# 什么是数据库

数据库（ $Database$ ）是按照数据结构来组织、存储和管理数据的仓库。
$RDBMS$ 即关系数据库管理系统 ( $Relational~Database~Management~System$ ) 的特点：

数据以表格的形式出现
每行为各种记录名称
每列为记录名称所对应的数据域
许多的行和列组成一张表单
若干的表单组成 $database$

# RDBMS 术语

数据库: 数据库是一些关联表的集合。
数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
列: 一列 (数据元素) 包含了相同类型的数据，例如邮政编码的数据。
** 行：** 一行（元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
** 外键：** 外键用于关联两个表。
复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
** 索引：** 使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

键 (key): 键的值在当前列中具有唯一性。

# 数据类型

# 数值类型

# 日期 / 时间类型

每个时间类型有一个有效值范围和一个 "零" 值，当指定不合法的 MySQL 不能表示的值时使用 "零" 值。

# 字符串类型

# 注意

char(n) 和 varchar(n) 的括号的 $n$ 代表字符的个数
char 和 varchar 类型类似，但保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

# 枚举和集合类型

enum ：枚举类型，用于存储单一值，可以选择一个预定义的集合
set ：集合类型，用于存储多个值，可以选择多个预定义的集合

# 空间数据类型

GEOMETRY , POINT , LINESTRING , POLYGON , MULTIPOINT , MULTILINESTRING , MULTIPOLYGON , GEOMETRYCOLLECTION : 用于存储空间数据（地理信息、几何图形等）。

# 数据库的命令

mysql -u 用户名 -p ：连接数据库
use 数据库名 ：选择要操作的 $MySQL$ 数据库，后续的命令都只针对该数据库
show databases; ：列出所有数据库
show tables; ：显示指定数据库的所有表（ use 数据库 是这个命令的前置操作）
show columns from 数据表 ：显示数据表的属性、属性类型、主键信息、是否为 $NULL$ / 默认值等信息（针对一个表）
show index from 数据表 ：显示数据表的详细索引信息，包括主键
create database 数据库名; ：创建数据库
- 如果存在该名字的数据库，将会报错
- 可以使用 create database if not exists 数据库名;
drop database 数据库名; ：删除数据库
- 如果不存在该名字的数据库，将会报错
- 可以使用 drop database if exists 数据库名;

# 创建数据表

创建数据库需要以下信息

表名
表字段名
定义每个表字段的数据类型

# 语法

create table 表名 (
	列名1 数据类型,
	列名2 数据类型,
	...
);

# 实例

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    birthdate DATE,
    is_active BOOLEAN DEFAULT TRUE
);

id ：用户 $id$ ，整数类型，自增长，作为主键
username ：用户名，变长字符串，不许为空
is_active ：用户是否激活，布尔类型，默认为 $true$

# 删除数据表

drop table 表名; ：直接删除表，不检查是否存在
drop table if exists 表名; ：检查是否存在，如果存在才删除
truncate table 表名; ：删除表中所有数据，但保留表的结构

# 针对表中数据

# 插入数据

insert into 表名 (列名1, 列名2, ...)
value (数值1, 数值2, ...);

-- 例
insert into user (name, email, birthDate, isActive)
values ('duoxichangan', '3200513041@qq.com', '2006-03-20', true)

如果你要插入所有列的数据，可以省略列名。
对于自增长数据， $NULL$ 是用于自增长列的占位符，表示系统将为 id 列生成一个唯一的值。

# 查询数据

select 列名1, 列名2, ...
from 表名
[where condition]
[order by 列名 [ASC | DESC]]
[limit number];

[WHERE condition] ：可选语句，用于指定过滤条件
[ORDER BY 列名 [ASC | DESC]] ：可选语句，用于指定结果集的排序顺序，默认升序（ $ASC$ ）
LIMIT number ：可选语句，限制返回的行数

select *
-- 选中所有行
from user
where isActive = true
order by birthDate DESC
limit 10;

# 条件语句 WHERE

在 $where$ 子句中，可以使用各种条件运算符（如 = , < , > , <= , >= , != ），逻辑运算符（如 AND , OR , NOT ），以及通配符（如 % ）等。

-- 使用 AND 运算符和通配符（模糊匹配 LIKE）
SELECT * FROM users WHERE username LIKE 'j%' AND is_active = TRUE;

-- 使用 OR 运算符
SELECT * FROM users WHERE is_active = TRUE OR birthdate < '1990-01-01';

-- 使用 IN 子句
SELECT * FROM users WHERE birthdate IN ('1990-01-01', '1992-03-15', '1993-05-03');

-- 使用 BETWEEN
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

# 模糊搜索 LIKE

1 2	-- % 代表若干个字符, _ 代表一个字符, COLLATE utf8mb4_general_ci 表示忽略大小写 SELECT * FROM employees WHERE last_name LIKE 'sm_i%' COLLATE utf8mb4_general_ci;

# 连接操作符 UNION

$UNION$ 操作符用于连接两个以上的 $SELECT$ 语句的结果组合到一个结果集合，并去除重复的行。 $UNION$ 操作符必须由两个或多个 $SELECT$ 语句组成，每个 $SELECT$ 语句的列数和对应位置的数据类型必须相同。

SELECT 列名1, 列名2, ...
FROM 表名1
WHERE condition1
UNION
SELECT 列名1, 列名2, ...
FROM 表名22
WHERE condition2
[ORDER BY column1, column2, ...];

如果想保留重复行的话，可以把 UNION 换成 UNION ALL

# 排序语句 ORDER BY

-- 单列排序
order by birthDate ASC;

-- 先a排再b排
order by birthDate DESC, number ASC;

-- 使用数字表示列的位置(按第 x 列排)
order by 3 DESC, 1 ASC

-- 使用表达式排序
SELECT productName, price * discountRate AS discountedPrice
FROM products
ORDER BY discountedPrice DESC;

-- 空值排先后
SELECT product_name, price
FROM products
ORDER BY price DESC NULLS LAST;
-- 或者
ORDER BY price DESC NULLS FIRST;

# 分组语句 GROUP BY

SELECT 列名, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY 表名;

aggregate_function(column2) ：对分组后的每个组执行的聚合函数。

1
2
3

SELECT customerId, SUM(orderAmount) AS totalAmount
FROM orders
GROUP BY customerId;

我们使用 GROUP BY customerId 将结果按 customerId 列分组，然后使用 SUM(orderAmount) 计算每个组中 orderAmount 列的总和。
AS totalAmount 是为了给计算结果取一个别名，使查询结果更易读。

GROUP BY 子句通常与聚合函数一起使用，因为分组后需要对每个组进行聚合操作
SELECT 子句中的列通常要么是分组列，要么是聚合函数的参数
可以使用多个列进行分组，只需在 GROUP BY 子句中用逗号分隔列名即可

mysql> SELECT * FROM employee_tbl;
+----+--------+------------------+--------+
| id | name   | date             | signin |
+----+--------+------------------+--------+
|  1 | 小明 | 2016-04-22 15:25:33 |      1 |
|  2 | 小王 | 2016-04-20 15:25:47 |      3 |
|  3 | 小丽 | 2016-04-19 15:26:02 |      2 |
|  4 | 小王 | 2016-04-07 15:26:14 |      4 |
|  5 | 小明 | 2016-04-11 15:26:40 |      4 |
|  6 | 小明 | 2016-04-04 15:26:54 |      2 |
+----+--------+-------------------+-------+
6 rows in set (0.00 sec)

mysql> select name, count(*) from employeeTbl
group by name;
+--------+--------+
| name | COUNT(*) |
+--------+--------+
| 小丽 |        1 |
| 小明 |        3 |
| 小王 |        2 |
+--------+--------+
3 rows in set (0.01 sec)

# 使用 WITH ROLLUP

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计 (SUM, AVG, COUNT...) 。如果要将以上的数据表按名字进行分组，再统计每个人登录的次数，则：

mysql> SELECT name, SUM(signin) as signinCount
FROM employeeTbl
GROUP BY name WITH ROLLUP;
+------+--------------+
| name | signin_count |
+------+--------------+
| 小丽 |            2 |
| 小明 |            7 |
| 小王 |            7 |
| NULL|           16 |
+------+--------------+
4 rows in set (0.00 sec)

其中记录

NULL

表示所有人的登录次数。

# coalesce 语法：

1	select coalesce(a,b,c);

参数说明：如果 a == null ，则选择

b

；如果 b == null , 则选择

c

；如果 a != null ，则选择

a

；如果

a,b,c

都为

NULL

，则返回为

NULL

。

以下实例中如果名字为空我们使用总数代替：

mysql> SELECT coalesce(name, '总数'), SUM(signin) as signin_count
FROM employeeTbl
GROUP BY name WITH ROLLUP;
+-----------------------+--------------+
| coalesce(name, '总数') | signin_count |
+-----------------------+--------------+
| 小丽                   |            2 |
| 小明                   |            7 |
| 小王                   |            7 |
| 总数                   |           16 |
+-----------------------+--------------+
4 rows in set (0.01 sec)

# 更新数据

1
2
3

update 表名
set 列名 = 值1, 列2 = 值2, ...
[where condition];

可以同时更新一个或多个字段
可以在一个单独表中同时更新数据

-- 更新单个列
UPDATE employees
SET salary = 60000
WHERE employee_id = 101;

-- 更新多个列
UPDATE orders
SET status = 'Shipped', ship_date = '2023-03-01'
WHERE order_id = 1001;

-- 表达式更新
UPDATE products
SET price = price * 1.1
WHERE category = 'Electronics';

-- 更新使用字查询
UPDATE customers
SET total_purchases = (
    SELECT SUM(amount)
    FROM orders
    WHERE orders.customer_id = customers.customer_id
)
WHERE customer_type = 'Premium';

注意： 在使用 $UPDATE$ 语句时，请确保你提供了足够的条件来确保只有你想要更新的行被修改。如果不提供 $WHERE$ 子句，将更新表中的所有行，可能导致不可预测的结果。

# 删除数据

1 2	delete from 表名 [where condition];

如果没有指定 $where$ 子句，表中的所有记录将被删除
可以在 $where$ 子句中指定任何条件
可以在单个表中一次性删除记录

delete from customers
where customerId in (
	select customerId from orders
	where orderDate < '2023-01-01'
);

数据库