JDBC编程--MYSQL

JDBC编程–MYSQL

1.初识数据库

1.1,为什么要学数据库

1. 岗位需求
2. 现在的世界，大数据时代
3. 被迫存数据
4. 数据库是所有软件体系中最核心的存在：DBA

1.2,什么是数据库

1. 数据库（DB,DataBase）
2. 概念：数据仓库，软件（navicat）,操作系统（win10，linux，max）
3. 作用：存数据，管理数据

1.3.数据库分类

• 关系型数据库：（SQL“结构化查询语句”）

  • MySql,Oracle,Sql Sever,DB2,Sqllite
  • 通过表和表之间，行和列之间的关系进行数据的存储
    • 员工表,考勤表

• 非关系型数据库：（NotSQL）Not Only

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

DBMS（数据库管理系统）

• 数据库的管理软件（Navicat）,有逻辑的管理数据，维护和保护数据
• MySQL,数据库管理系统

1.4 MySQL

MySQL是一种开放源代码的关系型数据库管理系统

前世：瑞典Sql AB公司

今生：属于Oracle

MySQL是一种开放源代码的关系型数据库管理系统（RDBMS），使用最常用的数据库管理语言–结构化查询语言（SQL）进行数据库管理。

体积小，速度快，总体拥有成本滴，招人成本低，所有必须会

中小型网站，或者大型网站，集群

2.基本数据库命令

2.1.命令行

MYSQL命令行语句

mysql -uroot -p123456--链接数据库

# 查看所有的数据库
SHOW DATABASES ;
# 创建一个数据库
CREATE DATABASE 数据库名;
# 删除一个数据库
DROP DATABASE 数据库名;
# 使用这个数据库
USE 数据库名;

表

# 查看所有的表
SHOW TABLES ;
# 创建一个表
CREATE TABLE n(id INT, name VARCHAR(10));
CREATE TABLE m(id INT, name VARCHAR(10), PRIMARY KEY (id), FOREIGN KEY (id) REFERENCES n(id), UNIQUE (name));
CREATE TABLE m(id INT, name VARCHAR(10));
# 直接将查询结果导入或复制到新创建的表
CREATE TABLE n SELECT * FROM m;
# 新创建的表与一个存在的表的数据结构类似
CREATE TABLE m LIKE n;
# 创建一个临时表
# 临时表将在你连接MySQL期间存在。当断开连接时，MySQL将自动删除表并释放所用的空间。也可手动删除。
CREATE TEMPORARY TABLE l(id INT, name VARCHAR(10));
# 直接将查询结果导入或复制到新创建的临时表
CREATE TEMPORARY TABLE tt SELECT * FROM n;
# 删除一个存在表
DROP TABLE IF EXISTS 表名;
# 更改存在表的名称
ALTER TABLE n RENAME 表名;
RENAME TABLE n TO m;
# 查看表的结构(以下五条语句效果相同）
DESC n;   # 因为简单，所以建议使用
DESCRIBE n;
SHOW COLUMNS IN n;
SHOW COLUMNS FROM n;
EXPLAIN n;
# 查看表的创建语句
SHOW CREATE TABLE n;

表的结构-数据

# 添加字段
ALTER TABLE n ADD age VARCHAR(2) ;
# 删除字段
ALTER TABLE n DROP age;
# 更改字段属性和属性
ALTER TABLE n CHANGE age a INT;
# 只更改字段属性
ALTER TABLE n MODIFY age VARCHAR(7) ;

# 增加数据
INSERT INTO n VALUES (1, 'tom', '23'), (2, 'john', '22');
INSERT INTO n SELECT * FROM n;  # 把数据复制一遍重新插入
# 删除数据
DELETE FROM n WHERE id = 2;
# 更改数据
UPDATE n SET name = 'tom' WHERE id = 2;
# 数据查找
SELECT * FROM n WHERE name LIKE '%h%';
# 数据排序(反序)
SELECT * FROM n ORDER BY name, id DESC ;

键

# 添加主键
ALTER TABLE n ADD PRIMARY KEY (id);
ALTER TABLE n ADD CONSTRAINT pk_n PRIMARY KEY (id);   # 主键只有一个，所以定义键名似乎也没有什么用
# 删除主键
ALTER TABLE n DROP PRIMARY KEY ;
# 添加外键
ALTER TABLE m ADD FOREIGN KEY (id) REFERENCES n(id);    # 自动生成键名m_ibfk_1
ALTER TABLE m ADD CONSTRAINT fk_id FOREIGN KEY (id) REFERENCES n(id);   # 使用定义的键名fk_id
# 删除外键
ALTER TABLE m DROP FOREIGN KEY `fk_id`;
# 修改外键
ALTER TABLE m DROP FOREIGN KEY `fk_id`, ADD CONSTRAINT fk_id2 FOREIGN KEY (id) REFERENCES n(id);    # 删除之后从新建
# 添加唯一键
ALTER TABLE n ADD UNIQUE (name);
ALTER TABLE n ADD UNIQUE u_name (name);
ALTER TABLE n ADD UNIQUE INDEX u_name (name);
ALTER TABLE n ADD CONSTRAINT u_name UNIQUE (name);
CREATE UNIQUE INDEX u_name ON n(name);
# 添加索引
ALTER TABLE n ADD INDEX (age);
ALTER TABLE n ADD INDEX i_age (age);
CREATE INDEX i_age ON n(age);
# 删除索引或唯一键
DROP INDEX u_name ON n;
DROP INDEX i_age ON n;

视图

# 创建视图
CREATE VIEW v AS SELECT id, name FROM n;
CREATE VIEW v(id, name) AS SELECT id, name FROM n;
# 查看视图(与表操作类似)
SELECT * FROM v;
DESC v;
# 查看创建视图语句
SHOW CREATE VIEW v;
# 更改视图
CREATE OR REPLACE VIEW v AS SELECT name, age FROM n;
ALTER VIEW v AS SELECT name FROM n ;
# 删除视图
DROP VIEW IF EXISTS v;

联接

# 内联接
SELECT * FROM m INNER JOIN n ON m.id = n.id;
# 左外联接
SELECT * FROM m LEFT JOIN n ON m.id = n.id;
# 右外联接
SELECT * FROM m RIGHT JOIN n ON m.id = n.id;
# 交叉联接
SELECT * FROM m CROSS JOIN n;   # 标准写法
SELECT * FROM m, n;
# 类似全连接full join的联接用法
SELECT id,name FROM m
UNION
SELECT id,name FROM n;

函数

# 聚合函数
SELECT count(id) AS total FROM n;   # 总数
SELECT sum(age) AS all_age FROM n;   # 总和
SELECT avg(age) AS all_age FROM n;   # 平均值
SELECT max(age) AS all_age FROM n;   # 最大值
SELECT min(age) AS all_age FROM n;   # 最小值
# 数学函数
SELECT abs(-5);   # 绝对值
SELECT bin(15), oct(15), hex(15);   # 二进制，八进制，十六进制
SELECT pi();   # 圆周率3.141593
SELECT ceil(5.5);   # 大于x的最小整数值6
SELECT floor(5.5);   # 小于x的最大整数值5
SELECT greatest(3,1,4,1,5,9,2,6);   # 返回集合中最大的值9
SELECT least(3,1,4,1,5,9,2,6);    # 返回集合中最小的值1
SELECT mod(5,3);    # 余数2
SELECT rand();    # 返回０到１内的随机值，每次不一样
SELECT rand(5);   # 提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。
SELECT round(1415.1415);   # 四舍五入1415
SELECT round(1415.1415, 3);   # 四舍五入三位数1415.142
SELECT round(1415.1415, -1);    # 四舍五入整数位数1420
SELECT truncate(1415.1415, 3);    # 截短为3位小数1415.141
SELECT truncate(1415.1415, -1);   # 截短为-1位小数1410
SELECT sign(-5);    # 符号的值负数-1
SELECT sign(5);    # 符号的值正数1
SELECT sqrt(9);   # 平方根3
SELECT sqrt(9);   # 平方根3
# 字符串函数
SELECT concat('a', 'p', 'p', 'le');   # 连接字符串-apple
SELECT concat_ws(',', 'a', 'p', 'p', 'le');   # 连接用','分割字符串-a,p,p,le
SELECT insert('chinese', 3, 2, 'IN');    # 将字符串'chinese'从3位置开始的2个字符替换为'IN'-chINese
SELECT left('chinese', 4);   # 返回字符串'chinese'左边的4个字符-chin
SELECT right('chinese', 3);   # 返回字符串'chinese'右边的3个字符-ese
SELECT substring('chinese', 3);   # 返回字符串'chinese'第三个字符之后的子字符串-inese
SELECT substring('chinese', -3);   # 返回字符串'chinese'倒数第三个字符之后的子字符串-ese
SELECT substring('chinese', 3, 2);   # 返回字符串'chinese'第三个字符之后的两个字符-in
SELECT trim(' chinese ');    # 切割字符串' chinese '两边的空字符-'chinese'
SELECT ltrim(' chinese ');    # 切割字符串' chinese '两边的空字符-'chinese '
SELECT rtrim(' chinese ');    # 切割字符串' chinese '两边的空字符-' chinese'
SELECT repeat('boy', 3);    # 重复字符'boy'三次-'boyboyboy'
SELECT reverse('chinese');    # 反向排序-'esenihc'
SELECT length('chinese');   # 返回字符串的长度-7
SELECT upper('chINese'), lower('chINese');    # 大写小写 CHINESE    chinese
SELECT ucase('chINese'), lcase('chINese');    # 大写小写 CHINESE    chinese
SELECT position('i' IN 'chinese');    # 返回'i'在'chinese'的第一个位置-3
SELECT position('e' IN 'chinese');    # 返回'i'在'chinese'的第一个位置-5
SELECT strcmp('abc', 'abd');    # 比较字符串，第一个参数小于第二个返回负数- -1
SELECT strcmp('abc', 'abb');    # 比较字符串，第一个参数大于第二个返回正数- 1
# 时间函数
SELECT current_date, current_time, now();    # 2018-01-13   12:33:43    2018-01-13 12:33:43
SELECT hour(current_time), minute(current_time), second(current_time);    # 12  31   34
SELECT year(current_date), month(current_date), week(current_date);   # 2018    1   1
SELECT quarter(current_date);   # 1
SELECT monthname(current_date), dayname(current_date);   # January  Saturday
SELECT dayofweek(current_date), dayofmonth(current_date), dayofyear(current_date);    # 7   13  13
# 控制流函数
SELECT if(3>2, 't', 'f'), if(3<2, 't', 'f');    # t f
SELECT ifnull(NULL, 't'), ifnull(2, 't');    # t 2
SELECT isnull(1), isnull(1/0);    # 0 1 是null返回1，不是null返回0
SELECT nullif('a', 'a'), nullif('a', 'b');    # null a 参数相同或成立返回null，不同或不成立则返回第一个参数
SELECT CASE 2
       WHEN 1 THEN 'first'
       WHEN 2 THEN 'second'
       WHEN 3 THEN 'third'
       ELSE 'other'
       END ;     # second
# 系统信息函数
SELECT database();    # 当前数据库名-test
SELECT connection_id();   # 当前用户id-306
SELECT user();    # 当前用户-root@localhost
SELECT version();   # 当前mysql版本
SELECT found_rows();    # 返回上次查询的检索行数

用户

# 增加用户
CREATE USER 'test'@'localhost' IDENTIFIED BY 'test';
INSERT INTO mysql.user(Host, User, Password) VALUES ('localhost', 'test', Password('test'));    # 在用户表中插入用户信息，直接操作User表不推荐
# 删除用户
DROP USER 'test'@'localhost';
DELETE FROM mysql.user WHERE User='test' AND Host='localhost';
FLUSH PRIVILEGES ;
# 更改用户密码
SET PASSWORD FOR 'test'@'localhost' = PASSWORD('test');
UPDATE mysql.user SET Password=Password('t') WHERE User='test' AND Host='localhost';
FLUSH PRIVILEGES ;
# 用户授权
GRANT ALL PRIVILEGES ON *.* TO test@localhost IDENTIFIED BY 'test';
# 授予用'test'密码登陆成功的test@localhost用户操作所有数据库的所有表的所有的权限
FLUSH PRIVILEGES ;   # 刷新系统权限表,使授予权限生效
# 撤销用户授权
REVOKE DELETE ON *.* FROM 'test'@'localhost';   # 取消该用户的删除权限

存储过程

# 创建存储过程
DELIMITER //    # 无参数
CREATE PROCEDURE getDates()
  BEGIN
    SELECT * FROM test ;
  END //
CREATE PROCEDURE getDates_2(IN id INT)    # in参数
  BEGIN
    SELECT * FROM test WHERE a = id;
  END //
CREATE PROCEDURE getDates_3(OUT sum INT)    # out参数
  BEGIN
    SET sum = (SELECT count(*) FROM test);
  END //
CREATE PROCEDURE getDates_4(INOUT i INT)    # inout参数
  BEGIN
    SET i = i + 1;
  END //
DELIMITER ;
# 删除存储过程
DROP PROCEDURE IF EXISTS getDates;
# 修改存储过程的特性
ALTER PROCEDURE getDates MODIFIES SQL DATA ;
# 修改存储过程语句（删除再重建）略
# 查看存储过程
SHOW PROCEDURE STATUS LIKE 'getDates';    # 状态
SHOW CREATE PROCEDURE getDates_3;   # 语句
# 调用存储过程
CALL getDates();
CALL getDates_2(1);
CALL getDates_3(@s);
SELECT @s;
SET @i = 1;
CALL getDates_4(@i);
SELECT @i;    # @i = 2

3.三大范式

3.1.为什么要数据安全？

• 信息重复
• 更新异常
• 插入异常
  • 无法正常显示信息
• 删除异常
  • 丢失有效信息
  • 例：一个文章，你删除了文章内容，但没删除文章标题

3.2.三大范式

第一范式（1NF）

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

上面的表：家庭信息，和学校信息不满足第一范式原子性的要求，我们来进行修改：

可以看到，我们已经拆分了所有列，不能在进行拆分，满足了第一范式

第二范式（2NF）

前提：满足第一方式

每张表只描述一件事情

在上图所示的情况中，同一个订单中可能包含不同的产品，因此主键必须是“订单号”和“产品号”联合组成，

但可以发现，产品数量、产品折扣、产品价格与“订单号”和“产品号”都相关，但是订单金额和订单时间仅与“订单号”相关，与“产品号”无关，

这样就不满足第二范式的要求，调整如下，需分成两个表：

第三范式（3NF）

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

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

上表中，所有属性都完全依赖于学号，所以满足第二范式，但是“班主任性别”和“班主任年龄”直接依赖的是“班主任姓名”，

而不是主键“学号”，所以需做如下调整：

4.数据库的列类型

数字

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

字符串

• char 字符串固定长度0~255
• varchar 可变字符串0~65535
• tinytext 微型文本 2*8-1
• text 文本串 2*16-1----保存大文本

时间日期

java.util.Date

• date YYYY.MM.DD.日期
• time HH:mm:ss时间格式
• datetime YYYY.MM.DD.HH:mm:ss最常用的时间格式
• timestamp 时间戳。19701.1到现在的毫秒数！
• year 年份表示

null

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

5.数据库引擎

InnoDB存储引擎

1. 默认使用
2. 安全性高，事务的处理，多表多用户操作

MyISAM存储引擎

1. 早些年使用
2. 节约空间，速度较快

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

在物理空间存在的位置

所有的数据库文件都存在data目录下

本质还是文件的存储！

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

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

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

CHARSETT=utf8

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

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

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

character-set-server=utf8

6.修改 删除

修改

 --修改表名
alter table 表名 rename as 表名2;
--添加表字段
alter table 表名 add 字段名 int(11);
--修改约束
alter table 表名 modify 字段名 varchar(10);
--修改属性命名
alter table 表名 change 字段名 字段名1 int(5);

删除

--删除表字段
alter table 表名 Drop 字段名
--删除表
drop table [if exists] 表名

7.事务，视图，索引，备份和恢复

7.1什么是事务？

要么都成功，要么都失败

---

1. SQL执行 A给B转账 A1000—>200|B200
2. SQL执行 B收到A的钱 A800—>B400

---

将一组SQL放在一个批次中去执行

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

7.1.1.原子性**（Atomicity）**

原子性表示，这两个步骤一起成功，或者一起失败，不能只发生其中一个动作

要么都成功，要么都失败。

7.1.2.一致性**（Consistency）**

一致性表示事务完成后，符合逻辑运算

事务前后的数据完整性要保证一致（比如你手中有1000元，转来转去都是只有1000元，不可能变的）

7.1.3.隔离性**（Isolation）**

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离

7.1.4.持久性**（Durability）**

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

事务一但提交则不可逆，被持久化到数据库中！

---

7.2.事务隔离级别

7.2.1.脏读

指一个事务读取了另外一个事务未提交的数据。

7.2.2.不可重复读

在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）

7.2.3.幻读（虚读）

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

7.3.执行事务

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

--手动处理事务
set autocommit = 0 ;--关闭自动提交

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

--提交:持久化(成功！)
commit;
--回滚:回到原来的样子(失败！)
rollback;

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

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

8.索引

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

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

8.1索引的分类

在一个表中，主键索引只有一个，唯一索引可以有多个

• 主键索引（primary key）
  • 唯一标识，主键不可重复，只能有一个列为主键
• 唯一索引（unique key）
  • 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
• 常规索引（key/index）
  • 默认的，index,key关键字来设置
• 全文索引（FullText）
  • 在特定的数据库引擎下才有（MyISAM）
  • 快速定位数据

-- 索引的使用
-- 1.在创建表的时候给字段添加增加索引
-- 2.插件完毕后，增加索引

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

-- 添加一个全文索引（索引名）  列名
alter table 表名 add fulltext index `属性名`(`属性名`);

-- explain 分析sql执行的状况
explain select * from 表名;-- 分全文索引
explain select * from 表名 where match(属性名) against('属性值');-- 查找值在数据库的所占行数

8.2.测试索引

插入一百万条数据，进行索引

-- 我们先建立一个app用户表
create table `testdemo`.`app_users`(
	`id` bigint(20) unsigned not null auto_increment,
	`name` varchar(50) default'' comment'用户昵称',
	`email` varchar(50) not null comment'用户邮箱',
	`phone` varchar(20) default'' comment'手机号',
	`gender` tinyint(4) unsigned default '0' comment'性别:(0:男/1:女)',
	`pwd` varchar(100) not null comment '密码',
	`age` tinyint(4) default '0' comment '年龄',
	`create_time` datetime default current_timestamp,
	`update_time` timestamp null default current_timestamp on update current_timestamp,
	primary key(`id`)
)engine = innodb default charset=utf8mb4 comment='app用户表';

-- 插入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 app_users ( `name`, `email`, `phone`, `gender`, `pwd`, `age` )
VALUES
	(
	CONCAT( '用户',i ),
	'760458528@qq.com',
	CONCAT( '10', Floor( Rand( ) * ( ( 999999999-100000000 ) + 100000000 ) ) ),
	floor( Rand( ) * 2 ),
	UUID( ),
	floor( Rand( ) * 100 ) 
	);
SET i = i + 1;
END WHILE ;
return i;
END;

select mock_data() -- 测试mock——data()方法
--测试索引
select * from app_users where `name` = '用户9999' -- 0.859s
select * from app_users where `name` = '用户9998' -- 0.534s
select * from app_users where `name` = '用户9997' -- 0.541s
select * from app_users where `name` = '用户9996' -- 0.535s
select * from app_users where `name` = '用户9995' -- 0.547s


--添加索引
--id_表名_字段名
--create 索引属性 index 表名_name on 表名(`name`)
create Fulltext index 表名_name on 表名(`name`)

EXPLAIN select * from app_users where `name` = '用户4534'

8.3.索引原则

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

索引的数据结构

hash类型的索引

Btree：innodb的数据结构~

9.权限管理

9.1.用户管理

SQL语句–（设置权限）

用户表:mysql.user

本质添加

-- 创建用户 create user ljl identified by 'mima'
create user ljl identified by '123456'
-- 修改密码（当前用户密码）
set PASSWORD = PASSWORD('123456')
-- 修改密码（指定用户密码）
set password for ljl =password('123456')
-- 重命名
rename user 用户名 to 用户名1
-- 用户授权 ALL PRIVLEGES 全部权限,库.表
GRANT ALL PRIVLEGES on *.* to 用户名
-- 查询权限
show GRANTS for 用户名 --查看指定用户的权限
show GRANTS for root@localhost

-- root用户权限管理: GRANT ALL PRIVLEGES on *.* to 'root'@'localhost' with grant option
-- 撤销权限
revoke ALL PRIVLEGES on *.* from 用户名
-- 删除用户
drop user 用户名

9.2.mysql备份

为什么要备份：

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

mysql数据库备份的方式

• 直接拷贝物理文件

• 使用可视化工具进行手动导出

• 使用命令行 mysqldump

  • # mysqldump -h主机 -u用户名 -p密码 数据库 表名 > 位置 -- 导出一个
    # mysqldump -h主机 -u用户名 -p密码 数据库 表1,表2,表3 > 位置  --导出多个表
    # mysqldump -h主机 -u用户名 -p密码 数据库 > 位置  --导出数据库
    mysqldump -hlocalhost -root -ljl123 filetest student >D:/a.sql
    
    # 导入
    # 登录的情况下，切换到指定的数据库
    # source 备份文件
    source D:/a.sql
    # 未登录的情况下
    mysqldump -h主机 -u用户名 -p密码 数据库 < 位置  --导入数据库
    

10.银行数据库管理

-- bank_user表
CREATE TABLE `bankdemo`.`bank_user`  (
  `bank_name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户姓名',
  `bank_id` int(50) NOT NULL COMMENT '用户身份证',
  `bank_phone` int(50) NOT NULL COMMENT '用户手机号码',
  `居住地址` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '未知' COMMENT '用户地址',
  PRIMARY KEY (`bank_id`)
);

-- bank_user_number表
CREATE TABLE `bank_user_number`  (
  `bank_Card` int(50) NOT NULL COMMENT '银行卡号10103576',
  `bank_pwd` int(10) NOT NULL DEFAULT 888888 COMMENT '银行卡密码',
  `bank_class` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'RMB' COMMENT '货币类型',
  `bank_lei` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '存款类型',
  `bank_data` datetime(6) NOT NULL ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '存款时间',
  `bank_sum` decimal(65, 0) NOT NULL COMMENT '开户金额',
  `bank_balance` decimal(65, 0) NOT NULL COMMENT '余额',
  `bank_diu` int(10) NOT NULL DEFAULT 0 COMMENT '0/1 无挂失/挂失',
  PRIMARY KEY (`bank_Card`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- bank_jiaoyi表
CREATE TABLE `bank_jiaoyi`  (
  `bank_num_id` int(50) NOT NULL COMMENT '银行卡号',
  `bank_get_data` datetime(6) NOT NULL ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '交易时间',
  `bank_get_money` decimal(65, 0) NOT NULL COMMENT '交易金额',
  `bank_GetSet` int(10) NOT NULL COMMENT '0/1 支取/存入',
  `bank_text` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '备注',
  PRIMARY KEY (`bank_num_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

11.JDBC

11.1.JDBC简介

（作者是这样理解的“SUN公司”为了把程序员当人看，不想人程序员学习这么多数据库，所定出一系列的规定），JDBC是什么？JDBC英文名为：Java DataBase Connectivity(Java数据库连接)，官方解释它是Java编程语言和广泛的数据库之间独立于数据库的连接标准的Java API，根本上说JDBC是一种规范，它提供的接口，一套完整的，允许便捷式访问底层数据库。可以用JAVA来写不同类型的可执行文件：JAVA应用程序、JAVA Applets、Java Servlet、JSP等，不同的可执行文件都能通过JDBC访问数据库，又兼备存储的优势。简单说它就是JAVA与数据库的连接的桥梁或者插件，用JAVA代码就能操作数据库的增删改查、存储过程、事务等。

11.2.JDBC的工作原理

​ JDBC有什么用？我们用JAVA就能连接到数据库；创建SQL或者MYSQL语句；执行SQL或MYSQL的查询数据库；查看和修改结果记录。

我们思考一下？数据库是由不同生产产商决定的，例如Mysql、Oracle、SQL Server,而如果JAVA JDK不可能说提供对不同数据库的实现吧？还有，JAVA具备天生跨平台的优势，它就提供了JDBC的接口API，具体的实现由不同的生产产商决定。这样，数据库生产产商都根据JAVA API去实现各自的应用驱动，这问题就迎刃而解了。

11.3.jdbc常用接口

URL

//调用mysql的驱动//mysql端口编号为3306，这里有些情况会链接不了，建议使用127.0.0.1/数据库名？支持中文编码&设置他的字符薇UTF-8&使用安全的链接
private static String url="jdbc:mysql://localhost:3306/autosys?useUnicode=true&&characterEncoding=UTF-8&userSSL=true";

DriverManager //驱动类—用于获取Connection Connection链接对象接口

//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//链接数据库对象=驱动用户.获取链接(链接地址，数据库用户，数据库用户密码)--connection==代表代码数据库里的电脑管理员
Connection connection = DriverManager.getConnection(url, "root", "123456");
		//事务回滚
        connection.rollback();
        //事务提交
        connection.commit();
        //事务自动提交
        connection.setAutoCommit();

Statement //执行SQL对象 prepareStatement()

String sql="select * from autoTables";
//执行SQL语句------执行SQL的对象
Statement statement = connection.createStatement(sql);
//执行任何SQL语句--返回值会进行一个判断，相对于效率变低
statement.execute();
//查询方法（select）--返回一个结果集ResultSet--封装了结果集
statement.executeQuery();
//更新，删除，插入(Update,delete,insert)--返回一个受影响的行数
statement.executeUpdate();

ResultSet 查询结果集对象–封装所有查询结果

要获得指定的数据类型

//5.执行SQL对象，去执行SQL
String sql="select * from autoTables";
ResultSet resultSet = statement.executeQuery(sql);//返回结果集,
// 在不知道数据类型的时候使用
resultSet.getObject();
//知道使用以下
resultSet.getString();
resultSet.getInt();
resultSet.getDate();
resultSet.getDouble();

遍历数据，指针

resultSet.beforeFirst();//移动到最前面
resultSet.afterLast();//移动到最后面
resultSet.next();//移动到下一个
resultSet.previous();//移动到前一行
resultSet.absolute();//移动到指定行

释放资源–必须要做

//6。释放链接
resultSet.close();
statement.close();
connection.close();

jdbc基础链接

//1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.用户信息，url
        //useUnicode=true&&characterEncoding=UTF-8&userSSL=true
        //支持中文编码&设置他的字符薇UTF-8&使用安全的链接
        String url="jdbc:mysql://localhost:3306/autosys?useUnicode=true&characterEncoding=UTF-8&userSSL=true";
        String username="root";
        String pwd="123456";
        //3.链接数据库对象
        Connection connection = DriverManager.getConnection(url, username, pwd);

        //4.执行SQL语句------执行SQL的对象
        Statement statement = connection.createStatement();

        //5.执行SQL对象，去执行SQL
        String sql="select * from autoTables";
        ResultSet resultSet = statement.executeQuery(sql);//返回结果
        //遍历，指针
        while(resultSet.next()){
            logger.info("id="+resultSet.getObject("id"));
            logger.info("name="+resultSet.getObject("name"));
        }
        //6。释放链接
        resultSet.close();
        statement.close();
        connection.close();

11.4改写方法

#定义链接值
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/autosys?useUnicode=true&&characterEncoding=UTF-8&&userSSL=true"
username=root
pwd=ljl123

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

/**
 * @program: jdbcFirst
 * @author: Nil
 * @create: 2020-06-29 10:12
 **/

public class JdbcUtils {
    private static String driver= null ;
    private static String url= null ;
    private static String username= null ;
    private static String pwd= null ;
    static {
        try {
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            /**
             * 1.生成工具类，进行db.properties配置文件读取，inputStream流接受
             * 2.生成配置文件读取类，进行一个配置文件读取
             * 3.加载驱动
             * */
            Properties properties=new Properties();
            properties.load(in);
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            pwd = properties.getProperty("pwd");

            Class.forName(driver);
        }catch (IOException | ClassNotFoundException e){
            e.printStackTrace();
        }
    }
    public static Connection getConnection() throws SQLException {

        /**
         * 链接方法
         * @param
         * @author Nil
         * @date 2020/6/29
         * @return java.sql.Connection
         * @version JDK1.8
         */
        return DriverManager.getConnection(url,username,pwd);
    }

    public static void release(Connection conn, Statement st, ResultSet rs){

        /**
         * jdbc资源关闭
         * @param conn
         * @param st
         * @param rs
         * @author Nil
         * @date 2020/6/29
         * @return void
         * @version JDK1.8
         */
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

        Connection conn =null;
        Statement st=null;
        ResultSet rt=null;
        try {
            //取得数据库链接值
            conn = JdbcUtils.getConnection();
            st=conn.createStatement();
            String sql="INSERT INTO autotables(name) VALUES('jianli')";
            int a= st.executeUpdate(sql);
            if(a>0){
                System.out.println("插入成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rt);
        }

11.5.SQL注入

什么是SQL注入？

SQL注入是比较常见的网络攻击方式之一，它不是利用操作系统的BUG来实现攻击，而是针对程序员编写时的疏忽，通过SQL语句，实现无账号登录，甚至篡改数据库

SQL注入攻击的总体思路

1：寻找到SQL注入的位置

2：判断服务器类型和后台数据库类型

3：针对不同的服务器和数据库特点进行SQL注入攻击。

SQL注入实例

String sql = "select * from user_table where username=
' "+userName+" ' and password=' "+password+" '";

--当输入了上面的用户名和密码，上面的SQL语句变成：
SELECT * FROM user_table WHERE username=
'’or 1 = 1 -- and password='’

"""
--分析SQL语句：
--条件后面username=”or 1=1 用户名等于 ” 或1=1 那么这个条件一定会成功；

--然后后面加两个-，这意味着注释，它将后面的语句注释，让他们不起作用，这样语句永远都--能正确执行，用户轻易骗过系统，获取合法身份。
--这还是比较温柔的，如果是执行
SELECT * FROM user_table WHERE
username='' ;DROP DATABASE (DB Name) --' and password=''
--其后果可想而知…
"""

如何防止SQL注入呢？

PreparedStatement对象

PrepareedStatement效率更好，防止SQL注入

PreparedStatement pr=null;//放入一条预编译的SQL
String sql="insert into assetinfo(`Name`,`Date`,`Cause`,`Money`) VALUES(?,?,?,?)";//？代表占位符
 pr=conn.prepareStatement(sql);//如知道数据库里的值，我们可以接收一系列的值，如果不知道使用setObject()
            pr.setString(1, sout);
            pr.setString(2, date);
            pr.setString(3, use);
            pr.setInt(4, sout1);

12.数据库连接池

数据库连接—执行完毕—释放（十分消耗资源）

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

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

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

开源数据源实现

• DBCP
• C3P0
• Druid

DBCP

需要使用jar包

commons-dbcp-1.4,commons-pool-1.6

• dbcpconfig.properties配置文件

  #连接设置
  driverClassName=com.mysql.jdbc.Driver
  url=jdbc:mysql://localhost:3306/assetdb?useUnicode=true&&characterEncoding=UTF-8&&userSSL=true
  username=root
  password=ljl123
  
  #<!-- 初始化连接 -->
  initialSize=10
  
  #最大连接数量
  maxActive=50
  
  #<!-- 最大空闲连接 -->
  maxIdle=20
  
  #<!-- 最小空闲连接 -->
  minIdle=5
  
  #<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
  maxWait=60000
  
  
  #JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名=property;]
  #注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
  connectionProperties=useUnicode=true;characterEncoding=gbk
  
  #指定由连接池所创建的连接的自动提交（auto-commit）状态。
  defaultAutoCommit=true
  
  #driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
  #可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
  defaultTransactionIsolation=READ_UNCOMMITTED
  
  

• druid

  #连接设置
  driverClassName=com.mysql.jdbc.Driver
  url=jdbc:mysql://127.0.0.1:3306/assetdb?useUnicode=true&characterEncoding=utf8
  username=root
  password=ljl123
  

  //druidutils
  static DataSource ds;
      static{
          try {
              /**
               * 加载druid
               * */
              Properties properties = new Properties();
              properties.load(druidutils.class.getClassLoader().getResourceAsStream("dbcpconfig.properties"));
              /**
               * 给数据源进行赋值
               * */
              ds= DruidDataSourceFactory.createDataSource(properties);
          }catch (Exception e){
              e.printStackTrace();
          }
      }
      public static Connection getConn() throws SQLException {
  
          /**
           * 链接数据方法，返回链接对象
           * @param  * @param
           * @author Nil
           * @date 2020/7/7
           * @return java.sql.Connection
           * @version JDK1.8
           */
          return ds.getConnection();
      }
      public static void closeAll(ResultSet rs, PreparedStatement pr,Connection con){
  
          /**
           * 关闭资源，从后往前关
           * @param  * @param rs
           * @param pr
           * @param con
           * @author Nil
           * @date 2020/7/7
           * @return void
           * @version JDK1.8
           */
          if(rs!=null){
              try {
                  rs.close();
              } catch (SQLException e) {
                  e.printStackTrace();
              }
          }
          if(pr!=null){
              try {
                  pr.close();
              } catch (SQLException e) {
                  e.printStackTrace();
              }
          }
          if(con!=null){
              try {
                  con.close();
              } catch (SQLException e) {
                  e.printStackTrace();
              }
          }
      }