MySQL与JDBC精简笔记

Rochester

发布于 2020-09-01 10:30:12

3.9K0

发布于 2020-09-01 10:30:12

文章被收录于专栏：牛人NR牛人NR

一、数据库

1.数据库的基本概念

1.数据库的英文单词：

DataBase 简称：DB

2. 什么是数据库？

用于存储和管理数据的仓库。

3 .数据库的特点：

1.持久化存储数据的。其实数据库就是一个文件系统； 2.方便存储和管理数据； 3.使用了统一的方式操作数据库 — SQL

4. MySQL的安装和卸载

具体自己百度查询

5. MySQL的登录和退出

1. MySQL的登录

方式1：mysql -uroot -p密码
方式2：mysql -h连接目标的IP地址 -uroot -p连接目标的密码
方式3：mysql –host=ip –user=root –password=连接目标的密码

2. MySQL的退出

方式1：exit
方式2：quit

2. SQL

1.什么是SQL？

Structured Query Language：结构化查询语言

其实就是定义了操作所有关系数据库的规则。每一种数据库操作的方式存在不一样的地方，称为“方言”。

2. SQL通用语法

SQL语句可以单行或多行书写，以分号结尾；
可使用空格和缩进来增强语句的可读性；
MySQL数据库的SQL语句不区分大小写，关键字建议用大写书写；
有3种注释方式：
1. 单行注释：– 注释内容；（注意–后必须有空格）
2. 单行注释：# 注释内容；（MySQL特有方法）
3. 多行注释：/* 注释 */

3. SQL分类

DDL（Data Definition Language）数据定义语言用来定义数据库对象：数据库，表，列等。关键字：create,drop,alter等。
DML（Data Manipulation Language）数据操作语言：用来对数据库中表的数据进行增删改。关键字：insert,delete,update等。
DQL：（Data Query Language）数据查询语言：用来查询数据库中表的记录（数据）。关键字：select,where等。
DCL：（Data Control Language）数据控制语言（了解）用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT,REVOKE等。

3. DDL：操作数据库、表

1. 操作数据库：CRUD

1. C(Create)：创建

创建数据库：
- create database 数据库名称;

mysql> create database db1;
Query OK, 1 row affected (0.15 sec)

创建数据库，判断不存在，再创建：
- create database if not exists 数据库名称；

mysql> create database if not exists db2;
Query OK, 1 row affected (0.18 sec)

创建数据库，并制定字符集：
- create database 数据库名称 character set 字符集名；

mysql> create database db3 character set gbk;
Query OK, 1 row affected (0.16 sec)

2. R(Retrieve)：查询

查询所有数据库的名称：
- show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| db3                |
| db4                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
8 rows in set (0.37 sec)

查询某个数据库的字符集：查询某个数据库的创建语句
- show create database 数据库名称;

mysql> show create database db3;
+----------+------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                |
+----------+------------------------------------------------------------------------------------------------+
| db3      | CREATE DATABASE `db3` /*!40100 DEFAULT CHARACTER SET gbk */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3. U(Update)：修改

修改数据库的字符集
- alter database 数据库名称 character set 字符集名称；

mysql> alter database db4 character set utf8;
Query OK, 1 row affected, 1 warning (0.49 sec)

4. D(Delete)：删除

删除数据库
- drop database 数据库名称；

mysql> drop database db4;
Query OK, 0 rows affected (0.21 sec)

判断数据库存在，存在再删除
- drop database if exists 数据库名称；

mysql> drop database if exists db3;
Query OK, 0 rows affected (0.17 sec)

5. 使用数据库

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

mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

使用数据库：
- use 数据库名称;

mysql> use db2;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| db2        |
+------------+
1 row in set (0.00 sec)

2.操作表：

1. C(Create)：创建

1. 语法

create table 表名(
    列名1 数据类型1,
    列名2 数据类型2,
    ...
    列名n 数据类型n
);
* 注意：最后一列，不需要加逗号（，）

2. 数据库类型

int：整数类型
- age int,
double : 小数类型
- score double(5,2)
*注意：5表示总共取5位，2表示小数点后2位
date：日期，只包含年月日，yyyy-MM-dd
datetime：日期，包含年月日时分秒 yyyy-MM-dd HH:mm:ss
timestamp：时间错类型（自动赋值使用当前系统时间）：包含年月日时分秒：yyyy-MM-dd HH:mm:ss
- 如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
varchar：字符串
- name varchar(20)：表示姓名最大占用20个字符
  - zhangsan 8个字符张三 2个字符

练习：

在db1数据库中创建一个名为student数据表，
里面包含的列有id，name，age，score，birthday，创建日期（默认）展示表结构：

mysql> desc student;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | int         | YES  |     | NULL    |       |
| name        | varchar(20) | YES  |     | NULL    |       |
| age         | int         | YES  |     | NULL    |       |
| score       | double(3,1) | YES  |     | NULL    |       |
| birthday    | date        | YES  |     | NULL    |       |
| insert_time | timestamp   | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> use db1;
Database changed
mysql> create table student(
    ->          id int,
    ->          name varchar(20),
    ->          age int,
    ->          score double(3,1),
    ->          birthday date,
    ->          insert_time timestamp
    -> );
Query OK, 0 rows affected, 1 warning (0.28 sec)

3.复制表

create table 表名 like 被复制的表名;

2. R(Retrieve)：查询

查询某个数据库中所有的表名称
- show tables;

mysql> use mysql;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| component                 |
| db                        |
| user                      |
+---------------------------+
4 rows in set (0.10 sec)

查询表结构
- desc 表明；

mysql> desc db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(255)     | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(32)      | NO   | PRI |         |       |
+-----------------------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

3. U(Update)：修改

1. 修改表名

alter table 表名 rename to 新的表名;

2. 修改表的字符集

alter table 表名 character set 新字符集名称;

3. 添加一列

alter table 表名 add 列名 数据类型;

4.修改列名称、类型

1.alter table 表名 change 列名 新列名 新数据类型;
2.alter table 表名 modify 列名 新数据类型;    -- 只修改列的数据类型

5. 删除列

alter table 表名 drop 列名;

4. D(Delete)：删除

drop table 表名;
drop table if exists 表名;

mysql> drop table if exists stu;
Query OK, 0 rows affected (0.68 sec)

4. DML: 增删改表中数据

1. 添加数据：

* 语法：
insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);

注意：
1. 列名和值要一一对应；
2. 如果表名后，不定义列名，则默认给所有列添加值；
3. 除了数字类型，其它类型需要使用引号（单双都可以）引起来。

insert into 表名 values(值1,值2,...值n);

示例：输出结果：

INSERT INTO stu(id,NAME,age) VALUES(1001,'露娜',18); -- 给指定列添加数据

INSERT INTO stu VALUES(1002,'貂蝉',19,99.9,'2000-01-01',NULL); -- 默认全部添加数据

SELECT * FROM stu; -- 查询表中的数据

2. 删除数据：

* 语法：
delete from 表名 where 条件; -- 删除具体某一行数据

注意：
1. 如果不加条件，则删除表中所有记录；
2. 如果要删除所有记录：

1.delete from 表名; --不推荐使用。有多少条记录就会执行多少次删除操作，效率低；
2.truncate table 表名； --推荐使用，效率更高；先删除表，然后再创建一张一模一样的表。

示例：

DELETE FROM stu WHERE id=1001;

DELETE FROM stu;

TRUNCATE TABLE stu;

3. 修改数据：

* 语法：
update 表名 set 列名 = 值1,列名2 = 值2，... [where 条件];

注意：如果不加任何条件则会将表中所有记录全部修改。
示例：

UPDATE stu SET age = 20 WHERE id = 1002; -- 修改某一列某一数据

UPDATE stu SET age = 21; -- 修改表中全部某一数据的值为统一的值

5. DQL: 查询表中的记录

1. 语法：

select
    字段列表
from
    表名列表
where
    条件列表
group by
    分组字段
having
    分组之后的条件
order by
    排序
limit
    分页限定

2. 基础查询：

1.多个字段的查询

select 字段名1,字段名2,... from 表名;

2. 去重重复：

distinct

3. 计算列：

一般可以使用四则运算计算一些列的值。（一般只会进行数值型的计算）；
ifnull(表达式1，表达式2)
1. null参加的运算，计算结果都为null；
2. 表达式1：代表哪个字段需要判断是否为null；
3. 表达式2：如果该字段为null后的替换纸。

4. 起别名：

as ： as也可以省略

示例：

-- 创建表
CREATE TABLE student (
    id INT, -- 编号
    NAME VARCHAR(20), -- 姓名
    age INT, -- 年龄
    sex VARCHAR(5), -- 性别
    address VARCHAR(100), -- 地址
    math INT, -- 数学
    english INT -- 英语
);

-- 添加数据
INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES(1,'马云',55,'男','杭州',66,78),
(2,'马化腾',45,'女','深圳',98,87),(3,'马克涛',55,'男','香港',56,77),(4,'柳岩',20,'女','湖南',76,65),
(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港',99,99),
(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);

-- 查询编号和姓名
SELECT id,NAME FROM student;

-- 查询地址
SELECT address FROM student;

-- 去重，地址里面有重复的
SELECT DISTINCT address FROM student;

-- 计算每个人成绩总分
SELECT NAME,math,english,math + english FROM student;

-- 因为null参加的运算，结果都为null
-- 所以利用ifnull计算
SELECT DISTINCT NAME,math,english,math + IFNULL(english,0) FROM student;

-- 起别名
SELECT DISTINCT NAME AS 姓名,math AS 数学,english AS 英语,math + IFNULL(english,0) AS 总分 FROM student;

-- 起别名 中 AS 也可以省略
SELECT DISTINCT NAME 姓名,math 数学,english 英语,math + IFNULL(english,0) 总分 FROM student;

3. 条件查询

where子句后跟条件；
运算符
- <, >, <=, >=, =, <>
- between…and
- in(集合)
- like
- is null
- and 或 &&
- or 或 ||
- not 或！

示例：

-- 查询年龄大于20的人
SELECT * FROM student WHERE age > 20;

-- 查询年龄等于20的人
SELECT * FROM student WHERE age = 20;

-- 查询年龄大于等于20的人
SELECT * FROM student WHERE age >= 20;

-- 查询年龄不等于20的人
SELECT * FROM student WHERE age != 20;    -- 方式1
SELECT * FROM student WHERE age <> 20;    -- 方式2

-- 查询年龄大于等于20，小于等于30的人
SELECT * FROM student WHERE age >= 20 AND age <= 30;  -- 方式1
SELECT * FROM student WHERE age >= 20 && age <= 30;  -- 方式2
SELECT * FROM student WHERE age BETWEEN 20 AND 30;  -- 方式3

-- 查询年龄为22岁，18岁，25岁的人的信息
SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25;  -- 方式1
SELECT * FROM student WHERE age IN (22,18,25);

-- 查询英语成绩为null的人
-- select * from student where english = null;    -- 错误写法,NULL值不能使用 = 或者 != 进行判断
SELECT * FROM student WHERE english IS NULL;

-- 查询英语 成绩不为NULL的人
SELECT * FROM student WHERE english IS NOT NULL;

模糊查询（ like ）：
- 占位符：
  - _ ：单个任意字符；
  - % ：多个任意字符

示例：

-- 查询姓马的人
SELECT * FROM student WHERE NAME LIKE '马%';

-- 查询名字第二个字为化的人
SELECT * FROM student WHERE NAME LIKE '_化%';

-- 查询名字是3个 字的人
SELECT * FROM student WHERE NAME LIKE '___';  -- "三个_"

-- 查询名字中包含德的人
SELECT * FROM student WHERE NAME LIKE '%德%';

4. 排序查询

语法：
- order by 子句
- order by 排序字段1 排序方式1，排序字段2 排序方式2…
排序方式：
- ASC ：升序，默认的；
- DESC : 降序。
注意：
- 如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。
示例：

-- 查询数学成绩，按升序排序
SELECT * FROM student ORDER BY math ASC;

-- 查询数学成绩，按降序排序
SELECT * FROM student ORDER BY math DESC;

-- 查询数学成绩，按照降序排序，如果数学成绩一样，按照英语成绩降序排序
SELECT * FROM student ORDER BY math DESC,english DESC;

5. 聚合函数

count : 计算个数
- 一般选择非空的列：主键；
- count( * )
max ：计算最大值
min ：计算最小值
sum ：计算和
avg ：计算平均值

注意：

聚合函数的计算，排除null值；
- 解决方案：
  1. 选择不包含非空的列进行计算；
  2. ifnull函数。

示例：

-- 计算数学个数
SELECT COUNT(math) FROM student;

-- 计算英语个数
SELECT COUNT(IFNULL(english,0)) FROM student; -- 如果为null,可使用ifnull函数

-- 计算个数
SELECT COUNT(*) FROM student; -- 哪列没有NULL，就自动选取哪列计算个数

-- 计算数学最高分
SELECT MAX(math) FROM student;

-- 计算数学最小分
SELECT MIN(math) FROM student;

-- 计算数学总和
SELECT SUM(math) FROM student;

-- 计算数学平均分
SELECT AVG(math) FROM student;

6. 分组查询

语法：group by 分组字段；
注意：
- 分组之后查询的字段：分组字段、聚合函数
- where 和 having 的区别？
  1. where 在分组之前进行限定，如果不满足条件。则不参与分组；having在分组之后限定，如果不满足结果，则不会被查询出来。
  2. where 后不可以跟聚合函数，having 可以进行聚合函数的判断。

示例：


-- 按照性别分组，分别查询男、女同学的数学平均分
SELECT sex,AVG(math) GROUP BY sex;

-- 按照性别分组，分别查询男、女同学的数学平均分、人数
SELECT sex,AVG(math),COUNT(id) FROM student GROUP BY sex ;

-- 按照性别分组，分别查询男、女同学的数学平均分、人数,要求：分数低于70分的，不参与分组
SELECT sex,AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex;

-- 按照性别分组，分别查询男、女同学的数学平均分、人数,要求：分数低于70分的，不参与分组,
-- 分组之后，人数要大于2个人
SELECT sex,AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2; -- 方式1
SELECT sex,AVG(math),COUNT(id) 人数 FROM student WHERE math > 70 GROUP BY sex HAVING 人数 > 2; -- 方式2

7. 分页查询

语法：
- limit 开始的索引,每页查询的条数;
公式：
- $$ 开始的索引 = （当前的页码 - 1） * 每页显示的条数 $$
注意： limit 是一个MySQL“方言”。

示例：

-- 每页显示3条记录
SELECT * FROM student LIMIT 0,3; -- 第1页
SELECT * FROM student LIMIT 3,3; -- 第2页
SELECT * FROM student LIMIT 6,3; -- 第3页

8. 约束

1. 概念和分类

概念：对表中的数据进行限定，保证数据的正确性、有效性和完整性。
分类：
1. 主键约束：primary key
2. 非空约束：not null
3. 唯一约束：unique
4. 外键约束：foreign key

2. 非空约束：not null

-- 创建表时添加非空约束
CREATE TABLE stu (
    id INT,
    NAME VARCHAR(20) NOT NULL  -- name为非空
);

-- 创建完表后，再添加非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

-- 删除约束
ALTER TABLE stu MODIFY NAME VARCHAR(20);  -- 相当于修改

3. 唯一约束：UNIQUE

-- 创建表时，添加唯一约束
CREATE TABLE stu (
    id INT,
    phone_num VARCHAR(20) UNIQUE
);
* 注意：在mySQL中，唯一约束限定的列的值可以有多个NULL

-- 删除唯一约束
ALTER TABLE stu DROP INDEX phone_num;

-- 在创建表后，添加唯一约束
ALTER TABLE stu MODIFY phone_num VARCHAR(20) UNIQUE;

4. 主键约束：primary key

注意：
1. 含义：非空且唯一；
2. 一张表只能有一个字段为主键；
3. 主键就是表中记录的唯一标识。
在创建表时，添加主键约束；

CREATE TABLE stu(
    id INT PRIMARY KEY,
    NAME VARCHAR(20)
);

删除主键；

ALTER TABLE stu DROP PRIMARY KEY;

创建完表后，添加主键。

ALTER TABLE stu MODIFY id INT PRIMARY KEY;

自动增长
1. 概念：如果某一列是数值类型的，使用 auto_increment 可以来完成自动增长；
2. 在创建表时，添加主键约束，并且完成主键自动增长；
3. 删除自动增长；
代码语言：javascript
复制
```
ALTER TABLE stu MODIFY id INT ;
```
1. 创建完表后，添加自动增长。
代码语言：javascript
复制
```
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
```

CREATE TABLE stu (
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
);

-- 添加数据
INSERT INTO stu(id,NAME) VALUES (1,'露娜');
INSERT INTO stu(id,NAME) VALUES (NULL,'貂蝉');

5. 外键约束：foreign key

概述：
- 让表与表产生关系，从而保证数据的正确性。
在创建表时，添加外键

* 语法：
create table 表名 (
    ...
    外键列,
    constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
);

删除外键

alter table 表名 drop foreign key 外键名称;

创建表之后，添加外键

示例：

-- 主表必须先创建，否则外键创建失败
CREATE TABLE sch (
    id INT PRIMARY KEY AUTO_INCREMENT,
    address VARCHAR(20)  -- 地址
);

CREATE TABLE stu (
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    address_id INT, -- 外键列
    -- constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
    CONSTRAINT stu_sch_fy FOREIGN KEY (address_id) REFERENCES sch(id)
);

INSERT INTO sch(id,address) VALUES (1,'北京');
INSERT INTO sch(id,address) VALUES (NULL,'西安');

INSERT INTO stu(NAME,address_id) VALUES ('张三',1),('露娜',1),('貂蝉',2),('吕布',2),('赵云',1);
-- --------------------------分隔符-------------------------------------------

-- 删除外键
ALTER TABLE stu DROP FOREIGN KEY stu_sch_fy;

-- 创建完表后再添加外键
ALTER TABLE stu ADD CONSTRAINT stu_sch_fy FOREIGN KEY (address_id) REFERENCES sch(id);

alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主列表名称);

6.级联操作

添加级联操作：

* 语法：
alter table 表名 add constraint 外键名称
    foreign key (外键字段名称) references 主表名称(主表列名称) on update cascade on delete cascade;

分类：
1. 级联更新：on update cascade
2. 级联删除：on delete cascade

* 数据库的备份和还原

命令行：
- 语法：
  - 备份：mysqldump -u用户名 -p密码要备份的数据库名 > 保存的路径
  - 还原：
    1. 登录数据库
    2. 创建数据库
    3. 使用数据库；
    4. 执行文件：source 文件路径；
图形化工具： $$ 百度查询 $$

二、数据库的设计

1. 多表之间的关系：

1. 一对一：

如：人和身份证
分析：一个人只有一个身份证，一个身份证只能对应一个人
实现方式：可以在任意一方添加唯一外键指向另一方的主键。

2.一对多（多对一）：

如：部门和员工
分析：一个部门有多个员工，一个员工只能对应一个部门
实现方式：在多的一方建立外键，指向一的一方的主键。

3. 多对多：

如：学生和课程
分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择
实现方式：多对多关系实现需要借助第三张中间表；中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键。

4.联合主键

示例

CREATE TABLE table_uy (
    uid INT , -- 线路id
    yid INT , -- 用户id
    -- 创建复合主键
    PRIMARY KEY(uid,yid), -- 联合主键
    -- 分别相互关联对方表的主键
    FOREIGN KEY (uid) REFERENCES tab_yead (yid),
    FOREIGN KEY (yid) REFERENCES tab_user (uid)
    
);

2. 数据库设计的范式：

1. 概述：

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）

2. 函数依赖：

函数依赖：A–>B,如果通过A属性(属性组)的值，可以确定唯一B属性的值，则称B依赖于A；例如：学号 –> 姓名
完全函数依赖：A –> B,如果A是一个属性组，则B属性值的确定需要依赖于A属性组中所有的属性值；例如：（学号，课程名称） –> 分数
部分函数依赖：A –> B，如果A是一个属性组，则B属性值的确定只需要依赖于A属性组中某一些值即可；例如：（学号，课程名称） –> 姓名
传递函数依赖：A–>B,B –> C，如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性(属性组)的值可以确定唯一C属性的值，则称C传递函数依赖于A；例如：学号 –> 系名，系名 –> 系主任
码：如果在一张表中，一个属性或属性组，被其他所有属性完全依赖，则称这个属性(属性组)为该表的码。例如：该表中码为：（学号，课程名称） * 主属性：码属性组中所有的属性 * 非主属性：除过码属性组的属性

3. 三大范式：

第一范式（1NF）：每一列都是不可分割的院子数据项（只要能表能出来，都符合第一范式）；
第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于码（在1NF基础上消除非主属性对主码的函数部分依赖）；
第三范式（3NF）：在2NF基础上，任何非主属性不能依赖于其它非主属性（在2NF基础上消除传递依赖）。

三、多表查询

1. 笛卡尔积：

有两个集合A，B，取这两个集合的所有组成情况，
要完成多表查询，需要消除无用的数据

2. 多表查询的分类：

内连接查询
外连接查询
子查询
建表：

# 创建部门表
CREATE TABLE dept (
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
);
INSERT INTO dept(NAME) VALUES ('开发部'),('市场部'),('财务部');

# 创建员工表
CREATE TABLE emp (
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(10),
    sex CHAR(1),
    salary DOUBLE,
    jion_date DATE,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外键，关联部门表（部门表的主键）
);

INSERT INTO emp (NAME,sex,salary,jion_date,dept_id) VALUES ('孙悟空','男',7200,'2013-02-24',1),
('猪八戒','男',3600,'2010-12-02',2),
('唐曾','男',9600,'2008-08-08',2),
('白骨精','女',5000,'2015-10-17',3),
('蜘蛛精','女',4500,'2011-03-04',1);

3. 内连接查询：

1. 隐式内连接

使用where条件消除无用数据
示例：

-- 查询所有员工信息和对应的部门信息
SELECT * FROM emp,dept WHERE emp.dept_id = dept.id; 

-- 查询员工表的名称，性别。部门表的名称
SELECT emp.name,emp.sex,dept.name FROM emp,dept WHERE emp.dept_id = dept.id;

-- 标准写法
SELECT
    a.name, -- 员工表姓名
    a.sex, -- 员工表性别
    b.name -- 部门表姓名
FROM 
    emp a,
    dept b
WHERE 
    a.dept_id = b.id

2. 显示内连接：

* 语法：
    select 字段列表 from 表名1 [inner] jion 表名2 on 条件
    
* 示例：
-- 查询所有员工信息和对应的部门信息
SELECT * FROM emp INNER JOIN dept ON emp.dept_id = dept.id;
SELECT * FROM emp JOIN dept ON emp.dept_id = dept.id; -- inner可以省略

3. 内连接查询注意事项：

从哪些表中查询数据；
条件是什么；
查询哪些字段

4. 外连接查询：

1. 左外连接：

语法 select 字段列表 from 表1 left [outer] join 表2 on 条件;
查询的是左表所有数据以及其交集部分。
示例：

-- 左外连接
SELECT * FROM emp LEFT OUTER JOIN dept ON emp.dept_id = dept.id;

2. 右外连接：

语法： select 字段列表 from 表1 right [outer] join 表2 on 条件;
查询的是右表所有数据以及其交集部分
示例：

-- 右外连接
SELECT * FROM dept RIGHT OUTER JOIN emp ON emp.dept_id = dept.id;

5. 子查询：

1. 子查询的基本查询：

概念：查询中嵌套查询，成嵌套查询为子查询。
示例：

-- 查询最高工资员工信息
-- 1.先查询最高工资是多少
SELECT MAX(salary) FROM emp;

-- 2.查询员工信息，工资等于9600的
SELECT * FROM emp WHERE salary = 9600;

-- ----------------------------------------
-- 利用子查询完成
SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);

2.子查询的不同情况：

1. 子查询的结果是单行单列的：

子查询可以作为条件，使用运算符去判断。运算符：> , <, >=, <=, =
示例：

-- 查询工资小于平均工资的员工信息
SELECT * FROM emp WHERE salary < (SELECT AVG(salary) FROM emp);

2. 子查询的结果是多行单列的：

子查询可以作为条件，使用运算符in来判断
示例：

-- 查询财务部和市场部的所有员工信息
SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';
SELECT * FROM emp WHERE dept_id = 2 OR dept_id = 3;
SELECT * FROM emp WHERE dept_id IN (2,3);
-- 利用子查询解决
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');

3. 子查询的结果是多行多列的：

* 子查询可以作为一张虚拟表参与查询

-- 查询入职时间在2011-11-11之后的员工信息和部门信息
SELECT 
    * 
FROM 
    dept a,
    (SELECT * FROM emp WHERE jion_date > '2011-11-11') b
WHERE 
    b.dept_id = a.id;
    
-- ---------------------------------------------------
-- 普通内查询
SELECT * FROM emp a,dept b WHERE a.dept_id = b.id AND a.jion_date > '2011-11-11';

四、事务

1. 事务的基本概念

概念：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。
操作：
1. 开启事务：start tansacton
2. 回滚：rollback
3. 提交：commit

示例：

CREATE TABLE zhuangzhang (
    NAME VARCHAR(20),
    money INT
);
INSERT INTO zhuangzhang(NAME,money) VALUES ('张三',1000),('李四',1000);


-- 开启事务
START TRANSACTION;
-- 张三给李四转账
UPDATE zhuangzhang SET money = money - 500 WHERE NAME = '张三';

异常来啦

UPDATE zhuangzhang SET money = money + 500 WHERE NAME = '李四';

-- 发现没有问题，提交事务
COMMIT


-- 发现出问题了，回滚事务
ROLLBACK

2. 事务的四大特征

原子性：是不可能分割的最小操作单位，要么同时成功，要么同时失败；
持久性：当事务提交或回滚后，数据后会持久化的保存数据；
隔离性：多个事务之间，相互独立；
一致性：事务操作前后，数据总量不变。

3. 事务的隔离级别(了解)

1. 概念：

多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。

2. 存在问题：

脏读：一个事务，读取到另一个事务中没有提交的数据；
不可重复读(虚读)：早同一个事务中，两次读取到的数据不一样；
幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。

3. 隔离级别：

read uncommitted：读未提交
- 产生的问题：脏读、不可重复读、幻读
read committed：读已提交（Oracle默认级别）
- 产生的问题：不可重复读、幻读
repeatable read：可重复读（MySQL默认级别）
- 产生的问题：幻读
serializable：串行化
- 可以解决所有的问题

注意：隔离级别从小到大安全性越来越高，但是效率越来越低。

* 数据库查询隔离级别：
    select @@tx_isolation;
    
* 数据库设置隔离级别：
    set global transaction isolation level 级别字符串;

4. MySQL数据库中事务默认自动提交

1. 事务提交的两种方式：

自动提交：
- mysql就是自动提交的；
- 一条DML(增删改)语句会自动提交一次事务。
手动提交：
- Oracle数据库默认是手动提交事务；
- 需要先开启事务，再提交。

2. 修改事务的默认提交方式：

查看事务的默认提交方式
- select @@autocommit; 1代表自动提交，0代表手动提交
修改默认提交方式：
- set @@autocommit = 0;

五、DCL：管理用户、授权

1. 管理用户

1. 添加用户：

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

2. 删除用户：

drop user ‘用户名‘@’主机名’;

3. 修改用户密码：

方式1：update user set password = password(‘新密码’) where user = ‘用户名’;

方式2：set password for ‘用户名’ @’主机名’ = password(‘新密码’);

4. mysql中忘记了root用户的密码？

cmd –> net stop mysql 停止mysql服务
- 需要管理员运行cmd
使用无验证方式启动mysql：mysqld –skip-grant-tables
打开新的cmd窗口，直接输入mysql命令，敲回车，就可以登录成功
use mysql
update user set passwprd = password(‘你的新密码’) where user = ‘root’;
关闭两个窗口
打开任务管理器，手动结束mysqld.exe的进程
启动mysql服务
使用新密码登录

5. 查询用户：

1.切换到mysql数据库 use mysql;
2.查询user表 select * from user;
通配符： % 表示可以在任意主机使用用户登录数据库

2. 权限管理

1. 查询权限

show grants for ‘用户名‘@’主机名’;

2. 授予权限

grant 权限列表 on 数据库名.表名 to ‘用户名‘@’主机名’; 给哪位用户授权什么权限，在什么数据库的什么表上

给某位用户授予所有权限，在任意数据库任意表上
grant all on *.* to '用户名'@'localhost';

3. 撤销权限

revoke 权限列表 on 数据库名.表名 from ‘用户名‘@’主机名’;

六、JDBC

1. 概述：

概念：Java DataBase ConnectIvity ，Java 数据库连接，Java语言操作数据库。
JDBC本质：其实是官方(SUN公司)定义的一套操作所有关系型数据库的规则，即接口。各个数据库厂商去实现这套接口，提供数据库驱动jar包。这套接口(JDBC)编程，真正执行的代码是驱动jar包中的实现类。

2. 步骤

*        //1. 导入驱动jar包
*        //2. 注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver"); // mysql 5版本之后可以省略
*        //3. 获取数据库连接对象
        //Connection conn =DriverManager.getConnection("jdbc:mysql://localhost:3306/db3", "root", "lxj521..");
        Connection conn = DriverManager.getConnection("jdbc:mysql:///db3", "root", "lxj521..");//省略本机名和IP写法
*        //4. 定义sql语句
        String sql = "update zhuangzhang set money = 1000";
*        //5. 获取执行sql的对象     Statement
        Statement statement = conn.createStatement();
*        //6. 执行sql
        int count = statement.executeUpdate(sql);
*        //7. 处理结果
        System.out.println(count);
*        //8. 释放资源
        statement.close();
        conn.close();

3.详解各个对象：

1. DriverManager：驱动管理对象

* 功能：
    1. 注册驱动：告诉程序该使用哪一个数据库驱动jar
    static void registerDriver(Driver driver)：注册与给定的驱动程序DriverManager
    写代码使用：Class.forName("com.mysql.jdbc.Driver")
    
    通过查看源码发现：在com.mysql.jdbc.Driver类中存在静态代码块.
* 注意：mysql 5之后的驱动jar包可以省略注册驱动的步骤。但建议还是写上。

    2. 获取数据库连接
    * 方法：static Connection getConnection(String url,String user,String password)
    * 参数：
        * url:指定连接的路径
            * 语法：jdbc:mysql://ip地址(域名):端口号/数据库名称
            * 例子：jdbc:mysql://localhost:3306/db3
            * 细节：如果连接的是本机mysql服务器，并且mysql服务器默认端口是3306，则url可以简写为：jdbc:mysql:///数据库名称
        * user：用户名
        * password：密码

2. Connection:数据库连接对象

* 功能：
    1. 获取执行sql的对象
        * Statement createStatement()
        * PreparedStatement preparedStatement(String sql)
    2. 管理事务：
        * 开启事务：setAutoCommit(boolean autoCommit)：调用该方法设置参数为false，即开启事务
        * 提交事务：commit()
        * 回滚事务：rollback()

3. Statement：执行sql的对象

* 执行sql
    1. boolean execute(String sql):可以执行任意的sql（了解）
    2. int executeUpdate(String sql)：执行DML(insert,update,delete)语句，DDL(create,alter,drop)语句
        * 返回值：影响的行数，可以通过这个影响地行数判断DML是否执行成功，返回值>0的则执行成功，反之，则失败。
    3. ResultSet executeQuery(String sql)：执行DQL(select)语句。
        * 返回值为结果集对象。

4. ResultSet：结果集对象

* next()：游标向下移动一行
* getXxx(参数)：获取数据
    * Xxx：代表数据类型   如：int：getInt(), String:getString()
    * 参数：
        1.int :代表列的编号，从1开始。  如：getString(1)
        2.String:代表列名称。 如：getDoble("balance")

示例：

public class Demo05 {
    public static void main(String[] args) {
        ResultSet resultSet = null;
        Statement statement = null;
        Connection conn = null;
        try {
            //1.注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取数据库连接对象
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3", "root", "lxj521..");
            //3.定义sql
            String sql = "select * from zhuangzhang";
            //4.获取sql执行对象
            statement = conn.createStatement();
            //5.执行sql
            resultSet = statement.executeQuery(sql);
            //6.处理结果
            //6.1 游标向下移动一行：因为要查询第一行的数据
            resultSet.next();
            //6.2 获取数据
            String name = resultSet.getString(1);
            double money = resultSet.getDouble("money");
            //7.处理结果
            System.out.println(name + "===" + money);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ////8. 释放资源
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

附加：ResultSet遍历

* boolean next()：游标向下移动一行，判断当前是否是最后一行末尾(是否有数据),如果是，则返回false，如果不是则返回true

* 利用while循环遍历
while (rs.next()) {
                String name = rs.getString(1);
                double money = rs.getDouble("money");
                System.out.println(name + "---" + money);
            }

练习：查询emp表中所有数据

先创建一个emp类

public class emp {
    private int id;
    private String name;
    private String sex;
    private double salary;
    private Date jion_date;
    private int dept_id;

    public emp(int id, String name, String sex, double salary, Date jion_date, int dept_id) {
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.salary = salary;
        this.jion_date = jion_date;
        this.dept_id = dept_id;
    }

    public emp() {

    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public double getSalary() {
        return salary;
    }

    public void setSalary(double salary) {
        this.salary = salary;
    }

    public Date getJion_date(Date jion_date) {
        return this.jion_date;
    }

    public void setJion_date(Date jion_date) {
        this.jion_date = jion_date;
    }

    public int getDept_id() {
        return dept_id;
    }

    public void setDept_id(int dept_id) {
        this.dept_id = dept_id;
    }

    @Override
    public String toString() {
        return "emp{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex=" + sex +
                ", salary=" + salary +
                ", jion_date=" + jion_date +
                ", dept_id=" + dept_id +
                '}';
    }
}
/*
* 提示：该行代码过长，系统自动注释不进行高亮。一键复制会移除系统注释 
* <!--hexoPostRenderEscape:<figure class="highlight plain"><table><tr><td class="code"><pre><span class="line"></span><br><span class="line">  * 再创建一个主类，调用emp方法</span><br><span class="line"></span><br><span class="line">&#96;&#96;&#96;java</span><br><span class="line">public class Demo07 &#123;</span><br><span class="line">    public static void main(String[] args) &#123;</span><br><span class="line">        List&lt;emp&gt; list &#x3D; new Demo07().findAll();</span><br><span class="line">        System.out.println(list);</span><br><span class="line">        System.out.println(list.size());</span><br><span class="line"></span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    public List&lt;emp&gt; findAll() &#123;</span><br><span class="line">        ResultSet rs &#x3D; null;</span><br><span class="line">        Statement statement &#x3D; null;</span><br><span class="line">        Connection conn &#x3D; null;</span><br><span class="line"></span><br><span class="line">        List&lt;emp&gt; list &#x3D; null;</span><br><span class="line">        try &#123;</span><br><span class="line">            &#x2F;&#x2F;1.注册驱动</span><br><span class="line">            Class.forName(&quot;com.mysql.cj.jdbc.Driver&quot;);</span><br><span class="line">            &#x2F;&#x2F;2.获取连接数据库对象</span><br><span class="line">            conn &#x3D; DriverManager.getConnection(&quot;jdbc:mysql:&#x2F;&#x2F;&#x2F;db2&quot;, &quot;root&quot;, &quot;lxj521..&quot;);</span><br><span class="line">            &#x2F;&#x2F;3.定义sql</span><br><span class="line">            String sql &#x3D; &quot;select * from emp&quot;;</span><br><span class="line">            &#x2F;&#x2F;4.获取处理sql对象</span><br><span class="line">            statement &#x3D; conn.createStatement();</span><br><span class="line">            &#x2F;&#x2F;5.处理sql</span><br><span class="line">            rs &#x3D; statement.executeQuery(sql);</span><br><span class="line">            &#x2F;&#x2F;6.处理结果</span><br><span class="line">            emp emp &#x3D; null;</span><br><span class="line">            list &#x3D; new ArrayList&lt;emp&gt;();</span><br><span class="line">            while (rs.next()) &#123;</span><br><span class="line">                int id &#x3D; rs.getInt(&quot;id&quot;);</span><br><span class="line">                String name &#x3D; rs.getString(&quot;name&quot;);</span><br><span class="line">                String sex &#x3D; rs.getString(&quot;sex&quot;);</span><br><span class="line">                double salary &#x3D; rs.getDouble(&quot;salary&quot;);</span><br><span class="line">                Date jion_date &#x3D; rs.getDate(&quot;jion_date&quot;);</span><br><span class="line">                int dept_id &#x3D; rs.getInt(&quot;dept_id&quot;);</span><br><span class="line"></span><br><span class="line">                &#x2F;&#x2F;创建emp对象，并赋值</span><br><span class="line">                emp &#x3D; new emp();</span><br><span class="line">                emp.setId(id);</span><br><span class="line">                emp.setName(name);</span><br><span class="line">                emp.setSex(sex);</span><br><span class="line">                emp.setSalary(salary);</span><br><span class="line">                emp.getJion_date(jion_date);</span><br><span class="line">                emp.setDept_id(dept_id);</span><br><span class="line"></span><br><span class="line">                &#x2F;&#x2F;装载集合</span><br><span class="line">                list.add(emp);</span><br><span class="line">            &#125;</span><br><span class="line">        &#125; catch (ClassNotFoundException e) &#123;</span><br><span class="line">            e.printStackTrace();</span><br><span class="line">        &#125; catch (SQLException e) &#123;</span><br><span class="line">            e.printStackTrace();</span><br><span class="line">        &#125; finally &#123;</span><br><span class="line">            if (rs !&#x3D; null) &#123;</span><br><span class="line">                try &#123;</span><br><span class="line">                    rs.close();</span><br><span class="line">                &#125; catch (SQLException e) &#123;</span><br><span class="line">                    e.printStackTrace();</span><br><span class="line">                &#125;</span><br><span class="line">            &#125;</span><br><span class="line">            if (statement !&#x3D; null) &#123;</span><br><span class="line">                try &#123;</span><br><span class="line">                    statement.close();</span><br><span class="line">                &#125; catch (SQLException e) &#123;</span><br><span class="line">                    e.printStackTrace();</span><br><span class="line">                &#125;</span><br><span class="line">            &#125;</span><br><span class="line">            if (conn!&#x3D;null) &#123;</span><br><span class="line">                try &#123;</span><br><span class="line">                    conn.close();</span><br><span class="line">                &#125; catch (SQLException e) &#123;</span><br><span class="line">                    e.printStackTrace();</span><br><span class="line">                &#125;</span><br><span class="line">            &#125;</span><br><span class="line">        &#125;</span><br><span class="line">        return list;</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>:hexoPostRenderEscape-->
*/

5. PreparedStatement:执行sql的对象

1. SQL注入问题：

在拼接sql时，有一些sql的特殊关键字参与字符串的拼接，会造成安全性问题；
* 1.输入用户随便，输入密码：a' or 'a' = 'a
  2.sql:select * from user where username = "ahdahal" and password = a' or 'a' = 'a

2. 解决sql注入问题：

使用PreparedStatement对象来解决

3. 预编译的SQL：

参数使用 ? 作为占位符

*4. 步骤：**

1.导入驱动jar包;
2.注册驱动；
3.获取数据库连接对象 Connection
4.定义sql
    * 注意：sql的参数使用?作为占位符。
      如：select * from user where username = ? and password = ?;
5.获取执行sql语句的对象    PreparedStatement
    * Connection.prepareSttement(String sql)
6.给?赋值：
    * 方法：setXxx(参数1,参数2)
        * 参数1：?的位置编号 从1 开始
        * 参数2：?的值
7.执行sql，接受返回结果，不需要传递sql语句
8.处理结果
9.释放资源

示例：

public class Demo02 {
    public static void main(String[] args) {


        //键盘录入
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名：");
        String username = sc.nextLine();
        System.out.println("请输入密码：");
        String password = sc.nextLine();
        //调用对象
        boolean flag = new Demo02().login2(username, password);
        //判断结果
        if (flag) {
            System.out.println("登录成功");
        } else {
            System.out.println("用户名或者密码错误");
        }
    }

    //登录方法
    public boolean login2(String username,String password) {
        if ((username == null) || (password == null)) {
            return false;
        }

        //连接数据库判断是否登录成功
        Connection conn = null;
        PreparedStatement pstem =null;
        ResultSet rs = null;
        try {
            //1.获取连接数据库对象
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "select * from user where username = ? and password = ?";
            //3.获取处理sql对象
             pstem = conn.prepareStatement(sql);
             //给?赋值
            pstem.setString(1,username);
            pstem.setString(2,password);
            //4.执行查询sql，不需要传递sql
            rs = pstem.executeQuery();
            //5.判断
            return rs.next();//如果有下一行，则返回true
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(rs,pstem,conn);
        }

        return false;
    }
}

5.注意：

后期都会使用PreparedStatement来完成增删改查的所有操作
- 1、可以防止SQL注入；
- 2、效率更高

七、JDBC工具类：JDBCUtils

目的：简化书写
分析：
- 1、注册驱动也抽取；
- 2、抽取一个方法获取连接对象

* 需求：不想传递参数(麻烦)，还得保证工具类的通用性。
* 解决：配置文件：
    * 创建 jdbc.properties    文件
          url = 
          user = 
          password = 
          driver = 
* 示例：
url=jdbc:mysql:///db2
user=root
password=lxj521..
driver=com.mysql.cj.jdbc.Driver

创建工具类：

* 首先创建名为JDBCUtils.java  文件 

/*
 * JDBC工具类
 * */

import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Collection;
import java.util.Properties;

public class JDBCUtils {
    private static String url;
    private static String user;
    private static String password;
    private static String driver;

    /*
    * 文件的读取，只需要读取一次即可拿到这些值。只用静态代码块
    * */
    static {
        //读取资源文件，获取值

        try {
            //1.创建Properties集合类
            Properties pro = new Properties();

            //获取src路径下的文件的方式：ClassLoader 类加载器
            ClassLoader classLoader = JDBCUtils.class.getClassLoader();
            URL resource = classLoader.getResource("jdbc.properties");
            String path = resource.getPath();
            //System.out.println(path);

            //2.加载文件
//            pro.load(new FileReader("src/jdbc.properties"));
            pro.load(new FileReader(path));
            //3.获取数据，赋值
            url = pro.getProperty("url");
            user = pro.getProperty("user");
            password = pro.getProperty("password");
            driver = pro.getProperty("driver");
            //4.注册驱动
            Class.forName(driver);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /*
    * 获取连接
    * @return 连接对象
    * */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,user,password);
    }

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

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

调用工具示例：

public class Demo {
    public static void main(String[] args) {
        List<emp> list = new Demo().findAll();
        System.out.println(list);
        System.out.println(list.size());

    }

    public List<emp> findAll() {
        ResultSet rs = null;
        Statement statement = null;
        Connection conn = null;

        List<emp> list = null;
        try {
            //1.注册驱动
//            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接数据库对象
//            conn = DriverManager.getConnection("jdbc:mysql:///db2", "root", "lxj521..");
            conn = JDBCUtils.getConnection();
            //3.定义sql
            String sql = "select * from emp";
            //4.获取处理sql对象
            statement = conn.createStatement();
            //5.处理sql
            rs = statement.executeQuery(sql);
            //6.处理结果
            emp emp = null;
            list = new ArrayList<emp>();
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String sex = rs.getString("sex");
                double salary = rs.getDouble("salary");
                Date jion_date = rs.getDate("jion_date");
                int dept_id = rs.getInt("dept_id");

                //创建emp对象，并赋值
                emp = new emp();
                emp.setId(id);
                emp.setName(name);
                emp.setSex(sex);
                emp.setSalary(salary);
                emp.getJion_date(jion_date);
                emp.setDept_id(dept_id);

                //装载集合
                list.add(emp);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //释放资源
            JDBCUtils.close(rs, statement, conn);
        }
        return list;
    }
}

八、JDBC管理事务

1. 事务：

一个包含多个步骤的业务操作。如果这个业务操作被事务管理，则这多个步骤要么同时成功，要么同时失败。

2. 操作：

开启事务
提交事务
回滚事务

3. 使用Connection对象来管理事务

* 开启事务：setAutoCommit(boolean autoCommit)：调用该方法设置参数为false，即开启事务；
        *   在执行sql之前开启事务
* 提交事务：commit()
        *   当所有sql都执行完提交事务
* 回滚事务：rollback()
        *   在catch中回滚事务

示例：
发生异常后，如果数据没有变化，则说明我们管理事务成功，反之则失败。

public class Demo3 {
    public static void main(String[] args) {
        PreparedStatement pstm1 = null;
        PreparedStatement pstm2 = null;
        Connection conn = null;
        try {
            //1.注册驱动
            conn = JDBCUtils.getConnection();

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

            //2.定义sql
            String sql1 = "update zhuangzhang set money = money - ? where name = ?";
            String sql2 = "update zhuangzhang set money = money + ? where name = ?";
            //3.获取处理sql对象
            pstm1 = conn.prepareStatement(sql1);
            pstm2 = conn.prepareStatement(sql2);
            //4.给？赋值
            pstm1.setDouble(1, 500);
            pstm1.setString(2, "张三");

            pstm2.setDouble(1, 500);
            pstm2.setString(2, "李四");
            //5.处理sql，不需要获取sql
            pstm1.executeUpdate();

            //手动设置异常
            int i = 3 / 0;

            pstm2.executeUpdate();
        } catch (SQLException e) {
            //回滚事务
            try {
                if (conn != null) {
                    conn.rollback();
                }
            } catch (Exception ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            //释放资源
            JDBCUtils.close(pstm1, conn);
            JDBCUtils.close(pstm2, null);
        }
    }
}

九、数据库连接池

1. 概念：

其实就是一个容器(集合)，存放数据库连接的容器。

当系统初始化好后，容器被创建，容器中会申请宁一些连接对象，当用户来访问数据库时，从容器中中获取连接对象，榕湖访问完之后，会将连接对象归还给容器。

2. 好处：

节约资源；
用户访问高效。

3. 实现：

1. 标准接口：DataSource javax.sql包下的

方法：
    * 获取连接：getConnection()
    * 归还连接：Connection.close().如果连接对象Connection是从连接池获取的，那么调用Connection.close()方法，则不会再关闭连接了，而是归还连接。

2. 连接池种类：

一般我们不会去实现它，有数据库厂商来实现
- 1、C3P0：数据库连接池技术；
- 2、Druid：数据库连接池实现技术，由阿里巴巴提供的。

4.C3P0

* 步骤：
    1.导入jar包(两个)c3p0-0.9.5.5-sources.jar和mchange-commons-java-0.2.19-sources.jar；
        * 不要忘记导入数据库驱动jar包：mysql-connector-java-8.0.19.jar
    2.定义配置文件：
        * 名称：c3p0.properties 或者 c3p0-config.xml
        * 路径：直接将文件放在src目录下即可。
    3.创建核心对象：
        数据库连接池对象 ComboPooledDataSource
    4.获取连接：getConnection

5.Druid

1. 步骤

* 步骤：
        //1.导入jar包     druid-1.1.18.jar
        //2.定义配置文件
        * 是properties形式的
        * 可以叫任意名称，可以放在任意目录下

        //3.加载配置文件    Properties
        Properties pro = new Properties();                                    InputStreamis=DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties");
        pro.load(is);

        //4.获取连接池对象:通过工厂来获取  DruidDataSourceFactory
        DataSource ds = DruidDataSourceFactory.createDataSource(pro);

        //5.获取连接    getConnection
        Connection conn = ds.getConnection();
        System.out.println(conn);

配置文件：druid.properties

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql:///db3
username=root
password=lxj521..
# 初始化连接数量
initialSize=5
# 最大连接数
maxActive=10
# 最大等待时间
maxWait=3000

2. druid工具类：

1.定义一个类：JDBCUtils
2.提供静态代码块加载配置文件，初始化连接池对象
3.提供方法
    1.获取连接方法：通过数据库连接池获取连接；
    2.释放资源；
    3.获取连接池的方法。

创建工具类：JDBCUtils.java

public class JDBCUtils {

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

    static {

        try {
            //1.记载配置文件
            Properties prop = new Properties();
            prop.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
            //2.获取DataSource
            ds = DruidDataSourceFactory.createDataSource(prop);

        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

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

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

    public static void close(ResultSet resultSet,Statement statement, Connection conn) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close(); //归还连接
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //获取连接池方法
    public static DataSource getDataSource() {
        return ds;
    }
}

示例：给zhuangzhang表里添加数据：

public class Demo02 {
    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //1.获取连接：
            conn = JDBCUtils.getConnection();
            //2.定义sql：
            String sql = "insert into zhuangzhang value (?,?)";
            //3.获取处理sql对象
            ps = conn.prepareStatement(sql);
            //4.给?赋值
            ps.setString(1, "王五");
            ps.setDouble(2, 2000);
            //5.执行sql
            int count = ps.executeUpdate();
            System.out.println(count);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6.释放资源
            JDBCUtils.close(ps, conn);
        }
    }
}

十、JDBCTemplate

spring JDBC

Spring框架对JDBC的简单封装。提供了一个JDBCTemplate对象简化JDBC的开发
步骤：
1.导入jar包 2.创建JDBCTemplate对象，依赖于数据源DataSource * JdbcTemplate template = new JdbcTemplate(ds); 3.调用JdbcTemplate的方法来完成CRUD的操作： * update():执行DML语句。增删改 * queryForMap():查询结果将结果集封装为map集合 * queryForList():查询结果将结果集封装为list集合 * query():查询结果，将结果封装为JavaBean对象 * queryForObject:查询结果，将结果封装为对象

https://repo.spring.io/release/org/springframework/

文章作者: silentcow

文章链接: http://silentcow.cn/2020/08/06/MySQL%E4%B8%8EJDBC%E7%B2%BE%E8%AE%B2%E7%AC%94%E8%AE%B0/

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2020-08-06，如有侵权请联系 cloudcommunity@tencent.com 删除

jdbc

云数据库 SQL Server

数据库

sql

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！