【MySQL】数据库的设计

数据库设计

1、多表关系

1. 一对多(多对一)：在多的一方建立外键，指向一的一方的主键。
2. 多对多：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键
3. 一对一：一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键。

2、数据库设计的范式

1）概述

范式：设计数据库时，需要遵循的一些规范。要遵循后边的范式要求，必须先遵循前边的所有范式要求

2）分类

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

3）概念

1. 函数依赖A -> B,如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖于A
2. 完全函数依赖：A -> B， 如果A是一个属性组，则B属性值得确定需要依赖于A属性组中所有的属性值
3. 部分函数依赖：A -> B， 如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值即可
4. 传递函数依赖：A -> B, B -> C ，如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递函数依赖于A
5. 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码
6. 主属性：码属性组中的所有属性
7. 非主属性：除过码属性组的属性

3、多表查询

1）笛卡尔积

有两个集合A,B，取这两个集合的所有组成情况。

要完成多表查询，需要消除无用的数据

2）内连接查询

1. 隐式内连接

使用where条件消除无用数据

SELECT 
	t1.name, -- 员工表的姓名
	t1.gender,-- 员工表的性别
	t2.name -- 部门表的名称
FROM
	emp t1,
	dept t2
WHERE 
	t1.dept_id = t2.id;

2. 显式内连接

-- 语法
select 字段列表 from 表名1 [inner] join 表名2 on 条件;
-- 例如
SELECT * FROM emp INNER JOIN dept ON emp.dept_id = dept.id;	
SELECT * FROM emp JOIN dept ON emp.dept_id = dept.id;

3. 内连接查询使用思想

从哪些表中查询数据？

条件是什么？

查询哪些字段？

3）外链接查询

1. 左外连接

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

查询的是左表所有数据以及其交集部分

2. 右外连接

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

查询的是右表所有数据以及其交集部分

4）子查询

根据结果分类

1. 单行单列

子查询可以作为条件，使用运算符判断

select * from stu where age < (select avg(age) from stu);

2. 多行单列

子查询可以作为条件，使用in来判断

select * from stu where id in (select id from class where name = '一班' or name = '二班');

not in

3. 多行多列

虚拟表

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

普通内连接

select *
from emp t1,dept t2
where t1.dept_id = t2.id and t1.date > '2021-11-11'

4. 自关联查询

SELECT 
	t1.ename,
	t2.ename
FROM emp t1
LEFT JOIN emp t2
on t1.mgr = t2.id;

4、事务

1）事务概念

1. 概念

如果一个包含多个步骤的业务操作，被事务管理，要么同时成功，要么同时失败

2. 操作

开始事务：start transaction;

回滚：rollback;

提交：commit;

3. 事务默认提交

Oracle默认手动提交

一条DML(增删改)语句会自动提交一次事务

修改事务的默认提交方式：

-- 查询
select @@autocommit;	-- 自动：1；手动：0
-- 修改
select @@autocommit = 0;

2）事务的四大特征

1. 原子性：是不可分割的最小操作单位，要么同时成功，要么失败
2. 持久性：事务结束后，数据库会持久的保存数据
3. 隔离性：多个事务之间相互独立
4. 一致性：事务操作前后，数据总量不变

3）事务的隔离级别

1. 概念

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

2. 问题

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

3. 隔离级别

1. read uncommitted：读未提交 -->脏读、虚读、幻读
2. read committed：读已提交（Oracle默认）–> 虚读、幻读
3. repeatable read：可重复读（MySQL默认）–> 幻读
4. serializable：串行化 --> 没问题

隔离级别从小到大安全性越来越高，但是效率越来越低

-- 隔离级别的查询和设置
-- 查询
select @@tx_isolation;
-- 设置
set global transaction isolation level 级别字符串;

5、DCL：管理用户、授权

DBA：数据库管理员

1）管理用户

1. 添加用户

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

2. 删除用户

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

3. 修改密码

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

4. root用户密码忘记

cmd -> net stop mysql (需要管理员运行)

使用无验证方式启动mysqlmysqld --skip-grant-tables

修改密码

关闭mysqld服务

5. 查询用户

-- 1.切换到mysql数据库
use mysql;
-- 2.查询user表
select * from user;

通配符%：可以在任意主机上登陆

2）权限管理

1. 查询权限

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

2. 授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
grant all on *.* to 'wmh'@'%';	-- 授予root权限

3. 撤销权限

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