MySQL基础知识（view index语句）

view的特性：

1、创建视图的语句是按具体的列定义好的，

create view v_1 as select sid,dept_name from students a inner join dept b on a.dept_id=b.id;

mysql> show create table v_1 \G
*************************** 1. row ***************************
         View: v_1
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_1` AS 
         select `a`.`sid` AS `sid`,`b`.`dept_name` AS `dept_name` 
         from (`students` `a` join `dept` `b` on((`a`.`dept_id` = `b`.`id`)))
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> SELECT * FROM v_1;
+-----+-----------+
| sid | dept_name |
+-----+-----------+
|   1 | database  |
|   3 | database  |
|   2 | storage   |
|   4 | storage   |
+-----+-----------+
4 rows in set (0.00 sec)

2、后添加的字段不会影响现有的视图

mysql> ALTER TABLE students ADD COLUMN master varchar(20); 
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc students;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| sid     | int         | NO   | PRI | NULL    | auto_increment |
| sname   | varchar(10) | YES  |     | NULL    |                |
| gender  | varchar(12) | YES  |     | NULL    |                |
| dept_id | int         | NO   | MUL | NULL    |                |
| master  | varchar(20) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> select *from v_1;
+-----+-----------+
| sid | dept_name |
+-----+-----------+
|   1 | database  |
|   3 | database  |
|   2 | storage   |
|   4 | storage   |
+-----+-----------+
4 rows in set (0.00 sec)

3、后删除的字段会影响视图

mysql> alter table students drop column sid;
Query OK, 4 rows affected (0.29 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> desc students;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| sname   | varchar(10) | YES  |     | NULL    |       |
| gender  | varchar(12) | YES  |     | NULL    |       |
| dept_id | int         | NO   | MUL | NULL    |       |
| master  | varchar(20) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> select *  from v_1;
ERROR 1356 (HY000): View 'mysql.v_1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysql> 

4、视图是固定SQL的结果集

mysql> show create table v_1 \G
*************************** 1. row ***************************
        View: v_1
        Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_1` 
        AS select `a`.`sid` AS `sid`,`b`.`dept_name` AS `dept_name` 
        from (`students` `a` join `dept` `b` on((`a`.`dept_id` = `b`.`id`)))
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

5、可以通过视图往单表中插入数据，多表联合视图就不一定

mysql> select * from v_3;
+----------+---------+------+
| sname    | dept_id | sid  |
+----------+---------+------+
| narisu   |       1 | NULL |
| gutianle |       2 | NULL |
| bbb      |       1 | NULL |
| ccc      |       2 | NULL |
| night    |       1 | NULL |
+----------+---------+------+
5 rows in set (0.00 sec)

mysql> insert into v_3 values ('mysql',1,10002);
Query OK, 1 row affected (0.02 sec)

mysql> 
mysql> 
mysql> select * from v_3;
+----------+---------+-------+
| sname    | dept_id | sid   |
+----------+---------+-------+
| narisu   |       1 |  NULL |
| gutianle |       2 |  NULL |
| bbb      |       1 |  NULL |
| ccc      |       2 |  NULL |
| night    |       1 |  NULL |
| mysql    |       1 | 10002 |
+----------+---------+-------+
6 rows in set (0.00 sec)

mysql> select *from students_bak;
+----------+--------+---------+--------+-------+
| sname    | gender | dept_id | master | sid   |
+----------+--------+---------+--------+-------+
| narisu   | man    |       1 | NULL   |  NULL |
| gutianle | man    |       2 | NULL   |  NULL |
| bbb      | feman  |       1 | NULL   |  NULL |
| ccc      | feman  |       2 | NULL   |  NULL |
| night    | night  |       1 | NULL   |  NULL |
| mysql    | NULL   |       1 | NULL   | 10002 |
+----------+--------+---------+--------+-------+
6 rows in set (0.00 sec)

mysql> select *from students;
+----------+--------+---------+--------+------+
| sname    | gender | dept_id | master | sid  |
+----------+--------+---------+--------+------+
| narisu   | man    |       1 | NULL   | NULL |
| gutianle | man    |       2 | NULL   | NULL |
| bbb      | feman  |       1 | NULL   | NULL |
| ccc      | feman  |       2 | NULL   | NULL |
| night    | night  |       1 | NULL   | NULL |
+----------+--------+---------+--------+------+
5 rows in set (0.00 sec)

mysql> insert into v_1 values ('night2','night2',3,null,null);
ERROR 1394 (HY000): Can not insert into join view 'mysql.v_1' without fields list
mysql> 

6、视图里面的数据库可被排序

mysql> select * from v_3 order by 2;
+----------+---------+-------+
| sname    | dept_id | sid   |
+----------+---------+-------+
| narisu   |       1 |  NULL |
| bbb      |       1 |  NULL |
| night    |       1 |  NULL |
| mysql    |       1 | 10002 |
| gutianle |       2 |  NULL |
| ccc      |       2 |  NULL |
+----------+---------+-------+
6 rows in set (0.00 sec)

mysql>

3种索引创建方式：

普通索引

create index idx_st_sname  on students(sname);
drop index idx_st on students;

复合索引

create index idx_st_union on students(sname,sex);
drop index idx_st_union;

创建唯一索引

create unique index idx_st_sid on students(sid);
drop index idx_st_sid;

表字段增删

alter table students drop column sname2;
alter table students add column sname3;