MySQL之视图、DBA和范式

MySQL之视图

---

1. 视图（view）：站在不同的角度去看待同一份数据
2. 视图的作用：
   1. 简化开发，利于维护
   2. 增强数据安全性
   3. 避免数据冗余
   4. 提高数据的逻辑独立性

我们可以面向视图对象进行增删查改，对视图对象的增删改查会导致原表被操作！（视图最大的特点）
——在实际开发当中，假设有一条非常复杂的SQL语句，而这条SQL语句需要在不同的位置上反复使用。每一次使用这个语句的时候都需要重新编写，很长，很麻烦。这个时候我们可以把这条复杂的SQL语句以视图对象的形式创建，在需要编写这条SQL语句的位置直接使用视图对象，可以大大简化开发，并且利于后期的维护。因为修改的时候只需要修改一个位置就行，也只需要修改视图对象所映射的SQL语句。
——我们以后面向视图开发的时候，使用视图的时候可以向使用table一样，可以对视图进行增删改查等操作，视图不是在内存当中，视图对象也是存储在硬盘上的，不会消失。
3. 视图对象的创建：
create view emp_view as select * from emp' //把 select * from emp执行的查询结果当作一个视图创建出来
4. 删除视图对象：
drop view emp_view;
注意：只有DQL语句才能以view的形式创建。即as后面只能是DQL语句；但是视图创建完成以后，可以对视图进行增删改查等操作
补充：增删改查在公司程序员之间沟通的术语是CRUD。
C——Create（增）
R——Retrieve（查：检索）
U——Update（改）
D——Delete（删）

视图实例

mysql> select * from t_users;
+----+----------+------------+---------+
| id | name     | email      | address |
+----+----------+------------+---------+
|  1 | zhangsan | zs@qq.com  | aaaaaaa |
|  2 | wangwu   | ww@163.com | bbbbbbb |
|  3 | zhaoliu  | zl@qq.com  | ccccccc |
|  4 | aaa      | aa@qq.com  | aaaaaaa |
|  5 | bbb      | bb@qq.com  | bbbbbbb |
+----+----------+------------+---------+
5 rows in set (0.00 sec)

mysql> select * from t_user
    -> ;
+------+------+------+
| id   | name | sex  |
+------+------+------+
|    2 | abc  | m    |
|    1 | aaa  | m    |
|    3 | bbb  | m    |
+------+------+------+
3 rows in set (0.00 sec)

//以查询结果创建视图user_s_view
mysql> create view user_s_view as select u.sex,s.email from t_user u join t_users s on u.name = s.name;
Query OK, 0 rows affected (0.01 sec)

//视图创建以后被当做表来使用
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t_student      |
| t_user         |
| t_users        |
| user_s_view    |
+----------------+
4 rows in set (0.00 sec)

//一开始创建视图就是把查询结果当做新的表
mysql> select * from user_s_view;
+------+-----------+
| sex  | email     |
+------+-----------+
| m    | aa@qq.com |
| m    | bb@qq.com |
+------+-----------+
2 rows in set (0.00 sec)

//对视图的更新也会对原表进行更新
mysql> update user_s_view set sex = 'f' where email = 'aa@qq.com';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from  user_s_view;
+------+-----------+
| sex  | email     |
+------+-----------+
| f    | aa@qq.com |
| m    | bb@qq.com |
+------+-----------+
2 rows in set (0.00 sec)

mysql> select * from t_user;
+------+------+------+
| id   | name | sex  |
+------+------+------+
|    2 | abc  | m    |
|    1 | aaa  | f    |
|    3 | bbb  | m    |
+------+------+------+
3 rows in set (0.00 sec)

mysql> select * from t_users;
+----+----------+------------+---------+
| id | name     | email      | address |
+----+----------+------------+---------+
|  1 | zhangsan | zs@qq.com  | aaaaaaa |
|  2 | wangwu   | ww@163.com | bbbbbbb |
|  3 | zhaoliu  | zl@qq.com  | ccccccc |
|  4 | aaa      | aa@qq.com  | aaaaaaa |
|  5 | bbb      | bb@qq.com  | bbbbbbb |
+----+----------+------------+---------+
5 rows in set (0.00 sec)

mysql> update  user_s_view set email = 'sb@qq.com' where sex = m;
ERROR 1054 (42S22): Unknown column 'm' in 'where clause'
mysql> update  user_s_view set email = 'sb@qq.com' where sex = 'm';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user_s_view;
+------+-----------+
| sex  | email     |
+------+-----------+
| f    | aa@qq.com |
| m    | sb@qq.com |
+------+-----------+
2 rows in set (0.00 sec)

mysql> select * from t_users;
+----+----------+------------+---------+
| id | name     | email      | address |
+----+----------+------------+---------+
|  1 | zhangsan | zs@qq.com  | aaaaaaa |
|  2 | wangwu   | ww@163.com | bbbbbbb |
|  3 | zhaoliu  | zl@qq.com  | ccccccc |
|  4 | aaa      | aa@qq.com  | aaaaaaa |
|  5 | bbb      | sb@qq.com  | bbbbbbb |
+----+----------+------------+---------+
5 rows in set (0.00 sec)

//通过视图插入数据
mysql> insert into  user_s_view(sex,email) values('f','ifly@qq.com');
ERROR 1393 (HY000): Can not modify more than one base table through a join view 'test.user_s_view'
mysql> insert into t_user(id,name,sex) values(4,'Jack','m');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_user;
+------+------+------+
| id   | name | sex  |
+------+------+------+
|    2 | abc  | m    |
|    1 | aaa  | f    |
|    3 | bbb  | m    |
|    4 | Jack | m    |
+------+------+------+
4 rows in set (0.00 sec)

mysql> drop view  user_s_view;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t_student      |
| t_user         |
| t_users        |
+----------------+
3 rows in set (0.00 sec)

MySQL之DBA

---

DBA——Database Administrator数据库管理员
DBA常用命令

1. 新建用户：create user username identified by ‘password’;
   如：create user zdz identified by '123456'; //此时数据库除了root账户，还有一个zdz的用户，他的密码是123456
2. 授权等等。。。。（不是DBA了解了解就行）

重点：数据的导入与导出（数据的备份）

1. 数据导出：mysqldump test>d:\test.sql -uroot -p123456

C:\Windows\system32>mysqldump test>d:\MySQLTest\test.sql -uroot -p111111

注意不是在mysql->下写命令

导出指定表：C:\Windows\system32>mysqldump test t_user>d:\MySQLTest\test.sql -uroot -p111111
》

2. 数据导入：
   注意：需要先登录到mysql数据库服务器上。
   然后创建数据库：create database test;
   使用数据库：use test;
   然后初始化数据库：
   mysql> source D:\MySQLTest\test.sql

MySQL之范式

---

数据库设计的三范式

1. 概念：
   范式是数据库表的设计依据。是教我们怎么进行数据库表的设计的思想。
2. 范式划分：
   1. 第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。
   2. 第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。
   3. 第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。

面试官常问，要熟记
设计数据库表的时候，按照以上的范式进行，可以避免表中数据的冗余和空间的浪费

第一范式（最核心，最重要）

所有表的设计都需要满足，必须有主键，并且每一个字段都是原子性不可再分
以下表为例：

学生编号	学生姓名	联系方式
1001	张三	zs@qq.com,13599999999
1002	李四	ls@qq.com,13588888888
1001	王五	ww@qq.com,135666666666

该表就不满足第一范式，第一没有主键，第二联系方式可以分为邮箱地址和电话

第二范式

建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。
以下表为例：

学生编号	学生姓名	教师编号	教师姓名
1001	张三	001	王老师
1002	李四	002	赵老师
1001	王五	001	王老师

上表，首先不满足第一范式
修改：

学生编号（pk）	学生姓名	教师编号（pk）	教师姓名
1001	张三	001	王老师
1002	李四	002	赵老师
1002	王五	001	王老师

学生编号和教师编号两个字段联合做主键，复合主键（pk：学生编号+教师编号），此时满足第一范式，但不满足第二范式，张三依赖1001，王老师依赖001，显然产生了部分依赖（此时会造成数据冗余，空间浪费）
修改：使用三张表来表示多对多的关系——学生表、教师表、学生教师关系表
学生表：

学生编号（pk）	学生名字
1001	张三
1002	李四
1003	王五

教师表：

教师编号（pk）	教师姓名
001	王老师
002	赵老师

学生教师关系表

id（pk）	学生编号（fk）	教师编号（fk）
1	1001	001
2	1002	002
3	1003	001

背口诀：
多对多设计：多对多，三张表，关系表两个外键

第三范式

第三范式建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。
以下表为例：

学生编号 （pk）	学生姓名	班级编号	班级名称
1001	张三	01	一年一班
1002	李四	02	一年二班
1003	王五	03	一年三班
1004	赵六	03	一年三班

以上表是一对多关系
满足第一范式，有主键
满足第二范式，因为主键不是复合主键，没有产生部分依赖
不满足第三范式班，班级名称依赖班级编号，班级编号依赖主键，产生了传递依赖

设计修改，改成连个表

班级编号（pk）	班级名称
01	一年一班
02	一年二班
03	一年三班

学生表

学生编号（pk）	学生姓名	班级编号（fk）
1001	张三	01
1002	李四	02
1003	王五	03
1004	赵六	03

背口诀：
一对多设计：一对多，两张表，多的表加外键

总结

一对多：
一对多关系的表设计：一对多，两张表，多的表加外键
多对多：
多对多关系的表设计：多对多，三张表，关系表两个外键
一对一：
在实际的开发当中，可能存在一张表字段太多，太庞大。这个时候需要拆分表

login_id(fk+unique)——使用外键加唯一性约束（共享主键）
口诀：一对一，外键+唯一！！！！

数据库设计三范式是理论上的，实践和理论有的时候有偏差。最终的目的都是未来满足客户需求，有的时候会拿冗余换速度。因为在sql当中，表和表之间连接次数越多，效率越低。有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的，并且对于开发人员来说，SQL语句的编写难度也会降低。