数据库
数据库（DB：DataBase）
概念：数据仓库，软件，安装在操作系统之上，SQL，可以存储500万以上的数据
作用：存储数据，管理数据
数据库分类
- 关系型数据库：（SQL）
- MySQL，Oracle，Sql Server，DB2，SQLite
- 通过表与表之间，行与列之间的关系进行数据的存储，
- 非关系型数据库：（NoSQL：not only SQL）
- Redis，MongDB
- 非关系型数据库，对象存储，通过对象的自身属性来决定
DBMS：（数据库管理系统）
- 数据库的管理软件，科学有效的管理我们的数据，维护获取数据
mySQL基本使用
- MySQL是最流行的关系型数据库
- 开源的数据库
- 体积小，速度快，总体成本低
- SQL语言操作
可视化工具创建
-
字符集：utf8
-
数据库排列规则：utf8_general_ci
-
每一个SQLyog执行操作，本质上对应一个sql，可以在软件的历史记录中查看
基本的命令行操作
- 命令连接：所有的命令都以分号结尾
mysql -u root -p密码 #连接数据库
update mysql.user set authentication_string=password(‘123456’) where user=‘root’ and Host = ‘localhost’; #修改用户密码
flush privileges; #刷新权限
- 数据库基本命令操作
show databases; #查看所有的数据库
use 数据库名称; #使用数据库
show tables; #查看数据库中所有的表
describe 表名称; #显示数据库中所有表的信息
create database 名称; #创建一个数据库
exit; #退出连接
-- #单行注释
/*多行注释*/
- DDL 数据库定义语言
- DML 数据库操作语言
- DQL 数据库查询语言
- DCL 数据库控制语言
操作数据库
- SQL语句不区分大小写
- 创建数据库（create）
CREATE DATABASE IF NOT EXISTS test; #IF NOT EXISTS表示如果不存在，可以不写
- 删除数据库（drop）
DROP DATABASE IF EXISTS test; #IF EXISTS表示如果存在，可以不写
- 使用数据库
USE `school`; #如果表名或字段名是一个特殊字符，就要带 ``
- 查看数据库
SHOW DATABASES; #展示全部数据库
列数据类型
数值
- tinyint：十分小的数据： 1字节
- smallint：较小的数据： 2字节
- mediumint：中等大小的数据： 3字节
- int：标准的整数： 4字节（常用）
- bigint：较大的数据： 8字节
- float：单精度浮点数： 4字节
- double：双精度浮点数： 8字节
- decimal：字符串形式浮点数：
字符串
- char：字符串固定大小的： 0~255
- varchar：可变字符串：（常用） 0~65535
- tinytext：微型文本： 28-1
- text：文本串,保存大文本 216-1
时间日期
java.util.Date
- date：YYYY-MM-DD,日期
- time：HH：mm：ss，时间格式
- datetime：YYYY-MM-DD HH：mm：ss，最常用的格式
- timestamp：时间戳，1970年1月1日到现在的毫秒数
null
- 没有值，未知
- 不要使用null进行运算
数据库的字段属性（重点）
Unsigned
- 无符号的整数
- 声明了该列不能为负数
zerofill
- 零填充的
- 不足的位数使用0填充
自增
- 自动在上一条的记录的基础上+1
- 通常用来设计唯一的主键
- 必须是整数类型
- 可以自定义设计主键自增的起始值和步长
非空：not no null
- 假设设置为not null，如果不给赋值，就会报错。
- null，如果不填值，默认值值null
默认
- 如果不指定该列的值会有默认值填充
id：主键
version：乐观锁
is_delete：伪删除
qmt_create：创建时间
qmt_update：修改时间
创建数据库表
格式：
CREATE TABLE IF NOT EXISTS
表名(
字段名列类型 [属性] [索引] [注释],
字段名列类型 [属性] [索引] [注释],
…
字段名列类型 [属性] [索引] [注释],
PRIMARY KEY(主键名)
)ENGINE=INNODB DEFAULT CHARSET=字符集
查看已经创建好的数据库和表的sql语句
SHOW CREATE DATABASE school -- 查看创建数据库的sql语句
SHOW CREATE TABLE test -- 查看创建表的sql语句
DESC test -- 显示表的结构
- 使用英文(),表的表的名称和字段用``括起来
- AUTO_INCREMENT 自增
- NOT NULL 非空
- DEFAULT 默认
- PRIMARY KEY(
id).主键,一个表唯一一个
CREATE TABLE IF NOT EXISTS `test`(
`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
数据表的引擎：INNODB（默认），MYISAM（早期）
| MYISAM | INNODB | |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间的大小 | 较小 | 较大 ，约为MYISAM的两倍 |
- MYISAM ：节约空间，速度较快
- INNODB：安全性高，事务处理，多表多用户操作
==MySQL引擎在物理文件上的区别
- 所有的数据库都在MySQL下data目录下的
- INNODB：在数据库中只有一个（*.frm）文件，以及上级目录下的（ibdata1）文件
- MYISAM 对应文件：
- （*.frm）表结构的定义文件
- (*.MYD) 数据文件（data）
- (*.MYI) 索引文件（index）
设置数据库表的字符集编码
- 不设置，默认不支持中文编码
修改和删除数据表字段
-- 修改表名:ALTER TABLE 旧表名 RENAME AS 新名字
ALTER TABLE test RENAME AS teacher;
-- 增加表的字段:ALTER TABLE 表名 ADD 字段名 属性名
ALTER TABLE teacher ADD test VARCHAR(12);
-- 修改表的字段(重命名,修改约束!)
ALTER TABLE teacher MODIFY test INT(2); -- 修改约束
ALTER TABLE teacher CHANGE test test1 INT(2); -- 字段重命名
-- 删除表结构的字段
ALTER TABLE teacher DROP test1;
-- 删除表(如果表存在再删除)
DROP TABLE IF EXISTS student;
MySQL数据管理
外键
创建表的时候添加约束
KEY
FK_grade_id(grade_id), – 定义外键
CONSTRAINTFK_grade_idFOREIGN KEY(grade_id) REFERENCESgrade(grade_id)
CREATE TABLE IF NOT EXISTS `grade`(
`grade_id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`grade_name` VARCHAR(30) NOT NULL COMMENT '年级名称',
PRIMARY KEY(`grade_id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `test`(
`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 '邮件',
`grade_id` INT(10) NOT NULL COMMENT '学生年级', -- 添加一个从表
PRIMARY KEY(`id`),
KEY `FK_grade_id`(`grade_id`), -- 定义外键
CONSTRAINT `FK_grade_id` FOREIGN KEY(`grade_id`) REFERENCES `grade`(`grade_id`) -- 给这个外键添加约束(执行引用) references 引用
)ENGINE=INNODB DEFAULT CHARSET=utf8;
单独创建两个表，再添加约束
格式：
ALTER TABLE表名ADD CONSTRAINTFK_约束名FOREIGN KEY(作为外键的列) REFERENCES从表(从表字段);
/*添加外键*/
ALTER TABLE `test`
ADD CONSTRAINT `FK_grade_id` FOREIGN KEY(`grade_id`) REFERENCES `grade`(`grade_id`);
- 以上是物理外键，数据库级别的外键，不建议使用（避免数据库过多造成困扰）
DML语言（数据操作语言）
Insert（添加）
insert into 表名（[字段1，字段2，字段3]）values（值1)，(值2)
- 字段和字段之间要用英文逗号隔开
- 字段是可以省略的，但是后面的值必须要一一对应
- 可以同时插入多条数据
-- 插入语句(添加)
-- 格式：insert into 表名（[字段1，字段2，字段3]）values（值1)，(值2)
INSERT INTO `test`(`name`,`pwd`,`sex`)VALUES('huang','aaaaaa','男');
-- 如果不写表的字段，会一一匹配，没有的就是null，一般都要匹配
-- 插入多个值：
INSERT INTO `test`(`name`,`pwd`,`sex`)
VALUES('huang','aaaaaa','男'),
('张三','bbb','女'),
('李四','ccccc','男');
update（修改）
update 被修改表名 set 字段名=新值 where 条件;
update 被修改表名 set 字段名=新值,字段名=新值,字段名=新值 where 条件; # 修改多个属性
- 字段名是表的列，尽量带上``
- 条件：筛选条件，如果没有指定，则会修改所有的列
- 新值：是一个具体的值，也可以是一个变量
- 多个设置的属性之间，使用英文逗号隔开
-- 修改数据
-- 指定条件
UPDATE `test` SET `name`='王五' WHERE id=1;
-- 不指定条件，会修改数据库所有的数据
UPDATE `test` SET `name`='王五';
-- 修改多个属性，逗号隔开
UPDATE `test` SET `name`='王五',`pwd`='123456' WHERE id=1;
- where子句，运算符：id等于某个值，大于某个值，在某个区间内修改
- 操作符会返回布尔值
| 操作符 | 含义 | 范围 | 结果 |
|---|---|---|---|
| = | 等于 | 5=6 | false |
| <>或!= | 不等于 | 5<>6 | true |
| > | 大于 | 5 > 6 | false |
| < | 小于 | 5<6 | true |
| >= | 大于等于 | 5>=6 | false |
| <= | 小于等于 | 5<=6 | true |
| between … and … | 在某个范围 | between 3 and 5 | [3,5] |
| and/&& | 与运算 | 5>1 and 1>2 | false |
| or | 或运算 | 5>1 or 1>2 | true |
delete（删除）
delete from
表名where 条件
truncate table 表名
-
清空数据库一般用 truncate命令
-
truncate命令可以重新设置自增列，计数器会归零
-
truncate不会清空事务
-
delete删除问题：重启数据库
- InnoDB：自增列会从1开始，（存在内存中），断电即失
- myISAM：会继续从上一个自增量开始（存在文件中，不会丢失）
-- 删除数据
DELETE FROM `test`; -- 会删除表的所有的数据
-- 根据条件删除数据
DELETE FROM `test` WHERE id=1;
-- 清空表的命令 truncate命令
-- 完全清空一个数据库表，表的结构和索引约束不会变
TRUNCATE `test`;
DQL查询数据
- Data Query LANGUAGE ：数据查询语言
- 所有的查询都用 Select，数据库最核心的语言
基本语句
select 字段 from 表名
select 字段 as 别名 from 表名
-- 查询表中全部数据
SELECT * FROM `test`;
-- 查询指定字段
SELECT `id`,`name`,`pwd` FROM `test`;
-- 给查询数据结果取别名 as 表也可以起别名
SELECT `id` AS ID,`name` AS 姓名,`pwd` AS 密码 FROM `test`;
-- 函数 Concat 查询结果拼接字符串
SELECT CONCAT('姓名：',`name`) AS 新名字 FROM `test`;
去重（distinct）及数据库的表达式
加上关键字 distinct
- 去除select查询出来的数据中重复的数据，重复的数据只显示一条
- 数据库的表达式：文本值，列，null，函数，计算表达式，系统变量
-- 去除重复的数据
SELECT DISTINCT `name` FROM test;
数据库的列（表达式）
select version(); -- 查询系统版本（函数）
select 12*13 as 计算结果; -- 用来计算（表达式）
select @@auto_increment_increment; -- 查询自增的步长（变量）
-- 查询结果+1
select `id`+1 as 加一结果 from `test`;
where子句
- 搜索的条件，一般返回值是布尔值
- 尽量用英文符号
| 运算符 | 语法 | 描述 |
|---|---|---|
| and / && | a and b，a&&b | 逻辑与 |
| or | a or b | 逻辑或 |
| Not ！ | not a / ！a | 逻辑非 |
模糊查询：比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| is null | a is null | 如果操作符为null结果为真 |
| is not null | a is not null | 如果操作符不为null结果为真 |
| between and | a between b and c | 若a在b和c之间则返回真 |
| like | a like b | SQL匹配，如果a匹配b则结果为真 |
| in | a in（a1,a2,a3…） | 假如a在（a1，a2…）中返回真 |
-- like结合 %（代表0到任意个字符）_(代表一个字符)
SELECT `name`,`id` FROM `test`
WHERE `name` LIKE '王_';
SELECT `name`,`id` FROM `test`
WHERE `name` LIKE '王%';
-- in查询一个范围的值，匹配一个具体的值
SELECT `name`,`id` FROM `test`
WHERE `id` IN (1,3,4);
连表查询
select … #查什么
from … # 从哪查
XXX join…#和那张表联查
on … #交叉条件
#多张表联查时，一张一张的联查
- inner join ：两张表里都有的数据中查找
- left join：以左表为基准，查询
- right join ：以右表为基准，查询
-- inner join
SELECT t1.`id`,`name`,`class`
FROM test AS t1
INNER JOIN test2 AS t2
ON t1.id=t2.id;
-- right join
SELECT t1.`id`,`name`,`class`
FROM test AS t1
RIGHT JOIN test2 AS t2
ON t1.id=t2.id;
-- left join
SELECT t1.`id`,`name`,`class`
FROM test AS t1
LEFT JOIN test2 AS t2
ON t1.id=t2.id;
-- 三个表的连表查询
SELECT t1.`id`,`name`,`class`,`score`
FROM test AS t1
LEFT JOIN test2 AS t2
ON t1.`id`=t2.`id`
INNER JOIN test3 AS t3
ON t1.`id`=t3.`id`;
自连接查询
- 自己的表和自己的表连接，一张表拆成两张一样的表
-- 自连接查询
SELECT t1.`name`,t2.`name`
FROM test AS t1, test AS t2
WHERE t1.`id`=t2.`pid`;
分页和排序
排序（ORDER BY）
-- 排序：升序 asc，降序 desc
SELECT `id`,`score`
FROM `test3`
ORDER BY `score` DESC;
分页（limit 起始值，页面大小）
- limit (n-1)*pageSize,pageSize
- (n-1)*pageSize：起始值
- n：当前页，第n页
- pageSize：页面大小
- 总页数=数据总数/页面大小
-- 分页：limit 起始值，页面大小
-- LIMIT 0,2 1-2
-- LIMIT 2,4 3-4
LIMIT 2,2;
子查询和嵌套查询
-- 子查询返回值是单行
SELECT `name`
FROM test
-- =只能返回一行，in可以返回多行
WHERE `id` = (
SELECT `id` FROM test3 WHERE `score`=80
)
-- 嵌套查询
SELECT `name`
FROM test
WHERE `id` IN (
SELECT `id` FROM test3 WHERE `score`=(
SELECT `score` FROM test2 WHERE `id=1;`
)
分组（group by）和过滤（having）
MySQL函数
常用函数
数学运算
- select abs(-8) – 绝对值
- select ceiling(9.4) – 向上取整
- select floor(9.4) – 向下取整
- select rand() – 返回一个0-1的随机数
- select sign(-1) – 返回一个数的符号 0返回0 正数返回1 负数返回-1
字符串函数
- select char_length(‘huang’)` – 返回字符串长度
- select concat(‘ssss’,‘aaaa’) – 拼接字符串
- select insert(‘aaaaa’,1,2,bbb) – 从某个位置开始替换某个长度 结果：bbbaaa
- select lower(‘HHHHH’) – 大写转小写
- select upper(‘hhhhhh’) – 小写转大写
- select instr(‘yigezhifu’,‘i’) – 返回第一次出现的字串的索引
- select substr(‘abcdefgh’,4,6) – 返回指定的子串 ，参数（源字符串，截取位置，截取长度）
- select reverse(‘abce’) – 倒叙字符串
-- 查询姓王的替换为黄
SELECT REPLACE(`name`,'王','黄') FROM test
WHERE `name` LIKE '王%';
日期函数
- select current_date() – 获取当前日期
- select curdate() – 获取当前日期
- select now() – 获取当前时间
- select localTime() – 获取本地时间
- select sysdate() – 获取系统时间
- select year(now()) – 获取年
- select month(now()) – 获取月
- select day(now()) – 获取日
- select hour(now()) – 获取时
- select minute(now()) – 获取分
- select second(now()) – 获取秒
系统信息
- select system_user() – 获取系统当前用户
- select user() – 获取系统当前用户
- select version() – 获取系统信息
聚合函数（常用）
| 函数名称 | 描述 |
|---|---|
| count() | 计数 |
| sum() | 求和 |
| avg() | 平均值 |
| max() | 最大值 |
| min() | 最小值 |
| … | … |
-- 查询一个表中有多少个记录
SELECT COUNT(`name`) FROM test; -- count(字段)，会忽略所有的null值
SELECT COUNT(*) FROM test; -- count(*)，不会忽略null值，本质，计算行数
SELECT COUNT(1) FROM test; -- count(1)，也不会忽略null
数据库级别的MD5加密
- MD5：主要增强算法复杂度和不可逆性
- 具体的值的MD5是一样的
- 破解网站的原理，背后有一个字典，存着MD5加密后的值，加密前的值
-- MD5加密
ALTER TABLE test MODIFY pwd VARCHAR(100); -- 修改一下密码容量
UPDATE test SET pwd=MD5(pwd); -- MD5加密
-- 插入的时候加密
INSERT INTO test VALUE(5,'huang',MD5('111000'),'男',NULL,NULL,NULL,NULL);
-- 校验密码的时候，将用户传进来的密码进行md5加密，然后对比加密后的值
事务
要么都成功，要么都失败
- 将一组SQL放在一个批次中去执行
事务原则：ACID原则
原子性（Atomicity）：要么都成功，要么都失败
一致性（Consostency）：事务前后的数据完整性要保持一致
隔离性 （Isolation）：多个用户并发访问数据库时，数据库为每一个用户开启事务，不被其他数据事务所干扰
持久性（Durability）：事务一旦提交则不可逆，会被持久化到数据库中
- 脏读：一个事务读取了另一个事务未提交的数据
- 不可重复读：一个事务内读取表中的某一行数据，多次读取结果不同
- 虚读（幻读）：一个事务读取到别的事务插入的数据，导致前后读取不一致
执行事务的原理
-- ======================= 事务==============================
-- mysql时默认开启事务自动提交的
SET autocommit =0 -- 关闭
SET autocommit =1 -- 开启
-- =========================手动开始================================
-- 手动处理事务
SET autocommit =0 -- 关闭自动提交
start transaction -- 开启一个事务
INSERT XXX
INSERT XXX
-- 提交：持久化（成功），事务一旦提交就不能回滚
COMMIT
-- 回滚：回到原来的样子（失败）
ROLLBACK
-- 事务结束
SET autocommit =1 -- 开启自动提交
-- ===========================手动结束===================================
SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 撤销保存点
索引
MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。
索引的分类
- 主键索引 ：primary key
- 唯一标识，主键不可重复，只有一个列作为主键
- 唯一索引：unique key
- 避免重复的列出现，可以重复，多个列可以标识为唯一索引
- 常规索引：key/index
- 默认的，index/key关键字来设置
- 全文索引：fullText
- 在特定的数据库引擎下才有
- 快速定位数据
添加索引
-- ==================索引的使用==========================
-- 在创建表的时候给字段增加索引
-- 创建完毕后，增加索引
SHOW INDEX FROM test; -- 显示索引信息
-- 添加一个全文索引：索引名（列名）
ALTER TABLE school.`test` ADD FULLTEXT INDEX `name`(`name`);
-- explain 分析sql执行状况
EXPLAIN SELECT * FROM test; -- 非全文索引
EXPLAIN SELECT * FROM test WHERE MATCH(`name`) AGAINST('王'); -- 非全文索引
测试索引查找
-- ===================创建函数，循环插入数据============================
DELIMITER $$ -- 写函数之前必须要写，标志
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 1;
WHILE i < num DO
INSERT INTO test(`name`,`pwd`) VALUE(CONCAT('用户',i),UUID());
SET i=i+1;
END WHILE;
RETURN i;
END;
-- ====================================================================
SELECT mock_data();
EXPLAIN SELECT * FROM test WHERE `name` = '用户999999';
-- 创建索引
-- id_表名_字段名
-- create index 索引名 on 表(字段名)
CREATE INDEX id_test_name ON test(`name`);
-- 测试查询
EXPLAIN SELECT * FROM test WHERE `name` = '用户999999';
索引原则
- 索引不是越多越好
- 不要对进程变动数据加索引
- 小数据的表不需要加索引
- 索引一般加在常用的查询字段上
权限管理
- 用户表：mysql.user
- 本质：对这张表进行增删改查
-- ======================用户管理====================
-- 创建用户: create user 用户名 identified by 密码;
CREATE USER huang IDENTIFIED BY '123456';
-- 修改密码（修改当前用户的密码）
SET PASSWORD = PASSWORD('123456');
-- 修改密码（修改指定用户密码）
SET PASSWORD FOR huang =PASSWORD('111111');
-- 给用户重命名
RENAME USER huang TO huang2;
-- 用户授权:ALL PRIVILEGES全部权限，库，表
GRANT ALL PRIVILEGES ON *.* TO huang;
-- 查询权限
SHOW GRANTS FOR huang;
SHOW GRANTS FOR root@localhost;
-- 撤销权限 revoke
REVOKE ALL PRIVILEGES FROM *.* TO huang;
-- 删除用户 drop
DROP USER huang;
备份
备份方式
- 直接拷贝物理文件
- 在sqlyog这种可视化工具中手动导出
- 使用命令行导出，mysqldump 命令行使用
导出表
# mysqldump -h 主机 -u 用户名 -p密码 数据库 表名 > 物理磁盘目录/文件名
mysqldump -hlocalhost -uroot -p123456 school test >D:/a.sql
# mysqldump -h 主机 -u 用户名 -p密码 数据库 表名 表名表名 > 物理磁盘目录/文件名
mysqldump -hlocalhost -uroot -p123456 school test test1 test2 >D:/b.sql
导入表
- 登入mysql
- 切换到数据库下
source 目录文件
规范数据库设计
设计数据库的步骤：（个人博客）
- 收集信息，分析需求
- 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
- 分类表（文章分类，谁创建的）
- 文章表（文章的信息）
- 友链表（友链信息）
- 自定义表（系统信息，某个关键的字，或者一些主字段）
- 标识实体（把需求落实）
- 标识实体之间的关系
数据库设计三大范式
- 第一范式：（原子性）要求数据库表的每一列都是不可分割的原子数据项
- 第二范式：（每张表只描述一件事）满足第一范式：非码属性必须完全依赖候选码
- 第三范式：（）满足第二范式，确保数据表中的每一列数据都和主键相关，而不能间接相关
- 规范和性能问题
- 关联查询的表不得超过三张表（考虑成本、用户体验）
- 数据库的性能更加重要
- 增加一些冗余字段（多表查询变为单表查询）
- 增加计算列（从大数据量降低为小数据量的查询：索引）
JDBC（重点）
数据库驱动
SUN公司为了简化开发人员的（对数据库的统一操作），提供一个（Java操作数据库的规范）JDBC
- 导入数据库驱动包
使用JDBC连接数据库
- 加载驱动： Class.forName(“com.mysql.jdbc.Driver”); //固定写法，加载驱动
- 用户信息和url
- 连接对象，数据库对象（Connection）
- 创建执行sql的对象(Statement)
- 执行sql语句，返回结果(ResultSet)
- 释放资源
public class testSQL {
public static void main(String[] args) throws Exception {
//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="123456";
//3. 连接对象，数据库对象
Connection connection = DriverManager.getConnection(url, username, pwd);
//4. 创建执行sql的对象
Statement statement = connection.createStatement();
//5. 执行sql语句，返回结果
/**
* statement.executeQuery();执行查询，返回结果集
* statement.execute();` 执行所有的sql
* statement.executeUpdate();`执行更新，插入，删除，返回一个受影响的值
*/
String sql="select * from test";
/**
* ResultSet 查询的结果集
* result.getObject(); 不知道列类型时使用
* result.getString();
* result.getInt();
* result.getFloat();
* result.getDouble();
*
* result.beforeFirst(); 移动到最前面
* result.afterLast(); 移动到最后面
* result.next(); 移动到下一个
* result.previous(); 移动到前一行
* result.absolute(row); 移动到指定行
*/
ResultSet result = statement.executeQuery(sql);
while(result.next()){
System.out.println("id:"+result.getObject("id")+
"\tname:"+result.getObject("name")+
"\tpwd"+result.getObject("pwd"));
}
//6. 释放对象
result.close();
statement.close();
connection.close();
}
}
写一个MySQL工具类(untils)
public abstract class JDBCUtils {
private static String driver=null;
private static String url=null;
private static String username=null;
private static String password=null;
static {
try {
InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties=new Properties();
properties.load(in);
driver=properties.getProperty("driver");
url=properties.getProperty("url");
username=properties.getProperty("username");
password=properties.getProperty("password");
//1. 加载驱动
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
//释放资源
public static void close(Connection connection, Statement statement, ResultSet result){
if(result!=null) {
try {
result.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(statement!=null) {
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(connection!=null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
SQL注入(PreparedStatement)
- SQL存在漏洞会被攻击导致数据泄露
- SQL会被拼接
web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。
- PreparedStatement可以防止SQL注入,并且效率更高
- 本质:
- 把传进来的参数当作字符
- 如果存在转义字符,会被转义
//2. 创建执行sql的对象
//使用占位符 ?
String sql=" INSERT INTO test(`name`,`pwd`,`birthday`) VALUES(?,?,?)";
st = con.prepareStatement(sql); //预编译，先写sql，不执行
//手动添加参数
st.setString(1,"zhang");
st.setString(2,"678990");
st.setDate(3,new java.sql.Date(new java.util.Date().getTime()));
//执行
st.executeUpdate();
事务提交(Java实现)
public class testsql2 {
public static void main(String[] args) {
Connection con=null;
PreparedStatement st=null;
ResultSet rs=null;
try {
con= JDBCUtils.getConnection();
//关闭事务自动提交,开启事务
con.setAutoCommit(false);
//命令操作
String sql1="update test ";
st = con.prepareStatement(sql);
st.executeUpdate();
String sql2="update test ";
st = con.prepareStatement(sql);
st.executeUpdate();
//提交事务
con.commit();
System.out.println("成功");
}catch (Exception e){
//如果不成功，抛出异常,回滚
try {
con.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
}finally {
try {
con.setAutoCommit(true);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
JDBCUtils.close(con,st,rs);
}
}
}
数据库连接池
-
数据库的连接释放十分浪费系统资源
-
池化技术：准备一些预先的资源，过来就连接预先准备好的
- 最小连接数
- 最大连接数：业务最高承载上线
- 等待超时
-
编写连接池，实现一个接口：DataSource
-
开源数据源实现：使用这些数据库连接池之后，就不用编写连接数据库的代码了
- DBCP
- C3P0
- Druid：阿里巴巴
- 导入jar包
- 实现一个使用方法
private static String driver=null;
private static String url=null;
private static String username=null;
private static String password=null;
private static BasicDataSource dataSource=null;
static {
try {
InputStream in = DBCPUtils.class.getClassLoader().getResourceAsStream("db.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(); //从数据源获取连接
}
- 无论使用什么数据源，DataSource不会变
1076
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
