2021-04-25

SQL-总结

SQL的基本概念

关系数据库的三级模式结构：

外模式：包括若干视图（view）和部分基本表（base table），数据库模式包括若干基本表，内模式包括若干存储文件。
（1）基本表
基本表是本省独立存在的表，在关系数据库管理系统中一个关系就对应一个基本表，一个或多个基本表对应一个存储文件，一个表可以带若干索引，索引也存放在存储文件中。
（2）视图
视图师从一个或几个基本表导出的表。视图是一个虚表，试图在概念上与基本表类似，用户可以在视图的基础上在定义视图。

数据定义

SQL的数据定义功能包括模式定义、表定义、视图和索引的定义。

模式

定义模式：
CREATE SCHEMA <模式名> AUTHORIZATION <用户名>
删除模式：
DROP SCHEMA <模式名> <CASCADE|RESTRICT>

基本表

定义基本表：
CREATE TABLE <表名>（<列名><数据类型>【列级完整性约束条件】【，<>[],……】……【，<表级完整性约束条件>】；
修改基本表：
ALTER TABLE <表名>
【ADD[COLUMN] <新列名> <数据类型>【完整性约束条件】】
【ADD<表级约束性条件>】
【DROP [COLUMN]<列名>【CASCADE|RESTRICT】】
[DROP CONSTRAINT <完整性约束>【RESTRICT|CASCADE】]
[ALTER COLUMN<列名><数据类型>]
删除基本表
DROP TABLE <表名>RESTRICT|CASCADE；

索引的建立与删除

建立索引
CREATE [UNIQUE] [CLUSTER] INDEX <索引名>
ON <表名>;
修改表名
ALTER INDEX <旧索引名> RENAME TO <新索引名>;
删除索引
DROP INDEX <索引名>

视图

定义视图
CREATE VIEW <视图名>
AS <子查询>
[WITH CHECK OPTION]；
删除视图
DROP VIEW [视图名] [CASCADE]；

数据查询

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

单表查询

需要注意的有:
1.查询的不仅可以是表中的属性列，也可以是表达式。
2.消除重复的元素使用DISTINCT
3.谓词in可以用来查找属性值属于指定列集合的元组。
4.字符匹配，模糊查询
谓词like可以用来进行字符串的匹配。
%代表任意长度（包括0）的字符串
代表任意单个字符
\为换码字符,跟在“"后面”_"就没有了通配符的含义，为普通字符
5.用and和or可以连接多个查询条件，and的优先级高于or。
6.用户可以使用order by子句对查询结果按照一个或者多个属性列进行排序。ASC升序，DESC降序，默认为ASC。
7.聚集函数

COUNT（*)  --统计元组个数
COUNT  ([DISTINCT|ALL)]<列名>) --统计一列中值的个数
SUM ([DISTINCT|ALL)]<列名>) --计算一列值总和（此列必须是数值型）
AVG ([DISTINCT|ALL)]<列名>) --统计一列值的平均值（此列必须是数值型）
MAX ([DISTINCT|ALL)]<列名>) --求一列值中的最大值
MIN ([DISTINCT|ALL)]<列名>) --求一列值中的最小值

8.group by子句将查询结果按一列或多列的值分组，值相等的为一组。
【HAVING短语作用于组，从中选择满足条件的组（适用于聚集函数）】

连接查询

等值与非等值连接查询：
[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>；
比较运算符主要有=,<,>,<=,>=,!=(或<>)
=为等值连接，其他为非等值连接
自身连接：将自己与自己连接，可以通过将同一个表取两个别名从而进行连接。
外连接：
左外连接列出左边关系中所有的元组，右外连接列出右边关系中的所有元组。
多表连接：用and多连接一个条件，扩展到多个表的连接。

嵌套查询

例：

select Sno,Sname,Sdept
from Student
where Sdept in
(select Sdept from Student 
  			where Sname='刘晨');

该例为不相关子查询，不依赖父查询。
相关子查询：
例：

select Sno,Cno
from SC x
where Grade >=(select AVG(Grade)
							from SC y
							where y.Sno=x.Sno);

带有ANY（SOME）或ALL谓词的子查询:
子查询返回单值时可以使用比较运算符，返回多值时要使用**ANY（有的系统是SOME）**或这ALL谓词修饰符

>  ANY 大于子查询结果中的某个值

> ALL  大于子查询结果中的所有值

< ANY 小于子查询结果中的某个值

< ALL 小于子查询结果中的所有值

>=  ANY  大于等于子查询结果中的某个值

>= ALL  大于等于子查询结果中的所有值

<= ANY 小于等于子查询结果中的某个值

<= ALL 小于等于子查询结果中的所有值

= ANY 等于子查询结果中的某个值

= ALL 等于子查询结果中的所有值（通常没有实际意义）

!=(或<>) ANY 不等于子查询结果中的某个值

!=(或<>) ALL 不等于子查询结果中的任何一个值

带有EXISTS谓词的子查询
SQL中没有全称量词（for all），但是可以把带有全称量词的谓词传华为等价的带有存在量词的谓词：

(∀x)P=┐(∃x(┐P))

SQL语言中没有蕴含逻辑运算，但是可以利用谓词演算将一个逻辑蕴含的谓词等价转换为：

p→q＝┐pｖq

将该查询进行转换：

（∀y）p→q＝┐（∃y(┐(p→q)))=┐(∃y(┐(┐p V q)))=┐∃y(p V ┐q)

集合查询

select语句的查询结果是元组的集合，所以多个select语句的结果可以进行集合操作。集合操作主要包括并操作UNION、交操作INTERSECT和差操作EXCEPT。

基于派生表的查询

子查询不仅可以出现咋where子句中，也可以出现在from子句中，这是子查询产生的零时表成为著查询的查询对象。

数据更新

插入数据

SQL的数据插入语句通常有两种形式，一种是插入一个元组，另一种是插入子查询结果。
插入元组：
INSERT
INTO<表名>[(<属性列1>[,<属性列2>]…)]
VALUES(<常量1>[,<常量2>]…);
插入子查询结果：
INSERT
INTO <表名>[(<属性列1>[<属性列2>])]
子查询；

修改数据

UPDATE<表名>
SET <列名>=<表达式>[<列名>=<表达式>]…
[WHERE <条件>]

删除数据
DELETE
FROM <表名>
[WHERE <条件>];

空值的处理

空值一般使“不知道”，“不存在”，“无意义”的值，一般有以下几种情况：
1.该属性应该有一个值，但目前不知道它的具体值
2.该属性不应该有值
3.由于某种原因不便于填写

空值一般用IS NULL或IS　NOT　NULL

空值的约束条件
１.属性定义（或域定义）中有NOT　NOLL约束条件的不能取空值
２.加了UNIQUE限制的属性不能取空值
３.码属性不能取空值

空值的算术运算、比较运算和逻辑运算
空值与另一个值（包括空值）的比较运算结果为UNKNOW,传统逻辑运算结果（TRUE，FALSE）的逻辑就扩展成了三值逻辑。
只有使WHERE和HAVING都为TRUE的时候才能出输出结果。

视图的一些操作：
大致上与基本表类似。
视图的作用：
1、视图能够简化用户的操作
2、视图使用户能以多种角度看待同一数据
3、视图对重构数据库提供了一定程度的逻辑独立性
4、视图能够对机密数据提供安全保护
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<角色>[<角色>]

这里用一个例题展示：
① 创建角色R1

create role R1;

②然后使用GRANT语句，使角色R1拥有Student表的SELECT、UPDATE和INSERT权限。

grant select ，update，insert
on table Student
to R1;

③将这个角色授予王平。

grant R1
to 王平；

④一次性收回权限

revoke R1
from 王平；

审计

审计的功能是把用户对数据库的所有操作都自动记录到审计日记中。
添加审计形如：

AUDIT ALTER,UPDATE
ON SC;

删除审计：

AUDIT ALTER,UPDATE
ON SC;

数据库完整性

数据库的完整性是为了保障数据库中存储的数据是正确的。在关系系统中，最重要的是完整性约束时实体完整性和参照完整性，其它完整性都可以归入用户自定义的完整性。

实体完整性
实体完整i性在CREATE TABLE中用PRIMARY KEY定义，存在列级和表级两种说明方法。
实体完整性的两种检验方法：
1.检查主码是否唯一，若不唯一拒绝插入或修改
2.检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改

<定义列>primary key

参照完整性
参照完整性在CREATE TABLE中用FOREIGN KEY定义哪些列为外码，用PEFERENCES短语指明这些外码参照哪些表的主码。

FOREIGN KEY<外码>REDERENCES 外表<外码>；

用户定义完整性
在CREATE　TABLE中定义属性的同时，可以根据应用要求的定义属性上的约束条件，即属性限制，包括：
列值非空（NOT NULL）
列值唯一（UMIQUE）
检查列值是否满足一个条件表达式（CHECK语句）

完整性约束命名子句
CONSTRAINT<完整性约束条件名><完整性约束条件>

完整性约束条件包括：NOT NULL、UNIQUE、PRIMARY　KEY、FOREIGN　KEY、CHECK短语等。
修改表中的完整性限制：
可以使用ALTER TABLE语句修改表中的完整性限制

断言

创建断言的语句格式：

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

每个断言都被赋予一个名字，<CHECK 子句>中的约束条件与WHERE子句的条件表达式类似。
删除断言的语句格式：

DROP ASSERTION<断言名>；

T-SQL 中没有 ASSERTION 功能。

触发器

定义触发器
触发器又叫做事件-条件-动作规则。当特定的系统事件（如对一个表的增。删、改操作，事务的结束等）发生时，对规则中的条件进行检查，如果条件成立则执行规则中的动作，否则不执行该动作。
触发器的一般格式为：

CREATE TRIGGER <触发器名>
{BEFORE|AFTER}<触发事件>ON<表名>
REFERENCING NEW|OLD ROW AS <变量>
FOR EACH{ROW|STATEMENT}
[WHEN <触发条件>]<触发动作体>

出发事件可以是：INSERT、DELETE、UPDATE

删除触发器

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

存储过程
创建存储过程

CREATE OR REPLACE PROCEDURE 过程名 ([参数1,参数2,…])
AS<过程化SQL语言>;

执行存储过程

CALL/PERFORM PROCEDURE 过程名([参数1,参数2,…]);

修改存储过程
使用ALTER PROCEDURE 重命名一个存储过程

ALTER PROCEDURE 过程1 RENAME TO 过程2；

可以使用ALTER PROCEDURE重新编译一个存储过程

ALTER PROCEDURE 过程名 COMPILE

删除存储过程

DROP PROCEDURE 过程名();

函数

函数的定义语句格式

CREATE OR REPLACE FUNCTION 函数名 ([参数1,参数2,…])RETURNS<类型>
AS <过程化SQL块>;

函数的执行语句格式
CALL/SELECT 函数名 ([参数1,参数2,…]);

修改函数
可以使用ALTER FUNCTION重命名一个自定义函数；

ALTER FUNCTION 过程名1 RENAME TO 过程名2；

可以使用ALTER FUNCTION重新编译一个函数

ALTER FUNCTION 函数名 COMPILE;

—————————————————————

总结：
脑子：我觉得你会了。
手：你会个der。
继续努力。