作业

-- 所有信息
select * from stu_54;
select * from exam;
SELECT stu_54.code,name,subject,score from stu_54,exam
WHERE stu_54.code = exam.code;
select stu_54.code, name, subject,score from stu_54 
INNER JOIN exam
On stu_54.code = exam.code;
-- 张三的数学
SELECT stu_54.code,name,subject,score from stu_54,exam
WHERE stu_54.code = exam.code
AND name = "张三"
AND subject = "数学";
--
select stu_54.code, name, subject,score from stu_54 
INNER JOIN exam
On stu_54.code = exam.code
AND name = "张三"
AND subject = "数学";
-- 查看英语成绩
SELECT stu_54.code,name,subject,score from stu_54,exam
WHERE stu_54.code = exam.code
AND subject = "英语";
select stu_54.code, name, subject,score from stu_54 
INNER JOIN exam
On stu_54.code = exam.code
AND subject = "英语";
-- 各科的平均成绩
SELECT subject, avg(score) from exam
GROUP BY subject;
-- 总成绩
SELECT stu_54.code,name, sum(score) from stu_54,exam
WHERE stu_54.code = exam.code
GROUP BY stu_54.code
ORDER BY sum(score) DESC;
--
select stu_54.code,name,sum(score) from stu_54
INNER JOIN exam
ON stu_54.code = exam.code 
GROUP BY stu_54.code
ORDER BY sum(score) desc;

外连接

• left join: 左连接， 返回包括左表中的所有记录和右表中连接字段的相等的记录，如果右边的表中没有对应的数据，结果显示为 Null；
• right join： 右连接， 返回包括右表中的所有记录和左表中连接字段的相等的记录，如果左边的表中没有对应的数据，结果显示为 Null；

在公司中 95%的程序员在进行外连接的时候，喜欢使用左连接进行查询。

现在有两个表
学生表和签到表

-- 查询classmates表中 所有学员的签到情况，学号，学员姓名，签到时间，如果没有签到，则签到时间显示为空
select classmates.nu,name,checktime from classmates
left join checkout on classmates.nu = checkout.nu;

-- 查询 每个同学的签到次数， 如果没有签到，显示0
select classmates.nu,name,count(checktime) 签到次数 from classmates 
LEFT join checkout on classmates.nu = checkout.nu
group by classmates.nu,name
order by classmates.nu; -- 根据学号进行排序

-- 查询 每个同学的签到次数， 如果没有签到，显示0
select classmates.nu,name,count(checktime) 签到次数 from classmates 
LEFT join checkout on classmates.nu = checkout.nu
group by classmates.nu,name
order by classmates.nu; -- 根据学号进行排序

-- 统计出考勤最多的次数是多少次？
select count(checktime) 签到次数 from classmates 
LEFT join checkout on classmates.nu = checkout.nu
group by classmates.nu,name
order by count(checktime) desc  -- 根据签到次数进行排序
limit 1;

-- 统计考勤次数排名第二的次数是多少次？
select distinct(count(checktime)) 签到次数 from classmates 
LEFT join checkout on classmates.nu = checkout.nu
group by classmates.nu,name
order by count(checktime) desc 
LIMIT 1,1;

-- 签到次数第二高 人员

select classmates.nu,name,count(checktime) 签到次数 from classmates 
LEFT join checkout on classmates.nu = checkout.nu
group by classmates.nu,name
HAVING count(checktime)=(
    select distinct(count(checktime)) 签到次数 from classmates 
    LEFT join checkout on classmates.nu = checkout.nu
    group by classmates.nu,name
    order by count(checktime) desc
    LIMIT 1,1
);

全连接

(select * from classmates
LEFT JOIN checkout
on classmates.nu = checkout.nu)
UNION
(select * from classmates
RIGHT JOIN checkout
on classmates.nu = checkout.nu)

两个表连在一起

三表联查

-- 查询所有订单的信息， 订单id，订单所属顾客id，顾客姓名，商品名称，商品单价，下单时间

SELECT * from customers61;
SELECT * from products61;
SELECT * from orders61;


-- where 内连接
SELECT * from orders61,products61,customers61
WHERE orders61.c_id = customers61.c_id 
AND orders61.p_id = products61.p_id;


SELECT * from orders61
inner JOIN customers61
ON orders61.c_id = customers61.c_id
INNER JOIN products61
ON orders61.p_id = products61.p_id;

-- 查有成绩的学生信息   (学生信息，student) （成绩，score） ==》 关系是s_id 内连接
  select student.* from student,score
    where student.s_id = score.s_id;

-- 查询「李」姓老师的数量

SELECT count(t_name) from teacher
WHERE t_name like "李%";

-- 查询名字中含有「风」字的学生信息
select * from student
WHERE s_name like "%风%";

-- 查询 1990 年出生的学生名单
SELECT * from student
WHERE s_birth like "1990%";

-- 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
-- 1. 查看张三老师教的课程id
SELECT t_id from teacher
WHERE t_name = "张三";  -- t_id   01
-- 2. 查看张三所教授的课程
select c_id from course  
WHERE t_id = "01";     -- 数学， 课程id c_id = 02

-- 3. 查看选修课为数学的学员信息  学生表，成绩表

select max(s_score) from score
WHERE c_id = "02";  -- 找到数学最高分  90

-- 4. 查找数学90分的学号
SELECT * from score
WHERE s_score = 90; -- 01

-- 4. 根据学号 查找学生
select S1.s_id,S1.s_name,S1.s_birth,S1.s_sex,score.c_id,score.s_score from 
(select * from student
WHERE s_id in (
    SELECT s_id from score
    WHERE s_score = 90
)) as S1

INNER JOIN score 
on S1.s_id = score.s_id;
AND score.c_id = "02"

总结