数据库学习(二) 约束、数据库的设计、多表查询、事务、DCL

约束

概念：对表中的数据进行限定，保证数据的正确性、有效性和完整性
例如student1中，不能有name为null的数据
分类：

1. 主键约束：primary key
2. 非空约束：not null
3. 唯一约束：unique
4. 外键约束：foreign key

在Yog中可以右键点击使用的表，选择改变表来查看各种约束

非空约束：not null

1. 创建表时添加约束

· create table stu(
	id int,
	name varchar(20) not null
);

此时如果添加数据的name为null 就会报错

2. 创建表之后添加约束

· alter table stu modify name varchar(20) not null;

同理，如果想取消非空约束，也可以用alter指令进行调整

· alter table stu modify name varchar(20);

唯一约束：unique

· create table stu(
	id int,
	phone_number varchar(20) unique;
);

null不受唯一约束
与非空约束的删除方式不同，alter table stu modify phone_number varchar(20);并不能删掉唯一约束，而是使用

· alter table stu drop index phone_number; --删除索引

创建之后再添加约束则和非空约束的方法类似

· alter table stu modify name varchar(20) unique;

主键约束：primary key

主键约束

含义：非空且唯一
主键是表中记录的唯一标识，就像身份证，不能没有且不能重复

创建表时添加主键约束

create table stu(
	id int primary key,
	name varchar(20)
);

删除主键
也不可以用modify来删除主键,而是使用

· alter table stu drop primary key;

创建完后，添加主键

· alter table stu modify name varchar(20) primary key;

自动增长：auto_increment

如果某一列是数值类型的，使用auto_increment来完成值的自动增长,当然也可以不和主键连在一起用，但是这种比较少，通常都是和主键一起用。
创建时添加

create table stu(
	id int primary key auto_increment;
	name varchar(20)
）

此时再添加数据时会有

· insert into stu values(null,'ccc');

id会自动变成上一条数据的id+1，如果不是null而是设定的数，则是设定的数
删除自动生长可以用modify

· alter table stu id int;--主键约束没有被删掉，自动增长被删掉了

添加自动增长

· alter table stu id int auto_increment;

外键约束

外键操作

foreign key, 让表与表产生关系，从而保证数据的正确性。
数据有冗余。比如销售部在杭州，研发部在深圳，就可以把员工的部门和工作地点合并表示。
例如下面的员工表和部门表

CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32),
dep_id INT
);
INSERT INTO employee(id,NAME,dep_id) VALUES(NULL,"A",1),(NULL,"B",1),(NULL,"C",1),(NULL,"D",2),(NULL,"E",2),(NULL,"F",2);
SELECT * FROM employee;

CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(32),
dept_address VARCHAR(32)
);
INSERT INTO department(id,dept_name,dept_address) VALUES(NULL,"研发部","深圳"),(NULL,"销售部","杭州");
SELECT * FROM department;

但是这两张表现在没有实际的关联，比如要裁掉研发部，此时还有三个员工ABC在，没有部门了。所以应该先裁掉ABC，再裁掉研发部。

需要让employee表的dep_id与department表的id关联

create table [表名](...
	[外键列]
	constraint [外键名称] foreigh key ([外键列名称]) references ([主表列名称])
);

drop掉之前的两个表，重新创建。注意，因为部门表是被关联的，所以要先创建

CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(32),
dept_address VARCHAR(32)
);
INSERT INTO department(id,dept_name,dept_address) VALUES(NULL,"研发部","深圳"),(NULL,"销售部","杭州");

CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32),
dep_id INT,
CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)
);
INSERT INTO employee(id,NAME,dep_id) VALUES(NULL,"A",1),(NULL,"B",1),(NULL,"C",1),(NULL,"D",2),(NULL,"E",2),(NULL,"F",2);

现在内部就关联起来了。
1、不能随意删除部门
此时调用
DELETE FROM department WHERE id=1;
会报错

2、不能随意添加员工的部门id
此时调用
INSERT INTO employee(id,NAME,dep_id) VALUES(NULL,“G”,5);
会报错

删除外键及创建后添加
删除语法：

· alter table employee drop foreign key [外键名称];

创建后添加语法

· alter table [表名] add constraint [外键名称] foreign key([外键字段名称]) references [主表名称]([主表列名称]);

级联操作

现在想修改研发部的编号，直接update employee set dep_id = 5 where dep_id = 1是不可以的，甚至需要先把员工表dep_id=1的dep_id置为null，再改部门表的id=1为5，再update员工表的dep_id为5，很麻烦
所以用到级联操作，可以直接联动。
tips，Yog中的新架构设计器可以看到外键关系

点击后，把department和employee拖进来，会发现id与dep_id之间连线

添加级联，也就是更改部门号，所属员工的部门号也改变

alter table [表名] add constraint [外键名称] 
foreign key([外键字段名称]) 
references [主表名称]([主表列名称]) 
on update cascade;

级联删除,也就是删除部门，所属员工都删除

alter table [表名] add constraint [外键名称]
foreign key([外键字段名称]) 
references [主表名称]([主表列名称]) 
on update cascade on delete cascade;

实际开发谨慎使用级联

数据库的设计

多表关系

三种多表关系：

• 一对一（了解，少见）一个人一个身份证
• 一对多、多对一 一个部门多个员工，一个员工只能对应一个部门
• 多对多 学生和课程的关系

一对多

员工表就作为“多”，部门表就作为“一”，实现这种对应关系，可以用添加外键来实现。
在“多”的一方建立外键，指向“一”的一方的主键

多对多

学生表和课程表都能作为“多”，实现这种关系需要借助中间表
中间表最少有两个字段，如学生表的主键字段和课程表的主键字段。这两个字段作为第三张表的外键，分别指向两张表的主键。

student id	class id
1	1
2	1
1	2
2	2

这里有个知识点，中间表显然不能有重复的数据，这是通过联合主键来实现的。

一对一

学生表和身份证表， 可以在任意的一方添加外键，且让外键唯一。
也可以让两个表的外键相同。
这种情况比较少。因为可以把两个表合成一起。

示例

例如这种旅游APP的数据库，可以分为旅游分类表、旅游线路表、用户表。分类表和线路表显然是一对多的关系：一种分类可以对应多种线路，但是一个线路只能属于一种分类。
线路表和用户表则是多对多的关系：一个线路可以被多个用户选择，一个用户可以选择多个线路。
这样的话，根据之前的说明，在线路表中加入外键指向分类表的主键，即可实现一对多；添加一个中间表，即可实现多对多。

那么，实操一下这个表

CREATE TABLE tab_category(
	cid INT PRIMARY KEY AUTO_INCREMENT,
	cname VARCHAR(32) NOT NULL UNIQUE 
);
CREATE TABLE tab_route(
	rid INT PRIMARY KEY AUTO_INCREMENT,
	rname VARCHAR(32) NOT NULL UNIQUE,
	price INT,
	cid INT,
	FOREIGN KEY (cid) REFERENCES tab_category(cid) --这里的外键写法是简略写法，省去了constraint [外键名称]，省略后会自动分配一个外键名称
);
CREATE TABLE tab_user(
	id INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(32) NOT NULL UNIQUE,
	PASSWORD VARCHAR(32) NOT NULL
);
CREATE TABLE tab_favorite(
	rid INT,
	DATE DATETIME,
	uid INT,
	PRIMARY KEY (rid,uid),
	FOREIGN KEY (rid) REFERENCES tab_route(rid),
	FOREIGN KEY (uid) REFERENCES tab_user(id)
);

范式

概念：设计数据库时，需要遵循的一些规范
设计数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
目前关系数据库有六种范式：（第一范式、第二范式、第三范式、巴斯-科德范式、第四范式、第五范式（完美范式））
通常满足前三种即可

1. 第一范式（INF）：每一列都是不可分割的原子数据项
2. 第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）
3. 第三范式（3NF）：zai2NF的基础上，任何非主属性不依赖于其他非主属性（在2NF基础上消除传递依赖）

第一范式

下表就是不满足每一列都是不可分割的原子数据项，因为系这一列可以分为系名和系主任这两栏

修改方法

把系名和系主任分成两列

第一范式存在的问题：

1. 存在严重的数据冗余，姓名、系名、系主任都有重复
2. 添加数据时存在问题，比如新开设系和系主任，不合法
3. 如上表，张无忌毕业后，删除数据，会将系的数据也删掉

第二范式

几个概念：

1. 函数依赖：如果通过A属性（或属性组）的值可以确定唯一B属性的值，则B依赖于A
   如学号可确定姓名，姓名依赖于学号
   学号和课程名称可以确定分数，分数依赖于学号+课程名称
2. 完全函数依赖，如果A是一个属性组，则B属性值的确定需要依赖于A属性组中所有的属性值
   例如，确定分数，需要同时确定学号和课程名称
3. 部分函数依赖，如果A是一个属性组，则B属性值的确定只需要A的一部分即可
   例如，确定姓名，在学号和课程名称的属性组中，只需要知道学号即可
4. 传递函数依赖，由A唯一确定B，再由B唯一确定C，则C传递依赖于A
   例如，学号确定系名，系名确定系主任，系主任就传递依赖于学号
5. 码，如果一张表中，一个属性或属性组，被其他所有属性完全依赖，则称这个属性为该表的码
   例如，该表中的码，是学号和课程名称的属性组
6. 主属性，码属性组中的所有属性
   非码属性，除码属性的其他属性

那么，怎么消除非主属性对主码的*部分函数依赖呢？
学号+名称为码，而系名、系主任只依赖于学号，就是部分依赖主码
则对表进行拆分：选课表中主码为学号+课程名称，学生表为学号

第二范式存在的问题：
1. 存在严重的数据冗余，姓名、系名、系主任都有重复
2. 添加数据时存在问题，比如新开设系和系主任，不合法
3. 如上表，张无忌毕业后，删除数据，会将系的数据也删掉

第三范式

任何非主属性不依赖于其他非主属性（在2NF上消除传递依赖）
如上面的学生表，系主任传递依赖于学号
再分一张表，系表

存在的问题：
1. 存在严重的数据冗余，姓名、系名、系主任都有重复
2. 添加数据时存在问题，比如新开设系和系主任，不合法
3. 如上表，张无忌毕业后，删除数据，会将系的数据也删掉

数据库的备份和还原

两种方式：

1. 命令行

· mysqldump -u[用户名] -p[密码] [数据库名称] > [保存到路径] --备份
· source [文件路径] --还原

实操

drop掉db1数据库之后，再还原
create database db1;
use db1;
source d://s.sql;

图形化很简单

多表查询

查询语法

select [列名列表] from [表名列表] where...

还用之前用的员工表和部门表

· select * from employee,department;

返回的是一个笛卡尔积，即两个表所有元素能组合成的所有情况，这个例子就是2*6=12个

明显有很多无用的数据
消除方法有三种

• 内连接查询
• 外链接查询
• 子查询

内连接

隐式内连接

使用where

· select * from emp,dept where emp.dept_id = dept.id;

如果只想要部分列，把*替换成表.列名即可

· select emp.name,emp.gender,dept.name from emp,dept where emp.dept_id = dept.id;

显式内连接

语法：inner join

· select [字段列表] from [表名1] inner join [表名2] on [条件]

inner是可以省略的

与上面对应的操作是

select * from emp inner join dept on emp.dept_id = dept.id;

内连接需要的要素：

1. 从哪些表中查询
2. 条件是什么
3. 查询哪些字段

外连接

左外连接

· select [字段列表] from [表1] left outer join [表2] on [条件]

outer是可以省略的

示例,在员工表添加一个新员工，部门id为空

查询所有员工信息，如果员工有部门，则查询部门名称，否则不显示部门名称
如果用

select
	t1.*,t2.name
from 
	emp t1,dept t2
where
	t1.dept_id = t2.id

并没有新增的员工信息，这是因为，新增的部门号为空，被视为了非法信息

select 
	t1.*, t2.name
from
	emp t1
left join
	dept t2
on
	t1.dept_id = t2.id

为什么呢？因为左外连接，查询的是左表所有数据及其交集部分，内连接求的是左表和右表交集的部分

右外连接

则右外连接同理，查询的是右表所有数据及其交集部分语法为

select [字段列表] from [表1] right outer join [表2] on [条件]

子查询

查询中嵌套查询，称嵌套查询为子查询，也就是select套select

单行单列

查询最高工资的员工信息，可先差最高工资是多少，再查谁的工资等于这个数额
分两步的办法是

select max(salary) from emp;--返回9000
select * from emp where salary = 9000;

一条sql完成操作

select * from emp where salary = select max(salary) from emp

这是子查询结果是单行单列的情况，子查询可作为条件，使用常用的单值运算符（> < =）

多行单列

查询财务部和市场部所有员工的信息

select id from dept where name = '财务部' or name = '市场部';
select * from emp where dept_id in (3,2);

合并在一起

select * from emp where dept_id in (select id from dept where name = '财务部' or name = '市场部');

子查询结果是多行单列,可以使用运算符in作为条件

多行多列

查询入职日期在2011-11-11之后的员工信息和部门信息

select * from emp.join_data > '2011-11-11'

可以子查询结果作为表，放到SQL语句的[表]的位置

select * from dept t1, (select * from emp.join_data > '2011-11-11') t2 where t1.id = t2.dept_id;

子查询结果是多行多列，可以将其作为一张虚拟表，加入查询之中

事务

基本介绍

概念：如果一个包含多个步骤的业务操作，被事务管理，那么这些扫做要么同时成功，要么同时失败

张三给李四转账500

1. 查询张三账户余额是否大于500
2. 张三账户 金额-500
3. 李四账户 金额+500
   如果某一步失败了，比如第二步结束后出异常了，不能让500块钱没了，此时要回滚，到最开始。

这就是一个事务。

实现事务的操作：

1. 开启事务 start transaction
2. 回滚 rollback
3. 提交 commit

CREATE TABLE account( 
	id INT PRIMARY KEY,
	NAME VARCHAR(32),
	balance DOUBLE
);
INSERT INTO account (id,NAME,balance) VALUES(1,"张三",1000),(2,"李四",200);

如果完成一次转账，则是

UPDATE account SET balance = balance - 500 WHERE NAME = '张三';
UPDATE account SET balance = balance + 500 WHERE NAME = '李四';

此时张三和李四一个少500一个多500

如果这两步之间出错了呢怎么办？

UPDATE account SET balance = balance - 500 WHERE NAME = '张三';
出错了...
UPDATE account SET balance = balance + 500 WHERE NAME = '李四';

此时张三少了500元，不翼而飞

这就说明要使用事务管理
在转账之前开启事务：

start transaction;
UPDATE account SET balance = balance - 500 WHERE NAME = '张三';
出错了...
UPDATE account SET balance = balance + 500 WHERE NAME = '李四';

此时如果查询account表，会发现500块还是没了

此时的变化时临时的变化
但是如果再打开一个yog窗口（不是新建一个查询窗口）中查询account表，则发现，张三的钱并没有丢。

发现出问题了要回滚事务

rollback;

张三的钱又回来了
如果没有问题，提交事务

commit;

在mysql中，事务默认自动提交。如果选择了手动提交，即使用start transaction后，如果没有commit，就不会完成事务。

select @@autocommit;

这条语句可以查看是否是自动提交。

set @@autocommit = 0;

这条语句可以改为手动提交，此后如果不加commit，增删改不会成功。

事务的四大特征（面试常考）

原子性：事务要么同时成功，要么同时失败
持久性：如果一旦提交或回滚，数据库会发生持久化的保存数据
隔离性：多个事务之间相互独立
一致性：事务操作前后，数据总量不变

事务的隔离级别（了解）

概念：多个事务之间隔离的，相互独立的，但是如果多个事务操作同一批数据，则会引发一些问题，设置不同隔离级别就可以解决这个问题
存在问题：

1. 脏读：一个事务，读取到另一个事务中没有提交的数据
2. 不可重复读（虚读）：在同一个事务中，两次读取到的数据不一样
3. 幻读：一个事物操作（DML）数据表中所有记录，另一个事务查询不到自己的修改

隔离级别

1. read uncommitted:脏读、不可重复读、幻读
2. read committed: 不可重复读、幻读
3. repeatable read:不可重复读
4. serializable串行化:解决所有问题

隔离级别从小到大，安全性越来越高，效率越来越低，所以要选择合适的隔离级别

select @@tx_isolation;--查询隔离级别
set global transaction level read committed;--设置隔离级别

示例：
脏读：
隔离级别 读未提交：

set global transaction isolation level read committed;

在另一个窗口中查询隔离级别，可以看到已经被修改了

两个窗口都开启事务

此时在一个事务中改变数值，然后查询，可以看到第二个事务查询到的数据，是未提交的数据，也就是发生了脏读

那这个时候如果第一个事务回滚了，第二个事务再读的时候就是回滚之后的数据了，这就发生了不可重复读。

隔离级别：read committed
解决了脏读的问题但是还是会有在同一个事务中两次读不一样的情况。
隔离级别：repeatable read
这时与上一个级别的区别是，第一个事务提出修改，第二个事务查询是未提交的数据；第一个事务提交，第二个事务查询还是上一次的数据；第二个事务提交；再次在第二个事务查询，此时就是修改过的了。确保一个事务中的查询是相同的。
隔离界别：serializable
开启事务1；开启事务2；事务2查询时，光标闪烁，并不执行查询；只有事务1提交了，事务2才能查询。类似于多线程加锁。

DCL

DBA 数据库管理员
DCL 管理用户，授权

1. 添加用户
2. 删除用户

查询用户
切换mysql数据库，查询user表

use mysql;
select * from user;

主机名：
localhost表示本地
通配符%表示，可以在任意主机使用用户登录数据库

创建用户

create user '用户名'@'主机名' identified by '密码'

删除用户

drop user '用户名'@'主机名';

修改用户密码

update user set password = password('新密码') where user = '用户名';--需要使用password对密码加密

set password for '用户名'@'主机名' = password('新密码');

忘记了root密码怎么办？

1. 以管理员身份运行cmd后，net stop mysql
2. 使用无验证方式启动mysql服务 mysqld --skip-grant-tables
3. 再打开mysql就可以不用密码直接登录了
4. 设置新的密码
5. 手动关闭mysqld
6. 打开mysql服务 net start mysql
7. 此时就可以用账号密码登录了

权限管理
查询权限

show grants for '用户名'@'主机名';

比如新建了一个lisi用户，他只有一个登录权限，想use数据库是不能的

而root基本上有所有的权限
授予权限

grant '权限列表' on '数据库名.表明' to '用户名'@‘主机名’

权限列表就是增删改查，如果只给了select权限，那就只能查

授予所有权限，在任意数据库任意表

grant all on *.* to '用户名'@'主机名';

撤销权限

revoke '权限列表' on  '数据库.库名' from '用户名'@'主机名'