MySQL基础学习

o( ￣⌣￣ )o included in Other

2024-04-01 4944 words 10 minutes

Contents

Nature's Beauty

Explore the wonders of the world

参考资料

简介

数据库连接工具可使用 Navicat 等软件。

基本语法

SQL 语句不区分大小写，但关键字建议大写
语句需要以分号分隔
可以写成一行也可以分为多行
使用两个横杠 -- 进行注释

SQL 分类

数据定义语言（DDL）：用于定义数据结构和数据库对象，如 CREATE、ALTER、DROP
数据操纵语言（DML）：用于访问数据库中的对象和数据，如 INSERT、UPDATE、DELETE、SELECT（增删改查）
事务控制语言（TCL）：用于管理数据库中的事务，如 COMMIT、ROLLBACK
数据控制语言（DCL）：用于控制数据访问权限，如 GRANT、REVOKE

命名规范

数据库表名和字段名可选择驼峰式或下划线式命名，但不推荐混用。

1
2
3
4
5
6
7
8
9


-- 下划线式
SELECT customer_name, order_date
FROM customer_orders
WHERE is_active = true;

-- 驼峰式
SELECT CustomerName, OrderDate
FROM CustomerOrders
WHERE IsActive = 1;

小写下划线式对大小写不敏感的数据库有好处。

常见命名示例

1
2
3
4
5
6
7
8


-- 布尔类型用 is_/has_/can_ 前缀
is_verified
has_permission

-- 时间字段用 _at 或 _date 后缀
created_at
updated_at
expiration_date

选择命名方式时可参考对应数据库平台官方推荐。PostgreSQL 建议使用 snake_case，SQL Server 可用 CamelCase。结合所使用的 ORM 框架进一步考虑。无论选择哪种风格，重要的是整个项目或团队保持一致。

数据库操作

显示数据库版本：SELECT VERSION();

显示时间：SELECT NOW();

查看所有数据库：SHOW DATABASES;

创建用于练习的数据库：CREATE DATABASE mytest_school CHARSET=utf8;

切换到数据库：USE mytest_school;

查看当前使用的数据库：SELECT DATABASE();

删除数据库：DROP DATABASE IF EXISTS mytest_school;

退出：exit 或 quit

数据表操作

创建表

定义字段时需考虑：字段名、类型（如 INT、VARCHAR）、是否为主键、是否 NOT NULL、可选的 DEFAULT 值。

常用约束：

INT UNSIGNED：无符号整型
AUTO_INCREMENT：设置自增，通常用于主键
NOT NULL：设置字段不能为空
PRIMARY KEY：设置主键
DEFAULT：设置默认值
ENGINE=InnoDB：设置存储引擎

1
2
3
4
5
6
7
8


-- 创建学生表
CREATE TABLE students (
  id INT ( 10 ) UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '自增 id',
  username VARCHAR ( 64 ) NOT NULL DEFAULT 'default' COMMENT '用户名',
  age TINYINT ( 3 ) COMMENT '年龄',
  gender ENUM ( '男', '女', '保密' ) COMMENT '性别',
  cls_id INT ( 10 ) UNSIGNED COMMENT '课程 id'
) COMMENT = '学生表';

查看已有表格：SHOW TABLES;

查看表结构：DESC students;

删除表

1

DROP TABLE IF EXISTS students;

修改表

添加列

1
2
3


-- 新增一个电子邮箱列
ALTER TABLE students
ADD COLUMN email VARCHAR ( 32 );

删除列

1
2


ALTER TABLE 表名
DROP COLUMN 列名;

修改列名

1
2


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

修改表名

1
2


ALTER TABLE 表名
RENAME AS 新表名;

修改默认值

1
2


ALTER TABLE 表名
ALTER 列名 SET DEFAULT 1000;

修改列的类型或约束

1
2


ALTER TABLE 表名
MODIFY COLUMN 列名 新类型 【新约束】;

修改表的编码格式

1
2
3


ALTER TABLE 表名
CONVERT TO CHARACTER
SET utf8mb4 COLLATE utf8mb4_general_ci;

utf8mb4 编码是 utf8 的超集，兼容 utf8 并能存储 4 字节的表情字符。如果表的编码指定为 utf8，在保存 emoji 时会报错。

数据操作

增（INSERT）

全列插入：主键字段可以用 0、NULL、DEFAULT 来占位。

1
2
3


INSERT INTO students
VALUES
  ( NULL, '小陆', 18, 1.78, '男', 1 );

部分插入：

1
2
3


INSERT INTO students ( id, username )
VALUES
  ( 23, '小思' );

多行插入：

1
2
3
4
5


INSERT INTO students
VALUES
  ( NULL, '小风', 18, 1.78, '男', 1 ),
  ( NULL, '小晨', 19, 1.61, '女', 3 ),
  ( NULL, '小展', 19, 1.75, '男', 2 );

三种插入方式的区别：

INSERT INTO：遇到重复主键会报错
REPLACE INTO：遇到重复主键会进行替换
INSERT IGNORE：遇到重复主键会直接忽略

删（DELETE）

物理删除：

1
2
3


DELETE FROM students
WHERE
  id = 2;

逻辑删除：用字段标识数据是否可用。

1
2
3
4
5
6
7
8
9


-- 添加逻辑删除字段
ALTER TABLE students
ADD is_deleted BIT DEFAULT 0;

-- 标记为不可用
UPDATE students
SET is_deleted = 1
WHERE
  id = 3;

改（UPDATE）

1
2
3
4
5
6


UPDATE students
SET
  high = 1.56,
  gender = '女'
WHERE
  username = '小环';

重要：删除和修改操作务必加上 WHERE 条件限制，否则会作用于全表。

查（SELECT）

查询所有列：

1
2
3
4


SELECT
  *
FROM
  students;

查询指定列：

1
2
3
4
5


SELECT
  id,
  username
FROM
  students;

条件查询：

1
2
3
4
5
6


SELECT
  *
FROM
  students
WHERE
  id = 2;

使用 AS 为列或表指定别名：

1
2
3
4
5


SELECT
  s.id AS '编号',
  s.username AS '姓名'
FROM
  students AS s;

表连接类型

CROSS JOIN：两两结合，不需要条件，类似笛卡尔积
INNER JOIN 或 JOIN：返回两张表中具有匹配值的记录
LEFT JOIN、RIGHT JOIN：属于外连接，左外连接的左表记录全部保留，右表匹配到就加入；右外连接同理，缺失数据显示为 NULL

视图

对视图的操作与对普通表的操作一样。但视图是 MySQL 中的虚拟表，基于一个或多个实际表的查询结果，本身不存储数据，不能对其进行索引操作。

特点

虚拟表：不存储实际数据，只存储查询定义
简化复杂查询：将复杂查询封装
数据安全：可以限制用户访问底层表的特定列，提高数据库安全性
逻辑独立性：可以屏蔽表结构变化对应用的影响

缺点

复杂视图可能导致查询性能下降
过多视图会使数据库维护困难

视图操作

创建视图：

1
2
3
4
5
6
7
8


CREATE VIEW order_summary (customer_name, order_count, total_amount) AS
SELECT
    CONCAT(c.first_name, ' ', c.last_name),
    COUNT(o.order_id),
    SUM(o.amount)
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

查询视图：

1

SELECT * FROM order_summary WHERE last_name LIKE 'S%';

删除视图：

1

DROP VIEW order_summary;

常用函数

聚合函数

SUM()、AVG()、COUNT()、MAX()、MIN()

一般配合 GROUP BY 使用。分组 GROUP BY ..., ... 支持多字段。

日期函数

DATE()、DATETIME()、TIME()

计算两个日期相差的时间：

1
2


-- 查看 Date1 与 Date2 相差的时间
DATEDIFF(Date1, Date2)

时间段数据查询：

1
2
3
4
5
6
7


-- 方式一
time >= '2020-01-01 00:00:00'
AND time <= '2020-12-31 23:59:59'

-- 方式二（等同于上面）
time BETWEEN '2020-01-01 00:00:00'
AND '2020-12-31 23:59:59'

字符串与日期格式的转换：

1
2
3
4
5


-- 根据给定格式将字符串转为日期
STR_TO_DATE(str, format)

-- 根据给定格式将日期转为字符串
DATE_FORMAT(date, format)

排序

ORDER BY 可以指定多个字段排序。先对字段 a 升序，再对字段 b 降序：

1
2
3
4
5
6
7


SELECT
  ...
FROM
  ...
ORDER BY
  a ASC,
  b DESC

字符串函数

判断是否为空：

1
2
3
4
5


-- 判断 a 是否为空
ISNULL(a)

-- 若 a 不为 NULL 则返回 a，否则返回 b（设定默认值）
IFNULL(a, b)

大小写转换：

1
2


UPPER(str)
LOWER(str)

截取和拼接：

1
2
3
4
5


-- 从索引 1 开始截取长度为 3 的字符串
SUBSTR(str, 1, 3)

-- 拼接字符串
CONCAT(str1, str2, ...)

其他处理：

1
2
3
4
5
6
7
8


-- 去掉首尾字符
TRIM(str)

-- 获取字符串长度
LENGTH(str)

-- 查找子串，找到则返回第一次出现的索引，找不到则返回 0
INSTR(str, substr)

条件表达式

1
2
3
4
5


-- 满足条件 a 则返回 b，否则返回 c
IF(a, b, c)

-- 示例
IF(x > 5, '大', '小')

等同于 CASE 语句：

1
2
3
4
5


CASE
    WHEN ... THEN ...
    WHEN ... THEN ...
    ELSE ...
END AS ...

子查询

IN 语句：

1
2
3
4
5
6
7
8


SELECT
  var
FROM
  ...
WHERE
  var IN (
    又一个 SELECT 语句
  )

EXISTS 语句：逻辑运算符，返回布尔值，用于检查子查询是否返回任何行。一旦子查询找到匹配行，EXISTS 返回 TRUE 并停止处理；没有返回任何行则返回 FALSE。

1
2
3
4
5
6
7
8


SELECT
  var
FROM
  ...
WHERE
  EXISTS (
    又一个 SELECT 语句
  )

示例：查找有订单的客户，使用 NOT EXISTS 则查找没有订单的客户：

1
2
3
4
5
6
7


SELECT customer_name
FROM customers
WHERE EXISTS (
  SELECT 1
  FROM orders
  WHERE orders.customer_id = customers.customer_id
);

EXISTS 特别适用于基于相关子查询的存在性来过滤数据的场景。

拼接表

1
2
3
4
5
6
7
8
9


-- 返回的数据已去重
查询语句 1
UNION
查询语句 2

-- 返回的数据可能存在重复值
查询语句 1
UNION ALL
查询语句 2

窗口函数

窗口函数是一种强大的查询工具，允许对分组数据进行计算，同时保留原始行的详细信息。通过指定字段将数据分为几个窗口，再使用聚合函数进行分析。

支持的窗口函数

包括 RANK、DENSE_RANK、ROW_NUMBER，以及所有聚合函数等。

特点

相比 GROUP BY，执行完后数据条数不变
只能在 SELECT 子句中使用

示例

分组后组内求和：

1
2
3
4
5
6
7


SELECT
  ...,
  SUM(计算字段名) OVER (
    PARTITION BY 分组字段名
  ) AS...
FROM
  ...

SUM 函数可替换为 AVG、MIN、MAX 等。需计算同组内的累加和时，需要加上 ORDER BY：

1
2
3
4
5
6
7
8


SELECT
  ...,
  SUM(计算字段名) OVER (
    PARTITION BY 分组字段名
    ORDER BY 根据某字段排序
  ) AS...
FROM
  ...

排序函数：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18


SELECT
  ...,
  RANK() OVER (
    PARTITION BY 分组字段
    ORDER BY 排序字段 DESC
  ) AS...
FROM
  ...

-- ROW_NUMBER 用于唯一排名
SELECT
  ...,
  ROW_NUMBER() OVER (
    PARTITION BY 分组字段
    ORDER BY 排序字段 DESC
  ) AS...
FROM
  ...

三种排序函数的区别：

函数	作用
RANK	排名，遇并列会跳过名次，例如：1,2,2,4,5
DENSE_RANK	排名，遇并列不跳过名次，例如：1,2,2,3,4
ROW_NUMBER	连续序号，例如：1,2,3,4,5

LAG、LEAD 用于相邻行的比较。

事务

事务用于保证一组操作要么全部成功，要么全部失败。

ACID 特性

原子性（Atomicity）：事务是整体，要么全部执行，要么都不执行
一致性（Consistency）：事务执行前后，数据应保持一致状态，如银行转账前后两个账户总额不变
隔离性（Isolation）：多个事务同时执行时，它们之间不会互相影响
持久性（Durability）：事务一旦提交，对数据库的改变是永久的

基本使用

打开事务：BEGIN 或 START TRANSACTION
执行操作：INSERT、UPDATE、DELETE
确认修改：COMMIT
撤销操作：ROLLBACK

示例

回滚示例：打开一个终端执行 BEGIN 后修改数据再 ROLLBACK，数据会恢复为原始值。

原子性与一致性示例：打开两个终端，在终端 1 中执行 BEGIN 后修改数据，此时终端 1 能看到修改，但终端 2 看不到（因为未提交）。终端 1 执行 COMMIT 后，终端 2 才能看到更新。

隔离性示例：终端 1 执行 BEGIN 后修改某行并持有行锁。终端 2 尝试更新同一行会阻塞等待。终端 1 执行 COMMIT 后，终端 2 的阻塞解除，更新生效。

持久性：事务提交（COMMIT）后，修改将永久保存到数据库。

注意事项

InnoDB 引擎支持事务，MyISAM 等其他引擎不支持
Python 操作数据库时默认开启事务，需手动 COMMIT 提交增删改操作
终端客户端（如 MySQL CLI）会默认自动提交，可通过 SET autocommit=0 关闭

事务的意义

保证操作的原子性，例如银行转账必须完整执行，不允许部分成功。

其他

数据库备份与恢复

导出数据库：mysqldump –u root –p 数据库名 > python.sql

导入数据库：mysql -u root –p 新数据库名 < python.sql

复制表

只复制结构：CREATE TABLE b LIKE a
复制结构及数据：CREATE TABLE b SELECT * FROM a

连接表

MySQL 不支持全连接，使用 JOIN 需要搭配 ON，默认 JOIN 是 INNER JOIN。

连接两个含有相同字段的表格可使用 UNION，UNION 会去重，UNION ALL 直接合并。

操作符

符号	描述
`=`	等于
`<>` `!=`	不等于
`>` `>=`	大于大于等于
`<` `<=`	小于小于等于
`BETWEEN`	在某范围内
`IN`	在指定集合中
`IS NULL`	为空
`IS NOT NULL`	不为空
`AND`	并且
`OR`	或者
`NOT`	否定
`ASC`	升序（默认）
`DESC`	降序

通配符

符号	描述
`LIKE`	针对文本值字段的匹配
`%`	任何字符任意次数
`_`	任何字符出现一次
`[]`	字符集合中的任意一个

Linux 系统下安装 MySQL

Ubuntu 可直接 sudo apt-get install mysql-server

查看运行状态：/etc/init.d/mysql status

如果能直接 apt install 是最好的，可以减少很多麻烦。问题是只能安装仓库中已有的版本。

也可以下载二进制文件安装：

下载地址：https://downloads.mysql.com/archives/community/

1
2
3
4
5
6
7


sudo tar -xvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.xz
sudo mv mysql-5.7.26-linux-glibc2.12-x86_64 /usr/local/mysql
sudo groupadd mysql
sudo useradd -r -g mysql -s /bin/false mysql
sudo chown -R mysql:mysql /usr/local/mysql
cd /usr/local/mysql
sudo bin/mysqld --initialize --user=mysql

常见问题

新版本 Ubuntu 安装 MySQL 5.7.x 可能遇到各种问题：

缺少 libtinfo5：参考 https://www.cnblogs.com/mili0601/p/18652590 和 https://blog.csdn.net/aajjw/article/details/111144100
缺少 libaio.so.1：参考 https://blog.csdn.net/qq_36514197/article/details/118522552

完全卸载 MySQL：

1
2
3
4


sudo apt-get purge mysql-\*
sudo rm -rf /etc/mysql/ /var/lib/mysql
sudo apt autoremove
sudo apt autoclean

Windows 系统下安装 MySQL

my.ini 配置文件

配置文件通常在 C:\ProgramData\MySQL\MySQL Serverx.x 目录下，或自行在解压路径下新建：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22


[mysql]
# 设置 MySQL 客户端默认字符集
default-character-set=utf8

[mysqld]
# 设置 3306 端口
port = 3306

# 设置 MySQL 的安装目录
basedir=$自己的解压路径$

# 设置 MySQL 数据库的数据存放目录（MySQL 8.0+ 不需要以下配置）
datadir=$自己的解压路径$\data

# 允许最大连接数
max_connections=20

# 服务端使用的字符集
character-set-server=utf8

# 默认存储引擎
default-storage-engine=INNODB

设置密码

打开 CMD，输入 mysql -u root -p，默认无密码直接回车。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


-- 切换到 mysql 数据库
USE mysql;

-- 设置 root 用户密码为 123456
UPDATE USER
  SET authentication_string = PASSWORD ( '123456' )
WHERE
  USER = 'root';

-- 刷新权限
FLUSH PRIVILEGES;

设置远程连接

1
2
3
4
5
6


USE mysql;

-- 设置用户远程访问
GRANT ALL ON *.* TO user@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

FLUSH PRIVILEGES;

安装完成后需配置环境变量，才能在 CMD 中使用 mysql 命令。

拓展与后续学习建议

索引优化：深入学习复合索引的最左前缀原则、覆盖索引、回表查询等概念
查询优化：学习使用 EXPLAIN 分析执行计划，理解 type、key、Extra 等关键字段含义
分区表：了解 MySQL 分区表的原理和使用场景
数据库安全：学习用户权限精细化管理、SQL 注入防护、数据加密存储等安全相关知识
数据库监控：了解慢查询日志、性能 _schema、information_schema 的使用方法