SQL Basics

Only searching operation for data engineering.

SQL Basics

SQL零基础入门笔记.
SQLZOO.
Programming order: select - from - where - group by - having - order by - limit
Running order: from - where - group by - having - order by - limit - select

select A renamedA from chart

select distinct

select calculation

where

where name like ‘_t%’

_ for one char, % for any

order by

limit 3,4: 3,4,5,6,7

group by

share same function of “distinct”
use together with statistics functions like : min()max()count()avg()…

having

conditions after “group by”
is able to used with statistic()

Functions

round()
concat(’’,’’,’’,…)
cast(x as type)
data: year(date) month() day() datediff(date1, date2) data_format(data, format)
Cut: left(‘string’, num) right(,) substring(‘string’,start,num)
logic: if(event, v1(real),v2(wrong)); case [when + then] + [when then] + [else] + end
replace()

Partition

SELECT name,
data_format(whn,’%Y-%m-%d’) date,
(confirmed - lag(confirmed,1)over(partition by name order by whn)) New
from covid
where name = ‘Italy’
and Weekday(whn) = 0
order by whn

Join

all join: all data
inner join: data in pairs
left join: all data on left

SQL*50

图解SQL面试题：经典50题.

1. 项目1
2. 项目2
3. 项目3