Mysql復(fù)習(xí)必備----50條經(jīng)典Sql語(yǔ)句

關(guān)于學(xué)生，課程，成績(jī)，教師表

student(學(xué)號(hào)#,姓名,性別,年齡)

course(課程號(hào)#，課程名，教師號(hào)#)

score(學(xué)號(hào)#，課程號(hào)#，成績(jī))

teacher(教師號(hào)#，教師名)


--1.查詢“001”課程比“002”課程成績(jī)高的所有學(xué)生的學(xué)號(hào)?

select a.stuNo from score a,score b?

where a.cNo='c001' and b.cNo='c002' and a.stuNo=b.stuNo and a.score>b.score?

--2.查詢平均成績(jī)大于60分的同學(xué)的學(xué)號(hào)和平均成績(jī)?

select stuNo,avg(score)from score?

group by stuNo?

having avg(score)>60?

--3.查詢所有同學(xué)的學(xué)號(hào)、姓名、選課數(shù)、總成績(jī)?

select a.stuNo,a.stuName,count(cNo),sum(score) from student a,score b?

where a.stuNo=b.stuNo?

group by a.stuNo,a.stuName?

--4.查詢姓“趙”的老師的個(gè)數(shù)?

select count(tName),tName from teacher?

where tName like '趙%'?

group by tName?

--5.查詢沒學(xué)過(guò)“某某”老師課的同學(xué)的學(xué)號(hào)、姓名?

select stuNo,stuName from student?

where stuNo not in?

(select a.stuNo from student a,score b where a.stuNo=b.stuNo and cNo in?

(select d.cNo from teacher c,course d where c.tNo=d.tNo and c.tName='錢市保'))?

--6.查詢學(xué)過(guò)“001”并且也學(xué)過(guò)編號(hào)“002”課程的同學(xué)的學(xué)號(hào)、姓名；?

select a.stuNo,a.stuName from student a,score b,score c?

where a.stuNo=b.stuNo and b.stuNo=c.stuNo and b.cNo='c001' and c.cNo='c002'?

--7.查詢學(xué)過(guò)“某某”老師所教的所有課的同學(xué)的學(xué)號(hào)、姓名?

select stuNo,stuName from student?

where stuNo in (select stuNo from score a,course b,teacher c?

where a.cNo=b.cNo and b.tNo=c.tNo and c.tName='錢市保'?

group by stuNo? ? ? ? ? ? ?

having count(a.cNo)>=(select count(cNo) from course d,teacher e?

where d.tNo=e.tNo and e.tName='錢市保'))?

--老師所教課程為一門課?

select stuNo,stuName from student?

where stuNo in (select a.stuNo from student a,score b where a.stuNo=b.stuNo and b.cNo in?

(select cNo from teacher c,course d where c.tNo=d.tNo and c.tName='錢市保'))?

--8.查詢課程編號(hào)“002”的成績(jī)比課程編號(hào)“001”課程低的所有同學(xué)的學(xué)號(hào)、姓名?

select stuNo,stuName from student?

where stuNo in?

(select a.stuNo from score a,score b?

where a.cNo='c001' and b.cNo='c002' and a.stuNo=b.stuNo and a.score>b.score)?

--9.查詢所有課程成績(jī)小于60分的同學(xué)的學(xué)號(hào)、姓名?

select stuNo,stuName from student?

where stuNo in (select stuNo from score?

where score<60?

group by stuNo?

having count(cNo)=(select count(cNo) from course))?

--10.查詢沒有學(xué)全所有課的同學(xué)的學(xué)號(hào)、姓名?

select b.stuNo,a.stuName,count(b.cNo) from student a,score b?

where a.stuNo=b.stuNo?

group by b.stuNo,a.stuName?

having count(b.cNo)<(select count(cNo) from course)?

--11.查詢至少有一門課與學(xué)號(hào)為“1”的同學(xué)所學(xué)相同的同學(xué)的學(xué)號(hào)和姓名?

select distinct a.stuNo,stuName from student a,score b?

where a.stuNo=b.stuNo and cNo in (select cNo from score?

where stuNo='001')?

--12.查詢至少學(xué)過(guò)學(xué)號(hào)為“001”同學(xué)一門課的其他同學(xué)學(xué)號(hào)和姓名?

&&& select distinct a.stuNo,stuName from student a,score b?

where a.stuNo=b.stuNo and cNo all join (select cNo from score?

where stuNo='001')?

--13.把“SC”表中“某某”老師教的課的成績(jī)都更改為此課程的平均成績(jī)?

update score set score=savg?

from score d,(select avg(score) as savg,a.cNo from score a,course b,teacher c?

where a.cNo=b.cNo and b.tNo=c.tNo and tName='錢市保'?

group by a.cNo) e?

where d.cNo=e.cNo?

--老師所教課程為一門課?

update score?

set score=(select avg(score) from score?

group by cNo?

having cNo=(select a.cNo from course a,teacher b where a.tNo=b.tNo and b.tName='錢市保'))?

where cNo=(select a.cNo from course a,teacher b where a.tNo=b.tNo and b.tName='錢市保')?

select * from score?

--14.查詢和“001”號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)學(xué)號(hào)和姓名?

select stuNo from score?

where cNo in (select cNo from score where stuNo='005')?

group by stuNo?

having count(cNo)=(select count(*) from score where stuNo='005')?

--15.刪除學(xué)習(xí)“某某”老師課的SC表記錄?

delete from score where cNo=(select cNo from course a,teacher b where a.tNo=b.tNo and b.tName='錢市保')?

select * from score?

--16.向SC表中插入一些記錄，這些記錄要求符合以下條件：沒有上過(guò)編號(hào)“003”課程的同學(xué)學(xué)號(hào)、2號(hào)課的平均成績(jī)?

--17.按平均成績(jī)從高到低顯示所有學(xué)生的“C語(yǔ)言”、“sql”、“java”三門的課程成績(jī)?

--按如下形式顯示： 學(xué)生ID,C語(yǔ)言,sql,JAVA,有效課程數(shù),有效平均分?

--18.查詢各科成績(jī)最高和最低的分：以如下形式顯示：課程ID，最高分，最低分?

select? cNo,max(score) as 最高分,min(score) as 最低分 from score?

group by cNo?

--19.按各科平均成績(jī)從低到高和及格率的百分?jǐn)?shù)從高到低順序?

select avg(c.score),count(a.score)/count(b.score) from score c,(select a.cNo,count(a.score) from score a?

where a.score<60?

group by a.cNo) d,(select b.cNo,count(b.score) from score b?

group by b.cNo) e?

where d.cNo=e.cNo?

group by c.cNo?

order by avg(c.score) desc?

(select a.cNo,count(a.score) from score a?

where a.score<60?

group by a.cNo) d?

(select b.cNo,count(b.score) from score b?

group by b.cNo) e?

--20.查詢?nèi)缦抡n程平均成績(jī)和及格率的百分?jǐn)?shù)(用"1行"顯示): C語(yǔ)言（001），數(shù)據(jù)結(jié)構(gòu)（002），JAVA（003），離散數(shù)學(xué)（004）?

--21.查詢不同老師所教不同課程平均分從高到低顯示?

select tNo,a.cNo,avg(score) from course a,score b?

where a.cNo=b.cNo?

group by tNo,a.cNo?

order by avg(score) desc?

--22.查詢?nèi)缦抡n程成績(jī)第 3 名到第 6 名的學(xué)生成績(jī)單：C語(yǔ)言（001），數(shù)據(jù)結(jié)構(gòu)（002），JAVA（003），離散數(shù)學(xué)（004）?

--? [學(xué)生ID],[學(xué)生姓名],C語(yǔ)言,數(shù)據(jù)結(jié)構(gòu),JAVA,離散數(shù)學(xué),平均成績(jī)?

--23.統(tǒng)計(jì)列印各科成績(jī),各分?jǐn)?shù)段人數(shù):課程ID,課程名稱,[100-85],[85-70],[70-60],[ <60]?

select distinct e.cNo,count(a.stuNo) as '100-85',count(b.stuNo) as '85-70',count(c.stuNo) as '70-60',count(d.stuNo) as '<60' from score a,score b,score c,score d,score e?

where a.cNo in (select cNo from course) and a.score between 85 and 100 and b.cNo in (select cNo from course) and b.score between 71 and 84 and c.cNo in (select cNo from course) and? c.score between 60 and 70 and d.cNo in (select cNo from course) and d.score<60?

group by e.cNo,a.stuNo,b.stuNo,c.stuNo,d.stuNo?

having a.stuNo<>b.stuNo and a.stuNo<>c.stuNo and a.stuNo<>d.stuNo and b.stuNo<>c.stuNo and b.stuNo<>d.stuNo and c.stuNo<>d.stuNo?

select cNo,count(stuNo) from score?

where score between 70 and 100 and cNo='c001'?

group by cNo?

--24.查詢學(xué)生平均成績(jī)及其名次?

select stuNo,avg(score) from score?

group by stuNo?

order by avg(score) desc?

--25.查詢各科成績(jī)前三名的記錄:(不考慮成績(jī)并列情況)?

select a.stuNo,a.cNo,a.score?

from score a?

where a.score in (select top 3 score from score b?

where a.cNo=b.cNo?

order by score)?

order by a.cNo?

--26.查詢每門課程被選修的學(xué)生數(shù)?

select b.cNo ,count(stuNo) from score a right join course? b?

on a.cNo=b.cNo?

group by b.cNo?

--27.查詢出只選修了一門課程的全部學(xué)生的學(xué)號(hào)和姓名?

select b.stuNo,a.stuName from student a,score b?

where a.stuNo=b.stuNo?

group by b.stuNo,a.stuName?

having count(b.cNo)=1?

--28.查詢男生、女生人數(shù)?

select stuSex,count(stuSex) from student?

group by stuSex?

--29.查詢姓‘zhao’的學(xué)生名單?

select * from student?

where stuName like '趙%'?


--30.查詢同名同性學(xué)生名單，并統(tǒng)計(jì)同名人數(shù)?

select a.stuNo,a.stuName,count(a.stuNo) from student a,student b?

where a.stuName=b.stuName and a.stuSex=b.stuSex and a.stuNo<>b.stuNo?

group by a.stuNo,a.stuName?


--31.1981年出生的學(xué)生名單(注：Student表中Sage列的類型是datetime?

--32.、查詢每門課程的平均成績(jī)，結(jié)果按平均成績(jī)升序排列，平均成績(jī)相同時(shí)，按課程號(hào)降序排列?

select cNo,avg(score) from score?

group by cNo?

order by avg(score) asc,cNo?

--33.查詢平均成績(jī)大于70的所有學(xué)生的學(xué)號(hào)、姓名和平均成績(jī)?

select b.stuNo,a.stuName,avg(score) from student a,score b?

where a.stuNo=b.stuNo?

group by b.stuNo,a.stuName?

having avg(score)>70?

--34.查詢課程名稱為“java”，且分?jǐn)?shù)低于70的學(xué)生姓名和分?jǐn)?shù)?

select a.stuName,b.score from student a,score b?

where a.stuNo=b.stuNo and score<70 and b.cNo=(select cNo from course where cName='java')?

--35.查詢所有學(xué)生的選課情況?

select a.stuNo,c.cNo from student a,score b,course c?

where a.stuNo=b.stuNo and b.cNo=c.cNo?

order by a.stuNo?

select a.stuNo,cNo from student a left join (select a.stuNo,c.cNo from student a,score b,course c?

where a.stuNo=b.stuNo and b.cNo=c.cNo) d?

on a.stuNo=d.stuNo?

order by a.stuNo?

--36.查詢?nèi)魏我婚T課程成績(jī)?cè)?0分以上的姓名、課程名稱和分?jǐn)?shù)?

select a.stuName,b.cNo,score from student a,score b?

where score>70 and a.stuNo=b.stuNo?

--37.查詢不及格的課程，并按課程號(hào)從大到小排列?

select cNo,score from score?

where score<60?

order by cNo?

--38.查詢課程編號(hào)為003且課程成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和姓名?

select b.stuNo,a.stuName from student a,score b?

where b.cNo='c003' and score>60 and a.stuNo=b.stuNo?

--39.求選了課程的學(xué)生人數(shù)?

select count(a.stuNo) from (select distinct stuNo from score) a?

--40.查詢選修“趙”老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生姓名及其成績(jī)?

select b.stuNo,a.stuName,max(score) from student a,score b?

where a.stuNo=b.stuNo and b.cNo in (select a.cNo from course a,teacher b where a.tNo=b.tNo and b.tName='錢市保')?

group by b.stuNo,a.stuName,b.cNo?

having b.cNo in (select a.cNo from course a,teacher b where a.tNo=b.tNo and b.tName='錢市保')?

--41.查詢各個(gè)課程及相應(yīng)的選修人數(shù)?

select cNo,count(stuNo) from score?

group by cNo?

select b.cNo ,count(stuNo) from score a right join course? b?

on a.cNo=b.cNo?

group by b.cNo?

--42.查詢不同課程成績(jī)相同的學(xué)生的學(xué)號(hào)、課程號(hào)、學(xué)生成績(jī)?

select a.stuNo,a.cNo,a.score from score a,score b?

where a.stuNo=b.stuNo and a.score=b.score and a.cNo<>b.cNo?

--43. 查詢每門功成績(jī)最好的前兩名?

select a.stuNo,a.cNo,a.score?

from score a?

where score in(select top 2 score from score b?

where a.cNo=b.cNo?

order by score desc)?

order by a.cNo?

--44.統(tǒng)計(jì)每門課程的學(xué)生選修人數(shù)（超過(guò)2人的課程才統(tǒng)計(jì)）。要求輸出課程號(hào)和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列,若人數(shù)相同，按課程號(hào)升序排列?

select cNo,count(stuNo) 課程數(shù) from score?

group by cNo?

having count(stuNo)>2?

order by count(stuNo) desc,cNo?

--45.檢索至少選修兩門課程的學(xué)生學(xué)號(hào)?

select stuNo from score?

group by stuNo?

having count(cNo)>=2?

--46.查詢?nèi)繉W(xué)生都選修的課程的課程號(hào)和課程名?

select a.cNo,b.cName from score a,course b?

where a.cNo=b.cNo?

group by a.cNo,b.cName?

having count(a.stuNo)=(select count(stuNo) from student)?

select a.cNo,b.cName from score a,course b?

group by a.cNo,b.cName,b.cNo?

having a.cNo=b.cNo and count(a.stuNo)=(select count(stuNo) from student)?

--47.查詢沒學(xué)過(guò)“錢”老師講授的任一門課程的學(xué)生姓名?

select stuNo,stuName from student?

where stuNo not in (select stuNo from score a,course b,teacher c?

where a.cNo=b.cNo and b.tNo=c.tNo and c.tName='錢市保'?

group by stuNo?

having count(a.cNo)<=(select count(cNo) from course d,teacher e?

where d.tNo=e.tNo and e.tName='錢市保'))?

select stuNo,stuName from student?

where stuNo not in?

(select stuNo from score where cNo in?

(select cNo from teacher c,course d where c.tNo=d.tNo and c.tName='錢市保'))?

--48.查詢兩門以上不及格課程的同學(xué)的學(xué)號(hào)及其平均成績(jī)?

select stuNo,avg(score) from score?

where score<60?

group by stuNo?

having count(cNo)>2?

--49.檢索“004”課程分?jǐn)?shù)小于60，按分?jǐn)?shù)降序排列的同學(xué)學(xué)號(hào)?

select stuNo from score?

where score<60 and cNo='c004'?

order by score desc?

--50.刪除“2”同學(xué)的“001”課程的成績(jī)?

delete from score where stuNo='002' and cNo='c001'

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • 1）.創(chuàng)建數(shù)據(jù)庫(kù) create database學(xué)生選課數(shù)據(jù)庫(kù) 2）.創(chuàng)建四張表 Create table Stu...
    blvftigd閱讀 1,690評(píng)論 0贊 0
  • 最近打算采用關(guān)系型數(shù)據(jù)庫(kù)來(lái)理一下公司的運(yùn)營(yíng)數(shù)據(jù)，先拿點(diǎn)東西練手找感覺。下面是幾個(gè)關(guān)于學(xué)生課業(yè)的表，需要建立一個(gè)數(shù)據(jù)...
    九天朱雀閱讀 1,046評(píng)論 0贊 3
  • 希望對(duì)大家面試有幫助,很經(jīng)典的幾道題,大家有興趣的話,歡迎大家加群討論,QQ群號(hào):295383988 問題及描述：...
    hc愛編程閱讀 1,957評(píng)論 0贊 12
  • 50個(gè)常用的sql語(yǔ)句Student(S#,Sname,Sage,Ssex) 學(xué)生表Course(C#,Cname...
    哈哈海閱讀 1,334評(píng)論 0贊 7
  • 作為一枚Java后端開發(fā)者，數(shù)據(jù)庫(kù)知識(shí)必不可少，對(duì)數(shù)據(jù)庫(kù)的掌握熟悉度的考察也是對(duì)這個(gè)人是否有扎實(shí)基本功的考察。特別...
    丶Orz丶閱讀 557評(píng)論 0贊 0

友情鏈接更多精彩內(nèi)容