第四次作业:

一、创建数据表 grade

-- 创建表 grade
CREATE TABLE grade(
    id INT NOT NULL,
    sex CHAR(1),
    firstname VARCHAR(20) NOT NULL,
    lastname VARCHAR(20) NOT NULL,
    english FLOAT,
    math FLOAT,
    chinese FLOAT
);

-- 向表 grade 插入数据
INSERT INTO grade
VALUES 
(1, 'm', 'John', 'Smith', 88.0, 85.0, 82.0),
(2, 'f', 'Adam', 'Smith', 76.0, 78.0, 90.0),
(3, 'm', 'Allen', 'William', 88.0, 92.0, 95.0),
(4, 'm', 'George', 'William', 62.0, 58.0, 72.0),
(5, 'f', 'Alice', 'Davis', 89.0, 94.0, 98.0),
(6, 'm', 'Kevin', 'Miller', 77.0, 88.0, 99.0),
(7, 'f', 'Helen', 'Davis', 79.0, 83.0, 91.0),
(8, 'm', 'Andrew', 'Johnson', 81.0, 86.0, 88.0);

二、查询操作

1. 查询所有字段

SELECT * FROM grade;

2. 查询 grade 表中的 id, firstname, lastname 字段

SELECT id, firstname, lastname FROM grade;

3. 查询 grade 表中 id 大于 4 的学生姓名

SELECT firstname, lastname FROM grade WHERE id > 4;

4. 查询 grade 表中女生的记录

SELECT * FROM grade WHERE sex = 'f';

5. 查询 grade 表中 id 值为 2, 4, 6 的学生记录

SELECT * FROM grade WHERE id IN (2, 4, 6);

6. 查询 grade 表中 math 成绩在 85 到 94 之间的记录

SELECT * FROM grade WHERE math BETWEEN 85 AND 94;

7. 查询 grade 表中 firstname 以 A 开头的记录

SELECT * FROM grade WHERE firstname LIKE 'A%';

8. 查询 grade 表中 firstname 以 A 开头以 e 结尾的记录

SELECT * FROM grade WHERE firstname LIKE 'A%e';

9. 查询 grade 表中 firstname 包含 l 的记录

SELECT * FROM grade WHERE firstname LIKE '%l%';

10. 查询 grade 表中 firstname 以 A 开头后面有 4 个字符的记录

SELECT * FROM grade WHERE firstname LIKE 'A____';

11. 查询 grade 表中 english 在 80 到 90 之间的记录

SELECT * FROM grade WHERE english BETWEEN 80 AND 90;

12. 查询 grade 表中 math 大于 90 或者 chinese 大于 90 的记录

SELECT * FROM grade WHERE math > 90 OR chinese > 90;

13. 查询 grade 表中 id 不是 1、3、5、7 的记录

SELECT * FROM grade WHERE id NOT IN (1, 3, 5, 7);

14. 查询 grade 表中的性别有哪些

SELECT DISTINCT sex FROM grade;

15. 查询 grade 表中的 lastname 有哪几种

SELECT DISTINCT lastname FROM grade;

16. 求出表中所有记录的条数

SELECT COUNT(*) FROM grade;

17. 求出表中英语成绩为 80 的记录的条数

SELECT COUNT(*) FROM grade WHERE english = 80;

18. 计算所有学生的数学成绩的和

SELECT SUM(math) FROM grade;

19. 计算女生的数学成绩的和

SELECT SUM(math) FROM grade WHERE sex = 'f';

20. 计算英语成绩平均分

SELECT AVG(english) FROM grade;

21. 计算男生的英语成绩平均分

SELECT AVG(english) FROM grade WHERE sex = 'm';

22. 求出数学成绩的最高分

SELECT MAX(math) FROM grade;

23. 求出男生中的数学最高分

SELECT MAX(math) FROM grade WHERE sex = 'm';

24. 按照 math 成绩的升序进行排列

SELECT * FROM grade ORDER BY math ASC;

25. 按照 sex 字段的升序和 chinese 字段的降序排列

SELECT * FROM grade ORDER BY sex ASC, chinese DESC;

26. 查询数据表 grade 中的记录，按照 sex 字段进行分组

SELECT sex, COUNT(*) AS count FROM grade GROUP BY sex;

27. 将 grade 表按照 lastname 字段值分组，并计算每个分组中的学生数

SELECT lastname, COUNT(*) AS student_count FROM grade GROUP BY lastname;

28. 对 grade 表按照 lastname 字段分组，查出 math 字段和小于 100 的组

SELECT lastname, SUM(math) AS total_math FROM grade GROUP BY lastname HAVING SUM(math) < 100;

29. 查询 grade 表中的第 3 到第 6 条记录

SELECT * FROM grade LIMIT 2, 4;

在 MySQL 中，LIMIT 子句从第 0 条记录开始计数，因此 LIMIT 2, 4 表示从第 3 条记录开始查询 4 条记录。

30. 查询 grade 表中男生平均成绩(三科)大于 85 的记录

SELECT * FROM grade WHERE sex = 'm' AND (english + math + chinese) / 3 > 85;