MySQL的索引、视图

什么是索引

模式(schema)中的一个数据库对象
在数据库中用来加速对表的查询
通过使用快速路径访问方法快速定位数据,减少了磁盘的I/O
与表独立存放，但不能独立存在，必须属于某个表
由数据库自动维护，表被删除时，该表上的索引自动被删除。
索引的作用类似于书的目录，几乎没有一本书没有目录，因此几乎没有一张表没有索引。

索引的原理

就是把无序的数据变成有序的查询

把创建的索引的列的内容进行排序
对排序结果生成倒排表
在倒排表内容上拼上数据地址链
在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

什么是视图

视图通过以定制的方式显示来自一个或多个表的数据
视图是一种数据库对象，用户可以像查询普通表一样查询视图
视图内其实没有存储任何数据，它只是对表的一个查询
视图的定义保存在数据字典内，创建视图所基于的表称为“基表”

索引与视图的练习

学生表：Student (Sno, Sname, Ssex , Sage, Sdept)

学号，姓名，性别，年龄，所在系 Sno为主键

课程表：Course (Cno, Cname,)

课程号，课程名 Cno为主键

学生选课表：SC (Sno, Cno, Score)

学号，课程号，成绩 Sno,Cno为主键

1. 用SQL语句创建学生表student，定义主键，姓名不能重名，性别只能输入男或女，所在系的默认值是 “计算机”。

#student
mysql8.0 [chap03]>create table syudent(
    -> sno int primary key,
    -> sname char(30) unique,
    -> ssex char(1) check (ssex in ('男','女')),
    -> sage int,
    -> sdept char(20)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql8.0 [chap03]>alter table student modify sdept char(20) default '计算机';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

#course
mysql8.0 [chap03]>create table course(
    -> cno int,
    -> cname char(20),
    -> primary key(cno)
    -> );
Query OK, 0 rows affected (0.01 sec)
mysql8.0 [chap03]>desc course;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| cno   | int      | NO   | PRI | NULL    |       |
| cname | char(20) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

#sc
mysql8.0 [chap03]>create table sc(
    -> sno int,
    -> cno int,
    -> score int,
    -> primary key (sno,cno)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql8.0 [chap03]>desc sc;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| sno   | int  | NO   | PRI | NULL    |       |
| cno   | int  | NO   | PRI | NULL    |       |
| score | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
3 rows in set (0.00 sec)

2.修改student 表中年龄（age）字段属性，数据类型由int 改变为smallint。

mysql8.0 [chap03]>alter table student modify sage smallint;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql8.0 [chap03]>desc student;
+-------+----------+------+-----+-----------+-------+
| Field | Type     | Null | Key | Default   | Extra |
+-------+----------+------+-----+-----------+-------+
| sno   | int      | NO   | PRI | NULL      |       |
| sname | char(30) | YES  | UNI | NULL      |       |
| ssex  | char(1)  | YES  |     | NULL      |       |
| sage  | smallint | YES  |     | NULL      |       |
| sdept | char(20) | YES  |     | 计算机    |       |
+-------+----------+------+-----+-----------+-------+
5 rows in set (0.00 sec)

3.为SC表建立按学号（sno）和课程号（cno）组合的升序的主键索引，索引名为SC_INDEX 。

mysql8.0 [chap03]>create index sc_index on sc (sno asc,cno asc);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql8.0 [chap03]>show index from sc;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| sc    |          0 | PRIMARY  |            1 | sno         | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| sc    |          0 | PRIMARY  |            2 | cno         | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| sc    |          1 | sc_index |            1 | sno         | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| sc    |          1 | sc_index |            2 | cno         | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.00 sec)

4.创建一视图 stu_info,查询全体学生的姓名，性别，课程名，成绩。

mysql8.0 [chap03]>create view stu_info as select st.sname,
    -> st.ssex,co.cname,
    -> sc.score from student st,
    -> course co,
    -> sc where st.sno=sc.sno and co.cno=sc.cno;
Query OK, 0 rows affected (0.00 sec)