MYSQL实用技术教程

MySQL数据库实用技术教程

第一章 数据库基础

1.1 数据库概述

数据库是按照数据结构来组织、存储和管理数据的仓库。随着信息技术的不断发展，数据库已经成为现代信息系统不可或缺的核心组成部分。MySQL作为最流行的开源关系型数据库管理系统之一，因其体积小、速度快、总体拥有成本低而广受开发者和企业的青睐。MySQL支持多种操作系统，提供了丰富的存储引擎选项，能够满足不同业务场景的需求。Navicat是一款功能强大的图形化数据库管理工具，可以帮助用户更直观地进行数据库操作，支持数据库的创建、表的设计、数据的增删改查等功能，大大提高了开发效率。

1.2 MySQL安装与配置

MySQL的安装过程相对简单，用户可以从官方网站（http://mysql.com/cn/）下载适合自己操作系统的安装包。在安装过程中，需要注意选择合适的安装类型和配置选项。对于初学者来说，使用默认配置通常就能满足日常学习需求。安装完成后，可以通过命令行工具或图形化工具（如Navicat）来连接和管理MySQL服务器。Navicat提供了付费订阅版本和学生认证免费版本，学生可以通过教育邮箱申请免费使用，这对于在校学生来说是一个非常好的学习工具。连接MySQL服务器时，需要注意正确填写主机地址、端口号（默认3306）、用户名和密码等信息，确保能够成功建立连接。

第二章 数据库操作

2.1 创建数据库

创建数据库是使用MySQL的第一步。在MySQL中，可以使用CREATE DATABASE语句来创建一个新的数据库。基本语法格式如下：

CREATE DATABASE 数据库名;

例如，要创建一个名为"book"的数据库，可以使用以下语句：

CREATE DATABASE book;

在创建数据库时，建议选择有意义的名称，以便于后续的管理和维护。数据库名称应该遵循一定的命名规范，通常使用小写字母和下划线组成，避免使用特殊字符和中文命名。创建数据库时，MySQL会在磁盘上创建一个与数据库名称相同的文件夹，用于存储该数据库的所有数据文件。如果在创建数据库时出现错误，可能是由于数据库名称已存在或者用户权限不足导致的。

2.2 查看数据库

查看数据库是了解当前MySQL服务器上有哪些数据库的重要操作。使用SHOW DATABASES语句可以查看所有数据库：

SHOW DATABASES;

执行该语句后，MySQL会返回一个包含所有数据库的列表，其中包括系统自带的数据库（如information_schema、mysql、performance_schema等）和用户创建的数据库。通过查看数据库列表，可以确认数据库是否创建成功，或者了解当前MySQL服务器的管理范围。在使用SHOW DATABASES语句时，需要注意权限问题，普通用户可能只能看到被授权访问的数据库。

2.3 打开数据库

在创建或查看数据库之后，需要打开（选择）要使用的数据库，才能对该数据库中的数据表进行操作。使用USE语句可以打开指定的数据库：

USE 数据库名;

例如，要打开名为"book"的数据库：

USE book;

打开数据库后，后续的所有操作都将在该数据库上下文中执行，直到再次使用USE语句切换到其他数据库。在编写MySQL脚本时，通常会在脚本开头先使用USE语句指定要操作的数据库，这样可以避免在每个表名前面都加上数据库名的前缀。值得注意的是，USE语句不需要分号结束，但为了保持与其他SQL语句的一致性，很多开发者仍然习惯在末尾加上分号。

2.4 删除数据库

删除数据库是一个需要谨慎操作的行为，因为一旦删除，数据库中的所有数据和表结构都将无法恢复。在MySQL中，使用DROP DATABASE语句可以删除指定的数据库：

DROP DATABASE 数据库名;

例如，要删除名为"book"的数据库：

DROP DATABASE book;

在执行删除操作之前，务必确认数据库中的数据已经备份或者不再需要。删除数据库时，MySQL会物理删除该数据库对应的文件夹及其中的所有文件，因此这个操作是不可逆的。为了防止误删除，可以在DROP DATABASE语句中添加IF EXISTS关键字，这样即使数据库不存在也不会报错：

DROP DATABASE IF EXISTS book;

第三章 数据表操作

3.1 创建数据表

数据表是数据库中最基本的数据存储单位，由行和列组成。创建数据表需要定义表名、字段名、字段类型等信息。基本语法格式如下：

CREATE TABLE 表名 (
    字段1 字段类型1,
    字段2 字段类型2,
    ...
);

例如，创建一个学生信息表：

CREATE TABLE 学生情况 (
    学号 VARCHAR(8) PRIMARY KEY,
    姓名 VARCHAR(20),
    性别 VARCHAR(2),
    出生日期 DATE,
    所在分院 VARCHAR(20)
);

在创建表时，需要根据数据的特性选择合适的字段类型。常用的字段类型包括：INT（整数）、VARCHAR（可变长度字符串）、DATE（日期）、DATETIME（日期时间）、TEXT（文本）等。字段类型的正确选择对于数据的存储效率和查询性能都有重要影响。此外，还可以在创建表时定义各种约束条件，如主键、唯一键、非空等，以保证数据的完整性和一致性。

3.2 查看数据表

创建数据表之后，可以使用多种方式查看表的信息。使用DESCRIBE或DESC语句可以查看表的结构信息：

DESCRIBE 数据表名;
-- 或者
DESC 数据表名;

执行该语句后，会返回表的字段列表，包括字段名、字段类型、是否为空、默认值、键信息等内容。通过查看表结构，可以确认表是否按照预期创建成功，以及各个字段的定义是否正确。

使用SHOW CREATE TABLE语句可以查看表的创建语句，包括详细的表定义信息：

SHOW CREATE TABLE 表名;

这个语句对于了解表的完整定义和复制表结构非常有用。此外，还可以使用SHOW TABLE STATUS命令查看表的行号信息、存储引擎、数据大小等统计信息：

SHOW TABLE STATUS;

3.3 修改数据表

修改数据表包括添加列、修改列、删除列、重命名表等操作。使用ALTER TABLE语句可以完成这些修改任务：

在表中添加列：

ALTER TABLE 表名 ADD COLUMN 列名 列类型;

例如，要在学生情况表中添加一个备注字段：

ALTER TABLE 学生情况 ADD COLUMN 备注 VARCHAR(100);

在表中修改列：

修改列名和类型使用CHANGE COLUMN关键字：

ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 新列类型;

只修改列类型使用MODIFY COLUMN关键字：

ALTER TABLE 表名 MODIFY COLUMN 列名 新列类型;

在表中修改表名：

ALTER TABLE 表名 RENAME TO 新表名;

3.4 删除数据表

删除数据表同样是一个需要谨慎操作的行为。使用DROP TABLE语句可以删除一个或多个数据表：

DROP TABLE [IF EXISTS] 表名1 [, 表名2, 表名3];

例如，删除学生情况表：

DROP TABLE 学生情况;

使用IF EXISTS关键字可以防止报错：

DROP TABLE IF EXISTS 学生情况;

删除数据表时，表中的所有数据都会被永久删除，因此在执行删除操作之前一定要确认数据已经备份或者不再需要。如果要删除有关联关系的数据表，需要先解除外键约束，或者按照正确的顺序删除（先删除子表，再删除父表）。

第四章 约束

4.1 主键约束

主键是数据表中每一个数据的唯一标识，用于确保表中每条记录的唯一性。主键可以是单个字段（单字段主键），也可以是多个字段的组合（多字段主键）。主键列的值不能为空，也不能重复。一个表只能有一个主键，但主键可以由多个字段组成。

方法一：在创建数据表时指定主键

CREATE TABLE 图书分类 (
    类别编码 VARCHAR(10) PRIMARY KEY,
    类别名 VARCHAR(20)
);

方法二：在修改表时添加主键

ALTER TABLE 图书情况 ADD PRIMARY KEY (图书编号);

多字段主键的设置：

CREATE TABLE 借还记录 (
    学号 VARCHAR(8),
    图书编号 VARCHAR(10),
    借阅日期 DATE,
    归还日期 DATE,
    备注 VARCHAR(100),
    PRIMARY KEY (学号, 图书编号, 借阅日期)
);

4.2 自增主键

自增主键是MySQL中的一个特殊功能，可以为每条新记录自动生成唯一的数字值。这对于没有明显业务含义的主键（如ID）非常有用。

基本语法：

字段名 数据类型 AUTO_INCREMENT

示例：

CREATE TABLE 借还记录 (
    流水号 INT(4) PRIMARY KEY AUTO_INCREMENT,
    学号 VARCHAR(8),
    图书编号 VARCHAR(10)
);

设置初始值：

默认情况下，自增字段从1开始自动增长。如果需要设置初始值，可以使用AUTO_INCREMENT属性：

CREATE TABLE 借还记录 (
    流水号 INT(4) PRIMARY KEY AUTO_INCREMENT,
    学号 VARCHAR(8),
    图书编号 VARCHAR(10)
) AUTO_INCREMENT = 100;

重要说明：

• 默认情况下，自增字段从1开始，每添加一条记录，字段值自动加1
• 可以设置为0，此时会从1开始计数
• 一张表中只能有一个AUTO_INCREMENT字段
• AUTO_INCREMENT只能设置在整数类型字段上，其最大值受数据类型限制

4.3 非空约束

非空约束用于指定某个字段的值不能为空。当字段设置了非空约束后，如果用户在添加数据时没有为该字段指定值，数据库系统会报错。

方法一：在创建表时设置非空约束

CREATE TABLE 图书分类 (
    图书编号 INT(10) PRIMARY KEY,
    图书名 VARCHAR(20) NOT NULL
);

方法二：在修改表时添加非空约束

ALTER TABLE 借还记录
CHANGE COLUMN 借阅日期 借阅日期 DATE NOT NULL;

4.4 唯一约束

唯一约束用于确保字段的值不能重复出现，但允许为空值（只能有一个空值）。与主键约束不同，一个表可以有多个唯一约束。

方法一：在创建表时设置唯一约束

CREATE TABLE 图书情况 (
    图书编号 VARCHAR(10) PRIMARY KEY,
    图书名 VARCHAR(50),
    ISBN号 VARCHAR(13) UNIQUE
);

方法二：在修改表时设置唯一约束

ALTER TABLE 图书分类
ADD CONSTRAINT unique_name UNIQUE(图书名);

4.5 默认约束

默认约束用于为字段设置默认值。当添加数据时如果没有为该字段指定值，数据库会自动使用默认值。

方法一：在建表时设置默认值

CREATE TABLE 学生情况 (
    学号 VARCHAR(8) PRIMARY KEY,
    姓名 VARCHAR(20),
    性别 VARCHAR(2) DEFAULT '男',
    出生日期 DATE,
    所在分院 VARCHAR(20) DEFAULT '信息'
);

方法二：在修改表时设置默认值

ALTER TABLE 学生情况
CHANGE COLUMN 所在分院 所在分院 VARCHAR(20) DEFAULT '信息';

4.6 外键约束

外键约束用于关联两个表之间的关系，确保引用完整性。外键表中的某个字段（外键）引用另一个表的主键字段，从而建立表与表之间的关联关系。

方法一：在创建表时设置外键约束

CREATE TABLE 图书情况 (
    图书编号 VARCHAR(10) PRIMARY KEY,
    类别编码 VARCHAR(10),
    图书名 VARCHAR(50),
    FOREIGN KEY (类别编码) REFERENCES 图书分类(类别编码)
);

方法二：在修改表时添加外键

ALTER TABLE 借还记录
ADD CONSTRAINT fk_tsbh
FOREIGN KEY (图书编号)
REFERENCES 图书情况(图书编号);

ALTER TABLE 借还记录
ADD CONSTRAINT fk_xh
FOREIGN KEY (学号)
REFERENCES 学生情况(学号);

第五章 数据操作

5.1 添加数据

向表中添加数据使用INSERT语句。MySQL支持多种添加数据的方式。

向表中全部字段添加记录：

INSERT INTO 学生情况
VALUES ('21080104', '李一蒙', '女', '2003-4-4', '商贸');

向表中部分字段添加记录：

INSERT INTO 学生情况 (学号, 姓名, 性别, 出生日期)
VALUES ('21080105', '吉莉', '女', '2002-4-3');

同时添加多条记录：

INSERT INTO 学生情况 (学号, 姓名, 性别, 出生日期, 所在分院)
VALUES
('21080106', '张三', '男', '2003-1-1', '信息'),
('21080107', '李四', '女', '2002-5-5', '商贸');

使用SET方式添加数据：

INSERT INTO 学生情况
SET 学号 = '21080108', 姓名 = '王五';

5.2 修改数据

修改表中数据使用UPDATE语句。基本语法格式如下：

UPDATE 表名 SET 字段1 = 值1 [, 字段2 = 值2, ...] [WHERE 条件] [ORDER BY 字段] [LIMIT 行数];

修改表中的全部数据：

UPDATE 学生情况 SET 所在分院 = '信息';

这条语句会将表中所有记录的“所在分院”字段更新为“信息”。

按条件修改表中记录：

UPDATE 学生情况
SET 出生日期 = '2002-3-3'
WHERE 学号 = '21080108';

WHERE子句用于指定修改条件，只有满足条件的记录才会被更新。如果不加WHERE条件，将更新表中所有的记录，这在实际操作中需要特别小心。

5.3 删除数据

删除表中数据使用DELETE语句。基本语法格式如下：

DELETE FROM 表名 [WHERE 条件] [ORDER BY 字段] [LIMIT 行数];

按条件删除记录：

DELETE FROM 学生情况 WHERE 学号 = '21080108';

删除表中全部记录：

DELETE FROM 学生情况;

删除表中全部记录会保留表结构，但删除所有数据。与DELETE相比，TRUNCATE语句可以更快速地清空表中的所有记录：

TRUNCATE TABLE 学生情况;

TRUNCATE语句会删除表中所有记录并重新设置自增字段的起始值，执行效率比DELETE高，但对于有外键约束的表可能无法执行。

第六章 数据查询操作

6.1 基本查询

数据查询是数据库操作中最常用也是最重要的功能。使用SELECT语句可以从表中检索数据。

查询表中全部数据：

SELECT * FROM 图书情况;

星号（*）表示查询所有字段。

查询表中部分字段：

SELECT 图书编号, 图书名 FROM 图书情况;

查询结果字段重命名：

SELECT 图书编号, 图书名 AS '图书名称' FROM 图书情况;

使用AS关键字可以为查询结果中的字段设置别名，使结果更易读。

6.2 按条件查询

使用WHERE子句可以设置查询条件，筛选满足条件的记录。

SELECT 图书编号, 图书名, 出版社
FROM 图书情况
WHERE 出版社 = '清华大学出版社';

这条语句查询清华大学出版社出版的所有书籍。

6.3 查询结果去重

使用DISTINCT关键字可以去掉查询结果中的重复记录。

SELECT DISTINCT 出版社 FROM 图书情况;

6.4 查询结果排序

使用ORDER BY子句可以对查询结果进行排序。ASC表示升序（默认），DESC表示降序。

SELECT 图书编号, 图书名, 定价
FROM 图书情况
ORDER BY 定价 DESC;

这条语句按定价从高到低排序查询结果。

6.5 查询条件与运算表达式

比较运算符：

示例：查询定价大于等于30元的图书

SELECT 图书编号, 图书名, 定价
FROM 图书情况
WHERE 定价 >= 30
ORDER BY 定价;

BETWEEN AND：

用于判断一个值是否在两个值之间。

SELECT 图书编号, 图书名, 定价
FROM 图书情况
WHERE 定价 BETWEEN 20 AND 30;

IS NULL / IS NOT NULL：

判断字段是否为空。

SELECT * FROM 学生情况 WHERE 所在分院 IS NULL;

IN：

判断字段是否在指定的集合中。

SELECT * FROM 学生情况 WHERE 所在分院 IN ('信息', '商贸');

6.6 模糊查询

使用LIKE关键字可以进行模糊查询，常配合通配符使用。

SELECT * FROM 学生情况 WHERE 姓名 LIKE '张%';

这条语句查询所有姓“张”的学生。

SELECT * FROM 学生情况 WHERE 姓名 LIKE '_三';

这条语句查询姓名第二个字是“三”的学生（如“张三”、“李三”等）。

6.7 逻辑运算符

SELECT * FROM 学生情况 WHERE 所在分院 = '信息' AND 性别 = '男';

SELECT * FROM 学生情况 WHERE 所在分院 = '信息' OR 所在分院 = '商贸';

第七章 多表查询

7.1 连接查询

连接查询是将两个或多个表根据一定的条件组合在一起的查询方式。常见的连接类型包括内连接、外连接等。

内连接（INNER JOIN）：

内连接只返回两个表中匹配到的记录。

SELECT 图书情况.图书编号, 图书情况.图书名, 图书分类.类别名
FROM 图书情况
INNER JOIN 图书分类 ON 图书情况.类别编码 = 图书分类.类别编码;

左外连接（LEFT JOIN）：

左外连接返回左表中的所有记录以及右表中匹配的记录。

SELECT 图书情况.图书编号, 图书情况.图书名, 图书分类.类别名
FROM 图书情况
LEFT JOIN 图书分类 ON 图书情况.类别编码 = 图书分类.类别编码;

右外连接（RIGHT JOIN）：

右外连接返回右表中的所有记录以及左表中匹配的记录。

SELECT 图书情况.图书编号, 图书情况.图书名, 图书分类.类别名
FROM 图书情况
RIGHT JOIN 图书分类 ON 图书情况.类别编码 = 图书分类.类别编码;

7.2 USING关键字

在使用连接查询时，如果两个表中的关联字段名称相同，可以使用USING关键字简化查询语句。

SELECT *
FROM A
JOIN B USING (id);

这条语句等价于：

SELECT *
FROM A
INNER JOIN B ON A.id = B.id;

7.3 联合查询

使用UNION关键字可以将多个查询的结果合并在一起。

(SELECT * FROM 表1 WHERE 条件 ORDER BY 字段)
UNION
(SELECT * FROM 表2 WHERE 条件 ORDER BY 字段);

需要注意的是，每个SELECT语句必须返回相同数量的字段，且对应字段的类型应该兼容。UNION会自动去掉重复的记录，如果要保留重复记录，可以使用UNION ALL。

7.4 子查询

子查询是指在一个查询语句中嵌套另一个完整的查询语句。子查询可以返回单个值、一行数据、一列数据或一个表。

标量子查询：

返回单个值的子查询。

SELECT * FROM 图书情况
WHERE 定价 > (SELECT AVG(定价) FROM 图书情况);

行子查询：

返回一行多列的结果。

SELECT * FROM 员工表
WHERE (部门编号, 薪资) IN
    (SELECT 部门编号, MAX(薪资) FROM 员工表 GROUP BY 部门编号);

表子查询：

返回多行多列的结果，可以作为临时表使用。

SELECT e.姓名, e.薪资, d.平均薪资
FROM 员工表 e
JOIN (
    SELECT 部门编号, AVG(薪资) AS 平均薪资
    FROM 员工表
    GROUP BY 部门编号
) d ON e.部门编号 = d.部门编号
WHERE e.薪资 > d.平均薪资;

** EXISTS子查询：**

用于检查子查询是否返回任何行。

SELECT 姓名 FROM 员工 e
WHERE EXISTS (
    SELECT 1 FROM 部门 d WHERE d.经理编号 = e.员工编号
);

附录：常用函数

聚合函数

字符串函数

日期函数