MySQL学生向笔记以及使用过程问题记录（内含8.0.34安装教程
MySQL
只会写代码 基本码农
要学好数据库，操作系统，数据结构与算法 不错的程序员
离散数学、数字电路、体系结构、编译原理。+实战经验， 高级程序员
去IOE：去掉IBM的小型机、Oracle数据库、EMC存储设备，代之以自己在开源软件基础上开发的系统。
一 初识MYSQL
1.1 数据库
数据库（DB，DataBase）是按照数据结构来组织、存储和管理数据的仓库，安装在操作系统之上
DBMS(数据库管理系统)数据库的管理软件，科学有效的管理我们的数据，维护和获取数据；
1.2 数据库分类
关系型数据库（SQL）：
- MYSQL、Oracle、Sql Server、DB2、SQLlite
- 通过表和表之间，行和列之间的关系进行数据的存储， 学员信息库，考勤表
非关系型数据库（NoSQL Not Only SQL）:
- Redis，MongDB
- 非关系型数据库，对象存储，通过对象自身的属性来决定
1.3 MySQL简介
MySQL是一个关系型数据库管理系统DBMS (Relational Database Management System，关系数据库管理系统)，属于Oracle旗下产品，使用的SQL语言。
SQL语言Structure Query Language(结构化查询语言)是用于访问数据库的最常用标准化语言，由于其体积小、速度快、开放源码等特点，一般中小型网站的开发都选择MySQL作为网站数据库。
1.4 SQL的分类
- DDL(Data Definition Language) 数据定义语言，用来操作数据库、表、列等； 常用语句：
CREATE、ALTER、DROP - DML(Data Manipulation Language) 数据操作语言，用来操作数据库中表里的数据；常用语句：
INSERT、UPDATE、DELETE - DCL(Data Control Language) 数据控制语言，用来操作访问权限和安全级别； 常用语句：
GRANT、DENY - DQL(Data Query Language) 数据查询语言，用来查询数据 常用语句：
SELECT
1.5 安装MySQL（8.0.34+win11）
1.5.1 下载压缩包
MySQL官网下载
安装建议：使用压缩包，不使用.exe安装（会产生注册表），卸载麻烦
下载并解压在环境目录下
1.5.2 配置环境变量
右键此电脑——属性——高级系统设置——环境变量——path（系统变量中的）——编辑
点击新建，将解压后的bin目录地址
1.5.3 编写MySQL配置文件
新建mysql配置文件，my.ini
注意basedir、datadir是需要改的
[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录 ----------是你的文件路径-------------
basedir=D:\Environment\Mysql-8.0.34\
# 设置mysql数据库的数据的存放目录 ---------是你的文件路径data文件夹自行创建，不要自己创建
#datadir=D:\DataBase\MySQL\Data\
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。
max_connect_errors=10
# 服务端使用的字符集默认为utf8mb4
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8mb4
-
启动
管理员模式下的CMD，进入mysql的bin目录，我的是cd/d D:\Environment\Mysql-8.0.34\bin
6. 输入mysqld -install安装mysql服务
-
输入
mysqld --initialize-insecure --user=mysql初始化数据库文件，会出现data文件夹
-
输入
net start mysql启动mysql
-
通过命令行
mysql -u root -p两个回车进入mysql，输入alter user 'root'@'localhost'identified by '123456';修改密码（SQL语句后面一定要加分号！） -
最后输入
flush privileges;刷新权限
-
输入quit或者exit都可以退出
-
重启mysql。
net start mysql连接测试，若连接成功即可 -
mysql -uroot -p登陆 输入密码 ok
- 然后右键此电脑——管理——服务与应用程序——服务
找到mysql将启动类型改为手动，防止电脑启动过慢
1.5.4 如果出现问题重装
清空服务
sc delete mysql
1.6 安装SQLyog
SQLyog 是一个易于使用的、快速而简洁的图形化管理MYSQL数据库的工具
-
注意下载一个社区的的版本（下载包里含Community）
1.7 SQLyog基本使用
每一个SQLyog的执行操作，本质就是对应了一个sql语句，可以在软件的历史记录中查看
-
新建数据库（前提连接mysql）
名称随意，输入密码
IP地址：本地访问本地安装的数据库，使用localhost
用户名：使用root用户
密码：安装mysql时，设置的密码
端口号：安装mysql时设置的端口号，一般使用3306
输入信息，点击连接时，报错，错误代码为2058。说明mysql没链接，重连即可1 -
新建一个数据库school
-
新建一张表student
-
字段： id,name,age
-
查看表
-
自己尝试添加多条记录
记得刷新保存
- 如何查看实际执行的sql语言
1.8 基本命令行操作
命令行里的连接：
-- 所有语句都用分号结尾
cd/d D:\Environment\Mysql-8.0.34\bin
mysql -uroot -p --链接数据库
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost'; --修改密码
flush privileges; --修改权限
show databases; -- 查看所有的数据库
use school -- 切换数据库（use 数据库名）
show tables; -- 查看数据库中所有的表
desc student; -- 显示表结构（desc 表名）
create database hello; -- 创建一个数据库hello
exit; -- 退出连接
-- 单行注释
/*
多行注释
*/
二 数据库、表的基本操作
操作数据库——操作数据库中的表——操作数据库中表的数据
mysql数据库关键字不区分大小写
2.1 操作数据库
2.1.1 创建数据库
([]表示可选，{}表示必选)
CREATE DATABASE [IF NOT EXISTS] 数据库名 [CHARACTER SET utf8 COLLATE utf8_general_ci]
2.1.2 删除数据库
DROP DATABASE [IF EXISTS] 数据库名
2.1.3 使用数据库·
-- tab键的上面，如果你的表名或者字段名是一个特殊字符，就需要带上``
USE `school`
2.1.4 查看数据库
SHOW DATABASES -- 查看所有数据库
2.2 操作数据库表
自定义名称和字段 尽量使用 ``括起来
-- 目标：创建一个school数据库
-- 创建学生表（列，字段） 使用sql创建
-- 学号int 登录密码varchar(20) 姓名，性别varchar(2)， 出生日期(datetime)，家庭住址，email
--
-- AUTO_INCREMENT 自增
-- COMMENT 备注，注释
-- 字符创使用 单引号括起来！
-- 所有的语句后面加英文的逗号, ，最后一个字段加英文的分号;
-- primary key 主键，一般一个表只有一个唯一的主键！
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`general` 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`)
UNIQUE KEY `general` (`general`),
KEY `email` (`email`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
唯一索引和一般索引语法：UNIQUE KEY 给唯一键取的名字( 原名 ) / KEY/INDEX 给唯一键取的名字( 原名 )
格式
CREATE TABLE [IF NOT EXISTS] `表名`(
`字段名` 列类型 [属性] [索引] [注释],
……
`字段名` 列类型 [属性] [索引] [注释]
)[表类型][字符集设置][注释]
常用命令
SHOW CREATE DATABASE school -- 查看创建数据库的语句
SHOW CREATE TABLE student -- 查看student数据表的定义语句
DESC student -- 显示表的结构
三 数据库的数据类型
3.1 数值
| 简称的类型名 | 适用 | 长度（字节） |
|---|---|---|
| tinyint | 十分小的数据 | 1 |
| smallint | 较小的数据 | 2个字节 |
| mediumint | 中等大小的数据 | 3 |
| int | 标准的数据 | 4 |
| big | 较大的数据 | 8 |
| float | 浮点数 | 4 |
| double | 浮点数 | 8 |
| decima | 字符串形式的浮点数 | 16 |
int(3) 意思不是最大保存3位数，而是指最大显示宽度，即输入2、20、200，显示分别为002、020、200
decimal(5,3) 指的是总长度为5(总长度<=5)，但小数后有3位，如存数据5---->5.000总长度4；存数据123.7由于小数点后会补零至3位—>123.700总长度 超过5，将报错或者写成999.99}
decimal不存在精度损失，金融计算的时候，一般使用decimal
2.2.2 字符串
| 简称的类型名 | 适用 | 长度（字节） |
|---|---|---|
| char | 固定字符串 | 0~255 |
| varchar | 可变字符串 | 0~65535 |
| tinytext | 微型文本 | 0~255 |
| ext | 文本串 | 0~65535 |
在使用中char(5)指固定长度为5，即只能输入5位数据，否则报错，一般知道文本长度就用char
在使用中varchar(20)指的可变长度，只要长度<=20即可，这里存中文和英文存储的个数都可以使20个以内，中文不占3个字节，但在5.0之前就会
2.2.3 时间日期
| 简称的类型名 | 格式 | 适用 |
|---|---|---|
| date | YYYY-MM-dd | 日期格式 |
| time | HH:mm:ss | 时间格式 |
| datetime | YYYY-MM-DD HH:mm:ss | 最常用的时间格式 |
| timestamp | 时间戳 1970.1.1到现在的毫秒数 | |
| year | 年份表示 |
2.2.4 null
使用null进行计算，结果肯定为null
2.3、数据库的字段属性
![[(img-pnPnfXQ0-1690606873589)(D:\笔记\java笔记\MySQL.assets\image-20201111132905138.png)]](https://img-blog.csdnimg.cn/efb05731f8dd4e75bb936c66aae479ae.png)
- Unsigned：无符号（即不能为负数）的整数
- zerofill：0填充，不足的位数，使用0来填充
- 自增（auto incr）：通常理解为自动上一条记录的基础上+1（默认）
- 通常用来设计唯一的主键，
index必须是整数类型 - 可以在高级处自定义设计主键自增的起始值和步长
-
非空（Null、not null）：设置为
not null，如果不赋值会报错
-
默认：设置默认的值
/*每一个表都必须存在以下五个字段
id 主键
`version` 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
*/
2.5、数据表的类型
-- 关于数据库引擎
/*
INNODB 默认使用
MYISAM 早些年使用的
*/
| MYISAM | INNDB | |
|---|---|---|
| 事物支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间的大小 | 较小 | 较大，约为MYISAM的两倍 |
常规使用操作：
- MYISAM 节约空间，速度较快
- INNODB 安全性高，事务的处理，多表多用户操作
在物理空间存在的位置
所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库
本质上还是文件的存储！
MySQL引擎在物理文件上的区别
-
innoDB在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件（在data目录下）
-
创建表的引擎为MYISAM时生成的文件
- *.frm 表结构的定义文件
- *.MYD 数据文件（data）
- *.MYI 索引文件（index）
设置数据库表的字符集编码
CHARSET=utf8
不设置话，会是mysql默认的字符集编码 Latin1（不支持中文）
在my.ini中配置默认的编码
character-set-server=utf8
2.6、修改删除表
修改
修改表名语法： ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1
增加表的字段语法：ALTER TABLE 表名 ADD 新增字段名 数据类型
ALTER TABLE teacher1 ADD age INT(11)
用modify修改表字段语法：ALTER TABLE 表名 MODIFY 字段名 数据类型[]
ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- modify不能重命名，只能修改的是约束和数据类型
用change修改表字段语法：ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型][]
ALTER TABLE teacher1 CHANGE age agel INT(11) -- change可以重命名、约束和数据类型，但必须要写上旧名和新名，即使名字都一样
删除表的字段语法：ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher1 DROP agel
删除表语法：DROP TABLE 表名
DROP TABLE IF EXISTS teacher1
所有的创建和删除操作尽量加上判断，以免报错
注意点：
- `` 反引号
- 注释 --(空格) /**/
- 所有符号用英文
3、MySQL数据管理
3.1、外键（了解）
方式一、在创建表的时候，增加约束（比较复杂）
CREATE TABLE IF NOT EXISTS `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(10) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 学生表的grad字段要去引用年级表的gradeid
-- 定义外键key
-- 给这个外键添加约束（执行引用） references 引用
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(20) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(20) DEFAULT '匿名' COMMENT '名字',
`gender` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` DATETIME DEFAULT '2020-11-13' COMMENT '生日',
`gradeid` INT(10) NOT NULL COMMENT '年级',
PRIMARY KEY(`id`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
删除有外键关系的表的时候，必须要先删除引用别人的表（从表），再删除被引用的表（主表）
方式二：创建没有约束的表成功后，再创建约束
-- 学生表的grad字段要去引用年级表的gradeid
-- 定义外键key
-- 给这个外键添加约束（执行引用） references 引用
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(20) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(20) DEFAULT '匿名' COMMENT '名字',
`gender` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` DATETIME DEFAULT '2020-11-13' COMMENT '生日',
`gradeid` INT(10) NOT NULL COMMENT '年级',
PRIMARY KEY(`id`),
KEY `FK_gradeid` (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 创建表时没有外键关系，创建后添加外键
ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
以上的操作都是物理外键，数据库级别的外键，我们不建议使用，仅了解即可！
最佳实践
- 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
- 我们想使用多张表的数据，想使用外键就用程序去实现
3.2、DML语言（背下来）
**数据库意义：**数据存储，数据管理
DML语言：数据操作语言
- Insert
- update
- delete
3.2.1、添加（insert）
-- 插入语句（添加）
-- insert into 表名（[字段名1,2,3]） values（'值1')，('值2')，('值3'）
INSERT INTO `grade`(`gradename`) VALUES('大四')
-- 由于grade表中的主键gradeid设置了自增，所以我们可以省略不写，
-- 但如果不写后面的gradename，由于是顺序存储，系统默认为存入的数据是gradeid的就会报错
INSERT INTO `grade`(`gradename`) VALUES('大五') -- 省略写法
INSERT INTO `grade`(`gradeid`,`gradename`) VALUES(NULL,'大五') -- 实际运行
-- 所以一般写插入语句，我们一定要数据和字段一一对应
-- 插入多个字段
INSERT INTO `grade`(`gradename`) VALUES('大一'),('大二')
INSERT INTO `student`(`name`,`gradeid`) VALUES('张三','大一'),('万物','大二'),('玩具呀','大三')
总结：
- 语法：
insert into 表名([字段名1,2,3]) value(s)('值1','值2','值3')【添加一条数据时，可以使用value】 - 字段和字段之间使用英文逗号 隔开！
- 字段时可以省略的，但是后面的值必须要一一对应。
- 可以同时插入多条数据，values后面的值，需要使用隔开即可，values(),(),()·····
3.2.2、修改（update）
-- 修改学员的名字
-- 指定条件的情况下
UPDATE `student` SET `name`='时代' WHERE `id`=1
-- 不指定条件的情况下，会改动所有表中对应的数据
UPDATE `student` SET `name`='积分'
-- column_name（即col常说的列）的常用写法
-- 语法：UPDATE '表名' set column_name=value,[column_name=value],…… where [条件]
-- 修改多个属性，用逗号隔开
UPDATE `student` SET `name`='开心',`gradeid`='难过' WHERE `id`=1
Where 子句
搜索 empno 等于 7900 的数据：
Select * from emp where empno=7900;
1、Where +条件（筛选行）
条件：列，比较运算符，值
比较运算符包涵：= > < >= ,<=, !=,<> 表示（不等于）
Select * from emp where ename='SMITH';
例子中的 SMITH 用单引号引起来，表示是字符串，字符串要区分大小写。
2、逻辑运算
And(或者&&): 与 同时满足两个条件的值。
Select * from emp where sal > 2000 and sal < 3000;
查询 EMP 表中 SAL 列中大于 2000 小于 3000 的值。
Or(或者||): 或 满足其中一个条件的值
Select * from emp where sal > 2000 or comm > 500;
查询 emp 表中 SAL 大于 2000 或 COMM 大于500的值。
Not(或者 ! ) : 非 满足不包含该条件的值。
select * from emp where not sal > 1500;
查询EMP表中 sal 小于等于 1500 的值。
逻辑运算的优先级：
() not and or
3、特殊条件
1.空值判断： is null
Select * from emp where comm is null;
查询 emp 表中 comm 列中的空值。
2.between and (在 之间的值)
Select * from emp where sal between 1500 and 3000;
查询 emp 表中 SAL 列中大于 1500 的小于 3000 的值。
注意：大于等于 1500 且小于等于 3000， 1500 为下限，3000 为上限，下限在前，上限在后，查询的范围包涵有上下限的值。
3.In
Select * from emp where sal in (5000,3000,1500);
查询 EMP 表 SAL 列中等于 5000，3000，1500 的值。
4.like
Like模糊查询
Select * from emp where ename like 'M%';
查询 EMP 表中 Ename 列中有 M 的值，M 为要查询内容中的模糊信息。
- % 表示多个字值，_ 下划线表示一个字符；
- M% : 为能配符，正则表达式，表示的意思为模糊查询信息为 M 开头的。
- %M% : 表示查询包含M的所有内容。
- %M_ : 表示查询以M在倒数 第二位的所有内容。
语法：UPDATE '表名' set column_name=value,[column_name=value],…… where [条件]
注意
-
column_name（即col常说的列） 是数据库的列，尽量带上``
-
条件，筛选的条件，如果没有指定，则会修改所有的列
-
value，是一个具体的值，也可以是一个变量。举例：
-
update `student`set `birthday`=current_time where `name`='开心'
-
-
多个设置的属性之间，使用英文逗号隔开
3.2.3、删除（delete）
delete命令
语法：delete from 表名 [where 条件]
-- 删除数据（避免这样写，会全部删除)
DELETE FROM `student`
-- 删除指定数据
DELETE FROM `student` WHERE id =1
truncate命令（截短，缩短，删节）
作用：完全清除一个数据库表，表的结构和索引约束不会变！
语法：TRUNCATE table 表名
--
TRUNCATE table `student`
truncate和delete的区别：
- 相同点：都能删除数据，且不会删除表结构
- 不同点：
- truncate 会重新设置自增列，计数器会归零。而delete则不会归零，即使删除了表数据，当再次插入数据时，仍会继续之前计数器的大小开始计数
- truncate速度更快，且不留痕迹。而delete是逐行删除，速度慢且会发送报告留下痕迹
拓展（了解即可）：delete的删除问题：当重启数据库时发生的现象
- 引擎为InnoDB 自增列会从1开始（InnoDB是存在内存当中的，所以断电即失）
- 引擎为MYISAM 继续从上一个自增量开始（存在文件中，不会丢失）
4、DQL查询数据（究极重要）
本章的所有建表数据均在目录：D:\笔记\java笔记\MySQL.assets\4、DQL查询数据建表数据.sql
4.1、DQL
（Data Query Language：数据查询语言）
- 所有的查询操作都是它 Select
- 数据库中最核心的语言
- 使用频率最高的语句
4.2、查询指定字段
-- 查询全部学生信息 SELECT 字段 FROM 表名
SELECT * FROM `student`
-- 查询指定字段
SELECT `studentno`,`studentname` FROM `student`
-- 给查询的结果起别名 可以给字段起别名，也可以给表起别名 AS可省略
SELECT `studentno` AS 学号,`studentname` AS 别名 FROM `student` AS s
-- 函数 Concat (str1,str2,……) 令查询的语句结果的呈现发生变化
SELECT CONCAT('姓名:',`studentno`,`studentname`) AS 别字 FROM `student` a
查询结果为： [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QqtTZhqf-1690606873590)(D:\笔记\java笔记\MySQL.assets\image-20201114183234694.png)]
语法：select 需要查询的字段名 [AS] [给查询的字段名取的别名] from 表名 [AS] [给表取的别名]
有的时候，列的名字不是那么见名知意，我们就需要起别名，起名时的AS可以省略，但为了代码浏览，建议保留
去重 distinct
作用：去除SELECT查询出来的结果中重复的数据，每条数据只显示一次（例张三参加运动会中的20个项目，而要查询的事都有谁参加过运动会，那么查询结果张三只显示一次）
-- 第一步：查询有哪些同学参加了考试
SELECT `studentno` FROM `result`
-- 第二步：发现有重复数据，去重
SELECT DISTINCT `studentno` FROM `student`
数据库的列表达式
- 通过version()函数，查询mysql系统版本
SELECT VERSION() --查询系统版本
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EXvBFxTj-1690606873591)(D:\笔记\java笔记\MySQL.assets\image-20201114201909937.png)]
- 通过计算表达式，用来计算普通计算
SELECT 1+1 AS 计算结果
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SWbDvQxK-1690606873591)(D:\笔记\java笔记\MySQL.assets\image-20201114201934993.png)]
- 通过变量查询自增的步长(自增)
SELECT @@auto_increment_increment AS 步长结果
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4CvgfMJJ-1690606873591)(D:\笔记\java笔记\MySQL.assets\image-20201114202130136.png)]
- 学院考试成绩+1分查看
SELECT `studentno`,`studentresult`+1 FROM `result`
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fe0uWpzF-1690606873592)(D:\笔记\java笔记\MySQL.assets\image-20201114211043487.png)]
数据库中的表达式：文本值，列，null，函数，计算表达式，系统变量……
语法：select 表达式 from 表名
4.3、where条件子句（详细请看3.3.2的where子句）
4.4、联表查询
4.4.1、Join
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wzaULdx1-1690606873592)(D:\笔记\java笔记\MySQL.assets\image-20201115163224209.png)]
七种Join概念：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8zFVIitd-1690606873592)(D:\笔记\java笔记\MySQL.assets\20181103160140252.png)]
注意：A left join B 表示的是A为主表，A里要查找的内容都显示，B为从表，B里的内容去匹配A显示的内容，如果B表里没有，则自动不上null再显示
4.4.2、where和on的区别
-
在左(右)外连接时（left/right Join），on是在生成临时表时使用的条件，它不管on中的条件是否为真，都会先返回左（右）表中的记录，所以即使右(左)表的记录不满足on里的条件，依旧会显示出来（只是不满足的就补null）
-
如需要查询左表中的studentno和studentname并 左 连接 右 表中的subjectno、examdate和studentresult：
SELECT s.`studentno` ,`studentname`,`subjectno`,`examdate`,`studentresult` FROM `student` AS s LEFT JOIN `result` AS r ON s.studentno = r.studentno- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mvlEyXt5-1690606873593)(D:\笔记\java笔记\MySQL.assets\image-20201115173456021.png)]
-
-
而在外连接中，where是在临时表生成后，再对临时表进行过滤的条件。所以只要（left/right Join）在生成的左右表不满足where条件，则都不显示
-
如需要查询左表中的studentno和studentname并 左 连接 右 表中的subjectno、examdate和studentresult：
SELECT s.`studentno` ,`studentname`,`subjectno`,`examdate`,`studentresult` FROM `student` AS s LEFT JOIN `result` AS r ON s.studentno = r.studentno -- 这里是为了显示列属性，若不加会报错 WHERE s.studentno = r.studentno- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jAp5niad-1690606873593)(D:\笔记\java笔记\MySQL.assets\image-20201115174038206.png)]
-
总结：on是建立连接，where是对结果的筛选
联表查询思路：
- 分析需求，分析查询的字段来自哪些表，（连接查询）
- 确定使用哪种连接查询？7种
确定交叉点（这两个表中哪个数据是相同的）
判断条件：如：学生表 studentno = 成绩表 studentno
举个例子：多表联表查询
-- （思考题）：查询参加考试的学生信息：学号，学生姓名，科目名，分数）
SELECT st.`studentno`,`studentname`,`studentresult`,sb.`subjectno`
FROM `student` st LEFT JOIN `result` r -- 先从student表左连接result表（为了student为主表，主表的所有内容显示）
ON st.`studentno`=r.`studentno`
INNER JOIN `subject` sb --再从以上得到的两表连接结果和subject表做内连接（取两者的交集）
ON r.`subjectno` =sb.`subjectno`
语法：from 表名[别名] left|right|inner join 表名[别名] on 查询条件
4.4.3、自连接
自己的表和自己的表连接，核心：一张表拆为两张一样的表即可
先拆第一个表（父类表）
| categoryid | categoryName |
|---|---|
| 2 | 信息技术 |
| 3 | 软件开发 |
| 5 | 美术设计 |
子类
| pid | categoryid | categoryName |
|---|---|---|
| 3 | 4 | 数据库 |
| 2 | 8 | 办公信息 |
| 3 | 6 | web开发 |
| 5 | 7 | ps技术 |
操作：查询父类对应的子类关系
| 父类 | 子类 |
|---|---|
| 信息技术（categoryid=2） | 办公信息（pid=2） |
| 软件开发（categoryid=3） | 数据库（pid=3） |
| 软件开发（categoryid=3） | web开发（pid=3） |
| 美术设计（categoryid=5） | ps技术（pid=5） |
例如：
-- =========自查询==========
SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid`
即自查询:把一张表看成两张一模一样的表
4.5、分页和排序
4.5.1、排序
-- ==========分页 limit 和排序 order by==========
-- ORDER BY 通过哪个字段排序就怎么排
-- 排序：升序 ASC（Ascending order）， 降序 DESC（descending order）
SELECT r.`studentresult`,st.`studentno`,st.`studentname`,sb.`subjectno`
FROM `student` st
LEFT JOIN `result` r
ON st.`studentno`=r.`studentno`
INNER JOIN `subject` sb
ON sb.`subjectno`=r.`subjectno`
ORDER BY studentno DESC
语法：ORDER BY 需要排序的字段 DESC|ASC
4.5.2、分页
当数据太多时，可以使用分页（缓解数据库压力）
但也可以使用瀑布流（例如淘宝商品永远到不了底，比较暴力）
SELECT st.`studentno`,st.`studentname`,sb.`subjectno`,r.`studentresult`
FROM `student` st
LEFT JOIN `result` r
ON st.`studentno`=r.`studentno`
INNER JOIN `subject` sb
ON sb.`subjectno`=r.`subjectno`
WHERE r.`studentresult`>80
ORDER BY r.`studentresult` DESC
LIMIT 1,3 -- 跳过的的行数，每页只显示的行数
没加limit前，总共有8条数据[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uJpZQ1bq-1690606873593)(D:\笔记\java笔记\MySQL.assets\image-20201117203356342.png)]
加了limit后：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iFFBbFYY-1690606873594)(D:\笔记\java笔记\MySQL.assets\image-20201117203447751.png)]
语法：limit 跳过的数据条数(如原本数字‘28’数据在第7行，那要想直接显示28，则跳过6行即可在第一行显示数据28)，页面显示多少条数据
一般要像页面这样显示[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0zM7dQkP-1690606873594)(D:\笔记\java笔记\MySQL.assets\image-20201117203719290.png)]
-- 第一页 limit 0,5 跳过0条数据，每个页面容纳5条数据
-- 第二页 limit 5,5 第二页会从第六条数据开始显示，所以跳过前5，每个页面容纳5条数据
-- 第三页 limit 10,5 ………………
-- 第n页 limit 5(n-1),5 limit (n-1)*pageSize,pageSize
-- 【pageSize：页面容量大小】
-- 【n：当前页码】
-- 【数据总数/页面容量大小= 总页数（向上取整)】
4.6、子查询（由里及外：先由最里面那层开始想）
where（这个值是计算出来的）
本质：在where语句中嵌套一个子查询语句
例子一：
-- =========1、查询 高等数学-3 的所有考试结果（学号,科目编号，成绩），降序排列
-- 方式一：使用联表查询
SELECT r.`studentno`,`subjectname`,r.`subjectno`,`studentresult`
FROM `result` r
INNER JOIN `subject` sb
ON r.`subjectno`=sb.`subjectno`
WHERE subjectname='高等数学-3'
-- 方式二：使用子查询（由里及外：先由最里面那层开始想）
/*思路：先看条件需要 高等数学-3 出自subject表
而条件 学号,科目编号，成绩 都出自result表；
那么subject表和result表关联的就是 subjectno
则在子查询中就以subjectno为索引在subject表中查找
返回的结果作为result表筛选的依据
*/
SELECT r.`studentno`,r.`subjectno`,`studentresult`
FROM result r
WHERE subjectno=(
SELECT `subjectno` FROM `subject` WHERE `subjectname`='高等数学-3'
)
例子二：
-- 查询课程为 高等数学-1 且分数不小于80的同学的学号和姓名
-- 方法一：使用联表查询
SELECT st.`studentno`,`studentname`
FROM `student` st
INNER JOIN `result` r
ON st.`studentno`=r.`studentno`
INNER JOIN `subject` sb
ON sb.`subjectno`=r.`subjectno`
WHERE `studentresult`>=80 AND `subjectname`='高等数学-1'
-- 方法二：使用子查询（由里及外：先由最里面那层开始想）
SELECT st.`studentno`,`studentname`
FROM `student` st
INNER JOIN `result` r
ON st.`studentno`=r.`studentno`
WHERE `studentresult`>=80 AND
`subjectno`=(
SELECT `subjectno` FROM `subject` WHERE `subjectname`='高等数学-1'
)
-- 方法三：双重自查询（由里及外：先由最里面那层开始想）
/*首先看条件 高等数学-1且分数不小于80 一个出自result表，一个出自subject表
那么谁做最里面那层呢？
因为subject表只能返回和题目相关的subjectname，而result可以返回题目相关的studentresult和studentno
所以只能由subject作为最里面的条件换得`subjectno`作为result表的筛选条件，再和result表的studentresult作为student表的筛选条件最终返回结果
*/
【③SELECT `studentno`,`studentname` FROM `student` WHERE `studentno`IN (
【②SELECT `studentno` FROM `result` WHERE `studentresult`>=80 AND `subjectno`= (
【①SELECT `subjectno` FROM `subject` WHERE `subjectname`='高等数学-1'】
)】
)】
4.7、分组和过滤
-- 查询不同课程的平均分，最高分，最低分
-- 核心：（根据不同的课程分组）
SELECT r.`subjectno`, AVG(`studentresult`) as 平均分,MAX(`studentresult`) as 最高分,MIN(`studentresult`) as 最低分
FROM `result` r
INNER JOIN `subject` sb
ON r.`subjectno`=sb.`subjectno`
GROUP BY r.`subjectno` -- 通过什么字段来分组
HAVING 平均分>=80
由于上面的avg等函数式聚合函数，where用不了，所以需要用having
4.8、select小结
Select 完整的语法：
selet[ALL | DISTINCT] -- 查询所有/去重
{* | table.* |[table.field[as alias1][,table.field2[as alias2]][……]]}
FROM table_name [as table_alias]
[left |right |inner join table_name2 on ……] -- 联合查询
[WHERE……] -- 指定结果需满足的条件（不能跟聚合函数）
[GROUP BY……] -- 指定结果按照哪几个字段来分组
[HAVING ……] -- 过滤分组的记录必须满足的次要条件（可以跟聚合函数）
[ORDER BY ……] -- 指定查询记录按一个或多个条件排序 desc降序 asc升序
[LIMIT {[offest,Jrow_count | row_countOFFSET offset]};
-- 指定查询的记录从哪条至哪条
注意：前后顺序不能换，[]代表可选,{}代表必选
5、MySQL函数
网址：https://dev.mysql.com/doc/refman/8.0/en/functions.html
5.1、常用函数
-- ==========常用函数===========
-- 数学运算
SELECT ABS(-1) -- 绝对值
SELECT CEILING(9.2) -- 向上取整
SELECT FLOOR(9.6) -- 向下取整
SELECT RAND() -- 返回0~1之间的随机数
SELECT SIGN(0) -- 判断一个数的符号 0返回0 负数返回-1 整数返回1
-- 字符串
SELECT CHAR_LENGTH('这行字不用数了就十个') -- 字符串长度
SELECT CONCAT('这','里','是','b','站')
SELECT INSERT('一二三四五六七',1,2,'520') -- 查询，从某个位置开始把新字符串替换原字符串
SELECT UPPER('hello') -- 大写字母
SELECT LOWER('Owner') -- 小写字母
SELECT INSTR('大力接待费','接') -- 返回第一次出现的子字符串
SELECT REPLACE('兼职就能成功','兼职','屁') -- 替换出现的指定字符串
SELECT SUBSTR('骄傲ID设计费呢',5,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()
5.2、聚合函数（常用）
| 函数名称 | 描述 |
|---|---|
| COUNT() | 计数 |
| SUM() | 求和 |
| AVG() | 平均值 |
| MAX() | 最大值 |
| MIN() | 最小值 |
| ······· | ······ |
-- ==========聚合函数============
-- COUNT()函数，都能够统计 表中的数据（想查询一个表中有多少个记录，就是用count()）
SELECT COUNT(studentname) FROM student -- 18 count（字段） 返回指定列名的个数，会忽略所有null值
SELECT COUNT(*) FROM student -- count(*),不会忽略null值，本质 计算行数
SELECT COUNT(1) FROM result -- 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
5.3、数据库级别的MD5加密（扩展）
MD5：信息摘要算法
主要增强算法复杂度和不可逆性。
MD5不可逆，具体的值的md5是一样的
MD5破解网站的原理，背后有一个字典，MD5加密后的值，加密的前值
?```sql
-- ========测试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
-- 明文密码
INSERT INTO `testmd5` VALUES(1,'zhangsan','123456'),(2,'lisi','234567'),(3,'wangwu','345678')
-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id=1
UPDATE testmd5 SET pwd=MD5(pwd) -- 加密全部的密码
-- 插入数据时直接加密
INSERT INTO testmd5 VALUES(4,'小明',MD5('456798'))
-- 如何校验123456：将用户传递进来的密码，进行md5加密，再比对加密后的值
SELECT * FROM testmd5 WHERE `name`='小明' AND pwd=MD5('456798')
6、事务（重点）
要么都成功，要么都失败
1、SQL执行 A给B转账 A 1000 ---->转200 B 0
2、SQL执行 B收到A的钱 A 800 —>B 200
必须1、2都成功交易才完成，任意一个失败都导致失败
核心：将一组SQL放在一个批次中执行
事务原则：ACID原则
-
A(Atomicity)：原子性
- 指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
-
C(Consistency)：一致性
- 指事务前后数据的完整性必须保持一致，，，总数不变。
-
I(Isolation)：隔离性
- 指多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
- **脏读：**一个事务读取了另外一个事务未提交的数据。
- **不可重复读：**在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）
- **虚(幻)读：**是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。（一般是行影响，多了一行）
- 指多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
-
D(Durability)：持久性
- 表示事务结束后的数据不随着外界原因（例如服务器宕机或断电）导致数据丢失
- 事务还没提交，外界原因发生，数据状态应复原
- 事务已经提交，外界原因发生，数据状态应变为提交后的数据
- 表示事务结束后的数据不随着外界原因（例如服务器宕机或断电）导致数据丢失
执行事务
-- ==================事务=================
-- mysql是默认开启事务自动提交的
SET autocommit=0 -- 自动提交关闭，开启手动处理事务
SET autocommit=1 -- 地洞提交开启（默认）
-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的sql都在同一个事务内
-- 提交：持久化（成功！）
COMMIT
-- 回滚：回到原来的样子（失败！）
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 开启自动提交
SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销指定的保留点
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4PPgtFYJ-1690606873594)(D:\笔记\java笔记\MySQL.assets\image-20201119211503196.png)]
模拟场景
-- 转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
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 `name`='A' -- A减500
UPDATE account SET money=money+500 WHERE `name`='B' -- B减500
-- 如果成功
COMMIT; -- 提交事务
-- 如果失败
ROLLBACK; -- 回滚
SET autocommit=1 -- 恢复默认值
在java中事务的实现
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VRLnRW0x-1690606873595)(D:\笔记\java笔记\MySQL.assets\image-20201120215020533.png)]
7、索引
MySQLT官方对索引的定义为：索引（index）是帮助 MYSQLR高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构
7.1、索引的分类
一、单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。 这里不要搞混淆了
-
**普通索引(key / Index)：**MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一 点。
-
唯一索引(UNIQUE KEY)：索引列中的值必须是唯一的，但是允许为空值。
-
主键索引(PRIMARY KEY)：是一种特殊的唯一索引，不允许有空值。（主键约束，就是一个主键索引）。
主键索引与唯一索引的区别：
-
主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。
-
主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
-
唯一性索引列允许空值，而主键列不允许为空值。
-
主键索引在创建时，已经默认为非空值+ 唯一索引了。
-
一个表最多只能创建一个主键索引，但可以创建多个唯一索引。
-
主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。
-
主键可以被其他表引用为外键，而唯一索引不能。
二、组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。例如，这里由id、name和age3个字段构成的索引，索引行中就按id/name/age的顺序存放，索引可以索引下面字段组合(id，name，age)、(id，name)或者(id)。如果要查询的字段不构成索引最左面的前缀，那么就不会是用索引，比如，age或者（name，age）组合就不会使用索引查询。
三、全文索引(FullText)：全文索引，==只有在MyISAM引擎上才能使用==，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。全文索引（就类似像平时的Ctrl+F）的使用涉及了很多细节，我们只需要知道这个大概意思。一般开发中，不会用到全文索引，因为其占用很大的物理空间和降低了记录修改性，故较为少用。
创建索引的语句：
1、直接创建索引：
CREATE INDEX index_name ON table(column(length)); -- 创建普通索引
CREATE UNIQUE INDEX indexName ON table(column(length)); -- 创建唯一索引
CREATE FULLTEXT INDEX index_content ON article(content); -- 全文索引
2、修改表结构的方式添加索引：
ALTER TABLE table_name ADD INDEX index_name ON (column(length)); -- 创建普通索引
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length)); -- 创建唯一索引
ALTER TABLE `table` ADD INDEX name_city_age (name,city,age); -- 组合索引
ALTER TABLE article ADD FULLTEXT index_content(content); -- 全文索引
3、创建表的时候创建索引：
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER NOT NULL ,
`content` text CHARACTER NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
INDEX index_name (title(length))
); -- 普通索引
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER NOT NULL ,
`content` text CHARACTER NULL ,
`time` int(10) NULL DEFAULT NULL ,
UNIQUE indexName (title(length))
); -- 唯一索引
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) NOT NULL ,
PRIMARY KEY (`id`)
); -- 主键索引
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER NOT NULL ,
`content` text CHARACTER NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
FULLTEXT (content)
); -- 全文索引
4、删除索引：
DROP INDEX index_name ON table;
5、显示所有索引：
show index from table_name;
6、基础语法：
-- =========索引的使用========
-- 1、在创建表的时候给字段增加索引
-- 2、创建完毕后，增加索引
-- 显示所有的索引信息
SHOW INDEX FROM student
-- 增加一个全文索引(索引名，列名）
ALTER TABLE student ADD FULLTEXT INDEX `studentname`(`studentname`);
-- EXPLAIN 用来分析SQL执行的状况（如看看查询语句时总共查询了多少行才查到等）
EXPLAIN SELECT * FROM student -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('张')
索引的优缺点：
1、优点：创建索引可以大大提高系统的性能。
第一、通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
第二、可以大大加快 数据的检索速度，这也是创建索引的最主要的原因。
第三、可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
第四、在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
第五、通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
2、缺点：
? 增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？这是因为，增加索引也有许多不利的一个方面:
? 第一、创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
? 第二、索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么 需要的空间就会更大。
? 第三、当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
7.2、测试索引
目的：创建一个表并插入百万条数据，比较没有索引和有索引时在这一百万的数据中查找数据的耗时
- 创建测试表app_user
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
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),'122025658@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() -- 执行此函数 生成一百万条数据
- 为每条数据添加索引
-- CREATE INDEX 索引名 on 表(字段)
-- 常用的索引命名方式：id_表名_字段名
CREATE INDEX id_app_user_name ON app_user(`name`);
对比
`SELECT * FROM app_user WHERE `name`='用户500000' -- 查找数据
EXPLAIN SELECT * FROM app_user WHERE `name`='用户500000'; -- 分析SQL执行的状况
添加索引前查找数据：
- 总耗时：0.663sec
- 总共执行条数：993037[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bnRL4jbU-1690606873595)(D:\笔记\java笔记\MySQL.assets\image-20201120213934841.png)]
添加索引后查找数据：
- 总耗时：0sec
- 总共执行条数：1[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CIXwhwpo-1690606873595)(D:\笔记\java笔记\MySQL.assets\image-20201120214136629.png)]
如何删除索引：
ALTER TABLE `school`.`app_user` DROP INDEX `id_app_user_name`;
或者直接右击表–改变表–索引–删除–保存
索引在小数据的时候，用处不大；处理大数据是，区别十分明显
7.3、索引原则
- 索引不是越多越好
- 不要对进程变动数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上！
索引的数据结构
Hash类型的索引
Btree：InnDB的默认数据结构
详细索引的博客：https://blog.codinglabs.org/articles/theory-of-mysql-index.html
8、数据库备份
8.1、用户管理
SQLyog可视化管理
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gNIZbE6U-1690606873596)(D:\笔记\java笔记\MySQL.assets\image-20201120220201937.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xKRXsy2D-1690606873596)(D:\笔记\java笔记\MySQL.assets\image-20201120220251374.png)]
SQL命令操作
用户表：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tU8mrIJl-1690606873596)(D:\笔记\java笔记\MySQL.assets\image-20201120220432177.png)]
**本质：**对这张表进行增删改查
- 创建用户语法：`CREATE USER
-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER LONG IDENTIFIED BY '123456'
CREATE USER `jidao` IDENTIFIED BY '1234567'
-- 修改密码（修改当前用户密码）
SET PASSWORD=PASSWORD('123456')
-- 修改密码（修改制定用户密码）
SET PASSWORD FOR jidao = PASSWORD('121212')
-- 重命名 RENAME USER 原名 TO 新名
RENAME USER wenlong TO wenlong2
-- 用户授权 ALL PRIVILEGES 全部权限(或库，表)
-- ALL PRIVILEGES除了给别人授权这个权利外，其他都可以
GRANT ALL PRIVILEGES ON *.* TO wenlong2
-- 查询制定用户的权限
SHOW GRANTS FOR wenlong2
-- Root用户权限：GRANT ALL PRIVILEGES ON *.* To 'root'@'localhost' WITH GRANT OPTION
-- 撤销权限 REVOKE 那些权限 在哪个库撤销 给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM wenlong2
-- 删除用户
DROP USER wenlong2
8.2、MySQL备份
为什么要备份：
- 保证重要的数据不丢失
- 数据转移
数据库备份方式
-
直接拷贝物理文件
-
在SQLyog这种可视化工具中手动导出
- 在想要导出的表或者库中-------右键-------选择备份或导出
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IOBMUYox-1690606873597)(D:\笔记\java笔记\MySQL.assets\image-20201121161736727.png)]
-
在DOS界面使用命令行导出 mysqldump 命令行使用
-
导出
-
# 在DOS中导出单/多个表 # mysqldump -h 目标ip -u 用户名 -p 密码 数据库 表1 表2 表3…… > 物理磁盘位置/文件名 mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql #导出数据库 # mysqldump -h 目标ip -u 用户名 -p 密码 数据库 > 物理磁盘位置/文件名 mysqldump -hlocalhost -uroot -p123456 school >D:/a.sql -
导入
-
-
# 在DOS界面登录mysql用户的情况下 # 先登录 mysql -uroot -p123456 # 若导入的是表，则切换到指定的数据库；若导入的是数据库则直接导入即可 use school; # 导入文件 source 文件路径 source d:/a.sql # 没有登录的情况下 mysql -u用户名 -p密码 库名 < 备份文件路径
9、权限管理
9.1、为什么需要设计
当数据库比较复杂的时候，我们就需要设计
糟糕的数据库设计：
- 数据冗余，浪费空间
- 数据库插入和删除都会麻烦、异常【屏蔽使用物理外键】
- 程序的性能差
良好的数据库设计：
- 节省内存空间
- 保证数据的完整性
- 方便我们开发系统
软件开发中，关于数据库的设计
- 分析需求：分析业务和需要处理的数据库的需求
- 概要设计：设计关系图E-R图
设计数据库的步骤：（个人博客）
-
收集信息，分析需求
- 用户表（用户登录注销、用户的个人信息，写博客、创建分类）
- 分类表（文章分类，谁创建的）
- 文章表（文章的信息）
- 评论表
- 友链表（友情信息）
- 自定义表（系统信息，某个关键的字，或者一些主字段） key：value
- 说说表（发心情，id···content···
-
标识实体（把需求落地到每个字段）
-
标识实体之间的关系
- 写博客要用到的表：user—>blog
- 创建分类要用到的表：user ->category
- 关注用到的表：user -> user
- 友链用到的表：links
- 评论用到的表：user->user->blog
9.2、三大范式
为什么需要数据规范化？
- 信息会重复
- 更新会导致异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 丢失一下有效的信息
三大范式
第一范式（1NF）
原子性：保证每一列不可再分
第二范式（2NF）
前提是要满足第一范式
第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）
每张表只描述一件事情
第三范式（3NF）
前提是要满足第一范式 第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关
（目的：规范数据库的设计）
规范性 和 性能的问题‘
关联查询的表不得超过三张表
- 考虑商业化的需求和目标，（成本，用户体验!）数据库的性能更加重要
- 在规范性能的问题的时候，需要适当的考虑一下 规范性！
- 故意给某些表增加一些冗余的手段。（从多表查询中变为单表查询）
- 故意增加一些计算列（从大数据量降低为小数据量的查询：索引）
10、JDBC
10.1、数据库驱动
驱动：声卡、显卡、数据库
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DvzNkeUq-1690606873597)(D:\笔记\java笔记\MySQL.assets\image-20201121190257749.png)]
我们的程序会通过 数据库 驱动，和数据库打交道！
10.2、JDBC
SUN公司为了简化开发人员（对数据库的统一）的操作，提供了一个（Java操作数据库）规范，俗称JDBC
这些规范的实现由具体的厂商去做
对于开发人员来说，我们只需要掌握JDBC接口的操作即可！
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nr6PRFTp-1690606873597)(D:\笔记\java笔记\MySQL.assets\image-20201121190723179.png)]
要学会JDBC要先知道两个包：java.sql、javax.sql
还需要导入一个数据库驱动mysql-connector-java-5.1.47.jar，到maven线上仓库搜索MySQL Connector/J即可找到
10.3、第一个JDBC程序
创建测试数据库
-- 创建第一个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、在IDEA中创建一个普通项目
2、导入数据库驱动
- 创建文件夹lib，并往里粘贴上要导入的mysql-connector-java-5.1.47.jar jar包
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-E7UcciK5-1690606873598)(D:\笔记\java笔记\MySQL.assets\image-20201121194906238.png)]
3、新建java文件编写测试代码
package com.Long.lesson01;
import java.sql.*;
public class JdbcFirstDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver"); //固定写法，加载的驱动是先前导入好的驱动目录
//2.添加用户信息和url(数据库的路径)
//useUnicode=true&characterEncoding=utf8&useSSL=true
//jdbc:mysql(协议)://主机地址:端口号/数据库名?参数1&参数2&参数3
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false"; //useUnicode使用Unicode字符集
//useSSL使用安全协议用于兼容版本
String username = "root"; //你连接数据库时的用户名
String password ="123456"; //连接数据库时的密码
//3.通过用户信息和url(数据库的路径)连接到数据库。 Connection 代表连接数据库
Connection connection = DriverManager.getConnection(url, username, password);
//4.连接数据库后，创建Statement(陈述，报告，说明)对象，返回statement对象 用于执行SQL
Statement statement = connection.createStatement();
//5.通过执行查询来执行SQL对象，并通过返回的结果对象集输出结果
String sql="SELECT * FROM users";
//execut执行 Query询问 执行查询
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();
}
}
注意：useSSL就是使用JDBC跟你的数据库连接的时候，你的JDBC版本与MySQL版本不兼容，MySQL的版本更高一些，在连接语句后加上“useSSL=‘true’” ，就可以连接到数据库了。更高版本。
步骤总结
1、加载驱动
2、连接数据库DriverManager（驱动管理）
3、获取执行sql的对象 Statement
4、（执行完后）获得返回的结果集
5、释放连接
解释：
DriverManager
// DriverManager.registerDriver(new com.mysql.jdbc.Driver()); 不建议这样写，因为Driver的源码已经注册了Driver，这么写相当于注册了两次
Class.forName("com.mysql.jdbc.Driver"); //固定写法，加载驱动，这个类在添加的驱动对应的路径里面
Connection connection = DriverManager.getConnection(url, username, password);
//connection 代表数据库
//就可以设置数据库自动提交、事务提交、事务回滚
connection.rollback();
connection.commit();
connection.setAutoCommit();
URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
//mysql默认端口---3306
//jdbc:mysql(协议)://主机地址:端口号/数据库名?参数1&参数2&参数3
//Oracle默认端口-----1521
//jdbc:oracle:thin:@localhost:1521:sid
Statement执行SQL的对象（还有PrepareStatement也是执行SQL的对象）
String sql="SELECT * FROM users";//编写SQL
statement.executeQuery();//查询操作返回resultset结果集
statement.execute();//执行任何SQL
statement.executeUpdate();//更新、插入、删除都是用这个，返回一个受影响的行数
statement.executeBatch();//放多个SQL进去同时执行
ResultSet查询的结果集：封装了所有的查询结果
获得指定的数据类型
resultSet.getObject();//在不知道列的类型的情况下用Object
resultSet.getString();//如果知道列类型就用指定的类型
resultSet.getInt();
resultSet.getDouble();
resultSet.getFloat();
resultSet.getDate();
·········
遍历，指针
resultSet.beforeFirst(); //遍历时指针移动到最前面
resultSet.afterLast(); //指针移动到最后面
resultSet.next(); //指针移动到下一个
resultSet.previous(); //移动到前一行
resultSet.absolute(row); //移动到指定行
释放资源
//6.释放资源
resultSet.close();
statement.close();
connection.close(); //好资源，要关闭掉
10.4、statement对象(重要)
Jdbc中的 statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。
Statement对象的 executeupdate方法，用于向数据库发送增、删、改的sq语句， executeupdate执行完后，将会返回一个整数（即增刪改语句导致后的数据库有多少行数据发生了变化）
Statement.executeQuery（execute执行 Query询问 执行查询）方法用于向数据库发送査询语句，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){//如果受影响的行数>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){//如果受影响的行数>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){//如果受影响的行数>0说明修改成功
System.out.println("修改成功!");
}
CRUD操作–read 查
使用executeUpdate(String sql)方法完成数据查操作；
Statement st=conn.createStatement();
String sql = "select * from user where id=1";
ResultSet rs = st.executeQuery(sql); //executeQuery返回的是结果集
while(rs.next()){
//根据获取列的数据类型，分别调用rs的相应的方法映射到java对象中
}
代码实现
1、创建一个properties配置类
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tXbvgY5S-1690606873598)(D:\笔记\java笔记\MySQL.assets\image-20201122170236435.png)]
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSLL=true
username=root
password=123456
2、提取工具类
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fW2VZd7J-1690606873598)(D:\笔记\java笔记\MySQL.assets\image-20201122170337366.png)]
package com.Long.lesson02.utils;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
//用于简化连接JDBC，除了查询操作外都可以从这个类中直接获取
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 in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(in);
driver=properties.getProperty("driver");
url=properties.getProperty("url");
username=properties.getProperty("username");
password=properties.getProperty("password");
//1.驱动只用加载一次
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
//2.通过驱动管理获取连接对象
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
//3.释放连接资源
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();
}
}
}
public static void release(Connection conn, Statement st){
if (st!=null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
3.1、测试代码————插入数据
package com.Long.lesson02;
import com.Long.lesson02.utils.JdbcUtils;
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();//创建statement对象用于执行sql语句
String sql="INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)" +
"VALUES(5,'liuliu','123456','254615@qq.com','1995-11-11')";
int i = st.executeUpdate(sql);
if (i>0){
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
3.2、测试代码————删除数据
package com.Long.lesson02;
import com.Long.lesson02.utils.JdbcUtils;
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();//创建statement对象用于执行sql语句
String sql="DELETE FROM users WHERE id=1";
int i = st.executeUpdate(sql);
if (i>0){
System.out.println("删除成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
3.3、测试代码————修改数据
package com.Long.lesson02;
import com.Long.lesson02.utils.JdbcUtils;
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 {
Connection connection = JdbcUtils.getConnection();//获取链接对象
st=conn.createStatement();
String sql="UPDATE users SET `PASSWORD`=2231546 WHERE id=2";
int i=st.executeUpdate(sql);
if (i>0){
System.out.println("修改成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
3.4、测试代码————查询数据
package com.Long.lesson02;
import com.Long.lesson02.utils.JdbcUtils;
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();//创建statement对象用于执行sql语句
String sql1="select * from users";
rs = st.executeQuery(sql1);
while (rs.next()){
System.out.println("id:"+rs.getObject("id"));
System.out.println("NAME:"+rs.getObject("NAME"));
System.out.println("PASSWORD:"+rs.getObject("PASSWORD"));
System.out.println("email:"+rs.getObject("email"));
System.out.println("birthday:"+rs.getObject("birthday"));
System.out.println("=========================================");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
10.5、SQL注入问题
package com.Long.lesson02;
import com.Long.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* sql存在漏洞，当程序实现为用户输入用户名和密码登录时，
* 由于后台是使用sql语句，所以用户可以用拼接条件“or”来实现
*/
public class SQL注入 {
public static void main(String[] args) throws SQLException {
//Sql注入时，由于使用的方法实际上是拼接字符串，所以可以修改条件
login(" 'or '1=1"," 'or '1=1");
//用户这样输入后，后台实际上输入的是
//SELECT * FROM `users` WHERE `NAME`=' 'or '1=1' AND `PASSWORD`=' 'or '1=1'
//即无论如何都为真，就可以查看数据库中所有用户的所有信息
}
public static void login(String username,String password) throws SQLException {
Connection conn=null;
Statement st=null;
ResultSet rs=null;
try {
conn=JdbcUtils.getConnection();
st=conn.createStatement();
//原意为用户输入用户名和密码来查看对应用户的所有信息
// "SELECT * FROM `users` WHERE `NAME`='"+用户自己的用户名+"' AND `PASSWORD`='"+用户自己的密码+"'"
String sql="SELECT * FROM `users` WHERE `NAME`='"+username+"' AND `PASSWORD`='"+password+"'";
rs= st.executeQuery(sql);
while (rs.next()){
System.out.println("id:"+rs.getObject("id"));
System.out.println("NAME:"+rs.getObject("NAME"));
System.out.println("PASSWORD:"+rs.getObject("PASSWORD"));
System.out.println("email:"+rs.getObject("email"));
System.out.println("birthday:"+rs.getObject("birthday"));
System.out.println("=========================================");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
10.6、PreparedStatement对象
PreparedStatement可以防止SQL注入，而且效率更高！
1、新增
package com.Long.lesson03;
import com.Long.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/*因为使用statement对象会有SQL注入问题，所以使用PreparedStatement来解决这个问题*/
public class TestInsert {
public static void main(String[] args) {
Connection conn=null;
PreparedStatement pst=null;
try {
conn=JdbcUtils.getConnection();
//主要区别：
//使用‘?’占位符代替参数
String sql= "INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES(?,?,?,?,?)";
//预编译sql，先写sql，然后不执行
pst=conn.prepareStatement(sql);
//手动给参数赋值
pst.setInt(1,6); //给第1个问号（也就是id）赋值为5
pst.setString(2,"行到1人"); //给第2个问号 (也就是name)赋值为 行到1人
pst.setString(3,"123456"); //给第3个问号 (也就是password)赋值为 123456
pst.setString(4,"123465@qq.com"); //给第4个问号 (也就是email)赋值为 123465@qq.com
//注意点：sql.Date 应用于数据库 java.sql.Date()
// util.Date 应用于java new Date().getTime获取时间戳
pst.setDate(5,new java.sql.Date(new Date().getTime())); //给给第5个问号 (也就是birthday)赋值为 行到1人
//执行
int i = pst.executeUpdate(); //不用传参数，因为前面已经预编译了，可以直接执行
if (i>0){
System.out.println("插入数据成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,pst);
}
}
}
2、删除
package com.Long.lesson03;
import com.Long.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/*因为使用statement对象会有SQL注入问题，所以使用PreparedStatement来解决这个问题*/
public class TestDelete {
public static void main(String[] args) {
Connection conn=null;
PreparedStatement pst=null;
try {
conn=JdbcUtils.getConnection();
//主要区别：
//使用‘?’占位符代替参数
String sql="DELETE FROM users WHERE id=?";
//预编译sql，先写sql，然后不执行
pst=conn.prepareStatement(sql);
//手动给参数赋值
pst.setInt(1,5);
//执行，不用传参数，因为前面已经预编译了，可以直接执行
int i = pst.executeUpdate();
if (i>0){
System.out.println("删除成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,pst);
}
}
}
3、修改
package com.Long.lesson03;
import com.Long.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/*因为使用statement对象会有SQL注入问题，所以使用PreparedStatement来解决这个问题*/
public class TestUpdate {
public static void main(String[] args) {
Connection conn=null;
PreparedStatement pst=null;
try {
conn= JdbcUtils.getConnection();
//主要区别：
//使用‘?’占位符代替参数
String sql="UPDATE users SET `NAME`=? WHERE id=?";
//预编译sql，先写sql，然后不执行
pst=conn.prepareStatement(sql);
//手动给参数赋值
pst.setObject(1,"张三");
pst.setInt(2,2);
//执行，不用传参数，因为前面已经预编译了，可以直接执行
int i = pst.executeUpdate();
if (i>0){
System.out.println("更新成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,pst);
}
}
}
4、查询
package com.Long.lesson03;
import com.Long.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/*因为使用statement对象会有SQL注入问题，所以使用PreparedStatement来解决这个问题*/
public class TestSelect {
public static void main(String[] args) {
Connection conn=null;
PreparedStatement pst=null;
ResultSet rs=null;
try {
conn= JdbcUtils.getConnection();
//主要区别：
//使用‘?’占位符代替参数
String sql="SELECT * FROM users WHERE `NAME`=?";
//预编译sql，先写sql，然后不执行
pst=conn.prepareStatement(sql);
//手动给参数赋值
pst.setObject(1,"张三");
//执行，不用传参数，因为前面已经预编译了，可以直接执行
rs = pst.executeQuery();
if (rs.next()){
System.out.println("name:"+rs.getObject("NAME"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,pst,rs);
}
}
}
5、防止SQL注入
package com.Long.lesson03;
import com.Long.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class 防止SQL注入 {
public static void main(String[] args) {
login("张三","123456");
}
public static void login(String username,String password){
Connection conn=null;
PreparedStatement pst=null;
ResultSet rs=null;
try {
conn= JdbcUtils.getConnection();
//主要区别：
//使用‘?’占位符代替参数
String sql="SELECT * FROM users WHERE `NAME`=? and `PASSWORD`=?";
//预编译sql，先写sql，然后不执行
pst=conn.prepareStatement(sql);
//手动给参数赋值
pst.setObject(1,username);
pst.setObject(2,password);
//执行，不用传参数，因为前面已经预编译了，可以直接执行
rs = pst.executeQuery();
if (rs.next()){
System.out.println("id:"+rs.getObject("id"));
System.out.println("NAME:"+rs.getObject("NAME"));
System.out.println("PASSWORD:"+rs.getObject("PASSWORD"));
System.out.println("email:"+rs.getObject("email"));
System.out.println("birthday:"+rs.getObject("birthday"));
System.out.println("=========================================");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,pst,rs);
}
}
}
10.7、使用IDEA连接数据库
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uXwtkJg5-1690606873599)(D:\笔记\java笔记\MySQL.assets\image-20201122202514346.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tgFG64rQ-1690606873599)(D:\笔记\java笔记\MySQL.assets\image-20201122203252944.png)]
- 连接成功后，可以选择数据库
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DmMmthyK-1690606873599)(D:\笔记\java笔记\MySQL.assets\image-20201122203532456.png)][外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-89jpI8nU-1690606873600)(D:\笔记\java笔记\MySQL.assets\image-20201122203637091.png)]
- 双击数据库：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-j04sYn85-1690606873600)(D:\笔记\java笔记\MySQL.assets\image-20201122203903820.png)]
4.数据库编写区
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JRRFdGSv-1690606873600)(D:\笔记\java笔记\MySQL.assets\image-20201122211045244.png)]
- 连接失败，查看原因
-
有可能没有导入数据库驱动
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uoqkE8aW-1690606873601)(D:\笔记\java笔记\MySQL.assets\image-20201122211228947.png)]
-
版本不对或删除了某些默认设置
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2FjufHuz-1690606873601)(D:\笔记\java笔记\MySQL.assets\image-20201122211438515.png)]
10.8、事务
要么都成功，要么都失败
用ＩＤＥＡ实现
- 开启事务
conn.setAutoCommit(false); - 一组业务执行完毕，提交事务
- 可以在catch语句中显示的定义回滚语句，但默认失败就会回滚
完整代码
package com.Long.lesson04;
import com.Long.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/**
* @author Eric
* @date 2020-11-22-21:23
*/
public class TestTransaction01 {
public static void main(String[] args) {
Connection conn=null;
PreparedStatement pst=null;
try {
conn=JdbcUtils.getConnection();
//关闭数据库的自动提交,自动回开启事务
conn.setAutoCommit(false);
String sql1="update account set money=money-100 where name='A'";
pst=conn.prepareStatement(sql1);
int i = pst.executeUpdate();
if (i>0){
System.out.println("A的数据修改了");
}
//int a=1/0;
String sql2="update account set money=money+100 where name='B'";
pst=conn.prepareStatement(sql2);
int j = pst.executeUpdate();
if (j>0){
System.out.println("B的数据修改了");
}
//事务成功则提交数据
conn.commit();
System.out.println("事务成功");
} catch (SQLException throwables) {
//若事务失败，默认回滚
try {
conn.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,pst);
}
}
}
10.9、数据库连接池
每次连接时会发生：数据库连接----执行完毕-----释放 十分浪费资源
池化技术：准备一些预先的资源，过来就连接预先准备好的
作用：预先准备好的连接池就好比银行的服务员，只要有需求连接（需要服务），就可以直接用（进行服务），否则（等待）
最小连接数 一般赋值与 常用连接数 一致
最大连接数：最高承载上限，超过的话连接就只能等待
编写连接池，实现一个接口 DataSource
开源数据源实现
DBCP
C3P0
Druid：阿里巴巴
使用了这些数据库连接池之后，我们在项目开发中就不需要编写连接数据库的代码了
DBCP
需要先导入到IDEA中的jar包：
- commons-dbcp-1.4.jar
- commons-pool-1.6.jar
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-F7ZLDbDb-1690606873601)(D:\笔记\java笔记\MySQL.assets\image-20201122235416201.png)]
1、配置properties文件
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fdMtEThb-1690606873601)(D:\笔记\java笔记\MySQL.assets\image-20201122235503723.png)]
#连接设置
driverClassName=com.mysql.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
#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
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
2、提取工具类
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aaTdkxyn-1690606873602)(D:\笔记\java笔记\MySQL.assets\image-20201122235853850.png)]
package com.Long.lesson05.utils;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
//用于简化连接JDBC，除了查询操作外都可以从这个类中直接获取
public class JdbcUtils_DBCP {
private static DataSource dataSource=null;
static {
try {
InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
Properties properties = new Properties();
properties.load(in);
//创建数据源 工厂模式--->创建
dataSource = BasicDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
//2.通过驱动管理获取连接对象
public static Connection getConnection() throws SQLException {
return dataSource.getConnection(); //从数据源中获取连接
}
//3.释放连接资源
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();
}
}
}
public static void release(Connection conn, Statement st){
if (st!=null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
3、测试查询数据库
package com.Long.lesson05;
import com.Long.lesson05.utils.JdbcUtils_DBCP;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/*因为使用statement对象会有SQL注入问题，所以使用PreparedStatement来解决这个问题*/
public class TestDBCP {
public static void main(String[] args) {
Connection conn=null;
PreparedStatement pst=null;
ResultSet rs=null;
try {
conn= JdbcUtils_DBCP.getConnection();
//主要区别：
//使用‘?’占位符代替参数
String sql="SELECT * FROM users WHERE `NAME`=?";
//预编译sql，先写sql，然后不执行
pst=conn.prepareStatement(sql);
//手动给参数赋值
pst.setObject(1,"张三");
//执行，不用传参数，因为前面已经预编译了，可以直接执行
rs = pst.executeQuery();
if (rs.next()){
System.out.println("id:"+rs.getObject("id"));
System.out.println("NAME:"+rs.getObject("NAME"));
System.out.println("PASSWORD:"+rs.getObject("PASSWORD"));
System.out.println("email:"+rs.getObject("email"));
System.out.println("birthday:"+rs.getObject("birthday"));
System.out.println("=========================================");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils_DBCP.release(conn,pst,rs);
}
}
}
C3P0
需要用到的jar包：
- c3p0-0.9.5.5.jar
- mchange-commons-java-0.2.19.jar
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bGzAu8zB-1690606873602)(D:\笔记\java笔记\MySQL.assets\image-20201123000356788.png)]
1、配置c3p0-config.xml文件
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-99hwNgae-1690606873603)(D:\笔记\java笔记\MySQL.assets\image-20201123000429788.png)]
<?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?userUnicode=true&characterEncoding=utf8&uesSSL=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的缺省（默认）配置
如果在代码中"ComboPooledDataSource ds=new ComboPooledDataSource();"这样写就表示使用的是c3p0的缺省（默认）-->
<name-config name="MySQL">
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&characterEncoding=utf8&uesSSL=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>
</name-config>
</c3p0-config>
2、提取工具类，配置JdbcUtils_C3P0
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YZlOH59n-1690606873603)(D:\笔记\java笔记\MySQL.assets\image-20201123000531274.png)]
package com.Long.lesson05.utils;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
//用于简化连接JDBC，除了查询操作外都可以从这个类中直接获取
public class JdbcUtils_C3P0 {
private static DataSource dataSource=null;
static {
try {
//这些都可以用配置好的c3p0-config.xml文件直接获取别人的
// dataSource=new ComboPooledDataSource();
// dataSource.setDriverClass();
// dataSource.setUser();
// dataSource.setPassword();
// dataSource.setJdbcUrl();
// dataSource.setMaxPoolSize();
// dataSource.setMinPoolSize();
//创建数据源 工厂模式--> 创建
dataSource=new ComboPooledDataSource("MySQL");
} catch (Exception e) {
e.printStackTrace();
}
}
//2.通过驱动管理获取连接对象
public static Connection getConnection() throws SQLException {
return dataSource.getConnection(); //从数据源中获取连接
}
//3.释放连接资源
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();
}
}
}
public static void release(Connection conn, Statement st){
if (st!=null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
3、测试
package com.Long.lesson05;
import com.Long.lesson05.utils.JdbcUtils_C3P0;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/*因为使用statement对象会有SQL注入问题，所以使用PreparedStatement来解决这个问题*/
public class TestC3P0 {
public static void main(String[] args) {
Connection conn=null;
PreparedStatement pst=null;
ResultSet rs=null;
try {
conn= JdbcUtils_C3P0.getConnection();
//主要区别：
//使用‘?’占位符代替参数
String sql="SELECT * FROM users WHERE `NAME`=?";
//预编译sql，先写sql，然后不执行
pst=conn.prepareStatement(sql);
//手动给参数赋值
pst.setObject(1,"张三");
//执行，不用传参数，因为前面已经预编译了，可以直接执行
rs = pst.executeQuery();
if (rs.next()){
System.out.println("id:"+rs.getObject("id"));
System.out.println("NAME:"+rs.getObject("NAME"));
System.out.println("PASSWORD:"+rs.getObject("PASSWORD"));
System.out.println("email:"+rs.getObject("email"));
System.out.println("birthday:"+rs.getObject("birthday"));
System.out.println("=========================================");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils_C3P0.release(conn,pst,rs);
}
}
}
结论
无论使用什么数据源，本质还是一样的，DataSource接口不会变，方法就不会变
补充
1、修改引擎
ALTER TABLE 表名 ENGINE=引擎名
2、创建/删除复合主键（multiple key）
- 如果项设置复合主键，复合主键的特点是同时创建、同时删除，所以需要把主键删除，但是这里设置了自增，需要先把自增删除，才可以删除主键，如果不先删除自增，而直接删除主键的话会报错
- 先删除自增，即直接更改字段的属性（没有自增请跳过）
-
ALTER TABLE 表名 MODIFY 字段名 字段属性····
- 这里我们创建复合主键的话，把name字段上的普通索引先删除掉，再给字段名1，字段名2字段同时创建复合主键
-
DROP INDEX 字段名 ON 表名
-
ALTER TABLE STU ADD PRIMARY KEY(字段名1，字段名2)
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：chenni525@qq.com进行投诉反馈，一经查实，立即删除！
- Python教程
- 深入理解 MySQL 中的 HAVING 关键字和聚合函数
- Qt之QChar编码（1）
- MyBatis入门基础篇
- 用Python脚本实现FFmpeg批量转换
- 记一次报表不平的排查过程（科目类型设置要慎重）
- 超声功率放大器怎么用
- WPF Icon矢量库 MahApps.Metro.IconPacks
- swift对接环信sdk
- 记.net core 6 集成efcore7 oracle
- 腾讯云4核8G服务器选择轻量还是标准型S5服务器？
- 【从服务器获取共享列表失败】【无法与设备或资源通信】解决方案！
- SIT75176B：3.0~5.5V 供电，32 节点，10Mbps 半双工 RS485/RS422 收发器
- Simpy简介：python仿真模拟库-03/5
- PulseGAN