做网站培训,青海省住房和城乡建设厅的官方网站,网页制作与网站建设答案,深圳网站制作公司MySQL50-4-第6-10题本文中介绍的是第6-10题#xff0c;涉及到的主要知识点#xff1a;模糊匹配和通配符使用表的自连接in/not in连接查询的条件筛选image题目6题目需求查询“李”姓老师的数量分析过程使用通配符和like来解决SQL实现select count(t_name) from Teacher where …MySQL50-4-第6-10题本文中介绍的是第6-10题涉及到的主要知识点模糊匹配和通配符使用表的自连接in/not in连接查询的条件筛选image题目6题目需求查询“李”姓老师的数量分析过程使用通配符和like来解决SQL实现select count(t_name) from Teacher where t_name like 李%; -- 通配符image这题怕是最简单的吧题目7image题目需求查询学过张三老师授课的同学的信息分析过程张三老师Course---t_name课程c_id------Score.c_id-------Student.*SQL实现-- 方法1通过张三老师的课程的学生来查找自己的方法select * -- 3. 通过学号找出全部学生信息from Studentwhere s_id in (select s_id -- 2.通过课程找出对应的学号from Score Sjoin Course Con S.c_id C.c_id -- 课程表和成绩表where C.t_id(select t_id from Teacher where t_name张三) -- 1.查询张三老师的课程);-- 方法2通过张三老师的课程来查询select s1.*from Student s1join Score s2on s1.s_ids2.s_idwhere s2.c_id in (select c_id from Course c where t_id( -- 1. 通过老师找出其对应的课程select t_id from Teacher t where t_name张三))-- 方法3select s.* from Teacher tleft join Course c on t.t_idc.t_id -- 教师表和课程表left join Score sc on c.c_idsc.c_id -- 课程表和成绩表left join Student s on s.s_idsc.s_id -- 成绩表和学生信息表where t.t_name张三;自己的方法image方法2来实现image方法3实现image题目8题目需求找出没有学过张三老师课程的学生分析过程和上面的题目是互补的考虑取反操作SQL实现select * -- 3. 通过学号找出全部学生信息from Studentwhere s_id not in ( -- 2.通过学号取反学号不在张三老师授课的学生的学号中select s_idfrom Score Sjoin Course Con S.c_id C.c_idwhere C.t_id(select t_id from Teacher where t_name 张三) -- 1.查询张三老师的课程);-- 方法2select *from Student s1where s1.s_id not in (select s2.s_id from Student s2 join Score s3 on s2.s_ids3.s_id where s3.c_id in(select c.c_id from Course c join Teacher t on c.t_idt.t_id where t_name张三));-- 方法3select s1.*from Student s1join Score s2on s1.s_ids2.s_idwhere s2.c_id not in (select c_id from Course c where t_id( -- 1. 通过老师找出其对应的课程select t_id from Teacher t where t_name张三));image方法2image题目9image题目需求查询学过编号为01并且学过编号为02课程的学生信息分析过程课程编号Score——c_id(课程编号)学生信息Student——*(学生信息)SQL实现-- 自己的方法通过自连接实现select s1.*from Student s1where s_id in (select s2.s_id from Score s2join Score s3on s2.s_ids3.s_idwhere s2.c_id01 and s3.c_id02);-- 方法2直接通过where语句实现select s1.*from Student s1, Score s2, Score s3where s1.s_ids2.s_idand s1.s_ids3.s_idand s2.c_id01 and s3.c_id02;-- 方法3两个子查询-- 1. 先查出学号select sc1.s_idfrom (select * from Score s1 where s1.c_id01) sc1,(select * from Score s1 where s1.c_id02) sc2where sc1.s_idsc2.s_id;-- 2.找出学生信息select *from Studentwhere s_id in (select sc1.s_id -- 指定学号是符合要求的from (select * from Score s1 where s1.c_id01) sc1,(select * from Score s1 where s1.c_id02) sc2where sc1.s_idsc2.s_id);先从Score表中看看哪些人是满足要求的01-05同学是满足的image通过自连接查询的语句如下image查询出学号后再匹配出学生信息image通过where语句实现image方法3的实现image题目10题目需求查询学过01课程但是没有学过02课程的学生信息(注意和上面题目的区别)分析过程使用的表和字段是相同的课程编号Score——c_id(课程编号)学生信息Student——*(学生信息)SQL实现首先看看哪些同学是满足要求的只有06号同学是满足的image错误思路1直接将上面一题的结果全部排出导致那些没有学过01课程的学生也出现了0708select s1.*from Student s1where s_id not in ( -- 直接将上面一题的结果全部排出导致那些没有学过01课程的学生也出现了0708select s2.s_id from Score s2join Score s3on s2.s_ids3.s_idwhere s2.c_id01 and s3.c_id 02);image错误思路2将上面题目中的02课程直接取反导致同时修过010203或者只修0103的同学也会出现select s1.*from Student s1where s_id in (select s2.s_id from Score s2join Score s3on s2.s_ids3.s_idwhere s2.c_id01 and s3.c_id !02 -- 直接取反是不行的因为修改(01,02,03)的同学也会出现);image正确思路-- 方法1根据两种修课情况来判断select s1.*from Student s1where s1.s_id in (select s_id from Score where c_id01) -- 修过01课程要保留and s1.s_id not in (select s_id from Score where c_id02); -- 哪些人修过02需要排除image方法2先把06号学生找出来select * from Student where s_id in (select s_idfrom Scorewhere c_id01 -- 修过01课程的学号and s_id not in (select s_id -- 同时学号不能在修过02课程中出现from Scorewhere c_id02));select s_idfrom Scorewhere c_id01 -- 修过01课程的学号and s_id not in (select s_id -- 同时学号不能在修过02课程中出现from Scorewhere c_id02)imageimage如何找出06号学生如何Score中找出06号学生imageimage
