课本上的数据库知识

//老师
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');