MySQL表结构，索引，视图操作

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

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

课程表：Course (Cno, Cname,)

课程号，课程名 Cno为主键

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

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

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

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

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

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

1.建表

root@localhost [student_info]>create table Student(
    -> Sno int(10) primary key,
    -> Sname varchar(15) unique,
    -> Ssex char(2) check(Ssex in ('男','女')),
    -> Sage int,
    -> Sdept varchar default '计算机'
    -> );
root@localhost [student_info]>desc student;
+-------+-------------+------+-----+-----------+-------+
| Field | Type        | Null | Key | Default   | Extra |
+-------+-------------+------+-----+-----------+-------+
| Sno   | int         | NO   | PRI | NULL      |       |
| Sname | varchar(15) | YES  | UNI | NULL      |       |
| Ssex  | varchar(5)  | YES  |     | NULL      |       |
| Sage  | int         | YES  |     | NULL      |       |
| Sdept | varchar(15) | YES  |     | 计算机    |       |
+-------+-------------+------+-----+-----------+-------+
5 rows in set (0.00 sec)

2.修改表

root@localhost [student_info]>alter table student modify Sage smallint;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [student_info]>desc student;
+-------+-------------+------+-----+-----------+-------+
| Field | Type        | Null | Key | Default   | Extra |
+-------+-------------+------+-----+-----------+-------+
| Sno   | int         | NO   | PRI | NULL      |       |
| Sname | varchar(15) | YES  | UNI | NULL      |       |
| Ssex  | varchar(5)  | YES  |     | NULL      |       |
| Sage  | smallint    | YES  |     | NULL      |       |
| Sdept | varchar(15) | YES  |     | 计算机    |       |
+-------+-------------+------+-----+-----------+-------+
5 rows in set (0.00 sec)

3.创建索引

root@localhost [student_info]>create index SC_INDEX on SC (sno,c,cno);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

4.创建视图

root@localhost [student_info]>create view view1 as select s.sno,c.cname,s.score from course c join sc s on c.cno=s.cno;
 
root@localhost [student_info]>select s.sname,s.ssex,v.cname,v.score from student s join view1 v on s.sno=v.sno;
 
root@localhost [student_info]>create view stu_info as select s.sname,s.ssex,v.cname,v.score from student s join view1 v on s.sno=v.sno;