基于MySQL的数据库操作索引

       首先构建学生表student，定义主键，姓名不能重名，性别只能输入男或女，所在系的默认值是“计算机”，

mysql> desc Student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| Sno   | int         | NO   | PRI | NULL    | auto_increment |
| Sname | varchar(30) | NO   | UNI | NULL    |                |
| Ssex  | varchar(2)  | NO   |     | NULL    |                |
| sage  | smallint    | YES  |     | NULL    |                |
| Sdept | varchar(10) | NO   |     | 计算机  |                |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> desc Course;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Cno   | int         | NO   | PRI | NULL    |       |
| Cname | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc SC;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Sno   | int         | NO   |     | NULL    |       |
| Cno   | varchar(10) | NO   |     | NULL    |       |
| Score | int         | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

完成准备工作之后，可以进行如下操作： 

• 修改student 表中年龄(sage)字段属性，数据类型由int 改变为smallint

  mysql> alter table Student modify column sage smallint;
  Query OK, 0 rows affected (0.07 sec)
  Records: 0  Duplicates: 0  Warnings: 0

• 为Course表中Cno 课程号字段设置索引,并查看索引

  mysql> create index index_Cno  on Course(Cno);
  Query OK, 0 rows affected (0.02 sec)
  Records: 0  Duplicates: 0  Warnings: 0

• 为SC表建立按学号(sno)和课程号(cno)组合的升序的主键索引，索引名为SC_INDEX4.创建一视图stu info,查询全体学生的姓名，性别，课程名，成绩

  mysql> alter table sc drop primary key;
  Query OK, 0 rows affected (0.06 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  
  mysql> alter table sc add primary key SC_INDEX(sno,cno);
  Query OK, 0 rows affected (0.05 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  
  mysql> create view stu_info as select s.sname,s.ssex,c.cname,sc.score from student s join sc on s.sno=sc.sno join course c on sc.cno=c.cno;
  Query OK, 0 rows affected (0.01 sec)

• 删除所有索引

  mysql> drop index index_Cno on course;
  Query OK, 0 rows affected (0.01 sec)
  Records: 0  Duplicates: 0  Warnings: 0

       内容围绕 MySQL 数据库操作展开。首先新建名为 mydb15_indexstu 的数据库并使用；接着新建三张表，包括学生表 Student、课程表 Course、选课表 SC，分别定义了表结构及相关约束；最后是处理表的操作，涵盖修改 Student 表字段属性、为 Course 表字段设置索引、为 SC 表建立组合主键索引、创建 stu_info 视图查询全体学生相关信息等任务。