数据库期末复习 SQL

一、QUERY

1.1 单表查询

SELECT [ALL|DISTINCT] <目标列表达式> [，<目标列表达式>] …
FROM <表名或视图名>[， <表名或视图名> ] …
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ]；

1.1.1 SELECT

1. 可以对表头重定义 AS
2. LOWER()用小写表示
3. SELECT * 表示查询全部信息
4. 可以用DISTINCT去重，默认保留重复行
5. SELECT 2020-birth_year AS age
6. SELECT ‘test’ AS Test

1.1.2 WHERE

1. =, <>, <, >, <=, >=, between, in
2. between 10 and 12(包括10和12)
3. in (‘Mary’,‘Jack’)
4. 字符匹配 like ‘李%’ (%匹配任意字符串，_匹配任意字符)
5. ESCAPE换码
6. IS NULL/IS NOT NULL 判断空值
7. 多重条件用AND/OR
8. WHERE中不能使用聚集函数

1.1.3 ORDER BY

1. 默认升序ASC
2. 降序DESC
3. 可以根据多属性列

1.2 分组和聚集

If any aggregation is used, then each element of the SELECT list must be either:

1. Aggregated, or
2. An attribute on the GROUP BY list.

如果查询中使用了聚集函数，所有元素必须要么是聚集函数，要么是分组属性。

1.2.1 GROUP BY

1. 按指定属性列的值进行分组
2. 可添加HAVING子句进行筛选

1.2.2 HAVING

They may refer to attributes of those relations, as long as the attribute makes sense within a group; i.e., it is either:

1. A grouping attribute, or
2. Aggregated.

1.2.3 聚集函数

计数
COUNT（[DISTINCT|ALL] *）
COUNT（[DISTINCT|ALL] <列名>）
计算总和
SUM（[DISTINCT|ALL] <列名>）
计算平均值
AVG（[DISTINCT|ALL] <列名>）
求最大值
MAX（[DISTINCT|ALL] <列名>）
求最小值
MIN（[DISTINCT|ALL] <列名>）

1. ALL短语：不取消重复值
2. ALL为缺省值
3. 除了COUNT，其余聚集函数都是对一个属性操作
4. DISTINCT短语：在计算时要取消指定列中的重复值

1.3 三值逻辑

1. TRUE = 1, FALSE = 0, and UNKNOWN = ½
2. AND = MIN; OR = MAX, NOT(x) = 1-x

1.4 多表查询

1.4.1 内连接

SELECT <属性或表达式列表>
FROM <表名> [INNER] JOIN <表名>
ON <连接条件>
[WHERE <限定条件>]

1.4.2 外连接

1. Left outer join
2. Right outer join
3. Full outer join

1.5 集合查询

并UNION 交INTERSECT 差EXCEPT

1.6 嵌套查询

1.6.1 带IN的子查询

1.6.2 带EXISTS的子查询

1. EXISTS常用于表现全称量词
   

1.6.3 带比较运算符的子查询

1.7 基于派生表的查询

在FROM中使用子查询

二、DDL

2.1 表格table

2.2.1 创建表

2.2.2 更新表

1. 添加属性
   ALTER TABLE < name >
   ADD < attribute declaration >;
2. 删除属性
   ALTER TABLE < name >
   DROP < attribute >;

2.2.3 删除表

DROP TABLE <表名> [RESTRICT | CASCADE];

2.2 视图view

2.2.1 创建

CREATE VIEW < name > AS < query >;

2.2.2 使用

1. 插入FROM后使用
2. 添加with check option 保持更新

2.2.3 删除

DROP VIEW <视图名> [CASCADE]；

2.3 索引index

索引虽然能加速数据库查询，但是需要占用一定的空间，当基本表更新时，索引要进行相应的维护，这些都会增加数据库的负担，因此要根据实际应用的需要有选择地创建索引。

2.3.1 创建

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

1. 对于已含重复值的属性列不能建UNIQUE索引
2. 对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束
3. 缺省值：ASC
4. 一个基本表只能建立一个聚簇索引

2.3.2 使用

1. DBMS自动选择是否使用索引以及使用哪些索引
2. 修改 ALTER INDEX <旧索引名> RENAME TO <新索引名>;

2.3.3 删除

DROP INDEX <索引名> ;

三、DML

3.1 Insert

1. INSERT INTO < relation > VALUES ( < list of values > );

2. INSERT INTO < relation > ( < subquery > );
   

3.2 Delete

DELETE FROM < relation > WHERE < condition >;

3.3 Update

UPDATE < relation >
SET < list of attribute assignments >
WHERE < condition on tuples >;

四、DCL

4.1 用户权限

1. 授予权限
   GRANT { ALL [ PRIVILEGES ] | 权限 [ ,…n ] }
   { [ ( 列名 [ ,…n ] ) ] ON { 表名 | 视图名 }
   | ON { 表名 | 视图名 } [ ( 列名 [ ,…n ] ) ]
   | ON { 存储过程} } TO {用户 | 角色} [ ,…n ]
   [ WITH GRANT OPTION ]
   [ AS { 角色 } ]
2. 收回权限
   REVOKE [ GRANT OPTION FOR ]
   { [ ALL [ PRIVILEGES ] ] | permission [ ( column [ ,…n ] ) ] [ ,…n ] }
   [ ON [ class :: ] securable ]
   { TO | FROM } principal [ ,…n ] [ CASCADE]
   [ AS principal ]
   Cascade选项：将由该用户散播出去的指定权限一并收回
3. 拒绝权限
   DENY { ALL [ PRIVILEGES ] }
   | permission [ ( column [ ,…n ] ) ] [ ,…n ]
   [ ON [ class :: ] securable ]
   TO principal [ ,…n ]
   [ CASCADE]
   [ AS principal ]

五、约束

5.1 键值约束

1. Primary Key:
   CREATE TABLE branch(
   bname CHAR(15) PRIMARY KEY,
   bcity CHAR(20),
   assets INT);
   or
   CREATE TABLE depositor(
   cname CHAR(15),
   acct_no CHAR(5),
   PRIMARY KEY(cname, acct_no));
2. Candidate Keys:
   CREATE TABLE customer (
   ssn CHAR(9) PRIMARY KEY,
   cname CHAR(15),
   address CHAR(30),
   city CHAR(10),
   UNIQUE (cname, address, city);
3. UNIQUE允许空值，PRIMARY KEY不允许

5.2 外键(参照)约束

1. 模版：
   FOREIGN KEY ( )
   REFERENCES ( )
   ON [UPDATE, DELETE] [SET NULL/CASCADE]

2. 例子：
   –CREATE TABLE Beers (
   name CHAR(20) PRIMARY KEY,
   manf CHAR(20) );
   –CREATE TABLE Sells (
   bar CHAR(20),
   beer CHAR(20) REFERENCES Beers(name),
   price REAL );

3. 联动
   1）Default : Reject the modification.
   2）Cascade : Make the same changes in Sells.
   –Deleted beer: delete Sells tuple.
   –Updated beer: change value in Sells.
   3）Set NULL : Change the beer to NULL.

5.3 属性约束

1. NOT NULL
   e.g.: CREATE TABLE branch(
   bname CHAR(15) NOT NULL,
   …
   )
   Note: declaring bname as primary key also prevents null values

2. CHECK
   e.g.1: CREATE TABLE depositor(
   …
   balance int NOT NULL,
   CHECK( balance >= 0),
   …
   )
   affect insertions, update in affected columns
   e.g.2:
   CREATE TABLE Sells (
   bar CHAR(20),
   beer CHAR(20) CHECK ( beer IN
   (SELECT name FROM Beers)),
   price REAL CHECK ( price <= 5.00 )
   );

5.4 断言

1. 模版
   CREATE ASSERTION
   CHECK ( );

2. 例子
   CREATE ASSERTION FewBar CHECK (
   (SELECT COUNT() FROM Bars) <=
   (SELECT COUNT() FROM Drinkers)
   );

六、触发器

Another name for “trigger” is ECA rule, or event-condition-action rule.
Event : typically a type of database modification, e.g., “insert on Sells.”
Condition : Any SQL boolean-valued expression.
Action : Any SQL statements.
1.

2.

3. 只有表的拥有者才可以在表上创建触发器
4. 触发器不能建在视图上
5. 删除
   DROP TRIGGER <触发器名> ON <表名>；