MySQL+MyBatis – pidanxia’ s Blog

DDL(数据定义语言)：

概念：

DDL用于定义数据库的结构和模式，包括创建、修改和删除数据库对象，如表、索引、视图、约束等。常见的DDL命令包括CREATE、ALTER和DROP等。

目的：

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

约束：

非空约束：

限制该字段值不能为null

关键字：not null

唯一约束：

保证字段的所有数据都是唯一、不重复的

关键字：unique

主键约束：

主键是一行数据的唯一标识，要求非空且唯一

关键字：primary key

默认约束：

保存数据时，如果未指定该字段值，则采用默认值

关键字：default

外键约束：

让两张表的数据建立连接，保证数据的一致性和完整性

关键字：foreign key

格式：

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

例子：

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 '用户表';

DDL(表操作)

添加字段：alter tablr 表名 add 字段名类型(长度) [comment 注释] [约束];
修改字段类型：alter tablr 表名 modify 字段名新数据类型(长度)
修改字段名和字段类型:alter tablr 表名 change 旧字段名新字段名类型(长度) [comment 注释] [约束]
删除字段：alter tablr 表名 drop column 字段名
修改表名：rename table 表名 to 新表名
删除表：drop table [if exists] 表名;

DML(数据操作语言):

DML用于对数据库中的数据进行操作，包括插入、更新、删除和查询数据。常见的DML命令包括INSERT、UPDATE、DELETE和SELECT等。

添加数据：(INSERT)
修改数据：(UPDATE)
删除数据：(DELETE)

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

注意：

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

update：

修改数据：update 表名 set 字段名 = 值1,字段名2 = 值2,……[where 条件];
where可以有，也可以没有，如果没有，则会修改整张表的所有数据

delete：

删除数据：delete from 表名 [where 条件];
where可以有，也可以没有，如果没有，则会修改整张表的所有数据
DELETE语句不能删除某一个字段的值(如果要操作，可以使用UPDATE，将改字段的值置为null)

DQL(数据查询语言)：

DQL用于查询数据库中的数据，它是最常用的数据库操作语言。通过DQL，可以从数据库中检索所需的数据。常见的DQL命令是SELECT，它允许用户指定要检索的数据列、条件和排序规则等。

select
字段列表
from
表名列表
where
条件列表
group by
分组字段列表
having
分组后条件列表
order by
排序字段列表
limit
分页参数

聚合函数：

count：统计数量
max：最大值
min：最小值
avg：平均值
sum：求和

案例1：查询员工

按需求完成员工管理的条件分页查询-根据输入条件，查询第一页数据，每页展示10条记录

根据输入的员工姓名、员工性别、入职时间搜索满足条件的员工信息
其中员工姓名，支持模糊匹配；性别进行精确查询；入职时间进行范围查询
支持分页查询
并对查询的结果，根据最后修改时间进行倒序排序

输入条件：

姓名：张
性别：男
入职时间：2000-01-01 ~ 2015-12-31

select
*
from
tb_emp
where name like '%张%' 
and gender = 1 
and entrydate between '2000-01-01' and '2015-12-31'
order by update_time desc
limit 0,10

案例2-1：

完成员工性别信息统计

if(条件表达式,true取值,false取值)

select if(gender = 1,'男性员工','女性员工') 性别,count(*) from tb_emp group by gender;

案例2-2：

case 表达式 when 值1 then 结果1 when 值2 then 结果2 … else … end

select
    (case job when 1 then '班主任' when 2 then '讲师' when3 then '学工主管' when 4 then '教研主管' else '未分配岗位' end) 职
    count(*)
from tb_emp group by job;

外键语法：

创建时指定：

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

建完表后，添加外键
alter table 表名 add constraint 外键名称 foreign key(外键字段名) reference 主表(字段名)

物理外键：

使用foreign key 定义外键关联另外一张表

缺点：

影响增、删、改的效率(需要检查外键关系)
仅用于单节点数据库，不适用于分布式、集群场景
容易引发数据库的死锁问题，消耗性能

逻辑外键：

在业务层逻辑中，解决外键关联
通过逻辑外键，就可以很方便的解决上述问题

一对一：

案例：用户与身份证信息的关系
关系：一对一关系，多用于单表拆分，将一张张表的基础阻断放在一张表中，其他字段放在另一张表中，以提升操作效率
任意一方，添加外键，关联另一方的主键

一对多：

在多的一方添加外键，关联另一方的主键

多对多：

案例：学生与课程的关系
关系：一个学生可以选修多门课程，一门课程也可以提供多个学生选择
实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

多表查询：

连接查询：

内连接：

相当于查询A、B交集部分数据

外连接：

左外连接：
查询左表所有数据(包括两张表交集部分数据)
右外连接：

查询右表所有数据(包括两张表交际部分数据)

子查询：

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

列子查询：子查询返回的结果为一列

行子查询：子查询返回的结果为一行

表子查询：子查询返回的结果为多行多列

列子查询：

select entrydate,job from tb_emp where name = '韦一笑';
select * from tb_emp where entrydate = '2007-01-01' and job = 2;
等价于：select * from tb_emp where (entrydate,job)=('2007-01-01',2);
等价于：select * from tb_emp where (entrydate,job)=(select entrydate,job from tb_emp where name = '韦一笑');

表子查询：

查询入职时间是’2006-01-01’之后的员工信息，及其部门名称

select * from tb_emp where entrydate > '2006-01-01';

select e.*,d.name from (select * from tb_emp where entrydate > '2006-01-01') as e,tb_dept as d where e.dept_id = d.id

事务：

事务介绍：

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

注意：默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务

事务操作：

开启事务：start transaction;/begin;

提交事务：commit;

回滚事务：rollback;

四大特性：

原子性
事务是不可分割的最小单元，要么全部成功，要么全部失败
一致性

事务完成时，必须使所有的数据都保持一致
隔离性

数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境上运行
持久性

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

索引：

索引是帮助数据库高效获取数据的数据结构

优点：

提高数据查询的效率，降低数据库的IO成本
通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗

缺点：

索引会占用存储空间
索引大大提高了查询效率，同时也降低了insert、updata、delete的效率

结构：

MySQL数据库支持的索引结构有很多，如：Hash索引、B+Tree索引、Full-Text索引等。

我们平常所说的索引，如果没有特别指明，都是指默认的B+Tree结构组织的索引

B+Tree(多路平衡搜索树)：

每一个节点，可以存储多个key(有n个key，就有n个指针)
所有的数据都存储在叶子节点，非叶子节点仅用于索引数据
叶子节点形成了一颗双向链表，便于数据的排序及区间范围查询

语法：

创建索引
create [unique] index 索引名 on 表名(字段名,……);
查看索引

show index from 表名;
删除索引

drop index 索引名 on 表名;

注意事项：
1. 主键字段，在建表时，会自动创建主键索引
2. 添加唯一约束时，数据库实际上会添加唯一索引

Mybatis:

准备工作(创建springboot工程、数据库表user、实体类User)

引入Mybatis的相关依赖，配置Mybatis(数据库连接信息)

#驱动类名称
spring.datasource.drive-class-name=com.mysql.cj.jdbc.Driver
#数据库链接的url
spring.datasource.url=jdbc:mysql://localhost:3306/mybatis
#链接数据库的用户名
spring.datasource.username=root
#连接数据库的密码
spring.datasource.password=1234

编写SQL语句(注解/XML)

@Mapper//在运行时，会自动生成该接口的实现类对象(代理对象)，并且将该对象交给IOC容器管理
public interface UserMapper {
   //查询全部用户
   @Select("select * from user")
   public List<User> list();
}

test：

@Autowired
private UserMapper userMapper;
@Test
public void testListUser(){
    List<User> userList = userMapper.list();
    userList.stream().forEach(user -> {
        System.out.println(user);
    });
}

数据库连接池：

数据库连接池是个容器，负责分配、管理数据库链接(Connection)
它允许应用程序重复使用一个现有的数据库链接，而不是再重新建立一个
释放空闲时间超过最大空闲时间的链接，来避免因为没有释放链接而引起的数据库链接遗漏

优势：

资源复用
提升系统响应速度
避免数据库链接遗漏

标准接口：DataSource

官方(sun)提供的数据库连接池接口，由第三方组织实现此接口
功能：获取链接：Connection getConnection() throws SQLException;
springboot默认的是Hikari

Druid:

Druid连接池是阿里巴巴开源的数据库连接池项目
功能强大，性能优秀，是Java语言最好的数据库连接池之一

切换连接池：

引入Druid的起步依赖就可以