MySQL基础（二）

MySQL函数

常用函数

-- 常用函数
-- 数学运算
SELECT ABS(-8);  -- 绝对值
SELECT CEILING(9.4);  -- 向上取整
SELECT FLOOR(9.4);  -- 向下取整
SELECT RAND();  -- 返回一个0～1 之间的随机数
SELECT SIGN(10);  -- 判断一个数的符号 0-0  负数返回-1，正数返回 1

-- 字符串 函数
SELECT CHAR_LENGTH('即使再小的帆也能远航');  -- 字符串长度
SELECT CONCAT('我','爱','大家');  -- 拼接字符串
SELECT INSERT('我爱编程 hello world',1,2,'非常热爱'); -- 从某个位置替换某个长度
SELECT LOWER('BinZaZa');  -- 小写字母
SELECT UPPER('BinZaZa');  -- 大写字母
SELECT INSTR('BinZaZa','a');  -- 返回第一次出现的字符串的索引
SELECT REPLACE('大佬说坚持就能成功','坚持','努力');  -- 替换出现的指定字符串
SELECT SUBSTR('大佬说坚持就能成功',4,3);  -- 返回指定的字符串（源字符串，截取的位置，截取的长度）
SELECT REVERSE('大佬说坚持就能成功'); -- 反转

-- 查询姓周的同学，并把名字改成“邹”
SELECT REPLACE(`name`,'周','邹') AS "姓名"
FROM `readerinfo`
WHERE `name` LIKE '周%';

-- 时间和日期函数。（记住）
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 VERSION();

聚合函数（常用）

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

-- 聚合函数
-- 下面都能统计 表中的数据 （想查询一个表中有多少记录，就使用这个count()）
SELECT COUNT(`book_name`) FROM `bookinfo`;  -- COUNT(指定列), 会忽略所有的null
SELECT COUNT(*) FROM `readerinfo`;  -- COUNT(*), 不会忽略null 值 
SELECT COUNT(1) FROM `borrowinfo`;  -- COUNT(1)，不会忽略所有的null 值；COUNT(1) 比 count(*) 快

SELECT SUM(`balance`) AS "总和" FROM `readerinfo`;
SELECT AVG(`balance`) AS "平均" FROM `readerinfo`;
SELECT MAX(`balance`) AS "最大" FROM `readerinfo`;
SELECT MIN(`balance`) AS "最小" FROM `readerinfo`;

-- 查询不同类别书籍的评价书价，最高书架，最低书价，平均价钱大于50
-- 核心（根据不同类型书籍分组）,bi.book_name,AVG(bi.price),MAX(bi.price),MIN(bi.price)
SELECT bc.category AS "书籍类别",AVG(bi.price) AS "平均价钱",MAX(bi.price) AS "最高价",MIN(bi.price) AS "最低价"
FROM `bookinfo` AS bi
INNER JOIN `bookcategory` AS bc
ON bi.book_category_id=bc.category_id
GROUP BY bi.book_category_id;  -- 通过平均字段来分组
HAVING bi.price > 50;

数据库级别的MD5加密

MD5

一种被广泛使用的密码散列函数
不可逆，具体值的md5值是一样的

-- 测试MD5加密

CREATE TABLE `testMD5`(
	`id` INT(4) NOT NULL,
	`name` VARCHAR(20) NOT NULL,
	`pwd` VARCHAR(50) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 明文密码
INSERT INTO `testMD5` 
VALUES
(1,'zhangsan','123456'),
(2,'lisi','123456'),
(3,'wangwu','123456');

-- 加密
UPDATE `testMD5` SET pwd = MD5(pwd);  -- 加密全部的密码

-- 插入的时候加密
INSERT INTO `testMD5` 
VALUES(4,'xiaoming',MD5('654321'));

-- 如何校验
-- 将用户传递进来的密码，进行md5加密，然后对比加密后的值
SELECT * FROM `testMD5` WHERE `name`="xiaoming" AND pwd=MD5("654321");

事务

什么是事务

要么都成功，要么都失败

将一组sql放在一个批次中去执行～

事务原则：ACID原则

ACID，是指数据库管理系统（DBMS）在写入或更新资料的过程中，为保证事务（transaction）是正确可靠的，所必须具备的四个特性：原子性（atomicity，或称不可分割性）、一致性（consistency）、隔离性（isolation，又称独立性）、持久性（durability）。

事务没有提交，恢复到原状
事务已经提交，持久化到数据库

事务的隔离级别

脏读

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

不可重复读

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

虚读（幻读）

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。（一般是行影响，多了一行）

总结

原子性（atomicity，或称不可分割性）

要么都成功，要么都失败

一致性（consistency）

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

隔离性（isolation，又称独立性）

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

持久性（durability）

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

测试事务完成转账

-- MySQL是默认开启事务自动提交的
SET autocommit = 0 /* 关闭 */
SET autocommit = 1 /* 开启(默认) */

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

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

INSERT xx
INSERT xx

-- 提交：持久化（成功！）
COMMIT

-- 回滚：回到的原来的样子（失败！）
ROLLBACK

-- 事务结束
SET autocommit = 1  -- 开启自动提交

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

模拟场景

-- 转账
CREATE DATABASE `shop` CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;

-- 建表
CREATE TABLE `account`(
	`id` INT(8) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(30) NOT NULL,
	`money` DECIMAL(9,2) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `account`(`name`,`money`)
VALUES("A",2000.00),("B",10000.00);

-- 模拟转账：事务
SET autocommit = 0;  -- 关闭自动提交
START TRANSACTION; -- 开启一个事务

UPDATE `account` SET `money` = `money`-500 WHERE `name` = 'A';  -- A减500
UPDATE `account` SET `money` = `money`+500 WHERE `name` = 'B';  -- B加500

COMMIT;  -- 提交事务
ROLLBACK;  -- 回滚

SET autocommit = 1; -- 恢复默认值

索引

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个

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

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

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

-- 增加一个索引 (索引名)(列名)
ALTER TABLE `readerinfo` ADD FULLTEXT INDEX `readerName`(`name`);

-- EXPLAIN 分析sql执行情况
EXPLAIN SELECT * FROM `readerinfo`;

EXPLAIN SELECT * FROM `readerinfo` WHERE MATCH(`name`) AGAINST('刘');

测试索引

建表

CREATE TABLE `app_user`(
	`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) DEFAULT '' COMMENT '用户名称',
	`email` VARCHAR(50) NOT NULL COMMENT '用户邮箱',
	`phone` VARCHAR(50) DEFAULT '' COMMENT '手机号',
	`gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别 （0:男  1:女）',
	`password` VARCHAR(100) NOT NULL COMMENT '密码',
	`age` TINYINT(4) DEFAULT '0' COMMENT '年龄',
	`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
	`update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT 'app用户表';

插入数据

-- 插入100万数据
DELIMITER $$  -- 写函数之前必须要写，标志
CREATE FUNCTION mock_data()
RETURNS INT
NO SQL  -- 此处加上声明没有SQL语句。才能成功创建函数。
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	WHILE i<num DO
			-- 插入语句
			INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`) VALUES(CONCAT('用户',i),'475355108@qq.com',CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
			SET i = i+1;
	END WHILE;
	RETURN i;
END;

SELECT mock_data();

测试索引

-- 测试索引
SELECT * FROM `app_user` WHERE `name` = "用户99999";  -- > 时间: 0.945s
SELECT * FROM `app_user` WHERE `name` = "用户9999";  -- > 时间: 1.115s
SELECT * FROM `app_user` WHERE `name` = "用户999";  -- > 时间: 0.998s
SELECT * FROM `app_user` WHERE `name` = "用户99";  -- > 时间: 1.004s



EXPLAIN SELECT * FROM `app_user` WHERE `name` = "用户99999";  
-- rows = 992803 查了这么多行才找到

-- 创建索引
-- CREATE (FULLTEXT) INDEX 索引名 ON 表(字段)
CREATE INDEX `id_app_user_name` ON app_user(`name`);


SELECT * FROM `app_user` WHERE `name` = "用户99999";  -- > 时间: 0.004s 查询时间变短了

EXPLAIN SELECT * FROM `app_user` WHERE `name` = "用户99999";  
-- rows = 1 一行就查到了

索引在小数据量的时候，用处不大，但是在大数据的时候，区别十分明显

索引原则

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

索引的数据结构

Hash 类型的索引

Btree：InnoDB 默认的索引类型

权限管理和备份

用户管理

Navicat 操作用户权限

CREATE USER `binzaza`@`localhost` IDENTIFIED BY '123456';

GRANT Alter, Create, Delete, Drop, Grant Option, Index, Insert, Select, Show Databases, Update ON *.* TO `binzaza`@`localhost`;

SQL 命令操作

用户表：mysql.user
本质：就是对这张表进行增删改查

SELECT * FROM `user`;

-- 创建用户
-- CREATE USER 用户名 IDENTIFIED BY 密码
CREATE USER "binzaza2" IDENTIFIED BY "123456";

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

-- 修改密码 （修改指定用户密码）
SET PASSWORD FOR `binzaza2` = PASSWORD("111111");

-- 重命名
RENAME USER `binzaza2` TO `xiaozaza`;
RENAME USER `xiaozaza` TO `binzaza2`;

-- 用户授权
-- GRANT ALL PRIVILEGES ON 库.表 TO 用户
-- ALL PRIVILEGES 除了给别人授权，其他都能干
GRANT ALL PRIVILEGES ON *.* TO `binzaza2`; 

-- 查询权限
SHOW GRANTS FOR `binzaza2`;  -- 查看字段用户的权限
SHOW GRANTS FOR root@localhost;
-- GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
-- GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM `binzaza2`;

-- 删除用户
DROP USER `binzaza2`;

MySQL备份

为什么要备份数据库

保证重要的数据不丢失
数据转移 A -> B

MySQL备份的方式

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

命令转存

# mysqldump -h 主机 -u 用户名 -p密码 数据库 表1 表2 表3 > 物理磁盘位置/文件.sql
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

规范数据库设计

为什么需要设计数据库

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

糟糕的数据库设计

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

良好的数据库设计

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

关于数据库的设计

分析需求：分析业务和需要处理的数据库的需求
概要设计：设计关系图 E-R图

设计数据库的步骤（个人博客）

收集信息，分析需求
- 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
- 分类表（文章分类，谁创建的）
- 文章表（文章的信息）
- 评论表
- 友链表（友链信息）
- 自定义表（系统信息、某个关键字、或者一些主题字段） key: value
- 说说表（发表心情）
标识实体（把需求落地到每个字段）

创建库

建表

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户的唯一id',
  `username` varchar(60) NOT NULL COMMENT '用户名',
  `password` varchar(60) NOT NULL COMMENT '用户密码',
  `sex` varchar(2) NOT NULL COMMENT '性别',
  `age` int(3) DEFAULT NULL COMMENT '年龄',
  `sign` varchar(200) DEFAULT NULL COMMENT '签名',
  `open_id` varchar(1000) NOT NULL COMMENT '微信id',
  `avater` varchar(1000) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `category` (
  `id` int(10) NOT NULL COMMENT '分类id',
  `category_name` varchar(30) NOT NULL COMMENT '分类标题',
  `create_user_id` int(10) NOT NULL COMMENT '创建用户的id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `blog` (
  `id` int(10) NOT NULL COMMENT '文章的唯一标识id',
  `title` varchar(100) NOT NULL COMMENT '文章标题',
  `author` int(10) NOT NULL COMMENT '编写文章的用户',
  `category` int(10) NOT NULL COMMENT '文章分类',
  `content` text NOT NULL,
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '修改时间',
  `love` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `comment` (
  `id` int(10) NOT NULL COMMENT '评论id',
  `blog_id` int(10) NOT NULL COMMENT '所属文章',
  `user_id` int(10) NOT NULL COMMENT '评论人',
  `content` varchar(2000) NOT NULL COMMENT '评论的内容',
  `create_time` datetime NOT NULL COMMENT '评论时间',
  `user_id_parent` int(10) NOT NULL COMMENT '回复人id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `links` (
  `id` int(10) NOT NULL COMMENT '友链id',
  `links` varchar(50) NOT NULL COMMENT '网站名称',
  `bref` varchar(2000) NOT NULL COMMENT '网站链接',
  `sort` int(10) NOT NULL COMMENT '排序',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `user_follow` (
  `id` int(10) NOT NULL COMMENT '唯一标识',
  `user_id` int(10) NOT NULL COMMENT '被关注的id',
  `follow_id` int(10) NOT NULL COMMENT '关注人的id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

标识实体之间的关系
- 写博客：user -> blog
- 创建分类：user -> category
- 关注：user -> user
- 友链：links
- 评论：user - user - blog

三大范式

为什么需要数据规范化？

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

三大范式

第一范式（1NF）
原子性：保证每一列不可再分

第二范式（2NF）
前提：满足第一范式
每张表只描述一件事情

第三范式（3NF）
前提：满足第一范式和第二范式
第三范式需要确保数据表中每一列数据都和主键直接相关，而不能间接相关。

JDBC

数据库驱动

我们的程序会通过数据库驱动来和数据库打交道

JDBC

SUN公司为了简化开发人员的（对数据库的统一）操作，提供了一个（JAVA操作数据库的）规范，俗称 JDBC
这些规范的实现由厂商去做
对开发人员来说，只需要掌握JDBC接口的操作即可

导入jar包

java.sql
javax.sql
还需要导入一个数据库驱动包 mysql-connector-java-5.1.47.jar

第一个JDBC程序

创建测试数据库

CREATE DATABASE `jdbcStudy` CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `jdbcStudy`;

CREATE TABLE `users`(
	`id` INT PRIMARY KEY,
	`name` VARCHAR(40),
	`password` VARCHAR(40),
	`email` VARCHAR(60),
	`birthday` DATE
)ENGINE = INNODB DEFAULT CHARSET = utf8;

INSERT INTO `users`(`id`,`name`,`password`,`email`,`birthday`)
VALUES(1,"zhangsan","123456","zs@sina.com","1980-12-21"),
(2,"lisi","123456","lisi@sina.com","1981-12-23"),
(3,"wangwu","123456","wangwu@sina.com","1978-10-16");

下载jar包：https://downloads.mysql.com/archives/c-j/

创建一个普通项目
导入数据库驱动
编写测试代码

package com.binzaza.jdbcdemo01;

import java.sql.*;

// 第一个jdbc程序
public class JdbcTest {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        // 1、加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver"); // 固定写法，加载驱动

        // 2、用户信息和url
        String url = "jdbc:mysql://localhost:3310/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "123456";

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

        // 4、执行SQL对象 statement 执行SQL的对象
        Statement statement = connection.createStatement();

        // 5、执行SQL，可能存在结果，查看返回结果
        String sql = "SELECT * FROM users";

        ResultSet resultSet = statement.executeQuery(sql); // 返回的结果集,结果封装了我们全部的查询出来的结果

        while (resultSet.next()){
            System.out.println("id="+resultSet.getObject("id"));
            System.out.println("name="+resultSet.getObject("NAME"));
            System.out.println("pwd="+resultSet.getObject("PASSWORD"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("birth="+resultSet.getObject("birthday"));
            System.out.println("=================================");
        }

        // 6、释放连接
        resultSet.close();
        statement.close();
        connection.close();

    }
}

id=1
name=zhangsan
pwd=123456
email=zs@sina.com
birth=1980-12-21
=================================
id=2
name=lisi
pwd=123456
email=lisi@sina.com
birth=1981-12-23
=================================
id=3
name=wangwu
pwd=123456
email=wangwu@sina.com
birth=1978-10-16
=================================

Process finished with exit code 0

步骤总结

加载驱动
连接数据库DriverManager
获得执行sql的对象 Statement
获得返回的结果集
释放连接

DriverManager

// DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
Class.forName("com.mysql.cj.jdbc.Driver"); // 固定写法，加载驱动
Connection connection = DriverManager.getConnection(url,username,password);

// connection代表数据库
// 数据库设置自动提交
// 事务提交
// 事务回滚
connection.rollback();
connection.commit();
connection.setAutoCommit();

URL

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

// mysql默认端口3306
// jdbc:mysql://localhost:3310/数据库名?参数1&参数2...
// 协议://主机地址:端口号/数据库名?参数1&参数2&参数3

// oracle默认端口1521
// jdbc:oracle:thin:@localhost:1521:sid

statement 执行SQL的对象

String sql = "SELECT * FROM users"; // 编写SQL

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

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

获得指定的数据类型

resultSet.getObject(); // 在不知道类型的情况下使用

// 如果知道列的类型就使用
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDouble();
resultSet.getDate();

遍历，指针

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

释放资源

resultSet.close();
statement.close();
connection.close(); // 耗资源，用完关掉

statement 对象

代码实现

提取工具类
首先建一个db.properties文件

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456

在utils目录下封装工具类

package com.binzaza.lesson02.utils;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

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();
        }
    }

}

编写增删改方法，executeUpdate
测试插入数据,创建文件TestInsert.java

package com.binzaza.lesson02;

import com.binzaza.lesson02.utils.JdbcUtils;

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

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

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

        try {
            conn = JdbcUtils.getConnection(); // 获取数据库连接
            st = conn.createStatement(); // 获取sql的执行对象
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES(4,'binzaza','123456','475355201@qq.com','2022-01-01')";
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("插入成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

插入成功！

Process finished with exit code 0

测试删除数据，新建文件TestDelete.java

package com.binzaza.lesson02;

import com.binzaza.lesson02.utils.JdbcUtils;

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

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

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

        try {
            conn = JdbcUtils.getConnection(); // 获取数据库连接
            st = conn.createStatement(); // 获取sql的执行对象
            String sql = "DELETE FROM users WHERE id = 4";
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("删除成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

删除成功！

Process finished with exit code 0

测试更改数据，新建文件TestUpdate.java

package com.binzaza.lesson02;

import com.binzaza.lesson02.utils.JdbcUtils;

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

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

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

        try {
            conn = JdbcUtils.getConnection(); // 获取数据库连接
            st = conn.createStatement(); // 获取sql的执行对象
            String sql = "UPDATE users SET `NAME` = 'binzaza',`email` = '475355208@qq.com' WHERE id = 1";
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("更新成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

更新成功！

Process finished with exit code 0

编写查询方法，executeQuery

package com.binzaza.lesson02;

import com.binzaza.lesson02.utils.JdbcUtils;

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

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

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

        try {
            conn = JdbcUtils.getConnection(); // 获取数据库连接
            st = conn.createStatement(); // 获取sql的执行对象
            String sql = "SELECT * FROM `users` WHERE id = 1";
            rs = st.executeQuery(sql);
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

binzaza

Process finished with exit code 0

SQL注入的问题

sql存在漏洞，会被攻击导致数据泄露
例：

package com.binzaza.lesson02;

import com.binzaza.lesson02.utils.JdbcUtils;

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

public class SQLinjuction {

    public static void main(String[] args) throws SQLException {
//        login("binzaza","123456"); // 正常登录
        login(" 'or '1=1","123456"); // 非正常登录
    }

    // 登录业务
    public static void login(String username,String password) throws SQLException {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection(); // 获取数据库连接
            st = conn.createStatement(); // 获取sql的执行对象

            // SELECT * FROM `users` WHERE `NAME` = 'binzaza' AND `PASSWORD` = '123456';
            String sql = "SELECT * FROM `users` WHERE `NAME` = '"+username+"' AND `PASSWORD` = '"+password+"'";
            rs = st.executeQuery(sql);
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("PASSWORD"));
                System.out.println("=======================================");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

binzaza
123456
=======================================
lisi
123456
=======================================
wangwu
123456
=======================================

Process finished with exit code 0

PreparedStatement 对象

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

1、新增

package com.binzaza.lesson03;

import com.binzaza.lesson02.utils.JdbcUtils;

import java.sql.*;
import java.util.Date;

public class TestInsert {
    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils.getConnection();

            //区别
            // 使用 ？ 占位符代替参数
            String sql = "INSERT INTO `users`(id,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES(?,?,?,?,?)";

            st = conn.prepareStatement(sql); // 预编译，先写sql，不执行

            // 手动给参数赋值
            st.setInt(1,4); // id
            st.setString(2,"binzaza001");
            st.setString(3,"123456");
            st.setString(4,"475355478@qq.com");
            // 注意点： sql.Date 数据库
            //          util.Date  Java   new Date().getTime() 获得时间戳
            st.setDate(5,new java.sql.Date(new Date().getTime()));
            // 执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("插入成功！");
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

插入成功！
Process finished with exit code 0

2、更改

package com.binzaza.lesson03;

import com.binzaza.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestDelete {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils.getConnection();

            //区别
            // 使用 ？ 占位符代替参数
            String sql = "DELETE FROM `users` WHERE id = ?";

            st = conn.prepareStatement(sql); // 预编译，先写sql，不执行

            // 手动给参数赋值
            st.setInt(1,4);

            // 执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("删除成功！");
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

删除成功！

Process finished with exit code 0

3、删除

package com.binzaza.lesson03;

import com.binzaza.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestUpdate {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils.getConnection();

            //区别
            // 使用 ？ 占位符代替参数
            String sql = "UPDATE `users` SET `NAME` = ? WHERE id = ?";

            st = conn.prepareStatement(sql); // 预编译，先写sql，不执行

            // 手动给参数赋值
            st.setString(1,"张三");
            st.setInt(2,1);

            // 执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("更新成功！");
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

更新成功！

Process finished with exit code 0

3、查询

package com.binzaza.lesson03;

import com.binzaza.lesson02.utils.JdbcUtils;

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

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

        try {
            conn = JdbcUtils.getConnection();
            String sql = "SELECT * FROM `users` WHERE id = ?";

            st = conn.prepareStatement(sql);
            st.setInt(1,2); // 传递参数

            // 执行
            rs = st.executeQuery(); // 执行

            if (rs.next()){
                System.out.println(rs.getString("NAME"));
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

lisi

Process finished with exit code 0

防止SQL注入

package com.binzaza.lesson03;

import com.binzaza.lesson02.utils.JdbcUtils;

import java.sql.*;

public class SQLinjuction {

    public static void main(String[] args) throws SQLException {
//        login("lisi","123456"); // 正常登录
        login("'' or 1=1","123456"); // 非正常登录
    }

    // 登录业务
    public static void login(String username,String password) throws SQLException {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection(); // 获取数据库连接
            // PreparedStatement 防止SQL注入的本质，把传递进来的参数当做字符
            // 假设其中存在转义字符，会被直接转义
            String sql = "SELECT * FROM `users` WHERE `NAME` = ? AND `PASSWORD` = ?";

            st = conn.prepareStatement(sql); // 获取sql的执行对象
            st.setString(1,username);
            st.setString(2,password);

            rs = st.executeQuery();
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("PASSWORD"));
                System.out.println("=======================================");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}


Process finished with exit code 0

事务

要么都成功，要么都失败

ACID原则

原子性： 要么全部完成，要么都完成
一致性： 总数不变
隔离性：多个进程互不干扰
持久性： 一旦提交不可逆，持久化到数据库了

隔离性问题：
脏读一个事务读取了另一个没有提交的事务
不可重复读 在同一个事务内，重复读取表中的数据，表数据发生了改变
虚读（幻读） 在一个事务内，读取到了别人的数据，导致前后读出来结果不一致

准备数据

/*创建账户表*/
CREATE TABLE `account`(
	id INT PRIMARY KEY AUTO_INCREMENT,
	`NAME` VARCHAR(40),
	`money` FLOAT
);

/*插入数据*/
INSERT INTO `account`(`NAME`,`money`) 
VALUES("A",1000),("B",1000),("C",1000);

创建文件TestTransation1.java

package com.binzaza.lesson04;

import com.binzaza.lesson02.utils.JdbcUtils;

import javax.xml.transform.Result;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

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

        try {
            conn = JdbcUtils.getConnection();
            // 关闭数据库的 自动提交功能
            conn.setAutoCommit(false);

            String sql1 = "UPDATE `account` SET `money` = `money` - 100 WHERE `NAME` = 'A'";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();

            String sql2 = "UPDATE `account` SET `money` = `money` + 100 WHERE `NAME` = 'B'";
            st = conn.prepareStatement(sql2);
            st.executeUpdate();

            // 业务完毕，提交事务
            conn.commit();
            System.out.println("成功！");

        } catch (SQLException e) {
            conn.rollback(); // 如果失败则回滚事务
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

成功！

Process finished with exit code 0

数据库连接池

背景
数据库连接 --- 执行完毕 --- 释放
连接 --- 释放十分浪费系统资源

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

最小连接数
最大连接数
排队等待
等待超时

编写连接池，只需要一个接口DataSource

开源数据源实现

DBCP
C3P0
Druid: 阿里巴巴

使用了这些数据库连接池后，我们在项目开发中就需要编写连接库的代码

DBCP

需要用到的jar包
jar包下载地址：https://mvnrepository.com/
commons-dbcp-1.4.jar、commons-pool-1.6.jar
放入lib目录下

创建文件dbcpconfig.properties

# 连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456

#<!-- 初始化连接-->
initialSize=10

# 最大连接数量
maxActive=50

#<!-- 最大空闲连接-->
maxIdle=20
#<!-- 最小空闲连接-->
minIdle=5

#<!--超时等待时间以毫秒为单位 6000毫秒/1000 等于60秒-->
maxWait=60000


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

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

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

#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）
#可用值为下列之一：（详情可见javadoc）NONE,READ_UNCOMMITTED,READ_COMMITTED,REPEATABLE_READ,SERIALIZABLE_READ
defaultTransactionIsolation=READ_UNCOMMITTED

创建utils目录，目录下创建JdbcUtils_DBCP.java

package com.binzaza.lesson05.utils;

import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils_DBCP {

    private static DataSource dataSource = null;

    static {

        try {
            InputStream in = JdbcUtils_DBCP.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();
        }
    }

}

测试DBCP TestDBCP.java

package com.binzaza.lesson05;

import com.binzaza.lesson05.utils.JdbcUtils_DBCP;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

public class TestDBCP {
    public static void main(String[] args) throws SQLException {
        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils_DBCP.getConnection();

            //区别
            // 使用 ？ 占位符代替参数
            String sql = "INSERT INTO `users`(id,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES(?,?,?,?,?)";

            st = conn.prepareStatement(sql); // 预编译，先写sql，不执行

            // 手动给参数赋值
            st.setInt(1,4); // id
            st.setString(2,"binzaza001");
            st.setString(3,"123456");
            st.setString(4,"475355478@qq.com");
            // 注意点： sql.Date 数据库
            //          util.Date  Java   new Date().getTime() 获得时间戳
            st.setDate(5,new java.sql.Date(new Date().getTime()));
            // 执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("插入成功！");
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JdbcUtils_DBCP.release(conn,st,null);
        }
    }
}

插入成功！

Process finished with exit code 0

posted @ 2022-10-17 22:55 Binzichen 阅读(25) 评论(0) 编辑收藏举报

刷新页面返回顶部

Loading

超香Q的五香卤猪脚

MySQL基础（二）

MySQL函数

常用函数

聚合函数（常用）

数据库级别的MD5加密

事务

什么是事务

事务的隔离级别

测试事务完成转账

索引

索引的分类

测试索引

索引原则

权限管理和备份

用户管理

MySQL备份

规范数据库设计

为什么需要设计数据库

关于数据库的设计

三大范式

JDBC

数据库驱动

JDBC

第一个JDBC程序

statement 对象

PreparedStatement 对象

事务

数据库连接池

公告