MySQL

1. 数据库的分类

关系型数据库（SQL）

• 通过表和表之间，行和列之间的关系进行数据的存储。

非关系型数据库（NoSQL）

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

2. SQL语句的分类

3. 安装

Windows安装

Linux步骤大致相同，命令有所不同而已。

1. 解压

2. 把mysql下的bin文件夹添加环境变量中。

3. 在mysql文件夹下新建my.ini文件

   [mysqld]
   
   port=3306
   basedir=C:\Program Files\MySQL\MySQL Server 5.7\
   datadir=C:\ProgramData\MySQL\MySQL Server 5.7\Data\
   skip-grant-tables
   

4. 进入bin目录下，管理员cmd运行

   mysqld -install
   mysqld --initialize-insecure --user=mysql
   
   mysql -u root -p    # 进入管理界面 空密码回车
   

5. 更改root密码

   update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';
   
   -- 8.0+
   alter user 'root'@'localhost' identified by '123456';
   

   flush privileges    # 刷新权限
   

6. 注释my.ini中的skip-grant-tables，最前面加#

7. 重启mysql

   net stop mysql
   net start mysql
   

Docker安装

docker pull mysql
docker run -it -d --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql

MYSQL_ROOT_PASSWORD为密码。

4. 数据库操作

MySQL默认端口号：3306.

默认选择的选项：

基字符集：utf8

数据库排序规则：utf8_general_cli

所有的SQL语句用分号结尾。

MySQL不区分大小写。

如果表名或者字段名带特殊字符或者关键字之类，需要用`包裹。

4.1. 基本命令

连接数据库：mysql -uroot -p123456

创建数据库：create database [if not exists] 数据库名;

删除数据库：drop database [if exists] 数据库名;

查看数据库：show databases;

切换数据库：use 数据库名;

退出连接：exit;

单行注释：--

多行注释：/**/

4.2. 操作表

查看表：show tables;

查看表结构：describe 表名;

4.3. 创建数据表

create table [if not exists] `表名`(
   '字段名1' 列类型 [属性][索引][注释],
   '字段名2' 列类型 [属性][索引][注释],
  ...
   '字段名n' 列类型 [属性][索引][注释]
)[表类型][表字符集][注释];

4.4. 数据类型

• 数值类型

• 字符串类型

• 日期和时间型数值类型

• NULL值

不要用NULL进行算术运算 , 结果仍为NULL。

4.5. 数据字段属性

UnSigned

• 无符号的
• 声明该数据列不允许负数

ZEROFILL

• 0填充的
• 不足位数的用0来填充 , 如int(3),5则为005

Auto_InCrement

• 自动增长的 , 每添加一条数据 , 自动在上一个记录数上加 1(默认)

• 通常用于设置主键 , 且为整数类型

• 可定义起始值和步长

  • 当前表设置步长(AUTO_INCREMENT=100) : 只影响当前表

  • SET @@auto_increment_increment=5 ; 影响所有使用自增的表(全局)

NULL 和 NOT NULL

• 默认为NULL , 即没有插入该列的数值
• 如果设置为NOT NULL , 则该列必须有值

DEFAULT

• 默认的
• 用于设置默认值
• 例如,性别字段,默认为"男" , 否则为 "女" ; 若无指定该列的值 , 则默认值为"男"的值

扩展

每一个表都必须存在以下五个字段。

id    主键
`version`    乐观锁
is_delete    伪删除
gmt_create        创建时间
gmt_update    修改时间

-- 目标 : 创建一个school数据库
-- 创建学生表(列,字段)
-- 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datetime),家庭住址,email
-- 创建表之前 , 一定要先选择数据库
USE DATABASE school;
-- COMMENT  注释 ，英文逗号隔开语句，最后一句不用逗号
CREATE TABLE
IF
    NOT EXISTS `student` (
        `id` INT ( 4 ) NOT NULL AUTO_INCREMENT COMMENT '学号',
        `name` VARCHAR ( 30 ) NOT NULL DEFAULT '匿名' COMMENT '姓名',
        `pwd` VARCHAR ( 20 ) NOT NULL DEFAULT '123456' COMMENT '密码',
        `gender` VARCHAR ( 2 ) NOT NULL DEFAULT '男' COMMENT '性别',
        `birthday` datetime DEFAULT NULL COMMENT '生日',
        `address` VARCHAR ( 100 ) DEFAULT NULL COMMENT '地址',
        `email` VARCHAR ( 50 ) DEFAULT NULL COMMENT '邮箱',
        PRIMARY KEY ( `id` ) 
    ) ENGINE = INNODB DEFAULT CHARSET = utf8 -- 查看->创建数据库的定义SQL语句

SHOW CREATE DATABASE school;-- 查看->创建数据库的定义SQL语句
SHOW CREATE TABLE student;-- 查看->创建数据表的定义SQL语句示表结构
DESC student;-- 显示表结构
-- 设置严格检查模式(不能容错了)SET sql_mode='STRICT_TRANS_TABLES';

4.6. 数据表的类型

CREATE TABLE 表名(
   -- 省略一些代码
   -- Mysql注释
   -- 1. # 单行注释
   -- 2. /*...*/ 多行注释
)ENGINE = MyISAM (or InnoDB)

-- 查看mysql所支持的引擎类型 (表类型)
SHOW ENGINES;

MyISAM 与 InnoDB 类型

适用场合：

• 适用 MyISAM : 节约空间及相应速度

• 适用 InnoDB : 安全性 , 事务处理及多用户操作数据表

数据表的存储位置

• MySQL数据表以文件方式存放在磁盘中

  • 包括表文件 , 数据文件 , 以及数据库的选项文件
  • 位置 : Mysql安装目录/data下存放数据表 . 目录名对应数据库名 , 该目录下文件名对应数据表

• 注意 :

  • *. frm -- 表结构定义文件

  • * . MYD -- 数据文件 ( data )

  • * . MYI -- 索引文件 ( index )

  • InnoDB类型数据表只有一个 *.frm文件 , 以及上一级目录的ibdata1文件

  • MyISAM类型数据表对应三个文件 :

    *.frm
    *.MYD
    *.MYI
    

设置数据表字符集

我们可为数据库,数据表,数据列设定不同的字符集，设定方法 :

• 创建时通过命令来设置 , 如 : CREATE TABLE 表名( .... )CHARSET = utf8;
• 如无设定 , 则根据MySQL数据库配置文件 my.ini 中的参数设定，这样子导入其他电脑的SQL文件会出现乱码

4.7. 修改数据库

修改表

修改表名 :ALTER  TABLE  旧表名  RENAME  AS  新表名

添加字段 : ALTER  TABLE  表名  ADD  字段名  列属性[属性]

修改字段 :
  ALTER  TABLE  表名  MODIFY  字段名  列类型[属性]       
  修改属性约束

  ALTER  TABLE  表名  CHANGE  旧字段名  新字段名  列属性[属性]     
  字段重命名和修改约束

删除字段 :  ALTER  TABLE  表名  DROP  字段名

删除表

语法：DROP  TABLE  [IF EXISTS]  表名

• IF EXISTS为可选 , 判断是否存在该数据表
• 如不加IF EXISTS，删除不存在的数据表会抛出错误

5. MySQL数据管理

5.1. 外键

作用

保持数据一致性，完整性，主要目的是控制存储在外键表中的数据,约束。使两张表形成关联，外键只能引用外表中的列的值或使用空值。

外键的两种创建方式

-- 创建外键的方式一 : 创建子表同时创建外键
-- 年级表 (id\年级名称)
CREATE TABLE `grade` ( `gradeid` INT ( 10 ) NOT NULL AUTO_INCREMENT COMMENT '年级ID', `gradename` VARCHAR ( 50 ) NOT NULL COMMENT '年级名称', PRIMARY KEY ( `gradeid` ) ) ENGINE = INNODB DEFAULT CHARSET = utf8 

-- 学生信息表 (学号,姓名,性别,年级,手机,地址,出生日期,邮箱,身份证号)
CREATE TABLE `student` (
    `studentno` INT ( 4 ) NOT NULL COMMENT '学号',
    `studentname` VARCHAR ( 20 ) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `gender` TINYINT ( 1 ) DEFAULT '1' COMMENT '性别',
    `gradeid` INT ( 10 ) DEFAULT NULL COMMENT '年级',
    `phoneNum` VARCHAR ( 50 )DEFAULT NULL COMMENT '手机',
    `address` VARCHAR ( 255 ) DEFAULT NULL COMMENT '地址',
    `borndate` DATETIME DEFAULT NULL COMMENT '生日',
    `email` VARCHAR ( 50 ) DEFAULT NULL COMMENT '邮箱',
    `idCard` VARCHAR ( 18 ) DEFAULT NULL COMMENT '身份证号',
    PRIMARY KEY ( `studentno` ),
    KEY `FK_gradeid` ( `gradeid` ),
CONSTRAINT `FK_gradeid` FOREIGN KEY ( `gradeid` ) REFERENCES `grade` ( `gradeid` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8

-- 创建外键方式二 : 创建子表完毕后,修改子表添加外键
ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY ( `gradeid` ) REFERENCES `grade` ( `gradeid` );

注意 : 删除具有主外键关系的表时 , 要先删（依赖）子表 , 后删（被依赖）主表。

-- 删除外键
ALTER TABLE student DROP FOREIGN KEY FK_gradeid;
-- 发现执行完上面的,索引还在,所以还要删除索引

-- 注:这个索引是建立外键的时候默认生成的
ALTER TABLE student DROP INDEX FK_gradeid;

以上操作都是物理外键，数据库级别的外键，不建议使用。（在程序层面进行实现）

阿里巴巴Java规范中有一条：不得使用外键与级联，一切外键概念必须在应用层解决。

5.2. DML语言

DML：数据操作语言。用于操作数据对象所包含的数据

• INSERT
• UPDATE
• DELETE

添加数据

Insert

语法：

INSERT INTO 表名 [(字段 1,字段 2,字段 3,...)]
VALUES
    (
        '值1',
    '值2',
    '值3'
  );

注意点：

• 字段或值之间用英文逗号隔开 。
• ' 字段1,字段2...' 该部分可省略 ，但添加的值务必与表结构，数据列，顺序相对应,且数量一致 。
• 可同时插入多条数据 ，values 后用英文逗号隔开 。

-- 使用语句如何增加语句?
-- 语法 : INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3')
INSERT INTO grade ( gradename )
VALUES
    ( '大一' );

-- '字段1,字段2...'该部分可省略 , 但添加的值务必与表结构,数据列,顺序相对应,且数量一致，一一对应。

-- 一次插入多条数据
INSERT INTO grade(gradename) VALUES ('大三'),('大四');

如果有外键，被依赖的表必须先有数据，依赖的表才可以插入数据。

修改数据

update

语法：

UPDATE 表名 
SET column_name = 
VALUE
    [, column_name2 = value2,...] [ WHERE CONDITION ];

注意：

• column_name 为要更改的数据列，多个用英文逗号隔开。
• value 为修改后的数据 , 可以为变量 , 具体值 , 表达式或者嵌套的SELECT结果。

UPDATE `student` 
SET `borndate` = CURRENT_TIME () 
WHERE
    `studentno` > 1 
    AND `studentno` < 8;

CURRENT_TIME ()为变量，表当前时间。

condition 为筛选条件 , 如不指定则修改该表的所有列数据。

开启安全模式防止无约束进行更新：

SET SQL_SAFE_UPDATES = 0;  -- 关闭安全模式
SET SQL_SAFE_UPDATES = 1;  -- 开启安全模式

where条件子句

删除数据

delete

语法：

DELETE 
FROM
    表名 [ WHERE CONDITION ];

注意：condition为筛选条件 , 如不指定则删除该表的所有列数据。

-- 删除某一行数据
DELETE 
FROM
    grade 
WHERE
    gradeid = ?;

truncate

作用：完全清空一个数据表，表的结构和索引约束不会变。

语法：

TRUNCATE [TABLE] table_name;

-- 清空年级表
TRUNCATE TABLE `grade`;

delete和truncate的区别：

• 使用TRUNCATE TABLE 会重新设置AUTO_INCREMENT计数器。
• 使用TRUNCATE TABLE不会对事务有影响。
• TRUNCATE速度更快。

delete表后重启数据库，会发现：

• InnoDB 自增列会重新从1开始 （存在内存当中，断电即失）

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

5.3. DQL语言

DQL( Data Query Language 数据查询语言 )

select

SELECT
    [ ALL | DISTINCT ] 
    { * | TABLE.* | [ TABLE.field1 [ AS 别名 ] 
                   [, TABLE.field2 [ AS 别名 ]] 
                   [,...]] } 
FROM
    table_name [ AS 别名 ] 
    [ LEFT | RIGHT | INNER JOIN table_name2 ON....] -- 联合查询
    [ WHERE...] -- 指定结果需满足的条件
    [ GROUP BY...] -- 指定结果按照哪几个字段来分组
    [ HAVING ] -- 过滤分组的记录必须满足的次要条件
    [ ORDER BY...] -- 指定查询记录按一个或多个条件排序
    [ 
    LIMIT {[ OFFSET,] row_count | row_countOFFSET OFFSET }];-- offset 起始数据行  row_count 行数
-- 指定查询的记录从哪条至哪条

注意 : [ ] 括号代表可选 , { }括号代表必选，条件限制 where 和 having 等语句顺序只能按照上面的顺序写。

指定查询字段

-- 查询所有学生信息，使用通配符效率低，不推荐
SELECT
    * 
FROM
    student;-- 查询指定列(学号 , 姓名)
SELECT
    studentno,
    studentname 
FROM
    student;

as关键字作为别名

• 可给数据列取一个新别名
• 可给表取一个新别名
• 可把经计算或总结的结果用另一个新名称来代替

-- 这里是为列取别名(as关键词可以省略)
SELECT
    studentno AS 学号,
    studentname AS 姓名 
FROM
    student;

-- 使用as也可以为表取别名
SELECT
    studentno AS 学号,
    studentname AS 姓名 
FROM
    student AS s;

-- 使用as,为查询结果取一个新名字
-- CONCAT()函数拼接字符串
SELECT
    CONCAT( '姓名:', studentname ) AS 新姓名 
FROM
    student;

distinct关键字

作用 : 去掉SELECT查询返回的记录结果中重复的记录 ( 返回所有列的值都相同 ) , 只返回一条。

--  查看哪些同学参加了考试(学号) 去除重复项
SELEC
    * 
FROM
    result;

-- 查看考试成绩
SELECT
    studentno 
FROM
    result;

-- 查看哪些同学参加了考试
SELECT DISTINCT
    studentno 
FROM
    result;

-- selcet查询中可以使用表达式
SELECT
    @@auto_increment_increment;-- 查询自增步长

SELECT
    VERSION();-- 查询版本号

SELECT
    100 * 3-1 AS 计算结果;-- 表达式

-- 考试成绩全部提分一分查看
SELECT
    studentno,
    studentresult + 1 AS '提分后' 
FROM
    result;

where条件语句

作用：用于检索数据表中 符合条件 的记录

逻辑操作符

-- 满足条件的查询(where)
SELECT
    Studentno,
    studentresult 
FROM
    result;

-- 查询考试成绩在95-100之间的
SELECT
    Studentno,
    studentresult 
FROM
    result 
WHERE
    studentresult >= 95 
    AND studentresult <= 100; 

-- AND也可以写成 &&
SELECT Studentno, StudentResult FROM result WHERE StudentResult >= 95 && StudentResult <= 100;

-- 模糊查询
SELECT
    Studentno,
    studentresult 
FROM
    result 
WHERE
    StudentResult BETWEEN 95 AND 100;

-- 除了1000号同学,要其他同学的成绩
SELECT
    studentno,
    studentresult 
FROM
    result 
WHERE
    studentno != 1000;

-- 使用NOT
SELECT
    studentno,
    studentresult 
FROM
    result 
WHERE
    NOT studentno = 1000;

模糊查询

比较操作符

注意：

• 数值数据类型的记录之间才能进行算术运算 ;
• 相同数据类型的数据之间才能进行比较 。

-- 查询姓刘的同学的学号及姓名
-- like结合使用的通配符 : % (代表0到任意个字符) _ (一个字符)
SELECT
    studentno,
    studentname 
FROM
    student 
WHERE
    studentname LIKE '刘%';

-- 查询姓刘的同学,后面只有一个字的
SELECT
    studentno,
    studentname 
FROM
    student 
WHERE
    studentname LIKE '刘_';

-- 查询姓刘的同学,后面只有两个字的
SELECT
    studentno,
    studentname 
FROM
    student 
WHERE
    studentname LIKE '刘__';

-- 查询姓名中含有 嘉 字的
SELECT
    studentno,
    studentname 
FROM
    student 
WHERE
    studentname LIKE '%嘉%';

-- 查询中含有特殊字符的需要使用转义符号 '\'
-- 自定义转义符关键字: ESCAPE ':'

-- IN
-- 查询学号为1000,1001,1002的学生姓名
SELECT
    studentno,
    studentname 
FROM
    student 
WHERE
    studentno IN ( 1000, 1001, 1002 );

-- 查询地址在北京,南京,河南洛阳的学生
SELECT
    studentno,
    studentname,
    address 
FROM
    student 
WHERE
    address IN ( '北京', '南京', '河南洛阳' );

-- 查询出生日期没有填写的同学
-- 不能直接写=NULL , 这是代表错误的 , 用 is null
SELECT
    studentname 
FROM
    student 
WHERE
    borndate IS NULL;

-- 查询出生日期填写的同学
SELECT
    studentname 
FROM
    student 
WHERE
    borndate IS NOT NULL;

-- 查询没有写家庭住址的同学(空字符串不等于null)
SELECT
    studentname 
FROM
    student 
WHERE
    address = '' 
    OR address IS NULL;

没有填写数据为null，查询这样的数据时用 is null。

连接查询

Join对比

七种Join连接方式

/*
连接查询
   如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询
内连接 inner join
   查询两个表中的结果集中的交集
外连接 outer join
   左外连接 left join
       (以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充)
   右外连接 right join
       (以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充)

等值连接和非等值连接

自连接
*/ 

-- 查询参加了考试的同学信息(学号,学生姓名,科目编号,分数)
SELECT
    * 
FROM
    student;

/*思路:
(1):分析需求,确定查询的列来源于两个类,student result,连接查询
(2):确定使用哪种连接查询?(内连接)
*/

SELECT
    s.studentno,
    studentname,
    subjectno,
    studentresult 
FROM
    student s
    INNER JOIN result r ON r.studentno = s.studentno 

-- 右连接(也可实现)
SELECT
    s.studentno,
    studentname,
    subjectno,
    studentresult 
FROM
    student s
    RIGHT JOIN result r ON r.studentno = s.studentno 

-- 等值连接
SELECT
    s.studentno,
    studentname,
    subjectno,
    studentresult 
FROM
    student s,
    result r 
WHERE
    r.studentno = s.studentno

-- 左连接 (查询了所有同学,不考试的也会查出来)
SELECT
    s.studentno,
    studentname,
    subjectno,
    studentresult 
FROM
    student s
    LEFT JOIN result r ON r.studentno = s.studentno 

-- 查一下缺考的同学(左连接应用场景)
SELECT
    s.studentno,
    studentname,
    subjectno,
    studentresult 
FROM
    student s
    LEFT JOIN result r ON r.studentno = s.studentno 
WHERE
    StudentResult IS NULL 

-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
SELECT
    s.studentno,
    studentname,
    subjectname,
    studentresult 
FROM
    student s
    INNER JOIN result r 
    -- 此处用 LEFT JOIN  RIGHT JOIN 都行，因为都是要与 subject 表进行连接的，结果都一样
    ON r.studentno = s.studentno
    INNER JOIN `subject` sub ON sub.subjectno = r.subjectno

自连接

数据表与自身进行连接

/*
自连接
   数据表与自身进行连接

需求:从一个包含栏目ID , 栏目名称和父栏目ID的表中
    查询父栏目名称和其他子栏目名称
*/
 -- 创建一个表
CREATE TABLE `category` (
    `categoryid` INT ( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
    `pid` INT ( 10 ) NOT NULL COMMENT '父id',
    `categoryName` VARCHAR ( 50 ) NOT NULL COMMENT '主题名字',
    PRIMARY KEY ( `categoryid` ) 
) ENGINE = INNODB AUTO_INCREMENT = 9 DEFAULT CHARSET = utf8 

-- 插入数据
INSERT INTO `category` ( `categoryid`, `pid`, `categoryName` )
VALUES
    ( '2', '1', '信息技术' ),
    ( '3', '1', '软件开发' ),
    ( '4', '3', '数据库' ),
    ( '5', '1', '美术设计' ),
    ( '6', '3', 'web开发' ),
    ( '7', '5', 'ps技术' ),
    ( '8', '2', '办公信息' );

-- 编写SQL语句,将栏目的父子关系呈现出来 (父栏目名称,子栏目名称)
-- 核心思想:把一张表看成两张一样的表,然后将这两张表连接查询(自连接)
SELECT
    a.categoryName AS '父栏目',
    b.categoryName AS '子栏目' 
FROM
    category AS a,
    category AS b 
WHERE
    a.`categoryid` = b.`pid`

-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
SELECT
    s.studentno,
    studentname,
    subjectname,
    studentresult 
FROM
    student s
    INNER JOIN result r ON r.studentno = s.studentno
    INNER JOIN `subject` sub ON sub.subjectno = r.subjectno 

-- 查询学员及所属的年级(学号,学生姓名,年级名)
SELECT
    studentno AS 学号,
    studentname AS 学生姓名,
    gradename AS 年级名称 
FROM
    student s
    INNER JOIN grade g ON s.`gradeid` = g.`gradeid` 

-- 查询科目及所属的年级(科目名称,年级名称)
SELECT
    subjectname AS 科目名称,
    gradename AS 年级名称 
FROM
    SUBJECT sub
    INNER JOIN grade g ON sub.gradeid = g.gradeid 

-- 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩)
SELECT
    s.studentno,
    studentname,
    subjectname,
    studentresult 
FROM
    student s
    INNER JOIN result r ON r.studentno = s.studentno
    INNER JOIN `subject` sub ON r.subjectno = sub.subjectno 
WHERE
    subjectname = '数据库结构-1'

分页和排序

排序order by

升序ASC（默认）

降序DESC

-- 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩)
-- 按成绩降序排序
SELECT
    s.studentno,
    studentname,
    subjectname,
    studentresult 
FROM
    student s
    INNER JOIN result r ON r.studentno = s.studentno
    INNER JOIN `subject` sub ON r.subjectno = sub.subjectno 
WHERE
    subjectname = '数据库结构-1' 
ORDER BY
    studentresult DESC

分页

语法 :

SELECT
    * 
FROM
TABLE 
    LIMIT [ OFFSET,] rows | rows OFFSET OFFSET


    /*
    推导:
   第一页 : limit 0,5
   第二页 : limit 5,5
   第三页 : limit 10,5
   ......
   第N页 : limit (pageNo-1)*pageSzie,pageSzie
   [pageNo:页码,pageSize:单页面显示条数]
    */

好处 : (用户体验,网络传输,查询压力)

-- 每页显示5条数据
SELECT
    s.studentno,
    studentname,
    subjectname,
    studentresult 
FROM
    student s
    INNER JOIN result r ON r.studentno = s.studentno
    INNER JOIN `subject` sub ON r.subjectno = sub.subjectno 
WHERE
    subjectname = '数据库结构-1' 
ORDER BY
    studentresult DESC 
    LIMIT 0,5

-- 查询 JAVA第一学年 课程成绩大于80分的前10名的学生信息(学号,姓名,课程名,分数)
SELECT
    s.studentno,
    studentname,
    subjectname,
    studentresult 
FROM
    student s
    INNER JOIN result r ON r.studentno = s.studentno
    INNER JOIN `subject` sub ON r.subjectno = sub.subjectno 
WHERE
    subjectname = 'JAVA第一学年' 
    AND studentresult >= 80 
ORDER BY
    studentresult DESC 
    LIMIT 0,
    10

子查询

什么是子查询? 在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句。 嵌套查询可由多个子查询组成,求解的方式是由里及外。 子查询返回的结果一般都是集合,故而建议使用IN关键字。

-- 查询 数据库结构-1 的所有考试结果(学号,科目编号,成绩),并且成绩降序排列
-- 方法一:使用连接查询
SELECT
    studentno,
    r.subjectno,
    studentresult 
FROM
    result r
    INNER JOIN `subject` sub ON r.`SubjectNo` = sub.`SubjectNo` 
WHERE
    subjectname = '数据库结构-1' 
ORDER BY
    studentresult DESC;

-- 方法二:使用子查询(执行顺序:由里及外)
SELECT
    studentno,
    subjectno,
    studentresult 
FROM
    result 
WHERE
    subjectno =(
    SELECT
        subjectno 
    FROM
        `subject` 
    WHERE
        subjectname = '数据库结构-1' 
    ) 
ORDER BY
    studentresult DESC;

-- 查询课程为 高等数学-2 且分数不小于80分的学生的学号和姓名
-- 方法一:使用连接查询
SELECT
    s.studentno,
    studentname 
FROM
    student s
    INNER JOIN result r ON s.`StudentNo` = r.`StudentNo`
    INNER JOIN `subject` sub ON sub.`SubjectNo` = r.`SubjectNo` 
WHERE
    subjectname = '高等数学-2' 
    AND studentresult >= 80 

-- 方法二:使用连接查询+子查询
-- 分数不小于80分的学生的学号和姓名
SELECT
    r.studentno,
    studentname 
FROM
    student s
    INNER JOIN result r ON s.`StudentNo` = r.`StudentNo` 
WHERE
    studentresult >= 80 

-- 在上面SQL基础上,添加需求:课程为 高等数学-2
SELECT
    r.studentno,
    studentname 
FROM
    student s
    INNER JOIN result r ON s.`StudentNo` = r.`StudentNo` 
WHERE
    studentresult >= 80 
    AND subjectno =(
    SELECT
        subjectno 
    FROM
        `subject` 
    WHERE
        subjectname = '高等数学-2' 
    ) 

-- 方法三:使用子查询
-- 分步写简单sql语句,然后将其嵌套起来
SELECT
    studentno,
    studentname 
FROM
    student 
WHERE
    studentno IN (
    SELECT
        studentno 
    FROM
        result 
    WHERE
        studentresult >= 80 
        AND subjectno =(
        SELECT
            subjectno 
        FROM
            `subject` 
        WHERE
            subjectname = '高等数学-2' 
        ) 
    )

分组和过滤

-- 查询不同课程的平均分,最高分,最低分
-- 前提:根据不同的课程进行分组
SELECT
    subjectname,
    AVG( studentresult ) AS 平均分,
    MAX( StudentResult ) AS 最高分,
    MIN( StudentResult ) AS 最低分 
FROM
    result AS r
    INNER JOIN `subject` AS s ON r.subjectno = s.subjectno 
GROUP BY
    r.subjectno -- 这里也可以用 sub.subjectno
HAVING
    平均分 > 80;

where写在group by前面。 分组后面的筛选，要使用HAVING。 因为having是从前面筛选的字段再筛选，而where是从数据表中的>字段直接进行筛选的。

6. MySQL函数

MySQL内置函数在线API

6.1. 数学函数

SELECT
    ABS(- 8 );
/*绝对值*/

SELECT
    CEILING( 9.4 );
/*向上取整*/

SELECT
    FLOOR( 9.4 );
/*向下取整*/

SELECT
    RAND();
/*随机数,返回一个0-1之间的随机数，包含0，不包含1*/

SELECT
    SIGN( 0 );
/*符号函数: 负数返回-1,正数返回1,0返回0*/

SELECT
    ROUND(x[, y])
/*若无y参数，则返回最接近于参数x的整数；若有y参数，则对参数进行四舍五入的操作，返回值保留小数点后面指定的y位*/

CAST ([double value] as INTEGER)
/*将浮点数强制转换为整数*/

6.2. 字符串函数

SELECT
    CHAR_LENGTH( '坚持就能成功' );
/*返回字符串包含的字符数*/

SELECT
    CONCAT( '我', '爱', '程序' );
/*合并字符串,参数可以有多个*/

SELECT INSERT
    ( '我爱编程helloworld', 1, 2, '超级热爱' );
/*这里的1是第一个字符，替换字符串,从某个位置开始替换某个长度*/

SELECT
    LOWER( 'helloworld' );
/*小写*/

SELECT
    UPPER( 'helloworld' );
/*大写*/

SELECT LEFT
    ( 'hello,world', 5 );
/*从左边第一个字符截取，长度*/

SELECT RIGHT
    ( 'hello,world', 5 );
/*从右边第一个字符截取，长度*/

SELECT REPLACE
    ( '坚持就能成功', '坚持', '努力' );
/*替换字符串*/

SELECT
    SUBSTR( '坚持就能成功', 4, 6 );
/*截取字符串,开始和长度*/

SELECT
    REVERSE( '坚持就能成功' );
/*反转 */

 -- 查询姓周的同学,改成邹
 SELECT REPLACE(studentname,'周','邹') AS 新名字
 FROM student WHERE studentname LIKE '周%';

6.3. 日期和时间函数

SELECT CURRENT_DATE
    ();
/*获取当前日期*/

SELECT
    CURDATE();
/*获取当前日期*/

SELECT
    NOW();
/*获取当前日期和时间*/

SELECT LOCALTIME
    ();
/*获取当前日期和时间*/

SELECT
    SYSDATE();
/*获取当前日期和时间*/

-- 获取年月日,时分秒
SELECT YEAR
    (
    NOW());

SELECT MONTH
    (
    NOW());

SELECT DAY
    (
    NOW());

SELECT HOUR
    (
    NOW());

SELECT MINUTE
    (
    NOW());

SELECT SECOND
    (
    NOW());

6.4. 系统信息函数

SELECT
    VERSION();
/*版本*/

SELECT USER
    ();
SELECT
    SYSTEM_USER();
/*用户*/

6.5. 聚合函数

-- count(字段)不统计字段为null 的记录
SELECT
    COUNT( studentname ) 
FROM
    student

-- count(*)统计字段为null 的记录
SELECT
    COUNT(*) 
FROM
    student;

-- count(1)统计字段为null 的记录
SELECT
    COUNT( 1 ) 
FROM
    student;

/* 
 1）列名不为主键时，count(1) 比 count(列名)快
 2）有主键时，主键作为计算条件，count(主键) 效率最高；
 3）表有多列并且没有主键，cout（1）比 count（*）效率高
 4）若表格只有一个字段，则 count(*) 效率较高。
 */

SELECT
    SUM( StudentResult ) AS 总和 
FROM
    result;
SELECT

    AVG( StudentResult ) AS 平均分 
FROM
    result;

SELECT
    MAX( StudentResult ) AS 最高分 
FROM
    result;

SELECT
    MIN( StudentResult ) AS 最低分 
FROM
    result;

6.6. 数据库级别的MD5加密

CREATE TABLE `testmd5` ( 
  `id` INT ( 4 ) NOT NULL, 
  `name` VARCHAR ( 20 ) NOT NULL, 
  `pwd` VARCHAR ( 50 ) NOT NULL, 
  PRIMARY KEY ( `id` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8

-- 更新明文成密文
UPDATE testmd5 
SET pwd = md5( pwd );

-- 插入新的数据时自动加密
INSERT INTO testmd5
VALUES
    (
        4,
    'root3',
    md5( '123456' ));

-- 查询登录用户信息（md5对比使用，查看用户输入加密后的密码进行比对）
SELECT
    * 
FROM
    testmd5 
WHERE
    `name` = 'root' 
    AND pwd = MD5( '123456' );

6.7. 开窗函数

Ref: BEGTUT

练习地址：window functions

支持版本：MySQL 8.0+

开窗函数，又称OLAP函数（Online Analytical Processing).

语法结构

<开窗函数> OVER (
            [PARTITION BY <列清单>]    -- 按照某列进行分区
            [ORDER BY <排序列清单>]    -- 根据某列进行排序
            [ROWS BETWEEN <值> PRECEDING AND <值> FOLOWING]    -- 窗口范围
            )

开窗函数主要有两种

1. 作为开窗的聚合函数(max, min, avg, max, count)
2. 专用开窗函数(rank, dense_rank, row_number)

窗口范围

1. rows between 1 preceding and 1 following 窗口范围是分区中的前一行、当前行、后一行共三行记录
2. rows between 1 preceding and current row 窗口范围是分区中的前一行、当前行共两行记录
3. rows between current row and 1 following 窗口范围是分区中当前行、后一行共两行记录
4. rows unbounded preceding 窗口范围是分区中第一行到当前行
5. rows between unbounded preceding and current row 窗口范围是分区中的第一行到当前行
6. rows between current row and unbounded following 窗口范围是分区中当前行到最后一行
7. rows between unbounded preceding and unbounded following 窗口范围是当前分区中的所有行，等同于不写

窗口范围可用rows/range

如果指定了order by 子句未指定窗口子句，则默认为RANGE BETWEEN unbounded preceding AND CURRENT ROW

select name,
weight,
percent_rank() over (order by weight) * 100 as percent
 from cats 

select distinct(breed),
               nth_value(weight, 2)
                         over ( partition by breed order by weight RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) as imagined_weight
from cats
order by breed;

6.8. 其他函数

1. COALESCE(val1, val2) 如果val1的值为NULL，则用VAL2的值进行替代。COALESCE意为合并。

7. 视图

含义：视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。视图引用自表，并且在引用视图时动态生成。视图具有表结构文件，但不存在数据文件。

视图是存储在数据库中的查询的sql语句，它主要出于两种原因：安全原因，视图可以隐藏一些数据，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等，另一原因是可使复杂的查询易于理解和使用。

-- 创建视图
CREATE [ OR REPLACE ] 
[ ALGORITHM = { UNDEFINED | MERGE | TEMPTABLE }] 
VIEW view_name [(
    column_list 
)] AS select_statement ;

-- 视图名必须唯一，同时不能与表重名
-- 视图可以使用 SELECT
-- 语句查询到的列名，也可以自己指定相应的列名
-- 可以指定视图执行的算法，通过 ALGORITHM 指定
-- column_list 如果存在，则数目必须等于SELECT语句检索的列数 

--  查看结构
SHOW CREATE VIEW view_name 

-- 删除视图
-- 删除视图后，数据依然存在。
-- 可同时删除多个视图。 
DROP VIEW [ IF EXISTS ] view_name...

-- 修改视图结构
-- 一般不修改视图，因为不是所有的更新视图都会映射到表上。 
ALTER VIEW view_name [(
    column_list 
)] AS select_statement 

/*
视图作用
 1.简化业务逻辑
 2.对客户端隐藏真实的表结构 
*/

-- 视图算法(ALGORITHM)
/*
MERGE 合并
将视图的查询语句，与外部查询需要先合并再执行！

TEMPTABLE 临时表
将视图执行完毕后，形成临时表，再做外层查询！ 

UNDEFINED 未定义 (默认)，指的是 MySQL自主去选择相应的算法。
*/

8. 事务

事务的原则ACID

原子性(Atomic)

• 整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（ROLLBACK）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性(Consist)

• 一个事务可以封装状态改变（除非它是一个只读的）。事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。也就是说：如果事务是并发多个，系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an Invariant)，以转账案例为例，假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间转账15元，五个账户总额也应该还是500元，这就是保护性和不变性。

隔离性(Isolated)

• 隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。

持久性(Durable)

• 在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

并发事物带来的问题

• 更新丢失：多个事务对同一数据进行修改，最后只保留了最后修改的值，其他事务修改的值都被覆盖了。

• 脏读：指一个事务读取了另外一个事务已修改但未提交的数据。

• 不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。读取到了另一个事务提交的修改数据，不符合隔离性（这个不一定是错误，只是某些场合不对）

• 虚读(幻读)：是指在一个事务内读取到了别的事务新增插入的数据，导致前后读取不一致。 （一般是行影响，多了一行）

事务隔离级别：未提交读(Read uncommitted)，已提交读(Read committed)，可重复读(Repeatable read)，可序列化(Serializable)。

SELECT
    @@GLOBAL.transaction_isolation,
    @@transaction_isolation;

-- 查询隔离级别
-- @@global.transaction_isolation 全局事务隔离级别
-- @@transaction_isolation 当前表的事务隔离级别

四种隔离级别的比较

ySQL InnoDB的事务隔离级别为 可重复读(Repeatable read) ，而MySQL 8.0 以上已经可以解决幻读问题，但是隔离级别还是 可重复读(Repeatable read) .

基本语法

-- 使用set语句来改变自动提交模式

SET autocommit = 0;
/*关闭*/

SET autocommit = 1;
/*开启*/

-- 注意:
-- 1.MySQL中默认是自动提交 
-- 2.使用事务时应先关闭自动提交


-- 开始一个事务,标记事务的起始点
SET autocommit = 0; 
START TRANSACTION 

-- 提交一个事务给数据库，commit 一次就提交一次事务
COMMIT 
-- 将事务回滚,数据回到本次事务的初始状态
ROLLBACK -- 还原MySQL数据库的自动提交

SET autocommit = 1;



-- 保存点
SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点

/*
A在线买一款价格为500元商品,网上银行转账.
A的银行卡余额为2000,然后给商家B支付500.
商家B一开始的银行卡余额为10000

创建数据库shop和创建表account并插入2条数据
*/ 

CREATE DATABASE `shop` CHARACTER 
SET utf8 COLLATE utf8_general_ci;
USE `shop`;

CREATE TABLE `account` (
    `id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR ( 32 ) NOT NULL,
    `cash` DECIMAL ( 9, 2 ) NOT NULL,
    PRIMARY KEY ( `id` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8 

INSERT INTO account ( `name`, `cash` )
VALUES
    ( 'A', 2000.00 ),( 'B',10000.00 ); -- 转账实现


SET autocommit = 0;-- 关闭自动提交
START TRANSACTION;-- 开始一个事务,标记事务的起始点

UPDATE account 
SET cash = cash - 500 
WHERE
    `name` = 'A';

UPDATE account 
SET cash = cash + 500 
WHERE
    `name` = 'B';

COMMIT;-- 提交事务
-- ROLLBACK;

SET autocommit = 1;-- 恢复自动提交

9. 索引

索引就是排好序的快速查找数据结构 。

在数据库之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

9.1. 索引的优势

• 提高查询速度
• 确保数据的唯一性
• 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
• 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
• 全文检索字段进行搜索优化.

9.2. 索引的劣势

• 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
• 虽然索引大大提高了查询的速度，同时也会降低更新表的速度，如 insert， update，delete 。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

9.3. 索引准则

• 索引不是越多越好，一般建议5个
• 不要对经常变动的数据加索引
• 小数据量的表建议不要加索引
• 如果某个数据列包含许多重复内容，为它建立索引没有太大的实际效果
• 高并发下推荐使用复合索引
• 索引一般应加在频繁作为查找条件的字段
• 查询中与其他表关联的字段
• where 条件里用不到的字段不建立索引
• 排序字段建立索引，大大提高排序速度
• 查询中统计或者分组的字段

9.4. 索引的数据结构

我们可以在创建上述索引的时候，为其指定索引类型，分两类： hash类型的索引：查询单条快，范围查询慢 btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

-不同的存储引擎支持的索引类型也不一样 InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引； MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引； Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引； NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引； Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

9.5. 索引的分类

• 主键索引 (Primary Key)
• 唯一索引 (Unique)
• 常规索引 (Index)
• 全文索引 (FullText)

聚集索引（主键索引）

作用 : 某一个属性组能唯一标识一条记录

特点 :

• 最常见的索引类型，是特殊的唯一索引
• 确保数据记录的唯一性
• 确定特定数据记录在数据库中的位置
• 不能为空

唯一索引

作用 : 避免同一个表中某数据列中的值重复

与主键索引的区别

• 主键索引只能有一个，唯一索引可能有多个

• 主键索引不能为空值，唯一索引可以为空值

CREATE TABLE `Grade` (
    `GradeID` INT ( 11 ) AUTO_INCREMENT PRIMARYKEY,
  `GradeName` VARCHAR ( 32 ) NOT NULL UNIQUE -- 或 UNIQUE KEY `GradeID` (`GradeID`)
)

常规索引

作用 : 快速定位特定数据

注意 :

• index 和 key 关键字都可以设置常规索引
• 应加在查询找条件的字段
• 不宜添加太多常规索引,影响数据的插入,删除和修改操作

CREATE TABLE `result` ( -- 省略一些代码
    INDEX / KEY `ind` ( `studentNo`, `subjectNo` ) -- 创建表时添加
) 

-- 创建后添加
ALTER TABLE `result` ADD INDEX `ind` ( `studentNo`, `subjectNo` );

全文索引

作用场景：当查询条件为where column like ‘%xxx%’时，会让索引失效，此时全文索引便派上用场了

作用 : 快速定位特定数据

注意 :

• 只能用于MyISAM类型的数据表（5.6以上后 InnoDB 也支持）
• 只能用于CHAR , VARCHAR , TEXT数据列类型
• 数据表越大，全文索引效果好，小表返回结果可能不理想
• 对于较大的数据集，将数据输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把数据输入现有FULLTEXT索引的速度更为快
• 生成全文索引是一个非常耗时且非常耗硬盘空间的做法
• 全文索引创建速度慢，而且对有全文索引的各种数据修改操作也慢
• 少于3个字符的单词不会被包含在全文索引里，可以通过修改my.cnf的ft_min_word_len选项进行设置

9.6. 索引的创建

/*
# 方法一：创建表时
  　　CREATE TABLE 表名 (
               字段名1 数据类型 [完整性约束条件…],
               字段名2 数据类型 [完整性约束条件…],
               [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
               [索引名] (字段名[(长度)] [ASC |DESC])
               );


# 方法二：CREATE在已存在的表上创建索引
       CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
                    ON 表名 (字段名[(长度)] [ASC |DESC]) ;


# 方法三：ALTER TABLE在已存在的表上创建索引
       ALTER TABLE 表名 
       ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (字段名[(长度)] [ASC |DESC]) ;


# 删除索引：DROP INDEX 索引名 ON 表名字;
# 删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;


# 显示索引信息: SHOW INDEX FROM student;
*/ 

/*增加全文索引*/
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` ( `StudentName` );

/*EXPLAIN : 分析SQL语句执行性能*/
EXPLAIN SELECT
    * 
FROM
    student 
WHERE
    studentno = '1000';

/*使用全文索引*/
-- 全文搜索通过 MATCH() 函数完成。
-- 搜索字符串作为 against() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行，MATCH() 返回一个相关性值。即，在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。
EXPLAIN SELECT
    * 
FROM
    student 
WHERE
    MATCH ( studentname ) AGAINST ( 'love' );

全文索引的版本、存储引擎、数据类型的支持情况

MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引； MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引; 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

9.7. 测试索引

测试表

CREATE TABLE `app_user` (
    `id` BIGINT ( 20 ) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR ( 50 ) DEFAULT '' COMMENT '用户昵称',
    `email` VARCHAR ( 50 ) NOT NULL COMMENT '用户邮箱',
    `phone` VARCHAR ( 20 ) DEFAULT '' COMMENT '手机号',
    `gender` TINYINT ( 4 ) UNSIGNED DEFAULT '0' COMMENT '性别（0:男；1：女）',
    `password` VARCHAR ( 100 ) NOT NULL COMMENT '密码',
    `age` TINYINT ( 4 ) DEFAULT '0' COMMENT '年龄',
    `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
    `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,-- 数据插入和更新时，更新值
PRIMARY KEY ( `id` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;

批量插入100万条数据

当二进制日志启用后，这个变量就会启用。它控制是否可以信任存储函数创建者，不会创建写入二进制日志引起不安全事件的存储函数。如果设置为0（默认值），用户不得创建或修改存储函数，除非它们具有除CREATE ROUTINE或ALTER ROUTINE特权之外的SUPER权限。 设置为0还强制使用DETERMINISTIC特性或READS SQL DATA或NO SQL特性声明函数的限制。 如果变量设置为1，MySQL不会对创建存储函数实施这些限制。

-- 打开用户创建和修改函数的权限
SET GLOBAL log_bin_trust_function_creators = 1;

DROP FUNCTION
IF
    EXISTS mock_data;

DELIMITER {% math %}    -- 要写函数必须写，设置成以 {% endmath %} 为语句结束符
CREATE FUNCTION mock_data () 
RETURNS INT BEGIN
    DECLARE
        num INT DEFAULT 1000000;
    DECLARE
        i INT DEFAULT 0;
    WHILE i < num 
            DO
            INSERT INTO app_user ( `name`, `email`, `phone`, `gender`, `password`, `age` )
        VALUES
            (
                CONCAT( '用户', i ),
                '[email protected]',
                CONCAT(
                    '18',
                FLOOR( RAND()*( 999999999-100000000 )+ 100000000 )),    -- 随机生成手机号
                FLOOR( RAND()* 2 ),
                UUID(),
            FLOOR( RAND()* 100 ));

        SET i = i + 1;

    END WHILE;
    RETURN i;

END $$;
SELECT
    mock_data (); -- 执行函数

测试索引

SELECT * FROM app_user WHERE name = '用户9999';    -- OK, Time: 1.301000s
SELECT * FROM app_user WHERE name = '用户9999';    -- OK, Time: 1.349000s
EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'    -- 993353

CREATE INDEX idx_app_user_name ON app_user(name);

SELECT * FROM app_user WHERE name = '用户9999';    -- OK, Time: 0.004000s
SELECT * FROM app_user WHERE name = '用户9999';    -- OK, Time: 0.004000s
EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'    -- 1

9.8. 复合索引（联合索引）

复合索引

索引可以覆盖多个数据列，如像INDEX（columnA，columnB）索引。这种索引的特点是 MySQL可以有选择地使用一个这样的索引。如果查询操作只需要用到 columnA 数据列上的一个索引，就可以使用复合索引 INDEX（columnA,columnB）。不过，这种用法仅适用于在复合索引中排列在前的数据列组合。比如说，INDEX（A，B，C）可以当做A或（A, B）的索引来使用，但不能当做B、C或（B，C）的索引来使用。（这里的字段组合判断只跟select的where条件、group by 、order by字段有关）

特点

创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。

复合索引的建立原则

如果仅对一个列多次执行搜索，则该列应该是复合索引中的第一列。如果对一个两列索引中的两个列执行单独的搜索，则应该创建另一个仅包含第二列的索引。

包含多个列的主键始终会自动以复合索引的形式创建索引，其列的顺序是它们在表定义中出现的顺序，而不是在主键定义中指定的顺序。在考虑将来通过主键执行的搜索，确定哪一列应该排在最前面。创建复合索引应当包含少数几个列，并且这些列经常在select查询里使用。在复合索引里包含太多的列不仅不会给带来太多好处。而且由于使用相当多的内存来存储复合索引的列的值，其后果是内存溢出和性能降低。

查询优化

复合索引对排序的优化：

复合索引只对和索引中排序相同或相反的order by 语句优化 在创建复合索引时，每一列都定义了升序或者是降序。如定义一个复合索引

CREATE INDEX idx_example
ON table1 
( col1 ASC, 
 col2 DESC, 
 col3 ASC );

执行和复合索引顺序相同或者相反的查询操作时（要么全部相同，要么全部相反），可以被复合索引优化。反之不会。

10. 用户管理

10.1. 基本命令

/* 用户和权限管理 */ 
-- 刷新权限
FLUSH PRIVILEGES 

-- 增加用户 
CREATE USER user1 IDENTIFIED BY '123456'
CREATE USER 用户名 IDENTIFIED BY [ PASSWORD ] 密码 (字符串) 
-- 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。
-- 只能创建用户，不能赋予权限。
-- 用户名，注意引号：如 'user_name'@'192.168.1.1'或者'user_name'@'localhost'

-- 密码也需引号，纯数字密码也要加引号
-- 要在纯文本中指定密码，需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的混编值，需包含关键字PASSWORD
-- 重命名用户 
RENAME USER user1 TO user2
RENAME USER old_user TO new_user 

-- 设置密码
SET PASSWORD = PASSWORD ( '密码' ) 

-- 为当前用户设置密码
SET PASSWORD FOR 用户名 = PASSWORD ( '密码' ) -- 为指定用户设置密码

-- 8.0版本以上不支持上面命令，得下面命令
USE MYSQL;
ALTER USER 'root' @'localhost' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY '123456';
FLUSH PRIVILEGES;

-- 删除用户 
DROP USER user2
DROP USER 用户名 
-- 分配权限/添加用户
GRANT 权限列表 ON 表名 TO 用户名 [ IDENTIFIED BY [ PASSWORD ] 'password' ] 
-- ALL PRIVILEGES 表示所有权限，但不包含授权给他人权限 
-- *.* 表示所有库的所有表  
-- 库名.表名 表示某库下面的某表 

-- 查看权限 SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR 用户名 
-- 查看当前用户权限
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER ();

-- 撤消权限
REVOKE 权限列表 ON 表名 
FROM
    用户名 

-- 撤销所有权限
REVOKE ALL PRIVILEGES,
GRANT OPTION 
FROM
    用户名

mysql 5.7.9以后废弃了password字段和password()函数；authentication_string:字段表示用户密码。

10.2. 权限解释

-- 权限列表
ALL [ PRIVILEGES ] -- 设置除GRANT OPTION之外的所有简单权限
ALTER -- 允许使用ALTER TABLE
ALTER ROUTINE -- 更改或取消已存储的子程序
CREATE -- 允许使用CREATE TABLE
CREATE ROUTINE -- 创建已存储的子程序
CREATE TEMPORARY TABLES -- 允许使用CREATE TEMPORARY TABLE
CREATE USER -- 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW -- 允许使用CREATE VIEW
DELETE -- 允许使用DELETE
DROP -- 允许使用DROP TABLE
EXECUTE -- 允许用户运行已存储的子程序
FILE -- 允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX -- 允许使用CREATE INDEX和DROP INDEX
INSERT -- 允许使用INSERT
LOCK TABLES -- 允许对您拥有SELECT权限的表使用LOCK TABLES
PROCESS -- 允许使用SHOW FULL PROCESSLIST
REFERENCES -- 未被实施
RELOAD -- 允许使用FLUSH
REPLICATION CLIENT -- 允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE -- 用于复制型从属服务器（从主服务器中读取二进制日志事件）
SELECT-- 允许使用SELECT
SHOW DATABASES -- 显示所有数据库
SHOW VIEW -- 允许使用SHOW CREATE VIEW
SHUTDOWN -- 允许使用mysqladmin shutdown
SUPER -- 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。
UPDATE -- 允许使用UPDATE
USAGE -- “无权限”的同义词
GRANT OPTION -- 允许授予权限

/* 表维护 */
-- 分析和存储表的关键字分布
ANALYZE [ LOCAL | NO_WRITE_TO_BINLOG ] TABLE 表名...-- 检查一个或多个表是否有错误
CHECK TABLE tbl_name [, tbl_name ]...[ OPTION ]...OPTION = { QUICK | FAST | MEDIUM | EXTENDED | CHANGED } -- 整理数据文件的碎片
OPTIMIZE [ LOCAL | NO_WRITE_TO_BINLOG ] TABLE tbl_name [, tbl_name ]...

11. MySQL备份

数据库备份必要性

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

MySQL数据库备份方法

• mysqldump
• 数据库管理工具
• 直接拷贝数据库文件和相关配置文件

11.1. mysqldump

-- 导出
1.导出一张表 
mysqldump [-h主机名] -uroot -p123456 school student >/Users/admin/Downloads/1.sql
　　
mysqldump - u用户名 - p密码 库名 表名 > 文件名 ( 路径 ) 

2.导出多张表 
mysqldump [-h主机名] -uroot -p123456 school student result >/Users/admin/Downloads/1.sql
　　
mysqldump - u用户名 -p密码 库名 表1 表2 表3 > 文件名  ( 路径 )

3.导出所有表 
mysqldump [-h主机名] -uroot -p123456 school >/Users/admin/Downloads/1.sql
mysqldump -u用户名 -p密码 库名 > 文件名  ( 路径 )

4.导出一个库 
mysqldump [-h主机名] -uroot -p123456 -B school >/Users/admin/Downloads/1.sql

mysqldump - u用户名 - p密码 - B 库名 > 文件名 ( 路径 ) [ - w携带备份条件 ]

-- 导入
1.在登录 mysql的情况下： 
source 还原文件 
-- 导入表的话需要指定导入的数据库，先 use 数据库。

2.在不登录的情况下
　　 mysql - u用户名 - p密码 库名 < 备份文件

12. 规范化数据库设计

糟糕的数据库设计 :

• 数据冗余,存储空间浪费
• 数据更新和插入的异常
• 程序性能差

良好的数据库设计 :

• 节省数据的存储空间
• 能够保证数据的完整性
• 方便进行数据库应用系统的开发

软件项目开发周期中数据库设计 :

• 需求分析阶段: 分析客户的业务和数据处理需求
• 概要设计阶段:设计数据库的E-R模型图 , 确认需求信息的正确和完整

设计数据库步骤

• 收集信息
• • 与该系统有关人员进行交流 , 座谈 , 充分了解用户需求 , 理解数据库需要完成的任务.
• 标识实体[Entity]
• • 标识数据库要管理的关键对象或实体,实体一般是名词
  • 标识每个实体需要存储的详细信息[Attribute]
  • 标识实体之间的关系[Relationship]

12.1. 三大范式

问题 : 为什么需要数据规范化?

不合规范的表设计会导致的问题：

• 信息重复

• 更新异常

• 插入异常

• • 无法正确表示信息

• 删除异常

• • 丢失有效信息

第一范式 (1st NF)

第一范式的目标是确保每列的原子性,如果每列都是不可再分的最小数据单元,则满足第一范式

第二范式(2nd NF)

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。

第二范式要求每个表只描述一件事情

第三范式(3rd NF)

如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式.

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

规范化和性能的关系

关联查询的表不得超过三张表

• 为满足某种商业目标 , 数据库性能比规范化数据库更重要

• 在数据规范化的同时 , 要综合考虑数据库的性能

• 通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间（从多表查询变为单表查询）

• 通过在给定的表中插入计算列,以方便查询通过在给定的表中插入计算列,以方便查询（从大数据量降低为小数据量的查询：索引）

13. JDBC

步骤：

1、加载驱动，通过 Class.forname（）加载

2、获取数据库连接对象 Connection ，通过 DriverManager 的 getConnection 方法获取

3、获得执行SQL的对象 Statement，通过连接对象 Connection 的createStatement 获取

4、获得返回的结果集 Resultset，通过 Statement 对象 executeQuery 或 executeUpdate 返回获得

5、关闭所有连接对象，Resultset、Statement、Connection 的关闭。

URL

String url = "jdbc:mysql://localhost:3306/数据库名?useUnicode=true&characterEncoding=gbk&useSSL=true" ;    
//"jdbc:oracle:thin:@localhost:1521:表名"  Oracle 默认端口是1521

Statement对象的一些常用方法

• execute() ，返回的结果boolean，boolean表示是否有结果集返回（除select外为false），有为true，其他情况都为false

• executeUpdate() ，返回的结果int，int表是对数据库影响的行计数

• executeQuery()， 返回的结果resultSet，一般情况存放的是select查询的结果集

  • resultSet

    ResultSet resultSet = prepareStatement.executeQuery(sql);
    
    resultSet.next();    // 移动到下一行
    resultSet.previous();    // 移动到上一行
    resultSet.beforeFirst();    // 移动到最前面
    resultSet.afterLast();    // 移动到最后面
    resultSet.absolute(int row);    // 移动到指定行
    

13.1. 封装工具类

JDBCUtils

package com.gs.utils;

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

/**
 * @author admin
 * @date 2021/9/6 12:20 下午
 */
public class JDBCUtils {
    private static Connection connection = null;

    static {
        try {
            InputStream resourceAsStream = JDBCUtils.class.getClassLoader().getResourceAsStream("properties/jdbc.properties");
            Properties properties = new Properties();
            properties.load(resourceAsStream);
            String classname = properties.getProperty("classname");
            String url = properties.getProperty("url");
            String username = properties.getProperty("username");
            String password = properties.getProperty("password");
            Class.forName(classname);
            connection = DriverManager.getConnection(url, username, password);
        } catch (IOException | ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        return connection;
    }

    public static void close(Connection connection, Statement statement, ResultSet resultSet) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

properties/jdbc.properties

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

测试

package com.gs.utils;

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

/**
 * @author admin
 * @date 2021/9/6 12:32 下午
 */
public class Test {
    static Connection connection = null;
    static Statement statement = null;
    static ResultSet resultSet = null;

    public static void main(String[] args) throws SQLException {
        try {
            String sql = "SELECT * FROM app_user WHERE name = '用户9999'";
            connection = JDBCUtils.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                System.out.println(resultSet.getString("name"));
            }
        } finally {
            JDBCUtils.close(connection, statement, resultSet);
        }
    }
}

13.2. PreparedStatement

PreparedStatement 可以预防SQL语句注入，更安全，当然它的效率也更高一些。

SQL注入

经典注入：'or '1=1

SQL注入攻击指的是通过构建特殊的输入作为参数传入 Web 应用程序，而这些输入大都是 SQL 语法里的一些组合，通过执行 SQL语句进而执行攻击者所要的操作，其主要原因是程序没有细致地过滤用户输入的数据，致使非法数据侵入系统。

根据相关技术原理，SQL注入可以分为平台层注入和代码层注入。前者由不安全的数据库配置或数据库平台的漏洞所致；后者主要是由于程序员对输入未进行细致地过滤，从而执行了非法的数据查询。基于此，SQL注入的产生原因通常表现在以下几方面：①不当的类型处理；②不安全的数据库配置；③不合理的查询集处理；④不当的错误处理；⑤转义字符处理不合适；⑥多个提交处理不当。

PreparedStatement 防止 SQL 注入的原理

PreparedStatement 把传递过来的参数当做字符，假设其中存在转义字符，比如说 ` 会被直接转义

//STEP 1. Import required packages

import java.sql.*;

public class JDBCExample {
    // JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost/Test?serverTimezone=UTC";

    // Database credentials
    static final String USER = "root";
    static final String PASS = "root";

    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            // STEP 2: Register JDBC driver
            Class.forName("com.mysql.jdbc.Driver");

            // STEP 3: Open a connection
            System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            // STEP 4: Execute a query
            System.out.println("Creating statement...");
            String sql = "UPDATE Employees set age=? WHERE id=?";
            stmt = conn.prepareStatement(sql);

            // Bind values into the parameters.
            stmt.setInt(1, 35); // 设置参数
            stmt.setInt(2, 102); // 设置参数

            // Let us update age of the record with ID = 102;
            int rows = stmt.executeUpdate();
            System.out.println("Rows impacted : " + rows);

            // Let us select all the records and display them.
            sql = "SELECT id, first, last, age FROM Employees";
            ResultSet rs = stmt.executeQuery(sql);

            // STEP 5: Extract data from result set
            while (rs.next()) {
                // Retrieve by column name
                int id = rs.getInt("id");
                int age = rs.getInt("age");
                String first = rs.getString("first");
                String last = rs.getString("last");

                // Display values
                System.out.print("ID: " + id);
                System.out.print(", Age: " + age);
                System.out.print(", First: " + first);
                System.out.println(", Last: " + last);
            }
            // STEP 6: Clean-up environment
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            // Handle errors for JDBC
            se.printStackTrace();
        } catch (Exception e) {
            // Handle errors for Class.forName
            e.printStackTrace();
        } finally {
            // finally block used to close resources
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException se2) {
            } // nothing we can do
            try {
                if (conn != null)
                    conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            } // end finally try
        } // end try
        System.out.println("Goodbye!");
    }// end main
}// end JDBCExample

13.3. 事务

package com.gs.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;

public class Test {
    public static void main(String[] args) throws ClassNotFoundException, IOException {
        InputStream resourceAsStream = JDBCUtils.class.getClassLoader().getResourceAsStream("properties/jdbc.properties");
        Properties properties = new Properties();
        properties.load(resourceAsStream);
        String classname = properties.getProperty("classname");
        String url = properties.getProperty("url");
        String username = properties.getProperty("username");
        String password = properties.getProperty("password");
        Class.forName(classname);
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DriverManager.getConnection(url, username, password);
            //关闭自动提交，就会自动开启事务，开启事务前必须先关闭自动提交
            connection.setAutoCommit(false);
            String sql1 = "update shop set money = money - 200 where name = '张三'";
            String sql2 = "update shop set money = money + 200 where name = '李四'";

            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.executeUpdate();

            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();
            connection.commit();
            System.out.println("成功");

        } catch (Exception e) {
            //有异常默认会回滚事务
            //  connection.rollback();
            e.printStackTrace();
        } finally {
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }
}

13.4. DBCP

自动化操作，自动化加载配置文件，并且可以自动设置到对象中。

DBCPUtils

package com.gs.utils;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;

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

/**
 * @author admin
 * @date 2021/9/6 12:20 下午
 */
public class DBCPUtils {
    private static Connection connection = null;

    static {
        try {
            InputStream resourceAsStream = DBCPUtils.class.getClassLoader().getResourceAsStream("properties/dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(resourceAsStream);
            DataSource dataSource = BasicDataSourceFactory.createDataSource(properties);
            connection = dataSource.getConnection();
        } catch (IOException | ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        return connection;
    }

    public static void close(Connection connection, Statement statement, ResultSet resultSet) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }


}

properties/dbcpconfig.properties

#连接设置
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=root

#<!-- 初始化连接 -->
initialSize=10

#最大连接数量
maxActive=50

#<!-- 最大空闲连接 -->
maxIdle=20

#<!-- 最小空闲连接 -->
minIdle=5

#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000


#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名=property;]
#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=gbk

#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

测试

package com.gs.utils;

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

/**
 * @author admin
 * @date 2021/9/6 12:32 下午
 */
public class Test {
    static Connection connection = null;
    static Statement statement = null;
    static ResultSet resultSet = null;

    public static void main(String[] args) throws SQLException {
        try {
            String sql = "SELECT * FROM app_user WHERE name = '用户9999'";
            connection = DBCPUtils.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                System.out.println(resultSet.getString("name"));
            }
        } finally {
            JDBCUtils.close(connection, statement, resultSet);
        }
    }
}

13.5. C3P0

半自动化操作，不能自动连接

C3P0Utils

package com.gs.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * @author admin
 * @date 2021/9/6 12:20 下午
 */
public class C3P0Utils {
    private static Connection connection = null;

    static {
        try {
            ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
            connection = comboPooledDataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        return connection;
    }

    public static void close(Connection connection, Statement statement, ResultSet resultSet) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

c3p0-config.xml

<c3p0-config>
    <!-- 使用默认的配置读取连接池对象 -->
    <default-config>
        <!--  连接参数 -->
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <!-- 必须用&amp; 替代 &-->
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/test?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=true</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.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/test?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=true</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>

测试

package com.gs.utils;

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

/**
 * @author admin
 * @date 2021/9/6 12:32 下午
 */
public class Test {
    static Connection connection = null;
    static Statement statement = null;
    static ResultSet resultSet = null;

    public static void main(String[] args) throws SQLException {
        try {
            String sql = "SELECT * FROM app_user WHERE name = '用户9999'";
            connection = C3P0Utils.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                System.out.println(resultSet.getString("name"));
            }
        } finally {
            JDBCUtils.close(connection, statement, resultSet);
        }
    }
}

13.6. Druid

13.7. Hikari

13.8. JDBC工具类

com.mysql.cj.util.StringUtils

• StringUtils.isNullOrEmpty(String test)测试request获得的值是否为空或者为null。

14. 一些问题

1. MySQL 8-和MySQL 8+的驱动包名不同。

   MySQL8-:com.mysql.jdbc.Driver
   MySQL8+:com.mysql.jdbc.cj.Driver
   

2. MySQL 8 连接最好设置时区serverTimezone=GMT+8

3. MySQL分页的时候，使用limit的第一个参数为开始下标，第二个参数为每页的个数。每页的个数不能为负数。以前有些版本可以为负数，实际上是一个bug，已经被修复。只能用一个大的正数。

   https://bugs.mysql.com/bug.php?id=2037

4. npm依赖不支持MySQL8+版本的强密码模式

   解决方法：

   修改密码规则并更改MySQL密码，同时刷新权限。

   ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘password’ PASSWORD EXPIRE NEVER;    // 修改mysql密码规则
   ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’;    // 更改新的密码，可以将密码设置为简单类型
   FLUSH PRIVILEGES; //刷新权限
   

5. 在获取某些最新的记录的时候，使用max(column_name)不会将max(column_name)所带的整行数据列出，只是某个值进行替换，可以变成字查询（SQL5.7开始子查询顺序排序无效，除非加上limit条件）。

   select table_b.id                         as id,    -- 子查询中的最大id
          table_b.content                 as content   -- 最新的信息
   from (
            select max(id) as id   -- 要取的最大的id
            from table_a
            where name = "a"   -- 指定检索条件
            group by gender    -- 聚合条件，去重之用
        ) as a -- 这里获得的是符合条件的最大id
            left join table_b b on table_a.id = table_b.id -- 指定join的条件
   

JDBC驱动参考：

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=Asia/Shanghai
username=root
password=root