深入浅出MySQL(二) 查询技巧

今天这篇文章主要是来讲解一些mysql高级查询技巧方面的内容：时间宝贵，现在我们直接进入主题：

技巧1：union和union all联表查询

Mysql的联合查询命令UNION和UNION ALL,总结了使用语法和注意事项，以及学习例子和项目例子,需要的朋友可以参考下
一、UNION和UNION ALL的作用和语法
UNION 用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行。UNION 内部的 SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同.
SQL UNION 语法：
sql脚本代码如下:

SELECT column_name FROM table1
UNION
SELECT column_name FROM table2

注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。
SQL UNION ALL 语法
sql脚本代码如下:

SELECT column_name FROM table1
UNION ALL
SELECT column_name FROM table2

举个例子来说明

如果要用union来进行相应的查询的话：

SELECT country,'female', sum(population) from country GROUP 
BY country
UNION
SELECT country,'male',sum(population) from country GROUP 
BY country

乍眼一看，没什么意义，这个案例只是用于进行讲解

嗯嗯，确实查询出来也没什么意义，但是如果我们把性别字段统一之后看会有什么效果：

SELECT country,'sex', sum(population) from country GROUP 
BY country
UNION
SELECT country,'sex',sum(population) from country GROUP 
BY country

果然字段信息里面重复的内容会有消失，这就是union链表查询的特点，去重

如果我们将关键字换成了union all的话，就会变成了以下内容：

SELECT country,'sex', sum(population) from country GROUP
 BY country
UNION ALL
SELECT country,'sex',sum(population) from country GROUP 
BY country

这个案例告诉了我们联合查询里面的union和union all的区别了。
那么这种用法又有什么应用场景呢？
不急，现在就来一个案例：
还是之前country这张表：
如果想要按照国家和性别进行分组，得出结果如下 ：
国家 男 女
中国 340 260
美国 45 55
加拿大 51 49
英国 40 60
那么sql该怎么写？
这个时候可以借鉴上述的union关键字来进行联表查询了！！

SELECT country,'男', sum(population) from country WHERE 
sex=1 GROUP BY country 
UNION 
SELECT country,'女',sum(population) from country  WHERE 
sex=2 GROUP BY country

技巧2：case语句

按照上述的那个案例来说：
还是之前country这张表：
如果想要按照国家和性别进行分组，得出结果如下 ：
国家 男 女
中国 340 260
美国 45 55
加拿大 51 49
英国 40 60
那么sql该怎么写？
原先的写法是：

SELECT country,'男', sum(population) from country WHERE 
sex=1 GROUP BY country 
UNION 
SELECT country,'女',sum(population) from country  WHERE 
sex=2 GROUP BY country

嗯嗯，这样写是没有错，但是你是否有考虑过性能优化方面的问题呢？
假射现在我往这个数据表里面插入了100万条数据之后了？sql查询两次，是否会造成性能方面的耽误？为何不试试用case语句来进行优化呢？
以下是相关解决方案：

SELECT country, 
       SUM( CASE WHEN sex = 1 THEN 
                      population ELSE 0 END) as 'male',   
       SUM( CASE WHEN sex = 2 THEN 
                      population ELSE 0 END)  as 'female'
FROM  country 
GROUP BY country; 

这样一来，sql的查询次数就只需要一遍了

查询结果如上图所示，有没有感觉清晰了很多，哈哈哈。这是一种sql优化的技巧。

技巧3：case语句技巧深入

Case具有两种格式。简单Case函数和Case搜索函数。
–简单Case函数
CASE sex
WHEN ‘1’ THEN ‘男’
WHEN ‘2’ THEN ‘女’
ELSE ‘其他’ END
–Case搜索函数
CASE WHEN sex = ‘1’ THEN ‘男’
WHEN sex = ‘2’ THEN ‘女’
ELSE ‘其他’ END

---

简单case函数的案例：
有如下数据:(为了看得更清楚，我并没有使用国家代码，而是直接用国家名作为Primary Key)
国家（country） 人口（population）
中国 600
美国 100
加拿大 100
英国 200
法国 300
日本 250
德国 200
墨西哥 50
印度 250

根据这个国家人口数据，统计亚洲和北美洲的人口数量。应该得到下面这个结果。
洲 人口
亚洲 1100
北美洲 250
其他 700

想要解决这个问题，你会怎么做？生成一个带有洲Code的View，是一个解决方法，但是这样很难动态的改变统计的方式。
如果使用Case函数，SQL代码如下:

SELECT  SUM(population), 
        CASE country 
                WHEN '中国'     THEN '亚洲' 
                WHEN '印度'     THEN '亚洲' 
                WHEN '日本'     THEN '亚洲' 
                WHEN '美国'     THEN '北美洲' 
                WHEN '加拿大'  THEN '北美洲' 
                WHEN '墨西哥'  THEN '北美洲' 
                  WHEN '德国'  THEN '欧洲'
             WHEN '法国'  THEN '欧洲'
             WHEN '英国'  THEN '欧洲'
        ELSE '其他' END 
FROM  country 
GROUP BY CASE country 
                WHEN '中国'     THEN '亚洲' 
                WHEN '印度'     THEN '亚洲' 
                WHEN '日本'     THEN '亚洲' 
                WHEN '美国'     THEN '北美洲' 
                WHEN '加拿大'  THEN '北美洲' 
                WHEN '墨西哥'  THEN '北美洲' 
                  WHEN '德国'  THEN '欧洲'
                WHEN '法国'  THEN '欧洲'
                WHEN '英国'  THEN '欧洲' 
        ELSE '其他' END; 

同样的，我们也可以用这个方法来判断工资的等级，由于之前的emp表里面的数据已经过了一百万，操作起来比较慢，因此先copy其中的部分数据到emp2表里面进行操作：
INSERT INTO emp2
SELECT * from emp limit 10
ok，这下新的表格创建好了。
现在需要对数据库表里面的数据进行分类管理：

SELECT  FIRST_NAME,SALARY,
 CASE 
  WHEN emp2.SALARY <=1000 THEN '穷苦'
  WHEN emp2.SALARY >1000 AND SALARY<=5000  THEN '普通'
  WHEN emp2.SALARY >5000 THEN '富裕'
END
FROM emp2

经过查询最后得出相应的结果图如下：

技巧4：update函数里面使用case

还是刚才的那个emp2表格里面：
例，有如下更新条件
工资5000以上的职员，工资减少10%
工资在2000到4600之间的职员，工资增加15%
很容易考虑的是选择执行两次UPDATE语句，如下所示
–条件1

UPDATE emp2
SET salary = salary * 0.9 
WHERE salary >= 5000; 

–条件2

UPDATE emp2
SET salary = salary * 1.15 
WHERE salary >= 2000 AND salary < 4600; 

但是事情没有想象得那么简单，假设有个人工资5000块。首先，按照条件1，工资减少10%，变成工资4500。接下来运行第二个SQL时候，因为这个人的工资是4500在2000到4600的范围之内， 需增加15%，最后这个人的工资结果是5175,不但没有减少，反而增加了。如果要是反过来执行，那么工资4600的人相反会变成减少工资。暂且不管这个规章是多么荒诞，如果想要一个SQL 语句实现这个功能的话，我们需要用到Case函数。代码如下:

UPDATE emp2 SET emp2.SALARY=
CASE
 WHEN salary>5000 THEN emp2.SALARY*1.15
 WHEN salary>2000 AND salary<4000 THEN SALARY*0.4
ELSE salary END;

嗯嗯，更新成功了

感兴趣的读者也可以关注小编的个人公众号，我会定期在上边分享一些有价值的java知识内容