作者
远辰
本文为「数据不吹牛」投稿
今天给大家分享的是我学习MySQL记录的详细笔记，有基础知识，也有实战案例，文章较长，建议收藏~
基本语法
--显示所有数据库showdatabases;--创建数据库CREATEDATABASEtest;--切换数据库usetest;--显示数据库中的所有表showtables;--创建数据表CREATETABLEpet(nameVARCHAR(20),ownerVARCHAR(20),speciesVARCHAR(20),sexCHAR(1),birthDATE,deathDATE);--查看数据表结构--describepet;descpet;--查询表SELECT*frompet;--插入数据INSERTINTOpetVALUES(puffball,Diane,hamster,f,-03-30,NULL);--修改数据UPDATEpetSETname=squirrelwhereowner=Diane;--删除数据DELETEFROMpetwherename=squirrel;--删除表DROPTABLEmyorder;
数据库大三大设计范式
1NF不可分割性，只要字段值还可以继续拆分，就不满足第一范式。
不可分割的意思就按字面理解就是最小单位，不能再分成更小单位了。
字段只能是一个值，不能被拆分成多个字段，否则的话，它就是可分割的，就不符合一范式。
2NF第二范式就是要有主键，要求其他字段都依赖于主键。
为什么要有主键？没有主键就没有唯一性，没有唯一性在集合中就定位不到这行记录，所以要主键。
在满足第一范式的前提下，主键外的每一列都必须完全依赖于主键。如果出现不完全依赖，只可能发生在联合主键的情况下。
3NF在满足第二范式的前提下，除了主键列之外，其他列之间不能有传递依赖关系，即“消除冗余”。
消除冗余，就是各种信息只在一个地方存储，不出现在多张表中。
范式总结：范式，其实是用来学习参考的，设计的时候根据情况，未必一定要遵守，要灵活结合业务实际情况决定。
查询练习
准备数据--创建数据库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(,曾华,男,-09-01,);INSERTINTOstudentVALUES(,匡明,男,-10-02,);INSERTINTOstudentVALUES(,王丽,女,-01-23,);INSERTINTOstudentVALUES(,李军,男,-02-20,);INSERTINTOstudentVALUES(,王芳,女,-02-10,);INSERTINTOstudentVALUES(,陆军,男,-06-03,);INSERTINTOstudentVALUES(,王尼玛,男,-02-20,);INSERTINTOstudentVALUES(,张全蛋,男,-02-10,);INSERTINTOstudentVALUES(,赵铁柱,男,-06-03,);--添加教师表数据INSERTINTOteacherVALUES(,李诚,男,-12-02,副教授,计算机系);INSERTINTOteacherVALUES(,张旭,男,-03-12,讲师,电子工程系);INSERTINTOteacherVALUES(,王萍,女,-05-05,助教,计算机系);INSERTINTOteacherVALUES(,刘冰,女,-08-14,助教,电子工程系);--添加课程表数据INSERTINTOcourseVALUES(3-,计算机导论,);INSERTINTOcourseVALUES(3-,操作系统,);INSERTINTOcourseVALUES(6-,数字电路,);INSERTINTOcourseVALUES(9-,高等数学,);--添加添加成绩表数据INSERTINTOscoreVALUES(,3-,92);INSERTINTOscoreVALUES(,3-,86);INSERTINTOscoreVALUES(,6-,85);INSERTINTOscoreVALUES(,3-,88);INSERTINTOscoreVALUES(,3-,75);INSERTINTOscoreVALUES(,6-,79);INSERTINTOscoreVALUES(,3-,76);INSERTINTOscoreVALUES(,3-,68);INSERTINTOscoreVALUES(,6-,81);--查看表结构SELECT*FROMcourse;SELECT*FROMscore;SELECT*FROMstudent;SELECT*FROMteacher;
基础查询--查询student表的所有行SELECT*FROMstudent;--查询student表中的name、sex和class字段的所有行SELECTname,sex,classFROMstudent;--查询teacher表中不重复的department列--department:去重查询SELECTDISTINCTdepartmentFROMteacher;--查询score表中成绩在60-80之间的所有行（区间查询和运算符查询）--BETWEENxxANDxx:查询区间,AND表示并且SELECT*FROMscoreWHEREdegreeBETWEEN60AND80;SELECT*FROMscoreWHEREdegree60ANDdegree80;--查询score表中成绩为85,86或88的行--IN:查询规定中的多个值SELECT*FROMscoreWHEREdegreeIN(85,86,88);--查询student表中班或性别为女的所有行--or:表示或者关系SELECT*FROMstudentWHEREclass=orsex=女;--以class降序的方式查询student表的所有行--DESC:降序，从高到低--ASC（默认）:升序，从低到高SELECT*FROMstudentORDERBYclassDESC;SELECT*FROMstudentORDERBYclassASC;--以c_no升序、degree降序查询score表的所有行SELECT*FROMscoreORDERBYc_noASC,degreeDESC;--查询班的学生人数--COUNT:统计SELECTCOUNT(*)FROMstudentWHEREclass=;--查询score表中的最高分的学生学号和课程编号（子查询或排序查询）。--(SELECTMAX(degree)FROMscore):子查询，算出最高分SELECTs_no,c_noFROMscoreWHEREdegree=(SELECTMAX(degree)FROMscore);--排序查询--LIMITr,n:表示从第r行开始，查询n条数据SELECTs_no,c_no,degreeFROMscoreORDERBYdegreeDESCLIMIT0,1;--LIMITnoffsetr:表示查询n条数据,从第r行开始SELECTs_no,c_no,degreeFROMscoreORDERBYdegreeDESCLIMIT1offse
分组计算平均成绩查询每门课的平均成绩
--AVG:平均值SELECTAVG(degree)FROMscoreWHEREc_no=3-;SELECTAVG(degree)FROMscoreWHEREc_no=3-;SELECTAVG(degree)FROMscoreWHEREc_no=6-;--GROUPBY:分组查询SELECTc_no,AVG(degree)FROMscoreGROUPBYc_no;
分组条件与模糊查询查询score表中至少有2名学生选修，并以3开头的课程的平均分数
分析表发现，至少有2名学生选修的课程是3-、3-、6-，以3开头的课程是3-、3-。也就是说，我们要查询所有3-和3-的degree平均分。
--首先把c_no,AVG(degree)通过分组查询出来SELECTc_no,AVG(degree)FROMscoreGROUPBYc_no+-------+-------------+
c_no
AVG(degree)
+-------+-------------+
3-
85.
3-
76.
6-
81.
+-------+-------------+--再查询出至少有2名学生选修的课程--HAVING:表示持有HAVINGCOUNT(c_no)=2--并且是以3开头的课程--LIKE表示模糊查询，%是一个通配符，匹配3后面的任意字符。ANDc_noLIKE3%;--把前面的SQL语句拼接起来，--后面加上一个COUNT(*)，表示将每个分组的个数也查询出来。SELECTc_no,AVG(degree),COUNT(*)FROMscoreGROUPBYc_noHAVINGCOUNT(c_no)=2ANDc_noLIKE3%;+-------+-------------+----------+
c_no
AVG(degree)
COUNT(*)
+-------+-------------+----------+
3-
85.
3
3-
76.
3
+-------+-------------+----------+
多表查询-1查询所有学生的name，以及该学生在score表中对应的c_no和degree
SELECTno,nameFROMstudent;+-----+-----------+
no
name
+-----+-----------+
曾华
匡明
王丽
李军
王芳
陆军
王尼玛
张全蛋
赵铁柱
+-----+-----------+SELECTs_no,c_no,degreeFROMscore;+------+-------+--------+
s_no
c_no
degree
+------+-------+--------+
3-
92
3-
86
6-
85
3-
88
3-
75
6-
79
3-
76
3-
68
6-
81
+------+-------+--------+
通过分析可以发现，只要把score表中的s_no字段值替换成student表中对应的name字段值就可以了，如何做呢？
--FROM...:表示从student,score表中查询--WHERE的条件表示为，只有在student.no和score.s_no相等时才显示出来。SELECTname,c_no,degreeFROMstudent,scoreWHEREstudent.no=score.s_no;+-----------+-------+--------+
name
c_no
degree
+-----------+-------+--------+
王丽
3-
92
王丽
3-
86
王丽
6-
85
王芳
3-
88
王芳
3-
75
王芳
6-
79
赵铁柱
3-
76
赵铁柱
3-
68
赵铁柱
6-
81
+-----------+-------+--------+
多表查询-2查询所有学生的no、课程名称(course表中的name)和成绩(score表中的degree)列。
只有score关联学生的no，因此只要查询score表，就能找出所有和学生相关的no和degree：
SELECTs_no,c_no,degreeFROMscore;+------+-------+--------+
s_no
c_no
degree
+------+-------+--------+
3-
92
3-
86
6-
85
3-
88
3-
75
6-
79
3-
76
3-
68
6-
81
+------+-------+--------+
然后查询course表：
SELECTno,nameFROMcourse;+-------+-----------------+
no
name
+-------+-----------------+
3-
计算机导论
3-
操作系统
6-
数字电路
9-
高等数学
+-------+-----------------+
只要把score表中的c_no替换成course表中对应的name字段值就可以了。
--增加一个查询字段name，分别从score、course这两个表中查询。--as表示取一个该字段的别名。SELECTs_no,nameasc_name,degreeFROMscore,courseWHEREscore.c_no=course.no;+------+-----------------+--------+
s_no
c_name
degree
+------+-----------------+--------+
计算机导论
92
计算机导论
88
计算机导论
76
操作系统
86
操作系统
75
操作系统
68
数字电路
85
数字电路
79
数字电路
81
+------+-----------------+--------+
三表关联查询查询所有学生的name、课程名(course表中的name)和degree。
只有score表中关联学生的学号和课堂号，我们只要围绕着score这张表查询就好了。
SELECT*FROMscore;+------+-------+--------+
s_no
c_no
degree
+------+-------+--------+
3-
92
3-
86
6-
85
3-
88
3-
75
6-
79
3-
76
3-
68
6-
81
+------+-------+--------+
只要把s_no和c_no替换成student和course表中对应的name字段值就好了。
首先把s_no替换成student表中的name字段：
SELECTname,c_no,degreeFROMstudent,scoreWHEREstudent.no=score.s_no;+-----------+-------+--------+
name
c_no
degree
+-----------+-------+--------+
王丽
3-
92
王丽
3-
86
王丽
6-
85
王芳
3-
88
王芳
3-
75
王芳
6-
79
赵铁柱
3-
76
赵铁柱
3-
68
赵铁柱
6-
81
+-----------+-------+--------+
再把c_no替换成course表中的name字段：
--课程表SELECTno,nameFROMcourse;+-------+-----------------+
no
name
+-------+-----------------+
3-
计算机导论
3-
操作系统
6-
数字电路
9-
高等数学
+-------+-----------------+--由于字段名存在重复，使用表名.字段名as别名代替。SELECTstudent.nameass_name,course.nameasc_name,degreeFROMstudent,score,courseWHEREstudent.NO=score.s_noANDscore.c_no=course.no;
子查询加分组求平均分查询班学生每门课程的平均成绩。
在score表中根据student表的学生编号筛选出学生的课堂号和成绩：
--IN(..):将筛选出的学生号当做s_no的条件查询SELECTs_no,c_no,degreeFROMscoreWHEREs_noIN(SELECTnoFROMstudentWHEREclass=);+------+-------+--------+
s_no
c_no
degree
+------+-------+--------+
3-
88
3-
75
6-
79
3-
76
3-
68
6-
81
+------+-------+--------+
这时只要将c_no分组一下就能得出班学生每门课的平均成绩：
SELECTc_no,AVG(degree)FROMscoreWHEREs_noIN(SELECTnoFROMstudentWHEREclass=)GROUPBYc_no;+-------+-------------+
c_no
AVG(degree)
+-------+-------------+
3-
82.
3-
71.
6-
80.
+-------+-------------+
子查询-1查询在3-课程中，所有成绩高于号同学的记录。
先用子查询查找出同学在3-中的成绩
select*fromscorewherec_no=3-ands_no=
然后再以课程3-为条件，查找成绩大76的记录
select*fromscorewherec_no=3-anddegree(selectdegreefromscorewherec_no=3-ands_no=);
子查询-2查询所有成绩高于号同学的3-课程成绩记录。
--不限制课程号，只要成绩大于号同学的3-课程成绩就可以。SELECT*FROMscoreWHEREdegree(SELECTdegreeFROMscoreWHEREs_no=ANDc_no=3-);
YEAR函数与带IN关键字查询查询所有和、号学生同年出生的no、name、birthday列。
selectno,name,birthdayfromstudentwhereyear(birthday)in(selectyear(birthday)fromstudentwherenoin(,));
多层嵌套子查询查询张旭教师任课的学生成绩表。
用的三张表teacher、course、score，首先找到教师编号：
SELECTNOFROMteacherWHERENAME=张旭
通过sourse表找到该教师课程号：
selectnofromcoursewheret_no=(SELECTNOFROMteacherWHERENAME=张旭)
通过筛选出的课程号查询成绩表：
select*fromscorewherec_no=(selectnofromcoursewheret_no=(selectnofromteacherwherename=张旭));
多表查询查询某选修课程多于5个同学的教师姓名。
首先在teacher表中，根据no字段来判断该教师的同一门课程是否有至少5名学员选修：
--查询teacher表SELECTno,nameFROMteacher;+-----+--------+
no
name
+-----+--------+
李诚
王萍
刘冰
张旭
+-----+--------+SELECTnameFROMteacherWHEREnoIN(--在这里找到对应的条件);
查看和教师编号有有关的表的信息：
SELECT*FROMcourse;--t_no:教师编号+-------+-----------------+------+
no
name
t_no
+-------+-----------------+------+
3-
计算机导论
3-
操作系统
6-
数字电路
9-
高等数学
+-------+-----------------+------+
我们已经找到和教师编号有关的字段就在course表中，但是还无法知道哪门课程至少有5名学生选修，所以还需要根据score表来查询：
--在此之前向score插入一些数据，以便丰富查询条件。INSERTINTOscoreVALUES(,3-,90);INSERTINTOscoreVALUES(,3-,91);INSERTINTOscoreVALUES(,3-,89);--查询score表SELECT*FROMscore;+------+-------+--------+
s_no
c_no
degree
+------+-------+--------+
3-
90
3-
91
3-
92
3-
86
6-
85
3-
89
3-
88
3-
75
6-
79
3-
76
3-
68
6-
81
+------+-------+--------+--在score表中将c_no作为分组，并且限制c_no持有至少5条数据。SELECTc_noFROMscoreGROUPBYc_noHAVINGCOUNT(*)5;+-------+
c_no
+-------+
3-
+-------+
根据筛选出来的课程号，找出在某课程中，拥有至少5名学员的教师编号：
SELECTt_noFROMcourseWHEREnoIN(SELECTc_noFROMscoreGROUPBYc_noHAVINGCOUNT(*)5);+------+
t_no
+------+
+------+在teacher表中，根据筛选出来的教师编号找到教师姓名：SELECTnameFROMteacherWHEREnoIN(--最终条件SELECTt_noFROMcourseWHEREnoIN(SELECTc_noFROMscoreGROUPBYc_noHAVINGCOUNT(*)5));
子查询-3查询“计算机系”课程的成绩表。
思路是，先找出teacher表中所有计算机系课程的编号，根据这个编号查询course表中的课程编号，再用课程编号查找score表
--通过teacher表查询所有`计算机系`的教师编号SELECTno,name,departmentFROMteacherWHEREdepartment=计算机系+-----+--------+--------------+
no
name
department
+-----+--------+--------------+
李诚
计算机系
王萍
计算机系
+-----+--------+--------------+--通过course表查询该教师的课程编号SELECTnoFROMcourseWHEREt_noIN(SELECTnoFROMteacherWHEREdepartment=计算机系);+-------+
no
+-------+
3-
3-
+-------+--根据筛选出来的课程号查询成绩表SELECT*FROMscoreWHEREc_noIN(SELECTnoFROMcourseWHEREt_noIN(SELECTnoFROMteacherWHEREdepartment=计算机系));+------+-------+--------+
s_no
c_no
degree
+------+-------+--------+
3-
86
3-
75
3-
68
3-
90
3-
91
3-
92
3-
89
3-
88
3-
76
+------+-------+--------+
UNION和NOTIN的使用查询计算机系与电子工程系中的不同职称的教师。
+-----+------+-----+------------+------------+------------+
no
name
sex
birthday
profession
department
+-----+------+-----+------------+------------+------------+
李诚
男
-12-02
副教授
计算机系
王萍
女
-05-05
助教
计算机系
刘冰
女
-08-14
助教
电子工程系
张旭
男
-03-12
讲师
电子工程系
+-----+------+-----+------------+------------+------------+--NOT:代表逻辑非SELECT*FROMteacherWHEREdepartment=计算机系ANDprofessionNOTIN(SELECTprofessionFROMteacherWHEREdepartment=电子工程系)--合并两个集UNIONSELECT*FROMteacherWHEREdepartment=电子工程系ANDprofessionNOTIN(SELECTprofessionFROMteacherWHEREdepartment=计算机系);
ANY表示至少一个-DESC(降序)查询课程3-且成绩至少高于3-的score表。
SELECT*FROMscoreWHEREc_no=3-;+------+-------+--------+
s_no
c_no
degree
+------+-------+--------+
3-
90
3-
91
3-
92
3-
89
3-
88
3-
76
+------+-------+--------+SELECT*FROMscoreWHEREc_no=3-;+------+-------+--------+
s_no
c_no
degree
+------+-------+--------+
3-
86
3-
75
3-
68
+------+-------+--------+--ANY:符合SQL语句中的任意条件。--也就是说，在3-成绩中，只要有一个大于从3-筛选出来的任意行就符合条件，--最后根据降序查询结果。SELECT*FROMscoreWHEREc_no=3-ANDdegreeANY(SELECTdegreeFROMscoreWHEREc_no=3-)ORDERBYdegreeDESC;+------+-------+--------+
s_no
c_no
degree
+------+-------+--------+
3-
92
3-
91
3-
90
3-
89
3-
88
3-
76
+------+-------+--------+
表示所有的ALL查询课程3-且成绩高于3-的score表。
--只需对上一道题稍作修改。--ALL:符合SQL语句中的所有条件。--也就是说，在3-每一行成绩中，都要大于从3-筛选出来全部行才算符合条件。SELECT*FROMscoreWHEREc_no=3-ANDdegreeALL(SELECTdegreeFROMscoreWHEREc_no=3-);+------+-------+--------+
s_no
c_no
degree
+------+-------+--------+
3-
90
3-
91
3-
92
3-
89
3-
88
+------+-------+--------+
复制表的数据作为条件查询查询某课程成绩比该课程平均成绩低的score表。
--查询平均分SELECTc_no,AVG(degree)FROMscoreGROUPBYc_no;b表+-------+-------------+
c_no
AVG(degree)
+-------+-------------+
3-
87.
3-
76.
6-
81.
+-------+-------------+--查询score表select*fromscore;a表+------+-------+--------+
s_no
c_no
degree
+------+-------+--------+
3-
90
3-
91
3-
92
3-
86
6-
85
3-
89
3-
88
3-
75
6-
79
3-
76
3-
68
6-
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
+------+-------+--------+
3-
75
6-
79
3-
76
3-
68
6-
81
+------+-------+--------+
子查询-4查询所有任课(在course表里有课程)教师的name和department。
SELECTname,departmentFROMteacherWHEREnoIN(SELECTt_noFROMcourse);+--------+-----------------+
name
department
+--------+-----------------+
李诚
计算机系
王萍
计算机系
刘冰
电子工程系
张旭
电子工程系
+--------+-----------------+
条件加组筛选查询student表中至少有2名男生的class。
--查看学生表信息SELECT*FROMstudent;+-----+-----------+-----+------------+-------+
no
name
sex
birthday
class
+-----+-----------+-----+------------+-------+
曾华
男
-09-01
匡明
男
-10-02
王丽
女
-01-23
李军
男
-02-20
王芳
女
-02-10
陆军
男
-06-03
王尼玛
男
-02-20
张全蛋
男
-02-10
赵铁柱
男
-06-03
张飞
男
-06-03
+-----+-----------+-----+------------+-------+--只查询性别为男，然后按class分组，并限制class行大于1。select*fromstudentwheresex=男groupbyclasshavingcount(class)=2；+-----+------+-----+------------+-------+
no
name
sex
birthday
class
+-----+------+-----+------------+-------+
曾华
男
-09-01
匡明
男
-10-02
+-----+------+-----+------------+-------+
NOTLIKE模糊查询取反查询student表中不姓王的同学记录。
--NOT:取反--LIKE:模糊查询mysqlSELECT*FROMstudentWHEREnameNOTLIKE王%;+-----+-----------+-----+------------+-------+
no
name
sex
birthday
class
+-----+-----------+-----+------------+-------+
曾华
男
-09-01
匡明
男
-10-02
李军
男
-02-20
陆军
男
-06-03
张全蛋
男
-02-10
赵铁柱
男
-06-03
张飞
男
-06-03
+-----+-----------+-----+------------+-------+
YEAR与NOW函数查询student表中每个学生的姓名和年龄。
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)
+---------------+---------------+
-09-01
-06-03
+---------------+---------------+
多段排序以class和birthday从大到小的顺序查询student表。
SELECT*FROMstudentORDERBYclassDESC,birthdayDESC;+-----+--------+-----+------------+-------+
no
name
sex
birthday
class
+-----+--------+-----+------------+-------+
张飞
男
-06-03
曾华
男
-09-01
李军
男
-02-20
王尼玛
男
-02-20
王丽
女
-01-23
匡明
男
-10-02
王芳
女
-02-10
张全蛋
男
-02-10
陆军
男
-06-03
赵铁柱
男
-06-03
+-----+--------+-----+------------+-------+
子查询-5查询男教师及其所上的课程。
SELECT*FROMcourseWHEREt_noin(SELECTnoFROMteacherWHEREsex=男);+-------+--------------+------+
no
name
t_no
+-------+--------------+------+
3-
操作系统
6-
数字电路
+-------+--------------+------+
MAX函数与子查询查询最高分同学的score表。
--找出最高成绩（该查询只能有一个结果）SELECTMAX(degree)FROMscore;--根据上面的条件筛选出所有最高成绩表，--该查询可能有多个结果，假设degree值多次符合条件。SELECT*FROMscoreWHEREdegree=(SELECTMAX(degree)FROMscore);+------+-------+--------+
s_no
c_no
degree
+------+-------+--------+
3-
92
+------+-------+--------+
子查询-6查询和李军同性别的所有同学name。
selectnamefromstudentwheresex=(SELECTsexFROMstudentwherename=李军);+--------+
name
+--------+
曾华
匡明
李军
陆军
王尼玛
张全蛋
赵铁柱
张飞
+--------+
子查询-7查询和李军同性别且同班的同学name。
SELECTname,sex,classFROMstudentWHEREsex=(SELECTsexFROMstudentWHEREname=李军)ANDclass=(SELECTclassFROMstudentWHEREname=李军);+-----------+-----+-------+
name
sex
class
+-----------+-----+-------+
曾华
男
李军
男
王尼玛
男
+-----------+-----+-------+
子查询-8查询所有选修计算机导论课程的男同学成绩表。
需要的计算机导论和性别为男的编号可以在course和student表中找到。
SELECT*FROMscoreWHEREc_no=(SELECTnoFROMcourseWHEREname=计算机导论)ANDs_noIN(SELECTnoFROMstudentWHEREsex=男);+------+-------+--------+
s_no
c_no
degree
+------+-------+--------+
3-
90
3-
91
3-
89
3-
76
+------+-------+--------+
按等级查询建立一个grade表代表学生的成绩等级，并插入数据：
CREATETABLEgrade(lowINT(3),uppINT(3),gradechar(1));INSERTINTOgradeVALUES(90,,A);INSERTINTOgradeVALUES(80,89,B);INSERTINTOgradeVALUES(70,79,C);INSERTINTOgradeVALUES(60,69,D);INSERTINTOgradeVALUES(0,59,E);SELECT*FROMgrade;+------+------+-------+
low
upp
grade
+------+------+-------+
90
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,gradeWHEREdegreeBETWEENlowANDupp;+------+-------+-------+
s_no
c_no
grade
+------+-------+-------+
3-
A
3-
A
3-
A
3-
B
6-
B
3-
B
3-
B
3-
C
6-
C
3-
C
3-
D
6-
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;--出现错误：--ERROR4(42S22):Unknowncolumnperson.cardIdinonclause--MySQL全连接语法，使用UNION将两张表合并在一起。SELECT*FROMpersonLEFTJOINcardonperson.cardId=card.idUNIONSELECT*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
邮政卡
+------+--------+--------+------+-----------+
全文到这里撒花完结~