成都心情

第一章        基本SELECT 語句

ORACLE數據庫的體系結構。
SYS和SYSTEM用戶。

SQL語句不區分大小寫。
‘’和””的區別。
字符串區分大小寫。

NULL值。
聯接運算符：   ||
時間的默認格式為DD-MON-YY。

SELECT * FROM TAB;

字段別名
SELECT job_title AS Title, min_salary AS “Minimum Salary” FROM jobs;

保證唯一性
SELECT DISTINCT department_id,job_id FROM employees;

DUAL表
SELECT SYSDATE, USER FROM DUAL;

不等于(!=、<>或^=)
SELECT first_name || ‘ ‘ || last_name “Name”,commission_pct FROM employees
WHERE commission_pct != 0.35 ;

<=(小于等于)
SELECT first_name || ‘ ‘ || last_name “Name”,commission_pct FROM employees
WHERE commission_pct <= 0.35 ;

>=(大于等于)
SELECT first_name || ‘ ‘ || last_name “Name”,commission_pct FROM employees
WHERE commission_pct >= 0.35 ;

ANY(相當于OR)
SELECT first_name || ‘ ‘ || last_name “Name”,department_id FROM employees
WHERE department_id <= ANY (10,15,20,25);

ALL(相當于AND)
SELECT first_name || ‘ ‘ || last_name “Name”,department_id FROM employees
WHERE department_id >= ALL (80,90,100);

IN 和 NOT IN
SELECT first_name,last_name,department_id FROM employees
WHERE department_id IN (10,20,90);

IN 相當于 =ANY。

SELECT first_name,last_name,department_id FROM employees
WHERE department_id NOT IN
(10,30,40,50,60,80,90,110,100);

NOT IN 相當于 !=ALL。

注意：last_name NOT IN(‘Smith’,’Thomas’,NULL)
不返回任何記錄。
任何與NULL值的比較都是NULL值。

BETWEEN
SELECT first_name,last_name,salary FROM employees WHERE salary BETWEEN 5000 AND 6000;

EXISTS
SELECT last_name,first_name,department_id FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d
WHERE d.department_id = e.department_id
   AND d.department_name=’Administration’);

IS NULL和IS NOT NULL
SELECT last_name,department_id FROM employees WHERE department_id IS NULL;

LIKE
SELECT first_name,last_name FROM employees WHERE first_name LIKE ‘Su%’
AND last_name NOT LIKE ‘S%’;
%、_的含義。

SELECT job_id,job_title FROM jobs WHERE job_id LIKE ‘AC\_%’ ESCAPE ‘\’;

結果集的排序
SELECT first_name || ‘ ‘ || last_name “Employee Name” FROM employees WHERE department_id =90 ORDER BY last_name;
ASC、DESC。
SELECT first_name,hire_date,salary,manager_id mid FROM employees WHERE department_id IN (110,100) ORDER BY mid ASC,salary DESC, hire_date;

錯誤：SELECT DISTINCT ‘Region ‘ || region_id FROM countries ORDER BY region_id;
正確：SELECT DISTINCT ‘Region ‘ || region_id FROM countries
ORDER BY ‘Region ‘ ||region_id;

SELECT first_name,hire_date,salary,manager_id mid FROM employees WHERE department_id IN (110,100) ORDER BY 4,2,3;

NULL的排序
默認情況下,升序排列中NULL值排在最后。
SELECT last_name,commission_pct FROM employees WHERE last_name LIKE ‘R%’
ORDER BY commission_pct ASC, last_name DESC;

SELECT last_name,commission_pct FROM employees WHERE last_name LIKE ‘R%’
ORDER BY commission_pct ASC NULLS FIRST, last_name DESC;

CASE 表達式(9i新增)
SELECT country_name,region_id,
        CASE region_id WHEN 1 THEN ‘Europe’
                   WHEN 2 THEN  ‘America’
WHEN 3 THEN ‘Asia’
ELSE ‘Other’ END Continent
FROM countries WHERE country_name LIKE ‘I%’;

SELECT first_name,department_id,salary,
        CASE WHEN salary < 6000 THEN ‘Low’
         WHEN salary < 10000 THEN ‘Medium’
         WHEN salary >= 10000 THEN  ‘High’ END Category
FROM employees WHERE department_id <=30 ORDER BY first_name;

第二章        SQL * Plus的使用
SQL * Plus的常用命令。

第三章        Oracle 9i 中的函數

NVL
SELECT first_name,last_name,salary,bonus,salary + bonus total_comp FROM employees;

SELECT first_name,last_name,salary,bonus,salary + NVL(bonus,0)
total_comp FROM employees;

NVL2(9i新增)
SELECT first_name,last_name,salary,bonus, NVL2(bonus,salary + bonus,salary)
total_comp FROM employees;

字符串函數
SELECT ASCII(‘A’) Big_A,ASCII(‘z’) Little_z FROM dual;
SELECT CHR(65),CHR(223) FROM dual;
SELECT CONCAT(‘Peter’,’Mackovicky’) FROM dual;
SELECT INITCAP(‘the three musketeers’) book_title FROM dual;
SELECT INSTR(‘Mississippi’,’i’,3,3) test1, INSTR(‘Mississippi’,’i’,1,3) test2
       INSTR(‘Mississippi’,’i’,-2,3) test3 FROM dual;

解析數據
text_string =’Sunday|Monday|Tuesday’
SELECT text_string,SUBSTR(text_string,INSTR(text_string,’|’,1,1) + 1,
INSTR(text_string,’|’,1,2) - INSTR(text_string,’|’,1,1) – 1 ) FROM dual;

INSTRB
SELECT LENGTH(‘the three musketeers’) title_length FROM dual;
LENGTHB
LOWER
SELECT LPAD(‘Yes’,7,’.’) FROM dual;
SELECT LTRIM(‘Mississippi’,’Mis’) test1, LTRIM(‘RPadded        ’) test2,
       LTRIM(‘     RPadded’) test3, LTRIM(‘     RPadded’,’Z’) test4 FROM dual;

數學函數
ABS、ACOS、ASIN、ATAN、ATAN2、BITAND、CEIL、COS、COSH、EXP、FLOOR、LN、LOG、MOD、POWER、SIGN、SIN、SINH、SQRT、TAN、TANH。
ROUND=TRUNC
SELECT TRUNC(123.456,2) pos, TRUNC(123.456,-1) neg FROM dual;

日期函數
ADD_MONTHS、LAST_DAY、MONTH_BETWEEN、NEXT_TIME、NEXT_DAY、ROUND、SYSDATE。

轉換函數
CHARTOROWID、CONVERT、HEXTORAW、NLS_CHARSET_ID、NLS_CHARSET_NAME、
RAWTOHEX、ROWIDTOCHAR、TO_CHAR、 TO_DATE、 TO_ LABEL、 TO_MULTI_BYTE、
TO_NUMBER、TO_SINGLE_BYTE。

SELECT TO_CHAR(SYSDATE,’yyyy-mm-dd hh24:mi:ss’) FROM dual;
SELECT TO_CHAR(SYSDATE,’month’) FROM dual;
SELECT TO_CHAR(SYSDATE,’SSSSS’) FROM dual;

INSERT INTO demo(demo_key,date_col)
   VALUES (1,TO_DATE(‘2004-7-1’,’yyyy-mm-dd’));

其它函數
SELECT COALESCE(NULL,’Oracle’,’24’) string_type,
       COALESCE(3,14,COS(0)) bnr_type FROM dual;
返回第一個非NULL值。

SELECT DECODE(rating,null,1000,’C’,2000,’B’,3000.’D’,4000,5000) FROM accts;

SELECT USER,UID FROM dual;

GREATEST、LEAST。

SELECT ename,mgr,comm,NULLIF(comm,0) test1 FROM scott.emp
       WHERE empno IN(7844,7839,7654,7369); (9i新增)
如果x1=x2,返回NULL。否則x1。

SELECT SYS_CONTEXT(‘USERENV’,’IP_ADDRESS’) FROM dual; (9i新增)

USERENV函數在9i中停用，建議用SYS_CONTEXT函數。

SELECT VSIZE(user),user FROM dual;

第四章        分組匯總語句

GROUP BY 語句
SELECT cust_state_province, count(*) customer_count
        FROM sh.customers GROUP BY cust_state_province;
重點：GROUP BY 語句中SELECT后字段的限制：只能是分組字段和集合函數。
GROUP BY的結果集排序。

SELECT deptno,MIN(sal),MAX(sal)  FROM emp  WHERE job=’CLERK’
GROUP  BY deptno; (注意此處與SQL Server不同!)

SELECT cust_state_province, count(*) customer_count
        FROM sh.customers GROUP BY cust_state_province ORDER BY COUNT(*) DESC;
或：SELECT cust_state_province, count(*) customer_count
        FROM sh.customers GROUP BY cust_state_province ORDER BY 2 DESC;

缺定表的一個數據塊中平均有多少行數據：
SELECT AVG(row_count ),MAX(row_count),MIN(row_count)
FROM (SELECT count(*) row_count FROM employees GROUP BY SUBSTR(rowid,1,15));

HAVING
SELECT t.fiscal_month_desc,s.channel_id,SUM(s.quantity_sold),SUM(s.amount_sold)
FROM sh.times t,sh.sales s WHERE t.time_id = s.time_id AND s.promo_id <> 9999
GROUP BY t.fiscal_month_desc,s.channel_id HAVING SUM(s.amount_sold) > 2000000;
HAVING=WHERE。

錯誤：
SELECT t.fiscal_month_desc,s.channel_id,SUM(s.quantity_sold),SUM(s.amount_sold)
FROM sh.times t,sh.sales s WHERE t.time_id = s.time_id AND s.promo_id <> 9999 AND SUM(s.amount_sold) > 2000000  GROUP BY t.fiscal_month_desc,s.channel_id;

CUBE和ROLLUP
SELECT cust_gender gender,NVL(cust_marital_status,’unknown’) marital_status,COUNT(*)
FROM sh.customers GROUP BY cust_gender, NVL(cust_marital_status,’unknown’);

SELECT cust_gender gender,NVL(cust_marital_status,’unknown’) marital_status,COUNT(*)
FROM sh.customers GROUP BY cust_gender, ROLLUP(NVL(cust_marital_status,’unknown’ ));

SELECT cust_gender gender,NVL(cust_marital_status,’unknown’) marital_status,COUNT(*)
FROM sh.customers GROUP BY ROLLUP(cust_gender, NVL(cust_marital_status,’unknown’ ));

SELECT cust_gender gender,NVL(cust_marital_status,’unknown’) marital_status,COUNT(*)
FROM sh.customers GROUP BY CUBE(cust_gender, NVL(cust_marital_status,’unknown’ ));

嵌套函數
SELECT deptno,GREATEST(COUNT(DISTINCT job),COUNT(DISTINCT mgr)) cnt,
       COUNT(DISTINCT job ) jobs,   COUNT(DISTINCT mgr) mgrs
FROM emp GROUP BY deptno;

SELECT MAX(COUNT(DISTINCT job)) FROM emp GROUP BY deptno;

集合函數
SELECT empno,sal FROM scott.emp WHERE deptno=20 ORDER BY sal;

DISTINCT和ALL的區別
SELECT AVG(sal) avg, AVG(ALL sal) avg_all,AVG(DISTINCT sal) avg_dist,
COUNT(sal) cnt, COUNT(DISTINCT sal) cnt_dist,SUM(sal) sum_all,SUM(DISTINCT sal ) sum_dist FROM scott.emp WHERE deptno=20;

默認是ALL。

SELECT job_id,AVG(salary) FROM hr.employees WHERE job_id LIKE ‘AC%’ GROUP BY job_id;

SELECT COUNT(*) emp_count,
       COUNT(DISTINCT department_id) dept_count,
       COUNT(ALL department_id) not_null_dept_count FROM hr.employees;

SELECT cust_gender gender, NVL(cust_marital_status,’unknown’) marital_status,
COUNT(*) emp_count, GROUPING(cust_gender) gender_superagg,
GROUPING(NVL(cust_marital_status,’unknown’)) martial_superagg
FROM sh.customers GROUP BY CUBE(cust_gender, NVL(cust_marital_status,’unknown’));

Create table tmp as
SELECT cust_gender gender, NVL(cust_marital_status,’unknown’) marital_status,
COUNT(*) emp_count, GROUPING(cust_gender) gender_superagg,
GROUPING(NVL(cust_marital_status,’unknown’)) martial_superagg
FROM sh.customers GROUP BY CUBE(cust_gender, NVL(cust_marital_status,’unknown’));

第五章        聯接語句

Oracle 9i中新增加了JOIN子句，以符合 ANSI SQL 1999標準。

對等聯接(內聯接)
Oracle語法：
SELECT locations.location_id,city,department_name FROM locations,departments
WHERE locations.location_id = departments.location_id;

SELECT locations.location_id,city,department_name FROM locations,departments
WHERE locations.location_id = departments.location_id AND country_id != ‘US’;

使用表的別名
SELECT l.location_id,city,department_name FROM
locations l,departments d WHERE l.location_id = d.location_id AND country_id != ‘US’;

當使用表的別名時，只能用別名來限定表名。
錯誤：SELECT locations.location_id,city,department_name FROM
locations l,departments d WHERE locations.location_id = d.location_id;

ANSI語法：
NATURAL JOIN
SELECT location_id,city,department_name FROM locations NATURAL JOIN departments;
自動使用兩個表的同名字段進行聯接。
不要用表名或表的別名來限定列名。

SELECT * FROM regions NATURAL JOIN countries;

SELECT region_name,country_name,city FROM regions NATURAL JOIN countries
NATURAL JOIN locations;
等價于：SELECT region_name,country_name,city FROM regions,countries,locations
WHERE regions.region_id = countries.region_id AND
countries.country_id= locations.country_id;

JOIN … USING
當兩個表的共同字段數據類型不同時。
SELECT location_id,city,department_name FROM locations  JOIN departments USING(location_id);

SELECT region_name,country_name,city FROM regions
JOIN countries USING(region_id)
JOIN locations USING(country_id);

SELECT region_name,country_name,city FROM regions
JOIN countries USING(region_id)
JOIN locations USING(country_id)
WHERE country_id = ‘US’
ORDER BY 1;

JOIN ON
SELECT region_name,country_name,city FROM regions r
JOIN countries c ON r.region_id = c.region_id 
JOIN locations l ON c.country_id= l.country_id
WHERE country_id = ‘US’;

SELECT first_name,department_name,city
FROM employees e JOIN departments d ON (e.department_id = d.department_id)
JOIN locations l ON (d.location_id = l.location_id);

笛卡爾乘積(交叉聯接)
Oracle語法：
SELECT region_name,country_name FROM regions,countries WHERE countries.country_id LIKE ‘I%’;
ANSI 語法：
SELECT region_name,country_name FROM regions CROSS JOIN countries
WHERE countries.country_id LIKE ‘I%’;

外聯接(非對等聯接)
Oracle語法：
SELECT c.country_name,l.city FROM countries c ,locations l WHERE c.country_id = l.country_id(+);

select ename,emp.deptno,dname from emp,dept where emp.deptno(+)=dept.deptno(+);

外聯接符(+)要用于所有的條件。
SELECT c.country_name,l.city FROM countries c ,locations l WHERE c.country_id = l.country_id(+) AND l.city LIKE ‘B%’;

SELECT c.country_name,l.city FROM countries c ,locations l WHERE c.country_id = l.country_id(+) AND l.city(+) LIKE ‘B%’;

外聯接符(+)不能用于OR和IN。
錯誤：SELECT c.country_name,l.city FROM countries c ,locations l WHERE c.country_id = l.country_id(+) OR l.city(+) LIKE ‘B%’;

正確：SELECT c.country_name,l.city FROM countries c ,locations l WHERE c.country_id = l.country_id(+) AND c.country_name IN (‘India’,’Israel’);

ANSI語法：
左外聯接
SELECT c.country_name,l.city FROM countries c LEFT OUTER JOIN locations l ON
c.country_id = l.country_id;

select ename,emp.deptno,dname from emp LEFT OUTER JOIN dept ON emp.deptno=dept.deptno;
OUTER是可選的。
SELECT country_name,city FROM countries  LEFT JOIN locations USING(country_id);
SELECT country_name,city FROM countries  NATURAL LEFT JOIN locations;

右外聯接
SELECT country_name,city FROM locations  NATURAL RIGHT OUTER JOIN countries;

SELECT c.country_name,l.city FROM locations l RIGHT JOIN countries c ON
c.country_id = l.country_id;

完全外聯接(9i新增)
SELECT e.employee_id,e.last_name,d.department_id,d.department_name
FROM employees e FULL OUTER JOIN departments d ON (e.department_id = d.department_id)

錯誤：select ename,emp.deptno,dname from emp,dept where emp.deptno(+)=dept.deptno(+);

自聯接
用于層次型數據庫。
SELECT e.last_name Employee, m.last_name Manager FROM employees e ,employees m
WHERE m.employee_id = e.manager_id;

SELECT e.last_name Employee, m.last_name Manager FROM employees e INNER JOIN
employees m ON m.employee_id = e.manager_id;

第六章        集合語句和子查詢

集合語句：把多個SELECT語句的結果集合并成一個結果集。
UNION ALL 合并多個結果集，不消除重復值。
UNION     合并多個結果集，消除重復值。
INTERSECT
MINUS

SELECT last_name, hire_date FROM employees WHERE department_id = 90;
SELECT last_name, hire_date FROM employees WHERE last_name LIKE ‘K%’;

SELECT last_name, hire_date FROM employees WHERE department_id = 90
UNION ALL
SELECT last_name, hire_date FROM employees WHERE last_name LIKE ‘K%’;

SELECT last_name, hire_date FROM employees WHERE department_id = 90
UNION
SELECT last_name, hire_date FROM employees WHERE last_name LIKE ‘K%’;

SELECT last_name, hire_date FROM employees WHERE department_id = 90
INTERSECT
SELECT last_name, hire_date FROM employees WHERE last_name LIKE ‘K%’;

SELECT last_name, hire_date FROM employees WHERE department_id = 90
MINUS
SELECT last_name, hire_date FROM employees WHERE last_name LIKE ‘K%’;

錯誤：
SELECT last_name, hire_date FROM employees WHERE department_id = 90 ORDER BY last_name
MINUS
SELECT first_name, hire_date FROM employees WHERE last_name LIKE ‘K%’ ORDER BY first_name;

SELECT last_name, hire_date “Join Date” FROM employees WHERE department_id = 90 MINUS
SELECT first_name, hire_date FROM employees WHERE last_name LIKE ‘K%’ ORDER BY last_name,  “Join Date”;

SELECT last_name, hire_date “Join Date” FROM employees WHERE department_id = 90 MINUS
SELECT first_name, hire_date FROM employees WHERE last_name LIKE ‘K%’ ORDER BY 1,2;

子查詢
子查詢的特征：   子查詢要加括號。
             分為嵌套子查詢和相關子查詢。(區別：子查詢里是否包含聯接。)
             分為單值和多值。
             相關子查詢可用聯接語句代替。

單值嵌套子查詢的例子：
SELECT lastname,firstname,salary FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

SELECT lastname,firstname,salary FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name=’Accounting’);

多值嵌套子查詢的例子：
SELECT lastname,firstname,salary FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE first_name=’John’);

相關子查詢的例子：
SELECT department_id,last_name,salary FROM employees e1 WHERE salary =
        (SELECT MAX(salary) FROM employees e2 WHERE e1.department_id = e2.department_id);

比較：SELECT department_id,last_name,salary FROM employees WHERE salary =
        (SELECT MAX(salary) FROM employees );

SELECT last_name,first_name, department_id FROM employees e1 WHERE EXISTS
        (SELECT ‘x’ FROM employees e2 WHERE first_name= ‘John’ AND e1.department_id = e2.department_id);

子查詢用在case表達式中：
SELECT city,country_id,(CASE
      WHEN country_id IN
(SELECT country_id FROM countries WHERE country_name = ‘India’) THEN ‘Indian’
ELSE ‘Non_Indian’
END) “INDIA?”
FROM locations WHERE city LIKE ‘’B%;

子查詢用在SELECT子句中：
SELECT last_name,department_id,
     (SELECT MAX(salary) FROM employees sq WHERE sq.ddepartment_id = e.department_id) HSAL FROM employees e WHERE last_name LIKE ‘R%’;

UPDATE employees e1
SET salary = (SELECT MAX(salary) FROM employees e2 WHERE e1.department_id= e2.department_id);

DELETE FROM employees e WHERE salary <(SELECT AVG(salary) FROM employees WHERE department_id= e.department_id);

INSERT INTO employee_archive SELECT * FROM employees;

INSERT INTO departments(department_id,department_name) VALUES
((SELECT MAX(department_id) + 10 FROM departments),’EDP’);

可以在INSERT、UPDATE、DELETE語句中使用一個子查詢來代替表名。
DELETE FROM (SELECT * FROM departments WHERE department_id < 20) WHERE department_id =10;

DELETE FROM (SELECT * FROM department WHERE department_id < 20) WHERE department_id =10;

INSERT INTO (SELECT department_id,department_name FROM department WHERE department_id < 20) VALUES (35, ‘MARKETING’);

WITH READ ONLY、 WITH CHECK OPTION。

INSERT INTO (SELECT department_id,department_name FROM departments WHERE department_id < 20 WITH CHECK OPTION) VALUES (45, ‘MARKETING’);

第七章        修改數據(INSERT、UPDATE、DELETE語句)

INSERT語句
INSERT INTO checking(account_id,create_date,balance)
VALUES(‘Kiesha’,SYSDATE,5000);

INSERT INTO brokerage(account_id,create_date,balance)
SELECT account_id, SYSDATE,0 FROM checking WHERE account_type =’C’;

INSERT INTO e_checking
SELECT * FROM checking WHERE account_type =’C’;

UPDATE語句
UPDATE order_rollup SET (qty,price) = (SELECT SUM(qty),SUM(price) FROM order_lines
       WHERE customer_id=’KHOL’) WHERE customer_id =’KHOL’ AND order_period= TO_DATE(’01-Oct-2001’);

UPDATE order_rollup SET phone = ‘123456’,fax=’234567’ WHERE customer_id=’KHOL’

DELETE語句
DELETE FROM customers WHERE customer_id = ‘GOMEZ’;
DELETE FROM order_staging;
DML
TRUNCATE語句
TRUNCATE TABLE order_staging;
TRUNCATE語句是一個DDL語句。

SELECT … FOR UPDATE 語句

事務語句
COMMIT、COMMIT WORK。
ROLLBACK。
SAVEPOINT。

DDL與語句不需要提交。

RENAME語句
rename T1 to T2;

CREATE TABLE … AS SELECT …語句
CREATE TABLE emp_tmp AS SELECT*FROM emp WHERE deptno=10;
復制表：create table t2 as select * from t1;
復制表的結構：create table t2 as select*from t1 where 1=2;

Top n 語句
SELECT last_name,salary FROM employees WHERE rownum <=5 ORDER BY salary DESC;

.........................................................................................................................
useraccount表，主鍵為userid，有一個字段為手機號碼mobilephone

如果重復的記錄的比例不是很大

delete useraccount where rowid in (
select rid from
(select rowid rid,row_number() over(partition by mobilephone order by userid desc) rn from useraccount )
where rn > 1) ;