数据库mysql
文章目录
概念
1.数据库
-
概念
按照数据结构来组织、存储和管理数据的仓库,软件,安装在操作系统之上---存储数据
-
分类:
分类:关系数据库(SQL) :通过表与表之间,行与列之间的关系进行数据的存储,管理 mysql,oracle,Sql Server 非关系数据库(NoSQL):对象存储,通过对象自身的属性来决定 Redis,MongDB
2.数据库管理系统
-
概念
一种操纵和管理数据库的大型软件,用于建立、使用和维护数据库。---管理和操作数据
-
mysql
MySQL:关系型数据库管理系统 瑞典MySQL AB 公司开发 属于 Oracle 旗下产品 MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一 SQL语言是用于访问数据库的最常用标准化语言 开放源码 一般中小型网站的开发都选择 MySQL 作为网站数据库 版本:5.7 8.0
Mysqlyog
基本命名行操作
Mysqlyog软件的可视化操作
SQL语句
Mysql数据管理
1.外键
2.DML语言
- 增–insert
DESC student;
INSERT INTO `student`(id,NAME) VALUE (5,'王飞翔');
-- 自增可以不写
INSERT INTO `student`(NAME) VALUE ('黄训');
-- 插入多个字段
-- INSERT INTO `student`(字段1,字段2,字段3) value(与字段一一对应)
INSERT INTO `student`(NAME,age,sex) VALUE ("胡明雄",18,'女');
-- 插入相同字段多条数据
-- INSERT INTO `student`(字段1,字段2,字段3) value(与字段一一对应),()
INSERT INTO `student`(NAME,age,sex)
VALUE ("胡明雄1",18,'女'),("胡明雄2",18,'女'),("胡明雄3",18,'女');
INSERT INTO student
(字段1,字段2,字段3) value(与字段一一对应)
注意事项
- 自增,可以不用设置
- 前面的字段与后面的值一一对应
- 改–update
-- 修改(where 定位)
-- upadate 修改谁(条件) set 旧值=新值
UPDATE student SET `name` = '吕豪' WHERE `name`='胡明雄1';
UPDATE student SET `name` = '朱俊良' WHERE sex = '女';
UPDATE student SET age = 19 WHERE id = 7;
UPDATE student SET sex = '男' WHERE id = 8;
-- 修改多条数据 where定位到 一条或多条
UPDATE student SET `name` = '大大' WHERE `name` = '猪' AND `sex` = '女';
-- 修改多个字段
UPDATE student SET `name` = '虎牙',age = 27 WHERE `name`='大大';
update 修改谁 set 字段=值 where 定位
- 注意事项:
- 值可以是具体的值,也可以是变量(日期)
- where:定位,可以定位到一条或多条记录
- 删除
-- 删除指定数据
DELETE FROM student WHERE id =10;
-- 清空表
TRUNCATE `student`;
格式:
注意事项:
- 区别: TRUNCATE自增量归零,支持事务
- 相同:都不会删除表结构
DQL查询数据
1.基本查询
SELECT * FROM student;
SELECT studentno AS '学号',studentname AS '姓名' FROM student;
SELECT VERSION()
SELECT 3+4;
SELECT * FROM student;
-- concat 连接字符串
SELECT CONCAT('姓名:',studentname) FROM student;
-- distinct 得到参加考试的学生的学号
SELECT studentno FROM result;
SELECT DISTINCT studentno FROM result;
-- where:条件 检索数据中符合条件的值
-- 成绩在80分以上
SELECT studentno,studentresult FROM result
WHERE studentresult >= 80
-- 成绩在80分和90分之间
SELECT studentno,studentresult,subjectno FROM result
WHERE studentresult >= 80 AND studentresult<=90
SELECT studentno,studentresult,subjectno FROM result
WHERE studentresult BETWEEN 70 AND 80;
-- 模糊查询
-- like:匹配 % _ 模糊值 查询名字带有俊的学生
SELECT studentno,studentname FROM student
WHERE studentname LIKE '刘%'
SELECT studentno,studentname FROM student
WHERE studentname LIKE '%俊%'
-- in ;在 具体的一个值或多个值
SELECT studentname,address FROM student
WHERE address IN('北京朝阳','广东深圳')
UPDATE student SET email = NULL
WHERE studentno IN(1000,1001)
-- is null is not null
SELECT studentno AS '学号',studentname AS '姓名' FROM student
WHERE email IS NULL
SELECT studentno AS '学号',studentname AS '姓名' FROM student
WHERE email IS NOT NULL
知识点:
- SELECT 字段或表达式
- concat 连接字符串
- distinct 去重
- where:条件 检索数据中符合条件的值
- like in is null is not null
2.连表查询
-- 连表查询
/*获得参加了考试的学生信息(学生名,学号,科目,名字)
表:student result
建立连接: 相同字段 studentno
七种查询方式
连接 inner join left join right join
*/
-- 内连接:相同字段的值匹配
`student`
-- 右连接:返回右表所有匹配的值,即使左表没有,去和左表匹配
SELECT s.studentno,studentname,subjectno,studentresult FROM
student AS s RIGHT JOIN result AS r
ON(s.studentno = r.studentno)
-- 左连接:返回左表所有匹配的值,即使右表没有,去和右表匹配
SELECT s.studentno,studentname,subjectno,studentresult FROM
student AS s LEFT JOIN result AS r
ON(s.studentno = r.studentno)
-- 查询参加考试的学生(学号,学生名字,科目名,成绩)
/*表:student result subject
建立连接: student-studentno-result result-subjectno-subject
七种查询方式
连接 inner join left join right join
*/
SELECT s.studentno,studentname,subjectname,studentresult FROM
student s RIGHT JOIN result r
ON(s.studentno = r.studentno)
INNER JOIN `subject` sub ON(r.subjectno=sub.subjectno)
SELECT s.studentno,studentname,subjectname,studentresult FROM
student s RIGHT JOIN result r
ON(s.studentno = r.studentno)
INNER JOIN `subject` sub ON(r.subjectno=sub.subjectno)
-- 查询科目所属的年级(科目名称,年纪名称)
SELECT `subjectname`,`gradename` FROM
`subject` INNER JOIN grade
ON(`subject`.`gradeid` =grade.`gradeid` )
-- 查询参加数据库考试的学生(学号,学生名字,科目名,成绩)
SELECT DISTINCT s.studentno,studentname,subjectname,studentresult FROM
student s RIGHT JOIN result r
ON(s.studentno = r.studentno)
INNER JOIN `subject` sub ON(r.subjectno=sub.subjectno)
WHERE subjectname LIKE '数据库结构-1' AND
studentresult >60
知识点:
- 内连接:相同字段的值匹配
- 右连接:以右表位中心,返回右表所有匹配的值,即使左表没有,去和左表匹配
- 左连接:以左表位中心,返回左表所有匹配的值,即使右表没有,去和右表匹配
3.自连接
-- 查询父类对应的子类关系
SELECT a.categoryname AS '父类名',b.categoryname AS '子类名' FROM
category AS a,category AS b
WHERE a.categoryid = b.pid
知识点:
- 自连接:根据表的关系分为两张表,将这两张表进行连接查询(条件:相同属性)
###### 4.分页和排序
````sql
/*
排序: ASC:ascending DESC:Descending
ORDER BY 某个字段
*/
SELECT DISTINCT s.studentno,studentname,subjectname,studentresult FROM
student s RIGHT JOIN result r
ON(s.studentno = r.studentno)
INNER JOIN `subject` sub ON(r.subjectno=sub.subjectno)
WHERE subjectname LIKE '数据库结构%'
ORDER BY studentresult ASC
/*
分页
*/
SELECT DISTINCT s.studentno,studentname,subjectname,studentresult FROM
student s RIGHT JOIN result r
ON(s.studentno = r.studentno)
INNER JOIN `subject` sub ON(r.subjectno=sub.subjectno)
WHERE subjectname = 'java程序设计-1' AND studentresult >=80
LIMIT 0,2
知识点:
- 排序:ORDER BY 某个字段 ASC DESC
- 分页:LIMIT 数据起始值,数据条数 推导一个固定公式
- 查询的基本结构
````sql
select distinct 字段1 as 别名,字段2 as 别名,字段3 as 别名,字段4 as 别名
from 表(inner join,left join,right join)表
on(连接条件)
where 条件判断
having 条件
order by 排序
limit x,datasize
4.常用函数
-- 常用函数
SELECT CEILING(5.6)
SELECT FLOOR(5.6)
SELECT RAND()
SELECT CURRENT_DATE()
SELECT CURDATE()
SELECT NOW()
SELECT LOCALTIME()
SELECT SYSDATE()
-- 聚合函数
-- count max min sum avg having(过滤条件)
5.md5加密
-- 加密md5()
-- 获取用户的账户,密码,传给数据库,执行sql语句,匹配返回,登录成功
6.sql总结
select distinct 字段1 as 别名,字段2 as 别名,字段3 as 别名,字段4 as 别名
from 表(inner join,left join,right join)表
on(连接条件)
where 条件判断
having 条件
order by 排序
limit x,datasize
事务
什么是事务
- 事务就是将一组SQL语句放在同一批次内去执行
- 如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行
- MySQL事务处理只支持InnoDB和BDB数据表类型
事务的ACID原则 百度 ACID
原子性(Atomic)
- 整个事务中的所有操作,要么全部完成,要么全部不完成,不可能停滞在中间某个环节。事务在执行过程中发生错误,会被回滚(ROLLBACK)到事务开始前的状态,就像这个事务从来没有执行过一样。
一致性(Consist)
- 一个事务可以封装状态改变(除非它是一个只读的)。事务必须始终保持系统处于一致的状态(事务前后状态一致),不管在任何给定的时间并发事务有多少。也就是说:如果事务是并发多个,系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an Invariant),以转账案例为例,假设有五个账户,每个账户余额是100元,那么五个账户总额是500元,如果在这个5个账户之间同时发生多个转账,无论并发多少个,比如在A与B账户之间转账5元,在C与D账户之间转账10元,在B与E之间转账15元,五个账户总额也应该还是500元,这就是保护性和不变性。
隔离性(Isolated)
- 事务的执行不受其他事务的干扰,事务执行的中间结果对其他事务必须是透明的。通俗的来讲就是,多个事务并发执行的时候,一个事务不影响另一个事务的操作。比如要查看数据更新的状态,那么事务A将查看的数据是事务B更新之前或更新之后的数据,不会看到该数据中间的状态。
账户A有1000元,账户B有1000元,账户C有1000元。在同一事务中,账户A给账户B转账200元,账户C给账户B转账100元。那么事务A去查询账户B的余额,只会看到账户B的余额为转账之前的1300元或者是转账之前的1000元,不会看到其他的结果。 - 1、脏读:事务A读取了事务B中没有提交的数据。
栗子:
哈哈,还是转账的栗子。账户A中有1000元,开始事务A,账户A取走200元,没有提交,这时候切换到事务B中,事务B查看账户A余额,此时查看到账户A中的余额还是1000元,因为事务A没有提交,所以产生了脏读。 - 2、不可重复读:在一个事务里面读取了两次某个数据,读出来的数据不一致。
栗子:
还是这个栗子。事务A开启事务,查看账户A中的余额为1000元,此时切换到事务B中,事务B开启事务,对取走账户A中的200元,提交。此时切换到事务A中,查看到账户A余额为800元,事务A两次读取到的数据不一致。这就是不可重复读。 - 3、幻读:在一个事务里面的操作中发现了未被操作的数据(读取了其他数据,多了一行数据)。
栗子:
现在咱们换个栗子,事务A开启事务对A班的学生缴纳学费情况列表都改为已缴纳然后提交,切换到事务B中,给A班的学生列表中增加一个新入学的新生,这个学生当然是没有缴纳学费的了,然后提交,此时在切换回事务A中,查看学生缴纳学费信息情况,发现有一个学生没有缴纳,这就是幻读,就跟产生了幻觉一样。
持久性(Durable)
- 在事务完成以后(一旦提交),该事务对数据库所作的更改便持久的保存在数据库之中,并不会被回滚。
事务的隔离级别
事务的隔离级别
数据库的隔离级别有4个,由高到低分别是:
读未提交(Read uncommitted)、读已提交(Read committed)、可重复读(Repeatable read)、序列化(Serializable)。这四个隔离级别可以解决脏读,不可重复读和幻读等问题。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lfnqLlpv-1621264885956)(C:\Users\LCJ\AppData\Roaming\Typora\typora-user-images\image-20210517231237479.png)]
隔离级别和并发性能的关系:
事务的隔离级别越高,他们的并发性能越差
Mysql查看当前隔离级别
select @@tx_isolation;
mysql设置隔离级别
set session transaction isolation level 隔离级别
基本语法
-- 使用set语句来改变自动提交模式
SET autocommit = 0; /*关闭*/
SET autocommit = 1; /*开启*/
-- 注意:
--- 1.MySQL中默认是自动提交
--- 2.使用事务时应先关闭自动提交
-- 开始一个事务,标记事务的起始点
START TRANSACTION
-- 提交一个事务给数据库
COMMIT
-- 将事务回滚,数据回到本次事务的初始状态
ROLLBACK
-- 还原MySQL数据库的自动提交
SET autocommit =1;
-- 保存点
SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点
测试
/*
课堂测试题目
A在线买一款价格为500元商品,网上银行转账.
A的银行卡余额为2000,然后给商家B支付500.
商家B一开始的银行卡余额为10000
创建数据库shop和创建表account并插入2条数据
*/
CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;
CREATE TABLE `account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`cash` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO account (`name`,`cash`)
VALUES('A',2000.00),('B',10000.00)
-- 转账实现
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION; -- 开始一个事务,标记事务的起始点
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- 提交事务
# rollback;
SET autocommit = 1; -- 恢复自动提交
索引
索引的作用
- 提高查询速度
- 确保数据的唯一性
- 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
- 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
- 全文检索字段进行搜索优化.
分类
- 主键索引 (Primary Key)
- 唯一索引 (Unique)
- 常规索引 (Index)
- 全文索引 (FullText)
主键索引
主键 : 某一个属性组能唯一标识一条记录
特点 :
- 最常见的索引类型
- 确保数据记录的唯一性
- 确定特定数据记录在数据库中的位置
唯一索引
作用 : 避免同一个表中某数据列中的值重复
与主键索引的区别
- 主键索引只能有一个
- 唯一索引可能有多个
CREATE TABLE `Grade`(
`GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,
`GradeName` VARCHAR(32) NOT NULL UNIQUE
-- 或 UNIQUE KEY `GradeID` (`GradeID`)
)
常规索引
作用 : 快速定位特定数据
注意 :
- index 和 key 关键字都可以设置常规索引
- 应加在查询找条件的字段
- 不宜添加太多常规索引,影响数据的插入,删除和修改操作
CREATE TABLE `result`(
-- 省略一些代码
INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- 创建表时添加
)
-- 创建后添加
ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);
全文索引
百度搜索:全文索引
作用 : 快速定位特定数据
注意 :
- 只能用于MyISAM类型的数据表
- 只能用于CHAR , VARCHAR , TEXT数据列类型
- 适合大型数据集
/*
#方法一:创建表时
CREATE TABLE 表名 (
字段名1 数据类型 [完整性约束条件…],
字段名2 数据类型 [完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[索引名] (字段名[(长度)] [ASC |DESC])
);
#方法二:CREATE在已存在的表上创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (字段名[(长度)] [ASC |DESC]) ;
#方法三:ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名 (字段名[(长度)] [ASC |DESC]) ;
#删除索引:DROP INDEX 索引名 ON 表名字;
#删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;
#显示索引信息: SHOW INDEX FROM student;
*/
/*增加全文索引*/
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`StudentName`);
/*EXPLAIN : 分析SQL语句执行性能*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';
/*使用全文索引*/
-- 全文搜索通过 MATCH() 函数完成。
-- 搜索字符串作为 against() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行,MATCH() 返回一个相关性值。即,在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。
EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');
/*
开始之前,先说一下全文索引的版本、存储引擎、数据类型的支持情况
MySQL 5.6 以前的版本,只有 MyISAM 存储引擎支持全文索引;
MySQL 5.6 及以后的版本,MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
测试或使用全文索引时,要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。
*/
拓展:测试索引
建表app_user:
CREATE TABLE `app_user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT '' COMMENT '用户昵称',
`email` varchar(50) NOT NULL COMMENT '用户邮箱',
`phone` varchar(20) DEFAULT '' COMMENT '手机号',
`gender` tinyint(4) unsigned DEFAULT '0' COMMENT '性别(0:男;1:女)',
`password` varchar(100) NOT NULL COMMENT '密码',
`age` tinyint(4) DEFAULT '0' COMMENT '年龄',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'
批量插入数据:100w
DROP FUNCTION IF EXISTS mock_data;
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`, `email`, `phone`, `gender`, `password`, `age`)
VALUES(CONCAT('用户', i), '24736743@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
SET i = i + 1;
END WHILE;
RETURN i;
END;
SELECT mock_data();
索引效率测试
无索引
SELECT * FROM app_user WHERE name = '用户9999'; -- 查看耗时
SELECT * FROM app_user WHERE name = '用户9999';
SELECT * FROM app_user WHERE name = '用户9999';
mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 992759
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
创建索引
CREATE INDEX idx_app_user_name ON app_user(name);
测试普通索引
mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: ref
possible_keys: idx_app_user_name
key: idx_app_user_name
key_len: 203
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)
mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)
mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)
索引准则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表建议不要加索引
- 索引一般应加在查找条件的字段
索引的数据结构
-- 我们可以在创建上述索引的时候,为其指定索引类型,分两类
hash类型的索引:查询单条快,范围查询慢
btree类型的索引:b+树,层数越多,数据量指数级增长(我们就用它,因为innodb默认支持它)
-- 不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务,支持行级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引;
MyISAM 不支持事务,支持表级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引;
Memory 不支持事务,支持表级别锁定,支持 B-tree、Hash 等索引,不支持 Full-text 索引;
NDB 支持事务,支持行级别锁定,支持 Hash 索引,不支持 B-tree、Full-text 等索引;
Archive 不支持事务,支持表级别锁定,不支持 B-tree、Hash、Full-text 等索引;
JDBC
JDBC认识
为什么要学习JDBC
背景:不同的数据库需要不同的数据库驱动进行–开发复杂
为了减化开发人员统一操作数据库的操作,提出了一种操作数据库的规范(jdbc)
应用程序–>JDBC(开发人员)—>相应的数据库的驱动(相应的厂家的实现)---->数据库
连接用于与数据库建立连接,操作数据库
JDBC的概念
Java数据库连接,(Java Database Connectivity,简称JDBC)是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口,提供了诸如查询和更新数据库中数据的方法。JDBC也是Sun Microsystems的商标。我们通常说的JDBC是面向关系型数据库的。
jdbc的使用
基本步骤
-
加载数据库驱动
-
传入相应信息获得数据库对象–Connection
-
通过数据库对象获得操作sql的对象–Statement
-
通过Statement的相应方法执行sql
-
关闭资源
-
注意点
useSSL=false
public class JdbcText1 {
/*
JDBC测试:
执行sql的对象:statement
statement.execute();执行任何sql,效率低
statement.executeUpdate();更新,插入,删除,返回受影响的行数
statement.executeQuery();执行搜索操作
结果集(封装了所有执行sql的数据):ResultSet---更具字段类型选择
resultSet.getObject();
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getBoolean();
resultSet.next() --指针,移动到下一个数据
*/
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取用户信息,账户,密码(添加参数?)
String url= "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&&useSSL=false";
String name="root";
String password="123456";
//3.连接数据库,成功获得数据库对象--数据库层面的操作
Connection connection = DriverManager.getConnection(url, name, password);
//4.获得执行sql语句的对象 statement:可执行sql的对象
Statement statement = connection.createStatement();
String sql = "SELECT * FROM users";
//5.执行sql语句
ResultSet resultSet = statement.executeQuery(sql);//返回一个结果集(对象)
while(resultSet.next()){
System.out.println("id"+resultSet.getObject("id"));
}
//6.释放连接
resultSet.close();
statement.close();
connection.close();
}
}
JDBC配置类
只要使用数据库就要进行基本步骤,可以把不变的操作创建一个工具类,想要连接就使用工具类
public class Jdbcutils {
/**
* 创建一个工具类:jdbc固定配置
* 1.加载驱动
* 2.与数据库建立连接,得到数据库对象
* 3.释放资源
*/
static String driver = null;
static String url = null;
static String username = null;
static String password = null;
static {
try{
InputStream resourceAsStream =Jdbcutils.class.getClassLoader().getResourceAsStream("db.properties");
//配置类去加载配置文件
Properties properties = new Properties();
properties.load(resourceAsStream);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
Class.forName(driver);
}catch(IOException | ClassNotFoundException E){
E.printStackTrace();
}
}
//建立连接获取数据库对象
public static Connection connection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
//释放连接
public static void release(Connection con, ResultSet re, Statement st) throws SQLException {
if(con!=null){
con.close();
}
if(re!=null){
re.close();
}
if(st!=null){
st.close();
}
}
}
测试
public class UpdtaUtil {
/**
* update工具类:实现更新操作
*/
public static void main(String[] args) throws SQLException {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = Jdbcutils.connection();
statement = connection.createStatement();
String update ="UPDATE users SET `password` = 12345678 WHERE id = 2";
String insert ="INSERT INTO users(id,`name`,`password`,email,birthday) \n" +
"VALUES(5,'zahngjun',1234546,'1297474907@qq.com','2020-01-01')\n";
int i = statement.executeUpdate(update);
int i1 = statement.executeUpdate(insert);
if(i>0){
System.out.println("修改成功");
}
if(i1>0){
System.out.println("插入成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
Jdbcutils.release(connection,null,statement);
}
}
}
步骤都是固定的,变化的是sql语句和执行sql语句的方法
SQL注入
- sql注入:通过修改sql(拼接)语句访问数据库数据
- 解决:PreparedStatement(有准备的执行sql的对象)
public class BusinessText {
/**
* 业务测试:
* 1.登录业务:输入账户名和密码 与数据库匹配(sql搜索)
* 问题:sql注入:通过修改sql(拼接)语句访问数据库数据
* 所谓SQL注入,就是通过把SQL命令插入到Web表单提交或输入域名或页面请求的查询字符串
* 最终达到欺骗服务器执行恶意的SQL命令
* 它是利用现有应用程序,将(恶意的)SQL命令注入到后台数据库引擎执行的能力
* 它可以通过在Web表单中输入(恶意)SQL语句得到一个存在安全漏洞的网站上的数据库
* 解决:
* PreparedStatement
*
*/
public static void main(String[] args) throws SQLException {
BusinessText bt = new BusinessText();
bt.login("'' or '1=1'",123456);
}
public void login(String name,int password) throws SQLException {
Connection connection = null;
PreparedStatement preparest = null;
ResultSet resultSet = null;
try {
connection = Jdbcutils.connection();
//SELECT * FROM users
//WHERE `name` = 'zhansan' AND `password` = 123456
String select = "select * from users where`name`=? AND `password`=?";
System.out.println(select);
//预编译,不执行
preparest = connection.prepareStatement(select);
//PreparedStatement我、防止sql注入的本质:将传入的参数当做字符
//如果字符中存在转义字符,会被直接转义('--忽略)
preparest.setString(1,name);
preparest.setInt(2,password);
resultSet = preparest.executeQuery();
while(resultSet.next()) {
System.out.println(resultSet.getString("name"));
System.out.println(resultSet.getInt("password"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
Jdbcutils.release(connection,resultSet,preparest);
}
}
}
IDEA连接数据库
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JIMRklJI-1621264885958)(C:\Users\LCJ\AppData\Roaming\Typora\typora-user-images\image-20210517223201979.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CXMX85tc-1621264885960)(C:\Users\LCJ\AppData\Roaming\Typora\typora-user-images\image-20210517223246869.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cC4cATZ9-1621264885961)(C:\Users\LCJ\AppData\Roaming\Typora\typora-user-images\image-20210517223315656.png)]
JDBC操作事务
public class TransactionText {
public static void main(String[] args) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
connection = Jdbcutils.connection();
//关闭自动提交,开启事务
connection.setAutoCommit(false);
String u1 ="UPDATE account SET money = money-500 WHERE `name` = 'A'";
String u2 = "UPDATE account SET money = money+500 WHERE `name` = 'B'";
//预编译
statement = connection.prepareStatement(u1);
statement.executeUpdate();
int i = 1/0;
statement = connection.prepareStatement(u2);
statement.executeUpdate();
connection.commit();
System.out.println("成功");
} catch (SQLException throwables) {
connection.rollback();
throwables.printStackTrace();
} finally {
Jdbcutils.release(connection,null,statement);
}
}
}
数据库连接池
SQL 执行步骤
SQL 执行会有三个步骤:数据库连接 → 执行完毕 → 释放,连接 → 释放这个过程十分浪费系统资源。
池化技术
池化技术就是准备一些预先的资源,执行时直接连接预先准备好的资源。类似于银行的多个业务窗口,安排多个业务员给客户服务。
最小连接数
最小连接数 = 常用连接数。连接池中的最小连接数就类似于银行最少安排多少个业务员。
最大连接数
最大连接数 = 业务最高承载上限。连接池中的最大连接数就类似于银行所有窗口都安排了业务员。
等待超时
如果客户等待了设定的超时时间,就通知客户不要再等待。
-
使用数据库连接池
使用一个数据库连接池,只需要实现 DataSource 接口即可。
市面上的开源数据源实现:DBCP、C3P0、Druid(阿里)。
使用了数据库连接池之后,在项目开发中就不需要编写连接数据库的代码了。 -
配置文件
-
使用那个池就要导入相应的配置文件和jar包
public class DbcpUtils { /** * */ private static DataSource dataSource = null; static { try{ InputStream resourceAsStream =Jdbcutils.class.getClassLoader().getResourceAsStream("dbc.properties"); //配置类去加载配置文件 Properties properties = new Properties(); properties.load(resourceAsStream); //基础数据源工厂 dataSource = BasicDataSourceFactory.createDataSource(properties); }catch(IOException | ClassNotFoundException E){ E.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } } public static Connection getConnection() throws SQLException { return dataSource.getConnection(); } //释放连接 public static void release(Connection con, ResultSet re, PreparedStatement st) throws SQLException { if(con!=null){ con.close(); } if(re!=null){ re.close(); } if(st!=null){ st.close(); } } }
总结
- 对于多次使用,步骤有不变的操作可以把他封装成一个工具类,我们使用这个工具类(静态代码块与类一起加载)就可以去实现基本操作
注意点和总结
-
数据库的基本命名行命名
-
SQL语句–增(insert)删(delete)改(update)查(select)
#sql查询的基本格式 select distinct 字段1 as 别名,字段2 as 别名,字段3 as 别名,字段4 as 别名 from 表(inner join,left join,right join)表 on(连接条件) where 条件判断 having 条件 order by 排序 limit x,datasize
-
事务的四个特性,隔离性带来的影响,解决方法
-
索引
-
JDBC