//老师
create table Teachers
(
Tno char(7) primary key,
Tname char(10) not null,
Sex char(2) check(Sex = '男' or Sex = '女'),
Birthday Date,
Title char(4),
Dno char(4),
foreign key(Dno) references Departments(Dno)
);
//学生
create table Students
(
Sno char(9) primary key,
Sname char(10) not null,
Sex char(2) check(Sex = '男' or Sex = '女'),
Birthday Datetime,
Enrollyear char(4),
Speciality char(20),
Dno char(3),
foreign key(Dno) references Department(Dno)
);
//课程
create table Courses
(
Cno char(5) primary key,
Cname char(20) not null,
Period smallint,
Credit smallint
);
//学生选修课程
create table SC
(
Sno char(9),
Cno char(5),
Grade smallint check(Grade >=0 and Grade <= 100),
primary key(Sno,Cno),
foreign key(Sno) references Students(Sno),
foreign key(Cno) references Courses(Cno)
);
//老师教授课程
create table Teaches
(
Tno char(7),
Cno char(5),
TCscore smallint,
primary key(Tno,Cno),
foreign key(Tno) references Teachers(Tno),
foreign key(Cno) references Courses(Cno)
);
-------------------------------------
//查询
//降序输出
select * from SC where Cno = ‘CS202’ order by Grade desc;
//查询某一条件的个数
select count(*) from SC where Cno = 'CS302';
//查询最大、最小、平均值
select max(Grade), min(Grade),avg(Grade) from SC where Cno = 'CS302';
//分组
//查询每个学生的平均成绩
select Sno, avg(Grade) from SC group by Sno;
select Sno, avg(Grade) from Sc group by Sno having avg(grade) > 85;
//连接查询
select Students.Sno, Sname,Grade from
Students, SC
where Students.Sno = SC.Sno and Cno = 'CS302' and Grade > 90;
//嵌套查询
select Sno,Sname from Students where Sex = '女' and Speciality in
( select Specliality from Students where Sname = '王丽丽');
//存在量词的查询
select Sno,Cname
from Students S
where exists
(select * from SC
where Sno = S.Sno and Cno = 'CS302');
//查询选修了全部课程的学生的学号和姓名
select Sno, Sname
from Students S
where not exists
( select * from Courses
where not exists
( select * from SC
where SC.Sno = S.Sno and SC.Cno = C.Cno
)
);
-----------------------------------------
//插入操作
insert into Students values('2010213','细弯眉', '男', 1989-09-2,'计算数学', 'MATH');
insert into SC(Sno, Cno) values('200132', 'MA302');
//删除操作
delete from Students where Sno = '200132';
//删除计算机专业的所有学生的选课记录
delete from SC where Sno in(select Sno from Students where Speciality = '计算机');
//修改操作
//将软件工程课程成绩低于60分的所有学生的成绩提高5分
update SC set Grade = Grade + 5
where Grade < 60 and Cno in(select Cno from Courses where Cname = '软件工程');
-----------------------------------------
//视图
create view SE_students as select Sno,sname,Sex,birthday,Dno
from Students where Speciality = '软件工程' with check option;
create view EI_SC(sco,cno,Grade)
as select * from SC
where Sno in(select Sno from Students where Dno = 'IE');