Py学习  »  DATABASE

MySQL 教程:颠覆你对 MySQL 的认知(全)

Linux就该这么学 • 1 月前 • 85 次点击  

MySQL教程:全面理论与实践指南(全)

图片

一、MySQL简介与理论基础

MySQL是世界上最流行的开源关系型数据库管理系统之一,广泛应用于网站、应用程序和企业级系统。它采用客户端/服务器架构,支持多用户环境,并基于SQL(结构化查询语言)标准。

关系型数据库核心概念

  1. 1. 关系模型:数据以表格(二维表)形式存储,表之间通过关系连接
  2. 2. ACID特性
  • • 原子性(Atomicity):事务中的操作要么全部完成,要么全部不完成
  • • 一致性(Consistency):事务执行前后,数据库从一个一致状态变到另一个一致状态
  • • 隔离性(Isolation):并发执行的事务之间不会互相影响
  • • 持久性(Durability):事务一旦提交,其结果将永久保存

MySQL架构

MySQL采用多层架构设计:

  1. 1. 连接层:处理客户端连接请求
  2. 2. 服务层:包括查询解析、优化和缓存
  3. 3. 存储引擎层:负责数据的存储和提取
  4. 4. 文件系统层:将数据持久化到磁盘

存储引擎

MySQL支持多种存储引擎,每种都有特定的特性和用途:

  • • InnoDB:默认存储引擎,支持事务、外键和行级锁
    `CREATE TABLE example (id INT) ENGINE=InnoDB;`



  • • MyISAM:适合读密集型应用,支持全文索引
    `CREATE TABLE logs (id INT, message TEXT) ENGINE=MyISAM;`


  • • Memory:将数据存储在内存中,速度极快但不持久
    
    
        
    `CREATE TABLE temp_data (id INT) ENGINE=MEMORY;`


  • • Archive:适合存储和检索大量很少被查询的历史数据
    `CREATE TABLE old_logs (id INT, log_text TEXT) ENGINE=ARCHIVE;`


MySQL数据类型

  1. 1. 数值类型
  • • INT:整数类型,4字节
  • • TINYINT:小整数,1字节
  • • BIGINT:大整数,8字节
  • • FLOAT/DOUBLE:浮点数
  • • DECIMAL:精确小数
  • 2. 字符串类型
    • • CHAR(n):固定长度字符串
    • • VARCHAR(n):可变长度字符串
    • • TEXT:长文本
  • 3.  日期和时间类型
    • • DATE:日期,格式'YYYY-MM-DD'
    • • TIME:时间,格式'HH:MM:SS'
    • • DATETIME:日期和时间,格式'YYYY-MM-DD HH:MM:SS'
    • • TIMESTAMP:时间戳
  • 4. 其他类型
    • • ENUM:枚举类型
    • • SET:集合类型
    • • BLOB:二进制大对象

    二、数据库和表的基本操作

    数据库操作语法详解

    -- 创建数据库
    CREATE DATABASE [IF NOTEXISTS] mydb 
    [CHARACTER SET charset_name] 
    [COLLATE collation_name];

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

    -- 使用数据库
    USE mydb;

    -- 删除数据库
    DROP DATABASE [IF EXISTS] mydb;

    表操作语法详解

    
    
    
        
    -- 创建表
    CREATE TABLE students (
        id INT AUTO_INCREMENT PRIMARY KEY,  -- 主键,自动递增
        name VARCHAR(50NOT NULL,          -- NOT NULL约束
        age INTCHECK (age >0),            -- CHECK约束
        gender ENUM('男''女'),            -- 枚举类型
        class VARCHAR(20),
        score FLOATDEFAULT0,              -- 默认值
        created_at TIMESTAMPDEFAULTCURRENT_TIMESTAMP,  -- 自动记录创建时间
        INDEX idx_class (class)             -- 索引
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

    -- 查看表结构
    DESCRIBE students;
    -- 或
    SHOW COLUMNS FROM students;

    -- 修改表结构
    ALTER TABLE students ADDCOLUMN email VARCHAR(100);
    ALTER TABLE students MODIFY COLUMN name VARCHAR(100);
    ALTER TABLE students DROPCOLUMN email;

    -- 删除表
    DROPTABLE [IF EXISTS] students;

    三、增:INSERT 插入数据

    插入语法详解

    基本语法:

    INSERT INTO table_name [(column1, column2, ...)]
    VALUES (value1, value2, ...)[, (value1, value2, ...), ...];

    插入单条数据

    INSERT INTO students (name, age, gender, class, score) 
    VALUES ('张三'20'男''计算机科学1班'89.5);

    插入多条数据

    INSERT INTO students (name, age, gender, class, score) VALUES
    ('李四'19'男''计算机科学1班'76.0),
    ('王五'21'男''计算机科学2班'92.5 ),
    ('赵六'20'女''计算机科学2班'85.0),
    ('钱七'22'女''计算机科学1班'79.5);

    INSERT的高级用法

    -- 插入或更新(如果主键存在则更新)
    INSERT INTO students (id, name, score)
    VALUES (1'张三'95)
    ON DUPLICATE KEY UPDATE score =95;

    -- 从其他表插入数据
    INSERT INTO students_backup
    SELECT*FROM students WHERE class ='计算机科学1班';

    -- 忽略错误继续执行
    INSERT IGNORE INTO students (id, name, age)
    VALUES (1'张三'20);

    实际应用场景

    当新学生入学时,需要将学生信息录入系统:

    -- 新学期开始,添加一批新生
    INSERT INTO students (name, age, gender, class, score) VALUES
    ('刘备'18'男''计算机1班'NULL),  -- 新生还没有成绩
    ('关羽'19'男''计算机1班'NULL),
    ('张飞'18'男''计算机1班'NULL);

    四、查:SELECT 查询数据

    SELECT语法详解

    基本语法:

    SELECT [DISTINCT] column1, column2, ...
    FROM table_name
    [JOIN table_name2 ON join_condition]
    [WHEREcondition]
    [GROUPBYcolumn(s)]
    [HAVING group_condition]
    [ORDERBYcolumn(s) [ASC|DESC]]
    [LIMIT offset, row_count];

    查询所有记录

    `SELECT*FROM students;`



    查询特定列

    `SELECT name, age, score FROM students;`



    条件查询与WHERE子句详解 

    -- 查询计算机科学1班的学生
    SELECT*FROM students WHERE class ='计算机科学1班';

    -- 查询成绩大于80的学生
    SELECT name, score FROM students WHERE score >80;

    -- 查询年龄在19到21岁之间的学生
    SELECT*FROM students WHERE age BETWEEN19AND21;

    -- 复合条件:AND, OR, NOT
    SELECT *FROM students 
    WHERE (class ='计算机科学1班'OR class ='计算机科学2班')
    AND score >=80
    ANDNOT gender ='女';

    -- NULL值处理
    SELECT*FROM students WHERE score ISNULL;
    SELECT*FROM students WHERE score ISNOT NULL;

    排序与ORDER BY子句

    -- 按成绩降序排列
    SELECT*FROM students ORDERBY score DESC;

    -- 先按班级升序,再按成绩降序
    SELECT*FROM students ORDERBY class ASC, score DESC;

    -- 按字段位置排序(不推荐,但需了解)
    SELECT name, age, score  FROM students ORDERBY3DESC-- 按第3列(score)排序

    分组和聚合函数

    -- 计算每个班级的平均分
    SELECT class, AVG(score) as avg_score FROM students GROUPBY class;

    -- 查找每个班级的最高分和最低分
    SELECT
        class, 
        MAX(score) as highest_score, 
        MIN(score) as lowest_score,
        COUNT(*as student_count,
        SUM(score) as total_score,
        STDDEV(score) as score_deviation  -- 标准差
    FROM students 
    WHERE score ISNOT NULL
    GROUPBY class;

    -- HAVING子句(对分组结果进行筛选)
    SELECT class, AVG(score) as avg_score 
    FROM students 
    GROUPBY class
    HAVING avg_score >80;

    限制结果数量

    -- 查询前3名学生
    SELECT*FROM students ORDERBY score DESC LIMIT 3;

    -- 分页查询:每页5条,查询第2页
    SELECT*FROM students LIMIT 55;  -- 偏移量5,返回5条
    -- 或使用更现代的语法
    SELECT*FROM students LIMIT 5OFFSET5;

    模糊查询与LIKE操作符

    -- 查询名字中包含"张"的学生
    SELECT*FROM students WHERE name LIKE'%张%';

    -- 查询以"计算机"开头的班级
    SELECTDISTINCT class FROM students WHERE class LIKE'计算机%';

    -- 通配符说明
    -- %:匹配任意数量的字符
    -- _:匹配单个字符
    SELECT*FROM students WHERE name LIKE'张_';  -- 匹配"张"后跟一个字符的名字

    正则表达式查询

    -- 查询名字中包含数字的学生
    SELECT*FROM students WHERE name REGEXP '[0-9]';

    -- 查询名字以"张"或"王"开头的学生
    SELECT*FROM students WHERE name REGEXP '^[张王]';

    实际应用场景

    期末考试后,教师需要统计班级情况:

    -- 查询每个班级的及格率
    SELECT
        class,
        COUNT(*as total_students,
        SUM(CASEWHEN score >=60THEN1ELSE0END as passed_students,
        ROUND(SUM(CASEWHEN score >=60THEN1ELSE0END/COUNT(**1002as pass_rate
    FROM students
    WHERE score ISNOT NULL
    GROUPBY class;

    五、改:UPDATE 更新数据

    UPDATE语法详解

    基本语法:

    UPDATE table_name
    SET column1 = value1, column2 = value2, ...
    [WHEREcondition]
    [ORDERBY ...]
    [LIMIT row_count];

    更新单个记录

    -- 更新张三的成绩
    UPDATE students SET score =92.0WHERE name ='张三';

    更新多个字段

    -- 李四转班并更新信息
    UPDATE students 
    SET class ='计算机科学2班', age =20
    WHERE name ='李四';

    条件更新与表达式

    -- 给所有90分以上的学生加5分奖励(但不超过100分)
    UPDATE students 
    SET score = LEAST(score +5100
    WHERE score >90;

    -- 所有学生年龄增加1岁
    UPDATE students SET age = age +1;

    -- 使用CASE表达式进行条件更新
    UPDATE students
    SET score  =CASE
        WHEN score <60THEN score +5-- 不及格加5分
        WHEN score >=60AND score <90THEN score +3-- 良好加3分
        ELSE score  -- 优秀不变
    END;

    多表更新

    -- 基于另一个表的数据更新当前表
    UPDATE students s
    JOIN student_extra_info sei ON s.id = sei.student_id
    SET s.email = sei.email, s.phone = sei.phone
    WHERE sei.update_flag =1;

    实际应用场景

    期中考试后,某些学生参加了补考,需要更新成绩:

    -- 批量更新补考成绩
    UPDATE students
    SET score =CASE
        WHEN name ='李四'THEN82.5
        WHEN name ='钱七'THEN88.0
        ELSE score
    END
    WHERE name IN ('李四''钱七');

    六、删:DELETE 删除数据

    DELETE语法详解

    基本语法:

    DELETEFROM table_name
    [WHEREcondition]
    [ORDERBY ...]
    [LIMIT row_count];

    删除特定记录

    -- 删除指定学生
    DELETEFROM students WHERE name ='赵六';

    条件删除

    -- 删除成绩不及格的学生
    DELETEFROM students WHERE score <60;

    限制删除数量

    -- 删除成绩最低的3名学生
    DELETEFROM students
    ORDERBY score ASC
    LIMIT 3;

    多表删除

    -- 删除已经在毕业生表中的学生
    DELETE s FROM students s
    JOIN graduated_students g ON s.id = g.student_id;

    清空表

    -- 删除表中所有数据(逐行删除,可回滚)
    DELETEFROM students;

    -- 或者(直接删除表并重建,效率更高,不可回滚)
    TRUNCATETABLE students;

    DELETE与TRUNCATE的区别

    1. 1. 事务支持:DELETE支持事务回滚,TRUNCATE不支持
    2. 2. 速度:TRUNCATE通常更快
    3. 3. 自增值:TRUNCATE会重置AUTO_INCREMENT计数器
    4. 4. 触发器:DELETE会触发DELETE触发器,TRUNCATE不会

    实际应用场景

    学期结束,需要清理临时学生数据:

    -- 删除已经毕业的学生
    DELETEFROM students WHERE id IN (
        SELECT id FROM graduated_students
    );

    -- 假设要删除旧学期的数据并保留新学期数据
    -- 创建备份
    CREATE TABLE students_new_semester AS
    SELECT*FROM students WHERE entry_year =2025;

    -- 清空原表
    TRUNCATETABLE students;

    -- 将新数据插回原表
    INSERT INTO students 
    SELECT*FROM students_new_semester;

    -- 删除临时表
    DROPTABLE students_new_semester;

    七、高级查询技巧

    连接查询详解

    MySQL支持多种连接类型:

    • • INNER JOIN(内连接):返回两表中匹配的行
    • • LEFT JOIN(左连接):返回左表所有行和右表匹配的行
    • • RIGHT JOIN(右连接):返回右表所有行和左表匹配的行
    • • CROSS JOIN(交叉连接):返回两表的笛卡尔积

    假设我们有一个课程表:

    CREATE TABLE courses (
        course_id INT AUTO_INCREMENT PRIMARY KEY,
        course_name VARCHAR(50NOT NULL,
        teacher VARCHAR(30),
        credits INT
    );

    CREATE TABLE student_courses (
        id INT AUTO_INCREMENT PRIMARY KEY,
        student_id INT,
        course_id INT,
        FOREIGN KEY (student_id) REFERENCES students(id),
        FOREIGN KEY (course_id) REFERENCES courses(course_id)
    );

    -- 插入一些数据
    INSERT INTO courses (course_name, teacher, credits) VALUES
    ('数据库原理''陈教授'3),
    ('计算机网络''王教授'4),
    ('操作系统''李教授'4);

    INSERT INTO student_courses (student_id, course_id) VALUES
    (11), (12), (21), (33), (42), (53);

    连接查询示例:

    -- 内连接:查询学生及其选修的课程
    SELECT s.name, c.course_name, c.teacher
    FROM students s
    JOIN student_courses sc ON s.id = sc.student_id
    JOIN courses c ON sc.course_id = c.course_id
    ORDERBY s.name;

    -- 左连接:查询所有学生,包括未选课的
    SELECT s.name, IFNULL(c.course_name, '未选课'as course
    FROM students s
    LEFTJOIN student_courses sc ON s.id = sc.student_id
    LEFTJOIN courses c ON sc.course_id = c.course_id
    ORDERBY s.name;

    -- 右连接:查询所有课程,包括无人选修的
    SELECT c.course_name, IFNULL(s.name, '无人选修'as student
    FROM student_courses sc
    RIGHTJOIN courses c ON sc.course_id = c.course_id
    LEFTJOIN students s ON sc.student_id = s.id
    ORDERBY c.course_name;

    -- 自连接:查找同班同学
    SELECT s1.name, s2.name as classmate
    FROM students s1
    JOIN students s2 ON s1.class = s2.class AND s1.id != s2.id
    ORDERBY s1.class, s1.name;

    子查询详解

    子查询是嵌套在另一个查询中的SELECT语句,可以用在:

    • • SELECT子句
    • • FROM子句
    • • WHERE子句
    • • HAVING子句
    
    
        
    
    -- WHERE子句中的子查询
    -- 查询选修了"数据库原理"课程的学生
    SELECT name, age, class
    FROM students
    WHERE id IN (
        SELECT student_id
        FROM student_courses
        WHERE course_id = (SELECT course_id FROM courses WHERE course_name ='数据库原理')
    );

    -- FROM子句中的子查询(派生表)
    -- 查询每个班级的平均分,并与学生个人分数比较
    SELECT s.name, s.score, c.avg_score,
           s.score - c.avg_score as difference
    FROM students s
    JOIN (
        SELECT class, AVG(score) as avg_score
        FROM students
        GROUPBY class
    ) c ON s.class = c.class
    ORDERBY difference DESC;

    -- SELECT子句中的子查询(标量子查询)
    -- 查询每个学生选修的课程数量
    SELECT s.name, s.class, 
        (SELECTCOUNT(*FROM student_courses WHERE student_id = s.id) AS course_count
    FROM students s
    ORDERBY course_count DESC;

    -- EXISTS子查询
    -- 查询至少选修了一门课程的学生
    SELECT name, class
    FROM students s
    WHEREEXISTS (
        SELECT1FROM student_courses
        WHERE student_id = s.id
    );


    公用表表达式(CTE)

    CTE是一种临时结果集,可以在单个SQL语句中多次引用:

    -- 使用WITH子句定义CTE
    WITH ClassAvg AS (
        SELECT class, AVG(score) as avg_score
        FROM students
        GROUPBY class
    ),
    ClassRanking AS (
        SELECT s.id, s.name, s.score, s.class,
               RANK() OVER (PARTITIONBY s.class ORDERBY s.score DESCas class_rank
        FROM students s
    )
    -- 使用定义的CTE
    SELECT r.name, r.score, r.class, r.class_rank, c.avg_score
    FROM ClassRanking r
    JOIN ClassAvg c ON r.class = c.class
    WHERE r.class_rank <=3
    ORDERBY r.class, r.class_rank;

    窗口函数

    窗口函数对一组行执行计算,返回每行的值:

    -- 计算每个班级中学生的排名
    SELECT name, score, class,
           RANK() OVER (PARTITIONBY class ORDERBY score DESCas class_rank,
           DENSE_RANK() OVER (PARTITIONBY class ORDERBY score DESCas dense_rank,
           ROW_NUMBER() OVER (PARTITIONBY class ORDERBY score DESCas row_num
    FROM students;

    -- 计算累计总和
    SELECT name, score, class,
           SUM(score) OVER (PARTITIONBY class  ORDERBY score) as running_total,
           AVG(score) OVER (PARTITIONBY class) as class_avg
    FROM students;

    八、事务控制

    事务是一组操作,要么全部成功,要么全部失败。

    -- 开始事务
    START TRANSACTION;

    -- 执行操作
    UPDATE students SET score = score +10WHERE id =1;
    UPDATE courses SET credits = credits +1WHERE course_id =2;

    -- 如果一切正常,提交事务
    COMMIT;

    -- 如果出现问题,回滚事务
    -- ROLLBACK;

    事务隔离级别

    MySQL支持四种事务隔离级别:

    -- 查看当前隔离级别
    SELECT @@TRANSACTION_ISOLATION;

    -- 设置隔离级别
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
    1. 1. READ UNCOMMITTED:可以读取未提交的数据(脏读)
    2. 2. READ COMMITTED:只能读取已提交的数据
    3. 3. REPEATABLE READ:MySQL默认级别,确保同一事务中多次读取结果一致
    4. 4. SERIALIZABLE:最高级别,完全串行执行

    保存点

    在长事务中,可以设置保存点,回滚到特定位置:

    START TRANSACTION;
    UPDATE students SET score = score +5WHERE id =1;

    SAVEPOINT point1;
    UPDATE students SET score = score +10WHERE id =2;

    -- 如果需要,可以回滚到保存点
    ROLLBACKTOSAVEPOINT point1;

    -- 继续事务
    UPDATE students SET score = score +15WHERE id =3;
    COMMIT;

    九、索引与性能优化

    索引是提高查询性能的关键:

    -- 创建索引
    CREATE INDEX idx_student_name ON students(name);

    -- 创建复合索引
    CREATE INDEX idx_class_score ON students(class, score);

    -- 创建唯一索引
    CREATEUNIQUE INDEX idx_email ON students(email);

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

    -- 删除索引
    DROP INDEX idx_student_name ON students;

    索引类型

    1. 1. B-Tree索引:默认索引类型,适用于等值查询和范围查询
    2. 2. 哈希索引:仅适用于等值比较,Memory引擎支持
    3. 3. 全文索引:用于全文搜索
      `CREATE FULLTEXT INDEX idx_fulltext ON articles(title, content);`

      AI写代码sql
    4. 4. 空间索引:用于地理空间数据
      `CREATE SPATIAL INDEX idx_location ON places(location);`


      AI写代码sql

    EXPLAIN分析查询

    -- 分析查询执行计划
    EXPLAIN SELECT*FROM students WHERE class ='计算机科学1班'AND score >80;

    查询优化

    -- 使用FORCE INDEX强制使用特定索引
    SELECT*FROM students FORCE INDEX (idx_class_score)
    WHERE class  ='计算机科学1班'AND score >80;

    -- 使用STRAIGHT_JOIN控制连接顺序
    SELECT STRAIGHT_JOIN s.name, c.course_name
    FROM students s
    JOIN student_courses sc ON s.id = sc.student_id
    JOIN courses c ON sc.course_id = c.course_id;

    十、视图

    视图是基于SQL查询的虚拟表,可以简化复杂查询:

    -- 创建视图
    CREATEVIEW student_course_view AS
    SELECT s.id, s.name, s.class, c.course_name, c.teacher
    FROM students s
    JOIN student_courses sc ON s.id = sc.student_id
    JOIN courses c ON sc.course_id = c.course_id;

    -- 使用视图
    SELECT*FROM student_course_view WHERE class ='计算机科学1班';

    -- 更新视图(如果基表允许)
    UPDATE student_course_view SET name ='张三丰'WHERE id =1;

    -- 删除视图
    DROPVIEW student_course_view;

    视图的优点

    1. 1. 简化复杂查询:将复杂查询封装为视图
    2. 2. 提高安全性:限制用户只能访问视图中的特定列
    3. 3. 数据独立性:应用程序使用视图,底层表结构变化时不需要修改应用

    十一、存储过程与函数

    存储过程是一组预编译的SQL语句,可以接受参数并执行复杂操作:

    -- 创建存储过程
    DELIMITER //
    CREATEPROCEDURE update_student_score(IN student_id INTIN new_score FLOAT)
    BEGIN
        UPDATE students SET score = new_score WHERE id = student_id;
        
        -- 条件语句
        IF new_score >=90THEN
            INSERT INTO honor_students (student_id, honor_type) 
            VALUES (student_id, '优秀学生');
        END IF;
    END//
    DELIMITER ;

    -- 调用存储过程
    CALL update_student_score(195);

    -- 创建函数
    DELIMITER //
    CREATEFUNCTION get_grade(score FLOATRETURNSCHAR(1)
    DETERMINISTIC
    BEGIN
        DECLARE grade CHAR(1);
        
        IF score >=90THEN
            SET grade ='A';
        ELSEIF score >=80THEN
            SET grade ='B';
        ELSEIF score >=70THEN
            SET grade ='C';
        ELSEIF score >=60THEN
            SET grade ='D';
        ELSE
            SET grade ='F';
        END IF;
        
         RETURN grade;
    END//
    DELIMITER ;

    -- 使用函数
    SELECT name, score, get_grade(score) as grade FROM students;

    存储过程的高级特性

    DELIMITER //
    CREATEPROCEDURE process_new_students(IN class_name VARCHAR(50))
    BEGIN
        -- 声明变量
        DECLARE done INTDEFAULTFALSE;
        DECLARE s_id INT;
        DECLARE s_name VARCHAR(50);
        
        -- 声明游标
        DECLARE student_cursor CURSORFOR
            SELECT id, name FROM students 
            WHERE class = class_name AND score ISNULL;
        
        -- 声明异常处理
        DECLARE CONTINUE HANDLER FORNOT FOUND  SET done =TRUE;
        
        -- 开始事务
        START TRANSACTION;
        
        -- 打开游标
        OPEN student_cursor;
        
        -- 循环处理
        read_loop: LOOP
            FETCH student_cursor INTO s_id, s_name;
            IF done THEN
                LEAVE read_loop;
            END IF;
            
            -- 为新生创建初始记录
            INSERT INTO student_records (student_id, status) 
            VALUES (s_id, 'active');
        END LOOP;
        
        -- 关闭游标
        CLOSE student_cursor;
        
        -- 提交事务
        COMMIT;
    END//
    DELIMITER ;

    十二、触发器

    触发器是在表上执行特定操作(INSERT、UPDATE、DELETE)时自动执行的特殊存储过程:

    -- 创建触发器
    DELIMITER //
    CREATETRIGGER after_student_update
    AFTER UPDATEON students
    FOREACHROW
    BEGIN
        -- 记录成绩变化
        IF OLD.score  != NEW.score THEN
            INSERT INTO score_changes (student_id, old_score, new_score, change_date)
            VALUES (NEW.id, OLD.score, NEW.score, NOW());
        END IF;
    END//
    DELIMITER ;

    -- 查看触发器
    SHOW TRIGGERS;

    -- 删除触发器
    DROPTRIGGER after_student_update;

    触发器类型

    1. 1. BEFORE触发器:在操作执行前触发
    2. 2. AFTER触发器:在操作执行后触发
    3. 3. 行级触发器:对每一行执行一次(FOR EACH ROW)

    十三、用户管理与权限控制

    MySQL提供了完善的用户管理和权限控制系统:

    -- 创建用户
    CREATEUSER'teacher'@'localhost' IDENTIFIED BY'password123';

    -- 授予权限
    GRANTSELECTUPDATEON school.students TO'teacher'@ 'localhost';

    -- 授予所有权限
    GRANTALL PRIVILEGES ON school.*TO'admin'@'localhost';

    -- 查看用户权限
    SHOW GRANTS FOR'teacher'@'localhost';

    -- 撤销权限
    REVOKEUPDATEON school.students FROM'teacher'@'localhost';

    -- 删除用户
    DROPUSER'teacher'@'localhost';

    权限级别

    MySQL的权限系统分为四个级别:

    1. 1. 全局级别:适用于所有数据库
    2. 2. 数据库级别:适用于特定数据库中的所有对象
    3. 3. 表级别:适用于特定表
    4. 4. 列级别:适用于特定列

    十四、备份与恢复

    数据备份是数据库管理的关键部分:

    # 使用mysqldump备份数据库
    mysqldump -u root -p school > school_backup.sql

    # 备份特定表
    mysqldump -u root -p school students courses > tables_backup.sql

    # 恢复数据库
    mysql -u root -p school < school_backup.sql

    逻辑备份与物理备份

    1. 1. 逻辑备份
    • • 以SQL语句形式保存数据(如mysqldump)
    • • 优点:可跨版本、平台迁移,可选择性恢复
    • • 缺点:备份和恢复速度较慢,尤其是大型数据库
  • 2. 物理备份
    • • 直接复制数据文件(如MySQL Enterprise Backup, Percona XtraBackup)
    • • 优点:备份和恢复速度快
    • • 缺点:通常依赖于特定MySQL版本和平台

    备份策略

    1. 1. 完整备份:备份整个数据库
      
      
      
          
      # 完整备份
      mysqldump -u root ---all-databases > full_backup.sql
    2. 2. 增量备份:仅备份自上次备份以来的变化
      # 使用二进制日志进行增量备份
      mysqlbinlog mysql-bin.000001> incremental_backup.sql
    3. 3. 差异备份:备份自上次完整备份以来的所有变化

    备份选项与参数

    # 包含存储过程和函数
    mysqldump -u root -p --routines school > school_with_routines.sql

    # 包含触发器
    mysqldump -u root -p --triggers school > school_with_triggers.sql

    # 包含事件
    mysqldump -u root -p --events school > school_with_events.sql

    # 锁定表以确保一致性
    mysqldump -u root -p --lock-tables school > school_locked.sql

    # 使用单个事务进行一致性备份
    mysqldump -u root -p --single-transaction school > school_consistent.sql





    自动备份

    使用cron作业自动执行备份:

    # 在crontab中添加以下内容,每天凌晨2点执行备份
    0 2 * * * /usr/bin/mysqldump -u root -p'password' school > /backup/school_$(date +\%Y\%m\%d).sql





    备份验证与恢复测试

    定期测试备份的有效性:

    # 创建测试数据库
    mysql -u root -p -e "CREATE DATABASE school_test;"

    # 恢复备份到测试数据库
    mysql -u root -p school_test < school_backup.sql

    # 验证数据
    mysql -u root -p -e "SELECT COUNT(*) FROM school_test.students;"





    十五、分区表

    分区表允许将大表分割成更小的、更易管理的部分:

    -- 创建按范围分区的表
    CREATE TABLE student_scores (
        id INTNOT NULL,
        student_id INT,
        subject VARCHAR(50),
        score INT,
        test_date DATE,
        PRIMARY KEY (id, test_date)
    )
    PARTITIONBYRANGE (YEAR(test_date)) (
        PARTITION p0 VALUES LESS THAN (2020),
        PARTITION p1 VALUES LESS THAN (2021),
        PARTITION p2 VALUES LESS THAN (2022),
        PARTITION  p3 VALUES LESS THAN (2023),
        PARTITION p4 VALUES LESS THAN MAXVALUE
    );

    -- 创建按列表分区的表
    CREATE TABLE regional_sales (
        id INTNOT NULL,
        region VARCHAR(50),
        sales DECIMAL(10,2),
        PRIMARY KEY (id, region)
    )
    PARTITIONBY LIST (region) (
        PARTITION p_east VALUESIN ('东北''华东'),
        PARTITION p_south VALUESIN ('华南''西南'),
        PARTITION p_north VALUESIN ('华北''西北'),
        PARTITION p_central VALUESIN ('华中')
    );

    -- 创建按哈希分区的表
    CREATE TABLE access_logs (
        id INTNOT NULL,
        user_id INT,
        access_time DATETIME,
        url VARCHAR(255),
        PRIMARY KEY (id, user_id)
    )
    PARTITIONBY HASH (user_id)
    PARTITIONS 4;

    分区管理

    -- 添加分区
    ALTER TABLE student_scores ADDPARTITION (
        PARTITION p5 VALUES LESS THAN (2024)
    );

    -- 删除分区
    ALTER TABLE student_scores DROPPARTITION p0;

    -- 重组分区
    ALTER TABLE student_scores REORGANIZE PARTITION p1, p2 INTO (
        PARTITION p1_2 VALUES LESS THAN (2022)
    );

    -- 查看表分区信息
    SELECT*FROM information_schema.partitions 
    WHERE table_name ='student_scores';

    分区优势

    1. 1. 提高查询性能:查询可以只扫描相关分区
    2. 2. 简化数据管理:可以单独备份、恢复或优化特定分区
    3. 3. 提高可用性:不同分区可以存储在不同磁盘上

    十六、复制与高可用

    MySQL复制允许数据从一个MySQL数据库服务器(主服务器)复制到一个或多个MySQL数据库服务器(从服务器)。

    主从复制配置

    主服务器配置:

    # my.cnf 主服务器配置
    [mysqld]
    server-id =1
    log_bin = mysql-bin
    binlog_format =ROW

    从服务器配置:

    # my.cnf 从服务器配置
    [mysqld]
    server-id =2
    relay_log = mysql-relay-bin

    设置复制

    -- 在主服务器上创建复制用户
    CREATEUSER'repl'@'%' IDENTIFIED BY'password';
    GRANT REPLICATION SLAVE  ON*.*TO'repl'@'%';

    -- 获取主服务器状态
    SHOW MASTER STATUS;

    -- 在从服务器上配置复制
    CHANGE MASTER TO
        MASTER_HOST='master_host_ip',
        MASTER_USER='repl',
        MASTER_PASSWORD='password',
        MASTER_LOG_FILE='mysql-bin.000001',
        MASTER_LOG_POS=123;

    -- 启动从服务器复制
    START SLAVE;

    -- 检查从服务器状态
    SHOW SLAVE STATUS\G

    复制类型

    1. 1. 异步复制:默认模式,主服务器不等待从服务器确认
    2. 2. 半同步复制:主服务器等待至少一个从服务器确认接收事件
    3. 3. 组复制:多主模式,提供高可用性和自动故障转移

    高可用解决方案

    1. 1. MySQL Group Replication:内置的高可用解决方案
    2. 2. MySQL InnoDB Cluster:结合MySQL Shell、Group Replication和MySQL Router
    3. 3. 第三方工具:如Percona XtraDB Cluster、MariaDB Galera Cluster

    十七、性能调优

    服务器配置优化

    # my.cnf 关键参数
    [mysqld]
    # 缓冲池大小(通常为系统内存的50-80%
    innodb_buffer_pool_size =4G

    # 日志文件大小
    innodb_log_file_size =256M

    # 并发连接数
    max_connections =500

    # 查询缓存(MySQL 8.0已移除)
    query_cache_size =64M
    query_cache_type =1

    # 临时表大小
    tmp_table_size =64M
    max_heap_table_size =64M

    查询优化

    1. 1. 使用EXPLAIN分析查询
      `EXPLAIN SELECT*FROM students WHERE class ='计算机科学1班';`
      
      
          
      


      AI写代码sql
    2. 2. 优化索引
      -- 添加适当的索引
      CREATE INDEX idx_class ON students(class);

      -- 使用复合索引
      CREATE INDEX idx_class_score ON students(class, score);
    3. 3. 重写查询
      -- 优化前
      SELECT*FROM students WHEREYEAR(created_at) =2022;

      -- 优化后(可以使用索引)
      SELECT*FROM students WHERE created_at BETWEEN'2022-01-01'AND'2022-12-31';
    4. 4. 限制结果集大小
      
      
      
          
      -- 使用LIMIT避免返回过多数据
      SELECT*FROM logs ORDERBY created_at DESC LIMIT 1000;

    表优化

    -- 分析表
    ANALYZE TABLE students;

    -- 优化表(重建表和索引)
    OPTIMIZE TABLE students;

    -- 检查表是否损坏
    CHECKTABLE students;

    -- 修复表
    REPAIR TABLE students;

    监控工具

    1. 1. MySQL性能模式(Performance Schema)
      -- 启用性能模式
      SETGLOBAL performance_schema =ON;

      -- 查询等待事件
      SELECT*FROM performance_schema.events_waits_summary_global_by_event_name
      ORDERBY SUM_TIMER_WAIT DESC LIMIT 10;
    2. 2. MySQL系统变量
      -- 查看系统变量
      SHOW VARIABLES LIKE'innodb_buffer_pool_size';

      -- 查看状态变量
      SHOW STATUS LIKE'Threads_connected';
    3. 3. 慢查询日志
      # my.cnf 配置
      slow_query_log =1
      slow_query_log_file =/var/log/mysql/mysql-slow.log
      long_query_time =2

    十八、JSON数据类型与操作

    MySQL 5.7及更高版本支持原生JSON数据类型:

    -- 创建包含JSON字段的表
    CREATE TABLE user_profiles (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id  INT,
        profile JSON
    );

    -- 插入JSON数据
    INSERT INTO user_profiles (user_id, profile) VALUES
    (1'{"name": "张三", "age": 25, "interests": ["编程", "音乐", "旅行"]}');

    -- 更新JSON数据
    UPDATE user_profiles 
    SET profile = JSON_SET(profile, '$.age'26'$.email''zhangsan@example.com')
    WHERE user_id =1;

    -- 查询JSON数据
    SELECT
        user_id,
        JSON_EXTRACT(profile, '$.name'AS name,
        JSON_EXTRACT(profile, '$.age'AS age
    FROM user_profiles;

    -- 使用简化语法(->)
    SELECT
        user_id,
        profile->'$.name'AS name,
        profile->'$.age'AS age
    FROM user_profiles;

    -- 使用->>运算符(去除引号)
    SELECT
        user_id,
        profile->>'$.name'AS name,
        profile->>'$.age'AS age
    FROM user_profiles;

    JSON函数

    -- 创建JSON对象
    SELECTJSON_OBJECT('name''李四''age'30'city''北京');

    -- 创建JSON数组
    SELECTJSON_ARRAY('编程''音乐''旅行');

    -- 合并JSON文档
    SELECT JSON_MERGE_PRESERVE('{"name": "张三"}''{"age": 25}');

    -- 查找JSON数组中的元素
    SELECT JSON_CONTAINS(profile->'$.interests''"音乐"')
    FROM user_profiles;

    -- 提取JSON数组中的所有元素
    SELECT JSON_EXTRACT(profile, '$.interests[*]')
    FROM user_profiles;

    -- 获取JSON对象中的所有键
    SELECT JSON_KEYS(profile)
    FROM user_profiles;

    十九、全文搜索

    MySQL支持全文搜索功能,允许对文本内容进行高效搜索:

    -- 创建带全文索引的表
    CREATE TABLE articles (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(200),
        content TEXT,
        FULLTEXT INDEX idx_content (title, content)
    ) ENGINE=InnoDB;

    -- 插入数据
    INSERT INTO articles (title, content) VALUES
    ('MySQL基础教程''本文介绍MySQL的基本操作,包括增删改查等内容...'),
    ('SQL高级技巧''本文详细讲解SQL的高级用法,包括子查询、存储过程等...'),
    ('数据库优化指南''如何优化MySQL数据库性能,包括索引优化、查询优化等...');

    -- 自然语言模式搜索
    SELECT*FROM articles
    WHEREMATCH(title, content) AGAINST('MySQL 基础'INNATURALLANGUAGE MODE);

    -- 布尔模式搜索
    SELECT*FROM articles
    WHEREMATCH(title, content) AGAINST('+MySQL -高级'INBOOLEAN MODE);

    -- 带有扩展查询的搜索
    SELECT*FROM articles
    WHEREMATCH(title, content) AGAINST('数据库'WITH QUERY EXPANSION);

    全文搜索操作符(布尔模式)

    • • +:必须包含该词
    • • -:必须不包含该词
    • • >:增加相关性权重
    • • <:减少相关性权重
    • • *:通配符
    • • "":精确短语匹配
    -- 使用布尔模式操作符
    SELECT*FROM articles
    WHEREMATCH(title, content) AGAINST('+MySQL +"基础教程"'INBOOLEAN MODE);

    二十、常见问题与最佳实践

    安全最佳实践

    1. 1. 定期更新MySQL:保持最新安全补丁
    2. 2. 使用强密码:为所有用户设置强密码
    3. 3. 最小权限原则:只授予用户必要的权限
    4. 4. 加密连接:使用SSL/TLS加密客户端与服务器之间的通信
    5. 5. 审计日志:启用审计日志记录关键操作
    -- 启用SSL连接
    CREATEUSER'secure_user'@'%' IDENTIFIED BY'password' REQUIRE SSL;

    -- 设置密码策略
    SETGLOBAL validate_password.policy = STRONG;

    性能最佳实践

    1. 1. 适当使用索引:为常用查询条件创建索引,但避免过多索引
    2. 2. **避免SELECT ***:只查询需要的列
    3. 3. 使用批量操作:批量插入比单条插入更高效
    4. 4. 定期维护表:分析和优化表
    5. 5. 合理设置缓存:根据系统内存配置适当的缓冲池大小

    常见错误与解决方案

    1. 1. Too many connections
      
      
      
          
      -- 增加最大连接数
      SETGLOBAL max_connections =500;

      -- 查看当前连接
      SHOW PROCESSLIST;

      -- 终止空闲连接
      KILL connection_id;
    2. 2. Deadlock found
      -- 查看最后一个死锁信息
      SHOW ENGINE INNODB STATUS;

      -- 调整事务隔离级别
      SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
    3. 3. Table is full
      -- 增加临时表大小
      SETGLOBAL tmp_table_size =128M;
      SETGLOBAL max_heap_table_size =128M;

    总结

    本文通过理论与实例详细介绍了MySQL数据库的基础知识和高级特性:

    1. 1. 基础操作:数据库和表的创建、修改和删除
    2. 2.  CRUD操作:数据的增删改查
    3. 3. 高级查询:连接、子查询、窗口函数和CTE
    4. 4. 数据库管理:事务控制、索引优化、备份恢复
    5. 5. 高级特性:存储过程、触发器、视图、分区表
    6. 6. 性能与安全:性能调优、用户管理、安全最佳实践

    熟练掌握这些知识点,可以帮助你构建高效、安全、可靠的MySQL数据库应用。随着对MySQL的深入学习和实践,你将能够解决更复杂的数据管理和分析需求,为应用程序提供强大的数据支持。

    记住,在生产环境中使用MySQL时,务必关注数据安全性、性能优化和备份恢复等关键问题,确保数据库系统的稳定和可靠运行。持续学习和实践是成为MySQL专家的关键。

    END

    想要学习Linux系统的读者可以点击"阅读原文"按钮来了解书籍《Linux就该这么学》,同时也非常适合专业的运维人员阅读,成为辅助您工作的高价值工具书!


    Python社区是高质量的Python/Django开发社区
    本文地址:http://www.python88.com/topic/183027
     
    85 次点击