SQL 笔记

学生——课程数据库

 

* 学生表：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';