一、SQL查詢基本結(jié)構(gòu)
1. 單關(guān)系查詢
① distinct：刪除重復(fù)
select distinct dept_name from instructor;
② all：顯示指明不去除重復(fù)
select all dept_name from instructor;
③ 運(yùn)算符的算術(shù)表達(dá)式：+厢岂、-帜平、*宁赤、/
select salary * 1.1 from instructor;
④ as：old-name as new-name
找出滿足下列條件的所有教師的姓名，他們比Biology系教師的最低工資要高店展。
select T.name, S.course_id from instructor as T, teaches as S where T.ID = S.ID;
如果沒用as關(guān)鍵字的話渐逃，instructor
⑤ 字符串運(yùn)算：字符串上可以使用like操作符來實(shí)現(xiàn)模式匹配
百分號(%)：匹配任意字符串
下劃線(_)：匹配任意一個字符
找出所在建筑名稱中包含子串 'Waston' 的所有系名
select dept_name from department where building like '%Waston%';
2. 多關(guān)系查詢
查詢教師的姓名伐谈，以及所在系名和系所在的建筑名
select name, instructor.dept_name, building from instructor, department where instructor.dept_name = department.dept_name;
select name, course_id from instructor, teaches where instructor.ID = teaches.ID and instructor.dept_name = 'Comp.Sei';
① natural join：考慮這樣的元組烂完，來自關(guān)系a的元組和來自關(guān)系b的元組在共同屬性ID上的卻只相同
select name, course_id from instructor, teaches where instructor.ID = teaches.ID;
可改寫為
select name, course_id from instructor natural join teaches;
為了發(fā)揚(yáng)自然連接的優(yōu)點(diǎn)，同時避免不必要的相等屬性帶來的危險诵棵，可以采用join...using...
select name, title from teaches natural join instructor, course where teaches.course_id = course.course_id;
與下面的自然連接不等
select name, title from teaches natural join instructor natural join course;
因?yàn)閠eaches和instructor關(guān)系的自然連接的結(jié)果中有course_id和dept_name是相同屬性抠蚣，所以與course自然連接的時候會使用這兩個屬性。
使用join using 得到正確的結(jié)果
select name, title from (instructor natural join teaches) join course using (course_id);
except：兩個集合求差非春，出現(xiàn)在A中卻不出現(xiàn)在B中
select course_id from section where semester = 'Fall' and year = 2009
except
select course_id from section where semester = 'Spring' and year = 2010;
得到存在于09年秋但不存在于10年春的所有課程柱徙。
Having子句：作用于分組
與select相似，任何出現(xiàn)在having子句中奇昙，但沒有被聚集（聚集函數(shù)）的屬性必須出現(xiàn)在group by子句中护侮，否則查詢就被當(dāng)成錯誤的。
包含聚集储耐、group by和having子句的查詢的含義可通過下列操作序列來定義：
1. 與不帶聚集的查詢情況類似羊初，最先根據(jù)from子句來計(jì)算出一個關(guān)系。
2. 如果出現(xiàn)where子句，where子句中的謂詞將應(yīng)用到from子句的結(jié)果關(guān)系上长赞。
3. 如果出現(xiàn)group by子句晦攒，滿足where謂詞的元組通過group by子句形成分組。如果沒有g(shù)roup by子句得哆，滿足where謂詞的整個元組被當(dāng)做一個分組脯颜。
4. 如果出現(xiàn)having子句，它將作用在每個分組上贩据，不滿足having子句的分組被拋棄栋操。
5. select子句利用剩下的分組產(chǎn)生出查詢結(jié)果中的元組，即在每個分組上應(yīng)用聚集函數(shù)來得到單個元組饱亮。
= some 等價于in矾芙，<>all 等價于not in
exists
not exists
Mysql 不支持 with語句
MySQL create table 例子：注意foreign key reference
create table course
(course_id varchar(8),
title varchar(50),
dept_name varchar(20),
credits numeric(2,0) check (credits > 0),
primary key (course_id),
foreign key (dept_name) references department (dept_name)
on delete set null
);
習(xí)題：
3.1:
a. select title from course where dept_name = 'Comp. Sci.' and credits = 3;
b. select distinct ID from student natural join takes where course_id in ( select course_id from instructor natural join teaches where name = 'Einstein');
c. select name from instructor where salary = (select max(salary) from instructor);
d. select course_id, sec_id, count(ID) from takes where semester = 'Fall' and year = 2009 group by course_id, sec_id;
e. select max(num) from ( select course_id, count(ID) as num from takes where semester = 'Fall' and year = 2009 group by course_id) t;
f.?
mysql without with clause
mysql> select a.course_id, a.sec_id from (
-> select course_id, sec_id, count(ID) as num from takes where semester = 'Fall' and year = 2009 group by course_id, sec_id
-> ) as a where a.num = (
-> select max(b.num) from (
-> select course_id, sec_id, count(ID) as num from takes where semester = 'Fall' and year = 2009 group by course_id, sec_id
-> ) as b
-> );
3.2:
a. select sum(points * credits) from (takes natural join course) natural join grade_points where ID = '12345';
b. select sum(points * credits)/sum(credits) from (takes natural join course) natural join grade_points where ID = '12345';
c. select ID, sum(points * credits) / sum(credits) from (takes natural join course) natural join grade_points group by ID;
3.3
a. update instructor set salary = salary * 1.1 where dept_name = 'Comp. Sci.';
b. delete from course where course_id not in (select course_id from section);
c. insert into instructor (select ID, name, dept_name, 10000 from student where tot_cred > 100);
