《数据库系统概念》学习笔记之三

SQL

SQL 语言概述

• 数据定义语言（Data-Definition Language, DDL）:提供定义关系模式，删除关系以及修改关系模式的命令
• 数据操作语言（Data-Manipulation Language,DML)：提供从数据库中查询信息，以及在数据库中插入元组、删除元组、修改元组的能力
• 完整性（integrity）：定义完整性约束的命令，保存在数据库中的数据必须满足所定义的完整性约束。
• 视图定义（view definition）：定义视图的命令。
• 事务控制（transation control）：定义事务开始和结束的命令。
• 嵌入式SQL和动态SQL（embedded SQL and dynamic SQL）:嵌入式和动态SQL定义SQL语句如何嵌入到通用编程语言
• 授权（authorization）：定义对关系和视图的访问权限的命令。

---

SQL 数据定义

基本模式定义

1. create table

用来定义数据关系
- primary key(Aj1, Aj2, …, Ajn):表示关系主码，主码属性必须非空且唯一
- foreign key(Ak1, Ak2, …, Akn) reference s :表示关系中任意元组在属性（Ak1,Ak2,…,Akn）上的取值必须对应关系s中元组在主码属性上的值。
- not null:一个属性上的not null 约束表明在该属性上不允许空

create table r
        (A1 D1,
        A2 D2,
        ...
        An Dn,
        <完整性约束1>,
        ...
        <完整性约束k>
        )；

2. insert into

可以用insert命令将数据加载到关系中。

insert into r 
    values ( 101, 'Smith', 'Blog', 666);

3. delete from

删除元组

delete from r;

4. drop table

删除元组并且删除模式

drop table r;

5. alter table

修改关系

alter table r add A D;
alter table r drop A;

---

SQL查询的基本结构

SQL查询的基本结构由三个子句组成：select、from和where。

单关系查询

select name from instructor;                        -- 查询的内容放在select子句中

select distinct dept_name from instructor;          -- 去除重复的数据增加distinct关键字

select all dept_name from instructor;               -- all显式指明不去除重复

select ID, name, dept_name, salary*1.1 from instructor;
-- select 子句可以带含有+-*/运算符的算术表达式，运算对象可以是常数或者元组的属性

select name from instructor where dep_name = 'math' and salary > 7000;
-- where子句允许只选出那些在from子句的结果关系中满足特定谓词的元组
-- 使用逻辑连词and,or,not 和逻辑运算符 >, <, >=, <=, = 和 <>

---

多关系查询

多关系的通用形式为：
- select 子句用于列出查询结果中所需要的属性
- from 子句是一个查询求值中需要访问的关系列表
- where 子句是一个作用于from子句中关系的属性上的谓词

select A1, A2， ...,An
from r1, r2, ..., rm
where P;

子句的运算顺序为：

举例：

select name, instructor, dep_name,building
from instructor,department
where instructor.dep_name = department.dep_name;
--多关系查询需要把关系列在from子句中
--相同属性需要指明前缀（关系.属性）

---

自然连接(natural join)

运算作用于两个关系，并产生一个关系作为结果。它将第一个关系的每个元组与第二个关系的所有元组都进行连接。自然连接只考虑那些在两个关系模式中都出现在属性上取值相同的元组对。

select A1, A2, ..., An
from r1 natural join r2 natural join r3 ...natural join rm
where P;

select A1, A2, ..., An
from (r1 natural join r2) join r3 using (column1, column2) 

---

附加的基本运算

1.更名运算

select T.name S.course_id
from instructor as T, teachers as S
where T.ID = S.ID;

2.字符串运算

select name from student where name like 'ab\%' escape '\'; 

3.select子句中的属性说明

select instructor.* from instructor, teachers;
--*表示所有的属性

4.排列元组的显示次序

select name from instructor order by salary desc, name asc;
-- des表示降序，asc表示升序

5.where 子句谓词

--between...and...
select * 
from instructor,teachers 
where (instructor.ID, dept_name)=(teachers.ID, 'Biology');

集合运算

--union 并运算
--intersect 交运算
--except 差运算
(select course_id
from section
where semester ='Fall' and year=2009)
union
(select course_id 
from section
where semester=‘Spring‘ and year=2010);

空值

and: true and unknow的结果是unknown, false and unknown的结果是unknown, unknown and unknown的结果是unknown.
or: true or unknow的结果是true, false or unknown的结果是unknown, unknown and unknown的结果是unknown.
not: not unknown 是unknown.

聚合函数

基本聚合

• avg
• min
• max
• sum
• count

select count (distinct ID)
from teachers
where semester ='Spring';

分组聚合

--select语句中但没有被聚集的属性只能是出现在group by
select avg (salary)
from instructor
group by dept_name;

having 子句

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

对空值和布尔值的聚集

除了count，其他聚合函数都忽略空值。

---

嵌套子查询

集合成员资格

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

select distinct name 
from instructor
where name not in ('Mozart', 'Einstein');

select count(distinct ID)
from takes
where (course_id, sec_id, semester, year) in (select course_id, sec_id, semester, year
from teachers
where teachers.ID=10101);

集合成员资格

SQL允许测试元组在关系中的成员资格。连接词in测试元组是否集合中的成员。

集合的比较

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

select dept_name
from instructor
group by dept_name
having avg(salary) >= all (select avg(salary)
from instructor
group by dept_name);

空关系测试

关键字：exsit, not exsit, except

重复元组存在性测试

关键字：unique

from子句中的子查询

with子句

标量子查询

---

数据库的修改

删除

delete from r where P;

插入

insert into course values ();

更新

update instructor set salary=salary*1.05;

update a
set b=case
    when pred1 then result1
    when pred2 then result2
    when pred3 then result3
    else resultn
end