code/notebook/sql
数据库笔记
以下Mysql为例
数据库完整性
实体完整性
实体完整性即主码,用PRIMARY KEY
定义.
- 列级约束:只能应用于一列上。
- 表级约束:可以应用于表的多个列上。
简而言之,如果完整性约束涉及到该表的多个属性列,必须定义在表级上,否则既可以定义在列级也可以定义在表级。
参照完整性
参照完整性即外码,用FOREIGN KEY (列名) REFERENCES <表名>(列名)
定义.
用constraint指定约束名,约束不指定名称时,系统会给定一个名称。
注意:外码一定参照主码(可以是其他表的主码,也可以时自己的),而且外码的列数一定要等于被参照表的主码列数。
违约处理:对被参照表进行update
/delete
/insert
操作时会破坏参照完整性时,参照表需要告诉被参照表听改怎么做。需要对参照表定义外码时添加on delete
/on update[<no action>/<cascade>]
- CASCADE:在父表(被参照表)上update/delete记录时,同步子表(参照表)的匹配记录。
- SET NULL:在父表(被参照表)上update/delete记录时,将子表(参照表)上匹配的列设为null(子表的外键列不能为not null)。
- NO ACTION:如果子表中有匹配的记录,不允许对父表进行操作。
- RESTRICT:同NO ACTION,立即检查外键约束。
NULL、RESTRICT、NO ACTION
删除:从表记录不存在时,主表才可以删除。删除从表,主表不变
更新:从表记录不存在时,主表才可以更新。更新从表,主表不变(无法更新从表)
CASCADE
删除:删除主表时自动删除从表。删除从表,主表不变
更新:更新主表时自动更新从表。更新从表,主表不变(无法更新从表)
SET NULL
删除:删除主表时自动更新从表值为NULL。删除从表,主表不变
更新:更新主表时自动更新从表值为NULL。更新从表,主表不变
总结:CASCADE对主表操作 主表影响子表.
RESTRICT对主表操作 子表限制主表.
Mysql中均无法更新子表.
用户定义完整性
列值非空(not null),列值唯一(unique),列值需要满足条件表达式(check).
- not null:列值不能为空,(列级完整性约束)
- unique:列值唯一,(列级或表级约束)
- check:列值应该满足的条件,(列级或表级约束)
a | b |
---|---|
属性 | 列级 |
元组 | 表级 |
完整性约束命名子句
完整性约束命名子句是用来给完整性约束命名,这样我们就可以通过该名称对完整性约束进行删除、增加操作。
数据定义
索引类型
类型 | 特点 |
---|---|
普通索引 | 是最基本的索引,它没有任何限制。 |
唯一索引 | 索引列的值必须唯一,但允许有空值。 |
主键索引 | 是一种特殊的唯一索引,一个表只能有一个主键,不允许有空值。 |
组合索引 | 指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。 |
全文索引 | 主要用来查找文本中的关键字,而不是直接与索引中的值相比较。fulltext索引配合match against操作使用,而不是一般的where语句加like。 |
索引方法
类型 | 特点 |
---|---|
BTREE | BTree索引是最常用的mysql数据库索引算法,因为它不仅可以被用在=,>,>=,<,<=和between这些比较操作符上,而且还可以用于like操作符,只要它的查询条件是一个不以通配符开头的常量 |
HASH | Hash索引只能用于对等比较,例如=,<=>(相当于=)操作符。由于是一次定位数据,不像BTree索引需要从根节点到枝节点,最后才能访问到页节点这样多次IO访问,所以检索效率远高于BTree索引。 |
数据查询
连接查询
- 等值与非等值连接查询
=为等值连接,连接字段必须为可比的。
- 自身连接
自己对自己相连,比如要查询先修课的先修课,要对自己查询两次。
- 外连接
LEFT JOIN
保存左侧所有元组,右侧不存在的用NULL
补齐.(Mysql中LEFT JOIN
与LEFT OUTER JOIN
相同)
- 多表连接
跨多个表进行连接查询。
- *交叉连接
笛卡尔积。SELECT * FROM <TABLE1>, <TABLE2>
或SELECT * FROM <TABLE1> CROSS JOIN <TABLE2>
- *内连接
如果没有连接条件,INNER JOIN 和 CROSS JOIN 在语法上是等同的,两者可以互换。
SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句]
嵌套查询
- 不相关子查询
子查询的查询条件不依赖于父查询。
有些嵌套查询可以使用连接运算替代,有些不可以。
- 相关子查询
子查询的条件依赖于父查询。SELECT Sno, Cno FROM SC x WHERE Grade >= (SELECT AVG(Grade) FROM SC y WHERE y.Sno = x.Sno)
- 带有ANY(SOME)或ALL谓词的子查询
子查询返回单值用比较运算符,返回多值时需要用ANY(SOME)或ALL谓词修饰符。且使用ANY或ALL时需要同时使用比较运算符。
- 带有EXISTS谓词的子查询
子查询不返回任何数据,只产生逻辑真值true
或false
。
注意:全称量词可以通过否运算转换成存在量词。
注意:蕴含式可以转换为析取式。
- 集合查询
并操作UNION
,交操作INTERSECT
,差操作EXCEPT
.
- 派生表查询
子查询出现在FROM子句中时,这是子查询生成临时的派生表。
注意:必须为派生关系指定一个别名。