数据库的语句（oracle）二

字段的別名
当我们在SELECT语句中查询内容时，若某个字段是一个函数或者表达式的时候，那么在查询结果集中
对应的该字段的名字就是这个函数或者表达式，可读性差。这时候我们可以为该函数或者表达式添加
别名，这样结果集中会使用该别名作为该字段的名字。


SELECT 
  empno id,ename name,sal*12 "Annual sal"
  FROM emp_lb
  
SELECT 
  empno id,ename name,sal*12 sal
  FROM emp_lb
  
WHERE子句
WHERE可以用在DML语句中，也可以用在SELECT语句中。
作用是添加过滤条件，只选满足条件的记录。
在SELECT中使用时，当数据库查询表中数据时，在每查询一天记录时，会先查看该记录是否满足条件，
并将满足条件的记录查询出来
SELECT * FROM emp_lb
WHERE deptno=20


查看只为是“SALESMAN"的员工信息
SELECT * FROM emp_lb
WHERE job='SALESMAN'




查询职员表中不属于部门10的员工信息
SELECT ename,sal,job FROM emp_lb
WHERE deptno<>10;


查询职员表中在1982年1月1日以后入职的职员信息，比较日期类型数据
SELECT ename,sal,hiredate FROM emp_lb
WHERE hiredate>to_date('1982-1-1','YYYY-MM-DD');




查询薪水大于1000并且职位是'CLERK'的职员信息
SELECT ename,sal,job FROM emp_lb
WHERE sal>1000 AND job='CLERK';


查询薪水大于1000或者职位是'CLERK'的职员信息
SELECT ename,sal,job FROM emp_lb
WHERE sal>1000 OR job='CLERK';


使用AND和OR可以连接多个条件，AND的优先级是高于OR的
SELECT ename,sal,job FROM emp_lb
WHERE sal>1000 AND (job='CLERK' OR job='SALESMAN');


LIKE用于对字符串进行模糊匹配，其中支持两个通配符：
"_":表示单一的一个字符
"%":表示任意个字符（0-多次）
查看名字第3个字符是A的员工的信息
SELECT ename,job FROM emp_lb
WHERE ename LIKE '__A%';


IN(list)
判断给定内容等于列表中的其中之一


NOT IN(list)
判断给定内容不能是列表中的所有内容


IN与NOT IN常用语子句查询中使用


SELECT ename,job FROM emp_lb
WHERE job IN('MANAGER','CLERK');
等价
SELECT ename,job FROM emp_lb
WHERE job='MANAGER' OR job='CLERK';


查询薪水在1500-3000之间的职员信息
SELECT ename,sal FROM emp_lb
WHERE sal BETWEEN 1500 AND 3000;




ANY与ALL与>,>=,<,<=联合使用。
用于判断内容与列表中的值之间的比较。
ANY和ALL用于在子查询中作判断使用。
>ANY:大于列表中最小的即可
<ANY:小于列表中最大的即可
>ALL:大于列表中最大的即可
<ALL:小于列表中最小的即可


SELECT empno,ename,job,sal,deptno
FROM emp_lb
WHERE sal>ANY(3500,4000,4500);


WHERE中也可以使用函数或者表达式的结果作为过滤条件
SELECT ename,sal,job
FROM emp_lb
WHERE ename=UPPER('king');


SELECT ename,sal,job
FROM emp_lb
WHERE sal * 12>50000




去除重复行
DISTINCT关键字，可以去除指定字段值相同的记录行。
DISTINCT必须紧跟在SELECT关键字之后


查看公司总共有多少职位
SELECT DISTINCT job FROM emp_lb


DISTINCT关键字可以对多个字段进行去重复工作，那么是对这几个字段值的组合去重。
SELECT DISTINCT job,deptno FROM emp_lb
上面的查询结果中不会出现job，deptno的值组合相同的记录


        排序
ORDER BY子句
用于排序查询出来的结果集，可以对指定的字段按照升序或者降序排列。
ORDER BY子句必须写在SELECT语句的最后面
ORDER BY 排序的字段默认是按照升序进行，若在字段后面添加DESC则为降序


查看工资排名情况：
SELECT ename,sal
FROM emp_lb
ORDER BY sal


SELECT ename,sal
FROM emp_lb
ORDER BY sal DESC


ORDER BY也可以根据多个字段对结果集进行排序。多字段排序时存在优先级问题，
先按照第一个字段的方式排序，当第一个字段的值有相同的那些记录中，再按照
第二个字段的方式排序
每个字段可以单独指定升降序


SELECT ename,deptno,sal
FROM emp_lb
ORDER BY deptno DESC,sal DESC




聚合函数，又称为多行函数，分组函数。
聚合函数可以将多条记录中的指定字段的值进行统计，然后得出一个结果。
MAX()与 MIN()
求最大值与最小值
SELECT MAX(sal),MIN(sal)
FROM emp_lb


AVG()求平均值
查看公司平均工资
SELECT AVG(sal)
FROM emp_lb


聚合函数是忽略NULL值的，对于平均值效果最明显，可以使用NVL函数将NULL替换来解决
查看平均的奖金
SELECT AVG(NVL(comm,0))
FROM emp_lb




SUM()求总和
查看公司的工资总和
SELECT SUM(sal)
FROM emp_lb




COUNT()
统计指定字段不为NULL的记录条数
有奖金的总共多少人？
SELECT COUNT(comm)
FROM emp_lb




查看公司总共多少人?
SELECT COUNT(*)
FROM emp_lb




GROUP BY子句
GROUP BY子句是为了配合聚合函数使用的，使用GROUP BY子句后，分组可以更细致，
就可以不在将整张表所有记录看做一组再进行统计了，可以将表中指定字段的值相同
的记录看做一组再进行每组统计


查看每个部门工资的最高值与最低值？
SELECT MAX(sal),MIN(sal),deptno
FROM emp_lb
GROUP BY deptno


当SELECT子句中出现了分组函数，那么凡不在聚合函数的单独字段必须出现在GROUP BY子句中。
但是返回来没有强制要求。
SELECT MAX(sal),MIN(sal)
FROM emp_lb
WHERE deptno=10


GROUP BY子句后面也可以写多个字段，当按照多个字段分组时，是按照记录中这些字段值的组合
相同的看做一组


查看每个部门每种职位总共多少人?
SELECT COUNT(*),deptno,job
FROM emp_lb
GROUP BY deptno,job


查看平均工资高于2000的那些部门的平均工资具体多少?
SELECT AVG(sal),deptno
FROM emp_lb
GROUP BY deptno
HAVING AVG(sal)>2000


查看平均工资高于2000的部门的最高工资与最低工资
SELECT MAX(sal),MIN(sal),deptno
FROM emp_lb
GROUP BY deptno
HAVING AVG(sal)>2000


查看最高工资大于2950的那些部门的平均工资？
SELECT AVG(sal),deptno
FROM emp_lb
GROUP BY deptno
HAVING MAX(sal)>2950


HAVING与WHERE
他们都是用于在查询中添加过滤条件的，但是他们的过滤时机不同，先WHERE后HAVING.
WHERE是在第一次从表中查询数据的时候进行过滤的，而HAVING是在查询出的数据中进行统计，
然后使用统计结果再次进行过滤。所以WHERE中不能使用组合函数。
HAVING必须跟在GROUP BY后面的。




关联查询
查询的结果集中的字段可能来自多张表。要从多张表中获取数据时，就要找到这几张表中记录的对应关系，
然后建立联系后分别获取。
所以连接条件（指明表之间记录的对应关系）在关联查询中是十分重要的。
N张表关联查询，至少有N-1个关联条件，否则会出现笛卡尔积。则是一个无用的结果集，对系统资源
开销大，甚至会使系统瘫痪。


查看每个员工以及所在部门名称
SELECT e.ename,e.deptno,d.dname
FROM emp_lb e,dept_lb d
WHERE e.deptno=d.deptno




查看SALES部门的员工都有谁？
SELECT e.ename,e.deptno
FROM emp_lb e,dept_lb d
WHERE e.deptno=d.deptno
AND d.dname='SALES'




查看职位是CLERK的员工都在哪里工作？
SELECT e.ename,e.job,d.loc
FROM emp_lb e,dept_lb d
WHERE e.deptno=d.deptno
AND e.job='CLERK'




内连接
关联查询的另一种方式
查看员工以及所在部门名称：
SELECT e.ename,d.dname
FROM emp_lb e,dept_lb d
WHERE e.deptno=d.deptno
AND d.dname='SALES'
等价于
SELECT e.ename,d.dname
FROM emp_lb e JOIN dept_lb d
ON e.deptno=d.deptno
WHERE d.dname='SALES'


UPDATE emp_lb
SET deptno=50
WHERE ename='SCOTT'


SELECT 
  e.ename,e.job,e.deptno,d.dname,d.loc
FROM emp_lb e JOIN dept_lb d
ON e.deptno=d.deptno
关联查询出现的一个问题是，不满足连接条件的数据不会被显示到结果中




查看每个员工的信息，以及其所在部门的信息，若没有部门，也要将员工信息列出来
使用外连接来解决将不满足连接条件的数据，在关联查询中显示到结果集中
外连接分为:
左外连接：以左表为驱动表，其数据都要显示，来自右表记录中的字段在结果集中，
若不满足连接条件，则值为NULL
右外连接：以右表为驱动表
全外连接:两张表的数据都要查询出来，哪边不满足条件哪边值为NULL


SELECT
  e.ename,e.job,e.deptno,d.dname,d.loc
FROM emp_lb e LEFT OUTER JOIN
ON e.deptno=d.deptno


SELECT
  e.ename,e.job,e.deptno,d.dname,d.loc
FROM emp_lb e RIGHT OUTER JOIN
ON e.deptno=d.deptno


SELECT
  e.ename,e.job,e.deptno,d.dname,d.loc
FROM emp_lb e FULL OUTER JOIN
ON e.deptno=d.deptno






自连接：
当前表的记录与当前表的其他的其他记录产生对应关系，不需要额外表的参与，
自连接的设计目的是为了解决，相同类型数据间有存在父子级关系的数据结构。
（典型的是树状结构）


每个员工以及其上司是谁？
SELECT
  e.ename,m.ename
FROM emp_lb e,emp_lb m
WHERE e.mgr=m.empno


内连接：
SELECT
  e.ename,m.ename
FROM
  emp_lb e LEFT OUTER JOIN emp_lb m
ON
  e.mgr=m.empno
  
  
  
子查询
当我们需要执行某条SQL语句时，需要某些查询的结果作为依据时，先期执行的这条
查询语句就是子查询


查看谁的工资比CLARK高？
SELECT ename,sal
FROM emp_lb
WHERE sal>(SELECT sal FROM emp_lb WHERE ename='CLARK')




DDL中使用子查询。
以子查询结果快速创建表：
该表包含：empno,ename,sal,job,deptno
CREATE TABLE myemployee_lc
AS
SELECT e.empno,e.ename,e.sal,e.job,d.deptno,d.dname,d.loc
FROM emp_lb e,dept_lb d
WHERE e.deptno=d.deptno


DML中使用子查询
删除与CLARK相同部门的所有员工
DELETE FROM emp_lb
WHERE deptno=(SELECT deptno FROM emp_lb WHERE ename='CLARK')
SELECT * FROM emp_lb




子查询根据查询的结果不同应用也不完全一样。
通常按照查询结果分为：
单行单列子查询：查询结果为1个值
多行多列子查询：查询结果为1个字段，但是有多条记录
多行多列子查询：看起来结果集像一张表，也常当做表来使用






查找和SCOTT同职的员工
SELECT e.ename,e.job
FROM emp e
WHERE e.job=(SELECT job FROM emp WHERE ename='SCOTT');


查找薪水比整个机构平均薪水高的员工
SELECT deptno,ename,sal
FROM emp_lb e
WHERE sal>(SELECT AVG(sal) FROM emp_lb);


和10号部门员工职位相同的都有谁?
SELECT e.ename,e.job
FROM emp_lb e
WHERE job IN(SELECT job FROM emp_lb WHERE deptno=10)




查看比30号部门所有员工工资都高的有谁？
SELECT ename,sal
FROM emp_lb
WHERE sal>ALL(SELECT sal FROM emp_lb WHERE deptno=30);
等价于
SELECT ename,sal
FROM emp_lb
WHERE sal>(SELECT MAX(sal) FROM emp_lb WHERE deptno=30);




EXISTS关键字
其后要跟一个查询语句，只要该查询能查询出至少一条数据，EXISTS表达式就返回真


查看有员工的部门的信息
SELECT deptno,dname FROM dept_lb d
WHERE EXISTS (SELECT * FROM emp_lb e WHERE d.deptno=e.deptno);


查看部门的最低薪水，前提是该部门最低的薪水要高于1000
SELECT MIN(sal),deptno
FROM emp_lb
GROUP BY deptno
HAVING MIN(sal)>1000




查看部门的最低薪水，前提是该部门最低的薪水要高于30号部门的最低薪水
SELECT MIN(sal),deptno
FROM emp_lb
HAVING MIN(sal)>(SELECT MIN(sal) FROM emp_lb WHERE DEPTNO=30);


查看工资高于本部门平均工资的员工信息？
SELECT e.ename,e.sal,e.deptno
FROM emp_lb e,(SELECT AVG(sal) avg_sal,deptno
              FROM emp_lb
              GROUP BY deptno) s
WHERE e.deptno=s.deptno
AND e.sal>s.avg_sal


子查询写在SELECT语句中，是外连接的一种写法
SELECT e.ename,e.sal,
  (SELECT d.dname FROM dept_lb d
  WHERE d.deptno=e.deptno)
  FROM emp_lb e;
  
  
分页查询
当表中数据量大时，查询表中数据一次性全部查询出来，会导致数据全部加载到内存中，
对系统资源占用有很大的影响，响应速度也慢，但往往用户也不需要一次性将这么多数据
全部查看，这时我们可以分批查询表中数据。
由于标准的SQL中没有对分页进行语法定义，所以不同的数据库中分页的语法不一致。




伪列：ROWNUM
该字段并不存在任何一张表中，但是可以从任何一张表中去查询该字段，当查询该字段时，ORACLE只要
可以从表中查询出一条记录，就会为该条记录中该字段添加“行号".行号从1开始，会自动增长。


SELECT ROWNUM,ename,sal,job
FROM emp_lb


SELECT ROWNUM,ename,sal,job
FROM emp_lb
WHERE ROWNUM>1
由于ROWNUM只有查询出一条记录后，才会该记录编号，然后ROWNUM自增，所以我们
不能在第一次查询表中数据时使用ROWNUM>1以上的数字进行过滤，否则将得不到任何数据。




为此，需要进行两次查询，第一次仅使用ROWNUM为结果集编号，在基于这个结果集
进行二次查询，根据ROWNUM的编号取需要的范围内的记录


SELECT *
FROM(
SELECT ROWNUM rn,ename,sal,job,deptno
    FROM emp_lb
    )
    WHERE rn BETWEEN 5 AND 10
    
    
FROM(
  SELECT ROWNUM rn,ename,sal,job,deptno
    FROM emp_lb


原因在于：使用ROWNUM对结果集编号是在查询过程中进行的，而ORDER BY排序是在
查询出结果后进行的，所以，当排序过后，原本的编号顺序就打乱了。
为此，我们需要先排序，在将排序后的结果集进行编号，然后取范围才能得到正确的结果


SELECT *
FROM(
  SELECT ROWNUM rn,t.*
  FROM(
      SELECT ename,sal,job,deptno
      FROM emp_lb
      ORDER BY sal DESC
    ) t)
WHERE rn BETWEEN 5 AND 10
 
 
pageSize：8 每页显示的条目数
page：2页数
start=(page-1)*pageSize+1
end=page*pageSize


SELECT ename,job,sal,
  DECODE(job,
          'MANAGER',sal*1.2,
          'ANALYST',sal*1.1,
          'SALESMAN',sal*1.05,
          sal
      )bonus
FROM emp_lb


和DECODE函数功能相似的有CASE语句，实现类似于if-else的操作
SELECT ename,job,sal,
  CASE job WHEN'MANAGER'THEN sal*1.2
          WHEN'ANALYST'THEN sal*1.1
          WHEN'SALESMAN'THEN sal*1.05
          ELSE sal END
  bonus
FROM emp_lb




SELECT COUNT(*),DECODE(job,'ANALYST','VIP','MANAGER','VIP','OTHER')
FROM emp_lb
GROUP BY DECODE(job,
                'ANALYST','VIP',
                'MANAGER','VIP',
                  'OTHER')