"MySQL [图片] > 数据库 > 操作系统，数据结构与算法 > 离散数学，数字电路，体系结构，编译原理数据库 ::DB::（DataBase）概念：软件安装在操作系统之上作用：存储，管理数据库分类关系型：SQL MySQL Oracle SqlServer DB2 ...."

someone1
AI专业者开发社区 1 号成员
AI开发者社区 • 0 回帖 • 5 浏览 • 5 个月前

Mysql基础

MySQL

> 数据库
> 操作系统，数据结构与算法
> 离散数学，数字电路，体系结构，编译原理

数据库

::DB::（DataBase）
概念：软件安装在操作系统之上
作用：存储，管理

数据库分类

关系型：SQL

MySQL Oracle SqlServer DB2 SQLlite

非关系型：NoSQL

Redis MongDB

::DBMS:: 数据库管理系统

MySQL 简介

是一个关系型数据库管理系统
5.7 稳
8.0
尽量不要使用 exe
使用压缩包安装

数据库语言

CRUD 增删改查

DDL 定义

DML 操作

DQL 查询

DCL 控制

操作数据库

不区分大小写

操作数据库（了解）

创建数据库
```
create database [if not exists] school
```
删除数据库
```
drop database [if exists] school 
```
使用数据库
```
USE `school`
```

查询数据库

SHOW DATABASES -- 查看所有数据库

数据库列类型

> 数值

tinyint 1 字节
smallint 2 字节
mediumint 3 字节
int 4 字节
bigint 8 字节
float 4 字节
double 8 字节
decimal 金融计算使用

> 字符串

char 0~255
varchar 0~65535
tinytext 2^8-1
text 2^16-1 保存大文本

> 时间日期

java.util.Date

date YYYY-MM-DD 日期
time HH：mm：ss 时间
datetime YYYY-MM-DD HH：mm：ss
timestamp 时间戳 1970.1.1 到现在的毫秒数
year 年份

> null

没有值
注意：不要用 null 进行运算

数据库的字段属性（重点）

Unsigned：

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

ZF zerofill：

0 填充的
不足的位数，使用 0 来填充

自增 UN：

自动在上一条记录的基础上 +1
通常用来设计主键，必须为整数
可自定义设计主键自增的起始值、步长

非空 NN：

必须有值
null 不填默认是 null

默认：

设置默认值

基本字段：


id	主键
version	乐观锁
is_delete	伪删除
gmt_create	创建时间
gmt_update	修改时间

创建数据库表

-- not null 非空 auto_increment 自增 comment 注释 default 默认值
create table if not exists `student`(
	`id` int(4) not null auto_increment comment '学号',
    `name` varchar(30) not null default '匿名' comment '姓名',
    `pwd` varchar(20) not null comment '密码',
    `sex` varchar(2) not null default '女' comment '性别',
    `birthday` datetime default null comment '出生日期', 
    `address` varchar(100) default null comment '地址',
    `email` varchar(50) default null comment '邮箱',
    primary key(`id`)
)engine=innodb default charset=utf8

格式

create table [if not exists] '表明'（
	`字段名` 列类型 [属性] [索引] [注释],
	...
	`字段名` 列类型 [属性] [索引] [注释]
）[表类型][字符集设置][注释]

常用命令

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

数据表类型

INNODB 默认

MYISAM 早年

	MYISAM	INNODB
事务支持	×	√
数据行锁定	×	√
外键	×	√
全文索引	√	×
表空间大小	小	大
	节约空间，速度快	安全性高，多表多用户操作

所有数据存在 data

修改删除表

-- 修改表名 alter table 旧表名 rename as 新表名
alter table studenta rename as student
-- 增加字段 alter table 表名 add 字段名 列属性
alter table student_a add age int(11)
-- 修改字段 
-- alter table 表名 modify 字段名 列属性
alter table student_a modify age varchar(11) -- 修改约束
-- alter table 表名 change 字段名 列属性
alter table student_a change age age1 int(11) -- 重命名
-- 删除字段 alter table 表名 drop 字段名
alter table student_a drop age1
-- 删除表
drop table if exists student_a

所有创建删除尽量加上判断

MySQL 数据管理

外键（了解）

alter table `student` add constraint `FK_gradeid` foreign key(`gradeid`) references `grade`(`gradeid`);

以上为物理外键，不建议

DML 语言（全部记住）

DML 语言：数据操作语言

insert
update
delete

添加

-- insert into 表名（[字段名 1，字段名 2，字段名 3]）value（'值 1','值 2','值 3'）
insert into `grade`(`gradename`,`pwd`) values('大四','qqqqqq')
insert into `grade`(`gradename`) values('大三'),('大四')

修改

-- update 表名 set 列名 ='具体值'[,` 列名 `='具体值',...] where 条件
update `student` set `name`='aa' where id = 1

where 条件（一定要写！）

操作符	含义	范围	结果
=	等于	5=6	false
<> 或!=	不等于	5<>6	true
>
<
<=
>=
between...and...	在... 之间	[2,5]
and	和
or	或

current_time 变量时间

删除

delete from `student` where id = 1;

清空

truncate `student`

> truncate 和 delete 区别

相同点：都能删除数据，都不会删除表结构
不同点：

truncate 重新设置自增列计数器归零，不会影响事物

delete 删除的问题，重启数据库：（了解）

innoDB 自增列从 1 开始（存在内存中，断电即失）
MyISAM 从上个增量开始（存在文件中，不丢失）

DQL 查询数据 (最重点)

Date Quary LANGUAGE(DQL)

select 语法

select [all|distinct]
{*|table.*|[table.field1[as alias1][,table.field2[as alias2][,...]]}
from table_name [as table_alias]
            [left|right|inner join table_name2]   -- 联合查询
            [where ...]   -- 满足条件
            [group by ...]   -- 结果按哪几个字段分组
            [having]   -- 过滤分组的字段记录必须满足次要条件
            [order by ...]   -- 指定查询记录按一个或多个条件排序
            [limit {[offset,]row_count|row_countoffset offset}];   -- 指定查询记录从哪条至哪条

Select 字段

-- select 字段,... from 表 select * from student select `name`,`pwd` from student select `name` as 姓名,`pwd` as 密码 from student as 学生表 -- 别名 as -- 函数 concat(a,b) 拼接字段 select concat('姓名：',name) as 新名字 from student; -- 结果新名字 -- 姓名：aaa

distinct 去重

select distinct `gradename` from grade

表达式

文本、列、null、函数、计算表达式、系统变量

-- select 表达式 from 表 select version() -- 查版本号（函数） select 100*3-1 as 计算结果 -- 计算（表达式） select @@auto_increment_increment -- ** 查询自增步长（变量）**

select id,age+1 as '加一岁后' from student

where 条件子句

返回布尔值

逻辑运算符

运算符（尽量用英语）	描述
and &&	与
or \|\|	或
not !	非

select `studentid`,`studentresult` from result where studentresult>=90 and studentresult<=100

select studentid,studentresult from result where studentresult between 90 and 100

模糊查询：比较运算符

运算符	语法	描述
is null	a is null	null 为真
is not null	a is not null	不为 null 为真
between	a between b and c	之间为真
like	a like b 注：结合 % 和 _ 使用	字符中有 b
in	a in (a1,a2,a3...) 注意：a1... 必须为具体值	为其中的某个值为真

-- like  %(任意个字符)  _(一个字符)
select `id`,`name` from `student`
where name like 'a_'  -- 查询姓 a 的
where name like '%a%'  -- 查询名字中有 a 的

联表查询

-- join（连接的表） on（判断的条件）连接查询 -- where 等值查询（可以把 on 改成 where） -- inner join 交集 select s.studentid,studentname,strdentresult from student as s inner join result as r where s.studentid = r.studentid -- right join 右查询 select s.studentid,studentname,strdentresult from student as s right join result as r on s.studentid = r.studentid

-- left join 左查询 select s.studentid,studentname,strdentresult from student as s left join result as r on s.studentid = r.studentid

操作	描述
inner join	如果表中至少有一个匹配就返回
right join	返回所有右表值，即使左表中没有
left join	返回所有左表值，即使右表中没有

-- 查参加了考试的同学的学号，姓名，科目，分数
select s.studentid,studentname,subjectid,result
from student as s
right join result as r
on r.studentid=s.studentid
inner join subject as sub
on r.subjectid=sub.subjectid

查哪些数据           select...                          注意：如有相同数据必须（表明.数据）
在那几个表中         from 表 as 别名
*什么连接方式        xxx join 连接的表 as 别名
*什么条件           on 交叉条件
*什么连接方式        xxx join 连接的表 as 别名
*什么条件           on 交叉条件

> from a right join b

> from a left join b

自连接

核心：一张表拆为两张一样的表

父类

myid	name
2	水果
3	蔬菜
4	其他

子类

fatherid	myid	name
2	5	西瓜
3	6	西兰花
4	7	大米
4	8	飞机

父类 --- 子类（结果）

父类	子类
水果	西瓜
蔬菜	西兰花
其他	大米
其他	飞机

select name as '父',name as '子'
from things as f,things as z
where f.myid=z.fatherid

分页 limit 和排序 order by

排序：升序降序

order by result asc -- 升序

order by result desc -- 降序

分页

语法：limit 起始值，页面大小

limit 0,5 显示 1~5 条数据

limit 1,5 显示 2~6 条数据

pagesize：页面大小
n: 当前页
(n-1)*pagesize 起始值
总数 / 页面大小 = 总页数

-- 查询学生 id，姓名，课程，分数
select s.studentid,studentname,subject,result
from student s
inner join result r
on r.studentid=s.studentid
inner join subject sub
on r.subjectid=sub.subjectid
where subjectname='数据结构'
order by result asc  -- 升序
limit 5,5

子查询

where(这个值是计算出来的)

本质：在 where 语句中嵌套一个子查询语句

-- 查询分数不小于 80 分的学生学号和姓名
select distinct studentid,studentname
from student where studentid in(
	select studentid from result where result&gt;=80 and studentid=(
    	select subjectid from subject where subjectname ='高等数学'
    )
)

mysql 函数

常用函数

-- 数学运算
select abs(-8) -- 绝对值
select ceiling(9.4) -- 向上取整
select floor(9.4) -- 向下取整
select rand() -- 0-1 随机数
select sign() -- 判断数的符号 0&gt;0 负 &gt;-1 正 &gt;1
-- 字符串
select char_length('产生的') -- 字符串长度 3
select concat('我','爱') -- 拼接
select insert('成都市',1,2,'是') -- （原值，开始位置，个数，替换的值）   是市
select lower('LOVE') -- love
select upper('love') -- LOVE

聚合函数（常用）

sun()
count()
ave() 平均
max()
min()

select now() 现在时间

md5() 加密

分组和过滤

group by

having

分组

ACID 原则：

原子性

一致性

持久性

隔离性（问题：脏读、不可重复的、幻读）

-- 事物自动提交关闭 set autocommit = 0 -- 开始事物 start transaction insert (update) insert (update) -- 提交 commit -- 回滚 rollback -- 事物自动提交开启 set autocommit = 1 -- 了解 savepoint -- 保存点 rollback to savepoint -- 回滚到保存点 release savepoint -- 撤销保存点

索引（找的快）

索引分类

主键索引（PRIMARY KEY）
唯一索引 (UNIQUE KEY)
常规索引 (KEY/INDEX)
全文索引 (FULLTEXT)

快速定位数据

myisam 才有

测试索引 explain

-- 创建表增加索引
alter table school.student add fulltext index `name`(`name`);
explain -- 分析 SQL 执行状况
explain select * from student; -- 常规索引
explain select * from student where match(name) against('刘');

-- 不用看
delimiter $$ -- 写函数前必写，标志
create function mock_data() -- 创建函数，了解
returns int -- 返回值
begin -- 开始
	declear num int default 1000000; -- 定义
	declear i int default 0; -- 定义
	while i<num do="" --="" 循环插入 =""insert=""into=""num(`numname`,`num`)=""value=""(concat(' 用户 ',i),concat('18',floor(rand()*((8999999999-100000000)+100000000)));=""set=""i="i+1;"end=""while;=""return=""i;=""end;=""select=""mock_data()=""num(`num`)=""concat('18',floor(rand()*((8999999999-100000000)+100000000))=""```=""```sql=""*=""from=""num=""where=""`name`=" 用户 9999";=""id_ 表名 _ 字段名 =""create=""index=""索引名 =""on=""表（字段）=""id_num_numname=""num(`numname`)=""创建索引两种方式 =""alter=""table=""school.student=""add=""`name`(`name`);=""###=""索引原则 =""-=""不是越多越好（）=""不要对经常变动的数据加索引 =""小数据量不需要索引 =""索引一般加在经常查询的字段上 =""索引的数据结构 =""hash 类型的索引 =""btree:innodb 默认的数据结构 =""参考：https:=""blog.csdn.net=""wufuhuai=""article=""details=""79631466（看！！！！）=""##=""权限管理 =""用户管理 =""> sql 命令
用户表：user
本质：对这张表增删改查
- 创建用户      create user 用户名 identified by '密码'
<span class="hljs-keyword">create</span> <span class="hljs-keyword">user</span> <span class="hljs-keyword">new</span> identified <span class="hljs-keyword">by</span> <span class="hljs-string">'123456'</span>
</code></pre>
<ul>
<li>
<p><strong>修改密码</strong></p>
<pre class="highlighter-hljs" highlighted="true"><code class="highlighter-hljs language-sql hljs"><span class="hljs-comment">-- 修改当前密码</span>
<span class="hljs-keyword">set</span> password <span class="hljs-operator">=</span> password(<span class="hljs-string">'1111'</span>)
<span class="hljs-comment">-- 修改指定密码</span>
<span class="hljs-keyword">set</span> password <span class="hljs-keyword">for</span> <span class="hljs-keyword">new</span> <span class="hljs-operator">=</span> password(<span class="hljs-string">'1111'</span>)
</code></pre>
</li>
<li>
<p><strong>重命名         rename user 原名 to 新名</strong></p>
<pre class="highlighter-hljs" highlighted="true"><code class="highlighter-hljs language-sql hljs">rename <span class="hljs-keyword">user</span> <span class="hljs-keyword">new</span> <span class="hljs-keyword">to</span> new1
</code></pre>
</li>
<li>
<p><strong>用户授权     all privileges 全部权限  ，库 表</strong></p>
<pre class="highlighter-hljs" highlighted="true"><code class="highlighter-hljs language-sql hljs"><span class="hljs-keyword">grant</span> <span class="hljs-keyword">all</span> privileges <span class="hljs-keyword">on</span> <span class="hljs-operator">*</span>.<span class="hljs-operator">*</span> <span class="hljs-keyword">to</span> <span class="hljs-keyword">new</span> <span class="hljs-comment">-- 除了给别人授权，其他权限都有</span>
</code></pre>
</li>
<li>
<p><strong>查询权限</strong></p>
<pre class="highlighter-hljs" highlighted="true"><code class="highlighter-hljs language-sql hljs"><span class="hljs-keyword">show</span> grants <span class="hljs-keyword">for</span> <span class="hljs-keyword">new</span>
<span class="hljs-keyword">show</span> grants <span class="hljs-keyword">for</span> root<span class="hljs-variable">@localhost</span> <span class="hljs-comment">-- 查看root权限</span>
</code></pre>
</li>
<li>
<p>root权限</p>
<pre class="highlighter-hljs" highlighted="true"><code class="highlighter-hljs language-sql hljs"><span class="hljs-keyword">grant</span> <span class="hljs-keyword">all</span> privileges <span class="hljs-keyword">on</span> <span class="hljs-operator">*</span>.<span class="hljs-operator">*</span> <span class="hljs-keyword">to</span> <span class="hljs-string">'root@localhost'</span> <span class="hljs-keyword">with</span> <span class="hljs-keyword">grant</span> option
</code></pre>
</li>
<li>
<p><strong>撤销权限</strong></p>
<pre class="highlighter-hljs" highlighted="true"><code class="highlighter-hljs language-sql hljs"><span class="hljs-keyword">revoke</span> <span class="hljs-keyword">all</span> privileges <span class="hljs-keyword">on</span> <span class="hljs-operator">*</span>.<span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> <span class="hljs-keyword">new</span>
</code></pre>
</li>
<li>
<p><strong>删除用户</strong></p>
<pre class="highlighter-hljs" highlighted="true"><code class="highlighter-hljs language-sql hljs"><span class="hljs-keyword">drop</span> <span class="hljs-keyword">user</span> <span class="hljs-keyword">new</span>
</code></pre>
</li>
</ul>
<h2 id="备份">备份<button class="cnblogs-toc-button" title="显示目录导航" aria-expanded="false"></button></h2>
<p>why:</p>
<ul>
<li>
<p>保证重要数据不丢失</p>
</li>
<li>
<p>数据转移</p>
</li>
</ul>
<p>数据库备份方式：</p>
<ul>
<li>
<p>拷贝物理文件（磁盘文件夹data文件）</p>
</li>
<li>
<p>可视化工具（右键[数据库、表]导出）</p>
</li>
<li>
<p>命令行 mysqldump（cmd）</p>
<pre class="highlighter-hljs" highlighted="true"><code class="language-bash highlighter-hljs hljs"><span class="hljs-comment"># 导出</span>
<span class="hljs-comment"># mysqldump -h主机 -u用户名 -p密码 数据库 表明 &amp;gt;物理磁盘位置/文件名</span>
mysqldump -hlocalhost -uroot -p123456 school student &amp;gt;D:/a.sql
mysqldump -hlocalhost -uroot -p123456 school student result &amp;gt;D:/b.sql
<span class="hljs-comment">#导入</span>
<span class="hljs-comment"># 登录</span>
mysql -uroot -p123456
use school
<span class="hljs-built_in">source</span> d:/a.sql
<span class="hljs-comment"># 未登录 mysql -u用户名 -p密码 库名&amp;lt; 备份文件</span>

</code></pre>
</li>
</ul>
<h2 id="数据库的归约三大范式">数据库的归约，三大范式<button class="cnblogs-toc-button" title="显示目录导航" aria-expanded="false"></button></h2>
<h3 id="规范数据库设计">规范数据库设计<button class="cnblogs-toc-button" title="显示目录导航" aria-expanded="false"></button></h3>
<ul>
<li>分析需求：分析业务和需求处理的数据库的需求</li>
<li>概要设计：设计关系图e-r图</li>
</ul>
<h3 id="设计数据库步骤">设计数据库步骤<button class="cnblogs-toc-button" title="显示目录导航" aria-expanded="false"></button></h3>
<ul>
<li>收集信息，分析需求</li>
<li>标识实体（把需求落地到每个字段）（xx表，xx表）</li>
<li>标识实体之间的关系（xx表-xx表）</li>
</ul>
<h3 id="三大范式">三大范式<button class="cnblogs-toc-button" title="显示目录导航" aria-expanded="false"></button></h3>
<ul>
<li>第一范式（1NF）
<ul>
<li>原子性：保证每一列不可再分</li>
</ul>
</li>
<li>第二范式（2NF）
<ul>
<li>前提：满足第一范式</li>
<li>每张表只描述一件事</li>
</ul>
</li>
<li>第三范式（3NF）
<ul>
<li>前提：满足第一、二范式</li>
<li>每一列与主键直接相关</li>
</ul>
</li>
</ul>
<p><strong>规范和性能问题：</strong></p>
<p>关联查询的表不得超过三张表</p>
<h2 id="jdbc重点">JDBC（重点）<button class="cnblogs-toc-button" title="显示目录导航" aria-expanded="false"></button></h2>
<h3 id="数据库驱动">数据库驱动<button class="cnblogs-toc-button" title="显示目录导航" aria-expanded="false"></button></h3>
<h3 id="jdbc">JDBC<button class="cnblogs-toc-button" title="显示目录导航" aria-expanded="false"></button></h3>
<p>简化开发人员（对数据库统一）操作，提供的（java操作数据库的）规范称JDBC</p>
<p>java.sql</p>
<p>javax.sql</p>
<p>导入一个数据库驱动包 mysql-connector-java-5.1.47.jar</p>
<h3 id="jdbc项目">JDBC项目<button class="cnblogs-toc-button" title="显示目录导航" aria-expanded="false"></button></h3>
<p>&gt; 创建测试数据库</p>
<pre class="highlighter-hljs" highlighted="true"><code class="highlighter-hljs language-sql hljs"><span class="hljs-keyword">create</span> database jdbcstudy <span class="hljs-type">character</span> <span class="hljs-keyword">set</span> utf8 <span class="hljs-keyword">collate</span> utf8_general_ci;

use jdbcstudy;

<span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> users(
	id <span class="hljs-type">int</span> <span class="hljs-keyword">primary</span> key,
  name <span class="hljs-type">varchar</span> (<span class="hljs-number">40</span>),
  password <span class="hljs-type">varchar</span>(<span class="hljs-number">40</span>),
  email <span class="hljs-type">varchar</span>(<span class="hljs-number">60</span>),
  birthday <span class="hljs-type">date</span>
);

<span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> users(id,name,password,email,birthday)
<span class="hljs-keyword">values</span>(<span class="hljs-number">1</span>,<span class="hljs-string">'zhangsan'</span>,<span class="hljs-string">'123456'</span>,<span class="hljs-string">'zs@sina.com'</span>,<span class="hljs-string">'1980-12-04'</span>),
(<span class="hljs-number">2</span>,<span class="hljs-string">'lisi'</span>,<span class="hljs-string">'123456'</span>,<span class="hljs-string">'ls@sina.com'</span>,<span class="hljs-string">'1981-12-04'</span>),
(<span class="hljs-number">3</span>,<span class="hljs-string">'wangwu'</span>,<span class="hljs-string">'123456'</span>,<span class="hljs-string">'ww@sina.com'</span>,<span class="hljs-string">'1982-12-04'</span>);
</code></pre>
<p>1、idea创建一个普通项目</p>
<p>2、导入数据库驱动</p>
<ul>
<li><img alt="image-20210517142651097" loading="lazy"></li>
<li>将mysql-connector-java-5.1.47.jar粘贴到lib目录下</li>
<li>右键lib-&gt;add as library</li>
<li><img alt="image-20210518161249675" loading="lazy"></li>
</ul>
<p>3、编写测试代码</p>
<ul>
<li>右键src-&gt;new-&gt;package-&gt;com.jdbc.lesson01</li>
<li>右键lesson01-&gt;new-&gt;java class-&gt;First</li>
</ul>
<pre class="highlighter-hljs" highlighted="true"><code class="language-java highlighter-hljs hljs"><span class="hljs-keyword">package</span> com.jdbc.lesson01;

<span class="hljs-keyword">import</span> java.sql.*;

<span class="hljs-keyword">public</span> <span class="hljs-keyword">class</span> <span class="hljs-title class_">FirstDemo</span> {
  <span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">main</span><span class="hljs-params">(String[] args)</span> <span class="hljs-keyword">throws</span> ClassNotFoundException, SQLException {
      <span class="hljs-comment">//1.加载驱动</span>
      Class.forName(<span class="hljs-string">"com.mysql.jdbc.Driver"</span>);<span class="hljs-comment">//固定写法</span>
      
      <span class="hljs-comment">//2.用户信息和url</span>
      <span class="hljs-type">String</span> <span class="hljs-variable">url</span> <span class="hljs-operator">=</span><span class="hljs-string">"jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&amp;amp;characterEncoding=utf8&amp;amp;useSSL=true"</span>;
      <span class="hljs-comment">//useUnicode支持中文编码   characterEncoding设定中文字符集为utf-8   useSSL使用安全</span>
      <span class="hljs-type">String</span> <span class="hljs-variable">username</span> <span class="hljs-operator">=</span> <span class="hljs-string">"root"</span>;
      <span class="hljs-type">String</span> <span class="hljs-variable">password</span> <span class="hljs-operator">=</span> <span class="hljs-string">"123456"</span>;
      
      <span class="hljs-comment">//3.连接数据库成功  Connection代表数据库</span>
      <span class="hljs-type">Connection</span> <span class="hljs-variable">connection</span> <span class="hljs-operator">=</span> DriverManager.getConnection(url,username,password);<span class="hljs-comment">//驱动管理获得链接</span>
      
      <span class="hljs-comment">//4.执行sql的对象  Statement执行sql对象</span>
      <span class="hljs-type">Statement</span> <span class="hljs-variable">statement</span> <span class="hljs-operator">=</span> connection.createStatement();
      
      <span class="hljs-comment">//5.执行sql的对象执行sql，查看返回结果</span>
      <span class="hljs-type">String</span> <span class="hljs-variable">sql</span> <span class="hljs-operator">=</span><span class="hljs-string">"select * from users"</span>;
      <span class="hljs-type">ResultSet</span> <span class="hljs-variable">resultSet</span> <span class="hljs-operator">=</span> statement.executeQuery(sql);<span class="hljs-comment">//返回的结果集</span>
      <span class="hljs-keyword">while</span> (resultSet.next()){
          System.out.println(<span class="hljs-string">"id="</span> + resultSet.getObject(<span class="hljs-string">"id"</span>));
          System.out.println(<span class="hljs-string">"name="</span> + resultSet.getObject(<span class="hljs-string">"name"</span>));
          System.out.println(<span class="hljs-string">"password="</span> + resultSet.getObject(<span class="hljs-string">"password"</span>));
          System.out.println(<span class="hljs-string">"email="</span> + resultSet.getObject(<span class="hljs-string">"email"</span>));
          System.out.println(<span class="hljs-string">"birthday="</span> + resultSet.getObject(<span class="hljs-string">"birthday"</span>));
      }
      
      <span class="hljs-comment">//6.释放连接</span>
      resultSet.close();
      statement.close();
      connection.close();
  }
}

</code></pre>
<p>&gt; DriverManager   <strong>Connection</strong></p>
<pre class="highlighter-hljs" highlighted="true"><code class="language-java highlighter-hljs hljs">Class.forName(<span class="hljs-string">"com.mysql.jdbc.Driver"</span>);<span class="hljs-comment">//固定写法,加载驱动</span>
<span class="hljs-type">Connection</span> <span class="hljs-variable">connection</span> <span class="hljs-operator">=</span> DriverManager.getConnection(url,username,password);
<span class="hljs-comment">//connection代表数据库（数据库设置自动提交，事物提交，事务回滚）</span>
connection.rollback();
connection.commit();
connection.setAutoCommit();
</code></pre>
<p>&gt; URL</p>
<pre class="highlighter-hljs" highlighted="true"><code class="language-java highlighter-hljs hljs"><span class="hljs-type">String</span> <span class="hljs-variable">url</span> <span class="hljs-operator">=</span><span class="hljs-string">"jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&amp;amp;characterEncoding=utf8&amp;amp;useSSL=true"</span>;
<span class="hljs-comment">// jdbc:mysql://主机地址:端口号/数据库名？参数1&amp;amp;参数2&amp;amp;参数3</span>
</code></pre>
<p>&gt; <strong>Statement</strong> 执行sql的对象       PrepareStatement执行sql的对象</p>
<pre class="highlighter-hljs" highlighted="true"><code class="language-java highlighter-hljs hljs"><span class="hljs-type">Statement</span> <span class="hljs-variable">statement</span> <span class="hljs-operator">=</span> connection.createStatement();

statement.executeQuery(); <span class="hljs-comment">// 查询操作返回resultset</span>
statement.execute(); <span class="hljs-comment">// 执行任何SQL</span>
statement.executeUpdate(); <span class="hljs-comment">// 更新、插入、删除都用这个，返回一个受影响的行数</span>
</code></pre>
<p>&gt; <strong>ResultSet</strong> 查询的结果集，封装了所有查询结果</p>
<p>获得指定的数据类型</p>
<pre class="highlighter-hljs" highlighted="true"><code class="language-java highlighter-hljs hljs"><span class="hljs-type">ResultSet</span> <span class="hljs-variable">resultSet</span> <span class="hljs-operator">=</span> statement.executeQuery(sql);

resultSet.getObject();
resultSet.getString();
resultSet.getInt();
</code></pre>
<p>遍历，指针</p>
<pre class="highlighter-hljs" highlighted="true"><code class="language-java highlighter-hljs hljs">resultSet.beforeFirst();<span class="hljs-comment">//移动到最前面</span>
resultSet.afterLast();<span class="hljs-comment">//移动到最后</span>
resultSet.next();
resultSet。absolute();<span class="hljs-comment">// 指定行</span>
</code></pre>
<p>&gt; 释放资源</p>
<pre class="highlighter-hljs" highlighted="true"><code class="language-java highlighter-hljs hljs">resultSet.close();
statement.close();
connection.close();
</code></pre>
<h3 id="statement对象主要核心会变的">statement对象（主要核心会变的）<button class="cnblogs-toc-button" title="显示目录导航" aria-expanded="false"></button></h3>
<p>向数据库发送sql语句，增删改查</p>
<p>两个方法</p>
<ul>
<li>executeQuery</li>
<li>executeUpdate</li>
</ul>
<p>&gt; <strong>create</strong>  添加数据</p>
<p>executeUpdate</p>
<pre class="highlighter-hljs" highlighted="true"><code class="language-java highlighter-hljs hljs"><span class="hljs-type">Statement</span> <span class="hljs-variable">statement</span> <span class="hljs-operator">=</span> connection.createStatement();
<span class="hljs-type">String</span> <span class="hljs-variable">sql</span> <span class="hljs-operator">=</span> <span class="hljs-string">"insert into user(...)values(...)"</span>;
<span class="hljs-type">int</span> <span class="hljs-variable">num</span> <span class="hljs-operator">=</span> statement.executeUpdate(sql);
<span class="hljs-keyword">if</span>(num&amp;gt;<span class="hljs-number">0</span>){
  System.out.pringln(<span class="hljs-string">"插入成功!!!"</span>);
}
</code></pre>
<p>&gt; <strong>delete</strong>  删除</p>
<p>executeUpdate</p>
<pre class="highlighter-hljs" highlighted="true"><code class="language-java highlighter-hljs hljs"><span class="hljs-type">Statement</span> <span class="hljs-variable">statement</span> <span class="hljs-operator">=</span> connection.createStatement();
<span class="hljs-type">String</span> <span class="hljs-variable">sql</span> <span class="hljs-operator">=</span> <span class="hljs-string">"delete from user where id=1"</span>;
<span class="hljs-type">int</span> <span class="hljs-variable">num</span> <span class="hljs-operator">=</span> statement.executeUpdate(sql);
<span class="hljs-keyword">if</span>(num&amp;gt;<span class="hljs-number">0</span>){
  System.out.pringln(<span class="hljs-string">"删除成功!!!"</span>);
}
</code></pre>
<p>&gt; <strong>update</strong>  修改</p>
<p>executeUpdate</p>
<pre class="highlighter-hljs" highlighted="true"><code class="language-java highlighter-hljs hljs"><span class="hljs-type">Statement</span> <span class="hljs-variable">statement</span> <span class="hljs-operator">=</span> connection.createStatement();
<span class="hljs-type">String</span> <span class="hljs-variable">sql</span> <span class="hljs-operator">=</span> <span class="hljs-string">"update user set name='' where name=''"</span>;
<span class="hljs-type">int</span> <span class="hljs-variable">num</span> <span class="hljs-operator">=</span> statement.executeUpdate(sql);
<span class="hljs-keyword">if</span>(num&amp;gt;<span class="hljs-number">0</span>){
  System.out.pringln(<span class="hljs-string">"修改成功!!!"</span>);
}
</code></pre>
<p>&gt; <strong>read</strong>  查</p>
<p>executeQuery</p>
<pre class="highlighter-hljs" highlighted="true"><code class="language-java highlighter-hljs hljs"><span class="hljs-type">Statement</span> <span class="hljs-variable">statement</span> <span class="hljs-operator">=</span> connection.createStatement();
<span class="hljs-type">String</span> <span class="hljs-variable">sql</span> <span class="hljs-operator">=</span> <span class="hljs-string">"select * from user where id=1"</span>;
<span class="hljs-type">ResultSet</span> <span class="hljs-variable">rs</span> <span class="hljs-operator">=</span> statement.executeQuery(sql);
<span class="hljs-keyword">while</span>(rs.next()){
  <span class="hljs-comment">//根据获取的数据类型，分别调用rs的相应方法映射到java对象中</span>
}
</code></pre>
<p>&gt; 代码实现</p>
<p>1、提取工具类（许多代码不变的）</p>
<pre class="highlighter-hljs" highlighted="true"><code class="language-java highlighter-hljs hljs"><span class="hljs-keyword">package</span> com.jdbc.lesson02.utils;

<span class="hljs-keyword">import</span> java.io.IOException;
<span class="hljs-keyword">import</span> java.io.InputStream;
<span class="hljs-keyword">import</span> java.sql.*;
<span class="hljs-keyword">import</span> java.util.Properties;

<span class="hljs-keyword">public</span> <span class="hljs-keyword">class</span> <span class="hljs-title class_">JdbcUtils</span> {

  <span class="hljs-keyword">private</span> <span class="hljs-keyword">static</span> String driver;
  <span class="hljs-keyword">private</span> <span class="hljs-keyword">static</span> String url;
  <span class="hljs-keyword">private</span> <span class="hljs-keyword">static</span> String username;
  <span class="hljs-keyword">private</span> <span class="hljs-keyword">static</span> String password;

  <span class="hljs-keyword">static</span> {
      <span class="hljs-keyword">try</span>{
          <span class="hljs-type">InputStream</span> <span class="hljs-variable">in</span> <span class="hljs-operator">=</span> JdbcUtils.class.getClassLoader().getResourceAsStream(<span class="hljs-string">"db.properties"</span>);
          <span class="hljs-type">Properties</span> <span class="hljs-variable">properties</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">Properties</span>();
          properties.load(in);

          driver = properties.getProperty(<span class="hljs-string">"driver"</span>);
          url = properties.getProperty(<span class="hljs-string">"url"</span>);
          username = properties.getProperty(<span class="hljs-string">"username"</span>);
          password = properties.getProperty(<span class="hljs-string">"password"</span>);

          <span class="hljs-comment">//1.驱动只用加载一次</span>
          Class.forName(driver);
      } <span class="hljs-keyword">catch</span> (IOException e) {
          e.printStackTrace();
      } <span class="hljs-keyword">catch</span> (Exception e) {
          e.printStackTrace();
      }
  }

  <span class="hljs-comment">//获取连接</span>
  <span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> Connection <span class="hljs-title function_">getConnection</span><span class="hljs-params">()</span> <span class="hljs-keyword">throws</span> SQLException {
      <span class="hljs-keyword">return</span> DriverManager.getConnection(url, username, password);
  }

  <span class="hljs-comment">//释放资源</span>
  <span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">release</span><span class="hljs-params">(Connection co, Statement st, ResultSet rs)</span>{
      <span class="hljs-keyword">if</span>(rs!=<span class="hljs-literal">null</span>){
          <span class="hljs-keyword">try</span> {
              rs.close();
          } <span class="hljs-keyword">catch</span> (SQLException e) {
              e.printStackTrace();
          }
      }
      <span class="hljs-keyword">if</span>(st!=<span class="hljs-literal">null</span>){
          <span class="hljs-keyword">try</span> {
              st.close();
          } <span class="hljs-keyword">catch</span> (SQLException e) {
              e.printStackTrace();
          }
      }
      <span class="hljs-keyword">if</span>(co!=<span class="hljs-literal">null</span>){
          <span class="hljs-keyword">try</span> {
              co.close();
          } <span class="hljs-keyword">catch</span> (SQLException e) {
              e.printStackTrace();
          }
      }
  }

}

</code></pre>
<p>2、编写增删改executeUpdate</p>
<pre class="highlighter-hljs" highlighted="true"><code class="language-java highlighter-hljs hljs"><span class="hljs-comment">// 增加</span>
<span class="hljs-keyword">package</span> com.jdbc.lesson02;

<span class="hljs-keyword">import</span> com.jdbc.lesson02.utils.JdbcUtils;

<span class="hljs-keyword">import</span> java.sql.Connection;
<span class="hljs-keyword">import</span> java.sql.ResultSet;
<span class="hljs-keyword">import</span> java.sql.SQLException;
<span class="hljs-keyword">import</span> java.sql.Statement;

<span class="hljs-keyword">public</span> <span class="hljs-keyword">class</span> <span class="hljs-title class_">TestInsert</span> {
  <span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">main</span><span class="hljs-params">(String[] args)</span> {
      <span class="hljs-type">Connection</span> <span class="hljs-variable">co</span> <span class="hljs-operator">=</span><span class="hljs-literal">null</span>;
      <span class="hljs-type">Statement</span> <span class="hljs-variable">st</span> <span class="hljs-operator">=</span><span class="hljs-literal">null</span>;
      <span class="hljs-type">ResultSet</span> <span class="hljs-variable">rs</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
      <span class="hljs-keyword">try</span> {
          co = JdbcUtils.getConnection();<span class="hljs-comment">//获取连接</span>
          st = co.createStatement();<span class="hljs-comment">//获得sql执行对象</span>
          <span class="hljs-type">String</span> <span class="hljs-variable">sql</span> <span class="hljs-operator">=</span> <span class="hljs-string">"insert into users(id,name,password,email,birthday)"</span> +
                  <span class="hljs-string">"values(5,'zasan','123456','z@sina.com','1980-10-04')"</span>;
          <span class="hljs-type">int</span> <span class="hljs-variable">i</span> <span class="hljs-operator">=</span> st.executeUpdate(sql);
          <span class="hljs-keyword">if</span>(i&amp;gt;<span class="hljs-number">0</span>){
              System.out.println(<span class="hljs-string">"插入成功！"</span>);
          }
      } <span class="hljs-keyword">catch</span> (SQLException e) {
          e.printStackTrace();
      }<span class="hljs-keyword">finally</span>{
          JdbcUtils.release(co,st,rs);
      }

  }
}

</code></pre>
<p>3、查询</p>
<pre class="highlighter-hljs" highlighted="true"><code class="language-java highlighter-hljs hljs"><span class="hljs-keyword">package</span> com.jdbc.lesson02;

<span class="hljs-keyword">import</span> com.jdbc.lesson02.utils.JdbcUtils;

<span class="hljs-keyword">import</span> java.sql.Connection;
<span class="hljs-keyword">import</span> java.sql.ResultSet;
<span class="hljs-keyword">import</span> java.sql.SQLException;
<span class="hljs-keyword">import</span> java.sql.Statement;

<span class="hljs-keyword">public</span> <span class="hljs-keyword">class</span> <span class="hljs-title class_">TestSelect</span> {
  <span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">main</span><span class="hljs-params">(String[] args)</span> {

      <span class="hljs-type">Connection</span> <span class="hljs-variable">co</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
      <span class="hljs-type">Statement</span> <span class="hljs-variable">st</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
      <span class="hljs-type">ResultSet</span> <span class="hljs-variable">rs</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;

      <span class="hljs-keyword">try</span> {
          co = JdbcUtils.getConnection();
          st = co.createStatement();

          <span class="hljs-comment">//SQL</span>
          <span class="hljs-type">String</span> <span class="hljs-variable">sql</span> <span class="hljs-operator">=</span> <span class="hljs-string">"select * from users where id=1"</span>;

          rs = st.executeQuery(sql);

          <span class="hljs-keyword">while</span> (rs.next()){
              System.out.println(rs.getString(<span class="hljs-string">"name"</span>));
          }
          
      } <span class="hljs-keyword">catch</span> (SQLException e) {
          e.printStackTrace();
      }<span class="hljs-keyword">finally</span>{
          JdbcUtils.release(co,st,rs);
      }

  }
}

</code></pre>
<p>&gt; SQL注入问题</p>
<p>sql存在漏洞，会被攻击，导致数据泄露</p>
<pre class="highlighter-hljs" highlighted="true"><code class="language-java highlighter-hljs hljs"><span class="hljs-keyword">package</span> com.jdbc.lesson02;

<span class="hljs-keyword">import</span> com.jdbc.lesson02.utils.JdbcUtils;

<span class="hljs-keyword">import</span> java.sql.Connection;
<span class="hljs-keyword">import</span> java.sql.ResultSet;
<span class="hljs-keyword">import</span> java.sql.SQLException;
<span class="hljs-keyword">import</span> java.sql.Statement;

<span class="hljs-keyword">public</span> <span class="hljs-keyword">class</span> <span class="hljs-title class_">SqlZhuru</span> {
  <span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">main</span><span class="hljs-params">(String[] args)</span> {
      <span class="hljs-comment">//login ("aaa","123456");</span>
      login (<span class="hljs-string">"'od '1=1"</span>,<span class="hljs-string">"'or'1=1"</span>);
  }

  <span class="hljs-comment">//登录业务</span>
  <span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">login</span><span class="hljs-params">(String username,String password)</span> {
      <span class="hljs-type">Connection</span> <span class="hljs-variable">co</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
      <span class="hljs-type">Statement</span> <span class="hljs-variable">st</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
      <span class="hljs-type">ResultSet</span> <span class="hljs-variable">rs</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;

      <span class="hljs-keyword">try</span> {
          co = JdbcUtils.getConnection();
          st = co.createStatement();

          <span class="hljs-comment">//SQL</span>
          <span class="hljs-comment">//select * from users where name=''or '1=1' and password =''or'1=1'</span>
          <span class="hljs-type">String</span> <span class="hljs-variable">sql</span> <span class="hljs-operator">=</span> <span class="hljs-string">"select * from users where name='"</span>+username+<span class="hljs-string">"' and password ='"</span>+password+<span class="hljs-string">"'"</span>;

          rs = st.executeQuery(sql);

          <span class="hljs-keyword">while</span> (rs.next()){
              System.out.println(rs.getString(<span class="hljs-string">"name"</span>));
              System.out.println(rs.getString(<span class="hljs-string">"password"</span>));
          }

      } <span class="hljs-keyword">catch</span> (SQLException e) {
          e.printStackTrace();
      }<span class="hljs-keyword">finally</span>{
          JdbcUtils.release(co,st,rs);
      }

  }
}

</code></pre>
<p><img src="http://masterfile.aigcbbs.cn/Fh224WNfpMm8gkZ8ceCMYAqBKgvi" alt="" loading="lazy" class="medium-zoom-image"></p>
<h3 id="preparestatement对象">PrepareStatement对象<button class="cnblogs-toc-button" title="显示目录导航" aria-expanded="false"></button></h3>
<p>防止sql注入，效率更高</p>
<ol>
<li>
<p>新增</p>
</li>
<li>
<p>删除</p>
</li>
<li>
<p>更新</p>
<pre class="highlighter-hljs" highlighted="true"><code class="language-java highlighter-hljs hljs"><span class="hljs-keyword">package</span> com.jdbc.lesson03;

<span class="hljs-keyword">import</span> com.jdbc.lesson02.utils.JdbcUtils;

<span class="hljs-keyword">import</span> java.sql.Connection;
<span class="hljs-keyword">import</span> java.sql.PreparedStatement;
<span class="hljs-keyword">import</span> java.sql.ResultSet;
<span class="hljs-keyword">import</span> java.sql.SQLException;
<span class="hljs-keyword">import</span> java.util.Date;

<span class="hljs-keyword">public</span> <span class="hljs-keyword">class</span> <span class="hljs-title class_">TestUpdate</span> {
  <span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">main</span><span class="hljs-params">(String[] args)</span> {
      <span class="hljs-type">Connection</span> <span class="hljs-variable">co</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
      <span class="hljs-type">PreparedStatement</span> <span class="hljs-variable">st</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
      <span class="hljs-type">ResultSet</span> <span class="hljs-variable">rs</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;

      <span class="hljs-keyword">try</span> {
          co = JdbcUtils.getConnection();

          <span class="hljs-comment">//区别</span>
          <span class="hljs-comment">//使用？占位符代替参数</span>
          <span class="hljs-type">String</span> <span class="hljs-variable">sql</span> <span class="hljs-operator">=</span><span class="hljs-string">"update users set name=? where id=?"</span>;

          st = co.prepareStatement(sql);<span class="hljs-comment">//预编译sql，先写sql不执行</span>

          <span class="hljs-comment">//手动赋值</span>
          st.setString(<span class="hljs-number">1</span>,<span class="hljs-string">"奥是"</span>);
          st.setInt(<span class="hljs-number">2</span>,<span class="hljs-number">1</span>);

          <span class="hljs-comment">//执行</span>
          <span class="hljs-type">int</span> <span class="hljs-variable">i</span> <span class="hljs-operator">=</span> st.executeUpdate();
          <span class="hljs-keyword">if</span>(i&amp;gt;<span class="hljs-number">0</span>){
              System.out.println(<span class="hljs-string">"更新成功！"</span>);
          }


      } <span class="hljs-keyword">catch</span> (SQLException e) {
          e.printStackTrace();
      }<span class="hljs-keyword">finally</span> {
          JdbcUtils.release(co,st,rs);
      }


  }
}

</code></pre>
</li>
<li>
<p>查询</p>
<pre class="highlighter-hljs" highlighted="true"><code class="language-java highlighter-hljs hljs"><span class="hljs-keyword">package</span> com.jdbc.lesson03;

<span class="hljs-keyword">import</span> com.jdbc.lesson02.utils.JdbcUtils;

<span class="hljs-keyword">import</span> java.sql.*;

<span class="hljs-keyword">public</span> <span class="hljs-keyword">class</span> <span class="hljs-title class_">TestSelect</span> {
  <span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">main</span><span class="hljs-params">(String[] args)</span> {

      <span class="hljs-type">Connection</span> <span class="hljs-variable">co</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
      <span class="hljs-type">PreparedStatement</span> <span class="hljs-variable">st</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
      <span class="hljs-type">ResultSet</span> <span class="hljs-variable">rs</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;

      <span class="hljs-keyword">try</span> {
          co = JdbcUtils.getConnection();

          <span class="hljs-type">String</span> <span class="hljs-variable">sql</span> <span class="hljs-operator">=</span> <span class="hljs-string">"select * from users where id = ?"</span>;

          st = co.prepareStatement(sql);<span class="hljs-comment">//预编译</span>

          st.setInt(<span class="hljs-number">1</span>,<span class="hljs-number">1</span>);<span class="hljs-comment">//传递参数</span>

          rs = st.executeQuery();<span class="hljs-comment">//执行</span>

          <span class="hljs-keyword">if</span>(rs.next()){
              System.out.println(rs.getString(<span class="hljs-string">"name"</span>));
          }

      } <span class="hljs-keyword">catch</span> (SQLException e) {
          e.printStackTrace();
      }<span class="hljs-keyword">finally</span> {
          JdbcUtils.release(co,st,rs);
      }
  }
}

</code></pre>
</li>
<li>
<p>防止sql注入</p>
<pre class="highlighter-hljs" highlighted="true"><code class="language-java highlighter-hljs hljs"><span class="hljs-keyword">package</span> com.jdbc.lesson03;

<span class="hljs-keyword">import</span> com.jdbc.lesson02.utils.JdbcUtils;

<span class="hljs-keyword">import</span> java.sql.*;

<span class="hljs-keyword">public</span> <span class="hljs-keyword">class</span> <span class="hljs-title class_">SqlZhuru</span> {
  <span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">main</span><span class="hljs-params">(String[] args)</span> {
      <span class="hljs-comment">//login ("lisi","123456");</span>
      login (<span class="hljs-string">"''or 1=1"</span>,<span class="hljs-string">"''or 1=1"</span>);
  }

  <span class="hljs-comment">//登录业务</span>
  <span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">login</span><span class="hljs-params">(String username,String password)</span> {
      <span class="hljs-type">Connection</span> <span class="hljs-variable">co</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
      <span class="hljs-type">PreparedStatement</span> <span class="hljs-variable">st</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
      <span class="hljs-type">ResultSet</span> <span class="hljs-variable">rs</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;

      <span class="hljs-keyword">try</span> {
          co = JdbcUtils.getConnection();
          
          <span class="hljs-comment">//PreparedStatement把传递进来的参数当作字符，若存在转义字符则忽略，'会被转义</span>
          <span class="hljs-type">String</span> <span class="hljs-variable">sql</span> <span class="hljs-operator">=</span> <span class="hljs-string">"select * from users where name=? and password=?"</span>;

          st = co.prepareStatement(sql);
          st.setString(<span class="hljs-number">1</span>,username);
          st.setString(<span class="hljs-number">2</span>,password);

          rs = st.executeQuery();

          <span class="hljs-keyword">while</span> (rs.next()){
              System.out.println(rs.getString(<span class="hljs-string">"name"</span>));
              System.out.println(rs.getString(<span class="hljs-string">"password"</span>));
          }

      } <span class="hljs-keyword">catch</span> (SQLException e) {
          e.printStackTrace();
      }<span class="hljs-keyword">finally</span>{
          JdbcUtils.release(co,st,rs);
      }

  }
}

</code></pre>
</li>
</ol>
<h3 id="使用idea连接数据库">使用idea连接数据库<button class="cnblogs-toc-button" title="显示目录导航" aria-expanded="false"></button></h3>
<p><img src="http://masterfile.aigcbbs.cn/FtqDU1fLv6CnsSA4Ux7CCDUbnGtc" alt="" loading="lazy" class="medium-zoom-image"></p>
<h3 id="事务">事务<button class="cnblogs-toc-button" title="显示目录导航" aria-expanded="false"></button></h3>
<p>都成功或都失败</p>
<p>ACID原则</p>
<pre class="highlighter-hljs" highlighted="true"><code class="language-java highlighter-hljs hljs"><span class="hljs-keyword">package</span> com.jdbc.lesson04;

<span class="hljs-keyword">import</span> com.jdbc.lesson02.utils.JdbcUtils;

<span class="hljs-keyword">import</span> java.sql.Connection;
<span class="hljs-keyword">import</span> java.sql.PreparedStatement;
<span class="hljs-keyword">import</span> java.sql.ResultSet;
<span class="hljs-keyword">import</span> java.sql.SQLException;

<span class="hljs-keyword">public</span> <span class="hljs-keyword">class</span> <span class="hljs-title class_">TestTransation</span> {
  <span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">main</span><span class="hljs-params">(String[] args)</span> {
      <span class="hljs-type">Connection</span> <span class="hljs-variable">co</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
      <span class="hljs-type">PreparedStatement</span> <span class="hljs-variable">st</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
      <span class="hljs-type">ResultSet</span> <span class="hljs-variable">rs</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>;
      <span class="hljs-keyword">try</span> {
          co = JdbcUtils.getConnection();
          <span class="hljs-comment">//关闭自动提交功能,开启事务</span>
          co.setAutoCommit(<span class="hljs-literal">false</span>);

          <span class="hljs-type">String</span> <span class="hljs-variable">sql1</span> <span class="hljs-operator">=</span> <span class="hljs-string">"update account set money = money-100 where name = 'q'"</span>;
          st = co.prepareStatement(sql1);
          st.executeUpdate();

          <span class="hljs-type">String</span> <span class="hljs-variable">sql2</span> <span class="hljs-operator">=</span> <span class="hljs-string">"update account set money = money+100 where name = 'qq'"</span>;
          st = co.prepareStatement(sql2);
          st.executeUpdate();

          <span class="hljs-comment">//业务完毕</span>
          co.commit();
          System.out.println(<span class="hljs-string">"yes"</span>);

      } <span class="hljs-keyword">catch</span> (SQLException e) {
          <span class="hljs-keyword">try</span> {
              co.rollback();<span class="hljs-comment">//失败回滚</span>
          } <span class="hljs-keyword">catch</span> (SQLException e1) {
              e1.printStackTrace();
          }
          e.printStackTrace();
      }<span class="hljs-keyword">finally</span> {
          JdbcUtils.release(co,st,rs);
      }

  }
}

</code></pre>
<h3 id="数据库连接池">数据库连接池<button class="cnblogs-toc-button" title="显示目录导航" aria-expanded="false"></button></h3>
<p>连接-释放   十分浪费系统资源</p>
<p><strong>池化技术：预先准备一些资源</strong></p>
<p>常用连接数=最小连接数</p>
<p>最大连接数（业务最高承载上限）</p>
<p>等待超时</p>
<p>编写连接池：实现一个接口<strong>DataSource</strong></p>
<p>&gt; 开源数据源实现</p>
<p>DBCP</p>
<p>C3P0</p>
<p>Druid:阿里巴巴</p>
<p>使用这些数据连接池后就不用写连接数据库代码了</p>
<p>&gt; DBCP</p>
<p>需要用到的JAR包</p>
<p>commons-dbcp-1.4.jar       commons-pool-1.6.jar</p>
<p>&gt; C3P0</p>
<p>需要用到的JAR包</p>
<p>c3p0-0.9.5.5.jar       mchange-commons-java-0.2.19.jar</p>
<p>DataSource接口不变，方法就不变</p>
<p>--以上是业务级别MySQL</p>

Mysql基础

MySQL

> 数据库> 操作系统，数据结构与算法> 离散数学，数字电路，体系结构，编译原理

数据库

数据库分类

MySQL 简介

数据库语言

操作数据库

操作数据库（了解）

数据库列类型

数据库的字段属性（重点）

创建数据库表

数据表类型

修改删除表

MySQL 数据管理

外键（了解）

DML 语言（全部记住）

添加

修改

删除

DQL 查询数据 (最重点)

where 条件子句

逻辑运算符

模糊查询：比较运算符

联表查询

自连接

分页 limit 和排序 order by

排序：升序降序

分页

子查询

mysql 函数

常用函数

聚合函数（常用）

分组和过滤

分组

索引（找的快）

索引分类

测试索引 explain

相关帖子

随便看看

Mysql基础

> 数据库
> 操作系统，数据结构与算法
> 离散数学，数字电路，体系结构，编译原理