一、SQL查询基本结构
1. 单关系查询
① distinct：删除重复
select distinct dept_name from instructor;
② all：显示指明不去除重复
select all dept_name from instructor;
③ 运算符的算术表达式：+、-、*、/
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关键字的话，instructor
⑤ 字符串运算：字符串上可以使用like操作符来实现模式匹配
百分号(%)：匹配任意字符串
下划线(_)：匹配任意一个字符
找出所在建筑名称中包含子串 'Waston' 的所有系名
select dept_name from department where building like '%Waston%';
2. 多关系查询
查询教师的姓名，以及所在系名和系所在的建筑名
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：考虑这样的元组，来自关系a的元组和来自关系b的元组在共同属性ID上的却只相同
select name, course_id from instructor, teaches where instructor.ID = teaches.ID;
可改写为
select name, course_id from instructor natural join teaches;
为了发扬自然连接的优点，同时避免不必要的相等属性带来的危险，可以采用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;
因为teaches和instructor关系的自然连接的结果中有course_id和dept_name是相同属性，所以与course自然连接的时候会使用这两个属性。
使用join using 得到正确的结果
select name, title from (instructor natural join teaches) join course using (course_id);
except：两个集合求差，出现在A中却不出现在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相似，任何出现在having子句中，但没有被聚集（聚集函数）的属性必须出现在group by子句中，否则查询就被当成错误的。
包含聚集、group by和having子句的查询的含义可通过下列操作序列来定义：
1. 与不带聚集的查询情况类似，最先根据from子句来计算出一个关系。
2. 如果出现where子句，where子句中的谓词将应用到from子句的结果关系上。
3. 如果出现group by子句，满足where谓词的元组通过group by子句形成分组。如果没有group by子句，满足where谓词的整个元组被当做一个分组。
4. 如果出现having子句，它将作用在每个分组上，不满足having子句的分组被抛弃。
5. select子句利用剩下的分组产生出查询结果中的元组，即在每个分组上应用聚集函数来得到单个元组。
= 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
);
学校数据库其中一些table
习题：
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);
网友评论