MySQL多表关联查询练习

练习要求：

练习一 

1.创建student和score表
CREATE TABLE student (  
    id INT(10) NOT NULL UNIQUE PRIMARY KEY,  
    name VARCHAR(20) NOT NULL,  
    sex VARCHAR(4),  
    birth YEAR, -- 不需要指定长度，YEAR 类型默认就是4位  
    department VARCHAR(20),  
    address VARCHAR(50)  
);
创建score表。SQL代码如下：
CREATE TABLE score (  
    id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,  
    stu_id INT(10) NOT NULL,  
    c_name VARCHAR(20),  
    grade INT(10)  
);
2.为student表和score表增加记录
向student表插入记录的INSERT语句如下：
INSERT INTO student VALUES( 901,'张老大', '男',1985,'计算机系', '北京市海淀区');
INSERT INTO student VALUES( 902,'张老二', '男',1986,'中文系', '北京市昌平区');
INSERT INTO student VALUES( 903,'张三', '女',1990,'中文系', '湖南省永州市');
INSERT INTO student VALUES( 904,'李四', '男',1990,'英语系', '辽宁省阜新市');
INSERT INTO student VALUES( 905,'王五', '女',1991,'英语系', '福建省厦门市');
INSERT INTO student VALUES( 906,'王六', '男',1988,'计算机系', '湖南省衡阳市');
向score表插入记录的INSERT语句如下：
INSERT INTO score VALUES(NULL,901, '计算机',98);
INSERT INTO score VALUES(NULL,901, '英语', 80);
INSERT INTO score VALUES(NULL,902, '计算机',65);
INSERT INTO score VALUES(NULL,902, '中文',88);
INSERT INTO score VALUES(NULL,903, '中文',95);
INSERT INTO score VALUES(NULL,904, '计算机',70);
INSERT INTO score VALUES(NULL,904, '英语',92);
INSERT INTO score VALUES(NULL,905, '英语',94);
INSERT INTO score VALUES(NULL,906, '计算机',90);
INSERT INTO score VALUES(NULL,906, '英语',85);

3.查询student表的所有记录

要查询student表的所有记录，可以使用SELECT语句配合*通配符来选择所有列。

SELECT * FROM student;

效果展示：

 

4.查询student表的第2条到4条记录

SELECT * FROM student  
ORDER BY id -- 或者其他您希望用于排序的字段  
LIMIT 4 OFFSET 1; -- LIMIT 2表示返回4条记录，OFFSET 1表示跳过前面的1条记录

效果展示：

5.从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息

要从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息，可以使用

SELECT id, name, department FROM student;

效果展示：

6.从student表中查询计算机系和英语系的学生的信息

SELECT * FROM student  
WHERE department = '计算机系' OR department = '英语系';

效果展示：

7.从student表中查询年龄18~22岁的学生信息

SELECT * FROM student  
WHERE YEAR(CURDATE()) - birth BETWEEN 18 AND 22;

在这个查询中，CURDATE()函数返回当前日期，YEAR(CURDATE())则提取当前日期的年份部分。然后，这个年份与birth字段中的年份相减，得到学生的年龄。BETWEEN 18 AND 22则用于筛选出年龄在18到22岁之间的学生。

8.从student表中查询每个院系有多少人

要从student表中查询每个院系有多少人，可以使用GROUP BY子句来按department字段分组，并使用COUNT(*)函数来计算每个组（即每个院系）中的记录数。

SELECT department, COUNT(*) as student_count  
FROM student  
GROUP BY department;

说明：这个查询会返回每个department的值以及对应的student_count，即该院系的学生数量。GROUP BY department确保结果按院系分组，而COUNT(*)则计算每个组中的行数，即该院系的学生数。as student_count是一个别名，用于在结果集中更清晰地标识学生数量的列。 

效果展示：

9.从score表中查询每个科目的最高分

SELECT c_name, MAX(grade) as max_grade  
FROM score  
GROUP BY c_name;

这个查询会返回每个c_name（科目名称）的值以及对应的max_grade（最高分），即该科目的最高分数。GROUP BY c_name确保结果按科目名称分组，而MAX(grade)则计算每个组中的最高分数。as max_grade是一个别名，用于在结果集中更清晰地标识最高分数的列。 

效果展示：

10.查询李四的考试科目（c_name）和考试成绩（grade）

SELECT s.name, sc.c_name, sc.grade  
FROM student s  
JOIN score sc ON s.id = sc.stu_id  
WHERE s.name = '李四';

效果展示：

11.用连接的方式查询所有学生的信息和考试信息

SELECT student.*, score.*  
FROM student  
JOIN score ON student.id = score.stu_id;

效果展示;

12.计算每个学生的总成绩

SELECT s.id AS student_id, s.name AS student_name, SUM(sc.grade) AS total_grade  
FROM student s  
JOIN score sc ON s.id = sc.stu_id  
GROUP BY s.id, s.name;

说明：

在这个查询中：

• 选择了student表（别名为s）的id和name字段，以及score表（别名为sc）的grade字段。
• 通过JOIN子句将两个表连接起来，连接条件是学生的id（或stu_id）在两个表中必须匹配。
• 使用GROUP BY子句按学生的id和name分组，这确保了每个学生只有一个总成绩记录（即使他们有多门课程的分数）。
• 使用SUM(sc.grade)来计算每个学生的总成绩，并将其命名为total_grade。

效果展示：

13.计算每个考试科目的平均成绩

SELECT c_name, AVG(grade) AS average_grade  
FROM score  
GROUP BY c_name;

效果展示：

14.查询计算机成绩低于95的学生信息

SELECT student.*  
FROM student  
JOIN score ON student.id = score.stu_id  
WHERE score.c_name = '计算机' AND score.grade < 95;

效果展示：

15.查询同时参加计算机和英语考试的学生的信息

SELECT DISTINCT student.*  
FROM student  
JOIN score AS s1 ON student.id = s1.stu_id AND s1.c_name = '计算机'  
JOIN score AS s2 ON student.id = s2.stu_id AND s2.c_name = '英语';

效果展示：

16.将计算机考试成绩按从高到低进行排序

SELECT *  
FROM score  
WHERE c_name = '计算机'  
ORDER BY grade DESC;

效果展示：

17.从student表和score表中查询出学生的学号，然后合并查询结果

SELECT id AS student_id FROM student  UNION   SELECT DISTINCT stu_id AS student_id FROM score;

效果展示：

18.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩

SELECT student.name, student.department, score.c_name, score.grade  
FROM student  
JOIN score ON student.id = score.stu_id  
WHERE student.name LIKE '张%' OR student.name LIKE '王%';

效果展示：

19.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩

SELECT   
    s.name AS student_name,      
    s.department AS student_department,   
    sc.c_name AS exam_course,   
    sc.grade AS exam_grade  
FROM   
    student s  
JOIN   
    score sc ON s.id = sc.stu_id  
WHERE   
    s.address like '湖南省%';

效果展示：