MySQL详解

最新推荐文章于 2025-04-05 18:13:50 发布

路边摆摊

最新推荐文章于 2025-04-05 18:13:50 发布

阅读量2.1k

点赞数

文章标签： mysql 数据库 database java

本文链接：https://blog.youkuaiyun.com/jhyy_cc/article/details/122503015

版权

这篇博客全面讲解了MySQL，从初识到高级应用，涵盖了数据类型、数据库操作、DML/DQL语言、查询优化、事务处理、索引、权限管理、备份以及JDBC的使用。特别强调了DQL查询数据的重要性，包括子查询、分组过滤，并讨论了数据库设计的规范和性能考虑。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

文章目录

初识MySQL

操作数据库

--创建数据库
CREATE DATABASE IF NOT EXISTS westos
--删除数据库
DROP DATABASE IF EXISTS westos
--使用数据库。tab键上面的·如果表格或字段名是特殊字符，需要带·
USE `school`

SELECT `user` FROM student
--查看 数据库
SHOW DATABASE

数据库类型

数值：

tinyint 1字节

smallint 2个字节

mediumint 3个字节

int 4个字节

bigint 8个字节

float 4个字节

double 8个字节

decimal 字符串形式的浮点数金融计算一般使用decimal

字符串：

char 0~255

varchar 0~65535

tinytest 2^8-1

test 2^16-1

时间日期

java.util.Date

date YYYY-MM-DD，日期格式

time HH: mm: ss 时间格式

datetime YYYY-MM-DD HH: mm: ss 最常用的时间格式

timestamp 时间戳， 1970.1.1到现在的毫秒数，最为常用

year 年份表示

null

没有值，未知

注意，不要使用null进行运算，结果为null

数据库的字段属性

创建数据库表

数据表的类型

修改和删除

MySQL数据管理

外键

删除有外键关系的表的时候，必须要先删除引用别人的表（从表），再删除被引用的表

以上的操作都是物理外键，数据库级别的外键，不建议使用（避免数据库过多造成困扰）

DML语言

添加

注意事项：

1.字段之间用英文逗号隔开

2.字段可以省略，但后面的值必须一一对应

3.可以同时插入多条数据，values后面的值需要使用“，”隔开即可

修改

删除

DQL查询数据（最重点）

DQL

查询指定字段

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CIuR6Ylg-1642168282400)(https://s2.loli.net/2022/01/12/og3IG5ranBjEdHJ.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xRITkzjq-1642168282401)(https://s2.loli.net/2022/01/12/6d37XsMzoBqDpCb.png)]

where

联表查询

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sRlc11ct-1642168282404)(https://s2.loli.net/2022/01/12/abezUgiMYAvwSK5.png)]

排序

分页

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-14671FcT-1642168282404)(https://s2.loli.net/2022/01/12/MzxVlUgwuFDAyX4.png)]

子查询

本质：在where中嵌套子查询语句

分组和过滤

MySQL函数

常用函数

聚合函数

数据库级别的MD5加密

增强算法复杂度，不可逆

事务

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

隔离所导致的问题：脏读、不可重复读、幻读（虚读）

测试事务实现转账

索引

索引的分类

测试索引

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xXTisCQL-1642168282408)(https://s2.loli.net/2022/01/13/L2VZtgE6inqyTzc.png)]

索引原则

不要对进程变动数据加索引

小数据量的表不需要加索引

索引一般加在常用来查询的字段上

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

权限管理和备份

用户管理

MySQL备份

为什么要备份：

保证重要数据不丢失

数据转移

MySQL数据库备份方式：

直接拷贝物理文件

在可视化工具中手动导出

使用命令行导出 mysqldump 命令行使用

把数据库给别人：给sql文件即可

规范数据库设计

为什么需要设计

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

三大范式

为什么需要数据规范化

信息重复，更新异常，插入异常：无法正常显示信息，删除异常：丢失有效信息

三大范式

第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项。

第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）

第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。

第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

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

规范性和性能的问题

JDBC

数据库驱动

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nD69SGdL-1642168282411)(https://s2.loli.net/2022/01/14/gAzwQvV6GriyHOq.png)]

JDBC

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

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

对于开发人员只需要掌握JDBC接口的操作即可

java.sql

javax.sql

还需导入一个包mysql-connector-java-5.1.47.jar

第一个JDBC程序

创建测试数据库程序

1.创建一个普通项目

2.导入数据库驱动

3.编写测试代码

import java.sql.*;

//我的第一个jdbc程序
public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1、加载驱动
        Class.forName("com.mysql.jdbc.Driver");//固定写法

        //2、用户信息和URL
        //useUnicode=true&characterEncoding=utf8&useSSL=true
        String 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、执行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("psw="+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();
    }
}

步骤总结：

1、加载驱动

2、用户信息

3、连接数据库DriverManager

4、获取执行sql对象statement

5、获得返回结果集

6、释放连接

statement对象详解

代码实现

1、提取工具类

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(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
}

2、编写增删查改的方法

db.properties

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

TestInsert

import com.cc.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) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            String sql = "insert into users(id,`name`,password,email,birthday)"+
                    "values(5,'kuangshen','123456','24736743@qq.com','2020-01-01')";
            int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("插入成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

TestSelect

import com.cc.lesson02.utils.JdbcUtils;

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

public class TestSelect {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            String sql = "select * from users where id = 1";
            rs = st.executeQuery(sql);

            while (rs.next()){
                System.out.println(rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

sql注入问题

sql存在漏洞，会被攻击导致数据泄露，SQL会被拼接“or”

select * from users where name=" ’ or '1=1"

PreparedStatement对象

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

TestInsert

import com.cc.lesson02.utils.JdbcUtils;

import java.sql.*;

public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();

            //区别 使用？占位符
			String sql = "insert into users(id,`name`,password,email,birthday)"+
                    "values(?,?,?,?,?)";
            st = conn.prepareStatement(sql);//预编译SQL，先写SQL，然后不执行
            //手动给参数赋值
            st.setInt(1,6);
            st.setString(2,"qinjiang");
            st.setString(3,"123456");
            st.setString(4,"24734673@qq.com");
            //注意点 sql.Date 数据库 java.sql.Date()
            //       util.Date Java  java.util.Date().getTime() 获得时间戳
            st.setDate(5,new Date(new java.util.Date().getTime()));

            int i = st.executeUpdate();
            if(i>0){
                System.out.println("插入成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

PreparedStatement防止SQL注入的本质，把传递进来的参数当字符

假设其中存在转义字符，比如说 ’ ，会被直接转义掉

login(username:"lisi",password:"123456");
//login(username:"'' or 1=1",password:"'' or 1=1")
//登录业务
    public static void login(String username,String password){
    ...
        String sql = "select * from users where `name`=? and `password`=?";
}

使用IDEA连接数据库

先连接，再选数据库

修改表提交

写sql

事务

要么都成功，要么都失败

import com.cc.lesson02.utils.JdbcUtils;

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

public class TestTransaction1 {
    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'";
            String sql2 = "update account set money = money+100 where name='B'";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();

            conn.prepareStatement(sql2);
            st = conn.prepareStatement(sql2);
            st.executeUpdate();

            //业务完毕，提交事务
            conn.commit();
            System.out.println("成功！");

        } catch (SQLException e) {
            try {
                conn.rollback();//如果失败则回滚(默认回滚，不写也行）
            } catch (SQLException ex) {
                ex.printStackTrace();
            }finally {
                JdbcUtils.release(conn,st,rs);
            }
            e.printStackTrace();
        }
    }
}

数据库连接池（DataSource接口）

public class JdbcUtils_DBCP {
    private static DataSource dataSource = null;

    static {
        try {
            InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("resources/dbcpconfig.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 conn, Statement st, ResultSet rs) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (st != null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4dyWgJf0-1642168282414)(https://s2.loli.net/2022/01/14/M3L4w1tYPWgpmJV.png)]

public class JdbcUtils_C3P0 {
    private static DataSource dataSource = null;

    static {
        try {

            //xml不需要读文件，直接加载
            //创建数据源 工厂模式--->创建
            dataSource = new ComboPooledDataSource();//有配置文件的写法,可传入参数选择自定义配置
            /*
            也可不用配置文件，代码版配置：
            private static ComboPooledDataSource dataSource = null;
            ...
            dataSource = new ComboPooledDataSource();
            dataSource.setDriverClass();
            dataSource.setUser();
            dataSource.setPassword();
            dataSource.setJdbcUrl();
            dataSource.setMaxPoolSize();
            dataSource.setMinPoolSize();
            */

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();//从数据源中获取连接
    }

    //释放连接资源
    public static void release(Connection conn, Statement st, ResultSet rs) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (st != null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

public class TestC3P0 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils_C3P0.getConnection();//原来是自己实现的，现在用别人的
            
            ......
                
        finally {
            JdbcUtils_C3P0.release(conn,st,null);
        }

结论

无论使用什么数据源，本质还是一样，DataSource接口不会变，方法就不会变（只是实现不同），调用方法就不变。