数据库基础练习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