数据操作语言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 Sale