作业描述
![](https://i-blog.csdnimg.cn/direct/1951d6f1e6d3456a9418afae984056c4.jpeg)
处理
#1.修改Student表中年龄(Sage)字段属性,数据类型由int改变为smallint
mysql> alter table Student modify Sage smallint;
Query OK, 0 rows affected (0.03 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 | |
| Sage | smallint | YES | | NULL | |
| Sdept | varchar(10) | NO | | 计算机 | |
+-------+-------------+------+-----+-----------+----------------+
5 rows in set (0.01 sec)
#2.为Course表中Cno课程号字段设置索引,并查看索引
mysql> create index idx_Course_Cno on Course(Cno);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table Course \G;
*************************** 1. row ***************************
Table: Course
Create Table: CREATE TABLE `course` (
`Cno` int NOT NULL,
`Cname` varchar(20) NOT NULL,
PRIMARY KEY (`Cno`),
KEY `idx_Course_Cno` (`Cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
ERROR:
No query specified
#3.为SC表建立按学号(Sno)和课程号(Cno)组合的升序的主键索引,索引名为SC_INDEX
mysql> create index SC_INDEX on SC(Sno,Cno);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table SC \G;
*************************** 1. row ***************************
Table: SC
Create Table: CREATE TABLE `sc` (
`Sno` int NOT NULL,
`Cno` varchar(10) NOT NULL,
`Score` int NOT NULL,
PRIMARY KEY (`Cno`),
KEY `SC_INDEX` (`Sno`,`Cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
ERROR:
No query specified
#4.创建一视图stu_info,查询全体学生的姓名,性别,课程名,成绩
mysql> create or replace view stu_info(Sname,Ssex,Cno,Score) as select Sname,Ssex,Cno,Score from Student join sc on Student.Sno=SC.Sno;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+---------------------------+
| Tables_in_mydb15_indexstu |
+---------------------------+
| course |
| sc |
| stu_info |
| student |
+---------------------------+
4 rows in set (0.00 sec)
mysql> desc stu_info;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Sname | varchar(30) | NO | | NULL | |
| Ssex | varchar(2) | NO | | NULL | |
| Cno | varchar(10) | NO | | NULL | |
| Score | int | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
#5.删除所有索引
mysql> drop index SC_INDEX on SC;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> drop index idx_Course_Cno on Course;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0