SQL 总结

前言

数据库的操作包括
创建：CREATE
增：ADD
删：DROP（表）、DELETE（数据）
改：ALTER（表）、UPDATE （数据）
查：SELECT
另外包括标准SQL才可以使用的
暴力删除数据包括级联：CASCADE
删除数据有级联就怂了的：RESYRICT
一些聚集函数：
COUNT（*) --统计元组个数
COUNT ([DISTINCT|ALL)]<列名>) --统计一列中值的个数
SUM ([DISTINCT|ALL)]<列名>) --计算一列值总和（此列必须是数值型）
AVG ([DISTINCT|ALL)]<列名>) --统计一列值的平均值（此列必须是数值型）
MAX ([DISTINCT|ALL)]<列名>) --求一列值中的最大值
MIN ([DISTINCT|ALL)]<列名>) --求一列值中的最小值

模式

创建模式

CREATE SCHEMA <模式名> AUTHORIZATION <用户名>;

为模式授权

CREATE SCHEMA <模式名> AUTHORIZATION <用户名> [<表定义子句>|<视图定义子句>|<授权定义子句>];

删除模式

DROP SCHEMA <模式名><CASCADE|RESTRICT>;

表

创建表

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

常用数据类型：
CHAR(n),CHARACTER(n) --长度为n的定长字符串
VARCHAR(n),CHARACTERVARYING(n) --最大长度为n的变长字符串
INT,INTEGER --长整数（4字节）
SMALLINT --短整数（2字节）
BIGINT --大整数（8字节）
NUMERIC(p,d) --定点数，p位数字（不包括括号，小数点）组成，小数点后面有d位数字
FLOAT(n) --可选精度的浮点数，精度至少为n位数字
BOOLEAN --逻辑布尔量
DATE --日期，包含一日的年，月，日，格式为：YYYY-MM-DD
TIME --时间，包含一日的时，分，秒，格式为HH：MM：SS

修改基本表

ALTER TABLE<表名>  --用于增加新列，新的列级完整性约束条件和新的表级完整性约束条件
[ADD[COLUMN]<新列名><数据类型>[完整性约束]] 
[ADD<表级完整性约束>]
[DROP[COLUMN]<列名>[CASCADE|RESTRICT]] --删除表中的列
[DROP CONSTRAINT<完整性约束名>[CASCADE|RESTRICT]]  --删除指定的完整性约束条件
[ALTER COLUMN<列名><数据类型>];  --修改原有的列定义

删除基本表

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

详情参考

索引

建立索引

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

==UNIQUE表名此索引的每一个索引值只对应唯一的数据记录
CLUSTER表示要建立的索引是聚簇索引

修改索引

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

删除索引

DROP INDEX <索引名>;

数据查询

一般格式：

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

单表查询

模糊查询

[NOT]LIKE'<匹配串>'[ESCAPE'<换码字符>']

通配符
%代表任意长度（包括0）的字符串
代表任意单个字符
\为换码字符,跟在“"后面”_"就没有了通配符的含义，为普通字符
NULL
对一个属性赋值使用NULL的话，要使用“IS NULL” 使用“=”不会报错，但是没有结果
ORDER BY子句
注意：可以按照一个或多个属性排序，有优先级。ASC升序，DESC降序，默认为ASC
聚集函数

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

GROUP　BY子句
==注：WHERE子句作用于基本表或视图，从中选择满足条件的元组。
HAVING短语作用于组，从中选择满足条件的组（适用于聚集函数）

连接查询

等值与非等值连接查询：

[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>

比较运算符主要有=,<,>,<=,>=,!=(或<>)
=为等值连接，其他为非等值连接
自身链接
主要就是对于一个表取两个名字（例如X，Y）进行连接
外连接
形如左外连接：

SELECT *
FROM Student LEFT OUTER JOIN SC ON(Student.Sno=SC.Sno);

多表连接
用and多连接一个条件，扩展到多个表的连接

嵌套查询

形如：

SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN
(SELECT Sdept
FROM Student
WHERE Sname='刘晨');

为SELECT中有下一级SELECT进行条件

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

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

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

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

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

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

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

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

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

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

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

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

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

=ANY等价于IN谓词，<ANY等价于<MAX，<>ALL等价于NOT IN谓词，<ALL等价于<MIN.
带有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)

集合查询

主要包括并操作UNION交操作INTSERSECT差操作EXCEPT
'及‘可以看成’或‘，既可以用’OR来表示
交集INTERSECT可以看成并且AND

基于派生表的查询

在FROM语句中在构建一个临时的表，用来进行参考，多使用聚集函数

数据更新

插入数据

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的时候才能出输出结果。

视图

建立试图

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

WITH CHECK OPTION表示视图对UPDATE、INSERT、DELETE操作时要保证更新、插入或删除的行满足视图定义的谓词条件（子查询的条件表达式）
以下三种情况必须明确指定组成视图的所有列名：
1、某个目标列不是单纯的属性名，而是聚集函数或列表达式
2、多表连接时选出了几个同名列作为视图的字段
3、需要在视图中为某个列启用新的更合适的名字

删除视图

DROP VIEW <视图名>[DASCADE];

查询视图

言简意赅，查询视图操作和查询基本表不能说一摸一样，只能说完全一致

更新视图

嗯哼，上一条包括更新视图

视图的作用

1、视图能够简化用户的操作
2、视图使用户能以多种角度看待同一数据
3、视图对重构数据库提供了一定程度的逻辑独立性
4、视图能够对机密数据提供安全保护
5、适当利用视图可以更清晰地表达查询

数据库

授权：授予与收回

1.GRANT（授予）
一般格式：

GRANT<权限>[<权限>]
ON<对象类型><对象名>[<对象类型><对象名>]
TO<用户>[<用户>]
[WITH GRANT OPTION];   --获得某种权限的用户还可以把这种权限授予其他用户

2.REVOKE（收回）
一般格式：

REVOLE<>[<>]
ON<>[<>]
FROM<>[<>][DASCADE|RESTRICT]

数据库角色

1.角色的创建

CREATE ROLE <角色名>；

2.给角色授权

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

3. 将一个角色授予其他的用户或用户

GRANT <角色1>[<角色2>]
TO<角色3>[<用户1>]
[WITH ADMIN OPTION]；  --则获得某种权限的角色或用户还可以把这种权限授予其他角色

该语句时把角色授予某用户，再授予另一个角色，这样，一个角色（3)有的权限就是授予他的全部角色（1和2）所包含权限的总和。
4.角色权限的收回

REVOKE <权限>[<权限>]
ON<对象类型><对象名>
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 功能。

类似的有RULE，但使用方法不同：

上下文中不允许使用子查询，只允许使用标量表达式。

触发器

定义触发器

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

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;

函数与存储过程：
同：都是持久性的存储模块
异：函数必须指定返回类型

总结

经过长达这几天对标准SQL的总结，能很大程度上弥补我在几天前为了赶进度而忽略的标准SQL语句，然后再一遍遍品味之前作业的时候，渐渐发现之前理解不透彻的地方，对于一些有着细微差别的作用语句进行细微区别。。。