SQL 增删改查

SQL 增删改查

特别是条件查询(where, group by, order by等)

创建数据库

use test;
create table Monthly_Indicator(
	city_name varchar(20) not null, # varchar 可变长度字符串;  not null 非空
    month_key date not null,
    aqi int(4) not null default 0, # default 默认
    aqi_range varchar(20) not null default '-',
    air_quality varchar(20) not null default '-',
    pm25 float(6,2) not null default 0,
    pm10 float(6,2) not null default 0,
    so2 float(6,2) not null default 0,
    co float(6,2) not null default 0,
    no2 float(6,2) not null default 0,
    o3 float(6,2) not null default 0,
    ranking int(4) not null default 0,
    primary key(city_name,month_key) # primary key() 主键 要求唯一 不能空值
    );
 select * from Monthly_Indicator;

这里的效果,假装只有columns, 没有数据

“增” 导入数据

INSERT INTO 表名称 VALUES (值1, 值2,…)
INSERT INTO table_name (列1, 列2,…) VALUES (值1, 值2,…)

01 外部导入

load data local infile 'C:/Users/M/Desktop/all.txt'  #不能有中文,只能是单引号
	into table Monthly_Indicator # table名
    fields terminated by '\t' 
    ignore 1 lines; # 忽略源文件中的第一行

02 直接插入

insert into Student 
values
	('02' , '钱电' , '1990-12-21' , '男'),
    ('03' , '孙风' , '1990-05-20' , '男'),
    ('04' , '李云' , '1990-08-06' , '男'),
    ('05' , '周梅' , '1991-12-01' , '女');

“删”

01 删除行
DELETE FROM 表名称 WHERE 列名称 = 值

delete from Monthly_Indicator
where city_name="三亚";

02 删除所有行

truncate table Monthly_Indicator;#清空表内数据

03 删除列(column也一起删除)

Alter table Monthly_Indicator drop o3;

04 删数据库中的表

drop table emp;

“改”

01 修改数据
Update 语句用于修改表中的数据
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

update Monthly_Indicator
set air_quality="优"
where air_quality="良" and city_name="三门峡"

02 修改数据表属性

Create table emp01(
Depid char(3) primary key,
Depname varchar(20) not null default '-',
Peoplecount int unique
);

Alter table emp01 rename empded; # 修改表名
Alter table empded add maname varchar(10); # 添加column
Alter table empded modify depname varchar(30);# 修改已有column的属性
Alter table empded drop maname;#删除column

“查”

01 单表查询
单表查询比较简单, 只考虑一个表, 主要就是多重分组,排序

select <目标列组>
	from <table>
	where <选择条件>
	group by <分列组> [having <组选择条件>]
	order by <排序列1> [desc 降序];
	
select city_name, avg(pm25), avg(pm10)
from Monthly_Indicator
where pm25>50
# city_name, month_key 分组, 其中city_name不属于北京
group by city_name, month_key having city_name <> "北京"
#分组后select city_name, avg(pm25), avg(pm10)
#然后再按照avg(pm25) 降序排序
order by avg(pm25) desc; 

# 降序查找不同城市的pm10的平均值
select city_name, avg(pm25)
from Monthly_Indicator
group by city_name
order by avg(pm25) desc;

02 多表查询

需要学习在连接数据库中的表格时与主键和外键相关的关键要素：

• 主键 - 对于表格中的每行都是唯一的。主键通常是数据库中的第一列（就像 Parch & Posey 数据库中每个表格的 id 列）。
• 外键 - 是出现在另一个表格中的主键，允许行不是唯一的行。
  数据库的数据设置非常重要，但通常不是数据分析师的职责。这一过程称为数据库规范化。

多表查询是指将两个以上的数据表通过关键字段连接在一起, 并从不同表中获取不同字段进行查询.
那什么是关键字段呢?
通俗说 就是两表中值能匹配上的字段, column可以一致, 也可以不一致.
有以下必备条件:
1 两表都需要有关键字段
2 关键字段的值能匹配上

select <select_list>
from <左表> inner join <右表>
on 左表.key = 右表.key;

on 这一行, 如果两表中匹配字段的column不同名, 可以不加左表 右表的名字, 相同的话必须加
举个栗子:
这个例子是最完美的例子, 关键字段没有重复值, 没有缺失值, 一一对应.

select 学员信息表.*, 学员成绩表.*
from 学员信息表 left join 学员成绩表
on 学员信息表.学号 = 学员成绩表.学号;

另一个例子, 不是完美的匹配:
inner join 交集

select 学员信息表.*, 学员成绩表.*
from 学员信息表 inner join 学员成绩表
on 学员信息表.学号 = 学员成绩表.学号;

结果不是完美匹配, 有重复值, 也有信息不匹配的情况.
左表的第1个a 匹配右表的2个a, 左表的第2个a匹配右表的2个a.

left join 左连接
左表中有信息, 但右表汇总匹配不到的, 会自动填为空值.


案例解析

为每个销售代表对应的区域以及相关的客户创建一个表格，这次仅针对 Midwest 区域。最终表格应该包含三列：区域名称、销售代表姓名，以及客户名称。根据客户名称按字母顺序 (A-Z) 排序。

SELECT r.name region, s.name rep, a.name account
FROM sales_reps s
JOIN region r
ON s.region_id = r.id
JOIN accounts a
ON a.sales_rep_id = s.id
WHERE r.name = 'Midwest'
ORDER BY a.name;

为每个销售代表对应的区域以及相关的客户创建一个表格，这次仅针对 Midwest 区域，并且销售代表的名字以 S 开头。最终表格应该包含三列：区域名称、销售代表姓名，以及客户名称。根据客户名称按字母顺序 (A-Z) 排序。

SELECT r.name region, s.name rep, a.name account
FROM sales_reps s
JOIN region r
ON s.region_id = r.id
JOIN accounts a
ON a.sales_rep_id = s.id
WHERE r.name = 'Midwest' AND s.name LIKE 'S%'
ORDER BY a.name;

为每个销售代表对应的区域以及相关的客户创建一个表格，这次仅针对 Midwest 区域，并且销售代表的姓以 K 开头。最终表格应该包含三列：区域名称、销售代表姓名，以及客户名称。根据客户名称按字母顺序 (A-Z) 排序。

SELECT r.name region, s.name rep, a.name account
FROM sales_reps s
JOIN region r
ON s.region_id = r.id
JOIN accounts a
ON a.sales_rep_id = s.id
WHERE r.name = 'Midwest' AND s.name LIKE '% K%'
ORDER BY a.name;

提供每个订单的每个区域的名称，以及客户名称和所支付的单价 (total_amt_usd/total)。但是，只针对标准订单数量超过 100 的情况提供结果。最终表格应该包含三列：区域名称、客户名称和单价。为了避免除以 0 个订单，这里可以在分母上加上 0.01，即：(total_amt_usd/(total+0.01))。

SELECT r.name region, a.name account, o.total_amt_usd/(o.total + 0.01) unit_price
FROM region r
JOIN sales_reps s
ON s.region_id = r.id
JOIN accounts a
ON a.sales_rep_id = s.id
JOIN orders o
ON o.account_id = a.id
WHERE o.standard_qty > 100;

提供每个订单的每个区域的名称，以及客户名称和所支付的单价 (total_amt_usd/total)。但是，只针对标准订单数量超过 100 且广告纸数量超过 50 的情况提供结果。最终表格应该包含三列：区域名称、客户名称和单价。按照最低的单价在最之前排序。为了避免除以 0 个订单，这里可以在分母上加上 0.01，即：(total_amt_usd/(total+0.01))。

SELECT r.name region, a.name account, o.total_amt_usd/(o.total + 0.01) unit_price
FROM region r
JOIN sales_reps s
ON s.region_id = r.id
JOIN accounts a
ON a.sales_rep_id = s.id
JOIN orders o
ON o.account_id = a.id
WHERE o.standard_qty > 100 AND o.poster_qty > 50
ORDER BY unit_price;

提供每个订单的每个区域的名称，以及客户名称和所支付的单价 (total_amt_usd/total)。但是，只针对标准订单数量超过 100 且广告纸数量超过 50 的情况提供结果。最终表格应该包含三列：区域名称、客户名称和单价。按照最高的单价在最之前排序。为了避免除以 0 个订单，这里可以在分母上加上 0.01，即：(total_amt_usd/(total+0.01))。

SELECT r.name region, a.name account, o.total_amt_usd/(o.total + 0.01) unit_price
FROM region r
JOIN sales_reps s
ON s.region_id = r.id
JOIN accounts a
ON a.sales_rep_id = s.id
JOIN orders o
ON o.account_id = a.id
WHERE o.standard_qty > 100 AND o.poster_qty > 50
ORDER BY unit_price DESC;

account id 为 1001 的客户使用了哪些不同的渠道。最终表格应该包含 2 列：客户名称和不同的渠道。你可以尝试使用 SELECT DISTINCT 使结果仅显示唯一的值。

SELECT DISTINCT a.name, w.channel
FROM accounts a
JOIN web_events w
ON a.id = w.account_id
WHERE a.id = '1001';

找出发生在 2015 年的所有订单。最终表格应该包含 4 列：occurred_at、account name、order total 和 order total_amt_usd。

SELECT w.occurred_at, a.name, o.total, o.total_amt_usd
FROM accounts a
JOIN orders o
ON o.account_id = a.id
JOIN web_events w
ON a.id = w.account_id
WHERE w.occurred_at BETWEEN '01-01-2015' AND '01-01-2016'
ORDER BY w.occurred_at DESC;

03 联合查询 纵向合并

union 用于合并2个或多个select语句的结果集, 并消去表中的任何重复行.
union all 用于合并2个或多个select语句的结果集, 保留重复行.
两表中都有a 13 这里用union降只保留一个, 如果用union all 降保留所有的行.

select * from t1
union
select * from t2;