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.创建 学生表，课程表，学生选课表。

mysql> create database db_zuoye1;
Query OK, 1 row affected (0.01 sec)

mysql> use db_zuoye1;
Database changed

mysql> CREATE TABLE Student(
    -> sno int(20) unique primary key auto_increment,
    -> sname varchar(30) unique not null,
    -> ssex varchar(10) check(ssex in("男","女")),
    -> sage int(10) not null,
    -> sdept varchar(50) default"计算机");
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> desc Student;
+-------+-------------+------+-----+-----------+----------------+
| Field | Type        | Null | Key | Default   | Extra          |
+-------+-------------+------+-----+-----------+----------------+
| sno   | int         | NO   | PRI | NULL      | auto_increment |
| sname | varchar(30) | NO   | UNI | NULL      |                |
| ssex  | varchar(10) | YES  |     | NULL      |                |
| sage  | int         | NO   |     | NULL      |                |
| sdept | varchar(50) | YES  |     | 计算机    |                |
+-------+-------------+------+-----+-----------+----------------+
5 rows in set (0.01 sec)

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

mysql> desc Course;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| cno   | int         | NO   | PRI | NULL    |       |
| cname | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 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(sno) references Course(cno));
Query OK, 0 rows affected, 3 warnings (0.03 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)

mysql>

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

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

mysql> desc Student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| sno   | int(20)     | NO   | PRI | NULL    | auto_increment |
| sname | varchar(30) | NO   | UNI | NULL    |                |
| ssex  | varchar(10) | YES  |     | NULL    |                |
| sage  | smallint(6) | YES  |     | NULL    |                |
| sdept | varchar(50) | YES  |     | 计算机  |                |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 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)

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

#先查询，再创建视图，
mysql> select Student.sname as studentsname,Student.ssex as studentssex,Course.cno as coursecno,sc.score as scscorre from Student,Course,sc where Student.sno = sc.sno and Course.cno=sc.cno;
Empty set (0.00 sec)

mysql> create view stu_info as select Student.sname as studentsname,Student.ssex as studentssex,Course.cno as coursecno,sc.score as scscorre from Student,Course,sc where Student.sno = sc.sno and Course.cno=sc.cno;
Query OK, 0 rows affected (0.02 sec)

mysql> desc stu_info;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| studentsname | varchar(30) | NO   |     | NULL    |       |
| studentssex  | varchar(10) | YES  |     | NULL    |       |
| coursecno    | int         | NO   |     | NULL    |       |
| scscorre     | int         | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)