SQL总结

一、关系数据库标准语言SQL

SQL功能	动词
数据查询	SELECT
数据定义	CREATE，DROP，ALTER
数据操纵	INSERT，UPDATE，DELETE
数据控制	GRANT，REVOKE

这几种操纵几乎包含了常用的数据库操作，据我目前了解为止，数据查询是使用最多的操作。

1. 数据定义

操作对象	操作方式
	创建	删除	修改
模式	CREATE SCHEMA	DROP SCHEMA
表	CREATE TABLE	DROP TABLE	ALTRE TABLE
视图	CREATE VIEW	DROP VIEW
索引	CREATE INDEX	DROP INDEX	ALTER INDEX

（1）模式

定义模式：CREATE SCHEMA<模式名> AUTHORIZATION <用户名>;

删除模式： DROP SCHEMA <模式名> <CASCADE|RESTRICT>;

做实验时模式用的不是很多，在SQl Server中是架构。

（2）基本表

a. 定义基本表：

CREATE TABLE<表名>(<列名><数据类型>[列级完整性约束条件]
				[,<列名><数据类型>[列级完整性约束条件]]
				...
				[,<列级完整性约束条件>]);

在定义表时除了选择合适的数据类型，大多情况还应确定表的主码，有的需要外码，考虑参照关系，比如SC表的学号和课程号。为了简便也可以不设置约束，但真的不推荐

b. 常用的数据类型：

数据类型	定义
char(n)	长度为n的定长字符串
varchar(n)	最大长度为n的变长字符串
int	长整数
float(n)	可选精度的浮点数
boolean	布尔量
date	日期，年、月、日
time	时间，时、分、秒

c. 模式与表

每一个基本表都属于某一个模式，有三种方法定义基本表的模式：
在表名中明显地给出模式名
（CREATE TABLE “S-T.Student”）;
在创建模式语句中同时创建表
设置所属的模式

在目前的实验中确实用的很少，不过还好，我记住了

d. 修改基本表

SQL 语言用ALTER TABLE 语句修改基本表，其一般格式为：

ALTER TABLE <表名>
[ADD [COLUMN] <新列名><数据类型> [完整性约束]]
[ADD <表级完整性约束>]
[DROP [COLUMN] <列名> [CASCADE|RESTRICT]]
[DROP CONSTRAINT <完整性约束名> [RESTRICT|CASCADE]]
[ALTER COLUMN <列名><数据类型>];

ADD	增加新列、新的列级完整性约束条件和新的表级完整性约束条件
DROP COLUMN	删除列，CASCADE删除引用的对象，RESTRICT 被其他对象引用拒绝删除
DROP CONSTRAINT	删除指定的完整性约束条件
ALTER COLUMN	修改原有的列定义，包括修改列名和数据类型

e. 删除基本表

语句格式为：

DROP TABLE<表名>[RESTRICT|CASCADE];

默认情况是RESTRICT

在标准SQl中是可以使用[RESTRICT|CASCADE]的，但在SQl Server中是不支持这种删除方式的，如果要删除的表被其他的表引用了是不可以删除的，需要先将引用的表删除或将引用关系删除。一般的话就不建议删除表，因为建一个表不容易。（有必要还是要删的）

（3）索引

SQL语言建立索引的格式如下：

CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名>(<列名>[<次序>] [,<列名> [<次序>]]);

排列次序：（默认为升序）

ASC	升序
DESC	降序

索引的类型：

UNIQUE	此索引的每一个索引值只对应唯一的数据记录
CLUSTER	聚簇索引

SQL 修改索引语句的一般格式：

ALTER INDEX <旧索引名> RENAME TO <新索引名>;

SQL删除索引的语句：

DROP INDEX <索引名>;

建立索引的目的是为了加快查询的速度，书上还关于索引介绍了一堆，确实没记住，不过基本的操作还是会的，但在具体的实验中并没有用到索引，可能是数据量太小吧

2. 数据查询

SELECT [ALL|DISTINCT] <目标列表达式> [,<目标列表达式>]...
FROM <表名或视图名> [,<表名或视图名>...] | (<SELECT 语句>) [AS] <别名>
[WHERE<条件表达式>]
[GROUP BY <列名1>[HAVING<条件表达式>]]
[ORDER BY <列名2>[ASC|DESC]];

这里几乎包含了查询的全部内容

（1）单表查询

a. 查询列
查询指定列：select Sname from Student
查询全部列：select * from Student
查询经过计算的值（目标行列式为表达式时）：select 2014-Sage from Student

select Sname NAME,'Year of Birth:'BIRTH,2020-Sage BIRTHDAY,lower(Sdept)DEPARTMENT--小写
from Student;

这个例子是说明可以为某一列列指定别的名字。

b. 选择表中 的若干元组

Ⅰ 若在结果中想要去掉重复的行，用distinct消除。

Ⅱ 查询满足条件的元组：

通过where子句实现。其包含的常用的查询条件如如下表：

查询条件	谓词
比较	=,>,<,>=,<=,!=,<>,!>,!<;not+上述比较运算符
确定范围	between and（闭区间），not between and
确定集合	in，not in
字符匹配	like，not like
空值	is null，is not null
多重条件（逻辑运算）	and，or，not

字符匹配有两个 ：

%	代表任意长度（长度可以为0）的字符串
_	代表任意单个字符

c. ORDER BY 子句

该子句对查询结果按照一个或多个属性列的升序或降序排列，默认为升序。
对于空值，排序时显示的次序由具体系统实现来决定。

d. 聚集函数

count(*)	统计元组个数
count（[distinct\all]<列名>）	统计一列中值的个数
sum（[distinct\all]<列名>）	统计一列值得综合（此列必须是整值型）
avg（[distinct\all]<列名>）	计算一列值得平均值（此列必须是整值型）
max（[distinct\all]<列名>）	求一列中的最大值
min（[distinct\all]<列名>）	求一列中的最小值

distinct是计算时取消指定列中的重复值，缺省为all。
聚集函数遇到空值时，除count（*）外，都跳过空值而只处理非空值。
WHERE子句中不能用聚集函数作为条件表达式。

e. GROUP BY 子句

将查询结果按某一列或多列的值分组，值相等的为一组。

单表的查询都挺简单的，聚集函数，分组和排序在实际查询中使用的蛮多的，不过在后面时都是有很多条件的查询，就没有这么简单了

（2）连接查询

连接查询有四种：等值与非等值连接查询、自身连接、外连接、多表连接。
外连接分两种：
左外连接：列出左边关系中所有的元组
右外连接：列出右边关系中的所有元组

连接查询不过是通过一个或多个表通过一定的关系选择符合条件的元组，找到要查询的元组与各个表的联系就很简单了，不过有时需要注意指明哪个表的属性列或为某些属性列取一个别名。
详细看：https://blog.csdn.net/dfndj/article/details/115438573?spm=1001.2014.3001.5502

感觉好多的题都是可以使用连接查询的，所以学会连接查询，就学会一大半了 ^ _ ^

（3）嵌套查询

先来一波概念：

嵌套查询：将一个查询块嵌套在另一个查询块的where子句或having短句的条件中的查询称为嵌套查询。
分为父查询（外层查询）与子查询（内层查询）
【注】子查询的select语句中不能使用order by 子句
不相关子查询：子查询的查询条件不依赖于父查询
相关子查询：子查询的条件依赖于父查询

嵌套查询有四种：
带有in谓词的子查询
带有比较运算符的子查询（内层查询返回的是单值）
带有ANY（SOME）或ALL谓词的子查询
带有EXISTS谓词的子查询

前三种还比较好理解，最后一个是真的挺难的，详细介绍又得写一堆东西和例子了，看之前写的吧：https://blog.csdn.net/dfndj/article/details/115613423?spm=1001.2014.3001.5502

嵌套查询好多也是可以使用连接查询来实现的。

（4）集合查询

当涉及到查询结果的集合时会使用到集合查询。

三种集合操作：
并-union
交-intersect
差-except

（5）基于派生表的查询

子查询出现在from子句中，这时子查询生成的临时派生表称为主查询的查询对象。
这里要注意的是，子查询中有聚集函数派生表需要指定属性列。

在做实验或写作业时基于派生表的查询操作不是很多。

3. 数据更新

（1）插入数据

插入元组：

INSERT 
INTO <表名> [(<属性列1> [,<属性列2>]...)]
VALUES (<常量1> [,<常量2>]...);

常量要与属性列一一对应。

插入子查询结果：

INSERT
INTO <表名> [(<属性列1>，<属性列2>...)]
子查询

遇表必有插入数据，手动一条条的好慢啊

（2）修改数据

UPDATE <表名>
SET <列名>=<表达式> [,<列名>=<表达式>]...
[WHERE <条件>];

省略WHERE子句代表要修改所有的元组。

（3）删除数据

DELETE
FROM <表名>
[WHERE <条件>];

在进行数据插入的操作时应注意基本表的完整性规则

4. 空值

空值的出现条件：
（1）该属性应该有一个值，但目前不知道它的值
（2）该属性不应该有值
（3）由于某种原因不便于书写

用IS NULL 或IS NOT NULL判断一个属性的值是否为空。

not null 不能取空值，有unique限制的属性不能为空，码属性不能取空值。

5. 视图

视图是一个虚表，数据库中只存放视图的定义，而不存放视图对应的数据。
视图一经定义，就可以和基本表一样被查询、被删除。也可以在一个视图之上再定义新的视图，但对视图的更新操作有一定的限制。

（1）定义视图

建立视图的一般语句：

CREATE VIEW <视图名>[(<列名> [,<列名>]...)]
AS <子查询>
[WITH CHECK OPTION];

WITH CHECK OPTION会在对视图进行操作时的行满足视图定义中的谓词条件。

删除语句的格式为：

DROP VIEW <视图名>[CASCADE]

组成视图的属性列名或者全部省略或者全部指定。

视图可以建立在一个或多个基本表上，也可以建立在一个或多个已定义好的视图上，或建立在基本表与视图上。

例如：

create view IS_Student
as 
select Sno,Sname,Sage
from Student
where Sdept='IS'
with check option;
select * from IS_Student;

（2）查询视图

查询视图的操作与查询基本表的操作基本相同，注意在where子句后面不要用聚集函数作为条件表达式。

（3）更新视图

更新视图的操作：插入，删除，修改

因为视图是虚表，所以对视图的更新最终要转换为对基本表的更新。

[WITH CHECK OPTION]——在更新视图时会检查是否满足视图中定义的条件，不满足拒绝该操作。

例如：

更新：
update Student
set Sname='陈东'
where Sno='201215128';

插入：
insert
into IS_Student
values('201215129','赵新',20);

删除：
delete
from IS_Student
where Sno='201215129';

学完视图最大的感觉就是它和基本表差不多，它们的建立，更新，删除都几乎一样。但视图是一个虚表，在视图上的操作最终会转换为在基本表上的操作，可以通过建立视图来呈现出想要的数据，简化表格，在一定程度上也对数据具有保密性。

【在第三章的SQL语句学习中，关于基本表的增、删、改、插基本上是学习完了，可能对于一些复杂的查询等操作不是很了解，但对一个基本表的操作应该是掌握了。感觉这些操作在各个方面都在普遍地使用着。】

二 数据库安全性

1 数据库安全概述

（1）对数据库安全性产生威胁的主要因素：
a. 非授权用户对数据库的恶意存取和破坏
b. 数据库中重要或敏感的数据被泄露
c. 安全环境的脆弱性

2 数据库安全性控制

（1）用户身份鉴别

a. 静态口令鉴别
b. 动态口令鉴别
c. 生物特征鉴别
d. 智能卡鉴别

（2）存取控制

存取控制机制主要包括用户定义权限和合法权限检查两部分：

定义用户权限，并将用户权限登记到数据字典中。
合法权限检查

（3）自主存取控制方法

主要通过GRANT和REVOKE语句来实现。

（4）授权

GRANT：向用户授予操作权限
REVOKE：向用户收回操作权限

GRANT：
语句的一般格式为：（将对指定操作对象的指定操作权限授予指定的用户）

GRANT <权限>[,<权限>]...
ON <对象类型><对象名>[,<对象类型><对象名>]...
TO<用户>[,<用户>]...
[WITH GRANT OPTION]

语句执行者：数据库管理员，数据库对象创建者，已拥有该权限的用户
接受权限的用户可以是一个或多个具体用户，也可是PUBLIC。

若指定 WITH GRANT OPTION 子句，获得某种权限的用户还可以将此权限授权于其他用户，没有则不能传播该权限。

当授予某个属性列的权限时，要指明项相应的属性列名。

REVOKE：
收回授权，SQl语句一般格式为：

REVOKE <权限>[,<权限>]...
ON <对象类型><对象名>[,<对象类型><对象名>]...
FROM<用户>[,<用户>]...[CASCADE|RESTRICT]

（5）数据库角色

数据库角色是被命名的一组于数据库操作相关的权限，角色是权限的集合。

1. 角色创建

CREATE ROLE <角色名>

2. 给角色授权

GRANT <权限>[,<权限>]...
ON<对象类型>对象名
TO<角色>[,<角色>]...

3. 将一个角色授予其他的角色或用户
把角色授予给某用户或授予另一个角色

GRANT <角色1>[,<角色2>]...
TO <角色3>[,<用户1>]...
[WITH ADMIN OPTION]

若指定WITH ADMIN OPTION子句，则获得某种权限的角色或用户还可以把这种权限再授予其他角色。

4. 角色权限的收回

REVOKE <权限>[,<权限>]...
ON<对象类型><对象名>
FROM<角色>[,<角色>]...

REVOKE 动作的执行者或是角色的创建者，或者拥有在这个角色上的ADMIN OPTION

（6）强制存取控制方法

强制存取控制使用于那些对数据有严格而固定密级分类的部门。

在强制存取控制中，数据库管理系统所管理的全部实体被分为主体和客体两部分。
主体：系统中的活动实体，既包括数据库管理系统所管理的实际用户，也包括代表用户的各进程。
客体：系统中的被动实体，是受主体操纵的，包括文件、基本表、索引、视图等。

敏感度标记：绝密->机密->可信-> 公开

遵循规则：
（1）仅当主体的许可证级别大于或等于客体的密级时，该主体才能读取相应的客体。
（2）仅当主体的许可证级别小于或等于客体的密集时，该主体才能写相应的客体。

3 视图机制

视图机制间接地实现存取谓词地用户权限定义。
在不支持存取谓词的系统中，可以先建立视图，在视图上定义存取权限。

4 审计

审计功能把用户对数据库的所有操作自动记录下来放入审计日志，审计员可检查审计日志来监控数据库中的各种行为，防止不法行为的产生。

（1）审计事件：
服务器事件
系统权限
语句事件
模式对象事件

（2） 审计功能：
基本功能，提供多种审计查阅方式
提供多套审计规则
提供审计分析和报表功能
审计日志管理功能
系统提供查询审计设置及审计记录信息的专门视图

3.AUDIT语句和NOAUDIT语句

AUDIT：设置审计功能
NOAUDIT：取消审计功能

audit alter ,update	--对SC表的表结构和数据的修改进行审计
on SC;
not audit alter,update	--取消对SC表的表结构和数据的修改进行审计
on SC;

5 数据加密

（1）存储加密：
透明存储加密：内核级加密保护方式，对用户完全透明
非透明存储加密：通过多个加密函数实现

（2）传输加密：
链路加密和端到端加密

【第四章学的好多都是理论性的东西，在目前的实验中用到的确实不是很多，而且对介绍到的安全知识只是初步的了解】

三 数据库完整性

数据库的完整性是指数据的正确性和相容性。
正确性：数据是符合现实世界语义、反应当前实际情况的；
相容性：数据库同一对象在不同关系表中的数据是复合逻辑的。

1 实体完整性

在CREATE TABLE中用 PRIMARY KEY定义。
分为列级约束条件和表级约束条件。

定义主码后的检查内容：
（1)检查主码值是否唯一，如果不唯一则拒绝插入或修改。
（2）检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改。

2 参照完整性

关系模型的参照完整性在CREATE TABLE 中用FOREIGN KEY短语定义那些列为外码，用REFERENCES短语指明这些外码参照那些表的主码。

当参照表和被参照表的操作违反参照完整性性时，系统选用默认策略，如果想让系统采用其他策略则必须在创建参照表时显示地加以说明。

3 用户自定义完整性

（1）属性上的约束条件

属性值限制：
列值非空（not null）
列值唯一（unique）
检查列值是否满足一个条件表达式（check短语）
(用CHECK短语指定列值应满足的条件。)

（2）元组上的约束条件

与属性上的约束条件类似，但元组级的限制可以设置不同属性之间的取值的相互约束条件。

这三个完整性是建表时常用的，一定要注意表的实体完整性和参照完整性，用户自定义性可以根据不同的需求设置不同的约束条件

4 完整性约束命名子句

CONSTRAINT <完整性约束条件名><完整性约束条件>
用来对完整性约束条件命名
完整性约束条件：not null,unique,primary key,foreign key,check 短语等。

使用ALTER TABLE语句修改表中的完整性限制。
例如：

alter table Student
drop constraint C1;
alter table Student
add constraint C1 check(Sno between 900000 and 999999);

5 断言

断言也相当于约束，可以定义涉及多个表或聚集函数操作的比较复杂的完整性约束。

CREATE ASSERTION <断言名><CHECK子句>
DROP ASSERTION <断言名>；

6 触发器

触发器又叫做事件-条件-动作规则，当特定的系统事件（如对一个表的增、删、改操作，事务的结束等）发生时，对条件进行检查，满足条件执行动作。

定义触发器：

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

a. 触发事件可以是INSERT,DELETE或UPDATE，也可以是这几个事件的组合，也可以是UPDATE OF<触发列,...>
b. 触发器类型有行级触发器和语句级触发器

激活触发器：

执行顺序：
（1）执行该表上的before触发器
（2）激活触发器的SQL语句
（3）执行该表上的AFTER触发器

删除触发器：

DROP TRIGGER <触发器名>ON <表名>;

感觉触发器这方面的题还是挺难的，可能是不太了解它的一些语句吧

第三章都是SQl语句，学会了就可以对数据库进行基本的操作了。第四五章好多是理论上的东西，但也包含一些SQL语句，但对四五章的掌握感觉还是差了一点，感觉这些语句比第三章学的更难了些。

关于SQL语句写完了每节的内容了，现在回头一看大部分还是记住的了，不过也有部分忘记了的，还好，写完总结又收回来点。看着之前写的内容进行总结真的是另一种感受。