MySQL必备的常见知识点汇总整理

2023-07-25 19:17:19 177

本文实例总结了MySQL必备的常见知识点。分享给大家供大家参考，具体如下：

最近在整理sql的时候发现一份优秀的笔记，是原作者学习sql所做的笔记，分享这份总结给大家，对大家对sql的可以来一次全方位的检漏和排查，感谢原作者hjzCy的付出，原文链接放在文章最下方，如果出现错误，希望大家共同指出！

登录和退出MySQL服务器

#登录MySQL
$mysql-uroot-p12345612

#退出MySQL数据库服务器
exit;

基本语法

--显示所有数据库
showdatabases;

--创建数据库
CREATEDATABASEtest;

--切换数据库
usetest;

--显示数据库中的所有表
showtables;

--创建数据表
CREATETABLEpet(
nameVARCHAR(20),
ownerVARCHAR(20),
speciesVARCHAR(20),
sexCHAR(1),
birthDATE,
deathDATE
);

--查看数据表结构
--describepet;
descpet;

--查询表
SELECT*frompet;

--插入数据
INSERTINTOpetVALUES('puffball','Diane','hamster','f','1990-03-30',NULL);

--修改数据
UPDATEpetSETname='squirrel'whereowner='Diane';

--删除数据
DELETEFROMpetwherename='squirrel';

--删除表
DROPTABLEmyorder;

建表约束

主键约束

--主键约束
--使某个字段不重复且不得为空，确保表内所有数据的唯一性。
CREATETABLEuser(
idINTPRIMARYKEY,
nameVARCHAR(20)
);

--联合主键
--联合主键中的每个字段都不能为空，并且加起来不能和已设置的联合主键重复。
CREATETABLEuser(
idINT,
nameVARCHAR(20),
passwordVARCHAR(20),
PRIMARYKEY(id,name)
);

--自增约束
--自增约束的主键由系统自动递增分配。
CREATETABLEuser(
idINTPRIMARYKEYAUTO_INCREMENT,
nameVARCHAR(20)
);

--添加主键约束
--如果忘记设置主键，还可以通过SQL语句设置（两种方式）：
ALTERTABLEuserADDPRIMARYKEY(id);
ALTERTABLEuserMODIFYidINTPRIMARYKEY;

--删除主键
ALTERTABLEuserdropPRIMARYKEY;

唯一主键

--建表时创建唯一主键
CREATETABLEuser(
idINT,
nameVARCHAR(20),
UNIQUE(name)
);

--添加唯一主键
--如果建表时没有设置唯一建，还可以通过SQL语句设置（两种方式）：
ALTERTABLEuserADDUNIQUE(name);
ALTERTABLEuserMODIFYnameVARCHAR(20)UNIQUE;

--删除唯一主键
ALTERTABLEuserDROPINDEXname;

非空约束

--建表时添加非空约束
--约束某个字段不能为空
CREATETABLEuser(
idINT,
nameVARCHAR(20)NOTNULL
);

--移除非空约束
ALTERTABLEuserMODIFYnameVARCHAR(20);

默认约束

--建表时添加默认约束
--约束某个字段的默认值
CREATETABLEuser2(
idINT,
nameVARCHAR(20),
ageINTDEFAULT10
);

--移除非空约束
ALTERTABLEuserMODIFYageINT;

外键约束

--班级
CREATETABLEclasses(
idINTPRIMARYKEY,
nameVARCHAR(20)
);

--学生表
CREATETABLEstudents(
idINTPRIMARYKEY,
nameVARCHAR(20),
--这里的class_id要和classes中的id字段相关联
class_idINT,
--表示class_id的值必须来自于classes中的id字段值
FOREIGNKEY(class_id)REFERENCESclasses(id)
);

--1.主表（父表）classes中没有的数据值，在副表（子表）students中，是不可以使用的；
--2.主表中的记录被副表引用时，主表不可以被删除。

数据库的三大设计范式

1NF

只要字段值还可以继续拆分，就不满足第一范式。

范式设计得越详细，对某些实际操作可能会更好，但并非都有好处，需要对项目的实际情况进行设定。

2NF

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

--订单表
CREATETABLEmyorder(
product_idINT,
customer_idINT,
product_nameVARCHAR(20),
customer_nameVARCHAR(20),
PRIMARYKEY(product_id,customer_id)
);

实际上，在这张订单表中，product_name只依赖于product_id，customer_name只依赖于customer_id。也就是说，product_name和customer_id是没用关系的，customer_name和product_id也是没有关系的。

这就不满足第二范式：其他列都必须完全依赖于主键列！

CREATETABLEmyorder(
order_idINTPRIMARYKEY,
product_idINT,
customer_idINT
);

CREATETABLEproduct(
idINTPRIMARYKEY,
nameVARCHAR(20)
);

CREATETABLEcustomer(
idINTPRIMARYKEY,
nameVARCHAR(20)
);

拆分之后，myorder表中的product_id和customer_id完全依赖于order_id主键，而product和customer表中的其他字段又完全依赖于主键。满足了第二范式的设计！

3NF

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

CREATETABLEmyorder(
order_idINTPRIMARYKEY,
product_idINT,
customer_idINT,
customer_phoneVARCHAR(15)
);

表中的customer_phone有可能依赖于order_id、customer_id两列，也就不满足了第三范式的设计：其他列之间不能有传递依赖关系。

CREATETABLEmyorder(
order_idINTPRIMARYKEY,
product_idINT,
customer_idINT
);

CREATETABLEcustomer(
idINTPRIMARYKEY,
nameVARCHAR(20),
phoneVARCHAR(15)
);

修改后就不存在其他列之间的传递依赖关系，其他列都只依赖于主键列，满足了第三范式的设计！

查询练习

准备数据

--创建数据库
CREATEDATABASEselect_test;
--切换数据库
USEselect_test;

--创建学生表
CREATETABLEstudent(
noVARCHAR(20)PRIMARYKEY,
nameVARCHAR(20)NOTNULL,
sexVARCHAR(10)NOTNULL,
birthdayDATE,--生日
classVARCHAR(20)--所在班级
);

--创建教师表
CREATETABLEteacher(
noVARCHAR(20)PRIMARYKEY,
nameVARCHAR(20)NOTNULL,
sexVARCHAR(10)NOTNULL,
birthdayDATE,
professionVARCHAR(20)NOTNULL,--职称
departmentVARCHAR(20)NOTNULL--部门
);

--创建课程表
CREATETABLEcourse(
noVARCHAR(20)PRIMARYKEY,
nameVARCHAR(20)NOTNULL,
t_noVARCHAR(20)NOTNULL,--教师编号
--表示该tno来自于teacher表中的no字段值
FOREIGNKEY(t_no)REFERENCESteacher(no)
);

--成绩表
CREATETABLEscore(
s_noVARCHAR(20)NOTNULL,--学生编号
c_noVARCHAR(20)NOTNULL,--课程号
degreeDECIMAL,--成绩
--表示该s_no,c_no分别来自于student,course表中的no字段值
FOREIGNKEY(s_no)REFERENCESstudent(no),
FOREIGNKEY(c_no)REFERENCEScourse(no),
--设置s_no,c_no为联合主键
PRIMARYKEY(s_no,c_no)
);

--查看所有表
SHOWTABLES;

--添加学生表数据
INSERTINTOstudentVALUES('101','曾华','男','1977-09-01','95033');
INSERTINTOstudentVALUES('102','匡明','男','1975-10-02','95031');
INSERTINTOstudentVALUES('103','王丽','女','1976-01-23','95033');
INSERTINTOstudentVALUES('104','李军','男','1976-02-20','95033');
INSERTINTOstudentVALUES('105','王芳','女','1975-02-10','95031');
INSERTINTOstudentVALUES('106','陆军','男','1974-06-03','95031');
INSERTINTOstudentVALUES('107','王尼玛','男','1976-02-20','95033');
INSERTINTOstudentVALUES('108','张全蛋','男','1975-02-10','95031');
INSERTINTOstudentVALUES('109','赵铁柱','男','1974-06-03','95031');

--添加教师表数据
INSERTINTOteacherVALUES('804','李诚','男','1958-12-02','副教授','计算机系');
INSERTINTOteacherVALUES('856','张旭','男','1969-03-12','讲师','电子工程系');
INSERTINTOteacherVALUES('825','王萍','女','1972-05-05','助教','计算机系');
INSERTINTOteacherVALUES('831','刘冰','女','1977-08-14','助教','电子工程系');

--添加课程表数据
INSERTINTOcourseVALUES('3-105','计算机导论','825');
INSERTINTOcourseVALUES('3-245','操作系统','804');
INSERTINTOcourseVALUES('6-166','数字电路','856');
INSERTINTOcourseVALUES('9-888','高等数学','831');

--添加添加成绩表数据
INSERTINTOscoreVALUES('103','3-105','92');
INSERTINTOscoreVALUES('103','3-245','86');
INSERTINTOscoreVALUES('103','6-166','85');
INSERTINTOscoreVALUES('105','3-105','88');
INSERTINTOscoreVALUES('105','3-245','75');
INSERTINTOscoreVALUES('105','6-166','79');
INSERTINTOscoreVALUES('109','3-105','76');
INSERTINTOscoreVALUES('109','3-245','68');
INSERTINTOscoreVALUES('109','6-166','81');

--查看表结构
SELECT*FROMcourse;
SELECT*FROMscore;
SELECT*FROMstudent;
SELECT*FROMteacher;

1到10

--查询student表的所有行
SELECT*FROMstudent;

--查询student表中的name、sex和class字段的所有行
SELECTname,sex,classFROMstudent;

--查询teacher表中不重复的department列
--department:去重查询
SELECTDISTINCTdepartmentFROMteacher;

--查询score表中成绩在60-80之间的所有行（区间查询和运算符查询）
--BETWEENxxANDxx:查询区间,AND表示"并且"
SELECT*FROMscoreWHEREdegreeBETWEEN60AND80;
SELECT*FROMscoreWHEREdegree>60ANDdegree<80;

--查询score表中成绩为85,86或88的行
--IN:查询规定中的多个值
SELECT*FROMscoreWHEREdegreeIN(85,86,88);

--查询student表中'95031'班或性别为'女'的所有行
--or:表示或者关系
SELECT*FROMstudentWHEREclass='95031'orsex='女';

--以class降序的方式查询student表的所有行
--DESC:降序，从高到低
--ASC（默认）:升序，从低到高
SELECT*FROMstudentORDERBYclassDESC;
SELECT*FROMstudentORDERBYclassASC;

--以c_no升序、degree降序查询score表的所有行
SELECT*FROMscoreORDERBYc_noASC,degreeDESC;

--查询"95031"班的学生人数
--COUNT:统计
SELECTCOUNT(*)FROMstudentWHEREclass='95031';

--查询score表中的最高分的学生学号和课程编号（子查询或排序查询）。
--(SELECTMAX(degree)FROMscore):子查询，算出最高分
SELECTs_no,c_noFROMscoreWHEREdegree=(SELECTMAX(degree)FROMscore);

--排序查询
--LIMITr,n:表示从第r行开始，查询n条数据
SELECTs_no,c_no,degreeFROMscoreORDERBYdegreeDESCLIMIT0,1;

分组计算平均成绩

查询每门课的平均成绩。

--AVG:平均值
SELECTAVG(degree)FROMscoreWHEREc_no='3-105';
SELECTAVG(degree)FROMscoreWHEREc_no='3-245';
SELECTAVG(degree)FROMscoreWHEREc_no='6-166';

--GROUPBY:分组查询
SELECTc_no,AVG(degree)FROMscoreGROUPBYc_no;

分组条件与模糊查询

查询score表中至少有2名学生选修，并以3开头的课程的平均分数。

SELECT*FROMscore;
--c_no课程编号
+------+-------+--------+
|s_no|c_no|degree|
+------+-------+--------+
|103|3-105|92|
|103|3-245|86|
|103|6-166|85|
|105|3-105|88|
|105|3-245|75|
|105|6-166|79|
|109|3-105|76|
|109|3-245|68|
|109|6-166|81|
+------+-------+--------+

分析表发现，至少有2名学生选修的课程是3-105、3-245、6-166，以3开头的课程是3-105、3-245。也就是说，我们要查询所有3-105和3-245的degree平均分。

--首先把c_no,AVG(degree)通过分组查询出来
SELECTc_no,AVG(degree)FROMscoreGROUPBYc_no
+-------+-------------+
|c_no|AVG(degree)|
+-------+-------------+
|3-105|85.3333|
|3-245|76.3333|
|6-166|81.6667|
+-------+-------------+

--再查询出至少有2名学生选修的课程
--HAVING:表示持有
HAVINGCOUNT(c_no)>=2

--并且是以3开头的课程
--LIKE表示模糊查询，"%"是一个通配符，匹配"3"后面的任意字符。
ANDc_noLIKE'3%';

--把前面的SQL语句拼接起来，
--后面加上一个COUNT(*)，表示将每个分组的个数也查询出来。
SELECTc_no,AVG(degree),COUNT(*)FROMscoreGROUPBYc_no
HAVINGCOUNT(c_no)>=2ANDc_noLIKE'3%';
+-------+-------------+----------+
|c_no|AVG(degree)|COUNT(*)|
+-------+-------------+----------+
|3-105|85.3333|3|
|3-245|76.3333|3|
+-------+-------------+----------+

多表查询-1

查询所有学生的name，以及该学生在score表中对应的c_no和degree。

SELECTno,nameFROMstudent;
+-----+-----------+
|no|name|
+-----+-----------+
|101|曾华|
|102|匡明|
|103|王丽|
|104|李军|
|105|王芳|
|106|陆军|
|107|王尼玛|
|108|张全蛋|
|109|赵铁柱|
+-----+-----------+

SELECTs_no,c_no,degreeFROMscore;
+------+-------+--------+
|s_no|c_no|degree|
+------+-------+--------+
|103|3-105|92|
|103|3-245|86|
|103|6-166|85|
|105|3-105|88|
|105|3-245|75|
|105|6-166|79|
|109|3-105|76|
|109|3-245|68|
|109|6-166|81|
+------+-------+--------+

通过分析可以发现，只要把score表中的s_no字段值替换成student表中对应的name字段值就可以了，如何做呢？

--FROM...:表示从student,score表中查询
--WHERE的条件表示为，只有在student.no和score.s_no相等时才显示出来。
SELECTname,c_no,degreeFROMstudent,score
WHEREstudent.no=score.s_no;
+-----------+-------+--------+
|name|c_no|degree|
+-----------+-------+--------+
|王丽|3-105|92|
|王丽|3-245|86|
|王丽|6-166|85|
|王芳|3-105|88|
|王芳|3-245|75|
|王芳|6-166|79|
|赵铁柱|3-105|76|
|赵铁柱|3-245|68|
|赵铁柱|6-166|81|
+-----------+-------+--------+

多表查询-2

查询所有学生的no、课程名称(course表中的name)和成绩(score表中的degree)列。

只有score关联学生的no，因此只要查询score表，就能找出所有和学生相关的no和degree：

SELECTs_no,c_no,degreeFROMscore;
+------+-------+--------+
|s_no|c_no|degree|
+------+-------+--------+
|103|3-105|92|
|103|3-245|86|
|103|6-166|85|
|105|3-105|88|
|105|3-245|75|
|105|6-166|79|
|109|3-105|76|
|109|3-245|68|
|109|6-166|81|
+------+-------+--------+

然后查询course表：

+-------+-----------------+
|no|name|
+-------+-----------------+
|3-105|计算机导论|
|3-245|操作系统|
|6-166|数字电路|
|9-888|高等数学|
+-------+-----------------+

只要把score表中的c_no替换成course表中对应的name字段值就可以了。

--增加一个查询字段name，分别从score、course这两个表中查询。
--as表示取一个该字段的别名。
SELECTs_no,nameasc_name,degreeFROMscore,course
WHEREscore.c_no=course.no;
+------+-----------------+--------+
|s_no|c_name|degree|
+------+-----------------+--------+
|103|计算机导论|92|
|105|计算机导论|88|
|109|计算机导论|76|
|103|操作系统|86|
|105|操作系统|75|
|109|操作系统|68|
|103|数字电路|85|
|105|数字电路|79|
|109|数字电路|81|
+------+-----------------+--------+

三表关联查询

查询所有学生的name、课程名(course表中的name)和degree。

只有score表中关联学生的学号和课堂号，我们只要围绕着score这张表查询就好了。

SELECT*FROMscore;
+------+-------+--------+
|s_no|c_no|degree|
+------+-------+--------+
|103|3-105|92|
|103|3-245|86|
|103|6-166|85|
|105|3-105|88|
|105|3-245|75|
|105|6-166|79|
|109|3-105|76|
|109|3-245|68|
|109|6-166|81|
+------+-------+--------+

只要把s_no和c_no替换成student和srouse表中对应的name字段值就好了。

首先把s_no替换成student表中的name字段：

SELECTname,c_no,degreeFROMstudent,scoreWHEREstudent.no=score.s_no;
+-----------+-------+--------+
|name|c_no|degree|
+-----------+-------+--------+
|王丽|3-105|92|
|王丽|3-245|86|
|王丽|6-166|85|
|王芳|3-105|88|
|王芳|3-245|75|
|王芳|6-166|79|
|赵铁柱|3-105|76|
|赵铁柱|3-245|68|
|赵铁柱|6-166|81|
+-----------+-------+--------+

再把c_no替换成course表中的name字段：

--课程表
SELECTno,nameFROMcourse;
+-------+-----------------+
|no|name|
+-------+-----------------+
|3-105|计算机导论|
|3-245|操作系统|
|6-166|数字电路|
|9-888|高等数学|
+-------+-----------------+

--由于字段名存在重复，使用"表名.字段名as别名"代替。
SELECTstudent.nameass_name,course.nameasc_name,degree
FROMstudent,score,course
WHEREstudent.NO=score.s_no
ANDscore.c_no=course.no;

子查询加分组求平均分

查询95031班学生每门课程的平均成绩。

在score表中根据student表的学生编号筛选出学生的课堂号和成绩：

--IN(..):将筛选出的学生号当做s_no的条件查询
SELECTs_no,c_no,degreeFROMscore
WHEREs_noIN(SELECTnoFROMstudentWHEREclass='95031');
+------+-------+--------+
|s_no|c_no|degree|
+------+-------+--------+
|105|3-105|88|
|105|3-245|75|
|105|6-166|79|
|109|3-105|76|
|109|3-245|68|
|109|6-166|81|
+------+-------+--------+

这时只要将c_no分组一下就能得出95031班学生每门课的平均成绩：

SELECTc_no,AVG(degree)FROMscore
WHEREs_noIN(SELECTnoFROMstudentWHEREclass='95031')
GROUPBYc_no;
+-------+-------------+
|c_no|AVG(degree)|
+-------+-------------+
|3-105|82.0000|
|3-245|71.5000|
|6-166|80.0000|
+-------+-------------+

子查询-1

查询在3-105课程中，所有成绩高于109号同学的记录。

首先筛选出课堂号为3-105，在找出所有成绩高于109号同学的的行。

SELECT*FROMscore
WHEREc_no='3-105'
ANDdegree>(SELECTdegreeFROMscoreWHEREs_no='109'ANDc_no='3-105');

子查询-2

查询所有成绩高于109号同学的3-105课程成绩记录。

--不限制课程号，只要成绩大于109号同学的3-105课程成绩就可以。
SELECT*FROMscore
WHEREdegree>(SELECTdegreeFROMscoreWHEREs_no='109'ANDc_no='3-105');

YEAR函数与带IN关键字查询

查询所有和101、108号学生同年出生的no、name、birthday列。

--YEAR(..):取出日期中的年份
SELECTno,name,birthdayFROMstudent
WHEREYEAR(birthday)IN(SELECTYEAR(birthday)FROMstudentWHEREnoIN(101,108));

多层嵌套子查询

查询'张旭'教师任课的学生成绩表。

首先找到教师编号：

SELECTNOFROMteacherWHERENAME='张旭'

通过sourse表找到该教师课程号：

SELECTNOFROMcourseWHEREt_no=(SELECTNOFROMteacherWHERENAME='张旭');

通过筛选出的课程号查询成绩表：

SELECT*FROMscoreWHEREc_no=(
SELECTnoFROMcourseWHEREt_no=(
SELECTnoFROMteacherWHERENAME='张旭'
)
);

多表查询

查询某选修课程多于5个同学的教师姓名。

首先在teacher表中，根据no字段来判断该教师的同一门课程是否有至少5名学员选修：

--查询teacher表
SELECTno,nameFROMteacher;
+-----+--------+
|no|name|
+-----+--------+
|804|李诚|
|825|王萍|
|831|刘冰|
|856|张旭|
+-----+--------+

SELECTnameFROMteacherWHEREnoIN(
--在这里找到对应的条件
);

查看和教师编号有有关的表的信息：

SELECT*FROMcourse;
--t_no:教师编号
+-------+-----------------+------+
|no|name|t_no|
+-------+-----------------+------+
|3-105|计算机导论|825|
|3-245|操作系统|804|
|6-166|数字电路|856|
|9-888|高等数学|831|
+-------+-----------------+------+

我们已经找到和教师编号有关的字段就在course表中，但是还无法知道哪门课程至少有5名学生选修，所以还需要根据score表来查询：

--在此之前向score插入一些数据，以便丰富查询条件。
INSERTINTOscoreVALUES('101','3-105','90');
INSERTINTOscoreVALUES('102','3-105','91');
INSERTINTOscoreVALUES('104','3-105','89');

--查询score表
SELECT*FROMscore;
+------+-------+--------+
|s_no|c_no|degree|
+------+-------+--------+
|101|3-105|90|
|102|3-105|91|
|103|3-105|92|
|103|3-245|86|
|103|6-166|85|
|104|3-105|89|
|105|3-105|88|
|105|3-245|75|
|105|6-166|79|
|109|3-105|76|
|109|3-245|68|
|109|6-166|81|
+------+-------+--------+

--在score表中将c_no作为分组，并且限制c_no持有至少5条数据。
SELECTc_noFROMscoreGROUPBYc_noHAVINGCOUNT(*)>5;
+-------+
|c_no|
+-------+
|3-105|
+-------+

根据筛选出来的课程号，找出在某课程中，拥有至少5名学员的教师编号：

SELECTt_noFROMcourseWHEREnoIN(
SELECTc_noFROMscoreGROUPBYc_noHAVINGCOUNT(*)>5
);
+------+
|t_no|
+------+
|825|
+------+

在teacher表中，根据筛选出来的教师编号找到教师姓名：

SELECTnameFROMteacherWHEREnoIN(
--最终条件
SELECTt_noFROMcourseWHEREnoIN(
SELECTc_noFROMscoreGROUPBYc_noHAVINGCOUNT(*)>5
)
);

子查询-3

查询“计算机系”课程的成绩表。

思路是，先找出course表中所有计算机系课程的编号，然后根据这个编号查询score表。

--通过teacher表查询所有`计算机系`的教师编号
SELECTno,name,departmentFROMteacherWHEREdepartment='计算机系'
+-----+--------+--------------+
|no|name|department|
+-----+--------+--------------+
|804|李诚|计算机系|
|825|王萍|计算机系|
+-----+--------+--------------+

--通过course表查询该教师的课程编号
SELECTnoFROMcourseWHEREt_noIN(
SELECTnoFROMteacherWHEREdepartment='计算机系'
);
+-------+
|no|
+-------+
|3-245|
|3-105|
+-------+

--根据筛选出来的课程号查询成绩表
SELECT*FROMscoreWHEREc_noIN(
SELECTnoFROMcourseWHEREt_noIN(
SELECTnoFROMteacherWHEREdepartment='计算机系'
)
);
+------+-------+--------+
|s_no|c_no|degree|
+------+-------+--------+
|103|3-245|86|
|105|3-245|75|
|109|3-245|68|
|101|3-105|90|
|102|3-105|91|
|103|3-105|92|
|104|3-105|89|
|105|3-105|88|
|109|3-105|76|
+------+-------+--------+

UNION和NOTIN的使用

查询计算机系与电子工程系中的不同职称的教师。

--NOT:代表逻辑非
SELECT*FROMteacherWHEREdepartment='计算机系'ANDprofessionNOTIN(
SELECTprofessionFROMteacherWHEREdepartment='电子工程系'
)
--合并两个集
UNION
SELECT*FROMteacherWHEREdepartment='电子工程系'ANDprofessionNOTIN(
SELECTprofessionFROMteacherWHEREdepartment='计算机系'
);

ANY表示至少一个-DESC(降序)

查询课程3-105且成绩至少高于3-245的score表。

SELECT*FROMscoreWHEREc_no='3-105';
+------+-------+--------+
|s_no|c_no|degree|
+------+-------+--------+
|101|3-105|90|
|102|3-105|91|
|103|3-105|92|
|104|3-105|89|
|105|3-105|88|
|109|3-105|76|
+------+-------+--------+

SELECT*FROMscoreWHEREc_no='3-245';
+------+-------+--------+
|s_no|c_no|degree|
+------+-------+--------+
|103|3-245|86|
|105|3-245|75|
|109|3-245|68|
+------+-------+--------+

--ANY:符合SQL语句中的任意条件。
--也就是说，在3-105成绩中，只要有一个大于从3-245筛选出来的任意行就符合条件，
--最后根据降序查询结果。
SELECT*FROMscoreWHEREc_no='3-105'ANDdegree>ANY(
SELECTdegreeFROMscoreWHEREc_no='3-245'
)ORDERBYdegreeDESC;
+------+-------+--------+
|s_no|c_no|degree|
+------+-------+--------+
|103|3-105|92|
|102|3-105|91|
|101|3-105|90|
|104|3-105|89|
|105|3-105|88|
|109|3-105|76|
+------+-------+--------+

表示所有的ALL

查询课程3-105且成绩高于3-245的score表。

--只需对上一道题稍作修改。
--ALL:符合SQL语句中的所有条件。
--也就是说，在3-105每一行成绩中，都要大于从3-245筛选出来全部行才算符合条件。
SELECT*FROMscoreWHEREc_no='3-105'ANDdegree>ALL(
SELECTdegreeFROMscoreWHEREc_no='3-245'
);
+------+-------+--------+
|s_no|c_no|degree|
+------+-------+--------+
|101|3-105|90|
|102|3-105|91|
|103|3-105|92|
|104|3-105|89|
|105|3-105|88|
+------+-------+--------+

复制表的数据作为条件查询

查询某课程成绩比该课程平均成绩低的score表。

--查询平均分
SELECTc_no,AVG(degree)FROMscoreGROUPBYc_no;
+-------+-------------+
|c_no|AVG(degree)|
+-------+-------------+
|3-105|87.6667|
|3-245|76.3333|
|6-166|81.6667|
+-------+-------------+

--查询score表
SELECTdegreeFROMscore;
+--------+
|degree|
+--------+
|90|
|91|
|92|
|86|
|85|
|89|
|88|
|75|
|79|
|76|
|68|
|81|
+--------+

--将表b作用于表a中查询数据
--scorea(b):将表声明为a(b)，
--如此就能用a.c_no=b.c_no作为条件执行查询了。
SELECT*FROMscoreaWHEREdegree<(
(SELECTAVG(degree)FROMscorebWHEREa.c_no=b.c_no)
);
+------+-------+--------+
|s_no|c_no|degree|
+------+-------+--------+
|105|3-245|75|
|105|6-166|79|
|109|3-105|76|
|109|3-245|68|
|109|6-166|81|
+------+-------+--------+

子查询-4

查询所有任课(在course表里有课程)教师的name和department。

SELECTname,departmentFROMteacherWHEREnoIN(SELECTt_noFROMcourse);
+--------+-----------------+
|name|department|
+--------+-----------------+
|李诚|计算机系|
|王萍|计算机系|
|刘冰|电子工程系|
|张旭|电子工程系|
+--------+-----------------+

条件加组筛选

查询student表中至少有2名男生的class。

--查看学生表信息
SELECT*FROMstudent;
+-----+-----------+-----+------------+-------+
|no|name|sex|birthday|class|
+-----+-----------+-----+------------+-------+
|101|曾华|男|1977-09-01|95033|
|102|匡明|男|1975-10-02|95031|
|103|王丽|女|1976-01-23|95033|
|104|李军|男|1976-02-20|95033|
|105|王芳|女|1975-02-10|95031|
|106|陆军|男|1974-06-03|95031|
|107|王尼玛|男|1976-02-20|95033|
|108|张全蛋|男|1975-02-10|95031|
|109|赵铁柱|男|1974-06-03|95031|
|110|张飞|男|1974-06-03|95038|
+-----+-----------+-----+------------+-------+

--只查询性别为男，然后按class分组，并限制class行大于1。
SELECTclassFROMstudentWHEREsex='男'GROUPBYclassHAVINGCOUNT(*)>1;
+-------+
|class|
+-------+
|95033|
|95031|
+-------+

NOTLIKE模糊查询取反

查询student表中不姓"王"的同学记录。

--NOT:取反
--LIKE:模糊查询
mysql>SELECT*FROMstudentWHEREnameNOTLIKE'王%';
+-----+-----------+-----+------------+-------+
|no|name|sex|birthday|class|
+-----+-----------+-----+------------+-------+
|101|曾华|男|1977-09-01|95033|
|102|匡明|男|1975-10-02|95031|
|104|李军|男|1976-02-20|95033|
|106|陆军|男|1974-06-03|95031|
|108|张全蛋|男|1975-02-10|95031|
|109|赵铁柱|男|1974-06-03|95031|
|110|张飞|男|1974-06-03|95038|
+-----+-----------+-----+------------+-------+

YEAR与NOW函数

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

--使用函数YEAR(NOW())计算出当前年份，减去出生年份后得出年龄。
SELECTname,YEAR(NOW())-YEAR(birthday)asageFROMstudent;
+-----------+------+
|name|age|
+-----------+------+
|曾华|42|
|匡明|44|
|王丽|43|
|李军|43|
|王芳|44|
|陆军|45|
|王尼玛|43|
|张全蛋|44|
|赵铁柱|45|
|张飞|45|
+-----------+------+

MAX与MIN函数

查询student表中最大和最小的birthday值。

SELECTMAX(birthday),MIN(birthday)FROMstudent;
+---------------+---------------+
|MAX(birthday)|MIN(birthday)|
+---------------+---------------+
|1977-09-01|1974-06-03|
+---------------+---------------+

多段排序

以class和birthday从大到小的顺序查询student表。

SELECT*FROMstudentORDERBYclassDESC,birthday;
+-----+-----------+-----+------------+-------+
|no|name|sex|birthday|class|
+-----+-----------+-----+------------+-------+
|110|张飞|男|1974-06-03|95038|
|103|王丽|女|1976-01-23|95033|
|104|李军|男|1976-02-20|95033|
|107|王尼玛|男|1976-02-20|95033|
|101|曾华|男|1977-09-01|95033|
|106|陆军|男|1974-06-03|95031|
|109|赵铁柱|男|1974-06-03|95031|
|105|王芳|女|1975-02-10|95031|
|108|张全蛋|男|1975-02-10|95031|
|102|匡明|男|1975-10-02|95031|
+-----+-----------+-----+------------+-------+

子查询-5

查询"男"教师及其所上的课程。

SELECT*FROMcourseWHEREt_noin(SELECTnoFROMteacherWHEREsex='男');
+-------+--------------+------+
|no|name|t_no|
+-------+--------------+------+
|3-245|操作系统|804|
|6-166|数字电路|856|
+-------+--------------+------+

MAX函数与子查询

查询最高分同学的score表。

--找出最高成绩（该查询只能有一个结果）
SELECTMAX(degree)FROMscore;

--根据上面的条件筛选出所有最高成绩表，
--该查询可能有多个结果，假设degree值多次符合条件。
SELECT*FROMscoreWHEREdegree=(SELECTMAX(degree)FROMscore);
+------+-------+--------+
|s_no|c_no|degree|
+------+-------+--------+
|103|3-105|92|
+------+-------+--------+

子查询-6

查询和"李军"同性别的所有同学name。

--首先将李军的性别作为条件取出来
SELECTsexFROMstudentWHEREname='李军';
+-----+
|sex|
+-----+
|男|
+-----+

--根据性别查询name和sex
SELECTname,sexFROMstudentWHEREsex=(
SELECTsexFROMstudentWHEREname='李军'
);
+-----------+-----+
|name|sex|
+-----------+-----+
|曾华|男|
|匡明|男|
|李军|男|
|陆军|男|
|王尼玛|男|
|张全蛋|男|
|赵铁柱|男|
|张飞|男|
+-----------+-----+

子查询-7

查询和"李军"同性别且同班的同学name。

SELECTname,sex,classFROMstudentWHEREsex=(
SELECTsexFROMstudentWHEREname='李军'
)ANDclass=(
SELECTclassFROMstudentWHEREname='李军'
);
+-----------+-----+-------+
|name|sex|class|
+-----------+-----+-------+
|曾华|男|95033|
|李军|男|95033|
|王尼玛|男|95033|
+-----------+-----+-------+

子查询-8

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

需要的"计算机导论"和性别为"男"的编号可以在course和student表中找到。

SELECT*FROMscoreWHEREc_no=(
SELECTnoFROMcourseWHEREname='计算机导论'
)ANDs_noIN(
SELECTnoFROMstudentWHEREsex='男'
);
+------+-------+--------+
|s_no|c_no|degree|
+------+-------+--------+
|101|3-105|90|
|102|3-105|91|
|104|3-105|89|
|109|3-105|76|
+------+-------+--------+

按等级查询

建立一个grade表代表学生的成绩等级，并插入数据：

CREATETABLEgrade(
lowINT(3),
uppINT(3),
gradechar(1)
);

INSERTINTOgradeVALUES(90,100,'A');
INSERTINTOgradeVALUES(80,89,'B');
INSERTINTOgradeVALUES(70,79,'C');
INSERTINTOgradeVALUES(60,69,'D');
INSERTINTOgradeVALUES(0,59,'E');

SELECT*FROMgrade;
+------+------+-------+
|low|upp|grade|
+------+------+-------+
|90|100|A|
|80|89|B|
|70|79|C|
|60|69|D|
|0|59|E|
+------+------+-------+

查询所有学生的s_no、c_no和grade列。

思路是，使用区间(BETWEEN)查询，判断学生的成绩(degree)在grade表的low和upp之间。

SELECTs_no,c_no,gradeFROMscore,grade
WHEREdegreeBETWEENlowANDupp;
+------+-------+-------+
|s_no|c_no|grade|
+------+-------+-------+
|101|3-105|A|
|102|3-105|A|
|103|3-105|A|
|103|3-245|B|
|103|6-166|B|
|104|3-105|B|
|105|3-105|B|
|105|3-245|C|
|105|6-166|C|
|109|3-105|C|
|109|3-245|D|
|109|6-166|B|
+------+-------+-------+

连接查询

准备用于测试连接查询的数据：

CREATEDATABASEtestJoin;

CREATETABLEperson(
idINT,
nameVARCHAR(20),
cardIdINT
);

CREATETABLEcard(
idINT,
nameVARCHAR(20)
);

INSERTINTOcardVALUES(1,'饭卡'),(2,'建行卡'),(3,'农行卡'),(4,'工商卡'),(5,'邮政卡');
SELECT*FROMcard;
+------+-----------+
|id|name|
+------+-----------+
|1|饭卡|
|2|建行卡|
|3|农行卡|
|4|工商卡|
|5|邮政卡|
+------+-----------+

INSERTINTOpersonVALUES(1,'张三',1),(2,'李四',3),(3,'王五',6);
SELECT*FROMperson;
+------+--------+--------+
|id|name|cardId|
+------+--------+--------+
|1|张三|1|
|2|李四|3|
|3|王五|6|
+------+--------+--------+

分析两张表发现，person表并没有为cardId字段设置一个在card表中对应的id外键。如果设置了的话，person中cardId字段值为6的行就插不进去，因为该cardId值在card表中并没有。

内连接

要查询这两张表中有关系的数据，可以使用INNERJOIN(内连接)将它们连接在一起。

--INNERJOIN:表示为内连接，将两张表拼接在一起。
--on:表示要执行某个条件。
SELECT*FROMpersonINNERJOINcardonperson.cardId=card.id;
+------+--------+--------+------+-----------+
|id|name|cardId|id|name|
+------+--------+--------+------+-----------+
|1|张三|1|1|饭卡|
|2|李四|3|3|农行卡|
+------+--------+--------+------+-----------+

--将INNER关键字省略掉，结果也是一样的。
--SELECT*FROMpersonJOINcardonperson.cardId=card.id;

注意：card的整张表被连接到了右边。

左外连接

完整显示左边的表(person)，右边的表如果符合条件就显示，不符合则补NULL。

--LEFTJOIN也叫做LEFTOUTERJOIN，用这两种方式的查询结果是一样的。
SELECT*FROMpersonLEFTJOINcardonperson.cardId=card.id;
+------+--------+--------+------+-----------+
|id|name|cardId|id|name|
+------+--------+--------+------+-----------+
|1|张三|1|1|饭卡|
|2|李四|3|3|农行卡|
|3|王五|6|NULL|NULL|
+------+--------+--------+------+-----------+

右外链接

完整显示右边的表(card)，左边的表如果符合条件就显示，不符合则补NULL。

SELECT*FROMpersonRIGHTJOINcardonperson.cardId=card.id;
+------+--------+--------+------+-----------+
|id|name|cardId|id|name|
+------+--------+--------+------+-----------+
|1|张三|1|1|饭卡|
|2|李四|3|3|农行卡|
|NULL|NULL|NULL|2|建行卡|
|NULL|NULL|NULL|4|工商卡|
|NULL|NULL|NULL|5|邮政卡|
+------+--------+--------+------+-----------+

全外链接

完整显示两张表的全部数据。

--MySQL不支持这种语法的全外连接
--SELECT*FROMpersonFULLJOINcardonperson.cardId=card.id;
--出现错误：
--ERROR1054(42S22):Unknowncolumn'person.cardId'in'onclause'

--MySQL全连接语法，使用UNION将两张表合并在一起。
SELECT*FROMpersonLEFTJOINcardonperson.cardId=card.id
UNION
SELECT*FROMpersonRIGHTJOINcardonperson.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|邮政卡|
+------+--------+--------+------+-----------+

事务

在MySQL中，事务其实是一个最小的不可分割的工作单元。事务能够保证一个业务的完整性。

比如我们的银行转账：

--a->-100
UPDATEusersetmoney=money-100WHEREname='a';

--b->+100
UPDATEusersetmoney=money+100WHEREname='b';

在实际项目中，假设只有一条SQL语句执行成功，而另外一条执行失败了，就会出现数据前后不一致。

因此，在执行多条有关联SQL语句时，事务可能会要求这些SQL语句要么同时执行成功，要么就都执行失败。

如何控制事务-COMMIT/ROLLBACK

在MySQL中，事务的自动提交状态默认是开启的。

--查询事务的自动提交状态
SELECT@@AUTOCOMMIT;
+--------------+
|@@AUTOCOMMIT|
+--------------+
|1|
+--------------+

自动提交的作用：当我们执行一条SQL语句的时候，其产生的效果就会立即体现出来，且不能回滚。

什么是回滚？举个例子：

CREATEDATABASEbank;

USEbank;

CREATETABLEuser(
idINTPRIMARYKEY,
nameVARCHAR(20),
moneyINT
);

INSERTINTOuserVALUES(1,'a',1000);

SELECT*FROMuser;
+----+------+-------+
|id|name|money|
+----+------+-------+
|1|a|1000|
+----+------+-------+

可以看到，在执行插入语句后数据立刻生效，原因是MySQL中的事务自动将它提交到了数据库中。那么所谓回滚的意思就是，撤销执行过的所有SQL语句，使其回滚到最后一次提交数据时的状态。

在MySQL中使用ROLLBACK执行回滚：

--回滚到最后一次提交
ROLLBACK;

SELECT*FROMuser;
+----+------+-------+
|id|name|money|
+----+------+-------+
|1|a|1000|
+----+------+-------+

由于所有执行过的SQL语句都已经被提交过了，所以数据并没有发生回滚。那如何让数据可以发生回滚？

--关闭自动提交
SETAUTOCOMMIT=0;

--查询自动提交状态
SELECT@@AUTOCOMMIT;
+--------------+
|@@AUTOCOMMIT|
+--------------+
|0|
+--------------+

将自动提交关闭后，测试数据回滚：

INSERTINTOuserVALUES(2,'b',1000);

--关闭AUTOCOMMIT后，数据的变化是在一张虚拟的临时数据表中展示，
--发生变化的数据并没有真正插入到数据表中。
SELECT*FROMuser;
+----+------+-------+
|id|name|money|
+----+------+-------+
|1|a|1000|
|2|b|1000|
+----+------+-------+

--数据表中的真实数据其实还是：
+----+------+-------+
|id|name|money|
+----+------+-------+
|1|a|1000|
+----+------+-------+

--由于数据还没有真正提交，可以使用回滚
ROLLBACK;

--再次查询
SELECT*FROMuser;
+----+------+-------+
|id|name|money|
+----+------+-------+
|1|a|1000|
+----+------+-------+

那如何将虚拟的数据真正提交到数据库中？使用COMMIT:

INSERTINTOuserVALUES(2,'b',1000);
--手动提交数据（持久性），
--将数据真正提交到数据库中，执行后不能再回滚提交过的数据。
COMMIT;

--提交后测试回滚
ROLLBACK;

--再次查询（回滚无效了）
SELECT*FROMuser;
+----+------+-------+
|id|name|money|
+----+------+-------+
|1|a|1000|
|2|b|1000|
+----+------+-------+

总结

自动提交

查看自动提交状态：SELECT@@AUTOCOMMIT；

设置自动提交状态：SETAUTOCOMMIT=0。

手动提交
@@AUTOCOMMIT=0时，使用COMMIT命令提交事务。

事务回滚
@@AUTOCOMMIT=0时，使用ROLLBACK命令回滚事务。

事务的实际应用，让我们再回到银行转账项目：

--转账
UPDATEusersetmoney=money-100WHEREname='a';

--到账
UPDATEusersetmoney=money+100WHEREname='b';

SELECT*FROMuser;
+----+------+-------+
|id|name|money|
+----+------+-------+
|1|a|900|
|2|b|1100|
+----+------+-------+

这时假设在转账时发生了意外，就可以使用ROLLBACK回滚到最后一次提交的状态：

--假设转账发生了意外，需要回滚。
ROLLBACK;

SELECT*FROMuser;
+----+------+-------+
|id|name|money|
+----+------+-------+
|1|a|1000|
|2|b|1000|
+----+------+-------+

这时我们又回到了发生意外之前的状态，也就是说，事务给我们提供了一个可以反悔的机会。假设数据没有发生意外，这时可以手动将数据真正提交到数据表中：COMMIT。

手动开启事务-BEGIN/STARTTRANSACTION

事务的默认提交被开启(@@AUTOCOMMIT=1)后，此时就不能使用事务回滚了。但是我们还可以手动开启一个事务处理事件，使其可以发生回滚：

--使用BEGIN或者STARTTRANSACTION手动开启一个事务
--STARTTRANSACTION;
BEGIN;
UPDATEusersetmoney=money-100WHEREname='a';
UPDATEusersetmoney=money+100WHEREname='b';

--由于手动开启的事务没有开启自动提交，
--此时发生变化的数据仍然是被保存在一张临时表中。
SELECT*FROMuser;
+----+------+-------+
|id|name|money|
+----+------+-------+
|1|a|900|
|2|b|1100|
+----+------+-------+

--测试回滚
ROLLBACK;

SELECT*FROMuser;
+----+------+-------+
|id|name|money|
+----+------+-------+
|1|a|1000|
|2|b|1000|
+----+------+-------+

仍然使用COMMIT提交数据，提交后无法再发生本次事务的回滚。

BEGIN;
UPDATEusersetmoney=money-100WHEREname='a';
UPDATEusersetmoney=money+100WHEREname='b';

SELECT*FROMuser;
+----+------+-------+
|id|name|money|
+----+------+-------+
|1|a|900|
|2|b|1100|
+----+------+-------+

--提交数据
COMMIT;

--测试回滚（无效，因为表的数据已经被提交）
ROLLBACK;

事务的ACID特征与使用

事务的四大特征：

A原子性：事务是最小的单位，不可以再分割；
C一致性：要求同一事务中的SQL语句，必须保证同时成功或者失败；
I隔离性：事务1和事务2之间是具有隔离性的；
D持久性：事务一旦结束(COMMIT)，就不可以再返回了(ROLLBACK)。

事务的隔离性

事务的隔离性可分为四种(性能从低到高)：

READUNCOMMITTED(读取未提交)
如果有多个事务，那么任意事务都可以看见其他事务的未提交数据。
READCOMMITTED(读取已提交)
只能读取到其他事务已经提交的数据。
REPEATABLEREAD(可被重复读)
如果有多个连接都开启了事务，那么事务之间不能共享数据记录，否则只能共享已提交的记录。
SERIALIZABLE(串行化)
所有的事务都会按照固定顺序执行，执行完一个事务后再继续执行下一个事务的写入操作。

查看当前数据库的默认隔离级别：

--MySQL8.x,GLOBAL表示系统级别，不加表示会话级别。
SELECT@@GLOBAL.TRANSACTION_ISOLATION;
SELECT@@TRANSACTION_ISOLATION;
+--------------------------------+
|@@GLOBAL.TRANSACTION_ISOLATION|
+--------------------------------+
|REPEATABLE-READ|--MySQL的默认隔离级别，可以重复读。
+--------------------------------+

--MySQL5.x
SELECT@@GLOBAL.TX_ISOLATION;
SELECT@@TX_ISOLATION;

修改隔离级别：

--设置系统隔离级别，LEVEL后面表示要设置的隔离级别(READUNCOMMITTED)。
SETGLOBALTRANSACTIONISOLATIONLEVELREADUNCOMMITTED;

--查询系统隔离级别，发现已经被修改。
SELECT@@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
|@@GLOBAL.TRANSACTION_ISOLATION|
+--------------------------------+
|READ-UNCOMMITTED|
+--------------------------------+

脏读

测试READUNCOMMITTED(读取未提交)的隔离性：

INSERTINTOuserVALUES(3,'小明',1000);
INSERTINTOuserVALUES(4,'淘宝店',1000);

SELECT*FROMuser;
+----+-----------+-------+
|id|name|money|
+----+-----------+-------+
|1|a|900|
|2|b|1100|
|3|小明|1000|
|4|淘宝店|1000|
+----+-----------+-------+

--开启一个事务操作数据
--假设小明在淘宝店买了一双800块钱的鞋子：
STARTTRANSACTION;
UPDATEuserSETmoney=money-800WHEREname='小明';
UPDATEuserSETmoney=money+800WHEREname='淘宝店';

--然后淘宝店在另一方查询结果，发现钱已到账。
SELECT*FROMuser;
+----+-----------+-------+
|id|name|money|
+----+-----------+-------+
|1|a|900|
|2|b|1100|
|3|小明|200|
|4|淘宝店|1800|
+----+-----------+-------+

由于小明的转账是在新开启的事务上进行操作的，而该操作的结果是可以被其他事务（另一方的淘宝店）看见的，因此淘宝店的查询结果是正确的，淘宝店确认到账。但就在这时，如果小明在它所处的事务上又执行了ROLLBACK命令，会发生什么？

--小明所处的事务
ROLLBACK;

--此时无论对方是谁，如果再去查询结果就会发现：
SELECT*FROMuser;
+----+-----------+-------+
|id|name|money|
+----+-----------+-------+
|1|a|900|
|2|b|1100|
|3|小明|1000|
|4|淘宝店|1000|
+----+-----------+-------+

这就是所谓的脏读，一个事务读取到另外一个事务还未提交的数据。这在实际开发中是不允许出现的。

读取已提交

把隔离级别设置为READCOMMITTED：

SETGLOBALTRANSACTIONISOLATIONLEVELREADCOMMITTED;
SELECT@@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
|@@GLOBAL.TRANSACTION_ISOLATION|
+--------------------------------+
|READ-COMMITTED|
+--------------------------------+

这样，再有新的事务连接进来时，它们就只能查询到已经提交过的事务数据了。但是对于当前事务来说，它们看到的还是未提交的数据，例如：

--正在操作数据事务（当前事务）
STARTTRANSACTION;
UPDATEuserSETmoney=money-800WHEREname='小明';
UPDATEuserSETmoney=money+800WHEREname='淘宝店';

--虽然隔离级别被设置为了READCOMMITTED，但在当前事务中，
--它看到的仍然是数据表中临时改变数据，而不是真正提交过的数据。
SELECT*FROMuser;
+----+-----------+-------+
|id|name|money|
+----+-----------+-------+
|1|a|900|
|2|b|1100|
|3|小明|200|
|4|淘宝店|1800|
+----+-----------+-------+


--假设此时在远程开启了一个新事务，连接到数据库。
$mysql-uroot-p12345612

--此时远程连接查询到的数据只能是已经提交过的
SELECT*FROMuser;
+----+-----------+-------+
|id|name|money|
+----+-----------+-------+
|1|a|900|
|2|b|1100|
|3|小明|1000|
|4|淘宝店|1000|
+----+-----------+-------+

但是这样还有问题，那就是假设一个事务在操作数据时，其他事务干扰了这个事务的数据。例如：

--小张在查询数据的时候发现：
SELECT*FROMuser;
+----+-----------+-------+
|id|name|money|
+----+-----------+-------+
|1|a|900|
|2|b|1100|
|3|小明|200|
|4|淘宝店|1800|
+----+-----------+-------+

--在小张求表的money平均值之前，小王做了一个操作：
STARTTRANSACTION;
INSERTINTOuserVALUES(5,'c',100);
COMMIT;

--此时表的真实数据是：
SELECT*FROMuser;
+----+-----------+-------+
|id|name|money|
+----+-----------+-------+
|1|a|900|
|2|b|1100|
|3|小明|1000|
|4|淘宝店|1000|
|5|c|100|
+----+-----------+-------+

--这时小张再求平均值的时候，就会出现计算不相符合的情况：
SELECTAVG(money)FROMuser;
+------------+
|AVG(money)|
+------------+
|820.0000|
+------------+

虽然READCOMMITTED让我们只能读取到其他事务已经提交的数据，但还是会出现问题，就是在读取同一个表的数据时，可能会发生前后不一致的情况。这被称为不可重复读现象(READCOMMITTED)。

幻读

将隔离级别设置为REPEATABLEREAD(可被重复读取):

SETGLOBALTRANSACTIONISOLATIONLEVELREPEATABLEREAD;
SELECT@@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
|@@GLOBAL.TRANSACTION_ISOLATION|
+--------------------------------+
|REPEATABLE-READ|
+--------------------------------+

测试REPEATABLEREAD，假设在两个不同的连接上分别执行STARTTRANSACTION:

--小张-成都
STARTTRANSACTION;
INSERTINTOuserVALUES(6,'d',1000);

--小王-北京
STARTTRANSACTION;

--小张-成都
COMMIT;

当前事务开启后，没提交之前，查询不到，提交后可以被查询到。但是，在提交之前其他事务被开启了，那么在这条事务线上，就不会查询到当前有操作事务的连接。相当于开辟出一条单独的线程。

无论小张是否执行过COMMIT，在小王这边，都不会查询到小张的事务记录，而是只会查询到自己所处事务的记录：

SELECT*FROMuser;
+----+-----------+-------+
|id|name|money|
+----+-----------+-------+
|1|a|900|
|2|b|1100|
|3|小明|1000|
|4|淘宝店|1000|
|5|c|100|
+----+-----------+-------+

这是因为小王在此之前开启了一个新的事务(STARTTRANSACTION)，那么在他的这条新事务的线上，跟其他事务是没有联系的，也就是说，此时如果其他事务正在操作数据，它是不知道的。

然而事实是，在真实的数据表中，小张已经插入了一条数据。但是小王此时并不知道，也插入了同一条数据，会发生什么呢？

INSERTINTOuserVALUES(6,'d',1000);
--ERROR1062(23000):Duplicateentry'6'forkey'PRIMARY'

报错了，操作被告知已存在主键为6的字段。这种现象也被称为幻读，一个事务提交的数据，不能被其他事务读取到。

串行化

顾名思义，就是所有事务的写入操作全都是串行化的。什么意思？把隔离级别修改成SERIALIZABLE:

SETGLOBALTRANSACTIONISOLATIONLEVELSERIALIZABLE;
SELECT@@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
|@@GLOBAL.TRANSACTION_ISOLATION|
+--------------------------------+
|SERIALIZABLE|
+--------------------------------+

还是拿小张和小王来举例：

--小张-成都
STARTTRANSACTION;

--小王-北京
STARTTRANSACTION;

--开启事务之前先查询表，准备操作数据。
SELECT*FROMuser;
+----+-----------+-------+
|id|name|money|
+----+-----------+-------+
|1|a|900|
|2|b|1100|
|3|小明|1000|
|4|淘宝店|1000|
|5|c|100|
|6|d|1000|
+----+-----------+-------+

--发现没有7号王小花，于是插入一条数据：
INSERTINTOuserVALUES(7,'王小花',1000);

此时会发生什么呢？由于现在的隔离级别是SERIALIZABLE(串行化)，串行化的意思就是：假设把所有的事务都放在一个串行的队列中，那么所有的事务都会按照固定顺序执行，执行完一个事务后再继续执行下一个事务的写入操作(这意味着队列中同时只能执行一个事务的写入操作)。

根据这个解释，小王在插入数据时，会出现等待状态，直到小张执行COMMIT结束它所处的事务，或者出现等待超时。

更多关于MySQL相关内容感兴趣的读者可查看本站专题：《MySQL查询技巧大全》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》

希望本文所述对大家MySQL数据库计有所帮助。