新手村之SQL——函数多表联结
1.将数值四舍五入——ROUND
ROUND( X )：返回参数 X 四舍五入后的一个整数。
ROUND(X, D)： 返回参数 X 四舍五入且保留 D 位小数后的一个数字。如果 D 为 0，结果将没有小数点或小数部分。
2.判断字段是否为NULL——ISNULL
是否为 NULL 值返回 0 或 1。
SELECT ISNULL(`column_name`)
FROM `table_name`;
IFNULL() 函数也用于判断字段是否为NULL，但是与 ISNULL() 不同的是它接收两个参数，第一个参数 column_name 为列名，第二个参数 value 相当于备用值。
SELECT IFNULL(`column_name`, `value`)
FROM `table_name`;
3.获取当前时间——NOW() 、 CURDATE()、CURTIME()
NOW() 可以用来返回当前日期和时间 格式：YYYY-MM-DD hh:mm:ss
CURDATE() 可以用来返回当前日期 格式：YYYY-MM-DD
CURTIME() 可以用来返回当前时间 格式：hh:mm:ss
在使用 NOW() 和 CURTIME() 时，如果要精确的秒以后的时间的话，可以在（）中加数字，加多少，就表示精确到秒后多少位。
比如 NOW(3) 就是精确到毫秒，表示为： 2021-03-31 15:27:20.645
4.提取日期和时间——DATE()、TIME()
SELECT DATE('2021-03-25 16:16:30') AS `date`,TIME('2021-03-25 16:16:30') AS `time`;
+------------+----------+
| date | time |
+------------+----------+
| 2021-03-25 | 16:16:30 |
+------------+----------+
1 row in set
mysql> SELECT `name`, `created_at`,
-> DATE_FORMAT(DATE(`created_at`),"%Y-%m-%d") AS `created_date`,
-> DATE_FORMAT(TIME(`created_at`),"%H:%i:%s") AS `created_time`
-> FROM `courses`;
+-------------------------+---------------------+--------------+--------------+
| name | created_at | created_date | created_time |
+-------------------------+---------------------+--------------+--------------+
| Advanced Algorithms | 2020-06-01 09:10:12 | 2020-06-01 | 09:10:12 |
| System Design | 2020-07-18 10:11:12 | 2020-07-18 | 10:11:12 |
| Django | 2020-02-29 12:10:12 | 2020-02-29 | 12:10:12 |
| Web | 2020-04-22 13:01:12 | 2020-04-22 | 13:01:12 |
| Big Data | 2020-09-11 16:01:12 | 2020-09-11 | 16:01:12 |
| Artificial Intelligence | 2018-05-13 18:12:30 | 2018-05-13 | 18:12:30 |
| Java P6+ | 2019-01-19 13:31:12 | 2019-01-19 | 13:31:12 |
| Data Analysis | 2019-07-12 13:01:12 | 2019-07-12 | 13:01:12 |
| Object Oriented Design | 2020-08-08 13:01:12 | 2020-08-08 | 13:01:12 |
| Dynamic Programming | 2018-08-18 20:01:12 | 2018-08-18 | 20:01:12 |
+-------------------------+---------------------+--------------+--------------+
10 rows in set
5.提取指定的时间信息——EXTRACT
EXTRACT() 函数用于返回日期/时间的单独部分，如 YEAR (年)、MONTH (月)、DAY (日)、HOUR (小时)、MINUTE (分钟)、 SECOND (秒)。
mysql> SELECT `name`, EXTRACT(HOUR FROM `created_at`) AS `created_hour`
FROM `courses`;
+-------------------------+--------------+
| name | created_hour |
+-------------------------+--------------+
| Advanced Algorithms | 9 |
| System Design | 10 |
| Django | 12 |
| Web | 13 |
| Big Data | 16 |
| Artificial Intelligence | 18 |
| Java P6+ | 13 |
| Data Analysis | 13 |
| Object Oriented Design | 13 |
| Dynamic Programming | 20 |
+-------------------------+--------------+
10 row in set
6.格式化输出日期——DATE_FORMAT()
mysql> SELECT DATE_FORMAT(`created_at`, '%Y %m') AS `DATE_FORMAT`
-> FROM `courses`;
+-------------+
| DATE_FORMAT |
+-------------+
| 2020 06 |
| 2020 07 |
| 2020 02 |
| 2020 04 |
| 2020 09 |
| 2018 05 |
| 2019 01 |
| 2019 07 |
| 2020 08 |
| 2018 08 |
+-------------+
10 rows in set (0.01 sec)
7.增加和减少时间——DATE_ADD&DATE_SUB
select name,date_add(created_at,interval 1 day) as new_created from courses
推迟一天
8.时间差——TIMESTAMPDIFF
select TIMESTAMPDIFF(month,created_at,'2020-04-22') as 'MonthDiff' from courses
8.JOIN 连接子句
INNER JOIN：如果表中有至少一个匹配，则返回行
LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
FULL JOIN：只要其中一个表中存在匹配，则返回行
CROSS JOIN：又称笛卡尔积，两个表数据一一对应，返回结果的行数等于两个表行数的乘积
INNER JOIN：
标准语法：
SELECT `table1`.`column1`, `table2`.`column2`...
FROM `table1`
INNER JOIN `table2`
ON `table1`.`common_field` = `table2`.`common_field`;
示例：
SELECT `c`.`id`, `c`.`name` AS `course_name`, `t`.`name` AS `teacher_name`
FROM `courses` `c`
INNER JOIN `teachers` `t` ON `c`.`teacher_id` = `t`.`id`;
LEFT JOIN：
以关键字 LEFT JOIN 左边的表为参考表。左外连接的结果包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是连接列所匹配的行，这就意味着，左连接会返回左表中的所有记录，加上右表中匹配到的记录。如果左表的某行在右表中没有匹配行，那么在相关联的结果行中，右表的所有选择列表均为空值。
mysql-> SELECT c.name AS course_name, t.name AS teacher_name
-> FROM teachers t
LEFT JOIN courses c ON c.teacher_id = t.id;
+------------------------+--------------------+
| course_name | teacher_name |
+------------------------+--------------------+
| Big Data |Eastern Heretic |
| Data Analysis |Eastern Heretic |
| Dynamic Programming |Eastern Heretic |
| NULL |Northern Beggar |
| System Design |Western Venom |
| Django |Western Venom |
| Artificial Intelligence|Western Venom |
| Java P6+ |Western Venom |
| Senior Algorithm |Southern Emperor |
| Web |Southern Emperor |
| Object Oriented Design |Southern Emperor |
| NULL |Linghu Chong |
+------------------------+--------------------+
12 rows in set (0.01 sec)
RIGHT JOIN:
以关键字 RIGHT JOIN 右边的表为参考表，如果右表的某行在左表中没有匹配行，左表就返回空值。
mysql-> SELECT c.name AS course_name, t.name AS teacher_name, t.email AS teacher_email
-> FROM courses c
RIGHT JOIN teachers t ON c.teacher_id = t.id;
+------------------------+----------------------+---------------------------+
| course_name | teacher_name | teacher_email |
+------------------------+----------------------+---------------------------+
| Dynamic Programming | Eastern Heretic | eastern.heretic@gmail.com |
| Data Analysis | Eastern Heretic | eastern.heretic@gmail.com |
| Big Data | Eastern Heretic | eastern.heretic@gmail.com |
| Dynamic Programming | Northern Beggar | northern.beggar@qq.com |
| Java P6+ | Western Venom | western.venom@163.com |
| Artificial Intelligence| Western Venom | western.venom@163.com |
| Django | Western Venom | western.venom@163.com |
| System Design | Western Venom | western.venom@163.com |
| Object Oriented Design | Southern Emperor | southern.emperor@qq.com |
| Web | Southern Emperor | southern.emperor@qq.com |
| Advanced Algorithms | Southern Emperor | southern.emperor@qq.com |
| NULL | Linghu Chong | NULL |
+------------------------+----------------------+---------------------------+
12 rows in set (0.01 sec)
FULL (OUTER) JOIN:
只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行。FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。
MySQL 数据库不支持全连接，想要实现全连接可以使用 UNION ALL 来将左连接和右连接结果组合在一起实现全连接。
mysql-> SELECT c.name AS course_name, t.age AS teacher_age
-> FROM courses c
LEFT JOIN teachers t ON c.teacher_id = t.id
-> UNION
-> SELECT c.name AS course_name, t.age AS teacher_age
-> FROM courses c
RIGHT JOIN teachers t ON c.teacher_id = t.id;
+------------------------+---------------+
| course_name | teacher_age |
+------------------------+---------------+
| Advanced Algorithms | 21 |
| System Design | 28 |
| Django | 28 |
| Web Southern | 21 |
| Big Data | 20 |
| Artificial Intelligence| 28 |
| Java P6+ | 28 |
| Data Analysis Eastern | 20 |
| Object Oriented Design | 21 |
| Dynamic Programming | 20 |
| Linghu Chong | 18 |
| NULL | 21 |
| NULL | 18 |
+------------------------+----------------------+---------------------------+
13 rows in set (0.01 sec)