SQL数据操纵

个人博客

SQL的基本查询功能

映像语句

• 目标子句：SELECT *|colname {, colname…}
• 范围子句：FROM tablename {, tablename…}
• 条件子句：[WHERE search_condition]
• 分组子句：[GROUP BY colname {, colname…}
• 分组查询子句：[HAVING group_condition]]
• 排序输出子句：[ORDER BY colname [ASC|DESC] {, colname[ASC|DESC]…}]

上述子句执行顺序：FROM(选表)->WHERE(条件选择)->GROUP BY(分组)->HAVING(条件选择)->SELECT(投影)->ORDER BY(排序)

目标子句

SELECT子句相当于投影运行，有如下构造方式：

• 给出结果属性的属性名

  • 通过‘表名.属性名’的方式来表明是哪一张表中的属性

  • 结果属性的重命名，比如：

    <column_expression> AS <colname>
    

• 可用’*'来代替表中所有属性

• 可用保留字’distinct’来消除结果中的重复元组

范围子句

FROM指定操作对象

可以在FROM子句中对一个关系重新命名，如：

<table_name> <alias_name>

• 主要用于关系自身的联接运算

SELECT子句和FROM子句是一条映像语句中必不可少的两个组成部分

条件子句

WHERE是映像语句中的可选部分，用于定义查询条件。包括单个关系的元组选择条件以及关系与关系之间的联接条件都需要WHERE子句的逻辑表达式表示出来，比如说：

• FROM子句中给出的关系只能表明此次查询需要这些关系，它们是通过笛卡尔积运算合并起来的
• 如果需要执行他们之间$\theta$联接或自然联接运算，则需要在WHERE子句中显式地给出他们的联接条件

常用谓词

除常用的算术比较运算符外，SQL还提供了若干比较谓词，以增加查询语句的表达能力：

• DISTINCT（去重）
• BETWEEN … AND … （查询一个区间）
• NOT BETWEEN … AND …
• LIKE（见下）
• NOT LIKE
• IS NULL（为空）
• IS NOT NULL

DISTINCT仅用于SELECT子句中，其他谓词一般用于WHERE子句中，用于构造查询条件

LIKE谓词的使用方法

column [NOT] LIKE val1 [ESCAPE val2]

• 模板（pattern）：val1
  • 下划线（_）：可以匹配任意一个字符
  • 百分号（%）：可以匹配任意一个字符串（包括长度为0的空字符串）
  • 其他字符：只能匹配其自身
• 转义指示字符：val2
  • 紧跟在转义指示字符val2之后的"_“或”%"(包括转义字符本身)不再是通配符，而是其自身

例：查询姓名为A开头，且第三个字符必为P的学生的姓名和系别

S(sno,sn,sd,sa) C(sno,cn,pno) SC(sno,cno,g)

SELECT 	sn,sd
FROM	S
WHERE	sn LIKE 'A_P%';

查询在课程名中含有下划线（_）的课程的课程名

SELECT cno
FROM C
WHERE cn LIKE '%A_%' ESCAPE 'A';

布尔表达式

在WHERE子句中，可以使用NOT、AND与OR这三个逻辑运算符构造出复杂的查询条件

简单连接

在WHERE子句中，通过两个属性之间的相等比较实现表与表之间的连接

例：查询修读课程号为DATABASE的所有学生的姓名

S(sno,sn,sd,sa) C(sno,cn,pno) SC(sno,cno,g)

SELECT	S.sn
FROM	S,SC,C
WHERE	S.sno=SC.sno AND SC.cno=C.cno AND C.cn='DATABASE';

自连接

在查询中，有时需要对相同的表进行连接。为了区分两张相同的表，必须在FROM子句中至少对其中之一进行换名，以区分开这两张表

例：查询至少修读学号为S5的学生所修读的一门课程的学生的学号

S(sno,sn,sd,sa) C(sno,cn,pno) SC(sno,cno,g)

SELECT 	SC1.sno
FROM	SC SC1, SC SC2
WHERE	SC1.cno=SC2.cno AND SC2.sno = 's5';

结果排序

有时，需要查询结果按照某种顺序显示，此时需要加一个排序子句ORDER BY，形式如下：

ORDER BY <colname> [ASC|DESC] {, ...}

其中：

• 给出需要排序的列的列名
• ASC|DESC则给出排序升序或降序参数，缺省值是升序

分层查询与集合谓词使用

• 分层结构指的是一条映像语句的某个子句中嵌入另一条映像语句，被嵌入的映像语句通常称为子查询。如下:

SELECT	...
FROM	...
WHERE	...(
	SELECT	...
    FROM	...
    WHERE	...);

• 子查询通常被嵌入WHERE子句中，可使结构清晰

• 由于子查询的查询结果是一个集合，因此需要在WHERE子句中引入集合谓词

• WHERE子句中的集合谓词主要有：

  • IN谓词：标量与集合量之间的属于比较

    expr [NOT] IN (subquery)
    

  • 限定比较谓词：标量与集合中元素之间的量化比较

    expr θ ANY|ALL (subquery)
    

  • EXISTS谓词：是否为空集的判断谓词

    [NOT] EXISTS (subquery)
    

• 嵌套查询的处理顺序

  • 一般情况下，嵌套查询中的子查询只需要被执行一次，然后利用所获得的中间查询结果来计算外层的查询语句，这样的子查询也被称为独立子查询，其处理顺序由内到外
  • 在有些情况下，在子查询中调用了外层查询中的表以及元组变量。随着外层元组变量的每一次的取值变化，都需要重新执行子查询以获得相关的中间查询结果，这样的子查询也被称为相关子查询，其处理顺序是由外到内，直至处理完外层查询表中的所有元组

IN谓词的使用

例：查询修读课程名为C1的所有学生的姓名

S(sno,sn,sd,sa) C(sno,cn,pno) SC(sno,cno,g)

独立子查询

SELECT 	S.sn
FROM	S
WHERE	S.sno IN (
	SELECT	SC.sno
	FROM	SC
	WHERE	SC.cno='c1');

相关子查询

SELECT	S.sn
FROM	S
WHERE	'C1' IN (
	SELECT	SC.cno
	FROM	SC
	WHERE	SC.sno=S.sno)

限定比较谓词的使用

谓词ANY表示子查询结果集合中的某个值，而谓词ALL表示子查询结果集中的所有值。

例：查询有学生成绩大于 C1 课程号中所有学生成绩的学生学号

S(sno,sn,sd,sa) C(sno,cn,pno) SC(sno,cno,g)

SELECT	sno
FROM	SC
WHERE	g > ALL (
	SELECT	g
	FROM	SC
	WHERE	cno='C1');

查询有学生成绩大于等于 C1 课程号中的任何一位学生成绩的学生学号

SELECT	sno
FROM	SC
WHERE	g >= ANY (
	SELECT	g
	FROM	SC
	WHERE	cno='C1');

谓词CONTAINS的使用

集合间的关系可以通过WHERE子句中SELECT语句间的包含符CONTAINS实现

例：查询至少修读学号为 S4 的学生所修读的所有课程的学生的学号

S(sno,sn,sd,sa) C(sno,cn,pno) SC(sno,cno,g)

SELECT 	sno
FROM 	SC
WHERE(
    SELECT 	SCx.cno
    FROM 	SC SCx
    WHERE 	SC.sno = SCx.sno)
	CONTAINS
		(SELECT SC.cno
    	 FROM SC.SCy
         WHERE SCy.sno = ‘S4’);

• SQL标准中并没有提供CONTAINS操作符

谓词EXISTS的使用

例：查询修读课程号为 C1 的所有学生的姓名

SELECT 	S.sn
FROM 	S
WHERE	EXISTS(
	SELECT 	*
	FROM	SC
	WHERE S.sno = SC.sno AND SC.cno = ‘C1’);

SELECT语句间的运算

子查询之间的并、交、差运算：

• <子查询1> UNION [ALL] <子查询2>
• <子查询1> INTERSECT [ALL]<子查询2>
• <子查询1> EXCEPT [ALL]<子查询2>

例：查询计算机系的学生以及年龄小于 20 岁的学生

S(sno,sn,sd,sa) C(sno,cn,pno) SC(sno,cno,g)

(SELECT * FROM S WHERE sd='CS')
UNION
(SELECT * FROM S WHERE sa<20);

SQL计算、统计、分类的功能

统计功能

函数名称	参数类型	结果类型	说明
COUNT	any(can be *)	numeric	count of rows
SUM	numeric	numeric	sum of argument
AVG	numeric	numeric	average of argument
MAX	char or numeric	same as argument	maximum value
MIN	char or numeric	same as argument	minimum value

上述函数不能在WHERE语句中使用

例：给出学号为 S1 学生修读的课程门数

S(sno,sn,sd,sa) C(sno,cn,pno) SC(sno,cno,g)

SELECT	COUNT (*)
FROM	SC
WHERE	sno='S1'

给出学号为 S7 学生所修读课程的平均成绩

SELECT	AVG(G)
FROM	SC
WHERE	sno='S7'

查询所享受的折扣(discnt)并非最高的客户编号(cid)

Customers (cid, cname, city, discnt)
Agents (aid, aname, city, percent)
Products (pid, pname, city, quantity, price)
Orders (ordno, month, cid, aid, pid, qty, dollars)

SELECT 	cid
FROM 	Customers
WHERE 	discnt < ALL (
	SELECT 	max(c2.discnt)
	FROM 	Customers c2);

查询有两个或两个以上的客户订购过的商品的编号

SELECT	p.pid
FROM	Products p
WHERE 	2 <= ALL (
	SELECT 	count(distinct cid)
	FROM	orders o
	WHERE	o.pid = p.pid);

分类功能

• 分组查询子句：GROUP BY colname {, colname …}
  • GROUP BY的参数是SELECT的子集
  • 根据属性colname的取值的不同，将满足WHERE条件的元组划分为不同的集合
  • 使用GROUP BY子句的目的是可以在SELECT子句中针对不同的元组集合分别进行统计计算，实现分类统计查询
• HAVING group_condition
  • 根据GROUP BY子句的分组结果，定义分组查询条件
  • 只有满足条件的元组集合才会被保留下来，用于生成最终的查询结果
  • 在HAVING子句中给出的条件是定义在分组后元组集合上

IBM的例子

SELECT 	DEP, JOB, AVG(SAL)
FROM	EMPL
WHERE	JOB <>'M'
GROUP BY DEP,JOB
HAVING AVG(SAL) > 28000
ORDER BY AVG(SAL) DESC

例 按总平均值降序给出所有课程均及格但不包括C8的所有学生总平均成绩

SELECT	sno, AVG(G)
FROM	SC
WHERE 	cno <> C8
GROUP BY sno
HAVING	MIN(G) >= 60
ORDER BY AVG(G) DESC

查询每一个供应商在每一种商品上为‘ c002’ 和‘ c003’两位客户订购的总数量（ 结果给出供应商的编号和名称、商品的编号和名称以及销售总数量

SELECT 	a.aid, a.aname, p.pid, p.pname, sum(qty)
FROM 	Agents a, Products p, Orders o
WHERE	a.aid=o.aid and p.pid=o.pid and (o.cid='002' or o.cide='003')
GROUP BY a.aid, a.aname, p.pid, p.pname

SELECT语句使用的一般规则

1. 合并FROM子句中的表（笛卡尔乘积）
2. 利用WHERE子句中的条件进行元组选择，抛弃不满足WHERE条件的那些元组
3. 根据GROUP BY子句对保留下来的元组进行分组
4. 利用HAVING子句中的条件对分组后的元组集合(group)进行选择，抛弃不满足HAVING条件的那些元组集合
5. 根据SELECT子句进行统计计算，生成结果关系的元组
6. 根据ORDER BY子句对查询结果进行排序