学习MySQL

有幸在本科期间学过SQL Server这门课程，但是由于年代实在过于久远，加之曾经为了装软件把家里的老电脑装崩了，从此之后数据库就给我留下了阴影，现在已经忘得差不多了。。。而MYSQL拥有安装轻便、使用简单的优点，还是先来学习一下这个吧！

笔记按照b站上的“一天学会MySQL”课程为顺序，一天学完。。。是不太可能的了

一、基础语法

1.登录数据库

在命令提示符—管理员中打开（CMD/Ctrl+R）

Users>mysql -uroot 用户名 

Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 4
Server version: 5.7.20 MySQL Community Server (GPL) Copyright (c) 2000, 2017, 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.

2.随时退出数据库

mysql>exit
bye

或

mysql>\q
bye

或者ctrl+z

mysql>^z
bye

语句中注意一定要写“；”

3.在数据库服务其中创建一个新的数据库（test）

mysql>create database test;
Query OK, 1 row affected (0.01 sec)

查看MYSQL已创建的所有的数据库：

show databases;

4.删除该某数据库（ppt）：

drop database ppt；

5.使用数据库服务器中的某一数据库（test）

mysql>use test;
Database changed

6.查看某数据库（test）中的所有数据表

mysql>show tables;
Empty set (0.00 sec)                     \*代表数据表为空

7.在某数据库下创建一个新数据表（pet）**

注意;一定要加入“，”，MYSQL对大小写不敏感

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.05 sec)

再次查看是否创建成功：

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| pet            |
+----------------+
1 row in set (0.00 sec)

mysql常用数据类型
支持多种类型，大致可以分为三类：数值、日期/时间、字符串（字符）类型。

为了不破坏教程的完整性，详情可见mysql的常用数据类型

7.删除数据表（以表user4为例）：

mysql> drop table user4;
Query OK, 0 rows affected (0.01 sec)

8.查看创建好的数数据表（table名为pet）的结构

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.02 sec)

Field 字段
Type 字段类型
Null 是否允许为空
Key 表示约束相关
Default 默认值为空

9.查看数据表（pet）内的记录**

mysql> select * from pet;
Empty set (0.00 sec)

9.为已有数据表（student）增加属性（增加列）
eg. sbirthday为属性名，datetime为属性类型

 ALTER TABLE **student** ADD COLUMN **sbirthday** **datetime**;

10.向数据表（pet）中添加数据记录**

mysql> insert into pet                                                           \*注意这里没有“；”，否则可能会报错
    -> values ('Puffball','Diane','hamster','f','1999-03-30',null);
Query OK, 1 row affected (0.01 sec)                                              \*创建成功

查询：

mysql> select  * from pet;
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Puffball | Diane | hamster | f    | 1999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)

多次添加数据，需要多次重复上述两条指令
再次查看：

11.删除数据表（pet）中的数据**

mysql> delete from pet where name='ufl';
Query OK, 1 row affected (0.02 sec)

删除后：

（后续删除了最后重复的一条数据）

12.修改数据表（pet）中的数据**

mysql> update set name='所有者' where owner='Harold';
Query OK, 1 row affected (0.01 sec)                             

13.修改数据表名称（从user7→user6）**

 mysql>alter table user7 rename to user6;
Query OK, 0 rows affected (0.02 sec)

小总结：

• 增加 insert
• 删除 delete
• 修改 unpdate
• 查询 select

二、约束

1.主键约束 (primary key)

能够唯一确定一张表中的一条记录，也就是我们通过对某个字段添加约束，就可以使该字段不重复且不为空。

(1)在某一属性上试用 primary key
在这里，我们创建一个新的数据表 user 供使用

mysql> create table user(
    -> id int primary key,
    -> name varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into user values('1','zhangsan');
Query OK, 1 row affected (0.01 sec)

再次输入：不可

mysql>  insert into user values('1','zhangsan');
ERROR 1062 (23000):                                              \*Duplicateentry '1' fro key 'PRIMARY'  ————因为之前在id部分的设置，不可提交重复的内容

id输入null：不可

mysql>  insert into user values(null,'zhangsan');
ERROR 1048 (23000): Unknown error 1048                           \*  Column 'id' cannot be null                

更改id：可

mysql>  insert into user values('2','zhangsan');
Query OK, 1 row affected (0.01 sec)                                           

(2)限定于多种属性上：联合主键
——只要联合的主键值中某一个不重复即可通过（类似于非门+）；但联合主键的任一字段不可为空

eg.先创建一个新的表 user2；

mysql> create table user2(
    -> id int,
    -> name varchar(20),
    -> password varchar(20),
    -> primary key(id,name));                      \*联合主键
Query OK, 0 rows affected (0.03 sec)

插入一条信息：

mysql> insert into user2 values(1,'zhangsan','123');
Query OK, 1 row affected (0.01 sec)

重复插入同样的信息，报错

 insert into user2 values(1,'zhangsan','123');
 ERROR 1062 (23000):

改变某一（id）信息，重复插入，成功（说明只要一个成立即可）

mysql>  insert into user2 values(2,'zhangsan','123');
Query OK, 1 row affected (0.01 sec)

设置id为null：

mysql> insert into user2 values(null,'wanger','123');
ERROR 1048 (23000): Unknown error 1048                                       \*ERROR 1048 (23000):Column 'id' cannot be null 任一字段不可为空

2.自增约束

与 1.主键约束primary key 配合使用，会自动管控相应的值，使其自动增长

eg.
创建新的表user3

mysql> create table user3(
    -> id int primary key auto_increment,
    -> name varchar(20));
Query OK, 0 rows affected (0.02 sec)

在指定字段 name上加入信息：

mysql> insert into user3 (name) values('zhangsan');
Query OK, 1 row affected (0.01 sec)

此时自动生成了id

再次插入一个name：

mysql> insert into user3 (name) values('zhangsan');
Query OK, 1 row affected (0.01 sec)

此时自动产生id=2

——倘若没有在创立表格时添加约束，接下来该如何处理？

eg.创建新表user4，含id，name

mysql>  create table user4(
    -> id int,
    -> name varchar(20)
    -> );

在表（user4）中修改表结构，添加对某一属性（id）的主键约束：

mysql> alter table user4 add primary key(id);
 Query OK, 0 rows affected (0.04 sec)

如果需要对多个属性添加约束：

mysql>  alter table user4 add primary key(id,name);
Query OK, 0 rows affected (0.04 sec)

Modify：或者以修改表（user4）中的属性（以修改id属性为例）添加约束

mysql> alter table user4 modify id int primary key;
Query OK, 0 rows affected (0.03 sec)

查看修改后的情况：（desc是describe命令的缩写）

mysql> desc user4;

删除主键约束：

mysql> alter table user4 drop primary key;
Query OK, 0 rows affected (0.04 sec)

所有主键约束均被删除：

3.唯一约束

约束修饰的字段的值不能重复

eg.创建一个仅id和name属性的数据表（user5）

Unique添加唯一约束：

mysql> alter table user5 add unique(name);
Query OK, 0 rows affected (0.02 sec)

或MODIFY添加：

mysql> alter table user5 modify name varchar(20) unique;

直接添加：

mysql>  create table user7(
    -> id int,
    -> name varchar(20) unique
    -> );

或

mysql>  create table user7(
    -> id int,
    -> name varchar(20) ，
       -> unique(name)
    -> );

效果：插入数据后，再次插入已设置属性（name）下相同数据时报错

mysql> insert into user5 values(1,'zhagnsan');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user5 values(2,'zhagnsan');
ERROR 1062 (23000):

若同时在为多种属性（id,name）添加唯一约束

mysql> create table user8(
    -> id int,
    -> name varchar(20),
    -> unique(id,name)                          \*两种中有一种属性满足唯一约束条件即可通过，类似于“或”
    -> );

key标识变为MUL

eg.插入数据

mysql> insert into user8 values(1,'zhangsan');
Query OK, 1 row affected (0.01 sec)

mysql>  insert into user8 values(1,'zhangsan');                           \*重新输入相同的id和name
ERROR 1062 (23000):

mysql> insert into user8 values(2,'zhangsan');                            \*改变id
Query OK, 1 row affected (0.01 sec)

mysql>  insert into user8 values(1,'zhangsn');                            \*改变name
Query OK, 1 row affected (0.01 sec)

删除唯一约束：

mysql> alter table user8 drop index id;                                        \*在这里，MUL与id一行
Query OK, 0 rows affected (0.02 sec)

4.NOT NULL非空约束

eg.建立一个在name上具有not null属性的数据表user9

mysql> create table user9(
    -> id int,
    -> name varchar(20) not null
    -> );
Query OK, 0 rows affected (0.02 sec)

NULL栏产生了标记（name不能为空）：

尝试添加数据：

mysql> insert into user9(id) values(1);                      \*name为空，不符合设定
ERROR 1364 (HY000):

mysql> insert into user9 values(1,'zhangsan');                                           
Query OK, 1 row affected (0.01 sec)

mysql> insert into user9(name) values('lisi');               \*id为空，不影响插入数据
Query OK, 1 row affected (0.01 sec)

5.默认约束

插入字段值时，若未传值，就会使用默认值
eg.建立一个属性上具有默认约束的数据表user10

mysql> create table user10(
    -> id int,
    -> name varchar(20),
    -> age int default 10
    ->   );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into user10 (id,name) values(1,'zhangsan');    \*未指定age，自动指定默认值
Query OK, 1 row affected (0.01 sec)

mysql> insert into user10 (id,name,age) values(2,'lisi',19); \*传值age，则不会用到默认值
Query OK, 1 row affected (0.01 sec)

6.外键约束

• 涉及两种表：父表、子表（主表、副表）

eg.建立两种表并进行关联
父表：

mysql> create table classes(
    -> id int primary key,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.02 sec)

子表：

mysql>  create table students(
    ->  id int primary key,
    ->  name varchar(20),
    -> class_id int,
    -> foreign key(class_id) references classes(id)      \*表中的class_id属性值必须在classes表内的id
    -> );
Query OK, 0 rows affected (0.02 sec)

子表中KEY类型出现MUL：

母表插入多条数据：

mysql> insert into classes values(1,'一班');
Query OK, 1 row affected (0.01 sec)

mysql>  insert into classes values(2,'二班');
Query OK, 1 row affected (0.01 sec)

mysql> insert into classes values(3,'三班');
Query OK, 1 row affected (0.01 sec)

mysql>  insert into classes values(4,'四班');
Query OK, 1 row affected (0.01 sec)

子表：

mysql> insert into students values(1001,'张三',1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into students values(1002,'张三',2);
Query OK, 1 row affected (0.01 sec)

mysql> insert into students values(1003,'张三',3);
Query OK, 1 row affected (0.01 sec)

mysql> insert into students values(1004,'张三',4);
Query OK, 1 row affected (0.01 sec)

mysql>  insert into students values(1005,'lisi',5);   \*出现错误，表classes中没有id=5
ERROR 1452 (23000): Unknown error 1452

• 主表classes中没有的数据值，在副表中，是不可以使用的。
• 若母表已经被引用，则该记录不可以被删除
  eg.

mysql> delete from calsses where id=4；              \*若主表子表被引用class_id=4，则母表的id=4不能删除
ERROR 1146 (42S02): Unknown error 1146

三、数据表设计

数据库的三大设计范式
范式根据实际操作来选定

1. 第一范式（1NF）

数据表中的所有字段都是不可分割的原子值
eg.该例子中（1）的字段值还可以继续拆分，不满足第一范式
address-》cuntry | privence | city | details

mysql> create table student2(
    -> id int primary key,
    -> name varchar(20),
    -> address varchar(30)
    -> );
    
mysql>  insert into student2 values(1,'张三','中国四川省成都市武侯区武侯大道100号');
Query OK, 1 row affected (0.01 sec)

mysql>  insert into student2 values(2,'李四','中国四川省成都市武侯区京城大道200号');
Query OK, 1 row affected (0.01 sec)

mysql>  insert into student2 values(3,'李五','中国四川省成都市武侯区天府大道90号');               \*这里的address值还可以继续拆分
Query OK, 1 row affected (0.01 sec)

设计的越详细，对于某些实际操作有益，但可能会增加一些复杂度

2. 第二范式（2NF）

必须在满足第一范式的前提下。
第二范式要求，除主键外的每一列都必须完全依赖于主键
如果要出现不完全依赖，只可能发生在联合主键的情况下。

eg.订单表.(1)除主键以外的其他列，只依赖于主键的部分字段。

mysql>   create table myorder(
    -> product_id int,
    ->  customer_id int,
    ->  product_name varchar(20),
    -> customer_name varchar(20),
    -> primary key(product_id,customer_id)
    -> );

进行拆分，以满足第二范式：

mysql>   create table myorder(
    -> product_id int,
    ->  customer_id int,
    ->  product_name varchar(20),
    -> customer_name varchar(20),
    -> primary key(product_id,customer_id)
    -> );
    
mysql>   create table myorder(
    -> order_id int  primary key
    ->  product_id int，
    -> customer_id int
   );
mysql>   create table product(
   -> id int primary key;
   -> name varchar(20),
    );

mysql>   create table customer(
   -> id int primary key;
   -> name varchar(20),
    );

3. 第三范式（3NF）

必须先满足第二范式，除开主键列的其他列之间不能有传递依赖关系。
eg.

create table myorder(
order_id int primary key,
product_id int,
customer_id int,
customer_name varchar(20);        \*customer id和name有传递依赖关系，所以该表不代表3NF
);

create table myorder(
order_id int primary key,
product_id int,
customer_id int
);
create table customer(
id int primary key,
name varchar(20),
phone varchar(15)
);

四、查询练习

eg. 学生表、课程表、成绩表、教师表
注意联合主键的位置

1.查询表（student）中的某些列（sname，ssex，class）：

其它列不会显示

mysql> select sname, ssex, class from student;

2.DISTINCT查询表（student）中的某属性（class）下不重复的列

eg.查询不同班级并列出

mysql> select distinct class from student;
+-------+
| class |
+-------+
| 95033 |
| 95031 |
+-------+
2 rows in set (0.01 sec)

3. 查询表中数字在101~102间的所有记录

eg.BETWEEN 从表(student)中查询属性值(class)介于某范围（95032≤class≤95033）之间的值（在这里class的值为varchar（20）也是适用的）

mysql> select *from  student  where  class  between  95032  and  95033 ;

·还可以直接使用运算符比较（表score，参数degree为int形式，范围80≤degree≤100）

mysql>  select * from score where degree>80 and degree <100;

如果只需要显示某些属性，可以这么写：

mysql>  select sno,degree from score where degree>80 and degree <100;

4. 查询在特定属性值情况下的所有数据

eg.查询表（score）中属性（degree）为某些值（85,86,88）的结果

mysql>  select * from score where degree in(85,86,88);

5.查询表中不满足不同属性下某值的结果

eg.查询表（student）中属性1（class）和属性2（sex）为x（‘95031’） 和y （‘女’）的记录

mysql>   select *  from student where class='95031' or sex='女'；

6.以某属性为准按一定顺序序查询数据表中所有记录

eg.降序查询以class属性为准的表student的数据

mysql>  select * from student order by class desc;

eg.升序：

select * from student order by class asc;

或者省略asc：

mysql>  select * from student order by class;

7.以A属性升序，B属性降序查询score表中的所有记录

eg.以属性cno升序，且degreee降序，查询表score的所有记录

mysql>  select * from score order by cno as,degree desc;

8. COUNT查询某属性下的记录个数

• 需用到统计 count
  eg.符合统计表student中属性（class）为值A（‘95031’）的数据数

mysql>  select  count(*) from student where class='95031';

9. 查看某属性数据最值的数据信息

eg.查询表（score）中的某最值属性（degree的最大值）对应的其它属性信息（学生学号sno）和（课程号cno）

mysql> select sno,cno from score where degree=(select max(degree) from score);

该指令属于子查询（1.找到最高分 2.找最高分的sno 和cno）
·有时可能会忘记表（score）所设定的属性的类别，可以通过相应指令来查看原始代码：

mysql> show create table score;

结果:

10. 排序的做法

eg. 显示表（score）中的某几个属性（sno cno degree三种）并按照某一属性（degree）的升序排列

mysql> select sno，cno，degree from score order by degree;

eg.显示倒序第一条
limit（x，y）y——查询多少条，x——从第x条开始（起始号为0）

mysql> select sno，cno，degree from score order by degree desc limit 0,1;

11. 计算表中限定条件下某属性值的平均值

eg.从表（score）中，在特定条件下（属性cno为=‘3-105’）查询属性（degree）的平均值

select avg（degree）from score where cno=‘3-105’；

12. 计算表中限定条件下某属性值的平均值（不同条件同时显示）

eg.显示在不同条件限制下（属性cno包含的所有属性值），表（score）中某属性（degreee）的平均值

mysql>select cno，avg（degree）from score group by cno’；

13.查询表中特定条件的平均数

复杂问题分段操作：
eg. 找出表（score）中至少有2名学生选修，并以3开头的课程（cno）的平均分数（degree），并统计人数

mysql>select cno,avg(degree),count(*) from score group by cno
->having count (cno)>=2 and cno like 3%;

14.同时查询多个表中的某些特定属性对应的数据

eg.从多个表（student、score）中显示多属性（sname| cno，degree）对应的数据

mysql>select snme,cno,degree from student,score
->where student.sno=score.sno;                            \*有主键

eg.查询所有学生的sname属性（表student）,cname属性（表course）和degree属性（表score）列
并将sname列命名为stu_name

mysql>select sname as stu_name, cname ,degree from student,course,score
->where student.sno=score.sno and course.cno=score.cno;

eg. 查询“95031”班（class限定）的学生（sno）每门课（score数据表）的分数

mysql>select * from score where sno in (select sno from student where class=‘95031’)                           \*数据表score的含有sutdent的信息
->

eg. 查询“95031”班（class限定）的学生（sno）每门课（score数据表）的平均分

select cno,avg(degree) from score where sno in (select sno from student where class=‘95031’)  group by  cno;                    

WHERE ·GROUPBY ·AVG · df·

eg.查询选修课程“3-105”的成绩高于“109“号同学"3-105"课程的所有记录

select * from score where cno='3-105' and degree> (select degree from score where sno='109' and cno='3-105');               

eg.在表（student）中查询学号（sno）为108,101的同学的出生日期（sbirthday）的年份

select year（sbirthday） from student where sno in（108,101）

eg.在表（student）中查询学号（sno）为108,101的同学的出生日期（sbirthday）的年份相同的所有人的信息
```
select * from student where year (sbirthday) in (select year（sbirthday） from student where sno in（108,101));

eg. 查询选修课某课程(cno)的同学人数多于5人的教师姓名(tname)

select cno from score group by cno having count(*)>5

select* from teacher;
select tno from course where cno  in (select cno from score group by cno having count(*)>5);

select tname from teacher in(select tno from course where tno  in (select tno from course where cno  in (select cno from score group by cno having count(*)>5));

eg.查询出“计算机系“ 和"电子工程系"且不同职称的教师的tname，prof（并集-交集）
——Union求并集

select prof from teacher where depart="电子工程系"；

select* fromteacher where depart=“计算机系”and prof not in（select prof from teacher where depart="电子工程系"）     \*无分号
union
select* fromteacher where depart=“电子工程系" and prof not in（select prof from teacher where depart=“计算机系”）；

eg.查询选修编号为“3-105”课程且成绩 至少大于一个 选修编号为“3-245”的同学的所有信息，并按Degree从高到低次序排序

• 至少一种\ ANY

select * from score where cno='3-105' and degree any(select degree from score where cno=‘3-245’)
->order by degree desc;

eg.找出取得课程"3-105"的成绩，高于所有选修编号“3-245”成绩的同学的所有信息

• 且\ ALL的使用

select * from score
->where cno="3-105" 
->and degree>all(select from score where cno='345');

eg.查询教师、同学的name sex 和birthday

select tname as name,tsex as sex,tbithday as bithday from teacher
->union
->select sname ,ssex,sbirhtday from student;           \*两张表合一后表头名按照tanme，tsex，tbithday排列（第二条指令默认与第一条同），需改变名称

eg.？？？查询成绩<该课程平均成绩的同学的成绩表

cerate temporary table b select * from a;                                \*？？？？？？？
select * from score score where degree<(select avg(degree) from score b where a.cno=b.cno);

eg.所有任课老师的tnme 和deoart

select tname depart from teacher where tno in (select tno from course);

eg.查询至少有2名男生的班号

select class from student where ssex='男' group by class having count(*)>1;`

eg.查询student表中不姓“王的同学记录”

• 模糊查询 NOT LIKE 用到了正则表达式

select * from student where sname not like ''王%;                      \*   %通配符，表示任意字符，表示不是比较的重点

eg.查询student表中每个学生的姓名（sname）和年龄（表中仅有date属性）
select year(now())；

• NOW（）

select snme，year（now（））-year（sirthday） as ‘年龄’ from student；

eg.查询student表中最大和最小的sbirthday日期值

• MAX MIN

select max(sbiethday) as '最大', min(sbiethday) as '最小'from student；  \*但是这里的max和min是按照数字大小排列的

eg.以班号（属性class）和年龄（属性sbirthday）从大到小的顺序查询表（student）中的全部记录

select * from student order by class desc,sbirthday;                \*属性class相比sbirthday的优先级更高

eg.查询与Alice性别且同班同学的sname。

sleect * from student where 
->ssex=（select ssex from student where name=‘Alice’）
->and class=（select class from student where name=‘Alice’）；

eg.查询所有选修“计算机导论”课程（course表）的“男”同学（student）的的成绩表（score）

select * fromscore
->where cno=（select cno from course where cname=‘计算机导论’）
->and sno in (slect sno from student where ssex='男')；

eg.查询所有同学的sno、cno和grade

• 新建一个表格，用来存放成绩等级

mysql> create table grade(
    -> low int(3),
    -> upp int(3),
    -> grade char(1)
    -> );
mysql> insert into grade values(90,100,'A');
Query OK, 1 row affected (0.02 sec)

mysql> insert into grade values(80,89,'B');
Query OK, 1 row affected (0.01 sec)

mysql> insert into grade values(70,79,'C');
Query OK, 1 row affected (0.01 sec)

mysql> insert into grade values(60,69,'D');
Query OK, 1 row affected (0.01 sec)

mysql> insert into grade values(0,59,'E');
Query OK, 1 row affected (0.01 sec)

• 各表进行组合

mysql>select sno cno grade from score,grade where degree between low and upp;

五、连接查询

eg.建立两张表

内连接 inner join / join

将多表中有关联的字段进行等值连接
（表中数据通过某个字段相对，查询出相关记录数据）

mysql>select * from person inner join card on person.cardId=card.id;
mysql>select * from person inner join card on person.cardId=card.id;     \*和上一条效果相同

![在这里插入图片描述](https://img-blog.csdnimg.cn/20201225204434848.png)

外连接

1. 左连接 left join / left outer join
   

左外连接，会将左侧表中所有的数据提取出，而右侧数据，若相等则显示；不相等则补NULL

mysql>select * from person left join card on person.cardId=card.id;
mysql>select * from person left outer join card on person.cardId=card.id;          \*和上一条效果相同

2. 右连接 irght join right / outer join
   

mysql>select * from person right join card on person.cardId=card.id;
mysql>select * from person right outer join card on person.cardId=card.id;          \*和上一条效果相同

3. 完全外连接 fulll join / full outer join
   

mysql不支持full join

mysql>select * from person full join card on person.cardId=card.id;
mysql>select * from person full outer join card on person.cardId=card.id;          \*和上一条效果相同

ERROR 1054 (42S22): Unknown colum 'person.cardId' in 'on clause''

六、事务

mysql中，事务是一个最小的不可风格的工作单元。事务能够保证一个业务的完整性。

eg.银行转账：
a -> -100
update user set money=money-100 where name =‘a’;
b->+100
update user set money=money+100 where name =‘a’;

• 而在实际的程序中，如果只有一条语句执行成功，而另一条没执行成功时，可能会发生问题
• 出现数据前后不一致
  update user set money=money-100 where name =‘a’;
  update user set money=money+100 where name =‘a’;
• 多条sql语句，可能会有同时成功的要求，要么就同时失败。

事务的四大特征：
A. 原子性：事务是最小的单位，不可再分
C. 一致性：食物要求 ，同一事物中的sql语句，必须同时保证同时成功或者同时失败。
I. 隔离性：事务1 和 事务2 之间是具有隔离性的。
D. 持久性：事务一旦结束（commit，rollback），就不可返回

MYSQL如何控制事务

建立一个表格：

       1.mysql默认是开启事务（自动提交）

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.01 sec)

• 作用：当执行一个sql语句时，效果会立即体现出来，且不可回滚
  回滚：ROLLBACK

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

而进行该操作之后表并未产生变化（插入操作并未撤销）

而关闭mysql的自动提交（AUYOCOMMIT=0）后：

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

再次添加数据并使用ROLLBACK：
添加数据的操作（其实未提交，是临时效果）被撤销，提供了返回的机会：
若需要保留数据，在insert完成后使用COMMIT：

mysql> commit;

再次使用rollback，结果不变（持久性）

再次调回commit=1，自动提交

（1）手动开启事务后的信息撤回：采用 BEGIN可以使rollback生效

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)

mysql> update user set money=money+100 where name='b';
Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

撤回前后：

（2）手动开启事务后的信息撤回：采用START TRANSACTION也可以有以上效果

mysql> start transaction;
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)

mysql> update user set money=money+100 where name='b';
Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

但若在rollback使用前输入commit，则不会触发rollback效果

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

事务的隔离性

1. read uncommitted；读未提交的

• 若在两个不同地方，都在进行操作，若事务a开启之后，它的数据可以被其他事物读取到 这样会出现——脏读

• 脏读：一个事务读到了另外一个事务未提交的数据，就叫脏读，这在实际开发中是不允许出现的。

如果有事务a和事务b：

（1）a事务对数据进行操作，在操作的过程中，事务没有被提交，但b可以看到a的操作结果

·查看数据库的隔离级别？

• 对于mysql 8.0：

mysql>select @@global.transaction_isolation;
mysql> select @@transaction_isolation;

• 对于mysql 5.x：

mysql> @@global.transaction_isolation；
mysql> @@tx_isolation;

默认隔离级别：

·修改数据库的隔离级别

set global transaction isolation level read uncommitted;

随后隔离性变为READ-UNCOMMITTED：

mysql>  select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| READ-UNCOMMITTED               |
+--------------------------------+
1 row in set (0.00 sec)

eg.转账：小明在淘宝店买商品：800元
小明→成都 ATM
淘宝店→ 广州AMT

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update user set money=money-800 where name='小明';
Query OK, 1 row affected (0.00 sec)

mysql>  update user set money=money+800 where name='淘宝店';
Query OK, 1 row affected (0.00 sec)

mysql> rollback;                                \*而另一方进行rollback操作，则钱没了
Query OK, 0 rows affected (0.00 sec)

2. read committed;读已提交的

• 将系统转为read committed模式

mysql> set global transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

 事务a和事务b同时操作一张表，事务a提交的数据，不能被事务b读取到，就会造成幻读。

eg.两方共同使用数据库，虽然只能读到提交的数据，但还是会出现问题：另一方发现前后不一致

3. repeatable read; 可以重复读

eg.两方共用一个数据，前后插入信息并提交：

A:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql>insert into user values(6,'d',1000);
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

B：

mysql>insert into user values(6,'d',1000);
ERROR 1062（23000）：Duplicate entry '6' for key 'primary';                  \*出现幻读现象

4. serializable;单行化

串行化带来的问题：性能差

set global transaction isolation level serializable;
select @@global.transaction_isolation;

eg.两方共用一个数据，前后插入信息并提交：

A:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql>insert into user values(7,'f',100);
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

B：

mysql> select * from user;                        \*看到了A更新后的表格
mysql>insert into user values(8,'oo',1000);



                                                            \命令执行时卡住

操作串行化，两端不可同时进行写入操作，进入等待状态
A提交数据
A:

commit;

B立刻弹出结果
B：

Query OK, 1 row affected (7.76 sec)                                \*等待commit后

若超时则会弹出。此时B端需要重新插入：

ERROR 1064（4200）：you have an error in your SQL
syntax; check the manual that corresponds to your mysql server version fo the righr syntax to use near 'mysql'     \*超时

mysql>insert into user values(8,'oo',1000);
Query OK, 1 row affected (0.01 sec)                                

• 当表user被另外一个实务操作时，其它事务中的写作错做，是不可以进行的。
• 进入排队状态（串行化），直到A事务结束之后，B的写入操作才能被执行（但需要再没有等待超时的情况下）

性能对比	>	隔离级别高,	性能减弱（问题多）
READ-UNCIMMUTTED	READ-COMMITTED	REPEATABLE-READ	SWRIALIZABLE

课程来自一天学会MySQL数据库？