【跟着例子学MySQL】SQL进阶 -- 子查询和时间

于 2024-05-27 16:56:39 发布
阅读量982 收藏 30
点赞数 22
分类专栏： # 数据库入门 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/nick0314221_1/article/details/139240536
版权

文章目录

前言

在这里插入图片描述
举例子，是最简单有效的学习方法。本系列文章以一个贯穿始终的场景，结合多个实例讲解MySQL的基本用法。

❔ 为什么要写这个系列？

  • 模仿是最好的老师，实践是检验成果的方法。
  • 本系列以实操样例和应用场景为核心，将MySQL基本用法贯穿其中，达到学以致用的效果。

❔ 为什么要学习MySQL？

  • MySQL是最常用的数据库之一，具有简单易用的特点，适合初学者学习
  • 数据库的基本用法有相通之处，学会MySQL可以为学习其他数据库打下良好基础

❔ 跟别的入门教材有什么不同？

  • 以一个贯穿始终的应用场景为主线，渐进地讲解用法
  • 难度适中，既有基础方法，也有值得注意的关键细节

❔ 本系列文章不包含哪些内容？

该系列文章的目标是，将最核心的知识，以最直接的方式，呈现给初学者。因此不包含：

  • MySQL安装方法
  • MySQL系统管理方法，例如备份、恢复、导入导出等
  • 高级主题，例如数据库监控、数据库调优和SQL优化

回顾

上篇文章👉《【跟着例子学MySQL】 补充内容 – 主外键和索引》 讲了更多关于主键、外键和索引的知识。这篇开始简介SQL进阶的用法。

子查询

一个查询的结果可以在另一个SQL语句中使用。如果涉及到多个表，则子查询会很有用。

包含子查询的SELECT

在前面的多对多产品销售示例中，如何找到不提供任何产品的供应商？你可以在products_suppliers表中查询提供至少一种产品的供应商，然后查询供应商表中不在上一个结果集中的供应商。

mysql> SELECT suppliers.name from suppliers
         WHERE suppliers.supplierID
         NOT IN (SELECT DISTINCT supplierID from products_suppliers);

你能不使用子查询做到吗？

子查询可以返回一个标量、单个列、一行或一个表。你可以标量使用比较运算符（例如，‘=’，‘>’），对单行或列使用IN或NOT IN ，使用EXISTS或NOT EXIST在来测试空集。

带有子查询的INSERT|UPDATE|DELETE

你还可以将子查询与其他SQL语句一起使用，如插入、删除或更新。例如：

-- 供应商“QQ Corp”现在供应“Pencil 6B”
-- 你需要将Select子句放在括号中
mysql> INSERT INTO products_suppliers VALUES (
         (SELECT productID FROM products WHERE name = 'Pencil 6B'),
         (SELECT supplierID FROM suppliers WHERE name = 'QQ Corp'));
-- 供应商“QQ Copr”不再供应任何项目
mysql> DELETE FROM products_suppliers
          WHERE supplierID = (SELECT supplierID FROM suppliers WHERE name = 'QQ Corp');

日期和时间

日期和时间对数据库应用程序特别重要。这是因为业务记录通常携带日期/时间信息（例如，订单日期、交付日期、付款日期、出生日期），以及需要及时创建和最后更新记录，以进行审计和安全。使用日期/时间数据类型，你可以按日期对结果进行排序，搜索特定日期或日期范围，计算日期之间的差异，通过从给定日期中添加/减去时间间隔来计算新日期。

日期例子

让我们从日期（没有时间）开始。请注意，日期值必须被写入为“yyyy-mm-dd”格式的字符串，例如，“2012-01-31”。

-- 创建一个诊所的“patients”表格
mysql> CREATE TABLE patients (
           patientID INT UNSIGNED NOT NULL AUTO_INCREMENT,
           name VARCHAR(30) NOT NULL DEFAULT '',
           dateOfBirth DATE NOT NULL,
           lastVisitDate DATE NOT NULL,
           nextVisitDate DATE NULL,
           -- “日期”类型包含“yyyy-mm-dd”中的日期值
           PRIMARY KEY (patientID)
        );
mysql> INSERT INTO patients VALUES
         (1001, 'Ah Teck', '1991-12-31', '2012-01-20', NULL),
         (NULL, 'Kumar', '2011-10-29', '2012-09-20', NULL),
         (NULL, 'Ali', '2011-01-30', CURDATE(), NULL);
         -- 日期必须写成“yyyy-mm-dd”
         -- 函数CURDATE()返回今天的日期
mysql> SELECT * FROM patients;
+-----------+---------+-------------+---------------+---------------+
| patientID | name    | dateOfBirth | lastVisitDate | nextVisitDate |
+-----------+---------+-------------+---------------+---------------+
| 1001      | Ah Teck | 1991-12-31  | 2012-01-20    | NULL          |
| 1002      | Kumar   | 2011-10-29  | 2012-09-20    | NULL          |
| 1003      | Ali     | 2011-01-30  | 2012-10-21    | NULL          |
+-----------+---------+-------------+---------------+---------------+
-- 选择最后一次访问是在特定日期范围内的患者
mysql> SELECT * FROM patients
           WHERE lastVisitDate BETWEEN '2012-09-15' AND CURDATE()
           ORDER BY lastVisitDate;
+-----------+-------+-------------+---------------+---------------+
| patientID | name  | dateOfBirth | lastVisitDate | nextVisitDate |
+-----------+-------+-------------+---------------+---------------+
| 1002      | Kumar | 2011-10-29  | 2012-09-20    | NULL          |
| 1003      | Ali   | 2011-01-30  | 2012-10-21    | NULL          |
+-----------+-------+-------------+---------------+---------------+
-- 选择在特定年份出生的患者，并按出生月份进行分类
-- 功能 YEAR(date), MONTH(date), DAY(date)返回给定日期的年、月、日的一部分
mysql> SELECT * FROM patients
          WHERE YEAR(dateOfBirth) = 2011
          ORDER BY MONTH(dateOfBirth), DAY(dateOfBirth);
+-----------+-------+-------------+---------------+---------------+
| patientID | name  | dateOfBirth | lastVisitDate | nextVisitDate |
+-----------+-------+-------------+---------------+---------------+
| 1003      | Ali   | 2011-01-30  | 2012-10-21    | NULL          |
| 1002      | Kumar | 2011-10-29  | 2012-09-20    | NULL          |
+-----------+-------+-------------+---------------+---------------+
-- 选择生日是今天的患者
mysql> SELECT * FROM patients
           WHERE MONTH(dateOfBirth) = MONTH(CURDATE())
           AND DAY(dateOfBirth) = DAY(CURDATE());
-- 列出患者的年龄
-- 函数n TIMESTAMPDIFF(unit, start, end)返回指定单位的差异
mysql> SELECT name, dateOfBirth, TIMESTAMPDIFF(YEAR, dateOfBirth, CURDATE()) AS age
           FROM patients
           ORDER BY age, dateOfBirth;
+---------+-------------+------+
| name    | dateOfBirth | age  |
+---------+-------------+------+
| Kumar   | 2011-10-29  | 0    |
| Ali     | 2011-01-30  | 1    |
| Ah Teck | 1991-12-31  | 20   |
+---------+-------------+------+
-- 列出最后一次就诊超过60天的患者
mysql> SELECT name, lastVisitDate FROM patients
           WHERE TIMESTAMPDIFF(DAY, lastVisitDate, CURDATE()) > 60;
-- 函数 TO_DAYS(date)将日期转换为天
mysql> SELECT name, lastVisitDate FROM patients
           WHERE TO_DAYS(CURDATE()) - TO_DAYS(lastVisitDate) > 60;
           
-- 选择18岁或以下的患者
-- 函数DATE_SUB(date, INTERVAL x unit)返回日期
-- 用x单位减去给定的日期。
mysql> SELECT * FROM patients
           WHERE dateOfBirth > DATE_SUB(CURDATE(), INTERVAL 18 YEAR);
           
-- 把Ali的下一次访问安排在6个月后
-- 函数DATE_ADD(date, INTERVAL x unit)返回日期
-- 通过以x为单位添加给定的日期
mysql> UPDATE patients
           SET nextVisitDate = DATE_ADD(CURDATE(), INTERVAL 6 MONTH)
           WHERE name = 'Ali';

Date/Time 函数

MySQL提供了这些内置函数，用于获取当前日期、时间和日期时间：

  • NOW() ：以‘YYYY-MM-DD HH:MM:SS’的格式返回当前日期和时间。
  • CURDATE()（或CURRENT_DATE()，或CURRENT_DATE）：返回“YYYY-MM-DD”格式的当前日期。
  • CURTIME()（或CURRENT_TIME()，或CURRENT_TIME）：以“HH:MM:SS”的格式返回当前时间。测试例

例如

mysql> select now(), curdate(), curtime();
+---------------------+------------+-----------+
| now()               | curdate()  | curtime() |
+---------------------+------------+-----------+
| 2012-10-19 19:53:20 | 2012-10-19 | 19:53:20  |
+---------------------+------------+-----------+

Date/Time 类型

MySQL提供了以下日期/时间的数据类型：

  • DATETIME：以“YYYY-MM-DD HH:MM:SS”的格式存储日期和时间。有效范围为“1000-01-01 00:00:00”到“9999-12-31 23:59:59”。你可以使用有效格式（例如，‘2011-08-15 00:00:00’）设置值。也可以应用函数NOW()或 CURDATE()（时间将设置为“00:00:00”），但不能使用CURTIME()。
  • DATE：仅以“YYYY-MM-DD”的格式存储日期。这个范围是“1000-01-01”到“9999-12-31”。你可以在此字段上应用CURDATE()或NOW()（时间丢弃）。
  • TIME：仅以“HH:MM:SS”的格式存储时间。您可以应用 CURTIME() 或 NOW()（日期丢弃）
  • YEAR(4|2)：在“YYYY”或“YY”中。年数的范围是1901年到2155年。在此范围之外的年份使用日期类型。你可以将CURDATE()（月和日丢弃）。
  • TIMESTAMP：类似于日期时间，但存储了自1970年1月1日以来的秒数（unix风格）。活动范围是“1970-01-
    01 00:00:00”到“2037-12-31 23:59:59”。
    • 日期时间和时间戳之间的差异是：
      • 范围
      • 时区支持
      • 可以使用默认的CURRENT_TIMESTAMP声明时间戳列，以将默认值设置为当前的日期/时间。（所有其他数据类型的默认值，包括日期时间，必须是常量，而不是函数返回值）。你还可以使用ON UPDATE CURRENT_TIMESTAMP声明一个时间戳列，以捕获上次更新的时间戳。

日期/时间值可以作为字符串文字手动输入（例如，“2010-12-31 23:59:59”）。如果要插入的日期/时间值无效或超出范围，MySQL将发出警告并插入所有0（例如，‘0000-00-00 00:00:00）。"0000-00-00”被称为“虚拟”日期。

更多Date/Time 函数

有许多日期/时间功能：
提取部分日期/时间：YEAR(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND()，例如，

mysql> SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW()), HOUR(NOW()), MINUTE(NOW()), SECOND(NOW());
+-------------+--------------+------------+-------------+---------------+---------------+
| YEAR(NOW()) | MONTH(NOW()) | DAY(NOW()) | HOUR(NOW()) | MINUTE(NOW()) | SECOND(NOW()) |
+-------------+--------------+------------+-------------+---------------+---------------+
| 2012        | 10           | 24         | 11          | 54            | 45            |
+-------------+--------------+------------+-------------+---------------+---------------+

提取信息：DAYNAME()（例如，“Monday”），MONTHNAME()（例如，“March”），DAYOFWEEK()（1=Sunday，…，7=Saturday），DAYOFYEAR()（1-366），……。

mysql> SELECT DAYNAME(NOW()), MONTHNAME(NOW()), DAYOFWEEK(NOW()), DAYOFYEAR(NOW());
+----------------+------------------+------------------+------------------+
| DAYNAME(NOW()) | MONTHNAME(NOW()) | DAYOFWEEK(NOW()) | DAYOFYEAR(NOW()) |
+----------------+------------------+------------------+------------------+
| Wednesday      | October          | 4                | 298              | 
+----------------+------------------+------------------+------------------+

计算另一个日期/时间：DATE_SUB(date, INTERVAL expr unit), DATE_ADD(date, INTERVAL expr unit),
TIMESTAMPADD(unit, interval, timestamp)，例如:

mysql> SELECT DATE_ADD('2012-01-31', INTERVAL 5 DAY);
2012-02-05
mysql> SELECT DATE_SUB('2012-01-31', INTERVAL 2 MONTH);
2011-11-30

计算间隔：DATEDIFF(end_date, start_date), TIMEDIFF(end_time, start_time), TIMESTAMPDIFF(unit,
start_timestamp, end_timestamp)，例如：

mysql> SELECT DATEDIFF('2012-02-01', '2012-01-28');
4
mysql> SELECT TIMESTAMPDIFF(DAY, '2012-02-01', '2012-01-28');
-4

表示：TO_DAYS(date)（自第0年以来的天数），FROM_DAYS（天编号），例如:

mysql> SELECT TO_DAYS('2012-01-31');
734898
mysql> SELECT FROM_DAYS(734899);
2012-02-01

格式化： DATE_FORMAT(date, formatSpecifier)，例如，

mysql> SELECT DATE_FORMAT('2012-01-01', '%W %D %M %Y');
Sunday 1st January 2012
        -- %W: 星期名字
        -- %D: 带后缀的日期
        -- %M: 月名
        -- %Y: 4位数年份
        -- 格式说明符可区分大小写
mysql> SELECT DATE_FORMAT('2011-12-31 23:59:30', '%W %D %M %Y %r');
         Saturday 31st December 2011 11:59:30 PM
        -- %r: 时间为12小时的格式，后缀为AM/PM

例子

1.创建一个包含各种日期/时间列的表。只有时间戳列可以有默认的CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP。

mysql> CREATE TABLE IF NOT EXISTS `datetime_arena` (
        `description` VARCHAR(50) DEFAULT NULL,
        `cDateTime` DATETIME DEFAULT '0000-00-00 00:00:00',
        `cDate` DATE DEFAULT '0000-00-00',
        `cTime` TIME DEFAULT '00:00:00',
        `cYear` YEAR DEFAULT '0000',
        `cYear2` YEAR(2) DEFAULT '00',
        `cTimeStamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
     );
mysql> DESCRIBE `datetime_arena`;
+-------------+-------------+------+-----+---------------------+-----------------------------+
| Field       | Type        | Null | Key | Default             | Extra                       |
+-------------+-------------+------+-----+---------------------+-----------------------------+
| description | varchar(50) | YES  |     | NULL                |                             |
| cDateTime   | datetime    | YES  |     | 0000-00-00 00:00:00 |                             |
| cDate       | date        | YES  |     | 0000-00-00          |                             |
| cTime       | time        | YES  |     | 00:00:00            |                             |
| cYear       | year(4)     | YES  |     | 0000                |                             |
| cYear2      | year(2)     | YES  |     | 00                  |                             |
| cTimeStamp  | timestamp   | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
+-------------+-------------+------+-----+---------------------+-----------------------------+

2.使用字符串文字手动插入值。

mysql> INSERT INTO `datetime_arena`
          (`description`, `cDateTime`, `cDate`, `cTime`, `cYear`, `cYear2`)
        VALUES
          ('Manual Entry', '2001-01-01 23:59:59', '2002-02-02', '12:30:30', '2004', '05');
mysql> SELECT * FROM `datetime_arena` WHERE description='Manual Entry';
+--------------+---------------------+------------+----------+-------+--------+---------------------+
| description  | cDateTime           | cDate      | cTime    | cYear | cYear2 | cTimeStamp          |
+--------------+---------------------+------------+----------+-------+--------+---------------------+
| Manual Entry | 2001-01-01 23:59:59 | 2002-02-02 | 12:30:30 | 2004  | 05     | 2010-04-08 14:44:37 |
+--------------+---------------------+------------+----------+-------+--------+---------------------+

3.正在检查更新时的时间戳。

mysql> UPDATE `datetime_arena` SET `cYear2`='99' WHERE description='Manual Entry';
mysql> SELECT * FROM `datetime_arena` WHERE description='Manual Entry';
+--------------+---------------------+------------+----------+-------+--------+---------------------+
| description  | cDateTime           | cDate      | cTime    | cYear | cYear2 | cTimeStamp          |
+--------------+---------------------+------------+----------+-------+--------+---------------------+
| Manual Entry | 2001-01-01 23:59:59 | 2002-02-02 | 12:30:30 | 2004  | 99     | 2010-04-08 14:44:48 |
+--------------+---------------------+------------+----------+-------+--------+---------------------+

4.使用MySQL内置函数 now(), curdate(), curtime()插入值。

mysql> INSERT INTO `datetime_arena`
          (`description`, `cDateTime`, `cDate`, `cTime`, `cYear`, `cYear2`)
       VALUES
          ('Built-in Functions', now(), curdate(), curtime(), now(), now());
mysql> SELECT * FROM `datetime_arena` WHERE description='Built-in Functions';
+--------------------+---------------------+------------+----------+-------+--------+---------------------+
| description        | cDateTime           | cDate      | cTime    | cYear | cYear2 | cTimeStamp          |
+--------------------+---------------------+------------+----------+-------+--------+---------------------+
| Built-in Functions | 2010-04-08 14:45:48 | 2010-04-08 | 14:45:48 | 2010  | 10     | 2010-04-08 14:45:48 |
+--------------------+---------------------+------------+----------+-------+--------+---------------------+

5.插入无效的值或超出范围的值。MySQL被替换为所有的零。

mysql> INSERT INTO `datetime_arena`
          (`description`, `cDateTime`, `cDate`, `cTime`, `cYear`, `cYear2`)
       VALUES
         ('Error Input', '2001-13-31 23:59:59', '2002-13-31', '12:61:61', '99999', '999');
mysql> SELECT * FROM `datetime_arena` WHERE description='Error Input';
+-------------+---------------------+------------+----------+-------+--------+---------------------+
| description | cDateTime           | cDate      | cTime    | cYear | cYear2 | cTimeStamp          |
+-------------+---------------------+------------+----------+-------+--------+---------------------+
| Error Input | 0000-00-00 00:00:00 | 0000-00-00 | 00:00:00 | 0000  | 00     | 2010-04-08 14:46:10 |
+-------------+---------------------+------------+----------+-------+--------+---------------------+

6.一个有用的内置函数区间可以用来计算未来的日期，例如，

mysql> SELECT `cDate`, `cDate` + INTERVAL 30 DAY, `cDate` + INTERVAL 1 MONTH FROM `datetime_arena`;
+------------+---------------------------+----------------------------+
| cDate      | `cDate` + INTERVAL 30 DAY | `cDate` + INTERVAL 1 MONTH |
+------------+---------------------------+----------------------------+
| 2002-02-02 | 2002-03-04                | 2002-03-02                 |
| 2010-04-08 | 2010-05-08                | 2010-05-08                 |
| 0000-00-00 | NULL                      | NULL                       |
+------------+---------------------------+----------------------------+

未完待续

下一篇我们接着介绍视图的用法。

如果喜欢这篇文章，请不要忘记关注🧡、点赞👍和收藏📔哦！

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

架构师昌哥

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值