-- 學(xué)生表：存儲學(xué)生基礎(chǔ)信息
CREATE TABLE students (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主鍵ID，自增',
    sn INT NOT NULL UNIQUE COMMENT '學(xué)號，唯一約束',
    name VARCHAR(20) NOT NULL COMMENT '姓名',
    qq VARCHAR(20) COMMENT 'QQ號，允許為空'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 成績表：存儲學(xué)生考試成績（核心查詢案例表）
CREATE TABLE exam_result (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主鍵ID',
    name VARCHAR(20) NOT NULL COMMENT '學(xué)生姓名',
    chinese FLOAT DEFAULT 0.0 COMMENT '語文成績',
    math FLOAT DEFAULT 0.0 COMMENT '數(shù)學(xué)成績',
    english FLOAT DEFAULT 0.0 COMMENT '英語成績'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 給成績表插入測試數(shù)據(jù)
INSERT INTO exam_result (name, chinese, math, english)
VALUES 
('張三', 67, 98, 56),
('李四', 87, 78, 77),
('王五', 88, 98, 90),
('趙六', 82, 84, 67),
('錢七', 55, 85, 45),
('孫八', 70, 73, 78),
('周九', 75, 65, 30);

-- 標準語法，INTO 可省略
INSERT [INTO] 表名 
[(字段1, 字段2, ...)] 
VALUES (值1, 值2, ...), (值1, 值2, ...);

-- 單行全列插入
INSERT INTO students VALUES (1, 1001, '張三', '123456');
-- 多行全列插入
INSERT INTO students VALUES 
(2, 1002, '李四', '654321'),
(3, 1003, '王五', NULL); -- QQ為空，使用 NULL

-- 僅插入學(xué)號、姓名，id自增、qq默認為NULL
INSERT INTO students (sn, name) 
VALUES (1004, '趙六'), (1005, '錢七');

-- id=1 已存在，觸發(fā)更新；不存在則新增
INSERT INTO students (id, sn, name) 
VALUES (1, 1009, '小張')
ON DUPLICATE KEY UPDATE sn = 1009, name = '小張';

-- 學(xué)號sn是唯一鍵，重復(fù)則刪除舊數(shù)據(jù)、插入新數(shù)據(jù)
REPLACE INTO students (sn, name, qq) VALUES (1001, '新張三', '999999');

-- 語法：INSERT ... SELECT
INSERT INTO 目標表(字段1,字段2) SELECT 字段1,字段2 FROM 源表 [條件];

-- 示例：將成績表中數(shù)學(xué)>80的學(xué)生姓名、成績插入新表
CREATE TABLE temp_result (name VARCHAR(20), math FLOAT);
INSERT INTO temp_result (name, math) 
SELECT name, math FROM exam_result WHERE math > 80;

SELECT [DISTINCT] 字段列表
FROM 表名
[WHERE 條件]          -- 行過濾
[GROUP BY 分組字段]   -- 分組
[HAVING 分組后條件]   -- 分組過濾
[ORDER BY 排序字段]   -- 結(jié)果排序
[LIMIT 分頁限制];     -- 分頁

SELECT * FROM exam_result;

-- 只查詢姓名、語文、數(shù)學(xué)成績
SELECT name, chinese, math FROM exam_result;

-- 1. 計算總分，并設(shè)置別名 總分
SELECT 
    name,
    chinese,
    math,
    english,
    chinese + math + english AS total_score -- 別名
FROM exam_result;

-- 2. AS 可省略，簡寫形式
SELECT name, chinese + math + english total FROM exam_result;

-- 查詢所有不重復(fù)的數(shù)學(xué)成績
SELECT DISTINCT math FROM exam_result;

-- 1. 英語成績低于60分的學(xué)生（比較運算）
SELECT name, english FROM exam_result WHERE english < 60;

-- 2. 語文成績在 [80,90] 區(qū)間（兩種寫法等價）
SELECT name, chinese FROM exam_result WHERE chinese >=80 AND chinese <=90;
SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90;

-- 3. 數(shù)學(xué)成績?yōu)?78、84、98 之一（IN 枚舉）
SELECT name, math FROM exam_result WHERE math IN(78,84,98);

-- 4. 模糊查詢：姓"張"的學(xué)生（% 通配）
SELECT name FROM exam_result WHERE name LIKE '張%';
-- 名字第二個字為"五"（_ 單個字符）
SELECT name FROM exam_result WHERE name LIKE '_五';

-- 5. 判斷空值：查詢QQ號為空的學(xué)生
SELECT name FROM students WHERE qq IS NULL;

-- 6. 多條件組合：語文>80 并且 不姓王
SELECT name, chinese FROM exam_result 
WHERE chinese > 80 AND NOT name LIKE '王%';

-- 數(shù)學(xué)成績升序（默認ASC）
SELECT name, math FROM exam_result ORDER BY math;
-- 數(shù)學(xué)成績降序
SELECT name, math FROM exam_result ORDER BY math DESC;

-- 先數(shù)學(xué)降序，數(shù)學(xué)相同則英語升序
SELECT name, math, english FROM exam_result
ORDER BY math DESC, english ASC;

-- 寫法1：LIMIT 行數(shù) （從第0條開始，取N條）
SELECT 字段 FROM 表 LIMIT N;

-- 寫法2：LIMIT 偏移量, 行數(shù) （偏移量從0開始）
SELECT 字段 FROM 表 LIMIT offset, N;

-- 寫法3（推薦，語義清晰）：LIMIT 行數(shù) OFFSET 偏移量
SELECT 字段 FROM 表 LIMIT N OFFSET offset;

-- 第1頁：偏移0，取3條
SELECT * FROM exam_result ORDER BY id LIMIT 3 OFFSET 0;
-- 第2頁：偏移3，取3條
SELECT * FROM exam_result ORDER BY id LIMIT 3 OFFSET 3;
-- 第3頁：偏移6，取3條（不足3條則返回剩余數(shù)據(jù)）
SELECT * FROM exam_result ORDER BY id LIMIT 3 OFFSET 6;

-- 1. 統(tǒng)計總?cè)藬?shù)（COUNT(*) 統(tǒng)計所有行）
SELECT COUNT(*) AS total_student FROM exam_result;

-- 2. 統(tǒng)計有QQ號的學(xué)生數(shù)（COUNT(字段) 忽略NULL）
SELECT COUNT(qq) AS have_qq FROM students;

-- 3. 數(shù)學(xué)成績總分、平均分
SELECT SUM(math) AS math_sum, AVG(math) AS math_avg FROM exam_result;

-- 4. 英語最高分、最低分
SELECT MAX(english) AS max_en, MIN(english) AS min_en FROM exam_result;

-- 5. 去重統(tǒng)計：不重復(fù)的數(shù)學(xué)成績數(shù)量
SELECT COUNT(DISTINCT math) FROM exam_result;

-- 模擬場景：新增班級字段，按班級分組統(tǒng)計
ALTER TABLE exam_result ADD class VARCHAR(10) COMMENT '班級';
UPDATE exam_result SET class = '一班' WHERE id <=4;
UPDATE exam_result SET class = '二班' WHERE id >4;

-- 1. 按班級分組，統(tǒng)計每個班級人數(shù)、語文平均分
SELECT class, COUNT(*) AS num, AVG(chinese) AS avg_ch
FROM exam_result
GROUP BY class;

-- 2. 分組后過濾：只顯示平均分大于70的班級（HAVING）
SELECT class, AVG(chinese) AS avg_ch
FROM exam_result
GROUP BY class
HAVING avg_ch > 70;

UPDATE 表名 
SET 字段1 = 值1, 字段2 = 值2, ...
[WHERE 條件] 
[ORDER BY 排序]
[LIMIT 行數(shù)];

-- 1. 單字段更新：將張三的數(shù)學(xué)成績改為80
UPDATE exam_result SET math = 80 WHERE name = '張三';

-- 2. 多字段更新：同時修改語文、英語成績
UPDATE exam_result 
SET chinese = 70, english = 60 
WHERE name = '李四';

-- 3. 基于原數(shù)據(jù)更新（字段自增/自減）
UPDATE exam_result SET math = math + 10 WHERE math < 80;

-- 4. 結(jié)合排序+分頁更新：成績倒數(shù)3名，數(shù)學(xué)+20分
UPDATE exam_result 
SET math = math + 20
ORDER BY chinese + math + english ASC
LIMIT 3;

-- 刪除符合條件的行
DELETE FROM 表名 [WHERE 條件] [LIMIT 行數(shù)];

-- 1. 刪除指定學(xué)生數(shù)據(jù)（推薦，帶WHERE）
DELETE FROM exam_result WHERE name = '李四';

-- 2. 刪除全表數(shù)據(jù)（高危?。?
DELETE FROM students;

TRUNCATE [TABLE] 表名;