美文网首页
MySQL高级查询

MySQL高级查询

作者: EwanRenton | 来源:发表于2018-07-26 16:33 被阅读0次

    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
    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;

    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语句中定义的列和聚合表达式上。

    相关文章

      网友评论

          本文标题：MySQL高级查询

          本文链接：https://www.haomeiwen.com/subject/wdibmftx.html

          延伸阅读

          深度阅读

          • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

          栏目导航

          热点阅读

          关于我们|服务条款|联系我们|MySQL高级查询|投稿指南|网站地图|RSS订阅|排版工具|手机版

          提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

          Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

          备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

          本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

          所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！