为了账号安全,请及时绑定邮箱和手机立即绑定

SQL基础(二)

标签:
SQL Server

查询

在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执行顺序

webp

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.NameFROM
    Country coJOIN
    City ciON
    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.NameFROM
    Country coLEFT JOIN
    City ciON
    co.Capital = ci.ID;

右联接

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

需求

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

分析

  • 分析结果数据: 输出没有国家语言的国家.展示国家名国家语言,没有国家语言的化,该列为NULL.

  • 分析数据来源: 来自Country表CountryLanguage表.需要多表联接.

sql
以下两张sql等价

SELECT
    co.Name,
    col.LanguageFROM
    Country coLEFT JOIN
    CountryLanguage colON
    co.Code = col.CountryCodeWHERE
    col.CountryCode IS NULL;    
select 
    co.name,
    cl.languagefrom 
    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_listfrom 
  tablewhere
   expr 
     operator
   (select select_list        from table
    );

特点

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

需求

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

分析

  • 我们可以先获取面积最小的国家面积

  • 再按行匹配看那条记录的国家面积等于最小面积

  • 输出该国家名称和面积

sql

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

作为表

语法

select 
  select_listfrom 
  tablejoin
   (select select_list        from table
    )on
  conditionwhere 
  expr operator;

需求

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

分析

  • 我们需要联接Country表City表

  • 可以使用子查询先分别获取Country表和·City表`的虚表降低数据冗余

  • 进行联接

sql

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



作者:sixleaves
链接:https://www.jianshu.com/p/bd8a153892ed


点击查看更多内容
2人点赞

若觉得本文不错,就分享一下吧!

评论

作者其他优质文章

正在加载中
感谢您的支持,我会继续努力的~
扫码打赏,你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫,即可进行扫码打赏哦
今天注册有机会得

100积分直接送

付费专栏免费学

大额优惠券免费领

立即参与 放弃机会
意见反馈 帮助中心 APP下载
官方微信

举报

0/150
提交
取消