视图view

概述

view:站在不同的角度去看待同一份数据

创建与删除

创建视图对象:

例:

mysql> create view test_view as select * from test;
Query OK, 0 rows affected (0.01 sec)

删除视图对象:

例:

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

注:只有DQL语句才能以view的形式创建,相当于as后面跟的必须是DQL语句

视图作用

我们可以面向视图对象进行增删改查,对视图对象的增删改查,会导致原表被操作

例:

先看一下原表与视图

原表:

mysql> select * from test;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

视图:

mysql> select * from test_view;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.01 sec)

面向视图插入:

mysql> insert into test_view(deptno,dname,loc) values(60,'SALES','Toyko');
Query OK, 1 row affected (0.00 sec)

再次查询原表数据,发现也同样插入了数据:

mysql> select * from test;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     60 | SALES      | Toyko    |
+--------+------------+----------+
5 rows in set (0.00 sec)

面向视图删除:

mysql> delete from test_view;
Query OK, 5 rows affected (0.00 sec)

查询原表数据,发现原表数据也同样被删除了:

mysql> select * from test;
Empty set (0.00 sec)
总结

假设有一条非常复杂的SQL语句,而这条SQL语句需要在不同的位置上反复使用,每一次使用这条SQL语句的时候都需要重新编写,很麻烦,就可以把这条复杂的SQL语句以视图对象的形式新建,在需要编写这条SQL语句的位置直接使用视图对象,可以大大简化开发

并且利于后期的维护,因为修改的时候也只需修改一个位置即可,只需要修改视图对象所映射的SQL语句,在以后面向视图开发的时候,使用视图也可以像使用table一样,可以对视图进行增删改查等操作,视图不是存放在内存当中,视图对象也是存放在硬盘上的,不会消失

数据库设计三范式

概述

第一范式:要求任何一张表必须有主键,每一个字段原子性不可再分

第二范式:建立在第一范式的基础之上,要求所有非主键字段完全依赖主键,不要产生部分依赖

第三范式:建立在第二范式的基础之上,要求所有非主键字段直接依赖,不要产生传递依赖

设计数据库表的时候,按照以上的范式进行,可以避免表中数据的冗余,空间的浪费

第一范式

先看一张不满足的表:

学生编号学生姓名联系方式
1001YQHP-YuKiYY@mail.com 1345678932323
1002XPXP@mail.com 2321332321312
1003HarryHH@mail.com 999928293283

不满足:

  1. 没有主键
  2. 联系方式可以分为邮箱与电话,不满足原子性不可再分

改为正确的表:

学生编号(PK)学生姓名邮箱地址联系电话
1001YQHP-YuKiYY@mail.com1345678932323
1002XPXP@mail.com2321332321312
1003HarryHH@mail.com999928293283
第二范式

先看一张连第一范式都不符合的表

学生表学生姓名教师编号教师姓名
1001李桑001彭老师
1002信桑002代老师
1003小野桑001彭老师
1001李桑002代老师

这张表描述了学生和老师的关系,1个学生可能有多个老师,1个老师有多个学生,典型的多对多关系

但因为不满足第一范式,从而对其进行修改

学生编号+教师编号(PK)学生姓名教师姓名
1001001李桑彭老师
1002002信桑代老师
1003001小野桑彭老师
1001002李桑代老师

但却不满足第二范式,因为李桑依赖1001,彭老师依赖001,产生了部分依赖,从而导致了数据冗余,空间浪费,例如李桑重复了,彭老师重复了

所以对其进行修改,使其满足第二范式,需要使用3张表来表示多对多的关系

  • 学生表
学生编号(PK)学生姓名
1001李桑
1002信桑
1003小野桑
  • 教师表
教师表(PK)教师姓名
001彭老师
002代老师
  • 学生教师关系表
id(PK)学生编号(fk)教师编号(fk)
11001001
21002002
31003001
41001002
第三范式

先看这一张一对多的表,满足第一与第二范式

学生编号(PK)学生姓名班级编号班级名称
1001小李01一年级一班
1002小张02一年级二班
1003小王03一年级三班
1004小寒03一年级三班

满足第二范式,因为主键不是复合主键,没有产生部分依赖,主键是单一主键,但不满足第三范式,因为一年级一班依赖01,01依赖1001,产生了传递依赖

这时改成满足第三范式的表

  • 班级表
班级编号(PK)班级名称
01一年级一班
02一年级二班
03一年级三班
  • 学生表
学生编号(PK)学生姓名班级编号(fk)
1001小李01
1002小张02
1003小王03
1004小寒03
总结

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

Last modification:March 13th, 2021 at 09:37 am