一怒允、多表查詢(xún)
--編寫(xiě)多表查詢(xún)語(yǔ)句的一般過(guò)程
--（1）、分析句子要涉及到哪些表
--（2）件炉、對(duì)應(yīng)的表中要查詢(xún)哪些關(guān)聯(lián)字段
--（3）、確定連接條件或篩選條件
--（4）矮湘、寫(xiě)成完整的SQL查詢(xún)語(yǔ)句
1斟冕、多表查詢(xún)指使用SQL查詢(xún)時(shí)不只是一張表的查詢(xún)，要點(diǎn)：
① 多個(gè)表之間必須建立連接關(guān)系
② 表別名的用法
③ 如果from后面使用了表的別名 缅阳，則select后和where后必須用別名代替
二磕蛇、案例一
學(xué)生表student：
+-----+--------+-----+-------+------------+--------------+
| id? | name? | sex | birth | department | address? ? ? |
+-----+--------+-----+-------+------------+--------------+
| 901 | 張老大 | 男? |? 1985 | 計(jì)算機(jī)系? | 北京市海淀區(qū) |
| 902 | 張老二 | 男? |? 1986 | 中文系? ? | 北京市昌平區(qū) |
| 903 | 張三? | 女? |? 1990 | 中文系? ? | 湖南省永州市 |
| 904 | 李四? | 男? |? 1990 | 英語(yǔ)系? ? | 遼寧省阜新市 |
| 905 | 王五? | 女? |? 1991 | 英語(yǔ)系? ? | 福建省廈門(mén)市 |
| 906 | 王六? | 男? |? 1988 | 計(jì)算機(jī)系? | 湖南省衡陽(yáng)市 |
+-----+--------+-----+-------+------------+--------------+
成績(jī)表score：
+----+--------+--------+-------+
| id | stu_id | c_name | grade |
+----+--------+--------+-------+
|? 1 |? ? 901 | 計(jì)算機(jī) |? ? 98 |
|? 2 |? ? 901 | 英語(yǔ)? |? ? 80 |
|? 3 |? ? 902 | 計(jì)算機(jī) |? ? 65 |
|? 4 |? ? 902 | 中文? |? ? 88 |
|? 5 |? ? 903 | 中文? |? ? 95 |
|? 6 |? ? 904 | 計(jì)算機(jī) |? ? 70 |
|? 7 |? ? 904 | 英語(yǔ)? |? ? 92 |
|? 8 |? ? 905 | 英語(yǔ)? |? ? 94 |
|? 9 |? ? 906 | 計(jì)算機(jī) |? ? 90 |
| 10 |? ? 906 | 英語(yǔ)? |? ? 85 |
+----+--------+--------+-------+
1、男同學(xué)的考試科目
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秀撇、姓張同學(xué)的考試科目
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、同時(shí)學(xué)習(xí)英語(yǔ)和計(jì)算機(jī)的學(xué)生信息
select * from student where id in (select stu_id from score where c_name='計(jì)算機(jī)'? and stu_id in (select stu_id from score where c_name='英語(yǔ)'));
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='計(jì)算機(jī)' and s3.c_name='英語(yǔ)';
練習(xí)：
1向族、女同學(xué)的考試科目
2呵燕、同時(shí)學(xué)習(xí)中文和計(jì)算機(jī)的學(xué)生信息；
3件相、姓王的同學(xué)并且有一科以上成績(jī)大于80分的學(xué)生信息再扭；
4、查詢(xún)李四的考試科目（c_name）和考試成績(jī)（grade）
select c_name,grade from score,student where student.id=score.stu_id and name='李四';
5夜矗、查詢(xún)計(jì)算機(jī)成績(jī)低于95的學(xué)生信息
select student.* from score,student where student.id=score.stu_id and c_name='計(jì)算機(jī)' and grade<95;
6泛范、查詢(xún)姓張或者姓王的同學(xué)的姓名、院系和考試科目及成績(jī)
select name,department,c_name,grade from student,score where student.id=score.stu_id and (name like '王%' or name like '張%' )
練習(xí)：
1紊撕、查詢(xún)都是湖南的學(xué)生的姓名罢荡、年齡、院系和考試科目及成績(jī)
2对扶、成績(jī)大于80分的女同學(xué)的信息
3区赵、查詢(xún)出女生成績(jī)最低的人的姓名；
案例二
如下辩稽，有三張表：
學(xué)生表student：
+-----+-------+-----+-----+
| SNO | SNAME | AGE | SEX |
+-----+-------+-----+-----+
|? 1 | 李強(qiáng)? |? 23 | 男? |
|? 2 | 劉麗? |? 22 | 女? |
|? 5 | 張友? |? 22 | 男? |
+-----+-------+-----+-----+
課程表course：
+-----+------------+---------+
| CNO | CNAME? ? ? | TEACHER |
+-----+------------+---------+
| k1? | c語(yǔ)言? ? ? | 王華? ? |
| k5? | 數(shù)據(jù)庫(kù)原理 | 程軍? ? |
| k8? | 編譯原理? | 程軍? ? |
+-----+------------+---------+
成績(jī)表sc：
+-----+-----+-------+
| SNO | CNO | SCORE |
+-----+-----+-------+
|? 1 | k1? |? ? 83 |
|? 2 | k1? |? ? 85 |
|? 5 | k1? |? ? 92 |
|? 2 | k5? |? ? 90 |
|? 5 | k5? |? ? 84 |
|? 5 | k8? |? ? 80 |
+-----+-----+-------+
1惧笛、檢索＂李強(qiáng)＂同學(xué)不學(xué)課程的課程號(hào)(CNO)从媚；
select cno from course where cno not in (select cno from
sc,student where sname='李強(qiáng)' andstudent.sno=sc.sno) ;
2逞泄、查詢(xún)“李強(qiáng)”同學(xué)所有課程的成績(jī)：
select score from student,sc where
student.sname='李強(qiáng)' and student.sno=sc.sno;
3、查詢(xún)課程名為“C語(yǔ)言”的平均成績(jī)
select avg(score) from sc,course where cname='c語(yǔ)言' and course.cno=sc.cno;
練習(xí)：
1拜效、求選修K1 課程的學(xué)生的平均年齡喷众；
select avg(age)from student,sc where student.sno=sc.sno and cno='k1';
2、求王老師所授課程的每門(mén)課程的學(xué)生平均成績(jī)紧憾。
select avg(score) from sc,course where teacherlike '王%' andcourse.cno=sc.cno group by sc.cno;
案例三
有四張表格：
學(xué)生表student：
+-----+-------+---------------------+------+
| sid | sname | sage? ? ? ? ? ? ? ? | ssex |
+-----+-------+---------------------+------+
| 01? | 趙雷? | 1990-01-01 00:00:00 | 男? |
| 02? | 錢(qián)電? | 1990-12-21 00:00:00 | 男? |
| 03? | 孫風(fēng)? | 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? | 語(yǔ)文? | 02? |
| 02? | 數(shù)學(xué)? | 01? |
| 03? | 英語(yǔ)? | 03? |
| 04? | 物理? | 04? |
+-----+-------+-----+
成績(jī)表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到千、查詢(xún)"01"課程比"02"課程成績(jī)高的學(xué)生的信息及課程分?jǐn)?shù)
1.1、查詢(xún)同時(shí)存在"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赴穗、查詢(xún)同時(shí)存在"01"課程和"02"課程的情況和存在"01"課程但可能不存在"02"課程 的情況(不存在時(shí)顯示為 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憔四、查詢(xún)"01"課程比"02"課程成績(jī)低的學(xué)生的信息及課程分?jǐn)?shù)
3膀息、查詢(xún)平均成績(jī)大于等于60分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)
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、查詢(xún)平均成績(jī)小于60分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績(jī)（注意成績(jī)?yōu)閚ull的學(xué)生了赵，使用ifnull（）函數(shù)）
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潜支、查詢(xún)所有同學(xué)的學(xué)生編號(hào)、學(xué)生姓名柿汛、選課總數(shù)冗酿、所有課程的總成績(jī)
select a.sid AS 學(xué)生編號(hào), a.Sname as 學(xué)生姓名, count(b.cid) 選課總數(shù), sum(score) 所有課程的總成績(jī) from Student a left join score b on a.sid = b.sid group by a.sid,a.Sname order by a.sid
