MySQL 学习小结

清疚 2022-06-03 23:53 274阅读 0赞

# MySQL 学习小结 #

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

## 多表查询 ##

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

### 子查询 ###

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

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

### 连接查询 ###

#### inner join 或 join ####

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

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 ####

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

#### right join ####

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

#### 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 - 格式 HH:MM:SS
 *  YEAR - 格式 YYYY 或 YY
 *  DATETIME - 格式: YYYY-MM-DD HH:MM:SS
 *  TIMESTAMP - 格式: YYYY-MM-DD HH:MM:SS

### 常用内建日期函数 ###

参考[日期函数][Link 1]、[日期格式化][Link 2]、[时间单位][Link 3]

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>用途</th> 
   <th>举例</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>curdate()</td> 
   <td>当前日期</td> 
   <td>2017-11-29</td> 
  </tr> 
  <tr> 
   <td>curtime()</td> 
   <td>当前时间（累加执行时间）</td> 
   <td>09:32:32</td> 
  </tr> 
  <tr> 
   <td>now()</td> 
   <td>语句开始执行时的日期和时间</td> 
   <td>2017-11-29 09:32:32</td> 
  </tr> 
  <tr> 
   <td>sysdate()</td> 
   <td>实时的系统日期和时间（累加执行时间）</td> 
   <td>2017-11-29 09:32:32</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>date_format()</td> 
   <td>格式化日期/时间</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>extract()</td> 
   <td>提取想要部分</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>date()</td> 
   <td>提取日期部分</td> 
   <td>2017-11-29</td> 
  </tr> 
  <tr> 
   <td>hour()</td> 
   <td>提取小时部分</td> 
   <td>09</td> 
  </tr> 
  <tr> 
   <td>to_days()</td> 
   <td>返回天数（从公元 0 年开始）</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>dayOfYear()</td> 
   <td>返回一年中第几天（1-366）</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>dayOfMonth()</td> 
   <td>返回本月中第几天（1-31）</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>dayOfWeek()</td> 
   <td>返回本周中第几天(1周日-7周六)</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>date_add()</td> 
   <td>日期添加指定间隔</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>date_sub()</td> 
   <td>日期减去指定间隔</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>from_unixtime(unix_timestramp)</td> 
   <td>将Unix 时间戳转为日期格式</td> 
   <td>2017-11-29 09:32:32.6560</td> 
  </tr> 
  <tr> 
   <td>from_unixtime(unix_timestramp, format)</td> 
   <td></td> 
   <td>格式化’%Y-%m-%d %h:%i:%s’</td> 
  </tr> 
 </tbody> 
</table>

> **注意**：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;

### 格式化 ###

[日期格式化][Link 2]

快速参考使用

-- %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 年（季、月、周、时、分、秒） ###

[时间单位][Link 3]

语法

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());

[SouthEast]: /images/20220604/34591cc2325f4cab970710677006be24.png
[SouthEast 1]: /images/20220604/79f3e89759104c9798f3a2fc3f483b46.png
[SouthEast 2]: /images/20220604/1efe479b9b234bde8ef4c11f5b974183.png
[Link 1]: https://www.w3cschool.cn/sql/82rg1ozi.html
[Link 2]: http://www.w3school.com.cn/sql/func_date_format.asp
[Link 3]: http://www.w3school.com.cn/sql/func_date_add.asp