数据库中常用的复杂查询，希望对你有所帮助

在数据库操作中，sql语句结束时一定要带上 ; 号 否则不会执行。

1.一般特性

1.查看当前用户——show user;
2.查看当前用户有哪些表——select table_name from user_tables;
3.查看emp表的结构——desc emp;
4.列出emp表的所有列——select * from emp;
5.查看指定列——select ename from emp;

特性：
1.数字左对齐，字母右对齐
2.支持运算符——select sal+100 from emp;
3.运算符具有优先级；
4.列别名，使用单引号可以保持数据原样
5.连接运算符|| ——
select ename||sal from emp;
6.去除重复的行——DISTANCE关键字——select DISTANCE job from emp;

2.简单查询

基本语法规则：
SELECT 需要查找的字段 FROM 表名 WHERE 条件 ;
例如：
查找每个雇员的编号——select empno from emp;
查找每个雇员的职位——select job from emp;

注意：
查询出来的job内容有重复数据，使用distance删除

简单查询可以使用四则运算

查询每个雇员的姓名，职位和基本年薪：
select ename,job,sal12 from emp;
有时候列的名称不方便浏览，可以用列别名来表示：
select ename,job,sal12 as yearsal from emp;

限定查找

SELECT 查找字段[字段别名] FROM 表名 [表别名] WHERE 条件；
其中条件包含有：
’ > , < , >= , < = , ! = , <> , BETWEEN…AND… , LIKE , IN , IS NULL , AND , OR , NOT’
查询出所有职位是办事员的雇员信息：
select * from emp where ename=‘CLERK’;

注意：
在Oracle数据库中，数据区分大小写

查询职位是办事员，或者是销售员，且他们的工资大于2000：
select * from emp where (job=‘CLERK’ or job=‘SALEMAN’) and sal>2000;
查询基本工资在1500-3000之间的雇员的姓名：
select ename from emp where sal between 1500 and 3000;

在这里介绍一下模糊查找 LIKE:

LIKE子句：
_ ：匹配单个字符
%：匹配任意字符
例如：
1.查询雇员姓名中是以字母A开头的信息：
select * from emp where ename LIKE ‘A%’;
2.查询雇员姓名的第二个字符是A的信息：
select * from emp where ename LIKE ‘_A%’;
3.查询雇员姓名中包含A的信息：
select * from emp where ename LIKE ‘%A%’;

这里介绍一下数据排序

数据排序其实很简单，无非就是 ：
SELECT 字段 FROM 表名 WHERE 条件 ORDER BY 需要排序的字段 DESC(ASC)；
这里DESC 表示降序，ASC表示升序；

例如:
查找所有雇员信息，按照工资降序排列，工资相同按照雇佣日期从早到晚排列：
select * from emp order by sl desc,hiredate asc;

字符函数：主要对字符串数据进行操作

UPPER(字符串|列） 将输入的字符串变为大写
LOWER(字符串|列）将输入的字符串变为小写
INITCAP(字符串|列） 将字符串首字母大写
LENGTH(字符串|列） 将字符串长度返回
REPLACE(字符串|列） 替换字符串
SUBSTR(字符串|列） 截取字符串
oracle提供了一个虚拟表 dual；

1.将所有雇员的姓名以小写返回，并计算每个姓名的长度：
select lower(ename),length(ename) from emp;
2.使用字符‘-’替换雇员姓名的所有字母A：
select replace(ename,‘A’,’-’) from emp;

字符串截取有两种语法：

1.SUBSTR(字符串，开始点），表示从开始点一直截取到结尾；
2.SUBSTR(字符串，开始点，截取多少位），表示从开始点截取多少位；
例如：要求截取雇员姓名的后三个字母：
select substr(ename,length(ename)-2) from emp;

数字函数

ROUND(数字，保留小数的范围)——四舍五入的操作
TRUNC(数字，保留小数的范围）——舍弃指定位置的内容
MOD(数字1，数字2）——取模，取余数；

日期函数

取得系统当前的年月日可以用SYSDATE；
日期除了上述的三个公式外，还有以下几个操作：
1.LAST_DAY(日期)：求出指定日期当月的最后一天
求出本月的最后一天——select LAST_DAY(sysdate) from dual;
2.NEXT_DAY(日期)：求出下一个指定星期几的日期
下一个周日是几号——select NEXT_DAY(sysdate,‘sun’) from dual;
3.ADD_MONTHS(日期，数字)：求出若干月之后的日期
四个月之后是几号——select ADD_MONTHS(sysdate,4) from dual;
4.MONTHS_BETWEEN(日期1，日期2)：求出两个日期之间所经历的月份
求出每个雇员到今天为止的雇佣月份——select MONTHS_BETWEEN(sysdate,hiredate) from emp;

转换函数TO_CHAR

转换函数主要是完成几种数据之间的转换：
TO_CHAR(字符串，格式字符串）：将日期或数字转换为字符产形式
TO_DATE(字符串，格式字符串）：将字符串转换为DATE数据显示
TO_NUMBER(字符串）：将字符串转换为数字形式

TO_CHAR()函数的格式：
年(yyyy):月(mm):日(dd);
例如：将时间按照年-月-日格式显示：
select TO_CHAR(sysdate,‘yyyy-mm-dd’) stime from dual;
使用24小时制：
select TO_CHAR(sysdate,‘yyyy-mm-dd hh24:mi:ss’) time from dual;

通用函数

主要有NVL(),DECODE()两个
NVL():处理数据位是空的情况
DECODE():多数值判断，类似于if-else，但仅仅只判断数字；

3.多表查询

基本语法：
SELECT 字段1[别名1]，字段2[别名2] FROM 表名1[别名1]，表名2[别名2] WHERE 条件；
在多表查询之前，首先必须查找各个表中的数据量，用COUNT()函数；
确认数据量之后，执行多表查询语句，但是由于数据库的机制问题，会产生笛卡尔积，所以如何去掉笛卡儿积？

去除笛卡儿积

采用关键字段关联的形式，emp表和dept表之间存在了deptno的关联字段，从关联字段下手：
select * from emp,dept where emp.deptno=dept.deptno;
如果表名比较长，可以使用表别名：
select * from emp e,dept d where e.deptno=d.deptno;

左右连接

左右连接可以改变判断条件的参考方向，例如：
select * from emp e,dept d where e.deptno=d.deptno;

注意：
一共有四个部门，但这里只显示了三个，缺少40部门的信息，所以必须改变参考方向，这时就需要左右连接
select * from emp e,dept d where e.deptno(+)=d.deptno;

用于左右连接的更改有两种使用情况：
(+)= : 表示右连接
=(+) : 表示左连接

自然连接：
不用区分关联条件，oracle自己区分；例如：
查询公子大于2000的员工编号，员工姓名和部门名称：
select e.empno,e.ename,d.dname from emp e natural join dept d where e.sal>2000;

统计函数

COUNT()：查询表中的数据记录
AVG()：求平均值
SUM()：求和
MAX()：求最大值
MIN()：求最小值
统计出公司所有雇员每个月支付的平均工资和总工资；
select count(empno),avg(sal),sum(sal) from emp ;
注意：
COUNT()函数的主要功能是进行数据的统计，如果该表中没有统计的数据，那么COUNT()也会返回数据0/null；

分组统计

当数据库中某一列的信息存在重复，可以按照性质对其进行分组统计；
GROUP BY()子句：
SELECT 字段 FROM 表名 WHERE 条件 GROUP BY 分组字段 ；
按照部门编号分组，求出每个部门的人数和平均工资：
select d.depno,count(e.empno),avg(e.sal)
from emp e,dept d
where e.deptno=d.deptno
group by d.deptno;

注意：
1.分组函数可以在没有分组的时候单独使用，可是不能出现其他查询字段

2.如果要进行分组，则select子句之后，只能出现分组的字段和统计函数

3.分组函数允许嵌套，但是嵌套之后的查询不能右其他字段

多字段分组

要求显示每个部门的编号，名称，位置，部门的人数和平均工资：
select d.deptno,d.dname,d.loc,count(e.empno),trunc(nvl(avg(e.sal),0)) from emp e,dept d
where e.deptno=(+)d.deptno
group by d.deptno,d.dname,d.loc;

如果要对分组后的数据再次进行筛选，可以使用HAVING子句。
SELECT 字段名，统计函数 FROM 表名 WHERE 条件 GROUP BY 字段 HAVING 筛选条件；
注意：HAVING在GROUP BY 分组之后再次过滤，可以使用统计函数；

子查询（重点）

子查询=简单查寻+限定查询+多表查询+统计查询的综合体；
SELECT 字段，统计函数
FROM 表名,(SELECT 字段，统计函数 FROM 表名 WHERE 条件 GROUP BY 分组)别名
WHERE 条件
GROUP BY 分组名
HAVING 再次过滤

WHERE：子查询一般返回单行多列，多行多列，单行单列的数据
FROM：子查询返回的一般是多行多列的数据，当成一个临时表

1.查询工资比SMITH还高的全部信息
select * from emp
where sal>(
select sal from emp where ename=‘SMITH’);

如果子查询返回的是单行多列的数据，这时需要用到三种判断符：

1.IN操作符：用于指定一个子查询的范围
2.ANY操作符，与每个内容相匹配，有三种形式：
1）=ANY：功能和IN一样，在范围内查询匹配
2）>ANY：比子查询中返回记录最小的还要大
3）<ANY：比子查询中返回记录最大的还要小
3.ALL操作符：与每个内容相匹配，有两种形式：
1）>ALL：比子查询中最大的还要大
2）<ALL：比子查询中最小的还要小
例如：
select * from emp
where sal<all(
select sal from emp where job=‘SALEMEN’);

以上所有的都是where中的单行单列/单行多列，那么下面就是FROM中多行多列：
1.查询出每个部门的编号，名称，位置和部门人数，平均工资：
select d.deptno,d.dname,d.loc,nvl(res.count,0),nvl(res.avg,0) from dept d,(
select deptno dno,count(empno)count,avg(sal)avg from emp group by deptno)res
where d.deptno=res.dno(+);
2.列出所有工作的最低工资及从事此工资的雇员姓名：
select e.ename,e.job,e.sal from emp e,(
select job,min(sal)min from emp group by job)temp
where e.sal=temp.sal and e.job=temp.job;
3.列出各部门的MANAGER的最低薪资、姓名、部门名称、部门人数：
select e.ename,e.sal,d.dname,res.count from emp e,dept d,(
select deptno dep,min(sal)min from emp where job=‘MANAGER’ group by deptno)temp,(
select deptno dno,count(empno)count from emp group by deptno)res
where d.deptno=e.deptno
and e.sal=temp.min
and e.deptno=temp.dep
and e.deptno=res.dno;