MySQL4 -----事务、索引、权限管理和备份、数据库规范三大范式、JDBC、数据库连接池

2024-01-28 19:38
文章标签 三大 jdbc 管理 数据库 索引 连接池 范式 权限 备份 事务 规范 mysql4

本文主要是介绍MySQL4 -----事务、索引、权限管理和备份、数据库规范三大范式、JDBC、数据库连接池，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

目录

      • 6. 事务
        • 6.1 什么是事务？
      • 7. 索引
        • 7.1 索引的分类
        • 7.2 测试索引
        • 7.3. 索引原则
      • 8 权限管理和备份
        • 8.1 用户管理
        • 8.2 SQL备份
      • 9 规范数据库设计
          • 9.1 为什么要有数据库设计
          • 9.2 三大范式
      • 10 JDBC
          • 10.1 数据库驱动
          • 10.2 JDBC
          • 10.3 第一个JDBC程序
          • 10.4 statement 对象
          • 10.5 prepareStatement
          • 10.6 事务
          • 10.7 数据库连接池

6. 事务

6.1 什么是事务？

要么都成功，要么都失败

SQL语句1
.
.
.
SQL语句n

事务的特性：ACID原则，也伴随一些问题（脏读、幻读、）

  • 原子性(Atomicity)：要么全部执行成功，要么全部执行失败，不会只执行一部分。
  • 一致性(Consistency)：针对一个事务操作前后的状态保持一致。
  • 持久性（Durability）：事务结束后的数据不会因为外界原因导致丢失。 事务一旦提交就不可逆。
  • 隔离性(Isolation):多个事务同时运行，彼此互不干扰。

隔离导致的问题

事务的隔离级别：

  • 脏读：一个事务执行中读取到了另一个事务未提交的数据。
  • 不可重复读：事务A在已使用一个数据一次后，这时事务B进行修改该数据，接着事务A再次读取该数据，导致问题，事务A前后读取到的数据不一致。
  • 幻读：在查询方面前后搜索出来的结果不一致。

执行事务

事务流程图

 -- ========= 事务 ==========-- MySQL 默认提交事务SET autocommit = 0  -- 关闭自动提交SET autocommit = 1  -- 开启自动提交（默认开启）-- 手动处理事务SET autocommit = 0  -- 关闭自动提交 -- 开启事务START TRANSACTION -- 标记一个事务的开始-- 提交事务，进行持久化COMMIT-- 回滚事务，恢复原样ROLLBACK-- 事务结束SET autocommit = 1  -- 开启自动提交--  以下为了解SAVEPOINT 保存点名 -- 在事务中可以设置一个保存点ROLLBACK TO SAVEPOINT 保存点名 -- 可以回滚到保存点名RELEASE SAVEPOINT 保存点名 -- 可以删除保存点

模拟场景

-- 模拟
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shopINSERT INTO `account`(`name`,`money`) VALUES('小明',2000.00),
('小森',10000.00)-- 模拟 一组转账事务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; -- 回滚事务SET autocommit = 1; -- 开启自动提交

7. 索引

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

得出结论：索引是一种数据结构，作用是帮助MySQL高效获取数据。

7.1 索引的分类
  • 主键索引（PRIMARY KEY）：
    • 唯一标识，主键不可重复，只能有一个列作为主键。
  • 唯一索引（UNIQUE KEY）:
    • 避免重复的列 ，一张表里可以有多个唯一索引
  • 常规索引 (KEY/INDEX）
    • 默认值是KEY，常规值是INDEX ，可以通过key和index关键字来设置
  • 全文索引（FULLTEXT）
    • 在特定的数据库引擎中才有， 如：MyISAM
    • 快速定位数据
7.2 测试索引
-- 创建app用户表
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 DEFAULT '' COMMENT '密码',
`age` TINYINT(4) DEFAULT '0' NULL 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
BEGINDECLARE 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),'19224305@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`= '用户19999' -- 执行耗时  : 0.629 sec
EXPLAIN SELECT * FROM `app_user` WHERE `name`= '用户19999'-- 索引命名格式 ： id_表名_字段名
-- create index 索引名 on 表(`字段`)
CREATE INDEX `id_appuser_name` ON `app_user`(`name`);-- 建立索引后，执行时间。
SELECT * FROM `app_user` WHERE `name`= '用户19999' -- 执行耗时  : 0.002 secEXPLAIN SELECT * FROM `app_user` WHERE `name`= '用户19999'

索引 在数据量小的时候 效果不明显，在数据量大的时候效果十分明显。

7.3. 索引原则
  • 索引不是越多越好
  • 不要对经常变动的数据加索引
  • 小数据量的表不用加索引
  • 索引一般加在常用来查询的字段上

索引的数据类型

InnoDB的默认数据结构是B+树

8 权限管理和备份

8.1 用户管理

SQL命令

存放用户信息的表 : mysql.user

-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER swrici IDENTIFIED BY '123456'-- 修改当前用户密码
SET PASSWORD = PASSWORD('123456')-- 修改指定用户密码
SET PASSWORD FOR swrici = PASSWORD('111111')-- 重命名  RENAME USER 原名 TO 新名
RENAME USER swrici TO swrici2-- 用户授权 授予全部的权限 GRANT ALL PRIVILEGES
-- 不能给别人授权
GRANT ALL PRIVILEGES ON *.* TO swrici2-- 查看权限
SHOW GRANTS FOR swrici2   -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost-- 撤销权限 revoke 哪些权限  on 哪个库 from  给谁撤销
REVOKE ALL PRIVILEGES  ON *.* FROM swrici2-- 删除用户
DROP USER swrici2
8.2 SQL备份

为什么要备份？

  • 保证重要的数据不丢失。
  • 数据转移

MySQL数据库备份的方式：

  • 直接拷贝物理文件
  • 在SQL yog 这种可视化工具中 手动导出
  • mysqldump 在命令行中使用 ，cmd
--导出 在命令行中
-- mysqldump -h主机 -u用户名 -p密码 库名 表名 >哪个盘:/文件名.sql
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql-- mysqldump -h主机 -u用户名 -p密码 库名 表名1 表名2 表名3>哪个盘:/文件名.sql
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql-- 导入 在sql中
--source 文件路径
source d:/a.sql

9 规范数据库设计

9.1 为什么要有数据库设计

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

  • 糟糕的数据库设计：
    • 数据冗余,浪费空间。
    • 数据库插入和删除都很麻烦、异常（屏蔽使用物理外键）
    • 程序的性能差
  • 良好的数据库设计:
    • 节省空间
    • 保证了数据库的完整性
    • 方便我们开发系统

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

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

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

  • 收集信息，分析需求
    • 用户表:（用户的登录注销，用户的个人信息，写博客，创建分类）
    • 分类表：（文章分类，谁创建的）
    • 文章表：（文章的信息）
    • 友链表：（友链信息）
    • 自定义表：（系统信息，某个关键的字，或者一些主字段）
  • 标识实体 (把需求落实到每个字段)
  • 标识实体之间的关系
    • 用户写博客：user -> blog
    • 用户创建分类：user -> category
    • 用户关注用户：user -> user
    • 友链：links
    • 评论：user->user->blog
9.2 三大范式

为什么需要数据规范化？

  • 避免信息重复
  • 更新数据可能会导致一些异常
  • 插入数据可能会导致一些异常
    • 可能会有一些表能没有插入信息
  • 删除数据可能会导致一些异常
    • 存在一些表删不干净

三大范式

第一范式（1NF）

  • 保证每一列不可再分，每一列只能由一个数据，不能一列出现两个数据的情况。

第二范式（2NF）
前提：满足第一范式
每张表只描述一件事。可能存在一个主键对应多类东西的表，但是这几种类之间并没有关系，所以要把这几类东西分别划分成几个表，让他们和主键一一对应。
第三范式（3NF）
前提：满足第二范式
在一个表中不能存在 不依赖该表主键 而去 依赖其他主键 的字段

规范 和性能 的问题
阿里规范，关联查询的表不能超过三张表

  • 在考虑商业化的需求和目标，（成本，用户体验），数据库的性能更重要。
  • 在规范性能问题的时候，需要适当考虑一下 规范性
  • 有时也会故意增加一些冗余的字段，从多表查询变成了单表查询
  • 也可能会故意增加一些计算列（从大数据量变成了小数据量的查询，索引）

10 JDBC

10.1 数据库驱动

应用程序要通过数据库驱动才能访问数据库

10.2 JDBC

SUN公司为了简化操作人员（对数据库的统一）操作，提供了一个（Java操作数据库的）规范，俗称JDBC

10.3 第一个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')

编写Java程序进行连接

public class JDBCTestDemo {public static void main(String[] args) throws ClassNotFoundException, SQLException {//1. 加载驱动Class.forName("com.mysql.jdbc.Driver"); //固定写法，加载驱动//2.链接信息，用户信息和urlString url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";String username = "root";String password = "123456";//3.链接成功，获得数据库对象Connection connection = DriverManager.getConnection(url, username, password);//4.执行SQL的对象Statement statement = connection.createStatement();//5. 使用该对象去执行SQLString sql = "select * from users";ResultSet rs = statement.executeQuery(sql);while (rs != null && rs.next()) {System.out.println(rs.getString(1) + ":" + rs.getString(2) + ":" + rs.getString(3));}//6. 释放链接rs.close();statement.close();connection.close();}
}

步骤：

  1. 加载驱动
  2. 连接数据库DriverManeger
  3. 获得连接 Connection
  4. 获得执行SQL的对象Statement
  5. 执行SQL获得结果集对象ResultSet
  6. 依次释放连接（rs,state,connect）

//等同于DriverManager.registerDriver(new com.mysql.jdbc.Driver())
class.forName(“com.mysql.jdbc.Driver”);//固定写法，加载驱动

url

String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";//mysql 默认端口 3306
//jdbc:mysql://主机地址:端口号/数据库名?参数1&参数2//Oracle 默认端口： 1521
//jdbc:oracle:thin:@localhost:1521:sid

获得数据库对象

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

Statement 执行SQL的类 prepareStatement

//sql具体要看什么操作
statement.executeQuery(sql);//执行查询操作
statement.execute(sql);//执行所有操作
statement.executeUpdate(sql);//执行更新，增加，删除操作

ResultSet rs结果集对象：分装了所有的查询结果

获得指定的数据类型：

//不知道类型的时候 rs是ResultSet的实例化对象rs.getObject();
//知道具体的类型的时候rs.getString();rs.getInt();rs.getFloat();rs.getArray();

遍历

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

释放连接，因为连接很耗资源

10.4 statement 对象

jdbc中的statement对象用于向数据库发送SQL语句，对数据库进行增删改查
主要方法：
excuteQuery(),执行查询，返回一个结果集，包含了查询的结果。
exucteUpdate(),执行更新操作，返回一个int值，进行更新的条数。（更新指的是增加、删除、修改)

SQL注入的问题

SQL语句存在漏洞，被攻击

10.5 prepareStatement

prepareStatement 可以防止SQL注入，并且效率更高

  1. 新增
  2. 删除
  3. 修改
  4. 查询
10.6 事务
package com.swrici.jdbcDemo.transaction;import com.swrici.jdbcDemo.utils.JdbcUtils;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;public class TransactionDemo {public static void main(String[] args) throws SQLException {Connection connection = null;PreparedStatement preparedStatement = null;ResultSet resultSet = null;try{connection = JdbcUtils.getConnection();//关闭数据库的自动提交，自动开启事务connection.setAutoCommit(false);String sql1  = "update account set money = money - 100 where name = 'A';";preparedStatement = connection.prepareStatement(sql1);preparedStatement.executeUpdate();int a = 1/0;String sql2  = "update account set money = money + 100 where name = 'B';";preparedStatement = connection.prepareStatement(sql2);preparedStatement.executeUpdate();//业务完毕，提交事务connection.commit();System.out.println("成功");} catch (Exception e) {connection.rollback();e.printStackTrace();}finally {JdbcUtils.realase(connection,preparedStatement,resultSet);}}
}

手动执行事务的时候，要先关闭自动提交事务的按钮，然后在事务结束时要提交事务，再开启自动提交事务。

10.7 数据库连接池

数据据库的连接到释放是十分耗费资源的，如果频繁的调用数据库连接释放就会产生大量的系统开销。
池化技术，就是为了解决这样的麻烦，通过预先准备好一些资源让其连接。
最小连接数：
最大连接数：业务最高承载上限，一旦连接数超过，就让多余的进入等待。
等待超时：时间
编写连接池，只需要一个接口DataSource

开源数据库实现(拿来即用)

DBCP，需要用到的jar包

  • commons-dbcp-1.4.jar
  • commons-pool-1.6.jar

C3P0，需要用到的jar包

  • c3p0-0.9.5.2.jar
  • mchange-commons-java-0.2.15.jar

Druid:
在使用这些连接池技术之后，项目开发就中就不需要编写连接数据库的代码了。

结论： 都要实现DataSource接口，所以提供的方法不会变，进和出层面的。

这篇关于MySQL4 -----事务、索引、权限管理和备份、数据库规范三大范式、JDBC、数据库连接池的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/654590。

相关文章

详谈redis跟数据库的数据同步问题

详谈redis跟数据库的数据同步问题

《详谈redis跟数据库的数据同步问题》文章讨论了在Redis和数据库数据一致性问题上的解决方案,主要比较了先更新Redis缓存再更新数据库和先更新数据库再更新Redis缓存两种方案,文章指出,删除R... 目录一、Redis 数据库数据一致性的解决方案1.1、更新Redis缓存、删除Redis缓存的区别二
阅读更多...
oracle数据库索引失效的问题及解决

oracle数据库索引失效的问题及解决

《oracle数据库索引失效的问题及解决》本文总结了在Oracle数据库中索引失效的一些常见场景,包括使用isnull、isnotnull、!=、、、函数处理、like前置%查询以及范围索引和等值索引... 目录oracle数据库索引失效问题场景环境索引失效情况及验证结论一结论二结论三结论四结论五总结ora
阅读更多...
Redis事务与数据持久化方式

Redis事务与数据持久化方式

《Redis事务与数据持久化方式》该文档主要介绍了Redis事务和持久化机制,事务通过将多个命令打包执行,而持久化则通过快照（RDB）和追加式文件（AOF）两种方式将内存数据保存到磁盘,以防止数据丢失... 目录一、Redis 事务1.1 事务本质1.2 数据库事务与redis事务1.2.1 数据库事务1.
阅读更多...
Linux中chmod权限设置方式

Linux中chmod权限设置方式

《Linux中chmod权限设置方式》本文介绍了Linux系统中文件和目录权限的设置方法,包括chmod、chown和chgrp命令的使用,以及权限模式和符号模式的详细说明,通过这些命令,用户可以灵活... 目录设置基本权限命令：chmod1、权限介绍2、chmod命令常见用法和示例3、文件权限详解4、ch
阅读更多...
C#实现文件读写到SQLite数据库

C#实现文件读写到SQLite数据库

《C#实现文件读写到SQLite数据库》这篇文章主要为大家详细介绍了使用C#将文件读写到SQLite数据库的几种方法,文中的示例代码讲解详细,感兴趣的小伙伴可以参考一下... 目录1. 使用 BLOB 存储文件2. 存储文件路径3. 分块存储文件《文件读写到SQLite数据库China编程的方法》博客中，介绍了文
阅读更多...
Android数据库Room的实际使用过程总结

Android数据库Room的实际使用过程总结

《Android数据库Room的实际使用过程总结》这篇文章主要给大家介绍了关于Android数据库Room的实际使用过程,详细介绍了如何创建实体类、数据访问对象（DAO）和数据库抽象类,需要的朋友可以... 目录前言一、Room的基本使用1.项目配置2.创建实体类（Entity）3.创建数据访问对象（DAO
阅读更多...
SpringBoot使用minio进行文件管理的流程步骤

SpringBoot使用minio进行文件管理的流程步骤

《SpringBoot使用minio进行文件管理的流程步骤》MinIO是一个高性能的对象存储系统,兼容AmazonS3API,该软件设计用于处理非结构化数据,如图片、视频、日志文件以及备份数据等,本文... 目录一、拉取minio镜像二、创建配置文件和上传文件的目录三、启动容器四、浏览器登录 minio五、
阅读更多...
SQL Server数据库磁盘满了的解决办法

SQL Server数据库磁盘满了的解决办法

《SQLServer数据库磁盘满了的解决办法》系统再正常运行,我还在操作中,突然发现接口报错,后续所有接口都报错了,一查日志发现说是数据库磁盘满了,所以本文记录了SQLServer数据库磁盘满了的解... 目录问题解决方法删除数据库日志设置数据库日志大小问题今http://www.chinasem.cn天发
阅读更多...
Python中列表的高级索引技巧分享

Python中列表的高级索引技巧分享

《Python中列表的高级索引技巧分享》列表是Python中最常用的数据结构之一,它允许你存储多个元素,并且可以通过索引来访问这些元素,本文将带你深入了解Python列表的高级索引技巧,希望对... 目录1.基本索引2.切片3.负数索引切片4.步长5.多维列表6.列表解析7.切片赋值8.删除元素9.反转列表
阅读更多...
IDEA中的Kafka管理神器详解

IDEA中的Kafka管理神器详解

《IDEA中的Kafka管理神器详解》这款基于IDEA插件实现的Kafka管理工具,能够在本地IDE环境中直接运行,简化了设置流程,为开发者提供了更加紧密集成、高效且直观的Kafka操作体验... 目录免安装：IDEA中的Kafka管理神器！简介安装必要的插件创建 Kafka 连接第一步：创建连接第二步：选
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2