查询
在SQL中, 最难的莫过于查询.因为查询涉及到比较多的业务逻辑。而其他的语句, 不过是一些记忆性的数据库维护语句.所以本篇博客,我重点总结下SQL中的查询.
前置知识
单行函数
当行函数主要分为以下几类 数据库函数、数字函数、字符函数
数据库函数
database()
查看当前所在所在数据库version()
查看当前数据库服务器版本now()
查看当服务器日期时间
数字函数
round(number, 保留的小数位数) 四舍五入
round(5.329, 2)输出5.33mod(number1, number2) 取余
mod(1600, 300)输出100truncate(数字, 保留的小数位数) 截断
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.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
随时随地看视频
