MySql笔记——常用命令篇

mysql 命令

desc/describe table_name;  -- 查看表字段信息
show full columns from table_name;-- 查看表字段信息（详细）
show create table table_name;--  查看建表语句
EXPLAIN(extended) yoursql -- 查看sql执行
SELECT now() from DUAL -- 获取数据库当前时间
select column_name from table_name forceIndex(index_name) where ... -- 查询时强制走索引
-- 解析json字段(mysql 5.7以上才支持)
SELECT JSON_UNQUOTE(json_extract(column_name ,'$.json中你想提取的key名')) FROM table_name;
SELECT column_name ->>'$.json中你想提取的key名' from table_name;
show processlist -- 显示哪些线程正在运行
kill 进程id -- 杀死进程
SHOW VARIABLES -- 查看系统变量及其值;

-- 查看表字段是否存在索引
SELECT * FROM information_schema.statistics 
  WHERE table_schema = [DATABASE NAME] 
    AND table_name = [TABLE NAME] AND column_name = [COLUMN NAME]

DDL 语句

-- 修改表名注释
alter table tablename comment '表名注释';
-- 添加索引
ALTER TABLE table_name ADD INDEX idx_column_name  (column_name );
create index idx_column_name  on table_name (column_name );
-- 在某字段后添加字段
ALTER TABLE table_name ADD column_name varchar(255) DEFAULT NULL COMMENT '字段注释' after 某字段;
-- 删除索引
drop index index_name on table_name ;
alter table table_name drop index index_name ;

分组统计进阶

elt + interval 实现分组统计

SELECT
    elt (
        -- 划分区间（0<=x<60, 60<=x<71,71<=x<81,81<=x<90,90+）
    interval ( 90, 0, 60, 71, 81, 90),
    "0-59","60-70","71-80","81-90","90+" 
    ) as level；

case when实现分组统计

case when conditon then result1 (else result2) end;
case column when conditon then result1 (else result2) end;

mysql 实现oracle rownum功能

SELECT @rownum := @rownum + 1 AS rownum, tb.*
  FROM (SELECT @rownum := 0) r, table_name tb;

mysql查询指定时间段内的每一天的日期

select a.Date 
from (
    -- 当天的日期--1000天前的日期
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where a.Date between '2018-12-01' and '2018-12-31' 
order by a.Date;

mysql查询指定时间段内的每一天的日期

select *from (
-- 距离当期月1-25个月的月份列表
SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL xc MONTH), '%Y-%m') as mm
FROM ( 
        -- 1-25的列表
        SELECT @xi:=@xi+1 as xc from 
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc1, 
        (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc2,  
        (SELECT @xi:=0) xc0 
) tp_month 
) tb_month where tb_month.mm between '2018-10' and '2019-04'
order by tb_month.mm desc;

MySQL中show语法

    1. show tables或show tables from database_name; -- 显示当前数据库中所有表的名称。 
    2. show databases; -- 显示mysql中所有数据库的名称。 
    3. show columns from table_name from database_name; 或show columns from database_name.table_name; -- 显示表中列名称。 
    4. show grants for user_name; -- 显示一个用户的权限，显示结果类似于grant 命令。 
    5. show index from table_name; -- 显示表的索引。 
    6. show status; -- 显示一些系统特定资源的信息，例如，正在运行的线程数量。 
    7. show variables; -- 显示系统变量的名称和值。 
    8. show processlist; -- 显示系统中正在运行的所有进程，也就是当前正在执行的查询。大多数用户可以查看他们自己的进程，但是如果他们拥有process权限，就可以查看所有人的进程，包括密码。 
    9. show table status; -- 显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间。 
    10. show privileges; -- 显示服务器所支持的不同权限。 
    11. show create database database_name; -- 显示create database 语句是否能够创建指定的数据库。 
    12. show create table table_name; -- 显示create database 语句是否能够创建指定的数据库。 
    13. show engines; -- 显示安装以后可用的存储引擎和默认引擎。 
    14. show innodb status; -- 显示innoDB存储引擎的状态。 
    15. show logs; -- 显示BDB存储引擎的日志。 
    16. show warnings; -- 显示最后一个执行的语句所产生的错误、警告和通知。 
    17. show errors; -- 只显示最后一个执行语句所产生的错误。 
    18. show [storage] engines; --显示安装后的可用存储引擎和默认引擎。

-- 查看所有表的注释
SELECT
table_name 表名,
table_comment 表说明
FROM
information_schema.TABLES
WHERE
table_schema = '数据库名'
ORDER BY
table_name

-- 查询表的所有字段的注释
select 
table_name 表名,
COLUMN_NAME 字段名,
column_comment 字段说明,
column_type 字段类型,
column_key 约束 from information_schema.columns 
where table_schema = '数据库名'
-- 或者使用
show full columns from table_name;

-- -- 查询数据库中所有表注释为空的表
select TABLE_NAME, CREATE_TIME, TABLE_COMMENT
from information_schema.TABLES
where information_schema.TABLES.table_schema = schema_name
  and (TABLE_COMMENT is null or TABLE_COMMENT = '')
order by CREATE_TIME desc

-- 查询数据库中未添加索引的大表
SELECT t.TABLE_NAME,t.TABLE_ROWS,t.TABLE_COMMENT
FROM information_schema.`TABLES` t
         LEFT JOIN information_schema.STATISTICS s ON t.TABLE_SCHEMA = s.TABLE_SCHEMA
    AND t.table_name = s.TABLE_NAME
    AND s.INDEX_NAME <> 'PRIMARY'
WHERE t.TABLE_SCHEMA = '{你的数据库库名}'
  AND t.TABLE_NAME LIKE 'ads_%'
  AND TABLE_TYPE = 'BASE TABLE'
  AND TABLE_ROWS > 500000
  AND s.INDEX_NAME IS NULL;

索引相关

总结一下不走索引的情况：
1、条件字段选择性弱，查出的结果集较大(>30%)，不走索引；
2、字段类型不一致，不走索引；
（这时候需要转换字段类型才能走到索引，通过char(column_name)、concat(column_name)、CONVERT()、CAST()）
3、优化器分析的统计信息陈旧也可能导致不走索引；
4、对于count(*)当索引字段有not null约束时走索引，否则不走索引；
5、like 后面的字符当首位为通配符时不走索引；
6、使用不等于操作符如：<>、!= 等不走索引；
7、索引字段前加了函数或参加了运算不走索引；

查询优化

mysql in和exists 效率:如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：
mysql not in和not exists 效率: 无论那个表大，用not exists都比not in要快。
IN子查询包含超大数据量值，单表索引in 查询扫描超过30%就不会走索引了。

mysql误区

distinct()中使用多个字段,只对某一字段去重的方法
场景：查出用户表中用户ID, 用户名并对用户名去重

select distinct user_name, user_id from user -- （实际结果是对user_id, user_name一起去重，而不是单个字段去重）
而 select user_id, distinct user_name from user -- 会报错，执行不通过
-- 正确用法是：
select user_id, group_concat(distinct user_name) from user group by user_name;

在mysql中，查询某字段为空时，切记不可用 = null，而是 is null，不为空则是 is not null

select * from table where column is null;
select * from table where column is not null;

SUBSTRING()函数
substring（str, pos）说明：substring（被截取字段，从第几位开始截取）
substring（str, pos, length）substring（被截取字段，从第几位开始截取，截取长度）

字段类型转换函数
MySQL 的CAST()和CONVERT()函数可用来获取一个类型的值，并产生另一个类型的值。两者具体的语法如下：
1、CAST(value as type) 就是CAST(xxx AS 类型)
2、CONVERT(value, type) 就是CONVERT(xxx,类型)
但是要特别注意,可以转换的数据类型是有限制的。这个类型可以是以下值其中的一个：
1）二进制，同带binary前缀的效果 : BINARY
2）字符型，可带参数 : CHAR()
3）日期 : DATE
4）时间: TIME
5）日期时间型 : DATETIME
6）浮点数 : DECIMAL
7）整数 : SIGNED
8）无符号整数 : UNSIGNED

FROM_UNIXTIME()函数 FROM_UNIXTIME(unix_timestamp,format)
UNIX_TIMESTAMP()函数即将日期类型的转换为时间戳显示

group by 和distinct区别
group by/ order by同时使用的顺序
inner(left/right/逗号/out) join 区别
DATE_FORMAT('时间字段','%Y-%m-%d'<日期格式>) -- 日期格式化
-- 按条件计和
错误使用 case when condition then sum(***) else 0
正确使用 ifnull(sum(case when condition then *** else ***),0)

其他

解决查询大数据量内存溢出的问题
方案：修改连接名为 url = "jdbc:mysql://localhost:3306/pk_name?useCursorFetch=true&defaultFetchSize=100";
连接查询时不走索引
使用explain分析查询语句注意索引字段类型是否一致！

string转date函数STR_TO_DATE(str, format)最少为年-月-日的格式
下面的sql格式转换会不成功。。

select STR_TO_DATE("2019-01-01", '%Y-%m')

结果是

我们可以通过拼接的方式补齐位数来实现

select DATE_FORMAT(date_sub(concat("2019-01","-01"), interval 1 month),'%Y-%m')

解决字段类型不一致导致索引无效
通过char(column_name)、concat(column_name)、CONVERT()、CAST()等函数进行转换，
将类型严格的一方(表中这个字段存储的数据类型较少)转换为类型松散的一方的类型，这样也能避免精度丢失。比如，可以将数值型向字符串类型转，反过来则会有问题。

MySql笔记——常用命令篇