江苏龙网

  1. 主页 > 生活百科 > >

巧用SQL拼接语句

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 DISTINCTCONCAT('User: '',USER,''@'',HOST,'';') AS QUERYFROMmysql.USER;3.拼接创建用户的语句# 有密码字符串 在其他实例执行 可直接创建出与本实例相同密码的用户SELECTCONCAT('create user '',user,''@'',Host,'' IDENTIFIED BY PASSword '',authentication_string,'';') AS CreateUserQueryFROMmysql.`user`WHERE`User` NOT IN ('root','mysql.session','mysql.sys');# 这样拼接也可以 带有密码认证插件SELECTCONCAT('create user '',user,''@'',Host,'' IDENTIFIED WITH '',plugin,'' AS '',authentication_string,'';') AS CreateUserQueryFROMmysql.`user`WHERE`User` NOT IN ('root','mysql.session','mysql.sys');4.拼接show grants语句查询用户权限SELECTCONCAT('show grants for '',user,''@'',Host,'';') AS ShowGrantsFROMmysql.`user`WHERE`User` NOT IN ('root','mysql.session','mysql.sys');5.拼接创建数据库语句SELECTCONCAT('create database if not exists ','`',SCHEMA_NAME,'`',' DEFAULT CHARACTER SET ',DEFAULT_CHARACTER_SET_NAME,';') AS CreateDatabaseQueryFROMinformation_schema.SCHEMATAWHERESCHEMA_NAME NOT IN ('information_schema','performance_schema','mysql','sys');6.拼接DROP tableSELECTCONCAT('DROP table ',TABLE_NAME,';')FROMinformation_schema.TABLESWHERETABLE_SCHEMA = 'testdb' and TABLE_TYPE = 'BASE TABLE';7.拼接kill连接【巧用SQL拼接语句】# 可以组合改变条件SELECTconcat( 'KILL ', id, ';' ) FROMinformation_schema.PROCESSLIST WHERESTATE LIKE 'Sending data';SELECTconcat( 'KILL ', id, ';' ) FROMinformation_schema.`PROCESSLIST` WHERECommand = 'Sleep'AND TIME > 2000;8.查看数据库大小# 查看整个实例大小SELECTconcat( round( sum( data_length / 1024 / 1024 ), 2 ), 'MB' ) AS data_length_MB,concat( round( sum( index_length / 1024 / 1024 ), 2 ), 'MB' ) AS index_length_MB FROMinformation_schema.TABLES;# 查看各个库大小SELECTTABLE_SCHEMA,concat( TRUNCATE ( sum( data_length )/ 1024 / 1024, 2 ), ' MB' ) AS data_size,concat( TRUNCATE ( sum( index_length )/ 1024 / 1024, 2 ), 'MB' ) AS index_size FROMinformation_schema.TABLES GROUP BYTABLE_SCHEMA ORDER BYdata_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作为主键# 查找出无主键的表SELECTt1.table_schema,t1.table_nameFROMinformation_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')WHEREt2.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;')FROMinformation_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')WHEREt2.table_name IS NULLAND t1.table_type = 'BASE TABLE'AND t1.TABLE_SCHEMA NOT IN ('information_schema','performance_schema','mysql','sys') ;


推荐阅读


上一篇：现实中的金山寺在哪里？

下一篇：《城南旧事》是哪位著名女作家的代表作？