【MySQL】数据库的设计

2024-01-10 06:10:27

数据库设计

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 字段列表 from1 left [outer] join2 on 条件;

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

2. 右外连接
select 字段列表 from1 right [outer] join2 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 '用户名'@'主机名';

文章来源:https://blog.csdn.net/wmh1024/article/details/135431463
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。