学生——课程数据库
?
* 学生表：Student(Sno,Sname,Ssex,Sage,Sdept)
* 课程表：Course(Cno,Cname,Cpno,Ccredit)
* 学生选课表:Sc(Cno,Cno,Crade)
?
?
学生表：Student
?
?
?
课程表：Course
?
?
?
?
学生选课表:Sc
?
?
?
?
?
一：其本表的定义、删除、修改
?
create table Student1( Sno char(9) primary key, Sname char(20) unique, Ssex char(2), Sage smallint, Sdept char(20))create table Course1 ( Cno char(4) primary key, Cname char(40), Cpno char(4), Ccredit smallint)create table Sc1( Sno char(9), Cno char(4), Grade smallint, primary key(Sno,Cno), foreign key(Sno)references Student(Sno), foreign key(Cno) references Course(Cno))alter table Student Add S-entrance DATE;alter table Student alter column Sage int;-- 若选择restrict:则该表的删除是有限制条件的。欲删除的基本表不能被其他表的--- 约束所引用（如CHECK,FOREIGN等约束），不能有视图，不能有触发器--------- (trigger),不能有存储过程或函数等。-- 如果存在这些依赖该表的对象，则此表不能删除。-- 若选择cascade:则该表的删除没有限制条件。在删除其本表的同时，相关的依---- 赖对象，-- 例如视图，都将被一起删除。*/drop table Student cascade;drop table Student restrict;
?
?
?
?
二：数据查询：
?
-- 表单查询-- 1:查询指定列select Sno,Sname from Student;-- 2:查询全部列select * from Student;-- 等价于select Sno,Sname,Ssex,Sage,Sdept from Student;-- 3:查询经过计算的值select Sname,2012-Sage from Student;-- 二。选择表中的若干元组-- 1:消除取值重复的行-- select distinct Sno from Sc;-- 2:查询满足条件的元组-- 2.1比较大小-- select Sname from student where Sdept='CS';-- select Sname,Sage from student where Sage<20;select distinct Sno from sc where Grade<60;-- 2.2 确定范围select Sname,Sdept,Sage from student where Sage between 20 and 23;select Sname,Ssex from student where Sdept in('CS','MA','IS');-- 2.3 字符匹配select * from student where Sno like '200215121';select * from student where Sname like '刘%';select * from student where Sname like '欧阳__';-- 2.4 涉及空值的查询select Sno,Cno from Sc where Grade is null;select Sno,Cno from Sc where Grade is not null;-- 2.5 多重条件查询select Sname from student where Sdept='CS' and Sage<20;select Sname from student where Sdept='CS' or Sdept='MA' or Sdept='IS'-- 三，order by 子句select Sno,Grade from Sc where Cno='3' order by Grade desc;select * from student order by Sdept,Sage desc;-- 四，聚集函数select count(*) from student;select count(distinct Sno) from sc;select avg(Grade) from sc where Cno='1';select max(Grade) from sc where Cno='1';select sum(Ccredit) from sc,course where Sno='200215121' and sc.Cno=course.Cno;-- 五，group by 子句-- 求各个课程号及相应的选课人数select Cno,count(Sno) from sc group by Cno;-- 查询选修了3门以上课程的学生学号select Sno from sc group by Sno having count(*)>3;-- 连接查询-- 一。等值与非等值连接查询-- 查询每个学生及其选修课程的情况。select student.*,sc.* from student,sc where student.Sno=sc.Sno;-- 外连接-- 在通常的连接操作中，只有满足连接条件的元组才能作为结果输出。上例中的结果-- 中没有其他两个学生的信息，原因在于他们没有选课 。-- 有时想以student表为主体列出每个学生的基本情况及其选课情况。若某个学生没有选课，-- 仍把舍弃的student元组保存在结果关系中，而在sc表的属性上填空值(null),这时就需要-- 使用外连接。select student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade from student left join sc on(Student.Sno=sc.Sno) where student.Sage<20;select student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade from student right join sc on(Student.Sno=sc.Sno) where student.Sage<20;-- 四、复合条件连接select student.Sno,Sname from student,sc where student.Sno=sc.Sno and sc.Cno='2' and sc.Grade<90;-- 2.4.3 嵌套查询-- 查询与‘刘晨’在同一系学习的学生。select Sno,Sname,Sdept from student where Sdept in(select Sdept from student where Sname='刘晨');-- 带有比较动算符的子查询select Sno,Cno from sc x where Grade>=(select avg(Grade) from sc y where y.Sno=x.Sno);-- 四’带有exists谓词的子查询-- 查询所有选修了1号课程的学生姓名select Sname from student where exists (select * from sc where Sno=student.Sno and Cno='1');-- 查询选修了全部课程的学生姓名select Sname from student where not exists(select * from course where not exists (select * from sc where Sno=student.Sno and Cno=course.Cno));-- 集合查询-- 集合操作主要包括并操作UNION、交操作INTERSECT和差操作EXCEPT。select * from student where Sdept='CS' union select * from student where Sage<=19;??
?
三：数据更新
?
-- 3.5 数据更新-- 1:数据更新-- insert into student values('200215128','陈冬','男','IS',18);-- 2:修改数据-- update student set Sage=23 where Sno='200215121';-- 3:删除数据delete from student where Sno='200215128';?
?