也只是尽快搬完这快一个月 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 尽可能写为自己的风格, 简单, 优雅的那种.
耐心和恒心, 总会获得回报的.
发表评论