目录
视图
1.视图
❓如果需要在原表中隐藏部分字段时，怎么办？ 视图 📖视图:
是一个没有存储任何数据的表，可以对其CRUD
视图所有的数据来源都来自于底层表
是一个数据对象
删除视图时，原数据不会变化
操作视图数据时，源数据会发生变化，因为视图数据来自于原表
📘视图设计原则:
视图必须有唯一命名
在mysql中视图的数量没有限制
创建视图必须从管理员那里获得必要的权限
视图支持嵌套，也就是说可以利用其他视图检索出来的数据创建新的视图
在视图中可以使用OREDR BY，但是如果视图内已经使用该排序子句，则视图的ORDER BY将覆盖前面的ORDER BY。
视图不能索引，也不能关联触发器或默认值
视图可以和表同时使用
2.操作
1.创建视图
mysql> create view employee_v_1
-> as
-> select id,number,name,job from employee;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-------------------+
| Tables_in_dml_lls |
+-------------------+
| department |
| employee |
| employee_v_1 |
| user |
+-------------------+
4 rows in set (0.00 sec)
2.视图没有索引
mysql> desc employee_v_1;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | bigint(20) | NO | | 0 | |
| number | varchar(20) | NO | | NULL | |
| name | varchar(50) | YES | | NULL | |
| job | varchar(50) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
3.查询视图数据
mysql> select * from employee_v_1;
+----+-------------+-----------+-----------------+
| id | number | name | job |
+----+-------------+-----------+-----------------+
| 1 | E2018010001 | 吴所为 | 总经理 |
| 2 | E2018070003 | 韩金龙 | 总经理 |
| 3 | E2018060002 | 王黎明 | 总经理 |
| 4 | E2018020002 | 龚爱国 | 总经理 |
| 5 | E2019050001 | 马金花 | 财务总监 |
| 6 | E2019050018 | 李昌贵 | 财务专员 |
| 7 | E2019100011 | 王建国 | 网络管理员 |
| 8 | E2019110004 | 黎锦熙 | 网络管理员 |
| 9 | E2020020023 | 繁茂森 | 销售专员 |
| 10 | E2019060005 | 张善民 | 销售经理 |
| 11 | E2019060009 | 廖云龙 | 技术总监 |
| 12 | E2019120021 | 刘盛会 | 研发工程师 |
| 13 | E2019020001 | 马明全 | 高级工程师 |
| 14 | E2019120015 | 李意 | 行政专员 |
| 15 | E2019020017 | 刘六一 | 财务总监 |
| 16 | E2020020012 | 陈超 | 研发工程师 |
+----+-------------+-----------+-----------------+
16 rows in set (0.00 sec)
4.更改视图数据
mysql> insert into employee_v_1 values(null,"202222222","张三","程序员");
Query OK, 1 row affected (0.00 sec)
5.原表数据也会发生变化
mysql> select * from employee;
+----+-------------+-----------+-----------------+-------------+------------+---------+---------+-------+---------------+
| id | number | name | job | leader_NO | hire_date |salary | bonus | level | department_NO |
+----+-------------+-----------+-----------------+-------------+------------+---------+---------+-------+---------------+
| 1 | E2018010001 | 吴所为 | 总经理 | NULL | 2018-01-01 |3000.00 | 4000.00 | 9 | D2019060001 |
| 2 | E2018070003 | 韩金龙 | 总经理 | NULL | 2018-07-01 |2800.00 | 4000.00 | 8 | D2019090001 |
| 3 | E2018060002 | 王黎明 | 总经理 | NULL | 2018-06-01 |2800.00 | 4000.00 | 8 | D2019060002 |
| 4 | E2018020002 | 龚爱国 | 总经理 | NULL | 2018-02-01 |2800.00 | 4000.00 | 8 | D2020010001 |
| 5 | E2019050001 | 马金花 | 财务总监 | E2018010001 | 2019-01-01 |3800.00 | 500.00 | 6 | D2019060011 |
| 6 | E2019050018 | 李昌贵 | 财务专员 | E2019050001 | 2019-04-21 |2800.00 | 800.00 | 4 | D2019060011 |
| 7 | E2019100011 | 王建国 | 网络管理员 | E2018010001 | 2019-10-01 |3200.00 | NULL | 5 | D2019060013 |
| 8 | E2019110004 | 黎锦熙 | 网络管理员 | E2019100011 | 2019-11-01 |3200.00 | NULL | 5 | D2019060013 |
| 9 | E2020020023 | 繁茂森 | 销售专员 | E2019060005 | 2020-02-01 |2800.00 | 0.00 | 4 | D2019060014 |
| 10 | E2019060005 | 张善民 | 销售经理 | E2018010001 | 2019-06-01 |2800.00 | 500.00 | 6 | D2019060014 |
| 11 | E2019060009 | 廖云龙 | 技术总监 | E2018010001 | 2019-06-01 |4800.00 | 2000.00 | 7 | D2019060012 |
| 12 | E2019120021 | 刘盛会 | 研发工程师 | E2019060009 | 2019-12-11 |4800.00 | 500.00 | 5 | D2019060012 |
| 13 | E2019020001 | 马明全 | 高级工程师 | E2019060009 | 2019-02-01 |4800.00 | 1000.00 | 6 | D2019060012 |
| 14 | E2019120015 | 李意 | 行政专员 | E2018070003 | 2019-12-20 |2800.00 | 500.00 | 4 | D2019090001 |
| 15 | E2019020017 | 刘六一 | 财务总监 | E2018070003 | 2019-02-16 |3800.00 | 1000.00 | 6 | D2019090011 |
| 16 | E2020020012 | 陈超 | 研发工程师 | E2019060009 | 2020-02-18 |4200.00 | 500.00 | 5 | D2019060012 |
| 17 | 202222222 | 张三 | 程序员 | NULL | NULL |NULL | NULL | 0 | NULL |
+----+-------------+-----------+-----------------+-------------+------------+---------+---------+-------+---------------+
17 rows in set (0.00 sec)
6.删除张三
mysql> delete from employee_v_1 where name = "张三";
Query OK, 1 row affected (0.00 sec)
7.修改视图--起别名
mysql> alter view employee_v_1 (id,员工号,姓名,工作) as select id,number,name,job from employee;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-------------------+
| Tables_in_dml_lls |
+-------------------+
| department |
| employee |
| employee_v_1 |
| user |
+-------------------+
4 rows in set (0.00 sec)
mysql> select * from employee_v_1;
+----+-------------+-----------+-----------------+
| id | 员工号 | 姓名 | 工作 |
+----+-------------+-----------+-----------------+
| 1 | E2018010001 | 吴所为 | 总经理 |
| 2 | E2018070003 | 韩金龙 | 总经理 |
| 3 | E2018060002 | 王黎明 | 总经理 |
| 4 | E2018020002 | 龚爱国 | 总经理 |
| 5 | E2019050001 | 马金花 | 财务总监 |
| 6 | E2019050018 | 李昌贵 | 财务专员 |
| 7 | E2019100011 | 王建国 | 网络管理员 |
| 8 | E2019110004 | 黎锦熙 | 网络管理员 |
| 9 | E2020020023 | 繁茂森 | 销售专员 |
| 10 | E2019060005 | 张善民 | 销售经理 |
| 11 | E2019060009 | 廖云龙 | 技术总监 |
| 12 | E2019120021 | 刘盛会 | 研发工程师 |
| 13 | E2019020001 | 马明全 | 高级工程师 |
| 14 | E2019120015 | 李意 | 行政专员 |
| 15 | E2019020017 | 刘六一 | 财务总监 |
| 16 | E2020020012 | 陈超 | 研发工程师 |
+----+-------------+-----------+-----------------+
16 rows in set (0.00 sec)
8.替换/修改视图
mysql> create or replace view employee_v_1 (id,员工号,姓名,职位,入职日期) as select id,number,name,job,hire_date from employee where name = "陈超";
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-------------------+
| Tables_in_dml_lls |
+-------------------+
| department |
| employee |
| employee_v_1 |
| user |
+-------------------+
4 rows in set (0.00 sec)
mysql> select * from employee_v_1;
+----+-------------+--------+-----------------+--------------+
| id | 员工号 | 姓名 | 职位 | 入职日期 |
+----+-------------+--------+-----------------+--------------+
| 16 | E2020020012 | 陈超 | 研发工程师 | 2020-02-18 |
+----+-------------+--------+-----------------+--------------+
1 row in set (0.00 sec)
9.删除视图
mysql> drop view employee_v_1
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-------------------+
| Tables_in_dml_lls |
+-------------------+
| department |
| employee |
| user |
+-------------------+
3 rows in set (0.00 sec)
10.删除视图，原表不发生变化
mysql> select * from employee;
+----+-------------+-----------+-----------------+-------------+------------+--
-------+---------+-------+---------------+
| id | number | name | job | leader_NO | hire_date |
salary | bonus | level | department_NO |
+----+-------------+-----------+-----------------+-------------+------------+--
-------+---------+-------+---------------+
| 1 | E2018010001 | 吴所为 | 总经理 | NULL | 2018-01-01 |3000.00 | 4000.00 | 9 | D2019060001 |
| 2 | E2018070003 | 韩金龙 | 总经理 | NULL | 2018-07-01 |2800.00 | 4000.00 | 8 | D2019090001 |
| 3 | E2018060002 | 王黎明 | 总经理 | NULL | 2018-06-01 |2800.00 | 4000.00 | 8 | D2019060002 |
| 4 | E2018020002 | 龚爱国 | 总经理 | NULL | 2018-02-01 |2800.00 | 4000.00 | 8 | D2020010001 |
| 5 | E2019050001 | 马金花 | 财务总监 | E2018010001 | 2019-01-01 |3800.00 | 500.00 | 6 | D2019060011 |
| 6 | E2019050018 | 李昌贵 | 财务专员 | E2019050001 | 2019-04-21 |2800.00 | 800.00 | 4 | D2019060011 |
| 7 | E2019100011 | 王建国 | 网络管理员 | E2018010001 | 2019-10-01 |3200.00 | NULL | 5 | D2019060013 |
| 8 | E2019110004 | 黎锦熙 | 网络管理员 | E2019100011 | 2019-11-01 |3200.00 | NULL | 5 | D2019060013 |
| 9 | E2020020023 | 繁茂森 | 销售专员 | E2019060005 | 2020-02-01 |2800.00 | 0.00 | 4 | D2019060014 |
| 10 | E2019060005 | 张善民 | 销售经理 | E2018010001 | 2019-06-01 |2800.00 | 500.00 | 6 | D2019060014 |
| 11 | E2019060009 | 廖云龙 | 技术总监 | E2018010001 | 2019-06-01 |4800.00 | 2000.00 | 7 | D2019060012 |
| 12 | E2019120021 | 刘盛会 | 研发工程师 | E2019060009 | 2019-12-11 |4800.00 | 500.00 | 5 | D2019060012 |
| 13 | E2019020001 | 马明全 | 高级工程师 | E2019060009 | 2019-02-01 |4800.00 | 1000.00 | 6 | D2019060012 |
| 14 | E2019120015 | 李意 | 行政专员 | E2018070003 | 2019-12-20 |2800.00 | 500.00 | 4 | D2019090001 |
| 15 | E2019020017 | 刘六一 | 财务总监 | E2018070003 | 2019-02-16 |3800.00 | 1000.00 | 6 | D2019090011 |
| 16 | E2020020012 | 陈超 | 研发工程师 | E2019060009 | 2020-02-18 |4200.00 | 500.00 | 5 | D2019060012 |
+----+-------------+-----------+-----------------+-------------+------------+--
-------+---------+-------+---------------+
16 rows in set (0.00 sec)
11.索引
1.定义
什么是索引？
索引是对数据库一列或者多列的值进行排序的一种结构。
索引的建立会大大提高mysql的检索速度。
如果想高效的使用mysql,而且数据量大时，需要花费事件去设计索引，建立优秀的索引规则，或优化sql
语句
2.优缺点:
优点:
索引减少了服务器需要扫描的数据量，加快了检索的速度，这就是使用索引的主要原因
通过创建唯一索引，可以保证数据表中每一行数据的唯一性
索引可以帮助服务器避免排序和创建临时表
缺点:
创建索引和维护索引，需要成本，随着数据量的增加成本需要增加。
它会占用物理空间，除了数据表占用的空间外，每一个索引都需要占用一定的物理空间，特别是聚簇索引，更会占用大量空间
对数据表数据操作(删除、修改、增加)时，索引需要动态维护，降低了整个维护速度
如果具备大量重复数据时，没有建立索引的必要了。
对于非常小的表，没有必要
！！mysql 5.5及之后的版本，默认使用innodb引擎，因此默认会添加主键索引，底层为B+Tree
3.分类
1.根据底层: 聚簇索引(主键)和非聚簇索引(其他索引)----后续面试时，一定要冲刺这部分
聚簇索引按照数据存放的物理位置为顺序---多行检索快
非聚簇索引对单行的检索特别快
2.根据作用点: 主键索引、唯一索引、单列索引、多列索引、全文索引等等
4.索引的设计原则
1.选择惟一性索引
2.为经常需要排序、分组和联合操作的字段建立索引
3.为常作为查询条件的字段建立索引
4.限制索引的数目
5.尽量使用数据量少的索引
6.尽量使用前缀来索引
7.删除不再使用或者很少使用的索引
5.索引的使用
1. 查询索引
show index from 表名
show index from 表名\G --- 竖着显示详细信息
2. 创建索引
create index 索引名 on 表名(字段名) -- 1.创建普通索引 -单列索引
create unique index 索引名 on 表名(字段名) -- 2.创建唯一索引
create index 索引名 on 表名(字段1,字段n) -- 3.多列索引
create fulltext index 索引名 on 表名(字段名(只能为char\varchar\text)) -- 4.全文索引
Create table index6( -- 5.空间索引
Id int,
Space geometry not null,
Spatial index index6_sp(space)
)engine=myisam;
建空间索引时，表的存储引擎必须是myisam类型，而且索引字段必须有非空约束。空间数据类型包括geometry,point,linestring和polygon类型等。平时很少用到。
3. 删除索引
drop index 索引名 on 表名
4. 执行计划--看索引是否命中
explain 查询sql
EXPLAIN分析结果的含义:
table：这是表的名字。
type：连接操作的类型，ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）
possible_keys：可能可以利用的索引的名字
Key：它显示了MySQL实际使用的索引的名字。如果它为空（或NULL），则MySQL不使用索引。
key_len：索引中被使用部分的长度，以字节计。
ref：它显示的是列的名字（或单词“const”），MySQL将根据这些列来选择行
rows：MySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然，这里最理想的数字就是1
Extra：这里可能出现许多不同的选项，其中大多数将对查询产生负面影响
1.查看索引
mysql> show index from employee;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employee | 0 | PRIMARY | 1 | id | A |16 | NULL | NULL | | BTREE | | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
2.查看主键索引的执行计划
mysql> explain select * from employee where id = 10;
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key |key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | employee | NULL | const | PRIMARY | PRIMARY | 8| const | 1 | 100.00 | NULL |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
3.竖着展示详细信息
mysql> show index from employee\G;
*************************** 1. row ***************************
Table: employee
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 16
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
4.创建索引-为name字段
mysql> create index name_index on employee(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from employee;
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment
|+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employee | 0 | PRIMARY | 1 | id | A |16 | NULL | NULL | | BTREE | | |
| employee | 1 | name_index | 1 | name | A |16 | NULL | NULL | YES | BTREE | | |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
5.查询命中情况
mysql> explain select * from employee where name = "陈超";
+----+-------------+----------+------------+------+---------------+------------
+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key |key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | employee | NULL | ref | name_index | name_index |203 | const | 1 | 100.00 | NULL |
+----+-------------+----------+------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
6.对比非索引的字段查询情况
mysql> explain select * from employee where job = "研发工程师";
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key |key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+------
---+------+------+----------+-------------+
| 1 | SIMPLE | employee | NULL | ALL | NULL | NULL | NULL| NULL | 17 | 10.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+------
---+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> show index from employee\G:
*************************** 1. row ***************************
Table: employee
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 16
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: employee
Non_unique: 1
Key_name: name_index
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 16
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)
7.删除索引
mysql> drop index name_index on employee;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0