mysql索引與視圖

原始表student字段：

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)

表中原始數據：

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)

索引創建格式：

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]

創建一個最簡單的索引：

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樹。所以上面的索引其實是這樣子的：

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索引可以換成哈希索引。

也可以為多個列創建唯一的索引：

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

添加索引：

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

刪除索引：

mysql> drop index stu_index on student;
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

創建表時定義索引：

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從其他表整合而來的。當其他表的內容改變是，視圖內的內容跟著改變。在一定條件下，對視圖的更新也將改變源表。

創建視圖：

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)

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重新覆蓋創建。

創建視圖時還可以更改原始列名。

mysql> create or replace view view1(id,name,tel,score) as
    -> (select * from student);
Query OK, 0 rows affected (0.03 sec)

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 ]選項對更新對有效檢查。

刪除視圖：

drop view view1;