mysql笔试题大餐---2、exists加一些查询

一、总结

一句话总结：

实践：我之前的mysql真的学的太浅了，这种情况下，依据实践（做题）才是唯一能把它学好的方式

 

1、MySQL中EXISTS如何使用？

1、exists是和in相提并论的东西

2、返回值bool值：EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False

比如在Northwind数据库中有一个查询为SELECT c.CustomerId,CompanyName FROM Customers c WHERE EXISTS(SELECT OrderID FROM Orders o WHERE o.CustomerID=c.CustomerID) 这里面的EXISTS是如何运作呢？子查询返回的是OrderId字段，可是外面的查询要找的是CustomerID和CompanyName字段，这两个字段肯定不在OrderID里面啊，这是如何匹配的呢？ EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或FalseEXISTS 指定一个子查询，检测 行 的存在。语法： EXISTS subquery参数： subquery 是一个受限的 SELECT 语句 (不允许有 COMPUTE 子句和 INTO 关键字)。结果类型： Boolean 如果子查询包含行，则返回 TRUE ，否则返回 FLASE 。

 

 

2、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名？

注意exists：select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002');

EXISTS()查询是将主查询的结果集放到子查询中做验证，根据验证结果是true或false来决定主查询数据结果是否得以保存。

 

3、mysql中的增删改查？

用脑子：站在设计者的思想上怎么才能完成任务：INSERT INTO student(id,name,grade) VALUES(1,'zhangshan',98);

删：DELETE  FROM student WHERE id=7;

改：UPDATE student SET name=‘caocao’,grade=50 WHERE id=1;

 

4、查询所有同学的学号、姓名、选课数、总成绩？

多表查询推荐用链接查询方式：比如inner join

##多表查询推荐用链接查询方式：比如inner joinselect s.s_id,s.s_name,count(ss.ss_s_id) course_num,sum(ss.ss_score) from student s inner join student_score ss on s.s_id=ss.ss_s_id group by ss.ss_s_id;

 

 

5、查询姓“鲁”的老师的个数？

like中的百分号的用法 + count用法

select count(t.t_id) num from teacher t where t_name like '鲁%'

 

 

6、查询没学过“鲁迅”老师课的同学的学号、姓名？

没学过：总的减去学过了的

去掉重复：distinct函数

总的减去学过了的如何实现：通过嵌套查询+ not in

##找出鲁迅老师课的id，学生not in 里面就好##没学过：总的减去学过了的##去掉重复：distinct函数##总的减去学过了的如何实现：通过嵌套查询+ not inselect c.c_id from course c inner join teacher t on t.t_id=c.c_t_id where t_name='鲁迅'select distinct(c.c_id) from course c inner join teacher t on t.t_id=c.c_t_id where t_name='鲁迅'select distinct(s_id) from student s inner join student_score ss on s.s_id=ss.ss_s_id inner join course c on c.c_id=ss.ss_c_id inner join teacher t on t.t_id=c.c_t_id where c.c_id in (select distinct(c.c_id) from course c inner join teacher t on t.t_id=c.c_t_id where t_name='鲁迅')select s.s_id,s.s_name from student s where s.s_id not in (select distinct(s_id) from student s inner join student_score ss on s.s_id=ss.ss_s_id inner join course c on c.c_id=ss.ss_c_id inner join teacher t on t.t_id=c.c_t_id where c.c_id in (select distinct(c.c_id) from course c inner join teacher t on t.t_id=c.c_t_id where t_name='鲁迅'))

 

 

7、查询学过“鲁迅作品集”并且也学过“狂人日记”课程的同学的学号、姓名？

思路：把学过“鲁迅作品集”的人的id找出来，把学过“狂人日记”的人的id找出来，做对比找出同时学过的人的id

特别注意：from的表示一个嵌套查询的表：from (select s.s_id from student s inner join student_score ss on s.s_id=ss.ss_s_id inner join course c on c.c_id=ss.ss_c_id where c.c_name='鲁迅作品集')  a

##思路：把学过“鲁迅作品集”的人的id找出来，把学过“狂人日记”的人的id找出来，做对比找出同时学过的人的id##特别注意：from的表示一个嵌套查询的表：from (select s.s_id from student s inner join student_score ss on s.s_id=ss.ss_s_id inner join course c on c.c_id=ss.ss_c_id where c.c_name='鲁迅作品集')  aselect * from student s inner join student_score ss on s.s_id=ss.ss_s_id inner join course c on c.c_id=ss.ss_c_id inner join teacher t on t.t_id=c.c_t_id#把学过“鲁迅作品集”的人的id找出来select s.s_id from student s inner join student_score ss on s.s_id=ss.ss_s_id inner join course c on c.c_id=ss.ss_c_id where c.c_name='鲁迅作品集'#把学过“狂人日记”的人的id找出来select s.s_id from student s inner join student_score ss on s.s_id=ss.ss_s_id inner join course c on c.c_id=ss.ss_c_id where c.c_name='狂人日记'#做对比select a.s_id from (select s.s_id from student s inner join student_score ss on s.s_id=ss.ss_s_id inner join course c on c.c_id=ss.ss_c_id where c.c_name='鲁迅作品集')  a,(select s.s_id from student s inner join student_score ss on s.s_id=ss.ss_s_id inner join course c on c.c_id=ss.ss_c_id where c.c_name='狂人日记') b where a.s_id=b.s_id#找学生信息select * from student where s_id in (select a.s_id from (select s.s_id from student s inner join student_score ss on s.s_id=ss.ss_s_id inner join course c on c.c_id=ss.ss_c_id where c.c_name='鲁迅作品集')  a,(select s.s_id from student s inner join student_score ss on s.s_id=ss.ss_s_id inner join course c on c.c_id=ss.ss_c_id where c.c_name='狂人日记') b where a.s_id=b.s_id)

 

 

 

二、内容在总结中

1、具体操作及截图

数据库数据的大致样子：

select * from student s inner join student_score ss on s.s_id=ss.ss_s_id inner join course c on c.c_id=ss.ss_c_id inner join teacher t on t.t_id=c.c_t_id

 

 

#3、查询所有同学的学号、姓名、选课数、总成绩；

#3、查询所有同学的学号、姓名、选课数、总成绩； ##多表查询推荐用链接查询方式：比如inner joinselect s.s_id,s.s_name,count(ss.ss_s_id) course_num,sum(ss.ss_score) from student s inner join student_score ss on s.s_id=ss.ss_s_id group by ss.ss_s_id;

 

#4、查询姓“鲁”的老师的个数；

select count(t.t_id) num from teacher t where t_name like '鲁%'

 

 

 

 #5、查询没学过“鲁迅”老师课的同学的学号、姓名；

##找出鲁迅老师课的id，学生not in 里面就好##没学过：总的减去学过了的##去掉重复：distinct函数##总的减去学过了的如何实现：通过嵌套查询+ not inselect c.c_id from course c inner join teacher t on t.t_id=c.c_t_id where t_name='鲁迅'select distinct(c.c_id) from course c inner join teacher t on t.t_id=c.c_t_id where t_name='鲁迅'select distinct(s_id) from student s inner join student_score ss on s.s_id=ss.ss_s_id inner join course c on c.c_id=ss.ss_c_id inner join teacher t on t.t_id=c.c_t_id where c.c_id in (select distinct(c.c_id) from course c inner join teacher t on t.t_id=c.c_t_id where t_name='鲁迅')select s.s_id,s.s_name from student s where s.s_id not in (select distinct(s_id) from student s inner join student_score ss on s.s_id=ss.ss_s_id inner join course c on c.c_id=ss.ss_c_id inner join teacher t on t.t_id=c.c_t_id where c.c_id in (select distinct(c.c_id) from course c inner join teacher t on t.t_id=c.c_t_id where t_name='鲁迅'))

 

#6、查询学过“鲁迅作品集”并且也学过“狂人日记”课程的同学的学号、姓名；

##思路：把学过“鲁迅作品集”的人的id找出来，把学过“狂人日记”的人的id找出来，做对比找出同时学过的人的id##特别注意：from的表示一个嵌套查询的表：from (select s.s_id from student s inner join student_score ss on s.s_id=ss.ss_s_id inner join course c on c.c_id=ss.ss_c_id where c.c_name='鲁迅作品集')  aselect * from student s inner join student_score ss on s.s_id=ss.ss_s_id inner join course c on c.c_id=ss.ss_c_id inner join teacher t on t.t_id=c.c_t_id#把学过“鲁迅作品集”的人的id找出来select s.s_id from student s inner join student_score ss on s.s_id=ss.ss_s_id inner join course c on c.c_id=ss.ss_c_id where c.c_name='鲁迅作品集'#把学过“狂人日记”的人的id找出来select s.s_id from student s inner join student_score ss on s.s_id=ss.ss_s_id inner join course c on c.c_id=ss.ss_c_id where c.c_name='狂人日记'#做对比select a.s_id from (select s.s_id from student s inner join student_score ss on s.s_id=ss.ss_s_id inner join course c on c.c_id=ss.ss_c_id where c.c_name='鲁迅作品集')  a,(select s.s_id from student s inner join student_score ss on s.s_id=ss.ss_s_id inner join course c on c.c_id=ss.ss_c_id where c.c_name='狂人日记') b where a.s_id=b.s_id#找学生信息select * from student where s_id in (select a.s_id from (select s.s_id from student s inner join student_score ss on s.s_id=ss.ss_s_id inner join course c on c.c_id=ss.ss_c_id where c.c_name='鲁迅作品集')  a,(select s.s_id from student s inner join student_score ss on s.s_id=ss.ss_s_id inner join course c on c.c_id=ss.ss_c_id where c.c_name='狂人日记') b where a.s_id=b.s_id)

 

 

 

2、代码

#3、查询所有同学的学号、姓名、选课数、总成绩； ##多表查询推荐用链接查询方式：比如inner joinselect s.s_id,s.s_name,count(ss.ss_s_id) course_num,sum(ss.ss_score) from student s inner join student_score ss on s.s_id=ss.ss_s_id group by ss.ss_s_id;#4、查询姓“鲁”的老师的个数； select count(t.t_id) num from teacher t where t_name like '鲁%'#5、查询没学过“鲁迅”老师课的同学的学号、姓名；##找出鲁迅老师课的id，学生not in 里面就好##没学过：总的减去学过了的##去掉重复：distinct函数##总的减去学过了的如何实现：通过嵌套查询+ not inselect c.c_id from course c inner join teacher t on t.t_id=c.c_t_id where t_name='鲁迅'select distinct(c.c_id) from course c inner join teacher t on t.t_id=c.c_t_id where t_name='鲁迅'select distinct(s_id) from student s inner join student_score ss on s.s_id=ss.ss_s_id inner join course c on c.c_id=ss.ss_c_id inner join teacher t on t.t_id=c.c_t_id where c.c_id in (select distinct(c.c_id) from course c inner join teacher t on t.t_id=c.c_t_id where t_name='鲁迅')select s.s_id,s.s_name from student s where s.s_id not in (select distinct(s_id) from student s inner join student_score ss on s.s_id=ss.ss_s_id inner join course c on c.c_id=ss.ss_c_id inner join teacher t on t.t_id=c.c_t_id where c.c_id in (select distinct(c.c_id) from course c inner join teacher t on t.t_id=c.c_t_id where t_name='鲁迅'))#6、查询学过“鲁迅作品集”并且也学过“狂人日记”课程的同学的学号、姓名；##思路：把学过“鲁迅作品集”的人的id找出来，把学过“狂人日记”的人的id找出来，做对比找出同时学过的人的id##特别注意：from的表示一个嵌套查询的表：from (select s.s_id from student s inner join student_score ss on s.s_id=ss.ss_s_id inner join course c on c.c_id=ss.ss_c_id where c.c_name='鲁迅作品集')  aselect * from student s inner join student_score ss on s.s_id=ss.ss_s_id inner join course c on c.c_id=ss.ss_c_id inner join teacher t on t.t_id=c.c_t_id#把学过“鲁迅作品集”的人的id找出来select s.s_id from student s inner join student_score ss on s.s_id=ss.ss_s_id inner join course c on c.c_id=ss.ss_c_id where c.c_name='鲁迅作品集'#把学过“狂人日记”的人的id找出来select s.s_id from student s inner join student_score ss on s.s_id=ss.ss_s_id inner join course c on c.c_id=ss.ss_c_id where c.c_name='狂人日记'#做对比select a.s_id from (select s.s_id from student s inner join student_score ss on s.s_id=ss.ss_s_id inner join course c on c.c_id=ss.ss_c_id where c.c_name='鲁迅作品集')  a,(select s.s_id from student s inner join student_score ss on s.s_id=ss.ss_s_id inner join course c on c.c_id=ss.ss_c_id where c.c_name='狂人日记') b where a.s_id=b.s_id#找学生信息select * from student where s_id in (select a.s_id from (select s.s_id from student s inner join student_score ss on s.s_id=ss.ss_s_id inner join course c on c.c_id=ss.ss_c_id where c.c_name='鲁迅作品集')  a,(select s.s_id from student s inner join student_score ss on s.s_id=ss.ss_s_id inner join course c on c.c_id=ss.ss_c_id where c.c_name='狂人日记') b where a.s_id=b.s_id)