Post

Databases（04735）

Posted
By Cc 38 min read

数据库系统原理（04735）

第一章数据库系统概述

第三节数据库系统的结构

模式：物理独立性 外模式：逻辑独立性

  • 数据库系统结构
    • 客户端系统结构
    • 浏览器/服务器结构
      • 表示层、处理层、数据层

第四节数据模型

数据模型的分类：概念层数据模型、逻辑层数据模型、物理层数据模型

  • 组成三要素
    • 数据结构：
    • 数据操作：
    • 数据约束：

  • 数据模型

    • 型：Type

    • 值：Value

    • E-R 图：实体型（矩形）、属性（椭圆）、联系（棱形）

      1.14

    • 逻辑模型

      • 层次模型
      • 网状模型
      • 关系模型
      • 面向对象模型

    • 物理模型

习题

  • DBMS 提供子模式描述语言来严格定义子模式
  • 关系数据库优点
    • 建立在严格数据概念基础之上
    • 关系模型对于透明、数据独立、保密性、简化了开发工作

第二章关系数据库（重点）

关系数据库概述

关系数据模型

关系数据库模型

  • 数据模型要素：数据结构、数据操作、数据约束
  • 组成要素：关系数据库结构、关系操作集合、关系完整性约束
  • 数据结构
    • 表名（唯一）
    • 列
    • 行
  • 关系数据模型
    • 表（table）：也成关系、而为数据结构，由唯一的表名、列、行
    • 关系（Relation）：关系逻辑对于的一张二维表，集本关系（基本表、基表），查询表、试图表
    • 列（Column）：也称字段（Field）或属性（Attribute），属性的个数为关系的元或度；列值称为属性值，其取范围称为值域
    • 行（Row）：也称元组（Tuple）记录（Record），是按照行存储的
    • 分量（Component）：元组（行 ）其中的一个值
    • 域（Domain）：表示属性取之范围
    • 数据类型（Date Type）：每列的数据类型一样的
    • 码或键（key）：属性得值都能用来唯一的标识该关系的元组
    • 候选码或者候选键（Candidate Key）： 多个列
    • 主属性、非主属性
    • 主码或主键（Primary key）：在若干个候选码中指定一个唯一标识
    • 全码或全键（All- Key）：关系模式的所有属性集合、主键或者主键
    • 外码或外键（Foreign Key）：另一个关系的主键
    • 参照关系和被参照关系
    • 关系模式：关系模式是型（Type）、关系是值（Value），关系模式怼关系描述、不断改变
  • 关系数据库所有关系的集合
    • 每一个属性都是不可分解的（不允许表中有表）
    • 每个关系属性必须命名，属性不同
    • 不允许 key 完全相同的元组
    • 顺序无关紧要、可以任意交换
    • 列无关紧要、可以任意交换

  • 关系操作集合

    • 基本操作
      • 查询（Query）：一次一集合（结果集）
      • 插入（Insert）
      • 删除（Delete）
      • 修改（Update）
    • 关系数据语言分类
      • 关系代数语言：操作对象、操作符、操作结果
      • SQL：结构化查询语言
      • 关系演算语言：元祖关系演算、域关系演算

    • 关系代数

      • 传统的集合运算
        • 并 - U ：去掉相同的元组（数据）
        • 差 - -
        • 交 - U ：找出两个元组（数据）
        • 笛卡尔积：R1*R2（第一个表的每一个行与第二个表的每一个行组合）

      • 专门的关系运算

        • 选择(select): σF(R)
          • 查询性别为男的：σsex=’男’(S1)
        • 投影 πA(R)
          • 选择列：π sname,age (S1)
        • 链接（join）⋈ n : 链接
        • 除：R/S
      • 关系完整性约束：正确性、相容性、一致性
        • 实体完整性主属性
        • 参照完整性约束

关系数据库的规范化理论

冗余和异常问题

  • 数据冗余
  • 更新异常
  • 插入异常
  • 删除异常

函数依赖

  • 完全依赖：
  • 部分依赖：

范式关系规范化过程

  • 第一范式（1NF）：每一行取值，不可再分的，存在非属性对码的部分函数依赖
    • 缺点：冗余高，插入操作异常、删除操作异常
  • 第二范式（2NF）：所有的非主属性完全依赖候选关键字
  • 第三范式：：每一个非主属性不传递函数依赖候选
    • 第三范式改进（BCNF）

第三章数据库设计

数据库设计概要

  • 概述：1-数据库分析与设计阶段；2-数据库实现与操作阶段
  • 目标：满足应用功能需求（存、取、删、改），改良数据库性能（数据高效存取和空间的节省共享性、完整性、一致性、安全保密性）
  • 内容：用户对数据需求出发，结构和行为设计
    • 结构设计（静态的）
      • 数据库概念结构设计
      • 逻辑结构设计
      • 物理结构设计
    • 数据库行为设计（动态的）：操作
  • 方法：
    • 直观设计方法（最原始的设计方法）
    • 规范设计法：普遍的、常用数据设计方法
      • 常见的有
        • 新奥尔良设计方法：需求分许、概念结构设计、逻辑结构设计、物理结构设计
        • 基于 E-模型设计方法
        • 基于第三范式的设计方法，结构法设计方法
    • 计算机辅助设计法（辅助软件工具->俗称【CASE】工具）
  • 过程
    • 需求分析
    • 概念结构设计
    • 逻辑结构设计
    • 物理结构设计
    • 数据实施
    • 数据库的运行和维护

数据库设计基本步骤

  • 需求分析（数据库设计的起点）
    • 作用：后续概念设计、逻辑设计、物理设计、数据库建立与维护的依据
    • 步骤
      • 确认数据库范围（第一项工作）
      • 应用过程分析
      • 收集与分析数据
      • 编写需求分析报告
  • 概念结构设计（概念模型）：最大限度满足应用需求
  • 逻辑结构设计：关系模型（层次、网状、关系数据来表示）
  • 物理设计：数据库在存储设备上的结构
  • 数据库实施
    • 加载数据
    • 应用程序设计
    • 数据库试运行
  • 数据库运行与维护：保证数据库的正常运行；最困难的工作室数据库重组与重构

数据库设计方法

  • 关系数据库设计过于各级模式
    • 概念模式
    • 逻辑模式
    • 内模式
  • 概念结构设计
    • E-R 图表示方法
      • 实体性、属性、联系
        • 实体性之间三种联系
          • 一对一联系（1:1）：可以在两个实体类型转换成两个关系模式中的任意一个关系模式的属性中加入另一个关系模式的键和联系类型的属性。
          • 一对多联系（1:N）：在 N 端实体类型转换成的关系模式中加入 1 端实体类型转换成的关系模式的键和联系类型的属性
          • 多对多联系（M:N）：将联系类型也转换成关系模式，其属性为两端实体类型的键加上联系类型的属性，而键为两端实体键的组合
    • 局部信息结构设计
    • 全局信息结构设计
  • 逻辑结构设计方法
    • 主要三项
      1. 将 E-R 图转换为关系模型
      2. 对关系数据模型进行优化
      3. 设计面向用户外模式
  • 物理设计方法
    • 建立索引（逻辑联系）
    • 建立聚集（物理聚集）

第四章 SQL 与关系数据库基本操作（重点）

SQL 概述（Structured Query Language，SQL）

结构化查询语言， SQL3 颁布标准->1999 年

  • 特点：SQL 不是某个特定数据库供应商专有的语言
  • 组成
    • 数据查询
    • 数据定义语言（DDL）
      • cretate ：创建数据库或数据库对象
      • alter：修改数据库或数据库对象进行修改
      • drop：删除数据库的数据对象
    • 数据操纵（DML）：用于操纵数据库中各种对象，特别是检索和修改数据
      • select：从表试图中检索数据
      • insert：将数据插入表或试图
      • update：修改表活着试图中的数据
      • delete：从表或试图中删除数据
    • 数据控制（DCL）：主要是权限
      • grant：用于授予权限
      • revoke：用于收回权限

MySQL 预备知识

关系型数据库：体积小、速度快、开放源代码

  • MySQL 基础
    • 使用架构（LAMP、WAMP）；Linux、Apache、MySQL、（PHP、Perl、Python）、Windows
    • 扩展语言要素
      • 常量：也称字面值或标量
        • 字符串：单引号、双引号括起来的字符序列
        • 数值：整数常亮、浮点数常量
        • 十六进制：每对十六进制数字转换为一个字符，X***
        • 时间日期：用单引号将表示日期自负串号起来构成（例如：’2018-06-05’）
        • null 值：表示“没有值”、”无数据“
      • 变量：临时存储数据，随着程序变化而变化；变量属性有名字、数据类型
        • 用户变量：用户变量前常添加一个符号@用于区分
        • 系统变量：大多数系统变量应用于其他的 SQL 语句时，必须在系统变量前添加两个@
    • 运算符
      • 算数运算符
      • 位运算符
      • 比较运算符
      • 逻辑运算符
    • 表达式：常量、变量、列名、复杂计算、运算符、函数的组合
      • 字符型
      • 数值型
      • 日期型

数据定义

  • 数据库模式定义

    • 创建数据库（create）

      • if not

    • 选择数据库（use）: use test;

    • 修改数据库（alter）：alter database test default charcter set gb2312

    • 删除数据库（drop）: drop database test

    • 查看数据库（show）：show databases

      create database if not exists mysql_test;
use mysql_test; # 选择并且使用数据库
alter database mysql_test default character set utf8;
drop database mysql_test;
drop database if exists test;

    • 表定义

      • 查询表结构

        show create table mysql_test.customers;
show columns from mysql_test.customers;
desc mysql_test.customers;

      • 创建表

        create table customers (
  cust_id int not null auto_increment,# 设置自增，自动编号
  cust_name char(50) not null,
  cust_sex char(1) not null default 0,# 指定默认值
  cust_address char(50) null,
  cust_contact char(50) null,
  primary key (cust_id) # 主键
);

      • 修改数据表和更改值

        alter table mysql_test.customers add cust_city char(50) not null default 'Wuhan' after cust_sex;# 添加一列，不能为空、默认值为'wuhan'、在'cust_sex'之后
alter table mysql_test.customers change cust_sex sex char(1) null default 'm'; # 修改列名字，并且设置新的属性
alter table mysql_test.customers alter column cust_city set default 'Beijing';# 修改字段属性
alter table mysql_test.customers modify column cust_name char(20) first;
alter table mysql_test.customers drop column cust_contact;# 删除多余的列
alter table mysql_test.customers rename to mysql_test.backup_customers;# 修改表名
rename table mysql_test.backup_customers to mysql_test.customers;# 修改表名
drop table customers; # 删除表
drop table if exists customers; #  询问是否存在再删除

    • 索引定义

      • 优点：索引是提高数据文件访问效率的有效方法

      • 缺点

        • 以文件的形式存储，如果有大量的索引，索引文件可能比数据文件更快达到最大的文件尺寸
        • 在提高查询速度的同时，会降低更新表的速度

      • 分类：索引通常呗创建成单列索引和组合索引

      • 类型

        • 普通索引 index 或 key
        • 唯一性索引 unique
        • 主键 primary key

      • 创建

        create index index_name on mysql_test.customers(cust_name(3) asc ); # 索引升序
create index index_name_and_address on mysql_test.customers(cust_name,cust_address);# 创建组合索引
alter table mysql_test.customers add index index_seller_name(cust_contact);
alter table customers add cust_index char(2);# 先添加临时字段
alter table mysql_test.customers add constraint index_unique unique(cust_index);# 修改表时添加唯一字段

      • 查看

        show index from mysql_test.customers;

      • 删除

        drop index index_unique on mysql_test.customers;
alter table mysql_test.customers drop index index_name;# 使用alter

数据更新（重点）

  • 插入数据（insert）
    • insert … values：insert into mysql_test.customers values(901,'Cc','F','jiangxi','jiujiang');
    • insert … set：insert into mysql_test.customers set cust_name="Lcc" ,cust_address='beijing',cust_sex=default;
    • insert … select：insert into customers(cust_id,cust_name,cust_sex,cust_address,cust_contact) select cust_id,cust_name,cust_sex,cust_address,cust_contact from customers_back;
  • update：update customers set cust_name="HongCheng" where cust_id=902;
  • delete：delete from customers where cust_name='ChaoChengLi';

数据查询

-

定义：从数据库的一张或多张（试图）中检索出满足条件的数据信息，通常查询的结果是由 0 行（没有满足条件的数据）或多行记录组成的一个记录集合，允许选择一个或多个座位输出字段；使用灵活数学理论基础是关系数据模型中对表对象的一组关系运算，即选择投影和连接

  • select：select [列名|*] from table_name where 条件 group by 列名 having 条件 order by 列名 limit offset,rowcount

  • 查询数据

    • 查询内容
    select cust_name,cust_address from customers;#指定列
select * from customers;# 通配符（全部）
select cust_name as name ,cust_address as address from customers;# 别名查询
select case when cust_sex='F' then '男' else '女' end as sex from mysql_test.customers;
# case 表达式(cust_sex内容等于'F'，则显示男，否则显示女)
select case cust_id when 902 then "一" when 903 then '二' else '其他' end as name from customers;
select cust_name,cust_sex ,cust_id+100 from customers;# 查处结果上cust_id都加上100

    • 聚合函数

      函数名 说 明
      COUNT 求组中项数，返回 INT 类型整数
      MAX 求最大值
      MIN 求最小值
      SUM 返回表达式中所有值的和
      AVG 求组中值的平均值
      STD 或 STDDEV 返回给定表达式中所有值的标准值
      VARIANCE 返回给定表达式中所有值的方差
      GROUP CONCAT 返回由属于一组的列值连接组合而成的结果
      BIT AND 逻辑或
      BIR OR 逻辑与
      BIT XOR 逻辑异或 
      select count(cust_id) from customers;
select count(*) from customers;
select sum(cust_id) from customers;
select min(cust_id) from customers;
select avg(cust_id) from customers;

    • from 子句与多表连接查询

      # 准备
drop table tbl1,tbl2;
create table tbl1 (id int,sex int);
create table tbl2 (id int,name char(30));
insert into tbl1 values(1,0),(2,1),(3,0),(4,1);
insert into tbl2 value(1,'Cc'),(2,'CH'),(3,'ChaoChengLi'),(5,"Test");

      • 交叉连接（笛卡尔积）:两张表记录行数的乘积

        select * from tbl1 cross join tbl2;
select * from tbl1 join tbl2;

      • 内连接：通过设置连接条件，来移除查询结果中某些数据之后交叉连接；如果on子句中的连接条件使用运算符=方式为等值连接，其他的为非等值连接

        select * from tbl1 inner join tbl2 on tbl1.id=tbl2.id;#可以看到只查出了1，2，3
        • 自连接：可以将一个表与它本身进行连接，称自连接

      • 外连接：交叉连接时，有时会输出不满足的条件记录

        • 左外连接：左表为基础，from中使用left outer join或left join
        select * from tbl1 laft join tbl2;
        • 右外连接：有表为基础，from 中使用right outer join或right join

    • where 子句与条件查询

      • 比较运算符

        比较运算符 说 明
        = 等于
        ＜＞ 不等于
        != 不等于
        ＜ 小于
        ＜= 小于等于
        ＞ 大于
        ＞= 大于等于
        ＜=＞ 不会返回 UNKNOWN 
        insert into customers values(901,"Cc",'M',"japan",'sss'),(904,"ChaoCheng",'M',"us",'ttt');
select * from customers where cust_sex='M';# 查找男性用户
select * from customers where cust_id between 901 and 903;# 判断范围
select * from customers where  cust_id in (901,903);# 查询指定
select * from customers where cust_contact is null; # 查询 联系为空的
select * from customers where cust_contact is not null;# 查询 联系不为空的

      • 子查询：把查出来的结果作为匹配的对象再次查询

        select id from tbl1 where id  in (select id from tbl2 where id > 0);# in 自查询结果集
select * from tbl1 where id = 1 and exists (select id from tbl2 where id > 0);# 查询此值是否在自查询结果中
        • group by子句与分组：结果集中的数据根据选择列进行逻辑分组
        update customers set cust_address='beijing' where cust_id=901;
select cust_address,cust_sex,count(*) as '人数' from customers group by cust_address,cust_sex;# 地址相同的男性和女性用户数量
select cust_address,cust_sex,count(*) as '人数' from customers group by cust_address,cust_sex with rollup;# 在group by 上面再统计

        • having

          select cust_name,cust_address from customers group by cust_address,cust_name having count(*) <= 1;

        • having VS where

          • having
            • 过滤分组
            • 可以包含聚合函数
            • 在数据分组后进行过滤
          • where
            • 过滤数据行
            • 不可以包含聚合函数
            • 在数据分组前进行过滤

        • order by子句：排序

          select cust_name,cust_sex from customers order by cust_name desc ,cust_address desc;

        • limit : 限制 select 语句返回行数，两个参数（1-偏移量、2-最大数目），初始偏移量是 0（而不是 1）

          select * from customers limit 2,2;# 偏移2个,最大数目2
select * from customers limit 3; # 一个参数只显示最大记录数目

视图

视图是数据库中一个对象，它是数据库系统提供给用户的一朵中角度观察数据的一种机制，视图不是数据库真实的表，而是一张虚拟表，其自身并不存储数据

  • 优点

    • 集中分散数据
    • 简化查询语句
    • 重用 SQL 语句
    • 保护数据安全
    • 共享所需数据
    • 更改数据格式

  • 操作

    # 创建视图
create or replace view customers_view as select * from customers where cust_sex='M' with check option;
create view customers_view_back as select * from customers where cust_sex='M' with check option;
# delete
drop view customers_view;
drop view customers_view_back;
# 查看
show create view customers_view;
# 插入
insert into customers_view value(910,'MySql','M','US','jp');
# 更新；更新视图内容会更新基表内容，基表内容修改也会修改视图内容
update customers_view set cust_address='Ch';
# 删除；对于依赖多个基表的视图，不能使用delete语句
delete from customers_view where cust_name='Cc';
# 查询
select * from customers_view where cust_id=902;

  第五章数据库编程（难点）

存储过程

存储过程是一组为了完成某项特定功能的 SQL 语句集，其实质就是一段存储在数据库中的代码；存储过程可以有声明式的 SQL 语句（update 、create 、select）和过程式的 SQL 语句（if … then … else）组成。这组语句集经过编译后存储在数据库中，用户只需要通过指定存储过程的名字并给定参数（参数可有可无），可随时调用并执行，而不需要重新编译

  • 优点

    • 可增强 SQL 语言的功能和灵活性
    • 良好的封装性
    • 高性能
    • 可减少网络流量
    • 可作为一种安全机制来确保数据库的安全性和数据的完整性

  • 创建

    # 创建
delimiter $$ # 定义结束符 delimiter !! 但是需要和end !! 对应起来
create procedure sp_update_sex(in cid int,csex char(1))
begin # 开始
update customers set cust_sex=csex where cust_id=cid;
end $$ # 结束

  • 存储过程体

    • 使用declare语句声明局部变量：declare cid int (10);

      • 只能在存储过程体的begin...end语句块声明
      • 必须在存储过程的开头处声明
      • 不同于用户变量
        • 局部变量：前面没有@符号，只能在存储过程体的begin...end语句块使用
        • 用户变量：前面有@符号，同时已声明的用户变量存在于整个会话中

    • 使用set语句为局部变量赋值（set cid=911）

    • select into 语句把选定列值直接存储到局部变量中

    • 流程控制语句

      • 条件判断：if then else ；end if
      • 循环：while 、repeat 、loop；iterate 退出当前循环，且重新开始下个循环

    • 游标（cursor）

      在 mysql 中一条 select…into 语句中成功执行后，会返回带有一行数据，这行数据可以被读取到存储过程中进行处理，然后在使用 select 语句进行数据检索时，若该语句成功被执行，则会返回一组结果集的数据行，该结果集拥有多行数据，这数据无法直接一行一行进行处理，此时需要使用游标；游标是一个 select 语句检索出来的结果集，在存储了游标后，应用程序或用户就可以更具需要滚动或浏览器中数据

      • 声明
      • 打开：open cursor_name
      • 读取：fetch cursor
      • 关闭：close cursor_name
      use mysql_test;
delimiter $$ # mysql 默认是; delimiter ;
create procedure sp_sumofrow (out rows int)
begin
declare cid int
declare found boolean default true
declare cur_cid cursor for # 创建游标
select cust_id from customers;
declare continue handler for not found
set found=false
set rows=0
open cur_cid
fetch cur_id into cid
while found do
set rows=rows+1
fetch cur_cid into cid
end while
close cur_id;
end $$
delimiter;
call sp_sumofrow(@rows);
      • 注意事项
        • 只能用于存储过程或者存粗函数，不能单独在查询操作中使用
        • 使用中可以用多个游标，但是在 begin…end 语句块每个游标的名字必须时唯一的
        • 不是一条 select 语句，是被 select 语句检索出来的结果集

    • 调用存储过程、删除、查看

      show procedure status;# 查看所有详情
show create procedure sp_update_sex;# 查看
call sp_update_sex(909,'M');# 调用
drop procedure sp_update_sex; # 删除
drop procedure if exists sp_update_sex; #  删除

存储函数

存储函数与存储过程一样，组成代码片段，调用语句

  • 存储过程与存储函数区别

    • 存储函数不能有参数，因自身就是输出函数参数；过程可以拥有输出参数
    • 可以直接对存储函数调用，不需要call语句；存储过程需要call调用
    • 存储函数必须包含return语句；存储过程必须要包含这个特殊语句

  • 操作

    # 创建
use mysql_test;
delimiter $$
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 ;
# 调用
select fn_search(902);
# 删除
drop function fn_search;
drop function if exists fn_search;

第六章数据库安全与保护

数据库完整性：数据的正确性和相容性

  • 作用：防止数据库中存在不符合定义的数据
  • 约束的作用对象
    • 列级约束：列类型、取值范围、精度等约束
    • 元组约束：元组中的各个字段之间的相互约束
    • 表级约束：若干个元组，关系之间的联系约束

  • 核心：实体完整性、参照完整性、用户定义性的完整性

    • 实体完整性：通过主键约束和候选键约束实现（规则如下）
      • 每个表只能定义一个主键
        • 主键：一个表只能有一个；primary key
        • 候选键：可以定义若干个；unique
      • 主键的值（键值）必须能够唯一标志表每一行记录，且不能为空
      • 复合主键不能包含不必要的多余的列
      • 一个列名在复合主键的列表中只能出现一次

    • 参照完整性：在通过创建表或更新表同时定义一个外键声明实现

      • 方式

        • 在某个列定义属性后直接加上reference definition语法
        • 在表列属性定义后添加foreign key(index_col name...)
        use mysql_test;
create table orders (
  order_id int not null auto_increment,
  order_product char(50) not null,
  order_product_type char(50) not null,
  cust_id int null,
  order_date datetime not null,
  order_price double not null,
  order_amount int not null,
  primary key(order_id),
  foreign key (cust_id) references customers(cust_id) on delete restrict on update restrict);

      • 完整性约束

        • 非空约束：not null
        • check：check （expr）
        • 触发器

      • 命名完整性约束（指定约束的名字）

      • 更新完整性约束

        • 完整性约束不能直接修改
        • 使用alter table语句，可以独立的删除完整性约束，而且不会删除表本身（使用 drop table 删除表，会自动删除）

触发器（Trigger）

在关系表上的一类由事件驱动的数据库对象，也是一种保证数据完整性的方法，一旦定义无需用户调用，任何对表的修改操作均有数据库服务器自动激活相应的触发器；触发器与表的关系十分密切，其主要作用是实现主键和外键不能保证负责的参照完整性和数据一致性，从而有效地保护表中的数据

  • 注意：创建中，每个表每个事件每次只允许一个触发器，因每个表最多支持6个触发器（insert、update、delete）之前，之后

  • 操作

    # 创建
create trigger customers_insert_trigger after insert on customers for each row set @str='one customers added!'
# 删除
drop trigger customers_insert_trigger;
drop trigger if exists customers_insert_trigger;
# insert
create trigger customers_insert_trigger after insert on customers for each row set @str=new.cust_id;
# update
create trigger customers_update_trigger before update on customers for each row set new.cust_address=old.cust_contact;

安全性与访问控制：防止不合法的操作而造成数据泄漏，更改或破坏

create user 'Cc'@'localhost' identified by 'password';# 创建
show grants for 'Cc'@'localhost';# 查看
set password for 'Cc'@'localhost' = '123456asdf';# update password
drop user 'Cc'@'localhost';# 删除
grant select (cust_id,cust_name) on mysql_test.customers to 'Cc'@'localhost';# 授权 目标用户需要重新登录
select cust_id,cust_name from mysql_test.customers; #  授权登录用户查看下结果
grant all on mysql_test.* to 'Cc'@'localhost';#  授权mysql_test 库的所有操作权限
grant create user on *.* to 'Cc'@'localhost'; # 创建用户的权限

revoke select on mysql_test.* from 'Cc'@'localhost';# 撤回权限

事务与并发控制：事务保证数据一致性的基本手段

用户定义操作序列，这些操作作为一个完整工作但愿，要么全部执行，要么全部不执行，是一个不可分割的工作单位，事务中的操作一般是对数据的更新操作（增、删、改）

  • 特征（ACID）
    • 原子性：事务时不可分割的最小工作单位
    • 一致性
    • 隔离性
    • 持续性（永久性）
  • 锁->做常用的并发控制
    • 含义：事务通过向系统请求对他的希望的数据加锁，以确保他的不被非预期改变
    • 锁：一个锁是实质上是允许阻止一个事物对一个数据对象存取特权
    • 类型
      • 排他锁（X 锁）用于写操作
      • 共享锁（S 锁）用于读操作
    • 原理
      • 事务 T 对 D 数据加 X 或 S 锁，必须等待事务释放锁才能进行写或者读
      • 事务一直占有获得锁直到结束是释放锁
    • 粒度：单元数据大小；锁粒度越细，并发性越大，软件复杂性和系统开销也就越大
    • 锁级别（一致性或隔离性）：0 ～ 3 级封锁
    • 死锁和活锁
      • 活锁：活锁处理方案—-先来先服务
      • 死锁：活锁的预防方案
        • 一次性锁请求
        • 锁请求排序
        • 序列化处理
        • 资源剥夺
      • 死锁检测：图论的方法检测死锁，并以正在执行的事务为节点
    • 可串行性：基本操作的一种排序
      • 两段封锁法
        • 发展（Growing）或加锁阶段
        • 收缩（Shrinking）或放缩阶段

备份与恢复：保证数据库的可靠性和完整性

  • 数据丢失的途经：计算机硬件故障、计算机软件故障、病毒、认为误操作、自然灾害、盗窃

  • 概念：数据备份通过数据导出数据或复制表文件的方式来制作数据的副本；数据库的恢复是以备份为基础的，与备份相对应的系统维护和管理操作

  • 操作

    SELECT * FROM mysql_test.customers
INTO OUTFILE '/opt/work/backupfile.txt'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY ""
LINES TERMINATED BY '?';

  • 需要注意：多个用户使用数据库情况下，为了得到一个一致的备份，需要在指定表上使用lock tables table name read语句做一个读锁定，为防止其他用户更新，而当恢复时，则需要使用lock tables，table name write语句做一个写锁定，以避免发生数据冲突，数据库备份或恢复完毕之后，需要使用unlock tables 语句对该表进行解锁

第七章数据库应用设计与开发实例

第八章数据库管理技术的发展

Cc, SelfStudy, ComputerBasics
SelfStudy
This post is licensed under CC BY 4.0 by the author.