SQL

SQL 的数据定义语言 (DDL) 部分使我们有能力创建或删除表格。我们也可以定义索引（键），规定表之间的链接，以及施加表间的约束。

SQL 中最重要的 DDL 语句:

CREATE DATABASE - 创建新数据库ALTER DATABASE - 修改数据库CREATE TABLE - 创建新表ALTER TABLE - 变更（改变）数据库表DROP TABLE - 删除表CREATE INDEX - 创建索引（搜索键）DROP INDEX - 删除索引

查询和更新指令构成了 SQL 的 DML 部分：

SELECT - 从数据库表中获取数据UPDATE - 更新数据库表中的数据DELETE - 从数据库表中删除数据INSERT INTO - 向数据库表中插入数据

DML数据操作语言

可以把 SQL 分为两个部分：数据操作语言 (DML) 和 数据定义语言 (DDL)

SQL是非过程化语言。保留字不能分行写。

SELECT语句的处理顺序：FROM，WHERE,GROUP BY, HAVING,SELECT ，ORDER BY

**SELECT部分：**DISTINCT 去掉重复的，SELECT的计算，别名

WHERE部分：比较/计算，AND，OR，NOT（NOT>AND>OR）,BETWEEN AND（包括端点）,NOT BETWEEN　AND,NOT IN,IN,LIKE(模式匹配:’%’–任意个字符,’_’----单个字符,),IS NULL,IS NOT NULL(不能用’ '代替)

**ORDER BY部分:**ASC是升序,DESC是降序,直接加在列名后面.默认是升序.还可以多列排序,直接用逗号连接.

聚集函数

AVG(xx)计算平均数,COUNT(xxx)计算数据个数,SUM返回数据总和,MIN,MAX.

聚集函数只能用于SELECT列表和HAVING子句中,如果SELECT列表包括聚集函数,却没有使用GROUP BY子句分组,那么SELECT列表的任何项都不能引用列,除了作为聚集函数的参数.

COUNT(DISTINCT date)去掉重复项 SELECT dept_name,AVG(salary) AS avg_salary FROM instructor GROUP BY dept_name-----------计算系中老师的平均工资并显示 SELECT COUNT(DISTINC propertyNo) AS A FROM nn WHERE ... GROUP BY xx----先分成一个组方便计算 先排序再选择

GROUP BY部分

SELECT子句中的所有列除非用在聚集函数中,否则必须在GROUP BY子句中出现.反之,GROUP BY子句中出现的项不一定出现在SELECT子句中.

HAVING 部分

HAVING子句使用的列名必须出现在GROUP BY子句列表中,或包括在聚集函数中.

任何使用HAVING子句的查询都可以用不带HAVING子句的语句重写.

子查询

子查询可以使用在WHERE,HAVING子句和INSERT\UPDATE\DELETE语句中,子查询三种类型:标量子查询(返回单个值),行子查询(返回多个列,但只有单个行),表子查询(返回多个行,每行有一个或多个列)

SELECT staffNo,position,salary-(SELECT AVG(salary) FROM Staff)AS salDiff FROM staff WHERE salary> (SELECT AVG(salary) FROM Staff)

子查询应遵循规则:

(1)ORDER BY子句不能用于子查询;

(2)子查询SELECT列表必须由单个列名或表达式组成,除非使用EXITS

(3)子查询必须出现在比较式的右边

ANY和ALL

ANY/SOME 只要有一个值满足条件就为真;ALL 所有值都满足条件时才为真.子查询为空时,ALL为真,ANY为假

NOT EXISTS/EXISTS

当且仅当子查询返回结果至少存在一行是EXISTS返回真,NOT EXISTS相反.

引出子查询的运算符:

(1)比较运算符

(2)带有in/not in

(3)some / any/all

分类方式(一):单值子查询/列表子查询

分类方式(二):相关子查询[子查询的where字句中引用了外部查询]/不相关子查询

不相关子查询

SELECT * FROM staff S1 WHERE salary> SELECT AVG(salary) FROM Staff S2 WHERE S1.salary=S2.salary

多表查询

简单连接

SELECT c.clientNO,fName,IName,comment FROM Client c,Viewing v WHERE c.clientNo=v.clientNo --------其他方式 FROM Client c JOIN Viewing v ON c.clientNo=v.clientNO FROM Client JOIN Viewing USING clientNO FROM Client NATURAL JOIN Viewing

排序连接

SELECT c.clientNO,fName,IName,comment FROM Client c,Viewing v, WHERE c.clientNo=v.clientNo ORDER BY c.clientNo,v.comment

三表连接

按多个列分组

内连接

如果表中某一行不匹配另一表的任何行,那么这行将从表中删除.

左外连接

除了内连接的行,第一个表中与第二个表无匹配行的那些行也会显示.

SELECT b.*,p.* FROM Branch b LEFT JOIN PropertyRorRent p ON b.bcity=p.pcity ----Branch的不匹配的信息也会显示,关于PropertyForRent就填NULL

右外连接

全外连接

两边表不匹配的信息也会显示

SELECT b.*,p.* FROM Branch b FULL JOIN PropertyRorRent p ON b.bcity=p.pcity

合并结果表

并UNION,交INTERSECT,差EXCEPT

用于集合操作的表有一些限制,最重要的一点是两个表具有并相容性—两个表具有相同数目的列,且对应 的列具有相同的数据类型和长度.

(SELECT city FROM Branch WHERE city IS NOT NULL) UNION (SELECT city FROM PropertyForRent WHERE city IS NOT NULL ) 或者 (SELECT * FROM Branch WHERE city IS NOT NULL) UNION CORRESPONDING BY city (SELECT * FROM PropertyForRent WHERE city IS NOT NULL ) (SELECT city FROM Branch WHERE city IS NOT NULL) INTERSECT (SELECT city FROM PropertyForRent WHERE city IS NOT NULL ) 或者 (SELECT * FROM Branch WHERE city IS NOT NULL) INTERSECT CORRESPONDING BY city (SELECT * FROM PropertyForRent WHERE city IS NOT NULL ) (SELECT city FROM Branch WHERE city IS NOT NULL) EXCEPT (SELECT city FROM PropertyForRent WHERE city IS NOT NULL ) 或者 (SELECT * FROM Branch WHERE city IS NOT NULL) EXCEPT CORRESPONDING BY city (SELECT * FROM PropertyForRent WHERE city IS NOT NULL )

数据库更新

INSERT:向表中添加新的行

INSERT INTO 语句可以有两种编写形式。 第一种形式无需指定要插入数据的列名，只需提供被插入的值即可： INSERT INTO table_name VALUES (value1,value2,value3,...); 第二种形式需要指定列名及被插入的值： INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);

UPDATE:修改表中现有的行

UPDATE table_name SET column1=value1,column2=value2,... WHERE some_column=some_value;

DELETE：删除表中已有的行

DELETE FROM table_name WHERE some_column=some_value;

ALTER TABLE 语句

ALTER TABLE table_name ALTER COLUMN column_name datatype

DDL数据定义语言

完整性增强特性:必须有值的数据,域约束,实体完整性,引用完整性,一般性约束.

(1)必须有值的数据

某些列的值必须是有效值,不允许为空.

(2)域约束

合法值的集合.定义域两种方式:CHECK子句.或者CREATE DOMAIN语句

CHECK后面可以加SELECT查询

CREATE DOMAIN SexType AS CHAR DEFAULT 'M' CHECK（VALUE IN（‘M’，‘F’））; -------------- sex CHAR NOT NULL CHECK(sex IN('M','F'))

(3)实体完整性

PRIMARY KEY()----主键,每个表中只能使用一个PRIMARY key子句,但是可以用UNIQUE保证列的唯一性

(4)引用完整性

FOREIGN KEY(branchNO) REFERENCES Branch;----外部关键字 ------------- CASCADE删除父表中的行并且自动删除子表中匹配的行 SET NULL删除父表中的元组且设置子表中的外部关键字为NULL SET　DEFAULT删除父表中的元组且设置子表中的外部关键字为默认值 NO　ACTION拒绝对父表进行删除操作

(5)一般性约束.

和前面的check语句很像，作用范围更大

CREATE ASSERTION StaffPro CHECK(NOT EXISTS(SELECT satffNo FROME PropertyForRent GROUP BY staffNo HAVING COUNT(*)>100))

数据定义

CREATE TABLE - 创建新表

ALTER TABLE- 变更（改变）数据库表

DROP TABLE - 删除表

CREATE SCHEMA-架构名 建立架构

DROP SCHEMA+架构名 删除架构

ALTER SCHEMA 新架构名 改变架构

CREATE DOMAIN

DROP DOMAIN

ALTER DOMAIN

CREATE VIEW

DROP VIEW

ALTER VIEW

（1）创建数据库

SCHEMA是一组数据库对象的命名集合

DROP SCHEMA+架构名［ＲＥＳＴＲＩＣＴ｜ＣＡＳＣＡＤＥ］

ＲＥＳＴＲＩＣＴ　这个默认值的话，删除的模式必须是空的

（２）创建表

CREATE　TABLE　tablename columnName dataType[Not NULL][UNIQIUE][DEFAULT value][CHEACK...] ------ ALTER TABLE tablename set columnName dataType[Not NULL][UNIQIUE][DEFAULT value][CHEACK...] ALTER columnName dataType[Not NULL][UNIQIUE][DEFAULT value][CHEACK...] ---------- CREATE TABLE a( arr1 int, arr2 char(14), arr3 date, hours int default 8 CHECK(hours>=0) Primary key(arr1), foreign key (arr1) references B(arr1), ... )

(3)创建索引

和排序差不多

（3）创建视图

CREATE VIEW 视图名{（列1，列2，列3......）} AS SELECT s.列1，s.列2,t.列3...... FROM 表1 s,表2 t WHERE s.sNO=t.tNO GROUP BY s.列1,t.列3 ----删除视图--- DROP VIEW 视图名 [RESTRICT|CASCADE] -------视图分解---- SELECT staffNo,cnt FROM StaffView WHERE branchNo='B003' ORDER staffNo ------视图插入/更新--- INSERT INTO 视图名 VALUES（值1，值2......）; UPDATE Manager3Staff SET branchNo='Boo5' WHERE staffNo='SG34'; ------WITH CHECK OPTION---- CREATE VIEW 视图名{（列1，列2，列3......）} AS SELECT s.列1，s.列2,t.列3...... FROM 表1 s,表2 t WHERE s.sNO=t.tNO WITH CHECK OPTION;

水平视图：水平视图限制用户只能访问一个或多个表中选定的元组。

垂直视图：垂直视图限制用户只能访问一个或多个表中选定的列。

**视图的局限性：**如果视图中某个列是基于聚集函数的，那么在访问该视图的查询语句中，该列只能出现在SELECT和ORDER BY子句里，也不能作为任何聚集函数的参数。

可更新视图：为了使视图可更新，对于任何一个行或列，DBMS必须能追溯到其源表中相应的行或列。

可更新性：

1)没有指定DISTINCT，即重复元组未从查询结果中消除

2）定义查询的SELECT列表中的每个元素均为列名，且列名出现的次数不多于一次

3）FROM只指定一个表

4）WHERE子句不能包括任何引用了FROM子句中的表的嵌套SELECT操作

5）定义查询中不能有GROUP BY或HAVING子句

优点:

数据独立性;实时性

（4）创建角色

CREATE ROLE manager ----授权 GRANT SELECT，UPDATE...（权限名字） ON table TO 角色 WITH GRANT OPTION ---授予全部权限---- CREATE ROLE Manager; CREATE ROLE Director; GRANT ALL PRIVILEGES ON test01,test02 TO Manager,Director WITH GRANT OPTION;---可以传递这些权限给其他用户 -----授予特定权限--- GRANT SELECT,UPDATE ON test01,test02 TO Manager,Director -----授予所有用户--- GRANT SELECT,UPDATE ON test01,test02 TO PUBLIC; ------撤销用户权限--- REVOKE SELECT,UPDATE ON staffView FROM Manager;