-- 創(chuàng)建數(shù)據(jù)庫
CREATE DATABASE IF NOT EXISTS test001;
-- 切換數(shù)據(jù)庫
USE test001;
-- 刪除數(shù)據(jù)表
DROP TABLE IF EXISTS student_course;
DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS course;
-- 1. 學(xué)生表（主表）
CREATE TABLE IF NOT EXISTS `student` (
    student_id INT NOT NULL AUTO_INCREMENT COMMENT '學(xué)生ID（主鍵）',
    student_no VARCHAR(20) NOT NULL COMMENT '學(xué)號（唯一標(biāo)識，如2024001）',
    student_name VARCHAR(50) NOT NULL COMMENT '學(xué)生姓名',
    gender CHAR(1) NOT NULL COMMENT '性別（男/女）',
    birth_date DATE NULL COMMENT '出生日期',
    major VARCHAR(50) NOT NULL COMMENT '所屬專業(yè)',
    enroll_date DATE NOT NULL COMMENT '入學(xué)時間',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '記錄創(chuàng)建時間',
    -- 列級約束
    PRIMARY KEY (student_id),
    UNIQUE KEY uk_student_no (student_no),  -- 學(xué)號唯一
    CHECK (gender IN ('男', '女'))  -- 限制性別只能是男或女
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci
AUTO_INCREMENT = 1001  -- 學(xué)生ID從1001開始
COMMENT = '學(xué)生信息表';
-- 2. 課程表（主表）
CREATE TABLE IF NOT EXISTS `course` (
    course_id INT NOT NULL AUTO_INCREMENT COMMENT '課程ID（主鍵）',
    course_no VARCHAR(20) NOT NULL COMMENT '課程編號（如CS101）',
    course_name VARCHAR(100) NOT NULL COMMENT '課程名稱',
    credit TINYINT NOT NULL COMMENT '學(xué)分（1-6分）',
    teacher_name VARCHAR(50) NOT NULL COMMENT '授課教師',
    course_hours INT NOT NULL COMMENT '課時數(shù)',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '記錄創(chuàng)建時間',
    -- 列級約束
    PRIMARY KEY (course_id),
    UNIQUE KEY uk_course_no (course_no),  -- 課程編號唯一
    CHECK (credit BETWEEN 1 AND 6),  -- 學(xué)分范圍限制
    CHECK (course_hours > 0)  -- 課時必須為正數(shù)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci
AUTO_INCREMENT = 101  -- 課程ID從101開始
COMMENT = '課程信息表';
-- 3. 選課表（關(guān)系表，關(guān)聯(lián)學(xué)生表和課程表）
CREATE TABLE IF NOT EXISTS `student_course` (
    id INT NOT NULL AUTO_INCREMENT COMMENT '選課記錄ID（主鍵）',
    student_id INT NOT NULL COMMENT '學(xué)生ID（外鍵關(guān)聯(lián)學(xué)生表）',
    course_id INT NOT NULL COMMENT '課程ID（外鍵關(guān)聯(lián)課程表）',
    select_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '選課時間',
    score DECIMAL(5,2) NULL COMMENT '課程成績（0-100分，NULL表示未考試）',
    is_valid TINYINT NOT NULL DEFAULT 1 COMMENT '是否有效（1-有效，0-已退課）',
    -- 表級約束
    PRIMARY KEY (id),
    -- 聯(lián)合唯一約束：同一學(xué)生不能重復(fù)選同一門課
    UNIQUE KEY uk_stu_course (student_id, course_id),
    -- 外鍵約束：關(guān)聯(lián)學(xué)生表
    CONSTRAINT fk_sc_student FOREIGN KEY (student_id)
        REFERENCES `student`(student_id)
        ON DELETE CASCADE  -- 學(xué)生記錄刪除時，關(guān)聯(lián)的選課記錄自動刪除
        ON UPDATE CASCADE,  -- 學(xué)生ID更新時，選課記錄同步更新
    -- 外鍵約束：關(guān)聯(lián)課程表
    CONSTRAINT fk_sc_course FOREIGN KEY (course_id)
        REFERENCES `course`(course_id)
        ON DELETE CASCADE  -- 課程記錄刪除時，關(guān)聯(lián)的選課記錄自動刪除
        ON UPDATE CASCADE,
    -- 檢查約束：成績范圍限制（0-100分）
    CONSTRAINT chk_score CHECK (score IS NULL OR (score BETWEEN 0 AND 100)),
    -- 檢查約束：is_valid只能是0或1
    CONSTRAINT chk_is_valid CHECK (is_valid IN (0, 1))
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_general_ci
COMMENT = '學(xué)生選課關(guān)系表';
-- 插入 500 條學(xué)生數(shù)據(jù)
DELIMITER $$
CREATE PROCEDURE InsertStudents()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE gender CHAR(1);
    DECLARE year_start DATE;
    DECLARE birth DATE;
    DECLARE major_name VARCHAR(50);
    -- 常見專業(yè)列表
    SET @majors = '計算機科學(xué)與技術(shù),軟件工程,電子信息工程,數(shù)學(xué)與應(yīng)用數(shù)學(xué),物理學(xué),化學(xué),生物技術(shù),機械工程,自動化,通信工程,'
                  '土木工程,環(huán)境工程,經(jīng)濟學(xué),金融學(xué),會計學(xué),法學(xué),漢語言文學(xué),英語,新聞傳播學(xué),臨床醫(yī)學(xué),護理學(xué)';
    WHILE i <= 500 DO
        SET gender = IF(RAND() > 0.5, '男', '女');
        -- 隨機入學(xué)年份：2020 - 2024
        SET year_start = MAKEDATE(2020 + FLOOR(RAND() * 5), 1 + FLOOR(RAND() * 365));
        -- 出生日期：入學(xué)時 17~23 歲
        SET birth = DATE_SUB(year_start, INTERVAL FLOOR(17 + RAND() * 7) YEAR);
        -- 隨機選擇專業(yè)
        SET major_name = ELT(CEILING(RAND() * 21), 
            '計算機科學(xué)與技術(shù)','軟件工程','電子信息工程','數(shù)學(xué)與應(yīng)用數(shù)學(xué)','物理學(xué)',
            '化學(xué)','生物技術(shù)','機械工程','自動化','通信工程',
            '土木工程','環(huán)境工程','經(jīng)濟學(xué)','金融學(xué)','會計學(xué)',
            '法學(xué)','漢語言文學(xué)','英語','新聞傳播學(xué)','臨床醫(yī)學(xué)','護理學(xué)'
        );
        INSERT INTO `student` (student_no, student_name, gender, birth_date, major, enroll_date)
        VALUES (
            CONCAT('20', LPAD(FLOOR(RAND() * 90 + 24), 2, '0'), LPAD(i, 3, '0')),  -- 如 2024001
            CONCAT(
                ELT(CEILING(RAND() * 10), '張','李','王','劉','陳','楊','黃','趙','周','吳'),
                ELT(CEILING(RAND() * 10), '偉','芳','敏','靜','勇','磊','洋','娟','強','軍')
            ),
            gender,
            birth,
            major_name,
            year_start
        );
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;
-- 執(zhí)行并清理
CALL InsertStudents();
DROP PROCEDURE IF EXISTS InsertStudents;
-- 插入 500 條課程數(shù)據(jù)
DELIMITER $$
CREATE PROCEDURE InsertCourses()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE course_name_prefix VARCHAR(50);
    DECLARE teacher_name VARCHAR(50);
    SET @prefixes = '高等數(shù)學(xué),線性代數(shù),概率統(tǒng)計,C語言程序設(shè)計,Java編程,Python數(shù)據(jù)分析,數(shù)據(jù)結(jié)構(gòu),算法設(shè)計,操作系統(tǒng),'
                    '計算機網(wǎng)絡(luò),數(shù)據(jù)庫原理,軟件工程,電路分析,模擬電子技術(shù),數(shù)字邏輯,信號與系統(tǒng),電磁場與波,自動控制原理,'
                    '大學(xué)物理,大學(xué)化學(xué),馬克思主義基本原理,中國近代史綱要,英語讀寫,體育健康,藝術(shù)鑒賞,心理學(xué)導(dǎo)論,經(jīng)濟學(xué)基礎(chǔ)';
    WHILE i <= 500 DO
        SET course_name_prefix = ELT(CEILING(RAND() * 27),
            '高等數(shù)學(xué)','線性代數(shù)','概率統(tǒng)計','C語言程序設(shè)計','Java編程','Python數(shù)據(jù)分析','數(shù)據(jù)結(jié)構(gòu)','算法設(shè)計','操作系統(tǒng)','計算機網(wǎng)絡(luò)',
            '數(shù)據(jù)庫原理','軟件工程','電路分析','模擬電子技術(shù)','數(shù)字邏輯','信號與系統(tǒng)','電磁場與波','自動控制原理',
            '大學(xué)物理','大學(xué)化學(xué)','馬克思主義基本原理','中國近代史綱要','英語讀寫','體育健康','藝術(shù)鑒賞','心理學(xué)導(dǎo)論','經(jīng)濟學(xué)基礎(chǔ)'
        );
        SET teacher_name = CONCAT(
            ELT(CEILING(RAND() * 10), '張','李','王','劉','陳','楊','黃','趙','周','吳'),
            '老師'
        );
        INSERT INTO `course` (course_no, course_name, credit, teacher_name, course_hours)
        VALUES (
            CONCAT('CS', LPAD(i, 3, '0')),  -- CS001, CS002...
            CONCAT(course_name_prefix, '(', CEILING(RAND() * 10), '期)'),
            CEILING(RAND() * 6),  -- 1~6 學(xué)分
            teacher_name,
            CASE CEILING(RAND() * 5)
                WHEN 1 THEN 32
                WHEN 2 THEN 48
                WHEN 3 THEN 64
                WHEN 4 THEN 80
                ELSE 96
            END
        );
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;
-- 執(zhí)行并清理
CALL InsertCourses();
DROP PROCEDURE IF EXISTS InsertCourses;
-- 插入 500 條不重復(fù)的選課記錄
DELIMITER $$
CREATE PROCEDURE InsertStudentCourse()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE sid INT;
    DECLARE cid INT;
    DECLARE retry_count INT DEFAULT 0;
    DECLARE max_retries INT DEFAULT 2000;
    WHILE i <= 500 DO
        -- 隨機學(xué)生ID：1001 ~ 1500
        SET sid = FLOOR(1001 + RAND() * 500);
        -- 隨機課程ID：101 ~ 600
        SET cid = FLOOR(101 + RAND() * 500);
        -- 嘗試插入，跳過已存在的組合
        BEGIN
            DECLARE CONTINUE HANDLER FOR 1062 BEGIN END; -- 忽略重復(fù)鍵錯誤
            INSERT INTO `student_course` (student_id, course_id, select_time, score, is_valid)
            VALUES (
                sid,
                cid,
                NOW() - INTERVAL FLOOR(RAND() * 365) DAY,  -- 近一年內(nèi)選課
                IF(RAND() > 0.2, ROUND(40 + RAND() * 60, 2), NULL), -- 80% 有成績
                IF(RAND() > 0.1, 1, 0) -- 90% 有效，10% 已退課
            );
            -- 成功插入才計數(shù)
            SET i = i + 1;
        END;
        SET retry_count = retry_count + 1;
        IF retry_count > max_retries THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '插入失敗：可能可用的 (student_id, course_id) 組合已耗盡';
        END IF;
    END WHILE;
END$$
DELIMITER ;
-- 執(zhí)行并清理
CALL InsertStudentCourse();
DROP PROCEDURE IF EXISTS InsertStudentCourse;
-- 檢查每張表的數(shù)據(jù)量
SELECT 'student' AS table_name, COUNT(*) AS count FROM student
UNION ALL
SELECT 'course' AS table_name, COUNT(*) AS count FROM course
UNION ALL
SELECT 'student_course' AS table_name, COUNT(*) AS count FROM student_course;

SELECT [ALL DISTINCT] <字段名> [AS 別名1] [, <字段名2> [AS 別名2]]
FROM <表名1> [AS 表1別名] [, <表名2> [AS 表2別名], ...]
[WHERE <檢索條件>]
[GROUP BY <列名1> [HAVING <條件表達(dá)式>]]
[ORDER BY <列名2> [ASC DESC]];

SELECT [ALL DISTINCT] 字段名1 [AS 別名1], 字段名2 [AS 別名2], ...
FROM 表名1 [AS 表1別名]
[INNER LEFT RIGHT [OUTER] CROSS] JOIN 表名2 [AS 表2別名]
ON 條件;

SELECT s.student_name, c.course_name, sc.score
FROM student s
INNER JOIN student_course sc ON s.student_id = sc.student_id
INNER JOIN course c ON sc.course_id = c.course_id;

SELECT s.student_name, c.course_name, sc.score
FROM student s, student_course sc, course c
WHERE s.student_id = sc.student_id AND  sc.course_id = c.course_id;

SELECT s.student_name, c.course_name
FROM student s
INNER JOIN student_course sc ON s.student_id = sc.student_id
INNER JOIN course c ON sc.course_id = c.course_id
WHERE s.major = '計算機科學(xué)與技術(shù)';

SELECT
	s.major,
	s.student_name,
	c.course_name 
FROM
	student s,
	student_course sc,
	course c 
WHERE
	s.student_id = sc.student_id 
	AND sc.course_id = c.course_id 
	AND s.major = '計算機科學(xué)與技術(shù)';

SELECT s.student_no, s.student_name, s.major, c.course_name, sc.score
FROM student s
LEFT JOIN student_course sc ON s.student_id = sc.student_id
LEFT JOIN course c ON sc.course_id = c.course_id
ORDER BY s.student_id;

SELECT s.student_name, COUNT(sc.course_id) AS select_course_count
FROM student s
LEFT JOIN student_course sc ON s.student_id = sc.student_id
GROUP BY s.student_no, s.student_name
ORDER BY select_course_count DESC;

SELECT c.course_no, c.course_name, c.teacher_name, s.student_name, sc.score
FROM student s
RIGHT JOIN student_course sc ON s.student_id = sc.student_id
RIGHT JOIN course c ON sc.course_id = c.course_id
ORDER BY c.course_id;

SELECT c.course_name, COUNT(sc.student_id) AS student_count
FROM student s
RIGHT JOIN student_course sc ON s.student_id = sc.student_id
RIGHT JOIN course c ON sc.course_id = c.course_id
GROUP BY c.course_id, c.course_name, c.credit
ORDER BY student_count DESC;

SELECT s.student_name, c.course_name
FROM student s
CROSS JOIN course c;

SELECT s.student_name, c.course_name
FROM student s
CROSS JOIN course c
WHERE s.major = '計算機科學(xué)與技術(shù)' AND c.course_name LIKE '%計算機%';

SELECT
  s1.student_name AS 學(xué)生A, 
	s1.student_no 學(xué)號A,
  s2.student_name AS 學(xué)生B, 
	s2.student_no 學(xué)號B,
  s1.major AS 共同專業(yè)
FROM student s1
INNER JOIN student s2 ON s1.major = s2.major
WHERE s1.student_id < s2.student_id;

SELECT
  s1.student_name AS 學(xué)生A, 
	s1.student_no 學(xué)號A,
  s2.student_name AS 學(xué)生B, 
	s2.student_no 學(xué)號B,
  s1.major AS 共同專業(yè)
FROM student s1, student s2
WHERE s1.major = s2.major AND s1.student_id < s2.student_id;

SELECT student_name, student_no
FROM student
WHERE major = (
  SELECT major
  FROM student
  WHERE student_name = '劉娟' AND student_no = 2078009
);

SELECT s.student_name
FROM student s
INNER JOIN student_course sc ON s.student_id = sc.student_id
WHERE sc.course_id IN (
  SELECT course_id
  FROM course
  WHERE course_name LIKE '%數(shù)據(jù)結(jié)構(gòu)%'
);

SELECT 
  temp.course_name, 
  AVG(temp.score) AS avg_score
FROM (
  SELECT s.student_id, s.student_name, c.course_name, sc.score
  FROM student s
  INNER JOIN student_course sc ON s.student_id = sc.student_id
  INNER JOIN course c ON sc.course_id = c.course_id
  WHERE s.major = '計算機科學(xué)與技術(shù)'
) AS temp
GROUP BY temp.course_name
ORDER BY avg_score DESC;

SELECT student_name
FROM student s
WHERE EXISTS (
  SELECT 1
  FROM student_course sc
  WHERE sc.student_id = s.student_id AND sc.score >= 90
);

SELECT student_id, student_name, '計算機專業(yè)學(xué)生' AS type
FROM student
WHERE major = '計算機科學(xué)與技術(shù)'
UNION
SELECT s.student_id, s.student_name, '數(shù)據(jù)庫選課學(xué)生' AS type
FROM student s
INNER JOIN student_course sc ON s.student_id = sc.student_id
INNER JOIN course c ON sc.course_id = c.course_id
WHERE c.course_name LIKE '%數(shù)據(jù)庫原理%';

SELECT s1.student_name
FROM (
  SELECT s.student_id, s.student_name
  FROM student s
  INNER JOIN student_course sc ON s.student_id = sc.student_id
  INNER JOIN course c ON sc.course_id = c.course_id
  WHERE c.course_name LIKE '%數(shù)據(jù)庫%'
) s1
INNER JOIN (
  SELECT s.student_id
  FROM student s
  INNER JOIN student_course sc ON s.student_id = sc.student_id
  INNER JOIN course c ON sc.course_id = c.course_id
  WHERE c.course_name LIKE '%計算機%'
) s2 ON s1.student_id = s2.student_id;

SELECT s.student_name, sc.id AS 選課記錄ID
FROM student_course sc
INNER JOIN student s ON sc.student_id = s.student_id
WHERE sc.score IS NULL
AND NOT EXISTS (
  SELECT 1
  FROM student_course sc2
  WHERE sc2.id = sc.id
    AND sc2.is_valid = 0
);