播客One

一、单表查询

1.基础查询

1. 查询所有列
select * from tablename;

2. 查询特定列
select column1,column2 from tablename;

3. 列别名
select column1 as name1,column2 as name2 from tablename;

4. 去重查询
select distinct column1 from tablename;

5. 限制返回行数
select * from tablename limit 10;

6. 分页查询
select * from tablename limit 10 offset 20;

7. 排序查询
select * from tablename order by column1 desc;

8. 多列查询
select * from tablename order by column1 desc, column2 asc;

2.数据过滤

1. 基础过滤：>, <, >=, <=,!= ,=
select * from tablename where column1 > value1;  # >, <, >=, <=,!= ,=

2. 多条件过滤：and、or
select * from tablename where column1 > value1 and column2 < value2;
select * from tablename where column1 > value1 or column2 < value2;

3. 范围查询
select * from tablename where column1 between value1 and value2;

4. IN操作符
select * from tablename where colum1 in (value1, value2, value3);

5. 模糊查询
select * from tablename where column1 like '%value%';
select * from tablename where column1 like '%value';
select * from tablename where column1 like 'value%';

6. NULL值判断
select * from tablename where column1 IS NULL;

7. 排除特定值
select * from tablename where column1 != value1;

3.聚合函数

1. 计算总数 count()
select count(*) as cnt from tablename where column1 = value1;

2. 分组求和 sum()
select column1 as col1, sum(column2) as col2 from tablename group by column1;

3. 分组平均值 avg()
select column1 as col1,avg(column2) as col2 from tablename group by column1;

4. 分组最大值 max()
select column1 as col1,max(column2) as col2 from tablename group by col1;

5. 分组最小值 min()
select column1 as col1,min(column2) as col2 from tablename group by col1;

6. 分组筛选 having
select column1 as col1, sum(column2) as col2
from tablename
group by column1
where column3=value3
having sum(column2) > value2;

7. 多列分组
select column1 as col1, column2 as col2, sum(column3) as col3 from tablename group by column1,column2;

4.高级窗口函数

1. ROW_NUMBER 生成唯一序号
select column1,column2,
ROW_NUMBER() over (order by column2) as row
from tablename;

2. RANK 与 DENSE_RANK 排名
select column1,column2, 
ANK() over (order by column2 desc) as rank,
DENSE_RANK() over (order by column2 desc) as dense_rank
from tablename;

3. 累计百分比计算
select column1,column2,
SUM(column2) over (order by column1) / SUM(column2) over() as cumulative_percent
from tablename;

4. 移动平均（最近三个窗口）
select column1, column2,
avg(column2) over (order by column1 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg
from tablename;

5. 分组内前n名
select * from (
	select column1, column2, column3, ROW_NUMBER() over (PARTITION BY column1 ORDER BY column2 DESC) as rn
	from tablename
) where rn <=3;

二、多表查询

1.表连接操作

1. 内连接
select t1.column1,t2.column2
from table1 t1
join table2 t2
on t1.column3 = t2.column2;

2. 左连接
select t1.column1,t2.column2
from table1 t1
left join table2 t2
on t1.column3 = t2.column2;

3. 右连接
select t1.clumn1,t2.column2
from table1 t1
right join table2 t2
on t1.column3 = t2.column3;

4. 全外连接
select t1.column1,t2.column2
from table1 t1
full outer join table2 t2
on t1.column3 = t2.column3;

5. 自连接
select t1.column as column1,t2.column as column2
from table1 t1
join table1 t2
on t1.column1 = t2.column2;

6. 交叉连接
select * from colors cross join sizes;

2.子查询

1. 标量子查询
select column1,(
 select count(*) 
 from tableB 
 where column2 = a.column2
) as cnt
from tableA a;

2. IN子查询
select column1
from tableA
where column2 in (
 select column2 from categories where name='electronics'
);

3. EXISTS子查询
select column1
from tableA a
where exists (
 select 1 
 from tableB 
 where column2=a.column2
);

4. 子查询作为派生表
select avg(sum) as avg
from (
 select sum(column2) as sum from tahlea group by column1
) as t;

5. 多条件子查询
select column1,column2
from tableA
where column2 > (
 select avg(column2) from tableA
);

3.联合查询

1. 去重联合
select column1
from tableA 
 union select column1 from tableB;

2. 不去重联合
select column1 
from tableA
 union all select column1 from tableB;