欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

常用SQL语句分享

程序员文章站 2023-04-04 18:09:04
前言: 日常工作或学习过程中,我们可能会经常用到某些SQL,建议大家多多整理记录下这些常用的SQL,这样后续用到会方便很多。笔者在工作及学习过程中也整理了下个人常用的SQL,现在分享给你!可能有些SQL你还不常用,但还是希望对你有所帮助,说不定某日有需求就可以用到。 注:下文分享的SQL适用于MyS ......

前言: 

日常工作或学习过程中,我们可能会经常用到某些sql,建议大家多多整理记录下这些常用的sql,这样后续用到会方便很多。笔者在工作及学习过程中也整理了下个人常用的sql,现在分享给你!可能有些sql你还不常用,但还是希望对你有所帮助,说不定某日有需求就可以用到。

注:下文分享的sql适用于mysql 5.7 版本,低版本可能稍许不同。有些sql可能执行需要较高权限。

1.show相关语句

# 查看实例参数 例如:
show variables like '%innodb%';
show global variables like '%innodb%';

# 查看实例状态,例如:
show status like 'uptime%';
show global status like 'connection%';

# 查看数据库链接:
show processlist;
show full processlist;

# 查询某个表的结构:
show create table tb_name;

# 查询某个表的详细字段信息:
show full columns from tb_name;

# 查询某个表的全部索引信息:
show index from tb_name;

# 查询某个库以cd开头的表:
show tables like 'cd%';

# 查询某个库中的所有视图:
show table status where comment='view';

# 查询某个用户的权限:
show grants for 'test_user'@'%';

2.查看账户相关信息

# 这里先介绍下concat函数:在mysql中 concat()函数用于将多个字符串连接成一个字符串,
利用此函数我们可以将原来一步无法得到的sql拼接出来,后面部分语句有用到该函数。
# 当拼接字符串中出现''时 需使用\转义符

# 查看所有用户名:
select distinct
    concat(
        'user: \'',
        user,
        '\'@\'',
        host,
        '\';'
    ) as query
from
    mysql.user;

# 查看用户详细信息:
select user,
    host,
    authentication_string,
    password_expired,
    password_lifetime,
    password_last_changed,
    account_locked 
from
    mysql.user;

3.kill数据库链接

# 下面列举sql只是拼接出kill 链接的语句,若想执行 直接将结果复制执行即可。
# 杀掉空闲时间大于2000s的链接:
select
    concat( 'kill ', id, ';' ) 
from
    information_schema.`processlist` 
where
    command = 'sleep' 
    and time > 2000;
  
# 杀掉处于某状态的链接:
select
    concat( 'kill ', id, ';' ) 
from
    information_schema.`processlist` 
where
    state like 'creating sort index';
  
# 杀掉某个用户的链接:
select
    concat( 'kill ', id, ';' ) 
from
    information_schema.`processlist` 
where
    where user='root';

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'
    );
    
# 拼接创建用户语句(排除系统用户):
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'
    );
# 有密码字符串哦 在其他实例执行 可直接创建出与本实例相同密码的用户。

5.查看库或表大小

# 查看整个实例占用空间大小:
select
    concat( 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 
from
    information_schema.`tables`;
  
# 查看各个库占用大小:
select
    table_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 
from
    information_schema.`tables`
group by
    table_schema;
  
# 查看单个库占用空间大小:
select
    concat( 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 
from
    information_schema.`tables`
where
    table_schema = 'test_db';
  
# 查看单个表占用空间大小:
select
    concat( 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 
from
    information_schema.`tables`
where
    table_schema = 'test_db' 
    and table_name = 'tbname';

6.查看表碎片及收缩语句

# 查看某个库下所有表的碎片情况:
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_db' 
order by
    datafree desc;
  
# 收缩表,减少碎片:
alter table tb_name engine = innodb;
optimize table tb_name;

7.查找无主键表

# 查找某一个库无主键表:
select
table_schema,
table_name
from
    information_schema.`tables`
where
    table_schema = 'test_db'
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_db'
);

# 查找除系统库外 无主键表:
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'
) ;

总结: 

希望这些sql语句能对你有所帮助,可以收藏一下,说不定某次就用到了呢!原创不易,感谢大家支持。

常用SQL语句分享