【2020-2021春学期】数据库作业14：SQL总结

第3章 关系数据库标准语言SQL

这一章主要介绍SQL的基本功能。

SQL完成核心功能只用了9个动词：

SQL功能	动词
数据查询	select
数据定义	create、drop、alter
数据操纵	insert、update、delete
数据控制	grant、revoke

一、数据定义

1.模式的定义与删除

-- 建立模式
create schema 模式名 authorization 用户名
-- 删除模式（T-SQL需要先保证模式为空）
drop schema 模式名

2.基本表的定义、删除与修改

-- 基本表的定义
create table  <表名>(<列名> <数据类型>[ <列级完整性约束条件> ]
      			    [,<列名> <数据类型>[ <列级完整性约束条件>] ] 
        		    …
      			    [,<表级完整性约束条件> ] );
--完整性约束条件到第5章会讲

--基本表的修改
alter table <表名>
[ add [column] <新列名> <数据类型> [ 完整性约束 ] ] --添加列
[ add <表级完整性约束> ] --添加约束
[ drop [column] <列名> [restrict|cascade] --删除列
[ drop constraint <完整性约束名> [restrict|cascade] --删除约束
[ alter column <列名><数据类型> ] ; --修改数据类型

-- 基本表的删除
drop table 表名 [restrict|cascade]
--T-SQL中 [restrict|cascade] 不可用，具体见之前的博客

3.索引

-- 建立索引 例如：
create unique index SCno on SC(Sno ASC,Cno DESC); -- ASC 升序 DESC 降序 ，一般默认为升序
-- 修改索引 例如：
alter index SCno on SC rename to SCSno --标准sql
exec sp_rename 'SC.SCno','SCSno','index'; --T-sql
-- 删除索引 
drop index 索引名 on 表名 ;

二、数据查询

select [all|distinct] <目标列表达式>,...
from <表名或视图名>,... | (<select >) [as] <别名>
[where <条件表达式>]
[group by <列名>[having<条件表达式>]]
[order by <列名>[asc|desc]];

1.单表查询 【2020-2021春学期】数据库作业5：单表查询例题练习

常用的查询条件

查询条件	谓词
比较	=，<，>，<=，>=，!=，<>，!>，!<；NOT+上述比较运算符
确定范围	BETWEEN AND; NOT BETWEEN AND
确定集合	IN, NOT IN
字符匹配	LIKE, NOT LIKE（通配符 % 和 _ ）
空值	IS NULL, IS NOT NULL
多重条件（逻辑运算）	AND, OR, NOT

聚集函数的功能和实现：

功能	实现
统计元组个数	COUNT（*）
统计一列中值的个数	COUNT([DISTINCT或ALL] <列名>)
计算一列值的总和	SUM([DISTINCT或ALL] <列名>)
计算一列值的平均值	AVG([DISTINCT或ALL] <列名>)
求一列中的最大值和最小值	MAX([DISTINCT或ALL] <列名>) MIN([DISTINCT或ALL] <列名>)

DISTINCT或ALL，DISTINCT是相同的数据行只显示一次，ALL是全部显示出来

2.连接查询 【2020-2021春学期】数据库作业6：连接查询例题练习

• 等值查询和非等值查询
• 自身连接
• 外连接
• 多表连接

3.嵌套查询 【2020-2021春学期】数据库作业7：嵌套查询例题练习

• 带有IN谓词的子查询
• 带有比较运算符的子查询
• 带有ANY（SOME）或ALL的子查询
• 带有EXISTS谓词的子查询

4.集合查询 【2020-2021春学期】数据库作业8：集合查询和视图例题练习

• 并-union
• 交-intersect
• 差-except

5.基于派生表查询

子查询不止可以出现在where语句中，还可以出现在from子句中，这时子查询形成的临时派生表成为了主查询查询的对象。

三、数据更新

--元组插入
insert
into <表名>[<属性1>,...]
value(<常量1>,...);
--子查询结果插入
insert
into <表名>[<属性1>,...]
子查询;
--数据修改
update <表名>
set <列名>=<表达式>,...
[where <条件>];
--删除数据
delete
from <表名>
[where <条件>];

四、视图

视图的特点：

• 虚表。是从一个或几个基本表中导出的图。
• 只存放视图的定义。不负责存放数据。
• 基表中的数据发生变化，视图中查询出的数据也会发生改变。

1.定义视图

CREATE VIEW<视图名>[(<列名>[,<列名>]…)]
AS<子查询>
[WITH CHECK OPTION]; -- 在更新操作时自动检查子查询中的条件

2.删除视图

DROP VIEW<视图名>[CASCADE]; --T-SQL中不用CASCADE也默认级联删除

3.查询视图

对用户来说，查询表和查询视图没什么区别

RDBMS是通过视图消解法来进行转化查询的。

1. 进行有效性检查
2. 转换成等价的对基本表的查询
3. 执行修正后的查询

4.更新视图

由于视图是不实际存储数据的虚表，因此对视图的更新最终要转换为对基本表的更新。像查询视图那样，对视图的更新操作也是通过视图消解，转换为对基本表的更新操作。

第4章 数据库安全性

【2020-2021春学期】数据库作业9：数据库安全性（授权、角色）

一、授权：授予和收回

GRANT语句向用户授予权限
REVOKE语句收回已经授予用户的权限

1.GRANT

grannt <权限>[,<权限>]... 
on <对象类型> <对象名>[,<对象类型> <对象名>]…
to <用户>[,<用户>]...
[with grant option];

如果指定了with grant option子句，则获得权限的用户还可以把获得的权限再授予给其他用户（但不允许循环授权）。没有指定则这个用户只能使用该权限但不能传播该权限。

2.REVOKE

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

二、数据库角色

1.角色的创建

create role<角色名>

2.为角色授权

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

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

grant<角色1>[,<角色2>]…
to <角色3>[,<用户1>]… 
[with admin option]

4.角色权限的收回

revoke <权限>[,<权限>]...
on <对象类型> <对象名>
from <角色>[,<角色>]...

第5章 数据库完整性

【2020-2021春学期】数据库作业12：第五章 数据库完整性例题

一、实体完整性

关系模型的实体完整性在create table中用primary key定义。说明方法有两种，一种是定义为列级约束条件，另一种是定义为表级约束条件。

create table Student(
	   	Sno char(9) primary key, -- 在列级定义主码
	);
	
create table Student(
		 Sno char(9),  
	     primary key(Sno) -- 在表级定义主码
	);

二、参照完整性

关系模式的参照完整性在 create table 中用 foreign key 短语定义哪些列为外码，用 references 短语指明这些外码参照哪些表的主码。

例如： foreign key (Cno) references Course(Cno)

三、用户定义完整性

属性上的约束条件的定义：

1. 列值非空（not null）
2. 列值唯一（unique）
3. 检查列值是否满足一个条件表达式（check语句）。

四、完整性约束子句

constraint<完整性约束条件名><完整性约束条件>

-- <完整性约束条件>包括，not null、unique、primary key、foreign key、check短语等。

五、断言

在SQL中可以用create assertion语句来指定更具一般性的约束。可以涉及多个表和聚集操作的比较复杂的完整性约束。

--创建断言：
CREATE ASSERTION<断言名><CHECK子句>
--T-SQL中断言可以用触发器实现

--删除断言：
drop assertion asse_name;

六、触发器

• 任何用户对表的增、删、改操作均由服务器自动激活相应的触发器。
• 触发器可以实施更为复杂的检查和操作，具有更精细和更强大的数据控制能力 。

定义触发器：

--标准SQL
create trigger <触发器名>  
{before | after} <触发事件> on <表名>
referencing new|old row as<变量>
for each  {row | statement}
[when<触发条件>]<触发动作体>


--T-SQL（来自SQLserver帮助文档）
CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
ON { table | view }   
[ WITH <dml_trigger_option> [ ,...n ] ]  
{ FOR | AFTER | INSTEAD OF }	
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
[ WITH APPEND ]  
[ NOT FOR REPLICATION ]   
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }  

删除触发器：

drop trigger <触发器名> on <表名>;
--T-SQL的不同就是不用on表名了

七、存储过程

存储过程：由过程化SQL语句，经编译和优化后存储在数据库服务器中，可以被反复调用，运行速度较快。

创建存储过程：

create or replace procedure过程名([参数1,参数2,...]) as <过程化SQL块>；

删除存储过程：

drop procedure transfer_salary;

学习心得

学了这么长时间，感觉数据库越学到后面越觉得有意思。学习的过程是渐进式的，第三章一开始的建表，插入数据，简单查询的比较基础的知识都很容易接受，语句也都非常简单，每学习一个知识点都可以应用起来。到exists谓词就稍微上难度了，但有能力完成的查询也更复杂多了。之后又学习了视图，进一步理解了外模式是如何作用的。第四章在视图的基础上又进一步学习了授权和角色，这时对于数据库的查询操作就可以不只用DBA的身份了，也就更符合数据库在现实中的应用场景了，这个时候特别想学习学习怎么做可视化，想随便做个应用出来，但确实太忙了（舍不得宝贵的玩游戏的时间哈哈）连那个MD5的小应用都没完成。第五章就是对之前的知识的完善和进一步学习，其中触发器和存储过程印象最深（要打的代码突然变长了…），触发器可以进行很复杂和精细的处理和操作，存储过程则是更像过程化语言了，更理解了数据是如何被应用到现实问题中解决现实问题的。