数据库学习

1，初始MySql

1.1，为什么要学习数据库

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

1.2，什么是数据库

DB（DataBase）

概念：是指长期存储在计算机内的，有组织，可共享的数据的集合。数据库中的数据按一定的数学模型组织、描述和存储，具有较小的冗余，较高的数据独立性和易扩展性，并可为各种用户共享。

作用：存储数据，管理数据

1.3，数据库分类

关系型数据库：（SQL）

• MySql, Oracle, Sql Server, DB2, SQLlite
• 通过表和表之间，行和列之间的关系进行数据的存储

**非关系型数据库: ** (NoSQL)Not Only SQL

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

1.4,MySql

• MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于Oracle旗下产品。

• MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

• MySQL所使用的SQL语言是用于访问数据库的最常用标准化语言。

命令行操作（以管理员身份运行命令提示符）

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

update mysql.user set authentication_string=password('123') where user='root' and Host='localhost';--修改密码

show datebase;
use school;
show tables;
describe student;

exit;--退出连接

• SQL (Structured Query Language）

• DDL (Data Definition Language)

• DML (Data Manipulation Language)

• DQL (Data Query Language)

• DCL: (Data Control Language)

操作数据库>操作数据库中的表>操作数据库中的表中的数据

2，操作数据库

2.1，操作数据库

1. 创建数据库

CREATE DATABASE [IF NOT EXISTS] ym

2. 查看数据库

SHOW DATABASES

3. 使用数据库

USE ym--如果是特殊字符，需要带``,即 use `ym`

4. 删除数据库

DROP DATABASE [IF EXISTS] ym

2.2,数据库列数据类型

数值型

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

字符串

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

时间日期

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

null

• 空值

2.3，数据库的字段属性

Unsigned:

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

zerofill:

• 0填充
• 不足的位数，用0填充 eg:int(3) 5—>005

自增：

• 通常理解为自增，自动在上一条记录的基础上+1（默认）
• 通常用来设计唯一的主键
• 可以设置起始值和步长

非空：null,not null

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

默认default：

• 设置默认的值，如果不指定该列的值，则该列为默认值

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

2.4，创建数据库表

CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名', `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码', `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别', `birthday` DATETIME DEFAULT NULL COMMENT '出生日期', `address` VARCHAR(30) DEFAULT NULL COMMENT '家庭住址', `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
 PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

格式：

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

常用命令：

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

2.5数据表的类型

/*
数据库引擎
InnoDB 默认使用的
MyISAM 早些年使用的
*/

	MyISAM	InnoDB
事务支持	x	√
数据行锁定	x	√
外键约束	x	√
全文索引	√	x
表空间的大小	较小	较大，约为2倍
	节约空间，速度较快	安全性高

在物理空间存在的位置

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

MySql引擎在物理文件上的区别：

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

• MyISAM对应的文件

  *.frm 表结构的定义文件

  *.MYD 数据文件（data）

  *.MYI 索引文件（）index

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

charset=utf8

mysql默认的字符集编码为Latin1，不支持中文

2.6，删除修改表

sqlyog注释快捷键：

**Ctrl+Shift+C **将选中行注释

**Ctrl+Shift+R **去除选中行注释

修改表 alter

-- 修改表名 
-- ALTER TABLE  `oldName` RENAME AS `newName`
   ALTER TABLE  `student` RENAME AS `stu`

-- 增加表的字段
-- ALTER TABLE `表名` ADD  字段名  属性列
   ALTER TABLE `stu` ADD  `age` INT(10)

-- 修改表的字段
ALTER TABLE  `stu` CHANGE `age` `age1` INT(11)-- 字段重命名
ALTER TABLE `stu` MODIFY  `age1`  VARCHAR(10)-- 修改约束

-- 删除表的字段
--ALTER TABLE `表名` DROP `字段名`
  ALTER TABLE `stu` DROP `age1`

删除表 drop

-- 删除表
DROP TABLE IF EXISTS `stu`

注意：

• 所有的创建和删除操作尽量加上判断，以免报错
• 字段名用``包裹
• 注释 – /**/
• sql关键字大小写不敏感，建议小写
• 所有的符号用英文的

3，MySql数据管理

3.1,外键

给表添加外键约束

ALTER TABLE `student` 
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
--alter table 表名 add constraint 约束名 foreign key (作为外键的列) references 哪个表(哪一列)

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

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

• 数据库就是单纯的表，只有行和列

• 要想使用多张表的数据，使用外键的话，用程序去实现

3.2，DML语言

数据操纵语言

• insert

• update

• delete

3.3，添加 insert

insert into 表名 ([字段名1],[字段名2]) values(‘值1’),(‘值2’)

INSERT INTO `grade` (`gradename`)
VALUES('大一'),
('大二'),
('大三'),
('大四')

INSERT INTO `student` 
(`name`,`pwd`,`sex`,`birthday`,`address`,`email`,`gradeid`)
VALUES
('ym','111','女','2021.04.05','地球','111@111','1'),
('ymm','111','女','2021.04.05','地球','111@111','2'),
('ymmm','111','女','2021.04.05','地球','111@111','3')

3.4，修改 update

update 表名 set 列名=值，列名=值 where 条件

UPDATE `student` SET `pwd` = '999' , sex = '男' WHERE `id` = 3 AND `name`='ymmm'

3.5，删除 delete truncate

delete from 表名 where 条件

DELETE FROM `student` WHERE `id`=3

TRUNCATE 表名

	delete	truncate
相同点	都能删除数据，不会删除表结构
不同点	删除一条或多条或所有记录，	删除所有记录，重新设置自增列，计数器归0

delete删除，重启数据库后，

• InnoDB 自增量会从1开始 （存在内存中，断电即失）
• MyISAM 继续从上一个自增量开始 （存在文件中，不会丢失）

4，DQL查询数据

4.1，DQL

Data Query Language:数据查询语言

select 完整语法：

4.2,指定字段查询

select 字段1 ，…… from 表名

SELECT * FROM `student`

AS 起别名

concat()拼接

SELECT `studentno` AS 学号,`studentname` AS 姓名 FROM `student` 

SELECT CONCAT('0000',`studentno`)AS 学号,`studentname` AS 姓名 FROM `student` 

distinct 去重，相同的结果只保留一个

SELECT * FROM `result`
SELECT `studentno` FROM `result`
SELECT DISTINCT `studentno` FROM `result`

select 表达式 from 表名

SELECT VERSION()-- 查看系统版本
SELECT 100+100-5 AS 计算结果 -- 用来计算
SELECT `studentresult`+1 AS 学生分数 FROM `result` -- 所有学生的分数都加1

数据库中的表达式：函数，计算表达式，系统变量，文本值，……

4.3,where条件子句

逻辑运算符

• 逻辑与 and &&
• 逻辑或 or ||
• 逻辑非 not !

模糊查询 比较运算符(结果为true ,false)

• is null
• is not null
• between……and……
• like
• in

like 结合 通配符

%	替代0个或多个字符
_	仅替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist] [!charlist]	不在字符列中的任何单一字符

SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '张%'

4.4,联表查询

思路：

1. 分析需求，分析查询的字段来自哪些表
2. 确定使用哪种连接查询
3. 确定交叉点
4. 条件

SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` AS su
ON su.`subjectno` = r.`subjectno`

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

自连接

自己的表和自己的表连接，核心是：一张表拆成两张表

父类：

categogyid	categoryname
2	信息技术
3	软件开发
5	美术设计

子类：

pid	categoryid	categoryname
3	4	数据库
3	6	web开发
5	7	ps技术
2	8	办公信息

父类	子类
信息技术	办公信息
软件开发	数据库，web开发
美术设计	ps技术

SELECT a.`categoryname` AS 父栏目,b.`categoryname` AS 子栏目
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid`

4.5,分组和过滤

查询 课程平均分 大于80 的 不同课程 的 平均分，最高分，最低分

SELECT `subjectname`,AVG(`studentresult`),MAX(`studentresult`),MIN(`studentresult`)
FROM `subject` AS sub
INNER JOIN `result` AS r
ON sub.`subjectno`=r.`subjectno`
ORDER BY sub.`subjectno`
HAVING AVG(`studentresult`) >70

4.6,排序和分页

排序 order by

把查询结果根据某字段按==升序(asc)或降序(desc)==排序

……

where……

order by ……

分页 limit

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

第一页 limit 0 , 5

第二页 limit 5 , 5

第三页 limit 10 , 5

……

第 n 页 limit (n-1)*5 , 5

第 n 页 limit (n-1)*pagesize, pagesize

n:当前页，第n页

pagesize:页面大小，一页存几条数据

(n-1)*pagesize：页面的起始值，该页从第几条数据开始

总页数=（数据条数/页面大小）向上取整

4.7，子查询和嵌套查询

SELECT s.`studentno`,`studentname`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno`=r.`studentno`
WHERE `subjectno`=(SELECT `subjectno` FROM `subject` WHERE `subjectname`='C语言-1')
ORDER BY `studentresult` DESC
LIMIT 0,5

5,MySql函数

官网文献资料：官网文献资料

5.1，常用函数

数学运算

SELECT ABS(-6.5)-- 取绝对值
SELECT RAND()-- 取随机数
SELECT CEILING(6.5)-- 向上取整
SELECT FLOOR(6.5)-- 向下取整
SELECT SIGN(-8)-- 判断一个数的符号，正数返回1，负数返回-1，0返回0

字符串函数

SELECT CHAR_LENGTH('坚持就是胜利')-- 查询字符串长度，6
SELECT CONCAT('坚持','就是','胜利，加油')-- 字符串拼接，坚持就是胜利，加油
SELECT INSERT('坚持就会有结果',1,3,'努力')-- 从第一个字符(坚)开始，替换掉三个(坚持就)，努力会有结果
SELECT UPPER('JavaPython123哈哈')-- 把含有的字母都转为大写
SELECT LOWER('JavaPython123哈哈')-- 把含有的字母都转为小写
SELECT INSTR('JavaPython','va')-- 判断子串在母串中是否存在，不存在返回0，存在返回字串的第一个字符在母串中的索引，3
SELECT REPLACE('javasqlpython','sql','and')-- javaandpython,替换
SELECT SUBSTR('坚持就是胜利',2,3)-- 从第二位(持)开始截取，截取三位(持就是)
SELECT REVERSE('琦卓刘')-- 反转字符串

时间和日期函数

SELECT CURRENT_TIME()-- 当前时间
SELECT NOW()-- 当前日期和时间
SELECT LOCALTIME()-- 当前日期和时间
SELECT SYSDATE()-- 系统日期和时间

SELECT YEAR(NOW())-- 年
SELECT MONTH('2021-02-04')-- 月
SELECT DAY(NOW())-- 日
SELECT HOUR(NOW())-- 时
SELECT MINUTE(NOW())-- 分
SELECT SECOND(NOW())-- 秒

系统

SELECT SYSTEM_USER()-- root@localhost
SELECT USER()-- root@localhost
SELECT VERSION()-- 5.7.20-log

5.2，聚合函数

SELECT COUNT(`email`) FROM `student`-- count(字段)，会忽略所有的null值
SELECT COUNT(*) FROM `student`-- count(*) 不会忽略所有的null值，本质是计算行数
SELECT COUNT(1) FROM `student`-- count(1) 不会忽略所有的null值，本质是计算行数


SELECT SUM(`studentresult`) AS 成绩总和 FROM `result`
SELECT AVG(`studentresult`) AS 平均成绩 FROM `result`
SELECT MAX(`studentresult`) AS 最高分 FROM `result`
SELECT MIN(`studentresult`) AS 最低分 FROM `result`

5.3，数据库级别的MD5加密

MD5信息摘要算法（MD5 Message-Digest Algorithm），一种被广泛使用的密码散列函数，可以产生出一个128位（16字节）的散列值（hash value）

MD5不可逆

相同值的md5值也一样

-- 插入数据的时候给密码加密
INSERT INTO `testmd5` VALUES (1,'h',MD5('333444'))

-- 给id=3的密码加密
UPDATE `testmd5` SET pwd = MD5(pwd) WHERE `id`=3

6，事务

事务 transaction

一般是指要做的或所做的事情

指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。

事务的特性ACID：

• 原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的操作要么都做，要么都不做。

• 一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一个事务在执行之前和执行之后，数据库都必须处于一致性状态。

• 隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

• 持久性（durability）。持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

隔离所导致的问题

• 脏读：指一个事务读到了另一个事务未提交的(提交前的)数据
• 不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同
• 虚读(幻读)：指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致

模拟场景

-- 模拟转账
SET autocommit=0
START TRANSACTION

UPDATE `account` SET money=money-50 WHERE `name`='A'
UPDATE `account` SET money=money+50 WHERE `name`='B'

COMMIT;
ROLLBACK;

SET autocommit=1

7，索引

索引(index)是帮助mysql高效获取数据的数据结构

7.1，索引的分类

• 主键索引 Primary Key
• 唯一索引 Unique Key
• 常规索引 Key/index
• 全文索引 FullText

基础语法

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

SHOW INDEX FROM `student`-- 显示所有的索引信息

-- 增加一个全文索引
ALTER TABLE school.student ADD FULLTEXT INDEX `srudentname`(`studentname`)


-- explain 分析sql执行的状况
EXPLAIN SELECT * FROM student

7.2，测试索引

插入1000000条数据，分析建立索引前后查找的快慢

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_user`(`name`,`email`,`phone`,`gender`)VALUES(CONCAT('用户',i),'11111qq.com','123456789',FLOOR(RAND()*2));
		SET i=i+1;
	END WHILE;
	RETURN i;
END;

SELECT mock_data() -- 执行此函数 生成一百万条数据
-- 建立索引前
SELECT * FROM `app_user` WHERE `name`='用户9999'-- 总耗时  : 1.415 sec

CREATE INDEX id_app_user ON `app_user`(`name`)
-- 建立索引后
SELECT * FROM `app_user` WHERE `name`='用户9999'-- 总耗时  : 0.003 sec

7.3，索引原则

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

8，权限管理和备份

用户权限

-- 创建用户
-- CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER ym IDENTIFIED BY '123456'

-- 修改指定用户的密码
SET PASSWORD FOR root@localhost = PASSWORD('root')
SET PASSWORD FOR ym =PASSWORD('ym')

-- 用户名重命名
RENAME USER ym TO ymm

-- 用户授权，all privileges on 库.表 全部的权限，除了grant
GRANT ALL PRIVILEGES  ON *.* TO ymm


-- 查询权限
SHOW GRANTS FOR ymm
SHOW GRANTS FOR root@localhost

-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM ymm

-- 删除用户
DROP USER ymm

备份

1. 备份原因：保证数据不丢失；数据转移

2. 备份方式：

• 直接copy物理文件
• 在sqlYog这种可视化工具中手动导出
• 使用命令行导出 musqldump

-- 导出
-- mysqldump -h主机名 -u用户名 -p密码 数据库 [表名1 表名2] > 存放路径

-- 导入
source 路径

9，规范数据库设计

数据库三大范式

参考：参考

• 第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项。

• 第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖），第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。

• 第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖），第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

10，JDBC

10.1，数据库驱动

10.2，JDBC

Java数据库连接(Java Database Connectivity，简称JDBC）是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。JDBC也是Sun Microsystems的商标。我们通常说的JDBC是面向关系型数据库的。

java.sql

javax.sql

还需要导入一个jar包，mysql-connector-java-5.1.38.jar

10.3,第一个JDBC程序

创建测试数据库

CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;

USE jdbcStudy;

CREATE TABLE `users`(
	id INT PRIMARY KEY,
	NAME VARCHAR(40),
	PASSWORD VARCHAR(40),
	email VARCHAR(60),
	birthday DATE
);

INSERT INTO `users`(id,NAME,PASSWORD,email,birthday)
VALUES(1,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')

1. IDEA中创建一个项目

2. 导入数据库驱动

   ​ 新建lib

   ​ 把jar包copy到lib中

   ​ jar包add as library

3. 编写测试代码

package com.ym.lesson01;

import java.sql.*;

public class FirstJdbcDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1,加载驱动
        Class.forName("com.mysql.jdbc.Driver");

        //2,填写信息 url,username,password
        String url = "jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "root";

        //3,连接数据库，返回数据库对象 connection
        Connection connection = DriverManager.getConnection(url, username, password);

        //4,通过connection创建执行sql的对象 statement
        Statement statement = connection.createStatement();

        //5,statement执行sql语句,返回结果集 resultSet
        String sql= "SELECT * FROM `users`";

        ResultSet resultSet = statement.executeQuery(sql);

        //6，循环得到每条数据
        while(resultSet.next()){
            System.out.println("id:"+resultSet.getObject("id"));
            System.out.println("NAME:"+resultSet.getObject("NAME"));
            System.out.println("PASSWORD:"+resultSet.getObject("PASSWORD"));
            System.out.println("email:"+resultSet.getObject("email"));
            System.out.println("birthday:"+resultSet.getObject("birthday"));
            System.out.println("============");
        }

        //7，释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

步骤总结：

• 加载驱动
• 连接数据库，返回数据库对象 connection
• 通过connection创建执行sql的对象 statement
• statement执行sql语句,返回结果集 resultSet
• 释放连接

10.4, statement对象

• jdbc中的statement对象用于向数据库发送sql语句

• 想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查的sql语句即可

• statement对象的executeUpdate方法，用于向数据库发送 增，删，改 的sql语句。executeUpdate执行完后，将会返回一个整数（即增删改语句导致了数据库几行数据发生了变化。受影响的行数）

• statement对象的executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的resultSet对象

db.propertied

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

JdbcUtils

package com.ym.lesson02.utils;

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

public class JdbcUtils {

    //提升作用域
    private static String driver=null;
    private static String url=null;
    private static String username=null;
    private static String password=null;

    static{
        try {
            //读取配置文件db.properties，返回输入流
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");

            //创建流对象
            Properties properties = new Properties();

            //加载输入流
            properties.load(in);


            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            //1,加载驱动，驱动只用加载一次
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //2,获取连接
    public static Connection getConnection() throws SQLException {
       return DriverManager.getConnection(url,username,password);
    }
    //3，释放资源
    public static void release(Connection conn, Statement st, ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

insert

package com.ym.lesson02.utils;

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

public class InsertTest {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
             //获取链接
            conn = JdbcUtils.getConnection();

             //创建执行sql的st对象
            st = conn.createStatement();

            String sql="INSERT INTO `users`(id,NAME,PASSWORD,email,birthday) VALUES(6,'ym','123456','ym@qq.com','2021-04-07')";

            //执行sql语句
            int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("插入成功");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //释放连接
            JdbcUtils.release(conn,st,null);
        }
    }
}

delete

package com.ym.lesson02.utils;

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

public class DeleteTest {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            String sql="DELETE  FROM `users` WHERE `id`=4";
            int i = statement.executeUpdate(sql);
            if(i>0){
                System.out.println("删除成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,null);
        }
    }
}

update

package com.ym.lesson02.utils;

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

public class UpdateTest {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            String sql = "UPDATE `users` SET `NAME`='lzq' WHERE `id`=6";
            int i = statement.executeUpdate(sql);
            if(i>0){
                System.out.println("更新成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,null);
        }

    }
}

Query

package com.ym.lesson02.utils;

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

public class SelectTest {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            String sql="SELECT `id`,`NAME`,`PASSWORD` FROM `users` WHERE id IN(1,2,3)";
            resultSet = statement.executeQuery(sql);
            while(resultSet.next()){
                System.out.println(
                        "id:"+resultSet.getInt("id")+
                                ",NAME："+resultSet.getString("NAME")+
                                ",PASSWORD:"+resultSet.getString("PASSWORD"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

sql注入

package com.ym.lesson02.utils;

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

public class Sql注入 {
    public static void main(String[] args) {
//        login("lisi","123456");
        login(" 'or'1=1"," 'or'1=1");
    }
    public static  void login(String username,String password){
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            //          SELECT * FROM `users` WHERE `NAME`='lisi' AND `PASSWORD`='123456'
            String sql="SELECT * FROM `users` WHERE `NAME`='"+username+"' and `PASSWORD`='"+password+"'";
            resultSet = statement.executeQuery(sql);
            while(resultSet.next()){
                System.out.println(
                        "id:"+resultSet.getInt("id")+
                                ",NAME："+resultSet.getString("NAME")+
                                ",PASSWORD:"+resultSet.getString("PASSWORD"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }

}

10.5,PreparedStatement对象

insert

package com.ym.lesson03;

import com.ym.lesson02.utils.JdbcUtils;

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

public class InsertTest {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement st = null;
        try {
            connection = JdbcUtils.getConnection();

            //使用?占位符代表参数值
            String sql="INSERT INTO `users`(id,NAME,PASSWORD,email,birthday) VALUES(?,?,?,?,?)";

            //预编译sql
            st = connection.prepareStatement(sql);

            //手动给参数赋值
            st.setInt(1,4);
            st.setString(2,"xxx");
            st.setString(3,"123456");
            st.setString(4,"111@111");
            st.setDate(5,null);

            //执行，返回受影响的行数
            int i = st.executeUpdate();

            if(i>0){
                System.out.println("插入成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,st,null);
        }
    }
}

delete

package com.ym.lesson03;

import com.ym.lesson02.utils.JdbcUtils;

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

public class DeleteTtest {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement st = null;
        try {
            connection = JdbcUtils.getConnection();
            String sql="delete from users where id=?";
            st = connection.prepareStatement(sql);
            st.setInt(1,4);
            int i = st.executeUpdate();
            if(i>0){
                System.out.println("删除成功");
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,st,null);
        }
    }
}

update

package com.ym.lesson03;

import com.ym.lesson02.utils.JdbcUtils;

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

public class UpdateTest {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement st = null;
        try {
            connection = JdbcUtils.getConnection();
            String sql="update users set `NAME`=? where `id`=?";
            st = connection.prepareStatement(sql);
            st.setString(1,"ym");
            st.setInt(2,2);
            int i = st.executeUpdate();
            if(i>0){
                System.out.println("更新成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,st,null);
        }
    }
}

query

package com.ym.lesson03;

import com.ym.lesson02.utils.JdbcUtils;

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

public class SelectTest {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            connection = JdbcUtils.getConnection();
            String sql = "select * from users where `id`in (?,?)";
            st = connection.prepareStatement(sql);
            st.setInt(1,1);
            st.setInt(2,2);
            rs = st.executeQuery();
            while(rs.next()){
                System.out.println(
                        "id:"+rs.getInt("id")+
                                ",NAME："+rs.getString("NAME")+
                                ",PASSWORD:"+rs.getString("PASSWORD"));

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,st,rs);
        }
    }
}

sql注入

package com.ym.lesson03;

import com.ym.lesson02.utils.JdbcUtils;

import java.sql.*;

public class Sql注入 {
    public static void main(String[] args) {
//        login("ym","123456");
        login(" ' ' or 1=1"," ' 'or 1=1");
    }
    public static  void login(String username,String password){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();

            String sql="SELECT * FROM `users` WHERE `NAME`=? and `PASSWORD`=?";

            statement = connection.prepareStatement(sql);

            statement.setString(1,username);
            statement.setString(2,password);

            resultSet = statement.executeQuery();
            while(resultSet.next()){
                System.out.println(
                        "id:"+resultSet.getInt("id")+
                                ",NAME："+resultSet.getString("NAME")+
                                ",PASSWORD:"+resultSet.getString("PASSWORD"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }

}

10.6,使用IDEA连接数据库

10.7,操作事务

package com.ym.lesson04;

import com.ym.lesson02.utils.JdbcUtils;

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

public class TransactionTest {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement pst = null;
        try {
            connection = JdbcUtils.getConnection();

            connection.setAutoCommit(false);//关闭数据库的自动提交，默认自动开启事务

            String sql1 = "update account set money=money-100 where name ='A' ";
            pst = connection.prepareStatement(sql1);
            pst.executeUpdate();

            /*int x=1/0;//会报错*/

            String sql2 = "update account set money=money+100 where name ='B' ";
            pst = connection.prepareStatement(sql2);
            pst.executeUpdate();

            connection.commit();//业务完毕，提交事务
            System.out.println("执行成功！");
        } catch (SQLException e) {
            /*try {
                connection.rollback();//失败则回滚，默认会回滚，不写也可
            } catch (SQLException ex) {
                ex.printStackTrace();
            }*/
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,pst,null);
        }
    }
}

10.8,数据库连接池

数据库连接–执行完毕–释放连接 浪费系统资源

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

常用连接数

最小连接数

最大连接数

等待超时

编写连接池，实现接口DataSource

开源数据源

DBCP

C3P0

Druid

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

DBCP

C3P0