Web后端基础(MySQL)

Web后端基础(数据库)

目录

• Web后端基础(数据库)
  • 1、关系型数据库（RDBMS）
    • 数据模型
  • 2、SQL语句
    • 2.1、DDL语句
      • 数据库操作
      • 表操作
      • 表操作-其他操作
    • 2.2、DML语句
    • 2.3、DQL语句
      • 基本查询
      • 条件查询
      • 聚合函数
      • 分组查询
      • 排查查询
      • 分页查询

首先来了解一下什么是数据库。

• 数据库：英文为 DataBase，简称DB， 它是存储和管理数据的仓库。

数据是存储在数据库中的，要操作数据库以及数据库中所存放的数据会涉及到一个软件，那就是数据库管理系统。

• 数据库管理系统（DataBase Management System，简称DBMS），是操作和管理数据库的大型软件。

DBMS这个软件怎么知道要操作的是哪个数据库、哪个数据呢？是对数据做修改还是查询呢？

需要给DBMS软件发送一条指令，告诉这个软件我们要执行的是什么样的操作，要对哪个数据进行操作。而这个指令就是SQL语句。

• SQL（Structured Query Language，简称SQL）：结构化查询语言，它是操作关系型数据库的 编程语言，定义了一套操作关系型数据库的统一标准。

结论：程序员给数据库管理系统(DBMS)发送SQL语句，再由数据库管理系统操作数据库当中的数据。

1、关系型数据库（RDBMS）

概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。而所谓二维表，指的是由行和列组成的表。

二维表的优点：

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

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

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

数据模型

MySQL是关系型数据库，是基于二维表进行数据存储的，具体的结构：

• 通过MySQL客户端连接数据库管理系统DBMS，然后通过DBMS操作数据库。
• 使用MySQL客户端，向数据库管理系统发送一条SQL语句，由数据库管理系统根据SQL语句指令去操作数据库中的表结构及数据。
• 一个数据库服务器中可以创建多个数据库，一个数据库中也可以包含多张表，而一张表中又可以包含多行记录。

在Mysql数据库服务器当中存储数据，需要：

1. 先去创建数据库（可以创建多个数据库，之间是相互独立的）
2. 在数据库下再去创建数据表（一个数据库下可以创建多张表）
3. 再将数据存放在数据表中（一张表可以存储多行数据）

2、SQL语句

SQL：结构化查询语言。一门操作关系型数据库的编程语言，定义操作所有关系型数据库的统一标准。SQL语句根据其功能被分为四大类：DDL、DML、DQL、DCL 。

分类	全称	说明
DDL	Data Definition Language	数据定义语言，用来定义数据库对象(数据库，表，字段)
DML	Data Manipulation Language	数据操作语言，用来对数据库表中的数据进行增删改
DQL	Data Query Language	数据查询语言，用来查询数据库中表的记录
DCL	Data Control Language	数据控制语言，用来创建数据库用户、控制数据库的访问权限

2.1、DDL语句

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

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

数据库操作

• 查询所有数据库

show databases;

• 查询当前数据库

  我们要操作某一个数据库，必须要切换到对应的数据库中。通过指令：select database() ，就可以查询到当前所处的数据库

select database();

• 创建数据库

create database [ if not exists ] 数据库名  [default charset utf8mb4];

​ 创建数据库时，可以不指定字符集。 因为在MySQL8版本之后，默认的字符集就是 utf8mb4。

• 使用数据库

  use 数据库名 ;
  

  我们要操作某一个数据库下的表时，就需要通过该指令，切换到对应的数据库下，否则不能操作。

• 删除数据库

drop database [ if exists ] 数据库名 ;

​ 如果删除一个不存在的数据库，将会报错。

​ 可以加上参数 if exists ，如果数据库存在，再执行删除，否则不执行删除。

表操作

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

• 创建

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

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

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

• 约束

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

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

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

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

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

主键自增：auto_increment

每次插入新的行记录时，数据库自动生成id字段(主键)下的值

具有auto_increment的数据列是一个正数序列开始增长(从1开始自增)

• 数据类型

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

1). 数值类型

2). 字符串类型

3). 日期时间类型

设计表流程：

1. 阅读页面原型及需求文档
2. 基于页面原则和需求文档，确定原型字段(类型、长度限制、约束)
3. 再增加表设计所需要的业务基础字段(id、create_time、update_time)
   1. create_time：记录的是当前这条数据插入的时间。
   2. update_time：记录当前这条数据最后更新的时间。

表操作-其他操作

• 查询数据库表的具体的语法：

-- 查询当前数据库的所有表
show tables;

-- 查看指定的表结构
desc 表名 ;   -- 可以查看指定表的字段、字段的类型、是否可以为NULL、是否存在默认值等信息

-- 查询指定表的建表语句
show create table 表名 ;

• 修改数据库表结构的具体语法：

添加字段

-- 添加字段
alter table 表名 add  字段名  类型(长度)  [comment 注释]  [约束];

-- 比如： 为tb_emp表添加字段qq，字段类型为 varchar(11)
alter table tb_emp add  qq  varchar(11) comment 'QQ号码';

修改字段

-- 修改字段类型
alter table 表名 modify  字段名  新数据类型(长度);

-- 比如： 修改qq字段的字段类型，将其长度由11修改为13
alter table tb_emp modify qq varchar(13) comment 'QQ号码';
-- 修改字段名，字段类型
alter table 表名 change  旧字段名  新字段名  类型(长度)  [comment 注释]  [约束];

-- 比如： 修改qq字段名为 qq_num，字段类型varchar(13)
alter table tb_emp change qq qq_num varchar(13) comment 'QQ号码';

删除字段

-- 删除字段
alter table 表名 drop 字段名;

-- 比如： 删除tb_emp表中的qq_num字段
alter table tb_emp drop qq_num;

修改表名

-- 修改表名
rename table 表名 to  新表名;

-- 比如: 将当前的emp表的表名修改为tb_emp
rename table emp to tb_emp;

删除表结构

-- 删除表
drop  table [ if exists ]  表名;

-- 比如：如果tb_emp表存在，则删除tb_emp表
drop table if exists tb_emp;  -- 在删除表时，表中的全部数据也会被删除。

关于表结构的查看、修改、删除操作，工作中一般都是直接基于图形化界面操作。

2.2、DML语句

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

（1）添加数据（INSERT）

（2）修改数据（UPDATE）

（3）删除数据（DELETE）

• 向指定字段添加数据

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, ...);

insert操作的注意事项：

1. 插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
2. 字符串和日期型数据应该包含在引号中。
3. 插入的数据大小，应该在字段的规定范围内。

• 修改(update)

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

注意事项:

1. 修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。
2. 在修改数据时，一般需要同时修改公共字段update_time，将其修改为当前操作时间。

• 删除(delete)

delete from 表名  [where  条件] ;

注意事项:

• DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
• DELETE 语句不能删除某一个字段的值(可以使用UPDATE，将该字段值置为NULL即可)。
• 当进行删除全部数据操作时，会提示询问是否确认删除所有数据，直接点击Execute即可。

2.3、DQL语句

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

查询关键字：SELECT

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

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

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

基本查询

在基本查询的DQL语句中，不带任何的查询条件。

语法如下：

• 查询多个字段

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

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

select *  from  表名;

• 设置别名

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

• 去除重复记录

select distinct 字段列表 from  表名;

条件查询

语法：

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

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

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

常用的比较运算符如下:

常用的逻辑运算符如下:

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

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

• 案例2：查询 薪资小于等于 5000 的员工信息

select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
where salary <=5000;

• 案例3：查询 没有分配职位 的员工信息

select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
where job is null ;

注意：查询为NULL的数据时，不能使用 = null 或 ！=null 。得使用 is null 或 is not null。

• 案例4：查询 有职位 的员工信息

select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
where job is not null ;

• 案例5：查询 密码不等于 '123456' 的员工信息

-- 方式1：
select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
where password <> '123456';
-- 方式2：
select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
where password != '123456';

• 案例6：查询 入职日期 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间的员工信息

-- 方式1：
select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
where entry_date >= '2000-01-01' and entry_date <= '2010-01-01';

-- 方式2： between...and
select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
where entry_date between '2000-01-01' and '2010-01-01';

• 案例7：查询 入职时间 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间 且 性别为女 的员工信息

select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
where entry_date between '2000-01-01' and '2010-01-01';
      and gender = 2;

• 案例8：查询 职位是 2 (讲师), 3 (学工主管), 4 (教研主管) 的员工信息

-- 方式1：使用or连接多个条件
select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
where job=2 or job=3 or job=4;

-- 方式2：in关键字
select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
where job in (2,3,4);

• 案例9：查询 姓名 为两个字的员工信息

select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
where name like '__';  # 通配符 "_" 代表任意1个字符

• 案例10：查询 姓 '张' 的员工信息

select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
where name like '张%'; # 通配符 "%" 代表任意个字符（0个 ~ 多个）

• 案例11：查询 姓名中包含 '二' 的员工信息

select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
where name like '%二%'; # 通配符 "%" 代表任意个字符（0个 ~ 多个）

聚合函数

之前我们做的查询都是横向查询，就是根据条件一行一行的进行判断，而使用聚合函数查询就是纵向查询，它是对一列的值进行计算，然后返回一个结果值。（将一列数据作为一个整体，进行纵向计算）

常用聚合函数：

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

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

案例演示:

• 案例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;

• 案例2：统计该企业员工的平均薪资

select avg(salary) from  emp;

• 案例3：统计该企业员工的最低薪资

select min(salary) from emp;

• 案例4：统计该企业员工的最高薪资

select max(salary) from emp;

• 案例5：统计该企业每月要给员工发放的薪资总额(薪资之和)

select sum(salary) from emp;

分组查询

• 分组： 按照某一列或者某几列，把相同的数据进行合并输出。
  • 分组其实就是按列进行分类(指定列下相同的数据归为一类)，然后可以对分类完的数据进行合并计算。
  • 分组查询通常会使用聚合函数进行计算。

语法：

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

案例演示：

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

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

• 案例2：查询入职时间在 '2015-01-01' (包含) 以前的员工 , 并对结果根据职位分组 , 获取员工数量大于等于2的职位

select job, count(*)
from emp
where entry_date <= '2015-01-01'   -- 分组前条件
group by job                      -- 按照job字段分组
having count(*) >= 2;             -- 分组后条件

注意事项:

• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义
• 执行顺序：where > 聚合函数 > having

where与having区别（面试题）

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

排查查询

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

语法：

select  字段列表  
from   表名   
[where  条件列表] 
[group by  分组字段 ] 
order  by  字段1  排序方式1 , 字段2  排序方式2 … ;

• 排序方式：
  • ASC ：升序（默认值）
  • DESC：降序

案例演示：

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

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

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

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

• 案例2：根据入职时间，对员工进行降序排序

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

• 案例3：根据入职时间对公司的员工进行升序排序，入职时间相同，再按照更新时间进行降序排序

select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
order by entry_date ASC , update_time DESC;

注意事项：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

分页查询

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

分页查询语法：

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

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

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

• 案例2：查询 第1页 员工数据, 每页展示5条记录

select id, username, password, name, gender, phone, salary, job, image, entry_date, create_time, update_time
from emp
limit 5; -- 如果查询的是第1页数据，起始索引可以省略，直接简写为：limit 条数

• 案例3：查询 第2页 员工数据, 每页展示5条记录

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

• 案例4：查询 第3页 员工数据, 每页展示5条记录

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

注意事项:

1. 起始索引从0开始。
2. 计算公式 ：起始索引 = （查询页码 - 1）* 每页显示记录数
3. 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT
4. 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 条数

以上笔记内容来自黑马程序员