Day35-SQL权威编程指南-1-同乐学堂

前言

很多人跟小编吐槽，在了解MySQL的高级特性之后，比如架构设计、备份与复制、分析查询、智能监控、故障处理等等，对SQL语句的编写还是比较难上手!

对编写稍微复杂的SQL查询就比较心虚，这样的童鞋肯定不占少数！

从开发的角度来讲，数据库只是为程序存储数据，不需数据库来进行任何复杂计算，如果利用数据库进行复杂计算，这样会非常影响数据库的性能。

但是对于其他职业，例如BI工程、测试工程师、ETL工程师、还有近几年的新兴职业：叫什么数据工程师，会玩数据，处理数据，对数据非常感兴趣，鼻子非常灵敏的职业：统称数据工程师！

如果老板要数据非常急，我们利用BI工具，ETL工具，甚至编写java代码、C代码、甚至比较简洁的Python代码，这都是从思想上不对的！

如果您的一定规模的数据是存储在数据库中：

小编的建议是：能尽量SQL就SQL，不能SQL，就BI，BI不行就ETL上。ETL不行在写代码！

（如果有比较急、和实时性比价高的数据报表请求，一定要在读权重非常低的从库上进行！ ）

我给大家总结一个道理：在数据最近的地方处理数据，是最省成本、最省时间的的最佳方案。

下面使用案例中的表结构

dept

create table dept(deptno int primary key,  dname varchar(30),loc varchar(30))engine=InnoDB;

insert into dept values(10,'accounting','new york');
insert into dept values(20,'research','dallas');
insert into dept values(30,'sales','chicago');
insert into dept values(40,'operations','boston');

emp

create table emp(empno int primary key,ename varchar(30),job varchar(30),mgr int, hiredate datetime, sal numeric(10,2), comm numeric(10,2),  deptno int,  foreign key (deptno) references dept(deptno) on delete cascade)engine=innoDB;

insert into emp(empno,ename,job,mgr,hiredate,sal,deptno) values (7934,'miller','clerk',7782,'1982-1-23',1300.00,10);
insert into emp(empno,ename,job,mgr,hiredate,sal,deptno) values (7902,'ford','analyst',7566,'1981-12-3',3000.00,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,deptno) values (7900,'james','clerk',7698,'1981-12-3',950.00,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,deptno) values (7876,'adams','clerk',7788,'1987-5-23',1100.00,20);
insert into emp values (7844,'turner','salsman',7698,'1982-9-8',1500.00,0.00,30);
insert into emp(empno,ename,job,hiredate,sal,deptno) values (7839,'king','president','1981-11-17',5000.00,10);
insert into emp(empno,ename,job,mgr,hiredate,sal,deptno) values(7788,'scott','analyst',7566,'1987-4-19',3000.00,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,deptno) values (7782,'clark','manager',7839,'1981-6-9',2450.00,10);
insert into emp(empno,ename,job,mgr,hiredate,sal,deptno) values(7698,'blake','manager',7839,'1981-5-1',2850.00,30);
insert into emp values (7654,'martin','salsman',7698,'1981-9-28',1250.00,1400.00,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,deptno) values(7566,'jones','manager',7839,'1981-4-2',2975.00,20);
insert into emp values(7521,'ward','salesman',7698,'1981-2-22',1250.00,500.00,30);
insert into emp values (7499,'allen','salseman',7698,'1981-2-20',1600.00,300.00,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values(7369,'smith','clerk',7902,'1980-12-27',800.00,20);

一、多条件查询

1、使用WHERE子句和OR和子句。例如，如果你想找部门（10）的所有员工，以及任何获得佣金的员工，以及部门20中工资不超过2000美元的员工。

        1 select *
        2  from emp
        3  where deptno = 10
        4    or comm is not null
        5    or sal <= 2000 and deptno=20

解说：WHERE 子句查找满足下列条件的数据行：DEPTNO是10，或 COMM为NULL或DEPTNO 为20的工资为2000美元以下的员工。

2、连接列值


select ename, job from emp where deptno = 10

ENAME      JOB
---------   ---------
CLARK      MANAGER
KING       PRESIDENT
MILLER     CLERK

mysql concat(str1,str2,...)

返回结果为连接参数产生的字符串，如果任何一个参数带有null，则返回结果为null.如果所有参数均为非二进制字符串，则结果为非二进制字符串。如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。

一个数字参数被转化为与之相等的二进制字符串格式；若要避免这种情况，可使用显式类型 cast, 例如： SELECT CONCAT(CAST(int_col AS CHAR), char_col);

select concat(ename, ' WORKS AS A ',job ) as msg from emp where  deptno =10;

+---------------------------+
| msg                       |
+---------------------------+
| CLARK WORKS AS A MANAGER  |
| KING WORKS AS A PRESIDENT |
| MILLER WORKS AS A CLERK   |
+---------------------------+
3 rows in set

3、多结果集进行if-else操作。

您希望在SELECT语句中对值执行if-else操作。例如，您希望生成一个结果集，如果员工的工资为2000美元或更少，则返回“低工资”的消息，如果员工的工资是4000美元或更多，则返回一个“多付”的信息，如果他们在两者之间工资，则返回“OK”。结果集应该是这样的:

    1 select ename,sal,
        2        case when sal <= 2000 then 'UNDERPAID'
        3             when sal >= 4000 then 'OVERPAID'
        4             else 'OK'
        5        end as status
        6   from emp

        ENAME             SAL  STATUS
        ---------- ----------  ---------
        SMITH             800  UNDERPAID
        ALLEN            1600  UNDERPAID
        WARD             1250  UNDERPAID
        JONES            2975  OK
        MARTIN           1250  UNDERPAID
        BLAKE            2850  OK
        CLARK            2450  OK
        SCOTT            3000  OK
        KING             5000  OVERPAID
        TURNER           1500  UNDERPAID
        ADAMS            1100  UNDERPAID
        JAMES             950  UNDERPAID
        FORD            3000   OK
        MILLER          1300   UNDERPAID

4、空值处理

查找空值

1 select *

        2  from emp

        3  where comm is null

将空值转换为实际值

（只要comm非空就返回comm的值，否则返回0）

1 select coalesce(comm,0)
2 from emp

5、按模式搜索

在部门10和20中需要返回一个名字中带I 或者职位中带“ER”的员工。

1 select ename, job
        2   from emp
        3  where deptno in (10,20)
        4    and (ename like '%I%' or job like '%ER')

6、排序

您要排序EMP的行首先由DEPTNO升序，然后按薪水降序排序。您要返回以下结果集：

ORDER BY子句中不同的排序列，用逗号分隔：

1 select empno,deptno,sal,ename,job
   2  from emp
   3  order by deptno, sal desc

        EMPNO      DEPTNO        SAL  ENAME      JOB
        ----------  ----------  ----------  ----------  ---------
              7839          10        5000  KING        PRESIDENT
              7782          10        2450  CLARK      MANAGER
              7934          10        1300  MILLER      CLERK
              7788          20        3000  SCOTT      ANALYST
              7902          20        3000  FORD        ANALYST
              7566          20        2975  JONES      MANAGER
              7876          20        1100  ADAMS      CLERK
              7369          20        800  SMITH      CLERK
              7698          30        2850  BLAKE      MANAGER
              7499          30        1600  ALLEN      SALESMAN
              7844          30        1500  TURNER      SALESMAN
              7521          30        1250  WARD        SALESMAN
              7654          30        1250  MARTIN      SALESMAN
              7900          30        950  JAMES      CLERK

您想按字符串的特定部分对查询的结果进行排序。

例如，要从表EMP返回员工名称和作业，并按作业字段中最后两个字符进行排序。结果集应如下所示：

select ename,job

          from emp

        order by substr(job,length(job)-2)

如果对排序感兴趣的可以自行查阅：mysql的校队规则等！

6.1、对字母和数字混合的数据排序

1、首先创建个视图

create view V
        as
        select concat(ename,' ',deptno) as data
          from emp

select * from V

        DATA
        -------------
        SMITH 20
        ALLEN 30
        WARD 30
        JONES 20
        MARTIN 30
        BLAKE 30
        CLARK 10
        SCOTT 20
        KING 10
        TURNER 30
        ADAMS 20
        JAMES 30
        FORD 20
        MILLER 10

2、进行排序

select data,
              replace(data,replace( (data,'0123456789','##########'),'#',''),'') nums,
          replace(

            translate(data,'0123456789','##########'),'#','') chars


       from V


        DATA        NUMS  CHARS

        ------------ ------ ----------

        SMITH 20    20    SMITH

        ALLEN 30    30    ALLEN

        WARD 30      30    WARD

        JONES 20    20    JONES

        MARTIN 30    30    MARTIN

        BLAKE 30    30    BLAKE

        CLARK 10    10    CLARK

        SCOTT 20    20    SCOTT

        KING 10      10    KING

        TURNER 30    30    TURNER

        ADAMS 20    20    ADAMS

        JAMES 30    30    JAMES

        FORD 20      20    FORD

        MILLER 10    10    MILLER

上述是Oracle的排序方案！

mysql中还没特别好的方案，有一些BI工具把一个字段拆分成两个字段，然后在进行排序！

如果是单个varchar字段里面是数值，可以通过下方案例。

今天遇到一个问题，就是对mysql数值字符串类型进行排序，在默认情况下使用order by 字段名称 desc/asc 进行排序的时候，mysql进行的排序规则是按照ASCII码进行排序的，并不会自动的识别出这些数据是数值，百度了一下，发现对于这种情况，可以采用

order by 字段名称+0 desc/asc的形式进行排序(亲测有效)

order by 字段名称*1 desc/asc的形式进行排序( 没有进行测试 )

这样mysql会将数值字符串类型的数据当作数值进行处理，但是存在效率问题，不推荐使用varchar/char类型来存储数值，这样会带来不不必要的问题。

6.2 处理排序空值

您想通过COMM对EMP的结果进行排序，但该字段为空。您需要一种方式来指定是否为最后排序null：

当值为NULL时，使用CASE表达式来“标记”。

这个想法是拥有一个带有两个值的标志：一个表示NULL，另一个表示非NULL。一旦这样做，只需将该标志列添加到ORDER BY子句。您可以轻松地控制NULL值是先排序还是最后排序，而不会影响非NULL值：

1  select ename,sal,comm

        2    from (

        3  select ename,sal,comm,

        4        case when comm is null then 0 else 1 end as is_null

        5    from emp

        6        ) x

        7    order by is_null desc,comm asc

6.3、条件排序

你想根据一些条件逻辑进行排序。例如：如果JOB是“SALESMAN”，你想对COMM进行排序; 否则，您想按SAL排序。您要返回以下结果集：

 select ename,sal,job,comm
          from emp
          order by case when job LIKE 'sal%' then comm else sal end

二、多表操作

2.1、例如，要在表EMP中显示部门10中的员工的姓名和部门编号，以及表DEPT中每个部门的名称和部门编号。您希望结果集如下所示：

ENAME_AND_DNAME      DEPTNO
---------------  ----------
CLARK                    10
 KING                    10
MILLER                  10
       
----------
ACCOUNTING              10
RESEARCH                20
SALES                    30
OPERATIONS              40


1  select ename as ename_and_dname, deptno
        2    from emp
        3  where deptno = 10
        4  union all

        5  union all
        6  select dname, deptno
        7    from dept

2.2 查找两个表中的共同行

您希望返回与视图V中的行匹配的EMP中所有员工的EMPNO，ENAME，JOB，SAL和DEPTNO。

create view V
        as
        select ename,job,sal
          from emp
        where job = 'CLERK'

        select * from V

        ENAME      JOB              SAL
        ----------  --------- ----------
        SMITH      CLERK            800
        ADAMS      CLERK          1100
        JAMES      CLERK            950
        MILLER      CLERK          1300

两种方法：

第一种：

1 select e.empno,e.ename,e.job,e.sal,e.deptno
        2  from emp e, V
        3  where e.ename = v.ename
        4    and e.job  = v.job
        5    and e.sal  = v.sal

第二种：

1 select e.empno,e.ename,e.job,e.sal,e.deptno
        2  from emp e join V
        3    on (    e.ename  = v.ename
        4        and e.job    = v.job
        5        and e.sal    = v.sal )

结果

EMPNO  ENAME      JOB            SAL    DEPTNO
        --------  ----------  --------- ---------- ---------
          7369  SMITH      CLERK          800        20
          7876  ADAMS      CLERK          1100        20
          7900  JAMES      CLERK          950        30
          7934  MILLER      CLERK          1300        10

2.3 从一个表中查找另一个表中没有的值

从dept的deptno中查找emp表中不存在的值:

1 select deptno
        2  from dept
        3  where deptno not in (select deptno from emp)

如果 deptno字段有空值。则返回结果中就会为空的记录。

如果要解决使用IN 的逻辑的语法并且与null相关联出现的异常：可以用 not exists 来代替，解决表中有空值的问题！

select d.deptno
          from dept d
        where not exists ( select null
                              from emp e
                            where d.deptno = e.deptno )

2.4 从一个表中查找与其他表不匹配的记录

1 select d.*
        2  from dept d left outer join emp e
        3    on (d.deptno = e.deptno)
        4  where e.deptno is null

2.5、向查询中增加联接而不影响其他链接

您有一个查询返回您想要的结果。您需要其他信息，但是当尝试获取它时，您将从原始结果集中丢失数据。

例如，您要返回所有员工，他们工作的部门的位置以及他们收到奖金的日期。对于此问题，EMP_BONUS表包含以下数据：

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for emp_bonus
-- ----------------------------
DROP TABLE IF EXISTS `emp_bonus`;
CREATE TABLE `emp_bonus` (
  `EMPNO` int(11) DEFAULT NULL,
  `RECEIVED` datetime DEFAULT NULL,
  `TYPE` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of emp_bonus
-- ----------------------------
INSERT INTO `emp_bonus` VALUES ('7369', '2015-03-14 23:26:43', '1');
INSERT INTO `emp_bonus` VALUES ('7900', '2015-03-24 23:27:10', '2');
INSERT INTO `emp_bonus` VALUES ('7788', '2015-03-21 23:27:30', '3');

select * from emp_bonus

          EMPNO  RECEIVED          TYPE
     ----------  ----------- ----------
           7369  14-MAR-2005          1
           7900  14-MAR-2005          2
           7788  14-MAR-2005          3

您开始的查询如下所示：

select e.ename, d.loc
      from emp e, dept d
     where e.deptno=d.deptno

      ENAME      LOC
      ---------- -------------
      SMITH      DALLAS
      ALLEN      CHICAGO
      WARD       CHICAGO
      JONES      DALLAS
      MARTIN     CHICAGO
      BLAKE      CHICAGO
      CLARK      NEW YORK
      SCOTT      DALLAS
      KING       NEW YORK
      TURNER     CHICAGO
      ADAMS      DALLAS
      JAMES      CHICAGO
      FORD       DALLAS
      MILLER     NEW YORK

您想向这些结果添加奖金给员工的日期，但是加入EMP_BONUS表会返回更少的行数，因为并不是每个员工都有一个奖金：

select e.ename, d.loc,eb.received
      from emp e, dept d, emp_bonus eb
     where e.deptno=d.deptno
       and e.empno=eb.empno

  ENAME       LOC           RECEIVED
    ----------  ------------- -----------
    SCOTT       DALLAS        14-MAR-2005
    SMITH       DALLAS        14-MAR-2005
    JAMES       CHICAGO       14-MAR-2005

您想要的结果集如下：

  ENAME       LOC            RECEIVED
    ----------  -------------  -----------
    ALLEN       CHICAGO
    WARD        CHICAGO
    MARTIN      CHICAGO
    JAMES       CHICAGO        14-MAR-2005
    TURNER      CHICAGO
    BLAKE       CHICAGO
    SMITH       DALLAS         14-MAR-2005
    FORD        DALLAS
    ADAMS       DALLAS
    JONES       DALLAS
    SCOTT       DALLAS         14-MAR-2005
    CLARK       NEW YORK
    KING        NEW YORK
    MILLER      NEW YORK

您可以使用外连接获取附加信息，而不会丢失原始查询中的数据。首先连接表EMP到表DEPT以获得所有员工和他们工作的部门的位置，然后外部加入表EMP_ BONUS返回奖金的日期，如果有的话.

1 select e.ename, d.loc, eb.received
    2   from emp e join dept d
    3     on (e.deptno=d.deptno)
    4   left join emp_bonus eb
    5     on (e.empno=eb.empno)
    6  order by 2

您还可以使用标量子查询（位于SELECT列表中的子查询）来模拟外连接：

1 select e.ename, d.loc,
        2        (select eb.received from emp_bonus eb
        3          where eb.empno=e.empno) as received
        4  from emp e, dept d
        5  where e.deptno=d.deptno
        6  order by 2

2.5、检测两个表中是否有相同的值。

create view V
        as
        select * from emp where deptno != 10
        union all
        select * from emp where ename = 'WARD'

        select * from V

        EMPNO ENAME      JOB        MGR  HIREDATE      SAL  COMM DEPTNO
        ----- ----------  --------- ----- ----------- ----- ----- ------
        7369 SMITH      CLERK      7902 17-DEC-1980  800          20
        7499 ALLEN      SALESMAN  7698 20-FEB-1981  1600  300    30
        7521 WARD        SALESMAN  7698 22-FEB-1981  1250  500    30
        7566 JONES      MANAGER    7839 02-APR-1981  2975          20
        7654 MARTIN      SALESMAN  7698 28-SEP-1981  1250  1300    30
        7698 BLAKE      MANAGER    7839 01-MAY-1981  2850          30
        7788 SCOTT      ANALYST    7566 09-DEC-1982  3000          20
        7844 TURNER      SALESMAN  7698 08-SEP-1981  1500    0    30
        7876 ADAMS      CLERK      7788 12-JAN-1983  1100          20
        7900 JAMES      CLERK      7698 03-DEC-1981  950          30
        7902 FORD        ANALYST    7566 03-DEC-1981  3000          20
        7521 WARD        SALESMAN  7698 22-FEB-1981  1250  500    30

查找方案：

在E中找出V中没有的行： ：

  select *
             from (
           select e.empno,e.ename,e.job,e.mgr,e.hiredate,
                 e.sal,e.comm,e.deptno, count(*) as cnt
             from emp e
           group by empno,ename,job,mgr,hiredate,
                   sal,comm,deptno
                 ) e
           where not exists (
          select null
            from (
          select v.empno,v.ename,v.job,v.mgr,v.hiredate,
                v.sal,v.comm,v.deptno, count(*) as cnt
            from v
          group by empno,ename,job,mgr,hiredate,
                    sal,comm,deptno
                ) v
            where v.empno    = e.empno
              and v.ename    = e.ename
              and v.job      = e.job
              and v.mgr      = e.mgr
              and v.hiredate = e.hiredate
              and v.sal      = e.sal
              and v.deptno  = e.deptno
              and v.cnt      = e.cnt
              and coalesce(v.comm,0) = coalesce(e.comm,0)
          )
            union all
           select *
            from (
            select v.empno,v.ename,v.job,v.mgr,v.hiredate,
                  v.sal,v.comm,v.deptno, count(*) as cnt
              from v
            group by empno,ename,job,mgr,hiredate,
                    sal,comm,deptno
                  ) v
            where not exists (
           select null
             from (
           select e.empno,e.ename,e.job,e.mgr,e.hiredate,
                  e.sal,e.comm,e.deptno, count(*) as cnt
            from emp e
           group by empno,ename,job,mgr,hiredate,
                   sal,comm,deptno
                  ) e
           where v.empno    = e.empno
              and v.ename    = e.ename
             and v.job      = e.job
            and v.mgr      = e.mgr
              and v.hiredate  = e.hiredate
              and v.sal      = e.sal
             and v.deptno    = e.deptno
              and v.cnt      = e.cnt
             and coalesce(v.comm,0) = coalesce(e.comm,0)
          )

首先，在表EMP中查找视图不存在的行。

然后将这些行与表EMP中不存在的视图V中的行结合（UNION ALL）

在V中找出E中没有的行：

select *
          from (
        select v.empno,v.ename,v.job,v.mgr,v.hiredate,
              v.sal,v.comm,v.deptno, count(*) as cnt
          from v
        group by empno,ename,job,mgr,hiredate,
                  sal,comm,deptno
              ) v
          where not exists (
        select null
          from (
        select e.empno,e.ename,e.job,e.mgr,e.hiredate,
                e.sal,e.comm,e.deptno, count(*) as cnt
          from emp e
          group by empno,ename,job,mgr,hiredate,
                  sal,comm,deptno
              ) e
          where v.empno    = e.empno
            and v.ename    = e.ename
            and v.job      = e.job
            and v.mgr      = e.mgr
            and v.hiredate = e.hiredate
            and v.sal      = e.sal
            and v.deptno  = e.deptno
            and v.cnt      = e.cnt
            and coalesce(v.comm,0) = coalesce(e.comm,0)
        )

打赏

Day35-SQL权威编程指南-1

相关推荐

特别的技术，给特别的你！

觉得文章有用就打赏一下文章作者

非常感谢你的打赏，我们将继续给力更多优质内容，让我们一起创建更加美好的网络世界！

支付宝扫一扫打赏

微信扫一扫打赏