526互联

10. 视图

发布时间 2023-09-12 23:18:35作者: bingo-HF

 一. 创建视图

--
-- 创建视图
--
root@mysqldb 09:28:  [test]> create view view_t4 as select * from t4;
Query OK, 0 rows affected (0.00 sec)
-- 也可以对select结果增加条件进行过滤后，再创建视图

root@mysqldb 09:33:  [test]> show create table t4\G
*************************** 1. row ***************************
       Table: t4
Create Table: CREATE TABLE `t4` (  -- 得到的是表结构
  `a` int NOT NULL AUTO_INCREMENT,
  `b` int DEFAULT NULL,
  `c` varchar(20) DEFAULT 'test',
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

root@mysqldb 09:36:  [test]> show create table view_t4\G  -- 他是以一张表的形式存在的，可通过show tables看到
*************************** 1. row ***************************
                View: view_t4
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_t4` AS select `t4`.`a` AS `a`,`t4`.`b` AS `b` from `t4`
                      -- 和真正的表不同的是，这里show出来的是视图的定义
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

root@mysqldb 09:36:  [test]> select * from view_t4;  -- 可以直接查询该视图得结果
+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 2 |   20 |
| 3 |   30 |
| 4 |   40 |
| 5 |   50 |
| 6 |  100 |
+---+------+
6 rows in set (0.00 sec)
-- 视图的作用是，对开发人员是透明的，可以隐藏部分关键的列
-- 视图在MySQL是虚拟表。根据视图的定义，还是取执行定义中的select语句。

-- 只开放部分列
root@mysqldb 09:39:  [test]> create view view_t5 as select a from t4;  -- 只开放a列
Query OK, 0 rows affected (0.00 sec)

root@mysqldb 09:39:  [test]> select * from view_t5;  -- 即使 select * ，也只能看到a列，具有隐藏原来表中部分列的功能
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+---+
6 rows in set (0.00 sec)

1. 不要取用select * from 去创建视图，因为mysql会把*逐个解析成列。

2. 当原来的表结构发生变化时，视图的表结构是不会发生变化的，视图在创建的瞬间，便确定了结构。比如，当你alter原来的表 增加列(add columns)时,再去查询该视图,新增加的列是不存在的。 

3. mysql中的视图都是虚拟表。不像Oracle可以物化成真实存在的表。

4. 每次查询视图，实际上还是去查询的原来的表，只是查询的规则是在视图创建时经过定义的。 

5. 修改视图的数据原表的数据也会跟着修改

6. 不能修改视图结构

 

二. 视图的算法

  • 视图的算法( ALGORITHM )有三种方式：
    • UNDEFINED 默认方式，由MySQL来判断使用下面的哪种算法
    • MERGE ： 每次 通过 物理表 查询得到结果，把结果merge(合并)起来返回
    • TEMPTABLE ： 产生一张 临时表 ，把数据放入临时表后，客户端再去临时表取数据（ 不会缓存 ）

TEMPTABLE 特点 ：即使访问条件一样，第二次查询还是会去读取物理表中的内容，并重新生成一张临时表,并不会取缓存之前的表。（临时表是Memory存储引擎，默认放内存，超过配置大小放磁盘）当查询有一个较大的结果集时，使用 TEMPTABLE 可以快速的结束对该物理表的访问，从而可以快速

释放这张物理表上占用的资源。然后客户端可以对临时表上的数据做一些耗时的操作，而不影响原来的物理表。

所以一般我们使用 UNDEFINED ，由MySQL自己去判断

 

三. UNION

1. UNION 的作用是将两个查询的结果集进行合并。

2. UNION必须由 两条或两条以上 的SELECT语句组成，语句之间用关键字 UNION 分隔。

3. UNION中的每个查询必须包含相同的列（ 类型相同或可以隐式转换 ）、表达式或聚集函数。

root@mysqldb 10:14:  [test]> create table test_union_1( a int, b int );
Query OK, 0 rows affected (0.01 sec)

root@mysqldb 10:14:  [test]> create table test_union_2( a int, c int );
Query OK, 0 rows affected (0.01 sec)

root@mysqldb 10:15:  [test]> insert into test_union_1 values(1, 2), (3, 4), (5, 6), (10, 20);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

root@mysqldb 10:15:  [test]> insert into test_union_2 values(10, 20), (30, 40), (50, 60);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

root@mysqldb 10:15:  [test]> select * from test_union_1;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    3 |    4 |
|    5 |    6 |
|   10 |   20 |  -- test_union_1 中的10, 20
+------+------+
4 rows in set (0.00 sec)

root@mysqldb 10:15:  [test]> select * from test_union_2;
+------+------+
| a    | c    |
+------+------+
|   10 |   20 |   -- test_union_2 中的10, 20
|   30 |   40 |
|   50 |   60 |
+------+------+
3 rows in set (0.00 sec)

root@mysqldb 10:15:  [test]> select a, b from test_union_1 
    -> union 
    -> select a, c from test_union_2;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    3 |    4 |
|    5 |    6 |
|   10 |   20 |  -- 只出现了一次 10, 20，union会去重
|   30 |   40 |
|   50 |   60 |
+------+------+
6 rows in set (0.00 sec)

root@mysqldb 10:18:  [test]> select a, b from test_union_1 
    -> union all   -- 使用 union all 可以不去重
    -> select a, c from test_union_2;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    3 |    4 |
|    5 |    6 |
|   10 |   20 |  -- test_union_1 中的10, 20
|   10 |   20 |  -- test_union_2 中的10, 20
|   30 |   40 |
|   50 |   60 |
+------+------+
7 rows in set (0.00 sec)

 

四. 触发器

  • 定义
    • 触发器的对象是表，当表上出现特定的事件时触发该程序的执行
  • 触发器的类型
    • UPDATE
      • update 操作
    • DELETE
      • delete 操作
      • replace 操作
        • 注意：drop，truncate等DDL操作不会触发 DELETE
    • INSERT
      • insert 操作
      • load data 操作
      • replace 操作
        • 注意， replace 操作会触发两次，一次是 UPDATE 类型的触发器，一次是 INSERT 类型的触发器

MySQL 5.6版本同一个类型的触发器只能有一个(针对一个表)

MySQL 5.7允许多个同一类型的触发器

注意：触发器只触发DML(Data Manipulation Language)操作，不会触发DDL(Data Definition Language)操作（create,drop等操作）

 

  •  创建触发器 
CREATE
    [DEFINER = user]
    TRIGGER [IF NOT EXISTS] trigger_name  -- 触发器名字
    trigger_time trigger_event  -- 触发时间和事件
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body

trigger_time: { BEFORE | AFTER }  -- 事件之前还是之后触发

trigger_event: { INSERT | UPDATE | DELETE }  -- 三个类型

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

root@mysqldb 10:44:  [test]> create table test_trigger_1(
    -> name varchar(20), 
    -> score int,
    -> primary key(name)
    -> );
Query OK, 0 rows affected (0.01 sec)

root@mysqldb 10:58:  [test]> delimiter // -- 将语句分隔符定义成 // （原来是';'）
root@mysqldb 10:59:  [test]> create trigger trg_upd_score_1  -- 定义触发器名字
    -> before update on test_trigger_1  -- 作用在test_trigger_1 更新(update)之前(before)
    -> for each row -- 每行
    -> begin  -- 开始定义
    -> if new.score < 0 then -- 如果新值小于0
    -> set new.score=0;  -- 则设置成0
    -> elseif new.score > 100 then  -- 如果新值大于100
    -> set new.score = 100;  -- 则设置成100
    -> end if;  -- begin对应的 结束
    -> end;//  -- 结束，使用新定义的 '//' 结尾
Query OK, 0 rows affected (0.01 sec)

root@mysqldb 11:03:  [test]> delimiter ;  -- 恢复 ';' 结束符

-- new.col : 表示更新以后的值
-- old.col : 表示更新以前的值(只读)

root@mysqldb 11:00:  [test]>  insert into test_trigger_1 values ("tom", 200); -- 插入新值
Query OK, 1 row affected (0.00 sec)

root@mysqldb 11:03:  [test]> select * from test_trigger_1;
+------+-------+
| name | score |
+------+-------+
| tom  |   200 |  -- 没改成100，因为定义的是update，而执行的是insert
+------+-------+
1 row in set (0.00 sec)

root@mysqldb 11:03:  [test]> update test_trigger_1 set score=300 where name='tom';  -- 改成300
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@mysqldb 11:03:  [test]> select * from test_trigger_1;
+------+-------+
| name | score |
+------+-------+
| tom  |   100 |  -- 通过触发器的设置，大于100的值被修改成100
+------+-------+
1 row in set (0.00 sec)