修改密码

# 修改MySQL库的user表
UPDATE mysql.user SET authentication_string = PASSWORD("new-password");
FLUSH PRIVILEGES;

# 使用set语句修改密码
修改当前用户密码：
SET PASSWORD = PASSWORD("new-password");

# 修改其他用户密码：
SET PASSWORD FOR 'user'@'hostname' = PASSWORD("new-password");

# 使用ALTER USER语句修改用户密码
## 修改当前用户密码（USER()为获取当前连接用户的函数）：
ALTER USER USER() IDENTIFIED BY 'new-password';

## 修改其他用户的密码：
ALTER USER 'user'@'hostname' IDENTIFIED BY 'new-password';

时间范围

-- 指定特定日期
SELECT * FROM table_name WHERE DATE(create_at) = '2023-10-11';
-- 指定日期范围
SELECT * FROM table_name WHERE create_at BETWEEN '2023-10-10' AND '2023-10-12';
-- 查找指定年份的记录
SELECT * FROM table_name WHERE YEAR(create_at) = 2023;
-- 查找指定月份的记录
SELECT * FROM table_name WHERE MONTH(create_at) = 10;
-- 查找指定日期之后的记录
SELECT * FROM table_name WHERE create_at > '2023-10-11';

时间 列+30天

SELECT DATE_ADD(valid_end_datetime, INTERVAL 30 DAY) AS expired_day FROM ben_material_export_task;

指定修改后缀

https://example.com/upload/video/2d/32/7f/2d327f47faec8ec456f1f0a2c8060215.mpg
修改为 https://example.com/upload/video/2d/32/7f/2d327f47faec8ec456f1f0a2c8060215.avi

select DISTINCT(extension) from ben_video_file

UPDATE video_file
SET url = REPLACE(url, '.mpg', '.avi')
WHERE extension = 'avi';

找出重复

# SELECT video_id,name,md5, COUNT(*)
SELECT video_id,name,md5, COUNT(md5)
FROM video_file
GROUP BY video_id,md5
HAVING COUNT(*) > 1;

中英文括号替换

显示新列

SELECT REPLACE(REPLACE(column_name, '（', '('), '）', ')') AS replaced_column
FROM your_table;

修改数据

UPDATE your_table
SET column_name = REPLACE(REPLACE(column_name, '（', '('), '）', ')')
WHERE condition;

使用JOIN 子查询来获取每个 track_id 对应的最大sample_rate，

SELECT a.album_id, b.id as audio_id, b.track_id, b.name
FROM ben_song_extend as a
JOIN ben_song_audio as b ON a.id = b.track_id
JOIN (
  SELECT track_id, MAX(sample_rate) AS max_sample_rate
  FROM ben_song_audio
  WHERE bits_per_sample >= 16 AND sample_rate >= 44100
  GROUP BY track_id
) AS c ON b.track_id = c.track_id AND b.sample_rate = c.max_sample_rate
WHERE b.bits_per_sample >= 16 AND b.sample_rate >= 44100
AND b.track_id IN (1, 2, 3, 4);

SELECT a.album_id, b.id as audio_id, b.track_id, b.bits_per_sample, b.sample_rate, c.cover, c.album_name as name
FROM ben_song_extend as a
JOIN ben_song_audio as b ON a.id = b.track_id
JOIN ben_album as c ON a.album_id = c.id
WHERE b.sample_rate = (SELECT MAX(sample_rate) FROM ben_song_audio WHERE track_id = 2151)
AND b.bits_per_sample >= 16
AND b.track_id = 2151;

sql 表A和表B track_id 对应 ， 设置表A的bpm 等于 表B 的第一个

UPDATE tableA
SET bpm = (
    SELECT bpm
    FROM tableB
    WHERE tableB.track_id = tableA.track_id
    LIMIT 1
);

时长秒数转换为HH:MM:SS格式

# 查询
SELECT se.id AS song_extend_id, sa.track_id, sa.duration,sa.created_at, SEC_TO_TIME(sa.duration) AS formatted_duration
FROM ben_song_extend AS se
JOIN ben_song_audio AS sa ON se.id = sa.track_id where date(sa.created_at) = 20240227

# 更新
UPDATE ben_song_extend AS se
JOIN (
    SELECT sa.track_id, SEC_TO_TIME(sa.duration) AS formatted_duration
    FROM ben_song_audio AS sa
    WHERE date(sa.created_at) = '2024-02-27'
) AS formatted_audio
ON se.id = formatted_audio.track_id
SET se.track_duration = formatted_audio.formatted_duration;

通过a表更新b表

UPDATE ben_track_platform_onshelf AS b
JOIN ben_album AS a ON b.album_id = a.id
SET b.ddex_upc = a.isbn;

SELECT
    a.album_id,
    b.id,
    b.album_name,
    b.release_date 
FROM
    ben_track_platform_onshelf AS a
    RIGHT JOIN ben_album AS b ON b.id = a.album_id 
WHERE
    a.album_id IN ( 10262, 10263 ) 
    AND a.music_platform = 4 
    AND a.id > 6646

UPDATE ben_track_platform_onshelf AS a
RIGHT JOIN ben_album AS b ON b.id = a.album_id 
SET a.album_name = b.album_name, a.onshelf_date = b.release_date, a.ddex_upc = b.isbn
WHERE
    a.album_id IN (10262, 10263) 
    AND a.music_platform = 4 
    AND a.id > 6646;

解决group by 排序失效

select * from ben_song_audio as a 
left join (select * from ben_song_audio group by id  order by id desc)  as b 
on a.id =  b.id where b.track_id = 37112 group by b.track_id
# 需要where 条件

要按 music_platform 和 album_id 分组，并获取每个 album_id 中最大ID的数据

SELECT t1.*
FROM `ben_track_platform_onshelf` AS t1
right JOIN (
    SELECT music_platform, album_id, MAX(id) AS max_id
    FROM `ben_track_platform_onshelf`
    WHERE `deleted_at` IS NULL
    GROUP BY music_platform, album_id
) AS t2 ON t1.id = t2.max_id