MySQL

# 数据库

DB：DataBase，数据库，存储和管理数据的仓库
DBS：DataBase Management System，数据库管理系统，操作和管理数据库的软件
SQL：Structured Query Language，操作关系型数据库的编程语言，定义操作所有关系型数据库的统一标准
关系型数据库：建立在关系模型基础上，由多张相互连接的二维表组成的数据库

# MySQL

mysql download
SQL 语句：

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

# DDL

database 可替换为 schema ；MySQL8 默认字符集为 utf8mb4

	-- 数据库
	-- 查询所有数据库
	show databases;

	-- 查询当前数据库
	select database();

	-- 使用 / 切换数据库
	use <数据库名>;

	-- 创建数据库
	create database [if not exists] <数据库名> [default charset utf8mb4];

	-- 删除数据库
	drop database [if exists] <数据库名>；

数据类型

数值类型：tinyint、int、bigint、float、double、decimal
字符串类型：char（定长）、varchar（变长）
日期类型：date、datetime

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

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

基础字段：id 主键、create_time 创建时间、update_time 修改时间

	-- 表结构
	create table <表名>(
	<字段1> <字段类型> [约束] [comment 字段1注释],
	……
	<字段n> <字段类型> [约束] [comment 字段n注释]
	)[comment 表注释];

	create table user(
	id int primary key auto_increment comment 'ID，唯一标识', -- 主键，自增 id
	username varchar(50) not null unique comment '用户名', -- 非空，唯一
	name varchar(10) not null comment '姓名', -- 非空
	age int comment '年龄',
	gender char(1) default '男' comment '性别', -- 默认为男
	create_time datetime comment '创建时间',
	update_time datetime comment '修改时间'
	)comment '用户信息表';

	-- 查询当前数据库的所有表
	show tables;
	-- 查询表结构
	desc <表名>;
	-- 查询建表语句
	show create table <表名>;

	-- 修改表结构
	alter table <表名> add <字段名类型>; -- 添加字段
	alter table <表名> modify <字段名> <新数据类型>; -- 修改字段类型
	alter table <表名> change <旧字段名> <新字段名> <新类型类型>; -- 修改字段名与字段类型
	alter table <表名> drop <字段名>; -- 删除字段
	alter table <表名> rename to <新表名>; -- 修改表名
	drop table [if exists] <表名> ; -- 删除表

# DML

	-- 添加数据
	insert into <表名> (字段1,字段2) values (值1,值2) [,(值1,值2)……];
	insert into <表名> values (值1,值2……) [,(值1,值2……) ……];
	insert into user (name, create_time)
	values ('mof',now()), ('fom', now());

	-- 修改数据
	update <表名> set 字段1 = 值1, 字段2 = 值2, …… [where 条件];
	update user set create_time = NOW() where id = 1;

	-- 删除数据
	delete from <表名> [where 条件];
	delete from user where id = 2;

# DQL

select 字段列表 from 表名列表 where 条件列表 group by 分组字段列表 having 分组后条件列表 order by 排序字段列表 limit 分页参数
like：模糊匹配， _ 单个字符， % 任意个字符
group by 后只能查询分组字段和聚合函数
聚合函数：将一列数据作为整体，纵向计算，不包括 null

count：count(*)、count(1)、count(id)
max、min、avg、sum

order by：默认升序 asc；降序 desc
limit 起始索引，查询记录数

	-- distinct 不重复；as 取别名
	select distinct name as '姓名' from user;
	select gender, count(*) from user group by gender;
	select * from user order by entry_time, update_time desc;
	select * from user limit 10;

# 多表查询

多表关系

一对多：多的表增加额外字段
一对一：查询频率少的表增加额外字段
多对多：中间表连接

外键

物理外键：影响性能，只适用于单节点数据库，容易死锁
alter table student add constraint fk_student_clazz_id foreign key (clazz_id) references clazz(id);
逻辑外键：业务逻辑中实现

内连接：舍弃不满足连接条件的（去除 null 值）

	-- 隐式连接，常用
	select 字段 from 表1 [as 别名1], 表2 [as 别名2] where 连接条件;

	-- 显式连接
	select 字段 from 表1 [inner] join 表2 on 连接条件;

外连接：完全包含左表或右表（包括 null 值）

	-- 左外连接，常用
	select 字段 from 表1 left [outer] join 表2 on 连接条件;

	-- 右外连接
	select 字段 from 表1 right [outer] join 表2 on 连接条件;

子查询
查询每个部门最高薪资的员工信息

	-- 多个字段用括号去比较
	select * from emp where (emp.salary, emp.dept_id) in (select MAX(salary), dept_id from emp group by dept_id);

	-- 子查询作临时表
	select * from emp, (select dept_id, MAX(salary) maxsalary from emp group by dept_id) e where emp.dept_id = e.dept_id and emp.salary = e.maxsalary;

# 事务管理

事务是一组操作的集合，事务会把所有操作作为一个整体一起向系统提交或撤销操作请求，这些操作要么同时成功，要么同时失败。
四大特性（ACID）：原子性、一致性、隔离性、持久性

开启事务： start transaction 或 begin
提交事务： commit ，全部操作成功
回滚事务： rollback ，存在操作失败

Spring 事务管理注解： @Transactional(rollbackFor = {Exception.class}, propagation = Propagation.REQUIRED)

rollbackFor：用于控制出现何种异常类型，回滚事务，默认为 RuntimeException
propagation：事务传播行为，当一个事务方法被另一个事务方法调用时，这个事务方法如何进行事务控制，默认为 REQUIRED

属性值	含义
REQUIRED	需要事务，有则加入，无则创建新事务。
REQUIRES_NEW	需要新事务，无论有无，总是创建新事务。
SUPPORTS	支持事务，有则加入，无则在无事务状态中运行。
NOT_SUPPORTED	不支持事务，在无事务状态下运行，如果当前存在已有事务，则挂起当前事务。
MANDATORY	必须有事务，否则抛异常。
NEVER	必须没事务，否则抛异常。

# JDBC

JDBC：Java DataBase Connectivity，使用 Java 操作关系型数据库的一套 API，由官方定义规范（接口），由数据库厂商实现接口（驱动）
常见框架：MyBatis、MyBatis Plus
url：JDBC: 数据库类型://ip:port / 数据库名
预编译 SQL：更安全（SQL 注入）、性能更高（缓存）
驱动依赖

	<dependency>
	<groupId>com.mysql</groupId>
	<artifactId>mysql-connector-j</artifactId>
	</dependency>

使用

	public void testJDBC() throws ClassNotFoundException, SQLException {
	// 注册驱动
	Class.forName("com.mysql.cj.jdbc.Driver");

	// 获取数据库连接
	String url = "jdbc:mysql://ip:port/database";
	String username = "";
	String password = "";
	Connection connection = DriverManager.getConnection(url, username, password);

	// 获取 SQL 语句执行对象
	Statement statement = connection.createStatement();

	// 执行 SQL 语句
	ResultSet resultSet = statement.executeQuery("select * from user");
	while (resultSet.next()){
	System.out.println(resultSet.getString("name"));
	}

	// 释放资源
	statement.close();
	connection.close();
	}

# MyBatis

MyBatis：持久层框架，简化 JDBC 开发

# 配置

依赖

	<dependency>
	<groupId>com.mysql</groupId>
	<artifactId>mysql-connector-j</artifactId>
	</dependency>
	<dependency>
	<groupId>org.mybatis.spring.boot</groupId>
	<artifactId>mybatis-spring-boot-starter</artifactId>
	</dependency>
	<dependency>
	<groupId>org.projectlombok</groupId>
	<artifactId>lombok</artifactId>
	</dependency>

数据库配置

	# 数据源
	spring:
	datasource:
	driver-class-name: com.mysql.cj.jdbc.Driver
	url: jdbc:mysql://ip:port/demo
	username:
	password:
	# type: com.alibaba.druid.pool.DruidDataSource # Druid 连接池配置
	mybatis:
	configuration:
	map-underscore-to-camel-case: true # 下划线、驼峰命名转换
	log-impl: org.apache.ibatis.logging.stdout.StdOutImpl # 日志输出
	mapper-locations: classpath:mapper/*.xml # xml 映射文件位置

# 数据库连接池

容器，负责分配、管理数据库连接，允许重复使用，空闲自动释放

标准接口：DataSource，getConnection 获取连接
常见产品：Druid、Hikari（SpringBoot 默认）

	<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>druid-spring-boot-starter</artifactId>
	</dependency>

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

# 使用

实体类 pojo.User

	@Data
	public class User implements Serializable {
	private static final long serialVersionUID = -1105300093577690395L;

	private int id;
	private String name;
	private Date createTime;
	}

持久层 Mapper.UserMapper

符号	说明	场景	优缺点
`#{}`	占位符。执行时，会将替换为，生成预编译 SQL	参数值传递	安全、性能高（推荐）
`${}`	拼接符。直接将参数拼接在 SQL 语句中，存在 SQL 注入问题	表名、字段名动态设置时使用	不安全、性能低

springboot 官方骨架中使用 Maven 插件，保留形参名称，所以可以去除 Param 注解，aliyun 骨架的不行
@Options(useGeneratedKeys = true, keyProperty = "id") 插入成功后返回主键到 id

	@Mapper
	public interface UserMapper {

	@Select("select id, name, create_time from user")
	List<User> list();

	@Delete("delete from user where id = #{id}")
	int delete(int id);

	@Options(useGeneratedKeys = true, keyProperty = "id")
	@Insert("insert into user (name, create_time) values (#{name}, now())")
	int add(User user);

	@Update("update user set name = #{name} where id = ${id};")
	int update(User user);

	@Select("select * from user where id = #{id} and name = #{name}")
	User findByIdAndName(int id, String name);
	// User findByIdAndName(@Param("id") int id, @Param("name") String name);
	//springboot 官方骨架中使用 Maven 插件，保留形参名称，所以可以去除 Param 注解，aliyun 骨架的不行

	List<User> findByName(String name);
	}

# XML 映射配置

插件：MyBatisX

mybatis.mapper-locations=classpath:mapper/*.xml 文件位置配置为 resource/mapper/
文件名称与 Mapper 接口一致
namespace 设置为接口全限定名
id 与方法名一致

动态 SQL

	<?xml version="1.0" encoding="UTF-8" ?>
	<!DOCTYPE mapper
	PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
	"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
	<mapper namespace="com.mof.mapper.EmpMapper">

	<!-- where 会自动去除条件前面多余的 and 或 or 关键字 -->
	<select id="find" resultType="com.mof.pojo.Emp">
	select e.*, d.name deptName
	from emp e left join dept d on e.dept_id = d.id
	<where>
	<if test="name != null and name != ''">
	e.name like concat('%', #{name}, '%')
	</if>
	<if test="gender == 1 or gender == 2">
	and e.gender = #{gender}
	</if>
	<if test="begin != null">
	and e.entry_date > #{begin}
	</if>
	<if test="end != null">
	and #{end} > e.entry_date
	</if>
	</where>
	order by e.update_time desc
	</select>

	<!-- 遍历插入，collection 为集合，item 为集合内元素，separator 为遍历元素的分割符 -->
	<!-- open 为遍历开始前拼接的内容，close 为遍历结束后拼接的内容 -->
	<insert id="insertBatch">
	insert into emp_expr (emp_id, begin, end, company, job) values
	<foreach collection="exprList" item="expr" separator=",">
	(#{expr.empId}, #{expr.begin}, #{expr.end}, #{expr.company}, #{expr.job})
	</foreach>
	</insert>
	</mapper>

# 分页查询

PageHelper：第三方分页插件，简化分页操作
依赖

	<dependency>
	<groupId>com.github.pagehelper</groupId>
	<artifactId>pagehelper-spring-boot-starter</artifactId>
	<version>1.4.7</version>
	</dependency>

使用

	PageHelper.startPage(page, pageSize);
	List<Emp> emps = empMapper.findAll(); // 正常 sql 语句
	Page<Emp> p = (Page<Emp>) emps;
	p.getTotal(); // 获取总记录数
	p.getResult(); // 获取数据

实现机制：
先 select count(0) ，计算总记录数
再加上 limit ?, ? ，分页查询
因此，自定义的 select 语句结尾不能加分号，不然会中断，报错

select e.*, d.name deptName from emp e left join dept d on e.dept_id = d.id order by e.update_time desc
Preparing: SELECT count(0) FROM emp e LEFT JOIN dept d ON e.dept_id = d.id
Preparing: select e.*, d.name deptName from emp e left join dept d on e.dept_id = d.id order by e.update_time desc LIMIT ?, ?

# MyBatis-plus

	<!--mysql 驱动依赖 -->
	<dependency>
	<groupId>com.mysql</groupId>
	<artifactId>mysql-connector-j</artifactId>
	</dependency>
	<!--mybatis-plus 起步依赖 -->
	<dependency>
	<groupId>com.baomidou</groupId>
	<artifactId>mybatis-plus-boot-starter</artifactId>
	<version>3.5.3</version>
	</dependency>
	<!--lombok 依赖，添加 getter、setter、toString-->
	<dependency>
	<groupId>org.projectlombok</groupId>
	<artifactId>lombok</artifactId>
	</dependency>

# 使用

pojo.User
用户实体类，与数据表对应

	@Data // 添加 get 和 set 方法
	@TableName("user") // 表名
	public class User{
	// 驼峰命名与数据库的下划线命名自动转换，不用在配置文件配置
	@TableId(type = IdType.AUTO) // 自增键值
	private Integer userId;

	private String userName;
	private String password;
	private String userPhonenumber;
	}

mapper.UserMapper
数据映射层，数据操作，这里继承 BaseMapper，封装了大部分数据库操作
启动类需要添加注解 @MapperScan(basePackages = "com.mof.demo.mapper")

	public interface UserMapper extends BaseMapper<User> {
	}

service.UserService
继承 IService，可以调用已实现的方法

	public interface UserService extends IService<User> {
	String byId(Integer id);
	}

方法一，直接调用封装的函数

	@Service
	public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
	@Override
	public String byId(Integer id) {
	return this.getById(id).toString();
	}
	}

方法二，QueryWrapper 查询

	@Service
	public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
	@Override
	public String byId(Integer id) {

	QueryWrapper<User> queryWrapper = new QueryWrapper<>();
	queryWrapper.eq("user_id", id);

	return userMapper.selectOne(queryWrapper);
	}
	}

# 分页查询

配置类设置分页插件

	@Configuration
	public class UserConfiguration {

	// Mybatis 分页插件
	@Bean
	public MybatisPlusInterceptor mybatisPlusInterceptor(){
	MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
	interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));

	return interceptor;
	}
	}

具体操作

	// 按类别 id 查询，按销量倒序排序
	QueryWrapper<Product> queryWrapper = new QueryWrapper<>();
	queryWrapper.eq("category_id", categoryId).orderByDesc("product_sales");

	// 按页查询
	IPage<Product> ipage = new Page<>(1, 10);
	IPage<Product> page = productMapper.selectPage(ipage, queryWrapper);

	// 获取总记录数和数据
	long total = page.getTotal();
	List<Product> productList = page.getRecords();

# 数据库

# MySQL

# DDL

# DML

# DQL

# 多表查询

# 事务管理

# JDBC

# MyBatis

# 配置

# 数据库连接池

# 使用

# XML 映射配置

# 分页查询

# MyBatis-plus

# 使用

# 分页查询

java相关知识