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 -- 查看系统变量及其值;
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
索引相关
总结一下不走索引的情况：
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()等函数进行转换，
将类型严格的一方(表中这个字段存储的数据类型较少)转换为类型松散的一方的类型，这样也能避免精度丢失。比如，可以将数值型向字符串类型转，反过来则会有问题。
网友评论