1.创建数据库
1 create database if not exists shop default character set utf8mb4 default collate utf8mb4_unicode_ci;
2.查询数据库中重复数据
select column,count(*) as count from table group by column HAVING count > 1
2.2 查询数据库中多个字段联合的重复数据（如下：查询三个字段联合的重复数据）
SELECT * FROM ( SELECT *, CONCAT( c_id, '_', p_id, '_', u_id ) AS cpu FROM jw ) t WHERE t.cpu IN ( SELECT CONCAT( c_id, '_', p_id, '_', u_id ) AS cpu FROM jw GROUP BY cpu HAVING count( cpu ) > 1 ) ORDER BY t.cpu;
3.依据关联表字段数据更新主表字段，并且限制更新条数
UPDATE a INNER JOIN b ON b.order_id = a.id SET a.target_id = b.target_id, a.item_type = b.type WHERE a.id in (select id from (SELECT id from awhere target_id = 0 order by id limit 100) sub1);
4.删除重复行，仅保留一行
DELETE FROM table as ta WHERE ta.id <> ( select t.maxid from (select max(tb.id) as maxid from table as tb where ta.order_id = tb.order_id) t );
执行时间会很长，可以对字段加索引
4.1、分组重复数据处理
查出三字段联合后的分组中 重复的数据，并将分组后 按last_time_point 排序，标记序号 SELECT *, ROW_NUMBER() over (partition by cpu order by last_time_point desc) as rn FROM ( SELECT *, CONCAT( user_id, '_', course_id, '_', period_id ) AS cpu FROM user_study_schedules where id > 1*1000000 and id <= 2*1000000 ) t WHERE t.cpu IN ( SELECT CONCAT( user_id, '_', course_id, '_', period_id ) AS cpu FROM user_study_schedules where id > 1*1000000 and id <= 2*1000000 GROUP BY cpu HAVING count( cpu ) > 1 ); 查出三字段联合后的分组中 重复的数据，并将分组后 按last_time_point 排序，标记序号，仅查出序号大于1的行 select * from ( SELECT * , ROW_NUMBER() over (partition by cpu order by last_time_point desc) as rn FROM ( SELECT *, CONCAT( user_id, '_', course_id, '_', period_id ) AS cpu FROM user_study_schedules where id > 1*1000000 and id <= 2*1000000 ) t WHERE t.cpu IN ( SELECT CONCAT( user_id, '_', course_id, '_', period_id ) AS cpu FROM user_study_schedules where id > 1*1000000 and id <= 2*1000000 GROUP BY cpu HAVING count( cpu ) > 1 ) ) w where w.rn > 1; 查出三字段联合后的分组中 重复的数据，并将分组后 按last_time_point 排序，标记序号，仅查出序号大于1的行，删除这些数据 update user_study_schedules set deleted_at = '2024-05-09 14:14:14' where id in ( select w.id from ( SELECT * , ROW_NUMBER() over (partition by cpu order by last_time_point desc) as rn FROM ( SELECT *, CONCAT( user_id, '_', course_id, '_', period_id ) AS cpu FROM user_study_schedules where id > 1*1000000 and id <= 2*1000000 ) t WHERE t.cpu IN ( SELECT CONCAT( user_id, '_', course_id, '_', period_id ) AS cpu FROM user_study_schedules where id > 1*1000000 and id <= 2*1000000 GROUP BY cpu HAVING count( cpu ) > 1 ) ) w where w.rn > 1);
5.select case赋值
1 select name, (case sex when 0 then '未知' when 1 then '男' when 2 then '女') as '性别' from users;
6.select concat字符串处理
select title, concat('http://host/?u=', type) as url from address;
7.update replace替换字段
update table set url = replace(url, 'qq.com', 'aa.com');
8.where json查询
select * from table where json->'$.oid' = 2;
9.update json字段
update table set json = json_set(json, '$.oid', 3);
或
update table set json = json_object('oid', 3);
10.You can't specify target table '表名' for update in FROM clause
翻译：不能先select出同一表中的某些值，再update这个表(在同一语句中)
处理方法：将查询结果单独作为结果集
update table set type = 1,type_symbol = 'aa' where order_id in (select id from (select orders.id from orders left join table on table.order_id = orders.id where orders.item_type = 2 and table.type != 1) t);
11.查询表字段
show full fields from table;
12.表添加字段
ALTER TABLE `database`.`table` ADD COLUMN `column` tinyint(1) UNSIGNED NULL DEFAULT 1 COMMENT '备注' AFTER `column0`;
13.结果集排序
/*取积分最高的前10条数据*/ SELECT id,nickname,score from users order by score desc limit 10; 等效于 SELECT id,nickname,score from users order by score desc limit 0, 10; 此处可看到limit用法，limit i,n表示返回查询结果集中游标从i开始的n行数据（游标起始为0）。 /*给结果集加上序号*/ SELECT (@i:=@i+1) as rowno,id,nickname,score from users, (select @i:=0) as rowno order by score desc limit 0, 10; /*取第二行数据，limit 1, 1*/ SELECT (@i:=@i+1) as rowno,id,nickname,total_revenue from users, (select @i:=1) as rowno order by total_revenue desc limit 1,1; /*取第三行数据，limit 2, 1*/ SELECT (@i:=@i+1) as rowno,id,nickname,total_revenue from users, (select @i:=2) as rowno order by total_revenue desc limit 2,1;
14.分数名次排名，相同的分数占据位次一样，后续连续不隔断
增加一个变量，用于记录上一条数据的分数，只要当前数据分数跟上一条数据的分数比较，相同分数的排名就不变，不相同分数的排名就加一，并且更新变量的分数值为该条数据的分数，依次比较
select Score, (select count(distinct(Score)) from Scores as t where t.Score >= s.Score) as `Rank` from Scores as s order by Score desc;
以下sql不严谨，有误
SELECT Score, (case when @currentScore = Score then @i when @currentScore := Score then @i := @i +1 end) as `rank` from test, (select @i := 0, @currentScore := null) as t order by Score desc;
15.各种连接join
https://www.cnblogs.com/fudashi/p/7491039.html
inner join
left join
right join
outer join
full join
cross join
16.第N高的薪水，因为offset不支持运算，必须是定值，故提前得出游标
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGIN set N:=N-1; RETURN ( select (select distinct Salary from Employee order by Salary desc limit 1 offset N) as SecondHightSalary ); END
17.连续出现的数字，查找所有至少连续出现三次的数字，结果去重
select distinct(Num) as ConsecutiveNums from (select Num, case when @current = Num then @t:=@t+1 when @current := Num then @t:=1 end as times from Logs, (select @t:=1, @current:=null) as tmp) as tmp2 where times >= 3;
或
SELECT DISTINCT l1.Num AS ConsecutiveNums FROM Logs l1, Logs l2, Logs l3 WHERE l1.Id = l2.Id - 1 AND l2.Id = l3.Id - 1 AND l1.Num = l2.Num AND l2.Num = l3.Num ;
