MySQL查询练习

MySQL查询练习

导读：

以下是MySQL中查询练习题，该练习题是个人整理的，如果哪些地方有错误或者疑问，欢迎指出；

个人使用navicate版本是15，mysql版本5.7.31

如果有些语句显示group by的问题，建议查看MySQL版本：

如果是mysql5.7.x版本，默认是开启了 only_full_group_by 模式，会导致代码报错；

解决方法：

1、查看sql_mode：

select @@global.sql_mode;

查询出来的值为：

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

2、去掉ONLY_FULL_GROUP_BY，重新设置值。

 set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

3、上面是改变了全局sql_mode，对于新建的数据库有效。对于已存在的数据库，则需要在对应的数据下执行：

set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

---

资源问题：

将student各表，以及study表放到百度网盘中，自取；

链接：https://pan.baidu.com/s/1CxZA_pb9k_4UKZDKMQovNw
提取码：1234

一、 实验目的

1、掌握查询语句的基本组成和使用方法。

2、掌握常用查询技巧。

二、 实验预习

1、 SQL中查询语句的语句格式：

Select 属性名 from 表名;

2、 SQL中创建数据表的语句格式：

Create table 表名(

	字段名 字段类型，………

);

三、 实验内容及要求

1、 数据库*db_student*中基本表的数据如下，输入下列数据。

学生表：Student

Sno	Sname	Ssex	Sage	Sdept
9512101	李勇	男	19	计算机系
9512103	王敏	女	20	计算机系
9521101	张莉	女	22	信息系
9521102	吴宾	男	21	信息系
9521103	张海	男	20	信息系
9531101	钱小平	女	18	数学系
9531102	王大力	男	19	数学系

课程表：Course

Cno	Cname	Ccredit	Semster	Period
C01	计算机导论	3	1	3
C02	VB	4	3	4
C03	计算机网络	4	7	4
C04	数据库基础	6	6	4
C05	高等数学	8	1	8

选课表：SC

Sno	Cno	Grade
9512101	C03	95
9512103	C03	51
9512101	C05	80
9512103	C05	NULL
9521101	C05	NULL
9521102	C05	80
9521103	C05	45
9531101	C05	81
9531101	C01	67
9531102	C05	94
9521103	C01	80
9512101	C01	NULL
9531102	C01	NULL
9512101	C02	87
9512101	C04	76

2、根据db_student中的数据，完成下列查询，将查询语句写在下方。

（1）查询全体学生的信息。

Select * from student;

（2）查询“信息系”学生的学号，姓名和出生年份。

Select sno,sname,YEAR(NOW())-sage from student 

WHERE sdept='信息系';

（3）查询考试不及格的学生的学号。

Select distinct sno from sc where grade<60;

（4）查询无考试成绩的学生的学号和相应的课程号。

Select sno,cno from sc where grade is null;

（5）将学生按年龄升序排序。

Select * from student order by sage;

（6）查询选修了课程的学生的学号和姓名。

（要求：分别使用连接查询、嵌套子查询完成）

连接查询：

Select distinct student.sname,sc.sno from student,sc where student.sno=sc.sno ;

嵌套子查询：

select sno,sname from student where sno in (
select distinct sno from sc);

补充：

=any-->等于子查询结果中的某个值。

Select sno,sname from student where sno=any (select distinct sno from sc);

（7）查询年龄在20-23岁之间的学生的系，姓名，年龄，按照系升序排序。

Select sname,sage,sdept from student where sage between 20 and 23 order by sdept;

补充：

注意：utf8默认的校队集是utf-8-general-ci,他不是按照中文来的，需要强制让mysql按照中文来排序，gbk包含全部的中文字符，utf-8则包含全世界所有国家需要用到的字符；

Select sname,sage,sdept from student where sage between 20 and 23 order by convert(sdept using gbk);

（8）查询选修了“计算机网络”或者“数据库基础”课程的学生的学号，姓名。

（要求：分别使用连接查询、嵌套子查询完成）

连接查询：

select distinct student.sno,sname from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and (cname='计算机网络' or cname='数据库基础');

嵌套查询：

select student.sno,sname 

from student

where sno in 

(

	select sno From sc

	where cno in (select cno from course where cname = '计算机网络' or cname = '数据库基础' ));

补充：

联合查询：

Select sno,sname from student where sno in (select sno from course,sc where course.cno =sc.cno and cname ='计算机网络')
Union
Select sno,sname from student where sno in (select sno from course,sc where course.cno =sc.cno and cname ='数据库基础');

（9）查询姓“张”的学生的基本信息。

select * from student where sname like'张%';

（10）查询学生的选课情况，要求输出学号，姓名，课程门数，课程名列表（用逗号分隔），按照学号升序排序。

SELECT student.sno,sname,COUNT(*),

GROUP_CONCAT(cname ORDER BY cname SEPARATOR ',')'课程列表'

FROM student,sc,course

WHERE student.sno=sc.sno AND sc.cno=course.cno

GROUP BY student.sno

ORDER BY student.sno;

（11）查询选修了课程的学生的总人数。

（要求：分别使用嵌套子查询的谓词IN和EXISTS完成）

谓词IN：

SELECT count(DISTINCT sno) FROM sc WHERE sno in (select sno from sc);

谓词EXISTS：

SELECT COUNT(DISTINCT sno) FROM sc WHERE  EXISTS (SELECT sno FROM sc);

（12）统计各门课程选修人数，要求输出课程代号，课程名，选修人数，有成绩人数（grade不为NULL），最高分，最低分，平均分，按课程号排序。

Select c.cno,cname,count(*),MAX(grade),MIN(grade),AVG(grade),COUNT(grade)

From student s,sc,course c where s.sno=sc.sno and sc.cno=c.cno GROUP BY c.cno;

（13）统计各门课程的重修人数（包括grade为NULL），要求输出课程代号，重修人数。

SELECT cno,COUNT(*) from sc WHERE grade<60 OR grade is NULL GROUP BY cno;

（14）查询选修通过2门（包括2门）以上的学生的信息，输出学号、选修通过门数、平均成绩，按门数降序排序，若门数相同，按照成绩降序。

select sno, count(*),avg(grade)

from sc	where grade >= 60

group by sno

having count(*) >= 2 order by count(*) DESC,avg(grade) desc;

（15）查询与“王大力”同一个系的学生的基本信息。

SELECT * FROM student WHERE sname !='王大力' and sdept in (

SELECT distinct sdept FROM student WHERE sname='王大力');

（16）查询每个学生高于他自己选修平均分的那门课程的成绩，输出学号，课程号，课程成绩，他所有课程的平均分，并按学号升序排列。

（要求：使用基于子查询派生表的查询方法）

SELECT sc.sno,cno,grade, avggrade FROM sc,

(SELECT sno, AVG(grade) avggrade FROM sc GROUP BY sno) AS  avg_sc

WHERE sc.sno=avg_sc.sno AND

 sc.grade>avg_sc.avggrade

ORDER BY sc.sno;

（17）查询没有同时选修“计算机导论”和“计算机网络”两门课的学生的学号，姓名。

Select sno,sname from student where sno not in(

Select sno from sc,course where sc.cno=course.cno and cname='计算机导论'

And sno in (

Select sno from sc,course where sc.cno=course.cno and cname='计算机网络'

));(包含了没有任何选课的同学的信息)

Select sno,sname from student where sno not in (select sno from student where not exists(

Select * from course where cname in ('计算机网络','计算机导论') and not exists(

Select * from sc where sno=student.sno and cno=course.cno)

));(包含了没有任何选课的同学信息)

Select distinct sc.cno,sname from student,sc where student.sno=sc.sno and

sc.sno not in(select sno from sc,course where sc.cno=course.cno and cname='计算机导论' and sno in (select sno from sc,course where sc.cno=course.cno and cname='计算机网络'));

（18）查询选修了全部课程的学生的学号，姓名，系名。

select student.sno,sname,sdept from student where NOT exists     

(select  * from course where NOT exists    

(select * from sc where sc.sno = student.sno  and sc.cno = course.cno));

补充：

Select sno,sname,sdept from student where sno in(

Select sno from sc group by sno having count(*)=(select count(*) from course)

);

（19）输出“高等数学”课程成绩前三名的学生的学号，姓名，系名

SELECT s.sno,sname,sdept from student s,sc,course c WHERE s.sno=sc.sno and sc.cno=c.cno AND cname='高等数学'

ORDER BY grade DESC LIMIT 3;(不考虑成绩有重复值的情况)

补充：

Select student.sno,sname,sdept from student,sc,course,(select distinct grade from sc,course where sc.cno=course.cno and cname='高等数学'order by grade
desc limit 3) as g where student.sno=sc.sno and sc.cno=course.cno and sc.grade=g.grade and cname='高等数学';

3、导入数据库study，完成下列查询，将查询语句写在下方。

（1）查询总经理、经理以下的职员信息，包括NULL值记录。

select * from employee where job_title is null or job_title not in (
select job_title from employee where job_title ='总经理' or job_title='经理'
)；

（2）查询“联荣资产”的客户信息。

select * from customer where customer_name like '%联荣资产%'；

3、导入数据库study，完成下列查询，将查询语句写在下方。

（1）查询总经理、经理以下的职员信息，包括NULL值记录。

SELECT * FROM employee WHERE job_title is NULL

OR job_title not IN(

SELECT job_title from employee WHERE job_title='总经理'

OR job_title='经理'

);

（2）查询“联荣资产”的客户信息。

SELECT * from customer WHERE customer_name LIKE '%联荣资产%';

（3）查询价格5000-6000的“联想”品牌和价格在5000以下的“小米”品牌的产品信息。

select * FROM product WHERE description LIKE '%联想%' AND price BETWEEN 5000 AND 6000 

UNION 

SELECT * FROM product WHERE description LIKE '%小米%' AND price < 5000;

（4）查询如“GTX950M”/“GTX960M”系列的产品信息。

SELECT * FROM product WHERE description LIKE '%GTX950M%' OR description LIKE '%GTX960M%';

（5）统计各年份订单总数，订单总额，按年份降序排列。

SELECT YEAR(pay_time),count(*) as number,sum(total_money) as money FROM payment GROUP BY YEAR(pay_time) ORDER BY YEAR(pay_time) DESC;

（6）统计2016年各产品的订购数量（降序排列），输出5-10名的统计信息，包括产品ID，订购总数。

select product_id,payment.order_id, count(*), payment_id

from payment, order_detail where year(pay_time) = 2016 

and payment.order_id = order_detail.order_id group by order_id

order by buy_number desc limit 4,6;

结尾：

基础部分会尽快完善，还不了解的可看之前的MySQL部分：

https://www.cnblogs.com/xbhog/p/13721359.html

GitHub：https://github.com/xbhog

如果可以希望star!

导读部分有些引自于:https://blog.csdn.net/With_Her/article/details/88120534;

感谢各位看到最后，加油，代码人！