数据库——实验4 SQL的数据查询功能

2.SQL   查询命令格式

SQL 的核心是查询，SQL 的查询命令也称作 SELECT 命令，它的基本形式由 SELECT-FROM-WHERE  查询块组成，多个查询块可以嵌套执行。SELECT  语句的一般 格式是：

SELECT [ ALL | DISTINCT ]{*|<表达式>,…,<表达式>}

FROM <表名>[,<表名> … ]

[WHERE <条件>]

[GROUP BY <列名>[,<列名> … ][HAVING <谓词>]]

[ORDER BY <列名> [ASC|DESC][,  <列名> [ASC|DESC].…]

整个语句的作用是：根据WHERE子句中的条件表达式，从基本表或者视图中找出满 足条件的元组，按照 SELECT子句中的目标列，选出元组中的分量形成结果表。下面是对 语句中的几个关键字的功能给出的解释。

(1)SELECT子句：指定要显示的属性列。

说明要查询的数据，ALL 说明不去掉重复元组，DISTINCT  说明要去掉重复元组，<表 达式>一般是表中的列名，如果要查询表中的所有列可以使用“*”标识。

(2)FROM子句：指定查询对象(基本表或视图)说明要查询的数据来自哪个(些)表，

可以基于单个表或多个表进行查询。

(3)WHERE子句：指定查询条件。说明查询条件，即选择元组的条件，可以用于查询

条件的运算符也非常丰富。

(4)GROUP  BY 子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个 组。通常会在每组中作用聚集函数。GROUP BY 短语用于对查询结果进行分组，可以利用它进行分组汇总。

(5)HAVING短语：筛选出满足指定条件的组。HAVING  短语必须跟随 GROUP使用，它用来限定分组必须满足的条件。

(6)ORDER BY 子句：对查询结果表按指定列值的升序或降序排序。可以对查询的结果进行排序，可以是升序或降序，可以按多列排序。ORDER BY 默认是升序，降序需用DESC。

3. 几种常用的查询方法

下面以“学生管理”数据库为例，对常用的查询方法进行说明。

在“学生管理”数据库中有“学生”表、“课程”表、“成绩”表，结构如图所示。

1)简单查询

(1)从“成绩”表中查出所有学生分数。

方法一：

SELECT 分数 FROM  成 绩

方法二：

SELECT DISTINCT 分数 FROM 成绩

(2)查询“学生”表中的所有学生信息。

方法一：

SELECT * FROM 学 生

方法二：

SELECT 学号,姓名,性别,出生日期,所在院系 FROM 学生

(3)查询成绩大于90分的学生学号。

SELECT  学号

FROM  成绩

WHERE 分数 > 90

(4)给出在计算机系或外语系学习的男学生的学号。

SELECT 学号

FROM  学生

WHERE 性别='男'AND(所在院系='计算机系'OR所在院系='外语系')

(5)检索出分数在60～100分范围内的成绩信息。

SELECT  *

FROM 成绩

WHERE 分数 BETWEEN 60 AND 100

注意：表达式“分数 BETWEEN 60 AND100”等价于(分数>=60)AND(分数<=100)

(6)从“学生”表中检索出姓“张”的学生信息。

SELECT  *

FROM 学生

WHERE 姓名 LIKE' 张 % '

注意：这里的LIKE 是字符串匹配运算符，通配符“%”表示0个或多个字符，另外还有 一个通配符“_”(下划线)表示一个字符，

(7)找出尚未确定分数的成绩信息。

SELECT *

FROM 成绩

WHERE 分数 IS NULL

注意：查询空值时要使用IS NULL,而 =NULL是无效的，因为空值不是一个确定的值，所以不能用“=”这样的运算符进行比较。

(8)列出已经确定了分数的成绩信息。

SELECT *

FROM 成绩

WHERE 分数 IS NOT NULL

2)排序

(1)按学生的分数升序检索出全部成绩信息。

SELECT *

FROM 成绩

ORDER BY 分数

(2)先按学号排序再按分数排序输出全部学生成绩信息。

SELECT *

FROM 成绩

ORDER BY 学号,分数

3)分组及计算查询

在分组查询时，经常用到聚集函数，主要包括：

COUNT——计数。

SUM—— 求和。

AVG—— 计算平均值。

MAX——求最大值。

MIN—— 求最小值。

(1)求每名学生的平均分数。

SELECT 学号,AVG(分数)
FROM 成绩
GROUP BY 学号

(2)求至少选修2 门课的每名学生的平均分数。

SELECT 学号 ,COUNT(*),AVG(分数)

FROM 成绩

GROUP  BY 学号

HAVING COUNT(*)>=2

4)连接查询

当查询的结果出自多个表时，需要通过表之间的连接操作来完成。

(1) 一 般连接

常规的两个表或多个表之间的连接。

① 找出分数高于90分的学生姓名和所在院系。

SELECT 姓名,所在院系

FROM 学生,成绩

WHERE(分数>90)

AND(学生.学号=成绩.学号)

② 找出选修数据库课程的学生的学号和分数。

SELECT 学号,分数
FROM 课程,成绩
WHERE(课程名='数据库')
AND(课程.课程号=成绩.课程号)

③ 给出计算机系选修数据库课程的学生姓名和分数。

SELECT  姓名,分数

FROM 学生,课程,成绩

WHERE 所在院系='计算机系'AND课程名='数据库'

AND 学生.学号=成绩.学号 AND 成绩.课程号=课程.课程号

(2)别名和自连接查询

一个表通过不同的属性到自身的连接称作自连接。这种关系中的一些元组，根据出自同一值域的两个不同的属性，可以与另外一些元组有一种对应关系(一对多的联系)。为了实现自连接，需要将一个关系看作两个逻辑关系，为此需要给关系指定别名。 

下面以“雇员”表为例，对别名和自连接查询方法进行说明。

设有如图1-21所示的“雇员”表，其中雇员号和经理两个属性出自同一个值域，同一元组的这两个属性值是“上、下级”关系。查询：根据“雇员”表列出上一级经理及其职员(被其领导)的清单。

SQL 语句为：

SELECT S.雇员姓名,'领导',E.雇员姓名
FROM 雇员 S,雇员 E
WHERE S.雇员号=E.经理

5)嵌套查询

下面以“仓库管理”数据库为例，对常用的查询方法进行说明。

在“仓库管理”数据库中有“职工”表、“仓库”表和“供应商”表，结构如图1-22所示。

(1)普通嵌套查询

当检索关系X 中的元组时，它的条件依赖于相关的关系Y 中的元组的属性值，这时使用普通的嵌套查询将非常方便。

① 哪些城市至少有一个仓库的职工的工资为1250元。

SELECT 城市
FROM 仓库
WHERE 仓库号 IN (SELECT 仓库号
FROM 职工
WHERE 工资=1250)

② 找出和职工 E4 工资相同的所有职工。

SELECT 职工号
FROM 职工
WHERE 工资 =(SELECT 工资
FROM 职工
WHERE 职工号='E4')

③找出哪些城市的仓库向北京的供应商发出了订购单。

SELECT 城市 FROM 仓库 WHERE 仓库号 IN
(SELECT 仓库号 FROM 职工 WHERE 职工号 IN
(SELECT 职工号 FROM 订购单 WHERE 供应商号 IN
(SELECT 供应商号 FROM 供应商 WHERE 地址='北京')))

(2)使用量词的嵌套查询

在嵌套查询中可以使用ANY、SOME、ALL  等量词，它们的形式是：

SELECT < 表达式 > < 比较运算符 > [ANY|ALL|SOME](子查询)

其中 ANY 和 SOME 是同义词，在进行比较运算时只要子查询中有一行能使结果为真，则结果就为真；而 ALL 则要求子查询中的所有行都使结果为真时，结果才为真。 

① 检索有职工的工资大于或等于 WH1 仓库中职工的工资的仓库号。

SELECT DISTINCT 仓库号 
FROM 职工 
WHERE 工资 >= ANY(SELECT 工资 FROM 职工
WHERE 仓库号='WH1')

SELECT DISTINCT 仓库号 
FROM 职工
WHERE 工资 >= (SELECT MIN(工资)FROM 职工
WHERE 仓库号='WH1')

② 检索有职工的工资大于或等于 WH1 仓库中所有职工的工资的仓库号。

SELECT DISTINCT 仓库号 
FROM 职工 
WHERE 工资 >= ALL(SELECT 工资 FROM 职工
WHERE 仓库号='WH1')

SELECT DISTINCT 仓库号
FROM 职工
WHERE 工资 >= (SELECT MAX(工资) FROM 职工
WHERE 仓库号 ='WH1')

③ 求支付的工资总数。

SELECT SUM(工资)
FROM 职工

 

④ 求在“上海”仓库工作的职工的最高工资值。

SELECT MAX(工资)
FROM 职工
WHERE 仓库号 IN 
(SELECT 仓库号 FROM 仓库 
WHERE 城市='上海')