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,查询全体学生的姓名，性别，课程名，成绩。

# 用SQL语句按要求创建三个表
mysql8.0.30 [(none)]>create database chap04;
Query OK, 1 row affected (0.00 sec)

mysql8.0.30 [(none)]>use chap04
Database changed
mysql8.0.30 [chap04]>create table student( Sno int(20) primary key, Sname char(20) unique, Ssex char(20) check(Ssex in ("男","女")), Sage int, Sdept char(20) default '计算机');
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql8.0.30 [chap04]>desc student;
+-------+----------+------+-----+-----------+-------+
| Field | Type     | Null | Key | Default   | Extra |
+-------+----------+------+-----+-----------+-------+
| Sno   | int      | NO   | PRI | NULL      |       |
| Sname | char(20) | YES  | UNI | NULL      |       |
| Ssex  | char(20) | YES  |     | NULL      |       |
| Sage  | int      | YES  |     | NULL      |       |
| Sdept | char(20) | YES  |     | 计算机    |       |
+-------+----------+------+-----+-----------+-------+
5 rows in set (0.01 sec)

mysql8.0.30 [chap04]>create table Course(
    -> Cno int(20) primary key,
    -> Cname char(20)
    -> );
Query OK, 0 rows affected, 1 warning (0.37 sec)

mysql8.0.30 [chap04]>desc Course;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| Cno   | int      | NO   | PRI | NULL    |       |
| Cname | char(20) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

#修改数据类型
mysql8.0.30 [chap04]>alter table student modify Sage smallint;
Query OK, 0 rows affected (0.38 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql8.0.30 [chap04]>desc student;
+-------+----------+------+-----+-----------+-------+
| Field | Type     | Null | Key | Default   | Extra |
+-------+----------+------+-----+-----------+-------+
| Sno   | int      | NO   | PRI | NULL      |       |
| Sname | char(20) | YES  | UNI | NULL      |       |
| Ssex  | char(20) | YES  |     | NULL      |       |
| Sage  | smallint | YES  |     | NULL      |       |
| Sdept | char(20) | YES  |     | 计算机    |       |
+-------+----------+------+-----+-----------+-------+
5 rows in set (0.00 sec)

#给SC表添加索引
mysql8.0.30 [chap04]>create table SC( Sno int(20), Cno int(20), Score int,primary key (Sno,Cno));
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql8.0.30 [chap04]>create unique index SC_INDEX on SC(Sno asc,Cno asc);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql8.0.30 [chap04]>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)

mysql8.0.30 [chap04]>show index from SC;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| SC    |          0 | PRIMARY  |            1 | Sno         | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| SC    |          0 | PRIMARY  |            2 | Cno         | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| SC    |          0 | SC_INDEX |            1 | Sno         | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| SC    |          0 | SC_INDEX |            2 | Cno         | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.00 sec)

#给三个表写上一些数据方便观察效果
mysql8.0.30 [chap04]>insert into student values(1,'xiaoming','男',18,'英语'),(2,'xiaohong','女',19,'中文'),(3,'xiaolan','女',17,'计算机');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql8.0.30 [chap04]>select * from student;
+-----+----------+------+------+-----------+
| Sno | Sname    | Ssex | Sage | Sdept     |
+-----+----------+------+------+-----------+
|   1 | xiaoming | 男   |   18 | 英语      |
|   2 | xiaohong | 女   |   19 | 中文      |
|   3 | xiaolan  | 女   |   17 | 计算机    |
+-----+----------+------+------+-----------+
3 rows in set (0.00 sec)

mysql8.0.30 [chap04]>insert into Course values(1,'music'),(2,'mate'),(3,'PE');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql8.0.30 [chap04]>select * from Course;
+-----+-------+
| Cno | Cname |
+-----+-------+
|   1 | music |
|   2 | mate  |
|   3 | PE    |
+-----+-------+
3 rows in set (0.00 sec)

mysql8.0.30 [chap04]>insert into SC values(1,2,60),(1,3,95),(2,1,75),(2,2,88),(3,3,100);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql8.0.30 [chap04]>select * from SC;
+-----+-----+-------+
| Sno | Cno | Score |
+-----+-----+-------+
|   1 |   2 |    60 |
|   1 |   3 |    95 |
|   2 |   1 |    75 |
|   2 |   2 |    88 |
|   3 |   3 |   100 |
+-----+-----+-------+
5 rows in set (0.00 sec)

#创建视图stu_info查看三表要求内容合并效果
mysql8.0.30 [chap04]>create view stu_info as select st.Sname,st.Ssex,co.Cname,SC.Score from student st,Course co,SC where st.Sno=SC.Sno and co.Cno=SC.Cno;
Query OK, 0 rows affected (0.00 sec)

mysql8.0.30 [chap04]>select * from stu_info;
+----------+------+-------+-------+
| Sname    | Ssex | Cname | Score |
+----------+------+-------+-------+
| xiaoming | 男   | mate  |    60 |
| xiaoming | 男   | PE    |    95 |
| xiaohong | 女   | music |    75 |
| xiaohong | 女   | mate  |    88 |
| xiaolan  | 女   | PE    |   100 |
+----------+------+-------+-------+
5 rows in set (0.00 sec)