Oracle基本数据查询

SELECT * FROM customers  
WHERE customer_id NOT IN(2,3,4,NULL);  
  
SELECT * FROM customers  
WHERE customer_id BETWEEN 1 AND 4;  
  
SELECT * FROM customers  
WHERE customer_id NOT BETWEEN 1 AND 4;  
  
--NAN的意思是非数字  
SELECT * FROM customers  
WHERE customer_id IS NAN;  
  
--逻辑运算符AND OR NOT  
SELECT * FROM customers   
WHERE dob>'01-JAN-1970' AND customer_id>3;  
  
SELECT * FROM customers   
WHERE dob>'01-JAN-1970' OR NOT customer_id > 3;  
  
--ORDER BY进行排序  
SELECT * FROM customers  
ORDER BY last_name;  
  
SELECT * FROM customers  
ORDER BY first_name ASC, last_name DESC;  
  
--直接用数字表示第几列  
SELECT * FROM customers  
ORDER BY 1 ASC, 2 DESC;  
  
--多表连接查询  
SELECT products.name, product_types.name  
FROM products, product_types  
WHERE products.product_type_id = product_types.product_type_id  
AND products.product_id = 3;  
  
SELECT products.name, product_types.name  
FROM products, product_types  
WHERE products.product_type_id = product_types.product_type_id  
ORDER BY products.name;  
  
--使用表别名  
SELECT P.NAME, PT.NAME  
FROM products P, product_types PT  
WHERE p.product_type_id = pt.product_type_id  
ORDER BY p.name;  
  
--笛卡尔集  
SELECT PT.product_type_id, P.product_id  
FROM product_types PT, products P;  
  
--多表连接查询  
SELECT C.FIRST_NAME, C.LAST_NAME, P.NAME AS PRODUCT, PT.NAME AS TYPE  
FROM customers C, purchases PR, products P, product_types PT  
WHERE c.customer_id = pr.customer_id  
AND p.product_id = pr.product_id  
AND p.product_type_id = pt.product_type_id  
ORDER BY p.name; 

SELECT * FROM customers  
WHERE customer_id NOT IN(2,3,4,NULL);  
SELECT * FROM customers  
WHERE customer_id BETWEEN 1 AND 4;  
SELECT * FROM customers  
WHERE customer_id NOT BETWEEN 1 AND 4;  
--NAN的意思是非数字  
SELECT * FROM customers  
WHERE customer_id IS NAN;  
--逻辑运算符AND OR NOT  
SELECT * FROM customers   
WHERE dob>'01-JAN-1970' AND customer_id>3;  
SELECT * FROM customers   
WHERE dob>'01-JAN-1970' OR NOT customer_id > 3;  
--ORDER BY进行排序  
SELECT * FROM customers  
ORDER BY last_name;  
SELECT * FROM customers  
ORDER BY first_name ASC, last_name DESC;  
--直接用数字表示第几列  
SELECT * FROM customers  
ORDER BY 1 ASC, 2 DESC;  
--多表连接查询  
SELECT products.name, product_types.name  
FROM products, product_types  
WHERE products.product_type_id = product_types.product_type_id  
AND products.product_id = 3;  
SELECT products.name, product_types.name  
FROM products, product_types  
WHERE products.product_type_id = product_types.product_type_id  
ORDER BY products.name;  
--使用表别名  
SELECT P.NAME, PT.NAME  
FROM products P, product_types PT  
WHERE p.product_type_id = pt.product_type_id  
ORDER BY p.name;  
--笛卡尔集  
SELECT PT.product_type_id, P.product_id  
FROM product_types PT, products P;  

--多表连接查询  
SELECT C.FIRST_NAME, C.LAST_NAME, P.NAME AS PRODUCT, PT.NAME AS TYPE  
FROM customers C, purchases PR, products P, product_types PT  
WHERE c.customer_id = pr.customer_id  
AND p.product_id = pr.product_id  
AND p.product_type_id = pt.product_type_id  
ORDER BY p.name;  
--不等连接  
SELECT E.FIRST_NAME, E.LAST_NAME, E.TITLE, E.SALARY, SG.SALARY_GRADE_ID  
FROM employees E, salary_grades SG  
WHERE e.salary BETWEEN sg.low_salary AND sg.high_salary  
ORDER BY sg.salary_grade_id;  
--外连接('(+)'位于与含空值列相反的一边)  
SELECT P.NAME PRODUCT, PT.NAME TYPE  
FROM products P, product_types PT  
WHERE p.product_type_id = pt.product_type_id (+)  
ORDER BY p.name;  
--左外连接和右外连接  
--左外连接  
SELECT P.NAME PRODUCT, PT.NAME TYPE  
FROM products P, product_types PT  
WHERE p.product_type_id = pt.product_type_id (+)  
ORDER BY P.NAME;  
--右外连接  
SELECT P.NAME PRODUCT, PT.NAME TYPE  
FROM products P, product_types PT  
WHERE p.product_type_id (+) = pt.product_type_id  
ORDER BY P.NAME;  
--外连接的限制 1、只能在连接的一端使用外连接操作符，不能在两端都使用外连接操作符  
--           2、不能同时会用一个外连接条件和另外一个使用OR操作符的连接条件  

--自连接（同一个表进行连接， 必须会用不同的表别名来实现连接）  
SELECT W.FIRST_NAME||' '||W.LAST_NAME||' WORKS FOR '  
||M.FIRST_NAME||' '||M.LAST_NAME  
FROM employees W, employees M  
WHERE W.manager_id = M.employee_id  
ORDER BY W.first_name;  
SELECT W.FIRST_NAME||' '||W.LAST_NAME||' WORKS FOR '  
||M.FIRST_NAME||' '||NVL(M.LAST_NAME, 'shareholders')  
FROM employees W, employees M  
WHERE W.manager_id = M.employee_id (+)  
ORDER BY W.first_name;  
--内连接  
SELECT P.NAME, PT.NAME  
FROM products P INNER JOIN product_types PT  
ON p.product_type_id = pt.product_type_id（+）  
ORDER BY p.name;  
SELECT E.FIRST_NAME, E.LAST_NAME, E.SALARY, SG.SALARY_GRADE_ID  
FROM employees E INNER JOIN salary_grades SG  
ON e.salary BETWEEN sg.low_salary AND sg.high_salary  
ORDER BY sg.salary_grade_id;  
 
--使用USING关键字(使用USING关键字的时候最好不要使用表别名，否则会出错)  
SELECT P.NAME, PT.NAME  
FROM products P INNER JOIN product_types PT  
USING(product_type_id)  
ORDER BY p.name;  
--多表内连接  
SELECT C.LAST_NAME, P.NAME AS PRODUCT, PT.NAME AS TYPE  
FROM customers C INNER JOIN purchases PR  
USING (customer_id)  
INNER JOIN products P  
USING (product_id)  
INNER JOIN product_types PT  
USING (product_type_id)  
ORDER BY p.name;  
--多列内连接  
--SELECT * FROM  
--TABLE1 INNER JOIN TABLE2  
--USING(COLUMN1,COLUMN2,…);  
--使用USING关键字的外连接  
--左外连接  
SELECT P.NAME, PT.NAME   
FROM products P LEFT OUTER JOIN product_types PT  
USING(product_type_id);  
--右外连接  
SELECT P.NAME, PT.NAME   
FROM products P RIGHT OUTER JOIN product_types PT  
USING(product_type_id);  
--全外连接  
SELECT P.NAME, PT.NAME   
FROM products P FULL OUTER JOIN product_types PT  
USING(product_type_id);