一、CASE表达式

1、CASE表达式概述

case表达式有简单case表达式和搜索case表达式两种写法

-- 简单case表达式

case sex

        when '1'  then '男'

        when '0'  then '女'

else '其他' end

-- 搜索case表达式

case when  sex='1'  then  '男'

         when  sex='1'  then  '男'

else '其他' end 

        这两种写法执行的结果都是一样的，sex列为1时，结果为男；sex列为0时，结果为女。
        我们在编写SQL时需要注意，在发现为真的when子句时，case表达式的真假判断就会终止，剩余的when子句会被忽略。        

        注意事项：

1、同一返回的数据类型
        各分支case表达式里各个分支返回的数据类型是否一致

2、不要忘记end

3、养成写else子句的习惯
        虽然else作为可选，但是不写else时case会返回null。可能会造成语法没错，但是结果有
        出入的情况。
       

2、将已有编号方式转换为新的方式并统计

        在进行非定制统计时，我们经常会遇见将已有编号方式转换为另外一种便于分析的方式并进行统计的需求。
        例子：现在有各个城市的人口数据(城市名、人口数)，现在需要按照这些城市的省份进行统计（省名、人口数）。

select 
    (case city
        when '成都' then '四川'
        when '乐山' then '四川'
        when '万宁' then '海南'
        when '陵水' then '海南'
        when '南宁' then '广西'
        when '北海' then '广西'
    else '未知' end) as "province",
    sum(num) as num
from tb_city
group by 
    case city
        when '成都' then '四川'
        when '乐山' then '四川'
        when '万宁' then '海南'
        when '陵水' then '海南'
        when '南宁' then '广西'
        when '北海' then '广西'
    else '未知' end

    

在上方示例中可以将group by后方的case子句使用province替换。但是严格来说这种写法时违规的，因为group by子句比select语句先执行，所以在group by子句中引用select子句定义的别称是不被允许的。Oracle、DB2、SQL server不支持这种写法，MySQL支持。

3、用一条SQL语句进行不同的条件统计

        例子：现在有一张表(城市名称、性别、人数)，需要按照城市统计男女数量（城市、男人数、女人数）。

select 
    city,

    sum(
        case sex 
            when '1' then num
        else 0 end
    ) as man,

    sum(
        case sex 
            when '0' then num
        else 0 end
    ) as wuman,

from tb_city
group by city
   

        这个技巧可贵的地方在于，他能将SQL的查询结果转换为二维表的格式。

4、update语句里进行条件分支

        例子1：工资为30万以上的人降薪10%，25万到27万增加20%。

        处理方式：两条SQL

update table set pay=pay*0.9
where pay > 30

update table set pay=pay*120
where pay > 25 and pay <27

        结果：这样执行可能会造成数据处理错误，比如一个人的薪资在30万以上，执行第一条SQL后，他的薪资降为了25-27之间，在执行第二次的时候会给此条数据增加20%。这种结果是不想得到的。所以在处理的时候需要同时进行更新

        正确方式：

update table
set pay=(
    case 
    when pat>30 then pay*0.9
    when pay<27 and pay >25 then pay * 120
    else pay end
)

        这种方式的应用很广，有时需要调换两条记录的值时可以使用这种方式。
        例子2：俩条数据主键为1、2。此时想调换这两条数据的主键。

        一般方式：使用第三个作为零时存储的地方

        使用case：

update table
set id=(case 
    when id=1 then 2
    when id=2 then 1
    else id end
)
where id in(1,2)

5、表之间的数据匹配

        在case表达式里，可以使用between、like和<、>等组合，以及能嵌套子查询的in和exists。
        例子：课程表（id，name）；课程时间表（moth，class_id）。现在想查询课程在时间上的分布情况，查询结果：name、1月、2月、3月。。。       

-- in方法
select name,
    (case 
        when id in(select class_id from 课程时间表 where moth="6")
        then "√" 
        else "" end ) as "6月",
    (case 
        when id in(select class_id from 课程时间表 where moth="7")
        then "√" 
        else "" end ) as "7月",....
form 课程表

-- exists方法
select name,
    (case 
        when exists(select class_id from 课程时间表 where moth="6")
        then "√" 
        else "" end ) as "6月",
    (case 
        when exists(select class_id from 课程时间表 where moth="7")
        then "√" 
        else "" end ) as "7月",....
form 课程表

        结果集类似：

        无论是in还是exists，他们的结果都是一样的，但是从性能方面来说，exists更好，通过exists进行的子查询能够用到moth，class_id这样的主键索引，尤其是在课程时间表的数据比较多时。

6、在case表达式中使用聚合函数

        例子：有一张表StudentClub，这张表的主键是“学号、社团DI”，存储了学生和社团多对多的关系。

        有的学生同时加入了多个社团，有的学生只加入了一个社团，对于加入了多个社团的学生，在主社团标志中，以Y来表示主社团；对于只有一个的学生以N表示主社团。
        需求：获取只加入一个社团的学生的社团ID，获取加入了多个社团的主社团ID。

select std_id,
    (case 
        when count(*) =1 then max(club_id)
        when count(*)>1 then  
            max(case when main_club_flg="Y" then club_id else null end )
        else max(club_id) end) as aaa

from StudentClub
group by std_id
    

二、自链接的用法

1、可重排序、排列、组合

        现在又一张表：products（name 名字，price 价格）       

        a、通过自联结生成结果集（笛卡尔积）

select a.name,b.name
from products a,products b

        b、针对上方的结果集进行过滤，过滤name1==name2的数据行。

select a.name,b.name
from products a,products b
where a.name<>b.name

        c、对b中的例子中再进行数据过滤，过滤重复的组合（苹果，橘子：橘子：苹果）

select a.name,b.name
from products a,products b
where a.name>b.name

2、查找局部不一致的列

        表数据如下：

        需求：使用自连接查出表中有相同价格的数据记录。

select distinct a.name,a.price
from products a,products b
where a.price =b.price 
and a.name <> b.name 

          SQL中的distinct不添加的话会有重复数据，类似2.1.b中的示例。

3、排序

        还是products表，按照水果的价格来排序，并显示排序的名次

select name,price,
	(select count(p.name) from products p
	where p.price >a.price)+1 as ranks	
from products a
order by price desc

        解释：ranks子句：计算a表中在p表中，比a表价格大的数据条数，+1是因为计算的数据条数从0开始（比最高价格还要高的记录为0）。

        扩展：在子句中添加distinct，那么存在相同次的记录时，就可以跳过之后的次位置

select name,price,
	(select count(distinct  p.name) from products p
	where (p.price+0) >(a.price+0))+1 as ranks	
from products a
order by (price+0) desc 

        扩展：去重所有价格，计算出价格比自己高的记录，保存比自己高的记录数量作为名次。求出下列结果集。

select a.name,
       max(a.price) as name ,
       count(b.name)+1 as ranke
from product a left outer join product b
on a.price<b.price
group by a.name
order by ranke

        解释：自链接的表a作为主表，b作为子表，其数据为小于a表中记录的所有记录。
                此处用的是外连接，非内连接。如果是内连接的话会使最大的那个价格查不到比自己更
                高的价格，从而导致在结果集中没有此行数据。

三、三值逻辑和NULL

        普通语言里的布尔型只有true和false两个值，这种逻辑体系被称为二值逻辑，在SQL语言里面，除此2之外还有第三个值unknown，因此这种逻辑体系被称为三值逻辑。

        为什么必须写成“is null”，而不是“=null”
        对于NULL使用比较运算得到的结果都是unknown。而查询结果只会包含where字句的判断结果为true的执行，不会包含判断结果为false和unknown的行。不只是等号，对NULL使用其他比较谓词，结果也都一样。
        NULL既不是值也不是变量。NULL只是表示一个“没有值”的标记。

        unknown、第三个值
        注意：（真值unknown） 和 （作为NULL的一种UNKNOW） 是不同的东西。
        unknown是明确的布尔型的真值，NULL既不是值也不是变量。

        在运算符中的优先级情况

1、比较谓词和NULL：排中律不成立

        排中律：“约翰是20岁，或者不是20岁，二者必居其一。”
        对于这种就是真命题，而在SQL中此类并不成立。

        如果SQL中排中律成立，下列SQL则会查出表中所有数据，但是实际上约翰的那条数据并不会被查出来。

select name,age
from students
where age =20 or age <>20

        在上列理论知识中，null比较运算的结果是unknown，所以需要查出约翰则sql需要改写为下列格式

select name
from students
where age=20 or age <>20 or age is null

2、比较谓词和NULL：CASE表达式和NULL

        例子：在sex为1时返回男，其他返回女       

case sex 
    when 1 then "男"
    when null then "女"
end

        上列例子中，结果永远不会返回女，因为when null等价于=null，所以他的判断结果永远为unknown。

        正确的写法为下面这种：

case 
    when sex =1 then "男"
    when sex is null then "女"
end

3、NOT IN和NOT EXISTS是不等价的

        例子：查询表A中年龄不在表B中的数据，且表B中的age可能为NULL

select name,age
from A 
where age not in(select age from B )

上列SQL是不能查出数据的，因为B中的age可能会存在null的现象，就会得出一下过程(最后部分)

        此时可以使用exists来改写

select name,age
from A 
where age not exists(select age from B)

        下列为SQL执行过程

4、限定谓词和NULL

        SQL里面有ALL和ANY两个限定谓词。因为ANY与IN是等价的，所以我们不经常使用。
        ALL可以和比较谓词一起使用，用来表达“与所有的XX都相等”或“比所有的XX都大”的意思。       

select name,age
from A 
where age > all(select age from B)

        上列SQL的意思是查询A表中age大于B表中的所有age的记录。

        但是上列SQL存在一个问题，如果B中的age存在NULL就会导致查不出数据。详细的过程：

5、限定谓词和极值函数不是等价的

        重写4中的列子

select name,age
from A 
where age > (select max(age) from B )

    如果此时B表中的age存在NULL，但不全是NULL，则极值函数会排除NULL的值。
    如果B表中没有存在记录，则此时的极值函数也会返回NULL。

    为NULL的情况：那一列的值全为NULL、没有记录

6、聚合函数和NUL      

        当输入为空表时返回NULL的不只是极值函数，count以外的聚合函数也是如此。

四、HAVING子句的力量

1、用having子句进行子查询：求众数

        例子：求出此表中收入的平均工资。

        使用SQL计算出平均值为55000。从这个例子中可以看出，简单的求平均值有一个缺点。那就是容易受到离群值的影响。这种时候就需要使用更能准确反映出群体群体趋势的指标——众数。它是指群体中出现次数最多的值。

SELECT income,count(*)
from Graduates
group by income 
having count(*) >=all 
	(SELECT count(*) 
	from Graduates
	group by income  )

        上列SQL中计算出了出现次数最多的工资。但是会有一个问题，如果工资列有NULL，则结果为空(3节中提到的)。所以可以使用极值函数来优化上方SQL。

SELECT income,count(*)
from Graduates
group by income 
having count(*) >=(SELECT max(cut) 
	from
	(SELECT count(*) as cut
	from Graduates
	group by income  ))

2、用HAVING子句进行自连接：求中位数

        当平均值不可信时，与众数一样经常被用到的另一个指标是中位数。它指的是讲集合中的元素按照升序排序后恰好位于正中间的元素。如果元素个数为偶数则取两个偶数的平均值。
        做法：将集合里的元素按照大小分为上半部分和下半部分两个子集，同时让这2个子集共同拥有集合正中间的元素。

SELECT AVG(income)
from 
	(SELECT a.income
	from Graduates a,Graduates b
	group by income 
	having 
		sum(case when a.income>b.income then 1 else 0 end)>=COUNT(*)/2
		and sum(case when a.income<b.income then 1 else 0 end)>=COUNT(*)/2)c

        解释：以a中的income字段分组，统计每种工资大于其他的个数大于自己个数一半且每种工资小于其他工资个数大于自己个数的一半。     
        SQL中要点在于比较条件“>= count(*)/2”里面的等号，这个是故意加上的。加上等号并不是为了清晰的分开子集a、b，而是为了两个子集拥有共同部分。如果去掉等号，将条件改成“>count(*)/2”，那么当元素为偶数个时，a、b就没有共同元素了，也就无法求出中位数了。
        如果事先知道集合的元素个数是奇数，那么因为from子句里的子查询结果只有一条数据，所以外层的AVG函数可以去掉。但是如果要写出更通用的SQL语句，AVG函数还是需要的。

3、查询不包含NULL的集合

        count函数的使用方法有count(*)和count(列名)两种，他们的区别另有两个：第一个是性能上的区别：第二个是count(*)可以用于NULL，而count(列名)与其他聚合函数一样，要先排除NULL的行在进行统计。第二个区别也可以这么理解：count(*)查询的是所有行的数目，而count(列名)查询的则不一定。

        下列是一个空表：使用count(*)和count(col_1)进行统计

        

        统计结果为

        我们可以通过利用这个特性查询一些特殊数据。下列表中，其中提交日期有些为空，需要查出学院全部有提交日期的学院。

        如果我们直接使用where sbmt_date is not null 就会把“文学院”包含进来，因为文学院有null也有数据。此时就可以使用count()函数的特性进行查询。

SELECT dpt
from students
group by dpt
having count(*)=count(sbmt_date)

        也可以使用case进行改变

SELECT dpt
from students
group by dpt
having count(*)=(case when sbmt_date is null then 0 else 1 end )

4、用关系除法运算进行购物篮分析

        假设有两张表：商品表items，各个店铺的库存管理表shopItems。

        这次我们要查询的是囊括了表items中所有商品的店铺。也就是结果集为仙台和东京，大版没有啤酒，所以就不是我们的目标。这个问题在实际工作中的原型是数据挖掘技术中的“购物篮分析”。
        使用where子句指定的条件只对表里的某一行数据有效。所以在这个场景下where in or等不适用。此时就可以使用having

select shop
from shopItems inner join items
on shopItems.item=items.item
group by shop
having count(shopItems.item)=(SELECT count(item) from items)

        上方例子中，为什么要连表查询，因为需要排除类似(仙台、窗帘)这种情况 。
        此时看看如何排除掉仙台(仙台店的仓库中存在“窗帘”，但是商品表里没有“窗帘”)，让结果里只出现东京店。这类问题被称为“精确关系除法”。

select shop
from shopItems left join items
on shopItems.item=items.item
group by shop
having count(shopItems.item)=(SELECT count(item) from items)
	and count(items.item)=(SELECT count(item) from items)

五、外连接的用法

1、外连接进行行列转换：制作交叉表       

        首先让我们利用上面这张表生成下面的交叉表。“⚪”表示学习过，“”表示为没有学习过。转换为下列这种。

示例代码：         

select a.name,
    case when b.name is not null then '⚪' else then null end as "SQL入门",
    case when c.name is not null then '⚪' else then null end as "UNIX基础",
    case when d.name is not null then '⚪' else then null end as "Java中级"
from (select name from courses ) a,
    left outer join 
        (select name from courses where course="SQL入门") b
    on a.name=b.name
    left outer join 
        (select name from courses where course="UNIX基础") c
    on a.name=c.name
    left outer join 
        (select name from courses where course="Java中级") d
    on a.name=d.name

        SQL中的a、b、c、d子集为：

        a为主表包含了所有的人，其余子表为个科学习的人。

        实现上列效果还有两种方式：

       方式一，直接在返回字段进行子查询。

select a.name,
    (select '⚪' from courses where course = "SQL入门" and a.name=name) as "SQL入门",
    (select '⚪' from courses where course = "UNIX基础" and a.name=name) as "UNIX基础",
    (select '⚪' from courses where course = "Java中级" and a.name=name) as "Java中级"
from (select distinct name from courses)a

        方式二，使用group by

select name,
    if(sum(if(course='SQL基础',1,0))=1,'⚪','') as "SQL基础",
    if(sum(if(course='UNIX基础',1,0))=1,'⚪','') as "UNIX基础",
    if(sum(if(course='Java中级',1,0))=1,'⚪','') as "Java中级"
from cources 
group by name 

2、用外连接进行行列转换：列转行：汇总重复项于一列

         把上图的数据结构转换为下图的数据形式。

select employee,child
from  
personnel
left join 
(select child_1 as child
from personnel 
union 
select child_2 as child
from personnel 
union 
select child_3 as child
from personnel)a
on a.child in (personnel.child_1,personnel.child_2,personnel.child_3)
order by employee

        这里的子表a为所有的孩子集，重点在于连接条件是通过in谓词指定的。

六、用关联子查询比较行与行

1、增长、减少、维持现状

        需要用到行间数据比较的具有代表性的业务场景，使用基于时间序列的表进行时间序列分析。假设有下面这样一张记录了某个公司每年的营业额的表Sales。

        通过图标可以清晰的看出每年营业额的趋势。如果用面向过程语言来解决一般就是按年份递增的顺序排列，循环的与前后两个数据做比较。但是在SQL中要获取类似的数据，和面向过程语言不一样。
        例子：求出与前一年营业额一致的数据。

select year,sale
from sales a
where sale = (select sale from sales where year=a.year-1)

        解释：select sale from sales where year=a.year-1，这句SQL是为了查出前一年的营业额

        扩展：展示每年的营业额相较于上一年的趋势。

select year,sale,
	(select if(a.sale>s.sale,"降",if(a.sale<s.sale,"涨","不变"))
	 from sales a
	 where year=s.year-1) as sss
from sales s 

        扩展：如果年份不连续时

select year,sale,
	(select if(max(a.sale)>s.sale,"降",if(max(a.sale)<s.sale,"涨","不变"))
	 from sales a
	 where year<s.year) as sss
from sales s 

2、移动累计值和移动平均值

      我们把截止到某个时间点按照时间记录的数值累加而得出来的数值称为累计值。例如下列表中去求他们的累计值，将上图查出下图结果。

        

select year,sale,
	(select sum(a.sale)
	 from sales a
	 where year<=s.year) as sss
from sales s 

        解释：上列代码只是6.1中的表，但是处理方式是一致的。

        扩展：求三行的合，比如当前行为6行，求和为4、5、6行，每行如此

        

select year,sale,
	(select sum(ab.sale)
	from (select a.sale
	 from sales a
	 where year<=s.year
	 order by `year` desc 
	 limit 3
	 )ab) as sss
from sales s 
order by `year` 

上述方式为使用limit配合order by使用。

select year,sale,
	(select sum( a.sale)
	 from sales a
	 where year<=s.year
	 	and (select count(*) from sales 
	 		 where year between a.year and s.year)<=3
	 having count(*)=3 
	 ) as sss
from sales s 
order by `year` 

 上述的是书中的示例方法：4、5行的条件就是控制只计算3行的数据，6行的条件为少于3行的不进行累计计算。4、5行代码解释：子表a的年份到主表s的年份数据个数为3个以下，然后筛选a的年份到理想的范围内。

3、查询重叠的时间区间

        假设有下面这样一张表Reservations，记录了酒店某间房的预约情况。

        显然图中具有日期重合的数据，现在需要查出这种数据。

select a.reserver,a.start_date,a.end_date
from Reservations a,Reservations b
where a.reserver<>b.reserver and 
	((a.start_date >=b.start_date and a.start_date<=b.end_date ) or 	
	(a.end_date >=b.start_date and a.end_date<=b.end_date ) )

七、用SQL进行集合运算

 1、导入篇：集合运算的几个注意事项

        SQL能操作具有重复行的集合，可以通过可选项ALL来支持

        一般集合论是不允许集合里存在重复元素的，因此集合{1,1,2,3,3}和集合{1,2,3}被视为相同集合。但是关系数据库里的表允许重复的行，称为多重集合。
        SQL的集合运算提供了允许重复和不允许重复的两种用法。如果直接使用union和intersect，结果就不会出现重复行。如果想在结果集留下重复的行可以使用union all。
        除了运算结果以外，这两种用法还有不同。集合运算符为了排除重复行，默认会发生排序，而加上ALL后，就不会排序了，所以性能有所提升。

        集合运算符有优先级

        intersect比union和except优先级高。因此同时使用union和intersect，又想让union先执行，必须用括号明确的指定运算顺序。

        各个DBMS提供商在集合运算的实现程度上参差不齐

        SQL Server从2005版开始支持intersect和except，而MySQL还都不支持。还有Oracle，实现了except功能但却命名为minus。

        除法运算没有标准定义

        四则运算里的和(union)、差(except)、积(cross join)都被引入了标准SQL。但商(divide by)却没能标准化。

2、比较表和表：检查集合相等性

        在迁移数据库的时候，或者备份数据和最新数据的时候，我们需要比较两张表是否相等。这里说的相等是指行数和列数以及内容都相同，即“是同一集”的意思。
        例子：判断下列两张表的数据是否一致。

       

         方式一：使用union计算行数。利用union的去重特性可以排除两个表相同的数据，如果得出的计算行数为两个表的行数那么这两个表的数据一致。        

select count(*)
from 
(select * from tbl_A 
    union
 select * from tbl_B
)a

       这里的union又一个非常重要的性质，在数学上我们称为幂等性。

        及 s union s=s                s union s union s union s=s

        方式二：利用两个集合的并集和差集来判断相等性。如果使用SQL语言描述就是“a union b = a intersect b ，则集合A和B相等”。之后只需要判断(A UNION B)  EXCEPT (A INTERSECT B)的结果是否是空集就可以。如果A=B，则结果是空集，否则找个结果集里肯定有数据。
        注意：EXCEPT和INTERSECT目前不支持在MySQL中。（MySQL8.0支持）

select if(count(*) =0,"相等","不相等") isSame
from 
((select * from A  
 union 
 select * from B)a
except
(select * from A
 intersect
 select * from B)b)c

3、用差集实现关系除法运算

        SQL中没有直接进行关系除法运算的运算符。因此为了进行除法运算，必须自己实现。方法比较多，其中代表性的是下面三个：

        1、嵌套使用not exists

        2、使用having子句转换成一对一关系

        3、把除法变减法              

例子1：从表EmpSkills中找出精通表Skills中所有技术的员工。

       方式1：使用group by+having

SELECT emp
from empskills
INNER JOIN skills
on empskills.skill=skills.skill
GROUP BY emp
HAVING count(*)=(SELECT count(*) from skills)

        方式2：使用EXISTS、excpet

sELECT DISTINCT emp
  FROM EmpSkills ES1
 WHERE NOT EXISTS
        (SELECT skill
           FROM Skills
         EXCEPT
         SELECT skill
           FROM EmpSkills ES2
          WHERE ES1.emp = ES2.emp);

       解释：这段代码的要点在于EXCEPT运算符和关联子查询。关联子查询 建立在表EmpSkills 上，这是因为，我们要针对每个员工进行集合运算。 即从需求的技术的集合中减去每个员工自己的技术的集合，如果结果是空 集，则说明该员工具备所有的需求的技术，否则说明该员工不具备某些需 求的技术

 4、寻找相等的子集

        需求：求出种类数量和种类完全一样的供应商组合。

SELECT a.sup,b.sup
from supparts a,supparts b
-- 查询全部组合，且去重
where a.sup<b.sup
-- 查询同类型的
 and a.part=b.part
GROUP BY a.sup,b.sup
-- 种类数量一致
HAVING count(*)=(SELECT count(*) from supparts where supparts.sup=a.sup)
	and count(*)=(SELECT count(*) from supparts where supparts.sup=b.sup)

        解释：首先通过自连接查询所有组合且去重，然后添加条件查询同类型的组合。然后使用group by分组，添加having条件查询两个类型数量一致的。