MySql
1.数据库(DB,DataBase)
-
JavaEE:企业级开发 web 前端+后端+数据库
-
前端:页面展示数据
-
后台:连接前端与数据库(JDBC)
-
数据库:存数据
-
数据库是所有软件体系中最核心的存在----DBA
-
数据仓库,软件,安装在操作系统之上
1.1数据库分类
-
关系型数据库:Excel 行列(SQL)
-
- Mysql,Oracle,Sql Server,DB2,SqlLite
- 表和表之间,行列之间的关系进行存储
-
非关系型数据库:(key,value)
-
- Redis,MongDB
- 对象存储,通过自身属性决定
- NOSQL NOT Only SQl
-
DBMS 数据库管理系统
-
- 科学有效的管理数据
- 维护和获取数据
-
MySql : 关系型数据库管理系统
-
Sql语言
-
最好的关系型数据库管理系统
-
开源
-
小,快!
1.2MySql安装下载
1.3Sqlyog安装使用
- 每一个sqlyog操作都对应历史记录
1.4连接数据库
- 命令行连接数据库
mysql -u root -p123456 --连接数据库 用户名:root 密码:123456
- CMD窗口所有语句 ; 结尾
- 查看所有数据库
show database ;-- 查看所有数据库
use *** ;--切换数据库
show tables;--查看所有的表
describe ***;--查看表信息
create database ***;--创建数据库
exit -- 推出连接
- DDL 数据库定义语言
- DML 操作
- DQL 查询
- DCL 控制
2.操作数据库 *
-
操作数据库
-
- 操作数据库中的表
-
- 操作表的数据
-
操作数据库:创建,删除,使用
create database [if not exist] **;--创建数据库
drop database [if exists] **; --删除数据库
- 学习中对照Sqlyog可视化历史记录
2.1列的数据类型
数值
- tinyint 特别小的 1个字节
- smallint 较小的 2个字节
- int 正常的 4个字节
- bigint 较大的 8个字节
- float 浮点数 4个字节
- double 浮点数 8个字节
- decimal 字符串式的浮点数 超精密计算(金融)
字符串
- char 字符串固定大小 0-255
- varchar 可变字符串 0-65535(String)
- tinytext 微型文本
- text 文本串(大) 保存大文本
时间日期
- date 日期
- time 时分秒
- datetime
- timestamp 1970.1.1到现在的毫秒数
- year
null
- null 避免进行运算
2.2数据库字段属性
- Unsigned 无符号整数,不能为负数
- Zerofill 不足的位数默认0填充
- 自增 自动+1,设置主键,整数类型
- 可以高级设置里设置主键起始值和步长max,min
- NULL , not NULL
- 默认:默认值
- 主键:正常必须有,唯一标识
2.3创建数据库表
- 使用sql创建
CREATE TABLE IF NOT EXISTS children (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT'id',
`name` VARCHAR(20) NOT NULL DEFAULT'匿名' COMMENT '姓名',
`pwd` VARCHAR(50) NOT NULL DEFAULT'123456' COMMENT'密码',
`sex` VARCHAR(2) NOT NULL DEFAULT'男' COMMENT'性别',
`birthday` DATETIME DEFAULT NULL COMMENT'出生日期',
`email` VARCHAR(20) NOT NULL DEFAULT'无' COMMENT'email',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
格式
create table [if not exists] 表名(
列名 字段类型 属性 索引 注释,
列名 字段类型 属性 索引 注释,
...
列名 字段类型 属性 索引 注释,
主键
)[字符编码]
SHOW CREATE DATABASE school -- 查看数据库建库信息
SHOW CREATE TABLE children -- 查看建表信息
DESC children -- 查看表结构
设置字符节编码
charset=utf-8
2.4MyISAM 与 InnoDB区别
-
关于数据库引擎
-
MyISAM :早些年使用的
-
- 不支持事务
- 不支持数据行锁定
- 不支持外键约束
- 支持全文索引
- 表空间较小
- 节省空间,速度快
-
InnoDB:默认
-
- 支持事务
- 支持行锁定
- 支持外键约束
- 不支持全文索引
- 表空间约为MyISAM两倍
- 安全性高,事务处理,多表多用户关联操作
-
数据库所有的文件本质上还是文件的存储(data目录下)
-
InnoDB在数据库表中只有一个*from文件,其他是备份,以及上级目录下的ibdata1文件
-
MyISAM:*from定义文件,*MYD数据文件(data),MYI索引文件(index)
2.5修改和删除表 alter
修改
-- 修改表名
ALTER TABLE student RENAME AS student1`school`
-- 增加表的字段
ALTER TABLE student1 ADD class INT(10)
-- 修改表的字段
ALTER TABLE student1 MODIFY age VARCHAR(33) -- 修改表的字段约束
ALTER TABLE student1 CHANGE age age1 INT(5) -- 修改字段名
删除
-- 删除
ALTER TABLE student1 DROP class -- 删除字段
DROP TABLE IF EXISTS student1 -- 删除表
3.MySql数据处理
3.1 数据库级别的外键
方式一,在创建表时候,增加约束
-- 学生表年级字段引用年级表
-- 年级表
CREATE TABLE grade(
gradeId INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级',
gradeName VARCHAR(10) NOT NULL COMMENT'名称',
PRIMARY KEY(gradeId)
)ENGINE = INNODB DEFAULT CHARSET=utf8
-- 学生表
CREATE TABLE student(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT'id',
`name` VARCHAR(20) NOT NULL DEFAULT'匿名' COMMENT '姓名',
`pwd` VARCHAR(50) NOT NULL DEFAULT'123456' COMMENT'密码',
gradeId INT(10) NOT NULL COMMENT '年级',
`sex` VARCHAR(2) NOT NULL DEFAULT'男' COMMENT'性别',
`birthday` DATETIME DEFAULT NULL COMMENT'出生日期',
`email` VARCHAR(20) NOT NULL DEFAULT'无' COMMENT'email',
PRIMARY KEY(`id`),
KEY FK_gradeId (gradeId), -- 定义外键key
-- 给外键添加约束 表内gradeId字段 引用(reference) grade表内的gradeId字段
CONSTRAINT FK_gradeId FOREIGN KEY (gradeId) REFERENCES grade(gradeId)
)ENGINE=INNODB DEFAULT CHARSET=utf8
- 删除有外键关系的表(grade表)时,必须先删除引用的表(student表)
方式二,创建表成功后添加外键约束
-- 创建表的时候没有外键关系
ALTER TABLE student
ADD CONSTRAINT FK_gradeId FOREIGN KEY(gradeId) REFERENCES grade (gradeId)
- 数据库级别的外键 不建议使用,避免数据库过多
最佳实践
- 数据库就是单纯的表,只用来存数据
- 想用多张表的数据,去程序实现使用外键
3.2 Insert 语句
DML 语言 – 数据操作语言
- Insert
- Update
- Delete
- Select
-- 保证数据跟字段一一对应
-- 注意非空无默认字段
INSERT INTO grade (gradeName) VALUES ('大班')
INSERT INTO grade (gradeName)
VALUES ('小班'),('大班')
3.3 Update 语句
update 修改谁 (条件) set 原来的值 = 新值
-- 指定条件 必须带!
UPDATE grade SET gradeName = '一年级' WHERE gradeId = 2
-- 没条件 全改
UPDATE grade SET gradeName = '二年级' WHERE gradeName = '一年级'
3.4 Delete 语句
delete from 表明 where 条件
- 删除表 优选 truncate
TRUNCATE grade
DELETE FROM grade
-
delete 与 truncate 区别
-
- 都能删除数据,且不修改表结构
- Truncate 重新设置自增列,子增量 计数器归零
- Truncate 不会影响事务
- delete 不影响自增
-
delete 删除 ,重启数据库
-
- InnoDB 自增列从1开始(存在内存中,断电即失)
- MyISAM 继续从子增量开始 (存在文件中,丢不了)
3.4 Select 语句
- DQL 查询数据:数据查询语言
- 数据库中最核心的语言,最重要的,使用频率最高的
Select 找啥? FROM 在哪?Where 条件?
SELECT * FROM grade -- 查询所有数据
SELECT gradeId,gradeName FROM grade -- 查询指定字段
- 别名 , 给结果起个名字 AS 表名和字段名都可以起别名
SELECT gradeId AS 序号 , gradeName AS 年级 FROM grade
SELECT gradeId AS 序号 , gradeName AS 年级 FROM grade AS G
- 拼接字符串
SELECT CONCAT('年级名:',gradeName) AS 新名字 FROM grade
-
select 啥都能查
-
-
SELECT VERSION() -- 查询版本号 SELECT 100*3+1 AS 计算结果 -- 数学运算 SELECT @@auto_increment_increment -- 查询自增步长(变量)
-
3.5 去重 distinct
- distinct 去重
- all 全部数据
SELECT DISTINCT gradeName FROM grade
SELECT ALL gradeName FROM grade
3.6 where 子句
-
检索数据中符合条件的值
-
尽量使用英文符号-and = && , or = || , != / not
3.7 模糊查询
- sql里面运算符不一定是符号
比较运算符
- like a like b a匹配到b,结果为真
- in a in b a在b里面某一个,结果为真
-- 查询名字只有一个吴的
SELECT NAME FROM children WHERE NAME LIKE '吴'
-- 查询名字姓吴的,吴*****
SELECT NAME FROM children WHERE NAME LIKE '吴%'
-- 查询名字姓吴的,且后面只有一个字
SELECT NAME FROM children WHERE NAME LIKE '吴_'
-- 查询名字姓吴的,且后面只有两个字
SELECT NAME FROM children WHERE NAME LIKE '吴__'
-- 查询名字中间有阳的
SELECT NAME FROM children WHERE NAME LIKE '%阳%'
-- 查询名字里这俩人
SELECT * FROM children WHERE NAME IN ('吴签','欧阳娜娜')
-- is null ,is not null
SELECT * FROM children WHERE birthday ='' OR birthday IS NULL
SELECT * FROM children WHERE birthday ='' OR birthday IS NOT NULL
- 不能写= null 要写 is null
- 通配符只能在like中使用
3.8 连表查询 joinON
join 对比
-- 表中有匹配的就行
SELECT * FROM children
INNER JOIN grade
WHERE children.`id` = grade.`gradeId`
-- 左查询,返回左表中所有值,即使右表没有匹配
SELECT id , NAME , email , gradename FROM children
LEFT JOIN grade
ON children.`id` = grade.`gradeId`
-- 右查询,返回右表中所有值,即使左表没有匹配
SELECT id , NAME , email , gradename FROM children
RIGHT JOIN grade
ON children.`id` = grade.`gradeId`
-- 查那些数据 select...
-- 从哪些表查 查 from 表 xxx join 连接的表
-- on 判断条件
-- 多张表时,慢慢来两张两张查
-
左右查询中on 后的匹配结果不影响是否显示,不匹配顶多是null
-
join on 连接查询
-
where 等值查询
select 找啥 frm (左表名) join (连接的右表名) on (条件)
3.9 自连接
- 自己的表和自己的表连接
- 核心思想:给表或字段起不同的别名那就是两份数据
SELECT a.name AS 父表 , b.name AS 子表
FROM children AS a,children AS b
WHERE a.id = b.pid
3.10 分页和排序
- limit order By
Order By 排序
SELECT id , NAME , gradeName , sex FROM children
INNER JOIN grade
ON children.`id` = grade.`gradeId`
ORDER BY id DESC -- 降序
SELECT id , NAME , gradeName , sex FROM children
INNER JOIN grade
ON children.`id` = grade.`gradeId`
ORDER BY id ASC -- 升序
limit 分页
-
用户体验好
-
瀑布流----无限加载,没有分页 抖音/快手-无限划没有尽头,刷视频
-
所有语句的最后一个
SELECT id , NAME , gradeName , sex FROM children
INNER JOIN grade
ON children.`id` = grade.`gradeId`
ORDER BY id ASC
LIMIT 0,2 -- limit 起始值,每页显示多少行数据
-- limit (pageNum = pageSize-(n-)*pageSize,pageSize)
3.11 子查询和嵌套查询
- where(值是计算出来的)
- 本质:where 中嵌套一个查询语句
SELECT id , NAME , gradeName , sex FROM children
WHERE gradeName = (
SELECT gradeName FROM grade WHERE gradeId > 1
)
3.12 MySql 常用函数 *
-- 数学函数
SELECT ABS(-1) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 0-1 随机数
SELECT SIGN(71) -- 判断一个数的符号,整数返回1,负数返回-1 ,0-0
-- 字符串函数
SELECT CHAR_LENGTH('emmmmmmmmm') -- 字符串长度
SELECT CONCAT('小','阔','爱') -- 拼接字符串
SELECT INSERT('俺爱编程',2,1,'超级热爱') -- 从第二个字符开始替换一个长度
SELECT LOWER('ssdfsfASFSA') -- 转小写
SELECT UPPER('fdsaWSDFGF') -- 转大写
SELECT INSTR('lxxxy','y') -- 第一次出现字串的索引
SELECT REPLACE('坚持就能去找大哥玩','坚持','努力') -- 指定替换字符串
SELECT SUBSTR('坚持就能去找大哥玩',3,5) -- 从第三个开始截取五个
SELECT REVERSE('asdfg') -- 反转字符串
-- 时间日期
SELECT CURRENT_DATE() -- 当前日期
SELECT NOW() -- 当前时间
SELECT LOCALTIME() -- 本地时间
SELECT SYSDATE() -- 系统时间
SELECT CURDATE() -- 当前时间
-- 系统
SELECT USER() -- 用户
SELECT VERSION() -- 版本
3.13聚合函数及分组过滤
- count() -计数
SELECT COUNT(NAME) FROM children -- 指定列,忽略 null
SELECT COUNT(*) FROM children -- 不忽略 null 查找所有行数
SELECT COUNT(1) FROM children -- 不忽略 null 所有字段变成1,查所有行数,快!
-
sum() -求和
-
avg() -平均值
-
max() -最大值
-
min() -最小值
-
group by 通过**字段分组
-
select 语法 优先级
-
- inner join – 联合查询
- where – 条件 一个查询语句只能有一个
- group by – 按哪几个字段分组
- having – 过滤一个或多个条件,也是where
- order by – 排序
- limt – 分页
3.14 数据库级别的MD5 加密
- 增强算法复杂度,不可逆!
- MD5破解原理,背后有字典,只有简单的
CREATE TABLE testMd5(
id INT(4) NOT NULL,
NAME VARCHAR(10) NOT NULL,
pwd VARCHAR(100) NOT NULL,
PRIMARY KEY (id)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO testMd5 VALUES(1,'zhangsan','123445'),(2,'赵四','emmmem'),(3,'王老五','laasf')
-- 先插入再加密加密
UPDATE `testmd5` SET pwd=MD5(pwd)
-- 插入的时候加密
INSERT INTO testMd5 VALUES(4,'qianlaoliu',MD5('2333'))
-- 查询要输入明码
SELECT pwd FROM testMd5 WHERE pwd=MD5('2333')
4.事务
4.1 ACID原则,脏读,不可重复读,幻读
什么是事务?
- 将一组sql放在一个批次执行
事务原则 - ACID 原子性,一致性,隔离性,持久性 -脏读,幻读
- 原子性:要么都成功,要么都失败,不能只发生一部分动作
- 一致性:结果一致性:事务前后的数据完整性保持一致
- 持久性:事务结束后不会因为外界原因使数据丢失,没提交将恢复到原状,提交后持久化到数据库,不可逆
- 隔离性:多个用户操作一个数据需要隔离
- 脏读:一个事务读取到了另外一个事务未提交的数据
- 不可重复读:一个事务内重复读取表数据,数据发生变化
- 幻读:事务中读取到了其他事务读取到了其他事务更新的数据
4.2 测试事务实现转账
- mysql默认开启事务自动提交
-- mysql默认开启事务自动提交
SET autocoment = 0 -- 关闭
SET autocoment = 1 -- 开启(默认)
-- 事务开启
START TRANSACTION -- 标记一个事务的开启
-- 提交-持久化
COMMIT
-- 回滚
ROLLBACK
-- 事务结束
SET autocommt = 1 -- 开启(默认)
SAVEPOINT -- 设置一个保存点
ROLLBACK TO SAVEPOINT -- 回到保存点(打游戏存档,回滚)
RELEASE TO SAVEPOINT -- 撤销保存点
-- 手动处理事务
SET autocommit = 0 -- 关闭 1
START TRANSACTION -- 标记一个事务的开启 2
COMMIT -- 成功 3
ROLLBACK -- 失败
SET autocommit = 1 -- 开启(默认) 4
-- 模拟场景-转账
CREATE TABLE account(
id INT(3) NOT NULL AUTO_INCREMENT,
NAME VARCHAR(10) NOT NULL,
money DECIMAL(9,2) NOT NULL,
PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO account(NAME,money)
VALUES('A',2000.00),('B',1000.00)
SET autocommit = 0; -- 关闭
START TRANSACTION -- 标记一个事务的开启 -- 完整的一个事务
UPDATE account SET money=money-500
WHERE NAME = 'A'
UPDATE account SET money=money+500
WHERE NAME = 'B'
COMMIT; -- 提交
ROLLBACK; -- 回滚
SET autocommit = 1;
5.索引
5.1 索引及其分类
- 索引:帮助Mysql高效获取数据的数据结构,提取句子主干,可以得到索引的本质,索引是数据结构
索引的分类
-
主键索引:primary key
-
- 唯一标识,不可重复,只能有一个列作为主键
-
唯一索引:unique key
-
- 避免重复列出现,唯一索引可以重复,多个列可以表示为唯一索引
-
常规索引:key,index
-
- 默认的
-
全文索引:fulltext
-
- 在特定的数据库引擎才有,MyISAM
- 快速定位数据
-
创建表时增加索引或创建完毕增加索引
SET autocommit = 1;
-- 显示表所有索引信息
SHOW INDEX FROM student
-- 增加全文索引 索引名(列名)
ALTER TABLE school.student ADD FULLTEXT INDEX pwd(pwd)
-- EXPLAIN 分析sql执行状况
EXPLAIN SELECT * FROM student -- 非全文索引
SELECT * FROM student WHERE MATCH(pwd) AGAINST('1') -- 全文索引
5.2 超大数据测试索引
- 索引在小数据无意义,只有在庞大数据量面前才会有效
-- 创建索引测试用户表
CREATE TABLE `user`(
id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT'id',
NAME VARCHAR(20) NOT NULL COMMENT'姓名',
email VARCHAR(15) NOT NULL COMMENT'email',
phone VARCHAR(20) NOT NULL COMMENT'电话',
gender INT(2) NOT NULL COMMENT'性别 1/2',
PASSWORD VARCHAR(50) NOT NULL COMMENT'密码',
age INT(5) NOT NULL COMMENT'年龄',
PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 单条插入语句
INSERT INTO `user`(NAME,email,phone,gender,PASSWORD,age)
VALUES(CONCAT('用户',i),
'123.com',
CONCAT('18',FLOOR(RAND()*((99999999-1000000)+1000000))),
FLOOR(RAND()*2),
UUID(),
FLOOR(RAND()*100)
)
-- 插入100w条数据的函数
DELIMITER $$ -- 写函数之前必写
CREATE FUNCTION mock_data() -- 函数名
RETURNS INT -- 返回值
BEGIN -- 函数体
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i<num DO
-- 插入语句
INSERT INTO `user`(NAME,email,phone,gender,PASSWORD,age)
VALUES(CONCAT('用户',i),
'123.com',
CONCAT('18',FLOOR(RAND()*((99999999-1000000)+1000000))),
FLOOR(RAND()*2),
UUID(),
FLOOR(RAND()*100)
);
SET i = i +1;
END WHILE;
RETURN i;
END;
-- 方法调用
SELECT mock_data()
SELECT * FROM USER WHERE NAME LIKE '%999'
SELECT * FROM USER WHERE NAME = '用户884866'
SELECT * FROM USER
EXPLAIN SELECT * FROM USER WHERE NAME = '用户884866'
-- id_表名_字段名
CREATE INDEX id_app_user_name ON USER(NAME)
-- 添加完索引继续上面查询
-- 唯一定位,不用遍历
5.3 索引原则
- 索引不是越多越好
- 不对经常变动的数据加索引
- 小数据靠边别瞎搞,起码也得几百万条数据
- 一般加在常用来查询到字段上
hash 类型的索引
btree :innodb 默认的类型
- 度娘直接搜:mysql索引背后的数据结构及算法原理
6. 权限管理与备份 *
6.1 数据库用户管理
Sqlyog 可视化管理
-
创建新用户:localhost与127.0.0.1 有强制要求
-
对初始mysql数据库中user表进行增删改查
-
root 用户拥有最高权限
6.2 数据库备份
- 保证重要的数据不丢失
- 数据转移
- 备份方法
-
拷贝data文件
-
在sqlyog可视化工具中手动导出 – 结构跟数据都带走
-
cmd命令行导出 – mysqldump -localhost -u -p 库 表
-
导入 – source (先登录)
7. 数据库设计 *
7.1 规范数据库设计
- 当数据库较复杂时,需要设计
- mysql数据库不区分大小写,没有驼峰命名,下划线处理
糟糕的设计
- 数据沉余,浪费空间
- 数据插入删除麻烦,异常(外键问题)
- 程序性能差
良好的数据库设计
- 节省空间
- 保证数据完整性
- 方便开发系统
软件开发中,数据库的设计
- 分析需求
- 概要设计:设计关系图(E-R图)
设计步骤 (个人博客)
- 收集信息,需求分析
-
- 用户表
- 分类表(文章分类,文章作者)
- 文章表(文章信息)
- 自定义表 -系统信息,某个关键字
- 评论表
- 标识实体—需求落到每个关键字
- 标识实体间关系:表之间相互关联
7.2 三大范式
- 关系型数据库设计六大范式,前三个就够了
第一范式
- 原子性,保证每一列不可再分
第二范式
- 满足第一范式
- 一张表只能描述一件事
- 每一列与主键直接相关
第三范式
- 满足一二范式
- 消除传递依赖,有关联地方就行
规范性和性能问题
- 关联查询不能太多表
- 性能更加重要!
- 故意给某些表增加一些冗余字段
- 故意增加一些计算列 --从大数据量降低为小数据量的计算 – 索引(占内存)
8.JDBC-------------重点
10.1 数据库驱动
- 驱动:声卡,显卡,数据库
- JDBC:java操作数据库的规范
- 这些规范的实现由具体的厂商去做
- 对于开发人员来说只需掌握jdbc接口即可
- 没有什么是加一层解决不了的
- 需要导入数据库驱动包
10.2 第一个JDBC程序
- 导入mysql-connector包,版本最好与数据库版本一致
连接步骤
- 加载驱动
- 准备用户信息
- 连接数据库
- 获得数据库对象
- 获得sql执行对象
- 操作数据库
- 释放连接
import java.sql.*;
//第一个jdbc程序
public class JdbcDom1 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver"); // 固定写法
//2.用户信息,url--记住固定写法
String url = "jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=false";
String username = "root";
String pwd = "lxxxy";
//3.连接成功 数据库对象--connection
Connection connection = DriverManager.getConnection(url, username, pwd);
//4.执行sql对象 Statement执行sql对象
Statement statement = connection.createStatement();
String sql = "SELECT * FROM children";
ResultSet resultSet = statement.executeQuery(sql); // 返回的结果集,封装了全部查询结果
while (resultSet.next()){
System.out.println(resultSet.getObject("id"));
System.out.println(resultSet.getObject("name"));
System.out.println(resultSet.getObject("pwd"));
System.out.println(resultSet.getObject("sex"));
System.out.println(resultSet.getObject("birthday"));
System.out.println(resultSet.getObject("email"));
}
//5.释放连接
resultSet.close();
statement.close();
connection.close();
}
}
10.3 jdbc中的对象解释
import java.sql.*;
//第一个jdbc程序
public class JdbcDom1 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//原来的写法不推荐,里面就是一个静态代码块,代码块已经可以完成加载驱动,forname就能直接拿到,没必要用registerDriver方法注册两次
Class.forName("com.mysql.jdbc.Driver"); // 固定写法
//2.用户信息,url--记住固定写法
//url组成公式---jdbc:mysql://localhost:3306/school ? 后面是连接参数
// jdbc: 协议 主机 端口号 数据库 ? 参数1 & 参数2&...
//oracel---端口号 1521 *
String url = "jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=false";
String username = "root";
String pwd = "lxxxy";
//3.连接成功 数据库对象--connection
Connection connection = DriverManager.getConnection(url, username, pwd);
//connection代表数据库对象,主要操作数据库拿到数据库基本信息
//4.执行sql对象 Statement执行sql对象
Statement statement = connection.createStatement();
//执行sql的对象
//statement.executeQuery(); //执行查询语句 返回resultset
//statement.execute() ; //执行所有sql 有个判断过程:查询还是更新? 效率低一点
//statement.executeUpdate(); //执行更新,修改,删除,返回影响的行数
// 编写具体的sql
String sql = "SELECT * FROM children";
// 结果集对象,只有查询才有,封装所有查询结果
ResultSet resultSet = statement.executeQuery(sql); // 返回的结果集,封装了全部查询结果
//获得结果集,可以拿到指定类型的数据,不知道啥数据类型情况下用万能Object
//resultSet.getString();
//resultSet.getInt();
//resultSet.getLong();
//resultSet.getObject();
//resultSet.next() 指针:指针光标向前移动,也就是遍历下一个
//resultSet.beforeFirst(); 移动到最前面
//resultSet.afterLast(); 移动到最后面
//resultSet.absolute(); 移动到指定位置
while (resultSet.next()){
System.out.println(resultSet.getObject("id"));
System.out.println(resultSet.getObject("name"));
System.out.println(resultSet.getObject("pwd"));
System.out.println(resultSet.getObject("sex"));
System.out.println(resultSet.getObject("birthday"));
System.out.println(resultSet.getObject("email"));
}
//5.释放连接---必须释放,连接特别耗费资源
resultSet.close();
statement.close();
connection.close();
}
}
10.4 Statement对象
-
提取工具类—每次执行sql语句要变动的只有sql与Statement对象
-
准备sql语句
-
连接
-
src目录下db.properties文件
driver = com.mysql.jdbc.Driver
url=jdbc:mysql:localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSLL=false
username = root
password = *****
- util配置文件
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 {
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 (IOException | ClassNotFoundException 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) throws SQLException {
if(rs!=null){
rs.close();
}
if(st!=null){
st.close();
}
if(conn!=null){
conn.close();
}
}
}
- 测试连接
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import study.JdbcUtils;
public class jdbcDom2 {
public static void main(String[] args) throws SQLException {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
//数据库对象
conn = JdbcUtils.getConnection();
//执行sql对象
st = conn.createStatement();
//sql
String sql = "INSERT INTO children (NAME,pwd,sex,email) VALUES('赵不信','emmmmem','女','@250.com')";
//返回的结果影响行数
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
import study.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class jdbcDom3 {
public static void main(String[] args) throws SQLException {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
//数据库对象
conn = JdbcUtils.getConnection();
//执行sql对象
st = conn.createStatement();
//sql
String sql = "DELETE FROM children WHERE id = 6";
//返回的结果影响行数
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
-
增加删除修改统一都是executeUpdate()
只有查询是 executeQuery()
10.5 SQL注入问题
-
sql存在漏洞,会被攻击导致数据泄露
-
sql会被拼接 如查询语句添加 or ’1=1’ 这种永久成立的判断
-
- input 输入框输入 sql语句等危险情况
-
几乎所有的sql都存在sql注入的漏洞
10.6 PreparedStatement对象
- 防止sql注入,效率更高
package study;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class jdbcDom3 {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
String sql = "";
rs = st.executeQuery(sql);
while (rs.next()){
System.out.println(rs.getObject("id"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
JdbcUtils.release(conn,st,rs);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
10.7 使用idea连接数据库
- 注意版本对应
10.8 jdbc操作事务
- ACID原则
import java.sql.*;
//第一个jdbc程序
public class JdbcDom1 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver"); // 固定写法
String url = "jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=false";
String username = "root";
String pwd = "lxxxy";
Connection connection = DriverManager.getConnection(url, username, pwd);
// 关闭自动提交功能,自动开启事务
connection.setAutoCommit(false);
Statement statement = connection.createStatement();
String sql = "UPDATE account SET money=money-500 \n" +
"WHERE NAME = 'A'";
statement.executeUpdate(sql);
try {
//业务完毕,提交事务
connection.commit();
System.out.println("成功");
} catch (SQLException e) {
//失败则回滚,不写也会回滚,不用写
//connection.rollback();
e.printStackTrace();
}
statement.close();
connection.close();
}
}
- idea里面一组完整事务跟sqlyog对比只需要:
-
- 开启事务:connection.setAutoCommit(false);
- 提交:connection.commit();
- 最后回滚可以写但没必要,失败默认自动回滚(牛啊!)
10.9 数据库连接池
- 每次操作数据库都要连接释放资源,其本身也是对系统资源的很大浪费
- 池化技术:准备一些预留的资源,过来直接连接
-
- 最大最小连接数 ------- 最大最小承载资源
- 等待超时:报个异常,下次再来
开源数据源实现—当下较火的
-
DBCP —传统的
-
C3P0 —传统的
-
Druid:阿里巴巴 德鲁伊 —这个厉害,为监控而生
-
- 无需代码连接数据库的操作
-
无论使用什么数据源,DataSource接口不会变,方法不会变