SQL数据库操作练习题：更新、删除、查询及统计

SQL数据库操作练习题

以下是一些常见的SQL数据库操作练习题，可以帮助你更好地理解SQL语句的使用。

1. 更新数据

把 'user' 表中字段 'students' 为'小明' 所在字段 'score' 更改为30分

UPDATE user SET score = 30 WHERE students = '小明';

把 'user' 表 'students' 字段为'小明'的记录删除

DELETE FROM user WHERE students = '小明';

2. 查询数据

查询出男女生的最大年龄

SELECT MAX(age) FROM user WHERE sex = '男';
SELECT MAX(age) FROM user WHERE sex = '女';

查询出哪个班的最大年龄小于18

SELECT class FROM user GROUP BY class HAVING MAX(age) < 18;

查询出每个名字的使用数量并降序排列

SELECT students, COUNT(*) AS count FROM user GROUP BY students ORDER BY count DESC;

查询出每班男女平均年龄大于1岁的数据

SELECT class FROM user GROUP BY class HAVING AVG(age) > 1;

查询出每班重名的信息

SELECT class, students FROM user GROUP BY class, students HAVING COUNT(*) > 1;

查询出平均年龄在20岁以下的性别内容：为女生的人员名单

SELECT students FROM user WHERE sex = '女' AND class IN (SELECT class FROM user GROUP BY class HAVING AVG(age) < 20);

查询出每个班级分数排名前三的学生信息

SELECT * FROM user ORDER BY class, score DESC LIMIT 3;

查询出每个班级的平均分，并按照平均分从高到低排序

SELECT class, AVG(score) AS avg_score FROM user GROUP BY class ORDER BY avg_score DESC;

查询出每个班级的总分，并按照总分从高到低排序

SELECT class, SUM(score) AS total_score FROM user GROUP BY class ORDER BY total_score DESC;

查询出每个班级不及格（<60分）的学生数量

SELECT class, COUNT(*) AS fail_count FROM user WHERE score < 60 GROUP BY class;

查询出每个班级及格（>=60分）的学生数量

SELECT class, COUNT(*) AS pass_count FROM user WHERE score >= 60 GROUP BY class;

查询出每个班级优秀（>=90分）的学生数量

SELECT class, COUNT(*) AS excellent_count FROM user WHERE score >= 90 GROUP BY class;

查询出每个班级男女生的数量

SELECT class, SUM(CASE WHEN sex = '男' THEN 1 ELSE 0 END) AS male_count, SUM(CASE WHEN sex = '女' THEN 1 ELSE 0 END) AS female_count FROM user GROUP BY class;

注意：

以上SQL语句仅供参考，具体的实现需要根据实际数据库表结构进行调整。
这些练习题涉及到SQL语句的多种操作，如更新、删除、查询、聚合函数、分组、排序等，可以帮助你更好地掌握SQL语言。
练习这些SQL语句，可以提高你的数据库操作能力，让你能够更有效地管理和分析数据。