MySQL学习笔记三(索引和视图)

一.创建和管理索引

1.1了解索引类型

索引是内部结构，MySQL用它基于索引列的值来提供对表中记录的快速访问。

MySQL服务器不从第一个记录开始扫描全部记录，而是扫描过程从第一个匹配记录开始，在发现第一个不匹配记录时完成。

定义主键，外键和唯一约束时会在约束的列上自动创建索引。

索引类型：

主键：要求定义了该索引的列必须为表中的所有记录包含唯一值，不为null。

外键：在事务表中引用主表列的引用列上定义，以定义这些表之间的关系，并确保这些表中存储的数据之间的一致性。

唯一：在包含表中记录的唯一值的列上定义，可以包含null。

常规：在可以包含表中的重复值和null值的列上定义。

全文：在可以接受字符串的列上定义。（char，varchar,text）

全文引擎只能在可以使用MyISAM存储引擎访问的表上定义。

1.2创建索引

create table if not exists food_menu
(
food_item_id int not null,
food_item_desc varchar(45) not null,
food_item_rate float,
food_cat varchar(20),
cuisine varchar(20),
index rate_index(food_item_rate)
)engine = myisam;

创建全文索引

create table if not exists food_menu
(
food_item_id int not null,
food_item_desc varchar(45) not null,
food_item_rate float,
food_cat varchar(20),
cuisine varchar(20),
index rate_index (food_item_rate),
fulltext food_cat_index (food_cat)
)engine = myisam;

 在已经存在的表上创建索引

alter table food_menu
add index food_item_id_index (food_item_id);


alter table food_menu
add fulltext index food_item_desc_index (food_item_desc);

create index创建索引

创建唯一索引
create unioue index <index_name>
on <table_name> (<column_name>);

创建常规索引
create index <index_name>
on <table_name> (<column_name>);

创建全文索引
create fulltext index <index_name>
on <table_name> (<column_name>);

1.3查看索引

show indexes from table_name;

show indexes from table_name [from database_name];

1.4删除索引

alter table <table_name> drop index <index_name>;

drop index <index_name> on <table_name>;

二.创建和管理视图

视图是虚拟表，提供对来自一个或者多个表或者视图或者记录的子集进行访问。除了限制访问之外，视图还可以用于创建和保存基于多个表的查询。与直接对表查询比起来对视图发起查询运行缓慢，在查询性能和磁盘空间之间进行权衡。可以选择创建视图

2.1创建视图

create [or replace]
view view_name [column_name] as <select_statement>

or replace:是可选子句，用于把现有的视图的定义替换为当前创建的视图。
select_statement:指定定义视图的select语句。


b_view视图中获取表agent,agent_r,guests,booking的数据
create view b_view as
select 
a.agent_id,ahent_name,ar.agent_r_id,b.booking_id,guest_name 
from agent as a,agent_r as ar,guests as g,bookings as b 
where a.agent_id = ar.agent_id and ar.agent_r_id = b.agent_r_id 
and g.guest_id = b.guest_id;


create view view_name 
as
select
......
;


create or replace view view_name(old_view_name1,old_view_name2)
as 
select
......
;
删除view_name视图的定义，重新定义。old_view_name1和old_view_name2是分配给view_name视图的列的显式名称

2.2查询视图

select * from view_name;


查询有where子句的视图
where子句可以包含在视图定义中以精化视图要显示的记录。
查询roomrate小于600的记录
create view room_view as 
select * from room where room_rate < 600;

查询room_view视图
select * from room_view;

查询具有order by子句的视图
create view guest_view as 
select * from guest order by guest_name desc;

2.3视图限制

限制：

不能在临时表上创建视图；

不能创建临时视图；

不能在视图上创建触发器；

不能在视图上创建索引；

视图的表或者视图必须已经存在；

视图定义的select语句不能在from子句中包含子查询。

2.4可更新视图

只有在视图上执行的插入、更新或者删除操作可以修改底层表时才可以认为视图可更新。

不可以更新的情况：

列列表中存在聚合函数；

select语句中包含distinct、drop by、having子句；

select语句中存在子查询；

from子句中存在不可更新视图。

在视图中执行插入操作

只能对包含底层表中所有not null列且在列列表中不包含任何计算的视图执行插入操作。

插入

可更新
create view hotel_view as 
select hotel_id,hotel_name,hotel_rating from hotels;

insert into hotel_view 
values ('12312','Tom Hotel',5);

不可更新
create view hotel_view as 
select hotel_name,hotel_rating from hotels;

insert into hotel_view 
values ('Tom Hotel',10);
因为没有包含不为null的hotel_id

 在视图上执行更新操作

以下情况不能在视图上执行更新操作：

更新操作会修改派生列的值；

更新操作会导致修改多个表。

在视图上执行更新操作只可以修改一个表

update hotel_view set hotel_name = 'Tim Hotel' where hotel_id = 12423;

在视图上执行删除操作

在视图上删除记录会导致从底层表中删除记录；视图中有大于2个底层表时，MySQL会限制视图执行任何删除操作。

delete from hotel_view where hotel_id = 12312;
删除hotel_id = 12312的记录。

 

2.5管理视图

更改视图

create view hotel_view 
as select hotel_id from hotels;

alter view hotel_view as 
select hotel_id,hotel_name from hotels;

删除视图

drop view [if exists] view_name[,view_name];

drop view hotel_view;

目录

一.创建和管理索引

1.1了解索引类型

1.2创建索引

1.3查看索引

1.4删除索引

二.创建和管理视图

2.1创建视图

2.2查询视图

2.3视图限制

2.4可更新视图

2.5管理视图

未完！待续......

---

未完！待续......