数据库操作-DQL
DQL英文全称是Data Query Language(数据查询语言),用来查询数据库表中的记录
- 基本查询
- 条件查询where
- 分组查询group by
- 排序查询order by
- 分页查询limit
基本查询
语法
查询多个字段
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或! 非,不是
分组查询
聚合函数
- 将一列数作为一个整体,进行纵向计算
- 语法:select 聚合函数(字段列表) from 表名;
- 函数
count---统计数量
max---最大值
min---最小值
avg---平均值
sum---求和
- 注意事项
null值不参与所有聚合函数运算
统计数量可以使用:count(*) count(字段) count(常量),推荐使用count(*)
分组查询group by
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];
where与having区别
- 执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤
- 判断条件不同:where不能对聚合函数进行判断,而having可以
注意事项
- 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义
- 执行顺序:where>聚合函数>having
排序查询
语法
select 字段列表 from 表名 [where 条件列表] [group by 分组字段] order by 字段1 排序1, 字段2 排序2 ... ;
排序方式
- ASC:升序(默认值)
- DESC:降序
注意事项
如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序
分页查询
语法
select 字段列表 from 表名 limit 起始索引,查询记录数
注意事项
-
起始索引从0开始,起始索引=(查询页码-1)*每页显示记录数
-
分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT
-
如果查询的第一页数据,起始索引可以省略,直接简写为limit 10
函数:
if(表达式,true value,false value):当表达式为true时,取值true value;当表达式为false时,取值false value
case 表达式 when value1 then result1 [ when value2 then value2 ...] [else result] end
多表设计
概述
项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：
- 一对多
- 多对多
- 一对一
外键
create table 表名(
字段名 数据类型
...
[constraint] [外键名称] foreign key (外键字段名) references 主表 (字段名)
);
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(字段名);
外键约束
物理外键
使用foreign key定义外键关联另外一张表
缺点
- 影响增,删,改的效率(需要检查外键的关系)
- 仅用于单节点数据库,不适用与分布式,集群场景
- 容易引发数据库的死锁问题,消耗性能
逻辑外键
- 在业务层逻辑中,解决外键关联
- 通过逻辑外键,就可以很方便的解决上述问题(推荐)
-
一对多
在多的一方添加字段,指向一的一方的主键 -
一对一
多用于单表拆分,将一张表的基础字段放在一张表中,其他字段放在另一张表中,以提升操作效率 -
多对多
通过中间表来维护,中间表的两个外键,分别关联另两张表的主键
多表查询
概述
- 指从多张表中查询数据
- 笛卡尔积:指在数学中,两个集合的所有组合情况(在多表查询时,需要消除无效的笛卡尔积)
分类
连接查询
内连接
查询两个表的交集部数据,也就是能通过主外键关联的数据
外连接
左外连接
查询左表的所有数据和另一张表的交集
右外连接
查询右表的所有数据和另一张表的交集
子查询
SQL语句中嵌套select语句,称为嵌套查询,又称子查询
内连接
语法
隐式内连接
select 字段列表 from 表1,表2 where 条件 ... ;
显式内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件 ... ;
查询结果特点:内连接相当于查询A,B交集数据
外连接
语法
select 字段列表 from 表1 left [outer] join 表2 on 连接条件 ... ;
select 字段列表 from 表1 right [outer] join 表2 on 连接条件 ... ;
查询结果特点:查询一个表的全部数据和另一个表的交集数据
子查询
概述
select * from t1 where column1 = (select column1 from t2 ... );
子查询外部的语句可以是insert / update / delete / select 的任何一个,最常见的是select
分类
标量子查询
- 子查询返回的结果为单行单列
- 作为where条件使用,常用的操作符:= <> > >= < <=
- select 字段列表 from 表 where 字段名 = (子查询);
列子查询
- 子查询返回的结果为多行单列
- 作为where条件使用,常用操作符:in, not in等
表子查询
- 子查询返回的结果为单行/多行多列
- 常作为临时表,临时表必须取别名
- select 字段列表 from 表1,(子查询) 表2 where 条件;
事务
一种机制,指的是把一组SQL操作看成一个整体发送给系统执行.都执行成功就提交,有一个执行失败就回滚,从而保证这组操作要么同时成功,要么同时失败
事务控制
- 开启事务:start transaction;/begin;
- 提交事务:commit;
- 回滚事务:rollback;
注意事项
默认MySQL的事务是自动提交的,也就是说,当执行一条DML语句,MySQL会立即隐式的提交事务
四大特性(ACID)
-
原子性Atomicity
事务是不可分割的最小单元,要么全部成功,要么全部失败 -
一致性Consistency
事务完成时,必须使所有的数据都保持一致状态 -
隔离性Isolation
数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行 -
持久性Durability
事务一旦提交或回滚,它对数据库中的数据的改变就是永久的
索引
概念
索引时帮助数据库高效获取数据的数据结构
优点
- 提高数据查询的效率,降低数据库的IO成本
- 通过索引列对数据进行排序,降低数据排序的成本,降低CPU消耗
缺点
- 索引会占用存储空间
- 索引大大提高了查询效率,同时却也降低了insert,update,delete的效率
结构
MySQL数据库支持的索引结构有很多,如:Hash索引,B+Tree索引,Full-Text索引等
二叉树存在的问题
大数据量情况下,层级深,检索速度慢
B+Tree(多路平衡搜索树)
- Btree是数据库中最常见最为频繁的一种索引结构
- Btree中的B代表平衡(balance),而不是二叉(binary)
- Btree是从最早的二叉查找树,平衡二叉树(红黑树)和平衡多路查找树(Btree)这些树逐步优化而来
- 每一个节点,可以存储多个key(有n个key,就有n+1个指针)
- 所有的数据都存储在叶子节点,非叶子节点仅用于索引数据
- 叶子节点中每一页通过双向链表互相连接,便于数据的排序及区间范围查询
语法
创建索引
create [unique] index 索引名 on 表名 (字段名, ...);
查看索引
show index from 表名;
删除索引
drop index 索引名 on 表名;
注意事项
- 主键字段,在建表时,会自动创建主键索引
- 添加唯一约束时,数据库实际上会添加唯一索引
MyBatis
什么是Mybatis
MyBatis是一款优秀的持久层框架，用于简化JDBC（Java数据库连接）的开发。
Mybatis入门
快速入门
步骤
- 准备工作,创建数据库和表
- 创建springboot工程,引入Mybatis的相关依赖,配置Mybatis(数据库连接信息)
- 实体类User,编写Mapper,SQL语句(注解/XML)
- 单元测试
配置SQL提示
产生原因
IDEA和数据库没有建立连接,不识别表信息
解决方式
在IDEA中配置MySQL数据库连接
JDBC介绍
介绍
Java DataBase Connectivity,就是使用Java语言操作关系型数据库的一套API接口
本质
- sun公司官方定义的一套操作所有关系型数据库的规范,即接口
- 各个数据库厂商去实现这套接口,提供数据库驱动jar包
- 我们可以使用这套接口(JDBC)编程,真正执行的代码是驱动jar包中的实现类
缺点
- 注册驱动硬编码
- 封装对象集合繁琐
- 资源浪费,性能降低
数据库连接池
数据库连接池是个容器,负责分配,管理数据库连接(Connection)
- 它允许应用程序重复使用一个现有的数据库连接,而不是再重新建立一个
- 释放空闲时间超过最大空闲时间的连接,来避免因为没有释放连接而引起的数据库连接遗漏
优势
- 资源重用
- 提升系统响应速度
- 避免数据库连接遗漏
标准接口
DataSource
官方sun提供的数据库连接池接口,由第三方组织实现此接口
功能:获取连接
Connection getConnection() throws SLQException;
常见产品
- C3PO
- DBCP
- Druid
Druid连接池是阿里巴巴开源的数据库连接池项目
功能强大,性能优秀,是Java语言最好的数据库连接池之一 - Hikari
Lombok
是一个实用的Java类库,能通过注解的形式自动生成构造器,getter/setter,equals,hashcode,toString等方法,并可以自动化生成日志变量,简化java开发,提高效率
注解
@Getter/@Setter
为所有的属性提供get/set方法
@ToString
会给类自动生成易阅读的toString方法
@EqualsAndHash
根据类所拥有的非静态字段自动重新equals方法和hashCode方法
@Data
提供了更综合的生成代码功能(@Getter+@Setter+@ToString+@EqualsAndHashCode)
@NoArgsConstructor
为实体生成无参的构造器方法
@AllArgsConstructor
为实体类生成除了static修饰的字段之外带有各参数的构造器方法
注意事项
lombok会在编译时,自动生成对应的java代码,我们使用lombok时,还需要安装一个lombok的插件(idea自带)
Lombok工具包-@Slf4j
使用sout输出信息弊端
- 信息只会展示在控制台
- 不能将日志记录到其他位置(文件,数据库)
- 想取消日志,需要修改源代码才可以完成
使用@Slf4j注解
- 添加lombok依赖
- 在需要进行日志输出的类上使用@Slf4j,生成logger对象