首页 > 安全资讯 >

SQL(高级查询)

16-12-13

SQL(高级查询)。子查询是一条SELECT语句,但它是嵌套其他SQL语句中的,为的是给该SQL提供数据以支持其执行操作。

SQL(高级查询)。

子查询

子查询是一条SELECT语句,但它是嵌套其他SQL语句中的,为的是给该SQL提供数据以支持其执行操作。

查看谁的工资高于CLARK?
SELECT ename,sal
FROM emp_swm
WHERE sal>(
    SELECT sal
    FROM emp_swm
    WHERE ename='CLARK')

查看与CLARK同职位的员工?
  SELECT ename,job
  FROM emp_swm
  WHERE job=(SELECT job FROM emp_swm
            WHERE ename='CLARK')

查看与CLARK同部门的员工?
  SELECT ename,deptno 
  FROM emp_swm 
  WHERE deptno=(SELECT deptno FROM emp_swm
                WHERE ename='CLARK')

在DDL中使用子查询

可以根据子查询的结果集快速创建一张表

创建表employee,表中字段为:
empno,ename,job,sal,deptno,dname,loc
数据为现有表中emp与dept对应的数据

CREATE TABLE employee_swm
  AS
  SELECT e.empno id,e.ename name,e.job,e.sal salary,
    e.deptno,d.dname,d.loc
  FROM emp_swm e,dept_swm d
  WHERE e.deptno=d.deptno

DESC employee_swm
SELECT * FROM employee_swm

创建表时若子查询中的字段有别名,则该表
对应得字段就使用该别名作为其字段名。
当子查询中一个字段含有函数或表达式
那么该字段必须给别名。

删除表
DROP TABLE employee_swm

DML 中使用子查询

将CLARK所在部门的所有员工删除
  DELETE FROM employee_swm
  WHERE deptno=(SELECT deptno FROM employee_swm 
                WHERE name='CLARK')


查询薪水比整个机构平均薪水高的员工?
  SELECT name,salary
  FROM employee_swm
  WHERE (SELECT AVG(salary) FROM employee_swm)

子查询常用于SELECT语句中,子查询根据查询结果集的不同分为:

单行单列子查询:

常用于过滤条件, 可以配合=,>,>=,<,<=使用

多行单列子查询:

常用于过滤条件,由于查询出多个值 在判断=时要用IN,判断>,>=等操作配合ANY,ALL

多行多列子查询:

常当做一张表来看待

查询与SALESMAN同部门的其他职位员工:
  SELECT ename,job,deptno
  FROM emp_swm
  WHERE deptno IN(SELECT deptno 
                    FROM emp_swm WHERE job='SALESMAN')
        AND job<>'SALESMAN'

查看比职位是CLARK和SALESMAN工资都高的员工?
  SELECT ename,sal
  FROM emp_swm
  WHERE sal>ALL(SELECT sal FROM emp_swm 
          WHERE job IN('CLARK','SALESMAN'))

EXISTS 关键字

EXISTS后面跟一个子查询,当该子查询可以查询出
至少一条记录时,则EXISTS表达式成立并返回true

SELECT deptno,dname FROM dept_swm d
WHERE NOT EXISTS(SELECT * FROM emp_swm e 
  WHERE d.deptno=e.deptno)

查看每个部门的最低薪水是多少?前提是该部门的最低薪水都要高于30号部门的最低薪水

SELECT deptno,MIN(sal)
FROM emp_swm
GROUP BY deptno
HAVING MIN(sal)>(SELECT MIN(sal) FROM emp_swm 
  WHERE deptno=30)

子查询在 FROM 子句中使用

当一个子查询是多列子查询,通常将该子查询
的结果集当做一张表看待并基于它进行二次查询。

查看比自己所在部门平均工资高的员工?
  1:查看每个部门的平均工资
  SELECT AVG(sal),deptno FROM emp_swm
  GROUP BY deptno

  SELECT e.ename,e.sal,e.deptno
  FROM emp_swm e,(SELECT AVG(sal) avg_sal,deptno 
      FROM emp_swm GROUP BY deptno) t
  WHERE e.deptno=t.deptno
  AND e.sal>t.avg_sal

子查询在SELECT子句中使用

可以将查询的结果当做外层查询记录
中的一个字段值显示

SELECT e.ename,e.sal,(SELECT d.dname 
      FROM dept_swm d 
      WHERE d.deptno=e.deptno) deptno
  FROM emp_swm e

分页查询

分页查询时将查询表中数据时,分段查询
而不是一次性将所有数据查询出来。
有时查询数据量非常庞大,这会导致系统资源
消耗大,相应速度长,数据冗余严重。
为此当遇到这种情况时一般使用分页查询解决。
数据库基本都支持分页,但是不同数据库
语法不同。

Oracle中的分页是基于伪列ROWNUM实现的。
ROWNUM 不存在于任何一张表中,但是所有的表
都可以查询该字段,该字段的值是随着查询自动
生成的,方法是:每当可以从表中查询一条记录时,
该字段的值即为该条记录的行号,从1开始,逐次递增

SELECT ROWNUM,empno,ename,sal,job
  FROM emp_swm WHERE ROWNUM>5

在使用ROWNUM对结果集进行编号的查询过程中,不能使用ROWNUM做>1以上的数字判断,
否则查询不出任何数据。

SELECT * FROM(SELECT ROWNUM rn,empno,
  ename,sal,job
  FROM emp_swm)
WHERE rn BETWEEN 6 AND 10

查看公司工资排名的6-10

  SELECT * FROM
    (SELECT ROWNUM rn,t.* FROM
      (SELECT empno,ename,sal FROM emp_swm
       ORDER BY sal DESC) t
    )
  WHERE rn BETWEEN 6 AND 10

  SELECT * FROM 
    (SELECT ROWNUM rn,t.* FROM
      (SELECT empno,ename,sal FROM emp_swm
       ORDER BY sal DESC
      ) t 
     WHERE ROWNUM<=10  
    )
  WHERE rn>=6

计算区间公式
  pageSize:每页显示的条目数
  page:页数

  star:(page-1)*pageSize+1
  end:pageSize*page

  int start = (page-1)*pageSize+1;
  int end = pageSize*page;

  String sql= "SELECT * FROM "+
    "(SELECT ROWNUM rn,t.* FROM"+
      "(SELECT empno,ename,sal FROM emp_swm"+
      " ORDER BY sal DESC"+
     " ) t "+
    " WHERE ROWNUM<="+end+
   " )"+
  "WHERE rn>="+start;

DECODE函数,可以实现分支效果的函数

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

DECODE 在GROUP BY 分组中的应用可以将字段不同的记录看做一组

统计人数,将职位是'MANAGER','ANALYST'
  看作一组,其余职业看做另外一组分别统计人数
  SELECT COUNT(*),
    DECODE(
      job
      ,'MANAGER','VIP'
      ,'ANALYST','VIP'
      ,'OTHER')
  FROM emp_swm 
  GROUP BY 
    DECODE(
      job
      ,'MANAGER','VIP'
      ,'ANALYST','VIP'
      ,'OTHER'
    )

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

排序函数

排序函数允许对结果集按照指定的字段分组
在组内再按照指定的字段排序,最终生成组内编号。

ROW_NUMBER()函数

生成组内连续且唯一的数字:

PARTITION BY 根据什么分组

ORDER BY 根据什么排序

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

RANK函数

生成组内不连续也不唯一的数字,同组内排序字段值一样的记录
生成的数字也一样。

SELECT ename,sal,deptno,
    RANK() 
    OVER(
      PARTITION BY deptno
      ORDER BY sal DESC
    )
FROM emp_swm

DENSE_RANK函数

生成组内连续但不唯一的数字。

SELECT ename,sal,deptno
    ,DENSE_RANK()
    OVER(
        PARTITION BY deptno
        ORDER BY sal DESC
    ) rank
FROM emp_swm

集合操作

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

UNION、UNION ALL

**UNION和UNION ALL用来获取两个或两个以上结果集的并集:
UNION操作符会自动去掉合并后的重复记录。
UNION ALL返回两个结果集中的所有行,包括重复的行。
UNION操作符对查询结果排序,UNION ALL不排序。**

合并职位是’MANAGER’的员工和薪水大于2500的员工集合,查看两种方法的结果差别

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

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

INTERSECT

INTERSECT函数获得两个结果集的交集,只有同时存在于两个结果集中的数据,才被显示输出。使用INTERSECT操作符后的结果集会以第一列的数据作升序排列。

SELECT ename,job,sal FROM emp_swm
WHERE job='MANAGER'
    INTERSECT
SELECT ename,job,sal FROM emp_swm
WHERE sal>2500;

MINUS

MINUS函数获取两个结果集的差集。只有在第一个结果集中存在,在第二个结果集中不存在的数据,才能够被显示出来。也就是结果集一减去结果集二的结果。

列出职位是MANAGER但薪水低于2500的员工记录:

SELECT ename, job, sal FROM emp
WHERE job = 'MANAGER'
MINUS
SELECT ename, job, sal FROM emp
WHERE sal> 2500;

创建练习表 sales_tab_swm

CREATE TABLE sales_tab_swm(
    year_id NUMBER NOT NULL,
    month_id NUMBER NOT NULL,
    day_id NUMBER NOT NULL,
    sales_value NUMBER(10,2) NOT NULL
);

随机插入1000条数据

INSERT INTO sales_tab
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,
    TRUNC(DBMS_RANDOM.value(1,100)) AS sales_value
FROM dual
CONNECT BY level<=1000;
COMMIT

查看每天的营业额?

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

每月的营业额?

SELECT year_id,month_id,SUM(sales_value)
FROM sales_tab_swm
GROUP BY year_id,month_id
ORDER BY year_id,month_id

每年的营业额?

SELECT year_id,SUM(sales_value)
FROM sales_tab_swm
GROUP BY year_id
ORDER BY year_id

总共的营业额?

SELECT SUM(sales_value)
FROM sales_tab_swm

高级分组函数

高级分组函数用在GROUP BY 子句中,每个高级分组函数都有一套分组策略。

ROLLUP():

分组原则,参数逐次递减,一直到所有参数都不要,每一种分组都统计一次结果并且在一个结果集显示。

GROUP BY ROLLUP(a,b,c)
等价于:
GROUP BY a,b,c
UNION ALL
GROUP BY a,b
UNION ALL
GROUP BY a
UNION ALL
全表

查看每天,每月,每年以及总共的营业额?

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

CUBE()

每种组合分一组。分组次数:2的参数个数次方

SELECT year_id,month_id,day_id,SUM(sales_value)
FROM sales_tab_swm
GROUP BY
CUBE(year_id,month_id,day_id)
ORDR BY year_id,month_id,day_id

GOUPING SETS

每个参数是一种分组方式,然后将这些分组统计后,并在一个结果集显示。

仅查看每天和每月营业额?

SELECT year_id,month_id,day_id,SUM(sales_value)
FROM sales_tab_swm
GROUP BY
GROUPING SETS((year_id,month_id,day_id),
    (year_id,month_id)
)
相关文章
最新文章
热点推荐