数据库系统-结构化查询语言
SQL数据定义
SQL基本数据类型
数值型
- INT/INTEGER:整数,取值范围取决于DBMS实现
- SMALLINT:整数,取值范围比INT小
- BIGINT:整数,取值范围比INT大
- NUMERIC(p,s)或DECIMAL(p,s):定点数,p位有效数字,小数点后s位
- FLOAT(n):浮点数,精度至少为n位数字
- REAL:同FLOAT,但精度由DBMS确定
- DOUBLE PRECISION:同FLOAT,但精度由DBMS确定,比REAL高
布尔型
- BOOLEAN:真值TRUE或FALSE
字符串型
- CHAR(n)/CHARACTER(n):定长字符串,长度为n
- VARCHAR(n)/CHARACTER VARYING(n):变长字符串,最大长度为n
- CLOB/CHARACTER LARGE OBJECT:超长字符串,长度超过VARCHAR(n)类型字符串的长度上限
二进制串型
- BINARY(n):定长二进制串,长度为n个字节
- VARBINARY(n):变长二进制串,最大长度为n个字节
- BLOB/BINARY LARGE OBJECT:超长二进制串,长度超过VARBINARY(n)类型二进制串的长度上限
日期时间型
- DATE:日期,格式YYYY-MM-DD
- TIME:时间,格式HH:MM:SS
- TIME WITH TIME ZONE:时间,包含时区
- TIMESTAMP:日期和时间,格式YYYY-MM-DD HH:MM:SS TIMESTAMP WITH TIME + ZONE:日期和时间,包含时区
时间区间型
- INTERVAL YEAR TO MONTH:时间区间,用年和月表示
- INERVAL DAY TO SECOND:时间区间,用日、时、分、秒表示
MySql基本数据类型
数值型
- INTEGER或INT:4字节
- SMALLINT:2字节
- TINYINT:1字节MEDIUMINT:3字节
- BIGINT:8字节
- DECIMAL(p,s):有效数字共p位,小数点后s位
- NUMERIC(p,s):同DECIMAL(p,s)
- FLOAT:单精度,4字节
- DOUBLE:双精度,8字节
- BIT(b):b位二进制数(1≤b≤64),(b+7)/8字节,输入时用b’101’表示5
日期时间型
- YEAR:年类型
存储:1字节整数YYYY
显示和输入格式:‘YYYY’
- DATE:日期型
存储:3字节整数YYYY×16×32+MM×32+DD
显示和输入格式:‘YYYY-MM-DD’
- TIME:时间型
存储:3字节整数DD×24×3600+HH×3600+MM×60+SS
显示和输入格式:‘HH:MM:SS’或’HHH:MM:SS’
- DATETIME:日期时间型(与时区无关)
存储:8字节整数,其中4字节存储整数YYYYMMDD,另外4字节存储整数HHMMSS
显示和输入格式:‘YYYY-MM-DD HH:MM:SS’
- TIMESTAMP:时间戳型(和UNIX时间戳相同,与时区相关)
存储:4字节整数,保存从1970-01-01 00:00:00 UTC以来的秒数
显示和输入格式:‘YYYY-MM-DDHH:MM:SS’
字符串型
- CHAR(n):定长字符串型,最多存储n个字符(n≤255)
存储空间固定
当字符串不足门个字符时,后面用空格补全
- VARCHAR(n):变长字符串型,最多存储n个字符(n≤65535)
存储空间根据字符串的实际字节长度L变化
- TEXT:文本型
TINYTEXT:L<2^8
TEXT:L<2^16
MEDIUMTEXT:L<2^24
LONGTEXT:L<2^32
集合型
- SET(值列表):集合型
例:SET(‘Mercury’,Venus’,‘Earth’,‘Mars’)集合型的值只能是值集合的子集,如’Mercury,Earth’值列表中最多包含64个不同的值
存储为二进制数,列表中每个值对应一个二进制位.值在子集中存在,则相应位置为1;否则,置为0.例如,'Mercury,Earth’存储为0101
占用空间大小取决于集合中元素的个数
语句
例:
CREATE TABLE Student(
Sno CHAR(6),
Sname VARCHAR(10) NOT NULL,
Ssex CHAR NOT NULL CHECK (Ssex IN ('M','F')),
Sage INT DEFAULT O CHECK (Sage >=0),
Sdept VARCHAR(20),
PRIMARY KEY (Sno)
);
CREATE TABLE SC(
Sno CHAR(6),
Cno CHAR(4),
Grade INT,
PRIMARY KEY (Sno,Cno),
FOREIGN KEY (Sno) REFERENCES Student (Sno)
);
创建关系模式
CREATE TABLE
声明主键
PRIMARY KEY
声明外键
FOREIGN KEY ... REFERENCES
因为SC中的外键{Sno}参照Student的主键{Sno},所以可以省略FOREIGN KEY子句中被参照关系Student中的被参照属性列表(Sno)
用户定义完整性约束
- 规定属性值非空:NOT NULL
- 规定属性值不重复:UNIQUE
- 定义属性的缺省值:DEFAULT 缺省值
- 规定属性值必须满足表达式给出的条件:CHECK(表达式)
删除关系
DROP TABLE 关系名1,关系名2,...,关系名n;
修改关系模式
ALTER TABLE
修改关系名
ALTER TABLE Student CHANGE 旧属性名 新属性名 属性定义;
修改属性定义
ALTER TABLE Student MODIFY 属性名 属性定义;
增加属性
ALTER TABLE Student ADD Mno CHAR(6);
删除属性
ALTER TABLE Student DROP Mno;
设置属性缺省值
ALTER TABLE Student ALTER Sage SET DEFAULT 18;
取消属性缺省值
ALTER TABLE Student ALTER Sname DROP DEFAULT;
增加表约束
ALTER TABLE Student
ADD CONSTRAINT fk_mno
FOREIGN KEY (Mno) REFERENCES Student(Sno);
删除表约束
ALTER TABLE Student DROP CONSTRAINT fk_mno;
视图
- 基本关系:关系数据库中真实存储的关系
- 视图:从用户的视角所看到的数据
- 功能:创建、修改、删除视图
创建视图
CREATE VIEW 视图名 [(属性名列表)] AS 子查询;
修改视图
ALTER VIEW 视图名 [(属性名列表)] AS 子查询;
删除视图
DROP VIEW 视图名;
SQL数据更新
插入数据
INSERT INTO 关系名 [(属性名1,...,属性名n]) VALUES (表达式1,...,表达式n);
- 列出的属性名与表达式个数相同
- 表达式i的值是该元组属性的值
- 若没给出属性名列表,则表示插入一条完整的元组,其属性顺序与关系模式中定义的属性顺序相同
- 若只列出部分属性名,则该元组的其他属性值为空
修改数据
UPDATE 关系名 SET 属性名1=表达式1,...,属性名n=表达式n [WHERE 修改条件];
- 若WHERE子句存在,则将满足修改条件的元组中属性的值修改为表达式的值
- 若WHERE子句不存在,则将所有元组中属性i的值修改为表达式i的值
- 修改条件仅涉及该关系本身
删除数据
DELETE FROM 关系名 [WHERE 删除条件];
数据完整性检查
实体完整性检查
- 插入元组或对主键中属性的值进行修改时检查
- 检查主键中各属性的值是否为空.只要有一个为空,就拒绝插入或修改
- 检查主键值是否唯一.若不唯一,则拒绝插入或修改
参照完整性检查
- 在参照关系中插入一个元组,该元组的外键的值在被参照关系的主键值中找不到,即悬空.若岁生这种情况,则拒绝插入该元组
- 修改参照关系中的一个元组,造成该元组的外键的值在被参照关系的主键值中找不到.若发生这种情况,则拒绝修改该元组
- 从被参照关系中删除一个元组,造成参照关系中某些元组的外键的值在被参照关系的主键值中找不到.如果发生这种情况,则采取下列处理方法之一:
- 当参照关系中没有任何元组的外键值与被参照关系中待删除元组的主键值相对应时,方可删除改元组;否则拒绝删除该元组
- 级联删除参照关系中相关联的元组
- 将参照关系中相关联元组的外键值置为空
- 修改被参照关系中一个元组,造成参照关系中某些元组的外键的值在被参照关系的主键值中找不到.如果发生这种情况,则采取下列处理方法之一:
- 当参照关系中没有任何元组的外键值与被参照关系中待删除元组的主键值相对应时,方可删除改元组;否则拒绝删除该元组
- 级联删除参照关系中相关联的元组
- 将参照关系中相关联元组的外键值置为空
语法实现
在FOREIGN KEY子句中声明,当违反参照完整性约束时,如何处理
语法:在FOREIGN KEY子句的末尾加上
ON DELETE NO ACTION|RESTRICT|CASCADE|SET NULL|SET DEFAULT
ON UPDATE NO ACTION|RESTRICT CASCADE|SET NULL|SET DEFAULT
- RESTRICT或NO ACTION:拒绝删除或修改,缺省处理方式
- CASCADE:级联删除或修改
- SET NULL:置为空值
- SET DEFAULT:置为被参照属性的缺省值
用户定义完整性检查
- 插入或修改元组时进行检查
- 若不满足用户定义完整性约束,则拒绝插入或修改该元组
更新视图
不是所有的视图都可以更新
视图可更改的条件:视图中的元组和基础关系中的元组存在1对1关系
如果视图含有以下条件,则不可修改
- 包含聚集函数
- 包含DISTINCT
- 包含GROUPBY
- 包含UNION
- FROM子句中包含子查询
- FROM子句中包含不可更新的视图
- 包含特定类型连接
- WHERE子句包含相关子查询
视图可插入的条件 - 视图可修改
- 视图的属性不能定义为表达式
- 视图中不能包含基本关系中同一属性的多个副本
SQL数据查询
单关系查询
投影查询
SELECT [DISTINCT] 属性名 列表 FROM 关糸名;
- 查询结果中属性的顺序与属性名列表给出的属性顺序相同
- 不加DISTINCT,则不去除结果中的重复元组;加上DISTINCT,则去除结果中的重复元组
- 若要返回关系中所有的列,可将属性名列表可简写为*
可将投影查询中的属性名替换为表达式,做更复杂的投影操作
SELECT [DISTINCT] 表达式列表 FROM 关系名;
- 表达式可以是常量、算术表达式、函数表达式、逻辑表达式
- 查询结果中表达式列的名称就是该表达式的字符串,可以用“表达式 AS 属性名”将表达式列重命名
选择查询
SELECT [DISTINCT] 表达式列表 FROM 关系名 WHERE 选择条件;
选择查询条件
表达式比较
表达式1 比较运算符 表达式2
比较运算符:=,<,<=,>,>=,!=,<>(不等于)
范围比较
表达式1 [NOT] BETWEEN 表达式2 AND 表达式3
集合元素判断
表达式1 [NOT] IN (表达式2,...,表达式n)
字符串匹配
字符串表达式 [NOT] LIKE 模式 [ESCAPE转义字符]
- 通配符:匹配单个字符
- 通配符%:匹配任意长度的字符串(含空串)
- 可通过ESCAPE子句指定转义字符,默认转义字符为\
字符串正则表达式匹配
字符串表达式 [NOT] REGEXP|RLIKE 模式
空值运算
属性名 IS [NOT] NULL
集合运算
- 查询语句1的结果的属性名将作为集合操作结果的属性名
- 若使用关键词ALL,则并集不去重
- 即使两个查询语句的结果都是有序的,集合操作的结果也未必有序
求并
查询语句1 UNION [ALL] 查询语句2
求交
查询语句1 INTERSECT 查询语句2
求差
查询语句1 MINUS/EXCEPT 查询语句2
查询结果排序
在查询语句的后面加上
ORDER BY 属性名1 [ASC|DESC],...,属性名n [ASC|DESC]
- 按照(属性1,…,属性n)的字典序进行排序
- ASC表示升序(默认),DESC表示降序
- 若排序属性含空值,ASC:属性值为空的元组排在最前;DESC:属性值为空的元组排在最后
- 通常用ORDER BY子句对最终查询结果排序,而不对中间结果排序
限制查询数量
在查询语句的后面加上
LIMIT [偏移量,] 结果数量
LIMIT 结果数量 [OFFSET 偏移量]
- 从偏移量(默认是0)位置的元组开始,返回指定数量的元组
聚集查询
SELECT 聚集函数 ([DISTINCT] 表达式) FROM...WHERE...
聚集函数 | 解释 |
---|---|
COUNT (*) | 所有元组的数量 |
COUNT (表达式) | 非空表达式值的数量 |
COUNT (DISTINCT 表达式) | 不同的非空表达式值的数量 |
MAX ([DISTINCT] 表达式) | 表达式的最大值 |
MIN ([DISTINCT] 表达式) | 表达式的最小值 |
SUM (表达式) | 表达式值的和 |
SUM (DISTINCT 表达式) | 不同的表达式值的和 |
AVG (表达式) | 表达式值的平均值 |
AVG (DISTINCT 表达式) | 不同的表达式值的平均值 |
MySQL还支持其他聚集函数,如GROUP_CONCAT(拼接),VARIANCE(方差),STD(标准差)
参考这
聚集函数不能出现在WHERE子句中!
分组查询
SELECT 分组属性列表,聚集函数表达式列表 FROM 关系名 WHERE 选择条件 GROUP BY分组属性列表 HAVING 分组筛选条件;
- SELECT子句的目标列中只能包含分组属性和聚集函数
- WHERE子句的查询条件中不能出现聚集函数
- HAVING子句的分组筛选条件中可以使用聚集函数
- WHERE、GROUP BY和HAVING的执行顺序
- 按照WHEE子句给出的条件,从关系中选出满足条件的元组
- 按照GROUP BY子句指定的分组属性,对元组进行分组
- 按照HAVING子句给出的条件,对分组进行筛选
连接查询
内连接
SELECT ··· FROM 关系名1 [INNER] JOIN 关系名2 ON 连接条件
当内连接是等值连接,且连接属性同名时,可使用如下语法
关系名1 [INNER] JOIN 关系名2 USING (连接属性列表)
自然连接
关系名1 NATURAL JOIN 关系名2
自连接
语法与其他连接操作相同
注意:
- 参与连接的关系在物理上是同一个关系,但在逻辑上看作两个关系,因此用AS必须重命名
- 当为关系取了别名后,但凡用到该关系时都必须使用其别名,不能再使用原关系名
- 属性名前必须加别名做前缀
例子:查询和Elsa在同一个系学习的学生的学号和姓名
SELECT S2.Sno,S2.Sname
FROM Student AS S1 JOIN Student AS S2
ON S1.Sdept =2.Sdept AND S1.Sno!=S2.Sno WHERE S1.Sname='Elsa';
外连接
当外连接是等值连接,且连接属性同名时,可使用USING(连接属性列表)声明连接条件,但连接属性只会保留一个副本(如果是左(右)外连接,则保留左(右)关系中的连接属性)
左外连接
关系名1 LEFT [OUTER] JOIN 关系名2 ON 连接条件
右外连接
关系名1 RIGHT [OUTER] JOIN 关系名2 ON 连接条件
全外连接
关系名1 FULL [OUTER] JOIN 关系名2 ON 连接条件
自然外连接
关系名1 NATURAL LEFT|RIGHT [OUTER] JOIN 关系名2
嵌套查询
- 查询块:一个SELECT-FROM-WHERE语句称为一个查询块
- 嵌套查询:将一个查询块嵌套在另一个查询块中得到的查询称为嵌套查询,内层查询块称为子查询
- 子查询的类型
- 不相关子查询:子查询不依赖于外层查询
- 相关子查询:子查询依赖于外层查询
嵌套查询的写法
在集合判断条件中使用子查询
表达式 [NOT] IN (子查询)
在比较条件中使用子查询
表达式 比较运算符 [ALL|ANY|SOME] (子查询)
- ALL:当表达式的值与子查询结果中任意的值都满足比较条件时,返回真;否则,返回假
- ANY或SOME:当表达式的值与子查询结果中某个值满足比较条件时,返回真;否则,返回假
- 如果子查询结果应仅包含单个值,则无需在比较运算符后面加ALL、ANTY或SOME
在存在性测试条件中使用子查询
[NOT] EXISTS (子查询)
子查询的SELECT后无需列出目标列,只需用SELECT*,因为我们只判断子查询结果是否为空,并不需要使用子查询结果
还可以利用EXISTS实现以下功能
- 全称量词
- 逻辑蕴含
子查询结果作为派生关系
FROM (子查询)
- 子查询必须是独立子查询
- 派生表必须重命名