SQL-视图（15）
2018-09-01 本文已影响14人
小白201808
1.含义：
mysql5.1出现的新特性，本身是一张虚拟表，和普通的表一样使用，它的数据来自于表，通过执行时动态生成。
2.视图和表的区别：
使用方式 占用物理空间
视图 完全相同 不占用，仅仅保存的是sql逻辑
表 完全相同 占用
3.视图的好处：
1、sql语句提高重用性，效率高
2、和表实现了分离，提高了安全性
一.视图的创建
语法：
CREATE VIEW 视图名
AS
查询语句;
案例：创建一个视图emp_v1，要求查询部门的最高工资高于12000的部门的信息
mysql> #创建一个视图emp_v1，要求查询部门的最高工资高于12000的部门的信息
mysql> create view emp_v1
-> as
-> select max(salary) mx,department_id from employees group by department_id having mx>12000;
Query OK, 0 rows affected (0.08 sec)
mysql> select d.*,m.mx from departments d join emp_v1 m on m.department_id = d.department_id;
+---------------+-----------------+------------+-------------+----------+
| department_id | department_name | manager_id | location_id | mx |
+---------------+-----------------+------------+-------------+----------+
| 20 | Mar | 201 | 1800 | 13000.00 |
| 80 | Sal | 145 | 2500 | 14000.00 |
| 90 | Exe | 100 | 1700 | 24000.00 |
+---------------+-----------------+------------+-------------+----------+
3 rows in set (0.15 sec)
注意⚠️：视图我们一般不更新数据，一般只有简单的视图才能实现数据的增删改。大多情况视图只做查询，我们会为视图添加只读权限，或者说一般视图的查询语句都不是简单地查询的一个表的数据，当涉及复杂语句时，视图的数据我们是修改不了的，会报错的。
二.视图的增删改查
1、查看视图的数据 ★(和表的查看一样道理）
SELECT * FROM 视图名;
mysql> select * from emp_v1;
+----------+---------------+
| mx | department_id |
+----------+---------------+
| 13000.00 | 20 |
| 14000.00 | 80 |
| 24000.00 | 90 |
+----------+---------------+
3 rows in set (0.00 sec)
mysql> select * from emp_v1 where mx=13000;
+----------+---------------+
| mx | department_id |
+----------+---------------+
| 13000.00 | 20 |
+----------+---------------+
1 row in set (0.29 sec)
2、插入视图的数据
insert into 视图名 （列名...) valuse (...)
3、修改视图的数据
update 视图名 set 字段 where 筛选条件
4、删除视图的数据
DELETE FROM 视图名;
创建一个简单视图进行简单修改
mysql> create view my_v1
-> as
-> select last_name ,email
-> from employees;
Query OK, 0 rows affected (0.20 sec)
mysql>#插入数据，添加‘小白’
mysql> insert into my_v1 values('小白','1234@qq.com');
Query OK, 1 row affected (0.19 sec)
mysql> select * from my_v1;
+-------------+-------------+
| last_name | email |
+-------------+-------------+
| K_ing | SKING |
...
...
| Gietz | WGIETZ |
| 小白 | 1234@qq.com |
+-------------+-------------+
108 rows in set (0.00 sec)
#也影响了实际表的数据，我没骗你吧！！！
mysql> select * from employees;
+-------------+-------------+-------------+-------------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name | last_name | email | phone_number | job_id | salary | commission_pct | manager_id | department_id | hiredate |
+-------------+-------------+-------------+-------------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
| 100 | Steven | K_ing | SKING | 515.123.4567 | AD_PRES | 24000.00 | NULL | NULL | 90 | 1992-04-03 00:00:00 |
...
...
| 207 | NULL | 小白 | 1234@qq.com | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+-------------+-------------+-------------+-------------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
108 rows in set (0.00 sec)
mysql> #修改，将‘小白’名字改为’小黑‘
mysql> update my_v1 set last_name='小黑' where last_name ='小白';
Query OK, 1 row affected (0.18 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from my_v1;
+-------------+-------------+
| last_name | email |
+-------------+-------------+
| K_ing | SKING |
...
...
| Gietz | WGIETZ |
| 小黑 | 1234@qq.com |
+-------------+-------------+
108 rows in set (0.06 sec)
mysql> select * from employees;
+-------------+-------------+-------------+-------------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name | last_name | email | phone_number | job_id | salary | commission_pct | manager_id | department_id | hiredate |
+-------------+-------------+-------------+-------------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
| 100 | Steven | K_ing | SKING | 515.123.4567 | AD_PRES | 24000.00 | NULL | NULL | 90 | 1992-04-03 00:00:00 |
...
...
| 207 | NULL | 小黑 | 1234@qq.com | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+-------------+-------------+-------------+-------------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
108 rows in set (0.01 sec)
#删除‘小黑’
mysql> delete from my_v1 where last_name = '小黑';
Query OK, 1 row affected (0.14 sec)
#查看会发现没有小黑的信息了就剩107条记录了。
mysql> select * from my_v1;
+-------------+----------+
| last_name | email |
+-------------+----------+
| K_ing | SKING |
...
...
| Gietz | WGIETZ |
+-------------+----------+
107 rows in set (0.00 sec)
mysql> select * from employees;
+-------------+-------------+-------------+----------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
| employee_id | first_name | last_name | email | phone_number | job_id | salary | commission_pct | manager_id | department_id | hiredate |
+-------------+-------------+-------------+----------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
| 100 | Steven | K_ing | SKING | 515.123.4567 | AD_PRES | 24000.00 | NULL | NULL | 90 | 1992-04-03 00:00:00 |
...
...
| 206 | William | Gietz | WGIETZ | 515.123.8181 | AC_ACCOUNT | 8300.00 | NULL | 205 | 110 | 2016-03-03 00:00:00 |
+-------------+-------------+-------------+----------+--------------------+------------+----------+----------------+------------+---------------+---------------------+
107 rows in set (0.00 sec)
四.某些视图数据不能更新
包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
常量视图
Select中包含子查询
join
from一个不能更新的视图
where子句的子查询引用了from子句中的表
五.视图逻辑的更新
#方式一：
CREATE OR REPLACE VIEW 视图名
AS
查询语句
CREATE OR REPLACE VIEW test_v1
AS
SELECT last_name FROM employees
WHERE employee_id>100;
#方式二:
ALTER VIEW 视图名
AS
查询语句
ALTER VIEW test_v1
AS
SELECT employee_id FROM employees;
SELECT * FROM test_v1;
六.视图的删除
DROP VIEW 视图名1,视图名2 ...视图名n;(可以删除多个）
七.视图结构的查看
1.DESC 视图名;
mysql> desc emp_v1;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| mx | double(10,2) | YES | | NULL | |
| department_id | int(4) | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
2.SHOW CREATE VIEW 视图名;（查询出的内容很详细）
mysql> show create view emp_v1;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| emp_v1 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `emp_v1` AS select max(`employees`.`salary`) AS `mx`,`employees`.`department_id` AS `department_id` from `employees` group by `employees`.`department_id` having (`mx` > 12000) | utf8 | utf8_general_ci |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.06 sec)
注：这是本人的学习笔记及练习，如果有错误的地方望指出一起讨论，谢谢！
