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_INDEX 

4.创建一视图 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: 0

4.创建一视图 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;