mysql學習知識點大全概要正常設定及操作mysql查詢一張表所有的字段名稱日期查詢例子

概要

执行顺序

# 书写顺序
select distinct 
	< select_list>
from 
	<left_table> <join_type>
join <right_table> on <join_condition>
where 
	<where_condition>
group by 
	<group_by_list>
having 
	<having_condition>
order by  
	<order_by_condition>
limit <limit_number>

# 执行顺序
from <left_table>
on <join_condition>
<join_type> join <right_table>
where 
 <where_condition>
group by <group_by_list>
having <having_condition>
select 
distinct < select_list>
order by <order_by_condition>
limit <limit_number>
           

单表约束

# 主键约束：PRIMARY KEY          要求被装饰的字段：唯一和非空
# 唯一约束：UNIQUE               要求被装饰的字段：唯一，
#                             .联合唯一：在结尾：unique(字段1，字段2)
# 非空约束：NOT NULL             要求被装饰的字段：非空
# 外键约束：FOREIGN KEY          某主表的外键
# 自动增加：AUTO_INCREMENT       自动增加(需要和主键 PRIMARY KEY 同时用)
# 设置默认值: DEFAULT            为该属性设置默认值
# 在int、char中：zerofill        不足位数默认填充0
           

常用数据类型

数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 byte	(-128，127)	(0，255)	小整数值
SMALLINT	2 bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

日期和时间类型

类型	大小 ( bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

字符串类型

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	ä¸è¶ è¿‡ 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符

常规设置及操作

服务器及配置

服务器设置远程访问

Linux中数据库的基本操作命令

启动：service mysql start
停止：service mysql stop
重启：service mysql restart
清屏：clear,reset
           

备份数据库

恢复备份的数据库

# 首先在mysql里建好数据库名
# mysql -uroot -p密码 数据库名 < D:/备份文件名.sql
           

查询binlog日志是否开启

基本操作

查看数据列表

show databases;					# 查看所有数据库
show create table 表名;		# 查看表的创建细节
desc 表名; 					# 查看表结构
           

进入数据库

# use 数据名
use localdb
           

创建数据库

# CREATE DATABASE 数据库名
CREATE DATABASE localdb; 
CREATE DATABASE localdb charset utf8;

# 修改数据库编码
alter database localdb charset gbk;
           

删除数据库

# drop database 需要删除的数据库名
drop database localdb;
           

创建数据库并设置数据集

CREATE DATABASE xxxname CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';
           

导出sql脚本中，创建数据库并创建表

SET FOREIGN_KEY_CHECKS=0;

CREATE DATABASE spiderflow;
USE spiderflow;

DROP TABLE IF EXISTS `sp_flow`;
CREATE TABLE `sp_flow` (
  `id` varchar(32) NOT NULL,
  `name` varchar(64) DEFAULT NULL COMMENT '任务名字',
  `xml` longtext DEFAULT NULL COMMENT 'xml表达式',
  `cron` varchar(255) DEFAULT NULL COMMENT 'corn表达式',
  `enabled` char(1) DEFAULT '0' COMMENT '任务是否启动,默认未启动',
  `create_date` datetime DEFAULT CURRENT_TIMESTAMP  COMMENT '创建时间',
  `last_execute_time` datetime DEFAULT NULL  COMMENT '上一次执行时间',
  `next_execute_time` datetime DEFAULT NULL   COMMENT '下一次执行时间',
  `execute_count` int(8) DEFAULT NULL  COMMENT '定时执行的已执行次数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '任务表';
           

查看表

select database(); 	 	# 查看当前所在的库
show tables; 		 	# 查看数据库中所有表
desc 表名; 				# 查看表结构
show create table 表名; 	# 查看表的创建细节
           

创建表

# 创建新表
# create table 新建数据表名（
#      字段名 类型(长度) [约束(具体见1)],
#      字段名 类型(长度) [约束(具体见1)]
#  );
create table class(
   id INT AUTO_INCREMENT,
name varchar(32) UNIQUE,
age varchar(32) NOT NULL
);

# 根据已有的表创建新表
create table 新表 like 旧表  	# 使用旧表创建新表,只复制表结构和全部索引
create table 新表 as select 字段1 字段2... from definition only  # as用来创建相同表结构并复制源表数据

# 可以拷贝一个表中其中的一些字段:
CREATE TABLE newadmin AS
(
    SELECT username, password FROM admin
)

# 可以将新建的表的字段改名:
CREATE TABLE newadmin AS
(  
    SELECT id, username AS uname, password AS pass FROM admin
)

# 可以拷贝一部分数据
CREATE TABLE anewadmin AS
(
    SELECT id,`name` AS username FROM users WHERE id = 1
)

# 可以在创建表的同时定义表中的字段信息:
CREATE TABLE newadmin
(
    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY/id varchar(20)等等
)
AS
(
    SELECT * FROM admin
)  
           

删除表

# drop table 数据库表名
drop table Python
           

修改表

alter table 表名 add 字段名 类型（长度） [约束];	 # 添加列
alter table 表名 modify 字段名 类型（长度） [约束]; # 修改列的类型长度及约束
alter table 表名 change 旧字段名 新字段名 类型（长度） [约束]; # 修改列表名
alter table 表名 drop 字段名; # 删除列
alter table 表名 character set 字符集; # 修改表的字符集
rename table 表名 to 新表名; # 修改表名
           

增加数据

insert into 表(字段名1，字段名2..) values(值1，值2..); # 向表中插入某些列
insert into 表 values(值1，值2，值3..); 		# 向表中插入所有列

# ON DUPLICATE KEY UPDATE   不存在则插入、存在则更新
# 此为mysql特有语法。不是标准sql语法。
# mysql语法支持如果数据存在则更新，不存在则插入，首先判断数据存在还是不存在的那个字段要设置成unique索引。
INSERT INTO 表名(唯一索引列, 列2, 列3) VALUE(值1, 值2, 值3) ON DUPLICATE KEY UPDATE 列=值, 列=值

# REPLACE INTO 不存在则插入、存在则先删除后插入
REPLACE INTO 表名称(列1, 列2, 列3) VALUES(值1, 值2, 值3)

           

修改数据

update 表名 set 字段名=值,字段名=值...;  # 这个会修改所有的数据，把一列的值都变了
update 表名 set 字段名=值,字段名=值... where 条件; # 只改符合where条件的行
           

删除数据

# 1. delete可以删除行数据，也可以对整表数据删除，一般与where关联使用。delete删除内容，释放内容空间，但是表结构和表定义不删除。delete每次删除一行时，都会将该行的删除操作作为事务记录在日志中。以便进行回滚操作。
delete from 表名  # 删除表中所有记录
delete from 表名 where 条件 # 删除符合 where条件的数据

# 2. truncate只清除表中的内容，释放内容所占空间，但是表结构和表定义不删除。其次truncate不能删除行数据，比delete删除的彻底。
truncate table 表名; 

# 3. drop删除数据和定义，释放空间，它会将表的结构也一起删除掉。
drop table 表名

执行速度：drop > truncate > delete：因为delete每执行一次，都要在事务日志中记录一次。所以最慢
           

数据的简单查询

select * from 表名; # 查询所有列
select 字段名1,字段名2,字段名3.. from 表名; # 查询指定列
           

基本的sql语句

select * from 表名 where 范围                           # 选择查询
insert into 表名(field1,field2) values(value1,value2)  # 插入
delete from 表名 where 范围                             # 删除
update 表名 set field1=value1 where 范围                # 更新
select * from 表名 where field1 like ’%value1%’        # 查找
select * from 表名 order by field1,field2 [desc]       # 排序
select count as 需要统计总数的字段名 from 表名             # 总数
select sum(field1) as sumvalue from 表名               # 求和
select avg(field1) as avgvalue from 表名               # 平均
select max(field1) as maxvalue from 表名               # 最大
select min(field1) as minvalue from 表名               # 最小
           

存储引擎

# 查看所有的存储引擎
show engines;

# 查看不同存储引擎存储表结构文件特点
create table t1(id int)engine=innodb; # MySQL默认的存储引擎,支持事务,支持行锁，支持外键。有且只有一个主键，用来组织数据的依据
create table t2(id int)engine=myisam; # 不支持事务，不支持外键，支持全文索引，处理速度快。
create table t3(id int)engine=blackhole; # 黑洞，写入它的任何内容都会消失
create table t4(id int)engine=memory; # 将表中的数据存储在内存中。表结构以文件存储于磁盘。

insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);
           

设置严格模式

# 查询
show variables like '%mode%';
# 设置
set session # 设置当前窗口下有效
set global  # 全局有效，终身受用
set global sql_mode = "STRICT_TRANS_TABLES";
# 设置完成后需要退出客户端，重新登录客户端即可，不需要重启服务端

group by分组涉及到的模式：
设置sql_mode为only_full_group_by，意味着以后但凡分组，只能取到分组的依据，
不应该在去取组里面的单个元素的值，那样的话分组就没有意义了，因为不分组就是对单个元素信息的随意获取

set global sql_mode="strict_trans_tables,only_full_group_by";
# 重新链接客户端
           

like 的用法

A:% 包含零个或多个字符的任意字符串：
1、like'Mc%' 将搜索以字母 Mc 开头的所有字符串（如 McBadden）。
2、like'%inger' 将搜索以字母 inger 结尾的所有字符串（如 Ringer、Stringer）。
3、like'%en%' 将搜索在任何位置包含字母 en 的所有字符串（如 Bennet、Green、McBadden）。

B:_（下划线） 任何单个字符：
like'_heryl' 将搜索以字母 heryl 结尾的所有六个字母的名称（如 Cheryl、Sheryl）。

C：[ ] 指定范围 ([a-f]) 或集合 ([abcdef]) 中的任何单个字符： 
1，like'[CK]ars[eo]n' 将搜索下列字符串：Carsen、Karsen、Carson 和 Karson（如 Carson）。
2、like'[M-Z]inger' 将搜索以字符串 inger 结尾、以从 M 到 Z 的任何单个字母开头的所有名称（如 Ringer）。

D：[^] 不属于指定范围 ([a-f]) 或集合 ([abcdef]) 的任何单个字符：
like'M[^c]%' 将搜索以字母 M 开头，并且第二个字母不是 c 的所有名称（如MacFeather）。

E：* 它同于DOS命令中的通配符，代表多个字符：
c*c代表cc,cBc,cbc,cabdfec等多个字符。

F：？同于DOS命令中的？通配符，代表单个字符 :
b?b代表brb,bFb等

G：# 大致同上，不同的是代只能代表单个数字。k#k代表k1k,k8k,k0k 。

下面我们来举例说明一下：

例1，查询name字段中包含有“明”字的。
select * from table1 where name like '%明%'

例2，查询name字段中以“李”字开头。
select * from table1 where name like '李%'

例3，查询name字段中含有数字的。
select * from table1 where name like '%[0-9]%'

例4，查询name字段中含有小写字母的。
select * from table1 where name like '%[a-z]%'

例5，查询name字段中不含有数字的。
select * from table1 where name like '%[!0-9]%'
           

查询操作

外键表创建

一对多（Foreign Key）
# foreign key(需要关联的本字段) references 需要关联对表的表(需要关联对表的字段)
例如：
创建dep
foreign key(dep_id) references dep(id)
# 同步更新，同步删除
on update cascade # 同步更新
on delete cascade # 同步删除
           

复制表

# 复制表
create table t1 select * from test;
# 只复制表结构
create table t1 select * from test where 1=2;
           

单表查询

where 查询

# between 在...之间
select * from emp where id between 3 and 6;  
相当于：
select id,name from emp where id >= 3 and id <= 6;

# or 或者
select *  from emp where id >= 3 or id <= 6;

# in，后面可以跟多个值
select * from 表名 where 字段名 in (条件1,条件2,条件3); 

# char——length()  取字符长度
select * from 表名 where char_length(需要获取长度的字段名) = 4;

not 配合使用
注意：判断空不能用 = ，只能用 is 
# null 表示什么也不是， 不能=、>、< … 所有的判断，结果都是false，所有只能用 is null进行判断。
# 默认情况下，推荐使用 IS NOT NULL去判断，因为SQL默认情况下对！= Null的判断会永远返回0行，但没有语法错误.如果你一定想要使用！= Null来判断，需要加上这个语句：
set ANSI_NULLS off # 这时你会发现IS NOT NULL 和 != null 是等效的
           

group by 分组

select 查询字段1,查询字段2,... from 表名
   where 过滤条件
   group by分组依据  # 分组后取出的是每个组的第一条数据
           

聚合查询 ：以组为单位统计组内数据>>>聚合查询(聚集到一起合成为一个结果)

# max 最大值
# 每个部门的最高工资
select post,max(salary) from emp group by post;

# min 最小值
# 每个部门的最低工资
select post,min(salary) from emp group by post;

# avg 平均值
# 每个部门的平均工资
select post,avg(salary) from emp group by post;
# 每个部门的工资总和

# sum 求和
select post,sum(salary) from emp group by post;

# 求班级男女人数统计
select sum(case when sex ='男' then 1 else 0 end) 男,sum(case when sex ='女' then 1 else 0 end) 女,count(*) 总数 from student

# count（需要计数字段） 计数
# 每个部门的人数
select post,count(id) from emp group by post;

# concat() 函数用于将多个字符串连接成一个字符串
CONCAT(str1,str2,…)  # 返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。可以有一个或多个参数
举例：
   select concat("NAME: ",name) as 姓名 from emp;
   
# concat_ws: 指定拼接参数之间的分隔符
CONCAT_WS(separator,str1,str2,…) # CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。但是CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。
举例：
   select concat_ws(':',name,age,sex) as info from emp;
   
# group_concat函数返回一个字符串结果，该结果由分组中的值连接组合而成。
举例:
select post,group_concat(name) from emp group by post;  # post：分组字段，name 需要分组后显示的字段

# group_concat完整的语法如下:
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

# group_concat 的理解
select * from aa; 

+------+------+
| id| name |
+------+------+
|1 | 10|
|1 | 20|
|1 | 20|
|2 | 20|
|3 | 200 |
|3 | 500 |
+------+------+
6 rows in set (0.00 sec)

# 以id分组，把name字段的值打印在一行，逗号分隔(默认)
select id,group_concat(name) from aa group by id;  
+------+--------------------+
| id| group_concat(name) |
+------+--------------------+
|1 | 10,20,20|
|2 | 20 |
|3 | 200,500|
+------+--------------------+
3 rows in set (0.00 sec)

# 以id分组，把name字段的值打印在一行，分号分隔
select id,group_concat(name separator ';') from aa group by id;  
+------+----------------------------------+
| id| group_concat(name separator ';') |
+------+----------------------------------+
|1 | 10;20;20 |
|2 | 20|
|3 | 200;500 |
+------+----------------------------------+
3 rows in set (0.00 sec)

# 以id分组，把name字段的值打印在一行，逗号分隔，以name排倒序
select id,group_concat(name order by name desc) from aa group by id; 
+------+---------------------------------------+
| id| group_concat(name order by name desc) |
+------+---------------------------------------+
|1 | 20,20,10 |
|2 | 20|
|3 | 500,200|
+------+---------------------------------------+
3 rows in set (0.00 sec)
           

函数

substring_index
# substring_index("待截取有用部分的字符串"，"截取数据依据的字符"，截取字符的位置N)
#  N为正数，从左往右数，第N个分隔符的左边（前面）的全部内容
SELECT SUBSTRING_INDEX('www.csdn.net','.',1)
结果为：www

#   N为负数，从右往左数，第N个分隔符的右边（后面）的全部内容
SELECT SUBSTRING_INDEX('www.csdn.net','.',-1)
结果为：net

#   拓展：需要获取csdn字符串
#先从左往右取两个，再从右往左取一个
   SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('www.csdn.net','.',2),'.',-1)
   
使用案例见 --> 查询例子
           

having 过滤查询

# having的语法格式与where一致，只不过having是在分组之后进行的过滤，即where虽然不能用聚合函数，但是having可以！

# 用法
select 查询字段1,查询字段2,... from 表名
   where 过滤条件1
   group by分组依据
having avg(过滤条件2) > 10000;
           

distinct 去重

# 对有重复的展示数据进行去重操作
select distinct 需取重字段 from 表名;
           

order by 排序

select * from emp order by salary asc; # 默认升序
select * from emp order by salary desc; # 降序

# 多条件排序
# 先按照age降序，在年龄相同的情况下再按照薪资升序
select * from emp order by age desc,salary asc; 

# 按指定内容排序
SELECT xxi.CALLPER, xxi.QXMC, COUNT(1) AS CS, exi.EVENT_TYPE
FROM XFR_XFJ_INFO xxi
	LEFT JOIN EVENT_XFJ_INFO exi ON xxi.EVENT_ID = exi.EVENT_ID
WHERE exi.EVENT_CLASS = '其他'
GROUP BY xxi.CALLPER
ORDER BY CASE 
	WHEN exi.EVENT_TYPE LIKE '%越级%' THEN 3
	WHEN exi.EVENT_TYPE LIKE '%重复%' THEN 2
	WHEN exi.EVENT_TYPE LIKE '%群体%' THEN 1
	WHEN exi.EVENT_TYPE LIKE '%敏感事件%' THEN 0
END DESC
LIMIT 5
           

limit 限制展示条数

# 限制展示条数
select * from emp limit 3;
# 查询工资最高的人的详细信息
select * from emp order by salary desc limit 1;

# 分页显示
select * from emp limit 0,5;  # 第一个参数表示起始位置，第二个参数表示的是条数，不是索引位置
select * from emp limit 5,5;

# 如果确定查询结果只有一条时，加上limti 1 可以提高查询速度。
           

正则

select * from emp where name regexp '^j.*(n|y)$';
           

replace 替换

replace(str1,old,new) # str1：需要替换的字段名
update gd_km set mc=replace（mc,'土地','房子'）
说明：new替换str1中出现的所有old，返回新的字符串，如果有某个参数为NULL，此函数返回NULL 
该函数可以多次替换，只要str1中还有old存在，最后都被替换成new 
若new为空，则删除old
           

多表查询

内连接、左连接、右连接、全连接

# 内连接：只取两张表有对应关系的记录（只拼两个表共有的）
左表 inner join 右表 on 条件
select * from emp inner join dep on emp.dep_id = dep.id
    where dep.name = "技术";

# 左连接：在内连接的基础上，保留左边的数据，右边没有就为空
左表 inner left 右表 on 条件


# 右连接：在内连接的基础上，保留右边的数据，左边没有就为空
左表 inner right 右表 on 条件

# 全连接：左右连接都有，用union连接
左表 inner left 右表 on 条件 union 左表 inner right 右表 on 条件

select * from emp left join dep on emp.dep_id = dep.id
union
select * from emp right join dep on emp.dep_id = dep.id;
           

子查询

# 就是将一个查询语句的结果用括号括起来当作另外一个查询语句的条件去用
select name from where id in(select dep_id from emp where age>25);
           

mysql查询一张表所有的字段名称

select COLUMN_NAME, column_comment from INFORMATION_SCHEMA.Columns where table_name='你的表名’
           

日期

时间格式化

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')
           

常用方法

获取当前时间

SELECT CURDATE() -- 2020-09-27
CURRENT_TIME() -- 00:00:00
SELECT NOW() -- 2020-09-27 00:00:00
           

获取当时时间戳（10位）

SELECT UNIX_TIMESTAMP() -- 1601190811
           

格式化时间戳

SELECT FROM_UNIXTIME(unix_timestamp(),'%Y-%m-%d %H:%i:%s')
           

处理13位的时间戳

SELECT FROM_UNIXTIME(16011908110000/1000,'%Y-%m-%d %H:%i:%s')
           

PS:

1. 10位时间戳和13位时间戳的区别：

   java的date默认精度是毫秒，也就是说生成的时间戳就是13位的，而像c++或者php因为其精度是秒，所以生成的时间戳默认就是10位的。

2. 如何相互转换

   将13位时间戳除以1000取整，将10位时间戳*1000L

返回两个日期相减（expr1 − expr2 ）相差的天数

DATEDIFF(expr1, expr2) 

SELECT DATEDIFF('2001-01-01','2001-02-02') -- 32
SELECT DATEDIFF('2019-01-14 14:32:59','2019-01-02') -- 12
           

返回两个日期相减（expr1 − expr2 ）相差的时间数

TIMEDIFF(expr1, expr2) # 两个参数类型必须相同
SELECT TIMEDIFF('18:32:59','60000') -- 12:32:59  
SELECT TIMEDIFF('18:32:59','2019-1-1 60000') -- NULL
           

日期时间运算函数

# 分别为给定的日期date加上(add)或减去(sub)一个时间间隔值expr
　# 格式:
　DATE_ADD(date, INTERVAL  expr  unit);
　DATE_SUB(date, INTERVAL  expr  unit);
　
　interval是间隔类型关键字
  expr是一个表达式，对应后面的类型
  unit是时间间隔的单位(间隔类型)（20个）
           

interval	含义
HOUR	小时
MINUTE	分
SECOND	秒
MICROSECOND	毫秒
YEAR	å¹´
MONTH	月
DAY	日
WEEK	周
QUARTER	å­£
YEAR_MONTH	年和月
DAY_HOUR	日和小时
DAY_MINUTE	日和分钟
DAY_ SECOND	日和秒
HOUR_MINUTE	小时和分
HOUR_SECOND	小时和秒
MINUTE_SECOND	分钟和秒

# 加一天
select now(),date_add(now(),interval 1 day);　　
+---------------------+--------------------------------+
| now()               | date_add(now(),interval 1 day) |
+---------------------+--------------------------------+
| 2020-01-01 00:59:59 | 2020-01-02 00:59:59          |
+---------------------+--------------------------------+

# 减1天1小时1分1秒
SELECT date_sub('2020-01-03 00:00:00',INTERVAL '1 1:1:1' DAY_SECOND) -- 2020-01-01 22:58:59
           

选取日期时间的各个部分(常用)

SELECT now(),date(now()); -- 日期

SELECT now(),time(now()); -- 时间

SELECT now(),year(now()); -- å¹´

SELECT now(),quarter(now()); -- 季度

SELECT now(),month(now()); -- 月

SELECT now(),week(now()); -- 周

SELECT now(),day(now()); -- 日

SELECT now(),hour(now()); -- 小时

SELECT now(),minute(now()); -- 分钟

SELECT now(),second(now()); -- 秒

SELECT now(),microsecond(now()); -- 微秒
           

日期位置计算

# DAYOFWEEK函数返回日期的工作日索引值，即星期日为1，星期一为2，星期六为7。 这些索引值对应于ODBC标准。

# 返回日期d在一周、一月、一年中是第几天
dayofweek(d)
dayofmonth(d)
dayofyear(d)

# 计算日期 d 是本年的第几个星期，范围是 0 到 53
WEEK(d)
SELECT WEEK('2011-11-11 11:11:11') -- 45

# 计算日期 d 距离 0000 年 1 月 1 日的天数
TO_DAYS(d)
SELECT TO_DAYS('0001-01-01 01:01:01') -- 366

# 日期 d 是星期几，0 表示星期一，1 表示星期二
WEEKDAY(d)
SELECT WEEKDAY("2017-06-15") -- 3

# 计算日期 d 是本年的第几个星期，范围是 0 到 53
WEEKOFYEAR(d)
SELECT WEEKOFYEAR('2011-11-11 11:11:11') -- 45
           

查询例子

# 查询近一天：
select * from table where to_days(column_time) = to_days(now());
select * from table where date(column_time) = curdate(); 

# 查询近一周：
select * from table  where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(column_time);

# 查询近一个月：
select * from table  where DATE_SUB(CURDATE(), INTERVAL INTERVAL 1 MONTH) <= date(column_time);
           

mybatis xml 查询时间范围

<if test="eventCondition.times != null">
            <foreach collection="eventCondition.times" index="index" item="item">
                <if test="index == 0 and item != ''">
                    AND DATE_FORMAT( XFRQ ,'%Y-%m-%d') &gt;= DATE_FORMAT( #{item},'%Y-%m-%d')
                </if>
                <if test="index == 1 and item != ''">
                    AND DATE_FORMAT( XFRQ ,'%Y-%m-%d') &lt;= DATE_FORMAT( #{item},'%Y-%m-%d')
                </if>
            </foreach>
        </if>
           

查前十来电比例

SELECT EVENT_NAME, LDCS
	, CONCAT(ROUND(LDCS / (
		SELECT SUM(LDCS)
		FROM EVENT_12345_INFO
	) * 100.00, 2), '%') AS proportion
FROM EVENT_12345_INFO
ORDER BY LDCS DESC
LIMIT 10
           

查询每天的平均办理时间和最大办理所花时间

SELECT DATE_FORMAT(xi.BJSJ, '%Y-%m-%d') AS BJSJ
	, ROUND(AVG(TIMESTAMPDIFF(SECOND, xi.DJSJ, xi.BJSJ) / 3600), 2) AS avgHours
	, MAX(TIMESTAMPDIFF(HOUR, xi.DJSJ, xi.BJSJ)) AS maxHours
	, COUNT(1) AS once
FROM EVENT_XFJ_INFO exi
	LEFT JOIN XFJ_INFO xi ON exi.EVENT_ID = xi.EVENT_ID
WHERE exi.EVENT_CLASS = '其他'
	AND xi.BJBZ = 1
GROUP BY DATE_FORMAT(xi.BJSJ, '%Y-%m-%d')
ORDER BY DATE_FORMAT(xi.BJSJ, '%Y-%m-%d') DESC
           

展示菜场的菜最后一次记录价格与前一次记录价格的对比情况

SELECT 

		a.id,
		a.food_name AS foodName,
		a.food_pic AS foodPic,
		a.price,
		b.price AS originalPrice,
		(
			SELECT
			`name`
			FROM
			tb_market
			WHERE
			market_id = a.market_id
			) marketName,
		CASE
        WHEN a.price - IF(b.price IS NULL,0,b.price) > 0 THEN
        '上涨'
        WHEN a.price - b.price = 0 THEN
        '持平'
        WHEN a.price - b.price < 0 THEN
        '下降'
        END AS trend
 FROM(

SELECT * FROM tb_market_price WHERE id in( 
select 
substring_index(substring_index(group_concat(tmp.id order by tmp.id desc ),',',2),',',1) AS '最新菜品id' 
	from `tb_market_price` tmp			
			group by tmp.food_name,tmp.market_id
)

) a INNER JOIN (

SELECT * FROM tb_market_price WHERE id in( 
select 
substring_index(substring_index(group_concat(tmp.id order by tmp.id desc ),',',2),',',-1) AS '最新菜品前一次id' 
	from `tb_market_price` tmp			
			group by tmp.food_name,tmp.market_id
)

) b ON a.food_name = b.food_name AND a.market_id = b.market_id