前端必知必会 --- 数据库1. 定义数据库通俗讲是存储数据的仓库。 2. 数据库类型 1. SQL语句分类 2. S

👊：重点核心知识

🔨：官方文档

📕：概念定义

🧢：实践操作

🙋‍：清晰记忆的概念

一、基础概念

1. 定义

🔨 数据库是结构化信息或数据（一般以电子形式存储在计算机系统中）的有组织的集合，通常由数据库管理系统（DBMS）来控制。在现实中，数据、DBMS及关联应用一起被称为数据库系统，通常简称为数据库。 ----- Oracle官网文档

数据库通俗讲是存储数据的仓库。

2. 数据库类型

2.1 关系型数据库

📕 关系型数据库通常会创建很多二维数据表，表之间互相关联，形成一对一、一对多、多对多等关系；之后可以利用SQL语句在多张表中查询数据，并且关系型数据库支持事务，对数据访问更安全。常见数据库有MySQL、Oracle、DB2、SQL Server、Postgre SQL等。

2.2 非关系型数据库

📕 非关系型数据库（NoSQL）存储数据更加自由，因为是基于Key-Value的对应关系，并且查询的过程中不需要经过SQL解析，所以性能更高，但是不支持事务，需要编写时做保证原子性的操作。常见的非关系型数据库有MongoDB、Redis、Memcached、HBase等。

2.3 业务场景

🧢 根据项目进行分析，如果进行后端接口开发，使用关系型数据库为主。如果是进行存储爬虫数据及数据缓存，可以使用非关系型数据库。

二、`SQL`基础

1. `SQL`语句分类

1.1 `DDL`

🙋‍DDL(Data Definition Language)数据库定义语言，对数据库或表进行：创建、删除、修改操作。

1.2 `DML`

🙋‍DML(Data Manipulation Language)数据操作语言，对表进行：添加、删除、修改操作。

1.3 `DQL`

🙋‍DQL(Data Query Language)数据查询语言，对数据库进行：查询记录操作。👊

1.4 `DCL`

🙋‍DCL(Data Control Language)数据控制语言，对数据库、表格的权限进行相关访问控制操作。

2. `SQL`数据类型

MYSQL支持的数据类型有：数字类型、日期和时间类型、字符串类型、空间类型和JSON数据类型

2.1 数字类型

整数数字类型：INTEGER INT SMALLINT TINYINT MEDIUMINT BIGINT
浮点数字类型：FLOAT(4字节) DOUBLE(8字节)
精准数字类型：DECIMAL NUMERIC(DECIMAL是NUMERIC的实现形式)

2.2 日期类型

类型	格式	范围
`YEAR`	`YYYY`	`1901`到`2155`，和`0000`
`DATE`	`YYYY-MM-DD`	`1000-01-01`到`9999-12-31`
`DATETIME`	`YYYY-MM-DD hh:mm:ss`	`1000-01-01 00:00:00` 到`9999-12-31 23:59:59`
`TIMESTAMP`	`YYYY-MM-DD hh:mm:ss`	`1970-01-01 00:00:01` 到`2038-01-19 03:14:07`

DATETIME和TIMESTAMP可以包括微秒（6位）精度的后小数秒一部分。比如1000-01-01 00:00:00.000000。

2.3 字符串类型

CHAR创建表时为固定长度，长度0~255。被查询时，会删除后面的空格
VARCHAR是可变长度，长度0~65535。被查询时，不会删除后面的空格
BINARY和VARBINARY类型用于存储二进制字符串，存储的时字节字符串
BLOB存储大的二进制类型
TEXT存储大的字符串类型

3. `SQL`关键字

名称	作用	备注
主键（`PRIMARY KEY`）	每一条记录的唯一性，不能重复，不能为空	主键是表中唯一的索引；开发中主键字段应和业务无关
唯一（`UNIQUE`）	字段值不能重复	-
不能为空（`NOT NULL`）	字段值不能为`null`	-
默认值（`DEFAULT`）	字段值的默认值	-
自动递增（`AUTO_INCREMENT`）	某些字段不设置值时，也会自动递增	-
外键约束（`FOREIGN KEY`）	与其他表进行关联的字段	`FOREIGN KEY (user_id) REFERENCES courses(id)`

🚹外键存在时，更新和删除数据：需要修改ON DELETE或ON UPDATE的值。可以给更新或删除时设置以下值：

RESTRICT(默认值)：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有会报错，不允许更新或删除。

NO ACTION：与RESTRICT一致。

CASCADE：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话，更新：会更新对应的记录；删除：关联的记录一起被删除。

SET NULL：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话，将对应的值设置为NULL。

4. 数据库操作

# 建议SQL语句关键字使用大写
-----------------------------------------
# 1. 查看当前数据库
# 查看所有数据库
SHOW DATABASES;

# 使用数据库
USE XXXX;

# 查看正在使用的数据库
SELECT DATABASE();
-----------------------------------------
# 2. 创建新的数据库
CREATE DATABASE xxxx; # 创建xxxx数据库
CREATE DATABASE IF NOT EXISTS xxxx; # 如果不存在xxxx则创建
CREATE DATABASE IF NOT EXISTS xxxx
				DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; # 创建时指定编码
-----------------------------------------	
# 3. 删除数据库
DROP DATABASE xxxx;
DROP DATABASE IF EXIT xxxx;
-----------------------------------------
# 4. 修改数据库
ALTER DATABASE xxxx CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;

3. 数据表操作

# 1. 查看数据表
# 查看所有的数据表
SHOW TABLES;

# 查看某一个表结构
DESC xxx;
-----------------------------------------
# 2. 创建数据表
CREATE TABLE IF NOT EXISTS xxx (
    id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20) NOT NULL,
    age INT DEFAULT 0,
    telPhone VARCHAR(20) DEFAULT '' UNIQUE NOT NULL
);
-----------------------------------------
# 3. 删除数据表
DROP TABLE users;
DROP TABLE IF EXISTS users;
-----------------------------------------
# 4. 修改表
# 4.1 修改表名
ALTER TABLE xxx RENAME TO XXX;
# 4.2 添加新列
ALTER TABLE xxx ADD publishTime DATETIME;
# 4.3 添加新的外键
ALTER TABLE xxx ADD 
	FOREIGN KEY (xxx_id) REFERENCES yyy(id) 
	ON UPDATE CASCADE 
	ON DELETE CASCADE;
# 4.4 删除一列数据
ALTER TABLE xxx DROP publishTime;
# 4.5 删除外键
ALTER TABLE xxx DROP FOREIGN KEY xxx_id;
# 4.6 修改列的名称
ALTER TABLE xxx CHANGE telPhone phoneNum VARCHAR(20) DEFAULT '' UNIQUE NOT NULL;
# 4.7 修改列的数据类型
ALTER TABLE xxx MODIFY id INT;
-----------------------------------------
# 5. 插入数据
INSERT INTO xxx (name, age, telPhone) VALUES ('kwan', 18, '888888');
-----------------------------------------
# 6. 删除数据
# 6.1 删除表中所有数据
DELETE FROM xxx;
# 6.2 删除符合条件的数据
DELETE FROM xxx WHERE id = 1;
-----------------------------------------
# 7. 修改数据
# 7.1 修改表中所有数据
UPDATE xxx SET name = 'dean';
# 7.2 修改符合条件的数据
UPDATE xxx SET name = 'dean' WHERE id = 1;
# 7.3 如果修改数据后，直接显示最新的更新时间
ALTER TABLE xxx ADD updateTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

4. `DQL`

# 基本查询
# 查询所有数据并显示所有字段
SELECT * FROM xxx;
# 查询指定字段的数据
SELECT name, age FROM xxx;
# 字段起别名，别名一般在多张表或客户端返回对应的key时会使用到
SELECT name as n, age as a FROM xxx;
-----------------------------------------
# WHERE查询
SELECT * FROM xxx WHERE id = 1;
SELECT * FROM xxx WHERE id = 1 && name = 'kwan';
SELECT * FROM xxx WHERE id = 1 || name = 'kwan';
# 范围区间
SELECT * FROM xxx WHERE id BETWEEN 1 AND 3;
# 多个结果中的一个
SELECT * FROM xxx WHERE name in ('kwan', 'dean');
# 模糊查询，%表示匹配任意个的任意字符，_表示匹配一个的任意字符
# 查询所有以k开头的name
SELECT * FROM xxx WHERE name LIKE 'k%';
# 查询带M的name
SELECT * FROM xxx WHERE name LIKE '%M%';
# 查询带M的name，必须是第三个字符
SELECT * FROM xxx WHERE name LIKE '__M%';
-----------------------------------------
# 查询结果排序，使用ORDER BY， ASC---> 升序， DESC---> 降序
SELECT * FROM xxx WHERE name = 'kwan' ORDER BY id ASC;
-----------------------------------------
# 分页查询，用法[LIMIT{[offset,]row_count | row_count OFFSET offset}]
SELECT * FROM xxx LIMIT 30 OFFSET 0;
SELECT * FROM xxx LIMIT 0, 30; # offset row_count
-----------------------------------------
# 聚合函数 AVG() MAX() MIN() COUNT() SUM()...
SELECT MIN(id) FROM xxx;
-----------------------------------------
# 分组GROUP BY --- 通常和聚合函数一起使用
# 根据name进行分组，包括最大年龄，最小年龄，平均年龄
SELECT MIN(age), MAX(age), AVG(age) FROM xxx GROUP BY name;
# GROUP BY 约束 HAVING
# 在以上需求的基础上，对结果筛选，展示大于18岁
SELECT MIN(age), MAX(age), AVG(age) FROM xxx GROUP BY name HAVING age > 18;

5. 多表查询

参考博文：多表查询

# 直接查询两张表，效果就是两张表字段合并展示
# 这种查询的结果是笛卡尔乘积，也称之为直积，表示为X*Y
SELECT * FROM xxx, yyy;

5.1 左连接

# 以左表为主
# 无论左表的id是否有对应的右表id，左表数据都会被查询出来
SELECT * FROM xxx LEFT JOIN yyy ON xxx.id = yyy.id;
# 
SELECT * FROM xxx LEFT JOIN yyy ON xxx.id = yyy.id WHERE yyy.id IS NULL;

5.2 右连接

# 以右表为主
# 右表会被全部查询出来
SELECT * FROM xxx RIGHT JOIN yyy ON xxx.id = yyy.id;
#
SELECT * FROM xxx RIGHT JOIN yyy ON xxx.id = yyy.id WHERE xxx.id IS NULL;

5.3 内连接

# 内连接表示左表与右表都有对应的数据关联。
# 内连接的写法 INNER JOIN | CROSS JOIN | JOIN
SELECT * FROM xxx INNER JOIN yyy ON xxx.id = yyy.id;
# 效果等同于
SELECT * FROM xxx, yyy WHERE xxx.id = yyy.id;
# 注：内连接代表的是两张表连接时就会约束数据之间的关系，才决定之后查询的结果
#    WHERE条件，代表的是先计算出笛卡尔乘积，在此基础上进行WHERE条件筛选

5.4 全连接

# MYSQL中不支持FULL JOIN，但是可以使用UNION实现
# 两表并集
(SELECT * FROM xxx LEFT JOIN yyy ON xxx.id = yyy.id)
UNION
(SELECT * FROM xxx RIGHT JOIN yyy ON xxx.id = yyy.id);
# 两表去除交集后的结果
(SELECT * FROM xxx LEFT JOIN yyy ON xxx.id = yyy.id WHERE xxx.id IS NULL)
UNOIN
(SELECT * FROM xxx RIGHT JOIN yyy ON xxx.id = yyy.id WHERE yyy.id IS NULL);

5.5 查询数据

students

id	name	age
1	`why`	18
2	`tom`	22
3	`lilei`	25
4	`lucy`	16
5	`lily`	20

courses

id	name	price
1	英语	100
2	语文	666
3	数学	888
4	历史	80
5	地理	886

students_select_courses

id	student_id	course_id
1	1	1
2	1	3
3	3	2
4	3	3
5	3	4

# 查询所有的学生选择的所有课程
SELECT
	stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
FROM student stu
JOIN students_select_course ssc ON stu.id = ssc.student_id
JOIN courses cs ON ssc.course_id = cs.id;

运行结果：

`studentId`	`studentName`	`courseId`	`courseName`	`coursePrice`
1	`why`	1	英语	100
3	`lilei`	2	语文	666
1	`why`	3	数学	888
3	`lilei`	3	数学	888
3	`lilei`	4	历史	80

# 查询所有的学生选课情况
# 分析： 最终结果学生表全部展示，所以使用左连接
SELECT
	stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
FROM students stu
LEFT JOIN students_select_courses ssc ON stu.id = ssc.student_id
LEFT JOIN courses cs ON ssc.course_id = cs.id;

运行结果：

`studentId`	`studentName`	`courseId`	`courseName`	`coursePrice`
1	`why`	1	英语	100
1	`why`	3	数学	888
2	`tom`	`NULL`	`NULL`	`NULL`
3	`lilei`	2	语文	666
3	`lilei`	3	数学	888
3	`lilei`	4	历史	80
4	`lucy`	`NULL`	`NULL`	`NULL`
5	`lily`	`NULL`	`NULL`	`NULL`

# 查询why同学选择的课程
# 分析：应基于所有学生的选课情况（包括未选课的同学）的结果中进行筛选
SELECT
	stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
FROM students stu
LEFT JOIN students_select_courses ssc ON stu.id = ssc.student_id
LEFT JOIN courses cs ON ssc.course_id = cs.id
WHERE stu.id = (SELECT id FROM students WHERE name = 'why');

运行结果：

`studentId`	`studentName`	`courseId`	`courseName`	`coursePrice`
1	`why`	1	英语	100
1	`why`	3	数学	888

# 查询未选课的学生
SELECT
	stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
FROM students stu
LEFT JOIN students_select_courses ssc ON stu.id = ssc.student_id
LEFT JOIN courses cs ON ssc.course_id = cs.id
WHERE cs.id IS NULL;

运行结果：

`studentId`	`studentName`	`courseId`	`courseName`	`coursePrice`
2	`tom`	`NULL`	`NULL`	`NULL`
4	`lucy`	`NULL`	`NULL`	`NULL`
5	`lily`	`NULL`	`NULL`	`NULL`

# 查询未被选中的课程
SELECT
	stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
FROM students stu
RIGHT JOIN students_select_courses ssc ON stu.id = ssc.student_id
RIGHT JOIN courses cs ON ssc.course_id = cs.id
WHERE stu.id IS NULL;

运行结果：

`studentId`	`studentName`	`courseId`	`courseName`	`coursePrice`
`NULL`	`NULL`	5	地理	886

多表查询时，首先要确定以哪张表为基础，连接关系表后，再关联对应有映射关系的表。

6. 查询结果格式转换

6.1 转换对象

🧢在真实开发中，有时会将查询到的附属表的内容放在对象中返回。

SELECT 
	xxx.id as id, xxx.name as name, JSON_OBJECT('id', yyy.id, 'name', yyy.name) as other
FROM xxx
LEFT JOIN yyy ON xxx.id = yyy.id;
# 返回结果
{
	id: 0,
	name: 'kwan',
	other: {
		id: 1,
		name: 'dean'
	}
}

6.2 多对多转成数组

SELECT
	stu.id, stu.name, JSON_ARRAYAGG(JSON_OBJECT('id', cs.id, 'name', cs.name)) as course
FROM students stu
LEFT JOIN students_select_courses ssc ON stu.id = ssc.stude_id
LEFT JOIN courses cs ON ssc.course_id = cs.id
GROUP BY stu.id;
# 返回结果
{
	id: 1,
	name: 'kwan',
	course: [
        {
        	id: 1,
        	name: 'dean'
        }
    ]
}

前端必知必会 --- 数据库

一、基础概念

1. 定义

2. 数据库类型

2.1 关系型数据库

2.2 非关系型数据库

2.3 业务场景

二、SQL基础

1. SQL语句分类

1.1 DDL

1.2 DML

1.3 DQL

1.4 DCL

2. SQL数据类型

2.1 数字类型

2.2 日期类型

2.3 字符串类型

3. SQL关键字

4. 数据库操作

3. 数据表操作

4. DQL

5. 多表查询

5.1 左连接

5.2 右连接

5.3 内连接

5.4 全连接

5.5 查询数据

6. 查询结果格式转换

6.1 转换对象

6.2 多对多转成数组

二、`SQL`基础

1. `SQL`语句分类

1.1 `DDL`

1.2 `DML`

1.3 `DQL`

1.4 `DCL`

2. `SQL`数据类型

3. `SQL`关键字

4. `DQL`