MySQL_2

最新推荐文章于 2022-07-21 11:19:56 发布

BenBuYi

最新推荐文章于 2022-07-21 11:19:56 发布

阅读量368

点赞数

本文链接：https://blog.csdn.net/weixin_48295367/article/details/108804619

版权

5. MySQL函数

官网手册：https://dev.mysql.com/doc/refman/8.0/en/sql-function-reference.html

5.1 常用函数

-- 数学运算
select abs(-8) -- 8
select ceiling(9.4) -- 10 向上取整
select floor(9.4) -- 9 向下取整
select rand(N) -- 返回0-1之间的随机数 N种子
select sign(N) -- 返回参数符号 0 - 0 负数返回-1 正数返回1

-- 字符串函数
select char_length('字符串') -- 3 返回字符串长度
select concat('字','符','串！') -- 字符串 拼接字符串
select insert(str,pos,len,newstr)
SELECT INSERT('我是谁呀',1,2,'奥特曼')-- 奥特曼谁呀 替换字符串 从str的第pos个字符开始，len长度的字符串被替换成newstr
select instr(str,searchStr) -- 返回str中searchStr第一次出现的下标 实例：'我说行就行','行' 3
select upper('asdf你哈哈') -- 只对字母有效
select lower()
select replace('我说行就行','行','不行')-- 我说不行就不行 替换 
select reverse('反转字符串')
 
select replace(studentname,'周','邹') from student
where studentname 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（）

5.2 聚合函数（重点）

函数名称	描述
count（）	计数
sum（）	求和
avg（）	求平均
max（）	最大值
min（）	最小值

select count(studentname) from student; -- count(字段)，会忽略指定类中的NULL值
select count(*) from student; -- count（*），不会忽略null值，计算行数
select count(1) from student; -- count(1)，不会忽略所有的null值，本质：计算行数

select sum(studentresult) as '总和' from result
select avg(studentresult) as '平均分' from result
select min(studentresult) from result
select max(studentresult) from result


where -- 指定结果需要满足的条件
group by -- 指定结果按照哪几个字段分组
having -- 过滤分组的记录必须满足的次要条件
order by -- 排序
limit -- 指定从哪条到哪条

-- 查看不同课程的平均分，最高分，最低分，平均分要大于80
SELECT `subjectname`,AVG(`studentresult`),MAX(`studentresult`),MIN(`studentresult`)
FROM `result` r 
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
GROUP BY r.`subjectno` -- 通过什么字段分组
HAVING AVG(`studentresult`)>50

5.3 数据库级别的MD5加密

MD5加密是摘要算法，不论多长的原文，都会生成128bit（16字节/32位16进制数）的加密值，本身就是丢失了数据的。

要想被破解：只能通过彩虹表去反向匹配，可以通过多轮加密保证密码足够安全

CREATE TABLE `testMD5`(
	`id` INT(4) NOT NULL,
	`name` VARCHAR(20) NOT NULL,
	`pwd` VARCHAR(50) NOT NULL,
	PRIMARY KEY(`id`)
	)ENGINE = INNODB  CHARSET = utf8
	
	
INSERT INTO testMD5 VALUES(1,'wang','123456'),(2,'li','123456'),(3,'kou','123456')

UPDATE testmd5 SET pwd = MD5(pwd) WHERE id =1
UPDATE testmd5 SET pwd = MD5(pwd) -- 加密全部密码

-- 插入的时候加密
INSERT INTO testmd5 VALUES(4,'zhang',MD5('123456'))

6. 事务

要么都成功，要么都失败

1、SQL执行 A给B转账，如果转账时服务器崩溃

2、SQL执行 B收到钱，即使2号成功了，一号SQL失败了，2号也是失败

将一组SQL放进一个批次中执行

事务原则：ACID原则原子性、一致性、隔离性、持久性

原子性Atomicity：两个步骤一起成功，或者一起失败，不能分离

一致性Consistency：针对一个事务操作前与操作后状态一致（转账前后总金额不变）

持久性Durability：事务结束后数据不随外界原因导致数据丢失。（如果出现断电、宕机，事务没提交，会恢复原状，已经提交，则持久化到数据库）

隔离性Isolation：事务的隔离性时多个用户并发访问数据库时，数据库为每个用户开启的事务，不能被其他事务的操作干扰。

隔离导致的一些问题

脏读

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

不可重复读

一个事务内读取表中的某一行数据，再次读取时发现结果不同。（可能受到了其他事务的影响）

幻读

一个事务内读取到了其他事务插入的新数据

执行事务

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

-- 手动处理事务
set autocommit = 0
start transaction -- 标记一个事务的开始

insert
insert

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(3) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(20) NOT NULL,
	`money`DECIMAL(9,2) NOT NULL,
	PRIMARY KEY(`id`)
	)ENGINE = INNODB DEFAULT CHARSET = utf8
	
INSERT INTO account(`name`,`money`)
VALUES ('a',2000),('b',10000)

-- 模拟转账：事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION; -- 开启事务
UPDATE account SET money = money -500 WHERE  `name` = 'a';
UPDATE account SET money = money +500 WHERE  `name` = 'b';
COMMIT; -- 提交事务，一旦提交就被持久化了，之后rollback没作用
ROLLBACK; -- 回滚
SET autocommit = 1; -- 恢复默认值

7. 索引

http://blog.codinglabs.org/articles/theory-of-mysql-index.html

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。

提取句子主干，就可以得到索引的本质：索引是数据结构。

索引：是根据字段对所有的数据进行了重新排布，生成了一个指向源数据库的数据结构，当再次查询这个字段的某个值时，可以在数据结构中快速查找这个值，并返回实际存储的位置，去源数据库中查询

7.1 索引的分类

在一个表中，唯一索引可以有多个

主键索引 primary key
- 唯一的标识，不可重复，可以确定具体每一行的列来做主键
唯一索引 unique key
- 避免重复的列出现，唯一索引可以重复，每个列都可以标识为唯一索引，不会出现重名列
常规索引 key/index
- 默认的
全文索引 FullText
- 在特定的数据引擎下才有，MyISAM
- 快速定位数据

show index from 表名 -- 显示所有索引信息

-- 增加一个全文索引   `索引名`（`字段名/列名`）
alter table school.student add fulltext index `索引名`(`studentName`)

-- explain 分析sql执行的情况
explain select * from student; -- 非全文索引
explain select * from student where match(studentName) against*('刘')

7.2 索引测试

-- 新建表
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(20) 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 NOT 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 
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),
		'123132esw@qq.com',
		CONCAT('18',FLOOR(RAND()*999999999)),
		FLOOR(RAND()*2),
		UUID(),
		FLOOR(RAND()*100)	
	);
	SET i = i+1;
	END WHILE;
	RETURN i;
END;

SELECT mock_data(); -- 执行函数
DROP FUNCTION mock_data; -- 删除函数

truncate user_app ; -- 清空表

SELECT * FROM app_user WHERE `name` = '用户999' -- 1.724 sec
SELECT * FROM app_user WHERE `name` = '用户10009' -- 0.609 sec
SELECT * FROM app_user WHERE `name` = '用户999332'  -- 0.594 sec

创建索引方式

-- 方式一 修改表结构
-- alter table 表名 add inedx 索引名(列名)
-- alter table 表名 add unique (列名)
-- alter table 表名 add primary key(列名)
-- alter table 表名 add fulltext 索引名(列名)

-- 方式二 创建索引
-- 索引名命名习惯  id_表名_字段名
-- create index 索引名 on 表名(字段)
create index id_app_user_name ON app_user(`name`); -- 实际创建了一棵新的数,有了唯一定位，无序遍历

SELECT * FROM app_user WHERE `name` = '用户999336' -- 0 sec

有索引的情况下 rows = 1

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-b3H85mfk-1601040501923)(picture/image-20200810123908970.png)]

没有索引的情况下 row

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oo4e3dgs-1601040501925)(picture/image-20200810124214487.png)]

7.3 创建索引原则

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

索引的数据结构

Hash 类型的索引

Btree （默认innoDB索引数据结构）

8. 权限管理和备份

8.1 用户管理

SQLyog可视化管理

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YIoYAj64-1601040501926)(picture/image-20200810165007214.png)]

用户表：在mysql数据库下的user表中

本质：对这个表进行增删改

-- 创建用户 用户名 密码
create user BenBuYi identified by '123456'

-- 修改密码（当前用户密码）
set password = password('123456');

-- 修改指定用户密码
set password for BenBuYi = password('111111')

-- 删除用户
drop user BenBuYi
-- 重命名 原名 --> 新名
rename user BenBuYi to BenBu

-- 授予用户权限 授予全部权限给所有的 库.表 用户（没有grant权限）
grant all privileges on *.* to BenBuYi

-- 撤销权限 撤销所有权限 全局特权
revoke all privileges on *.* to BenBuYi

-- 查询权限
show grants for BenBuYi
show grants for root@localhost -- root用户

8.2 MySQL备份

为什么要备份：

保证重要数据不丢失
数据转移

MySQL数据库备份的方式

直接拷贝data文件夹
在SQLyog鼠标右键导出
- 在想要导出的表、数据库右键备份，选择转储SQL
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xgQwBwwJ-1601040501928)(picture/image-20200810202529783.png)]
使用命令行导出 mysqldump

-- mysqldump -h主机 -u用户名 -p密码 数据库 表名 >物理磁盘位置/文件名
-- mysqldump -h主机 -u用户名 -p密码 数据库 表名1 表名2 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student > D:/b.sql

-- 导入 （登录的情况下）
use school -- 切换指定数据库
source D:a.sql

-- 没登陆的情况
mysql -u用户名 -p密码 库名 < 备份文件

9. 规范数据库设计

9.1 为什么要设计数据库

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

糟糕的数据库设计

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

良好的数据库设计

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

软件开发中，关于数据库的设计

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

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

收集信息，分析需求
- 用户表（用户登陆注销，用户个人信息，写博客，创建分类）
- 分类表（文章分类，创建者）
- 文章表（文章信息）
- 评论表（）
- 友情链接表（友链信息）
标识实体（把需求落实到每个字段）
标识实体之间的关系
- 写博客user --> blog
- 创建分类user --> category
- 关注user --> user
- 友链 links
- 评论 user–user–blog

9.2 三大范式

为什么要信息规范化

信息重复
更新异常
插入异常
- 无法正常显示
删除异常
- 没有彻底删除所有信息

三大范式

目前数据库有六种范式，常用的有三种范式

第一范式（1NF）：数据库的每一列都是不可再分的原资数据项

如：学校信息就可以再分为学校和专业

第二范式（2NF）：一张表只描述一件事情

首先要满足第一范式为前提，非主键必须要和主键字段有直接联系

如：订单号、订单金额、订单时间应该在一张表

产品号、产品数量、产品价格应在在一张表

第三范式（3NF）：每一列属性都必须和主键属性直接相关

在满足第二范式的基础上，每一列的信息都要和主键直接关联，不能间接关联

如：学号、姓名、性别、班主任姓名、班主任性别、班主任年龄表

需要拆分为：学号、姓名、性别、班主任姓名表 + 班主任姓名、班主任性别、班主任年龄表

规范性和性能的问题

阿里规范：关联查询的表不能超过三张

考虑商业化的需求和目标（成本、用户体验）数据库的性能更重要
在规范性能的前提下，也要适当考虑规范性
有时会故意给某些表增加一些冗余字段。（从表查询变为单表查询）
有时会故意增加计算列（从大数据量计算降低为数据的查询）

10. JDBC（重点）

10.1 数据库驱动

声卡、显卡、数据库都需要驱动

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-17WgMw8w-1601040501929)(picture/image-20200811110618288.png)]

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

10.2 JDBC

为了兼容各种数据库，sun公司为了简化（对数据库的统一）操作，提供了一个（java操作数据库的）规范，俗称JDBC

这些规范的实现由具体的厂商来做

开发人员只需要，掌握调用JDBC接口的方法即可。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PKfEAmN9-1601040501930)(picture/image-20200811111336076.png)]

10.3 第一个JDBC程序

java.sql包

javax.sql包

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

创建测试数据库

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

INSERT INTO `users`(id,NAME,PASSWORD,email,birthday)
VALUES(1,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')

步骤

1.新建JDBC项目

2.新建lib目录，复制jar包导入数据库驱动，并添加到项目库中

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Qwcxv8EE-1601040501932)(picture/image-20200811114251514.png)]

3.编写测试代码

package com.buyi.lesson01;

import java.sql.*;

public class JdbcFirstDemo01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1.加载驱动
        Class.forName("com.mysql.jdbc.Driver"); //固定写法，加载驱动
        // 2. 用户信息和url
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username="root";
        String password = "123456";
        // 3.连接成功，返回数据库对象 DriverManager
        Connection connection = DriverManager.getConnection(url, username, password);
        // 4.返回执行SQL的对象 statement声明
        Statement statement = connection.createStatement();
        // 5.执行SQL的对象 执行SQL，可能存在结果，查看返回结果 execute执行 Query查询
        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();
    }
}

DriverManager

// 1.加载驱动
//register 注册
//不推荐的写法，因为Driver源文件内的静态代码块已经包含了这句话，相当于执行了两次
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver"); //固定写法，加载驱动

//connection 代表数据库对象
Connection connection = DriverManager.getConnection(url, username, password);
connection.getAutoCommit();
connection.commit();
connection.rollback();

URL

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

//mysql协议写法 端口3306 jdbc:mysql://主机地址:端口/数据库名？参数1&参数2&参数3

//oralce写法 端口1521
//   jdbc:oracle:thin:@locathost:1521:sid

Statement 执行SQL的对象

PrepareStatement 执行SQL的对象

Statement statement = connection.createStatement();

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

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

ResultSet resultSet = statement.executeQuery(sql);
resultSet.getObject(); //不知道类类型的情况下使用
resultSet.getString();
resultSet.getArray();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
//遍历
resultSet.next();//移动到下一个数据，就像指针一样
resultSet.previous();       //移动到上一行
resultSet.beforeFirst();    //移动到第一行
resultSet.afterLast();      //移动到最后一行
resultSet.absolute(int row); //移动到指定行

释放资源

//connection很耗费资源
// 6.释放链接
resultSet.close();
statement.close();
connection.close();

10.4 statement对象

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

Statement对象的exceuteUpdate方法，用于向数据库发送增、删、改的SQL语句，executeUpdate执行完后，将会返回一个整数（int型，被影响的数据库行数）

Statement.exceuteQuery方法用于向数据库发送查询语句，exceuteQuery返回一个ResultSet对象保存查询结果

CRUD操作

create 增加

statement = connection.createStatement();
String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +
    "VALUES(4,'BuYi','123456','1232412@qq.com','2020-1-1')"; //插入语句、删除语句、更新语句
int i = statement.executeUpdate(sql);
if (i > 0) {
    System.out.println("插入成功！ ");
}

delete 删除

statement = connection.createStatement();
String sql = "delete from user where id = 1"; //插入语句、删除语句、更新语句
int i = statement.executeUpdate(sql);
if (i > 0) {
    System.out.println("删除成功！ ");
}

update 更改

statement = connection.createStatement();
String sql = "update user set `name` ='赵日天' where `name` = '王大宝' "; //插入语句、删除语句、更新语句
int i = statement.executeUpdate(sql);
if (i > 0) {
    System.out.println("更新成功！ ");
}

read 读取

statement = connection.createStatement();
String sql = "select * from users where id = 1"; //查询语句
resultSet = statement.executeQuery(sql); //返回结果集
while (resultSet.next()) {
    System.out.println("NAME: " + resultSet.getString("NAME"));
}

10.5 工具类实现增删改查

文件解耦，新建一个file文件存放账户数据 db.properties

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

读取工具类

package com.buyi.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;
	//读取db.properties文件中的数据
    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");

            //1.驱动只需加载一次
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取链接方法
    public static Connection getConnection() throws SQLException {
        Connection connection = DriverManager.getConnection(url, username, password);
        return connection;
    }

    //释放资源方法
    public static void  release(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
        if (resultSet != null) {
            resultSet.close();
        }
        if (statement != null) {
            statement.close();
        }
        if (connection != null) {
            connection.close();
        }
    }
}

增删改测试程序

package com.buyi.lesson02;

import com.buyi.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 connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +
                    "VALUES(4,'BuYi','123456','1232412@qq.com','2020-1-1')"; //插入语句、删除语句、更新语句
            int i = statement.executeUpdate(sql);//返回受影响的行数
            if (i > 0) {
                System.out.println("插入成功！ ");//删除成功 更改成功
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, statement, resultSet);
        }
    }
}

查询程序

package com.buyi.lesson02;

import com.buyi.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 connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            String sql = "select * from users where id = 1"; //查询语句
            resultSet = statement.executeQuery(sql); //返回结果集
            while (resultSet.next()) {
                System.out.println("NAME: " + resultSet.getString("NAME"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, statement, resultSet);
        }
    }
}

10.6 SQL注入

SQL存在漏洞，会被攻击以及数据泄露

为了防止SQL注入漏洞，可以使用prepareStatement类

package com.buyi.lesson02;

import com.buyi.lesson02.utils.JdbcUtils;

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

public class SQL_Injection {
    public static void main(String[] args) throws SQLException {
        //login("BuYi","123456");
        //SQL注入：SQL语句拼接技巧
        login("'or '1 = 1","'or '1=1");
        //SELECT * FROM users WHERE `NAME` = '' OR 1=1 AND `password` = '' OR 1=1
        //select * from users where `NAME` = '' or '1=1 'and `password` = '' or '1=1'
        //"select * from users where `NAME` = '" + username + " ' and `password` = '" + password +"'";
    }

    //登录业务
    public static void login(String username, String password) throws SQLException {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            String sql = "select * from users where `NAME` = '" + username + " ' and `password` = '" + password +"'" ; //查询语句
            resultSet = statement.executeQuery(sql); //返回结果集
            while (resultSet.next()) {
                System.out.println("NAME: " + resultSet.getString("NAME"));
                System.out.println("password: " + resultSet.getString("PASSWORD"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, statement, resultSet);
        }
    }
}

10.7 PerparedStatement对象

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

1.新增

package com.buyi.lesson02;
import com.buyi.lesson02.utils.JdbcUtils;
import java.util.Date;
import java.sql.*;

public class TestInsert {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        PreparedStatement statement = null;

        try {
            connection = JdbcUtils.getConnection();

            //使用？ 占位符代替参数
            String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
            statement = connection.prepareStatement(sql); //预编译sql，先写sql，但不执行

            //手动给参数赋值
            statement.setInt(1,6); //插入索引 1开始，插入值
            statement.setString(2,"王菲");
            statement.setString(3,"123456");
            statement.setString(4,"3342342344@qq.com");
            //注意点：java.sql.Data Sql用的  java.sql.Date 转化时间戳
            //       java.util.Data java用的 Date().getTime() 获得时间戳
            statement.setDate(5,new java.sql.Date(new Date().getTime()));

            //填充完毕 开始执行
            int i = statement.executeUpdate();
            if (i > 0) {
                System.out.println("插入成功！ ");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, statement, null);
        }
    }
}

2.删除

package com.buyi.lesson02;

import com.buyi.lesson02.utils.JdbcUtils;
import java.sql.*;

public class TestInsert {
    public static void main(String[] args)  {
        Connection connection = null;
        PreparedStatement statement = null;

        try {
            connection = JdbcUtils.getConnection();

            //使用？ 占位符代替参数
            String sql = "delete from users where id = ?";
            statement = connection.prepareStatement(sql); //预编译sql，先写sql，但不执行

            //手动给?参数赋值
            statement.setInt(1,6); //插入索引 1开始，插入值

            //填充完毕 开始执行
            int i = statement.executeUpdate();
            if (i > 0) {
                System.out.println("删除成功！ ");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, statement, null);
        }
    }
}

3.更新

package com.buyi.lesson02;

import com.buyi.lesson02.utils.JdbcUtils;
import java.sql.*;

public class TestInsert {
    public static void main(String[] args)  {
        Connection connection = null;
        PreparedStatement statement = null;

        try {
            connection = JdbcUtils.getConnection();

            //使用？ 占位符代替参数
            String sql = "update users set `name` = ? where id = ? ";
            statement = connection.prepareStatement(sql); //预编译sql，先写sql，但不执行

            //手动给参数赋值
            statement.setString(1,"王菲"); //插入索引 1开始，插入值
            statement.setInt(2,4); //插入索引 1开始，插入值

            //填充完毕 开始执行
            int i = statement.executeUpdate();
            if (i > 0) {
                System.out.println("更新成功！ ");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, statement, null);
        }
    }
}

4.查询

package com.buyi.lesson02;

import com.buyi.lesson02.utils.JdbcUtils;
import java.sql.*;

public class TestInsert {
    public static void main(String[] args)  {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();

            //使用？ 占位符代替参数
            String sql = "select * from users where id = ?";

            statement = connection.prepareStatement(sql); //预编译sql，先写sql，但不执行

            //传递参数：手动给参数赋值
            statement.setInt(1,2); //插入索引 1开始，插入值

            //填充完毕 开始执行
            resultSet  = statement.executeQuery();
            while (resultSet.next()) {
                System.out.println(resultSet.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, statement, resultSet);
        }
    }
}

防止SQL注入

package com.buyi.lesson02;

import com.buyi.lesson02.utils.JdbcUtils;

import java.sql.*;

public class SQL_Injection {
    public static void main(String[] args) throws SQLException {
        login("''or '1 = 1'","'' or '1=1'"); //防止SQL注入
    }

    //登录业务
    public static void login(String username, String password) throws SQLException {
        Connection connection = null;

        //PreparedStatement 防止SQL注入的本质：把传递进来的参数当作字符
        //假设其中存在转义字符，如 ' 会被直接转义为 /',从而达到放SQL注入的目的
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();

            //使用？ 占位符代替参数
            String sql = "select * from users where `name` = ? and `password` = ?";

            statement = connection.prepareStatement(sql); //预编译sql，先写sql，但不执行

            //传递参数：手动给参数赋值
            statement.setString(1,username);
            statement.setString(2,password); //插入索引 1开始，插入值

            //填充完毕 开始执行
            resultSet  = statement.executeQuery();
            while (resultSet.next()) {
                System.out.println(resultSet.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, statement, resultSet);
        }
    }
}

10.8 使用IDEA连接数据库

先导入驱动文件，然后连接MySQL

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sL5q23hW-1601040501934)(picture/image-20200811221056695.png)]

导入匹配的驱动，即可解决时区不同步问题

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IjP8S7sS-1601040501935)(picture/image-20200826184421813.png)]

修改SQL数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FpltEvgH-1601040501936)(picture/image-20200811222159374.png)]

编写SQl语句

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vH00Z3YU-1601040501938)(picture/image-20200811222352078.png)]

切换数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZeyMIxzA-1601040501940)(picture/image-20200811222553455.png)]

10.9 事务

ACID原则

原子性：一起成功，一起失败

一致性：总数不变

隔离性：多个进程互不干扰

持久性：提交后不可逆

隔离性问题：

脏读：一个事务读取了另外一个没有提交的事务

不可重复读：在同一个事务内，重复读取表中的数据，表数据发生了变化

虚度：在一个事务内，读取到了别人插入的数据，导致前后读取结果不同

package com.buyi.lesson03;

import com.buyi.lesson02.utils.JdbcUtils;

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

public class TestTransation {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();
            //关闭自动提交功能,会自动开启事务
            connection.setAutoCommit(false);
            for (int i = 0; i < 2; i++) {
                String sql1 = "update `account` set `money` = `money` - 100 where `name` = 'A'";
                statement = connection.prepareStatement(sql1);
                statement.executeUpdate();

                String sql2 = "update `account` set `money` = `money` + 100 where `name` = 'B'";
                statement = connection.prepareStatement(sql2);
                statement.executeUpdate();
            }

            //业务完毕，提交事务
            connection.commit();
            System.out.println(connection.getAutoCommit());//返回当前事务提交状态
            System.out.println("操作成功！ ");

        } catch (Exception throwables) {
            try {
                //如果失败则回滚
                connection.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}
boolean autoCommit = connection.getAutoCommit();//返回当前提交状态

代码实现

开启事务 connection.setAutoCommit(false);
一组事务执行完毕，手动提交事务connection.commit();
在catch语句中显示的定义回滚语句，但不写也会默认失败回滚。connection.rollback();

10.10 数据库连接池

数据库连接 – 执行完毕 – 释放，频繁的连接–释放十分消耗系统资源

池化技术：提前准备一些连接好的资源，过来就可以直接连接使用

举例：银行的业务员，如果每次有人办业务，临时抽调业务员会很消耗时间，不如提前准备几个业务员等候。

最小连接数：

最大连接数：

排队等待：

等待超时：

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

开源数据源实现

DBCP：

C3P0：

Druid：阿里巴巴

使用了这些数据库连接池之后，项目开发过程中就不需要连接数据库的代码了。

DBCP

DBCP

#dbconfig.properites 连接设置 变量名不能更改
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
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
defaultTransactionIsolation=READ_UNCOMMITTED

导入jar包 commons-dbcp-1.4.jar commons-pool-1.6.jar 到lib目录，并重新添加入库

工具类程序

package com.buyi.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("dbconfig.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 connection, PreparedStatement statement, ResultSet resultSet) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

测试类程序

package com.buyi.lesson05;

import com.buyi.lesson02.utils.JdbcUtils;
import com.buyi.lesson05.utils.JdbcUtils_DBCP;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestDBCP {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils_DBCP.getConnection(); //仅仅是更换了数据源

            //使用？ 占位符代替参数
            String sql = "select * from users where id = ?";

            statement = connection.prepareStatement(sql); //预编译sql，先写sql，但不执行

            //传递参数：手动给参数赋值
            statement.setInt(1,2); //插入索引 1开始，插入值

            //填充完毕 开始执行
            resultSet  = statement.executeQuery();
            while (resultSet.next()) {
                System.out.println(resultSet.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, statement, resultSet);
        }
    }
}

C3P0

C3P0

配置文件

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!--
    c3p0的缺省（默认）配置
    如果在代码中"ComboPooledDataSource ds=new ComboPooledDataSource();"这样写就表示使用的是c3p0的缺省（默认）-->
    <default-config>
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&amp;characterEncoding=utf8&amp;useSSL=true</property>
        <property name="user">root</property>
        <property name="password">123456</property>

        <property name="acquiredIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </default-config>

    <!--
    c3p0的命名配置
    如果在代码中"ComboPooledDataSource ds=new ComboPooledDataSource("MySQL");"这样写就表示使用的是mysql的缺省（默认）-->
    <named-config name="MySQL">
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&amp;characterEncoding=utf8&amp;useSSL=true</property>
        <property name="user">root</property>
        <property name="password">123456</property>

        <property name="acquiredIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </named-config>
</c3p0-config

工具类

package com.buyi.lesson05.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

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

public class JdbcUtils_C3P0 {
    private static ComboPooledDataSource dataSource = null;
    static {
        try {
            //代码方式实现配置
//            dataSource = new ComboPooledDataSource();
//            dataSource.setDriverClass();
//            dataSource.setUser();
//            dataSource.setPassword();
//            dataSource.setJdbcUrl();
//            dataSource.setMaxPoolSize();
//            dataSource.setMinPoolSize();
            //创建数据源 工厂模式
            dataSource = new ComboPooledDataSource("MySQL"); //配置文件写法
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    //释放连接
    public static void release(Connection connection, PreparedStatement statement, ResultSet resultSet) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

测试类

package com.buyi.lesson05;


import com.buyi.lesson05.utils.JdbcUtils_C3P0;

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

public class TestC3P0 {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils_C3P0.getConnection(); //更换为c3p0

            //使用？ 占位符代替参数
            String sql = "select * from users where id = ?";

            statement = connection.prepareStatement(sql); //预编译sql，先写sql，但不执行

            //传递参数：手动给参数赋值
            statement.setInt(1,2); //插入索引 1开始，插入值

            //填充完毕 开始执行
            resultSet  = statement.executeQuery();
            while (resultSet.next()) {
                System.out.println(resultSet.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils_C3P0.release(connection, statement, resultSet);
        }
    }
}

结论

无论使用什么数据源，本质都是一样的，DataSource接口不会变，方法不会变。

Druid

BenBuYi

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL_2

5. MySQL函数官网手册：https://dev.mysql.com/doc/refman/8.0/en/sql-function-reference.html5.1 常用函数-- 数学运算select abs(-8) -- 8select ceiling(9.4) -- 10 向上取整select floor(9.4) -- 9 向下取整select rand(N) -- 返回0-1之间的随机数 N种子select sign(N) -- 返回参数符号 0 - 0 负数返回-1 正数返回
复制链接

扫一扫