文章目录
- 什么是数据库
- 数据库分类
- DBMS（数据库管理系统）
- MySQL简介
- MySQL安装
- 操作数据库
- 数据库基本类型
- 数据库字段属性(掌握)
- 拓展：每个表都必须存在这五个字段
- 使用SQL创建数据表
- 数据表的引擎类型
- 操作表
- 外键（了解）
- DML 语言（重点）
- DQL语言
- where字句
- join
- 分页和排序
- 子查询
- MySQL函数
- 数据库的 MD5 加密
- 事务 -- 关键字 transaction
- 索引 ：关键字 index
- 锁
- 存储过程 ：关键字 procedure
- 视图 ：关键字 view
- 函数 ：关键字 function
- 触发器 ：关键字 trigger
- 游标 ：关键字 cursor
- MySQL访问控制的两个阶段
- MySQL用户管理
- 数据库备份
- MySQL技巧与优化
- 数据库维护
- 数据库设计
- 数据库的三大范式
- 数据库驱动
- JDBC
- 第一个 JDBC 案例
- statement 对象
- 抽取JDBC工具类
- SQL注入
- prepareStatement 对象
- 使用 JDBC 操作 事务
- 数据库连接池
什么是数据库
数据库 ：DB、DataBase
作用 ：存储数据、管理数据
数据库分类
关系型数据库 ：（SQL）
- MySQL、Oracle、SQLserver、DB2
- 通过表与表之间，行与列之间的关系进行数据存储
非关系型数据库 ：（NoSQL）not only SQL
- Redis、MongoDB
- 非关系型数据库，存储对象，通过对象自身的属性来决定
DBMS（数据库管理系统）
数据库管理系统，是一个管理数据的软件，能够科学有效的管理我们的数据，维护和获取数据。
MySQL简介
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于Oracle旗下产品。MySQL 是最流行的关系型数据库管理系统之一。MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。
MySQL安装
mysql 官网 ：https://www.mysql.com/
主要的版本： 5.7 和 8.0
安装建议 ：
- 不要使用 exe 文件安装，因为他会添加到注册表，卸载的时候会比较麻烦
- 尽可能的使用 压缩包的方式去安装
安装完成后添加环境变量 ：（默认添加，如果没有自己动手添加到path路径下）
C:\Program Files\MySQL\MySQL Shell 8.0\bin\C:\Program Files\MySQL\MySQL Server 8.0\bin
MySQL 在 cmd 中的基本命令
-- 启动
net start mysql
-- 停止
net stop mysql
-- 命令行连接MySQL，进入MySQL界面
mysql -u root -p
-- 创建数据库
create database [if not exists] test;
-- 查看数据库
show databases;
-- 进入数据库（使用数据库）
use test
-- 查看表
show tables;
数据库语言
DDL ：数据库定义语言
DML ：数据库操作语言
DQL ：数据库查询语言
DCL ：数据库控制语言
操作数据库
创建数据库
create database [if not exists] 数据库名称
删除数据库
drop database [if exists] 数据库名称
使用数据库
use 数据库名称
查看所有数据库
show databases
查看创建数据库的语句
show create database test
查看创建数据表的语句
show create table student
查看表的结构
desc student
数据库基本类型
数值
int 整数 4个字节
bigint 较大的整数 8个字节
float 浮点数 4个字节
double 浮点数 8个字节
decimal 字符串形式的浮点数 金融计算的时候一般使用该类型
字符串类型
char 字符串固定大小 0~255
varchar 可变字符串 0~65535
tinytext 微型文本 2^8-1
text 文本 2^16-1
时间类型
data ：YYYY-MM-DD 日期格式
time ：HH：mm：ss 时间格式
datatime ：YYYY-MM-DD HH：mm：ss
timestamp ：时间戳，从1970.1.1到现在的毫秒数
数据库字段属性(掌握)
无符号（unsigned）
声明了该列的值不能为负数
仅限于整数类型
零填充（zerofill）
不足的位数用0填充
例如 ： int(5) 用户输入的数字为3，数据库显示为 00003
自增
自动增加，在上一条的基础上默认增加 1 ，仅限于整数类型
非空
设置该列为 非空，在添加数据的时候，不给其赋值则会报错
默认
设置默认值
拓展：每个表都必须存在这五个字段
id -- 主键
version -- 乐观锁
is_delete -- 伪删除
gmt_create -- 创建时间
gmt_update -- 修改时间
使用SQL创建数据表
-- 格式
create table [if not exists] `student`(
字段名 字段类型 [属性] [索引] [注释],
字段名 字段类型 [属性] [索引] [注释],
字段名 字段类型 [属性] [索引] [注释]
)[表类型] [字符集设置]
-- 表和字段的名称尽量使用 `` 括起来
-- NOT NULL 非空
-- AUTO_INCREMENT 自增
-- COMMENT 注释，字符串使用 英文的单引号
-- 所有语句后面加逗号，最后一句不用加
-- DEFAULT 默认值
-- PRIMARY KEY (`id`) 设置主键，一般放在最后
-- ENGINE=INNODB 设置表的引擎
-- DEFAULT CHARSET 设置默认的编码格式
create table if not exists `student`(
`id` INT(5) NOT NULL AUTO_INCREMENT COMMENT '学生ID',
`name` VARCHAR(255) NOT NULL DEFAULT '张三' COMMENT '姓名',
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET='utf8'
数据表的引擎类型
INNODB 默认使用
MYISAM 以前使用的
| INNODB | MYISAM | |
|---|---|---|
| 事务 | 支持 | 不支持 |
| 行锁 | 支持 | 不支持 |
| 外键 | 支持 | 不支持 |
| 全文索引 | 不支持 | 支持 |
| 表占用空间 | 较大 | 较小 |
操作表
修改表
-- 修改表名
-- ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE student RENAME AS student1
-- 增加表的字段
-- ALTER TABLE 表名 ADD 字段名 字段类型
ALTER TABLE student1 ADD age INT(11)
-- 修改表的字段
-- ALTER TABLE 表名 MODIFY 字段名 字段类型
-- ALTER TABLE 表名 CHANGE 旧字段名 新字段名 字段类型
ALTER TABLE student1 MODIFY age VARCHAR(255) #修改约束
ALTER TABLE student1 CHANGE age age1 INT(3) #重命名
-- 注意 ：MODIFY 只能修该字段约束，CHANGE 只能修该字段名
-- 删除表的字段
-- ALTER TABLE 表名 DROP 字段名
ALTER TABLE student1 DROP age1
删除表
drop table [if exists] test
外键（了解）
-- 给表添加外键
-- alter table `表名` add
-- CONSTRAINT `约束名` FOREIGN KEY(`作为外键的列`) REFERENCES `目标表`(`字段`)
alter table `student` add
CONSTRAINT `FK_cid` FOREIGN KEY(`cid`) REFERENCES `class`(`cid`)
上面添加的都是物理外键，数据库级别的外键，不建议使用
最佳实践
数据库就是单纯的表，只用来存数据，需要多张表联合的时候，使用程序去实现
DML 语言（重点）
DML ：数据库操作语言
- insert
- delete
- update
添加 insert
-- INSERT INTO 表名([字段1,,字段2......]) VALUES (值1,值2......);
-- 如果不写字段，默认是全部字段
-- 可以同时添加多条数据，value 后的数据需要用逗号隔开 VALUES (值1,值2......), (值1,值2......)
INSERT INTO `student`(`id`, `name`, `cid`) VALUES (1, '张三', 1);
修改 update
-- UPDATE 表名 SET 字段1 = 值,[字段1 = 值......] WHERE 条件;
-- 如果不加 where 字句的话，将会把该表中的所有数据都修改
UPDATE `student` SET `name` = '张三', `cid` = 1 WHERE `id` = 1;
| 操作符 | 含义 |
|---|---|
| = | 等于 |
| <> 或 != | 不等于 |
| between … and … | 某个范围内 如 ：between 2 and 5 表示 2-4 不包含5 |
| and | 和 |
| or | 或 |
删除 delete
delete 命令
-- delete from 表名 where 条件;
-- 如果不加where 字句，将会删除该表下所有的数据
delete from student where id=1;
TRUNCATE 命令 ：完全清空一张数据表，表的结构和约束不变
-- TRUNCATE [table] 表名;
TRUNCATE student;
delete 和 TRUNCATE 的区别
相同点 ：
- 都能删除数据
- 都不会删除结构
不同点 ：
- TRUNCATE 重新设置自增列，自增值会归零
- TRUNCATE 不会影响事务
DQL语言
DQL ：数据库查询语言
数据库最核心的语言
select 的语法
SELECT
* | file
FROM table_name
[inner|left|right join table_name]-- 连表查询
[WHERE ...]-- where字句筛选查询结果
[GROUP BY...]-- 分组
[HAVING ...] -- 过滤分组的条件
[ORDER BY [ASC | DESC], ...] -- 排序
[LIMIT ...] -- 分页
简单查询
-- 查询全部学生的所有信息
-- select 字段,... from 表名
select * from student
-- 查询指定字段的全部学生信息
select sid,sname from student
-- 别名 AS 或 空格 ，可以给字段起别名，也可以和表起别名
select sid as 学号,sname as 学生姓名 from student as s
-- 函数 concat (a,b)
select concat('姓名 :',sname) as 学生姓名 from student
concat函数执行结果
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9PIfeCvr-1630767455810)(3、MySQL数据管理.assets/image-20210821191036079.png)]
去重
DISTINCT ：去除数据中重复的的数据，重复的数据只显示一条
-- 查看有哪些同学参加考试
select sid from score
-- 发现有重复数据，对其进行去重
select DISTINCT sid from score
数据库的列（表达式）
-- 查看数据库版本（函数）
select version()
-- 计算 （表达式）
select 3*5 as 结果
-- 查询自增的步长 (变量)
select @@auto_increment_increment
-- 让所有学生的分数加 1
select sid,score+1 as 分数 from score
where字句
作用 ：检索数据中筛选 符合条件 的数据
| 逻辑运算符 | 描述 | 语法 |
|---|---|---|
| and | 与，两个都为真，结果为真 | a and b |
| or | 或，一个为真，结果为真 | a or b |
| not | 非，真为假，假为真 | not a |
select * from `subject` where hour>24;
| 比较运算符 | 描述 | 语法 |
|---|---|---|
| is null | 若a为null，结果为真 | a is null |
| is not null | 若a 为 not null ，结果为真 | a is not null |
| between … and … | 若a 在 b 和c 之间，结果为真 | a between b and c |
| like | SQL匹配 | a like b |
| in | 若 a 等于 a1，a2中任意一个，结果为真 | a in(a1,a2,a3…) |
-- 查询学生姓名为空的同学
select * from student where sname is null or sname = ''
模糊查询 like
-- like
-- % ：表示匹配多个字符
select * from student where sname like '张%'
-- _ : 表示匹配一个字符
select * from student where sname like '张_'
join
七种 join 理论
-- 查询学号、学生姓名、成绩
-- inner join on
select s.sid,sname,score from student s inner join score sc on s.sid=sc.sid
-- left join on
select s.sid,sname,score from student s left join score sc on s.sid=sc.sid
-- right join on
select s.sid,sname,score from student s right join score sc on s.sid=sc.sid
| 操作 | 描述 |
|---|---|
| inner join on | 表中至少有一个匹配项，就返回行 |
| left join on | 会从左表中返回行，右表自动补充值，没有值的补充null |
| right join on | 会从右表中返回行，左表自动补充值，没有值的补充null |
-- 查询缺考的学生的
select s.sid,sname,score from student s
left join score sc on s.sid = sc.sid
where score is null
-- 查询学号、学生姓名、考试科目、成绩 （三表连接查询）
select s.sid,sname,cname,score from student s
right join score sc on s.sid = sc.sid
inner join course c on sc.cid = c.cid
自连接 ：使用与结构树，表中的数据有一个 pid（父ID）
核心是 ：一张表拆分成两张表
子类 ：
| pid | cid | cname |
|---|---|---|
| 7 | 1 | Java基础 |
| 7 | 2 | Javaweb |
| 8 | 3 | mysql基础 |
父类
| cid | cname |
|---|---|
| 7 | java开发 |
| 8 | 数据库 |
select a.cname as 子类名称,b.cname as 父类名称 from course a,course b where a.pid = b.cid
分页和排序
排序 order by 默认排序规则为升序
-- 升序 ASC
select * from score order by score asc
-- 降序 desc
select * from score order by score desc
分页 limit : 放在语句的最后
-- 语法 ：
limit 起始值,页面的大小
limit 0,5 表示的是 1-5 条数据
select * from score order by score desc limit 0,5
计算页面的公式
(n-1)*pageSize,pageSize
//n ：页码
//pageSize ：页面数据的大小
//(n-1)*pageSize ：页面数据的起始位置
子查询
where子查询，本质 ：where 字句中嵌套一个查询语句
-- 查询成绩表中科目编号为 2 的考试成绩中是否存在不及格的学生，如果存在不及格的学生就将
-- 参加科目编号 2 考试的学生编号和成绩全部查询显示出来
select sid,score.score from score where cid=2 and exists(select sid from score where cid=2 and score.score<60);
-- 查询成绩比科目编号为“1”的这门课程的所有成绩都大的学生考试信息
select * from score where score.score>all(select score.score from score where cid=1);
MySQL函数
常用函数
-- 数学函数
select abs(-8) -- 绝对值
select ceiling(9.1) -- 向上取整
select floor(9.9) -- 向下取整
select rand() -- 返回 0-1 之间的随机数
select sign(-10) -- 判断一个数的符号，正数返回1，负数返回-1
-- 字符串函数
select char_length('即使再小的帆也能远航') -- 返回字符串的长度
select concat('我','爱','编程') -- 拼接字符串
select insert('我爱编程',1,2,'非常热爱') -- 在指定的位置替换字符串结果 ：非常热爱编程
select lower('HanKunPeng') -- 转为小写
select upper('hankunpeng') -- 转为大写
select instr('han','h') -- 返回字符第一次出现的索引
select replace('坚持就能成功','坚持','努力') -- 替换出现的指定字符串，结果 ：努力就能成功
select substr('坚持就能成功',3,2) -- 返回指定的字符串(不加第三个参数，就是截到末尾)结果 ：就能
-- 查询姓张的同学，将张改为王
select replace(sname,'张','王') from student where sname like '张%'
-- 时间和日期函数
select current_time() -- 获取当前时间 HH:mm:ss
select current_date() -- 获取当前日期 YYYY-MM-DD
select now() -- 获取当前时间 YYYY-MM-DD HH:mm:ss
select sysdate() -- 获取系统时间 YYYY-MM-DD HH:mm:ss
select localtime() -- 获取当地时间 YYYY-MM-DD HH:mm:ss
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() -- MySQL版本
聚合函数
| 函数名称 | 描述 |
|---|---|
| count() | 计数 |
| sum() | 求和 |
| avg() | 平均值 |
| max() | 最大值 |
| min() | 最小值 |
-- 统计表中的数据
select count(sname) from student -- count(字段) 会忽略所有的null值
select count(*) from student -- 不会忽略null 值
select count(1) from student -- 不会忽略null值
-- 查询总分
select sum(score) as 总分 from score
-- 查询平均分
select avg(score) as 平均分 from score
-- 查询最高分
select max(score) as 最高分 from score
-- 查询最低分
select min(score) as 最低分 from score
数据库的 MD5 加密
MD5 ：主要增强算法复杂度和不可逆性
MD5 不可逆，具体的值的 MD5 是一样的
-- 给数据加密
update test set pwd = md5(pwd) where id = 1
-- 添加数据的时候加密
insert into test values(5,'xiaoming',md5(123123))
-- 校验
select * from test where `name`='xiaoming' and pwd = md5(123123)
事务 – 关键字 transaction
事务 ：是一条SQL或一组SQL组成的最小的操作单元，该操作单元不可再分，要么同时成功，一旦有一条失败，全部回滚。
注意：在mysql中，默认事务自动提交，每条SQL是一个事务，执行后自动执行提交操作。事务一般作用于DML语句中（DML一般指 增 删 改）
事务操作
-- 设置事务自动提交的
set autocommit = 0 -- 关闭
set autocommit = 1 -- 开启（默认）
-- 开启事务 :
start transaction;
-- 提交事务 : （成功）
commit;
-- 回滚事务 : （失败）
rollback;
-- 设置保存点
savepoint 保存点名称
-- 删除保存点
release savepoint 保存点名称
事务的特性（ACID）
① 原子性 ：事务是最小单元，不可再分。
② 一致性 ：事务要求所有的DML语句操作时，必须保证同时成功或同时失败。
③ 隔离性 ：同一时间，只允许一个事务请求同一数据。
④ 持久性 ：是事务的保证，事务终结的标志（内存中的数据持久到硬盘文件中）。
事务的隔离级别
① 读未提交（read uncommitted） ：事务A可以读到事务B未提交的数据，这种数据成为“脏数据”。
② 读已提交（read committed） ：事务A只能读到事务B已提交的数据。可以避免“脏数据”，但会产生“不可重复度”。（不可重复度 ： 事务A在事务B没提交的时候读到一个数据；在事务B提交过后，又读到一个数据）
③ 可重复读（repeatable read） ：事务A读不到事务B已提交的数据，可以避免“不可重复读”，但会产生“幻读”。（幻读 ： 事务B提交数据后，事务A查不到已更新的数据，但事务A再去更新数据，会更新两次数据，称之为幻读）。mysql的默认隔离级别。
④ 串行化（serializable） ：事务A在操作数据时，事务B只能排队等候，不能操作数据，可以避免“幻读”。每次读取的数据都是真实存在的。
手动设置隔离级别
语法 ：set session transaction isolation level 事务隔离级别
设置读未提交
set session transaction isolation level read uncommitted;
设置读已提交
set session transaction isolation level read committed;
设置可重复读
set session transaction isolation level repeatable read;
设置串行化
set session transaction isolation level serializable;
-- 2.打开事务，再给学生表添加三条数据
start transaction;
insert into studentinfo values(null,'test1',1,'男',20,'2020-02-27','我是测试1');
insert into studentinfo values(null,'test2',1,'男',20,'2020-02-27','我是测试2');
insert into studentinfo values(null,'test3',1,'男',20,'2020-02-27','我是测试3');
commit;
索引 ：关键字 index
索引 ：相当于字典中的目录。是高效获取数据的数据结构。
索引本质是数据结构，默认为B数结构（大的在右，小的在左）
索引优势 ：
① 提高查询效率；
② 使用分组和排序子句进行检索时，可以显著减少查询时间；
索引劣势 ：
① 不是所有情况都适合加索引；
② 索引本身很大，存放于硬盘上；
③ 表中数据修改时，动态维护索引；
④ 降低 增 删 改 的效率；
索引语法
建表时
create table 表名(
id int,
sname varchar(255),
index 索引名称(列名(长度))
)
-- 1-1 在创建表时创建索引
create table mytable(
id int,
username varchar(16),
index myindex(username(16))
)
建表后
create index 索引名 on 表名 (列名(长度)) 或
alter table 表名 add index 索引名（列名）
-- 1-2 创建表之后创建索引
alter table mytable add index myindex(username);
索引的分类
主键索引
主键索引 ：主索引，一张表只能有一个主索引。主索引即主键。
-- 语法 ：alter table 表名 add primary key (主键列名)
-- 创建主键索引
alter table student add primary key(sid);
唯一索引
唯一索引 ：建立索引的列必须为唯一的，允许为空的。
-- 语法 ：alter table 表名 add unique 索引名 (列名)
-- 创建唯一索引
alter table student add unique uk_index(sname);
普通索引
普通索引 ：用普通的列创建，没有任何限制，仅加速查询。
-- 语法 ：alter table 表名 add index 索引名 (列名)
-- 创建普通索引
alter table student add index myindex(sex);
全文索引
全文索引 ：主要是用来查询文本中的关键字，而不是直接与索引中的值比较。
组合索引
组合索引 ：指多个字段上创建的索引，只有在查询条件中使用了创建索引第一个字段，索引才回被使用。
索引的操作
查看索引
show index from 表名
-- 1-3 查看表中的索引
show index from mytable;
删除索引
drop index 索引名 on 表名
或
alter table 表名 drop index 索引名
-- 1-4 删除表中的索引
drop index myindex on mytable;
explain计划
可以查看SQL的执行计划
-- 语法 ：explain + SQL语句
-- 1-5 通过explain命令查看索引使用情况
explain select * from mytable where username='123';
需要创建索引的情况（不是绝对的）
① 频繁作为查询条件的字段；
② 查询中统计或分组的字段；
不需要创建索引的情况（不是绝对的）
① 数据量小；
② 频繁更新的字段；
③ 很少使用的字段；
锁
锁 ：是控制访问资源的。
锁的分类
乐观锁
是操作数据库时候，想法很乐观，认为不会发生错误，在操作时不加锁，在操作后，在去判断是否有冲突。乐观锁不是数据库自带的，需要我们自己去实现。
悲观锁
是操作数据库时候，认为数据会发生错误。悲观锁是数据库自己实现的。
共享锁（读锁）
针对同一数据，多个读操作可以同时进行而且不互相影响。
排它锁（写锁）
当前写操作没有完成前，他会阻断其他读锁和写锁。
表锁
开销小，加锁速度快，不会出现死锁。锁定粒度最大，适合查询为主的，只有少量按索引条件更新数据的操作。
行锁
开销大，加锁速度慢，会出现死锁。锁定粒度最小，适合大量按索引条件并发更新少量不同的数据，同时又有并发查询操作。使用行级锁锁定的主要是innodb存储引擎。
注意 ：悲观锁和乐观锁是人们定义出来的概念。可以理解为一种思想，不要把他们与mysql中提供的锁的机制（表锁、行锁、排它锁、共享锁）混为一谈
存储过程 ：关键字 procedure
存储过程 ：一个或多个SQL的集合。
存储过程的优点 ：
① 简化复杂的操作；
② 简化变动的管理（数据库有变动，只需修改存储过程即可，不许要修改java的调用代码）；
③ 有助于提高应用程序的性能；
存储过程的缺点 ：
① 大量使用存储过程，会大量消耗系统资源；
② 有复杂的业务逻辑的存储过程变的更加困难；
③ 开发和维护存储过程不容易；
存储过程的语法
不带参数的
create procedure 存储过程名称()
begin
存储过程体（相当于java中的方法体）；
end
带参数的
create procedure 存储过程名称( in | out | inout 参数名 参数类型)
begin
存储过程体；
end
注意 ：
- begin……end 相当于java中的大括号 {}
- 存储过程的参数列表中的参数名，不能和列名一致
- 使用输入输出参数时，参数类型尽量为一致的,调用时，要使用set先给其赋值。
- 查询语句中，可以使用 into 将查询的值赋值给输出参数
带参数的存储过程分类
① in ：输入参数，表示调用者向过程内传入值（传入的值可以使字面量或变量）
② out ：输出参数，表示过程向调用者输出值（输出值可以是多个值，只能是变量）
③ inout ：输入输出参数，表示即可以表示调用者向过程内传入值，也可以表示过程向调用者传出值。（只可以是变量）；
调用存储过程的方法
call 存储过程的名称();
变量的分类
变量 ：程序中的一个容器。
全局变量
全局变量 ：又称为系统变量，以@@开头。
局部变量
局部变量 ：作用域是所在的方法；使用set赋值。
-- 语法 ：declare 变量名称 类型 [default 值]
-- 带默认值的
declare a int default 0;
-- 不带默认值的
declare user varchar(20);
用户变量
用户变量 ：作用域是当前的连接，声明、赋值、查询都是用@符号。
注意 ：declare 声明变量必须在存储过程的开始，否则会报错。
流程控制语句
分支结构
-- ① if 条件语句 ：包含多个条件，根据结果执行语句。
-- 语法 :
if 条件1 then 语句1;
elseif 条件2 then 语句2;
……..
else 语句;
end if;
-- ② case 条件语句 ：
-- 语法1 :
case
when 条件1 then 输出语句;
when 条件2 then 输出语句;
…….
else 输出语句;
end case ;
-- 语法2 :
case 值
when 条件1 then 输出语句;
when 条件2 then 输出语句;
……..
else 输出语句;
end case ;
-- 注意 ：语法1适合判断范围，语法2适合判断固定的值。（不是绝对的）
循环结构
-- ① while循环语句
-- 语法 ：
while 条件 do
循环体;
end while;
-- ② repeat 循环语句 : 先执行循环体，在判断条件，repeat 表达式为false时，执行循环体，为true时，结束循环。（类似于do … while ）
-- 语法 :
repeat
循环体;
until 结束循环的条件;
end repeat ;
-- ③ loop 循环语句 : 用来重复执行某些语句。执行中用leave语句或iterate跳出循环；可以嵌套if语句。
-- 语法 :
循环名称 : loop
循环体;
end loop 循环名称;
注意 ：leave语句相当于java中的break，用来终止循环；iterate相当于java中的continue 用来结束本次循环，进入下次循环。loop中没有明确结束的关键字，要结合if分支结构来结束。
管理存储过程语句
显示存储过程（特定查、模糊查皆可）
语法 ：show procedure status [where db=’数据库名称’];
显示存储过程源码
语法 ：show create procedure 过程名;
删除存储过程
语法 ：drop procedure 过程名;
练习
存储过程的创建
-- ① 创建存储过程，查询student表中的所有同学信息
create procedure pro_stu_All()
begin
select * from student ;
end
-- 调用存储过程 ：
call pro_stu_All();
-- ② 创建存储过程，输入性别，查询符合条件的同学信息
create procedure pro_stu_sex(in s varchar(20))
begin
select * from student where sex=s;
end
-- 调用存储过程 ：
call pro_stu_sex(‘男’);
-- ③ 创建存储过程，输入年龄，输出大于该年龄的人数
create procedure pro_stu_age(in a int,out c int)
begin
select count(*) into c from student where age>a;
end
-- 调用存储过程 ：
call pro_stu_age(12,@a);
select @a;
-- ④ 创建存储过程，使用inout，输入年龄，输出大于该年龄的人数
create procedure pro_stu_inout(inout a int)
begin
select count(*) into a from student where age>a ;
end
-- 调用存储过程 :
set @a=12;
call pro_stu_inout(@a);
select @a;
控制流程语句
-- ① 创建存储过程，输入整数，使用 if 语句判断是正数还是负数
create procedure pro_if(in z int)
begin
if z>0 then
select ‘正数’;
elseif z<0 then
select ‘负数’;
else select ‘是0’;
end if ;
end
-- 调用存储过程 ：
call pro_if(1);
-- ② 创建存储过程，输入整数，使用 case 语句判断是正数还是负数
create procedure pro_case1(in z int)
begin
case
when z>0 then
select ‘正数’;
when z<0 then
select ‘负数’;
else select ‘是0’;
end case;
end
-- 调用存储过程 ：
call pro_case1(1);
-- ③ 创建存储过程，输入整数，使用 case 语句判断是1还是2
create procedure pro_case2(in z int)
begin
case z
when 1 then select ‘是1’;
when 2 then select ‘是2’;
else select ‘不是1也不是2’;
end case ;
end
-- 调用存储过程 ：
call pro_case2(0);
视图 ：关键字 view
视图 ：是一个虚拟的表，其内容由查询定义（视图中的SQL语句为查询语句）。视图并不在数据库中以数值集形式存在，而是在每次调用视图的时候，动态化生成其内部查询语句所查询的数据。视图所操作的表称为基表（可以是一张表，也可以是多张表）。
视图的优点
-
简单化，数据所见即所得。
-
安全性，用户只能查询或修改他们所看到的数据。
-
逻辑独立性，可以屏蔽真实表结构变化带来的影响。
视图的缺点
-
性能相对较差，从视图查询数据，可能会很慢。
-
通过视图修改数据不方便，特别是复杂的聚合视图，基本无法修改。
视图的语法结构
基本语法结构
create view 视图名
as
SQL语句;
-- 创建视图，显示学生姓名、年龄、性别和所在班级编号
create view v_stu1
as
select sname,age,sex,cid from student;
-- 通过视图查询
select * from v_stu1;
带别名的语法结构
create view 视图名(列的别名……)
as
SQL语句;
-- 创建视图，显示学生姓名、年龄、性别和所在班级编号，并以中文表头显示
create view v_stu2(姓名,年龄,性别,所在班级)
as
select sname,age,sex,cid from student;
-- 通过视图查询
select * from v_stu2;
注意 ：别名不需要带引号
视图的调用
视图是一张虚拟表，调用时候和表一样 ：
-- select * from 视图名;
select * from v_stu2;
视图的操作
通过视图，可以对基表进行增删改操作
创建可更新视图不能包含 ：
-
聚合函数
-
distinct 子句
-
group by 子句
-
having 子句
-
union 和 union all 子句
-
外连接
注意 ：不建议使用基于多表创建的视图进行更新操作。
with check option 子句
一般创建视图时，都会加上这句话，加载SQL语句的最后面；加上后通过视图对数据操作的时候，会进行判定，不满足视图中的条件的数据，不允许更改。
-- 1-6 修改1-5视图，添加with check option 子句
-- 修改视图
alter view v_sub
as
select * from `subject` where hour>24 with check option;
-- 通过视图插入一门新课程
insert into v_sub values(null,'layui',20);
这样的视图，在增加 课时数少于20 的课程时，会报错
视图的管理
查看视图的定义
show create view 视图名 ;
查看视图
视图是虚拟表，因此和表相似。
-- 查看数据库中所有的表和视图。show tables ;
修改视图
alter view 视图名as新的SQL语句;
删除视图
drop view 视图名
函数 ：关键字 function
函数 ：和JavaScript中的函数作用相似，执行特定任务的代码块。函数就是封装好的代码块。mysql中只有标量值函数，没有表值函数。
函数 ：是一条或多条SQL的集合。
函数的优点
-
简化应用开发人员的很多工作。
-
提高了数据处理的效率。
函数的语法结构
create function 函数名([参数列表……])
returns 数据类型
begin
函数体;
return 值;
end
-- 2-2 创建一个带参数的函数，输入学号，返回学生姓名
create function fun_stu2(i int)
returns varchar(20)
begin
declare n varchar(20);
select sname into n from student where sid=i;
return n;
end
调用函数
select 函数名() ;
-- 调用函数
select fun_stu2(2);
创建函数的注意事项
-
函数后有一个小括号，括号内的参数可以填写也可以不填，但括号不能省略。
-
小括号后必须跟returns，returns后跟返回值类型，类型必须是mysql中的数据类型。
-
函数主体在begin……end 内，end 前要return与前面returns后跟的数据类型相同的值。
-
select 查询结果也可以用来给变量赋值，使用into关键词。
函数和存储过程的区别
-
返回值不同 ：函数必须有返回值，且只能有一个返回值，存储过程可以没有返回值，也可以有一个或多个返回值。
-
调用时不同 ：函数使用select 调用，存储过程使用call 调用。
-
参数不同 ：函数的参数都是in 参数，存储过程可以传递in/out/inout参数。
触发器 ：关键字 trigger
触发器 ：事先为某张表绑定一段代码（作用于表上），当表中的内容改变是（DML），系统会自动执行这段代码。
触发器的三要素
触发类型
增加 ：insert
修改 ：update
删除 ：delete
触发时间
before ：执行DML语句前触发
after ：执行DML语句后触发
触发对象
针对表中的每条记录。（for each row 加上这句，表中每行记录发生改变时都会触发触发器）
注意 ：一张表同一时间，同一类型的触发器只能有一个。每张表最多可有6个触发器。
触发器语法结构
create trigger 触发器名称
触发时间 触发类型 on 表名
for each row
begin
业务逻辑;
end
触发器中的新旧记录（重点）
new 和old 用来表示触发器所在表，触发了触发器的那一行数据。
-
在insert触发器中，new用来表示将要或已经增加的数据。insert中没有old旧数据。
-
在update 触发器中，old 用来表示将要或已经修改的原数据，new 用来表示将要或已经修改的新数据。
-
在delete 触发器中，old 用来表示将要或已经被删除的原数据。delete中没有new新数据。
after 和before 的区别
两者在一般的触发器中并没有什么区别，但是有时会有区别。after 在DML语句执行后才执行；before 在DML语句执行前就执行。
练习
-- 1. 创建触发器，当用户新增订单时，同时更新商品的库存，当用户订单商品数量超过库存时，修改订单为最大库存量。
create trigger tri1
before insert on orders
for each row
begin
declare n int default 0;
select num into n from product where pid=new.pid;
if new.num>n then
set new.num=n;
end if ;
update product set num=num-new.num where pid=new.pid；
end
-- 2. 创建出发器，当用户删除订单时，同时更新商品的库存。
create trigger tri2
after delete on orders
for each row
begin
update product set num=num+old.num where pid=old.pid;
end
-- 3. 创建触发器，当用户修改（增加）订单商品数量时，同时更新商品库存。
create trigger tri3
before update on orders
for each row
begin
delcare n int default 0;
select num into n from product where pid=new.pid;
if (new.num-old.num)>n then
set new.num=old.num+n;
end if ;
update product set num=num-(new.num-old.num) where pid=new.pid;
end
游标 ：关键字 cursor
游标 ：作用就是用于对查询数据库返回的记录进行遍历，以便进行相应的操作。
游标的特征
-
游标是只读的，不能进行增删改操作。
-
游标是不能滚动的，也就是只能在一个方向上进行遍历，不能在记录之间随意进退，不能跳过某些记录。
-
避免在已经打开游标的表上更新数据。
游标的语法结构
-
定义游标 ：declare 游标名 cursor for select语句
-
打开游标 ：open 游标名；
-
获取结果 ：fetch 游标名 into 变量，……(变量是select查询结果)；
-
关闭游标 ：close 游标名；
注意 ：游标一般是和存储过程或函数一起用的。
练习
-- 3-1 创建过程p1，使用游标返回student中的第一个学生信息
create procedure p1()
begin
declare sid,age,cid int;
declare sname,sex varchar(10);
declare cur1 cursor for select * from student;
open cur1;
fetch cur1 into sid,sname,sex,age,cid;
select sid,sname,sex,age;
close cur1;
end
call p1();
-- 3-2 创建过程p2,使用游标提取student的所有学生信息，插入到student2表中
create procedure p2()
begin
declare sid,age,cid int;
declare sname,sex varchar(10);
declare flag int default 0;
declare cur2 cursor for select * from student;
declare continue handler for not found set flag=1;
open cur2;
a:loop
fetch cur2 into sid,sname,sex,age,cid;
if flag=1 then
leave a;
end if;
insert into student2 values(sid,sname,sex,age,cid);
end loop;
close cur2;
end
call p2();
注意 ：返回表中所有字段的时候，变量名称随意，返回表中部分字段时，变量名称不能与表中字段名一致。
MySQL访问控制的两个阶段
-
连接验证 ：主机必须和MySQL授权表中的主机相匹配，要有 有效的用户和密码
-
请求验证 ：连接成功后，客户端发出的每条语句，MySQL会检查客户端是否具有足够的权限来执行该语句。
注意 ：MySQL默认情况下，授权的只有本机，只允许本地连接，远程连接需要修改授权列表。
MySQL用户管理
查看用户
在mysql数据库中，有一个user表，存放着用户的权限及信息
-- 语法 ：
select * from user;
注意 ：使用该语句前，应该先使用mysql数据库 ：
use mysql;
创建用户
-- 语法 ：create user 用户名 identified by 密码;
create user dbadmin@localhost identified by '123123'; -- 本地用户
create user dbroot identified by '123123'; -- 远程连接
注意 ：用户名后面加 @localhost 表示本地用户 ，不加 @localhost 表示远程连接；identified 用于指定密码 ；
给用户重命名
-- 语法 : RENAME user 旧用户名 to 新用户名;
RENAME user cs@localhost to ref@localhost;
删除用户
-- 语法 ：drop user 用户名;
-- 删除用户 dbroot
drop user dbroot;
-- 删除 dbadmin 用户
drop user dbadmin@localhost;
查看用户权限
-- 语法 ：show grants for 用户名;
show grants for dbadmin@localhost;
一般新创建的用户权限是这种 :
注意 ：
-
usage表示该用户只能登陆到数据库服务器的权限，连查看表数据的权限也没有 ； -
*.*表示所有数据库中的所有数据表
给用户授权
授予用户所有权限 ：(超级管理员权限)
-- 语法 ：grant all on *.* to 用户名 with grant option;
-- 给新用户授予超级管理员权限
grant all on *.* to dbadmin@localhost with grant option ;
注意 ：
-
all 表示所有权限 ；
-
with grant option 表示该用户可以给其他用户授权 ；
授予用户部分权限 ：
-- 语法 ：grant 权限 on *.* to 用户名;
-- 授予 cs 用户查询和修改schooldb 数据库中数据的权限
grant select,update on schooldb.* to cs@localhost;
注意：权限表示 insert、select、delete、update、alter、drop等等
撤销用户权限
-- 语法 ：revoke 权限 on *.* from 用户名;
-- 撤销 cs 用户对 schooldb 数据库的select 权限
revoke select on schooldb.* from cs@localhost;
注意：
-
授权的是grant……to , 撤销是 revoke……from
-
撤销权限，操作的用户必须具有with grant option 的权限，被撤销的用户要具有被撤销的权限
数据库备份
备份方式 ：
1、使用 Navicat 导出 – 一般情况下导出结构和数据
2、使用 cmd 命令行导出 – mysqldump 命令使用
好处 ：这样导出的都是数据库的表，可以在任意数据库中导入
# 导出
# mysqldump -h 主机 -u 用户名 -p 数据库 [表1 表2 表3......] > E:/文件名
# 如果不写表名，默认备份该数据库中的全部表
mysqldump -h localhost -u root -p schooldb test >E:/a.sql
#导入 source 备份文件
# 1、登录mysql
# 2、切换到需要备份到那个数据库
# 3、备份文件
#导入练习
mysql -u root -p
mysql> create database test;
mysql> use test
Database changed
mysql> source E:/a.sql;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| course |
| score |
| student |
| teacher |
| test |
+----------------+
5 rows in set (0.00 sec)
MySQL技巧与优化
查找重复记录 ：（分组计数）
通过分组统计的方法查询重复记录。
-- 1-1 使用SQL查询商品表中重复的商品记录
select pname,count(*) from product group by pname having count(*)>1;
-- 1-2 使用SQL语句查询表中商品名称和商品库存都重复的记录
select pname,num,count(*) from product group by pname,num having count(*)>1;
删除重复记录
-- 1-3 使用SQL语句删除商品名称重复的记录，只保留id最大的记录
delete p1 from product p1 inner join product p2
where p1.pid<p2.pid and p1.pname=p2.pname;
选择随机记录 ：（rand()函数）
rand () 函数产生的是 0-1 之间的随机数
使用 order by 和rand () 函数可以给记录随机排序
-- 1-4 使用SQL语句从student表中随机选择一名学生记录
select * from student order by rand() limit 1;
选择第n个最高记录
max 和 min 可以取最高和最低的
-- 语法 ：select * from 表名 order by 列名 desc limit n-1,1;
-- 1-6 查询库存第二多的商品信息
select * from product order by num desc limit 1,1;
比较两张表的数据
-- 1-7 查询A1字段的数据，存在t_a表中，但不存在t_b表中
select * from t_a where a1 not in(select a1 from t_b);
select * from t_a left join t_b on t_a.A1=t_b.A1 where t_b.B2 is null ;
行转列 ：（case……when……then）
行转列使用分支结构中的 case……when……then 来实现
-- 1-8 行转列
select user_name '姓名',
max(case course when '语文' then score else 0 end) '语文',
max(case course when '数学' then score else 0 end) '数学',
max(case course when '英语' then score else 0 end) '英语',
sum(score) '总分'
from test_tb_grade group by user_name;
exists 查询 ：（返回的结果为0或1 ）
exists后的子查询有数据时，前面的查询执行，否则不执行。
查询成绩表中科目编号为 2 的考试成绩中是否存在不及格的学生，如果存在不及 格的学生就将参加科目编号 2 考试的学生编号和成绩全部查询显示出来
-- 1-9 查询成绩表中科目编号为 2 的考试成绩中是否存在不及格的学生，如果存在不及格的学生就将
-- 参加科目编号 2 考试的学生编号和成绩全部查询显示出来
select sid,score.score from score where cid=2 and exists(select sid from score where cid=2 and score.score<60);
all 、any 、some查询
-
>all表示大于每一个值，相当于大于最大值； -
>any表示大于任何一个值，相当于大于最小值； -
some和any的作用是相似的； -
=any与in是等效的 -
<>all和not in是相同的 -
<>any和not in是不同的 ：<>any(a,b,c)表示不等于a，或者不等于b，或者不等于c ；not in (a,b,c)表示不等于a，不等于b，并且不等于c
-- 1-10 查询成绩比科目编号为“1”的这门课程的所有成绩都大的学生考试信息
select * from score where score.score>all(select score.score from score where cid=1);
-- 1-11 查询成绩比科目编号为“1”的任意一个成绩都大的考试信息
select * from score where score.score>any(select score.score from score where cid=1);
union 和union all
注意 ：
-
union 联合的两个select 必须拥有相同的列 ；
-
列必须具有相似的数据类型 ；
-
列的顺序必须相同 ；
-
union因为要去重，所以效率远不如union all ；
-- 1-12 有一张学生表 student 和一张教师表 teacher，查询所有师生信息
select * from student
union
select * from teacher;
计算列
计算列即可以拼接字符串，还可以进行算数运算。
-- 1-13 在存储用户信息时，姓和名是分开的，现在要求通过 sql 查询出用户信息，要求姓名用一个字段显示
select id,concat(first_name,last_name) from `use`;
-- 1-14 price 字段表示单价，discount 字段表示折扣，要求查询出商品信息，并计算出商品的售价
select id,`name`,price*discount,stock from goods;
排名查询 ：（借助变量）
-
先对成绩排序（降序）
-
再给每一列添加行号，排名，定义一个变量作为行号 ；
-
注意 ： 赋值用
：=比较用=
-- 1-15 要求查询出，科目编号为 2 的科目成绩及排名
select sid,s.score,@rownum:=@rownum+1 as rank
from score s,(select @rownum:=0) b
where sid=2 order by s.score desc;
#2.给每科的成绩进行排名
select cid,score,
case
when @b=score.cid then @a:=@a+1
else @a:=1 and @b:=score.cid
end rank
from score,(select @a:=0,@b:=null) ta order by cid,score desc;
分组统计 ：with rollup
with rollup 用来在分组的基础上在进行统计 ；
数据库维护
查看表 ：
查看数据库中所有的表和视图
-- 语法 ：show tables;
查看数据库中所有表和视图和类型
-- 语法 ：show full tables;
模糊查询数据库中的表和视图
-- 语法 ：show tables like '%关键字%';
-- 语法 ：show full tables like ‘%关键字%’;
查看数据表的引擎
-- 语法 ：show table status ;
分析表 ：（可修复索引）
-- 语法 ：analyze table 表名;
-- 3-2 分析test数据库中的score表
analyze table score;
优化表 ：（可进行碎片化整理）
-- 语法 ：optimize table 表名;
-- 3-3 对test数据库中score表进行碎片整理
optimize table score;
检查表
-- 语法 ：check table 表名;
-- 3-4 检查test数据库中score表的完整性
check table score;
注意 ：
-
检查表和分析表的运行结果一样；
-
check table 进检测数据表中的问题，不会修复；
-
analyze table 检查并修复数据表；
修复表 ：（可修复mylsam、Archive和CSV引擎的表）
-- 语法 ：repair table 表名;
数据库设计
糟糕的数据库设计 ：
- 数据冗余，浪费空间
- 数据库插入和删除都会很麻烦、出现异常
- 程序性能差
良好的数据库设计 ：
- 节省内存空间
- 保证数据库的完整性
- 方便我们开发
软件开发中，关于数据库的设计 ：
- 分析需求 ：分析业务和需要处理的数据库的需求
- 概要设计 ：设计关系图 E-R 图
数据库的三大范式
第一范式 ：1NF
原子性 ：保证每一列不可再分
第二范式 ：1NF
前提 ：满足第一范式
每张表只描述一件事情
第三范式 ：1NF
前提 ：满足第一范式和第二范式
需要确保数据表中的每一列数据都和主键之间相关，而不能间接相关
数据库驱动
我们的程序会通过数据库驱动连接数据库，这样的话，连接不同的数据库就需要不同的数据库驱动，显得很繁琐。
JDBC
SUN 公司 为了简化 开发人员对数据库的操作，提供了一个规范 — JDBC
这些规范的具体实现由厂商去做。对于开发人员，只需要掌握JDBC 的操作就行。
下载jar包
java 程序连接数据库需要一个jar 包 – mysql-connector-java ，不同的数据库版本找对应的jar包版本
下载 jar ：
第一个 JDBC 案例
1、创建一个 普通的java 项目
2、引入jar 包
步骤 ：
1、在项目根目录创建 lib 文件夹，将jar 包放进去
2、右键文件夹–> add as library
注意: 当jar包可以打开时，表明jar包已被加载进项目里
3、编写测试代码
步骤 ：
1、加载驱动
2、创建连接
3、创建 statement 对象
4、使用 statement 去执行 SQL
5、查看返回结果
6、释放资源
package com.hkp.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
/**
* 第一个 JDBC 程序
*/
public class Demo1 {
public static void main(String[] args) throws Exception {
//1、加载驱动
// mysql5 : com.mysql.jdbc.Driver
// mysql8 : com.mysql.cj.jdbc.Driver
Class.forName("com.mysql.cj.jdbc.Driver");
//2、用户信息和 URl
String url = "jdbc:mysql://localhost:3306/schooldb?useUnicode=true&characterEncoding=utf8&useSSL=true";
String userName = "root";
String password = "admin";
//3、连接成功，返回数据库对象 connection 代表数据库
Connection connection = DriverManager.getConnection(url, userName, password);
//4、创建 statement 对象，去执行SQL
Statement statement = connection.createStatement();
//5、执行SQL，返回结果集
String sql = "select * from student";
ResultSet resultSet = statement.executeQuery(sql);
//6、查看结果集
while(resultSet.next()){
System.out.print("学号 : "+resultSet.getObject("sid"));
System.out.print("姓名 : "+resultSet.getObject("sname"));
System.out.println();
}
//7、释放连接
resultSet.close();
statement.close();
connection.close();
}
}
statement 可以执行SQL
statement.executeQuery(sql); //查询，返回一个结果集 resultSet
statement.execute(sql); // 可以执行任何SQL
statement.executeUpdate(sql); // 修改、插入、删除 ，返回受影响行数
resultSet : 查询返回的结果集，封装了所有的结果
resultSet.getObject(); // 不知道什么数据类型，使用 object
// 知道是什么数据类型，使用具体的数据类型
resultSet.getString();
resultSet.getInt();
statement 对象
JDBC 中的 statement 对象用于向数据库发送 SQL 语句，向完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。
statement 对象的 executeQuery 方法，用于向数据库发送查询语句，返回一个结果集 resultSet
statement 对象的 executeUpdate 方法，用于向数据库发送修改、插入、删除语句 ，返回受影响行数
create
//使用 executeUpdate 方法完成数据库添加操作
Statement sta = con.createStatement();
String sql = "INSERT INTO `student`(`sname`, `sex`, `age`) VALUES ('test', '男', 42);";
int num = sta.executeUpdate(sql);
if(num>0){
System.out.println("添加成功");
}else{
System.out.println("添加失败");
}
update
//使用 executeUpdate 方法完成数据库修改操作
Statement sta = con.createStatement();
String sql = "UPDATE `student` SET `sname` = 'Tom'WHERE `sname` = 'test';";
int num = sta.executeUpdate(sql);
if(num>0){
System.out.println("修改成功");
}else{
System.out.println("修改失败");
}
delete
//使用 executeUpdate 方法完成数据库删除操作
Statement sta = con.createStatement();
String sql = "delete from student where sid = 6";
int num = sta.executeUpdate(sql);
if(num>0){
System.out.println("删除成功");
}else{
System.out.println("删除失败");
}
select
//使用 executeQuery 方法完成数据库查询操作
Statement statement = connection.createStatement();
String sql = "select * from student";
ResultSet resultSet = statement.executeQuery(sql);
while(resultSet.next()){
System.out.print("学号 : "+resultSet.getObject("sid"));
System.out.print("姓名 : "+resultSet.getObject("sname"));
System.out.println();
}
抽取JDBC工具类
package com.hkp.jdbc;
import java.sql.*;
public class JdbcUtil {
private static final String DRIVERNAME="com.mysql.cj.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost:3306/schooldb?useUnicode=true&characterEncoding=utf8&useSSL=true";
private static final String USER="root",PASSWORD="admin";
//连接数据库
public static Connection getConnection(){
try {
Class.forName(DRIVERNAME);
return DriverManager.getConnection(URL, USER, PASSWORD);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
//增删改方法
public static int executeUpdate(String sql){
Connection conn=null;
Statement sta=null;
try {
conn=getConnection();
sta=conn.createStatement();
return sta.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
return e.getErrorCode()*(-1);
}finally{
closeAll(conn,sta,null);
}
}
//释放资源
public static void closeAll(Connection conn, Statement sta, ResultSet set) {
try {
if(set!=null)
set.close();
if(sta!=null)
sta.close();
if(conn!=null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
使用工具类写增删改查
// 增加
public static void main(String[] args) {
String sql = "INSERT INTO `student`(`sname`, `sex`, `age`) VALUES ('test_create', '男', 42);";
int num = JdbcUtil.executeUpdate(sql);
if(num>0){
System.out.println("添加成功");
}else{
System.out.println("添加失败");
}
}
// 删除
public static void main(String[] args) {
String sql = "delete from student where sid = 8";
int num = JdbcUtil.executeUpdate(sql);
if(num>0){
System.out.println("删除成功");
}else{
System.out.println("删除失败");
}
}
//修改
public static void main(String[] args) {
String sql = "UPDATE `student` SET `sname` = 'test_update' WHERE `sid` = 8";
int num = JdbcUtil.executeUpdate(sql);
if(num>0){
System.out.println("修改成功");
}else{
System.out.println("修改失败");
}
}
//查询
//注意一定要关闭资源
public class TestSelect {
public static void main(String[] args) {
Connection con = null;
Statement sta = null;
ResultSet res= null;
try {
con = JdbcUtil.getConnection();
sta = con.createStatement();
String sql = "select * from student";
res = sta.executeQuery(sql);
while(res.next()){
System.out.print("学号 : "+res.getObject("sid"));
System.out.print("姓名 : "+res.getObject("sname"));
System.out.println();
}
}catch (Exception e){
e.printStackTrace();
System.out.println("出错了");
}finally {
//关闭资源
JdbcUtil.closeAll(con,sta,res);
}
}
}
SQL注入
参考博客 ：https://www.cnblogs.com/myseries/p/10821372.html
SQL注入是比较常见的网络攻击方式之一，它不是利用操作系统的BUG来实现攻击，而是针对程序员编写时的疏忽，通过SQL语句，实现无账号登录，甚至篡改数据库。
SQL 注入会获取用户表里的所有的用户信息
public class Sql {
public static void main(String[] args) {
//login("admin","admin"); 正常登陆
//SQL 注入式登陆
login("' or '1=1","' or '1=1");
}
public static void login(String username,String pwd){
Connection con = null;
Statement sta = null;
ResultSet res= null;
try {
con = JdbcUtil.getConnection();
sta = con.createStatement();
String sql = "select * from user where login_name='"+username+"' and password = '"+pwd+"'";
res = sta.executeQuery(sql);
while(res.next()){
System.out.print("用户名 : "+res.getObject("login_name")+"\t");
System.out.print("密码 : "+res.getObject("password"));
System.out.println();
}
}catch (Exception e){
e.printStackTrace();
System.out.println("出错了");
}finally {
//关闭资源
JdbcUtil.closeAll(con,sta,res);
}
}
}
prepareStatement 对象
prepareStatement 与 statement 对象一样，都可以执行 SQL 语句，但是prepareStatement 对象可以防止 SQL注入，效率更高！并且可以使用 ？ 做占位符
案例 ：增加、修改、删除都是类似的
public static void main(String[] args) {
Connection con = null;
PreparedStatement st = null;
con = JdbcUtil.getConnection();
// 使用 ？ 占位符
String sql = "INSERT INTO `schooldb`.`user`(`id`, `login_name`, `password`) VALUES (?,?,?);";
try {
// 预编译 ：先写SQL 不执行
st = con.prepareStatement(sql);
// 手动给参数赋值
st.setInt(1,12);
st.setString(2,"root");
st.setString(3,"admin");
//执行
int num = st.executeUpdate();
if(num>0){
System.out.println("添加成功");
}else{
System.out.println("添加失败");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
try {
con.close();
st.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
案例 ：查询
public class Test {
public static void main(String[] args) {
Connection con = null;
PreparedStatement st = null;
ResultSet res = null;
try {
con = JdbcUtil.getConnection();
// 使用 ？ 占位符
String sql = "select * from user where id = ?";
// 预编译 ：先写SQL 不执行
st = con.prepareStatement(sql);
// 手动给参数赋值
st.setInt(1, 12);
res = st.executeQuery();
while(res.next()){
System.out.println(res.getString("login_name"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JdbcUtil.closeAll(con,st,res);
}
}
}
PreparedStatement防止 SQL 注入的本质 ：把传递过来的参数，当做一个字符，参数中的转义字符均无效。
使用 JDBC 操作 事务
public class Transaction {
public static void main(String[] args) {
Connection con = null;
PreparedStatement st=null;
try {
con = JdbcUtil.getConnection();
// 关闭数据库的自动提交，自动会开启事务
con.setAutoCommit(false);
String sql1 = "UPDATE `acount` SET `money` = money-100 WHERE name = 'jack';";
st=con.prepareStatement(sql1);
st.executeUpdate();
// 模拟失败案例
int x = 1/0;
String sql2 = "UPDATE `acount` SET `money` = money+100 WHERE name = 'tom';";
st=con.prepareStatement(sql2);
st.executeUpdate();
// 业务完成，提交事务
con.commit();
System.out.println("成功");
} catch (SQLException throwables) {
// 失败默认回滚
try {
con.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
throwables.printStackTrace();
}finally {
JdbcUtil.closeAll(con,st,null);
}
}
}
数据库连接池
池化技术 ：准备一些预先的资源，过来就连接预先准备好的
编写连接池，只需要实现一个接口 ：DataSource
开源数据源实现
DBCP
C3P0
Druid ：阿里巴巴
使用了这些数据库连接池之后，我们在项目中就不需要编写连接数据库的代码了；
DBCP
需要用到的 jar 包 ：commons-dbcp-1.4、commons-pool-1.6
扫一扫
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
