我的个人博客

MySQL 数据库学习笔记

返回首页
技术分享 2025-12-20

连接MySQL服务器

本地连接

mysql -u root -p

远程连接

mysql -h 主机IP -u 用户名 -p

SSH连接到远程MySQL

ssh 用户名@服务器IP
# 连接成功后再执行
mysql -u root -p

SSH配置（如需远程连接）

编辑SSH配置文件 /etc/ssh/sshd_config：

# 启用SSH端口
Port 22

# 允许root登录
PermitRootLogin yes

# 启用公钥认证
PubkeyAuthentication yes

重启SSH服务：

systemctl restart sshd

创建与维护数据库

1. 创建数据库

基本语法

CREATE DATABASE <数据库名>;

指定字符集

CREATE DATABASE <数据库名> CHARACTER SET <字符集名>;

完整语法

CREATE DATABASE <数据库名> 
[DEFAULT] CHARACTER SET <字符集名> 
[COLLATE <排序规则名>];

示例

-- 创建基本数据库
CREATE DATABASE navicat_db;

-- 创建指定字符集的数据库
CREATE DATABASE sql_db CHARACTER SET gb2312;

-- 创建UTF8MB4字符集的数据库
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2. 查看数据库

查看所有数据库

SHOW DATABASES;

查看数据库创建信息

SHOW CREATE DATABASE <数据库名>;

示例

SHOW CREATE DATABASE sql_db;

3. 修改数据库

修改字符集

ALTER DATABASE <数据库名> CHARACTER SET <字符集名>;

示例

ALTER DATABASE sql_db CHARACTER SET utf8mb4;

4. 使用数据库

USE <数据库名>;

5. 删除数据库

DROP DATABASE <数据库名>;

示例

DROP DATABASE navicat_db;
DROP DATABASE sql_db;

创建与维护数据表

1. 创建数据表

基本语法

CREATE TABLE <表名> (
    字段名 数据类型 [约束条件],
    字段名 数据类型 [约束条件],
    ...
);

完整语法示例

CREATE TABLE course (
    cno CHAR(5) NOT NULL PRIMARY KEY,
    cname VARCHAR(20) NOT NULL,
    credit INT DEFAULT 0,
    description TEXT
);

常用数据类型

类型 说明 示例
INT 整数 age INT
VARCHAR 变长字符串 name VARCHAR(50)
CHAR 定长字符串 code CHAR(10)
TEXT 长文本 content TEXT
DATE 日期 birth DATE
DATETIME 日期时间 created_at DATETIME
DECIMAL 精确小数 price DECIMAL(10,2)
BOOLEAN 布尔值 is_active BOOLEAN

约束类型

约束 说明 示例
PRIMARY KEY 主键 id INT PRIMARY KEY
FOREIGN KEY 外键 user_id INT REFERENCES users(id)
NOT NULL 非空 name VARCHAR(50) NOT NULL
UNIQUE 唯一 email VARCHAR(100) UNIQUE
DEFAULT 默认值 status INT DEFAULT 0
CHECK 检查约束 age INT CHECK(age >= 18)

复杂表示例

CREATE TABLE student (
    sno CHAR(10) NOT NULL PRIMARY KEY,
    sname VARCHAR(20) NOT NULL,
    ssex CHAR(2) DEFAULT '男',
    sage INT CHECK(sage BETWEEN 15 AND 50),
    sdept VARCHAR(30),
    speciality VARCHAR(50),
    enrollment_date DATE,
    FOREIGN KEY (sdept) REFERENCES department(dept_name)
);

CREATE TABLE score (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sno CHAR(10) NOT NULL,
    cno CHAR(5) NOT NULL,
    score DECIMAL(5,2) CHECK(score BETWEEN 0 AND 100),
    exam_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sno) REFERENCES student(sno),
    FOREIGN KEY (cno) REFERENCES course(cno),
    UNIQUE KEY unique_student_course (sno, cno)
);

2. 插入数据

基本语法

INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);

批量插入

INSERT INTO course (cno, cname) VALUES
('C01', '数据库'),
('C02', '数学'),
('C03', '信息系统'),
('C04', '操作系统'),
('C05', '计算机网络');

插入完整记录

INSERT INTO student VALUES 
('2021001', '张三', '男', 20, '计算机', '软件工程', '2021-09-01'),
('2021002', '李四', '女', 19, '数学', '应用数学', '2021-09-01');

3. 修改表结构

添加字段

ALTER TABLE <表名> ADD <字段名> <数据类型> [约束];

示例

-- 添加入学时间字段
ALTER TABLE student ADD enrollment_date DATE;

-- 添加带默认值的字段
ALTER TABLE student ADD status INT DEFAULT 1;

修改字段类型

ALTER TABLE <表名> MODIFY <字段名> <新数据类型> [约束];

示例

-- 修改字段长度
ALTER TABLE student MODIFY sdept VARCHAR(50);

-- 修改字段类型和约束
ALTER TABLE student MODIFY sage INT NOT NULL;

修改字段名

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <数据类型> [约束];

示例

-- 重命名字段
ALTER TABLE student CHANGE sdept sdepartment VARCHAR(30);

-- 同时修改字段名和类型
ALTER TABLE student CHANGE sdepartment dept_name VARCHAR(50) NOT NULL;

删除字段

ALTER TABLE <表名> DROP <字段名>;

示例

-- 删除专业字段
ALTER TABLE student DROP speciality;

重命名表

ALTER TABLE <旧表名> RENAME <新表名>;

示例

-- 重命名表
ALTER TABLE teaching RENAME teach;

-- 或者使用RENAME TABLE语句
RENAME TABLE old_table TO new_table;

4. 删除表

DROP TABLE <表名>;

示例

DROP TABLE teach;

5. 复制表

复制表结构和数据

CREATE TABLE <新表名> AS SELECT * FROM <源表名>;

只复制表结构

CREATE TABLE <新表名> LIKE <源表名>;

示例

-- 完全复制
CREATE TABLE student_backup AS SELECT * FROM student;

-- 只复制结构
CREATE TABLE student_structure LIKE student;

常用操作命令总结

数据库操作

-- 创建数据库
CREATE DATABASE db_name CHARACTER SET utf8mb4;

-- 查看所有数据库
SHOW DATABASES;

-- 使用数据库
USE db_name;

-- 查看当前数据库
SELECT DATABASE();

-- 修改数据库字符集
ALTER DATABASE db_name CHARACTER SET utf8mb4;

-- 删除数据库
DROP DATABASE db_name;

表操作

-- 创建表
CREATE TABLE table_name (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 查看所有表
SHOW TABLES;

-- 查看表结构
DESC table_name;
-- 或者
DESCRIBE table_name;

-- 查看创建表的SQL
SHOW CREATE TABLE table_name;

-- 重命名表
RENAME TABLE old_name TO new_name;

-- 删除表
DROP TABLE table_name;

字段操作

-- 添加字段
ALTER TABLE table_name ADD column_name VARCHAR(100);

-- 修改字段类型
ALTER TABLE table_name MODIFY column_name INT;

-- 修改字段名
ALTER TABLE table_name CHANGE old_name new_name VARCHAR(100);

-- 删除字段
ALTER TABLE table_name DROP column_name;

-- 添加主键
ALTER TABLE table_name ADD PRIMARY KEY (id);

-- 添加外键
ALTER TABLE table_name ADD FOREIGN KEY (user_id) REFERENCES users(id);

数据操作

-- 插入数据
INSERT INTO table_name (col1, col2) VALUES (value1, value2);

-- 查询数据
SELECT * FROM table_name WHERE condition;

-- 更新数据
UPDATE table_name SET col1 = value1 WHERE condition;

-- 删除数据
DELETE FROM table_name WHERE condition;

实用技巧

1. 字符集设置

-- 创建数据库时指定字符集
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 创建表时指定字符集
CREATE TABLE mytable (
    id INT PRIMARY KEY
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2. 索引操作

-- 添加普通索引
ALTER TABLE table_name ADD INDEX index_name (column_name);

-- 添加唯一索引
ALTER TABLE table_name ADD UNIQUE INDEX index_name (column_name);

-- 添加全文索引
ALTER TABLE table_name ADD FULLTEXT INDEX index_name (column_name);

3. 查看表信息

-- 查看表状态
SHOW TABLE STATUS LIKE 'table_name';

-- 查看表的索引
SHOW INDEX FROM table_name;

-- 查看表的列信息
SHOW COLUMNS FROM table_name;

4. 备份与恢复

# 备份数据库
mysqldump -u username -p database_name > backup.sql

# 恢复数据库
mysql -u username -p database_name < backup.sql

视图 (VIEW)

1. 什么是视图

视图是基于SQL语句的结果集的可视化表。视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库表中的字段。

2. 创建视图

基本语法

CREATE VIEW <视图名> AS
SELECT <列名> FROM <表名> [WHERE 条件];

创建复杂视图

CREATE VIEW student_course_view AS
SELECT 
    s.sno,
    s.sname,
    s.sdept,
    c.cno,
    c.cname,
    sc.score
FROM student s
JOIN score sc ON s.sno = sc.sno
JOIN course c ON sc.cno = c.cno;

创建带统计的视图

CREATE VIEW student_score_stats AS
SELECT 
    s.sno,
    s.sname,
    COUNT(sc.cno) AS course_count,
    AVG(sc.score) AS avg_score,
    MAX(sc.score) AS max_score,
    MIN(sc.score) AS min_score
FROM student s
LEFT JOIN score sc ON s.sno = sc.sno
GROUP BY s.sno, s.sname;

3. 查看视图

查看所有视图

SHOW FULL TABLES WHERE table_type = 'VIEW';

查看视图结构

DESC <视图名>;
-- 或者
DESCRIBE <视图名>;

查看视图创建语句

SHOW CREATE VIEW <视图名>;

4. 使用视图

查询视图数据

-- 基本查询
SELECT * FROM student_course_view;

-- 带条件查询
SELECT * FROM student_course_view WHERE sdept = '计算机';

-- 统计查询
SELECT sdept, AVG(score) as avg_score 
FROM student_course_view 
GROUP BY sdept;

5. 修改视图

替换视图

CREATE OR REPLACE VIEW <视图名> AS
SELECT <新查询语句>;

示例

CREATE OR REPLACE VIEW student_course_view AS
SELECT 
    s.sno,
    s.sname,
    s.ssex,
    s.sdept,
    c.cno,
    c.cname,
    c.credit,
    sc.score,
    CASE 
        WHEN sc.score >= 90 THEN '优秀'
        WHEN sc.score >= 80 THEN '良好'
        WHEN sc.score >= 60 THEN '及格'
        ELSE '不及格'
    END AS grade_level
FROM student s
JOIN score sc ON s.sno = sc.sno
JOIN course c ON sc.cno = c.cno;

6. 删除视图

DROP VIEW <视图名>;

7. 视图的优点

  • 简化查询：将复杂的SQL查询封装为简单的视图
  • 安全性：只显示用户需要的数据，隐藏敏感信息
  • 逻辑独立性：表结构改变时，只需修改视图
  • 数据一致性：确保数据的一致性显示SQL 编程

1. 变量声明与使用

用户变量

-- 声明变量
SET @variable_name = value;

-- 使用变量
SELECT @variable_name;

-- 示例
SET @student_id = '2021001';
SELECT * FROM student WHERE sno = @student_id;

局部变量（在存储过程中使用）

DECLARE variable_name data_type [DEFAULT value];

2. 条件语句

IF-ELSE 语句

IF condition THEN
    -- 语句块
ELSEIF condition THEN
    -- 语句块
ELSE
    -- 语句块
END IF;

示例

SET @score = 85;
SET @grade = '';

IF @score >= 90 THEN
    SET @grade = 'A';
ELSEIF @score >= 80 THEN
    SET @grade = 'B';
ELSEIF @score >= 60 THEN
    SET @grade = 'C';
ELSE
    SET @grade = 'D';
END IF;

SELECT @grade AS grade;

CASE 语句

CASE 
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE result_n
END

示例

SELECT 
    sname,
    score,
    CASE 
        WHEN score >= 90 THEN '优秀'
        WHEN score >= 80 THEN '良好'
        WHEN score >= 60 THEN '及格'
        ELSE '不及格'
    END AS grade
FROM student_course_view;

3. 循环语句

WHILE 循环

WHILE condition DO
    -- 循环体
END WHILE;

示例

-- 计算1到10的和
SET @total = 0;
SET @i = 1;

WHILE @i <= 10 DO
    SET @total = @total + @i;
    SET @i = @i + 1;
END WHILE;

SELECT @total AS sum_result;

REPEAT 循环

REPEAT
    -- 循环体
UNTIL condition
END REPEAT;

LOOP 循环

loop_label: LOOP
    -- 循环体
    IF condition THEN
        LEAVE loop_label;
    END IF;
END LOOP loop_label;

4. 存储过程

创建存储过程

DELIMITER //
CREATE PROCEDURE procedure_name ([参数列表])
BEGIN
    -- 存储过程体
END //
DELIMITER ;

参数类型

-- IN 参数（输入参数）
CREATE PROCEDURE get_student(IN student_id CHAR(10))
BEGIN
    SELECT * FROM student WHERE sno = student_id;
END;

-- OUT 参数（输出参数）
CREATE PROCEDURE get_student_count(IN dept_name VARCHAR(30), OUT student_count INT)
BEGIN
    SELECT COUNT(*) INTO student_count 
    FROM student WHERE sdept = dept_name;
END;

-- INOUT 参数（输入输出参数）
CREATE PROCEDURE update_counter(INOUT counter INT, increment_val INT)
BEGIN
    SET counter = counter + increment_val;
END;

调用存储过程

-- 调用带IN参数的存储过程
CALL get_student('2021001');

-- 调用带OUT参数的存储过程
CALL get_student_count('计算机', @count);
SELECT @count;

-- 调用带INOUT参数的存储过程
SET @counter = 10;
CALL update_counter(@counter, 5);
SELECT @counter;

实际示例

DELIMITER //
CREATE PROCEDURE update_student_score(
    IN student_no CHAR(10),
    IN course_no CHAR(5),
    IN new_score DECIMAL(5,2),
    OUT result_message VARCHAR(100)
)
BEGIN
    DECLARE existing_score DECIMAL(5,2) DEFAULT NULL;
    
    -- 检查成绩是否存在
    SELECT score INTO existing_score 
    FROM score 
    WHERE sno = student_no AND cno = course_no;
    
    IF existing_score IS NOT NULL THEN
        -- 更新成绩
        UPDATE score SET score = new_score 
        WHERE sno = student_no AND cno = course_no;
        SET result_message = '成绩更新成功';
    ELSE
        -- 插入新成绩
        INSERT INTO score (sno, cno, score) 
        VALUES (student_no, course_no, new_score);
        SET result_message = '成绩插入成功';
    END IF;
END //
DELIMITER ;

5. 函数

创建函数

DELIMITER //
CREATE FUNCTION function_name ([参数列表]) RETURNS return_type
BEGIN
    -- 函数体
    RETURN return_value;
END //
DELIMITER ;

示例

DELIMITER //
CREATE FUNCTION calculate_grade(score DECIMAL(5,2)) RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
    DECLARE grade VARCHAR(10);
    
    IF score >= 90 THEN
        SET grade = 'A';
    ELSEIF score >= 80 THEN
        SET grade = 'B';
    ELSEIF score >= 60 THEN
        SET grade = 'C';
    ELSE
        SET grade = 'D';
    END IF;
    
    RETURN grade;
END //
DELIMITER ;

使用函数

SELECT 
    sno,
    cno,
    score,
    calculate_grade(score) AS grade
FROM score;

更多函数示例

-- 计算GPA的函数
DELIMITER //
CREATE FUNCTION calculate_gpa(student_id CHAR(10)) RETURNS DECIMAL(3,2)
DETERMINISTIC
BEGIN
    DECLARE gpa DECIMAL(3,2) DEFAULT 0.00;
    
    SELECT AVG(score)/20 INTO gpa
    FROM score 
    WHERE sno = student_id;
    
    RETURN gpa;
END //
DELIMITER ;

-- 字符串处理函数
DELIMITER //
CREATE FUNCTION format_student_name(full_name VARCHAR(50)) RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
    DECLARE formatted_name VARCHAR(50);
    
    -- 将姓名格式化为：姓 名（首字母大写）
    SET formatted_name = CONCAT(
        UPPER(LEFT(full_name, 1)), 
        LOWER(SUBSTRING(full_name, 2))
    );
    
    RETURN formatted_name;
END //
DELIMITER ;

6. 触发器

创建触发器

DELIMITER //
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- 触发器逻辑
END //
DELIMITER ;

示例

-- 插入前触发器：自动设置创建时间
DELIMITER //
CREATE TRIGGER before_student_insert
BEFORE INSERT ON student
FOR EACH ROW
BEGIN
    IF NEW.enrollment_date IS NULL THEN
        SET NEW.enrollment_date = CURRENT_DATE();
    END IF;
END //
DELIMITER ;

-- 更新后触发器：记录修改日志
DELIMITER //
CREATE TRIGGER after_student_update
AFTER UPDATE ON student
FOR EACH ROW
BEGIN
    INSERT INTO student_log (
        student_id, 
        old_name, 
        new_name, 
        operation, 
        log_time
    ) VALUES (
        NEW.sno,
        OLD.sname,
        NEW.sname,
        'UPDATE',
        NOW()
    );
END //
DELIMITER ;

-- 删除前触发器：检查约束
DELIMITER //
CREATE TRIGGER before_student_delete
BEFORE DELETE ON student
FOR EACH ROW
BEGIN
    DECLARE score_count INT;
    
    -- 检查学生是否有成绩记录
    SELECT COUNT(*) INTO score_count
    FROM score WHERE sno = OLD.sno;
    
    IF score_count > 0 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '无法删除有成绩记录的学生';
    END IF;
END //
DELIMITER ;

7. 游标

游标基本使用

DELIMITER //
CREATE PROCEDURE cursor_example()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE student_id CHAR(10);
    DECLARE student_name VARCHAR(20);
    DECLARE avg_score DECIMAL(5,2);
    
    -- 声明游标
    DECLARE student_cursor CURSOR FOR 
        SELECT sno, sname FROM student;
    
    -- 声明异常处理
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 创建临时表存储结果
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_results (
        student_id CHAR(10),
        student_name VARCHAR(20),
        avg_score DECIMAL(5,2)
    );
    
    -- 打开游标
    OPEN student_cursor;
    
    read_loop: LOOP
        -- 读取游标数据
        FETCH student_cursor INTO student_id, student_name;
        
        -- 退出条件
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 计算平均成绩
        SELECT AVG(score) INTO avg_score 
        FROM score 
        WHERE sno = student_id;
        
        -- 插入结果
        INSERT INTO temp_results VALUES 
        (student_id, student_name, IFNULL(avg_score, 0));
        
    END LOOP;
    
    -- 关闭游标
    CLOSE student_cursor;
    
    -- 返回结果
    SELECT * FROM temp_results;
    
    -- 清理临时表
    DROP TEMPORARY TABLE IF EXISTS temp_results;
    
END //
DELIMITER ;

8. 事务处理

事务基本语法

-- 开始事务
START TRANSACTION;

-- 执行SQL语句
UPDATE account SET balance = balance - 100 WHERE user_id = 1;
UPDATE account SET balance = balance + 100 WHERE user_id = 2;

-- 提交事务或回滚
COMMIT;  -- 提交
-- 或者
ROLLBACK; -- 回滚

事务示例

DELIMITER //
CREATE PROCEDURE transfer_money(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL(10,2),
    OUT result_message VARCHAR(100)
)
BEGIN
    DECLARE from_balance DECIMAL(10,2);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET result_message = '转账失败，已回滚';
    END;
    
    -- 开始事务
    START TRANSACTION;
    
    -- 检查余额
    SELECT balance INTO from_balance 
    FROM account 
    WHERE account_id = from_account;
    
    IF from_balance < amount THEN
        SET result_message = '余额不足';
        ROLLBACK;
    ELSE
        -- 扣款
        UPDATE account 
        SET balance = balance - amount 
        WHERE account_id = from_account;
        
        -- 入账
        UPDATE account 
        SET balance = balance + amount 
        WHERE account_id = to_account;
        
        -- 记录交易日志
        INSERT INTO transaction_log (
            from_account, 
            to_account, 
            amount, 
            transaction_time
        ) VALUES (
            from_account, 
            to_account, 
            amount, 
            NOW()
        );
        
        COMMIT;
        SET result_message = '转账成功';
    END IF;
END //
DELIMITER ;

9. 错误处理

DECLARE HANDLER 语法

DECLARE handler_type HANDLER 
FOR condition_value [, condition_value] ...
statement

-- handler_type:
--   CONTINUE: 继续执行
--   EXIT: 退出执行
--   UNDO: 回滚（事务中使用）

-- condition_value:
--   SQLSTATE 'VALUE'
--   SQLWARNING
--   NOT FOUND
--   SQLEXCEPTION

错误处理示例

DELIMITER //
CREATE PROCEDURE safe_divide(
    IN dividend DECIMAL(10,2),
    IN divisor DECIMAL(10,2),
    OUT result DECIMAL(10,2),
    OUT error_message VARCHAR(100)
)
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1
        error_message = MESSAGE_TEXT;
    END;
    
    IF divisor = 0 THEN
        SET error_message = '除数不能为零';
        SET result = NULL;
    ELSE
        SET result = dividend / divisor;
        SET error_message = '计算成功';
    END IF;
END //
DELIMITER ;