【数据库】5索引、视图、触发器

十一索引

分类：普通索引、唯一索引、单索引、组合索引、全文索引、空间索引

1创建

1）创建表时创建

①普通索引 index（）

create table book(
bookid int not null,
year_pub year not null,
Index(year_pub));

查看索引是否使用：explain
explain select * from book where year_pub=1990\G;

显示参数 select_type 使用select查询类型 table 表名 type 本表与其他数据表的关系 possible_keys 索引 key 实际索引 ref 关联关系另一个表里数据列名字 rows 从数据表里读取数据个数 extra 关联操作有关信息 key_len 索引长度

---

②唯一索引unique index uniqidx（） 索引列的值必须唯一

create table t1(
id int not null,
name char(30) not null,
unique index uniqidx(id));

查看：show create table t1\G;

---

③单索引一个表中可以创建多个，以上两个都是单索引

create table t2(
id int not null,
name char(50) not null,
name char(50) not null,
index SingleIdx（name（20））；

查看：show create table t2\G;

---

④组合索引

create table t3(
id int not null,
name char(30) not null,
age varchar(25),
Index MutiIdx(id,name,age(100));

查询id，name:explain select * from t3 where id=1 and name=’joe’\G;
查询name和age，possible_key为空，并没有索引

---

⑤全文索引FullText 只有MyISAM支持，只有char，warchar，text列，对整个列进行，不支持局部

create table t4(
id int not null,
name char(30) not null,
age int not null,
info varchar(255),
FullText index fulttxtidx(info)
)ENGINE=MyISAM;默认引擎为InnoDB

---

⑥空间索引 MyISAM,空间类型为非空

create table t5(
g geometry(空间类型) not null，
spatial spatidx（g）
）ENGINE=MyISAM;

查看：show create table t5\G;

---

2)已经创建的表上创建 alter table

①普通索引 index（）

alter table book add index 索引名（列（长度））；

查看：show index from 表\G;

---

②唯一索引unique index uniqidx（）

alter table book add unique index 索引名（列（长度））；

---

③单索引一个表中可以创建多个，以上两个都是单索引

alter table book add index 索引名（列（长度））；

---

④组合索引

alter table book add index 索引名（列（长度）,列（长度））；

---

⑤全文索引FullText

create table t6(
id int not null,
info varchar(255)
)ENGINE=MyISAM;
alter table t6 add FullText index fulttxtidx(info)；
show index from 表\G;

---

⑥空间索引 MyISAM,空间类型为非空

create table t7(
g geometry(空间类型) not null
）ENGINE=MyISAM;
alter table t7 add spatial index spatidx(g);

3)create index 创建索引

①普通索引

create index 索引名 on 表 （字段（字长））；

②唯一索引

create unique index 索引名 on 表 （字段（字长））；

③单索引

create index 索引名 on 表 （字段（字长））；

④组合索引

create index 索引名 on 表 （列（长度）,列（长度））；

⑤全文索引FullText

create table t6(
id int not null,
info varchar(255)
)ENGINE=MyISAM;
create FullText index on 表 (列（长度）)；

⑥空间索引 MyISAM,空间类型为非空

create table t7(
g geometry(空间类型) not null
）ENGINE=MyISAM;
create spatial index on 表 (列（长度）)；

2删除

1）alter table 表名 drop index 索引名；
2）drop index 索引名 on 表；
auto_increment 约束字段的唯一索引不能被删除

3查索引

Table:创建索引的表
Non_unique:索引非唯一，1非唯一，0唯一
key_name:索引名
seq_in_index:该字段索引中位置，单列索引为1，组合索引为字段的顺序
column_name:索引列字段
sub_part:索引长度
null:该字段能否为空
index_type:索引类型

4特殊情况

like第一个为%，索引不起作用
多列索引，用第二个字段索引时，不起作用P430
or前后两个条件都是索引才能使用索引
使用短索引varchar（5）

十二视图

1创建

①单表创建视图

create （or replace） view 视图名 as select 列1，列2 from 表；
create (or replace) view 视图名（列1，列2） as select 列1，列2 from 表；

②多表创建视图

create view 视图名（列1，列2）as select 表1.列，表2.列 from 表1，表2 where 表1.id=表2.id；

2删除

drop view if exists 视图名

3改

create or replace view 视图名 as select * from 表；
alter view 视图名 as select * from；

4查

desc 视图名；
show table status like ‘视图名’\G；很多信息为空，一个虚表
show create table view 视图名\G;
select * from information_schema.view\G;所有视图

5更新

update 视图 set 列=值；
insert into 表 values （值）；
一下视图不能更新
①视图中不包含基表中被定义的非空的列
②定义视图的select语句后的字段列表使用数学表达式、聚合函数、destinct、union、top、group by、having。

十三触发器

特殊存储过程

1创建

create tigger 名 时间 事件 on 表 for each row 程序体
①单执行语句触发器，对amount插入数据前先求和

创建表：
create table account（
acc_num int,
amount decimal(10,2));
创建触发器：
create tigger 触发器名 before insert on 表
for each row set
@sum=@sum+NEW.amount;
插入数据：
insert into account values(1,100),(2,200);

②多个执行语句触发器

delimiter//
create tigger 触发器名 before insert on 表
for each row begin
insert into 表2 set a2=NEW.a1；
insert into 表3 where a3=NEW.a1；
update 表4 set b4=b4+1 where a4=NEW.a1;
end//
delimiter;

2删除

drop tigger 触发器名；

3查看

show tiggers\G;
select * from information_schema.tigger\G;
指定查看：
select * from information_schema.tigger where tigger_name=‘触发器名’\G;

十四用户管理

user、db用户（权限）.host（主机权限）
新建用户：create user ‘用户名’@’localhost’ identified by ‘密码’；