Mysql-索引视图

目录

1.视图

1.1什么是视图

1.2为什么需要视图

1.3视图的作用和优点

1.4创建视图

1.5更新视图

1.6视图使用规则

1.7修改视图

1.8删除视图

2.索引

2.1什么是索引

2.2索引特点

2.3索引分类

2.4索引优缺点 

2.5创建索引

2.6查看索引

2.7删除索引

---

1.视图

1.1什么是视图

MySQL中的视图（view）是一种虚拟表，其内容由查询定义，视图本身并不包含数据。
视图看起来和真实的表完全相同，但其中的数据来自定义视图时用到的基本表，并且在打开视图时动态生成，类似对常用的复杂多表连接查询的结果截图拍照，之后需要用到时只需看一下视图即可无需重新查询，以节省资源
视图是一种数据库对象，其内没有存储任何数据，它只是对表的一个查询

1.2为什么需要视图

例如经常要对student和score表进行连接查询，每次都要做表的连接，写同样的一串语句，同时由于成绩数据比较敏感，对外要求不可见。对这样的问题就可以通过视图来解决。

1.3视图的作用和优点

作用：
        控制安全

        保存查询数据
优点：
        简化操作：通过视图可以使用户将注意力集中在他所关心的数据上。使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件。
        提高数据的安全性：在设计数据库时可以针对不同的用户定义不同的视图，使用视图的用户只能访问他们被允许查询的结果集。
        数据独立：视图的结构定义好之后，如果增加新的关系或对原有的关系增加新的字段对用户访问的数据都不会造成影响。

1.4创建视图

create [or replace] [algorithm = {undefined | merge | temptable}]
view view_name [(column_list)]
as select_statement
[with [cascaded | local] check option]
# 说明：
1、or replace：如果要创建的视图名称已存在，则替换已有视图。
2、algorithm：可选参数，表示视图选择的算法，默认算法是 undefined
(1)undefined：未定义指定算法
(2)merge:更新视图表数据的同时会更新真实表的数据
(3)temptable:只能查询不能更新
3、view_name：新建的视图名称。
4、column_list：可选，表示视图的字段列表。若省略，则使用 select 语句中的字段列表。
5、as select_statement：创建视图的 select 语句。
6、with check option：表示更新视图时要保证该视图的 where 子句为真。
比如定义视图：create view v1 as select * from salary > 5000;
如果要更新视图，则必须保证 salary 字段的值在 5000 以上，否则报错。
(1)cascaded:必须满足所有针对该视图的条件才可以更新
(2)local:只需满足本视图的条件就可以更新

 1.创建来源一张表的视图

mysql> show databases;
mysql> use mydb9_stusys;
mysql> create view v_student as select sno,sname,ssex,year(now())-year(birth) as
age from student;
mysql> select * from v_student;
mysql> show tables;

2.创建多表连接的视图

mysql> create view v_score as select student.*, score from student join sc on
student.sno = sc.sno;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from v_score;
mysql> show tables;
mysql> show create view v_score;

3.创建视图，字段起别名

mysql> create or replace view v_avg(sex,avg_score) as select ssex ,
round(avg(score),2) from student inner join sc on student.sno=sc.sno group by
ssex;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from v_avg;
+------+-----------+
| sex  | avg_score |
+------+-----------+
| 女   |   73.52   |
| 男   |   78.51   |
+------+-----------+

1.5更新视图

以下视图不可更新
包含以下关键字的 SQL 语句：

        聚合函数、distinct、group by 、having、union 或 uinon all
        select 中包含子查询
        from 一个不可更新的试图
        where 子句的子查询引用了 from 子句中的表。 

1.创建视图,限制更新

mysql> create or replace view v_age as select sno,sname,ssex,sage from student
where sage>20 with check option; # 增加限制更新参数
Query OK, 0 rows affected (0.00 sec)

mysql> select * from v_age;
mysql> update v_age set sage = 24 where sno = 's011'; # 更新视图，24符合条件
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from student; # 查看视图的基本表，数据已经变更


# 若更新时条件不符合where字句则限制更新
mysql> update v_age set sage = 18 where sno = 's011'; # 18岁不符合where子句
ERROR 1369 (HY000): CHECK OPTION failed 'mydb9_stusys.v_age'


mysql> select * from v_student;
mysql> update v_student set age=30 where sno="s001";
ERROR 1348 (HY000): Column 'age' is not updatable

1.6视图使用规则

视图必须有唯一命名
在mysql中视图的数量没有限制
创建视图必须从管理员那里获得必要的权限
视图支持嵌套，也就是说可以利用其他视图检索出来的数据创建新的视图
在视图中可以使用order by，但是如果视图内已经使用该排序子句，则视图的order by将覆盖前面的order by。
视图不能索引，也不能关联触发器或默认值
视图可以和表同时使用

1.7修改视图

mysql> desc v_student; # 查看结构

mysql> create or replace view v_student as select sno, sname, ssex, sage from
student; # 将age直接读取

mysql> alter view v_student as select sno, sname, ssex, sage from student where
ssex="女";
Query OK, 0 rows affected (0.02 sec)

mysql> select * from v_student;

1.8删除视图

drop view [if  exists] view_name;

2.索引

2.1什么是索引

索引是一种特殊的文件，用来快速查询数据库表中的特定记录，是提高数据库性能的重要方式
通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度

2.2索引特点

索引是存放在模式（schema）中的一个数据库对象
索引在数据库中用来加速对表的查询
通过使用快速路径访问方法快速定位数据,减少了磁盘的I/O
索引与表独立存放，但不能独立存在，必须属于某个表

2.3索引分类

按装算法分类：Hash索引和B+Tree索引

 

按照功能分类 

2.4索引优缺点 

优点
        索引可以提高检索数据的速度，这也是创建索引的最主要的原因
        对于有依赖关系的子表和父表之间的联合查询时，可以提高查询速度
        使用分组和排序子句进行数据查询时，同样可以显著节省查询中分组和排序的时间。
缺点
        创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加
        每一个索引要占一定的磁盘存储空间
        增加、删除和修改数据时，要动态的维护索引，会降低数据的维护速度

2.5创建索引

# 语法1
create table 表名 (
    字段名1 数据类型 [完整性约束条件…],
    字段名2 数据类型 [完整性约束条件…],
    [unique | fulltext | spatial] index | key
    [索引名] (字段名[(长度)] [asc | desc])
    );


# 语法2
create [unique | fulltext | spatial] index 索引名
ON 表名 ( 字段名[(长度)] [asc | desc] );


# 语法3
alter table 表名 add [unique | fulltext | spatial] index
索引名 (字段名[(长度)] [asc | desc]);

示例：

# 创建表的同时创建普通索引
mysql> create table index1_tb( id int, name varchar(20), sex boolean, index(id));

# 创建表的同时创建唯一索引
mysql> create table index2_tb( id int unique, name varchar(20), unique index
index2(id asc) );

# 创建单列索引 (即普通的单列索引)
mysql> create table index3_tb( id int, subject varchar(30), index
index3(subject(10)) );

# 创建多列索引 (即普通的多列索引)
# 注意：使用多列索引时一定要特别注意，只有使用了索引中的第一个字段时才会触发索引。
mysql> create table index4_tb( id int, name varchar(20), sex char(4), index
index4(name,sex) );

2.6查看索引

# 查询索引
show create table 表名 \G
# 查询某张表中索引情况
show index from table_name;
# 使用计划查询SQL使用索引情况
explain select * from 表名 where id=1 \G
# 使用系统表查看所有索引
select * from mysql.`innodb_index_stats` a where a.`database_name` = '数据库名’;
# 使用系统表查看单张表的所有索引
select * from mysql.`innodb_index_stats` a where a.`database_name` = '数据库名' and
a.table_name like '%表名%’;

示例：

mysql> show create table index1_tb \G
mysql> show index from index3_tb;
mysql> explain select * from index1_tb where id=1 \G
# 注意possible_keys和key 这两个属性，possible_keys：MySQL在搜索数据记录时可以选用的各个索引，
key：实际选用的索引

2.7删除索引

drop index 索引名 on 表名