SQL是一门查询语言；
然而除了数据库查询，它还具有很多别的功能，它可以定义数据结构、修改数据库中的数据以及说明安全性约束条件等。
1. SQL查询语言概览
SQL语言有以下几个部分：数据定义语言、数据操纵语言、完整性、视图定义、事务控制、嵌入式SQL和动态SQL、授权
2. SQL数据定义
- 每个关系的模式
- 每个属性的取值类型
- 完整性约束
- 每个关系维护的索引集合
- 每个关系的安全性和权限信息
- 每个关系在磁盘上的物理存储结构
2.1 基本类型：char(n), varchar(n), int, smallint, numeric(p, d), real/double precision, float(n)
2.2 基本模式定义：create table
create table department
(dept_name varchar(20),
building varchar(15),
budget numeric(12,2),
primary key(dept_name));
创建了一个department表，表内有三个属性，dept_name是最长20位的字符串，building是最长15位的字符串，budget是一个12位的数，其中2位数字在小数点后面。同时指明了dept_name属性是department关系的主码。
create table 命令的通用形式：
create table r
(A1 D1,
A2 D2,
A3 D3,
<完整性约束>
...
<完整性约束>);
A表示r关系中的一个属性名，D表示A的域。
primary key(A1, A2,..., An)表示A1,A2,...,An构成关系的主码；主码属性必须非空且唯一：没有一个元组在主码属性上取空值，关系中没有两个元组在所有主码属性上取值相同。
foreign key(A1, A2, ..., An) references s 表示任意元组在属性(A1, A2, ..., An)上的取值必须对应于关系s中某元组在主码属性上的取值。
insert into instructor
values(10211, 'Smith', 'Biology', 66000);
向instructor关系表中添加一个元组，其中id为10211，姓名为Smith，课程为Biology， 工资为66000美元。
delete from student 从student关系中删除所有元组
drop table r 从数据库中删除关于被去掉信息的所有信息
alter table r add A D;在关系r中添加属性，A为待添加的属性，D为待添加属性的域，关系中所有元组在新属性上的取值将被设为null
3. SQL查询的基本结构
由三个子句构成：select, from, where。查询的输入是在from子句中列出的关系，在这些关系上进行where和select子句中制定的运算，然后产生一个关系作为结果。
3.1 单关系查询
select name from instructor;
select dept_name from instructor;
select distinct dept_name from instructor; distinct 表示查询结果去重复
select name from instructor where dept_name = 'Comp. Sci.' and salary*1.2 > 8000;
3.2 多关系查询
select name, instructor.dept_name, building from instructor, department where instructor.dept_name = department.dept_name;
通常来说，一个SQL查询的含义可以理解如下：
- 为from子句列出的关系产生笛卡尔积
- 在步骤1的结果上应用where子句中指定的谓词
- 对于步骤2结果中的每个元组，输出select子句中指定的属性（或表达式的结果）
3.3 自然连接
select name, course_id from instructor natural join teaches; 等价于
select name, course_id from instructor, teaches where instructor.ID = teaches.ID;
select name, title from (instructor natural join teaches) join course using (course_id);
4 附加的基本运算
4.1 更名运算
select name as instructor_name, course_id from instructor, teaches where instructor.ID = teaches.ID;
select distinct T.name from instructor as T, instructor as S where T.salary > S.salary and S.dept_name='Biology'; 从instructor关系中选择所有老师的名称，这些老师至少比biology系中的某一个老师的工资要高。
4.2 字符串运算
百分号（%）：匹配任意子串
下划线（_）：匹配任意一个字符
使用比较运算符like来表达模式
select dept_name from department where building like '%ab\%cd%' excape '\' 在department关系中选择dept_name属性，约束条件为元组的building属性中包含ab%cd。
4.3 select子句中的属性说明
星号‘*’可以用在select子句中表示“所有的属性”
select instructor.* from instructor, teaches where instructor.ID = teaches.ID;
4.4 排列元组的显示次序
select name from instructor where dept_name = 'Physics' order by name;
select * from instructor order by salary desc, name asc; 在instructor关系中选择所有元组的属性，按salary属性降序， name属性升序排列。
4.5 where子句谓词
select name from instructor where salary between 90000 and 100000; 等价于
select name from instructor where salary <= 100000 and salary >= 90000;
select name, course_id from instructor, teaches where instructor.ID = teaches.ID and dept_name = 'Biology'; 等价于
select name, course_id from instructor, teaches where (instructor.ID, dept_name) = (teaches.ID, 'Biology');
5. 集合运算
union, intersect, except 并，交，非
5.1 并运算
(select course_id from section where semester = 'Fall' and year = 2009)
union (select course_id from section where semester = 'Fall' and year = 2010);
union操作自动去重，若不想去重应使用union all操作
5.2 交运算
(select course_id from section where semester = 'Fall' and year = 2009)
intersect (select course_id from section where semester = 'Fall' and year = 2010);
intersect操作自动去重复，若不想去重复应使用intersect all操作
5.3 差运算
(select course_id from section where semester = 'Fall' and year = 2009)
except (select course_id from section where semester = 'Fall' and year = 2010);
except为集差操作，即ECE-101在2009年秋天开设4个课程段，在2010年秋天开设2个课程段，则使用except操作显示的关系中不包括ECE-101元组；
若使用except all操作，则存在4-2=2个ECE-101元组。
6. 空值
涉及空值的比较运算结果为unknown
1 < null = unknown
true and unknown = unknown;
false and unknown = false;
true or unknown = true;
false or unknown = unknown;
not unknown = unknown;
7. 聚集函数
平均值：avg
最小值：min
最大值：max
总和：sum
计数：count
7.1 基本聚集
select avg(salary) as avg_salary from instructor where dept_name = 'Comp. Sci.';
select count(distinct ID) from teaches where semester='Spring' and year = 2010;
7.2 分组聚集
select dept_name, avg(salary) as avg_salary from instructor group by dept_name;
第一步：将元组按dept_name分组；
第二步：求平均值
注：要保证出现在select语句中但没有被聚集的属性只能是出现在group by子句中的那些属性。
错误： select dept_name, id, avg(salary) as avg_salary from instructor group by dept_name;
7.3 having子句
select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name
having avg(salary) > 42000;
在instructor关系中选择以dept_name为分组依据，分组平均salary大于42000的元组的dept_name和avg(salary)。
注：任何出现在having子句中但没有被聚焦的属性必须出现在group by 子句中，否则查询就会被当做是错误的。
操作序列：
1. 最先根据from子句来计算一个关系（笛卡尔积）
2. 如果出现where子句，where子句的谓词将应用到from子句的结果关系上
3. 如果出现了group by 子句，满足where谓词的元组通过group by子句形成分组。如果没有group by子句，满足where谓词的整个元组集将被当成一个分组
4. 如果出现having子句，它将应用到每个分组上，不满足having子句谓词的分组将被抛弃
5. select子句利用剩下的分组产生出查询结果中的分组，即每个分组应用聚焦函数来得到单个结果元组。
7.4 对空值和布尔值的聚焦
8. 嵌套子查询
8.1 集合成员资格
连接词in测试元组是否是集合中的成员
select distinct course_id from section where semester = 'Fall' and year = 2009 and course_id in (select course_id from section where semester = 'Spring' and year = 2010);
查找在2009年秋季和2010年春季同时开课的所有课程。
8.2 集合的比较
select name from instructor where salary > some(select salary from instructor where dept_name = 'Biology');
找出满足下面条件的所有教师的姓名，他们的工资至少比Biology系的某一个教师的工资要高。
8.3 空关系测试
select course_id from section as S where semester = 'Fall' and year = 2009 and exists (select * from section as T where semester = 'Spring' and year = 2010 and S.course_id = T.course_id);
查询在2009年秋季和2010年春季同时开课的所有课程名称。
8.4 重复元组的存在性测试
select T.course_id from course as T where unique(select R.course_id from section as R where T.course_id = R.course_id and R.year = 2009);
找出所有在2009年最多开设一次的课程（注：尚未被广泛应用）
等价于：select T.course_id from course as T where 1 >= (select count R.course_id from section as R where T.course_id = R.course_id and R.year = 2009);
8.5 from子句中的子查询
select dept_name from (select dept_name avg(salary) as avg_salary from instructor group by dept_name) where avg_salary > 42000;
8.6 with子句
提供了定义临时关系的方法
with max_budget(value) as
(select max(budget) from department)
select budget
from department, max_budget
where department.budget = max_budget.value;
找出具有最大预算值的系
8.7 标量子查询
只要子查询只返回包含单个属性的单个元组，这样的子查询称为标量子查询。
select dept_name, (select count (*) from instructor where department.dept_name = instructor.dept_name) as num_instructors
from department;
9. 数据库的修改
9.1 删除
delete from r where P;
delete from instructor where dept_name in (select dept_name from department where building = 'Watson');
9.2 插入
insert into course(course_id, title, dept_name, credits)
values('CS-427', 'Databases Systems', 'Comp. Sci.', 4);
insert into instructor select ID, name, dept_name, 18000 from student where dept_name = 'Music' and tot_cred>144;
让music系每个修满144学分的学生称为music系的教师，其工资为i18000美元。
9.3 更新
update instructor set salary = salary*1.05 where salary < 70000; 将instructor内属性salary小于70000的元组的salary更新为原来的1.05倍。
update instructor set salary = case when salary <= 10000 then salary * 1.05 else salary*1.03 end;
update student S set tot_cred = (select sum(credits) from takes natural join course where S.ID = takes.ID and takes.grade <> 'F' and takes.grade is not null);
更新student元组中tot_cred属性值，设为该生成功完成的课程学分总和。
将select sum(credits)替换为 select case when sum(credits) is not null then sum(credits) else 0 end即可解决如果一个学生没有学完任何课程上述更新语句将tot_cred属性值设置为空的问题，此时设置为0.
