本文主要以《数据库系统概论》教材里的例子为例教材里的 SQL 比较广泛不只是针对 MySQL但大部分概念都是适用的正好来一波满满的回忆杀为什么选择学生表、课程表和选课表作为例子呢原因也简单主要是因为这三张表能将大部分的查询示例都覆盖掉而且理解方面也容易。数据表创建学生表CREATE TABLE student ( Sno char(9) NOT NULL, Sname char(20) DEFAULT NULL, Ssex char(2) DEFAULT NULL, Sage smallint(6) DEFAULT NULL, Sdept char(20) DEFAULT NULL, PRIMARY KEY (Sno), UNIQUE KEY Sname (Sname) );课程表CREATE TABLE course ( Cno char(4) NOT NULL, Cname char(40) NOT NULL, Cpno char(4) DEFAULT NULL, Ccredit smallint(6) DEFAULT NULL, PRIMARY KEY (Cno), KEY Cpno (Cpno), CONSTRAINT course_ibfk_1 FOREIGN KEY (Cpno) REFERENCES course (cno) );选课表CREATE TABLE sc ( Sno char(9) NOT NULL, Cno char(4) NOT NULL, Grade smallint(6) DEFAULT NULL, PRIMARY KEY (Sno,Cno), KEY Cno (Cno), CONSTRAINT sc_ibfk_1 FOREIGN KEY (Sno) REFERENCES student (sno), CONSTRAINT sc_ibfk_2 FOREIGN KEY (Cno) REFERENCES course (cno) );数据插入学生表INSERT INTO student VALUES (201215121, 李勇, 男, 20, CS); INSERT INTO student VALUES (201215122, 刘晨, 女, 19, CS); INSERT INTO student VALUES (201215123, 王敏, 女, 18, MA); INSERT INTO student VALUES (201215125, 张立, 男, 19, IS);课程表INSERT INTO course VALUES (1, 数据库, 5, 4); INSERT INTO course VALUES (2, 数学, NULL, 2); INSERT INTO course VALUES (3, 信息系统, 1, 4); INSERT INTO course VALUES (4, 操作系统, 6, 3); INSERT INTO course VALUES (5, 数据结构, 7, 4); INSERT INTO course VALUES (6, 数据处理, NULL, 2); INSERT INTO course VALUES (7, PASCAL语言, 6, 4);注意课程表数据插入时可能会受外键约束影响导致无法插入数据可先将 Cpno 字段设为空记录插入后再使用 update 进行修改。INSERT INTO course VALUES (1, 数据库, NULL, 4); INSERT INTO course VALUES (2, 数学, NULL, 2); INSERT INTO course VALUES (3, 信息系统, NULL, 4); INSERT INTO course VALUES (4, 操作系统, NULL, 3); INSERT INTO course VALUES (5, 数据结构, NULL, 4); INSERT INTO course VALUES (6, 数据处理, NULL, 2); INSERT INTO course VALUES (7, PASCAL语言, NULL, 4); UPDATE course SET Cpno5 WHERE Cno1; UPDATE course SET Cpno1 WHERE Cno3; UPDATE course SET Cpno6 WHERE Cno4; UPDATE course SET Cpno7 WHERE Cno5; UPDATE course SET Cpno6 WHERE Cno7;选课表注意选课表的外键约束与上同理因此需要先插入好 学生表 和 课程表 的数据。INSERT INTO sc VALUES (201215121, 1, 92); INSERT INTO sc VALUES (201215121, 2, 85); INSERT INTO sc VALUES (201215121, 3, 88); INSERT INTO sc VALUES (201215122, 2, 90); INSERT INTO sc VALUES (201215122, 3, 80);数据查询常用查询条件单表查询普通查询select Sno, Sname from Student;mysql select Sno, Sname from Student; ------------------- | Sno | Sname | ------------------- | 201215122 | 刘晨 | | 201215125 | 张立 | | 201215121 | 李勇 | | 201215123 | 王敏 | ------------------- 4 rows in set (0.00 sec)查询计算后的值select Sname, 2021 - Sage from student;mysql select Sname, 2021 - Sage from student; --------------------- | Sname | 2021 - Sage | --------------------- | 李勇 | 2001 | | 刘晨 | 2002 | | 王敏 | 2003 | | 张立 | 2002 | --------------------- 4 rows in set (0.00 sec)字段设置别名select Sname, 2021 - Sage as birth from student;mysql select Sname, 2021 - Sage as birth from student; --------------- | Sname | birth | --------------- | 李勇 | 2001 | | 刘晨 | 2002 | | 王敏 | 2003 | | 张立 | 2002 | --------------- 4 rows in set (0.00 sec)查询结果去重select distinct Sno from SC;mysql select distinct Sno from SC; ----------- | Sno | ----------- | 201215121 | | 201215122 | ----------- 2 rows in set (0.00 sec)where条件查询select Sname from Student where SdeptCS;mysql select Sname from Student where SdeptCS; -------- | Sname | -------- | 李勇 | | 刘晨 | -------- 2 rows in set (0.00 sec)select Sname, Sage from Student where Sage20;mysql select Sname, Sage from Student where Sage20; -------------- | Sname | Sage | -------------- | 刘晨 | 19 | | 王敏 | 18 | | 张立 | 19 | -------------- 3 rows in set (0.00 sec)select distinct Sno from SC where Grade60;mysql select distinct Sno from SC where Grade60; ----------- | Sno | ----------- | 201215121 | | 201215122 | ----------- 2 rows in set (0.00 sec)between ... andselect Sname, Sdept, Sage from Student where Sage between 20 and 23;mysql select Sname, Sdept, Sage from Student where Sage between 20 and 23; --------------------- | Sname | Sdept | Sage | --------------------- | 李勇 | CS | 20 | --------------------- 1 row in set (0.00 sec)inselect Sname, Ssex from Student where Sdept in (CS, MA, IS);mysql select Sname, Ssex from Student where Sdept in (CS, MA, IS); -------------- | Sname | Ssex | -------------- | 李勇 | 男 | | 刘晨 | 女 | | 王敏 | 女 | | 张立 | 男 | -------------- 4 rows in set (0.00 sec)not inselect Sname, Ssex from Student where Sdept not in (CS, MA, IS);mysql select Sname, Ssex from Student where Sdept not in (CS, MA, IS); Empty set (0.00 sec)likeselect Sname, Sno, Ssex from Student where Sname like 刘%;mysql select Sname, Sno, Ssex from Student where Sname like 刘%; ------------------------- | Sname | Sno | Ssex | ------------------------- | 刘晨 | 201215122 | 女 | ------------------------- 1 row in set (0.00 sec)select Sname, Sno, Ssex from Student where Sname like 欧阳_;mysql select Sname, Sno, Ssex from Student where Sname like 欧阳_; Empty set (0.00 sec)nullselect Sno, Cno from SC where Grade is not null;orselect Sname from Student where SdeptCS or SdeptMA or SdeptIS;mysql select Sname from Student where SdeptCS or SdeptMA or SdeptIS; -------- | Sname | -------- | 李勇 | | 刘晨 | | 王敏 | | 张立 | -------- 4 rows in set (0.00 sec)order byselect Sno, Grade from SC where Cno3 order by Grade desc;mysql select Sno, Grade from SC where Cno3 order by Grade desc; ------------------ | Sno | Grade | ------------------ | 201215121 | 88 | | 201215122 | 80 | ------------------ 2 rows in set (0.00 sec)order by 多重排序select * from Student order by Sdept, Sage desc;mysql select * from Student order by Sdept, Sage desc; -------------------------------------- | Sno | Sname | Ssex | Sage | Sdept | -------------------------------------- | 201215121 | 李勇 | 男 | 20 | CS | | 201215122 | 刘晨 | 女 | 19 | CS | | 201215125 | 张立 | 男 | 19 | IS | | 201215123 | 王敏 | 女 | 18 | MA | -------------------------------------- 4 rows in set (0.00 sec)聚合查询常用聚合函数countselect count(*) from Student;mysql select count(*) from Student; ---------- | count(*) | ---------- | 4 | ---------- 1 row in set (0.00 sec)select count(distinct Sno) from SC;mysql select count(distinct Sno) from SC; --------------------- | count(distinct Sno) | --------------------- | 2 | --------------------- 1 row in set (0.00 sec)avgselect avg(Grade) from SC where Cno1;mysql select avg(Grade) from SC where Cno1; ------------ | avg(Grade) | ------------ | 92.0000 | ------------ 1 row in set (0.00 sec)maxselect max(Grade) from SC where Cno1;mysql select max(Grade) from SC where Cno1; ------------ | max(Grade) | ------------ | 92 | ------------ 1 row in set (0.00 sec)sumselect sum(Ccredit) from SC, Course where Sno201215122 and SC.CnoCourse.Cno;mysql select sum(Ccredit) from SC, Course where Sno201215122 and SC.CnoCourse.Cno; -------------- | sum(Ccredit) | -------------- | 6 | -------------- 1 row in set (0.00 sec)GROUP BYgroup by 子句将查询结果按某一列或多列的值分组值相等的为一组。select Cno, Count(Sno) from SC group by Cno;mysql select Cno, Count(Sno) from SC group by Cno; ----------------- | Cno | Count(Sno) | ----------------- | 1 | 1 | | 2 | 2 | | 3 | 2 | ----------------- 3 rows in set (0.00 sec)分组后可使用 having 指定筛选条件比如查询每个选课学生选课的平均成绩。select Sname, avg(Grade) from Student, SC where SC.SnoStudent.Sno group by SC.Sno;mysql select Sname, avg(Grade) from Student, SC where SC.SnoStudent.Sno group by SC.Sno; -------------------- | Sname | avg(Grade) | -------------------- | 李勇 | 88.3333 | | 刘晨 | 85.0000 | -------------------- 2 rows in set (0.00 sec)查询平均成绩大于 85 的学生。select Sno, avg(Grade) from SC group by Sno having avg(Grade)85;mysql select Sno, avg(Grade) from SC group by Sno having avg(Grade)85; ----------------------- | Sno | avg(Grade) | ----------------------- | 201215121 | 88.3333 | ----------------------- 1 row in set (0.00 sec)连接查询既然叫连接查询那么也就是属于多表查询的范畴了。select Student.*, SC.* from Student, SC where Student.SnoSC.Sno;mysql select Student.*, SC.* from Student, SC where Student.SnoSC.Sno; ------------------------------------------------------------- | Sno | Sname | Ssex | Sage | Sdept | Sno | Cno | Grade | ------------------------------------------------------------- | 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 1 | 92 | | 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 2 | 85 | | 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 3 | 88 | | 201215122 | 刘晨 | 女 | 19 | CS | 201215122 | 2 | 90 | | 201215122 | 刘晨 | 女 | 19 | CS | 201215122 | 3 | 80 | ------------------------------------------------------------- 5 rows in set (0.00 sec)交叉连接如果不添加限制条件的话本质上就是两个表的笛卡尔积产生的表比较大慎用。select Student.*, SC.* from Student, SC;mysql select Student.*, SC.* from Student, SC; ------------------------------------------------------------- | Sno | Sname | Ssex | Sage | Sdept | Sno | Cno | Grade | ------------------------------------------------------------- | 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 1 | 92 | | 201215122 | 刘晨 | 女 | 19 | CS | 201215121 | 1 | 92 | | 201215123 | 王敏 | 女 | 18 | MA | 201215121 | 1 | 92 | | 201215125 | 张立 | 男 | 19 | IS | 201215121 | 1 | 92 | | 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 2 | 85 | | 201215122 | 刘晨 | 女 | 19 | CS | 201215121 | 2 | 85 | | 201215123 | 王敏 | 女 | 18 | MA | 201215121 | 2 | 85 | | 201215125 | 张立 | 男 | 19 | IS | 201215121 | 2 | 85 | | 201215121 | 李勇 | 男 | 20 | CS | 201215121 | 3 | 88 | | 201215122 | 刘晨 | 女 | 19 | CS | 201215121 | 3 | 88 | | 201215123 | 王敏 | 女 | 18 | MA | 201215121 | 3 | 88 | | 201215125 | 张立 | 男 | 19 | IS | 201215121 | 3 | 88 | | 201215121 | 李勇 | 男 | 20 | CS | 201215122 | 2 | 90 | | 201215122 | 刘晨 | 女 | 19 | CS | 201215122 | 2 | 90 | | 201215123 | 王敏 | 女 | 18 | MA | 201215122 | 2 | 90 | | 201215125 | 张立 | 男 | 19 | IS | 201215122 | 2 | 90 | | 201215121 | 李勇 | 男 | 20 | CS | 201215122 | 3 | 80 | | 201215122 | 刘晨 | 女 | 19 | CS | 201215122 | 3 | 80 | | 201215123 | 王敏 | 女 | 18 | MA | 201215122 | 3 | 80 | | 201215125 | 张立 | 男 | 19 | IS | 201215122 | 3 | 80 | ------------------------------------------------------------- 20 rows in set (0.00 sec)等值连接select Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade from Student, SC where Student.SnoSC.Sno;mysql select Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade from Student, SC where Student.SnoSC.Sno; -------------------------------------------------- | Sno | Sname | Ssex | Sage | Sdept | Cno | Grade | -------------------------------------------------- | 201215121 | 李勇 | 男 | 20 | CS | 1 | 92 | | 201215121 | 李勇 | 男 | 20 | CS | 2 | 85 | | 201215121 | 李勇 | 男 | 20 | CS | 3 | 88 | | 201215122 | 刘晨 | 女 | 19 | CS | 2 | 90 | | 201215122 | 刘晨 | 女 | 19 | CS | 3 | 80 | -------------------------------------------------- 5 rows in set (0.00 sec)内连接内连接INNER JOIN根据连接谓词结合两个表table1 和 table2的列值来创建一个新的结果表。查询会把 table1 中的每一行与 table2 中的每一行进行比较找到所有满足连接谓词的行的匹配对。上面的例子用 using 关键字也是可以的哟usring(Sno) 表示使用两个表的相同字段 Sno 作为连接条件。select * from admin inner join user on admin.name user.name 类似于 select * from admin inner join user on using(name)select Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade from Student inner join SC using(Sno);mysql select Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade from Student inner join SC using(Sno); -------------------------------------------------- | Sno | Sname | Ssex | Sage | Sdept | Cno | Grade | -------------------------------------------------- | 201215121 | 李勇 | 男 | 20 | CS | 1 | 92 | | 201215121 | 李勇 | 男 | 20 | CS | 2 | 85 | | 201215121 | 李勇 | 男 | 20 | CS | 3 | 88 | | 201215122 | 刘晨 | 女 | 19 | CS | 2 | 90 | | 201215122 | 刘晨 | 女 | 19 | CS | 3 | 80 | -------------------------------------------------- 5 rows in set (0.00 sec)内连接与等值连接区别看定义和语法是否和等值连接是那么一会事儿其实是一回事情等效可以查看等值连接一般用where字句设置条件内连接一般用on字句设置条件但内连接与等值连接效果是相同的。内连接与自然连接区别内连接与自然连接基本相同不同之处在于自然连接只能是同名属性的等值连接而内连接可以使用using或on子句来指定连接条件连接条件中指出某两字段相等可以不同名自然连接我们可以看到上面的连接有重复的列。若等值连接把目标列中重复的属性列去掉则为自然连接。内连接与自然连接比较像只不过自然连接只考虑同名属性内连接则不要求必须为同名属性列用 on 关键字选择共同属性如等值连接。select Student.*, SC.* from Student natural join SC;mysql select Student.*, SC.* from Student natural join SC; -------------------------------------------------- | Sno | Sname | Ssex | Sage | Sdept | Cno | Grade | -------------------------------------------------- | 201215121 | 李勇 | 男 | 20 | CS | 1 | 92 | | 201215121 | 李勇 | 男 | 20 | CS | 2 | 85 | | 201215121 | 李勇 | 男 | 20 | CS | 3 | 88 | | 201215122 | 刘晨 | 女 | 19 | CS | 2 | 90 | | 201215122 | 刘晨 | 女 | 19 | CS | 3 | 80 | -------------------------------------------------- 5 rows in set (0.00 sec)查询选修 2 号课程且成绩 85 分以上的学生的学号和姓名。select Student.Sno, Sname from Student, SC where Student.SnoSC.Sno and SC.Cno2 and SC.Grade85;mysql select Student.Sno, Sname from Student, SC where Student.SnoSC.Sno and SC.Cno2 and SC.Grade85; ------------------- | Sno | Sname | ------------------- | 201215122 | 刘晨 | ------------------- 1 row in set (0.00 sec)自连接一个表和表自身进行连接。多用于字段映射转换。比如下面查询指定用户的语句其中的 created_by 是用户表中的一个 id。那我应该如何在查询结果中将 created_by 从用户 id 转换成用户的 email 呢SELECT id, username, email, created_by FROM user Where idxxxx;这个时候我们就可以使用自连接来实现这种id - email的转换了。SELECT u.id, u.username, u.email, creator.email AS created_by FROM user u LEFT JOIN user creator ON u.created_by creator.id Where idxxxx;外连接和普通连接相比逗号变成了 left outer join 当然也有 right outer joinwhere 变成了 on。左外连接列出左边关系中所有的元组右外连接列出右边关系中所有的元组。select Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade from Student left outer join SC on Student.SnoSC.Sno;mysql select Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade from Student left outer join SC on Student.SnoSC.Sno; --------------------------------------------------- | Sno | Sname | Ssex | Sage | Sdept | Cno | Grade | --------------------------------------------------- | 201215121 | 李勇 | 男 | 20 | CS | 1 | 92 | | 201215121 | 李勇 | 男 | 20 | CS | 2 | 85 | | 201215121 | 李勇 | 男 | 20 | CS | 3 | 88 | | 201215122 | 刘晨 | 女 | 19 | CS | 2 | 90 | | 201215122 | 刘晨 | 女 | 19 | CS | 3 | 80 | | 201215123 | 王敏 | 女 | 18 | MA | NULL | NULL | | 201215125 | 张立 | 男 | 19 | IS | NULL | NULL | --------------------------------------------------- 7 rows in set (0.00 sec)全外连接只要其中一个表中存在匹配就返回行左外连接 union 右外连接。select Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade from Student left outer join SC on Student.SnoSC.Sno union select Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade from Student right outer join SC on Student.SnoSC.Sno;多表连接查询每个学生的学号姓名选修课程和成绩。where 第一个条件判断学生选修了哪些课程第二个条件则是在第一个条件的基础上查询选修课程的名称。select Student.Sno, Sname, Cname, Grade from Student, SC, Course where Student.SnoSC.Sno and SC.CnoCourse.Cno;mysql select Student.Sno, Sname, Cname, Grade from Student, SC, Course where Student.SnoSC.Sno and SC.CnoCourse.Cno; ---------------------------------------- | Sno | Sname | Cname | Grade | ---------------------------------------- | 201215121 | 李勇 | 数据库 | 92 | | 201215121 | 李勇 | 数学 | 85 | | 201215121 | 李勇 | 信息系统 | 88 | | 201215122 | 刘晨 | 数学 | 90 | | 201215122 | 刘晨 | 信息系统 | 80 | ---------------------------------------- 5 rows in set (0.00 sec)嵌套查询查询选修 2 号课程的学生。select Sname from Student where Sno in (select Sno from SC where Cno2);mysql select Sname from Student where Sno in - (select Sno from SC where Cno2); -------- | Sname | -------- | 李勇 | | 刘晨 | -------- 2 rows in set (0.00 sec)有些嵌套查询可以写成表连接查询的形式替代有些则是不能替代的。select Sname from Student, SC where Student.SnoSC.Sno and SC.Cno2;mysql select Sname from Student, SC where Student.SnoSC.Sno and SC.Cno2; -------- | Sname | -------- | 李勇 | | 刘晨 | -------- 2 rows in set (0.00 sec)带有 in 的子查询查询选修了课程名为 “信息系统” 的学生学号和姓名。select Sno, Sname from Student where Sno in (select Sno from SC where Cno in (select Cno from Course where Cname信息系统));mysql select Sno, Sname from Student where Sno in - (select Sno from SC where Cno in - (select Cno from Course where Cname信息系统)); ------------------- | Sno | Sname | ------------------- | 201215121 | 李勇 | | 201215122 | 刘晨 | ------------------- 2 rows in set (0.00 sec)带有比较运算的子查询不相关子查询子查询的查询条件不依赖于父查询。select Sno, Sname, Sdept from Student where Sdept (select Sdept from Student where Sname刘晨);mysql select Sno, Sname, Sdept from Student where Sdept - (select Sdept from Student where Sname刘晨); -------------------------- | Sno | Sname | Sdept | -------------------------- | 201215121 | 李勇 | CS | | 201215122 | 刘晨 | CS | -------------------------- 2 rows in set (0.00 sec)相关子查询子查询的查询条件依赖于父查询。select Sno, Cno from SC x where Grade (select avg(Grade) from SC y where x.Snoy.Sno);mysql select Sno, Cno from SC x where Grade - (select avg(Grade) from SC y where x.Snoy.Sno); ---------------- | Sno | Cno | ---------------- | 201215121 | 1 | | 201215122 | 2 | ---------------- 2 rows in set (0.00 sec)带有 ANY 或 ALL 的子查询select Sname, Sage from Student where Sageany( select Sage from Student where SdeptCS) and Sdept!CS;mysql select Sname, Sage from Student where Sageany - select Sage from Student where SdeptCS) - and Sdept!CS; -------------- | Sname | Sage | -------------- | 王敏 | 18 | | 张立 | 19 | -------------- 2 rows in set (0.00 sec)上面查询也可以用聚合函数实现毕竟 any(result) 等价于 max(result) all(result) 等价于 min(result)。更多等价转换关系请参考下表select Sname, Sage from Student where Sage( select max(Sage) from Student where SdeptCS) and Sdept!CS;mysql select Sname, Sage from Student where Sage( - select max(Sage) from Student where SdeptCS) - and Sdept!CS; -------------- | Sname | Sage | -------------- | 王敏 | 18 | | 张立 | 19 | -------------- 2 rows in set (0.00 sec)带有 EXISTS的子查询select Sname from Student where exists (select * from SC where SnoStudent.Sno and Cno1);mysql select Sname from Student where exists - (select * from SC where SnoStudent.Sno and Cno1); -------- | Sname | -------- | 李勇 | -------- 1 row in set (0.00 sec)当然这块直接用表连接查询也是可以实现的select Sname from Student, SC where Student.SnoSC.Sno and Cno1;mysql select Sname from Student, SC where Student.SnoSC.Sno and Cno1; -------- | Sname | -------- | 李勇 | -------- 1 row in set (0.00 sec)集合查询主要是 UNION、INTERSECT 和 EXCEPT。注意参加集合操作的查询结果列数必须相同对应项的数据类型也必须相同。UNIONselect Sno from SC where Cno1 union select Sno from SC where Cno2;mysql select Sno from SC where Cno1 union - select Sno from SC where Cno2; ----------- | Sno | ----------- | 201215121 | | 201215122 | ----------- 2 rows in set (0.00 sec)INTERSECTmysql 好像不支持。select * from Student where SdeptCS INTERSECT select * from Student where Sage19;EXCEPTmysql 好像也不支持。不过这两个基本上都可以通过表连接或者子查询来进行等价替换。select * from Student where SdeptCS EXCEPT select * from Student where Sage19;