mysql

2022-06-07 01:13:59

MySQL（二）

6、事务

=要么都成功，要么都失败
1、sql执行，a 给 b 转账
2、sql执行，b 收到 a 的钱
将一组sql放在一个批次中去执行~

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

  • 原子性（Atomicity）：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
  • 一致性（Consistency）：事务前后数据的完整性必须保持一致。
  • 隔离性（Isolation）：事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
  • 持久性（Durability）：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

隔离所导致的一些问题

  • 脏读：指一个事务读取了另外一个事务未提交的数据。
  • 不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不对。（这个不一定是错误，只是某些场合不对）
  • 虚读（幻读）：是指在一个事务内读取了别的事务插入的数据，导致前后读取不一致

事务

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

-- 手动处理事务
SET autocommint = 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 (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`)
VALUE(‘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 -- 恢复默认值

7、索引

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

7.1、索引的分类

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

  • 主键索引 （primary key）

    • 唯一的标识，主键不可重复，只能有一个列作为主键

  • 唯一索引 （unique key）

    • 避免重复的列出现，唯一索引可以重复，多个列都可以标识为 唯一索引

  • 常规索引 （key/index）

    • 默认的，可以用index或者key关键字来设置

  • 全文索引 （fulltext）

    • 在特定的数据库引擎下才有，MYISAM
    • 快速定位数据

基础语法

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

-- 显示所有的索引
SHOW INDEX FROM student

-- 增加一个索引
ALTER TABLE school.`student` ADD FULLTEXT INDEX ‘studentname‘(‘studentname‘);

-- EXPLAIN 分析SQL执行的状况
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST(‘刘‘)

7.2、测试索引

-- id _ 表名 _ 字段名
-- create index 索引名 on 表（字段）
CREATE INDEX id_app_user_name ON app_user(‘name‘)

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

7.3、索引原则

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

索引的数据结构（参考文章：https://blog.csdn.net/weichi7549/article/details/88315157）

Hash类型的索引
Btree：innodb默认的数据结构~

8.权限管理和备份

8.1、用户管理

SQLyog可视化管理
mysql

sql命定操作

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

-- 创建用户 CREATE USER 用户名 IDENTIFIED BY 密码
CREATE USER kuangshen IDENTIFIED BY ‘123456‘ -- 修改密码（修改当前用户密码）
SET PASSWORD = PASSWORD(‘111111‘) -- 修改密码（修改指定用户密码）
SET PASSWORD FOR kuangshen = PASSWORD(‘111111‘) -- 重命名 RENAME USER 原名字 TO 新名字
RENAME USER kuangshen TO kuangshen2 -- 用户授权 ALL PRIVILEGES 全部的权限，库，表
-- ALL PRIVILEGES 除了给别人授权（grant），其他都可以做
GRANT ALL PRIVILEGES ON *.* TO kuangshen2 -- 查询权限

SHOW GRANTS FOR kuanghsen2 -- 查看指定用户的权限
SHOW GRANTS FOR root @localhost -- root用户权限：GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘ WITH GRANT OPTION
-- 撤销权限 revoke 哪些权限，在哪个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* TO kuangshen2
-- 删除用户
DROP USER kuangshen

8.2、MySQL备份

为什么备份：

  • 保证重要的数据不丢失
  • 数据转移
    MySQL数据库转移方式
  • 直接拷贝物理文件
  • 在sqlyog这种可视化工具中手动导出
  • 使用命令行 MySQLdump 命令行使用
格式：#mysqldump -h主机 —u用户名 —密码 数据库 表名 > 物理磁盘位置/文件名 (命令行只有空格)
mysqldump -hlacalhost -uroot -p123456 school student >D:/a.sql

导出多张表
#mysqldump -h主机 —u用户名 —密码 数据库 表1 表2 > 物理磁盘位置/文件名

导入
尽量在登录的情况下，切换到指定的数据库（导入数据库则不用）
source 备份文件
source D:/a.sql
mysql -u用户名 -p密码 库名 < 备份文件

9.规范数据库设计

9.1、为什么需要设计

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

  • 数据冗余，浪费空间
  • 数据库插入和删除麻烦，异常平【屏蔽使用物理外键】
  • 程序的性能查

良好的数据库设计：

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

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

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

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

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

9.2、三大范式

第一范式（1NF）：

原子性：保证每一列都不可再分

第二范式（2NF）：

前提：满足第一范式
每张表只描述一件事情

第三范式（3NF）：

前提：满足第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能简介相关

（规范数据库的设计）
阿里里面规定关联查询的表不得超过三张表

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

10、JDBC（重点）

10.1、数据库驱动

10.2、JDBC

SUN公司为了简化开发人员的（对数据库统一）操作，提供了一个（Java操作数据库的）规范，俗称JDBC
这些规范的实现由具体的厂商的去做~
对于开发人员来说，我们只需要掌握JDBC接口的操作即可！
需要导入一个数据库驱动包

10.3、第一个JDBC程序

创建测试数据库

CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;

USE jdbcStudy;

CREATE TABLE users(
	id INT(3) PRIMARY KEY,
	`name` VARCHAR(40),
	`password` VARCHAR(40),
	email VARCHAR(60),
	birthday DATE
);

INSERT INTO users
VALUE (1,‘zhangsan‘,‘123456‘,‘zs@sina.com‘,‘1980-12-04‘),
(2,‘lisi‘,‘123456‘,‘ls@sina.com‘,‘1981-12-04‘),
(3,‘wangwu‘,‘123456‘,‘ww@sina.com‘,‘1982-12-04‘)

2、导入数据库驱动
3、编写测试代码

package com.lesson01;
import java.sql.*;
public class test {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1、加载驱动
        Class.forName("com.mysql.jdbc.Driver");//固定写法，记载驱动

        //2、用户信息和URL
        //useUnicode=true&characterEncoding=utf8&use=true
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&use=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("password=" + resultSet.getObject("password"));
             System.out.println("email=" + resultSet.getObject("email"));
             System.out.println("birthday=" + resultSet.getObject("birthday"));
        }
         //6、释放连接
         resultSet.close();
         statement.close();
         connection.close();
    }
}

步骤总结：
1、加载驱动
2、连接数据库DriverManager
3、获得执行SQL的对象 Statement
4、获得返回的结果集
5、释放连接

DriverManager

  DriverManager.registerDriver(new com.mysql.jdbc.Driver());
  Class.forName("com.mysql.jdbc.Driver");//固定写法，记载驱动
// connection 代表数据库
//数据库设置自动提交
//事务提交
//事务回滚
connection.rollback();
connection.commit();
connection.setAutoCommit();

URL

String url = "jdbc:mysql://localhost:3306/jdbcstudyuseUnicode=true&characterEncoding=utf8&use=true";
// mysql -- 3306
// //协议://主机地址：端口号/数据库名?参数1&参数2&参数3&

//oralce -- 1521
//jdbc:oracle:thin:@localhost:1521:sid

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

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

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

resultSet.getObject();//在不知道类型的情况下使用
//如果知道列的类型就使用指定的类型
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();

遍历，指针

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

释放资源

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

10.4、statement对象

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

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

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

CRUD操作-create
使用executeUpdate（String SQL）方法完成数据添加操作，实例操作：

Statement st = com.createStatemrnt();
String sql = "insert into user(...) values(...)";
int num = st.executeUpdate(sql);
if (num>0){
      System.out.printIn("插入成功！！！");
}

CRUD操作-delete
使用executeUpdate（String sql）方法完成数据删除操作，实例操作：

Statement st = com.createStatemrnt();
String sql = "delete from user where id=‘1’";
int num = st.executeUpdate(sql);
if (num>0){
      System.out.printIn("删除成功！！！");
}

CRUD操作-update
使用executeUpdate（String sql）方法完成数据修改操作，实例操作：

Statement st = com.createStatemrnt();
String sql = "update user set name =‘’ where name=‘’";
int num = st.executeQuery(sql);
if (num>0){
      System.out.printIn("修改成功！！！");
}

>代码实现
1、提取工具类
###10.5 PreparedStatement对象
PreparedStatement可以防止SQL注入，效率更高。

mysql

上一篇：MySql MVCC是如何实现的-MVCC多版本并发控制？


下一篇：Linux系列：解决Ubuntu或Fedora虚拟机重启网络时出现的“No such device eth0”或“Job failed”错误