【MySQL】常用拼接语句

pakolagij 发布于2019-08-01 10:38 / 1968人阅读

摘要：前言在中函数用于将多个字符串连接成一个字符串，利用此函数我们可以将原来一步无法得到的拼接出来，在工作中也许会方便很多，下面主要介绍下几个常用的场景。注适用于版本低版本可能稍许不同。

前言：在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"
) ;

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

云数据库MySQL mysql常用sql语句常用sql语句常用sql查询语句常用sql语句大全

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/48581.html

MySQL常用操作命令

摘要：终端操作常用命令。函数函数大全及用法示例从上的测试，我们可以看出方法在进行加减法操作时可以有效的计算日期。示例对月份进行分组，并将分组月份的获取到，如果用这个方法最好不过了。 MySQL 终端操作常用命令。 1、连接数据库 mysql -uroot -p 2、展示mysql的数据库 mysql> show databases; 创建数据库： mysql> create database...

tuniutech 2019-07-31 17:17 评论0 收藏0
Gradle实战：执行sql操作hive数据库

摘要：以下分为各部分来讲述我们的实现方案脚本配置文件代码使用方法数据产出。中的配置数据再在中新建一个名为的配置文件，用于存放连接数据库所需的相关参数。查看原文：http://blog.csdn.net/u0108184... Gradle实战系列文章：《Gradle基本知识点与常用配置》《Gradle实战：Android多渠道打包方案汇总》《Gradle实战：不同编译类型的包同设备共存...

calx 2019-06-25 10:45 评论0 收藏0
Gradle实战：执行sql操作hive数据库

摘要：以下分为各部分来讲述我们的实现方案脚本配置文件代码使用方法数据产出。中的配置数据再在中新建一个名为的配置文件，用于存放连接数据库所需的相关参数。查看原文：http://blog.csdn.net/u0108184... Gradle实战系列文章：《Gradle基本知识点与常用配置》《Gradle实战：Android多渠道打包方案汇总》《Gradle实战：不同编译类型的包同设备共存...

苏丹 2019-07-01 15:38 评论0 收藏0
Mysql必知必会（5）：计算字段的使用

摘要：计算字段是在检索过程中建立的虚拟字段，他们并不实际存在于数据库中。计算字段和程序处理之间关系数据库这种临时处理数据，格式化输出的结果，都可以使用编程语言来实现。之间多个值使用逗号分割。过滤字段上面使用来组合字段。本文主要介绍Mysql中计算字段的使用方法我博客文章地址：http://weiya.me/item/60.html 简介在创建数据库表的时候，往往为了数据库的各种性能，我...

高璐 2019-06-27 15:25 评论0 收藏0
Mysql必知必会（5）：计算字段的使用

摘要：计算字段是在检索过程中建立的虚拟字段，他们并不实际存在于数据库中。计算字段和程序处理之间关系数据库这种临时处理数据，格式化输出的结果，都可以使用编程语言来实现。之间多个值使用逗号分割。过滤字段上面使用来组合字段。本文主要介绍Mysql中计算字段的使用方法我博客文章地址：http://weiya.me/item/60.html 简介在创建数据库表的时候，往往为了数据库的各种性能，我...

JayChen 2019-07-31 16:43 评论0 收藏0