关系型数据库 MySQL 表索引和视图详解

经过一个周末,对前面的 MySQL 基础知识以及 MySQL 的体系结构和 InnoDB 体系结构有所了解,感兴趣的可查看历史记录或者戳此了解[关系型数据库 MySQL 之 InnoDB 体系结构],今天来一起学习一下 MySQL 表索引和视图的相关知识。

一、索引

数据库索引通俗的讲就是和书本的目录一样,主要就是为了提高查询数据的效率。由于数据存储在数据库表中,所以索引是创建在数据库表对象上,由表中的一个字段或多个字段生成的键组成,这些键存储在数据结构(B-树或 hash 表)中,通过 MySQL 可以快速有效地查找与键值相关联的字段。根据索引的存储类型,可以将索引分为B型树索引(BTREE)和哈希索引(HASH)。 MySQL 5.5.21 版本中支持的索引有 6 种,分别为普通索引、唯一索引、全文索引、单列索引、多列索引和空间索引。

1、 创建和查看索引

1> 创建和查看普通索引

(1)创建表时创建普通索引

eg:create table tab_name(
L1 数据类型1,
L2 数据类型2,
L3 数据类型3,
……
L4 数据类型4,
index|key 索引名(列名 长度 ASC|DESC)
);

为了查看是否创建成功,使用以下语句查看一下;

show create table tab_name\G 

为了校验表中索引是否被使用,需执行 explain 语句:

EXPLAIN 
    select * from tab_name where deptno=1\G;

(2)在已经存在的表上创建普通索引

create index 索引名 on 表名(列名 长度 ASC|DESC);

eg:create index index_deptno on t_dept(deptno);

(3)通过 alter table 创建普通索引

alter table tab_name add index|key 索引名(列名 长度 ASC|DESC);

eg:alter table t_dept add index index_deptno(deptno);

2> 创建和查看唯一索引

(1)创建表时创建唯一索引

eg:create table tab_name(
L1 数据类型1,
L2 数据类型2,
L3 数据类型3,
……
L4 数据类型4,
unique index|key 索引名(列名 长度 ASC|DESC)
);

为了查看是否创建成功,使用以下语句查看一下;

show create table tab_name\G 

为了校验表中索引是否被使用,需执行 explain 语句:

EXPLAIN 
    select * from tab_name where deptno=10\G;

(2)在已经存在的表上创建唯一索引

create  unique index 索引名 on 表名(列名 长度 ASC|DESC);

eg:create unique index index_deptno on t_dept(deptno);

(3)通过alter table 创建唯一索引

alter table tab_name add  unique index|key 索引名(列名 长度 ASC|DESC);

eg:alter table t_dept add unique index index_deptno(deptno);

3> 创建和查看全文索引

(1)创建表时创建全文索引

eg:create table tab_name(
L1 数据类型1,
L2 数据类型2,
L3 数据类型3,
……
L4 数据类型4,
fulltext index|key 索引名(列名 长度 ASC|DESC)
);

为了查看是否创建成功,使用以下语句查看一下;

show create table tab_name\G 

为了校验表中索引是否被使用,需执行explain语句:

EXPLAIN 
    select * from tab_name where deptno=10\G;

(2)在已经存在的表上创建全文索引

create  fulltext index 索引名 on 表名(列名 长度 ASC|DESC);

eg:create fulltext index index_deptno on t_dept(deptno);

(3)通过alter table 创建唯一索引

alter table tab_name add  fulltext index|key 索引名(列名 长度 ASC|DESC);

eg:alter table t_dept add fulltext index index_deptno(deptno);

4> 创建和查看多列索引

(1)创建表时创建多列索引

eg:create table tab_name(
L1 数据类型1,
L2 数据类型2,
L3 数据类型3,
……
L4 数据类型4,
index|key 索引名(列名1 长度 ASC|DESC,
                 列名2 长度 ASC|DESC,
                 ……
                 列名n 长度 ASC|DESC)
);
eg:create table t_dept(
        deptno int,
        dnmae varchar(20),
        loc varchar(40),
        key index_dname_loc(dname,loc)
);

为了查看是否创建成功,使用以下语句查看一下;

show create table t_dept\G 

为了校验表中索引是否被使用,需执行 explain 语句:

EXPLAIN 
    select * from t_dept where deptno=10\G;

(2)在已经存在的表上创建多列索引

create index 索引名 on 表名(列名1 长度 ASC|DESC,
                                      列名n 长度 ASC|DESC );

eg:create index index_dname_loc on t_dept(dname,loc);

(3)通过 alter table 创建多列索引

alter table tab_name add  index|key 索引名(列名1 长度 ASC|DESC,
                                           列名n 长度 ASC|DESC);

eg:alter table t_dept add  index index_dname_loc(dname,loc);

2、 删除索引

之所以要删除索引,是由于有些索引会降低表的更新速度,影响数据库的性能。

删除索引语法如下:

drop index index_name on tab_name;

二、视图

视图:本身就是一种虚拟表,其内容与真实表类似,包含一系列带有名称的列和行数据。视图并不在数据库中以存储数据值的形式存在。行和列数据来定义视图的查询所引用基本表,并且在具体引用视图时动态生成。

视图的特点:

  • 视图的列可以来自于不同的表,是表的抽象在逻辑意义上建立的新关系;
  • 视图是由基本表(实表)产生的表(虚表);
  • 视图的建立和删除不影响基本表;
  • 对视图内容的更新(添加、删除、修改)直接影响基本表;
  • 当视图来自多个基本表时,不允许添加和删除数据。

1、创建视图

视图被看成是一种虚拟表,在物理上是不存在的,即数据库管理系统没有专门的位置为视图存储数据。

1>创建视图的语法为:

create view as select 列1,列2,列3 from tab_name;

eg:create view view_selectproduct 
        as 
            select id,name
                    from t_product;

2>查询视图:

select * 
        from view_selectproduct;

3>创建各种视图:

(1)封装实现查询常量语句的视图,即常量视图,语句如下:

create view  view_test1
    as
        select 3.1415926;

(2)封装使用聚合函数(sum、min、max、count 等)查询语句的视图,语句如下:

create view view_test2 
    as 
        select count(name) from t_student;

(3)封装了实现排序功能(order by )查询语句的视图,语句如下:

create view view_test3 
    as
        select name 
            from  t_student
                order by id desc;

(4)封装了实现表内连接查询语句的视图,语句如下:

create view view_test4 
    as 
        select s.name
            from  t_student as s,t_group as g
                where s.group_id=g_id and g.id=2;

(5)封装了实现外连接( left join 和 right join)查询语句的视图,语句如下:

create  view view_test5 
    as 
        select s.name
            from t_student as s left join t_group as g on s.group_id=g.id
                where g.id=2;

(6)封装了实现子查询相关查询语句的视图,语句如下:

create view view_test6 
    as 
        select s.name
            from t_student as s 
                where s.group_id in (select id from t_group);

(7)封装了实现记录联合( union 和 union all )查询语句的视图,语句如下:

create view view_test7
    as
        select id,name from t_student
        union all
        select id,name from t_group;

2、查看视图

创建完视图后,需要查看视图信息,MySQL5.5 提供了 3 种方法:

*show tables、show table status、show create view;*

(1)show tables 查看视图名;

use view;
show tables;

(2)show table status 查看视图详细信息;

语法:

show table status from db_name [like 'pattern'];

eg:查看view数据库里所有的表和视图的详细信息:

show table status from view \G

(3)show create view 语句查看视图定义信息:

show create view view_selectproduct \G

(4) describe|desc 语句查看视图设计信息:

desc view_name;  #和describe view_name效果一样;

(5)通过系统表查看视图信息:

当 MySQL 安装成功后,系统会自动创建一个名为 ==information_schema== 的系统数据库,该库中包含了视图信息的表格,可以通过查看表==views==来查看所有视图的信息。

use infomation_schema;

select * 
    from views
         where table_name='view_selectproduct'\G

3、删除视图

drop view view_name[,view_name2,view_name3];

4、修改视图

(1)Crete or replace view语句来修改视图:

对于已经建好的视图,尤其是已经存在大量的数据视图,可以先删除在创建视图:

eg:drop view view_selectproduct;
   create view view_selectproduct
        as
            select name
                    from t_product;

不过这样看起来有点麻烦。所以使用如下语句:

create  or replace view view_selectproduct
        as
            select name
                    from t_product;

查看视图,已经改过来了:

select * from view_selectproduct;

(2)alter 语句修改视图:

alter view view_name
    as select name from t_product;

5、利用视图操作基本表

(1)检索(查询)语句

select * from view_selectproduct;

(2)利用视图操作基本表数据

视图是一种虚表,对视图的操作就是对表的操作,但要注意两点就是:

  • 对视图数据进行添加、删除直接影响基本表;
  • 视图来源于多个基本表时,不允许添加或删除数据;

1、添加数据:

insert into view_product(id,name,price,order_id)
    values(11,'PEAR4',12.3,2);

2、删除数据

delete from view_product
    where name='apple1';

3、更新数据

update view_product 
    set price=3.5
        where name='pear1';

好啦,关于 MySQL 索引和视图就到这里了,小伙伴们准备好小板凳继续关注了,后期会有更多干活持续关注就可以啦。


原文发布于微信公众号 - JiekeXu之路(JiekuXu_IT)

原文发表时间:2019-03-11

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券