MySql初步学习

安装mysql 。。。自行百度

链接数据库

通过命令行！！！

mysql -u用户名 -p密码 链接数据库

-- 更新数据库密码（适合小白忘记密码）
update mysql.user set authentication_string=password('a') where user= 'root' and Host='localhost';

flush privileges ; -- 刷新权限

-- --------------------------------------
-- 查看所有的数据库
show databases; 
-- 使用某个数据库
user 数据库名;

-- 展现数据库中所有的表
show tables;-- 查看数据库中所有的表
-- 查看表中的数据
describe 表名;
-- 创建一个数据库
create database 数据库名;
use 数据库名;

-- 退出数据库
exit ;

数据库语言 CURD业务程序员（简单的增删改查），API，

DDL(数据库定义语言)：

是对数据库内部的对象进行创建，删除，修改等的操作语言。它和DML语言最大的区别是DML只是对表内部数据的操作，而不涉及到表的定义，结构的修改，更不会涉及到其它对象。

创建数据库

1 2	create database if not exists <name> ; -- 创建一个数据库

删除数据库

1	drop database if not exists <name> ; 删除一个数据库

使用数据库

1	use <name> ; -- 使用数据库

查看数据库

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

数据库的数据类型

数值

tinyint	十分小的数据	1个字节
smallint	比较小的数据	2个字节
mediumint	中等大小的数据	3个字节
int	标准的整数	4个字节
bigint	较大的数据	8个字节
float	单精度（浮点性）	4个字节
double	双精度	8个字节
decimal	字符串形式的浮点数	金融计算的时候一般使用

字符串

char	字符串固定大小	0-255
varchar :1st_place_medal:	可变字符串	0-65535
tinytext	微型文本	2^8 -1
text :2nd_place_medal:	文本串	2^16-1

时间日期

date	YYYY-MM-DD	日期格式
time	HH:mm:ss	时区格式
datetime	YYYY-MM-DD HH:mm:ss	最长用的时间格式
timestamp	时间戳，1970.1.1	从1970.1.1到现在的毫秒数！也比较常用
year	年份表示

null

没有值，注意，不要使用null进行运算，结果运算为null.

数据库的子段属性：

Unsigned：

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

zerofill：

0填充的
不足的位数，用0来补充,int(3)， 5 —- 005

自增

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

非空 null not null:

假设设置为not null 如果不给他赋值就会报错
null 如果不填写值，这个值就会自动填充为空值。

默认

设置默认的值

如果不给他设置值，他就会默认为我们设置的填充值

一般创建表格的时候我们必须创建的存在的5个字段

id 主键

version 乐观锁

is_delete 伪删除数据无价假删除保数据

gmt_create 创建时间

gmt_update 删除时间

创建表

-- 创建表格，一般表面我们使用``进行标记
-- 注意表中字符串的使用一般是单引号或者双引号
-- 所有的语句后添加，最后一个不要加

create table if not exists test1(
    -- id 列名  int 数据类型  primary key 主键 not null 不为空 auto_increment 自增   comment "" 别名
	id  int  primary key  not null auto_increment comment '主键',
    -- default '' 默认的
    name varchar(10) not null default '匿名' comment '姓名'，
    
    birthday datetime default null comment '生日'

)Engine=innodb default charset=utf8;

格式

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

show create database [数据库名]  -- 查看创建数据库的语句
show create table [数据库名] -- 查看数据库表的定义语句
Desc [表名] -- 显示表的结构

数据库引擎

MYISAM 早先年使用的

事务支持：不支持

数据行锁定：不支持

外键：不支持

全文索引：支持

表空间大小：较小

INNODB 目前默认使用的

事务支持：支持

数据行锁定：支持

外键：支持

全文索引：不支持

表空间大小：较大，约为2倍

常规使用操作：

MYISAM 节约空间，速度较快

INNODB 安全性高，事务的处理，多表多用户操作

在物理空间存储的位置

所有的数据库文件都存在data下一个文件夹就代表一个数据库

本质还是文件的存储

MYSQL引擎在物理文件上的区别

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

MYISAM对应文件
- *.frm —表结构的定义文件
- *.MYD 数据文件（data）
- *.MYI 索引文件（index）

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

1	charset=utf8;

不设置的话会是使用mysql中默认的编码，他不支持中文。

my.ini中配置默认的编码

1	character-set-server=utf8;

===============================================================

修改表

1 2	-- 修改表的名字 alter table 旧表名 rename as 新表名 alter table san rename san1;

增加表字段

1 2	-- 添加表字段 alter table 表名 add 列名数据类型 alter table test add min int(4);

修改表的字段

-- 修改约束 alter table 表名 modify 列名 数据类型
alter table test modify min varchar(20);

-- 字段 重命名 以及修改约束  alter  table  表名 change 旧名字 新名字 列属性
alter table test change min min1 int(11); 

-- 删除表字段  alter table 表名 drop 列名
alter table 表名 drop 列名；

-- 删除表 
drop table if exists 表名

== 所有的创建和删除操作劲量加上判断，以免造成错误 ==

MYSQL数据管理

外键

方式一

-- 定义外键key
-- 给这个外键添加约束执行引用 references 引用
key FK_列名(列名) ，
constraint FK_列名 foreign key 列名 references 表名（列名）

-- 方式二，创建表成功后，添加外键约束
alter table test
add constraint FK_列名 foregin key (列名) references  `表名`(列名)；
-- alter table 表 add constraint 约束名 foreign key(作为主键的列) referebces 那个表（哪个字段）

删除有外键关联的值的时候，必须要删除外键。

最好的办法，数据库就是单纯的表，用来存数据，只有行和列

我们想使用多张表的时候想使用外键。

DML（数据库操作语言）：

数据操作语言DML（Data Manipulation Language），用户通过它可以实现对数据库的基本操作。例如，对表中数据的查询、插入、删除和修改。在DML中，应用程序可以对数据库作插，删，改，排，检等五种操作

数据库意义，存储，管理。

创建一个表

create table if not exists test(
    -- id 列名  int 数据类型  primary key 主键 not null 不为空 auto_increment 自增   comment "" 别名
	id  int  primary key  not null auto_increment comment '主键',
    -- default '' 默认的
    name varchar(10) not null default '匿名' comment '姓名'
)Engine=innodb default charset=utf8;

添加(insert):

-- 一般写插入语句，我们一定要数据和字段一一对应 一个子段对应一个值
-- 插入数据
insert into 表名 (【字段1，自段2，字段三】) value ('值1')，('值2'),('值3')；

insert into test(name) value("张三")；
 -- 或者
 -- 已知列  id name 
 insert into test value ('12','name')；
 
 -- 由于主键自增，那么我们不插入id
 insert into test value( ,'name')；
 
 -- 一次插入多个数据
 insert into test(name) value ('张三'),（'李四'）;

注意：

字段使用英文逗号隔开
不写具体的列进行插入，那么我们插入的时候就需要整个数据库列的值全部插入
字段可以省略，但是后面的值必须要一一对应，不能少
可以同时插入多条数据，value后边的值，需要使用英文逗号隔开。(),()

更新(update):

一般是更新数据库中的数据，而不是更新表的子段。。。。

-- 修改学员的名字
update test set name = '张三' where id = 1 ;
-- 在不指定具体的条件的情况下更改会更改表中所有name的数据
update test set name = '李四';  -- 更新整个表中列为name的所有数据是name都等于'李四'；  切记不要傻乎乎的使用。。。后果就是删库跑路

-- 修改多个属性，逗号隔开
update test set name =  '张三'  , tel = '123321', email = '2569758@qq.com' where id = 1;



-- 语法 
update 表名 set 【列名1 = 值1，列2 = 值2 。。。。】 where 【条件】；

操作符	含义	范围	结果
=	等于	5=6	false
<> 或 !=	不等于	5<> 6	true
>	大于	5>6	false
<	小于	5<6	true
<=	小于等于	—
>=	大于等于	—
between… and …	介于…之间	[2,5]
and	和	5>1 and 1>2	false
or	或	5>1 or 1>2	true

update 表名 set 【列名1 = 值1，列2 = 值2 。。。。】 where 【条件】；

注意：

更新某个类需要指定列名，顺带加上条件，没有加条件则会更新所有的列
不同列名之间需要加，隔开。

删除(delete)

删除表中的数据，和drop不一样，drop是删除整个表，delete是删除表中的数据

-- 删除数据 尽量避免不添加条件删除
delete from 表名 ; -- 这样会删除整个数据库

-- 删除指定数据，也就是根据条件删除
delete from 表名 where  列名1 = 值1 and 列名2 = 值2 ...；

TRUNCATE命令（推荐）

清空某一张表。。。

1 2	-- 清空student表 truncate TRUNCATE 表名；

delete 和 truncate区别：

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

delete删除问题，重启数据库，现象

InnoDB 自增会重1开始(存在内存中的，断电就失去)；

MyISAM 继续从上一个自增量开始(存在文件中，不会丢失）

DQL（数据库查询语言）：

DQL（data query language）数据查询语言，专门用来查询数据。

一般使用的都是Select 所有的查询操作都是这个
简单的查询和复杂的查询都能做
数据库中最核心的语言，也是最重要的语句。
使用频率最高的语句

查询所有

1	select * from 表名； -- 一般不推荐使用 sql优化不推荐使用

查询指定字段

1	select id , name from 表名；

查询起别名，然后通过别名得到数据

as 可以给字段起别名，也可以给表起别名

如： from 表名 as ‘别名’

1	select 列名1 as '别名1' , 列名2 as '别名2' ... from 表名；

去重查询distinct

去除查到的重复的值

select distinct 列名 from 表名；  -- 查询表中重复的数据，使得重复的数据只显示一条。

select version() ; -- 查询系统版本
select 100*3 -1 as '结果' ;  -- 用来计算  
select @@auto_increment_increment ;   -- 查询自增的步长（变量）

-- 例子 查询考试成绩 + 1 查看
select id , scode + 1 as '加分后' from 表名;

数据库中表达式：

文本值，列，NULL，函数，计算表达式，系统变量….

select 表达式 from 表名；

WHERE条件

一般作为检索数据中的符合条件的值

搜索的条件一般由一个或多个条件组成。

select id , score from 表名；
-- 查询成绩在95 - 100 直接的 id
select id from 表名 where score >=95  and score <= 100;

-- and  && 
select id from 表名 where score >=95  && score <= 100;

-- 模糊查询（between）
select id from 表名 where score between 95 and 100;

-- 除了1000号同学以为的所有同学的成绩
select id ,score from 表名 where id != 1000;

-- != not 
select id ,score from 表名 where not id = 1000;

了解逻辑运算符：

运算符	语法	描述
and &&	a and b a && b	逻辑与，两真为真
or \	\		a \	\	b a or b	逻辑或，一真为真，全假为假
not !	not a !a	逻辑非，真为假，假为真**

模糊查询

运算符	语法	描述
is null	a is null	如果操作符为null,结果为真
is not null	a is not null	如果操作符不为空，结果为真
between	a between b and c	如果a在b和c之间，则结果为真
like	a like b	sql匹配成功，如果a匹配b，则结果为真
in	a in b	a 在 b 中

举例：

-- 查询姓刘的同学的名字
select id , name from 表名 where name like '刘%'；

-- 查询姓刘的同学，且只有一个字的
select id , name from 表名 where name like '刘_'；

-- 查询姓刘的同学，且只有两个字的
select id , name from 表名 where name like '刘__'；

-- 查询名字中有嘉的同学，%嘉%
select id , name from 表名 where name like '%嘉%'；

-- =================== in（具体的一个或多个值） ================
 -- 查询101,102,103 号学员
 select id, name from 表名 where id in ( 101,102,103);
 
 -- 用in查询名字等于张三，李四。。。的学生
 select id , name from 表名 where name in ('张三', '李四' ,'王五'...)；
 
 -- 查询姓名为空的学习（当然我们设置默认值，必定不存在这个值）
  select id , name from 表名 where name  = '' or name is null;
  
  
  -- 查询姓名不为空的同学
    select id , name from 表名 where name  != '' or name is not null;

连表查询（JOIN on）

1666274982133

-- 连接两个表
select s.id , s.name ,cid ,cname 
from stu s inner join class as r
where s.id = r.id;
 
-- 右连接 right join 
select s.id , name , cid , cname from stu s
right join class c
on s.id = c.id

-- 左连接 left join  
select s.id , name , cid , cname from stu s
left join class c
on s.id = c.id

-- join  on  连接查询
-- where  等值查询

-- 查询name为空的同学
select s.id , sname , cid, cname 
from stu s left join 
class c on s.id = c.id 
where name is null;

-- 查询时间为空的学生信息
select s.id ,date, name ,cid , cname from stu s
left join class c
on s.id = c.id
where date is null;

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

-- 测试数据库
create table `table_a` (
  `aid` int(11) NOT NULL AUTO_INCREMENT,
  `a_name` varchar(255) NOT NULL,
  `age` smallint NOT NULL,
  PRIMARY KEY(`aid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '测试表A';
 
create table `table_b` (
  `bid` int(11) NOT NULL AUTO_INCREMENT,
  `b_name` varchar(255) NOT NULL,
  `age` smallint NOT NULL,
  PRIMARY KEY(`bid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '测试表B';
 
# 插入测试数据
INSERT INTO `table_a`(aid, a_name, age) VALUES(1, 'test1', 1),(2, 'test2', 2),(3, 'test3', 3);
INSERT INTO `table_b`(bid, b_name, age) VALUES(1, 'test2', 2),(2, 'test3', 3),(4, 'test4', 4);

这三条SQL都是等价的

1
2
3

SELECT * FROM table_a JOIN table_b;
SELECT * FROM table_a INNER JOIN table_b;
SELECT * FROM table_a CROSS JOIN table_b;

结果如下：

inner join连接

-- 相当于笛卡尔积
mysql> SELECT * FROM table_a INNER JOIN table_b;
+-----+--------+-----+-----+--------+-----+
| aid | a_name | age | bid | b_name | age |
+-----+--------+-----+-----+--------+-----+
|   1 | test1  |   1 |   1 | test2  |   2 |
|   2 | test2  |   2 |   1 | test2  |   2 |
|   3 | test3  |   3 |   1 | test2  |   2 |
|   1 | test1  |   1 |   2 | test3  |   3 |
|   2 | test2  |   2 |   2 | test3  |   3 |
|   3 | test3  |   3 |   2 | test3  |   3 |
|   1 | test1  |   1 |   4 | test4  |   4 |
|   2 | test2  |   2 |   4 | test4  |   4 |
|   3 | test3  |   3 |   4 | test4  |   4 |
+-----+--------+-----+-----+--------+-----+

有ON和WHERE筛选条件，此时得到的结果是两张表的交集（中间的图），对于内连接，ON和WHERE是等价的，但是对于外连接则不是，在下面会讲到。

# 这两条SQL是等价的，不过建议使用ON关键字，约定俗成
SELECT * FROM table_a a INNER JOIN table_b b ON a.a_name=b.b_name;
SELECT * FROM table_a a INNER JOIN table_b b WHERE a.a_name=b.b_name;

# 结果如下：
mysql> SELECT * FROM table_a a INNER JOIN table_b b ON a.a_name=b.b_name;
+-----+--------+-----+-----+--------+-----+
| aid | a_name | age | bid | b_name | age |
+-----+--------+-----+-----+--------+-----+
|   2 | test2  |   2 |   1 | test2  |   2 |
|   3 | test3  |   3 |   2 | test3  |   3 |
+-----+--------+-----+-----+--------+-----+

右连接(right join)

右连接的关键字是 RIGHT JOIN，从上图可以得到（右边的图），右连接其实就是两个表的交集+右表剩下的数据 ，当然这是在没其他过滤条件的情况下。

mysql> SELECT * FROM `table_a` a RIGHT JOIN `table_b` b ON a.a_name=b.b_name;
+------+--------+------+-----+--------+-----+
| aid  | a_name | age  | bid | b_name | age |
+------+--------+------+-----+--------+-----+
|    2 | test2  |    2 |   1 | test2  |   2 |
|    3 | test3  |    3 |   2 | test3  |   3 |
| NULL | NULL   | NULL |   4 | test4  |   4 |
+------+--------+------+-----+--------+-----+

左连接(left join)

左连接的关键字是 LEFT JOIN，从上图可以得到（左边的图），左连接其实就是两个表的交集+左表剩下的数据 ，当然这是在没其他过滤条件的情况下。

# 没找到的被置为NULL
mysql> SELECT * FROM `table_a` a LEFT JOIN `table_b` b ON a.a_name=b.b_name;
+-----+--------+-----+------+--------+------+
| aid | a_name | age | bid  | b_name | age  |
+-----+--------+-----+------+--------+------+
|   2 | test2  |   2 |    1 | test2  |    2 |
|   3 | test3  |   3 |    2 | test3  |    3 |
|   1 | test1  |   1 | NULL | NULL   | NULL |
+-----+--------+-----+------+--------+------+

WHERE子句中的过滤条件就是我们常见的那种，不管是内连接还是外连接，只要不符合WHERE子句的过滤条件，都会被过滤掉。

而ON子句中的过滤条件对于内连接和外连接是不同的，对于内连接，ON和WHERE的作用是一致的，因为匹配不到的都会过滤，所以你可以看到内连接并不强制需要 ON 关键字；但是对于外连接，ON决定匹配不到的是否要过滤，所以你可以看到外连接是强制需要 ON 关键字的。

我要查询哪些数据 select …

从那几个表中查 from 表 XXX join 连接的表 on 交叉条件

假设存在一种多张表查询，慢慢来，先查询两张表然后在慢慢添加。

自连接(了解)

自己的表和自己的表连接： 核心是一张表拆为两张表使用

将数据同一张表中数据进行查分，然后起别名。将一张表变成两张表处理。

分为父和子，也就是拆分的表可以成为父和子关系或者说包含和被包含关系。

分页和排序

limit 和 order by

limit（分页）

limit 当前页，页面大小

limit 0 , 5 —前边数字表示第几页，后边数据每页显示的数据条数

order by （排序）

升序 asc

order by id asc 根据id升序排序
降序 desc

order by id desc根据id降序排序

子查询和嵌套查询

出现在其他语句中的select语句被称为子查询

:top:查询顺序是由里及外。

-- 查询 数据库结构的所有考试结果(学号，科目编号，成绩)，降序排列
-- 方式1  id 是stu 表 学生编号，sid 是 score 表，成绩编号
select id , s.sid, score
from score s,
inner join sub  u
on s.id = u.id
where sname = '数据结构'
order by score desc

-- 方式2 使用子查询
select id , sid ,score
from score 
where sid = (
    -- 作为子查询 先查询里边的然后在查询外边的
select sid 
    from score 
    where sname = '数据结构'
)
order by score desc;


-- 查询所有数据库结构 的学生学号
select id from score where sname = '数据结构'； 

-- 查询分数不小于80分的学号和姓名
select distinct id , name 
from stu t
inner join score s
on s.id = t.id
where score > 80;

-- 在此基础上添加一个科目，高等数学
select distinct id , name 
from stu t
inner join score s
on s.id = t.id
where score > 80 and sid = (
	select sid  from score 
    where sname = '高等数学'
)

按位置分类：

select后面
from后面
where或having后面
exists后面
按结果级的行数不同分类：

标量子查询 (结果集有一行一列)
列子查询 (结果集只有一列多行)
行子查询 (结果集只有一行多列)
表子查询 (结果集一般为多行多列)

where或having后面长放的

标量子查询
列子查询
行子查询

特点：

子查询都放在小括号内，结尾不用加 ;
子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
子查询优先于主查询执行，主查询使用了子查询的执行结果
子查询根据查询结果的行数不同分为以下两类：单行子查询和多行子查询

单行子查询
结果集只有一行
一般搭配单行操作符使用：> < = <> >= <=
非法使用子查询的情况：

子查询的结果为一组值
子查询的结果为空

多行子查询
结果集有多行
一般搭配多行操作符使用：any、all、in、not in
in：属于子查询结果中的任意一个就行
any和all:往往可以用其他查询代替

MySQL常用函数

-- 数学运算
SELECT ABS(-10);-- 绝对值
SELECT CEIL(10.1);-- ceil和ceiling 向上取整
SELECT FLOOR(10.1);-- 向下取整
SELECT RAND();-- 返回0-1随机数
SELECT SIGN(NULL);-- 返回参数的符号 负数返回-1 正数返回1 
-- 0和非数字返回0 null返回null

-- 字符串函数
SELECT CHAR_LENGTH('fwebw');-- 返回字符串长度
SELECT CONCAT('1','2','3');-- 拼接字符串
SELECT INSERT('123456',1,3,'0');-- 先把第一个字符串的第1位起的0个字符去除
-- 然后再把第二个字符串插入到对应位置
SELECT LOWER('JIOji');-- 转小写字母
SELECT UPPER('wfdINOI');-- 转大写字母 
SELECT INSTR('asdasd','sd');-- 返回第一个字串出现的位置
SELECT REPLACE('123456789123','123','hhh');-- 把s1中s2部分替换为s3（所有s2）
SELECT SUBSTR('123456789',2,4);-- 截取字符串，从第2个字符开始截取4个字符
SELECT REVERSE('123465');-- 翻转字符串

-- 查询姓周的同学并把姓换为邹
SELECT REPLACE(studentName,'周','邹') FROM students
WHERE studentName LIKE '周%';

-- 时间和日期函数
SELECT CURRENT_DATE();-- 获取当前日期
SELECT CURDATE()-- 获取当前日期
SELECT CURRENT_TIME();-- 获取当前时间（时分秒）
SELECT SYSDATE();-- 获取系统时间
SELECT NOW();-- 获取时间
SELECT YEAR(NOW());-- 获取当前年
SELECT MONTH(NOW());-- 获取当前月
-- ······获取日时分秒同理

-- 系统
SELECT SYSTEM_USER();
SELECT USER();-- 获取当前用户
SELECT VERSION(); -- 获取当前版本信息

函数字段		描述
abs()		绝对值
ceiling()		向上取整
floor()		向下取整
rand()		返回一个0-1直接的随机数
sign()		判断一个数的符号负数范围-1，正数返回1
char_length()		判断字符串长度
concat(‘’,’’,’’)		拼接字符串
insert(‘查询并被替换的’,1，3,’要替换的’)		查询替换从1，到3号位置替换
lower()		将字符串专为小写
upper()		将小写转为大写
instr()		返回第一次出现的字符串索引
replace()		替换出现的指定字符串
substr()		截取字符串
reverse()		反转
….		….

聚合函数和分组过滤（having by ）

聚合函数	描述
count()	统计
sum()	求和
avg()	平均值
max()	最大值
min()	最小值

having的用法

having字句可以让我们筛选成组后的各种数据，where字句在聚合前先筛选记录，也就是说作用在group by和having字句前。而 having子句在聚合后对组记录进行筛选。

-- 查询不同科目的平均分最高分和最低分 平均分大于80
-- 由于按照顺序执行，所以where不能限定分组后计算出的平均值等信息
-- 可以使用having
SELECT sname,AVG(score),MAX(score),MIN(score)
FROM score s
INNER JOIN `sub` sub
ON s.id=sub.id
GROUP BY s.id
HAVING AVG(score)>=80;

事务（transaction）

事务特性：ACID（原子性，一致性，隔离性，持久性）

A:原子性
- 要么成功要么不成功
C:一致性
- 最终一致性，保证最终结果一致
I:隔离性
- 一个事务的提交不会影响下一个事务。
- 隔离产生的问题
  - 脏读：一个事务读取了另一个事务未提交的数据。
  - 不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。
  - 幻读：指在一个事务内读取到别的事务插入的数据，导致前后读取不一致。
D:持久性
- 表示事务结束后的数不会随着外界原因导致数据丢失
- 也就是说，事务没有提交，那么就恢复到原来的未提交状态，如果提交，那么就更新为提交后的状态，持久化到数据，事务一旦提交就不可逆。

-- 手动开启事务
start transaction -- 标记一个事务的开始，从这个之后的sql都在同一个事务内

insert xxx
insert xxx

-- 提交 持久化
commit ;
-- 如果提交失败就回滚到原来的样子
rollback;

-- 事务结束
set autocommit = 1 ；-- 开启自动提交

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

模拟场景

-- 转账
create database shop character set utf8 collate utf_8_general_cli;
use shop;
create table account(
	id int (3) not null primary key auto_increment,
    name varchar(20) not null，
    money decimal(9,2) not null
) engine = innodb default charset=utf8;

-- 插入数据
insert into account(name,money) 
value('A',1024.00),('B',2048.00)

-- 模拟转账：事务
set autocommit = 0; -- 关闭自动提交
start transaction ; -- 开启事务
update account set money - 500 where name = 'A';
update account set money + 500 where name = 'B';

commit ; -- 提交事务
rollback ; -- 回滚
set autocommit = 1; -- 恢复默认值

索引

==是帮助mysql高效获取数据的数据结构，提取句子主干，可以得到索引的本质，索引是数据结构。==

索引分类：

主键索引：创建主键后会自动建立索引

唯一标识，主键不可重复，只能有一个主键

#随表一起建索引：
CREATE TABLE customer (
    id INT(10) UNSIGNED  AUTO_INCREMENT ,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
    PRIMARY KEY(id) 
);
#使用AUTO_INCREMENT关键字的列必须有索引(只要有索引就行)。
CREATE TABLE customer2 (
    id INT(10) UNSIGNED,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
 	PRIMARY KEY(id) 
);
#单独建主键索引：
ALTER TABLE customer 
add PRIMARY KEY 
customer(customer_no);  
#删除建主键索引：
ALTER TABLE customer
drop PRIMARY KEY ;  
#修改建主键索引：
#必须先删除掉(drop)原索引，再新建(add)索引

唯一索引:索引列的值必须唯一，但允许有空值

避免重复的列出现，唯一索引可以重复，多个列都可以标识为，唯一索引。

#随表一起建索引：
CREATE TABLE customer (
    id INT(10) UNSIGNED  AUTO_INCREMENT ,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
 	 PRIMARY KEY(id),
 	 KEY (customer_name),
 	 UNIQUE (customer_no)
);
#建立 唯一索引时必须保证所有的值是唯一的（除了null），若有重复数据，会报错。   
#单独建唯一索引：
CREATE UNIQUE INDEX 
idx_customer_no 
ON customer(customer_no); 
#删除索引：
DROP INDEX idx_customer_no
on customer ;

常规索引

默认的，index，key关键字来设置

单列索引：一个索引只包含单个列，一个表可以有多个单列索引

#随表一起建索引：
CREATE TABLE customer (
    id INT(10) UNSIGNED  AUTO_INCREMENT ,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
 	 PRIMARY KEY(id),
  	KEY (customer_name)  
);
#随表一起建立的索引 索引名同 列名(customer_name)
#单独建单值索引：
CREATE INDEX idx_customer_name 
ON customer(customer_name); 
#删除索引：
DROP INDEX idx_customer_name ;

 3.  复合索引：一个索引包含多个列，在数据库操作期间，复合索引比单值索引所需要的开销更小（对于相同的多个列建索引） 



    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#随表一起建索引：
CREATE TABLE customer (
    id INT(10) UNSIGNED  AUTO_INCREMENT ,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_name),
  KEY (customer_no,customer_name)
);
#单独建索引：
CREATE INDEX idx_no_name
ON customer(customer_no,customer_name); 
#删除索引：
DROP INDEX idx_no_name  on customer ;

全文索引

在特定的数据库引擎下才有的，myIsam

快速定位数据

-- 索引的使用
-- 在创建表的时候给字段增加索引
-- 创建完毕后，增加索引

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

-- 增加一个全文索引（索引名） 列名
alter table school.student
add fulltext 
index studentName(studentName);

-- explain 分析mysql执行的状况
explain select * from student ; -- 非全文索引

select * from student
where match(studentName) against('刘')；

聚集索引和非聚集索引
1. 聚集索引：指索引项的排序方式和表中数据记录排序方式一致的索引。
2. 非聚集索引：与聚集索引相反，索引顺序与物理存储顺序不一致

索引测试

-- 我们在创建索引的时候一般使用  primary  key (列名)事实上这就是设置主键，但是这个也就是创建了唯一索引
create table if not exists yyl(
	id int(10) not null primary key auto_increment comment '主键',
    name varchar(20) default null comment  '姓名',
    pwd varchar(50) not null default '123321' comment '密码',
    age int (10) not null default '0' comment '年龄'
)engine = innodb default charset = utf8;

-- 插入100万数据
delimiter $$ -- 写数据之前必须要写，标志
create function mock_data()
returns int
begin
	declare num int default 1000000;
	declare i int default 0;
	while i < num do
	-- 插入语句
		
		insert into yyl (name,pwd,age) 
		value(concat('用户',i),UUID(),floor(rand()*100));
		set i = i + 1;
	end while;
	return i;
end;

-- 执行一下
select mock_data();

/*
select mock_data();
受影响的行: 0
时间: 42.708ms

*/

-- 插入表数据
insert into yyl (name,pwd,age) 
value(concat('用户',i),UUID(),floor(rand()*100));

-- 由于插入了1000000条数据，那么查询一个值
select * from yyl where name = '用户99999';
/*
没创建索引的时候去查询数据
[SQL] select * from yyl where name = '用户99999';
受影响的行: 0
时间: 0.744ms
我们发现时间比较长
*/
explain select * from yyl where name = '用户99999'; -- rows 1000539次

-- 创建索引 id_表名_字段名 on 索引名 on 表（字段）
create index id_yyl_name on yyl(name);

-- 删除索引
drop index id_yyl_name ;
/*
创建索引后再去查询数据非常快
[SQL] select * from yyl where name = '用户99999'; 
受影响的行: 0
时间: 0.009ms
*/
-- 此时explain   rows =1;
explain select * from yyl where name = '用户99999';

==创建索引==

==create index id 表名字段名 on 索引名表(字段)；==

==索引在小数据量的时候，用处不大，但是在大数据的时候，区别十分明显。==

索引原则

索引不是越多越好
不要对进程变动数据加索引
小数据量的表不需要加索引
索引一般加在常常用来查询的字段上！

索引的数据结构：

Hash 类型的索引
B+树：innodb的默认数据结构

讨论为啥Innodb要使用B+树而不使用其他数据结构呢？

B-树：（不是二分查找，却相似于二分查找）
- B-树是一种多路自平衡的搜索树，它类似普通的平衡二叉树，不同的一点是B-树允许每个节点有更多的子节点。B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。
- 存在问题：
  - 每个节点中有key，也有data，而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小。当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率
B+树：
- B+Tree是在B-Tree基础上的一种优化，InnoDB存储引擎就是用B+Tree实现其索引结构。
- 它带来的变化点：
  - B+树每个节点可以包含更多的节点，这样做有两个原因，一个是降低树的高度。另外一个是将数据范围变为多个区间，区间越多，数据检索越快
  - 非叶子节点存储key，叶子节点存储key和数据叶子节点两两指针相互链接（符合磁盘的预读特性），顺序查询性能更高
- 通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。
- 因此可以对B+Tree进行两种查找运算：
  - 一种是对于主键的范围查找的分页查找。
  - 另一种是从根节点开始，进行随机查找。
B树和B+树的区别：

B+树内节点不存储数据，所有数据存储在叶节点导致查询时间复杂度固定为log n
B-树查询时间复杂度不固定，与Key在树中的位置有关，最好为O(1)
B+树叶节点两两相连可大大增加区间访问性，可使用在范围查询等
B+树更适合外部存储（存储磁盘数据）。由于内节点无data域，每个节点能索引的范围更大更精确。

DCL（数据库控制语言）：

数据控制语言用户权限控制 grant、revoke

权限管理

用户管理

-- 创建用户 create user 用户名 identified by '密码'
create user xh identitied by 'a';
-- 修改密码
set password = password('123321');
-- 修改密码（修改指定用户密码）
set password from xh = password('a');
-- 重命名
rename user 原来名 to 新名字;
-- 用户授权
-- all privileges 除了给别人授权，其他都能干
grant all privileges on *.* to xh;
-- 查询权限
show grant for xh ; -- 查看指定用户的权限
show grant for root@loaclhost
-- root用户权限：Grant all privileges on *.* to 'root'@'localhost' with grant option 
Grant all privileges on *.* to 'root'@'localhost' with grant option 
-- 撤销权限 revoke 哪些权限，在哪个库撤销，给谁撤销
revoke all privileges on *.* from xh;
-- 删除用户
drop user xh;

MYSQL备份

为啥要备份：

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

MYSQL数据库备份的方式：

直接拷贝物理文件
在可视化编辑器中手动导出
使用命令行导出 mysqldump 命令行导出。

# 导出数据库，windows或者linux导出
# -p 后密码 ，数据库名 ，表名 >D:/文件名.sql
mysqldump -hlocalhost -uroot -pa test test1 >D:/a.sql
# 导入数据库
mysql -uroot -pa 库名 < 备份文件

use test;

source d:/文件名.sql;

数据库规范

为啥需要设计？

==当数据库比较复杂的时候，我们需要设计==

一个糟糕的数据库设计：

存在数据沉余，浪费空间
数据插入和删除都会比较麻烦，异常【屏蔽使用物理外键】
程序的性能很差

良好的数据库设计：

节省内存空间。
保证数据库的完整性。
方便我们进行开发。

软件开发中，关于数据库设计

分析需求：分析业务和需要处理的数据库的需求。
概要设计：设计关系图E-R图。

用个人博客：

收集用户信息：
- 用户表（用户登录注册，用户信息，写博客，创建分类）
- 分类表（类型编号，类型名，类型图片）
- 文章表(文章id,文章名，文章类型，文章内容，作者)
- 友链表（）
- 自定义表（）

三大范式

为什么需要数据规范化

信息重复
更新异常
插入异常
- 无法正常显示信息
删除异常
- 丢失有效的信息

三大范式

第一范式：保证原子性，也就是每一列都不可再分。

第二范式：必须先满足第一范式，然后才能判断是否满足第二范式，确保==每张表只干一件事==

第三范式：确保满足第二范式的基础下，确保每张表每一列的数据都和主键直接相关，而不是间接相关。

规范性和性能问题

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

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

JDBC

连接数据库的一个包，可以通过这个包，我们通过包提供的封装类，完成连接数据库指令操作。

创建一个JDBC

// 连接mysql

public class JDBC{
    public static void main(String [] args) throws ClassNotFoundException{
        //1加载驱动
        Class.forName("com.mysql.jdbc.Driver");//固定语法，加载驱动，注意mysql5.0和8.0直接驱动连接有差别.5.0的不需要有cj，也就是com.mysql.jdbc.Driver,但是8.0的是com.mysql.cj.jdbc.Driver
        
        //2用户信息，url
        String url = "jdbc:mysql://localhost:3306/数据库名？useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "a"; 
        //3连接成功,数据库对象 Connection 代表数据库
       Connection connection = DriverManager.getConnection(url,username,password); 
        //4执行sql对象  Statement 执行sql对象的
     Statement statement = connection.createStatement();   
        //执行sql对象去执行sql的可能存在的结果
        String sql = "你要写的sql语句"

        ResuletSet resultSet = statement.executeQuery(sql);//返回结果集，结果集中封装了我们全部查询出来的结果
        while(resultSet.next()){
            //以下全为表中的列字段
            System.out.println("id= " resultSet.getObject("id"));
               System.out.println("name= " resultSet.getObject("name"));
               System.out.println("pwd= " resultSet.getObject("pwd"));
               System.out.println("email= " resultSet.getObject("email"));
               System.out.println("birth= " resultSet.getObject("brithday"));
            
        }
        
                //释放连接，关闭。
        resultSet.close();
        statement.close();
        connection.close();

    }
}

步骤总结：

加载驱动
连接数据库DriverManager
获取执行sql的对象Statement
返回获得的结果集（list）
释放连接

DriverManager

Class.forName("com.mysql.jdbc.Driver");

   Connection connection = DriverManager.getConnection(url,username,password);//连接数据库
//事务以及自动提交都可以通过这个操作

Url

String url = "jdbc:mysql://localhost:3306/数据库名?useUnicode=true&characterEncoding=utf8&useSSL=true";
// mysql 3306 
// jdbc:mysql://主机地址：端口号/数据库名？参数1&参数2.。。。
//oracle 1521
//jdbc:oracle:thin:@localhost:1521:表名？

Statement

 //4执行sql对象  Statement 执行sql对象的
    Statement statement = connection.createStatement();   
statement.executeQuery();// 查询操作返回ResultSet
statement.execute();//执行任何Sql
statement.executeUpdate()；//更新，插入删除，都是通过这个。

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

获得指定的数据类型

   ResuletSet resultSet = statement.executeQuery(sql);//返回结果集，结果集中封装了我们全部查询出来的结果
//如果不知道列类型的话我们就使用
resultSet.getObject();
//知道的话
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
....

遍历指针

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

释放资源:必须要做的

1
2
3

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

工具类

创建一个JDBC链接的工具类（oracle）


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DBHelper {
	//加载成功，在整个程序中，只需要加载在一次就好了
    //放到静态快里边：因为静态快，自动执行，并且只执行一次
	static {
		try {
			Class.forName(MyProperties.getInstance().getProperty("driverClass"));
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}//静态方法，可以不实例化，而直接调用
		
	}

	//获取连接这一段代码，太长了，对此我们封装一下
	public Connection getCon() {
		Connection conn = null;
		//在这里，连接地址，用户名，和密码，对于不同的用户，可能不一样
		//我们可以将这个三个东西，作为配置项，配置到程序中
		try {
			conn = DriverManager.getConnection(MyProperties.getInstance().getProperty("url"),
					MyProperties.getInstance());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}
	/**
	 * 增删改
	 * @param sql 要执行的语句
	 * @param params 要注入的参数
	 * @return
	 */
	public int doUpdate(String sql,List<Object> params) {
		//定义返回值
		int result = -1;
		try {
			//获取连接
			Connection conn = getCon();
			//创建预处理语句对象
			PreparedStatement ps = conn.prepareStatement(sql);
			//如果有问号，有参数，要注入参数
			doParams(ps,params);
			//执行
			result = ps.executeUpdate();
			//关闭各种连接，接口
			closeAll(conn,null,ps);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//最终返回数据
		return result;
	}
	
	/**
	 * 查询语句
	 * @param sql
	 * @param params
	 * @return
	 */
	public List<Map<String,Object>> findAll(String sql,List<Object>params){
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		try {
			//获取连接
			Connection conn = getCon();
			//创建预处理语句对象
			PreparedStatement ps = conn.prepareStatement(sql);
			//如果有问号，有参数，要注入参数
			doParams(ps, params);
			//执行这里开始不一样
			ResultSet rs = ps.executeQuery();
			//我们要将resultSet里边的值，转化为List<Map<String,Object>>
			//Map<String,Object> 键值对     键  ： 表的字段  值:这个字段对应的值
			ResultSetMetaData rsmd =rs.getMetaData();
			//我们将所有的列名，存到一个数组中
			String [] cnames = new String [rsmd.getColumnCount()];
			for(int i =0 ;i<cnames.length;i++) {
				cnames[i] = rsmd.getColumnName(i+1);
			}
			//开始获得数据
			while (rs.next()) {
				//创建map对象
				Map<String,Object> map = new HashMap<String, Object>();
				for(int i =0;i<cnames.length;i++) {
				
					if(rs.getObject(cnames[i])== null){
						map.put(cnames[i].toLowerCase(), "");
						continue;
					}
					if("oracle.sql.BLOB".equals(rs.getObject(cnames[i]).getClass().getName())){
						map.put(cnames[i].toLowerCase(), rs.getBytes(cnames[i]));
					}else{
					//键  转换为小写     值  
	map.put(cnames[i].toLowerCase(),rs.getObject(cnames[i]));
				    }
					
				}
				//将map添加到list中
				list.add(map);
			}
			//关闭
			closeAll(conn,rs,ps);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
		
		
		}
	
	public List<Map<String,Object>> findAll(String sql,Object...params){
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		try {
			//获取连接
			Connection conn = getCon();
			//创建预处理语句对象
			PreparedStatement ps = conn.prepareStatement(sql);
			//如果有问号，有参数，要注入参数
			doParams(ps, params);
			
			//执行这里开始不一样
			ResultSet rs = ps.executeQuery();
			//我们要将resultSet里边的值，转化为List<Map<String,Object>>
			//Map<String,Object> 键值对     键  ： 表的字段  值:这个字段对应的值
			ResultSetMetaData rsmd =rs.getMetaData();
			//我们将所有的列名，存到一个数组中
			String [] cnames = new String [rsmd.getColumnCount()];
			for(int i =0 ;i<cnames.length;i++) {
				cnames[i] = rsmd.getColumnName(i+1);
			}
			//开始获得数据
			while (rs.next()) {
				//创建map对象
				Map<String,Object> map = new HashMap<String, Object>();
				for(int i =0;i<cnames.length;i++) {
					// 键          转为小写    值
					//对于blob 的值，我们这里要区分一下
					if(rs.getObject(cnames[i])== null){
						map.put(cnames[i].toLowerCase(), "");
						continue;
					}
//					System.out.println(rs.getObject(cnames[i]).getClass().getName());
					
					
					if("oracle.sql.BLOB".equals(rs.getObject(cnames[i]).getClass().getName())){
						map.put(cnames[i].toLowerCase(), rs.getBytes(cnames[i]));
					}else{
					//键  转换为小写     值  
					map.put(cnames[i].toLowerCase(),rs.getObject(cnames[i]));
				    }
				}
				//将map添加到list中
				list.add(map);
			}
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
		}
	
	//注入参数
	private void doParams(PreparedStatement ps, List<Object> params) {
		if(ps != null &&params!= null&& params.size()>0) {
			//循环注入
			for(int i = 0;i<params.size();i++) {
				try {
					ps.setObject(i+1, params.get(i));
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		
	}
	
	//重载                                                                                                                      ...量词参数  代表任意个参数 本质是数组
	private void doParams(PreparedStatement ps, Object...params) {
		if(ps != null && params.length>0) {
			//循环注入
			for(int i = 0;i<params.length;i++) {
				try {
					ps.setObject(i+1, params[i]);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}		
	}
	
	public int doUpdate(String sql,Object... params) {
		//定义返回值
		int result = -1;
		try {
		//获取连接
		Connection conn = getCon();
		//创建预处理语句对象
			PreparedStatement ps = conn.prepareStatement(sql);
			//如果有问号，有参数，要注入参数
			doParams(ps,params);
			//执行
			result = ps.executeUpdate();
			//关闭各种连接，接口
			closeAll(conn,null,ps);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		//最终返回数据
		return result;
		
    }
	
	
      private void closeAll(Connection conn, ResultSet rs, PreparedStatement ps) {
    	  if(conn != null ) {
    		  try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    	  }
    	  if(rs != null ) {
    		  try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    	  }
    	  if(ps != null ) {
    		  try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    	  }
    	 }
}

工具类（单例）(Oracle)

public class MyProperties extends Properties{
	private static MyProperties myProperties;
	
	//构造函数私有化  单例模式
	//单例  只有一个实例
	private MyProperties(){					//装载				通过数据流获得的资源		配置文件
		InputStream is = MyProperties.class.getClassLoader().getResourceAsStream("db.properties");
		
		//load 装载配置文件  void java.util.Properties.load(InputStream inStream) throws IOException
		try {
			this.load(is);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	//单例模式设计之后 ，一定要有一个公有的方法，让其他对象访问到这个属性
	public static MyProperties getInstance(){
		if( myProperties  == null ){
			myProperties = new MyProperties();
		}
		return myProperties;
	}
	
}

db.properties

#db.properties
driverClass=oracle.jdbc.OracleDriver
url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
user=scott
password=a

#mysql连接方式
#driverClass=com.mysql.jdbc.Driver
#url=jdbc:mysql://127.0.0.1:3306/res
#user=root
#password=a

工具类（Mysql,DBHelper）

/**
 * 封装  数据库操作的工具类
 * @author 38929
 * @date  2021年5月29日 下午4:43:06
 */
public class DBHelper {
	
	//配置信息
	//ORACLE
	//private static String driverClassName = "oracle.jdbc.driver.OracleDriver";
	//private String url = "jdbc:oracle:thin:@localhost:1521:ORCL";
	//private String user = "scott";
	//private String password = "a";
	
	//MySQL

	//相关对象
	private Connection conn = null;//连接对象
	private PreparedStatement pstmt = null;//预编译
	private ResultSet rs = null;//结果集
			
	//只在类中第一次加载才执行 且 执行一次      ->   当使用JNDI连接数据库时  可以注释该静态加载 
	static {
		//2、加载并注册依赖oracle.jdbc.driver.OracleDriver.class
		try {
			Class.forName( ReadConfig.getInstance().getProperty("driverClassName"));
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	//建立连接
	private Connection getConn() {
		//3、建立连接     url  uname  pwd
		//url 统一资源定位符   jdbc:oracle:thin:@数据库的IP地址:1521:实例名    127.0.0.1 | localhost:1521:ORCL 
		//ctrl 1 + 2 -> L
		try {
			//conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "a");
			conn = DriverManager.getConnection(ReadConfig.getInstance().getProperty("url"), ReadConfig.getInstance());
		
			//从服务器 context.xml 中 上下文获取配置的DataSource  
			//Context context= new InitialContext();
			//从命名目录接口中根据资源名查询  前面 java:comp/env/blog 固定的   类似于协议
			//DataSource dataSource =  (DataSource)context.lookup("java:comp/env/blog");
			//从数据库连接池获取一个空闲的连接 
			//conn = dataSource.getConnection();
		/*} catch (NamingException e) {
			e.printStackTrace();*/
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	/**
	 * 关闭资源
	 * @param rs
	 * @param pstmt
	 * @param conn
	 */
	@SuppressWarnings("unused")
	private void closeAll(ResultSet rs, PreparedStatement pstmt, Connection conn) {
		//7、关闭资源  先开启的后关闭  后开启的先关闭  结果集 -> 语句块 -> 连接
		if( rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if( pstmt != null) {
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if( conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 更新操作   Object ... params  不定参数   类似于数组  万物皆对象
	 * @param sql  要执行的更新语句   可以 insert update delete 
	 * @param params 要执行的SQL语句中的占位符？ 所对应参数的值 
	 * @return
	 */
	public int update(String sql, Object ... params) {
		int result = -1;//定义默认的范围值
		
		try {
			conn = this.getConn();//获取连接
			pstmt = conn.prepareStatement(sql);//预编译对象装载SQL
			this.setParams(pstmt, params);//需要给占位符注入参数
			result = pstmt.executeUpdate();//执行更新语句
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * 给预编译语句块中占位符 赋值 设置参数
	 * @param pstmt   预编译对象
	 * @param params  要执行的SQL语句中的占位符？ 所对应参数的值 
	 */
	private void setParams(PreparedStatement pstmt, Object ... params) {
		//去空判断  说明没有参数给我   也就是执行的SQL语句中没有占位符
		if( null == params || params.length <= 0) {
			return;
		}
		//有参数 则循环参数  给预编译语句块中占位符 赋值
		//先获取参数的长度  节省资源 提高性能
		for(int i = 0, len = params.length; i < len; i++) {
			//我不清楚占位符 对应的详细数据类型    万物皆对象
			try {
				pstmt.setObject( i + 1, params[i]);// % +v+ %
			} catch (SQLException e) {
				e.printStackTrace();
				//项目可以使用日志来记录
				System.out.println("第" + (i+1) + "个参数注值失败...");
			}
		}
	}
	
	/**
	 * 单条查询 返回一条记录   select * from userinfo where user_name = ? and user_pwd = ?
	 * @param sql  查询SQL  
	 * @param params 查询参数
	 * @return map  一条记录
	 */
	public Map<String, Object> findSingle(String sql, Object ... params){
		Map<String, Object> map = null;
		try {
			conn = this.getConn();//获取连接
			pstmt = conn.prepareStatement(sql);//预编译对象装载SQL
			this.setParams(pstmt, params);//需要给占位符注入参数
			rs = pstmt.executeQuery();//执行更新语句
			
			//现获取所有的列名
			List<String> columnNames = this.getAllColumnNames(rs);
			if( rs.next()) { //处理结果集
				map = new HashMap<String, Object>();
				//map.put("user_id", rs.getInt("user_id"));
				//map.put("user_name", rs.getString("user_name"));
				//map.put("user_pwd", rs.getString("user_pwd"));
				//如果换了查询的表  还会有这些列吗？  select * from emp; 
				//如何查询表所对应各个列的名称？  请去API 先行查看 rs.getMetaData()
				
				Object value = null;//列所对应的值
				String type = "";//列所对应的值的类型
				
				//增强for
				for(String columnName : columnNames) {
					//列对应的类型不确定
					//map.put(columnName, rs.getObject(columnName));
					value = rs.getObject(columnName);
					//判空
					if( null == value) {
						map.put(columnName, value);
						continue;
					}
					type = value.getClass().getName(); //获取类型
					//System.out.println(type); //oracle.sql.BLOB   java.math.BigDecimal
					//判断类型
					//TODO 如果是Blob类型 该怎么办 ?  图片的处理？？? 想办法获取对应的数据类型？
					if("oracle.sql.BLOB".equals(type)) {
						//获取对应类型数据
						Blob blob = rs.getBlob(columnName);
						//获取对应二进制流操作
						try(InputStream is = blob.getBinaryStream()){
							byte [] bt = new byte[ (int)blob.length()];
							is.read(bt);
							map.put(columnName, bt);//存入字节数组
						}catch(IOException e) {
							e.printStackTrace();
						}
					}else {
						map.put(columnName, value);
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return map;
	}
	
	/**
	 * JDBC2.0 获取所有的列名
	 * @param rs
	 * @return
	 */
	public List<String> getAllColumnNames(ResultSet rs){
		//存储列的集合
		List<String> list = new ArrayList<String>();
		try {
			//getMetaData() 获取此 ResultSet 对象的列的编号、类型和属性
			ResultSetMetaData rsmd = rs.getMetaData();
			//获取列的数量
			int count = rsmd.getColumnCount();
			//列的范围确定 循环
			for(int i = 1; i <= count; i++){
				//获取对应列的列名
				list.add(rsmd.getColumnName(i).toLowerCase());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 多行查询 返回多条  select * from emp; 
	 * @param sql  查询SQL  
	 * @param params 查询参数
	 * @return list 
	 */
	public List<Map<String, Object>> findMultiple(String sql, Object ... params){
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		Map<String, Object> map = null;
		try {
			conn = this.getConn();//获取连接
			pstmt = conn.prepareStatement(sql);//预编译对象装载SQL
			this.setParams(pstmt, params);//需要给占位符注入参数
			rs = pstmt.executeQuery();//执行更新语句
			
			//现获取所有的列名
			List<String> columnNames = this.getAllColumnNames(rs);
			
			while( rs.next()) { //处理结果集
				map = new HashMap<String, Object>();
				Object value = null;//列所对应的值
				String type = "";//列所对应的值的类型
				
				//增强for
				for(String columnName : columnNames) {
					//列对应的类型不确定
					//map.put(columnName, rs.getObject(columnName));
					value = rs.getObject(columnName);
					//判空
					if( null == value) {
						map.put(columnName, value);
						continue;
					}
					type = value.getClass().getName(); //获取类型
					//System.out.println(type); //oracle.sql.BLOB   java.math.BigDecimal
					//判断类型
					//TODO 如果是Blob类型 该怎么办 ?  图片的处理？？? 想办法获取对应的数据类型？
					if("oracle.sql.BLOB".equals(type)) {
						//获取对应类型数据
						Blob blob = rs.getBlob(columnName);
						//获取对应二进制流操作
						try(InputStream is = blob.getBinaryStream()){
							byte [] bt = new byte[ (int)blob.length()];
							is.read(bt);
							map.put(columnName, bt);//存入字节数组
						}catch(IOException e) {
							e.printStackTrace();
						}
					}else {
						map.put(columnName, value);
					}
				}
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 *  以对象的方式 将将查询结果返回
	 *   c.newInstance() -> new AdminInfo() 
	 *   m.invoke(obj, value)  激活m方法
	 *   获取当前class实例中所有的方法和属性
	 * @param c		
	 * @param <T> 限定类型
	 * @param sql  查询SQL 
	 * @param params 注入的参数 
	 * @return
	 */
	public <T>List<T> findMultiple(Class c,String sql, Object ... params){
		List<T> list = new ArrayList<T>();
		System.out.println(1111);
		try {
			conn = this.getConn();//获取连接
			pstmt = conn.prepareStatement(sql);//预编译对象装载SQL
			this.setParams(pstmt, params);//需要给占位符注入参数
			rs = pstmt.executeQuery();//执行更新语句
			
			//现获取所有的列名
			List<String> columnNames = this.getAllColumnNames(rs);
			
			T t = null;//声明一个对象
			Object value = null;//列所对应的值
			String typeName = "";//列所对应的值的类型
			
			//通过反射来获取类中所有的方法 methods
			Method [] methods = c.getDeclaredMethods();
			
			while( rs.next()) { //处理结果集
				//创建对象
				t =  (T)c.newInstance();//调用无参数的构造方法  AdminInfo admin = new AdminInfo();
				
				//增强for  列
				for(String columnName : columnNames) {
					//列对应的类型不确定
					value = rs.getObject(columnName);
					
					//判空
					if( null == value) { // 数据库无数据
						continue;
					}
					
					//循环类中所有的方法
					for(Method method : methods) {
						//是否有对应setXXX 方法名   set + columnName -> method的名字
						String name = "set" + columnName;
						//获取列对应值的类型
						typeName = value.getClass().getName();
						//System.out.println( typeName );
						//找到对应的方法名
						if( name.equalsIgnoreCase( method.getName())) {
							//判断数据类型
							if("java.math.BigDecimal".equals(typeName)) {
								try {
									method.invoke( t, rs.getDouble( columnName ));
								}catch(Exception e) {
									method.invoke( t, rs.getString( columnName ));
								}
							}else if("java.lang.String".equals(typeName)) {
								method.invoke( t, rs.getString( columnName ));
							}else if("java.lang.Double".equals(typeName)) {
								method.invoke( t, rs.getDouble( columnName ));
							}else if("java.lang.Integer".equals(typeName)) {
								method.invoke( t, rs.getInt( columnName ));
							}else if("java.lang.Date".equals(typeName)) {
								//MySQL 中date 数据类型 转换成JavaBean 对象中使用String 
								method.invoke( t, rs.getString( columnName ));
							}else if("oracle.sql.BLOB".equals(typeName)) {  //TODO 如果是Blob类型 该怎么办 ?  图片的处理？？? 想办法获取对应的数据类型？
								//获取对应类型数据
								Blob blob = rs.getBlob(columnName);
								//获取对应二进制流操作
								try(InputStream is = blob.getBinaryStream()){
									byte [] bt = new byte[ (int)blob.length()];
									is.read(bt);
									method.invoke( t, bt);
								}catch(IOException e) {
									e.printStackTrace();
								}
							}else if("oracle.sql.CLOB".equals(typeName)){
								Reader in = rs.getCharacterStream(columnName);
								BufferedReader br = new BufferedReader( in );
								StringBuffer sb =  new StringBuffer();
								try {
									String str = br.readLine();//每次读取一行数据
									while( null != str ) {
										sb.append( str );
										str = br.readLine();
									}
									method.invoke( t, sb.toString());
								} catch (IOException e) {
									e.printStackTrace();
								}
							}else {
								//TODO 后期需要 自行扩展
							}
						}
					}
				}
				list.add(t); //设置对象到list集合中
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}catch (InstantiationException | IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 *  以对象的方式 将将查询结果返回
	 *   c.newInstance() -> new AdminInfo() 
	 *   m.invoke(obj, value)  激活m方法
	 *   获取当前class实例中所有的方法和属性
	 * @param c		
	 * @param <T> 限定类型
	 * @param sql  查询SQL 
	 * @param params 注入的参数 
	 * @return 
	 * @return
	 */
	public <T> T findSingle(Class c,String sql, Object ... params){
		T t = null;//声明一个对象
		try {
			conn = this.getConn();//获取连接
			pstmt = conn.prepareStatement(sql);//预编译对象装载SQL
			this.setParams(pstmt, params);//需要给占位符注入参数
			rs = pstmt.executeQuery();//执行更新语句
			
			//现获取所有的列名
			List<String> columnNames = this.getAllColumnNames(rs);
			
			Object value = null;//列所对应的值
			String typeName = "";//列所对应的值的类型
			
			//通过反射来获取类中所有的方法 methods
			Method [] methods = c.getDeclaredMethods();
			
			if( rs.next()) { //处理结果集
				//创建对象
				t =  (T)c.newInstance();//调用无参数的构造方法  AdminInfo admin = new AdminInfo();
				//增强for  列
				for(String columnName : columnNames) {
					//列对应的类型不确定
					value = rs.getObject(columnName);
					//判空
					if( null == value) { // 数据库无数据
						continue;
					}
					//循环类中所有的方法
					for(Method method : methods) {
						
						//是否有对应setXXX 方法名   set + columnName -> method的名字
						String name = "set" + columnName;
						//获取列对应值的类型
						typeName = value.getClass().getName();
						//System.out.println( name + "--" + typeName );
						//找到对应的方法名
						if( name.equalsIgnoreCase( method.getName())) {
							//判断数据类型
							if("java.math.BigDecimal".equals(typeName)) {
								try {
									method.invoke( t, rs.getDouble( columnName ));
								}catch(Exception e) {
									method.invoke( t, rs.getString( columnName ));
								}
							}else if("java.lang.String".equals(typeName)) {
								method.invoke( t, rs.getString( columnName ));
							}else if("java.lang.Double".equals(typeName)) {
								method.invoke( t, rs.getDouble( columnName ));
							}else if("java.lang.Integer".equals(typeName)) {
								method.invoke( t, rs.getInt( columnName ));
							}else if("java.lang.Date".equals(typeName)) {
								//MySQL 中date 数据类型 转换成JavaBean 对象中使用String 
								method.invoke( t, rs.getString( columnName ));
							}else if("oracle.sql.BLOB".equals(typeName)) {  //TODO 如果是Blob类型 该怎么办 ?  图片的处理？？? 想办法获取对应的数据类型？
								//获取对应类型数据
								Blob blob = rs.getBlob(columnName);
								//获取对应二进制流操作
								try(InputStream is = blob.getBinaryStream()){
									byte [] bt = new byte[ (int)blob.length()];
									is.read(bt);
									method.invoke( t, bt);
								}catch(IOException e) {
									e.printStackTrace();
								}
							}else if("oracle.sql.CLOB".equals(typeName)){
								Reader in = rs.getCharacterStream(columnName);
								BufferedReader br = new BufferedReader( in );
								StringBuffer sb =  new StringBuffer();
								try {
									String str = br.readLine();//每次读取一行数据
									while( null != str ) {
										sb.append( str );
										str = br.readLine();
									}
									method.invoke( t, sb.toString());
								} catch (IOException e) {
									e.printStackTrace();
								}
							}else {
								//TODO 后期需要 自行扩展
							}
						}
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}catch (InstantiationException | IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
			e.printStackTrace();
		}
		return t;
	}
	   public int total(String sql,Object...params) {
		int result = 0;
		try {
			conn = this.getConn();
			pstmt = conn.prepareStatement(sql);
			//设置参数
			setParams(pstmt,params);
			//执行语句块
			rs= pstmt.executeQuery();
			
			//处理结果集
			if(rs.next()) {
				result = rs.getInt(1);//列索引第一列
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			this.closeAll(rs, pstmt, conn);
		}
		return result;
	}
}

工具类（mysql,也是单例）


public class ReadConfig extends Properties{
	private static final long serialVersionUID = 1560020482133144083L;
	//饿汉单例   提前实例化对�?
	private static ReadConfig instance = new ReadConfig();

	//构造方法私有化  
	private ReadConfig() {
		//想办法读取自己的配置文件   
		//JDK1.7 会自动关闭且�?定关闭资�?   try - with -resources
		try(InputStream is = this.getClass().getClassLoader().getResourceAsStream("db.properties")){
			//业务代码  流处�? 
			this.load(is); //读取配置文件
		}catch(IOException e) {
			e.printStackTrace();
		}
	}
	//因为构�?�私有化就意味着外部无法实例化该对象 必须对外提供�?个获取实例的方法  并在内部提前实例�? 
	//又因为外部无法是实例�? 只能通过类名方法 该方�? �?声明为静态的 
	public  static ReadConfig getInstance() {
		return instance;
	}
}

db.properties

#MySQL5
#driverClassName=com.mysql.jdbc.Driver
#url=jdbc:mysql://localhost:3306/blog
#user=root
#password=a

#MySQL8
driverClassName=com.mysql.cj.jdbc.Driver

url=jdbc:mysql://127.0.0.1:3306/数据库?useOldAliasMetadataBehavior=true&useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8
user=root
password=a


#Oracle
#driverClassName=oracle.jdbc.driver.OracleDriver
#url=jdbc:oracle:thin:@localhost:1521:orcl
#user=scott
#password=a

SQL注入

public class AdminInfoDAOImpl implements IAdminInfoDAO {

	@Override
	public AdminInfo login(String aname, String pwd) {
         DBHelper db = new DBHelper();
         String sql = "select * from admininfo where aname = ? and pwd = md5(?)";
         return db.findSingle(AdminInfo.class,sql,aname,pwd);
	
	
	}

	@Override
	public int add(String aname, String pwd, String photo) {
		DBHelper db = new DBHelper();
        String sql = "insert into admininfo value(0,?,md5(?),?)";
        return db.update(sql, aname,pwd,photo);
	}

	//默认不查询密码 如果需要查询 将来界面渲染密码  必须摘码显示 123456 -> 1***6
	@Override
	public List<AdminInfo> findAll() {
		DBHelper db = new DBHelper();
        String sql = "select aid,aname,photo from admininfo order by aid desc";
        return db.findMultiple(AdminInfo.class, sql);
	}
}

==sql注入漏洞也就是sql拼接==

PreparedStatement：可以预防SQL注入

上述我们使用的工具类就是使用PreparedStatement来的。

事务：

/**
 * 更新操作   Object ... params  不定参数   类似于数组  万物皆对象
 * @param sql  要执行的更新语句   可以 insert update delete 
 * @param params 要执行的SQL语句中的占位符？ 所对应参数的值 
 * @return
 */
public int update(String sql, Object ... params) {
	int result = -1;//定义默认的范围值
	
	try {
		conn = this.getConn();//获取连接
		//开启事务   conn.setAutoCommit(false);
		pstmt = conn.prepareStatement(sql);//预编译对象装载SQL
		this.setParams(pstmt, params);//需要给占位符注入参数
		result = pstmt.executeUpdate();//执行更新语句
		
	  //事务提交	conn.commit();
	
	} catch (SQLException e) {
		//如果失败事务提交失败则回滚
	/*	try {
			conn.rollback();
		}catch(SQLException e){
			e.printStackTrace();
		}
		*/
		e.printStackTrace();
	}
	return result;
}

数据库链接池，池化技术

数据库链接—执行完毕—释放

连接—释放十分浪费资源

池化技术：

准备一些预先的资源，过来就连接预先准备好的

需要的包下载：https://blog.csdn.net/qq_51178489/article/details/118805942


import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;

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

public class JDBCUtils_C3P0 {

    private static DataSource dataSource=null;
    static {
        try {
            //xml数据源直接加载不用反射
//            dataSource= new ComboPooledDataSource();     //无参数时用默认数据源
            dataSource=new ComboPooledDataSource("MySQL"); //用配置name="MySQL"的数据源   也可以datasource.setMaxPoolSize();等
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {     //提供连接
        return dataSource.getConnection();         //数据源中获取连接，自动装好参数比如账号密码url
    }

    public static void release(Connection con, PreparedStatement st, ResultSet re){   //提供关闭
        if(re!=null) {
            try {
                re.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(st!=null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(con!=null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
}

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!-- 默认配置，如果没有指定则使用这个配置 -->
    <default-config>
        <property name="user">root</property>
        <property name="password">a</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/school</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="checkoutTimeout">30000</property>
        <property name="idleConnectionTestPeriod">30</property>
        <property name="initialPoolSize">3</property>
        <property name="maxIdleTime">30</property>
        <property name="maxPoolSize">100</property>
        <property name="minPoolSize">2</property>
        <property name="maxStatements">200</property>
    </default-config>


    <!-- 命名的配置,可以通过方法调用实现 -->
    <named-config name="MySQL">
        <property name="user">root</property>
        <property name="password">123456</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/school</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <!-- 如果池中数据连接不够时一次增长多少个 -->
        <property name="acquireIncrement">5</property>
        <!-- 初始化数据库连接池时连接的数量 -->
        <property name="initialPoolSize">20</property>
        <!-- 数据库连接池中的最大的数据库连接数 -->
        <property name="maxPoolSize">25</property>
        <!-- 数据库连接池中的最小的数据库连接数 -->
        <property name="minPoolSize">5</property>
    </named-config>
</c3p0-config>