国产成人精品亚洲日本在线_欧美精品v国产精品v_亚洲欧洲日韩国产一区二区三区_亚洲乱码一二三四区

-- 創建學生表
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;