数据库SQL总结

SQL,Structured Query Language,标准数据库标准语言。

集 DDL、DML、DCL于一体。

SQL的动词

数据查询：SELECT
数据定义：CREATE、DROP、ALTER
数据操纵：INSERT、UPDATE、DELETE
数据控制：GRANT、REVOKE

SQL外模式

外模式包括若干视图、部分基本表

数据库-模式-表，视图，索引

模式（架构）

创建

CREATE SCHEMA 模式名 AUTHORIZATION 用户名;
/*没有指定模式名，模式名隐含为用户名*/

删除

DROP SCHEMA 模式名 CASCADE|RESTRICT;
/*                 级联   |限制*/

表

创建表

CREATE TABLE 表名
(
 列名 数据类型 PRIMARY KEY(主码，不设主码不写),
 列名 数据类型,
 ……
)

例如创建一个学生表

CREATE TABLE Student
		(Sno CHAR(9) PRIMARY KEY,
		 Sname CHAR(20) UNIQUE,
		 Ssex CHAR(2),
		 Sage SMALLINT,
		 Sdept CHAR(20)
		 );

修改表

增加列

ALTER TABLE Table_Name
ADD 列名 数据类型;

删除列

DROP [ COLUMN ] <列名> [CASCADE| RESTRICT]
/*这里的CASCADE RESTRICT 上面由介绍*/

修改基本表的一般格式

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

索引

创建索引

例子

CREATE UNIQUE INDEX Stusno ON Student(Sno);
CREATE UNIQUE INDEX Coucno ON Course (Cno);
CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC);

修改索引

ALTER INDEX SCno RENAME TO SCSno;
/*          旧索引名        新索引名*/

删除索引

DROP INDEX Stusname;

查询

可以在选择的列名后加上想要在结果表格中的别名，中间需要用空格隔开。
大写函数 UPPER 小写函数 LOWER

一般格式

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

group by是按照<列名>的值进行分组，在组里面用聚集函数。
查询数据括号里

查询经过计算的值：

select Sname,2021-Sage
from student;

查询列的别名

select Sname,'出生月份：',2021-Sage,LOWER(Sdept)
from student;

查询加条件（WHERE）

select Sname , Sdept,Sage
from student
where Sage between 18 and 23;
 WHERE + IN 
 				+ BETWEEN AND
 				+ LIKE
 				+ NOT LIKE

LIKE 后面加%（百分号）代表任意长度（长度可为0）的字符串。
_（下划线）代表任意单个字符。

ORDER BY

asc升序；desc降序，默认为asc

连接查询

自身连接 外连接 多表连接
就是在FROM 后面写上要连接的表，自身连接需要对自己的表 进行定义两个名字，外连接 表 LEFT OUTER JOIN 表 ON（……） 左外连接
还有又外连接

嵌套查询

在WHERE后的IN里面进行SELECT查询
上层叫外层查询或父查询，下层查询叫内层查询或者子查询。
不相关子查询：由里向外，子查询不依赖父查询
相关子查询：由外向里，子查询依赖

例3.62 查询选修了全部课程的学生姓名。

SQL中没有全称量词 all但是可以把带有全称量词的谓词转换为等价的带有存在量词的谓词

先把所有转换成用存在量词的谓词，再用EXISTS进行查询。

SELECT Sname
FROM Student
WHERE NOT EXISTS(SELECT *
FROM Course
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE Sno=Student.Sno
AND Cno=Course.Cno));

带有EXISTS谓词的子查询

只返回true或者false；
当内层查询里有数据时，为true，否则为false;
相当于存在量词。
一般内层查询的列只用*，他只返回一个true 或者 false。
not exists 相当于exist取反。

集合查询

select返回的是一个集合，集合操作就是普通的集合操作，交 并 差 union intersect except。

例3.64 查询计算机科学系的学生及年龄不大于19岁的学生。

SELECT *
FROM Student
WHERE Sdept='CS'
UNION SELECT *
FROM Student
WHERE Sage<=19;

这就是查询的两个结果进行并集。
注意：参加集合操作的各查询结果的列数必须相同；对应项的数据类型也必须相同。

派生表

子查询不仅可以在WHERE子句中，在FROM里也可以，这个时候叫做 临时派生表。

数据更新

INSERT插入 UPDATE修改数据 DELETE删除
INSERT
INTO <表名> [(<属性列1> [,<属性列2>… )]
子查询;

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

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

视图

子查询可以是任意的SELECT语句
组成视图的属性列名或者全部省略或者全部指定，没有第三种方法。
在下列三种情况下必须明确指定组成视图的所有列名。
（1）某个目标列不是单纯的属性名，而是聚集函数或者列表达式
（2）多表连接时选出了几个同名列作为视图的字段
（3）需要在视图中为某个列启用新的更合适的名字
例如：

CREATE VIEW CS_S1(Sno,Sname,Grade)
AS
	SELECT Student.Sno,Sname,Grade
	FROM Student,sc
	WHERE Student.Sno=sc.Sno AND Cno='1' AND Sdept='CS';

删除视图

如果视图上还导出了其他视图，则使用CASCADE级联删除语句把该视图和由他导出的所有视图一起删除。

创建用户

先创建登录名，然后再右键创建的登录名，点击属性，找到用户映射，选择要映射的数据库确认，再返回数据库找到用户就会看到映射的用户创建成功了，断开sa连接，输入刚刚创建的登录名和密码，登陆成功会发现在映射的那个数据库中的用户里面只会出现登录名映射的用户名。

授权：授予和收回

GRANT语句向用户授予权限，REVOKE语句收回权限。
如果指定WITH GRANT OPTION，则获得某种权限的用户可以把这种权限再授予其他用户，没有则不能授予。
例4.5 把对表SC的INSERT权限授予U5用户，并允许将此权限再授予其他用户。

GRANT INSERT
ON TABLE SC
TO U5
WITH GRANT OPTION;

例4.9 收回所有用户对表SC的查询权限。

REVOKE SELECT
ON TABLE SC
FROM PUBLIC;

角色

角色其实就是一个集合，相当于一类人，权限的集合。使用角色来管理数据库权限可以简化授权的过程。

创建角色

CREATE ROLE<角色名>

给角色授权

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

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

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

角色权限收回

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

创建角色

CREATE ROLE R1;
GRANT SELECT,UPDATE,INSERT
ON TABLE Student
TO R1;
GRANT R1
TO U1,U2,U3;–包含全部权限
–如果要收回权限
REVOKE R1
FROM U1;
TSQL赋予角色应该用

EXEC sp_addrolemember ‘角色名’,‘用户名’
删除用户U1的R1角色,并尝试进行修改操作，显示没有权限，则证明U1不属于R1类角色，并且没有了R1相关的权限。

EXEC sp_droprolemember ‘R1’, ‘U1’;

角色的权限修改。

例：
–使角色R1增加了DELETE权限
GRANT DELETE
ON TABLE Student
TO R1;

视图机制

对用户对试图的使用进行权限定义，间接实现支持存取谓词的用户权限定义，从而自动对数据提供一定程度的安全保护。
进行授权权限，换用户登录时，需要把建立的查询全部关闭，不然会造成错误，例4.14，UPDATE就报错了。

审计

审计功能就是数据库管理系统达到C2以上安全级别必不可少的一项指标。
审计功能把用户对数据库的所有操作自动记录下来放进审计日志中。人管理员可以通过这个文件，找出是否存在可疑行为。

AUDIT语句和NOAUDIT语句

audit alter ,update
on sc
noaudit alter ,update
on sc

取消对SC表的一切审计。

NOAUDIT ALTER,UPDATE
ON SC;

alter server audit MyFileAudit with(state=off)
给用户某角色权限
TSQL用EXEC sp_addrolemember ‘角色名’,‘用户名’

实体完整性

实体完整性在CREATE TABLE中用PRIMARY KEY定义，就是定义码。

参照完整性

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

用户定义完整性

列值非空 NOT NULL
列值唯一 UNIQUE
检查列值是否满足一个条件 CHECK短语

定义表时，说明一些属性不允许空值。

CREATE TABLE SC
(Sno CHAR(9) NOT NULL,
Cno CHAR(4) NOT NULL,
Grade SMALLINT NOT NULL,
PRIMARY KEY (Sno,Cno),
……);
这里设置的Sno，Cno，Grade都不能为空值，就是在定义的时候，在数据类型后面加上 NOT NULL。

CHECK()

这个括号里面如果填列值区间，就直接填列值的条件表达式，要是在几个选项之间选择，就用列值IN（，，，）。
往表中插入或修改元组的时候，如果不满足元组的约束条件，就会被拒绝执行。

完整性约束命名子句

CONSTRAINT<完整性约束条件名><完整性约束条件>
完整性约束条件就是NOT NULL、UNIQUE、PRIMARY、FOREIGN KEY、CHECK短语等。
这个是嵌入在CREATE里面的。
例：

建立学生登记表Student，要求学号在90000~99999之间，姓名不能取空值，年龄不能小于30，性别只能是“男”或“女”。

CREATE TABLE Student
(
Sno NUMERIC(6)
CONSTRAINT C1 CHECK(Sno BETWEEN 90000 AND 99999),
Sname CHAR(20)
CONSTRAONT C2 NOT NULL,
Sage NUMERIC(3)
CONSTRAINT C3 CHECK (Sage<30),
Ssex CHAR(2)
CONSTRAINT C4 CHECK(Ssex IN (‘男’,‘女’))，
CONSTRAINT StudentKey PRIMARY(Sno)
);

删除原来的约束条件

ALTER TABLE Student
DROP CONSTRAINT C1;

断言

创建断言

CREATE ASSERTION <断言名><CHECK 子句>
CHECK 子句中的约束条件与WHERE子句的条件表达式相似。

删除断言

DROP ASSERTION <断言名>

触发器

定义触发器

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

删除触发器

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

存储过程

创建存储过程

CREATE OR REPLACE PROCEDURE 过程名([参数1,参数2,...]) 
AS  <过程化SQL块>；

执行

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

修改存储过程

ALTER PROCEDURE 过程名1 RENAME TO 过程名2;

删除存储过程

DROP PROCEDURE 过程名()；
TSQL使用方法
DROP PROCEDURE 过程名；

函数

函数和存储过程类似，都是持久性存储模块。不同之处是函数必须指定返回的类型。
函数的定义语句格式

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

函数的执行语句格式

CALL/SELECT 函数名 ([参数1,参数2,…]);

修改函数

ALTER FUNCTION 过程名1 RENAME TO 过程名2;--重命名一个函数
ALTER FUNCTION 过程名 COMPILE;--重新编译一个函数