layout: post
title: "MySQL高级查询"
date: 2016-06-02 11:14:38 +0800
comments: true
categories: [mysql]
前段时间了解了点数据库优化的内容，但是发现自己对很多数据库的高级查询还不是很数据。
下面就整理下最近看的一些高级查询：
| emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |
|---|---|---|---|---|---|
| 100001 | 红枫 | 29 | 9000 | 1977-01-01 | male |
| 100002 | 丽鹃 | 27 | 8000 | 1979-12-31 | fmale |
| 100005 | 啸天 | 27 | 4000 | 1979-07-10 | male |
查询结果的字段联合和重新命名
select concat(emp_id," ",emp_name) from emp;
查询结果：
| concat(emp_id," ",emp_name) |
|---|
| 100005 啸天 |
| 100001 红枫 |
| 100002 丽鹃 |
用AS关键字重新给输出结果命名标题
select concat(emp_id," ",emp_name) as info from emp;
查询结果：
| info |
|---|
| 100005 啸天 |
| 100001 红枫 |
| 100002 丽鹃 |
GROUP BY
group by语法可以根据给定数据列的每个成员对查询结果进行分组统计，最终得到一个分组汇总表。
SELECT子句中的列名必须为分组列或列函数。列函数对于GROUP BY子句定义的每个组各返回一个结果。
select emp_sex,count(*) from emp group by emp_sex;
查询结果如下：
| emp_sex | count(*) |
|---|---|
| fmale | 1 |
| male | 2 |
- 满足“SELECT子句中的列名必须为分组列或列函数”，因为SELECT有GROUP BY emp_sex中包含的列emp_sex。
- “列函数对于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;
| id | name | name |
|---|---|---|
| 1 | Pirate | Rutabaga |
| 2 | Monkey | Pirate |
| 3 | Ninja | Darth Vader |
| 4 | Spaghetti | Ninja |
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;
| id | name | id | name |
|---|---|---|---|
| 1 | Pirate | 2 | Pirate |
| 3 | Ninja | 4 | Ninja |
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;
| id | name | id | name |
|---|---|---|---|
| 1 | Pirate | 2 | Pirate |
| 2 | Monkey | NULL | NULL |
| 3 | Ninja | 4 | Ninja |
| 4 | Spaghetti | NULL | NULL |
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;
| id | name | id | name |
|---|---|---|---|
| 2 | Monkey | NULL | NULL |
| 4 | Spaghetti | NULL | 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;
| id | name | id | name |
|---|---|---|---|
| 1 | Pirate | 2 | Pirate |
| 3 | Ninja | 4 | Ninja |
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;
结果
| id | name | id | name |
|---|---|---|---|
| 2 | Monkey | NULL | NULL |
| 4 | Spaghetti | NULL | NULL |
| NULL | NULL | 1 | Rutabaga |
| NULL | NULL | 3 | Darth Vader |
Right join
mysql> select * from A right join B on A.name = B.name;
| id | name | id | name |
|---|---|---|---|
| NULL | NULL | 1 | Rutabaga |
| 1 | Pirate | 2 | Pirate |
| NULL | NULL | 3 | Darth Vader |
| 3 | Ninja | 4 | Ninja |
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语句中定义的列和聚合表达式上。
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
