Mysql Day Nine

视图

   试图 view：是一种有结构没有结果的虚拟表，数据来源于基表，本质是执行封装的select语句

   意义：简化复杂sql 查询语句、数据相对安全，容易权限控制

  创建视图

-- create view 视图名字  as select 语句 （可以是普通查询 可以是连接查询 可以是联合查询）
-- 创建单表视图
create view v1  as select * from m_copy;
create view v2 as select * from m_primary;
-- 创建多表视图
create view v3 as select c.*,p.name as p_name,p.id as p_id from m_copy as c left join  m_primary as p on c.name=p.name;

-- 查看视图结构

show tables;
desc v1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | int(11)             | YES  |     | NULL    |       |
| name  | varchar(10)         | NO   |     | NULL    |       |
| sex   | enum('man','women') | YES  |     | NULL    |       |
| class | int(11)             | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+


show create table v1;
+------+---------------
| View | Create View                                                                                                                                                                                                      | character_set_client | collation_connection |
+------+---------------
| v1   | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `m_copy`.`id` AS `id`,`m_copy`.`name` AS `name`,`m_copy`.`sex` AS `sex`,`m_copy`.`class` AS `class` from `m_copy` | gbk                  | gbk_chinese_ci       |
+------+---------------

show create view v1;
+------+------------------
| View | Create View                                                                                                                                                                                                      | character_set_client | collation_connection |
+------+------------------
| v1   | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `m_copy`.`id` AS `id`,`m_copy`.`name` AS `name`,`m_copy`.`sex` AS `sex`,`m_copy`.`class` AS `class` from `m_copy` | gbk                  | gbk_chinese_ci       |
+------+------------------

  使用视图

select * from v1;
select * from v2;
select * from v3;

select * from v1;
+------+-------+-------+-------+
| id   | name  | sex   | class |
+------+-------+-------+-------+
|    1 | name1 | women |     4 |
|    2 | name2 | man   |     4 |
|    3 | name3 | women |     2 |
|    4 | name4 | man   |     2 |
|    5 | name5 | women |     1 |
|    6 | name6 | women |     1 |
|    7 | name7 | women |     3 |
|    8 | name8 | women |     3 |
|    9 | name1 | man   |     4 |
|   10 | name2 | man   |     4 |
|   11 | name3 | women |     2 |
|   12 | name4 | man   |     2 |
|   13 | name5 | man   |     1 |
|   14 | name6 | man   |     1 |
|   15 | name7 | man   |     3 |
|   16 | name8 | women |     3 |
+------+-------+-------+-------+


select * from v2;
+-------+----+
| name  | id |
+-------+----+
| name1 |  1 |
| name2 |  2 |
+-------+----+


select * from v3;
+------+-------+-------+-------+--------+------+
| id   | name  | sex   | class | p_name | p_id |
+------+-------+-------+-------+--------+------+
|    1 | name1 | women |     4 | name1  |    1 |
|    9 | name1 | man   |     4 | name1  |    1 |
|    2 | name2 | man   |     4 | name2  |    2 |
|   10 | name2 | man   |     4 | name2  |    2 |
|    3 | name3 | women |     2 | NULL   | NULL |
|    4 | name4 | man   |     2 | NULL   | NULL |
|    5 | name5 | women |     1 | NULL   | NULL |
|    6 | name6 | women |     1 | NULL   | NULL |
|    7 | name7 | women |     3 | NULL   | NULL |
|    8 | name8 | women |     3 | NULL   | NULL |
|   11 | name3 | women |     2 | NULL   | NULL |
|   12 | name4 | man   |     2 | NULL   | NULL |
|   13 | name5 | man   |     1 | NULL   | NULL |
|   14 | name6 | man   |     1 | NULL   | NULL |
|   15 | name7 | man   |     3 | NULL   | NULL |
|   16 | name8 | women |     3 | NULL   | NULL |
+------+-------+-------+-------+--------+------+

  修改视图

-- 修改视图其实是修改select语句
-- alter view 视图名 as 新select语句
alter view v1 as select id,name,sex from m_copy;
select * from v1;
+------+-------+-------+
| id   | name  | sex   |
+------+-------+-------+
|    1 | name1 | women |
|    2 | name2 | man   |
|    3 | name3 | women |
|    4 | name4 | man   |
|    5 | name5 | women |

  视图删除

-- drop view 视图名;
create view v4 as select * from m_primary;
drop  view v4;

  视图操作数据

-- 新增数据
-- 1.多表时不能插入数据
-- 2.单表视图插入数据时，视图中包含的字段必须包含不允许为空和没有默认值的全部字段
insert into v2 values("name3","3");


-- 删除数据
-- 1.多表视图不能删除数据
delete from v2  where id=3；


-- 更新数据
update v2 set name="jjjjjj" where id=1;

视图算法

    undefined（默认）：表示视图没有自定义视图算法

    temptable ：视图应该先执行视图的select语句，后执行外部查询语句

    merge：系统应该将视图对应的select语句与外部的视图查询语句进行合并让后在执行

-- 指点视图算法
create algorithm=temptable view v6 as selcet * from m_copy;

select * from m_copy;
+-------+-------+
| id   | name  | sex   |
+------+-------+-------+
|    1 | name1 | women |
|    2 | name2 | man   |
|    3 | name3 | women |
|    4 | name4 | man   |
|    5 | name5 | women |
|    6 | name6 | women |
|    7 | name7 | women |
|    8 | name8 | women |
|    9 | name1 | man   |
|   10 | name2 | man   |
|   11 | name3 | women |
|   12 | name4 | man   |
|   13 | name5 | man   |
|   14 | name6 | man   |
|   15 | name7 | man   |
|   16 | name8 | women |
+------+-------+-------+
create algorithm=merge view v8 as select * from m_copy order by name desc;


create algorithm=temptable view v7 as select * from m_copy order by name desc;


create view v6 as select * from m_copy order by name desc;


select * from v7 group by sex;
+------+-------+-------+-------+
| id   | name  | sex   | class |
+------+-------+-------+-------+
|    8 | name8 | women |     3 |
|   15 | name7 | man   |     3 |
+------+-------+-------+-------+
2 rows in set (0.03 sec)

select * from v6 group by sex;
+------+-------+-------+-------+
| id   | name  | sex   | class |
+------+-------+-------+-------+
|    1 | name1 | women |     4 |
|    2 | name2 | man   |     4 |
+------+-------+-------+-------+
2 rows in set (0.00 sec)

select * from v8 group by sex;
+------+-------+-------+-------+
| id   | name  | sex   | class |
+------+-------+-------+-------+
|    1 | name1 | women |     4 |
|    2 | name2 | man   |     4 |
+------+-------+-------+-------+