mysql索引--实例
学生表：Student (Sno, Sname, Ssex , Sage, Sdept)学号，姓名，性别，年龄，所在系 Sno为主键课程表：Course (Cno, Cname,)课程号，课程名 Cno为主键学生选课表：SC (Sno, Cno, Score)学号，课程号，成绩 Sno和Con为主键1.用SQL语句创建学生表student，定义主键，姓名不能重名，性别只能输入男或女，所在系的默认值是 “计算机”。2.修改student 表中年龄（age）字段属性，数据类型由int 改变为smallint。3.为SC表建立按学号（sno）和课程号（cno）组合的升序的主键索引，索引名SC_INDEX4.创建一视图 stu_info,查询全体学生的姓名，性别，课程名，成绩。
创建表
#student
>create table student(Sno int primary key,-> Sname char(20) unique, -> Ssex char(4) check(Ssex in ('男','女')), -> Sage int, -> Sdept char(20) default '计算机');#course
>cretae table course(-> Cno int primary key,-> Cname char(20));#sc
>create table SC(-> sno int(10),-> cno int(10), -> score int(10),-> primary key (sno,cno),-> foreign key(sno) references Student(sno),# 外键约束-> foreign key(cno) references Course(cno));一个表可以有多个主键；
>create table sc( Sno int, Cno int, Score int);
Query OK, 0 rows affected (0.00 sec)mysql8.0 [HH]>desc sc;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| Sno | int | YES | | NULL | |
| Cno | int | YES | | NULL | |
| Score | int | YES | | NULL | |
+-------+------+------+-----+---------+-------+
3 rows in set (0.00 sec)mysql8.0 [HH]>alter table sc add primary key (Sno,Cno);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql8.0 [HH]>desc sc;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| Sno | int | NO | PRI | NULL | |
| Cno | int | NO | PRI | NULL | |
| Score | int | YES | | NULL | |
+-------+------+------+-----+---------+-------+2.修改student 表中年龄（age）字段属性，数据类型由int 改变为smallint。
>alter table student modify Sage smallint;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql8.0 [HH]>desc student;
+-------+----------+------+-----+-----------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+-----------+-------+
| Sno | int | NO | PRI | NULL | |
| Sname | char(20) | YES | UNI | NULL | |
| Ssex | char(4) | YES | | NULL | |
| Sage | smallint | YES | | NULL | |
| Sdept | char(20) | YES | | 计算机 | |
+-------+----------+------+-----+-----------+-------+
5 rows in set (0.01 sec)3.为SC表建立按学号（sno）和课程号（cno）组合的升序的主键索引，索引名SC_INDEX
#一个表可以有多个主键：在原表没有有主键的基础上才可以添加两个主键，如果已有主键先删除
>create table sc( Sno int, Cno int, Score int);
Query OK, 0 rows affected (0.00 sec)mysql8.0 [HH]>desc sc;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| Sno | int | YES | | NULL | |
| Cno | int | YES | | NULL | |
| Score | int | YES | | NULL | |
+-------+------+------+-----+---------+-------+
3 rows in set (0.00 sec)mysql8.0 [HH]>alter table sc add primary key (Sno,Cno);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql8.0 [HH]>desc sc;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| Sno | int | NO | PRI | NULL | |
| Cno | int | NO | PRI | NULL | |
| Score | int | YES | | NULL | |
+-------+------+------+-----+---------+-------+>create index SC_INDX on sc(sno asc,cno asc);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 04.创建一视图 stu_info,查询全体学生的姓名，性别，课程名，成绩。
>CREATE VIEW stu_info as select student.Sname,student.Ssex,course.Cno,sc.score
from student,sc,course where student.Sno=scc.sno and sc.cno=course.Cno;