【Oracle】day03_查询语句_聚合函数_关联查询

【Oracle】day03_查询语句_聚合函数_关联查询

1.查询语句

1）别名

当SELECT语句中要查询的内容不是单纯的字段，而是一个函数或者表达式时，查询出来的结果集中对应的该字段的字段名就是这个函数或者表达式。这样不利于读取，为此我们会为这样的列加一个别名，使得查询出来的该字段的名字为这个别名增加可读性，甚至在子查询中对这样的情况还要求必须写别名

别名可以在字段之后使用空格分隔开,然后定义或者在字段名之后使用“AS”然后在后面定义,但通常不用写AS
别名不区分大小写,且不能使用空格,若需要在别名中区分大小写或者加空格,那么别名需要被双引号括起来.
SELECT ename,sal*12 "sal"
FROM emp;

where子句,与DML中使用一致，WHERE是用来限定条件，在SELECT语句中使用，可以只将满足WHERE要求的记录查询出来
SELECT ename,deptno
FROM emp
WHERE deptno='10';

SELECT * FROM emp
WHERE job='SALESMAN';

2）>,<,>=,<=,!=,<>,= (!= 等价与 <>)
SELECT ename,sal FROM emp
WHERE sal>=3000;

SELECT ename,sal FROM emp
WHERE sal<>3000;

SELECT ename,sal,hiredate FROM emp
WHERE hiredate>TO_DATE('1982-1-1','YYYY-MM-dd');

SELECT ename FROM emp WHERE sal>2000;

3）AND,OR (AND 优先级>OR)
SELECT * FROM emp
WHERE sal>1000 AND job='CLERK';
SELECT * FROM emp
WHERE sal>1000 OR job='CLERK';
SELECT * FROM emp
WHERE sal>1000 AND (job='SALESMAN' OR job='CLERK');

4）LIKE (模糊查询字符串)
% -- 0到多个字符
_ -- 单个字符
SELECT * FROM emp
WHERE ename LIKE '_A%';
SELECT * FROM emp
WHERE sal LIKE '____';

5）IN(list)
判断指定内容的值是否等于列表中任意一项
NOT IN(list)
判断指定内容的值不等于列表中任意一项
--list可以是一个查询结果
SELECT ename,sal,job FROM emp
WHERE job NOT IN(SELECT job FROM emp WHERE sal>2000);
SELECT ename,sal,job FROM emp
WHERE job IN('CLERK','MANAGER');

6）BETWEEN ... AND...
判断指定内容的值在一个区间范围内(小的在前大的在后)
SELECT ename,sal FROM emp
WHERE sal BETWEEN 1500 AND 3000;

7）ANY与ALL
判断指定内容>,>=,<,<=一个列表内容时使用
>ANY:大于列表中其中一项,所以大于最小的即可.
>ALL:大于列表中所有项,所以必须大于最大的.
<ANY:小于最大的.
<ALL:小于最小的.
SELECT * FROM emp
WHERE sal>ANY(1000,2000,3000);
ANY与ALL列表中通常不会写死几个数据,否则没有意义,通常列表中是一个查询的结果,所以
它们常用在判断子查询结果中使用.

WHERE子句中可以使用函数或者表达式的结果作为过滤条件.
SELECT ename,sal,job FROM emp
WHERE UPPER(ename)=UPPER('king');
SELECT ename,sal,job FROM emp
WHERE sal*12>50000;

8）DISTINCT 去除重复
DISTINCT关键字必须紧跟在SELECT关键字之后,DISTINCT后面可以有多个字段,若是多个
字段,那么则是这些字段值的组合没有重复的.
SELECT DISTINCT deptno,job FROM emp;

9）ORDER BY 子句
用来排序查询的结果集,ORDER BY 子句必须写在SELECT语句的最后面.
ORDER BY 可以根据后面指定的字段的值进行升序或者降序排列.
DESC--降序
ASC--升序(通常不写,默认的)
SELECT * FROM emp ORDER BY deptno,sal DESC;

2.聚合函数

又叫做多行函数，分组函数
作用:将若干行记录进行统计，然后产生一个结果。

1）MAX(),MIN()
统计指定内容中所有参与统计记录中的最大值与最小值

查看公司中最高工资与最低工资?
SELECT MAX(sal) max_sal,
       MIN(sal) min_sal
FROM emp

2）AVG(),SUM()
统计平均值与总和
查看公司的平均工资，与工资总和
SELECT AVG(sal),SUM(sal)
FROM emp

聚合函数忽略NULL值。
若希望NULL值的记录也参与统计，可以使用NVL函数先将这些记录转换为非NULL值。
SELECT AVG(NVL(comm,0))
FROM emp

3）COUNT()
该函数统计的是非空的记录有多少条，而并不关心
具体每条记录的值是多少。

查看公司总共多少人?(整张表多少条记录?)
SELECT COUNT(*)
FROM emp

查看每个部门的最高工资，最低工资，平均工资以及工资总和?

4）GROUP BY 子句
GROUP BY 关键字后面可以跟若干字段，作用是根据给定的字段值相同的记录看做一组，若是多个字段，则是这些字段值的组合相同的记录看做一组。

SELECT语句中若使用了组函数，那么不在组函数中的其他字段必须出现在 GROUP BY 子句中!
SELECT
  MAX(sal),MIN(sal),
  AVG(sal),SUM(sal),deptno
FROM
  emp
GROUP BY
  deptno

查看公司每个职位都有多少人?
SELECT COUNT(*),job
FROM emp
GROUP BY job

查看平均工资高于2000的部门的最高工资和最低工资?
下面的做法是不可以的，WHERE子句中不允许使用分组函数
SELECT
 MAX(sal),MIN(sal)
FROM
 emp
WHERE
 AVG(sal)>2000
GROUP BY
 deptno
原因:过滤时机不同
我们若想判断平均工资大于2000，这说明我们已经将表中的数据查询出来，并先进行统计，在统计的结果基础上再进行判断。而WHERE的过滤时机是在查询表的过程中进行的，表中的数据查询出那条记录是由WHERE的判断结果为准，所以这里WHERE已经在实际需要判断的地方之前就进行完毕了。

5）HAVING 子句，该子句必须跟在GROUP BY子句之后不能单独定义。作用是在分组后，做统计，并用统计结果进行过滤。

SELECT MAX(sal),MIN(sal),deptno
FROM emp
GROUP BY deptno
HAVING AVG(sal)>2000

SELECT ename,deptno
FROM emp

SELECT deptno,dname
FROM dept

3.关联查询

联合多张表进行查询。
需要注意的是，N张表关联查询至少要有N-1个连接条件。连接条件的目的在于让数据库知道表中记录与另一张表中的记录是如何对应上的。  

查看SALES部门的员工名字?
SELECT emp.ename
FROM emp,dept
WHERE emp.deptno=dept.deptno
AND dept.dname='SALES'

查看每个员工的名字以及所在部门的名称以及所在地?
SELECT e.ename,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno

不添加连接条件，或者连接条件无效，都会产生笛卡尔积。笛卡尔积会产生大量无用的关联。对于系统资源产生严重破坏，严重时可导致系统瘫痪

查看每个员工的名字以及所在部门的名称以及所在地?
1）使用内连接的方式:
SELECT e.ename,d.dname,d.loc
FROM emp e JOIN dept d
ON e.deptno=d.deptno

这样的写法好处在于连接条件定义在ON子句中，WHERE里只定义过滤条件即可，调理更清晰。

查看每个员工名字以及部门号，部门名称，没有部门也要将员工信息列出来
2）外连接:除了将满足连接条件的数据查询出来以外还可以将不满足连接条件的数据也都查询出来。

外连接分为:左外连接，右外连接，全外连接

左外连接:以左边的表为主，左边表中的所有记录都查询出来，当有不满足连接条件的记录时，来自右边表中的字段全部为NULL。

右外连接:右表为主，左边补NULL

全外连接:哪边不满足连接条件，哪边补NULL

SELECT e.ename,e.deptno,d.dname
FROM emp e LEFT|RIGHT|FULL OUTER JOIN dept d
ON e.deptno=d.deptno

3）自连接:
自己表中的一条数据对应自己表中的多条数据。
自连接为了解决数据一样，但是之间又存在父子关系的情况。而且层级关系中有多少层不确定的情况。
自连接简单说就是解决了树状结构的数据关系。
例如：员工表，都是员工，但是员工间有存在上
     下级关系
     商品类别表，都是类别，但是有存在上下级
     关系
查看员工，以及其上司的名字?
SELECT e.ename,m.ename
FROM emp e JOIN emp m
ON e.mgr=m.empno