SQL自学，mysql从入门到精通 --- 第 11天，视图和索引

创建视图

-- 创建一个视图
root@mysqldb 16:29:  [d1]> CREATE VIEW cars_price_view AS
    -> SELECT name, num, colour, price, type FROM cars_price;
Query OK, 0 rows affected (0.01 sec)

-- 添加限制条件
root@mysqldb 16:32:  [d1]> CREATE VIEW cars_price_view2 AS
    -> SELECT name, num, colour, price, type FROM cars_price
    -> WHERE price > 13;
Query OK, 0 rows affected (0.01 sec)

创建索引

-- 在已有的表中创建索引
root@mysqldb 16:32:  [d1]> create INDEX index_price
    -> ON cars_price (name,num,colour);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看所有
root@mysqldb 16:35:  [d1]> SHOW INDEX FROM cars_price;
+------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| cars_price |          1 | index_price |            1 | name        | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
| cars_price |          1 | index_price |            2 | num         | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |               |
| cars_price |          1 | index_price |            3 | colour      | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

--创建表时创建索引
root@mysqldb 10:03:  [d1]> CREATE TABLE t2 (
    -> name varchar(10),
    -> class varchar(9),
    -> age tinyint,
    -> INDEX(name),
    -> INDEX(class)
    -> );
Query OK, 0 rows affected (0.01 sec)

root@mysqldb 10:04:  [d1]> DESC t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  | MUL | NULL    |       |
| class | varchar(9)  | YES  | MUL | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

root@mysqldb 10:04:  [d1]> SHOW INDEX FROM t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t2    |          1 | name     |            1 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| t2    |          1 | class    |            1 | class       | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

-- 查看视图数据
root@mysqldb 16:46:  [d1]> select * from cars_price_view;
+---------+------+--------+-------+------+
| name    | num  | colour | price | type |
+---------+------+--------+-------+------+
| CHANGAN |   23 | white  |    15 | zdfs |
| JILI    |   24 | black  |    14 | zdzx |
| ADI     |   22 | red    |    13 | sdfx |
| WLAI    |   21 | green  |    12 | acd  |
| LKE     |   20 | white  |    11 | aa   |
| BCHI    |   23 | white  |    15 | zdfs |
+---------+------+--------+-------+------+
6 rows in set (0.00 sec)

root@mysqldb 16:46:  [d1]> select * from cars_price_view2;
+---------+------+--------+-------+------+
| name    | num  | colour | price | type |
+---------+------+--------+-------+------+
| CHANGAN |   23 | white  |    15 | zdfs |
| JILI    |   24 | black  |    14 | zdzx |
| BCHI    |   23 | white  |    15 | zdfs |
+---------+------+--------+-------+------+
3 rows in set (0.00 sec)

删除视图

root@mysqldb 16:46:  [d1]> DROP VIEW cars_price_view2;
Query OK, 0 rows affected (0.01 sec)

删除索引

root@mysqldb 16:54:  [d1]> DROP INDEX index_price ON cars_price;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0