主键，外键，视图（第三次作业1）

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

学号，姓名，性别，年龄，所在系   Sno为主键课程表：Course (Cno,   Cname,)

mysql> create table Course(
    -> Cno int(10) primary key,
    -> Cname varchar(16));

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

课程号，课程名     Cno为主键学生选课表：SC (Sno,   Cno,     Score)

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

mysql> 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));
Query OK, 0 rows affected, 3 warnings (0.02 sec)

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

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

   mysql> create table Student(
       -> Sno int primary key auto_increment,
       -> Sname varchar(10) unique not null,
       -> Ssex varchar(4) check(Ssex='男' or Ssex='女'),
       -> Sage int,
       -> Sdept varchar(255) default '计算机'
       -> );
   Query OK, 0 rows affected (0.02 sec)
   
   mysql> select * from Student;
   Empty set (0.01 sec)
   
   mysql> desc Student;
   +-------+--------------+------+-----+-----------+----------------+
   | Field | Type         | Null | Key | Default   | Extra          |
   +-------+--------------+------+-----+-----------+----------------+
   | Sno   | int          | NO   | PRI | NULL      | auto_increment |
   | Sname | varchar(10)  | NO   | UNI | NULL      |                |
   | Ssex  | varchar(4)   | YES  |     | NULL      |                |
   | Sage  | int          | YES  |     | NULL      |                |
   | Sdept | varchar(255) | YES  |     | 计算机    |                |
   +-------+--------------+------+-----+-----------+----------------+
   5 rows in set (0.01 sec)
   

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

   mysql> alter table Student modify Sage smallint;
   Query OK, 0 rows affected (0.06 sec)
   Records: 0  Duplicates: 0  Warnings: 0
   
   mysql> desc Student;
   +-------+--------------+------+-----+-----------+----------------+
   | Field | Type         | Null | Key | Default   | Extra          |
   +-------+--------------+------+-----+-----------+----------------+
   | Sno   | int          | NO   | PRI | NULL      | auto_increment |
   | Sname | varchar(10)  | NO   | UNI | NULL      |                |
   | Ssex  | varchar(4)   | YES  |     | NULL      |                |
   | Sage  | smallint     | YES  |     | NULL      |                |
   | Sdept | varchar(255) | YES  |     | 计算机    |                |
   +-------+--------------+------+-----+-----------+----------------+
   5 rows in set (0.01 sec)
   

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

   mysql> create unique index SC_INDEX on SC(Sno asc,Cno asc);
   Query OK, 0 rows affected (0.02 sec)
   Records: 0  Duplicates: 0  Warnings: 0
   

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

   mysql> create view stu_info
       -> as select Student.Sname,Student.Ssex,Course.Cname,SC.Score
       -> from Student,Course,SC
       -> where Student.Sno=SC.Sno and Course.Cno=SC.Cno;
   Query OK, 0 rows affected (0.01 sec)
   
   mysql>
   mysql>  select * from stu_info;
   Empty set (0.00 sec)  ---- 没有数据查询结果为空