學好SQL查詢：無他，概手熟耳，

1. 基礎表：

學生表：

老師表：

課程表：

成績表：

2. 題目：

1、查詢名字中含有"華"字的學生資訊

select * from 學生 where sname like '%華%'

2、查詢并統計同齡學生人數

select sage,count(sid) from 學生 group by sage

3、查詢每門課程的平均成績，結果按平均成績升序排列，平均成績相同時，按課程號降序排列

select cid,avg(Cscore) from 成績 group by cid order by avg(Cscore),cid desc 

4、按平均成績從高到低顯示所有學生的平均成績

select sid,avg(Cscore) from 成績 group by sid order by avg(Cscore) desc

5、查詢各科成績最高分、最低分和平均分：以如下形式顯示：課程ID，課程name，最高分，最低分，平均分

select sc.cid 課程ID, c.cname 課程名, max(sc.Cscore) 最高分,min(sc.Cscore) 最低分,avg(sc.Cscore) 平均分 from 成績 sc left join 課程 c on sc.cid=c.cid
group by sc.cid

6、按各科平均成績從低到高順序排序

select cid, avg(Cscore) from 成績 group by cid order by avg(Cscore) desc

7、查詢學生的總成績并進行排名

select sid 學號,sum(Cscore) 總分 from 成績 group by sid order by sum(Cscore) desc

8、檢索至少選修兩門課程的學生學號

select sid from 成績 group by sid having count(cid) > 1

9、查詢每門課程被選修的學生數

select cid,count(cid) from 成績 group by cid 

10、查詢不及格的課程，并按課程號從大到小排列

select * from 成績 group by cid having cscore < 60 order by cid desc

11、查詢出只選修了一門課程的全部學生的學號和姓名

select sid,sname from 學生 where sid in(
	select sid from 成績 group by sid having count(sid) = 1
)

12、查詢課程編號為"01"且課程成績在60分以上的學生的學號和姓名；

select sid,sname from 學生 where sid in(
	select sid from 成績 where 
	cid = 'c01'
	and Cscore>=60
)

13、查詢“01”課程比“02”課程成績高的所有學生的學號；

select sc1.sid from 成績 sc1, 成績 sc2 where
sc1.sid = sc2.sid
and sc1.cid = 'c01'
and sc2.cid = 'c02'
and sc1.cscore > sc2.Cscore 

14、查詢平均成績大于60分的同學的學號和平均成績；

select sid, avg(Cscore) from 成績 group by sid having avg(Cscore) > 60

15、查詢姓“李”的老師的個數；

select count(1) from 教師 where Tname like '李%'

16、查詢沒學過“何倩文”老師課的同學的學號、姓名；

select sid, sname from 學生 where sid not in(
	select sid from 成績 where cid =(
	select cid from 課程 where Cteacher = '何倩文'
	)
)

17、查詢學過編號“01”并且也學過編號“02”課程的同學的學號、姓名；

select sid, sname from 學生 where sid in(
	select sc1.sid from 成績 sc1 where sid in(
		select sc2.sid from 成績 sc2 where cid = 'c01'
	)
	and cid = 'c02'
)

或

select sid,sname from 學生 where sid in(
	select sc1.sid from 成績 sc1,成績 sc2 where
	sc1.sid=sc2.sid
	and sc1.cid = 'c01'
	and sc2.cid = 'c02'
)

18、查詢沒學過"李征輝"老師講授的任一門課程的學生姓名

select sid,sname from 學生 where sid not in(
	select distinct(sid) from 成績 where cid in (
		select cid from 課程 where Cteacher = '李征輝'
	)
)

19、查詢所有同學的學號、姓名、選課數、總成績

select sc.sid, s.sname, count(sc.cid), sum(Cscore) from 成績 sc 
left join 學生 s on sc.sid = s.sid 
group by sc.sid

20、查詢學過“張三”老師所教的課的同學的學號、姓名；

select sid, sname from 學生 where sid in(
	select distinct(sc.sid) from 成績 sc 
    left join 課程 c on sc.cid=c.cid where c.Cteacher = '李征輝'
) order by sid

21、查詢課程編號“01”的成績比課程編號“02”課程低的所有同學的學號、姓名；

select sid, sname from 學生 where sid in(
	select sc1.sid from 成績 sc1, 成績 sc2 where
	sc1.sid = sc2.sid
	and sc1.cid = 'c01'
	and sc2.cid = 'c02'
	and sc1.Cscore < sc2.Cscore
)

22、查詢所有課程成績小于60分的同學的學號、姓名；

select sid, sname from 學生 where sid not in(
	select distinct(sid) from 成績 where cscore >= 60
) and sid in(
	select sid from 成績 group by sid having count(sid) != 0
)

或

select sid, sname from 學生 where sid not in(
	select s.sid from 學生 s 
    left join 成績 sc on s.sid=sc.sid where Cscore > 60 or Cscore is null
)

或

select sid, sname from 學生 where sid in(
	select sc.sid from 成績 sc group by sid having max(sc.Cscore)<60
) 

23、查詢沒有學全所有課的同學的學號、姓名；

select s.sid, s.sname from 學生 s 
left join 成績 sc on s.sid=sc.sid 
group by s.sid having count(s.sid) != (
	select count(1) from 課程
)

24、查詢至少有一門課與學號為“01”的同學所學相同的同學的學號和姓名；

select sid, sname from 學生 where sid in(
	select distinct(sid) from 成績 where cid in (
		select cid from 成績 where sid = 's01'
	) and sid != 's01'
)

或

select distinct(s.sid),sname from 學生 s 
left join 成績 sc on s.sid = sc.sid where sc.cid in(
	select cid from 成績 where sid = 's01'
)and s.sid != 's01'

25、查詢和"01"號的同學學習的課程完全相同的其他同學的學號和姓名

select s.sid from 學生 s left join 成績 sc on s.sid = sc.sid where sc.cid in(
	select cid from 成績 where sid = 's01'
) group by s.sid 
having count(s.sid) = 
(select count(cid) from 成績 where sid='s01') 
and s.sid != 's01'

26、把“成績”表中“高磊”老師教的課的成績都更改為此課程的平均成績；

update 成績 set Cscore = (
	select avg(sc.Cscore) from 課程 c 
    left join 成績 sc on c.cid = sc.cid where Cteacher = '高磊'
) where cid in (
	select cid from 課程 where Cteacher = '高磊'
)

27、查詢兩門及其以上不及格課程的同學的學號，姓名及其平均成績

select s.sid, s.sname, avg(sc.Cscore) from 學生 s 
left join 成績 sc on s.sid = sc.sid where s.sid in(
	select sid from 成績 sc where Cscore < 60 group by sid having count(sid) > 2
)

28、檢索"01"課程分數小于60，按分數降序排列的學生資訊

select * from 學生 s left join 成績 sc on s.sid=sc.sid where
sc.Cscore < 60 
and sc.cid = 'c01'
order by sc.Cscore desc

29、查詢不同老師所教不同課程平均分從高到低顯示

select c.Cteacher,c.Cname, avg(sc.Cscore) from 成績 sc 
left join 課程 c on sc.cid=c.cid 
group by sc.cid 
order by avg(sc.Cscore) desc

30、查詢選修“李征輝”老師所授課程的學生中，成績最高的學生姓名及其成績

select s.sname, max(sc.Cscore) from 學生 s 
left join 成績 sc on s.sid=sc.sid where sc.cid in (
	select cid from 課程 where Cteacher = '李征輝'
)

標籤：MySQL

上一篇：比例時空圖

下一篇：MySQL Aborted_clients和 Aborted_connects狀態變數詳解