第三章 关系数据库标准语言SQL

3.1SQL概述

SQL的特点

1. 综合统一（包含数据库中所有的操作）

2. 高度非过程化

3. 面向集合的操作方式

4. 以同一种语法结构提供两种使用方法（嵌入式SQL和交互式查询）

5. 语言简洁，易学易用

SQL的功能

1. 数据定义DDLCREATE, DROP, ALTER

2. 数据查询DQLSELECT

3. 数据操纵DMLINSERT, UPDATE, DELETE

4. 数据控制DCLGRANT, REVOKE

5. 事务控制DTLCOMMIT, ROLLBACK

3.2数据定义

基本类型

• char(n): 固定长度的字符串

• varchar(n): 可变长度的字符串,至多长度为n

• int: (or integer) 整型

• smallint: 小整数类型

• numeric(p,d): p位数字（不含符号位），其中小数点右边有d位；eg：numeric（3，1） 精确储存44.5

• decimal(n):小数位数为n的浮点数

• real,double precision: 浮点数和双精度浮点数

• float(n): 精度至少n位的浮点数

常用的列级完整性约束

primary key(D1,D2,...Dn)： 主键，非空且唯一

foreign key(D1,D2,...,Dn) references Table_k： 外键

not null: 表示非空，eg: name varchar(20) not null.

关系修改

create table r (
    <type> attribute1 <列级完整性约束>, 
    <type> attribute2 <列级完整性约束>,
    ...
    <表级完整性约束>
)；
​
delete from student;    // 删除student下所有的tuples
​
drop table r;   // 删除table r 包括其中所有tubles
​
delete from r;  // 删除table r 中所有的元素，但是保留table r
​
alter table r add A D;  // 在table r 中增加一条 attribute  A , 定义该 attribute 的类型为 D
​
alter table r drop A;   // 删除table r 中的 attribute A
​
alter table r modify A D; // 将table r中attribute A的类型改为D

3.3查询

3.3.1单表查询

select A1,A2,...An  //selext 表示选择的attribute
from r1,r2,...rn    //from 需要访问的关系列表
where P             //where 加以限定的关系谓词

• as

给表或者属性一个临时的名字

select name as S
from instructor as T

• string 字符串匹配

  • 用[NOT]LIKE运算符

  • LIKE可以用=替代，NOT LIKE不能用!=替代

where building like '%Waston_'
// %: 匹配任意子串、
// _: 匹配任意一个字符
// 使用\% 来表示% 字符 ,ESCAPE转义字符

• order by 查询结果排序

  • 默认为升序，desc: 降序，asc: 升序

  • 空值默认为无穷大

order by name;
order by salary desc,name asc; // 先用salary 降序排列，再用name 升序排列

• where 查询满足条件的元组

where salary between 90000 and 100000;
where salary <> 100000 and salary >= 90000; // <>的含义是不等于，类似的有!>,!<

• is null , is not null 空值查询

注意没有= NULL的写法

where salary is null;

3.3.2 分组查询

常用集函数

• avg 求平均值

• min

• max

• sum

• count 计数

select avg(salary) as avg_salary
from instructor
where dept_name = 'Comp. Sci';
​
select count (distinct ID)
from teaches
where semester = 'Spring' and year = 2009;
​
select count (*)
from course;

注意：不能使用select count (distinct *); 原因：系统不知道用哪一个attribute 判断distinct

除了 count（*），其他的聚集函数均忽略 null

分组聚集

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;

注意：使用GRUOP BY后SELECT所选择的属性，只能出现分组属性或者集函数的结果

having

类似于对 group 的 where

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

• 判定顺序： 先where再group，先select，再gruop

• where作用于基表或者视图，having作用于元组

3.3.3连接查询

连接的种类

1. 笛卡尔连接A join B

2. 自然连接A natural join B: 在等值连接的基础上删除重复属性

3. 外连接 A outer join B

   • 左外连接 LEFT OUTER JOIN ：保留左边的悬浮元组

   • 右外连接RIGHT OUTER JOIN

连接操作的执行过程

1. 嵌套循环法（暴力枚举）

2. 排序合并法（常用于等值连接）

3. 索引连接

3.3.4集合查询

• in, not in<值表>

• union 并运算

• intersect 交运算

• expect(minus) 差运算

集合运算默认是去掉重复的，如果想保留重复，可以使用all

3.3.5嵌套子查询

• in, not in

  • 用于嵌套子查询

  • 用于枚举集合 where name not in ('Mozaet','Einstein')

• ALL ,ANY

用于集合的比较

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

= all 和 in: = all 是和每一个都相等，一般为假命题

<> all 和 not in：二者等价

• exist , not exist

用于判断查询的表是否为空，若为空，EXIST返回false

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

上面查询语句的意思：

找出选修了Biology系开设的所有课程的学生

• 其他操作

  • from 子句中的子查询（即将from 的对象用select 描述）

  • with 子句（在select前，使用with创建一个临时表）

3.3.6重难点

• sql 语句无法实现判断全部有某性质的功能，只能通过 count 或者 exist 语句进行描述

• 用EXIST/NOT EXIST实现全称量词

• 用EXIST/NOT EXIST实现逻辑蕴含 p\rightarrow q = \lnot p \lor q

3.4数据更新

delete 删除

谓词形式类似于select

delete from instructor
where salary < (select avg(salary)
				from instructor);

Insert 插入

插入的元组：写在语句中，或者使用select 语句筛出一个集合来进行插入(先执行select 再执行insert)

如何清楚插入元组的顺序：指定属性列 or 按定义顺序插入（未指定的列取空值）

insert into course(course_id,title,dept_name,credits)
	values('CS-437','Database System','Comp.Sci.',4);

insert into course	// 此处将使用默认的插入格式，和定义的顺序一致
	values('CS-437','Database System','Comp.Sci.',4);
	
insert into instructor	
	select ID,name,dept_name,18000
	from student
	where dept_name = 'Music' and tot_cred > 144;

updata 更新

• 使用set 语句进行更新处理

• 其他的约束与select 一致

update instructor
set salary = salary * 1.06
where salary < 70000;

3.5视图

• 对视图的操作类似于表，操作语法都是一致的

• 视图，是一个虚表，只存放视图的定义，不会出现数据冗余

• DBMS实现视图查询的方法

  • 实体化视图：生成一个临时表

  • 视图消解法：转化为等价的查询语句

• 视图的作用

  • 视图能在一定程度上提供数据的逻辑独立性

  • 视图能够对机密数据提供安全保护

• 视图的受限更新

  1. 允许对包含主码的行列子集视图进行更新

  2. 不允许对三个及以上基本表导出的视图更新

  3. 视图的字段来自字段表达式、常数，允许INSERT,UPDATE，不允许DELETE

  4. 视图的字段来自集函数，不允许更新

  5. 视图定义有GROUP BY，不允许更新

  6. 视图定义有DISTINCT，不允许更新

  7. 视图定义有嵌套查询，且是相关子查询，不允许更新

  8. 被定义在不可更新视图上的视图，不允许更新

3.6数据控制

• 数据控制也叫做数据保护，包含以下几个方面

  • 安全性控制

  • 完整性控制

  • 并发控制

  • 恢复

• 数据库的完整性是指数据库中数据的正确性和相容性

• 并发控制是为了保证数据库的一致性

• DBMS会把授权的结果存入数据字典

• 授权GRANT与收回权限REVOKE

  GRANT <权限> ON <对象类型><对象名>  TO <用户>
  REVOKE <权限> ON <对象类型><对象名> FROM <用户>

  • 一个用户可被多个用户授权，对某一个表的权限可来自多个用户

3.7嵌入式SQL

• 引入嵌入式SQL的必要性

  • SQL语言是非过程性语言

  • 事务处理应用需要高级语言

• 嵌入式SQL的一般形式 ： 前缀 EXEC SQL

• DBMS处理宿主型数据库语言SQL的方法

  • 预编译

  • 修改和扩充主语言，使之能支持SQL语句

• 嵌入式SQL语言与主语言之间的通信：游标

• 主语言一般是面向记录的，SQL是面向集合的

游标

• 解决集合性操作语言与过程性操作语言的不匹配

• 游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果

• 使用游标的步骤

  1. 说明游标DECLARE

  2. 打开游标EXEC SQL OPEN

  3. 移动游标指针，然后取当前记录NEXT|PRIOR|FIRST|LAST

  4. 关闭游标EXEC SQL CLOSE