MySQL视图索引练习

# 创建学生表
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 nul1);

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


# 创建SC 表
mysql> create table SC
(Sno int not null ,Cno int auto_increment primary key
 not null,Score int not null);


/*

1.修改student 表中年龄(sage)字段属性，数据类型由int 改变为smallint
2.为Course表中Cno 课程号字段设置索引,并查看索引
3.为SC表建立按学号(sno)和课程号(cno)组合的升序的主键索引，索引名为SC_INDEX
4.创建一视图 stu info,查询全体学生的姓名，性别，课程名，成绩
5.删除所有索引

*/

1.修改student 表中年龄(sage)字段属性，数据类型由int 改变为smallint
2.为Course表中Cno 课程号字段设置索引,并查看索引
3.为SC表建立按学号(sno)和课程号(cno)组合的升序的主键索引，索引名为SC_INDEX
4.创建一视图 stu info,查询全体学生的姓名，性别，课程名，成绩
5.删除所有索引

mysql> alter table Student modify sage smallint;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show columns from 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 index_Cno on Course(Cno);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 

# 查看索引
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: index_Cno
 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 rows in set (0.00 sec)

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

# 删除原有的主键索引
mysql> ALTER TABLE SC DROP PRIMARY KEY;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0


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

# 创建联合索引
mysql> ALTER TABLE SC ADD constraint SC_UNION_INDEX  PRIMARY KEY(Cno,Sno);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc SC;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| Sno   | int  | NO   | PRI | NULL    |       |
| Cno   | int  | NO   | PRI | NULL    |       |
| Score | int  | NO   |     | NULL    |       |
+-------+------+------+-----+---------+-------+
3 rows in set (0.00 sec)

# 查看是否创建索引
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 | Cno         | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| SC    |          0 | PRIMARY  |            2 | Sno         | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)

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

CREATE OR REPLACE VIEW stu_info AS
SELECT
    Student.Sname,
    Student.Ssex,
    Course.Cname,
    SC.Score
FROM
    Student
INNER JOIN SC ON Student.Sno = SC.Sno
INNER JOIN Course ON SC.Cno = Course.Cno;

5.删除所有索引

# 删除 Student表的索引
mysql> ALTER TABLE Student DROP INDEX Sname;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> ALTER TABLE Student MODIFY Sno INT;
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE Student DROP PRIMARY KEY;
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

# 删除Course表的索引
mysql> ALTER TABLE Course DROP INDEX index_Cno;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE Course DROP PRIMARY KEY;
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

# 删除SC表的索引
mysql> ALTER TABLE SC DROP  PRIMARY KEY;
Query OK, 7 rows affected (0.05 sec)
Records: 7  Duplicates: 0  Warnings: 0