SQL练习代码（第二篇）

📚 SQL练习代码（第二篇）

• 🔁 文章原始同步：微信搜索公众号：sky wide 技术
• 📖 QQ技术群：308191819
• 欢迎各位加入
  该篇文章主要是将博主在学习sql时候的代码进行讲解，提供给小白入门使用。

-- 1. 创建数据库并设置字符集
CREATE DATABASE IF NOT EXISTS collage;
USE collage;
ALTER DATABASE collage DEFAULT CHARACTER SET gbk;

-- 检查字符集和排序规则设置
SHOW VARIABLES LIKE '%character%';
SHOW VARIABLES LIKE 'collation_%';

-- 2. 禁用外键检查以进行表创建
SET FOREIGN_KEY_CHECKS=0;

-- 3. 创建表
-- a. 部门表
CREATE TABLE Department (
    Did CHAR(5) PRIMARY KEY,            -- 部门编号，主键
    Dname VARCHAR(30) NOT NULL UNIQUE,  -- 部门名称，唯一约束
    Dheader CHAR(5),                     -- 部门负责人
    CONSTRAINT fk_dheader FOREIGN KEY (Dheader) REFERENCES Teacher (Tid)  -- 外键约束
) DEFAULT CHARSET=gbk;

-- b. 教师表
CREATE TABLE Teacher (
    Tid CHAR(5) PRIMARY KEY,            -- 教师编号，主键
    Tname VARCHAR(10) NOT NULL,         -- 教师姓名
    Tsex ENUM('男','女'),                -- 教师性别
    Tprof ENUM('助教','讲师','副教授','教授'),  -- 教师职称
    Tdept CHAR(3),                       -- 教师所属部门
    Sal DECIMAL(5,1),                   -- 工资
    Comm DECIMAL(5,1),                  -- 佣金
    CONSTRAINT fk_tdept FOREIGN KEY(Tdept) REFERENCES Department(Did)  -- 外键约束
) DEFAULT CHARSET=gbk;

-- c. 学生表
CREATE TABLE Student (
    Sid CHAR(5) PRIMARY KEY,            -- 学生编号，主键
    Sname VARCHAR(10) NOT NULL,         -- 学生姓名
    Ssex ENUM('男','女') DEFAULT '男',   -- 学生性别，默认值为 '男'
    Sbirth DATE,                        -- 学生出生日期
    Sdept CHAR(3),                      -- 学生所属部门
    CONSTRAINT fk_sdept FOREIGN KEY(Sdept) REFERENCES Department(Did)  -- 外键约束
) DEFAULT CHARSET=gbk;

-- d. 课程表
CREATE TABLE Course (
    Cid CHAR(5) PRIMARY KEY,            -- 课程编号，主键
    Cname VARCHAR(20) NOT NULL,         -- 课程名称
    Ccredit INT,                        -- 学分
    Cpno CHAR(3),                       -- 先修课程编号
    Climit INT DEFAULT 60               -- 课程人数上限，默认值 60
) DEFAULT CHARSET=gbk;

-- e. 教师-课程关系表
CREATE TABLE Tc (
    Tid CHAR(4),                        -- 教师编号
    Cid CHAR(3),                        -- 课程编号
    PRIMARY KEY(Tid, Cid),              -- 主键（教师编号，课程编号）
    CONSTRAINT fk_tid FOREIGN KEY(Tid) REFERENCES Teacher(Tid),  -- 外键约束
    CONSTRAINT fk_cid FOREIGN KEY(Cid) REFERENCES Course(Cid)    -- 外键约束
) DEFAULT CHARSET=gbk;

-- f. 学生-课程关系表
CREATE TABLE Sc (
    Sid CHAR(4),                        -- 学生编号
    Cid CHAR(3),                        -- 课程编号
    Grade DECIMAL(5,1),                 -- 成绩
    PRIMARY KEY(Sid, Cid),              -- 主键（学生编号，课程编号）
    CONSTRAINT fk_sid FOREIGN KEY(Sid) REFERENCES Student(Sid),  -- 外键约束
    CONSTRAINT fk_cid2 FOREIGN KEY(Cid) REFERENCES Course(Cid)  -- 外键约束
) DEFAULT CHARSET=gbk;

-- 4. 显示表和描述
SHOW TABLES;                           -- 显示所有表
DESC Course;                           -- 显示课程表结构

-- 5. 创建显示列的存储过程
DELIMITER //

CREATE PROCEDURE ShowColumns()
BEGIN
    DECLARE tableArray VARCHAR(255);
    DECLARE tables VARCHAR(255) DEFAULT 'course,department,sc,student,tc,teacher';
    DECLARE currentPosition INT DEFAULT 1;

    WHILE currentPosition > 0 DO
        SET @nextComma = LOCATE(',', tables, currentPosition);
        
        IF @nextComma = 0 THEN
            SET tableArray = SUBSTRING(tables, currentPosition);
            SET currentPosition = 0; -- 到达表名的末尾
        ELSE
            SET tableArray = SUBSTRING(tables, currentPosition, @nextComma - currentPosition);
            SET currentPosition = @nextComma + 1;
        END IF;

        IF tableArray != '' THEN
            SET @cmd = CONCAT('SHOW COLUMNS FROM ', tableArray);
            PREPARE stmt FROM @cmd;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END IF;
    END WHILE;
END //

DELIMITER ;

-- 调用存储过程显示列
CALL ShowColumns();

-- 使用完后删除存储过程
DROP PROCEDURE ShowColumns;

-- 6. 其他操作
SHOW DATABASES;                       -- 显示所有数据库
USE collage;                          -- 使用 collage 数据库
SHOW TABLES;                         -- 显示数据库中的所有表

-- 对 `ac` 表的示例操作（似乎重命名为 `sc`）
USE collage;                         -- 再次使用数据库
DESC ac;                             -- 显示 `ac` 表结构

-- 示例表操作
ALTER TABLE ac DROP i;              -- 删除某一列（如果存在，否则会报错）
ALTER TABLE ac DROP COLUMN birthdate; -- 删除已存在的列
ALTER TABLE ac ADD COLUMN birthdate DATE; -- 添加新列

-- 将表重命名从 `ac` 为 `sc`
ALTER TABLE ac RENAME TO sc;