SQL 日常练习 (二十)

复习, 子查询, 常用时间函数, 类型转换函数等.

也只是尽快搬完这快一个月 sql 的砖, 准备要来整新学习模块了, 因此, 正好趁着五一, 加波速. 也会一直坚守和追求, 学无止境, 气有浩然. 每次都会说, 这是一种精神的传承,而我想的是, 不仅仅是精神的传承, 更是要实践出来, 我就是要不断地学习和进步, 看看胸中有误氤氲着浩然之气呢?

看着群里, 田兄结婚, com.cn/tag/%e5%90%8c%e5%ad%a6" target="_blank">同学们纷纷到场祝贺, 真的是让我羡慕死, 我也想结婚, 不谈恋爱, 直接结婚那种, 也没啥欲求. 当然这也只是我的表面. 而我真正想的是啥呢, 其实是一种渴望被认可的那种, 但我却始终无法真正去融入他们, 唯有看看书籍, 抄抄代码, 这可能让我更加平静一些, 同时也透露我现阶段的一种无奈和疑惑感. 所以还是得给自己一点高级的鸡汤, 还好平时读书多, 储备了很多哦. 道德经第二十章恰好:

" 众人熙熙,如享太牢,如春登台。
我独泊兮,其未兆,如婴儿之未孩;傫傫兮,若无所归!
众人皆有余,而我独若遗。
我愚人之心也哉!
沌沌兮!
俗人昭昭,我独昏昏;俗人察察,我独闷闷。
澹兮,其若海,飂兮,若无止。
众人皆有以,而我独顽似鄙。
我独异于人,而贵食母。

希望, 终有一天, 我贵食母吧.

不扯远了, 继续将 后面的一些 sql 常用的 一波给带走 ~

表关系

需求 01

查询 选修了全部课程的学生信息

分析

先算出, 总共有多少门课, 然后, group by + having 即可, 一波带走.

+--------+-----------+
| 学号 | 姓名 |
+--------+-----------+
| 0001 | 王二 |
| 0003 | 胡小适 |
+--------+-----------+
2 rows in set (0.00 sec)

这种就非常简单的一个子查询而已, 练练手还行的.

需求 02

查询 各学生的年龄

分析

涉及时间计算, 这里用 DATEDIFF ( ) 返回两个日期的相差天数, (前 - 后, 字符时间)

我的 sql 另外一点风格, 关键词我都用 小写, 函数全部用 大写

mysql> select datediff("2020/5/3", "2020-5-10");
+-----------------------------------+
| datediff("2020/5/3", "2020-5-10") |
+-----------------------------------+
| -7 |
+-----------------------------------+
-- 获取当前日期

mysql> select CURDATE();

+------------+
| CURDATE() |
+------------+
| 2020-05-03 |
+------------+
1 row in set (0.00 sec)

然后, 直接从 学生表中, 用 (当前日期 - 出生日期) / 365 就得到年龄了呀.

select
s_id as 学号,
s_name as 姓名,
gender as 性别,

DATEDIFF(CURDATE(), birth_date) / 365 as '年龄(岁)'
from student
+--------+-----------+--------+-------------+
| 学号 | 姓名 | 性别 | 年龄(岁) |
+--------+-----------+--------+-------------+
| 0001 | 王二 | 男 | 31.3562 |
| 0002 | 星落 | 女 | 29.3863 |
| 0003 | 胡小适 | 男 | 28.3863 |
| 0004 | 油哥 | 男 | 23.6027 |
+--------+-----------+--------+-------------+
4 rows in set (0.00 sec)

还是要取整的哦, 对于年龄来说, 在 mysql 中通常有 3 个函数来做这个事情.

  • ROUND(X,D) -- 表示将值 X四舍五入为小数点后 D 位的数值
  • FLOOR(X)表示向下取整,只返回值X的整数部分,小数部分舍弃
  • CEILING(X) 表示向上取整,只返回值X的整数部分,小数部分舍弃

显然, 这里是需要 floor 呀. 于是改为:

select
s_id as 学号,
s_name as 姓名,
gender as 性别,

ABS(FLOOR(DATEDIFF(CURDATE(), birth_date) / 365)) as '年龄(岁)'
from student
+--------+-----------+--------+-------------+
| 学号 | 姓名 | 性别 | 年龄(岁) |
+--------+-----------+--------+-------------+
| 0001 | 王二 | 男 | 31 |
| 0002 | 星落 | 女 | 29 |
| 0003 | 胡小适 | 男 | 28 |
| 0004 | 油哥 | 男 | 23 |
+--------+-----------+--------+-------------+
4 rows in set (0.00 sec)

需求 03

查询 没有学过 "欧拉" 老师讲授的任意一门课程的学生姓名

分析

从反面来查询, 先查出, 学过欧拉老师的课程的那些 s_id, 然后再排除掉它们即可呀.

select t_id from teacher
where t_name = '欧拉'
+------+
| t_id |
+------+
| 0001 |
+------+
1 row in set (0.00 sec)

然后再看 t_id = 0001 的是哪门课, 从课表中. (数据集是, 一个老师教一门课的设定)

select c_id from course
where t_id = (
select t_id from teacher
where t_name = '欧拉'
)
+------+
| c_id |
+------+
| 0002 |
+------+
1 row in set (0.00 sec)

然后再看 c_id = 0002 的有哪些兄弟 s_id

select distinct s_id from score

where c_id = (
select c_id from course
where t_id = (
select t_id from teacher
where t_name = '欧拉'
)
)
+------+
| s_id |
+------+
| 0001 |
| 0002 |
| 0003 |
+------+
3 rows in set (0.00 sec)

可以, 这些兄弟果然都是学霸, 除了4号老铁油哥 . 最后从 student 中过滤即可.

select 
s_id,
s_name,
birth_date,
gender
from student
where s_id not in (

select distinct s_id from score

where c_id = (
select c_id from course
where t_id = (
select t_id from teacher
where t_name = '欧拉'
)
)
)
+------+--------+------------+--------+
| s_id | s_name | birth_date | gender |
+------+--------+------------+--------+
| 0004 | 油哥 | 1996-10-01 | 男 |
+------+--------+------------+--------+

演示, 另外一种错误思路, 就是先将 4张表都给拼接起来, 一并过滤即可. 工作中一般是不允许写 * 当然这里练习就无所谓了.这样一张宽表, 然后再同意过滤即可.

select
a.s_id,
a.s_name,
a.gender,
a.birth_date
from student as a

inner join score as b
on a.s_id = b.s_id

inner join course as c
on b.c_id = c.c_id

inner join teacher as d
on c.t_id = d.t_id

where d.t_name != "欧拉"

这明显就是错的, 也是记录,我第一次写那会儿, 没有经验, 逻辑一不小心就错了. 发现很多问题, 还是要先想清楚了再动手. 同时, 子查询和 join 还是要配合.

小结

  • SQL 常用函数, 如 FLOOR, ROUND, CEILING; CURDATE, DATEDIFF ... 等函数的应用 (后面总结一波)
  • 子查询 和 Join 要配合使用, 原以为 Join 就能搞定大部分, 现在开始喜欢子查询和写套娃了
  • SQL 尽可能写为自己的风格, 简单, 优雅的那种.

耐心和恒心, 总会获得回报的.