数据库二：数据操作语句

查询数据是数据库的最基本和最重要的语句之一，其功能是从数据库中检索满足条件的数据。查询的数据可以来自一张表，也可以来自多张表或者视图，查询的结果是由0行(没有满足条件的数据)或多行记录组成的一个一个记录集合，并允许选择一个或多个字段作为输出字段。
所有查询的表都是学生表，课程表，学生选课表，来自数据库一：创建表。

1.查询数据的基本结构(数据查询)

select：指定输出的字段(必须)
from：指定数据来源(必须)
where：指定数据的选择条件
group by：用于对检索到的记录进行分组
having：用于指定组的选择条件
order by：用于对查询的结果进行排序

2.单表查询(数据查询)

2.1选择表中若干列

查询指定的列

#查询全体学生的学号与姓名

select sno,sname drom student

查询全部列

#查询全体学生的详细信息

select sno,sname,ssex,sage,sdept from student
select * from student

查询经过计算的列

select查询的目标列名序列可以是表中存在的属性，也可以是表达式、常量或者函数

select sname,2015-sage from student
select sname,'出生年份',2015-sage from student

讲过计算的列、常量列的显示结果没有列名，通过为列起列名的方法可以指定或改变查询结果显示的列名，这个列名就称为列别名。

select sname AS 姓名,'出生年份' AS 常量列,2015-sage AS 年份 from student

2.2选择表中若干组

消除取值相同的行

本来在数据库表中并不存在取值全部相同的元组，但在进行对列的选择后，就有可能在查询结果中出现取值完全相同的行。
distinct关键字可以去掉查询结果中的重复数据。

#在选课表中查询哪些学生选了课，列出学生的学号
select distinct sno from sc

查询满足条件的元组

查询条件	谓词
比较	= > < >= <= <>(!=)
确定范围	between and；not between and
确定集合	in not in
字符匹配	like not like
空值	is null；is not null
多重条件	and or

比较大小

#查询计算机系全体学生的姓名
select sname from student where sdept='计算机系'

#查询所有年龄在20岁以下的学生的姓名和年龄
select sname,sage from sc where sage < '20'

#查询考试成绩有不及格的学生的学号
select distinct sno from sc where grade < 60

确定范围
between 下限值 and 上限值的含义：当值大于下限值且小于下限值的时候结果为TRUE.

#查询年龄在20~30岁之间的姓名、所在系和年龄
select sname,sdept,sage from student where sage between 20 and 30
select sname,sdept,sage from student where sage >= 20 and sage <=30

确定集合

in的含义：当列中的值与in中的某个常量值相等时结果为TRUE。

#查询信息系、数学系和计算机系学生的姓名和性别
select sname,ssex from student where sdept in ('信息系','数学系','计算机系')

字符串匹配

_：匹配任意一个字符
%：匹配0个或多个字符
[]：匹配其中的任意一个字符
[^]：不匹配其中的任意一个字符

#查询行张、李、刘姓的学生的详细信息
select * from student where sname like '[张李刘]%'

设计空值的查询

空值(null)在数据库中有特殊含义，他代表不确定的值。
判断取值为空：is null
判断取值不为空：is not null

#查询没有考试成绩的学生的学号和相应的课程号
select sno,cno from sc where grade is null

多重条件的查询
在where语句中and表示必须满足所有表达式的条件结果才为TRUE，or表示只要满足其中的一个就为TRUE。

#查询计算机系和信息系年龄大于等于20岁的学生姓名、所在系和年龄
select sname,sdept,sage from where (sdept = '计算机系' or sdept = '信息系') and sage >= 20

2.3对查询结果进行排序

order by：可以将查询结果按用户指定列的值进行排序
ASC：升序
DESC：降序

查询选修了c002号课程的学生的学号及其成绩 查询结果按成绩降序排列
select sno,grade from sC where cno = 'c002' order by grade ASC

2.4使用聚合函数汇总数据

聚合函数也称集合函数或统计函数、聚集函数，起作用是对一组值进行计算并返回一个单值。聚合函数不能出现在where语句中。

count(*)：统计表中元组的个数 即行数
count([distinct]<列名>)：统计本列非空值个数 distinct表示不包括列的重复值
sum(列名)：计算列值总和
avg：计算平均值
max：计算最大值
min：计算最小值

#统计学生总人数
select count(*) AS 学生人数 from student

#统计选修了课程的学生人数
select count(distinct sno) as 选课人数 from sc

#计算1512101学生的选课门数和考试总成绩
select sum(grade) as 总成绩,count(*) as 选课门数 from sc where sno =‘1512101’

#查询c001课程的考试最高分和最低分 
select max(grade) as 考试最高分,min(grade) as 考试最低分 from sc where cno=‘c001’

#查询1512101学生的选课门数、已考试课程门数以及考试最高分最低分和平均分
select count(*) as 选课门数,count(grade) as 考试课程门数,max(grade) as 最高分,min(grade) as 最低分,avg(平均分) as 平均分 from sc where sno=‘1512101’

2.5对查询结果进行分组统计

group by可将统计控制在组一级，分组目的是细化聚合函数的作用对象，如果使用了分组语句，则查询列表中的每个列必须要么是分组依据列即再group by后边的列，要么是聚合函数。分组依据列不能是text、ntext、image类型。
使用group by子句时，如果在select语句查询的列表中包含聚合函数，则是针对每个组计算出一个汇总值，从而实现对查询结果的分组统计。
group by后跟的列必须是已经存在的列，不能是as子句指派的列。

group by子句

#统计每门课程的选课人数、列出课程号和选课人数
#不用加distinct因为一个人不可能选一门课两次
select cno as 课程号,count(sno) as 选课人数 from sc group by cno

#统计每个系的女生人数
select sdept,count(*) 女生人数 from student where ssex=‘女’ group by sdept

#统计每个系每个系的男生人数和女生人数，以及男生最大年龄和女生最大年龄 结婚按系名排序
select sdept,ssex,count(*) 人数,max(sage) 最大年龄 from student group by sdept,ssex order by sdept

使用having子句

having子句用于对分组后的结果再进行筛选，功能类似where子句，但它用于组而不是单个记录。在having子句中可以使用聚合函数对分组后的数据进行筛选，但在where语句中却不能。

#查询选修了三门以上课程的学生的学号和选课门数
#先利用group by进行分组 然后计算 最后筛选>3的
select sno,count(*) from sc group by sno having count(*)>3

#统计每个系的男生，只列出男生数目大于等于2的系
#先执行where语句列出所有男生→再执行group by将相同系放在一个分组→然后执行聚合函数计算count→最后执行having子句列出>2的系
select sdept count(*) 人数 from student where ssex ='男' group by sdept having count(*)>2

where：用来筛选from子句中指定的数据源所产生的行数据
group by：用来对where子句筛选后的结果进行分组
having：对分组后的结果数据再进行筛选

3.多表连接查询(数据查询)

若一个查询涉及两个或两个以上的表，则称为多表查询。

内连接
使用内连接时，如果两个表的相关字段满足连接条件，则从这两个表中提取数据并组合成新的记录。

• theta连接方式：非ANSI中 连接操作使用where
• ANIS连接方式：AHSI中 连接操作使用join子句

格式：
from 表一 inner join 表二 on 连接条件(inner可省略)
表名1.列名 比较运算符 表名2.列名

当比较运算符为=时，成为等值连接，其他称为非等值连接。
DBMS执行连接操作的过程是：首先取表一中的第一个元组，然后从头开始扫描表二，逐一查找满足连接条件的元组，找到后就将表一中的第一个元组与该元组拼接起来，形成结果表中的一个元组，表二全部查完后，再取第二个以此类推进行查询。

#查询每个学生及其选课的详细信息
select * from student inner join sc on student.sno=sc.sno
select student.sno,sname,ssex,sage,sdept,cno,grade from student join sc on student.sno=sc.sno

#查询信息系选修了计算机文化学课程的学生的成绩，要求列出学生姓名、课程名和成绩
select sname,cname,grade from student s join sc on s.sno=sc.sno join course c on c.cno=sc.cno where sdept='计算机系' and cname ='计算机文化课'

#用有分组的多表连接查询，统计每个系的学生的考试平均成绩
select sdept,avg(grade) as averagegrade from student s join sc on s.sno=sc.sno group by sdept

#有分组和行选择条件的多表连接查询，统计计算机系每门课程的选课人数、平均成绩、最高成绩和最低成绩
select cno,count(*) 选课人数 avg(grade) 平均成绩 max(grade) 最高成绩 min(grade) 最低成绩 from student s join sc on s.sno=sc.sno where sdept='计算机系' group by cno

自连接

自连接是一种特殊的内连接，它是指互相连接的表在物理上为同一张表，但在逻辑上将其看成两张表。要让物理上的一张表在逻辑上成为两个表，必须通过为表取别名的方法实现。

#查询与刘晨在同一个系学习的学生的姓名和所在系
#s1查询条件表 s2结果表
select s2.sname,s2.sdept from student s1 join student s2 on s1.sdept=s2.sdept where s1.name = '刘晨' and s2.sname != '刘晨'

外连接

在内连接操作中，只有满足连接条件的元组才能作为结果输出，外连接是只限制一张表中的数据必须满足条件，而另一张表中的数据可以不用满足连接条件。

格式：from 表一 left | right outer join 表二 on 连接条件

• left outer：左外连接 限制表二的数据必须满足连接条件 不管表一是否满足条件都输出
• right outer：右外连接 限制表一的数据必须满足条件 不管表二是否满足条件都输出

#查询学生的选课情况，包括了课程的学生和没有选课程的学生
select student.sno,sname,cno,grade from student left outer join sc on student.sno=sc.sno

使用TOP限制结果集

如果只希望列出结果集中的前几行结果，就可以使用TOP。
TOP写在select单词的后面，如果有distinct则在其后面，查询列表的前面。

• n：非负数
• top n：前n行
• top n percent：查询结果的前n%行数据
• wish ties：表示包括并列的结果

select top 3 sname,sage,sdept from student order by sage desc

子查询
如果一个select语句嵌套在一个select、insert、update或delete语句中，则称为子查询或内层查询，包含子查询的语句称为主查询或外层查询。

使用子查询进行基于集合的测试

where 表达式 in (子查询)

select sno,sname,sdept from student where sdept in (select sdept from student where sname='刘晨'

select s2.sno,s2.sname,s2.sdept from student s1 join student s2 on s1.sno=s2.sno where s1.sdept ='计算机系'

使用子查询进行比较测试

#查询选了c005课程且成绩高于此课程的平均成绩的学生的学号和成绩

#查询c005课程的平均成绩 73
select avg(grade) from sc where cno='c005'
#再查询大于73的学生
select sno,grade from sc where cno = 'c005' and grade >73

select sno,grade from sc where cno='c005' and grade > (select avg(grade) from student where cno='c005'

使用子查询进行存在性测试

exists：当子查询中有满足条件的数据时，exists返回真值，否则返回假值；
not exists：当子查询中有满足条件的数据时，not exists返回假值，否则返回真值。
其先执行外层查询再执行内层查询。由外层查询的值决定内层查询的结果，内层查询的执行次数由外层查询的结果决定。

#查询选修了c001课程的学生的姓名
select sname from student where exists (select * from sc where sno =student.sno and cno ='c005')

4.数据更改

4.1插入数据

insert into 表名 列名表 values 值列表

• 列表名必须是已有的列表名
• 值列表中的值与列表中的列按位置顺序对应 数据类型必须一致
• 若表名后面没有指明列名 则新插入记录的值的顺序必须与表中列的定义顺序一致 且每一个列均有值 可以为空

select into student values (‘1521104’,’陈冬’,’男’,18,’信息系’)

select into sc(sno,cno) values(‘1521104’,’c001’)

4.2更新数据

update 表名 set 列名 =表达式 where 更新条件
无条件更新

update student set sage=sage+1

有条件更新

• 基于本表条件的更新 即要更新的记录和更新记录在同一张表中
• 基于其他表的更新 即要更新的条件来自于另一张表

基于其他表的更新可以用两种方法：

1. 使用多表连接
2. 使用子查询

#基于本表的更新
update student set sage =21 where sno=‘1212101’

#基于其他表的更新
update sc set grade =grade+5 where sno in (select sno from student where sdept =‘计算机系’)
select sc set grade =grade+5 from sc join student on sc.sno=student.sno where sdept =‘计算机系’

4.3删除数据

无条件删除

无条件删除是删除表中所有数据，但保留表的结构。

delete from sc

有条件删除
和更改数据一样，分为基于本表的删除和基于其他表的删除。其中基于其他表的删除也有两种方式和上面一样。

delete from sc where grade<60

delete from sc where grade<60 and in (select sno from student where sdept =‘计算机系’)

delete from sc join student on sc.sno=student.sno where grade <60 and sdept =‘计算机系’

5.数据查询拓展

5.1将查询结果保存到新表中

#统计每个系的学生人数 并将结果保存到永久表dept_cnt中
select sdept count(*) as 人数 into dept_cnt from student group by sdept

5.2case 表达式

case表达式是一种多分支表达式，他可以根据条件列表的值返回多个可能的结果表达式中的一个。

简单case表达式
简单case表达式将一个测试表达式和一组简单表达式进行比较，如果某个简单表达式与测试表达式值相等，则返回相应的结果表达式的值。

#查询选了Java课程的学生的学号 姓名 所在系和成绩 并对所在系做以下处理
#所在系为计算机的时候 查询结果显示CS
#所在系为信息系的时候 查新结果为IM
#所在系为数学系的时候 查询结果为MA

select s.sno 学号,s.sname 姓名,
 case sdept
  when '计算机系' then 'CS'
  when '信息系' then 'IM'
  when '数学系' then 'MA'
 end as 所在系,grade 成绩
 from student s join sc on s.sno=sc.sno
 join course on c.cno=sc.cno
 where cname ='Java'

探索表达式

简单表达式只能进行一个值的比较，如果涉及多个值就要用探索表达式。

#查询c001课程的考试情况 列出学号 成绩以及成绩等级 对成绩等级的处理如下
#成绩大于等于90 等级为优
#成绩在80到89 等级为良
#成绩在70到79 等级为中
#成绩字60到69 等级为及格
#成绩小于60 等级为不合格

select sno,grade,case
	when grade >90 then '优秀'
	when grade between 80 and 89 then '良'
	when grade between 70 and 79 then '中'
	when grade between 60 and 69 then '及格'
	when grade <60 then '不合格'
 end as 等级
 from sc where cno ='c001'

case语句用在更新语句中：

#修改全体学生的Java考试成绩，修改规则如下
#数学系学生加10
#信息系学生加5
#其他系学生不变

update sc set grade=grade+
(
	case s.sdept
	when '数学系' then 10
	when '信息系' then 5
	else 0
	end
)
from student s join sc on sc.sno=s.sno
join course c on sc.cno=c.cno
where cname='Java'

5.4查询结果的并 交 差运算

并运算

并运算可以将两个或多个查询语句的结果合并为一个结果集，通过union云南算符实现。
使用时需要注意：

• 各select语句中查询列的个数必须相同，而且对应列的语句应该相同。
• 各select语句中每个列的数据类型必须与其他查询语句中对应列的数据类型是隐式兼容的，即他们可以隐式转换就可。
• 合并后的结果集将采用第一个select语句的列标题。
• 如果对查询的结果进行排序，则order by子句应该下载最后一个查询语句之后，且排序的依据列应该是第一个查询语句中出现的列名。

#查询李勇和刘晨所选的全部课程
select c.cname,c.sdept from student s join sc on sc.sno=s.sno
join course c on sc.cno=c.cno where sname ='李勇'
union
select c.cname,c.sdept from student s join sc on sc.sno=s.sno
join course c on sc.cno=c.cno where sname ='刘晨'

课后习题的命令语句汇总

create table Student(
	Sno char(7) primary key,
	Sname char(10) not null,
	Ssex char(2),
	Sage tinyint,
	Sdept char(20)
)

create table Course(
  Cno char(6) not null,
  Cname char(20) not null,
  Credit tinyint,
  Semester tinyint,
  primary key(Cno)
)

create table SC(
  Sno char(7) not null,
  Cno char(6) not null,
  Grade smallint,
  primary key(Sno,Cno),
  foreign key(Sno) references Student(Sno),
  foreign key(Cno) references Course(Cno)
)

#1查询学生课表中的全部数据
select * from sc

#2查询计算机系的学生的姓名 年龄
select sname,sage from student where sdept = '计算机系'

#3查询成绩在70~80之间的学生的学号 课程号和成绩
select sno,cno,grade from sc where grade between 70 and 80

#4查询计算机系年龄在18~20之间且性别为男的学生的姓名 年龄
select sname,sage from student where ssex = '男' and sage between 18 and 20

#5查询课程为c001的课程的最高的分数
select max(grade) as 最高分 from sc where cno ='c001'

#6查询计算机系学生的最大年龄和最小年龄
select max(sage) as 最大年龄,min(sage) as 最小年龄 from student where sdept='计算机系'

#7统计每个系的学生人数
select sdept,count(*) as 学生人数 from student group by sdept

#8统计每门课程的选课人数和考试最高分
select cno,count(*) as 人数,max(grade) as 最高分 from sc group by cno

#9统计每个学生的选课门数和考试总成绩 并按选课门数升序显示结果
select sno,count(*) as 选课门数,sum(grade) as 考试总成绩 from sc group by sno order by 选课门数 

#10查询总成绩超过200分的学生 要求列出学号 总成绩
select sno,sum(grade) as 总成绩 from sc group by sno having sum(grade)>200

#11查询选修了c002号课程的学生的姓名和所在系
select sname,sdept 
from student where sno in (select sno from sc where cno='c002')

select  student.sname,student.sdept from student  join sc 
on student.sno=sc.sno where sc.cno='c002'

#12查询成绩80分以上的学生的姓名 课程号和成绩 并按照成绩降序排列结果
select student.sname,sc.sno,sc.grade from student 
join sc on student.sno=sc.sno
where sc.grade>80 order by sc.grade desc

#13查询哪些学生没有选课 要求列出学号 姓名和所在系
select sno,sname,sdept from student where
sno not in (select distinct sno from sc)

select student.sno,sname,sdept from student
left outer join sc on student.sno=sc.sno
where sc.cno is null

#14查询与Java在统一学期开设的课程的课程名和开课学期
select * from course where Semester in (select Semester from course where cname='Java')

select s1.* from course s1 join course s2
on s1.Semester=s2.Semester
where s1.cname='Java' 

#15查询与李勇年龄相同的学生的姓名 所在系和年龄
select s1.sname,s1.sdept,s1.sage from student s1 join student s2
on s1.sno=s2.sno
where s1.sage ='19'

#16用子查询实现以下查询
#查询选修了c001号课程的学生的姓名和所在系
select sname,sdept from student where sno in (select sno from sc where cno = 'c001')

#查询数学成绩80分以上的学生的姓名 学号 课程号和成绩
select student.sno,sname,cno,grade from student join sc on student.sno = sc.sno
where grade >80

#查询计算机考试成绩最高的学生的姓名
select sname from student where sno in (select max(grade) from sc where sdept='计算机系')

#查询数据结构考试成绩最高的学生的姓名 所在系 性别和成绩
select sname,sdept,ssex,grade from student join sc on student.sno = sc.sno
where sc.sno in (select max(grade) from sc where cno ='c007')

#17查询没有选修Java课程的学生的姓名和所在系
select sname,sdept from student left outer join sc on 
student.sno = sc.sno
where sc.cno != 'c005'

#18查询计算机系没有选课的学生的姓名和性别
select sname,ssex from student 
where student.sno not in (select sno from sc where sdept = '计算机系')

select sname,ssex from student where student.sno not in
(select distinct sno from sc)

select sname,ssex from student where not exists
(select cno from sc where sc.sno=student.sno)

#19创建一个新表 表名为test_t 其结构为(col1 col2 col3)
#并写出插入数据的语句
create table test_t(
	col1 int,
	col2 char(10) not null,
	col3 char(10)
)

insert into test_t(col2) values('17')

#20删除考试成绩低于50分的学生的选课记录
delete from sc where grade<50

#21删除没有人选的课程记录
delete from Course where cno not in (select cno from sc)

delete from course from course left outer join sc 
on course.cno=sc.cno where sno is null

#22删除计算机系Java成绩不及格学生的Java选课记录
delete from sc where
grade<60
and sno in (select sno from student where sdept='计算机系')
and cno in (select cno from course where cname='Java')

delete from sc from sc join student on sc.sno=student.sno
join Course on sc.cno=course.cno where sdept='计算机系'
and cname='Java' and Grade<60

#23将第二学期开设的所有课程的学分增加两分
update course set credit=credit+2 where Semester='2'

#24将Java课程的学分改为3分
update course set credit=3 where cname='Java'

#25将计算机系学生的年龄增加1岁
update student set sage=sage+1 where sdept='计算机系'

#26将信息系学生的计算机文化学课程的考试成绩加5分
update sc set grade=grade+5 where
sc.sno in (select sno from student where sdept='信息系')
and sc.cno in (select cno from Course where cname='计算机文化学')

update sc set grade=grade+5 from sc join student on
sc.Sno=student.sno join course on sc.cno=course.cno 
where sdept='信息系' and cname='计算机文化学'

#27查询每个系年龄大于等于20的学生人数 并将结果保留在一个新永久表Dept_Age中
select count(*) as rs,sdept into dept_age from student where sage>=20 group by sdept

select* from dept_age

#28查询计算机系每个学生的Java考试情况 列出学号 姓名 成绩和成绩情况 列成绩情况显示规则
#成绩大于90为好 成绩在80-89为较好 成绩在70-79位一般 成绩在60-69为较差 成绩小于60为差

select s.sno,s.sname,sc.grade,
case
 when sc.grade>=90 then '好'
 when sc.grade>=80 and sc.grade<=89 then '较好'
 when sc.grade between 70 and 79 then '一般'
 when sc.grade between 60 and 69 then '较差'
 when sc.Grade<60 then '差'
end as  成绩等次
from student s join sc on s.sno=sc.sno join course c on sc.cno=c.cno
where s.Sdept='计算机系' and c.cname='Java' order by sc.grade desc

#29统计每个学生的选课门数(包括没有选课的) 列出学号 选课门数和选课情况 其中选课情况显示规则
#选课门数大于等于6为多 3-5为一般 1-2为偏少 没有选课为未选课

select s.sno,count(*) as 选课门数，
case
 when 选课门数>=6 then '多'
 when 选课门数 between 3 and 5 then '较好'
 when 选课门数 between 1 and 2 then '一般'
 when 选课门数=0 then '未选课'
end as 选课情况
from student s join sc on sc.sno=s.sno
order by s.sno

#30修改全部课程的学分 修改规则如下
#如果是1-2学期开设的课 则学分加5分 3-4学期学分加3分 5-6学期学分加1分 其他学期的课学分不变

update sc set grade =grade+
case semster
 when 1 or 2 then 5
 when 3 or 4 then 3
 when 5 or 6 then 1
 else 0
end
from student s join sc on s.sno =sc.sno join course c o  c.cno =sc.cno

#31查询李勇和王大力所选全部课程 列出课程名 开课学期和学分 不包括重复的结果
#并运算union不包含重复结果 union all包含重复结果

select cnames,semester,credit from course c join sc on c.cno =sc.cno join student s on s.sno =sc.sno
where sname='李勇'
union
select cnames,semesterf,cno from course c join sc on c.cno =sc.cno join student s on s.sno =sc.sno
where sname='王大力'

#32查询在第三学期开设的课程中 李勇选了但王大力没选的课程 列出课程名和学分
#交运算

select c.cno,credit from course c join sc on c.cno =sc.sno join student s on s.sno =sc.sno
where sname = '李勇'
except
select c.cno,credit from course c join sc on c.cno =sc.sno join student s on s.sno =sc.sno
where sname = '王大力'

#33查询在学分大于3分的课程中 李勇和王大力所选的相同课程 列出课程名和学分
#

select c.cno,credit from course c join sc on c.cno =sc.sno join student s on s.sno =sc.sno
where sname = '李勇' and credit >=3
intersect
select c.cno,credit from course c join sc on c.cno =sc.sno join student s on s.sno =sc.sno
where sname = '王大力' and credit >=3