数据库入门教程
     
  数据库
2 小时 读完 (大约 19827 个字)

数据库入门教程

数据库的基本概念

数据库初识

数据库 就是用于存储和管理数据的仓库，它的英文单词： DataBase ，简称 ： DB 。数据库本质上是一个文件系统，还是以文件的方式存在服务器的电脑上的。所有的关系型数据库都可以使用通用的 SQL 语句进行管理 ，即我们常说的 数据库管理系统 DBMS 【DataBase Management System】

数据库的特点

数据库的特点：
1、持久化存储数据的。其实数据库就是一个文件系统。 2、存储和管理数据。 3、使用了统一的方式操作数据库 --> SQL 。

常见数据库

常见的数据库：
1、Oracle：收费的大型数据库，Oracle 公司的产品。 2、MySQL：开源免费的数据库，小型的数据库，已经被 Oracle 收购了。MySQL6.x 版本也开始收费。 3、DB2：IBM 公司的数据库产品,收费的。常应用在银行系统中。 4、SQLServer：MicroSoft 公司收费的中型的数据库。C#、.net 等语言常使用。 5、SQLite：嵌入式的小型数据库，应用在手机端，如：Android。

数据库排行榜

MySQL 数据库

MySQL 安装

以下将 以图的方式 简述 MySQL 安装过程，如下：




















进入命令行 ，测试 MySQL 是否安装成功：

MySQL 卸载

有时候我们安装 MySQL 不成功，这个时候就要卸载 MySQL。但是，如果卸载不干净，那么再次安装 MySQL 的成功概率就特别低了，甚至就再也不能成功安装了。所以需要学会卸载 MySQL。话不多说，卸载步骤走起。第一步： 找到 MySQL 安装目录，打开 my.ini 文件：

第二步： 使用 CTRL + F 查找 datadir ，找到的结果就是 MySQL 的数据存放目录，如果不出意外，大家结果都是一样的。找到之后复制出来，后面会用到这个目录。我的查找结果如下：

1
2
#Path to the database root
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"

第三步： win + r ，然后输入 appwiz.cpl 打开应用控制面板，找到 MySQL ，然后卸载，如下图：

第四步： 当我们卸载了 MySQL 之后，其实并没有卸载干净。这时就要用到 第二步 查找到的那个目录的路径。我们截取此目录路径的一部分： C:/ProgramData ，然后打开这个目录，如下图：

到这里为止，MySQL 的安装卸载 就搞定了。

MySQL 服务启动和关闭

图形界面方式： 打开命令行 win + r ，然后输入 services.msc 进入到服务，如下图：

命令行方式： 打开命令行 win + r ，然后输入 net stop mysql 关闭 MySQL 服务，如下图：

哎，发现不行呀。对的，这是权限不足问题。现在我们以 管理员身份 运行 net stop mysql 试试吧。如下图：

MySQL 登陆和退出

命令行方式： 打开命令行 win + r ，然后输入 cmd 进入到命令行，相关操作如下图：

还记得我们安装 MySQL 设置密码的时候，那里勾选了远程连接，用来连接到远程的数据库。现在来讲解一下。举个例子：比如我要连接到我云服务器的 MySQL ，咋弄？ 来，这么办，演示如下图：

MySQL 目录结构

MySQL 的安装目录

MySQL 的安装目录其实就是 MySQL 的安装位置 ，这个目录里面的文件我们简单介绍一下：

MySQL 的数据目录


SQL 初识

什么是 SQL

结构化查询语言(Structured Query Language) 简称 SQL ，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。【————百度百科】 SQL 的 作用 其实就是 定义了操作所有关系型数据库的规则 。但是每一种数据库操作的方式存在不一样的地方，称为 “方言” 。

SQL 通用语法

SQL 特点：
1、SQL 语句可以单行或多行书写，以分号结尾。 2、可使用空格和缩进来增强语句的可读性。 3、MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。
SQL 3 种注释：
1、单行注释: -- 注释内容 【-- 后面有一个空格，没有会出错】 2、单行注释: # 注释内容( mysql 特有) 【#后面可以没有空格】 3、多行注释: /* 注释内容，和 Java 多行注释一样 */ 
1
2
3
4
5
select * from user;    -- 单行注释

select * from user;    # 单行注释 ( mysql 特有)

select * from user;    /* 多行注释 */

SQL 分类

我将以图的方式对下述 四种 SQL 分类 进行介绍，请看下图：

SQL-小白命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
/*
 * DDL 命令操作数据库
 */

-- 查询数据库
show databases;                          # 查看所有数据库
show create database mysql;              # 查看 mysql 数据库的创建语句，其实主要目的是查看数据库的字符集

-- 创建数据库
create database db01;                    # 创建 db01 数据库，默认字符集是 utf8
create database if not exists db01;      # 判断 db01 数据库是否存在，存在就不创建了，不存在就创建
create database db02 character set gbk;  # 创建 db02 数据库并指定 gbk 字符集

-- 修改和删除数据库
alter database db02 character set utf8;  # 修改 db02 数据库的字符集为 utf8
drop database db02;                      # 删除 db02 数据库，如果不存在这个数据库，会报错
drop database if exists db02;            # 如果存在 db02 数据库，则删除数据库，不存在，不会报错

-- 使用数据库
select database();                       # 查询当前正在使用的数据库名称
use db02;                                # 使用 db02 数据库
use book-system;                         # 这样写会报 1064 错误，正确写法： use `book-system`;



/*
 * DDL 命令操作数据表
 */
 
-- 查询数据表
show tables;                             # 查询当前数据库的所有表
show create table stu_tbl;               # 查看 stu_tbl 数据表的字符集
desc book_tbl;                           # 查询 book_tbl 表结构，desc 是 description 的缩写

-- 创建和复制数据表
create table stu_tbl(                    # 创建一张名为 stu_tbl 的数据表
    stu_id int,                          # id 字段为第一列，以下以此类推， 字段类型为 int
    stu_name varchar(128),               # name 字段为第二列，字段类型为 varchar
    stu_age int, 
    stu_birthday date,
    stu_address varchar(128)             # 这是最后一个字段，它的后面没有逗号
);
create table student_tbl like stu_tbl;   # 复制一张和 stu_tbl 一样的表，名字为 student_tbl

-- 修改和删除数据表
drop table stu_tbl;                      # 删除 stu_tbl 数据表，如果不存在这个数据表，会报错
drop table if exists stu_tbl;            # 如果存在 stu_tbl 数据表，则删除数据表，不存在，不会报错
alter table stu_tbl rename to stu;       # 把 stu_tbl 数据表重命名为 stu
alter table stu_tbl character set gbk;   # 把 stu_tbl 数据表的字符集修改为 gbk
alter table stu_tbl add gender varchar(32);      # 给 stu_tbl 数据表添加 gender 字段，数据类型为 varchar。就是新增了 gender 这么一列
alter table stu_tbl change gender sex int;       # 把 stu_tbl 数据表的 gender 列改名为 sex 列，并且修改了数据类型为 int
alter table stu_tbl modify gender int;           # 把 stu_tbl 数据表的 gender 列的数据类型修改为了 int
alter table stu_tbl drop gender;         # 删除 stu_tbl 数据表的 gender 列



/*
 * DML 命令，添加、删除、修改表中的数据
 */
 
-- 添加数据，遇到字符和日期类型，需要用引号（单双都行）
insert into stu_tbl (stu_id,stu_name) values (1,'Jack');               # 给 id 和 name 两个字段插入值，记得数据类型要对应，否则报错
insert into stu_tbl (stu_id,stu_name,stu_age,stu_birthday,stu_address) values (1,'Jack',18,"1997-6-6",'hangzhou');   # 给所有字段插入值，这是完整的写法
insert into stu_tbl values (1,'Jack',18,null,'hangzhou');              # 给所有字段插入值，这是简化的写法

-- 删除数据
delete from stu_tbl;                             # 删除 stu_tbl 表中所有数据，有多少条记录就会执行多少次删除操作，效率很慢，不推荐使用【谨慎操作】
truncate table stu_tbl;                          # 先删除 stu_tbl 表，然后重新创建同名的空表，删除效率高，推荐使用【谨慎操作】
delete from stu_tbl where id = 1;                # 删除 stu_tbl 表中 id=1 的那一行数据
delete from stu_tbl where name = 'Tom';          # 删除 stu_tbl 表中 name='Tom' 的那一行数据

-- 修改数据
UPDATE stu_tbl SET name = "Jack" WHERE id = 1;    #  把 id=1 的记录中的 name 修改为 Jack

DDL-数据定义语言

DDL 概念： 数据定义语言【DDL】是 (Data Definition Language) 的缩写形式。用来定义和操作数据库对象。例如：数据库，表，列等。相关关键字： create , drop , alter 等。

CRUD 操作：
1、C(Create):    创建 2、R(Retrieve)： 查询 3、U(Update):    修改 3、D(Delete):    删除

安装完 MySQL 后，系统自带了 4 个数据库。都代表什么呢。相关的介绍如下图：

接下来介绍 创建数据库 和 指定字符集 （指定字符集的命令在 SQL-小白命令 处）相关的操作，如下图：


操作数据库的其他命令就不再一个一个地演示了，详情看上方的 SQL 命令汇总 自行练习。接下来介绍 DDL 命令操作数据表 。

接着我们介绍 创建数据表和查询数据表 操作，首先认识 SQL 的数据类型，如下图：

接下来我们 创建表和查询表 ，操作如下图：


操作数据表的其他命令 就不再一个一个地演示了，详情看上方的 SQL 命令汇总 自行练习。接下来介绍 图形化界面工具 SQLyog 。

图形化界面工具有时间再补充……

DML-数据操作语言

DML 概念： 数据操作语言【DML】是 (Data Manipulation Language) 的缩写形式。用来对数据库中 表的数据进行增删改 。相关关键字： insert , delete , update 等。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
/*
 * DML 命令，添加、删除、修改表中的数据
 */

-- 命令模板
# 添加数据。注意事项：1、列名和值要一一对应；2、除了数字类型，其他类型需要使用引号(单双都可以)引起来
insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
# 如果表名后，不定义列名，则默认给所有列添加值
insert into 表名 values(值1,值2,...值n);

# 删除数据,如果不加条件，则删除表中所有记录。
delete from 表名 [where 条件];
# 不推荐使用。有多少条记录就会执行多少次删除操作，效率低。此条命令谨慎使用
delete from 表名;
# 推荐使用，效率更高 先删除表，然后再创建一张一样的表。此条命令谨慎使用
TRUNCATE TABLE 表名;

# 修改数据，如果不加任何条件，则会将表中所有记录全部修改。
update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];



-- 命令实际操作
-- 添加数据，遇到字符和日期类型，需要用引号（单双都行）
insert into stu_tbl (stu_id,stu_name) values (1,'Jack');               # 给 id 和 name 两个字段插入值，记得数据类型要对应，否则报错
insert into stu_tbl (stu_id,stu_name,stu_age,stu_birthday,stu_address) values (1,'Jack',18,"1997-6-6",'hangzhou');   # 给所有字段插入值，这是完整的写法
insert into stu_tbl values (1,'Jack',18,null,'hangzhou');              # 给所有字段插入值，这是简化的写法

-- 删除数据
delete from stu_tbl;                             # 删除 stu_tbl 表中所有数据，有多少条记录就会执行多少次删除操作，效率很慢，不推荐使用【谨慎操作】
truncate table stu_tbl;                          # 先删除 stu_tbl 表，然后重新创建同名的空表，删除效率高，推荐使用【谨慎操作】
delete from stu_tbl where id = 1;                # 删除 stu_tbl 表中 id=1 的那一行数据
delete from stu_tbl where name = 'Tom';          # 删除 stu_tbl 表中 name='Tom' 的那一行数据

-- 修改数据
UPDATE stu_tbl SET name = "Jack" WHERE id= 1;    #  把 id=1 的记录中的 name 修改为 Jack
UPDATE stu_tbl SET name = "Jack";                #  表中的 name 字段的值都是 Jack，大家的名字都一样了。谨慎操作

由于时间关系，具体的操作截图就不弄了，命令模板 和 命令实际操作 上述代码都有，参阅然后自行实操。

DQL-数据查询语言

DQL 概念： 数据查询语言【DQL】是 (Data Query Language) 的缩写形式。用来查询数据库中表的记录(数据)。相关关键字： select , where 等。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 基本语法介绍

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

接下来，我们使用上述基本语法实现各种查询骚操作。第一步： 创建一张表并插入数据，操作如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 创建 student 表
CREATE TABLE student(
id INT,                 -- 编号
NAME VARCHAR(20),       -- 姓名
age INT,                -- 年龄
sex VARCHAR(5),         -- 性别
address VARCHAR(100),   -- 地址
math INT,               -- 数学
english INT             -- 英语
);

-- 向 student 表中插入数据
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);

-- 查询 student 表
SELECT * FROM student;

第二步： 我们使用 SQLyog 图形化界面工具创建上述的 student 表 ，并插入数据和查询，如下图：

第三步： 开始操作。接下来的各种查询操作都是 基于 student 表 ，所以必须创建好，并且有数据，以下是 基础查询操作命令 如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
-- 基础查询操作
select * from student;                       # 查询 student 表所有数据
select name,address from student;            # 查询 student 表中 name 和 address
select address from student;                 # 只查询 address ，结果可能重复
select distinct address from student;        # 只查询 address ，去除了重复数据。注意事项：结果集必须都一样才能去重。例如 3,"23" 和 4,"23" 是不能去重的，结果集不是完全一样


-- 计算 math 和 English 之和。如果字段比较多，就应该这么写查询语句
select                                       
    name,
    math,
    english,
    (math + english)                         # 这里 english 可能为 null，那么计算结果也为 null，这样计算显然不合理 我们使用 ifnull 对其判断，如下
from 
    student;


-- 对有 null 值的数据进行优化，使用 IFNULL 函数
select                                       
    name,
    math,
    english,
    (math + ifnull(english,0))               # 使用 ifnull 对其判断，有 null 就返回 0
from 
    student;


-- 给 (math + ifnull(english,0)) 起别名，使用 as 关键字
select                                       
    name,
    math,
    english,
    (math + ifnull(english,0)) as scores     # 起别名为 scores ，也可以使用空格替代 as
from 
    student;


-- 使用 空格和关键字 AS 起别名操作
select                                       
    name 名字,                               # 给 name 起别名为 名字，使用的是空格起别名
    math as 数学,                            # 给 math 起别名为 数学，使用的是 as 关键字
    english,
    (math + ifnull(english,0)) as scores     # 起别名为 scores ，也可以使用空格替代 as
from 
    student;

第四步： 上述基础查询操作命令自己去练习，时间紧张，就不截图了。接下来介绍 条件查询操作命令 ，如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- 条件查询操作，where 子句后跟条件

# 比较运算符 > 、< 、<= 、>= 、= 、<>
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;                 # 查询年龄等于 20 岁的数据
SELECT * FROM student WHERE age != 20;                # 查询年龄不等于 20 岁的数据
SELECT * FROM student WHERE age <> 20;                # 查询年龄不等于 20 岁的数据


# 范围查询：BETWEEN...AND 【and 或 &&】【or 或 || 】
SELECT * FROM student WHERE age >= 20 && age <= 30;   # 查询年龄在 [20,30] 岁之间的数据，不推荐使用
SELECT * FROM student WHERE age >= 20 AND age <= 30;  # 查询年龄在 [20,30] 岁之间的数据，不推荐使用
SELECT * FROM student WHERE age BETWEEN 20 AND 30;    # 查询年龄在 [20,30] 岁之间的数据，推荐使用


# IN( 集合)  【not 或 !】
SELECT * FROM student WHERE age = 20 OR age = 18 OR age=25;   # 查询年龄为 20、18 或者 25 岁的数据
SELECT * FROM student WHERE age IN(20,18,25);                 # 查询年龄为 20、18 或者 25 岁的数据
SELECT * FROM student WHERE age NOT IN(20,18,25);             # 查询年龄不为 20、18 且 25 岁的数据


# IS NULL
SELECT * FROM student WHERE english = NULL;                   # 查询 english 没成绩的数据。但是这是错误的 SQL 语句，结果显示 Empty Set 。【不能写 XXX = NULL】
SELECT * FROM student WHERE english IS NULL;                  # 查询 english 没成绩的数据。
SELECT * FROM student WHERE english IS NOT NULL;              # 查询 english 有成绩的数据。


# LIKE：模糊查询。两种占位符：下划线 _ 代表单个任意字符， 百分号 % 代表多个任意字符
SELECT * FROM student WHERE name like "马%";                  # 查找姓 ”马“ 的人
SELECT * FROM student WHERE name like "_化%";                 # 查找姓名第二个字是 ”化“ 的人
SELECT * FROM student WHERE name like "___";                  # 查找姓名是三个字的人
SELECT * FROM student WHERE name like "%德%";                 # 查找姓名中包含 ”德“ 的人

第五步： 上述条件查询操作命令自己去练习，时间紧张，就不截图了。接下来介绍 排序查询操作命令 ，如下：

1
2
3
4
5
6
7
8
# 排序查询操作语法： order by 排序字段1 排序方式1 ，  排序字段2 排序方式2...
# 排序方式：ASC：升序，默认的；DESC：降序。
# 注意事项：如果有多个排序条件，只有当前边的条件值一样时，才会进行第二个条件。

SELECT * FROM student ORDER BY math;                       # 以 math 成绩 排序所有记录，默认是升序
SELECT * FROM student ORDER BY math ASC;                   # 以 math 成绩 升序排序所有记录
SELECT * FROM student ORDER BY math DESC;                  # 以 math 成绩 降序排序所有记录
SELECT * FROM student ORDER BY math ASC, english DESC;     # 以 math 成绩 升序排名，如果数学成绩一样，则按照英语成绩 降序排名

第六步： 接下来介绍 聚合函数操作命令 ，如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# 聚合函数：将一列数据作为一个整体，进行纵向的计算。

-- 1. count：计算个数
SELECT COUNT(id) FROM student;           # 以 id 这一列计算个数，结果是 8，因为有 8 条数据
SELECT COUNT(*) FROM student;            # 以不为空的列计算个数，结果是 8，* 号的写法是不推荐的
SELECT COUNT(english) FROM student;      # 以 english 这一列计算个数，结果是 7，因为聚合函数的计算，会排除 null 值

-- 2. max：计算最大值
SELECT MAX(math) FROM student;           # 计算 math 成绩最高分
SELECT MAX(english) FROM student;        # 计算 english 成绩最高分

-- 3. min：计算最小值
SELECT MIN(math) FROM student;           # 计算 math 成绩最低分
SELECT MIN(english) FROM student;        # 计算 english 成绩最低分
SELECT * 
FROM stu 
WHERE math=(SELECT MIN(math) FROM stu);  # 子查询，查询 math 成绩最低分的数据

-- 4. sum：计算和
SELECT SUM(math) FROM student;           # 计算 math 成绩总和
SELECT SUM(english) FROM student;        # 计算 english 成绩总和

-- 5. avg：计算平均值
SELECT AVG(math) FROM student;           # 计算 math 平均成绩
SELECT AVG(english) FROM student;        # 计算 english 平均成绩
SELECT AVG(name) FROM student;           # 计算 name 平均成绩，因为 name 非数值，所以结果是 0

第七步： 接下来介绍 分组查询操作命令 ，如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
# 分组查询语法: group by 分组字段；
#注意事项：1. 分组之后查询的字段是分组字段或者聚合函数，而不能是有关单条记录的字段，如下：
SELECT name , sex , AVG(math),COUNT(id) FROM student GROUP BY sex;    # 这条语句中的 name 是没有任何意义的虽然不报错，但是就是无意义


-- 按照性别分组。分别查询男、女同学的平均分
SELECT sex , AVG(math) FROM student 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;
SELECT sex , AVG(math),COUNT(id) 人数 FROM student WHERE math > 70 GROUP BY sex HAVING 人数 > 2;         # 在聚合函数后面起别名，方便判断，最好别起中文，这里只是演示


# where 和 having 的区别？
# 1. where 在分组之前进行限定，如果不满足条件，则不参与分组。having 在分组之后进行限定，如果不满足结果，则不会被查询出来
# 2. where 后不可以跟聚合函数，having 可以进行聚合函数的判断。

第八步： 接下来介绍 分页查询操作命令 ，如下：

1
2
3
4
5
6
7
8
# 分页查询语法：limit 开始的索引,每页查询的条数;
# 开始索引公式：开始索引 = （当前的页码 - 1） * 每页显示的条数
# 注意事项：limit 是一个 MySQL "方言" ，只能在 MySQL 中使用，其他数据库中用不了

-- 每页显示 3 条记录：第一个参数表示开始索引，第二个参数表示每页显示条数
SELECT * FROM student LIMIT 0,3;       # 查询第 1 页
SELECT * FROM student LIMIT 3,3;       # 查询第 2 页
SELECT * FROM student LIMIT 6,3;       # 查询第 3 页

DCL-数据控制语言

DCL 概念： 数据控制语言【DCL】是 (Data Control Language) 的缩写形式。用来定义数据库的访问权限和安全级别，以及创建用户。相关关键字： GRANT ， REVOKE 等。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# 1、查询用户
-- 切换到 MySQL 数据库
USE mysql;
-- 查询 user 表
SELECT * FROM USER;        -- 通配符： % 表示可以在任意主机使用用户登录数据库，localhost 代表只能在本地登陆

# 2、创建用户
-- 创建用户语法
CREATE USER '用户名'@'地址' IDENTIFIED BY '密码';
-- 创建用户举例
CREATE USER 'zhangsan'@'%' IDENTIFIED BY '123';             -- 可在本地和远程登陆
CREATE USER 'lisi'@'localhost' IDENTIFIED BY '123';         -- 仅限本地登陆

# 3、删除用户
-- 删除用户语法
DROP USER '用户名'@'主机名';
-- 删除用户举例
DROP USER 'lisi'@'localhost';

# 4、修改用户密码
-- 修改用户密码语法：PASSWORD() 函数可以实现密码自动加密
UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';    # 第一种方式
SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');                # 第二种方式
-- 修改用户密码举例
UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');

用户权限管理 相关的操作如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 1、查询权限
-- 查询权限语法
SHOW GRANTS FOR '用户名'@'主机名';
-- 查询权限实例
SHOW GRANTS FOR 'lisi'@'localhost';

# 2、授予权限
-- 授予权限语法
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
-- 给张三用户授予所有权限，在任意数据库任意表上
GRANT SELECT ON db01.emp TO 'zhangsan'@'localhost';    -- 给 zhangsan 用户授予查询权限，仅限于 db01数据库中的 emp 表查询，其他操作均不可
GRANT ALL ON *.* TO 'zhangsan'@'localhost';            -- ALL 代表所有权限，第一个 * 代表所有数据库，第二个代表每个数据库下的所有表

# 3、撤销权限
-- 撤销权限语法
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
-- 撤销权限实例
REVOKE UPDATE ON db01.`account` FROM 'lisi'@'%';        -- 撤销 lisi 用户的修改权限

重点来喽： 如果我们忘记了 MySQL 密码怎么办？ 以下 骚操作 记好：

mysql 中忘记了 root 用户的密码？
1、cmd -- > net stop mysql 停止 MySQL 服务。注意：这条命令需要管理员身份运行 2、使用无验证方式启动 MySQL 服务： mysqld --skip-grant-tables 3、打开新的 cmd 窗口,直接输入 mysql 命令，敲回车。就可以登录成功 4、use mysql; 5、update user set password = password('你的新密码') where user = 'root'; 6、关闭两个窗口 7、打开任务管理器，手动结束 mysqld.exe 的进程 8、启动 MySQL 服务 9、使用新密码登录。

表的约束

约束概念及分类

约束概念： 对表中的数据进行限定，保证数据的 正确性 、有效性 和 完整性 。

约束的分类：
1、主键约束：primary key 2、非空约束：not null 3、唯一约束：unique 4、外键约束：foreign key

约束命令汇总

第一个： 非空约束 命令汇总如下：

1
2
3
4
5
6
7
8
9
10
11
# 非空约束：not null，加了这个约束的字段的值不能为 null，如下表的 name 字段

-- 创建表时添加约束
CREATE TABLE stu1(
    id INT,
    name VARCHAR(20) NOT NULL                    -- name 为非空，如果不给值会报错
);
-- 删除 name 的非空约束
ALTER TABLE stu1 MODIFY name VARCHAR(20);
-- 创建表后，然后添加非空约束
ALTER TABLE stu1 MODIFY name VARCHAR(20) NOT NULL;

第二个： 唯一约束 命令汇总如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 唯一约束：unique，值不能重复
# 注意事项：mysql 中，唯一约束限定的列的值可以有多个 null ，这个不算重复

-- 创建表时，添加唯一约束
CREATE TABLE stu2(
    id INT,
    phone_number VARCHAR(20) UNIQUE      -- 给 phone_number 字段添加了唯一约束，如果值重复就报错

);
-- 删除唯一约束
ALTER TABLE stu2 DROP INDEX phone_number;
-- 在创建表后，添加唯一约束
-- 这里要注意：如果 phone_number 字段有重复值，必须先删除某个重复值然后再执行次命令
ALTER TABLE stu2 MODIFY phone_number VARCHAR(20) UNIQUE;

第三个： 主键约束 命令汇总如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# 主键约束：primary key。
# 注意事项：主键就是表中记录的唯一标识，非空且唯一，一张表只能有一个字段为主键

-- 在创建表时，添加主键约束
create table stu3(
    id int primary key,                -- 给 id 添加主键约束
    name varchar(20)
);
-- 删除主键
alter table stu3 modify id int ;        -- 这是错误删除方式，虽然执行成功不报错，但是主键仍然在，并没有被删除
ALTER TABLE stu3 DROP PRIMARY KEY;      -- 正确删除主键的方式
-- 创建完表后，添加主键
ALTER TABLE stu3 MODIFY id INT PRIMARY KEY;

# 自动增长：如果某一列是数值类型的，使用 auto_increment 可以来完成值得自动增长
-- 在创建表时，添加主键约束，并且完成主键自增长
create table stu4(
    id int primary key auto_increment,        -- 给 id 添加主键约束，并自动增长
    name varchar(20)
);
-- 删除自动增长
ALTER TABLE stu4 MODIFY id INT;               -- 只是删除了自动增长，并不会删除主键
-- 添加自动增长
ALTER TABLE stu4 MODIFY id INT AUTO_INCREMENT;

第四个： 外键约束 命令汇总如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
# 外键约束：foreign key , 让表于表产生关系，从而保证数据的正确性。

-- 01-创建员工表
CREATE TABLE emp(                        # 创建员工表
id INT PRIMARY KEY AUTO_INCREMENT,       # 主键 id，自增
name VARCHAR(30),                        # 姓名
age INT,                                 # 年龄
dep_name VARCHAR(30),                    # 部门名称
dep_location VARCHAR(30)                 # 部门所在位置
);

-- 02-添加数据
INSERT INTO emp(NAME,age,dep_name,dep_location) VALUES ('张三',20,'研发部','广州');
INSERT INTO emp(NAME,age,dep_name,dep_location) VALUES ('李四',21,'研发部','广州');
INSERT INTO emp(NAME,age,dep_name,dep_location) VALUES ('王五',20,'研发部','广州');
INSERT INTO emp(NAME,age,dep_name,dep_location) VALUES ('老王',20,'销售部','深圳');
INSERT INTO emp(NAME,age,dep_name,dep_location) VALUES ('大王',22,'销售部','深圳');
INSERT INTO emp(NAME,age,dep_name,dep_location) VALUES ('小王',18,'销售部','深圳');

# 上述 emp 表存在数据冗余问题。举个例子：研发部 已经改名为 研究部，地点迁移到 杭州。那么如果研发部这个部门有 100 名员工，
# 那 emp 表中每个人的信息都要修改，岂不是要修改 100 次。那 1 万人呢？10 万人呢？修改 1 万次？10 万次？显然是不合理的。

-- 03-解决数据冗余方案：把 emp 表分成两张表，主表 department 和 从表 employee
-- 03-1 创建部门表【一方，主表】
CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(20),
dep_location VARCHAR(20)
);
-- 03-2 添加 2 个部门
INSERT INTO department VALUES (NULL,'研发部','广州'),(NULL,'销售部','深圳');
SELECT * FROM department;

-- 03-3 创建员工表【多方，从表】
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_id INT
);
-- 03-4 添加 6 个数据
INSERT INTO employee(NAME,age,dep_id) VALUES ('张三',20,1);
INSERT INTO employee(NAME,age,dep_id) VALUES ('李四',21,1);
INSERT INTO employee(NAME,age,dep_id) VALUES ('王五',20,1);
INSERT INTO employee(NAME,age,dep_id) VALUES ('老王',20,2);
INSERT INTO employee(NAME,age,dep_id) VALUES ('大王',22,2);
INSERT INTO employee(NAME,age,dep_id) VALUES ('小王',18,2);

# 这个时候就把 emp 表拆分成了 employee 表和 department 表。但是这两张表没有关联啊，
# 所以此时需要用外键 foreign key 把他们关联起来

DROP TABLE employee;              -- 删除员工表
DROP TABLE department;            -- 删除部门表

# 上述删除表的母的就是可以再次创建有外键的表。语法如下：
 create table 表名(
    ....
    外键列
    # 这里解释一下 外键名称，例如：A表和B表相关联，外键名为A-B , C表和D表相关联，外键名为C-D,所以外键名称主要用于区分 哪两张表相关联
    # 外键列名称 表示这张表当中哪个字段需要链接外键，主表名称(主表列名称) 代表外键链接到的是哪一个表中的哪一个字段
    constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
 );

-- 04-再次创建部门表
CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(20),
dep_location VARCHAR(20)
);
-- 添加 2 个部门
INSERT INTO department VALUES (NULL,'研发部','广州'),(NULL,'销售部','深圳');
SELECT * FROM department;

-- 05-再次创建员工表，这次的带了外键哦
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_id INT,                         -- 外键对应主表的主键
-- 创建外键约束 ，emp_depid_fk 是外键名字，dep_id 是外键，department(id) 是外键所指向的表,关联 id 字段
CONSTRAINT emp_depid_fk FOREIGN KEY (dep_id) REFERENCES department(id)
);

-- 06-删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;              # 删除外键语法
ALTER TABLE employee DROP FOREIGN KEY emp_depid_fk;    # 删除 employee 表与 department 表之间的外键 emp_depid_fk

-- 07-创建表之后，添加外键
# 添加外键语法
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
# 给 employee 表 dep_id 字段添加外键
ALTER TABLE employee ADD CONSTRAINT emp_depid_fk FOREIGN KEY (dep_id) REFERENCES department(id);

第五个： 外键约束_级联操作 命令汇总如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 由于 employee 表和 department 表有外键关联，所以当需要修改 department 表的 id 字段时会报错。

-- 解决修改 department 表 id 字段报错问题【麻烦版本，不建议使用】
UPDATE employee SET dep_id = NULL WHERE dep_id = 1;         # 外键可以设置为 NULL，但不可以不存在
UPDATE department SET id = 3 WHERE id = 1;
UPDATE employee SET dep_id = 3 WHERE dep_id = NULL;         # 现在就把 department 表修改了，而且 employee 表也修改了


# 有没有更简单的方法呢？只修改 department 表中 id 字段的值，然后 employee 表 dep_id 跟着变化。哎，那还真有，那就是 级联操作
# 级联操作需要在添加外键的时候来设置，操作如下：

-- 删除外键，如何判断是否删除  使用 SQLyog 软件中架构设计器，拖入两张相关联的表，没连线代表删除成功，连线代表没有删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_depid_fk;

-- 添加级联操作语法
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) 
REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE ;

-- 添加外键，设置级联更新【ON UPDATE CASCADE】  和设置级联删除【】
ALTER TABLE employee ADD CONSTRAINT emp_depid_fk FOREIGN KEY (dep_id) 
REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE ;

# 级联操作需要谨慎使用，以免误删数据。其次是进行级联操作影响效率和性能。

多表操作及关系

多表之间的关系：
1、一对一(了解)。 如：人和身份证【一个人只有一个身份证，一个身份证只能对应一个人】 2、一对多(多对一)。如：部门和员工【一个部门有多个员工，一个员工只能对应一个部门】 3、多对多。如：学生和课程【一个学生可以选择很多门课程，一个课程也可以被很多学生选择】

第一步： 一对多(多对一) 的实现关系，如下图：

第二步： 多对多 的实现关系，如下图：

第三步： 一对一(了解) 的实现关系，如下图：

三大范式

设计数据库时，需要遵循的一些规范，这些规范就叫范式。而要遵循后边的范式要求，必须先遵循前边的所有范式要求。

范式的概念：
设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。
范式的分类：
1、第一范式（1NF）：每一列都是不可分割的原子数据项 2、第二范式（2NF）：在 1NF 的基础上，非码属性必须完全依赖于码（在 1NF 基础上消除非主属性对主码的部分函数依赖） 3、第三范式（3NF）：在 2NF 的基础上，任何非主属性不依赖于其它非主属性（在 2NF 基础上消除传递依赖） 
几个概念：
1. 函数依赖：A-->B,如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖于A
    例如：学号-->姓名。  （学号，课程名称） --> 分数
2. 完全函数依赖：A-->B， 如果A是一个属性组，则B属性值得确定需要依赖于A属性组中所有的属性值。
    例如：（学号，课程名称） --> 分数
3. 部分函数依赖：A-->B， 如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值即可。
    例如：（学号，课程名称） -- > 姓名
4. 传递函数依赖：A-->B, B -- >C . 如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递函数依赖于A
    例如：学号-->系名，系名-->系主任
5. 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码
    例如：该表中码为：（学号，课程名称）
    * 主属性：码属性组中的所有属性
    * 非主属性：除过码属性组的属性



数据库的备份和还原

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 命令行操作方式

-- 备份数据库操作
-- 备份语法
mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
# 把 db01 数据库备份到 G 盘下的 db.sql
mysqldump -uroot -proot db01 > G:\\db.sql

-- 还原数据库操作
mysql -uroot -proot                                     # 1、登录数据库
create database db01;                                   # 2、创建数据库，数据库名任意，不一定是备份的数据库名
use db01;                                               # 3、使用数据库；
source G:\\db.sql;                                      # 4、执行文件，把备份在 G 盘的 sql 文件还原


# 图形界面操作方式
# 图形界面相对简单，只要看的懂文字即可，即可实现备份和还原。
# 推荐工具： SQLyog ，Navicat

SQL 高级

多表查询及分类

当我们进行多表查询时，返回结果是 笛卡尔积 。笛卡尔积存在很多无用的数据，需要消除他们，就需要用到下述三类查询。

多表查询的分类：
1、内连接查询 2、外连接查询 3、子查询

前期准备 ，就是建立需要进行查询的表，并了解一些概念，如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# 首先准备两张表，部门表和员工表
# 创建部门表
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),
    gender CHAR(1),         -- 性别
    salary DOUBLE,          -- 工资
    join_date DATE,         -- 入职日期
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外键，关联部门表(部门表的主键)
);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);

# 此时部门表 dept 有三条记录，员工表 emp 有五条记录
select * from emp,dept;     -- 最简单的多表查询，结果是 笛卡尔积【有两个集合A,B .取这两个集合的所有组成情况。】

内连接查询

隐式内连接： 使用 where 条件 消除无用数据，如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

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

-- 查询员工表中的姓名、性别，部门表的名称
SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.`dept_id` = dept.`id`;

-- 如果查询字段多，表名很长，条件很多，就是用以下格式
SELECT
    t1.name,                        -- 员工表的姓名
    t1.gender,                      -- 员工表的性别
    t2.name                         -- 部门表的名称
FROM 
    emp t1,                         -- 给 emp 表取别名为 t1
    dept t2                         -- 给 dept 表取别名为 t2
WHERE
    t1.`dept_id` = t2.`id`;         -- 查询条件

显式内连接： 使用显式内连接来消除无用数据，如下：

1
2
3
4
5
6
-- 显式内连接语法
select 字段列表 from 表名1 [inner] join 表名2 on 条件

-- 显示内连接
SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;      -- 显示内连接查询，INNER 可以省略
SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`;            -- INNER 省略了，查询结果同上
内连接查询注意事项：
1、从哪些表中查询数据 2、条件是什么 3、查询哪些字段

外连接查询

左外连接：左外连接的相关介绍及语法如下：

1
2
3
4
5
6
7
8
9
-- 左外连接语法：查询的是左表所有数据以及其交集部分。outer 可以省略
select 字段列表 from 表1 left [outer] join 表2 on 条件

-- 左外连接实例：查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
INSERT INTO emp VALUES (6,"Lisa",NULL,NULL,NULL,NULL);        -- 插入一条外键为空的记录
SELECT * FROM emp INNER JOIN dept ON emp.dept_id = dept.id;   -- 使用内连接查找，发现查不到 Lisa 的信息

-- 左外连接查询：可以查到 Lisa 记录
SELECT t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;

右外连接：右外连接的相关介绍及语法如下：

1
2
3
4
5
-- 右外连接语法：查询的是右表所有数据以及其交集部分。
select 字段列表 from 表1 right [outer] join 表2 on 条件

-- 右外连接实例：查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`;

TIPS： 左外连接和右外连接学一个就行了，都是一样的意思。

子查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
# 子查询概念：查询中嵌套查询，称嵌套查询为子查询。
-- 查询工资最高的员工信息，普通查询方式
-- 1 查询最高的工资是多少 9000
SELECT MAX(salary) FROM emp;
-- 2 查询员工信息，并且工资等于 9000 的
SELECT * FROM emp WHERE emp.`salary` = 9000;

-- 使用子查询，一条 sql 就完成这个操作
SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);



# 子查询三种不同情况

-- 1. 子查询的结果是单行单列的：子查询可以作为条件，使用运算符去判断。 运算符： > >= < <= =
-- 查询员工工资小于平均工资的人
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);

-- 2. 子查询的结果是多行单列的：子查询可以作为条件，使用运算符 in 来判断
-- 查询 '财务部' 和 '市场部' 所有的员工信息，普通查询方式
SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';
SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
-- 子查询
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');

-- 3. 子查询的结果是多行多列的：子查询可以作为一张虚拟表参与查询
-- 查询员工入职日期是 2011-11-11日 之后的员工信息和部门信息
-- 子查询
SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2WHERE t1.id = t2.dept_id;

-- 普通内连接查询方式
SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` >  '2011-11-11'

多表查询练习

第一步： 创建一个数据库，然后创建下列表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
-- 部门表
CREATE TABLE dept (
  id INT PRIMARY KEY PRIMARY KEY, -- 部门id
  dname VARCHAR(50), -- 部门名称
  loc VARCHAR(50) -- 部门所在地
);

-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');


-- 职务表，职务名称，职务描述
CREATE TABLE job (
  id INT PRIMARY KEY,
  jname VARCHAR(20),
  description VARCHAR(50)
);

-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');



-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工id
  ename VARCHAR(50), -- 员工姓名
  job_id INT, -- 职务id
  mgr INT , -- 上级领导
  joindate DATE, -- 入职日期
  salary DECIMAL(7,2), -- 工资
  bonus DECIMAL(7,2), -- 奖金
  dept_id INT, -- 所在部门编号
  CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
  CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);

-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);



-- 工资等级表
CREATE TABLE salarygrade (
  grade INT PRIMARY KEY,   -- 级别
  losalary INT,  -- 最低工资
  hisalary INT -- 最高工资
);

-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

第二步： 需求一和需求二分析查询，如下表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- 1、查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
/*
 * 需求分析：
 * 1.员工编号，员工姓名，工资，需要查询 emp 表；  职务名称，职务描述 需要查询 job 表
 * 2.查询条件 emp.job_id = job.id
 */
SELECT 
    t1.`id`,          -- 员工编号
    t1.`ename`,       -- 员工姓名
    t1.`salary`,      -- 员工工资
    t2.`jname`,       -- 职务名称
    t2.`description`  -- 职务描述
FROM 
    emp t1, job t2
WHERE 
    t1.`job_id` = t2.`id`;



-- 2、查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
/* 
 * 需求分析：
 * 1. 员工编号，员工姓名，工资 emp  职务名称，职务描述 job  部门名称，部门位置 dept
 * 2. 条件： emp.job_id = job.id and emp.dept_id = dept.id
 */
SELECT 
    t1.`id`,               -- 员工编号
    t1.`ename`,            -- 员工姓名
    t1.`salary`,           -- 员工工资
    t2.`jname`,            -- 职务名称
    t2.`description`,      -- 职务描述
    t3.`dname`,            -- 部门名称
    t3.`loc`               -- 部门位置
FROM 
    emp t1, job t2,dept t3
WHERE 
    t1.`job_id` = t2.`id` AND t1.`dept_id` = t3.`id`;

第三步： 需求三和需求四分析查询，如下表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- 3、查询员工姓名，工资，工资等级
/*
 * 需求分析：
 * 1、员工姓名，工资 emp  工资等级 salarygrade
 * 2、条件 emp.salary >= salarygrade.losalary and emp.salary <= salarygrade.hisalary
 *    emp.salary BETWEEN salarygrade.losalary and salarygrade.hisalary
*/
SELECT 
    t1.ename ,
    t1.`salary`,
    t2.*
FROM emp t1, salarygrade t2
WHERE
    t1.`salary` BETWEEN t2.`losalary` AND t2.`hisalary`;



-- 4、查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
/*
 * 需求分析：
 * 1、员工姓名，工资 emp ， 职务名称，职务描述 job 部门名称，部门位置，dept  工资等级 salarygrade
 * 2、条件： emp.job_id = job.id and emp.dept_id = dept.id and emp.salary BETWEEN salarygrade.losalary and salarygrade.hisalary
 */
SELECT 
    t1.`ename`,
    t1.`salary`,
    t2.`jname`,
    t2.`description`,
    t3.`dname`,
    t3.`loc`,
    t4.`grade`
FROM 
    emp t1,job t2,dept t3,salarygrade t4
WHERE 
    t1.`job_id` = t2.`id` 
    AND t1.`dept_id` = t3.`id`
    AND t1.`salary` BETWEEN t4.`losalary` AND t4.`hisalary`;

第四步： 需求五和需求六分析查询，如下表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
-- 5、查询出部门编号、部门名称、部门位置、部门人数
/*
 * 需求分析：
 * 1、部门编号、部门名称、部门位置 dept 表。 部门人数 emp表
 * 2、使用分组查询。按照emp.dept_id完成分组，查询count(id)
 * 3、使用子查询将第2步的查询结果和dept表进行关联查询
 * 
*/
SELECT 
    t1.`id`,t1.`dname`,t1.`loc` , t2.total
FROM 
    dept t1,
    (SELECT
        dept_id,COUNT(id) total
    FROM 
        emp
    GROUP BY dept_id) t2
WHERE t1.`id` = t2.dept_id;



-- 6、查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
/*
 * 需求分析：
 * 1、姓名 emp， 直接上级的姓名 emp
 *     emp表的id 和 mgr 是自关联
 * 2、条件 emp.id = emp.mgr
 * 3、查询左表的所有数据，和 交集数据
 *    使用左外连接查询
 * 
 */

/* 查不出没有领导的员工
select
    t1.ename,
    t1.mgr,
    t2.`id`,
    t2.ename
FROM emp t1, emp t2
WHERE t1.mgr = t2.`id`;
*/

-- 能查出没有领导的员工
SELECT 
    t1.ename,
    t1.mgr,
    t2.`id`,
    t2.`ename`
FROM emp t1
LEFT JOIN emp t2
ON t1.`mgr` = t2.`id`;

事务

事务概念及相关操作

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

事务相关的操作：
1、开启事务： start transaction; 2、回滚：rollback; 3、提交：commit;

代码举例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- 创建 account 表
CREATE TABLE account (
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(10),
    balance DOUBLE
);

-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);

SELECT * FROM account;
UPDATE account SET balance = 1000;

-- 张三给李四转账 500 元

-- 0. 开启事务
START TRANSACTION;

-- 1. 张三账户 -500
UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';

-- 2. 李四账户 +500
-- 出错了...
UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';

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

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

事务的两种提交方式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 事务提交的两种方式：

自动提交： MySQL 默认是自动提交的。一条 DML (增删改)语句会自动提交一次事务。

手动提交： 需要先开启事务，再提交。Oracle 数据库默认是手动提交事务


 
# 查看事务的默认提交方式：
SELECT @@autocommit;       -- 1 代表自动提交  0 代表手动提交

# 修改默认提交方式：
set @@autocommit = 0;
commit;                    -- 改为手动提交后，就需要 commit 才能把修改的数据生效

事务的四大特征

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

事务的隔离级别

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

存在问题：
1、脏读：一个事务，读取到另一个事务中没有提交的数据。 2、不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。 3、幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。
隔离级别：
1、read uncommitted：读未提交。产生的问题：脏读、不可重复读、幻读 2、read committed：读已提交 （Oracle 默认）。产生的问题：不可重复读、幻读 3、repeatable read：可重复读（MySQL 默认）。产生的问题：幻读 4、serializable：串行化。可以解决所有的问题

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

1
2
3
4
5
6
7
# 数据库查询隔离级别：
select @@tx_isolation;

# 数据库设置隔离级别：
set global transaction isolation level  级别字符串;             -- 设置隔离级别语法
set global transaction isolation level  read committed;         -- 设置为 读已提交
select @@tx_isolation;                                          -- 查询仍然是 repeatable read，需要关闭软件，再次打开即可

隔离级别演示如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
# CMD 同时打开两个 MySQL 窗口，同时进入相同数据库和相同表，并且开启事务

-- 第一个窗口
set global transaction isolation level read uncommitted;      -- 设置需要测试的隔离级别
start transaction;
-- 转账操作
update account set balance = balance - 500 where id = 1;      -- 给 1 号转账，且不提交事务
update account set balance = balance + 500 where id = 2;

-- 第二个窗口
start transaction;                -- 开启事务
select * from account;            -- 查找 account 表，可能出现的问题：脏读，虚读，幻读
`

JDBC 相关知识

JDBC 基本概念

JDBC 概念： JDBC 是 Java DataBase Connectivity 的缩写形式，翻译成中文为 Java 数据库连接 ，即使用 Java 语言操作数据库。JDBC 本质： 其实是官方（sun 公司）定义的一套操作所有关系型数据库的规则，即 接口 。各个数据库厂商去实现这套接口，提供 数据库驱动 jar 包 。我们可以使用这套接口（JDBC）编程，真正执行的代码是驱动 jar 包中的实现类。

快速入门

JDBC 使用步骤：
1、导入驱动 jar 包： mysql-connector-java-5.1.37-bin.jar     1.1 复制 mysql-connector-java-5.1.37-bin.jar 到项目的 libs 目录下     1.2 右键 --> Add As Library 2、注册驱动 3、获取数据库连接对象 Connection 4、定义 sql 5、获取执行 sql 语句的对象 Statement 6、执行 sql，接受返回结果 7、处理结果 8、释放资源

快速入门小案例，代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
/**
 * JDBC 快速入门
 */
public static void main(String[] args) throws Exception {

    // 1. 导入驱动 jar 包，需要连接哪个数据库，就导入哪个数据库的 jar 包

    // 2.注册驱动
    Class.forName("com.mysql.jdbc.Driver");

    // 3.获取数据库连接对象
//    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db01", "root", "root");
    Connection conn = DriverManager.getConnection("jdbc:mysql:///db01", "root", "root");

    // 4.定义sql语句
//    String sql = "update account set balance = 2000 where id = 1";
    String sql = "update account set balance = 2000";

    // 5.获取执行sql的对象 Statement
    Statement stmt = conn.createStatement();

    // 6.执行sql
    int count = stmt.executeUpdate(sql);

    // 7.处理结果
    System.out.println(count);

    // 8.释放资源
    stmt.close();
    conn.close();

}

JDBC 详解

JDBC 详解各个对象：
1、DriverManager：驱动管理对象 2、Connection：数据库连接对象 3、Statement：执行 sql 的对象 4、ResultSet：结果集对象，封装查询结果 5、PreparedStatement：执行 sql 的对象

第一个： DriverManager：驱动管理对象 ，它有如下两个功能：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# DriverManager 功能一：注册驱动，告诉程序该使用哪一个数据库驱动 jar

static void registerDriver(Driver driver) :注册与给定的驱动程序 DriverManager 。 
写代码使用：  Class.forName("com.mysql.jdbc.Driver");

# 通过查看源码发现：在 com.mysql.jdbc.Driver 类中存在静态代码块：
static {
    try {
        java.sql.DriverManager.registerDriver(new Driver());
    } catch (SQLException E) {
        throw new RuntimeException("Can't register driver!");
    }
}
# 注意事项：mysql5 之后的 驱动jar包 可以省略注册驱动的步骤。
1
2
3
4
5
6
7
8
9
10
11
# DriverManager 功能二：获取数据库连接，方法：static Connection getConnection(String url, String user, String password) 
# 方法参数介绍：

url：指定连接的路径
    语法：jdbc:mysql://ip地址(域名):端口号/数据库名称
    例子：jdbc:mysql://localhost:3306/db01
    细节：如果连接的是本机 mysql 服务器，并且 mysql 服务默认端口是 3306，则 url 可以简写为：jdbc:mysql:/// 数据库名称

user：用户名

password：密码

第二个： Connection：数据库连接对象 ，它有如下两个功能：

1
2
3
4
5
6
7
8
9
10
# Connection 的两个功能：

# 1、获取执行 sql 的对象：有下面两个方法
    Statement createStatement()
    PreparedStatement prepareStatement(String sql)

# 2、管理事务：
    开启事务：setAutoCommit(boolean autoCommit) ：调用该方法设置参数为false，即开启事务
    提交事务：commit() 
    回滚事务：rollback()

第三个： Statement：执行 sql 的对象 ，介绍如下：

1
2
3
4
5
6
7
8
9
10
# 1、执行 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)语句

# 2、练习
    1、account表 添加一条记录
    2、account表 修改记录
    3、account表 删除一条记录

Statement：执行 sql 的对象 的练习，代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
public static void main(String[] args) {
    Statement stmt = null;
    Connection conn = null;
    try {
        // 1. 注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 2. 定义 sql，需要执行哪个操作就选用那一条 sql
        String sql = "insert into account values(null,'王五',3000)";    // 添加操作
        //String sql = "update account set balance = 1500 where id = 3";  // 修改操作
        //String sql = "delete from account where id = 3";                // 删除操作
        //String sql  = "create table student (id int , name varchar(20))"; // 创建 student 表，但是这种操作不常用
        // 3.获取Connection对象
        conn = DriverManager.getConnection("jdbc:mysql:///db01", "root", "root");
        // 4.获取执行sql的对象 Statement
        stmt = conn.createStatement();
        // 5.执行sql
        int count = stmt.executeUpdate(sql); // 影响的行数
        // 6.处理结果
        System.out.println(count);
        if(count > 0){
            System.out.println("添加/修改/删除成功！");
        }else{
            System.out.println("添加/修改/删除失败！");
        }

    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    }finally {
        // stmt.close();
        // 7. 释放资源
        // 避免空指针异常
        if(stmt != null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

第四个： ResultSet：结果集对象，封装查询结果 ，介绍如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
# ResultSet：结果集对象，封装查询结果

boolean next(): 游标向下移动一行，判断当前行是否是最后一行末尾(是否有数据)，如果是，则返回 false，如果不是则返回 true
getXxx(参数):获取数据。Xxx：代表数据类型   如： int getInt() , String getString()
参数：
    1. int：代表列的编号,从 1 开始   如： getString(1)
    2. String：代表列名称。 如： getDouble("balance")


使用步骤：
    1. 游标向下移动一行
    2. 判断是否有数据
    3. 获取数据

ResultSet 结果集对象的一个练习 （只查询单条记录） ，代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
public static void main(String[] args) {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
        // 1. 注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 2.获取连接对象
        conn = DriverManager.getConnection("jdbc:mysql:///db01", "root", "root");
        // 3.定义sql
        String sql  = "select * from account";
        // 4.获取执行sql对象
        stmt = conn.createStatement();
        //5.执行sql
        rs = stmt.executeQuery(sql);
        // 6.处理结果
        // 6.1 让游标向下移动一行
        rs.next();
        // 6.2 获取数据
        int id = rs.getInt(1);
        String name = rs.getString("name");
        double balance = rs.getDouble(3);

        System.out.println(id + "---" + name + "---" + balance);

    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        // 7.释放资源

        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(stmt != null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

ResultSet 结果集对象的一个练习 （查询所有记录） ，代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
public static void main(String[] args) {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
        //1. 注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取连接对象
        conn = DriverManager.getConnection("jdbc:mysql:///db01", "root", "root");
        //3.定义sql
        String sql  = "select * from account";
        //4.获取执行sql对象
        stmt = conn.createStatement();
        //5.执行sql
        rs = stmt.executeQuery(sql);
        //6.处理结果
        //循环判断游标是否是最后一行末尾。
        while(rs.next()){

            //获取数据
            //6.2 获取数据
            int id = rs.getInt(1);
            String name = rs.getString("name");
            double balance = rs.getDouble(3);

            System.out.println(id + "---" + name + "---" + balance);
        }

    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        //7.释放资源

        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(stmt != null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

ResultSet 结果集对象的一个 小案例 ，把数据库表中的数据封装到 JavaBean 中，代码如下：

Emp.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
/**
 * 封装 Emp 表数据的 JavaBean
 */
public class Emp {
    private int id;
    private String ename;
    private int job_id;
    private int mgr;
    private Date joindate;
    private double salary;
    private double bonus;
    private int dept_id;


    public int getId() {
        return id;
    }

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

    public String getEname() {
        return ename;
    }

    public void setEname(String ename) {
        this.ename = ename;
    }

    public int getJob_id() {
        return job_id;
    }

    public void setJob_id(int job_id) {
        this.job_id = job_id;
    }

    public int getMgr() {
        return mgr;
    }

    public void setMgr(int mgr) {
        this.mgr = mgr;
    }

    public Date getJoindate() {
        return joindate;
    }

    public void setJoindate(Date joindate) {
        this.joindate = joindate;
    }

    public double getSalary() {
        return salary;
    }

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


    public int getDept_id() {
        return dept_id;
    }

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


    public double getBonus() {
        return bonus;
    }

    public void setBonus(double bonus) {
        this.bonus = bonus;
    }


    @Override
    public String toString() {
        return "Emp{" +
                "id=" + id +
                ", ename='" + ename + '\'' +
                ", job_id=" + job_id +
                ", mgr=" + mgr +
                ", joindate=" + joindate +
                ", salary=" + salary +
                ", bonus=" + bonus +
                ", dept_id=" + dept_id +
                '}';
    }
}

上面定义好了一个实体类，与数据库相映射，现在就来编写具体的测试类，代码如下；

JDBC_Test.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
/**
 * 定义一个方法，查询 emp 表的数据将其封装为对象，然后装载集合，返回。
 */
public class JDBC_Test {

    public static void main(String[] args) {
        List<Emp> list = new JDBCDemo8().findAll2();
        System.out.println(list);
        System.out.println(list.size());
    }
    /**
     * 查询所有 emp 对象
     * @return
     */
    public List<Emp> findAll(){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<Emp> list = null;
        try {
            //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取连接
            conn = DriverManager.getConnection("jdbc:mysql:///db01", "root", "root");
            //3.定义sql
            String sql = "select * from emp";
            //4.获取执行 sql 的对象
            stmt = conn.createStatement();
            //5.执行sql
            rs = stmt.executeQuery(sql);
            //6.遍历结果集，封装对象，装载集合
            Emp emp = null;
            list = new ArrayList<Emp>();
            while(rs.next()){
                //获取数据
                int id = rs.getInt("id");
                String ename = rs.getString("ename");
                int job_id = rs.getInt("job_id");
                int mgr = rs.getInt("mgr");
                Date joindate = rs.getDate("joindate");
                double salary = rs.getDouble("salary");
                double bonus = rs.getDouble("bonus");
                int dept_id = rs.getInt("dept_id");
                // 创建 emp 对象,并赋值
                emp = new Emp();
                emp.setId(id);
                emp.setEname(ename);
                emp.setJob_id(job_id);
                emp.setMgr(mgr);
                emp.setJoindate(joindate);
                emp.setSalary(salary);
                emp.setBonus(bonus);
                emp.setDept_id(dept_id);

                //装载集合
                list.add(emp);
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(rs != null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }

}

JDBC 工具类

JDBC 工具类 小案例，代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
/**
 * JDBC 工具类
 */
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 res  = classLoader.getResource("jdbc.properties");
            String path = res.getPath();
           // System.out.println(path);///D:/IdeaProjects/itcast/out/production/day04_jdbc/jdbc.properties
            //2. 加载文件
           // pro.load(new FileReader("D:\\IdeaProjects\\itcast\\day04_jdbc\\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);
    }

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

        if( conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


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

        if( stmt != null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if( conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

编写配置文件，放在 src 目录 下，文件内容如下：

jdbc.properties
1
2
3
4
url=jdbc:mysql:///db01
user=root
password=root
driver=com.mysql.jdbc.Driver

编写 JDBC 工具类的演示类，代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

/**
 * 演示 JDBC 工具类
 * @return
 */
public List<Emp> findAll2(){
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    List<Emp> list = null;
    try {
        conn = JDBCUtils.getConnection();
        //3.定义sql
        String sql = "select * from emp";
        //4.获取执行sql的对象
        stmt = conn.createStatement();
        //5.执行sql
        rs = stmt.executeQuery(sql);
        //6.遍历结果集，封装对象，装载集合
        Emp emp = null;
        list = new ArrayList<Emp>();
        while(rs.next()){
            //获取数据
            int id = rs.getInt("id");
            String ename = rs.getString("ename");
            int job_id = rs.getInt("job_id");
            int mgr = rs.getInt("mgr");
            Date joindate = rs.getDate("joindate");
            double salary = rs.getDouble("salary");
            double bonus = rs.getDouble("bonus");
            int dept_id = rs.getInt("dept_id");
            // 创建emp对象,并赋值
            emp = new Emp();
            emp.setId(id);
            emp.setEname(ename);
            emp.setJob_id(job_id);
            emp.setMgr(mgr);
            emp.setJoindate(joindate);
            emp.setSalary(salary);
            emp.setBonus(bonus);
            emp.setDept_id(dept_id);

            //装载集合
            list.add(emp);
        }

    } catch (SQLException e) {
        e.printStackTrace();
    }finally {
        JDBCUtils.close(rs,stmt,conn);
    }
    return list;
}

JDBC 练习

JDBC 的一个登陆操作小案例，代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107

/**
 * 练习：
 *    1. 通过键盘录入用户名和密码
 *    2. 判断用户是否登录成功
 */
public class JDBCLogin_Test {

    public static void main(String[] args) {
        //1.键盘录入，接受用户名和密码
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名：");
        String username = sc.nextLine();
        System.out.println("请输入密码：");
        String password = sc.nextLine();
        //2.调用方法
        boolean flag = new JDBCLogin_Test().login(username, password);
        //3.判断结果，输出不同语句
        if(flag){
            //登录成功
            System.out.println("登录成功！");
        }else{
            System.out.println("用户名或密码错误！");
        }


    }



    /**
     * 登录方法
     */
    public boolean login(String username ,String password){
        if(username == null || password == null){
            return false;
        }
        //连接数据库判断是否登录成功
        Connection conn = null;
        Statement stmt =  null;
        ResultSet rs = null;
        //1.获取连接
        try {
            conn =  JDBCUtils.getConnection();
            //2.定义sql
            String sql = "select * from user where username = '"+username+"' and password = '"+password+"' ";
            System.out.println(sql);
            //3.获取执行sql的对象
            stmt = conn.createStatement();
            //4.执行查询
            rs = stmt.executeQuery(sql);
            //5.判断
            return rs.next();//如果有下一行，则返回true

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(rs,stmt,conn);
        }
        return false;
    }

}

``

上述操作会被 `SQL 注入` ，所以改造登陆方法，代码如下：

​```java
/**
 * 登录方法,使用PreparedStatement实现
 */
public boolean login2(String username ,String password){
    if(username == null || password == null){
        return false;
    }
    //连接数据库判断是否登录成功
    Connection conn = null;
    PreparedStatement pstmt =  null;
    ResultSet rs = null;
    //1.获取连接
    try {
        conn =  JDBCUtils.getConnection();
        //2.定义sql
        String sql = "select * from user where username = ? and password = ?";
        //3.获取执行sql的对象
        pstmt = conn.prepareStatement(sql);
        //给?赋值
        pstmt.setString(1,username);
        pstmt.setString(2,password);
        //4.执行查询,不需要传递sql
        rs = pstmt.executeQuery();
        //5.判断
       /* if(rs.next()){//如果有下一行，则返回true
            return true;
        }else{
            return false;
        }*/
        return rs.next();//如果有下一行，则返回true

    } catch (SQLException e) {
        e.printStackTrace();
    }finally {
        JDBCUtils.close(rs,pstmt,conn);
    }
    return false;
}

第五个： PreparedStatement：执行 sql 的对象 ，介绍如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# PreparedStatement：执行 sql 的对象

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

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

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

JDBC 事务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
/**
 * JDBC 事务操作
 */
public class JDBC_Transaction {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt1 = null;
        PreparedStatement pstmt2 = null;

        try {
            //1.获取连接
            conn = JDBCUtils.getConnection();
            //开启事务
            conn.setAutoCommit(false);

            //2.定义sql
            //2.1 张三 - 500
            String sql1 = "update account set balance = balance - ? where id = ?";
            //2.2 李四 + 500
            String sql2 = "update account set balance = balance + ? where id = ?";
            //3.获取执行sql对象
            pstmt1 = conn.prepareStatement(sql1);
            pstmt2 = conn.prepareStatement(sql2);
            //4. 设置参数
            pstmt1.setDouble(1,500);
            pstmt1.setInt(2,1);

            pstmt2.setDouble(1,500);
            pstmt2.setInt(2,2);
            //5.执行sql
            pstmt1.executeUpdate();
            // 手动制造异常
            int i = 3/0;

            pstmt2.executeUpdate();
            //提交事务
            conn.commit();
        } catch (Exception e) {
            //事务回滚
            try {
                if(conn != null) {
                    conn.rollback();
                }
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            JDBCUtils.close(pstmt1,conn);
            JDBCUtils.close(pstmt2,null);
        }
    }

}

数据库连接池

当我们操作数据库时，我们每次都创建一个连接，然后操作完成之后释放这个连接。如此的频繁操作导致效率很低，因而连接池出现了，它解决了 资源浪费、效率低下 的问题。

连接池概念和优点

连接池概念： 连接池其实就是一个容器(集合)，存放数据库连接的容器。当系统初始化好后，容器被创建，容器中会申请一些连接对象，当用户来访问数据库时，从容器中获取连接对象，用户访问完之后，会将连接对象归还给容器。草图如下：

连接池的好处：
1、节约资源 2、用户访问高效

连接池的实现

Java 提供了一个标准接口来实现连接池： javax.sql 包下的 DataSource 接口。我们不会去实现它，而是由数据库厂商去实现。

接口中的方法：
1、获取连接方法：getConnection() 2、归还连接方法：Connection.close()。 3、注意：如果连接对象 Connection 是从连接池中获取的，那么调用 Connection.close()方法，则不会关闭连接，而是归还连接到连接池。

C3P0 连接池

C3P0 是一个开源的 JDBC 连接池 ，它实现了数据源与 JNDI 绑定，支持 JDBC3 规范和实现了 JDBC2 的标准扩展说明的 Connection 和 Statement 池的 DataSources 对象 。即将用于连接数据库的连接整合在一起形成一个随取随用的数据库连接池（Connection pool）。

C3P0 连接池使用步骤：
1、导入 jar 包【三个】 c3p0-0.9.5.2.jar ， mchange-commons-java-0.2.12.jar ，mysql-connector-java-5.1.37-bin.jar 2、定义配置文件。名称： c3p0.properties 或者 c3p0-config.xml 。路径：直接将文件放在 src 目录下即可。【系统自动加载】 3、创建核心对象：数据库连接池对象 ComboPooledDataSource 4、获取连接： getConnection

C3P0 代码演示如下：

c3p0-config.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
<c3p0-config>
  <!-- 使用默认的配置读取连接池对象 -->
  <default-config>
  	<!--  连接参数 -->
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/db01</property>
    <property name="user">root</property>
    <property name="password">root</property>
    
    <!-- 连接池参数 -->
    <!--初始化申请的连接数量-->
    <property name="initialPoolSize">5</property>
    <!--最大的连接数量-->
    <property name="maxPoolSize">10</property>
    <!--超时时间-->
    <property name="checkoutTimeout">3000</property>
  </default-config>

  <named-config name="otherc3p0"> 
    <!--  连接参数 -->
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/db01</property>
    <property name="user">root</property>
    <property name="password">root</property>
    
    <!-- 连接池参数 -->
    <property name="initialPoolSize">5</property>
    <property name="maxPoolSize">8</property>
    <property name="checkoutTimeout">1000</property>
  </named-config>
</c3p0-config>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
/**
 * C3P0 的演示
 */
public class C3P0_Demo {
    public static void main(String[] args) throws SQLException {
        // 1.创建数据库连接池对象
        DataSource ds  = new ComboPooledDataSource();

        // 2. 获取连接对象
        Connection conn = ds.getConnection();

        // 3. 打印，查看一下 conn 到底是什么
        System.out.println(conn);
    }
}

验证一下 c3p0-config.xml 中的各种参数，代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

/**
 * c3p0 参数验证演示
 */
public class C3P0_Test {

    public static void main(String[] args) throws SQLException {
        // 1. 获取DataSource，使用默认配置
        DataSource ds  = new ComboPooledDataSource();

        // 2.获取连接：验证默认配置的最大连接数量： <property name="maxPoolSize">10</property>
        for (int i = 1; i <= 11 ; i++) {
            Connection conn = ds.getConnection();
            System.out.println(i+":"+conn);

            // 如果没有下列 if 语句，那么超过 3 秒就会报错<property name="checkoutTimeout">3000</property>
            // 因为连接池最大 10 个，而要产生 11 个对象，所以过了 超时检测时间 就要报错
            if(i == 5){
                conn.close(); // 归还连接到连接池中
            }
        }

        //testNamedConfig();

    }


    public static void testNamedConfig() throws SQLException {
        // 1.1 获取DataSource，使用指定名称配置
        DataSource ds  = new ComboPooledDataSource("otherc3p0");
        //2.获取连接：验证指定配置 otherc3p0 下的最大连接数量：8 个
        for (int i = 1; i <= 10 ; i++) {
            Connection conn = ds.getConnection();
            System.out.println(i+":"+conn);
        }
    }

}

Druid 连接池

Druid 为阿里巴巴的数据源（数据库连接池），集合了 c3p0、dbcp、proxool 等连接池的优点，还加入了日志监控，有效的监控 DB 池连接和 SQL 的执行情况。Druid 的 DataSource 类 为：com.alibaba.druid.pool.DruidDataSource 。

Druid 连接池使用步骤：
1、导入 jar 包： druid-1.0.9.jar 2、定义配置文件：是 properties 形式的，可以叫任意名称，可以放在任意目录下【需要手动加载】 3、加载配置文件：Properties 4、获取数据库连接池对象：通过工厂来来获取 DruidDataSourceFactory 5、获取连接：getConnection

Druid 代码演示如下：

druid.properties
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
driverClassName=com.mysql.jdbc.Driver

url=jdbc:mysql:///db01

username=root

password=root

# 初始化连接数量
initialSize=5

# 最大连接数
maxActive=10

# 最大等待时间
maxWait=3000
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/**
 * Druid演示
 */
public class Druid_Demo {
    public static void main(String[] args) throws Exception {
        //1.导入jar包
        //2.定义配置文件
        //3.加载配置文件
        Properties pro = new Properties();
        InputStream is = DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties");
        pro.load(is);

        //4.获取连接池对象
        DataSource ds = DruidDataSourceFactory.createDataSource(pro);

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

    }
}

封装一个工具类，方便以后的各种操作，工具类如下：

Druid 定义 JDBCUtils 工具类：
1、定义一个类 JDBCUtils 2、提供静态代码块加载配置文件，初始化连接池对象 3、提供相关的方法     1、获取连接方法：通过数据库连接池获取连接     2、释放资源     3、获取连接池的方法
JDBCUtils.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
/**
 * Druid 连接池的工具类
 */
public class JDBCUtils {

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

    static{
        try {
            //1.加载配置文件
            Properties pro = new Properties();
            pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"));

            //2.获取DataSource
            ds = DruidDataSourceFactory.createDataSource(pro);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

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

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

        if(conn != null){
            try {
                conn.close(); // 归还连接
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }*/

       close(null,stmt,conn);
    }


    public static void close(ResultSet rs , Statement stmt, Connection conn){


        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


        if(stmt != null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(conn != null){
            try {
                conn.close(); // 归还连接
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 获取连接池方法
     */

    public static DataSource getDataSource(){
        return  ds;
    }

}

工具类类写好了，是否好用呢？来测试一下吧。代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
/**
 * 使用新的工具类
 */
public class Druid_Test {

    public static void main(String[] args) {
        /*
         * 完成添加操作：给 account 表添加一条记录
         */
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            // 1.获取连接
            conn = JDBCUtils.getConnection();
            // 2.定义 sql
            String sql = "insert into account values(null,?,?)";
            // 3.获取 pstmt 对象
            pstmt = conn.prepareStatement(sql);
            // 4.给 ？赋值
            pstmt.setString(1,"王五");
            pstmt.setDouble(2,3000);
            // 5.执行 sql
            int count = pstmt.executeUpdate();
            System.out.println(count);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            // 6.释放资源
            JDBCUtils.close(pstmt,conn);
        }
    }

}

到此为止，连接池的内容就结束了！

JDBC Template

JDBC Template 介绍

连接池虽然提高了效率，工具类也简化了一些操作，但是仍然觉得很麻烦。有木有更简单一点的操作呢？只关注 sql 本身，而不去管其他的东西？有，它就是 JDBC Template 。它是 Spring 框架对 JDBC 的简单封装，提供了一个 JDBCTemplate 对象简化 JDBC 的开发。

JDBC Template 使用步骤：
1、导入 jar 包，总共 7 个 2、创建 JdbcTemplate 对象。依赖于数据源 DataSource 。JdbcTemplate template = new JdbcTemplate(ds); 3、调用 JdbcTemplate 的方法来完成 CRUD 的操作     1、update(): 执行 DML 语句。增、删、改语句     2、queryForMap(): 查询结果将结果集封装为 map 集合，将列名作为 key，将值作为 value 将这条记录封装为一个 map 集合。注意：这个方法查询的结果集长度只能是 1     3、queryForList(): 查询结果将结果集封装为 list 集合。注意：将每一条记录封装为一个 Map 集合，再将 Map 集合装载到 List 集合中     4、query(): 查询结果，将结果封装为 JavaBean 对象。query 的参数：RowMapper， 一般我们使用 BeanPropertyRowMapper 实现类。可以完成数据到 JavaBean 的自动封装     5、queryForObject：查询结果，将结果封装为对象。一般用于聚合函数的查询

JDBC Template 入门程序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
/**
 * JdbcTemplate 入门程序
 */
public class JdbcTemplate_Demo {

    public static void main(String[] args) {
        // 1.导入 jar 包 （总共 7 个）
        // 2.创建 JDBCTemplate 对象
        JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

        // 3.调用方法
        String sql = "update account set balance = 5000 where id = ?";
        int count = template.update(sql, 3);
        System.out.println(count);
    }
}

JDBC Template 练习

练习需求：
1、修改 1 号数据的 salary 为 10000 2、添加一条记录 3、删除刚才添加的记录 4、查询 id 为 1 的记录，将其封装为 Map 集合 5、查询所有记录，将其封装为 List 5、查询所有记录，将其封装为 Emp 对象的 List 集合 5、查询总记录数

定义一个 实体类 ，与数据库中的 emp 表 相对应，如下：

Emp.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
import java.util.Date;

public class Emp {
    private Integer id;
    private String ename;
    private Integer job_id;
    private Integer mgr;
    private Date joindate;
    private Double salary;
    private Double bonus;
    private Integer dept_id;


    public Integer getId() {
        return id;
    }

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

    public String getEname() {
        return ename;
    }

    public void setEname(String ename) {
        this.ename = ename;
    }

    public Integer getJob_id() {
        return job_id;
    }

    public void setJob_id(Integer job_id) {
        this.job_id = job_id;
    }

    public Integer getMgr() {
        return mgr;
    }

    public void setMgr(Integer mgr) {
        this.mgr = mgr;
    }

    public Date getJoindate() {
        return joindate;
    }

    public void setJoindate(Date joindate) {
        this.joindate = joindate;
    }

    public Double getSalary() {
        return salary;
    }

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

    public Double getBonus() {
        return bonus;
    }

    public void setBonus(Double bonus) {
        this.bonus = bonus;
    }

    public Integer getDept_id() {
        return dept_id;
    }

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

    @Override
    public String toString() {
        return "Emp{" +
                "id=" + id +
                ", ename='" + ename + '\'' +
                ", job_id=" + job_id +
                ", mgr=" + mgr +
                ", joindate=" + joindate +
                ", salary=" + salary +
                ", bonus=" + bonus +
                ", dept_id=" + dept_id +
                '}';
    }
}

编写测试类，代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
public class JdbcTemplate_Practice {

    //Junit单元测试，可以让方法独立执行


    //1. 获取JDBCTemplate对象
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
    /**
     * 1. 修改1号数据的 salary 为 10000
     */
    @Test
    public void test1(){

        //2. 定义sql
        String sql = "update emp set salary = 10000 where id = 1001";
        //3. 执行sql
        int count = template.update(sql);
        System.out.println(count);
    }

    /**
     * 2. 添加一条记录
     */
    @Test
    public void test2(){
        String sql = "insert into emp(id,ename,dept_id) values(?,?,?)";
        int count = template.update(sql, 1015, "郭靖", 10);
        System.out.println(count);

    }

    /**
     * 3.删除刚才添加的记录
     */
    @Test
    public void test3(){
        String sql = "delete from emp where id = ?";
        int count = template.update(sql, 1015);
        System.out.println(count);
    }

    /**
     * 4.查询id为1001的记录，将其封装为Map集合
     * 注意：这个方法查询的结果集长度只能是1
     */
    @Test
    public void test4(){
        String sql = "select * from emp where id = ? or id = ?";
        Map<String, Object> map = template.queryForMap(sql, 1001,1002);
        System.out.println(map);
        //{id=1001, ename=孙悟空, job_id=4, mgr=1004, joindate=2000-12-17, salary=10000.00, bonus=null, dept_id=20}

    }

    /**
     * 5. 查询所有记录，将其封装为List
     */
    @Test
    public void test5(){
        String sql = "select * from emp";
        List<Map<String, Object>> list = template.queryForList(sql);

        for (Map<String, Object> stringObjectMap : list) {
            System.out.println(stringObjectMap);
        }
    }

    /**
     * 6. 查询所有记录，将其封装为Emp对象的List集合
     */

    @Test
    public void test6(){
        String sql = "select * from emp";
        List<Emp> list = template.query(sql, new RowMapper<Emp>() {

            @Override
            public Emp mapRow(ResultSet rs, int i) throws SQLException {
                Emp emp = new Emp();
                int id = rs.getInt("id");
                String ename = rs.getString("ename");
                int job_id = rs.getInt("job_id");
                int mgr = rs.getInt("mgr");
                Date joindate = rs.getDate("joindate");
                double salary = rs.getDouble("salary");
                double bonus = rs.getDouble("bonus");
                int dept_id = rs.getInt("dept_id");

                emp.setId(id);
                emp.setEname(ename);
                emp.setJob_id(job_id);
                emp.setMgr(mgr);
                emp.setJoindate(joindate);
                emp.setSalary(salary);
                emp.setBonus(bonus);
                emp.setDept_id(dept_id);

                return emp;
            }
        });


        for (Emp emp : list) {
            System.out.println(emp);
        }
    }

    /**
     * 6. 查询所有记录，将其封装为Emp对象的List集合
     */

    @Test
    public void test6_2(){
        String sql = "select * from emp";
        List<Emp> list = template.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));
        for (Emp emp : list) {
            System.out.println(emp);
        }
    }

    /**
     * 7. 查询总记录数
     */

    @Test
    public void test7(){
        String sql = "select count(id) from emp";
        Long total = template.queryForObject(sql, Long.class);
        System.out.println(total);
    }

}

数据库知识到此就结束了。以后还会写一些数据库性能优化的文章，继续加油吧！！！

问题解决

1、【使用可视化工具连接远程服务器数据库】client does not support authentication 解法

1
2
3
4

use mysql;
alter user 'root'@'%' identified with mysql_native_password by '********';
flush privileges;

解决 Can’t connect to MySQL server (10060) 问题：如果连接不上远程服务器的 MySQL，可以去阿里云或者其他云服务器官网添加安全组规则，使服务器开发3306端口。或者是查看防火墙的状态，如果防火墙开启了，那么需要关闭。

    / 

📚 本站推荐文章
  👉 从 0 开始搭建 Hexo 博客
  👉 计算机网络入门教程
  👉 数据结构入门
  👉 算法入门
  👉 IDEA 入门教程

喜欢这篇文章？打赏一下作者吧

Git 使用教程
歌曲白狐故事

可在评论区留言哦

一言句子获取中...

分类

我的歌单

标签云

AJAX CMD命令 Docker English Git IDEA教程 IO 流 JSON Java Java基础 Java集合 Jenkins Layui 博客 LeetCode Linux MySQL Oracle数据库 PL/SQL Redis SSM Spring Spring Boot Spring MVC Vue hello world html icarus主题 mybatis svn windows教程 函数式编程 前端 博客搭建 反射注解 常用 API 常用算法 故事 数据库 数据结构 算法 网络编程 英语 计算机网络 面试

订阅更新

立即订阅跳转人机认证后，点击完成按钮！

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×