2023年12月8日发(作者：戏冰)
MySQL笔记（coderwhy老师Nodejs课程）
MySQL 笔记
数据库概述
数据库通俗来讲就是一个存储数据的仓库，数据库本质上就是一个软件。一个程序
常见的数据库有哪些？
关系型数据库
MySQL、Oracle、DB2、SQL Server、Postgre SQL等
关系型数据库通常我们会创建很多个二维数据表
数据表之间相互关联起来，形成 一对一、一对多、多对多 等关系
之后可以利用SQL语句在多张表中查询我们所需的数据
支持事务，对数据的访问更加安全
非关系型数据库
MongoDB、Redis、Memcached、HBse 等
非关系型数据库的英文其实是 Mot only SQL，也简称为 NoSQL
相当于已非关系型数据库比较简单一些，存储数据也会更加自由（甚至我们可以直接将一个复杂的json对象直接塞入到数据库
中）
NoSQL 是基于 Key-Value 的对用关系，并且查询的过程中不需要经过 SQL 解析，所以性能更高
NoSQL 通常不支持事务，需要在自己的程序中来保证一些原子性的操作
开发中如何选择？
目前在公司进行后端开发（Node、Java、Go等），还是以关系型数据库为主
比较常用的用到菲关系型数据库的，在爬取大量数据进行存储时，会比较常见
认识 MySQL
MySQL 是一个关系型数据库，其实本质上就是一个软件、一个程序
这个程序中管理着多个数据库
每个数据库中可以有多张表
每个表可以有多条数据
查看数据库
show databases;
MySQL 默认的数据库：
infomation_schema：信息数据库，其中包括 MySQL 在维护的其他数据库、表、列、访问权限等信息
performance_schema：性能数据库，记录着 MySQL Server 数据库引擎在运行过程中的一些资源消耗相关的信息
mysql：用于存储数据库管理者的用户信息、权限信息以及一些日志信息等
sys：相当于是一个简易版的performance_schema，将性能数据库中的数据汇总成更容易理解的形式
创建数据库-表
#
创建数据库
create database coderhub;
#
查看数据库
show databases;
#
指定要使用的数据库
use coderhub;
#
查看当前正在使用的数据库
select database();
#
查看数据库中的表
show tables;
#
创建表
create table users(name varchar(10), age int, height double);
#
查看表中数据
select * from users;
#
向表中插入数据
insert into users (name, age, height) values ('kobe', 40, 1.98);
认识 SQL 语句
我们希望数据库（特别是在程序中），就需要有和数据库沟通的语言，这个言就是 SQL：
SQL 是 Structured Query Language，称之为结构化查询语言，简称 SQL
使用 SQL 遍写出来的语句，就称之为 SQL 语句
SQL 语句可以用于对数据库进行操作
事实上，常见的关系型数据库SQL语句都是比较相似的，所以你学会了MySQL 中的 SQL 语句，之后去操作比如 Oracle 或者其他的
关系型数据库，也是非常方便的
SQL 语句的常用规范：
通常关键字是大写的，比如
CREATE、TABLE、SHOW
等
一条语句结束后，需要以 ; 结尾
如果遇到关键性作为表名或者字段名称，可以用 `` 包裹
SQL 语句的分类：
常见的 SQL 语句分为4类
DDL (Data Definition Language)：数据定义语言
可以通过 DDL 语句对数据库或者表进行：创建、删除、修改等操作
DML (Data Manipulation Language)：数据操作语言
可以通过 DML 语句对表进行：添加、删除、修改等操作
DQL (Data Query Language)：数据查询语言
可以通过 DQL 从数据库中查询记录（重点）
DCL (Data Control Language)：数据控制语言
对数据库、表格的权限进行相关访问控制操作
数据库的操作
#
查看所有数据库
show databases;
#
选择某一个数据库
use coderhub;
#
查看当前正在使用的数据库
select database();
#
创建一个新的数据库
-- create database douyu;
#
首先判断数据库是否已存在，避免重复创建报错
create database if not exists douyu;
#
指定字符集
utf8mb4
、排序规则
utf8mb4_0900_ai_ci
create database if not exists huya default character set utf8mb4 collate utf8mb4_0900_ai_ci;
#
删除数据库
drop database if exists douyu;
#
修改数据库的编码
alter database huya character set = utf8 collate = utf8_unicode_ci;
数据表的操作
#
查看所有的表
show tables;
#
新建表
create table if not exists `students` (
`name` varchar(10),
`age` int,
`score` int
);
#
删除表
drop table if exists `moment`;
#
查看表的结构
desc `students`;
#
查看创建表的
SQL
语句
show create table `students`;
-- CREATE TABLE `students` (
-- `name` varchar(10) DEFAULT NULL,
-- `age` int DEFAULT NULL,
-- `score` int DEFAULT NULL
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SQL 的数据类型
1. 数字类型
1. 整数数字类型：
INTEGER, INT, SMALLINT, TINYNT, MEDIUMINT, BIGINT
2. 浮点数字类型：
FLOAT, DOUBLE
(FLOAT 是 4 个字节，DOUBLE 是8个字节)
3. 精确数字类型：
DECIMAL, NUMERIC
(DECIMAL是NUMERIC的实现形式)
2. 日期和时间类型
1.
YEAR
以
YYYY
格式显示值，范围 1901到2155，和 0000
2.
DATE
类型用于具有日期部分但没有时间部分的值
1.
DATE
以格式
YYYY-MM-DD
显示值
2. 支持的范围是
1000-01-01
到
9999-12-31
3.
DATETIME
类型用于包含日期部分和时间部分的值
1.
DATETIME
以格式
YYYY-MM-DD hh:mm:ss
显示值
2. 支持的范围是
1000-01-01 00:00:00
到
9999-12-31 23:59:59
4.
TIMESTEMP
数据类型被用于包含时间和日期部分的值
1.
TIMESTAMP
以格式
YYYY-MM-DD hh:mm:ss
显示值
2. 但是它的范围是 UTC 的时间范围：**
1970-01-01 00:00:01
到
2038-01-19 03:14:07
**
另外：
DATETIME
或
TIMESTAMP
值可以包括在高达微秒（6位）精度的后小数秒一部分
比如
DATETIME
表示的范围可以是
1000-01-01 00:00:00.000000
到
9999-12-31 23:59:59.999999
3. 字符串（字符和字节）类型
1.
CHAR
类型在创建表时为固定长度，长度可以是0 到 255 之间的任何值
1. 在被查询时，会删除后面的空格
2.
VARCHAR
类型的值是可变长度的字符串，长度可以指定为 0 到 65535 之间的值
1. 在被查询时，不会删除后面的空格
3.
BINARY
和
VARBINARY
类型用于存储二进制字符串，存储的是字节字符串
4.
BLOB
用于存储大的二进制类型
5.
TEXT
用于存储大的字符类型
4. 空间类型
5. JSON 数据类型
表约束
主键
PRIMARY KEY
一张表中，我们为了区分每一条记录的唯一性，必须有一个字段永远不会重复，并且不会为空的，这个字段我们通常将它设置为主
键：
主键是表中唯一的索引
并且必须是
NOT NULL
的，如果没有设置
NOT NULL
，那么 MySQL 也会隐式的设置为
NOT NULL
主键也可以是多列索引，
PRIMARY KEY(key_part, ...)
，我们一般称之为联合主键
建议：开发中主键字段应该是和业务无关的，尽量不要使用业务字段来作为主键
唯一：
UNIQUE
create table if not exists `students` (
`name` varchar(10),
`age` int,
`score` int,
`height` decimal(10, 2),
`birthday` timestamp,
`phonenumber` varchar(20) unique
);
某些字段在开发中我们希望是唯一的，不会重复的，比如手机号码、身份证号码等，这个字段我们可以使用
UNIQUE
来约束
使用
UNIQUE
约束的字段在表中必须是不同的
对于所有引擎，
UNIQUE
索引允许
NULL
包含的列有多个值
NULL
不能为空：
NOT NULL
某些字段我们要求用户必须插入值，不可以为空，这个时候我们可以使用
NOT NULL
来约束
默认值：
DEFAULT
某些字段我们希望在没有设置值时给予一个默认值，这个时候我们可以使用
DEFAULT
来完成
自动递增：
AUTO_INCREMENT
某些字段我们希望不设置值时可以进行递增，比如用户的id，这个时候可以使用
AUTO_INCREMENT
来完成
外键约束也是最常用的一种约束手段
创建一个完整的表
use huya;
#
创建完整表的语法
create table if not exists `users` (
`id` int primary key auto_increment,
`name` varchar(20) not null,
`age` int default 0,
`phoneNum` varchar(20) unique default '',
`createTime` timestamp
);
#
修改表
# 1.
修改表的名字
alter table `users` rename to `user`;
# 2.
添加一个新的列
alter table `user` add `updateTime` timestamp;
# 3.
修改字段名称
alter table `user` change `phoneNum` `telPhone` varchar(20);
# 4.
修改字段的类型
alter table `user` modify `name` varchar(30);
# 5.
删除某一个字段
alter table `user` drop `age`;
#
补充
#
根据一个表结构去创建另外一张表
create table `user2` like `user`;
#
根据另外一个表中的所有内容创建一个新的表
create table `user3` (select * from `user`);
DML 对数据库进行增删改
# DML
#
插入数据
insert into `users` values(110, 'why', 18, '020-110110', '2020-10-20');
insert into `users` (`name`, `age`, `phoneNum`, `createTime`) values('kobi', 28, '000-111111', '2020-10-10');
insert into `users` (`name`, `phoneNum`) values('lilei', '000-111112');
#
需求
createTime
和
updateTime
可以自动设置值
alter table `users` modify `createTime` timestamp default current_timestamp;
#
修改完数据后，直接可以显示最新的更新时间
alter table `users` modify `updateTime` timestamp default current_timestamp on update current_timestamp;
insert into `users` (`name`, `phoneNum`) values('hanmeimei', '000-111113');
insert into `users` (`name`, `phoneNum`) values('lucy', '');
#
删除数据
# 1.
删除所有数据
-- delete from `users`;
# 2.
删除符合条件的数据
delete from `users` where id = 110;
#
更新数据
#
会修改表中所有的数据
-- update `users` set `name` = 'lily', `phoneNum` = '010-110110';
#
会修改符合条件的数据
update `users` set `name` = 'lily', `phoneNum` = '010-110110' where id = 111;
DQL 语句
DQL：Date Query Language（数据库查询语言）
SELETE
用于从一个或者多个表中检索选中的行（Record）
格式
SELECT select_expr [, select_expr]...
[FROM table_references]
[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 * from `products`;
#
查询指定子弹
select title, price from `products`;
#
对字段结果起一个别名
select title as phoneTitle, price as currentPrise from `products`;
where
查询条件
WHERE
的比较运算符
# 1.
条件判断语句
#
案例：查询价格小于
1000
的手机
select * from `products` where price < 1000;
#
案例：价格等于
999
的手机
select * from `products` where price = 999;
#
案例：价格不等于
999
的手机
select * from `products` where price != 999;
select * from `products` where price <> 999;
#
案例：查询品牌是华为的手机
select * from `products` where brand = '华为';
WHERE
逻辑运算符
# 2.
逻辑运算语句
#
案例：价格大于
1000
小于
2000
的手机
select * from `products` where price > 1000 and price < 2000;
select * from `products` where price > 1000 && price < 2000;
# between and
包含等于
select * from `products` where price between 1000 and 2000;
#
案例：价格在
5000
以上或者品牌是华为的手机
select * from `products` where price > 5000 || brand = '华为';
select * from `products` where price > 5000 or brand = '华为';
#
将某些值设置为
NULL
update `products` set url = NULL where id >= 85 and id <= 88;
#
案例：查询某一个值为
NULL
select * from `products` where url is NULL;
select * from `products` where url = NULL;
#
案例：查询某一个值不为
NULL
select * from `products` where url is not NULL;
WHERE
模糊查询
模糊查询使用
LIKE
关键字，结合2个特殊符号：
%
表示匹配任意个的任意字符
_
表示匹配一个的任意字符
# 3.
模糊查询
#
案例：查询以
v
开头的
title
select * from `products` where title like 'v%';
#
案例：
title
字段里面
所有包含
M
字符的
select * from `products` where title like '%M%';
select * from `products` where title like '%P%';
#
案例：
title
字段里面
所有第二个字符包含
P
的
select * from `products` where title like '_P%';
# 4. IN
取多个值中的其中一个即可
select * from `products` where brand = '小米' or brand = '华为' or brand = '苹果';
select * from `products` where brand in ('华为', '小米', '苹果');
结果排序
#
按照价格的升序
select * from `products` where brand in ('华为', '小米', '苹果') order by price ASC;
#
按照价格
price
的升序，如果价格相同，再按照评分
score
的降序
select * from `products` where brand in ('华为', '小米', '苹果') order by price ASC, score DESC;
分页查询
#
格式
1
：
LIMIT limit OFFSET offset
#
格式
2
：
LIMIT limit, offset
# 0~20
select * from `products` limit 20 offset 0;
select * from `products` limit 0, 20;
# 21~40
select * from `products` limit 20 offset 20;
select * from `products` limit 20, 20;
# 41~60
select * from `products` limit 40, 20;
聚合函数：表示对值集合进行操作的组（集合）函数
# 1.
聚合函数的使用
#
求所有手机的价格的总和
select sum(price) from `products`;
-- select sum(price) totalPrice from `products`;
#
求一下华为手机价格的总和
select sum(price) from `products` where brand = '华为';
#
求华为手机的平均价格
select avg(price) from `products` where brand = '华为';
#
最高手机个最低手机的价格
select max(price) from `products`;
select min(price) from `products`;
#
求华为、苹果手机的个数
select count(*) from `products` where brand = '华为';
select count(url) from `products` where brand = '苹果';
#
不会将
url
字段为
null
的字段计算在内
select count(url) from `products` where brand = '苹果';
select count(price) from `products`;
#
价格重复的部不多次计数
select count(distinct price) from `products`;
分组
Group by
，通常和聚合函数一起使用，先分组，再进行聚合函数的计算
# 2.
分组
group by
的使用
#
根据品牌进行分组
展示不同品牌手机的平均价格
数量
评分
select brand, avg(price), count(*), avg(score) from `products` group by brand;
# 3. HAVING
的使用
#
所有品牌手机
平均价格大于
2000
的
select brand, avg(price) avgPrice, count(*), avg(score) from `products` group by brand having avgPrice > 2000;
# 4.
求评分
score > 7.5
的手机的平均价格
#
升级
平均分大于
7.5
分的手机，按照品牌进行分类，求出平均价格
select brand, avg(price) from `products` where score > 7.5 group by brand;
多表操作
外键
# 1.
创建
brand
表和插入数据
create table if not exists `brand` (
`id` int primary key auto_increment,
`name` varchar(20),
`website` varchar(100),
`phoneRank` int
);
insert into `brand` (name, website, phoneRank) values ('华为', '', 2);
insert into `brand` (name, website, phoneRank) values ('苹果', '', 10);
insert into `brand` (name, website, phoneRank) values ('小米', '', 5);
insert into `brand` (name, website, phoneRank) values ('oppo', '', 12);
insert into `brand` (name, website, phoneRank) values ('谷歌', '', 9);
insert into `brand` (name, website, phoneRank) values ('京东', '', 6);
# 2.
给
brand_id
设置引用
brand
中的
id
外键约束
#
添加一个
brand_id
的字段
alter table `products` add `brand_id` int;
-- alter table `products` drop `brand_id`;
#
修改
brand_id
为外键
alter table `products` add foreign key(brand_id) references brand(id);
#
设置
brand_id
的值
update `products` set `brand_id` = 1 where `brand` = '华为';
update `products` set `brand_id` = 2 where `brand` = '苹果';
update `products` set `brand_id` = 3 where `brand` = '小米';
update `products` set `brand_id` = 4 where `brand` = 'oppo';
# 3.
修改和删除外键引用的
id
#
报错
-- update `brand` set `id` = 100 where `id` = 1;
#
外键存在时更新和删除数据
# 4.
修改
brand_id
关联外键时的
action
# 4.1
获取到创建表时候的外键名称
show create table `products`;
# 4.2
根据名称将之前的外键删除
alter table `products` drop foreign key products_idfk_1;
# 4.3
重新添加外键约束
并设置新的
action
alter table `products` add foreign key(brand_id) references brand(id)
on update cascade
on delete restrict;
update `brand` set `id` = 100 where `id` = 1;
更新外键需要修改
on delete
或者
on update
的值
我们可以给更新或删除时设置以下几个值：
RESTRICT
（默认属性）：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话会报错，不允许更新或删
除
NO ACTION
：和
RESTRICT
是一致的，是在 SQL 标准中定义的
CASCADE
：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话：
更新：那么会更新对应的记录
删除：那么关联的记录会一起被删除掉
SET NULL
：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话，将对应的值设置为
NULL
多表查询
默认多表查询的结果
第一张表 108 * 第二张表的 6 条数据
也就是说第一张表中的每一个数据，都会和第二张表中的咩一条数据结合一次
这个结果我们称之为 笛卡尔乘积，也称之为 直积，表示为 X*Y
但是事实上很多的数据是没有意义的，比如华为和苹果、小米的品牌结合起来就是没有意义的，我们可不可以进行筛选呢？
使用
where
来进行筛选
这个表示查询到笛卡尔乘积后的结果中，符合
_id =
条件的数据过滤出来
# 1.
获取到的是笛卡尔乘积
select * from `products`, `brand`;
#
获取到的是笛卡尔乘积进行筛选
select * from `products`, `brand` where _id = ;
多表之间的连接
左连接
右连接
内连接
全连接
# 2.
左连接
# 2.1
查询所有的手机以及对应的品牌
（包括没有品牌信息的手机）以及对应的品牌
null
select * from `products` left join `brand` on _id = ;
select * from `products` left outer join `brand` on _id = ;
# 2.2
查询没有对应品牌数据的手机
select * from `products` left join `brand` on _id = where is null;
# 3.
右链接
# 3.1
查询所有的品牌（没有对应的手机数据，品牌也显示）以及对应的手机数据
select * from `products` right join `brand` on _id = ;
select * from `products` right outer join `brand` on _id = ;
# 3.2
查询没有对应品牌数据的手机
select * from `products` right join `brand` on _id = where _id is null;
# 4.
内连接
# 4.1
查询所有带品牌数据的手机
select * from `products` join `brand` on _id = ;
select * from `products` join `brand` on _id = where price = 8699;
# 5.
全连接
# mysql
是不支持
full outer join
#
通过左连接和右链接联合来实现
(select * from `products` left join `brand` on _id = )
union
(select * from `products` right join `brand` on _id = );
-- select * from `products` full join `brand` on _id = ;
(select * from `products` left join `brand` on _id = where is null)
union
(select * from `products` right join `brand` on _id = where _id is null);
多对多关系
在开发中我们会遇到多对多的关系：
比如学生可以选择多门课程，一个课程可以被多个学生选择
#
基本数据的模拟
#
创建学生表
create table if not exists `students` (
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);
#
创建课程表
create table if not exists `courses` (
`id` int primary key auto_increment,
`name` varchar(20) not null,
`price` double
);
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('物理', 888);
insert into `courses` (name, price) values('地理', 333);
# 2.
建立关系表
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,
foreign key (course_id) references courses(id) on update cascade
);
# 3.
学生选课
#
比如
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);
insert into `students_select_courses` (student_id, course_id) values(1, 4);
# lilei
选择了语文、历史
insert into `students_select_courses` (student_id, course_id) values(3, 2);
insert into `students_select_courses` (student_id, course_id) values(3, 4);
# lily
选择了语文、数学、历史
insert into `students_select_courses` (student_id, course_id) values(5, 2);
insert into `students_select_courses` (student_id, course_id) values(5, 3);
insert into `students_select_courses` (student_id, course_id) values(5, 4);
# 4.
查询需求
# 4.1
查询所有有选课的学生，选择了那些课程
#
内连接
# as
起别名
可省略
select stuId, stuName, stuAge, csId, csName, csPrice
from `students` stu
join `students_select_courses` ssc on = t_id
join `courses` cs on _id = ;
-- select * from `students` stu
-- join `students_select_courses` ssc on = t_id
-- join `courses` cs on _id = ;
-- select * from `students` as stu join `students_select_courses` as ssc on = t_id;
-- select * from `students` join `students_select_courses` on `students`.id = `students_select_courses`.student_id;
-- select * from `students` inner join `students_select_courses` on `students`.id = `students_select_courses`.student_id;
-- select * from `students` cross join `students_select_courses` on `students`.id = `students_select_courses`.student_id;
# 4.2
查询所有学生的选课情况
# 4.2
查询所有学生的选课情况
select stuId, stuName, stuAge, csId, csName, csPrice
from `students` stu
left join `students_select_courses` ssc on = t_id
left join `courses` cs on _id = ;
# 4.3
查询哪些学生没有选课
select stuId, stuName, stuAge, csId, csName, csPrice
from `students` stu
left join `students_select_courses` ssc on = t_id
left join `courses` cs on _id =
where is null;
# 4.4
查询哪些课没有被选择
select stuId, stuName, stuAge, csId, csName, csPrice
from `students` stu
right join `students_select_courses` ssc on = t_id
right join `courses` cs on _id =
where is null;
# 4.5
查询某一个学生选择了哪些课程
why
select stuId, stuName, stuAge, csId, csName, csPrice
from `students` stu
left join `students_select_courses` ssc on = t_id
left join `courses` cs on _id =
where = 1;
# tom
没有选课
select stuId, stuName, stuAge, csId, csName, csPrice
from `students` stu
left join `students_select_courses` ssc on = t_id
left join `courses` cs on _id =
where = 2;
# 5.
补充
/
扩展
# 5.1
将联合查询到的数据转成对象（一对多）
select stuId, stuName, stuAge,
json_object('id', , 'name', , 'price', )
from `students` stu
left join `students_select_courses` ssc on = t_id
left join `courses` cs on _id =
where = 1;
# {"id": 1, "name": "
英语
", "price": 100.0}
# 5.2
将查询到的多条数据，组织成对象，放入到一个数组中
（多对多）
select id, name, age,
json_arrayagg(json_object('id', , 'name', , 'price', ))
from `students` stu
join `students_select_courses` ssc on = t_id
join `courses` cs on _id =
group by ;
# [{"id": 1, "name": "
英语
", "price": 100.0}, {"id": 3, "name": "
数学
", "price": 888.0}, {"id": 4, "name": "
历史
", "price": 80.0}]
2023年12月8日发(作者：戏冰)
MySQL笔记（coderwhy老师Nodejs课程）
MySQL 笔记
数据库概述
数据库通俗来讲就是一个存储数据的仓库，数据库本质上就是一个软件。一个程序
常见的数据库有哪些？
关系型数据库
MySQL、Oracle、DB2、SQL Server、Postgre SQL等
关系型数据库通常我们会创建很多个二维数据表
数据表之间相互关联起来，形成 一对一、一对多、多对多 等关系
之后可以利用SQL语句在多张表中查询我们所需的数据
支持事务，对数据的访问更加安全
非关系型数据库
MongoDB、Redis、Memcached、HBse 等
非关系型数据库的英文其实是 Mot only SQL，也简称为 NoSQL
相当于已非关系型数据库比较简单一些，存储数据也会更加自由（甚至我们可以直接将一个复杂的json对象直接塞入到数据库
中）
NoSQL 是基于 Key-Value 的对用关系，并且查询的过程中不需要经过 SQL 解析，所以性能更高
NoSQL 通常不支持事务，需要在自己的程序中来保证一些原子性的操作
开发中如何选择？
目前在公司进行后端开发（Node、Java、Go等），还是以关系型数据库为主
比较常用的用到菲关系型数据库的，在爬取大量数据进行存储时，会比较常见
认识 MySQL
MySQL 是一个关系型数据库，其实本质上就是一个软件、一个程序
这个程序中管理着多个数据库
每个数据库中可以有多张表
每个表可以有多条数据
查看数据库
show databases;
MySQL 默认的数据库：
infomation_schema：信息数据库，其中包括 MySQL 在维护的其他数据库、表、列、访问权限等信息
performance_schema：性能数据库，记录着 MySQL Server 数据库引擎在运行过程中的一些资源消耗相关的信息
mysql：用于存储数据库管理者的用户信息、权限信息以及一些日志信息等
sys：相当于是一个简易版的performance_schema，将性能数据库中的数据汇总成更容易理解的形式
创建数据库-表
#
创建数据库
create database coderhub;
#
查看数据库
show databases;
#
指定要使用的数据库
use coderhub;
#
查看当前正在使用的数据库
select database();
#
查看数据库中的表
show tables;
#
创建表
create table users(name varchar(10), age int, height double);
#
查看表中数据
select * from users;
#
向表中插入数据
insert into users (name, age, height) values ('kobe', 40, 1.98);
认识 SQL 语句
我们希望数据库（特别是在程序中），就需要有和数据库沟通的语言，这个言就是 SQL：
SQL 是 Structured Query Language，称之为结构化查询语言，简称 SQL
使用 SQL 遍写出来的语句，就称之为 SQL 语句
SQL 语句可以用于对数据库进行操作
事实上，常见的关系型数据库SQL语句都是比较相似的，所以你学会了MySQL 中的 SQL 语句，之后去操作比如 Oracle 或者其他的
关系型数据库，也是非常方便的
SQL 语句的常用规范：
通常关键字是大写的，比如
CREATE、TABLE、SHOW
等
一条语句结束后，需要以 ; 结尾
如果遇到关键性作为表名或者字段名称，可以用 `` 包裹
SQL 语句的分类：
常见的 SQL 语句分为4类
DDL (Data Definition Language)：数据定义语言
可以通过 DDL 语句对数据库或者表进行：创建、删除、修改等操作
DML (Data Manipulation Language)：数据操作语言
可以通过 DML 语句对表进行：添加、删除、修改等操作
DQL (Data Query Language)：数据查询语言
可以通过 DQL 从数据库中查询记录（重点）
DCL (Data Control Language)：数据控制语言
对数据库、表格的权限进行相关访问控制操作
数据库的操作
#
查看所有数据库
show databases;
#
选择某一个数据库
use coderhub;
#
查看当前正在使用的数据库
select database();
#
创建一个新的数据库
-- create database douyu;
#
首先判断数据库是否已存在，避免重复创建报错
create database if not exists douyu;
#
指定字符集
utf8mb4
、排序规则
utf8mb4_0900_ai_ci
create database if not exists huya default character set utf8mb4 collate utf8mb4_0900_ai_ci;
#
删除数据库
drop database if exists douyu;
#
修改数据库的编码
alter database huya character set = utf8 collate = utf8_unicode_ci;
数据表的操作
#
查看所有的表
show tables;
#
新建表
create table if not exists `students` (
`name` varchar(10),
`age` int,
`score` int
);
#
删除表
drop table if exists `moment`;
#
查看表的结构
desc `students`;
#
查看创建表的
SQL
语句
show create table `students`;
-- CREATE TABLE `students` (
-- `name` varchar(10) DEFAULT NULL,
-- `age` int DEFAULT NULL,
-- `score` int DEFAULT NULL
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SQL 的数据类型
1. 数字类型
1. 整数数字类型：
INTEGER, INT, SMALLINT, TINYNT, MEDIUMINT, BIGINT
2. 浮点数字类型：
FLOAT, DOUBLE
(FLOAT 是 4 个字节，DOUBLE 是8个字节)
3. 精确数字类型：
DECIMAL, NUMERIC
(DECIMAL是NUMERIC的实现形式)
2. 日期和时间类型
1.
YEAR
以
YYYY
格式显示值，范围 1901到2155，和 0000
2.
DATE
类型用于具有日期部分但没有时间部分的值
1.
DATE
以格式
YYYY-MM-DD
显示值
2. 支持的范围是
1000-01-01
到
9999-12-31
3.
DATETIME
类型用于包含日期部分和时间部分的值
1.
DATETIME
以格式
YYYY-MM-DD hh:mm:ss
显示值
2. 支持的范围是
1000-01-01 00:00:00
到
9999-12-31 23:59:59
4.
TIMESTEMP
数据类型被用于包含时间和日期部分的值
1.
TIMESTAMP
以格式
YYYY-MM-DD hh:mm:ss
显示值
2. 但是它的范围是 UTC 的时间范围：**
1970-01-01 00:00:01
到
2038-01-19 03:14:07
**
另外：
DATETIME
或
TIMESTAMP
值可以包括在高达微秒（6位）精度的后小数秒一部分
比如
DATETIME
表示的范围可以是
1000-01-01 00:00:00.000000
到
9999-12-31 23:59:59.999999
3. 字符串（字符和字节）类型
1.
CHAR
类型在创建表时为固定长度，长度可以是0 到 255 之间的任何值
1. 在被查询时，会删除后面的空格
2.
VARCHAR
类型的值是可变长度的字符串，长度可以指定为 0 到 65535 之间的值
1. 在被查询时，不会删除后面的空格
3.
BINARY
和
VARBINARY
类型用于存储二进制字符串，存储的是字节字符串
4.
BLOB
用于存储大的二进制类型
5.
TEXT
用于存储大的字符类型
4. 空间类型
5. JSON 数据类型
表约束
主键
PRIMARY KEY
一张表中，我们为了区分每一条记录的唯一性，必须有一个字段永远不会重复，并且不会为空的，这个字段我们通常将它设置为主
键：
主键是表中唯一的索引
并且必须是
NOT NULL
的，如果没有设置
NOT NULL
，那么 MySQL 也会隐式的设置为
NOT NULL
主键也可以是多列索引，
PRIMARY KEY(key_part, ...)
，我们一般称之为联合主键
建议：开发中主键字段应该是和业务无关的，尽量不要使用业务字段来作为主键
唯一：
UNIQUE
create table if not exists `students` (
`name` varchar(10),
`age` int,
`score` int,
`height` decimal(10, 2),
`birthday` timestamp,
`phonenumber` varchar(20) unique
);
某些字段在开发中我们希望是唯一的，不会重复的，比如手机号码、身份证号码等，这个字段我们可以使用
UNIQUE
来约束
使用
UNIQUE
约束的字段在表中必须是不同的
对于所有引擎，
UNIQUE
索引允许
NULL
包含的列有多个值
NULL
不能为空：
NOT NULL
某些字段我们要求用户必须插入值，不可以为空，这个时候我们可以使用
NOT NULL
来约束
默认值：
DEFAULT
某些字段我们希望在没有设置值时给予一个默认值，这个时候我们可以使用
DEFAULT
来完成
自动递增：
AUTO_INCREMENT
某些字段我们希望不设置值时可以进行递增，比如用户的id，这个时候可以使用
AUTO_INCREMENT
来完成
外键约束也是最常用的一种约束手段
创建一个完整的表
use huya;
#
创建完整表的语法
create table if not exists `users` (
`id` int primary key auto_increment,
`name` varchar(20) not null,
`age` int default 0,
`phoneNum` varchar(20) unique default '',
`createTime` timestamp
);
#
修改表
# 1.
修改表的名字
alter table `users` rename to `user`;
# 2.
添加一个新的列
alter table `user` add `updateTime` timestamp;
# 3.
修改字段名称
alter table `user` change `phoneNum` `telPhone` varchar(20);
# 4.
修改字段的类型
alter table `user` modify `name` varchar(30);
# 5.
删除某一个字段
alter table `user` drop `age`;
#
补充
#
根据一个表结构去创建另外一张表
create table `user2` like `user`;
#
根据另外一个表中的所有内容创建一个新的表
create table `user3` (select * from `user`);
DML 对数据库进行增删改
# DML
#
插入数据
insert into `users` values(110, 'why', 18, '020-110110', '2020-10-20');
insert into `users` (`name`, `age`, `phoneNum`, `createTime`) values('kobi', 28, '000-111111', '2020-10-10');
insert into `users` (`name`, `phoneNum`) values('lilei', '000-111112');
#
需求
createTime
和
updateTime
可以自动设置值
alter table `users` modify `createTime` timestamp default current_timestamp;
#
修改完数据后，直接可以显示最新的更新时间
alter table `users` modify `updateTime` timestamp default current_timestamp on update current_timestamp;
insert into `users` (`name`, `phoneNum`) values('hanmeimei', '000-111113');
insert into `users` (`name`, `phoneNum`) values('lucy', '');
#
删除数据
# 1.
删除所有数据
-- delete from `users`;
# 2.
删除符合条件的数据
delete from `users` where id = 110;
#
更新数据
#
会修改表中所有的数据
-- update `users` set `name` = 'lily', `phoneNum` = '010-110110';
#
会修改符合条件的数据
update `users` set `name` = 'lily', `phoneNum` = '010-110110' where id = 111;
DQL 语句
DQL：Date Query Language（数据库查询语言）
SELETE
用于从一个或者多个表中检索选中的行（Record）
格式
SELECT select_expr [, select_expr]...
[FROM table_references]
[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 * from `products`;
#
查询指定子弹
select title, price from `products`;
#
对字段结果起一个别名
select title as phoneTitle, price as currentPrise from `products`;
where
查询条件
WHERE
的比较运算符
# 1.
条件判断语句
#
案例：查询价格小于
1000
的手机
select * from `products` where price < 1000;
#
案例：价格等于
999
的手机
select * from `products` where price = 999;
#
案例：价格不等于
999
的手机
select * from `products` where price != 999;
select * from `products` where price <> 999;
#
案例：查询品牌是华为的手机
select * from `products` where brand = '华为';
WHERE
逻辑运算符
# 2.
逻辑运算语句
#
案例：价格大于
1000
小于
2000
的手机
select * from `products` where price > 1000 and price < 2000;
select * from `products` where price > 1000 && price < 2000;
# between and
包含等于
select * from `products` where price between 1000 and 2000;
#
案例：价格在
5000
以上或者品牌是华为的手机
select * from `products` where price > 5000 || brand = '华为';
select * from `products` where price > 5000 or brand = '华为';
#
将某些值设置为
NULL
update `products` set url = NULL where id >= 85 and id <= 88;
#
案例：查询某一个值为
NULL
select * from `products` where url is NULL;
select * from `products` where url = NULL;
#
案例：查询某一个值不为
NULL
select * from `products` where url is not NULL;
WHERE
模糊查询
模糊查询使用
LIKE
关键字，结合2个特殊符号：
%
表示匹配任意个的任意字符
_
表示匹配一个的任意字符
# 3.
模糊查询
#
案例：查询以
v
开头的
title
select * from `products` where title like 'v%';
#
案例：
title
字段里面
所有包含
M
字符的
select * from `products` where title like '%M%';
select * from `products` where title like '%P%';
#
案例：
title
字段里面
所有第二个字符包含
P
的
select * from `products` where title like '_P%';
# 4. IN
取多个值中的其中一个即可
select * from `products` where brand = '小米' or brand = '华为' or brand = '苹果';
select * from `products` where brand in ('华为', '小米', '苹果');
结果排序
#
按照价格的升序
select * from `products` where brand in ('华为', '小米', '苹果') order by price ASC;
#
按照价格
price
的升序，如果价格相同，再按照评分
score
的降序
select * from `products` where brand in ('华为', '小米', '苹果') order by price ASC, score DESC;
分页查询
#
格式
1
：
LIMIT limit OFFSET offset
#
格式
2
：
LIMIT limit, offset
# 0~20
select * from `products` limit 20 offset 0;
select * from `products` limit 0, 20;
# 21~40
select * from `products` limit 20 offset 20;
select * from `products` limit 20, 20;
# 41~60
select * from `products` limit 40, 20;
聚合函数：表示对值集合进行操作的组（集合）函数
# 1.
聚合函数的使用
#
求所有手机的价格的总和
select sum(price) from `products`;
-- select sum(price) totalPrice from `products`;
#
求一下华为手机价格的总和
select sum(price) from `products` where brand = '华为';
#
求华为手机的平均价格
select avg(price) from `products` where brand = '华为';
#
最高手机个最低手机的价格
select max(price) from `products`;
select min(price) from `products`;
#
求华为、苹果手机的个数
select count(*) from `products` where brand = '华为';
select count(url) from `products` where brand = '苹果';
#
不会将
url
字段为
null
的字段计算在内
select count(url) from `products` where brand = '苹果';
select count(price) from `products`;
#
价格重复的部不多次计数
select count(distinct price) from `products`;
分组
Group by
，通常和聚合函数一起使用，先分组，再进行聚合函数的计算
# 2.
分组
group by
的使用
#
根据品牌进行分组
展示不同品牌手机的平均价格
数量
评分
select brand, avg(price), count(*), avg(score) from `products` group by brand;
# 3. HAVING
的使用
#
所有品牌手机
平均价格大于
2000
的
select brand, avg(price) avgPrice, count(*), avg(score) from `products` group by brand having avgPrice > 2000;
# 4.
求评分
score > 7.5
的手机的平均价格
#
升级
平均分大于
7.5
分的手机，按照品牌进行分类，求出平均价格
select brand, avg(price) from `products` where score > 7.5 group by brand;
多表操作
外键
# 1.
创建
brand
表和插入数据
create table if not exists `brand` (
`id` int primary key auto_increment,
`name` varchar(20),
`website` varchar(100),
`phoneRank` int
);
insert into `brand` (name, website, phoneRank) values ('华为', '', 2);
insert into `brand` (name, website, phoneRank) values ('苹果', '', 10);
insert into `brand` (name, website, phoneRank) values ('小米', '', 5);
insert into `brand` (name, website, phoneRank) values ('oppo', '', 12);
insert into `brand` (name, website, phoneRank) values ('谷歌', '', 9);
insert into `brand` (name, website, phoneRank) values ('京东', '', 6);
# 2.
给
brand_id
设置引用
brand
中的
id
外键约束
#
添加一个
brand_id
的字段
alter table `products` add `brand_id` int;
-- alter table `products` drop `brand_id`;
#
修改
brand_id
为外键
alter table `products` add foreign key(brand_id) references brand(id);
#
设置
brand_id
的值
update `products` set `brand_id` = 1 where `brand` = '华为';
update `products` set `brand_id` = 2 where `brand` = '苹果';
update `products` set `brand_id` = 3 where `brand` = '小米';
update `products` set `brand_id` = 4 where `brand` = 'oppo';
# 3.
修改和删除外键引用的
id
#
报错
-- update `brand` set `id` = 100 where `id` = 1;
#
外键存在时更新和删除数据
# 4.
修改
brand_id
关联外键时的
action
# 4.1
获取到创建表时候的外键名称
show create table `products`;
# 4.2
根据名称将之前的外键删除
alter table `products` drop foreign key products_idfk_1;
# 4.3
重新添加外键约束
并设置新的
action
alter table `products` add foreign key(brand_id) references brand(id)
on update cascade
on delete restrict;
update `brand` set `id` = 100 where `id` = 1;
更新外键需要修改
on delete
或者
on update
的值
我们可以给更新或删除时设置以下几个值：
RESTRICT
（默认属性）：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话会报错，不允许更新或删
除
NO ACTION
：和
RESTRICT
是一致的，是在 SQL 标准中定义的
CASCADE
：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话：
更新：那么会更新对应的记录
删除：那么关联的记录会一起被删除掉
SET NULL
：当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话，将对应的值设置为
NULL
多表查询
默认多表查询的结果
第一张表 108 * 第二张表的 6 条数据
也就是说第一张表中的每一个数据，都会和第二张表中的咩一条数据结合一次
这个结果我们称之为 笛卡尔乘积，也称之为 直积，表示为 X*Y
但是事实上很多的数据是没有意义的，比如华为和苹果、小米的品牌结合起来就是没有意义的，我们可不可以进行筛选呢？
使用
where
来进行筛选
这个表示查询到笛卡尔乘积后的结果中，符合
_id =
条件的数据过滤出来
# 1.
获取到的是笛卡尔乘积
select * from `products`, `brand`;
#
获取到的是笛卡尔乘积进行筛选
select * from `products`, `brand` where _id = ;
多表之间的连接
左连接
右连接
内连接
全连接
# 2.
左连接
# 2.1
查询所有的手机以及对应的品牌
（包括没有品牌信息的手机）以及对应的品牌
null
select * from `products` left join `brand` on _id = ;
select * from `products` left outer join `brand` on _id = ;
# 2.2
查询没有对应品牌数据的手机
select * from `products` left join `brand` on _id = where is null;
# 3.
右链接
# 3.1
查询所有的品牌（没有对应的手机数据，品牌也显示）以及对应的手机数据
select * from `products` right join `brand` on _id = ;
select * from `products` right outer join `brand` on _id = ;
# 3.2
查询没有对应品牌数据的手机
select * from `products` right join `brand` on _id = where _id is null;
# 4.
内连接
# 4.1
查询所有带品牌数据的手机
select * from `products` join `brand` on _id = ;
select * from `products` join `brand` on _id = where price = 8699;
# 5.
全连接
# mysql
是不支持
full outer join
#
通过左连接和右链接联合来实现
(select * from `products` left join `brand` on _id = )
union
(select * from `products` right join `brand` on _id = );
-- select * from `products` full join `brand` on _id = ;
(select * from `products` left join `brand` on _id = where is null)
union
(select * from `products` right join `brand` on _id = where _id is null);
多对多关系
在开发中我们会遇到多对多的关系：
比如学生可以选择多门课程，一个课程可以被多个学生选择
#
基本数据的模拟
#
创建学生表
create table if not exists `students` (
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);
#
创建课程表
create table if not exists `courses` (
`id` int primary key auto_increment,
`name` varchar(20) not null,
`price` double
);
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('物理', 888);
insert into `courses` (name, price) values('地理', 333);
# 2.
建立关系表
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,
foreign key (course_id) references courses(id) on update cascade
);
# 3.
学生选课
#
比如
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);
insert into `students_select_courses` (student_id, course_id) values(1, 4);
# lilei
选择了语文、历史
insert into `students_select_courses` (student_id, course_id) values(3, 2);
insert into `students_select_courses` (student_id, course_id) values(3, 4);
# lily
选择了语文、数学、历史
insert into `students_select_courses` (student_id, course_id) values(5, 2);
insert into `students_select_courses` (student_id, course_id) values(5, 3);
insert into `students_select_courses` (student_id, course_id) values(5, 4);
# 4.
查询需求
# 4.1
查询所有有选课的学生，选择了那些课程
#
内连接
# as
起别名
可省略
select stuId, stuName, stuAge, csId, csName, csPrice
from `students` stu
join `students_select_courses` ssc on = t_id
join `courses` cs on _id = ;
-- select * from `students` stu
-- join `students_select_courses` ssc on = t_id
-- join `courses` cs on _id = ;
-- select * from `students` as stu join `students_select_courses` as ssc on = t_id;
-- select * from `students` join `students_select_courses` on `students`.id = `students_select_courses`.student_id;
-- select * from `students` inner join `students_select_courses` on `students`.id = `students_select_courses`.student_id;
-- select * from `students` cross join `students_select_courses` on `students`.id = `students_select_courses`.student_id;
# 4.2
查询所有学生的选课情况
# 4.2
查询所有学生的选课情况
select stuId, stuName, stuAge, csId, csName, csPrice
from `students` stu
left join `students_select_courses` ssc on = t_id
left join `courses` cs on _id = ;
# 4.3
查询哪些学生没有选课
select stuId, stuName, stuAge, csId, csName, csPrice
from `students` stu
left join `students_select_courses` ssc on = t_id
left join `courses` cs on _id =
where is null;
# 4.4
查询哪些课没有被选择
select stuId, stuName, stuAge, csId, csName, csPrice
from `students` stu
right join `students_select_courses` ssc on = t_id
right join `courses` cs on _id =
where is null;
# 4.5
查询某一个学生选择了哪些课程
why
select stuId, stuName, stuAge, csId, csName, csPrice
from `students` stu
left join `students_select_courses` ssc on = t_id
left join `courses` cs on _id =
where = 1;
# tom
没有选课
select stuId, stuName, stuAge, csId, csName, csPrice
from `students` stu
left join `students_select_courses` ssc on = t_id
left join `courses` cs on _id =
where = 2;
# 5.
补充
/
扩展
# 5.1
将联合查询到的数据转成对象（一对多）
select stuId, stuName, stuAge,
json_object('id', , 'name', , 'price', )
from `students` stu
left join `students_select_courses` ssc on = t_id
left join `courses` cs on _id =
where = 1;
# {"id": 1, "name": "
英语
", "price": 100.0}
# 5.2
将查询到的多条数据，组织成对象，放入到一个数组中
（多对多）
select id, name, age,
json_arrayagg(json_object('id', , 'name', , 'price', ))
from `students` stu
join `students_select_courses` ssc on = t_id
join `courses` cs on _id =
group by ;
# [{"id": 1, "name": "
英语
", "price": 100.0}, {"id": 3, "name": "
数学
", "price": 888.0}, {"id": 4, "name": "
历史
", "price": 80.0}]