MySQL实验三：查询练习

MySQL实验三：查询练习

目录

前言

整理实验的查询练习题，数据库表会提供建立的sql语句，个人整理版非官方，如果有错误或者疑问，欢迎在评论区提出见解！

需要准备的工具：

1. Navicat9以上
2. mysql5.7或者mysql8都可以

sql文件

阿里云盘里包含了db_student和studymysql的表。

https://www.aliyundrive.com/s/47dHZqRHef9

提取码: od26

实验目的

1. 掌握查询语句的基本组成和使用方法。
2. 掌握常见的查询技巧。

实验预习

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

SELECT column_name,column_name FROM table_name;

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

 CREATE TABLE table_name (column_name1 data_type(size),column_name2 data_type(size),column_name3 data_type(size), .... );

column_name 参数规定表中列的名称。

data_type 参数规定列的数据类型（例如 varchar、integer、decimal、date 等等）。

size 参数规定表中列的最大长度。

实验内容及要求

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.Sno, Sname

FROM student

INNER JOIN sc ON student.Sno = sc.Sno;

这里使用了INNER JOIN连接了student和sc表，通过Sno字段将两个表关联起来，然后选择出Sno和Sname两个字段。

嵌套子查询:

SELECT Sno, Sname

FROM student

WHERE Sno IN (

  SELECT DISTINCT Sno

  FROM sc

);

这里使用了IN子句和嵌套子查询，首先在子查询中获取所有选过课程的学生学号（使用DISTINCT关键字去重），然后在外层查询中选择出学号和姓名两个字段。

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

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

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

连接查询：

SELECT student.Sno, Sname
FROM student
INNER JOIN sc ON student.Sno = sc.Sno
INNER JOIN course ON sc.Cno = course.Cno
WHERE Cname IN ('计算机网络', '数据库基础');

这里使用了INNER JOIN连接了student、sc和course表，通过Sno和Cno字段将三个表关联起来，然后使用WHERE子句过滤出选修了“计算机网络”或者“数据库基础”课程的选课记录，最后选择出Sno和Sname两个字段。

嵌套子查询:

SELECT Sno, Sname
FROM student
WHERE Sno IN (
    SELECT DISTINCT Sno
    FROM sc
    WHERE Cno IN (
        SELECT Cno
        FROM course
        WHERE Cname IN ('计算机网络', '数据库基础')
    )
);

这里使用了嵌套子查询，首先在最内层子查询中获取课程名为“计算机网络”或者“数据库基础”的课程号，然后在中间层子查询中获取选修了这些课程的学生学号（使用DISTINCT关键字去重），最后在外层查询中选择出学号和姓名两个字段。

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

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

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

SELECT student.Sno, Sname, COUNT(sc.Cno) AS CourseCount, GROUP_CONCAT(course.Cname SEPARATOR ', ') AS CourseList
FROM student
LEFT JOIN sc ON student.Sno = sc.Sno
LEFT JOIN course ON sc.Cno = course.Cno
GROUP BY student.Sno
ORDER BY student.Sno ASC;

需要注意的是MySQL的版本中启用了ONLY_FULL_GROUP_BY模式，该模式要求SELECT语句中的非聚合列必须在GROUP BY子句中出现。换句话说，如果SELECT语句中包含非聚合列，那么这些列必须在GROUP BY子句中列出。

可以通过修改MySQL的配置文件或者执行如下SQL语句来关闭ONLY_FULL_GROUP_BY模式：

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

需要先运行这行代码再查询，否则会报错。

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

SELECT count(DISTINCT sno) FROM sc WHERE sno in (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

order by 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 
  WHERE cno IN (
    SELECT cno 
    FROM course 
    WHERE cname IN ('计算机导论', '计算机网络')
  )
  GROUP BY sno
  HAVING COUNT(DISTINCT cno) = 2
);

这个SQL语句使用了三个子查询：

• 最内层的子查询 SELECT cno FROM course WHERE cname IN ('计算机导论', '计算机网络') 查询出了“计算机导论”和“计算机网络”两门课程的课程号。
• 中间层的子查询 SELECT sno FROM sc WHERE cno IN (...) GROUP BY sno HAVING COUNT(DISTINCT cno) = 2 查询出了同时选修了这两门课程的学生的学号。这里使用了GROUP BY子句和HAVING子句，分别对学生的选课记录按学号分组，然后筛选出选修的课程数为2的学生，即同时选修了“计算机导论”和“计算机网络”两门课程的学生。
• 最外层的查询 SELECT sno, sname FROM student WHERE sno NOT IN (...) 选择出没有选修这两门课程的学生的学号和姓名。这里使用NOT IN子句来排除已经在中间层查询中被筛选出的学生。

本题同样可以用not exits来写，思路一致。

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)

));

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));

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;
-- (不考虑成绩有重复值的情况)

数据库studymysql，完成下列查询

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，订购总数。

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

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;

总结

本篇设计大量的查询语句，初学者可能会对有些题产生困惑，这很正常，就好像人终究会被其年少不可得之物困扰一生，也会为一时一景解开其一生的困惑。加油吧少年！！