01-入门基础

常用术语
执行顺序
检索数据^{select、limit}
排序数据^{order by}
过滤数据^where
创建计算字段^{concat、case when、if}
汇总数据^{group by、having}
子查询^{with as}
联结表^union、join

常用术语

SQL（Structured Query Language）：是一种专用们来与数据库沟通的语言。
数据库（database）：是一个以某种有组织的方式存储的数据集合，就像是一个存放数据的文件柜。
数据表（table）：文件柜中存放的资料，是某种特定类型数据的结构化清单。
列（column）：数据表中的字段，所有表都由一个或多个列组成。
行（row）：表中的一行记录。
主键（primary key）：一列（或一组列），其值能够唯一标识表中每一行。主键是唯一的。

执行顺序

FROM
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
UNION
ORDER BY

注意：

FROM是SQL语句执行的第一步，并非SELECT，数据库在执行SQL语句的第一步是将数据从硬盘加载到数据缓冲区中，以便对这些数据进行操作；

SELECT 是在FROM和GROUP BY之后执行。这就是不能在WHERE中使用在SELECT中设定别名的字段作为判断条件的原因；

无论在语法上还是在执行顺序上， UNION总是排在在ORDER BY之前。

检索数据

select语句：选择列

-- 检索所有的列
select *
from tablename;

-- 检索多个列
select id, name
from tablename;

-- 检索不同的值
select distinct id
from tablename;

limit语句：限制行数

-- 限制前100行数据
select *
from tablename
limit 100;

-- 返回第5行(不含)之后的100行数据
select *
from tablename
limit 100 offset 5;

-- 返回第5行(不含)之后的100行数据(简写)
select *
from tablename
limit 5,100;

排序数据

order by语句：排序

order by默认升序：asc；降序为：desc

-- 默认升序
select apply_date, name
from tablename
order by apply_date;

-- 多列排序：apply_date升序，name降序
select apply_date, name
from tablename
order by apply_date, name desc;

-- 按位置排序：第1列升序，第2列降序
select apply_date, name
from tablename
order by 1, 2 desc;

过滤数据

where语句：增加过滤条件

select *
from tablename
where apply_date = '2021-01-21';

where语句常用操作符

条件查询：=, <>, !=, >, <（其中不等于符号<>是标准语法，适用范围比!=广）

select *
from tablename
where apply_date <> '2021-01-21';

select *
from tablename
where apply_date >= '2021-01-21';

范围查询：in, between

-- 文本值范围
select *
from tablename
where grade in ('A','B');

-- 数值范围（包含范围两端）
select *
from tablename
where age between 2 and 10;

空值查询：is (not) null

select *
from tablename
where name is (not) null;

模糊查询：”like”与’’%’‘、”_“结合（如果用Python读取SQL语句，需要将%改为%%）
- ’’%’‘百分号通配符：表示任何字符出现任意次数（可以是0次）
- “_“下划线通配符：表示只能匹配单个字符，不能多也不能少

-- 开头模糊
select *
from tablename
where name like '%ing';

-- 末尾模糊
select *
from tablename
where name like 'Fish%';

-- 两端模糊
select *
from tablename
where name like '%is%';

多条件查询：and，or（在处理or操作符之前，优先处理and操作符，使用圆括号明确分组！）

select *
from tablename
where grade in ('A','B')
and apply_date >= '2021-01-21';

创建计算字段

拼接字段

select concat(id, '-', name) as new_col
from tablename;

算术计算

常用算数符号：+, -, *, /

select period * amount as new_col
from tablename;

条件计算

if( [expr], [result1], [result2] )
case when [expr] then [result1] else [result2] end

select 
 apply_date
,if(state=1,'approve','reject') as state1
,case state
 when 1 then 'approve'
 when 2 then 'reject'
 else 'other'
 end as state2
,case when state = 1 then 'approve'
 	  when state = 2 then 'reject'
 	  else 'other' end as state3
from tablename;

汇总数据

聚合函数

常用聚合函数sum( ), count(), count(distinct ), avg(), max(), min()

select
 cont(distinct *) `总行数`
,count(id) `总人数`
,sum(amount) `总金额`
,avg(amount) `平均金额`
from tablename;

group by语句：数据分组

select 
 date_format(apply_time,'%Y-%m') `月份`
,count(id) `总人数`
from tablename
group by 1
order by 1;

-- 分组并展示细分汇总
select 
 province
,city
,count(id) `总人数`
from tablename
group by 1,2 with rollup

having语句：过滤分组

select 
 date_format(apply_time,'%Y-%m') `月份`
,count(id) `总人数`
from tablename
group by 1
having count(id) >= 30
order by 1;

子查询

子查询作为过滤数据条件

仅限单列。

select *
from tablename1
where id in
            (
            select id
            from tablename2
            where grade = 'A'
            );

子查询作为汇总数据前提

select id, avg(amount)
from 
    (
    select id, amount
    from tablename
    where grade = 'A'
    )
group by 1;

子查询作为计算字段

select
 id
,(
  select count(*)
  from tablename2
  where tablename2.id = tablename1.id
 ) as cnt
from tablename1;

运用with as创建临时表进行子查询

with 
temp1 as
(
select id, amount
from tablename1
where grade = 'A'
),

temp2 as
(
select id, name
from tablename2
)

select *
from temp1
left join temp2 on temp1.id = temp2.id;

联结表

union语句：纵向关联

union必须由两条或以上的select语句组成，中间用union分隔；
union中的每个查询必须包含相同的列、表达式或聚合函数（各列不需要以相同次序出现）；
列名称必须相同，且数据类型必须兼容，但类型不必完全相同。

-- union：自动去除重复行
select id, amount
from tablename1
union
select id, amount
from tablename1

-- union all：保留所有重复行
select id, amount
from tablename1
union all
select id, amount
from tablename1

join语句：横向关联

left join（左联结）：获取左表所有记录，即使右表没有对应匹配的记录；
right join（右联结）：获取右表所有记录，即使左表没有对应匹配的记录；
inner join（内联结）：取交集，获取两个表中字段匹配关系的记录；
full join（全联结）：取并集，获取两个表中字段所有的记录。

select 
 t1.id
,t1.name
,t2.grade
,t2.score
from tablename1 t1
left join tablename2 t2 on t1.id = t2.id;

内联结的两种写法

-- 方法1：inner join
select 
 t1.id
,t1.name
,t2.grade
,t2.score
from tablename1 t1
inner join tablename2 t2 on t1.id = t2.id;

-- 方法2：where
select 
 t1.id
,t1.name
,t2.grade
,t2.score
from tablename1 t1, tablename2 t2
where t1.id = t2.id;

横向无条件联结

select 
 t1.id
,t2.name
from tablename1 t1
left join tablename2 t2
on 1 = 1;