数据库学习三 SQL 语言

SQL 语言的分类

DDL (Data Definition Language, 数据定义语言)

在数据库系统中，每一个数据库、数据库中的表、视图和索引等都是数据库的对象，要建立和删除一个数据库对象，都可以通过 SQL 语言来完成。DDL 包括 CREATE、ALTER 和 DROP 等

DML (Data Manipulation Language, 数据操纵语言)

DML 是指用来添加、修改和删除数据库中数据的语句，包括 INSERT、DELETE 和 UPDATE 等

DQL (Data Query Language, 数据查询语言)

查询是数据库的基本功能，查询操作通过 SQL 数据查询语言来实现。例如，用 SELECT 查询表中的内容

DCL (Data Control Language, 数据控制语言)

DCL 包括数据库对象的权限管理和事务管理等

(一)、DDL 数据定义语言

1. 建立数据库

        
      
create database <数据库名>

2. 建立基本表

        
      
create table <表名>
(
	<列名> <数据类型> [列级完整性约束条件],
    [···,]
    [表级完整性约束条件,]
    [···]
)

<1> 数据类型

INT / INTEGER : 整数
FLOAT(n) : 浮点数，精度至少为 n 位数字
NUMERIC(p, d) / DECIMAL(p, d) / DEC(p, d) : 定点数，由 p 位数字 (不包括符号、小数点) 组成，小数点后面有 d 位数字
CHAR(n) : 长度为 n 的定长字符串
DATETIME : 日期时间型

        
      
-- 例如
create table test
(
	f1 char(10),
    f2 int
)

<2> 列级完整性约束

not null : 不为空
unique : 唯一
not null unique : 不为空且唯一
default : 定义一个默认值

        
      
-- 例如
create table test
(
	f1 int not null, 
    -- 该列不能为空
    
    f2 int unique, 
    -- 该列值可以为空，但每行应不一样
    
    f3 int not null unique, 
    -- 该列不为空且每行不一样
    
    f4 char(5) default 'nashi' 
    -- 若新增行未赋值，此列默认赋值 'nashi'
)

<3> 表级完整性约束

名称	子句	对应关系完整性
主键子句	PRIMARY KEY	实体完整性
检查子句	CHECK	用户自定义完整性
外键子句	FOREIGN KEY	参照完整性

        
      
-- 例如
create table student
(
	sid int,
    primary key(sid) -- not null unique
)
-- 或者
create table student
(
	sid int primary key
)

create table course
(
	cid int,
    sid int,
    score int,
    check(score >= 0 and score <= 100),
    foreign key(sid) references student(sid)
)

对于完整性的检查

断言 (一般不主张用)

        
      
CREATE ASSERTION <断言名> <check子句>  
-- 其中check子句与where子句的表达式类似
-- 删除断言名称
DROP ASSERTION <断言名>;

触发器

当对一个表增、删、改的时候，对触发器里面的条件进行检查，如果成立，则执行触发器里面的动作，否则不执行里面的动作。

        
      
CREATE TRIGGER <触发器名>           
-- 每当触发事件发生时，该触发器被激活
{BEFORE|AFTER} <触发事件> ON <表名> 
-- 指明触发器激活的时间是在执行触发事件前或后
REFERERCING NEW|OLD ROW AS <变量>  
-- REFERENCING指出引用的变量
FOR EACH{ROW|STATEMENT}            
-- 定义触发器的类型，指明动作体执行的频率
[WHEN <触发事件>] <触发动作体>      
-- 仅当触发条件为真时才执行出发动作体
 
-- 删除触发器
DROP TRIGGER <触发器名> ON <表名>

3. 修改基本表结构

增加一个新列

        
alter table <表名> add <列名> <类型>

-- 例如
alter table test add f3 datetime

修改一个列

        
      
alter table <表名> alter column <列名> <新类型>
-- MySQL
-- alter table <表名> modify <列名> <新类型>

-- 例如
alter table test alter column f3 float
-- alter table test modify f3 float

删除一个列

        
      
alter table <表名> drop column <列名>
-- MySQL
-- alter table <表名> drop <列名>

-- 例如
alter table test drop column f3
-- alter table test drop f3

4. 删除基本表

        
drop table <表名>

-- 例如
drop table test

(二)、DML 数据操纵语言

以下内容的例子基于这几张表

        
      
-- 注：为方便理解使用中文，在实际开发中，请尽量不要使用中文
-- 学生表
create table student
(
	学号 char(5) not null unique,
    姓名 char(8),
    性别 char(2),
    出生日期 datetime,
    班号 char(5)
)
-- 成绩表
create table score
(
	学号 char(5),
    课程号 char(5),
    分数 int
)
-- 教师表
create table teacher
(
	教师编号 char(5) not null unique,
    姓名 char(8),
    性别 char(2),
    出生日期 datetime,
    职称 char(6),
    系别 char(10)
)
-- 课程表
create table course
(
	课程号 char(5) not null unique,
    课程名 char(20),
    任课教师编号 char(5)
)

1. INSERT 插入语句

向 SQL 的基本表中插入数据有两种方式：直接插入元组值或插入一个查询的结果值

直接插入元组值

        
      
insert into 表名[(列名序列)] values (元组值)[, ···]
-- 或者
insert into 表名[(列名序列)]
(table (元组值)[, (元组值), ···])

-- 例如
insert into student
values 
('108', '张三', '男', '2000-01-01', '09033'),
('109', '李四', '男', '2000-05-05', '09034')
-- 插入指定列
insert into student(学号，姓名，性别，出生日期)
values (110, '王五', '男', '2000-04-18')

插入一个查询的结果值

        
insert into 表名[(列名序列)] select <查询语句>

-- 例如
insert into student
select 111, '赵六', '男', '2000-03-19', '09035'

2. DELETE 删除语句

        
      
delete from <表名> [where <条件表达式>]

-- 例如
delete from student -- 删除整个表的所有内容
-- 删除学号为 111 的元组
delete from student
where 学号 = 111

3. UPDATE 修改语句

        
      
update <表名>
set <列名> = <值表达式>[, ···]
[where <条件表达式>]

-- 例如
update student
set 班号 = '09058'
where 学号 = 110
-- 或者
update student
set 班号 = '09057', 性别 = '女'
where 学号 = 110 and 班号 = '09058'

(三)、DQL 数据查询语言

SQL 数据查询功能是通过 SELECT 语句实现，完整语法如下

        
      
select <目标表的列名或列表达式序列>
from <关系表序列>
[where <行条件表达式>]
[group by <列名序列>]
[having <组条件表达式>]
[order by <列名> [asc|desc]]

1. 投影查询

使用 SELECT 命令可以选择查询表中的任意列，当为多个列时，中间使用逗号 , 分隔。当 FROM 子句中的关系表不止一个时，每个表的表名都要写出

        
      
-- 例如
select 1 + 1
select 学号 * 100 from student
-- 查询学生学号与分数
select student.学号, score.分数
from student, score
-- 查询所有列
select *
from student

使用 DISTINCT 可以去除重复行

        
      
-- 去除重复行，查询被选修的课程
select distinct 课程号
from score

使用 AS 可为查询结果自定义列名

        
      
-- 例如
select 学号 as 'sno'
from student

2. 选择查询

选择查询就是指定查询条件，只从源表提取或显示满足该查询条件的记录

选择查询通常使用下列几类运算：

运算符	代码	描述
集合成员运算符	IN NOT IN	在集合中不在集合中
字符串匹配运算符	LIKE	与 `_` 和 `%` 进行单个或多个字符匹配
空值比较运算符	IS NULL IS NOT NULL	为空不能为空
算数运算符	> >= «br /><= = <>	大于大于等于小于小于等于等于不等于
逻辑运算符	AND OR NOT	与或非

<1> 条件表达式的比较运算

        
      
--- 查询分数在 60-80 之间的记录
select *
from score
where 分数 between 60 and 80
-- 使用 between 指包含 60 与 80，即 [60, 80]
-- 等价于 where 分数 >= 60 and 分数 <= 80

<2> 字符串的匹配运算

        
      
where [not] like '匹配串' [escape '匹配串']

其中 '匹配串' 可以是完整字符串，也可以包含通配符 % 和 _

% : 代表任意长度的字符
_ : 代表任意单个字符

<3> 集合的比较运算

        
      
-- 查询成绩为 85、86 或 88 的记录
select *
from score
where 分数 in (85, 86, 88)

<4> 逻辑组合运算

        
      
-- 查询 09031 班的所有女学生
select *
from student
where 班号 = '09031' and 性别 = '女'

3. 排序查询

通过使用 ORDER BY 子句可以对查询结果进行排序

        
      
-- 升序 asc (默认，可省略)
select 学号, 姓名
from student
order by 学号
-- 降序 desc
select 学号, 姓名
from student
order by 学号 desc

也可以指定多个列排序，将会先排序第一个，然后排序第二个

        
      
-- 以课程号升序，分数降序检索结果
select *
from score
order by 课程号 asc, 分数 desc

ORDER BY 子句必须放在查询的最后！

4. 聚合函数

聚合函数实现数据统计等功能，用于对一组值进行计算并返回单一的值

通常与 group by 子句一起使用，常用的聚合函数有：

函数名	描述
AVG	平均值
COUNT	计算指定表达式中选择的项数 COUNT(*) 统计查询输出的行数
MIN	最小值
MAX	最大值
SUM	数值总和
STDWV	计算指定表达式中所有数据的标准差
STDEVP	总体标准差

聚合函数参数的一般格式为：

        
      
[all|distinct] expr

其中 ALL 表示对所有值进行聚合函数运算，默认值。而 DISTINCT 指定每个唯一值都被考虑。expr 为表达式

        
      
-- 查询 '09031' 班学生人数
select count(*) as '人数'
from student
where 班号 = '09031'

5. 数据分组

因为聚合函数只会输出一个结果，查询某个列和聚合函数放一起时会报错

可以通过数据分组计算某一个类别的聚合函数结果，即聚合函数作用范围变成了每组的所有记录

如果想使用聚合函数作为筛选条件，必须放在 HAVING 子句后

        
      
-- 查询最低分大于 70，最高分小于 90 的学生学号
select 学号
from score
where 分数 is not null
group by 学号
having min(分数) > 70 and max(分数) < 90

当 WHERE 子句、GROUP BY 子句、HAVING 子句和聚合函数同时出现在一个查询中时，SELECT 命令的执行顺序如下：

执行 WHERE 子句，从表中选取行
由 GROUP BY 对选取的行进行分组
执行聚合函数
执行 HAVING 子句选取满足条件的分组

        
      
-- 查询每个课程的平均分
select 课程号, avg(分数)
from score
group by 课程号

聚合函数作为条件只能放在 HAVING 子句后，不能放在 WHERE 子句后

当 SELECT 子句中由列名与 聚合函数 时，所有的列名都应该被分组

6. 表的连接查询

在 FROM 子句中指定要连接的表，在 WHERE 子句中给出连接条件

        
      
select <列名1>[, ···]
from <表1>[, ···]
where 连接条件

对于两个表的公共列，需要提供前缀，例如 student.学号

在 SQL Server 中，连接分为内连接和外连接

<1> 内连接 INNER JOIN

Θ 连接，等值连接，自然连接

内连接是最基本的连接方式，使用 INNER JOIN 关键字，不使用该关键字时默认均为内连接

等值连接

即两个表之间的连接关系为 “等于”

        
      
-- 查询所有学生的姓名、课程号和分数列
select student.姓名, score.课程号, score.分数
from student, score
where student.学号 = score.学号
-- 即
select student.姓名, score.课程号, score.分数
from student
inner join score
on student.学号 = score.学号

为简化输入，可以使用表的别名

        
      
-- 上一个例子的简化
select x.姓名, y.课程号, y.分数
from student x, score y
where x.学号 = y.学号

非等值连接

即两个表之间的连接关系不是 “等于”

自连接

将同一个表进行连接，这种连接称之为自连接

        
      
-- 查询选修了 '3-105' 并且成绩高于 '109' 号同学成绩的学生记录
select x.课程号, x.学号, x.分数
from score x, score y
where x.课程号 = '3-105' and x.分数 > y.分数
		and y.学号 = '109' and y.课程号 = '3-105'
order by x.分数 desc

<2> 外连接 OUTER JOIN

左外连接 : LEFT OUTER JOIN 或 LEFT JOIN

右外连接 : RIGHT OUTER JOIN 或 RIGHT JOIN

全外连接 : FULL OUTER JOIN 或 FULL JOIN

        
      
-- 左外连接例子
-- 查询所有课程的任课教师的姓名
select course.课程名, teacher.姓名
from course left join teacher
on (course.任课教师编号 = teacher.教师编号)

7. 子查询

<1> 一般子查询

当一个查询是另一个查询的条件时，换言之，当从表中选取数据行的条件依赖于该表本身或其他表的联合信息时，需要使用子查询来实现

子查询最常用于 SELECT 语句中的 WHERE 子句中

        
      
-- 查询与学号为 '105' 的同学同年出生的学生
select 学号, 姓名, year(出生日期) as '出生年份'
from student
where year(出生日期) = (
						select year(出生日期)
    					from student
    					where 学号 = '105'
						)

<2> 相关子查询

在一般子查询中，每个子查询仅执行一次，返回的值为主查询的 WHERE 子句所用。

当主查询在判断每个待选行时，必须 “唤醒” 子查询，然后反馈给主查询。即内层的条件涉及到外层属性的子查询称为相关子查询

        
      
-- 查询成绩比该课程平均成绩低的学生
select 学号, 课程号, 分数
from score a
where 分数 < (
				select avg(分数)
    			from score b
    			where a.课程号 = b.课程号 
    				and 分数 is not null
				)

<3> 带 EXISTS 测试的子查询

EXISTS 代表存在量词 ∃，只返回逻辑真值或逻辑假值

        
      
-- 查询所有任课教师的姓名和系别
select 姓名, 系别
from teacher a
where exists (
				select *
    			from course b
    			where a.教师编号 = b.任课教师编号
				)

或者还有 NOT EXISTS ，将结果取反

8. 查询结果的并、交、差运算

可以使用以下运算符将两个 SELECT 查询语句的结果进行相应运算

UNION : 并
INSTERSECT : 交
EXCEPT : 差

        
      
-- 并的例子
-- 查询所有教师和学生的姓名、性别和出生年份
select 姓名, 性别, year(出生日期) as '出生年份'
from teacher
union
select 姓名, 性别, year(出生日期) as '出生年份'
from student
-- order by 出生年份

(四)、DCL 数据控制语言

数据控制控制的是用户对数据的存储权力，是由 DBA (数据库管理员) 决定的

数据库的安全性

不安的因素：

非授权对数据库的恶意存取和破坏
数据库中重要的数据泄露
安全环境的脆弱性

安全性控制：

用户身份鉴别：静态口令鉴别、动态口令鉴别、生物特征鉴别、智能卡鉴别
存取控制
自主存取控制方法 (用户可以自定义和分配其他用户的操作权限)

自主存取控制

DBMS (数据库管理系统) 数据控制应具有以下功能

通过 GRANT 和 REVOKE 将授权通知系统，并存入数据字典
当用户提出请求时，根据授权情况检查是否执行操作请求

SQL 标准包括 DELETE 、INSERT 、SELECT 和 UPDATE 权限。SELETE 权限对应于 READ 权限，SQL 还包括了 REFERENCES 权限，用来限制用户在创建关系时定义外码的能力

1. 授权的语句格式

        
      
grant <权限>[, ···] 
on <对象类型> <对象名>[, ···]
to <用户>[, ···]
[with grant option]

不同类型的操作对象有不同的权限，常见的权限如下

对象	对象类型	操作权限
属性列	TABLE	SELECT、INSERT、UPDATE、DELETE、 ALL PRIVILEGES (4 种权限的总和)
视图	TABLE	SELECT、INSERT、UPDATE、DELETE、 ALL PRIVILEGES (4 种权限的总和)
基本表	TABLE	SELECT、INSERT、UPDATE、DELETE、ALTER、INDEX ALL PRIVILEGES (6 种权限的总和)
数据库	DATABASE	CREATETAB 建立表的权限，可由 DBA 授予普通用户

PUBLIC : 接受权限的用户可以是单个或多个具体的用户，PUBLIC 参数可将权限赋给全体用户
WITH GRANT OPTION : 若指定了此子句，那么获得了权限的用户还可以将权限赋给其他用户

2. 收回权限语句格式

        
      
revoke <权限>[, ···]
on <对象类型> <对象名>[, ···]
from <用户>[, ···]

数据库的角色

角色指的时一类人，比如 CEO、总监、普通职员，可以给一类人授权

角色的创建

        
      
create role <角色名>

角色授权

        
      
grant <权限> on <对象类型> <对象名> to <角色1>[, ···]

把角色授权给其他用户或角色

        
grant <角色> to <角色>或<用户名字>
[with admin option]

with admin option 意味着这个用户可以把这权限授予其他角色或用户

角色权限的收回

        
      
revoke <权限> on <对象名> from <角色名字>

视图机制

视图是从一个或者多个基本表或视图中导出的表，是一个虚拟表

为不同的用户定义不同的视图，把不需要的数据给隐藏起来，这样用户就不会进行误操作

视图的创建

        
      
create view <视图名(列表名)>
as select <查询子句>
[with check option]

子查询的 SELECT 语句通常不允许含有 ORDER BY 子句和 DISTINCT 语句
WITH CHECK OPTION 表示对 UPDATE 、INSERT 、DELECT 操作时保证被操作的行满足视图定义中的谓语条件 (即子查询中的条件表达式)
组成视图的属性列名或者全部省略或者全部指定。如果省略，则隐含该视图由 SELECT 子查询目标列的主属性组成

        
      
-- 关系模式：student(sno, sname, sex, SD, email)
-- 建立 '计算机系 (CS)' 学生的视图，并且进行修改操作时
-- 保证视图中只有计算机系的学生会受到影响
create view cs-student
	as select sno, sname, sex
	from student
	where SD = 'CS'
	with check option

视图的删除

        
drop view <视图名>

--例如
drop view cs-student

审计

把对数据库的所有操作都记录到审计日志中，然后就可以通过日志审查这个里面是否有一些非法的行为

        
      
-- 对修改“SC”数据的操作进行审计
AUDIT UPDATE ON SC;
-- 取消对“SC”表的一切审计
NOAUDIT UPDATE ON SC;

数据加密

通过一些加密算法，把明文变成密文，这样别人就无法查看

索引

利用索引可以使数据库程序无需对整个表进行扫描，就可以在其中找到所需数据

数据库中的索引是某个表中一列或者若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单

由内模式定义 (内模式定义所有的内部记录类型、索引和文件的组织方式)

建立索引

        
create [unique] [cluster] index <索引名>
	on <表名>(列名[次序], ···)

次序：可选 ASC (默认) 或 DSC

UNIQUE ：表明此索引的每一个索引值只对应唯一的数据记录

CLUSTER ：表明要建立的索引是聚簇索引，意为索引项的顺序是与表中记录的物理顺序一致的索引组织

删除索引

        
      
drop index <索引>