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 test1
Database 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: 0
mysql> 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: 0
mysql> 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: 0
mysql> 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: 0
mysql> 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: 0
mysql> 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: 0
mysql> 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，cardID
1 张三 1
2 李四 3
3 王五 6
card 表
id,name
1 饭卡
2 建行卡
3 农行卡
4 工商卡
5 邮政卡
并没有创立外键
--inner join 查问（内连贯）SELECT * from person INNER JOIN card ON person.cardID=card.id;
id name cardid id name
1 张三 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 -> -100
update user set money=money-100 where name='a' ;
b -> +100
update 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: 0
mysql> update user set money=money+100 where name='b';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> 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: 0
mysql> update user set money=money+100 where name='b';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> 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…