layout: post
title: "MySQL高级查询"
date: 2016-06-02 11:14:38 +0800
comments: true
categories: [mysql]

前段时间了解了点数据库优化的内容，但是发现自己对很多数据库的高级查询还不是很数据。

下面就整理下最近看的一些高级查询：

查询结果的字段联合和重新命名

• select concat(emp_id," ",emp_name) from emp;

查询结果：

用AS关键字重新给输出结果命名标题

• select concat(emp_id," ",emp_name) as info from emp;

查询结果：

GROUP BY

group by语法可以根据给定数据列的每个成员对查询结果进行分组统计，最终得到一个分组汇总表。

SELECT子句中的列名必须为分组列或列函数。列函数对于GROUP BY子句定义的每个组各返回一个结果。

• select emp_sex,count(*) from emp group by emp_sex;

查询结果如下：

1. 满足“SELECT子句中的列名必须为分组列或列函数”，因为SELECT有GROUP BY emp_sex中包含的列emp_sex。
2. “列函数对于GROUP BY子句定义的每个组各返回一个结果”，根据性别分组，对每个性别返回一个结果，就是每个性别人人数。

从多个数据表中检索信息

-> where emp.emp_id=dept.dept_id;```

UNIN 用法

union:联合的意思，即把两次或多次查询结果合并起来。

• 要求：两次查询的列数必须一致
• 推荐：列的类型可以不一样，但推荐查询的每一列，相对应的类型一样
• 可以来自多张表的数据：多次sql语句取出的列名可以不一致，此时以第一个sql语句的列名为准。

如果不同的语句中取出的行，有完全相同(这里表示的是每个列的值都相同)，那么union会将相同的行合并，最终只保留一行。也可以这样理解，union会去掉重复的行。

如果不想去掉重复的行，可以使用union all``。 如果子句中有order by,limit```，需用括号()包起来。推荐放到所有子句之后，即对最终合并的结果来排序或筛选。

如：(select * from a order by id) union (select * from b order id);

在子句中，order by 需要配合limit使用才有意义。如果不配合limit使用，会被语法分析器优化分析时去除。

注意一点表项的结构必须相同，比如两个表的id int(10) 如果其中一个换成id int(9) 也不行，查询将会报错。但可以有不同的名称（不推荐），查询结果列将以SQL收到的第一份列名为准输出

<pre class="prettyprint linenums">
hotnews=mysql_query("SELECT id,title,pageview,tablenm FROM News UNION SELECT id,title,pageview,tablenm FROM Informs UNION SELECT id,title,pageview,tablenm FROM Article UNION SELECT id,title,pageview,tablenm FROM IntroORDER BY pageview DESC limit 15",conn);//获取在四个表中按浏览数高低排序的前15个文章
</pre>

Join

join 用于多表中字段之间的联系，语法如下：

from table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona

table1:左表；table2:右表。

join按照功能大致分为如下三类：

inner join（内连接,或等值连接）：取得两个表中存在连接匹配关系的记录。

LEFT JOIN（左连接）：取得左表（table1）完全记录，即是右表（table2）并无对应匹配记录。

RIGHT JOIN（右连接）：与 LEFT JOIN 相反，取得右表（table2）完全记录，即是左表（table1）并无匹配对应记录。

注意：mysql不支持Full join,不过可以通过UNION 关键字来合并 LEFT JOIN 与 RIGHT JOIN来模拟FULL join.

接下来给出一个列子用于解释下面几种分类。如下两个表(A,B)

mysql> select A.id,A.name,B.name from A,B where A.id=B.id;

4 rows in set (0.00 sec)

Inner join

内连接，也叫等值连接，inner join产生同时符合A和B的一组数据。

mysql> select * from A inner join B on A.name = B.name;

Left join

mysql> select * from A left join B on A.name = B.name;

或者：select * from A left outer join B on A.name = B.name;

4 rows in set (0.00 sec)

left join,（或left outer join:在Mysql中两者等价，推荐使用left join.）左连接从左表(A)产生一套完整的记录,与匹配的记录(右表(B)) .如果没有匹配,右侧将包含null。

如果想只从左表(A)中产生一套记录，但不包含右表(B)的记录，可以通过设置where语句来执行，如下：

mysql> select * from A left join B on A.name=B.name where A.id is null or B.id is null;

2 rows in set (0.00 sec)

同理，还可以模拟inner join. 如下：

mysql> select * from A left join B on A.name=B.name where A.id is not null and B.id is not null;

2 rows in set (0.00 sec)

求差集：

根据上面的例子可以求差集，如下：

SELECT * FROM A LEFT JOIN B ON A.name = B.name
WHERE B.id IS NULL
union

SELECT * FROM A right JOIN B ON A.name = B.name
WHERE A.id IS NULL;

结果

Right join

mysql> select * from A right join B on A.name = B.name;

4 rows in set (0.00 sec)

USING(column_list)子句
用于为一系列的列进行命名，这些列必须同时在两个表中存在
SELECT java.,mysql. FROM java LEFT JOIN mysql USING (name);

关于join的一个小测试：

有这样两个表：

message:

|id | username |message |
| ---------|:-------------:|
|1 |ewan |123|
|2 |wean2| 123|
|3 |1 |??|
|4 |1 |2343|
|5 |1 |sdfjkl|

user:

|id | username |password |
| ---------|:-------------:|
|1 |1|22|
|2 |2| 111|
|3 |Ewan2 |22|
|4 |Ewan5 |22|

测试如下:

<?php
$dsn="mysql:host=127.0.0.1;dbname=test";
$username="root";
$password="";
try{
    $pdo= new PDO($dsn,$username,$password);
}catch(PDOException $e){
    echo $e->getMessage();
}
$sql="SELECT * FROM message LEFT JOIN user ON message.id=user.id";
$back=$pdo->query($sql);
$back=$back->fetchAll(PDO::FETCH_ASSOC);
print_r($back);

输出结果：

Array
(
[0] => Array
    (
        [id] => 1
        [username] => 1
        [message] => 123
        [password] => 22
    )

[1] => Array
    (
        [id] => 2
        [username] => 2
        [message] => 123
        [password] => 111
    )

[2] => Array
    (
        [id] => 3
        [username] => Ewan2
        [message] => ??
        [password] => 22
    )

[3] => Array
    (
        [id] => 
        [username] => 
        [message] => 2343
        [password] => 
    )

[4] => Array
    (
        [id] => 
        [username] => 
        [message] => sdfjkl
        [password] => 
    )

)

可以看到在两个表都有username的情况下只取了前面的那个username而舍弃了后一个。

HAVING

HAVING 子句对 GROUP BY 子句设置条件的方式与 WHERE 和 SELECT 的交互方式类似。WHERE 搜索条件在进行分组操作之前应用；而 HAVING 搜索条件在进行分组操作之后应用。HAVING 语法与 WHERE 语法类似，但 HAVING 可以包含聚合函数。HAVING 子句可以引用选择列表中显示的任意项。

显示每个地区的总人口数和总面积．仅显示那些面积超过1000000的地区。

SELECT region, SUM(population), SUM(area) 
FROM bbc 
GROUP BY region 
HAVING SUM(area)>1000000 

在这里，我们不能用where来筛选超过1000000的地区，因为表中不存在这样一条记录。

我们在写sql语句的时候,经常会使用where语句,很少会用到having,其实在mysql中having子句也是设定条件的语句与where有相似之处但也有区别。having子句在查询过程中慢于聚合语句(sum,min,max,avg,count).而where子句在查询过程中则快于聚合语句(sum,min,max,avg,count)。

简单说来：

where子句：  
select sum(num) as rmb from order where id>10  
//先查询出id大于10的记录才能进行聚合语句  
 
having子句:  
select reportsto as manager, count(*) as reports from employees  
group by reportsto having count(*) > 4

having条件表达示为聚合语句。肯定的说having子句查询过程慢于聚合语句。
再换句说话说把上面的having换成where则会出错。统计分组数据时用到聚合语句。

对分组数据再次判断时要用having。如果不用这些关系就不存在使用having。直接使用where就行了。
having就是来弥补where在分组数据判断时的不足。因为where要快于聚合语句。

这几个关键字执行的顺序

关键字是按照如下顺序进行执行的：

• Where
• Group By
• Having
• Order by

首先where将最原始记录中不满足条件的记录删除(所以应该在where语句中尽量的将不符合条件的记录筛选掉，这样可以减少分组的次数)

然后通过Group By关键字后面指定的分组条件将筛选得到的视图进行分组
接着系统根据Having关键字后面指定的筛选条件，将分组视图后不满足条件的记录筛选掉

最后按照Order By语句对视图进行排序，这样最终的结果就产生了。

注意：

凡是在group by后面出现的字段，必须同时在select后面出现；

凡是在select后面出现的、同时未在聚合函数中出现的字段，必须同时出现在group by后面.

having 子句被限制子已经在SELECT语句中定义的列和聚合表达式上。