-- 创建学生表
CREATE TABLE students (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  age INT,
  gender ENUM('男', '女'),
  class_id INT,
  score DECIMAL(5,2)  -- 成绩（满分100）
);

-- 创建班级表
CREATE TABLE classes (
  id INT PRIMARY KEY AUTO_INCREMENT,
  class_name VARCHAR(50) NOT NULL
);

-- 插入班级数据
INSERT INTO classes (class_name) VALUES 
('一年级一班'), 
('一年级二班'), 
('二年级一班');

-- 插入学生数据
INSERT INTO students (name, age, gender, class_id, score) VALUES
('张三', 8, '男', 1, 95.5),
('李四', 9, '女', 1, 88.0),
('王五', 8, '男', 2, 76.5),
('赵六', 9, '女', 2, 92.0),
('钱七', 10, '男', 3, 60.0),
('孙八', 9, '女', 3, 98.5),
('周九', 8, '男', 1, 59.5),
('吴十', 10, '女', 3, 85.0);

-- 1. 查询所有学生的姓名和年龄
SELECT name, age FROM students;

-- 5. 查询所有男生的信息，按年龄从大到小排序
SELECT * FROM students WHERE gender = '男' ORDER BY age DESC;

-- 9. 统计每个班级的学生人数
SELECT class_id, COUNT(*) AS 人数 FROM students GROUP BY class_id;

-- 12. 联表查询学生姓名、班级名称和成绩
SELECT s.name, c.class_name, s.score 
FROM students s
JOIN classes c ON s.class_id = c.id;