MySQL 数据库（下）
事务
什么是事务
-
A给B转账，B收到A的钱
要么都成功，要么都失败
将一组SQL放到一个批次中去执行
-
ACID原则
原子性Atomicity，要么都完成，要么都不完成
一致性Consistency，针对一个事务操作前与操作后状态一直
持久性Durability，表示事务结束后的数据不随着外界原因导致数据丢失（事务一旦提交不可逆）
隔离性Isolation，针对多个用户同时操作，主要是排除其他事务对本次事务的影响
-
隔离导致的问题
脏读：指一个事务读取了另外- -个事务未是交的数据。
不可重复读：在一个事务内读取表中的某一行数据， 多次读取结果不同。(这个不一定是错误， 只是某些场合不对)
虚读(幻读)：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
MySQL中的事务
-
用法
-- MySQL 是默认开启事务自动提交的 SET autocommit = 0 -- 关闭 SET autocommit = 1 -- 开启 -- 手动处理事务 SET autocommit = 0 -- 关闭自动提交 -- 事务开启 START TRANSACTION -- 标记一个事务的开始，从这个之后的sql都在同一个事务内 INSERT XXX INSERT XXX -- 提交：持久化（成功） 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(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; -- 恢复默认值 -
Java中操作事务
方法(){ try(){ //正常的业务代码 commit(); }catch(){ rollback(); } }
索引
MySQL官方对索引的定义为：索引(Index) 是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。
-
索引的分类
主键索引 PRIMARY KEY：唯一的标识，不可重复，只有一个列作为主键
唯一索引 UNIQUE KEY：避免重复的列（字段）出现，可以重复，多个列都可以标识为唯一索引
常规索引 KEY / INDEX：默认的
全文索引 FULLTEXT INDEX：在特定的数据库引擎下才有，快速定位数据
-
索引的使用
-- 显示所有的索引信息 SHOW INDEX FROM student -- 添加一个全文索引 索引名（列名） ALTER TABLE school.student ADD FULLTEXT INDEX `name`(`name`); -- EXPLAIN 分析sql执行的状况 EXPLAIN SELECT * FROM student; -- 非全文索引 SELECT * FROM student WHERE MATCH(`name`) AGAINST('刘')
测试索引
-
插入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`,`phone`,`password`,`age`) VALUES(CONCAT('用户',i),CONCAT('18',FLOOR(1000000000-RAND()*999999999)),UUID(),FLOOR(RAND()*100)); -- 插入语句 SET i = i+1; END WHILE; RETURN i; END; CREATE TABLE app_user( `id` INT(3) NOT NULL AUTO_INCREMENT, `name` VARCHAR(20) NOT NULL, `phone` VARCHAR(11) NOT NULL, `password` VARCHAR(50) NOT NULL, `age` INT(3) NOT NULL, PRIMARY KEY(`id`) )ENGINE=INNODB DEFAULT CHARSET=utf8; SELECT mock_data(); -
测试
SELECT * FROM app_user WHERE `name`='用户9999'; -- 将近1s才找到 CREATE INDEX id_app_user_name ON app_user(`name`); -- 添加索引，用1s多 SELECT * FROM app_user WHERE `name`='用户9999'; -- 添加索引后查询0.001s -
索引在小数据量的时候用处不大，大数据的时候效果明显变快
索引原则
-
索引不是越多越好
-
不要对经常变动数据加索引
-
小数据量的表不需要加索引
-
索引一般加在常用来查询的字段上
-
索引的数据结构
Hash类型的索引
Btree：INNODB的默认数据结构
权限管理
用户管理
-
Navicat可视化管理
-
mysql.user 用户表
对用户表进行增删改查
-- 创建用户
CREATE USER peng IDENTIFIED BY '123456';
-- 修改当前用户密码
SET PASSWORD = PASSWORD('111111');
-- 修改指定用户密码
SET PASSWORD FOR peng = PASSWORD('111111');
-- 重命名
RENAME USER peng TO pengpeng;
-- 授予权限 库.表 *.*表示全部库的全部表
GRANT ALL PRIVILEGES ON *.* TO pengpeng; -- 除了GRANT别人，其他事都能干
-- 查询权限
SHOW GRANT FOR pengpeng;
SHOW GRANT FOR root@localhost;
-- ROOT用户的权限
GRANT ALL PRIVILEGES ON *.* TO root@localhost WITH GRANT OPTION;
-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM pengpeng;
-- 删除用户
DROP USER pengpeng;
MySQL备份
-
why
-
保证重要的数据不丢失
-
数据转换
-
存起来比较小
-
-
备份方式
-
直接拷贝物理文件
-
可视化工具：导出的是SQL语句，拉过来执行就行了
-
命令行 mysqlump 导出
mysqlump -h127.0.0.1 -uroot -p123456 school [student] >D:/a.sql #导出 source D:/a.sql #导入(需要先登录)
-
规范数据库设计
为什么需要设计
数据库比较复杂的时候，我们就需要设计
- 设计
- 分析需求：分析业务和需要处理的数据库的需求
- 概要设计：设计关系图E-R图
三大范式
-
问题
信息会重复
更新会导致异常
插入异常：无法正常显示信息
删除异常：丢失有效信息
-
三大范式（规范数据库）
- 第一范式(1NF)：要求数据库表的每一列都是不可分割的原子数据项，原子性
- 第二范式(1NF)：前提，满足第一范式；每张表只描述一件事情
- 第三范式(1NF)：前提，满足第一、二范式；每一列都跟主键直接相关，不能间接相关
-
规范性 和 性能 的问题
一般地，关联查询的表不能超过三张表
- 考虑商业化的需求和目标，(成本, 用户体验! )数据库的性能更加重要
- 在规范性能的问题的时候，需要适当的考虑一下 规范性!
- 故意给某些表增加一些冗余的字段。(从多表查询中变为单表查询)
- 故意增加一-些计算列(从大数据量降低为小数据量的查询: 索引)
JDBC
数据库驱动
-
驱动：声卡，显卡，数据库
程序通过数据库驱动连接数据库
-
JDBC
为了简化开发人员的（对数据库的）操作，提供的一个（Java操作数据库的）规范
-
Java包
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
);
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')
-
创建一个普通项目
-
导入数据库驱动
- 右键项目，新建一个 lib 目录
- 把 jar 包拷贝进来
- 右键 lib，选择Add as Library...
-
编写测试代码
import java.sql.*; //我的第一个JDBC程序 public class JdbcFirstDemo { public static void main(String[] args) throws Exception { //1.加载驱动 Class.forName("com.mysql.jdbc.Driver"); //固定写法 //2.连接信息，用户信息 和 url //SSL安全 String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true"; String username = "root"; String password = "3306"; //3.连接成功，数据库对象 Connection代表数据库 Connection connection = null; connection = DriverManager.getConnection(url,username,password); //4.执行SQL的对象 Statement statement = connection.createStatement(); //5.执行SQL的对象 去执行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(); } }-
JDBC 对象
DriverManager：
Class.forName("com.mysql.jdbc.Driver"); //固定写法 加载驱动Connection：代表数据库，数据库能做的他都能做
connection.rollback(); connection.commit(); connection.setAutoCommit(); ...URL：
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";Statement：执行sql的对象
statement.executeQuery(); //查 statement.execute(); //任何sql语句 statement.executeUpdate(); //增删改ResultSet：查询结果集
resultSet.getObject(); //不知道什么类型 resultSet.getString(); resultSet.getFloat(); ... resultSet.beforeFirst(); //移动到最前面 resultSet.afterLast(); //移动到最后面 resultSet.next(); //移动到下一个数据 resultSet.previous(); //移动到前一行 resultSet.absolute(row); //移动到指定行释放资源
resultSet.close(); statement.close(); connection.close(); //耗资源，用完关闭
-
statement对象
-
Jdbc中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句即可。
-
Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sq|语句，executeUpdate执行完后， 将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)。
-
Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的ResultSet对象。
-
CRUD操作-create
使用executeUpdate(String sq|)方法完成数据添加操作，示例操作：
Statement st = conn.createStatement(); Stirng sql = "insert into user(...) values(...)"; int num = st.executeUpdate(sql); if(num>0){ System.out.println("插入成功！！"); } -
CRUD操作-delete
使用executeUpdate(String sq|)方法完成数据删除操作，示例操作：
Statement st = conn.createStatement(); Stirng sql = "delete from user where id=1"; int num = st.executeUpdate(sql); if(num>0){ System.out.println("删除成功！！"); } -
CRUD操作-update
使用executeUpdate(String sq|)方法完成数据修改操作，示例操作：
Statement st = conn.createStatement(); Stirng sql = "update user set name='pengpeng' where name='peng'"; int num = st.executeUpdate(sql); if(num>0){ System.out.println("修改成功！！"); } -
CRUD操作-read
使用executeQuery(String sq|)方法完成数据查询操作，示例操作：
Statement st = conn.createStatement(); Stirng sql = "select * from user where id=1"; ResultSet rs = st.executeQuery(sql); while(rs.next){ //根据获取列的数据类型，分别调用rs的相应方法映射到java对象中 } -
代码
- 配置
-
工具类 JbdcUtils
package 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"); //1.驱动只用加载一次 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){ if (rs!=null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (st!=null){ try { st.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn!=null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } } -
测试类
package lesson02; import 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) { Connection conn = null; Statement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); //获取数据库连接 st = conn.createStatement(); //获得执行sql的对象 String sql = "DELETE * FROM users 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); } } }
-
SQL注入的问题
sql存在漏洞，会被攻击
login(username=" ' or ' 1=1 ",password=" ' or ' 1=1 ")
PrepareStatement
-
是Statement的子类，可以防止sql注入，并且效率更高
package lesson03; import lesson02.utils.JdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; 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 user(id,`name`,`password`,`birthday`) VALUES(?,?,?,?)"; st = conn.prepareStatement(sql); //预编译sql，先写sql，然后不执行 //手动给参数赋值 st.setInt(1,2018215555); st.setString(2,"peng"); st.setString(3,"8098"); //注意点： sql.Date 数据库 java.sql.Date()转换 // util.Date Java new Date().getTime() 获得时间戳 st.setDate(4,new java.sql.Date(new Date().getTime())); //执行 int i = st.executeUpdate(); if (i>0){ System.out.println("插入成功！！"); } } catch (SQLException e) { e.printStackTrace(); } finally { JdbcUtils.release(conn,st,null); } } } -
防注入本质
传递过来的参数当作字符，有转义字符时直接忽略
IDEA连接数据库
事务
-
ACID原则
原子性，一致性，隔离性，持久性
-
隔离性的问题
脏读：一个事务读取了另一个没有提交的事务
不可重复读：在同一个事务内，重复读取表中的数据，表数据发生了改变
虛读(幻读)：在一个事务内，读取到了别人插入的数据，导致前后读出来结果不一致package lesson04; import lesson02.utils.JdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class TestTransaction { public static void main(String[] args) { 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'"; conn.prepareStatement(sql2); st.executeUpdate(); //业务完毕，提交事务 conn.commit(); System.out.println("成功！！"); } catch (SQLException e) { //conn.rollback(); //失败则回滚，可以不写，失败默认回滚 e.printStackTrace(); } finally { try { conn.setAutoCommit(true); } catch (SQLException e) { e.printStackTrace(); } JdbcUtils.release(conn,st,rs); } } }
数据库连接池
连接 - - 释放，浪费系统资源
-
池化技术
准备一些预先的资源，过来就连接预先准备好的
常用连接数 10个
最小连接数 10
最大连接数 15 业务最高承载上限
等待超时 100ms
-
编写连接池
实现一个接口 DataSource
-
开源数据源实现(拿来即用)
DBCP，C3P0，Druid
使用了这些连接池后，我们在项目开发中就不需要编写连接数据库的代码了
-
DBCP
需要用到的jar包
commons-dbcp-1.4、commons-pool-1.6
-
C3P0
需要用到的jar包
c3p0-0.9.5.5、mchange-commons-java-0.2.19
-
结论
无论使用什么数据源，本质还是一样的，DataSource接口不会变, 方法就不会变
