MySQL 视图索引触发器

视图

什么是视图？

视图是一个虚拟的表，其内容由查询定义。简单来说视图是由select结果保存组成的表。

查询薪资大于2000并且是经理的人。简单的查询，怎么查询。

创建视图

mysql> create view emp_sal_vie as (select * from emp where sal > 2000);
Query OK, 0 rows affected (0.00 sec)

查看视图

mysql> describe emp_sal_vie;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| empmo    | decimal(4,0) | NO   |     | NULL    |       |
| ename    | varchar(10)  | YES  |     | NULL    |       |
| job      | varchar(9)   | YES  |     | NULL    |       |
| mgr      | decimal(4,0) | YES  |     | NULL    |       |
| hiredate | datetime     | YES  |     | NULL    |       |
| sal      | decimal(7,2) | YES  |     | NULL    |       |
| comm     | decimal(7,2) | YES  |     | NULL    |       |
| deptno   | decimal(2,0) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
8 rows in set (0.01 sec)

   　　

查看视图表

mysql> select * from emp_sal_vie;
+-------+------------+-----------+------+---------------------+---------+--------+--------+
| empmo | ename      | job       | mgr  | hiredate            | sal     | comm   | deptno |
+-------+------------+-----------+------+---------------------+---------+--------+--------+
|  7566 | JONES      | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |   NULL |     20 |
|  7698 | BLAKE      | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |   NULL |     30 |
|  7782 | CLARK      | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |   NULL |     10 |
|  7788 | SCOTT      | ANALYST   | 7566 | 1982-12-09 00:00:00 | 3000.00 |   NULL |     20 |
|  7839 | KING       | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |   NULL |     10 |
|  7902 | FORD       | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |   NULL |     20 |
|  7777 |   zhang    | NULL      | NULL | NULL                | 2356.54 | 234.45 |   NULL |
+-------+------------+-----------+------+---------------------+---------+--------+--------+
7 rows in set (0.00 sec)

　　查看所有的视图

mysql> show table status where comment='view' \G
*************************** 1. row ***************************
           Name: emp_sal_vie
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: VIEW
*************************** 2. row ***************************
           Name: emp_sal_view
         Engine: NULL
        Version: NULL
     Row_format: NULL
           Rows: NULL
 Avg_row_length: NULL
    Data_length: NULL
Max_data_length: NULL
   Index_length: NULL
      Data_free: NULL
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: NULL
       Checksum: NULL
 Create_options: NULL
        Comment: VIEW
2 rows in set (0.00 sec)

修改视图：or replace

mysql> create or replace view emp_salary_view as (select * from emp);
Query OK, 0 rows affected (0.02 sec)

删除视图

mysql>  drop view emp_salary_view;
Query OK, 0 rows affected (0.00 sec)

触发器

什么是触发器？

触发器是一种特殊的存储过程，它在插入，删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力

语法：

create  trigger 触发器名称  触发的时机  触发的动作  on 表名 for each row 触发器状态

参数说明：

触发器名称：自己定义

触发的时机：before /after  在执行动作之前还是之后

触发的动作：指的激发触发程序的语句类型<insert ,update,delete>

each row：操作每一行我都监控着

触发器创建语法四要素：

1.监视地点(table)

2.监视事件(insert/update/delete)

3.触发时间(after/before)

4.触发事件(insert/update/delete)

 　创建触发器

mysql> create trigger deltable after delete on emp for each row delete from dept;
Query OK, 0 rows affected (0.06 sec)

　　查看触发器

mysql> show triggers\G
*************************** 1. row ***************************
             Trigger: deltable
               Event: DELETE
               Table: emp
           Statement: delete from dept
              Timing: AFTER
             Created: 2020-04-13 11:28:51.29
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

　　查看触发器相关信息

mysql> show create trigger deltable \G
*************************** 1. row ***************************
               Trigger: deltable
              sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` trigger deltable after delete on emp for each row delete from dept
  character_set_client: utf8
  collation_connection: utf8_general_ci
    Database Collation: latin1_swedish_ci
               Created: 2020-04-13 11:28:51.29
1 row in set (0.01 sec)

　　删除触发器

mysql>   drop trigger deltable;
Query OK, 0 rows affected (0.00 sec)

　　索引

什么是索引？

简单来说类似于一本书的目录。

数据量过大时，查询数据的速度就会变慢，那么如何进行加快查询速度。索引

索引的分类：

单值索引：一个索引只包含单个列，一个表可以有多个单列索引

唯一索引：索引列的值必须唯一，但允许有空值

复合索引：一个索引包含多个列

全文索引：只有在myisam引擎上才能使用，只能在char\varchar\text类型字段上使用全文索引

自动创建索引：

在表上定义了主键时，会自动创建一个对应的唯一索引

在表上定义了一个外键时，会自动创建一个普通索引

创建索引

mysql> alter table employees add index (id);   普通索引添加
Query OK, 0 rows affected (0.48 sec)
Records: 0  Duplicates: 0  Warnings: 0

　　删除索引

mysql>  alter table employees drop index id;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

　　唯一

mysql> create table t1 (id int not null,name char(30) not null, unique index uniqidx(id));
Query OK, 0 rows affected (0.47 sec)
查看表结构
mysql> show create table t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` char(30) NOT NULL,
  UNIQUE KEY `uniqidx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)