MySQL详细基础教程

目录

1、初始MySQL

1.1 为什么要学习数据库？

1.2 什么是数据库？

1.3 数据库分类

1.4 DBMS（数据库管理系统）

1.5 MySQL

2、IDEA数据库操作

2.1 IDEA链接数据库使用

2.2 查看所有数据库

2.3 创建一个数据库

2.4 创建一个数据表

2.5 对数据库表进行查询

2.6 写SQL

3、IDEA数据库操作

3.1 MySQL连接

3.2 操作数据库

3.3 数据库的列类型

3.4 数据库的字段属性

3.5 数据库表的类型 

3.6 创建数据库表

3.7 外键

4、DML语言（数据操作语言）

4.1 添加

4.2 修改

4.3 删除

5、DQL语言（数据查询语言）

5.1 指定查询字段

5.2 去重（distinct）

5.3 数据库的列（表达式）

6、SELECT语法（DQL）

6.1 where条件子句

6.2 模糊查询

6.3 联表查询

6.3.1 JOIN对比

6.3.2 自连接

6.3.3 分页和排序

6.3.4 子查询

6.4 MySQL常用函数

6.4.1 常用函数

6.4.2 聚合函数

6.5 数据库级别的MD5加密

7、事务

7.1 什么是事务

7.2 事务的原则

7.3 事务原理

7.4 一个事务的实例

8、MySQL索引

8.1 索引的分类

8.2 基础语法

9、权限管理和备份

9.1 权限管理

9.2 MySQL备份

9.3 扩展：数据库的三大范式

10、JDBC（数据库连接）

10.1 数据库驱动

10.2 JDBC

10.3 第一个JDBC程序

10.3.1 导入数据库驱动

10.4 MySQL及SQL注入

10.5 Statement对象

10.5.1 提取工具类

10.5.2 举例：编写增加方法

10.6 PreparedStatement对象

10.6.1 编写增加方法

10.6.2 编写删除方法

10.6.3 编写修改方法

10.6.4 编写查询方法

10.7 数据库连接池

---

1、初始MySQL

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

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

MySQL支持大型的数据库，可以处理拥有上千万条记录的大型数据库。

MySQL 可以运行于多个系统上，并且支持多种语言。比如常见的编程语言C、C++、Python、Java等等一系列语言。

它作用在前端、后台、可连接前端。

数据库的作用：存数据，取数据。

1.1 为什么要学习数据库？

1，数据库是所有软件体系中最核心的存在。

2，因为现在是一个大数据时代，而数据库的作用是整个软件行业不可缺少的部分，在大数据时代，我们需要一个存取数据的地方，就算没有数据库也会有一个变量来存储数据，或者是另一个与数据库类似的会被创建出来，所以也就有了现在的数据库。

1.2 什么是数据库？

数据库（简称：DB，全称： DataBase）是按照数据结构来组织、存储和管理数据的仓库。

概念：

每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。

我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。

所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理大数据量。

数据库是一个按数据结构来存储和管理数据的计算机软件系统，它是安装在操作系统之上的。

作用：存数据，操作数据。一个数据库可以管理多个网站的数据。

SQL：是操作数据库的语句，可以存储大量的数据(属于控制语句)。

1.3 数据库分类

1，关系型数据库：行，列（SQL）

所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

• MySQL、Oracle、Sql Server、BD2、SQLlite
• 它们是通过表和表之间， 行和列之间的关系进行数据的存储。

2，非关系型数据库：（NoSQL）(No:not only不仅仅是SQL)

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

{key,value}：

• 键值对存储(key,value)：代表软件Redis，它的优点能够进行数据的快速查询，然而缺点就是数据库的功能有局限性。

1.4 DBMS（数据库管理系统）

数据库管理系统(Database Management System)

概念：是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称（DBMS）

主要就是数据库的管理软件。维护和获取。

• 作用：它可以科学有效的替我们管理数据，提供便利。

DBMS术语：

 在我们学习MySQL数据库之前，先了解一些DBMS术语。

• 数据库: 数据库是一些关联表的集合。
• 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
• 列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。
• 行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。

• 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
• 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
• 外键：外键用于关联两个表。
• 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
• 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。

1.5 MySQL

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

这种所谓的"关系型"可以理解为"表格"的概念, 一个关系型数据库由一个或数个表格组成。

它是开源数据库的软件。

安装MySQL：

MySQL 下载安装_长生的梦呓-CSDN博客

2、IDEA数据库操作

博主使用的是IDEA 全称 IntelliJ IDEA：Java开发工具。

2.1 IDEA链接数据库使用

idea可以链接很多数据库进行 操作，因为jetbrains公司把数据连接工具datagrip给做成 插件集成到idea了。

提示：如果idea右上角没有数据库，那么就点击左下角的一个电脑图标。​​

 这边输入地址，localhost代表127.x.x.x也就是本地。用户面就是mysql的用户密码。

​​

 如果少驱动，需要下载驱动。

​​

2.2 查看所有数据库

mysql中把数据库称之为架构schema。

​​

 mysql默认就有几个数据库。用于存放一些默认的信息。

2.3 创建一个数据库

可视化创建数据库。

​​

2.4 创建一个数据表

可视化创建数据表。

​​

 这边可以直接点点就创建好表的字段。下面有相关的键表字段时候的sql。

​​

2.5 对数据库表进行查询

这边可以双击user直接打开数据表，或者按F4。

​​

 打开数据表之后点击+号可以添加数据。数据添加好了之后一定要点绿色箭头submit推送到数据中。这样才会保存数据！

​​

2.6 写SQL

这边可以打开控制台写sql。

​​

 写完sql后可以点击绿色箭头执行，或者ctrl+enter快捷键选中执行。

​​

3、IDEA数据库操作

IDEA内操做是与MySQL里面写的是一样的。

3.1 MySQL连接

使用mysql二进制方式连接。

我们可以使用MySQL二进制方式进入到mysql命令提示符下连接MySQL数据库。

进入命令提示符：

mysql -uroot -p123456   ：连接数据库 u（账户）p（密码）

update mysql.user set authentication_string=password('123456')where user='root' and Host =

'localhost';    修改用户密码。

flush privileges;    刷新权限。

所有的语句都使用 ; 结尾。

show databases;    查看所有的数据库。

mysql>use school      切换数据库。use  +  数据库名

show tables;     查看数据库中所有的表。

describe student;   显示数据库中所有的表信息。student（数据库表名）

create database westos;   创建一个数据库。

exit;   退出连接。

SQL的本来的注释：

-- SQL单行注释
/* 
（SQL的多行注释）
hello
hi

*/

3.2 操作数据库

操作数据库：database

操作数据库的表：table （同样的方法，把database换成table就可以对表进行操作）

操作数据库表的数据（字段）：字段

注意点：mysql中的关键字不区分大小写。

1，创建数据库

create database IF NOT EXISTS school; -- 创建一个school，（IF NOT EXISTS）如果不存在school库就创建，存在就不创建。
IF NOT EXISTS  --解决已经有school数据库存在的异常，因为在有school数据库的情况下，再次创建就会出现异常。

2，删除数据库

drop database IF EXISTS hello;  -- 移除hello数据库，该数据不存在会报错。

3，使用数据库 use 切换到指定数据库

use ` school `;  -- 如果你的表名或字段名带有特殊符号，就要加  ` ` 这个符号
select  --查询信息

4，查看数据库

show databases -- 查看所有数据库

5，对比SQLyog可视化操作：

select *
from student;
create database IF NOT EXISTS school;
drop database IF EXISTS hello;
create database test;
drop database test;
use school;
show databases;

3.3 数据库的列类型

1，数值列类型

列类型	列类型介绍	列类型大小
tinyint	十分小的数据	1个字节
smallint	较小的数据	2个字节
mediumint	中等大小的数据	3个字节
int	标准的整数	4个字节 （常用）
bigint	较大的数据	8个字节
float	浮点数	4个字节
double	浮点数	8个字节
decimal	字符串形式的浮点数	在金融计算的时候，一般是使用decimal

 2，字符串列类型

列类型	列类型介绍	列类型大小
char	字符串固定大小	0~255
varchar	可变字符串	0~65535 <br>常用的变量 String
tinytext	微型文本	2^8-1
text	文本串	2^16-1 用于保存大的文本

 3，时间日期列类型

java.util.Date

列类型	列类型规范	列类型介绍
date	YYYY-MM-DD	日期格式
time	HH：mm：ss	时间格式
datetime	YYYY-MM-DD HH：mm：ss	最常用的时间格式
timestamp	时间戳，1970.1.1到现在的毫秒数	也较为常用
year	年份表示

4，null列类型：没有值，它是未知的。

NULL不能进行运算，因为结果就是NULL。

3.4 数据库的字段属性

Unsigned：

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

zerofill：

• 0填充。
• 不足的位数，使用0来填充。列如： int(3) , 5 那么返回结果就是005，因为3前面没有定义1和2，所以被0填充了。

自增：

• 自动在上一条记录的基础上+1。
• 通常用来设计唯一的主键，index，它必须是整数类型。
• 可以自定义设计主键的自增的起始值和步长。

非空NULL not null：

• 使用了它，如果不给它赋值，就会报错。
• NULL，如果不填写值，默认就是null。

3.5 数据库表的类型 

1，MYISAM 和 INNODB

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约为2倍

2，它的常规操作：

• MYISAM：节约空间，速度较快。
• INNODB：安全性高，更好用于事务的处理，多表多用户操作。

3，MYISAM 和 INNODB在物理空间存在的位置：

所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库，但本质还是文件的存储。

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

• INNODB：在数据库中只有一个*.frm文件，以及上级目录下的ibdata1文件。
• MYISAM：对应文件

*.frm 表结构的定义文件。

*.MYD 数据文件（data）。

*.MYI 索引文件（index）。

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

CHARSET=utf8

为什么要设置？

因为不设置的话，会是MySQL默认的字符集编码。（它不支持中文）

MySQL的默认编码是Latin1，是不支持中文的。

在my.ini中配置默认的编码：​​

3.6 创建数据库表

鼠标选中要执行的段落，进行执行。也可以选中后按住Ctrl + Enter执行。

1，创建数据库表：

-- 注意点：使用英文 () ，表的名称尽量用`` 括号起来，因为你也不知道什么时候它会变成一个关键字。
-- NOT NULL ID必须不为空
-- AUTO_INCREMENT 能自增的单词
-- COMMENT 注释
-- 字符串使用单引号 '' 括起来
-- 所有语句后面加, (英文的逗号) 最后一个字段不用加
-- PRIMARY KEY 主键，一般一个表只有一个，唯一的
create table if not exists `student`(
    `id` INT(4) NOT NULL  AUTO_INCREMENT COMMENT '学号',
    `name` VARCHAR(30) NOT NULL DEFAULT '张三' COMMENT '姓名',
    `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
    `SEX` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
    `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
    `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
    `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET =UTF8

格式： 

​​

常用命令：

SHOW CREATE DATABASE school; -- 查看创建数据库的语句
SHOW CREATE TABLE STUDENT; -- 查看student创建表的定义语句
DESC student -- 查看表的结构

2，修改删除表：

-- 修改表的名称 RENAME 重命名 AS 表名
ALTER TABLE STUDENT RENAME AS STUDENT1;
-- 增加表的字段 ADD 字段名 + 列属性
ALTER TABLE STUDENT1 ADD age int(11);
-- 修改表的字段（重命名 ，MODIFY修改约束！）
ALTER TABLE STUDENT1 MODIFY age1 varchar(11); -- 修改约束
-- CHANGE更改 age(旧名字) AGE1（新名字）列属性
ALTER TABLE STUDENT1 CHANGE age AGE1 INT(1); -- 字段重命名
-- 删除表的字段  表名  DROP 字段名
ALTER TABLE STUDENT1 DROP AGE1

3.7 外键

1，外键（FK）是用于建立和加强两个表数据之间的链接的一列或多列。通过将保存表中主键值的一列或多列添加到另一个表中，可创建两个表之间的链接。这个列就成为第二个表的外键。

2，通过学生表的gradeid字段，取引用年级表的gradeid：

定义外键key：

给这个外键添加约束（执行引用）references引用：

方式一：

create table `grade`(
    `gradeid` int(10) not null auto_increment comment '学生的年级', -- 年级表字段
    `gradename` varchar(10) not null comment '表单名字',
     primary key (`gradeid`)
)engine =innodb default charset =utf8;

create table if not exists `student`(
    `id` INT(4) NOT NULL  AUTO_INCREMENT COMMENT '学号',
    `name` VARCHAR(30) NOT NULL DEFAULT '张三' COMMENT '姓名',
    `gradeid` int(10) not null comment '学生的年级', -- 学生表字段
    `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
    `SEX` 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`),
    KEY `FK_gradeid`(`gradeid`),  -- 方式1
    CONSTRAINT `FK_gradeid` foreign key (`gradeid`)references `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET =utf8;

方式二：

在表创建好的前提下（没有外键关系），去创建外键。

-- 创建表的时候没有外键关系
alter table `student`  -- 方式2  修改这个表，外键  添加约束，约束名，通过外部的键去参考哪个表（哪个字段）
add constraint `FK_gradeid` foreign key (`gradeid`) references `grade`(`gradeid`);
-- alter table 表 add constraint 约束名 foreign key （作为外键的列）references 哪个表（哪个字段）

3,外键的三种操作：

一对一操作方式：比方说一个汉堡只能对应一块牛排，只能加一块牛排。

一对多操作方式：比方说一块汉堡可以对应多块牛排，可以加多块牛排。

多对多操作方式：多个汉堡，多个汉堡一对一操作的情况。

4、DML语言（数据操作语言）

数据库存在的意义：存取数据，数据的管理。

4.1 添加

语法：

insert into 表名(字段名) values (字段值)  -- insert into     ,  values

insert into `stdent`(`id`)values (1231),(47865);  -- 可以同时插入多个值

注意事项：

1，字段和字段之间使用英文逗号 , 隔开。

2，字段可以省略，但是后面的值必须要一一对应，不可以少。

3，可以同时插入多条数据，VALUES后面的值，需要使用 , 隔开 VALUES(),(),...

4.2 修改

1，where 条件判断语句（如果正确，就继续执行。）。

2，判断符号：= < > ! = ≤ ≥ between..and(在某个闭区间之内修改值) ...

判断单词	判断范围	判断规范	返回结果
bewween...and..	在某个范围内（或区间内）	比如：[2,5]
and	我和你	5>1 and 1>2	false
or	我或你	1 or 2	true

update  修改谁  （条件）  set原来的值=新值 
-- 修改学院的名字，带了简介
update `table_name` set `name`='李四' where id=1;-- id等于1，就更改name的值
-- 不指定条件的情况下，会改变所有的表，
update `table_name` set `name`='李四'
-- UPDATE 表名 set colnum_name=value   where 条件
and && 语句 如果name相等and（和） id也相等，那么就为true 执行命令
-- 李四是要修改的新值
update `test` set `name`='李四' ,`pwd`='123456' where `name`='张三' and id=2;-- 通过多个条件定位数据

3，注意事项：

1，name是数据库是的列，尽量带上 符号 。

2，条件：如果没有指定筛选的条件，那么就会修改所有的列。

3，多个设置的字段之间使用 , 英文逗号隔开。

4.3 删除

1，delete删除命令

语法：delete from 表名[where条件]

在数据库中，字段就是列。

-- 删除数据（避免这么写，因为会删掉该表的所有字段）
delete from `test`;
-- 删除指定数据
delete from `test` where id=2

2，truncate删除命令

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

truncate `test`  -- 清空一个表

3，delete 和 truncate的区别

delete from `text`where coll>=1; -- 不会影响自增
truncate table `text` -- 自增会归零

4，DELETE删除问题：重启数据库现象。

INNODB：自增列会从1开始（存在内存中的数据，断电了就会失去）。

MYISAM：继续从上一个自增量开始（存在文件中的数据，断电了不会丢失）。

5、DQL语言（数据查询语言）

概念：

• DQL（Data QueryLanguage）： 数据查询语言
• 所有的查询操作都用它

5.1 指定查询字段

语法：select...字段from表

AS：放在字段后面，可以给字段取一个别名。

concat()：函数 拼接字符串Concat(a,b)

-- 查询所有表的结构  select 字段 from 表
select * from text;
-- 查询指定字段
select `id`,`coll` from text;
-- 别名，给结果取一个名字 AS  可以给字段取别名，也可以给表取别名
select `id`AS 学号,`coll` AS 电话 from text AS S;
-- 函数 拼接字符串Concat(a,b)
select concat('电话：',coll) AS 新名字 from text

5.2 去重（distinct）

作用：查询select中重复的数据，并去除掉重复的数据，只显示一条。

select *  from text;-- 查询学生id和电话 （查询所有字段）
select `coll` from text;-- 查询哪些同学有电话
select distinct `coll` from text;-- 发现重复数据，去重（distinct）

5.3 数据库的列（表达式）

select VERSION();-- 查看系统版本（相当于函数）
select 100*3-123 AS 计算结果;-- 用来计算（相当于表达式）
select @@auto_increment_increment; -- 查询自增的步长（相当于变量）
-- 所有人的电话加个数
select `id`,`coll`+1 AS '电话加1后' from text;

6、SELECT语法（DQL）

SELECT语法是数据库中最核心的语言，最重要的语句。

它也是使用频率最高的语句。

注意点：SELECT语法的顺序很重要。

select distinct * from `text`  -- distinct（去重）
[left join | right join |inner join `name`] -- 联合查询 
[on] -- 等值判断
[where ...] -- 指定字段满足的条件
[group by ...] -- 指定结果按照哪个字段来分组
[having] -- 过滤分组的记录，必须满足的次要条件。条件和where是一样的，只是位置不同
[order by ...] -- 指定查询记录一个或多个条件排序（升序或降序）
[limit] startindex,pagesize  -- 指定查询记录从哪条至哪条 
-- limit 开始下标 ， 页面大小

6.1 where条件子句

作用：检索数据中符合条件的值（运算符的使用）

查询：and

模糊查询：between 值 and 值 之间

不等于：! = 或 not

select `id`,`coll` from text;
-- 查询id在2~4之间的电话   and = &&
select `id`,`coll` from text
where `id` >=2 and `id` <=4;
-- 模糊查询（区间范围） between..and..
select `id`,`coll` from text;
select `id`,`coll` from text
where `id` between 2 and 4;
-- 查询id不等于1的coll
select `id`,`coll` from text  -- ！=
where `id` !=2;
select `id`,`coll` from text -- not
where not `id` =1;

注意点：添加多个判断时，需要使用add不可以重复使用where。

6.2 模糊查询

比较运算符：

运算符	语法	描述
is null	a is null	如果操作符为null，结果为真
is not null	a is not null	如果操作符不为null，结果为真
between	a between b and c	若a在b和c之间，则结果为真
like	a like b	SQL匹配，如果a匹配b，则结果为真
in	a in (a1,a2,a3...)	假设a在a1，或者a2...其中的某一个值中，结果为真

-- ===============         where      ============
-- 查询王开头的字
-- like 结合 %（代表0到任意个字符） _ （一个字符）
select * from text;
select id, coll,  age,app,name from text
where name like '刘%';
-- 查询王开头的字 名字后面只有一个字的
select id, coll,  age,app,name from text
where name like '刘_';
-- 查询王开头的字 名字后面只有两个个字的
select id, coll,  age,app,name from text
where name like '刘__';
-- 查询名字中间有七的同学
select id, coll,  age,app,name from text
where name like '%七%';

-- ===============         in(具体的一个或者多个值)      ============
-- 查询2，3，4学员的信息
select id,name from text
where id  in (2,4,3);
-- 查询在打王者荣耀的学生
select id,name from text
where app in ('王者荣耀');

-- ===============         null not null      ============
-- 查询名字为空的同学 null（空是指空字符串）
select id,app from text
where name =' '   or name is null;
-- 查询名字不为空的同学
select id,app from text
where name is not null;
-- 查询没有名字的同学
select id,app from text
where name is  null;

6.3 联表查询

6.3.1 JOIN对比

结构图：

​​

三种查询方式：

LeftJoin：左表查询

inner join：查询共同拥有的值

Right Join：右表查询

怎么分左右表呢？

在from指引的后面就是左表，在Join后面的就是右表。

注意点：

1，分析需求，分析查询的字段来自哪些表，超过了一张表就要连接查询 。

2，确定使用哪种连接查询？一种7中（但本质上只有三种，其他查询的为null）。

3，确定一个交叉表（这两个表中哪个数据是相同的）。

-- ===============      联表查询 join      ============
-- 查询有电话的同学（id，coll，app，age，pwd）
select *
from `text`;
select *
from `test`;
-- 判断条件：text中的id=test中的id（两者的字段相等，那么就进行查询，并打印出结构图）
select S.id, coll, app, age, pwd
from text AS S
inner join test AS R  -- inner join 查询到共同拥有的值
where S.id = R.id;
-- right join   可以查到右边所有id的值
select s.id, coll, app, age, pwd
from text AS s
right join test AS r
on s.id = r.id;
-- left join  可以查到左边所有id的值
select s.id, coll, app, age, pwd
from text AS s
left join test AS r
on s.id = r.id;

-- join（连接的表） on（条件判断） 语法查询（连接查询） 
-- join where 等值查询 

6.3.2 自连接

自连接：自己的表和自己的表连接。

核心：一张表拆为两张一样的表即可。

--  ==========  自连接 ==========
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;

-- 查询父子信息:把一张表看为两个一模一样的表
select a.`categoryname` AS '父栏目',b.`categoryname` AS '子栏目'
from `category` as a , `category` as b
where a.`categoryid`=b.`pid`    
-- 1是最高级，所以1变成了categoryid的最高级，所以与pid相对应，由大到小依次对应（没有的数字默认为空）

查询结果对比：

​​

6.3.3 分页和排序

1，排序：order by

升序：ASC

降序：DESC

语法：order by（通过哪个字段排序，怎么排）

-- 查询结果，根据年龄降序排序
select * from text
order by age DESC; -- 通过降序给年龄排序
select * from text
order by age ASC -- 通过升序给年龄排序

2，分页：limit

语法：limit（查询起始下标，pageSize）

pageSize：页面大小

为什么要分页？

分页可以缓解数据库的压力，给人一种好的体验。

扩展：瀑布流查询（可以无限向下刷新的数据，列如微信朋友圈就是瀑布流查询。）

分页公式：

limit 0,3 第一页 （1-1）*3

limit 3,3 第二页 （2-1）*3

limit 6,3 第三页 （3-1）*3

limit N,3 第N页 （n-1）*pageSize，pageSize

（n-1）*pageSize（起始值，n代表当前页）

总结：数据总数/页面总数=总页数

-- 分页，每页值显示3条数据
select *from text
limit 6,3

6.3.4 子查询

在where 语句中 and 添加一个子查询。

-- 子查询
select a.id,app,pwd from text as a
inner join test as b
on a.id = b.id
where a.id <10 and app in (
    select app from text
    where app = '王者荣耀'  -- 我通过子查询查询到text中app等于王者荣耀的数据
    )
    

6.4 MySQL常用函数

6.4.1 常用函数

-- =========  常用函数  ===========
-- 数学运算
select ABS (-8); -- 绝对值
select ceiling(9.4);  -- 向上取整
select floor(9.4);  -- 向下取整
select rand();  -- 返回0到1之间的随机数
select sign(0);  -- 判断一个数的符号  负数返回-1，整数返回1，0返回零
-- 字符串
select char_length('一二三四五');  -- 返回字符串长度
select concat('天天','学习'); -- 拼接字符串
select insert('二十四月',1,2,'超级热爱');  -- 插入，从某个位置开始替换某个长度
select lower('ZHANGSAN'); -- 全部转化为小写
select upper('zhangsan'); -- 全部转换为大写
select instr('zhangsan','g'); -- 返回第一次出现字串的索引
select replace('坚持就能成功','坚持','奋斗'); -- 替换出现的指定字符串
select substring('床前明月光',2,3); -- 返回指定的字符串 (原字符串，截取的位置，截取的长度)
select reverse('床前明月光'); -- 反转

-- 查询性刘的同学，将名字换成李
select replace(name,'刘','李') from text
where name like '刘%';

-- ======    时间和日期函数   =====
select current_date();  -- 获取当前日期
select now(); -- 获取当前时间
select localtime(); -- 获取本地时间
select sysdate(); -- 获取系统时间
select year(now());-- 获取本年（需要在里面设置一个现在）
-- 系统
select system_user(); -- 系统
select user();
select VERSION(); -- 当前版本

6.4.2 聚合函数

比较常用的：

count()：计数

sum()：求和

avg()：平均值

max()：最大值

min()：最小值

-- ========  聚合函数  ==========
-- 都能统计表中的数据（想查询这个表中有多少条记录，就可以用count() ）
select count(name) from text; -- count(指定列)，会忽略所有的null值
select count(*) from text; -- count(*)，不会忽略所有的null值
select count(1) from text; -- count(*)，不会忽略所有的null值

select sum(`age`) AS 总和 from text;
select avg(`age`) AS 平均分 from text;
select max(`age`) AS 最高分 from text;
select min(`age`) AS 最低分 from text;

-- 查询课程的平均分，最高分，最低分,平均分大于80的
-- 核心：根据不同的课程分组
select a.id,avg(b.`sub`) as 平均分,max(a.`sub`) as 最高分,min(a.`sub1`)  as 最低分 from text a
inner join test b
on a.id = b.id
group by a.id
having 平均分>80

6.5 数据库级别的MD5加密

MD5信息摘要算法（英语：MD5 Message-Digest Algorithm）

概念：一种被广泛使用的密码散列函数，可以产生出一个128位（16字节）的散列值（hash value），用于确保信息传输完整一致。

主要增强算法复杂度和不可逆性。

create table `testmd5`(
    `id` int(4) not null,
    `name` varchar(10) not null ,
    `pwd` varchar(10) not null,
     primary key(`id`)
)engine = innodb  default charset =utf8;

-- 明文密码
insert into testmd5 values(1,'张三','123456 '),(2,'李四','654321'),(3,'刘大','123789 ');

-- 加密
UPDATE testmd5 SET PWD =MD5(PWD) WHERE id=1;
UPDATE testmd5 SET PWD =MD5(PWD);-- 加密全部的密码
-- 插入的时候加密
insert into testmd5 values (4,'zhangsan',md5('123456'));
-- 如何校验：将用户传递进来的密码，进行md5加密，然后比对加密后的值
SELECT * FROM testmd5 WHERE `NAME`='ZHANGSAN' AND PWD = MD5('123456') -- 只能查询到MD5加密过一次的密码

7、事务

7.1 什么是事务

概念：要么都成功，要么都失败。

操作：将一组sql放在同一批次中执行。

7.2 事务的原则

四大原则：

原子性（Atomicity）：要么都成功，要么都失败。

一致性（Consistency）：事务前后的数据完整性要保证一致。（执行前是1000，那么执行后这1000是不可变的）

持久性（Durability）：事务一旦提交则不可逆，它会被持久化到数据库中。

隔离性（Lsolation）：事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，它不能被其他事务的操作数据所干扰，所以事务之间要相互隔离。

隔离后会导致一些问题：

脏读：指一个事务读取了另一个事务未提交的数据。

不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，可能只是某些场合不对）

虚读（或称为：幻读）：是指在一个事务内读取了别的事务插入的数据，导致前后读取不一致。

7.3 事务原理

手动处理事务：

关闭自动提交—>事务开启(标记一个事务开始)—>进行操作—>事务提交（或事务回滚）—>事务结束

-- ==============   事务   ================
-- mysql 是默认开启事务自动提交的
set autocommit =0;  /*关闭*/
set autocommit =1;  /*开启(默认的)*/

-- 手动处理事务
set autocommit =0;-- 关闭自动提交
-- 事务开启
start transaction; -- 标记一个事务的开始，从这个开始之后的sql都在用一个事务内
INSERT XXX -- 事务操作
INSERT XXX -- 事务操作

-- 提交：持久化  （成功就提交）
commit;
-- 回滚：回到原来的样子  （失败就回滚）
rollback;

-- 事务结束
set autocommit =1; -- 事务结束后开启自动提交

SAVEPOINT 保存点名; -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名; -- 回滚到保存点
RELEASE SAVEPOINT 保存点名  -- 撤销保存点

事务处理过程：

​​

7.4 一个事务的实例

idea中操作事务不会立即显示出结果，而是在进行事务操作后，commit提交事务后，才会显示出结果。

-- 转账
create database shop;
use shop;
create table `account`(
    `id` INT(3) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(30) NOT NULL ,
    `money` DECIMAL(9,2) NOT NULL,
     PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET =UTF8;

INSERT INTO account(`NAME`,`MONEY`) VALUES ('A',2000.00),('B',10000.00);
-- 模拟转账：事务
SET AUTOCOMMIT =0;-- 关闭自动提交
start transaction ; -- 开启一个事务
update account set money=money-500 where `id`='1'; -- id=1减500
update account set money=money+500 where `id`='2' ;-- id=2加500
commit;-- 提交事务  就会被持久化
rollback; -- 回滚
SET AUTOCOMMIT =1; -- 恢复默认值

8、MySQL索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆法拉利的话，那么没有设计和使用索引的MySQL就是一辆人力三轮车。

8.1 索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个。

主键索引（PRIMARY KEY）：唯一的标识，主键不可重复，只能有一个列作为主键。

唯一索引（UNIQUE KEY）：避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引。

常规索引（KEY/INDEX）：默认的，index , key 关键字来设置。

全文索引（FullText）：在特定的数据库引擎下才有，MyISAM。

全文索引的功能：快速定位数据。

8.2 基础语法

索引的使用：

1，创建表的时候给字段增加索引。

2，创建完毕后，增加索引。

-- 显示所有的索引信息
show index from student
-- 增加一个全文索引（索引名）列名
alter table school.student add fulltext index `studentName`(`studentName`)
-- exolain 分析sql执行的情况
explain select * from student; -- 非全文索引
explain select * from student where match(studentName) against('刘');

9、权限管理和备份

9.1 权限管理

增：create

删：drop

改：set

查：show

-- 创建用户   create user '用户名' IDENTIFIED BY '密码'
create user 'modong' IDENTIFIED BY '123456';

-- 修改密码（修改当前用户）  提示：练习后要记得更改回原来的密码
set password=password ('123456');
-- 修改密码（修改指定用户）   提示：练习后要记得更改回原来的密码
set password for modong =password ('123456');

-- 重命名   RENAME USER 原用户名 TO 新用户名
RENAME USER modong TO modong2;

-- 用户授权 ALL PRIVILEGES,全部的权限，库，表
-- ALL PRIVILEGES 除了给其他用户授权，其他都可以做
GRANT ALL PRIVILEGES ON *.* TO modong2;

-- 查询权限
SHOW GRANTS FOR modong2;  -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost;
-- root用户的权限： GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
-- 撤销权限 REVOKE 哪些权限，在哪个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM modong2;

-- 删除用户
DROP USER modong2;

9.2 MySQL备份

为什么要备份：防止数据丢失，方便数据转移。

（一）可以直接导出到文件。

直接在数据库中右键导出即可。

（二）使用cmd命令行导出。

导入：在登录的情况下，切换到指定的数据库。

mysqldump -h 主机（localhost） -u 用户名（root） -p 密码（123456） 数据库 表名 >物理磁盘位置/文件名

​​

注意点：要导出数据库的时候就只用写数据库就好了，要导出表的时候，就数据库+表名即可。

source：备份文件

​​

9.3 扩展：数据库的三大范式

1，为什么要数据规范化？

• 信息重复
• 更新异常
• 插入异常：无法正常显示信息
• 删除异常：丢失有效的数据

2，三大范式：

第一范式（1NF）

原子性：保证每一列不可再分。

第二范式 (2NF)

前提：满足第一范式

每一张表指描述一件事情。

第三范式 (3NF)

前提：满足第一范式和第二范式

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

10、JDBC（数据库连接）

10.1 数据库驱动

数据库驱动是不同数据开发商为了某一种开发语言环境能够实现统一的数据库调用而开发的一个程序，它的作用相当于一个翻译人员，将Java语言中对数据库的调用语言通过这个翻译翻译成各个种类的数据库自己的数据库语言当然这个翻译（数据库驱动）是由各个开发商针对统一的接口自定义开发的。

参考：

什么是数据库驱动？-himrpei-ChinaUnix博客

没有驱动，显卡就不会有那么高的性能。

我们程序会通过数据库驱动，和数据库打交道。

关系图：

​​

10.2 JDBC

SUN公司为了简化开发人员的（对数据库的统一）操作，提供了一个规范（Java操控数据库的规范：俗称JDBC），这些规范的实现由具体的厂商去做。

对于开发人员来说，我们只需要掌握JDBC接口的操做即可。

我们需要了解两个包：java.sql ~ javax.sql。

我们对数据库的连接还需要导入一个数据库驱动包。

根据自己的sql版本下载。

下载连接：Maven Repository: mysql » mysql-connector-java (mvnrepository.com)

下载数据库驱动包后，这时候我们开发人员只用管理JDBC即可。

关系图：

10.3 第一个JDBC程序

创建测试数据库：

以下代码在数据库中创建。

CREATE DATABASE jdbcStudy;
use jdbcStudy;
create table users(
    `id` int primary key ,
    `name` varchar(40),
    `PASSWORD` varchar(40),
    `email` varchar(60),
    `birthday` DATE
);

insert into users (id,name,password,email,birthday)
values (1,'zhangsan','123456','zs@sina.com','2020-12-20'),
       (2,'zhaoyiyi','123456','zy@sina.com','2020-9-20'),
       (3,'lisi','123456','ls@sina.com','2020-8-20');

10.3.1 导入数据库驱动

（1）创建一个项目。

（2）导入数据库驱动。

点击lib目录，右键打开，将此包添加到库里面，点击确认。

​​

 （3）编写测试代码

package com.lei.lesson01;

import java.sql.*;

// 我的第一个JDBC程序
public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1，加载驱动
        Class.forName("com.mysql.jdbc.Driver"); // 固定写法，加载驱动

        // 2，用户信息和url              
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useSSL=false&serverTimezone=Hongkong&characterEncoding=utf-8&autoReconnect=true";
        String username = "root";
        String password = "123456";
        // 3，连接成功，返回数据库对象   Connection 代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);

        // 4，执行sql的对象   Statement执行sql的对象
        Statement statement = connection.createStatement();

        // 5，执行sql对象 去 执行sql，可能存在结果，我们要查看连接结果
        String sql = "SELECT * FROM users;";

        ResultSet resultSet = statement.executeQuery(sql); // 返回的结果集，结果集中封装了我们全部的查询出来的结果

        while (resultSet.next()){
            System.out.println("id="+resultSet.getObject("id"));
            System.out.println("name="+resultSet.getObject("NAME"));
            System.out.println("password="+resultSet.getObject("PASSWORD"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("birthday="+resultSet.getObject("birthday"));
            System.out.println("=======================");
        }

        // 6,释放连接
        resultSet.close();
        statement.close();
        connection.close();

    }

}

数据库连接失败的解决方式：IDEA配置数据库连接失败的问题_zuixiaoyao_001的博客-CSDN博客

10.4 MySQL及SQL注入

如果您通过网页获取用户输入的数据并将其插入一个MySQL数据库，那么就有可能发生SQL注入安全的问题。

所谓SQL注入，就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。

我们永远不要信任用户的输入，我们必须认定用户输入的数据都是不安全的，我们都需要对用户输入的数据进行过滤处理。

SQL注入及防范措施参考：Sql注入详解及防范方法_wodetian1225的博客-CSDN博客_sql注入防范措施

10.5 Statement对象

Jdbc中的Statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查即可。

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

Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的ResultSet对象。

CRUD操作-create

使用executeUpdate（String sql）方法完成数据添加操作。

Statement st = conn.createStatement();
String sql = "insert into user(...)values(...)";
int num = st.executeUpdate(sql);
if(num>0){
    System.out.println("插入成功");
}

CRUD操作-delete

使用executeUpdate（String sql）方法完成数据删除操作。

Statement st = conn.createStatement();
String sql = "delete from user where id=1";
int num = st.executeUpdate(sql);
if(num>0){
    System.out.println("删除成功");
}

CRUD操作-update

使用executeUpdate（String sql）方法完成数据修改操作。

Statement st = conn.createStatement();
String sql = "update user set name='' where name=''";
int num = st.executeUpdate(sql);
if(num>0){
    System.out.println("修改成功");
}

CRUD操作-read

使用executeUpdate（String sql）方法完成数据查询操作。

Statement st = conn.createStatement();
String sql = "select * from user where id=1";
ResultSet rs = st.executeUpdate(sql);
while(rs.next()){
// 根据获取列的数据类型，分别调用rs的相应方法映射到Java对象中
}

10.5.1 提取工具类

private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;

static {

    try {
        InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("dp.properties");
        Properties properties = new Properties();
        properties.load(in);

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

        // 驱动只用加载一次
        Class.forName(driver);

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

}

// 获取连接
public static Connection getConnection() throws SQLException {
    return DriverManager.getConnection(url, username, password);

}

// 释放连接
public static void release(Connection conn, Statement st, ResultSet rs) {
    if (rs != null) {
        try {
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    if (st != null) {
        try {
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    if (conn != null) {
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

10.5.2 举例：编写增加方法

操作成功后到数据库对应的表查询。

删除，修改也是相同代码，需要修改代码的地方只有在sql语句中。

public static void main(String[] args) {
    Connection conn = null;
    Statement st = null;
    ResultSet rs = null;
    try {
        conn = JdbcUtils.getConnection(); // 获取数据库连接
        st = conn.createStatement(); // 获取sql的执行对象
        // 只能加载一次，加载成功后，再次加载就会出现异常（解决方法，把加载的数据删除，重新加载）
        String sql = "delete from users where id =4";
        int i = st.executeUpdate(sql);
        if (i>0){
            System.out.println("插入成功！");
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        JdbcUtils.release(conn,st,rs);
    }
}

10.6 PreparedStatement对象

java.sql包中的PreparedStatement 接口继承了Statement。

PreparedStatement：可以防止SQL注入，效率更高！

SQL注入、参考：SQL注入百度百科

是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。

10.6.1 编写增加方法

注意点：两个Date不一样。

sql.Date ：数据库 new java.sql.Date()

util.Date ：Java new Date().getTime() 获得时间戳

与Statement中（SQL语句）区别：

使用？占位符代替参数

public static void main(String[] args) {
    Connection conn = null;
    PreparedStatement st = null;

    try {
        conn = JdbcUtils.getConnection();


        String sql = "insert into users(id,NAME,PASSWORD,email,birthday) values(?,?,?,?,?)";
        st = conn.prepareStatement(sql);  // 预编译SQL，先写SQL，然后不执行

        // 手动给参数赋值
        st.setInt(1, 4); // 给id赋值
        st.setString(2, "zhangsan");
        st.setString(3, "123456");
        st.setString(4, "123456@qq.com");
        st.setDate(5, new java.sql.Date(new java.util.Date().getTime()));
        // 执行
        int i = st.executeUpdate();
        if (i > 0) {
            System.out.println("插入成功");
        }

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

10.6.2 编写删除方法

public static void main(String[] args) {
    Connection conn = null;
    PreparedStatement st = null;

    try {
        conn = JdbcUtils.getConnection();

        // 区别
        // 使用？占位符代替参数
        String sql = "delete from users where id=?";
        st = conn.prepareStatement(sql);  // 预编译SQL，先写SQL，然后不执行

        // 手动给参数赋值
        st.setInt(1,4);

        // 执行
        int i = st.executeUpdate();
        if (i > 0) {
            System.out.println("删除成功");
        }

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

10.6.3 编写修改方法

public static void main(String[] args) {
    Connection conn = null;
    PreparedStatement st = null;

    try {
        conn = JdbcUtils.getConnection();

        // 区别
        // 使用？占位符代替参数
        String sql = "update users set `NAME` = ? where id =?;";
        st = conn.prepareStatement(sql);  // 预编译SQL，先写SQL，然后不执行

        // 手动给参数赋值
        st.setString(1, "张三");
        st.setInt(2, 1);

        // 执行
        int i = st.executeUpdate();
        if (i > 0) {
            System.out.println("更新成功");
        }

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

10.6.4 编写查询方法

public static void main(String[] args) {
    Connection conn = null;
    PreparedStatement st = null;
    ResultSet rs = null;
    try {
        conn = JdbcUtils.getConnection();
        String sql = "select * from users where id =? "; // 编写SQL
        st = conn.prepareStatement(sql);// 预编译

        st.setInt(1,2); // 传递参数

        rs = st.executeQuery(); // 执行

        if (rs.next()){
            System.out.println(rs.getString("NAME"));
        }

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

10.7 数据库连接池

原概念：数据库连接—>执行完毕—>释放

连接—>释放过程是十分浪费系统资源的。

现概念：池化技术

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

列如：一个银行服务的过程。

从开门—>业务员：等待—>服务—>等到要关门的时候在关门。（也就是加入了一个业务员的等待过程）

备用连接数：五个

常用连接数：10个

最小连接数：10个

最大连接数：15业务最高乘载上限（因为加上备用的连接数也只有15个，不能超过）

等待超时：100ms

编写连接池：需要实现一个接口DataSource。

它可以直接拿来就用的，与原生代码一样，需要把这些包导入到idea中，并存到库里。

简便了我们写代码的时间，提高了效率，性能。

编写连接池需要实现一个接口 DataSource。

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

常用的数据库连接池：DBCP、C3P0、HikariCP、Druid。

参考：（10）常见的几种数据库连接池的配置和使用_Soup's Blog-CSDN博客_数据库连接池

DBCP：

DBCP通过连接池预先同数据库建立一些连接放在内存中(即连接池中)，应用程序需要建立数据库连接时直接到从接池中申请一个连接使用，用完后由连接池回收该连接，从而达到连接复用，减少资源消耗的目的。

C3P0：

 优点是功能简单易用，稳定性好。 缺点就是性能差，由于其架构设计过于复杂，重构成了一件很难的事。

HikariCP：

HikariCP号称“性能杀手”。

Druid：

中文文档比较齐全，它的优点在于强大的监控功能，可以清楚的知道 连接池和SQL的工作情况，方便扩展。