数据库复习

SQL动词

数据查询：Select
数据定义：Create  Drop   Alter
数据操纵：Insert   Delete   Update
数据控制：Grant   Revoke

数据定义

	创建	删除	修改
模式	Create Schema	Drop Schema
表	Create Table	Drop Table	Alter Table
视图	Create View	Drop View
索引	Create Index	Drop Index	Alter Index

模式的定义和删除

Create Schema <模式名> AUTHORIZATION <用户名>;

eg:
Create Schema "S-T" AUTHORIZATION WANG;
Create Schema AUTHORIZATION WANG;
没有指定模式名，所以用户名隐含为用户名WANG;

删除模式:
Drop Schema <模式名> <CASCADE|RESTRICT>
选择了CASCADE(级联)表示在删除模式的同时,把该模式中所有数据库对象全部删除;
选择了RESTRICT(限制)表示如果模式中已经定义了下属的数据库对象(如表、视图),
则拒绝该语句的执行

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

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

eg:
Create Table SC (Sno CHAR(9),
				 Cno CHAR(4),
				 Grade SMALLINT,
				 PRIMARY KEY(Sno,Cno),
				 FOREIGN KEY(Sno) REFERENCES Student(Sno),
				 FOREIGN KEY(Cno) REFERENCES Course(Cno)
				 );

数据类型（几个常用的）

数据类型	含义
CHAR(n) ,CHARACTER(n)	长度为n的定长字符串
VARCHAR(n)	最大长度为n的变长字符串
INT, INTEGER	长整数(4B）
SMALLINT	短整数(2B)
NUMERICAL(p,d),DECIMAL(p,d)	定点数,由p位数字(不包括符号，小数点)组成，小数点后面有d位数字
FLOAT(n)	可选精度的浮点数，浮点数至少为n位数字
BOOLEAN	布尔值
DATE	日期,包含年、月、日，格式为YYYY-MM-DD
TIME	时间,包含一日的时、分、秒，格式为:HH:MM:SS
TIMESTAMP	时间戳类型

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

DROP COLUMN 时,如果指定了CASCADE短语,则自动删除引用了该列的其他对象,比如视图;
如果指定了RESTRICT短语,该列被其他对象引用,则拒绝删除该列;
DROP CONSTRAINT 子句用于删除指定的完整性约束条件

删除外键的属性时，需要先删除外键约束关系,可以参考:
添加链接描述

Alter Table SC
DROP FOREIGN KEY CID CASCADE;

然后再删除列属性:
Alter Table Class
Drop COLUMN CID CASCADE; 

删除表

Drop Table <表名> [RESTRICT|CASCADE];
若选择RESTRICT,表示欲删除的表不能被其他表的约束所引用,不能有视图,
不能有触发器,不能有存储过程或者函数,否则,将删除失败;

若选择CASCADE,则该表的删除没有限制条件,删除表的同时,相关的依赖对象,
例如视图都将被删除;

索引的建立与删除

当表的数据量过大，查询操作会比较耗时，建立索引，可以加快查询速度,可以在基本表上建立一个或多个索引。

常见的索引方式：B+树索引、散列索引、位图索引、顺序文件上的索引等;
B+树索引有动态平衡的优点;
散列索引是建立若干桶，根据散列函数映射到相应的桶中,具有查找速度快的优点;

建立索引：
Create [UNIQUE] [CLUSTER] INDEX <索引名>
ON <表名>(列名1 次序1,列名2 次序2);

UNIQUE表示此索引每一个索引值只对应唯一的数据记录;
CLUSTER表示要建立的索引是聚簇索引;
次序ASC表示升序,DESC表示降序;

eg:
Create UNIQUE INDEX Stusno On Students(Sno);
Create UNIQUE INDEX SCno On SC(Sno ASC,Cno DESC);

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

删除索引：
DROP　INDEX　<索引名>;

数据查询

Select [ALL|DISTINCT] <目标列表达式>...
From <表名或视图名>...
WHERE <条件表达式>
Group By <列名>
Having <条件表达式>
Order By <列名2> [ASC|DESC];

DISTINCT 用来消除重复的行; 

查询条件	谓词
比较	=, >, <, >=, <=, !=, <>, !>, !<
确定范围	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] <列名>);		求一列的最小值

自身连接：
Select First.Cno,Second.Cpno
From Course First,Course Second
Where First.Cpno = Second.Cpno;

左外连接:
Select Student.Sno,Sname,Sex,...
From Student Left OUTER JOIN SC 
ON (Student.Sno = Sc.Sno);

不相关子查询：子查询的查询条件不依赖于父查询;
eg:
查询选修了课程名为"信息系统"的学生学号和姓名
Select Sno, Sname
From Student
Where Sno IN (Select Sno
			From SC
			Where Cno IN (Select Cno
						From Course
						Where Cname = '信息系统'
						)
			  );
		
相关子查询:如果子查询的查询条件依赖于父查询;
eg:
找出每个学生超过他自己选修课程平均成绩的课程号
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	小于子查询结果中的所有值;
同理>= , <= , = , != , <>(不等于);

带有EXISTS谓词的子查询

EXISTS代表存在量词存在,带有EXISTS谓词的子查询不反回任何数据,
只产生逻辑真值True或逻辑假值False

集合查询

Select 语句查询结果是元组的集合,因此可以进行集合操作,包括并操作Union、交操作InterSect、差操作Except。
注意:参加集合操作的各查询结果的列数必须相同,对应的数据类型也必须相同。

数据更新

插入语句:
1)插入元组:
Insert into <表名> (属性1,属性2,属性3...属性n)
Values(常量1,常量2,常量3...常量n);

eg:
Insert into Student(Sno,Sname,Ssex,Sdept,Sage)
Values(201215128,'小明','男','IS',18);

注意:属性值如果为字符串,需要使用单引号括起来;

2)插入子查询结果
Insert into <表名> (属性1,属性2,属性3,...属性n)
子查询;

eg:
Insert into Dept_age(Sdept,Avg_age)
Select Sdept,AVG(Sage)
From Student 
Group By Sdept;

修改数据(更新操作)
Update <表名> 
Set <列名>=表达式,<列名2>=表达式2...
Where <条件>;

eg:
Update Student 
Set Sage=22
Where Sno='201215121';

修改多个元组的值:
将所有学生年龄增加1岁
Update Student
Set Sage = Sage+1;

带有子查询的修改语句:
Update SC
Set Grade = 0
Where Sno In (Select Sno
			From Student
			Where Sdept='CS'
			);

删除语句:
Delete
From <表名>
Where 条件;

1)删除一个元组的值:
Delete From Student
Where Sno = '201215128';

2)删除多个元组的值:
Delete From Student;

3)带子查询的删除语句:
Delete From SC
Where Sno In (Select Sno 
			From Student
			Where Sdept = 'SC'
			);

空值的处理

空指含有不确定性的意义,不知道/不存在/无意义
空值的判断：使用IS NULL 或IS NOT NULL；
空值的限制:

1. 加了UNIQUE限制的属性不能取空值;
2. 码属性不能取空值;

视图

从一个或几个表中导出来的表，实际并不存在,一旦表中数据发生变化，视图中的查询出来的数据也就随之改变;
视图一经定义就可以被查询、删除,也可以在一个视图上定义新的视图,但对使徒的更新操作有一定限制。

建立视图:
Create View <视图名> [(列名1,列名2,..列名n)]
AS 子查询
[With Check Option];

With Check Option表示对视图进行Update、Insert和Delete操作要保证
更新、插入或删除的行满足视图定义中的谓词条件

Create View IS_Student
AS
Select Sno,Sname,Sage
From Student
Where Sdept='IS'
With Check Option;
在当对视图进行插入、修改、删除时因为有With Check Option语句,关系数据库会自动加上
Sdept='IS';

删除视图
Drop View <视图名> [CASCADE];

查询视图
首先检查视图、表是否存在,如果存在,则从数据字典中去除视图定义,
把定义的子查询和用户查询结合起来,转换成对基本表的查询;
Select IS_Student,Sno,Sname
From IS_Student,SC
Where IS_Student.Sno=SC.Sno AND SC.Cno = '1';

查询视图和基于派生表的查询的区别:
视图定义后永久存在数据字典中,之后所有查询都可以直接引用该视图,而派生表只在语句中
临时定义,语句执行后该定义即被删除;

更新视图:
Update IS_Student
Set Sname='刘辰'
Where Sno = '201215122';

Insert into IS_Student
Values('201215122','小明',20);

Delete From IS_Student
Where Sno='201215122';