MySQL学习笔记

名字都没了~

已于 2022-07-04 18:49:22 修改

阅读量319

点赞数

文章标签：数据库数据仓库

于 2022-07-04 18:47:29 首次发布

本文链接：https://blog.csdn.net/weixin_45912372/article/details/125606320

版权

1.MySQL

1.1 什么是数据库

概念：数据仓库。安装在操作系统之上

作用：存储数据，管理数据

1.2 数据库分类

关系型数据库

MySQL，Oracle，SqlServer，DB2，SQLlite
通过表和表之间，行和列之间的关系进行数据存储

非关系型数据库：

Redis，MongDB
对象存储，通过自身的属性来决定

DBMS(数据库管理系统)

数据库管理软件，可以科学有效地管理数据(维护和获取数据)
MySQL本质是数据库管理系统

1.4 MySQL简介

MySQL是一个关系型数据库管理系统，开源的数据库软件，体积小，速度快，总体拥有成本低。集群

2.基本命令

2.1 命令行连接：

mysql -uroot -p

2.2 修改密码：

update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost'; -- 123456为要修改的密码
flush privileges; -- 刷新权限

2.3 查看所有数据库

show databases; -- 查看所有的数据库
currentdatabase> use targetdatabase -- 切换数据库 currentdatabase为当前数据库，targetDataBase为目标数据库

2.4 查看数据库中的所有数据表

show tables; -- 查看数据库中所有的表
describe student; -- 显示数据库中所有的表的信息

2.5 创建数据库

create database newdatabase; --创建新的数据库newdatabase

2.6 特殊命令

exit; --退出连接
-- 单行注释
/*
sql的多行注释
hellp
*/

2.7 数据库语言

DDL(数据库定义语言)
DML(数据库操作)
DQL(数据库查询语言)
DCL(数据库控制语言)

3.操作数据库

操作数据库→操作数据库中的表→操作数据库中表的数据

MySQL关键字大小写不敏感

3.1 操作数据库

3.1.1 创建数据库

create database [if not exists] newdatabase;

3.1.2 删除数据库

drop database [if exists] newdatabase;

3.1.3 使用数据库

use school;

3.1.4 查看数据库

show databases; --查看所有的数据库

3.2 数据库的列类型

3.2.1 数值

名称	描述	大小	备注
tinyint	十分小的数据	1个字节
smallit	较小的数据	2个字节
mediumint	中等大小的数据	3个字节
int	标准的整数	4个字节	最常用
bigint	较大的数据	8个字节
float	浮点数	4个字节
double	浮点数	8个字节
decimal	字符串形式的浮点数		金融计算时，一般使用decimal

3.2.2 字符串

名称	描述	大小	备注
char	字符串固定大小	0-255
varchar	可变字符串	0-65535	常用
tinytext	微型文本	2⁸-1
text	文本串	2¹⁶-1	保存大文本

3.2.3 时间日期

名称	描述	备注
date	YYYY-MM-DD	日期格式
time	HH:mm:ss	时间格式
datetime	YYYY-MM-DD HH:mm:ss	最常用的时间格式
timestamp	时间戳，1970.1.1值今的毫秒数
year	年份表示

3.2.4 null

没有值，未知
不要使用NULL进行运算，结果为NUL

3.3 数据库的字段属性

3.3.1 Unsigned

无符号的整数
声明了该列不能声明为负数

3.3.2 zerofill

0填充的
不足的位数使用0来填充
- int(3) 5→005

3.3.3 自增

自动在上一条记录的基础上+1(默认)
通常用于设置唯一主键，必须为整数类型
可以自定义设置主键自增起始值和步长

3.3.4 非空

not null：如果不赋值就会报错
null：默认为null

3.4 创建数据表

create database if not exists school;
use school;
create table if not exists student(
id int(4) not null auto_increment comment '学号', /*auto_increment 自增*/
name varchar(30) not null default '匿名' comment '姓名',
pwd varchar(20) not null default '123456' comment '密码',
sex varchar(2) not null default '男' comment '性别',
birth datetime default null comment '出生日期',
address varchar(100) default null comment '家庭住址',
email varchar(50) default null comment '邮箱',
primary key(id)
)engine=innodb default charset=utf8;

格式

create table [if not exists] 表名(
字段名 列类型 [属性] [索引] [注释],
字段名 列类型 [属性] [索引] [注释],
字段名 列类型 [属性] [索引] [注释],
......
字段名 列类型 [属性] [索引] [注释]
)[表类型] [字符集设置] [注释];

常用命令

show create database school; -- 查看创建数据库的语句
show create table student -- 查看student数据表的定义语句
desc student -- 显示表的结构

3.5 数据表的类型

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大，约为MYISAM的2倍

常规使用操作

MYISAM：节约空间，速度较快
INNODB：安全性高，支持事务的处理。多表多用户操作

MySQL引擎在物理文件上的区别：

INNODB在数据库表中只有一个*.frm文件，以及上级目录下的ibdata文件

(mysql8.0只有.ibd文件)
MYISAM对应文件
- *.frm：表结构定义文件
- *.MYD：数据文件
- *.MYI：索引文件

设置数据库表的字符集编码

charset=utf8

MySQL默认编码是Latin1，不支持中文

在my.ini中配置默认的编码

character-set-server=utf8

3.6 修改删除表

3.6.1 修改

-- 修改表名
-- alter table 旧表名 rename as 新表名
alter table teacher rename as teacher1;
-- 增加表的字段
-- alter table 表名 add 字段名 列属性
alter table teacher1 add age int(11);
-- 修改表的字段 （重命名,修改约束)
-- 修改约束
-- alter table 表名 modify 字段名 列属性[]
alter table teacher1 modify age varchar(11);
-- 字段重命名
-- alter table 表名 change 旧字段名 新字段名 列属性[]
alter table teacher1 change age age1 int(11); 
-- 删除表字段
-- alter table 表名 drop 字段名
alter table teacher1 drop age1;
-- 删除表
-- drop table [if exists] 表名
drop table if exists teacher1;

所有的创建和删除操作应加上判断，以免报错

4.MySQL数据管理

4.1 外键

方式一：在创建表时增加约束

create table if not exists student(
id int(4) not null auto_increment comment '学号', -- auto_increment 自增
name varchar(30) not null default '匿名' comment '姓名',
pwd varchar(20) not null default '123456' comment '密码',
sex varchar(2) not null default '男' comment '性别',
birth datetime default null comment '出生日期',
gradeid int(10) not null  comment '学生的年级',
address varchar(100) default null comment '家庭住址',
email varchar(50) default null comment '邮箱',
primary key(id),
key fk_gradeid(gradeid),
constraint fk_gradeid foreign key (gradeid) references grade(gradeid)
)engine=innodb default charset=utf8;

create table if not exists grade(
gradeid int(10) not null auto_increment comment '年级id',
gradename varchar(50) not null comment '年级名称',
primary key(`gradeid`)
)engine=innodb default charset=utf8;

方式二：创建表成功后，添加外键约束

create table if not exists student(
id int(4) not null auto_increment comment '学号', -- auto_increment 自增
name varchar(30) not null default '匿名' comment '姓名',
pwd varchar(20) not null default '123456' comment '密码',
sex varchar(2) not null default '男' comment '性别',
birth datetime default null comment '出生日期',
gradeid int(10) not null  comment '学生的年级',
address varchar(100) default null comment '家庭住址',
email varchar(50) default null comment '邮箱',
primary key(id)
)engine=innodb default charset=utf8;

create table if not exists grade(
gradeid int(10) not null auto_increment comment '年级id',
gradename varchar(50) not null comment '年级名称',
primary key(`gradeid`)
)engine=innodb default charset=utf8;

-- 创建表时没有外键关系
alter table student add constraint fk_gradeid foreign key(gradeid) references grade(gradeid);

最佳实践

数据库只存放数据，只有行(数据)和列(字段)
使用程序实现外键约束

4.2 DML语言

数据库的意义：数据存储、数据管理

DML语言(数据操作语言)

insert
update
delete

4.2.1 添加

-- 插入语句(添加)
-- insert into 表名([字段名1,字段名2,字段名3]) values ('值1','值2','值3');
insert into grade(gradename) values ('大三');
insert into student(name,pwd,sex) values('张三','aaaaaa','男');

-- 插入多个字段
-- insert into 表名([字段名1,字段名2,字段名3]) values ('值1','值2','值3'),('值4','值5','值6');
insert into grade(gradename) values ('大一'),('大二');

字段可以省略，但后面的值必须一一对应

4.2.2 修改

-- update 表名 set 列名=value[,列名=value,列名=value.....] where 条件
update student set name ='李四',email='123456' where id=1;

条件：where子句 如果没有指定，会修改所有的列

操作符	含义	备注
=	等于
<>或!=	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
between … and …	在某个范围内	between 2 and 5 表示在2到5的闭合区间
and	和	必须满足所有条件
or	或	满足一个条件即可

4.2.3 删除

方式一：delete

-- 语法
-- delete from 表名 [where 条件];
-- 删除数据
delete from student where id=2;

方式二：truncate

作用：完全清空数据库表，表的结构和索引约束不会变

-- 清空student表
truncate student;

delete和truncate对比：

相同点：都能删除数据，且都不会删除表结构
不同点
- truncate会重新设置自增列，计数器会归零
- truncate不会影响事务

5.DQL查询数据

create database if not exists `school`;
-- 创建一个school数据库
use `school`;-- 创建学生表
drop table if exists `student`;
create table `student`(
	`studentno` int(4) not null comment '学号',
    `loginpwd` varchar(20) default null,
    `studentname` varchar(20) default null comment '学生姓名',
    `sex` tinyint(1) default null comment '性别，0或1',
    `gradeid` int(11) default null comment '年级编号',
    `phone` varchar(50) not null comment '联系电话，允许为空',
    `address` varchar(255) not null comment '地址，允许为空',
    `borndate` datetime default null comment '出生时间',
    `email` varchar (50) not null comment '邮箱账号允许为空',
    `identitycard` varchar(18) default null comment '身份证号',
    primary key (`studentno`),
    unique key `identitycard`(`identitycard`),
    key `email` (`email`)
)engine=myisam default charset=utf8;
-- 创建年级表
drop table if exists `grade`;
create table `grade`(
	`gradeid` int(11) not null auto_increment comment '年级编号',
  `gradename` varchar(50) not null comment '年级名称',
    primary key (`gradeid`)
) engine=innodb auto_increment = 6 default charset = utf8;

-- 创建科目表
drop table if exists `subject`;
create table `subject`(
	`subjectno`int(11) not null auto_increment comment '课程编号',
    `subjectname` varchar(50) default null comment '课程名称',
    `classhour` int(4) default null comment '学时',
    `gradeid` int(4) default null comment '年级编号',
    primary key (`subjectno`)
)engine = innodb auto_increment = 19 default charset = utf8;

-- 创建成绩表
drop table if exists `result`;
create table `result`(
	`studentno` int(4) not null comment '学号',
    `subjectno` int(4) not null comment '课程编号',
    `examdate` datetime not null comment '考试日期',
    `studentresult` int (4) not null comment '考试成绩',
    key `subjectno` (`subjectno`)
)engine = innodb default charset = utf8;

-- 插入学生数据
insert into `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
values
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1002,'123456','张益达',1,2,'13800003333','广东深圳','1990-1-2','text122@qq.com','123456345255522334'),
(1003,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233');

-- 插入成绩数据  这里仅插入了一组，其余自行添加
insert into `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
values
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);

-- 插入年级数据
insert into `grade` (`gradeid`,`gradename`) 
values(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');
-- 插入科目数据
insert into `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)values
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);

5.1 查询指定字段

-- 语法
-- select 字段 from 表名
-- 查询全部信息
-- select * from 表名
select * from student;

运行结果：
在这里插入图片描述

-- 查询指定字段
select studentno,studentname from student;

运行结果：

在这里插入图片描述

-- 别名：给结果起一个名字 as可以省略
select studentno as 学号,studentname as 姓名 from student;

运行结果：

在这里插入图片描述

-- 函数 concat(a,b)
select concat('姓名:',studentname) as name from student;

运行结果：

在这里插入图片描述

5.2 去重

-- 查询参加考试的学生及成绩
select * from result; -- 查询全部成绩
select studentno from result; -- 查询参加考试的学生
select distinct studentno from result; -- 去重

5.3 模糊查询

运算符	语法	描述
is null	a is null	如果操作符为null,结果为真
is not null	a is not null	如果操作符为not null,结果为真
between	a between b and c	如果a在b和c之间,结果为真
like	a like b	SQL匹配，如果a能匹配b，则结果为真
in	a in (a1,a2,a3…)	如果a在in后范围中，结果为真

-- 查询姓张的同学
select studentno,studentname from student where studentname like '张%';

运行结果：

在这里插入图片描述

-- 查询姓张的同学，且名字后面只有一个字的
select studentno,studentname from student where studentname like '张_';

在这里插入图片描述

5.4 联表查询

5.4.1 联表查询

思路：

分析查询的字段来自那些表
确定使用哪种连接查询
确定交集

-- 联表查询
-- 查询参加考试的同学(学号、姓名、科目编号、分数)、
select s.studentno,studentname,subjectno,studentresult 
from student as s
inner join result as r
where s.studentno=r.studentno;

运行结果：

在这里插入图片描述

操作	描述
inner join	链表点的双方有一个只非空，则都会返回记录（不匹配的则为空）
left join	以左表的非空交叉点为主，右表的是否非空无关
right join	以右表的交叉点非空判断，与左表无关

连接查询 join (连接的表) on (判断条件)

等值查询 where

-- 查询参加考试的学生信息：学号，姓名。科目名，分数
select distinct s.studentno,studentname,subjectname,studentresult
from student s
right join result r
on s.studentno=r.studentno
inner join subject sub
on r.subjectno=sub.subjectno;

运行结果

在这里插入图片描述

5.4.2 自连接

一张表拆分为两张表即可

use school;
drop table if exists category;
create table  if not exists category(
categoryid INT(3) NOT NULL COMMENT 'id', 
pid int(3) not null comment '父id 没有父则为1', 
categoryname varchar(10) not null comment '种类名字', 
primary key (categoryid) 
)engine=innodb charset=utf8; 
insert into category (categoryid, pid, categoryname) VALUES 
('2', '1', '信息技术'),
('3', '1', '软件开发'),
('5', '1', '美术设计'),
('4', '3', '数据库'),
('8', '2', '办公信息'),
('6', '3', 'web开发'),
('7', '5', 'ps技术');

-- 查询父类和子类信息
select f.categoryname as '父栏目',s.categoryname as '子栏目'
from category as f, category as s
where f.categoryid=s.pid;

运行结果
在这里插入图片描述

5.5 分页和排序

5.5.1 分页(limit)

作用

缓解数据库压力
提升用户体验

-- 分页 每页显示3条数据
-- 语法：limit 起始值,页面大小
-- 第一页	limit 0,3
-- 第二页	limit 3,3
-- 第n页	limit 3*(n-1),3
/*
limit (n-1)*pagesize,pagesize
[pagesize:页面大小]
[(n-1)*pagesize:起始值]
[n:当前页]
[数据总数/页面大小+1=总页数]
*/
select distinct s.studentno,studentname,subjectname,studentresult
from student s
right join result r
on s.studentno=r.studentno
inner join subject sub
on r.subjectno=sub.subjectno
order by studentresult desc
limit 0,3;

运行结果

在这里插入图片描述

5.5.2 排序(order by)

升序：asc 降序：desc

-- 查询参加考试的学生信息：学号，姓名。科目名，分数
-- 查询结果根据成绩降序排序
select distinct s.studentno,studentname,subjectname,studentresult
from student s
right join result r
on s.studentno=r.studentno
inner join subject sub
on r.subjectno=sub.subjectno
order by studentresult desc;

运行结果

在这里插入图片描述

6.MySQL函数

MySQL :: MySQL 8.0 Reference Manual

6.1 常用函数

-- 数学运算
select abs(-8); -- 绝对值
select ceiling(9.4); -- 向上取整
select floor(9.4); -- 向下取整
select rand(); -- 生成随机数
select sign(10); -- 判断一个数的符号 0-0 负数返回-1 正数返回1
-- 字符串函数
select char_length('啦啦啦啦aaa'); -- 字符串长度
select concat('a','b','c'); -- 拼接字符串
select insert('helloworld',1,5,'hi'); -- 插入，替换
select lower('HELLO'); -- 转小写字母
select upper('hello'); -- 转大写字母
select reverse('hello'); -- 反转
-- 时间和日期
select current_date(); -- 获取当前的日期
select curdate(); -- 获取当前日期
select now(); -- 获取当前时间
select localtime(); -- 获取本地时间
select sysdate(); -- 获取系统时间
select localtimestamp(); -- 获取当地时间戳

-- 系统函数
select user(); -- 获取当前用户
select version(); -- 获取版本信息

6.2 聚合函数

函数名称	描述
count()	计数
sum()	求和
avg()	求平均值
max()	取最大值
min()	取最小值
…	…

6.3 数据库级别的MD5加密

概念：MD5_百度百科 (baidu.com)

不可逆

-- 测试MD5加密
drop table if exists testmd5;
create table if not exists testmd5(
id int(4) not null,
name varchar(20) not null,
pwd varchar(50) not null,
primary key(id)
)engine=InnoDB default charset=utf8;
-- 明文密码
insert into testmd5 values
(1,'张三','123456'),(2,'李四','135792468'),(3,'王五','987654321');
-- 加密
update testmd5 set pwd=md5(pwd) where id=1;

-- 插入时加密
insert into testmd5 value (4,'小明',md5('12345'));
-- 校验:将用户传递进来的密码进行md5加密，然后比对加密后的值
select * from testmd5 
where name='小明' and pwd=md5('12345');

7.事务

事务原则(ACID原则)：原子性，一致性，隔离性，持久性

参考博客：事务ACID理解_dengjili的博客-CSDN博客_事务acid

原子性：事务中的动作要么一起成功，要么一起失败

一致性：事务在结束后必须符合逻辑运算

持久性：事务结束后的数据不随着外界原因丢失

事务没有提交->恢复原状
事务已经提交->持久化到数据库
事务一旦提交便不可逆

隔离性：多个事务同时进行时，不影响其他事务

脏读：一个事务读取了另外一个数据未提交的数据
不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同
幻读：在一个事务内读取到别的事务插入的数据，导致前后结果不一致

-- mysql 默认开启事务自动提交
set autocommit=0; -- 关闭
set autocommit=1; -- 开启

-- 手动处理事务

-- 事务开启
start transaction; -- 标记一个事务的开始
/*
sql语句
*/
-- 提交：持久化
commit;
-- 回滚：回到原来的样子
rollback;
-- 事务结束
set autocommit=1; -- 开启自动提交

savepoint name; -- 设置一个事务的保存点
rollback to savepoint name; -- 回滚到保存点
release savepoint name; -- 撤销保存点

8.索引

索引是帮助MySQL高效获取数据的数据结构

索引在数据量大的时候作用明显

8.1 索引的分类

主键索引 primary key
- 唯一标识，主键不可重复 只能有一个列作为主键
唯一索引 unique key
- 避免出现重复列，唯一索引可重复，多个列都可以标识为唯一索引
常规索引 key/index
- 默认
全文索引 fulltext
- 在特定数据库引擎下才有
- 快速定位数据

-- 显示所有的索引信息
show index from student;

-- 增加一个全文索引
-- alter table 表名 add fulltext index 索引名(列名)
alter table student add fulltext index studentname(studentname);

-- explain 分析sql执行的状况
explain select * from student; -- 非全文索引
explain select * from student where match(studentname) against ('张');

8.2 索引原则

索引不是越多越好，会造成空间的浪费
对经常变动的数据不应加索引
小数据量的表不需要加索引
索引一般加在常用来查询的字段上

9. 权限管理和备份

9.1 用户管理

用户表：mysql.user

本质：对用户表进行增、删、改、查

9.2 MySQL备份

保证数据不丢失
数据转移

备份方式

拷贝物理文件
使用可视化工具中手动导出

使用命令行导出

-- mysqldump -h主机 -u用户名 -p密码 数据库 [表1,表2,...]> 物理磁盘位置/文件名

使用命令行导入

-- 登录的情况下，切换到指定的数据库
-- source 备份文件
-- 未登录情况下
-- mysql -u用户名 -p密码 库名< 备份文件

10. 规范数据库设计

良好的数据库设计

节省内存空间
保证数据库的完整性
方便系统开发

9.1 设计数据库的步骤

收集信息，分析需求
标识实体，把需求转换为每个字段
标识实体之间的关系

9.2 三大范式

参考博客：数据库三大范式是什么？(3NF详解)_Dzzzzzzzzzzzzzzz的博客-CSDN博客_数据库三大范式是什么

第一范式

原子性：保证每一列都不可分

第二范式

满足第一范式且不存在部分依赖

每张表只描述一件事情

第三范式

满足第二范式且不存在传递依赖

数据表中每一列数据都和主键直接相关

关联查询的表不能超过三张表

11.JDBC

java.sql
javax.sql
数据库驱动

11.1 JDBC程序

package com.example.demo1;

import java.sql.*;

public class JdbcDemo1 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //用户信息和url
        String url="jdbc:mysql://localhost:3306/school?userUnicode=true&characterEncoding=utf8&useSSL=true";
        String username="**";//用户名
        String password="******";//密码
        //连接成功，数据库对象
        Connection connection=DriverManager.getConnection(url,username,password);
        //执行SQL的对象
        Statement statement=connection.createStatement();
        //执行SQL对象去执行SQL，可能存在结果，查看返回结果
        String sql="select * from student";
        ResultSet resultSet=statement.executeQuery(sql);//返回的结果集，结果集中封装了所有查询结果
        while (resultSet.next()){
            System.out.println("studentno="+resultSet.getObject("studentno"));
            System.out.println("loginpwd="+resultSet.getObject("loginpwd"));
            System.out.println("studentname="+resultSet.getObject("studentname"));
            System.out.println("sex="+resultSet.getObject("sex"));
            System.out.println("gradeid="+resultSet.getObject("gradeid"));
            System.out.println("phone="+resultSet.getObject("phone"));
            System.out.println("address="+resultSet.getObject("address"));
            System.out.println("borndate="+resultSet.getObject("borndate"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("identitycard="+resultSet.getObject("identitycard"));
            System.out.println("====================================");
        }
        //释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

运行结果

在这里插入图片描述

DriverManager

Class.forName("com.mysql.jdbc.Driver");//加载驱动

Connection connection=DriverManager.getConnection(url,username,password);
// connection代表数据库
connection.rollback();//事务回滚
connection.commit();//事务提交
connection.setAutoCommit();//设置自动提交

URL

/* 
mysql默认端口号:3306
jdbc:mysql://主机地址:端口号/数据库名?参数1&参数2&参数3...
*/

Statement PrepareStatement

String sql="";//编写SQL
statement.executeQuery(sql);//查询操作，返回ResultSet
statement.execute(sql);//执行任何sql语句
statement.executeUpdate(sql);//更新、插入、删除，返回受影响行数

ResultSet 查询得到的结果集，封装了所有的查询结果

// 获得指定的数据类型
resultSet.getObject();//在不知道列类型情况下使用
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
...
//遍历，指针
resultSet.beforeFirst();//移动到最前面
resultSet.afterLast();//移动到最后面
resultSet.next();//移动到下一个数据
resultSet.previous();//移动到前一行
resultSet.absolute(row);//移动到指定行

释放连接

resultSet.close();
statement.close();
connection.close();

11.2 Statement对象

11.2.1 使用executeUpdate(String sql)方法完成删除数据

Statement st=conn.createStatement();
String sql="delete from user where id=1";
int num=st.executeUpdate(sql);
if(num>0){
    System.out.println("删除成功！");
}

11.2.2 使用executeUpdate(String sql)方法添加数据

Statement st=conn.createStatement();
String sql="insert into user(...) values(...)";
int num=st.executeUpdate(sql);
if(num>0){
    System.out.println("插入成功！");
}

11.2.3 使用executeUpdate(String sql)方法修改数据

Statement st=conn.createStatement();
String sql="update user set name='' where name=''";
int num=st.executeUpdate(sql);
if(num>0){
    System.out.println("修改成功！");
}

11.2.4使用executeQuery(String sql)方法查询数据

Statement st=conn.createStatement();
String sql="select * from user where id=1";
ResultSet rs=st.executeQuery(sql);
while(rs.next){
    //根据获取列的数据类型，分别调用rs的相应方法映射到java对象中
}

11.2.5 代码实现

db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true
username=****//用户名
password=******//密码

JdbcUtils.java

package com.example.demo2.utils;

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 inputStream=JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties=new Properties();
            properties.load(inputStream);
            driver=properties.getProperty("driver");
            url=properties.getProperty("url");
            username=properties.getProperty("username");
            password=properties.getProperty("password");
            //驱动只用加载一次
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }
    //释放资源
    public static void release(Connection connection, Statement statement, ResultSet resultSet){
        if(resultSet!=null){
            try{
                resultSet.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
        if(statement!=null){
            try {
                statement.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
        if(connection!=null){
            try{
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

11.2.5.1插入数据

package com.example.demo2.test;

import com.example.demo2.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestInsert {
    public static void main(String[] args) {
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try {
            connection= JdbcUtils.getConnection();
            statement=connection.createStatement();
            String sql=
                    "insert into student(studentno,loginpwd,studentname,sex,gradeid,phone,address,borndate,email,identitycard)" +
                    "values(1004,'1234567','王五',0,3,'13800002233','中国台湾','1999-10-10','text333@qq.com','123456198033311234')";
            int num=statement.executeUpdate(sql);
            if(num>0){
                System.out.println("插入成功!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

11.2.5.2 删除数据

package com.example.demo2.test;

import com.example.demo2.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestDelete {
    public static void main(String[] args) {
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try {
            connection= JdbcUtils.getConnection();
            statement=connection.createStatement();
            String sql= "delete from student where studentno=1004";
            int num=statement.executeUpdate(sql);
            if(num>0){
                System.out.println("删除成功!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

11.2.5.3 修改数据

package com.example.demo2.test;

import com.example.demo2.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestUpdate {
    public static void main(String[] args) {
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try {
            connection= JdbcUtils.getConnection();
            statement=connection.createStatement();
            String sql= "update student set studentname='李四',sex=1 where studentno='1000' ";
            int num=statement.executeUpdate(sql);
            if(num>0){
                System.out.println("更新成功!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

11.2.5.4 查询数据

package com.example.demo2.test;

import com.example.demo2.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestSelect {
    public static void main(String[] args) {
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try {
            connection= JdbcUtils.getConnection();
            statement=connection.createStatement();
            String sql= "select * from student where studentno=1002";
            resultSet=statement.executeQuery(sql);
            while(resultSet.next()){
                System.out.println(resultSet.getString("studentname"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

11.3 SQL注入

本质：SQL存在漏洞，会被攻击导致数据泄露。sql会被拼接

package com.example.demo2.test;

import com.example.demo2.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SQLInject {
    public static void main(String[] args) {
        //login("1002","123456");
        login("''or '1=1'","''or '1=1'");
    }
    //登录业务
    public static void login(String username,String password){
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try {
            connection= JdbcUtils.getConnection();
            statement=connection.createStatement();
            String sql= "select * from student where studentno="+username+" and loginpwd="+password;
            resultSet=statement.executeQuery(sql);
            while(resultSet.next()){
                System.out.println(resultSet.getString("studentname"));
                System.out.println(resultSet.getString("loginpwd"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

运行结果

在这里插入图片描述

11.4 PrepareStatement对象

防止SQL注入的本质：把传递进来的参数当做字符，并忽略其中的转义字符

11.4.1 增加数据

package com.example.demo3;

import com.example.demo2.utils.JdbcUtils;

import java.sql.*;
import java.util.Date;

public class TestInsert {
    public static void main(String[] args) {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        try{
            connection=JdbcUtils.getConnection();
            //使用?占位替代参数
            String sql="insert into student(studentno,loginpwd,studentname,sex,gradeid,phone,address,borndate,email,identitycard)" +
                    "values(?,?,?,?,?,?,?,?,?,?)";
            preparedStatement=connection.prepareStatement(sql);//预编译sql
            //手动给参数赋值
            preparedStatement.setInt(1,1004);
            preparedStatement.setString(2,"123456");
            preparedStatement.setString(3,"王五");
            //1004,'1234567','王五',0,3,'13800002233','中国台湾','1999-10-10','text333@qq.com','123456198033311234'
            preparedStatement.setInt(4,0);
            preparedStatement.setInt(5,3);
            preparedStatement.setString(6,"13800002233");
            preparedStatement.setString(7,"中国台湾");
            preparedStatement.setDate(8,new java.sql.Date(new Date().getTime()));
            preparedStatement.setString(9,"text333@qq.com");
            preparedStatement.setString(10,"123456198033311234");
            int num=preparedStatement.executeUpdate();
            if(num>0){
                System.out.println("插入成功！");
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,null);
        }

    }
}

11.4.2 删除数据

package com.example.demo3;

import com.example.demo2.utils.JdbcUtils;

import java.sql.*;

public class TestDelete {
    public static void main(String[] args) {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        try{
            connection=JdbcUtils.getConnection();
            //使用?占位替代参数
            String sql="delete from student where studentno=?";
            preparedStatement=connection.prepareStatement(sql);//预编译sql
            //手动给参数赋值
            preparedStatement.setInt(1,1004);
            int num=preparedStatement.executeUpdate();
            if(num>0){
                System.out.println("删除成功！");
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,null);
        }

    }
}

11.4.3 更新数据

package com.example.demo3;

import com.example.demo2.utils.JdbcUtils;

import java.sql.*;

public class TestUpdate {
    public static void main(String[] args) {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        try{
            connection=JdbcUtils.getConnection();
            //使用?占位替代参数
            String sql="update student set studentname=? where studentno=?";
            preparedStatement=connection.prepareStatement(sql);//预编译sql
            //手动给参数赋值
            preparedStatement.setString(1,"张伟");
            preparedStatement.setString(2,"1002");
            int num=preparedStatement.executeUpdate();
            if(num>0){
                System.out.println("更新成功！");
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,null);
        }

    }
}

11.4.4 查询数据

package com.example.demo3;

import com.example.demo2.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestSelect {
    public static void main(String[] args) {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        try{
            connection=JdbcUtils.getConnection();
            String sql="select * from student where studentno=?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,1002);
            resultSet=preparedStatement.executeQuery();
            while (resultSet.next()){
                System.out.println(resultSet.getString("studentname"));
            }
        }catch (SQLException e){
            e.printStackTrace();
        }

    }
}

11.4.5 防止SQL注入

package com.example.demo2.test;

import com.example.demo2.utils.JdbcUtils;

import java.sql.*;

public class SQLInject {
    public static void main(String[] args) {
        //login("1002","123456");
        login("''or '1=1'","''or '1=1'");
    }
    //登录业务
    public static void login(String studentname,String password){
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        try {
            connection= JdbcUtils.getConnection();
            String sql= "select * from student where studentno=? and loginpwd=?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,studentname);
            preparedStatement.setString(2,password);
            resultSet=preparedStatement.executeQuery();
            while(resultSet.next()){
                System.out.println(resultSet.getString("studentname"));
                System.out.println(resultSet.getString("loginpwd"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }
}

运行结果：

不会报错，也不会显示数据
在这里插入图片描述

11.5 JDBC操作事务

account数据表

drop table if exists account;
create table account(
    id int primary key auto_increment,
    name varchar(40),
    money float
);

insert into account(name, money) VALUES
('A',1000),('B',1000),('C',1000);

代码实现

package com.example.demo4;

import com.example.demo2.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestTransaction {
    public static void main(String[] args) {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        try{
            connection= JdbcUtils.getConnection();
            //关闭数据库自动提交，自动会开启事务
            connection.setAutoCommit(false);
            String sql_update1="update account set money=money-100 where name='A'";
            preparedStatement=connection.prepareStatement(sql_update1);
            preparedStatement.executeUpdate();

            String sql_update2="update account set money=money+100 where name='B'";
            preparedStatement=connection.prepareStatement(sql_update2);
            preparedStatement.executeUpdate();
            //业务完毕,提交事务
            connection.commit();
            System.out.println("成功!");
        }catch (SQLException e){
            try{
                connection.rollback();
            }catch (SQLException e1){
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }
}

11.6 数据库连接池

池化技术：提前准备一些资源，在需要时可以重复使用这些预先准备的资源

参考博客：【池化技术】池化技术基础和原理_小熊coder的博客-CSDN博客_池化技术

下载地址：

commons-dbcp2-2.9.0.jar：

DBCP – Download Apache Commons DBCP

commons-pool2-2.11.1.jar：

Pool – Download Apache Commons Pool

DBCP2之后的版本需要logging包

Apache Commons Logging - Download Apache Commons Logging

dbcpconfig.properties

#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=411307

#<!-- 初始化连接 -->
initialSize=10

#最大连接数量
maxActive=50

#<!-- 最大空闲连接 -->
maxIdle=20

#<!-- 最小空闲连接 -->
minIdle=5

#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：【属性名=property;】
#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8

#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的只读（read-only）状态。
#如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix）
defaultReadOnly=

#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

JdbcUtils_DBCP.java

package com.example.demo5.utils;


import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;


import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils_DBCP {
    private  static  BasicDataSource dataSource=null;
    static {
        try{
            InputStream inputStream=JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties properties=new Properties();
            properties.load(inputStream);

            //创建数据源
            dataSource = BasicDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() throws SQLException{
        return dataSource.getConnection();//从数据源中获取连接
    }
    //释放资源
    public static void release(Connection connection, Statement statement, ResultSet resultSet){
        if(resultSet!=null){
            try{
                resultSet.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
        if(statement!=null){
            try {
                statement.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
        if(connection!=null){
            try{
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

代码实现

package com.example.demo5.test;

import com.example.demo5.utils.JdbcUtils_DBCP;

import java.sql.*;

public class TestDBCP {
    public static void main(String[] args) {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        try{
            connection= JdbcUtils_DBCP.getConnection();
            //使用?占位替代参数
            String sql="insert into student(studentno,loginpwd,studentname,sex,gradeid,phone,address,borndate,email,identitycard)" +
                    "values(?,?,?,?,?,?,?,?,?,?)";
            preparedStatement=connection.prepareStatement(sql);//预编译sql
            //手动给参数赋值
            preparedStatement.setInt(1,1004);
            preparedStatement.setString(2,"123456");
            preparedStatement.setString(3,"王五");
            //1004,'1234567','王五',0,3,'13800002233','中国台湾','1999-10-10','text333@qq.com','123456198033311234'
            preparedStatement.setInt(4,0);
            preparedStatement.setInt(5,3);
            preparedStatement.setString(6,"13800002233");
            preparedStatement.setString(7,"中国台湾");
            preparedStatement.setDate(8,new java.sql.Date(new java.util.Date().getTime()));
            preparedStatement.setString(9,"text333@qq.com");
            preparedStatement.setString(10,"123456198033311234");
            int num=preparedStatement.executeUpdate();
            if(num>0){
                System.out.println("插入成功！");
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally {
            JdbcUtils_DBCP.release(connection,preparedStatement,null);
        }
    }
}

名字都没了~

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MySQL学习笔记

概念：数据仓库。安装在操作系统之上作用：存储数据，管理数据关系型数据库非关系型数据库：DBMS(数据库管理系统)MySQL是一个关系型数据库管理系统，开源的数据库软件，体积小，速度快，总体拥有成本低。集群2.2 修改密码：2.3 查看所有数据库2.4 查看数据库中的所有数据表2.5 创建数据库2.6 特殊命令2.7 数据库语言DDL(数据库定义语言)DML(数据库操作)DQL(数据库查询语言)DCL(数据库控制语言)3.操作数据库操作数据库→操作数据库中的表→操作数据库
复制链接

扫一扫