数据库（MySQL语句）

数据库语句实践

一. 概念

数据库是长期存放在计算机内、有组织的、可共享的数据集合
数据库中的数据按一定的数据模型组织、描述和存储，且具有较小的冗余度、较高的数据独立性和易拓展性

数据库管理系统（DBMS）是用于建立、使用和维护数据库的软件

• 建立：定义、组织和存储数据
• 使用：查询、插入、删除和修改数据
• 维护：保证数据的安全性、完整性

数据库系统两级独立性
物理独立性：内模式（物理结构/存储结构）修改时，只需修改模式/内模式映像，使得模式尽可能不变
逻辑独立性：模式（逻辑模式）修改时，只需修外模式/模式映像，外模式（用户模式）不变

概念模型：E-R模型
逻辑模型： 层次模型、网状模型、关系模型

二. 关系数据库

关系数据模型：用二维表表示实体集，用外键表示实体之间的联系

关系模型的组成

• 数据结构：全部数据及相互联系都被组织成关系（二维表）
• 数据操作：查询和更新
• 完整性规则：实体完整性、参照完整性、用户定义的完整性

笛卡尔积：所有域的所有取值的一个组合
候选键：某一属性能唯一标识元组
主键： 多个候选键，选定一个为主键

完整性规则

• 实体完整性：主键不为空
• 参照完整性：基本关系上的外键要么空值，要么是某个基本基本关系的主键
• 用户定义的完整性：用户自定义约束

关系代数
R和S传统集合运算

• 合并∪：R和S具有相同属性，结果由属于R或S的元组构成，消除重复元组
• 相交∩：R和S具有相同属性，结果由既属于R又属于S的元组组成
• 求差-：R和S具有相同属性，结果由属于R而不属于S的所有元组组成
• 笛卡尔积X：结果，列属性为R与S之和，行个数为R与S的乘积

关系运算

• 选择：从行角度，选择满足给定条件的元组
• 投影：从列角度，取消某些列，同时可能取消元组（避免重复）
• 求商：（例，求选修全部课程的学生学号）
• 连接：从两个关系的笛卡尔积中选择满足条件的元组

关于连接
等值连接筛选出对应值相等的元组
自然连接筛选属性相同且值相等的元组，同时去掉重复属性名
外连接：自然连接的基础上，把舍去元组其他属性上填NULL加入结果中
（只保留左边关系叫左外连接，只保留右边关系叫右外连接）

三. 关系数据理论

关系数据库的规范化理论
为了避免数据冗余、更新异常、插入异常、删除异常等情况的出现

函数依赖：X函数确定Y，Y依赖于X，记作X—>Y
完全函数依赖：X->Y,并且X的任意真子集不能确定Y
部分函数依赖：X的真子集也能确定Y
传递函数依赖：X->Y且Y—>Z,导致Z传递依赖X

第一范式（1NF）：所有域为简单域，属性不可再分
第二范式(2NF)：每一个非主属性都完全函数依赖于任一个候选码（可以是属性组合）
第三范式(3NF)：非属性不传递依赖于任何一个码
BC范式（BCNF）：每一个决定属性集都包含候选码

四. 视图、索引、存储过程、触发器、事务和并行

视图：虚表，只存放视图的定义，不存放数据，当基表数据变化时，视图数据也发生改变

索引：加快查询速度的有效手段，采用B+树（动态平衡）、HASH索引（快速查找）实现

存储过程：预编译和优化后的sql语句，存储在数据库服务器中，使用时直接调用
触发器：特殊类型的存储过程，定义在关系表上，由事件驱动，在指定表数据发生变化时自动执行

事务：用户定义的数据库操作序列，不可分割的基本工作单位
事务的ACID特性

• 原子性（Atomicity）：事务中对数据库的操作是一个不可分割的操作序列
• 一致性(Consistency): 数据库质只包含成功事务提交的结果
• 隔离性(Isolation): 并发事务互不干扰，数据独立隔离
• 持续性(Durability): 事务一旦提交，改变永久性反映在数据库中

并发控制的基本单位是事务
封锁：独占式的访问数据，释放锁之前其他事务不能更新此数据对象
排它锁（写锁）：给数据对象加上锁后，只允许该事务读取访问
共享锁（读锁）：所有事务都只能读，不可修改，其它事务可以同时上共享锁

三级封锁协议：防丢失修改、防读脏数据、防不可重复读

五. MySQL语句

5.1. 创建、修改、删除（数据库、基本表、视图、索引）

CREATE SCHEMA <模式名> AUTHORIZATION <用户名>
CREATE TABLE <表名> （）;//初始化数据，同时可以加约束条件

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

CREATE [UNIQUE] [CLUSTER] INDEX  <索引名> ON <表名>

ALTER TABLE <表名>  //增删列、约束，修改列名和数据类型
ALTER INDEX <索引名>

DROP SCHEMA <模式名> <CASCADE|RESTRICT>
DROP TABLE <表名> [CASCADE|RESTRICT]
DROP VIEW <视图名> [CASCADE];
DROP INDEX

5.2. 插入、更新、删除(数据)

INSERT
INTO <表名> [(<属性列1>[,<属性列2 >…)]
VALUES (<常量1> [,<常量2>]… );

INSERT INTO <表名> [(<属性列1> [,<属性列2>… )]
子查询;

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

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

5.3. 特权指令

GRANT
REVOKE
COMMIT
ROLLBACK

5.4. 查询

1. 基本语句

SELECT [ALL|DISTINCT] <目标列表达式>      //指定要显示的属性列
FROM <表名或视图名>                       //指定的查询对象（基本表或视图）
[ WHERE <条件表达式> ]                    //指定查询条件
[ GROUP BY <列名1> [HAVING <条件表达式>]] //对查询结果按指定列分组，同时筛选满足指定条件的组
[ ORDER BY <列名2> [ASC|DESC]；           //按指定列值的升序或降序排序

WHERE子句常用查询条件

比较：=、>、< >= <= 
确定范围： BETWEEN AND， NOT BETWEEN AND
确定集合： IN， NOTIN
字符匹配： LIKE ，NOT LIKE  (%匹配任意字符串，_匹配单个字符)
空值： IS NULL，IS NOT NULL
多重条件： AND ， OR

2. 单表查询

SELECT  Sname NAME, LOWER(Sdept) DEPARTMENT   //使用别名改变列标题，同时查询经过计算的值
FROM Student
WHERE NOT Sage < 20 AND Sdept IN('IS','MA','CS') AND Sname LIKE '何%' //查询条件
ORDER BY Grade DESC; //排序

//对满足条件的分组进行统计，例为查询选修3门以上课程且成绩90分以上的学生学号
SELECT Cno，COUNT(Sno)
FROM SC
WHERE Grade>=90  //对数据进行筛选
GROUP BY Cno HAVING COUNT(*) >3； //对分组进行筛选

3. 连接查询

同时涉及两个以上的表的查询，其实等价于笛卡尔积再进行行筛选

//等值连接（会有重复列）
SELECT Student.*，SC.*
FROM Student，SC
WHERE Student.Sno = SC.Sno；

//自身连接（需要起别名）
SELECT FIRST.Cno，SECOND.Cpno
FROM Course FIRST，Course SECOND
WHERE FIRST.Cpno = SECOND.Cno；

//外连接（不满足连接条件的元组一并输出）
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno,Grade
FROM Student left outer join SC on
Student.Sno = SC.Sno;

//多表连接
SELECT Student.Sno, Sname, Cname, Grade
FROM Student, SC, Course
WHERE Student.Sno = SC.Sno and SC.Cno = Course.Cno;

4. 嵌套查询

查询执行顺序由里往外，子查询结果作为父查询条件
子查询谓词： IN /比较符号 + 子查询单值/ 比较符号 + [ANY|ALL]/ exists

例为查询有一门课等于95分的学号和姓名

//不相关子查询
select sno，sname from s
where sno in
(
select sno from sc
where grade = 95
)；

//相关子查询
select sno，sname
from s
where exists
(
select *
from sc
where s.sno=sc.sno //作为相关条件，结果返回true或false
and grade = 95
)；

//同样可以使用连接，效率会低一些
select s.sno, sname
from s, sc
where s.sno = sc.sno and grade = 95

5. 集合查询

并操作UNION、交操作INTERSECT、差操作EXCEPT