数据库基础练习4（有关索引，视图完整解答）

建立需要的表

学生表

mysql> create table studnet(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.08 sec)

课程表，选课表

mysql> create table course (cno int primary key not null ,cname varchar(20) not null);
Query OK, 0 rows affected (0.04 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.05 sec)

处理表 

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

mysql> alter table student modify column sage smallint;
ERROR 1146 (42S02): Table 'mydb15_indexstu.student' doesn't exist
mysql> alter table studnet modify column sage smallint;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table studnet rename student;
Query OK, 0 rows affected (0.05 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  | smallint    | YES  |     | NULL    |                |
| sdept | varchar(10) | NO   |     | 计算机  |                |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

在创建学生表名字出现拼写错误，将名字改回

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

mysql> create index idx_cno on course(cno);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from course;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| course |          0 | PRIMARY  |            1 | cno         | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| course |          1 | idx_cno  |            1 | cno         | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.02 sec)

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: idx_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.01 sec)

加上\G使观看更加简单

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

mysql> alter table sc drop primary key;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table sc add constraint SC_INDEX primary key(sno,cno);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> create index SC_INDEX on sc(sno,cno);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

原本是想先设置联合主键在改名，但是行不通，可能是直接设置联合索引 

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;
Query OK, 0 rows affected (0.01 sec)

5. 删除所有索引 

mysql> alter table course drop  index idx_cno;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> alter table student drop index sname;

mysql> alter table sc drop index SC_INDEX;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0