• 查看当前登陆用户：
  1. select user();
  • 登录数据库
  1.  mysql -u用户名 -p密码 -h 连接地址
  • 修改当前用用户密码
  1. set password = password('123456');

库操作

  • 创建库

相当于创建了一个文件夹

  1. create database python;
  • 查看库
  1. show databases;

使用库

切换到对应的文件夹

  1. use python

表操作

  • 创建表
  1. create table tablename(字段名 类型(长度),字段名 类型(长度) 约束...)
  2. # 例子
  3. create table score(id int(8),name char(20),num int(4));
  • 修改表

使用

  1. 语法：
  2. 1. 修改表名
  3.       ALTER TABLE 表名 
  4.                       RENAME 新表名;
  6. 2. 增加字段
  7.       ALTER TABLE 表名
  8.                       ADD 字段名  数据类型 [完整性约束条件…],
  9.                       ADD 字段名  数据类型 [完整性约束条件…];
  11. 3. 删除字段
  12.       ALTER TABLE 表名 
  13.                       DROP 字段名;
  15. 4. 修改字段
  16.       ALTER TABLE 表名 
  17.                       MODIFY  字段名 数据类型 [完整性约束条件…];
  18.       ALTER TABLE 表名 
  19.                       CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
  20.       ALTER TABLE 表名 
  21.                       CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
  23. 5.修改字段排列顺序/在增加的时候指定字段位置
  24.     ALTER TABLE 表名
  25.                      ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
  26.     ALTER TABLE 表名
  27.                      ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;
  28.     ALTER TABLE 表名
  29.                      CHANGE 字段名  旧字段名 新字段名 新数据类型 [完整性约束条件…]  FIRST;
  30.     ALTER TABLE 表名
  31.                      MODIFY 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;

alter操作非空和唯一

  1. create table t(id int unique,name char(10) not null);
  3. #去掉null约束
  4. alter table t modify name char(10) null;
  5. # 添加null约束
  6. alter table t modify name char(10) not null;
  9. # 去掉unique约束
  10. alter table t drop index id;
  11. # 添加unique约束
  12. alter table t modify id int unique;
  14. alter处理null和unique约束

alter操作主键

  1. 1、首先创建一个数据表table_test：
  2. create table table_test(
  3. `id` varchar(100) NOT NULL,
  4. `name` varchar(100) NOT NULL,
  5. PRIMARY KEY (`name`)
  6. ); 
  7. 2、如果发现主键设置错了,应该是id是主键,但如今表里已经有好多数据了,不能删除表再重建了，仅仅能在这基础上改动表结构。
  8. 先删除主键
  9. alter table table_test drop primary key;
  10. 然后再增加主键
  11. alter table table_test add primary key(id);
  12. 注:在增加主键之前,必须先把反复的id删除掉。

为表添加外键

  1. 创建press表
  2. CREATE TABLE `press` (
  3.   `id` int(11) NOT NULL,
  4.   `name` char(10) DEFAULT NULL,
  5.   PRIMARY KEY (`id`)
  6. ) ；
  8. 创建book表
  9. CREATE TABLE `book` (
  10.   `id` int(11) DEFAULT NULL,
  11.   `bk_name` char(12) DEFAULT NULL,
  12.   `press_id` int(11) NOT NULL,
  13.   KEY `press_id` (`press_id`)
  14. ) ；
  16. 为book表添加外键
  17. alter table book add constraint fk_id foreign key(press_id) references press(id);
  19. 删除外键
  20. alter table book drop foreign key fk_id;

alter示例

  1. mysql> desc staff_info;
  2. +-------+-----------------------+------+-----+---------+-------+
  3. | Field | Type                  | Null | Key | Default | Extra |
  4. +-------+-----------------------+------+-----+---------+-------+
  5. | id    | int(11)               | YES  |     | NULL    |       |
  6. | name  | varchar(50)           | YES  |     | NULL    |       |
  7. | age   | int(3)                | YES  |     | NULL    |       |
  8. | sex   | enum('male','female') | YES  |     | NULL    |       |
  9. | phone | bigint(11)            | YES  |     | NULL    |       |
  10. | job   | varchar(11)           | YES  |     | NULL    |       |
  11. +-------+-----------------------+------+-----+---------+-------+
  12. 6 rows in set (0.00 sec)
  14. # 表重命名
  15. mysql> alter table staff_info rename staff;
  16. Query OK, 0 rows affected (0.00 sec)
  18. mysql> desc staff;
  19. +-------+-----------------------+------+-----+---------+-------+
  20. | Field | Type                  | Null | Key | Default | Extra |
  21. +-------+-----------------------+------+-----+---------+-------+
  22. | id    | int(11)               | YES  |     | NULL    |       |
  23. | name  | varchar(50)           | YES  |     | NULL    |       |
  24. | age   | int(3)                | YES  |     | NULL    |       |
  25. | sex   | enum('male','female') | YES  |     | NULL    |       |
  26. | phone | bigint(11)            | YES  |     | NULL    |       |
  27. | job   | varchar(11)           | YES  |     | NULL    |       |
  28. +-------+-----------------------+------+-----+---------+-------+
  29. 6 rows in set (0.00 sec)
  31. # 删除sex列
  32. mysql> alter table staff drop sex;
  33. Query OK, 0 rows affected (0.02 sec)
  34. Records: 0  Duplicates: 0  Warnings: 0
  36. mysql> desc staff;
  37. +-------+-------------+------+-----+---------+-------+
  38. | Field | Type        | Null | Key | Default | Extra |
  39. +-------+-------------+------+-----+---------+-------+
  40. | id    | int(11)     | YES  |     | NULL    |       |
  41. | name  | varchar(50) | YES  |     | NULL    |       |
  42. | age   | int(3)      | YES  |     | NULL    |       |
  43. | phone | bigint(11)  | YES  |     | NULL    |       |
  44. | job   | varchar(11) | YES  |     | NULL    |       |
  45. +-------+-------------+------+-----+---------+-------+
  46. 5 rows in set (0.01 sec)
  48. # 添加列
  49. mysql> alter table staff add sex enum('male','female');
  50. Query OK, 0 rows affected (0.03 sec)
  51. Records: 0  Duplicates: 0  Warnings: 0
  53. # 修改id的宽度
  54. mysql> alter table staff modify id int(4);
  55. Query OK, 0 rows affected (0.02 sec)
  56. Records: 0  Duplicates: 0  Warnings: 0
  58. mysql> desc staff;
  59. +-------+-----------------------+------+-----+---------+-------+
  60. | Field | Type                  | Null | Key | Default | Extra |
  61. +-------+-----------------------+------+-----+---------+-------+
  62. | id    | int(4)                | YES  |     | NULL    |       |
  63. | name  | varchar(50)           | YES  |     | NULL    |       |
  64. | age   | int(3)                | YES  |     | NULL    |       |
  65. | phone | bigint(11)            | YES  |     | NULL    |       |
  66. | job   | varchar(11)           | YES  |     | NULL    |       |
  67. | sex   | enum('male','female') | YES  |     | NULL    |       |
  68. +-------+-----------------------+------+-----+---------+-------+
  69. 6 rows in set (0.01 sec)
  71. # 修改name列的字段名
  72. mysql> alter table staff change name sname varchar(20);
  73. Query OK, 4 rows affected (0.03 sec)
  74. Records: 4  Duplicates: 0  Warnings: 0
  76. mysql> desc staff;
  77. +-------+-----------------------+------+-----+---------+-------+
  78. | Field | Type                  | Null | Key | Default | Extra |
  79. +-------+-----------------------+------+-----+---------+-------+
  80. | id    | int(4)                | YES  |     | NULL    |       |
  81. | sname | varchar(20)           | YES  |     | NULL    |       |
  82. | age   | int(3)                | YES  |     | NULL    |       |
  83. | phone | bigint(11)            | YES  |     | NULL    |       |
  84. | job   | varchar(11)           | YES  |     | NULL    |       |
  85. | sex   | enum('male','female') | YES  |     | NULL    |       |
  86. +-------+-----------------------+------+-----+---------+-------+
  87. 6 rows in set (0.00 sec)
  89. # 修改sex列的位置
  90. mysql> alter table staff modify sex enum('male','female') after sname;
  91. Query OK, 0 rows affected (0.02 sec)
  92. Records: 0  Duplicates: 0  Warnings: 0
  94. mysql> desc staff;
  95. +-------+-----------------------+------+-----+---------+-------+
  96. | Field | Type                  | Null | Key | Default | Extra |
  97. +-------+-----------------------+------+-----+---------+-------+
  98. | id    | int(4)                | YES  |     | NULL    |       |
  99. | sname | varchar(20)           | YES  |     | NULL    |       |
  100. | sex   | enum('male','female') | YES  |     | NULL    |       |
  101. | age   | int(3)                | YES  |     | NULL    |       |
  102. | phone | bigint(11)            | YES  |     | NULL    |       |
  103. | job   | varchar(11)           | YES  |     | NULL    |       |
  104. +-------+-----------------------+------+-----+---------+-------+
  105. 6 rows in set (0.00 sec)
  107. # 创建自增id主键
  108. mysql> alter table staff modify id int(4) primary key auto_increment;
  109. Query OK, 4 rows affected (0.02 sec)
  110. Records: 4  Duplicates: 0  Warnings: 0
  112. mysql> desc staff;
  113. +-------+-----------------------+------+-----+---------+----------------+
  114. | Field | Type                  | Null | Key | Default | Extra          |
  115. +-------+-----------------------+------+-----+---------+----------------+
  116. | id    | int(4)                | NO   | PRI | NULL    | auto_increment |
  117. | sname | varchar(20)           | YES  |     | NULL    |                |
  118. | sex   | enum('male','female') | YES  |     | NULL    |                |
  119. | age   | int(3)                | YES  |     | NULL    |                |
  120. | phone | bigint(11)            | YES  |     | NULL    |                |
  121. | job   | varchar(11)           | YES  |     | NULL    |                |
  122. +-------+-----------------------+------+-----+---------+----------------+
  123. 6 rows in set (0.00 sec)
  125. # 删除主键，可以看到删除一个自增主键会报错
  126. mysql> alter table staff drop primary key;
  127. ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
  129. # 需要先去掉主键的自增约束，然后再删除主键约束
  130. mysql> alter table staff modify id int(11);
  131. Query OK, 4 rows affected (0.02 sec)
  132. Records: 4  Duplicates: 0  Warnings: 0
  134. mysql> desc staff;
  135. +-------+-----------------------+------+-----+---------+-------+
  136. | Field | Type                  | Null | Key | Default | Extra |
  137. +-------+-----------------------+------+-----+---------+-------+
  138. | id    | int(11)               | NO   | PRI | 0       |       |
  139. | sname | varchar(20)           | YES  |     | NULL    |       |
  140. | sex   | enum('male','female') | YES  |     | NULL    |       |
  141. | age   | int(3)                | YES  |     | NULL    |       |
  142. | phone | bigint(11)            | YES  |     | NULL    |       |
  143. | job   | varchar(11)           | YES  |     | NULL    |       |
  144. +-------+-----------------------+------+-----+---------+-------+
  145. 6 rows in set (0.01 sec)
  147. mysql> alter table staff drop primary key;
  148. Query OK, 4 rows affected (0.06 sec)
  149. Records: 4  Duplicates: 0  Warnings: 0
  151. # 添加联合主键
  152. mysql> alter table staff add primary key (sname,age);
  153. Query OK, 0 rows affected (0.02 sec)
  154. Records: 0  Duplicates: 0  Warnings: 0
  156. # 删除主键
  157. mysql> alter table staff drop primary key;
  158. Query OK, 4 rows affected (0.02 sec)
  159. Records: 4  Duplicates: 0  Warnings: 0
  161. # 创建主键id
  162. mysql> alter table staff add primary key (id);
  163. Query OK, 0 rows affected (0.02 sec)
  164. Records: 0  Duplicates: 0  Warnings: 0
  166. mysql> desc staff;
  167. +-------+-----------------------+------+-----+---------+-------+
  168. | Field | Type                  | Null | Key | Default | Extra |
  169. +-------+-----------------------+------+-----+---------+-------+
  170. | id    | int(11)               | NO   | PRI | 0       |       |
  171. | sname | varchar(20)           | NO   |     |         |       |
  172. | sex   | enum('male','female') | YES  |     | NULL    |       |
  173. | age   | int(3)                | NO   |     | 0       |       |
  174. | phone | bigint(11)            | YES  |     | NULL    |       |
  175. | job   | varchar(11)           | YES  |     | NULL    |       |
  176. +-------+-----------------------+------+-----+---------+-------+
  177. 6 rows in set (0.00 sec)
  179. # 为主键添加自增属性
  180. mysql> alter table staff modify id int(4) auto_increment;
  181. Query OK, 4 rows affected (0.02 sec)
  182. Records: 4  Duplicates: 0  Warnings: 0
  184. mysql> desc staff;
  185. +-------+-----------------------+------+-----+---------+----------------+
  186. | Field | Type                  | Null | Key | Default | Extra          |
  187. +-------+-----------------------+------+-----+---------+----------------+
  188. | id    | int(4)                | NO   | PRI | NULL    | auto_increment |
  189. | sname | varchar(20)           | NO   |     |         |                |
  190. | sex   | enum('male','female') | YES  |     | NULL    |                |
  191. | age   | int(3)                | NO   |     | 0       |                |
  192. | phone | bigint(11)            | YES  |     | NULL    |                |
  193. | job   | varchar(11)           | YES  |     | NULL    |                |
  194. +-------+-----------------------+------+-----+---------+----------------+
  195. 6 rows in set (0.00 sec)
  • 查看表
  1. show tables;
  • 查看表结构
  1. # 查看一些简单的信息，更直观
  2. desc score;
  3. # 详细，可以查看到表名编码，存储引擎等..
  4. show create table score;
  • 删除表
  1. drop table score;

查看数据库的字符集设置

  1. show variables like '%character%';

临时设置字符集（数据库重启既失效）

永久设置需要修改mysql配置文件

  1. # 命令临时修改
  2. set character_set_server='utf8';

数据的增删改查

增

  1. # 插入一条数据
  2. insert into 表名(字段名) values(值)
  3. insert into score(id,name,num) values(1,'alex',0);
  4. # 插入多条数据
  5. insert into score(id,name,num) values(1,'alex',0),(2,'mhy',0);

删

  1. delete from 表名 where 条件
  2. # 删除这张表里ID等于2的数据
  3. delete from score where id = 2;

改

  1. update 表 set 字段名='新的值' where 条件
  2. update score set id = 4 where name = 'mhy';

查

  1. # 查询表里所有的数据
  2. select * from score;

约束 和 类型

类型

数字类型：

整型()： 长度的约束是无效的，它能够表示的大小只和它存储的字节数相关
类型 大小 范围 用途
tinyint 1bytes 2**8 小整数值（年龄）
smallint 2bytes 2**16 大整数值
miedium 3bytes 2**24 大整数值
int 4bytes 2**32 大整数值（整数）
bigint 8bytes 2**64 极大整数值

示例：
  1. # age3 tinyint unsigned 无符号（-）
  2. create table t1(age1 tinyint(2),age2 tinyint,age3 tinyint unsigned);

浮点型()：薪资

  • float(m,n) 单精度：

    • m 表示一共多少位
    • n 表示小数部分占其中的多少

示例
  1. create table t2(money float(6,2));
  2. insert into t2 values(2222222.33323232);
  • double 双精度：能够表示的小数点之后的位数更精准（和float使用方法一样）

示例：
  1. create table t4(money double);
  2. insert into t4 values(333.339324234323232);

字符串类型：

char（255）：
  • 浪费空间
  • 操作节省时间

例如：用户名\密码\手机号\身份证号

char是定长存储 在存储过程中会将剩余的字节数用空格来替补

char(20) 存储后就是 ‘mhy ‘会将剩余没有使用的使用空格来填补

varchar（65535）
  • 存储节省空间
  • 更加浪费时间（慢）

例如：评论\微博\微信朋友圈\论坛

varchar变长存储

‘mhy’ 存储过后就是 ‘mhy3’

示例
  1. create table t5(username char(20),password char(32));

时间类型：

datetime

年月日时分秒

例如：登陆时间\修改时间\出生日期

date

年月日

例如：注册时间

time

时分秒

例如：跑步计时

timestamp

时间戳

时间戳4字节：1970-2038-xx-xx

year

年

示例：
  1. # 创建
  2. create table t6(dt datetime,d date,t time,ts timestamp,y year);
  3. # 查看当前时间
  4. select now();
  5. # 插入当前时间
  6. insert into t6(dt) values(now());
  7. insert into t6(y) values(now());
  8. insert into t6(t) values(now());
  9. # 插入自定义时间
  10. insert into t6(d) values(20190915);
  11. insert into t6(t) values(230909);
  12. # 字符串形式
  13. insert into t6(t) values('23:09:09');
  14. insert into t6(d) values('2019/09/15');
  16. # 更新后timestamp会随着最后的时间进行修改
  17. update t6 set dt='2019-09-14 23:12:12' where d = '2019/09/15';
  19. # 更新
  20. create table t6(dt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, d date, t time, ts timestamp, y year);

单选和多选 性别 爱好

  • enum 单选

  • set 多选 并去重

示例：
  1. create table t8(gender enum('男','女'),hobby set('抽烟','喝酒','烫头','洗脚'));
  2. insert into t8 values('男','抽烟');
  3. insert into t8 values('男','抽烟,烫头,洗脚');

Innodb引擎

mysql 5.6以上版本默认的存储引擎就是innodb引擎。

frm：格式的文件，存储表结构

ibd：存储数据

查看当前数据库的存储引擎

  1. show variables like "default_storage_engine";

查看数据库的支持的存储引擎：

  1. show engines;

Innodb引擎：支持事务，行级锁定，外键

事务：

  1. 原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。

  2. 一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。

  3. 隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

  4. 持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

行级锁定：

当一个用户请求发生了事务，提交成功后，会对某一个用户加锁（行级锁），进行修改，同一时间，其他的人申请修改这个行的数据不能修改，但是其他行的数据不影响。这就是行级锁，但是如果同一时间修改的行过多，行级锁其实效率不高，innodb也支持表锁

外键：

表的外键是另一张表的主键。将两张表联系到一起。

作用：简单的说是为了保证数据的完整性。

严格模式设置：

  1. #临时修改
  2. set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
  4. # 配置文件修改
  5. sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"