ORACLE百例試煉一

    Oracle系列《一》：簡單SQL與單行函數

 【1】EMP表內容查詢 

SQL> SELECT * FROM emp;  

出錯，原因是沒有找到該表，因為該表時SCOTT用戶的表，所以查詢時應該加上scott.emp就可以了

【2】顯示當前用戶

SQL> show user

【3】查看當前用戶的所有表

 SQL> SELECT * FROM tab;  

【4】若想重復執行上一條SQL語句，則在sqlplus命令行下輸入"/"即可  

【5】查詢一張表的結構，例如dept表 

SQL> desc dept  

【6】在雇員表中查詢雇員的編號、姓名、工作 

SQL> SELECT empno,ename,job FROM emp;  

【7】可以為列名取別名，在Linux下Oracle如果英文別名不加上雙引號則會變成大寫 

SQL> SELECT empno 編號,ename 姓名,job 工作 FROM emp;

【8】查詢所有的工作 

SQL> SELECT DISTINCT job FROM emp;  工作可能會重復，加上DISTINCT關鍵字

【9】若要求按照以下的格式進行結果輸出，如 

NO:7469,Name:SMITH,Job:CLERK 

SQL> SELECT 'NO:'||empno||',Name:'||ename||',Job:'||job FROM emp;

【10】要求列出每個雇員的姓名及年薪 

SQL> SELECT ename,sal*12 income FROM emp; 這里年薪最好用別名進行標識，可以一眼就能明白

【11】查看每月可以得到獎金的雇員信息 

SQL> SELECT * FROM emp WHERE comm is NOT NULL;

【12】要求基本工資大于1500，同時可以領取獎金的雇員信息 

SQL> SELECT * FROM emp WHERE sal>1500 AND comm is NOT NULL; 

【13】查詢基本工資不大于1500，同時不可以領取獎金的雇員信息 -------------如果是或的是關系，則使用 OR 

SQL> SELECT * FROM emp WHERE NOT (sal>1500 OR comm is NOT NULL);

或

SQL>SELECT * FROM emp WHEREM sal<1500 and comm is null;

【14】查詢在1981年雇傭的全部雇員信息

SQL>select * from emp where to_char(hiredate,'yyyy')=1981;

【15】Oracle對大小敏感，所以查詢時名字要區分大小寫 

【16】要求查詢出雇員編號不是 7369、7499的雇員信息 

SQL> SELECT * FROM emp WHERE empno NOT IN(7369,7499);

SQL> select * from emp where empno   !=  all(7369，7499)

【17】SQL中LIKE語句要注意通配符 % 和 _ 

SQL> SELECT * FROM emp WHERE hiredate LIKE '%81%';

【18】查看雇員編號不是7369的雇員信息，使用<>或!= 

SQL> SELECT * FROM emp WHERE empno<>7369;

【19】要求對雇員的工資由低到高進行排序，升序為默認(ASC)，降序(DESC) 

SQL> SELECT * FROM emp ORDER BY sal;

【20】查看出部門號為10的雇員信息，查詢的信息按照工資從高到低，若工資相等則按雇用日期從早到晚排列 

SQL> SELECT * FROM emp WHERE deptno=10 ORDER BY sal DESC,hiredate ASC;

      數據庫系統中，每個數據庫之間區別最大的就是在函數的支持上，單行函數是最簡單的函數，單行函數分為  

 1、字符函數：接受字符輸入并且返回字符或數值

 2、數值函數：接受數值輸入并返回數值  

 3、日期函數：對日期型數據進行操作  

 4、轉換函數：將一種數據類型轉換為另一種數據類型 

 5、通用函數：NVL、DECODE 函數

字符函數：

 【1】大小寫轉換 UPPER 和 LOWER  

SQL> SELECT UPPER('smith') FROM dual; 

 【2】將雇員姓名變為開頭字母大寫，INITCAP 

SQL> SELECT INITCAP(ename) FROM emp; 

  字符函數中有連接函數CONCAT，但不如 || 好用，還有字符串處理的一些函數 

字符串截取：substr() 

字符串長度：length() 

內容替換：replace()  

查找字符：instr(ename,'A')=1(第一個字母為A的名字)

SQL> SELECT  substr('hello',1,3),length('hello'),replace('hello','l','x') FROM dual;  

這里注意的是Oracle中字符串截取從0和從1開始都是一樣的，謹防面試提問

【3】要求顯示所有雇員的姓名及姓名的后3個字符 

SQL> SELECT ename,SUBSTR(ename,LENGTH(ename)-2) FROM emp;

 以上操作顯得較為麻煩，substr()函數是可以倒著截取 

SQL> SELECT ename,SUBSTR(ename,-3,3) FROM emp;

數值函數：

1、四舍五入：ROUND() 

2、截斷小數位：TRUNC() 

3、取余(取模)：MOD 

【1】保留1位小數,(如果是-2則對整數進行四舍五入，變為800了

 SQL> SELECT ROUND(783.56,1) FROM dual; 

結果是-------783.6

【2】截斷小數位，

 SQL> SELECT ROUND(783.56) FROM dual; 

結果是-------783

【3】使用MOD()函數進行取余操作 

 SQL> SELECT MOD(10,3) FROM dual;

結果是--------1

日期函數： 

1、日期 - 數字 = 日期 

2、日期 + 數字 = 日期 

3、日期 - 日期 = 數字(天數) 

【1】求出當前日期 

SQL> SELECT SYSDATE FROM dual;

Oracle提供了以下的日期函數支持： 

1.MONTHS_BETWEEN():求出給定日期范圍的月數 

select months_between(to_date('2015-10','yyyy-mm'),to_date('1997-4','yyyy-mm') )from dual

2.ADD_MONTHS():在指定日期上加上指定的月數

select add_months(to_date('2015-10-1','yyyy-mm-dd'),1) from dual

3.求出之后的日期 NEXT_DAY():下一個的今天的日期 

select next_day(sysdate,'星期三') from dual

4.LAST_DAY():求出給定日期的最后一天日期

select last_day(to_date('2015-10','yyyy-mm'))from dual 

【2】求出從雇用日期到今天所有雇員的雇員編號、姓名和月數

SQL> SELECT empno,ename,ROUND(MONTHS_BETWEEN(SYSDATE,hiredate)) FROM emp; 

轉換函數： 

1、TO_CHAR()：  將日期或數值轉換成字符串

2、TO_NUMBER()：將字符串轉換成數字（字符串里是數字） 

3、TO_DATE()：  將字符串轉換成日期

【1】將年月日進行分開，要指定拆分的通配符,yyyy-mm-dd 

SQL> SELECT empno,ename,TO_CHAR(hiredate,'yyyy') datetime FROM emp

【2】將薪水的數字進行格式化，'$99,999'表示美元，'L99,999'表示當地貨幣 

SQL> SELECT empno,ename,TO_CHAR(sal,'L99,999) salary FROM emp;

【3】TO_NUMBER()驗證 

SQL> SELECT TO_NUMBER('123')+TO_NUMBER('123') FROM DUAL;

【4】TO_DATE

SQL> SELECT TO_DATE('2011-7-11','yyyy-mm-dd') FROM DUAL;

通用函數： 

【1】求出每個雇員的年薪(應算上獎金) 

SQL> SELECT empno,ename,(sal+comm)*12 FROM emp;

由于comm中有NULL,NULL值計算后還是NULL,正確如下： 

SQL> SELECT empno,ename,NVL(comm,0),(sal+NVL(comm,0))*12 income FROM emp; 

NVL可以理解為將NULL值轉換為具體的內容，這里是0

【2】DECODE()函數，該函數類似于 IF ... ELSEIF...ELSE 語法如下： 

DECODE(字段,選擇1,結果1[，選擇2，結果2，...,默認])

驗證DECODE()函數 

SQL>select  empno,ename,hiredate,DECODE

 (job,'CLERK','業務員','SALESMAN','銷售人員','MANAGER','經理','ANALYST','分析員','PRESIDENT','總裁') 職位 FROM emp;

SQL簡單語句練習：

【1】找出傭金高于薪金的60%的員工 

SQL> SELECT * FROM emp WHERE comm>sal*0.6

【2】找出部門10中所有經理(MANAGER)和部門20中所有辦事員(CLERK)的詳細資料 

SQL> SELECT * FROM emp 

WHERE (deptno=10 AND job='MANAGER') OR (deptno=20 AND job='CLERK');

【3】找出既不是經理又不是辦事員但其薪金大于或等于2000的所有員工的資料 

SQL> SELECT * FROM emp 

WHERE job NOT IN('MANAGER','CLERK') AND sal >= 2000;

【4】找出有獎金的員工的不同工作

SQL> SELECT DISTINCT job FROM emp WHERE comm IS NOT NULL;

【5】找出各月倒數第3天受雇的所有員工

 SQL> SELECT * FROM emp 

WHERE LAST_DAY(hiredate)-2=hiredate;

【6】找出早于30年前受雇的員工 

SQL> SELECT * FROM emp 

WHERE MONTHS_BETWEEN(sysdate,hiredate)/12 > 30;

【7】顯示剛好為5個字符的員工的姓名 

SQL> SELECT ename FROM emp WHERE length(ename)=5;

【8】顯示不帶有"R"的員工的姓名 

SQL> SELECT ename FROM emp WHERE ename NOT LIKE '%R%';

【9】顯示員工的姓名和受雇日期，將最老的員工排在最前 

SQL> SELECT * FROM emp GROUP BY hiredate;

【10】顯示所有員工的姓名，加入公司的年份和月份，按受雇日期所在月排序，若月份相同則按年份排序

select   ename,  to_char(hiredate,'yyyy')  年份   ,  to_char(hiredate,'mm')  月份 from emp  order by 月份,年份

【11】找出在2月受聘的員工 

SQL> SELECT * FROM emp 

WHERE TO_CHAR(hiredate,'mm')=2;

【12】以年月日方式顯示所有員工服務年限 

SQL> SELECT ename,

TRUNC(MONTHS_BETWEEN(sysdate,hiredate)/12)year, 

TRUNC(MOD(MONTHS_BETWEEN(sysdate,hiredate),12)) month, 

TRUNC(MOD(sysdate-hiredate,30)) day 

FROM emp;