MySQL 的学习笔记

MySQL 的学习笔记

Chapter One MySQL的相关终端操作

1. Mac下MySQL服务的启动、关闭命令

在系统偏好设置中找到MySQL图标，进行MySQL服务的启动和关闭；

曾试图用命令行对MySQL服务的启动、关闭进行操作，发现报错：

命令如下：

sudo /usr/local/mysql/support-files/mysql.server start

sudo /usr/local/mysql/support-files/mysql.server stop

sudo /usr/local/mysql/support-files/mysql.server restart

出现报错： ERROR! MySQL server PID file could not be found!

搜索之后发现问题出在：未安装MySQLStartupItem.pkg——该包可以使Mysql服务在电脑启动时，自启动；安装在/Library/StartupItems/MySQL/ 目录下，若不想系统启动时就自启动MySQL服务，就不必安装啦。图形化界面启动关闭MySQL服务也挺方便的。

2. 关于Windows下的MySQL服务的启动、关闭命令

• 方式一：同Mac一样，找到图形化管理界面进行MySQL服务的启动、关闭

​ 这里在“ 计算机——>右键管理——>服务里，找到自己安装的MySQL服务名

• 方式二：通过cmd打开命令行界面，输入net start 服务名 e.g. ： net start mysql0815；

​ 还有net stop 服务名 / net restart 服务名

3.关于命令行登陆及退出MySQL的相关命令

• 在Mac和Windows下的命令行登陆MySQL的命令是一致的：

/usr/local/mysql/bin/ mysql [-h 主机名 -P 端口号] -u 用户名 -p密码

e.g. : mysql -h localhost -P 3306 -u root -p123456

• 退出MySQL使用 exit;

4. 展示、选择数据库和展示数据表

• show databases；
• use + 数据库名;
• show tables;

Chapter Two MySQL的基本操作

1. 以命令的形式创建数据库数据表

• CREATE DATABASE + DATABASE_NAME

  e.g. :CREATE DATABASE school_manager;

注意：数据库名，数据表名使用小写英文以及下划线组成,尽量说明是那个应用或者系统在使用的。（例如：user_info)

• CREATE TABLE TBALE_NAME(

  数据字段1 数据类型及范围，

  数据字段2 数据类型及范围，

  数据字段3 数据类型及范围);

  e.g. : CREATE TABLE student_info(

  ​ std_id varchar(20),

  ​ std_name varchar(20),

  ​ std_gender varchar(10));

  可以通过关键词DESC（describe的缩写）来查看所创建的数据表的结构

  DESC student_info;

2.MySQL常用数据类型及相应的存储范围

数值类型

类型	大小	范围（有符号）	范围（无符号）
TINYINT	1字节	(-27，27-1)	(0，2^8-1)
SAMLLINT	2字节	(-215，215-1)	(0，2^16-1)
MEDIUMINT	3字节	(-223,223-1)	(0，2^24-1)
INT/INTEGER	4字节	(-231,231-1)	(0，2^32-1)
BIGINT	8字节	(-263,263-1)	(0，2^32-1)
FLOAT	4字节
DOUBLE	8字节

日期和时间类型

类型	范围	格式
DATE	1000-01-01～9999-12-31	YYYY-MM-DD
TIME	-838:59:59～838-59-59	HH:MM:SS
TEAR	1901~2155	YYYY
DATETIME	1000-01-01 00:00:00~ 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS
TIMESTAMP	1970-01-01 00:00:00～ 2038-01-19 03 :14:07 具有自动初始化和自动更新功能	YYYY-MM-DD HH：MM：SS

字符串类型

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

数据类型如何选择：

如期类型 按照格式进行选择；

数值和字符串类型 按照大小进行选择；

2.通过图形化软件进行数据库数据表的创建

安装Navicat并激活详情自行百度

3.INSERT 向数据表中插入记录

INSERT INTO table_name VALUES(’ ‘,’ ‘,’ ');

e.g.: INSERT INTO student_info VALUES(12,‘章三’,‘女’)；

4.DELETE 从数据表中删除记录

DELETE FROM table_name where …;

e.g. : DELETE FROM student_info where std_id = 12;

5.UPDATE 修改更新数据表中的记录

UPDATE table_name set column_name = … WHERE …;

e.g. : UPDATE student_info set std_name = “张三” WHERE std_id = 12；

6.SELECT 查询数据表中的记录

SELECT column_name FROM table_name [WHERE …];

e.g. : SELECT std_name,std_gender FROM student_info WHERE std_id = 12;

Chapter Three MySQL建表约束

1.主键约束（PRIMARY KEY）

主键——能够唯一确定表中的一条记录，即，通过给某个字段添加主键约束，就可以使得该字段不重复不为空；

e.g.: CREATE TABLE class_info(

​ c_id int PRIMARY KEY,

​ c_name varchar(20),

​ t_id int);

这里指定了c_id为主键，则在插入数据时，c_id字段不能重复且不能为空；

2.联合主键

CREATE TABLE class_info(

​ c_id int,

​ c_name varchar(20),

​ t_id int，

​ PRIMARY KEY(c_id,c_name));

这里指定了c_id，c_name为主键，则在插入数据时，c_id，c_name字段不能重复且不能为空；

注意：但是联合主键，只要保证c_id和c_name的组合是唯一就可以。

例如下表中的1，3虽然c_id重复了，但是c_name不同，则这条记录是唯一的。

序号	c_id	c_name
1	1	Maths
2	2	Chinese
3	1	English

1.1.自增约束（auto_increament）

CREATE TABLE class_info(

​ c_id int PRIMARY KEY auto_increament,

​ c_name varchar(20),

​ t_id int);

此时插入数据只需要指定非自增的列名

INSERT INTO class_info (c_name,t_id) VALUES(‘MUSIC’,321);

1.2.在创建表是忘记添加主键（ALTER）

• 使用ALTER+ADD添加主键约束：

ALTER TABLE class_info ADD PRIMARY KEY(c_id);

• 或者使用ALTER+MODIFY修改字段，添加主键约束：

ALTER TABLE class_info MODIFY c_id int PRIMARY KEY;

1.3.删除主键（DROP）

通过ALTER+DROP删除主键约束：

ALTER TABLE class_info DROP PRIMARY KEY；

1.4.唯一索引（UNIQUE KEY）

1.4.1 在建表的时候添加UNIQUE唯一索引约束

UNIQUE唯一约束修饰的字段值不可以重复

CREATE TABLE teacher_info(

​ t_id int unique,

​ t_name varchar(20),

​ t_gender varchar(10));

或者：

CREATE TABLE teacher_info(

​ t_id int,

​ t_name varchar(20),

​ t_gender varchar(10),

​ unique(t_id,t_name));

1.4.2 删除UNIQUE约束——ALTER+DROP INDEX

e.g.: ALTER TABLE teacher_info DROP INDEX name;

1.4.3 若在建表的时候未添加UNIQUE约束

• 使用ALTER+ADD添加唯一索引约束：

ALTER TABLE teacher_info ADD UNIQUE INDEX (t_id);

• 或者使用ALTER+MODIFY修改字段，添加唯一索引约束：

ALTER TABLE teacher_info MODIFY t_id int UNIQUE;

1.5 非空约束（NOT NULL）

NOT NULL 修饰字段不能为空；

1.5.1 建表时添加NOT NULL 约束

CREATE TABLE employee_info(

​ e_id int PRIMARY KEY NOT NULL,

​ e_name varchar(20) NOT NULL,

​ e_salary int);

1.5.2 建表时未添加NOT NULL约束

• 使用ALTER+ADD添加NOT NULL约束：

ALTER TABLE employee_info ADD NOT BULL(e_id);

• 或者使用ALTER+MODIFY修改字段，NOT NULL约束：

ALTER TABLE employee_info MODIFY e_id int NOT NULL;

1.6默认值约束（DEFAULT）

在插入字段值是若没有传入字段值，默认使用的值

1.6.1 建表时添加DEFAULT约束

CREATE TABLE employee_info(

​ e_id int PRIMARY KEY NOT NULL,

​ e_name varchar(20) NOT NULL,

​ e_salary int DEFAULT 0);

1.6.2 建表时未添加DEFAULT约束

• 使用ALTER+ADD添加DEFUALT约束：

ALTER TABLE employee_info ADD NOT BULL(e_salary);

• 或者使用ALTER+MODIFY修改字段，添加DEFAULT约束：

ALTER TABLE employee_info MODIFY e_salary int NOT NULL DEFAULT 0;

1.7 外键约束（FOREIGN KEY）

涉及到两张表：主表和副表

dept_manager是主表（夫表），employee_info是副表（子表）

CREATE TABLE dept_manager(

​ dm_id int PRIMARY KEY NOT NULL,

​ dm_name varchar(20) NOT NULL,

​ dm_member int);

CREATE TABLE employee_info(

​ e_id int PRIMARY KEY NOT NULL,

​ e_name varchar(20) NOT NULL,

​ e_salary int DEFAULT 0,

​ dm_id int NOT NULL

​ FOREIGN KEY(dm_id) references dept_manager(dm_id));

注意：

1. 主表dept_manager中没有的数据值，在副表中是不可以使用的；

2. 有外键约束下插入数据和删除数据是存在约束的；

3. 主表中的记录被副表引用，是不可以通过副表被删除的。

   e.g. DELETE FROM employee_info WHERE e_id = 1001;

Chapter Four 数据库设计的三大范式

1. 第一范式（1NF）

字段值还可以继续拆分的，就不满足第一范式；

范式，设计的越详细，对于某些实际操作可能更好，但并不一定都是好处

**例如：**下方的student表中的s_address字段可以继续拆分为国家country，省份province，城市city，街道street以及详细地址，因此该student表的设计不满足第一范式。

CREATE TABLE student(

​ s_id int PRIMARY KEY NOT NULL,

​ s_name varchar(20),

​ s_address varchar(40));

对该表做如下改进使其满足第一范式：

CREATE TABLE student(

​ s_id int PRIMARY KEY NOT NULL,

​ s_name varchar(20),

​ s_country varchar(20)，

​ s_province varchar(20),

​ s_city varchar(20),

​ s_street varchar(30),

​ s_details varchar(40));

2. 第二范式（2NF）

在满足第一范式的前提下，第二范式要求，除主键外的每一列都必须完全依赖于主键

如果要出现出现不完全依赖，只可能发生在联合主键的情况下

**例如：**下方的订单表order中，除主键以外的其他字段，只依赖于部分主键，product_name只依赖于product_id，customer_name只依赖于customer_id，不满足第二范式的要求。

CREATE TABLE order(

​ product_id int,

​ customer_id int,

​ product_name varchar(30),

​ customer_name varchar(20),

​ PRIMARY KEY(product_id,customer_id));

为使其满足第二范式的要求，需进行拆表处理，将其拆分为三张表:product_info、customer_info、order：

CREATE TABLE customer_info(

​ customer_id int PRIMARY KEY,

​ customer_name varchar(30),

​ customer_address varchar(40));

CREATE TABLE product_info(

​ product_id int PRIMARY KEY,

​ product_name varchar(20),

​ product_details varchar(40),

​ product_price double);

CREATE TABLE order(

​ order_id int PRIMARY KEY,

​ product_id int,

​ customer_id int);

3. 第三范式（3NF）

在满足第二范式的前提下，除主键外的其他列之间不能有传递依赖关系

**例如：**下方的order表中，customer_id和customer_phone_number之间存在依赖关系，不满足3NF的要求：

CREATE TABLE order(

​ order_id int PRIMARY KEY,

​ product_id int,

​ customer_id int,

​ customer_phone_number varchar(20));

为使其满足3NF的要求，将customer_phone_number 字段添加到customer_info表中：

CREATE TABLE customer_info(

​ customer_id int PRIMARY KEY,

​ customer_name varchar(30),

​ customer_phone_number varchar(20),

​ customer_address varchar(40));

Chapter Five MySQL查询练习

首先创建四张表：student学生表、course课程表、score成绩表、teacher教师表

• teacher 表:t_id,t_name,t_gender,t_birth,t_title,t_college

CREATE TABLE teacher(

​ t_id int PRIMARY KEY NOT NULL,

​ t_name varchar(20) NOT NULL,

​ t_gender varchar(10),

​ t_birth date,

​ t_title varchar(20),

​ t_college varchar(40)

);

• Course表：c_id，c_name，t_id

CREATE TABLE course(

​ c_id int PRIMAYR KEY NOT NULL,

​ c_name varchar(20) NOT NULL,

​ t_id int NOT NULL,

​ FOREIGN KEY(t_id) REFERENCES teacher(t_id)

);

• student表：s_id，s_name，s_gender,s_birth,s_class

CREATE TABLE student(

​ s_id int PRIMARY KEY NOT NULL,

​ s_name varchar(20) NOT NULL,

​ s_gender varchar(10),

​ s_birth date,

​ s_class varchar(30) NOT NULL,

​ );

• score 表：s_id,c_id,

CREATE TABLE score(

​ s_id int NOT NULL,

​ c_id int NOT NULL,

​ degree decimal,

​ FOREIGN KEY(s_id) REFERENCES student(s_id),

​ FOREIGN KEY(c_id) REFERENCES course(c_id),

​ PRIMARY KEY(s_id,c_id)

);

1. distinct关键字

• 从teacher表中查询出有多少个不同的院系college

  e.g.： select distinct t_college from teacher；

2. BETWEEN … AND … 关键字

• 查询score表中成绩degree在60到80之间的所有记录

  直接用and

  select * from score where degree >=60 and degree <=80;

• 或者用between… and…

  select * from score where degree between 60 and 80;

• 建立一张等级评分表grade

  CREATE TABLE grade(

  ​ low int(3),

  ​ up int(3),

  ​ grade char(1)

  );

  insert into grade values(90,100,‘A’);

  insert into grade values(80,89,‘B’);

  insert into grade values(70,79,'C‘);

  insert into grade values(60,69,‘D’);

  insert into grade values(0,59,‘E’);

• 现查询所有学生的s_id,c_id，grade

  select s_id, c_id,grade from score s, grade g where s.degree between g.low and g.upp;

3. IN和NOT IN 关键字

• 查询score表中成绩为85，86或者88的记录

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

• 查询score表中成绩不为85，86，88的记录

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

4. OR关键字

• 查询student表中“95031”班或者性别为“女”的同学的记录

  select * from student where s_class = 95031 OR s_gender =‘女’；

5. DESC关键字(ORDER BY+字段名)

• 以s_class降序用DESC查询student表中的所有记录

  select * from student order by s_class DESC;

• 以s_class升序order by默认升序ASC排列，查询student表中的所有记录

  select * from student order by s_class ASC;

• 以c_id升序，degree降序查询score表中的所有记录：

  select * from score order by c_id, degree DESC;

• 以班级号和年龄从大到小的顺序查询student表中的全部记录

  select * from student order by s_class DESC,s_birth;

6. LIMIT 关键字

limit arg1,arg2——arg1表示从哪一行开始，初始行为0，arg2表示显示多少行记录；

例如：

limit 0,2:表示在符合条件的记录中，从第一行开始显示2条记录

limit 1,3:表示在符合条件的记录中，从第2行开始显示3条记录

limit 10:表示在符合条件的记录中，显示前10条记录（默认从第0行开始）

7. COUNT()函数

• 查询班级编号为“95031”的学生人数

  select count(*) from student where s_class = ‘95031’;

8. MAX() \ MIN()函数

• 查询score表中的最高分的学生学号和课程号（使用排序）

  select s_id,c_id from score order by degree limit 0,1;

• 查询每门课最高分学生的s_id，c_id和degree

  select s_id,c_id,degree from score gourd by c_id having max(degree);

• 或者使用子查询的形式

  select s_id，c_id from score where degree = （select max(degree) from score);

• 查询student表中最大和最小的s_birth日期值

  select min(s_birth) min_birth, max(s_birth) max_birth from student;

9. AVG()函数

查询每门课的平均成绩

select avg(degree) from score group by c_id;

10. GROUP BY…[HAVING…] 语句

以某字段进行分组

• 例如9中的select avg(degree) from score group by c_id，

  表示先以c_id进行分组，再对每组中的degree字段求平均值

• 查询至少有两名男生的班级编号

  select s_class from student where s_gender=‘男’ group by s_class having count(s_id)>=2;

在where语句中不能使用聚合函数，而在having语句中可以，但having语句是和group by语句组合使用的

11. LIKE \ NOT LIKE 模糊查询

• 查询score表中至少2名学生选修的且课程编号以3开头的课程的平均成绩

  select c_id,avg(degree) from score group by c_id having c_id like ‘3%’ and count(s_id) >=2;

• 在score表中查询分数大于70，小于90的s_id

  select s_id,degree from score where degree between 70 and 90;

• 等价于：

  select s_id,degree from score where degree >70 and degree <90;

• 查询student表中不姓“王”的学生的记录

  select * from student where s_name not like ‘王%’;

12. AS 关键字

由于数据表为了能够更加清晰的表示所取得名称可能比较长，这时可以通过as关键字为其取别名，as可以省略

例如：select s.s_id,c.c_name,s.degree from score as s, course as c where s.c_id = c.c_id;

• 查询所有教师和学生的name，gender，birth

  select t_name as name,t_gender as gender,t_birth as birth from teacher

  union

  select s_name,s_gender,s_birth from student;

• 查询所有‘女’教师和女同学的name，birth

  select t_name as name, t_birth as birth from teacher where t_gender =‘女’

  union

  select s_name,s_birth from student where s_gender =‘女’;

13. 多表查询

• 查询s_name,c_id,degree

  select s.s_name,sc.c_id,sc.degree from student as s, score as sc where s.s_id = sc.s_id;

• 查询所有学生的s_id，c_name，和degree

  select s.s_id,c.c_name,s.degree from score s, course c where s.c_id = c.c_id;

• 查询所有学生的s_name，c_name，degree列（涉及到三张表）

  select s.s_name,c.c_name,sc.degree from student s, course c, score sc where s.s_id = sc.s_id and c.c_id = sc.c_id;

• 查询课程学修人数多于5人的，任课教师姓名

  select t_name from teacher where t_id in (

  select c.t_id from course c, score sc where c.c_id = sc.c_id and

  c.c_id in (select c_id from score group by c_id having count(s_id)>5));

14. 子查询

• 查询’95031’班学生每门课的平均分

  select c_id, avg(degree) from score where s_id in (

  select s_id from course where c_name=‘95031’);

• 查询选修课程编号为‘3-105’，且课程成绩高于s_id为‘109’的同的所有同学的记录

  select * from score where c_id=‘3-105’ degree > (

  select degree from score where c_id=‘3-105’ and s_id =109);

• 查询成绩高于s_id为109，课程号为‘3-105’的成绩的所有记录

  select * from score where degree > (select degree from score where s_id =109 and c_id =‘3-105’);

• 查询“男”教师及其所任教的课程

  select t.t_name c.c_name from teacher t, course c where t.t_id = c.c_id and t_gender =‘男’;

• 查询和李军同性别的所有学生的s_name

  select s_name from student where s_gender = (select s_gender from student where s_name =‘李军’);

• 查询和李军同性别且同班的所有学生的s_name

  select s_name from student where s_gender = (select s_gender from student where s_name =‘李军’)

  and s_class = (select s_class from student where s_name = ‘李军)’;

• 查询所有选修计算机导论课程的男同学的成绩表

  Select * from score where c_id =(select c_id from course where c_name=‘计算机导论’)

  and s_id in (select s_id from student where s_gender=‘男’ );

• 多层嵌套子查询

  • 查询“张旭”教师任课的学生成绩

  select s_id,degree from score where c_id in (

  select c_id from course where t_id in(

  select t_id from teacher where t_name = ‘张旭’));

  • 查询出“计算机系”教师所教课程的成绩表

    select * from score where c_id in(

    select c_id from course where t_id in(

    select t_id from teacher where t_college = ‘计算机系‘’));

15. YEAR()函数和in关键字的联合使用

• 查询和学号为108， 101同年出生的所有学生的s_id，s_name和s_birth记录

  select s_id, s_name, s_birth from student where year(s_birth) in (

  select year(s_birth) from student where s_id in(108,101));

• 查询班级名95031和95033班级所有学生的记录

  select * from student where s_class in(‘95031’,‘96033’));

• 查询所有任课教师的t_name和t_college

  select t_name，t_college from teacher where t_id in (select t_id from course);

16. WHER条件查询

• 查询存在有85分以上成绩的课程c_id

  select distinct c_id from score where degree > 85;

• 也可以通过group by 语句查询

select distinct c_id from score gourp by c_id having degree >85;

17. UNION 和 UNION ALL

用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中，会删除重复的数据（即求并集）

• 查询“计算机系”与“电子工程系”不同职称的教师的t_name和职称t_title

  理解：计算机系中教师已有职称电子工程系中不能有，同理电子工程教师已有职称不能出现在计算机系中

  select t_name, t_title from teacher where t_college = ‘计算机系’ and t_title not in(

  select t_tile from teacher where t_college=‘电子工程系’)

  union

  select t_name, t_title from teacher where t_college = '‘电子工程系’ and t_title not in(

  select t_tile from teacher where t_college=’‘计算机系’);

**注释：**UNION 只会选取不同的值（即求并集）， UNION ALL 可选取重复的值

18. ANY()函数

至少，任意一个

• 查询选修课程编号c_id为3-105，且课程成绩至少高于课程编号为3-245中学生的成绩的所有记录，并按degree从高到低次序排序

  select * from score where c_id=‘3-105’

  and degree>any(select degree from score where c_id=‘3-245’)

  order by degree DESC;

19.ALL()函数

全部

• 查询选修课程编号c_id为3-105，且课程成绩高于课程编号为3-245中学生的成绩的所有记录

  select * from score where c_id=‘3-105’

  and degree>all(select degree from score where c_id=‘3-245’);

20.复制表数据做条件查询

• 查询成绩比课程平均成绩低的学生的成绩

  select * from score group by c_id having degree < avg(degree);

  select * from score s1 where degree < (select avg(degree) from score s2 where s1.c_id = s2.c_id);

21. NOW()函数

• 查询student表中每个学生的年龄和姓名

  select (year(now())-year(s_birth) as age, s_name from student;

22. 连接查询

22.1 内连接

inner join 或者 join

22.2 外连接

1. 左连接 left join 或者 left outer join
2. 右连接 right join 或者 right outer join
3. 全外连接 full join

创建两张表person、card（并没有创建外键）

CREATE TABLE person(

​ p_id int PRIMARY KEY,

​ name varchar(20),

​ card_id int);

CREATE TABLE card(

​ c_id int PRIMARY KEY,

​ name varchar(20));

insert into card values(1,‘饭卡’);

insert into card values(2,‘农行卡’);

insert into card values(3,‘工行卡’);

insert into card values(4,‘建行卡’);

insert into person values(1,‘张三’,1);

insert into person values(2,‘李四’,3);

insert into person values(3,‘王五’,5);

————————————————————————————————————

• inner join内联查询(table_1 inner join table_2 on… )相当于取两张表的交集

  将两张表中的数据，通过某一字段相关联，查询出满足条件的记录数据

  select * from person p inner join card c on p.card_id = c.c_id;

  ±----- ±------- ±-----------±------±--------+

  | p_id | name | card_id | c_id | name|

  ±----- ±------- ±--------±-----±------------+

  | 1 | 张三 | 1 | 1 | 饭卡 |

  | 2 | 李四 | 3 | 3 | 工行卡 |

  ±----- ±------- ±--------±-----±-------------+

  

• left join 左连接

  将两张表中的数据，通过某一字段相关联，会将左边的表中的数据全部获取出来，对于右表有对应的数据则显示，无对应的数据则null

  select * from person p left join card c on p.card_id = c.c_id;

  ±-------±--------±-----------±--------±----------+

  | p_id | name| card_id | c_id | name |

  ±-------±--------±-----------±--------±----------+

  | 1 | 张三 | 1 | 1 | 饭卡 |

  | 2. | 李四 | 3 | 3 | 工行卡 |

  | 3. | 王五 | 5 | NULL | NULL |

  ±-------±-------±------------±---------±----------+

  

• right join 右连接

  将两张表中的数据，通过某一字段相关联，会将右表中的数据全部获取到，对于左表有对应的数据则显示，否则为null

  select * from person p right join card c on p.card_id = c.c_id;

  ±---------±---------±-----------±-----±----------+
  | p_id | name | card_id | c_id | name |
  ±---------±---------±-----------±-----±----------+
  | 1 | 张三 | 1 | 1 | 饭卡 |
  | 2 | 李四 | 3 | 3 | 工行卡 |
  | NULL | NULL | NULL | 2 | 农行卡 |
  | NULL | NULL | NULL | 4 | 建行卡 |
  ±--------±-----------±-----------±-----±---------+
  

24. MySQL不支持FULL JOIN全连接

但可以通过union实现两张表的全连接
select * from person p left join card c on p.card_id = c.c_id
union
select * from person p right join card c on p.card_id = c.c_id;
±---------±---------±-----------±--------±-----------+
| p_id | name | card_id | c_id | name |
±---------±---------±-----------±--------±-----------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 工行卡 |
| 3 | 王五 | 5 | NULL | NULL |
| NULL | NULL | NULL | 2 | 农行卡 |
| NULL | NULL | NULL | 4 | 建行卡 |
±--------±----------±-----------±--------±------------+
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hCvGuKMd-1585382511075)(/Users/chenminli/Library/Application Support/typora-user-images/image-20200328145438094.png)]

Chapter Six 事务

1. 什么是事务（transaction）

MySQL中，事务是一个最小的不可分割的工作单元。事务能够保证一个业务的完整性。
例如：最典型的就是银行转账
对于a、b两个账号a向b转账1000
则相应的两人的账号余额会发生更新:
a—>-1000
update user set money = money-1000 where name=‘a’;
b–>+1000
update user set money = money-1000 where name=‘a’;
若只有其中一条语句被成功执行，则数据前后出现不一致
事务就可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

2. MySQL 事务处理的两种方法

1、用 BEGIN, ROLLBACK, COMMIT来实现

• BEGIN \ START TRANSACTION 开始一个事务- ROLLBACK 事务回滚
• COMMIT 事务确认
  2、直接用 SET 来改变 MySQL 的自动提交模式:
• SET AUTOCOMMIT=0 禁止自动提交- SET AUTOCOMMIT=1 开启自动提交

例如：先创建一张transaction_test表：
​ CREATE TABLE transaction_test(
​ id int);
​ begin;
​ insert into runoob_transaction_test value(1);
​ insert into runoob_transaction_test value(3);
​ – 在commit；之前是可以进行回滚的；
​ commit;
此时表中的信息如下：
±-----+
| id |
±-----+
| 1 |
| 3 |
±-----+
————————————————————————

• 在commit之前进行回滚操作
  ​ begin;
  ​ insert into runoob_transaction_test value(5);
  ​ insert into runoob_transaction_test value(6);
  ​ rollback;
  ​ commit;
  表中数据没有发生变化，两句insert语句被撤销：
  ±-----+
  | id |
  ±-----+
  | 1 |
  | 3 |
  ±-----+
  在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就马上执行 COMMIT 操作。
  通过语句：select @@autocomm;进行查看
• 默认自动提交事务开启后：
  在执行一条sql语句时，效果会立即提现出来，不能回滚rollback
• rollback：事务回滚，撤销SQL语句的执行效果
  可以将自动提交设置为false
  set autocommit = 0;
• 显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION
  将 autocommit设置会true
  set autocommit=1;

3. 事务的四大特征ACID

• **原子性（Atomicity）：**一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

• **一致性（consistency）：**在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

• **隔离性（Isolation）：**数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
  事务隔离分为不同级别，包括

  • 读未提交（Read uncommitted） - 读提交（read committed）
  • 可重复读（repeatable read） - 串行化（Serializable）

• **持久性（Durability）：**事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

4. 事务的隔离性

• 事务隔离分为不同级别，包括 - 读未提交（Read uncommitted）：一个事务可以读取另一个未提交事务的数据

  • 读提交（read committed）：一个事务要等另一个事务提交后才能读取数据 - 可重复读（repeatable read）：在一个事务开启时，不再允许其他事务的update操作，但可以进行INSERT操作
  • 串行化（Serializable）：最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。
    可以通过select @@global.transaction_isolation;进行查看
    ±---------------------------------------------+
    | @@global.transaction_isolation |
    ±---------------------------------------------+
    | REPEATABLE-READ |
    ±---------------------------------------------+
    修改隔离级别：
    set globle transaction isolation level read committed;

• read uncommitted隔离级别下会引发脏读的情况 - 脏读：指一个事务读取了另外一个事务未提交的数据，Read committed隔离级别，能解决脏读问题。

• read committed隔离级别下会引发不可重复读的情况：若有事务对数据进行更新（UPDATE）操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。

  • 不可重复读（update操作）：在一个事务范围内两个相同的查询却返回了不同数据，多次读取结果不同；

• repeatable read隔离级别下会引发幻读的情况

  • 幻读（insert操作）：因为重读读隔离级别只是限制了其他事务的update操作，并没有限制insert操作，因此在事务b
    两次读取数据期间，事务a进行了插入操作导致两次读取的数据不一致

• 幻读：指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

事务的隔离特征参考网址：https://www.cnblogs.com/virgosnail/p/10398668.html