SQL基础(二)
2018-08-12 本文已影响45人
sixleaves
前言
基础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.
