oracle数据查询

一、SELECT语句介绍

SELECT [ALL|DISTINCT] colum_name[,expression...]FROM table1_name[,table2_name,view_name,...][WHERE condition] [GROUP BY column_name1[,colum_name2,...] [HAVING group_condition]] [ORDER BY column_name2 [ASC|DESC[,column_name2,...]];

二、简单查询

---

1. 无条件查询
2. 有条件查询
3. 查询排序
4. 查询统计

---

1.无条件查询

• 查询所有列：SELECT * FROM employees;
• 查询指定列：SELECT department_id , departmen_name FROM departments;
• 使用算数表达式：SELECT employee_id,salary*0.8 FROM employees;
• 使用字符常量：SELECT employee_id, ‘salary is:’,salary FROM employees;
• 使用函数：SELECT * employee_id,UPPER(first_name) FROM employees;
• 改变列标题：SELECT employee_id empno,salary sal FROM employees;
• 使用连接字符串：SELECT ‘员工名：’||first_name||last_name FROM employees;
• 消除重复行：SELECT DISTINCT department_id FROM employees;

2.有条件查询

  WHERE 子句中常用的运算符：

• 关系运算：>,<,=,>=,<=,<>,!=
• 确定范围：BETWEEN AND,NOT BETWEEN AND
• 确定集合：IN,NOT IN
• 逻辑操作：NOT,AND,OR
• 字符匹配：LIKE,NOT LIKE
• 空值判断：IS NULL, IS NOT NULL
• 注意：日期
  例子：
  1.WHERE hire_date>=’01-9月-1999‘；
  2.ALTER SESSION SET NLS_DATE__FORMAT=’YYY-MM-DD HH24:MI:SS’;
  SELECT hire_date FROM employees WHERE hire_date>=’1999-9-1’;

3.查询排序
ORDER BY 属性名 ASC|DESC
4.查询统计
常用的聚集函数

• COUNT([DISTINCT|ALL] column)：返回结果集中的记录个数
• COUNT([DISTINCT|ALL] column)：返回结果集中非空记录个数
• AVG([DISTINCT|ALL] column):平均值
• MAX([DISTINCT|ALL] column):最大值
• MIN([DISTINCT|ALL] column): 最小值
• SUM([DISTINCT|ALL] column):总和
• VARIANCE(column):方差
• STDDEV(column):标准差

三、分组查询

1.单列分组查询：SELECT department_id,count(*),avg(salary) FROM employees GRUOP BY department_id ORDER BY department_id;
2.多列分组查询：查询部门中不同职位的员工人数和平均工资
SELECT department_id,job_id,count(*),avg(salary) FROM employees GRUOP BY department_id,job_id;
3.HAVING子句限制返回组：

查询部门平均工资高于8000元的部门号、部门人数和部门平均工资。SELECT department_id,count(*),avg(salary) FROM employees GRUOP BY department_id HAVING avg(salary)>8000;

统计10号部门中各个职位的员工人数和平均工资，并返回平均工资高于1000元的职位的人数和平均工资。SELECT job_id,count(*),avg(salary) FROM employeesWHERE department_id=10GRUOP BY job_id HAVING avg(salary)>1000;

四、多表查询

---

• 交叉查询: 笛卡儿积（无条件）

• 内连接

1.等值连接查询10号部门员工号，工资，部门号和部门名。SELECT employee_id,salary,e.department_id,department_nameFROM employee e JOIN departments dON e.department_id=d.department_id AND e.department_id=10;或者：SELECT  employee_id,salary,e.department_id,department_nameFROM employee e,departments dWHERE e.department_id=d.department_id AND e.department_id=10;

2.不等值连接  WHERE salary>=min_salary AND salary<=max_salary;

3.自身连接查询所有员工的员工号，员工名和该员工领导的员工名，员工号。SELECT w.employee_id,w.name,m.employee_id,m.nameFROM employees w,employees mWHERE w.manager_id=m.employee_id;

• 外连接

1.左外连接查询100号部门的部门名，员工号，员工名和所有其他部门的名称。SELECT department_name,employee_id,first_name,last_nameFORM departments d LEFT JOIN employees eON d.department_id=e.department_id AND d.department_id=100;

2.右外连接查询20号部门的部门名称及其员工号，员工名和所有其他部门的员工名，员工号。SELECT employee_id,first_name,last_nameFROM departments d RIGHT JION employees eON d.deparment_id=e.department_id AND d.department_id=20;

---

五、子查询

---

1. 无关子查询
   1).单行单列子查询：单行单列子查询（=，>,<,>=，<=,!=)
   2).多行单列子查询：多行比较运算(IN,NOT,>ANY,=ANY,>ALL,..)

 3).单行多列子查询：查询与159号员工的工资，职位都相同的员工信息。SELECT employee_id,name,salary,job_idFROM employees WHERE (salary,job_id)=(SELECT salary,job_id FROM employees WHERE employee_id=159）；

 4.多行多列子查询：WHERE (salary,job_id) IN (SELECT salary,job_id FROM ...)

3. 相关子查询(如果子查询子执行时需要引用外部父查询的信息，那么这种查询就称为相关子查询）

1.查询没有任何员工的部门信息：SELECT * FROM departments d WHERE NOT EXISTS(SELECT * FROM employees e WHERE e.department_id=d.department_id);2.查询比本部门平均工资高的员工信息。 SELECT employee_id,name,salary FROM employees e WHERE salary>(SELECT avg(salary) FROM employees WHERE department_id=e.department_id);

1. DDL语句中的子查询：
   CREATE TABLE}VIEW tablename|viewname
   AS
   SELECT 属性名 FROM tablename
   WHERE …;

---

六、合并操作

---

1. 并集运算(UNION)(UNION ALL)
2. 交集运算(INTERSECT)
3. 差集运算(MINUS)

---

七、SQL内置函数

---

1. 数值函数:
   *ABS(n):绝对值
   *CELL(n):大于或等于n的最小整数
   *FLOOR(n):小于或等于n的最大对数
   *MOD(M,N):M除以N的余数
   *ROUND(M[,N]):四舍五入
2. 字符函数
   *LOWER(char):转为小写
   *UPPER(char):转为大写
   *CONCAT(char1,char2):连接
   *SUBSTR(char,m[,n])：截取，m为起始位置，n为长度。
3. 日期函数
   *SYSDATE:系统时间
   *ADD_MONTHS(d,n)
   *MONTHS_BETWEEN(d1,d2):相差的月数
   *NEXT_DAY
   *LAST_DAY
4. 转换函数:TO DATE(‘09-3-28’,’YY-MM-DD’)
5. 其他函数:NVL(expr1,expr2)

---