MySQL 学习小结

MySQL 学习小结
- 多表查询
  - 子查询
  - 连接查询
    - inner join 或 join
    - left join
    - right join
    - full join
- 日期
  - 常用内建日期函数
  - 日期查询简化
  - 格式化
  - 查询时间
    - 当前年月日年中第几天年中第几周
    - 本月第一天本月最后一天
    - 本月有多少天
  - 前后 n 年季月周时分秒
  - 昨天今天明天
  - 上周本周
  - 上一月本月
  - 上一季本季
  - 上一年本年

多表查询

MySQL从4.1版本开始支持子查询，以提高查询的灵活度。

子查询

子查询：把内层查询结果当作外层查询的比较条件。

缺点
执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响，这里多了一个创建和销毁临时表的过程。
优化
使用连接查询代替子查询，连接查询不需要建立临时表，因此其速度比子查询快。

连接查询

inner join 或 join

这里写图片描述
两表都有该数据的记录。

select a.id 
from table1 a, table2 b 
where a.a_id=b.a_id

等价于

select a.id from table1 a inner join table2 b on a.a_id=b.a_id

left join

这里写图片描述
左表所有记录 + 右表关联到的记录。

right join

这里写图片描述
右表所有记录 + 左表关联到的记录。

full join

查只在左表中有、不能在右表中存在的数据

select a.name from a where a.name not in (select name from b)
或
select a.name from a where not exists(select 1 from B where a.name=b.name)
或
select a.name
from a 
    left join b on a.name=b.name
where b.name is null

日期

MySQL 使用下列数据类型在数据库中存储日期或日期/时间值：

DATE - 格式 YYYY-MM-DD
TIME - 格式 HHSS
YEAR - 格式 YYYY 或 YY
DATETIME - 格式: YYYY-MM-DD HHSS
TIMESTAMP - 格式: YYYY-MM-DD HHSS

常用内建日期函数

参考日期函数、日期格式化、时间单位

函数	用途	举例
curdate()	当前日期	2017-11-29
curtime()	当前时间（累加执行时间）	09:32:32
now()	语句开始执行时的日期和时间	2017-11-29 09:32:32
sysdate()	实时的系统日期和时间（累加执行时间）	2017-11-29 09:32:32

date_format()	格式化日期/时间

extract()	提取想要部分
date()	提取日期部分	2017-11-29
hour()	提取小时部分	09
to_days()	返回天数（从公元 0 年开始）
dayOfYear()	返回一年中第几天（1-366）
dayOfMonth()	返回本月中第几天（1-31）
dayOfWeek()	返回本周中第几天(1周日-7周六)

date_add()	日期添加指定间隔
date_sub()	日期减去指定间隔

from_unixtime(unix_timestramp)	将Unix 时间戳转为日期格式	2017-11-29 09:32:32.6560
from_unixtime(unix_timestramp, format)		格式化’%Y-%m-%d %h:%i:%s’

注意：now() 和 curtime()、sysdate() 返回的 Time 可能是不一样的，因为 curtime()、sysdate() 返回的时间会累加执行时间，而 now() 返回的时间永远都是开始执行时的时间：
实际使用时，一般用 now() ，因为用 sysdate() 可能导致主库和从库执行时返回值不一样，导致主从数据库不一致。

发布后格式与编辑预览时是不一致，自己看的话，还是先点编辑再看好了。

日期查询简化

传统对比

select * from dg_user where date_format(updateTime, ‘%Y-%m-%d’) = date_format(NOW(), ‘%Y-%m-%d’);
简写

select * from dg_user where updateTime >= date_format(NOW(),’%Y-%m-%d’);
利用函数（推荐）

SELECT * FROM dg_user WHERE DATEDIFF(updateTime, NOW()) = 0;

格式化

日期格式化

快速参考使用

-- %Y：年（4 位）；%m：月(00-12)；%d：日，月的天(00-31)；
-- %T：时间, 24-小时 (hh:mm:ss)；
-- %H：小时(00-23)；%i：分钟(00-59)；%s：秒(00-59)；%f：毫秒；
select now(), date_format(now(), '%Y-%m-%d %T') from dual;
select now(), date_format(now(), '%Y-%m-%d %H:%i:%s') from dual;

查询时间

当前年、月、日、年中第几天、年中第几周

select year(curdate()), month(curdate()), day(curdate()), dayofyear(curdate()), weekofyear(curdate());

本月第一天、本月最后一天

select DATE_SUB(curdate(), interval day(curdate()) - 1 day);
select last_day(curdate());

本月有多少天

select day(LAST_DAY(now()));
select dayOfMonth(LAST_DAY(now()));

前（后） n 年（季、月、周、时、分、秒）

时间单位

语法

DATE_ADD(date, INTERVAL expr type);
DATE_SUB(date, INTERVAL expr type);
时间 +/- INTERVAL expr type;
-- date 参数是合法的日期表达式； 
-- expr 参数是您希望添加的时间间隔；
-- type 参数是时间单位；

使用

-- 前 7 天
select * from dg_user where date(updateTime) >= date_sub(curdate(), interval 7 day) select * from dg_user where date(updateTime) >= curdate() - interval 7 day

昨天、今天、明天

-- 昨天
select * from dg_user where to_days(updateTime) = to_days(CURDATE()) - 1;
select * from dg_user where DAYOFYEAR(updateTime) = DAYOFYEAR(now()) - 1;
SELECT * FROM dg_user WHERE DATEDIFF(updateTime, CURDATE()) = -1;
select * from dg_user where updateTime between date_sub(now(), interval 1 day) and CURDATE();
-- 今天
select * from dg_user where to_days(updateTime) = to_days(CURDATE());
SELECT * FROM dg_user WHERE DATEDIFF(updateTime, CURDATE()) = 0;
-- 明天
select * from dg_user where to_days(updateTime) = to_days(CURDATE()) + 1;
SELECT * FROM dg_user WHERE DATEDIFF(updateTime, CURDATE()) = 1;

上周、本周

-- 查看今天周几：0周一 ~ 6周日
SELECT WEEKDAY(now());
-- 上周
select * from dg_user where yearweek(date(updateTime)) = yearweek(now()) - 1;
select * from dg_user where yearweek(date_format(updateTime, '%Y-%m-%d')) = yearweek(now()) - 1;
select * from dg_user where period_diff(date_format(updateTime, '%Y%m'), date_format(curdate(), '%Y%m')) = -1;
-- 本周
select * from dg_user where yearweek(date_format(updateTime, '%Y-%m-%d')) = yearweek(now());

上一月、本月

-- 上一月
select * from dg_user where MONTH(updateTime) = MONTH(now()) - 1;
-- 当前时刻的上一月的时间
select now(), date_sub(now(), INTERVAL 1 YEAR_MONTH), date_sub(now(), INTERVAL 1 MONTH), now() - INTERVAL 1 MONTH;
-- 本月
select * from dg_user where date_format(updateTime, '%Y-%m') = date_format(curdate(), '%Y-%m');

上一季、本季

-- 上一季
select * from dg_user where QUARTER(updateTime) = QUARTER(now()) - 1;
-- 本季
select * from dg_user where QUARTER(updateTime) = QUARTER(now());

上一年、本年

-- 上一年
select * from dg_user where year(updateTime) = year(date_sub(now(), interval 1 year));
-- 本年
select * from dg_user where year(updateTime) = year(now());