索引和视图

学生表：Student (Sno, Sname, Ssex , Sage, Sdept)
学号，姓名，性别，年龄，所在系 Sno为主键
课程表：Course (Cno, Cname,)
课程号，课程名 Cno为主键
学生选课表：SC (Sno, Cno, Score)
学号，课程号，成绩 Sno,Cno为主键

mysql> create table Student(
    -> Sno int primary key auto_increment,
    -> Sname varchar(255) not null unique,
    -> Ssex char(2)  check(Ssex='男' or Ssex='女'),
    -> Sage int,
    -> Sdept varchar(255) default '计算机'
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> create table Course (
    -> Con int(10) primary key,
    -> Cname varchar(16)
    -> );
Query OK, 0 rows affected, 1 warning (0.02 sec)

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



1.用SQL语句创建学生表student，定义主键，姓名不能重名，性别只能输入男或女，所在系的默认值是 “计算机”。
mysql> desc Student;
+-------+--------------+------+-----+-----------+----------------+
| Field | Type         | Null | Key | Default   | Extra          |
+-------+--------------+------+-----+-----------+----------------+
| Sno   | int          | NO   | PRI | NULL      | auto_increment |
| Sname | varchar(255) | NO   | UNI | NULL      |                |
| Ssex  | char(2)      | YES  |     | NULL      |                |
| Sage  | int          | YES  |     | NULL      |                |
| Sdept | varchar(255) | YES  |     | 计算机    |                |
+-------+--------------+------+-----+-----------+----------------+
5 rows in set (0.02 sec)

2.修改student 表中年龄（age）字段属性，数据类型由int 改变为smallint。
mysql> desc Student;
+-------+--------------+------+-----+-----------+----------------+
| Field | Type         | Null | Key | Default   | Extra          |
+-------+--------------+------+-----+-----------+----------------+
| Sno   | int          | NO   | PRI | NULL      | auto_increment |
| Sname | varchar(255) | NO   | UNI | NULL      |                |
| Ssex  | char(2)      | YES  |     | NULL      |                |
| Sage  | smallint     | YES  |     | NULL      |                |
| Sdept | varchar(255) | YES  |     | 计算机    |                |
+-------+--------------+------+-----+-----------+----------------+
5 rows in set (0.00 sec)

3.为SC表建立按学号（sno）和课程号（cno）组合的升序的主键索引，索引名为SC_INDEX 。
 create unique index sc_index on SC(Sno asc,Cno asc);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

4.创建一视图 stu_info,查询全体学生的姓名，性别，课程名，成绩。
create view stu_info as select Student.Sname as '年龄',Student.Ssex as '性别',Course.Cname as '课程 名',SC.Score as '成绩' from Student,Course,SC;
Query OK, 0 rows affected (0.01 sec)

mysql> describe stu_info;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| 年龄      | varchar(255) | NO   |     | NULL    |       |
| 性别      | char(2)      | YES  |     | NULL    |       |
| 课程名    | varchar(16)  | YES  |     | NULL    |       |
| 成绩      | int          | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)