db_3 SQL关系数据库标准语言

SQL概述

特点

综合统一
高度非过程化
面向集合的操作方式
以同一种语法结构提供两种使用方式
语言简捷，易学易用

数据查询 SELECT
数据定义 CREATE，DROP，ALTER
数据操纵 INSERT，UPDATE，DELETE
数据控制 GRANT，REVOK

基本概念

基本表和导出表都是关系
基本表
- 实际存在的关系:基本表=1:1
- 基本表:存储文件=n:1/1:1
- 表:索引=1:n 索引在存储文件
导出表
- 视图
  - 虚表
  - 视图所对应的数据不实际存储在数据库中
  - 数据库的数据字典中存储视图的定义
  - 视图一经定义就可以和基本表一样进行查询等操纵
  - 视图可以用来定义新的视图
- 快照
关系数据库的三级模式结构

学生-课程数据库

学生表:Student(Sno,Sname,Ssex,Sage,Sdept)

课程表:Course(Cno,Cname,Cpno,Ccredit)

学生选课表:SC(Sno,Cno,Grade)

数据定义

操作对象	创建	删除	修改
模式	CREATE SCHEMA	DROP SCHEMA
表	CREATE TABLE	DROP TABLE	ALTER TABLE
视图	CREATE VIEW	DROP VIEW
索引	CREATE INDEX	DROP INDEX	ALTER INDEX

模式

定义

1 2	CREATE SCHEMA <模式名> AUTHORIZATION <用户名> [<表定义子句>\|<视图定义子句>\|<授权定义子句>];

没有<模式名>隐含为<用户名>

删除

1	DROP SCHEMA<模式名><CASCADE\|RESTRICT>;

CASCADE级联模式下的所有数据库对象删除
RESTRICT限制只有模式下每下属对象才能执行
二选一

基本表

定义

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

完整性约束：

NULL/NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY： Foreign key (S#) references S (S#)
CHECK： Check (SA >=18 and SA <=45)

数据类型：

数据类型	含义
CHAR(n), CHARACTER(n)	长度为 n 的定长字符串
VARCHAR(n), CHARACTER VARYING(n)	最大长度为 n 的变长字符串
CLOB	字符串大对象
BLOB	二进制大对象
INT, INTEGER	长整数（4 字节）
SMALLINT	短整数（2 字节）
BIGINT	大整数（8 字节）
NUMERIC(p, d)	定点数，由 p 位数字（不包括符号、小数点）组成，小数点后面有 d 位数字
DECIMAL(p, d), DEC(p, d)	同 NUMERIC
REAL	取决于机器精度的单精度浮点数
DOUBLE PRECISION	取决于机器精度的双精度浮点数
FLOAT(n)	可选精度的浮点数，精度至少为 n 位数字
BOOLEAN	逻辑布尔量
DATE	日期，包含年、月、日，格式为 YYYY-MM-DD
TIME	时间，包含一日的时、分、秒，格式为 HH:MM:SS
TIMESTAMP	时间戳类型
INTERVAL	时间间隔类型

模式与表

在表名中明显地给出模式名
- CREATE TABLE “S-T”.Student(…);
在创建模式语句中同时创建表
设置所属的模式
- SET search_path TO “S-T”,PUBLIC

修改

Alter Table <表名>
            [Add  <新列名><数据类型>[<完整性约束>]]
            [Drop <完整性约束名>]
            [Modify  <列名><数据类型>];
            
书上是：
ALTER TABLE <表名>
            [ADD [COLUMN] <新列名> <数据类型> [完整性约束]]
            [ADD  <表级完整性约束>]
            [ADD CONSTRAINT <完整性约束名> <完整性约束>]？？
            [DROP [COLUMN] <列名><CASCADE|RESTRICT> ]
            [DROP CONSTRAINT <完整性约束名><CASCADE|RESTRICT>]
            [ALTER COLUMN <列名> <数据类型> ];

删除

1
2
3

DROP TABLE<表名>
书上是：
DROP TABLE<表名>[RESTRICT|CASCADE];

RESTRICT:

不能有check/foreign key约束
不能视图触发器函数…..

索引

定义

1 2	CREATE[UNIQUE][CLUSTER]INDEX<索引名> ON<表名>(<列名>[<次序>][，<列名>[<次序>]]…);

次序
- ASC（默认）升序
- DESC 降序
UNIQUE
- 此索引的每一个索引值只对应唯一的数据记录
CLUSTER
- 建立的索引是聚簇索引

例子：CREATE UNIOUE INDEX SCno ON SC(Sno ASC,Cno DESC)

修改

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

删除

1	DROP INDEX <索引名>;

同时从数据字典中删去有关该索引的描述

数据字典

数据字典是关系数据库管理系统内部的一组系统表，它记录了数据库中所有的定义信息包括关系模式定义、视图定义、索引定义、完整性约束定义、各类用户对数据库的操作权限统计信息等。

关系数据库管理系统在执行SQL的数据定义语句时，实际上就是在更新数据字典表中的相应信息。在进行查询优化和查询处理时，数据字典中的信息是其重要依据。

数据查询

基本格式

SELECT [ALL|DSTINCT] <目标列表达式>[，目标列表达式>]……
FROM <表名或视图名>[,<表名或视图名>……]|(<SELECT 语句>) [AS]<别名>
[WHERE<条件表达式>]
[GROUPBY<列名1>[HAVING<条件表达式>]]
[ORDER BY<列名2>[ASC|DESC]];

单表查询

选取表的若干列
- 查询指定列：<目标列表达式>指定查询的属性列
- 查询全部列：<目标列表达式>为*(列的顺序相同)
- 查询计算的值：<目标列表达式>为算术表达式此时列名也会变成这个表达式
- <目标列表达式>为常数，常数填充这一列！此时列名也会变成这个常数
- 以上两种列名可以通过指定别名的形式改变
  1
  2
  SELECT Sname NAME, "Year of Birth:" BIRTH, 2014-Sage BIRTHDAY, LOWER(Sdept) DEPARTMENT
  FROM Student;
选择表的若干元组
- 消除取值重复的行:DISTINCT
- 查询满足条件的行：WHERE子句
  
  | 查询条件 | 谓词 | | —————————— | ———————————————————————- | | 比较 | =, >, <, >=, <=, !=, <>, !=; NOT+上述比较运算符 | | 确定范围 | BETWEEN AND, NOT BETWEEN AND | | 确定集合 | IN, NOT IN | | 字符匹配 | LIKE, NOT LIKE | | 空值 | IS NULL, IS NOT NULL | | 多重条件（逻辑运算） | AND, OR, NOT |
  - 确定集合：IN/NOT IN
    1
    2
    3
    SELECT Sname,Ssex
    FROM Student
    WHERE Sdept IN ('CS',MA','IS');
  - 字符匹配
    1
    [NOT]LIKE'<匹配串>'[ESCAPE'<换码字符>']
    - %表示任意长度（可以为0）的字符串
    - _表示任意长度为1的字符
    - ESCAPE 是因为匹配串内有%或者_ 表示转义
      1
      2
      'DB\_%i_ _' ESCAPE '\'
      就是DB_?*i??
  - 涉及空值
    - IS NULL 不能写成 = NULL！
  - 多重条件查询
    - AND优先级高于OR
ORDER BY子句
- ORDER BY<列名2>[ASC|DESC]
- 空值由具体系统实现决定次序
聚集函数
- 只能用于SELECT子句和GROUP BY的HAVING
- 不能用于WHERE子句
  
  | 函数 | 描述 | | —————————————————- | ——————————————————— | | COUNT(*) | 统计元组个数 | | COUNT([DISTINCT|ALL] <列名>) | 统计一列中值的个数 | | SUM([DISTINCT|ALL] <列名>) | 计算一列值的总和（此列必须是数值型） | | AVG([DISTINCT|ALL] <列名>) | 计算一列值的平均值（此列必须是数值型） | | MAX([DISTINCT|ALL] <列名>) | 求一列值中的最大值 | | MIN([DISTINCT|ALL] <列名>) | 求一列值中的最小值 |

GROUP子句
- 目的：细化聚集函数的作用对象
- 分组后聚集函数将作用于每一个组，即每一组都有一个函数值
- HAVING 短语作用于组，选择满足条件的组
- WHERE子句作用于行，选择满足条件的行
  - 不能用聚集函数！

连接查询

等值连接、非等值连接
- WHERE子句中连接条件/连接谓词
  1
  2
  [<表名 1>.]<列名 1><比较运算符>[<表名 2>.]<列名2>
  其中比较运算符主要有=、><=、!=(或<>)等
- =就是等值
- 去掉重复属性列就是自然连接（不会自己去掉的…）
- WHERE子句的选择谓词还可以同时完成选择和连接

自身连接

通过定义别名，将一个表看成两个表，进行连接

-- 检索所有比李勇年龄大的学生姓名、年龄。
SELECT  X.SN，X.SA
FROM   S  X，S  Y
WHERE X.SA>Y.SA AND Y.SN='李勇';

外连接
- 悬浮元组上一章！NULL
- */+所在边的表增加了一个空行
- *在右左外连接
  
  它可以与另一个表中所有不满足连接条件的元组进行连接，使这些元组能够输出。
  
  例子：s的s#种类比sc的s#多
  1
  2
  3
  4
  -- 检索所有学生的全部信息。
  SELECT S.Sno, SN,SA,SD, Cno,G
  FROM S,SC
  WHERE S.Sno=SC.Sno(*);
多表连接（2个表以上）

嵌套查询

子查询SELECT语句不能用ORDER BY
ORDER BY只能对最终查询结果排序

普通子查询：与外部查询无关，可单独执行得一组值。
相关子查询：把外查询的列值作为检索条件的条件

-- 找出每个学生超过他自己选修课程平均成绩的课程号
SELECT x.Sno, x.Cno
FROM SC x
WHERE x.Grade >= (
    SELECT AVG(y.Grade)
    FROM SC y
    WHERE y.Sno = x.Sno
);

带IN谓词的子查询

带比较运算符的子查询
- 子查询返回单值
带ANY（SOME）、ALL谓词的子查询
- 子查询返回多值
带EXISTS谓词的子查询
- WHERE只返回逻辑真值true和逻辑假值false
- EXISTS 子查询结果为非空 true
- NOT EXISTS 子查询结果为空 true
- 全称量词 $\forall x P \equiv \neg (\exists x \neg P)$
- 蕴含
  
  $(\forall y) (p \rightarrow q) \equiv \neg (\exists y (\neg (p \rightarrow q)))\\ \equiv \neg (\exists y (\neg p \lor q)) \equiv \neg (\exists y (p \land \neg q))$

集合查询

操作对象必须是相容的，是同类关系

必须有相同数量的属性列，且相应属性列的域也必须相同

UNION 并
- UNION会自动DISTINCT
INTERSECT 交
EXCEPT 差

基于派生表查询

子查询出现在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

Insert
Into <表名>[(<属性列>[{,<属性列>}])]
Values(<值>[{,<值>}]);

没列举的属性名会赋NULL

插入子查询结果：

1
2
3

Insert
Into <表名>[(<属性列>[{,<属性列>}])]
子查询;

修改数据

1
2
3

UPDATE<表名>
SET<列名>=<表达式>[,<列名>=<表达式>]…
[WHERE<条件>];

删除数据

1
2
3

DELETE
FROM<表名>
[WHERE<条件>];

删除的是表中的数据，而不是关于表的定义
省略WHERE 子句则表示删除表中全部元组，但表的定义仍在字典中

空值处理

判断
- IS NULL / IS NOT NULL
约束条件
- NOT NULL
- UNIQUE
- 码/主属性
算术运算
- NULL + ？ = NULL
比较运算
- NULL > ? -> UNKOWN
逻辑运算

视图

定义视图

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

子查询可以是任意的SELECT语句
WITH CHECK OPTION
- 对视图进行CURD操作时满足视图定义中的谓词条件（子查询中的条件表达式”where…”）
视图可以建立在视图之上

行列子集视图

从单个基本表导出的
去掉了基本表的某些行和某些列
保留了主码

带表达式的视图

派生属性并不在基本表中实际储存（虚拟列）

分组视图

带有聚集函数和GROUP BY子句的查询定义的视图

删除视图

1	DROP VIEW<视图名>[CASCADE];

CASCADE 将该视图导出的视图一起删除

查询视图

视图消解

从数据字典中取出视图的定义

把定义中的子查询和用户的查询结合起来

转换成等价的对基本表的查询，然后再执行修正的查询

作用

简化用户操作
用户以多种角度看待同一数据
提供了一定程度的逻辑独立性
能够对数据提供安全保护
适当利用视图更清晰表达

数据控制

定义完整性约束条件
支持事务操作
提供安全控制功能
- 授权
  
  GRANT 〈权限〉[ON 〈对象类型〉〈对象名〉]
  
  TO 〈用户〉
- 收回权限
  
  REVOKE 〈权限〉[ON 〈对象类型〉〈对象名〉]
  
  FROM 〈用户〉