MySQL实验三：数据查询

MySQL实验三：数据查询

1、查询选修了课程的学生的总人数

学生数据库db_student包括三个数据表student（学生表）、course（课程表）和sc（选课表）。表结构如下：

1、student（学生表）：

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course（课程表）

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc（选课表）

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 成绩 SMALLINT

代码

SELECT COUNT(DISTINCT SNO)
FROM sc;

2、查询考试不及格的学生的学号

学生数据库db_student包括三个数据表student（学生表）、course（课程表）和sc（选课表）。表结构如下：

1、student（学生表）：

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course（课程表）

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc（选课表）

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 成绩 SMALLINT

代码

SELECT distinct SNO
FROM sc
WHERE GRADE < 60;

3、将学生按年龄降序排序

学生数据库db_student包括三个数据表student（学生表）、course（课程表）和sc（选课表）。表结构如下：

1、student（学生表）：

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course（课程表）

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc（选课表）

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 成绩 SMALLINT

代码

SELECT *
FROM student
ORDER BY SAGE DESC;

4、查询“信息系”学生的学号，姓名和出生年份

学生数据库db_student包括三个数据表student（学生表）、course（课程表）和sc（选课表）。表结构如下：

1、student（学生表）：

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course（课程表）

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc（选课表）

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 成绩 SMALLINT

代码

SELECT SNO, SNAME, 2020-SAGE AS BIRTH_YEAR
FROM student
WHERE SDEPT = '信息系';

5、查询全体学生的基本信息

学生数据库db_student包括三个数据表student（学生表）、course（课程表）和sc（选课表）。表结构如下：

1、student（学生表）：

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course（课程表）

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc（选课表）

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 成绩 SMALLINT

代码

SELECT *
FROM student;

6、查询姓“张”的学生的基本信息

学生数据库db_student包括三个数据表student（学生表）、course（课程表）和sc（选课表）。表结构如下：

1、student（学生表）：

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course（课程表）

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc（选课表）

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 成绩 SMALLINT

代码

SELECT * FROM student WHERE SNAME LIKE '张%';

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

学生数据库db_student包括三个数据表student（学生表）、course（课程表）和sc（选课表）。表结构如下：

1、student（学生表）：

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course（课程表）

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc（选课表）

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 成绩 SMALLINT

代码

SELECT SDEPT, SNAME, SAGE
FROM student
WHERE SAGE BETWEEN 20 AND 23
ORDER BY SDEPT ASC, SAGE DESC;

8、查询无考试成绩的学生的学号和相应的课程号

学生数据库db_student包括三个数据表student（学生表）、course（课程表）和sc（选课表）。表结构如下：

1、student（学生表）：

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course（课程表）

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc（选课表）

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 成绩 SMALLINT

代码

SELECT SNO, CNO
FROM sc
WHERE GRADE IS NULL;

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

学生数据库db_student包括三个数据表student（学生表）、course（课程表）和sc（选课表）。表结构如下：

1、student（学生表）：

SNO学号CHAR(7)

SNAME姓名CHAR(10)

SSEX性别CHAR(2)

SAGE年龄SMALLINT

SDEPT所在系 VARCHAR(20)

2、course（课程表）

CNO课程号CHAR(10)

CNAME课程名VARCHAR(20)

CCREDIT学分SMALLINT

SEMSTER学期SMALLINT

PERIOD学时SMALLINT

3、sc（选课表）

SNO 学号CHAR(7)

CNO 课程号CHAR(10)

GRADE 成绩 SMALLINT

代码

SELECT CNO, COUNT(COALESCE(GRADE, 0)) AS '未通过人数'
FROM sc
WHERE GRADE < 60 OR GRADE IS NULL
GROUP BY CNO
ORDER BY CNO ASC;

COUNT(COALESCE(GRADE, 0)) 是一个SQL聚合函数，用于计算指定列的非空值数量。在这里，COALESCE函数的作用是将NULL值替换为0，这样即使该列中包含NULL值，也能正确计算非空值数量。

例如，如果一个课程有3个学生选修，其中2个学生成绩为80和90，而另一个学生没有成绩（即该列为NULL），那么使用COUNT(COALESCE(GRADE, 0))函数将返回值2，因为只有2个学生成绩不为空。如果使用COUNT(GRADE)函数，则会返回值1，因为该列中只有2个学生成绩，其中1个为NULL。

10、查询订单的产品ID列表

代码

select
    order_id,  -- 选择要查询的字段，订单id
    GROUP_CONCAT(  -- 使用 GROUP_CONCAT 函数将多个 product_id 合并为一个字符串
        distinct product_id  -- distinct 关键字去重，确保合并后的字符串中每个 product_id 只出现一次
        ORDER BY product_id asc  -- 对 product_id 进行升序排序，使得合并后的字符串也是升序排列的
        SEPARATOR ':'  -- 使用冒号作为分隔符，将多个 product_id 连接为一个字符串
    )
from order_detail  -- 从 order_detail 表中查询
where order_id = 2  -- 只查询订单id为2的记录
group by order_id;  -- 根据订单id进行分组，确保每个订单只有一行结果