一、基本操作表和資料

-- 建表
CREATE TABLE ab_student (
id number(4)
)
create table ab_class(
id number(4),
name varchar2(20)
)
-- 修改表
ALTER TABLE ab_student ADD(classid number(4))
ALTER TABLE ab_student ADD(birthday date)
ALTER TABLE ab_student MODIFY(classid number(4))
ALTER TABLE ab_student DROP  COLUMN classid
RENAME ab_student TO ab_student2

-- 删除表
DROP TABLE ab_student
DROP TABLE ab_class

-- 增加一條資料
INSERT INTO ab_student(id, birthday) VALUES(2, '02-8月-13')
ALTER SESSION SET nls_date_format='yyyy-mm-dd'
INSERT INTO ab_student(id, birthday) VALUES(3, '2013-08-05')
INSERT INTO ab_student(id, birthday) VALUES(4, '2014-05-05')
INSERT INTO ab_student(id, birthday) VALUES(5, '1992-05-25')
INSERT INTO ab_student(id) VALUES(10 )

-- 删除一條資料
DELETE FROM ab_student WHERE id = 1
-- 删除所有記錄，表結構還在，寫日志，可以恢複的，速度慢
DELETE FROM ab_student
-- delete的資料可以恢複，一個有經驗的dba，在確定完成無誤的情況下要定期建立還原點。
SAVEPOINT sp_201308020941 -- 建立儲存點
DELETE FROM ab_student
ROLLBACK TO sp_201308020941 -- 恢複到儲存點

SELECT * FROM ab_student
SELECT * FROM ab_student WHERE birthday IS NULL
SELECT * FROM ab_student WHERE birthday IS NOT NULL

-- 連接配接查詢
INSERT INTO ab_class(id, name) VALUES(1, '軟體班')
INSERT INTO ab_class(id, name) VALUES(2, '軟測班')
INSERT INTO ab_class(id, name) VALUES(3, '多媒體班')
INSERT INTO ab_class(id, name) VALUES(5, '網絡班')
SELECT * FROM ab_student
SELECT * FROM ab_class
-- 内連接配接
SELECT *
FROM ab_student a
INNER JOIN ab_class b ON b.id = a.classid
-- 左外連接配接
SELECT *
FROM ab_student a
LEFT OUTER JOIN ab_class b ON b.id = a.classid
-- 右外連接配接
SELECT *
FROM ab_student a
RIGHT OUTER JOIN ab_class b ON b.id = a.classid
-- 交叉連接配接
SELECT *
FROM ab_student a
CROSS JOIN ab_class b

UPDATE ab_student SET birthday = '1992-05-25' WHERE id = 3
UPDATE ab_student SET classid =1 WHERE id = 3
UPDATE ab_student SET classid =3 WHERE id = 5
UPDATE ab_student SET classid =5 WHERE id = 10

DROP TABLE ab_student --删除表的結構和資料；
DELETE FROM ab_student where id = 1 --删除一條記錄；
TRUNCATE TABLE ab_student --删除表中的所有記錄，表結構還在，不寫日志，無法找回删除的記錄，速度快。

二、表的使用技巧

-- 1. 檢視表結構
DESC SCOTT.EMP
SELECT * FROM user_tab_columns WHERE table_name= 'EMP'
GO
SELECT 'SCOTT.EMP' FROM all_tables -- 所有使用者的表 
GO

-- 2 表複制語句
CREATE TABLE SCOTT.AA_DEPT (
    DEPTNO     NUMBER(2,0) NOT NULL,
    DNAME      VARCHAR2(14) NULL,
    LOC        VARCHAR2(13) NULL,
    CONSTRAINT PK_AA_DEPT PRIMARY KEY(DEPTNO)
     NOT DEFERRABLE
     VALIDATE
)
GO
INSERT INTO TABLE aa_dept(deptno, dname, loc)
SELECT a.deptno, a.dname, a.loc FROM DEPT a
GO
SELECT deptno, dname, loc FROM AA_DEPT

-- 3. 用查詢結果建立新表，這個指令是一種快捷的建表方式
CREATE TABLE AB_DEPT (id, name, sal, job, deptno) AS SELECT empno, ename, sal, job, deptno FROM emp
SELECT * FROM AB_DEPT

三、 SQL基本查詢技巧

-- 1. 查詢所有
SELECT * FROM EMP

-- 2.使用WHERE子句
-- 查詢smith所在部門，工作，薪水
SELECT deptno, job, sal FROM emp WHERE ename = 'SMITH'
-- 顯示工資高于三千的員工
SELECT ename, sal FROM emp WHERE sal > 3000
-- 如何查找1982.1.1後入職的員工
ALTER SESSION SET nls_date_format='yyyy-mm-dd'
SELECT ename, hiredate FROM emp WHERE hiredate > '1982-01-01'
-- 如何顯示工資在2000到3000的員工
SELECT ename, sal FROM emp WHERE sal >= 2000 AND sal <= 3000

-- 3. 使用IN 
	-- 如何顯示empno為7844,7839,123,456的雇員情況？
SELECT * FROM emp WHERE empno IN (7844, 7839, 123, 456)

-- 4. 排除重複行DISTINCT
SELECT DISTINCT  deptno, job FROM emp

-- 5. 使用列的别名AS
SELECT ename AS "姓名" , sal * 12 AS "年收入" FROM emp

-- 6. 連接配接字元串||
SELECT ename || ' is a ' || job FROM emp

-- 7. 使用LIKE關鍵字，%：表示0到多個字元 _：表示任意單個字元
-- 如何顯示首字元為s的員工姓名和工資？
SELECT ename,sal FROM emp WHERE ename like 'S%'
-- 如何顯示第三個字元為大寫o的所有員工的姓名和工資？
SELECT ename,sal FROM emp WHERE ename LIKE '__O%'

-- 8. 使用内置函數
-- nvl()函數
/* 格式為：nvl(string1, replace_with) 
功能：如果string1為null，則nvl函數傳回replace_with的值，否則傳回string1的值。 */
SELECT sal * 13 + NVL(comm, 0) * 13 AS "年薪", ename, comm FROM emp
-- 如何顯示沒有上級的雇員的情況
SELECT ename,mgr FROM emp WHERE mgr IS NULL
-- count()函數
SELECT COUNT(*) FROM emp

-- 9. 使用邏輯操作符号
-- 問題：查詢工資高于500或者是崗位為manager的雇員，同時還要滿足他們的姓名首字母為大寫的J？
SELECT * FROM emp WHERE (sal > 500 Or job = 'MANAGER') AND ename LIKE 'J%'

-- 10. 使用order by字句 預設asc
-- 問題：如何按照工資從低到高的順序顯示雇員的資訊？
SELECT * FROM emp ORDER BY sal
-- 問題：按照部門号升序而雇員的工資降序排列
SELECT * FROM emp ORDER BY deptno ASC, sal DESC

-- 11. 使用列的别名排序
-- 問題：按年薪排序
SELECT ename, (sal + NVL(comm, 0)) * 12 AS "年薪" FROM emp ORDER BY "年薪" ASC

-- 12. 聚合函數用法：max，min，avg，sum，count
-- 問題：如何顯示所有員工中最高工資和最低工資？
SELECT MAX(sal), MIN(sal) FROM emp e
-- 最高工資那個人是誰？
SELECT ename, sal FROM emp WHERE sal = (SELECT MAX(sal) FROM emp)
/*
錯誤寫法：select ename, sal from emp where sal=max(sal)
注意：select ename, max(sal) from emp;這語句執行的時候會報錯，說ora-00937：非單組分組函數。因為max是分組函數，而ename不是分組函數.......
但是select min(sal), max(sal) from emp;這句是可以執行的。因為min和max都是分組函數，就是說：如果列裡面有一個分組函數，其它的都必須是分組函數，否則就出錯。這是文法規定的
*/
-- 問題：如何顯示所有員工的平均工資和工資總和
SELECT AVG(e.sal) AS "所有員工平均工資", SUM(e.sal) AS "所有員工工資總和" FROM emp e
-- 查詢最高工資員工的名字，工作崗位
SELECT e.ename, e.job FROM emp e WHERE e.sal = (SELECT MAX(sal) FROM emp)
-- 顯示工資高于平均工資的員工資訊
SELECT e.ename FROM emp e WHERE e.sal > (SELECT AVG(sal) FROM emp)

-- 13. GROUP BY的用法
-- 問題：如何顯示每個部門的平均工資和最高工資？
SELECT  AVG(sal) AS "平均工資", MAX(sal) AS "最高工資", deptno
FROM emp
GROUP BY deptno
--（注意：這裡暗藏了一點，如果你要分組查詢的話，分組的字段deptno一定要出現在查詢的清單裡面，否則會報錯。因為分組的字段都不出現的話，就沒辦法分組了）
-- 問題：顯示每個部門的每種崗位的平均工資和最低工資？
SELECT AVG(sal) AS "平均工資", MIN(sal) AS "最低工資", deptno, job
FROM emp
GROUP BY deptno, job
-- 問題：顯示平均工資低于2000的部門号和它的平均工資？
SELECT AVG(sal), deptno
FROM emp
GROUP BY deptno
HAVING AVG(sal) < 2000
/*
對資料分組的總結
(1)分組函數隻能出現在選擇清單、having、order by子句中(不能出現在where中)
(2)如果在select語句中同時包含有group by, having, order by 那麼它們的順序是group by, having, order by
(3)在選擇列中如果有列、表達式和分組函數，那麼這些列和表達式必須有一個出現在group by 子句中，否則就會出錯。
如select deptno, avg(sal), max(sal) from emp group by deptno having avg(sal) < 2000;這裡deptno就一定要出現在group by中
*/

-- 14. 多表查詢
-- 顯示雇員名，雇員工資及所在部門的名字
SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno
-- 問題：顯示部門号為10的部門名、員工名和工資
SELECT d.deptno , d.dname, e.deptno, e.empno, e.ename, e.sal FROM emp e, dept d WHERE 1 = 1 AND e.deptno = d.deptno AND d.deptno = 10
-- 問題：顯示雇員名，雇員工資及所在部門的名字，并按部門排序
SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno ORDER BY e.deptno ASC
-- 自連接配接。自連接配接是指在同一張表的連接配接查詢
-- 問題：顯示某個員工的上級上司的姓名？比如顯示員工‘FORD’的上級
SELECT  worker.ename, boss.ename FROM emp worker, emp boss WHERE worker.mgr = boss.empno AND worker.ename = 'FORD'

-- 15. 單行子查詢？
-- 單行子查詢是指隻傳回一行資料的子查詢語句
-- 請思考：顯示與SMITH同部門的所有員工？
SELECT e.deptno, e.ename
FROM emp e
WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'SMITH')

-- 16. 多行子查詢
-- 多行子查詢指傳回多行資料的子查詢
-- 請思考：如何查詢和部門10的工作相同的雇員的名字、崗位、工資、部門号
SELECT e.ename, e.job, e.sal, e.deptno
FROM emp e
WHERE e.job IN(SELECT job FROM emp WHERE deptno = 10)
--  在多行子查詢中使用all操作符
-- 問題：如何顯示工資比部門30的所有員工的工資高的員工的姓名、工資和部門号？
SELECT e.ename, e.sal, e.deptno
FROM emp e
WHERE e.sal > ALL(SELECT sal FROM emp WHERE deptno = 30)

SELECT e.ename, e.sal , e.deptno
FROM emp e
WHERE e.sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30)

-- 17. 合并查詢
/*有時在實際應用中，為了合并多個select語句的結果，可以使用集合操作符号union，union all，intersect，minus。
多用于資料量比較大的資料局庫，運作速度快。*/
1). union
該操作符用于取得兩個結果集的并集。當使用該操作符時，會自動去掉結果集中重複行。
2).union all
該操作符與union相似，但是它不會取消重複行，而且不會排序。
3). intersect
使用該操作符用于取得兩個結果集的交集。
4). minus
使用該操作符用于取得兩個結果集的差集，他隻會顯示存在第一個集合中，而不存在第二個集合中的資料。
SELECT ename, sal, job FROM emp WHERE sal > 2500
/* UNION */ /*  UNION ALL */ /* INTERSECT */ MINUS
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER'

四、分頁查詢

1. 方法一根據rowid來分

SELECT *
FROM EMP
WHERE ROWID IN
       (SELECT RID
          FROM (SELECT ROWNUM RN, RID
                  FROM (SELECT ROWID RID, EMPNO FROM EMP ORDER BY EMPNO DESC)
                 WHERE ROWNUM <= 5
         WHERE RN > 0 
 ORDER BY EMPNO DESC

2. 方法二按分析函數來分

SELECT *
FROM (SELECT T.*, ROW_NUMBER() OVER(ORDER BY empno DESC) RK FROM emp T)
WHERE RK <= 5
AND RK > 0

3. 方法三按rownum 來分

SELECT *
FROM (SELECT T.*, ROWNUM RN
          FROM (SELECT * FROM EMP ORDER BY EMPNO DESC) T
         WHERE ROWNUM <= 5)
WHERE RN > 0

五、複雜表查詢

1、查詢每個職員的姓名、工種、入社日期，工資和年工資。

SELECT e.ename,e.job,e.hiredate,e.sal,e.sal*12 FROM emp e

2、查找部門平均工資在2000元以上的部門号和部門名稱，按平均工資的降序排列。

SELECT v.deptno,v.dname
FROM (
        SELECT AVG(e.sal) avgsal, d.deptno, d.dname
        FROM emp e
        LEFT JOIN dept d ON e.deptno=d.deptno
        GROUP BY e.deptno,d.deptno,d.dname) v
WHERE v.avgsal > 2000

3、查找營業部（SALES）比研究部（RESEARCH）平均工資都高的職員的情況。（要求使用子查詢來完成）

SELECT e.*
FROM emp e
LEFT JOIN dept d ON e.deptno=d.deptno
WHERE d.dname='SALES' AND e.sal > (
      SELECT DISTINCT AVG(e.sal) OVER (PARTITION BY e.deptno)
      FROM emp e
      LEFT JOIN dept d ON e.deptno=d.deptno
      WHERE d.dname='RESEARCH' )

4、在EMP表的MGR屬性列上建一個名為mgr_bit的位圖索引。

CREATE BITMAP INDEX mgr_bit ON emp(mgr)

5、查詢各個部門的最高工資的職員情況

SELECT e.ename, e.job, e.sal, e.deptno 
FROM emp
WHERE (sal,deptno) IN (SELECT MAX(e.sal), e.deptno FROM emp e GROUP BY e.deptno)

SELECT e.ename, e.job, e.sal, e.deptno
FROM scott.emp e, (select e.deptno, MAX(e.sal) sal from scott.emp e group by e.deptno) me
WHERE e.deptno = me.deptno AND e.sal = me.sal;

SELECT e.ename, e.job, e.sal, e.deptno
FROM (SELECT e.ename,
               e.job,
               e.sal,
               e.deptno,
               RANK() OVER(PARTITION BY e.deptno ORDER BY e.sal desc) RANK FROM scott.emp e) e
 WHERE e.rank = 1;

SELECT e.ename, e.job, e.sal, e.deptno
FROM (SELECT e.ename,
               e.job,
               e.sal,
               e.deptno,
               DENSE_RANK() OVER(PARTITION BY e.deptno ORDER BY e.sal desc) RANK FROM scott.emp e) e
 WHERE e.rank = 1;

6、 查詢各個部門的最高工資的職員情況，同時算出雇員工資與部門最高/最低工資的差額

select e.ename,
         e.job,
         e.sal,
         e.deptno,
         e.sal - me.min_sal diff_min_sal,
         me.max_sal - e.sal diff_max_sal
    from scott.emp e,
         (select e.deptno, min(e.sal) min_sal, max(e.sal) max_sal
            from scott.emp e
           group by e.deptno) me
   where e.deptno = me.deptno
   order by e.deptno, e.sal;

select e.ename,
       e.job,
       e.sal,
       e.deptno,
       nvl(e.sal - min(e.sal) over(partition by e.deptno), 0) diff_min_sal,
       nvl(max(e.sal) over(partition by e.deptno) - e.sal, 0) diff_max_sal
  from scott.emp e;

7、 計算個人工資與比自己高一位/低一位工資的差額

select e.ename,
       e.job,
       e.sal,
       e.deptno,
       lead(e.sal, 1, 0) over(partition by e.deptno order by e.sal) lead_sal,
       lag(e.sal, 1, 0) over(partition by e.deptno order by e.sal) lag_sal,
       nvl(lead(e.sal) over(partition by e.deptno order by e.sal) - e.sal,
           0) diff_lead_sal,
       nvl(e.sal - lag(e.sal) over(partition by e.deptno order by e.sal), 0) diff_lag_sal
  from scott.emp e;

8、查詢各個部門比平均工資高的員工

SELECT e.ename, e.deptno, e.sal, ds.mysal
FROM emp e, (SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno) ds
WHERE e.deptno = ds.deptno AND e.sal > ds.mysal;

9、顯示deptno和job唯一的員工薪資，并按降序排列

SELECT a.ename, a.sal, a.deptno, a.job
FROM emp a
WHERE a.empno in
(
SELECT MAX(e.empno)  
FROM emp e
GROUP BY e.deptno,e.job
)
ORDER BY a.sal DESC

SELECT a.ename, a.sal, a.deptno, a.job
FROM emp a, (
SELECT MAX(e.rowid) AS myrowid, e.deptno, e.job, count(*)
FROM emp e
GROUP BY e.deptno,e.job
HAVING count(*) >= 1
) me
WHERE a.rowid IN me.myrowid
ORDER BY a.sal DESC

Oracle SQL大全一、基本操作表和資料二、表的使用技巧三、 SQL基本查詢技巧四、分頁查詢五、複雜表查詢

一、基本操作表和資料

二、表的使用技巧

三、 SQL基本查詢技巧

四、分頁查詢

五、複雜表查詢

繼續閱讀

快速删除ORACLE重複記錄

oracle海量資料表删除重複記錄

[引]在Oracle中如何利用Rowid查找和删除表中的重複記錄

oralce與mysql中，如何删除重複記錄

查詢表中重複記錄

ORACLE 表壓縮

oracle中的start with connect by用法

使用JDeveloper Remote debug PLSQL程式

LoadRunner 的多種Vuser類型

性能測試（并發負載壓力）測試分析－簡要篇

賦予使用者dba權限--解釋以上：

Oracle的基本操作

為什麼要選擇UniDAC

windows不能在本地計算機上運作oracleDbConsoleorcl

SQL語言基礎：常用的資料查詢語句

Oracle 批量查詢傳入List 傳回List

Oracle SQL大全一、 基本操作表和資料二、 表的使用技巧三、 SQL基本查詢技巧四、分頁查詢五、 複雜表查詢

一、 基本操作表和資料

二、 表的使用技巧

三、 SQL基本查詢技巧

四、分頁查詢

五、 複雜表查詢

繼續閱讀

Oracle SQL大全一、基本操作表和資料二、表的使用技巧三、 SQL基本查詢技巧四、分頁查詢五、複雜表查詢

一、基本操作表和資料

二、表的使用技巧

五、複雜表查詢