SQL基础查询语句（二）

sql一定要多练习，这学期学校太忙，终于有时间了再把sql基础语句整理一遍，复杂的查询语句大多数都是这些基础语句的组合使用。

####################### 多表连接查询
# join    left join / right join / inner join
select a.*, b.province, b.area, c.category, c.small_category 
from spm_order as a
left join spm_area as b on a.city = b.city
left join spm_product as c on a.product_id = c.product_id
order by order_date

####################### 复杂查询
# 条件查询（case when）
select 
case when profit > 0 then '盈利'
		 when profit = 0 then '不盈不亏'
		 else '亏损'
end as '订单类型',
sum(sales) as '销售额'
from spm_order
group by 
case when profit > 0 then '盈利'
		 when profit = 0 then '不盈不亏'
		 else '亏损'
end

# 分组排序函数(row_number)
# row_number() 编号的功能
# partition by 将相同的数据分区
# order by     将数据按一定的顺序排序

# 计算销售人员的销售额，结果从高到低排序，包含销售的排名
select sales_name, sum(sales), row_number() over(order by sum(sales) desc) as 'rank'
from spm_order
group by sales_name

# 计算销售人员在不同城市的销售额，根据销售人员在不同城市的销售额进行分组排序，结果包含分组排名
select sales_name, city, sum(sales), row_number() over(partition by sales_name order by sum(sales) desc) as 'rank'
from spm_order
group by sales_name, city

# 嵌套查询

# 每个销售人员销售额top5的城市
select t1.* 
from (select sales_name, city, sum(sales), row_number() over(partition by sales_name order by sum(sales) desc) as 'rank'
from spm_order
group by sales_name, city) as t1 
where t1.rank between 1 and 5

# 子查询(in)
select 字段 
from 表名 
where 字段 in (select 字段 from 表名 where 条件)

# 组合查询(union)
-- select 字段
-- from 表名
-- union 
-- select 字段
-- from 表名
-- union 
-- select 字段
-- from 表名

-- 返回去重后的结果 union()
-- 不去重，返回所有 union all()

-- 查询利润大于0的订单
-- 查询城市在‘北上广深杭’的订单
-- 将两个查询结果合并

# 不去重
select * from spm_order where profit > 0
union all
select * from spm_order where city in ('北京','上海','广州','深圳','杭州');

# 去重，以下两句等价
select * from spm_order where profit > 0
union
select * from spm_order where city in ('北京','上海','广州','深圳','杭州');

select * from spm_order where profit > 0 or city in ('北京','上海','广州','深圳','杭州')

#####sql操作定义语句
#################### 创建表/库
create databases 库名
create table ()

#################### 插入行(insert) 增加行
# 插入一行
insert into 表名
values
(值1，值2，值3...)

# 指定列插入多行
insert into 表名
(字段1，字段2，字段3...)
values
(值1，值2，值3...)
(值1，值2，值3...)
(值1，值2，值3...)
(值1，值2，值3...)

#################### 更新表内数据(update)
update 表名
set 字段=新值
where 字段=旧值

# 把订单id为‘0002’的订单中，日期修改为‘2017/4/1’
update order_2017
set date = '2017/4/1'
where order_id = '0002'

#################### 更新表结构(alter) 增加列删除列
# 增加列
alter table 表名
add 列名 数据类型

# 在‘order_2017’表中增加实付金额‘spend’列
alter table order_2017
add spend double(10,2) # 定义字段数据类型

# 删除列
alter table 表名
drop 列名

# 在‘order_2017’表中删除实付金额‘spend’列
alter table order_2017
drop spend

#################### 删除库/表/行(delete/drop)
# 删除行
delete from 表名
where 条件

# 在表order_2017中删除订单id为‘0002’的订单
delete from order_2017
where order_id = '0002'

# 删除表
drop table 表名

# 删除名字为‘order_2017‘的表
drop table order_2017

# 跑路
drop databases 数据库名