关系数据库标准数据库SQL

创建表：

create table student
(sno char(9) primary key,
sname char(20) unique,
ssex char(2),
sage smallint,
sdept char(20)
);
create table course
(cno char(4) primary key,
cname char(40) not null,
cpno char(4),
ccredit smallint,
foreign key(cpno)references course(cno) 
);
create table sc
(sno char(9),
cno char(4),
grade smallint,
primary key(sno,cno),
foreign key(sno)references student(sno),
foreign key(cno)references course(cno)
);

 

数据查询：

• 单表查询

--1.选择表中的若干列
--(1)查询指定列
--eg3.16:查询全体学生的学号和姓名
select sno,sname from student

--eg3.17:查询全体学生的姓名、学号、所在系
select sname,sno,sdept from student

--(2)查询全部列
--eg3.18:查询全体学生的详细记录
select* from student--等价于select sno,sname,ssex,sage,sdept from student

--(3)查询经过计算的值
--eg3.19:查询全体学生的姓名及出生年份
select sname,2014-sage from student

--eg3.20:查询全体学生的姓名、出生年份、所在院系，要求用小写字母表示系名
select sname,'year of birth:',2014-sage,lower(sdept) from student
select sname NAME,'year of birth:'BIRTH,2014-sage BIRTHDAY,lower(sdept)DEPARTMENT from student--指定别名

--2.选择表中的若干元组
--(1)消除取值重复的行
--eg3.21:查询选修了课程的学生学号
select sno from sc
select distinct sno from sc
select all sno from sc

--(2)查询满足条件的元组
--①比较大小
--eg3.22:查询计算机科学系全体学生的名单
select sname from student where sdept='CS'

--eg3.22:查询所有年龄在20岁以下的学生姓名及其年龄
select sname,sage from student where sage<20

--eg3.24:查询考试成绩不及格的学生的学号
select distinct sno from sc where grade<60

--②确定范围
--eg3.25:查询年龄在20~23岁（包括20和23）之间的学生的姓名、系别和年龄
select sname,sdept,sage from student where sage between 20 and 23

--eg3.26:查询年龄不在20~23岁之间的学生的姓名、系别和年龄
select sname,sdept,sage from student where sage not between 20 and 23

--③确定集合
--eg3.27:查询计算机科学系（CS）、数学系（MA）、和信息系（IS）学生的姓名和性别
select sname,ssex from student where sdept in('cs','ma','is')

--eg3.28:查询既不是计算机科学系、数学系，也不是信息系的学生的姓名和性别
select sname,ssex from student where sdept not in('cs','ma','is')

--④字符匹配（通配符%代表任意长度的字符串，可以为0；通配符_代表任意单个字符）
--eg3.29:查询学号为201215121的学生的详细情况
select* from student where sno like '201215121'--等价于sno='201215121'

--eg3.30:查询所有姓刘的学生的姓名、学号和性别
select sname,sno,ssex from student where sname like '刘%'

--eg3.31:查询姓“欧阳”且全名为三个汉字的学生的姓名
select sname from student where sname='欧阳_'

--eg3.32:查询名字中第二个字为“阳”的学生的姓名和学号
select sname,sno from student where sname like '_阳%'

--eg3.33:查询所有不姓刘的学生的姓名、学号和性别
select sname,sno,ssex from student where sname not like '刘%'

--eg3.34:查询DB_Design课程的课程号和学分
select cno,ccredit from course where cname like 'DB\_Design' ESCAPE'\'--换码字符

--eg3.35:查询以“DB_”开头且倒数第三个字符为i的课程的详细情况
select * from course where cname like 'DB\_%i__' ESCAPE'\'--换码字符&通配符

--⑤涉及空值的查询
--eg3.36:查询缺少成绩的学生的学号和课程号
select sno,cno from sc where grade is NULL--确定值才可以用‘=’否则只能用is

--eg3.37:查所有有成绩的学生学号和课程号
select sno,cno from sc where grade is not NULL

--⑥多重条件查询
--eg3.38:查询计算机科学系年龄在20岁以下的学生姓名
select sname from student where sdept='cs'and sage<20

--(3)order by 语句
--eg3.39:查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列
select sno,grade from sc where cno='3'
order by grade desc

--eg3.40:查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列
select * from student 
order by sdept asc,sage desc

--(4)聚集函数(计数，总和，平均值，最大值，最小值)
--eg3.41:查询学生总人数
select count(*) from student 

--eg3.42:查询选修了课程的学生人数
select count(distinct sno) from sc--存在一个学生选择多门课程，故去重

--eg3.43:计算选修1号课程的学生平均成绩
select avg(grade) from sc where cno='1'

--eg3.44:查询选修1号课程的学生最高分数
select max(grade) from sc where cno='1'

--eg3.45:查询学生201215012选修课程的总学分数
select sum(ccredit) from sc,course 
where sc.cno=course.cno and sno='201215012'

--(4)group by子句
--eg3.46:求各个课程号及相应的选课人数
select cno,count(sno) from sc 
group by cno--选择谁按谁分组

--eg3.47:查询选修了三门以上课程的学生的学号
select sno from sc
group by sno
having count(*)>3--having语句作用于组，where语句作用于基本表或视图

--eg3.48:查询平均成绩大于等于90分的学生学号和平均成绩
select sno,avg(grade) from sc
group by sno--选择谁按谁分组
having avg(grade)>=90

• 连接查询

--1.等值与非等值连接查询
--eg3.49:查询每个学生及其选修课程的情况
select student.*,sc.* 
from student,sc
where student.sno=sc.sno

--左外连接改写：
select student.*,sc.* 
from student left outer join sc on(student.sno=sc.sno)

--eg3.50:用自然连接查询每个学生及其选修课程的情况--去掉重复列即为自然连接
select student.sno,sname,ssex,sage,sdept,cno,grade
from student,sc
where student.sno=sc.sno

--eg3.51:查询选修2号课程且成绩在90分以上的所有学生的学号和姓名
select student.sno,sname from sc,student 
where sc.sno=student.sno and cno='2' and grade>90

--2.自身连接
--eg3.52:查询每一门课的间接先修课（即先修课的先修课）
select first.cno,second.cpno
from course first,course second
where first.cpno=second.cno

--3.外连接
--eg3.53:
select student.sno,sname,ssex,sage,sdept,cno,grade
from student left outer join sc on(student.sno=sc.sno)

select student.sno,sname,ssex,sage,sdept,cno,grade
from student left outer join sc using(sno)--去掉重复值

--改写eg3.49:查询每个学生及其选修课程的情况
select student.*,sc.* 
from student left outer join sc on(student.sno=sc.sno)

--4.多表连接
--eg3.54:查询每个学生的学号、姓名、选修的课程名及成绩
select student.sno,sname,cname,grade 
from student,sc,course
where student.sno=sc.sno and sc.cno=course.cno

• 嵌套查询

--1.带有in谓词的子查询
--eg3.55:查询与“刘晨”在同一个系学习的学生
select sno,sname,sdept 
from student
where sdept in --因为一个学生只可能选择一个系故可以用‘=’
	(select sdept  --不相关子查询：子查询不依赖父查询
	 from student 
	 where sname='刘晨')

select s1.sno,s1.sname,s1.sdept  --自身连接的解法
from student s1,student s2
where s1.sdept=s2.sdept and s2.name='刘晨'

--eg3.56:查询选修了课程名为“信息系统”的学生学号和姓名
select sno,sname
from student 
where sno in
     (select sno 
      from sc 
      where cno in
	   (select cno 
	    from course 
	    where cname='信息系统'
	   )
     );

select student.sno,sname--尽可能用连接运算（目前商用关系对嵌套查询的优化不够完善）
from student,sc,course
where student.sno=sc.sno and 
      sc.cno=course.cno  and 
      course.cname='信息系统';

--2.带有比较运算符的子查询
--eg3.57:225出每个学生超过他自己选修课程平均成绩的课程号 *****
select sno,cno --结果的属性有两列，主码学号不可少
from sc x
where grade>=(select avg(grade) --相关子查询的例子——反复查询
	      from sc y 
	      where y.sno=x.sno);

查询步骤：·从外层sc x取出一个元祖将其sno=‘’赋值给内层
         ·执行内层查询，算出avg代替内层查询，得到外层查询
         ·执行这个查询得到当前sno符合条件的元组(重复1~3)
		  
--3.带有any(some)或all谓词的子查询：//返回单值用比较运算符，返回多值用any(some)或all + 比较运算符
--eg3.58:查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄
select sname,sage
from student 
where sdept!='cs'
and sage<any(select sage 
	     from  student
             where sdept='cs')

select sname,sage
from student 
where sdept!='cs'
and sage<(select max(sage) --使用聚合函数来实现
	  from student 
	  where sdept='cs')

--eg3.59:查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄
select sname,sage
from student
where sage<all
          (select sage
           from student
           where sdept='cs')
   and sdept!='cs'

select sname,sage
from student
where sdept!='cs'
and sage<
        (select min(sage)--聚集函数
	 from student
	 where sdept='cs')

--4.带有exist谓词的子查询
--eg3.60:查询所有选修了1号课程的学生姓名
select sname 
from student 
where exists --返回真值,有时是高效的方法
     (select *
      from sc
      where sno=student.sno and cno='1');

查询步骤：取外层一个元组，根据与内层查询相关的属性处理内层，返回真假值，取外层sname放入结果表，再处理下一元组

--eg3.61:查询没有选修1号课程的学生姓名
select sname 
from student 
where not exists
	 (select *
	  from sc
	  where sno=student.sno and cno='1');

--eg3.62:查询选修了全部课程的学生姓名
select sname
from student
where not exists --没有一门课是他不选修的(P59)
	 (select *
	  from course 
	  where not exists
		(select *
		 from sc
		 where sno=student.sno and cno=course.cno));

--eg3.63:查询至少选修了学生201215122选修的全部课程的学生号码
select distinct sno 
from sc scx
where not exists --不存在这样的课程y，学生201215122选修了y而学生x没有选修
      (select *
       from sc scy
       where scy.sno='201215122'and
	     not exists 
	     (select *
	      from sc scz
	      where scz.sno=scx.sno and
		    scz.cno=scy.cno))

• 集合查询

--集合查询
--eg3.64:查询计算机科学系的学生及年龄不大于19岁的学生
select *
from student 
where sdept='cs'
union
select *
from student
where sage<=19

--eg3.65:查询选修了课程1的学生或者选修了课程2的学生
select sno
from sc 
where cno='1'
union
select sno
from sc
where cno='2' 

--eg3.66:查询计算机科学系的学生与年龄不大于19岁的学生的交集
select *
from student 
where sdept='cs'
intersect
select *
from student
where sage<=19

--eg3.67:查询既选修了课程1又选修了课程2的学生
select *
from sc 
where cno='1'
intersect
select *
from sc
where cno='2' 

select sno  
from sc
where cno='1'and sno in
	         (select sno 
		  from sc
		  where cno='2')

--eg3.68:查询计算机科学系的学生与年龄不大于19岁学生的差集
select *
from student
where sdept='cs'
except 
select *
from student
where sage<=19

select * from student where sdept='cs'and sage>19--等价于

• 基于派生表的查询

--改写eg3.57:找出每个学生超过他自己选修课程平均成绩的课程号 *****
select sno,cno
from sc,(select sno,avg(grade) from sc group by sno) as avg_sc(avg_sno,avg_grade)
where sc.sno=avg_sc.avg_sno and sc.grade>=avg_sc.avg_grade  

--改写eg3.60:查询所有选修了1号课程的学生姓名
select sname 
from student,(select sno from sc where cno='1') as sc1
where student.sno=sc1.sno

 

数据更新

• 插入数据

--1.插入元组
--eg3.69:将一个新学生元组（学号：201215128，姓名：陈冬，性别：男，所在系：is，年龄：18岁）插入student表中
insert 
into student(sno,sname,ssex,sdept,sage)
values('201215128','陈冬','男','is',18);

--eg3.70:将学生张成民的信息插入到student表中
insert 
into student --次序相同
values('201215126','张成民','男','18','cs');

--eg3.71:插入一条选课记录
insert 
into sc
values('201215128','1',NULL)

--2.插入子查询结果
--eg3.72:对于每一个系，求学生的平均年龄，并把结果存入数据库 
create table dept_age
	   (sdept char(15)
	    avg_age smallint);
insert 
into dept_age(sdept,avg_age)
select sdept,avg(sage)
from student 
group by sdept

• 修改数据

--1.修改某一个元组的值
--eg3.73:将学生201215121的年龄改为22岁
update student 
set sage=22
where sno='201215121'

--2.修改多个元组的值
--eg3.74:将所有学生的年龄增加1岁
update student 
set sage=sage+1

--3.带子查询的修改语句
--eg:3.75:将计算机科学系全体学生的成绩置零
update sc
set grade=0
where sno in
	(select sno
	 from student 
	 where sdept='cs');

• 删除数据

--1.删除某一个元组的值
--eg3.76:删除学号为201215128的学生记录
delete 
from student
where sno='201215128'

--2.删除多个元组的值
--eg3.77:删除所有学生的选课记录 
delete 
from sc

--3.带子查询的删除语句
--eg3.78:删除计算机科学系所有学生的选课记录
delete 
from sc
where sno in
	 (select sno 
	  from student 
	  where sdept='cs')

 

空值的处理

--1.空值的产生
--eg3.79:向sc中插入一个元组，学生号是“201215126”
insert into sc(sno,cno,grade)
	   values('201215126','1',NULL)

--eg3.80:将student表中学生号为“201215200”的学生所属的系改为空值     
update student
set sdept=NULL
where sno='201215200'

--2.空值的判断
--eg3.81:从student表中找出漏填了数据的学生信息
select *
from student 
where sname is null or ssex is null or sage is null or sdept is null

--3.空值的约束条件

--4.空值的算术运算、比较运算和逻辑运算
--eg3.82:找出选修1号课程的不及格的学生
select sno
from sc 
where grade<60 and cno='1'--缺考使条件grade<60的值为unknown

--eg3.83:选出选修1号课程的不及格的学生以及缺考的学生
select sno
from sc
where grade<60 and cno='1'
union
select sno
from sc
where grade is null and cno='1'

select sno
from sc
where cno='1' and (grade<60 or grade is null) 

视图

定义视图

• 建立视图

--eg3.84:建立信息系学生的视图
create view IS_Student --省略了视图中的列名，隐含了由子查询中的三个列名组成
as
select sno,sname,sage
from student
where sdept='is'

--eg3.85:建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生
create view IS_student
as 
select sno,sname,sage
from student 
where sdept='is'
with check option --行列子集的视图（只是去掉了基本表中的某些列或某些行）

--eg3.86:建立信息系选修了1号课程的学生的视图（包括学号、姓名、成绩）
create view IS_s1(sno,sname,grade)
as
select student.sno,sname,grade
from  student,sc
where sdept='is' and
	  student.sno=sc.sno and 
	  sc.cno=1

--eg3.87:建立信息系选修了1号课程且成绩在90分以上的学生的视图
create view IS_s2
as
select sno,sname,grade
from IS_s1 
where grade>=90

--eg3.88:定义一个反映学生出生年份的视图
create view BT_S(sno,sname,sbirth)--带表达式的视图
as
select sno,sname,2014-sage
from student

--eg3.89:将学生的学号及平均成绩定义为一个视图
create view S_G(sno,gavg)
as 
select sno,avg(grade)
from sc
group by sno --分组视图

--eg3.90:将student表中所有女生记录定义为一个视图
create view F_Student(F_sno,name,sex,age,sdept)
as
select *
from student
where ssex='女'

• 删除视图

--eg3.91:删除视图BT_S和视图IS_S1
drop view BT_S;
drop view IS_S1 cascade;--删除视图IS_S1和它导出的所有视图

查询视图

--eg3.92:在信息系学生的视图中找出年龄小于20岁的学生
select sno,sage --视图消解:将视图定义中的子查询与用户查询结合起来转换成等价的对基本表的查询
from IS_Student
where sage<20

--eg3.93:查询选修了一号课程的信息系学生
select IS_Student.sno,sname
from IS_Student,sc
where IS_Student.sno=sc.sno and sc.cno='1'

--eg3.94:在S_G视图（eg3.89中定义的视图）中查询平均成绩在90分以上的学生学号和平均成绩，语句为
select * 
from S_G 
where grade>=90

select sno,avg(grade)--等价于
from sc
group by sno
having avg(grade)>=90

select *
from(select sno,avg(grade)
     from sc
     group by sno)as S_G(sno,gavg)--派生表是临时定义，视图是永久性的
where grade>=90

更新视图

--eg3.95:将信息系学生视图IS_Student中学号为‘201215122’的学生姓名改为‘刘辰’
update IS_Student
set sname='刘辰'
where sno='201215122'

--eg3.96:向信息系学生视图IS_Student中插入一个新的学生记录，其中学号为‘201215129’，姓名为‘赵新’，年龄20岁
insert 
into IS_Student
values('201215129','赵新','20')

--eg3.97:删除信息系学生视图IS_Student中学号为‘201215129’的记录
delete 
from IS_Student
where sno='201215129'

--利用视图清晰表达查询的样例
create view vmgrade
as 
select sno,max(grade)mgrade
from sc
group by sno

select sc.sno,cno
from sc,vmgrade
where sc.sno=vmgrade.sno and sc.grade=vmgrade.mgrade