Oracle學習筆記（一）--簡單查詢

001:在SQL語句中使用算術運算符

SELECT empno, ename, (500+sal)*12 
FROM emp; 
           

002:使用列的别名

SELECT empno AS "員工編号", ename name, (500+sal)*12 "Annual Salary" 
FROM emp;
           

當别名沒有被雙引号擴起來時, 其顯示結果為大寫.

如果在别名中包含了特殊字元(如中文字元), 或想讓别名原樣顯示, 就要用雙引号把别名擴起來.

003:連接配接運算符

SELECT ename || ' annual salary is ' || (500+sal)*12 "Employee's Salary" 
FROM emp;

SELECT ename || '的年薪為:' || (500+sal)*12 "員工的年薪" 
FROM emp;
           

004:查詢時使用DISTINCT去掉重複行

SELECT DISTINCT deptno 
FROM dept;

SELECT DISTINCT deptno,job 
FROM emp;
           

DISTINCT用于多列時, 顯示的結果為各列組合結果隻顯示一行

005:選擇性查詢

--選擇性查詢
SELECT empno, ename, sal 
FROM emp 
WHERE sal>=1500;

--BETWEEN運算符用于數字
SELECT empno, ename, sal 
FROM emp 
WHERE sal BETWEEN 1500 AND 2900;

--BETWEEN運算符用于日期
SELECT empno, ename, sal, hiredate
FROM emp
WHERE hiredate BETWEEN '01-JAN-81' AND '31-MAY-82';

--NOT BETWEEN運算符用于日期
SELECT empno, ename, sal, hiredate
FROM emp
WHERE hiredate NOT BETWEEN '01-JAN-81' AND '31-MAY-82';

--"="運算符
SELECT empno, ename, job
FROM emp
WHERE JOB='SALESMAN';

--IN運算符
SELECT empno, ename, sal, job
FROM emp
WHERE job IN('SALESMAN', 'CLERK', 'MANAGER');

--NOT IN運算符  
SELECT empno, ename, sal, job
FROM emp
WHERE job NOT IN('ANALYST', 'PRESIDENT'); 

--LIKE比較運算符, %通配符
SELECT empno, ename, sal, job 
FROM emp 
WHERE job LIKE 'SAL%';

--LIKE比較運算符, _通配符
SELECT empno, ename, sal, job
FROM emp 
WHERE job LIKE 'S_L_S%';

--LIKE比較運算符, %通配符用于日期
SELECT empno, ename, sal, hiredate 
FROM emp
WHERE hiredate LIKE '%81';
           

006:使用轉義操作符

--建立臨時表單
CREATE TABLE dept_temp 
AS 
SELECT * FROM dept;  

--在表單内加入測試項
INSERT INTO dept_temp 
VALUES(88, 'IT_RESEARCH', 'BEIJING');

--使用轉義操作符
SELECT * 
FROM dept_temp 
WHERE dname LIKE 'IT\_%' escape'\';  
           

007:排序操作

--增序(預設情況)
SELECT empno, ename, sal 
FROM emp 
WHERE sal >= 1500 
ORDER BY sal ASC;

--降序
SELECT empno, ename, sal 
FROM emp 
WHERE sal >= 1500 
ORDER BY sal DESC; 

--在ORDER BY 子句中使用别名 
SELECT empno AS "Employee Number", ename name, (500+sal)*12 "Annual Salary" 
FROM emp 
ORDER BY "Annual Salary" DESC;

--在ORDER BY 子句中使用表達式
SELECT empno AS "Employee Number", ename name, (500+sal)*12 "Annual Salary" 
FROM emp 
ORDER BY (500+sal)*12 DESC;

--在ORDER BY 子句中使用列号
SELECT empno AS "Employee Number", ename name, (500+sal)*12 "Annual Salary" 
FROM emp 
ORDER BY 3 DESC;

--在ORDER BY 子句中使用多列進行排序, 首先按job進行排序,然後按sal進行排序
SELECT ename, job, sal 
FROM emp 
ORDER BY job, sal DESC;

--使用不在SELECT清單中的列來排序
SELECT ename, job, sal 
FROM emp 
ORDER BY empno;