MySQL基础知识系统学习

最新推荐文章于 2022-12-26 08:00:00 发布
最新推荐文章于 2022-12-26 08:00:00 发布
阅读量2.6k 收藏 3
点赞数 1
分类专栏： 数据库版块 # MySQL学习 文章标签： JDBC MySQL 连接池 XML DBUtils工具类
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_19314763/article/details/110306068
版权

文章目录

思维导图概览全文

在这里插入图片描述

一、MySQL基础

1、数据库的基本概念

1.1 什么是数据库

  • 数据库，英文名DataBase，是用来存储和管理数据的仓库
  • 数据库本质还是一个文件系统，将数据以文件的形式保存在计算机的磁盘中

1.2 为什么要使用数据库

存储方式优点缺点
内存读写速度快不能永久保存数据，数据都是临时存储
硬盘数据可以永久保存IO流读写数据，很不方便
数据库1、数据可以永久保存
2、方便存储和管理数据
3、使用统一方式操作数据(SQL)		占用资源，另外有些数据库需要付费(比如Oracle)

1.3 常见的数据库软件

  • 2020年11月数据库最新排行榜
    在这里插入图片描述

说明：
MySQL开发公司原先是瑞典MySQL AB公司，
2008年被开发Java语言的Sun公司收购，
2009年Sun公司又被甲骨文(Oracle)公司收购，
所以现在Java、MySQL、Oracle都属于甲骨文公司旗下的产品

  • 开发中常见的数据库
数据库介绍
MySQL开源免费(6版本之前)
因为免费开源，运作简单，常作为中小项目的数据库首选
2009年被甲骨文公司收购，现在6.x版本及后续版本都要收费
Oracle甲骨文公司
收费
大型数据库
安全性高
Oracle公司核心产品
DB2IBM公司
超大型数据库
常在政府、银行项目中使用
SelServerMicroSoft微软公司
中型数据库
C#、.net语言经常使用
只能运行在Windows机器上，扩展性、稳定性、安全性、性能都表现平平
  • 选择MySQL原因
    • 功能强大，足以满足web应用开发
    • 开源，免费

2、MySQL的安装和卸载

MySQL5.7的安装和卸载教程

3、MySQL环境变量配置

  • 拷贝下载的MySQL文件夹的bin路径
  • 环境变量中添加一个新的系统变量：MYSQL_HOME,值为刚才拷贝的路径
  • 编辑系统变量Path,在最后添加：%MYSQL_HOME%；

4、MySQL的启动和关闭

界面方式启动和关闭

  • 启动
    • windows + R 键 输入：services.msc 回车
    • 找到mysql服务并选中
    • 右键选择启动
    • 启动完成
  • 关闭
    • windows + R 键 输入：services.msc 回车
    • 找到mysql服务并选中
    • 右键选择停止
    • 关闭完成

命令行方式启动和关闭

  • 启动

    • 计算机的任务中，点击搜索按钮，输入cmd
    • 选择：以管理员身份运行
    • 打开的DOS窗口输入：net start mysql57 回车
      在这里插入图片描述

  • 关闭

    • 计算机的任务中，点击搜索按钮，输入cmd
    • 选择：以管理员身份运行
    • 打开的DOS窗口输入：net stop mysql57 回车

5、MySQL的登录

命令行方式登录

  • 登录本地数据库
    1. mysql -uroot -p123456 回车
    2. mysql -uroot -p 回车，再输入密码123456 回车
  • 登录远程数据库
    1. mysql -h10.0.192.209 -uroot -p123456
    2. mysql -h10.0.192.209 -uroot -p 回车，再输入密码 123456回车

SQLyog工具登录

6、MySQL的目录结构

在这里插入图片描述
在这里插入图片描述

7、数据库管理系统

7.1 基本概念

一种操作和管理维护数据库的大型软件
比如：MySQL、Oracle、Sqlserver等

7.2 作用

用于建立、使用、维护数据库，对数据库进行统一的管理

7.3 DBMS、DB、表之间的关系

MySQL中管理着很多数据库，每个数据库对应着多张表，每张表保存对应业务的数据

在这里插入图片描述

8、数据库表

  • 数据库中以表为组织单位存储数据
  • 表类似Java中的类，每个字段都有对应的数据类型，每个Java对象对应表的每一条记录

9、MySQL自带的数据库

在这里插入图片描述

10、MySQL中的字段类型

- int   整型
- double  浮点型
- varchar	字符串型
- date  日期型，只显示年月日（yyyy-MM-dd）
- datetime  日期型，年月日时分秒
- char 	字符串(MySQL中代表字符串，Java中代表字符)
  • varchar 和 char 的区别
    • varchar：可变长度，根据实际字符串长度开辟空间
    • char： 固定长度，指定多少，开辟多少空间存取数据

二、SQL入门

1、SQL概念

1.1 什么是SQL

  • SQL全称为：结构化查询语言(Structure Query Language)，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理数据库系统

1.2 SQL的作用

  • 是所有关系型数据库的统一查询规范
  • 所有关系型数据库都支持SQL
  • 不同数据库之间SQL有一些区别，类似各地方言

2、SQL通用语法

  • 支持单行、多行书写，分号结尾（SQLyog中可以不用分号）
  • 可以使用空格和缩进增加SQL语句的可读性
  • MySQL中使用SQL不区分大小写，一般关键字大写，数据库名，表名，字段名小写
  • 注释方法
写法说明
--空格 注释内容单行注释
/*注释内容*/多行注释
#注释内容MySQL特有的单行注释

3、SQL分类

分类说明
数据库定义语言(DDL)Data Definition Language，用来定义数据库对象：数据库、表、字段
数据库操作语言(DML)Data Manipulation Language，用于对数据库中表的记录进行更新
数据库查询语言(DQL)Data Query Language，用于对数据库中表的记录进行查询
数据控制语言(DCL)Data Control Language，用于定义数据库的访问权限、安全级别、创建用户

3.1 DDL语言

对数据库进行操作

- 创建数据库（指定名称的方式）
create database mydb;  # 该方式创建的数据库默认编码为latin1编码，很可能会导致乱码情况

- 创建数据库（指定名称 + 字符集的方式）
create database mydb character set utf8;

- 切换数据库
use mydb;

- 查询当前正在使用的数据库
select database();

- 查询所有数据库
show databases;

- 修改数据库字符集
alter database mydb character set utf-8;

- 查询数据库的基本信息
show create database mydb;

- 删除数据库
drop database mydb;    # 慎用

对表进行操作

  • 格式：

      create table 表名(
  	字段名称1	 字段类型1,
  	字段名称2     字段类型2,
  	字段名称3     字段类型3
  ) ;

  • 示例：

      - 创建表
  	create table tuser(
  		id	int, # 学号
  		name  varchar(30) # 姓名
  	);
  	
  - 快速创建一个表结构相同的表
  	create table tuser2 like tuser;
  
  - 查看表结构
  	desc tuser2;
  
  - 查看当前数据库中所有表
  	show tables;
  	
  - 查看创建表的SQL
  	show create table tuser2;
   
  - 删除表（永久删除）
  	drop table tuser2;
  	drop table if exists tuser2; # 存在则删除，不存在不执行删除
  
  - 修改表名称
  	rename table tuser2 to tuser3;
  	
  - 修改表的字符集
  	alter table tuser3 character set gbk;
  	
  -   向表中添加字段
  	alter table tuser3 add class varchar(10);
  
  - 修改表中列的类型 / 长度
  	alter table tuser3 modify class varchar2(15);
  	alter table tuser3 modify class char(15);
  
  - 修改字段名
  	alter table 表名 change 旧字段名  新字段名  新字段的类型;
  	alter table tuser3 change class level int;
  
  - 删除列
  	alter table tuser3 drop level;

3.2 DML语言

对表中数据操作–新增

  • 格式：

      insert into 表名(字段1，字段2) values(字段1值,字段2值);

  • 示例：

      - 插入全部字段，并写出所有字段名
  	insert into tuser(id,name) values(1,'孙悟空');
  	
  - 插入全部字段，不写全部字段名
  	insert into tuser values(2,'猪八戒');
  	
  - 插入指定字段
  	insert into tuser(id) values(3);

  • 注意事项：

      1. 字段与字段值必须对应，
  2. 插入varchar、char、date类型时，必须使用单引号 / 双引号 包裹
  3. 如果插入空值，可以不写该字段和字段值，也可以写上字段，但字段值写null

对表中数据操作–修改

  • 格式：

      1. update 表名 set 字段1 = 字段1新值,字段2 = 字段2新值;
  2. update 表名 set 列名 = 新值 where 条件表达式;

  • 示例：

      1. update tuser set name = '王刚',level = 5; （慎用！！）
  2. update tuser set name = '金刚3号',level = 5 where id = 3;

对表中数据操作–删除

  • 格式：
    1. delete from 表名;
    2. delete from 表名 where 条件表达式;

  • 示例：

      1. delete from tuser; # 删除所有数据（不推荐，删除效率低）
  2. truncate table tuser;  # 删除所有数据（推荐，删除整张表，再创建一张一样的空表）
  3. delete from tuser where id = 3 # 删除指定数据;

3.3 DQL语言

简单查询

  • 格式

      select 字段名 from 表名;

  • 示例：

      - 查询所有数据，显示全部字段
  	select * from tuser;  
  - 查询所有数据，只显示个别字段
  	select id,name from tuser; 
  - 查询所有数据，给字段名起别名
  	select id as "用户编号",name as "用户姓名" from tuser; # as 可以省略
  - 查询有几个level
  	select level from tuser;
  - 查询level，并去重
  	select distinct level  from tuser;
  - 将用户level都加100展示
  	select level + 100 as new_level from tuser;

条件查询

比较运算符说明
>
<
>=
<=
=
<>
!=		大于
小于
大于等于
小于等于
等于
不等于
不等于
BETWEEN, AND …显示在某一段区间内的数据
比如：level between 200 and 1000
IN(集合)表示某字段的值在这个集合范围内的数据
1. 集合可以直接写固定值
比如：name in(‘孙悟空’,‘猪八戒’)
2. 集合可以用查询结果代替
比如：name in (select name from tuser2)
LIKE ‘%内容%’模糊查询
IS NULL查询某字段为空的数据
比如：level is null
注意：不能写成: level = null
逻辑运算符说明
and
&&		多个条件同时成立
or
||		多个条件任一成立
not不成立，取反

  • 格式：

      select 字段名 from 表名 where 条件表达式;

  • 示例：

      - 查询level在1-5之间的用户信息
  select * from tuser where level >= 1 and level <= 5;

  - 查询编号不等于1的所有的用户姓名
  1. select name from tuser where id <> 1;
  2. select name from tuser where id != 1;

  - 查询level在5-8之间的用户信息
  select * from tuser where level between 5 and 8;

  - 查询编号等于1和3的用户姓名和等级
  select name,level from tuser where id in(1,3);

  - 查询姓孙的所有用户全部信息
  select * from tuser where name like '孙%'; # 表示名字以'孙'开头的所有用户

  - 查询名字包含'空'的用户编号和姓名
  select id,name from tuser where name like '%空%'; # 表示名字中只要包含'空'都查询

  - 查询未配置level的用户信息
  select * from tuser where level is null;

  - 查询姓张，并且level大于5的所有用户信息
  select * from tuser where name like '张%' and level >5;

  - 查询编号小于10或者level小于10的用户信息
  select * from tuser where id <10 or level < 10;

  - 查询配置了level的用户信息
  select * from tuser where level is not null;

排序查询

  • 格式：

      select 字段名 from 表名 order by 排序字段 [desc/asc];
  desc  降序
  asc  升序

  • 示例：

      - 单列排序
  select id,name from tuser order by level ; # 默认升序
  select id,name from tuser order by level desc; # 降序排列

  - 组合排序
  select * from tuser order by level desc,id desc;	 #在等级基础上， 再通过编号排序
  特点：如果第一个排序字段值相同，就按照第二个排序字段排序

聚合函数

  • 作用
    将一列数据作为一个整体，进行纵向的统计计算

    常用聚合函数功能
    count(字段)统计记录数,计算时会忽略空值，不能使用有空值的列计算
    sum(字段)求和操作
    max(字段)求最大值
    min(字段)求最小值
    avg(字段)求平均值

  • 格式：

      select 聚合函数(字段) from 表名 [where 条件表达式];

  • 示例：

      - 查询全部员工的数量
  	select count(1) from tuser;	
  	select count(*) from tuser;
  	select count(id) from tuser;	

  - 查询编号大于10的用户数量
  	select count(1) from tuser where id > 10;

  - 查询等级大于5的所有用户数量
  	select count(1) from tuser where level >5;

  - 查询用户表的最高等级、最低等级、平均等级、 
  	select 	max(level)
  			,min(level)
  			,avg(level)	
  	from tuser;

分组查询

  • 注意事项
    分组的目的，是为了统计，所以一般分组会与聚合函数一起使用，单独进行分组是没有意义的。

  • 格式：

      	select	[分组字段 / 聚合函数] from 表名 group by 分组字段;

  • 示例：

      - 通过性别进行分组，求各组的平均薪资
  	select sex as '性别',avg(salary) as '平均薪资' from tuser group by sex;

  - 求各个等级的平均薪资，等级为空的不考虑
  	select level as '等级'
  			,avg(salary)  as '等级平均薪资' 
  	from tuser 
  	where level is not null 
  	group by level; 

  - 查询平均薪资大于6000的部门(上述表不再适用，需要相关自建表)
  	 select dept_name '部门名称'
  			,avg(salary) '部门平均薪资'
      where dept_name is not null
      group by dept_name
      having avg(salary) > 6000; #分组之后进行过滤，使用having

  • where 和 having 的区别
    where
     1. 在分组前进行过滤；
     2. where 后面不能跟聚合函数
    having
     1. having在分组后进行过滤
     2. having后面可以写聚合函数

limit关键字

- 查询tuser表前5条数据
	1. select * from tuser limit 0,5;
	2. select * from tuser limit 5;

- 查询tuser表，从第4条开始，查询6条数据
	select * from tuser limit 3,6;

- limit分页操作
	select * from tuser limit 0,3;  #第一页
	select * from tuser limit 3,3;  #第二页
	select * from tuser limit 6,3;  #第三页  (3-1)*3 = 6
	
	分页计算公式
		起始行数 = （当前页码 - 1）* 每页显示条数

3.4 DCL语言

创建用户

参数说明
用户名创建的新用户的登录名称
主机名指定用户登录的机器，本地登录用’localhost‘
如果希望用户可以在任意一台机器登录，可以使用’%’
密码登录密码

  • 语法格式

      create user '用户名'@'主机名' identified by '密码';

  • 注意
    用户创建完后会保存在MySQL自带的名为mysql数据库的user表中

  • 示例

      # 创建一个用户，只能在localhost机器登录MySQL服务器
  create user 'testuser'@'localhost' identified by '123456';

    登录报错参考：MySQL8.0的登录时caching_sha2_password问题

用户授权

  • 语法格式
    grant 权限1,权限2… on 数据库名.表 to ‘用户’@‘主机名’;

  • 示例

      --给testuser用户分配对dbtest数据库的employee表的查询权限，并且只能localhost主机可以访问
  grant select on dbtest.employee to 'testuser'@'localhost';
  --给testuser用户分配对所有数据库的所有资源的所有权限，并且可以在任意机器访问
  grant all on *.* to 'testuser'@'%';

  • 注意
    报错参考：You are not allowed to create a user with GRANT

查看用户权限

  • 语法格式

      show grants for '用户名'@'主机名';

  • 示例

      show grants for 'testuser'@'localhost';

查询用户和删除用户

  • 查询用户

      1.切换到mysql数据库 
  2. select * from user;

  • 删除用户

      drop user '用户名'@'主机名';
  eg:
  drop user 'testuser'@'localhost';

三、MySQL单表、约束、事务

1、主键约束

  • 特点
    不可重复
    唯一
    非空

  • 作用
    用来表示数据库表中的每一条记录

  • 语法格式

      字段名  字段类型  primary key

  • 示例

      创建一个带主键的表--方式1
  	create table blogs(
  		bid   int primary key,
  		btype char(4),
  		content varchar(2000)
  	); 
  
  - 创建一个带主键的表--方式2
  	create table blogs(
  		bid   int，
  		btype char(4),
  		content varchar(2000)，
  		primary key(bid)
  	);
  
  - 创建一个带主键的表--方式3
  	create table blogs(
  		bid   int，
  		btype char(4),
  		content varchar(2000)
  	);
  	alter table blogs add primary key(bid);

  - 验证表是否创建成功
  	desc blogs;

  - 删除主键
  	alter table blogs drop primary key;

2、主键自增

  • 使用关键字，字段类型必须是整数类型
    auto_increment

      - 创建主键自增的表
  	create table clogs(
  		cid int primary key auto_increment,
  		ctype char(4),
  		cinfo varchar2(2000)
  	);

  - 添加数据
  	1. insert into clogs(ctype,cinfo) values('java','java se 基础知识学习');
  	2. insert into clogs values(null,'java','java se 基础知识学习');
  
  - 修改自增的起始值
  	create table clogs(
  		cid int primary key auto_increment,
  		ctype char(4),
  		cinfo varchar2(2000)
  	) auto_increment = 100;  # 自增从100开始增加

  • delete 和 truncate 对自增的影响

    • delete删除表中所有数据：
      将表中所有数据逐条删除
      对自增没有影响(会在删除之前的数据的最大id上继续自增)

    • truncate删除表中所有数据：
      将整个表删除，再创建一个结构相同的表
      自增从1开始(因为默认创建的新表的自增起始值都是1)

3、非空约束

  • 语法格式

      字段名  字段类型  not null

  • 示例

      create table blogs (
  	bid int,
  	btype char(4) not null,
  	binfo varchar2(2000)
  );

4、唯一约束

  • 特点
    保证表中某一列不能够重复；
    但是对null值不做判断

  • 语法格式

      字段名 字段类型  unique

  • 示例

      	create table blogs(
  		bid int primary key,
  		bcode char(10)  unique,
  		binfo varchar2(2000)
  	);

  • 主键约束 和 唯一约束区别

      - 主键约束：唯一、且不能为空
  - 唯一约束：唯一，但可以为空
  - 一个表中只能有一个主键，可以有多个唯一约束

5、默认值

  • 作用
    用来指定某一列的默认值

  • 语法格式

      字段名  字段类型  default 默认值

  • 示例

      create table blogs(
  	bid int primary key,
  	bcode char(10),
  	binfo varchar2(2000) default '无内容'
  );

6、数据库事务

  • 什么是事务
    一个由一条或多条SQL组成的一个整体，事务中的操作，要么全部成功，要么全部失败
  • 手动提交事务
    • 开始事务
      1. start transaction;
      2. begin
    • 提交事务
      1. commit
    • 回滚事务
      1. rollback
  • 自动提交事务
    • MySQL默认的提交方式：自动提交事务
    • 每执行一条单独的DML语句，都是一个单独的事务，会默认开启事务和提交事务
    • 查看自动提交是否开启
      show variables like ‘autocommit’;
    • 关闭自动提交事务
      set @@autocommit = off;

7、MySQL事务隔离级别（了解）

7.1 数据并发访问

一个数据库可能拥有多个访问客户端，这些客户端都可以并发方式访问数据库，数据库的相同数据可能被多个事务同时访问，如果不采取隔离措施，会导致各种问题，破坏数据的完整性。

7.2 并发访问会产生的问题

并发访问的问题说明
脏读一个事务读取到了另一个事务中尚未提交的事务
不可重复读一个事务中俩次读取的数据内容不一致，要求在一个事务中多次读取是一致的。
这是update操作时引发的问题
幻读一个事务中，某一次的select的数据，无法支撑后续业务操作，查询得到的数据状态不准确，导致幻读

7.3 四种隔离级别

隔离级别防止问题
read uncommited 读未提交无
read commited 读已提交 (Oracle默认隔离级别)脏读
repeatable read可重复读 (MySQL默认隔离级别)脏读
不可重复读
serializable 串行化脏读
不可重复读
幻读

  • 注意
    隔离级别越高，安全性越高，但是效率越低
    所以应该根据不用情况选择隔离级别

  • 隔离级别相关命令

    1. 查看隔离级别

       select @@tx_isolation;   # MySQL8以前
 select @@transaction_isolation; # MySQL8开始

    2. 设置隔离级别

       格式：
 set global transaction isolation level 隔离级别;
 eg:
 set global transaction isolation level read commited;
 重新连接一次数据库，查询隔离级别进行验证

7.4 脏读的案例与解决

  • 案例

       如果事务隔离级别是read uncommited
  - 某客户余额有1000
  - B事务显式开启了事务(start transaction/begin)，将余额减掉500,但是还未提交事务
  - 此时A事务刚好去查询该账户余额，查询到的余额为500，发送短信通知客户账户余额为500
  - B提交事务之前因为其他错误原因导致数据回滚rollback，余额又变回1000
  - 此时客户去APP查询余额，发现账户余额仍然为1000，与刚才发送短信不符，这就是因为脏读导致的数据不一致问题

  • 解决

    - 提升事务隔离级别到read uncommited 之上
  (B事务提交之前，数据不会生效，A事务查询的余额始终为1000)

7.5 不可重复读案例与解决

  • 案例

      如果事务隔离级别低于repeatable 
  - 某手机卡余额有10元
  - A事务使用begin开启事务，事务中第一次查询卡余额为10，并判断10 < 月套餐费用19元，
  	将该余额插入一张待发短信表中（等待A事务提交后发送缴费提醒短信），此时还未提交事务
  - 刚好此时B事务对卡余额充值100元，余额此时变为110元，充值完B提交事务
  - A事务此时第二次查询卡余额，得到余额110，再次判断110 > 月套餐费用19元，不会将该余额数据插入到另一张短信提醒日志表，
  	然后A提交事务，之后服务器会监控待发短信表给客户发送缴费提醒短信。
  - 此时导致的结果就是客户还没收到短信之前，就已经缴费100元，结果缴完费，却收到了余额10元的缴费提醒，
  	同时数据库中待发短信有记录，但是短信提醒日志表却没有记录，明显数据有矛盾，这就是不可重复读导致的问题

  • 解决

      - 事务隔离级别提升到read commited之上
  	(A事务开启到提交中，查询到的结果始终一致，B事务的结果只有A事务提交后才能看到变化)

7.6 幻读案例及解决

  • 案例

      - 某表现有记录0条,有个id字段
  - A事务begin开启事务，判断表中是否有id=1的记录，查询结果为0条，然后执行插入操作，插入一条id=1的数据，此时尚未提交事务
  - B事务此时也insert 插入id=1的数据，然后提交事务
  - A事务提交事务时发现报错，不允许id重复插入，这就是幻读导致的问题(同一个事务A中，第一次查询没有数据，第二次查询却有数了)

  • 解决

      - 将事务隔离级别提升到repeatable read以上
  	(A事务开启后，执行了插入操作，B事务的插入操作会进入等待状态，直到A事务提交，B事务才会执行真正的插入操作)
  	(这种方法会导致事务等待，比较影响系统性能，一般数据库不使用该隔离级别)

四、 MySQL多表、外键、数据库设计

1、多表

  • 实际开发中，一个项目通常需要多表才能完成。
  • 单表的缺点
    冗余：同一个字段会出现大量重复数据

2、外键约束

添加外键约束

  • 格式1：创建表时添加

      [constraint] [外键约束名] foreign key(外键字段) references 主表(主表主键字段)

  • 示例

      create table department(
  	id int primary key auto_increment,
  	dept_name varchar(30),
  	dept_location varchar(20) 
  );
  create table employee(
  	eid int primary key auto_increment,
  	ename varchar(20),
  	age int,
  	dept_id int,
  	constraint emp_dept_fk foreign key(dept_id) references department(id)
  );

  • 格式2：创建表后添加

      alter table 从表 add [constraint] [外键约束名] foreign key(外键字段) references 主表(主表主键字段)

  • 示例：

      alter table employee add foreign key(dept_id) references department(id);

  • 添加外键约束之后，会产生一个强制外键约束检查
    检查从表中外键字段值，如果插入的字段值是主表主键中不存在的则会出错

  • 外键约束注意事项

    1. 从表的外键类型必须与主表的主键类型一致
    2. 添加数据时，应该先添加主表的数据

删除外键约束

  • 格式

      alter table 从表 drop foreign key 外键约束名称;

  • 示例

      alter table employee drop foreign key emp_dept_fk;

3、级联删除（了解）

  • 概念
    删除主表数据的同时，可以同时删除与之相关的从表的数据

  • 格式

      on delete cascade

  • 示例

      create table employee(
  	eid int primary key auto_increment,
  	ename varchar(20),
  	age int,
  	dept_id int,
  	constraint emp_dept_fk foreign key(dept_id) references department(id)
  	-- 添加级联删除
  	on delete cascade
  );

4、多表关系

4.1 一对一

比如：人和身份证

  • 注意
    任意一张表设置外键，指向另一张表的主键

4.2 一对多

比如：班级和学生、部门和员工

  • 注意
    在数据多的一方建立外键，指向数据少的一方的主键

4.3 多对多

比如：学生与课程、学生与老师、演员和角色

  • 建表原则
    需要创建第三张表-中间表，该表至少要有俩个字段，分别为俩张表的主键，作为中间表的外键

5、多表查询

  1. 内连接查询
  2. 外连接查询
    1. 左外连接
    2. 右外连接

5.1 笛卡尔积

多表连接查询时，不添加多表之间的关联条件，此时的查询属于笛卡尔积，是不能使用的（禁止笛卡尔积出现！！）

5.2 内连接查询

  • 特点
    通过指定的条件，匹配俩张表的数据，匹配上才显示

  • 隐式内连接

      select 字段名... from 表1,表2 where 表1与表2连接条件;

  • 显式内连接

      select 字段名... from 表1 [inner] join 表2 on 表1与表2连接条件

5.3 外连接查询

  • 左外连接

      	select 字段名... from 左表 left join 右表 on 连接条件
  	特点：
  	以左表为基准，匹配右表的数据，能匹配上都显示，匹配不上，左表的数据正常显示，右表字段都显示为null

  • 右外连

      	select 字段名... from 左表 right join 右表 on 连接条件
  	特点：
  	以右表为基准，匹配左表的数据，能匹配上都显示，匹配不上，右表的数据正常显示，左表字段都显示为null

5.4 三种查询方式的总结

在这里插入图片描述

6、子查询

  • 概念
    一条select查询的结果作为另一条select语句的一部分
  • 特点
    1. 子查询必须放在小括号中
    2. 子查询一般作为父查询的查询条件使用
  • 子查询常见分类
    1. where型子查询：将子查询的结果，作为父查询的比较条件
    2. from型子查询：将子查询的结果，作为一张表，提供给父层查询使用
    3. exists型子查询：子查询结果是单列多行，经常会作为父查询 IN 函数的条件使用

7、数据库设计三范式

  • 概念
    三范式是指数据库设计的 一个规则
  • 作用
    为了创建 冗余较小，结构合理的数据库
  • 范式
    设计数据库的要求/规范

7.1 第一范式(1NF)

满足最低要求的范式

  • 要求
    列具有原子性，设计列要做到不可拆分
    (比如：不能把’中国北京海淀区’当作一个字段值，需要拆分为3个字段：国家、城市、区/县)

7.2 第二范式(2NF)

满足第一范式的基础之上，进一步满足更多的范式

  • 要求
    1. 一张表只能描述一件事情
      (比如：不能把学生信息和课程信息都放在一张表里)

7.3 第三范式 (3NF)

满足第二范式的基础之上，进一步满足更多的范式

  • 要求
    消除传递依赖，表中的信息如果能够推导出来，就不要设计一个字段单独记录(空间最省原则，时间换空间)
    (比如：有单价和数量，就不需要有总价字段，使用时直接计算，可节省数据库占用空间)

8、数据库反三范式

  • 概念
    通过增加冗余或者重复数据，提高数据库读性能
    该方式会浪费存储空间，节省查询空间 （空间换时间）
  • 冗余字段
    某一个字段属于一张表，但是又在其他表中出现
    (比如：用户表包括姓名字段，订单表中除了订单信息字段外，也包括了用户姓名字段，当订单数据量较大时，会比join用户表的方式查询效率高，只是多占用了一些数据库的空间)

9、数据库设计总结

  1. 尽量根据三范式的规则设计数据库
  2. 可以合理的假如冗余字段，减少join的操作，让数据库执行更快

五、MySQL索引、存储过程、触发器

1、索引

1.1 什么是索引

数据库中对字段建立索引，可以大大提高 查询速度

1.2 常见索引分类

索引名称说明
主键索引(primary key)主键是一种唯一性索引，每个表只能有一个主键，用于标识数据库表的每一条记录
唯一索引(unique)唯一索引指，索引列的所有值都只能出现一次，必须唯一
普通索引(index)最常见的索引，作用是加快对数据的访问速度
表对应的索引被保存在一个索引文件中，如果对数据进行增删改操作，那MySQL就需要对索引进行更新

1.3 索引创建与删除

索引创建

  • 主键索引(上文约束部分有提到)

      1. 创建表时添加
  2. 表建完后添加

  • 唯一索引

      1. 创建表时添加
  	1.1 UNIQUE INDEX 索引名(字段名)  #唯一索引
  	1.2  INDEX 索引名(字段名1,字段名2)  #普通索引
  2. 表建完后添加
  	2.1 修改表结构添加(alter table 表名 add unique(字段名))
  	2.2 create语句添加(create unique index 索引名 on 表名(字段名))

  • 普通索引

      1. create语句添加(create index 索引名 on 表名(字段名[长度]))
  2. 修改表结构添加(alter table 表名 add index 索引名(字段名))

索引删除

	alter table 表名 drop index 索引名;

1.4 索引总结

  • 创建索引的原则
    优先选择给 经常出现在查询条件 / 排序 / 分组 后面的字段创建索引
  • 索引优点
    1. 大大提高查询速度
    2. 减少查询中排序、分组时间
    3. 通过创建唯一索引可以保证数据的唯一性
  • 索引缺点
    1. 创建和维护索引需要时间，数据量越大，时间越久(所以一般在数据量很大之前，应该创建好索引)
    2. 表中数据进行增删改操作时，索引也需要维护，降低了数据维护的速度
    3. 索引文件需要占用磁盘空间

2、视图

2.1 视图介绍和创建

  • 概念
    视图是由查询结果形成的一张虚拟的表

  • 视图作用
    如果某个查询的结果使用的比较频繁，而且查询语法比较复杂，此时可以根据该查询创建一个视图，方便查询

  • 视图语法

      create view 视图名[字段列表] as select查询语句;

  • 示例

      CREATE VIEW v_depart_emp AS 
  SELECT * FROM department d LEFT JOIN employee e ON d.`id` = e.`dept_id`;

  • 注意事项
    视图主要用于查询数据，不用于DML操作

2.2 视图与表的区别

  1. 视图建立在表的基础之上
  2. 视图主要用于简化查询，不要用于增删改操作
  3. 删除视图，表不受影响；删除表，视图则无法使用

3、存储过程（了解）

3.1 存储过程介绍

  • 概念
    • MySQL5.0版本开始支持存储过程
    • 存储过程(Sstored Procudure)是一种在数据库中存储复杂程序，以便外部对象调用的一种数据库对象。
    • 存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可以根据指定存储过程名称并给参数来调用执行。
    • 换句话说：存储过程就是很多SQL的合并，中间加入了一些逻辑控制
  • 优缺点
    • 优点
      1. 存储过程一旦调试完成，便可以稳定运行（业务逻辑相对稳定，没有变化的情况下）
      2. 存储过程减少业务系统与数据库的交互，降低耦合，数据库交互更加快捷（应用服务器与数据库服务器不再同一个地区）
    • 缺点
      1. 在互联网行业中大量使用MySQL，但是MySQL的存储过程与Oracle相比较弱，所以较少使用，另外互联网行业需求变化较快也降低了MySQL存储过程的使用
      2. 尽量在简单的逻辑中使用，存储过程移植十分困难；数据库集群环境下， 保证各个库之间的存储过程变更一致也十分困难
      3. 阿里的代码规范中也明确提出禁止使用存储过程(存储过程难以调试和扩展，更没有移植性)，存储过程维护起来的确麻烦

3.2 存储过程创建和调用方式

  • 创建的语法格式

      delimiter $$    #声明存储过程的结束符号，自定义
  create procedure 存储过程名称(IN 参数名 参数类型, OUT 参数名 数据类型)   #声明存储过程及参数
  begin	#存储过程开始
  	#要执行的SQL语句	 
  end $$		#存储过程结束

  • 调用的语法格式
    call 存储过程名称(实参)

  • 变量的赋值
    MySQL变量赋值参考资料

      set @变量名 = 值;

  • OUT输出参数

      OUT 变量名  数据类型

  • 示例

      DELIMITER $$
  CREATE PROCEDURE proc_test(IN id INT,OUT O_INFO VARCHAR(100))
  BEGIN
  	SET @O_INFO = CONCAT('id = ',id); #MySQL中拼接字符串
  	SELECT @O_INFO;
  END $$

4、触发器

4.1 触发器介绍

  • 概念
    触发器（trigger），是MySQL提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程。它的执行不是由程序调用，也不是手工启动，而是事件触发。比如对一个表进行增删改操作时，就会激活 触发器执行

4.2 触发器创建及使用

  • 创建的四要素

    1. 监视目标 (table)
    2. 监视事件 (insert / delete / update)
    3. 触发时间 (before/after)
    4. 触发事件 (insert / delete / update)

  • 创建触发器

      delimiter $$
  create trigger 触发器名
  before/after  insert/delete/update
  on 表名
  for each row
  begin
  	#触发的事件
  	insert/delete update 表名 [set 字段名 = 字段值] where 条件表达式;
  	#无需显式commit
  end $$

5、数据库备份和还原

5.1 数据库备份和还原（SQLyog方式）

数据库备份

  1. 选中要备份的数据库，右键 备份导出 ->备份数据库
    在这里插入图片描述
  2. 选择导出文件的位置在这里插入图片描述

数据库还原

  1. 右键选择 SQL执行脚本
    在这里插入图片描述
  2. 选择备份的数据库SQL脚本
    在这里插入图片描述
  3. 执行完成，刷新数据库即可
    在这里插入图片描述

5.2 数据库备份和还原（命令行方式）

数据库备份

  • 语法格式

      mysqldump -u用户名 -p密码 数据库名 > 文件路径

  • 注意

      该方式导出的脚本没有创建数据库的语句，恢复时需要先创建数据库

  • 备份步骤

  1. 进入mysql安装路径的bin目录下
    在这里插入图片描述
  2. 在文件路径那里输入cmd，回车
    在这里插入图片描述
  3. 输入导出数据库对象的命令
    在这里插入图片描述

数据库还原

  1. 创建空数据库
    在这里插入图片描述

  2. 导入备份数据库资源

     source 备份脚本路径

在这里插入图片描述


六、JDBC

1、JDBC概述

1.1 客户端操作数据库的方式

  1. 第三方客户端访问
  2. 命令行访问
  3. Java程序访问（JDBC）

1.2 什么是JDBC

  • JDBC（Java Data Base Connectivity）是Java访问数据库的标准规，是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问。
  • JDBC由一组Java编写的类和接口组成

1.3 JDBC原理

  • JDBC是接口，驱动是接口的实现，没有驱动无法完成数据库的连接，从而不能操作数据库。
  • 每个数据库厂商都需要提供自己的驱动，用来连接自己公司的数据库，也就是驱动一般都是由数据库生产厂商提供
    在这里插入图片描述

2、JDBC开发

2.1 JDBC开发步骤

数据准备

  1. SQLyog在db4数据库中创建一张表jdbc_user
  2. 表中插入几条数据

MySQL驱动包

将MySQL的驱动包下载到本地myJar文件夹，用于存放当前项目所需的所有jar包
官方下载地址

IDEA添加驱动

  1. IDEA新建一个Java项目
  2. 配置jar包的位置（可以指定具体的jar包，也可以使用jar所在的目录）
    在这里插入图片描述

JDBC开发

  1. 注册驱动（可以省略-JDBC3开始）
Class.forName("com.mysql.cj.jdbc.Driver"); //MySQL8.0版本
Class.forName("com.mysql.jdbc.Driver"); //MySQL5.7版本
  1. 获取连接Connection对象
String url = "jdbc:mysql://localhost:3306/db4?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true";
Connection connection = DriverManager.getConnection(url, "root", "123456");
  1. 获取语句执行对象
Statement statement = connection.createStatement();
  1. 执行SQL / 处理结果集(只有查询时使用)
//执行DDL语句
String sql = "create table jdbc_test(id int,name varchar(20))";//创建表
int i = statement.executeUpdate(sql); //返回影响的行数

//获取查询结果集并处理
String sql = "select * from jdbc_user";
ResultSet resultSet = statement.executeQuery(sql); //返回查询结果集
while(resultSet.next()){
    System.out.print(" id = " + resultSet.getInt(1)); //获取第一列的值
    System.out.print(" userid = " + resultSet.getString("userid") );//获取列名为userid的值
    System.out.println(" 姓名 = " + resultSet.getString("name") );
}

  1. 关闭资源

     释放资源原则：先开后关
 ResultSet -> Statement -> Connection

try{

}catch(){

}finally{
	//关闭结果集
    try {
        resultSet.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    //关闭语句执行对象
    try {
        statement.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    //关闭连接
    try {
        connection.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

JDBC开发总结

  1. 获取驱动(可以省略)
  2. 获取连接
  3. 获取Statement对象
  4. 执行SQL/处理结果集(只有查询时需要处理)
  5. 释放资源

2.2 提取JDBCUtils工具类及测试

参考上方JDBC开发步骤总结，显然有些步骤是基本不变的写法，为了减少代码的冗余，提高代码可读性，可以将一些会重复利用的代码提取到一个单独的工具类中。

  • 工具类包含内容
    1. 几个字符串可以定义为常量：用户名、密码、url、驱动类
    2. 获取数据库的连接
    3. 关闭所有打开的资源

JDBCUtils工具类开发

import java.sql.*;

/**
 * @program: proj_homework_2_1
 * @description:
 * @author: 
 * @create: 2020-12-04 11:00
 **/
public class JDBCUtils {
    private static final String DRIVERNAME = "com.mysql.cj.jdbc.Driver";
    private static final String URL = "jdbc:mysql://localhost:3306/db4?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true";
    private String userName;
    private String password;

    static {
        try {
            //注册驱动
            Class.forName(DRIVERNAME);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public JDBCUtils() {
    }

    public JDBCUtils(String userName, String password) {
        this.userName = userName;
        this.password = password;
    }

    /**
     * 获取默认的URL的连接
     * @return
     * @throws SQLException
     */
    public Connection getConnection(){
        try {
            return DriverManager.getConnection(URL,userName,password);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 获取指定URL的连接
     * @param url
     * @return
     * @throws SQLException
     */
    public Connection getConnection(String url) {
        try {
            return DriverManager.getConnection(url,userName,password);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 无结果集的释放资源
     */
    public void close(Connection conn, Statement statement){
        if(statement != null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(null != conn){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 有结果集的释放资源
     */
    public void close(Connection conn, Statement statement, ResultSet resultSet){
        if(null != resultSet){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        close(conn,statement);
    }
}

JDBCUtils工具类测试

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;

/**
 * @program: proj_homework_2_1
 * @description:
 * @author: 
 * @create: 2020-12-04 11:17
 **/
public class JDBCUtilTest {
    private static JDBCUtils jdbcUtils = new JDBCUtils("root","123456");
    @Test
    public void testDML() throws SQLException{
        //获取连接
        Connection conn = jdbcUtils.getConnection();
        if(null != conn){
            //获取语句执行对象
            Statement statement = conn.createStatement();

            //执行SQL
            String sql = "insert into jdbc_user(userid,name,password)values('programmer','程序员','123123')";
            int row = statement.executeUpdate(sql);
            System.out.println(row > 0 ? "插入成功！" : "插入失败！");

            //释放资源
            jdbcUtils.close(conn,statement);
        }
    }

    @Test
    public void testDQL() throws SQLException {
        //获取连接
        Connection conn = jdbcUtils.getConnection();

        //获取语句执行对象
        Statement statement = conn.createStatement();

        //获取并处理数据集
        String sql = "select * from jdbc_user where logins is not null";
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()){
            System.out.print(resultSet.getInt("id"));
            System.out.print(" " + resultSet.getString("userid"));
            System.out.print(" " + resultSet.getString("name"));
            System.out.print(" " + resultSet.getString("password"));
            System.out.print(" " + resultSet.getString("logins"));
            System.out.println(" " + resultSet.getDate("birth"));
        }

        //释放资源
        jdbcUtils.close(conn,statement,resultSet);
    }
}

在这里插入图片描述

3、SQL注入问题

  • 问题描述
    对字符串进行非法拼接导致查询出错的情况。比如：

    • 用户登录时，正确情况下输入用户名：admin 密码：123123，完成登录。

        对应的查询SQL：
  select count(1) from jdbc_user 
  where userid='admin' and password = '123123';

    • 如果输入用户名：admin’ or ‘1’='1 密码：123。

       对应的查询SQL：
 select count(1) from jdbc_user 
 where userid='admin' or '1'='1' and password = '000000';

    • 结果是：用户输入了错误的信息，也能正常登录系统，这就是SQL注入导致的问题。可以用下方代码验证：

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

/**
 * @program: proj_homework_2_1
 * @description:
 * @author: 
 * @create: 2020-12-04 14:00
 **/
public class LoginTest {
    private static JDBCUtils jdbcUtils = new JDBCUtils("root","123456");

    /**
     * 测试登录中遇到的SQL注入问题
     */
    public static void main(String[] args) throws SQLException {
        //获取连接
        Connection connection = jdbcUtils.getConnection();

        //获取Statement对象
        Statement statement = connection.createStatement();

        //获取结果集并处理
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名：");
        String userid = sc.nextLine();
        System.out.println("请输入密码：");
        String password = sc.next();
        int count = 0; //查询到的用户数
        String sql = "select count(1) as num from jdbc_user where userid = '" + userid + "' and password = '" + password + "'";
        System.out.println("sql = " + sql);
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()){
            count = resultSet.getInt("num");
        }
        System.out.println(count > 0 ? "登录成功" : "登录失败！");

        //释放资源
        jdbcUtils.close(connection,statement,resultSet);
    }
}

在这里插入图片描述

  • 根本原因
    后台查询采用的是字符串拼接的方式，这种方式就导致用户可以随意输入任何字符串进行登录，而导致对查询结果无法准确控制

4、预处理对象（可以解决SQL注入）

  • 预处理对象PreparedStatement优点：
    • 提供预编译功能，提高查询效率
    • 通过占位符的方式设置参数，可以防止SQL注入问题
	private static JDBCUtils jdbcUtils = new JDBCUtils("root","123456");
	/**
     * 使用预处理对象解决SQL注入问题
     * @throws SQLException
     */
    public static void testLogin2() throws SQLException {
        //获取连接
        Connection connection = jdbcUtils.getConnection();

        //获取PreparedStatement对象
        String sql = "select count(1) as num from jdbc_user where userid = ? and password = ?";
        PreparedStatement statement = connection.prepareStatement(sql);

        //设置参数并执行SQL
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名：");
        String userid = sc.nextLine();
        System.out.println("请输入密码：");
        String password = sc.next();
        int count = 0; //查询到的用户数
        statement.setString(1,userid);  //设置参数
        statement.setString(2,password);
        ResultSet resultSet = statement.executeQuery(); //执行SQL
        while (resultSet.next()){
            count = resultSet.getInt("num");
        }
        System.out.println(count > 0 ? "登录成功" : "登录失败！");



        //释放资源
        jdbcUtils.close(connection,statement,resultSet);
    }

在这里插入图片描述

  • PreparedStatement对象与Statement对象区别
    • Statement对象每执行一条SQL就会发给数据库，数据库先编译再执行，如果要插入多条记录，就会需要编译多次。
    • PreparedStatement对象会将SQL发给数据进行预编译，然后将预编译SQL保存起来，这样就只需要编译一次。
      在这里插入图片描述

5、JDBC控制事务

java.sql.Connection接口的方法实现事务控制

方法功能
void setAutoCommit​(boolean autoCommit)参数为true或false。
为false表示关闭自动提交事务，相当于开启事务
void commit()提交事务
void rollback()回滚事务
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @program: proj_homework_2_1
 * @description:
 * @author: 
 * @create: 2020-12-04 15:40
 **/
public class JDBCControlTransactionTest {
    private static JDBCUtils jdbcUtils = new JDBCUtils("root","123456");

    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            //获取连接
            connection = jdbcUtils.getConnection();

            //开启事务
            connection.setAutoCommit(false);

            //获取预编译对象
            String sql = "insert into jdbc_user(userid,name,password)values(?,?,?)";
            ps = connection.prepareStatement(sql);

            //执行SQL
            ps.setString(1,"sunqiang3");
            ps.setString(2,"孙强3");
            ps.setString(3,"123123");
            ps.executeUpdate();

            //System.out.println(1/0); //测试事务是否会回滚

            //提交事务（有异常就回滚事务）
            connection.commit();
        } catch (SQLException e) {
            //回滚事务
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            //释放资源
            jdbcUtils.close(connection,ps);
        }
    }
}

七、数据库连接池、DBUtils

1、数据库连接池

1.1 数据库连接池介绍

在这里插入图片描述

  • 如何使用连接池
    Java为数据库连接池提供了公共的接口：javax.sql.DataSource，各个厂商需要让自己的连接池实现这个接口，这样应用程序可以方便的切换不同厂商的连接池。
  • 常见的连接池
    1. DBCP连接池
    2. C3P0连接池
    3. Druid连接池

1.2 DBCP连接池

DBCP是一个开源的连接池，是Apache成员之一，在企业开发中比较常见，是tomcat内置的连接池。

  1. 下载Jar包并添加依赖

（1）下载jar包
commons-pool包下载地址
commons-dbcp包下载地址

（2）导入本地myJar文件夹
在这里插入图片描述

（3）IDEA添加myJar文件夹依赖（后续有新的jar需要依赖，只需要下载后放入该文件中，无需每次下载后在idea中添加一次依赖）
在这里插入图片描述

  1. DBCP连接池工具类编写
import org.apache.commons.dbcp.BasicDataSource;

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

/**
 * @program: proj_homework_2_1
 * @description:
 * @author: 
 * @create: 2020-12-04 16:57
 **/
public class DBCPUtils {
    //1、定义常量，保存数据库连接相关信息
    public static final String DRIVERNAME = "com.mysql.cj.jdbc.Driver";
    public static final String URL = "jdbc:mysql://localhost:3306/db4?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true";
    private String userName;
    private String password;

    //2、创建连接池对象（由DBCP提供的实现类）
    public BasicDataSource database = new BasicDataSource();

    //3、使用构造代码块进行配置
    {
        //3.1 给连接池对象配置驱动，连接地址
        database.setDriverClassName(DRIVERNAME);
        database.setUrl(URL);
    }

    public DBCPUtils() {
    }

    //3.2 给连接池配置用户名和密码
    public DBCPUtils(String userName, String password) {
        this.userName = userName;
        this.password = password;
        database.setUsername(userName);
        database.setPassword(password);
    }

    //4、提供获取连接的方法
    public Connection getConnection() throws SQLException {
        return database.getConnection();
    }
    //重载方法，用于访问非默认数据库时，获取连接指定URL的数据库的Connection
    public Connection getConnection(String url) throws SQLException {
        database.setUrl(url);
        return database.getConnection();
    }


    //5、提供释放资源的方法
    public void close(Connection conn, Statement statement){
        if(statement != null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(null != conn){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    //重载方法，用于执行查询后的资源释放
    public void close(Connection conn, Statement statement, ResultSet resultSet){
        if(null != resultSet){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        close(conn,statement);
    }
}
  1. DBCP连接池工具类测试
import org.junit.Test;

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

/**
 * @program: proj_homework_2_1
 * @description:
 * @author: 
 * @create: 2020-12-04 17:16
 **/
public class DBCPUtilsTest {
    private static DBCPUtils dbcpUtils = new DBCPUtils("root","123456");

    @Test
    public void testDML() throws SQLException {
        //获取连接
        Connection connection = dbcpUtils.getConnection();

        //预编译SQL
        String sql = "select name from jdbc_user where userid like concat('%',?,'%')";
        PreparedStatement ps = connection.prepareStatement(sql);

        //获取结果集并处理
        ps.setString(1,"sun");
        ResultSet resultSet = ps.executeQuery();
        while (resultSet.next()){
            System.out.println(resultSet.getString("name"));
        }

        //释放资源
        dbcpUtils.close(connection,ps,resultSet);
    }
}
  1. DBCP常见的配置项介绍
属性描述
driverClassName数据库驱动名称
url数据库地址
username用户名
password密码
maxActive最大连接数量
maxIdle最大空闲连接
minIdle最小空闲连接
initialSize初始化连接

1.3 C3P0连接池

C3P0是一个开源的JDBC连接池，支持JDBC3规范和JDBC2的标准扩展。目前使用的开源项目有Hibernate、Spring等。C3P0拥有比DBCP更丰富的配置属性

  1. 下载添加Jar包依赖

（1）下载jar包并移入myJar文件夹
mchange-commons-java jar包下载地址
c3p0 jar包下载

（2）导入配置文件c3p0-config.xml

  • 此文件为官方提供的配置文件，文件名不可修改。
<c3p0-config>
  
  <!--默认配置-->
    <default-config>  
	
		<!-- initialPoolSize：初始化时获取三个连接，取值应在minPoolSize与maxPoolSize之间 --> 
        <property name="initialPoolSize">3</property>  
		
		<!-- maxIdleTime：最大空闲时间,60秒内未使用则连接被丢弃。若为0则永不丢弃。-->
        <property name="maxIdleTime">60</property>  
		
		<!-- maxPoolSize：连接池中保留的最大连接数 -->
        <property name="maxPoolSize">100</property>  
		<!-- minPoolSize: 连接池中保留的最小连接数 -->
        <property name="minPoolSize">10</property>  
		
    </default-config>  
  
   <!--配置连接池mysql-->
    <!-- 自定义数据库连接池信息 -->
    <named-config name="mysql">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <!-- <property name="jdbcUrl">jdbc:mysql://localhost:3306/db4?characterEncoding=UTF-8</property> mysql5使用 -->
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/db4?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true</property>
        <property name="user">root</property>
        <property name="password">123456</property>
        <property name="initialPoolSize">10</property>
        <property name="maxIdleTime">30</property>
        <property name="maxPoolSize">100</property>
        <property name="minPoolSize">10</property>
    </named-config>
    <!--配置连接池2,可以配置多个-->

</c3p0-config>
  • 存放位置
    1. 放在src目录下。
    2. 放在自定义的资源文件夹下。
      在idea项目右键新建一个文件夹resources，再将该文件夹设置为Resources Root文件，然后将该文件放在该文件夹下
  1. C3P0连接池工具类编写
import com.mchange.v2.c3p0.ComboPooledDataSource;

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

/**
 * @program: proj_homework_2_1
 * @description:
 * @author: 
 * @create: 2020-12-05 13:58
 **/
public class C3P0Utils {
    /**
     * 获取连接
     * @return
     * @throws SQLException
     */
    public static Connection getConnection(String poolName) throws SQLException {
        //使用默认的数据库连接池信息
        //ComboPooledDataSource cpds = new ComboPooledDataSource();
        //使用指定的数据库连接池信息
        return new ComboPooledDataSource(poolName).getConnection();
    }

    //释放资源
    public static void close(Connection conn, Statement statement){
        if(statement != null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(null != conn){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //重载方法，用于执行查询后的资源释放
    public static void close(Connection conn, Statement statement, ResultSet resultSet){
        if(null != resultSet){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        close(conn,statement);
    }
}
  1. C3P0连接池工具类测试
import org.junit.Test;

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

/**
 * @program: proj_homework_2_1
 * @description:
 * @author: 
 * @create: 2020-12-05 14:07
 **/
public class C3P0UtilsTest {

    @Test
    public void testC3P0Utils() throws SQLException {
        //获取连接
        Connection connection = C3P0Utils.getConnection("mysql");

        //获取预编译语句执行对象
        String sql = "select userid,name from jdbc_user where userid like concat('%',?,'%')";
        PreparedStatement ps = connection.prepareStatement(sql);

        //获取结果集并处理
        ps.setString(1,"s");
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            System.out.print(rs.getString("userid") + " ");
            System.out.println(rs.getString("name"));
        }

        //释放资源
        C3P0Utils.close(connection,ps,rs);
    }
}

在这里插入图片描述

  1. C3P0常见配置
分类属性描述
必须项user用户名
-password密码
-driverClass数据库驱动
-jdbcUrl连接地址
基本配置initialPoolSize连接池初始化时创建的连接数
默认3
-maxPoolSize连接池中拥有的最大连接数
默认15
-minPoolSize连接池保持的最小连接数
默认10
-maxIdleTime连接的最大空闲时间。如果超过这个时间，某个数据库连接还没有被使用，则会断开这个连接，如果为0，则永远不会断开连接。
默认0

1.4 Druid连接池

  • Druid(德鲁伊)是阿里巴巴开发的号称为监控而生的开源数据库连接池
  • Druid是目前最好的数据库连接池，在功能、性能、扩展性方面，都超过其他数据库连接池。
  • 同时加入了日志监控，可以很好的监控DB连接池和SQL的执行情况
  1. 下载添加Jar包依赖

（1）下载jar包并移入myJar文件夹
druid jar包下载地址

（2）导入配置文件

  • properties形式的配置文件
  • 可以叫任意名称
  • 可以放任意目录下，为统一管理，都放在resources目录下
# druid.properties文件的配置
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/db4?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
username=root
password=123456
# 初始化连接数量
initialSize=5
# 最大活跃连接数
maxActive=10
# 最大超时时间
maxWait=3000
  1. Druid连接池工具类编写
import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * @program: proj_homework_2_1
 * @description:
 * @author: 
 * @create: 2020-12-05 15:03
 **/
public class DruidUtils {

    //1、定义成员变量
    private static DataSource ds;

    //2、给成员变量赋值
    static {
        try {
            //创建属性对象
            Properties properties = new Properties();

            //Druid不会自动加载配置文件，需要指定配置文件,返回配置文件的输入流
            InputStream inputStream = DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties");

            //将配置文件内容读入Properties对象中
            properties.load(inputStream);

            //Druid数据源工厂创建数据源，参数为Properties对象
            ds = DruidDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接
     * @return
     */
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    //释放资源
    public static void close(Connection conn, Statement statement){
        if(statement != null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(null != conn){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //重载方法，用于执行查询后的资源释放
    public static void close(Connection conn, Statement statement, ResultSet resultSet){
        if(null != resultSet){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        close(conn,statement);
    }
}
  1. Druid连接池工具类测试
import org.junit.Test;

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

/**
 * @program: proj_homework_2_1
 * @description:
 * @author: 
 * @create: 2020-12-05 15:25
 **/
public class DruidUtilTest {

    @Test
    public void testDruidUtils() throws SQLException {
        //获取连接
        Connection connection = DruidUtils.getConnection();

        //获取语句执行对象
        Statement statement = connection.createStatement();

        //获取结果集并处理
        ResultSet rs = statement.executeQuery("select * from jdbc_user");
        while (rs.next()){
            System.out.print(rs.getString("userid") + " ");
            System.out.print(rs.getString("name") + " ");
            System.out.print(rs.getString("password") + " ");
            System.out.print(rs.getString("logins") + " ");
            System.out.print(rs.getString("birth") + " ");
            System.out.println(rs.getString("orgid") + " ");
        }

        //释放资源
        DruidUtils.close(connection,statement,rs);
    }
}

在这里插入图片描述

2、DBUtils工具类（熟练）

2.1 DBUtils简介

  • 使用JDBC我们发现冗余代码太多了，为了简化开发，选择使用DBUtils工具类。
  • Common DBUtils是Apache组织提供的一个对JDBC进行简单封装的开源工具类库，使用他可以简化JDBC应用程序的开发，同时也不会影响程序性能
  • 使用方式
    DBUtils就是JDBC的简化开发工具包，需要下载commons-dbutils-1.6.jar包
    commons-dbutils jar包下载地址

2.2 DBUtils核心功能介绍

  1. QueryRunner中提供对SQL语句操作的API
  2. ResultSetHandler接口，用于定义select操作后，怎样封装结果集
  3. DBUtils类，定义了关闭资源与事务处理相关方法

2.3 相关知识

  • 表和类的关系
    一张表相当于一个类；
    一条记录相当于一个类的实例化对象
  • JavaBea组件
    JavaBean就是一个类，开发中通常用于封装数据。有以下特点：
    1. 需要实现序列化接口Serializable
    2. 提供私有字段(成员变量)
    3. 提供get和set方法
    4. 提供空参构造器

2.4 DBUtils完成CRUD

QueryRunner核心类

  • 构造方法
    • QueryRunner() --手动模式创建对象
    • QueryRunner(DataSource ds) --自动模式创建对象，提供数据源(连接池)，DBUtils底层自动维护连接connecttion
  • 常用方法
    • update(Connection conn, String sql, Object… params)，用来完成增、删、改操作
    • query(Connection conn, String sql, ResultSetHandler rsh, Object… params)，用来完成查询操作

QueryRunner实现增删改操作

import com.lagou.test.pool.DruidUtils;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.junit.Test;

import java.sql.Array;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @program: proj_homework_2_1
 * @description: 实现增删改操作
 * @author: 
 * @create: 2020-12-07 15:12
 **/
public class DBUtilsDemo02 {
    /**
     * 测试插入操作
     * @throws SQLException
     */
    @Test
    public void testInsert() throws SQLException {
        //1、创建QueryRunner对象，手动模式创建
        QueryRunner qr = new QueryRunner();

        //2、编写占位符sql
        String sql = "insert into jdbc_user(userid,name,password)values(?,?,?)";

        //3、设置占位符内容
        Object[] list = {"liqi2","李琦2","123123"};

        //4、执行update操作
        Connection connection = DruidUtils.getConnection();
        qr.update(connection,sql,list);

        //5、释放资源
        DbUtils.closeQuietly(connection);
    }

    /**
     * 测试更新操作
     * @throws SQLException
     */
    @Test
    public void testUpdate() throws SQLException {
        //1、创建QueryRunner，自动模式
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

        //2、编写占位SQL
        String sql = "update jdbc_user set name = ? where userid = ?";

        //3、编写占位符内容
        Object[] params = {"李琦2号","liqi2"};

        //4、执行修改操作，自动模式下不需要传入connection，也不需要手动释放资源
        qr.update(sql,params);
    }

    @Test
    public void testDelete() throws SQLException {
        //1、创建QueryRunner对象，自动模式
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

        //2、编写占位符SQL
        String sql = "delete from jdbc_user where userid = ?";

        //3、编写占位符内容，只有一个参数时不需要创建数组
        //Object[] params = {"liqi2"};

        //4、执行删除操作，自动模式不需要传入connection,也不需要手动释放资源
        qr.update(sql,"liqi2");
    }
}

QueryRunner实现查询操作

  • ResultSetHandler接口简介
    • ResultSetHandler可以对查询出来的结果ResultSet结果集进行处理，满足一些业务的需求
  • QueryRunner的查询方法
    • query方法的返回结果都是泛型，具体的返回值类型，会根据结果集的处理方法发生变化
方法说明
query(String sql, handler, Object[] params)自动模式下创建QueryRunner，执行查询
query(Connection conn, handler, Object[] params)手动模式下常见QueryRunner，执行查询
  • ResultSetHandler结果集处理类
方法说明
ArrayHandler将结果集中每一条记录封装到一个Oject[]数组中，数组中的每一个元素就是这条记录中每一个字段的值
ArrayListHandler将结果集中每一条记录都封装到一个Object[]数组中，将这些数组再封装到List集合中
BeanHandler将结果集中第一条记录封装到一个指定的JavaBean中
BeanListHandler将结果集中每一条记录封装到指定的JavaBean中，再将这些JavaBean封装到一个List集合中
ColumnListHandler将结果集中指定列的字段值，封装到一个List集合中
KeyedHanlder将结果集中每一条记录封装到Map<String,Object>中，再将这个map集合作为另一个Map的value，另一个集合的key是指定的字段的值
MapHandler将结果集第一条记录封装到Map<String,Object>中，key是字段名称，value是字段值
MapListHandler将结果集每一条记录封装到Map<String,Object>中，key是字段名称，value是字段值，再将这些map集合封装到List集合中
ScalarHandler用于封装单个数据。比如select count(1) from user的查询操作
import com.lagou.test.pool.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

/**
 * @program: proj_homework_2_1
 * @description:
 * @author: 
 * @create: 2020-12-07 16:29
 **/
public class DBUtilsDemo03 {
    /**
     * 查询指定用户
     * @throws SQLException
     */
    @Test
    public void testArrayHandler() throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

        String sql = "select * from jdbc_user where userid = 'liqi'";

        Object[] obj = qr.query(sql, new ArrayHandler());

        System.out.println(Arrays.toString(obj));
    }

    /**
     * 查询userid包含q的所有用户信息
     */
    @Test
    public void testArrayListHandler() throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

        String sql = "select * from jdbc_user where userid like concat('%',?,'%')";

        List<Object[]> objList = qr.query(sql, new ArrayListHandler(),"q");

        for (Object[] obj : objList) {
            System.out.println(Arrays.toString(obj));
        }
    }


    /**
     * 将指定用户封装到Map集合中
     * @throws SQLException
     */
    @Test
    public void testMapHandler() throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

        String sql = "select * from jdbc_user where userid = ?";

        Map<String, Object> objMap = qr.query(sql, new MapHandler(), "liqi");

        for (String key:objMap.keySet()) {
            System.out.println(key + " = " + objMap.get(key));
        }
    }

    /**
     * 查询id>3的用户数量
     * @throws SQLException
     */
    @Test
    public void testScalarHandler() throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

        String sql = "select count(1) from jdbc_user where id > 3";

        Long count = qr.query(sql, new ScalarHandler<>());

        System.out.println(count);
    }
}

3、数据库批处理

3.1 什么是批处理

  • 批处理(batch)操作数据库
    • 批处理指的是一次操作中，执行多条SQL语句，批处理相比一次一次执行效率高很多
    • 当向数据库添加大量数据时，需要用到批处理
  • 举例：送货员的工作
    • 未使用批处理时，送货员一件一件送到送给客户
    • 使用批处理，送货员可以将所有货物用车带到发放处派发给客户

3.2 实现批处理

Statement和PreparedStatement都支持批处理操作，这里介绍PreparedStatement的批处理方式

  • 用到的方法
方法说明
void addBatch()将指定sql添加到Statement对象当前命令列表中
通过执行executeBatch()可以批量执行列表中的命令
int[] executeBatch()每次提交一批命令到数据库中执行，如果所有的命令都成功执行了，则返回一个数组。
该数组用来来说明每个命令影响的行数

  • MySQL批处理默认是关闭的，需要在url最后添加一个参数打开MySQL批处理

      &rewriteBatchStatements=true

import com.lagou.test.pool.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.junit.Test;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @program: proj_homework_2_1
 * @description:
 * @author: 
 * @create: 2020-12-07 18:03
 **/
public class TestBatch {
    /**
     * 批处理向表中添加10000条数据
     */
    @Test
    public void testBatchInsert() throws SQLException {
        //1、获取连接
        Connection connection = DruidUtils.getConnection();

        //2、获取预处理对象
        String sql = "insert into jdbc_user(userid,name,password,logins,orgid) values(?,?,?,?,?)";
        PreparedStatement ps = connection.prepareStatement(sql);

        //3、将10000条sql插入到批处理列表
        for (int i = 1; i <= 10000; i++) {
            ps.setString(1,"second_user" + i );
            ps.setString(2,"第二批用户" + i);
            ps.setString(3,"123123");
            ps.setString(4,"0");
            ps.setString(5,"1");
            //将sql添加到批处理列表
            ps.addBatch();
        }

        long beforeTime = System.currentTimeMillis();
        
        //4、统一执行批量操作
        ps.executeBatch();
        
        long afterTime = System.currentTimeMillis();
        System.out.println("批量插入10000条记录用时：" + (afterTime - beforeTime) + "毫秒");
        
        //5、释放资源
        DruidUtils.close(connection,ps);
    }
}

4、MySQL元数据

4.1 什么是元数据

  • 除了表之外的数据都是元数据，可分为三类：
    1. 查询结果信息：UPDATE / DELETE受影响的记录
    2. 数据库和数据表的信息：包含了数据库及数据表的结构信息
    3. MySQL服务器信息：包含了数据库服务器的当前状态，版本号等

4.2 常用命令

  • 元数据相关的命令介绍

    1. 查看服务器当前状态

       show status;

    2. 查看MySQL版本信息

       select version();

    3. 查询表中的详细信息

       show columns from jdbc_user;

    4. 显示数据表的详细索引信息

       show index from jdbc_user;

    5. 列出所有数据库

       show databases;

    6. 显示当前数据库的所有表

       show tables;

    7. 获取当前的数据库名

       select database();

4.3 使用JDBC获取元数据

通过JDBC也可以获取到元数据，比如数据库的相关信息。
或者当我们查询一个不熟悉的表时，我们可以通过获取元数据信息，了解表中的字段数量，字段名称，字段类型

  • 常用类介绍
    • DatabaseMetaData类：描述数据库的元数据对象
    • ResultSetMetaData类：描述结果集的元数据对象
  • 获取元数据对象的方法：getMetaData()
    • connection对象：调用getMetaData()方法获取的是DatabaseMetaData数据库元数据对象
    • PreparedStatement预处理对象：调用getMetaData()方法获取的是ResultSetMetaData结果集元数据对象
  • DatabaseMetaData类常用方法
方法说明
getURL()获取数据库的URL
getUserName()获取当前数据库的用户名
getDatabaseProductName()获取数据库的产品名称
getDatabaseProductVersion()获取数据库版本号
import com.lagou.test.pool.DruidUtils;
import org.junit.Test;

import java.sql.*;

/**
 * @program: proj_homework_2_1
 * @description:
 * @author: 
 * @create: 2020-12-07 20:18
 **/
public class TestMetaData {
    /**
     * JDBC获取数据库元数据信息
     * @throws SQLException
     */
    @Test
    public void testDatabaseMetaData() throws SQLException {
        //1、获取数据库连接
        Connection conn = DruidUtils.getConnection();

        //2、获取代表数据库的元数据对象
        DatabaseMetaData metaData = conn.getMetaData();

        //3、获取数据库相关的元数据信息
        System.out.println("数据库URL：" + metaData.getURL());
        System.out.println("当前用户：" + metaData.getUserName());
        System.out.println("数据库产品名" + metaData.getDatabaseProductName());
        System.out.println("数据库版本号：" + metaData.getDatabaseProductVersion());
        System.out.println("数据库驱动名：" + metaData.getDriverName());
        System.out.println("是否为只读数据库：" + (metaData.isReadOnly() == true ? "是" : "否"));

        //4、释放资源
        conn.close();
    }

    /**
     * 获取结果集元数据对象信息
     * @throws SQLException
     */
    @Test
    public void testResultSetMetaData() throws SQLException {
        //1、获取连接
        Connection connection = DruidUtils.getConnection();

        //2、获取预处理对象
        String sql = "select userid as '域账号',name as '姓名',password as '密码',logins as '登录次数',orgid as '部门编号' from jdbc_user";
        PreparedStatement ps = connection.prepareStatement(sql);

        //3、获取结果集元数据对象信息
        ResultSetMetaData metaData = ps.getMetaData();
        int columnCount = metaData.getColumnCount();
        for (int i = 1; i <= columnCount; i++) {
            System.out.print(" 字段名：" + metaData.getColumnName(i));
            System.out.print(" 字段类型名：" + metaData.getColumnTypeName(i));
            System.out.println(" 字段别名：" + metaData.getColumnLabel(i));
        }

        //4、释放资源
        DruidUtils.close(connection,ps);
    }
}

八、XML

1、XML基本介绍

1.1 概述

  • XML，全称为可扩展标记语言（Extensible Markup Language）
    • W3C在1998年2月发布了1.0版本，2004年2月发布了1.1版本，但因为1.1版本不能向下兼容1.0版本，所以1.1版本没有被使用。同时W3C在2004年2月又发布了1.0版本的第三版。
    • 所以现在要学的还是1.0版本
  • 特点
    • 可扩展性，标签都是自定义
    • 语法十分严格

1.2 XML作用

  1. 用于存储数据

     一般情况我们都会在数据库存储数据，但是如果希望数据的可移植性更强，可以把数据存储在XML中

  2. 作为配置文件（最多）

     作为各种技术框架的配置文件使用

  3. 网络中传输

     客户端可以使用XML格式向服务端发送数据，服务端收到XML格式数据进行解析

2、XML语法

2.1 XML文档声明格式

  • 文档声明必须以<?xml 开头
  • 必须以 ?> 结尾
  • 文档声明必须写在文件第一行

示例：

<?xml version="1.0" encoding="UTF-8" ?>

属性说明：

  • version: 指定XML文档版本，必须属性。
  • encoding：指定当前文档的编码。可选属性，默认是utf-8

2.2 元素

  • 元素(Element)：文档中最重要的组成部分

  • 元素的命名规则

    1. 不能使用空格、冒号
    2. xml标签名称区分大小写
    3. xml必须有且只有一个根元素

  • 元素体可以是文本，可以是一组标签

  • 也可以使用空元素（没有结束标签）

      <label/>

2.3 属性

  • 属性是元素的一部分，只能出现在元素的开始标签中
  • 属性值必须使用单引号/双引号包裹
  • 一个元素标签可以定义多个属性
<?xml version="1.0" encoding="utf-8" ?>
<jdbc_users>
    <jdbc_user id="1">
        <userid>sunqiang</userid>
        <name>孙强</name>
        <password>123123</password>
        <logins>0</logins>
        <orgid>1</orgid>
        <birth>1991-09-01</birth>
    </jdbc_user>
    
    <jdbc_user id="2">
        <userid>sunqiang2</userid>
        <name>孙强2</name>
        <password>123123</password>
        <logins>0</logins>
        <orgid>1</orgid>
        <birth>1996-09-16</birth>
    </jdbc_user>
</jdbc_users>

2.4 注释

参考：XML注释方法

3、XML约束

  • 在XML中，可以定义一个文档来约束一个XML文档的书写规范，这称为XML约束
  • 常见的XML约束
    • DTD
    • Schema
  • 作为程序员只需要掌握
    • 会阅读
    • 会引入
    • 不用自己编写
      在这里插入图片描述

3.1 DTD约束

DTD（Document Type Definition）,文档类型定义，用来约束XML文档。用于约束XML文档中元素的名称、子元素的名称和顺序、元素的属性等

  • 编写DTD
    • 开发中，开发人员不需要自己编写DTD约束文档
    • 通常情况都是通过框架提供的DTD约束文档，编写对应的XML文档，常见框架使用DTD约束有：Struts2、Hibernate等
      在这里插入图片描述
  • 引入DTD
    • 俩种方式

      1. 内部dtd：将约束规则定义在xml文档中

      2. 外部dtd：将约束规则定义在外部的dtd文件中

         本地：<!DOCTYPE 根标签名 SYSTEM "dtd文件的位置">
 网络：<!DOCTYPE 根表签名 PUBLIC "dtd文件名" "dtd文件的位置url">

3.2 Schema约束

  • 什么是Schema
    • Schema是新的XML文档约束，比DTD强大很多，是DTD的替代者
    • Schema本身也是XML文档，但Schema文档扩展名为xsd，而不是xml
    • Schema功能更强大，内置多种简单和复杂的数据类型
    • Schema支持命名空间（一个XML中可以引入多个约束文档）
      在这里插入图片描述
  • 引入Schema
    在这里插入图片描述

4、XML解析

4.1 解析概述

将数据存储在XML后，我们希望通过程序获取XML的内容。如果通过IO读取对的话，虽然可以完成，但是实现起来非常繁琐，且开发中会遇到不同的问题（只读，读写）。
人们为不同的内容提供不同的解析方式，并提交对应的解析器，方便开发人员解析XML

4.2 XML解析方式

开发中较常见的解析方式有俩种：

  • DOM - 要求解析器把整个XML文档读取到内存中，并解析成一个Document对象
    • 优点：元素与元素之间保留结构关系，故可以进行增删改查操作
    • 缺点：XML文档过大，可能出现内存溢出的现象
  • SAX - 逐行扫描文档，边扫描边解析。并以事件驱动的方式进行具体解析，每执行一行都将触发对应的事件。（了解）
    • 优点：占用内存少， 处理速度快，可以处理大文件
    • 缺点：只能读，逐行后将释放资源

4.3 XML常见的解析器

解析器：就是根据不同的解析方式提供的具体实现，因为解析器过于繁琐，为方便开发，提供了易于操作的解析开发包

  • JAXP
    sun公司提供的解析器，支持DOM和SAX俩种方式
  • Dom4j
    一款非常优秀的解析器。
    Dom4j是一个易用、开源的库，用于XML、Xpath和XSLT。
    它应用与Java平台，采用了Java集合框架并完全支持DOM、SAX、JAXP
  • Jsoup
    Jsoup是一款Java的HTML解析器，当然也可以解析XML
  • PULL
    Android内置的XML解析器，类似于SAX

4.4 Dom4j的使用

  1. 下载Jar包并添加到myJar文件夹
  2. API介绍
    使用核心类SaxReader加载xml获得Document，通过Document对象获得文档的根元素，然后就可以操作了。

    常用API如下：
方法说明
SaxReader read(…)加载执行xml文档
Document getRootElement()获取根元素
Element elements(…)获取指定名称的所有子元素，可以不指定名称
element(…)获取指定名称的第一个子元素，可以不指定名称
getName()获取当前元素的元素名
attributeValue(…)获取指定属性名的属性值
elementText(…)获取指定名称元素的文本值
getText()获取当前元素的文本值
  1. 解析XML
import org.dom4j.Attribute;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.junit.Test;

import java.util.List;
/**
 * @program: proj_homework_2_1
 * @description:
 * @author: 
 * @create: 2020-12-08 09:06
 **/
public class TestDom4j {
    @Test
    public void testDom4j() throws DocumentException {
        //1、创建XML解析对象
        SAXReader saxReader = new SAXReader();

        //2、解析XML，获取文档对象
        Document doc = saxReader.read("D:\\lagou_git\\lagouhomework\\level2\\module1\\code\\proj_homework_2_1\\src\\com\\lagou\\test\\xml\\test.xml");

        //3、根据Document对象获取根元素
        Element root = doc.getRootElement();

        //4、获取根元素名称
        System.out.println("根元素：" + root.getName());

        List<Element> elements = root.elements();
        for (Element e:elements) {
            List<Attribute> attributes = e.attributes();
            System.out.print("元素：" + e.getName());


            for (Attribute a:attributes) {
                System.out.println(" 属性：" + a.getName() + "=" + a.getValue());
            }

            //求元素的子元素
            List<Element> elements2 = e.elements();
            for (Element e2:elements2){
                System.out.println("   子元素：" + e2.getName() + " 子元素文本：" + e2.getText());
            }
            System.out.println();
        }
    }
}

4.5 Xpath方式读取XML

  • Xpath介绍

    • Xpath是一门在XML文档中查找信息的语言
    • 作用
      由于Dom4j解析XML时只能一层一层解析，当XML文件层数过多时使用很不方便，结合Xpath就可以直接获取到某个元素

  • Xpath基本语法
    在这里插入图片描述

  • API介绍
    在这里插入图片描述

  • Xpath读取XML

    1. 下载jar包并添加到myJar文件夹
    2. Xpath语法获取单个节点信息
	@Test
    public void testXpath() throws DocumentException {
        //1、创建XML解析对象
        SAXReader reader = new SAXReader();

        //2、解析XML，获取文档对象
        Document doc = reader.read("D:\\lagou_git\\lagouhomework\\level2\\module1\\code\\proj_homework_2_1\\src\\com\\lagou\\test\\xml\\test.xml");

        //3、通过selectSingleNode()获取 name节点
        Node name = doc.selectSingleNode("/jdbc_users/jdbc_user/name");  //结合Xpath语法获取节点
        System.out.println("节点名称：" + name.getName());
        System.out.println("节点文本值：" + name.getText());

        //4、获取第二个人的姓名
        Node name2 = doc.selectSingleNode("/jdbc_users/jdbc_user[2]/name");  //结合Xpath语法获取节点
        System.out.println("节点名称2：" + name2.getName());
        System.out.println("节点文本值2：" + name2.getText());
    }
  1. Xpath语法获取属性值
@Test
    public void testGetAttribute() throws DocumentException {
        //1、创建XML解析对象
        SAXReader reader = new SAXReader();

        //2、解析XML，获取文档对象
        Document doc = reader.read("D:\\lagou_git\\lagouhomework\\level2\\module1\\code\\proj_homework_2_1\\src\\com\\lagou\\test\\xml\\test.xml");

        //3、获取第一个jdbc_user节点，id属性的值
        Node id = doc.selectSingleNode("/jdbc_users/jdbc_user/attribute::id");
        System.out.println("第一个用户的id属性值：" + id.getText());

        //4、获取最后一个jdbc_user节点，id属性的值
        Node idLast = doc.selectSingleNode("/jdbc_users/jdbc_user[last()]/attribute::id");
        System.out.println("最后一个用户的id属性值：" + idLast.getText());

        //5、通过id值获取 jdbc_user中的name值
        Node jdbc_user = doc.selectSingleNode("jdbc_users/jdbc_user[@id='2']"); //获取id=2的用户节点
        Node name = jdbc_user.selectSingleNode("name");
        System.out.println("最后一个id的用户姓名：" + name.getText());
    }
  1. Xpath获取多个节点信息
@Test
    public void testSelectNodes() throws DocumentException {
        //1、创建解析器对象
        SAXReader reader = new SAXReader();

        //2、解析XML对象，获取文档对象
        Document doc = reader.read("D:\\lagou_git\\lagouhomework\\level2\\module1\\code\\proj_homework_2_1\\src\\com\\lagou\\test\\xml\\test.xml");

        //3、查询所有节点，//表示获取多个
        List<Node> list = doc.selectNodes("//*");
        for (Node n:list) {
            System.out.println("节点名：" + n.getName());
        }

        //4、获取所有的用户userid
        List<Node> list1 = doc.selectNodes("//userid");
        for (Node n:list1) {
            System.out.println("userid：" + n.getText());
        }

        //5、获取id值为2的节点中所有内容
        List<Node> list2 = doc.selectNodes("/jdbc_users/jdbc_user[@id='2']//*");
        for (Node n:list2) {
            System.out.println(n.getName() + " = " + n.getText());
        }
    }

5、JDBC自定义XML

5.1 定义配置文件

jdbc-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<jdbc>
    <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
    <property name="url">jdbc:mysql://localhost:3306/db4?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true</property>
    <property name="username">root</property>
    <property name="password">123456</property>
</jdbc>

5.2 编写工具类(配置式)

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Node;
import org.dom4j.io.SAXReader;

import java.sql.*;

/**
 * @program: proj_homework_2_1
 * @description:
 * @author: 
 * @create: 2020-12-08 10:27
 **/
public class JDBCConfigUtils {
    private static String DRIVERNAME;
    private static String URL;
    private static String USER;
    private static String PASSWORD;

    //静态代码块解析JDBC的XML配置文件
    static{
        try {
            //创建XML解析器
            SAXReader reader = new SAXReader();

            //解析XML对象，获取文档对象
            Document doc = reader.read("D:\\lagou_git\\lagouhomework\\level2\\module1\\code\\proj_homework_2_1\\src\\com\\lagou\\test\\jdbcxml\\jdbc-config.xml");

            //结合Xpath语法获取驱动名并赋值
            DRIVERNAME = doc.selectSingleNode("/jdbc/property[@name='driverClass']").getText();

            //获取URL并赋值
            URL = doc.selectSingleNode("/jdbc/property[@name='url']").getText();

            //获取username并赋值
            USER = doc.selectSingleNode("/jdbc/property[@name='username']").getText();

            //获取password并赋值
            PASSWORD = doc.selectSingleNode("/jdbc/property[@name='password']").getText();

            //注册驱动
            Class.forName(DRIVERNAME);
        } catch (DocumentException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL,USER,PASSWORD);
    }

    /**
     * 无结果集的释放资源
     */
    public static void close(Connection conn, Statement statement){
        if(statement != null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(null != conn){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 有结果集的释放资源
     */
    public static void close(Connection conn, Statement statement, ResultSet resultSet){
        if(null != resultSet){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        close(conn,statement);
    }
}

5.3 测试工具类

import org.junit.Test;

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

/**
 * @program: proj_homework_2_1
 * @description:
 * @author: 
 * @create: 2020-12-08 10:39
 **/
public class JDBCConfigUtilTest {
    @Test
    public void testJDBCConfigUtils() throws SQLException {
        //获取连接
        Connection connection = JDBCConfigUtils.getConnection();

        //获取预处理对象
        String sql = "select * from jdbc_user where userid like concat('%',?,'%')";
        PreparedStatement ps = connection.prepareStatement(sql);

        //执行SQL,获取结果集
        ps.setString(1, "q");
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            System.out.print("id = " + rs.getInt("id"));
            System.out.print(" userid = " + rs.getString("userid"));
            System.out.print(" name = " + rs.getString("userid"));
            System.out.print(" password = " + rs.getString("password"));
            System.out.print(" logins = " + rs.getString("logins"));
            System.out.print(" orgid = " + rs.getString("orgid"));
            System.out.println(" birth = " + rs.getString("birth"));
        }

        //释放资源
        JDBCConfigUtils.close(connection,ps,rs);

    }
}
迟到_啦
关注
专栏目录
mysql 8 设置允许远程连接 You are not allowed to create a user with GRANT
mxskymx的专栏
03-23 7万+
1.登录mysql : mysql -u root -p 2.输入密码： Enter password: xxxxx ------Server version: 8.0.15 MySQL Community Server - GPL 3.进入mysql数据库：use mysql; 4.设置允许远程用户访问： MySQL [mysql]> GR...
mysql基础系统学习
jsty181818的博客
04-29 299
基础
Mysql系统知识梳理
weixin_30426065的博客
03-30 56
1 数据库分类 　　MySQL Oracle redis 2 MySQL 存储引擎有哪些 　　ENGINE=InnoDB 提供事务安全表，支持外键。 　　MyISAM 　　Memory数据存入内存中，如果内存出现异常或事重启关机，所有数据都会消失 3 事务 　　概念：逻辑上的一组sql语句，组成这组操作的sql语句，要么成功，要么失败。 3.1 事务四大特性ACID 　　原子性...
MySQL知识点系统总结
雷雪松
04-06 1556
MySQL数据库是一个非常流行的关系型数据库。配合Linux、PHP、Apache，简称lamp，是一般个人企业网站的首选。MySQL用起来不难，要系统的用好，可不是一件简单的事。于是我对MySQL系统的总结一下几点：1、MySQL的安装（linux和windows）2、MySQL常用操作、基本特性 1.增 insert into 表名 values(0,'测试'); 2.删数据 delete f...
mysql 的基础知识梳理，你想要的都在这里了
qq_41578037的博客
06-12 562
binlog redolog undolog 事务隔离级别 表锁 行锁 间隙锁 mvcc ACID 索引 B+树 聚簇索引 非聚簇索引 回表 索引吓退 覆盖索引
“You are not allowed to create a user with GRANT”错误出现原因及解决方法：
qq_43802625的博客
11-04 2万+
2、创建新用户：create user '用户名'@'localhost' identified by '123456';3、设置用户权限：grant all on *.* to 'firstuser'@'localhost';8.0之后的mysql不支持 授权的时候就进行用户创建，所以创建 之后才能授权;1、查看正在mysql登录状态的用户：select current_user;3、登录验证查看权限：mysql -ufirstuser -p。
MySQL基础学习
qq_40831898的博客
01-02 231
1、进入某一目录下：cd /home/shiyanlou/Desktop 2、克隆代码：git clone https://github.com/shiyanlou/SQL4.git 3\数据库导入外部代码：source /home/shiyanlou/Desktop/SQL4/MySQL-04-01.sql 打开MySQL服务:sudo service mysql start 安装 MySQL...
快速学习MySQL基础知识
09-08
快速学习MySQL基础知识涵盖了SQL的基本用法，包括SQL语句的规范、数据库类型、SELECT语句的执行过程以及常用的查询技巧。 首先，SQL（Structured Query Language）是数据库管理的核心语言，分为四个主要部分：DDL...
MySQL 基础知识的总结
06-10
本文将深入探讨MySQL的基础知识，包括安装、数据类型、表的创建、查询语句、索引以及事务处理等核心概念。 1. **安装与配置** MySQL的安装过程通常包括下载适合操作系统的安装包，按照向导进行安装，然后配置...
mysql基础知识和mysql优化整理
12-24
MySQL是世界上最受欢迎的关系型数据库管理系统之一，广泛应用于各种规模的企业和项目中...通过文档《01_mysql基础知识.docx》和《02_mysql的优化.docx》，你可以更深入地学习这些内容，为你的MySQL应用提供有力的支持。
mysql学习笔记之基础知识
09-09
在MySQL的学习过程中，了解基础命令是至关重要的。本文将介绍一些基本的MySQL操作命令，帮助初学者快速上手。 首先，我们需要查看已经存在的数据库。在MySQL中，可以使用`SHOW DATABASES;`命令来列出所有数据库。当...
MySQL8.0 ERROR 1410 (42000): You are not allowed to create a user with GRANT
一个标题
11-04 1万+
如果已建用户是:testuser@localhost,你要授权给testuser@%,那肯定不行，必须一样才行。实质问题原因是：授权语法不严谨导致所授权的用户及其所在主机名，要与实际存在的用户及其主机名一致。在mysql上授权指定账号特定数据库表的查询权限，出现下面的报错。所以主机名不一致的需要先修改主机名或者修改授权SQL语句。-- 更新指定用户主机。-- 查看用户信息。
ERROR 1410 (42000): You are not allowed to create a user with GRANT
qq_41160301的博客
02-25 4797
今天为创建的tom用户授予相关权限提示如下错误： mysql> grant select,insert,update,delete on test.* to tom@localhost; ERROR 1410 (42000): You are not allowed to create a user with GRANT 产生用户不能授权的原因是mysql 数据库中user 表中的特定用户(root) 的host 的属性值为localhost. 解决办法如下： 使用mysql 数据库 mysql&gt
1410 - You are not allowed to create a user with GRANT
m0_70556273的博客
08-23 1645
解决办法：于是修改host：update user set host=’%’ where user=‘wecube’;1064原因分析 ：此版的的mysql版本把将创建账户和赋予权限分开了。
解决mysql8报错：ERROR 1410 (42000): You are not allowed to create a user with GRANT
热门推荐
qq_34680444的博客
01-10 6万+
1. 今天刚装了mysql8.0.13，试着分配几个账号和权限，结果报错： 2. 查资料得知mysql8的分配权限不能带密码隐士创建账号了，要先创建账号再设置权限 输入命令：Grant all privileges on test.* to 'test'@'%'; 又报错：You are not allowed to create a user with GRANT ； 不允许使用...
mysql8.0无法给用户授权或提示You are not allowed to create a user with GRANT的问题
u013235495的博客
09-27 3586
提示意思是不能用grant创建用户，mysql8.0以前的版本可以使用grant在授权的时候隐式的创建用户，8.0以后已经不支持，所以必须先创建用户，然后再授权，命令如下： mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'Hadoop3!'; Query OK, 0 rows affected (0.04 sec) mysql> grant all privileges on *.* to 'root'@'%'; Query OK, 0 rows
You are not allowed to create a user with GRANT
m0_62946761的博客
12-26 7524
mysql设置远程连接步骤
沈阳建筑大学在河南2021-2024各专业最低录取分数及位次表.pdf
最新发布
09-16
全国各大学在河北2021-2024年各专业最低录取分数及录取位次数据，高考志愿必备参考数据
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

迟到_啦

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值