取经四人组SQL
一.表结构与数据
- 1.
create table user1 (id int not null,user_name varchar(20) not null,over varchar(20) default null) comment '师徒四人表'; - 2.
create table user2 (id int not null,user_name varchar(20) not null,over varchar(20) default null) comment '悟空兄弟表'; - 3.插入数据
insert into user1 (id,user_name) values (1,'唐僧'),(2,'猪八戒'),(3,'孙悟空'),(4,'沙僧');
insert into user2 (id,user_name) values (1,'孙悟空'),(2,'牛魔王'),(3,'蛟魔王'),(4,'鹏魔王'),(5,'狮驼王');
- 4.更新字段
update user1 set over='旃檀功德佛' where user_name='唐僧';
update user1 set over='净坛使者' where user_name='猪八戒';
update user1 set over='斗战神佛' where user_name='孙悟空';
update user1 set over='金身罗汉' where user_name='沙僧';
- 5.更新字段
update user2 set over='成佛' where user_name='孙悟空';
update user2 set over='被降服' where user_name='牛魔王';
update user2 set over='被降服' where user_name='蛟魔王';
update user2 set over='被降服' where user_name='鹏魔王';
update user2 set over='被降服' where user_name='狮驼王';
- 6.师徒杀怪表
create table user_kills (id int not null,user_id int not null, timestr DATETIME not null,kils int not null) comment '师徒杀怪表';
insert into user_kills values(1,2,'2013-01-10 00:00:00',10);
insert into user_kills values(2,2,'2013-02-01 00:00:00',2);
insert into user_kills values(3,2,'2013-02-05 00:00:00',12);
insert into user_kills values(4,4,'2013-01-10 00:00:00',3);
insert into user_kills values(5,4,'2013-02-11 00:00:00',5);
insert into user_kills values(6,4,'2013-02-06 00:00:00',1);
insert into user_kills values(7,3,'2013-01-11 00:00:00',20);
insert into user_kills values(8,3,'2013-02-12 00:00:00',10);
insert into user_kills values(9,3,'2013-02-07 00:00:00',17);
- 7.修正字段
alter table user_kills change kils kills int;
二.连接的几种方式
- 1.inner join，基于连接谓词将两张表(A表，B表)的列组合在一起，产生新的结果表
select colname from table a inner join table b on a.key=b.key;
- 需求：查询A/B表中的公共部分,查询同时存在user1和user2的用户信息
select user1.user_name,user1.over,user2.over from user1,user2 where user1.user_name=user2.user_name;
select a.user_name,a.over,b.over from user1 a inner join user2 b where a.user_name=b.user_name;
select a.user_name,a.over,b.over from user1 a inner join user2 b on a.user_name = b.user_name;
select a.user_name,a.over,b.over from user1 a,user2 b where a.user_name=b.user_name and a.user_name in (select user_name from user2);
select a.user_name,a.over,b.over from user1 a left join user2 b on a.user_name=b.user_name where a.user_name in (select user_name from user2);
select a.user_name,a.over,b.over from user1 a left join user2 b on a.user_name=b.user_name where b.user_name is not null;
- 2.left join，以左边的表（A/B)为基准，将A表所有结果集显示
select colname from table a left join table b on a.key=b.key;
select colname from table a left join table b where a.key=b.key where b.key is null;
- 需求：只显示存在user1不存在user2的结果集
可以使用in，但是in是不走索引的
select a.user_name,a.over,b.over from user1 a left join user2 b on a.user_name=b.user_name where a.user_name not in (select user_name from user2);
select a.user_name,a.over,b.over from user1 a left join user2 b on a.user_name=b.user_name where b.user_name is null;
- 3.right join，以右边的表（A/B)为基准，将B表所有结果集显示
select colname from table a right join table b on a.key=b.key;
elect colname from table a right join table b on a.key=b.key where a.key is null;
- 需求：只显示只存在user2不存在user1的结果集
select b.user_name,a.over,b.over from user1 a right join user2 b on a.user_name=b.user_name where b.user_name not in (select user_name from user1);
select b.user_name,a.over,b.over from user1 a right join user2 b on a.user_name=b.user_name where a.user_name is null;
- 4.full outer，取并集或者并集的异
并集：select colname from table a full join table b on a.key=b.key;
异：select colname from table a full join table b on a.key=b.key where a.key is null or b.key is null;
select * from user1 a full join user2 b on a.user_name=b.user_name;
mysql不支持全连接，MSSQL和oracle支持
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'full join user2 b on a.user_name=b.user_name' at line 1
- 使用union all，先左连接后右连接再取并集
select a.user_name,a.over,b.over from user1 a left join user2 b on a.user_name=b.user_name
union all
select b.user_name,b.over,a.over from user1 a right join user2 b on a.user_name=b.user_name;
union和union all
1)必须具有相同列数，和能转换的列字段类型
2)union去重进行默认排序
3)union包含重复行，不排序
- 5.corss，笛卡尔积，N行*M行结果集
select * from user1 a cross join user2 b;
三.行列转换
1.行转列
应用场景：报表统计
- 需求：求杀怪总数-行转列例子：
1)第一步：先求每个人杀怪数量
select a.user_name,b.kills from user1 a join user_kills b on a.id=b.user_id;
2)第二步：再分组汇总成行
select a.user_name,sum(kills) from user1 a join user_kills b on a.id=b.user_id group by a.user_name;
3)第三步：取聚合函数别名
select a.user_name,sum(c.kills) from user1 a join user_kills c on a.id=c.user_id and a.user_name='孙悟空';
4)第四步：最后行转列
select sum(c.kills) as '孙悟空' from user1 a join user_kills c on a.id=c.user_id and a.user_name='孙悟空';
5)第五步：1行乘以1行还是1行，数量越大花费成本越大，不常用
select * from (
select sum(kills) as '孙悟空' from user1 a join user_kills c on a.id=c.user_id and a.user_name='孙悟空')
x cross join
(select sum(kills) as '猪八戒' from user1 a join user_kills c on a.id=c.user_id and a.user_name='猪八戒')
y cross join
(select sum(kills) as '沙僧' from user1 a join user_kills c on a.id=c.user_id and a.user_name='沙僧') z;
- 更通用的是case语句
case when colname1 then colname2 else xxx end 当满足colname1条件，返回coname2并结束
select sum(case when user_name='孙悟空' then kills end) as '孙悟空',sum(case when user_name='猪八戒' then kills end ) as '猪八戒',sum(case when user_name='沙僧' then kills end) as '沙僧' from user1 a join user_kills b on a.id=b.user_id;
2.另一个行转列
应用场景：1)属性拆分
应用场景：2)ETL数据导入导出
- 需求：将user1中的每个人的电话转成行
增加数据
alter table user1 add column mobile varchar(100);
update user1 set mobile='121123456,141123456,151123456' where id=1;
update user1 set mobile='12144643321,14144643321' where id=2;
update user1 set mobile='12146666666,14166666666,16166666666,18166666666' where id=3;
update user1 set mobile='12198765432,14198765432' where id=4;
原表数据全部在一列中显示：
root@master 14:53: [db1]> select * from user1;
+----+-----------+-----------------+-------------------------------------------------+
| id | user_name | over | mobile |
+----+-----------+-----------------+-------------------------------------------------+
| 1 | 唐僧 | 旃檀功德佛 | 121123456,141123456,151123456 |
| 2 | 猪八戒 | 净坛使者 | 12144643321,14144643321 |
| 3 | 孙悟空 | 斗战神佛 | 12146666666,14166666666,16166666666,18166666666 |
| 4 | 沙僧 | 金身罗汉 | 12198765432,14198765432 |
+----+-----------+-----------------+-------------------------------------------------+
4 rows in set (0.00 sec)
- 方法：引用序列表：只存序列号的表，就是单列多行表
create table tb_sequence(id int auto_increment not null,primary key(id));
insert into tb_sequence values(),(),(),(),(),(),(),(),();
#1个括号代表插入1个序列
1)第一步：增加逗号，计算号码个数，几个逗号就几个号码
select user_name,concat(mobile,',') as mobile,length(mobile)-length(replace(mobile, ',' , ''))+1 size from user1 b;
concat(mobile,',') 字符连接，给mobile字段的值后面增加1个逗号
length(replace(mobile, ',' , '') 将出现的,号全部剪掉
replace(object,search,replace) 把object中出现search的全部替换为replace
length(mobile)-length(replace(mobile, ',' , ''))+1，有逗号的长度减去没有逗号的长度，求个数
2)第二步：根据个数交叉连接序列表，生成多行的结果集
select *
from tb_sequence a
cross join (
select user_name,concat(mobile,',') as mobile,length(mobile)-length(replace(mobile, ',' , ''))+1 size from user1 b)
b on a.id <= b.size;
3)第三步：
select user_name,replace(substring(substring_index(mobile,',',a.id),char_length(substring_index(mobile,',',a.id-1))+1),',','') as mobile
from tb_sequence a
cross join (
select user_name,concat(mobile,',') as mobile,length(mobile)-length(replace(mobile, ',' , ''))+1 size from user1 b)
b on a.id <= b.size;
3.列转行
增加数据：
create table user1_equipment ( id int, user_id int,arms varchar(10),clothing varchar(10),shoe varchar(10));
insert into user1_equipment values(1,3,'金箍棒','锁子黄金甲','藕丝步云履');
insert into user1_equipment values(2,2,'九齿钉耙','僧衣','僧鞋');
insert into user1_equipment values(3,4,'降妖宝杖','僧衣','僧鞋');
insert into user1_equipment values(4,1,'九环锡杖','锦斓袈裟','僧鞋');
- 第一种方法：
基础表
select a.user_name,arms,clothing,shoe from user1 a join user1_equipment b on a.id=b.user_id;
+-----------+--------------+-----------------+-----------------+
| user_name | arms | clothing | shoe |
+-----------+--------------+-----------------+-----------------+
| 孙悟空 | 金箍棒 | 锁子黄金甲 | 藕丝步云履 |
| 猪八戒 | 九齿钉耙 | 僧衣 | 僧鞋 |
| 沙僧 | 降妖宝杖 | 僧衣 | 僧鞋 |
| 唐僧 | 九环锡杖 | 锦斓袈裟 | 僧鞋 |
+-----------+--------------+-----------------+-----------------+
4 rows in set (0.00 sec)
1)将一列转成行
select a.user_name,'arms' as equipment,arms from user1 a join user1_equipment b on a.id=b.user_id;
select a.user_name,'clothing' as equipment,clothing from user1 a join user1_equipment b on a.id=b.user_id;
select a.user_name,'shoe' as equipment,shoe from user1 a join user1_equipment b on a.id=b.user_id;
2)连接不同的结果集用union
select a.user_name,'arms' as equipment,arms from user1 a join user1_equipment b on a.id=b.user_id
union all
select a.user_name,'clothing' as equipment,clothing from user1 a join user1_equipment b on a.id=b.user_id
union all
select a.user_name,'shoe' as equipment,shoe from user1 a join user1_equipment b on a.id=b.user_id
order by user_name;
- 第二种方法：
使用序列号实现行转列
基础表
select a.user_name,arms,clothing,shoe from user1 a join user1_equipment b on a.id=b.user_id;
+-----------+--------------+-----------------+-----------------+
| user_name | arms | clothing | shoe |
+-----------+--------------+-----------------+-----------------+
| 孙悟空 | 金箍棒 | 锁子黄金甲 | 藕丝步云履 |
| 猪八戒 | 九齿钉耙 | 僧衣 | 僧鞋 |
| 沙僧 | 降妖宝杖 | 僧衣 | 僧鞋 |
| 唐僧 | 九环锡杖 | 锦斓袈裟 | 僧鞋 |
+-----------+--------------+-----------------+-----------------+
4 rows in set (0.00 sec)
1)需要将1行转换成3行
select user_name,arms,clothing,shoe from user1 a join user1_equipment b on a.id=b.user_id
cross join
tb_sequence c where c.id <= 3 order by user_name;
2)使用case只取一次值
select user_name,
case when c.id=1 then arms end,
case when c.id=2 then clothing end,
case when c.id=3 then shoe end
from user1 a join user1_equipment b on a.id=b.user_id
cross join
tb_sequence c where c.id <= 3 order by user_name;
3)使用coalesce取非空值
select user_name,
coalesce(case when c.id=1 then arms end,
case when c.id=2 then clothing end,
case when c.id=3 then shoe end) as equiment
from user1 a join user1_equipment b on a.id=b.user_id
cross join
tb_sequence c where c.id <= 3 order by user_name;
4)增加额外列
select user_name,
case
when c.id=1 then 'arms'
when c.id=2 then 'clothing'
when c.id=3 then 'shoe'
end as equiment,
coalesce(case when c.id=1 then arms end,
case when c.id=2 then clothing end,
case when c.id=3 then shoe end) as equiment
from user1 a join user1_equipment b on a.id=b.user_id
cross join
tb_sequence c where c.id <= 3 order by user_name;
三.生成唯一序列号
-
1.数据库主键
业务序列号 发票号 车票号 订单号
优先选择系统提供的序列号生成方式
create table t (id int auto_increment not null, primary key(id));
可能会产生跳号问题 -
2.特殊情况下可以使用sql方式生产序列号abc123abc
需求：生成订单号，并且订单号的格式如下
YYYYMMDDNNNNNNNN:201505120000003
四.删除重复数据
人为：重复录入，重复提交
系统：升级、设计，可以重复的时候不可以使用
- 如何查询数据是否重复--利用group by和having
create table user1_test( id int auto_increment not null,
user_name varchar(3),
over varchar(5),
mobile varchar(100),
primary key(id));
insert into user1_test(user_name,over,mobile) select user_name,over,mobile from user1;
insert into user1_test(user_name,over,mobile) from user1 limit 2;
select user_name,count(*) from user1_test group by user_name having count(*) >1;
select user_name,over,count(*) from user1_test group by user_name,over having count(*) >1;
删除重复数据--对于相同数据保留ID最大者
delete a
from user1_test a join (
select user_name,count(*) ,max(id) as id
from user1_test
group by user_name having count(*) > 1)
b on a.user_name=b.user_name
where a.id < b.id
- 如何删除上表的重复值？
列转行，删除重复值
五.如何在子查询中匹配两个值
子查询：当一个查询是另一个查询的条件时，称之为子查询
场景：
尽量使用连接替代子查询，子查询某些情况下查询速度并不好
1.使用子查询可以避免由于子查询中的数据产生的重复
- 需求：谁在取经路上打了怪
select user_name from user1 where id in (select user_id from user_kills);
user_id 在user_kills中是重复出现，但是整体结果集并没有重复
改成使用连接方式
select a.user_name from user1 a join user_kills b on a.id=b.user_id;
#连接方式可能会存在1对多或者多对多情况，需要去重：
select distinct a.user_name from user1 a join user_kills b on a.id=b.user_id;
2.使用子查询更符合语义，更好理解
- 需求：查询出每一个取经人打怪最多的日期，并列出取经人的姓名，打怪最多的日期和打怪数量
- 1)先求最大的打怪数量
select user_id,max(kills) cnt from user_kills group by user_id;
- 2)根据打怪数量过来日期
select a.user_name,b.timestr,kills
from user1 a join user_kills b on a.id=b.user_id
join (select user_id,max(kills) as cnt from user_kills group by user_id)
c on b.user_id=c.user_id and b.kills=c.cnt
- 改成子查询方式：mysql独有的多列过滤方式
select a.user_name,b.timestr,kills from user1 a
join user_kills b
on a.id=b.user_id
where (b.user_id,b.kills ) in(
select user_id,max(kills)
from user_kills
group by user_id);
六.解决同属性多过滤的问题
create table user1_skills (user_name varchar(20),skill varchar(20),skill_level int);
alter table user1_skills change user_name user_id int;
insert into user1_skills values(1,'紧箍咒',5);
insert into user1_skills values(1,'打坐',4);
insert into user1_skills values(1,'念经',5);
insert into user1_skills values(1,'变化',0);
#
insert into user1_skills values(2,'变化',4);
insert into user1_skills values(2,'腾云',3);
insert into user1_skills values(2,'浮水',5);
insert into user1_skills values(2,'念经',0);
insert into user1_skills values(2,'紧箍咒',0);
#
insert into user1_skills values(3,'变化',5);
insert into user1_skills values(3,'腾云',5);
insert into user1_skills values(3,'浮水',3);
insert into user1_skills values(3,'念经',2);
insert into user1_skills values(3,'请神',5);
insert into user1_skills values(3,'紧箍咒',0);
#
insert into user1_skills values(4,'变化',2);
insert into user1_skills values(4,'腾云',2);
insert into user1_skills values(4,'浮水',4);
insert into user1_skills values(4,'念经',1);
insert into user1_skills values(4,'紧箍咒',0);
- 需求：查询出同时具有变化和念经两项技能的取经人
1.不能得出结果的SQL:
select a.user_name,b.skill,b.skill_level from user1 a join user1_skills b on a.id=b.user_id where b.skill in('变化','念经') and b.skill_level > 0;
in是or的判断，因此只要符合条件之一就出结果
select a.user_name,b.skill,b.skill_level from user1 a join user1_skills b on a.id=b.user_id where ( b.skill ='变化' or b.skill ='念经' ) and b.skill_level > 0;
2.两次关联同一张表
select a.user_name,b.skill,c.skill
from user1 a
join user1_skills b
on a.id=b.user_id and b.skill='变化'
join user1_skills c
on a.id=c.user_id and c.skill='念经'
where c.skill_level >0 and b.skill_level >0 ;
#
select a.user_name,b.skill,c.skill
from user1 a
join user1_skills b
on a.id=b.user_id
join user1_skills c
on a.id=c.user_id and c.skill='念经'
where b.skill='变化' and c.skill='念经' and c.skill_level >0 and b.skill_level >0 ;
- 需求：查询出同时具有变化和念经、腾云三项技能的取经人
select a.user_name,b.skill,c.skill,d.skill
from user1 a
join user1_skills b
on a.id=b.user_id
join user1_skills c
on a.id=c.user_id
join user1_skills d
on a.id=d.user_id
where d.skill='腾云' and b.skill='变化' and c.skill='念经' and c.skill_level >0 and b.skill_level >0 and d.skill_level >0 ;
#
select a.user_name,b.skill,c.skill,d.skill
from user1 a
join user1_skills b
on a.id=b.user_id and b.skill='变化'
join user1_skills c
on a.id=c.user_id and c.skill='念经'
join user1_skills d
on a.id=d.user_id and d.skill='腾云'
where c.skill_level >0 and b.skill_level >0 and d.skill_level >0 ;
#
select a.user_name,b.skill,c.skill,d.skill
from user1 a
join user1_skills b
on a.id=b.user_id and b.skill='变化' and b.skill_level >0
join user1_skills c
on a.id=c.user_id and c.skill='念经' and c.skill_level >0
join user1_skills d
on a.id=d.user_id and d.skill='腾云' and d.skill_level >0 ;
- 需求：在100个技能查询出同时具有变化和念经、腾云、浮水四项技能的取经人？
select a.user_name,b.skill,c.skill,d.skill,e.skill
from user1 a
join user1_skills b
on a.id=b.user_id and b.skill='变化' and b.skill_level >0
join user1_skills c
on a.id=c.user_id and c.skill='念经' and c.skill_level >0
join user1_skills d
on a.id=d.user_id and d.skill='腾云' and d.skill_level >0
join user1_skills e
on a.id=e.user_id and e.skill='浮水' and e.skill_level >0 ;
#
#left join会有null值：
select a.user_name,b.skill,c.skill,d.skill,e.skill
from user1 a
left join user1_skills b
on a.id=b.user_id and b.skill='变化' and b.skill_level >0
left join user1_skills c
on a.id=c.user_id and c.skill='念经' and c.skill_level >0
left join user1_skills d
on a.id=d.user_id and d.skill='腾云' and d.skill_level >0
left join user1_skills e
on a.id=e.user_id and e.skill='浮水' and e.skill_level >0 ;
- 需求：在100个技能查询出同时具有变化和念经、腾云、浮水任意两项技能的取经人？
select a.user_name,b.skill,c.skill,d.skill,e.skill
from user1 a
left join user1_skills b
on a.id=b.user_id and b.skill='变化' and b.skill_level >0
left join user1_skills c
on a.id=c.user_id and c.skill='念经' and c.skill_level >0
left join user1_skills d
on a.id=d.user_id and d.skill='腾云' and d.skill_level >0
left join user1_skills e
on a.id=e.user_id and e.skill='浮水' and e.skill_level >0
where (case when b.skill is not null then 1 else 0 end) +
(case when c.skill is not null then 1 else 0 end) +
(case when d.skill is not null then 1 else 0 end) +
(case when e.skill is not null then 1 else 0 end) >= 2;
- 实际上只需要user_name
select a.user_name
from user1 a
left join user1_skills b
on a.id=b.user_id and b.skill='变化' and b.skill_level >0
left join user1_skills c
on a.id=c.user_id and c.skill='念经' and c.skill_level >0
left join user1_skills d
on a.id=d.user_id and d.skill='腾云' and d.skill_level >0
left join user1_skills e
on a.id=e.user_id and e.skill='浮水' and e.skill_level >0
where (case when b.skill is not null then 1 else 0 end) +
(case when c.skill is not null then 1 else 0 end) +
(case when d.skill is not null then 1 else 0 end) +
(case when e.skill is not null then 1 else 0 end) >= 2;
- 使用group by过滤同一属性多值的情况更通用：
select a.user_name
from user1 a
join user1_skills b on a.id=b.user_id
where b.skill in('念经','变化','腾云','浮水') and b.skill_level > 0
group by
a.user_name having count(*) >=2 ;
七.如何计算累进税问题
alter table user1 add column money float(10,2);
update user1 set money=35000.00 where id=1;
update user1 set money=15000.00 where id=2;
update user1 set money=28000.00 where id=3;
update user1 set money=8000.00 where id=4;
create table taxRate(id int not null auto_increment primary key,low float(10,2) not null,high float(10,2) not null,rate float(2,2) not null);
insert into taxRate(low,high,rate) values
(0.00,1500.00.0.03),
(1500.00,4500.00,0.1),
(4500.00,9000.00,0.2),
(9000.00,35000.00,0.25),
(35000.00,55000.00,0.3),
(55000.00,80000.00,0.35),
(80000.00,9999999.00,0.45);
- 需求：每个取经人缴税金额是多少？
1)使用join实现工资读不同纳税区间的匹配
select a.user_name,a.money,b.low,b.high,b.rate from user1 a join taxRate b on a.money > b.low order by user_name;
2)利用least()函数确定每个区间的纳税额
select a.user_name,a.money,b.low,b.high,
least(money - low,high - low) as curmoney,b.rate from
user1 a join taxRate b
on a.money > b.low order by a.user_name,curmoney;
3)每个区间缴税金额求和，就是每个人该缴的税
select user_name,sum(curmoney*rate) as tax from (
select user_name,money,low,high,least(money-low,high-low)as curmoney,rate
from user1 a join taxRate b on a.money > b.low) a
group by user_name;
