如何在MySQL数据库中进行有效的练习以提升技能？

MySQL数据库练习

建表和插入数据

在开始之前，先建立本文所需要的数据表格：

1、学生表

CREATE TABLE Student(
    s_id VARCHAR(20),
    s_name VARCHAR(20) NOT NULL DEFAULT '',
    s_birth VARCHAR(20) NOT NULL DEFAULT '',
    s_sex VARCHAR(10) NOT NULL DEFAULT '',
    PRIMARY KEY(s_id)
);

2、课程表

CREATE TABLE Course(
    c_id VARCHAR(20),
    c_name VARCHAR(20) NOT NULL DEFAULT '',
    t_id VARCHAR(20) NOT NULL,
    PRIMARY KEY(c_id)
);

3、教师表

CREATE TABLE Teacher(
    t_id VARCHAR(20),
    t_name VARCHAR(20) NOT NULL DEFAULT '',
    PRIMARY KEY(t_id)
);

4、成绩表

CREATE TABLE Score(
    s_id VARCHAR(20),
    c_id VARCHAR(20),
    s_score INT(3),
    PRIMARY KEY(s_id, c_id)
);

5、插入学生表测试数据

INSERT INTO Student VALUES('01', '赵雷', '19900101', '男');
INSERT INTO Student VALUES('02', '钱电', '19901221', '男');
INSERT INTO Student VALUES('03', '孙风', '19900520', '男');
INSERT INTO Student VALUES('04', '李云', '19900806', '男');
INSERT INTO Student VALUES('05', '周梅', '19911201', '女');
INSERT INTO Student VALUES('06', '吴兰', '19920301', '女');
INSERT INTO Student VALUES('07', '郑竹', '19890701', '女');
INSERT INTO Student VALUES('08', '王菊', '19900120', '女');

6、插入课程表测试数据

INSERT INTO Course VALUES('01', '语文', '02');
INSERT INTO Course VALUES('02', '数学', '01');
INSERT INTO Course VALUES('03', '英语', '03');

7、插入教师表测试数据

INSERT INTO Teacher VALUES('01', '张三');
INSERT INTO Teacher VALUES('02', '李四');
INSERT INTO Teacher VALUES('03', '王五');

8、插入成绩表测试数据

INSERT INTO Score VALUES('01', '01', 80);
INSERT INTO Score VALUES('01', '02', 90);
INSERT INTO Score VALUES('01', '03', 99);
INSERT INTO Score VALUES('02', '01', 70);
INSERT INTO Score VALUES('02', '02', 60);
INSERT INTO Score VALUES('02', '03', 80);
INSERT INTO Score VALUES('03', '01', 80);
INSERT INTO Score VALUES('03', '02', 80);
INSERT INTO Score VALUES('03', '03', 80);
INSERT INTO Score VALUES('04', '01', 50);
INSERT INTO Score VALUES('04', '02', 30);
INSERT INTO Score VALUES('04', '03', 20);
INSERT INTO Score VALUES('05', '01', 76);
INSERT INTO Score VALUES('05', '02', 87);
INSERT INTO Score VALUES('06', '01', 31);
INSERT INTO Score VALUES('06', '03', 34);
INSERT INTO Score VALUES('07', '02', 89);
INSERT INTO Score VALUES('07', '03', 98);

练习题及解答

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

SELECT a.*, b.s_score as score_01, c.s_score as score_02
FROM Student a
JOIN Score b ON a.s_id = b.s_id AND b.c_id = '01'
LEFT JOIN Score c ON a.s_id = c.s_id AND c.c_id = '02';

2、查询同时存在"01"课程和"02"课程的情况

SELECT a.*, b.s_score as score_01, c.s_score as score_02
FROM Student a
JOIN Score b ON a.s_id = b.s_id AND b.c_id = '01'
JOIN Score c ON a.s_id = c.s_id AND c.c_id = '02';

3、查询存在"01"课程但可能不存在"02"课程的情况（不存在时显示为NULL）

SELECT a.*, b.s_score as score_01, c.s_score as score_02
FROM Student a
JOIN Score b ON a.s_id = b.s_id AND b.c_id = '01'
LEFT JOIN Score c ON a.s_id = c.s_id AND c.c_id = '02';

4、查询不存在"01"课程但存在"02"课程的情况

SELECT a.*, b.s_score as score_01, c.s_score as score_02
FROM Student a
LEFT JOIN Score b ON a.s_id = b.s_id AND b.c_id = '01'
JOIN Score c ON a.s_id = c.s_id AND c.c_id = '02'
WHERE b.s_score IS NULL;

5、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

SELECT s_id, s_name, AVG(s_score) as average_score
FROM Student a
JOIN Score b ON a.s_id = b.s_id
GROUP BY s_id, s_name
HAVING AVG(s_score) >= 60;

6、查询在Score表中存在成绩的学生信息

SELECT DISTINCT a.*
FROM Student a
JOIN Score b ON a.s_id = b.s_id;

7、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩，没成绩的显示为NULL

SELECT a.s_id, a.s_name, COUNT(b.c_id) as course_count, COALESCE(SUM(b.s_score), 0) as total_score
FROM Student a
LEFT JOIN Score b ON a.s_id = b.s_id
GROUP BY a.s_id, a.s_name;

8、查询"李"姓老师的数量

SELECT COUNT(*) as teacher_count
FROM Teacher a
WHERE t_name LIKE '李%';

9、查询学过"张三"老师授课的同学的信息

SELECT DISTINCT a.*
FROM Student a
JOIN Score b ON a.s_id = b.s_id
JOIN Course c ON b.c_id = c.c_id AND c.t_id = (SELECT t_id FROM Teacher WHERE t_name = '张三');

10、查询没有学全所有课程的同学的信息

SELECT a.*
FROM Student a
WHERE (SELECT COUNT(*) FROM Score WHERE s_id = a.s_id) < (SELECT COUNT(*) FROM Course);