数据库笔记-SQLday04

数据查询语言（DQL）：Data Query Language


--子查询：
--子查询是一条查询语句，其嵌套在其他SQL语句之中，为其嵌套的SQL语句提供数据以便执行使用。


--查看比CLARK工资高的员工？
 SELECT ename,sal
 FROM emp_wensq
 WHERE sal>(
          SELECT sal
          FROM emp_wensq
          WHERE ename='CLARK'
         )
--查看和FORD相同部门的员工？
SELECT ename,deptno
FROM emp_wensq
WHERE deptno=(SELECT deptno FROM emp_wensq WHERE ename='FORD')


--查看比公司平均工资高的员工信息?
SELECT ename,sal
FROM emp_wensq
WHERE sal>(SELECT AVG(sal)FROM emp_wensq)


--DDL中使用子查询
--创建一张表myemployee_wensq,包含empnp,ename,sal,job,deptno,dname,loc字段，并且数据与emp和dept一致
CREATE TABLE myemployee_wensq
AS
SELECT e.empno,e.ename,e.sal,e.job,d.deptno,d.dname,d.loc
FROM emp_wensq e,dept_wensq d
WHERE e.deptno=d.deptno


DESC myemployee_wensq
SELECT * FROM myemployee_wensq


--DML中也可以使用子查询
--删除与FORD同部门的所有员工
DELETE FROM emp_wensq
WHERE deptno=(SELECT deptno FROM emp_wensq WHERE ename='FORD')


--查看与20号部门职位相同的员工信息?
SELECT ename,job
FROM emp_wensq
WHERE job IN (SELECT job FROM emp_wensq WHERE deptno=20)


--查看比职位是CLERK的工资都高的员工信息?
SELECT ename,sal
FROM emp_wensq
WHERE sal>ALL(SELECT sal FROM emp_wensq WHERE job='CLERK')


--EXISTS关键字
--用于WHERE子句，EXISTS关键字后面跟一个子查询，只要该查询能查询出至少一条记录，那么就返回TRUE
SELECT deptno,dname FROM dept_wensq d
WHERE EXISTS(SELECT *FROM emp_wensq e WHERE d.deptno=e.deptno)


--★那些人是别人的领导?
SELECT m.ename FROM emp_wensq m
WHERE EXISTS(SELECT * FROM emp_wensq e WHERE m.empno=e.mgr )


--查看各部门的最低薪水，前提是比20号部门的最低薪水高？
SELECT deptno,MIN(sal) FROM emp_wensq
GROUP BY deptno
HAVING MIN(sal)>(SELECT MIN(sal) FROM emp_wensq WHERE deptno=20)


--查看谁比自己所在部门平均工资高？
SELECT e.ename,e.sal,e.deptno
FROM emp_wensq e,(SELECT AVG(sal) avg_sal, deptno
                 FROM emp_wensq
                 GROUP BY deptno) s
WHERE e.deptno=s.deptno AND e.sal>s.avg_sal


SELECT e.ename,e.sal,(SELECT d.dname FROM dept_wensq d
                    WHERE d.deptno=e.deptno) dname
FROM emp_wensq e;


--分页查询
--当查询数据量庞大时，通常会将数据"分段查询出来"，这样做可以减少内存开销，提高响应速度。
--由于标准SQL中没有规定分页的语法，所以不同数据库针对分页的操作是不一样的。
--在ORACLE中分页是基于伪列ROWNUM实现的
--ROWNUM不存在与任何一张表中，但是每张表都可以查询该列，该；列的值是为查询出来的每一条记录添加一个行号。
--ROWNUM的值会自增，从1开始，只要
可以从表中查询出一条记录，就会为其标号，然后自增。


SELECT ROWNUM,ename,sal,JOB,deptno FROM emp_wensq
--查看第五条以后的数据?
SELECT ROWNUM,ename,sal,JOB,deptno FROM emp_wensq
WHERE ROWNUM>1                                       --查不到
--在使用ROWNUM对结果集编行号的过程中，不应当使用WHERE进行ROWNUM>1以上的数字进行过滤，否则得不到任何结果。


--查看5-10条记录
SELECT * FROM(SELECT ROWNUM ro,ename,sal,job,deptno FROM emp_wensq)t
WHERE ro BETWEEN 5 AND 10


--查看公司工资排名的5-10名
SELECT *
FROM(SELECT ROWNUM rn,t.*
    FROM(SELECT ename,sal,job,deptno
     FROM emp_wensq
     ORDER BY sal DESC)t)
WHERE rn BETWEEN 5 AND 10


WHERE rn BETWEEN 6 AND 10
page(页数) pagesize(每页的条目数)
STAR = (page-1)*pagesize+1
END = pageSize*page


--DECODE函数可以实现分支--相当于Java中的 switch--case
SELECT ename,job,sal,
     DECODE (job,
            'MANAGER',sal*1.2,
            'ANALYST',sal*1.1,
            'SALESMAN',sal*1.05,
            sal
     ) bonus
FROM emp_wensq


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_wensq    .


--统计公司中：MANAGER和ANALYST一共多少人，其他职位共多少人
SELECT
  COUNT(*),DECODE(job,
                  'MANAGER','VIP',
                  'ANALYST','VIP',
                  'OTHER')
FROM emp_wensq
GROUP BY DECODE(job,
                  'MANAGER','VIP',
                  'ANALYST','VIP',
                  'OTHER')




SELECT deptno,dname,loc
FROM dept_wensq
ORDER BY DECODE(dname,
                'OPERATIONS',1,
                'ACCOUNTING',2,
                'SALES',3)


--排序函数
--排序函数允许我们根据指定的字段分组，然后再按照指定的字段排序后生成一个组内记录的编号
--ROW_NUMBER 生成组内连续，唯一的数字


--查看每个部门的工资排名?
SELECT ename,sal,deptno,
 ROW_NUMBER() OVER(
     PARTITION BY deptno
     ORDER BY sal DESC
     )rank
FROM emp_wensq


--RANK函数，生成组内不连续不唯一的数字
SELECT ename,sal,deptno,
 RANK() OVER(
     PARTITION BY deptno
     ORDER BY sal DESC
     )rank
FROM emp_wensq


--DENSE_RANK  生成组内连续，但不唯一的数字
SELECT ename,sal,deptno,
 DENSE_RANK() OVER(
     PARTITION BY deptno
     ORDER BY sal DESC
     )rank
FROM emp_wensq


UNION、UNION ALL


--集合操作
--为了合并多个SELECT语句的结果，可以使用集合操作符，实现集合的并、交、差。
--集合操作符包括UNION、UNION ALL、INTERSECT和MINUS。多条作集合操作的SELECT语句的列的个数和数据类型必须匹配。
--ORDER BY子句只能放在最后的一个查询语句中。


--UNION操作符会自动去掉合并后的重复记录。
--UNION ALL返回两个结果集中的所有行，包括重复的行。
--UNION操作符对查询结果排序，UNION ALL不排序。


--合并职位是’MANAGER’的员工和薪水大于2500的员工集合，查看两种方式的结果差别：
SELECT ename,job,sal FROM emp_wensq
WHERE job = 'MANAGER'
UNION
SELECT ename,job,sal FROM emp_wensq
WHERE sal>2500


SELECT ename,job,sal FROM emp_wensq
WHERE job = 'MANAGER'
UNION ALL
SELECT ename,job,sal FROM emp_wensq
WHERE sal>2500


--显示职位是’MANAGER’的员工和薪水大于2500的员工的交集
SELECT ename,job,sal FROM emp_wensq
WHERE job='MANAGER'
INTERSECT
SELECT ename,job,sal FROM emp_wensq
WHERE sal>2500


--INTERSECT函数获得两个结果集的交集，只有同时存在于两个结果集中的数据，才被显示输出。使用INTERSECT操作符后的结果集会以第一列的数据作升序排列。
--例如：显示职位是’MANAGER’的员工和薪水大于2500的员工的交集：
SELECT ename,job,sal FROM emp_wensq
WHERE job='MANAGER'
MINUS
SELECT ename,job,sal FROM emp_wensq
WHERE sal>2500


CREATE TABLE sales_tab_wensq(
year_id NUMBER NOT NULL,
month_id NUMBER NOT NULL,
day_id NUMBER NOT NULL,
sales_value NUMBER(10,2)NOT NULL);
INSERT INTO sales_wensq
SELECT TRUNC(DBMS_RANDOM.value(2010,2012))AS year_id,
       TRUNC(DBMS_RANDOM.value(1,13))AS month_id,
       TRUNC(DBMS_RANDOM.value(1,32))AS day_id,
       ROUND(DBMS_RANDOM.value(1,100),2)AS sales_value
FROM dual
CONNECT BY level<=1000;


--查看每天营业额？




--高级分组函数
--ROLLUP() 函数可以使用在GROUP BY 子句中，ROLLUP函数的参数可以是任意个。ROLLUP会根据参数逐个
--递减的方式分别进行分组，然后将所有分组的结果并在一个结果集例显示


GROUP BY ROLLUP(a,b,c)


GROUP BY a,b,c
UNION ALL
GROUP BY b,c
UNION ALL
GROUP BY c


SELECT year_id,month_id,
       day_id,SUM(sales_value)
FROM sales_wensq
GROUP BY
ROLLUP(year_id,month_id,day_id)
ORDER BY year_id,month_id,day_id


GROUP BY CUBE(a,b,c)
a,b,c
a,b
a,c
b,c
a
b
c
--全方位


SELECT year_id,month_id,
       day_id,SUM(sales_value)
FROM sales_wensq
GROUP BY
CUBE(year_id,month_id,day_id)
ORDER BY year_id,month_id,day_id


--查看每天和美誉的销售额？
GROUPING SETS()
--该高级分组函数允许我们按照自定义的分组方式进行统计，再将它们并到一个结果集显示
SELECT year_id,month_id,
       day_id,SUM(sales_value)
FROM sales_wensq
GROUP BY
GROUPING SETS(
              (year_id,month_id,day_id),
              (year_id,month_id)
              )
ORDER BY year_id,month_id,day_id






SELECT * FROM sales_wensq
RENAME sales_tab_wensq TO sales_wensq
SELECT *FROM emp_wensq