数据库作业（4）

题目：

 

解题过程：

建库：

mysql> create database mydb15_indexstu;

使用库：

mysql> use mydb15_indexstu;

创建student表：

mysql> create table Student(
Sno int primary key auto_increment,
Sname varchar(30) not nu
ll unique,
Ssex varchar(2) check(Ssex='男' or Ssex='女') not null,
Sage int not null,
Sdept varchar(10) default '计算机' not null);

查看建表情况：

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   |     | 计算机    |                |
+-------+-------------+------+-----+-----------+----------------+

创建course表：

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

查看建表情况：

mysql> desc Course;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Cno   | int         | NO   | PRI | NULL    |       |
| Cname | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

创建sc表：

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

查看建表情况：

mysql> desc SC;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Sno   | int         | NO   |     | NULL    |       |
| Cno   | varchar(10) | NO   | PRI | NULL    |       |
| Score | int         | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

 

处理表：

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

mysql> alter table Student modify column Sage smallint;

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   |     | 计算机    |                |
+-------+-------------+------+-----+-----------+----------------+

 

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

mysql> create index Cno_index on Course(Cno);

mysql> show index from Course\G
*************************** 1. row ***************************
        Table: course
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: Cno
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: course
   Non_unique: 1
     Key_name: Cno_index
 Seq_in_index: 1
  Column_name: Cno
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL

 

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

mysql> create index SC_INDEX on SC(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
1 row in set (0.00 sec)

mysql> show index from SC\G
*************************** 1. row ***************************
        Table: sc
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: Cno
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: sc
   Non_unique: 1
     Key_name: SC_INDEX
 Seq_in_index: 1
  Column_name: Sno
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 3. row ***************************
        Table: sc
   Non_unique: 1
     Key_name: SC_INDEX
 Seq_in_index: 2
  Column_name: Cno
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL

 

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

mysql> create view stu_info as 
select S.Sname,S.Ssex,C.Cname,SC.Score 
from Student S 
join SC on S.Sno=SC.Sno 
join Course C on SC.Cno=C.Cno;

mysql> desc stu_info;
+-------+-------------+------+-----+---------+-------+
| 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 Cno_index on Course;
Query OK, 0 rows affected (0.01 sec)

mysql> drop index SC_INDEX on SC;
Query OK, 0 rows affected (0.01 sec)