10-DBA命令和数据库设计的三范式
课程目标
掌握新建用户、授权、回收权限、导出导入、第一范式、第二范式、第三范式、三范式总结。
10.1新建用户
create user username identified by 'password';
说明:username——你将创建的用户名, password——该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器.
例如：
create user xme361 identified by '123';
10.2 授权
1.命令详解
MySQL> grant all privileges on dbname.tbname to 'username'@'login ip' identified by 'password' with grant option;
- dbname=*表示所有数据库
- tbname=*表示所有表
- login ip=%表示任何ip
- password为空，表示不需要密码即可登录
- with grant option; 表示该用户还可以授权给其他用户
2.细粒度授权
首先以root用户进入MySQL，然后键入命令：
grant select,insert,update,delete on *.* to xme361 @localhost Identified by "123";
如果希望该用户能够在任何机器上登陆MySQL，则将localhost改为 "%" 。
3.粗粒度授权
我们测试用户一般使用该命令授权，
grant all privileges on *.* to 'xme361'@'%' identified by "123";
注意:用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令:
grant all privileges on *.* to 'hk361'@'%' identified by "123" with grant option;
privileges包括：
- alter：修改数据库的表
- create：创建新的数据库或表
- delete：删除表数据
- drop：删除数据库/表
- index：创建/删除索引
- insert：添加表数据
- select：查询表数据
- update：更新表数据
- all：允许任何操作
- usage：只允许登录
10.3 回收权限
命令详解
revoke privileges on dbname[.tbname] from username@ip;
revoke all privileges on *.* from hk361;
use MySQL
select * from user
进入 MySQL库中
修改密码;
update user set password = password('qwe') where user = 'hk361';
刷新权限;
flush privileges
10.4 导出导入
1. 导出
(1) 导出整个数据库
在Windows的DOS命令窗口中执行：mysqldump heze>d:/hk.sql -u root -p
(2)导出指定库下的指定表
在Windows的DOS命令窗口中执行：mysqldump heze emp>d:/hk.sql -u root -p
2. 导入
登录MySQL数据库管理系统之后执行：source d:/yunke.sql
10.5 第一范式
数据库表中不能出现重复记录，每个字段是原子性的不能再分
不符合第一范式的示例，如下表10-1所示：
表10-1
学生编号 | 学生姓名 | 联系方式 |
1001 | 张三 | zs@gmail.com,1359999999 |
1002 | 李四 | ls@gmail.com,1369999999 |
1001 | 王五 | ww@163.net,13488888888 |
存在问题：
最后一条记录和第一条重复（不唯一，没有主键）
联系方式字段可以再分，不是原子性的，如下表10-2所示：
表10-2
学生编号(pk) | 学生姓名 | 联系电话 | |
1001 | 张三 | zs@gmail.com | 1359999999 |
1002 | 李四 | 1369999999 | |
1003 | 王五 | 13488888888 |
关于第一范式，每一行必须唯一，也就是每个表必须有主键，这是我们数据库设计的最基本要求，主要通常采用数值型或定长字符串表示，关于列不可再分，应该根据具体的情况来决定。如联系方式，为了开发上的便利行可能就采用一个字段了。
10.6 第二范式
第二范式是建立在第一范式基础上的，另外要求所有非主键字段完全依赖主键，不能产生部分依赖，如下表10-3所示：
示例：
表10-3
学生编号 | 学生姓名 | 教师编号 | 教师姓名 |
1001 | 张三 | 001 | 王老师 |
1002 | 李四 | 002 | 赵老师 |
1003 | 王五 | 001 | 王老师 |
1001 | 张三 | 002 | 赵老师 |
确定主键，如下表10-4所示：
表10-4
学生编号(PK) | 教师编号(PK) | 学生姓名 | 教师姓名 |
1001 | 001 | 张三 | 王老师 |
1002 | 002 | 李四 | 赵老师 |
1003 | 001 | 王五 | 王老师 |
1001 | 002 | 张三 |
以上虽然确定了主键，但此表会出现大量的冗余，主要涉及到的冗余字段为“学生姓名”和“教师姓名”，出现冗余的原因在于，学生姓名部分依赖了主键的一个字段学生编号，而没有依赖教师编号，而教师姓名部门依赖了主键的一个字段教师编号，这就是第二范式部分依赖。
解决方案如下：
学生信息表，如下表10-5所示：
表10-5
学生编号（PK） | 学生姓名 |
1001 | 张三 |
1002 | 李四 |
1003 | 王五 |
教师信息表，如下表10-6所示：
表10-6
教师编号（PK） | 教师姓名 |
001 | 王老师 |
002 | 赵老师 |
教师和学生的关系表，如下表10-7所示：
表10-7
学生编号(PK) fk?学生表的学生编号 | 教师编号(PK) fk?教师表的教师编号 |
1001 | 001 |
1002 | 002 |
1003 | 001 |
1001 | 002 |
如果一个表是单一主键，那么它就复合第二范式，部分依赖和主键有关系
以上是一种典型的“多对多”的设计
10.7 第三范式
建立在第二范式基础上的，非主键字段不能传递依赖于主键字段。（不要产生传递依赖），如下表10-8所示：
表10-8
学生编号（PK） | 学生姓名 | 班级编号 | 班级名称 |
1001 | 张三 | 01 | 一年一班 |
1002 | 李四 | 02 | 一年二班 |
1003 | 王五 | 03 | 一年三班 |
1004 | 赵六 | 03 | 一年三班 |
从上表可以看出，班级名称字段存在冗余，因为班级名称字段没有直接依赖于主键，班级名称字段依赖于班级编号，班级编号依赖于学生编号，那么这就是传递依赖，解决的办法是将冗余字段单独拿出来建立表，如：
学生信息表，如下表10-9所示：
表10-9
学生编号（PK） | 学生姓名 | 班级编号（FK） |
1001 | 张三 | 01 |
1002 | 李四 | 02 |
1003 | 王五 | 03 |
1004 | 赵六 | 03 |
班级信息表，如下表10-10所示：
表10-10
班级编号（PK） | 班级名称 |
01 | 一年一班 |
02 | 一年二班 |
03 | 一年三班 |
以上设计是一种典型的一对多的设计，一存储在一张表中，多存储在一张表中，在多的那张表中添加外键指向一的一方的主键
10.8 三范式总结
第一范式：有主键，具有原子性，字段不可分割
第二范式：完全依赖，没有部分依赖
第三范式：没有传递依赖
数据库设计尽量遵循三范式，但是还是根据实际情况进行取舍，有时可能会拿冗余换速度，最终用目的要满足客户需求。
一对一设计，有两种设计方案：
第一种设计方案：主键共享
第二种设计方案：外键唯一
10.9 本章小结
本章主要阐述了MySQL数据库中的DBA命令和数据库设计的三范式。