第四次作业题目
新建数据库并使用
create database mydb15_indexstu;
use mydb15_indexstu;
新建表
1.学生表student,定义主键,姓名不能重名,性别只能输入男或女,所在系的默认值是“计算机”,结构如下:Student(sno学号,Sname姓名,Ssex性别,Sage 年龄,sdept所在系)sno为主键
create database mydb15_indexstu; use mydb15_indexstu;
2.用SQL语句创建课程表
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 null );
3.用SOL语句创建选课表
create table course ( cno int primary key not null, cname varchar(20) not null );
create table sc ( sno int not null, cno int not null, score int not null, primary key (sno, cno) );
处理表
1.修改student 表中年龄(sage)字段属性,数据类型由int 改变为smallint
2.为Course表中Cno 课程号字段设置索引,并查看索引
3.为SC表建立按学号(sno)和课程号(cno)组合的升序的主键索引,索引名为SC_INDEX
4.创建一视图 stu info,查询全体学生的姓名,性别,课程名,成绩
5.删除所有索引
实现
- 修改
student
表中sage
字段的数据类型:
alter table student modify sage smallint;
- 为
Course
表中的Cno
字段设置索引,并查看索引:
create index idx_cno on course(cno);
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.00 sec)
- 为
SC
表建立按sno
和cno
组合的升序的主键索引,索引名为SC_INDEX
:
alter table sc add primary key sc_index(sno, cno);
#由于之前已经定义主键上句受影响应改为
alter table sc unique key sc_index(sno,cno);
- 创建视图
stu_info
,查询全体学生的姓名、性别、课程名和成绩(单行):
create view stu_info as select student.sname, student.ssex, course.cname, sc.score from student join sc on student.sno = sc.sno join course on sc.cno = course.cno;
- 删除所有索引:
drop index idx_cno on course;
alter table sc drop primary key;