mysql拼接两张表_巧用SQL拼接语句 - Go语言中文社区

mysql拼接两张表_巧用SQL拼接语句

前言： 

在日常数据库运维过程中，可能经常会用到各种拼接语句，巧用拼接SQL可以让我们的工作方便很多，达到事半功倍的效果。本篇文章将会分享几个日常会用到的SQL拼接案例，类似的SQL还可以举一反三，探索出更多的可能性哦。

注意：适用于5.7版本，其他版本可能稍许不同。

1.CONCAT函数介绍

授人以鱼不如授人以渔，拼接SQL主要用到的是CONCAT函数，我们先来介绍下该函数的用法。

CONCAT(s1,s2...,sn) 是合并字符串函数，返回结果为连接参数产生的字符串，参数可以是一个或多个，若有任何一个参数为 NULL，则返回值为 NULL。当拼接字符串中有 ' 时，要用 转义，貌似用两个单引号也行，不过还是推荐用 转义，下面用几个示例来了解下CONCAT函数的用法。

mysql> SELECT CONCAT('MySQL','5.7'),CONCAT('MySQL',NULL),CONCAT(''MySQL'');+-----------------------+----------------------+---------------------+| CONCAT('MySQL','5.7') | CONCAT('MySQL',NULL) | CONCAT(''MySQL'') |+-----------------------+----------------------+---------------------+| MySQL5.7              | NULL                 | 'MySQL'             |+-----------------------+----------------------+---------------------

简单介绍完CONCAT函数的使用方法后，下面分享几个用到SQL拼接的场景，也许在你工作中会用到哦。

2.拼接查询所有用户

SELECT DISTINCT  CONCAT(    'User: '',    USER,    ''@'',    HOST,    '';'  ) AS QUERYFROM  mysql.USER;

3.拼接创建用户的语句

# 有密码字符串 在其他实例执行 可直接创建出与本实例相同密码的用户SELECT  CONCAT(    'create user '',    user,    ''@'',    Host,    '' IDENTIFIED BY PASSWORD '',    authentication_string,    '';'  ) AS CreateUserQueryFROM  mysql.`user`WHERE  `User` NOT IN (    'root',    'mysql.session',    'mysql.sys'  );# 这样拼接也可以 带有密码认证插件SELECT  CONCAT(    'create user '',    user,    ''@'',    Host,    '' IDENTIFIED WITH '',    plugin,    '' AS '',    authentication_string,    '';'  ) AS CreateUserQueryFROM  mysql.`user`WHERE  `User` NOT IN (    'root',    'mysql.session',    'mysql.sys'  );

4.拼接show grants语句查询用户权限

SELECT  CONCAT(    'show grants for '',    user,    ''@'',    Host,    '';'  ) AS ShowGrantsFROM  mysql.`user`WHERE  `User` NOT IN (    'root',    'mysql.session',    'mysql.sys'  );

5.拼接创建数据库语句

SELECT  CONCAT(    'create database if not exists ',    '`',    SCHEMA_NAME,    '`',    ' DEFAULT CHARACTER SET ',    DEFAULT_CHARACTER_SET_NAME,    ';'  ) AS CreateDatabaseQueryFROM  information_schema.SCHEMATAWHERE  SCHEMA_NAME NOT IN (    'information_schema',    'performance_schema',    'mysql',    'sys'  );

6.拼接DROP table

SELECT  CONCAT(    'DROP table ',    TABLE_NAME,    ';'  )FROM  information_schema.TABLESWHERE  TABLE_SCHEMA = 'testdb' and TABLE_TYPE = 'BASE TABLE';

7.拼接kill连接

# 可以组合改变条件SELECT    concat( 'KILL ', id, ';' ) FROM    information_schema.PROCESSLIST WHERE    STATE LIKE 'Sending data';  SELECT    concat( 'KILL ', id, ';' ) FROM    information_schema.`PROCESSLIST`WHERE    Command = 'Sleep'     AND TIME > 2000;

8.查看数据库大小

# 查看整个实例大小SELECT    concat( round( sum( data_length / 1024 / 1024 ), 2 ), 'MB' ) AS data_length_MB,    concat( round( sum( index_length / 1024 / 1024 ), 2 ), 'MB' ) AS index_length_MBFROM    information_schema.TABLES;# 查看各个库大小SELECT    TABLE_SCHEMA,    concat( TRUNCATE ( sum( data_length )/ 1024 / 1024, 2 ), ' MB' ) AS data_size,    concat( TRUNCATE ( sum( index_length )/ 1024 / 1024, 2 ), 'MB' ) AS index_sizeFROM    information_schema.TABLES GROUP BY    TABLE_SCHEMAORDER BY    data_length DESC;

9.查找表碎片

SELECT t.TABLE_SCHEMA,       t.TABLE_NAME,       t.TABLE_ROWS,     concat(round(t.DATA_LENGTH / 1024 / 1024, 2), 'M') AS size,       t.INDEX_LENGTH,       concat(round(t.DATA_FREE / 1024 / 1024, 2), 'M') AS datafreeFROM information_schema.tables tWHERE t.TABLE_SCHEMA = 'testdb' order by DATA_LENGTH desc;

10.查找无主键表及增加自增ID作为主键

# 查找出无主键的表SELECT    t1.table_schema,    t1.table_nameFROM    information_schema.TABLES t1LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMAAND t1.table_name = t2.TABLE_NAMEAND t2.CONSTRAINT_NAME IN ('PRIMARY')WHERE    t2.table_name IS NULLAND t1.TABLE_SCHEMA NOT IN (    'information_schema',    'performance_schema',    'mysql',    'sys') ;# 拼接出增加自增ID作为主键的SQLSELECTCONCAT('ALTER TABLE ',t1.table_schema,'.',t1.table_name,' ADD COLUMN increment_id INT UNSIGNED NOT NULL auto_increment COMMENT '自增主键' PRIMARY KEY FIRST;')FROM    information_schema.TABLES t1LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMAAND t1.table_name = t2.TABLE_NAMEAND t2.CONSTRAINT_NAME IN ('PRIMARY')WHERE    t2.table_name IS NULLAND t1.table_type = 'BASE TABLE'AND t1.TABLE_SCHEMA NOT IN (    'information_schema',    'performance_schema',    'mysql',    'sys') ;

11.查找大写表及转为小写表

# 若lower_case_table_names=0可能导致表名既有大写又有小写，# 想将lower_case_table_names设为1的话 需要先将大写的表和视图名称改为小写的。# 查找出名称为大写的表和视图SELECT    TABLE_SCHEMA,    TABLE_NAME,    TABLE_TYPEFROM    information_schema.`TABLES`WHERE    TABLE_SCHEMA NOT IN ( 'information_schema', 'sys', 'mysql', 'performance_schema' )     AND TABLE_NAME REGEXP BINARY '[A-Z]';# 拼接出大写表名改为小写的SQLSELECT    CONCAT( 'rename table ', TABLE_SCHEMA, '.', TABLE_NAME, ' to ', TABLE_SCHEMA, '.', LOWER( TABLE_NAME ), ';' ) FROM    information_schema.`TABLES`WHERE    TABLE_SCHEMA NOT IN ( 'information_schema', 'sys', 'mysql', 'performance_schema' )     AND TABLE_TYPE = 'BASE TABLE'     AND TABLE_NAME REGEXP BINARY '[A-Z]';

PS：MySQL技术交流群创建啦！有意向的小伙伴可以扫一扫下方二维码加我微信，后续统一邀请大家入群。加微信请备注“加群”，非诚勿扰，谢谢配合。

0aec91a1e97c8ec9430baa96c1c662f5.png

推荐阅读

(点击标题可跳转阅读)

Truncate用法详解

utf8字符集下的比较规则

关于Aborted connection告警日志的分析

— END —

如果你喜欢我的文章

请在文末右下角点一下在看❤

e2fff241bde7b1d16d5dd3f5ac689efc.png

版权声明：本文来源CSDN，感谢博主原创文章，遵循 CC 4.0 by-sa 版权协议，转载请附上原文出处链接和本声明。
原文链接：https://blog.csdn.net/weixin_33810040/article/details/113089152
站方申明：本站部分内容来自社区用户分享，若涉及侵权，请联系站方删除。
  • 发表于 2021-04-10 23:33:43
  • 阅读 ( 970 )
  • 分类：数据库

你可能感兴趣的文章

精选的优质文章

0 条评论

请先 登录 后评论

官方社群

GO教程

推荐文章

猜你喜欢

随便看看

来源51CTO

如果觉得我的文章对您有用，请随意打赏。你的支持将鼓励我继续创作！