数据库基础练习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: 0mysql> 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: 0mysql> 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: courseNon_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: cnoCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:
Index_comment:Visible: YESExpression: NULL
*************************** 2. row ***************************Table: courseNon_unique: 1Key_name: idx_cnoSeq_in_index: 1Column_name: cnoCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:
Index_comment:Visible: YESExpression: 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: 0mysql> alter table sc add constraint SC_INDEX primary key(sno,cno);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> 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: 0mysql> 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