目录概述
数据库设计
- MySQL概述
- 数据库设计 -DDL
- 多表设计
数据库操作
- 数据库操作 -DQL
- 多表设计
- 事务
- 多表查询
数据库优化
- 索引
概述
- 初始化mysql
mysqld --initialize-insecure - 注册MySQL服务
mysqld -install - 启动服务
net start mysql - 停止mysql
net stop mysql
数据库密码
- 数据库登录
mysql -uroot -p回车 mysql -u用户名 -p密码 [-h数据库服务器IP地址 -p端口号]
关系型数据库：建立在关系模型基础上，有多张相互连接的二维表组成的数据库
- 使用表存储数据，格式统一，便于维护
- 使用SQL语言操作，标准统一，使用方便，可用于复杂查询
MySQL数据模型
通用语法
- SQL语句可以单行或多行书写，以分号结尾
- SQL语句可以使用空格/缩进来增强语句的可读性
- MySQL数据库的SQL语句不区分大小写
- – 单行注释
- /* */ 多行注释
SQL分类
- DDL
Data Definition Language - DML
Data Manipulation Language - DQL
Data Query Language - DCL
Data Control Language
开发流程
- 需求 + 原型
- 设计（概要设计，详细设计，接口设计，
数据库设计） - Java程序
- 数据库优化
- 设计（概要设计，详细设计，接口设计，
DDL
查询
- show databases;
- select database();
使用 - use 数据库名；
创建 - create database [if not exists] 数据库名；
删除 - drop database [if exists] 数据库名；
数据库可视化
- IDEA中就有
create table 表面（
字段1 字段类型 [约束] [comment 字段1注释],
字段2 字段类型 [约束] [comment 字段2注释],
）[comment 表注释]；
约束：作用于表中字段上的规则，用于限制存储在表中的数据
目的：保证数据库中数据的正确性，有效性和完整性
| 关键字 | 约束 | 描述 |
|---|---|---|
not null | 非空约束 | 限制该字段值不能为 null |
unique | 唯一约束 | 保证字段的所有数据都是唯一、不重复的 |
primary key (auto_increment自增) | 主键约束 | 主键是一行数据的唯一标识，要求非空且唯一 |
default | 默认约束 | 保存数据时，如果未指定该字段值，则采用默认值 |
foreign key | 外键约束 | 让两张表的数据建立连接，保证数据的一致性和完整性 |
| 数据类型分类 | 具体类型 | 说明 |
|---|---|---|
| 数值类型 | INT | 整数类型，通常占 4 字节，范围约 - 21 亿到 21 亿 |
| tinyint | ||
| smallint | ||
| mediumint | ||
| BIGINT | 大整数类型，占 8 字节，范围更大 | |
| FLOAT | 单精度浮点数，存储近似小数 | |
| DOUBLE | 双精度浮点数，精度比 FLOAT 高 | |
| DECIMAL(M,D) | 精确小数类型，M 表示总位数，D 表示小数位数，适合财务数据等精确计算 | |
| 字符串类型 | CHAR(n) | 固定长度字符串，n 为长度，不足补空格，适合短字符串（如密码、代码） |
| VARCHAR(n) | 可变长度字符串，n 为最大长度，节省空间，适合长度不固定的文本（如姓名） | |
| TEXT | 长文本类型，可存储较大文本（如文章内容） | |
| BLOB | 二进制大对象，存储图片、音频等二进制数据 | |
| 日期时间类型 | DATE | 日期类型，格式为 ‘YYYY-MM-DD’，表示年、月、日 |
| TIME | 时间类型，格式为 ‘HH:MM:SS’，表示时、分、秒 | |
| DATETIME | 日期时间类型，格式为 ‘YYYY-MM-DD HH:MM:SS’，范围较广 | |
| TIMESTAMP | 时间戳类型，格式同 DATETIME，范围较小，受时区影响 | |
| 布尔类型 | BOOLEAN | 布尔类型，通常用 1 表示 TRUE，0 表示 FALSE |
| 其他类型 | ENUM | 枚举类型，只能从预定义的列表中选择一个值（如性别：’ 男 ‘,’ 女 '） |
| SET | 集合类型，可从预定义列表中选择多个值 |
创建表 —> 去阅读页面原型
- create_time : 记录当前数据的创建时间
- update_time: 记录的是这条数据最后的创建时间
熟悉IDEA中数据库可视化的操作
这一部分都不需要重点掌握，可视化可解决
-- DDL
-- 查看当前数据库下的表
show tables;
-- 查看指定的表结构
desc tb01;
-- 查看数据库的建表语句
show create table tb01
-- 添加字段
alter table 表名 add 字段名 类型（长度） [comment 注释][约束]
-- 修改字段类型
alter table 表名 modify 字段名 新数据类型（长度）;
-- 修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 类型（长度） [comment 注释] [约束]；
-- 删除字段
alter table 表名 drop column 字段名；
-- 修改表名
rename table 表名 to 新表名
-- 删除表
drop table [if exists] 表名；
注：在删除表结构的时候，里面的数据也都会被删除
DML
- 添加数据（INSERT）
- 修改数据（UPDATE）
- 删除数据（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...)；
- 插入数据时，指定的字段顺序需要与值的顺序一一对应的
- 字符串和日期型数据应该包含在引号中
- 插入的数据大小，应该在字段的规定范围内
-- 修改数据
update 表名 set 字段名1 = 值1， 字段名2 = 值2 ... [where 条件]
- 修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据
-- 删除数据
delete from 表名 [where 条件];
- DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据
- DELETE语句不能删除某一个字段的值（如果要操作，可以使用UPDATE，将该字段的值置为NULL）
DQL
基本查询
-- 查询多个字段
select 字段1，字段2，字段3 from 表名；
-- 查询所有字段（通配符）
-- 不推荐（不直观，性能低）
select * from 表名；
-- 设置别名
select 字段1 [as 别名1]， 字段2[as 别名2] from 表名;
-- 去除重复记录
select distinct 字段列表 from 表名；
- *号代表查询所有字段，在实际开发中尽量少用（不直观，影响效率）
条件查询
select 字段列表 from 表名 where 条件列表
| 比较运算符 | 功能说明 |
|---|---|
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
= | 等于 |
<> 或 != | 不等于 |
between ... and ... | 在某个范围之内（含最小、最大值） |
in(...) | 在 in 之后的列表中的值，多选一 |
like 占位符 | 模糊匹配（_ 匹配单个字符，% 匹配任意个字符） |
is null | 判断是否为 null 值 |
| 逻辑运算符 | 功能 |
|---|---|
and 或 && | 并且（多个条件同时成立） |
or 或 ` | 或者（多个条件任意一个成立） |
not 或 ! | 非 ，不是 |
聚合函数
- count 统计数量
- count(字段) —> 不对null 进行运算
- count(常量)
- count(*) —> 推荐写法
- max 最大值
- min 最小值
- avg 平均值
- sum 求和
select 聚合函数 from 表
分组查询
- 这里的查询有要求：查询字段 + 聚合函数
- 分组之后的过滤写在 having 后面
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件]
-
where 与 having 区别
- 执行时机不同：where是分组之前进行过滤,不满足where条件，不参与分组 ; 而having 是分组之后对结果进行过滤
- 判断条件不同：where不能对聚合函数进行判断，而having可以
-
注意事项
- 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无意义
- 执行顺序： where > 聚合函数 > having
排序查询
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件] order by 字段1 排序方式1， 字段2 排序方式2
排序方式： ASC（默认），DESC（降序）
注：弱国是多字段排序，当第一个字段值相同时，才会更具第二个字段进行排序
分页查询
select 字段列表 from 表名 limit 起始索引,查询记录数；
- 注意事项
- 起始索引 = （页码 - 1 ）* 每页记录数；
- 分页查询为数据库方言，不同的数据库有不同的实现，MySQL 中是 limit
- 如果查询的是第一页数据，起始索引可以省略，直接简写为limit xxx
多表设计
- 一对多
- 一对一
- 多对多
一对多：把多的一方称为子表，另外一方称为父表
实现：在数据库中多的一方添加一个外键，来关联一的一方的主键
- 物理外键
- 概念：使用 foreign key 定义外键关联另外一张表
- 缺点：仅用单结点数据库，不使用于分布式，集群场景；容易英法数据库的死锁问题，消耗性能
- 逻辑外键
- 在业务逻辑中，解决外键关联
- 通过逻辑外键，解决物理外键的问题
一对一：
- 案例：用户 与 身份证信息 的关系
- 关系：多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另外一张表中，以提升操作效率
- 实现：在任意一方添加外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）
多对多
- 案例：学生 与 课程的关系
- 关系：一个学生可以选修多门课程，一门课程可以供多个学生选择
- 实现：建立第三张中间表，中间至少包含两个外键，分别关联两方主键
![[Pasted image 20250803210722.png]]
多表查询
笛卡尔积：两个集合的所有组合情况
-- 这样查询会有笛卡尔积
select * from tb01,tb02;
-- 消除笛卡尔积
select * from tb01,tb02 where tb01.id = tb02.tem_id;
- 连接查询
- 内连接：相当于查询A，B交集部分数据
- 外连接
- 左外连接：查询左表所有数据（包括两张表交集部分数据）
- 右外连接：查询右表所有数据（包括两种表交集部分数据）
- 子查询
内连接
--隐式内连接
select 字段列表 from 表1，表2 where 条件...;
--显示内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件..;
注：这两个是等价的，不如写前面（好记）
外连接
-- 左外连接
select 字段列表 from 表1 left[outer] join 表2 on 连接条件...;
-- 右外连接
select 字段列表 from 表1 right[outer] join 表2 on 连接条件...;
注：个人理解，左外连接会完全包含左表的信息，即使坐标中数据不满足连接条件
子查询
-
介绍：SQL语句中嵌套select语句，称为嵌套查询，又称为子查询
-
形式：
select * from t1 where column1 = (select column1 from t2 ...) -
子查询外部的语句可以是 insert/update/delete/select 的任何一种，最常见的是select
-
标量子查询：子查询返回的结果为单个值
-
列子查询：子查询返回的结果为一列
-
行子查询：子查询返回的结果为一行
-
表子查询：子查询返回的结果为多行多列
标量子查询
- 子查询返回的结果是单个值（数字，字符串，日期），最简单的形式
- 常用的操作符： = <> > >= < <=
例： 查询张三入职之后的员工信息
- 查找张三的入职时间
- 查找需要恶的员工信息
标量子查询
- 子查询返回的结果是一列（可以是多行）
- 常用的操作符 : in , not in
行子查询
- 子查询返回的结果是一行（可以是多列）
- 常用的操作符： = <> in not in
select * from tb_emp where (entrydate,job)=('2007-01-01',2);
表子查询
- 将select 语句的返回值当作一张临时表使用
事务
概念：事务是一组操作的集合，他是一个不可分割的工作单位，事务会把所有的操作作为一个整个一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败
注：默认MySQL的事务是自动提交的，也就是说，当执行一体哦啊MDL语句，MySQL会立即隐式的提交事务
- 开启事务
start transaction; / begin; - 提交事务
commit; - 回滚事务
rollback
四大特性
- 原子性
- 事务是不可分割的最小单元，要么全部成功，要么全部失败
- 一致性
- 事务完成时，必须使所有的数据都保持一致状态
- 隔离性
- 数据库系统提供的隔离机制，保证事务在不受外部并发影响的独立环境下运行
- 持久性
- 事务一旦提交或回滚，它对数据库中的数据的改变就是永久的
索引
概念：索引时数据库高效获取数据的数据结构
优点：
- 提高数据查询的效率，降低数据库的IO成本
- 通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗
缺点：
- 索引会暂用存储空间
- 缩影大大提高了查询效率，同时却也降低了insert,update,delete 的效率
结构
MySQL数据库支持的索引结构很多，如： Hash,B+Tree,Full_Text;如果没有他把指明，都是默认为B+Tree结构组织的索引
- 每一个结点可以存储多个key（有n个key，就有n个指针）
- 所有的数据都存储在叶子结点，非叶子结点仅用于索引数据
- 叶子结点形成了一颗双向链表，便于数据的排序及区间范围查询
语法
-- 创建索引
create [unique] index 索引名 on 表名(字段名,..);
-- 查看索引
show index from 表名；
-- 删除索引
drop index 索引名 on 表名;
注意事项：
- 主键字段，在建表时，会自动创建主键索引
- 田间唯一约束时，数据库实际上会添加唯一索引
扫一扫
32万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
