文章目录
- MySQL
- 1.简介
- 1.1数据库分类
- 1.2MySQL
- 2.基础
- 2.1数据类型
- 2.1.1数值
- 2.1.2字符串
- 2.1.3时间/日期
- 2.1.4null
- 2.2字段属性
- 2.3数据库引擎
- 2.4设置字符集编码
- 2.5创建数据库/表
- 2.6修改/删除 表
- 3.数据管理
- 3.1外键（了解）
- 3.2DML
- 3.3INSERT
- 3.4UPDATE
- 3.5DELECT
- 3.6 TRUNCATE
- 4.DQL查询数据(重点)
- 4.1.DQL（数据查询语言）
- 4.2指定查询字段：
- 4.2.1可以给字段起别名，也可以给表取别名
- 4.2.2去重：去除SELECT语句查询的结果中，重复的数据
- 4.2.3数据库的列（表达式）
- 4.3**WHERE条件字句**
- 4.3.1逻辑运算符
- 4.3.2 模糊查询
- 4.3.3联表查询
- 4.3.4自连接(了解)
- 4.4分页/排序
- 4.4.1排序：
- 4.4.2 分页：
- 4.5子查询
- 4.6分组和过滤
- 4.7MySQL函数
- 4.7.1常用函数（不常用）
- 4.7.2聚合函数（常用）
- 5.事务
- 6.索引
- 6.1索引的分类
- 6.2测试索引
- 6.3索引原则
- 7.权限管理
- 7.1用户管理
- 7.2数据库备份
- 8.数据库设计
- 8.1.为什么需要设计
- 8.2.三大范式（了解）
- 9.JDBC（重点）
- 9.1.数据库驱动
- 9.2.JDBC
- 9.3.第一个JDBC程序
- 9.4.statement对象
- 9.5.代码实现
- 5.1提取工具类
- 5.2编写增删改的方法
- 5.3编写查询的方法
- 5.4 SQL注入问题
- 5.5 PreparedStatement
- 9.6.使用Eclipse连接数据库
- 9.7.事务
- 9.8.数据库连接池
MySQL
1.简介
1.1数据库分类
-
关系型数据库：行，列 – (SQL )
- MySQL，Oracle，Sql Server
- 通过表和表之间，行和列之间的关系进行数据存储。 学员信息，考勤表
-
非关系型数据库：{Key，Value} (NoSQL – not only SQL )
- Redis，MongoDB
- 以对象存储，通过对象自身的属性来决定
DBMS – 数据库管理系统
-
数据库的管理软件！科学有效的管理我们的数据，维护和获取数据
-
MySQL – 本质上是一个数据库管理系统！！！DB是纯数据库，只进行数据存储
数据库XXX语言
- DDL ：定义
- DML：操作
- DQL：查询
- DCL：控制
1.操作数据库 2.操作数据库中的表 3.操作数据库表中的数据
mysql关键字不区分大小写；
如果表名或者字段名是一个特殊字符，需要带**``**
1.2MySQL
- 关系型数据库管理系统，【行、列】
- 前世今生 – MySQLAB – Oracle
- 最好的数据库管理系统之一
- 体积小，速度快，成本低（所有人必须会） - 适用于中小型网站或者大型网站！
- 5.7稳//8.0较稳
2.基础
2.1数据类型
2.1.1数值
tinyint -- 十分小的数据，一个byte
smallint -- 较小的数据，2个byte
mediumint -- 中等的数据，3个byte
***int --标准的整数（常用） - 4个byte
bigint -- 较大的数据，8个bytefloat -- 4个byte
double --8个byte
decimal -- 字符串形式的浮点数 - 精度问题！（金融，一般使用！）
2.1.2字符串
char -- 字符串，0-255
varchar -- 常用，可变字符串，0-65535
tinytext -- 微型文本，2^8-1
text -- 大型文本，文本串，2^16-1（保存大文本）
2.1.3时间/日期
date -- YYYY-MM--DD
time -- HH：mm：ss
datetime -- YYYY-MM--DD HH：mm：ss - 最常用
timestamp -- 时间戳，1970/1/1/00：00:00 - 全球统一！
year -- 年份表示
2.1.4null
没有值，未知
不要使用null进行运算；如果使用，一定为null
2.2字段属性
primary key --主键notnull --非空
NULL -- 设置为notnull，如果不赋值，就会报错！
Unsigned -- 无符号的整数 不能声明为负数
zerofill -- 0填充 --不足的位数，全部用0填充，int(3)--005
autoincrement --自增 -- 自动在上一条记录的基础上+1-- 通常用来设置唯一的主键-- 必须是整数类型-- 可以自己定义设置主键自增的初始值和步长
default --默认
2.3数据库引擎
INNODB（默认）
MYISAM（5.5 早些年使用的）
| MYISAM | INNODB | |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间大小 | 较小 | 较大(约为MYISAM的两倍) |
-
常规使用操作：
- MYISAM：节约空间，速度较快
- INNODB：安全性、支持事务的处理、多表多用户操作
-
物理空间存在的位置：
- 所有的数据文件都存在data目录下
- 本质还是文件存储
-
MySQL引擎在物理文件上的区别：
- Innodb：在数据表中，只有一个*.frm文件，以及上级目录的ibdata
- MYISAM：
- *.frm - 表结构的定义文件
- *.MYD - 数据文件（data）
- *.MYI - 索引文件（index）
2.4设置字符集编码
CHARSET=utf8
- 不设置的话，会是mysql的默认字符集编码（不支持中文）
2.5创建数据库/表
--语句：所有的语句使用 ; 结尾!
create database “数据库名称” ：创建数据库
show database
use “数据库名”：切换数据库
show tables； - 查看数据库中所有的表
describe “表名称” - 查看表中的信息
exit ：退出连接--单行注释/* */ --多行注释！
--创建：
create datebase if not exist `数据库名称`
--删除：
drop database if exist `数据库名称`
--使用：
use `数据库名称` select `表名` from `数据库名称`
--查看：
show databases
2.6修改/删除 表
ALTER TABLE `旧表名` RENAME `新表名`;-- 修改表名：
ALTER TABLE `表名` ADD `字段` INT(3); -- 增加表的字段：--修改（1）重命名：ALTER TABLE `teacher01` CHANGE `age` `age01` INT(3);【原来的`age`，现在叫·`age01`】（2）修改字段约束：ALTER TABLE `teacher01` MODIFY `age` VARCHAR(10);【原来的·`age`是int，现在变成varchar】
--删除
ALTER TABLE `teacher01` DROP `age01`;-- 删除表的字段：
DROP TABLE if EXISTS `teacher01` ;-- 删除表：
3.数据管理
3.1外键（了解）
存在引用（外键），不能随便删除！
一定要删除的话，先删除从表（引用别人的表），再删除主表（被引用的表）
-
方式一：在创建表的时候，增加约束（麻烦、复杂）
- 第一步定义外键KEY：
KEY `FK_gradeid` （`gradeid`）- 第二步给外键添加约束：
CONSTRAIN `FK_gradeid` FOREGIN KEY （`gradeid`）REFERENCES `grade` （`gradeid`） -
方式二：创建表后，添加外键约束：
ALTER TABLE `student` ADD CONSTRAINT `约束名` FOREIGN KEY(`作为外键的列`) REFERENCES `引用表` (`引用列`)
以上的操作都是物理外键，数据库级别的外键，不建议使用！（表表之间的关联，删除很麻烦！）避免数据库过多造成困扰！！！这里了解即可！
最佳实践：
- 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
- 想使用外键，用程序去实现！>（阿里规范：一些外键，在应用层解决，也就是说在程序上去实现）
3.2DML
（全部记住，背下来）增删改查
数据库管理语言（Database Management Language）
-
insert：添加
-
update：修改
-
delete：删除
3.3INSERT
INSERT INTO `student` (`name`) VALUES ('吴青峰'),
INSERT INTO `表名 ` (`字段名`) VALUES ('字段内容'),
-
字段字段之间必须用 英文逗号
-
不写字段名，必须全部写上字段内容！
3.4UPDATE
UPDATE `student` SET `name`='告五人' WHERE `id` =1;-- 修改一个属性
UPDATE `student` SET `name`='告五人',`email`='1XXX@qq.com' WHERE `id` =1;-- 修改多个属性
UPDATE `student` SET `birthday`=CURRENT_TIME WHERE `id` =1;-- 修改一个属性（日期）
| 操作符 | 含义 | 范围 | 结果 |
|---|---|---|---|
| = | 等于 | 5=6 | false |
| <>或!= | 不等于 | 5！=6 | true |
| > | |||
| < | |||
| >= | |||
| <= | |||
| BETWEEN…and… | 在某个范围内 | between 2 and 5 | [2,5] |
| AND | && | 5>1 AND 1>2 | false |
| OR | || | 5>1 OR 1>2 | true |
3.5DELECT
DELETE FROM `student` WHERE `id`=2;
3.6 TRUNCATE
TRUNCATE `student`;
- 清空数据库表，表的结构和索引约束不会变
delete和TRUNCATE区别：
-
相同：都能删除数据，都不会删除表结构！
-
不同：TRUNCATE会重新设置自增列，计数器归0，不会影响事务！
-
不同（了解）：DELETE的问题：重启数据库，现象
- INNODB：自增列会从1开始，存储在内存中，断电即失
- MYISAM：会继续，存储在文件中，不会丢失
4.DQL查询数据(重点)
4.1.DQL（数据查询语言）
注意顺序！！！顺序很重要！！！顺序很重要！！！顺序很重要！！！
SELECT 去重 要查询的字段 FROM 表（注意：表和字段要取别名）
LEFT/RIGHT/INNER JOIN 要联接的表 ON 等值判断
WHERE 具体的值/或者子查询语句
GROUP BY 通过哪个字段来分组
HAVING 过滤分组后的信息，条件和where是一样的，只是位置不同
ORDER BY 通过哪个字段排序 升序还是降序
LIMIT startindex，pagesize
- 所有的查询操作都用它：Select
- 简单的查询，复杂的查询，都用
- 数据库，最核心的语言！
- 使用频率最高的语言
4.2指定查询字段：
4.2.1可以给字段起别名，也可以给表取别名
SELECT `studentno` AS 学号 ,`studentname` AS 学生姓名 FROM `student` ;
4.2.2去重：去除SELECT语句查询的结果中，重复的数据
SELECT DISTINCT `studentno` FROM `result`;
4.2.3数据库的列（表达式）
SELECT VERSION();--查询系统版本！
SELECT 100*3-1 AS 计算结果; --用来计算！
SELECT @@auto_increment_increment ; --查询自增的步长（变量）
SELECT `studentno`,`studentresult`+1 AS 增加之后 FROM `result`;
数据库中的表达式：文本值、列、null、函数、计算表达式、系统变量
SELECT 表达式 from 表名
4.3WHERE条件字句
-
作用：检索数据中，符合条件的值；
-
搜索的条件，就是由一个或者多个表达式组成！结果为布尔值！
4.3.1逻辑运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| and && | a and b a && b | 逻辑与 |
| or || | a or b a||b | 逻辑或 |
| not ! | not a !a | 逻辑非 |
尽量使用英文字母的！！！
SELECT `studentno`,`studentresult` FROM `result` WHERE `studentresult`>=90 and `studentresult`<=100;
4.3.2 模糊查询
- 本质是比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| IS NULL | a IS NULL | boolean |
| IS NOT NULL | a IS NOT NULL | boolean |
| BETWEEN … and … | a between b and c | 若a在b和c之间，则为true |
| 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 '李%';--%任意个字符，_只有一个
SELECT `studentno`,`studentname` FROM `student` WHERE `studentno` IN (1001,1002,1003);
SELECT `studentno`,`studentname` FROM `student` WHERE `address` IN ('重庆','迈阿密');
4.3.3联表查询
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-opYF2pGN-1690205301627)(MySQL.assets/image-20210115224101766.png)]
| 操作 | 描述 |
|---|---|
| inner join | 如果表中有一个匹配，则返回匹配的结果 |
| left join | 会从左表中返回所有的值，即使右表中没有匹配 |
| right join | 会从右表中返回所有的值，即使左表中没有匹配 |
--inner join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
WHERE s.studentno = r.studentno；--where用于单个--right join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.studentno=r.studentno;--on用于批量--left join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
LEFT 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
WHERE `studentresult` IS NULL;--join … on … 连接查询（批量）
--join… where… 等值查询（单个）
#联查三张！
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.studentno=r.studentno
INNER JOIN `subject`AS sub
ON r.subjectno=sub.subjectno;
联表查询-思路
1.我要查询的数据SELECT XXX
2.从哪几个表中查FROM 表 XXXX JOIN 表 ON 交叉条件
3.假设存在一种:多张表查询，慢慢来，先查询两张表，然后再慢慢添加
4.3.4自连接(了解)
- 自己的表和自己的表连接，核心：一张表，拆成两张一样的即可
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jaeK7fHn-1690205301627)(MySQL.assets/image-20210115225544881.png)]
父类：
| categoryid | categoryname |
|---|---|
| 2 | 信息技术 |
| 3 | 软件开发 |
| 5 | 美术设计 |
子类：
| pid | categoryid | categoryname |
|---|---|---|
| 3 | 4 | 数据库 |
| 2 | 8 | 办公信息 |
| 3 | 6 | web开发 |
| 5 | 7 | ps技术 |
操作：查询父类对应的子类的关系
| 父类 | 子类 |
|---|---|
| 信息技术 | 办公信息 |
| 软件开发 | 数据库 |
| 软件开发 | web开发 |
| 美术设计 | ps技术 |
SELECT a.`categoryname` AS '父栏目',b.`categoryname` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.categoryid=b.pid;
4.4分页/排序
4.4.1排序：
- （DESC-降序，ASC-升序）
ORDER BY `studentresult`DESC
4.4.2 分页：
LIMIT 0,2LIMIT （n-1）*pagesize , pagesize
*LIMIT （n-1）pagesize , pagesize n代表当前页，pagesize页面大小，也就是一个页面放多少个数据
总页数=数据大小/页面大小
4.5子查询
本质：在where语句中，嵌套一个子查询语句
where (select * from .XXX)
SELECT DISTINCT s.`studentno`,s.`studentname`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
ON r.studentno=s.studentno
WHERE `studentresult`>80 AND `subjectno`=(
SELECT `subjectno` FROM `subject`
WHERE `subjectname`='高等数学-2'
)
4.6分组和过滤
SELECT `subjectname`,AVG(`studentresult`),MAX(`studentresult`),MIN(`studentresult`)
FROM `result` AS r
INNER JOIN `subject` AS sub
ON r.subjectno=sub.subjectno
GROUP BY r.subjectno --通过什么分组
HAVING AVG(`studentresult`)>80 --分组之后加限制
4.7MySQL函数
4.7.1常用函数（不常用）
| 数学运算部分 | 解释 |
|---|---|
| select ABS(-8) | 绝对值 |
| select ceiling(9.5) | 向上取整，10 |
| select floor（9.5） | 向下取整 |
| select rand（） | 随机数，位于0-1之间 |
| select sign（-10） | 判断一个数的符号 |
| 时间日期函数 | 解释 |
|---|---|
| SELECT CURRENT_DATE() | 获取当前日期 |
| SELECT NOW() | 获取当前的日期和时间 |
| SELECT LOCALTIME() | 获取本地的时间 |
| SELECT SYSDATE() | 获取系统的时间 |
| 系统 | 解释 |
|---|---|
| SELECT SYSTEM_USER( ) | 显示系统当前用户 |
| SELECT USER( ) | 显示系统当前用户 |
| SELECT VERSION( ) | 系统版本 |
4.7.2聚合函数（常用）
| 函数名称 | 解释 |
|---|---|
| COUNT( ) | 计数 |
| SUM( ) | |
| AVG( ) | 平均值 |
| MAX( ) | |
| MIN( ) |
SELECT COUNT(`studentname`) FROM `student` --count(指定列)
SELECT COUNT(*) FROM `student`
SELECT COUNT(1) FROM `student`
5.事务
/*关闭事务自动提交！*/
SET autocommit=0 --手动处理事务
--事务开启
START TRANSACTION --标记一个事务的开始！从此之后的sql都在同一个事务内，INSERT XXXX
INSERT XXX--提交：持久化
COMMIT --回滚:失败的时候，回滚
ROLLBACK --事务结束
SET autocommit=1SAVEPOINT --设置一个事务的保存点！
ROLLBACK TO SAVEPOINT --回滚到保存点！
RELEASE SAVEPOINT --撤销、删除保存点
模拟场景
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;--恢复自动提交！
6.索引
官方对索引的定义为：索引是帮助MySQL高效获取数据的数据结构、提取句子主干，就可以得到索引的本质，索引是数据结构
6.1索引的分类
在一个表中，主键索引只能有一个，唯一索引可以有多个！
- 主键索引：PRIMARY KEY
- 唯一标识，主键不可重复，只能有一个字段被列为主键
- 唯一索引：UNIQUE KEY
- 避免重复的列出现，可以重复，多个列都能被标为唯一索引
- 常规索引：KEY/INDEX
- 默认的，index或者key关键字来设置
- 全文索引：FullText
- 在特定的数据库引擎下采用
- 快速定位数据
基础语法
--索引的使用
--1.创建表的时候，给字段增加索引
--2.创建完毕后，增加索引--显示所有的索引信息：
SHOW INDEX FROM `student`--增加索引 索引名（`列名`）
ALTER TABLE `student` ADD FULLTEXT INDEX `studentname`(`studentname`);--分析SQL执行的状况
EXPLAIN SELECT * FROM `student`;--常规索引！
EXPLAIN SELECT * FROM `student` WHERE MATCH(`studentname`) AGAINST ('李');
6.2测试索引
-- 插入100万数据.
delimiter $$
set global log_bin_trust_function_creators=TRUE;
-- 写函数之前必须要写，作为标志
CREATE FUNCTION mock_data2 ()
RETURNS INT
BEGINDECLARE num INT DEFAULT 1000000;DECLARE i INT DEFAULT 0;WHILE i<num DOINSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`)VALUES(CONCAT('用户',i),'1466XXXX@qq.com',CONCAT('13',FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),123456);SET i=i+1;END WHILE;RETURN i;
END;
SELECT mock_data2() -- 执行此函数 生成一百万条数据SELECT * FROM `app_user` WHERE `name`='用户9999' --耗时:0.516
EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户9999'
SELECT * FROM `student` WHERE `name`='用户9999' --耗时0.000--添加索引：
--id_表名_字段名
--CREATE [ FULL TEXT ]INDEX 索引名 ON `表`(`字段`)
CREATE INDEX id_app_user_name ON `app_user`(`name`);SELECT * FROM `app_user` WHERE `name`='用户777' --创建索引之后，耗时：0.047
EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户777'
索引在小数据量的时候用处不大，但是在大数据量的时候，测试效果明显！
6.3索引原则
-
索引不是越多越好
-
不要对经常变动的数据加索引
-
小数据量的表，不需要加索引
-
索引一般加在经常用来查询的字段上
索引的数据结构：
数据库索引介绍
-
hash类型的索引：
-
Btree：INNODB默认的数据结构
7.权限管理
7.1用户管理
SQL命令操作：
用户表：mysql.user
本质：对这张表进行增删改查
--创建用户 CREATE user 用户名 IDENTIFIED BY 密码
CREATE user chenyuqing IDENTIFIED BY '123456';--修改当前用户密码
SET PASSWORD=PASSWORD('111111')--修改指定用户密码
SET PASSWORD FOR chenyuqing=PASSWORD('111111')--给用户重命名
RENAME user chenyuqing TO CYQ;--用户授权 ALL PRIVILEGES 库.表
--ALL PRIVILEGES 除了给别人授权，其他的都可以做！
GRANT ALL PRIVILEGES ON *.* TO CYQ;--查看权限
SHOW GRANTS FOR CYQ;
SHOW GRANTS FOR root@localhost; --GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION--撤销权限 REVOKE 哪些权限，在哪个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM CYQ;--删除用户
DROP user CYQ;
7.2数据库备份
为什么要备份？
- 保证重要的数据不丢失
- 方便数据转移
MySQL数据库备份的方式：
-
直接拷贝物理文件
-
在可视化工具中，手动导出
- 右键选择"备份/导出"
-
使用命令行：mysqldump
#导出表：mysqldump -h主机 -u用户名 -p密码 数据库 表 >物理磁盘位置:/文件名 mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql#导出库 mysqldump -h主机 -u用户名 -p密码 数据库 >物理磁盘位置:/文件名 mysqldump -hlocalhost -uroot -p123456 school >D:/a.sql#导入 #登录的情况下，切换到指定的数据库 #source 备份文件 source d:/a.sql
假设你要备份数据库，防止数据丢失。
把数据库给朋友！sql文件给别人即可。
8.数据库设计
8.1.为什么需要设计
当数据库比较复杂的时候，需要设计
糟糕的数据库设计
- 数据冗余，浪费空间
- 数据 插入/删除 都比较麻烦，而且会产生异常【屏蔽使用物理外键】
- 程序的性能差
良好的数据库设计
-
节省内存空间
-
保证数据的完整性
-
方便开发系统
软件开发中，关于数据库的设计：
- 分析需求：分析业务和需要处理的数据需求
- 概要设计：设计关系图E-R图（流程图）
设计数据库的步骤：
-
收集信息，分析需求
- 用户表（用户登录/注销，用户的个人信息，写博客，创建分类）
- 分类表（文章分类，谁创建的）
- 文章表（文章信息）
- 友链表（友链信息）
- 评论表
- 自定义表（系统信息，某个关键的字，或者一些字段）
- 说说表（发表心情，谁发表的，创建时间，…）
-
标识实体（把需求落地到每个字段）
-
表示实体之间的关系
-
user：写博客 blog
-
user：创建分类 catagory
…
-
antdesign - 设计的很好看！
8.2.三大范式（了解）
为什么需要数据规范化？
- 信息重复
- 更新异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 比如：文章被删除了，但是标签还在
第一范式
原子性：保证每一列不可再分
第二范式
前提：满足第一范式
每张表只描述一件事情
第三范式
前提：满足第一范式和第二范式
确保数据表中的每一列数据都和主键直接相关，而不能间接相关
规范数据库的设计！
规范性和性能的问题！
阿里：关联查询的表，不得超过三张表
- 考虑-成本、用户体验
- 商业化的时候，数据库的性能更加重要
- 商业化的时候，适当的考虑规范性；有时会故意的增加冗余字段，以便从多表查询变成单表查询
- 估计增加计算列：从大数据量，减为小数据量的查询 （或者增加索引！）
9.JDBC（重点）
9.1.数据库驱动
驱动：声卡、显卡、数据库
我们的程序，通过一个数据库驱动，和数据库打交道！
9.2.JDBC
JDBC：java database connection
SUN公司，为了简化开发人员对数据库的统一操作，提供了一套统一（java操纵数据库）的规范，俗称JDBC
这些规范的实现，由具体的厂商去做。
对于开发者来说，只需要掌握JDBC接口的操作，即可
java.sql
javax.sql
还需要导入一个数据库驱动包：mysql-connector-java-5.1.47.jar
9.3.第一个JDBC程序
创建测试数据库
--创建测试jdbc
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;USE jdbcStudy;CREATE TABLE `users`(id INT PRIMARY KEY,NAME VARCHAR(40),PASSWORD VARCHAR(40),email VARCHAR(60),birthday DATE
);INSERT INTO `users`(id,NAME,PASSWORD,email,birthday) VALUES
(1,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')
1.创建一个普通项目：
2.导入数据库驱动：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PNaTzMUS-1690205301628)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20201216172936882.png)]
3.编写测试代码：
//一个jdbc程序
public class JdbcFirstDemo {public static void main(String[] args) throws ClassNotFoundException, SQLException {//1.加载驱动Class.forName("com.mysql.jdbc.Driver");//固定写法//2.用户信息和urlString url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSl=true";String username="root";String password="123456";//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("PASSWORD:"+resultSet.getObject("PASSWORD"));System.out.println("email:"+resultSet.getObject("email"));System.out.println("birthday:"+resultSet.getObject("birthday"));}//6.释放连接resultSet.close();statement.close();connection.close();}
}
步骤：
1.加载驱动
2.连接数据库 connection（相当于 - 登录 - ）
3.获取执行sql的对象 statement （java中万物皆对象，）
4.获取返回的结果集 resultSet
5.释放连接
DriverManager
//DriverManage.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver");//固定写法，加载驱动Connection connection = DriverManager.getConnection(url,username,password);
//connection代表数据库
//数据库设置自动提交
//事务提交
//事务回滚
URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSl=true";//mysql默认3306
//协议:mysql://主机地址:端口号/数据库名？参数1&参数2&参数3//oracle默认1521
//jdbc:oracle:thin:@localhost:1521:sid
statement
Statement statement = connection.createStatement();//执行sql的对象//编写sql
String sql="SELECT * FROM `users`";statement.executeQuery(sql);//返回结果集，结果集中封装了我们全部的查询出来的结果
statement.execute(sql);//执行任何的操作
statement.executeUpdate(sql);//更新/插入/删除 都使用，返回受影响的行数
resultSet
查询query 的结果集，封装了全部的查询结果。
//获得指定的数据类型：
resultSet.getObject();//不知道列类型的情况下使用，如果知道，可以使用指定的数据类型
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
遍历：
resultSet.next();//移动到下一个数据
resultSet.previous();//移动到前一行
resultSet.beforeFirst();//移动到最前面
resultSet.afterLast();//移动到最后面
resultSet.absolute(row);//移动到指定行
释放资源必须做
resultSet.close();
statement.close();
connection.close();
9.4.statement对象
jdbc中的statement对象用于向数据库发送SQL语句，像完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。
CURD操作 - create
使用statement.executeUpdate(sql);方法完成数据添加操作，示例操作：
Statement st=conn.createStatement();
String sql = "insert into user(...) values(...)";
int num = st.executeUpdate(sql);
if(num>0){System.out.println("插入成功！");
}
CURD操作 - delete
使用statement.executeUpdate(sql);方法完成数据删除操作，示例操作：
Statement st=conn.createStatement();
String sql = "delete from user(...) where xxx";
int num = st.executeUpdate(sql);
if(num>0){System.out.println("删除成功！");
}
CURD操作 - update
使用statement.executeUpdate(sql);方法完成数据修改操作，示例操作：
Statement st=conn.createStatement();
String sql = "UPDATE `users` SET `NAME`='fushan' WHERE `id`=2 ";
int num = st.executeUpdate(sql);
if(num>0){System.out.println("插入成功！");
}
CURD操作 - read
使用statement.executeQuery(sql);方法完成数据查询操作，示例操作：
Statement st=conn.createStatement();
String sql = "select from user(...) where XXX";
ResultSet rs = st.executeQuery(sql);
while(rs.next()){//根据获取到的列的数据类型，分别调用rs的相应方法映射到java对象中//例如：rs.getObject("id")
}
9.5.代码实现
5.1提取工具类
package com.cyq.lesson02.util;import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;public class JdbcUtil {private static String driver = null;private static String url = null;private static String username = null;private static String password = null;static {try {InputStream in = JdbcUtil.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) {// TODO: handle exceptione.printStackTrace();}}// 获取连接：public static Connection getConnector() 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 e) {// TODO Auto-generated catch blocke.printStackTrace();}}if (st != null) {try {st.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}if (conn != null) {try {conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}}5.2编写增删改的方法
均使用：executeUpdate(sql);
下面例子使用的：修改！！！
package com.cyq.lesson02;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;import com.cyq.lesson02.util.JdbcUtil;public class TestUpdate {public static void main(String[] args) {Connection conn = null;Statement st = null;ResultSet rs = null;try {//1.获取数据库连接conn = JdbcUtil.getConnector();//2.获得sql的执行对象st = conn.createStatement();//3.编写sqlString sql = "UPDATE `users` SET `NAME`='fushan',`email`='21525XXX@qq.com' WHERE `id`=2";//4.执行sqlint i = st.executeUpdate(sql);if (i>0) {System.out.println("修改成功！");}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally {JdbcUtil.release(conn, st, rs);}}
}
5.3编写查询的方法
通过execteQuery（sql）；
package com.cyq.lesson02;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;import com.cyq.lesson02.util.JdbcUtil;public class TestSelect {public static void main(String[] args) {Connection conn = null;Statement st = null;ResultSet rs = null;try {// 1.获取数据库连接conn = JdbcUtil.getConnector();// 2.获得sql的执行对象st = conn.createStatement();// 3.编写sqlString sql = "SELECT * FROM `users` WHERE `id`=2";//一个空格都不能多！// 4.执行sqlrs = st.executeQuery(sql);while (rs.next()) {System.out.print(rs.getString("name"));}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();} finally {JdbcUtil.release(conn, st, rs);}}
}5.4 SQL注入问题
sql存在漏洞，踩在攻击导致数据泄露，SQL会被拼接
5.5 PreparedStatement
PreparedStatement可以防止SQL注入，而且效率更高
新增/删除/修改 都使用update
本次举例使用：新增
package com.cyq.lesson03;import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;import com.cyq.lesson02.util.JdbcUtil;public class TestInsert {public static void main(String[] args) {Connection conn = null;PreparedStatement st = null;ResultSet rs = null;try {conn = JdbcUtil.getConnector();//区别：//1.使用问好占位符，代替参数！String sqlString = "INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES(?,?,?,?,?)";st = conn.prepareStatement(sqlString);//预编译SQL，然后不执行//手动给参数赋值st.setInt(1, 1);st.setString(2, "jinzixin");st.setString(3,"123456");st.setString(4, "1725XXX@qq.com");//注意：sql.Date java.sql.Date()// util.Date 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 e) {// TODO Auto-generated catch blocke.printStackTrace();}finally {JdbcUtil.release(conn, st, rs);}}
}
查询
package com.cyq.lesson03;import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;import com.cyq.lesson02.util.JdbcUtil;public class TestSelect {public static void main(String[] args) {Connection conn = null;PreparedStatement st = null;ResultSet rs = null;try {conn = JdbcUtil.getConnector();// 区别：// 1.使用问好占位符，代替参数！String sqlString = "SELECT * FROM `users` WHERE `id`=? ";//PreparedStatement防止SQL注入的本质是，把传递进来的参数当作字符！//数据其中存在转义字符，直接忽略掉。""会被直接转义！st = conn.prepareStatement(sqlString);// 预编译SQL，然后不执行// 手动给参数赋值st.setInt(1, 2);// 执行rs = st.executeQuery();while (rs.next()) {System.out.println(rs.getString("name"));}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();} finally {JdbcUtil.release(conn, st, rs);}}
}
9.6.使用Eclipse连接数据库
Windows - showView - Others - dataManagagement - dataSourceExplorer
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PlmFUgl1-1690205301628)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20201217142114451.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-n8HSXUYU-1690205301629)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20201217150910621.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xdYJle1r-1690205301630)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20201217150758186.png)]
9.7.事务
要么都成功，要么都失败！
ACID：原子性、一致性、隔离性、持久性
原子性：要么全部完成，要么都不完成
一致性：总数不变
隔离性：多个进程互不干扰
持久性：一旦提交不可逆，持久化到数据库！
隔离性的问题：
- 脏读：一个事务读取了另一个没有提交的事务！
- 不可重复读：在同一个事务内，重复读取表中的数据，表数据发生了改变
- 幻读/虚读：在一个事务内，读取到别人插入的数据，导致前后读取出来的数据不一致！
代码实现：
package com.cyq.lesson04;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;import com.cyq.lesson02.util.JdbcUtil;public class TestTransaction {public static void main(String[] args) {Connection conn = null;PreparedStatement st = null;// 防止sql注入！// ResultSet re = null;try {conn = JdbcUtil.getConnector();// 1.关闭数据库的自动提交功能；会自动开启事务！conn.setAutoCommit(false);// 2.执行sqlString 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();// 3.业务完毕，提交事务：conn.commit();System.out.println("操作成功！");} catch (SQLException e) {// TODO Auto-generated catch blocktry {conn.rollback();// 失败则回滚事务！} catch (SQLException e1) {// TODO Auto-generated catch blocke1.printStackTrace();}} finally {JdbcUtil.release(conn, st, null);}}
}
步骤：
1.开启事务
2.事务执行完毕，提交事务；可以在Catch语句中显示的定义回滚语句，不定义也可，若失败，默认回滚。
9.8.数据库连接池
数据库连接 – 执行完毕 – 释放 由连接到释放十分浪费系统资源！
池化技术
准备一些预先的资源，过来就连接预先准备好的
– 开门 – 服务 – 关门 –
– 开门 – 业务员 --等待 --服务 --关门
常用连接数 100 个左右！
最小连接数：就设置100个左右！（根据常用连接数设置）
最大连接数： 业务最高承载上限；
待处理业务数大于最大连接数，排队等待！
等待超时：超过特定时间，抛出异常，（相当于让客户走人，别等了，浪费时间！）
编写连接池：实现一个接口DataSource
- 开源数据源实现
- DBCP
- C3P0
- Druid：阿里巴巴（德鲁伊）
使用了这些数据库连接池之后，我们在项目开发中，就不需要编写连接数据库的代码了！
-
DBCP
-
需要用到的jar包：commons-dbcp-1.4.jar commons-pool-1.6.jar
-
拿来即用，即可！
-
-
C3P0
- 需要用到的jar包：mchange-commons-java-0.2.19.jar c3p0-0.9.5.5.jar
结论
无论使用什么数据源。本质都是一样的，DataSource接口不会变，方法不会变！
