【数据库】多个MySQL表结果集组合,复杂查询

做了一个如下的小厕所,如果我需要得到返回是 d,f 那我需要用那组语句呢?

数据分组 – max,min,avg,sum,count

Eg: 数据分组查询,熟练应用max,min,avg,sum,count

1.

(min

max)

SQL> select min(sal),max(sal) fromemp;

  MIN(SAL)  MAX(SAL)

      800      5000      查询出最大工资和最少工资

2.

(min

max)

SQL>  select ename,sal fromemp where sal=(select max(sal) from emp);

ENAME           SAL

KING        5000.00        查询出员工中工资最高的人(子查询)

3.

(avg

sum)

SQL> select avg(sal),sum(sal) fromemp;

  AVG(SAL)  SUM(SAL)

2073.21428     29025      查询员工的平均工资和总工资

4.

(count)

SQL> select count(ename) from emp;

COUNT(ENAME)

         14               查询员工总人数

5.

(avg

子查询)

SQL> select * from emp where sal>(selectavg(sal) from emp);

EMPNO ENAME     JOB        MGR HIREDATE         SAL     COMM DEPTNO

7566 JONES     MANAGER   7839 1981/4/2     2975.00              20

 7698 BLAKE     MANAGER   7839
1981/5/1     2850.00              30                        查询出工资高于平均工资的人的信息

MySQL表结果集相信大家都比较了解,下面就为您介绍多个MySQL表结果集组合的几种方法,希望可以让您对MySQL表结果集有更深的了解。

A:

Groupby 和having子句

1.      Groupby 用于对查询的结果分组统计

2.      Having 子句用于限制分组的显示结果

Eg:groupby和having语句的熟练应用。注意group by后可以跟多个条件

1.(

Group

by)

SQL> selectDEPTNO,avg(sal),sum(sal) from emp group by DEPTNO order
byDEPTNO;

DEPTNO  AVG(SAL)  SUM(SAL)

10     2916.66666  8750

20      2175     10875  查询每个部门的平均工资和最高工资

2.

(group

   by)

SQL> selectDEPTNO,avg(sal),job from emp group by DEPTNO,job order by DEPTNO;

DEPTNO  AVG(SAL) JOB

10      1300 CLERK

10      2450 MANAGER

10      5000 PRESIDENT查询每个部门的每种岗位的平均工资和最低工资

3.

(group

By,  having)

SQL> selectdeptno,avg(sal) from emp group by deptno having avg(sal)<(select avg(sal) fromemp);

DEPTNO  AVG(SAL)

30 1566.66666   查询出部门平均工资低于平均工资的部门及其平均工资

3.      分组函数只能出现在选择列表、having、order by子语句中

4.      如果在select语句中同时包含group by、having和order
by,那么他们的顺序是group by→having→order by

5.      在选择列中如果有列、表达式和分组函数,那么至少有一个要出现在group
by字句中,否则则会出错。 —(oracle11g测试分组条件可以不出现在查询字段中)

select * from table2
union all
select * from table2

;WITH CA AS(
SELECT *
    FROM (VALUES('a'),('b'),('c'),('d'))a (A))
,CB AS (
SELECT *
    FROM (VALUES('a'),('b'),('c'),('f'))a (A)
)
SELECT * FROM CA
EXCEPT SELECT * FROM CB
UNION 
SELECT * FROM CB
EXCEPT SELECT * FROM CA

 Oracle的多表查询

select * from table2 union select * from table2

B:

—基于2个或两个以上的表的查询

Eg:表的联力查询

1.

(a1.no=

a2.no)

显示雇员名,雇员工资和所在部门的名称

SQL> select ename,sal,DNAME from emp,dept whereemp.DEPTNO =
dept.DEPTNO;

ENAME           SAL DNAME

CLARK       2450.00 ACCOUNTING

SCOTT       3000.00 RESEARCH

WARD        1250.00 SALES

2.

(and

)

显示部门号为10的部门名和员工和工资

SQL> select ename,sal,DNAME from emp,dept whereemp.DEPTNO =
dept.DEPTNO and emp.deptno=10;

ENAME           SAL DNAME

CLARK       2450.00 ACCOUNTING

3.

(between

and)

显示雇员的性命,工资及其工资的级别 – (添加表salgrade)

SQL> select ename,sal,grade from emp,salgradewhere emp.sal between salgrade.losal
andsalgrade.hisal;

ENAME           SAL     GRADE

SMITH        800.00         1

MILLER      1300.00         2

ALLEN       1600.00         3

JONES       2975.00         4

KING        5000.00         5

注:where内容可where
(emp.sal>salgrade.losal andemp.sal

4.

(order

by)

显示雇员名,工资以及部门名,并按部门排序

SQL> select ename,sal,dname from emp,dept whereemp.deptno=dept.deptno
order by dept.dname;

ENAME           SAL DNAME

MILLER       1300.00ACCOUNTING

KING        5000.00 ACCOUNTING

FORD        3000.00 RESEARCH

在MS-SQL如果将两个或更多查询的结果组合为单个结果集,该结果集包含联合查询中的所有查询的全部行,常用的方法如下:

;WITH CA AS(
SELECT *
    FROM (VALUES('a'),('b'),('c'),('d'))a (A))
,CB AS (
SELECT *
    FROM (VALUES('a'),('b'),('c'),('f'))a (A)
)
SELECT * FROM CA
EXCEPT SELECT * FROM CB
UNION 
(SELECT * FROM CB
EXCEPT SELECT * FROM CA)

自连接查询

  • ### 自连接是指在同一张表的连接查询

Eg:显示员工的上级领导的姓名— 同表联立当成2个表来考虑就可以了

SQL> selecta1.empno,a1.ename,a1.mgr,a2.empno,a2.ename from emp a1,emp a2 wherea1.mgr=a2.empno;

EMPNO ENAME       MGR EMPNO ENAME

7902 FORD       7566  7566JONES

7788 SCOTT      7566  7566JONES

  • ### 子查询

—- 子查询是指嵌入在其他sql语句中的select语句

一.   使用union 或union All语句
1.   union 与 union all语句的区别
Ø UNION 组合多个表或结果集)并将其作为单个结果集返回;
Ø UNION ALL 在结果中包含所有的行,包括重复行。
Ø 也就是说,使用UNION组合两个表时,将重复的记录删除;而使用UNION
ALL组合两多个表时,不考虑结果集中是否存在重复记录。
2.   使用 UNION 运算符时请注意:
Ø 所有查询中的列数和列的顺序必须相同。
 在使用 UNION
运算符组合的语句中,所有显示列表的数目必须相同列表内容可以包括:列名、算术表达式、聚合函数等);
Ø 数据类型必须兼容。
 在使用 UNION
组合的结果集中的相应列必须具有相同数据类型,或者两种数据类型之间存在隐性数据转换,或提供了类型转换。例如,在datetime
数据类型的列和 binary 数据类型的列之间不可能存在 UNION
运算符,除非提供了显式转换,而在 money 数据类型的列和 int
数据类型的列之间可以存在 UNION 运算符,因为它们可以进行隐性转换。
Ø 用 UNION 运算符组合的各语句中对应的结果集列出现的顺序必须相同,因为
UNION 运算符是按照各个查询给定的顺序逐个比较各列。
3.   与其它 Transact-SQL语句一起使用 UNION 语句时,应注意:
Ø
如果在有UNION的语句中使用INTO语句,则只能在最终表或结果集)中使用,如果要在单个表或结果集)中使用INTO语句,MS-SQL将会提示错误;
错误语句:Select AID,AName,ADemo Into From ATable Union All Select
BID,BName,BDemo Into From Btable
Ø 只有在 UNION 语句的结尾,才允许使用 ORDER BY 和 COMPUTE
子句以定义最终结果的顺序或计算汇总值。不能在组建 UNION
语句的单独查询中使用这些子句。
错误语句:
Select AID,AName,ADemo From ATable order by AID
Union All
Select BID,BName,BDemo From BTable Order By BID  
可以这样写:
Select * From
(Select AID,AName,ADemo From ATable Union All Select BID,BName,BDemo
From BTable) a
Order By a.AID
也可以这样写:
Select AID,AName,ADemo From ATable
Union All
Select BID,BName,BDemo From BTable
  Order By AID
  
此处之所以按AID来排序,是因为MS-SQL中将最后的结果表中的第一列命名为AID。某些DBMS的系统并不对使用不同列名的查询的表列命名,这样就不能使用Order
By 列名的方式来排序,不过可以引用结果表中列的顺序来排序,例如:Order By
1,则相当于Order By AID
Ø GROUP BY 和 HAVING
子句只能在单独的查询中使用;它们不能用来影响最终的结果集。
Ø UNION 运算符可用于 INSERT 语句中。
 FOR BROWSE 子句不能在包含 UNION 运算符的语句中使用。FOR
BROWSE使用及说明可以参考SELECT语句的说明)
  
注意:某些DBMS系统在由UNION组合查询的Select子句中不允许使用函数和表达式。

 C:

单行子查询

—- 只返回单行数据的子查询

Eg:查询与SMITH同一部门的所有员工

SQL> select ename,DEPTNO from emp whereDEPTNO=(select DEPTNO from emp whereename=’SMITH’);

ENAME     DEPTNO

SMITH         20

SCOTT         20

二.使用except 或 INTERSECT 运算符
与UNION相似,except 或 INTERSECT
运算符也可以将多个表或结果集)组合生成一个新表或结果集),只是结果不同而已。
1.   Except运算符
和 UNION 指令类似, Except也是对两个 SQL
语句所产生的结果做处理的。不同的地方是Except在每一个表或结果集)与第二个表或结果集)比较前,已从每一个表的结果集中清除了所有重复的信息。也就是从Except运算所得到的结果集绝不会包含重复的记录行)。
例如:我们要在Table_A(学生信息表)与Table_B(学生必修课完成信息表)中找出未完成的学生信息
   Select * From Table_A
   Except
   Select * From Table_B
注意:许多DBMS系统不支持EXCEPT运算符,则此时不得不使用Left Outer
Join运算符来实现。而在Oracle中的Minus与EXCEPT相似。
2.  INTERSECT运算符
和 UNION 指令类似, INTERSECT 也是对两个 SQL
语句所产生的结果做处理的。不同的地方是,UNION 是联集,而 INTERSECT
是交集。也就是说,INTERSECT生成的结果集是多个表或结果集所共有的记录行)。
   Select AID,AName,ADemo From ATable
   INTERSECT
Select BID,BName,BDemo From Btable

;WITH CA AS(
SELECT *
    FROM (VALUES('a'),('b'),('c'),('d'))a (A))
,CB AS (
SELECT *
    FROM (VALUES('a'),('b'),('c'),('f'))a (A)
)
(SELECT * FROM CA
EXCEPT SELECT * FROM CB)
UNION 
(SELECT * FROM CB
EXCEPT SELECT * FROM CA)

多行子查询

—- 返回多行数据的子查询

Eg:查询和部门10的工作相同的雇员的姓名、工作和部门号

SQL> select ename,job,deptno from empwhere job in (select distinct job from empwhere deptno=10);

ENAME     JOB      DEPTNO

CLARK     MANAGER      10

BLAKE     MANAGER      30

JONES     MANAGER      20

—- 使用all操作符

Eg:查询出工资比部门20的所有员工的工资高的员工

SQL> select ename,sal,deptno from emp wheresal> all (select sal from empwhere deptno=30);

ENAME           SAL DEPTNO

JONES       2975.00    20

SCOTT       3000.00    20

— 使用any操作符

Eg:显示工资比20部门任意一个员工工资的员工

SQL> select ename,sal,deptno from emp wheresal> any (select sal from empwhere deptno=30);

ENAME           SAL DEPTNO

KING        5000.00    10

FORD        3000.00    20

三.使用UNION Corresponding组合来自两个或多个与UNION不兼容的表中的行
例如:有两个表
   Create Table Table_A(ID int,office int,address
varchar(20),department char(5),pay money)
   Create Table Table_B(office int,ID int,address
varchar(20),department char(5))
   可以使用UNION Corresponding来获取两个表中有共同列名的列中数据:
   Select * From table_A UNION Corresponding Select * From Table_b
   注意:并不是所有的DBMS都支持UNION Corresponding运算,包括MS-SQL
Server

 

多列子查询

— where后的条件是一一对应的,实际是两个条件的and连接

Eg:查询出与’SMITH’部门和岗位一样的员工信息

SQL> selectename,job,deptno from emp where (deptno,job)=(select deptno,job from emp whereename=’SMITH’);

ENAME     JOB      DEPTNO

ADAMS     CLERK        20

— 在from子句中使用子查询  —-  重要内容

 From中的子查询被认定为一个内嵌试图,并且应该取别名

Eg:显示高于自己部门平均工资的员工信息

SQL> selectename,sal,mysal,a1.deptno from emp a1,(select deptno,avg(sal) mysal from emp group bydeptno) a2 wherea1.deptno=a2.deptno anda1.sal>a2.mysal;

ENAME           SAL     MYSAL DEPTNO

ALLEN       1600.00 1566.66666    30

JONES       2975.00      2175    20

MySQL分表处理的实现方法

 实际情况执行一下就知道,其实BC
的语句都可以执行。而A的执行情况就是只返回了 d。这个其实是跟select
的执行顺序有关的。

分页查询          —–         非常重要

  • Rownum分页

a.      (select * from emp) 建立内嵌试图

b.      显示rownum。(这里的rownum是oracle自带分配的)

Eg

SQL> select ename,job,sal,rownum rn from(select * from emp) a1;

ENAME     JOB            SAL        RN

SMITH     CLERK       800.00         1

ALLEN     SALESMAN   1600.00         2

WARD      SALESMAN   1250.00         3

c.      筛选出rn为6-10的数据 –(非常重要)

d.      Oracle采用二分法筛选,所以需要先筛选出1-10.,在筛选出6-10

e.      如需改变查询列时,只需改变最里层的select的就可以了

f.       如需排序,也只需修改最里层的select视图就可以了

Eg: 在第一次的筛选中要用rownum,第二次要用别名rn

SQL> select * from (select a1.*,rownum rn from (selectename,job,sal from emp) a1 where rownum<=10) wherern>=6;

ENAME     JOB            SAL        RN

BLAKE     MANAGER    2850.00         6

CLARK     MANAGER    2450.00         7

SCOTT     ANALYST    3000.00         8

  • 根据rowid分页

  • 按分析函数分页

MySQL授权表使用示例

通常我们看select
的执行顺序的时候,可能会忽略这2个不常用的连接谓词导致误判。这里做一个实验就可以发现,其实 Except(Intersect
同理) 的查询优先级是和 union 或者union all 是同级的。

利用查询结果来创建新表

Eg:创建一个部门平均工资表从emp表中生成

SQL> create table avgsal (deptno,avgsal) as select deptno,avg(sal) fromemp group by deptno;

Table created

SQL> select * from avgsal;

DEPTNO    AVGSAL

30      1566.66666

20      2175

10      2916.66666

MySQL内存表的弊端

所以遵循从左到右的顺序,如果不用括号改变执行顺序,从上面的例子,将会返回
 CA Except CB -> Union CB -> Except CA
的执行结果。也就是一个容易忽略的位置。

合并查询   —-    比较麻烦,但效率较高

Ag:在实际应用中,为了合并多个select语句的结果,可以使用集合操作符号:union all,union,intersect,minus

教您彻底删除MySQL注册表信息

因为比较少用,所以我也躺枪了。在此分享一波,希望大家不要踩坑。

a.      Union

Union用于取得两个结果集的合集

Eg:查询sal大于2500并且部门为20的员工,实际效果等同于and

SQL> select ename,deptno,sal from emp wheresal>2500

  2  union select ename,deptno,sal from emp where deptno=20;

ENAME     DEPTNO      SAL

ADAMS         20  1100.00

BLAKE         30  2850.00

FORD          20  3000.00

JONES         20  2975.00

KING          10  5000.00

Mysql User表的权限字段说明介绍

PS

b.      Unionall

注: union
all与union用法相同,但他的效果是缺两个集合的全集(不会合并相同的)

selec…

1 select 执行顺序的地址

c.      Intersect

Intersect是用于取两个集合的交集

Eg: 对于分页查询同样可以采用这种方法

SQL> select * from (select ename,job,sal,rownumrn from emp) where rownum<=10

  2  intersect select * from (selectename,job,sal,rownum rn from emp) where rn>=6;

ENAME     JOB            SAL        RN

BLAKE     MANAGER    2850.00         6

CLARK     MANAGER    2450.00         7

KING      PRESIDENT  5000.00         9

SCOTT     ANALYST    3000.00         8

TURNER    SALESMAN   1500.00        10

2 Except 和 Intersect 都是返回交叉之后不重复的结果的,这个需要特别注意

d.      Minus

Minus是取两个集合的差集(即取A集合减去与B集合重复的部分)

union all, union,intersect,minus

Ag:A:(1,2,3)B:(3,4,5){
        1.   A minus B = (1,2)
        2.   A  union B= (1,2,3,4,5)
        3.   A  uniomall  B = (1,2,3,3,4,5)
        4.   A intersect  B =
(3)数据库 1