1、终端操作MySQL数据库
1.1如何登陆数据库服务器：
C:Usersusername>mysql -uroot -proot
1.2如何查询数据库服务器中的数据库
mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys || test1 |+--------------------+5 rows in set (0.03 sec)复制代码1.3如何选中某一个数据库进行操作
mysql> use test1Database changed复制代码1.4SQL语句中的查问
《2020最新Java根底精讲视频教程和学习路线！》
mysql> select * from person;+-----------+------+| name | sex |+-----------+------+| 张三 | 男 || 李四 | 女 || 王麻子 | 男 |+-----------+------+3 rows in set (0.00 sec)mysql> select * from person where name="张三";+--------+------+| name | sex |+--------+------+| 张三 | 男 |+--------+------+1 row in set (0.00 sec)复制代码1.5如何退出数据库服务器
mysql> exit;Bye 复制代码2、如何创立数据库数据表
2.1如何在数据库服务器中创立咱们的数据库
mysql> create database test2;Query OK, 1 row affected (0.05 sec)mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys || test1 || test2 |+--------------------+6 rows in set (0.00 sec)mysql> use test2;Database changed复制代码2.2如何查看某个数据库中的所有数据表
mysql> show tables;Empty set (0.00 sec)复制代码2.3如何创立一个数据表
mysql> create table pet( -> name VARCHAR(20), -> owner VARCHAR(20), -> species VARCHAR(20), -> sex CHAR(1), -> birth DATE, -> death DATE);Query OK, 0 rows affected (0.09 sec)复制代码创立了一个宠物数据表
查看数据表是否创立胜利
mysql> show tables;+-----------------+| Tables_in_test2 |+-----------------+| pet |+-----------------+1 row in set (0.00 sec)复制代码2.4查看创立好的数据表的构造
mysql> describe pet;+---------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+---------+-------------+------+-----+---------+-------+| name | varchar(20) | YES | | NULL | || owner | varchar(20) | YES | | NULL | || species | varchar(20) | YES | | NULL | || sex | char(1) | YES | | NULL | || birth | date | YES | | NULL | || death | date | YES | | NULL | |+---------+-------------+------+-----+---------+-------+6 rows in set (0.00 sec)复制代码3、insert减少数据记录
3.1查看数据表中的记录
mysql> select * from pet;Empty set (0.00 sec)复制代码3.2如何往数据表中增加数据记录
mysql> insert into pet -> values('Tom','LiMing','hamster','f','2020-11-11',null);Query OK, 1 row affected (0.01 sec)复制代码再一次查问
mysql> select * from pet;+------+--------+---------+------+------------+-------+| name | owner | species | sex | birth | death |+------+--------+---------+------+------------+-------+| Tom | LiMing | hamster | f | 2020-11-11 | NULL |+------+--------+---------+------+------------+-------+1 row in set (0.00 sec)复制代码mysql> insert into pet -> values('旺财','周星星','狗','公','2020-11-11',null);Query OK, 1 row affected (0.01 sec)复制代码查看
mysql> select * from pet;+--------+-----------+---------+------+------------+-------+| name | owner | species | sex | birth | death |+--------+-----------+---------+------+------------+-------+| Tom | LiMing | hamster | f | 2020-11-11 | NULL || 旺财 | 周星星 | 狗 | 公 | 2020-11-11 | NULL |+--------+-----------+---------+------+------------+-------+2 rows in set (0.00 sec)复制代码3.3MySQL罕用数据类型
MySQL反对多种类型，大抵能够分为三类：数值、日期/工夫和字符串(字符)类型。
- 数值类型
如果数值超出数值类型的范畴就会报错：
mysql> create table testType( -> number tinyint);Query OK, 0 rows affected (0.10 sec)mysql> describe testType;+--------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+---------+------+-----+---------+-------+| number | tinyint | YES | | NULL | |+--------+---------+------+-----+---------+-------+1 row in set (0.00 sec)mysql> insert into testType -> values(128);ERROR 1264 (22003): Out of range value for column 'number' at row 1复制代码- 日期和工夫类型
- 字符串类型
数据类型如何抉择
日期：依照日期格局
数值和字符串：依照大小
4、数据记录增删查改操作
4.1插入：
mysql> insert into pet values('Buffy','Job','cat','m','2020-11-10',null);Query OK, 1 row affected (0.01 sec)mysql> insert into pet values('Slim','邓紫棋','dog','f','2019-11-11',null);Query OK, 1 row affected (0.01 sec)mysql> insert into pet -> values('Fang','成龙','cat','m','2018-11-11','2020-10-10');Query OK, 1 row affected (0.01 sec)mysql> select * from pet;+--------+-----------+---------+------+------------+------------+| name | owner | species | sex | birth | death |+--------+-----------+---------+------+------------+------------+| Tom | LiMing | hamster | f | 2020-11-11 | NULL || 旺财 | 周星星 | 狗 | 公 | 2020-11-11 | NULL || Buffy | Job | cat | m | 2020-11-10 | NULL || Slim | 邓紫棋 | dog | f | 2019-11-11 | NULL || Fang | 成龙 | cat | m | 2018-11-11 | 2020-10-10 |+--------+-----------+---------+------+------------+------------+5 rows in set (0.00 sec)复制代码4.2如何删除数据
mysql> delete from pet where name='Tom';Query OK, 1 row affected (0.01 sec)复制代码再次查看
mysql> select * from pet;+--------+-----------+---------+------+------------+------------+| name | owner | species | sex | birth | death |+--------+-----------+---------+------+------------+------------+| 旺财 | 周星星 | 狗 | 公 | 2020-11-11 | NULL || Buffy | Job | cat | m | 2020-11-10 | NULL || Slim | 邓紫棋 | dog | f | 2019-11-11 | NULL || Fang | 成龙 | cat | m | 2018-11-11 | 2020-10-10 |+--------+-----------+---------+------+------------+------------+4 rows in set (0.00 sec)复制代码4.3如何批改数据
mysql> update pet set name='旺财财' where owner='周星星';Query OK, 1 row affected (0.01 sec)复制代码再次查看：
mysql> select * from pet;+-----------+-----------+---------+------+------------+------------+| name | owner | species | sex | birth | death |+-----------+-----------+---------+------+------------+------------+| 旺财财 | 周星星 | 狗 | 公 | 2020-11-11 | NULL || Buffy | Job | cat | m | 2020-11-10 | NULL || Slim | 邓紫棋 | dog | f | 2019-11-11 | NULL || Fang | 成龙 | cat | m | 2018-11-11 | 2020-10-10 |+-----------+-----------+---------+------+------------+------------+4 rows in set (0.00 sec)复制代码4.4总结：数据记录常见操作
减少：insert
删除：delete
批改：update
查问：select
5、MySQL建表束缚
5.1主键束缚 primary key
它可能惟一确定一张表中的一条记录，也就是咱们通过给某个字段增加束缚，就能够使得该字段不反复且不为空。
mysql> create table user( -> id int primary key, -> name varchar(20));Query OK, 0 rows affected (0.08 sec)mysql> describe user;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int | NO | PRI | NULL | || name | varchar(20) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)复制代码再次插入id=1的记录
mysql> insert into user values(1,'李四');ERROR 1062 (23000): Duplicate entry '1' for key 'user.PRIMARY'复制代码id为空的时候
mysql> insert into user values(null,'李四');ERROR 1048 (23000): Column 'id' cannot be null复制代码5.2联结主键
mysql> create table user2( -> id int, -> name varchar(20), -> password varchar(20), -> primary key(id,name) -> );Query OK, 0 rows affected (0.06 sec)复制代码插入记录
mysql> insert into user2 values(1,'zhangsan','123');Query OK, 1 row affected (0.01 sec)mysql> insert into user2 values(1,'zhangsan','234');ERROR 1062 (23000): Duplicate entry '1-zhangsan' for key 'user2.PRIMARY'mysql> insert into user2 values(2,'zhangsan','234');Query OK, 1 row affected (0.01 sec)复制代码联结主键只有主键字段之间加起来不反复就能够，但任何一个主键字段都不能够为空。
5.3自增束缚auto_increment
mysql> create table user3( -> id int primary key auto_increment, -> name varchar(20) -> );Query OK, 0 rows affected (0.05 sec)复制代码插入数据
mysql> insert into user3 (name) values('zhangsan');Query OK, 1 row affected (0.01 sec)查看mysql> select * from user3;+----+----------+| id | name |+----+----------+| 1 | zhangsan |+----+----------+1 row in set (0.00 sec)复制代码自增束缚和主键束缚搭配在一起应用时，它会主动给咱们管去控值，例如：主动减少id。
5.4如果创立表的时候遗记创立主键束缚怎么办？
mysql> create table user4( -> id int, -> name varchar(20));Query OK, 0 rows affected (0.06 sec)mysql> desc user4;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int | YES | | NULL | || name | varchar(20) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)复制代码批改表构造，减少主键束缚 alter ... add
mysql> alter table user4 add primary key(id);Query OK, 0 rows affected (0.12 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc user4;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int | NO | PRI | NULL | || name | varchar(20) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)复制代码如何删除呢？alter ...drop
mysql> alter table user4 drop primary key;Query OK, 0 rows affected (0.10 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc user4;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int | NO | | NULL | || name | varchar(20) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)复制代码应用modify，批改字段，增加束缚
mysql> alter table user4 modify id int primary key;Query OK, 0 rows affected (0.08 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> describe user4;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int | NO | PRI | NULL | || name | varchar(20) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)复制代码5.5惟一束缚
束缚润饰的字段的值不能够反复。
mysql> create table user5( -> id int, -> name varchar(20));Query OK, 0 rows affected (0.06 sec)mysql> desc user5;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int | YES | | NULL | || name | varchar(20) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> alter table user5 add unique(name);Query OK, 0 rows affected (0.04 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc user5;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int | YES | | NULL | || name | varchar(20) | YES | UNI | NULL | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> insert into user5 values(1,'zhangsan');Query OK, 1 row affected (0.03 sec)mysql> insert into user5 values(1,'zhangsan');ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'user5.name'复制代码name=lisi不能反复
mysql> insert into user5 values(1,'lisi');Query OK, 1 row affected (0.01 sec)mysql> create table user6( -> id int, -> name varchar(20), -> unique(name));Query OK, 0 rows affected (0.07 sec)复制代码能够对id和name都增加惟一束缚，这时候只有id+name不反复就能够增加记录。
mysql> create table user7( -> id int, -> name varchar(20), -> unique(id,name));Query OK, 0 rows affected (0.06 sec)mysql> insert into user7 values(1,'zhangsan');Query OK, 1 row affected (0.01 sec)mysql> insert into user7 values(2,'zhangsan');Query OK, 1 row affected (0.04 sec)mysql> select * from user7;+------+----------+| id | name |+------+----------+| 1 | zhangsan || 2 | zhangsan |+------+----------+2 rows in set (0.00 sec)复制代码如何删除惟一束缚
mysql> alter table user6 drop index name;Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc user6;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int | YES | | NULL | || name | varchar(20) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)复制代码通过modify增加惟一束缚
mysql> alter table user6 modify name varchar(20) unique;Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc user6;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int | YES | | NULL | || name | varchar(20) | YES | UNI | NULL | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)复制代码总结：
- 建表的时候增加束缚
- 能够用alter... add增加
- 能够用alter... modify增加束缚
- 删除用alter...drop
5.6非空束缚
润饰的字段不能为空null
mysql> create table user8( -> id int, -> name varchar(20) not null);Query OK, 0 rows affected (0.05 sec)mysql> desc user8;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int | YES | | NULL | || name | varchar(20) | NO | | NULL | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)复制代码测试：如果name为空
mysql> insert into user8 (id)values(1);ERROR 1364 (HY000): Field 'name' doesn't have a default value复制代码正确增加不为空时
mysql> insert into user8 values(1,'lisi');Query OK, 1 row affected (0.01 sec)mysql> select *from user8;+------+------+| id | name |+------+------+| 1 | lisi |+------+------+1 row in set (0.00 sec)复制代码如果只传name是能够的
mysql> insert into user8 (name)values('zhangsan');Query OK, 1 row affected (0.01 sec)mysql> select *from user8;+------+----------+| id | name |+------+----------+| 1 | lisi || NULL | zhangsan |+------+----------+2 rows in set (0.00 sec)复制代码同样能够用alter ... add，alter ... modify来增加惟一束缚。
5.7默认束缚default
就是当咱们插入字段值的时候，如果没有传值，就会应用默认值。
mysql> create table user9( -> id int, -> name varchar(20), -> age int default 10 -> );Query OK, 0 rows affected (0.06 sec)复制代码查看表构造
mysql> desc user9;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int | YES | | NULL | || name | varchar(20) | YES | | NULL | || age | int | YES | | 10 | |+-------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)复制代码插入值时直插入id和name那么age应用默认值10，如果age也插入值那么就应用插入的值不应用默认值。
mysql> insert into user9(id,name) values (1,'lisi');Query OK, 1 row affected (0.01 sec)mysql> select *from user9;+------+------+------+| id | name | age |+------+------+------+| 1 | lisi | 10 |+------+------+------+1 row in set (0.00 sec)复制代码5.8 外键束缚
波及到两个表：父表，子表。
班级表：
mysql> create table classes( -> id int primary key, -> name varchar(20) -> );Query OK, 0 rows affected (0.05 sec)mysql> desc classes;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int | NO | PRI | NULL | || name | varchar(20) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)复制代码插入值
mysql> insert into classes values(1,'yiban');Query OK, 1 row affected (0.01 sec)mysql> insert into classes values(2,'erban');Query OK, 1 row affected (0.03 sec)mysql> insert into classes values(3,'sanban');Query OK, 1 row affected (0.01 sec)mysql> select *from classes;+----+--------+| id | name |+----+--------+| 1 | yiban || 2 | erban || 3 | sanban |+----+--------+3 rows in set (0.00 sec)复制代码学生表：
mysql> create table students( -> id int primary key, -> name varchar(20), -> class_id int, -> foreign key(class_id) references classes(id));Query OK, 0 rows affected (0.06 sec)复制代码mysql> desc students;+----------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| id | int | NO | PRI | NULL | || name | varchar(20) | YES | | NULL | || class_id | int | YES | MUL | NULL | |+----------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)复制代码插入值
mysql> insert into students values(1001,'zhangsan',1);Query OK, 1 row affected (0.01 sec)mysql> insert into students values(1002,'zhangsan',2);Query OK, 1 row affected (0.01 sec)mysql> insert into students values(1003,'zhangsan',3);Query OK, 1 row affected (0.04 sec)mysql> select *from students;+------+----------+----------+| id | name | class_id |+------+----------+----------+| 1001 | zhangsan | 1 || 1002 | zhangsan | 2 || 1003 | zhangsan | 3 |+------+----------+----------+3 rows in set (0.00 sec)复制代码父表classes中没有的数据值，是不能够在子表students中应用的。
父表中的记录被子表援用，是不能够被删除的。
mysql> delete from classes where id=3;ERROR 1451 (23000): Cannot delete or update a parent row:a foreign key constraint fails (`test2`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))复制代码数据库三大范式
- 第一范式 1NF
- *
数据库表中的所有字段都是不可分割的原子值。
mysql> create table student( -> id int primary key, -> name varchar(20), -> address varchar(30) -> );Query OK, 0 rows affected (0.12 sec)mysql> insert into student values(1,'zhangsan','甘肃会宁');Query OK, 1 row affected (0.02 sec)mysql> insert into student values(2,'lisi','甘肃会宁');Query OK, 1 row affected (0.01 sec)mysql> insert into student values(3,'laowang','甘肃会宁');Query OK, 1 row affected (0.01 sec)mysql> select * from student;+----+----------+--------------+| id | name | address |+----+----------+--------------+| 1 | zhangsan | 甘肃会宁 || 2 | lisi | 甘肃会宁 || 3 | laowang | 甘肃会宁 |+----+----------+--------------+3 rows in set (0.00 sec)复制代码address字段值还能够持续拆分的就不满足第一范式。
create table student2( id int primary key, name varchar(20), country varchar(30), privence varchar(30), city varchar(30), details varchar(30) );复制代码复制代码insert into student2 values(1,'张三','中国','甘肃','兰州','安宁区安定街道30号'); insert into student2 values(2,'李四','中国','甘肃','兰州','安宁区安定街道34号'); insert into student2 values(3,'王五','中国','甘肃','兰州','安宁区安定街道80号');
mysql> select * from student2;+----+--------+---------+----------+--------+----------------------------+| id | name | country | privence | city | details |+----+--------+---------+----------+--------+----------------------------+| 1 | 张三 | 中国 | 甘肃 | 兰州 | 安宁区安定街道30号 || 2 | 李四 | 中国 | 甘肃 | 兰州 | 安宁区安定街道34号 || 3 | 王五 | 中国 | 甘肃 | 兰州 | 安宁区安定街道80号 |+----+--------+---------+----------+--------+----------------------------+3 rows in set (0.00 sec)复制代码范式：设计的越具体，对于某些实际操作可能更好，但不肯定都有益处。个别要结合实际状况设计。
- 第二范式 2NF
- *
第二范式必须是满足第一范式的前提下，要求，除主键外的每一列都必须齐全依赖于主键。 如果呈现不齐全依赖，只可能产生在联结主键的状况下。
create table myolder( product_id int, customer_id int, product_name varchar(30), customer_name varchar(30), primary key(product_id,customer_id));复制代码问题： 除主键外的其余列，只依赖于主键的局部字段。 不满足第二范式 解决办法是拆表。
create table myolder( older_id int primary key, product_id int, customer_id int);create table product( id int primary key, name varchar(20));create table customer( id int primary key, name varchar(20));复制代码分成三个表之后就满足第二范式的设计！！！
- 第三范式 3NF
- *
必须先满足第二范式， 除开主键列的其余列之间不能有传递依赖关系。 例如：
create table myolder( older_id int primary key, product_id int, customer_id int, customer_phone varchar(15));复制代码在这个表中，咱们能够看到customer_phone和customer_id之间存在依赖关系，这样就不满足第三范式的要求；解决办法是：咱们把customer_phone放到customer表中。如下：
create table customer( id int primary key, name varchar(20), phone varchar(15));复制代码sql的四种连贯查问
内连贯inner join 或者join外连贯左连贯 left join 或者left outer join右连贯 right join 或者right outer join齐全外连贯full join 或者full outer join复制代码创立两个表：
person表id，name，cardID1 张三 12 李四 33 王五 6card表id,name1 饭卡2 建行卡3 农行卡4 工商卡5 邮政卡并没有创立外键--inner join查问（内连贯）SELECT * from person INNER JOIN card ON person.cardID=card.id;id name cardid id name1 张三 1 1 饭卡2 李四 3 3 农行卡--内连查问，其实就是两张表中的数据，通过某个字段相等查问出相干记录数据。--left join左外连贯SELECT * FROM person LEFT JOIN card ON person.cardID=card.id;+------+--------+--------+------+-----------+| id | name | cardID | id | name |+------+--------+--------+------+-----------+| 1 | 张三 | 1 | 1 | 饭卡 || 2 | 李四 | 3 | 3 | 农行卡 || 3 | 王五 | 6 | NULL | NULL |+------+--------+--------+------+-----------+3 rows in set (0.00 sec)---左外连贯，会把右边表里所有的数据取出来，而左边表里的数据，如果有相等的，就显示进去，如果没有就补null。----right join右外连贯SELECT * FROM person RIGHT JOIN card ON person.cardID=card.id;+------+--------+--------+------+-----------+| id | name | cardID | id | name |+------+--------+--------+------+-----------+| 1 | 张三 | 1 | 1 | 饭卡 || NULL | NULL | NULL | 2 | 建行卡 || 2 | 李四 | 3 | 3 | 农行卡 || NULL | NULL | NULL | 4 | 工商卡 || NULL | NULL | NULL | 5 | 邮政卡 |+------+--------+--------+------+-----------+5 rows in set (0.00 sec)---右外连贯，会把左边表里所有的数据取出来，而右边表里的数据，如果有相等的，就显示进去，如果没有就补null。---------------------------------------------------------------------------------------------------------------------------------------------full join全外连贯---MySQL不反对全外连贯---只能通过union来实现mysql> SELECT * FROM person LEFT JOIN card ON person.cardID=card.id -> UNION -> SELECT * FROM person RIGHT JOIN card ON person.cardID=card.id;+------+--------+--------+------+-----------+| id | name | cardID | id | name |+------+--------+--------+------+-----------+| 1 | 张三 | 1 | 1 | 饭卡 || 2 | 李四 | 3 | 3 | 农行卡 || 3 | 王五 | 6 | NULL | NULL || NULL | NULL | NULL | 2 | 建行卡 || NULL | NULL | NULL | 4 | 工商卡 || NULL | NULL | NULL | 5 | 邮政卡 |+------+--------+--------+------+-----------+6 rows in set (0.00 sec)复制代码事务
MySQL事务
mysql 中，事务其实是一个最小的不可分割的工作单元。事务可能保障一个业务的完整性。
比方咱们的银行转账:a -> -100update user set money=money-100 where name='a' ;b -> +100update user set money=money+100 where name='b ';复制代码理论的程序中，如果只有一条语句执行胜利了，而另外一条没有执行胜利，就会呈现数据前后不统一。
多条sql语句，可能会有同时胜利的要求，要么就同时失败。
MySQL中如何管制事务
- MySQL是默认开启事务的（主动开启）
SELECT @@autocommit;-------------@@autocommit 1-------------复制代码默认事务开启的作用是什么?
当咱们去执行一个sql语句的时候，成果会立刻体现进去，且不能回滚。
先创立一个表：
create database bank;create table user( id int primary key, name varchar(20), money int);insert into user values( 1, 'a' ,1000);复制代码事务回滚：撤销sql语句执行成果。rollback;
mysql> rollback;Query OK, 0 rows affected (0.00 sec)mysql> select *from user;+----+------+-------+| id | name | money |+----+------+-------+| 1 | a | 1000 |+----+------+-------+1 row in set (0.00 sec)复制代码设置mysql主动提交为false。
mysql> set autocommit = 0;Query OK, 0 rows affected (0.00 sec)mysql> select @@autocommit;+--------------+| @@autocommit |+--------------+| 0 |+--------------+1 row in set (0.00 sec)复制代码下面的操作，敞开了mysql的主动提交(commit)。
咱们再插入一条记录：
mysql> insert into user values(2,'b',1000);Query OK, 1 row affected (0.00 sec)mysql> select * from user;+----+------+-------+| id | name | money |+----+------+-------+| 1 | a | 1000 || 2 | b | 1000 |+----+------+-------+2 rows in set (0.00 sec)mysql> rollback;Query OK, 0 rows affected (0.01 sec)mysql> select * from user;+----+------+-------+| id | name | money |+----+------+-------+| 1 | a | 1000 |+----+------+-------+1 row in set (0.00 sec)复制代码插入第二条数据其实没有插入到理论表中；
要想插入胜利，则进行如下操作：
再次插入mysql> insert into user values(2,'b',1000);Query OK, 1 row affected (0.00 sec)手动提交数据mysql> commit;Query OK, 0 rows affected (0.01 sec)撤销不能够（持久性）mysql> rollback;Query OK, 0 rows affected (0.00 sec)mysql> select * from user;+----+------+-------+| id | name | money |+----+------+-------+| 1 | a | 1000 || 2 | b | 1000 |+----+------+-------+2 rows in set (0.00 sec)复制代码总结：
主动提交：@@autocommit
手动提交：commit
事务回滚：rollback
例如转账操作：
mysql> update user set money=money-100 where name='a' ;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> update user set money=money+100 where name='b ';Query OK, 0 rows affected (0.00 sec)Rows matched: 0 Changed: 0 Warnings: 0mysql> select * from user;+----+------+-------+| id | name | money |+----+------+-------+| 1 | a | 900 || 2 | b | 1000 |+----+------+-------+2 rows in set (0.00 sec)mysql> rollback;Query OK, 0 rows affected (0.01 sec)mysql> select * from user;+----+------+-------+| id | name | money |+----+------+-------+| 1 | a | 1000 || 2 | b | 1000 |+----+------+-------+2 rows in set (0.00 sec)复制代码事务给咱们提供了一个返回的机会。
手动开启事务
两种形式begin; start transaction;复制代码mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> update user set money=money-100 where name='a';Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> update user set money=money+100 where name='b ';Query OK, 0 rows affected (0.00 sec)Rows matched: 0 Changed: 0 Warnings: 0mysql> select * from user;+----+------+-------+| id | name | money |+----+------+-------+| 1 | a | 800 || 2 | b | 1000 |+----+------+-------+2 rows in set (0.00 sec)mysql> rollback;Query OK, 0 rows affected (0.01 sec)mysql> select * from user;+----+------+-------+| id | name | money |+----+------+-------+| 1 | a | 900 || 2 | b | 1000 |+----+------+-------+2 rows in set (0.00 sec)复制代码事务开启之后，一旦commit提交，就不能回滚了（也就是以后的这个事务在提交的时候就完结了）。
事务的一些基本概念
事务的四大特色ACID
A 原子性：事务是最小的单位，不能够在宰割。
C 一致性：事务要求，同一事务中的sql语句，必须保障同时胜利或者同时失败。
I 隔离性：事务1和事务2之间是具备隔离性的。
D 持久性：事务一旦完结（commit，rollback），就不能够返回。
事务开启
1。批改默认提交set autocommit=0;
begin;start transaction;
事务手动提交
commit;
事务手动回滚
rollback;
事务的隔离性
1、read uncommitted;读未提交的 2、read committed;读曾经提交的3、repeatable read;能够反复读4、serializable;串行化mysql 默认隔离级别REPEATABLE-READ解释：如果有事务a,和事务b,a事务对数据进行操作，在操作的过程中，事务没有被提交，然而b 能够看见a操作的后果。eg：bank数据库的user表：insert into user values( 3,'小明',1000);insert into user values(4,'淘宝店' ,1000) ;如何查看数据库的隔离级别：零碎级别：select @@global.transaction_isolation;会话级别：select @transaction_isolation;mysql> select @@global.transaction_isolation;+--------------------------------+| @@global.transaction_isolation |+--------------------------------+| REPEATABLE-READ |+--------------------------------+1 row in set (0.00 sec)如何批改隔离级别：set global transaction isolation level read uncommitted;mysql> set global transaction isolation level read uncommitted;Query OK, 0 rows affected (0.00 sec)mysql> select @@global.transaction_isolation;+--------------------------------+| @@global.transaction_isolation |+--------------------------------+| READ-UNCOMMITTED |+--------------------------------+1 row in set (0.00 sec)复制代码转账:小明在淘宝店买鞋子:800块钱,
小明-》成都ATM
淘宝店-》广州ATM
start transaction;update user set money=money-800 where name='小明';update user set money=money+800 where name='淘宝店';mysql> select * from user;+----+-----------+-------+| id | name | money |+----+-----------+-------+| 1 | a | 900 || 2 | b | 1000 || 3 | 小明 | 200 || 4 | 淘宝店 | 1800 |+----+-----------+-------+4 rows in set (0.00 sec)复制代码如果两个不同的中央，都在进行操作，事务a 开启之后，他的数据能够被其余事务读取到。这样就会呈现(脏读)。
脏读：一个事务读到了另外一个事务没有提交的数据，就叫做脏读。
理论开发是不容许脏读呈现的。
不可反复读 尽管我只能读到另外一个事务提交的数据，但还是会呈现问题，就是读取同一个表的数据，发现前后不统一。不可反复读景象。
幻读事务a和事务b 同时操作一张表，事务a提交的数据，也不能被事务b读到，就能够造成幻读。
串行化 问题是，性能特差!!!READ—UNCOMMITTED > READ-COMMITTED > REPEATABLE-READ > SERIALIZABLE;
隔离级别越高，性能越差。
原文链接：https://juejin.cn/post/689819...