SQL小结
一、关系数据库标准语言SQL
1、数据定义语言
操作对象 | 创建 | 删除 | 修改 |
---|---|---|---|
模式 | CREATE SCHEMA | DROP SCHEMA | |
表 | CREATE TABLE | DROP TABLE | ALTER TABLE |
视图 | CREATE VIEW | DROP VIEW | |
索引 | CREATE INDEX | DROP INDEX | ALTER INDEX |
建立数据库
CREATE DATABASE STUDENT;--建立名为‘STUDENT’的数据库
模式的定义与删除
1、定义
①仅建立模式
CREATE SCHEMA <模式名> AUTHORIZATION <用户名>; --创建模式
CREATE SCHEMA <模式名> AUTHORIZATION <用户名> --创建模式
[<表定义子句>|<视图定义子句>|<授权定义子句>]; --创建模式同时创建
2、删除
DROP SCHEMA <模式名> RESTRICT;--或CASCADE
RESTRICT:限制删除
CASCADE:级联删除
基本表的定义、删除与修改
1、定义
注意:如果完整性约束条件涉及该表的多个属性列,则必须定义在表记上,否则既可以定义在列级也可以定义在表级。
CREATE TABLE<表名>(<列名><数据类型>[列级完整性约束条件]
[<列名><数据类型>[列级完整性约束条件]]
...
[,<表级完整性约束条件>]);
表与模式
方法一:在表名中明显地给出模式名
方法二:在创建模式的语句中同时创建表
方法三:设置所属模式,这样在创建表时表名不必给出模式名
2、删除
DROP TABLE<表名>[RESTRICT|CASCADE];
RESTRICT:限制删除
CASCADE:级联删除
3、修改
ALTER TABLE <表名>
[ADD [COLUMN]<新列名><数据类型>[完整性约束]]
[ADD<表级完整性约束条件>]
[DROP [COLUMN] <列名>[CASCADE|RESTRICT]]
[DROP CONSTRAINT <完整性约束条件>[CASCADE|RESTRICT]]
[ALTER COLUMN <列名><数据类型>]
索引的建立与删除
1、建立
UNIQUE:表明此索引的每一个索引值只对应唯一的数据记录
CLUSTER:表示要建立的索引是聚簇索引
ASC:按升序排列(默认状态)
DESC:按降序排列
CREATE [UNIQUE][CLUSTER]INDEX<索引名>
ON<表名>(<列名>[<次序>][,<列名>[<次序>]);
2、删除
注意:SQL SERVER中删除索引一定要同时指明表和搜因
DROP INDEX<索引名>;
3、修改
ALTER INDEX <旧索引名>RENAME TO<新索引名>;
**注意:给INDEX重命名时,T-SQL与SQL标准语言不同:
**
EXEC SP_rename 'SC.SCno', 'SCSno';--ALTER INDEX SCno RENAME TO SCSno;
2、数据查询语言
SELECT [ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]...
FROM<表名或视图名>[,<表名或视图名>...]|(<SELECT语句>)[AS]<别名>
[WHERE<条件表达式>]
[GROUP BY<列名1>[HAVING<条件表达式>]]
[ORDER BY<列名2>[ASC|DESC]];
包括:单表查询、连接查询、嵌套查询、集合查询、基于派生表的查询
目标列表达式的可选格式
(1)*
(2)<表名>.*
(3)COUNT([DISTINCT | ALL]*)
(4)[<表名>.] <属性列名表达式> [, [<表名>.] <属性列名表达式>] …
其中,<属性列名表达式>可以是由属性列、作用于属性列的聚集函数和常量的任意算术运算(+, -,x,÷)组成的运算公式。
聚集函数的一般格式
聚集函数 | 作用 |
---|---|
COUNT(*) | 统计元组个数 |
COUNT( [DISTINCT\ALL] <列名>) | 统计一列中值的个数 |
SUM( [DISTINCT\ALL] <列名>) | 计算一列值的总和 (此列必须是数值型) |
AVG( [DISTINCT\ALL] <列名>) | 计算一列值的平均值(此列必须是数值型) |
MAX( [DISTINCT\ALL] <列名>) | 求一列值中的最大值 |
MIN([DISTINCT\ALL] <列名>) | 求一列值中的最小值 |
如果指定DISTINCT 短语,则表示在计算时要取消指定列中的重复值。如果不指定DISTINCT短语或指定ALL短语(ALL 为默认值),则表示不取消重复值。
WHERE 子向的条件表达式的可选格式
(1)<属性列名>θ{<属性列名>、<常量>、[ANYIALL] (SELECT语句)}
(2)<属性列名> [NOT] BETWEEN {<属性列名>、<常量>、(SELECT语句)} AND {<属性列名>、<常量>、(SELECT语句)}
(3)<属性列名> [NOT] IN {(<值1>[,<值2>] …)、(SELECT语句)}
(4) <属性列名> [NOT] LIKE <匹配串>
(5) <属性列名>IS [NoT] NULL
(6) [NOT] EXISTS (SELECT语句)
(7) <条件表达式> {AND 、OR} <条件表达式> {{AND 、OR} <条件表达式>}
2、数据更新语言
插入数据
INSERT
INTO<表名>[<属性列1>[,<属性列2>]...)]
VALUES(<常量1>[,<常量2>]...);
带子查询:
INSERT
INTO<表名>[(<属性列1>[,<属性列2>...])]
子查询;
修改数据
UPDATE<表名>
SET<列名>=<表达式>[,<列名>=<表达式>]...
[WHERE<条件>];
删除数据
DELETE
FROM<表名>
[WHERE<条件>];
3、空值的处理
空值的产生
(1)插入空值
(2)修改为空值
空值的判断
同IS NULL 或 IS NOT NULL来表示。
空值的约束条件
属性定义(或者域定义)中有NOT NULL,约束条件的不能取空值,码属性不能取空值。以及主码的值不允许为空。
空值的算术运算、比较运算和逻辑运算
空值与另一个值(包括另一个空值)的算术运算的结果为空值,空值与另一个值(包括另一的个空值)的比较运算的结果为UNKNOWN。有了UNKNOWN 后,传统的逻辑运 算中二值
(TRUE, FALSE) 逻辑就扩展成了三值逻辑。
4、视图
视图是从一个或几个基本表(或视图)导出的表。它与基本表不同,是一个虚表。数据库中只存放视图的定义而不存放视图对应的数据,这些数据仍存放在原来的基本表中。所以一旦基本表中的数据发生变化,从视图中查询出的数据也就随之改变了。
建立视图
CREATE VIEW 命令
CREATE VIEW <视图名>[(<列名>[,<列名>]...)]
AS <子查询>
[WITH CHECK OPTION]; --表示对视图进行UPDATE、INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件(即子查询中的条件表达式)。
组成视图的属性列名或者全部省略或者全部指定,没有第三种选择。如果省略了视图的各个属性列名,则隐含该视图由子查询中SELECT子句目标列中的诸字段组成。
下列三种情况下必须明确指定组成视图的所有列名:
(1)某个目标列不是单纯的属性名,而是聚集函数或列表达式:
(2) 多表连接时选出了几个同名列作为视图的字段;
(3)需要在视图中为某个列启用新的更合适的名字。
删除视图
语句的格式为:
DROP VIEW <视图名> [CASCADE] ;
视图删除后视图的定义将从数据字典中删除。如果该视图上还导出了其他视图,则使用CASCADE级联删除语句把该视图和由它导出的所有视图一起删除。
基本表删除后,由该基本表导出的所有视图均无法使用了,但是视图的定义没有集字典中清除。删除这些视图定义需要显式地使用DROP VIEW语句。
查询视图
关系数据库管理系统执行对视图的查询时,首先进行有效性检,检查查询中涉及的表、视图等是否存在。 如果存在,则从数据字典中取出视图的定义,把定义中的子查询和用户的查询结合起来,转换成等价的对基本表的查询,然后再执行修正了的查询, 这一转换过程称为视图消解。
二、数据库安全性
1、授权与收回
授权
GRANT <权限>[,<权限>]...
ON<对象类型><对象名>[,<对象类型><对象名>]...
TO<用户>[,<用户>]...
[WITH GRANT OPTION];
收回
REVOKE<权限>[,<权限>]...
ON<对象类型><对象名>[,<对象类型><对象名>]...
FROM<用户>[,<用户>]...[CASCADE|RESTRICT];
2、数据库角色
创建角色
CREATE ROLE<角色名>;
授权
GRANT<权限>[,<权限>]
ON<对象类型>对象名
TO<角色>[,<角色>];
角色授予角色
GRANT<角色1>[,<角色2>]...
TO<角色3>[,<用户1>]...
[WITH ADMIN OPTION];
角色权限的收回
REVOKE <权限>[,<权限>]…
ON <对象类型> <对象名>
FROM <角色>[,<角色>]…;
用户可以回收角色的权限,从而修改角色拥有的权限。即先删除,再重新授权。
3、视图机制
可以为不同的用户定义不同的视图,把数据对象限制在一定的范围内。也就是说,通过视图机制把要保密的数据对无权存取的用户隐藏起来,从而自动对数据提供定程度的,安全保护。
视图机制间接地实现: 支持存取谓词助用户权限定义。
三、数据库完整性
1、实体完整性
实体完整性在CREATE TABLE 中用PRIMARY KEY定义。
(1)对单属性构成的码有两种说明方法,一种是定义为列级约束条件,另一种是定义为表级约束条件。
(2)对多个属性构成的码只有一种说明方法,即定义为表级约束条件。
列级定义主码
CREATE TABLE <表名> --定义表
(<列名> 数据类型 PRIMARY KEY, --在列级定义主码
);
表级定义主码
例2:将Student表中的Sno属性定义为码。
代码:
CREATE TABLE <表名> --定义表
(<列名> 数据类型,
<列名> 数据类型...
PRIMARY KEY(<列名>) --在表级定义主码
);
2、参照完整性
参照完整性在CREATE TABLE中用FOREIGN KEY短语定义哪些列为外码,用REFERENCES短语指明这些外码参照哪些表的主码。
表级定义完整性
FOREIGN KEY (<列名>) REFERENCES 被参照表(<列名>);--表级完整性约束
可能破坏参照完整性的情况:
被参照表 | 参照表 | 违约处理 |
---|---|---|
可能破坏参照完整性 | 插入元祖 | 拒绝 |
可能破坏参照完整性 | 修改外码值 | 拒绝 |
删除元组 | 可能破坏参照完整性 | 拒绝、级联删除、设置空值 |
修改主码值 | 可能破坏参照完整性 | 拒绝、级联修改、设置空值 |
注:某些属性列既是外码,又是主码属性组中的一个,此时不允许设置空值
因此定义参照完整性,除了应该定义外码,还应定义外码列是否允许空值。
显示说明参照完整性
如果想让系统采用其他策略则必须在创建参照完整性是显示地加以说明。
FOREIGN KEY (<列名>) REFERENCES 被参照表(<列名>);--表级完整性约束
ON <操作> CASCADE
ON <操作> CASCADE
ON <操作> NO ACTION;
3、用户自定义完整性
在CREATE TABLE中定义属性的同时,可以根据应用要求定义属性上的约束条件,即属性值限制。
属性上的约束条件
(1)NOT NULL 不允许取空值
(2)UNIQUE 列值唯一
(3)用CHECK短语指定列值应该满足的条件
元组上的约束条件
与属性上约束条件的定义类似,在CREATE TABLE语句中可以用CHECK短语定义元组上的约束条件,即元组级的限制。同属性值限制相比,元组级的限制可以设置不同属性之间的取值的相互约束条件。
4、完整性约束命名子句
完整性约束条件除了在CREATE TABLE语句中定义,还CREATE TABLE语句中提供了完整性约束命名子句CONSTRAINT,用来对完整性约束条件命名,从而灵活地增加、删除一个完整性约束条件。
完整性约束命名子句
CONSTRAINT <完整性约束条件名> <完整性约束条件>
<完整性约束条件>包括NOT NULL、UNIQUE、 PRIMARY KEY、FOREIGN KEY、CHECK短语等。
修改表中完整性限制
可以使用ALTER TABLE语句修改表中的完整性限制。
5、断言
创建断言
CREATE ASSERTION <断言名> <CHECK子旬>
每个断言都被赋予一个名字,<CHECK子向>中的约束条件与WHERE子句的条件表达式类似。
删除断言
DROP ASSERTION <断言名>;
注:T-SQL 中没有 ASSERTION 功能,需要用触发器编写。
SQL SERVER中无断言
6、触发器
触发器使用户定义在关系上的一类由事件驱动的特殊过程。
定义触发器
建立触发器的一般格式:
CREATE TRIGGER <触发器名> /*每当触发事件发生时,该触发器被激活*/
{BEFORE |AFTER} <触发事件> ON <表名> /*指明触发器激活的时间是在执行触发事件前或后*/
REFERENCING NEW|OLD ROW AS<变量> /*REFERENCING 指出引用的变量*/
FOR EACH {ROW|STATEMENT}/* 定义触发器的类型,指明动作体执行的频率*/
[WHEN <触发条件>] <触发动作体> /*仅当触发条件为真时才执行触发动作体*/
激活触发器
触发器的执行,是由触发事件激活的,并由数据库服务器自动执行!
一个数据表上可能定义了多个触发器,遵循如下的执行顺序:
(1) 执行该表上的BEFORE触发器;
(2) 激活触发器的SQL语句;
(3) 执行该表上的AFTER触发器。
删除触发器
删除触发器语句:
DROP TRIGGER <触发器名> ON <表名>
7、存储过程
存储过程是由过程化SQL语句书写的过程,这个过程经编译和优化后存储在数据库服务器中,使用时只要调用即可。
创建存储过程
CREATE OR REPLACE PROCEDURE /*过程名([参数1,参数二...])/*存储过程首部*/
AS<过程化SQL块>;/*存储过程体,描述该存储过程的操作*/
执行存储过程
执行语句:
CALL/PERFORM PROCEDURE 过程名([参数1,参数2,...]);
修改存储过程
重命名语句:
ALTER PROCEDURE 过程名 RENAME TO 过程名;
重新编译语句:
ALTER PROCEDURE 过程名 COMPILE;
删除存储过程
删除语句:
DROP PROCEDURE 过程名;
八、函数
函数和存储过程类似,都是持久性存储模块。函数的定义和存储过程也类似,不同之处是函数必须指定返回的类型。
定义函数
定义函数语句:
CREATE OR REPLACE FUNCTION函数名([参数1,参数2,...)
RETURNS <类型>
AS <过程化SQL块>;
执行函数
CALL/SELECT 函数名([参数1,参数2,...]);
修改函数
重命名语句:
ALTER FUNCTION 过程名1 RENAME TO 过程名2;
重新编译语句:
ALTER FUNCTION 过程名 COMPILE;
这一部分结束啦,但是感觉学了一段时间以后,前面的语句就会忘记很多,会把各种语句弄混淆,ON\FROM…之类的,用着用着就混乱了!!!还需要多看多练记住了才好。
这里还没有熟练掌握就是存在量词、触发器、存储过程以及函数这几部分,还需要多去理解这些。
关于标准SQL和T-SQL语言,从触发器开始,就感觉到了很大的差别,好难记啊!!!哈哈哈哈。
革命尚未成功,同志仍需努力!!!