MYSQL数据库——事务—索引—三大范式—JDBC

最新推荐文章于 2024-09-20 21:26:51 发布

追JAVA的小菜鸟

最新推荐文章于 2024-09-20 21:26:51 发布

阅读量252

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_45673367/article/details/113524236

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

事务

事务原则：ACID

原子性（Atomicity）：一起成功一起失败
一致性（Consistency）：事务券后的数据完整性要保持一致
持久性（Durability）：事务一旦提交，就会被持久化到数据库中
隔离性（Isolation）：事务的隔离性是多个用户并发访问数据库时。数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离

隔离所导致的问题：

脏读：一个事务读取了另一个事务未提交的数据

不可重复读：多次读取同一条数据出现结果不同

虚读：指一个事务读取到别的事务插入的数据，导致前后读取结果不一致

-- 创建一个数据库
CREATE DATABASE if NOT EXISTS shop
use shop
-- 创建一个表
CREATE TABLE IF NOT EXISTS bank(
	`id` INT(20) NOT NULL auto_increment,
	`name` VARCHAR(30) NOT NULL,
	`money` DECIMAL(9,2) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET = utf8
-- 注入数据
INSERT bank VALUES(1,'A',5000),(2,'B',10000)
-- 关闭自动提交
SET autocommit = 0
-- 开启事务
START TRANSACTION
-- 转账
UPDATE bank SET money = money-500 WHERE id = 1
UPDATE bank SET money = money+500 WHERE id = 2
-- 提交事务
COMMIT
-- 失败则回滚
ROLLBACK
-- 恢复默认值
SET autocommit = 1

索引

概念：（Index）帮助MySQL高效获取数据的数据结构

索引的分类

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

主键索引——primary key
- 唯一的标识，主键不可重复，只能有一个列作为主键
唯一索引——unique key
- 避免重复列，唯一索引可以重复
常规索引——key/index
- 默认的，index，key关键字
全文索引——full text
- 在特定的数据库引擎下才有
- 快速定位数据

-- 显示所有索引信息
SHOW INDEX FROM student
-- 增加一个全文索引
ALTER TABLE schooldemo.student ADD FULLTEXT studentName(`studentName`)

索引的原则

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

数据库设计

糟糕的数据库设计：

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

良好的数据库设计：

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

三大范式

———— 为什么需要数据规范化？
———— 信息重复更新异常插入异常删除异常

第一范式（1NF）

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

第二范式（2NF）

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

第三范式（3NF）

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

规范性和性能的问题

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

JDBC

概念：SUN 公司为了简化开发人员的（对数据库的统一）操作，提供了一个（Java操作数据库的）规范

第一个JDBC代码——步骤：

加载驱动
用户信息和URL
连接数据库
获得执行sql语句的statement
执行sql语句，获得结果集遍历
释放连接

import java.sql.*;
public class JDBCdemo01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1. 加载驱动（固定写法）
        Class.forName("com.mysql.jdbc.Driver");
        // 2. 用户信息和URL
        /* useUnicode——支持中文编码  characterEncoding——设定中文字符集   useSSL——使用安全连接*/
				        //（固定写法）
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true"; 
        String username = "root";
        String password = "zy123456";
        // 3. 连接数据库
        Connection connection = DriverManager.getConnection(url, username, password);
        // 4. 获得执行sql的对象statement
        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"));
            System.out.println("=======================================");
        }
        // 6. 释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

创建配置文件——db.properties

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

创建工具类——JDBCutils

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();
        }
    }
    // 3. 获得connection连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }
    // 6. 释放资源
    public static void release(Connection con, Statement st, ResultSet re){
        if(con != null){
            try {
                con.close();
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
        } if(st != null){
            try {
                st.close();
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
        } if(re != null){
            try {
                re.close();
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

编写增删改查代码

增

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class insertDemo {
    public static void main(String[] args) throws SQLException {
        JDBCutils jdbCutils = new JDBCutils();
        Connection con = JDBCutils.getConnection();
        Statement st = con.createStatement();
        String sql = " INSERT INTO users VALUES(4,'小花',123456,'xiaohua@qq.com',null)";
        int i = st.executeUpdate(sql);
        if (i!=0){
            System.out.println("插入成功！");
        }
        jdbCutils.release(con,st,null);
    }
}

删

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class deleteDemo {
    public static void main(String[] args) throws SQLException {
        JDBCutils jdbCutils = new JDBCutils();
        Connection con = jdbCutils.getConnection();
        Statement st = con.createStatement();
        String sql = "DELETE FROM users WHERE id = 4";
        int i = st.executeUpdate(sql);
        if (i!=0){
            System.out.println("删除成功！");
        }
        jdbCutils.release(con,st,null);
    }
}

改

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class updataDemo {
    public static void main(String[] args) throws SQLException {
        JDBCutils jdbCutils = new JDBCutils();
        Connection con = jdbCutils.getConnection();
        Statement st = con.createStatement();
        String sql = "UPDATE users SET password = 666666 WHERE id = 4";
        int i = st.executeUpdate(sql);
        if (i!=0){
            System.out.println("修改成功！");
        }
        jdbCutils.release(con,st,null);
    }
}

查

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class selectDemo {
    public static void main(String[] args) throws SQLException {
        JDBCutils jdbCutils = new JDBCutils();
        Connection con = jdbCutils.getConnection();
        Statement st = con.createStatement();
        String sql = "SELECT NAME FROM users";
        ResultSet rs = st.executeQuery(sql);
        while (rs.next()){
            System.out.println(rs.getString("NAME"));
        }
        jdbCutils.release(con,st,rs);
    }
}

sql注入问题

为了防止sql注入的问题进行盗取信息，改用PreparedStatement 防止注入

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class sqlzhuri {
    public static void main(String[] args) {
        // login("kuangshen","123456");    正常登录
        login(" 'or '1=1"," 'or'1=1"); // sql注入问题
    }
    // 登录业务
    public static void login(String username,String password){
        Connection conn =null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JDBCutils.getConnection();
            st = conn.createStatement();
            // SELECT * FROM users WHERE `Name` = 'kuangshen' AND `password` = '123456';
            // SELECT * FROM users WHERE `Name` = '' or '1=1' AND `password` = '' or '1=1';
            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);
        }
    }
}

使用PreparedStatement——防止sql注入

增

package Demo03;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class insertDemo {
    public static void main(String[] args) throws SQLException {
        JDBCutils jdbCutils = new JDBCutils();
        Connection con = jdbCutils.getConnection();
        String sql = "insert into users(id,NAME,PASSWORD,email) values(?,?,?,?)";
        PreparedStatement pst = con.prepareStatement(sql);
        pst.setInt(1,5);
        pst.setString(2,"小明");
        pst.setInt(3,123456);
        pst.setString(4,"xiaoming@qq.com");
        int i = pst.executeUpdate();
        if (i!=0){
            System.out.println("插入成功！");
        }
        jdbCutils.release(con,pst,null);
    }
}

删

package Demo03;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class deleteDemo {
    public static void main(String[] args) throws SQLException {
        JDBCutils jdbCutils = new JDBCutils();
        Connection con = jdbCutils.getConnection();
        String sql = "DELETE FROM users WHERE id = ?";
        PreparedStatement pst = con.prepareStatement(sql);
        pst.setInt(1,5);
        int i = pst.executeUpdate();
        if (i!=0){
            System.out.println("删除成功！");
        }
        jdbCutils.release(con,pst,null);
    }
}

改

package Demo03;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class updataDemo {
    public static void main(String[] args) throws SQLException {
        JDBCutils jdbCutils = new JDBCutils();
        Connection con = jdbCutils.getConnection();
        String sql = "UPDATE users SET password = ? WHERE id = ?";
        PreparedStatement pst = con.prepareStatement(sql);
        pst.setInt(1,123456);
        pst.setInt(2,4);
        int i = pst.executeUpdate();
        if (i!=0){
            System.out.println("更新成功！");
        }
        jdbCutils.release(con,pst,null);
    }
}

查

package Demo03;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class selectDemo {
    public static void main(String[] args) throws SQLException {
        JDBCutils jdbCutils = new JDBCutils();
        Connection con = jdbCutils.getConnection();
        String sql = "SELECT NAME FROM users";
        PreparedStatement pst = con.prepareStatement(sql);
        ResultSet rs = pst.executeQuery();
        while (rs.next()){
            System.out.println(rs.getString("NAME"));
        }
        jdbCutils.release(con,pst,rs);
    }
}

sql注入问题————解决方法

package Demo03;
import java.sql.*;
public class SQLzhuru {
    public static void main(String[] args) {
        // login("kuangshen","123456");    正常登录
        login("'' or 1=1","123456"); // sql注入问题
    }
    // 登录业务
    public static void login(String username,String password){
        Connection conn =null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            conn = JDBCutils.getConnection();
            String sql = "select * from users where `NAME`=? and `PASSWORD`=?";
            pst = conn.prepareStatement(sql);
            pst.setString(1,username);
            pst.setString(2,password);
            // SELECT * FROM users WHERE `Name` = 'kuangshen' AND `password` = '123456';
            // SELECT * FROM users WHERE `Name` = '' or '1=1' AND `password` = '' or '1=1';

            rs = pst.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,pst,rs);
        }
    }
}