MySQL常用拼接语句有哪些

发布时间：2022-01-12 00:18:59 所属栏目：MySql教程来源：互联网

导读：这篇文章给大家分享的是有关MySQL常用拼接语句有哪些的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。 1.拼接查询所有用户 SELECT DISTINCT CONCAT( User: , USER, @, HOST, ; ) AS QUERY FROM mysql.USER; # 当拼接字符串

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

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源码安置	MySQL自增列主从不一致
数据库设计的主要过程	mysql company