数据库系统原理
2021-01-23 22:08:13 2 举报AI智能生成
计算机管理专业自考本科
作者其他创作
大纲/内容
数据编程
存储过程
概念：
是一组为了完成某项特定功能的SQL语句集
其实质就是一段存储在数据库中的代码
它可以由声明式的sql语句和过程式sql语句组成
优点：
可增强SQL语言的功能和灵活性
良好的封装性
高性能
可减少网络流量
可作为一种安全机制来确保数据库的安全性和数据的完整性
是一组为了完成某项特定功能的SQL语句集
其实质就是一段存储在数据库中的代码
它可以由声明式的sql语句和过程式sql语句组成
优点：
可增强SQL语言的功能和灵活性
良好的封装性
高性能
可减少网络流量
可作为一种安全机制来确保数据库的安全性和数据的完整性
定义：
delimiter $$ 修改系统定义的存储过程结束符，否则会和存储过程中语句;号作用重叠
创建存储过程：
create procedure sp_name([proc_parameter[,...]])
begin
routine_body
end
参数说明： [in | out | inout] param_name_type
调用：
call sp_name([parameter[,...]])
删除
drop procedure [if exist] sp_name $$
delimiter $$ 修改系统定义的存储过程结束符，否则会和存储过程中语句;号作用重叠
创建存储过程：
create procedure sp_name([proc_parameter[,...]])
begin
routine_body
end
参数说明： [in | out | inout] param_name_type
调用：
call sp_name([parameter[,...]])
删除
drop procedure [if exist] sp_name $$
存储过程体
declare 定义变量
DECLARE var_name[,…] type [DEFAULT value]
局部变量
1）只能在存储过程体的BEGIN…END语句块中声明；
2）必须在存储过程的开头处声明；
3）作用范围仅限于声明它的BEGIN…END语句块；
4）不同于用户变量
局部变量与用户变量的区别：
1）局部变量声明时，在其前面没有@符号，并且它只能 被声明它的BEGIN…END语句块中的语句所使用；
2）用户变量在声明时，会在其名称前面使用@符号，同 时已声明的用户变量存在于整个会话之中。
SET var_name=expr[,var_name=expr]… set 语句赋值
SELECT…INTO语句 把选定列的值直接存储到局部变量中
SELECT col_name[,…] INTO var_name[,…] table_expr
流程控制语句：
if ... then .... else ... end if
case
循环：
while .. end while
repeat ... end repeat
loop ... end loop
iterate 语句 退出循环
游标
DECLARE cursor_name CURSOR FOR select_statement 定义
OPEN cursor_name 打开
FETCH cursor_name INTO var_name[,var_name] … 读取游标数据
CLOSE cursor_name 最后关闭游标
declare 定义变量
DECLARE var_name[,…] type [DEFAULT value]
局部变量
1）只能在存储过程体的BEGIN…END语句块中声明；
2）必须在存储过程的开头处声明；
3）作用范围仅限于声明它的BEGIN…END语句块；
4）不同于用户变量
局部变量与用户变量的区别：
1）局部变量声明时，在其前面没有@符号，并且它只能 被声明它的BEGIN…END语句块中的语句所使用；
2）用户变量在声明时，会在其名称前面使用@符号，同 时已声明的用户变量存在于整个会话之中。
SET var_name=expr[,var_name=expr]… set 语句赋值
SELECT…INTO语句 把选定列的值直接存储到局部变量中
SELECT col_name[,…] INTO var_name[,…] table_expr
流程控制语句：
if ... then .... else ... end if
case
循环：
while .. end while
repeat ... end repeat
loop ... end loop
iterate 语句 退出循环
游标
DECLARE cursor_name CURSOR FOR select_statement 定义
OPEN cursor_name 打开
FETCH cursor_name INTO var_name[,var_name] … 读取游标数据
CLOSE cursor_name 最后关闭游标
创建存储过程例子：
DELIMITER $$
DROP PROCEDURE IF EXISTS Pro_Employee$$
CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )
BEGIN
SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid;
END$$
DELIMITER ;
调用：
CALL Pro_Employee(101,@pcount);
SELECT @pcount;
DELIMITER $$
DROP PROCEDURE IF EXISTS Pro_Employee$$
CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )
BEGIN
SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid;
END$$
DELIMITER ;
调用：
CALL Pro_Employee(101,@pcount);
SELECT @pcount;
存储函数
概念：
是由SQL语句和过程式语句组成的代码片段。
是由SQL语句和过程式语句组成的代码片段。
定义：
CREATE FUNCTION sp_name([func_parameter[,…]]) RETURNS type routine_body 创建
例子：
Use mysql_test;
DELIMITER $$
DROP FUNCTION IF EXISTS fn_search$$
CREATE FUNCTION fn_search(cid INT)
RETURNS CHAR(20)
DETERMINISTIC
BEGIN
DECLARE SEX CHAR(20);
SELECT cust_sex INTO SEX FROM customers WHERE cust_id=cid;
IF SEX IS NULL
THEN RETURN(SELECT ‘没有该客户’);
ELSE IF SEX=‘F’
THEN RETURN(SELECT’女’);
ELSE
RETURN(SELECT ‘男’);
END IF;
END IF;
END $$
DELIMITER ;
DETERMINISTIC 解释：如果程序或线程总是对同样的输入参数产生同样的结果，则被认为它是“确定的”，否则就是“非确定”的。
删除：
DROP FUNCTION [IF EXISTS] sp_name
调用：
SELECT sp_name([func_parameter[,…]])
CREATE FUNCTION sp_name([func_parameter[,…]]) RETURNS type routine_body 创建
例子：
Use mysql_test;
DELIMITER $$
DROP FUNCTION IF EXISTS fn_search$$
CREATE FUNCTION fn_search(cid INT)
RETURNS CHAR(20)
DETERMINISTIC
BEGIN
DECLARE SEX CHAR(20);
SELECT cust_sex INTO SEX FROM customers WHERE cust_id=cid;
IF SEX IS NULL
THEN RETURN(SELECT ‘没有该客户’);
ELSE IF SEX=‘F’
THEN RETURN(SELECT’女’);
ELSE
RETURN(SELECT ‘男’);
END IF;
END IF;
END $$
DELIMITER ;
DETERMINISTIC 解释：如果程序或线程总是对同样的输入参数产生同样的结果，则被认为它是“确定的”，否则就是“非确定”的。
删除：
DROP FUNCTION [IF EXISTS] sp_name
调用：
SELECT sp_name([func_parameter[,…]])
存储函数和存储函数区别
存储函数：
存储过程：
存储函数：
- 不能拥有输出参数
- 必须包含一条RETURN语句
- 可以直接使用select调用存储函数，不需要CALL语句
存储过程：
- 可以拥有输出参数
- 不允许包含RETURN语句
- 需要CALL语句调用存储过程
数据库安全与保护
数据库完整性
数据库完整性是指数据库中数据的正确性和相容性。
如性别：只能是男或女 年龄：为0-200岁 身份证：422422199011240088 格式固定
如性别：只能是男或女 年龄：为0-200岁 身份证：422422199011240088 格式固定
完整性约束条件的作用对象
列级约束：包括对列的类型、取值范围、精度等的约束
元组约束：指元组中各个字段之间的相互约束 （ 如开始日期小于结束日期）
表级约束：指若干元组、关系之间的联系的约束 （比如说参照完整性约束）
定义与实现完整性约束
实体完整性约束：是通过主键约束和候选键约束实现的（主码、主属性、候选键不能为空）
参照完整性约束：外码为空，且外码等于主码
用户定义的完整性约束：年龄在0-200之间
主键列必须遵守的规则
每一个表只能定义一个主键
主键唯一，且不能为NULL
复合主键不能包含不必要的多余列（不能为超码）
一个列名在复合主键的列表中只能出现一次
约束定义：
主键约束在create table 或 alter table 时 使用 PRIMARY KEY定义
候选键约束在create table 或 alter table 时 使用 unique定义
主键约束和候选键约束区别：
主键约束：一个表只能创建一个主键，使用 primary key定义
候选键约束：可以定义若干个候选键，使用unique 定义
定义参照完整性，这个语句是执行外键表的，参照表是主键表
REFERENCES tbl_name(index_col_name[(length)][ASC | DESC],…) tbl_name 参照表名 index_col_name 是被参照的列
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option 选项： RESTRICT（[默认]限制策略，拒绝操作） | CASCADE（级联策略） | SET NULL（置空策略，把外键置空，才能操作主键表） | NO ACTION（不采取实施策略）
列级约束：包括对列的类型、取值范围、精度等的约束
元组约束：指元组中各个字段之间的相互约束 （ 如开始日期小于结束日期）
表级约束：指若干元组、关系之间的联系的约束 （比如说参照完整性约束）
定义与实现完整性约束
实体完整性约束：是通过主键约束和候选键约束实现的（主码、主属性、候选键不能为空）
参照完整性约束：外码为空，且外码等于主码
用户定义的完整性约束：年龄在0-200之间
主键列必须遵守的规则
每一个表只能定义一个主键
主键唯一，且不能为NULL
复合主键不能包含不必要的多余列（不能为超码）
一个列名在复合主键的列表中只能出现一次
约束定义：
主键约束在create table 或 alter table 时 使用 PRIMARY KEY定义
候选键约束在create table 或 alter table 时 使用 unique定义
主键约束和候选键约束区别：
主键约束：一个表只能创建一个主键，使用 primary key定义
候选键约束：可以定义若干个候选键，使用unique 定义
定义参照完整性，这个语句是执行外键表的，参照表是主键表
REFERENCES tbl_name(index_col_name[(length)][ASC | DESC],…) tbl_name 参照表名 index_col_name 是被参照的列
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option 选项： RESTRICT（[默认]限制策略，拒绝操作） | CASCADE（级联策略） | SET NULL（置空策略，把外键置空，才能操作主键表） | NO ACTION（不采取实施策略）
触发器
触发器是用户定义在关系表上的一类由事件驱动的数据库对象，也是一种保证
数据完整性的方法。
数据完整性的方法。
创建
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_body
删除
DROP TRIGGER[IF EXISTS][schema_name.]trigger_name
使用触发器
1、 INSERT 触发器
2、DELETE 触发器
3、UPDATE 触发器
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_body
删除
DROP TRIGGER[IF EXISTS][schema_name.]trigger_name
使用触发器
1、 INSERT 触发器
2、DELETE 触发器
3、UPDATE 触发器
安全性与访问控制
用户账号管理
创建用户账号
CREATE USER user[IDENTIFIED BY [PASSWORD]‘password’]
删除用户账号
DORP USER user[，user]…
修改用户账号
RENAME USER old_user TO new_user[,old_user TO new_user]…
修改用户口令
SET PASSWORD[FOR user]= {
PASSWORD(‘new_password’)
|’encrypted password’
}
CREATE USER user[IDENTIFIED BY [PASSWORD]‘password’]
删除用户账号
DORP USER user[，user]…
修改用户账号
RENAME USER old_user TO new_user[,old_user TO new_user]…
修改用户口令
SET PASSWORD[FOR user]= {
PASSWORD(‘new_password’)
|’encrypted password’
}
账户权限管理
权限授予
GRANT
Pri_type[(column_list)]
[,pri_type[(column_list)]]…
ON[object_type]priv_level
TO user_specification[,user_specification]…
[WITH GRANT OPTION]
权限的转移
权限的转移可以通过 GRANT 语句中使用 WITH 子句来实现。如果将 WITH 子 句指定为关键字“WITH GRANT OPTION”，则表示 TO 子句中所指定的所有用
户都具有把自己所拥有的权限授予给其他用户的权利，而无论那些其他用户是
否拥有该权限
用户的撤销（回收用户权限）
回收某些特定的权限
REVOKE
priv_type[(column_list)]
[,priv_type[(column_list)]]…
ON[object_type]priv_level
FROM user[,user]…
回收全部
REVOKE ALL PRIVILEGES,GRANT OPTION FROM user[,user]…
GRANT
Pri_type[(column_list)]
[,pri_type[(column_list)]]…
ON[object_type]priv_level
TO user_specification[,user_specification]…
[WITH GRANT OPTION]
权限的转移
权限的转移可以通过 GRANT 语句中使用 WITH 子句来实现。如果将 WITH 子 句指定为关键字“WITH GRANT OPTION”，则表示 TO 子句中所指定的所有用
户都具有把自己所拥有的权限授予给其他用户的权利，而无论那些其他用户是
否拥有该权限
用户的撤销（回收用户权限）
回收某些特定的权限
REVOKE
priv_type[(column_list)]
[,priv_type[(column_list)]]…
ON[object_type]priv_level
FROM user[,user]…
回收全部
REVOKE ALL PRIVILEGES,GRANT OPTION FROM user[,user]…
事务与并发控制
概念
所谓事务是用户定义的一个数据操作序列，这些操作可作为一个完整的工作单元，要么全部执行，要么全部不执行，是一个不可分割的工作单位。事务中的操作一般是对数据的更新操作，包括增、删、改。
所谓事务是用户定义的一个数据操作序列，这些操作可作为一个完整的工作单元，要么全部执行，要么全部不执行，是一个不可分割的工作单位。事务中的操作一般是对数据的更新操作，包括增、删、改。
特征（ACID）
原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持续性（Durability）
原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持续性（Durability）
使用事务
以BEGIN TRANSACTION语句开始
以COMMIT语句或ROLLBACK语句结束
以BEGIN TRANSACTION语句开始
以COMMIT语句或ROLLBACK语句结束
并发问题
丢失更新、
事务T1,T2同时读入同一数据并加以修改，T2的提交结果会破坏T1提交的结果
不可重复读、
事务T1读取数据后，事务T2执行更新操作，使T1无法再现前一次读取结果
脏读
事务T1修改数据后撤销，使得T2读取的数据与数据库中不一致
如何解决并发问题？
封锁
丢失更新、
事务T1,T2同时读入同一数据并加以修改，T2的提交结果会破坏T1提交的结果
不可重复读、
事务T1读取数据后，事务T2执行更新操作，使T1无法再现前一次读取结果
脏读
事务T1修改数据后撤销，使得T2读取的数据与数据库中不一致
如何解决并发问题？
封锁
备份与恢复
会造成数据库运行事务异常中断的因素可能是：
（1） 计算机硬件故障
（2） 计算机软件故障
（3） 病毒
（4） 人为误操作
（5） 自然灾害
（6） 盗窃
（1） 计算机硬件故障
（2） 计算机软件故障
（3） 病毒
（4） 人为误操作
（5） 自然灾害
（6） 盗窃
语句
备份数据
SELECT*INTO OUTFILE’file_name’export_options
|INTO DUMPFILE’file_name’
可选项
[FIELDS
[TERMINATED BY ‘string’]
[[OPTIONALLY] ENCLOSED BY’char’]
[ESCAPED BY’char’]
]
[LINES TERMINATED BY’string’]
恢复数据
LOAD DATA INFILE ‘file_name.txt’
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY ’string’]
[OPTIONALLY] ENCLOSED BY ’char’]
[ESCAPED BY ’char’]
]
[LINES
[STARTING BY ’string’]
[TERMINATED BY ’string’]
]
备份数据
SELECT*INTO OUTFILE’file_name’export_options
|INTO DUMPFILE’file_name’
可选项
[FIELDS
[TERMINATED BY ‘string’]
[[OPTIONALLY] ENCLOSED BY’char’]
[ESCAPED BY’char’]
]
[LINES TERMINATED BY’string’]
恢复数据
LOAD DATA INFILE ‘file_name.txt’
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY ’string’]
[OPTIONALLY] ENCLOSED BY ’char’]
[ESCAPED BY ’char’]
]
[LINES
[STARTING BY ’string’]
[TERMINATED BY ’string’]
]
概述
数据（Data）：描述事物的符号记录，是指利用物理符号记录下来的可以鉴别的信息。
数据是信息存在的一种形式，只有通过解释或处理的数据才能成为有用的信息
数据是信息存在的一种形式，只有通过解释或处理的数据才能成为有用的信息
数据库（DB）：是指长期存储在计算机中的有组织的、可共享的数据集合
数据要按照一定的数据模型组织、描述、和存储，具有较小的冗余度、较高的数据独立性，易于扩展，可共享
数据要按照一定的数据模型组织、描述、和存储，具有较小的冗余度、较高的数据独立性，易于扩展，可共享
数据库管理系统（DBMS）：
概念：是专门用于简历和管理数据库的一套软件
功能：1、数据定义功能（建表）
2、数据操纵（增删改查）
3、数据库的运行管理
4、数据库的建立和维护
5、数据的组织、存储、管理
6、网络通信等其他功能
概念：是专门用于简历和管理数据库的一套软件
功能：1、数据定义功能（建表）
2、数据操纵（增删改查）
3、数据库的运行管理
4、数据库的建立和维护
5、数据的组织、存储、管理
6、网络通信等其他功能
数据库系统的构成：
用户 → 应用程序（微信）→ DBMS → DB → 数据库管理员（DBA）
用户 → 应用程序（微信）→ DBMS → DB → 数据库管理员（DBA）
发展史
人工管理阶段
数据不保存
应用程序之间数据不能交换，自己管理自己的数据
文件系统阶段
可以保存
优点：物理数据独立性
数据库系统阶段
特点：
1、目的：数据集成（把一堆数据集中在一起）
2、数据共享性高
3、数据冗余小
4、数据一致性
5、数据独立性高
6、实施统一管理与控制
主要包括数据的安全性、完整性、并发控制与故障恢复等，即数据保护
1、目的：数据集成（把一堆数据集中在一起）
2、数据共享性高
3、数据冗余小
4、数据一致性
5、数据独立性高
6、实施统一管理与控制
主要包括数据的安全性、完整性、并发控制与故障恢复等，即数据保护
数据库系统的结构
数据库系统的结构分类
DBA视角
内部系统结构采用三级模式：
→ 应用
→ 外模式 （子模式或用户模式） （数据视图或用户视图） 如：一小部分领域（用户级）
→ 模式 （概念模式或逻辑模式）（概念视图） 如：长度5，数据类型（varchar）（数据库核心，也是数据设计的关键）
→ 内模式 （存储模式）（内部视图或存储视图） 如：存照片，确定怎么存，存成什么样的
→ 数据库
外部系统结构：
集中式结构、分布式结构、并行结构
→ 应用
→ 外模式 （子模式或用户模式） （数据视图或用户视图） 如：一小部分领域（用户级）
→ 模式 （概念模式或逻辑模式）（概念视图） 如：长度5，数据类型（varchar）（数据库核心，也是数据设计的关键）
→ 内模式 （存储模式）（内部视图或存储视图） 如：存照片，确定怎么存，存成什么样的
→ 数据库
外部系统结构：
集中式结构、分布式结构、并行结构
三级模式结构的两层映像与数据独立性
映像：就是一种对应规则，它指出映像双方是如何进行转换的
模式/内模式映像：保证了数据与程序的物理独立性，模式→内模式的映像（关系），指示内模式是如何转换成模式的
外模式/模式映像：保证了数据与程序的逻辑独立性，外模式→模式
映像：就是一种对应规则，它指出映像双方是如何进行转换的
模式/内模式映像：保证了数据与程序的物理独立性，模式→内模式的映像（关系），指示内模式是如何转换成模式的
外模式/模式映像：保证了数据与程序的逻辑独立性，外模式→模式
用户视角
C/S（客户/服务器结构）：
单机、网络
表示层→数据层
B/S（浏览器/服务器结构）：
web
表示层→处理层→数据层
单机、网络
表示层→数据层
B/S（浏览器/服务器结构）：
web
表示层→处理层→数据层
数据模型
如何把现实世界虚拟到计算机中？
最高层：概念层（数据库设计阶段）
中间层：逻辑层
最低层：物理层
最高层：概念层（数据库设计阶段）
中间层：逻辑层
最低层：物理层
1、什么是数据模型？
模型是现实世界特征的模拟和抽象表达（飞机模型）
数据模型是对现实世界数据特征的抽象，描述的是数据的共性内容（表结构）
模型是现实世界特征的模拟和抽象表达（飞机模型）
数据模型是对现实世界数据特征的抽象，描述的是数据的共性内容（表结构）
2、数据的特征
静态特征：（不会变）
1、数据的基本结构（链表、树）
2、数据间的联系
3、数据取值范围的约束（如：字段取值范围，性别：男、女）
动态特征：对数据进行符合一定规则的操作（增删改）
静态特征：（不会变）
1、数据的基本结构（链表、树）
2、数据间的联系
3、数据取值范围的约束（如：字段取值范围，性别：男、女）
动态特征：对数据进行符合一定规则的操作（增删改）
3、数据模型组成要素
1、数据结构：描述系统静态特征（包含数据类型、内容、属性、数据对象之间的联系）
2、数据操作：描述系统的动态特征（增删改查）
3、数据约束：描述数据结构中数据间的语法和语义关联（数据正确性、有效性等）
1、数据结构：描述系统静态特征（包含数据类型、内容、属性、数据对象之间的联系）
2、数据操作：描述系统的动态特征（增删改查）
3、数据约束：描述数据结构中数据间的语法和语义关联（数据正确性、有效性等）
4、数据模型的分类
→ 现实世界
→ 概念模型或信息模型（信息世界）：也称为概念层，是数据抽象级别的最高层，处于数据库设计阶段
→ 数据库
type（型）：表头
value（值）：值
→ 现实世界
→ 概念模型或信息模型（信息世界）：也称为概念层，是数据抽象级别的最高层，处于数据库设计阶段
→ 数据库
type（型）：表头
value（值）：值
5、什么是概念层数据模型？
6、信息世界涉及的基本概念
信息世界涉及的基本概念：实体（如：学生）、属性（年龄）、码或键（特殊的属性，如：ID）、域（值域）、实体型（实体+属性）、实体集（同类型的实体集合）、关系（实体与实体之间的联系）
信息世界涉及的基本概念：实体（如：学生）、属性（年龄）、码或键（特殊的属性，如：ID）、域（值域）、实体型（实体+属性）、实体集（同类型的实体集合）、关系（实体与实体之间的联系）
7、概念模型的表示方法
使用ER图进行设计
矩形：表示实体
椭圆：表示属性
菱形：表示联系
使用ER图进行设计
矩形：表示实体
椭圆：表示属性
菱形：表示联系
8、什么是逻辑层数据模型
层次模型：早期使用的一种数据模型，有且仅有一个节点，没有父节点，称作根
网状模型：以网状结构表示实体与实体间的关系
关系模型（多表关系）：目前常用的一种模型
面向对象模型
层次模型：早期使用的一种数据模型，有且仅有一个节点，没有父节点，称作根
网状模型：以网状结构表示实体与实体间的关系
关系模型（多表关系）：目前常用的一种模型
面向对象模型
9、逻辑模式的类型
10、物理层数据模型
描述数据在存储介质上的组织结构，是逻辑模型的物理实现，是数据库最底层的抽象，设计目标是提高数据库性能和有效利用存储空间
描述数据在存储介质上的组织结构，是逻辑模型的物理实现，是数据库最底层的抽象，设计目标是提高数据库性能和有效利用存储空间
简答题：
简述概念模型、逻辑模型、物理模型之间的关系
这三个不同的数据模型之间既相互独立，又存在着关联。从现实世界到概念模型的转换是由数据库设计人员完成的；从概念模型到逻辑模型的转换可以由数据库设计人员完成，也可以用数据库设计工具协助设计人员完成；从逻辑模型到物理模型的转换主要是由数据库管理系统完成的。
简述概念模型、逻辑模型、物理模型之间的关系
这三个不同的数据模型之间既相互独立，又存在着关联。从现实世界到概念模型的转换是由数据库设计人员完成的；从概念模型到逻辑模型的转换可以由数据库设计人员完成，也可以用数据库设计工具协助设计人员完成；从逻辑模型到物理模型的转换主要是由数据库管理系统完成的。
关系数据库
发展史
1970年
IBM的E.F.Codd提出了关系模型，奠定了关系数据库的理论基础
20世纪70年代末
关系方法理论研究和软件系统的研制取得了重大突破
1981年
出现了比较成熟的关系数据库管理技术，证实了关系数据库的优点：高级的非过程语言接口、较好的数据独立性。
20世纪80年代后
网状模型和层次模型与底层实现的结合紧密，关系模型具有坚实理论基础，成为主流数据模型。
IBM的E.F.Codd提出了关系模型，奠定了关系数据库的理论基础
20世纪70年代末
关系方法理论研究和软件系统的研制取得了重大突破
1981年
出现了比较成熟的关系数据库管理技术，证实了关系数据库的优点：高级的非过程语言接口、较好的数据独立性。
20世纪80年代后
网状模型和层次模型与底层实现的结合紧密，关系模型具有坚实理论基础，成为主流数据模型。
关系数据模型的组成要素：
1、关系数据结构
2、关系操作集合
3、关系完整性约束
1、关系数据结构
2、关系操作集合
3、关系完整性约束
关系数据结构就是表
表也称为关系（Relation）
有
表名：必须唯一
列：字段或者属性，数据类型相同、值域、必须唯一
行：元组或记录，分量：指行中的一列
表也称为关系（Relation）
有
表名：必须唯一
列：字段或者属性，数据类型相同、值域、必须唯一
行：元组或记录，分量：指行中的一列
关系的三种类型
基本关系（基本表）（实际存在的表）
查询表（虚表）
视图表（虚表）
基本关系（基本表）（实际存在的表）
查询表（虚表）
视图表（虚表）
码或者键
属性（或者属性组）的值都能用来唯一标识该关系的元组，则称为该关系的码或者键
也是就是表中的主键
属性（或者属性组）的值都能用来唯一标识该关系的元组，则称为该关系的码或者键
也是就是表中的主键
超码或者超键
在码中去除某个属性，它仍然是这个关系的码
属性组中有多余的属性
在码中去除某个属性，它仍然是这个关系的码
属性组中有多余的属性
候选码或候选键
在码中不能从中移除任何一个属性，否则它不再是这个关系的码或者键
候选码或者候选键是这个关系的最小超码或超键
属性组中没有多余的属性
在码中不能从中移除任何一个属性，否则它不再是这个关系的码或者键
候选码或者候选键是这个关系的最小超码或超键
属性组中没有多余的属性
主属性或码属性
在数据库中，关系中包含在任何一个候选码中的属性称为主属性或码属性
在数据库中，关系中包含在任何一个候选码中的属性称为主属性或码属性
主码或主键（Primary Key）
在若干个候选码中指定一个唯一标识关系的元组
简单理解就是从候选码中人为选择的
唯一
在若干个候选码中指定一个唯一标识关系的元组
简单理解就是从候选码中人为选择的
唯一
全码或全键（All Key）
所有属性都是这个关系的主码或主键
所有属性都是这个关系的主码或主键
外码或外键
某个属性或属性组不是这个关系的主码或候选码，而是另一个关系的主码
多表
这样的两种关系称为参照关系/主从关系
某个属性或属性组不是这个关系的主码或候选码，而是另一个关系的主码
多表
这样的两种关系称为参照关系/主从关系
域
取值范围
取值范围
关系数据库对关系的限定/要求？
1）每一个属性都是不可分解的（不允许表中有表）；
2）每一个关系仅仅有一种关系模式；
3）每一个关系模式中的属性必须命名，属性名不同；
4）同一个关系中不允许出现候选码或候选键值完全相同的元组；
5）在关系中元组的顺序（即行序）是无关紧要的，可以任意交换；
6）在关系中属性的顺序（即列序）是无关紧要的，可以任意交换。
1）每一个属性都是不可分解的（不允许表中有表）；
2）每一个关系仅仅有一种关系模式；
3）每一个关系模式中的属性必须命名，属性名不同；
4）同一个关系中不允许出现候选码或候选键值完全相同的元组；
5）在关系中元组的顺序（即行序）是无关紧要的，可以任意交换；
6）在关系中属性的顺序（即列序）是无关紧要的，可以任意交换。
关系数据模型
基本的关系操作
Query、Insert、Delete、Update
Query、Insert、Delete、Update
查询（query）
选择
投影
并
差
笛卡尔积
投影
并
差
笛卡尔积
衍生：
连接
除
交
连接
除
交
关系的完整性约束
数据库的数据完整性是指数据库中的数据正确性、相容性、一致性
分类：
1、实体完整性约束：主码的组成不能为空，主属性不能是空值NULL
2、参照完整性约束（两个表关联的外键）：外码要么等于主码值，要么等于为空
前面两条规则是不变的
3、用户定义完整性约束（或域完整性约束）：针对某一应用环境的完整性约束、其他
1、实体完整性约束：主码的组成不能为空，主属性不能是空值NULL
2、参照完整性约束（两个表关联的外键）：外码要么等于主码值，要么等于为空
前面两条规则是不变的
3、用户定义完整性约束（或域完整性约束）：针对某一应用环境的完整性约束、其他
简单应用：
1、执行插入操作的检验
需要所有检查
检查实体完整性（主码不能为空）→检查参照完整性（检查外码表主码）→检查用户定义完整性约束
2、删除
只考虑参照完整性约束和用户定义完整性约束
3、更新
可以理解为先删除后更新
1、执行插入操作的检验
需要所有检查
检查实体完整性（主码不能为空）→检查参照完整性（检查外码表主码）→检查用户定义完整性约束
2、删除
只考虑参照完整性约束和用户定义完整性约束
3、更新
可以理解为先删除后更新
关系数据库的规范化理论
关系模式中可能纯在的冗余和异常问题：
数据冗余：会造成以下异常
更新异常：
插入异常：
删除异常：
数据冗余：会造成以下异常
更新异常：
插入异常：
删除异常：
函数依赖：
设R为任一给定关系，如果对于R中属性X的每一个值，R中的属性Y只有唯一值与之对应，则称X函数决定Y或称Y函数依赖于X，记作X→Y。其中X称为决定因素。
反之则没有函数依赖
如：知道学号就知道了学生姓名
函数依赖分类
1、完全函数依赖 就是候选码的关系，不能移除任何一个属性（如 学号，课程，成绩） 必须是学号+课程才能决定成绩
设R为任一给定关系，X、Y为其属性集，若X→Y，且对X中的任何真子集X‘ ，都有X’不能决定 Y，则称Y完全函数依赖于X。
2、部分函数依赖 就是超码（如：学号，姓名，性别）学号或者姓名其中之一可以决定性别
设R为任一给定关系，X、Y为其属性集，若X→Y，且对X中的任何真子集X‘ ，都有X’→ Y，则称Y完全函数依赖于X
3、传递函数依赖 X、Y、Z 多对一对一
设R为任一给定关系，X、Y、Z为其不同属性子集，若X→Y，Y 不能决定 X，Y→Z，则有X→Z，称为Z传递函数依赖于X
如：书号，出版社名，出版社地址， 书号可以推出出版社名，但出版社名推不出书号，出版社名可以推出出版社地址，得出书号可以处处出版社地址
设R为任一给定关系，如果对于R中属性X的每一个值，R中的属性Y只有唯一值与之对应，则称X函数决定Y或称Y函数依赖于X，记作X→Y。其中X称为决定因素。
反之则没有函数依赖
如：知道学号就知道了学生姓名
函数依赖分类
1、完全函数依赖 就是候选码的关系，不能移除任何一个属性（如 学号，课程，成绩） 必须是学号+课程才能决定成绩
设R为任一给定关系，X、Y为其属性集，若X→Y，且对X中的任何真子集X‘ ，都有X’不能决定 Y，则称Y完全函数依赖于X。
2、部分函数依赖 就是超码（如：学号，姓名，性别）学号或者姓名其中之一可以决定性别
设R为任一给定关系，X、Y为其属性集，若X→Y，且对X中的任何真子集X‘ ，都有X’→ Y，则称Y完全函数依赖于X
3、传递函数依赖 X、Y、Z 多对一对一
设R为任一给定关系，X、Y、Z为其不同属性子集，若X→Y，Y 不能决定 X，Y→Z，则有X→Z，称为Z传递函数依赖于X
如：书号，出版社名，出版社地址， 书号可以推出出版社名，但出版社名推不出书号，出版社名可以推出出版社地址，得出书号可以处处出版社地址
关键字的定义 就是候选码
设R为任一给定关系，U为其所含的全部属性集合，X为U的子集，若有完全函数依赖X→U，则X为R的一个候选关键字。
设R为任一给定关系，U为其所含的全部属性集合，X为U的子集，若有完全函数依赖X→U，则X为R的一个候选关键字。
范式与关系规范化过程
第一范式（1NF）
设R为任一给定关系，若果R中每个列与行的交点处的取值都是不可再分的基本元素，则R为第一范式。
设R为任一给定关系，若果R中每个列与行的交点处的取值都是不可再分的基本元素，则R为第一范式。
第二范式（2NF） 消除第一范式中部分函数依赖即成为第二范式
设R为任一给定关系，若R为1NF，且其所有非主属性都完全函数依赖于候选关键字，则R为第二范式。
设R为任一给定关系，若R为1NF，且其所有非主属性都完全函数依赖于候选关键字，则R为第二范式。
第三范式（2NF） 消除第二范式中传递函数依赖即成为第三范式
设R为任一给定关系，若R为2NF，且其每一个非主属性都不传递函数依赖于候选关键字，则R为第三范式。
设R为任一给定关系，若R为2NF，且其每一个非主属性都不传递函数依赖于候选关键字，则R为第三范式。
第三范式的改进形式 -（巴斯范式） BCNF 消除传递函数依赖
设R为任一给定关系，X、Y为其属性集，F为其函数依赖集，若R为3NF，且其F中所有函数依赖X→Y(Y 不属于X)中的X必包含候选关键字，则R为BCNF
设R为任一给定关系，X、Y为其属性集，F为其函数依赖集，若R为3NF，且其F中所有函数依赖X→Y(Y 不属于X)中的X必包含候选关键字，则R为BCNF
数据库设计
概述
数据库的生命周期
1、数据库分析与设计阶段：需求分析、概念设计、逻辑设计、物理设计
2、数据库实现与操作阶段：实现、操作与监督（类似测试）、修改与调整
1、数据库分析与设计阶段：需求分析、概念设计、逻辑设计、物理设计
2、数据库实现与操作阶段：实现、操作与监督（类似测试）、修改与调整
数据库设计的目标
满足应用功能的需求：存取删改
良好的数据库性能：高效、节省空间、共享性、完整性、一致性、安全性高
满足应用功能的需求：存取删改
良好的数据库性能：高效、节省空间、共享性、完整性、一致性、安全性高
数据库设计的内容
结构设计（静态的）：数据库概念结构设计、逻辑结构设计、物理结构设计
行为设计（动态的）：功能设计、事务设计、程序设计
结构设计（静态的）：数据库概念结构设计、逻辑结构设计、物理结构设计
行为设计（动态的）：功能设计、事务设计、程序设计
数据库设计的方法
直观设计法：最原始的数据库设计方法
规范设计法：
1新奥尔良设计方法：需求分析、概念结构设计、逻辑结构设计、物理结构设计
2基于E-R模型的数据库设计方法
3基于第三范式的设计方法，是一类结构化设计方法
计算机辅助设计法：辅助软件工程工具
直观设计法：最原始的数据库设计方法
规范设计法：
1新奥尔良设计方法：需求分析、概念结构设计、逻辑结构设计、物理结构设计
2基于E-R模型的数据库设计方法
3基于第三范式的设计方法，是一类结构化设计方法
计算机辅助设计法：辅助软件工程工具
数据库设计的过程
需求分析
结构设计、行为设计
数据库实施 ：加载数据库数据、调试运行应用程序
数据库运行与维护
需求分析
结构设计、行为设计
数据库实施 ：加载数据库数据、调试运行应用程序
数据库运行与维护
数据库设计的基本步骤
需求分析
数据库设计的起点
目标：了解与分析用户的信息及应用的处理的要求，并将结果按一定格式整理而形成需求分析报告
改分析报告是后续概念设计、逻辑设计、物理设计、数据库建立与维护的依据
数据库设计的起点
目标：了解与分析用户的信息及应用的处理的要求，并将结果按一定格式整理而形成需求分析报告
改分析报告是后续概念设计、逻辑设计、物理设计、数据库建立与维护的依据
确定数据范围：数据库的第一项工作，有效地利用计算机设备及数据库系统的潜在能力，提高数据的应变能力，避免应用过程中对数据库做太多或者太大的修改，延长数据库的生命周期
→分析数据应用过程：了解并分析数据与数据处理间的关系，
（1）用到哪些数据；
（2）数据使用的顺序；
（3）对数据作何处理和处理的策略以及结果；
应用过程分析的结果是数据库结构设计的重要依据
（1）用到哪些数据；
（2）数据使用的顺序；
（3）对数据作何处理和处理的策略以及结果；
应用过程分析的结果是数据库结构设计的重要依据
→收集与分析数据：数据收集与分析的任务是了解并分析数据的组成格式及操作特征，每个数据元素的语义及关系等，并将它们收集起来整理归档。
静态结构 --- 不施加应用操作于其上时数据的原始状况
数据分类表：用于数据的总体描述
数据元素表：指通常意义下的数据项或属性
动态结构 ---将应用操作施加于数据之上后数据的状况
任务分类表：一个任务指为完成某一特定处理功能的相对独立的操作序列（如查询）
数据特征操作表：用以描述任务和数据之间的关系，它包括不同任务对数据执行不同操作的频率
数据约束 ---使用数据时的特殊要求
1）数据的安全保密性
2）数据的完整性
3）响应时间
4）数据恢复
静态结构 --- 不施加应用操作于其上时数据的原始状况
数据分类表：用于数据的总体描述
数据元素表：指通常意义下的数据项或属性
动态结构 ---将应用操作施加于数据之上后数据的状况
任务分类表：一个任务指为完成某一特定处理功能的相对独立的操作序列（如查询）
数据特征操作表：用以描述任务和数据之间的关系，它包括不同任务对数据执行不同操作的频率
数据约束 ---使用数据时的特殊要求
1）数据的安全保密性
2）数据的完整性
3）响应时间
4）数据恢复
→编写需求分析报告
1）数据库的应用功能目标
2）标明不同用户视图范围（如登录需要的数据）
3）应用处理过程需求说明，包括：数据流程图；任务分类表；数据操作特征表；操作过程说明书。
4）数据字典，是数据库系统中存储三级结构定义的数据库，通常指的是数据库系统中各类数据详细描述的集合。功能是存储和检索各种数据描述，即元数据。包括数据分类表、数据元素表和各类原始资料。
5）数据量
6）数据约束
1）数据库的应用功能目标
2）标明不同用户视图范围（如登录需要的数据）
3）应用处理过程需求说明，包括：数据流程图；任务分类表；数据操作特征表；操作过程说明书。
4）数据字典，是数据库系统中存储三级结构定义的数据库，通常指的是数据库系统中各类数据详细描述的集合。功能是存储和检索各种数据描述，即元数据。包括数据分类表、数据元素表和各类原始资料。
5）数据量
6）数据约束
概念结构设计
关系数据库设计方法
概念结构设计方法
E-R图的表示方法
1-N 属于
N-1 包含
M-N 拥有
1-N 属于
N-1 包含
M-N 拥有
局部信息结构设计（也就是部分领域）
1、确定局部范围：主要依据需求分析报告中标明的用户视图范围来确定
2、选择实体：数据分类表是选择实体的直接依据
3、选择实体的关键字属性：主码
4、确定实体间的联系：数据间的联系必须在概念设计时确定
5、确定实体的属性：分为标识属性（主码）和说明属性（非主属性）
1、确定局部范围：主要依据需求分析报告中标明的用户视图范围来确定
2、选择实体：数据分类表是选择实体的直接依据
3、选择实体的关键字属性：主码
4、确定实体间的联系：数据间的联系必须在概念设计时确定
5、确定实体的属性：分为标识属性（主码）和说明属性（非主属性）
全局信息结构设计（也就是整个系统设计）
注意联系也有属性，也就是多对多联系的映射表属性
注意联系也有属性，也就是多对多联系的映射表属性
逻辑结构设计方法
1、E-R图转换为关系模型的原则
1)一个实体型转换为一个关系模式，实体属性作为关系属性，实体码作为关系码，也就是实体转化为表
2)一个一对一联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并（联系转化为中间表，也可以使用主外键关联）
3)一个一对多联系可以转换为一个独立的关系模式，也可以与N端对应的关系模式合并（一对多联系变成中间表，也可以在N端设置外码关联）
4)一个多对多联系转换为一个关系模式，与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性（也就是转换成中间表）
5)三个或者以上的实体键的一个多元联系可以转换为一个关系模式
6)具有相同码的关系模式可合并
1)一个实体型转换为一个关系模式，实体属性作为关系属性，实体码作为关系码，也就是实体转化为表
2)一个一对一联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并（联系转化为中间表，也可以使用主外键关联）
3)一个一对多联系可以转换为一个独立的关系模式，也可以与N端对应的关系模式合并（一对多联系变成中间表，也可以在N端设置外码关联）
4)一个多对多联系转换为一个关系模式，与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性（也就是转换成中间表）
5)三个或者以上的实体键的一个多元联系可以转换为一个关系模式
6)具有相同码的关系模式可合并
2、对关系数据模型进行优化
1)确定各属性间的函数依赖关系
2)对于各个关系模式之间的数据依赖进行极小化处理，消除冗余的联系
3)判断各个关系模式的范式，根据实际需要确定最合适的范式
4)按照需求分析阶段得到的处理要求，分析这些模式对于这样的应用环境是否合适，确定是否要对某些模式进行合并或分解
5)对关系模式进行必要的分解，提高数据操作的效率和存储空间的利用率
1)确定各属性间的函数依赖关系
2)对于各个关系模式之间的数据依赖进行极小化处理，消除冗余的联系
3)判断各个关系模式的范式，根据实际需要确定最合适的范式
4)按照需求分析阶段得到的处理要求，分析这些模式对于这样的应用环境是否合适，确定是否要对某些模式进行合并或分解
5)对关系模式进行必要的分解，提高数据操作的效率和存储空间的利用率
3、设计面向用户的外模式
1)可以通过视图机制在设计用户视图时，重新定义某些属性的别名，使其更符合用户的习惯，以方便使用，
2)可以对不同级别的用户定义不同的视图，以保证系统的安全性
3)简化用户对系统的使用
也就是使用视图，视图的属性可以使用别名
1)可以通过视图机制在设计用户视图时，重新定义某些属性的别名，使其更符合用户的习惯，以方便使用，
2)可以对不同级别的用户定义不同的视图，以保证系统的安全性
3)简化用户对系统的使用
也就是使用视图，视图的属性可以使用别名
4、物理设计方法
建立索引
1、静态建立索引
2、动态建立索引
建立聚集（分数据块）
把数据放在不同的数据块中
建立索引
1、静态建立索引
2、动态建立索引
建立聚集（分数据块）
把数据放在不同的数据块中
SQL与关系数据库基础操作
SQL概述
什么是SQL？
结构化查询语言（Structured Query Language，SQL）是专门用来与数据库通信的语言，它可以帮助用户操作关系数据库。
结构化查询语言（Structured Query Language，SQL）是专门用来与数据库通信的语言，它可以帮助用户操作关系数据库。
特点：
- 不是某个特定数据库供应商专有的语言
- 简单易学
- 强大、灵活，可以进行非常复杂和高级的数据库操作
组成：
- 数据查询
- 数据定义
- 数据操纵
- 数据控制
数据定义语言（DDL）
- CREATE
- ALTER
- DROP
数据操纵语言（DML）
- SELECT
- INSERT
- UPDATE
- DELETE
数据控制语言（DCL）
- GRANT 用于授予权限
- REVOKE 用于收回权限
MYSQL预备知识
使用基础：
关系数据库管理系统（RDBMS）
优点：
体积小、书速度快、开源、遵循GPL
LAMP：Linux Apache Mysql PHP、Python
WAMP：Windows Apache ...
常量：
字符串常量 使用单引号 ASCII和Unicode编码
数值常量 整数、浮点数
十六进制常量 x
时间日期常量 使用单引号
位字段值 bit
布尔值 bool true、false
NULL值 空值
变量：
用户变量， 用户定义的 使用@符号定义
系统变量， 使用@@符号定义
运算符：
算术运算符 + - * / %
位运算符 & | ^ ~ >> <<
比较运算符 = > < >= <= <> != <=>
逻辑运算符 NOT(!) AND(&&) OR(||) XOR（逻辑异或）
函数：
数学函数 ABS()
聚合函数 COUNT() SUM()
字符串函数 ASCII()
日期函数 NOW()
加密函数 ENCODE()
控制流程函数 IF()
格式化函数 FORMAT()
类型转换函数 CAST()
系统信息函数 USER()
关系数据库管理系统（RDBMS）
优点：
体积小、书速度快、开源、遵循GPL
LAMP：Linux Apache Mysql PHP、Python
WAMP：Windows Apache ...
常量：
字符串常量 使用单引号 ASCII和Unicode编码
数值常量 整数、浮点数
十六进制常量 x
时间日期常量 使用单引号
位字段值 bit
布尔值 bool true、false
NULL值 空值
变量：
用户变量， 用户定义的 使用@符号定义
系统变量， 使用@@符号定义
运算符：
算术运算符 + - * / %
位运算符 & | ^ ~ >> <<
比较运算符 = > < >= <= <> != <=>
逻辑运算符 NOT(!) AND(&&) OR(||) XOR（逻辑异或）
函数：
数学函数 ABS()
聚合函数 COUNT() SUM()
字符串函数 ASCII()
日期函数 NOW()
加密函数 ENCODE()
控制流程函数 IF()
格式化函数 FORMAT()
类型转换函数 CAST()
系统信息函数 USER()
数据定义
show datatables; 显示数据库
show columns from table_name; 显示表结构
数据库模式（数据库）定义
CREATE DATABASE
或CREATE SCHEMA
语法：
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[DEFAULT]CHARACTER SET[=]charset_name | [DEFAULT]COLLATE[=]collation_name
查看数据库
SHOW {DATABASES | SCHEMAS} [LIKE’pattern’ | WHERE expr]
选择数据库：
USE db_name;
修改数据库：
ALTER{DATABASE|SCHEMA}[db_name] alter_specification…
示例mysql>ALTER DATABASE mysql_test -> DEFAULT CHARACTER SET gb2312 -> DEFAULT COLLATE gb2312_chinese_ci;
删除数据库：
DROP{DATABASE|SCHEMA}[IF EXISTS]db_name
show columns from table_name; 显示表结构
数据库模式（数据库）定义
CREATE DATABASE
或CREATE SCHEMA
语法：
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[DEFAULT]CHARACTER SET[=]charset_name | [DEFAULT]COLLATE[=]collation_name
查看数据库
SHOW {DATABASES | SCHEMAS} [LIKE’pattern’ | WHERE expr]
选择数据库：
USE db_name;
修改数据库：
ALTER{DATABASE|SCHEMA}[db_name] alter_specification…
示例mysql>ALTER DATABASE mysql_test -> DEFAULT CHARACTER SET gb2312 -> DEFAULT COLLATE gb2312_chinese_ci;
删除数据库：
DROP{DATABASE|SCHEMA}[IF EXISTS]db_name
表定义
创建表
Create table Users (
Id int not null default 0 auto_increament,
name char(50) not null
primary key(id),
index index_seller(name) --给Name增加索引
);
Create table Users (
Id int not null default 0 auto_increament,
name char(50) not null
primary key(id),
index index_seller(name) --给Name增加索引
);
更新表
ALTER Table 主句
ADD[COLUMN] 子句
CHANGE[COLUMN]子句 修改表中列的名称或数据类型
ALTER[COLUMN]子句 修改或删除表中指定列的默认值
MODIFY[COLUMN]子句 只修改指定列的数据类型，不会干涉它的列名。
DROP[COLUMN]子句 删除表中多余的列。
RENAME[TO]子句 为表重新赋予一个表名
mysql>ALTER TABLE mysql_test.customers
->RENAME TO mysql_test.backup_customers;
ADD INDEX index_name(column_name); 给表添加索引
主语句： RENAME TABLE mysql_test.backup_customers TO mysql_test.customers;
ALTER Table 主句
ADD[COLUMN] 子句
CHANGE[COLUMN]子句 修改表中列的名称或数据类型
ALTER[COLUMN]子句 修改或删除表中指定列的默认值
MODIFY[COLUMN]子句 只修改指定列的数据类型，不会干涉它的列名。
DROP[COLUMN]子句 删除表中多余的列。
RENAME[TO]子句 为表重新赋予一个表名
mysql>ALTER TABLE mysql_test.customers
->RENAME TO mysql_test.backup_customers;
ADD INDEX index_name(column_name); 给表添加索引
主语句： RENAME TABLE mysql_test.backup_customers TO mysql_test.customers;
删除表
DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [，tbl_name]…
[RESTRICT | CASCADE]
DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [，tbl_name]…
[RESTRICT | CASCADE]
索引
定义
索引是提高数据文件访问效率的有效方法
索引存在的弊端：1）索引是以文件的形式存储的，如果有大量的索引，索引文件可能比数据文件更快达到最大的文件尺寸；2）索引在提高查询速度的同时，会降低更新表的速度。
索引类别
普通索引Index或key
唯一性索引Unique （候选码，可有多个）
主键Primary Key（只能有一个）
组合索引 （索引通常被创建成单列索引，也可以多个列组成）
创建索引：
CREATE [UNIQUE] INDEX index_name
ON tbl_name(index_col_name,…)
例：
mysql> CREATE INDEX index_customers
-> ON mysql_test.customers(cust_name(3) ASC);
查看索引：
SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name
[{FROM | IN} db_name] [WHERE expr]
删除索引：
DROP INDEX index_name ON tbl_name
例：mysql>DROP INDEX index_cust ON mysql_test.customers;
使用 ALTER TABLE 主语句后面加子句删除
DROP PRIMARY KEY --删除主键
DROP INDEX index_customers; --删除索引
例：
mysql>ALTER TABLE mysql_test.customers
-> DROP PRIMARY KEY,
-> DROP INDEX index_customers;
索引是提高数据文件访问效率的有效方法
索引存在的弊端：1）索引是以文件的形式存储的，如果有大量的索引，索引文件可能比数据文件更快达到最大的文件尺寸；2）索引在提高查询速度的同时，会降低更新表的速度。
索引类别
普通索引Index或key
唯一性索引Unique （候选码，可有多个）
主键Primary Key（只能有一个）
组合索引 （索引通常被创建成单列索引，也可以多个列组成）
创建索引：
CREATE [UNIQUE] INDEX index_name
ON tbl_name(index_col_name,…)
例：
mysql> CREATE INDEX index_customers
-> ON mysql_test.customers(cust_name(3) ASC);
查看索引：
SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name
[{FROM | IN} db_name] [WHERE expr]
删除索引：
DROP INDEX index_name ON tbl_name
例：mysql>DROP INDEX index_cust ON mysql_test.customers;
使用 ALTER TABLE 主语句后面加子句删除
DROP PRIMARY KEY --删除主键
DROP INDEX index_customers; --删除索引
例：
mysql>ALTER TABLE mysql_test.customers
-> DROP PRIMARY KEY,
-> DROP INDEX index_customers;
数据更新
插入数据
INSERT…VALUES语句
INSERT [INTO] tbl_name
[(col_name,…)] {VALUES | VALUE}({expr | DEFAULT},…),(…),…
INSERT…SET语句 --插入部分列值数据
INSERT [INTO] tbl_name SET col_name={expr | DEFAULT},…
INSERT…SELECT语句 --插入子查询数据
INSERT [INTO] tbl_name [(col_name,…)] SELECT…
INSERT [INTO] tbl_name
[(col_name,…)] {VALUES | VALUE}({expr | DEFAULT},…),(…),…
INSERT…SET语句 --插入部分列值数据
INSERT [INTO] tbl_name SET col_name={expr | DEFAULT},…
INSERT…SELECT语句 --插入子查询数据
INSERT [INTO] tbl_name [(col_name,…)] SELECT…
删除数据
DELETE FROM tbl_name [WHERE where_condition] [ORDER BY …] [LIMIT row_count]
修改数据
UPDATE tbl_name SET col_name1={expr1|DEFAULT}[,col_name2={expr2|DEFAULT}]… [WHERE where_condition] [ORDER BY …] [LIMIT row_count]
数据查询
SELECT语句
列的选择
内置函数‘
多表连接查询
交叉连接（笛卡尔积）：
mysql> SELECT * FROM tbl1 CROSS JOIN tbl2;
或
mysql> SELECT * FROM tbl1,tbl2;
内连接
select some_columns from table1 inner join table2 on some conditions;
外连接：
LEFT JOIN 左连
RIGHT JOIN 右连
左外连接：也称左连接。以左表为基表，在FROM子句中使用关键字“LEFT OUTER JOIN”或关键字“LEFT JOIN”来连接两张表。
右外连接：也称右连接。以右表为基表，在FROM子句中使用关键字“RIGHT OUTER JOIN”或关键字“RIGHT JOIN”来连接两张表。
交叉连接（笛卡尔积）：
mysql> SELECT * FROM tbl1 CROSS JOIN tbl2;
或
mysql> SELECT * FROM tbl1,tbl2;
内连接
select some_columns from table1 inner join table2 on some conditions;
外连接：
LEFT JOIN 左连
RIGHT JOIN 右连
左外连接：也称左连接。以左表为基表，在FROM子句中使用关键字“LEFT OUTER JOIN”或关键字“LEFT JOIN”来连接两张表。
右外连接：也称右连接。以右表为基表，在FROM子句中使用关键字“RIGHT OUTER JOIN”或关键字“RIGHT JOIN”来连接两张表。
查询条件：
子查询
分类：表子查询、行子查询、列子查询、标量子查询（一个值）
语句： in 、 not in 包含
expression IS [NOT] NULL 判断空值
EXISTS (subquery) 子查询结果集是否存在，子查询的结果集不为空为true
子查询
分类：表子查询、行子查询、列子查询、标量子查询（一个值）
语句： in 、 not in 包含
expression IS [NOT] NULL 判断空值
EXISTS (subquery) 子查询结果集是否存在，子查询的结果集不为空为true
GROUP BY子句与分组数据
GROUP BY{col_name | expr | position}[ASC | DESC],… [WITH ROLLUP]
position ：指定用于分组的列在SELECT语句结果集中的位置，通常是一个正整数
with rollup :可选项，指定在结果集中不仅包含由GROUP BY子句分组后的数据行，还包含各分组的汇总行，以及所有分组的整体汇总行，可以得到每个分组以及每个分组汇总级别的值。
GROUP BY{col_name | expr | position}[ASC | DESC],… [WITH ROLLUP]
position ：指定用于分组的列在SELECT语句结果集中的位置，通常是一个正整数
with rollup :可选项，指定在结果集中不仅包含由GROUP BY子句分组后的数据行，还包含各分组的汇总行，以及所有分组的整体汇总行，可以得到每个分组以及每个分组汇总级别的值。
limit 子句
LIMIT {[offset,]row_count | row_count OFFSET offset}
limit 0,10 从第几行开始看，看几行
LIMIT {[offset,]row_count | row_count OFFSET offset}
limit 0,10 从第几行开始看，看几行
视图
概念：
视图是数据库中的一个对象，它是数据库管理系统提供给用户的以多种角度观察数据库中数据的一种重要机制。
视图不是数据库中真实的表，而是一张虚拟表，其自身并不存储数据。
视图是数据库中的一个对象，它是数据库管理系统提供给用户的以多种角度观察数据库中数据的一种重要机制。
视图不是数据库中真实的表，而是一张虚拟表，其自身并不存储数据。
使用视图的优点：
集中分散数据、简化查询语句、重用SQL语句、保护数据安全、共享所需数据、更改数据格式
集中分散数据、简化查询语句、重用SQL语句、保护数据安全、共享所需数据、更改数据格式
语句：
创建：
CREATE VIEW view_name[(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
删除：
DROP VIEW [IF EXISTS] view_name [,view_name]… [RESTRICT | CASCADE]
修改：
ALTER VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL]CHECK OPTION]
查看视图定义：
SHOW CREATE VIEW view_name
创建：
CREATE VIEW view_name[(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
删除：
DROP VIEW [IF EXISTS] view_name [,view_name]… [RESTRICT | CASCADE]
修改：
ALTER VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL]CHECK OPTION]
查看视图定义：
SHOW CREATE VIEW view_name
使用INSERT语句通过视图向基本表插入数据
insert into 视图名称
更新数据 使用 update
删除数据 使用 delete
insert into 视图名称
更新数据 使用 update
删除数据 使用 delete
0 条评论
下一页
