MySQL 主键与事务

一、MySQL 主键和表字段的注释

1.主键及自增

每一张表通常会有一个且只有一个主键，来表示每条数据的唯一性。

• 特性：值不能重复，不能为空 null

格式：create table test (ID int primary key) 

主键 + 自增的写法：

格式：create table test (ID int primary key auto_increment) 

• 注意：自增只能配合主键来使用（如果单独定义则会报错）

2.表字段的注释

mysql> alter table test modify Name varchar(12) comment '用户名'; 

3.多表查询

mysql> create table A(ID int primary key auto_increment,Name varchar(12),Department int); mysql> create table B(ID int primary key auto_increment,Name varchar(12)); mysql> insert into B(Name) values("财务"),("市场"); mysql> insert into A(Name,Department) values("张三",1),("李四",2),("王五",2); 

mysql> select B.Name 部门,A.Name from B,A where B.ID=2 and A.Department=2; 

二、数据库事务概述

数据库：关系型数据库（支持事务）；非关系型数据库（不支持）

• 什么是事务：保证成批操作要么完全执行，要么完全不执行，维护数据的完整性。简单来说就是要么成功要么不成功。

一个事务中包含多条 SQL 语句，而且这些 SQL 语句之间存在一定的关系：

• 事务可以是 N 条 SQL 语句（N >= 0）
• 不是所有数据库存储引擎都支持事务，InnoDB 存储引擎支持事务处理。

1.数据库事务特性（ACID）

2.事务并发不进行事务隔离

• 脏读： 事务 A 读到未提交事务 B 修改的数据，如果此时事务 B 中途执行失败回滚，那么此时事务 A 读取到的就是脏数据。
• 不可重复读： 同一个事务中，对同一份数据读取的结果不一致（针对数据）。
• 幻读： 同一个事务中，同一个查询多次返回的结果不一样（针对表中总行）。

区别：

• 脏读和不可重复读：脏读是事务读取了还未提交事务的更新数据。不可重复读是同一个事务中，几次读取的数据不同。
• 不可重复读和幻读：都是在同一个事务中，前者是几次读取数据不同，后者是几次读取数据整体不同。

隔离级别：

• MySQL 支持上面 4 种隔离级别，默认为可重复读。
• 如若想修改隔离级别需： sed -i '/\[mysqld]/a transaction-isolation = SERIALIZABLE' /etc/my.cnf

mysql> show variables like '%tx_is%'; mysql> exit [root@MySQL ~]# sed -i '/\[mysqld]/a transaction-isolation = SERIALIZABLE' /etc/my.cnf [root@MySQL ~]# systemctl restart mysqld [root@MySQL ~]# mysql -uroot -p -e "show variables like '%tx_is%';" 

4.MySQL 数据库管理事务

管理事务的三个命令：

• BEGIN：开始事务，后面有多条数据库操作语句开始执行。
• COMMIT：开始提交一个事务，对应前面的 BEGIN 操作，将事务处理的结果保存到数据文件中。
• ROLLBACK：开始回滚一个事务，在 BEGIN 和 COMMIT 中间，将事务中的全部语句撤销，恢复到 BEGIN 之前的数据状态。
• set autocommit = 0/1：禁用或开启自动提交，自动提交为退出 MySQL 连接程序时，或执行下一条 DML (数据操纵语言) 语句。

其实通过手动的方式来管理事务的目的很简单，就是为了使多条 SQL 语句变相的组成一个事务。因为事务会把每条 SQL 语句都当作事务来处理，正因为这样，一些具有关联性的 SQL 语句，如果中途执行失败，那么处理起来便会相对比较麻烦。

mysql> create table C(ID int); mysql> insert into C values(1),(2); mysql> select * from C; mysql> BEGIN; mysql> insert into C values(3); mysql> COMMIT; mysql> select * from C; 

mysql> show variables like 'autocommit'; #查看是否开启自动提交事务 mysql> BEGIN; mysql> insert into C values(4) mysql> select * from C; mysql> exit [root@localhost ~]# mysql -uroot -p -e "select * from Coco.C where ID=4" 

• set autocommit=0：在数据库中修改为临时生效。

mysql> set autocommit=0; mysql> select * from Coco.C; mysql> insert into Coco.C values(4); mysql> select * from Coco.C where ID=4; [root@localhost ~]# mysql -uroot -p -e "select * from Coco.C where ID=4" 

注意：

• 使用 BEGIN 或 START TRANSACTION 开启一个事务之后，自动提交将保持禁用状态，直到使用 COMMIT 或 ROLLBACK 结束事务。
• 之后，自动提交模式会恢复到之前的状态，即如果 BEGIN 前 autocommit = 1，则完成本次事务后 autocommit 还是 1。
• 如果 BEGIN 前 autocommit = 0，则完成本次事务后 autocommit 还是 0。

1）别名

mysql> select ID as "编号",Name as "姓名",Department as "部门" from A where ID=1; mysql> select ID "编号",Name "姓名",Department "部门" from A where ID=1; 

2）去重

mysql> select distinct Department from A; 

3）AND 和 OR 运算符

• AND：逻辑与（条件都要满足）；OR：逻辑或（条件只需要满足一个）。

mysql> select * from A where ID >= 3 and Department = 2; mysql> select * from A where ID >= 3 or Department = 2; 

• in：如果查询某个字段的值为多个时，可以使用 in 关键字。
• between and：在某两个数值之间包含 and 两边的数。

mysql> select * from A where ID in(1,3,4); mysql> select * from A where ID not in(1,3,4); mysql> select * from A where ID between 1 and 3; 

4）SQL LIKE 操作符

• -_：代表单个未知字符。
• - %：代表多个未知字符。
• 例：A% A 开头，%B B 结尾，%C% 包含 C，_D% 第二个字母是 D，%E__ 倒数第三个字母是 E，%@163.com 163 邮箱。

mysql> select * from A where Name like "%三%"; mysql> select * from A where Name like "%三%" or Name like "%四"; 

5）SQL ORDER BY 子句

order by：用于排序，by 后面添加排序规则的字段。

• asc：升序（默认就是升序）
• desc：降序。

mysql> select * from A order by ID desc; mysql> select * from A order by Department,ID desc; 

6）Limit 子句

mysql> select * from C; mysql> select * from C limit 2; mysql> select * from C limit 0,2;