1 数据库基本对象DDL

（数据库定义语言）

sql 不提供修改模式定义和修改视图定义的操作。如果想要更改，智能删除重建。
数据库中操作的基本对象主要有以下内容：模式、表、视图、索引

1.1 基本对象概述

主要包括增删改。没有针对基本对象的查询。就像你会查询表中的数据，但是不会查询存在的某一张表。查询表不存在，应该是查询表中的数据。

操作对象 创建 删除 修改
模式SCHEMA CREATE SCHEMA DROP SCHEMA
表TABLE CREATE TABLE DROP TABLE ALTER TABLE
视图VIEW CREATE VIEW DROP VIEW
索引INDEX CREATE INDEX DROP INDEX ALTER INDEX
字段COLUMN ADD COLUMN DROP COLUMN ALTER COLUMN 
1
2
3
4
5
graph LR
模式Schema-->表Table
模式Schema-->视图View
表Table-->索引Index
表Table-->字段Colume
  • 基本对象的创建、删除、修改。

其中表的修改包括对字段的增减、删除、修改等。其实我觉得这里也可以把字段看做数据库的基本对象，也有增删查改。基本对象和数据对象是相互独立的。字段作为基本对象的时候，必须指明属于哪一张表。

1.2 模式SCHEMA

创建

1
2
3
```

### 删除
1
2
3
4

## 1.3 表TABLE

### 创建
1
2

### 删除
1
2

### 修改
1
2
3
4
5
6
7
8
9
10
11
12
13
14

## 1.4 视图VIEW
### 定义
视图: view, 是一种不存在的虚拟表。视图有表结构。没有数据, 视图的数据来源都是基表。视图根据基表的数量分为两种：
* 单表视图: 基表只有一个
* 多表视图: 基表至少两个以


### 作用
1. 能够简化用户的操作。视图可以将复杂的SQL查询语句进行了封装，简化了SQL语句，从而提升了带宽的使用效率和网络间的传输效率
2. 主要是为了对外提供数据支持(外部系统)。隐藏基表字段(隐私)。保证了数据库的数据安全(保护数据库内部的数据结构)。可以灵活的控制对外的数据，保证针对每个接口都有一个单独的数据支持，增强了用户友好性.。
3. 视图利于权限控制: 有助于数据库对权限进行管理。

### 创建

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

1
2
3
4
5
6
7

视图创建的结果
* 会在对应的数据库的表空间中产生一个视图(表)
* 会在数据库对应存储文件夹下产生一个结构文件


1. 单基本表视图

行列子集视图-单个基本表视图。示例(建立信息系学生的视图)

CREATE VIEW IS_Student
AS SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept=’IS’
[WITH CHECK OPTION];

1
2
3
4
> 添加WITH CHECK OPTION ，对视图进行插入、修改和删除操作时，关系数据库管理系统会自动添加 满足视图条件的内容（Sdept='IS'）.

2. 多基本表视图

CREATE VIEW IS_S1(Sno,Sname,Grade)
AS SELECT Student.Sno,Sname,Grade
FROM Student,SC
WHERE Sdept=’IS’
AND Student.Sno=SC.Sno
AND SC.Cno=’1’;

1
2
3
> 视图可以建立在一个或多个已经定义好的基本表或者视图上。

3. 带虚拟列的视图（带表达式的视图）

添加了虚拟的计算列

CREATE VIEW BT_S(Sno,Sname,Sbirth)
AS SELECT Sno,Sname,2014-Sage
FROM Student;

使用了聚集函数

CREATE VIEW S_G(Sno,Gavg)
AS SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno;

1
2
3
> 总的来说，由SELECT语句进行查询形成的结果都能够构成一个单一的视图。

### 删除

DROP VIEW <视图名> [CASCADE]

DROP VIEW BT_S;

1
2
3
4
5
6
7
8
9
> 删除了已经建立的视图

### 视图中的数据对象操作
> 并非对视图内数据对象进行操作，而是对基本表中的数据对象进行操作。包括增删查改。

1. 查询数据

> 对视图的查询和对基本表的查询一致

SELECT Sno,Sage
FROM IS_Student
WHERE Sage<20;

1
2
3
4
5
6
7
8
9
10
11

> 查询数据的原理过程——
> 1. 视图检查：对视图进行查询时，首先先进行有效性检查，检查查询中设计的表、视图是否存在；
> 2. 视图消解：如果存在，则从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转化成等价的对基本表的查询。
> 3. 然后再执行修正了的查询。

> 视图与派生表的区别：视图一旦定义，将永久保存在数据字典中，之后的所有查询都可以直接引用该视图。派生表只是 在语句执行临时定义，语句执行后，该定义被删除。

2. 插入数据


插入

INSERT INTO IS_Student
VALUES(‘2015’,’zx’,20);

转化为

INSERT INTO Student(Sno,Sname,Sage,Sdept)
VALUES(‘2015’,’zx’,20,’IS’)

1
2
3

3. 删除数据

删除

DELETE FROM IS_Student
WHERE Sno=’2012’;

转化为

DELETE FROM Student
WHERE Sno=’2012’
AND Sdept=’IS’;

1
2
3

4. 修改数据（也可以更新更新）

修改

UPDATE IS_Student
SET Sname=’lc’
WHERE Sno=’2012’

转化为

UPDATE Student
SET Sname=’lc’
WHERE Sno=’2012’
AND Sdept=’IS’;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

5. 补充
> 对视图的更新操作，不一定能够完全对应为对基本表的操作。存在其他规定，来决定是否能够对基本表进行操作（DB2规定）
* 视图有两个以上的基本表导出的，不允许更新
* 视图中的字段有表达式或者常数，不允许INSERT,UPDATE,但允许DELETE。
* 视图中的字段来自聚集函数，不允许更新
* 视图定义中含有GROUP BY子句不允许更新
* 视图中含有DISTINCT 短语不允许更新
* 视图中有嵌套查询，不允许更新
* 一个不允许更新的视图上定义的视图也不允许更新。不允许更新和不可更新，不是同一个概念。

## 1.5 索引INDEX


### 索引类型

* 顺序文件上的索引：按指定属性升序或降序存储的关系。在属性上建立一个顺序索引文件，索引文件有属性值和响应的元组指针组成。
* B+树上的索引：B+树的叶节点为属性值和相应的元组指针。具有动态平衡的特点。
* 散列索引：建立若干个桶，将索引属性按照其散列函数值映射到相应的桶中，同种存放索引属性值和响应的元组指针。散列索引具有查找速度快的特点。
* 位图索引：用为向量记录索引属性中可能出现的值，每个为向量对应一个可能的值。
* tire索引/前缀索引：用来索引字符串。

> 特点：索引能够加快数据库查询，需要占用一定的存储空间。基本表更新后，索引也需要更新。用户不必显示地选择索引，关系数据库管理系统在执行查询时，会自动选择合适的索引作为存储路径。

### 创建

CREATE [UNIQUE][CLUSTER] INDEX <索引名>
ON <表名>（<列名>[<次序>],…）

实例

CREATE UNIQUE INDEX SCno ON SC(Sno,Cno DESC);

1
2
3

### 删除

DROP INDEX <索引名>;

实例

DROP INDEX <索引名>;

1
2

### 修改

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

实例

ALTER INDEX SCno RENAME TO SCSno

1
2
3
4
5
6
7

## 2 数据库数据对象操作DML
（数据库操作语言）

## 2.1 插入

1. 插入元组（一条或多条数据）

INSERT
INTO

[(属性,属性)]
VALUES (值,值);

示例

INSERT
INTO SC(Sno,Cno)
VALUES(‘12214’,2)

1
2
3

2. 插入子查询的结果

INSERT
INTO

[(属性,属性)]
子查询

示例

INSERT INTO Dept_age(Sdept,Avg_age)
SELECT Sdept,AVG(Sage)
FROM Student
GROUP BY Sdept;

1
2
3
4
5
6


## 2.2 删除

> 这里的删除是针对数据库的数据对象而言，并非基本对象，也就是说，只删除表中的数据，表的结构并不会受到影响。

DELETE
FROM


[WHERE <条件>]

1
2
3

1. 删除某一个元组的值

DELETE
FROM Student
WHERE Sno=’20125128’

1
2
3

2. 删除多个元组

DELETE
FROM SC

1
2
3

3. 带子查询的删除语句

DELETE FROM SC
WHERE Sno In
(SELECT Sno
FROM Student
WHERE Sdept=’CS’)

1
2
3
4
## 2.3 修改

1. 修改某一个元组的值

UPDATE


SET <列名>=<表达式>
WHERE <条件>

1
2
3

2. 修改多个元组的值

UPDATA Student
SET Sage=Sage+1

1
2
3

3. 带子查询的修改语句

UPDATE SC
SET Grade = 0
WHERE Sno IN
(SELECT Sno
FROM Student
WHERE Sdept=’CS’)

1
2
3
4
5
6
7
8
9

## 3 数据库数据对象查询DQL
（数据库查询语言）

## 3.1 单表查询

### 1. 查询多列


指定列

SELECT sno,sname FROM Student;

全部列

SELECT * FROM Student;

计算列

SELECT Sname,2014-Sage FROM Student;

字符串列

SELECT Sname,’Year of Birth:’,2014-Sage FROM Student;

函数列

SELECT LOWER(Sdept) FROM Student;

列别名

SELECT Sname NAME,2014-Sage BIRTHDAY,LOWER(Sdept) DEPARTMENT FROM Student

1
2
3
4
5
6
7
8
9
10
11
12
13

### 2. 范围查询

|查询条件|谓词|
|-|-|
|1). 比较|=,<,>,>=,<=,!=,<>,!>,!<,NOT+|
|2). 确定范围|BETWEEN AND,NOT BETWEEN AND|
|3). 确定集合| IN,NOT IN|
|4). 字符匹配|LIKE，NOT LIKE|
|5). 空值   | IS NULL,IS NOT NULL|
|6). 多重条件|AND,OR,NOT|


取消重复元组

SELECT DISTINCT Sno FROM SC;

满足附加条件的元组-比较

SELECT Sname FROM Student WHERE Sdept=’CS’

确定范围

SELECT Sname,Sdept,Sage FROM Student
WHERE Sage NOT BETWEEN 20 AND 23

确定集合

SELECT Sname,Ssex FROM Student
WHERE Sdept IN (‘CS’,’MA’,’IS’);

字符串匹配

SELECT Sname,Sno FROM Student
ERE Sname LIKE ‘_阳%’

涉及空值的查询

SELECT Sno,Cno From SC
WHERE Grade IS NULL

多重条查询

SELECT Sname FROM Student
WHERE Sdept=’CS’ AND Sage<20

1
2
3
4
5
6
7

### 3. 查询排序

|关键字|含义|
|-|-|
|ASC |升序  |
|DESC|  降序|

ORDER BY 排序

SELECT Sno,Grade FROM SC
WHERE Cno = ‘3’
ORDER BY Grade DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
> 空值的排序根据具体的系统来决定。

### 4. 聚集函数

|聚集函数|功能|
|-|-|
|COUNT(*)|统计数据元组个数|
COUNT(DISTINCT/All 列)|统计一列中数值的个数|
SUM(DISTINCT/All 列)|求和|
AVG(DISTINCT/All 列)|求平均值|
MAX(DISTINCT/All 列)|求最大值|
MIN(DISTINCT/All 列)|求最小值|

> 聚集函数遇到空值是，出了COUNT(*)外，都跳过空值，处理非空值。

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

普通的分组查询

SELECT Cno,COUNT(Sno) FROM SC
GROUP BY Cno;

带有分组条件的分组查询

SELECT Sno FROM SC
GROUP BY Sno
HAVING COUNT(*)>3

1
2
3
4
5
6
7
8
9

> WHERE 子句作用于基本表或视图，HAVING短语作用于组，从中选择满足条件的组，也就是分组条件。

## 3.2 连接查询(多表查询)

> 本质上是将多个表通过一些条件拼接成一张表。

### 1. 等值与非等值连接查询

SELECT Student.,SC.
FROM Student, SC
WHERE Student.Sno=SC.Sno

1
2
3
4
5
6
7
> 嵌套循环连接查询的基本步骤：首先扫描第一个表的第一个元组，然后从头扫描SC，查找满足条件的元组就进行拼接。


### 2. 自身连接查询

> 为自身的表取两个不同的别名。

SELECT first.Cno,second.Cpno
FROM Course first,Course second
WHERE first.Cpno=second.Cno;

1
2
3
4
5

### 3. 外连接查询

> 与等值连接不同的地方在于，有一张主表，主表中所有的元组会保留。而在等值连接中，两张表的地位是等价的，一张表中没有被连接的元素就回清除。

SELECT student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUTER JOIN SC on(Student.Sno=SC.Sno);

1
2
3

### 4. 多表连接查询
> 涉及两个或两个以上的表进行连接。我个人来说，如果嵌套查询和连接查询都能够实现时，我更喜欢连接查询。

SELECT Student.Sno,Sname,Cname,Grade
FROM Student,SC,Cource
WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno

1
2
3
4
5
6
7
8
9
10
11
12

## 3.3 嵌套查询（子查询）

* 一个SELECT FROM WHERE语句成为一个查询快。将一个查询快嵌套在另一个查询块的WHERE子句或HAVING短语的条件中，称为嵌套查询。用户可以用多个简单查询构造成负责的查询，层层嵌套的结构化查询。
  * 子查询的查询条件不依赖于父查询，称为**不相关子查询**。
  * 子查询的查询条件依赖于父查询，称为**相关子查询**。
  * 相关子查询不能一次求得查询结果，内层查询依赖外层查询的值，必须在内层查询与外层查询之间反复求值。

> 有些嵌套查询可以用连接运算替代，有些事不能替代的。


### 1. 带有IN谓词的子查询

不相关子查询_选修了“信息系统”的学生姓名

SELECT Sno,Sname
FROM Student
WHERE Sno IN
(SELECT Sno
FROM SC
WHERE Cno IN
(SELECT Cno
FROM Course
WHERE Cname=’信息系统’
)
);

1
2
3

### 2. 带有比较运算的子查询

相关子查询_超过自己选修课程平均成绩的课程号

SELECT Sno,Cno
FROM SC x
WHERE Grade>=(SELECT AVG(Grade)
FROM SC y
WHERE y.Sno=x.Sno);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

### 3. 带有ANY、SOME、ALL谓词的子查询

> 谓词说明，都能使用聚集函数来表示。

谓词| 含义
|-|-|
`>ANY` |
`>ALL` |
`<ANY` |
`<ALL` |
`>=ANY` |
`>=ALL` |
`<=ANY` |
`<=ALL` |
`=ANY` |
`=ALL` |
`!=ANY` |
`!=ALL` |

1
2
3
4
5
6
7

### 4. 带有EXISTS谓词的子查询

> 代表存在量词,带有EXISTS谓词的子查询不反悔任何数据，只产生逻辑真或假，true&false。

> 一些带有EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换，但是所有带有IN谓词的、比较运算符、ANY和ALL谓词的子查询杜能用EXISTS谓词的子查询等价替换。

SELECT Sname
FROM Student
WHERE EXISTS
(SELSECT *
FROM SC
WHERE Sno=Student.Sno AND Cno=’1’);

等价于

SELECT Sname
FROM Student
WHERE Student.Sno IN
(SELECT SC.Sno
FROM SC
WHERE SC.Cno=’1’)

1
2
3
4
5
6
7
8
9


## 3.4 集合查询

> 因为查询结果是元组的集合，所以多个SELECT语句的结果可进行集合操作。集合操作包括UNION、INTERSECT、EXCEPT。

> 集合查询是对查询结果的集合进行操作，而多重查询条件是多个并列的限制条件。


查询结果集合的并集

SELECT * FROM Student
WHERE Sdpt=’CS’
UNION
SELECT * FROM Student
WHERE Sage<=19

查询结果集合的交集

SELECT * FROM Student
WHERE Sdept=’CS’
INTERSECT
SELECT * FROM Student
WHERE Sage<=19

查询结果的差集

SELECT * FROM Student
WHERE Sdept=’CS’
EXCEPT
SELECT * FROM Student
WHERE Sage<=19

1
2
3
4
5

## 3.5 基于派生表的查询

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

相关子查询改为基于派生表的查询

SELECT Sno,Cno
FROM SC,(SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno) AS AVG_sc(avg_sno,avg_grade)
WHERE SC.sno=avg_sc.avg_sno and SC.Grade >= AVG_sc.avg_grade

1
2
3
4
5
6
7
8
9
10
> 必须为派生关系指定一个别名。
其实在普通的嵌套子查询当中或者链接查询当中，就是两个或者多个表之间的操作，对一个表的查询结果，作为一张中间表进行使用。

## 4 空值处理

> 空值是不确定的、不知道的、不存在的或无意义的值

### 4.1 空值的产生
### 4.2 空值的判断

IS NULL //空值
IS NOT NULL// 非空值

### 4.3 空值的约束条件

* 如果在字段定义中使用了NOT NULL、Primary Key、UNIQUE，则该属性不能使用空值。

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

* 空值与另一个值的算术运算结果为空值。

* 空值与另外一个值的比较运算结果为UNKNOWN

* 空值与另外一个值的逻辑运算遵循逻辑运算的法则。如果需要考虑空值，则为UNKNOWN，如果不虚要考虑空值就能得到结果，则为已经得到的结果。