DBMS
- Query Languages
- Interface and maintaining tools(GUI)
- APIs
- Class Library
QL 不是圖靈完備的，不是一種編程語言。
QL
SQL是一種非過程化的查詢語言。
- DDL數據定義語言：表，視圖
- QL 查詢語言
- DML 數據操縱語言
- DCL 數據控制語言
Base table：基表，實際存在的表
View： 視圖，不是實際存在的表，虛表
SELECT [DISTINCT] target_list
FROM ralation_list
WHERE qualification
Conceptual Evaluation Strategy
- 計算笛卡爾乘積
- 用
WHERE子句做篩選 - 根據
target_list做投影 - 根據是否有
DISTINCT消除重復
在不引起混亂的情況下，多表查詢可以不加別名。
使用distinct的時候需要注意，一般必須含有主鍵或者有unique約束的鍵。
SQL支持like表達的模糊查詢
查詢實例：
三張表：
Sailors :sid sname age rating
Boats : bid bname color
Reserves : sid bid day
create table Sailors
(sid int primary key,sname nvarchar(20) not null,rating int not null,age float
)go create table Boats
(bid int primary key,bname nvarchar(20) not null,color nvarchar(20) not null
)go create table Reserves
(sid int foreign key references Sailors(sid),bid int foreign key references Boats(bid),day date not null,primary key(sid, bid)
)goinsert into Sailors values (22,'dustin',7,45.0)
insert into Sailors values (31,'lubber',8,55.0)
insert into Sailors values (58,'rusty',10,35.0)
insert into Sailors values (28,'yuppy',9,35.0)
insert into Sailors values (44,'guppy',5,35.0)
insert into Sailors values(11,'dustin',8,20)insert into Boats values (101,'tiger','red')
insert into Boats values (103,'lion','green')
insert into Boats values (105,'hero','blue')insert into Reserves values (22,101,'1996-10-10')
insert into Reserves values (58,103,'1996-11-12')
insert into Reserves values (58,101,'1996-12-12')
insert into Reserves values(58,105,'1996-11-11')select * from Sailors
go
select * from Boats
go
select * from Reserves
SELECT S.age,age1 = S.age-5, 2*S.age As age2
FROM Sailors S
WHERE S.sname LIKE 'B_%B'--查找名字以B開頭結尾且至少有三個字符的人的年齡
使用as給列起別名在有的系統中不適用。
查詢預定過紅色或者綠色船的水手信息：
使用or或者分別查詢后使用union。需要注意的是使用union的兩張表需要滿足并兼容的條件。這里的兩張表是滿足的。
查詢預訂過紅色和綠色船的水手信息：
- 做
Reserves的自連接
SELECT S.sid
FROM Sailors S, Boats B1, Reserves R1, Boats B2, Reserves R2
WHERE S.sid=R1.sid AND S.sid=R2.sid AND R1.bid=B1.bid AND R2.bid=B2.bid and (B1.color='red' AND B2.color='green')
- 使用
INTERSECT
SELECT S.sid
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND B.bid=R.bid AND B.color='red'
INTERSECT
SELECT S.sid
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND B.bid=R.bid AND B.color='green'
需要注意的是集合的交INTERSECT的兩個表必須并兼容。
嵌套查詢
預定過編號103號船的水手的姓名：
非關聯子查詢：
SELECT S.sname
FROM Sailors S
WHERE S.sid IN ( SELECT R.sid FROM Reserves R WHERE R.bid=103)
關聯嵌套子查詢：
SELECT S.sname
FROM Sailors S
WHERE EXISTS (SELECT *FROM Reserves RWHERE R.bid=103 AND S.sid=R.sid)
關聯嵌套子查詢的效率一般比非關聯嵌套子查詢的效率低。
在子查詢中內層查詢可以直接使用外層查詢的值，相當于嵌套循環
預訂過103號船并且只預訂過一次船的水手的姓名：
SELECT S1.sname
FROM (SELECT *FROM Sailors SWHERE (SELECT COUNT(*) FROM Reserves R WHERE R.sid=S.sid)=2) S1
WHERE S1.sid IN(SELECT R.sid FROM Reserves R WHERE R.bid=103)
預訂過103號船并且只預訂過一次103號船的水手的姓名：
SELECT S.sname
FROM Sailors S
WHERE ((SELECT COUNT(*) FROM Reserves R WHERE R.sid=S.sid AND R.bid=103)=1)
查找只有一個人預訂的船：
不使用COUNT的方法：從Resevers得到所有沒有被其他人訂過的船
SELECT B.bname
FROM Boats B,Reserves R1
WHERE B.bid=R1.bid AND B.bid NOT IN (SELECT R2.bid FROM Reserves R2 WHERE R2.sid<>R1.sid)
使用COUNT的方法：
SELECT B.bname
FROM Boats B
WHERE (SELECT COUNT(*) FROM (SELECT DISTINCT R.bid,R.sid FROM Reserves R) R WHERE R.bid=B.bid)=1
還可以使用UNIQUE和NOT UNIQUE
ANY ALL
找到比任意一個叫dustin的級別高的人的姓名
SELECT *
FROM Sailors S
WHERE S.rating > ANY (SELECT S2.rating FROM Sailors S2 WHERE S2.sname='dustin')
找到比所有叫dustin的級別高的人的姓名
SELECT *
FROM Sailors S
WHERE S.rating > ALL (SELECT S2.rating FROM Sailors S2 WHERE S2.sname='dustin')
查找某個表中同時滿足另一個表所有條件的信息的時候使用除法
查找預訂過所有船的水手的姓名
- Solution 1:否定之否定，使用
EXCEPT
SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS((SELECT B.bid FROM Boats B)EXCEPT --集合差(SELECT R.bid FROM Reserves R WHERE R.sid=S.sid))
- Solution 2:不使用
EXCEPT
--不存在有船他沒有租過的人
SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS(SELECT * FROM Boats B WHERE B.bid NOT IN (SELECT R.bid FROM Reserves R WHERE R.sid=s.sid))--等價于
SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS(SELECT * FROM Boats B WHERE NOT EXISTS (SELECT * FROM Reserves R WHERE R.sid=s.sid AND R.bid=B.bid))
聚合函數
COUNT(*)COUNT([DISTINCT] A)查詢屬性A有多少個不同的值SUM([DISTINCT] A )對（不同的）屬性A進行求和AVG([DISTINCT] A)對（不同的）屬性A求平均值MIN(A)MAX(A)
SELECT COUNT(DISTINCT S.rating)
FROM Sailors S
WHERE S.name='Bob'
分組聚集group by
SELECT [DISTINCT] target-list
FROM relation-list
WHERE qualification
GROUP BY grouping-list
HAVING group-qualification
首先對from子句對表進行笛卡爾乘積，根據where子句對元組進行篩選，對篩選的結果根據group-by的值相同條件進行分組，然后對計算的結果根據having后的條件對分組進行篩選，最后再根據分組計算select子句后面的值，這要求select和having后的值對每個組都是單一的（是分組屬性集的子集）。
SQL無法簡單的從語法上確定。
有了group by 以后，計算是在分組上進行的，如果沒有，是在表上進行的。
分組其實是做排序，然后再將值相同的分組。
SELECT S.rating,MIN(S.age) as minage
FROM Sailors S
WHERE S.age>=18
GROUP BY S.rating
HAVING COUNT(*)>1 AND EVERY(S.age<=60)--EVERY對應的還有ANY
EVERY要求每個分組的每個元素都必須滿足要求，ANY要求每個分組至少有一個元素滿足要求
查詢每一條紅船的預訂人數：
SELECT B.bid,COUNT(*) AS scount
FROM Boats B,Resevers R
WHERE R.bid=B.bid AND B.color='red'
GROUP BY B.bid
下面的語句會報錯：
SELECT B.bid,COUNT(*) AS scount
FROM Boats B,Resevers R
WHERE R.bid=B.bid
GROUP BY B.bid
HAVING B.color='red'
報錯的原因是數據庫的語法檢查比較簡單，不會按照業務的語義來進行判斷，只會簡單的判斷SELECT 和 HAVING后面的子句有沒有在GROUP BY后面出現
因為這里的每一種船只有一種顏色，因此我們可以在聚合的時候加上B.color條件，這樣上面的查詢就可以了。
SELECT B.bid,COUNT(*) AS scount
FROM Boats B,Reserves R
WHERE R.bid=B.bid
GROUP BY B.bid,B.color
HAVING B.color='red'
對于每個至少有兩個人的級別，找出年齡大于18歲的最小年齡。
SELECT S.rating, MIN(S.age)
FROM Sailors S
WHERE S.age > 18
GROUP BY S.rating
HAVING 1<(SELECT COUNT(*) FROM Sailors S1 WHERE S1.rating=S.rating)
對于組中屬性的篩選只能通過WHERE子句，篩選過的元組再進行分組的時候需要對分組進行篩選，但是這里的分組已經不是以前的分組了，因此需要在子句中再使用子查詢。
查找平均年齡最小的級別：
SELECT top 1 S.rating,AVG(S.age) as 'avgAge'
FROM Sailors S
GROUP BY S.rating
ORDER BY avgAge
NULL值
不是0，不是""，是不知道。
Case表達式
--Officers(name,status,rank,title)
SELECT name,Case statusWHEN 1 THEN 'Active Duty'WHEN 2 THEN 'Reserve'WHEN 3 THEN 'Special Assignment'WHEN 4 THEN 'Retired'ELSE 'Unknown'END AS status
FROM Officers
--Machines(serialno, type, year, hours_used, accidents)
--Find the rate of the accidents of "chain saw" in the whole accidentsSELECT sum(CASE WHEN type='chain saw' THEN accidentsELSE 0e0END)/sum(accidents)
FROM Machines
注意如果CASE后面有字段名，則WHEN后面應該是該字段名的值的情況，如果沒有的話WHEN后面應該是布爾表達式
還需要注意的是可以同時對兩個聚合函數的值進行運算。
--查找每種設備的平均故障率SELECT type,CASE WHEN sum(hours_used)>0 THEN sum(accidents)/sum(hours_used)ELSE NULLEND AS accident_rate
FROM Machines
GROUP BY type
上面的查詢語句使用CASE語句的主要原因是可能有的設備沒有使用過，因此沒有故障率一說。如果非要計算的話有可能導致分母為0的情況。
CASE語句對于需要分情況處理的語句效果比較好。
對于含有GROUP BY語句的查詢，需要把SELECT里面的語句都對分組后進行處理。
對于上面的語句我們當然也可以在HAVING語句中對組進行篩選后再進行計算，可是這樣做的話就無法得到那些沒有時長的組的信息。
子查詢：
標量子查詢：查詢的結果是一個值，一般使用聚合函數
在SQL語句中，凡是可以出現一個值的地方，都可以出現標量子查詢。
SELECT d.deptno,d.deptname,(SELECT MAX(salary)FROM empWHERE deptno=d.deptno) as maxpay
FROM dept as d
WHERE d.location='NEW YORK'
在SELECT語句中也可以使用子查詢。
當然我們也可以使用聯表查詢。
表表達式：查詢的結果又是一張表
SELECT startyear,avg(pay)
FROM (SELECT name,salary+bonus as pay, year(startdate) as startyearFROM emp )as emp2
GROUP BY startyear
FROM子句中也可以出現子查詢，但是需要注意的是不可以在該語句的其他子查詢直接使用該子查詢得到的臨時表。
表表達式一般出現在FROM子句中
公共表表達式：如果多次使用同一個，只定義一次，多次使用
WITH子句定義公共子表達式，其實是一個臨時視圖
--尋找部門總收入最高的部門
WITH payroll(deptno,totalpay) AS(SELECT deptno,sum(salary)+sum(bonus)FROM empGROUP BY deptno)
SELECT deptno
FROM payroll
WHERE totalpay = (SELECT max(totalpay) FROM payroll)
--查找一個部門對，第一個部門的平均工資大于第二個的兩倍
WITH deptavg(deptno,avgsal) AS(SELECT deptno,avg(salary)FROM empGROUP BY deptno)
SELECT d1.deptno,d1.avgsal,d2.deptno,d2.avgsal
FROM deptavg AS d1, deptavg AS d2
WHERE d1.avgsal>2*d2.avgsal
需要注意的是WITH和SELECT之間沒有沒有逗號，整個合在一起是一條語句。
外連接
有兩種集合差操作：
EXCEPT：會消除重復元組，需要排序EXCEPT ALL：如果確信不會出現重復元組或者重復元組對結果沒有影響就是用這個，效率更高
上面所有都是一條SQL語句。
遞歸查詢
聯邦雇員FedEmp(name,salary,manager)
找到胡佛手下超過10萬員的雇員（包括簡接雇員）
WITH agents(name,salary) AS((SELECT name,salary)FROM empWHERE manager='Hoover')UNION ALL(SELECT f.name,f.salaryFROM agents as a,FedEmp as fWHERE f.manager=a.name))
SELECT name FROM agents WHERE salary>100000
DML
INSERT INTO table-name VALUES (); --插入一條元組
DELETE FROM table-name WHERE 條件
UPDATE table-list SET 字段名='' WHERE 條件
VIEW
- 普通視圖
CREATE VIEW view-name AS (SELECT 語句)
視圖的定義會進行保存
- 虛表
- 實現數據的邏輯獨立性
- 數據安全性
- 視圖更新問題：早期系統不能進行更新。如果視圖中的信息可以和基表中的信心一一對應，唯一映射的話，就可以進行修改。不同產品可能不同。
- 臨時視圖
定義不會進行保存，支持遞歸查詢
WITH table-list() AS ()
程序設計語言訪問數據庫
嵌入式SQL
- 以
EXEC SQL開始，以;結束
