Mysql数据库

数据库定义

依赖关系

平凡，非平凡函数依赖

在关系模式R(U)中，对于U的子集X和Y，

如果X→Y，但Y 不属于 X，则称X→Y 是非平凡的函数依赖

若X→Y，但Y 属于 X, 则称X→Y是 平凡的函数依赖

非平凡函数依赖 ：(学号, 课程号) → 成绩

平凡函数依赖 ：(学号, 课程号) → 学号 (学号, 课程号) →课程号

完全函数依赖，部分函数依赖

定义 在R(U)中，如果X→Y，并且对于X的任何一个真子集X’，

都有X’ Y, 则称Y对X完全函数依赖，记作X ->F Y。

若X→Y，但Y不完全函数依赖于X，则称Y对X部分函数依赖，记作X ->P Y。

完全函数依赖:

(学号，课程号)→成绩，并且 学号 不决定 成绩 ，

课程号 不决定 成绩

则(学号, 课程号) ->f 成绩

部分函数依赖:

(学号, 课程号)→ 姓名，学号→姓名

则(学号, 课程号) ->p 姓名

传递函数依赖

在R(U)中，如果X→Y，(Y 属于X) ,Y ->X, Y→Z， 则称Z对X传递函数依赖。记为：X →T Z

传递函数依赖:

学号→学院，学院→院长，并且 学院 不决定 学号

则学号 ->T 院长

关系模式规范化

第一范式(1NF)

R满足关系的每一分量是不可再分的数据项，则称R是第一范式的，记作R∈1NF

第二范式(2NF)

若关系模型R∈1NF，并且不存在非主属性部分函数依赖于R的码，则R∈2NF。

第三范式(3NF)

若关系模型R∈2NF，并且不存在非主属性传递函数依赖于R的码，则R∈3NF。

SQL语句

数据定义语句（DDL）

创建（create）

建数据库
1
2
3
Create database <database_name>;

Create database sale；
建表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create table table_name
(列名 数据类型 [列级约束]
[,列名 数据类型 [列级约束],……] 
[,表级约束（列名）] 
)
例：
CREATE TABLE  students(
sno char(7)  primary key,   
sname  varchar(8) not null,      
ssex   char(2) not null, 
score decimal(4,1) ,
class varchar(10) not null ,
SID char(18) not null ,
PHOTO varchar(50) ,
fingerp blob
)
复制表
1
2
3
4
5
6
7
8
9
只复制表结构：create table 新表名 like 已有表名；

复制表结构，并复制数据：
  create table 新表名 as select 语句； 
例1：
复制vip表的结构生成vip1，
并复制goods表中所有数据生成goods1。
Create table vip1 like vip；
Create table goods1 as select * from goods；
约束
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
//唯一约束 unique（值不重复，允许空值）

//非空约束 not null（不允许空值，默认情况下为空）

//默认约束 default（当新输入的数据没指定值时，就将默认值存储在数据表中）
Sex char（2） default ‘女’，

//自动增长 auto_increment（根据设置的不长自动增长，数据类型需为整型）

//检查约束 check（设置该值的取值范围）
Sex char（2） not null check（sex=‘男’ or sex=‘女’），
只能取值“男”或“女”。

// foreign key （外键约束的列名） references 主键表（主键表中的主键列名）
参照关系和和被参照关系可以是同一张表。

//主键约束

修改（alter）

1、一个alter table只能跟一个操作；

2、alter table后面可以跟多个add；

3、一个drop column可以删除多个字段

4、一个alter table只能修改一个字段

对字段（列）的操作

添加列

1
2
3
4
5
6
7
8
9
Alter table sale
add 属性名 数据类型 [列的完整性约束] 

例：
Alter table sale
Add id int primary key first，
Add Oid char（6） after number

关键字first表示位于某一列的前面，如果加入的字段需要位于某一列的后面，可以使用after

修改列

1
2
3
4
5
6
Alter table sale
modify [column] 属性名 新数据类型 

将sale的oid长度改为7，类型不变
Alter table sale
modiy Oid char（7）；

删除列

1
2
3
4
drop column 属性名

Alter table sale
Drop column Oid；

修改列名

1
2
3
4
5
6
alter table 表名 
change 旧属性名 新属性名 数据类型

例：将good表的classid改名为class。
alter table good 
change classid class varchar(20)
修改表名
1
2
3
4
Ø语法：alter table 旧表名 rename [to] 新表名

Ø例：将good表改名为goods。
      alter table good rename goods
添加删除约束
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
添加主码、唯一、外码约束：
Ø语法：alter table 表名 add primary key(字段名)

Øalter table 表名 add unique(字段名)

Øalter table foreign key(外码列名) references 主键表(主码列名)

添加默认约束
Ø语法：
alter table 表名 
alter column 列名 set default 默认值

Ø例：为good表的class添加默认约束，值为“生活用品”。
alter table good 
alter column class set default ‘生活用品’；

删除约束
•删除表的主码约束：
Ø语法：
alter table 表名 
drop primary key；

•删除表的唯一约束：
Ø语法：
alter table 表名 
drop index 唯一约束自动生成的索引名；

•删除表的外码约束：
Ø语法：
alter table 表名 
drop foreign key 外码名

删除（drop）

1
2
3
4
drop table 表名[,表名…]
Drop table vip1，goods1；

Drop datebase <database_name>;

数据操作语句（DML）

添加数据（insert）

1
2
3
Insert into 表名 [(列名表)] Values(对应列的值)

Insert into 表名 Select 语句;

修改数据（update）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
更新数据的语法格式：
Update 表名
Set 列名=表达式[,……]
[where 更新条件]

将得力卷笔刀的价格调整为3.5元。
Update goods
Set price=3.5
Where gname=‘得力卷笔刀’;

将宁波生产的得力彩笔套装规格改为’24色/包’,价格改为18元
Update goods
Set capacity=‘24色/包’,price=18
Where place=‘宁波’ and gname=‘得力彩笔套装’;

将周一于2018-11-26购买的商品数量增加1份。
Update sale Set number=number+1
Where btime=‘2018-11-26’ and vno in
(select vno from vip where vname=‘周一’);

删除数据（delete）

1
Delete from 表名 [where 删除条件]

数据查询语句（DQL)

关系运算

并（U）

交（∩）

差（-）

笛卡尔乘积（ X）

等值连接(⋈)

自然连接(⋈)

投影（∏）

选择（σ）

除（÷）

单表查询

select
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
distinct关键词能消除查询结果中的重复值。
select distinct class 
from goods ;

计算列
列表达式：商店活动，要显示全部商品信息，包括9折后的价格
select gno,gname,class,capacity,price,price*0.9,company,place,stock 
from goods 

列更名：查询商品名以及9折后的价格，显示的列名分别是“商品名”和“折后价”。“商品名”

语法格式：
旧列名 | 表达式 [ AS ] 新列名

select gname as 商品名,price*0.9 折后价
from goods ;

where
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
比较运算符
查询价格大于30元的商品的全部信息。
select * from goods
Where price>30 ;

确定范围（between）
查询价格在30和50元之间的商品信息
select * from goods
Where price betweent 30 and 50 

select * from goods
Where price>=30 and price <=50 

确定集合（in， not in）
查询产地在浙江、上海和南京的商品信息
select * from goods
Where place in (‘浙江’,’上海’,’南京’);

select * from goods
Where place=‘浙江’ or place=‘上海’ or place=‘南京’ 

模糊查询条件（like）

通配符%和 _
模糊查询中使用的通配符有两种： _  %
_代表任意一个字符
%代表任意多个字符

查询姓‘李’的会员信息。
select * from vip
Where vname like‘李%’ 

转义字符（escape）
查询商品名中包含100%的商品信息
select * from goods
Where gname like '%100!%%' escape '! ';
还可以直接用  \  
gname like ‘%100\%%’
group by
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
聚集函数
聚集函数（也称汇总函数、集合函数），用于数据的统计：
•COUNT（ * ）: 统计表中元组个数；
•COUNT（列名 ）: 统计本列列值个数；
•SUM（列名 ）:计算列值总和（必须是数值型列）；
•AVG（列名 ）:计算列值平均值（必须是数值型列）；
•MAX（列名 ）: 求列值最大值；
•MIN（列名 ）: 求列值最小值。

统计每类商品的平均价格
Select class,avg(price) 
From goods
group by class;

having 子句可以对分组统计后的结果进行筛选，不能用where代替。
统计商品超过10样的种类有哪些？
Select class,count(*) 
From goods
group by class
having count(*)>10;
order by
1
2
3
4
5
6
7
8
9
10
11
12
13
14
1、ORDER BY <列名> [ASC|DESC][,…n]
2、<列名>后面如果不加[ASC|DESC]则默认情况为升序
3、如有多个列名，先按第一关键字排序，当第一关键字相同的时候，会按照第二关键字排序，以此类推。

统计热销前十的商品编号。
Select gno,count(*) 
From sale
group by gno
order by count(*) desc
limit 10;

limit m,n 
从第m行开始选择n行 
m从0开始是第一行

多表查询

内连接
1
2
3
4
5
6
7
8
9
10
11
12
13
14
join...on
select 目标列名序列
from 表1 [inner] join 表2 on 连接条件 [join 表3 on 连接条件[……]]
[where 检索条件表达式] 
[group by 分组依据列] 
[having 检索条件表达式] 
[order by 排序依据列] 


多表连接
select v.vno,v.name,g.gno,gname,btime
from vip v join sale s
on v.vno=s.vno
join goods g on g.gno=s.gno;
外连接
1
2
3
4
5
左外连接left join 会返回左表不满足链接条件的所有信息

内连接[inner] join 会返回两表满足条件的信息

右外连接right join 会返回右表不满足链接条件的所有信息
自连接
1
2
3
4
5
Select g1.gname
From goods g1 join goods g2
On g1.place=g2.place
where g2.gname=‘六神清凉沐浴露’
and g1.gname!=‘六神清凉沐浴露’;

嵌套查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。如果一个SELECT语句是嵌套在一个SELECT、INSERT、UPDATE或DELETE语句中，则称之为子查询或内层查询；而包含子查询的语句则称为主查询或外层查询。 

Select gname
From goods
Where place=(Select place From goods Where gname=‘六神清凉沐浴露’)
And gname!=‘六神清凉沐浴露’ ;


in •In（返回的结果多于一个）
店庆抽奖抽中‘2018-11-26’购物的会员，现需会员名称及联系方式
Select vname,phone from vip 
Where vno in (Select vno from sale 
Where btime=‘2018-11-26’);

any
Select vname,phone from vip 
Where vno in (Select vno from sale 
Where btime=‘2018-11-26’);
=
Select vname,phone from vip 
Where vno =any(Select vno from sale 
Where btime=‘2018-11-26’);

不是最低价的所有商品。
Select * from goods 
Where price > any (Select price from goods);

数据控制语言（DCL）

权限管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Grant 权限名[(列名)] on 对象 To 用户
[With grant option]

【例1】授予用户newuser对jxgl数据库students表上sno列和sname列的查询权限
Grant select(sno,sname) on jxgl.students 
to ‘newuser’@’localhost’

•授予所有权限
Grant all on 对象 to 用户 [with grant option]

•创建新用户并授予权限
Grant 权限 on 对象 to 用户 indentified by ‘密码’ [with grant option]

移除权限
•语法：
•Revoke 权限 on 对象 from 用户

视图和索引

视图

视图创建

1
2
3
4
CREATE VIEW <视图名> [(视图列名表)] 
AS 
SELECT 查询语句
[WITH [cascaded] CHECK OPTION]

修改视图

1
2
3
ALTER VIEW 视图名[(列名1[,…n])]
AS SELECT查询语句
[WITH CHECK OPTION]

删除视图

1
2
•删除视图的SQL语句的格式为：
DROP VIEW <视图名>

索引

创建

1
2
3
4
5
6
CREATE INDEX语法格式：
CREATE [UNIQUE | FULLTEXT ] {INDEX| KEY} 索引名 ON 表名 (列名 [ASC|DESC] [,…n])

例：在表goods的gname字段上建立名为gNameIdx的索引。
CREATE INDEX gNameIdx 
ON goods (gname);

查看

1
2
3
4
5
查看索引
show index from <表名>

查看在good表上创建的所有索引。
show index from goods;

删除

1
2
3
4
5
6
•方法一：
DROP INDEX 索引名 ON 表名 ; 


•方法二：
ALTER TABLE 表名 DROP INDEX 索引名;

存储过程和函数

存储过程

存储过程是为了实现某个特定任务，由一组预先编译好的SQL语句组成，将其放在服务器上，由用户通过指定存储过程的名字来执行的一种数据库对象

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE PROCEDURE  存储过程名 
([ IN | OUT | INOUT ]  参数名 参数的数据类型)
routine_body
说明：
IN | OUT | INOUT：参数类型输入|输出|输入输出
routine_body：存储过程的SQL语句

•IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回
•OUT 输出参数:该值可在存储过程内部被改变，并可返回
•INOUT 输入输出参数:调用时指定，并且可被改变和返回

//创建存储过程
CREATE PROCEDURE sp_price(in pb char(30),out avg decimal(6,2) )
begin
   select avg(price) into avg
   from books
   where publish=pb;
end

// 调用存储过程
call sp_price('清华大学出版社',@avgprice);
select @avgprice;

变量

函数

事物和锁

事物

原子性

一致性

隔离性

持久性

事物的并发

丢失修改

•丢失修改是指两个事务T1和T2读取数据库中同一数据并进行修改，T2提交的结果破坏了T1提交的结果，导致T1对数据的修改丢失。

读“脏”数据

•读“脏”数据是指事务T1修改数据库中某一数据，并将其写回数据库，事务T2读取了同一数据（为事务T1修改后的值）后，事务T1由于某种原因被撤销，即事务T1回滚，T1已经修改过的数据恢复原值，此时事务T2所读到的数据与数据库中的数据不一致，则称T2所读到的数据为“脏”数据

不可重复读

不可重复读是指事务T1读取数据库中某一数据后，事务T2对该数据库执行了更新操作，并写回数据库中，当事务T1再次读取该数据时，所得到的结果与第一次读取得结果不同

并发控制

当多个用户并发地存取数据库时就可能产生多个事务同时存取同一数据的情况，若不对并发操作进行控制的话就可能会导致存取不正确的数据，破坏数据库的一致性。所以数据库管理系统必须提供并发控制机制。

•在数据库中，事务并发控制机制包括基于锁的协议、基于时间戳的协议、基于有效性检查的协议等几种方式，其中最常用的为基于锁的协议。

加锁

•加锁就是事务T在对某个数据操作之前，先向系统发出请求，封锁其所要使用的数据。在事务T释放它的锁之前，其他事务不能操作这些数据。

共享锁（s锁）

•与数据的读取操作紧密相关，因此又称为读锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，此时其它事务只能再对数据对象A加S锁，而不能加X锁，直到T释放A上的S锁为止。

排它锁（x锁）

与数据的修改操作紧密相关，因此又称为写锁。若事务T对数据对象A加上X锁，则只允许事务T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁为止。

封锁协议