【数据库通关之路】 MySQL 全路线学习知识点梳理（中）

前言

本文是 MYSQL零基础小白学习 系列的第二篇文章，点此阅读 上一篇文章

文末包邮送《分布式中间件核心原理与RocketMQ最佳实践 》(点击下方目录直达)一本，本文每+1000浏览额外加抽一人

6. 实战案例详解

需求：设计包含如下信息的学生表，请注重数据类型、长度的合理性。

1. 编号
2. 姓名，姓名最长不超过10个汉字
3. 性别，因为取值只有两种可能，因此最多一个汉字
4. 生日，取值为年月日
5. 成绩，小数点后保留两位
6. 地址，最大长度不超过 64
7. 学生状态（用数字表示，正常、休学、毕业…）

在完成这样一个案例前，首先要创建一个学生数据库，在数据库中创建一张新的表，创建表时注意语法格式，数据类型和长度的合理性。

以管理员身份运行命令提示符cmd，启动Mysql服务，登录MySQL：

创建学生信息数据库：

create database if not exists student;
1

使用student数据库：

use student;
1

创建数据表：

create table stu(
		id int ,-- 编号
		name varchar(10),-- 姓名
		gender char(1),-- 性别
		birthday date,-- 生日
		score double(5,2) ,-- 分数
		addr varchar(50),-- 地址
		status tinyint-- 状态
);
1
2
3
4
5
6
7
8
9

---

现在，我们已经学会了写SQL来操作数据库，但是我们在命令行中写SQL时，往往有体验感差，效率低等问题，现在开始我们就要学习在MySQL的图形化客户端Navicat中执行SQL语句。

Navicat 为数据库管理、开发和维护提供了一款直观而强大的图形化界面，大大的提高了工作效率，建议在学习中也使用这款开发工具。接下来，在Navicat中新建数据库，新建查询，我们就可以编写SQL并且执行SQL语句了。

7. DML- 增删改数据

7.1 添加数据

给指定列添加数据：

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

给全部列添加数据：

insert into 表名 values(值1,值2...);
1

批量添加数据：

insert into 表名(列名1,列名2...) values(值1,值2...),(值1,值2...)，(值1,值2...)...;
1

批量添加数据(省略字段名)：

insert into 表名 values(值1,值2...),(值1,值2...)，(值1,值2...)...;
1

在开发过程中添加数据时是不建议省略字段名的，这样降低了代码的可读性，使效率下降。例如：

查询表中的所有数据的方法是：

select * from 表名;
1

后面会用到的。

需求：往下面的tb_user表中添加一条数据。

insert into tb_user(id,name) values(2,'李四');
1

添加成功：

---

7.2 修改数据

修改表的数据：

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

在修改数据时，也可以不使用where条件，此时的操作是修改整列数据，这样的操作是很危险的。

需求：把下面tb_user表中的张三的密码改为abc23

update tb_user set passwor d ='abc123' where name='张三';
1

修改成功：

---

7.3 删除数据

删除表的数据：

delete from 表名 [where 条件];
1

在删除某条数据时，如果不使用where条件，将会导致删除整个表的数据。

需求：删除tb_user表中的李四记录。

delete from tb_user where name='李四';
1

操作成功：

8. DQL- 数据的查询操作

查询是数据操作至关重要的一部分，比如说在所有商品中查找出价格在规定范围内的所有商品，要想把数据库中的数据在客户端中展示给用户，一般都进行了查询的操作。

在实际开发中，我们要根据不同的需求，并且考虑查询的效率来决定怎样进行查询，学习查询前，可以先看看查询的完整语法：

SELECT
	字段列表
FROM
	表名列表
WHERE
	条件列表
GROUP BY
	分组字段
HAVING
	分组后条件
ORDER BY
	排序字段
LIMIT
	分页限定
1
2
3
4
5
6
7
8
9
10
11
12
13
14

根据查询的完整语法中的关键字，我们分别来学习基础查询，条件查询，排序查询，分组查询和分页查询。

下面的练习中使用以下的案例学习单表查询：

-- 删除stu表
drop table if exists stu;
-- 创建stu表
CREATE TABLE stu (
id int, -- 编号
name varchar(10), -- 姓名
age int, -- 年龄
gender varchar(5), -- 性别
math double(5,2), -- 数学成绩
english double(5,2) -- 英语成绩

);
-- 添加数据
INSERT INTO stu(id,name,age,gender,math,english)
VALUES
(1,'小张',23,'男',66,78),
(2,'小李',20,'女',98,87),
(3,'小陈',55,'男',56,77),
(4,'小樊',20,'女',76,65),
(5,'小马',20,'男',86,NULL),
(6,'小赵',57,'男',99,99);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

在Navicat中选中SQL并执行：

8.1 基础查询

1. 基础查询语法

查询多个字段：

select 字段列表 from 表名;
1

查询全部字段：

select * from 表名;
1

去除重复记录：

select distinct 字段列表 from 表名;
1

起别名操作：

select 字段名 别名 from 表名;
1

2. 基础查询练习

使用学生表进行基础查询练习：

查询多个字段的练习：

select name,math from stu;
1

起别名操作练习：

select name,english 英语成绩 from stu;
1

8.2 条件查询

1. 条件查询语法

一般语法：

select 字段列表 from 表名 where 条件列表;
1

条件查询一般配合运行符进行，下面是常见的几个运算符：

2. 条件查询练习

使用学生表进行条件查询练习：

查询年龄大于20的学生信息：

select * from stu where age>20;
1

查询年龄等于18岁 或者 年龄等于20岁 或者 年龄等于21岁的学生信息：

select * from stu where age in(18,20,21);
1

模糊查询使用like关键字，可以使用通配符进行占位:

• _ : 代表单个任意字符
• % : 代表任意个数字符

查询姓名中含有张的学生信息：

select * from stu where name like '%张%';
1

8.3 排序查询

1. 排序查询语法

select 字段列表 from 表名 order by 排序字段名1 [排序方式]...;
1

注：排序方式有两种：分别是升序ASC和降序DESC，默认情况下是升序ASC。

2. 排序查询练习

使用学生表进行排序查询练习：

查询学生信息，按照数学成绩降序排列：

select * from stu order by math DESC;
1

8.4 聚合函数

1. 聚合函数语法

什么是聚合函数呢？在进行查询操作时，往往需要对一整列进行运算，例如可以计算一整列成绩数据的平均值，我们就要使用聚合函数。下面是常见的聚合函数：

一般语法：

select 聚合函数 from 表名;
1

注：NULL值不参与聚合函数运算。

2.聚合函数练习

使用学生表进行聚合函数的练习：

统计该表中一共有几个学生：

select count(id) from stu;
1

上面我们使用某一字段进行运算，这样做可能面临的问题是某一个值可能是NULL，所以我们一般使用 * 进行运算，因为一行中不可能所有的字段都是NULL。

select count(*) from stu;
1

查询数学成绩的平均分:

select avg(math) from stu;
1

8.5 分组查询

1. 分组查询语法

select 字段列表 from 表名 [where 分组前的条件限定] group by 分组字段名 [having 分组后的条件过滤]
1

注：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义。

2. 分组查询练习

使用学生表进行分组查询练习：

查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组:

select gender, avg(math),count(*) from stu where math > 70 group by gender;
1

查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组，分组之后人数大于2个的:

select gender, avg(math),count(*) from stu where math > 70 group by gender having count(*) > 2;
1

注：where 和 having 执行时机不一样：where 是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过滤。所以，where 不能对聚合函数进行判断，having 可以。

8.6 分页查询

1. 分页查询语法

在我们的印象中，网页在展示大量的数据时，往往不是把数据一下全部展示出来，而是用分页展示的形式，其实就是对数据进行分页查询的操作，即每次只查询一页的数据展示到页面上。

select 字段列表 from 表名 limit 查询起始索引,查询条目数;
1

在 limit 关键字中，查询起始索引这个参数是从0开始的。

2. 分页查询练习

使用学生表进行分页查询练习：

从0开始查询，查询3条数据：

select * from stu limit 0,3;
1

起始索引 = (当前页码 - 1) * 每页显示的条数

在SQL标准中，一共规定了6种不同的约束，包括非空约束，唯一约束和检查约束等，而在MySQL中是不支持检查约束的，所以这篇文章先对其余5种约束做一个详解和练习。

9. 约束的概念

约束是作用于表中列上的规则，用于限制加入表的数据。例如，作为主键的列一定是非空的唯一的，否则将无法对数据进行区分。约束的存在保证了数据库中数据的正确性、有效性和完整性。所以约束在数据库设计中是非常重要的。

10. 约束的分类

前面说到SQL标准把约束分为了6大类，分别是非空约束，唯一约束，主键约束，检查约束，默认约束和外键约束，添加约束时我们只需要在SQL中添加关键词，便可以限制表中的数据。

11. 非空约束

目的：保证列中所有的数据不能有null值

添加约束：

CREATE TABLE 表名(
	列名 数据类型 NOT NULL,
	…
);

1
2
3
4
5

建完表后添加非空约束：

ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;
1

删除约束：

ALTER TABLE 表名 MODIFY 字段名 数据类型;
1

12. 唯一约束

目的：保证列中所有数据各不相同

添加约束：

CREATE TABLE 表名(
	列名 数据类型 UNIQUE [AUTO_INCREMENT],
	-- AUTO_INCREMENT: 当不指定值时自动增长
	…
);

CREATE TABLE 表名(
	列名 数据类型,
	…
	[CONSTRAINT] [约束名称] UNIQUE(列名)
);
1
2
3
4
5
6
7
8
9
10
11

建完表后添加唯有约束：

ALTER TABLE 表名 MODIFY 字段名 数据类型 UNIQUE;
1

删除约束：

ALTER TABLE 表名 DROP INDEX 字段名;
1

---

【免费赠书】

【！参与方式！】

点赞+收藏+评论本文“人生苦短，我爱MySQL”
截止日期：2023-04-15 早上9:00

注：抽奖方式为<程序随机抽取>，会在我的主页动态如期公布中奖者，包邮到家。

---

【内容简介↓】

本书从分布式系统的基础概念讲起，逐步深入分布式系统中间件进阶实战，并在最后结合一个大型项目案例进行讲解，重点介绍了使用Spring Cloud框架整合各种分布式组件的过程，让读者不但可以系统地学习分布式中间件的相关知识，而且还能对业务逻辑的分析思路、实际应用开发有更为深入的理解。

全书共分12章，前3个章节是学习分布式系统架构的准备阶段。第1章开篇部分，讲解演进过程中分布式系统是如何出现的；第2章Spring部分，讲解如何搭建目前流行的Spring Boot和Spring Cloud框架；第3章容器部分，讲解目前最流行的Docker容器技术和Kubernetes容器编排工具；第4~8章深入讲解消息中间件RocketMQ的相关知识，理论与实战并存；第9章将深入RocketMQ底层，探索阅读源码的乐趣，掌握精通RocketMQ的同时学会阅读源码的方法；第10章和第11章讲解分布式系统中必须考虑的问题：分布式事务与分布式锁；第12章以一个电商系统业务为例，让读者体验一个项目从无到有的过程，并学以致用。

本书内容由浅入深、结构清晰、实例丰富、通俗易懂、实用性强，适合需要全方位学习分布式中间件相关技术的人员，也适合培训学校作为培训教材，还可作为大、中专院校相关专业的教学参考书。
京东自营购买链接：《分布式中间件核心原理与RocketMQ最佳实践》