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.
?
?
?
?
?
?
?
?
?
?
?
