mysql第四次作业

#(1)修改student 表中年龄(sage)字段属性，数据类型由int 改变为smallint
mysql> alter table student modify sage smallint not null;
Query OK, 0 rows affected (0.11 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(30) | NO   | UNI | NULL    |                |
| Ssex  | varchar(2)  | NO   |     | NULL    |                |
| Sdept | varchar(10) | NO   |     | 计算机  |                |
| sage  | smallint    | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)


#(2)为Course表中Cno 课程号字段设置索引,并查看索引
mysql> create index index_Cno on course(Cno);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from course;
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| course |          0 | PRIMARY   |            1 | Cno         | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| course |          1 | index_Cno |            1 | Cno         | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)

#(3)为SC表建立按学号(sno)和课程号(cno)组合的升序的主键索引，索引名为SC_INDEX
mysql> ALTER TABLE SC DROP PRIMARY KEY;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE SC ADD PRIMARY KEY (Sno, Cno);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE SC
    -> ADD CONSTRAINT SC_INDEX UNIQUE (Sno, Cno) USING BTREE;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 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.01 sec)

#(4)创建一视图 stu_info,查询全体学生的姓名，性别，课程名，成绩
mysql> create view stu_info as
    -> select s.Sname as student_name,s.Ssex as gender,c.Cname as course_name,sc.score as grade
    -> from student s
    -> join sc on s.Sno=sc.Sno
    -> join course c on sc.Cno=c.Cno;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from stu_info;
Empty set (0.00 sec)

#(5)删除所有索引
mysql> drop index index_Cno on course;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop index SC_INDEX on sc;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0