一、MySQL

1.什么是数据库?

• 数据库：DataBase ( DB)，是存储和管理数据的仓库。
• 数据库管理系统：DataBase Management System (DBMS)，操纵和管理数据库的大型软件。
• SQL：Structured Query Language，操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准。

2.MySQL安装

• 官网下载地址： https://dev.mysql.com/downloads/mysql/

• 解压
• 添加环境变量

• 验证，选择命令提示符(管理员)，敲入mysql

ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost:3306' (10061)

• 初始化，在刚才的命令行中，输入如下的指令：mysqld --initialize-insecure
• 注册MySQL服务，命令行（注意必须以管理员身份启动）中，输入mysqld -install
• 启动MySQL服务，敲入net start mysql

net start mysql //启动mysql服务
net stop mysql  //停止mysql服务

• 修改默认账号密码，在黑框里敲入mysqladmin -u root passwond 1234，这里的 1234就是指默认管理员(即root账户)的密码，可以自行修改成你喜欢的。

mysqladmin -u root password 1234

• 登录，mysql -uroot -p1234
• 卸载
  • 选择"命令提示符"，选择右侧的"以管理员身份运行"
  • 敲入net stop mysql
  • 再敲入mysqld -remove mysql
  • 最后删除MySQL目录及相关的环境变量。

3.MySQL连接

• 语法：

mysql -u用户名―p密码[ -h数据库服务器IP地址-P端口号]

二、DDL

1.DDL（数据库操作)

• 查询
  • 查询所有数据库：show databases;
  • 查询当前数据库：select database();
• 创建
  • 创建数据库：create database [if not exists]数据库名;
• 使用
  • 使用数据库：use数据库名;
• 删除
  • 删除数据库：drop database [if exists]数据库名;

• 上述语法中的database，也可以替换成schema。如: create schema db01;

2.MySQL客户端工具

• 图形化工具
  

• DataGrip

  • 介绍：DataGrip是JetBrains旗下的一款数据库管理工具，是管理和开发MySQL、Oracle、PostgreSQL的理想解决方案。
  • 官网：https://www.jetbrains.com/zh-cn/datagrip/

3.表操作

• 创建

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

create table tb_user(id int comment 'ID，唯一标识',username varchar (20)comment '用户名',name varchar ( 10)comment '姓名',age int comment '年龄',gender char(1) comment '性别'
)comment '用户表';

• 约束
  • 概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。
  • 目的：保证数据库中数据的正确性、有效性和完整性。

4.数据类型

• MySQL中的数据类型有很多，主要分为三类:数值类型、字符串类型、日期时间类型。
• 参照：MySQL数据类型

• 年龄：age tinyint unsigned
• 分数：score double(4,1)

• char(10)：最多只能存10个字符，不足10个字符，占用10字符空间；性能高，浪费空间
• varchar(10)：最多只能存10个字符，不足10个字符，按照实际长度存储；性能低，节省空间
• 手机号：phone char(11)
• 用户名：username varchar(20)

• 生日：birthday date
• 操作时间：update_time datetime
• create_time:记录的是当前这条数据插入的时间
• update_time:记录当前这条数据最后更新的时间

5.表操作

• 查询
  • 查询当前数据库所有表：show tables;
  • 查询表结构：desc 表名;
  • 查询建表语句：show create table表名;

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

• 删除
  • 删除表：drop table [ if exists ]表名;
  • 在删除表时，表中的全部数据也会被删除。

三、DML

• DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增、删、改操作。
• 添加数据（INSERT)
• 修改数据（UPDATE)
• 删除数据（DELETE)

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

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

2.UODATE

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

3.DELETE

• 删除数据：delete from 表名 [where条件];
  

• DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。

• DELETE 语句不能删除某一个字段的值(如果要操作，可以使用UPDATE，将该字段的值置为NULL)。

四、DQL

• DQL英文全称是Data Query Language(数据查询语言)，用来查询数据库表中的记录。
• 关键字：SELECT

• 基本查询
• 条件查询(where)
• 分组查询(group by)
• 排序查询(order by)
• 分页查询(limit)

1.基本查询

• 查询多个字段：select 字段1,字段2,字段3 from 表名;
• 查询所有字段（通配符）： select * from 表名;
• 设置别名：select 字段1 [ as别名1 ]，字段2[ as别名2 ] from 表名;
• 去除重复记录：select distinct 字段列表 from 表名;

*号代表查询所有字段，在实际开发中尽量少用(不直观、影响效率)。

2.条件查询（where）

• 条件查询：select字段列表 from表名where条件列表;

3.分组查询（group by）

• 聚合函数
  • 介绍：将一列数据作为一个整体，进行纵向计算。
  • 语法：select 聚合函数(字段列表) from 表名;

count(字段)：不记录NULL值
建议使用：count(*)

• 分组查询：select 字段列表 from 表名 [where 条件] group by 分组字段名 [having分组后过滤条件];
• where与having区别
  1. 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组;而having是分组之后对结果进行过滤。
  2. 判断条件不同：where不能对聚合函数进行判断，而having可以。

• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
• 执行顺序：where >聚合函数>having 。

4.排序查询（order by）

• 条件查询：select 字段列 表from 表名[ where 条件列表][ group by分组字段] order by 字段1 排序方式1，字段2 排序方式2 …;
• ASC：升序（默认值)
• DESC：降序

• 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

5.分页查询（limit）

• 分页查询：select 字段列表 from 表名 limit 起始索引,查询记录数;
  

1. 起始索引从O开始，起始索引=(查询页码–7)*每页显示记录数。
2. 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
3. 如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10。

if(表达式, tvalue, fvalue)：当表达式为true时，取值tvalue;当表达式为false时，取值fvalue
case expr when value1 then result1 [when value2 then value2 …] [else result] end

五、多表设计

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

• 一对多(多对一)
• 多对多
• 一对一

1.一对多

• 一对多关系实现：在数据库表中多的一方，添加字段，来关联一的一方的主键。
• 外键语法

--创建表时指定
create table 表名(
字段名 数据类型，
…
[constraint][外键名称] foreign key(外键字段名) references 主表(字段名)
);
--建完表后，添加外键
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(字段名);

• 物理外键
  • 概念：使用foreign key定义外键关联另外一张表。
  • 缺点：
    • 影响增、删、改的效率（需要检查外键关系)。
    • 仅用于单节点数据库，不适用与分布式、集群场景。
    • 容易引发数据库的死锁问题，消耗性能。
• 逻辑外键（推荐）
  • 概念：在业务层逻辑中，解决外键关联。
  • 通过逻辑外键，就可以很方便的解决上述问题。

2.一对一

• 案例：用户与身份证信息的关系
• 关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率
• 实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

3.多对多

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

六、多表查询

• 多表查询：指从多张表中查询数据
• 笛卡尔积：笛卡尔乘积是指在数学中，两个集合(A集合和B集合)的所有组合情况。
• 在多表查询时，需要消除无效的笛卡尔积

• 连接查询
  • 内连接:相当于查询A、B交集部分数据
  • 外连接
    • 左外连接:查询左表所有数据(包括两张表交集部分数据)
    • 右外连接:查询右表所有数据(包括两张表交集部分数据)
• 子查询

1.内连接

• 隐式内连接：select 字段列表 from 表1,表2 where 条件…;
• 显式内连接：select 字段列表 from 表1 [ inner ] join 表2 on 连接条件…;

2.外连接

• 左外连接：select 字段列表 from 表1 left [outer] join 表2 on 连接条件…;
• 右外连接：select 字段列表 from 表1 right [outer] join 表2 on 连接条件…;

3.子查询

• 介绍：SQL语句中嵌套select语句，称为嵌套查询，又称子查询。

• 形式：select * from t1 where column1 = ( select column1 from t2…);

• 子查询外部的语句可以是insert / update / delete / select 的任何一个，最常见的是select,。

• 分类

  • 标量子查询：子查询返回的结果为单个值
  • 列子查询：子查询返回的结果为一列
  • 行子查询：子查询返回的结果为一行
  • 表子查询：子查询返回的结果为多行多列

1）标量子查询

• 子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式
• 常用的操作符：= ， <> ， >= ， < ， <=

2）列子查询

• 子查询返回的结果是一列（可以是多行)
• 常用的操作符：in . not in等

3）行子查询

• 子查询返回的结果是一行（可以是多列)。
• 常用的操作符：= 、<>、in、not in

4）表子查询

• 子查询返回的结果是多行多列，常作为临时表
• 常用的操作符：in

七、事务

1.介绍

• 事务是一组操作的集合，它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
• 默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。

2.事务控制

• 开启事务：start transaction; / begin ;
• 提交事务：commit;
• 回滚事务：rollback;

3.四大特性/ACID

• 原子性
  • 事务是不可分割的最小单元,要么全部成功，要么全部失败
• 一致性
  • 事务完成时，必须使所有的数据都保持一致状态
• 隔离性
  • 数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
• 持久性
  • 事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

八、索引

1.介绍

• 索引（index）是帮助数据库高效获取数据的数据结构。

2.优缺点

优点：

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

缺点：

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

3.结构

MySQL数据库支持的索引结构有很多，如：Hash索引、B+Tree索引、Full-Text索引等。我们平常所说的索引，如果没有特别指明，都是指默认的B+Tree结构组织的索引。

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

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

4.语法

• 创建索引
• create [ unique ] index 索引名 on 表名(字段名,…);
• 查看索引
• show index from 表名;
• 删除索引
• drop index 索引名 on 表名;

主键字段，在建表时，会自动创建主键索引。
添加唯一约束时，数据库实际上会添加唯一索引。

九、Mybatis

1.介绍

• MyBatis是一款优秀的持久层框架，用于简化JDBC的开发。

• MyBatis本是Apache的一个开源项目iBatis,2010年这个项目由apache迁移到了google code，并且改名为MyBatis 。2013年11月迁移到Github。
• 官网：https://mybatis.org/mybatis-3/zh/index.html

2.入门

1. 准备工作(创建springboot工程、数据库表user、实体类User)
   
   
   （PS：第一次创建可能比较慢）

• 创建完成，其他文件可以删除，保留如下文件
  
• 创建数据表
  

create table user
(id int unsigned not null auto_increment primary key,name varchar(100),age tinyint unsigned,gender tinyint unsigned,phone varchar(11)
);

• 创建实体类User

package com.example.springboot_mybatis_quickstart.pojo;public class User {private Integer id;private String name;private Short age;private Short gender;private String phone;public User() {}public User(Integer id, String name, Short age, Short gender, String phone) {this.id = id;this.name = name;this.age = age;this.gender = gender;this.phone = phone;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public Short getAge() {return age;}public void setAge(Short age) {this.age = age;}public Short getGender() {return gender;}public void setGender(Short gender) {this.gender = gender;}public String getPhone() {return phone;}public void setPhone(String phone) {this.phone = phone;}@Overridepublic String toString() {return "User{" +"id=" + id +", name='" + name + '\'' +", age=" + age +", gender=" + gender +", phone='" + phone + '\'' +'}';}
}

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

#配置数据库的连按信息-四要素
# 驱动类名称
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#非数据库连接的url（改成自己的数据库名字）
spring.datasource.url=jdbc:mysql://localhost:3306/mybatis
#连接致据库的用户名
spring.datasource.username=root
#连接数据库的密码（改成自己的数据库密码）
spring.datasource.password=123456

3. 编写SQL语句(注解/XML)

• 创建接口

package com.example.springboot_mybatis_quickstart.mapper;import com.example.springboot_mybatis_quickstart.pojo.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;import java.util.List;@Mapper //在运行时，会自动生成该接口的实现类对象（代理对象），并且将对象就交给IOC容器管理
public interface UserMapper {//查询全部用户信息@Select("select * from user")public List<User> list();
}

4. 测试

• 在test目录下写测试代码
  

package com.example.springboot_mybatis_quickstart;import com.example.springboot_mybatis_quickstart.mapper.UserMapper;
import com.example.springboot_mybatis_quickstart.pojo.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;import java.util.List;@SpringBootTest //springboot整合单元测试注解
class SpringbootMybatisQuickstartApplicationTests {@Autowiredprivate UserMapper userMapper;@Testpublic void testListUser(){List<User> userList = userMapper.list();userList.stream().forEach(user -> {System.out.println(user);});}}

• 运行测试程序，是否返回数据库中信息
  

3.配置SQL提示

• 默认在mybatis中编写SQL语句是不识别的。可以做如下配置：

• 如果表名红色
  

• 产生原因：ldea和数据库没有建立连接，不识别表信息

• 解决方式：在ldea中配置MySQL数据库连接

4.JDBC介绍

JDBC： (Java DataBase Connectivity )，就是使用Java语言操作关系型数据库的一套API。

驱动例如：

• sun公司官方定义的一套操作所有关系型数据库的规范，即接口。
• 各个数据库厂商去实现这套接口，提供数据库驱动jar包。
• 我们可以使用这套接口(JDBC）编程，真正执行的代码是驱动jar包中的实现类。

5.数据库连接池

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

优势：

• 资源重用
• 提升系统响应速度
• 避免数据库连接遗漏

标准接口：DataSource

• 官方(sun)提供的数据库连接池接口，由第三方组织实现此接口。
• 功能：获取连接

connection getConnection() throws SQLException;

常见产品：

Druid（德鲁伊)

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

切换Druid数据库连接池

• 官方地址：https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter
• pom.xml

  <dependency><groupld>com.alibaba</groupld><artifactld>druid-spring-boot-starter</artifactld><version>1.2.8</version>
  </dependency>
  

• application.properties

  spring.datasource.druid.url= 				#或	spring.datasource.url= 
  spring.datasource.druid.username= 			#或	spring.datasource.username=
  spring.datasource.druid.password= 			#或	spring.datasource.password=
  spring.datasource.druid.driver-class-name= 	#或 spring.datasource.driver-class-name=
  

6.lombok

Lombok是一个实用的Java类库，能通过注解的形式自动生成构造器、getter/setter、equals、hashcode、toString等方法，并可以自动化生成日志变量，简化java开发、提高效率。

• 想要使用Lombok得引入依赖

  <dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId>
  </dependency>
  

• 不需要指定版本，springboot会统一进行管理
• Lombok会在编译时，自动生成对应的java代码。我们使用lombok时，还需要安装一个lombok的插件(idea自带)。
  

十、Mybatis增删改查（CRUD）

1.删除操作

• SQL语句：

delete from emp where id = 17;

• 接口方法：

@Delete("delete from emp where id = #{id}")
public void delete(Integer id);

• 注意事项：
  如果mapper接口方法形参只有一个普通类型的参数，#{…}里面的属性名可以随便写，如:#[id}、#{value}。

2.日志输出

• 可以在application.properties中，打开mybatis的日志，并指定输出到控制台。

  #指定mybatis输出日志的位置,输出控制台
  mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
  

3.预编译SQL

• 优势：

  • 性能更高
    

  • 更安全(防止SQL注入)
    

参数占位符：

1. #{…}
   • 执行SQL时，会将#{…}替换为?，生成预编译SQL，会自动设置参数值。
   • 使用时机：参数传递，都使用#{…}
2. ${…}
   • 拼接SQL。直接将参数拼接在SQL语句中，存在SQL注入问题。
   • 使用时机：如果对表名、列表进行动态设置时使用。

4.SQL注入

• SQL注入是通过操作输入的数据来修改事先定义好的SQL语句，以达到执行代码对服务器进行攻击的方法。
  
  
  

5.新增操作

• SQL语句：

insert into emp(username, name, gender, image, job, entrydate, dept_id, create_time, update_time)
values ('songyuanqiao';'宋远桥',1, '1.jpg',2,2012-10-09',2,2022-10-0110:00:00,2022-10-0110:00:00');

• 接口方法：

@Insert("insert into emp(username, name, gender, image, job, entrydate, dept_id, create time,update _time) "+
"values(#(username),#(name}), #(gender), #(image), #job), #entrydate},#(deptld ), #(createTime), #(updateTime})")
public void insert(Emp emp);

6.新增主键返回

• 描述：在数据添加成功后，需要获取插入数据库数据的主键。
• 实现：

  @Options(keyProperty = "id", useGeneratedKeys = true)	//会自动将生成的主键值，赋值给emp对象的id属性
  @Insert("insert into emp(username, name, gender, image , job, entrydate, dept_id, create_time, update_time) " +
  "values(#(username), #(name}), #(gender}, #(image}, #[job), #(entrydate}),#(deptld), #(createTime}),#(updateTime)")
  public void insert(Emp emp);
  

7.更新操作

• SQL语句(根据ID更新员工信息)：

  update emp set username = 'songdaxia' , name = '宋大侠', gender = 1 , image = '7.jpg' , job =2, entrydate = '2012-01-01', dept_id =2, update_time = '2022-10-0112:12:12' where id = 19;
  

• 接口方法：

  @Update("update emp set username=#{username}, name=#{name}, gender=#{gender}, image=#{image}, job=#{job},entrydate=#{entrydate}, dept_id=#{deptld} , update_time=#{updateTime} where id=#{id}")
  public void update(Emp emp);
  

8.查询操作（根据ID查询）

• SQL语句：

  select * from emp where id = 19;
  

• 接口方法：

  @select("select * from emp where id = #{id}")
  public Emp getByld(Integer id);
  

9.数据封装

• 实体类属性名和数据库表查询返回的字段名一致，mybatis会自动封装。
• 如果实体类属性名和数据库表查询返回的字段名不一致，不能自动封装。

• 解决办法：
  1. 给数据库中的字段名起别名
     
  2. @Results手动映射
     
  3. 开启mybatis的驼峰命名自动映射开关（推荐）
     

10.查询操作（where条件查询）

• SQL语句：

select * from emp where name like '%张%' and gender=1 and entrydate between '2010-01-01' and '2020-01-01 ' order by update_time desc;

• 接口方法：

@Select("select * from emp where name like '%$:{name}%' and gender = #{gender} and entrydate between #{begin} and #{end} order byupdate_time desc")
public List<Emp> list(String name, Short gender , LocalDate begin , LocaiDate end);

• 参数名说明：

  • 在springBoot的2.x版本：
    

  • 在springBoot的1.x版本/单独使用mybatis：
    

十一、XML映射文件

1.XML映射文件规范

1. XML映射文件的名称与Mapper接口名称一致，并且将XML映射文件和Mapper接口放置在相同包下（同包同名)。
   

2. XML映射文件的namespace属性为Mapper接口全限定名一致。

3. XML映射文件中sql语句的id与Mapper接口中的方法名一致，并保持返回类型一致。
   

2.MybatisX

• MybatisX是一款基于IDEA的快速开发Mybatis的插件，为效率而生。
• 安装：
  

使用Mybatis的注解，主要是来完成一些简单的增删改查功能。如果需要实现复杂的SQL功能，建议使用XML来配置映射语句。

官方文档：https://mybatis.net.cn/

十一、动态SQL

随着用户的输入或外部条件的变化而变化的SQL语句，我们称为动态SQL。

1.<if>

<if>：用于判断条件是否成立。使用test属性进行条件判断，如果条件为true，则拼接SQL。

2. <where>

<where>：where元素只会在子元素有内容的情况下才插入where子句。而且会自动去除子句的开头的AND或OR。

3. <set>

<set>：动态地在行首插入SET关键字，并会删掉额外的逗号。(用在update语句中)

4. <foreach>

• SQL语句

delete from emp where id in (1,2,3);

• 接口方法

//批量删除
public void deleteBylds(List<Integer> ids);

• XML映射文件

<delete id="deleteBylds">delete from emp where id in<foreach collection="ids" item="id" separator="," open="(" close=")">#{id}</foreach>
</delete>

• 属性

  • collection：集合名称
  • item：集合遍历出来的元素/项
  • separator：每一次遍历使用的分隔符
  • open：遍历开始前拼接的片段
  • close：遍历结束后拼接的片段

• Java：
  

• XML：
  

• Test：
  

5. <sql><include>

<sql>：定义可重用的SQL片段。
<include>：通过属性refid，指定包含的sql片段。