国产探花免费观看_亚洲丰满少妇自慰呻吟_97日韩有码在线_资源在线日韩欧美_一区二区精品毛片,辰东完美世界有声小说,欢乐颂第一季,yy玄幻小说排行榜完本

首頁 > 開發(fā) > 綜合 > 正文

SQL總結(七)查詢實戰(zhàn)

2024-07-21 02:47:48
字體:
來源:轉載
供稿:網(wǎng)友
SQL總結(七)查詢實戰(zhàn) Posted on 2015-01-27 18:32 停留的風 閱讀(...) 評論(...) 編輯 收藏SQL總結(七)查詢實戰(zhàn)一、場景

給定一個場景,學生選課系統(tǒng)為例,大家很熟悉。

主要關系:

學生(學號、姓名、年齡、性別)

教師(教師ID,教師姓名)

課程(課程ID,課程名稱,任教教師ID)

成績(學生ID,課程ID,成績)

二、創(chuàng)建表并預置數(shù)據(jù)

創(chuàng)建關系表:

--學生:Student(SID,SName,SAge,SSex)--學生表(學號、姓名、年齡、性別)--性別,0表示男,1表示女----IF EXISTS(SELECT OBJECT_ID('Student')) /*此處永遠為true,原因是OBJECT_ID返回具體ID,或者NULL*/--使用下列語句,如果沒有,什么都不返回,也就不存在IF EXISTS(SELECT id FROM sysobjects WHERE name='Student')    DROP Table StudentCreate table Student(    SID nvarchar(20) PRimary key not null,    SName nvarchar(20),    SAge int,    SSex bit)--教師:Teacher(TID,TName)--教師表(教師ID,教師姓名)IF EXISTS(SELECT id FROM sysobjects WHERE name='Teacher') Drop table Teacher GOCreate table Teacher(    TID nvarchar(20) primary key not null,    TName nvarchar(20) not null,    )--課程:Course(CID,CName,TID)--課程表(課程ID,課程名稱,任教教師ID)IF EXISTS(SELECT id FROM sysobjects WHERE name='Course') BEGIN    DROP Table CourseENDCreate table Course(    CID  nvarchar(20)  primary key not null,    CName nvarchar(50) not null,    TID nvarchar(20))IF EXISTS(SELECT id FROM sysobjects WHERE name='SC') DROP TABLE SC--成績:SC(SID,CID,Score)--成績表(學生ID,課程ID,成績)Create table SC(    SID nvarchar(20) not null,    CID nvarchar(20) not null,    Score int)alter table SC add constraint PK_SC primary key(SID,CID)
預置數(shù)據(jù)

這里僅僅是個例子,針對不同的題目,可以預置適當?shù)臄?shù)據(jù)進行檢測。

/*預置數(shù)據(jù)*/DELETE FROM StudentINSERT INTO Student(SID,SName,SAge,SSex) VALUES('S001','Tom','20','0')INSERT INTO Student(SID,SName,SAge,SSex) VALUES('S002','Lucy','21','1')INSERT INTO Student(SID,SName,SAge,SSex) VALUES('S003','Jim','18','0')INSERT INTO Student(SID,SName,SAge,SSex) VALUES('S004','Brush','20','0')INSERT INTO Student(SID,SName,SAge,SSex) VALUES('S005','Kim','22','1')INSERT INTO Student(SID,SName,SAge,SSex) VALUES('S006','Fka','20','0')INSERT INTO Student(SID,SName,SAge,SSex) VALUES('S007','Cidy','17','1')INSERT INTO Student(SID,SName,SAge,SSex) VALUES('S008','YouNi','19','0')GODELETE FROM TeacherINSERT INTO Teacher(TID,TName) VALUES('T001','張三')INSERT INTO Teacher(TID,TName) VALUES('T002','李四')INSERT INTO Teacher(TID,TName) VALUES('T003','王五')GODELETE FROM CourseINSERT INTO Course(CID,CName,TID) VALUES('C01','英語','T001')INSERT INTO Course(CID,CName,TID) VALUES('C02','體育','T002')INSERT INTO Course(CID,CName,TID) VALUES('C03','數(shù)學','T003')GODELETE FROM SCINSERT INTO SC(SID,CID,Score) VALUES('S001','C01','78')INSERT INTO SC(SID,CID,Score) VALUES('S001','C02','60')INSERT INTO SC(SID,CID,Score) VALUES('S001','C03','97')INSERT INTO SC(SID,CID,Score) VALUES('S002','C01','56')INSERT INTO SC(SID,CID,Score) VALUES('S003','C01','55')INSERT INTO SC(SID,CID,Score) VALUES('S004','C01','55')GO
View Code三、具體題目

以下題目,希望是一種練習題,是對具體SQL查詢方法的具體應用。對于一些復雜查詢,也進行分步求解,希望不只是明白了一道題的解法,培養(yǎng)一種解題思路。

以后遇到類似的問題就能輕易破解。

答案默認隱藏,意在希望讀者在思考之后,再看參考答案。

當然參考答案也不一定完全正確,或許還有更優(yōu)解,如果你發(fā)現(xiàn)了,請?zhí)岢觥?/p>

1、查詢“C01”課程比“C02”課程成績高的所有學生的學號
--1) 最笨的方法--分別得到C01成績單和C02課程的成績單,然后再得到C01課程比C02課程高的學生學號SELECT SID,Score FROM SC WHERE CID='C01' SELECT SID,Score FROM SC WHERE CID='C02' SELECT A.SID FROM (SELECT SID,Score FROM SC WHERE CID='C01') AS AINNER JOIN(SELECT SID,Score FROM SC WHERE CID='C02') AS BON A.SID = B.SID WHERE A.Score>B.Score
點擊查看,參考答案2、查詢平均成績大于60分的同學的學號和平均成績
SELECT SID,AVG(Score) AS ScoreAverage FROM SC GROUP BY SID HAVING AVG(Score)>60 
點擊查看,參考答案3、查詢所有同學的學號、姓名、選課數(shù)、總成績
--1)通過Group查詢總成績和選課數(shù),然后再聯(lián)表查詢SELECT SID,COUNT(CID) AS CourseCount,SUM(Score) as SumScore FROM SC GROUP BY SIDSELECT Student.SID,SName,CourseCount,SumScore FROM Student LEFT JOIN (SELECT SID,COUNT(CID) AS CourseCount,SUM(Score) as SumScore FROM SC GROUP BY SID) AS B ON Student.SID = B.SID--2)聯(lián)表查詢后再GROUP BySELECT Student.SID,Student.Sname,COUNT(SC.CID),SUM(Score)FROM Student LEFT OUTER JOIN SC ON Student.SID=SC.SIDGROUP BY Student.SID,Sname
參考答案4、查詢姓“李”的老師的個數(shù),不能重復
SELECT COUNT(DISTINCT(TID)) FROM Teacher WHERE TName LIKE '李%'
參考答案5、查詢沒學過“張三”老師課的同學的學號、姓名
--1)查詢沒有學過“張三”老師課的同學的學號,然后再查詢得到學生姓名SELECT SID FROM SC LEFT JOIN Course ON SC.CID = Course.CID LEFT JOIN Teacher ON Course.TID = Teacher.TID WHERE Tname ='張三'SELECT SID,Sname FROM Student WHERE SID NOT IN (SELECT SID FROM SC LEFT JOIN Course ON SC.CID = Course.CID LEFT JOIN Teacher ON Course.TID = Teacher.TID WHERE Tname ='張三')--2)先查詢張三老師的所有課程,然后查詢選擇了張三老師課程的學生ID,最后查詢未選其課程的學生信息SELECT CID FROM Course INNER JOIN Teacher ON Course.TID = Teacher.TID WHERE Teacher.TName='張三'SELECT SID FROM SC LEFT JOIN (SELECT CID FROM Course INNER JOIN Teacher ON Course.TID = Teacher.TID WHERE Teacher.TName='張三') AS TeacherCID ON SC.CID = TeacherCID.CIDSELECT SID,SName FROM Student WHERE SID NOT IN(SELECT SID FROM SC LEFT JOIN (SELECT CID FROM Course INNER JOIN Teacher ON Course.TID = Teacher.TID WHERE Teacher.TName='張三') AS TeacherCID ON SC.CID = TeacherCID.CID)--3)查詢多表,獲取張三老師的課程SELECT Student.SID,Student.SName FROM Student WHERE SID NOT IN (SELECT DISTINCT(SC.SID) FROM SC,Course,Teacher WHERE  SC.CID=Course.CID and Teacher.TID=Course.TID and Teacher.Tname='張三')
參考答案6、查詢兩門以上不及格課程的同學的學號及其平均成績
--1)查詢有課程不及格的學生IDSELECT DISTINCT(SID) FROM SC WHERE Score<60SELECT SID,AVG(Score) AS ScoreAverage FROM SC GROUP BY SID HAVING  COUNT(SID)>2  AND SID IN (SELECT DISTINCT(SID) FROM SC WHERE Score<60)--2)查詢有兩門以上不及格的學號SELECT SID FROM SC WHERE Score<60 GROUP B
發(fā)表評論 共有條評論
用戶名: 密碼:
驗證碼: 匿名發(fā)表
主站蜘蛛池模板: 台北县| 霸州市| 乌恰县| 普格县| 嘉荫县| 北安市| 容城县| 中江县| 北京市| 洛南县| 宁陕县| 台中市| 益阳市| 城口县| 临夏县| 泾阳县| 沙坪坝区| 陆良县| 阳谷县| 油尖旺区| 永城市| 东乌珠穆沁旗| 麻江县| 山阳县| 黔东| 麟游县| 崇礼县| 姜堰市| 遂溪县| 全州县| 德保县| 繁昌县| 台前县| 福鼎市| 佳木斯市| 巍山| 莱州市| 郁南县| 岑巩县| 象州县| 兴国县|