數據庫編程1 Oracle 過濾函數分組外連接自連接

【本文謝絕轉載原文來自http://990487026.blog.51cto.com】

<大綱>數據庫編程1?Oracle?過濾?函數?分組?外連接?自連接本文實驗基于的數據表:winsows安裝好Oracle11g之后,開始實驗SQLplus?登陸?ORaclesqlplus?退出的方式查看用戶之下有什么表查看表的所有記錄，不區分大小寫設置SQLplus行寬,頁寬,列寬：清屏命令select?as?語法1,as別名的使用2,沒有引號帶有空格的別名,無法識別:3,帶有空格的別名解決辦法:年收入拋出問題,年收入顯示為空解決辦法,nvl(a,b)函數1,NULL空值?任何數與NULL運算都是NULL2,空值不是空?null?!=?null	ed修改上一條命令:保存退出,/開始執行	where?過濾,查詢獎金為空的員工:	||連接符,與虛表	dual虛表	DISTINCT?刪除重復行	DISTINCT修飾多個字段時,當兩個數據完全一樣才認為是重復的	SQLplus工具的?help	edit的幫助:	Oracle系統常用的參數	默認的時間顯示	臨時修改系統時間的顯示方式:	where過濾字句?>?大于	where過濾字句between?and	where過濾字句between?A??and?B,A?必須小于等于?B	where過濾字句?in	where過濾字句?not?in	where過濾字句?not?in?與null在一起,受影響	where?like模糊查詢,查詢員工姓名S開頭的信息:	where?like模糊查詢,查詢員工姓名是4個字母	where?like模糊查詢,轉義字符	where?order?by?工資排序,默認升序	where?order?by?別名	order?by?遇到null1,任何表達式與null運算都為空2,null!=nullnulls?last?把null放在最后	order修飾多個列,desc的作用范圍只是最近的哪一個!	count函數字符函數大小寫轉換函數	LOWER	UPPER	INITCAP字符串連接:	concatsubstr(a,b)?從a中第b位開始取字符串	length?字符數lengthb?字節數instr求子串的位置lpad,rpad左填充右填充:trim去除前后指定字符:replace?字符串替換數字函數round?四舍五入tuec截斷函數mod求余函數to_char格式化輸出兩個日期相減,返回日期之間相差的天數	精確顯示員工入職月數,時間運算months_between	add_months()向指定的日期中加若干的月數	3,1隱式轉換,字符串轉日期	3,1顯示轉換,字符串轉日期	字符串與字符串比較	to_char()對數字轉字符	to_number()字符轉數字	通用函數	nvl2(a,b,c)當a為null時返回c,否則返回bnullif(a,b)當a,b相等時返回NULL,否則返回a案例:總裁漲1000,經理漲800,其他漲500	方法1,SQL規范方法:方法2:Oracle專有方法組函數:	avg?sum?max?min?count?組函數與null在一起組函數會自動過濾空值:修正:分組數據:	求出各個部門的平均工資分組最難的地方:	having?分組過濾:	求10號部門的平均工資的兩種方法	1,先分組再過濾2,先過濾再分組SQL優化問題:	多表查詢:	[等值連接],顯示員工信息,員工號,姓名,月薪	[不等值連接]顯示員工信息,員工號,姓名,月薪,薪水級別	多表查詢4,求每個部門的人數:(需要分組)	[少了一個部門,這個方法有漏洞,因為40號部門沒有員工]?	[外連接]技術引出:	右外連接,+在等號的左邊左外連接,+在等號的右邊[難點]:[自連接]顯示老板的信息:[優化顯示][再優化][難點]:查詢每個部門的人數

[說明]本文實驗基于以下數據表

dept表;
emp表;
salgrade表;
______________________________________________________________________________
SQL>?select?*?from?dept;DEPTNO?DNAME????????????????????????LOC
----------?----------------------------?--------------------------10?ACCOUNTING???????????????????NEW?YORK20?RESEARCH?????????????????????DALLAS30?SALES????????????????????????CHICAGO40?OPERATIONS???????????????????BOSTON
______________________________________________________________________________SQL>?select?*?from?emp;EMPNO?ENAME????????????????JOB???????????????????????MGR?HIREDATE??????????????SAL???????COMM?????DEPTNO
----------?--------------------?------------------?----------?--------------?----------?----------?----------7369?SMITH????????????????CLERK????????????????????7902?17-12月-80????????????800?????????????????207499?ALLEN????????????????SALESMAN?????????????????7698?20-2月?-81???????????1600????????300??????307521?WARD?????????????????SALESMAN?????????????????7698?22-2月?-81???????????1250????????500??????307566?JONES????????????????MANAGER??????????????????7839?02-4月?-81???????????2975?????????????????207654?MARTIN???????????????SALESMAN?????????????????7698?28-9月?-81???????????1250???????1400??????307698?BLAKE????????????????MANAGER??????????????????7839?01-5月?-81???????????2850?????????????????307782?CLARK????????????????MANAGER??????????????????7839?09-6月?-81???????????2450?????????????????107788?SCOTT????????????????ANALYST??????????????????7566?19-4月?-87???????????3000?????????????????207839?KING?????????????????PRESIDENT?????????????????????17-11月-81???????????5000?????????????????107844?TURNER???????????????SALESMAN?????????????????7698?08-9月?-81???????????1500??????????0??????307876?ADAMS????????????????CLERK????????????????????7788?23-5月?-87???????????1100?????????????????207900?JAMES????????????????CLERK????????????????????7698?03-12月-81????????????950?????????????????307902?FORD?????????????????ANALYST??????????????????7566?03-12月-81???????????3000?????????????????207934?MILLER???????????????CLERK????????????????????7782?23-1月?-82???????????1300?????????????????101?tom_abc?????????????????????????????????????????????????????????????????8000????????????????????10已選擇15行。
______________________________________________________________________________SQL>?select?*?from?salgrade;GRADE??????LOSAL??????HISAL
----------?----------?----------1????????700???????12002???????1201???????14003???????1401???????20004???????2001???????30005???????3001???????9999
______________________________________________________________________________

winsows安裝好Oracle11g之后,

實驗基于[OracleServiceORCL]后臺服務進程的開啟:

以超級用戶方式登錄：

C:\Users\VMware>sqlplus?/nolog
SQL*Plus:?Release?11.2.0.1.0?Production?on?星期三?8月?24?22:03:26?2016
Copyright?(c)?1982,?2010,?Oracle.??All?rights?reserved.SQL>?connect?/as?sysdba
已連接。?
SQL>

1,SQLplus 登陸 ORacle:

winsows安裝好Oracle11g之后，修改scott的密碼

C:\Users\VMware>sqlplus請輸入用戶名:??scott
輸入口令:tiger
ERROR:
ORA-28001:?the?password?has?expired
更改?scott?的口令
新口令:
重新鍵入新口令:
口令已更改連接到:
Oracle?Database?11g?Enterprise?Edition?Release?11.2.0.1.0?-?64bit?Production
With?the?Partitioning,?OLAP,?Data?Mining?and?Real?Application?Testing?options

輸入賬戶和密碼，直接登錄

C:\Users\VMware>sqlplus?scott/11SQL*Plus:?Release?11.2.0.1.0?Production?on?星期三?8月?24?22:00:50?2016Copyright?(c)?1982,?2010,?Oracle.??All?rights?reserved.連接到:
Oracle?Database?11g?Enterprise?Edition?Release?11.2.0.1.0?-?64bit?Production
With?the?Partitioning,?OLAP,?Data?Mining?and?Real?Application?Testing?optionsSQL>

sqlplus 退出的方式：

C:\Users\VMware>sqlplus?scott/11
SQL>?quit
SQL>?exit

查看用戶之下有什么表：

tab是關鍵字
C:\Users\VMware>sqlplus?scott/11
SQL>?select?*?from?tab;TNAME????????????????????????????????????????????????????????TABTYPE
------------------------------------------------------------?--------------CLUSTERID
----------
BONUS????????????????????????????????????????????????????????TABLEDEPT?????????????????????????????????????????????????????????TABLEEMP??????????????????????????????????????????????????????????TABLETNAME????????????????????????????????????????????????????????TABTYPE
------------------------------------------------------------?--------------CLUSTERID
----------
SALGRADE?????????????????????????????????????????????????????TABLESQL>

查看表的所有記錄，不區分大小寫

SQL>?select?*?from?dept;DEPTNO?DNAME????????????????????????LOC
----------?----------------------------?--------------------------10?ACCOUNTING???????????????????NEW?YORK20?RESEARCH?????????????????????DALLAS30?SALES????????????????????????CHICAGO40?OPERATIONS???????????????????BOSTONSQL>

查看表結構：

SQL>?desc?dept;名稱??????????????????????????????????????是否為空??類型-----------------------------------------?--------?----------------------------DEPTNO????????????????????????????????????NOT?NULL?NUMBER(2)DNAME??????????????????????????????????????????????VARCHAR2(14)LOC????????????????????????????????????????????????VARCHAR2(13)SQL>

設置SQLplus行寬,頁寬,列寬：

設置行寬前：
SQL>?select?*?from?emp;EMPNO?ENAME????????????????JOB???????????????????????MGR?HIREDATE
----------?--------------------?------------------?----------?--------------SAL???????COMM?????DEPTNO
----------?----------?----------7369?SMITH????????????????CLERK????????????????????7902?17-12月-80800????????????????????207499?ALLEN????????????????SALESMAN?????????????????7698?20-2月?-811600????????300?????????307521?WARD?????????????????SALESMAN?????????????????7698?22-2月?-811250????????500?????????30EMPNO?ENAME????????????????JOB???????????????????????MGR?HIREDATE
----------?--------------------?------------------?----------?--------------SAL???????COMM?????DEPTNO
----------?----------?----------7566?JONES????????????????MANAGER??????????????????7839?02-4月?-812975????????????????????20
設置行寬后：
SQL>?set?linesize?999;
SQL>?select?*?from?emp;EMPNO?ENAME????????????????JOB???????????????????????MGR?HIREDATE??????????????SAL???????COMM?????DEPTNO
----------?--------------------?------------------?----------?--------------?----------?----------?----------7369?SMITH????????????????CLERK????????????????????7902?17-12月-80????????????800?????????????????207499?ALLEN????????????????SALESMAN?????????????????7698?20-2月?-81???????????1600????????300??????307521?WARD?????????????????SALESMAN?????????????????7698?22-2月?-81???????????1250????????500??????307566?JONES????????????????MANAGER??????????????????7839?02-4月?-81???????????2975?????????????????207654?MARTIN???????????????SALESMAN?????????????????7698?28-9月?-81???????????1250???????1400??????307698?BLAKE????????????????MANAGER??????????????????7839?01-5月?-81???????????2850?????????????????307782?CLARK????????????????MANAGER??????????????????7839?09-6月?-81???????????2450?????????????????107788?SCOTT????????????????ANALYST??????????????????7566?19-4月?-87???????????3000?????????????????207839?KING?????????????????PRESIDENT?????????????????????17-11月-81???????????5000?????????????????107844?TURNER???????????????SALESMAN?????????????????7698?08-9月?-81???????????1500??????????0??????307876?ADAMS????????????????CLERK????????????????????7788?23-5月?-87???????????1100?????????????????20EMPNO?ENAME????????????????JOB???????????????????????MGR?HIREDATE??????????????SAL???????COMM?????DEPTNO
----------?--------------------?------------------?----------?--------------?----------?----------?----------7900?JAMES????????????????CLERK????????????????????7698?03-12月-81????????????950?????????????????307902?FORD?????????????????ANALYST??????????????????7566?03-12月-81???????????3000?????????????????207934?MILLER???????????????CLERK????????????????????7782?23-1月?-82???????????1300?????????????????10已選擇14行。SQL>??????設置頁寬：?????
SQL>?set?pagesize?999;
SQL>?select?*?from?emp;EMPNO?ENAME????????????????JOB???????????????????????MGR?HIREDATE??????????????SAL???????COMM?????DEPTNO
----------?--------------------?------------------?----------?--------------?----------?----------?----------7369?SMITH????????????????CLERK????????????????????7902?17-12月-80????????????800?????????????????207499?ALLEN????????????????SALESMAN?????????????????7698?20-2月?-81???????????1600????????300??????307521?WARD?????????????????SALESMAN?????????????????7698?22-2月?-81???????????1250????????500??????307566?JONES????????????????MANAGER??????????????????7839?02-4月?-81???????????2975?????????????????207654?MARTIN???????????????SALESMAN?????????????????7698?28-9月?-81???????????1250???????1400??????307698?BLAKE????????????????MANAGER??????????????????7839?01-5月?-81???????????2850?????????????????307782?CLARK????????????????MANAGER??????????????????7839?09-6月?-81???????????2450?????????????????107788?SCOTT????????????????ANALYST??????????????????7566?19-4月?-87???????????3000?????????????????207839?KING?????????????????PRESIDENT?????????????????????17-11月-81???????????5000?????????????????107844?TURNER???????????????SALESMAN?????????????????7698?08-9月?-81???????????1500??????????0??????307876?ADAMS????????????????CLERK????????????????????7788?23-5月?-87???????????1100?????????????????207900?JAMES????????????????CLERK????????????????????7698?03-12月-81????????????950?????????????????307902?FORD?????????????????ANALYST??????????????????7566?03-12月-81???????????3000?????????????????207934?MILLER???????????????CLERK????????????????????7782?23-1月?-82???????????1300?????????????????10已選擇14行。設置數字的列寬:
oracle?9代表占位:
SQL>?set?linesize?199;
SQL>?select?*?from?emp;EMPNO?ENAME????????????????JOB???????????????????????MGR?HIREDATE??????????????SAL???????COMM?????DEPTNO
----------?--------------------?------------------?----------?--------------?----------?----------?----------7369?SMITH????????????????CLERK????????????????????7902?17-12月-80????????????800?????????????????207499?ALLEN????????????????SALESMAN?????????????????7698?20-2月?-81???????????1600????????300??????307521?WARD?????????????????SALESMAN?????????????????7698?22-2月?-81???????????1250????????500??????307566?JONES????????????????MANAGER??????????????????7839?02-4月?-81???????????2975?????????????????207654?MARTIN???????????????SALESMAN?????????????????7698?28-9月?-81???????????1250???????1400??????307698?BLAKE????????????????MANAGER??????????????????7839?01-5月?-81???????????2850?????????????????307782?CLARK????????????????MANAGER??????????????????7839?09-6月?-81???????????2450?????????????????107788?SCOTT????????????????ANALYST??????????????????7566?19-4月?-87???????????3000?????????????????207839?KING?????????????????PRESIDENT?????????????????????17-11月-81???????????5000?????????????????107844?TURNER???????????????SALESMAN?????????????????7698?08-9月?-81???????????1500??????????0??????307876?ADAMS????????????????CLERK????????????????????7788?23-5月?-87???????????1100?????????????????207900?JAMES????????????????CLERK????????????????????7698?03-12月-81????????????950?????????????????307902?FORD?????????????????ANALYST??????????????????7566?03-12月-81???????????3000?????????????????207934?MILLER???????????????CLERK????????????????????7782?23-1月?-82???????????1300?????????????????10已選擇14行。SQL>?col?empno?for?9999999999999999999
SQL>?select?*?from?emp;EMPNO?ENAME????????????????JOB???????????????????????MGR?HIREDATE??????????????SAL???????COMM?????DEPTNO
--------------------?--------------------?------------------?----------?--------------?----------?----------?----------7369?SMITH????????????????CLERK????????????????????7902?17-12月-80????????????800????????????????207499?ALLEN????????????????SALESMAN?????????????????7698?20-2月?-81???????????1600????????300?????????307521?WARD?????????????????SALESMAN?????????????????7698?22-2月?-81???????????1250????????500?????????307566?JONES????????????????MANAGER??????????????????7839?02-4月?-81???????????2975????????????????207654?MARTIN???????????????SALESMAN?????????????????7698?28-9月?-81???????????1250???????1400?????????307698?BLAKE????????????????MANAGER??????????????????7839?01-5月?-81???????????2850????????????????307782?CLARK????????????????MANAGER??????????????????7839?09-6月?-81???????????2450????????????????107788?SCOTT????????????????ANALYST??????????????????7566?19-4月?-87???????????3000????????????????207839?KING?????????????????PRESIDENT?????????????????????17-11月-81???????????5000????????????????107844?TURNER???????????????SALESMAN?????????????????7698?08-9月?-81???????????1500??????????0?????????307876?ADAMS????????????????CLERK????????????????????7788?23-5月?-87???????????1100????????????????207900?JAMES????????????????CLERK????????????????????7698?03-12月-81????????????950????????????????307902?FORD?????????????????ANALYST??????????????????7566?03-12月-81???????????3000????????????????207934?MILLER???????????????CLERK????????????????????7782?23-1月?-82???????????1300????????????????10已選擇14行。設置字符的列寬:60個字符的寬度
SQL>?col?ename?for?a60;
SQL>?select?*?from?emp;EMPNO?ENAME????????????????????????????????????????????????????????JOB???????????????????????MGR?HIREDATE??????????????SAL???????COMM?????DEPTNO
--------------------?------------------------------------------------------------?------------------?----------?--------------?----------?----------?----------7369?SMITH????????????????????????????????????????????????????????CLERK????????????????????7902?17-12月-80????????????800????????????????????207499?ALLEN????????????????????????????????????????????????????????SALESMAN?????????????????7698?20-2月?-81???????????1600????????300?????????307521?WARD?????????????????????????????????????????????????????????SALESMAN?????????????????7698?22-2月?-81???????????1250????????500?????????307566?JONES????????????????????????????????????????????????????????MANAGER??????????????????7839?02-4月?-81???????????2975????????????????????207654?MARTIN???????????????????????????????????????????????????????SALESMAN?????????????????7698?28-9月?-81???????????1250???????1400?????????307698?BLAKE????????????????????????????????????????????????????????MANAGER??????????????????7839?01-5月?-81???????????2850????????????????????307782?CLARK????????????????????????????????????????????????????????MANAGER??????????????????7839?09-6月?-81???????????2450????????????????????107788?SCOTT????????????????????????????????????????????????????????ANALYST??????????????????7566?19-4月?-87???????????3000????????????????????207839?KING?????????????????????????????????????????????????????????PRESIDENT?????????????????17-11月-81???????5000????????????????????107844?TURNER???????????????????????????????????????????????????????SALESMAN?????????????????7698?08-9月?-81???????????1500??????????0?????????307876?ADAMS????????????????????????????????????????????????????????CLERK????????????????????7788?23-5月?-87???????????1100????????????????????207900?JAMES????????????????????????????????????????????????????????CLERK????????????????????7698?03-12月-81????????????950????????????????????307902?FORD?????????????????????????????????????????????????????????ANALYST??????????????????7566?03-12月-81???????????3000????????????????????207934?MILLER???????????????????????????????????????????????????????CLERK????????????????????7782?23-1月?-82???????????1300????????????????????10已選擇14行。恢復,便于后面做實驗:
SQL>?col?ename?for?a20;
SQL>?col?empno?for?999999
SQL>?select?*?from?emp;EMPNO?ENAME????????????????JOB???????????????????????MGR?HIREDATE??????????????SAL???????COMM?????DEPTNO
-------?--------------------?------------------?----------?--------------?----------?----------?----------7369?SMITH????????????????CLERK????????????????????7902?17-12月-80????????????800?????????????????207499?ALLEN????????????????SALESMAN?????????????????7698?20-2月?-81???????????1600????????300??????307521?WARD?????????????????SALESMAN?????????????????7698?22-2月?-81???????????1250????????500??????307566?JONES????????????????MANAGER??????????????????7839?02-4月?-81???????????2975?????????????????207654?MARTIN???????????????SALESMAN?????????????????7698?28-9月?-81???????????1250???????1400??????307698?BLAKE????????????????MANAGER??????????????????7839?01-5月?-81???????????2850?????????????????307782?CLARK????????????????MANAGER??????????????????7839?09-6月?-81???????????2450?????????????????107788?SCOTT????????????????ANALYST??????????????????7566?19-4月?-87???????????3000?????????????????207839?KING?????????????????PRESIDENT?????????????????????17-11月-81???????????5000?????????????????107844?TURNER???????????????SALESMAN?????????????????7698?08-9月?-81???????????1500??????????0??????307876?ADAMS????????????????CLERK????????????????????7788?23-5月?-87???????????1100?????????????????207900?JAMES????????????????CLERK????????????????????7698?03-12月-81????????????950?????????????????307902?FORD?????????????????ANALYST??????????????????7566?03-12月-81???????????3000?????????????????207934?MILLER???????????????CLERK????????????????????7782?23-1月?-82???????????1300?????????????????10已選擇14行。

清屏命令:

SQL>?host?cls

select 語法:

select 參與運算語法:

SQL>?select?*?from?emp;EMPNO?ENAME????????????????JOB???????????????????????MGR?HIREDATE??????????????SAL???????COMM?????DEPTNO
-------?--------------------?------------------?----------?--------------?----------?----------?----------7369?SMITH????????????????CLERK????????????????????7902?17-12月-80????????????800?????????????????207499?ALLEN????????????????SALESMAN?????????????????7698?20-2月?-81???????????1600????????300??????307521?WARD?????????????????SALESMAN?????????????????7698?22-2月?-81???????????1250????????500??????307566?JONES????????????????MANAGER??????????????????7839?02-4月?-81???????????2975?????????????????207654?MARTIN???????????????SALESMAN?????????????????7698?28-9月?-81???????????1250???????1400??????307698?BLAKE????????????????MANAGER??????????????????7839?01-5月?-81???????????2850?????????????????307782?CLARK????????????????MANAGER??????????????????7839?09-6月?-81???????????2450?????????????????107788?SCOTT????????????????ANALYST??????????????????7566?19-4月?-87???????????3000?????????????????207839?KING?????????????????PRESIDENT?????????????????????17-11月-81???????????5000?????????????????107844?TURNER???????????????SALESMAN?????????????????7698?08-9月?-81???????????1500??????????0??????307876?ADAMS????????????????CLERK????????????????????7788?23-5月?-87???????????1100?????????????????207900?JAMES????????????????CLERK????????????????????7698?03-12月-81????????????950?????????????????307902?FORD?????????????????ANALYST??????????????????7566?03-12月-81???????????3000?????????????????207934?MILLER???????????????CLERK????????????????????7782?23-1月?-82???????????1300?????????????????10
SQL>?select?ename,?empno,?job,?sal,?sal*12?from?emp;ENAME??????????????????EMPNO?JOB???????????????????????SAL?????SAL*12
--------------------?-------?------------------?----------?----------
SMITH???????????????????7369?CLERK?????????????????????800???????9600
ALLEN???????????????????7499?SALESMAN?????????????????1600??????19200
WARD????????????????????7521?SALESMAN?????????????????1250??????15000
JONES???????????????????7566?MANAGER??????????????????2975??????35700
MARTIN??????????????????7654?SALESMAN?????????????????1250??????15000
BLAKE???????????????????7698?MANAGER??????????????????2850??????34200
CLARK???????????????????7782?MANAGER??????????????????2450??????29400
SCOTT???????????????????7788?ANALYST??????????????????3000??????36000
KING????????????????????7839?PRESIDENT????????????????5000??????60000
TURNER??????????????????7844?SALESMAN?????????????????1500??????18000
ADAMS???????????????????7876?CLERK????????????????????1100??????13200
JAMES???????????????????7900?CLERK?????????????????????950??????11400
FORD????????????????????7902?ANALYST??????????????????3000??????36000
MILLER??????????????????7934?CLERK????????????????????1300??????15600

select as 語法

as?可以省略
冒號可以省略
別名之間不能有空格1,as別名的使用
SQL>?select?ename?as?"員工編號",?empno　"編號",?job?工作?,?sal?月薪?,?sal*12?年薪??from?emp;員工編號???????????????????編號?工作?????????????????????月薪???????年薪
--------------------?----------?------------------?----------?----------
SMITH??????????????????????7369?CLERK?????????????????????800???????9600
ALLEN??????????????????????7499?SALESMAN?????????????????1600??????19200
WARD???????????????????????7521?SALESMAN?????????????????1250??????15000
JONES??????????????????????7566?MANAGER??????????????????2975??????35700
MARTIN?????????????????????7654?SALESMAN?????????????????1250??????15000
BLAKE??????????????????????7698?MANAGER??????????????????2850??????34200
CLARK??????????????????????7782?MANAGER??????????????????2450??????29400
SCOTT??????????????????????7788?ANALYST??????????????????3000??????36000
KING???????????????????????7839?PRESIDENT????????????????5000??????60000
TURNER?????????????????????7844?SALESMAN?????????????????1500??????18000
ADAMS??????????????????????7876?CLERK????????????????????1100??????13200
JAMES??????????????????????7900?CLERK?????????????????????950??????11400
FORD???????????????????????7902?ANALYST??????????????????3000??????36000
MILLER?????????????????????7934?CLERK????????????????????1300??????15600已選擇14行。

2,沒有引號帶有空格的別名,無法識別:

SQL>?select?ename?as?"員工編號",?empno　"編號",?job?工作?,?sal?月?薪?,?sal*12?年薪??from?emp;
select?ename?as?"員工編號",?empno　"編號",?job?工作?,?sal?月?薪?,?sal*12?年薪??from?emp*
第?1?行出現錯誤:
ORA-00923:?未找到要求的?FROM?關鍵字

3,帶有空格的別名解決辦法:

SQL>?select?ename?as?"員工編號",?empno　"編號",?job?工作?,?sal?"月??薪"?,?sal*12?年薪??from?emp;員工編號???????????????????編號?工作???????????????????月??薪???????年薪
--------------------?----------?------------------?----------?----------
SMITH??????????????????????7369?CLERK?????????????????????800???????9600
ALLEN??????????????????????7499?SALESMAN?????????????????1600??????19200
WARD???????????????????????7521?SALESMAN?????????????????1250??????15000
JONES??????????????????????7566?MANAGER??????????????????2975??????35700
MARTIN?????????????????????7654?SALESMAN?????????????????1250??????15000
BLAKE??????????????????????7698?MANAGER??????????????????2850??????34200
CLARK??????????????????????7782?MANAGER??????????????????2450??????29400
SCOTT??????????????????????7788?ANALYST??????????????????3000??????36000
KING???????????????????????7839?PRESIDENT????????????????5000??????60000
TURNER?????????????????????7844?SALESMAN?????????????????1500??????18000
ADAMS??????????????????????7876?CLERK????????????????????1100??????13200
JAMES??????????????????????7900?CLERK?????????????????????950??????11400
FORD???????????????????????7902?ANALYST??????????????????3000??????36000
MILLER?????????????????????7934?CLERK????????????????????1300??????15600已選擇14行。

年收入拋出問題,年收入顯示為空

SQL>?select?ename?as?"員工編號",?empno　"編號",?job?工作?,?sal?"月??薪"?,?sal*12+comm?年收入??from?emp;員工編號???????????????????編號?工作???????????????????月??薪?????年收入
--------------------?----------?------------------?----------?----------
SMITH??????????????????????7369?CLERK?????????????????????800
ALLEN??????????????????????7499?SALESMAN?????????????????1600??????19500
WARD???????????????????????7521?SALESMAN?????????????????1250??????15500
JONES??????????????????????7566?MANAGER??????????????????2975
MARTIN?????????????????????7654?SALESMAN?????????????????1250??????16400
BLAKE??????????????????????7698?MANAGER??????????????????2850
CLARK??????????????????????7782?MANAGER??????????????????2450
SCOTT??????????????????????7788?ANALYST??????????????????3000
KING???????????????????????7839?PRESIDENT????????????????5000
TURNER?????????????????????7844?SALESMAN?????????????????1500??????18000
ADAMS??????????????????????7876?CLERK????????????????????1100
JAMES??????????????????????7900?CLERK?????????????????????950
FORD???????????????????????7902?ANALYST??????????????????3000
MILLER?????????????????????7934?CLERK????????????????????1300

1,NULL空值任何數與NULL運算都是NULL

2,空值不是空 null != null

解決辦法,nvl(a,b)函數,當a是null是返回b,b可以是數字/字符串

SQL>?select?ename,empno,?job,?sal,?nvl(sal*12+comm,0)?from?emp;ENAME?????????????????????EMPNO?JOB???????????????????????SAL?NVL(SAL*12+COMM,0)
--------------------?----------?------------------?----------?------------------
SMITH??????????????????????7369?CLERK?????????????????????800??????????????????0
ALLEN??????????????????????7499?SALESMAN?????????????????1600??????????????19500
WARD???????????????????????7521?SALESMAN?????????????????1250??????????????15500
JONES??????????????????????7566?MANAGER??????????????????2975??????????????????0
MARTIN?????????????????????7654?SALESMAN?????????????????1250??????????????16400
BLAKE??????????????????????7698?MANAGER??????????????????2850??????????????????0
CLARK??????????????????????7782?MANAGER??????????????????2450??????????????????0
SCOTT??????????????????????7788?ANALYST??????????????????3000??????????????????0
KING???????????????????????7839?PRESIDENT????????????????5000??????????????????0
TURNER?????????????????????7844?SALESMAN?????????????????1500??????????????18000
ADAMS??????????????????????7876?CLERK????????????????????1100??????????????????0
JAMES??????????????????????7900?CLERK?????????????????????950??????????????????0
FORD???????????????????????7902?ANALYST??????????????????3000??????????????????0
MILLER?????????????????????7934?CLERK????????????????????1300??????????????????0已選擇14行。SQL>?select?ename,empno,?job,?sal,?nvl(sal*12+comm,0)?"年收入"?from?emp;ENAME?????????????????????EMPNO?JOB???????????????????????SAL?????年收入
--------------------?----------?------------------?----------?----------
SMITH??????????????????????7369?CLERK?????????????????????800??????????0
ALLEN??????????????????????7499?SALESMAN?????????????????1600??????19500
WARD???????????????????????7521?SALESMAN?????????????????1250??????15500
JONES??????????????????????7566?MANAGER??????????????????2975??????????0
MARTIN?????????????????????7654?SALESMAN?????????????????1250??????16400
BLAKE??????????????????????7698?MANAGER??????????????????2850??????????0
CLARK??????????????????????7782?MANAGER??????????????????2450??????????0
SCOTT??????????????????????7788?ANALYST??????????????????3000??????????0
KING???????????????????????7839?PRESIDENT????????????????5000??????????0
TURNER?????????????????????7844?SALESMAN?????????????????1500??????18000
ADAMS??????????????????????7876?CLERK????????????????????1100??????????0
JAMES??????????????????????7900?CLERK?????????????????????950??????????0
FORD???????????????????????7902?ANALYST??????????????????3000??????????0
MILLER?????????????????????7934?CLERK????????????????????1300??????????0已選擇14行。SQL>?select?ename,empno,?job,?sal,?nvl(sal*12+comm,0)as??"年收入"?from?emp;ENAME?????????????????????EMPNO?JOB???????????????????????SAL?????年收入
--------------------?----------?------------------?----------?----------
SMITH??????????????????????7369?CLERK?????????????????????800??????????0
ALLEN??????????????????????7499?SALESMAN?????????????????1600??????19500
WARD???????????????????????7521?SALESMAN?????????????????1250??????15500
JONES??????????????????????7566?MANAGER??????????????????2975??????????0
MARTIN?????????????????????7654?SALESMAN?????????????????1250??????16400
BLAKE??????????????????????7698?MANAGER??????????????????2850??????????0
CLARK??????????????????????7782?MANAGER??????????????????2450??????????0
SCOTT??????????????????????7788?ANALYST??????????????????3000??????????0
KING???????????????????????7839?PRESIDENT????????????????5000??????????0
TURNER?????????????????????7844?SALESMAN?????????????????1500??????18000
ADAMS??????????????????????7876?CLERK????????????????????1100??????????0
JAMES??????????????????????7900?CLERK?????????????????????950??????????0
FORD???????????????????????7902?ANALYST??????????????????3000??????????0
MILLER?????????????????????7934?CLERK????????????????????1300??????????0已選擇14行。

ed修改上一條命令:保存退出,/開始執行

SQL>?ed
已寫入?file?afiedt.buf1*?select?ename,empno,?job,?sal,?nvl(sal*12+comm,0)as??"年?入"?from?emp
SQL>?/ENAME?????????????????????EMPNO?JOB???????????????????????SAL??????年?入
--------------------?----------?------------------?----------?----------
SMITH??????????????????????7369?CLERK?????????????????????800??????????0
ALLEN??????????????????????7499?SALESMAN?????????????????1600??????19500
WARD???????????????????????7521?SALESMAN?????????????????1250??????15500
JONES??????????????????????7566?MANAGER??????????????????2975??????????0
MARTIN?????????????????????7654?SALESMAN?????????????????1250??????16400
BLAKE??????????????????????7698?MANAGER??????????????????2850??????????0
CLARK??????????????????????7782?MANAGER??????????????????2450??????????0
SCOTT??????????????????????7788?ANALYST??????????????????3000??????????0
KING???????????????????????7839?PRESIDENT????????????????5000??????????0
TURNER?????????????????????7844?SALESMAN?????????????????1500??????18000
ADAMS??????????????????????7876?CLERK????????????????????1100??????????0
JAMES??????????????????????7900?CLERK?????????????????????950??????????0
FORD???????????????????????7902?ANALYST??????????????????3000??????????0
MILLER?????????????????????7934?CLERK????????????????????1300??????????0已選擇14行。

where 過濾,查詢獎金為空的員工:

SQL>?select?*?from?emp?where?comm?is?null;EMPNO?ENAME????????????????JOB???????????????????????MGR?HIREDATE??????????????SAL???????COMM?????DEPTNO
----------?--------------------?------------------?----------?--------------?----------?----------?----------7369?SMITH????????????????CLERK????????????????????7902?17-12月-80????????????800?????????????????207566?JONES????????????????MANAGER??????????????????7839?02-4月?-81???????????2975?????????????????207698?BLAKE????????????????MANAGER??????????????????7839?01-5月?-81???????????2850?????????????????307782?CLARK????????????????MANAGER??????????????????7839?09-6月?-81???????????2450?????????????????107788?SCOTT????????????????ANALYST??????????????????7566?19-4月?-87???????????3000?????????????????207839?KING?????????????????PRESIDENT?????????????????????17-11月-81???????????5000?????????????????107876?ADAMS????????????????CLERK????????????????????7788?23-5月?-87???????????1100?????????????????207900?JAMES????????????????CLERK????????????????????7698?03-12月-81????????????950?????????????????307902?FORD?????????????????ANALYST??????????????????7566?03-12月-81???????????3000?????????????????207934?MILLER???????????????CLERK????????????????????7782?23-1月?-82???????????1300?????????????????10已選擇10行。

||連接符,與虛表

SQL>?select?'Hello?'?||?'World'?from?emp;'HELLO'||'WORLD'
----------------------
Hello?World
Hello?World
Hello?World
Hello?World
Hello?World
Hello?World
Hello?World
Hello?World
Hello?World
Hello?World
Hello?World
Hello?World
Hello?World
Hello?World已選擇14行。

dual虛表:

SQL>?select?'Hello?'?||?'World'?from?dual;'HELLO'||'WORLD'
----------------------
Hello?World

利用偽表,顯示系統時間

SQL>?select?sysdate?from?dual;SYSDATE
--------------
25-8月?-16

DISTINCT 刪除重復行,顯示部門個數:

SQL>?select?DISTINCT?deptno?from?emp;DEPTNO
----------302010

DISTINCT修飾多個字段時,當兩個數據完全一樣才認為是重復的

SQL>?select?deptno,job?from?emp;DEPTNO?JOB
----------?------------------20?CLERK30?SALESMAN30?SALESMAN20?MANAGER30?SALESMAN30?MANAGER10?MANAGER20?ANALYST10?PRESIDENT30?SALESMAN20?CLERK30?CLERK20?ANALYST10?CLERK已選擇14行。SQL>?select?DISTINCT?deptno,job?from?emp;DEPTNO?JOB
----------?------------------20?CLERK30?SALESMAN20?MANAGER30?CLERK10?PRESIDENT30?MANAGER10?CLERK10?MANAGER20?ANALYST已選擇9行。

SQLplus工具的 help

SQL>?help?topicHelp?is?available?on?the?following?topics:/
@
@@
ACCEPT
APPEND
ARCHIVE?LOG
ATTRIBUTE
BREAK
BTITLE
CHANGE
CLEAR
COLUMN
COMPUTE
CONNECT
COPY
DEFINE
DEL
DESCRIBE
DISCONNECT
EDIT
EXECUTE
EXIT
GET
HELP
HOST
INDEX
INPUT
LIST
MENU
PASSWORD
PAUSE
PRINT
PROMPT
QUIT
RECOVER
REMARK
REPFOOTER
REPHEADER
RESERVED?WORDS?(PL/SQL)
RESERVED?WORDS?(SQL)
RUN
SAVE
SET
SHOW
SHUTDOWN
SPOOL
SQLPLUS
START
STARTUP
STORE
TIMING
TOPICS
TTITLE
UNDEFINE
VARIABLE
WHENEVER?OSERROR
WHENEVER?SQLERROR
XQUERYSQL>

edit的幫助:

SQL>???editEDIT----Invokes?an?operating?system?text?editor?on?the?contents?of?thespecified?file?or?on?the?contents?of?the?SQL?buffer.?The?bufferhas?no?command?history?list?and?does?not?record?SQL*Plus?commands.ED[IT]?[file_name[.ext]]SQL>

Oracle系統常用的參數

SQL>?set?pagesize?299
SQL>?set?linesize?299
SQL>?select?*?from?v$nls_parameters;PARAMETER?????????????????????????VALUE???????????????????????????????????????????????????????????????????????????????????????
-----------------------------?-----------?
NLS_LANGUAGE??????????????????????SIMPLIFIED?CHINESE
NLS_TERRITORY?????????????????????CHINA
NLS_CURRENCY??????????????????????￥
NLS_ISO_CURRENCY??????????????????CHINA
NLS_NUMERIC_CHARACTERS????????????.,
NLS_CALENDAR??????????????????????GREGORIAN
NLS_DATE_FORMAT???????????????????DD-MON-RR
NLS_DATE_LANGUAGE?????????????????SIMPLIFIED?CHINESE
NLS_CHARACTERSET??????????????????AL32UTF8
NLS_SORT??????????????????????????BINARY
NLS_TIME_FORMAT???????????????????HH.MI.SSXFF?AM
NLS_TIMESTAMP_FORMAT??????????????DD-MON-RR?HH.MI.SSXFF?AM
NLS_TIME_TZ_FORMAT????????????????HH.MI.SSXFF?AM?TZR
NLS_TIMESTAMP_TZ_FORMAT???????????DD-MON-RR?HH.MI.SSXFF?AM?TZR
NLS_DUAL_CURRENCY?????????????????￥
NLS_NCHAR_CHARACTERSET????????????AL16UTF16
NLS_COMP??????????????????????????BINARY
NLS_LENGTH_SEMANTICS??????????????BYTE
NLS_NCHAR_CONV_EXCP???????????????FALSE已選擇19行。

默認的時間顯示

SQL>?select?sysdate?from?dual;SYSDATE
--------------
25-8月?-16

臨時修改系統時間的顯示方式:

SQL>?alter?session?set?NLS_DATE_FORMAT='yyyy-mm-dd';會話已更改。SQL>?select?sysdate?from?dual;SYSDATE
----------
2016-08-25SQL>?select?*?from?emp;EMPNO?ENAME????????????????JOB???????????????????????MGR?HIREDATE??????????SAL???????COMM?????DEPTNO
----------?--------------------?------------------?----------?----------?----------?----------?----------7369?SMITH????????????????CLERK????????????????????7902?1980-12-17????????800????????????????????207499?ALLEN????????????????SALESMAN?????????????????7698?1981-02-20???????1600????????300?????????307521?WARD?????????????????SALESMAN?????????????????7698?1981-02-22???????1250????????500?????????307566?JONES????????????????MANAGER??????????????????7839?1981-04-02???????2975????????????????????207654?MARTIN???????????????SALESMAN?????????????????7698?1981-09-28???????1250???????1400?????????307698?BLAKE????????????????MANAGER??????????????????7839?1981-05-01???????2850????????????????????307782?CLARK????????????????MANAGER??????????????????7839?1981-06-09???????2450????????????????????107788?SCOTT????????????????ANALYST??????????????????7566?1987-04-19???????3000????????????????????207839?KING?????????????????PRESIDENT?????????????????????1981-11-17???????5000????????????????????107844?TURNER???????????????SALESMAN?????????????????7698?1981-09-08???????1500??????????0?????????307876?ADAMS????????????????CLERK????????????????????7788?1987-05-23???????1100????????????????????207900?JAMES????????????????CLERK????????????????????7698?1981-12-03????????950????????????????????307902?FORD?????????????????ANALYST??????????????????7566?1981-12-03???????3000????????????????????207934?MILLER???????????????CLERK????????????????????7782?1982-01-23???????1300????????????????????10已選擇14行。

where過濾字句 > 大于:

查詢入職比80年1月1號晚的員工:日期格式是敏感的

SQL>?select?*?from?emp?where?hiredate?>?'1980-01-01';EMPNO?ENAME????????????????JOB???????????????????????MGR?HIREDATE??????????SAL???????COMM?????DEPTNO
----------?--------------------?------------------?----------?----------?----------?----------?----------7369?SMITH????????????????CLERK????????????????????7902?1980-12-17????????800????????????????????207499?ALLEN????????????????SALESMAN?????????????????7698?1981-02-20???????1600????????300?????????307521?WARD?????????????????SALESMAN?????????????????7698?1981-02-22???????1250????????500?????????307566?JONES????????????????MANAGER??????????????????7839?1981-04-02???????2975????????????????????207654?MARTIN???????????????SALESMAN?????????????????7698?1981-09-28???????1250???????1400?????????307698?BLAKE????????????????MANAGER??????????????????7839?1981-05-01???????2850????????????????????307782?CLARK????????????????MANAGER??????????????????7839?1981-06-09???????2450????????????????????107788?SCOTT????????????????ANALYST??????????????????7566?1987-04-19???????3000????????????????????207839?KING?????????????????PRESIDENT?????????????????????1981-11-17???????5000????????????????????107844?TURNER???????????????SALESMAN?????????????????7698?1981-09-08???????1500??????????0?????????307876?ADAMS????????????????CLERK????????????????????7788?1987-05-23???????1100????????????????????207900?JAMES????????????????CLERK????????????????????7698?1981-12-03????????950????????????????????307902?FORD?????????????????ANALYST??????????????????7566?1981-12-03???????3000????????????????????207934?MILLER???????????????CLERK????????????????????7782?1982-01-23???????1300????????????????????10已選擇14行。

where過濾字句between and,查詢工資在1000 ~ 2000之間的

SQL>?select?*?from?emp?where?sal?between?1000?and?2000;EMPNO?ENAME????????????????JOB???????????????????????MGR?HIREDATE??????????SAL???????COMM?????DEPTNO
----------?--------------------?------------------?----------?----------?----------?----------?----------7499?ALLEN????????????????SALESMAN?????????????????7698?1981-02-20???????1600????????300?????????307521?WARD?????????????????SALESMAN?????????????????7698?1981-02-22???????1250????????500?????????307654?MARTIN???????????????SALESMAN?????????????????7698?1981-09-28???????1250???????1400?????????307844?TURNER???????????????SALESMAN?????????????????7698?1981-09-08???????1500??????????0?????????307876?ADAMS????????????????CLERK????????????????????7788?1987-05-23???????1100????????????????????207934?MILLER???????????????CLERK????????????????????7782?1982-01-23???????1300????????????????????10已選擇6行。

where過濾字句between A ?and B,A 必須小于等于 B

SQL>?select?*?from?emp?where?sal?between?9000?and?200;未選定行SQL>

where過濾字句 and

SQL>?select?*?from?emp?where?sal?<=9000?and?sal?>?2000;EMPNO?ENAME????????????????JOB???????????????????????MGR?HIREDATE??????????SAL???????COMM?????DEPTNO
----------?--------------------?------------------?----------?----------?----------?----------?----------7566?JONES????????????????MANAGER??????????????????7839?1981-04-02???????2975????????????????????207698?BLAKE????????????????MANAGER??????????????????7839?1981-05-01???????2850????????????????????307782?CLARK????????????????MANAGER??????????????????7839?1981-06-09???????2450????????????????????107788?SCOTT????????????????ANALYST??????????????????7566?1987-04-19???????3000????????????????????207839?KING?????????????????PRESIDENT?????????????????????1981-11-17???????5000????????????????????107902?FORD?????????????????ANALYST??????????????????7566?1981-12-03???????3000????????????????????20已選擇6行。

where過濾字句 in

SQL>?select?*?from?emp?where?deptno?in?(10,20);EMPNO?ENAME????????????????JOB???????????????????????MGR?HIREDATE??????????SAL???????COMM?????DEPTNO
----------?--------------------?------------------?----------?----------?----------?----------?----------7369?SMITH????????????????CLERK????????????????????7902?1980-12-17????????800????????????????????207566?JONES????????????????MANAGER??????????????????7839?1981-04-02???????2975????????????????????207782?CLARK????????????????MANAGER??????????????????7839?1981-06-09???????2450????????????????????107788?SCOTT????????????????ANALYST??????????????????7566?1987-04-19???????3000????????????????????207839?KING?????????????????PRESIDENT?????????????????????1981-11-17???????5000????????????????????107876?ADAMS????????????????CLERK????????????????????7788?1987-05-23???????1100????????????????????207902?FORD?????????????????ANALYST??????????????????7566?1981-12-03???????3000????????????????????207934?MILLER???????????????CLERK????????????????????7782?1982-01-23???????1300????????????????????10已選擇8行。

where過濾字句 not in

SQL>?select?*?from?emp?where?deptno?not?in?(10,20);EMPNO?ENAME????????????????JOB???????????????????????MGR?HIREDATE??????????SAL???????COMM?????DEPTNO
----------?--------------------?------------------?----------?----------?----------?----------?----------7499?ALLEN????????????????SALESMAN?????????????????7698?1981-02-20???????1600????????300?????????307521?WARD?????????????????SALESMAN?????????????????7698?1981-02-22???????1250????????500?????????307654?MARTIN???????????????SALESMAN?????????????????7698?1981-09-28???????1250???????1400?????????307698?BLAKE????????????????MANAGER??????????????????7839?1981-05-01???????2850????????????????????307844?TURNER???????????????SALESMAN?????????????????7698?1981-09-08???????1500??????????0?????????307900?JAMES????????????????CLERK????????????????????7698?1981-12-03????????950????????????????????30已選擇6行。

where過濾字句 not in 與null在一起,受影響

SQL>?select?*?from?emp?where?deptno?in?(10,20);EMPNO?ENAME????????????????JOB???????????????????????MGR?HIREDATE??????????SAL???????COMM?????DEPTNO
----------?--------------------?------------------?----------?----------?----------?----------?----------7369?SMITH????????????????CLERK????????????????????7902?1980-12-17????????800????????????????????207566?JONES????????????????MANAGER??????????????????7839?1981-04-02???????2975????????????????????207782?CLARK????????????????MANAGER??????????????????7839?1981-06-09???????2450????????????????????107788?SCOTT????????????????ANALYST??????????????????7566?1987-04-19???????3000????????????????????207839?KING?????????????????PRESIDENT?????????????????????1981-11-17???????5000????????????????????107876?ADAMS????????????????CLERK????????????????????7788?1987-05-23???????1100????????????????????207902?FORD?????????????????ANALYST??????????????????7566?1981-12-03???????3000????????????????????207934?MILLER???????????????CLERK????????????????????7782?1982-01-23???????1300????????????????????10已選擇8行。SQL>?select?*?from?emp?where?deptno?in?(10,20,null);EMPNO?ENAME????????????????JOB???????????????????????MGR?HIREDATE??????????SAL???????COMM?????DEPTNO
----------?--------------------?------------------?----------?----------?----------?----------?----------7369?SMITH????????????????CLERK????????????????????7902?1980-12-17????????800????????????????????207566?JONES????????????????MANAGER??????????????????7839?1981-04-02???????2975????????????????????207782?CLARK????????????????MANAGER??????????????????7839?1981-06-09???????2450????????????????????107788?SCOTT????????????????ANALYST??????????????????7566?1987-04-19???????3000????????????????????207839?KING?????????????????PRESIDENT?????????????????????1981-11-17???????5000????????????????????107876?ADAMS????????????????CLERK????????????????????7788?1987-05-23???????1100????????????????????207902?FORD?????????????????ANALYST??????????????????7566?1981-12-03???????3000????????????????????207934?MILLER???????????????CLERK????????????????????7782?1982-01-23???????1300????????????????????10已選擇8行。SQL>?select?*?from?emp?where?deptno?not?in?(10,20,null);未選定行

where like模糊查詢,查詢員工姓名S開頭的信息:

SQL>?select?*?from?emp;EMPNO?ENAME????????????????JOB???????????????????????MGR?HIREDATE??????????SAL???????COMM?????DEPTNO
----------?--------------------?------------------?----------?----------?----------?----------?----------7369?SMITH????????????????CLERK????????????????????7902?1980-12-17????????800????????????????????207499?ALLEN????????????????SALESMAN?????????????????7698?1981-02-20???????1600????????300?????????307521?WARD?????????????????SALESMAN?????????????????7698?1981-02-22???????1250????????500?????????307566?JONES????????????????MANAGER??????????????????7839?1981-04-02???????2975????????????????????207654?MARTIN???????????????SALESMAN?????????????????7698?1981-09-28???????1250???????1400?????????307698?BLAKE????????????????MANAGER??????????????????7839?1981-05-01???????2850????????????????????307782?CLARK????????????????MANAGER??????????????????7839?1981-06-09???????2450????????????????????107788?SCOTT????????????????ANALYST??????????????????7566?1987-04-19???????3000????????????????????207839?KING?????????????????PRESIDENT?????????????????????1981-11-17???????5000????????????????????107844?TURNER???????????????SALESMAN?????????????????7698?1981-09-08???????1500??????????0?????????307876?ADAMS????????????????CLERK????????????????????7788?1987-05-23???????1100????????????????????207900?JAMES????????????????CLERK????????????????????7698?1981-12-03????????950????????????????????307902?FORD?????????????????ANALYST??????????????????7566?1981-12-03???????3000????????????????????207934?MILLER???????????????CLERK????????????????????7782?1982-01-23???????1300????????????????????10已選擇14行。SQL>?select?*?from?emp?where?ename?like?'S%';EMPNO?ENAME????????????????JOB???????????????????????MGR?HIREDATE??????????SAL???????COMM?????DEPTNO
----------?--------------------?------------------?----------?----------?----------?----------?----------7369?SMITH????????????????CLERK????????????????????7902?1980-12-17????????800????????????????????207788?SCOTT????????????????ANALYST??????????????????7566?1987-04-19???????3000????????????????????20

轉載于:https://blog.51cto.com/990487026/1843080