mysql 常用语句整理(一)
开发手册：https://dev.mysql.com/doc/refman/8.0/en/information-functions.html
1. 替换某个字段中的部分内容
SET @oldlink1 = 'http://192.168.100.66';
SET @newlink1 = 'www.baidu.com';
UPDATE Test1
SET Content = (
REPLACE (
Content ,@oldlink1 ,@newlink1
)
)
WHERE
Content LIKE concat('%' ,@oldlink1, '%');
2.连表删除数据
SET @UnitId = '27542'; DELETE t1 FROM Test1 AS t1 INNER JOIN Test2 AS t2 ON t1.Code = t2.Code WHERE t2.UnitId NOT IN (@UnitId);
3.连表插入数据
INSERT INTO Test ( ID, PostTime ) SELECT UUID() AS ID, t1.PostTime FROM Test1 AS t1 INNER JOIN Test2 AS t2 ON t1.Code = t2.Code;
4. 连表更新数据
UPDATE Test1 AS t1 INNER JOIN Test2 AS t2 ON t1.Code = t2.Code SET t1.NextFlowNodeId = 72 WHERE t1.ProcessStepId = 70;
5.获取某个库下的所有表名
select table_name from information_schema.tables where table_schema=@dbName
6.获取某个表的所有字段名
select COLUMN_NAME from information_schema.COLUMNS where table_name = @tableName and table_schema = @dbName;
7.CONCAT 拼接字符串
SET @SearchText= CONCAT（'%'，'测试','%'）； SELECT * FROM Test3 WHERE CONCAT(ItemName,ItemNumber,DeptName,RealName) LIKE @SearchText；
注意：若拼接项中存在NULL值，结果为NULL。如 CONCAT('测试','001',NULL,'小明'),结果为NULL，并不是我们想要的结果："测试001小明"
8.删除本表重复数据（You can’t specify target table for update in FROM clause，嵌套一层处理）
DELETE
FROM
A
WHERE
id IN (
SELECT
tt.*
FROM
(
SELECT
t1.id
FROM
A AS t1
WHERE
objectid = 'S'
AND Enabled = 0
AND EXISTS (
SELECT
*
FROM
A AS t2
WHERE
objectid = 'S'
AND t1.UserId = t2.UserId
GROUP BY
UserId
HAVING
COUNT(id) >= 2
)
) AS tt
);
9.生成序号
SELECT (@row_number :=@row_number + 1) AS num, SubjectCode, MemberID, Type, Sort FROM co_edu_subject.co_member, (SELECT @row_number := 0) AS t ORDER BY SubjectCode, Type, PostTime
示例：
10.生成分组序号
SELECT @row_number := CASE WHEN @sub_type = CONCAT(SubjectCode, Type) THEN @row_number + 1 ELSE 0 END AS num, @sub_type := CONCAT(SubjectCode, Type) AS sub_type, SubjectCode, MemberID, Type, Sort FROM co_edu_subject.co_member, ( SELECT @row_number := 0 ,@sub_type := '' ) AS t ORDER BY SubjectCode, Type, PostTime
示例：
10.批量更新数据表编码
SELECT CONCAT( 'ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;' ) AS runSql FROM information_schema.`TABLES` WHERE TAbLE_TYPE = 'BASE TABLE'
11 导出
11.1 导出单个数据库结构 mysqldump -h192.168.1.10 -uroot -p -d db1 >xxx.sql
11.2 导出多个数据库结构 mysqldump -h192.168.1.10 -uroot -p -d --databases db1 db2 >xxx.sql
11.3 导出单个数据库数据 mysqldump -h192.168.1.10 -uroot -p -t db1 >xxx.sql
11.4 导出多个数据库数据 mysqldump -h192.168.1.10 -uroot -p -t --databases db1 db2 >xxx.sql
11.5 导出单个数据库数据以及结构 mysqldump -h192.168.1.10 -uroot -p db1 >xxx.sql
11.6 导出多个数据库数据以及结构 mysqldump -h192.168.1.10 -uroot -p --databases db1 db2 >xxx.sql
11.7 导出全部数据库结构（不锁表） mysqldump --skip-lock-tables -h192.168.1.10 -uroot -p123 -d --all-databases >xxx.sql
11.8 导出全部数据库数据（不锁表） mysqldump --skip-lock-tables -h192.168.1.10 -uroot -p123 -t --all-databases >xxx.sql
11.9 导出某个数据库的一个数据表（不锁表） mysqldump --skip-lock-tables -h192.168.1.10 -uroot -p123 -t --dataases a --tables user >xxx.sql
12 json 数据查询
查询json数组里面对象的id等于142的记录
select * from log2 where JSON_CONTAINS(data,JSON_OBJECT('id', "142"))
13. mysql V5.7 创建用户并授权
grant ALL privileges ON *.* to 'test'@'192.168.50.131' IDENTIFIED BY '123456';
等同于：CREATE USER 'test'@'192.168.50.131';
ALTER USER 'test'@'192.168.50.131' IDENTIFIED BY '123456';
GRANT ALL privileges ON *.* to 'test'@'192.168.50.131';
14 取消授权
取消create 权限：
revoke create on *.* from 'test@192.168.50.131';
flush privileges;
参照：https://www.cnblogs.com/min225016/p/16531666.html
https://www.cnblogs.com/elijah-li/p/16286788.html
15 获取所有表的数据条数
select
concat( 'select "', TABLE_NAME, '", count(*) from ', TABLE_SCHEMA, '.', TABLE_NAME, ' union all')
from
information_schema.TABLES ;
