原创

    MySQL 数据库入门教程(一篇就够!)

    第一章:数据库的基本概念

    一、数据库初识

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

    二、数据库的特点

    数据库的特点:
    1、能够持久化存储数据,其实数据库就是一个文件系统 2、数据库可以存储和管理数据 3、使用了统一的方式操作数据库 ==> SQL

    三、常见数据库

    常见的数据库:
    1、Oracle:收费的大型数据库,Oracle 公司的产品 2、MySQL:开源免费的数据库,小型的数据库,已经被 Oracle 收购了。MySQL6.x 版本也开始收费 3、SQLServer:MicroSoft 公司收费的中型的数据库。C#、.net 等语言常使用 4、DB2:IBM 公司的数据库产品,收费的。常应用在银行系统中 5、SQLite:嵌入式的小型数据库,应用在手机端,如:Android 6、SyBase:已经淡出历史舞台,提供了一个非常专业的数据建模的工具 PowerDesigner

    小提示: 最常用的两个数据库是:MySQL、Oracle

    四、数据库排行榜

    数据库排行榜网址: https://db-engines.com/en/ranking

    第二章:MySQL 数据库

    一、MySQL 安装

    以下将 以图的方式 简述 MySQL 安装过程,如下:

    进入命令行 ,测试 MySQL 是否安装成功:

    二、MySQL 卸载

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

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

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

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

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

    TIPS: 成功卸载之后,就可以再次安装 MySQL 了。

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

    三、MySQL 服务启动和关闭

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

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

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

    四、MySQL 登陆和退出

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

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

    五、MySQL 目录结构

    1、MySQL 的安装目录

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

    2、MySQL 的数据目录

    那么客户端如何去操作数据库呢? 原理见下图:

    小提示: 客户端通过 ip 找到计算机,通过端口找到数据库服务器软件,最后通过这个软件即可对数据库及数据表进行操作了。

    第三章:SQL 初识

    一、什么是 SQL

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

    二、SQL 特点与注释

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

    三、SQL-小白基础命令

    /*
     * 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 ,最多可放 128 个字符
        stu_age int, 
        stu_birthday date,                   # date 类型,只包含年月日,yyyy-MM-dd ;datetime 类型,包含年月日时分秒 yyyy-MM-dd HH:mm:ss ; timestamp 类型,包含年月日时分秒,yyyy-MM-dd HH:mm:ss ,如果没有给此类型赋值,那么它的值就是系统当前的时间,而不是 null
        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 数据表,则删除数据表,不存在,不会报错
    drop table stu_tbl1,stu_tbl2,stu_tbl3;   # 同时删除 stu_tbl1,stu_tbl2,stu_tbl3 三张数据表
    
    -- 修改数据表
    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";                # 把 stu_tbl 表中 name 字段的值修改为 Jack,即每一条数据的 name 值都是 Jack。【不推荐使用】
    UPDATE stu_tbl SET name = "Jack" WHERE id = 1;   # 把 id=1 的记录中的 name 修改为 Jack
    

    第四章:SQL 的四种分类

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

    一、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-小白基础命令 处)相关的操作,如下图:

    TIPS: 操作数据库的其他命令就不再一个一个地演示了,详情看上方的 SQL-小白基础命令 自行练习。

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

    然后我们再来 创建表和查询表 ,操作如下图:

    操作数据表的其他命令 就不再一个一个地演示了,详情看上方的 SQL-小白基础命令 自行练习。

    二、DML-数据操作语言

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

    /*
     * 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-数据查询语言

    1、DQL 的概念

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

    -- 基本语法介绍
    select       # 后面接需要查询的字段            
    from         # 后面跟表名              
    where        # 后面跟条件            
    group by     # 后面跟分组字段               
    having       # 其后接分组之后的条件               
    order by     # 其后接排序字段               
    limit        # 分页限定
    

    2、数据查询前期准备

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

    -- 创建 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 表 ,并插入数据和查询,如下图:

    3、基础查询操作

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

    -- 基础查询操作
    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;
    

    4、条件查询操作

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

    -- 条件查询操作,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 "%德%";                 # 查找姓名中包含 ”德“ 的人
    

    5、排序查询操作

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

    # 排序查询操作语法: 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 成绩 升序排名,如果数学成绩一样,则按照英语成绩 降序排名
    

    6、聚合函数操作

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

    # 聚合函数:将一列数据作为一个整体,进行纵向的计算。
    
    -- 1. count:计算个数
    SELECT COUNT(id) FROM student;           # 以 id 这一列计算个数,结果是 8,因为有 8 条数据
    SELECT COUNT(*) FROM student;            # 以不为空的列计算个数【即表的记录条数】,结果是 8,* 号的写法是不推荐的
    SELECT COUNT(english) FROM student;      # 以 english 这一列计算个数,结果是 7,因为聚合函数的计算,会排除 null 值
    SELECT COUNT(IFNULL(english, 0)) FROM student;   # 使用 IFNULL 函数解决 null 值,结果是 8
    
    -- 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 平均成绩。如果存在 null 值,这条数值不纳入计算
    SELECT AVG(name) FROM student;           # 计算 name 平均成绩,因为 name 非数值,所以结果是 0
    

    7、分组查询操作

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

    # 分组查询语法: group by 分组字段;
    # 注意事项:分组之后查询的字段是分组字段或者聚合函数,而不能是有关单条记录的字段,如下:
    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 可以进行聚合函数的判断。
    

    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-数据控制语言

    1、DCL 的概念

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

    2、用户的 CRUD 操作

    -- 1、查询用户
    USE mysql;             # 切换到 MySQL 数据库
    SELECT * FROM USER;    # 查询 user 表。通配符 % 表示可以在任意主机使用用户登录数据库,localhost 代表只能在本地登陆
    
    -- 2、创建用户
    CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';    # 创建用户语法
    CREATE USER 'zhangsan'@'%' IDENTIFIED BY '1234';               # 创建用户举例【% 代表可在本地和远程登陆】
    CREATE USER 'lisi'@'localhost' IDENTIFIED BY '1234';           # 创建用户举例【localhost 表示仅限本地登陆】
    
    -- 3、删除用户
    DROP USER 'username'@'hostname';     # 删除用户语法
    DROP USER 'lisi'@'localhost';        # 删除用户举例
    
    -- 4、修改用户密码。注意:PASSWORD() 函数可以实现密码自动加密
    UPDATE USER SET PASSWORD=PASSWORD('new_passowrd') WHERE USER='username';  # 修改用户密码语法的第一种方式
    SET PASSWORD FOR 'username'@'hostname' = PASSWORD('new_password');        # 修改用户密码语法的第二种方式
    
    UPDATE USER SET PASSWORD=PASSWORD('abc') WHERE USER='lisi';               # 修改用户密码举例
    SET PASSWORD FOR 'root'@'localhost' = PASSWORD('1234');                   # 修改用户密码举例
    flush privileges;    # 新设置密码或更改密码后需用 flush privileges 刷新 MySQL 的系统权限相关表,否则会出现拒绝访问。还有一种方法就是重新启动 MySQL 服务器,来使新设置生效。
    

    3、用户权限管理操作

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

    -- 1、查询权限
    -- USAGE 表示该用户只有登录权限,其他的权限均无
    SHOW GRANTS FOR 'username'@'hostname';     # 查询权限语法
    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 用户的修改权限
    

    4、忘记 MySQL 密码咋办

    重点来喽: 如果我们忘记了 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

    二、约束的命令汇总

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

    # 非空约束: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;
    

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

    # 唯一约束: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;
    

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

    # 主键约束:primary key。
    # 注意事项:1、主键就是表中记录的唯一标识,非空且唯一。2、一张表只能有一个字段为主键
    
    -- 在创建表时,添加主键约束
    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;
    

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

    # 外键约束: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】
    
    -- 创建部门表【一方,主表】
    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;
    
    -- 创建员工表【多方,从表】
    CREATE TABLE employee(
        id INT PRIMARY KEY AUTO_INCREMENT,
        NAME VARCHAR(20),
        age INT,
        dep_id INT
    );
    
    -- 添加 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 表。
    
    -- 这个时候就把 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 主表名称(主表列名称)
        # 上述添加外键约束的代码还可以有省略形式,只不过外键名称是自动生成的。省略形式如下:
        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)
    );
    
    -- 删除外键约束,语法如下
    ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
    -- 删除 employee 表与 department 表之间的外键 emp_depid_fk
    ALTER TABLE employee DROP FOREIGN KEY emp_depid_fk;
    
    -- 在创建表之后添加外键约束。语法如下:
    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);
    

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

    # 由于 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 IS 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】  和设置级联删除【ON DELETE 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、多对多。如:学生和课程【一个学生可以选择很多门课程,一个课程也可以被很多学生选择】

    第一步: 一对多(多对一) 的关系实现,如下图:

    第二步: 多对多 的关系实现,如下图:

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

    四、SQL 的范式介绍

    # 范式的概念
    设计关系型数据库时,需要遵循不同的规范要求,从而设计出合理的关系型数据库,而这些规范就叫【范式】。
    
    # 范式的规则
    1、各种范式呈递次规范,越高的范式数据库冗余越小。2、后边的范式要求,必须先遵循前边的所有范式要求。
    
    # 关系型数据库有六种范式
    第一范式(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、码:如果在一张表中,一个属性或属性组,被其他所有属性所完全依赖,则称这个属性(属性组)为该表的码
    例如:该表中【码】为 ==>(学号,课程名称)  # 即通过【学号和课程名称】能够确定表中的任意一个属性,这就称为【码】
    # 【主属性:】码属性组中的所有属性。【非主属性:】除主属性以外的属性
    

    五、数据库的备份和还原

    # 一、命令行操作方式
    -- 备份数据库操作。备份语法如下:
    mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
    mysqldump -uroot -proot db01 > G:\\db.sql   # 把 db01 数据库备份到 G 盘下的 db.sql
    
    -- 还原数据库操作
    mysql -uroot -proot                         # 1、登录数据库
    create database db01;                       # 2、创建数据库,数据库名任意,不一定是备份的数据库名
    use db01;                                   # 3、使用数据库;
    source G:\\db.sql;                          # 4、执行文件,把备份在 G 盘的 sql 文件还原
    
    # 二、图形界面操作方式
    # 图形界面相对简单,只要看的懂文字即可,即可实现备份和还原。推荐的图形工具: SQLyog ,Navicat
    

    第六章:多表查询及分类

    当我们进行多表查询时,返回结果是 笛卡尔积 。那么什么是【笛卡尔积】呢?举个栗子:A 表有 3 条记录,B 表有 5 条记录,那么笛卡尔积就是 15 ,即三和五的乘积。 因此,笛卡尔积会存在很多无用的数据,需要消除他们,就需要用到下述三类查询【拉开 多表查询 的序幕】。

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

    前期准备 ,就是建立需要进行查询的表【部门表和员工表】,并了解一些概念,如下:

    -- 创建部门表
    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 条件 消除无用数据,如下:

    -- 查询所有员工信息和所对应的部门信息
    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`;           -- 查询条件
    

    显式内连接: 使用显式内连接来消除无用数据。相关关键字: INNERJOINON 。代码如下:

    -- 显式内连接语法
    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、查询哪些字段

    二、外连接查询

    左外连接: 【左外连接】的相关介绍及语法如下:

    -- 左外连接语法:查询的是左表所有数据以及其交集部分。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`;
    

    右外连接: 【右外连接】的相关介绍及语法如下:

    -- 右外连接语法:查询的是右表所有数据以及其交集部分。
    select 字段列表 from 表1 right [outer] join 表2 on 条件
    
    -- 右外连接实例:查询所有员工信息,如果员工有部门,则查询部门名称,没有部门,则不显示部门名称
    SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id`=t2.`id`;
    

    TIPS: 【左外连接】和【右外连接】学一个就行了,都是一样的意思。

    三、子查询

    子查询概念: 查询中嵌套查询,称嵌套查询为 子查询 。SQL 演示如下:

    -- 查询工资最高的员工信息,普通查询方式
    SELECT MAX(salary) FROM emp;                  -- 先查询出最高的工资是多少 => 9000
    SELECT * FROM emp WHERE emp.`salary` = 9000;  -- 再查询工资等于 9000 的员工信息,即工资最高的员工
    
    -- 使用子查询,一条 sql 就可以完成上述操作
    SELECT * FROM emp WHERE emp.`salary`=(SELECT MAX(salary) FROM emp);
    

    通过上述的代码练习,相信已经对 子查询 有了一定的了解。那么接下来就来说一说 子查询的三种不同的情况。接着往下看吧!问题及 SQL 如下:

    -- 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'
    

    四、多表查询练习

    第一步: 先自行创建一个数据库,然后创建下列各个数据表:

    -- 部门表
    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、查询所有员工信息。查询员工编号,员工姓名,工资,职务名称,职务描述
    /**
     * 需求分析:
     * 1、【员工编号,员工姓名,工资】需要查询 emp 表
     * 2、【职务名称,职务描述】需要查询 job 表
     * 3、【查询条件:】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 表
     * 2、【职务名称,职务描述】 ==> 查询 job 表
     * 3、【部门名称,部门位置】 ==> 查询 dept 表
     * 4、【查询条件:】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`;
    

    第三步: 对需求三和需求四进行分析,然后查询。如下表:

    -- 3、查询员工姓名,工资,工资等级
    /**
     * 需求分析:
     * 1、【员工姓名,工资】==> 查询 emp 表
     * 2、【工资等级】 ==> 查询 salarygrade 表
     * 3、【查询条件:】emp.salary >= salarygrade.losalary AND emp.salary <= salarygrade.hisalary
     */
    
    SELECT 
        t1.ename,
        t1.`salary`,
        t2.grade,
    FROM emp t1,salarygrade t2
    WHERE
        t1.`salary` BETWEEN t2.`losalary` AND t2.`hisalary`;
    
    -- 4、查询员工姓名,工资,职务名称,职务描述,部门名称,部门位置,工资等级
    /**
     * 需求分析:
     * 1、【员工姓名,工资】==> 查询 emp 表
     * 2、【职务名称,职务描述】==> 查询 job 表
     * 3、【部门名称,部门位置】==> 查询 dept 表
     * 4、【工资等级】==> 查询 salarygrade 表
     * 5、【查询条件:】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`;
    

    第四步: 对需求五和需求六进行分析,然后查询。如下表:

    -- 5、查询出部门编号、部门名称、部门位置、部门人数
    /*
     * 需求分析:
     * 1、【部门编号、部门名称、部门位置】==> 查询 dept 表
     * 2、【部门人数】==> 查询 emp 表
     * 3、使用分组查询。按照 emp.dept_id 完成分组,查询 count(id)
     * 4、使用子查询将第二步的查询结果和 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 表
     * 2、【直接上级的姓名】==> 查询 emp 表
     * 3、【注意事项:】emp 表的 id 和 mgr 是自关联
     * 4、【查询条件】emp.id=emp.mgr 。然后使用左外连接查询左表的所有数据和交集数据即可
     */
    
    -- 查不出没有领导的员工
    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`;
    

    小提示: 多表查询 相关知识到此结束,接下来请阅读 事务相关 的知识。

    第七章:MySQL 中的事务

    一、事务概念及相关操作

    事务的概念: 如果一个包含 多个步骤 的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败。 举个栗子:如果张三给李四转账 500 元,张三少了 500 元,这时,微信突然挂了,李四没有收到钱。那么,如果开启了事务,张三的 500 元就会被退回,提示转账不成功。如果未开启事务,那么张三就损失了 500 元。

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

    前期准备: 新建一张账户表。代码如下:

    -- 创建 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;
    

    张三开始给李四转账【开启两个 MySQL 窗口】。两种情况代码如下:

    -- 张三给李四转账 500 元【成功的情况】
    START TRANSACTION;    -- 1、开启事务
    UPDATE account SET balance = balance - 500 WHERE NAME='zhangsan';  -- 2、张三账户 - 500
    UPDATE account SET balance = balance + 500 WHERE NAME='lisi';      -- 3、李四账户 + 500
    COMMIT;               -- 4、发现执行没有问题,提交事务
    
    
    -- 张三给李四转账 500 元【失败的情况】
    START TRANSACTION;    -- 1、开启事务
    UPDATE account SET balance = balance - 500 WHERE NAME='zhangsan';  -- 2、张三账户 - 500
    手动制造 bug ...
    UPDATE account SET balance = balance + 500 WHERE NAME='lisi';      -- 3、这里会报错
    ROLLBACK;             -- 4、发现出问题了,回滚事务
    

    二、事务的两种提交方式

    在 MySQL 中,事务具有两种提交方式:自动提交手动提交自动提交 是 MySQL 数据库默认的提交方式,表示在执行一条 DML (增删改)语句时会自动提交一次事务。手动提交 需要先开启事务,再提交。Oracle 数据库 默认是手动提交事务。

    # 查看事务的默认提交方式。1 ==> 代表自动提交; 0 ==> 代表手动提交
    SELECT @@autocommit;
    
    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:串行化。可以解决所有的问题

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

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

    上述了解了 隔离级别的概念 ,接下来就来对 隔离级别 进行具体的演示。代码如下:

    # 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;   -- 更新 2 号账户余额,且不提交事务
    # 注意:代码写到这,在第一个窗口能够查询到转账后的数据。但是未提交。接下来打开第二个窗口
    
    -- 第二个窗口
    start transaction;                -- 开启事务
    select * from account;            -- 查找 account 表,可能出现的问题:脏读,虚读,幻读
    
    # 注意:上述就是一个隔离级别的演示操作【样例,接下来自己操作】。如果要更换其他的隔离级别,那就自行操作了
    

    第八章:JDBC 相关知识

    一、JDBC 基本概念

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

    二、JDBC 快速入门

    上述已经了解完了 JDBC 的概念, 那么接下来就来介绍 JDBC 的使用步骤。 如下:

    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、释放资源

    通过上面的了解,我们已经知道了 JDBC 使用步骤 ,那么我们就用代码操作一下吧!代码如下:

    /**
     * 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 快速入门 中,我们编写了一点代码。那么接下来就对代码中的各个对象进行详解。如下:

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

    1、DriverManager 对象

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

    /**
     * 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!");
        }
    }
    
    // 注意事项:mysql-5.0 之后的驱动 jar 包可以省略注册驱动这一步骤
    
    # 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:用户密码
    

    2、Connection 对象

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

    # Connection 对象的两个功能:
    
    # 一、获取执行 sql 的对象:有下面两个方法
        Statement createStatement()
        PreparedStatement prepareStatement(String sql)
    
    # 二、管理事务:
        开启事务:setAutoCommit(boolean autoCommit) :调用该方法设置参数为【false】,即开启事务
        提交事务:commit()
        回滚事务:rollback()
    

    3、Statement 对象

    第三个: 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】语句
    
    # 二、Statement 对象的练习题目
        1、account表 添加一条记录
        2、account表 修改记录
        3、account表 删除一条记录
    

    上述对 Statement 对象 进行了介绍以及给出了练习题,那么接下来我们就来实操一下吧。代码如下:

    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();
                }
            }
        }
    }
    

    4、ResultSet 对象

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

    # ResultSet:结果集对象,封装查询结果
    
    # next() 方法
    boolean next(): 游标向下移动一行,判断当前行是否是最后一行末尾(是否有数据)
    如果是最后一行末尾,则返回【false】表示没有数据。如果不是最后一行末尾,则返回【true】
    
    # getXxx(参数) 方法
    getXxx(参数):获取数据。Xxx:代表数据类型。例如:int getInt();String getString()
    参数含义:【int】:代表列的编号,从 1 开始。如:getString(1)。【String】:代表列名称。如:getString("name")
    
    # 使用步骤
    1、游标向下移动一行
    2、判断是否有数据
    3、获取数据
    

    ResultSet 结果集对象的一个练习 【只查询单条记录】。 代码如下:

    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"); // 获取数据表中当前行列名为 name 的数据
            double balance = rs.getDouble(3);   // 获取数据表中当前行第三列
            System.out.println(id + "---" + name + "---" + balanc
        } 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 结果集对象的一个练习 【查询所有记录】。 代码如下:

    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()){
                // 获取数据
                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 类,用于封装表数据,和数据库中的表数据产生对应关系。 代码如下:

    /**
     * 封装 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 +
                    '}';
        }
    }
    

    上面已经定义好了 Emp 实体类 与数据库相映射,那么现在就来编写具体的测试类。代码如下:

    /**
     * 定义一个方法,查询 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;
        }
    }
    

    5、PreparedState... 对象

    第五个: PreparedStatement:执行 sql 的对象 ,介绍如下:

    # 一、PreparedStatement:执行 sql 的对象。这个对象的作用如下:
    # 在后面的 JDBC 登录案例中会用上这个对象
    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:参数使用 ? 作为占位符
    
    # 二、PreparedStatement 对象使用步骤:
    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) 来获取 PrepareStatement 对象
    6、【给 ?赋值】【赋值的方法:】setXxx(参数1, 参数2)
       【参数1:】?的位置编号从 1 开始
       【参数2:】?的值
    7、执行 sql,接受返回结果,不需要传递 sql 语句
    8、处理结果
    9、释放资源
    
    # 注意:后期都会使用 PreparedStatement 来完成增删改查的所有操作
    1、可以防止 SQL 注入
    2、效率更高
    

    四、JDBC 工具类

    通过上述的学习,我们发现有很多代码是重复的。因此,我们将抽取出一个 JDBC 工具类 来简化我们的代码。具体如何做呢?第一步: 编写 jdbc.properties 配置文件,放在 src 目录 下。 配置文件内容如下:

    url=jdbc:mysql:///db01
    user=root
    password=root
    driver=com.mysql.jdbc.Driver
    

    第二步: 编写 JDBC 工具类。 代码如下:

    /**
     * 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);
                
               // 2、加载文件【注释掉的内容使用的绝对路径加载】
               // pro.load(new FileReader("C:\\User\\guoshizhan\\Desktop\\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 rs
         * @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();
                }
            }
        }
    }
    

    第三步: 我们使用 JDBC 工具类 进行演示。具体代码如下:

    /**
     * 演示 JDBC 工具类
     * @return
     * 第一步和第二步就是写 main 方法调用 findAll2() 方法即可。所以这里就不写了
     */
    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、判断用户是否登录成功
     */
    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 注入 ,所以需要改造登陆方法。代码如下:

    /**
     * 登录方法,使用 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、判断
            return rs.next(); // 如果有下一行,则返回 true
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(rs, pstmt, conn);
        }
        return false;
    }
    

    六、JDBC 事务

    在前面我们已经学习过了 事务 相关知识,所以在这里介绍一下 如何在 JDBC 中进行事务相关的操作。 在上面已经学习了 Connection 对象 的功能,其第二个功能就是用于操作事务的。具体代码如下:

    /**
     * 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、管理事务:使用 Connection 对象来管理事务
    2、开启事务:setAutoCommit(boolean autoCommit) 方法。在执行 sql 之前开启事务
    3、提交事务:commit() 方法。当所有 sql 都执行完后提交事务
    4、回滚事务:rollback() 方法。在 catch 中回滚事务
    

    第九章:数据库连接池

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


    一、连接池概念和优点

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

    使用连接池的好处:
    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 包【三个】
    mysql-connector-java-5.1.37-bin.jar;c3p0-0.9.5.2.jar;mchange-commons-java-0.2.12.jar
    # 2、定义配置文件:
    名称: c3p0.properties 或者 c3p0-config.xml。路径:直接将文件放在 src 目录下即可。【系统自动加载】
    # 3、创建核心对象:
    创建数据库连接池对象 ==> ComboPooledDataSource
    # 4、获取连接:
    通过 getConnection() 方法获取到连接对象
    

    第一个步骤自行完成。第二步: 编写 c3p0-config.xml 配置文件。 代码如下:

    <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>
    
      <!-- 随便给定一个名字【otherc3p0】,在获取 DataSource 的时候作为参数传入,从而获取其中的配置 -->
      <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>
    
    /**
     * C3P0 的演示
     */
    public class C3P0_Test {
        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 配置文件中,有一个名为 otherc3p0 的配置,它是在获取连接的时候作为参数传入,从而获取到对应的配置。 演示代码如下:

    /**
     * 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 语句,那么超过三秒就会报错 <property name="checkoutTimeout">3000</property>
                // 因为连接池最大 10 个,而要产生 11 个对象,所以过了超时检测时间,因此报错
                if (i == 5) {
                    conn.close(); // 归还连接到连接池中
                }
            }
            //testNamedConfig();
        }
    
        public static void testNamedConfig() throws SQLException {
            // 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 是阿里巴巴的数据源(数据库连接池)而且是目前最好的数据库连接池。 它集合了 c3p0dbcpproxool 等连接池的优点,还加入了日志监控,有效的监控 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.properties 配置文件。 代码如下:

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

    配置文件写好了,现在来测试一下 Druid 数据库连接池能否正常使用。 代码如下:

    /**
     * Druid 数据库连接池演示
     */
    public class Druid_Test {
        public static void main(String[] args) throws Exception {
            // 1、加载配置文件
            Properties pro = new Properties();
            InputStream is = DruidTest.class.getClassLoader().getResourceAsStream("druid.properties");
            pro.load(is);
            // 2、获取连接池对象
            DataSource ds = DruidDataSourceFactory.createDataSource(pro);
            // 3、获取连接
            Connection conn = ds.getConnection();
            System.out.println(conn);
        }
    }
    

    我们现在来封装一个工具类,方便以后的各种操作。 定义工具类步骤如下:

    Druid 定义 JDBCUtils 工具类:
    1、定义一个类 JDBCUtils 2、提供静态代码块加载配置文件,初始化连接池对象 3、提供相关的方法     1、获取连接方法:通过数据库连接池获取连接     2、释放资源     3、获取连接池的方法

    通过上述步骤,我们就来定义一个JDBCUtils 工具类。 代码如下:

    /**
     * Druid 连接池的工具类
     */
    public class JDBCUtils {
        // 定义成员变量 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) {
           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;
        }
    }
    

    JDBCUtils 工具类 写好了,是否好用呢?来测试一下吧。代码如下:

    /**
     * 使用新的工具类
     */
    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);
            }
        }
    }
    

    TIPS: 到此为止,连接池的内容就结束了!

    第十章: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 的操作
    
    # JdbcTemplate 中的方法
    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 入门程序

    /**
     * JdbcTemplate 入门程序
     */
    public class JdbcTemplate_Test {
        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 6、查询所有记录,将其封装为 Emp 对象的 List 集合 7、查询总记录数

    定义一个 Emp 实体类 ,与数据库中的 emp 表 相对应。 代码如下:

    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 +
                    '}';
        }
    }
    

    定义好实体类之后,我们使用 Junit 来编写测试类。 代码如下:

    // Junit 单元测试,可以让方法独立执行
    public class JdbcTemplate_Practice {
        // 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);
        }
    }
    

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

    MySQL
    • 文章作者:GuoShiZhan
    • 创建时间:2021-08-02 20:58:50
    • 更新时间:2021-10-08 15:18:40
    • 版权声明:本文为博主原创文章,未经博主允许不得转载!
    请 在 评 论 区 留 言 哦 ~~~
    1024