mysql索引与视图
mysql索引与视图
原始表student字段：?
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> select column_name,data_type -> from information_schema.columns -> where table_name = 'student'; +-------------+-----------+ column_name | data_type | +-------------+-----------+ stu_id | int | stu_name | varchar | stu_tel | int | stu_score | int | +-------------+-----------+ 4 rows in set (0.01 sec) |
表中原始数据：
?
1 2 3 4 5 6 7 8 9 10 | mysql> select * from student; +--------+----------+---------+-----------+ stu_id | stu_name | stu_tel | stu_score | +--------+----------+---------+-----------+ 1 | a | 151 | 60 | 2 | b | 152 | 61 | 3 | c | 153 | 62 | 4 | d | 154 | 63 | +--------+----------+---------+-----------+ 4 rows in set (0.00 sec) |
索引创建格式：
?
1 2 3 | create [ <index type> ] index <index name> [ using {btree | hash} ] on table specification ( <column in index> [,<column in index> ] ) <index type> := unique | fulltext | spatial <column in index>:=<column name> [asc | desc] |
创建一个最简单的索引：
?
1 2 3 4 | mysql> create index stu_index -> on student(stu_id); Query OK, 0 rows affected (0.36 sec) Records: 0 Duplicates: 0 Warnings: 0 |
这里创建立一个非唯一性的索引，其中默认使用asc升序排列。
如果没有指定using声明的话，mysql自动创建一个B树。所以上面的索引其实是这样子的：
?
1 2 3 4 | mysql> create index stu_index using btree -> on student(stu_id asc); Query OK, 0 rows affected (0.19 sec) Records: 0 Duplicates: 0 Warnings: 0 |
当然，btree索引可以换成哈希索引。
也可以为多个列创建唯一的索引：
?
1 2 3 4 | mysql> create unique index stu_index using hash -> on student(stu_id,stu_name); Query OK, 0 rows affected (0.19 sec) Records: 0 Duplicates: 0 Warnings: 0 |
添加索引：
?
1 2 3 4 5 | mysql> alter table student -> add unique index stu_index2 -> using hash (stu_tel); Query OK, 0 rows affected (0.36 sec) Records: 0 Duplicates: 0 Warnings: 0 |
删除索引：
?
1 2 3 | mysql> drop index stu_index on student; Query OK, 0 rows affected (0.22 sec) Records: 0 Duplicates: 0 Warnings: 0 |
创建表时定义索引：
?
1 2 3 4 5 6 7 | mysql> create table student( -> stu_id int primary key, -> stu_name varchar(5) not null, -> stu_tel int(5) unique, -> stu_score int(2), -> index stu_index(stu_id) -> ); |
只需在表的最后添加创建索引的语句即可。
视图是数据库中的虚拟表，它存储的不是自己的内容，而是经过select从其他表整合而来的。当其他表的内容改变是，视图内的内容跟着改变。在一定条件下，对视图的更新也将改变源表。
创建视图：
?
1 2 3 4 | create [ or replace ] view <view name> [<column list>] as <table expression> [with [ cascaded |local ] check option ] mysql> create view view1 as -> (select * from student); Query OK, 0 rows affected (0.16 sec) |
?
1 2 3 4 5 6 7 8 9 10 | mysql> select * from view1; +--------+----------+---------+-----------+ stu_id | stu_name | stu_tel | stu_score | +--------+----------+---------+-----------+ 1 | a | 151 | 60 | 2 | b | 152 | 61 | 3 | c | 153 | 62 | 4 | d | 154 | 63 | +--------+----------+---------+-----------+ 4 rows in set (0.00 sec) |
创建视图时，如果视图已存在，可用replace重新覆盖创建。
创建视图时还可以更改原始列名。
?
1 2 3 | mysql> create or replace view view1(id,name,tel,score) as -> (select * from student); Query OK, 0 rows affected (0.03 sec) |
?
1 2 3 4 5 6 7 8 9 10 | mysql> select * from view1; +----+------+------+-------+ id | name | tel | score | +----+------+------+-------+ 1 | a | 151 | 60 | 2 | b | 152 | 61 | 3 | c | 153 | 62 | 4 | d | 154 | 63 | +----+------+------+-------+ 4 rows in set (0.00 sec) |
当一个视图可以更新时，就能够使用[with [ cascaded |local ] check option ]选项对更新对有效检查。
删除视图：
?
1 | drop view view1; |