数据库(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