MySQL第四次作业

一、建库

mysql> create database mydb15_indexstu;
 
mysql> use mydb15_indexstu;

二、建表

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);

mysql> create table Course(
    -> Cno int primary key not null,
    -> Cname varchar(20) not null);

mysql> create table SC(
    -> Sno int not null,
    -> Cno varchar(10) primary key not null,
    -> Score int not null);

三、运行

1.

mysql> alter table Student modify Sage smallint not null;
+-------+-------------+------+-----+---------+----------------+
| 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    | NO   |     | NULL    |                |
| Sdept | varchar(10) | NO   |     | 计算机  |                |
+-------+-------------+------+-----+---------+----------------+

2.

mysql> create index index_Cno on Course(Cno);
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 `index_Cno` (`Cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

3.

mysql> alter table SC add index SC_INDEX (Sno,Cno asc);
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

4.

mysql> create view stuinfo1 as select Sname,Ssex,SC.Cno,Score 
    -> from Student join SC on Student.Sno=SC.Sno;
 
mysql> create view stuinfo as select Sname,Ssex,Cname,Score 
    -> from stuinfo1 join Course on stuinfo1.Cno=Course.Cno;
mysql> desc stuinfo;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Sname | varchar(30) | NO   |     | NULL    |       |
| Ssex  | varchar(2)  | NO   |     | NULL    |       |
| Cname | varchar(20) | NO   |     | NULL    |       |
| Score | int         | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

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 index_Cno on Course;
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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
 
 
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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci