选择mysql开发的原因_MySQL开发技巧

如何进行行列转换

行转列

场景：报表统计(sum())、汇总显示

表数据：

select * from score;

希望达到的效果

cross join

SQL如下：

select a.student_name '学生名', a.score '语文', b.score '数学', c.score '英语' from(select student_name, score from score where course_name='语文') across join(select student_name, score from score where course_name='数学') bcross join(select student_name, score from score where course_name='英语') cwhere a.student_name = b.student_name and b.student_name = c.student_name;

使用case

但是使用case的时候达到的效果不好，如下：

SQL如下：

select student_name '学生名',case when course_name = '语文' then score end '语文',case when course_name = '数学' then score end '数学',case when course_name = '英语' then score end '英语'

from score;

解决方案：在case的基础上使用分组，并使用sum()函数，SQL如下：

selectstudent_name,sum(case

when course_name = '语文' thenscoreelse 0

end) '语文',sum(case

when course_name = '数学' thenscoreelse 0

end) '数学',sum(case

when course_name = '英语' thenscoreelse 0

end) '英语'

fromscoregroup by student_name;

列转行

场景：属性拆分

表数据：

select * from interest;

希望达到的效果

SQL如下：

--需要使用序列表处理列转行的数据

create tabletb_sequence(

idint primary keyauto_increment

);--取决于逗号分割的数据量，这里兴趣爱好最多的就四个，那就暂时插入4条数据

insert into tb_sequence values(), (), (), ();select student_name '学生名', replace(substr(substring_index(interesting, ',', a.id), char_length(substring_index(interesting, ',', a.id - 1)) + 1), ',', '') '兴趣爱好'

fromtb_sequence across join(select student_name, concat(interesting, ',') interesting, length(interesting) - length(replace(interesting, ',', '')) + 1 size frominterest) bon a.id <= b.size;

场景：多列转行

表数据：

select * from student_dress;

希望达到的效果：

union all

SQL如下：

select student_name, 'cap' as '类别', cap '名称' fromstudent_dressunion all

select student_name, 'clothing' as '类别', clothing '名称' fromstudent_dressunion all

select student_name, 'pants' as '类别', pants '名称' fromstudent_dressunion all

select student_name, 'shoe' as '类别', shoe '名称' from student_dress order by student_name;

使用case

希望达到的效果：

SQL如下：

select student_name '学生名', coalesce(case when b.id = 1 then cap end,case when b.id = 2 then clothing end,case when b.id = 3 then pants end,case when b.id = 4 then shoe end)'名称' from student_dress a cross join tb_sequence b where b.id <= 4 order by student_name;

希望添加类别上去：

SQL如下：

select student_name '学生名',case

when b.id = 1 then 'cap'

when b.id = 2 then 'clothing'

when b.id = 3 then 'pants'

when b.id = 4 then 'shoe' end '类别',coalesce(case

when b.id = 1 thencapwhen b.id = 2 thenclothingwhen b.id = 3 thenpantswhen b.id = 4 then shoe end)'名称' from student_dress a cross join tb_sequence b where b.id <= 4 order by student_name;

如何生成唯一序列号

场景：数据库主键、业务序列号如发票号、车票号、订单号等。。。

生成序列号的方法：

MySQL：AUTO_INCREMENT

SQLServer：INDENTIDYTY/SEQUENCE

Oracle：SEQUENCE

PgSQL： SEQUENCE

优先选择系统提供的序列号生成方式

在特殊情况下可以使用SQL方式生成序列号

如何删除重复数据

产生数据重复的原因：

人为原因，如复录入数据，重复提交等。。。

系统原因，由于系统升级或者设计的原因使原来可以重复的数据变为不重复了

如何查询数据是否重复：

利用group by和having从句处理

如何处理重复的数据：

删除重复的数据，对于相同数据保留ID最大的

--创建测试删除重复数据表

CREATE TABLE`test_repeat` (

`id`bigint(20) NOT NULLAUTO_INCREMENT,

`name`varchar(45) NOT NULL,PRIMARY KEY(`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;--执行至少两次，这里执行两次即可

insert into test_repeat(name) values('Jef');--查询出的内容为执行的上处插入的条数

select * from test_repeat where name = 'Jef';--1、先把需要删除的数据查出来：

select a.id, a.name from test_repeat a join(select name, count(*) cnt, max(id) maxIdfromtest_repeatgroup by name having cnt > 1) b on a.name = b.name where a.id

delete a from test_repeat a join(select name, count(*) cnt, max(id) maxIdfromtest_repeatgroup by name having cnt > 1) b on a.name = b.name where a.id

select * from test_repeat;

如何在子查询中匹配两个值

常见的子查询使用场景

使用子查询可以避免由于子查询中的数据产生的重复

例子：

查询在超市购买过商品的学生名

select student_name '学生名' from student where student_id in(select student_id from student_shopping);

实现了去重

但是如果不用子查询，使用内连接

select student_name '学生名' from student s join student_shopping sp on s.student_id = sp.student_id;

这样购买了几件商品就会显示几次

可以使用distinct去重

select distinct student_name '学生名' from student where student_id in(select student_id from student_shopping);

使用子查询更符合语意，更好理解

查询出每一个学生购物种类最多的日期，并列出学生名，购物日期，购物种类

SQL如下：

select a.student_name '学生名', b.buy_date '购买日期', b.maxNum '购买种类' from student a join (select student_id, max(num) maxNum, buy_date from student_shopping group by student_id) b on a.student_id = b.student_id;

多列过滤的使用场景：

MySQL中独有的多列过滤方式

SQL如下，效果跟上图一样：

select a.student_name '学生名', b.buy_date '购买日期', b.num '购买种类' from student a join student_shopping b on a.student_id =

b.student_id where (b.student_id, b.num) in (select student_id, max(num) from student_shopping group by student_id);

如何解决同一属性的多值过滤

什么是同一属性的多值过滤

查询出含有Java技能并且技能等级>3的学生名、技能和技能等级

SQL如下：

select a.student_name '学生名', b.skill_name '技能名称', b.skill_level '技能等级' from student a join student_skill b on a.student_id = b.student_id where b.skill_name = 'Java' and b.skill_level > 3;