mysql从入门到精通

1、MYSQL概念

mysql是关系型数据库

非关系数据库有 redis、mogodb等

2、操作数据库

2.1、数据类型

数值 (Ctrl+Shift+] 即可打出小黑点)

• tinyint 十分小的数据 1个字节
• smallint 较小数据 2个字节
• mediumint 正等数据大小 3个字节
• int 标注字节 4个字节
• bigint 较大数据 8个字节
• float 浮点数 4个字节
• double 浮点数 8个字节
• decimal 字符串形式的浮点数 金融使用

字符串

• char 固定大小 0~255
• varar 可变字符串 0~65532 常用String
• tinytext 微型文本 2^8-1
• text 文本串 2^16-1 保存大文本

时间日期

• date YYYY-MM-DD 日期
• time HH: mm :ss 时间格式
• datetime YYYY-MM-DD HH:mm:ss 常用时间格式
• timestamp 时间戳 ，1970.1.1到现在的毫秒数 较为常用
• year 年份

null

• 没有值 ，未知 注：不要使用null进行运算，结果为null

2.2、字段属性(*)

Unsigned

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

zerofill

• 0填充的
• 不足的位数，使用0来填充，int（3），5—005

自增

• 通常理解为自增，自动在上一条记录的基础上+1
• 通常用来设计唯一的主键 index，必须是整数类型
• 可以自定义设计自增的起始值和步长

非空 NULL not null

• 假设设置为 not null ，如果不赋值会报错
• NULL 如果不填写默认为null

默认

• 设置默认的值

/*每一个表，都必须存在以下五个字段！
id  主键
version  乐观锁
is_delete  伪删除
gmt_create 创建时间
gmt_update 修改时间
*/

2.3、创建数据库表

格式

CREATE TABLE[IF NOT EXISTS] `表名`(
'字段名' 列类型 [属性] [索引] [注释]，
'字段名' 列类型 [属性] [索引] [注释]，
 ........
'字段名' 列类型 [属性] [索引] [注释]
)[表类型] [字符集设置] [注释]

--注意点，使用英文()，表的名称和字段尽量使用``括起来
--AUTO_INCREMENT自增
--字符串使用单引号括起来！
--所有的语句后面加，（英文的），最后一个不用加
--PRIMARY KEY主键，一般一个表只有一个唯一的主键！

CREATE TABLE IF NOT EXISTS student
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号'，
`name` VARCHAR(3O)  NOT NULL DEFAULT '匿名' COMMENT '姓名'，
`pWd` VARCHAR(2O) NOT NUEL DEFAULT '123456' COMMENT '密码'，
`SeX` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别'，
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期'，
`address` VARCHAR(1OO) DEFAULT NULL COMMENT '家庭住址'，
`emai1` VARCHAR(50) DEFAULT NULL COMMENT '邮箱'，
PRIMARY KEY(`id`)
ENGINE=INNODB DEFAULT CHARSET=utf8

SHOW CREATE DATABASE school--查看创建数据库的吾句
SHOW CREATE TABLE student--查看student数据表的定义语句

2.4、 数据表类型

数据库的存储引擎

• INNODB 默认使用
• MYISAM 早些年使用

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

常规使用操作

• MYISAM 节约空间、速度较快
• INNODB 安全性高、事物的处理、多表多用户操作

在物理空间存在的位置

• 所有数据库文件都存储在data目录下，一个文件夹对应一个数据库

MYISAM和INNODB存储引擎在物理文件上的区别

• INNODB在数据库表中只有一个*.frm文件，以及上一级目录下的ibdata1文件
• MYISAM对应文件
  • *.frm 表结构的定义文件
  • *.MYD 数据文件（data）
  • *.MYI 索引文件（index）

设置数据库的字符集编码

CHARSEF = utf8

• 不设置的话，会是ysql默认的字符集编码~（不支持中文！）

• 小ySQL的默认编码是Latin1,不支持中文

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

  character-set-server=utf8
  

2.5、修改删除表

修改表

 --修改表名 ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1
--增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher1 ADD age INT(11)
--修改表的字段 (重命名，修改约束！)
ALTER TABLE teacher1 MODIFY age VARCHAR(11)
--修改约束
ALTER TABLE teacher1 CHANGE ageage1INT(1)--字段重名名
删除表的字段
ALTER TABLE teacher1 DROP agel

删除

--删除表（如果表存在再删除）
DROP TABLE IF EXISTS teacher1

3、MySQL 数据管理

3.1、外键

• 删除 有外键关系的表时，必须要先删除引用别人的表（从表），再删除被引用的表（主表)

ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 那个表（哪个字段）

• 以上都是物理外键，数据库级别的外键不建议使用

3.2、DML语言

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

• insert 添加
• update 修改
• delete 删除
• select 查询

3.3、添加

--插入单条数据
insert into 表名(字段1，字段2，字段3)  values('值1','值2','值3')
--插入多条数据
insert into 表名(字段1，字段2，字段3)  values('值1','值2','值3'),('值1','值2','值3')......

3.4、修改

update  表名 set 属性1=`修改内容`,属性1=`修改内容`.....   where [条件]
--修改内容可以是一个变量  例如  birthday = CURRENT_TIME  当前日期

条件：where ，操作符返回布尔值

操作符	含义
= 、> 、<、>=、<=	基本比较符
<> 或 !=	不等于
between 起始范围 amd 终止范围	在某个区间内
amd	与
or	或

3.5、删除

delete from 表名 where [条件]

truncate 命令 ：完全清空一个数据库表，表的结构和约束不变

--清空student表
TRUNCATE 表名

delete 和 TRUNCATE 区别

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

4、DQL查询数控（*）

4.1、 DQL

DQL：数据查询语言

语法

查询全部

select * from 表名
select 属性1,属性2.... from 表名

别名 as 或者空格

select 属性1 as  别名   from  表名
select *   from  表名 as 别名

函数 concat(a,b) 拼接属性值内容

select Concat('姓名：',属性) as  别名  from  表名

去重 distinct 作用：去除select查询结果中重复的数据

select distinct 字段  from 表名

数据库列（表达式）

SELECT VERSION()--查询系统版本
(函数)
SELECT 100*3-1 AS 计算结果--用来计算
(表达式)
SELECT @@auto_increment_increment--查询自增的步长
(变量)
--学员考试成绩+1分查看
SELECT StudentNo,StudentResult+1 As '提分后' FROM resu1t

4.2、where条件

作用：检索数据中符合条件的值

逻辑运算符

运算符	语法	描述
and	a and b	逻辑与
or	a or b	逻辑或
Not	not true	逻辑非

模糊查询 : 比较运算符

运算符	语法	描述
is null	字段 is null	操作符为null，结果为真
is not null	字段 is not null	操作符不为null，结果为真
between	字段 between 起始范围 and 结束范围	若字段的值在起始和结束范围之间，结果为真
Like	a like b	sql匹配，如果a匹配b，则结果为真
in	a in （a1,a2,a3…）	假设a在a1或者a2其中一个值中，结果为真

--模糊查询  %value 、 value% 、 %value%  、  _value 、value_  、 _value_
select * from 表名 where  字段 like '%valuies%'

--in  包含
select * from 表名 where 字段 in （value1,value2....）

4.3、联表查询

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

--并集查询
select 字段1，字段2，字段3...... from  表1 别名 
inner join 表2 别名 on  表1.关联属性 = 表2.关联属性
where  [条件]
--左联接查询
select 字段1，字段2，字段3...... from  表1 别名 
left join 表2 别名 on  表1.关联属性 = 表2.关联属性
where  [条件]
--右联接查询
select 字段1，字段2，字段3...... from  表1 别名 
right join 表2 别名 on  表1.关联属性 = 表2.关联属性
where  [条件]
--混合查询
select 字段1，字段2，字段3...... from  表1 别名 
right join 表2 别名 on  表1.关联属性 = 表2.关联属性
left join 表3 别名 on 表2.关联属性 = 表3.关联属性
where  [条件]

--使用where实现多表查询
select 字段1，字段2，字段3...... from  表1 别名,表2 别名
where  表1.关联属性 = 表2.关联属性

--我要查询哪些数据 se1ect...
--从那几个表中查 FROM 表 XXX Join 连接的表 on 交叉条件
--假设存在一种多张表查询，慢慢来，先查询两张表然后再慢慢增加
--From a left join b
--From a right join b

自连接

自己的表和自己的表连接，核心： 一张表拆分为两张一样的表即可

4.4 分页和排序

排序 order by

排序规则：

• 升序 asc
• 降序 desc

order by  排序字段  排序规则

分页 limit 扩展：瀑布类

limit  起始行数据下标，页面大小

当前页：current
数据起始下标：(current-1)*size
页面数据大小：size

4.5、子查询

一-在改造（由里及外）
SELECT StudentNo,studentName FROM student WHERE StudentNo IN
SELECT StudentNo FROM result WHERE StudentResult>80 AND SubjectNo =
SELECT SubjectNo FROM subject`WHERE`SubjectName`='高等数学-2'
)

4.6、分组和过滤

5、mysql函数

5.1、数学函数

求绝对值：

select abs(字段) from 表名

返回小于参数的最大整数：

select floor(字段) from 表名

返回大于参数的最小整数::

select ceil(字段) from 表名

5.2、字符串函数

insert(s1,index,len,s2) ,s1中index位置(index从1开始)开始，长度为len的字符串替换成s2 ：

select insert(字段，开始位置，替换长度，替换内容) form 表名 where  条件

注：insert仅是对查询出来的显示数据进行修改，并不会修改原数据

upper(字段)：将查询出来的字母全部转换为大写：

select upper(字段) from 表名 where 条件

lower(字段): 将查询出来的字母全部转换为小写：

select lower(字段) from 表名 where 条件

left(s,len) 返回s字符串的前len个字符 ：

select left(字段，长度) from 表名 where 条件

right(s,len)返回s字符串的后len个字符：

select right(字段，长度) from 表名 where 条件

substring(s,index,len):截取s字符串从index位置开始长度为len的字符：

select substring(字段，开始位置，长度) from 表名 where 条件

reverse(字段)：反序输出 :

select reverse(字段) from 表名 where 条件

5.3、日期函数

curdate() 获取当前日期 ：

select curdate()

surtime() 获取当前时间 ：

select curtime()

now() 获取当前日期+时间 ：

select now()

datediff(D1,D2) 计算d1和d2之间相隔的天数 ：

select datediff('2023-3-12','2024-1-1')

adddate(d,n) 在返回d日期开始加上n天的日期

select adddate('2023-8-1',66)

subdate(d,n) 在返回d日期开始减去n天的日期

select subdate('2023-01-02',44)

5.4、聚合函数

count()根据某个字段统计总记录数:

select count(字段) from 表名

sum()计算某个字段值的总和 ：

select sum(字段) from 表名

avg()求某个字段的平均值 :

select avg(字段) from 表名

max() 求某个字段的最大值 ：

select max(字段) from 表名

min()求某个字段的最小值：

select min(字段) from 表名

5.5 MD5加密

MD5(字段)
update 表名 set 字段 = md5(字段)

6、innodb存储引擎事务

将多条sql作为一个整体，要么全部执行，要么一条都不执行;

6.1、事务的特性

ACID原则

• ​ A 原子性：多条sql是一个整体，不可再分割
• ​ C 一致性：sql语句执行，数据库的值保持一致
• ​ I 隔离性：一个事务的执行不能被其他事务所干扰
• ​ D 持久性：一个事务一旦提交，数据的改变是永久性的

如何保证ACID原则

• A原子性：由undo log日志保证，它记录了需要回滚的日志信息，事务回滚是撤销已经执行成功的sql
• C一致性：由其他三大特新保证、程序代码要保证业务员上的一致性
• I隔离性：由MVCC（多版本并发控制）来保证
• D持久性：由内存 + redo log来保证， mysql修改数据同时在内存和redo log记录这次操作，宕机的时候可以从redo log恢复

事务的操作：

1、开启事务 ：

satart transaction

2、回滚：

rollback

3、提交：

commit (mysql默认自动提交)

查看是否开启了自动提交 ：

 show variables like 'autocommit'

关闭自动提交：

set autocommit = 0
set autocommit = 1(默认 为开启)

事务使用例子：

  satart transaction
 	sql1
	sql2
   commit 

6.2、事务隔离级别

• ru 读未提交：可能会读取到其他事务未提交的数据，也叫做脏读
• rc 读已提交：读到的数据必须是已经提交的数据，可能会发生两次读取结果不一致，叫做不可重复读。不可重复读解决了脏读的问题，只会读取已经提交的事务（oracle默认隔离级别）
• rr 可重复读：mysql默认级别， 每次读取结果都是一样，但是有可能产生幻读
• s 串行化：一般不会使用，它会给每一行读取的数据加锁，会导致大量超时和锁竞争的问题

事务的隔离级别导致的问题

• 脏读：读取到另一个事务未提交的数据的现象就是脏读；
• 不可重复读：在同一个事务中，前后两次读取的数据不一致的现象
• 幻读：同一个事物两次读取同一个范围的数据，后一次读取到前一次查询没有看到的行，

幻读和不可重复读有些类似，但是幻读强调的是集合的增减，而不是单条数据的更新

6.3、 MVCC 多版本并发控制

多版本并发控制：读取数据时通过一种类似快照的方式将数据保存下来，这样读锁就和写锁不冲突了，不同的事物session会看到自己特定版本的数据，版本链；

• MVCC只在RC读已提交和RR 可重复读两个隔离级别下工作。
• 其他两个隔离级别和MVCC不兼容。因为RU读未提交总是读取最新的数据行，而不是符合当前事务版本的数据行。
• 而串行化则会对所有读取的行都加锁。
• mysql的MVCC通过版本链，实现多版本可并发读-写。通过ReadView生成策略的不同实现不同的隔离级别

7、索引

推荐文章：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。

提取句子主干，就可以得到索引的本质：索引是数据结构。

索引对数据库的影响：

• 1、索引可以极大的提高数据的查询速度。
• 2、通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
• 3、但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件
• 4、索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大，如果非聚集索引很多，一旦聚集索引改变，那么所有非聚集索引都会跟着变。

7.1、索引分类

• 主键索引(PRIMARY KEY)：建立在主键上的索引被称为主键索引，一张数据表只能有一个主键索引，索引列值不允许有空值，通常创建表时一起创建。

• 唯一索引(UNIQUE KEY)：建立在UNIQUE字段上的索引被称为唯一索引，一张表可以有多个唯一索引，索列值允许为空，列值中出现多个空值不会发生重复冲突，索引类型Nuique。

• 普通索引(KEY/INDEX)：建立在普通字段上的索引被称为普通索引，索引类型Normal。

• 前缀索引：前缀索引是指对字符类型字段的前几个字符或对二进制类型字段的前几个bytess建立的索引，而
  不是在整个字段上建索引。前缀索引可以建立在类型为char、varchar、binary、varbinaryl的列
  上，可以大大减少索引占用的存储空间，也能提升索引的查询效率。

  前缀索引是一种能使索引更小更快的有效方法，但是也包含缺点：mysql无法使用前缀索引做order by和group by

7.2、EXPLAIN 查看sql语句执行计划

EXPLAIN sql语句 
explain select * from user where id=1

7.3、 索引设计原则

基本原则：查询更快、占用空间更小

• 1.适合索引的列是出现在where子句中的列，或者连接子句中指定的列。
• 2.基数较小的表，索引效果较差，没有必要在此列建立索。
• 3.使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间，如果搜索向超过索引前缀长度，则使用索引排除不匹配的行，然后检查其余行是否可能匹配。
• 4.不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。
• 5.定义有外键的数据列一定要建立索引。
• 6.更新频繁字段不适合创建索引
• 7.若是不能有效区分数据的列不适合做索引列（如性别，男女未知，最多也就三种，区分度实在太低）
• 8.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(，b)的索引，那么只需要修改原来的索引即可。
• 9.对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
• 10.对于定义为text、image和bit的数据类型的列不要建立索引。

7.4、 解决慢sql查询

添加索引，索引是帮助MySQL高效获取数据的排好序的数据结构

• 1、检查是否走了索引，如果没有则优化SQL利用索引
• 2、检查所利用的索引，是否最优索引
• 3、检查所查字段是否都是必须的，是否查询了过多字段，查出了多余数据
• 4、检查表中数据是否过多，是否应该进行分库分表
• 5、检查数据库实例所在机器的性能配置，是否太低，是否可以适当增加资源

7.5 、导致索引失效

sql语句中只要直接对字段进行了操作，就会导致索引失效；

类如：

select * from user where name = 1 ;（字段类型转换）
select * from user where id+1 = 1；(对字段进行操作) 

• 1、没有符合最左前缀原则
• 2、字段进行了隐式数据类型转化
• 3、走索引没有全表扫描效率高

8、权限管理和备份

8.1 用户管理

--创建用户 CREATE USE R用户名 IDENTIFIED BY 密码
CREATE USER kuangshen IDENTIFIED BY '123456'
--修改密码（修改当前用户密码）
SET PASSWORD PASSWORD ('123456')
--修改密码（修改指定用户密码）
SET PASSWORD FOR kuangshen PASSWORD ('123456')
--重命名 RENAME USER原来名字TO新的名字
RENAME USER kuangshen To kuangshen2
--用户授权 ALL PRIVILEGES全部的权限，库.表
--ALL PRIVILEGES除了给别人授权，其他都能够干
GRANT ALL PRIVILEGES ON *.* TO kuangshen2

--查询权限
SHOW GRANTS FOR kuangshen2
--查看指定用户的权限
SHOW GRANTS FOR root@localhost
--ROOT用户权限：GRANT ALL PRIVILEGES ON *.* TO 'root'@'1oOCa1hOst' WITH GRANT OPTION
--撤销权限REVOKE哪些权限，在哪个库徽销，给谁徽销
REVOKE ALL PRIVILEGES ON *.* FROM kuangshen2
--删除用户
DROP USER kuangshen

8.2、MySQL备份

为什么备份

• 保证重要数据不丢失
• 数据转移

MySQL数据库备份的方式

• 直接拷贝物理文件
• 在可视化工具中手动导出
• 使用命令行导出 mysqldump 命令行

--导出数据库
# mysqldump -h 主机 -u 用户名 -p 密码 数据库 >物理磁盘位置/文件名

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

9、规范数据库设计

三大范式

第一范式（1NF）

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

第二范式（2NF）

前提：满足第一范式

每一张表只描述一件事情

第三范式（3NF）

前提：满足第一和第二范式

需要确保数据表中的每一列数据和主键直接相关，而不能间接相关

(规范数据库的设计)

规范性和性能的问题
阿里巴巴开发手册规范：关联查询的表不得超过三张表

• ·考虑商业化的需求和目标，（成本，用户体验！）数据库的性能更加重要
• ·在规范性能的问题的时候，需要适当的考虑一下规范性！
• ·故意给某些表增加一些冗余的字段。（从多表查询中变为单表查询）
• ·故意增加一些计算列（从大数据量降低为小数据量的查询：索引）

10、JDBC

10.1、数据库驱动

10.2、JDBC

SUN公司为了简化开发人员的（对数据库的统一）操作，提供了一个Uva操作数据库的)规范，俗称JDBC

这些规范的实现由具体的厂商去做~

对于开发人员来说，我们只需要掌握JDBC接口的操作即可！

导入数据库驱动包：mysql-connector-java-8.0.15.jar

10.3 java编写jdbc代码

1、导入jdbc驱动包mysql-connector-java-8.0.15.jar到java项目的bil目录下

2、编写java代码

//我的第一个JDBC程序
public class JdbcFirstDemo{
	public static void main(String[]args){
		//1.加载驱动
		C1ass.forName("com.mysq1.jdbc.Driver");//固定写法，加载驱动
		//2.用户信息和urL
		//useUnicode=true&characterEncoding=utf8&useSSL=true
		String url = "jdbc:mysql://localhost:3306/[数据库名]useUnicode=true&characterEncoding=utf8&useSSL=true";
		String username ="root"; //用户名
		String password "123456"; //密码
		//3.连接成功，数据库对象
    	Connection connection = DriverManager.getConnection(url,username,password);
		//4.执行SQL的对象
    	Statement statement = connection.createStatement();
		//5.执行SQL的对象去执行SQL,可能存在结果，查看返回结果 （查询使用executeQuery，修改使用executeUpdate）
  		String sql =  "SELECT FROM users";
		ResultSet resultSet = statement.executeQuery(sql);//返回的结果集
        //6、解析返回的结果集
        while (resultSet.next()){
			System.out.println("id="+resultSet.getobject(columnLabel:"id")); //映射数据库字段
			System.out.println("name="+resultSet.getobject(columnLabel:"NAME"));
			System.out.println("pwd="+resultSet.getobject(columnLabel:"PASSWORD"));
			System.out.println("email="+resultSet.getobject(columnLabel:"email"));
			System.out.println("birth="+resultSet.getobject(columnLabel:"birthday"));
        }
		//7、释放连接
        resultSet.close();
		statement.close();
		connection.close();
	}
}

步骤：

• 1、加载驱动
• 2、连接数据库DriverManager
• 3、获取执行sql的对象Statement
• 4、获取返回的结果集
• 5、释放连接

DriverManager

//DriverManager.registerDriver(new com.mysq1.jdbc.Driver();
c1ass.forName("com.mysq1.jdbc.Driver");//固定写法，加载驱动
Connection connection DriverManager.getConnection(url,username,password);

//connection代表数据库
//数据库设置自动提交
//事务提交
//事务滚回
connection.rollback();
connection.commit();
connection.setAutoCommit()

URL

string url "jdbc:mysq1://localhost:3306/jdbcstudy?
useUnicode=true&characterEncoding=utf8&usessL=true";
    
//mysq1--3306
// 协议：//主机地址：端口号/数据库名？参数1&参数2&参数3
    
//ora1ce--1521
//jdbc:oracle:thin:@localhost:1521:sid

Statement 执行SQL的对象 、 PrepareStatement 执行SQL的对象

string sql = "SELECT*FROM users";//编写SQL

statement.executeQuery();//查询操作返 Resultset
statement.execute();//执行任何sQL
statement.executeupdate);//更新、插入、删除。都是用这个，返回一个受影响的行数

ResultSet查询的结果集：封装了所有的查询结果

获得指定的数据类型

resultset.getobject();//在不知道列类型的情况下使用
//如果知道列的类型就使用指定的类型
resultset.getstring();
resultset.getInt();
resultset.getFloat();
resultset.getDate();
resultset.getobject();
......

遍历、指针

resultset.beforeFirst();//移动到最前面
resultset.afterLast();//移动到最后面
resu1tset.next();//移动到下一个数据
resultset.previous();//移动到前一行
resultset.absolute(row);//移动到指定行

释放资源

//6、释放连接
resultset.close();
statement.close();
connection.close();//耗资源，用完关掉

10.4 、statement对象

Jdbc中的statementy对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改
查语句即可。
Statementy对象的executeUpdate方法，用于向数据库发送增、删、改的sql语句，executeUpdate执行完后，将会返回一个整
数（即增删改语句导致了数据库几行数据发生了变化）
Statement.executeQuery,方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的ResultSet对象。

CRUD操作-insert（增）、delete（删）、update（改）

示例操作：

//insert（增）
Statement st =  conn.createstatement();
string sq1 = "insert into user(...) values(....)"
int num  = st.executeupdate(sq1);
if(num>0){
    System.out.println("插入成功！！！")；
}
//delete（删）
Statement st =  conn.createstatement();
string sq1 = "delete from user where id = 1"
int num  = st.executeupdate(sq1);
if(num>0){
    System.out.println("删除成功！！！")；
}
//update（改）
Statement st =  conn.createstatement();
string sq1 = "update user set name='' where name=''"
int num  = st.executeupdate(sq1);
if(num>0){
    System.out.println("修改成功！！！")；
}

CRUD操作-read

使用executeQuery(String sql))方法完成数据查询操作，示例操作：

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

编写工具类

1、编写配置文件 db.properties （放在项目src目录下）

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456

2、Utils工具类

//import .... 导入对应的包
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{
            //读取db.properties配置文件信息，如果配置文件在src子目录下需要填入相对路径获取
			Inputstream in = Jdbcutils.class.getclassLoader().getResourceAsstream("db.properties");
			Properties properties =  new Properties();
			properties.load(in);
			driver = properties.getproperty("driver");
      		url = properties.getproperty("url");
			username =  properties.getProperty("username");
			password =  properties.getProperty("password");
			//1.驱动只用加载一次
			class.forName(driver);
        }catch (Exception e){
			e.printstackTrace();
		}
	}
    
    //获取连接方法
	public static Connection getConnection() throws SQLException{
        return DriverManager.getConnection(url,username,password);
    }
	
    //释放连接资源方法
	public static void release(Connection conn,Statement st,ResultSet rs){
		if (rs!=null){
        	try{
            	rs.close();
        	}catch (SQLException e){
				e.printstackTrace();
       	 	}
    	}
        if (st!=null){
        	try{
            	st.close();
        	}catch (SQLException e){
				e.printstackTrace();
       	 	}
    	}
        if (conn!=null){
        	try{
            	conn.close();
        	}catch (SQLException e){
				e.printstackTrace();
       	 	}
    	}
	}
}

增删改使用工具类

//import .... 导入对应的包
public class TestInsert{
    public static void main(String[]args){
	Connection conn null;
	Statement st null;
	ResultSet rs null;
	try{
        conn=JdbcUtils.getConnection();//获取数据库连接
		st=conn.createStatement();/获QL的执行对象
		String sql =  "INSERT ......";
        int i=st.executeUpdate(sql);
        if (i>0){
            System.out.println("插入成功！")；
        }
    }catch (SQLException e){
        e.printstackTrace();
    }finally{
        Jdbcutils.release(conn,st,rs);
    }
}
}

查询使用工具类

//import .... 导入对应的包
public class TestSelect{
    public static void main(String[]args){
	Connection conn null;
	Statement st null;
	ResultSet rs null;
	try{
        conn=JdbcUtils.getConnection();//获取数据库连接
		st=conn.createStatement();/获QL的执行对象
		//SQL
		String sql =  "select from users where id 1";
		rs = st.executeQuery(sql);//查询完毕会返回一个结果集
		while (rs.next()){
            System.out.println(rs.getstring("NAME"));
        }
    }catch (SQLException e){
        e.printstackTrace();
    }finally{
        Jdbcutils.release(conn,st,rs);
    }
	}
}

SQL 注入

sql存在漏洞，会被攻击导致数据泄露，SQL会被拼接or

 //  传入 name =  ' or ' 1=1  password = ' or '1=1；
//拼接后:
SELECT FROM users WHERE Name = '' or '1=1' AND password = '' or '1=1';

10.5 、PrepareStatement 对象

PrepareStatement 可以防止sql注入。效率更高!

• PrepareStatement 类继承Statement 类，进行安全的封装
• PrepareStatement 对象执行sql语句需要进行预编译,后执行
• PrepareStatement 使用 ? 占位符代替参数

PrepareStatement防止SQL注入的本质，把传递进来的参数当作字符

假设其中存在转义字符，比如 ‘ 会直接转义

//import .... 导入对应的包
public class TestInsert{
public static void main(String[]args){
	Connection conn null;
	Statement st null;
	ResultSet rs null;
	try{
        conn=JdbcUtils.getConnection();//获取数据库连接
		//区别
		//使用？占位符代替参数
		String sql = "insert into users(id,NAME,PASSWORD',email,birthday') values(?,?,?,?,?)";
		st = conn.prepareStatement(sql); //预编译SQL,先写sql,然后不执行
		//手动给参数赋值
		st.setInt(1,4);//id
		st.setstring(2,"qinjiang");
		st.setstring(3,"1232112");
		st.setstring(4,"24734673@qq.com");
		//注意点：sqL.Date  数据库java.sql.Date()
		// util.Date Java  new Date().getTime()获得时间截
		st.setDate(5,new java.sql.Date(new Date().getTime()));
        
        //执行
        int i = st.executeUpdate()
        if(i > 0){
            System.out.println("插入成功");
        }

    }catch (SQLException e){
        e.printstackTrace();
    }finally{
        Jdbcutils.release(conn,st,rs);
    }
  }
}

10.6、JDBC操作事务

ACID 原则

• 原子性：要么全部成功，要么全部失败
• 一致性： 总数不变
• 隔离性： 多个进程互不干扰
• 持久性：一旦提交不可逆，持久化到数据库

隔离性产生的问题

• 脏读: 一个事务读取到另一个事务未提交的数据
• 不可重复度：在同一个事务内，重复读取表中的数据，表数据发发生了改变
• 幻读：在一个事务内，读取到了别人插入的数据，导致前后读取出来的结果不一致

java代码实现事务

• 1、开启事务 conn.setAutoCommit(false);
• 2、一组业务执行完毕，提交事务
• 3、可以在catch语句显示的定义回滚语句，但默认失败就会回滚

//import .... 导入对应的包
public class TestInsert{
public static void main(String[]args){
	Connection conn null;
	Statement st null;
	ResultSet rs null;
	try{
        conn = JdbcUtils.getConnection();//获取数据库连接
        
        //关闭数据库的自动提交，自动会开启事务
        conn.setAutoCommit(false);//开启事务
        
		String sqlA = "insert into users(id,NAME) values(?,?)";
		st1 = conn.prepareStatement(sqlA); 
		//手动给参数赋值
		st1.setInt(1,4);
		st1.setstring(2,"qinjiang");
        st.executeUpdate(); //执行
        
        String sqlB = "insert into users(id,NAME) values(?,?)";
		st2 = conn.prepareStatement(sqlB); 
		//手动给参数赋值
		st2.setInt(1,4);
		st2.setstring(2,"qinjiang");
        s2.executeUpdate(); //执行
        
        //业务完毕，提交事务
        conn.commit();
        System.out.println("成功");

    }catch (SQLException e){
        //如果失败,默认回滚,以下代码不用写
       // try{
       //     conn.rollback(); //如果失败则回滚事务
      //  }catch(SQLException e1){
      //       e1.printstackTrace();
       // }
        e.printstackTrace();
    }finally{
        Jdbcutils.release(conn,st,rs);
    }
  }
}

10.9、数据库连接池

数据库连接----执行完毕------释放

连接-----释放 十分浪费系统资源

池化技术：准备一些预先的资源，过来就连接预先准备好的资源

• 最小连接数： min
• 最大连接数： max
• 等待超时： 100ms

编写连接池，实现一个接口DataSource

开源数据源实现

• DBCP
• C3P0
• Druid：阿里巴巴

使用这些数据库连接池之后，我们在项目开发中就不需要编写连接数据库的代码

DBCP

导入需要用到的jar包到lib下，添加dbcpconfig.properties配置文件

commons-dbcp-1.4 、 commons-pool-1.6

C3P0

导入需要用到的jar包到lib下，添加c3p0-config.xml配置文件

c3p0-0.9.5.5 、 mchange-commons-java-0.2.19
开启事务

	String sqlA = "insert into users(id,NAME) values(?,?)";
	st1 = conn.prepareStatement(sqlA); 
	//手动给参数赋值
	st1.setInt(1,4);
	st1.setstring(2,"qinjiang");
    st.executeUpdate(); //执行
    
    String sqlB = "insert into users(id,NAME) values(?,?)";
	st2 = conn.prepareStatement(sqlB); 
	//手动给参数赋值
	st2.setInt(1,4);
	st2.setstring(2,"qinjiang");
    s2.executeUpdate(); //执行
    
    //业务完毕，提交事务
    conn.commit();
    System.out.println("成功");

}catch (SQLException e){
    //如果失败,默认回滚,以下代码不用写
   // try{
   //     conn.rollback(); //如果失败则回滚事务
  //  }catch(SQLException e1){
  //       e1.printstackTrace();
   // }
    e.printstackTrace();
}finally{
    Jdbcutils.release(conn,st,rs);
}

}
}

## 10.9、数据库连接池

数据库连接----执行完毕------释放

连接-----释放  十分浪费系统资源

> 池化技术：**准备一些预先的资源，过来就连接预先准备好的资源**

- 最小连接数： min
- 最大连接数： max
- 等待超时： 100ms

编写连接池，实现一个接口`DataSource`

> 开源数据源实现

- DBCP
- C3P0
- Druid：阿里巴巴

使用这些数据库连接池之后，我们在项目开发中就不需要编写连接数据库的代码



> DBCP

导入需要用到的jar包到lib下，添加dbcpconfig.properties配置文件

`commons-dbcp-1.4 `  、  `commons-pool-1.6`



> C3P0

导入需要用到的jar包到lib下，添加c3p0-config.xml配置文件

`c3p0-0.9.5.5`    、  `mchange-commons-java-0.2.19`