第一章 基本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) ;