資料庫查詢語句

use bookms
--select後接查詢的列名，from後面接查詢的列的表名，*表示所有的列
--查詢表中的所有的資料
select * from Student

--查詢表中的某一些列
select Sno,Sname,ddept from Student

--查詢時對列名重新命名
--重新命名的幾個方式，1.新名 = 舊名 2.舊名 as 新名 3.舊名 新名
select 書名 = Bname,Bbookconcern as 出版社,Bwriter 作者 from Book

--去掉列中的重複項——distinct
--distinct後接操作的列名
select distinct ddept from Student

--查詢部分資料
--查詢表中前三行的資料
select top 3 * from Book
--查詢表中前二分之一的資料
select top 50 percent * from Book

--查詢運算後的列值
select Sno,Cno,Cgrade = Cgrade * 1.1 from Sc
select Cgrade = Cgrade * 0.5 from Sc

--條件查詢
--帶條件的查詢——比較運算子的條件
--查詢成績在70分以上（包括70分）的學生的學號、課程號和成績
select Sno,Cno,Cgrade from Sc where Cgrade >= 70
--查詢成績在70分以下的學生的學號、課程號和成績
select Sno,Cno,Cgrade from Sc where Cgrade < 70

--多條件的查詢——and,or
--查詢選擇課程號為J01的男生/查詢選擇課程號為J01的學生或者所有的男生
select * from Student where ddept = 'J01' and/or Ssex = '男'

--條件查詢——between...and...
--查詢成績在80到90之間的學生學號、課程號以及成績
select Sno,Cno,Cgrade from Sc where Cgrade between 80 and 90

--條件查詢——like模糊查詢%_[]
--查詢姓張的學生
select * from Student where Sname like'張%'
--like ‘ab%' 返回以“ab”開始的任意字串。
--like '%abc' 返回以“abc”結束的任意字串。
--like '%abc%' 返回包含“abc”的任意字串。
--like '_ab' 返回以“ab”結束的三個字元的字串。
--like '[ACK]%' 返回以“A”、“C”或“K”開始的任意字串。
--like 'M[^c]%' 返回以“M”開始且第二個字元不是“c”的任意長度的字串。

--條件查詢——in範圍內查詢
select * from Student where Sno in (17010002,17010003,17020001,17020002)

--條件查詢——is null條件為空 （is not null條件不為空）
select * from Sc where Cgrade is null

--聚合函式
--avg(列名)平均值
--通過函式名，平均分是別名
select avg(Cgrade) 平均分 from Sc   
--統計帶條件的平均分
select avg(Cgrade) 平均分 from Sc where Sno = 17010003
--統計函式可以統計字元型資料的個數，可以和distinct一起用，去掉重複項
select count(distinct ddept) 系部個數 from Student
--統計所有資料的個數
select count(*) 個數 from Student
--統計ddept列的非空資料個數
select count(ddept) 系部個數 from Student

--巢狀查詢
select Sname,Sage from Student where Sage >= all(select Sage from Student)

--分組統計人數——group by 子句將查詢結果按某一列或多列的值分組，值相等的為一組：
--統計男同學、女同學人數分別是多少
select count(Sno) 人數,Ssex from Student group by Ssex
--統計選修了2門課以上的學生的學號
select Sno from Sc group by Sno having count(Sno) >= 2

--left join on(左聯接) 返回包括左表中的所有記錄和右表中聯結欄位相等的記錄
--right join on(右聯接) 返回包括右表中的所有記錄和左表中聯結欄位相等的記錄
--inner join on(等值連線) 只返回兩個表中聯結欄位相等的行
--join on 後面：表名.列名 = 表名.列名（例如：Sc.Cno = Course.Cno）是兩個表連線的條件，
--查詢書本的名稱及其借出的時間
select Bname,Sbbegindate from Sbook,Book where Sbook.Bno = Book.Bno
select Bname,Sbbegindate from Sbook inner join Book on Sbook.Bno = Book.Bno

--查詢每個學生的姓名，所借閱的圖書名和借閱的時間
select Sname,Bname,Sbbegindate from Student,Sbook,Book where Student.Sno = Sbook.Sno and Sbook.Bno = Book.Bno
select Sname,Bname,Sbbegindate from Student inner join Sbook on Student.Sno = Sbook.Sno inner join Book on Sbook.Bno = Book.Bno

--自連線查詢，把一張表看成為邏輯上的兩張表
--查詢姓名相同的學生的資訊
select a.Sname,b.Ssex,a.ddept,b.Scardid from Student a,Student b where a.Sname = b.Sname and a.Sno != b.Sno
select a.Sname,b.Ssex,a.ddept,b.Scardid from Student a inner join Student b on a.Sname = b.Sname and a.Sno != b.Sno

--查詢課程名稱相同的課程名稱，課程編號
select a.Cname,b.Cno from Course a,Course b where a.Cname = b.Cname and a.Cno != b.Cno
select a.Cname,b.Cno from Course a inner join Course b on a.Cname = b.Cname and a.Cno != b.Cno

--外連線查詢——left join，right join
--查詢每個學生及其選修課程的成績情況（含未選課的學生資訊）
select Student.*,Cgrade from Student left join Sc on Student.Sno = Sc.Sno

--查詢所有書本的借出情況
--包括沒有借出的書本，列出書名、圖書號、借書時間
select Bname,Book.Bno,Sbbegindate from Book left join Sbook on Book.Bno = Sbook.Bno

--在教學庫中查詢每個學生及其選修課程的情況（含未選修的學生資訊及未被學生選修的課程資訊）
--全連線（FULL JOIN）
select Cgrade,Sname,Cname from Student full join Sc on Student.Sno = Sc.Sno full join Course on Sc.Cno = Course.Cno

--查詢和‘李勇’在同一個專業學習的學生的學號、姓名和專業
select Ddept from Student where Sname = '李勇'
select Sno,Sname,Ddept from Student where Ddept = 'J01'
--子查詢
select Sno,Sname,ddept from Student where ddept = (select ddept from Student where Sname = '李勇')
--自連線查詢
select b.Sno,b.Sname,a.ddept from Student a join Student b on a.ddept = b.ddept where a.Sname = '李勇'

--查詢年齡最大的學生的學號和姓名
select Sno,Sname from Student where Sage >= (select MAX(Sage) from Student)

--查詢與任何J01專業同齡的學生資訊——any
select * from Student where Sage = any(select Sage from Student where ddept = 'J01')

--查詢所有學生中分數最高的學號,成績
select Sno,Cgrade from Sc where Cgrade >= any(select MAX(Cgrade) from Sc)

--查詢學生的姓名、學號、課程名、成績的相關資料存放在表“成績單”中，並對新表進行查詢
select Sname,Student.Sno,Cname,Cgrade into 成績單 from Student join Sc on Student.Sno = Sc.Sno join Course on Sc.Cno = Course.Cno
select * from 成績單