数据库系统概念学习 03. SQL概述

之前在关系模型概述中已经从数据模型和关系代数的角度解释了关系数据库模型，然而为了使得用户可以使用关系数据库，就必须解决两个问题：首先，定义提供给用户获取和更改数据库信息的方式，其次，采取措施对数据库的约束和权限进行保护。作为最为常用的查询语言(query language)，结构化查询语言(Structured Query Language, SQL)是IBM在20世纪70年代研发的"System R Project"的一部分，至今已经飞速发展，风靡世界，并由ANSI和ISO制定了很多标准，其中SQL-92标准是工业界的商业数据库支持最好的标准（当然，也并没有支持全部的特点）。SQL主要分为一下几个部分：数据定义语言(Data-definition Language, DDL)，数据操作语言(Data-manipulation Language, DML)，完整性约束(Integrity)，视图定义(View Definition)，事务控制(Transaction Control)，编程接口(Embedded SQL)，权限(Authorization)。

1. SQL的数据定义语言(DDL)包含表的以下信息：每个表的schema、表关联属性值的取值范围、完整性约束，对每个表索引的维护、每个表的安全和权限信息以及在磁盘上每个表的物理存储结构等。具体表现为5种操作，即create，insert, delete，drop和alter。
(1). 建立数据表

create table instructor(
ID         varchar(5),
name       varchar(20) not null,
dept_name  varchar(20) ,
salary     numeric(8,2),
primary key(ID),
foreign key(dept_name) references department);

create table department(
dept_name  varchar(20),
building   varchar(15),
budget     numeric(12,2),
primary key(dept_name));

基本类型包括char(n), varchar(n), int, smallint, numeric(p,q), real, double, float(n)，其中char(n)和varchar(n)都是固定长度字符变量，当实际长度小于定义长度时，char可能会使用空格进行自动补全（依赖于数据库具体实现），而varchar并不会，所以一般情况下推荐使用varchar；此外，primary和foreign定义了主键约束和外键约束，保证约束的属性值不能为空。建立表格后，表内记录为空，通过insert进行记录插入：

insert into instructor values(10211, 'Smith', 'Biology', 660000);

(2). 删除数据表
使用drop删除表格的所有记录，包括schema:

drop table instructor;

使用delete删除所有记录，但是并不删除包括schema:

delete from instructor;

(3). 修改数据表
使用命令alter修改已建立的数据表，其中增添的属性所对应的值为null

alter table instructor add info varchar(50);
alter table instructor drop info;

2. SQL的数据操作语言(DML)提供了对表进行查询、插入、删除、修改的能力，SQL查询的基本结构包括3个select-from-where子句，通过from子句确定操作的表格，由where子句通过关系代数确定筛选条件的tuples，由select来确定attributes的结果，其中一般使用distinct和all关键字来显式地表明是否保存重复项的结果。以下操作和关系代数语义相同：
　　\(\Pi_{(A1,A2,...,An)}{\sigma_{P}{(R1,R2,...,Rm)}}\)

select A1, A2, ..., An
from   R1, R2, ..., Rm
where  P

操作单个表格较为简单，重点研究一下操作多个表格的实例。如列出所有学院里教员薪水与工作地点名称：

select salary, building
from instructor, department
where instructor.dept_name = building.dept_name;

在该操作中，首先根据两个表格建立笛卡尔积(Cartesian Product)，再根据条件instructor.dept_name = building.dept_name进行筛选，最后列出筛选结果的salary和building属性。为了效率和简便，同样的查询需求可以通过natural join来实现:

select salary, building
from instructor natural join department;

因为natural join相比较建立笛卡尔积而言，仅仅选取相同attributes对应的值都相同的tuples，很大程度上提高了效率，并且可以级联。但是，如果要指定关心的attributes则可以使用join using来规范化：

select relationA, relationB
from (attr1 natural join attr2) join relationC using atrr3;

除此之外，SQL还提供其它操作，为用户描述操作数据库语句提供方便的工具。
(1). 重命名操作as
找出所有比计算机专业的一些教员工资高的教员：

select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name='ComputerSci';

这里的as有种按照类型实例化对象的意味 😃
(2). 字符串操作like
其中%表示任何子串，_表示单个字符，如找出所有名字可能包括'dar'的教员：

select name
from instructor
where name like '%dar%';

匹配模式是大小写敏感的；此外，为了查询特殊字符需要使用escape关键字，如下所示(假设instructor里面包含info属性)，查询info中以100%开头:

select ID
from instructor
where info like '100\%' escape '\';

(3). 排序操作order by
这个很好理解，在查询输出中可以指定升降序:

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

除此之外，还有between和not between等操作，概念简单，容易理解，此处略去；下面介绍SQL的集合操作(union intersect 和 except分别对应'或'、‘与’和‘非’的逻辑)。

• union 操作，例如找出所有在2009年秋天或者2010年春天教授的课程：

(select course_id
from section
where semester="Fall" and year=2009)
union
(select course_id
from section
where semester="Spring" and year=2010);

• intersect 操作，例如找出所有在2009年秋天并且2010年春天教授的课程：

(select course_id
from section
where semester="Fall" and year=2009)
intersect
(select course_id
from section
where semester="Spring" and year=2010);

• except 操作，例如找出所有在2009年秋天而不是2010年春天教授的课程：

(select course_id
from section
where semester="Fall" and year=2009)
except
(select course_id
from section
where semester="Spring" and year=2010);

**3. **NULL Values
以null为运算成员的算术运算都将结果认为是null；以null为运算成员的比较运算<、>、=、<>运算结果为unknown，但是('A',null)和('A',null)被认为是相等的；而以null为运算成员的逻辑运算却为之定义了第三种真值unknown，（在SQL-99标准引入）相对应的真值表如下所示：

OPR	InputA	InputB	Output
and	unknown	false	false
and	unknown	true	unknown
or	unknown	false	unknown
or	unknown	true	true
not	unknown		unknown

4. 聚合函数
聚合函数总共有5种，分别是avg,min,max,sum,count，对于avg和sum的输入必须是数，其它的输入可以是字符串等，运算结果通常保留重复项（关键词all是默认的），例如：

select avg(salary) as avg_salary
from instructor
where dept_name='ComputerSCI';

select count(distinct ID)
from teaches
where semester='Spring' and year=2010;

通常聚合函数配合聚合子句进行使用：

• group by
  通常使用group by对某些属性进行汇总。在SQL语句中使用group by进行聚合，要注意这么个坑：在select子句中的所有项，要么进行了聚合运算，要么就得出现在group by语句中：

select dept_name, count(distinct ID) as instr_count
from instructor natural join teaches
where semester='Spring' and year = 2010
group by dept_name;

• having
  having子句通常是作为group by子句结果进一步过滤的条件，因此group子句的约束同样适用于having子句：

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

此外，如果聚合函数遇到了null，SQL标准要求聚合函数（count除外）在运算时忽略null值；但是当运算集合是空集时，count(*)返回值0，而其它的聚合函数返回null；在更加复杂的SQL聚合语句中，各种实现的数据库会有轻微不同。

5. 嵌入子查询
由于SQL查询结果还是数据表，所以SQL语句是可以嵌套的，由in或者not in等关键字进行连接。
注意：in等同于=some；而not in等同于<>all*。
举个例子，找出平均工资最高的单位：

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

SQL语句用exists和not exists在嵌入子查询语句中表征空集和非空集，也可以用来表达包含关系：“关系A包含关系B”等价于“not exists(B except A)”。可以用于嵌入的还有:

• unique表示“最多一次”，not unique*表示“最少两次”
• lateral可以使得先执行的子句访问后执行的子句成员，SQL-03标准开始支持的，但是只有IBM DB2等很少量的数据库实现。例子:

select name, salary, avg_salary
from instructor I1, lateral(select avg(salary) as avg_salary
                          from instuctor I2
                          where I2.dept_name = I1,dept_name);

• with子句用来临时存放表，但是这些表只能在定义with子句的地方使用，如：

with dept_total(dept_name, value) as
               (select dept_name, sum(salary)
                from instructor
                group by department),
     dept_total_avg(value) as
               (select avg(value)
                from dept_total)
select dept_name
from dept_total, dept_total_avg
where dept_total.value >= dept_total_avg.value;

**6. **变更数据库
SQL提供语句来删除、增加和修改数据库记录。

• 删除 delete

delete from instructor
where dept_name in (select dept_name
                        from department
                        where building = 'Watson');

• 插入 insert

insert into instructor
  select ID, name, dept_name, 18000
  from student
  where dept_name='Music' and tot_cred > 144; 

• 变更 update

update instructor
set salary = salary * 1.03
where salary < 100000;

或者，更灵活地，

update instructor
set salary=case
                 when salary <= 10000 then salary * 1.03
                 else salary * 1.02
               end

本次总结算是对SQL的概述，SQL作为一种标准很有更多牛逼闪亮的特征，明天再续 😃