SQL Server总结

最新推荐文章于 2022-10-19 19:37:08 发布

原创最新推荐文章于 2022-10-19 19:37:08 发布 · 376 阅读

7 ·

CC 4.0 BY-SA版权

数据定义

1、模式的定义与删除

在SQL中，模式的定义语句如下：

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

如果没有指定<模式名>，那么<模式名>隐含为<用户名>

在SQL中，模式的删除语句如下：

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

2、基本表的定义与删除

SOL语言使用CREATE TABLE 语句定义基本表，其基本格式如下:

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

3、索引的建立与删除

在SQL中，建立索引使用CREATE INDEX语句，其一般格式为：

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

对于已经建立的索引，如需要对其重新命名，可以使用ALTER INDEX语句，其一般格式为：

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

在SQL中，删除索引使用使用DROP INDEX语句，其一般格式为：

DROP INDEX<索引名>;

数据查询

SQL提供了SELECT语句进行数据查询，该语句具有灵活的使用方式和丰富的功能，其一般格式如下：

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

1、单表查询

①选择表中的若干列

查询指定列

select sno,sname
from student;

查询全部列

select *
from student; 
--等价于
select sno,sname,ssex,sage,sdept
from student;

查询经过计算的值

select sname,2021-sage
from student;

②选择表中的若干元组

消除表中的重复行

select distinct sno
from sc;

查询满足条件的元组

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

③ORDER BY子句

用户可以用ORDER BY子句对查询结果按照一个或多个属性列的升序(ASC)或**降序(DESC)**排列,默认值为升序。

select sno,grade
from sc
where cno='3'
order by grade desc;--降序

④聚集函数

为了进一步方便用户，增强索引功能，SQL提供了许多聚集函数，主要有：

COUNT(*)：元组的个数
COUNT([ALL|DISTINCT]<列名>)：一列中值的个数‘
SUM([ALL|DISTINCT]<列名>)：一列值得总和（该列必须为数值型）
AVG([ALL|DISTINCT]<列名>)：一列值的平均值（该列必须为数值型）
MAX([ALL|DISTINCT]<列名>)：一列值的最大值
MIN([ALL|DISTINCT]<列名>)：一列值得最小值

⑤GROUP BY子句

GROUP BY子句将查询结果按某一列或多列的值分组，值相等的为一组

2、链接查询

前面的查询都是针对一个表进行的，若一个查询同时涉及两个以上的表，则称之为连接查询。连接查询是关系数据库中最主要的查询，包括等值查询、自然连接查询、非等值查询、自身连接查询、外连接查询和复合条件连接查询等

①等值与非等值连接查询

连接查询的WHERE子句中用来连接两个表的条件成为连接条件或连接谓词，其一般格式为：

[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>
其中比较运算符主要有=、>、<、>=、<=、!=(或<>)等

当连接运算符为=时，成为等值连接。使用其他运算符称为非等值连接

②自身连接

连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接，称为表的自身连接

select first.cno,second.cpno
from course first,course second
where first.cpno=second.cno;

③外连接

左外连接列出左边关系(如本例Student）中所有的元组，右外连接列出右边关系中所有的元组

select student.sno, sname, ssex, sage, sdept, cno, grade
from student left outer join SC ON(student.sno = SC.sno);

④多表链接

连接操作除了可以是两表连接、一个表与其自身连接外，还可以是两个以上的表进行连接，后者通常称为多表连接

select student.sno, sname, cname, grade
from student, sC, course
where student.sno = SC.sno And SC.cno = course.cno;

3、嵌套查询

在SQL语言中，一个SELECT-FROM-WHERE称为一个查询块。将一个查询块嵌套在另一个查询块中的WHERE子句或HAVING短语的条件中的查询称为嵌套查询
其中上层的查询块称为外层查询或者父查询，下层的查询块称为内层查询或者子查询

select sname 
from student
where sno in
			(select sno
			 from sc 
			 where cno='2');

①带有IN谓词的子查询

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 等于子查询结果中的所有值（通常没有实际意义）
!=(或<>) ANY 不等于子查询结果中的某个值
!=(或<>) ALL 不等于子查询结果中的任何一个值

事实上，用聚合函数实现子查询通常比直接用ANY或ALL查询效率要高。ANY、ALL与聚合函数的对应关系如下表所示：

	=	<>或！=	<	<=	>	>=
ANY	IN	–	<MAX	<=MAX	>MIN	>=MIN
ALL	–	NOT IN	<MIN	<=MIN	>MAX	>=MAX

④带有EXISTS谓词的子查询

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”

4、集合查询

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

注意：参加集合操作的各查询结果的列数必须相同；对应的数据类型也必须相同

5、基于派生表的查询

子查询不仅可以出现在WHERE字句中，还可以出现在FROM字句中。这时子查询生成的临时派生表成为主查询的对象

数据更新

数据更新操作有三种：向表中添加若干行数据、修改表中的数据和删除表中的若干行数据。在SQL中有相应的三类语句

1、插入数据

SQL的数据插入语句INSERT通常有两种形式，一种是插入一个元组，另一种是插入子查询结果。后者可以一次插入多个元组

①插入元组

插入元组的INSERT语句的格式为：

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

②插入子查询结果

子查询不仅可以嵌套在SELECT语句中用以构造父查询的条件，也可以嵌套在INSERT语句中用以生成要插入的批量数据

插入子查询结果的INSERT语句格式为：

INSERT
INTO<表名>[(属性列1>[,<属性列2>···)]
子查询；

2、修改数据

修改操作又称更新操作，其语句的一般格式为：

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

①修改某一个元组的值

update student
set sage=22
where sno='201215121';

②修改多个元组的值

update student
set sage=sage+1;

③带子查询的修改语句

子查询可以嵌套在UPDATE语句中，用以构造修改的条件

update sc
set grade = 0
where sno in
	（select sno
	  from student
	  where sdept = 'cs');

3、删除数据

删除语句的一般格式为：

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

①删除一个元组的值

delete
from student
where sno = '201215128';

②删除多个元组的值

delete
from sc;

③带子查询的删除语句

delete
from sc
where sno in (
	select sno
	from student
	where sdept = 'cs'
);

空值的处理

1、空值的产生

select * from sc;
insert into sc(sno,cno,grade)
values('201215127','1',null)

2、空值的判断

判断一个属性是否为空值，用is null或is not null来表示

3、空值的约束条件

属性定义（或者域定义）中有not null约束条件的不能取空值，加了unique限制的属性不能取空值，码属性不能取空值。

4、空值的算术运算、比较运算和逻辑运算

空值与另一个值（包括另一个空值）的算术运算结果为空值，空值与另一个值（包括另一个空值）的比较运算的结果为unknown。在查询语句中，只有使where和having子句中的选择条件为true的元组才被选出作为输出结果

视图

视图是从一个或及格基本表（或视图）导出的表。它与基本表不同，是一个虚表。数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍然存放在基本表中。所以一旦基本表中的数据发生变化，从视图中查询出的数据也就随之改变了。视图一经定义，就可以和基本表一样被查询、被删除。也可以在一个视图之上再定义新的视图

1、定义视图

①建立视图

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

②删除视图

DROP VIEW <视图名> [CASCADE];

2、查询视图

select Sno,Sname
from IS_Student
where Sage<20;

3、更新视图

概念：是指通过视图来插入、删除和修改数据，由于视图是不是存储数据的虚表，所以对视图的更新最终要转换为对基本表的更新。为了提高安全性，可以在定义视图时加上with check option子句，这样只有满足条件时才会执行

update IS_Student
set Sname='刘辰'
where Sno='201215125';

4、视图的作用

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

数据库安全概述

数据库的安全性是指保护数据库以防止不合法使用所造成的数据泄露，更改或破坏

1、数据库的不安全因素

①非授权用户对数据库的恶意存取和破坏
②数据库中的重要或敏感的数据被泄露
③安全环境的脆弱性

2、安全标准简介

TCSEC和CC两个准则

数据库安全性控制

在一般计算机系统中，安全系统使一级一级层层设置的，用户要求进入计算机时，系统首先根据输入的用户表示进行用户身份鉴定，只有合法的用户才准许进入计算机系统

1、用户身份鉴别

① 静态口令鉴别 ② 动态口令鉴别 ③生物特征鉴别 ④ 智能卡鉴别

2、存取控制

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

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

定义用户权限和合法权限检查机制一起组成啦数据库管理系统的存取控制子系统
C2级的数据库管理系统支持自主存取控制，B1级的数据库管理系统支持强制存取控制

3、自主存取控制方法

用户权限是由两个元素组成的：数据库对象和操作类型。定义存储权限称为授权。在关系数据库中，存取控制的对象不仅有数据本身（基本表中的数据、属性列上的数据），还有数据库模式（包括数据库，基本表，视图和索引的创建）

对象类型	对象	操作类型
	模式	CREATE SCHEMA
数据库	基本表	CREATE TABLE,ALTER TABLE
模式	视图	CREATE VIEW
	索引	CREATE INDEX

数据	基本表和视图	SELECT,INSERT,UPDATE,DELETE,REFERENCES,ALLPRIVILEGES
	属性列	SELECT,INSERT,UPDATE,REFERENCES,ALLPRIVILEGES

4、授权：授予与收回

①GRANT
GRANT语句一般格式为：

	GRANT语句的一般格式为
	GRANT<权限>
	ON<对象类型><对象姓名>
	TO<用户>
	[WITH GRANT OPTION];

②REVOKE
授予用户的权限可以由授权者用REVOKE语句收回，REVOKE语句的一般格式：

	REVOKE<权限>
	ON<对象类型><对象名>
	FROM<用户>;

5、数据库角色

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

可以为一组具有相同权限的用户创建一个角色

使用角色来管理数据库权限可以简化授权的过程
①角色的创建

CREATE  ROLE  <角色名> 
刚创建的角色是空的，没有任何内容。可以用GRANT为角色授权

②给角色授权

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

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

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

该语句把角色授予某用户，或授予另一个角色。一个角色所拥有的权限就是授予它的全部角色所包含的权限的总和
授予者是角色的创建者或拥有在这个角色上的ADMIN OPTION
指定了WITH ADMIN OPTION则获得某种权限的角色或用户还可以把这种权限授予其他角色

一个角色的权限：直接授予这个角色的全部权限加上其他角色授予这个角色的全部权限
④角色权限的收回

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

用户可以回收角色的权限，从而修改角色拥有的权限
REVOKE动作的执行者或者是角色的创建者或者拥有在这个（些）角色上的ADMIN OPTION

6、强制存取控制方法

在强制存取控制中，数据库管理系统所管理的全部实体被分为主体和客体两大类
主体是系统中的活动实体
项数据库管理系统所管理的实际用户
客体是系统中的被动实体
文件、基本表、索引、视图

敏感度标记
对于主体和客体，DBMS为它们每个实例（值）指派一个敏感度标记：
绝密（Top Secret，TS）
机密（Secret，S）
可信（Confidential，C）
公开（Public，P）
TS>=S>=C>=P

强制存取控制规则：

 仅当主体的许可证级别大于或等于客体的密级时，该主体才能读相应的客体
 仅当主体的许可证级别小于或等于客体的密级时，该主体才能写相应的客体

视图机制

①建立视图

create view cs_student
    as 
    select  *
    from   student
    where  sdept='cs';

    grant  select
     on  cs_student  
     to 王平;
     
     grant all priviliges
     on  cs_student  
     to  张明;

②删除视图

drop view cs_student;

实体完整性

1、定义实体完整性

关系模型的实体完整性在CREATE TABLE中用PRIMARY KEY定义。对单属性构成的码有两种说明方法，一种是定义为列级约束条件，另一种定义为表级约束条件。对多个属性构成的码只有一种说明方法，即定义为表级约束条件

create table student
	(sno char(9) primary key,
	 sname char(20) not NULL,
	 ssex char(2),
	 sage smallint,
	 sdept char(20)
	 );

2、实体完整性检查和违约处理

用PRIMARY KEY短句定义了关系的主码后，每当用户程序对基本表插入一条记录或对主码列进行更新时，关系数据库管理系统将按照实体完整性规则自动进行检查。包括：
①检查主码值是否唯一，如果不唯一则拒绝插入或修改
②检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改

参照完整性

1、定义参照完整性

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

create table sc
	(sno char(9) not NULL,
	 cno char(4) not NULL,
	 grade smallint,
	 primary key(sno,cno),
	 foreign key(sno) references student(sno),
	 foreign key(cno) references student(cno)
	 );

2、参照完整性检查和违约处理

参照完整性将两个表中的相应元组联系起来了。因此，对被参照和参照表进行增、删、该操作时有可能破坏参照完整性，必须进行检查以保证这两个表的相容性

用户定义的完整性

用户定义的完整性就是针对某一具体应用的数据必须满足的语义要求。目前的关系数据库管理系统都提供了定义和检验这类完整性的机制，使用了和整体完整性、参照完整性相同的技术和方法来处理他们

1、属性上的约束条件

①属性上约束条件的定义

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

②属性上约束条件的检查和违约处理

当往表中插入元组或修改属性的值时，关系数据库管理系统将检查属性上的约束条件是否被满足，如果不满足则操作被拒绝执行

2、元组上的约束条件

①元组上约束条件的定义

与属性上约束条件的定义类似，在CREATE TABLE语句中可以用CHECK短语定义元组上的约束条件，即元组级的限制、同属性值限制相比原组级的限制可以设置不同属性之间的取值的相互约束条件

②元组上约束条件的检查和违约处理

当往表中插入元组或修改属性的值时，关系数据库管理系统将检查元组上的约束条件是否被满足，如果不满足则操作被拒绝执行

完整性约束命名子句

1、完整性约束命名子句

CONSTRAINT<完整性约束条件名><完整性约束条件>
<完整性约束条件>包括NOT NULL、PRIMARY KEY、FOREIGN KEY、CHECK语句等

2、修改表中的完整性限制

可以使用ALTER TABLE语句修改表中的完整性限制

断言

1、创建断言的语句格式

CREATE ASSERTION <断言名> < CHECK子句 >
每个断言都被赋予一个名字，CHECK子句中的约束条件与WHERE子句的约束表达式类似

2、删除断言的语句格式

DROP ASSERTION<断言名>