MySQL基本使用

数据库常识

数据库的关系模型

什么是关系数据库？

关系数据库是一种用于存储相互关联的数据点并提供数据点访问的数据库。它采用关系模型，直接、直观地在表中展示数据。

在关系数据库中，表中的每一行都代表一条记录，每条记录都具有一个唯一的 ID（又被称为键），而表中的列则用于存储数据的属性——每条记录的每一个属性通常都有一个值。籍此，用户可以轻松在数据点之间建立关联。

数据库的关系模型（Relational Model）是当前最广泛使用的数据模型，它由 IBM 的 E.F. Codd 在 1970 年提出。该模型使用二维表格来组织数据，具有强大的理论基础和良好的实用性。[1]

举个例子：这是用来描述学生信息的关系表：

student_id (PK) name age
1001 Alice 20
1002 Bob 21
1003 Charlie 19
  • student_id是学生的键

这是可选课程表：

course_id (PK) course_name credits
C001 Database Systems 3
C002 Operating Systems 4
C003 Networks 3
  • course-id是课程的键

现在这些学生们需要选课，于是就有了选课表：

student_id (FK) course_id (FK) grade
1001 C001 A
1001 C002 B
1002 C001 A-
1003 C003 B+

通过上表，借由学生id就可以查询到课程id进而查询课程信息，又可以查询到学生信息，这样就建立起了不同关系表之间的映射关系。

数据类型

关系数据库支持的数据类型主要包括数值、字符串、日期和时间、布尔、二进制。

通常来说，BIGINT能满足整数存储的需求，VARCHAR(N)能满足字符串存储的需求，这两种类型是使用最广泛的。

SQL

SQL（Structured Query Language），即结构化查询语言，是一种用于管理关系型数据库的标准编程语言。不同的数据库都支持SQL，所以使用SQL这一种标准语言就可以操作不同的数据库。

除了 SQL 标准之外，大部分 SQL 数据库程序都拥有它们自己的私有扩展，这些扩展不能用在其他的数据库中。但是如果只使用SQL的核心功能的话，那不会有太大问题，常用的功能是相互兼容的。

SQL 定义了以下五种语句：

类别 关键作用 常见关键字
DDL 定义结构，通常由管理员执行 CREATE, DROP, ALTER
DML 操作数据，属于应用程序的日常操作 INSERT, UPDATE, DELETE
DQL 查询数据，供用户查询使用，最为频繁 SELECT
DCL 控制权限 GRANT, REVOKE
TCL 管理事务 COMMIT, ROLLBACK

SQL 的关键字不区分大小写，但是对于表名和列名，不同数据库的规定不同，所以最好关键词一律大写。表名和列名一律小写。

关系模型构成

用于唯一区分不同记录的字段就是主键。不能使用业务相关的字段作为主键。

将数据与另一张表联系起来的字段称为外键。

索引：加快查询速度。

在关系数据库中，索引是用于提高查询效率的重要数据结构。索引可以让数据库在查询时不必遍历整个表，而是快速定位到符合条件的记录，从而显著提升检索速度。

然而，索引也有其局限性。索引的主要缺点是在执行插入、更新和删除操作时，数据库需要同时调整所有相关的索引，这会增加系统开销。索引数量越多，数据修改的速度就越慢。因此，在设计数据库时，必须在查询优化和修改性能之间权衡。

数据库会自动为主键创建索引，主键索引的查询效率最高，因为主键保证每条记录的唯一性。

唯一索引：可以通过命令为需要确保唯一的数据创建唯一索引，或者添加唯一约束。

查询数据

基本查询

SELECT <定义列的名字> FROM <表名>

例如：SELECT * FROM students; 查询students表中的所有列

条件查询

SELECT * FROM <表名> WHERE <条件表达式>

使用WHERE指定搜索条件。条件表达式可以用

  • <条件1> AND <条件2>表达满足条件1并且满足条件2
  • <条件1> OR <条件2>表示满足条件1或者满足条件2。

应当注意，AND比OR有更高的运算优先级，某些情况下需要使用圆括号()指明运算先后顺序。

BETWEENT AND：查询位于两个值之间的数据，两边都是闭区间。

IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。例如：

1
2
3
4
SELECT cust_id, cust_name
FROM customers
WHERE cust_id IN (10001, 10003)
ORDER BY cust_name;

把 customers 表中的cust_id为 10001 或 10003 的数据查询出来。IN的作用实际上和OR没有差别，它的优点体现在：

  • IN操作符的语法更清楚且更直观；
  • 在使用IN时，计算的次序更容易管理（因为使用的操作符更少）；
  • IN操作符一般比OR操作符清单执行更快；
  • IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建立WHERE子句。

还有一个关键字是NOT。WHERE子句中的NOT操作符有且只有一个功能，那就是否定它之后所跟的任何条件。例如：

1
WHERE vend_id NOT IN (1002, 1003)

筛选所有 vend_id 不是1002或1003的数据。NOT实际上是MySQL的特性：

MySQL支持使用NOT对IN、BETWEEN和EXISTS子句取反，这与多数其他DBMS允许使用NOT对各条件取反有很大的差别。

投影查询

SELECT 列1, 列2, 列3 FROM ...

这样只会返回选中的列。

排序

SELECT id, name, gender, score FROM students ORDER BY score DESC, gender;

DESC表示倒序，默认为ASC即升序。

ORDER BY要放到WHERE的后面。

分页查询

在 SQL 中，分页查询用于从大型数据集中按页提取一部分数据，以便在应用程序或网页上逐步显示。

分页实际上就是从结果集中“截取”出第M~N条记录。这个查询可以通过LIMIT <N-M> OFFSET <M>子句实现。我们先把所有学生按照成绩从高到低进行排序：

例如：

1
2
3
4
5
-- 查询第1页:
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;

上面的语句表示：选取students中 id, name, gender, score的列，按照分数从高到低排序，最多只取前3条（第1页的3条记录，从第0条开始）。

所以LIMIT语句其实是一个截取的过程，输出的结果在外界看来好像实现了分页效果。

  • LIMIT总是设定为pageSize；
  • OFFSET计算公式为pageSize * (pageIndex - 1)。

如果查询结果为空，则会返回Empty set。OFFSET是可选的，默认为0。

聚合查询

对于统计总数、平均数这类计算，SQL提供了专门的聚合函数，使用聚合函数进行查询，就是聚合查询，它可以快速获得结果。

聚合函数 说明
COUNT() 统计数量
SUM() 求总和
AVG() 求平均值
MAX() 求最大值
MIN() 求最小值

（这里的COUNT()以行（组）为单位进行计数）

经常与之结合使用的还有:

  • CEILING(): 向上取整
  • FLOOR(): 向下取整

例如SELECT MAX(score), MIN(score) FROM students;用于计算学生的最高分和最低分。

计算的结果仍然以二维表的形式给出，即使只有一个数据。

使用聚合查询时可以给结果起一个别名，使用AS标记：

1
SELECT COUNT(*) AS num FROM students;

返回结果：

1
2
3
4
5
6
+-----+
| num |
+-----+
|  10 |
+-----+
1 row in set (0.024 sec)

其中最后一行的意思是：

  • 1 row in set：查询结果中有 1 行数据（就是你上面看到的那一行 num = 10）；
  • (0.024 sec)：这条 SQL 查询语句的执行时间是 0.024 秒。

聚合查询语句同样可以和条件语句等一起使用。

要特别注意：如果聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL：

分组

关键词：GROUP BY。分组操作常常需要把分组标准也加入表中，例如：

1
2
3
4
-- 第一列是性别，第二列是对应的数量，起一个新名字total
SELECT gender, COUNT(*) AS total
FROM students
GROUP BY gender;

查询结果：

1
2
3
4
5
6
7
+--------+-------+
| gender | total |
+--------+-------+
| M      |     5 |
| F      |     5 |
+--------+-------+
2 rows in set (0.013 sec)

注意：分组时，SELECT中的列，要么是GROUP BY里的，要么就得用聚合函数。这也很好理解，因为查询的结果是按照我们指定的字段进行分组的，如果引用了和分类无关的字段，这些字段该放到哪里？表格中根本没有为它们提供位置，所以肯定会报语法错误。

多表查询

现在我们手上有两个表格：班级表

id name
1 class1
2 class2
3 class3
4 class4

和学生表

id class_id name gender score
1 1 ming M 90
2 1 hong F 95
3 1 jun M 88
4 1 mi F 73
5 2 bai F 81
6 2 bing M 55
7 2 ling M 85
8 3 xin F 91
9 3 wang M 89
10 3 li F 85

这两个表格通过班级的 id 关联起来。

最简单的多表查询是笛卡尔积查询，数据量容易爆炸，基本用不上。

更有实际意义的是SQL JOIN连接查询。[2]

先看看分类：

INNER JOIN 内连接

依据两个表的重合部分进行连接，当然也可以用于同一个表的内部

1
2
3
4
mysql> SELECT A.id, A.name, B.name
    -> FROM classes A
    -> JOIN students B
    -> ON A.id = B.class_id;

这样就利用classes和students中的共同部分：班级id 把两个表合并了。students表的class_id列与classes表的id列相同的行需要连接。结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
+----+--------+------+
| id | name   | name |
+----+--------+------+
|  1 | class1 | ming |
|  1 | class1 | hong |
|  1 | class1 | jun  |
|  1 | class1 | mi   |
|  2 | class2 | bai  |
|  2 | class2 | bing |
|  2 | class2 | ling |
|  3 | class3 | xin  |
|  3 | class3 | wang |
|  3 | class3 | li   |
+----+--------+------+
10 rows in set (0.015 sec)

LEFT JOIN 左连接

获取左表中的所有记录，即使在右表没有对应匹配的记录。

1
2
3
4
5
-- 左连接：显示A表所有记录，即使在B表中没有匹配
-- 没有匹配的项则以NULL值代替
SELECT A.col1, B.col2
FROM A
LEFT JOIN B ON A.id = B.a_id;

初看起来没什么用，但是其实它在处理不匹配的数据时起着决定性作用，尤其在有“数据缺失”或“关联关系不完整”的情况下。

举个例子：把所有的班级及其学生放在同一个表格中，可以这样写：

1
2
3
SELECT classes.name, students.name
FROM classes
LEFT JOIN students ON classes.id = students.class_id;

结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
+--------+------+
| name   | name |
+--------+------+
| class1 | mi   |
| class1 | jun  |
| class1 | hong |
| class1 | ming |
| class2 | ling |
| class2 | bing |
| class2 | bai  |
| class3 | li   |
| class3 | wang |
| class3 | xin  |
| class4 | NULL |
+--------+------+

class4没有学生，但是它已经出现在了表格中。这样能够找到没有关联的孤儿数据。

RIGHT JOIN 右连接

1
2
3
4
-- 和左连接相反，返回 B 表所有记录，A 表不匹配的显示 NULL。
SELECT A.col1, B.col2
FROM A
RIGHT JOIN B ON A.id = B.a_id;

CROSS JOIN 交叉连接

每一条 A 表记录都会和 B 表的每一条组合，结果是 A 表记录数 × B 表记录数（笛卡尔积），用得不多。

1
2
3
SELECT A.col1, B.col2
FROM A
CROSS JOIN B;

MySQL 没有内建的 FULL JOIN（全连接）语法关键词。想实现全连接，要用LEFT JOIN + RIGHT JOIN + UNION：

1
2
3
4
5
6
7
8
9
10
11
-- 模拟 FULL OUTER JOIN
SELECT A.id, B.id
FROM A
LEFT JOIN B ON A.id = B.id

UNION

SELECT A.id, B.id
FROM A
RIGHT JOIN B ON A.id = B.id;

通配符

查询操作中的所有操作符都是针对已知值进行过滤的，有时候并不好用，例如，怎样搜索产品名中包含文本anvil的所有产品？用简单的比较操作符肯定不行，必须使用通配符。

通配符（wildcard） 用来匹配值的一部分的特殊字符。

搜索模式（search pattern） 由字面值、通配符或两者组合构成的搜索条件。

LIKE操作符

LIKE 用于告诉 MySQL 接下来将会使用通配符进行匹配。LIKE 一定要搭配通配符使用， 否则 MySQL 会认为使用的是完全匹配而非模糊匹配。

百分号(%)通配符

百分号（%）通配符 ：用于匹配多个字符，例如：

1
2
3
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE 'jet%';

这样就找出了找出所有以词jet起头的产品:

1
2
3
4
5
6
7
+---------+--------------+
| prod_id | prod_name    |
+---------+--------------+
| JP1000  | JetPack 1000 |
| JP2000  | JetPack 2000 |
+---------+--------------+
2 rows in set (0.017 sec)

'jet%'可以这样理解：%代表的其实是一个字符串剩余的其他字符，整个字符串必须以jet开头。类似地，%jet%代表的是包含jet的字符串，因为jet的前后都有字符。注意%匹配的也可以是空字符和空格，但是不能匹配NULL。

下划线(_)通配符

下划线的用途与%一样，但下划线只匹配单个字符而不是多个字符。

通配符虽然使用方便，但是通配符搜索的速度比一般的操作符要慢，所以不可以滥用通配符。在能达到相同目的睇情况下，应该优先使用其他操作符。

正则表达式

正则表达式是用来匹配文本的特殊的串（字符集合），功能非常强大。。MySQL用WHERE子句对正则表达式提供了初步的支持，只是正则表达式的一个很小的子集。

关键字：REGEXP

字符匹配

1
2
3
4
SELECT prod_name
FROM products
WHERE prod_name REGEXP '.000'
ORDER BY prod_name;

这里使用了正则表达式.000，小数点表示匹配任意一个字符，因此 1000 和 2000 都会被匹配。

正则表达式匹配默认是不区分大小写【1的，如果需要区分，在正则表达式之前加上BINARY，例如REGEXP BINARY 'JetPack .000'。

OR 匹配

搜索两个字符串之一，使用|。例如：REGEXP '1000|2000'匹配所有包含 1000 或 2000 的字符串。可以连续使用多个|。

匹配多个字符之一

[]可以匹配中括号中出现的字符之一：

1
2
3
4
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[123] Ton'
ORDER BY prod_name;

结果：

1
2
3
4
5
6
+-------------+
| prod_name   |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+

成功匹配 1 或 2 或 3。这是另一种形式的 OR 语句。[^123]可以匹配除了这三个字符以外的任何东西。

匹配范围

属于上一个例子的加强版：[0-9]匹配数字 0 到 9，不必把数字一个一个列举出来了。

[a-z]匹配任意英文字母。

特殊字符

匹配任意字符可以用小数点.，那匹配小数点呢？

对于这种特殊字符，需要在前面加上\\转义(escaping)来匹配它本身，和Java中的处理方法相同。

字符类

有预先定义待的字符集可供使用：

匹配多个实例

对匹配的次数加以指定。例如：

  • '[[:digit:]]{4}' 匹配连在一起的任意4位数字。

定位符

用于匹配特定位置的文本。

  • ^ 文本的开始
  • $ 文本的结尾
  • [[:<:]] 词的开始
  • [[:>:]] 词的结尾

修改数据

CRUD：Create、Retrieve、Update、Delete

增、删、改、查

前面的SELECT用于查，增删改对应的操作是：

  • INSERT
  • UPDATE
  • DELETE

INSERT

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

有默认值的字段在插入的时候可以不屑，自动附上默认值，但是没有默认值的话必须显式赋值。

可以一次添加多个记录，不同记录用逗号分隔。

UPDATE

1
UPDATE 表名 SET 字段1=值1, 字段2=值2, ... WHERE ...;

WHERE可以涵盖多个对象，同时修改多个对象的值。

如果不写WHERE条件，整张表的记录都会被修改。

DELETE

1
DELETE FROM 表名 WHERE ...;

如果不写WHERE条件，整张表的记录都会被删除。

MySQL

基本语句

新建数据库：CREATE DATABASE test

删除数据库DROP DATABASE test;

切换到当前的数据库USE test;

列举当前数据库的所有表：SHOW TABLES;

查看表的结构：DESC 表名;

查看创建表的SQL语句：SHOW CREATE TABLE 表名;

创建表CREATE TABLE 表名

删除表DROP TABLE 表名

参考链接

  1. https://www.oracle.com/cn/database/what-is-a-relational-database/ ↩︎
  2. https://zhuanlan.zhihu.com/p/68136613 ↩︎
Database
MySQL基本使用
https://kznep19.blog/2025/05/06/MySQL基本使用/
作者
banyee
发布于
2025年5月6日
许可协议