快速入门 mysql，完美掌握查询语句和函数

后端开发的主要任务包括，从数据库检索数据返回给前端进行渲染，以及将前端提交的数据保存到数据库中。在学习后端技术时，掌握数据库知识是非常关键的一步。

运行服务

通过 Docker Desktop 查询 MySQL 的镜像：pull 后点击 run 传入参数：客户端连接 MySQL 使用的端口通常是 3306。MySQL 8新增了 33060 端口，用于管理服务器。指定 volume，用本地目录作为数据卷挂载到容器的 /var/lib/mysql 目录，这个是 mysql 保存数据的目录。指定密码 MYSQL_ROOT_PASSWORD，作为客户端连接 MySQL 服务器的密码。

点击 run，mysql 容器就跑起来了：

命令行工具

mysql 镜像里自带命令行工具：使用 mysql -u root -p 命令和之前输入的密码，就可以通过命令行工具连接 MySQL 服务器。

GUI 客户端工具

操作界面概览

输入连接名，点击 store in keychain 输入密码（系统密码）点击“Test Connection”测试连接是否成功：

之后可以点击 ok 保存设置， 进入操作界面了：

在左侧“Schemas”标签下查看数据库、表、视图、存储过程和函数等：我们先点击这个图标看一下 sys_config 表中的数据：它会自动执行对应的查询表 sql。

点击第一个图标，会展示表的信息，比如多少列、多少行、占据了多大的空间等：点击第二个图标是修改表的列定义：

数据库和表操作

从 MySQL 5.0 版本开始，官方文档使用 schema 来指代 database，但实际上这两个词在MySQL中是等价的。每个数据库中可以存储多个表（table）、视图（view）、存储过程（stored procedure）和函数（function）。我们最常操作的是表。

下面五个按钮分别是创建数据库、表、视图、存储过程和函数，我们创建数据库，只需输入名称，指定字符集，点击“Apply”应用：创建成功之后，就可以看到我们刚建的数据库了：

选中 hello-mysql 数据库，点击创建 table 的按钮，我们来建个表：先建立 id 列：选中主键（Primary Key）、自动递增（Auto Increment） 的约束。

• 主键区分每一行数据的哪一列，这一列一般命名为 id。primary key 自带了唯一（unique）和非空（not null）的约束。
• 勾选 auto increment 这样插入数据的时候，会自动设置 1、2、3、4、5 递增的 id。

然后依次创建 name、age、sex、email、create_time、status（是否删除） 列：name 和 create_time 添加非空约束。点击右下角的 apply，就会生成建表 sql：这就是建表语句的语法。

数据类型

数值类型

• 整型：包含多种规格，用于存储整数。
  • TINYINT：非常小的整数。
  • SMALLINT：较小的整数。
  • MEDIUMINT：中等大小的整数。
  • INT/INTEGER：标准整数。
  • BIGINT：非常大的整数。
• 浮点型和双精度型：
  • FLOAT：单精度浮点数。
  • DOUBLE：双精度浮点数。
• 定点数：
  • DECIMAL、NUMERIC：用于存储精确的小数，常用于财务计算。

字符串类型

• CHAR：定长字符串，适合存储长度固定的文本。
• VARCHAR：变长字符串，适合存储长度可变的文本。
• TEXT：用于存储较长的文本数据。
• BLOB：用于存储二进制大对象，如图片、音频等。

日期和时间类型

• DATE：格式为 YYYY-MM-DD，仅存储日期。
• TIME：格式为 HH:MM:SS，仅存储时间。
• DATETIME：格式为 YYYY-MM-DD HH:MM:SS，存储日期和时间。
• TIMESTAMP：存储日期和时间，具有时区转换功能。
• YEAR：格式为 YYYY，仅存储年份。

二进制类型

• BINARY：定长二进制字符串。
• VARBINARY：变长二进制字符串。

枚举和集合类型

• ENUM：列只能有一个预定义的值。
• SET：列可以包含零个或多个预定义的值。

常用数据类型

• INT：用于存储整数。
• DOUBLE：存储双精度浮点数。
• VARCHAR(100)：存储最大长度为100字符的可变长度字符串。
• CHAR：存储固定长度字符串，不足部分自动填充空格。
• DATE：例如 2023-05-27，存储日期。
• TIME：例如 10:13，存储时间。
• DATETIME：例如 2023-05-27 10:13，存储日期和时间。
• TIMESTAMP：存储日期和时间，自动转换为 UTC 时间，适用于全球化应用。

SQL 语言分类

数据定义语言（DDL）

• CREATE：创建新的数据库或表。
• ALTER：修改现有的数据库或表。
• DROP：删除数据库或表。
• TRUNCATE：清空表中的所有数据。
• RENAME：重命名数据库或表。

数据操作语言（DML）

• INSERT：向表中插入新数据。
• UPDATE：更新表中的数据。
• DELETE：从表中删除数据。
• SELECT：从数据库中查询数据。

数据查询语言（DQL）

• 主要包含 SELECT 语句，用于查询数据库中的数据。

接下来我们来学习下 mysql 的查询和函数语法。

创建数据库表

首先，我在 test 数据库在创建一个 student 的表：

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'Id',
    name VARCHAR(50) NOT NULL COMMENT '学生名',
    gender VARCHAR(10) NOT NULL COMMENT '性别',
    age INT NOT NULL COMMENT '年龄',
    class VARCHAR(50) NOT NULL COMMENT '班级名',
    score INT NOT NULL COMMENT '分数'
) CHARSET=utf8mb4;

• id 是主键，自动增长。
• name 存储学生名字，不能为空。
• gender 存储性别，不能为空。
• age 存储年龄，不能为空。
• class 存储班级名，不能为空。
• score 存储分数，不能为空。

数据操作

数据插入

向 student 表中插入数据：

INSERT INTO student (name, gender, age, class, score)
    VALUES 
        ('张三', '男', 18, '一班', 85),
        ('李四', '女', 19, '二班', 86),
        ('王五', '男', 20, '三班', 87),
        ('赵六', '女', 21, '一班', 88),
        ('钱七', '男', 22, '二班', 89),
        ('孙八', '女', 23, '三班', 90),
        ('周九', '男', 24, '一班', 91),
        ('吴十', '女', 25, '二班', 92);

数据查询

查询所有数据

SELECT * FROM student;

查询指定列

SELECT name, score FROM student;

查询并重命名返回列

SELECT name as "名字", score as "分数" FROM student;

带条件的查询，通过 where：

-- 选择年龄大于或等于19岁的学生的名字和班级
SELECT name AS 名字, class AS 班级 FROM student WHERE age >= 19;

-- 选择性别为男且分数大于或等于90的学生的名字和班级
SELECT name AS 名字, class AS 班级 FROM student WHERE gender = '男' AND score >= 90;

特殊查询

模糊查询

-- 选择姓名以"张"开头的所有学生全部信息
SELECT * FROM student WHERE name LIKE '张%';

列表查询

-- class列的值必须是'一班'或者'二班'中的一个
SELECT * FROM student WHERE class IN ('一班', '二班');

-- class列的值不是'一班'和'二班'
SELECT * FROM student WHERE class NOT IN ('一班', '二班');

范围查询

SELECT * FROM student WHERE age BETWEEN 20 AND 22;

分页查询

SELECT * FROM student LIMIT 0, 5;  -- 第一页
SELECT * FROM student LIMIT 5, 5;  -- 第二页

数据排序和分组

排序

-- 对结果进行排序，先按分数降序排列，分数相同则按年龄升序排列
SELECT name, score, age FROM student ORDER BY score DESC, age ASC;

分组统计

-- AVG 计算每个班级的平均分数，并将结果列名重命名为“平均成绩”,按照 class 列（班级）来分组数据，按照计算出的平均成绩降序排列
SELECT class AS 班级, AVG(score) AS 平均成绩 FROM student GROUP BY class ORDER BY 平均成绩 DESC;

函数使用

聚合函数

-- 从student表中选择以下列
SELECT 
    AVG(score) AS 平均成绩,  -- 计算所有学生的平均分数
    COUNT(*) AS 人数,       -- 计算表中的总记录数，即学生总数
    SUM(score) AS 总成绩,   -- 计算所有学生的分数总和
    MIN(score) AS 最低分,   -- 找出所有学生中的最低分数
    MAX(score) AS 最高分    -- 找出所有学生中的最高分数
FROM student;               -- 指定查询的数据表为student

字符串函数

SELECT 
    CONCAT('xxx', name, 'yyy') AS 名字前后添加字符,
    SUBSTR(name, 2, 3) AS 名字中的一部分,
    LENGTH(name) AS 名字的长度,
    UPPER('aaa') AS 转换为大写,
    LOWER('BBB') AS 转换为小写
FROM student;

数值和日期函数

-- 从student表中选择以下数值函数的结果
SELECT 
    ROUND(1.234567, 2) AS 四舍五入到两位小数,
    CEIL(1.234567) AS 向上取整,
    FLOOR(1.234567) AS 向下取整,
    ABS(-1.234567) AS 绝对值,
    MOD(5, 2) AS 取模运算
FROM student;

-- 从student表中选择以下日期函数的结果
SELECT 
    YEAR('2023-06-01 22:06:03') AS 年份,
    MONTH('2023-06-01 22:06:03') AS 月份,
    DAY('2023-06-01 22:06:03') AS 日,
    DATE('2023-06-01 22:06:03') AS 日期,
    TIME('2023-06-01 22:06:03') AS 时间
FROM student;

条件函数

-- 从student表中选择学生的名字和基于分数的简单条件评价
SELECT 
    name,  -- 学生的名字
    IF(score >= 60, '及格', '不及格') AS 成绩评价  -- 使用IF函数根据分数判断学生是否及格
FROM student;

-- 从student表中选择学生的名字、分数和基于分数的详细条件评价
SELECT 
    name,  -- 学生的名字
    score, -- 学生的分数
    CASE  -- 使用CASE表达式进行更详细的成绩评价
        WHEN score >= 90 THEN '优秀'  -- 分数大于等于90，评价为“优秀”
        WHEN score >= 60 THEN '良好'  -- 分数大于等于60且小于90，评价为“良好”
        ELSE '差'                     -- 分数小于60，评价为“差”
    END AS '档次'  -- 结果列的标题为“档次”
FROM student;

系统函数

用于获取系统信息：

select VERSION(), DATABASE(), USER()

COALESCE 函数

COALESCE 函数用于返回参数列表中第一个非 null 的值：

SELECT COALESCE(NULL, 1), COALESCE(NULL, NULL, 2);

此查询将分别返回 1 和 2。

GREATEST 和 LEAST 函数

• GREATEST 函数返回所有给定参数中的最大值。
• LEAST 函数返回所有给定参数中的最小值。

SELECT GREATEST(1, 2, 3), LEAST(1, 2, 3, 4);

此查询将返回 3 和 1。

类型转换函数

CAST 和 CONVERT

类型转换函数如 CAST 和 CONVERT 可用于明确数据类型，以确保函数正确理解和处理数据。考虑以下查询，其中 GREATEST 函数未能正确比较字符串和数字：

SELECT GREATEST(1, '123', 3);

由于 '123' 被视为字符串，结果将是 3。为了正确比较，我们可以使用 CAST 或 CONVERT 函数进行类型转换：

SELECT GREATEST(1, CONVERT('123', SIGNED), 3);
SELECT GREATEST(1, CAST('123' AS SIGNED), 3);

这两个查询都将返回 123。

支持的类型转换

• SIGNED：整型
• UNSIGNED：无符号整型
• DECIMAL：浮点型
• CHAR：字符类型
• DATE：日期类型
• TIME：时间类型
• DATETIME：日期时间类型
• BINARY：二进制类型

日期格式转换

• DATE_FORMAT 函数用于将日期转换为指定格式的字符串。
• STR_TO_DATE 函数用于将字符串转换为日期类型。

SELECT DATE_FORMAT('2022-01-01', '%Y年%m月%d日');
SELECT STR_TO_DATE('2023-06-01', '%Y-%m-%d');

高级 SQL 技巧

使用 DISTINCT 去重

-- 查询 student 表中所有不同的班级
SELECT DISTINCT class FROM student;

使用 HAVING 对分组结果进行过滤

-- 查询每个班级的平均分数，并筛选出平均分数超过 85 分的班级
SELECT class, AVG(score) AS avg_score  -- 选择班级和平均分数，平均分数命名为 avg_score
FROM student                           -- 从 student 表中获取数据
GROUP BY class                         -- 按班级分组，以便计算每个班级的平均分数
HAVING avg_score > 85;                 -- 筛选出平均分数超过 85 分的班级

引号使用规则

在 SQL 中，引号的使用根据上下文有所不同：

• 单引号 (') 或双引号 (") 用于标记字符串值。
• 反引号 (`) 用于标记表名或列名，尤其是在名称中包含特殊字符或关键字时。
• 不加引号通常用于引用 SQL 关键字或函数名。