4、条件查询02

1 关于查询结果的去重？

select distinct job from emp； //distinct 关键字

+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+

select ename,distinct job from emp；

以上的sql语句是错误的。

注意：distinct 只能出现在所有字段的最前面。

select distinct job from emp; //distinct对于后面的联合起来去重

+--------+-----------+
| deptno | job       |
+--------+-----------+
|     20 | CLERK     |
|     30 | SALESMAN  |
|     20 | MANAGER   |
|     30 | MANAGER   |
|     10 | MANAGER   |
|     20 | ANALYST   |
|     10 | PRESIDENT |
|     30 | CLERK     |
|     10 | CLERK     |
+--------+-----------+

案例：统计岗位的数量

select count(distinct job) from emp;

+---------------------+
| count(distinct job) |
+---------------------+
|                   5 |
+---------------------+

2 连接查询

2.1 什么是连接查询

在实际开发中，大部分的情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终的结果。

在实际开发中，一般一个业务都会对应多张表，比如：学会和班级，起码两张表。

2.2 连接查询的分类？

根据语法出现的年代来划分的话，包括：

SQL92（一些老的DBA可能还在使用这种语法。DBA：DataBase Administrator，数据库管理员）

SQL99（比较新的语法）

根据表的连接方式来划分，包括：

内连接：

等值连接

费等值连接

自连接

外连接：

左外连接

右外连接

全连接（基本不用）

2.3 在外连接查询方面有一种现象被称为：笛卡尔现象。（笛卡尔乘积现象）

笛卡尔现象：当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。

关于表的别名：

select e.ename,d.dname from emp e,dept d;

表的别名有什么好处？

第一：执行效率高。

第二：可读性好。

2.4 怎么避免笛卡尔积现象？当然是加条件进行过滤。

思考：避免了笛卡尔积现象，会减少记录的匹配次数吗？

回答：不会，次数不变，只不过显示的是有效记录。

案例：找出每一个员工的部门名称，要求显示员工名和部门名。

select

e.ename,d.dname

from

emp e , dept d

where

e.deptno = d.deptno; //SQL92,以后不用

+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
+--------+------------+
14 rows in set (0.33 sec)

2.5 内连接之等值连接：最大的特点是：条件是等量关系。

案例：找出每一个员工的部门名称，要求显示员工名和部门名。

sql92：(太老，不用了)
?
    select
    e.ename,d.dname
    from
        emp e , dept d
    where
        e.deptno = d.deptno;   

    sql99：（常用）
    select
        e.ename,d.dname
    inner from      //inner 可以省略 带着可读性更好 表示内连接
        emp e 
    join
        dept d
    on
    e.deptno = d.deptno;  
    语法：
        。。。
            A
        join
            B
        on
            连接条件
        where
            。。。
    SQL99语法结构更清晰一些：表的连接条件和后来的where条件分离了。

2.6 内连接之非等值连接：最大的特点是：连接条件中的关系是非等量关系。

案例：找出每个员工的工资等级，要求显示员工名、工资、工资等级。

select ename，sal from emp；

+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+

select * from salgrade s;

+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

select -> e.ename,e.sal,s.grade -> from -> emp e -> join -> salgrade s -> on -> e.sal between s.losal and s.hisal;

+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+

2.7自连接：最大特点是：一张表看做两张表。自己连接自己

案例:找出每个员工的上级领导，要求显示员工名和对应的领导名

select -> a.ename as ‘员工名‘,b.ename as ‘领导名‘ -> from -> emp a -> join -> emp b -> on -> a.mgr = b.empno -> ;

+--------+-------+
| 员工名 | 领导名 |
+--------+-------+
| SMITH  | FORD  |
| ALLEN  | BLAKE |
| WARD   | BLAKE |
| JONES  | KING  |
| MARTIN | BLAKE |
| BLAKE  | KING  |
| CLARK  | KING  |
| SCOTT  | JONES |
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+

2.8外连接？

什么是内连接，和外连接有什么区别？

内连接：假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。

AB两张表没有主副之分，两张表是平等的。

外连接：使用外连接的话，AB两张表中有一张是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出Null与之匹配。

外连接的分类？

左外连接（左连接）：表示左边的表是主表

右外连接（右连接）：表示右边的表示主表

select

a.ename ‘员工名‘，b.ename ‘领导名‘

from

emp a

left outer join //outer 也可以省略 区分内外连接是 left 和right 有这俩的是外连接

emp b

on

a.mgr = b.empno;

外连接的最重要特点是：主表的数据无条件的全部查出来

3 子查询

3.1什么是子查询？子查询都可以出现在哪里？

select语句中嵌套的select语句，被嵌套的select语句是子查询。

子查询语句可以出现在哪里？

select

.. (select)

from

.. (select)

where

.. (select)

3.2 where 子句中使用子查询

案例：找出高于平均薪资的员工信息。

第一步：找出平均薪资

select avg(sal) from emp;

+-------------+
| avg(sal)    |
+-------------+
| 2073.214286 |
+-------------+

第二步：where过滤

select * from emp where sal >2073.213286；

+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+-----------+------+------------+---------+------+--------+

第一步和第二步合并；

select * from emp where sal > (select avg(sal) from emp);

+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+-----------+------+------------+---------+------+--------+

3.3 from后面嵌套子查询

案例：找出每个部门的平均的薪资等级。

第一步：找出每个部门的平均薪水（按照部门编号分组，求sal的平均值）

select depteno，avg(sal) as avgsal from emp group by deptno;

+--------+-------------+
| deptno | avgsal      |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+

第二步：将以上的查询结果当做临时表t。让t表和salgrade s连接，条件是：t.avgsal between s.losal and s.hisal

select

t .*, s .grade

from

(select deptno,avg(sal) as avgsal from emp group by deptno) t

join

salgrade s

on

t.avgsal between s.losal and s.hisal;

+--------+-------------+-------+
| deptno | avgsal      | grade |
+--------+-------------+-------+
|     30 | 1566.666667 |     3 |
|     10 | 2916.666667 |     4 |
|     20 | 2175.000000 |     4 |
+--------+-------------+-------+

3.4 union(可以将查询结果集相加)

案例：找出工作岗位是SALESMAN和MANAGER的员工？

第一种：select ename，job from emp where job = ‘MANAGER’ or job = ‘SALESMAN’；

第二种：select ename，job from emp where job in（’SALESMAN‘ ，’MANAGER‘）；

第三种：select ename，job from emp where job = ‘MANAGER’

union

select dname，job from emp where job = ‘SALESMAN’;

在使用union的时候 如果两个表的话，列数必须相同

4 limit（重点中的重点，以后分页都靠他）

4.1 limit是mysql特有的，其他数据库中没有，不通用。（oracle中有一个相同机制的叫做rownum）

5.2 limit取结果集中的部分数据，这是他的作用。

5.3语法机制：

limit startindex，length

startindex 表示起始位置，从0开始，0表示第一条数据。

length 表示取几个

案例：取出工资钱5名的员工（思路：降序取前5个）

select ename，sal from emp order by sal desc；排序

取前5个：

select ename，sal from emp order by sal desc limit 0,5；

select ename，sal from emp order by sal desc limit 5；

5.4 limit是sql语句最后执行的一个环节：

select 5

...

from 1

...

where 2

...

group by 3

...

having 4

...

order by 6

...

limit 7

5.5 案例：找出工资排名在第4到第9名的员工？

select ename,sal from emp order by sal desc limit 3,6；

+--------+---------+
| ename  | sal     |
+--------+---------+
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
+--------+---------+

5.6 通用的标准分页sql？

每页显示3条记录：

第1页：0,3

第2页：3,6

第3页：6,9

每页显示pagesize条记录：

第pageno页：（Pageno - 1）*pagesize，pagesize

pagesize是什么？每页显示多少条记录

pageno是什么？显示第几页

4、条件查询02

原文：https://www.cnblogs.com/wn-888/p/11649041.html