数据库开发-MySQL

YangeIT大约 52 分钟数据库数据库DDLSQLDMLDCLDQLMySQLNavicatIdea数据模型

数据库开发-MySQL

今日目标

  1. 能够说出 为什么要使用数据库以及常见的几种数据库管理系统 🍐
  2. 能够说出 数据库的存在形式 🍐
  3. 能够操作 Mysql 安装成功并且登陆成功
  4. 能够使用 Navicat 操作数据库 ✏️
  5. 能够使用 DML 添加数据，删除数据，修改数据 ✏️
  6. 能够使用 DQL 查询数据 ✏️ ❤️
  7. 基本查询 ✏️
  8. 条件查询 ✏️
  9. 聚合函数 ✏️
  10. 分组查询 ✏️
  11. 排序查询 ✏️
  12. 分页查询 ✏️

知识储备

  1. 理解数据要持久化存储的需求
  2. 有用过excel表格存储数据的经历
  3. 有使用过wps软件操作excel表格的经历

背景

点击查看GIF动画图解open in new window 👈

  1. 一般将web应用程序分为三层，即：Controller、Service、Daoopen in new window
  2. 在真实的企业开发中呢，都会采用数据库来存储和管理数据(数据库：英文为 DataBase，简称DB,它是存储和管理数据的仓库)
    • 像我们日常访问的电商网站京东，企业内部的管理系统OA、ERP、CRM这类的系统，以及大家每天都会刷的头条、抖音类的app，那这些大家所看到的数据，其实都是存储在数据库中的。
  3. SQL（Structured Query Language，简称SQL）：结构化查询语言，它是操作关系型数据库的编程语言，定义了一套操作关系型数据库的统一标准。我们学习数据库开发，最为重要的就是学习SQL语句
  4. 排名前十的数据库：image-20221205120408757

结论

只要我们学会了SQL语句，就可以通过SQL语句来操作Mysql🎯，也可以通过SQL语句来操作其他关系型数据库，如oracle等

1. MySQL概述 ✏️ 🍐

1.1 安装 ✏️

安装

image-20220610191829748
image-20220610191829748

官网：https://dev.mysql.com/open in new window

MySQL官方提供了两个版本：

  • 商业版本（MySQL Enterprise Edition）
    • 该版本是收费的，我们可以使用30天。 官方会提供对应的技术支持。
  • 社区版本（MySQL Community Server） 👈
    • 该版本是免费的，但是MySQL不会提供任何的技术支持。

本节，采用的是MySQL的社区版本（8.0.31）

1.2 数据模型 🍐

数据模型

目标：知悉Mysql数据库当中到底是 如何来存储和管理数据

关系型数据库（RDBMS）

概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。

而所谓二维表，指的是由行和列组成的表，如下图：

image-20221205145028667
image-20221205145028667

二维表的优点：

  • 使用表存储数据，格式统一，便于维护
  • 使用SQL语言操作，标准统一，使用方便，可用于复杂查询

我们之前提到的MySQL、Oracle、DB2、SQLServer这些都是属于关系型数据库，里面都是基于二维表存储数据的。

结论：基于二维表存储数据的数据库就成为关系型数据库，不是基于二维表存储数据的数据库，就是非关系型数据库（比如大家后面要学习的Redis，就属于非关系型数据库）。

总结

  1. 关系型数据库: 建立在关系模型基础上，由多张相互连接的二维表组成的数据库。
  2. 一个数据库服务器中可以创建多个数据库，一个数据库中也可以包含多张表，而一张表中又可以包含多行记录。

课堂作业

🎻 0. mysql数据库管理系统，是哪个公司的？有什么特性？🎤

🎻 1. mysql数据库管理系统，数据库，表，字段之间存在什么关系？🎤

🎻 2. 要往mysql数据库中插入数据或者查询数据，需要用什么语言？ 🎤

1.3 SQL简介

SQL简介

SQL：结构化查询语言，使用SQL可以操作任何关系型数据库.🎯

SQL通用语法

  1. SQL语句可以单行或多行书写，以分号结尾。
image-20221205151656214
image-20221205151656214
  1. SQL语句可以使用空格/缩进来增强语句的可读性。
image-20221205151953441
image-20221205151953441
  1. MySQL数据库的SQL语句不区分大小写。(注：oracle数据库区分)
image-20221205152112070
image-20221205152112070
  1. 注释：
  • 单行注释：-- 注释内容 或 # 注释内容(MySQL特有)
  • 多行注释： /注释内容/

以上就是SQL语句的通用语法，这些通用语法大家目前先有一个直观的认识，我们后面在讲解每一类SQL语句的时候，还会再来强调通用语法。

课堂作业

🎻 1.运行sql在线练习网站open in new window 中的案例，观察浏览器右侧的结果

  1. 观察sql语句的格式
  2. 思考案例代码中，哪些是插入数据的，哪些是查询数据的，哪些是创建数据库的，哪些是创建表格的？🔍

2. 数据库设计-DDL 🍐

下面我们就正式的进入到SQL语句的学习，在学习之前先给大家介绍一下我们要开发一个项目，整个开发流程是什么样的，以及在流程当中哪些环节会涉及到数据库。🎯

2.1 项目开发流程🍐

项目开发流程

image-20220829112953742
image-20220829112953742

需求文档：

image-20221205154101142
image-20221205154101142

设计：

  • 拿到产品原型和需求文档之后，我们首先要做的不是编码，而是要先进行项目的设计，其中就包括概要设计、详细设计、接口设计、数据库设计等等。
  • 数据库设计根据产品原型以及需求文档，要分析各个模块涉及到的表结构以及表结构之间的关系，以及表结构的详细信息。最终我们需要将数据库以及数据库当中的表结构设计创建出来。

开发/测试：

  • 参照页面原型和需求进行编码，实现业务功能。在这个过程当中，我们就需要来操作设计出来的数据库表结构，来完成业务的增删改查操作等。

部署上线：

  • 在项目的功能开发测试完成之后，项目就可以上线运行了，后期如果项目遇到性能瓶颈，还需要对项目进行优化。优化很重要的一个部分就是数据库的优化，包括数据库当中索引的建立、SQL 的优化、分库分表等操作。

在上述的流程当中，针对于数据库来说，主要包括三个阶段：

  1. 数据库设计阶段
    • 参照页面原型以及需求文档设计数据库表结构
  2. 数据库操作阶段
    • 根据业务功能的实现，编写SQL语句对数据表中的数据进行增删改查操作
  3. 数据库优化阶段
    • 通过数据库的优化来提高数据库的访问性能。优化手段：索引、SQL优化、分库分表等

接下来我们就先来学习第一部分数据库的设计，而数据库的设计就是来定义数据库，定义表结构以及表中的字段。

总结

  1. 项目开发流程分为：需求文档、设计、开发/测试、部署上线
  2. 在上述的流程当中，针对于数据库来说，主要包括三个阶段：
  • 数据库设计阶段
  • 数据库操作阶段
  • 数据库优化阶段

课堂作业

  1. 项目开发流程中数据库设计，主要是设计什么？🎤
  2. 在开发阶段，需要对数据库进行什么操作？

2.2 数据库操作

DDL英文全称是Data Definition Language(数据定义语言)，用来定义数据库对象(数据库、表)

DDL中数据库的常见操作：查询、创建、使用、删除

2.2.1 查询数据库

查询所有数据库：

记得输入密码和用户名先连接数据库

show databases;

命令行中执行效果如下：

image-20220829124104154
image-20220829124104154

查询当前数据库：

select database();

命令行中执行效果如果：

image-20221205174615074
image-20221205174615074

我们要操作某一个数据库，必须要切换到对应的数据库中。

通过指令：select database() ，就可以查询到当前所处的数据库

课堂作业

🎻 1.使用sql的ddl语言 创建一个名字为 javawebdb的数据库

2.3 图形化工具 ✏️

为了提高开发效率，都会借助于现成的图形化管理工具来操作数据库

在命令行当中来敲这些SQL语句很不方便，主要的原因有以下 3 点：

  1. 没有任何代码提示。（全靠记忆，容易敲错字母造成执行报错）
  2. 操作繁琐，影响开发效率。（所有的功能操作都是通过SQL语句来完成的）
  3. 编写过的SQL代码无法保存。

目前MySQL主流的图形化界面工具有以下几种：

image-20221205184802646
image-20221205184802646

DataGrip是JetBrains旗下的一款数据库管理工具，是管理和开发MySQL、Oracle、PostgreSQL的理想解决方案。

官网： https://www.jetbrains.com/zh-cn/datagrip/open in new window

安装： 参考资料中提供的《DataGrip安装手册》

说明：DataGrip这款工具可以不用安装，因为Jetbrains公司已经将DataGrip这款工具的功能已经集成到了 IDEA当中，所以我们就可以使用IDEA来作为一款图形化界面工具来操作Mysql数据库。

点击查看Idea连接数据库
2.2.3.1 连接数据库

1、打开IDEA自带的Database

image-20221205190929756
image-20221205190929756

2、配置MySQL

image-20221205191143072
image-20221205191143072

3、输入相关信息

image-20221205192606038
image-20221205192606038

4、下载MySQL连接驱动

image-20221205192847190
image-20221205192847190

5、测试数据库连接

image-20221205193304124
image-20221205193304124

6、保存配置

image-20221205193409856
image-20221205193409856

默认情况下，连接上了MySQL数据库之后， 数据库并没有全部展示出来。 需要选择要展示哪些数据库。具体操作如下：

点击查看GIF动画图解open in new window👈

点击查看Idea操作数据库
2.2.3.2 操作数据库

创建数据库：

image-20221205195346655
image-20221205195346655
image-20221205195510716
image-20221205195510716

有了图形化界面工具后，就可以方便的使用图形化工具：创建数据库，创建表、修改表等DDL操作。

其实工具底层也是通过DDL语句操作的数据库，只不过这些SQL语句是图形化界面工具帮我们自动完成的。

查看所有数据库：

image-20221205195128343
image-20221205195128343
image-20221205195833439
image-20221205195833439

其实image 也蛮好用的👍，经济宽裕的小伙伴，可以购买正版试试，如果要试用，点击查看Navicat安装教程 👈

2.3 表操作 ✏️ 🍐

表结构的操作也是包含四个部分：创建表、查询表、修改表、删除表。

2.3.1 创建

2.3.1.1 语法
create table  表名(
 字段1  字段1类型 [约束]  [comment  字段1注释 ],
 字段2  字段2类型 [约束]  [comment  字段2注释 ],
 ......
 字段n  字段n类型 [约束]  [comment  字段n注释 ] 
) [ comment  表注释 ] ;

注意： [ ] 中的内容为可选参数； 最后一个字段后面没有逗号

案例：创建tb_user表

  • 对应的结构如下：
image-20220829143005524
image-20220829143005524
  • 建表语句：
create table tb_user (
    id int comment 'ID,唯一标识',   # id是一行数据的唯一标识（不能重复）
    username varchar(20) comment '用户名',
    name varchar(10) comment '姓名',
    age int comment '年龄',
    gender char(1) comment '性别'
) comment '用户表';

数据表创建完成，接下来我们还需要测试一下是否可以往这张表结构当中来存储数据。

双击打开tb_user表结构，大家会发现里面没有数据：

image-20221205211008929
image-20221205211008929

添加数据：

点击查看GIF动画图解open in new window 👈

此时我们再插入一条数据：

image-20221205214447060
image-20221205214447060

我们之前提到过：id字段是一行数据的唯一标识，不能有重复值。但是现在数据表中有两个相同的id值，这是为什么呢？

  • 其实我们现在创建表结构的时候， id这个字段我们只加了一个备注信息说明它是一个唯一标识，但是在数据库层面呢，并没有去限制字段存储的数据。所以id这个字段没有起到唯一标识的作用。

想要限制字段所存储的数据，就需要用到数据库中的约束。

点击查看数据库中的约束
2.3.1.2 约束

概念：所谓约束就是作用在表中字段上的规则，用于限制存储在表中的数据。

作用：就是来保证数据库当中数据的正确性、有效性和完整性。（后面的学习会验证这些）

在MySQL数据库当中，提供了以下5种约束：

约束描述关键字
非空约束限制该字段值不能为nullnot null
唯一约束保证字段的所有数据都是唯一、不重复的unique
主键约束主键是一行数据的唯一标识，要求非空且唯一primary key
默认约束保存数据时，如果未指定该字段值，则采用默认值default
外键约束多表中讲解让两张表的数据建立连接，保证数据的一致性和完整性foreign key

注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

案例：创建tb_user表

  • 对应的结构如下：
image-20220829143005524
image-20220829143005524

在上述的表结构中:

  • id 是一行数据的唯一标识

  • username 用户名字段是非空且唯一的

  • name 姓名字段是不允许存储空值的

  • gender 性别字段是有默认值，默认为男

  • 建表语句：
create table tb_user (
    id int primary key comment 'ID,唯一标识', 
    username varchar(20) not null unique comment '用户名',
    name varchar(10) not null comment '姓名',
    age int comment '年龄',
    gender char(1) default '男' comment '性别'
) comment '用户表';

数据表创建完成，接下来测试一下表中字段上的约束是否生效

image-20221205215309011
image-20221205215309011
image-20221205215632599
image-20221205215632599

大家有没有发现一个问题：id字段下存储的值，如果由我们自己来维护会比较麻烦(必须保证值的唯一性)。MySQL数据库为了解决这个问题，给我们提供了一个关键字：auto_increment（自动增长）

主键自增：auto_increment

  • 每次插入新的行记录时，数据库自动生成id字段(主键)下的值
  • 具有auto_increment的数据列是一个正数序列开始增长(从1开始自增)
create table tb_user (
    id int primary key auto_increment comment 'ID,唯一标识', #主键自动增长
    username varchar(20) not null unique comment '用户名',
    name varchar(10) not null comment '姓名',
    age int comment '年龄',
    gender char(1) default '男' comment '性别'
) comment '用户表';

测试：主键自增

点击查看GIF动画图解open in new window👈

Idea中约束修改GIF动画教程 📹 👇 点击查看GIF动画图解open in new window👈

点击查看数据库中的数据类型
2.3.1.3 数据类型

在上面建表语句中，我们在指定字段的数据类型时，用到了int 、varchar、char，那么在MySQL中除了以上的数据类型，还有哪些常见的数据类型呢？ 接下来,我们就来详细介绍一下MySQL的数据类型。

MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型。

数值类型

类型大小有符号(SIGNED)范围无符号(UNSIGNED)范围描述
TINYINT1byte(-128，127)(0，255)小整数值
SMALLINT2bytes(-32768，32767)(0，65535)大整数值
MEDIUMINT3bytes(-8388608，8388607)(0，16777215)大整数值
INT/INTEGER4bytes(-2147483648，2147483647)(0，4294967295)大整数值
BIGINT8bytes(-263，263-1)(0，2^64-1)极大整数值
FLOAT4bytes(-3.402823466 E+38，3.402823466351 E+38)0 和 (1.175494351 E-38，3.402823466 E+38)单精度浮点数值
DOUBLE8bytes(-1.7976931348623157 E+308，1.7976931348623157 E+308)0 和 (2.2250738585072014 E-308，1.7976931348623157 E+308)双精度浮点数值
DECIMAL依赖于M(精度)和D(标度)的值依赖于M(精度)和D(标度)的值小数值(精确定点数)
示例: 
    年龄字段 ---不会出现负数, 而且人的年龄不会太大
 age tinyint unsigned
 
 分数 ---总分100分, 最多出现一位小数
 score double(4,1)

字符串类型

类型大小描述
CHAR0-255 bytes定长字符串(需要指定长度)
VARCHAR0-65535 bytes变长字符串(需要指定长度)
TINYBLOB0-255 bytes不超过255个字符的二进制数据
TINYTEXT0-255 bytes短文本字符串
BLOB0-65 535 bytes二进制形式的长文本数据
TEXT0-65 535 bytes长文本数据
MEDIUMBLOB0-16 777 215 bytes二进制形式的中等长度文本数据
MEDIUMTEXT0-16 777 215 bytes中等长度文本数据
LONGBLOB0-4 294 967 295 bytes二进制形式的极大文本数据
LONGTEXT0-4 294 967 295 bytes极大文本数据

char 与 varchar 都可以描述字符串，char是定长字符串，指定长度多长，就占用多少个字符，和字段值的长度无关 。而varchar是变长字符串，指定的长度为最大占用长度 。相对来说，char的性能会更高些。

示例： 
    用户名 username ---长度不定, 最长不会超过50
 username varchar(50)
 
 手机号 phone ---固定长度为11
 phone char(11)

日期时间类型

类型大小范围格式描述
DATE31000-01-01 至 9999-12-31YYYY-MM-DD日期值
TIME3-838:59:59 至 838:59:59HH:MM:SS时间值或持续时间
YEAR11901 至 2155YYYY年份值
DATETIME81000-01-01 00:00:00 至 9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值
TIMESTAMP41970-01-01 00:00:01 至 2038-01-19 03:14:07YYYY-MM-DD HH:MM:SS混合日期和时间值，时间戳
示例: 
 生日字段  birthday ---生日只需要年月日  
 birthday date
 
 创建时间 createtime --- 需要精确到时分秒
 createtime  datetime
2.3.1.4 案例

需求：根据产品原型/需求创建表((设计合理的数据类型、长度、约束)

参考资料中提供的《黑马-tlias智能学习辅助系统》页面原型，设计员工管理模块的表结构

暂不考虑所属部门字段

产品原型及需求如下：

image-20220829151032029
image-20220829151032029

步骤：

  1. 阅读产品原型及需求文档，看看里面涉及到哪些字段。

  2. 查看需求文档说明，确认各个字段的类型以及字段存储数据的长度限制。

  3. 在页面原型中描述的基础字段的基础上，再增加额外的基础字段。

使用SQL创建表：

create table emp (
  id int unsigned primary key auto_increment comment 'ID',
  username varchar(20) not null unique comment '用户名',
  password varchar(32) default '123456' comment '密码',
  name varchar(10) not null comment '姓名',
  gender tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
  image varchar(300) comment '图像',
  job tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管',
  entrydate date comment '入职时间',
  create_time datetime not null comment '创建时间',
  update_time datetime not null comment '修改时间'
) comment '员工表';

除了使用SQL语句创建表外，我们还可以借助于图形化界面来创建表结构，这种创建方式会更加直观、更加方便。

操作步骤如下：

  1. 在指定操作的数据库上，右键New ==> Table
image-20221205230447721
image-20221205230447721
  1. 编辑表结构的相关信息
image-20221205231846161
image-20221205231846161
2.3.1.5 设计表流程

通过上面的案例，我们明白了，设计一张表，基本的流程如下：

  1. 阅读页面原型及需求文档

  2. 基于页面原则和需求文档，确定原型字段(类型、长度限制、约束)

  3. 再增加表设计所需要的业务基础字段(id主键、插入时间、修改时间)

image-20221205232253088
image-20221205232253088

说明：

  • create_time：记录的是当前这条数据插入的时间。

  • update_time：记录当前这条数据最后更新的时间。

相关信息

如果你想了解Navicat软件如何进行表操作的，可以点击点击查看Navicat安装和使用视频open in new window

3. 数据库操作-DML ✏️ 🍐

DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增、删、改操作。

  • 添加数据（INSERT）
  • 修改数据（UPDATE）
  • 删除数据（DELETE）

3.1 增加(insert)

insert语法：

  • 向指定字段添加数据

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

  • 全部字段添加数据

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

  • 批量添加数据（指定字段）

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

  • 批量添加数据（全部字段）

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

案例1：向tb_emp表的username、name、gender字段插入数据

-- 因为设计表时create_time, update_time两个字段不能为NULL，所以也做为要插入的字段
insert into tb_emp(username, name, gender, create_time, update_time)
values ('wuji', '张无忌', 1, now(), now());

案例2：向tb_emp表的所有字段插入数据

insert into tb_emp(id, username, password, name, gender, image, job, entrydate, create_time, update_time)
values (null, 'zhirou', '123', '周芷若', 2, '1.jpg', 1, '2010-01-01', now(), now());

案例3：批量向tb_emp表的username、name、gender字段插入数据

insert into tb_emp(username, name, gender, create_time, update_time)
values ('weifuwang', '韦一笑', 1, now(), now()),
       ('fengzi', '张三疯', 1, now(), now());

图形化操作：双击tb_emp表查看数据

image-20221206002807531
image-20221206002807531

Insert操作的注意事项： 👈

  1. 插入数据时，指定的字段顺序需要与值的顺序是一一对应的。

  2. 字符串和日期型数据应该包含在引号中。

  3. 插入的数据大小，应该在字段的规定范围内。

课堂作业

🎻 1.将上述的案例按照顺序完成一遍

4. 数据库操作-DQL ✏️🍐 ❤️

4.1 介绍

DQL英文全称是Data Query Language(数据查询语言)，用来查询数据库表中的记录。

查询关键字：SELECT

查询操作是所有SQL语句当中最为常见，也是最为重要的操作。在一个正常的业务系统中，查询操作的使用频次是要远高于增删改操作的。当我们打开某个网站或APP所看到的展示信息，都是通过从数据库中查询得到的，而在这个查询过程中，还会涉及到条件、排序、分页等操作。

image-20220611103943417
image-20220611103943417

语法

DQL查询语句，语法结构如下：

SELECT
 字段列表
FROM
 表名列表
WHERE
 条件列表
GROUP  BY
 分组字段列表
HAVING
 分组后条件列表
ORDER BY
 排序字段列表
LIMIT
 分页参数

分为以下几个部分学习：

  • 基本查询（不带任何条件）
  • 条件查询（where）
  • 分组查询（group by）
  • 排序查询（order by）
  • 分页查询（limit）
点击查看测试数据
CREATE DATABASE db86;-- 创建数据库
USE db86;-- 切换数据库

-- 创建表格
CREATE TABLE emp (
	id INT UNSIGNED PRIMARY KEY auto_increment COMMENT 'ID,主键',
	username VARCHAR ( 20 ) NOT NULL UNIQUE COMMENT '用户名',
	PASSWORD VARCHAR ( 32 ) NOT NULL COMMENT '密码',
	NAME VARCHAR ( 10 ) NOT NULL COMMENT '姓名',
	gender TINYINT UNSIGNED NOT NULL COMMENT '性别, 1:男, 2:女',
	phone CHAR ( 11 ) NOT NULL UNIQUE COMMENT '手机号',
	job TINYINT UNSIGNED COMMENT '职位, 1:班主任,2:讲师,3:学工主管,4:教研主管,5:咨询师',
	salary INT UNSIGNED COMMENT '薪资',
	image VARCHAR ( 300 ) COMMENT '头像',
	entry_date date COMMENT '入职日期',
	create_time datetime COMMENT '创建时间',
	update_time datetime COMMENT '修改时间' 
) COMMENT '员工表';


-- 准备测试数据
INSERT INTO emp ( id, username, PASSWORD, NAME, gender, phone, job, salary, image, entry_date, create_time, update_time )
VALUES
	( 1, 'shinaian', '123456', '施耐庵', 1, '13309090001', 4, 15000, '1.jpg', '2000-01-01', '2023-10-27 16:35:33', '2023-10-27 16:35:35' ),
	( 2, 'songjiang', '123456', '宋江', 1, '13309090002', 2, 8600, '2.jpg', '2015-01-01', '2023-10-27 16:35:33', '2023-10-27 16:35:37' ),
	( 3, 'lujunyi', '123456', '卢俊义', 1, '13309090003', 2, 8900, '3.jpg', '2008-05-01', '2023-10-27 16:35:33', '2023-10-27 16:35:39' ),
	( 4, 'wuyong', '123456', '吴用', 1, '13309090004', 2, 9200, '4.jpg', '2007-01-01', '2023-10-27 16:35:33', '2023-10-27 16:35:41' ),
	( 5, 'gongsunsheng', '123456', '公孙胜', 1, '13309090005', 2, 9500, '5.jpg', '2012-12-05', '2023-10-27 16:35:33', '2023-10-27 16:35:43' ),
	( 6, 'huosanniang', '123456', '扈三娘', 2, '13309090006', 3, 6500, '6.jpg', '2013-09-05', '2023-10-27 16:35:33', '2023-10-27 16:35:45' ),
	( 7, 'chaijin', '123456', '柴进', 1, '13309090007', 1, 4700, '7.jpg', '2005-08-01', '2023-10-27 16:35:33', '2023-10-27 16:35:47' ),
	( 8, 'likui', '123456', '李逵', 1, '13309090008', 1, 4800, '8.jpg', '2014-11-09', '2023-10-27 16:35:33', '2023-10-27 16:35:49' ),
	( 9, 'wusong', '123456', '武松', 1, '13309090009', 1, 4900, '9.jpg', '2011-03-11', '2023-10-27 16:35:33', '2023-10-27 16:35:51' ),
	( 10, 'lichong', '123456', '林冲', 1, '13309090010', 1, 5000, '10.jpg', '2013-09-05', '2023-10-27 16:35:33', '2023-10-27 16:35:53' ),
	( 11, 'huyanzhuo', '123456', '呼延灼', 1, '13309090011', 2, 9700, '11.jpg', '2007-02-01', '2023-10-27 16:35:33', '2023-10-27 16:35:55' ),
	( 12, 'xiaoliguang', '123456', '小李广', 1, '13309090012', 2, 10000, '12.jpg', '2008-08-18', '2023-10-27 16:35:33', '2023-10-27 16:35:57' ),
	( 13, 'yangzhi', '123456', '杨志', 1, '13309090013', 1, 5300, '13.jpg', '2012-11-01', '2023-10-27 16:35:33', '2023-10-27 16:35:59' ),
	( 14, 'shijin', '123456', '史进', 1, '13309090014', 2, 10600, '14.jpg', '2002-08-01', '2023-10-27 16:35:33', '2023-10-27 16:36:01' ),
	( 15, 'sunerniang', '123456', '孙二娘', 2, '13309090015', 2, 10900, '15.jpg', '2011-05-01', '2023-10-27 16:35:33', '2023-10-27 16:36:03' ),
	( 16, 'luzhishen', '123456', '鲁智深', 1, '13309090016', 2, 9600, '16.jpg', '2010-01-01', '2023-10-27 16:35:33', '2023-10-27 16:36:05' ),
	( 17, 'liying', '12345678', '李应', 1, '13309090017', 1, 5800, '17.jpg', '2015-03-21', '2023-10-27 16:35:33', '2023-10-27 16:36:07' ),
	( 18, 'shiqian', '123456', '时迁', 1, '13309090018', 2, 10200, '18.jpg', '2015-01-01', '2023-10-27 16:35:33', '2023-10-27 16:36:09' ),
	( 19, 'gudasao', '123456', '顾大嫂', 2, '13309090019', 2, 10500, '19.jpg', '2008-01-01', '2023-10-27 16:35:33', '2023-10-27 16:36:11' ),
	( 20, 'ruanxiaoer', '123456', '阮小二', 1, '13309090020', 2, 10800, '20.jpg', '2018-01-01', '2023-10-27 16:35:33', '2023-10-27 16:36:13' ),
	( 21, 'ruanxiaowu', '123456', '阮小五', 1, '13309090021', 5, 5200, '21.jpg', '2015-01-01', '2023-10-27 16:35:33', '2023-10-27 16:36:15' ),
	( 22, 'ruanxiaoqi', '123456', '阮小七', 1, '13309090022', 5, 5500, '22.jpg', '2016-01-01', '2023-10-27 16:35:33', '2023-10-27 16:36:17' ),
	( 23, 'ruanji', '123456', '阮籍', 1, '13309090023', 5, 5800, '23.jpg', '2012-01-01', '2023-10-27 16:35:33', '2023-10-27 16:36:19' ),
	( 24, 'tongwei', '123456', '童威', 1, '13309090024', 5, 5000, '24.jpg', '2006-01-01', '2023-10-27 16:35:33', '2023-10-27 16:36:21' ),
	( 25, 'tongmeng', '123456', '童猛', 1, '13309090025', 5, 4800, '25.jpg', '2002-01-01', '2023-10-27 16:35:33', '2023-10-27 16:36:23' ),
	( 26, 'yanshun', '123456', '燕顺', 1, '13309090026', 5, 5400, '26.jpg', '2011-01-01', '2023-10-27 16:35:33', '2023-10-27 16:36:25' ),
	( 27, 'lijun', '123456', '李俊', 1, '13309090027', 5, 6600, '27.jpg', '2004-01-01', '2023-10-27 16:35:33', '2023-10-27 16:36:27' ),
	( 28, 'lizhong', '123456', '李忠', 1, '13309090028', 5, 5000, '28.jpg', '2007-01-01', '2023-10-27 16:35:33', '2023-10-27 16:36:29' ),
	( 29, 'songqing', '123456', '宋清', 1, '13309090029', 5, 5100, '29.jpg', '2020-01-01', '2023-10-27 16:35:33', '2023-10-27 16:36:31' ),
	( 30, 'liyun', '123456', '李云', 1, '13309090030', NULL, NULL, '30.jpg', '2020-03-01', '2023-10-27 16:35:33', '2023-10-27 16:36:31' );

4.2 基本查询(不带任何的查询条件)

在基本查询的DQL语句中，不带任何的查询条件，语法如下：

  • 查询多个字段

    select 字段1, 字段2, 字段3 from  表名;

  • 查询所有字段（通配符）

    select *  from  表名;

  • 设置别名

    select 字段1 [ as 别名1 ] , 字段2 [ as 别名2 ]  from  表名;

  • 去除重复记录

    select distinct 字段列表 from  表名;

案例1：查询指定字段 name，entrydate并返回

select name,entrydate from emp;
image-20221206112810199
image-20221206112810199

4.3 条件查询

条件查询

select  字段列表  from   表名   where   条件列表 ; -- 条件列表：意味着可以有多个条件

学习条件查询就是学习条件的构建方式，而在SQL语句当中构造条件的运算符分为两类：

  • 比较运算符
  • 逻辑运算符

常用的比较运算符如下:

比较运算符功能
>大于
>=大于等于
<小于
<=小于等于
=等于
<> 或 !=不等于
between ... and ...在某个范围之内(含最小、最大值)
in(...)在in之后的列表中的值，多选一
like 占位符模糊匹配(_匹配单个字符, %匹配任意个字符)
is null是null

常用的逻辑运算符如下:

逻辑运算符功能
and 或 &&并且 (多个条件同时成立)
or 或 ||或者 (多个条件任意一个成立)
not 或 !非 , 不是

案例1：查询 姓名 为 杨逍 的员工

select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
where name = '杨逍'; -- 字符串使用''或""包含
image-20221206121255784
image-20221206121255784

课堂作业

-- 查询id大于22
-- 查询id大于等于22 小于24
-- 查询名字不等于zhang5
-- 查询入职日期在2012-01-01到2015-01-01,且包含首位
-- 查询名字name 为张三  张四  方东白
-- 模糊查询 查询姓 张的 
-- 模糊查询 查询名字第二个字是十的数据
-- 模糊查询 查询名字包含方的
-- 查询名字name 为张三 要么 job为2 要么 入职日期是 2012-01-01
-- 查询job为null数据

4.4 聚合函数

聚合函数

聚合函数查询就是纵向查询，它是对一列的值进行计算，然后返回一个结果值。（将一列数据作为一个整体，进行纵向计算）

之前我们做的查询都是横向查询，就是根据条件一行一行的进行判断，而使用

语法：

select  聚合函数(字段列表)  from  表名 ;

注意 : 聚合函数会忽略空值，对NULL值不作为统计。

常用聚合函数：

函数功能
count统计数量
max最大值
min最小值
avg平均值
sum求和
  • count ：按照列去统计有多少行数据。
  • sum ：计算指定列的数值和，如果不是数值类型，那么计算结果为0
  • max ：计算指定列的最大值
  • min ：计算指定列的最小值
  • avg ：计算指定列的平均值
  • 在根据指定的列统计的时候，如果这一列中有null的行 ，该行不会被统计在其中。

案例1：统计该企业员工数量

# count(字段)
select count(id) from emp;-- 结果：30
select count(job) from emp;-- 结果：29 （聚合函数对NULL值不做计算）

# count(常量)
select count(0) from emp;
select count('A') from emp;

# count(*)  推荐此写法（MySQL底层进行了优化）
select count(*) from emp;

总结

  • count ：按照列去统计有多少行数据。
  • sum ：计算指定列的数值和，如果不是数值类型，那么计算结果为0
  • max ：计算指定列的最大值
  • min ：计算指定列的最小值
  • avg ：计算指定列的平均值
  • 在根据指定的列统计的时候，如果这一列中有null的行 ，该行不会被统计在其中。

课堂作业

  1. 将案例1-5进行练习
  2. 统计员工表中男性和女性的人数
  3. 计算每个职位的平均入职年限

4.5 分组查询

分组查询

分组： 按照某一列或者某几列，把相同的数据进行合并输出。

image
image

分组其实就是按列进行分类(指定列下相同的数据归为一类)，然后可以对分类完的数据进行合并计算。

分组查询通常会使用聚合函数进行计算。

语法：

select  字段列表  from  表名  [where 条件]  group by 分组字段名  [having 分组后过滤条件];

案例1：根据性别分组 , 统计男性和女性员工的数量

select gender, count(*)
from emp
group by gender; -- 按照gender字段进行分组（gender字段下相同的数据归为一组）
image-20221206172615000
image-20221206172615000

总结

where与having区别（面试题）

  • 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
  • 判断条件不同：where不能对聚合函数进行判断，而having可以。

课堂作业

  1. 计算不同年份入职员工的人数

编写一个SQL查询，计算不同年份入职的员工的人数，结果应包括两列：入职年份（entry_year）和人数（count）。

YEAR(时间) 可以返回年份

4.6 排序查询

排序查询

排序在日常开发中是非常常见的一个操作，有升序排序，也有降序排序。

语法：

select  字段列表  
from   表名   
[where  条件列表] 
[group by  分组字段 ] 
order  by  字段1  排序方式1 , 字段2  排序方式2 … ;
  • 排序方式：
    • ASC ：升序（默认值）
    • DESC：降序

案例1：根据入职时间, 对员工进行升序排序

select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from emp
order by entrydate ASC; -- 按照entrydate字段下的数据进行升序排序

select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from emp
order by  entrydate; -- 默认就是ASC（升序）
image-20221206175720337
image-20221206175720337

注意事项：如果是升序, 可以不指定排序方式ASC

总结

  • 格式：order by 字段1 排序方式1 , 字段2 排序方式2 … ;
  • 排序方式：
    • ASC ：升序（默认值）
    • DESC：降序

课堂作业

  1. 按照姓名字母顺序升序排序员工表

编写一个SQL查询，按照员工姓名（name）的字母顺序升序对员工表进行排序。

  1. 按照入职年限升序和性别降序排序员工表

编写一个SQL查询，按照员工入职年限（entrydate）升序和性别（gender）降序对员工表进行排序。

4.7 分页查询

分页查询

分页 操作在业务系统开发时，也是非常常见的一个功能，日常我们在网站中看到的各种各样的分页条，后台也都需要借助于数据库的分页操作。

image-20221206183310586
image-20221206183310586

分页查询语法：

select  字段列表  from   表名  limit  起始索引, 查询记录数 ;

案例1：从起始索引0开始查询员工数据, 每页展示5条记录

select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from emp
limit 0 , 5; -- 从索引0开始，向后取5条记录
image-20221206185257834
image-20221206185257834

总结

  • 语法格式： select 字段列表 from 表名 limit 起始索引, 查询记录数 ;
  • 注意事项:
    1. 起始索引从0开始。 计算公式 ： 起始索引 = （查询页码 - 1）* 每页显示记录数
    2. 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT
    3. 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 条数

课堂作业

  1. 为什么要分页查询？🎤
  2. 完成案例1-4

课后作业

🚩 1. 重点完成上述的课堂作业

  1. 晚自习第一节课的前30分钟，总结完毕之后，每个同学先必须梳理今日知识点 (记得写不知道的，以及感恩三件事)；整理好的笔记可以发给组长，组长交给班长，意在培养大家总结的能力）

  2. 晚自习第一节课的后30分钟开始练习（记住：程序员是代码堆起来的）：

    • 先要把今天的所有案例或者课堂练习，如果没练完的，练完他
    • 独立书写今日作业 👈

  3. 剩余的时间：预习第二天的知识，预习的时候一定要注意：

  • 预习不是学习，不要死看第二天的视频（很容易出现看了白看，为了看视频而看视频）
  • 预习看第二天的笔记，把笔记中标注重要的知识，可以找到预习视频，先看一遍，如果不懂的 ，记住做好标注。
焱哥的博客
Copyright © 2025 YangeIT