1. 数据库分类

通常我们将数据划分为两类：关系型数据库和非关系型数据库。

关系型数据库：MySQL、Oracle、DB2、SQL Server、Postgre SQL等。

关系型数据库通常我们会创建很多个二维数据表；
数据表之间相互关联起来，形成一对一、一对多、多对多等关系；
之后我们可以利用SQL语句在多张表中查询我们所需的数据；

非关系型数据库：MongoDB、Redis、Memcached、HBse等。

非关系型数据库的英文是Not Only SQL，也简称为NoSQL；
相对而言，非关系型数据库比较简单一些，存储数据也会更加自由（甚至我们可以直接将一个复杂的json对象直接塞到数据库中，一般用在爬虫数据中）；
NoSQL是基于Key-Value的对应数据，并且查询的过程中不需要经过SQL解析。

2. 数据库创建

2.1 终端操作

create database music_db;
use music_db;
show tables;
insert into t_singer (name, age) values ('五月天', 30);
select * from t_singer;

3. SQL 语句的常用规范

通常关键字使用大写字母，比如 CREATE、TABLE、SHOW 等等；
一条语句结束后，需要以；结尾
如果遇到关键字作为表明或者字段名称，可以使用 `` 包裹；

4. SQL 常见语句

DDL（Data Definition Language）：数据定义语言；
- 可以通过 DDL 语句对数据库或者表进行：创建、删除、修改等操作；
DML（Data Manipulation Language）：数据操作语言；
- 可以通过 DML 语句对表进行：添加、删除、修改等操作；
DQL（Data Query Language）：数据查询语言；
- 可以通过 DQL 从数据库中查询记录；
DCL（Data Control Language）：数据控制语言；
- 对数据库、表格的权限进行相关访问控制操作；

5. 数据库操作

5.1 DDL 数据库操作

5.1.1 查看当前数据库

查看所有的数据
- SHOW DATABASES;
使用某一个数据
- USE test_demo；
查看当前正在使用的数据库
- SELECT DATABASE();

5.1.2 创建新的数据库

创建数据库语句
- CREATE DATABASE test_demo;
- CREATE DATABASE IF NOT EXISTS test_demo;

5.1.3 删除数据库

删除数据库
- DROP DATABASE test_demo;
- DROP DATABASE IF EXISTS test_demo;

5.1.4 修改数据库

修改数据库
- ALTER DATABASE test_demo xxx;

5.2 DDL 操作表结构

5.2.1 查看当前数据库中含有的表

查看表
- SHOW TABLES;
查看某一张表
- DESC t_singer;

5.2.2 创建表

创建一张表

CREATE TABLE IF NOT EXISTS users(

name VARCHAR(10),

age INT,

height DOUBLE

);

5.2.3 删除表

删除表

DROP TABLE IF EXISTS users

5.2.4 修改表

修改表

-- 4. 修改表结构
-- 4.1 修改表名字
ALTER TABLE `users` RENAME TO `t_users`;
-- 4.2 添加新的字段（field）
ALTER TABLE `t_users` ADD createTime TIMESTAMP;
ALTER TABLE `t_users` ADD updateTime TIMESTAMP;
-- 4.3 修改字段的名称（field名称）
ALTER TABLE `t_users` CHANGE createTime createAt TIMESTAMP;
-- 4.4 删除某一个字段（field列）
ALTER TABLE `t_users` DROP updateTime;
-- 4.5 修改某一个字段的类型（id int => bigint）
ALTER TABLE `t_users` MODIFY id BIGINT;

6 SQL的数据类型

MySQL支持的数据类型有：数字类型、日期和时间类型，字符串（字符和字节）类型，空间类型和JSON类型。空间类型包括 Point（x，y，z）。JSON数据类型一般会单独建一张表，一般是非关系型数据库中使用，关系型数据库中很少使用。

6.1 数字类型

整数数字类型：INTEGER、INT、SMALLINT、TINYINT、MEDIUMINT、BIGINT。

浮点数类型：FLOAT、DOUBLE（前者4个字节，后者8个字节）。

精确类型：DECIMAL、NUMERIC（DECIMAL是NUMERIC的实现形式）

6.2 日期类型

YEAR 以 YYYY 格式显示值
- 范围 1901 到 2155，和 0000
DATE 类型用于具有日期部分但没有时间部分的值
- DATE 以格式 YYYY-MM-DD 显示值
- 支持的范围是 '1000-01-01' 到 '9999-12-31'
DATETIME 类型用于包含日期和时间部分的值
- DATETIME 以格式 'YYYY-MM-DD hh:mm:ss' 显示值
- 支持的范围是 1000-01-01 00:00:00 到 9999-12-31 23:59:59
TIMESTAMP 数据类型用于同时包含日期和时间部分的值
- TIMESTAMP 以格式 'YYYY-MM-DD hh:mm:ss' 显示值
- 但是它的范围是 UTC 的时间范围：'1970-01-01 00:00:01' 到 '2038-01-19 03:14:07'

PS: DATETIME 或 TIMESTAMP 值可以包括在高达微秒(6位)精度的后小数秒一部分

比如 DATETIME 表示的范围可以是 '1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999'

6.3 字符串类型

CHAR 类型在创建表时为固定长度，长度可以是0到255之间的任何值
- 在被查询时，会删除后面的空格
VARCHAR 类型的值是可变的字符串，长度可以指定为 0 到 65535 之间的值
- 在被查询时，不会删除后面的空格
BINARY 和 VARBINARY 类型用于存储二进制字符串，存储的是字节字符串
- 详情请看
BLOB 用于存储大的二进制类型
- 如果存储一些视频文件比较大，比如几 G 时，此时并不适合使用 BLOB 存储，最好将其作为文件存储，然后保存其路径即可。
TEXT 用于存储大的字符串类型

6.4 表约束

主键 PRIMARY KEY
- 一张表中，我们为了区分每一条记录的唯一性，必须有一个字段是永远不会重复，并且不会为空的，这个字段我们通常将它设置为主键：
  - 主键是表中唯一的索引
  - 并且必须是 NOT NULL 的，如果没有设置 NOT NULL，那么 MySQL 也会隐式的设置为 NOT NULL
  - 主键也可以多列索引，PRIMARY KEY(key_part，...)，我们一般称之为联合主键
  - 建议：开发过程中主键字段应该是和业务无关的，尽量不要使用业务字段来作为主键
唯一：UNIQUE
- 某些字段在开发过程中我们希望是唯一的，不会重复的，比如手机号码、身份证号码等，这个字段我们可以使用 UNIQUE 来约束
- 使用 UNIQUE 的约束字段在表中必须是不同的
- UNIQUE 索引允许 NULL 包含的列具有多个值 NULL
不能为空：NOT NULL
- 某些字段我们要求用户必须插入值，不可以为空，这个时候我们可以使用 NOT NULL 来约束
默认值：DEFAULT
- 某些字段我们希望在没有设置值时给予一个默认值，这个时候我们可以使用 DEFAULT 来完成
自动递增：AUTO_INCREMENT
- id 自动增加

此外，外键约束也是最常用的一种约束手段。

7. DML 数据库操作

-- 1. 新建商品表
CREATE TABLE IF NOT EXISTS `t_products`(
  id INT PRIMARY KEY AUTO_INCREMENT,
	title VARCHAR(20) UNIQUE NOT NULL,
	description VARCHAR(200) DEFAULT '',
	price DOUBLE DEFAULT 0,
	publishTime DATETIME
);

-- 2. DML语句：插入数据
-- INSERT INTO `t_products` (字段的名称) VALUES (指的列表)
INSERT INTO `t_products` (title, description, price, publishTime) VALUES ('查拉图斯特拉如是说', '尼采之作', 30, '2003-10-20');
INSERT INTO `t_products` (title, description, price, publishTime) VALUES ('活着', '面对痛苦，也要坚强的活下去', 40, '2004-01-20');
INSERT INTO `t_products` (title, description, price, publishTime) VALUES ('白夜行', '东野圭吾代表作', 20, '2001-12-20');

-- 3. DML语句：删除数据
-- 3.1 删除表中所有的数据（慎重使用）
-- DELETE FROM `t_products`
-- 3.2 根据条件，比如id删除某一条
DELETE from `t_products` WHERE id = 1;

-- 4. DML语句：修改数据
-- 4.1 修改表中的所有数据
-- UPDATE `t_products` SET price = 8888;
-- 4.2 根据条件修改某一条数据
-- UPDATE `t_products` SET price = 8888 WHERE id = 3;
UPDATE `t_products` SET price = 9999, title = '幻夜' WHERE id = 3;

-- 5. 扩展：当修改某一条数据时，使用最新的时间记录
ALTER TABLE `t_products` ADD `updateTime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

8. DQL 操作

SELECT 用于从一个或多个表中检索选中的行（Record）。查询格式如下：

SELECT select_expr [,select_expr]...
       [FROM table_references]
       [WHERE where_condition]
       [ORDER BY expr [ASC | DESC]]
       [LIMIT {[offset,] row_count | row_count OFFSET offset}]
       [GROUP BY expr]
       [HAVING where_condition]
       
// 实例
select id, title, desc, price
       from `t_products`
       where price > 1000
       order by price
       limit 20 offset 40

9. DQL 数据库查询操作

9.1 模糊查询

其中，模糊查询使用 LIKE 关键字，结合两个特殊的符号：
- % 表示匹配任意个的任意字符；
- _ 表示匹配一个的任意字符；

9.2 排序操作

查询结果希望可以按照某种方式进行排序，这个时候使用 ORDER BY；
- ASC：升序
- DESC：降序

9.3 分页查询

当数据库中的数据非常多时，一次性查询到所有的结果进行展示都是不太现实的
- 在真实开发过程中，要求用户传入offset、limit或者page等字段；
- 目的是可以在数据库进行分页查询
- 用法有[LIMIT{[offset,] row_count | row_count OFFSET offset}]

CREATE TABLE IF NOT EXISTS `products` (
  id INT PRIMARY KEY AUTO_INCREMENT,
	brand VARCHAR(20),
	title VARCHAR(100) NOT NULL,
	price DOUBLE NOT NULL,
	score DECIMAL(2,1),
	voteCnt INT,
	pid INT
);
-- 1. 基本查询
-- 1.1 查询所有的数据的所有的字段
-- SELECT * FROM `products`;

-- 1.2 查询所有的数据，并且指定对应的字段
-- SELECT id, brand, title, price FROM `products`;

-- 1.3 查到字段之后，给字段重命名（起一个别名，AS 关键字可以省略）
SELECT id AS phoneId, brand phoneBrand, title, price FROM `products`;

-- 2. 查询条件（比较运算符）
-- 2.1 查询所有价格小于2000的手机
SELECT * FROM `products` WHERE price < 2000;
-- 2.2 查询价格大于等于3000的手机
SELECT * FROM `products` WHERE price >= 3000;
-- 2.3 查询价格等于2399的手机
SELECT * FROM `products` WHERE price = 2399;
-- 2.4 查询所有的华为品牌
SELECT * FROM `products` WHERE brand = '华为';
-- 2.5 查询所有的不是华为手机品牌的商品
SELECT * FROM `products` WHERE brand != '华为';


-- 3. 查询条件（逻辑运算符）
-- 3.1 查询brand为华为，并且价格小于2000的手机
SELECT * FROM `products` WHERE brand = '华为' && price < 2000;
SELECT * FROM `products` WHERE brand = '华为' AND price < 2000;
-- 3.2 查询brand为华为，或者价格大于5000的手机
SELECT * FROM `products` WHERE brand = '华为' || price > 5000;
SELECT * FROM `products` WHERE brand = '华为' OR price > 5000;
-- 3.3 查询区间范围
SELECT * FROM `products` WHERE price >= 1000 && price <= 2000;
SELECT * FROM `products` WHERE price BETWEEN 1000 AND 2000;

-- 3.4 查询多个结果中的一个
SELECT * FROM `products` WHERE brand = '华为' || brand = '小米';
SELECT * FROM `products` WHERE brand IN ('华为', '小米');


-- 4. 查询条件（模糊查询LIKE）
-- 4.1 查询所有 title 中以 v 开头的商品
SELECT * FROM `products` WHERE title LIKE 'v%';
-- 4.2 查询所有title带v的商品
SELECT * FROM `products` WHERE title LIKE '%v%';
-- 4.3 查询所有title带M，并且M必须是第三个字符
SELECT * FROM `products` WHERE title LIKE '__M%';

-- 5. 对结果进行排序（ORDER BY）
-- 5.1 查询所有的价格小于 2000 的手机，并且按照评分的降序获取结果
SELECT * FROM `products`
  WHERE price < 2000
	ORDER BY score DESC;
	
SELECT * FROM `products`
  WHERE price < 2000
	ORDER BY score ASC;
        
-- 6. 对表进行分页查询
-- 6.1 默认不偏移（偏移0条数据）
SELECT * FROM `products` LIMIT 20;
-- 6.2 指定偏移多少条数据（size: 20, offset: 40）
SELECT * FROM `products` LIMIT 20 OFFSET 40;

-- 6.3 另外一种写法
SELECT * FROM `products` LIMIT 40, 20;

10. 数据库进阶操作

10.1 聚合函数

AVG()
MAX()/MIN()
SUM()
COUNT()

10.2 分组

GROUP BY 通常和聚合函数一起使用：
- 表示我们先对数据进行分组，再对每一组数据，进行聚合函数的计算；
- 如果希望给 GROUP BY 查询到的结果添加一些约束，可以使用 HAVING
having 和 where 的区别
- 前者一般结合分组查询一起使用
- 后者一般用在 select from where

-- 1. 需求：计算华为手机的平均价格
SELECT AVG(price) FROM `products` WHERE brand = '华为';

-- 2. 需求：计算华为手机的平均评分
SELECT AVG(score) AS minAvgScore FROM `products` WHERE brand = '华为';

-- 3. 需求：选择手机中评分最高/最低的分数(Max/Min)
SELECT MAX(score) FROM `products`;
SELECT MIN(score) FROM `products`;

-- 4. 需求：所有的手机一共有多少人投过票（SUM）
SELECT SUM(voteCnt) FROM `products`;

-- 5. 需求：一共有多少个商品
SELECT COUNT(*) FROM `products`;
SELECT COUNT(*) FROM `products` WHERE brand = '华为';

-- 6. group by：数据根据brand进行分组
SELECT 
	brand, MAX(price) maxPrice, MIN(price) minPrice, ROUND(AVG(price), 2) avgPrice, AVG(score) avgScore 
FROM `products` 
GROUP BY brand
HAVING avgScore > 7 AND avgPrice < 4000;

10.3 多表操作

10.3.1 创建多张表

当一张表中包含很多的冗余信息时，这时我们要考虑将这些重复信息抽到另外一张表中，即创建多张表。

-- 1. 创建歌曲表
CREATE TABLE IF NOT EXISTS `t_songs` (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  duration INT DEFAULT 0,
  singer VARCHAR(10),
  // 增加外键
  FOREIGN KEY (singer_id) REFERENCES brand(id)
);

INSERT INTO `t_songs` (name, duration, singer) VALUES ('温柔', 100, '五月天');
INSERT INTO `t_songs` (name, duration, singer) VALUES ('离开地球表面', 100, '五月天');
INSERT INTO `t_songs` (name, duration, singer) VALUES ('倔强', 100, '五月天');


-- 2. 创建歌手表
CREATE TABLE IF NOT EXISTS `t_singers`(
  id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(10),
	intro VARCHAR(200)
);

INSERT INTO `t_singers` (name, intro) VALUES ('五月天', 
'五月天，全亚洲代表性摇滚乐团。演出足迹踏遍美国，澳洲以及全亚洲地区.');

-- 3. 修改歌曲表
-- 当要修改的类型前后不一致时，修改不成功
-- ALTER TABLE `t_songs` CHANGE `singer` `singerId` INT;
ALTER TABLE `t_songs` DROP `singer`;
ALTER TABLE `t_songs` ADD `singer_id` INT;

-- 4. 为品牌单独创建一张表
CREATE TABLE IF NOT EXISTS `brands`(
  id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(10) UNIQUE NOT NULL,
	website VARCHAR(100),
	worldRank INT
);

INSERT INTO `brands` (name, website, worldRank) VALUES ('华为', 'www.huawei.com', 1);
INSERT INTO `brands` (name, website, worldRank) VALUES ('小米', 'www.mi.com', 10);
INSERT INTO `brands` (name, website, worldRank) VALUES ('苹果', 'www.apple.com', 5);
INSERT INTO `brands` (name, website, worldRank) VALUES ('oppo', 'www.oppo.com', 15);
INSERT INTO `brands` (name, website, worldRank) VALUES ('京东', 'www.jd.com', 3);
INSERT INTO `brands` (name, website, worldRank) VALUES ('Google', 'www.google.com', 8);

-- 5. 为products表添加brand_id，并且设置外键约束
ALTER TABLE `products` ADD `brand_id` INT;
ALTER TABLE `products` ADD FOREIGN KEY (brand_id) REFERENCES brands(id);
-- 5.1 将products中的brand_id关联到brand中的id值
UPDATE `products` SET `brand_id` = 1 WHERE `brand` = '华为';
UPDATE `products` SET `brand_id` = 4 WHERE `brand` = 'OPPO';
UPDATE `products` SET `brand_id` = 3 WHERE `brand` = '苹果';
UPDATE `products` SET `brand_id` = 2 WHERE `brand` = '小米';


-- 6. 在有外键约束的情况下，修改brand中的id
UPDATE `brands` SET id = 99 WHERE id = 1;

-- 7. 查看products中目前的外键
-- CREATE TABLE `products` (
--   `id` int NOT NULL AUTO_INCREMENT,
--   `brand` varchar(20) DEFAULT NULL,
--   `title` varchar(100) NOT NULL,
--   `price` double NOT NULL,
--   `score` int DEFAULT NULL,
--   `voteCnt` int DEFAULT NULL,
--   `pid` int DEFAULT NULL,
--   `brand_id` int DEFAULT NULL,
--   PRIMARY KEY (`id`),
--   KEY `brand_id` (`brand_id`),
--   CONSTRAINT `products_ibfk_1` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`)
-- ) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
show CREATE TABLE `products`;
ALTER TABLE `products` DROP FOREIGN KEY products_ibfk_1;
ALTER TABLE `products` ADD FOREIGN KEY (brand_id) REFERENCES brands(id)
											ON UPDATE CASCADE
											ON DELETE CASCADE;
											
UPDATE `brands` SET id = 99 WHERE id = 1;

10.3.2 添加字段和外键约束

将两张表关联起来，我们可以将products中的brand_id关联到brand的id
- 如果是创建表添加外键约束，我们需要在创建表的()最后添加如下语句
  - FOREIGN KEY (brand_id) REFERENCES brand(id)
- 如果是表已经创建好，我们需要额外添加外键
  - ALTER TABLE products ADD brand_id INT;
  - ALTER TABLE products ADD FOREIGN KEY (brand_id) REFERENCES brands(id);
然后关联
- UPDATE products SET brand_id = 1 WHERE brand = '华为';
- UPDATE products SET brand_id = 4 WHERE brand = 'OPPO';
- UPDATE products SET brand_id = 3 WHERE brand = '苹果';
- UPDATE products SET brand_id = 2 WHERE brand = '小米';

10.3.3 外键存在时更新和删除数据

UPDATE brands SET id = 99 WHERE id = 1;
这个时候代码报错：

如果希望更新，我们需要修改 on delete 或者 on update 的值
- RESTRICT(默认属性)：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话会报错，不允许更新或删除
- NO ACTION：和RESTRICT是一致的，是在SQL标准中定义的
- CASCADE：更新或者删除某个记录时，会检查该记录是否有关联的外键记录，有的话：
  - 更新：更新对应的记录
  - 删除：关联的记录一起被删掉
- SET NULL：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话，将对应的值设置为NULL

10.3.4 多表查询

默认情况下，会使用笛卡尔乘积展示查询到的结果，因此会得到 X * Y 条数据。
- SELECT * FROM products, brands;
从两张表中查询所有的数据，再对结果进行过滤
- SELECT * FROM products, brands WHERE products.brand_id = brands.id;

-- 1. 直接从两张表中查询数据
SELECT * FROM `products`, `brands`;

-- 2. 从两张表查询所有的数据，再对结果进行过滤
SELECT * FROM `products`, `brands` WHERE products.brand_id = brands.id;

10.3.5 多表之间的连接

事实上，我们想要的效果并不是这样的，而且表中的某些特定数据，这个时候我们可以使用 SQL JOIN 操作：

左连接
- 希望获取到的是左边所有的数据（以左表为主）
- 完整写法是 LEFT [OUTER] JOIN，但是 OUTER 是可以省略的
右连接
- 获取到的是右边所有的数据（以右表为主）
- 完整写法是 RIGHT [OUTER] JOIN，但是 OUTER 是可以省略的
内连接
- 表示的是左边的表和右边的表都有对应的数据关联
- 写法：CROSS JOIN 或者 JOIN 都可以
  
  SELECT * FROM products INNER JOIN brand ON products.brand_id = brands.id
- 和左连接中的一个写法相同
  
  SELECT * FROM products, brand WHERE products.brand_id = brands.id
- 含义并不相同
  - 内连接，代表的是在两张表连接时就会约束数据之间的关系，来决定之后查询的结果
  - where 条件，代表的是先计算出笛卡尔乘积，在笛卡尔乘积的数据基础之上进行 where 条件的筛选
全连接
- SQL 规范中全连接是使用 FULL JOIN，但是 MySQL 中并没有对它的支持，我们需要使用 UNION 来实现

-- 1. 直接从两张表中查询数据
SELECT * FROM `products`, `brands`;

-- 2. 从两张表查询所有的数据，再对结果进行过滤
SELECT * FROM `products`, `brands` WHERE products.brand_id = brands.id;


-- 表连接
-- 1. 左连接 LEFT [OUTER] JOIN `表` ON 连接条件
SELECT * FROM `products` LEFT JOIN `brands` ON products.brand_id = brands.id;

-- 查询左边的数据哪些是和右边没有交集
SELECT * FROM `products` LEFT JOIN `brands` ON products.brand_id = brands.id WHERE brands.id IS NOT NULL;


-- 2. 右连接：RIGHT [OUTER] JOIN
SELECT * FROM `products` RIGHT JOIN `brands` ON products.brand_id = brands.id;
SELECT * FROM `products` RIGHT JOIN `brands` ON products.brand_id = brands.id WHERE products.id IS NULL;


-- 3. 内连接：[CROSS/INNER] JOIN
SELECT * FROM `products` JOIN `brands` ON products.brand_id = brands.id;


-- 4. 全连接：MySQL不支持全连接，使用union
(SELECT * FROM `products` LEFT JOIN `brands` ON products.brand_id = brands.id)
UNION
(SELECT * FROM `products` RIGHT JOIN `brands` ON products.brand_id = brands.id);


(SELECT * FROM `products` LEFT JOIN `brands` ON products.brand_id = brands.id WHERE brands.id IS NULL)
UNION
(SELECT * FROM `products` RIGHT JOIN `brands` ON products.brand_id = brands.id WHERE products.id IS NULL);

10.3.6 多对多的表结构（分层架构思想的一种）

比如，在学校选课时，通常会遇到多个人选择不同的课程，如果只是在不同的课程中存储不同的id列表时，这种操作方法极为复杂，并且查询起来也不太方便。此时，我们可以选择在另外建立一张关系表，来保存每个学生对应的不同的课程编号，在查询时，我们只需要查询这张新表即可。

多表查询

-- 1. 创建多张表
-- 1.1 创建学生表
CREATE TABLE IF NOT EXISTS `students`(
  id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20) NOT NULL,
	age INT
);

INSERT INTO `students` (name, age) VALUES ('why', 18);
INSERT INTO `students` (name, age) VALUES ('tom', 22);
INSERT INTO `students` (name, age) VALUES ('lilei', 25);
INSERT INTO `students` (name, age) VALUES ('lucy', 16);
INSERT INTO `students` (name, age) VALUES ('lily', 20);

-- 1.2 创建课程表
CREATE TABLE IF NOT EXISTS `courses`(
  id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20) NOT NULL,
	price DOUBLE NOT NULL
);

INSERT INTO `courses` (name, price) VALUES ('英语', 100);
INSERT INTO `courses` (name, price) VALUES ('语文', 666);
INSERT INTO `courses` (name, price) VALUES ('数学', 888);
INSERT INTO `courses` (name, price) VALUES ('历史', 80);
INSERT INTO `courses` (name, price) VALUES ('物理', 100);

-- 1.3 创建学生选择的课程关系表
CREATE TABLE IF NOT EXISTS `students_select_courses`(
  id INT PRIMARY KEY AUTO_INCREMENT,
	student_id INT NOT NULL,
	course_id INT NOT NULL,
	FOREIGN KEY (student_id) REFERENCES students(id) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (course_id) REFERENCES courses(id) ON UPDATE CASCADE ON DELETE CASCADE
);

-- 2. 选择的过程
# why 选修了 英文和数学
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (1, 1);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (1, 3);

# lilei 选修了 语文、数学和英语
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 2);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 3);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 4);

-- 3. 查询数据
-- 3.1 所有有选课学生的选课情况（内连接）
SELECT 
  stu.name stuName, stu.age stuAge, cs.name csName, cs.price csPrice
FROM `students` stu
JOIN `students_select_courses` ssc ON stu.id = ssc.student_id
JOIN `courses` cs ON ssc.course_id = cs.id;

-- 3.2 所有的学生（不管有没有选课）的选课情况
SELECT 
  stu.name stuName, stu.age stuAge, cs.name csName, cs.price csPrice
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;

-- 3.3 单个学生选课的情况(why的选课情况)
SELECT 
  stu.name stuName, stu.age stuAge, cs.name csName, cs.price csPrice
FROM `students` stu
JOIN `students_select_courses` ssc ON stu.id = ssc.student_id
JOIN `courses` cs ON ssc.course_id = cs.id
WHERE stu.name = 'why';

-- 3.4 单个学生的选课情况（lily的选课情况）
-- 由于lily没有选课，在使用上面的查询条件时无法查询，所以使用左连接
SELECT 
  stu.name stuName, stu.age stuAge, cs.name csName, cs.price csPrice
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.name = 'lily';

-- 3.5 查看哪些学生没有选择课程
SELECT 
  stu.name stuName, stu.age stuAge, cs.name csName, cs.price csPrice
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;

-- 3.6 查看哪些课程没有被选择
SELECT 
  stu.name stuName, stu.age stuAge, cs.name csName, cs.price csPrice
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;

10.4 查询结果-对象和数组

-- 1. 单表查询
SELECT * FROM `products` WHERE price > 3000;


-- 2. 多表查询
-- 这种查询会将外表信息全部放到一个对象中，事实上我们想要获取的是将外表的信息单独放到一个对象中
SELECT * FROM `products` LEFT JOIN `brands` ON products.brand_id = brands.id WHERE price > 3000;


-- 3. 多表查询：品牌信息放到一个单独的对象中
SELECT
  products.id as id, products.title  title, products.price price,
	JSON_OBJECT('id', brands.id, 'name', brands.`name`, 'website', brands.website, 'rank', brands.worldRank) as brand 
FROM `products` LEFT JOIN `brands` ON products.brand_id = brands.id WHERE price > 3000;


-- 4. 多对多：直接查询
SELECT *
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 NOT NULL;

-- 多对多：查询到的结果会转化为数组，数组汇总又存放的是对象类型
SELECT
  stu.id id, stu.`name` name, stu.age age,
	JSON_ARRAYAGG(JSON_OBJECT('id', cs.id, 'name', cs.`name`, 'price', cs.price)) AS courses
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 NOT NULL
GROUP BY stu.id;

10.5 MySQL2

在 Node 代码中执行 SQL 语句，可以借助两个库：

mysql：最早的Node连接MySQL的数据库驱动
mysql2：在mysql的基础上，进行了很多的优化、改进
- 更快/更好地性能
- Prepared Statement（预编译语句）
  - 提高性能：将创建的语句模块发送给MySQL，然后MySQL编译（解析、优化、转化）语句模块，并且存储它但是不执行，之后我们在真正执行时会给？提供实际的参数才会执行；就算多次执行，也只会编译一次，所以性能是更高的
  - 简单来说，就是没有预编译之前，每一句SQL语句都会有：解析-优化-转化-执行 四个过程，有了预编译时，前面三个阶段只会执行一次，提高性能。
  - 防止SQL注入：之后传入的值不会像模块引擎那样编译，那么一些SQL注入的内容不会被执行；or 1 = 1 不会被执行
  - 之前一些不法分子通过在数据库语句中拼接一些字符串，从而绕过查询条件，从而实现查询，加大网站的风险，在出现预编译后，在数据库查询时，如果有其他非法字符串，此时会查询失败，提高数据安全性能。
- 支持Promise：所以我们可以使用 async 和 await 语法

10.5.1 基本用法

const mysql = require('mysql2')

// 1.创建一个连接（连接上数据库）
const connection = mysql.createConnection({
  host: 'localhost',
  port: 3306,
  database: 'test_db',
  user: 'root',
  password: 'xxx'
})

// 2. 执行操作语句，操作数据库
const statement = 'SELECT * FROM `students`;'
// structure query language: DDL/DML/DQL/DCL
connection.query(statement, (err, values, fields) => {
  if (err) {
    console.log('查询失败', err)
    return
  }

  // 查看结果
  console.log(values)
  console.log(fields)
})

10.5.2 预处理语句

const mysql = require('mysql2')

// 1.创建一个连接（连接上数据库）
const connection = mysql.createConnection({
  host: 'localhost',
  port: 3306,
  database: 'test_db',
  user: 'root',
  password: 'xxx'
})

// 2. 执行一个SQL语句：预处理语句
const statement = 'SELECT * FROM `products` WHERE price > ? AND score > ?;'

connection.execute(statement, [1000, 8], (err, values, fields) => {
  if (err) {
    console.log('查询失败', err)
    return
  }

  // 查看结果
  console.log(values)
  // console.log(fields)
})

10.5.3 连接池

有时,我们在创建一个连接的情况下并不能满足需求,此时我们需要建立多个连接.事实上,mysql2给我们提供了连接池(connection pools),连接池可以在需要的时候自动创建连接,并且创建的连接不会被销毁,会放到连接池中,后续可以继续使用;此外,在创建连接池的时候设置LIMIT,也就是最大创建个数.

写法很简单:只需要在创建数据库的时候使用createPool即可:

// 1.创建一个连接（连接上数据库）
const connectionPool = mysql.createPool({
  host: 'localhost',
  port: 3306,
  database: 'test_db',
  user: 'root',
  password: 'xxx',
  connectionLimit: 5
})

// 2. 执行一个SQL语句：预处理语句
const statement = 'SELECT * FROM `products` WHERE price > ? AND score > ?;'

connectionPool.execute(statement, [1000, 8], (err, values, fields) => {
  if (err) {
    console.log('查询失败', err)
    return
  }

  // 查看结果
  console.log(values)
  // console.log(fields)
})

10.5.4 Promise 异步操作

// 1.创建一个连接（连接上数据库）
const connectionPool = mysql.createPool({
  host: 'localhost',
  port: 3306,
  database: 'test_db',
  user: 'root',
  password: 'xxx',
  connectionLimit: 5
})

// 2. 执行一个SQL语句：预处理语句
const statement = 'SELECT * FROM `products` WHERE price > ? AND score > ?;'

connectionPool
  .promise()
  .execute(statement, [1000, 9])
  .then(res => {
    const [values, fields] = res
    console.log('------------------------values--------------------')
    console.log(values)
    console.log('------------------------fields--------------------')
    console.log(fields)
  })
  .catch(err => {
    console.log('查询结果失败: ', err)
  })

数据库相关知识