第四次作业

1.建库并启用

mysql> create database mydb15_indexstu;
Query OK, 1 row affected (0.01 sec)
 
mysql> use mydb15_indexstu;
Database changed
 

2.建表

mysql> create table Student(Sno int primary key auto_increment,Sname varchar(30) not null unique,Ssex varchar(2) check (Ssex='男' or Ssex='女') not null,Sage int not null,Sdept varchar(10) default '计算机' not null);
Query OK, 0 rows affected (0.04 sec)
 
mysql> create table Course(Cno int primary key not null,Cname varchar(20) not null);
Query OK, 0 rows affected (0.02 sec)
 
mysql> create table SC(Sno int not null,Cno varchar(10) primary key not null,Score int not null);
Query OK, 0 rows affected (0.02 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(2)  | NO   |     | NULL      |                |
| sage  | int         | NO   |     | NULL      |                |
| sdept | varchar(10) | NO   |     | 计算机    |                |
+-------+-------------+------+-----+-----------+----------------+
5 rows in set (0.00 sec)
​
mysql> desc course;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| cno   | int         | NO   | PRI | NULL    |       |
| cname | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
​
mysql> desc sc;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno   | int         | NO   |     | NULL    |       |
| cno   | varchar(10) | NO   | PRI | NULL    |       |
| score | int         | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

3.处理表

（1）.修改student 表中年龄(sage)字段属性，数据类型由int 改变为smallint

mysql> alter table student modify column Sage smallint;
Query OK, 0 rows affected (0.06 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.00 sec)

（2）。为Course表中Cno 课程号字段设置索引,并查看索引

mysql> create index index_course_cno on Course(Cno);
Query OK, 0 rows affected (0.02 sec)

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`),
  UNIQUE KEY `index_cno` (`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

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

mysql> alter table sc add unique index SC_INDEX (sno asc,cno asc);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

mysql> create view stu_info as select student.sno,ssex,sc.cno,score from student join sc on student.sno=sc.sno;
Query OK, 0 rows affected (0.01 sec)

（5）.删除所有索引

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