行专列

group by ,case ,sum

SELECT name,
sum(CASE WHEN class_name = '语文' then score end) 语文,
sum(CASE WHEN class_name = '数学' then score end) 数学,
sum(CASE WHEN class_name = '英语' then score end) 英语
FROM t_score GROUP BY name;

SELECT name,
sum(if(class_name='语文',score,0)) 语文,
sum(if(class_name='数学',score,0)) 数学,
sum(if(class_name='英语',score,0)) 英语
FROM t_score GROUP BY name;

查询出每组都大于80的数据

-- 该查询对于现有的数据进行查询
SELECT name
FROM t_score
GROUP BY name
HAVING MIN(score) > 80;

-- 这个查询的话对于数据没有录入完毕的话是不适用的；因为有个变量课程总数
SELECT name
FROM t_score
WHERE score > 80
GROUP BY name
HAVING COUNT(name) = 3;

-- 该查询是适用反向查询的，先查询出小于80分的人，然后用not in 将小于80分的人进行排除即可
SELECT DISTINCT name
FROM t_score
WHERE name NOT IN (SELECT DISTINCT name FROM t_score WHERE score <= 80);

-- 该查询最为繁琐，但是功能最为强大
SELECT *
FROM (SELECT name,
SUM(IF(class_name = '语文', score, 0)) 语文,
SUM(IF(class_name = '数学', score, 0)) 数学,
SUM(IF(class_name = '英语', score, 0)) 英语
FROM t_score
GROUP BY name) t
WHERE t.数学 > 80
AND t.语文 > 80
AND t.英语 > 80;

删除重复数据

用delete min/max聚合函数 group by (重复的字段1,重复的字段2) having count(1) >1

having 过滤分组后的条数，如果只有一条还被删掉就惨了

复制表结构

create table t_1 select * from t_2

复制表数据

insert into () select () from t_table

连表更新

update test1,test2
set test1.name=test2.name,test1.age=test2.age
where test1.id=test2.id;

-- 通用
update test1
set name=(select name from test2 where test2.id=test1.id),
age=(select age from test2 where test2.id=test1.id)