MySQL（查缺补漏）

喝口茶

已于 2022-10-17 22:05:46 修改

阅读量444

点赞数

分类专栏： Computer 文章标签： mysql

于 2022-10-17 22:05:27 首次发布

本文链接：https://blog.csdn.net/kkoyy459/article/details/127378113

版权

Computer 专栏收录该内容

42 篇文章 1 订阅

订阅专栏

MySQL42

数据库存储引擎：定义如何存储数据、如何为存储的数据建立索引、如何更新、查询数据等技术的具体实现。

MySQL 提供了很多种存储引擎，可以直接查询

MySQL 默认的存储引擎是 InnoDB，支持事务，是其它引擎所不能比拟的

InnoDB 支持事务，支持主键自增，支持外键

XA：表示是否支持分布式事务

在这里插入图片描述

SQL：是一种编程语言，专门用来操作数据库的，实现对数据库的增删改查都是通过执行对应 SQL 语句来完成的。

MySQL常用知识点

算术运算符：在查询的过程中对结果的值进行加减乘除、比较、逻辑运算等功能
特殊运算符
- 判断数据是否为空
- 判断值是否在某个区间之内
- 判断值是否在某个特定值的集合内
- 模糊查询
  - % 可以替换为任意字符
  - _ 表示一位长度
日期函数
- 获取当前日期
```
select curdate();
```
- 获取当前时间
```
select curtime();
```
- 获取当前日期+时间
```
select now();
```
- 获取两个日期相隔的天数
```
select datediff('2022-6-23','2019-05-01');
```
- 查询某个日期之后多少天的日期是多少
```
select adddate('2022-08-22',100);
```
- 查询某个日期之前多少天的日期是多少
```
select subdate('2022-08-22',100);
```

聚合函数

统计记录数
```
select count(id) from student;
```
计算字段值之和
```
select sum(age) from student;
```
求字段的平均值
```
select avg(age) from student;
```
求字段的最大值
```
select max(age) from student;
```
求字段的最小值
```
select min(age) from student;
```

MySQL 的数据类型

整数类型：tinyint、smallint、mediumint、int、bigint

浮点型：float、double、decimal

日期类型：datetime、date、timestamp、time、year

字符串类型：char、varchar、text

二进制类型：bit、binary、varbinary、tinyblob、blob、mediumblob、longblob

数据库是通过主键去唯一标识某条数据的

外键是建立两张表之间的联系的，进行数据之间的约束

数据表之间的关系

一对一：A 表中的一条记录唯一对应 B 表中的一条记录，B 表中的一条记录唯一对应 A 表中的一条记录。比如人和身份证
一对多：A 表中的一条记录对应 B 表中的一条记录，B 表中的一条记录对应 A 表中的多条记录。比如学生和班级
多对多：A 表中的一条记录对应 B 表中的多条记录，B 表中的一条记录对应 A 表中的多条记录。比如学生和选课

MySQL43

多表关联查询

嵌套查询
连接查询
- 内连接（默认是笛卡尔积的结果）
- 外连接
  - 左连接：左表所有数据和右表满足条件的数据组成的结果
  - 右连接：右表所有数据和左表满足条件的数据组成的结果

多对多查询，需要通过中间表将两张业务表进行关联

索引是一种特殊的数据库结构，可以用来快速查询数据表中的特定记录，索引是提升数据库性能的重要方式，主要是提高查询的速度。

索引：

普通索引：不需要任何限制条件的索引，可以在任意数据类型上创建
唯一索引：索引的值必须唯一，比如主键索引
全文索引：只能创建在 text、char、varchar 数据类型上，针对文本类型数据的索引
单列索引：只对应一个字段的索引
多列索引：在一张表的多个字段上添加索引，由多个字段共同构成索引结构
空间索引：只能建立在空间数据类型（经纬度），InnoDB 不支持空间索引

索引的设计原则：

出现在 where 语句中的列可以加索引，select 后面的列不需要加索引
索引的值，尽量唯一，效率更高
不要添加过多的索引，维护成本很高

1、添加索引

create index in_name on student(name);

2、删除索引

drop index in_name on student;

事务的特性：

原子性：多条 SQL 是一个整体，不可再分割
一致性：SQL 执行前后，数据库的数据值保持一致
隔离性：一个事务的执行不能被其他事务所干扰
持久性：一个事务一旦提交，数据库中数据的改变是永久性的

视图：数据库中一张虚拟的表，允许不同用户或应用程序以不同的方式查看同一张表中的数据

创建视图

create view view_common as select id,name from user;
create view view_all as select * from user;

使用视图

select * from view_common;
select * from view_all;

删除视图

drop view view_common;
drop view view_all;

触发器：提前定义好一系列的操作，可以在对指定表进行插入、更新、删除的同时自动执行这些操作

use db01;

create table tab1(
    tab1_id varchar(11)
);
create table tab2(
    tab2_id varchar(11)
);

# 创建触发器
create trigger t_afterinsert_on_tab1
    after insert on tab1 # 给tab1表添加后去执行触发器
    for each row # 针对每一行都有效
    begin 
        insert into tab2(tab2_id) values(new.tab1_id); # 这里是要执行的操作
    end;

# 创建触发器
create trigger t_afterdelete_on_tab1
    after delete on tab1
    for each row
    begin
        delete from tab2 where tab2_id = old.tab1_id;
    end;

# 删除触发器
drop trigger t_afterdelete_on_tab1;
drop trigger t_afterinsert_on_tab1;

存储过程：存储过程是提前定义好了一组 SQL 语句的集合，存储在数据库中，用户可以直接调用这些存储过程，类似于 Java 的方法调用。

use db01;
# 创建一个存储过程
create procedure add_name(in target int) # in表示传入的参数，target是参数名，int是参数类型
begin
    declare name varchar(20); # 声明一个name
    if target = 1 then
        set name = 'MySQL';
        else
        set name = 'Java';
    end if;
    insert into user(name) values(name);
end;
# 调用存储过程
call add_name(1);

# 创建一个存储过程
create procedure count_of_user(out count_num int) # out表示返回值，count_num表示返回值名字
begin
    select count(id) into count_num from user; # 把查出的结果赋给返回值count_num
end;
# 调用存储过程
call count_of_user(@count_num);
select @count_num;

# 删除存储过程
drop procedure add_name;
drop procedure count_of_user;

MySQL44

JDBC：用 Java 语言来操作数据库的一种技术，具有通用性，无论底层数据库是什么，都可以使用 JDBC 来进行操作，而不需要修改接口，JDBC本身就是接口

Java —> JDBC —> 数据库

JDBC基本的使用：

import java.sql.*;

public class Test {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql://localhost:3306/db01?useSSL=true";
        String user = "root";
        String password = "123456";
        Connection connection = DriverManager.getConnection(url, user, password);// 获取连接对象
        String sql = "select * from student";
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);// 将执行结果返回给结果集
        while (resultSet.next()) {
            ResultSetMetaData metaData = resultSet.getMetaData();// 获取结果集的字段属性
            int columnCount = metaData.getColumnCount();// 获取字段属性的个数
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);// 获取第i个字段属性的名字
                System.out.println("字段["+columnName+"]的值是："+resultSet.getObject(columnName));
            }
            System.out.println("*****************************************");
        }
    }
}

MySQL45

数据库连接池：预先创建好一定数量的数据库连接对象，放到缓冲池中，当开发者需要连接的时候直接从缓冲池中取出使用，用完再还回到缓冲池中，这样就做到了对于资源的重复利用，提高效率。

和线程池、字符串常量池、包装类常量池都是一样的原理，只是缓冲池中存放的资源不同。

下面是使用c3p0线程池的例子

import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.SQLException;

public class JDBCUtil {

    private static ComboPooledDataSource dataSource;

    static {
        try {
            //创建连接池对象
            dataSource = new ComboPooledDataSource();
            dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");// 不同版本的驱动这里也会有所不同
            dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/db01?useSSL=true");
            dataSource.setUser("root");
            dataSource.setPassword("123456");
            dataSource.setInitialPoolSize(20);
            dataSource.setMaxPoolSize(40);
            dataSource.setMinPoolSize(2);
            dataSource.setAcquireIncrement(5);
        } catch (PropertyVetoException e) {
            e.printStackTrace();
        }
    }
	
    // 通过线程池获取连接对象
    public static Connection getConnection(){
        try {
            Connection connection = dataSource.getConnection();
            return connection;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

}

import java.sql.*;

public class Test {
    public static void main(String[] args) throws Exception {
        Connection connection = JDBCUtil.getConnection();
        String sql = "select * from student";
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);
                System.out.println("字段["+columnName+"]的值是："+resultSet.getObject(columnName));
            }
            System.out.println("*****************************************");
        }
    }
}

上面的示例中：数据源的参数直接写到 Java 程序中，如果数据源参数有变动，则需要重新修改Java代码，此时Java文件就要重新编译，导致效率低下。所以我们一般把数据源的参数内容写到配置文件中。

DBUtils：是对 JDBC 的一套封装，可以简化 CRUD 的操作，是一个轻量级小工具。能够帮助我们把数据库得到的结果集映射成对应的实体类。

我们可以尝试手写一个DBUtils，底层实现主要使用到了反射的机制。我们可以体会一下框架背后实现的原理

关键代码如下：

package com.util;

public class Test2 {
    public static void main(String[] args) throws Exception{
        MyQueryRunner<User> myQueryRunner = new MyQueryRunner<>();
        String sql = "select * from user where id=1";
        User user = myQueryRunner.query(
                JDBCUtil.getConnection(),
                sql,
                new MyBeanHandler(User.class)
        );
        System.out.println(user);
    }
}

package com.util;

import org.apache.commons.dbutils.ResultSetHandler;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MyQueryRunner<T> {

    public T query(Connection connection, String sql, ResultSetHandler<T> rsh) throws SQLException {
        //非空校验
        if (connection == null) throw new SQLException("Null connection");
        if (sql == null) throw new SQLException("Null SQL statement");
        if (rsh == null) throw new SQLException("Null ResultSetHandler");
        //创建statement
        Statement stmt = connection.createStatement();
        //执行sql
        ResultSet rs = stmt.executeQuery(sql);
        //将结果集转为目标对象
        /*
        	主要思路就三步：
			1、获取实体类的结构
        	2、获取结果集的结构
        	3、完成映射即可
        */
        Object result = rsh.handle(rs);
        //返回目标对象
        return (T)result;
    }

}

package com.util;

import org.apache.commons.dbutils.ResultSetHandler;

import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.*;

public class MyBeanHandler implements ResultSetHandler {

    private Class type;

    public MyBeanHandler(Class type) {
        this.type = type;
    }

    @Override
    public Object handle(ResultSet resultSet) throws SQLException {
        //resultSet转为type对象
        //获取实体类的结构
        Map<String, Class> properties = getProperties();
        //获取结果集的结构
        List<String> colums = getColums(resultSet);
        //完成映射并且返回即可！
        return toBean(properties, colums,resultSet);
    }

    public Object toBean(Map<String, Class> properties,List<String> colums,ResultSet resultSet){
        Object result = null;
        try {
            //创建对象
            Constructor constructor = type.getConstructor(null);
            result = constructor.newInstance(null);
            //属性赋值
            if(resultSet.next()){
                for (String colum : colums) {
                    Class aClass = properties.get(colum);
                    String name = aClass.getName();
                    Object value = null;
                    switch (name){
                        case "java.lang.Integer":
                            value = resultSet.getInt(colum);
                            break;
                        case "java.lang.String":
                            value = resultSet.getString(colum);
                            break;
                    }
                    colum = "set"+colum.substring(0, 1).toUpperCase()+colum.substring(1);
                    Method declaredMethod = type.getDeclaredMethod(colum, aClass);
                    declaredMethod.invoke(result, value);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public List<String> getColums(ResultSet resultSet){
        List<String> list = new ArrayList<>();
        try {
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                list.add(metaData.getColumnName(i));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

    public Map<String,Class> getProperties(){
        Field[] declaredFields = type.getDeclaredFields();
        Map<String,Class> map = new HashMap<>();
        for (Field declaredField : declaredFields) {
            String fieldName = declaredField.getName();
            Class<?> fieldType = declaredField.getType();
            map.put(fieldName, fieldType);
        }
        return map;
    }
}

MySQL46

Maven 提供了一种服务，可以通过配置文件的方式从远程仓库下载相应的 jar 到本地仓库，自动从本地仓库将 jar 导入工程中。

配置文件中添加我们需要的 jar，Maven 会自动进行下载导入

MyBatis中一对多的关键代码如下：

package com.southwind.entity;

import lombok.Data;

@Data
public class Student {
    private Integer id;
    private String name;
    private Class clazz;
}

package com.southwind.entity;

import lombok.Data;

import java.util.List;

@Data
public class Class {
    private Integer id;
    private String name;
    private List<Student> students;
}

下面是查询学生

<?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.southwind.mapper.StudentMapper">

    <resultMap id="studentMap" type="com.southwind.entity.Student">
        <id property="id" column="sid"></id><!--结果集的字段为主键的用id标签-->
        <result property="name" column="sname"></result>
        <!--相当于把Class类中的id和name属性封装成Class类对象传给Student类中的clazz属性-->
        <association property="clazz" javaType="com.southwind.entity.Class">
            <id property="id" column="cid"></id><!--结果集的字段为主键的用id标签-->
            <result property="name" column="cname"></result>
        </association>
    </resultMap>

    <!--结果集映射成上面的studentMap-->
    <select id="findById" resultMap="studentMap">
        select s.id sid,s.name sname,c.id cid,c.name cname from student s,class c where s.cid = c.id and s.id = #{id}
    </select>

</mapper>

下面是查询班级

<?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.southwind.mapper.ClassMapper">

    <resultMap id="classMap" type="com.southwind.entity.Class">
        <id property="id" column="cid"></id>
        <result property="name" column="cname"></result>
        <collection property="students" ofType="com.southwind.entity.Student">
            <id property="id" column="sid"></id>
            <result property="name" column="sname"></result>
        </collection>
    </resultMap>

    <select id="findById" resultMap="classMap" parameterType="java.lang.Integer"><!--parameterType这是参数类型，可以省略不写-->
        select s.id sid,s.name sname,c.id cid,c.name cname from student s,class c where s.cid = c.id and c.id = #{id}
    </select>

</mapper>

关键思路就是：如何把结果集映射为对应的实体类

MySQL47

MyBatis的延迟加载：多表关联查询的时候，一种优化机制。（本质上就是把级联查询转换成多个单表的查询。根据需要决定执行的sql语句次数）

MyBatis的延迟加载关键实现代码如下：（相当于嵌套查询）

<?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.southwind.mapper.StudentMapper">

    <resultMap id="studetnMap" type="com.southwind.entity.Student">
        <id property="id" column="id"></id>
        <result property="name" column="name"></result>
        <association property="clazz" javaType="com.southwind.entity.Class" select="com.southwind.mapper.ClassMapper.findById" column="cid"></association>
    </resultMap>

    <select id="findById" resultMap="studetnMap">
        select * from student where id = #{id}
    </select>

</mapper>

<?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.southwind.mapper.ClassMapper">

    <select id="findById" resultType="com.southwind.entity.Class">
        select * from class where id = #{id}
    </select>

</mapper>

在MyBatis核心配置文件中添加如下配置开启MyBatis的延迟加载

<settings>
    <setting name="logImpl" value="STDOUT_LOGGING" /><!--开启打印日志-->
    <setting name="lazyLoadingEnabled" value="true"/><!--开启MyBatis的延迟加载-->
</settings>

一级缓存：自带，必须使用同一个 SqlSession

二级缓存：需要手动开启的

1、在MyBatis核心配置文件中开启二级缓存

<settings>
    <setting name="logImpl" value="STDOUT_LOGGING" />
    <setting name="lazyLoadingEnabled" value="true"/>
    <setting name="cacheEnabled" value="true"/><!--开启二级缓存-->
</settings>

2、在对应的 Mapper.xml 中配置二级缓存

<cache></cache>

3、实体类需要实现序列化接口

package com.southwind.entity;

import lombok.Data;

import java.io.Serializable;
import java.util.List;

@Data
public class Class implements Serializable {
    private Integer id;
    private String name;
    private List<Student> students;
}

4、必须关闭 sqlsession，重新赋值

import com.southwind.entity.Class;
import com.southwind.entity.Course;
import com.southwind.entity.Student;
import com.southwind.mapper.AccountMapper;
import com.southwind.mapper.ClassMapper;
import com.southwind.mapper.CourseMapper;
import com.southwind.mapper.StudentMapper;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.InputStream;

public class Test {
    public static void main(String[] args) {
        //加载MyBatis环境
        InputStream inputStream = Test.class.getClassLoader().getResourceAsStream("mybatis-config.xml");
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(inputStream);
        SqlSession sqlSession = factory.openSession();
        //调用接口
        ClassMapper classMapper = sqlSession.getMapper(ClassMapper.class);
        Class aClass = classMapper.findById(1);
        System.out.println(aClass);
        sqlSession.close();
        sqlSession = factory.openSession();
        classMapper = sqlSession.getMapper(ClassMapper.class);
        Class aClass1 = classMapper.findById(1);
        System.out.println(aClass1);
    }
}