DB2 730学习笔记5
数据操作语言DML使用SELECT语句从数据库表检索数据
SELECT * FROM staff
ID NAME DEPT JOB YEARS SALARY COMM
------ --------- ------ ----- ------ --------- ---------
10 Sanders 20 Mgr 7 98357.50 -
20 Pernal 20 Sales 8 78171.25 612.45
30 Marenghi 38 Mgr 5 77506.75 -
40 O'Brien 38 Sales 6 78006.00 846.55
50 Hanes 15 Mgr 10 80659.80 -
60 Quigley 38 Sales - 66808.30 650.25
70 Rothman 15 Sales 7 76502.83 1152.00
80 James 20 Clerk - 43504.60 128.20
90 Koonitz 42 Sales 6 38001.75 1386.70
100 Plotz 42 Mgr 7 78352.80 -
110 Ngan 15 Clerk 5 42508.20 206.60
120 Naughton 38 Clerk - 42954.75 180.00
130 Yamaguchi 42 Clerk 6 40505.90 75.60
140 Fraye 51 Mgr 6 91150.00 -
150 Williams 51 Sales 6 79456.50 637.65
160 Molinare 10 Mgr 7 82959.20 -
170 Kermisch 15 Clerk 4 42258.50 110.10
180 Abrahams 38 Clerk 3 37009.75 236.50
190 Sneider 20 Clerk 8 34252.75 126.50
200 Scoutten 42 Clerk - 41508.60 84.20
210 Lu 10 Mgr 10 90010.00 -
220 Smith 51 Sales 7 87654.50 992.80
230 Lundquist 51 Clerk 3 83369.80 189.65
240 Daniels 10 Mgr 5 79260.25 -
250 Wheeler 51 Clerk 6 74460.00 513.30
260 Jones 10 Mgr 12 81234.00 -
270 Lea 66 Mgr 9 88555.50 -
280 Wilson 66 Sales 9 78674.50 811.50
290 Quill 84 Mgr 10 89818.00 -
300 Davis 84 Sales 5 65454.50 806.10
310 Graham 66 Sales 13 71000.00 200.30
320 Gonzales 66 Sales 4 76858.20 844.00
330 Burke 66 Clerk 1 49988.00 55.50
340 Edwards 84 Sales 7 67844.00 1285.00
350 Gafney 84 Clerk 5 43030.50 188.00
35 record(s) selected.
要限制查询结果集中行的数量，可以使用FETCH FIRST子句
SELECT * FROM staff FETCH FIRST 10 ROWS ONLY
ID NAME DEPT JOB YEARS SALARY COMM
------ --------- ------ ----- ------ --------- ---------
10 Sanders 20 Mgr 7 98357.50 -
20 Pernal 20 Sales 8 78171.25 612.45
30 Marenghi 38 Mgr 5 77506.75 -
40 O'Brien 38 Sales 6 78006.00 846.55
50 Hanes 15 Mgr 10 80659.80 -
60 Quigley 38 Sales - 66808.30 650.25
70 Rothman 15 Sales 7 76502.83 1152.00
80 James 20 Clerk - 43504.60 128.20
90 Koonitz 42 Sales 6 38001.75 1386.70
100 Plotz 42 Mgr 7 78352.80 -
10 record(s) selected.
检索指定的列
SELECT name,salary FROM staff FETCH FIRST 10 ROWS ONLY
NAME SALARY
--------- ---------
Sanders 98357.50
Pernal 78171.25
Marenghi 77506.75
O'Brien 78006.00
Hanes 80659.80
Quigley 66808.30
Rothman 76502.83
James 43504.60
Koonitz 38001.75
Plotz 78352.80
10 record(s) selected.
使用DISTINCT子句消除结果集中的重复的行
SELECT DISTINCT dept,job FROM STAFFDEPT JOB
------ -----
10 Mgr
15 Clerk
15 Mgr
15 Sales
20 Clerk
20 Mgr
20 Sales
38 Clerk
38 Mgr
38 Sales
42 Clerk
42 Mgr
42 Sales
51 Clerk
51 Mgr
51 Sales
66 Clerk
66 Mgr
66 Sales
84 Clerk
84 Mgr
84 Sales
22 record(s) selected.
使用AS子句给选择列表中的表达式分配一个有意义的名字
SELECT name,salary+comm AS pay FROM staff
NAME PAY
--------- ----------
Sanders -
Pernal 78783.70
Marenghi -
O'Brien 78852.55
Hanes -
Quigley 67458.55
Rothman 77654.83
James 43632.80
Koonitz 39388.45
Plotz -
Ngan 42714.80
Naughton 43134.75
Yamaguchi 40581.50
Fraye -
Williams 80094.15
Molinare -
Kermisch 42368.60
Abrahams 37246.25
Sneider 34379.25
Scoutten 41592.80
Lu -
Smith 88647.30
Lundquist 83559.45
Daniels -
Wheeler 74973.30
Jones -
Lea -
Wilson 79486.00
Quill -
Davis 66260.60
Graham 71200.30
Gonzales 77702.20
Burke 50043.50
Edwards 69129.00
Gafney 43218.50
35 record(s) selected.
使用WHERE子句和谓词限制查询返回的数据量
寻找工资超过$20000的职员姓名
SELECT name,salary FROM STAFF WHERE salary > 20000
NAME SALARY
--------- ---------
Sanders 98357.50
Pernal 78171.25
Marenghi 77506.75
O'Brien 78006.00
Hanes 80659.80
Quigley 66808.30
Rothman 76502.83
James 43504.60
Koonitz 38001.75
Plotz 78352.80
Ngan 42508.20
Naughton 42954.75
Yamaguchi 40505.90
Fraye 91150.00
Williams 79456.50
Molinare 82959.20
Kermisch 42258.50
Abrahams 37009.75
Sneider 34252.75
Scoutten 41508.60
Lu 90010.00
Smith 87654.50
Lundquist 83369.80
Daniels 79260.25
Wheeler 74460.00
Jones 81234.00
Lea 88555.50
Wilson 78674.50
Quill 89818.00
Davis 65454.50
Graham 71000.00
Gonzales 76858.20
Burke 49988.00
Edwards 67844.00
Gafney 43030.50
35 record(s) selected.
列出工资超过$20000的不是经理的职员姓名、头衔和工资
SELECT name,job,salary FROM staff WHERE job <> 'Mgr' AND salary > 2000
NAME JOB SALARY
--------- ----- ---------
Pernal Sales 78171.25
O'Brien Sales 78006.00
Quigley Sales 66808.30
Rothman Sales 76502.83
James Clerk 43504.60
Koonitz Sales 38001.75
Ngan Clerk 42508.20
Naughton Clerk 42954.75
Yamaguchi Clerk 40505.90
Williams Sales 79456.50
Kermisch Clerk 42258.50
Abrahams Clerk 37009.75
Sneider Clerk 34252.75
Scoutten Clerk 41508.60
Smith Sales 87654.50
Lundquist Clerk 83369.80
Wheeler Clerk 74460.00
Wilson Sales 78674.50
Davis Sales 65454.50
Graham Sales 71000.00
Gonzales Sales 76858.20
Burke Clerk 49988.00
Edwards Sales 67844.00
Gafney Clerk 43030.50
24 record(s) selected.
寻找以字母S开头的所有姓名
SELECT name FROM staff WHERE name LIKE 'S%'
NAME
---------
Sanders
Sneider
Scoutten
Smith
4 record(s) selected.
子查询是主查询的WHERE子句中出现的SELECT语句,它将结果集提供给WHERE子句。
SELECT lastname FROM employee
WHERE lastname IN
(SELECT sales_person FROM sales
WHERE sales_date < '01/01/2005')
LASTNAME
---------------
LEE
1 record(s) selected.
相关名称是在查询的FROM子句定义的，可以作为表的简单名称；相关名称还可以消除对来自不同表的相同列名的二义性引用。
SELECT e.salary FROM employee e
WHERE e.salary <
(SELECT AVG(s.salary) FROM staff s)SALARY
-----------
66500.00
49250.00
55280.00
62250.00
44680.00
51340.00
50450.00
57740.00
49840.00
42180.00
48760.00
49180.00
47250.00
37380.00
36250.00
35340.00
37750.00
35900.00
39950.00
45370.00
43840.00
46500.00
39250.00
64680.00
37760.00
46250.00
35900.00
35370.00
31840.00
29 record(s) selected.
使用ORDER BY 子句对结果进行排序
SELECT name, salary FROM staff
WHERE salary > 80000
ORDER BY salary
NAME SALARY
--------- ---------
Hanes 80659.80
Jones 81234.00
Molinare 82959.20
Lundquist 83369.80
Smith 87654.50
Lea 88555.50
Quill 89818.00
Lu 90010.00
Fraye 91150.00
Sanders 98357.50
10 record(s) selected.
在ORDER BY子句中指定DESC可以对结果集进行降序排序
SELECT name, salary FROM staff
WHERE salary > 80000
ORDER BY salary DESC
NAME SALARY
--------- ---------
Sanders 98357.50
Fraye 91150.00
Lu 90010.00
Quill 89818.00
Lea 88555.50
Smith 87654.50
Lundquist 83369.80
Molinare 82959.20
Jones 81234.00
Hanes 80659.80
10 record(s) selected.
页:
[1]