DB2 SQL语法系列（二）

 一、数据库的基本查询

1、 使用case语句替换查询结果集中的数据

case语法格式为：

case

when 条件1 then 表达式1

when 条件2 then 表达式2

……..

else 表达式

end

as 替换的新列名

 

例如，查询student表中专业为计算机的各同学的学号、姓名和总学分，对其总学分按以下规则进行替换：若总学分为空值，替换为“尚未获取学分”；若总学分小于50，替换为“未修够学分”；若总学分大于等于50，则替换为“修够学分可授予学士学位”；列标题修改为“是否修够学分”。

$ db2 select 学号，姓名

case

      when 总学分 is null then ‘尚未获取学分’

      when 总学分 < 50 then ‘未修够学分’

      else ‘修够学分可授予学位’

end as 是否修够学分

from student

where 专业=‘计算机’

 

2、 使用聚集函数计算列值

常用的聚集函数有下面几种：

sum（） 求和

avg（） 求平均值

max（）求最大值

min（）求最小值

count（）汇总

接下来用实例来加深对它的理解。

实例1：求yuangong表中员工的总人数

select count(*) from yuangong

（注意count（）括号中写为null的不计）

 

实例2：求yuangong表中工资最少的员工信息

select * from yuangong where 工资=(select min (工资) from yuangong)

 

实例3：求每个部门的平均工资

这个实例必须要用到一个分组命令：group by

select 部门,avg(工资) from yuangong group by 部门

注意：当我们用group by这个命令时对其结果进行过滤时不能用where只能用having。

 

（1）sum和avg

 例如，求选修101课程的学生的平均成绩。

select avg （成绩）as 课程号101的平均成绩
from 成绩表
where 课程号=‘101’

 

（2）max和min

例如，求选修101课程的学生最高分和最低分。

select max（成绩）as “课程号101的最高分”，min（成绩）as “课程号101的最低分”
from 成绩表
where 课程号=‘101’

 

（3）count

使用count（*），则返回的结果不管行中是空值还是非空值，均返回。

使用count（col_name）指定了列名，则只返回行中有值的行数。

例1，求学生的总人数。

$ Select count（*） “学生总人数”

from 成绩表

 

例2，求选修了101课程的学生总人数。

$ Select count（distinct 学号）

from 成绩表

where 课程号=‘101’

 

例3，统计“离散数学”课程成绩在80分以上的人数。

$ select count（成绩） “离散数学80分以上的人数”

from 成绩表

where 成绩>=80 and 课程号

in（select 课程号 from 课程表 where 课程名=‘离散数学’）

 

 

（4）distinct

例如，查询student表中的专业和总学分列，并消除结果集中重复的行。

$ select distinct 专业，总学分

from student

 

3、 from子句以及from子句中的连接

（1）在SQL中，连接查询有三大类表示形式。

在where子句和having子句中使用SQL标准“连接谓词”的形式。

在from子句中用“逗号”隔开多个表名的形式称为“自然连接”。

在from子句中使用join的表示形式。

 

使用自连接

例如，查询选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名和成绩。

$ select x.学号，x.姓名，k.课程名，c.成绩

from 学生表 x，课程表 k，成绩表 c

where x.学号=c.学号 and  k.课程号=c.课程号

and k.课程名=‘计算机基础’ and  c.成绩 >=80

注意：连接和子查询的区别是，连接可以合并两个或多个表中的数据，而带子查询的select语句的结果只能来自一个表。使用连接有执行速度快的优点，应尽量使用连接。

 

（2）以join关键字指定的连接

内连接（inner join、join）

全外连接（full outer join）

左外连接（left outer join）

右外连接（right outer join）

交叉连接（cross join）

星型连接（star join）

 

内连接（inner join、join）

内连接按照on指定的连接条件合并两个表，返回满足条件的行。内连接仍可使用where子句指定条件。

例如，查询不同课程，但成绩相同的学生学号、课程号和成绩。

$ select a.学号，a.课程号，b.课程号，a.成绩

From 成绩表 a join 成绩表 b

On a.成绩=b.成绩 and a.学号=b.学号

and a.课程号 != b.课程号

 

外连接（outer join）

如果表A和表B通过外连接而连接，那么在两个表中不匹配的行也会出现在结果集中。因此，外连接把不匹配的行添加到结果集中。

外连接包含全外连接（full outer join）、右外连接（right outer join）和左外连接（left outer join）三种。

全外连接：包含连接的两个表的匹配行，且保留两个表的不匹配行。

右外连接：包含连接的两个表的匹配行，且保留第二个表的不匹配行。

左外连接：包含连接的两个表的匹配行，且保留第一个表的不匹配行。

例如，查询选修课程的选修情况和所有开设的课程名

Select c.*，课程名

From 成绩表 c right join 课程表 k

On c.课程号=k.课程号

 

交叉连接（cross join）

结果表是由第一个表的每行与第二个表的每一行拼接后形成的表。结果集中的数据行数等于第一个表乘以第二个表中符合查询条件的数据行数。

例如，列出学生所有可能的选课情况。

Select 学号，姓名，课程号，课程名

From 学生表 cross join 课程表

Where 学号=‘10001’

 

4、 fetch first 子句限制结果集返回行数

查询学生表中的姓名、专业和总学分三列，只返回结果的前6行。

Select 姓名，专业，总学分
From 学生表
Fetch first 6 row only [ 或使用limit 6，默认为0行数开始]

 

5、 where子句以及where子句中的谓词连接

where子句谓词有：

关系型谓词（比较型谓词）

Between

Like

In

Exists

Is null / is not null

每个谓词都指明了DB2在每行数据上所使用的过滤条件。

（1）关系型谓词

< 、> 、<= 、 >=  、= 、 < >  、not

例如，查询所有总学分超过50的学生姓名。

select 姓名 from 学生表 where 总学分 > 50

 

（2）between谓词

该谓词用于检查一个值是否在某个范围内。

select * from 学生表
where 出生时间 between ‘1990-01-01’and ‘1990-12-31’

注：如果在between之前加“not”操作符，则表示查询其他的所有值。

 

（3）like谓词

该谓词检查一个字符值是否包含了所描述的模式。

下划线（_），代表任意单个字符的通配符。

百分号（%），代表任意多个字符的通配符。

例如，查询所有姓“王”的学生信息。

select * from 学生表
where 姓名 like ‘王%’

注：like谓词需要谨慎使用，因为其速度慢、非常耗费资源，故而效率不高。

 

（4）in谓词

该谓词检查一个值是否匹配指定列中的某个值。

例如，查询“计算机”专业的所有学生成绩记录。

select * from 成绩表
where 学号 in （select 学号 from 学生表 where 专业=‘计算机’）

 

（5）exists谓词

该谓词用来确定某行数据是否存在于表中，后面还总是跟着一个子查询。

注意：in适合于外表大而内表小的情况；exists谓词适用于外表小而内表大的情况。

例如，查询“成绩表”中成绩大于80分的所有学生姓名。

select 姓名 from 学生表 x
where exists （select * from 成绩表 c where 成绩 > 80 and c.学号=x.学号）

 

（6）null空值

用于判定一个表达式的值是否为空值。

例如，查询“备注”列为空值的行。

select * from 学生表
where 备注 is null

 

6、 group by子句

group by子句根据指定列中的值对结果集中的行进行逻辑分组。

 

注意：group by子句中的列必须是select语句中的列

（1）简单的group by子句

例如，查询选修的各门课程的平均成绩和选修该课程的人数。

select 课程号，avg（成绩） 平均成绩，count（学号） 选修人数
from 成绩表
group by 课程号

 

（2）带with rollup关键字的group by子句

使用带rollup操作符的group by子句，在结果集内不仅包含有group by指定的行，还包含汇总的行。

例如，在学生表中统计每个专业的男生、女生人数、总人数以及学生总人数。

select 专业，性别，count（*）人数
from 学生表 Group by 专业，性别
with rollup

 

（3）grouping与带有cube/rollup的group by子句配合使用

例如，统计各专业男生、女生人数以及学生总人数、标志汇总行。

select 专业，性别，count（*）人数
grouping（专业）zy，grouping（性别）xb
from 学生表
group by 专业，性别
with cube

 

7、 having子句

注意：在select语句中，where用于筛选由from指定的数据对象，group by用于对where的结果进行分组，having则是对group by以后的分组数据进行过滤。

Having子句中的列可以是也可以不是select语句中的列。

例如，查找通信工程专业平均成绩在80分以上的学生学号和平均成绩。

select 学号，avg（成绩）平均成绩
from 成绩表
where 学号 in
（select 学号 from 学生表 where 专业=‘通信工程’）
group by 学号
having avg（成绩）>=80

 

8、 order by子句

该子句根据指定列中的值对结果数据集中的数据行进行排序。如果desc关键字在列名后面，则使用降序排列（默认是ASC升序）。

例如，根据姓名升序排列后，再根据学号降序排列。

select 姓名，学号 from 学生表
where 姓名 like ‘王%’
order by 姓名 asc，学号 desc

 

9、 union子句

该子句用来把两个单独的结果集合并成一个结果集。使用该子句时，两个结果集，必须有同样的数据列。

例如，一个select语句查询出生时间在1990年的学生，另一个select语句查询姓“王”的学生。再通过union将两个select语句连接起来。

select 学号，姓名，出生时间 from 学生表
where 出生时间 between ‘1990-01-01’and ‘1990-12-31’
union
select 学号，姓名，出生时间 from 学生表
where 姓名 like ‘王%’

 

 

二、数据库视图

视图的优点：

集中数据，简化用户数据查询和处理。

屏蔽数据库的复杂性

简化用户权限的管理

便于数据共享

定义视图后，可以在select、insert、update和delete等语句中使用视图

 

使用视图，应该注意：

视图的命名不能与表同名

不能在视图上建立任何索引

 

1、 创建视图

语法格式为：

create view view_name

as

select_attribute

with check option

注：在未指定with check option的情况下，在修改期间不会进行数据验证。

 

（1）创建检查选项的视图（即对称视图）

例如，创建视图“cj_view”，该视图包含“计算机基础”课程的学生成绩信息。

create view cj_view
as
select 学号，课程号，成绩
from 成绩表
where 课程号=‘1001’
with check option

 

（2）      创建嵌套视图

注：在创建视图时，还可以指定with local check option。那么在检查任何插入或更新操作时将使用该视图的定义。

例如，在视图“xs_view1”的基础上创建视图“xf_view2”，视图“xf_view2”限定为总学分>50。

create view xf_view2
as
select 学号，姓名，专业，姓别，出生时间，总学分
from xs_view1
where 总学分>50
with check option

分析：视图xf_view2是在xs_view1上创建的。

 

（3）      可删除视图

可删除视图，是可以对其成功执行delete语句的视图。

例如，创建视图“cj_view1”，该视图包含学号为“10001”学生的成绩信息。

create view cj_view1
as
select 学号，课程号，成绩
from 成绩表
     where 学号=‘10001’

 

（4）      可插入视图

（5）      可更新视图

（6）      只读视图

 

2、 查询视图

 

3、 更新视图

 

4、 删除视图

 

5、 查看视图定义

其格式为：show create view view_name

注：如同使用基本表一样，使用视图，只是将表名换成视图名而已。

 

 

说明

笔者在表、列中使用中文，仅是出于方便大家和自己更形象的学习、理解。