03SQL

SQL组成

• 数据定义语言DDL
• 数据操纵语言DML
• 完整性
• 视图定义
• 事务控制
• 嵌入式SQL和动态SQL
• 授权

---

SQL数据定义语言（DDL）允许指定关系的信息
- 每个关系的模式
- 每个属性的取值类型
- 完整性约束
- 每个关系维护的索引集合
- 每个关系的安全性和授权信息
- 每个关系在磁盘上的物理存储结构

---

SQL基本类型

• char(n)：固定长度的字符串，会自动追加空格
• varchar(n)：可变长度的字符串，最大长度为n
• int：整数类型
• smallint：小整数类型
• numeric(p,d)：定点数，p位数字（加上一个符号位），d位小数部分
• real, double precision：浮点数
• float(n)：精度至少位n的浮点数

---

创建表

create table r (
A1 D1, 
A2 D2, 
..., 
An Dn, 
(integrity-constraint1),
..., 
(integrity-constraintk));

• 完整性约束
• not null：不允许空值
• primary key (A1, ..., An)：主码，自动不允许空值
• foreign key (A1, ..., An) references r：参照r的外码

---

删除表

删除整个表

drop table r;

清空表

delete from r;

• 配合where可以删除指定元组

---

插入元组

insert into r values (a1, a2, … , a_n);

insert into r on (A1, A2, … , An) values (a1, a2, … , an);

• 如果插入的元组中只给出了部分属性的值，其余属性将赋空值

---

更改属性

update r
set a1 = b1, … ,an = bn
where P

在set中使用case … when可以执行多条更新语句

case
when p1 then result1
when p1 then result1
…
when pn then resultn
end

---

增加属性

alter table r add A;

---

删除属性

alter table r drop A;

---

查询

select A1, A2, … , Am
from r1, r2, …, rm
where P;

• 大小写不敏感
• 默认不去重
• 使用select distinct显式指明去重
• select *会返回所有属性
• select子句允许带+, -, *, / 运算符
• from子句会对所有的关系进行笛卡尔积
• where子句可以使用逻辑连词and, or, not，比较运算符<, >, <=, >=, =, <>

---

SQL查询的一种理解
1. 为from子句中列出的关系产生笛卡尔积
2. 在1的结果上应用where子句中指定的谓词
3. 对2的结果中的每个元组，输出select子句中指定的属性
实际会通过（尽可能）只产生满足where子句谓词的笛卡尔积的元素进行优化执行

---

自然连接（natural join）

select A1, A2, … , Am
from r1 natural join r2 natural join … natural join rm
where P;

• 只考虑在多个关系模式中都出现的属性上取值相同的元组对
• 危险：关系模式中相同名称的属性可能不相关，当不相等的时候不会被选择
• 解决方法
  
  • 使用from r1 join r1 where r1.A1 = r2.A1 代替 from r1 natural join r1
  • 使用from r1 join r2 using A1 代替 from r1 natural join r2

---

更名

select A1, A2, … , Am as B1, B2, … , Bm
from r1, r2, …, rm
where P;

select A1, A2, … , Am
from r1 as s1, r2 as s2, …, rn as sn
where P;

• as 可以省略
• 更名原因
• 把长的关系名替换成短的
• 为了适用于需要比较同一个关系中的元组的情况
• 被用来重命名关系的标识符被叫做相关名称、表别名、相关变量、元组变量

---

字符串运算

select name
from instructor
where name like '%ab\%cd%' escape '\';

• 使用一对单引号’表示字符串，用两个”表示字符串中的单引号
• 字符串的相等运算大小写敏感
• like的模式匹配
• %——匹配任意子串
• _——匹配任意一个字符
• escape转义字符

---

排列元组显示次序

select *
from instructor 
order by salary desc, name asc;

• 默认升序（asc），使用desc可以表示降序

---

where子句谓词

select name 
from instructor
where salary between 90000 and 100000;

select name, course_id
from instructor, teaches
where (instructor.ID, dept_name) = (teaches.ID, 'Biology');

---

集合运算

并

(select course_id 
from section 
where sem = ‘Fall’ and year = 2009) 
union
(select course_id 
from section 
where sem = ‘Spring’ and year = 2010);

• 自动去除重复，保留重复使用union all代替union

交

(select course_id 
from section 
where sem = ‘Fall’ and year = 2009) 
intersect
(select course_id 
from section 
where sem = ‘Spring’ and year = 2010);

• 自动去除重复，保留重复使用intersect all代替intersect

差

(select course_id 
from section 
where sem = ‘Fall’ and year = 2009) 
except
(select course_id 
from section 
where sem = ‘Spring’ and year = 2010);

• 自动去除重复，保留重复使用except all代替except

---

空值

• 算术运算：算术表达式的任一输入为空（null），该算术表达式（+, -, *, /）的结果为空（null）
• 比较运算：涉及空值的任何比较运算结果视为unknown
• 布尔运算：顺序为
  
  • true-unknown-false
  • true and unknown = unknown
  • unknown and unknown = unknown
  • false and unknown = false
  • true or unknown = true
  • unknown or unknown = unknown
  • false or unknown = unknown
  • not unknown = unknown
• 判断空：A is null当A为null的时候为真，A is unknown当A为unknown的时候为真
• 比较元组对应属性值都为非空且相等或者都为空，认为属性值是相同的
• 谓词中null = null返回unknown

---

聚集函数（Aggregate Functions）

• 聚集函数自动忽略空值，如果没有非空元素，除了count返回0，其它返回null

求和

select sum (salary)
from instructor
where dept_name='Comp. Sci.';

平均数

select avg (salary)
from instructor
where dept_name='Comp. Sci.';

计数

select count (*)
from course;

分组

select dept_name, avg (salary) 
from instructor
group by dept_name;

• 出现在select语句中但没有被聚集的属性必须出现在group by子句

having子句

select dept_name, avg (salary) 
from instructor
group by dept_name
having avg (salary) > 42000;

• 出现在having子句中但没有被聚集的属性必须出现在group by子句

嵌套子查询

• 嵌套在另一个查询中的select-from-where表达式
• where语句中的嵌套子查询
• 来自外层查询的相关名称可以用在where子句的子查询中，这个子查询叫做相关子查询
• 成员资格

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);

集合比较

select name
from instructor
where salary > some (select salary
                     from instructor
                     where dept_name = 'Biology');

• >some表示至少比一个大，也有<some, <=some, >=some, =some, <>some。其中=some等价于in

select name
from instructor
where salary > all (select salary
                    from instructor
                    where dept_name = 'Biology');

• >all表示比所有都大，也有<all, <=all, >=all, =all, <>all。其中<>all等价于not in

空关系测试

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);

• exists r等价于r不是空集

select distinct S.ID, S.name
from student as S
where not exists ((select course_id
                   from course
                   where dept_name = 'Biology') 
                  except
                  (select T.course_id 
                  from takes as T 
                  where S.ID = T.ID));

• not exists r等价于r是空集
• not exists(B except A)等价于B是A的子集

重复测试

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);

• 当查询的结果中没有重复的元组，unique返回true

from语句中的嵌套子查询

select dept_name, avg_salary
from (select dept_name, avg (salary) as avg_salary
      from instructor
      group by dept_name) 
      where avg_salary > 42000;

• 可以代替having子句
• from子句中使用关键词lateral作为前缀可以访问from子句中在它前面的表或者子查询的属性

with子句

with max_budget (value) as (select max(budget) 
                            from department) 
select budget
from department, max_budget
where department.budget = max_budget.value;

• with子句定义的是临时关系，只对包含with子句的查询有效

标量子查询

• 只返回单个属性的子查询出现在返回单个表达式能够出现的地方，这样的蛋哥子查询叫做标量子查询

select dept_name, (select count(*)
                   from instructor
                   where department.dept_name = instructor.dept_name) as num_instructors
from department;

• 可以出现在select, where, having子句中