2023-02-05

数据库学习3

文章目录

1. MySQL高级查询

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 2	select * from a_table a left join b_table b on a.a_id = b.b_id;

●右外连接：两个表在连接过程中除了返回满足连接条件的行以外，还返回右表中不满足条件的行。
右外连接语法如下：

1 2	select * from a_table a right join b_table b on a.a_id = b.b_id;

●全连接：两个表在连接过程中除了返回满足连接条件的行以外，还返回左表和右表中不满足条件的行。(MySQL暂不支持这种语句，不过可使用union将两个结果集“堆一起”，利用左连接，右连接分两次将数据取出，然后用union将数据合并去重。)
列如：

select * 
from a_table a left join b_table b on a.a_id = b.b_id;
union
select * 
from a_table a right join b_table b on a.a_id = b.b_id;

例：查询所有的班级及学生信息，没有学生的班级也显示。

-- 左外连接
SELECT c.bjname,s.stuName
FROM classes c LEFT JOIN student s ON c.bjid=s.bjid

-- 右外连接
SELECT c.bjname,s.stuName
FROM student s RIGHT JOIN classes c ON s.bjid=c.bjid

交叉连接

　　交叉连接即返回两个表的笛卡尔乘积，作用就是计算两个表之间每个可能的组合，结果集中的记录数等于两张表各自记录数的乘积。一般使用CROSS JOIN连接两张表进行交叉连接。
语法如下：

1 2	SELECT * FROM T1 CROSS JOIN T2

例：假设每个学生都学习每门必修课，显示学生和课程名。

1 2	SELECT s.stuName,c.cname FROM student s CROSS JOIN course c

例：用笛卡儿积查询所有学生姓名、课程名称及分数，将没有成绩的分数显示为0分。

1
2
3

SELECT s.stuName,c.cname,IFNULL(g.score,0)
FROM student s CROSS JOIN course c LEFT JOIN grade g
ON s.stuId=g.stuid AND c.cid=g.cid

自查询

　　某些情况下需要对一张表内的数据进行对比，获得各列层次关系，而通过一般的SQL写法，可能需要通过写多个子查询的方式才能解决，但用自连接查询可以轻松解决。自连接查询是指使用表的别名实现表与其自身连接的查询方法。

例：在学生表中查询李得胜同学的同乡有哪些人。

1
2
3

SELECT s2.stuName
FROM student s1,student s2
WHERE s1.stuAddress=s2.stuAddress AND s1.stuName='李得胜' AND s2.stuName<>'李得胜'

行列转换

　　实际应用中可能需要把表的某些行转换成列，或者把列转换成行的情况。如下图例：
SQL行列转换例子

例：查询学生姓名课程和成绩并行列转换，使用case ... when ... then ... else ... end方法解如下：

SELECT name,SUM(CASE WHEN course='软件工程' THEN score ELSE 0 END) AS "软件工程",
            SUM(CASE WHEN course='计算机技术' THEN score ELSE 0 END) AS "计算机技术",
            SUM(CASE WHEN course='物联网工程' THEN score ELSE 0 END) AS "物联网工程"
FROM student
GROUP BY name

Scholar’s Blog by scholargeek is licensed under a Creative Commons BY-NC-ND 4.0 International License.
由董仕麟创作并维护的scholargeek博客采用创作共用保留署名-非商业-禁止演绎4.0国际许可证。

本文永久链接：https://scholargeek.github.io/2023/02/05/MySQL3/

Scholar's Blog

DO WHAT WE WANT