当前位置：首页 -> AI编程基础 -> 数据库编程

Oracle基本数据查询(一)

2014-11-24 13:06:36 · 作者: · 浏览: 2
标签: 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 (+)  
ORD