一、多表查询
--编写多表查询语句的一般过程
--（1）、分析句子要涉及到哪些表
--（2）、对应的表中要查询哪些关联字段
--（3）、确定连接条件或筛选条件
--（4）、写成完整的SQL查询语句
1、多表查询指使用SQL查询时不只是一张表的查询，要点：
① 多个表之间必须建立连接关系
② 表别名的用法
③ 如果from后面使用了表的别名 ，则select后和where后必须用别名代替
二、案例一
学生表student：
+-----+--------+-----+-------+------------+--------------+
| id | name | sex | birth | department | address |
+-----+--------+-----+-------+------------+--------------+
| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 |
| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 |
| 903 | 张三 | 女 | 1990 | 中文系 | 湖南省永州市 |
| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 |
| 905 | 王五 | 女 | 1991 | 英语系 | 福建省厦门市 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 |
+-----+--------+-----+-------+------------+--------------+
成绩表score：
+----+--------+--------+-------+
| id | stu_id | c_name | grade |
+----+--------+--------+-------+
| 1 | 901 | 计算机 | 98 |
| 2 | 901 | 英语 | 80 |
| 3 | 902 | 计算机 | 65 |
| 4 | 902 | 中文 | 88 |
| 5 | 903 | 中文 | 95 |
| 6 | 904 | 计算机 | 70 |
| 7 | 904 | 英语 | 92 |
| 8 | 905 | 英语 | 94 |
| 9 | 906 | 计算机 | 90 |
| 10 | 906 | 英语 | 85 |
+----+--------+--------+-------+
1、男同学的考试科目
select distinct(c_name) from score s1,student s2 where s2.id=s1.stu_id and sex='男‘；
select distinct(c_name) from score where stu_id in (select id from student where sex='男');
2、姓张同学的考试科目
select distinct(c_name) from score where stu_id in (select id from student where name like '张%');
select c_name from score,student where score.stu_id=student.id and name like '张%';
3、同时学习英语和计算机的学生信息
select * from student where id in (select stu_id from score where c_name='计算机' and stu_id in (select stu_id from score where c_name='英语'));
select s1.* from student s1,score s2,score s3 where s1.id=s2.stu_id and s1.id=s3.stu_id and s2.c_name='计算机' and s3.c_name='英语';
练习：
1、女同学的考试科目
2、同时学习中文和计算机的学生信息；
3、姓王的同学并且有一科以上成绩大于80分的学生信息；
4、查询李四的考试科目（c_name）和考试成绩（grade）
select c_name,grade from score,student where student.id=score.stu_id and name='李四';
5、查询计算机成绩低于95的学生信息
select student.* from score,student where student.id=score.stu_id and c_name='计算机' and grade<95;
6、查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
select name,department,c_name,grade from student,score where student.id=score.stu_id and (name like '王%' or name like '张%' )
练习：
1、查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩
2、成绩大于80分的女同学的信息
3、查询出女生成绩最低的人的姓名；
案例二
如下，有三张表：
学生表student：
+-----+-------+-----+-----+
| SNO | SNAME | AGE | SEX |
+-----+-------+-----+-----+
| 1 | 李强 | 23 | 男 |
| 2 | 刘丽 | 22 | 女 |
| 5 | 张友 | 22 | 男 |
+-----+-------+-----+-----+
课程表course：
+-----+------------+---------+
| CNO | CNAME | TEACHER |
+-----+------------+---------+
| k1 | c语言 | 王华 |
| k5 | 数据库原理 | 程军 |
| k8 | 编译原理 | 程军 |
+-----+------------+---------+
成绩表sc：
+-----+-----+-------+
| SNO | CNO | SCORE |
+-----+-----+-------+
| 1 | k1 | 83 |
| 2 | k1 | 85 |
| 5 | k1 | 92 |
| 2 | k5 | 90 |
| 5 | k5 | 84 |
| 5 | k8 | 80 |
+-----+-----+-------+
1、检索＂李强＂同学不学课程的课程号(CNO)；
select cno from course where cno not in (select cno from
sc,student where sname='李强' andstudent.sno=sc.sno) ;
2、查询“李强”同学所有课程的成绩：
select score from student,sc where
student.sname='李强' and student.sno=sc.sno;
3、查询课程名为“C语言”的平均成绩
select avg(score) from sc,course where cname='c语言' and course.cno=sc.cno;
练习：
1、求选修K1 课程的学生的平均年龄；
select avg(age)from student,sc where student.sno=sc.sno and cno='k1';
2、求王老师所授课程的每门课程的学生平均成绩。
select avg(score) from sc,course where teacherlike '王%' andcourse.cno=sc.cno group by sc.cno;
案例三
有四张表格：
学生表student：
+-----+-------+---------------------+------+
| sid | sname | sage | ssex |
+-----+-------+---------------------+------+
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 |
| 03 | 孙风 | 1990-05-06 00:00:00 | 男 |
| 04 | 李云 | 1990-08-06 00:00:00 | 男 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 |
| 06 | 吴兰 | 1992-03-01 00:00:00 | 女 |
| 07 | 郑竹 | 1898-07-01 00:00:00 | 女 |
| 08 | 王菊 | 1990-01-20 00:00:00 | 女 |
+-----+-------+---------------------+------+
教室表teacher：
+-----+-------+
| tid | tname |
+-----+-------+
| 01 | 张三 |
| 02 | 李四 |
| 03 | 王五 |
| 04 | 赵六 |
+-----+-------+
课程表course：
+-----+-------+-----+
| cid | cname | tid |
+-----+-------+-----+
| 01 | 语文 | 02 |
| 02 | 数学 | 01 |
| 03 | 英语 | 03 |
| 04 | 物理 | 04 |
+-----+-------+-----+
成绩表score：
+-----+-----+-------+
| sid | cid | score |
+-----+-----+-------+
| 01 | 01 | 80 |
| 01 | 02 | 90 |
| 01 | 03 | 99 |
| 02 | 01 | 70 |
| 02 | 02 | 60 |
| 02 | 02 | 80 |
| 03 | 01 | 80 |
| 03 | 02 | 80 |
| 03 | 03 | 80 |
| 04 | 01 | 50 |
| 04 | 02 | 30 |
| 04 | 03 | 20 |
| 05 | 01 | 76 |
| 05 | 02 | 87 |
| 06 | 01 | 31 |
| 06 | 03 | 34 |
| 07 | 02 | 89 |
| 07 | 03 | 98 |
+-----+-----+-------+
题目：
1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
1.1、查询同时存在"01"课程和"02"课程的情况
select a.* , b.score,c.score from student a , score b , score c where a.sid = b.sid and a.sid = c.sid and b.cid = '01' and c.cid = '02' and b.score > c.score
1.2、查询同时存在"01"课程和"02"课程的情况和存在"01"课程但可能不存在"02"课程 的情况(不存在时显示为 null)
select a.* , b.score ,c.score from student a left join score b on a.sid = b.sid and b.cid = '01' left join score c on a.sid = c.sid and c.cid = '02' where b.score>IFNULL(c.score,0)
2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select a.sid , a.sname , cast(avg(b.score) as decimal(18,2)) avg_score from Student a , score b where a.sid = b.sid group by a.sid , a.sname having cast(avg(b.score) as decimal(18,2)) >= 60 order by a.sid
4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩（注意成绩为null的学生，使用ifnull（）函数）
select a.sid , a.Sname , IFNULL(cast(avg(b.score) as decimal(18,2)),0) avg_score from Student a left join score b on a.sid = b.sid group by a.sid , a.sname having ifnull(cast(avg(b.score) as decimal(18,2)),0) < 60 order by a.Sid
5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select a.sid AS 学生编号, a.Sname as 学生姓名, count(b.cid) 选课总数, sum(score) 所有课程的总成绩 from Student a left join score b on a.sid = b.sid group by a.sid,a.Sname order by a.sid
网友评论