MySQL视图索引基础练习

表定义

学生表：Student (Sno, Sname, Ssex , Sage, Sdept)
学号，姓名，性别，年龄，所在系 Sno为主键

课程表：Course (Cno, Cname,)
课程号，课程名 Cno为主键

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

练习

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

mysql> create table Student(
    -> Sno int primary key comment '学号',
    -> Sname varchar(50) unique comment '姓名',
    -> Ssex enum('男','女') comment '性别',
    -> Sage int comment '年龄',
    -> Sdept varchar(50) default '计算机' comment '所在系'
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> desc Student;
+-------+-------------------+------+-----+-----------+-------+
| Field | Type              | Null | Key | Default   | Extra |
+-------+-------------------+------+-----+-----------+-------+
| Sno   | int               | NO   | PRI | NULL      |       |
| Sname | varchar(50)       | YES  | UNI | NULL      |       |
| Ssex  | enum('男','女')    | YES  |     | NULL      |       |
| Sage  | int               | YES  |     | NULL      |       |
| Sdept | varchar(50)       | YES  |     | 计算机    |       |
+-------+-------------------+------+-----+-----------+-------+
5 rows in set (0.04 sec)

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

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

mysql> desc Student;
+-------+-------------------+------+-----+-----------+-------+
| Field | Type              | Null | Key | Default   | Extra |
+-------+-------------------+------+-----+-----------+-------+
| Sno   | int               | NO   | PRI | NULL      |       |
| Sname | varchar(50)       | YES  | UNI | NULL      |       |
| Ssex  | enum('男','女')    | YES  |     | NULL      |       |
| Sage  | smallint          | YES  |     | NULL      |       |
| Sdept | varchar(50)       | YES  |     | 计算机    |       |
+-------+-------------------+------+-----+-----------+-------+
5 rows in set (0.00 sec)

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

mysql> create table SC(
    -> Sno int comment '学号',
    -> Cno int comment '课程号',
	-> Score int comment '成绩',
    -> primary key SC_INDEX(Sno,Cno)
    -> );
Query OK, 0 rows affected (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)

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

(1)创建课程表：

mysql> create table Course(
    -> Con int comment '课程号',
    -> Cname varchar(50) comment '课程名'
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> desc Course;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Con   | int         | YES  |     | NULL    |       |
| Cname | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

(2)给学生表、课程表、学生选课表插入数据

a.学生表(Student)

mysql> insert into Student values 
    -> (1, '张三', '男', 20, '计算机'),
    -> (2, '李四', '女', 22, '计算机'),
    -> (3, '王五', '男', 21, '数学'),
    -> (4, '赵六', '女', 23, '数学'),
    -> (5, '孙七', '男', 24, '英语');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0\

mysql> select * from Student;
+-----+--------+------+------+-----------+
| Sno | Sname  | Ssex | Sage | Sdept     |
+-----+--------+------+------+-----------+
|   1 | 张三   | 男   |   20  | 计算机     |
|   2 | 李四   | 女   |   22  | 计算机     |
|   3 | 王五   | 男   |   21  | 数学       |
|   4 | 赵六   | 女   |   23  | 数学       |
|   5 | 孙七   | 男   |   24  | 英语       |
+-----+--------+------+------+-----------+
5 rows in set (0.00 sec)

b.课程表(Course)

mysql> insert into Course values (1, '云原生'),(1, '云原生'),(2, '高等数学'),(2, '高等数学'),(3, '英语');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from Course;
+------+--------------+
| Con  | Cname        |
+------+--------------+
|    1 | 云原生        |
|    1 | 云原生        |
|    2 | 高等数学      |
|    2 | 高等数学      |
|    3 | 英语         |
+------+--------------+
5 rows in set (0.01 sec)

c.学生选课(SC)

mysql> insert into SC values (1, 1, 90),(2, 1, 85),(3, 2, 92),(4, 2, 88),(5, 3, 95);
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from SC;
+-----+-----+-------+
| Sno | Cno | Score |
+-----+-----+-------+
|   1 |   1 |    90 |
|   2 |   1 |    85 |
|   3 |   2 |    92 |
|   4 |   2 |    88 |
|   5 |   3 |    95 |
+-----+-----+-------+
5 rows in set (0.00 sec)

(3)创建stu_info视图。

mysql> create view v_S_SC_C as 
    -> select distinct Sname 姓名,Ssex 性别,Cname 课程名,Score 成绩 from Student S 
    -> inner join SC on S.Sno = SC.Sno 
    -> inner join Course C on SC.Cno = C.Con;
Query OK, 0 rows affected (0.04 sec)

mysql> desc v_S_SC_C;
+-----------+-------------------+------+-----+---------+-------+
| Field     | Type              | Null | Key | Default | Extra |
+-----------+-------------------+------+-----+---------+-------+
| 姓名      | varchar(50)       | YES  |     | NULL    |       |
| 性别      | enum('男','女')   | YES  |     | NULL    |       |
| 课程名    | varchar(50)       | YES  |     | NULL    |       |
| 成绩      | int               | YES  |     | NULL    |       |
+-----------+-------------------+------+-----+---------+-------+
4 rows in set (0.03 sec)

mysql> select * from v_S_SC_C;
+--------+--------+--------------+--------+
| 姓名   | 性别   | 课程名       | 成绩   |
+--------+--------+--------------+--------+
| 张三   | 男     | 云原生       |     90 |
| 李四   | 女     | 云原生       |     85 |
| 王五   | 男     | 高等数学     |     92 |
| 赵六   | 女     | 高等数学     |     88 |
| 孙七   | 男     | 英语         |     95 |
+--------+--------+--------------+--------+
5 rows in set (0.00 sec)