ORACLE百例試煉一

    Oracle系列《一》：簡單SQL與單行函數(shù)

 【1】EMP表內(nèi)容查詢 

SQL> SELECT * FROM emp;  

出錯(cuò)，原因是沒有找到該表，因?yàn)樵摫頃r(shí)SCOTT用戶的表，所以查詢時(shí)應(yīng)該加上scott.emp就可以了

【2】顯示當(dāng)前用戶

SQL> show user

【3】查看當(dāng)前用戶的所有表

 SQL> SELECT * FROM tab;  

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

【5】查詢一張表的結(jié)構(gòu)，例如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;  工作可能會重復(fù)，加上DISTINCT關(guān)鍵字

【9】若要求按照以下的格式進(jìn)行結(jié)果輸出，如 

NO:7469,Name:SMITH,Job:CLERK 

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

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

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

【11】查看每月可以得到獎(jiǎng)金的雇員信息 

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

【12】要求基本工資大于1500，同時(shí)可以領(lǐng)取獎(jiǎng)金的雇員信息 

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

【13】查詢基本工資不大于1500，同時(shí)不可以領(lǐng)取獎(jiǎng)金的雇員信息 -------------如果是或的是關(guān)系，則使用 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對大小敏感，所以查詢時(shí)名字要區(qū)分大小寫 

【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】要求對雇員的工資由低到高進(jìn)行排序，升序?yàn)槟J(rèn)(ASC)，降序(DESC) 

SQL> SELECT * FROM emp ORDER BY sal;

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

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

      數(shù)據(jù)庫系統(tǒng)中，每個(gè)數(shù)據(jù)庫之間區(qū)別最大的就是在函數(shù)的支持上，單行函數(shù)是最簡單的函數(shù)，單行函數(shù)分為  

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

 2、數(shù)值函數(shù)：接受數(shù)值輸入并返回?cái)?shù)值  

 3、日期函數(shù)：對日期型數(shù)據(jù)進(jìn)行操作  

 4、轉(zhuǎn)換函數(shù)：將一種數(shù)據(jù)類型轉(zhuǎn)換為另一種數(shù)據(jù)類型 

 5、通用函數(shù)：NVL、DECODE 函數(shù)

字符函數(shù)：

 【1】大小寫轉(zhuǎn)換 UPPER 和 LOWER  

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

 【2】將雇員姓名變?yōu)殚_頭字母大寫，INITCAP 

SQL> SELECT INITCAP(ename) FROM emp; 

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

字符串截取：substr() 

字符串長度：length() 

內(nèi)容替換：replace()  

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

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

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

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

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

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

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

數(shù)值函數(shù)：

1、四舍五入：ROUND() 

2、截?cái)嘈?shù)位：TRUNC() 

3、取余(取模)：MOD 

【1】保留1位小數(shù),(如果是-2則對整數(shù)進(jìn)行四舍五入，變?yōu)?00了

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

結(jié)果是-------783.6

【2】截?cái)嘈?shù)位，

 SQL> SELECT ROUND(783.56) FROM dual; 

結(jié)果是-------783

【3】使用MOD()函數(shù)進(jìn)行取余操作 

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

結(jié)果是--------1

日期函數(shù)： 

1、日期 - 數(shù)字 = 日期 

2、日期 + 數(shù)字 = 日期 

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

【1】求出當(dāng)前日期 

SQL> SELECT SYSDATE FROM dual;

Oracle提供了以下的日期函數(shù)支持： 

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

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

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

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

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

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

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

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

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

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

轉(zhuǎn)換函數(shù)： 

1、TO_CHAR()：  將日期或數(shù)值轉(zhuǎn)換成字符串

2、TO_NUMBER()：將字符串轉(zhuǎn)換成數(shù)字（字符串里是數(shù)字） 

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

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

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

【2】將薪水的數(shù)字進(jìn)行格式化，'$99,999'表示美元，'L99,999'表示當(dāng)?shù)刎泿?nbsp;

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

【3】TO_NUMBER()驗(yàn)證 

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;

通用函數(shù)： 

【1】求出每個(gè)雇員的年薪(應(yīng)算上獎(jiǎng)金) 

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

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

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

NVL可以理解為將NULL值轉(zhuǎn)換為具體的內(nèi)容，這里是0

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

DECODE(字段,選擇1,結(jié)果1[，選擇2，結(jié)果2，...,默認(rèn)])

驗(yàn)證DECODE()函數(shù) 

SQL>select  empno,ename,hiredate,DECODE

 (job,'CLERK','業(yè)務(wù)員','SALESMAN','銷售人員','MANAGER','經(jīng)理','ANALYST','分析員','PRESIDENT','總裁') 職位 FROM emp;

SQL簡單語句練習(xí)：

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

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

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

SQL> SELECT * FROM emp 

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

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

SQL> SELECT * FROM emp 

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

【4】找出有獎(jiǎng)金的員工的不同工作

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

【5】找出各月倒數(shù)第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個(gè)字符的員工的姓名 

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】以年月日方式顯示所有員工服務(wù)年限 

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;