mysql基础操作4

日期操作相关函数

NOW() 获取当前日期和时间（日期+时间）

YEAR() 获取日期中的年份

MONTH() 获取日期中的月份

DAY() 获取日期中的天

HOUR() 获取日期中的小时

MINUTE() 获取日期中的分钟

SECOND() 获取日期中的秒

WEEK() 获取日期属于当月的第几个星期

QUARTER() 获取日期属于第几个季度

DAYOFMONTH() 获取日期属于当月的第几天

ADDDATE(d,expr) 返回日期参数d加上一段时间后的日期

SUBDATE(d,expr) 返回日期参数d减去一段时间后的日期

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) 返回两个日期的整数差

SELECT NOW()

SELECT YEAR(NOW())

SELECT TIMESTAMPDIFF(YEAR,'2021-01-01',NOW())

查询st1104班的2011年出生的学生姓名、课程名称、分数

SELECT s.stuName,co.cName,g.score

FROM classes c,student s,course co,grade g

WHERE c.bjid=s.bjid AND s.stuid=g.stuid AND co.cid=g.cid

AND c.bjname='st1104' AND YEAR(stuBirthday)=2011

查询工龄在3年以上的员工姓名和部门名称

SELECT e.ename,d.deptName

FROM dept d,emp e

WHERE d.did=e.did AND TIMESTAMPDIFF(YEAR,birthday,NOW())>=3

内连接查询操作列出与连接条件匹配的数据行（仅主外键有对应关系的）

左外连接符合内连接特点并且左边的表全部显示

查询所有的班级及学生信息，没有学生的班级也显示 左外连接实现

SELECT *

FROM classes c LEFT JOIN student s

ON c.bjid=s.bjid

查询所有的班级的学生总人数，显示班级名称和总人数

SELECT c.bjname,count(s.bjid)

FROM classes c LEFT JOIN student s

ON c.bjid=s.bjid

GROUP BY c.bjname

假设每个学生都学习了每门课，显示学生姓名和课程名

SELECT s.stuname,c.cname,g.score

FROM student s CROSS JOIN course c LEFT JOIN grade g

ON s.stuid=g.stuid AND c.cid=g.cid

交叉联接返回左表中的所有行，左表中的每一行与右表中所有行关联

假设每个学生都选择每门课，查询没有选择课程的学生姓名和课程名称

SELECT s.stuname,c.cname,g.score

FROM student s CROSS JOIN course c LEFT JOIN grade g

ON s.stuid=g.stuid AND c.cid=g.cid

WHERE g.score IS NULL

查询st1104班的学生信息 （要求使用子查询完成）

SELECT *

FROM student

WHERE bjid=(SELECT bjid

FROM classes

WHERE bjname='st1104')

查询学生王甘的老乡的信息 自查询：一张表赋予两种不同的身份

SELECT s2.*

FROM student s1,student s2

WHERE s1.stuAddress=s2.stuAddress

AND s1.stuname='王甘' AND s2.stuname<>'王甘'

查询所有的学生姓名、课程名称及分数， 并进行 行列转换（面试题）

SELECT s.stuname,

sum(CASE WHEN c.cname='HTML' THEN g.score ELSE 0 END)'HTML',

sum(CASE WHEN c.cname='JAVA' THEN g.score ELSE 0 END)'JAVA',

sum(CASE WHEN c.cname='MySQL' THEN g.score ELSE 0 END)'MySQL',

sum(CASE WHEN c.cname='SpringBoot' THEN g.score ELSE 0 END)'SpringBoot'

FROM student s,course c,grade g

WHERE s.stuid=g.stuid AND c.cid=g.cid

GROUP BY s.stuname

中间结果集的思想

查询所有SpringBoot成绩比其所在班级平均成绩高的学生信息

SELECT s1.*

FROM student s1,grade g1,course c1,(SELECT c.bjid,avg(g.score)pjf

FROM classes c,student s,grade g

WHERE c.bjid=s.bjid AND s.stuid=g.stuid

GROUP BY c.bjid)temp

WHERE s1.stuid=g1.stuid AND c1.cid=g1.cid AND s1.bjid=temp.bjid AND

c1.cname='SpringBoot' AND g1.score>temp.pjf