MySQL、Mybatis学习日记
MySQL：关系型数据库(多张二维表连接)
e.g.根据部门关联
配置环境变量
添加MYSQL_HOME
//路径：路径\MySQL
配置path
//路径：路径\MySQL\bin
MySQL连接
登录
mysql -u用户名 -p密码 [-h数据库服务器IP地址 -P端口号] //其中-h -P用来企业远程连接
退出
exit
不指定[]参数，则默认连接3306端口
SQL语法
DDL：数据库、表、字段
DDL语句、图形化：使用DataGrip
DataGrip安装
启动Mysql，使其运行状态
cmd + R
services.msc 后找到对应MySQL查看是否处于启动
下载驱动，然后测试链接显示succee即可
基础DDL语句
基础语句
查询所有数据库
SHOW DATABASES;
查询当前数据库
SELECT DATABASE();
使用/切换数据库
USE 数据库名;
创建数据库
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET utf8mb4];
删除数据库
DROP DATABASE [IF EXISTS] 数据库名;
其中database可以替换为schema，databases->schemas
约束：制定规则限制，多个约束空格分开
-- 创建带约束的用户信息表
CREATE TABLE user(
id INT PRIMARY KEY COMMENT 'ID, 唯一标识', -- 主键约束
username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名', -- 非空 + 唯一
name VARCHAR(10) NOT NULL COMMENT '姓名', -- 非空
age INT COMMENT '年龄',
gender CHAR(1) DEFAULT '男' COMMENT '性别' -- 默认值
) COMMENT '用户信息表';
实现自动增长auto_increment
id INT PRIMARY KEY auto_increment COMMENT 'ID, 唯一标识', -- auto_increment
数据类型：满足需求下，尽可能选占用小的
无符号使用：int unsigned
字符串类型：音视频文件blob后缀
长度固定char，其余varchar
描述信息存文本用text等
日期类型
表-增删改查
-- 创建表的基本语法
CREATE TABLE tablename(
字段1 字段类型 [约束] [COMMENT '字段1注释'],
......
字段2 字段类型 [约束] [COMMENT '字段2注释']
) [COMMENT '表注释'];
e.g.
点击查看代码
CREATE TABLE user(
id INT COMMENT 'ID, 唯一标识',
username VARCHAR(50) COMMENT '用户名',
name VARCHAR(10) COMMENT '姓名',
age INT COMMENT '年龄',
gender CHAR(1) COMMENT '性别'
) COMMENT '用户信息表';
查询当前数据库的所有表
SHOW TABLES;
查询表结构
DESC 表名;
查询建表语句
SHOW CREATE TABLE 表名;
添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT '注释'] [约束];
修改字段类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
修改字段名与字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT '注释'] [约束];
删除字段
ALTER TABLE 表名 DROP COLUMN 字段名;
修改表名
ALTER TABLE 表名 RENAME TO 新表名;
删除表
DROP TABLE [IF EXISTS] 表名;
案例
DML：数据操作
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 = 值1, 字段名2 = 值2, ...... [WHERE 条件];
若是不加以where约束十分危险，会更新整张表数据
多约束：数据使用AND/OR连接
e.g.
UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ...... where id = 1;
delete：无法删某个字段，删字段用update将值改为NULL
delete from 表名 [where 条件]；
无where删整张表
DQL：表查询
基本查询
查询多个字段
SELECT 字段1, 字段2, 字段3 FROM 表名;
查询所有字段（通配符）
SELECT * FROM 表名;
为查询字段设置别名，AS 关键字可以省略
SELECT 字段1 [AS 别名1], 字段2 [AS 别名2] FROM 表名;
去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
先写from表会自动识别字段，别名时若中间有空格(要引号)
条件查询：like模糊匹配，多条件之间可以添加括号
在20xx-9-1到20xx-7-1日，且X = 1的
(between '20xx-9-1' and '20xx-7-1) and X = 1;
查询是2，3，4的信息
in (2, 3, 4);
like：_代表单个字符，%代表任意个字符
分组查询：按某字段分组，操作聚合，所以是(分组 + 聚合)
聚合函数： 将一列数据作为一个整体，进行纵向计算
聚合函数不参与null值统计
格式
select 聚合函数() from 表名;
分组查询：分组后，select后字段不能随意写，能写分组字段 + 聚合函数
分组查询
SELECT 字段列表 FROM 表名 [WHERE 条件列表] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
按性别分组并统计男女员工数量
select gender, count(*) from 表名 group by gender;
查询入职时间在'20xx-xx-xx'及之前员工，并根据职位分组，最终获取员工数量>2的职位
SELECT job, COUNT(*) FROM 表名 WHERE entry_date <= '2015-01-01' GROUP BY job HAVING COUNT(*) >= 2;
where和having的区别：
执行顺序：where > 聚合函数 > having
排序查询：多字段只有一个字段值相同，才会根据第二个字段排序
排序查询
SELECT 字段列表 FROM 表名 [WHERE 条件列表] [GROUP BY 分组字段名 HAVING 分组后过滤条件] ORDER BY 排序字段 排序方式;
多字段排序：字段 方式，字段 方式，····
多字段只有一个字段值相同，才会根据第二个字段排序
分页查询(不同数据库这部分SQL不同)：数据多分页展示，分页参数
-- 分页查询
SELECT 字段 FROM 表名 [WHERE 条件] [GROUP BY 分组字段 HAVING 过滤条件] [ORDER BY 排序字段] LIMIT 起始索引, 查询记录数;
起始索引：代表从哪条数据往后查，起点从0开始
查询记录数：每页展示多少条
查询第二页数据，每页展示5条数据
select * from 表名 limit 5, 5; //第一页0-4，第二页从5开始
页码，起始索引的计算：第x页的起始索引 = (x - 1) * 每页展示记录数
Mybatis：java程序操作数据库 ，优化JDBC
JDBC:java语言操作关系型数据库的一套API
JDBC是一套规范，本身不提供实现，而是对应数据库的厂商提供接口(驱动Jar包)
所以直接使用JDBC接口即可，最终执行的是Jar包的实现类
JDBC入门程序
基于JDBC程序，执行update语句(update user set age = 25 where id = 1)
步骤：
- 创建maven项目、引入依赖 并准备数据库表user
pom.xml
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>8.0.33</version>
</dependency>
- 实现：编写JDBC操作数据库
JDBC程序
//1. 注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2. 获取连接
String url = "jdbc:mysql://localhost:3306/web01";
String username = "root";
String password = "1234";
Connection connection = DriverManager.getConnection(url, username, password);//连接对象Connection
//3. 获取SQL语句执行对象
Statement statement = connection.createStatement();//创建Statement对象执行SQL语句
//4. 执行SQL
int i = statement.executeUpdate("update user set age = 25 where id = 1");
其中.executeQuery()查询语句； .executeUpdate()增删改；
//5. 释放资源
statement.close();
connection.close();
其中驱动链接获取： 两次shift，搜索Driver：com.mysql.cj.jdbc，赋值全类名放到()中
对于url：固定格式为"jdbc:mysql://localhost3306/具体数据库名"
Mybatis是Dao层操作数据库： Mybatis
Mybatis配置：Mybatis开发只需要定义Mapper接口，实现类会自动实现
- 创建Springboot工程、数据库表、实体类
保持让类中属性名 与 表中字段名保持一致，这样会自动封装
使用实体类时，推荐使用包装类型，如：Integer等···
- 引用Mybits依赖，配置Mybatis
勾选SQL下：Mybatis Framework、MySQL Driver依赖
设置使用的数据库：在resource/application.properties
- Driver驱动(固定)
- url
固定协议jdbc:mysql://
jdbc:mysql://localhost:3306/使用的数据库名
- application.properties下配置
点击查看代码
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/mybatis
spring.datasource.username=用户名
spring.datasource.password=密码
- 编写SQL语句(注解/XML)：将SQL结果封装到返回值
@Mapper
public interface UserMapper {
@Select("select * from user")
public List<User> list();
}
多用包装类型：好处是包装类没有默认值，不存在歧义
JDBC执行DML、DQL语句？
执行DML
int rowsAffected = statement.executeUpdate();//返回int即影响记录数
执行DQL
ResultSet rs = statement.executeQuery();//结果集对象，封装查询结果
DQL语句执行完毕ResultSet的解析？
resultSet.next():光标下移一行
resultSet.getXxx():获取字段数据
ResultSet(结果集)：光标概念(默认在每行前一个)判断当前行是否有数据
while(resultSet.next()) {//遍历方法
int id = resultSet.getInt("id");
//···
}
提示词案例：使用AI
点击查看代码
你是一名java开发工程师，帮我基于JDBC程序来操作数据库，执行如下SQL语句：select id,username,password,name,age from user where username = 'daqiao' and password = '123456';
并将查询的每一行记录，都封装到实体类User中，然后将User对象的数据输出到控制台中。
User 实体类属性如下：
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
private Integer id; //ID
private String username; //用户名
private String password; //密码
private String name; //姓名
private Integer age; //年龄
}
提示词下的完整小案例：新知 & API
点击查看代码
package org.gchaoyang.testmybatis;
import org.gchaoyang.testmybatis.pojo.User;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import java.sql.*;
@SpringBootTest // Spring Boot 测试注解
public class JdbcTest {
@Test // JUnit 测试方法注解
public void testUpdate() throws Exception {
//1. 注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2. 获取数据库连接
String url = "jdbc:mysql://localhost:3306/mybatis";//链接地址
String username = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url, username, password);
//3. 获取SQL语句执行对象
Statement statement = connection.createStatement();
//4. 执行SQL (DML)
int i = statement.executeUpdate("update user set age = 25 where id = 1");
System.out.println("SQL执行完毕影响的记录数为: " + i);
//5. 释放资源
statement.close();
connection.close();
}
@Test
public void testSelect() {
String URL = "jdbc:mysql://localhost:3306/mybatis";
String USER = "root";
String PASSWORD = "root";
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;//封装查询结果，结果集对象
try {
// 1. 注册 JDBC 驱动
Class.forName("com.mysql.cj.jdbc.Driver");
// 2. 打开连接
conn = DriverManager.getConnection(URL, USER, PASSWORD);
// 3. 执行查询
//这种sql用法为预编译语句：安全，性能更高
String sql = "SELECT id, username, password, name, age FROM user WHERE username = ? AND password = ?";//？为占位符
stmt = conn.prepareStatement(sql);//为Statement子接口，也可以执行SQL语句
//但首先要给1，2占位符赋值
stmt.setString(1, "daqiao");
stmt.setString(2, "123456");
rs = stmt.executeQuery();//将查询结果封装到ResultSet对象中
// 4. 处理结果集：其中可能有多条数据
while (rs.next()) {//通过光标辨析是否有值
User user = new User(
rs.getInt("id"),
rs.getString("username"),
rs.getString("password"),
rs.getString("name"),
rs.getInt("age")
);
System.out.println(user); // 使用 Lombok 的 @Data 自动生成的 toString 方法
}
} catch (SQLException se) {
// Handle errors for JDBC
se.printStackTrace();
} catch (Exception e) {
// Handle errors for Class.forName
e.printStackTrace();
} finally {
// 5. 关闭资源
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
if (conn != null) conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
}
}
其中要给与完整的字段信息，用以数据匹配
预编译SQL：使用PreparedStatement，安全高性能
非固定形式，而是用参数传递形式，即为预编译
预编译：使用PreparedStatement
PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM user WHERE username = ? AND password = ?");
pstmt.setString(1, "daqiao");
pstmt.setString(2, "123456");
ResultSet resultSet = pstmt.executeQuery();
与其相对的则是静态SQL(参数硬编码)：直接使用Statement即可
静态SQL
点击查看代码
Statement statement = connection.createStatement();
int i = statement.executeUpdate("update user set age = 25 where id = 1");
System.out.println("SQL执行完毕，影响的记录数为：" + i);
优势：防止SQL注入、性能更高
运行jar包(jar包地址下)cmd， 启动后根据端口号浏览器打开
java -jar 名.jar
使用SQL注入:导致有select count(*) from emp where username = 'sjdkfjkdjfks' and password = '' or '1' = '1'; 1=1恒等
任意用户名xxxx
密码：写入带有攻击性字符串
'or'1'='1
SQL语句执行流程
预编译充分利用了缓存功能：实现了操作相同的优化
Mybatis(简化JDBC)：持久(Dao)层框架，1配置ap，2定义接口
对比：左为JDBC，右为Mybatis Mybatis
Mybatis写法:定义接口，在其中声明接口方法 并 在其上添加注解(要执行的SQL语句)
最终调用接口内部方法时，会执行其SQL语句，并将其结果封装到集合中，最后将其内每一条记录封住到一个对象中
自动
@Mapper
public interface UserMapper {
/**
* 查询全部
*/
@Select("select * from user")
public List<User> findAll();
}
此时@SpringBootTest注解中，使用注入Bean即可
@SpringBootTest //SpringBoot单元测试的注解 - 当前测试类中的测试方法运行时，会启动springboot项目 - IOC容器
class SpringbootMybatisQuickstartApplicationTests {
@Autowired
private UserMapper userMapper;
@Test
public void testFindAll() {
List<User> userList = userMapper.findAll();
userList.forEach(user -> System.out.println(user));
}
}
@SpringBootTset注解：测试类和启动类的包名要相同
辅助配置：SQL提示
对于@Mapper中SQL语句初始会被当做默认字符串(不提供提示)
以此标识此字符串为何种语言
但以此产生问题，(数据库配置信息只在运行时生效)此时不会识别
以此需要idea中连接数据库，配置信息后要test连通
辅助配置：Mybatis日志输出
关键字mybatislog.impl
JDBC vs Mybatis
JDBC存在参数硬编码问题(被固定)，且结果集获取繁琐
而Mybatis中存储在配置文件(无需编译)无需修改对应java文件
且通过数据库连接池优化资源浪费问题
数据库连接池
若无数据库连接池，则对于SQL进程要创建connection(每次创建->用完关闭)
而启用连接池则会持久化一个连接池(使用与归还)
连接池遗漏问题(处理)
对于空调用或其他情况一直占用进程，不归还给线程池情况
数据库连接池会监控进程空闲时间：> \(MAX_time\)，则会将连接归还给数据池
标准接口：DaTaSource
官方提供数据库连接池，由第三方组织实现
功能：获取连接
Connection getConnection() throws SQLException;
后2个较常用，默认Hikari
切换连接池：Druid
Mybatis增删改查：对于增删改无需返回void
删，使用@Delete()
使用预编译传参来使得内容不固定
此时调用即可
@Test
public void testDetleteById() {
userMapper.deleteById(参数);//直接调用方法即可
}
采用Integer返回值：接取被影响的记录数
Integer i = userMapper.deleteById(5);
System.out.println(i);//影响记录数
注意：对于删除掉的ID，也会默认占位，此时自增会默认从删之前的ID+1
预编译写法 #{} 与 ${}
-
#{}：更多是参数值传递
- ${}：更多是表名、字段名动态设置使用，会拼接到SQL语句中再编译，存在SQL注入
增：使用@Insert()，当参数过多封装对象传参
其中要和获取属性一一对应
@Test
public void testInsert(){
User user = new User(null, "gaoyuanyuan", "666888", "高圆圆", 18);//id未使用，可任意传参
userMapper.insert(user);
}
改：使用@Update()，多参数封装
使用方式同增
查：使用@Select()，返回值只有一个，所以封装到对象中
其中传递形参不会保留，即无法得知哪个是所需要的值，涉及多个形参要起名
若基于SpringBoot骨架项目则可省略
原因是SpringBoot其中存在副工程Maven下保留了接口形参名
对于框架错误查看：找到最后一个Caused by
XML映射
XML映射配置：resource目录用/
在Mybatis网站中获取配置信息：Mybatis中文网
此即为基础结构
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="xxx.xxx.xxx.xxx(与全限定名保持一致)">
内容
</mapper>
增删改查对应标签
e.g.全结构方式：
<select id = "findAll(方法名)" resultType = "xxx.xxx.User(返回类型的全类名)">
类型：
<update>
<delete>
<insert>
场景辨析
XML辅助配置：
错误：没找到对应SQL(未同包同名)
Invalid bound statement (not found): com.itheima.mapper.UserMapper.findAll
辅助配置1：配置XML指定映射文件位置
关键字mybatislocation···
指定XML映射配置文件的位置
mybatis.mapper-locations=classpath:mapper/*.xml
java和resource下文件编译后都放在classpath类路径下
辅助配置2：使用MybatisX插件
可以点击小鸟：快速切换定位在接口/配置文件之间
SpringBoot配置文件：properties：key-value，yaml、yml
yaml、yml配置文件：通过缩进确定层级，且：后要有空格
在application.yml
# 定义对象/Map集合
user:
name: Tom
age: 18
password: 123456
# 定义数组/List/Set集合
hobby:
- Java
- Game
- Sport
其中若以0开头，则要加''，如'0123456'，否则会认为其是8进制数据
将application.properties配置文件改为yml配置
输入类(原properties)关键字即可
e.g.
springapp···
springdat···
浙公网安备 33010602011771号