一、Mysql主键和表的注释
（1）主键概述
当一个表的某个列（项）设置为主键后，这个列的数据不可以重复并且不能为空，一般一张表只有一个主键
- 主键特性:
- 主键列的值具有唯一性
- 主键列的值不允许为空（NULL）
- 设置主键
设置主键可以在创建表时指定也可以在原有表的基础上使用alter修改
使用 primary key auto_increment可以配置主键自增，主键自增即在插入值时，不写主键的值，那么主键的值会自加1，只限于主键类型是整数int类型
******搭建mysql，进入 mysql> use aaa; #进入库aaa Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; #查看aaa库的所有表 +---------------+ | Tables_in_aaa | +---------------+ | bbb | +---------------+ 1 row in set (0.00 sec) mysql> desc bbb; #查看bbb表的结构 +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | char(10) | YES | | NULL | | | sex | char(10) | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> insert into bbb values(1,"zhangsan","man","18"); #插入数据 Query OK, 1 row affected (0.00 sec) mysql> insert into bbb values(2,"wangwu","woman","20"); Query OK, 1 row affected (0.00 sec) mysql> insert into bbb values(3,"liliu","woman","22"); Query OK, 1 row affected (0.01 sec) mysql> select * from bbb; #查看bbb表的数据 +------+----------+-------+------+ | id | name | sex | age | +------+----------+-------+------+ | 1 | zhangsan | man | 18 | | 2 | wangwu | woman | 20 | | 3 | liliu | woman | 22 | +------+----------+-------+------+ 3 rows in set (0.00 sec)
-创建新表配置主键
mysql> create table aaa (id int primary key,name char(10)); #创建一个表，主键设置为id Query OK, 0 rows affected (0.00 sec) mysql> show create table aaa; #查看aaa表的详细信息 +-------+-----------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------+ | aaa | CREATE TABLE `aaa` ( `id` int(11) NOT NULL, `name` char(10) DEFAULT NULL, PRIMARY KEY (`id`) #从这里发现成功设置主键为id ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+-----------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
-修改原有表配置主键
mysql> alter table bbb modify id int primary key; #修改id为主键，修改属性时，必须加modify Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table bbb; #查看bbb表的信息 +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | bbb | CREATE TABLE `bbb` ( `id` int(11) NOT NULL, `name` char(10) CHARACTER SET utf8 DEFAULT NULL, `sex` char(10) CHARACTER SET utf8 DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`) #发现成功修改 ) ENGINE=InnoDB DEFAULT CHARSET=gbk | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
-删除表的主键
mysql> alter table bbb drop primary key; #直接删除bbb表的主键 Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> show create table bbb; #查看bbb表的信息 +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | bbb | CREATE TABLE `bbb` ( `id` int(11) NOT NULL, `name` char(10) CHARACTER SET utf8 DEFAULT NULL, `sex` char(10) CHARACTER SET utf8 DEFAULT NULL, `age` int(11) DEFAULT NULL #发现主键没有了 ) ENGINE=InnoDB DEFAULT CHARSET=gbk | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
-配置主键自增
mysql> alter table bbb modify id int primary key auto_increment; #配置bbb表的id为主键并且自增，在创建新表时也是在primary key 后面跟auto_increment即可 Query OK, 3 rows affected (0.11 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> show create table bbb; #查看bbb表的信息 +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | bbb | CREATE TABLE `bbb` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(10) CHARACTER SET utf8 DEFAULT NULL, `sex` char(10) CHARACTER SET utf8 DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`) #成功增加主键 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=gbk | #成功配置主键自增 +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from bbb; #查看表的信息 +----+----------+-------+------+ | id | name | sex | age | +----+----------+-------+------+ | 1 | zhangsan | man | 18 | | 2 | wangwu | woman | 20 | | 3 | liliu | woman | 22 | +----+----------+-------+------+ 3 rows in set (0.00 sec) mysql> insert into bbb (name,sex,age) values("nimen","man","12"); #给bbb表插入数据，不写id的值，自增需要前面指定列，不能直接使用values插入 Query OK, 1 row affected (0.00 sec) mysql> select * from bbb; #查看表的信息，发现插入的数据的id自加了1 +----+----------+-------+------+ | id | name | sex | age | +----+----------+-------+------+ | 1 | zhangsan | man | 18 | | 2 | wangwu | woman | 20 | | 3 | liliu | woman | 22 | | 4 | nimen | man | 12 | +----+----------+-------+------+ 4 rows in set (0.00 sec)
-配置列不能为空
mysql> show create table bbb; #先查看bbb表的信息 +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | bbb | CREATE TABLE `bbb` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(10) CHARACTER SET utf8 DEFAULT NULL, `sex` char(10) CHARACTER SET utf8 DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=gbk | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> alter table bbb modify name char(10) not null; #修改bbb表的name项不能为空，要记得使用modify，在创建新表时配置不允许为空也是大同小异，直接在配置的项后面加not null即可，例如：create table aaa (aaa int not null); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0
（2）表的注释
即可以给列增加一个注释，方便识别，不常用，只能再查看show create table 时才可以看到注释
mysql> create table ccc (id int comment "工号"); Query OK, 0 rows affected (0.01 sec) mysql> insert into ccc values(1); Query OK, 1 row affected (0.00 sec) mysql> select * from ccc; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> show create table ccc; +-------+--------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------+ | ccc | CREATE TABLE `ccc` ( `id` int(11) DEFAULT NULL COMMENT '工号' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+--------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
二、事务概述
（1）什么是事务
- 事务就是多个SQL语句组成的，这些SQL语句会一起执行，维护数据的完整性，要么成功，要么不成功，就是说如果事务中的一条SQL语句没有执行成功，那么整个事务就会失败
- 一个事务中包含多条SQL语句，这些SQL语句之间存在着一定的关系，SQL语句的数量为n，n>0
- 不是所有的数据库引擎都支持事务，mysql的默认引擎innoDB是支持事务的
- 关系型数据库支持事务，非关系型数据库是不支持事务的
（2）事务的特性（ACID）
事务的特性为ACID，分别为：原子性、一致性、隔离性、持久性
- 原子性(Atomic)： 事务的所有操作要么全部执行，要么全部不执行，如果中途出现错误不会停止，而是回滚到执行事务之前的状态
例如：一个事务中有十条SQL语句，在执行事务的时候，这十条SQL语句必须全部执行成功之后，事务才会成功，反之只要有一条SQL语句失败，那么整个事务就会失败
- 一致性(Consistency)： 如果事务执行前是一致的，那么执行后也是一致的，不能破坏关系数据的完整性以及业务逻辑的一致性，事务按照预期实现
例如：用户A有1000RMB，用户B也有1000RMB，用户A向用户B转了500RMB，那么用户B会加500变成1500RMB，而用户A会减少500变成500RMB，但是两个用户RMB的总数始终都是2000RMB
- 隔离性(lsolation)： 隔离性可以防止多个事务一起执行时导致数据出现不一致
例如：用户A，用户B，用户C都有1000RMB，用户A向用户B转500RMB，用户C也向用户B转500RMB，此期间，用户A和用户B的操作是事务1，用户C和用户B的操作是事务2，两件事务是互相隔离的
- 持久性(Durability)： 事务执行成功后会对数据库进行永久性修改
例如：用户A有1000RMB，用户B也有1000RMB，用户A向用户B转了500RMB，那么用户B会加500变成1500RMB，而用户A会减少500变成500RMB，这个操作会在数据库里永久修改
（3）如果事务并发不进行事务隔离会怎么样
执行事务时，如果不进行事务隔离可以会造成三种错误操作，分别是：脏读、不可重复读、幻读
- 脏读： 事务1读到未提交的事务2修改的数据，如果此时事务2中途执行失败回滚，那么此时事务1读取的数据就是脏数据
例如：事务1对RMB进行修改，此时事务2已经读取到了事务1修改后的结果，但是此时事务1进行了回滚，又回到了修改之前的状态，但是事务2已经读取到了修改后的数据，此时，事务2读取到的就是脏数据
- 不可重复读： 同一个事务中，对同一份数据读取的结果不一致
例如：事务1在事务2执行之前已经读取了数据，在事务2执行之后又读取了一次数据，此时事务1是读取了两次数据，多读取了一次，造成数据结果的不一致
- 幻读： 同一个事务中，同一个查询但是多次返回的结果不一样
例如：事务2查询表的记录数量，查询完之后，事务1向表中插入一条记录，接着事务2又查询了一次表的数量，发现记录数量不一样
区别：
- 脏读和不可重复读： 脏读是事务读取了还未提交事务的更新数据，不可重复读是同一个事务中，进行多次读取的数据不一样
- 不可重复读和幻读的区别： 都是在同一个事务中，前者是进行多次读取的数据不一样，而后者是几次读取的数据整体不一致
（4）事务的隔离级别
| 隔离级别 | 作用 |
| Serializable（串行化） | 避免脏读、不可重复读、幻读 |
| Repeatable（可重复度） | 避免脏读、不可重复读 |
| Read committed（读已提交） | 避免脏读 |
| Read uncommitted（读未提交） | NONE（没用） |
Mysql支持这四种隔离级别，默认为可重复读
（5）Mysql数据库管理事务
管理事务的三个命令，分别是：begin、commit、rollback
- begin： 开始事务，后面有多条数据库操作语句开始执行
- commit： 开始提交一个事务，对应前面的begin操作，讲事务处理的结果保存到数据文件中
- rollback： 开始回滚一个事务，在begin和commit之间，讲事务中的全部语句撤回，恢复到执行begin之前的数据状态，要注意rollback需要在执行commit之前执行才有效
- set autocommit = 0 \ 1 ： 在数据库中执行，临时禁用或开启自动提交，自动提交为退出 mysql时就算没有输入commit也会提交或者是执行下一条DML （数据操纵语言，即insert、delete等） 语句时会自动提交，1为自动提交，0为禁止自动提交
（6）在Mysql中管理事务
-确保表的存储引擎为InnoDB，如果不是的话，使用alter修改即可
[root@rzy ~]# mysql -u root -p123123 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.12 Source distribution Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | aaa | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.10 sec) mysql> use aaa; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables ; +---------------+ | Tables_in_aaa | +---------------+ | aaa | | bbb | | ccc | +---------------+ 3 rows in set (0.01 sec) mysql> show create table bbb; +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | bbb | CREATE TABLE `bbb` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(10) NOT NULL, `sex` char(10) CHARACTER SET utf8 DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=gbk | #查看表的存储引擎为innoDB +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
-使用begin
mysql> select * from bbb; #先查看bbb表的全部信息 +----+----------+-------+------+ | id | name | sex | age | +----+----------+-------+------+ | 1 | zhangsan | man | 18 | | 2 | wangwu | woman | 20 | | 3 | liliu | woman | 22 | | 4 | nimen | man | 12 | +----+----------+-------+------+ 4 rows in set (0.00 sec) mysql> begin; #开始事务 Query OK, 0 rows affected (0.00 sec) mysql> insert into bbb values(5,"hehe","man",80); #插入数据 Query OK, 1 row affected (0.00 sec) mysql> select * from bbb; #再次查看，已经成功修改，但是这个事务还没有提交 +----+----------+-------+------+ | id | name | sex | age | +----+----------+-------+------+ | 1 | zhangsan | man | 18 | | 2 | wangwu | woman | 20 | | 3 | liliu | woman | 22 | | 4 | nimen | man | 12 | | 5 | hehe | man | 80 | +----+----------+-------+------+ 5 rows in set (0.00 sec) mysql> commit; #提交事务 Query OK, 0 rows affected (0.00 sec) mysql> select * from bbb; #再次查看bbb表的所有信息，发现成功插入数据， +----+----------+-------+------+ | id | name | sex | age | +----+----------+-------+------+ | 1 | zhangsan | man | 18 | | 2 | wangwu | woman | 20 | | 3 | liliu | woman | 22 | | 4 | nimen | man | 12 | | 5 | hehe | man | 80 | +----+----------+-------+------+ 5 rows in set (0.00 sec) mysql> exit #退出 Bye [root@rzy ~]# mysql -u root -p123123 #重新进入mysql mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.12 Source distribution Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use aaa; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from bbb; #查看bbb表，发现刚刚插入的数据还在，说明刚才那个事务已经成功执行 +----+----------+-------+------+ | id | name | sex | age | +----+----------+-------+------+ | 1 | zhangsan | man | 18 | | 2 | wangwu | woman | 20 | | 3 | liliu | woman | 22 | | 4 | nimen | man | 12 | | 5 | hehe | man | 80 | +----+----------+-------+------+ 5 rows in set (0.00 sec) mysql> begin ; #再次开启一个事务 Query OK, 0 rows affected (0.00 sec) mysql> insert into bbb values(6,"heihie","woman",99); #插入数据 Query OK, 1 row affected (0.00 sec) mysql> exit #这次不进行提交就退出 Bye [root@rzy ~]# mysql -u root -p123123 #重新退出 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.12 Source distribution Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select * from aaa.bbb; #再次查看表，发现没有进行手动提交的数据没有成功插入表中 +----+----------+-------+------+ | id | name | sex | age | +----+----------+-------+------+ | 1 | zhangsan | man | 18 | | 2 | wangwu | woman | 20 | | 3 | liliu | woman | 22 | | 4 | nimen | man | 12 | | 5 | hehe | man | 80 | +----+----------+-------+------+ 5 rows in set (0.00 sec)
