MySQL（二）

原创

wx63c373b99113d 2023-01-19 07:05:00 博主文章分类：自学知识笔记 ©著作权

文章标签 数据库 sql bc 文章分类 运维

©著作权归作者所有：来自51CTO博客作者wx63c373b99113d的原创作品，请联系作者获取转载授权，否则将追究法律责任

文章目录

5、MySQL函数

5.1、常用函数
5.2、聚合函数
5.3、数据库级别的MD5加密

6、事务

6.1、概述
6.2、使用

7、索引

7.1、索引分类
7.2、索引原则

8、权限管理和备份

8.1、用户管理
8.2、MySQL备份

9、规范数据库设计

9.1、为什么需要设计数据库
9.2、三大范式

10、JDBC（重点）

10.1、数据库驱动
10.2、JDBC
10.3、第一个JDBC程序
10.4、statement对象
10.5、编写我们的数据库工具类
10.6、PreparedStatement对象
10.7、JDBC中的事务提交
10.8、数据库连接池

5、MySQL函数

5.1、常用函数

SELECT ABS(-3) --返回数字的绝对值
SELECT CEILING(3.5) -- 数字向上取整
SELECT FLOOR(3.5) --数字向下取证
SELECT RAND() --返回一个0~1之间的随机数
SELECT SIGN(4) --判断一个数字的符号，负数就返回-1，正数就返回1
SELECT CHAR_LENGTH() -- 返回字符串长度

SELECT LOWER('MoBian') --全部转换成小写字母
SELECT UPPER('MoBian') --全部转换成大写字母
SELECT INSTR('mobian','o') --返回第一次出现的子串的索引位置
SELECT REVERSE('mobian') --将字符串进行反转


--(常用的函数)
SELECT CURRENT_DATE() --获取当前的日期
SELECT CURDATE() --获取当前日期
SELECT NOW() --获取当前的日期加时间
SELECT LOCALTIME() --获取本地时间
SELECT SYSDATE() --获取系统时间

--获取当前状态的时间详情
SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())

SELECT SYSTEM_USER() --获取当前的系统用户
SELECT USER() --获取当前的系统用户
SELECT SYSTEM_USER() --获取当前的版本

5.2、聚合函数

函数名称	描述
COUNT()	计数
SUM()	求和
AVG()	求平均值
MAX()	最大值
MIN()	最小值

使用方法：

SELECT COUNT(`age`) FROM `student2` --计算年龄的总数
SELECT AVG(`age`) FROM `student2` --计算年龄的平均数
SELECT MAX(`age`) FROM `student2` --计算年龄的最大值
SELECT MIN(`age`) FROM `student2` --计算年龄的最小值

补充：（具体细节还有差别）

--这三个都可以查询我我们年龄的总数
SELECT COUNT(`age`) FROM `student2` --使用该方法进行查询的时候，我们会自动忽略null值
SELECT COUNT(*) FROM `student2` --不会忽略null值（本质计算行数）
SELECT COUNT(1) FROM `student2` --不会忽略null值（本质计算行数）

5.3、数据库级别的MD5加密

MD5不可逆，不过相同值得MD5是一样的

MD5解网站的原理，他的背后有一个字典，记录不同数据在MD5机密下的新值

具体的sql形式

-- 在插入数据的时候进行MD5加密
INSERT INTO `test` VALUES(1,'mobian',MD5('123456'))

-- 对插入的数据进行MD5加密
UPDATE `test` SET pwd=MD5(pwd) WHERE id = 1

6、事务

6.1、概述

要么都成功，要么都失败

A 原子性（Atomicity）

要么都成功，要么都失败

C 一致性（Consistency）

事务前后的数据完整性要保证一致

I 隔离性（Isolation）

事务的隔离性是指多个用户并发访问数据库时，数据库为每一个用户开启的事务，则不能被其他事务的操作数据所干涉，事务之间要相互隔离

隔离所导致的一些问题

脏读：

指一个事务读取了另外一个事务未提交的数据

不可重复读：

在一个事务内读取表中的某一行数据，多次读取结果不同（这个不一定是错误，只是一些场合不适用）

虚读（幻读）：

指一个事务内读取到了别的事务插入的数据，导致前后读取到的数据不一致

D 持久性（Durability）

事务一旦提交则不可逆，被持久到数据库中

6.2、使用

手动开启事务语法格式：

-- MySQL是默认开启事务自动提交的
SET autocommit = 0 -- 关闭
SET autocommit = 1 -- 开启（默认）


-- 手动处理事务
SET autocommit = 0 -- 关闭自动提交事务

-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的sql都处在同一个事务中

-- 一组sql语句
INSERT xx
UPDATE xx SET xx=xx-1 WHERE id=1
UPDATE xx SET xx=xx+1 WHERE id=1

-- 提交
COMMIT;  -- 提交数据，进行持久化

-- 回滚
ROLLBACK;  -- 回到原来的样子(如果没有提交事务，那么就可以直接回滚到那一组sql之前)

-- 事务结束
SET autocommit = 1 -- 回复默认值


-- 了解
SAVEPOINT 保存点  --设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存中
RELEASE SAVEPOINT 保存点  -- 撤销保存点

7、索引

索引（Index）是帮助MySQL高效获取数据的数据结构
提取句子主干，就可以得到索引的本质：索引是数据结构

7.1、索引分类

主键索引（PRIMARY KEY）：唯一的表示，主键不可重复，只能有一个列作为主键
唯一索引（UNIQUE KEY）：避免重复的列出现，可以重复，多个列都可以做为标识位
常规索引（KEY / INDEX）：默认的index、key关键字来设置
全文索引（FULL TEXT）：在特定的数据库引擎MyISAM下才有用，快速定位数据

基础语法：

-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2.表创建完毕后，增加索引

-- 显示所有的索引信息
SHOW INDEX FROM `student2`

-- 增加一个全文索引（索引名） 列名
-- 之前的查找方式类似于全局查找，增加索引以后类似于唯一定位的查询
ALTER TABLE `stufent2` ADD FULLTEXT INDEX `name`(`name`)

--EXPLANIN 分析sql的执行状况
EXPLANIN SELECT * FROM `student` --非全文索引
EXPLANIN SELECT * FROM `student` WHERE MATCH(name) AGAINST('mobian')

索引在小数据的时候不明显，但是数据多了就能够明显

7.2、索引原则

索引不是越多越好
不要对经常变动的数据加索引
小数据量的表，不需要添加索引
索引一般加在常用来查询的字段上

8、权限管理和备份

8.1、用户管理

1、使用可视化界面直接操作

2、使用命令行操作

具体的sql语句

-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER `mobian` IDENTIFIED BY '123456'

-- 修改密码（修改当前用户密码）
SET PASSWORD = PASSWORD('123123')

-- 修改密码（指定的用户密码）
SET PASSWORD FOR mobian = PASSWORD('123321')

-- 重命名 RENAME USER 原来名字 TO 新的名字
RENAME USER mobian TO mibianpan

-- 用户授权 ALL PRIVILEGES 全部的权限，库.表
-- ALL PRIVILEGES除了给被人授权，其他的都能干
GRANT ALL PRIVILEGES ON *.* TO mobianpan

-- 查询权限
SHOW GRANTS FOR mobianpan -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost

-- ROOT用户权限，GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION

-- 撤销权限 REVOKE （哪些权限，在哪个库撤销，给谁撤销）
REVOKE ALL PRIVILEGES ON *.* FROM mobianpan

-- 删除用户
DROP USER mobianpan

8.2、MySQL备份

作用：

保证数据的不丢失
方便数据的转移

备份方式：

直接拷贝物理文件
在可视化工具中手动导出
使用命令行导出 mysqldump

-- 导出文件
-- mysqldump -h主机名 -u用户名 -p密码 数据库 表1 [表2] [表3] >磁盘位置:/文件名
mysqldump -hlocalhost -uroot -p123456 school student2 >f:/1.sql

-- 导入文件
-- 尽量在登录的情况下，并且切换到指定的数据库中，在执行
source f:/1.sql

-- 如果想和导出文件一样的写法
mysql -u用户名 -p密码 库名 < 备份文件的位置+名字

9、规范数据库设计

9.1、为什么需要设计数据库

当我们的数据库比较复杂的时候，数据库就需要我们设计了

糟糕的数据库设计：

数据冗余，浪费空间
数据库插入和删除都会麻烦、异常（屏蔽使用物理外键）
程序的性能差

良好的数据库设计：

节省内存空间
保证数据库的完整性
方便我们开发系统

9.2、三大范式

为什么需要数据规范化？

信息重复
更新异常
插入异常：无法正常显示信息
删除异常：丢失有效的信息

三大范式

第一范式（1NF）

保证每一列的表信息都不可以再分

第二范式（2NF）

在满足第一范式的前提下，每张表只描述一件事情

第三范式（3NF）

在满足第二范式的前提下，确保每张数据表中的每一列数据都是和主键直接相关，不能间接相关

规范性和性能的取舍问题（阿里巴巴开发手册：关联查询的表不得超过三张表）：

考虑商业化的需求和目标，如果在意成本和用户体验，那么数据库的性能就更加的重要
在规范性能的问题的时候，需要适当的考虑一下规范性
实际使用中，我们会故意给某些表增加一些冗余的字段（防止我们的单表查询变成多表查询）
故意增加一些计算列（从大数据量降低未小数据的查询量：索引）

10、JDBC（重点）

10.1、数据库驱动

类似于我们的显卡驱动，声卡驱动…

数据库驱动是用来连接我们的应用程序和数据库的

10.2、JDBC

在数据库驱动和应用程序中间又加一层，成为JDBC

SUN公司为了简化开发人员对数据库的统一操作，提供了一个java操作数据库的规范，俗称JDBC。而具体的规范实现则由具体的厂商去做。

对于开发人员来说，我们只需要掌握JDBC接口的操作就可以了。

在Java中我们需要使用：

java.sql
javax.sql
数据库驱动包：mysql-connector-java-xxxx.jar

10.3、第一个JDBC程序

1、创建数据库，并添加相应的数据

2、创建一个普通的项目

3、添加数据库驱动（未来我们可以直接使用maven添加驱动）

4、编写测试代码

public class JdbcTest {
    public static void main(String[] args) throws ClassNotFoundException {
        //1.加载驱动(固定写法)
        Class.forName("com.mysql.jdbc.Driver");

        //2.获取用户的信息和url
        String url = "jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "";

        //3.连接成功后，得到数据库对象
        Connection connection = DriverManager.getConnection(url, username, password);

        //4.执行sql的对象statement，执行sql的对象
        Statement statement = connection.createStatement()

        //5.执行相应的sql语句
        String sql = "SELECT * FROM student2";

        //返回的结果集中封装了我们查询出来的全部的对象
        ResulttSet resulttSet = statement.executeQuery(sql);

        while (resultSet.next()) {
            System.out.println("id=" + resulttSet.getObject("id"));
            System.out.println("name=" + resulttSet.getObject("name"));
            System.out.println("age=" + resulttSet.getObject("age"));
        }


        //6.释放连接，后开启的先关闭
        resulttSet.close();
        statement.close();
        connection.close();

    }
}

测试类的步骤：

加载驱动
连接数据库DriverManager
获得执行sql的对象，Statement
获得返回的结果集（只有查找才有结果）
释放连接

DriverManager

//加载驱动
Class.forName("com.mysql.jdbc.Driver");

//connection代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
//数据库的提交、设置自动提交以及回滚
connection.commit();
connection.setAutoCommit();
connection.rollback();

URL

String url = "jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true";

//mysql 3306
//协议：//主机地址：端口号/数据库名?参数1&参数2&参数3

//oracle 1521(oracle里面直接是表)
//jdbc:oracle:thin:@localhost:1521:sid

Statement 执行SQL对象
PrepareStatement 执行SQL对象

String sql = "xxx";//相应的sql语句

statement.executeQuery(sql); //查询操作，返回ResultSet
statement.executeUpdate(sql); //可以查询任何的SQL
statement.execute(sql); //更新、插入、删除，都可以使用这个。返回一个受影响的行数

ResultSet 查询的结果集：封装了所有的查询结果

获得指定的数据类型：

//可以用来接受不知道什么类型的时候接受
resultSet.getObject(); 

//使用具体的类型来接受对应的数据
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate()

遍历，指针

resultSet.beforeFirst(); //移动到最前面
resultSet.afterLast(); //移动到最后面
resultSet.next(); //移动到下一个数据
resultSet.previoue(); //移动到前一行
resultSet.absolute(row); //移动到指定行

释放资源

resulttSet.close();
statement.close();
connection.close();

10.4、statement对象

jdbc中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。

statement对象的executeUpdate方法，用于向数据库发送增删改的sql语句，executeUpdate执行完后，将会返回一个整数（即增上改语句导致了数据库几行发生了变化）

statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的Result对象

10.5、编写我们的数据库工具类

由于数据库中存在大量的死代码，所以我们可以提取一个工具类，来处理这些代码。

JdbcUtils

public class JdbcUtils {

    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    static {
        try{
            //通过反射获取到我们的配置文件
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");

            Properties properties = new Properties();
            //加载我们的配置文件
            properties.load(in);

            //分别获取到对应的值
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            //加载驱动
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    //获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    //释放连接的资源
    public static void release(Connection conn, Statement st, ResultSet rs) throws SQLException {
        if(rs != null) {
            rs.close();
        }
        if(st != null) {
            st.close();
        }
        if(conn != null) {
            conn.close();
        }
    }
}

使用工具类的jdbc编写方法改进

public class JdbcTest2 {
    public static void main(String[] args) throws SQLException {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            //1.通过我们的工具了获取对应的数据库连接
            conn = JdbcUtils.getConnection();

            //2.获取SQL的执行对象
            st = conn.createStatement();

            //3.编写我们的SQL语句
            String sql = "SELECT * FROM student2";

            //4.执行我们的SQL
            st.execute(sql);

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            //5.释放我们的连接
            JdbcUtils.release(conn,st,rs);
        }
    }
}

10.6、PreparedStatement对象

SQL注入问题

sql存在漏洞，会被攻击，导致数据泄露，使用SQL拼接 ‘or’ 1=1

PreparedStatement可以防止SQL注入，效率更高

使用方式：

public class JdbcTest3 {
    public static void main(String[] args) throws SQLException {

        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            //1.通过我们的工具了获取对应的数据库连接
            conn = JdbcUtils.getConnection();

            //2.编写我们的SQL语句
            String sql = "SELECT * FROM student2 where id = ?";

            //3.预编译我们的sql
            //PrepareStatement防止SQL注入的本质，把传递进来的参数当作字符
            //如果其中存在转义字符，比如 ' 会被直接转义
            st = conn.prepareStatement(sql);

            //4.将参数逐个填入我们编写的sql语句中
            st.setInt(1,2);
            //如果还需要其他的参数，依次添加即可
            //st.setString(2,1);
            //st.setInt(3,4);


            //5.执行我们的SQL
            st.execute(sql);

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            //6.释放我们的连接
            JdbcUtils.release(conn, st, rs);
        }

    }
}

10.7、JDBC中的事务提交

代码编写：

public class JdbcTest4 {
    public static void main(String[] args) throws SQLException {

        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            //1.关闭数据库的自动提交（默认是自动开启事务）
            conn.setAutoCommit(false);

            //编写对应的sql
            String sql1 = "update account set money = money-100 where name = 'mobian'";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();

            //可以使用一个错误的语句，进行测试
            int x = 1/0;

            String sql2 = "update account set money = money-100 where name = 'pan'";
            st = conn.prepareStatement(sql2);
            st.executeUpdate();

            //2.一组业务执行完成，提交事务
            conn.commit();
        } catch (SQLException e) {
            //3.如果失败，则回滚（默认也会回滚）
            conn.rollback();
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }
    }
}

10.8、数据库连接池

池化技术：准备一些预先的资源，过来就连接预先准备好的

部分配置：

最小连接数：5

最大连接数：10

等待超时：500ms

编写连接池就是实现一个DataSource接口

目前开源的数据源实现（拿来即用）

DBCP、C3P0、Druid

1、DBCP相关的配置

需要导入相应的jar包：commons-dbcp、commons-pool

配置文件：

#连接设置，这里的名字是使用DBCP数据源中定义的，而不是我们自己的
driverClassName=com.mysql.jdbc.Driver
url="jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true";
username=root
password=

#初始化连接
initialSize = 10

#最大连接数量
maxActive=50

#最大空闲连接
maxIdle=10

#最小空闲连接
minIdle=5

#超时等待时间以ms为单位
maxWit=500

#JDBC驱动建立连接时，附带的连接属性的格式必须是[属性名=property;]
#注意："user"和"password"两个属性会被明确地传递，因此这里不需要包含他们
connectionProperties=useUnicode=true;characterEncoding=UTF8

#指定由连接池所创建的连接的自动提交（auto-commit）状态
defaultAutoCommint=true

#driver default 指定由连接池所创建的连接的只读（read-only）状态
#如果没有设置该值，则"setReadOnly"方法将不被调用。（某些驱动并不支持只读模式，如Informax）
defaultReadOnly=

#driver default指定由连接池所创建的连接的事务级别（TransactionIsolation）
defaultTransactionIsolation=READ_UNCOMMITED

修改我们的之前的工具类

public class JdbcUtils2 {

    private static DataSource dataSource;
    static {
        try{
            //通过反射获取到我们的配置文件
            InputStream in = JdbcUtils2.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(in);

            //创建数据源，工厂模式创建
            dataSource = BasicDataSourceFactory.createDataSource(properties);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    //获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();//从数据源中获取连接
    }

    //释放连接的资源
    public static void release(Connection conn, Statement st, ResultSet rs) throws SQLException {
        if(rs != null) {
            rs.close();
        }
        if(st != null) {
            st.close();
        }
        if(conn != null) {
            conn.close();
        }
    }
}

在对应的测试类中，我们只需要将工具类进行替换就行了。

2、C3P0相关的配置

导入相关的jar包

编写我们的配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<c3p0-config>
   <default-config>
       <property name="driverClass">com.mysql.jdbc.Driver</property>
       <property name="jdbcUrl">"jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true"</property>
       <property name="user">root</property>
       <property name="password"></property>
       <property name="acquireIncrement">5</property>
       <property name="initialPoolSize">10</property>
       <property name="minPoolSizze">5</property>
       <property name="maxPoolSize">20</property>
   </default-config>


    <!--C3P0的命名配置-->
    <!--我们每次使用的时候就是一组一组进行配置，然后根据name使用对应的连接池（未来我们在学习Mybatis的时候，还会看到这种配置方式）-->
    <default-config>
        <property name="MySQL">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">"jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true"</property>
        <property name="user">root</property>
        <property name="password"></property>
        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSizze">5</property>
        <property name="maxPoolSize">20</property>
    </default-config>
</c3p0-config>

修改我们的工具类

public class JdbcUtils3 {

    private static ComboPooledDataSource dataSource = null;
    static {
        try {
            //指向我们的配置文件
            dataSource = new ComboPooledDataSource("MySQL");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() throws SQLException {
        //getConnection方法是接口方法，虽然我们使用了不同得数据源，但是最原始的接口方法是没有变的
        return dataSource.getConnection();//从数据源中获取连接
    }

    //释放连接的资源
    public static void release(Connection conn, Statement st, ResultSet rs) throws SQLException {
        if (rs != null) {
            rs.close();
        }
        if (st != null) {
            st.close();
        }
        if (conn != null) {
            conn.close();
        }
    }
}

当然我们也可以不适用xml文件，直接在工具类中传参（不建议使用）

public class JdbcUtils3 {
    private static ComboPooledDataSource dataSource = null;

    static {
        try {
            //我们使用代码的形式进行配置
            dataSource = new ComboPooledDataSource();
            dataSource.setDriverClass();
            dataSource.setUser();
            dataSource.setPassword();
            dataSource.setJdbcUrl();
            dataSource.setMaxPoolSize();
            dataSource.setMinPoolSize();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() throws SQLException {
        //getConnection方法是接口方法，虽然我们使用了不同得数据源，但是最原始的接口方法是没有变的
        return dataSource.getConnection();//从数据源中获取连接
    }

    //释放连接的资源
    public static void release(Connection conn, Statement st, ResultSet rs) throws SQLException {
        if (rs != null) {
            rs.close();
        }
        if (st != null) {
            st.close();
        }
        if (conn != null) {
            conn.close();
        }
    }
}