Oracle基本数据查询
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);
