三表联查

-- 查询 学生姓名，学号，考试的科目，对应成绩  inner join
SELECT sname, student.sno, cname,score from student
INNER JOIN scores
ON student.sno = scores.sno
INNER JOIN course
ON scores.cno = course.cno;

SELECT sname, student.sno, cname,score from student,scores,course
WHERE student.sno = scores.sno AND course.cno = scores.cno
ORDER BY scores.score;

思考：

左联结查询 left join

以表A为主，表B没有表A相关的数据显示为null，如果有，那就直接显示。

姓名	学生id
张三	01
李四	02

学生id	考勤时间
01	2021年7月5日

今日考勤

学生id	学生姓名	考勤时间
01	张三	2021年7月5日
02	李四

SELECT sname, student.sno, checktime from student
LEFT JOIN checkin     -- student 为主表
ON checkin.sno = student.sno;

左联结的时候有以左表为主，左表所有的字段都会显示，第二表中对应的数据如果没有，显示为Null。


-- 查询 学生姓名，学号，考试的科目，对应成绩 
-- 如果成绩表没有这个学生的对应考试成绩，则显示为 Null
select sname,student.sno,cname,score from student
left join scores
on student.sno = scores.sno
left join course
on scores.cno = course.cno

-- 查询 所有顾客的订单信息 顾客名字，顾客id，订单号， 订单时间， 如果顾客没有订单，订单信息显示为null

select cust_name, customers.cust_id, order_num,order_date from customers
LEFT JOIN orders
ON orders.cust_id = customers.cust_id;

练习

-- 查询 订单详情表中 所有订单（订单为主）的详细信息
-- 订单号，购买商品的名字，商品价格

SELECT order_num, prod_name, prod_price from orderitems
LEFT JOIN products
ON products.prod_id = orderitems.prod_id
ORDER BY order_num;

select sname,classname from classes
LEFT JOIN student
ON
classes.classid = student.classid;

右联结 right join

右联结与左联结相反，以右边的表为主。其他和左联结效果都一样。

select sname,classname from classes
RIGHT JOIN student
ON
classes.classid = student.classid;

作业：

左联结和右连接的区别？
inner join 和 left join 的区别？

union 合并多条sql

查询语文成绩及格或数学成绩及格，输出学生姓名，学生班级名称，科目，成绩

SELECT sname,classname,cname,score from student
INNER JOIN classes
ON classes.classid = student.classid
INNER JOIN scores
ON scores.sno = student.sno
INNER JOIN course
ON course.cno = scores.cno
WHERE scores.score >= 60 AND course.cname = "语文"

UNION

SELECT sname,classname,cname,score from student
INNER JOIN classes
ON classes.classid = student.classid
INNER JOIN scores
ON scores.sno = student.sno
INNER JOIN course
ON course.cno = scores.cno
WHERE scores.score >= 60 AND course.cname = "数学";

union 可以将两条sql语句查询出来的结果合并显示出来。

常用的函数

当前日期 CurDate()

curdate() 会返回当前的日期

-- 查询今天的考勤数据
select * from checkin
WHERE checktime = CURDATE();  -- CURDATE() 表示当前日期

DateDiff(date1,date2) 计算日期之差

SELECT DATEDIFF("2021-07-05","2021-07-06")

-- 查询7天以前(包含7天)的考勤数据   今天为 2021-07-05日

SELECT * FROM checkin
WHERE datediff(curdate(),checktime)  >= 7;   -- 1. 先计算出 curdate()  今天
                                                                                        -- 2.  checktime 表中的具体数据 比如2021-07-05
                                                                                         -- 3 datediff(今天，表中的数据)  ==》今天-表中的数据 n天
                                                                                         -- 4 n >= 7;  一周之前的数据

其他日期相关函数

SELECT Day(birthday) from student;  -- 日期中的天

SELECT MONTH(birthday) from student; -- 日期中的月

SELECT YEAR(birthday) from student;   -- 日期中的年

-- 查询出年龄大于18周岁的学生姓名，以及学生年龄，生日 (今天日期中的年份（比如今天2021-07-05，年份为2021）-生日日期（1976-09-01）中的年份（1976）=年龄（2021-1976=45）)

select sname, birthday, YEAR(CURDATE())-YEAR(birthday)  from student -- YEAR(CURDATE())-YEAR(birthday) 计算年龄
WHERE YEAR(CURDATE())-YEAR(birthday) > 18
ORDER BY birthday     -- 按照年龄大小排序

concat() 字段拼接

可以将字符串拼接在一起

# 学号_学生姓名 拼接在一起

SELECT CONCAT(sno,"_",sname) FROM student;

作业

— 作业— 多表查询

— 1. 查询student,classes 表中共有的数据，输出学号，姓名，性别，班级名称

SELECT sno,sname,sex,classname FROM student
INNER JOIN classes
on classes.classid=student.classid

— 2. 查询student，classes 表，输出班级名称，对应班级的人数

SELECT classname,COUNT(sname) from student
INNER JOIN classes
on classes.classid=student.classid
GROUP BY classname

— 3. 查询出语文分数最高的同学姓名，科目，班级名称。

-- 找最高分
SELECT MAX(scores.score) from scores
INNER JOIN course
on scores.cno=course.cno
where cname="语文"


-- 根据分数招人， 有可能 有多人同时分数一样
SELECT sname,cname,classname FROM student
INNER JOIN scores
on scores.sno=student.sno
INNER JOIN course
on course.cno=scores.cno
INNER JOIN classes
on classes.classid=student.classid
where scores.score=(
  SELECT MAX(scores.score) from scores
    INNER JOIN course
    on scores.cno=course.cno
    where cname="语文") 
and course.cname="语文";

— 4. 查询同学姓名，考试总分，平均成绩，并按总分排序。

SELECT sname,SUM(score),avg(score) FROM student
left JOIN scores
ON student.sno = scores.sno
GROUP BY sname
ORDER BY SUM(score) DESC

— 5. 查询出每个同学的考试信息，输出姓名，班级，科目，成绩，（以姓名为主，没有数据显示null）

select sname,classname,cname,score from student 
left join classes
on classes.classid=student.classid
left join scores
 on scores.sno=student.sno
left join course
 on course.cno=scores.cno;

— 时间函数相关
— 1. 统计checkin 表中 3天前的所有考勤数据

SELECT * from checkin 
WHERE datediff(curdate(),checktime)>=3   -- 今天的日期 - 时间 >=3

— 2. 统计 checkin， student表中， 3天前的考勤信息，输出学生姓名，考勤次数

SELECT sname,count(sname)from checkin
inner JOIN student
on checkin.sno=student.sno 
-- 添加条件
WHERE DATEDIFF(curdate(),checktime) >= 3 
GROUP BY sname;

— 3. 统计 55期涛飞班级中所有年龄在 18-30岁之间的学生信息

SELECT sname,classname,(year(CURDATE())-year(birthday)) as age from student 
INNER JOIN classes 
on student.classid=classes.classid 
WHERE classname="涛飞55期" 
and 
year(CURDATE())-year(birthday) BETWEEN 18 and 30

Linux，Mysql，接口测试

Mysql-04