mysql视图索引_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  在执行动作之前还是之后

触发的动作：指的激发触发程序的语句类型

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)