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: 0

mysql8.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: 0

mysql8.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: 0

mysql8.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;