sql 例题2

SQL的概念
* 通过 SQL 指令，用户先列出数据库档及查询的条件， SQL 程序便会在这数据库档内检查每笔记录是否符合这项条件，并把有关的资料显示出来。这个过程称为检索。 (见例 2)
* 用户除了直接查问各笔记录的资料外，也可查问统计数项，例如最大值、最小值、总和及平均值。
* 查询所得的结果会以表格的形式显示，用户亦可指示 SQL 程序把结果贮存成为数据库档。

在 FoxPro 如何使用 SQL
* 使用 SQL，必须先把有关数据库档开启。
* 用户可使用指令视窗 (Command Window) 直接把指令输入，亦可使用对话方块把指令输入。
* 若用户选用字符串的完全配对时，便须输入 SET ANSI ON。


2. SQL检索指令的基本结构

一般语法 SELECT, ALL / DISTINCT, *,
AS, FROM, WHERE
比较 IN, BETWEEN, LIKE "% _"
群组 GROUP BY, HAVING,
COUNT( ), SUM( ), AVG( ), MAX( ), MIN( )
显示次序 ORDER BY, ASC / DESC
逻辑运算符 AND, OR, NOT
输出 INTO TABLE / CURSOR
TO FILE [ADDITIVE], TO PRINTER, TO SCREEN
联合 UNION


简称：
expr = 表达式 expression_r, groupexpr = 群组表达式 group expression_r
col = 栏 column, comcol = 共同栏 common column
colname = 栏名 column name
nullval = 空值 null value








实例：学生个人资料

例子: 考虑以下贮存学生数据的数据库档 STUDENT. DBF：

(i) hcode 表示学生的社名(即红黄蓝绿四社)。
R = Red, Y = Yellow, B = Blue, G = Green
(ii) dcode 表示学生的居住地区码。
eg. TST = 尖沙咀(Tsim Sha Tsui), MKK = 旺角(Mong Kok)
(iii) remission 表示学生是否享有学费减免：
.T. =享有学费减免, .F. = 没有学费减免
(iv) mtest 贮存学生数学测验的分数，满分为100。

栏名 类型 栏宽 内容
id 数字 4 学生编号
name 字符 10 学生名字
dob 日期 8 出生日期
sex 字符 1 性别： M / F
class 字符 2 班别
hcode 字符 1 社名： R, Y, B, G
dcode 字符 3 地区码
remission 逻辑 1 学费减免
mtest 数字 2 数学测验分数



I 一般语法

SELECT ...... FROM ...... WHERE ......

SELECT [ALL / DISTINCT] expr1 [AS col1], expr2 [AS col2] ;
FROM tablename WHERE condition

– SQL 程序会从数据库档 tablename 选取符合条件的横列 (row) 并以表格的格式显示。
– 表达式 expr1, expr2 可以是 (1) 字段，或 (2) 以函数和字段组成的表达式。
– 而 col1, col2 是表达式 expr1, expr2 在输出结果的表格内的栏名。
– 选项 DISTINCT 会把重覆出现的横列删去(即只显示一次)，而选项 ALL 则会把所有重覆的保留。
– 条件 condition 可以是 (1) 等式或不等式，或 (2) 字符串的比较，并使用逻辑运算符 AND, OR, NOT。

在使用SQL之前，开启数据库档：
USE student

例 1 求出所有学生的资料。
SELECT * FROM student

注意: 1) 这个指令并不提出任何条件，所以把 WHERE 部分省去。
2) 在 SELECT 部分中使用 * 来表示拣选来源表格的所有栏。
3) 查询所得的结果将贮存於一个暂时的表格内。

id
name dob sex class mtest hcode dcode remission
9801 Peter 06/04/86 M 1A 70 R SSP .F.
9802 Mary 01/10/86 F 1A 92 Y HHM .F.
9803 Johnny 03/16/86 M 1A 91 G SSP .T.
9804 Wendy 07/09/86 F 1B 84 B YMT .F.
9805 Tobe 10/17/86 M 1B 88 R YMT .F.
: : : : : : : : :


例 2 求出 1A 班学生的名字和社名。

SELECT name, hcode, class FROM student ;
WHERE class="1A"

注意: 这例使用条件 class="1A" 拣选 1A 班学生：SQL 程序会从来源表格中的每一横列逐一地检查是否符合这条件。然后 SQL 程序会根据 SELECT 的选项而保留这些横列内的三栏，即 name, hcode 及 class。最后程序会把所得的结果贮存於一个暂时的表格内。









name
hcode class
Peter R 1A
Mary Y 1A
Johnny G 1A
Luke G 1A
Bobby B 1A
Aaron R 1A
: : :



例 3 求出红社社员的居住地区。(hcode="R")

SELECT DISTINCT dcode FROM student ;
WHERE hcode="R"

注意: 若两个或以上的学生居住於同一地区，使用选项 DISTINCT 便可把重覆的结果省去。

dcode

HHM
KWC
MKK
SSP
TST
YMT


例 4 求出 1B 班女生的名字和年龄。(准至一位小数)

SELECT name, ROUND((DATE( )-dob)/365,1) AS age FROM student ;
WHERE class="1B" AND sex="F"

注意: 1) 「1B 班女生」的条件包括两部分：class="1B" 和 sex="F"。而这项条件必须同时符合，所以要使用逻辑运算符 AND。
2) 在这数据库档裏并没有一栏直接贮存年龄，所以我们须要使用学生的出生日期 dob 来计算。首先，DATE( )-dob 表示该生的"日岁"，再除 365 就变成"年岁"。再用选项 AS age 去说明该栏的名称。

name
age
Wendy 12.1
Kitty 11.5
Janet 12.4
Sandy 12.3
Mimi 12.2



例 5 求出 1A 班没有学费减免的学生的名字和编号。

SELECT name, id, class FROM student ;
WHERE class="1A" AND NOT remission

注意: 1) 这裏包括两项条件：学生必须是 1A 班，而且没有享有学费减免。 所以在 WHERE 部分中加上运算符 AND。
2) 因 remission 是一个逻辑字段，所以可直接在逻辑表达式中使用 。在 remission 之前加上 NOT 便把意思相反。


name
id class
Peter 9801 1A
Mary 9802 1A
Luke 9810 1A
Bobby 9811 1A
Aaron 9812 1A
Ron 9813 1A
Gigi 9824 1A
: : :



II 比较

expr IN ( value1, value2, value3)
expr BETWEEN value1 AND value2
expr LIKE "%_"

– 在 WHERE 部分中，可使用以上的比较条款：
1) 若 expr 的值是相等於 value1, value2, value3 其中的一个时，条款 expr IN ( value1, value2, value3) 便会送回逻辑值 .T.。而 expr 可以是数值或字符串。
2) 若expr 是界乎於 value1 和 value2 之间，条款
expr BETWEEN value1 AND value2 便会送回逻辑值 .T.。
3) 若字符串 expr 符合 "%_" 的样式，条款 expr LIKE "%_" 便会送回逻辑值 .T.。在样式中， "%" 代表任何长度的字符串，而 "_" 则代表任何的单一字符。

例 6 求出所有出生於星期三或星期六的学生。

SELECT name, class, CDOW(dob) AS bdate FROM student ;
WHERE DOW(dob) IN (4,7)

注意: 学生若在星期三星期六出生， DOW(dob) 使会送回 数值 4 或 7，所以用 IN (4,7) 来检查。

name
class bdate
Peter 1A Wednesday
Wendy 1B Wednesday
Kevin 1C Saturday
Luke 1A Wednesday
Aaron 1A Saturday
: : :



例 7 求出所有不是在一月、三月、六月或九月出生的学生。

SELECT name, class, dob FROM student ;
WHERE MONTH(dob) NOT IN (1,3,6,9)

注意: 我们不想 MONTH(dob) = 1, 3, 6 或 9， 所以使用 NOT IN (1,3,6,9) 来检查。

name
class dob
Wendy 1B 07/09/86
Tobe 1B 10/17/86
Eric 1C 05/05/87
Patty 1C 08/13/87
Kevin 1C 11/21/87
Bobby 1A 02/16/86
Aaron 1A 08/02/86
: : :


例 8 求出1A 班的学生名字，其数学测验分数界乎於 80 至 90 分之间。

SELECT name, mtest FROM student ;
WHERE class="1A" AND mtest BETWEEN 80 AND 90

注意: 1) 这裏用了两个条件：第一个是 class="1A"，第二个是测验分数界乎於 80 至 90 分之间。这两个条件必须同时成立，所以须用 AND。
2) 测验分数界乎於 80 至 90 分之间可用 mtest BETWEEN 80 AND 90 来表示。

name
mtest
Luke 86
Aaron 83
Gigi 84



例 9 求出所有学生其名字是以 "T" 为起首。

SELECT name, class FROM student ;
WHERE name LIKE "T%"

注意: 这裏所用的样式是 "T%" ，这表示第一个字符必须是 "T" 而其后可以是任何的字符串。

name
class
Tobe 1B
Teddy 1B
Tim 2A


例10 求出所有红社社员其名字的第二个字母是"a"。

SELECT name, class, hcode FROM student ;
WHERE name LIKE "_a%" AND hcode="R"

注意: 这裏所用的样式是 "_a%" ，而其中的 "_" 符号代表任何单一字符，亦即第一个字符是任意的。第二个字符则必须是 "a"，而其后的 "%" 代表任何的字符串。


name
class hcode
Aaron 1A R
Janet 1B R
Paula 2A R



III 群组

SELECT ...... FROM ...... WHERE condition ;
GROUP BY groupexpr [HAVING requirement]


群组函数： COUNT( ), SUM( ), AVG( ), MAX( ), MIN( )


–GROUP BY groupexpr 列出群组组成所依照的表达式。一般都是数据库档的一栏。
– WHERE condition 列出个别横列所须符合的条件，而 HAVING requirement 则列出个别群组须符合的条件。
– 可使用以群组函数来计算统计数项：
COUNT( ): 记录出现的目数
SUM( ): 总和
AVG( ): 平均值
MAX( ): 最大值
MIN( ): 最小值


例11 求出每一班的人数。

SELECT class, COUNT(*) FROM student
GROUP BY class

注意: 1) 使用 GROUP BY class， SQL 程序便会先把表格裏的横列按照 class 排列起来。
2) 然后 SQL 程序会把相连的横列依照 class 来群组。
3) 最后使用群组函数 COUNT(*) 去算数每组的数目。