视图
什么是视图?
视图是一个虚拟的表,其内容由查询定义。简单来说视图是由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)