MySQL——数据库

缠丝猫 · 2024-11-10 21:16:40

什么是数据库

数据库（DB , DataBase）
概念：数据堆栈，软件，安装在操作系统（window，linux，max，...）之上
学习数据库最重要的就是学习SQL语句
存储500万以下的数据，若是500万以上就要优化索引，不然查询速率比力慢
作用：存储数据，管理数据

数据库分类

关系型数据库：（SQL）

MySQL，Oracle，Sql Server，DB2，SQLlite
通过表和表之间，行和列之间的关系进行数据的存储

非关系型数据库：（NoSQL） Not Only SQL

Redis，MongDB
非关系型数据库，对象存储，通过对象自身的属性来决定

DBMS（数据库管理系统）

数据库的管理软件，科学有效的管理我们的数据、维护和获取数据
MySQL本质上是一个数据库管理系统
跟DB的区别就在于：DB仅仅只存储数据，DBMS是用来管理和获取数据

MySQL简介

MySQL是一个关系型数据库管理系统
前世：瑞典MySQL AB公司
此生：属于Oracle旗下产品
MySQL是最好的RDBMS（关系型数据库管理系统）应用软件之一
开源的数据库软件
体积小、速率快、总体成本低
适用于大中小型网站，MySQL能够实现集群

如何卸载MySQL

sc delete mysql

复制代码

执行以上下令可以清空服务

如何新建数据库

字符集：utf8mb4
排序规则：utf8mb4_bin

我这边利用的是Navicat，Navicat->工具->历史日志里面可以检察操作纪录

每一个在navicat中的执行操作，本质上就是一个SQL语句

创建表

输入id、name、age的信息，养成写注释的风俗；id、name、age都属于重要的信息，因此要设置非空；id是主键，设置key；范例、长度都要根据现实情况进行设置

设置完后再点击保存，输入表的名称，就创建好表了

检察表

右键->打开表

添加多条数据（记得保存）

毗连数据库

下令行毗连
// 毗连数据库
  mysql -uroot -p123456

  // 修改密码
  update mysql.user set authentication_string=password('123456) where user='root' and Host ='localhost';

  // 刷新权限
  flush privileges;

  ------------------------------------------------------------------------------
  // 所有的语句都利用分号（；）末端

  // 检察所有的数据库
  show databases;

  // 切换数据库 use 数据库名
  use school

  .
  // 检察数据库中所有的表
  show tables;

  .
  // 检察表中的数据
  desc student;

  .
  // 创建一个数据库
  create database weston;

  .
  // 进行waston的一系列操作

  .
  // 退出毗连
  exit;
  .
  -- 单行注释
  /* 多行注释 */

数据库XXX语言（CRUD）增删改查

DDL——界说
DML——操作
DQL——查询
DCL——控制

操作数据库

操作数据库 > 操作数据库中的表 > 操作表中的数据
MySQL的关键字不区分巨细写
创建数据库

如果创建一个原本就有的数据库，就会报错

但是如果我们一开始并不知道是否存在这个数据库，又不想报错该怎么办？
这个时间就要利用以下代码

删除数据库

如果删除一个不存在的数据表，也会报错

假犹如样不想报错，可以利用以下代码

利用数据库

以上两种样式都可以，加上``是因为不知道school是否是特别字符

如果你的数据库名或者表名或者字段名是一个特别字符，就需要（`数据库名/表名/字段名`）
比方你的字段名是user
这个时间查询语句本应该是 SELECT USER FROM STUDENT;
但是USER会高亮，并且查询不到你需要的信息
这个时间SQL语句就需要改成，SELECT `user` FROM STUDENT;

检察数据库

数据库的列（数据）范例

数值

tinyint 非常小的数据 1个字节
smallint 较小的数据 2个字节
mediumint 中等巨细的数据 3个字节
int 尺度的整数 4个字节常用的
bigint 较大的数据 8个字节
float 浮点数 4个字节
double 浮点数 8个字节（精度问题）
decimal 字符串情势的浮点数金融盘算的时间一般利用decimal

字符串

char 字符串固定巨细的 0~255
varchar 可变字符串 0~65535 常用的变量对应String
tinytext 微型文本 2^8 - 1
text 文本串 2^16 - 1 保存大文本依旧对应String

时间日期

java.util.Data

date YYYY-MM-DD 日期格式
time HH:mm:ss 时间格式
datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
timestamp 时间戳 1970.1.1到现在的毫秒数较为常用
year 年份表示

null

没有值，未知
留意，不要利用NULL进行运算，因为怎么运行都为NULL

数据库的字段属性（重点）

Unsigned（无符号）

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

zerofill（0填充）

0填充的
假设你数组长度为10，但只添加了一个数据，这个时间有后面9位就会用0来填充

自增

通常理解为自动在上一条纪录的根本上 +1（默认）
通常用来设计唯一的主键 index，而且必须是整数范例
可以自界说设计主键自增的起始值和步长（在选项当中）

非空 NULL、NOT NULL

假设设置为NOT NULL，如果不给他赋值，就会报错！
如果设置为NULL，如果不填写值，默认就是NULL！

默认

设置默认值
假设我设置sex（性别）这个字段的默认值为男，如果我不指定该列的值，则该列的值都是男

小结

UNSIGNED（无符号）勾选上就说明填充的数据不能是负数
ZEROFILL（0填充）勾选上就说明会用0补上没有填写上的位数，比方你界说了int范例且长度是5，这个时间你填凑数据的时间只写了1，它就会帮你改写成00001；
NOT NULL（非空）勾选上就说明该列元素不能不填写 ->（除非你勾选上自增，且该列元素的属性是数值范例）
AUTO_INCREMENT（自增）勾选上就说明如果你没有填写该列数据，他就会在你上一行数据的根本上加1填充（都是整数），这种情况下是基于该列元素设置为NOT NULL
DEFAULT（默认）勾选上就可以设置该列元素的默认值，在新增字段的时间该列元素的值就是我们本身设置的默认值，可以修改

数据库中的每一个表都必须存在以下五个字段

id 主键
`version` 乐观锁每一条纪录都有本身对应的版本
is_delete 伪删除这条纪录是否被删除，我们并不盼望这个数据真正的被删掉，我们就用这个标记位来判断，如果这个标记位为1的话，我们就以为它被删掉了，但是数据库中还存在
gmt_create 创建时间这条纪录的创建时间
gmt_update 修改时间这条纪录的修改时间

创建数据库表（重点）

留意点：

表名和字段名最好都加上``，以免出现跟特别字符一样的情况
每个字段最好都设置默认值（DEFAULT '默认值'，默认值可以是NULL，DEFAULT NULL），并给出注释（COMMENT '注释'）
但是要留意一点的就是在设置NOT NULL之后，默认值就不能设置成NULL了
DATETIME不需要设置长度默认值的格式可以是 YYYY-MM-DD HH：mm：ss
PRIMARY KEY是用来设置主键的，一般不将它放在某一字段的后面，不敷一目了然，以是放在建表语句中的末了一行，PRIMARY KEY(字段名)
建表之后可以加上引擎名和字符集 ENGINE=INNODB DEFAULT CHARSET=utf8

检察创建数据库的语句

SHOW CREATE DATABASE 数据库名；

检察创建数据表的语句

SHOW CREATE TABLE 数据表名；

显示数据表的结构

DESC 数据表名；

关于数据库引擎

INNODB —— 默认利用
MYISAM —— 早些年利用的

	MYISAM	INNODB
事件支持	不支持	支持，好比说两个sql语句一起执行，要么全成功要么全失败
数据行锁定	不支持，只支持数据表锁定，适用于需要包管整个表的一致性，比方对整个表进行大批量的更新或者维护	支持，其他事件能够访问表中的其他行，适用于高并发读写，降低锁的争用
外键束缚	不支持，无法实现数据库级别的表跟表的联系	支持
全文索引	支持	以前不支持，现在支持
表空间的巨细	较小	较大，约为MYISAM的两倍

常规利用操作：

MYIASM：节省空间，速率较快
INNODB：安全性高，能够进行事件的处理，有外键束缚使得它能够多表多用户操作

MYSIAM和INNODB在物理空间存在的位置

所有的数据库文件都存在data目次下，一个文件夹对应一个数据库
本质上照旧文件的存储

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

INNODB在数据库表中只有一个*.frm文件（但是mysql8之后都归并在*.ibd文件当中）以及上级目次下的ibdata1文件
MYISAM对应文件
- *.frm —— 表结构的界说文件
- *.MYD —— 数据文件（data）
- *.MYI —— 索引文件（index）

设置数据库的字符集编码

DEFAULT CHARSET=utf8
如果不进行字符集编码的设置的话，会是MySQL默认的字符集编码（Latin1,不支持中文）
设置字符集编码

一种是在创建数据表的时间进行设置

在建表的SQL语句的末端加
在用可视化建表右键->设计表->选项里面设置

另一种是在my.ini中设置默认的编码
character-set-server=utf8
但一般来说，都需要接纳第一种方法进行设置，不然你的数据库拿到其他电脑上大概就不适用了，降低了通用性

修改数据表（ALTER）

修改表名（RENAME AS）

ALTER TABLE 旧的数据表名 RENAME AS 新的数据表名；

增加表的字段（ADD）

ALTER TABLE 数据表名 ADD 新的字段名数据束缚；

修改表的字段（MODIFY、CHANGE）

修改字段的束缚（MODIFY）

ALTER TABLE 数据表名 MODIFY 字段名新的数据束缚；

可以看到，已经修改成功了

既能修改字段的名字，也能修改字段的束缚（CHANGE）

但是利用CHANGE 就需要在重命名的同时加上字段的束缚
ALTER TABLE 数据表名 CHANGE 旧的字段名新的字段名数据束缚；

可以看到，在重命名字段的同时，数据束缚也修改了

删除表的字段

ALTER TABLE 数据表名 DROP 字段名；

删除表（DROP）

DROP TABLE 数据表名；或者 DROP TABLE IF EXITS 数据表名；

MySQL数据管理

外键（相识）

第一种方式

CREATE TABLE IF NOT EXISTS `grade` (
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(20) NOT NULL COMMENT '年级姓名',
PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4
CREATE TABLE IF NOT EXISTS `student` (
`id` INT(3) NOT NULL AUTO_INCREMENT COMMENT '学员id',
`password` INT(10) NOT NULL DEFAULT 123456 COMMENT '密码',
`name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
`birthday` DATETIME DEFAULT '2003-09-08 21:00:00' COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY(`id`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4

复制代码

第二种方式

CREATE TABLE IF NOT EXISTS `grade` (
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(20) NOT NULL COMMENT '年级姓名',
PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4
CREATE TABLE IF NOT EXISTS `student` (
`id` INT(3) NOT NULL AUTO_INCREMENT COMMENT '学员id',
`password` INT(10) NOT NULL DEFAULT 123456 COMMENT '密码',
`name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
`birthday` DATETIME DEFAULT '2003-09-08 21:00:00' COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`);

复制代码

删除有外键关系的表的时间，必须先删除引用别人的表student（从表），再删除被引用的表grade（主表）
以上的操作都是物理外键（数据库级别的外键），不发起利用。（制止数据库太多造成困扰，这里相识即可）

最佳实践

数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
我们想要利用多张表的数据或者说我们想利用外键的时间，这个时间用程序去实现。

DML语言

DML语言：数据操作语言
insert
update
delete

添加（INSERT INTO）

INSERT INTO 数据表名 (字段1,字段2,字段3,...) values ('值1','值2','值3',...);

复制代码

如果不写字段名，它就会一一匹配
比方：

INSERT INTO 数据表名 values ('值');
-- 假设这个表只有两个字段，并且这个值是赋值给第二个字段的
-- 默认就会编译成
INSERT INTO 数据表名 (字段1,字段2) values ('值',null);
-- 这个时候会发现值赋值给第一个字段，如果不符合该字段的数据约束，就会报错，并且也不能满足我们的需求

复制代码

因此，一般写插入语句的时间，肯定要包管数据和字段一一对应

插入多行数据

INSERT INTO 数据表名 (字段名) VALUES ('值1'),('值2'),...;

复制代码

区别好插入多个字段的值和插入多行数据（一个字段的多个数据）的区别

插入一个字段的数据

INSERT INTO `grade` (`gradename`) VALUES ('大三');

复制代码

这里之以是gradeid也有值，是因为我们刚开始建表的时间给该字段添加了自增选项，并且非空

插入一整行数据

INSERT INTO `grade` (`gradeid`,`gradename`) VALUES (1,'大一');
INSERT INTO `grade` (`gradeid`,`gradename`) VALUES (2,'大二');

复制代码

插入一个字段的多个数据

INSERT into `grade` (`gradename`) VALUES ('大四'),('大五'),('大六');

复制代码

插入多个字段的多个数据

INSERT INTO `grade` (`gradeid`,`gradename`) VALUES (11,'一年级'),(12,'二年级'),('13','三年级');

复制代码

留意事项

字段和字段之间利用英文逗号隔开
可以同时插入多条数据，VALUES后面的括号需要利用英文逗号隔开
- 1. INSERT INTO 数据表名 (字段名) VALUES ('值1'),('值2');
  复制代码
字段名是可以省略的，但后面的值必须要一一对应
- 1. INSERT INTO `grade` VALUES (21,'四年级'),(22,'五年级'),(23,'六年级');
  复制代码

修改（UPDATE）

UPDATE 数据表名 SET 字段名='新的值' WHERE 条件判断;

复制代码

初始表格

修改指定条件的某一字段的数据

UPDATE `student` set `name`='lhj' WHERE id=1;

复制代码

不指定条件的修改某一字段的数据

UPDATE `student` SET `name`='fjq';

复制代码

指定条件的修改多个字段的值

UPDATE `student` SET `name`='lhj',`address`='shantou' WHERE id=1;

复制代码

组合条件的修改字段的值

UPDATE `student` SET `name`='zhangsan' WHERE id<>1 AND sex='男';

复制代码

操作符

操作符	含义	范围	结果
=	即是	5 = 6	false
<>或!=	不即是	5 <> 6 （5 != 6）	true
>	大于	5 > 6	false
<	小于	5 < 6	true
>=	大于即是	5 >= 6	false
<=	小于即是	5 <= 6	true
BETWEEN...AND...	在某个范围内	比方判断1是否在[2, 5]之间	false
AND	我和你 &&	5>1 AND 1>2	false
OR	我或你 \| \|	5>1 OR 1>2	true

留意事项

字段名：字段名最好带上``，制止关键字
条件：筛选的条件，如果没有指定条件，就会修改该字段下所有的值
值：是一个详细的值，也可以是一个变量
- 比方：CURRENT_TIME
- 1. UPDATE `student` SET `birthday`=CURRENT_DATE WHERE id=2;
  复制代码

多个设置的属性之间，利用英文逗号隔开

UPDATE 数据表名 SET 字段名1='新的值1',字段名2='新的值2',... WHERE 条件;

复制代码

删除（DELETE FROM）

DELETE FROM 数据表名 WHERE 条件;

复制代码

原先的表格

删除指定的数据

DELETE FROM `student` WHERE id = 1;

复制代码

删除整张表的数据

DELETE FROM `student`;

复制代码

TRUNCATE下令

TRUNCATE `student`;

复制代码

delete和truncate的区别

相同点：都能删除数据，并且都不会删除表的结构
差别点：
- truncate 能够重新设置自增列，计数器归零
- truncate 不会影响事件

利用delete删除数据后再进行添加数据，自增列的计数器并不会归零

利用truncate删除数据后再进行添加数据，自增列的计数器会归零

DELETE删除后重启数据库

INNODB 自增列会从1开始（因为自增字段是存储在内存中的，断电即失，不过MySQL 8.0修复了这个问题，此时自增字段保存在redo log中，重启后不会重置）
MYISAM 自增列依旧从上一个自增量开始（因为自增字段存储在文件中，不会因为重启而丢失数据）

DQL查询数据（重点）（SELECT）

所有的查询操作都用它 SELECT
简朴的查询，发杂的查询都能做
数据库中最核心的语言

查询全部字段

SELECT * FROM 数据表名;

复制代码

查询指定字段

查询单个字段

SELECT 字段名 FROM 数据表名;

复制代码

查询多个字段

SELECT 字段名1,字段名2 FROM 数据表名;

复制代码

可以看到，数据固然查询出来了，但是表头照旧我们界说的字段名
因此就有一个新的玩法：别名给结果起一个名字（AS）
别名（AS）

可以给字段起别名，也可以给数据表起别名

SELECT 字段名1 AS 别名1,字段名2 AS 别名2 FROM 数据表名 AS 数据表的别名;

复制代码

CONCAT(a , b) —— 拼接字符串的函数

SELECT CONCAT('要拼接的字符串' , 字段名) AS 别名 FROM 数据表名;

复制代码

去重（DISTINCT）

去除SELECT查询出来的结果中重复的数据，重复的数据只显示一条

SELECT DISTINCT 字段名1 AS 别名1,字段名2 AS 别名2,... FROM 数据表名;

复制代码

可以看到，固然查询到了数据，但有很多重复的数据，但是我们不需要重复数据
因此，可以加上DISTINCT关键字

可以看到，重复数据只保留了一条

查询系统版本 —— 函数

SELECT VERSION();

复制代码

用来盘算 —— 表达式

SELECT 120*2-1 AS 计算结果;

复制代码

查询自增的步长 —— 变量

SELECT @@auto_increment_increment;

复制代码

数据库的表达式：文本值，列，NULL，函数，盘算表达式，系统变量……

SELECT 表达式 FROM 数据表;

复制代码

WHERE条件子句

检索数据中符合条件的值
检索的条件由一个或多个表达式组成结果返回的都是一个布尔值
逻辑运算符

运算符	语法	描述
and &&	a and b a && b	逻辑与，两个都为真，结果为真
or \| \|	a or b a \| \| b	逻辑或，其中一个为真，结果为真
not !	not a ! a	逻辑非，真为假，假为真

不指定条件的查询

查询结果在45到60之间的学生学号以及分数

以上这三种方法运行出来的结果都是一样的

查询学号不即是1000的学生学号以及结果

以上两种方式运行的结果都是一样的，要留意的是NOT 紧跟在WHERE的后面

查询学号不即是1000和1001的学生学号以及结果

查询结果在45到60之间且学号不即是1000的学生学号以及分数

含糊查询：比力运算符

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

LIKE 比力运算符

占位符

%表示0个到恣意个字符
_表示1个字符

查询姓张的同学（%）

查询姓张的同学，且姓之后只有一个字（_）

查询名字中心有伟的同学

IN 比力运算符

IN( ) 括号里面是详细的一个或多个值，不能利用占位符
查询学号为1000和1001的学生

查询在广东汕头的同学

IS NULL、IS NOT NULL

查询所在为空的同学（IS NULL 也是判断系统设置为NULL的数据）

查询所在不为空的同学（这种条件是设置了该字段可以为空并且该数据系统设置成NULL）

联表查询

分析需求，分析查询的字段来自那些表（毗连查询）
确定利用哪种毗连查询？（7种）
确定交叉点（这两个表当中有哪个字段是相同的）
比方：判断的条件：学生表中的 studentNo = 结果表中的 studentNo
前情提要：
student表

result表

subject表

grade表

查询参加了考试的同学（学号，姓名，科目编号，分数）

查询缺考的同学（学号，姓名，科目编号，分数）

查询同学的相关信息（学号，姓名，科目编号，科目名称，分数）

查询学生的学号，姓名，年级名称

查询科目所属的年级（科目名称，年龄名称）

查询参加了高等数学-4考试的学生信息（学号，姓名，科目，分数）

操作	描述
INNER JOIN	交集，两边都有
LEFT JOIN	即使右表中没有匹配，也会从左表中返回所有的值，只不过没有匹配的那一行返回的右表字段的值是NULL
RIGHT JOIN	即使左表中没有匹配，也会从右表中返回所有的值，只不过没有匹配的那一行返回的右表字段的值是NULL

JOIN的七种毗连

https://cloud.tencent.com/developer/article/1778572
需要留意的是相同的字段要界说好是哪个表（在SELECT 后面的，不是ON后面的）的，如果是内毗连就无需在意，如果是左毗连这个字段名就需要用左表.字段名，如果是右毗连就需要右表.字段名，全毗连和全不毗连都需要留意，因为都包含左毗连和右毗连
内毗连（INNER JOIN）

左毗连（LEFT JOIN）

右毗连（RIGHT JOIN）

左独有

右独有

全毗连

全不毗连

自毗连

本身的表和本身的表毗连，核心：一张表拆为两张一样的表即可
原先的表

这一张表其实是由两个表糅合在一起的，这个时间就要把他当作两张表在看
父类

categoryid	categoryName
2	信息技术
3	软件开发
5	美术设计

子类

pid	categoryid	categoryName
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	PS技术

操作：查询父类对应的子类关系

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	PS技术

分页（limit）和排序（order by）

排序

升序ASC 降序DESC

ORDER BY 字段名 ASC/DESC;

复制代码

查询参加了高等数学-3考试的学生的学号，姓名，科目名称，分数，并且按照分数降序排列

升序

分页

缓解数据库压力，给人的体验更好
一般图片才会利用瀑布流，不过照旧要根据现实情况

LIMIT 查询起始下标（当前页）,页面的大小;
--第n页
LIMIT 页面的大小*(n-1),页面的大小;
数据总数/页面的大小 = 总页数

复制代码

查询分数大于50的前五名的学生信息（学号，姓名，课程名称，分数）

子查询

WHERE 一个计算出来的值;

复制代码

本质：在where语句中嵌套一个子查询语句
留意：子查询返回的只能是1个值，不能是多行值
查询高等数学-3的所有考试结果（学号，科目编号，结果）降序排列

查询高等数学-3分数不少于50分的学生的学号和姓名

分组和过滤（GROUP BY 和 HAVING）

-- 查询不同课程的平均分，最低分，最高分，平均分大于60分
-- 核心：根据不同的课程分组
SELECT subjectname,AVG(studentresult),MAX(studentresult),MIN(studentresult)
FROM result AS r
INNER JOIN `subject` AS s
ON r.subjectno = s.subjectno
GROUP BY subjectname -- 通过什么字段来分组
HAVING AVG(studentresult) > 60;

复制代码

MySQL函数

常用函数

-- 数学运算
SELECT ABS(-8); -- 绝对值返回8
SELECT CEILING(9.4); -- 向上取整返回10
SELECT FLOOR(9.4); -- 向下取整返回9
SELECT RAND(); -- 返回一个0-1之间的随机数
SELECT SIGN(); -- 判断一个数的符号如果输入0，返回0；输入正数返回1，输入负数返回-1
-- 字符串函数
SELECT CHAR_LENGTH('即使再小的帆也能远航'); -- 字符串长度返回10
SELECT CONCAT('我','爱','JAVA'); -- 拼接字符串返回我爱JAVA
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱'); -- 插入替换,从第一个字符开始，替换2个字符，返回超级热爱编程helloworld
SELECT LOWER('ShanTou'); -- 转成小写字母返回shantou
SELECT UPPER('ShanTou'); -- 转成大写字母返回SHANTOU
SELECT INSTR('ShanTou','h'); -- 返回第一次出现的子串的索引返回2
SELECT REPLACE('坚持就能成功','坚持','努力'); -- 替换出现的指定字符串返回努力就能成功
SELECT SUBSTRING('坚持就能成功',3,2); -- 返回指定的子字符串返回就能
SELECT REVERSE('坚持就能成功'); -- 反转返回功成能就持坚
-- 查询姓周的同学并把张换成章
SELECT REPLACE(studentname,'张','章') FROM `student`
WHERE studentname like '张%';
-- 时间和日期函数
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());
-- 系统
SELECT SYSTEM_USER();
SELECT USER();
SELECT VERSION();

复制代码

聚合函数

函数名称	描述
COUNT()	计数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值
...	...

-- 聚合函数
-- 都能够统计表中的数据（想查询一个表中有多少个记录，就使用这个count()）
SELECT COUNT(borndate) FROM student; -- count(字段)，会忽略所有的null值
SELECT COUNT(*) FROM student; -- count(*) 不会忽略null值本质：计算行数，把所有的列都走一遍
SELECT COUNT(1) FROM student; -- count(1) 不会忽略null值本质：计算行数，一行只走一个列
SELECT SUM(studentresult) AS 总和 FROM result;
SELECT AVG(studentresult) AS 平均分 FROM result;
SELECT MAX(studentresult) AS 最高分 FROM result;
SELECT MIN(studentresult) AS 最低分 FROM result;

复制代码

数据库级别的MD5加密（扩展）

什么是MD5

MD5又称信息择要算法，重要加强算法复杂度和不可逆转
详细的值的MD5是一样的

MD5破解网站的原理

背后有一个字典，字典包含了MD5加密前后的值
假设你输入一个加密后的值，且在字典中能找到对应的加密前的值，就能进行破解
但是密码稍微复杂一点就破解不了

-- ======测试MD5 加密======
CREATE TABLE `testmd5` (
id INT(10) NOT NULL,
name VARCHAR(20) NOT NULL,
pwd VARCHAR(50) NOT NULL,
PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4
-- 明文密码
INSERT INTO `testmd5` (id,name,pwd) VALUES (1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456');

复制代码

-- 加密
UPDATE `testmd5` SET pwd = MD5(pwd) WHERE id = 1;

复制代码

-- 插入的时候加密
INSERT INTO `testmd5` (id,name,pwd) VALUES (4,'小明',MD5('123456'));

复制代码

事件

什么是事件

要么都成功，要么都失败
----------------------
1、SQL执行 A 给 B 转账 A 1000 -->200 B 200
2、SQL执行 B 收到 A 的钱 A 800 B 400
-----------------------
将一组SQL放在一个批次中去执行
事件原则：

ACID原则（原子性、一致性、隔离性、持久性）有大概产生脏读、幻读、不可重复读...
原子性（Atomicity）详解

原子性（Atomicity）的界说与重要性

原子性（Atomicity），作为事件处理的根本特性之一，确保了事件中的操作要么全部完成，要么全部不执行。在执行多步操作的事件中，如果任何一步失败，整个事件将被回滚到开始状态，就像这个事件从未被执行过一样。
事件原子性示例

在一个银行转账事件中，假设需要从账户A转移资金到账户B。
这个事件包含两个步调：

步调1：从账户A扣除相应金额
步调2：向账户B添加相应金额。

原子性确保了这两个操作要么同时成功，要么同时失败。如果在扣除金额后，向账户B添加金额时发生错误，整个事件将被回滚，账户A的金额将被规复。

一致性（Consistency）详解

一致性的详细含义

一致性在数据库事件管理中指的是事件将数据库从一个一致的状态转移到另一个一致的状态。这意味着事件执行的结果必须符合所有预界说的规则和束缚，包罗数据库的内部规则（如主键、外键束缚、唯一性束缚等）以及应用层面的业务规则。
一致性确保了数据的正确性和有效性，防止了数据冲突和逻辑错误。
一致性示例

在银行转账的场景中，假设初始时用户A和用户B分别用100美元，用户A需要向用户B转100美元。
这个操作涉及两个步调：从用户A的账户扣除100美元，向用户B的账户增加100美元。
一致性确保了在整个事件过程中，两个账户的总金额保持不变。如果事件在任何步调中失败，系统将回滚事件，确保账户金额的一致性不被破坏。

持久性（Durability）详解

持久性的界说和意义

持久性（Durability）指的是一旦事件被提交，它对数据库的修改应该是永久性的，即使发生系统崩溃或其他故障。
持久性确保了数据的可靠性和稳固性，是数据库管理系统（DBMS）的核心特性之一。对于任何关键业务来说，数据的持久性是不可或缺的，因为它包管了数据的安全性和一致性，即使在系统碰到故障时也能够保持数据不丢失。

隔离性（Isolation）

隔离性（Isolation）的界说

隔离性（Isolation）确保并发执行的事件是隔离的，即一个事件的执行不会被其他事件干扰。
这个特性是通过事件隔离级别来实现的，差别的隔离级别能够解决差别的并发事件中的问题，但同时也会在性能和一致性之间做出权衡。

隔离所导致的一些问题

脏读：

指一个事件读取了别的一个事件未提交的数据

不可重复读：

在一个事件内部读取表中某一行的数据，多次读取结果差别（这个不肯定是错误，只不过某些场合不对）

虚读（幻读）：

是指在一个事件内部读取到了别的事件插入的数据，导致前后读取不一致

执行事件

MySQL是默认开启事件自动提交的

-- 关闭事务
SET autocommit = 0;
-- 开启事务（默认）
SET autocommit = 1;
-- 手动处理事务
-- 第一步：关闭自动提交
SET autocommit = 0;
-- 事务开启
STRT TRANSACTION; -- 标记一个事务的开始，从这个之后的 SQL 都在同一个事务中
INSERT xxx;
INSERT xxx;
-- 提交：持久化（成功就提交）
COMMIT;
-- 回滚：回到原来的样子（失败就回滚）
ROLLBACK;
-- 事务结束
-- 最后一步：开启自动提交
SET autocommit = 1;
-- 设置一个事务的保存点
SAVEPOINT 保存点名;
-- 回滚到保存点
ROLLBACK TO SAVEPOINT 保存点名;
-- 撤销保存点
RELEASE SAVEPOINT 保存点名;

复制代码

模拟转账

-- 模拟转账
-- 关闭自动提交
SET autocommit = 0;
-- 开启事务
START TRANSACTION;
-- 编写一组事务
UPDATE account SET money = money - 500 WHERE name = 'A';
UPDATE account SET money = money + 500 WHERE name = 'B';
-- 提交
COMMIT;
-- 回滚
ROLLBACK;
-- 开启自动提交
SET autocommit = 1;

复制代码

首先，要关闭自动提交

-- 关闭自动提交
SET autocommit = 0;

复制代码

当我们运行事件中的代码后（不执行提交）

这个时间不执行提交，而是执行回滚

但是若是执行力提交

再执行回滚，数据并不会规复，因为执行了提交就已经吧数据持久化了

末了就是要记得规复自动提交

-- 开启自动提交
SET autocommit = 1;

复制代码

索引

MySQL官方对索引的界说为：索引（index）是帮助MySQL高效获取数据的数据结构
提取句子主干，就可以得到索引的本质：索引是数据结构
索引的分类

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

主键索引（PRIMARY KEY）
- 唯一的标识，主键不可重复，一张表只能有一个主键，但是可以由多个字段做一个主键
唯一索引（UNIQUE KEY）
- 制止重复的列出现，多个列都可以标识为唯一索引
常规索引（KEY或者INDEX）
- 默认的，可以用index、key关键字来设置
全文索引（FULLTEXT）
- 在特定的数据引擎下才有，以前只有MyISAM支持，现在其他有的也支持
- 快速定位数据
- 但是全文索引在数据量比力小的情况下是没有效的

添加primary key(主键索引)

alter table 表名 add primary key(列名);

复制代码

添加unique(唯一索引)

alter table 表名 add unique(列名);

复制代码

添加index(平凡索引)

alter table 表名 add index 索引名 (列名);

复制代码

添加fulltext(全文索引)

alter table 表名 add fulltext (列名);

复制代码

添加多列索引

alter table 表名 add index 索引名 (列名1，列名2.......);

复制代码

删除索引

利用 DROP INDEX 语句

语法格式

DROP INDEX 索引名 ON 表名;

复制代码

语法说明如下：
<索引名>：要删除的索引名。
<表名>：指定该索引所在的表名。
利用 ALTER TABLE 语句

删除表中的主键

ALTER TABLE 表名 DROP PRIMARY KEY;

复制代码

删除名称为 index_name 的索引

ALTER TABLE 表名 DROP INDEX 索引名;

复制代码

删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 索引名;

复制代码

留意：如果删除的列是索引的组成部分，那么在删除该列时，也会将该列从索引中删除；如果组成索引的所有列都被删除，那么整个索引将被删除。

先设置money为全文索引，在设置name为唯一索引

ALTER TABLE `account` ADD UNIQUE `money` (`money`);
DROP INDEX `NAME` ON `account`;
ALTER TABLE `account` ADD FULLTEXT `NAME`(`name`);
DROP INDEX `NAME` ON `account`;

复制代码

接着检察索引信息

-- 显示所有的索引信息
SHOW INDEX FROM `account`;

复制代码

测试索引

插入100万条数据

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 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'
-- 插入100万条数据
DELIMITER $$ -- 写函数之前必须要写，当作标志
CREATE FUNCTION mock_data()
RETURNS INT DETERMINISTIC
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),'232332@qq.com',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';
EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999';
-- id_表名_字段名
-- CREATE INDEX 索引名 ON 表名(字段名);
CREATE INDEX id_app_user_name ON app_user(`name`);
SELECT * FROM app_user WHERE `name`='用户9999';
EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999';

复制代码

没加索引前

加了索引后

索引在小数据量的时间，用处不大；但是在大数据的时间，区别非常显着

索引原则

索引不是越多越好
不要对经常变更的数据加索引
小数据量的表不需要加索引
索引一般加在经常用来查询的字段上

权限管理和备份

用户管理

Navicat 可视化管理

点击用户

点击新建用户

在常规处输入相关信息

在服务器权限处授予全部权限

末了点击保存即可
SQL下令操作

用户表：mysql下的user表
本质：对这张表进行增删改查
原先的user表

创建用户

-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码';
CREATE USER lhj IDENTIFIED BY '123456';

复制代码

修改密码

-- 修改密码（修改当前用户密码）
SET PASSWORD = '123456';
-- 修改密码（修改指定用户密码）
SET PASSWORD FOR lhj = '123456';

复制代码

用户重命名

-- 重命名 RENAME USER 旧名字 TO 新名字;
RENAME USER lhj TO lhj2;

复制代码

用户授权

-- 用户授权
-- ALL PRIVILEGES 除了给别人授权，其他都能干
GRANT ALL PRIVILEGES ON *.* TO lhj2;

复制代码

查询权限

-- 查询权限 SHOW GRANTS FOR 用户;
SHOW GRANTS FOR lhj2;
SHOW GRANTS FOR root@localhost;

复制代码

撤销权限

-- 撤销权限 REVOKE 哪些权限在哪个库撤销给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM lhj2;

复制代码

删除用户

-- 删除用户
DROP USER lhj2;

复制代码

MySQL备份

为什么要备份？

包管重要的数据不丢失
数据转移

MySQL数据库备份的方式

直接拷贝物理文件
在 Navicat 这种可视化工具中手动导出
- 在想要导出的库或者是表上，右键，选择转储SQL文件
利用下令行（ win+r 输入 cmd ）导出 mysqldump 下令行利用
- 1. # 导出一张表
  2. # mysqldump -h主机 -u用户名 -p密码数据库名数据表名 >物理磁盘位置/文件名
  3. mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
  5. # 导出多张表
  6. # mysqldump -h主机 -u用户名 -p密码数据库名数据表名1 数据表名2 >物理磁盘位置/文件名
  7. mysqldump -hlocalhost -uroot -p123456 school student result >D:/b.sql
  9. # 导出数据库
  10. # mysqldump -h主机 -u用户名 -p密码数据库名 >物理磁盘位置/文件名
  11. mysqldump -hlocalhost -uroot -p123456 school >D:/c.sql
  复制代码

MySQL数据库导入的方式

# 第一步：登录MySQL mysql -u用户名 -p密码
mysql -uroot -p123456;
# 第二步：选定要导入的位置 use 数据库名;
use school;
# 第三步：导入sql文件 source 物理磁盘位置/文件名;
source D:/a.sql;
# 另外一种方法 mysql -u用户名 -p密码数据库名 < 物理磁盘位置/文件名;
mysql -uroot -p123456 school < 物理磁盘位置/文件名;

复制代码

C:\Users\罗弘杰>mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 8.0.31 MySQL Community Server - GPL
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use school;
Database changed
mysql> source D:/a.sql
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 4 warnings (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

复制代码

规范数据库设计

为什么需要设计

当数据库比力复杂的时间，我们就需要设计了
区别

糟糕的数据库设计：

数据冗余，浪费空间
数据库插入和删除都会贫困，乃至会产生非常【屏蔽利用物理外键】
程序的性能差

精良的数据库设计：

节省内存空间
包管数据库的完备性
方便我们开发系统

软件开发中，关于数据库的设计

分析需求：分析业务和需要处理的数据库的需求
概要设计：设计关系图 E-R 图

设计数据库的步调：（个人博客）

收集信息，分析需求
- 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
- 分类表（文章分类，谁创建的）
- 文章表（文章的信息）
- 评论表
- 友链表（友链信息）
- 自界说表（系统信息，某个关键的字，或者一些主字段）
- 说说表（发表心情 .. id.. content..create_time）
标识实体（把需求落地到每个字段）
标识实体之间的关系
- 写博客：user --> blog
- 创建分类：user --> category
- 关注：user --> user
- 友链：links
- 评论：user --> user -->blog

三大范式

为什么需要数据规范化

信息重复
更新非常
插入非常
- 无法正常显示信息
删除非常
- 丢失有效的信息

第一范式（1NF）

原子性：包管每一列不可再分

属性不可再分，即表中的每个列都不可以再进行拆分。
如下学生信息表(student)：
id、name(姓名)、sex_code(性别代号)、sex_desc(性别描述)、contact(联系方式)
primary key(id)

如果在查询学生表时经常用到学生的电话号，则应该将联系方式(contact)这一列分为电话号(phone)和所在(address)两列，这样才符合第一范式。

第二范式（2NF）

条件：必须满足第一范式
每张表只描述一件事情

在满足1NF的条件下，表中不存在部分依赖，非主键列要完全依赖于主键。(重要是说在联合主键的情况下，非主键列不能只依赖于主键的一部分)
如下学天生绩表(score)：
stu_id(学生id)、kc_id(课程id)、score(分数)、kc_name(课程名)
primary key(stu_id, kc_id)

表中主键为stu_id和kc_id组成的联合主键。满足1NF；非主键列score完全依赖于主键，stu_id和kc_id两个值才能决定score的值；而kc_name只依赖于kc_id，与stu_id没有依赖关系，它不完全依赖于主键，只依赖于主键的一部分，不符合2NF。
修改使表满足2NF后：
结果表(score) primary key(stu_id)

课程表(kc) primary key(kc_id)

将原来的结果表(score)拆分为结果表(score)和课程表(kc)，而且两个表都符合2NF。
第三范式（3NF）

条件：必须满足第一范式和第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关

在满足2NF的条件下，不存在传递依赖。(A -> B, B -> C, A->C)
如下学生信息表(student)：
primary key(id)

表中sex_desc依赖于sex_code，而sex_code依赖于id(主键)，从而推出sex_desc依赖于id(主键)；sex_desc不直接依赖于主键，而是通过依赖于非主键列而依赖于主键，属于传递依赖，不符合3NF。
修改表使满足3NF后：
学生表(student) primary key(id)

性别代码表(sexcode) primary key(sex_code)

将原来的student表进行拆分后，两个表都满足3NF。
（规范数据库的设计）

规范性和性能的问题

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

考虑商业化的需求和目标（成本，用户体验）数据库的性能更加重要
在规范性能的问题的时间，需要适当的考虑一下规范性
故意给某些表增加一些冗余的字段（从多表查询酿成单表查询）
故意增加一些盘算列（从大数据量降低为小数据量的查询）也可以增加索引（但是索引数比力占内存）

JDBC

数据库驱动

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

SUN 公司为了简化开发职员对数据库的同一操作，提供了一个（Java操作数据库的）规范，俗称JDBC
这些规范的实现由详细的厂商去做
对于开发职员来说，只需要掌握JDBC接口的操作即可
java.sql
javax.sql
还需要导入一个数据库驱动包 mysql-connector-java-版本.jar

第一个JDBC程序

创建测试数据库

CREATE DATABASE `jdbcStudy` CHARACTER SET utf8 COLLATE utf8_general_ci;
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','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04');

复制代码

1、创建一个平凡项目

2、导入数据库驱动

3、编写测试代码

package com.ling.lesson01;
import java.sql.*;
// 我的第一个JDBC程序
public class JdbcFirstDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 1. 加载驱动
Class.forName("com.mysql.cj.jdbc.Driver"); // 固定写法，再在驱动
// 2. 用户信息和url
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&&useSSL=true";
String username = "root";
String password = "123456";
// 3. 连接成功，返回数据库对象 Connection代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
// 4. 创建执行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("pwd=" + resultSet.getObject("PASSWORD"));
System.out.println("email=" + resultSet.getObject("email"));
System.out.println("birth=" + resultSet.getObject("birthday"));
System.out.println("=============================");
}
// 6. 释放连接
resultSet.close();
statement.close();
connection.close();
}
}

复制代码

步调总结：
1、加载驱动
2、毗连数据库 DriverManager
3、获得执行sql的对象 Statement
4、获得返回的结果集
5、开释毗连

DriverManager

// 之前的写法但这种相当于注册了两次
DriverManager registerDriver(new com.mysql.cj.jdbc.Driver());
// 现在的写法
Class.forName("com.mysql.cj.jdb.Driver");

复制代码

URL

// 协议://主机地址:端口号/数据库名?参数1&参数2&参数3
// mysql端口号默认是3306
// oracl端口号默认是1521 jdbc:oracle:thin:@localhost:1521:sid
// 可以把jdbc:mysql想成http
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";

复制代码

Connection

// connection代表数据库
// 数据库设置自动提交
connection.setAutoCommit();
// 事务提交
connection.commit();
// 事务回滚
connection.rollback();

复制代码

Statement

Statement 和 PrepareStatement 都是执行SQL的对象

// 执行所有的sql
statement.execute();
// 执行查询返回ResultSet
statement.executeQuery();
// 执行更新、插入、删除返回一个受影响的行数
statement.executeUpdate();

复制代码

ResultSet

查询的结果集，封装了所有的查询结果
获得指定的数据范例

// 在不是到列的类型的时候使用
resultSet.getObject();
// 如果知道列的类型就使用指定的类型
resultSet.getString();
resultSet.getInt();
resultSet.geFloat();
resultSet.getDouble();

复制代码

遍历（指针）

// 指针移动到最前面
resultSet.beforeFirst();
// 指针移动到最后面
resultSet.afterLast();
// 指针移动到写一个数据
resultSet.next();
// 移动到前一行
resultSet.previous();
// 移动到指定行
resultSet.absolute(row);

复制代码

开释资源

// 释放连接
resultSet.close();
statement.close();
connection.close(); // 耗费资源，用完关掉

复制代码

statement对象

jdbc中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可
Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sql语句，executeUpdate执行完后，将会返回一个整数（即增删改语句导致数据库几行数据发生了变化）
Statement.executeQuery方法用于向数据库发送查询语句，mexecuteQuery方法返回代表查询结果的ResultSet对象

CRUD——create

利用executeUpdate(String sql)方法完成数据添加操作，示例操作：

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

复制代码

CRUD——delete

利用executeUpdate(String sql)方法完成数据删除操作，示例操作：

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

复制代码

CRUD——update

利用executeUpdate(String sql)方法完成数据修改操作，示例操作：

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

复制代码

代码实现
1、提取工具类

package com.ling.lesson02.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static {
try {
InputStream resourceAsStream = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(resourceAsStream);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
// 1. 驱动只用加载一次
Class.forName(driver);
} catch (IOException | ClassNotFoundException e) {
throw new RuntimeException(e);
}
}
// 获取连接
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) {
throw new RuntimeException(e);
}
}
if (st != null) {
try {
st.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}

复制代码

2、编写增删改的方法executeUpdate

package com.ling.lesson02.utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestInsert {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
String sql = "INSERT INTO `users`(id,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES (4,'lhj','123456','2479433253@qq,com','2003-09-08')";
int i = st.executeUpdate(sql);
if (i > 0) {
System.out.println("插入成功");
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JdbcUtils.release(conn,st,rs);
}
}
}

复制代码

package com.ling.lesson02.utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDelete {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
String sql = "DELETE FROM users where id = 1";
int i = st.executeUpdate(sql);
if (i > 0){
System.out.println("删除成功");
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JdbcUtils.release(conn,st,rs);
}
}
}

复制代码

package com.ling.lesson02.utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestUpdate {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
String sql = "UPDATE users SET NAME = 'fjq' WHERE id = 4";
int i = st.executeUpdate(sql);
if (i > 0){
System.out.println("修改成功");
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JdbcUtils.release(conn,st,rs);
}
}
}

复制代码

3、查询executeQuery

package com.ling.lesson02.utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestSelect {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
String sql = "SELECT * FROM users";
rs = st.executeQuery(sql);
while (rs.next()) {
System.out.println("id=" + rs.getInt("id"));
System.out.println("name=" + rs.getString("NAME"));
System.out.println("password=" + rs.getString("PASSWORD"));
System.out.println("email=" + rs.getString("email"));
System.out.println("birthday=" + rs.getDate("birthday"));
System.out.println("==========================");
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JdbcUtils.release(conn,st,rs);
}
}
}

复制代码

SQL注入的问题
SQL存在抖动，会被攻击导致数据泄漏 SQL会被拼接用到or

package com.ling.lesson02;
import com.ling.lesson02.utils.JdbcUtils1;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SQL注入 {
public static void main(String[] args) throws SQLException {
// 正常登录
//login("lhj","123456");
// SQL注入
login("'or'1=1","'or'1=1");
}
// 登录业务
public static void login(String username,String password) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils1.getConnection();
st = conn.createStatement();
String sql = "SELECT * FROM users WHERE NAME = '" + username +"' AND PASSWORD = '" + password + "'";
rs = st.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString("NAME"));
System.out.println(rs.getString("PASSWORD"));
System.out.println("===============================");
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JdbcUtils1.release(conn, st, rs);
}
}
}

复制代码

PrepareStatement

PrepareStatement 可以防止SQL注入并且效率更高

1、新增

package com.ling.lesson03;
import com.ling.lesson02.utils.JdbcUtils1;
import java.sql.*;
public class TestInsert {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
try {
conn = JdbcUtils1.getConnection();
String sql = "insert into users(id,NAME,PASSWORD,email,birthday) values (?,?,?,?,?)";
// 预编译
st = conn.prepareStatement(sql);
// 设置值
st.setInt(1,5);
st.setString(2,"hwd");
st.setString(3,"123456");
st.setString(4,"132213@qq.com");
st.setDate(5,new Date(new java.util.Date().getTime()));
// 对象执行sql
int i = st.executeUpdate();
if (i > 0) {
System.out.println("插入成功");
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JdbcUtils1.release(conn,st,null);
}
}
}

复制代码

2、删除

package com.ling.lesson03;
import com.ling.lesson02.utils.JdbcUtils1;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestDelete {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
try {
conn = JdbcUtils1.getConnection();
String sql = "delete from users where id = ?";
st = conn.prepareStatement(sql);
st.setInt(1,5);
int i = st.executeUpdate();
if (i > 0) {
System.out.println("删除成功");
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JdbcUtils1.release(conn,st,null);
}
}
}

复制代码

3、更新

package com.ling.lesson03;
import com.ling.lesson02.utils.JdbcUtils1;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.IdentityHashMap;
public class TestUpdate {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
try {
conn = JdbcUtils1.getConnection();
String sql = "update users set NAME=? where id=?;";
st = conn.prepareStatement(sql);
st.setString(1,"zxy");
st.setInt(2,5);
int i = st.executeUpdate();
if (i > 0) {
System.out.println("修改成功");
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JdbcUtils1.release(conn,st,null);
}
}
}

复制代码

4、查询

package com.ling.lesson03;
import com.ling.lesson02.utils.JdbcUtils1;
import com.ling.lesson02.utils.JdbcUtils3;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestSelect {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils3.getConnection();
String sql = "SELECT * FROM users WHERE PASSWORD = ?";
st = conn.prepareStatement(sql);
st.setString(1,"123456");
rs = st.executeQuery();
while (rs.next()) {
System.out.println("name=" + rs.getString("NAME"));
System.out.println("===================");
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JdbcUtils3.release(conn,st,rs);
}
}
}

复制代码

5、防止SQL注入

package com.ling.lesson03;
import com.ling.lesson02.utils.JdbcUtils1;
import java.sql.*;
public class SQL注入 {
public static void main(String[] args) throws SQLException {
// 正常登录
//login("lhj","123456");
// SQL注入
login("'or'1=1","'or'1=1");
}
// 登录业务
public static void login(String username,String password) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils1.getConnection();
// PreparedStatement 防止SQL注入的本质：把传递进来的参数当作字符
// 假设其中存在转义字符，就直接互虐，比如说引号（'）会被直接转移
String sql = "SELECT * FROM users WHERE NAME = ? AND PASSWORD = ?";
st = conn.prepareStatement(sql);
st.setString(1,username);
st.setString(2,password);
rs = st.executeQuery();
while (rs.next()) {
System.out.println(rs.getString("NAME"));
System.out.println(rs.getString("PASSWORD"));
System.out.println("===============================");
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JdbcUtils1.release(conn, st, rs);
}
}
}

复制代码

利用IDEA毗连数据库

接下来就是输入相关信息，测试毗连后应用

这个时间数据库默认是没有详细的数据库的，这个时间可以选择去添加

勾选想要添加的数据库进行应用

双击表格即可检察表的内容，并可以看到对应的SQL信息

更新数据肯定要点击提交，不然不会更新成功

如何编写SQL

事件

要么都成功，要么都失败
ACID原则
原子性：要么全部完成，要么都不完成
一致性：总数不变，最终一致性，符合逻辑
隔离性：多个事件互不干扰
持久性：一旦提交不可逆，持久化到数据库

隔离性的问题：
脏读：一个事件读取到另一个没有提交的事件
不可重复读：在同一个事件内重复读取表中的数据，表数据发生了改变，这个时间就不知道用原来的数据照旧新的数据
幻读（虚读）：在一个事件内读取到了别的事物插入的数据

代码实现
1、开启事件

conn.setAutoCommit(false);

复制代码

2、一组业务执行完毕，提交事件
3、可以在catch语句中界说回滚语句，但默认事件失败就会回滚

package com.ling.lesson04;
import com.ling.lesson02.utils.JdbcUtils3;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransaction {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils3.getConnection();
// 关闭数据库的自动提交，会自动开启事务
conn.setAutoCommit(false);
String sql1 = "UPDATE account set money = money - 100 WHERE name = 'A'";
st = conn.prepareStatement(sql1);
st.executeUpdate();
int x = 1/0; // 报错,如果没有这个错误，这个事务就会正常执行->提交
String sql2 = "UPDATE account set money = money + 100 WHERE name = 'B'";
st = conn.prepareStatement(sql2);
st.executeUpdate();
// 业务完毕，提交事务
conn.commit();
System.out.println("操作成功");
} catch (SQLException e) {
try {
conn.rollback(); // 如果失败则回滚事务
} catch (SQLException ex) {
throw new RuntimeException(ex);
}
throw new RuntimeException(e);
} finally {
JdbcUtils3.release(conn, st, rs);
}
}
}

复制代码

数据库毗连池

数据库毗连 --> 执行完毕 --> 开释（毗连到开释非常浪费系统资源）
池化技术：准备一些预先的资源，过来就毗连已经准备好的资源

最小毗连数：按照需求，一般常用毗连数是多少，最小毗连数就是多少
最大毗连数：业务最高承载上限
等待超时：如果超出就报非常

编写毗连池，实现一个接口 DataSource

开源数据源实现

DBCP
C3P0
Druid：阿里巴巴

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

DBCP

需要用到的 jar 包
commons-dbcp2-2.12.0.jar
commons-pool2-2.12.0.jar
commons-logging-1.3.4.jar

dbcp-config.properties

#????
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456
#
initialSize=10
#??????
maxActive=50
#
maxIdle=20
#
minIdle=5
#
maxWait=60000
#JDBC?????????????????????????????=property;?
#???"user" ? "password" ????????????????????????
connectionProperties=useUnicode=true;characterEncoding=UTF8
#??????????????????auto-commit????
defaultAutoCommit=true
#driver default ????????????????read-only????
#???????????setReadOnly?????????????????????????Informix?
defaultReadOnly=
#driver default ??????????????????TransactionIsolation??
#??????????????javadoc??NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

复制代码

package com.ling.lesson05.utils;
import org.apache.commons.dbcp2.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils_DBCP {
private static DataSource dataSource = null;
static {
try {
InputStream resourceAsStream = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcp-config.properties");
Properties properties = new Properties();
properties.load(resourceAsStream);
// 创建数据源工厂模式 --> 创建对象
dataSource = BasicDataSourceFactory.createDataSource(properties);
} catch (IOException | SQLException e) {
throw new RuntimeException(e);
}
}
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
public static void release(Connection conn, Statement st, ResultSet rs) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (st != null) {
try {
st.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}

复制代码

C3P0

需要导入的 jar 包
c3p0-0.9.5.5.jar
mchange-commons-java-0.2.19.jar

c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!--
c3p0的缺省（默认）配置
如果在代码中"ComboPooledDataSource ds=new ComboPooledDataSource();"这样写就表示使用的是c3p0的缺省（默认）-->
<default-config>
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="acquiredIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</default-config>
</c3p0-config>

复制代码

package com.ling.lesson05.utils;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcUtils_C3P0 {
private static ComboPooledDataSource dataSource = null;
static {
dataSource = new ComboPooledDataSource();
}
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
public static void release(Connection conn, Statement st, ResultSet rs) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (st != null) {
try {
st.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}

复制代码

结论

无论利用什么数据源，本质照旧一样的，DataSource接口不会变，方法就不变

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

		自动登录	找回密码
密码			立即注册

MySQL——数据库

本帖子中包含更多资源

0 个回复

快速回复

楼主热帖

标签云