MySQL学习

最新推荐文章于 2023-06-20 17:33:54 发布

文雀~

最新推荐文章于 2023-06-20 17:33:54 发布

阅读量1k

点赞数 1

文章标签： mysql 学习 sql

本文链接：https://blog.csdn.net/qq_53329474/article/details/122992116

版权

1.删除，清空

UPDATE `student` SET `name`='qwq' WHERE id BETWEEN 2 AND 4
UPDATE `student` SET `name`='qaq' WHERE id=1 OR age=5
TRUNCATE --清空，自增归零
UPDATE --删除，自增不会归零
SELECT * from result --查询所有表信息
SELECT `studentno` 学号1 from student as 学生 -- as添加别名
SELECT concat('姓名:',studentname) as 新姓名 FROM student -- 函数concat（a，b），将a拼接到b表中
SELECT DISTINCT `studentno` FROM result -- 发现查重数据，去重，重复的数据只显示一条
select VERSION()查询版本号
SELECT `studentresult`+1 as '提分后' FROM result

2.逻辑运算

运算符	语法	描述
is null	a is null	a为null，结果为真
between	a between b and c	a在b和c之间结果为真
like	a like b	SQL匹配，如果a匹配b，则结果为真
in	a in(a1,a2,a3…)	假设a在a1，a2，a3…其中之一则结果为真

举例：

SELECT `studentno`,`studentname` FROM `student`
WHERE studentname like '刘%' -- "%"代表0到任意字符，"_"代表一个字符
WHERE studentname like '%刘%' -- 查询名字含刘的
WHERE studentno in(1001,1002,1003)  -- 查询1001，1002，1003号学员

3.联表查询：

-- inner join
select s.studentno,studentname,subjectno,studentresult from student as s inner join result as r WHERE s.studentno = r.studentno 
-- left join
select s.studentno,studentname,subjectno,studentresult from student as s left join result as r on s.studentno = r.studentno WHERE studentresult is null
-- on作用于连接查询时条件判断，where作用于查询出来后后对初始结果进行再次筛选

操作	描述
inner join	如果表中至少有一个匹配，就返回行，都有必须标注
right join	会从左表中返回所有的值，即使右表没有匹配
left join	会从右表中返回所有的值，即使左表没有匹配

4.自连接

-- 同一个表通过不同字段相同元素连接
select a.`categoryname` as 'farther', b.`categoryname` as 'son'
from `category` as a , `category` as b 
where a.`categoryid` = b.`pid`

5.排序和分页

排序

select s.`studentno`,`studentname`,`subjectname`,`studentresult`
from student s inner join `result` r
on s.`studentno` = r.`studentno`
inner join `subject` su
on su.`subjectno` = r.`subjectno`
ORDER BY studentresult DESC -- 通过ORDER BY 排序，升序ASC，降序DESC

分页

语法：limit 起始值，页面的大小

limit 0,10 --从0开始，一页10行

6.子查询

本质嵌套,由里及外

select `studentno`,`subjectno`,`studentresult`
from `result`
where subjectno = (
      -- 查询目标subjectno为多少
      select subjectno from `subject`
      where subjectname = '数据库结构-1'

)
-- 查询C语言-1的同学信息
select `studentno`,`studentname`from student where studentno
In(
   select studentno from result where subjectno = (
			
			  select subjectno from `subject` where `subjectname`='C语言-1'
			
			)
)
ORDER BY studentno desc
limit 0,5

7.MySQL函数

官网https://dev.mysql.com/doc/refman/8.0/en/

常用函数

-- 数学运算
select ABS(-8) -- 绝对值
select ceiling(9.4) -- 向上取整
select floor(9.4) --向下取整
select rand() -- 返回0~1之间的随机数
select sign(-10) -- 判断一个数的符号，负数返回-1，正数返回1

-- 字符串函数
select CHAR_LENGTH('伟大伟大') -- 字符串长度
select CONCAT('i ','like ','w') -- 拼接字符串
select INSERT('i very like  ',13,1,'y') -- 查询，替换,从第13个位置替换为y，即从某个位置替换某个长度字符串
select lower('QwQ') -- 转为小写
select upper('QwQ') -- 转为大写
select instr('super like','k') --返回第一次出现的子串索引
select replace('how to experess my emotion','emotion','feeling') -- 替换出现的字符串
select SUBSTR('how to experess my emotion',1,3) -- 返回指定截取字符串
select REVERSE('ewf') -- 反转

-- 将姓李的改为高
select REPLACE(studentname,'李','高')
FROM student where studentname like '李%'

-- 时间与日期函数
select CURRENT_DATE() -- 获取当前日期
select NOW() -- 当前时间
select LOCALTIME() -- 本地时间
select SYSDATE() -- 系统时间
SELECT YEAR(now()) -- 获取时间的年
SELECT MONTH(now()) 
SELECT DAY(now()) 
SELECT YEAR(now())

聚合函数

函数	描述
COUNT()	计数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值

-- 以下三个都能够统计表中数据
select COUNT(studentname) from student; -- 指定列COUNT(字段)，会忽略所有的null值
select COUNT(studentresult) from result; 
select COUNT(*) from student; -- COUNT(*)，不会忽略null值，计算行数
select COUNT(1) from result;  -- COUNT(1)，不会忽略null值，计算行数

select SUM(`studentresult`) as 总和 from result 
select AVG(`studentresult`) as 平均 from result 
select MAX(`studentresult`) as 平均 from result 
select MIN(`studentresult`) as 平均 from result 

-- 查询不同课程的平均分，最高分，最低分，平均大于60
select subjectname,AVG(studentresult),MAX(studentresult),MIN(studentresult)
from result r
inner join `subject` sub
on r.subjectno = sub.subjectno
GROUP BY r.subjectno -- 通过什么字段来分组
HAVING AVG(studentresult)>60 -- 过滤分组满足的次要条件

8.事务

事务原则: 原子性，一致性，隔离性，持久性

参考博客连接：https://blog.csdn.net/dengjili/article/details/82468576

执行事务

-- MySQL 是默认开启事务是自动提交的
SET autocommit = 0 -- 关闭
SET autocommit = 1 -- 开启

-- 手动处理事务
SET autocommit = 0 -- 关闭自动提交

-- 事务开启

START TRANSACTION -- 标记一个事务的开始

-- 提交：持久化
COMMIT -- 提交事务
-- 回滚：回到原来的样子
ROLLBACK 

-- 事务关闭
SET autocommit = 1 -- 开启自动提交

SAVEPOINT 保存点名 -- 设置事务的一个保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点

9.索引

MySQL对索引的定义：索引（index）是帮助MySQL高效获取数据的数据结构

索引的分类

主键索引（PRIMARY KEY）
- 唯一的标识，主键不可重复
唯一索引（UNICUE KEY）
- 避免重复的列出现，唯一索引可以重复
常规索引（KEY/INDEX）
- 默认的
全文索引（FullText）
- 在特定的数据库引擎下才有，MyISAM
- 快速定位数据

索引的解释应用

explain:https://blog.csdn.net/jiadajing267/article/details/81269067

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

-- 显示所有的索引
show index from student

-- 增加一个全文索引
ALTER TABLE school.student add FULLTEXT index `studentname1`(`studentname`);-- 索引名（列名）

-- EXPLAIN 分析sql执行的状况
explain select * FROM student;-- 非全文索引
explain select * FROM student where match(studentname) against('刘')

SET GLOBAL log_bin_trust_function_creators=1; -- 开启创建函数功能
/*
  第一个语句 delimiter 将 mysql 解释器命令行的结束符由”;” 改成了”$$”，
  让存储过程内的命令遇到”;” 不执行
*/
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`,`eamil`,`phone`,`gender`)VALUES(CONCAT('用户',i),'19224305@qq.com','123456789',FLOOR(RAND()*2));
		SET i=i+1;
	END WHILE;
	RETURN i;
END;$$

SELECT mock_data()$$ -- 执行此函数 生成一百万条数据

-- id_表名_字段名
-- CREATE INDEX 索引名 on 表（字段）
CREATE INDEX id_app_user_name on app_user(`name`);

SELECT * from app_user where `name` = '用户9989';-- 加快查询速度

索引的原则

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

索引背后的的数据结构及算法原理https://blog.csdn.net/wufuhuai/article/details/79631466

10.权限管理和备份

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-U8cvBJwk-1645103579607)(C:\Users\A’y’la\AppData\Roaming\Typora\typora-user-images\image-20220214221358650.png)]

SQL 命令操作

用户表：mysql.user

本质：对这张表增删改查

-- 创建用户 CRATE USER 用户名 IDENTIFIED BY '密码'
CREATE user qaq IDENTIFIED by '1414'

-- 修改密码
ALTER USER "qaq"@"127.0.0.1" IDENTIFIED BY '14145012';
ALTER USER "Ayla"@"127.0.0.1" IDENTIFIED BY '14141414';

-- 查询权限
show grants for qaq
show grants for root@localhost

-- 赋予权限
GRANT Alter Routine ON *.* TO `Ayla`@`127.0.0.1`;

-- 撤销权限
REVOKE Alter ON *.* FROM `Ayla`@`127.0.0.1`;

-- 删除用户
DROP USER Ayla

MySQL备份

为什么要备份
- 保证重要的数据不丢失
- 数据转移A–>B
MySQL数据库备份的方式
- 直接拷贝物理文件
- 在可视化工具手动导出
- 使用命令行导出https://www.cnblogs.com/markLogZhu/p/11398028.html

mysqldump -hlocalhost -uroot -p1414 shool student >D:/a.sql

#导入
#source 备份文件
mysql > use db_name
mysql > source /backup/mysqldump/db_name.db

11规范数据库设计

三大范式

https://www.cnblogs.com/linjiqin/archive/2012/04/01/2428695.html

第一范式 （1NF）

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

第二范式（2NF）

第一范式前提下，在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中

第三范式（3NF）

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

（规范数据库的设计）

规范性和性能问题

关联查询的表不得超过三张表

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

12.JDBC

数据库驱动

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1uJgIRr8-1645103579610)(C:\Users\A’y’la\AppData\Roaming\Typora\typora-user-images\image-20220215224502307.png)]

我们的程序会通过数据库驱动和数据库打交道

JDBC

SUN公司为了简化开发人员的(对数据库的统一)操作,提供了一个(Java操作数据库的)规范,俗称JDBC

对于开发人员来说,我们只需要掌握JDBC接口

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lsTMXyeH-1645103579611)(C:\Users\A’y’la\AppData\Roaming\Typora\typora-user-images\image-20220215230132291.png)]

安装mysql-connector-java-8.0.28.jar

第一个jdbc程序

1.创建一个普通项目

2.导入数据库驱动

建立一个lib项目

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OGLfrBpc-1645103579612)(C:\Users\A’y’la\AppData\Roaming\Typora\typora-user-images\image-20220216003509411.png)]

复制jar到lib

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-L4DppcSi-1645103579613)(C:\Users\A’y’la\AppData\Roaming\Typora\typora-user-images\image-20220216003621261.png)]

点击add as library

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-leDX5Qix-1645103579615)(C:\Users\A’y’la\AppData\Roaming\Typora\typora-user-images\image-20220216003835468.png)]

3.编写一个测试代码

package com.company.lesson01;

import java.sql.*;

public class JdbcFirstDemo01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");//固定写法

        //2.用户信息和url
        /*
        useUnicode=true支持中文编码
        characterEncording=utf8字符集设为utf8
        useSSl=true使用一个安全的连接
         */
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncording=utf8&useSSl=false&serverTimezone=UTC";
        String usename = "root";
        String password = "1414";

        //3.连接成功   Connection代表数据库
        Connection connection = DriverManager.getConnection(url, usename, password);

        //4.执行SQL对象  Statement执行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();
    }
}

步骤总结：

1.加载驱动

Class.forName(“com.mysql.cj.jdbc.Driver”);

2.连接数据库 DriverManager

3.获得执行sql的对象 Statement

4.获得返回的结果集

5.释放连接

statement对象详解

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

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

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

CRUD操作-create

使用executeUpdate(String sql)方法完成数据添加操作，示例操作：

Statement statement = connection.createStatement();
String sql = "insert into user(...) values(...)";
int num = statement.executeUpdate(sql);
if (num > 0) {
    System.out.println("插入成功~");
}

CRUD操作-delete

Statement statement = connection.createStatement();
String sql = "delete from user where id=1";
int num = statement.executeUpdate(sql);
if (num > 0) {
    System.out.println("删除成功~");
}

CRUD操作-update

Statement statement = connection.createStatement();
String sql = "update user set name='' where name =''";
int num = statement.executeUpdate(sql);
if (num > 0) {
    System.out.println("修改成功~");
}

CRUD操作-read

Statement statement = connection.createStatement();
String sql = "SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
    //根据获取列的数据类型，分别调用resultSet的相应方法映射到java对象中
}

1.提取工具类

package com.company.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");

            //驱动只加载一次
            Class.forName(driver);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    //释放连接资源
    public static void relese(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();
            }
        }
    }

}

2.编写增删改的方法，excuteUpdate

增

package com.company.lesson02;

import com.company.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(4,'wqe','123456','1414501260@qq.c','2002-6-16')";
            int i = st.executeUpdate(sql);

            if (i>0){
                System.out.println("succeed");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.relese(conn,st,rs);
        }

    }
}

删

package com.company.lesson02;

import com.company.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();
            String sql = "DELETE FROM users WHERE id = 4";
            int i = st.executeUpdate(sql);

            if (i>0){
                System.out.println("succeed");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.relese(conn,st,rs);
        }

    }
}

改

package com.company.lesson02;

import com.company.lesson02.utils.JdbcUtils;

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

public class TestUpdata {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            String sql = "UPDATE users set `NAME` = 'qq',`email`='45674856@qq.com' where id=1";
            int i = st.executeUpdate(sql);

            if (i>0){
                System.out.println("succeed");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.relese(conn,st,rs);
        }

    }
}

查

package com.company.lesson02;

import com.company.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.printf(rs.getString("NAME"));
           }


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.relese(conn,st,rs);
        }

    }
}

sql注入问题

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

import com.zyy.lesson02.utils.JDBCUtils;

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

/**
 * @ClassName: SQLQuestion
 * @Description: TODO 类描述
 * @Author: zyy
 * @Date: 2021/07/14 22:23
 * @Version: 1.0
 */
public class SQLQuestion {
    public static void main(String[] args) {

        //正常登录
//        login("张三","1234567");

        //sql注入
        login("' or '1=1","123456");

    }

    /**
     * 登录业务
     */
    public static void login(String userName, String password) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            con = JDBCUtils.getConnection();
            st = con.createStatement();
            String sql = "SELECT * FROM users WHERE `name`='"+userName+"' AND `password`='"+password+"'";
            // SELECT * FROM users WHERE `name`='' or '1=1' AND `password`='123456'
            System.out.println(sql);
            rs = st.executeQuery(sql);
            while (rs.next()) {
                System.out.println("id="+rs.getInt("id"));
                System.out.println("name="+rs.getString("name"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(con, st, rs);
        }
    }
}

导致结果：错误的用户名或者密码可以获取到全部的用户信息

PreparedStatement对象

PreparedStatement可以防止SQL注入，效率更好

新增

import com.zyy.lesson02.utils.JDBCUtils;

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

/**
 * @ClassName: TestInsert
 * @Description: TODO 类描述
 * @Author: zyy
 * @Date: 2021/07/14 18:19
 * @Version: 1.0
 */
public class TestInsert {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            con = JDBCUtils.getConnection();
            //使用?占位符代替参数
            String sql = "INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`) VALUES (?,?,?,?,?)";
            //预编译SQL，先写SQL，然后不执行
            st = con.prepareStatement(sql);
            //手动给参数赋值
            st.setInt(1, 5);
            st.setString(2, "钱七");
            st.setString(3, "123456");
            st.setString(4, "qianqi@sina.com");
            st.setDate(5, new java.sql.Date(new java.util.Date().getTime()));
            int num = st.executeUpdate();
            if (num > 0) {
                System.out.println("插入成功！");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(con, st, rs);
        }

    }
}

删除

import com.zyy.lesson02.utils.JDBCUtils;

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

/**
 * @ClassName: TestDelete
 * @Description: TODO 类描述
 * @Author: zyy
 * @Date: 2021/07/14 18:19
 * @Version: 1.0
 */
public class TestDelete {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            con = JDBCUtils.getConnection();
            //使用?占位符代替参数
            String sql = "DELETE FROM users WHERE `id`=?";
            //预编译SQL，先写SQL，然后不执行
            st = con.prepareStatement(sql);
            //手动给参数赋值
            st.setInt(1, 5);
            int num = st.executeUpdate();
            if (num > 0) {
                System.out.println("删除成功！");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(con, st, rs);
        }

    }
}

更新

import com.zyy.lesson02.utils.JDBCUtils;

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

/**
 * @ClassName: TestUpdate
 * @Description: TODO 类描述
 * @Author: zyy
 * @Date: 2021/07/14 18:19
 * @Version: 1.0
 */
public class TestUpdate {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            con = JDBCUtils.getConnection();
            //使用?占位符代替参数
            String sql = "UPDATE users SET birthday=? WHERE id=?";
            //预编译SQL，先写SQL，然后不执行
            st = con.prepareStatement(sql);
            //手动给参数赋值
            st.setDate(1, new java.sql.Date(new java.util.Date().getTime()));
            st.setInt(2, 1);
            int num = st.executeUpdate();
            if (num > 0) {
                System.out.println("修改成功！");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(con, st, rs);
        }

    }
}

查询

import com.zyy.lesson02.utils.JDBCUtils;

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

/**
 * @ClassName: TestSelect
 * @Description: TODO 类描述
 * @Author: zyy
 * @Date: 2021/07/14 18:19
 * @Version: 1.0
 */
public class TestSelect {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            con = JDBCUtils.getConnection();
            //使用?占位符代替参数
            String sql = "SELECT * FROM users WHERE id=?";
            //预编译SQL，先写SQL，然后不执行
            st = con.prepareStatement(sql);
            //手动给参数赋值
            st.setInt(1, 1);
            rs = st.executeQuery();
            while (rs.next()) {
                System.out.println("id="+rs.getInt("id"));
                System.out.println("name="+rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(con, st, rs);
        }

    }
}

防止sql注入

import com.zyy.lesson02.utils.JDBCUtils;

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

/**
 * @ClassName: SQLQuestion
 * @Description: TODO 类描述
 * @Author: zyy
 * @Date: 2021/07/14 18:19
 * @Version: 1.0
 */
public class SQLQuestion {
    public static void main(String[] args) {

        //正常登录
//        login("张三","123456");

        //sql注入
        login("' or '1=1", "123456");

    }

    /**
     * 登录业务
     */
    public static void login(String userName, String password) {
        Connection con = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            con = JDBCUtils.getConnection();
            // PreparedStatement 防止SQL注入的本质，把传递进来的参数当做字符
            // 假设其中存在转义字符，比如说'会被直接转义
            String sql = "SELECT * FROM users WHERE `name`=? AND `password`=?";
            st = con.prepareStatement(sql);
            st.setString(1, userName);
            st.setString(2, password);
            rs = st.executeQuery();
            while (rs.next()) {
                System.out.println("id=" + rs.getInt("id"));
                System.out.println("name=" + rs.getString("name"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(con, st, rs);
        }
    }
}

使用idea连接数据库

连接成功后，就可以选择数据库

连接不上的话，可以看一下下面这里，配置对应的mysql版本

双击数据库

更新数据（提交）

idea编写sql

create table account
(
    id    int primary key auto_increment,
    name  varchar(40),
    money float
);

insert into account(name, money)
values ('A', 1000),
       ('B', 1000),
       ('C', 1000);

JDBC操作事务

要么都成功，要么都失败

ACID原则

原子性：要么全部成功，要么全部失败

一致性：总数不变

隔离性：多个进程互不干扰

持久性：一旦提交不可逆，持久化到数据库了

隔离性的问题：

脏读：一个事务读取了另外一个没有提交的事务

不可重复读：在同一个事务内，重复读取表中数据，表数据发生了改变

幻读：在一个事务内，读取到了别人插入的数据，导致前后读出来的结果不一致

代码实现

开启事务con.setAutoCommit(false);
一组业务执行完毕，提交事务
可以在catch语句中显示的定义回滚语句，但是默认失败就会回滚

正常情况

import com.zyy.lesson02.utils.JDBCUtils;

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

/**
 * @ClassName: TestTransaction1
 * @Description: TODO 类描述
 * @Author: zyy
 * @Date: 2021/07/15 21:59
 * @Version: 1.0
 */
public class TestTransaction1 {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            con = JDBCUtils.getConnection();
            //关闭自动提交 自动会开启事务
            con.setAutoCommit(false);//开启事务
            // A 转 B 100元
            String sql1 = "update account set money=money-100 where name='A'";
            ps = con.prepareStatement(sql1);
            ps.executeUpdate();
            String sql2 = "update account set money=money+100 where name='B'";
            ps = con.prepareStatement(sql2);
            ps.executeUpdate();
            //业务完毕，提交事务
            con.commit();

            System.out.println("A 转 B 100元 成功！");
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                con.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            JDBCUtils.release(con, ps, rs);
        }
    }
}

异常情况

import com.zyy.lesson02.utils.JDBCUtils;

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

/**
 * @ClassName: TestTransaction1
 * @Description: TODO 类描述
 * @Author: zyy
 * @Date: 2021/07/15 21:59
 * @Version: 1.0
 */
public class TestTransaction2 {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            con = JDBCUtils.getConnection();
            //关闭自动提交 自动会开启事务
            con.setAutoCommit(false);//开启事务
            // A 转 B 100元
            String sql1 = "update account set money=money-100 where name='A'";
            ps = con.prepareStatement(sql1);
            ps.executeUpdate();

            //默认失败
            int x = 1/0; //一定会异常

            String sql2 = "update account set money=money+100 where name='B'";
            ps = con.prepareStatement(sql2);
            ps.executeUpdate();
            //业务完毕，提交事务
            con.commit();

            System.out.println("A 转 B 100元 成功！");
        } catch (SQLException e) {
            e.printStackTrace();
            //如果异常，默认也会回滚，下面不写也可以
//            try {
//                con.rollback();
//            } catch (SQLException ex) {
//                ex.printStackTrace();
//            }
        } finally {
            JDBCUtils.release(con, ps, rs);
        }
    }
}

13.DBCP-C3P0连接池

数据库连接 – 执行完毕 – 释放

连接-- 释放是十分浪费系统资源的

池化技术：准备一些预先的资源，过来就连接预先准备好的

最小连接数：10(常用连接)

最大连接数：100 （业务最高承载上线）

等待超时：100ms

编写连接池，实现一个接口DataSource

开源数据源实现

DBCP

C3p0

Druid:阿里巴巴

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

DBCP

需要用到的jar包

commons-dbcp-1.4

commons-pool-1.6

配置文件dbcp.properties

#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456

#初始化连接
initialSize=10

#最大连接数量
maxActive=50

#最大空闲连接
maxIdle=20

#最小空闲连接
minIdle=5

#超时等待时间以毫秒为单位 6000毫秒/1000等于60秒
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：【属性名=property;】
#注意：user 与 password 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8

#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的只读（read-only）状态。
#如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix）
defaultReadOnly=

#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_COMMITTED

工具类

import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * @ClassName: JDBCDBCPUtils
 * @Description: TODO 类描述
 * @Author: zyy
 * @Date: 2021/07/14 17:48
 * @Version: 1.0
 */
public class JDBCDBCPUtils {
    private static DataSource dataSource = null;

    static {
        try {
            InputStream in = JDBCDBCPUtils.class.getClassLoader().getResourceAsStream("dbcp.properties");
            Properties properties = new Properties();
            properties.load(in);
            //创建数据源 工厂模式
            dataSource = BasicDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

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

    /**
     * 释放资源
     */
    public static void release(Connection con, 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 (con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

测试类

import com.zyy.lesson02.utils.JDBCUtils;
import com.zyy.lesson05.utils.JDBCDBCPUtils;

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

/**
 * @ClassName: TestDBCP
 * @Description: TODO 类描述
 * @Author: zyy
 * @Date: 2021/07/15 22:41
 * @Version: 1.0
 */
public class TestDBCP {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            con = JDBCDBCPUtils.getConnection();
            //使用?占位符代替参数
            String sql = "INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`) VALUES (?,?,?,?,?)";
            //预编译SQL，先写SQL，然后不执行
            st = con.prepareStatement(sql);
            //手动给参数赋值
            st.setInt(1, 5);
            st.setString(2, "钱七");
            st.setString(3, "123456");
            st.setString(4, "qianqi@sina.com");
            st.setDate(5, new java.sql.Date(new java.util.Date().getTime()));
            int num = st.executeUpdate();
            if (num > 0) {
                System.out.println("插入成功！");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCDBCPUtils.release(con, st, rs);
        }

    }
}

C3P0

需要用到的jar包

c3p0-0.9.5.5.jar

mchange-commons-java-0.2.19.jar

配置文件c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!--
    c3p0的缺省（默认）配置
    如果在代码中ComboPooledDataSource ds=new ComboPooledDataSource();这样写就表示使用的是c3p0的缺省（默认）
    -->
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false&amp;serverTimezone=UTC</property>
        <property name="user">root</property>
        <property name="password">123456</property>
        <property name="acquiredIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </default-config>


    <!--
    c3p0的命名配置
    如果在代码中ComboPooledDataSource ds=new ComboPooledDataSource("MySQL");这样写就表示使用的是name是MySQL
    -->
    <name-config name="MySQL">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false&amp;serverTimezone=UTC</property>
        <property name="user">root</property>
        <property name="password">123456</property>
        <property name="acquiredIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </name-config>
</c3p0-config>

工具类

import com.mchange.v2.c3p0.ComboPooledDataSource;

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

/**
 * @ClassName: JDBCC3P0Utils
 * @Description: TODO 类描述
 * @Author: zyy
 * @Date: 2021/07/14 17:48
 * @Version: 1.0
 */
public class JDBCC3P0Utils {
    private static DataSource dataSource = null;
    //private static ComboPooledDataSource dataSource = null;


    static {
        try {
            //代码的方式配置
//            dataSource = new ComboPooledDataSource();
//            dataSource.setDriverClass();
//            dataSource.setJdbcUrl();
//            dataSource.setUser();
//            dataSource.setPassword();
//            dataSource.setMaxPoolSize();
//            dataSource.setMinPoolSize();
            //配置文件写法
            dataSource = new ComboPooledDataSource("MySQL");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

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

    /**
     * 释放资源
     */
    public static void release(Connection con, 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 (con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

测试类

import com.zyy.lesson05.utils.JDBCC3P0Utils;
import com.zyy.lesson05.utils.JDBCDBCPUtils;

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

/**
 * @ClassName: TestC3P0
 * @Description: TODO 类描述
 * @Author: zyy
 * @Date: 2021/07/15 22:41
 * @Version: 1.0
 */
public class TestC3P0 {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            con = JDBCC3P0Utils.getConnection();
            //使用?占位符代替参数
            String sql = "INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`) VALUES (?,?,?,?,?)";
            //预编译SQL，先写SQL，然后不执行
            st = con.prepareStatement(sql);
            //手动给参数赋值
            st.setInt(1, 6);
            st.setString(2, "刘八");
            st.setString(3, "123456");
            st.setString(4, "liuba@sina.com");
            st.setDate(5, new java.sql.Date(new java.util.Date().getTime()));
            int num = st.executeUpdate();
            if (num > 0) {
                System.out.println("插入成功！");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCC3P0Utils.release(con, st, rs);
        }

    }
}

总结

无论用什么数据源，本质还是一样的，DataSource接口不会变，方法就不会变

Welcome to The Apache Software Foundation!

最后

感谢kuangshen

https://gitee.com/zhayuyao/java-notes/blob/master/md%E6%96%87%E6%A1%A3/%E7%8B%82%E7%A5%9E%E8%AF%B4java/03.MySql/MySQL%E5%9F%BA%E7%A1%80.md#2jdbc

et;
import java.sql.SQLException;

/**

@ClassName: TestC3P0
@Description: TODO 类描述
@Author: zyy
@Date: 2021/07/15 22:41
@Version: 1.0
*/
public class TestC3P0 {
public static void main(String[] args) {
Connection con = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
con = JDBCC3P0Utils.getConnection();
//使用?占位符代替参数
String sql = “INSERT INTO users(id,name,password,email,birthday) VALUES (?,?,?,?,?)”;
//预编译SQL，先写SQL，然后不执行
st = con.prepareStatement(sql);
//手动给参数赋值
st.setInt(1, 6);
st.setString(2, “刘八”);
st.setString(3, “123456”);
st.setString(4, “liuba@sina.com”);
st.setDate(5, new java.sql.Date(new java.util.Date().getTime()));
int num = st.executeUpdate();
if (num > 0) {
System.out.println(“插入成功！”);
}
```
 } catch (SQLException e) {
     e.printStackTrace();
 } finally {
     JDBCC3P0Utils.release(con, st, rs);
 }
```
}
}


> 总结

无论用什么数据源，本质还是一样的，DataSource接口不会变，方法就不会变

[Welcome to The Apache Software Foundation!](https://gitee.com/link?target=https%3A%2F%2Fwww.apache.org%2Findex.html%23projects-list)

## 最后

感谢kuangshen

[https://gitee.com/zhayuyao/java-notes/blob/master/md%E6%96%87%E6%A1%A3/%E7%8B%82%E7%A5%9E%E8%AF%B4java/03.MySql/MySQL%E5%9F%BA%E7%A1%80.md#2jdbc](https://gitee.com/zhayuyao/java-notes/blob/master/md文档/狂神说java/03.MySql/MySQL基础.md#2jdbc)

文雀~

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
MySQL学习

1.删除，清空UPDATE `student` SET `name`='qwq' WHERE id BETWEEN 2 AND 4UPDATE `student` SET `name`='qaq' WHERE id=1 OR age=5TRUNCATE --清空，自增归零UPDATE --删除，自增不会归零SELECT * from result --查询所有表信息SELECT `studentno` 学号1 from student as 学生 -- as添加别名SELECT concat(
复制链接

扫一扫