SQL进阶之路
- 1.数据库基础知识
- 2.数据定义语言DDL(data define language)
- 3.数据操作语言DML(data maintain language)
- 4.数据查询语言DQL(data query language)
- 5.SQL常用窗口函数
- 6.数据控制语言DCL(data control language)
- 7.SQL代码规范
- 8.DBeaver使用技巧总结
- 9.SQL案例积累
- 10.经典50刷题练习总结
- 11.牛客网刷题练习总结
- 12.Leecode刷题练习总结
- 13. sqlzoo刷题练习总结
图钉📌精神，持之以恒。
20200524正式开始，7月开始整理所学。
1.数据库基础知识
1.1 数据库
数据库：DataBase
可以理解为存放数据的容器，数据按照一定规则和数据结构存放在库里面。
1.2 数据库有哪些分类？
根据数据结构不同，有不同类型的数据库。常见有关系型数据库、非关系型数据库。
关系型数据库：sql 数据结构是二维的表格形式，由行、列组成，一张张数据表存在数据库中。
非关系型数据库：Nosql
1.3 什么是数据库管理系统？
数据库管理系统：DBMS（database management system）
是一种系统软件，对数据库进行定义、管理、维护等。
1.4 什么是数据库管理工具？
是连接数据库的一种客户端软件，具有可视化操作界面。
比如Navicat、DBeaver。
1.5 什么是SQL语言？
结构化查询语言：SQL(structured query language)
是一种编程语言。
1.6 SQL语言包含哪些内容？
- 数据定义语言DDL(data define language)
- 数据操作语言DML(data maintain language)
- 数据查询语言DQL(data query language)
- 数据控制语言DCL(data control language)
1.7 学习SQL和数据库知识的价值
对于从事数据分析工作的同学来说，巧妇难为无米之炊，没有数据，如何分析？
学习SQL可以提取想要的数据，并按照一定逻辑处理数据，得到数据结果。
1.8 MySQL介绍、下载和安装
下载社区版。
1.9 客户端如何连接数据库
可以连接mysql的本地服务器，也可以连接远程服务器，使用远程的数据库。
1.10 本章小结
2.数据定义语言DDL(data define language)
对数据对象进行增删改
数据对象包括：库、表、视图、索引等
2.1 对数据库、数据表增删改
数据定义命令：CRATE DROP ALTER
-- 创建数据库，名字是DB_demo
CREATE DATABASE DB_demo;
-- 删除数据库，名字是DB_demo
DROP DATABASE DB_demo;
必须在数据库下创建表，因此创建表必须先创建数据库或者指定某个数据库
-- 创建库
CREATE DATABASE DB_demo;
-- 再创建表
CREATE TABLE test1_hch_20200524(id varchar(100),name char(20),age int,birthday date);
-- 删除表
DROP TABLE test1_hch_20200524;
-- 删除指定数据库下的表，严谨
DROP TABLE DB_demo.test1_hch_20200524;
-- 删除库，操作谨慎
DROP DATABASE DB_demo;
指定某个数据库，在该数据库下创建表
-- 指定库
USE DATABASE DB_demo;
-- 再创建表
CREATE TABLE test1_hch_20200524(id varchar(100),name char(20),age int,birthday date);
ALTER TABLE 语句：主要是用于对列的操作，如在表中增加列、删除列、更改字段名、字段类型，或者更改表名
-- 进入数据库
USE 数据库名；
-- 增加一列
ALTER TABLE 数据库名.表名 ADD edu varchar(20);
-- 删除一列
ALTER TABLE 数据库名.表名 DROP edu;
-- 修改字段名id为member_id,旧字段名一定要加上
ALTER TABLE 数据库名.表名 CHANGE id member_id varchar(100);
-- 修改表名
ALTER TABLE 数据库名.表名 RENAME 数据库名.新表名;
-- 看一下是否表名已经改变
SHOW tables;
-- 查看目前所有数据库
SHOW DATABASES;
-- 查看目前所在数据库所有表
SHOW tables;
-- 查看表的内容
DESC 数据库名.表名;
运行结果：
知识点：
1.命令：动作+对象+具体对象名。
2.创建表：CREATE TABLE 表名（列名 数据类型），需要指定数据类型。
3.数据类型：整型、浮点型、日期/时间型、文本/字符串型。
一般金额、价格等有小数的数据使用float，整数使用int,其他比如有某个业务字段状态取值为0、1、2也可以字符型或者int。
具体的varchar（），括号中的数字可能需要数据库开发去考虑和设计，深入接触后可以再思考这个问题。
4.mysql中，数据库和表，名字不区分大小写。
3.数据操作语言DML(data maintain language)
针对表里面数据的操作，或者理解为针对行的操作，增删改。（一般数据库开发负责）
3.1 向数据表中插入数据（插入行）
指定列名，指定了具体的哪个字段插入哪一个数据值，列名和数据值前后一一对应即可。
- 可以插入所有字段的数据
- 可以插入部分字段的数据，插入部分字段，必须有列名。
- 字段的顺序可以不一样
INSERT into 数据库名.表名(order_id,member_id,age) values('11','001',26);
知识点
1.插入的数据，数据类型要与数据表中的数据类型一致。
2.字符类型需要加单引号’’。
不指定列名，插入的数据必须与数据表中的字段顺序一致，列数量一致。
INSERT into 数据库名.表名 values('11','001',26);
3.2 更新数据表中的数据
使用场景：某一列或者某个字段值有错误需要更改
-- 更新某个字段值/更新整个列的值
UPDATE 数据库名.表名 set order_id='1'
where member_id='11';
3.3 删除数据表中的数据
-- 删除某些行/所有行
DELETE FROM 数据库名.表名
WHERE member_id='11';
-- 清空表中数据，表还在
DELETE FROM 数据库名.表名;
4.数据查询语言DQL(data query language)
查询命令三段式：
select
from
where
4.1基础查询语法
- select 要查询的字段
- from 要查询的表
- where 过滤条件
- order by 排序
- distinct 对查询结果去重，可以一个字段也可以多个字段
-- 多个字段，组合结果唯一
select distinct t1.salesID
,t1.goodsID
from dw.fct_sales_item t1
where t1.dimDateID=20170801;
- 在where过滤时，使用in:过滤条件是集合
select *
from 数据库.表名
where member_id not in (1,21,11);
- 模糊查询
-- %:表示任意长度的字符串，长度可以为0
where member_id like '%5'
-- _:一个下划线表示一个字符
where member_id like 'abo_1'
- 汇总函数sum、count等
- group by 按照指定的列名进行分组统计（按照不同维度下钻再在组内聚合），一般与汇总函数一起使用。对select后面的字段进行汇总。
1.如果只有汇总函数，计算累计数值，不需要group by，就计算总金额，总人数等。
2.count计数时候，一定要注意是否需要去重。
- having 对分组后的汇总结果进行过滤，和group by一起使用，理清楚汇总前过滤用where ，汇总后过滤用having。
举个例子：
1.对会员订单金额累计汇总后，取累计金额大于100的使用having
2.统计订单金额大于100的会员记录，使用where过滤
-- 对汇总结果过滤
select member_id
,sum(t1.order_money) as sum_money
from demo_order t1
group by member_id
having sum(t1.order_money)>100 -- 使用原始字段名，因为有的数据库不支持别名
order by sum(t1.order_money);
-- 对原始表中的行进行过滤再汇总
select member_id
,sum(t1.order_money) as sum_money
from demo_order t1
where order_money >100 -- 原始订单中金额大于100的才进行汇总统计
group by member_id ;
4.2 基础函数–时间函数
1）举个例子：统计7月每天购物的顾客数，销售金额，订单量
select t1.dimDateID
,count(distinct t1.dimMemberID ) as member_num
,sum(t1.AMT ) as amt
,sum(t1.salesID ) as order_num
from dw.fct_sales t1
where t1.dimDateID between 20170701 and 20170730
group by t1.dimDateID
order by t1.dimDateID ;
-- 使用时间转换函数date_fromat(字段参数，想要的格式)
select date_format(t1.dimDateID ,'%Y-%m-%d') as dday
,count(distinct t1.dimMemberID ) as member_num
,sum(t1.AMT ) as amt
,sum(t1.salesID ) as order_num
from dw.fct_sales t1
where t1.dimDateID between 20170701 and 20170730
group by date_format(t1.dimDateID ,'%Y-%m-%d')
order by date_format(t1.dimDateID ,'%Y-%m-%d');
2）举个例子：统计7月每周购物的顾客数，销售金额，订单量
-- 7月每一周，按照每一天统计
select t2.weekName -- 另一张时间表，有具体的‘周’这个维度字段
,t1.dimDateID
,count(distinct t1.dimMemberID ) as member_num
,sum(t1.AMT ) as amt
,sum(t1.salesID ) as order_num
from dw.fct_sales t1
join dw.dim_date t2 on t1.dimDateID =t2.dimDateID
where t1.dimDateID between 20170701 and 20170730
group by t1.dimDateID ;
select t2.weekName
,count(distinct t1.dimMemberID ) as member_num
,sum(t1.AMT ) as amt
,sum(t1.salesID ) as order_num
from dw.fct_sales t1
join dw.dim_date t2 on t1.dimDateID =t2.dimDateID
where t1.dimDateID between 20170701 and 20170730
group by t2.weekName ;
- week 函数
select week(t1.dimDateID) as every_week
,count(distinct t1.dimMemberID ) as member_num
,sum(t1.AMT ) as amt
,sum(t1.salesID ) as order_num
from dw.fct_sales t1
where t1.dimDateID between 20170701 and 20170730
group by week(t1.dimDateID)
order by week(t1.dimDateID) ;
使用week，7月就从第26周开始了，这里是由于统计一周的时间范围时，不同的计算方法的影响，比如从周一开始还是从周日开始，我们具体看一下，week是把哪一天算作第26周的。
如下，可以看到，week是从周日到周六算作一周，也就是一周的开始是sunday。
select t1.dimDateID
,week(t1.dimDateID) as every_week
,dayname(t1.dimDateID ) as day_name
,count(distinct t1.dimMemberID ) as member_num
,sum(t1.AMT ) as amt
,sum(t1.salesID ) as order_num
from dw.fct_sales t1
where t1.dimDateID between 20170701 and 20170730
group by t1.dimDateID
order by t1.dimDateID ;
对比：
用原来数据表中的weekname字段，是从周一开始计算一周，看看第31周有哪些天
使用week函数，是从周日开始计算一周，第31周有哪些天，是如何统计的
3）举个例子：统计7月1号每小时：购物的顾客数，销售金额，订单量
目前有点疑问，待解决。小时的数据没有出来？？？
明白了：hour(time),现在hour(t1.dimDateID)是(date).
select hour(t1.dimDateID) as every_hour
,count(distinct t1.dimMemberID ) as member_num
,sum(t1.AMT ) as amt
,sum(t1.salesID ) as order_num
from dw.fct_sales t1
where t1.dimDateID=20170701
group by hour(t1.dimDateID)
order by hour(t1.dimDateID) ;
总结应用：按照不同时间维度来统计汇总数据，月、周、天、小时
通常企业中为了提升业务的分析效率，都有处理好的时间维度表
- datediff
两个日期相减，大前小后，返回天数 - timediff
time相减，返回差值 - 格式转换函数：str_to_date(str,format)
4.3 基础函数–控制函数
case when 条件 then 结果
when 条件 then 结果 (else)
end
1）举个例子
-- 有null显示为null空值，没有被分类
case when order_money>200 then '高'
when order_money between 100 and 200 then '中'
when order_money <100 then '低'
end as type_order
-- 不在上面三个条件下的，会被记录为其他，else不是必须写的
case when order_money>200 then '高'
when order_money between 100 and 200 then '中'
when order_money <100 then '低'
else '其他'
end as type_order
-- null值也会被统计进来，null被分组为‘低’
case when order_money>200 then '高'
when order_money between 100 and 200 then '中'
else '低'
end as type_order
-- group by 最好不用别名
group by case when order_money>200 then '高'
when order_money between 100 and 200 then '中'
when order_money <100 then '低' -- null就是空值，没有被分类
end;
if 函数
if(order_money>100,'高','低') as order_type
if 的嵌套
-- 没有case when直接
if(order_money>100,if(order_money>200,'高','低'),'较低') as order_type
总结应用：根据条件来进行判断生成对应的结果
1）生成新的分类字段/分类区间；2）按照不同结果分组统计
练习：统计订单明细表，把商品价格3等分，统计每个区间的商品数–n等分？？？待解答
select case when t1.AMT >100 then '高'
when t1.AMT between 50 and 100 then '中'
when t1.AMT <50 then '低'
end as good_pricetype
,count(distinct t1.goodsID ) as good_num
from dw.fct_sales_item t1
group by case when t1.AMT >100 then '高'
when t1.AMT between 50 and 100 then '中'
when t1.AMT <50 then '低'
end ;
对区间进行自定义分组并且统计落在各区间内的数据量：利用interval划出几个区间，再利用elt函数将这几个区间分别返回一个分组名，没有实现？？？
举个例子：
区间统计函数待办
等分和自定义分组统计
4.4 基础函数–文本函数和数学函数
1）字符的截取和拼接、返回某个字符所在位置信息、替换某个字符等
2）基本的数学运算、数据截取（小数位数取舍）、产生随机数、绝对值等等计算
4.5 子查询
4.5 .1 子查询–select
重要：一个select语句的查询结果能够作为另一个语句的输入值，三种主要情况为：
1）子查询可以出现在where子句中，作为过滤条件：只能是一个字段
2）子查询可以出现在from子句中，作为一个临时表使用：可以多个字段
3）能出现在select list 中，作为一个字段值来返回：只能是一个字段
好好理解接下来的例子！！！
举个例子：2017年7月2号计算每个会员购买金额，以及每个会员购买金额占总体金额的比
计算占比，无法查询出来总数，需要子查询，先计算一下总体，再计算占比。
-- 可以逐步验证，避免出错
select t1.dimMemberID
,sum(t1.AMT) as money
,sum(t1.AMT)/(select sum(t2.AMT)
from dw.fct_sales t2
where t2.dimDateID='20170702' and t2.dimMemberID<>0) as total_percent
,(select sum(t3.AMT)
from dw.fct_sales t3
where t3.dimDateID='20170702' and t3.dimMemberID<>0) as total_money
from dw.fct_sales t1
where t1.dimDateID='20170702' and t1.dimMemberID<>0
group by t1.dimMemberID
order by sum(t1.AMT) desc;
select t1.dimMemberID
,sum(t1.AMT) as money
,concat(round(sum(t1.AMT)/(select sum(t2.AMT)
from dw.fct_sales t2
where t2.dimDateID='20170702' and t2.dimMemberID<>0),4)*100,'%') as total_percent
from dw.fct_sales t1
where t1.dimDateID='20170702' and t1.dimMemberID<>0
group by t1.dimMemberID
order by sum(t1.AMT) desc;
4.5 .2 子查询–from
举个例子：2017年7月2号对每位会员累计购买金额进行分段
（临时表：得到每个会员的消费总金额，进一步判断总金额所在的分类，要每一个会员具体的金额和分类结果）
会员-购买金额-所属区间，累计购买金额不是现成的字段需要先计算出来创建临时表
select t2.dimMemberID
,t2.money
,case when t2.money>=1000 then 'A'
when t2.money>=500 and t2.money<1000 then 'B'
when t2.money>=200 and t2.money<500 then 'C'
else 'D'
end as type_money
from (select t1.dimMemberID
,sum(t1.AMT) as money
from dw.fct_sales t1
where t1.dimDateID='20170702' and t1.dimMemberID<>0
group by t1.dimMemberID) as t2
order by t2.money desc;
举个例子：对7月份每位会员累计购买金额进行分段统计，并统计每段的会员数
（临时表：得到每个会员的消费总金额，进一步判断总金额所在的分类，针对分类结果进行分组统计）
select case when t2.money>=1000 then 'A'
when t2.money>=500 and t2.money<1000 then 'B'
when t2.money>=200 and t2.money<500 then 'C'
else 'D'
end as type_money
,count(distinct t2.dimMemberID) as member_number
from (select t1.dimMemberID
,sum(t1.AMT) as money
from dw.fct_sales t1
where t1.dimDateID between 20170701 and 20170730 and t1.dimMemberID<>0
group by t1.dimMemberID
) as t2
group by case when t2.money>=1000 then 'A'
when t2.money>=500 and t2.money<1000 then 'B'
when t2.money>=200 and t2.money<500 then 'C'
else 'D'
end;
4.5 .3 子查询–where
举个例子：2017年7月统计累计购买金额在100-200的会员，寻找会员的消费记录
（先查找出累计金额符合条件的会员，再去查找他们的消费记录，最终要查找的消费记录根据子查询的id确定）
-- 暂时没有结果
select *
from dw.fct_sales
where dimDateID=20170702
and dimMemberID<>0
and dimDateID in (select dimMemberID
from dw.fct_sales
where dimDateID=20170702 and dimMemberID<>0
group by dimMemberID
having sum(AMT)>10 and sum(AMT)<200
order by sum(AMT) desc)
;
练习：查找7月份某周订单金额最大的会员，这个会员的所有消费记录！！！
（可能有很多人消费金额一样大，先确定最大金额，再去看哪些会员是这些金额，取出来id，再去根据id查询所有消费字段）
select *
from dw.fct_sales
where dimMemberID in
(select distinct dimMemberID
from dw.fct_sales
where AMT in (select max(AMT) -- 等于号和in都可以
from dw.fct_sales
where dimDateID='20170702' and dimMemberID<>0)
);
4.6 行合并
- union
- union all
知识点：
1.两个都可以合并行，或者查询的结果集，合并的部分，各自查询的字段名可以不一样，但是，字段数量、字段数据类型、字段顺序必须一样，这样合并的结果也才是有意义的。否则顺序错乱了也不知道哪一个是哪一个数据结果。
2.all不会去重
3.排序注意⚠️
统计周报：20170703-0709的每天及本周累计：销售金额、订单量、会员数、订单占比
-- 6个字段，每一天的数据
select dimDateID
,DATE_FORMAT(dimDateID,'%W') as week_name
,sum(AMT) as total_money
,count(distinct salesID ) as num_sales
,count(distinct dimMemberID ) as num_member
,concat(round(sum(AMT)/(select sum(AMT)
from dw.fct_sales
where dimDateID BETWEEN '20170703' and '20170709'),4)*100,'%') as money_per
from dw.fct_sales
where dimDateID BETWEEN '20170703' and '20170709'
group by dimDateID ;
-- 4个字段，汇总数据
select sum(AMT) as total_money
,count(distinct salesID ) as num_sales
,count(distinct dimMemberID ) as num_member
,sum(AMT)/(select sum(AMT)
from dw.fct_sales
where dimDateID BETWEEN '20170703' and '20170709') as money_per
from dw.fct_sales
where dimDateID BETWEEN '20170703' and '20170709';
-- 合并结果
select DATE_FORMAT(dimDateID,'%W') as week_name
,sum(AMT) as total_money
,count(distinct salesID ) as num_sales
,count(distinct dimMemberID ) as num_member
,CONCAT(round(sum(AMT)/(select sum(AMT)
from dw.fct_sales
where dimDateID BETWEEN '20170703' and '20170709'),4)*100,'%') as money_per
from dw.fct_sales
where dimDateID BETWEEN '20170703' and '20170709'
group by dimDateID
union -- union all也一样
select WEEK(dimDateID ,1) as week_1 -- 注意，一定要列一样多，函数再学习
,SUM(AMT) as total_money
,count(distinct salesID ) as num_sales
,count(distinct dimMemberID ) as num_member
,'100%' as total_per -- 常数
from dw.fct_sales
where dimDateID BETWEEN '20170703' and '20170709'
group by WEEK(dimDateID,1); -- 也要有一个字段/列和每一天的星期对齐
-- union all 不去重
select DATE_FORMAT(dimDateID,'%W') as week_name
,sum(AMT) as total_money
,count(distinct salesID ) as num_sales
,count(distinct dimMemberID ) as num_member
,CONCAT(round(sum(AMT)/(select sum(AMT)
from dw.fct_sales
where dimDateID BETWEEN '20170703' and '20170709'),4)*100,'%') as money_per
from dw.fct_sales
where dimDateID BETWEEN '20170703' and '20170709'
group by dimDateID
union all -- 不去重
select DATE_FORMAT(dimDateID,'%W') as week_name
,sum(AMT) as total_money
,count(distinct salesID ) as num_sales
,count(distinct dimMemberID ) as num_member
,CONCAT(round(sum(AMT)/(select sum(AMT)
from dw.fct_sales
where dimDateID BETWEEN '20170703' and '20170709'),4)*100,'%') as money_per
from dw.fct_sales
where dimDateID BETWEEN '20170703' and '20170709'
group by dimDateID ;
-- union 去重
select DATE_FORMAT(dimDateID,'%W') as week_name
,sum(AMT) as total_money
,count(distinct salesID ) as num_sales
,count(distinct dimMemberID ) as num_member
,CONCAT(round(sum(AMT)/(select sum(AMT)
from dw.fct_sales
where dimDateID BETWEEN '20170703' and '20170709'),4)*100,'%') as money_per
from dw.fct_sales
where dimDateID BETWEEN '20170703' and '20170709'
group by dimDateID
union
select DATE_FORMAT(dimDateID,'%W') as week_name
,sum(AMT) as total_money
,count(distinct salesID ) as num_sales
,count(distinct dimMemberID ) as num_member
,CONCAT(round(sum(AMT)/(select sum(AMT)
from dw.fct_sales
where dimDateID BETWEEN '20170703' and '20170709'),4)*100,'%') as money_per
from dw.fct_sales
where dimDateID BETWEEN '20170703' and '20170709'
group by dimDateID ;
⚠️不支持排序，如果要排序，嵌套使用子查询，把结果当作临时表，然后按照某个字段排序
-- 不支持排序，如果要排序，嵌套使用子查询，把结果当作临时表，然后按照某个字段排序
select t1.*
from
(select DATE_FORMAT(dimDateID,'%W') as week_name
,sum(AMT) as total_money
,count(distinct salesID ) as num_sales
,count(distinct dimMemberID ) as num_member
,CONCAT(round(sum(AMT)/(select sum(AMT)
from dw.fct_sales
where dimDateID BETWEEN '20170703' and '20170709'),4)*100,'%') as money_per
from dw.fct_sales
where dimDateID BETWEEN '20170703' and '20170709'
group by dimDateID
union
select WEEK(dimDateID ,1) as week_1
,SUM(AMT) as total_money
,count(distinct salesID ) as num_sales
,count(distinct dimMemberID ) as num_member
,'100%' as total_per
from dw.fct_sales
where dimDateID BETWEEN '20170703' and '20170709'
group by WEEK(dimDateID,1)) as t1 -- 临时表
order by total_money desc;
练习月报：统计201707月内每天及本月累计销售金额、订单量、会员数、订单占比、会员渗透率
1）会员渗透率：每天会员购买人数/本月总会员数（去重的），本质也就是以本月会员为一个总体，计算每天的会员占比有多大。
2）用渗透率的概念，是因为，如果把每天的占比结果加起来是大于1的，叫会员占比不太合适，渗透就是看看我每天有多少，分子肯定是有重复的，分母以月作为总体。
3）衡量会员购买的活跃度。
-- 本月
select month(dimDateID) as mmonth
,sum(AMT) as total_money
,count(distinct salesID ) as num_sales
,count(distinct dimMemberID ) as num_member
,'100%' as total_per1
,'100%' as total_per2
from dw.fct_sales
where dimDateID BETWEEN '20170701' and '20170731'
group by month(dimDateID);
select t1.*
from
(select dimDateID as dday
,sum(AMT) as total_money
,count(distinct salesID ) as num_sales
,count(distinct dimMemberID ) as num_member
,CONCAT(round(sum(AMT)/(select sum(AMT)
from dw.fct_sales
where dimDateID BETWEEN '20170701' and '20170730'),4)*100,'%') as money_per
,CONCAT(round(count(distinct dimMemberID )/(select count(distinct dimMemberID )
from dw.fct_sales
where dimDateID BETWEEN '20170701' and '20170730'),4)*100,'%') as member_per
from dw.fct_sales
where dimDateID BETWEEN '20170701' and '20170730'
group by dimDateID
union
select '7 'as month_num
,SUM(AMT) as total_money
,count(distinct salesID ) as num_sales
,count(distinct dimMemberID ) as num_member
,'100%' as total_per1
,'100%' as total_per2
from dw.fct_sales
where dimDateID BETWEEN '20170701' and '20170730'
group by '7') as t1 -- 要有？？？
order by total_money desc;
4.7 多表关联
多表查询的连接方式分为内关联和外关联。
多表关联应用：
1.从不同表取需要的字段
2.在业务分析中，明确好取数的规则和逻辑后，需要看要从哪个表中取，有的字段可以从多个表取（可以考虑从哪个表取是更符合逻辑的），有的字段只能从某个特定的表取。
3.一般不会关联多张表，运算速度很慢，占用资源。
4.表之间在关联时，通过代表相同含义的字段关联才有意义，可能在不同表中字段的名字不一样，但是实际是一个意思。
5.注意给表取别名，查询字段select后面：表别名.列名，告诉系统是哪个表中的字段。
-- 语法
select t1.列名1
,t2.列名2
,t3.*
from demo.goods t1
(inner)join demo.goods_item t2 on t1.列名=t2. 列名
(inner)join demo.member t3 on t1.列名=t3.列名 -- 都关联后相当于一个临时的表，被关联的表也可以不是原始表，临时表同样可以
where -- 针对所有字段都可以过滤
group by -- 针对所有字段都可以分组
4.7.1 多表关联-内关联：原理和类型
- 订单表28行5列
-
订单明细表10行6列
-
笛卡尔积：对两张表订单表（28行）和订单明细表（10行）不做任何操作时生成28*10=280行的数据集。列：5+6=11列。
-
订单表的第一条记录和订单明细表的10条记录分别对应拼接，形成10条记录，相当于这10条记录订单表的部分是10条重复的5列。m*n行，（a+b）列。
取A005和A019解释笛卡尔积
step1:没有on
过程原理：
验证一下数据集结果：
step2:关联时用on进行字段匹配，只取一样的字段对应的记录进行匹配
过程原理：
汇总统计结果，错误的写法：
订单金额和订单数量都重复计算了。
1.考虑生成的表是什么样的结果，是否需要去重；
2.是否需要使用最细粒度来计算某个字段，比如使用订单明细表中的金额*数量计算汇总的金额；
3.逻辑错误不容易发现；
4.where的作用是先过滤再去进行表关联匹配。
5.两张表无法直接关联，一定要借助另一张表间接关联。
6.关联的结果集就是一个临时表。
7.尽量少关联表，一定要注意数据重复的问题，思考逻辑是什么，结果数据是什么，想好用哪一个字段查询，用哪一个字段汇总还是需要间接汇总。
8.一对一、一对多关联匹配一般是不会有问题；多对多需要注意逻辑，如多个订单每个订单对应多个订单明细记录。
正确的写法和结果：
具体类型
内连接/内关联：分为等值连接、自然连接（去掉重复列）和自连接（自己和自己关联）。
等值连接：使用”=”关系将表连接起来的查询，其查询结果中列出被连接表中的所有列，包括其中的重复列。
自然连接：去掉重复列。
自连接：在一个连接查询中，涉及到的两个表都是同一个表，这种查询称为自连接查询。逻辑上是两张表，物理上其实是一张表。
4.7.2 多表关联-外连接：原理和类型
以一个场景为例开始外关联介绍：
会员表中是一直存在的会员明细记录，查询在7月购买的会员和购买记录，如果没有购买的需要进行促活或者使用一些运营手段进行触达。
如果使用内关联，只有找得到能匹配的会员才会出现在结果数据中。
左关联，表的位置调换一下就是右关联，理解一个就ok。
-- 语句
select t1.列名1
,t2.列名2
from demo.goods t1 -- 左表的所有信息都包含进去
left join demo.goods_item t2 on t1.列名=t2. 列名
where -- 针对所有字段都可以过滤
group by -- 针对所有字段都可以分组
使用左关联，显示所有会员的信息，但是这里因为计算金额是价格*数量，所以金额部分，在明细表中没有数据的会显示为null，但是在订单表中可能是有一个总金额的。
后三个字段都是从第二个表取的。（使用自己创建的表，可能不是很符合实际情况。）
外关联结合子查询使用：嵌套子查询，在子查询中去重，是最细粒度的数据，订单维度，保证不会重复。
过程原理：
具体类型
外连接分为左外连接、右外连接、全外连接三种。
4.7.2 多表关联应用
4.7.3 变量
5.SQL常用窗口函数
重要
6.数据控制语言DCL(data control language)
不重要，暂时了解就可以
7.SQL代码规范
7.1基础常识
- 查询多个字段时，换行保持对齐，逗号可以写在前面，漏掉好检查；
- 给字段取别名，简洁易懂即可；
- 表的别名有顺序、有一定规则即可；
- 查询某个字段时要带上表名，表别名.字段名；
- 可以先from找好表，然后分组或取字段，从逻辑上按顺序理解比较好下手敲代码。
7.2数据库和数据表的命名规则
- 命名库名和表名的时候，一般是：以字母开头，可以包括字母、数字和下划线。
- 数据库名称要反映出数据库的内容，具体格式可以遵循公司的命名规范，或者结合自己的习惯有统一的规则，比如DB_student,代表所有学生信息相关的数据库。
- 数据表命名也同样要反映出表的内容，如果是自己创建的表格，可以命名为：表内容_姓名_日期。
8.DBeaver使用技巧总结
- 编辑器中有*,说明有新的内容没有保存，写脚本注意时刻保存。
- 创建数据库/数据表后，由于客户端缓存问题，选中数据库右键刷新下就可以看到新生成的数据库。
9.SQL案例积累
9.1
9.2
9.3
10.经典50刷题练习总结
10.1 基础准备
-- 指定库
USE DATABASE class202005;
-- 创建表
create table Student(sid varchar(10),sname varchar(10),sage datetime,ssex varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
create table Course(cid varchar(10),cname varchar(10),tid varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(tid varchar(10),tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
create table SC(sid varchar(10),cid varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
-- 看看数据是否有错误，熟悉表和字段
select * from class202005.Student;
select * from class202005.Course ;
select * from class202005.SC ;
select * from class202005.Teacher ;
–学生表
Student(SId,Sname,Sage,Ssex)
–SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
–课程表
Course(CId,Cname,TId)
–CId 课程编号,Cname 课程名称,TId 教师编号
–教师表
Teacher(TId,Tname)
–TId 教师编号,Tname 教师姓名
–成绩表
SC(SId,CId,score)
–SId 学生编号,CId 课程编号,score 分数
10.2 练习题
10.2.1 查询“01”课程比“02”课程成绩高的所有学生的学号
10.2.2 查询平均成绩大于60分的同学的学号和平均成绩
3、查询所有同学的学号、姓名、选课数、总成绩
4、查询姓“李”的老师的个数；
5、查询没学过“张三”老师课的同学的学号、姓名；
6、查询学过编号“01”并且也学过编号“02”课程的同学的学号、姓名；
7、查询学过“张三”老师所教的课的同学的学号、姓名；
8、查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名；
9、查询所有课程成绩小于60分的同学的学号、姓名；
10、查询没有学全所有课的同学的学号、姓名；
10.3 分类知识点总结
11.牛客网刷题练习总结
11.1
12.Leecode刷题练习总结
12.1
13. sqlzoo刷题练习总结
13.1 SELECT from WORLD Tutorial
13.1.10 10题
Show per-capita GDP for the trillion dollar countries to the nearest $1000.
-- 我
select name,round(gdp/population,-3) as per_capita_gdp
from world
where gdp>=1000000000000;
-- 小技巧
SELECT name,
ROUND((gdp /(population*1000)))*1000
FROM world
WHERE gdp >= 1000000000000;
13.1.13 13题
-- 不对，没有考虑到字母的无序性
SELECT name
FROM world
WHERE name LIKE '%a%e%i%o%u%'
AND name NOT LIKE '% %';
SELECT name
FROM world
WHERE name LIKE '%a%'
AND name LIKE '%e%'
AND name LIKE '%i%'
AND name LIKE '%o%'
AND name LIKE '%u%'
AND name NOT LIKE '% %';
13.2 SELECT from Nobel Tutorial
13.2.8 8题
-- 且、或的逻辑关系
SELECT yr,subject,winner
FROM nobel
WHERE (subject = 'Physics' AND yr = '1980')
OR (yr = '1984' AND subject = 'Chemistry');
13.2.13 13题
注意：默认升序，降序用desc，单词注意。
13.2.14 14题
SELECT winner,
subject
FROM nobel
WHERE yr = 1984
ORDER BY subject IN ('Physics','Chemistry'),
subject,
winner;
13.2.quiz 3、5、6
Pick the code that shows the amount of years where no Medicine awards were given
SELECT COUNT(DISTINCT yr)
FROM nobel
WHERE yr NOT IN (SELECT DISTINCT yr
FROM nobel
WHERE subject = 'Medicine')
Select the code which would show the year when neither a Physics or Chemistry award was given
SELECT DISTINCT yr
FROM nobel
WHERE yr NOT IN(SELECT yr
FROM nobel
WHERE subject IN ('Chemistry','Physics'))
Select the code which shows the years when a Medicine award was given but no Peace or Literature award was
SELECT DISTINCT yr
FROM nobel
WHERE subject='Medicine'
AND yr NOT IN(SELECT yr
FROM nobel
WHERE subject='Literature')
AND yr NOT IN (SELECT yr
FROM nobel
WHERE subject='Peace')
SELECT DISTINCT yr
FROM nobel
WHERE subject='Medicine'
AND yr NOT IN(SELECT yr
from nobel
WHERE subject='Literature' AND subject='Peace')
13.3 SELECT within SELECT Tutorial
Using nested SELECT
第一次遇到
13.3.3 3题
SELECT name,
continent
FROM world
WHERE continent IN (SELECT continent
FROM world
WHERE name IN ('Argentina','Australia'))
ORDER BY name
13.3.4 4题
select name,population
from world
where population >(select population
from world
where name='Canada')
and population<(select population
from world
where name='Poland')
13.3.5 5题
SELECT name,
concat(ROUND(population/(SELECT population
FROM world
WHERE name = 'Germany'
)*100),'%')
FROM world
WHERE continent = 'Europe';
知识点
-
round
-
floor
-
ceil
-
mod
-
concat
-
substring
-
trim
13.3.7 7题
Using correlated subqueries
SELECT continent, name, area
FROM world x
WHERE area>= ALL(SELECT area
FROM world y
WHERE y.continent=x.continent AND area>0)
rank函数：使用窗口函数rank，首先计算出各国家在各自洲的rank，然后再取rank为1的国家亦可实现。
13.3.8 8题 知识点同上
SELECT continent,
name
FROM world x
WHERE x.name = (SELECT y.name
FROM world y
WHERE y.continent = x.continent
ORDER BY name LIMIT 1);
13.3.9 9题
SELECT name,
continent,
population
FROM world x
WHERE 25000000 >= ALL (SELECT population
FROM world y
WHERE x.continent = y.continent
AND population > 0)
13.3.10 10题
SELECT name,
continent
FROM world x
WHERE x.population / 3 >= ALL (SELECT population
FROM world y
WHERE x.continent = y.continent
AND x.name <> y.name
AND y.population > 0)
quiz-why
使用all可以直接和最大最小作为替换，但是为什么不对呢？
前面使用all，最开始是内外没有关联的，就是一个限定，后面内外有关联，需要x.=y.，回顾一下。
13.4 SUM and COUNT
13.4.0 nobel practice more练习
https://sqlzoo.net/wiki/The_nobel_table_can_be_used_to_practice_more_SUM_and_COUNT_functions.[nobel practice more练习]
13.4.3
没有去重，获奖者可能有重复，但是不去重。
13.4.7
13.4.9
想好按照谁分组，分组后的表是什么样子，按照什么聚合。得到的数据是什么样子。
13.4.10
第一个其实使用yr来计数也可以，但是会有一个问题，可能会有人一年内拿了两个不同的诺贝尔奖，那结果就不对了。所以使用subject比较好。
和下面的题做对比。
13.4.12
注意:12题需要再想一下，看看表格到底是什么，应该用哪个字段，是否有distinct。
select *
from nobel
where yr>=2000
select yr,subject
from nobel
where yr>=2000
group by yr,subject
having count(subject)=3
select yr,subject
from nobel
where yr>=2000
group by yr,subject
having count(winner)=3
select yr,subject
from nobel
where yr>=2000
group by yr,subject
having count(distinct winner)=3
13.5 The JOIN operation
13.5.8 8题
限制条件
SELECT DISTINCT b.player
FROM game a
JOIN goal b ON a.id = b.matchid
WHERE (b.teamid <> 'GER' AND (a.team1 = 'GER' OR a.team2 = 'GER'));
13.5.9 9题
count(其他字段)也一样结果，*是代表？
SELECT teamname,
COUNT(*)
FROM eteam a
JOIN goal b ON a.id = b.teamid
GROUP BY teamname
13.5.11 11题
group by
SELECT matchid,
mdate,
COUNT(b.player)
FROM game a
JOIN goal b ON a.id = b.matchid
WHERE (team1 = 'POL' OR team2 = 'POL')
GROUP BY matchid,
mdate
对比
13.5.13 13题经典
左关联为啥？
-- Notice in the query given every goal is listed.
-- If it was a team1 goal then a 1 appears in score1, otherwise there is a 0.
-- You could SUM this column to get a count of the goals scored by team1
SELECT a.mdate,
a.team1,
SUM(CASE WHEN teamid = a.team1 THEN 1 ELSE 0 END) AS score1,
a.team2,
SUM(CASE WHEN teamid = a.team2 THEN 1 ELSE 0 END) AS score2
FROM game a
LEFT JOIN goal b ON (a.id = b.matchid)-- why?
GROUP BY a.mdate,
b.matchid,
a.team1,
a.team2
13.6 More JOIN operations
13.6.7、8
使用子查询和两个关联哪个比较Ok？
SELECT a.title
FROM movie a
JOIN casting b ON a.id = b.movieid
JOIN actor c ON b.actorid = c.id
WHERE c.name = 'Harrison Ford'
？？？
SELECT a.title
FROM movie a
JOIN casting b ON a.id = b.movieid
WHERE b.actorid=(select c.id from actor c where c.name='Harrison Ford'）
13.6.9
第9题又对了？？？
SELECT a.title
FROM movie a
JOIN casting b ON a.id = b.movieid
WHERE b.actorid=(select c.id
from actor c
where c.name='Harrison Ford')
and b.ord <>1
或者
SELECT a.title
FROM movie a
JOIN casting b ON a.id = b.movieid
JOIN actor c ON b.actorid = c.id
WHERE c.name = 'Harrison Ford'
AND b.ord <> 1
13.6.11
SELECT yr,COUNT(title)
FROM movie
JOIN casting ON movie.id=movieid
JOIN actor ON actorid=actor.id
WHERE name='Rock Hudson'
GROUP BY yr
HAVING COUNT(title) > 2
13.6.12 值得思考和对比
SELECT title,name
FROM movie
JOIN casting ON movie.id=movieid
JOIN actor ON actorid=actor.id
where ord=1 and movie.id in
(SELECT a.movieid
FROM casting a
WHERE a.actorid IN (SELECT id FROM actor WHERE name='Julie Andrews'))
或者
SELECT a.title,
c.name
FROM movie a
JOIN casting b ON a.id = b.movieid
JOIN actor c ON b.actorid = c.id
WHERE a.id IN (SELECT a.id
FROM movie a
JOIN casting b ON a.id = b.movieid
JOIN actor c ON b.actorid = c.id
WHERE c.name = 'Julie Andrews')
AND b.ord = 1
13.6.13 细节
13.6.14 升序降序不明确，可以不查询count，直接order by，看要求
13.6.15 类似12题
SELECT distinct c.name
FROM movie a
JOIN casting b ON a.id = b.movieid
JOIN actor c ON b.actorid = c.id
WHERE a.id IN (SELECT a.id
FROM movie a
JOIN casting b ON a.id = b.movieid
JOIN actor c ON b.actorid = c.id
WHERE c.name = 'Art Garfunkel')
AND c.name <> 'Art Garfunkel'
717
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
