Mysql多表查詢

http://www.rzrgm.cn/haiyan123/p/7496765.html 摘自于海燕博客.

3.4.3 Group By和Having關鍵字一起使用
Having關鍵字與where字句作用相同，都用于設置條件表達式對查詢結果進行過濾，

兩者的區別在于，Having關鍵字后可以跟聚合函數，而where子句不可以

通常情況先Having關鍵字都和Group By一起使用，用于對分組后的結果進行過濾。

將student表中按照gender字段進行分組排序，查詢出grade字段值之和小于300的分組

多表查詢

一、介紹

首先先準備表

員工表和部門表

#建表
create table department(
id int,
name varchar(20) 
);

create table employee1(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#插入數據
insert into department values
(200,'技術'),
(201,'人力資源'),
(202,'銷售'),
(203,'運營');

insert into employee1(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;

查看表：

二、多表連接查詢

1.交叉連接：不適用任何匹配條件。生成笛卡爾積、

select * from employee1 ,department;

2.內連接：找兩張表共有的部分，相當于利用條件從笛卡爾積結果中篩選出了正確的結果。（只連接匹配的行）

#找兩張表共有的部分，相當于利用條件從笛卡爾積結果中篩選出了正確的結果
#department沒有204這個部門，因而employee表中關于204這條員工信息沒有匹配出來
select * from employee1,department where employee1.dep_id=department.id;

#上面用where表示的可以用下面的內連接表示，建議使用下面的那種方法
select * from employee1 inner join department on employee1.dep_id=department.id;

#也可以這樣表示哈

select employee1.id,employee1.name,employee1.age,employee1.sex,department.name from 
employee1,department where employee1.dep_id=department.id;

注意：內連接的join可以忽略不寫，但是還是加上看起來清楚點

3.左連接：優先顯示左表全部記錄。

#左鏈接：在按照on的條件取到兩張表共同部分的基礎上，保留左表的記錄
select * from employee1 left join department on department.id=employee1.dep_id;
select * from department left join  employee1 on department.id=employee1.dep_id;

4.右鏈接：優先顯示右表全部記錄。

#右鏈接：在按照on的條件取到兩張表共同部分的基礎上，保留右表的記錄
select * from employee1 right join department on department.id=employee1.dep_id;

select * from department right join employee1 on department.id=employee1.dep_id;

5.全外連接：顯示左右兩個表的全部記錄。

注意：mysql不支持全外連接 full join
強調：mysql可以使用union間接實現全外連接

select * from employee1 left join department on department.id=employee1.dep_id
union
select * from employee1 right join department on department.id=employee1.dep_id;

三、符合條件連接查詢

示例1：以內連接的方式查詢employee和department表，并且employee表中的age字段值必須大于25,
即找出公司所有部門中年齡大于25歲的員工

select * from employee1 inner join department on employee1.dep_id=department.id 

and age>25;

示例2：以內連接的方式查詢employee和department表，并且以age字段的升序方式顯示

select * from employee1 inner join department on employee1.dep_id=department.id 
=

and age>25 and age>25 order by age asc;

四、子查詢

#1：子查詢是將一個查詢語句嵌套在另一個查詢語句中。
#2：內層查詢語句的查詢結果，可以為外層查詢語句提供查詢條件。
#3：子查詢中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等關鍵字
#4：還可以包含比較運算符：= 、 !=、> 、<等

小練習

#查詢平均年齡在25歲以上的部門名
select name from department where id in (
select dep_id from employee group by dep_id having avg(age) > 25
);
#查看技術部員工姓名
select name from employee where dep_id = (select id from department where name='技術');

#查看小于2人的部門名
select name from department where id in (
select dep_id from employee1 group by dep_id having count(id) < 2
)
union
select name from department where id not in (select distinct dep_id from employee1);

#提取空部門                              #有人的部門
select * from department where id not in (select distinct dep_id from employee1);


或者：
select name from department where id in
(
select dep_id from employee1 group by dep_id having count(id) < 2
union
select id from department where id not in (select distinct dep_id from employee1);
);

五、綜合練習

一、SELECT語句關鍵字的定義順序

1 SELECT DISTINCT <select_list>
2 FROM <left_table>
3 <join_type> JOIN <right_table>
4 ON <join_condition>
5 WHERE <where_condition>
6 GROUP BY <group_by_list>
7 HAVING <having_condition>
8 ORDER BY <order_by_condition>
9 LIMIT <limit_number>

二、SELECT語句關鍵字的執行順序

(7)     SELECT 
(8)     DISTINCT <select_list>
(1)     FROM <left_table>
(3)     <join_type> JOIN <right_table>
(2)     ON <join_condition>
(4)     WHERE <where_condition>
(5)     GROUP BY <group_by_list>
(6)     HAVING <having_condition>
(9)     ORDER BY <order_by_condition>
(10)    LIMIT <limit_number>

init.sql文件內容

View Code

從init.sql文件中導入數據

#準備表、記錄
mysql> create database db1;
mysql> use db1;
mysql> source /root/init.sql

 1 1、查詢所有的課程的名稱以及對應的任課老師姓名
 2 
 3 2、查詢學生表中男女生各有多少人
 4 
 5 3、查詢物理成績等于100的學生的姓名
 6 
 7 4、查詢平均成績大于八十分的同學的姓名和平均成績
 8 
 9 5、查詢所有學生的學號，姓名，選課數，總成績
10 
11 6、 查詢姓李老師的個數
12 
13 7、 查詢沒有報李平老師課的學生姓名
14 
15 8、 查詢物理課程比生物課程高的學生的學號
16 
17 9、 查詢沒有同時選修物理課程和體育課程的學生姓名
18 
19 10、查詢掛科超過兩門(包括兩門)的學生姓名和班級
20 、查詢選修了所有課程的學生姓名
21 
22 12、查詢李平老師教的課程的所有成績記錄
23  
24 13、查詢全部學生都選修了的課程號和課程名
25 
26 14、查詢每門課程被選修的次數
27 
28 15、查詢之選修了一門課程的學生姓名和學號
29 
30 16、查詢所有學生考出的成績并按從高到低排序（成績去重）
31 
32 17、查詢平均成績大于85的學生姓名和平均成績
33 
34 18、查詢生物成績不及格的學生姓名和對應生物分數
35 
36 19、查詢在所有選修了李平老師課程的學生中，這些課程(李平老師的課程，不是所有課程)平均成績最高的學生姓名
37 
38 20、查詢每門課程成績最好的前兩名學生姓名
39 
40 21、查詢不同課程但成績相同的學號，課程號，成績
41 
42 22、查詢沒學過“葉平”老師課程的學生姓名以及選修的課程名稱；
43 
44 23、查詢所有選修了學號為1的同學選修過的一門或者多門課程的同學學號和姓名；
45 
46 24、任課最多的老師中學生單科成績最高的學生姓名

答案待續。。。。

  1 1、查詢所有的課程的名稱以及對應的任課老師姓名
  2 select cname 課程名稱,tname 老師姓名 from course  left join teacher on course.teacher_id = teacher.tid;
  3 
  4 2、查詢學生表中男女生各有多少人
  5 select gender ,count(gender) from student group by gender;
  6 
  7 3、查詢物理成績等于100的學生的姓名
  8 select sname from student inner join score on student.sid = score.student_id
  9 join course on score.course_id = course.cid
 10 where cname = '物理' and num = 100;
 11 
 12 4、查詢平均成績大于八十分的同學的姓名和平均成績
 13 (1):
 14 select sname 姓名,avg(num) 平均成績 from student inner join score on student.sid = score.student_id 
 15 group by student_id having avg(num)>80;
 16 (2):
 17 select sname,avg(num) from student inner join score on student.sid = score.student_id 
 18 group by student_id having avg(num)>80
 19 
 20 5、查詢所有學生的學號，姓名，選課數，總成績
 21 select student.sid 學號,sname 姓名,count(course_id) 選課數,sum(num) 總成績 from student left join score on student.sid = score.student_id
 22 group by student.sid ;
 23 6、 查詢姓李老師的個數
 24 select count(tid) from teacher where tname like '李%';
 25 
 26 7、 查詢沒有報李平老師課的學生姓名
 27 select sname 姓名 from student where sid not in (
 28 select student_id from score where course_id in(
 29 select cid from course where teacher_id =(
 30 select tid from teacher where tname = '李平老師'
 31 )));
 32 
 33 8、 查詢物理課程比生物課程高的學生的學號
 34 select t1.student_id  學號 from 
 35 (select student_id ,num from score inner join course on score.course_id=course.cid  where cname='物理' )as t1
 36 inner join 
 37 (select student_id , num from score inner join course on score.course_id=course.cid  where cname = '生物') as t2
 38 on t1.student_id = t2.student_id
 39 where t1.num>t2.num;
 40 
 41 
 42 
 43 9、 查詢沒有同時選修物理課程和體育課程的學生姓名
 44 (1):
 45 select sname from student inner join score on student.sid = score.student_id
 46 join course on course.cid=score.course_id and cname in ('物理','體育') 
 47 group by student_id having count(course_id)!=2;
 48 
 49 (2):
 50 select sname from student join score  on student.sid = score.student_id
 51 join course on course.cid=score.course_id where cname ='物理' or cname= '體育'
 52 group by student_id having count(course_id) !=2;
 53 
 54 10、查詢掛科超過兩門(包括兩門)的學生姓名和班級
 55 select sname 姓名,caption 班級 from student inner join score on student.sid = score.course_id
 56 join class on class.cid = score.course_id
 57 where num<60 group by student_id having count(course_id)>=2;
 58 
 59 11、查詢選修了所有課程的學生姓名
 60 (1)
 61 select sname 姓名 ,所有的課程數 from student inner join 
 62 (select student_id,count(course_id) 所有的課程數 from score group by student_id having count(course_id) = (
 63 select count(cid) from course)) as t1
 64 on t1.student_id = student.sid;
 65 
 66 (2)
 67 select sname,count(course_id) from student inner join score on student.sid = score.student_id
 68 group by student_id having count(course_id)=(select count(cid) from course);
 69 
 70 12、查詢李平老師教的課程的所有成績記錄
 71 (1):
 72 select num from score inner join course on course.cid=score.course_id
 73 join teacher on teacher.tid=course.teacher_id
 74 where tname = '李平老師';
 75 
 76 (2):
 77 select num from score where course_id in(select cid from course where teacher_id=
 78 (select tid from teacher where tname='李平老師'));
 79 
 80 13、查詢全部學生都選修了的課程號和課程名
 81 select cid 課程號,cname 課程名 from course
 82 select ;
 83 14、查詢每門課程被選修的次數 
 84 (1)
 85 select course.cname,count(student_id) 選課人數 from score inner join course on score.course_id=course.cid
 86 group by course_id;
 87 
 88 (2):也可以按照name分組
 89 select course.cname,count(student_id) 選課人數 from score inner join course on score.course_id=course.cid
 90 group by cname;
 91 
 92 15、查詢之選修了一門課程的學生姓名和學號
 93 select sname 姓名,student_id 學號 from student inner join score on student.sid = score.student_id
 94 group by student_id having count(course_id)=1;
 95 
 96 16、查詢所有學生考出的成績并按從高到低排序（成績去重）
 97 select distinct num from score order by num desc;
 98 
 99 17、查詢平均成績大于85的學生姓名和平均成績
100 (1):
101 select sname 姓名,avg(num) 平均成績 from student inner join score on student.sid = score.student_id
102 group by student_id having avg(num)>85;
103 
104 (2):
105 select student.sname,avg_num from student inner join
106 (select student_id,avg(num) as avg_num from score group by student_id having avg(num) > 85
107 ) t1
108 on student.sid=t1.student_id;
109 
110 
111 18、查詢生物成績不及格的學生姓名和對應生物分數
112 (1):
113 select student.sname ,num 生物成績 from student inner join score on student.sid = score.student_id
114 join course on course.cid=score.course_id
115 where cname='生物' and num<60;
116 
117 (2):
118 select student.sname,t1.num from student inner join
119 (
120 select student_id,num from score
121 where course_id=(select cid from course where cname='生物') and num < 60
122 ) t1
123 on t1.student_id=student.sid
124 ;
125 
126 19、查詢在所有選修了李平老師課程的學生中，這些課程(李平老師的課程，不是所有課程)平均成績最高的學生姓名
127 select sname from student where sid=(
128 select student_id from score where course_id in (
129 select cid from course where teacher_id=(select tid from teacher where tname='李平老師')
130 ) group by student_id order by avg(num) desc limit 1
131 )

posted @ 2018-12-12 15:48 萌哥-愛學習閱讀(479) 評論(0) 收藏舉報

刷新頁面返回頂部

萌哥

愛學習

Mysql多表查詢

多表查詢

二 、SELECT語句關鍵字的執行順序

公告

二、SELECT語句關鍵字的執行順序