原始表student字段:
?
表中原始數據:
?
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;