MySQL视图 , 索引
mysql视图是什么?
mysql视图(view) 是一种虚拟存在的表, 同实表一样, 视图也是有列和行构成, 但视图并不实际存在于数据库中, 行和列的数据来自于定义视图的查询语句, 实际上视图表并不真实存在数据, 只是单独的存放查询语句, 并且还是在使用视图时动态生成的
数据库中只存放的视图的定义, 即查询语句,并没有将数据存放于视图, 这些数据存放在真实的表中, 使用视图时调用里面从查询语句生成临时的数据展示出来, 即数据库会从真实表中取出对应的数据, 因此, 视图中的数据是依赖于真实表中的数据的, 一旦真实表中的数据发生变化, 视图表中的数据也会跟着发生变化.
视图可以从原有的表上选取对应用户的信息, 哪些对用户没用, 或者用户没有权限了解的信息, 都可以直接屏蔽掉, 作用类似于筛选, 这样即使用了简单化, 也保证了系统的安全
建表语句:
department表
create table department(d_id int not null primary key,
d_name varchar(20) not null unique , function varchar(20),
address varchar(100)
);
worker表
create table worker(num int not null primary key,
d_id int , name varchar(20) not null ,
sex char(10) not null ,
birthday datetime ,
homeaddress varchar(50)
) ;例如: 下面的数据库中有一张公司部门表 department. 表中包括部门号(d_id) 部门名称(d_name) 功能(function) 和办公地址(address) 表结构如下:
mysql> desc department;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| d_id | int(4) | NO | PRI | NULL | |
| d_name | varchar(20) | NO | | NULL | |
| function | varchar(50) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)还有一张工表 worker. 表中包含了员工的工号(num). 部门号(d_id) 姓名(name) 性别(sex) 出生日期(birthday) 和家庭住址 (homeaddress) 表结构如下:
mysql> desc worker;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| num | int(10) | NO | PRI | NULL | |
| d_id | int(4) | YES | | NULL | |
| name | varchar(20) | NO | | NULL | |
| sex | varchar(4) | NO | | NULL | |
| birthday | datetime | YES | | NULL | |
| homeaddress | varchar(50) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)由于部门领导的权限范围不同, 因此, 各部门的领导只能看到该部门的员工信息, 而且, 领导可能不关心员工的生日和家庭住址, 为了达到这个目的, 可以为各部门的领导建立一个视图, 通过该视图, 领导只能看到本部门员工指定信息
例如，为生产部门建立一个名为 product view 的视图。通过视图 product view，生产部门的领导只能看到生产部门员工的工作号、姓名和性别等信息。这些 department 表的信息和 worker 表的信息依然存在于各自的表中，而视图 product_view 中不保存任何数据信息。当 department 表和 worker 表的信息发生改变时，视图 product_view 显示的信息也会发生相应的变化。
SQL语句实现:
create view product_view as
select worker.num,worker.name,worker.sex,department.d_name from
department inner join worker on department.d_id=worker.d_id;
表中数据随意插入,能达到效果就行
效果如下:
mysql> select * from product_view;
+-----+-------+--------+-----------+
| num | name | sex | d_name |
+-----+-------+--------+-----------+
| 1 | chen | male | 技术部 |
| 2 | liurx | male | 销售部 |
| 3 | xiuy | female | 技术部 |
| 4 | mes | female | 运营部 |
| 5 | sec | male | 人事部 |
| 6 | mie | male | 运营部 |
+-----+-------+--------+-----------+
6 rows in set (0.00 sec)技巧: 如果经常需要很多个表查询指定字段的数据, 可以在这些表上建立一个视图, 通过这个视图显示这些字段的数据
MySQL 的视图不支持传入参数的功能, 因此交互性上还是有欠缺的, 但对于变化不大的操作. 使用视图可以很大程度上简单化用户的操作
总结视图与普通表的区别, 有一下几点
- 视图不是数据库中真实的数据表, 而是一张虚拟表, 其结构和数据是建立在数据中真实表的查询基础上的. 调用时会自动触发查询语句进行查询后拼接成一张虚拟表展示
- 存储在数据库中的查询操作SQL语句定义了视图的内容, 列数据和行数据来之于视图查询所引用的实际表, 引用视图动态生成这些数据的
- 视图没有实际的物理记录, 不是以数据集的形式存储在数据库中的, 它所对应的数据实际上是存储在视图索索引的真实表中
- 视图是数据的窗口, 而表示内容的真实存放地, 而视图只是以不同的展示方式展示数据, 其数据来源还是真实表的索引
- 视图是查看数据的一种方法, 可以查询数据表中某些字段构成的数据, 只是一些SQL语句的集合, 从安全的角度来看, 视图的数据安全性更高, 使用视图的用户不接触数据表, 不知道数据表结构
- 视图的建立和删除只会影响视图本身, 不会对真实数据表造成影响
视图的优质
视图与表在本质上虽然不同, 但是视图经过定义后, 结构形成和表一样, 可以进行查询, 修改, 更新, 和删除等操作, 同时, 具有以下优点
- 定制用户数据, 聚焦特定的数据
在实际的应用过程中, 不同的用户可能对不同的数据有不同的要求
例如 当数据库存在时, 学生基本信息表和课程表and老师信息表等多种表同时存在时, 可以根据需求让不同的用户使用不同的数据, 学生查看修改自己基本信息的视图, 安排课程人员查看修改课程表和老师信息视图, 教师课程学生信息和课程学校视图 - 简单化数据操作
在使用查询时, 很多时候要使用聚合函数, 同时不要显示其他字段的信息, 可能还需要关联到其他表, 语句肯能会很长, 如果这个表动作频繁发生的话, 可以创建视图来简化操作 - 提高数据的安全性
视图是虚拟的, 物理上是不存在的 可以值授予用户视图权限, 而不具体制定使用表的权限, 来保护基础数据的安全 - 共享所需要数据
通过使用视图, 每个用户不必都定义和存储自己所需的数据, 可以共性数据库中的数据, 同样的数据只需要存储一次 - 更改数据格式
通过使用视图, 可以重新格式化检索出数据, 并组织到其他语言程序中 - 重用SQL语句
视图提供的是对查询操作的封装，本身不包含数据，所呈现的数据是根据视图定义从基础表中检索出来的，如果基础表的数据新增或删除，视图呈现的也是更新后的数据。视图定义后，编写完所需的查询，可以方便地重用该视图。
MySQL创建视图(create view)
前言: 创建视图是指在已经在MySQL数据库表上建立视图, 视图可以建立在一张表中, 也可以建立在多张表中.
基本语法
语法格式:
create view <视图名> as <select语句>语法说明.
-
<视图名>: 指定视图的名称, 该名称在视图库中必须是唯一的, 不能与其他表或视图同名 -
<select 语句>: 指定创建视图的select 语句, 可用于查询多个基础表或原始图
对于创建视图中 select 语句的指定存在以下限制
- 用户处理拥由create view 权限外, 还具有操作种涉及的基础表和其他视图表的相关权限
- select 语句不能引用系统或用户变量
- select 语句不能包含from 子句中的子查询
- select 语句不能引用预处理语句参数
视图定义中引用的表或视图必须存在, 但是, 创建完成后, 可以删除定义引用的表或视图, 可以使用create table 语句检查视图定义是否存在这个类问题
视图定义允许使用 order by 语句, 但是若从特定视图进行选择, 而该视图使用了自己的 order by 语句, 则视图定义中的order by 将被忽略
视图定义中不能引用temporary表(临时表) 不能创建temporary视图
with check option 的意思是, 修改视图时, 修改视图时 , 检查插入的数据是否符合 where 设置的条件
创建基于单表的视图
创建测试数据库test 库中创建tb_student_info表 展示如下:
SQL语句:
create table tb_student_info(
id int not null primary key, name varchar(20) not null ,
dept_id int ,age tinyint, sex char(10), height float,login_date datetime
);
表:
mysql> select * from tb_student_info;
+----+---------+---------+------+------+--------+---------------------+
| id | name | dept_id | age | sex | height | login_date |
+----+---------+---------+------+------+--------+---------------------+
| 1 | Dany | 1 | 26 | F | 189 | 2020-09-09 18:46:27 |
| 2 | Green | 3 | 23 | F | 198 | 2020-09-09 18:46:51 |
| 3 | Henry | 2 | 22 | M | 170 | 2020-09-09 18:47:36 |
| 4 | Jane | 1 | 23 | M | 169 | 2020-09-09 18:48:05 |
| 5 | Jim | 1 | 32 | M | 174 | 2020-09-09 18:48:24 |
| 6 | John | 2 | 23 | F | 180 | 2020-09-09 18:48:47 |
| 7 | Lily | 4 | 21 | F | 172 | 2020-09-09 18:49:09 |
| 8 | Susan | 4 | 23 | F | 162 | 2020-09-09 18:49:31 |
| 9 | Thomas | 6 | 22 | M | 175 | 2020-09-09 18:49:51 |
| 10 | Tom | 4 | 34 | M | 184 | 2020-09-09 18:50:11 |
+----+---------+---------+------+------+--------+---------------------+
10 rows in set (0.00 sec)在tb_student_info 表上创建一个名为view_student_info的视图, 输入的语句和执行结果如下
简单的视图创建
mysql> create view view_student_info as select * from tb_student_info;
与前表一样, 只是在视图表中存放的是sql语句, 不是真实的数据
mysql> select * from view_student_info;
+----+---------+---------+------+------+--------+---------------------+
| id | name | dept_id | age | sex | height | login_date |
+----+---------+---------+------+------+--------+---------------------+
| 1 | Dany | 1 | 26 | F | 189 | 2020-09-09 18:46:27 |
| 2 | Green | 3 | 23 | F | 198 | 2020-09-09 18:46:51 |
| 3 | Henry | 2 | 22 | M | 170 | 2020-09-09 18:47:36 |
| 4 | Jane | 1 | 23 | M | 169 | 2020-09-09 18:48:05 |
| 5 | Jim | 1 | 32 | M | 174 | 2020-09-09 18:48:24 |
| 6 | John | 2 | 23 | F | 180 | 2020-09-09 18:48:47 |
| 7 | Lily | 4 | 21 | F | 172 | 2020-09-09 18:49:09 |
| 8 | Susan | 4 | 23 | F | 162 | 2020-09-09 18:49:31 |
| 9 | Thomas | 6 | 22 | M | 175 | 2020-09-09 18:49:51 |
| 10 | Tom | 4 | 34 | M | 184 | 2020-09-09 18:50:11 |
+----+---------+---------+------+------+--------+---------------------+
10 rows in set (0.00 sec)默认情况下, 创建的视图和基本表的字段是一样的, 也可以通过制定视图字段的的名字来创建视图
例:
在tb_student_inof 表上创建一个名为 v_student_info 的视图 输入SQL语句和执行结果如下
....
create view v_student_info
(s_id,s_name,d_id,s_age,s_sex,s_height,s_date)
as select id,name,dept_id,age,sex,height,login_date
from tb_student_info;
mysql> select * from v_student_info;
+------+---------+------+-------+-------+----------+---------------------+
| s_id | s_name | d_id | s_age | s_sex | s_height | s_date |
+------+---------+------+-------+-------+----------+---------------------+
| 1 | Dany | 1 | 26 | F | 189 | 2020-09-09 18:46:27 |
| 2 | Green | 3 | 23 | F | 198 | 2020-09-09 18:46:51 |
| 3 | Henry | 2 | 22 | M | 170 | 2020-09-09 18:47:36 |
| 4 | Jane | 1 | 23 | M | 169 | 2020-09-09 18:48:05 |
| 5 | Jim | 1 | 32 | M | 174 | 2020-09-09 18:48:24 |
| 6 | John | 2 | 23 | F | 180 | 2020-09-09 18:48:47 |
| 7 | Lily | 4 | 21 | F | 172 | 2020-09-09 18:49:09 |
| 8 | Susan | 4 | 23 | F | 162 | 2020-09-09 18:49:31 |
| 9 | Thomas | 6 | 22 | M | 175 | 2020-09-09 18:49:51 |
| 10 | Tom | 4 | 34 | M | 184 | 2020-09-09 18:50:11 |
+------+---------+------+-------+-------+----------+---------------------+
10 rows in set (0.00 sec)可以看到, view_student_info 和 v_student_info两个视图中的字段名称不同, 但是数据却相同, 因此, 在使用视图时, 可能用户不需要了解表结构, 更接触不到实际表中的数据, 从而保证了数据的安全性
创建基于多表的视图
通过这个视图可以很好的保护基本表中的数据, 视图中包含s_id, s_name和dept_name, s_id字段对应tb_student_info表中的id字段, s_name对应tb_student_info表中的name字段, dept_name字段对应tb_student_info表中的dept_name字段
查询视图
视图一定定义后, 就可以如同查询表数据一样, 使用select 语句查询视图中的数据, 语法和程序基础表的数据一样
视图用于查询追表应用在一下几个方面
- 使用视图重新格式化索引出的数据
- 使用视图简单化复杂的表连接
- 使用视图过滤数据
describe 简写desc 可以用来查看视图, 两者使用得到的结果是一样的
语法: desc 视图名 ; 或 describe 视图名;查看视图详细信息
使用方法和基本表是一样的
语法: show create view 视图表;通过以上例子查看前表的信息信息
一大坨乱七八糟的
mysql> show create view v_student_info\G;
*************************** 1. row ***************************
View: v_student_info
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `v_student_info` AS select `tb_student_info`.`id` AS `s_id`,`tb_student_info`.`name` AS `s_name`,`tb_student_info`.`dept_id` AS `d_id`,`tb_student_info`.`age` AS `s_age`,`tb_student_info`.`sex` AS `s_sex`,`tb_student_info`.`height` AS `s_height`,`tb_student_info`.`login_date` AS `s_date` from `tb_student_info`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)上述 SQL 语句以\G结尾，这样能使显示结果格式化。如果不使用\G，显示的结果会比较混乱，如下所示：
mysql> desc v_student_info;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| s_id | int(11) | NO | | NULL | |
| s_name | varchar(20) | NO | | NULL | |
| d_id | int(11) | YES | | NULL | |
| s_age | tinyint(4) | YES | | NULL | |
| s_sex | char(10) | YES | | NULL | |
| s_height | float | YES | | NULL | |
| s_date | datetime | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)阅读扩展
所有的视图定义都是存储在information_schema数据库下的views表中, 也可以在这个表中查看所有视图的详细信息, SQL语句如下
select * from information_schema.views;
查的巨多数据, 就不粘贴到这里了视图的修改
跟创建视图完全一样, 就是修改一个参数,如下:
alter view <视图名> as <select语句>
就修改一个 create >> alter 完成~ 还有 update修改视图内容
视图是一个虚拟表, 实际数据存放在基本表, 所以通过插入,修改,删除操作更新视图表中的数据, 实质上是在更新视图索引的基本表数据
注意: 对视图表修改就是对基本表的修改, 因此在修改时, 要满足基本表的数据定义
某些视图是可以更新的, 也就是说, 可以使用update, delete, insert等语句更新基本表内容, 对于更新视图, 视图中的行和基本表的行之间具有一一对应的关系
还有一些特定的结构, 这些结构会是的视图不可更新, 更具体的将, 如果视图包含以下结构中的任意一种, 他就是不可更新的
- 聚合函数sum(). min() max() count()
- distinct 关键字
- group by 句子
- having 句子
- union 或 union all 运算符
- 位于选择列表中的子查询
- from 句子中的不可更新视图或包含多个表
- where 工作中的子查询 引用 from 句子中的表
-
algorithm选项 为temptable(使用临时表总会使视图称为不可更新的) 的时候
删除视图
语法: drop view <视图名>;其中, <视图名> 指定要删除的视图名, drop view 语句可以一次删除多个视图, 但是每个视图上都要有drop权限
索引
索引是一种特殊的数据结构, 有数据表中的一列或多列组合而成, 可以用来快速查询数据表中的某一特定记录, 通过索引, 查询数据时不用读完记录的偶有的信息, 而只是查询索引列, 否则数据库系统将对其每条记录的所有信息进行匹配
可以把索引比作新华字典, 找字会先看目录后精准的确定到那一页
因此, 使用索引可以很大程度上提高数据库的查询速度, 还有效的提高数据库系统性能
MySQL中, 通常有一下两种访问数据库表的行数据
顺序访问
顺序访问既是全表扫描, 从头到尾逐行遍历, 直到在无序的数据中找到符合条件的目标数据或找不到直接返回empty
这样耗费大量时间 对短数据有提升效率, 对大文件不可行
索引访问
索引访问时通过遍历索引来直接访问表中的记录
这种方式的提前是对表建立索引, 在列上创建了索引之后, 查找数据是可以直接根据列上的索引找到该记录行位置, 从而快捷简便的找到数据, 索引存储了指定列数据的指针, 根据指定的顺序对这些指针进行排序
索引的优缺点
索引有其明显的优势, 也有不可避免的缺点
优点
- 通过创建唯一索引可以保证数据库表中每一行数据的唯一性
- 可以给所有的MySQL 类型设置索引
- 可以得到加快数据的查询速度, 这是使用索引最重要的原因
- 在现实数据的参考完整性方面可以加速表与表之间的连接
- 在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和查询时间
缺点
- 创建和维护索引组要耗费时间, 并且随着数据量的增加所耗费的时间也会增加
- 索引需要占用磁盘空间, 除了数据表占用空间以外, 每一个索引还要占用一定的物理空间, 如果有大量的索引, 索引文件可能比文件更快达到文件最大尺寸
- 当对表中的数据进行 增 删 改 查 的时候, 索引也要动态维护, 这样就降低了数据的维护速度
以上使用索引都要综合考虑优缺点
创建索引
基本语法
MySQL 提供了三种创建索引方法
- 使用 create index 语句
可以使用专门用于创建索引的 create index 语句在一个已有的表上创建索引, 但该语句不能创建主键
语法格式
create <索引(index)> <表名>(<列名>[<长度>][ASC|DESC])语法说明;
- <索引(index)> : 指定索引名. 一个表可以创建多个索引, 但每个索引在该表中的名称是唯一的
- <表名> : 指定要创建索引的表名
- <列名> : 指定要创建索引的列名, 通常可以考虑将查询语句中在join句子和where 句子里经常出现的列作为索引
- <长度> : 可选项. 最大限制为255字节,
- ASC | DESC 可选项. asc指定索引按照升序来排列, desc指定索引按照降序来排列, 默认为asc
- 使用 create table 语句
- create table constraint primary key
[索引类型](列表)
- 使用 alter table 语句
- alter table 表名 add xxx index 索引名;
创建普通索引
创建普通索引时，通常使用 INDEX 关键字。
创建一个表 tb_stu_info，在该表的 height 字段创建普通索引。输入的 SQL 语句和执行过程如下所示。
mysql> CREATE TABLE tb_stu_info
-> (
-> id INT NOT NULL,
-> name CHAR(45) DEFAULT NULL,
-> dept_id INT DEFAULT NULL,
-> age INT DEFAULT NULL,
-> height INT DEFAULT NULL,
-> INDEX(height)
-> );
Query OK，0 rows affected (0.40 sec)
mysql> SHOW CREATE TABLE tb_stu_info\G
*************************** 1. row ***************************
Table: tb_stu_info
Create Table: CREATE TABLE `tb_stu_info` (
`id` int(11) NOT NULL,
`name` char(45) DEFAULT NULL,
`dept_id` int(11) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`height` int(11) DEFAULT NULL,
KEY `height` (`height`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.01 sec)
INT DEFAULT NULL,
-> INDEX(height)
-> );
Query OK，0 rows affected (0.40 sec)
mysql> SHOW CREATE TABLE tb_stu_info\G
*************************** 1. row ***************************
Table: tb_stu_info
Create Table: CREATE TABLE `tb_stu_info` (
`id` int(11) NOT NULL,
`name` char(45) DEFAULT NULL,
`dept_id` int(11) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`height` int(11) DEFAULT NULL,
KEY `height` (`height`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.01 sec)