单表操作
语法：
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[SQL_CACHE | SQL_NO_CACHE]
select_expr [, select_expr ...]
[FROM
table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[FOR UPDATE | LOCK IN SHARE MODE]
说明：
字段显示可以使用别名：
col1 AS alias1, col2 AS alias2, ...
WHERE子句：指明过滤条件以实现"选择"的功能：
过滤条件：布尔型表达式
算术操作符：+, -, *, /, %
比较操作符：=,<=>（相等或都为空）, <>, !=(非标准SQL), >, >=, <, <=
范例查询: BETWEEN min_num AND max_num
不连续的查询: IN (element1, element2, ...)
空查询: IS NULL, IS NOT NULL
DISTINCT 去除重复行，范例：SELECT DISTINCT gender FROM students;
模糊查询: LIKE 使用 % 表示任意长度的任意字符 _ 表示任意单个字符
RLIKE：正则表达式，索引失效，不建议使用
REGEXP：匹配字符串可用正则表达式书写模式，同上
逻辑操作符：NOT，AND，OR，XOR
GROUP BY：根据指定的条件把查询结果进行"分组"以用于做"聚合"运算
常见聚合函数： count(), sum(), max(), min(), avg(),注意:聚合函数不对null统计
HAVING: 对分组聚合运算后的结果指定过滤条件
一旦分组 group by ，select语句后只跟分组的字段，聚合函数
ORDER BY: 根据指定的字段对查询结果进行排序
升序：ASC
降序：DESC
LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制,跳过offset,显示row_count
行,offset默为值为0
对查询结果中的数据请求施加"锁"
FOR UPDATE: 写锁，独占或排它锁，只有一个读和写操作
LOCK IN SHARE MODE: 读锁，共享锁，同时多个读操作
范例：SQL 注入攻击
select * from user where name=‘admin‘ and password=‘‘ or ‘1‘=‘1‘;
select * from user where name=‘admin‘ and password=‘‘ or ‘1=1‘;
select * from user where name=‘admin‘; -- ‘ and password=‘longxuan123‘;
select * from user where name=‘admin‘; # ‘ and password=‘longxuan123‘;
范例：简单查询
查看表结构
mysql> desc students;
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| StuID | int unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar(50) | NO | | NULL | |
| Age | tinyint unsigned | NO | | NULL | |
| Gender | enum(‘F‘,‘M‘) | NO | | NULL | |
| ClassID | tinyint unsigned | YES | | NULL | |
| TeacherID | int unsigned | YES | | NULL | |
+-----------+------------------+------+-----+---------+----------------+
# 查询stuid小于3
mysql> select * from students where stuid < 3;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
+-------+-------------+-----+--------+---------+-----------+
2 rows in set (0.00 sec)
# 查询genber字段是m
mysql> select * from students where gender=‘m‘;
# 查询是null
mysql> select * from students where gender is null;
Empty set (0.00 sec)
# 查询不是null
mysql> select * from students where gender is not null;
# 降序查询3行
mysql> select * from students order by name desc limit 3;
# 降序分页查询
mysql> select * from students order by name desc limit 1,3;
# 跳过前3个只显示后续的2个
mysql> select * from students order by age desc limit 3,2;
# 查询stuid的大于等于2到小于等于6范围
mysql> select * from students where stuid >= 2 and stuid <= 6;
# 查询stuid的2到6范围
mysql> select * from students where stuid BETWEEN 2 and 6;
# 查询name包含x开头其他结尾的
mysql> select * from students where name like ‘x%‘;
# 使用正则查询name包含字段lx
mysql> select * from students where name rlike ‘.*[lx].*‘;
# 使用别名
mysql> select stuid stuid,name as stuname from students;
# 判断字段是否为null
mysql> select * from students where classid is null;
# 同上
mysql> select * from students where classid <=> null;
# ifnull函数判断指定的字段是否为空值,如果空值则使用指定默认值
mysql> select stuid,name,ifnull(classid,‘无班级‘) from students where classid is null;
# 记录去重
mysql> select distinct gender from students;
# 将age和gender多个字段重复的记录去重
mysql> select distinct age,gender from students;
# 分页查询
mysql> select * from students limit 0,3;
# 聚合函数
mysql> select sum(age)/count(*) from students where gender=‘m‘;
mysql> select sum(age)/count(*) from students where gender=‘f‘;
# 分组统计
mysql> select classid,count(*) 数量 from students group by classid;
+---------+--------+
| classid | 数量 |
+---------+--------+
| 2 | 3 |
| 1 | 4 |
| 4 | 4 |
| 3 | 4 |
| 5 | 1 |
| 7 | 3 |
| 6 | 4 |
| NULL | 2 |
+---------+--------+
8 rows in set (0.00 sec)
# 分组统计
mysql> select classid,gender,count(*) 数量 from students group by classid,gender;
+---------+--------+--------+
| classid | gender | 数量 |
+---------+--------+--------+
| 2 | M | 3 |
| 1 | M | 2 |
| 4 | M | 4 |
| 3 | M | 1 |
| 5 | M | 1 |
| 3 | F | 3 |
| 7 | F | 2 |
| 6 | F | 3 |
| 6 | M | 1 |
| 1 | F | 2 |
| 7 | M | 1 |
| NULL | M | 2 |
+---------+--------+--------+
12 rows in set (0.00 sec)
# 分组统计平均年龄
mysql> select classid,avg(age) 平均年龄 from students where classid > 3 group by classid having 平均年龄 > 30;
+---------+--------------+
| classid | 平均年龄 |
+---------+--------------+
| 5 | 46.0000 |
+---------+--------------+
1 row in set (0.00 sec)
# 分组统计性别m的平均年龄
mysql> select gender,avg(age) 平均年龄 from students group by gender having gender=‘m‘;
+--------+--------------+
| gender | 平均年龄 |
+--------+--------------+
| M | 33.0000 |
+--------+--------------+
1 row in set (0.00 sec)
# 多个字段分组统计
mysql> select classid,gender,count(*) 数量 from students group by gender,classid;
# group_concat函数实现分组信息的集合
mysql> select gender,group_concat(name) from students group by gender;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| gender | group_concat(name) |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| F | Xiao Qiao,Huang Yueying,Xi Ren,Lin Daiyu,Ren Yingying,Yue Lingshan,Diao Chan,Wen Qingqing,Xue Baochai,Lu Wushuang |
| M | Tian Boguang,Sun Dasheng,Xu Xian,Ma Chao,Hua Rong,Lin Chong,Xu Zhu,Duan Yu,Shi Zhongyu,Yuan Chengzhi,Shi Qing,Yu Yutong,Ding Dian,Xie Yanke,Shi Potian |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
# with rollup 分组后聚合函数统计后再做汇总
mysql> select gender,count(*) from students group by gender with rollup;
+--------+----------+
| gender | count(*) |
+--------+----------+
| F | 10 |
| M | 15 |
| NULL | 25 |
+--------+----------+
3 rows in set (0.00 sec)
# # with rollup，group_concat(name) 分组后聚合函数统计后再做汇总
mysql> select gender,group_concat(name) from students group by gender with rollup;
#必须先过滤，再排序
mysql> select * from students where classid is not null order by gender desc, age asc;
# 多列排序
mysql> select * from students order by gender desc, age asc;
# 分组和排序
mysql> select classid,count(*) 数量 from students group by classid order by 数量;
+---------+--------+
| classid | 数量 |
+---------+--------+
| 5 | 1 |
| NULL | 2 |
| 2 | 3 |
| 7 | 3 |
| 1 | 4 |
| 4 | 4 |
| 3 | 4 |
| 6 | 4 |
+---------+--------+
8 rows in set (0.01 sec)
# 分组后再排序
mysql> select gender,classid,avg(age) from students where classid is not null group by gender,classid order by gender,classid;
多表查询
多表查询，即查询结果来自于多张表
子查询：在SQL语句嵌套着查询语句，性能较差，基于某语句的查询结果再次进行的查询
联合查询：UNION
交叉连接：笛卡尔乘积 CROSS JOIN
内连接：
等值连接：让表之间的字段以"等值"建立连接关系
不等值连接
自然连接：去掉重复列的等值连接 , 语法: FROM table1 NATURAL JOIN table2;
外连接：
左外连接：FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
右外连接：FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col
完全外连接: FROM tb1 FULL OUTER JOIN tb2 ON tb1.col=tb2.col 注意:MySQL 不支持此SQL
语法
自连接：本表和本表进行连接查询
3.7.2.1 子查询
子查询 subquery 即SQL语句调用另一个SELECT子句,可以是对同一张表,也可以是对不同表,主要有以下
四种常见的用法.
- 用于比较表达式中的子查询；子查询仅能返回单个值
mysql> select name,age from students where age > (select avg(age) from teachers);
+-------------+-----+
| name | age |
+-------------+-----+
| Sun Dasheng | 100 |
+-------------+-----+
1 row in set (0.00 sec)
mysql> update students set age=(select avg(age) from teachers) where stuid=24;
- 用于IN中的子查询：子查询应该单独查询并返回一个或多个值重新构成列表
mysql> select name,age from students where age in (select age from teachers);
- 用于EXISTS 和 Not EXISTS
参考链接：https://dev.mysql.com/doc/refman/8.0/en/exists-and-not-exists-subqueries.html
EXISTS(包括 NOT EXISTS )子句的返回值是一个BOOL值。 EXISTS 内部有一个子查询语句(SELECT
... FROM...)， 将其称为EXIST的内查询语句。其内查询语句返回一个结果集。 EXISTS子句根据其内
查询语句的结果集空或者非空，返回一个布尔值。将外查询表的每一行，代入内查询作为检验，如
果内查询返回的结果为非空值，则EXISTS子句返回TRUE，外查询的这一行数据便可作为外查询的
结果行返回，否则不能作为结果
mysql> select * from students s where exists (select * from teachers t where s.teacherid=t.tid);
# 说明：
1、EXISTS (或 NOT EXISTS) 用在 where之后，且后面紧跟子查询语句（带括号）
2、EXISTS (或 NOT EXISTS) 只关心子查询有没有结果,并不关心子查询的结果具体是什么
3、上述语句把students的记录逐条代入到Exists后面的子查询中，如果子查询结果集不为空，即说明
存在，那么这条students的记录出现在最终结果集，否则被排除
mysql> select * from students s where not exists (select * from teachers t where s.teacherid=t.tid);
用于FROM子句中的子查询
mysql> select s.classid,s.ages from (select classid,avg(age) as ages from students where classid is not null group by classid) as s where s.ages > 30;
+---------+---------+
| classid | ages |
+---------+---------+
| 2 | 36.0000 |
| 5 | 46.0000 |
+---------+---------+
2 rows in set (0.00 sec)
# 子查询：select 的执行结果，被其它SQL调用
mysql> select stuid,name,age from students where age > (select avg(age) from students);
范例：子查询用于更新表
mysql> update teachers set age = (select avg(age) from students) where tid=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 28 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
联合查询
联合查询 Union 实现的条件,多个表的字段数量相同,字段名和数据类型可以不同,但一般数据类型是相同
的
mysql> select name,age from students union select name,age from teachers;
范例：联合查询
# 多表纵向合并union
mysql> select tid as id,name,age,gender from teachers union select stuid,name,age,gender from students;
# union all 不去重
mysql> select * from teachers union all select * from teachers;
交叉连接
cross join 即多表的记录之间做笛卡尔乘积组合，并且多个表的列横向合并相加, "雨露均沾"
比如: 第一个表3行4列,第二个表5行6列,cross join后的结果为3*5=15行,4+6=10列
交叉连接生成的记录可能会非常多,建议慎用
mysql> select * from students cross join teachers;
内连接
inner join 内连接取多个表的交集
# 内连接inner join
# 如果表定义了别名，原表名将无法使用
mysql> select stuid,s.name as student_name, tid,t.name as teacher_name from students as s inner join teachers as t on s.teacherid=t.tid;
mysql> select s.name 学生姓名,s.age 学生年龄,s.gender 学生性别,t.name 老师姓名,t.age 老师年龄,t.gender 老师性别 from students s inner join teachers t on s.gender <> t.gender;
# 内连接后过滤数据
mysql> select * from students s inner join teachers t on s.teacherid=t.tid and s.age > 30;
自然连接
当源表和目标表共享相同名称的列时，就可以在它们之间执行自然连接，而无需指定连接列。
在使用纯自然连接时，如没有相同的列时，会产生交叉连接（笛卡尔乘积）
语法：(SQL:1999)SELECT table1.column, table2.column FROM table1 NATURAL JOIN table2;
范例:
mysql> create table t1 (id int,name char(20));
mysql> create table t2 (id int,title char(20));
mysql> insert t1 values(1,‘longwang‘),(2,‘longge‘),(3,‘liaoli‘);
mysql> insert t2 values(1,‘ceo‘),(2,‘cto‘);
mysql> select * from t1;
+------+----------+
| id | name |
+------+----------+
| 1 | longwang |
| 2 | longge |
| 3 | liaoli |
+------+----------+
3 rows in set (0.00 sec)
mysql> select * from t2;
+------+-------+
| id | title |
+------+-------+
| 1 | ceo |
| 2 | cto |
+------+-------+
2 rows in set (0.00 sec)
mysql> select * from t1 natural join t2;
+------+----------+-------+
| id | name | title |
+------+----------+-------+
| 1 | longwang | ceo |
| 2 | longge | cto |
+------+----------+-------+
2 rows in set (0.00 sec)
左和右外连接
左连接: 以左表为主根据条件查询右表数据﹐如果根据条件查询右表数据不存在使用null值填充
右连接: 以右表为主根据条件查询左表数据﹐如果根据条件查询左表数据不存在使用null值填充
# 左外连接 left
mysql> select s.stuid,s.name,s.age,s.teacherid,t.tid,t.name,t.age from students as s left join teachers as t on s.teacherid=t.tid;
# 左外连接扩展
mysql> select * from students s left join teachers t on s.teacherid=t.tid where t.tid is null;
# 多个条件的左外连接
mysql> select * from students s left join teachers t on s.teacherid=t.tid and s.teacherid is null;
# 先左外连接，再过滤
mysql> select * from students s left join teachers t on s.teacherid=t.tid where s.teacherid is null;
# 右外连接
mysql> select * from students s right join teachers t on s.teacherid=t.tid;
# 右外连接的扩展用法
mysql> select * from students s right join teachers t on s.teacherid=t.tid where s.teacherid is null;
完全外连接
MySQL 不支持完全外连接full outer join语法
# MySQL不支持完全外连接 full outer join,利用以下方式法代替
mysql> select * from students left join teachers on students.teacherid=teachers.tid
-> union
-> select * from students right join teachers on students.teacherid=teachers.tid;
mysql> select s.stuid,s.name,s.age,t.tid,t.name,t.age from students as s left join teachers as t on s.teacherid=t.tid
-> union
-> select s.stuid,s.name,s.age,t.tid,t.name,t.age from students as s right join teachers as t on s.teacherid=t.tid;
# 完全外连接的扩展示例
mysql> select * from students s left join teachers t on s.teacherid=t.tid where t.tid is null union select * from students s right join teachers t on s.teacherid=t.tid where s.teacherid is null;
mysql> select * from (select s.stuid,s.name s_name,s.teacherid,t.tid,t.name t_name from students s left join teachers t on s.teacherid=t.tid union select s.stuid,s.name,s.teacherid,t.tid,t.name from students s right join teachers t on s.teacherid=t.tid) as a where a.teacherid is null or a.tid is null;
自连接
自连接, 即表自身连接自身
mysql> select e.name,l.name from emp as e inner join emp as l on e.leaderid=l.id;
mysql> select e.name,ifnull(l.name,"无上级") from emp as e left join emp as l on e.leaderid=l.id;
mysql> select e.name,ifnull(l.name,"无上级")leader from emp as e left join emp as l on e.leaderid=l.id;
范例：三表连接
mysql> select name,course,score from students st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.courseid;
mysql> select st.name,co.course,sc.score from courses co inner join scores sc on co.courseid=sc.courseid inner join students st on sc.stuid=st.stuid;
ELECT 语句处理的顺序
查询执行路径中的组件：查询缓存、解析器、预处理器、优化器、查询执行引擎、存储引擎
SELECT语句的执行流程：
FROM Clause --> WHERE Clause --> GROUP BY --> HAVING Clause -->SELECT --> ORDER
BY --> LIMIT
实例
建表sql语句
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cid` int(11) NOT NULL,
`cname` varchar(30) DEFAULT NULL,
`tid` int(11) DEFAULT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (‘3001‘, ‘语文‘, ‘4‘);
INSERT INTO `course` VALUES (‘3002‘, ‘数学‘, ‘2‘);
INSERT INTO `course` VALUES (‘3003‘, ‘英语‘, ‘1‘);
INSERT INTO `course` VALUES (‘3004‘, ‘政治‘, ‘3‘);
-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
`sid` int(11) NOT NULL,
`cid` int(11) NOT NULL,
`score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES (‘101‘, ‘3001‘, ‘92‘);
INSERT INTO `sc` VALUES (‘102‘, ‘3001‘, ‘85‘);
INSERT INTO `sc` VALUES (‘103‘, ‘3001‘, ‘76‘);
INSERT INTO `sc` VALUES (‘105‘, ‘3001‘, ‘87‘);
INSERT INTO `sc` VALUES (‘106‘, ‘3001‘, ‘66‘);
INSERT INTO `sc` VALUES (‘108‘, ‘3001‘, ‘96‘);
INSERT INTO `sc` VALUES (‘101‘, ‘3002‘, ‘98‘);
INSERT INTO `sc` VALUES (‘102‘, ‘3002‘, ‘81‘);
INSERT INTO `sc` VALUES (‘103‘, ‘3002‘, ‘93‘);
INSERT INTO `sc` VALUES (‘104‘, ‘3002‘, ‘75‘);
INSERT INTO `sc` VALUES (‘105‘, ‘3002‘, ‘65‘);
INSERT INTO `sc` VALUES (‘108‘, ‘3002‘, ‘96‘);
INSERT INTO `sc` VALUES (‘101‘, ‘3003‘, ‘96‘);
INSERT INTO `sc` VALUES (‘102‘, ‘3003‘, ‘85‘);
INSERT INTO `sc` VALUES (‘103‘, ‘3003‘, ‘76‘);
INSERT INTO `sc` VALUES (‘104‘, ‘3003‘, ‘65‘);
INSERT INTO `sc` VALUES (‘105‘, ‘3003‘, ‘54‘);
INSERT INTO `sc` VALUES (‘106‘, ‘3003‘, ‘56‘);
INSERT INTO `sc` VALUES (‘107‘, ‘3003‘, ‘93‘);
INSERT INTO `sc` VALUES (‘108‘, ‘3003‘, ‘86‘);
INSERT INTO `sc` VALUES (‘101‘, ‘3004‘, ‘100‘);
INSERT INTO `sc` VALUES (‘102‘, ‘3004‘, ‘83‘);
INSERT INTO `sc` VALUES (‘103‘, ‘3004‘, ‘76‘);
INSERT INTO `sc` VALUES (‘104‘, ‘3004‘, ‘69‘);
INSERT INTO `sc` VALUES (‘105‘, ‘3004‘, ‘50‘);
INSERT INTO `sc` VALUES (‘106‘, ‘3004‘, ‘53‘);
INSERT INTO `sc` VALUES (‘107‘, ‘3004‘, ‘87‘);
INSERT INTO `sc` VALUES (‘108‘, ‘3004‘, ‘88‘);
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(11) NOT NULL,
`sname` varchar(30) DEFAULT NULL,
`sage` int(11) DEFAULT NULL,
`sgender` varchar(8) DEFAULT NULL,
PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (‘101‘, ‘龙龙‘, ‘18‘, ‘男‘);
INSERT INTO `student` VALUES (‘102‘, ‘文二‘, ‘19‘, ‘男‘);
INSERT INTO `student` VALUES (‘103‘, ‘张三‘, ‘18‘, ‘男‘);
INSERT INTO `student` VALUES (‘104‘, ‘李四‘, ‘19‘, ‘女‘);
INSERT INTO `student` VALUES (‘105‘, ‘王五‘, ‘20‘, ‘男‘);
INSERT INTO `student` VALUES (‘106‘, ‘李华‘, ‘19‘, ‘男‘);
INSERT INTO `student` VALUES (‘107‘, ‘李零‘, ‘19‘, ‘女‘);
INSERT INTO `student` VALUES (‘108‘, ‘李宁‘, ‘20‘, ‘男‘);
INSERT INTO `student` VALUES (‘109‘, ‘贝贝‘, ‘19‘, ‘女‘);
INSERT INTO `student` VALUES (‘110‘, ‘娜娜‘, ‘20‘, ‘女‘);
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` int(11) NOT NULL,
`tname` varchar(30) DEFAULT NULL,
PRIMARY KEY (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (‘1‘, ‘叶问‘);
INSERT INTO `teacher` VALUES (‘2‘, ‘李龙‘);
INSERT INTO `teacher` VALUES (‘3‘, ‘李逍遥‘);
INSERT INTO `teacher` VALUES (‘4‘, ‘朱文章‘);
创建数据库
mysql> create database db1;
进入数据库
mysql> use db1;
导入数据
mysql> source /root/student_teacher.sql
范例：查询所有同学的学号、姓名、选课数、总成绩
mysql> select s.sid as 学号,s.sname as 姓名,count(sc.cid) as 选课数 ,sum(sc.score) as 总成绩 from student s inner jooin sc sc on s.sid=sc.sid group by s.sid;
+--------+--------+-----------+-----------+
| 学号 | 姓名 | 选课数 | 总成绩 |
+--------+--------+-----------+-----------+
| 101 | 龙龙 | 4 | 378 |
| 102 | 文二 | 4 | 334 |
| 103 | 张三 | 4 | 320 |
| 105 | 王五 | 4 | 261 |
| 106 | 李华 | 3 | 174 |
| 108 | 李宁 | 4 | 356 |
| 104 | 李四 | 3 | 205 |
| 107 | 李零 | 2 | 178 |
+--------+--------+-----------+-----------+
8 rows in set (0.00 sec)
范例：查询学过“叶问”老师课的同学的学号、姓名
mysql> SELECT s.sid 学号,s.sname 姓名
-> FROM student s, sc sc, course c, teacher t
-> WHERE s.sid=sc.sid AND c.cid=sc.cid AND t.tid=c.tid AND t.tname="叶问";
+--------+--------+
| 学号 | 姓名 |
+--------+--------+
| 101 | 龙龙 |
| 102 | 文二 |
| 103 | 张三 |
| 104 | 李四 |
| 105 | 王五 |
| 106 | 李华 |
| 107 | 李零 |
| 108 | 李宁 |
+--------+--------+
8 rows in set (0.00 sec)
范例：查询所有课程成绩小于60分的同学的学号、姓名
mysql> SELECT sid,sname FROM student
-> WHERE sid NOT IN (
-> SELECT DISTINCT(sc.sid) FROM student s, sc sc
-> WHERE sc.sid=s.sid AND sc.score>60);
+-----+--------+
| sid | sname |
+-----+--------+
| 109 | 贝贝 |
| 110 | 娜娜 |
+-----+--------+
2 rows in set (0.01 sec)
范例：查询平均成绩大于60分的同学的学号和平均成绩
mysql> SELECT sid,AVG(score)
-> FROM sc
-> GROUP BY sid HAVING AVG(score)>60;
+-----+------------+
| sid | AVG(score) |
+-----+------------+
| 101 | 94.5000 |
| 102 | 83.5000 |
| 103 | 80.0000 |
| 105 | 65.2500 |
| 108 | 89.0000 |
| 104 | 68.3333 |
| 107 | 89.0000 |
+-----+------------+
7 rows in set (0.00 sec)
范例：查询没有学过“朱元璋”老师课的同学的学号、姓名
mysql> SELECT s.sid,s.sname
-> FROM student s
-> WHERE s.sid NOT IN (
-> SELECT s.sid
-> FROM student s, sc sc, course c, teacher t
-> WHERE s.sid=sc.sid AND c.cid=sc.cid AND t.tid=c.tid AND t.tname="朱元璋");
+-----+--------+
| sid | sname |
+-----+--------+
| 101 | 龙龙 |
| 102 | 文二 |
| 103 | 张三 |
| 104 | 李四 |
| 105 | 王五 |
| 106 | 李华 |
| 107 | 李零 |
| 108 | 李宁 |
| 109 | 贝贝 |
| 110 | 娜娜 |
+-----+--------+
10 rows in set (0.00 sec)