【MySQL】常用拼接语句

栏目: 数据库 · 发布时间: 5年前

内容简介:前言:在MySQL中 CONCAT ()函数用于将多个字符串连接成一个字符串,利用此函数我们可以将原来一步无法得到的sql拼接出来,在工作中也许会方便很多,下面主要介绍下几个常用的场景。欢迎大家收藏,其他同学有没有类似实用的语句呢 也可以分享出来哦!

前言:在 MySQL 中 CONCAT ()函数用于将多个字符串连接成一个字符串,利用此函数我们可以将原来一步无法得到的 sql 拼接出来,在工作中也许会方便很多,下面主要介绍下几个常用的场景。

注: 适用于5.7版本 低版本可能稍许不同。

1.拼接查询所有用户

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

2.拼接DROP table

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

3.拼接kill连接

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

4.拼接创建数据库语句

SELECT
    CONCAT(
        'create database ',
        '`',
    SCHEMA_NAME,
    '`',
    ' DEFAULT CHARACTER SET ',
    DEFAULT_CHARACTER_SET_NAME,
        ';'
    ) AS CreateDatabaseQuery
FROM
    information_schema.SCHEMATA
WHERE
    SCHEMA_NAME NOT IN (
        'information_schema',
        'performance_schema',
        'mysql',
        'sys'
    );

5.拼接创建用户的语句

SELECT
    CONCAT(
        'create user \'',
    user,
    '\'@\'',
    Host,
    '\''
    ' IDENTIFIED BY PASSWORD \'',
    authentication_string,
        '\';'
    ) AS CreateUserQuery
FROM
    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.sql
echo "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 datafree
FROM information_schema.tables t
WHERE t.TABLE_SCHEMA = 'test' order by DATA_LENGTH desc;

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

#查找某一个库无主键表
SELECT
table_schema,
table_name
FROM
    information_schema.TABLES
WHERE
    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_name
FROM
    information_schema. TABLES t1
LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMA
AND t1.table_name = t2.TABLE_NAME
AND t2.CONSTRAINT_NAME IN ('PRIMARY')
WHERE
    t2.table_name IS NULL
AND t1.TABLE_SCHEMA NOT IN (
    'information_schema',
    'performance_schema',
    'mysql',
    'sys'
) ;

欢迎大家收藏,其他同学有没有类似实用的语句呢 也可以分享出来哦!


以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

最优化导论

最优化导论

Edwin K. P. Chong、Stanislaw H. Zak / 孙志强、白圣建、郑永斌、刘伟 / 电子工业出版社 / 2015-10 / 89.00

本书是一本关于最优化技术的入门教材,全书共分为四部分。第一部分是预备知识。第二部分主要介绍无约束的优化问题,并介绍线性方程的求解方法、神经网络方法和全局搜索方法。第三部分介绍线性优化问题,包括线性优化问题的模型、单纯形法、对偶理论以及一些非单纯形法,简单介绍了整数线性优化问题。第四部分介绍有约束非线性优化问题,包括纯等式约束下和不等式约束下的优化问题的最优性条件、凸优化问题、有约束非线性优化问题的......一起来看看 《最优化导论》 这本书的介绍吧!

JS 压缩/解压工具
JS 压缩/解压工具

在线压缩/解压 JS 代码

URL 编码/解码
URL 编码/解码

URL 编码/解码

RGB CMYK 转换工具
RGB CMYK 转换工具

RGB CMYK 互转工具