数据库第四次作业

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);

alter table student modify sage smallint;

create index index_cno on course(cno);
show index from course;

create unique index sc_index on sc(sno,cno);

create view stu_info as select student.sname,student.ssex,course.cname,sc.score from student,course,sc
where student.sno = sc.sno and course.cno = sc.cno;

drop index index_cno on course;
drop index sc_index on sc;