MySQL常用拼接语句有哪些

这篇文章给大家分享的是有关MySQL常用拼接语句有哪些的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。

1.拼接查询所有用户

SELECT DISTINCT    CONCAT(        'User: \'',        USER,        '\'@\'',        HOST,        '\';'    ) AS QUERYFROM    mysql.USER;# 当拼接字符串中出现'时 需使用\转义符

2.拼接DROP table

SELECT    CONCAT(        'DROP table ',        TABLE_NAME,        ';'    )FROM    information_schema. TABLESWHERE    TABLE_SCHEMA = 'test';

3.拼接kill连接

SELECT    concat('KILL ', id, ';')FROM    information_schema. PROCESSLISTWHERE    STATE LIKE 'Creating sort index';

4.拼接创建数据库语句

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

5.拼接创建用户的语句

SELECT    CONCAT(        'create user \'',    user,    '\'@\'',    Host,    '\''    ' IDENTIFIED BY PASSWORD \'',    authentication_string,        '\';'    ) AS CreateUserQueryFROM    mysql.`user`WHERE    `User` NOT IN (        'root',        'mysql.session',        'mysql.sys'    );#有密码字符串哦 在其他实例执行 可直接创建出与本实例相同密码的用户

6.导出权限脚本 这个shell脚本也用到了拼接

#!/bin/bash  #Function export user privileges  pwd=yourpass  expgrants()  {    mysql -B -u'root' -p${pwd} -N $@ -e "SELECT CONCAT(  'SHOW GRANTS FOR ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \  mysql -u'root' -p${pwd} $@ | \  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/-- \1 /;/--/{x;p;x;}'  }  expgrants > /tmp/grants.sqlecho "flush privileges;" >> /tmp/grants.sql

7.查找表碎片

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 = 'test' order by DATA_LENGTH desc;

8.查找无主键表 这个没用到拼接 也分享出来吧

#查找某一个库无主键表SELECTtable_schema,table_nameFROM    information_schema.TABLESWHERE    table_schema = 'test'AND TABLE_NAME NOT IN (    SELECT        table_name    FROM        information_schema.table_constraints t    JOIN information_schema.key_column_usage k USING (        constraint_name,        table_schema,        table_name    )    WHERE        t.constraint_type = 'PRIMARY KEY'    AND t.table_schema = 'test');#查找除系统库外 无主键表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') ;

感谢各位的阅读！关于"MySQL常用拼接语句有哪些"这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！