Mysql之增删改查案例【第三篇】

一、表结构要求：

        -- 1.学生表-t_student  (sid 学生编号,sname 学生姓名,sage 学生年龄,ssex 学生性别)

        -- 2.教师表-t_teacher  (tid 教师编号,tname 教师名称)

        -- 3.课程表-t_course   (cid 课程编号,cname 课程名称,tid 教师名称)

        -- 4.成绩表-t_score     (sid 学生编号,cid 课程编号,score 成绩)

二、题目：

        01）查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

分析(需要用到子查询）：

        1、t_score/t_student

        2、先找出学过课程1和课程2的学生
        3、课程01的成绩>课程02的成绩，是否是同一个人？(t1.sid=t2.sid限定 就是同一个人)

        t1：别名        （以 t1&t2 为临时数据源）

(select sid,score as s1 from t_score where cid='01') t1    ==》找出课程1的学生id和成绩 

select s.*,temp.s1,temp.s2 from(
select t1.sid,t1.s1,t2.s2 from
(select sid,score as s1 from t_score where cid='01') t1,
(select sid,score as s2 from t_score where cid='02') t2
where t1.sid=t2.sid and t1.s1>t2.s2) temp,t_student1 s
where temp.sid=s.sid

---

        02）查询同时存在" 01 "课程和" 02 "课程的情况

分析：
        1.首先查找学过课程01和02的所有学生
        select * from t_score where cid='01';

        t1.sid=t2.sid       ==> 代表既学过课程1又学过课程2的学生

select * from
(select sid,score as s1 from t_score where cid='01') t1,
(select sid,score as s2 from t_score where cid='02') t2
where t1.sid=t2.sid; 

---

        03）查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null)

分析：1、连接查询（外连接）

           2、首先查找学过课程01和02的所有学生

           3、存在" 01 "课程但可能不存在" 02 "课程，故 t1 为主表
        1）内连接：inner join 只有连接条件完全成立才会显示查询结果
        2）外连接：左外连接（left join）和右外连接（right join）
        特点：连接条件完全成立则显示结果；连接条件不成立，主表记录永远保存，从表与null值关联

select * from
(select sid,score from t_score where cid='01') t1 left join
(select sid,score from t_score where cid='02') t2 on t1.sid=t2.sid; 

---

        04）查询不存在" 01 "课程但存在" 02 "课程的情况

分析：
        1、首先查找所有学过课程01的学生编号，在取相反值，not in       

        2、再到成绩表中使用 not in 排除学过01课程得学生，最后拼接条件 cid='02'

select * from t_score where sid not in
(select sid from t_score where cid='01') and cid='02';

---

        05）查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

分析：聚合函数
        四舍五入 round(5,2)总共5位数，取2位小数
        #1、t_score/t_student
        #2、平均成绩avg>=60
        #3、分组 group by || 过滤 having avg(score)>=60

select s.sid,s.sname,round(avg(sc.score),2) 
from t_student s,t_score sc 
where s.sid=sc.sid 
group by s.sid,s.sname
having avg(sc.score)>=60;

---

        06）查询在t_score表存在成绩的学生信息

分析：判断成绩表中是否存在有成绩的学生编号（内连接）

          1、有可能一个学生参加了多场考试，故要去重
          distinct：去重

select distinct s.* from t_score sc,t_student s where sc.sid=s.sid;