MySQL高级查询
本文将简述MySQL高级查询基本语法,如 “常用日期函数”、“外连接查询”、“交叉连接”、“自查询”、“行列转换”。
常用日期函数
MySQL中的常用日期函数如下:
函数 | 描述 |
---|---|
NOW() | 返回当前的日期和时间 |
YEAR(d) | 返回年份 |
QUARTER(d) | 返回日期d是第几季节,返回 1 到 4 |
MONTH(d) | 返回日期d中的月份值,1 到 12 |
WEEK(d) | 计算日期 d 是本年的第几个星期,范围是 0 到 53 |
DAYOFMONTH(d) | 计算日期 d 是本月的第几天 |
DAY(d) | 返回日期值 d 的日期部分 |
HOUR(t) | 返回 t 中的小时值 |
MINUTE(t) | 返回 t 中的分钟值 |
SECOND(t) | 返回 t 中的秒钟值 |
CURDATE() | 返回当前的日期 |
CURTIME() | 返回当前的时间 |
DATE() | 提取日期或日期/时间表达式的日期部分 |
EXTRACT() | 返回日期/时间按的单独部分 |
DATE_ADD() | 给日期添加指定的时间间隔 |
DATE_SUB() | 从日期减去指定的时间间隔 |
DATEDIFF() | 返回两个日期之间的天数 |
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) | 返回 datetime_expr2 − datetime_expr1 的时间差 |
DATE_FORMAT() | 用不同的格式显示日期/时间 |
外连接查询
外连接即合并具有同一列的两个以上的表的行,结果集中除了包含一个表与另一个表匹配的行之外,还查询到了左表或右表中不匹配的行。外连接又分为左外连接、右外连接、全连接。
●左外连接:两个表在连接过程中除了返回满足连接条件的行以外,还返回左表中不满足条件的行。
左外连接语法如下:
1 | select * |
●右外连接:两个表在连接过程中除了返回满足连接条件的行以外,还返回右表中不满足条件的行。
右外连接语法如下:
1 | select * |
●全连接:两个表在连接过程中除了返回满足连接条件的行以外,还返回左表和右表中不满足条件的行。(MySQL暂不支持这种语句,不过可使用union将两个结果集“堆一起”,利用左连接,右连接分两次将数据取出,然后用union将数据合并去重。)
列如:
1 | select * |
例:查询所有的班级及学生信息,没有学生的班级也显示。
1 | -- 左外连接 |
交叉连接
交叉连接即返回两个表的笛卡尔乘积,作用就是计算两个表之间每个可能的组合,结果集中的记录数等于两张表各自记录数的乘积。一般使用CROSS JOIN
连接两张表进行交叉连接。
语法如下:
1 | SELECT * |
例:假设每个学生都学习每门必修课,显示学生和课程名。
1 | SELECT s.stuName,c.cname |
例:用笛卡儿积查询所有学生姓名、课程名称及分数,将没有成绩的分数显示为0分。
1 | SELECT s.stuName,c.cname,IFNULL(g.score,0) |
自查询
某些情况下需要对一张表内的数据进行对比,获得各列层次关系,而通过一般的SQL写法,可能需要通过写多个子查询的方式才能解决,但用自连接查询可以轻松解决。自连接查询是指使用表的别名实现表与其自身连接的查询方法。
例:在学生表中查询李得胜同学的同乡有哪些人。
1 | SELECT s2.stuName |
行列转换
实际应用中可能需要把表的某些行转换成列,或者把列转换成行的情况。如下图例:
例:查询学生姓名课程和成绩并行列转换,使用case ... when ... then ... else ... end
方法解如下:
1 | SELECT name,SUM(CASE WHEN course='软件工程' THEN score ELSE 0 END) AS "软件工程", |
版权声明
Scholar’s Blog by scholargeek is licensed under a Creative Commons BY-NC-ND 4.0 International License.
由董仕麟创作并维护的scholargeek博客采用创作共用保留署名-非商业-禁止演绎4.0国际许可证。
本文首发于Scholar’s Blog博客,版权所有,侵权必究。