1.建库
create database mydb15_indexstu;
use mydb15_indexstu;
2.建表
2.1 student表学(sno)号为主键,姓名(sname)不能重名,性别(ssex)仅能输入男或女,默认所在系别(sdept)为’计算机’
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);
2.2 course表课程号(cno)为主键
create table course(
cno int primary key not null,
cname varchar(20) not null);
2.3 sc表课程号(cno)为主键
create table sc(
sno int not null,
cno varchar(10) primary key not null,
score int not null);
3.实现需求
3.1 修改student表中年龄(sage)字段属性,将数据类型由int改为smallint
alter table student modify sage smallint;
3.2 为course表中 课程号(cno)字段设置索引,并查看索引
create index cno_index on course(cno);
show create table course\G
3.3 为sc表建立按学号(sno)和课程号(cno)组合的升序的主键索引,索引名sc_index
create index sc_index on sc(sno,cno asc);
show create table sc\G
3.4 创建一个视图stu_info,查询全体学生的姓名,性别,课程名,成绩
create view stu_info as select sname,ssex,cname,score from student join sc on student.sno=sc.sno join course on sc.cno=course.cno;
show tables;
3.5 删除所有索引
drop index cno_index on course;
drop index sc_index on sc;