mysql-索引+视图(九)

索引

索引的概括

索引主要是存储引擎层面用于快速查询找到记录的一种数据结构，

索引对性能的影响非常重要

特别是表中数据量大的时候

正确的索引会极大的提成查询效率

在创建表的时候创建索引

1. 普通索引

特点：没有任何限制；只是加快对数据的访问速度。

create table people(
id int(11),
name varchar(30),
mobile varchar(11),
index n_index(name)
);

2. 组合索引

特点：在表的多个字段组合上创建的索引

create table people2(
id int(11),
name varchar(30),
age tinyint(4),
status tinyint(4),
index n_index(name,age,status)
);

在使用多字段组合索引时，要遵循“最左前缀”的规则，就是在查询时，利用索引中最左边的一个或几个字段匹配数据。
例如，在people1表中，索引是由name，age和status 3个字段组成的，可以使用的字段组合有（name,age,status）、（name,age）或name。

3. 唯一索引

特点：索引列的值必须唯一，但允许有空值；避免数据重复
主键索引：特殊的唯一索引；不允许为null；且一张表只能有一个。

create table people3(
id int(11),
name varchar(30),
mobile varchar(11),
unique index m_index(id)
);

4. 全文索引

特点：可以在char，varchar和text类型的列上创建；
允许列值重复和为NULL；只有MyISAM存储引擎支持使用。

create table people4(
id int(11),
name varchar(30),
intro text,
fulltext index i_fulltext(intro)
)engine=myisam default charset=gbk;

5. 空间索引

特点：对空间数据类型的字段创建的索引;
创建空间索引的字段，必须将其声明为not null;
该版本只有MyISAM存储引擎支持使用。
(MySQL中的空间数据类型主要有geometry、point、linestring和polygon)

create table people5(
id int(11),
name varchar(30),
geom geometry not null,
spatial index g_apatial(geom)
)engine=myisam default charset=gbk;

在已有的表上创建索引

1. 使用alter table 语句创建索引

alter table 表名 add [unique|fulltext|spatial] index 索引名(列名) [asc|desc];

alter table people add index m_index(mobile);

2. 使用create index 语句创建索引

create [unique|fulltext|spatial] index  索引名 on 表名 (列名) [asc|desc]; 

create unique index m_unique on people2(name);

查看索引

1. 使用show index 语句查看索引

show index from 表名;

show index from people;

2. 用关键词explain查看索引的使用情况

explain select * from 表名 where 索引相关条件;

explain select * from people2 where name='tom' and age=21;
explain select * from people2 where age=21 and status=1;

删除索引

1. 使用alter table语句删除索引

alter table 表名 drop index 索引名;
alter table people drop index m_index;

删除主键索引：

alter table 表名 drop primary key;

alter table people modify id int;————主键有自增，除自增；
alter table people drop primary key;  ?

2. 使用drop index语句删除索引

drop index 索引名 on 表名;
drop index n_index on people;

视图

视图概括

视图是从数据库中的一张或多张表中导出的表。

• 基表：创建视图是所引用的表
• 特点：它只是读取基表的数据
• 在对视图中的数据进行修改时，相应的基本表中的数据也会发生变化；同时，若基本表的数据发生变化，则这种变化也会反映到视图
• 视图的优势：简单、安全、数据独立

创建视图

create [or replace] view 视图名称[(各字段名)] as select语句;

注意

1. 定义中引用的任何表或视图都必须存在。
2. 创建视图不能引用临时表。
3. select语句中最大列名长度为64个字符。

在单表上创建视图

格式：
create view 视图名 as select 列1, 列2, ...,列n from 表名;

例子：
create view v_table as select id, name from good;
create view v_table2(good_id,名字) as select id, name from good;

在多表上创建视图

格式：
create view 视图名(字段列表名) as select *|列名 from 表1 inner join 表2 on 关联条件;

例子：
create view v_table3(订单编号,名称) as select orders.o_id,good.name from good 
inner join orders on good.id=orders.goods_id;

查看视图

查看视图基本信息

格式：
desc 视图名;     (describe)

例子：
desc v_table;

注意：视图中不存在主键，并且也不能在视图上创建索引；——实际上它只是个结果集.

查看视图定义语句

格式：
show create view 视图名;

例子：
show create view v_table;

结果：显示视图名称，及创建视图的语句等信息。

通过views表查看视图详细信息

格式：
select * from information_schema.views;

所有视图的详细信息都存储在系统数据库information_schema.views下的views表中;

修改视图

使用create or replace view 语句修改视图

格式：
create or replace view 视图名(字段列表名) as select 列名 from 表名;

例子：
create or replace view v_table as select id, name, price from good;

使用alter 语句修改视图

格式：
alter view 视图名(字段列表名) as select 列名 from 表名;

例子：
alter view v_table2(good_id,good_name,good_price) as select id, name, price from good;

删除视图

使用drop view 语句删除视图

格式：
drop view [if exists] 视图1,.......,视图n;

例子：
drop view if exists v_table;

查看：
show tables;

操作视图中数据

修改视图中数据

1. 视图中数据被修改，基表中的数据会同时被修改。
2. 基表中数据被修改，视图中的数据也会被修改。

格式：
update 视图名 set 列1=值1,列2=值2,...,列n=值n where 条件;

例子：
update v_table2 set good_name='哈姆雷特' where good_id=6;

删除视图中数据

格式：
delete from 视图名 where 删除条件;

例子：
delete from v_table2 where good_id=2;

查看视图中数据

1. 查看视图数据

select * from 视图名;

2. 查看基表数据

slect 列名 from 表名;

向视图中插入数据

insert into 视图名 values(值1,值2,...,值n);

例子：
insert into v_table2 values(7,'冰糖',134);

注意：插入时，基表中的其他列要允许为null；