一些基础的SQL指令 - Yourlai's BlogYourlai's Blog

用户与权限管理

1. 用户创建

-- 创建用户（第三章 3.3节）
CREATE USER 'wulian'@'sakila' IDENTIFIED BY '1234';
-- 创建只能在'sakila'主机上登录的用户wulian，密码为1234

2. 授予权限

-- 授予权限（第三章 3.3节）
GRANT SELECT, UPDATE, DELETE ON sakila.* TO wulian@sakila;
-- 授予wulian用户在sakila数据库所有表上的SELECT、UPDATE、DELETE权限

3. 撤销权限

-- 撤销权限（第三章 3.3节）
REVOKE SELECT, DELETE ON sakila.* FROM 'wulian'@'sakila';
-- 撤销wulian用户在sakila数据库所有表上的SELECT、DELETE权限

基本查看指令

1. 查看数据库信息

-- 查看所有数据库（第三章 3.3节）
SHOW DATABASES;

-- 查看特定数据库中的表（第三章 3.3节）
SHOW TABLES;

-- 查看表的创建语句（第三章 3.3节）
SHOW CREATE TABLE actor;

-- 查看当前字符集设置（第三章 3.3节）
SHOW VARIABLES LIKE 'character%';

2. 查看表结构

-- 查看表结构：两种等价写法（第四章 4.2节）
DESCRIBE student;  或  DESC student;
-- 查看student表的字段名、类型、约束等信息

3. 查看视图

-- 查看视图结构（第四章 4.4节）
DESC stu_view;

创建表与视图

1. 创建表（含完整约束）

-- 创建带有各种约束的学生表（第四章 4.1-4.2节）
CREATE TABLE student (
    -- 自增主键（表级约束也可在最后定义）
    snum INT AUTO_INCREMENT PRIMARY KEY,
    
    -- 学号：非空且唯一
    s_no CHAR(12) NOT NULL UNIQUE,
    
    -- 姓名：非空约束
    s_name VARCHAR(8) NOT NULL,
    
    -- 性别：枚举类型（隐含检查约束）
    gender ENUM('男', '女'),
    
    -- 出生日期
    DOB DATE,
    
    -- 入学成绩：检查约束（必须>500）
    score INT CHECK (score > 500),
    
    -- 电话：唯一约束
    phone VARCHAR(15) UNIQUE,
    
    -- 邮箱
    mail VARCHAR(20),
    
    -- 表级约束示例（为约束命名）
    CONSTRAINT ck_score CHECK (score > 500),
    CONSTRAINT uk_sno UNIQUE (s_no),
    CONSTRAINT uk_phone UNIQUE (phone)
);

-- 简化写法，将约束放在列定义中
CREATE TABLE student(
    snum INT AUTO_INCREMENT PRIMARY KEY,
    s_no CHAR(12) NOT NULL UNIQUE,
    s_name VARCHAR(8) NOT NULL,
    gender ENUM('男', '女'),
    DOB DATE,
    score INT CHECK(score>500),
    phone VARCHAR(15) UNIQUE,
    mail VARCHAR(20)
);

2. 创建外键约束

-- 创建课程表（第四章 4.1节） 
CREATE TABLE course(
    course_id CHAR(10) PRIMARY KEY,
    course_name VARCHAR(50),
    teacher_id INT,
    -- 表级外键约束（只能定义为表级约束）
    CONSTRAINT fk_teacher FOREIGN KEY (teacher_id) 
    REFERENCES teacher(teacher_id)
);

3. 创建视图

-- 创建简单的行列子集视图（可更新）（第四章 4.4节）
CREATE VIEW stu_view AS 
SELECT s_name, s_no, gender 
FROM student;

-- 创建带条件的视图（第四章 4.4节）
CREATE VIEW male_stu AS
SELECT s_name, s_no 
FROM student 
WHERE gender = '男';

-- 使用WITH CHECK OPTION（第四章 4.4节）
CREATE VIEW high_score_stu AS
SELECT s_no, s_name, score
FROM student
WHERE score > 600
WITH CHECK OPTION;  -- 保证插入/更新的数据必须满足score>600

SELECT查询（复杂示例）

1. 基础查询与虚列

-- 查询学号、姓名和年龄（虚列）（第五章 5.1节）
SELECT s_no, s_name, 
       YEAR(CURDATE()) - YEAR(DOB) AS age  -- 年龄是计算列
FROM student;

-- 使用DISTINCT去重（第五章 5.1节）
SELECT DISTINCT gender FROM student;

-- 排序和限制结果数量（第五章 5.1节）
SELECT s_no, s_name, score
FROM student
ORDER BY score DESC  -- 按成绩降序排列
LIMIT 5;             -- 只显示前5名

2. 分组与聚合函数

-- 按性别分组，统计人数和平均成绩（第五章 5.1节）
SELECT gender, 
       COUNT(*) AS stu_count, 
       AVG(score) AS avg_score
FROM student
GROUP BY gender
HAVING AVG(score) > 550;  -- 只显示平均分>550的组

3. 多表连接查询

-- 内连接（第五章 5.2节）
SELECT stu.s_name, stu.s_no, sc.course_name, sc.score
FROM student stu
INNER JOIN stu_score sc ON stu.s_no = sc.s_no
WHERE sc.course_name = '自动控制原理';

-- 左外连接（第五章 5.2节）
SELECT stu.s_name, sc.course_name, sc.score
FROM student stu
LEFT JOIN stu_score sc ON stu.s_no = sc.s_no;
-- 显示所有学生，即使他们没有成绩记录

-- 自然连接（自动匹配相同列名）
SELECT * FROM student NATURAL JOIN stu_score;

4. 嵌套查询

-- 使用IN的子查询（第五章 5.3节）
SELECT s_name, s_no
FROM student
WHERE s_no IN (
    SELECT s_no 
    FROM stu_score 
    WHERE score > 90
);

-- 使用比较运算符的子查询（第五章 5.3节）
SELECT s_name, score
FROM student
WHERE score > (
    SELECT AVG(score) 
    FROM student
);

-- 相关子查询（使用EXISTS）（第五章 5.3节）
SELECT s_name
FROM student stu
WHERE EXISTS (
    SELECT 1 
    FROM stu_score sc 
    WHERE sc.s_no = stu.s_no 
    AND sc.score > 85
);

5. 集合查询

-- 并集（UNION）（第五章 5.4节）
SELECT s_no FROM student WHERE gender = '男'
UNION
SELECT s_no FROM student WHERE score > 600;

表中数据的增删改

1. 插入数据

-- 标准插入（第三章 3.1节）
INSERT INTO student (s_no, s_name, gender, score)
VALUES ('202101100011', '张三', '男', 620);

-- 省略列名插入（需提供所有列值）
INSERT INTO student 
VALUES (NULL, '202101100012', '李四', '女', '2003-05-30', 580, '13800138000', NULL);

-- 使用SET插入（第四章 4.3节）
INSERT INTO student 
SET s_no = '202101100013', s_name = '王五', gender = '男', score = 650;

-- 批量从文件导入（第四章 4.3节）
LOAD DATA LOCAL INFILE 'D:\\testdata\\stu.txt' 
INTO TABLE student
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY ';'
(s_no, s_name, gender, DOB);

2. 更新数据

-- 更新单条记录（必须使用主键或唯一标识）
UPDATE student 
SET score = score + 10, phone = '13900139000'
WHERE s_no = '202101100011';  -- 基于主键条件

-- 批量更新符合条件的记录
UPDATE student 
SET score = score * 1.05 
WHERE score < 550;

-- 更新时使用子查询
UPDATE stu_score sc
SET sc.score = sc.score + 5
WHERE sc.s_no IN (
    SELECT s_no 
    FROM student 
    WHERE gender = '女'
);

3. 删除数据

-- 删除单条记录（第三章 3.1节）
DELETE FROM student 
WHERE s_no = '202101100011';

-- 删除所有记录
DELETE FROM student;

-- 删除多个符合条件的记录
DELETE FROM student 
WHERE score < 500 OR phone IS NULL;

-- 删除与其他表相关的记录（使用子查询）
DELETE FROM student 
WHERE s_no IN (
    SELECT s_no 
    FROM stu_score 
    WHERE course_name = '已取消课程'
);

视图中数据的修改

-- 注意：只有行列子集视图才可以更新数据！

-- 1. 更新视图数据（实际上更新原表）
UPDATE stu_view 
SET gender = '女' 
WHERE s_no = '202101100011';

-- 2. 向视图插入数据（第四章 4.4节）
INSERT INTO male_stu (s_no, s_name)
VALUES ('202101100014', '赵六');
-- 注意：如果原表有gender字段，这里未指定，可能会插入NULL

-- 3. WITH CHECK OPTION的效果（第四章 4.4节）
-- 假设有视图 high_score_stu (score > 600 WITH CHECK OPTION)
INSERT INTO high_score_stu VALUES ('202101100015', '孙七', 590);
-- 这里会失败！因为590不满足score>600的条件

-- 4. 删除视图中的数据
DELETE FROM stu_view 
WHERE s_name = '张三';
-- 这会从原表student中删除对应的记录

自定义函数与存储过程

1. 创建并调用函数

-- 计算长方形面积的函数（第六章 6.2节）
DELIMITER $$
CREATE FUNCTION calc_area(length INT, width INT)
RETURNS INT
BEGIN
    RETURN length * width;
END$$
DELIMITER ;

-- 调用函数
SELECT calc_area(3, 4) AS area;  -- 返回12

-- 带条件判断的函数（第六章 6.3节）
DELIMITER $$
CREATE FUNCTION t_score(score INT) 
RETURNS CHAR(15)
BEGIN
    DECLARE grade CHAR(15);
    IF score >= 85 THEN 
        SET grade = 'EXCELLENT';
    ELSE 
        SET grade = 'PASS';
    END IF;
    RETURN grade;
END$$
DELIMITER ;

SELECT s_name, t_score(score) AS grade FROM student;

2. 创建并调用存储过程

-- 简单的存储过程（第七章 7.1节）
DELIMITER $$
CREATE PROCEDURE get_student_info(IN stu_no CHAR(12))
BEGIN
    SELECT s_name, gender, score 
    FROM student 
    WHERE s_no = stu_no;
END$$
DELIMITER ;

-- 调用存储过程
CALL get_student_info('202101100011');

-- 带输入输出参数的存储过程（第七章 7.1节）[4](@context-ref?id=0)
DELIMITER $$
CREATE PROCEDURE count_by_grade(
    IN grade_year INT, 
    OUT stu_count INT
)
BEGIN
    SELECT COUNT(*) INTO stu_count
    FROM student
    WHERE s_no LIKE CONCAT(grade_year, '%');
END$$
DELIMITER ;

-- 调用并获取输出参数
CALL count_by_grade(2021, @count);
SELECT @count AS '2021级学生数';

触发器

1. 创建触发器

-- 删除数据时触发的触发器（第七章 7.2节）
DELIMITER $$
CREATE TRIGGER del_trigger
BEFORE DELETE ON student
FOR EACH ROW
BEGIN
    -- 在删除前将被删信息记录到日志表
    INSERT INTO delete_log(deleted_s_no, deleted_at)
    VALUES (OLD.s_no, NOW());
END$$
DELIMITER ;

-- 插入数据时触发的触发器（第七章 7.2节）
DELIMITER $$
CREATE TRIGGER insert_trigger
AFTER INSERT ON student
FOR EACH ROW
BEGIN
    -- 自动初始化相关表中的记录
    INSERT INTO stu_score(s_no) VALUES (NEW.s_no);
END$$
DELIMITER ;

-- 带条件检查的UPDATE触发器（第七章 7.2节）
DELIMITER $$
CREATE TRIGGER update_score_trigger
BEFORE UPDATE ON student
FOR EACH ROW
BEGIN
    IF NEW.score < 0 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '成绩不能为负数！';
    END IF;
END$$
DELIMITER ;

2. 查看和管理触发器

-- 查看所有触发器
SHOW TRIGGERS;

-- 删除触发器
DROP TRIGGER del_trigger;

计划事件

1. 创建计划事件

-- 一次性事件：5秒后执行（第七章 7.3节）
CREATE EVENT one_time_event
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 SECOND
DO
    INSERT INTO event_log(event_name, executed_at) 
    VALUES ('一次性事件', NOW());

-- 周期性事件：每天凌晨1点执行（第七章 7.3节）
CREATE EVENT daily_cleanup
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_DATE + INTERVAL 1 HOUR
DO
    -- 清理临时数据
    DELETE FROM temp_table WHERE created_at < DATE_SUB(NOW(), INTERVAL 7 DAY);

-- 指定结束时间的周期性事件（第七章 7.3节）
CREATE EVENT weekly_report
ON SCHEDULE EVERY 1 WEEK
STARTS CURDATE() + INTERVAL 1 WEEK
ENDS '2026-08-31 12:00:00'
DO
    -- 生成周报
    CALL generate_weekly_report();

2. 带语句块的事件

DELIMITER $$
CREATE EVENT batch_process
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 10 SECOND
DO
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE stu_no CHAR(12);
    DECLARE cur CURSOR FOR SELECT s_no FROM student WHERE score IS NULL;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO stu_no;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 为成绩为空的学生设置默认成绩
        UPDATE student SET score = 500 WHERE s_no = stu_no;
    END LOOP;
    CLOSE cur;
END$$
DELIMITER ;

3. 管理事件

-- 查看所有事件
SHOW EVENTS;

-- 启用/禁用事件
ALTER EVENT daily_cleanup DISABLE;
ALTER EVENT daily_cleanup ENABLE;

-- 删除事件
DROP EVENT daily_cleanup;

高级示例：综合运用

1. 使用游标的存储过程

-- 计算学生平均成绩并更新等级的存储过程（第七章 7.4节）
DELIMITER $$
CREATE PROCEDURE update_all_grades()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE v_s_no CHAR(12);
    DECLARE v_score INT;
    DECLARE cur CURSOR FOR SELECT s_no, score FROM student;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO v_s_no, v_score;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 根据成绩更新等级
        IF v_score >= 90 THEN
            UPDATE student SET grade = 'A' WHERE s_no = v_s_no;
        ELSEIF v_score >= 80 THEN
            UPDATE student SET grade = 'B' WHERE s_no = v_s_no;
        ELSEIF v_score >= 70 THEN
            UPDATE student SET grade = 'C' WHERE s_no = v_s_no;
        ELSE
            UPDATE student SET grade = 'D' WHERE s_no = v_s_no;
        END IF;
    END LOOP;
    
    CLOSE cur;
END$$
DELIMITER ;

CALL update_all_grades();

2. 带错误处理的事务

-- 转账操作（原子性事务）（第九章 9.1节）
DELIMITER $$
CREATE PROCEDURE transfer_money(
    IN from_account CHAR(20),
    IN to_account CHAR(20),
    IN amount DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '转账失败！';
    END;
    
    START TRANSACTION;
    
    -- 检查余额是否充足
    IF (SELECT balance FROM accounts WHERE account_no = from_account) < amount THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '余额不足！';
    END IF;
    
    -- 扣除转出账户余额
    UPDATE accounts SET balance = balance - amount 
    WHERE account_no = from_account;
    
    -- 增加转入账户余额
    UPDATE accounts SET balance = balance + amount 
    WHERE account_no = to_account;
    
    -- 记录交易日志
    INSERT INTO transfer_log(from_account, to_account, amount, trans_time)
    VALUES (from_account, to_account, amount, NOW());
    
    COMMIT;
END$$
DELIMITER ;

要点总结

需要特别注意的区别：

函数 vs 存储过程
- 函数必须有返回值，存储过程可以没有
- 函数用SELECT调用，存储过程用CALL调用
视图更新条件
- 必须是行列子集视图（单表、原始列、无计算）
- 不能使用聚合函数、DISTINCT、GROUP BY等
事务的类型
- 显式事务：START TRANSACTION + COMMIT/ROLLBACK
- 隐式事务：SET autocommit=0 + COMMIT/ROLLBACK
- 自动提交：SET autocommit=1（默认）
锁机制
- 表级锁：LOCK TABLE ... READ/WRITE
- 行级锁：SELECT ... LOCK IN SHARE MODE / FOR UPDATE
约束类型
- 行级约束：列定义时直接添加
- 表级约束：所有列定义完成后添加
- 外键只能定义为表级约束