MySQL复习

---

第一模块：数据库基础知识+关系数据库概念

---

数据库特点

1. 数据结构化
2. 数据的共享性高，冗余度低，易扩充
3. 数据独立性高（数据的物理独立性和逻辑独立性）
4. 数据由 DBMS统一管理和控制

数据库发展历程

数据库模型（层次模型、网状模型、关系模型）

• 层次模型以“树结构”表示数据之间的联系。
• 网状模型是以“图结构”来表示数据之间的联系。
• 关系模型是用“二维表”(或称为关系)来表示数据之间的联系的。

数据库体系结构（模式、外模式、内模式）

基于数据库三级模式的2层隔离带来的逻辑独立、物理独立，与逻辑独立相关的数据库逻辑结构的可扩充性，与物理独立相关的数据库物理平台的可迁移性。

物理独立性是指数据在磁盘上的数据库中如何存储是由DBMS管理的，用户程序不需要了解，应用程序要处理的只是数据的逻辑结构，这样一来当数据的物理存储结构改变时，用户的程序不用改变。

逻辑独立性是指用户的应用程序与数据库的逻辑结构是相互独立的，也就是说，数据的逻辑结构改变了，用户程序也可以不改变。

二维关系模式（简称：模式）

模式中的行（元组、记录）、模式中的列（属性、字段）。其中，列是模式的结构要素，是模式最小的结构单位，行是模式的数据要素，是最基本的数据单位。由多个列形成列集，也叫属性集、字段集。由多个行形成行集，也叫元组集、记录集。

键

键的概念，由列（列集）定义。从候选键到主键，其值具有唯一性，并带来行的唯一性。一个数据表可有多个候选键，并从这些候选键中产生出一个主键。
外键，主表主键引入到从表，产生外键，用于建立从表到主表的关联。

数据库完整性

实体完整性：数据表中行的唯一性，通过主键建立。
参照完整性：数据表之间数据的相关性、一致性，基于从表外键及从表到主表的关联建立，涉及主表到从表的级联操作，从表对主表的操作限制等约束性规则。
自定义完整性：其他数据约束。例：基于触发器带来的数据约束。

关系代数

关系模式的交、并、差、笛卡尔积、投影、选择等基本运算，关系模式的连接运算。

http://t.csdn.cn/Ve3lU

---

第二模块：SQL语言

涉及数据库结构定义（DDL）、数据操作（DML）

---

数据库结构定义（数据库模式定义语言Data Definition Language）

1. 数据库创建，create database
2. 数据表创建，create table
3. 数据视图创建，create view 视图名 as select...;
4. 数据索引创建，create index 索引名 on 表名(列1,列2...);

以上SQL语言，必须完全掌握，不仅搞懂知道，而且能用。

例1：创建数据库“学生管理”并建表class,student,course,teacher,study,teach

create database 学生管理;
use 学生管理;
show datebase 学生管理;
#删库drop database 学生管理;

create table class
(classno char(12) not null,
classname char(20) not null,
primary key(classno)
);

create table student
(
studentno char(12) not null,
studentname char(20) not null,
classno char(12) not null,
primary key(studentno),
constraint foreign key(classno) references class(classno)
);

create table course
(
courseno char(12) not null,
coursename char(20) not null,
credit float(2,1) default 0,
primary key(courseno)
);

create table teacher
(
teacherno char(6) not null,
teachername char(20) not null,
primary key(teacherno)
);

create table study
(
studentno char(12) not null,
courseno char(12) not null,
score float(3,1)default 0,
primary key(studentno,courseno)
);

create table teach
(
courseno char(12) not null,
teacherno char(6) not null,
period int(2) not null,
primary key(courseno,teacherno)
);

例2：修改表结构

show table;//查表清单
show create table;//查表创建SQL语句
describe student;//查表结构

alter table student
add 电话 char(13) null;//增加列

alter table course
modify credit int(3);//修改列

alter table course
change credit 学分;//改列名

alter table student
drop 电话;//删除列

//drop table course;删除表结构
//truncate table course;截断表：清除表中记录（行）

数据操作（数据操纵语言Data Manipulation Language）

1. 写数据操作：插入记录，insert into;更新记录，update;删除记录，delete
2. 读数据操作：select语句，多表连接查询，无重复行查询、嵌入查询（子查询），分组查询，聚合函数汇总统计。  

例3：写数据

insert into class(classno,classname) values('01','一班'),('02','二班'),('03','三班');

update study set score=score+10
where courseno='78'&&score>=50&&score<60;

delete from study where courseno='78';

例4：读数据

select * from student;
select distinct credit from course;//无重复行
select score as '分数',studentno as '学号' from study where score>80;
#内连接查询
select student.studentno,studentname,score 
from student 
inner join study on student.studentno=study.studentno 
where study.courseno='76';
#子查询
select studentno,sname,phone,Email 
from student
where studentno in (select studentno from score where final>93);
#分组查询
select studentno 学号,courseno 课程号,score 分数 
from study 
where score<60 
order by score desc;
#聚合函数汇总查询
select courseno 课程号,avg(score) 平均分 
from study 
group by courseno 
with rollup;

以上部分必须完全掌握，并能灵活应用。

---

第三模块：数据库设计

包括概念建模（ER图），逻辑建模（数据表关联图），逻辑模型优化。

---

概念建模

概念模型的建模元素是实体、联系、属性，其来源现实世界。

从现实世界中找到具有独立性的元素，其为实体。从现实世界实体的活动中找联系，从实体、联系的特征中找属性。

逻辑建模

基于概念模型可映射逻辑模型。涉及1：1映射，1：n映射，n：m映射。

逻辑模型优化

基于数据表范式规则进行优化。设计中涉及有1NF，2NF，3NF，BCNF。

这个部分举了3个例子：学生管理数据库模型、物品入出库管理数据库模型、影院售票管理数据库模型。大家可依据这3个模型体会并深入理解数据库设计。

---

第四模块：数据库编程

MySQL系统变量、用户会话变量、用户局部变量，MySQL流程控制语句，MySQL函数、存储过程、触发器，MySQL游标，MySQL异常处理设置语句。

---

函数，存储过程，是依附于数据库建立的程序。必须建立了数据库，并在这个数据库成为当前数据库后，才能在这个数据库中建立函数，存储过程。函数自身有返回，一般嵌入sql语句调用。存储过程自身无返回，但可通过参数返回，需要通过call语句调用。

例5：创建一个名为func_course的函数返回表course中的指定课程号的课程名。

delimiter &&
create  function func_course(c_no varchar(6))
　returns  char(6)
   begin
   return  (select  cname  from  course
　where  courseno =c_no);
　end &&
delimiter ;

例6：创建存储过程avg_score，输入课程号后，统计该课程的平均成绩。

delimiter //
create procedure avg_score(in c_no char(6))
　begin
　select courseno,avg(final) from score where courseno=c_no ;
　end //
delimiter ;
#调用存储过程avg_score ()的代码：
call avg_score('c05109');

触发器是基于数据表创建，触发器涉及触发事件（insert、update、delete），触发时间（before、after）。触发器涉及两个临时表，new、old。insert事件只有new表，delete事件只有old表，update事件有new、old两个表。

例7：创建一个触发器，当更改表course中某门课的课程号时，同时将score表课程号全部更新。

delimiter $$
create trigger cno_update after update
on course for each row
begin
update  score set courseno=new.courseno 
where courseno=old.courseno;
end $$
delimiter ;

数据库编程还涉及事务机制与并发控制。事务是一个任务单位，具有原子特性，为一个整体。事务原子特性是其最基本特性，并可延伸出一致性、隔离性、持久性等特性。默认状态下，每条sql语句就是一个事务，为自动提交事务。可通过start transaction语句启动用户提交事务，使多条语句组成一个事务，直到commit语句或rollback语句，结束事务。commit为提交事务，rollback为回退事务。
并发控制是指多个事务同时发生。这时事务之间可能会发生数据冲突，并带来数据异常。如丢失更新、不可重复读、读脏数据、幻读。为解决事务并发冲突，需要对事务进行有效隔离，有4个隔离级别，可通过set session transaction isolation level …语句进行事务隔离级别设置。

---

锁机制

X锁，就是排他锁（写锁、独占锁），用于写数据操作（insert，update，delete）。

S锁，就是共享锁（读锁），用于读数据操作（select）。

锁的粒度，锁数据大小。有：表锁，锁表。行锁，锁行。

事务中对数据加锁，可以解决事务并发冲突。

但两个事务对同样的数据（表、行）加锁，也可能带来死锁。X事务需要对a、b数据加锁，Y事务需要对b、a事务加锁，X事务在对a加锁后等待Y事务解锁b，Y事务对b加锁后等待X事务解锁a，这种相互等待就可能形成死锁。
可通过顺序加锁，即X事务按顺序对a、b加锁，Y事务也是按顺序对a、b加锁，解决死锁问题。
也可采用一次性加锁，即X事务对a、b同时加锁，其完全解锁后，Y事务再对a、b加锁。这也可以解决死锁问题。
 

#表锁：
lock table 学生 read；//这是对学生表加S锁。
lock table 课程 write；//这是对课程表加X锁。

unlock tables；//这是解除表锁。

#行锁：
start transaction；
select 物品名称，存量  from 物品 where 物品编号='w19010' lock in share mode;
//上面是对读取的行加S锁。

start transaction；
select 物品名称，存量  from 物品 where 物品编号='w19010' for update;

//上面是对读取的行加X锁。

MySQL中的行锁，是在显性启动事务后才能加行锁。并在结束事务后解除行锁。事务结束包括：提交事务（commit），或回退事务（rollback）。