oracle SQL集錦

 

1  普通的查詢語句，例如：
     SELECT last_name, department_id FROM employees;
2   查詢檢視
     SELECT employee_id, last_name, job_title, department_name, country_name,
       region_name FROM emp_details_view;
3   更換列名查詢
     SELECT employee_id "Employee ID number", last_name "Employee last name",
             first_name "Employee first name" FROM employees;
4   聯結查詢
     自然聯結：要求兩個表中的公用列必須有相同的名稱和結構類型，否則報錯。
     SELECT employee_id, last_name, first_name, department_id,
             department_name, manager_id  FROM employees  NATURAL JOIN departments;
     查詢三張或以上表時用USING，前提是用來串連兩張表的列必須名稱相同。
     SELECT e.employee_id, e.last_name, e.first_name, e.manager_id, department_id,
             d.department_name, d.manager_id FROM employees e
             JOIN departments d USING (department_id);
     聯結時加上WHERE 子句。
  SELECT e.employee_id, e.last_name, e.first_name, e.department_id,
             d.department_name, d.manager_id, d.location_id, l.country_id FROM employees e
             JOIN departments d ON e.department_id = d.department_id
             JOIN locations l ON d.location_id = l.location_id
             WHERE l.location_id = 1700;
     自我聯結：
     SELECT e.employee_id emp_id, e.last_name emp_lastname, m.employee_id mgr_id,
             m.last_name mgr_lastname
             FROM employees e
             JOIN employees m ON e.manager_id = m.employee_id;
      LEFT OUTER JOIN：左表中所有的記錄即使在右表中沒有聯結的都會被查詢出來。
     SELECT e.employee_id, e.last_name, e.department_id, d.department_name
             FROM employees e LEFT OUTER JOIN departments d
             ON (e.department_id = d.department_id);
     RIGHT OUTER JOIN：右表中所有的記錄即使在左表中沒有聯結的都會被查詢出來。
     SELECT e.employee_id, e.last_name, e.department_id, d.department_name
            FROM employees e RIGHT OUTER JOIN departments d
            ON (e.department_id = d.department_id);
     FULL OUTER JOIN：左右表中的記錄全部被查詢出來。
     SELECT e.employee_id, e.last_name, e.department_id, d.department_name
            FROM employees e FULL OUTER JOIN departments d
            ON (e.department_id = d.department_id);

5    綁定變數查詢：Oracle將已解析、已編譯的SQL連同其他內容儲存在共用池中，這是SGA中一個非常重要的儲存結構（記憶體結構主要分為SGA和PGA）。而綁定變數查詢在Oracle裡執行時只編譯一次，隨後就會把這個查詢計劃儲存在一個共用池中以便重用，即所謂的軟解析。具體的使用方法如下：
        SELECT * FROM employees WHERE employee_id = :employee_id
6      查詢虛擬列：所謂的虛擬列在Oracle裡類似表格的列，但並非儲存在表中。查詢虛擬列時將返回一個值，因此它又類似與函數。Oracle裡的虛擬列有：ROWNUM, SYSDATE, and USER。eg：
        SELECT SYSDATE "NOW" FROM DUAL;
        SELECT USER FROM DUAL;
        SELECT employee_id, hire_date, SYSDATE FROM employees WHERE ROWNUM < 10;
7    帶函數查詢：
      Using Numeric Functions：
        SELECT employee_id, ROUND(salary/30, 2) "Salary per day" FROM employees;
        SELECT employee_id, TRUNC(salary/30, 0) "Salary per day" FROM employees;
        SELECT employee_id, MOD(employee_id, 2) FROM employees;
      Using Character Functions：
        SELECT employee_id, UPPER(last_name), LOWER(first_name) FROM employees;
        SELECT employee_id, INITCAP(first_name), INITCAP(last_name) FROM employees;
        SELECT employee_id, RTRIM(first_name) || ' ' || LTRIM(last_name) FROM employees;
        SELECT employee_id, TRIM(last_name) || ', ' || TRIM(first_name) FROM employees;
        SELECT employee_id, RPAD(last_name, 30, ' '), first_name FROM employees;
        SELECT employee_id, SUBSTR(last_name, 1, 10) FROM employees;
        SELECT LENGTH(last_name) FROM employees;
        SELECT employee_id, REPLACE(job_id, 'SH', 'SHIPPING') FROM employees
                    WHERE SUBSTR(job_id, 1, 2) = 'SH';
        Using Date Functions：
        SELECT employee_id, TRUNC(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) "Months Employed"
                     FROM employees;
        SELECT employee_id, EXTRACT(YEAR FROM hire_date) "Year Hired" FROM employees;
        SELECT EXTRACT(YEAR FROM SYSDATE) || EXTRACT(MONTH FROM SYSDATE) ||
                   EXTRACT(DAY FROM SYSDATE) "Current Date" FROM DUAL;
        SELECT employee_id, hire_date, ADD_MONTHS(hire_date, 3) FROM employees;
        SELECT employee_id, hire_date, LAST_DAY(hire_date) "Last day of month"
                   FROM employees;
        SELECT SYSTIMESTAMP FROM DUAL;
      Using Conversion Functions：
        SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY AD') "Today" FROM DUAL;
        SELECT TO_CHAR(SYSDATE, 'FMMonth DD YYYY') "Today" FROM DUAL;
        SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "Now" FROM DUAL;
        SELECT hire_date, TO_CHAR(hire_date,'DS') "Short Date" FROM employees;
        SELECT hire_date, TO_CHAR(hire_date,'DL') "Long Date" FROM employees;
        SELECT TO_CHAR(EXTRACT(YEAR FROM SYSDATE)) ||
                      TO_CHAR(EXTRACT(MONTH FROM SYSDATE),'FM09') ||
                      TO_CHAR(EXTRACT(DAY FROM SYSDATE),'FM09') "Current Date" FROM DUAL;
        SELECT TO_CHAR(CURRENT_DATE, 'DD-MON-YYYY HH24:MI:SS') "Current Date" FROM DUAL;
        SELECT TO_CHAR(salary,'$99,999.99') salary FROM employees;
        SELECT TO_NUMBER('1234.99') + 500 FROM DUAL;
        SELECT TO_NUMBER('11,200.34', '99G999D99') + 1000 FROM DUAL;
        SELECT TO_DATE('27-OCT-98', 'DD-MON-RR') FROM DUAL;
        SELECT TO_DATE('28-Nov-05 14:10:10', 'DD-Mon-YY HH24:MI:SS') FROM DUAL;
        SELECT TO_DATE('January 15, 2006, 12:00 A.M.', 'Month dd, YYYY, HH:MI A.M.')
                     FROM DUAL;
        SELECT TO_TIMESTAMP('10-Sep-05 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF')
                     FROM DUAL;
        Using Aggregate Functions：
        SELECT COUNT(*) "Employee Count" FROM employees WHERE manager_id = 122;
        SELECT COUNT(*) "Employee Count", manager_id  FROM employees
                    GROUP BY manager_id  ORDER BY manager_id
        SELECT COUNT(commission_pct) FROM employees;
        SELECT COUNT(DISTINCT department_id) FROM employees;
        SELECT MIN(salary), MAX(salary), AVG(salary), job_id FROM employees
                    GROUP BY job_id  ORDER BY job_id;
        SELECT RANK(2600) WITHIN GROUP
                  (ORDER BY salary DESC) "Rank of $2,600 among clerks"
                   FROM employees WHERE job_id LIKE '%CLERK';
        SELECT job_id, employee_id, last_name, salary, DENSE_RANK() OVER
                  (PARTITION BY job_id ORDER BY salary DESC) "Salary Rank (Dense)"
                   FROM employees WHERE job_id = 'SH_CLERK';
        SELECT employee_id, salary, hire_date, STDDEV(salary)
                   OVER (ORDER BY hire_date) "Std Deviation of Salary"
                   FROM employees WHERE job_id = 'ST_CLERK';
        Using NULL Value Functions：
        SELECT commission_pct, NVL(commission_pct, 0) FROM employees;
        SELECT phone_number, NVL(phone_number, 'MISSING') FROM employees;
        SELECT employee_id , last_name,commission_pct, salary,
                    NVL2(commission_pct, salary + (salary * commission_pct), salary) income
                    FROM employees;
         Using Conditional Functions：
         SELECT employee_id, hire_date , salary,
                    CASE WHEN hire_date < TO_DATE('01-JAN-90') THEN salary*1.20
             WHEN hire_date < TO_DATE('01-JAN-92') THEN salary*1.15
             WHEN hire_date < TO_DATE('01-JAN-94') THEN salary*1.10
             ELSE salary*1.05 END  "Revised Salary"
                    FROM employees;
         SELECT employee_id, job_id , salary,
                    DECODE(job_id, 'PU_CLERK', salary*1.05,
                 'SH_CLERK', salary*1.10,
                 'ST_CLERK', salary*1.15,
                             salary) "Revised Salary"
                    FROM employees;