前言

基础SQL入门详情看SQL(一)
多表联接的原理详细看SQL(一)

查询

在SQL中, 最难的莫过于查询.因为查询涉及到比较多的业务逻辑。而其他的语句, 不过是一些记忆性的数据库维护语句.所以本篇博客,我重点总结下SQL中的查询.

前置知识

单行函数

当行函数主要分为以下几类 数据库函数、数字函数、字符函数

数据库函数

• database() 查看当前所在所在数据库

• version() 查看当前数据库服务器版本

• now() 查看当服务器日期时间

数字函数

• round(number, 保留的小数位数) 四舍五入 round(5.329, 2) 输出5.33
• mod(number1, number2) 取余 mod(1600, 300) 输出100
• truncate(数字, 保留的小数位数) 截断 truncate(5.329, 2) 输出5.32

字符函数

• toupper(str) 转大写
• tolower(str) 转小写
• SubStr(str, pos, len) 截取字符串,从索引pos开始截取len个字符(数据库相关索引都是从1开始)

mysql> select substr('hello', 1, 2);
+-----------------------+
| substr('hello', 1, 2) |
+-----------------------+
| he                    |
+-----------------------+
1 row in set (0.00 sec)

• Length(str)

mysql>select length("hello");
+-----------------+
| length("hello") |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)

• LPAD(value, number, fillStr) 设置字符串输出为number个宽度,,不够长时用fillStr填充左边
• RPAD(value, number ,fillStr) 设置字符串输出为number个宽度,,不够长时用fillStr填充右边
• Replace(newStr from Str)

mysql> select replace(str, oldstr, newstr); 从str中找到oldstr替换为newstr
+---------------------------+
| replace('abcd', 'd', 'm') |
+---------------------------+
| abcm                      |
+---------------------------+
1 row in set (0.00 sec)

SQL执行顺序

SQL执行顺序

• FROM 联接表格得到基表
• WHERE 过滤基表数据
• GROUP BY 对基表进行分组
• SELECT 返回最终确定的数据,形成虚表
• ORDER BY 对虚表进行排序
• limit 主要用于分页.用于限制每次返回的数据其实位置和数据数量(limit不是sql标准)

组函数

组函数是多行函数,其作用于多行之上。

单表查询

单表查询,即我们所需要的数据只来自一张表,这个比较简单.详情看SQL(一)文章即可.

多表查询

当我们所需要查询的数据,不是一张表中所能提供的,这时候我们就需要用到多表联接查询.多表联接查询又分为两种内联接和外联接。

前提数据，有如下三张表

mysql> desc City;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
 
mysql> desc Country;
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Field          | Type                                                                                  | Null | Key | Default | Extra |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Code           | char(3)                                                                               | NO   | PRI |         |       |
| Name           | char(52)                                                                              | NO   |     |         |       |
| Continent      | enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') | NO   |     | Asia    |       |
| Region         | char(26)                                                                              | NO   |     |         |       |
| SurfaceArea    | float(10,2)                                                                           | NO   |     | 0.00    |       |
| IndepYear      | smallint(6)                                                                           | YES  |     | NULL    |       |
| Population     | int(11)                                                                               | NO   |     | 0       |       |
| LifeExpectancy | float(3,1)                                                                            | YES  |     | NULL    |       |
| GNP            | float(10,2)                                                                           | YES  |     | NULL    |       |
| GNPOld         | float(10,2)                                                                           | YES  |     | NULL    |       |
| LocalName      | char(45)                                                                              | NO   |     |         |       |
| GovernmentForm | char(45)                                                                              | NO   |     |         |       |
| HeadOfState    | char(60)                                                                              | YES  |     | NULL    |       |
| Capital        | int(11)                                                                               | YES  |     | NULL    |       |
| Code2          | char(2)                                                                               | NO   |     |         |       |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
15 rows in set (0.00 sec)

mysql> desc CountryLanguage;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| CountryCode | char(3)       | NO   | PRI |         |       |
| Language    | char(30)      | NO   | PRI |         |       |
| IsOfficial  | enum('T','F') | NO   |     | F       |       |
| Percentage  | float(4,1)    | NO   |     | 0.0     |       |
+-------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

内联接

特点:内联接查询, 只保留匹配条件为true的行.
如上准备好的表数据,假设有如下需求。
语法: A表 join B表 on 行过滤条件
需求

查询出所有国家的首都, 没有首都的就不输出.

分析

• 观察分析最终结果。根据上述表, 最终我们要的数据是: 输出国家和国家对应的首都,没有首都的国家就过滤掉
• 判断来自哪些表。根据我们分析的最终数据,要国家名可以从Country表中提取也可以从City表中的CountryCode提取,要首都只能从Country表中获取ID到City表中提取, 所以要首都名肯定需要Country表和City表。所以我们肯定需要联接这两张表

sql代码

SELECT
    co.Name, 
    co.Capital, 
    ci.Name
FROM
    Country co
JOIN
    City ci
ON
    co.Capital = ci.ID;

结果
有232条记录.但国家一共有239个,所以有的国家是没有首都的,不符合ON条件,又因为是内联接所以被过滤掉.

+---------------------------------------+---------+------------------------------------+
| Name                                  | Capital | Name                               |
+---------------------------------------+---------+------------------------------------+
| Aruba                                 |     129 | Oranjestad                         |
| Afghanistan                           |       1 | Kabul                              |
| Angola                                |      56 | Luanda                             |

外联接

所谓的外联接就是用来解决上述问题衍生出的需求.这时候假设需求又变了, 要求你联通没有首都的国家也输出。这时候就得用到外联接。

特点: 外联接查询,能够指定那张表的数据保存完整.不因不满足过滤条件而被过滤.

左联接

特点: 保存左表
语法: A表 left join B表 on 条件

需求

假设需求又变了, 要求你联通没有首都的国家也输出。这时候就得用到外联接。

sql

SELECT
    co.Name, 
    co.Capital cap, 
    ci.Name
FROM
    Country co
LEFT JOIN
    City ci
ON
    co.Capital = ci.ID;

右联接

特点: 保存右表.
语法: A表 right join B表 on 条件.
注意:一般我们都直接使用左联接来替换右联接,替换方法就是将A表和B表对换位置即可。

需求

哪些国家没有列出任何使用语言？

分析

• 分析结果数据: 输出没有国家语言的国家.展示为国家名和国家语言,没有国家语言的化,该列为NULL.
• 分析数据来源: 来自Country表、CountryLanguage表.需要多表联接.

sql
以下两张sql等价

SELECT
    co.Name,
    col.Language
FROM
    Country co
LEFT JOIN
    CountryLanguage col
ON
    co.Code = col.CountryCode
WHERE
    col.CountryCode IS NULL;
    
select 
    co.name,
    cl.language
from 
    CountryLanguage cl 
right join 
    Country co 
on 
    cl.countrycode = co.code 
where 
    cl.language is null;

查询步骤总结

以下我结合自身理解总结了一套查询步骤.

1.分析数据从哪些表来.(分析最终要展示的数据表的列,根据列取判断这些数据来源)
    单表无序联接
    多表必须联接
    如果是多表,分析如何联接.
         是内联接(join...on)
         是外联接
             是保存左表的数据不丢 left join...on
         是保存右表的数据不丢 right join...on
2.分析是否需要过滤数据.
        是(where 行过滤条价. 为true的保留下来,false的过滤掉)
3.分析是否需要分组统计数据
        是.分析以什么字段作为分组(如:看每个国家的统计数据,以国家分组)
4.确定要输出的数据(SELECT). 统计数据的时候记得考虑数据是否要去重
5.分组完后, 分析是否需要再次过滤数据, 需要的话以HAVING 行过滤条件(不能使用Where)              
    分析是否是分组统计
        是. 先分组字段放第一列.
6.分析是否需要排序ORDER BY
                    降序(ORDER BY 列名 DESC)
                    升序(ORDER BY 列名)

子查询

为什么有子查询
很多时候,我们需要用到子查询来优化我们的查询.或者业务需求用子查询来实现的计算量比多表联接效率高, 或者该业务只能使用子查询实现.

分类
子查询主用被用在当做变量或者虚表来使用.主要分别用在如下场景

• 查询的时候基于未知的值。
• 查询的时候想用虚表来做联接。

执行顺序
子查询又称为内查询,所以在执行sql的时候,是先执行子查询,拿到子查询的值再执行外查询.

作为变量

语法

select 
  select_list
from 
  table
where
   expr 
     operator
   (select select_list
        from table
    );

特点

• 子查询作为变量的时候,只能有一列,但可以有多行.多行就配合in使用

需求

查询面积最小的国家名称和面积

分析

• 我们可以先获取面积最小的国家面积
• 再按行匹配看那条记录的国家面积等于最小面积
• 输出该国家名称和面积

sql

select 
    name,
    SurfaceArea
from 
    Country 
where   
    SurfaceArea = (select min(SurfaceArea) from Country)

作为表

语法

select 
  select_list
from 
  table
join
   (select select_list
        from table
    )
on
  condition
where 
  expr operator;

需求

查看国家名称和首都的名称.(使用子查询降低联接数据量)

分析

• 我们需要联接Country表和City表
• 可以使用子查询先分别获取Country表和·City表`的虚表降低数据冗余
• 进行联接

sql

select
    t1.countryName,
    t2.cityName
from 
    (select 
        Name countryName, 
        Capital
    from 
        Country) t1
join 
    (select 
        id,
        Name cityName
    from 
        City) t2 
on 
    t1.capital = t2.id;

子查询总结

• 子查询可以分为两种, 作为变量使用和作为表使用.
  如果作为变量使用,那么该子查询必须满足只有一列.
  如果作为虚表参与联接,那么该子查询没有做限制.
• 子查询要用括号括起来.
• 子查询先执行,返回结果后继续执行外查询.
• NULL参与任何运算,其结果都为false.