SQL堂上练习一
显示某个表所有的内容
select * from departments;
输出如下
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID ------------- ------------------------------------------------------------ ---------- ----------- 10 Administration 200 1700 20 Marketing 201 1800 30 Purchasing 114 1700 40 Human Resources 203 2400 50 Shipping 121 1500 60 IT 103 1400 70 Public Relations 204 2700 80 Sales 145 2500 90 Executive 100 1700 100 Finance 108 1700 110 Accounting 205 1700 120 Treasury 1700 130 Corporate Tax 1700 140 Control And Credit 1700 150 Shareholder Services 1700 160 Benefits 1700 170 Manufacturing 1700 180 Construction 1700 190 Contracting 1700 200 Operations 1700 210 IT Support 1700 220 NOC 1700 230 IT Helpdesk 1700 240 Government Sales 1700 250 Retail Sales 1700 260 Recruiting 1700 270 Payroll 1700 27 rows selected.
备注：如果输出出现了乱码，换行非常频繁，可以输入以下的语句调整单行的显示量
set line 200
数据库select语句显示特定的列
有时候我们不需要显示特别多的列，只需要显示特定的列，我们可以输入以下的指令
select department_id, location_id FROM departments;
这样只会显示两列的信息，我们不需要的信息不会输出
DEPARTMENT_ID LOCATION_ID ------------- ----------- 10 1700 20 1800 30 1700 40 2400 50 1500 60 1400 70 2700 80 2500 90 1700 100 1700 110 1700 120 1700 130 1700 140 1700 150 1700 160 1700 170 1700 180 1700 190 1700 200 1700 210 1700 220 1700 230 1700 240 1700 250 1700 260 1700 270 1700 27 rows selected.
使用算数运算符号
SQL中显示出来的内容，是可以经过运算后输出的，命令如下
SELECT last_name, salary, salary + 300 FROM employees;
我们会产生如下的输出
LAST_NAME SALARY SALARY+300 -------------------------------------------------- ---------- ---------- King 24000 24300 Kochhar 17000 17300 De Haan 17000 17300 Hunold 9000 9300 Ernst 6000 6300 Austin 4800 5100 Pataballa 4800 5100 Lorentz 4200 4500 Greenberg 12000 12300 Faviet 9000 9300 Chen 8200 8500 Sciarra 7700 8000 Urman 7800 8100 Popp 6900 7200 Raphaely 11000 11300 Khoo 3100 3400 Baida 2900 3200 Tobias 2800 3100 Himuro 2600 2900 Colmenares 2500 2800 Weiss 8000 8300 Fripp 8200 8500 Kaufling 7900 8200 Vollman 6500 6800 Mourgos 5800 6100 Nayer 3200 3500 Mikkilineni 2700 3000 Landry 2400 2700 Markle 2200 2500 Bissot 3300 3600 Atkinson 2800 3100 Marlow 2500 2800 Olson 2100 2400 Mallin 3300 3600 Rogers 2900 3200 Gee 2400 2700 Philtanker 2200 2500 Ladwig 3600 3900 Stiles 3200 3500 Seo 2700 3000 Patel 2500 2800 Rajs 3500 3800 Davies 3100 3400 Matos 2600 2900 Vargas 2500 2800 Russell 14000 14300 Partners 13500 13800 LAST_NAME SALARY SALARY+300 -------------------------------------------------- ---------- ---------- Errazuriz 12000 12300 Cambrault 11000 11300 Zlotkey 10500 10800 Tucker 10000 10300 Bernstein 9500 9800 Hall 9000 9300 Olsen 8000 8300 Cambrault 7500 7800 Tuvault 7000 7300 King 10000 10300 Sully 9500 9800 McEwen 9000 9300 Smith 8000 8300 Doran 7500 7800 Sewall 7000 7300 Vishney 10500 10800 Greene 9500 9800 Marvins 7200 7500 Lee 6800 7100 Ande 6400 6700 Banda 6200 6500 Ozer 11500 11800 Bloom 10000 10300 Fox 9600 9900 Smith 7400 7700 Bates 7300 7600 Kumar 6100 6400 Abel 11000 11300 Hutton 8800 9100 Taylor 8600 8900 Livingston 8400 8700 Grant 7000 7300 Johnson 6200 6500 Taylor 3200 3500 Fleaur 3100 3400 Sullivan 2500 2800 Geoni 2800 3100 Sarchand 4200 4500 Bull 4100 4400 Dellinger 3400 3700 Cabrio 3000 3300 Chung 3800 4100 Dilly 3600 3900 Gates 2900 3200 Perkins 2500 2800 Bell 4000 4300 Everett 3900 4200 LAST_NAME SALARY SALARY+300 -------------------------------------------------- ---------- ---------- McCain 3200 3500 Jones 2800 3100 Walsh 3100 3400 Feeney 3000 3300 OConnell 2600 2900 Grant 2600 2900 Whalen 4400 4700 Hartstein 13000 13300 Fay 6000 6300 Mavris 6500 6800 Baer 10000 10300 Higgins 12000 12300 Gietz 8300 8600 107 rows selected.
SELECT语句中的四则运算
SQL还支持一些更复杂的运算
SELECT last_name, salary, 12*salary+100 FROM employees;
输出如下
LAST_NAME SALARY 12*SALARY+100 -------------------------------------------------- ---------- ------------- King 24000 288100 Kochhar 17000 204100 De Haan 17000 204100 Hunold 9000 108100 Ernst 6000 72100 Austin 4800 57700 Pataballa 4800 57700 Lorentz 4200 50500 Greenberg 12000 144100 Faviet 9000 108100 Chen 8200 98500 Sciarra 7700 92500 Urman 7800 93700 Popp 6900 82900 Raphaely 11000 132100 Khoo 3100 37300 Baida 2900 34900 Tobias 2800 33700 Himuro 2600 31300 Colmenares 2500 30100 Weiss 8000 96100 Fripp 8200 98500 Kaufling 7900 94900 Vollman 6500 78100 Mourgos 5800 69700 Nayer 3200 38500 Mikkilineni 2700 32500 Landry 2400 28900 Markle 2200 26500 Bissot 3300 39700 Atkinson 2800 33700 Marlow 2500 30100 Olson 2100 25300 Mallin 3300 39700 Rogers 2900 34900 Gee 2400 28900 Philtanker 2200 26500 Ladwig 3600 43300 Stiles 3200 38500 Seo 2700 32500 Patel 2500 30100 Rajs 3500 42100 Davies 3100 37300 Matos 2600 31300 Vargas 2500 30100 Russell 14000 168100 Partners 13500 162100 LAST_NAME SALARY 12*SALARY+100 -------------------------------------------------- ---------- ------------- Errazuriz 12000 144100 Cambrault 11000 132100 Zlotkey 10500 126100 Tucker 10000 120100 Bernstein 9500 114100 Hall 9000 108100 Olsen 8000 96100 Cambrault 7500 90100 Tuvault 7000 84100 King 10000 120100 Sully 9500 114100 McEwen 9000 108100 Smith 8000 96100 Doran 7500 90100 Sewall 7000 84100 Vishney 10500 126100 Greene 9500 114100 Marvins 7200 86500 Lee 6800 81700 Ande 6400 76900 Banda 6200 74500 Ozer 11500 138100 Bloom 10000 120100 Fox 9600 115300 Smith 7400 88900 Bates 7300 87700 Kumar 6100 73300 Abel 11000 132100 Hutton 8800 105700 Taylor 8600 103300 Livingston 8400 100900 Grant 7000 84100 Johnson 6200 74500 Taylor 3200 38500 Fleaur 3100 37300 Sullivan 2500 30100 Geoni 2800 33700 Sarchand 4200 50500 Bull 4100 49300 Dellinger 3400 40900 Cabrio 3000 36100 Chung 3800 45700 Dilly 3600 43300 Gates 2900 34900 Perkins 2500 30100 Bell 4000 48100 Everett 3900 46900 LAST_NAME SALARY 12*SALARY+100 -------------------------------------------------- ---------- ------------- McCain 3200 38500 Jones 2800 33700 Walsh 3100 37300 Feeney 3000 36100 OConnell 2600 31300 Grant 2600 31300 Whalen 4400 52900 Hartstein 13000 156100 Fay 6000 72100 Mavris 6500 78100 Baer 10000 120100 Higgins 12000 144100 Gietz 8300 99700 107 rows selected.
sql支持带括号运算的列
SELECT last_name, salary, 12*(salary+100) FROM employees;
输出如下
LAST_NAME SALARY 12*(SALARY+100) -------------------------------------------------- ---------- --------------- King 24000 289200 Kochhar 17000 205200 De Haan 17000 205200 Hunold 9000 109200 Ernst 6000 73200 Austin 4800 58800 Pataballa 4800 58800 Lorentz 4200 51600 Greenberg 12000 145200 Faviet 9000 109200 Chen 8200 99600 LAST_NAME SALARY 12*(SALARY+100) -------------------------------------------------- ---------- --------------- Sciarra 7700 93600 Urman 7800 94800 Popp 6900 84000 Raphaely 11000 133200 Khoo 3100 38400 Baida 2900 36000 Tobias 2800 34800 Himuro 2600 32400 Colmenares 2500 31200 Weiss 8000 97200 Fripp 8200 99600 LAST_NAME SALARY 12*(SALARY+100) -------------------------------------------------- ---------- --------------- Kaufling 7900 96000 Vollman 6500 79200 Mourgos 5800 70800 Nayer 3200 39600 Mikkilineni 2700 33600 Landry 2400 30000 Markle 2200 27600 Bissot 3300 40800 Atkinson 2800 34800 Marlow 2500 31200 Olson 2100 26400 LAST_NAME SALARY 12*(SALARY+100) -------------------------------------------------- ---------- --------------- Mallin 3300 40800 Rogers 2900 36000 Gee 2400 30000 Philtanker 2200 27600 Ladwig 3600 44400 Stiles 3200 39600 Seo 2700 33600 Patel 2500 31200 Rajs 3500 43200 Davies 3100 38400 Matos 2600 32400 LAST_NAME SALARY 12*(SALARY+100) -------------------------------------------------- ---------- --------------- Vargas 2500 31200 Russell 14000 169200 Partners 13500 163200 Errazuriz 12000 145200 Cambrault 11000 133200 Zlotkey 10500 127200 Tucker 10000 121200 Bernstein 9500 115200 Hall 9000 109200 Olsen 8000 97200 Cambrault 7500 91200 LAST_NAME SALARY 12*(SALARY+100) -------------------------------------------------- ---------- --------------- Tuvault 7000 85200 King 10000 121200 Sully 9500 115200 McEwen 9000 109200 Smith 8000 97200 Doran 7500 91200 Sewall 7000 85200 Vishney 10500 127200 Greene 9500 115200 Marvins 7200 87600 Lee 6800 82800 LAST_NAME SALARY 12*(SALARY+100) -------------------------------------------------- ---------- --------------- Ande 6400 78000 Banda 6200 75600 Ozer 11500 139200 Bloom 10000 121200 Fox 9600 116400 Smith 7400 90000 Bates 7300 88800 Kumar 6100 74400 Abel 11000 133200 Hutton 8800 106800 Taylor 8600 104400 LAST_NAME SALARY 12*(SALARY+100) -------------------------------------------------- ---------- --------------- Livingston 8400 102000 Grant 7000 85200 Johnson 6200 75600 Taylor 3200 39600 Fleaur 3100 38400 Sullivan 2500 31200 Geoni 2800 34800 Sarchand 4200 51600 Bull 4100 50400 Dellinger 3400 42000 Cabrio 3000 37200 LAST_NAME SALARY 12*(SALARY+100) -------------------------------------------------- ---------- --------------- Chung 3800 46800 Dilly 3600 44400 Gates 2900 36000 Perkins 2500 31200 Bell 4000 49200 Everett 3900 48000 McCain 3200 39600 Jones 2800 34800 Walsh 3100 38400 Feeney 3000 37200 OConnell 2600 32400 LAST_NAME SALARY 12*(SALARY+100) -------------------------------------------------- ---------- --------------- Grant 2600 32400 Whalen 4400 54000 Hartstein 13000 157200 Fay 6000 73200 Mavris 6500 79200 Baer 10000 121200 Higgins 12000 145200 Gietz 8300 100800 已选择107行。
关于空值
ORACLE允许任何一种数据类型的字段为空（NULL）除非是主键或定义了非空
注意空不等于全是空格
SELECT last_name, job_id, salary, commission_pct FROM employees;
输出如下，可以发现部分COMMISSION_PCT的输出为空
LAST_NAME JOB_ID SALARY COMMISSION_PCT -------------------------------------------------- -------------------- ---------- -------------- King AD_PRES 24000 Kochhar AD_VP 17000 De Haan AD_VP 17000 Hunold IT_PROG 9000 Ernst IT_PROG 6000 Austin IT_PROG 4800 Pataballa IT_PROG 4800 Lorentz IT_PROG 4200 Greenberg FI_MGR 12000 Faviet FI_ACCOUNT 9000 Chen FI_ACCOUNT 8200 LAST_NAME JOB_ID SALARY COMMISSION_PCT -------------------------------------------------- -------------------- ---------- -------------- Sciarra FI_ACCOUNT 7700 Urman FI_ACCOUNT 7800 Popp FI_ACCOUNT 6900 Raphaely PU_MAN 11000 Khoo PU_CLERK 3100 Baida PU_CLERK 2900 Tobias PU_CLERK 2800 Himuro PU_CLERK 2600 Colmenares PU_CLERK 2500 Weiss ST_MAN 8000 Fripp ST_MAN 8200 LAST_NAME JOB_ID SALARY COMMISSION_PCT -------------------------------------------------- -------------------- ---------- -------------- Kaufling ST_MAN 7900 Vollman ST_MAN 6500 Mourgos ST_MAN 5800 Nayer ST_CLERK 3200 Mikkilineni ST_CLERK 2700 Landry ST_CLERK 2400 Markle ST_CLERK 2200 Bissot ST_CLERK 3300 Atkinson ST_CLERK 2800 Marlow ST_CLERK 2500 Olson ST_CLERK 2100 LAST_NAME JOB_ID SALARY COMMISSION_PCT -------------------------------------------------- -------------------- ---------- -------------- Mallin ST_CLERK 3300 Rogers ST_CLERK 2900 Gee ST_CLERK 2400 Philtanker ST_CLERK 2200 Ladwig ST_CLERK 3600 Stiles ST_CLERK 3200 Seo ST_CLERK 2700 Patel ST_CLERK 2500 Rajs ST_CLERK 3500 Davies ST_CLERK 3100 Matos ST_CLERK 2600 LAST_NAME JOB_ID SALARY COMMISSION_PCT -------------------------------------------------- -------------------- ---------- -------------- Vargas ST_CLERK 2500 Russell SA_MAN 14000 .4 Partners SA_MAN 13500 .3 Errazuriz SA_MAN 12000 .3 Cambrault SA_MAN 11000 .3 Zlotkey SA_MAN 10500 .2 Tucker SA_REP 10000 .3 Bernstein SA_REP 9500 .25 Hall SA_REP 9000 .25 Olsen SA_REP 8000 .2 Cambrault SA_REP 7500 .2 LAST_NAME JOB_ID SALARY COMMISSION_PCT -------------------------------------------------- -------------------- ---------- -------------- Tuvault SA_REP 7000 .15 King SA_REP 10000 .35 Sully SA_REP 9500 .35 McEwen SA_REP 9000 .35 Smith SA_REP 8000 .3 Doran SA_REP 7500 .3 Sewall SA_REP 7000 .25 Vishney SA_REP 10500 .25 Greene SA_REP 9500 .15 Marvins SA_REP 7200 .1 Lee SA_REP 6800 .1 LAST_NAME JOB_ID SALARY COMMISSION_PCT -------------------------------------------------- -------------------- ---------- -------------- Ande SA_REP 6400 .1 Banda SA_REP 6200 .1 Ozer SA_REP 11500 .25 Bloom SA_REP 10000 .2 Fox SA_REP 9600 .2 Smith SA_REP 7400 .15 Bates SA_REP 7300 .15 Kumar SA_REP 6100 .1 Abel SA_REP 11000 .3 Hutton SA_REP 8800 .25 Taylor SA_REP 8600 .2 LAST_NAME JOB_ID SALARY COMMISSION_PCT -------------------------------------------------- -------------------- ---------- -------------- Livingston SA_REP 8400 .2 Grant SA_REP 7000 .15 Johnson SA_REP 6200 .1 Taylor SH_CLERK 3200 Fleaur SH_CLERK 3100 Sullivan SH_CLERK 2500 Geoni SH_CLERK 2800 Sarchand SH_CLERK 4200 Bull SH_CLERK 4100 Dellinger SH_CLERK 3400 Cabrio SH_CLERK 3000 LAST_NAME JOB_ID SALARY COMMISSION_PCT -------------------------------------------------- -------------------- ---------- -------------- Chung SH_CLERK 3800 Dilly SH_CLERK 3600 Gates SH_CLERK 2900 Perkins SH_CLERK 2500 Bell SH_CLERK 4000 Everett SH_CLERK 3900 McCain SH_CLERK 3200 Jones SH_CLERK 2800 Walsh SH_CLERK 3100 Feeney SH_CLERK 3000 OConnell SH_CLERK 2600 LAST_NAME JOB_ID SALARY COMMISSION_PCT -------------------------------------------------- -------------------- ---------- -------------- Grant SH_CLERK 2600 Whalen AD_ASST 4400 Hartstein MK_MAN 13000 Fay MK_REP 6000 Mavris HR_REP 6500 Baer PR_REP 10000 Higgins AC_MGR 12000 Gietz AC_ACCOUNT 8300 已选择107行。
关于空值表达式
SQL表达式中，若有一个元素为NULL，则整个表达式的值均为空
甚至形如a*b+c*d的表达式，若a为NULL且bcd均不为NULL，则表达式的值也为空
SELECT last_name, 12*salary*commission_pct FROM employees;
输出如下
LAST_NAME 12*SALARY*COMMISSION_PCT -------------------------------------------------- ------------------------ King Kochhar De Haan Hunold Ernst Austin Pataballa Lorentz Greenberg Faviet Chen LAST_NAME 12*SALARY*COMMISSION_PCT -------------------------------------------------- ------------------------ Sciarra Urman Popp Raphaely Khoo Baida Tobias Himuro Colmenares Weiss Fripp LAST_NAME 12*SALARY*COMMISSION_PCT -------------------------------------------------- ------------------------ Kaufling Vollman Mourgos Nayer Mikkilineni Landry Markle Bissot Atkinson Marlow Olson LAST_NAME 12*SALARY*COMMISSION_PCT -------------------------------------------------- ------------------------ Mallin Rogers Gee Philtanker Ladwig Stiles Seo Patel Rajs Davies Matos LAST_NAME 12*SALARY*COMMISSION_PCT -------------------------------------------------- ------------------------ Vargas Russell 67200 Partners 48600 Errazuriz 43200 Cambrault 39600 Zlotkey 25200 Tucker 36000 Bernstein 28500 Hall 27000 Olsen 19200 Cambrault 18000 LAST_NAME 12*SALARY*COMMISSION_PCT -------------------------------------------------- ------------------------ Tuvault 12600 King 42000 Sully 39900 McEwen 37800 Smith 28800 Doran 27000 Sewall 21000 Vishney 31500 Greene 17100 Marvins 8640 Lee 8160 LAST_NAME 12*SALARY*COMMISSION_PCT -------------------------------------------------- ------------------------ Ande 7680 Banda 7440 Ozer 34500 Bloom 24000 Fox 23040 Smith 13320 Bates 13140 Kumar 7320 Abel 39600 Hutton 26400 Taylor 20640 LAST_NAME 12*SALARY*COMMISSION_PCT -------------------------------------------------- ------------------------ Livingston 20160 Grant 12600 Johnson 7440 Taylor Fleaur Sullivan Geoni Sarchand Bull Dellinger Cabrio LAST_NAME 12*SALARY*COMMISSION_PCT -------------------------------------------------- ------------------------ Chung Dilly Gates Perkins Bell Everett McCain Jones Walsh Feeney OConnell LAST_NAME 12*SALARY*COMMISSION_PCT -------------------------------------------------- ------------------------ Grant Whalen Hartstein Fay Mavris Baer Higgins Gietz 已选择107行。
别名定义
SQL支持给列名定义别名，可以通过在需要输出的列明后面增加as xxx来表示。
SELECT last_name AS name, commission_pct comm FROM employees;
输出如下
NAME COMM -------------------------------------------------- ---------- King Kochhar De Haan Hunold Ernst Austin Pataballa Lorentz Greenberg Faviet Chen NAME COMM -------------------------------------------------- ---------- Sciarra Urman Popp Raphaely Khoo Baida Tobias Himuro Colmenares Weiss Fripp NAME COMM -------------------------------------------------- ---------- Kaufling Vollman Mourgos Nayer Mikkilineni Landry Markle Bissot Atkinson Marlow Olson NAME COMM -------------------------------------------------- ---------- Mallin Rogers Gee Philtanker Ladwig Stiles Seo Patel Rajs Davies Matos NAME COMM -------------------------------------------------- ---------- Vargas Russell .4 Partners .3 Errazuriz .3 Cambrault .3 Zlotkey .2 Tucker .3 Bernstein .25 Hall .25 Olsen .2 Cambrault .2 NAME COMM -------------------------------------------------- ---------- Tuvault .15 King .35 Sully .35 McEwen .35 Smith .3 Doran .3 Sewall .25 Vishney .25 Greene .15 Marvins .1 Lee .1 NAME COMM -------------------------------------------------- ---------- Ande .1 Banda .1 Ozer .25 Bloom .2 Fox .2 Smith .15 Bates .15 Kumar .1 Abel .3 Hutton .25 Taylor .2 NAME COMM -------------------------------------------------- ---------- Livingston .2 Grant .15 Johnson .1 Taylor Fleaur Sullivan Geoni Sarchand Bull Dellinger Cabrio NAME COMM -------------------------------------------------- ---------- Chung Dilly Gates Perkins Bell Everett McCain Jones Walsh Feeney OConnell NAME COMM -------------------------------------------------- ---------- Grant Whalen Hartstein Fay Mavris Baer Higgins Gietz 已选择107行。
注意，我们不仅可以用AS xxxx的形式进行定义，我们还可以用AS "xxxx"的形式定义别名，这样别名中就可以包含大写字母和空格了。
SELECT last_name "Name", salary*12 "Annual Salary" FROM employees;
输出如下
Name Annual Salary -------------------------------------------------- ------------- King 288000 Kochhar 204000 De Haan 204000 Hunold 108000 Ernst 72000 Austin 57600 Pataballa 57600 Lorentz 50400 Greenberg 144000 Faviet 108000 Chen 98400 Name Annual Salary -------------------------------------------------- ------------- Sciarra 92400 Urman 93600 Popp 82800 Raphaely 132000 Khoo 37200 Baida 34800 Tobias 33600 Himuro 31200 Colmenares 30000 Weiss 96000 Fripp 98400 Name Annual Salary -------------------------------------------------- ------------- Kaufling 94800 Vollman 78000 Mourgos 69600 Nayer 38400 Mikkilineni 32400 Landry 28800 Markle 26400 Bissot 39600 Atkinson 33600 Marlow 30000 Olson 25200 Name Annual Salary -------------------------------------------------- ------------- Mallin 39600 Rogers 34800 Gee 28800 Philtanker 26400 Ladwig 43200 Stiles 38400 Seo 32400 Patel 30000 Rajs 42000 Davies 37200 Matos 31200 Name Annual Salary -------------------------------------------------- ------------- Vargas 30000 Russell 168000 Partners 162000 Errazuriz 144000 Cambrault 132000 Zlotkey 126000 Tucker 120000 Bernstein 114000 Hall 108000 Olsen 96000 Cambrault 90000 Name Annual Salary -------------------------------------------------- ------------- Tuvault 84000 King 120000 Sully 114000 McEwen 108000 Smith 96000 Doran 90000 Sewall 84000 Vishney 126000 Greene 114000 Marvins 86400 Lee 81600 Name Annual Salary -------------------------------------------------- ------------- Ande 76800 Banda 74400 Ozer 138000 Bloom 120000 Fox 115200 Smith 88800 Bates 87600 Kumar 73200 Abel 132000 Hutton 105600 Taylor 103200 Name Annual Salary -------------------------------------------------- ------------- Livingston 100800 Grant 84000 Johnson 74400 Taylor 38400 Fleaur 37200 Sullivan 30000 Geoni 33600 Sarchand 50400 Bull 49200 Dellinger 40800 Cabrio 36000 Name Annual Salary -------------------------------------------------- ------------- Chung 45600 Dilly 43200 Gates 34800 Perkins 30000 Bell 48000 Everett 46800 McCain 38400 Jones 33600 Walsh 37200 Feeney 36000 OConnell 31200 Name Annual Salary -------------------------------------------------- ------------- Grant 31200 Whalen 52800 Hartstein 156000 Fay 72000 Mavris 78000 Baer 120000 Higgins 144000 Gietz 99600 已选择107行。
字符串连接符
SQL运算中，支持连接符，我们可以通过连接符将两个列中的元素在一个列中显示
SELECT last_name||job_id AS "Employees" FROM employees;
输出如下
Employees ---------------------------------------------------------------------- KingAD_PRES KochharAD_VP De HaanAD_VP HunoldIT_PROG ErnstIT_PROG AustinIT_PROG PataballaIT_PROG LorentzIT_PROG GreenbergFI_MGR FavietFI_ACCOUNT ChenFI_ACCOUNT Employees ---------------------------------------------------------------------- SciarraFI_ACCOUNT UrmanFI_ACCOUNT PoppFI_ACCOUNT RaphaelyPU_MAN KhooPU_CLERK BaidaPU_CLERK TobiasPU_CLERK HimuroPU_CLERK ColmenaresPU_CLERK WeissST_MAN FrippST_MAN Employees ---------------------------------------------------------------------- KauflingST_MAN VollmanST_MAN MourgosST_MAN NayerST_CLERK MikkilineniST_CLERK LandryST_CLERK MarkleST_CLERK BissotST_CLERK AtkinsonST_CLERK MarlowST_CLERK OlsonST_CLERK Employees ---------------------------------------------------------------------- MallinST_CLERK RogersST_CLERK GeeST_CLERK PhiltankerST_CLERK LadwigST_CLERK StilesST_CLERK SeoST_CLERK PatelST_CLERK RajsST_CLERK DaviesST_CLERK MatosST_CLERK Employees ---------------------------------------------------------------------- VargasST_CLERK RussellSA_MAN PartnersSA_MAN ErrazurizSA_MAN CambraultSA_MAN ZlotkeySA_MAN TuckerSA_REP BernsteinSA_REP HallSA_REP OlsenSA_REP CambraultSA_REP Employees ---------------------------------------------------------------------- TuvaultSA_REP KingSA_REP SullySA_REP McEwenSA_REP SmithSA_REP DoranSA_REP SewallSA_REP VishneySA_REP GreeneSA_REP MarvinsSA_REP LeeSA_REP Employees ---------------------------------------------------------------------- AndeSA_REP BandaSA_REP OzerSA_REP BloomSA_REP FoxSA_REP SmithSA_REP BatesSA_REP KumarSA_REP AbelSA_REP HuttonSA_REP TaylorSA_REP Employees ---------------------------------------------------------------------- LivingstonSA_REP GrantSA_REP JohnsonSA_REP TaylorSH_CLERK FleaurSH_CLERK SullivanSH_CLERK GeoniSH_CLERK SarchandSH_CLERK BullSH_CLERK DellingerSH_CLERK CabrioSH_CLERK Employees ---------------------------------------------------------------------- ChungSH_CLERK DillySH_CLERK GatesSH_CLERK PerkinsSH_CLERK BellSH_CLERK EverettSH_CLERK McCainSH_CLERK JonesSH_CLERK WalshSH_CLERK FeeneySH_CLERK OConnellSH_CLERK Employees ---------------------------------------------------------------------- GrantSH_CLERK WhalenAD_ASST HartsteinMK_MAN FayMK_REP MavrisHR_REP BaerPR_REP HigginsAC_MGR GietzAC_ACCOUNT 已选择107行。
在SQL连接符的帮助下，我们不仅仅可以链接数据库中的字符，还可以将字符串常量插入其中
SELECT last_name ||' is a '||job_id AS "Employee Details" FROM employees;
输出如下
Employee Details ---------------------------------------------------------------------------------- King is a AD_PRES Kochhar is a AD_VP De Haan is a AD_VP Hunold is a IT_PROG Ernst is a IT_PROG Austin is a IT_PROG Pataballa is a IT_PROG Lorentz is a IT_PROG Greenberg is a FI_MGR Faviet is a FI_ACCOUNT Chen is a FI_ACCOUNT Employee Details ---------------------------------------------------------------------------------- Sciarra is a FI_ACCOUNT Urman is a FI_ACCOUNT Popp is a FI_ACCOUNT Raphaely is a PU_MAN Khoo is a PU_CLERK Baida is a PU_CLERK Tobias is a PU_CLERK Himuro is a PU_CLERK Colmenares is a PU_CLERK Weiss is a ST_MAN Fripp is a ST_MAN Employee Details ---------------------------------------------------------------------------------- Kaufling is a ST_MAN Vollman is a ST_MAN Mourgos is a ST_MAN Nayer is a ST_CLERK Mikkilineni is a ST_CLERK Landry is a ST_CLERK Markle is a ST_CLERK Bissot is a ST_CLERK Atkinson is a ST_CLERK Marlow is a ST_CLERK Olson is a ST_CLERK Employee Details ---------------------------------------------------------------------------------- Mallin is a ST_CLERK Rogers is a ST_CLERK Gee is a ST_CLERK Philtanker is a ST_CLERK Ladwig is a ST_CLERK Stiles is a ST_CLERK Seo is a ST_CLERK Patel is a ST_CLERK Rajs is a ST_CLERK Davies is a ST_CLERK Matos is a ST_CLERK Employee Details ---------------------------------------------------------------------------------- Vargas is a ST_CLERK Russell is a SA_MAN Partners is a SA_MAN Errazuriz is a SA_MAN Cambrault is a SA_MAN Zlotkey is a SA_MAN Tucker is a SA_REP Bernstein is a SA_REP Hall is a SA_REP Olsen is a SA_REP Cambrault is a SA_REP Employee Details ---------------------------------------------------------------------------------- Tuvault is a SA_REP King is a SA_REP Sully is a SA_REP McEwen is a SA_REP Smith is a SA_REP Doran is a SA_REP Sewall is a SA_REP Vishney is a SA_REP Greene is a SA_REP Marvins is a SA_REP Lee is a SA_REP Employee Details ---------------------------------------------------------------------------------- Ande is a SA_REP Banda is a SA_REP Ozer is a SA_REP Bloom is a SA_REP Fox is a SA_REP Smith is a SA_REP Bates is a SA_REP Kumar is a SA_REP Abel is a SA_REP Hutton is a SA_REP Taylor is a SA_REP Employee Details ---------------------------------------------------------------------------------- Livingston is a SA_REP Grant is a SA_REP Johnson is a SA_REP Taylor is a SH_CLERK Fleaur is a SH_CLERK Sullivan is a SH_CLERK Geoni is a SH_CLERK Sarchand is a SH_CLERK Bull is a SH_CLERK Dellinger is a SH_CLERK Cabrio is a SH_CLERK Employee Details ---------------------------------------------------------------------------------- Chung is a SH_CLERK Dilly is a SH_CLERK Gates is a SH_CLERK Perkins is a SH_CLERK Bell is a SH_CLERK Everett is a SH_CLERK McCain is a SH_CLERK Jones is a SH_CLERK Walsh is a SH_CLERK Feeney is a SH_CLERK OConnell is a SH_CLERK Employee Details ---------------------------------------------------------------------------------- Grant is a SH_CLERK Whalen is a AD_ASST Hartstein is a MK_MAN Fay is a MK_REP Mavris is a HR_REP Baer is a PR_REP Higgins is a AC_MGR Gietz is a AC_ACCOUNT 已选择107行。
输出设置
在默认情况下，一个页(page)的大小为10，所以每输出十条就会有一个换行+一个新的表头
通过set pagesize命令，我们可以大幅增大一个页的大小
set pagesize 1000
相同值处理
在SQL中，如果有多行的值完全相同，在默认情况下将会全部输出
SELECT department_id FROM employees;
输出如下
DEPARTMENT_ID ------------- 90 90 90 60 60 60 60 60 100 100 100 100 100 100 30 30 30 30 30 30 50 50 50 50 50 50 50 50 50 50 50 50 50 50 50 50 50 50 50 50 50 50 50 50 50 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 50 50 50 50 50 50 50 50 50 50 50 50 50 50 50 50 50 50 50 50 10 20 20 40 70 110 110 已选择107行。
我们可以在SELECT中增加一个DISTINCT指令，这样就可以只让SQL输出不同的数值了
SELECT DISTINCT department_id FROM employees;
输出如下
DEPARTMENT_ID ------------- 100 30 90 20 70 110 50 80 40 60 10 已选择12行。
显示表的结构
我们可以用DESCRIBE employees这条指令，显示出表employees的表结构，输出表中每个变量的类型和名称。
名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)
