一、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.无条件查询
- 查询所有列: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 employees
WHERE department_id=10
GRUOP BY job_id HAVING avg(salary)>1000;
四、多表查询
交叉查询: 笛卡儿积(无条件)
内连接
1.等值连接
查询10号部门员工号,工资,部门号和部门名。
SELECT employee_id,salary,e.department_id,department_name
FROM employee e JOIN departments d
ON e.department_id=d.department_id AND e.department_id=10;
或者:
SELECT employee_id,salary,e.department_id,department_name
FROM employee e,departments d
WHERE 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.name
FROM employees w,employees m
WHERE w.manager_id=m.employee_id;
- 外连接
1.左外连接
查询100号部门的部门名,员工号,员工名和所有其他部门的名称。
SELECT department_name,employee_id,first_name,last_name
FORM departments d LEFT JOIN employees e
ON d.department_id=e.department_id AND d.department_id=100;
2.右外连接
查询20号部门的部门名称及其员工号,员工名和所有其他部门的员工名,员工号。
SELECT employee_id,first_name,last_name
FROM departments d RIGHT JION employees e
ON d.deparment_id=e.department_id AND d.department_id=20;
五、子查询
- 无关子查询
1).单行单列子查询:单行单列子查询(=,>,<,>=,<=,!=)
2).多行单列子查询:多行比较运算(IN,NOT,>ANY,=ANY,>ALL,..)
3).单行多列子查询:
查询与159号员工的工资,职位都相同的员工信息。
SELECT employee_id,name,salary,job_id
FROM 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);
- DDL语句中的子查询:
CREATE TABLE}VIEW tablename|viewname
AS
SELECT 属性名 FROM tablename
WHERE …;
六、合并操作
- 并集运算(UNION)(UNION ALL)
- 交集运算(INTERSECT)
- 差集运算(MINUS)
七、SQL内置函数
- 数值函数:
*ABS(n):绝对值
*CELL(n):大于或等于n的最小整数
*FLOOR(n):小于或等于n的最大对数
*MOD(M,N):M除以N的余数
*ROUND(M[,N]):四舍五入 - 字符函数
*LOWER(char):转为小写
*UPPER(char):转为大写
*CONCAT(char1,char2):连接
*SUBSTR(char,m[,n]):截取,m为起始位置,n为长度。 - 日期函数
*SYSDATE:系统时间
*ADD_MONTHS(d,n)
*MONTHS_BETWEEN(d1,d2):相差的月数
*NEXT_DAY
*LAST_DAY - 转换函数:TO DATE(‘09-3-28’,’YY-MM-DD’)
- 其他函数:NVL(expr1,expr2)