Java Web DAY6-DAY8（黑马）

2024/10/13

·数据库：DataBase（DB），是存储和管理数据的仓库

·数据库管理系统：DataBase Management System （DBMS），操纵和管理数据库的大型软件

·SQL：Structured Query Language，操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准

·数据库产品：

——排名：

产品生产需求：

一、数据库设计

（一）MySQL概述

1.安装、配置

参照B站视频如下：

【2024最新最详细的mysql安装教程（附安装包和详细文档）】 https://www.bilibili.com/video/BV1KrvxeeEZZ/?share_source=copy_web&vd_source=64c0b3dd5ba86d2977b5cad6d27aa5af

打开后，首先连接MySQL：

第一次创建，根据自动提示需要下载jar包，以下是测试连接成功：

2.数据模型

mysql数据模型，即如何存储数据；我们的计算机就是作为了一台MySQL服务器，通过客户端连接上数据库的

——关系型数据库（RDBMS）：建立在关系模型基础上，由多张相互连接到二维表组成的数据库

特点：

·使用表存储数据，格式统一，便于维护

·使用SQL语言操作，标准统一，使用方便，可用于复杂查询

——数据模型

数据库服务器内置了一个软件DBMS，是数据库管理系统，用来操作数据库中的数据

3.SQL简介

——SQL：一门操作关系型数据库的编程语言，定义操作所有关系型数据库的统一标准

——通用语法：

·SQL语句可以单行或多行书写

·SQL语句可以使用空格/缩进来增强语句的可读性

·MySQL数据库的SQL语句不区分大小写

·注释：

        ·单行注释：-- 注释内容（有个空格）；#注释内容（MySQL特有）

        ·多行注释：/*注释内容*/

——SQL分类

SQL语句通常被分为四大类：

·DDL（Data Definition Language）：数据定义语言。定义数据库对象（数据库、表、字段）

·DML（Data Manipulation Language）：数据操作语言。对数据库表中的数据进行增删改

·DQL（Data Query Language）：数据查询语言。查询数据库表中表的记录

·DCL（Data Control Language）：数据控制语言。创建数据库用户，控制数据库的访问权限

（二）数据库设计——DDL

DDL（Data Definition Language）：数据定义语言。定义数据库对象（数据库、表、字段）

1.数据库

——数据库操作

（1）sql语言书写

（1）查询

·查询所有数据库：

show databases;

（2）创建

·创建数据库：

create databases[if not exists] 数据库名;

或使用图形化界面：

（3）使用

·使用数据库：

use 数据库名;

或使用图形化工具：

（4）删除

·删除数据库：

drop databases[if exists] 数据库名;

注：

上述语法的database，也可以替换成schema。如：

create schema 数据库名;

模式（schema）是数据库中全体数据的逻辑结构和特征的描述

（2）MySQL客户端工具——图形化工具

本文使用IDEA内置的dataGrip，具体使用操作指路：

datagrip与IDEA集成的datagrip的使用_idea集成datagrip-CSDN博客

*切换数据库（右上角）

*删除数据库

*返回上一次编辑处

2.表

（1）创建

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

*约束

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

·目的：保证数据库中数据的正确性、有效性和完整性

实例：

查看如下：

其中：

id部分为了自动添加序号（1，2，3，，），添加

auto_increment

（2）查询

·查询当前数据库所有表：

show tables;

·查询表结构：

desc 表名;

·查询建表语句：

show create table 表名;

（3）修改

图形化：

·添加字段：

alter table 表名 add 字段名 类型(长度) [comment 注释];

·修改字段类型：

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

·修改字段名和字段类型：

alter table 表名 change 旧字段名 新字段名 类型(长度);

·删除字段：

alter table 表名 drop column 字段名;

·修改表名：

rename table 表名 to 新表名;

（4）删除

·删除表：

drop table[if exists]表名;

*在删除表时，表中的全部数据也会被删除

（5）总结

（三）DDL(表操作)

1.数据类型

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

数值类型：

字符串类型：

日期类型：

2.案例

根据页面原型 / 需求创建表（设计合理的数据类型、长度、约束）

（1）创建

实例如下一步步创建：

输入name，Type，Comment（注释），勾选小框内限制

例如其中一步，性别设置 男女：

记得加上create_time和update_time

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

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

二、数据库操作

（一）数据库操作——DML

DML（Data Manipulation Language）：数据操作语言。对数据库表中的数据进行增、删、改

·添加数据（INSERT）

·修改数据（UPDATE）

·删除数据（DELETE）

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

实例：

*注：

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

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

·插入的数据大小，应该在字段的规定范围以内

2.update语法

为实现以下功能：

·修改数据：

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

*注：

·修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据

如下实例：

3.delete语法

为实现以下功能：

·删除数据：

delete from 表名 [where 条件];

实例：

*注：

·DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表

·DELETE语句不能删除某一个字段的值（如果要操作，可以使用UPDATE，将该字段的值置为NULL）

（二）数据库操作——DQL

·DQL（Data Query Language）：数据查询语言。查询数据库表中表的记录

·关键字：SELECT

1.语法

总览：

（1）基本查询

·查询多个字段：

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

·查询所有字段（通配符）：

*注：但不直观，最好还是第一种方法

select * from 表名;

·设置别名：

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

其中，as可以省掉，若别名中出现空格，应用单引号将别名引起来

·去除重复记录：

select distinct 字段列表 from 表名;

小结：

（2）条件查询

1.语法

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

实例：

（3）分组查询

1.聚合函数

（需求对某一字段纵进行计算）

·介绍：将一列数据作为一个整体，进行纵向计算

·语法：

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

·常见聚合函数

2.对于count的三种方式：

·推荐使用count(*)，星号（*）代表表中的所有列，因此它包括了所有行，即使某些列的值缺失。

·count(列名)表示的是 查询符合条件的列的值 不为NULL的行数

·count(*)不用扫描字段，是直接根据索引扫描表的，而统计字段时反而需要扫描整张表，所以count(*)更快

3.其他

4.注意

·null值不参与所有聚合函数运算

2.分组查询

语法：

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

实例：

·注：

·分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义

·执行顺序：where > 聚合函数>having

·where与having区别：

--判断条件：where不能使用聚合函数，having才行

--执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤

--where是筛选行级数据(一维)的，having是筛行级数据的组合体(二维)的，而聚合函数是表或者多个数据才有意义的，所以where不能接聚合函数，而having可以

（4）排序查询

1.语法

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

2.排序方式

·ASC（ascend）：升序（默认值）

·DESC（descend）：降序

·实例：

*注：

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

（5）分页查询

1.语法

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

起始索引 = （页码 - 1） *  每页展示记录数      （起始索引从0开始）

·注：

·分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT

·如果查询的是第一页数据，起始索引可以省略，直接简写为limit10

（6）案例

指路DAY7——P98

（三）多表设计

·概述：

项目开发中，在进行数据库结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为3种：

1.一对多

·需求：根据页面原型 及 需求文档，完成部门（父表） 与 员工（子表）模块的表结构设计

一对多关系实现：

在数据库表中多的一方，添加字段，来关联一的一方的主键

·多表问题分析：

（1）现象

·部门数据可以之间删除，然而还有部分员工归属于该部门下----此时出现了数据的不完整、不一致问题

（2）问题分析

·目前上述的两张表，在数据库层面，并未建立关联，所以无法保证数据的一致性和完整性，即多个表之间同个数据的一致性还有表与表之间的完整性

·仅仅在逻辑层面，在员工表添加了字段来指定当前员工归属ID（部门），在数据库层面没有，现在的员工部门的ID是我们自己插入的

*外键约束

**--解决多表之间的一致性和完整性（但是不推荐：数据迁移会很麻烦）

        阿里巴巴的《阿里巴巴Java开发手册》中有关于外键使用的明确指导原则。根据手册中的规定，强制要求不得使用外键与级联，一切外键概念必须在应用层解决。java开发手册禁止使用外键，外键概念需要在service处理。

        实际开发中用的不多，维护太麻烦

--但是：

        工作中这种情况是必须要用外键约束的,防止数据误删除,增加了安全性,并且子表添加数据的时候也得到了约束,提高了数据录入准确性

        企业开发时一般会给表添加一个删除字段，用来标记这条数据是不是被删除了，删除的数据标记为1，未删除标记为0，标记为1的数据只是逻辑意义上的删除，而不是物理意义上的删除

1.语法

第一种：创建表时指定

create table 表名(
        字段名 数据类型,
        ...
        [constraint] [外键名称] foreign key(外键字段名) references 主表(字段名)
);

第二种：建完表后，添加外键

alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(字段名);

第三种使用图形化工具：

子表--Modify Table--Forfeign Keys--+号--+号

变化：

2.外键约束的声明

（1）物理外键

·概念：使用foreign key 定义外键关联另外一张表

·缺点：

        ·影响增、删、改、查的效率（需要检查外键关系）

        ·仅用于单节点数据库，不适用于分布式、集群场景

        ·容易引发数据库的死锁问题，消耗性能

因此选择：

（2）逻辑外键

·概念：在业务逻辑中，解决外键关联

·通过逻辑外键，就可以方便的解决上述问题

通过代码来实现

2.一对一

·案例：用户 与 身份证信息 的关系

·关系：一对一，多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率

（该思想可以关联到  范式，或者像Hbase的主族和列）

·实现：

在任意一方加入外键，关联另一方的主键，并且设置外键为唯一的（UNIQUE）

用户基本信息表（tb_user）：

用户身份信息表（tb_user_card）：

*一对多不推荐物理外键，但是一对一可以

*逻辑外键就是在建A表的时候sql语句中没有使用foreignkey关键字来关联B表里的主键，但是A表中是存在外键那一列的。这样就可以在sql的条件语句中使用外键的逻辑，但是系统又没有记录外键关联，这样删除某一列的时候就不会报错

3.多对多

·案例：学生 与 课程的关系

·关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择

实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。中间表罗列所有对应关系

实例：

查看关系图：

4.案例

参考页面原型及 需求，设计合理的表结构

（1）关系分析

·需求：

参考《苍穹外卖_管理平台》页面原型，设计分类管理、菜品管理、套餐管理模块的表结构

·步骤：

·阅读页面原型 及需求文档，分析各个模块涉及到的表结构，及表结构之间的关系

·根据页面原型 及需求文档，分析各个表结构中具体的字段及约束

分析（表结构 以及 表结构之间的关系）如图：

分析涉及到的字段、字段的类型、字段应该建立怎么样的约束，再完成表结构的创建：

*数据库设计推荐用powerdesigner或者rose软件设计

（2）表结构

1.分类表的创建：category

2.菜品表的创建：dish

此时，菜系表中的一个字段category_id关联分类表的主键，这里使用的是逻辑外键，没有使用物理外键：

*逻辑外键就是我们自己知道这两个表之间有关联（用工具创建就是默认逻辑外键），但是数据库不知道

逻辑外键体现在：编写增删改查方法时，记得将有关联的表一起改

3.套餐表的创建：setmeal

此时，套餐表中的外键字段category_id关联的是分类表的主键id，目前我们采用的是逻辑外键：

但是套餐与菜品的关系还没有体现出来，套餐与菜品的关系是多对多的关系，于是

4.套餐菜品关系表：setmeal_dish

（四）多表查询

1.数据准备

多表查询的测试数据的导入

2.多表查询

·介绍：指从多张表中查询数据

·笛卡儿积：笛卡尔乘积指在数学中，两个集合的左右组合情况（而在多表查询时，需要消除无效的笛卡儿积）

如图：

转移到：

语法书写实例：

3.分类

（1）连接查询

1.内连接

查询A、B交集部分数据

·隐式内连接：

select 字段列表 from 表1, 表2 where 条件...;

·显示内连接：

select 字段列表 from 表1 [name] join 表2 on 连接条件...;

总实例：

*为表起别名为e：

2.外连接

*外连接一定显示整表，内连接无联系数据不显示

·左外连接：查询左表所有数据（包括两张表交集部分数据）

select 字段列表 from 表1 left [outer] join 表2 on 连接条件...;

·右外连接：查询右表所有数据（包括两张表交集部分数据）

select 字段列表 from 表1 right [outer] join 表2 on 连接条件...;

总实例：

*基本上使用的左外连接（优化较好）

（2）子查询

1.概述

查询中又嵌套了查询

·介绍：SQL语句嵌套select语句，称为嵌套查询，又称子查询

·形式：select * from t1 where column1 = (select column1 from t2 ...);

·子查询外部的语句可以是insert/update/delete/select 的任意一个，最常见的是select

2.分类

（1）标量子查询：子查询返回的结果为单个值

·数字、字符串、日期等，最简单的形式

·常用操作符：=  <>  >  >=  <=  <

实例1：

实例2：

（2）列子查询：子查询返回的结果为一列（可以是多行）

·常用的操作符：in、not in 等

实例：

改变前：

改变后：

*not in要注意null

（3）行字查询：子查询返回的结果为一行（但可以时多列）

·常用的操作符：= 、 <>、in、not in

实例：

改变前：

改变后：

或改写为：

（4）表子查询：子查询返回的结果为多行多列，常作为临时表

·常用的操作符：in

实例：

（3）案例

需求：

1.准备测试数据

·setmeal_dish：套餐-菜品中间表

setmeal_id关联的是setmeal的主键，dish_id关联的是dish的主键（都是使用的逻辑外键，没有使用物理外键）

1.需求1

ctrl + alt + L 对写的一串SQL代码格式化

如下：

2.需求2

即：

3.需求3

涉及分组聚合

4.需求4

涉及分组聚合

5.需求5

6.需求6

（五）事务

·场景：学工部 整个部门解散了，该部门及部门下的员工都需要删除了

·操作：

·问题：如果删除部门成功，而删除该部门的员工失败了，就造成了数据的不一致

1.介绍&操作

（1）介绍

事务 是一组操作的集合，它是一个不可分割的单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作 要么同时成功，要么同时失败

*因此，在此操作中，应作为一个整体进行，然而是一个成功一个失败

原因：默认MySQL的事务是自动提交的，即，当执行一条DML语句，MySQL会立即隐式的提交事务，也就是这里的代码被当作两个事务，因此...

（2）操作

1.事务控制

·开启事务：start  transaction; / begin;

·提交事务：commit;

·回滚事务：rollback;（当操作失败，恢复到操作前的数据）

实例：

这就是事务，一组数据要么同时成功，要么同时失败

·事物启动就相当于在当前开启一个副本或者说快照，后续操作在副本上执行，如果在副本操作成功了就提交，失败了就放弃修改也就是rollback；这些数据只是放在事务内存内存的缓冲区内,只有事务提交之后才会存入磁盘中；相当于是源数据变成了备份数据，如果提交，就会把备份数据覆盖，如果操作失败，就会回滚到备份数据

2.四大特性（ACID）

1.原子性Atomicity

事务是不可分割的最小单位，要么全部成功，要么全部失败

2.一致性Consistency

事务完成时，必须使所有的数据都保持一致状态（例如部门删除，响应部门员工也要一起删除）

3.隔离性Isolation

数据库系统提供的隔离机制（只要不提交（commit），别的窗口就看不到所操作的数据），保证事务在不受外部并发操作影响的独立环境下运行，可以通过隔离级别来设置的，越高越安全，但效率越低

4.持久性Durability

事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

三、数据库优化

（一）索引

1.介绍

（1）概念：索引（index） 是帮助数据库高效获取数据 的 数据结构

使用二叉搜索树（排序树）

select * from user where age = 45

（以下不是MySQL的底层索引结构，只是示例）

索引创建好了，即数据结构被创建好了，对数据结构进行查询效率提高

（2）优缺点

——优点：

·提高数据查询的效率，降低数据库的IO成本

·通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗

——缺点：

·索引会占用存储空间

·索引大大提高了查询效率，同时也降低了insert、update、delete的效率

2.结构

MySQL数据库支持的索引结构有很多，如：Hash索引、B+Tree索引、Full-Text索引等。我们平时所说的索引，如果没有特别指明，都是默认的B+Tree结构组织的索引

·B+Tree（多路平衡搜索树）

——特点：

·每一个节点中，存储了n个key，就会有n个指针（P1,P2,P3），也就会有n个子节点，三个指针分别指向下一个磁盘块

·一个页/磁盘块的大小为16kb

·黄虚线框中被称为非叶子节点，下面的这层节点被称为叶子节点，非叶子节点仅仅起到查询数据，索引数据的作用，并不保存具体的数据，而保存在叶子节点中，并且所有的k也会出现在叶子节点中

·叶子节点元素是按照从小到大的顺序排序的，在叶子节点形成了一个双向列表，由上一个元素可以找到下一个元素，反之也可，便于数据的排序及区间范围查询

*二分查找只能对数组，而数组在内存中存必须连续一整片空间，树是分页存储，可以离散地存在内存中。

3.语法

·创建索引：

create [unique] index 索引名 on 表名(字段名,...);

·查看索引：

show index from 表名;

*第一个索引，PRIMARY，对应的字段是id，因为id是主键（一旦我们指定了id是某一张表的主键，他就会自动创建一个索引，成为主键索引，并且主键索引的性能是最高的，数据库默认创建出来的主键索引）

*第二个索引，username，我们在emp表中的username字段上建立了一个约束，Unique，一旦我们指定了这个字段是唯一的，他就会自动给这个字段创建一个唯一索引（也就是[unique] index），如下：

在这里可以查看到该索引：

*第三个索引：就是自己创建的那个

·删除索引：

drop index 索引名 on 表名;

总实例：

（二）SQL优化

省

（三）分库分表

省