MySQL 笔记

MySQL

安装建议

不要用exe，走注册表，删除麻烦。尽可能使用压缩包安装

DOS命令

  • mysql -u root -p 登陆后输入密码

  • mysql语句必须以分号结尾

  • ctrl +c 强行终止

连接数据库

命令行连接

mysql -u root -p密码      --连接数据库
show datebases;     --查看所有的数据库
use school;     --切换数据库   use 数据库名  结果是Datebase changed
show tables;    --查看数据库中的所有的表
describe student;   --显示数据库中所有的表的信息
create database 数据库名；   --创建一个数据库
exit;       --退出连接
--  单行注释
/*  
    多行注释
*/
数据库XXX语言

DDL 定义

DML 操作

DQL 查询

DCL 控制

 

2 操作数据库

 

操作数据库 > 操作数据库中的表 > 操作数据库表中的数据

注意：mysql数据库不区分大小写

2.1 操作数据库 （了解）

1.创建数据库 [ ] 代表可选 { } 代表必选
CREATE DATABASE [IN NOT EXISTS] westos;
2.删除数据库
DROP DATABASE [IF EXISTS] westos;
3.使用数据库
--如果表明或者字段名是一个特殊字符，就需要带`  (tab上面的)
USE `school`;
4.查看数据库
SHOW DATABASES;     --查看所有数据库

2.2 数据库的列类型

数值

  • tinyint 十分小的数据 1个字节

  • smallint 较小的数据 2个字节

  • mediumint 中等大小的数据 3个字节

  • int 标准的整数 4个字节 最常用的

  • bigint 较大的数据 8个字节

  • float 单精度浮点数 4个字节

  • double 双精度浮点数 8个字节 （精度问题！）

  • decimal 字符串形式的浮点数 金融计算的时候，一般是使用decimal

字符串

  • char 字符串 固定大小的 0-255

  • varchar 可变字符串 0-65535 常用的String

  • tinytext 微型文本 2^8 -1

  • text 文本串 2^16 -1 保存大文本

时间日期

  • date YYYY-MM-DD，日期

  • time HH:mm:ss 时间日期

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

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

  • year 年份表示

null

  • 没有值，未知

  • 注意：不要使用null进行运算，结果一定为null

2.3 数据库的字段属性（重点）

Unsigned：

  • 无符号的整数

  • 声明了该列不能声明为负数

zerofill：

  • 0填充的

  • 不足的位数，用0来填充， int（3） 5----005

自增

  • 通常理解为自增，自动在上一条记录的基础上+1（默认）

  • 通常用来设计唯一的主键 index，必须是整数类型

  • 可以自定义设计主键自增的起始值和步长

非空 NULL not null

  • 不可以不填值

  • 不填值得话默认为null

默认：

  • 默认的值，如果不指定，就是默认值

 

拓展：

/*每个表，都必须存在以下五个字段，未来做项目用的，表示一个记录存在得意义
id  主键
`version`   乐观锁
is_delete   伪删除
gmt_create  创建时间
gmt_update  修改时间
*/

2.4 创建数据库表

注意点：

  • 使用英文（），表的名称和字段尽量使用 ``括起来

  • AUTO_INCREMENT 自增

  • 字符串使用 单引号 括起来

  • 所有语句后加，（英文的），最后一个不用加

CREATE TABLE IF NOT EXISTS `student`(
		`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
		`name` VARCHAR(30) NOT NULL DEFAULT'匿名' COMMENT '姓名',
		`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
		`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
		`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
		`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
		`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
		PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
格式
CREATE TABLE [IF NOT EXISTS] `表名`(
	`字段名` 列类型 [属性][索引][注释]，
	`字段名` 列类型 [属性][索引][注释]，
	......
	`字段名` 列类型 [属性][索引][注释]
)[表类型] [字符集设置] [注释]
--逆向操作
SHOW CREATE DATABASE school   --查看创建数据库得语句
SHOW CREATE TABLE student	  --查看student数据表的定义语句
DESC student		--显示表的结构

2.5 数据库表的类型

数据库引擎
/*
INNODB	默认使用
MYISAM	早些年使用的
*/
 MYISAMINNODB
事务支持 不支持 支持
数据行锁定 不支持 支持
外键约束 不支持 支持
全文索引 支持 不支持
表空间的大小 较小 较大，约为2倍
常规使用操作：

  • MYISAM 节约空间，速度较快

  • INNODB 安全性高，支持事物的处理，多表多用户操作

在物理空间存在的位置：

所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库

本质还是文件的存储！

MySQL 引擎在物理文件上的区别

  • InnoDB 在数据库表中只有一个 *.frm文件，以及上级目录下的ibdata1 文件

  • MYISAM 对应文件

    • *.frm 表结构的定义文件

    • *.MUD 数据文件（data）

    • *.MYI 索引文件（index）

设置数据库表的字符集编码
CHARSET=utf8

不设置的话，会是mysql默认的字符集编码 （不支持中文！）

MySQL的默认编码是Latin1，不支持中文

在my.ini中配置默认的编码

character-set-server=utf8

2.6 修改和删除表

修改表表明
ALTER TABLE 旧表名  AS 新表名
添加表的字段
ALTER TABLE 表明 ADD 字段名 列属性
修改表的字段（重命名，修改约束！）
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性			--字段重命名
ALTER TABLE 表名 MODIFY 字段名  列属性				--修改约束
删除表的字段
ALTER TABLE 表名 DROP 字段名
删除表
DROP TABLE [IF EXISTS] 表名

所有的创建和删除操作尽量加上判断，以免报错~

注意点：

  • ``字段名，要用反引号包裹

  • sql 关键字大小写不敏感，尽量小写

 

3 MySQL数据管理

3.1 外键（了解）

删除表先判断是否被引用为外键，否则删除失败

为表添加外键 (物理外键，一般不用)
ALTEER TABLE `表名` ADD CONSTRAINT `约束名` FOREIGN KEY (`作为外键的列`) REFERENCES `哪个表`(`哪个字段`);
最佳实践

  • 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）

  • 我们想使用多张表的数据，想使用外键（程序去实现）

 

3.2 DML语言（全部记住）

DML 数据操作语言：

  • insert

  • update

  • delete

3.3 添加

插入语句（添加）
-- 一行数据插入多个属性
INSERT INTO 表名 ([字段名1，字段名2，字段名3]) VALUES ('值1','值2','值3')

-- eg:
INSERT INTO `grade`(`gradename`) VALUES('大四');

-- 一般写插入语句，一定要数据和字段一一对应（如果不写表的字段，他就会一一匹配）

-- 插入多行数据
INSERT INTO 表名 ([字段名1]) VALUES ('值1'),'(值2'),('值3')

-- 字段可以省略，但字段名和值必须一一对应
INSERT INTO `grade` VALUES('大四','男');

3.4 修改

-- 语法：
UPDATE 表名 SET 列名='值'[,列名='值'];

-- 带条件的修改语句：
UPDATE 表名 SET 列名='值'[,列名='值'] WHERE 列名='值';

-- value可以是具体的值，也可以是一个变量（一般用于时间）

条件： where 子句 运算符判断

运算符：

<> 或 != 不等于

BETWEEN ... AND ... 闭合区间（包含区间）

可通过多个条件定位数据（多个运算符）

AND

OR

3.5 删除

-- 语法：
DELETE FROM 表名 [WHERE 条件]
TRUNCATE 命令

作用：完全情况一个数据库表，表的结构和索引约束不会变！

DELETE 和 TRUNCATE 区别

  • 共同点：都能删除数据，都不会删除表结构

  • 不同：

    • TRUNCATE 重新设置 自增列 计数器会归零

    • TRUNCATE 不会影响事务

 

4 DQL查询数据（最重点）

 

 

 

-- 查询全部
SELECT * FROM 表名

-- 别名，给字段取一个别名 AS，也可以给表起别名
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 from 表名 AS 表的别名

-- 函数 CONCAT(a,b)
SELECT CONCAT('拼接的字符串',字段名) AS 新名字 FROM 表名
别名 AS
去重 DISTINCT
SELECT DISTINCT 字段名 FROM 表名
SELECT 其他用法
SELECT VERSION()	-- 查询系统版本
SELECT 100*3-1 AS 计算结果		-- 用于计算数据
SELECT @@auto_increment_increment	-- 查询自增的步长

-- 数值加法运算
SELECT `StudentResult`+1 AS '提分后' FROM result

数据库中的表达式：文本值，列，Null，函数，计算表达式，系统变量....

SELECT 表达式 FROM 表

4.3 where 条件子句

运算符：

and &&

or ||

Not !

IS NULL

IS NOT NULL

LIKE

通配符 ： % 代表0-多个字符 _ 代表一个字符

匹配的字符要用单引号引起来 student LIKE '刘%'

IN

id  IN  (1001,1002,1003)

 

4.4 联表查询

LEFT JOIN INNER JOIN RIGHT JOIN

原理同笛卡尔积

思路：

  1. 分析需求，来自哪些表

  2. 确定交叉点

  3. 判断条件

JOIN ON 连接查询

WHERE 等值查询

SELECT s.student
FROM 表名1 AS s
INNER JOIN 表名2 AS r		-- AS可以省略
ON s.student = r.student	
WHERE 判断条件

--RIGHT JOIN
SELECT s.student
FROM 表名1 s
RIGHT JOIN 表名2 r		
ON s.student = r.student	
WHERE 判断条件

查询可以嵌套

SELECT s.student
FROM 表名1 s
RIGHT JOIN 表名2 r		
ON s.student = r.student	
INNER JOIN `subject` sub
ON r.SubjectNo = sub.SubjectNo
自链接

自己的表和自己的表连接，核心：一张表拆为两张一样的表即可

-- 查询父子信息：把一张表看成两个一摸一样的表
SELECT a.`Name` AS '父栏目',b.`Name`
FROM `student` AS a,`student` AS b
WHERE a.`parent` = b.`children`

 

4.5 分页和排序 LIMIT 和 ORDER BY

排序：

升序 ASC，降序 DESC

-- 默认是升序，不写就是升序
ORDER BY 要排序的字段 [ASC|DESC]
分页：

语法：LIMIT 起始值（行），页面的大小

第一页： limit 0，5

第二页： limit 5，5

第三页： limit 10，5

...

第n页： limit (n-1)*pagesize ，pagesize

总页数：数据总数/页面大小 向上取整

 

4.6 子查询

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

可以嵌套

where (select* from )

where studentno = (select student from 表名 where 条件)	-- 可以用 IN

 

5 MySQL函数

官网：MySQL ：： mySQL 8.0 参考手册 ：： 12.1 内置功能和操作员参考

5.1 常用函数（不常用）

-- 数学运算

ABS() 绝对值

CEILING() 向上取整

FLOOR() 向下取整

RAND() 返回0-1的随机数

SIGN() 返回一个数的符号

-- 字符串函数

CHAR_LENGTH() 字符串长度

CONCAT() 拼接字符串，用逗号连接

INSER('原字符串',1,2,'新字符串') 查询，替换；从某个位置替换某个长度，（该含义为，从第一个字符开始替换两个字符

LOWER() 转换小写

UPPER() 转换大写

INSTR('字符串','子串') 返回第一次出现字串的索引

-- 时间和日期 （记住）

CURRENT_DATE() 获取当前日期

CURDATE() 获取当前的日期

NOW() 获取当前的时间

LOCALTIME() 本地时间

SYSDATE() 系统时间

YEAR(NOW()) 年 -- MONTH DAY HOUR MINUTE SECOND

-- 系统

SYSTEM_USER()

USER()

VERSION()

 

5.2 聚合函数（常用）

COUNT() SUM() AVG() MAX() MIN()

COUNT() 查询表中有多少个记录

COUNT(列名) 会忽略所有NULL值

COUNT(*) 不会忽略NULL值

COUNT(1) 不会忽略NULL值

GROUP BY 分组

聚合函数要用 HAVING 过滤分组不能用 WHERE

5.3 数据库界别的MD5加密（扩展）

-- 名文密码
INSERT INTO 表名 VALUE(1,'zhang','123456')

-- 加密
UPDATE 表名 SET pwd=MD5(pwd) [WHERE 条件]

-- 插入的时候加密
INSERT INTO 表名 VALUES(1,'zhang',MD5('123456'))

-- 如何校验：将用户传递进来的密码，进行md5加密，然后对比加密后的值
SELECT * FROM 表名 WHERE `name`='zhang' AND pwd=MD5('123456')

 

6 事务

要么都成功，要么都失败

事务原则 ACID

atomicity 原子性 consistence 一致性 isolation 隔离性 durability 持久性

事物的隔离级别：

  • 脏读：

    指一个事务读取了另外一个事务未提交的数据

  • 不可重复读：

    在一个事务读取表中的某一行数据，多次读取结果不同

  • 虚读（幻读）：

    是指在一个事务内读取到了别的事物插入的数据，导致前后读取不一致

-- mysql是默认开启事务自动提交的
SET autocommit= 0	-- 关闭
SET autocommit= 1	-- 开启（默认的）
-- 手动处理事务
SET autocommit = 0	-- 关闭自动提交
-- 事务开启
START TRANSACTION 	-- 标记一个事务的开始，从这个之后的sql 都在同一个事物内

-- 提交：持久化（成功！）
COMMIT 
-- 回滚：回到原来的样子（失败！）
ROLLBACK

-- 事务结束
SET autocommit = 1	-- 开启自动提交

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

7. 索引

索引是帮助MySQL高效获取数据的数据结构

7.1 索引的分类

  • 主键索引 (PRIMARY KEY)

    • 唯一的标识，主键不可重复

  • 唯一索引 (UNNIQUE KEY)

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

  • 常规索引 (KEY/INDEX)

    • 默认的，index 和 key 关键字设置

  • 全文索引 (FULLTEXT)

    • 在特定的数据库引擎下才有，MyISAM

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

-- 添加一个全文索引 方式一：
ALERT TABLE school.student ADD FULLTEXT INDEX `索引名`('列名')

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

-- 添加索引 ，方式二
CREATE [索引类型] INDEX 索引名  ON 表（字段）

7.2 测试

7.3 索引原则

  • 索引不是越多越好

  • 不要对经常变动的数据加索引

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

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

 

8 权限管理和备份

8.1用户管理

-- 创建用户
CREATE USER 用户名 IDENTIFIED BY '密码'

-- 修改密码（修改当前用户密码）
SET PASSWORD = PASSWORD('123456')

-- 修改密码（修改指定用户密码）
SET PASSWORD FOR 用户名 = PASSWORD('123456U')

-- 重命名
RENAME USER 原来的名字 = TO 新的名字

-- 用户授权
-- ALL PRIVILEGES 除了给其他人授权，其他的都能干
GRANT ALL PRIVILEGES ON 库.表	TO 用户名	
GRANT ALL PRIVILEGES ON *.*	TO 用户名	-- 授予全部的权限

-- 查询权限
SHOW GRANT FOR 用户名	-- 查询指定用户的权限
SHOW GRANT FOR root@localhost	-- 查看root权限

-- 撤销权限
-- REVOKE 哪些权限，在那个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM 用户名

-- 删除用户
DROP USER 用户名

8.2 MySQL备份

MySQL 数据库备份的方式：

  • 直接拷贝物理文件

  • 在可视化工具中手动导出

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

# mysqldump -h主机 -u用户名 -p密码 数据库 表名 >无理磁盘的位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:a.sql

#多个表
# mysqldump -h主机 -u用户名 -p密码 数据库 表名1 表2 表3 >无理磁盘的位置/文件名

#导出数据库
# mysqldump -h主机 -u用户名 -p密码 数据库 >无理磁盘的位置/文件名
mysqldump -hlocalhost -uroot -p123456 school >D:a.sql

# 导入
# 登陆的情况下，切换到指定的数据库
# source 备份文件
source d:/a.sql

# 未登录的话
mysql -u用户名 -p密码 库名< 备份文件

假设你要备份数据库，防止数据丢失，把sql文件给别人即可！

 

9 规范数据库设计

Ant Design Pro

Element ui

9.2 三大范式

第一范式（1NF）

原子性：要求数据库表的每一列都是不可分割的原子数据项

第二范式（2NF）

第一范式基础上，消除非主属性对主码的部份依赖，（非码属性必须完全依赖于候选码）

每张表只描述一件事情

第三范式（3NF）

第二范式的基础上，消除传递依赖，（任何非主属性不依赖于其他的非主属性）

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

规范性 和 性能的问题

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

 

10 JDBC（重点）

10.1 数据库驱动

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

应用程序----------MySQL驱动 / Oracle 驱动 ------------数据库

10.2 JDBC

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

这些规范的实现由具体的厂商去做，对于开发人员来说，只需要掌握JDBC接口的操作即可

 

 

学习JDBC学要知道两个包 java.sql 和 javax.sql ,还需要导入一个数据库驱动包 mysql-connector-java

10.3 第一个JDBC程序

创建测试数据库

  1. 创建一个新项目

  2. 导入数据库驱动

    去官网下载 mysql-connector-java.jar 的jar包

    项目新建lib文件夹，将jar包移入lib文件夹，右击lib文件夹 --选择 Add as Library 即可打开

  3. 编写测试代码

package com.sr.lesson01;

import java.sql.*;

//我的第一个JDBC程序
public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.cj.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 = "root";

        //3.连接成功，数据库对象  connection 代表数据库
        Connection connection = DriverManager.getConnection(url, username, 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"));
        }

        //6.释放连接    先开放的后释放
        resultSet.close();
        statement.close();
        connection.close();

    }
}

步骤总结：

  1. 加载驱动

  2. 连接数据库 DriverManager

  3. 获得执行sql的对象 Statement

  4. 获得返回的结果集（）

  5. 释放连接

JDBC中的对象解释
url
// ？连接参数 useUnicode：支持中文编码  characterEncoding：设置字符集编码     useSSL：使用安全的连接
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";

// jdbc:mysql://主机地址：端口号/数据库名？参数1&参数2&参数3
//mysql端口号 默认3306
//oralce -- 1521
//jdbc:oralce:thin:@localhost:1521:sid	//oralce的驱动方法
//connection 代表数据库
//数据库设置自动提交
//事务提交
//事务回滚
connection.commit();
connection.rollback();
connection.setAutoCommit();

Connection connection = DriverManager.getConnection(url, username, password);
Statement 执行SQL的对象
String sql = "SELECT * FROM users";	//编写sql

statement.executeQuery();   //查询操作返回一个结果集 ResultSet
statement.execute(0);   //执行任何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); //移动到指定行
释放资源
//6.释放连接    先开放的后释放
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 = connection.createStatement();
String sql = "insert into user(..) values(..)";
int num = st.executeUpdate(sql);
if (num>0){
    System.out.println("插入成功！！");
}
CRUD操作 -delete

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

Statement st = connection.createStatement();
String sql = "delete from user where id=1";
int num = st.executeUpdate(sql);
if (num>0){
    System.out.println("删除成功！！");
}
CRUD操作 -update

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

Statement st = connection.createStatement();
String sql = "update user set name='' where name= ''";
int num = st.executeUpdate(sql);
if (num>0){
    System.out.println("修改成功！！");
}
CRUD操作 -read

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

Statement st = connection.createStatement();
String sql = "select * from user where id=1";
int rs = st.executeQuery(sql);
while(rs.next()){
     System.out.println(rs.getString("NAME"));
    //根据获取列的数据类型，分别调用rs的相应方法映射到java对象中
}

 

代码实现
工具类：

db.properties

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

JdbcUtils

package com.sr.lesson02.utils;

import java.io.IOException;
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 {
            //getClassLoader()：类加载      getResourceAsStream：获取资源
            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.驱动只用加载一次
            try {
                Class.forName(driver);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        } catch (IOException 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(rs!=null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }

}
测试类
package com.sr.lesson02;

import com.sr.lesson02.utils.JdbcUtils;

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

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();    //获得sql的执行对象
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +
                    "VALUES(5,'kuangshen','123456','6465@qq.com','2020-01-01')";
            int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("插入成功！!");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

 

SQL注入

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

//SELECT * FROM user WHERE Name = 'kuanghen' AND password = '123456';
//SELECT * FROM user WHERE Name = ''or '1=1' AND password = '123456'or'1=1';

10.5 PreparedStatement对象

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

插入
package com.sr.lesson03;

import com.sr.lesson02.utils.JdbcUtils;
import java.util.Date;
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();

            //区别
            //sql里先填问号占位符代替参数
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)" ;

            st = conn.prepareStatement(sql);   //预编译sql ，先写sql，然后不执行

            //手动给参数赋值
            st.setInt(1,6); //给id赋值，第一个参数代表下标（下标都从0开始），第二个参数代表所赋的值
            st.setString(2,"qinjiang");
            st.setString(3,"123456");
            st.setString(4,"2473@qq.com");
            // 注意： sql.Date    sql         java.sql.Date()      转换为sql类的
            //       util.Date  Java        new Date().getTime()  获得时间戳
            st.setDate(5,new java.sql.Date(new Date().getTime()));

            // 执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("插入成功！");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}
删除
try {
            conn = JdbcUtils.getConnection();

            //区别
            //sql里先填问号占位符代替参数
            String sql = "delete from users where id = ?";

            st = conn.prepareStatement(sql);   //预编译sql ，先写sql，然后不执行

            //手动给参数赋值
            st.setInt(1,4);

            // 执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("删除成功！");
            }
查询
try {
            conn = JdbcUtils.getConnection();

            //区别
            //sql里先填问号占位符代替参数
            String sql = "select * from users where id = ?";

            st = conn.prepareStatement(sql);   //预编译sql ，先写sql，然后不执行

            //手动给参数赋值
            st.setInt(1,1);

            // 执行
            rs = st.executeQuery();
            while(rs.next()){
            	System.out.println(rs.getString("NAME"));
            }
防止sql注入

image-20210604125740229

 

10.7 使用IDEA连接数据库

image-20210604130816433

image-20210604131012245

image-20210604131114106

image-20210604131216352

写sql

image-20210604131424110

image-20210604131702164

 

10.8 事务

要么都成功，要么都失败

ACID原则：

原子性：要么全部完成，要么都不完成

一致性：总数不变，最终一致性

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

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

 

隔离性的问题：

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

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

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

package com.sr.lesson04;

import com.sr.lesson02.utils.JdbcUtils;

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


public class TestTransaction {
    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'";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();
            
            String sql2 = "update account set money = money+100 where name = 'B'";
            st = conn.prepareStatement(sql2);
            st.executeUpdate();
            
            //业务完毕，提交事务
            conn.commit();
            System.out.println("操作成功");
            
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            try {
                conn.rollback();    //如果失败，则回滚事务，不写也默认回滚
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

总结：

  1. 开启事务 conn.setAutoCommit(false);

  2. 一组业务执行完毕，提交事务

  3. 可以在catch语句中显示的定义回滚语句，但默认失败就会回滚

 

10.9 数据库连接池

数据库连接 ------ 执行完毕--------释放 连接到释放的过程是十分浪费资源的

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

最小连接数：根据自己需求设定

最大连接数：

等待超时：

 

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

开源数据源实现

DBCP

C3P0

Druid:阿里巴巴

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

 

DBCP 代码实现

需要用到的jar包

commons-dbcp-.jar commons-pool-.jar common-logging-1.2.jar

代码实现

  • dbcpconfig.properties

#连接设置   这里面的名字，是DBCP数据源中定义好的
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=root

#!-- 初始化连接 --
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_UNCOMMITTED

 

  • JdbcUtils_DBCP

package com.sr.lesson05.Utils;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;

import javax.activation.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils_DBCP {
    private static BasicDataSource dataSource = null;

    static {

        try {
            //getClassLoader()：类加载      getResourceAsStream：获取资源
            InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("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(rs!=null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }

}

 

  • TestDBCP

package com.sr.lesson05;


import com.sr.lesson05.Utils.JdbcUtils_DBCP;

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

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

        try {
            conn = JdbcUtils_DBCP.getConnection();

            //区别
            //sql里先填问号占位符代替参数
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)" ;

            st = conn.prepareStatement(sql);   //预编译sql ，先写sql，然后不执行

            //手动给参数赋值
            st.setInt(1,7); //给id赋值，第一个参数代表下标（下标都从0开始），第二个参数代表所赋的值
            st.setString(2,"qinjiang");
            st.setString(3,"123456");
            st.setString(4,"2473@qq.com");
            // 注意： sql.Date    sql         java.sql.Date()      转换为sql类的
            //       util.Date  Java        new Date().getTime()  获得时间戳
            st.setDate(5,new java.sql.Date(new Date().getTime()));

            // 执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("插入成功！");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils_DBCP.release(conn,st,rs);

        }
    }
}

 

C3P0 代码实现

需要的jar包:

c3p0-0.9.5.5.jar mchange-commons-java-.jar

c3p0-config.xml

用xml配置文件

 

略! 看视频吧,狂神数据库基础最后一节

 

结论:

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

posted @ 2021-06-30 14:58  Sr淑女  阅读(63)  评论(0)    收藏  举报