题目
步骤
创建数据库和表
create database mydb15_indexstu;
use mydb15_indexstu;
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);
create table course(
cno int primary key not null,
cname varchar(20) not null);
create table SC(sno int not null,
cno varchar(10) primary key not null,
score int not null);
操作
1、修改student 表中年龄(sage)字段属性,数据类型由int 改变为smallint
alter table student modify sage smallint;
2、为Course表中Cno 课程号字段设置索引,并查看索引
create unique index course_cno_index on course(cno);
3、为SC表建立按学号(sno)和课程号(cno)组合的升序的主键索引,索引名为SC_INDEX
alter table SC drop primary key;
alter table SC add constraint SC_index primary key(sno, cno);
4、创建一视图 stu_info,查询全体学生的姓名,性别,课程名,成绩
create view stu_info as
select a.sname, a.ssex, b.cname, sc.score
from student a
join sc on a.sno=sc.sno
join course b on sc.cno=b.cno;
5、删除所有索引
drop index course_cno_index on course;
alter table sc drop primary key;