程序员经验分享-hwhale

MySQL数据库学习（保姆级教程）（1.7W字）

2023-11-09

1.初识MySQL

JavaEE:企业级Java开发 Web

前端（页面：展示，数据！）

后台（连接点：连接数据库JDBC，链接前端（控制，控制视图跳转，和给前端传递数据））

数据库（存数据，Txt，Excel，Word）

只会写代码，学好数据库，基本混饭吃！

操作系统，数据结构与算法！当一个不错的程序猿！

离散数学，数字电路，体系结构，编译原理+实战经验，高级程序猿优秀的程序猿

1.1为什么学习数据库

  1. 岗位需求
  2. 现在的世界，大数据时代，得数据者得天下。
  3. 被迫需求：存数据
  4. 数据库是所有软件体系中最核心的存在 DBA

1.2 什么是数据库

数据库（DB，DataBase）

概念：数据仓库，软件，安装在操作系统（window，Linux，mac、…）之上，可以存储大量的数据。500万！

作用：存储数据，管理数据

1.3数据库分类

关系数据库：Excel （SQL）

  • MySQL，Oracle，Sql Server，DB2，SQLlife
  • 通过表和表之间，行和列之间的关系进行数据的存储， 学员信息表，考勤表，。。。

非关系型数据库：{key:value} (NoSQL) Not Only SQL

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

DBMS（数据库管理系统）

  • 是数据库的管理软件，能够科学有效的管理我们的数据。维护和获取数据

  • 如MySQL就是一个数据库管理系统

    image-20220715212658203

1.4MySQL简介

MySQL是一个关系型数据库管理系统

前世：瑞典MySQL AB公司

今生：属于Oracle旗下产品

最好的RDBMS关系数据库管理系统应用软件之一

开源的数据库软件~

体积小、速度快、总体拥有成本低，招人成本比较低，所有人必须会！

中小型网站、或者大型网站，集群！

官网：https://www.mysql.com/

安装建议：

  1. 尽量不要使用exe，注册表
  2. 尽可能使用压缩包安装

1.5安装MySQL

教程：https://blog.csdn.net/mulinghanxue/article/details/93917244

1.6安装SQLyog

  1. 无脑安装

  2. 注册

  3. 打开连接数据库

    中

  4. 新建一个数据库如school

image-20220716234021215

每一个sqlyog的执行操作，本质就是对应了一个sql，可以在软件的历史记录中查看font color=‘red’>

  1. 新建一张表student
字段： id,name,age

image-20220716233920318

  1. 查看表

    右键打开表

  2. 自己尝试添加多条记录

    image-20220716234721208

1.7连接数据库

命令行连接！

mysql -uroot -p123456 -- 连接数据库

update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';-- 修改用户密码 123456可以换成其他的
flush privileges; -- 刷新权限

-----------------------------------
-- 所有的语句都使用“：”结尾
show databases; -- 查看所有的数据库

mysql> use school -- 切换数据库 use 数据库名
-- 出现Database changed 表示切换成功

-- 
show tables; -- 查看数据库中所有的表
describe student; -- 显示数据库中所有的表的信息

create database wsetos; -- 创建一个名为wsetos的数据库

exit; -- 退出连接

-- 单行注释（SQL本来的注释）
/*
hello 
mysql
!
*/

image-20220719191237799

image-20220719191314203

image-20220719191325166

数据库xxx语言 CRUD 增删改查！ CV程序猿 API程序猿 CRUD程序猿！（业务！）

DDL 定义

DML 操作

DQL 查询

DCL 控制

2.操作数据库

操作数据库>操作数据库中的表>操作数据库中表的数据

mysql关键字不区分大小写

2.1操作数据库（了解）

  1. 操作数据库

    CREATE  DATABASE [IF NOT EXISTS] westos;
创建一个名为westos的数据库，IF NOT EXISTS作为判断可写可不写

-- CREATE DATABASE shop CHARSET=utf8 COLLATE utf8_general_ci 创建商店 并初始化字符集和设置字符集效验
-- collate vt.校对；核对；对照;（图书馆工作中）核对，检查（书籍页码及其顺序）;检验（印张顺序）;授予（教区牧师）有俸职位;

  2. 删除数据库

    DROP DATABASE IF EXISTS westos;

  3. 使用数据库

USE `school`

-- tab 键的上面，如果你的表名或者是字段名是一个特殊字符，就需要带``
  1. 查看数据库
SHOW DATABASES;-- 查看所有的数据库

对比：SQLyog的可视化操作

image-20220719193056795

学习思路：

  • 对照SQLyog的可视化历史记录查看sql

  • 固定的语法或者关键字必须要强行记住！

数据库的列类型

数值

  • tinyint 十分小的数据 1个字节
  • smallint 较小的数据 2个字节
  • mediumint 中等大小的数据 3个字节
  • int 标准的整数 4个字节
  • bigint 较大的字节 8个字节
  • float 浮点数 4个字节
  • double 双精度浮点数 8个字节
  • decimal 字符串形式的浮点数 金融计算的时候，一般使用decima(长度,小数)
  • numeric 数字型通常用于数学计算 如 numeric(10,2)指字段是数字型,长度为10 小数为两位的

字符串

  • char 字符串固定大小的 0~255
  • varchar 可变字符串 0~65535 常用的 String
  • tinytext 微型文本 2^8-1
  • text 文本串 2^16-1 保存大文本

时间日期

java.util.Date中的类

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

null

  • 没有值，未知
  • ==注意，不要使用NULL进行运算，结果为NULL

SQL数据库中的数据类型长度——怎么取值

https://blog.csdn.net/weixin_42724467/article/details/88291168

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

Unsigned:

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

zerofill:

  • 0填充的
  • 不足的位数，使用0来填充，int（3），5—005

自增：

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

非空 NULL not null

  • 假设设置为not null，如果不给它赋值，就会报错
  • NUll，如果不填写值(不打√)，默认就是null

默认：

  • 设置默认的值！

  • sex，默认值 为 男，如果不指定该列的值，则会有默认的值！

  • 拓展：

    /*每一个表，都必须存在以下五个字段！未来做项目用的，表示一个记录存在的意义！

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

*/

2.4创建数据库表（重点）

-- 目标：创建一个school数据库
-- 创建学生表（列，字段） 使用SQL创建
-- 学号int 登录密码varchar(20) 姓名，性别varchar(2)，出生日期(datatime)，家庭住址，email

-- 注意点，使用英文()，表的名称 和 字段 尽量使用 `` 括起来
-- 字符串使用 单引号括起来！
-- 所有的语句后面加 , (英文的)，最后一个不用加 
-- PRIMARY KEY 主键，一般一个表只有一个唯一的主键！
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30)NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd`  VARCHAR(20)NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL  DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email`VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- CHARSET=utf8是CHARACTER SET utf8的省略

image-20220719210608005

格式

CREATE TABLE [IF NOT EXISTS] `表名`(
	`字段名` 列类型 [属性][索引][注释],
    `字段名` 列类型 [属性][索引][注释],
    ......
    `字段名` 列类型 [属性][索引][注释]   
)[表类型][字符集设置][注释]
”[ ]“内为可选内容

常用命令

SHOW CREATE DATABASE school; -- 查看创建数据库的语句
SHOW CREATE TABLE student;-- 查看student数据表的定义语句
DESC student; -- 显示表的具体结构 describe studnet

2.5数据表的类型

-- 关于数据库引擎 
/*
INNODB 默认使用
MYISAM 早些年使用
*/
MYISAM INNODB
事务支持 不支持 支持
数据行锁定 不支持 支持
外键约束 不支持 支持
全文索引 支持 不支持
表空间的大小 较小 较大，约为myisam的两倍

常规使用操作：

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

在物理空间存在的位置

所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库

本质还是文件的存储！

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

  • InnoDB在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
  • MYISAM对应文件
    • *.frm 表结构的定义文件
    • *.MYD 数据文件（data）
    • *.MYI 索引文件

image-20220720203526704

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

CHARSET=utf8

不设置的话，会是mysql默认的字符集编码~（不支持中文！）

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)
-- 修改表的字段（重命名，修改约束!）
-- ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- 修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
ALTER TABLE teacher1 CHANGE age age1 INT(1) -- 字段重命名
/*
change用来字段重命名，不能修改字段类型和约束；
modify不用来字段重命名，只能修改字段类型和约束
*/
-- 删除表的字段： ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher1 DROP age1

删除

-- 删除表(如果表存在再删除)
DORP TABLE IF EXISTS teacher1

所有的创建和删除操作尽量加上判断，以免报错~

注意点：

  • ``字段名，使用这个包裹！
  • 注释 – /**/
  • sql关键字大小写不敏感，建议写小写，不容易出错
  • 所有的符号全部用英文

3.MySQL数据管理

3.1外键（了解即可）

方式一：在创建表的时候，增加约束（麻烦，比较复杂）

CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8


-- 学生表的gradeid字段 要去引用年级表的 gradeid
-- 定义外键key
-- 给这个外键添加约束（执行引用） references 引用
SHOW CREATE TABLE `student`
CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` varchar(2) NOT NULL DEFAULT '女' COMMENT '性别',
  `birthday` datetime DEFAULT NULL COMMENT '出生日期',
  `gradeid` int(10) NOT NULL 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=utf8

删除有外键关系的表的时候，必须要先删除引用别人的表（从表），再删除被引用的表（主表）

（如例子中要先删除school才能删除grade表）

方式二：创建表成功后，添加外键约束

CREATE TABLE `student` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  `gradeid` INT(10) NOT NULL COMMENT '学生的年级',
  `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
  `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
-- 创建表的时候没有外键关系
ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`)
REFERENCES `grade`(`gradeid`);

-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 那个表(哪个字段)

以上的操作都是物理外键，数据库级别的外键，我们不建议使用！（避免数据库过多造成困扰，这里了解即可）

最佳实践

  • 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
  • 我们想使用多张表的数据，想使用外键（程序去实现）

3.2DML语言（全部记住）

数据库意义：数据存储，数据管理

DML语言：数据操作语言

  • Insert
  • update
  • delete

3.3添加

-- 插入语句（添加）
-- insert into 表名([字段名1，字段2，字段3])values('值1'),('值2'),('值3'),...
INSERT INTO `grade`(`gradename`)VALUES('大四')

-- 由于主键自增我们可以省略（如果不写表的字段，他就会一一匹配）
INSERT INTO `grade`VALUES('大三')
-- 一般写插入语句，我们一定要数据和字段一一对应！

-- 插入多个字段
INSERT INTO `grade`(`gradename`)VALUES('大二'),('大一')

image-20220723194513897

INSERT INTO `student`(`name`,`pwd`,`sex`)VALUES('张三','aaaaaa','男')
INSERT INTO `student`
(`name`,`pwd`,`sex`)VALUES('李四','aaaaaa','男'),('王五','aaaaaa','男')
INSERT INTO `student`VALUES
('5','李四','aaaaaa','男','2000-01-01',1,'西安','email')

注意把gradeid中的非空取消掉

image-20220723200621186

语法：insert into 表名([字段名1，字段2，字段3])values(‘值1’),(‘值2’),(‘值3’),…

注意事项：

  1. 字段和字段之间使用 英文逗号 隔开
  2. 字段是可以省略的，但是后面的值必须要一一对应，不能少
  3. 可以同时插入多条数据，VALUES后面的值，需要使用’,'隔开即可

3.4修改

update 修改谁 （条件） set 原来的值 = 新值

-- 修改学员名字
UPDATE `student` SET `name`='Starry'WHERE id=1

image-20220723200911657

-- 不指定条件的情况下，会改动所有的表！
UPDATE `student`SET`name`='长江7号'

image-20220723201134928

-- 语法
-- UPDATE 表名 set colnum_name = value,[colnum_name = value,....] where [条件]
-- 修改多个属性，逗号隔开
UPDATE `student`SET `name`='长江',`email`='1078904721@qq.com'WHERE id=1

image-20220723201500359

条件：where子句 运算符 id等于某个值，大于某个值，在某个区间内修改…

操作符会返回 布尔值

操作符 含义 范围 结果
= 等于 如5=6 false
<>或!= 不等于 5<>6 true
<
>
<=
>=
BETWEEN … and … 在某个范围内 [2,5]
AND 我和你&& 5>1 and 1>2 false
OR 我或你|| 5>1 or 1>2 true

image-20220723202404156

语法：UPDATE 表名 set colnum_name = value,[colnum_name = value,…] where [条件]

注意：

  • colnum_name 是数据库的列，尽量带上``
  • 条件，筛选的条件，如果没有指定，则会修改所有的列
  • value，是一个具体的值，也可以是一个变量
  • 多个设置的属性直接，使用英文逗号隔开
UPDATE `student`SET `birthday`=CURRENT_TIME WHERE'name'='长江7号' AND sex='女'

3.5删除

delete 命令

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

-- 删除数据（避免这样写，会全部删除）
DELETE FROM `student`
-- 删除指定数据
DELETE FROM `student` WHERE id=1;

TRUNCATE命令（vt.将…舍位；将…截位;adj.截形的；平截的;截断的;）

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

-- 清空 student 表
TRUNCATE `student`

image-20220723203631265

delete和truncate 的区别

  • 相同点：都能删除数据，都不会删除表结构
  • 不同：
    • TRUNCATE 重新设置 自增列 计数器会归零
    • TRUNCATE 不会影响事务
CREATE TABLE `test`(
`id` INT(4)NOT NULL AUTO_INCREMENT,
`coll`VARCHAR(20)NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `test`(`coll`)VALUES('1'),('2'),('3')

image-20220723205138058

DELETE FROM `test`-- 不会影响自增

image-20220723205251101

INSERT INTO `test`(`coll`)VALUES('1'),('2'),('3')

image-20220723205339454

TRUNCATE TABLE `test`-- 自增会归零

了解即可：DELETE删除的问题，重启数据库后的现象

  • INNODB 自增列会重1开始（存在内存当中，断电即失）
  • MYISAM 继续从上一个自增量开始（存在文件中，不会丢失）

drop
1、drop语句将删除表的结构，以及被依赖的约束(constrain),触发器(trigger),索引(index);

drop table table_name;
2、说明
　　1）、删除之后，依赖于该表的存储过程/函数将保留,但是变为invalid状态.
　　2）、drop也属于DDL语言，立即执行，执行速度最快
　　3）、删除内容和定义，释放空间。
区别
1、表和索引所占空间：
　　当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小；
　　DELETE操作不会减少表或索引所占用的空间；
　　DROP语句将表所占用的空间全释放掉。
　　
2、应用范围：
　　TRUNCATE 只能对table；
　　DELETE可以是table和view。

3、执行速度：
　　drop > truncate > delete

4、delete from删空表后，会保留一个空的页，truncate在表中不会留有任何页。

5、DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。
　　TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

6、当使用行锁执行 DELETE 语句时，将锁定表中各行以便删除。truncate始终锁定表和页，而不是锁定各行。

7、如果有identity产生的自增id列，delete from后仍然从上次的数开始增加，即种子不变；
　使用truncate删除之后，种子会恢复到初始值。

4.DQL查询数据（最重点）

4.1DQL

（Date Query LANGUAGE:数据查询语言）

  • 所有的查询操作都用它 Selet

  • 简单的查询，复杂的查询它都能做~

  • 数据库中最核心的语言，最重要的语句

  • 使用频率最高的语句

Select完整的语法：

SELECT语法

SELECT [ALL | DISTINCT]
{* | table.*|[table.field1[as alias1][,table.field2[as alias2]][,...]}
FROM table_name[as table_alias]
   [left | right | inner join table_name2] -- 联表查询
   [WHERE ...] -- 指定结果需满足的条件
   [GROUP BY ...] -- 指定结果按照哪几个字段来分组
   [HAVING] -- 过滤分组的记录必须满足的次要条件
   [ORDER BY ...] -- 指定查询记录按一个或多个条件排序
   [LIMIT {[offset,]row_count | row_countOFFSET offset}];
   -- 指定查询的记录从哪条至哪条

注意：[]括号内代表可选的，{}括号内代表必选的

4.2指定查询字段

-- 查询全部的学生	SELECT 字段 FROM 表
SELECT * FROM student


-- 查询指定字段
SELECT `studentno`,`studentname` FROM student

在这里插入图片描述

-- 别名，给结果起一个名字 AS 可以给字段起别名，也可以给表起别名
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM student AS 学生

在这里插入图片描述

注意别名不会改变本身名字

在这里插入图片描述

-- 函数 Concat(a,b)
SELECT CONCAT('姓名：',studentname) AS 新名字 FROM student

在这里插入图片描述

语法：SELECT 字段，. . . FROM 表

加引号只是为了区别mysql关键之和你的表名，可以不加

有的时候，列名字不是那么的见名知意。我们起别名 AS： 字段名 AS 别名 表名 AS 别名

去重 distinct

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

-- 查询一下有哪些同学参加了考试，成绩
SELECT * FROM result -- 查询全部的考试成绩

在这里插入图片描述

-- 查询有哪些同学参加了考试
SELECT `StudentNo` FROM result


-- 发现重复数据，去重
SELECT DISTINCT `StudentNo` FROM result

image-20220724205347820

数据库的列（表达式）

SELECT VERSION()-- 查询系统版本（函数）
SELECT 100*3-1 AS 计算结果 -- 用来计算（表达式）
SELECT @@auto_increment_increment -- 查询自增的步长（变量）

-- 学员考试成绩 +1分查看
SELECT `studentno`,`StudentResult` AS '提分后' FROM result

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

select 表达式 from 表

4.3where 条件字句

作用：检索数据中符合条件的值

搜索的条件由一个或者多个表达式组成！ 结果为布尔值

逻辑运算符

运算符 语法 描述
and && a and b a&&b 逻辑与，两个都为真，结果为真
or || a or b a||b 逻辑或，其中一个为真，则结果为真
not ! not a ! a 逻辑非，真为假，假为真！ 
SELECT `studentno`,`studentresult` FROM result

image-20220724210948107

-- 查询考试成绩在95~100分之间
SELECT studentno,studentresult FROM result
WHERE StudentResult>=95 AND StudentResult<=100
-- 模糊查询（区间）
SELECT studentno,studentresult FROM result
WHERE StudentResult BETWEEN 95 AND 100

image-20220724211142125

-- 除了1001号学生之外的同学的成绩
SELECT studentno,studentresult FROM result
WHERE studentno!=1001;
-- 或者
SELECT studentno,studentresult FROM result
WHERE NOT studentno=1001;

image-20220724211526821

模糊查询：比较运算符

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

like:

-- 查询姓赵的同学
-- like结合 %（代表0到任意个字符） _（一个字符）
SELECT `studentno`,`studentname` FROM `student`
WHERE studentname LIKE '赵%'

image-20220724212604560

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

SELECT `studentno`,`studentname` FROM `student`
WHERE studentname LIKE '赵__'

-- 查询名字中有嘉字的同学  %嘉%
SELECT `studentno`,`studentname` FROM `student`
WHERE studentname LIKE '%嘉%'

image-20220724213000662

in: in（具体的一个或者多个值）不能用in(‘北京%’)

-- 查看 1001，1002，1003号学员
SELECT `studentno`,`studentname` FROM `student`
WHERE studentno IN(1001,1002,1003);

image-20220724213422383

-- 查看在沈阳的学员
SELECT `studentno`,`studentname`,`address` FROM `student`
WHERE address IN('沈阳');

image-20220724213556962

null not null:

-- 查看地址为空的学员 null ''
SELECT `studentno`,`studentname`,`address` FROM `student`
WHERE address='' OR address IS NULL

image-20220724214242039

-- 查看有出生日期的学员 不为空
SELECT `studentno`,`studentname`,`address`,`BornDate`FROM `student`
WHERE `BornDate` IS NOT NULL
-- 查看没有出生日期的学员 为空
SELECT `studentno`,`studentname`,`address`,`BornDate`FROM `student`
WHERE `BornDate` IS NULL

image-20220724214530365

image-20220724214540676

4.4联表查询

JOIN对比

image-20220726211434104

MySQL七种join理论

img

以上7种join，从左到右从上到下分别是左连接、右连接、左连接之后去除共有部分、内连接、右连接之后去除共有部分、全连接、全连接之后去除共有部分

  1. 内连接

img

select * from A inner join B where A.key=B.key;

注意：内连接(两表的共有部分)

  1. 左连接

img

select * from A left join B on A.key=B.key where B.key is null;

注意：左连接（左表的全部，右表不满足补NULL）

  1. 右连接

img

select * from A right join B on A.key=B.key where A.key is null;

注意：右连接（右表的全部，左表不满足的补NULL）

  1. 左外连接

img

select * from A left join B on A.key=B.key;

注意：特殊的左连接，（显示为左表的独有的数据）

  1. 右外连接

img

select * from A right join B on A.key=B.key

注意：特殊的右连接（显示为右表的独有的数据 ）

  1. 全外连接

img

select * from A left join B where A.key=B.key union select * from A right join B where A.key=B.key;

注意：全连接（显示全部数据）（mysql 不支持 full outer join）

  1. 两表独有的数据集

img

select * from A left join B on A.key=B.key where B.key is null 

union 

select * from A right join B on A.key=B.key where A.key is null;

注意：显示两表的独有的数据

举例：

-- ==========联表查询 join==========
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM student
SELECT * FROM result

/*思路
1.分析需求，分析查询的字段来自哪些表，（连接查询）
2.确定使用哪种连接查询？7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件：学生表中的 studentno = 成绩表中的 studentno
*/

-- INNER Join
SELECT s.studentNO,StudentName,SubjectNo,StudentResult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentNo=r.studentNo

-- Right Join -- AS可以省略：空格代替 where用on 代替
SELECT s.studentNO,StudentName,SubjectNo,StudentResult
FROM student s
RIGHT JOIN result r
ON s.studentNo=r.studentNo

-- Left Join
SELECT s.studentNO,StudentName,SubjectNo,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNo=r.studentNo

Inner Join和Right Join结果如下

image-20220726213808755

Left Join结果如下

image-20220726213906203

操作 描述（以前三种基本理论为例）
Inner join 如果表中至少有一个匹配，就返回行
left join 会从左表中返回所有的值(赋null)，即使右表中没有匹配
right join 会从右表中返回所有的值(赋null)，即使左表中没有匹配

哪个表在FROM**前哪个为左表。 * *后面的表为主表

以left join为例，因为左表为student，右表为result，因为select的字段中属于student只有studentNo和studentName，而字段内的有些学生在result内并没有匹配。仍会返回student内所有的值，而无匹配的值赋null。

-- 查询缺考的同学
SELECT s.studentNO,StudentName,SubjectNo,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNo=r.studentNo
WHERE StudentResult IS NULL

image-20220726221651062

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

on作用于连接查询时的条件判断,where作用于查询出来后对初始结果进行再次筛选

-- 思考题（查询了参加考试的同学信息：学号，学生姓名，科目名，分数）
/*思路
1.分析需求，分析查询的字段来自哪些表，（连接查询）student、result、subject
2.确定使用哪种连接查询？7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件：学生表中的 studentno = 成绩表中的 studentno
*/
SELECT s.studentNo,StudentName,SubjectName,`StudentResult`
FROM student s
RIGHT JOIN result r
ON r.studentno=s.studentno
INNER JOIN `subject` sub
ON r.SubjectNo=sub.SubjectNo

-- 我要查询哪些数据 select ...
-- 从哪几个表中查 FROM 表 XXX Join 连接的表 on 交叉条件
-- 假设存在一种多张表查询，慢慢来，先查询两张表然后再慢慢增加
-- 即先
SELECT s.studentNo,StudentName,SubjectNo,`StudentResult`
FROM student s
RIGHT JOIN result r
ON r.studentno=s.studentno

-- 然后增加
INNER JOIN `subject` sub
ON r.SubjectNo=sub.SubjectNo
-- 并把SELECT s.studentNo,Studentno,SubjectName,`StudentResult`的
-- SubjectNo改为SubjectName
-- ==========================
-- From A left join B 以A表为基准
-- From A right join B 以B表为基准

先查两张表：

image-20220726224346032

再增加第三张表：

image-20220726224413598

自连接

自己的表和自己的表连接，核心：一张表拆为两张一样的表即可

父类

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

子类

pid cateforyid categoryName
3 4 数据库
2 8 办公信息
3 6 web开发
5 7 ps设计

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

父类 子类
信息技术 办公信息
软件开发 数据库
软件开发 web开发
美术设计 ps技术 
CREATE TABLE  IF NOT EXISTS`category`(
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT'主题id',
`pid`INT(10) NOT NULL COMMENT '父id',
`categoryName` VARCHAR(50) NOT NULL COMMENT'主题名字',
PRIMARY KEY(`categoryid`)
)ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

INSERT INTO `school`.`category` (`categoryid`, `pid`, `categoryname`) VALUES ('2', '1', '信息技术');
INSERT INTO `school`.`CATEGOrY` (`categoryid`, `pid`, `categoryname`) VALUES ('3', '1', '软件开发');
INSERT INTO `school`.`category` (`categoryid`, `PId`, `categoryname`) VALUES ('5', '1', '美术设计');
INSERT INTO `School`.`category` (`categoryid`, `pid`, `categorynamE`) VALUES ('4', '3', '数据库'); 
INSERT INTO `school`.`category` (`CATEgoryid`, `pid`, `categoryname`) VALUES ('8', '2', '办公信息');
INSERT INTO `school`.`category` (`categoryid`, `pid`, `CAtegoryname`) VALUES ('6', '3', 'web开发'); 
INSERT INTO `SCHool`performance_schema``school``.`category` (`categoryid`, `pid`, `categoryname`) VALUES 
('7', '5', 'ps技术');

创建好的category表：

image-20220727142929412

-- 查询父子信息：把一张表看成为两个一模一样的表
SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目'
FROM `category`AS a,`category`AS b
WHERE a.`categoryid`=b.`pid`

select 后的表：

image-20220727143042405

练习

-- 联表查询
-- 查询学员所属的年级（学号，学生的姓名，年级名称）
SELECT studentNo,studentName,GradeName
FROM student s
INNER JOIN `grade` g
ON s.`GradeID`=g.`GradeID

-- 联表查询
-- 查询科目所属的年级（科目名称,年级名称）
SELECT `subjectName`,`gradename`
FROM `grade`AS g
INNER JOIN `subject` AS s
ON s.gradeid=g.gradeid
-- 或
SELECT `subjectName`,`gradename`
FROM `grade` g,`subject` s
WHERE s.gradeid=g.gradeid

-- 思考题（查询了参加C语言-1考试的同学信息：学号，学生姓名，科目名，分数）
SELECT s.studentNo,StudentName,SubjectName,studentResult
FROM student s
INNER JOIN `result` r
ON s.studentNo=r.StudentNo
INNER JOIN `subject` sub
ON sub.subjectno=r.subjectno
WHERE subjectName = 'C语言-1'

image-20220727144014509

image-20220727144813710

image-20220727150153305

4.5分页和排序

排序

-- 排序：升序ASC，降序DESC
-- ORDER BY 通过哪个字段排序，怎么排
-- 查询的结果更具 成绩降序 排序
SELECT s.studentNo,StudentName,SubjectName,studentResult
FROM student s
INNER JOIN `result` r
ON s.studentNo=r.StudentNo
INNER JOIN `subject` sub
ON sub.subjectno=r.subjectno
WHERE subjectName LIKE '高等%'
ORDER BY StudentResult ASC

image-20220727152102108

分页

-- 100万条数据
-- 为什么要分页
-- 缓解数据库压力，给人的体验更好， 瀑布流与分页相反（可以往下无限加载数据，如百度搜索图片）

-- 分页，每页只显示两条数据
-- 语法：limit(查询起始下标,pageSize)
--	 limit	0,2 分成2页，当前页显示的初始值为总数据的第一条数据
--	 limit	1,2
--	 limit	2,2
-- 网页应用：当前，页的总数，页面的大小
SELECT s.studentNo,StudentName,SubjectName,studentResult
FROM student s
INNER JOIN `result` r
ON s.studentNo=r.StudentNo
INNER JOIN `subject` sub
ON sub.subjectno=r.subjectno
WHERE subjectName LIKE '高等%'
ORDER BY StudentResult ASC
LIMIT 0,2

-- 第一页 limit 0,5	（1-1）*5
-- 第二页 limit 5,5	（2-1）*5
-- 第三页 limit 10,5	（3-1）*5
-- 第N页 limit 0,5	（n-1）* pagesize
-- 【pagesize：页面大小】
-- 【(n-1)*pagesieze：起始值】
-- 【n：当前页】
-- 【数据总数/页面大小=总页数，有余数，要+1】

image-20220727153608175

思考题：

image-20220727154127798

4.6子查询

-- 查询数据结构-1 的所有考试结果（学号，科目编号，成绩），降序排序
-- 方式一：使用联表查询
SELECT `StudentNo`,`SubjectName`,`StudentResult`
FROM `result` r
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE subjectName='数据库结构-1'
ORDER BY StudentResult DESC

-- 方式二：使用子查询（由里及外）
SELECT `StudentNo`,`SubjectName`,`StudentResult`
FROM `result` 
WHERE SubjectNo=(
	SELECT SubjectNo FROM `subject`
	WHERE SubjectName='数据库结构-1'
)ORDER BY StudentResult DESC


-- 分数不小于80分的学生的学号和姓名，高等数学-2
-- 查询 高等数学-2 的编号
-- 子查询
SELECT DISTINCT s.`StudentNo`,`StudentName`-- 注意去重
FROM student s
INNER JOIN result r
ON r.`studentno`=s.studentno
WHERE `StudentResult`>=80 AND `SubjectNo`=
(
	SELECT SubjectNo FROM `subject`
	WHERE `SubjectName` = '高等数学-2'
)
-- 联表查询
SELECT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON s.studentno=r.`studentno`
INNER JOIN `subject` sub
ON sub.`subjectno`=r.`subjectno`
WHERE subjectName='高等数学-2' AND StudentResult>=80

-- 再改造（有里到外）
SELECT `StudentNo`,`StudentName`FROM student WHERE studentNo IN(
	SELECT StudentNo FROM result WHERE StudentResult>=80 AND SubjectNo=(
		SELECT SubjectNo FROM `subject` WHERE SubjectName='高等数学-2'
	)
)

image-20220727162233802

4.7分组和过滤

-- 查询不同课程的平均分，最高分，最低分
-- 核心：（根据不同的课程分组）
SELECT any_value(SubjectName),AVG(StudentResult),MAX(StudentResult),MIN(StudentResult)
FROM result r
INNER JOIN `subject` sub
ON r.`subjectNo`=sub.`SubjectNo`
GROUP BY r.SubjectNo -- 通过什么字段来分组
-- 只要有聚合函数sum()，count()，max()，avg()等函数就需要用到group by，否则就会报上面的错误。
-- 高版本要加any_value(`subjectname`)
-- 分组前，得到的是所有字段的总平均值、最大值、最小值。按字段分组后才能得到每个字段各自的平均、最大最小值。

分组前：

image-20220727173908930

分组后：

image-20220727173948504

-- 升级后
-- 求平均分大于80的
SELECT any_value(SubjectName),AVG(StudentResult)AS 平均分,MAX(StudentResult)AS 最高分,MIN(StudentResult)AS 最低分
FROM result r
INNER JOIN `subject` sub
ON r.`subjectNo`=sub.`SubjectNo`
GROUP BY r.SubjectNo
HAVING 平均分>80

image-20220727174348981

SELECT小结

image-20220727200540064

补充：

1.【数据库】select、from、where、group by、having、order by、limit的组合用法

https://blog.csdn.net/qq_33591903/article/details/83686780

2.AS的两种用法

as 一般用在两个地方，一个是query的时候，用来重新指定返回的column 名字
如：一个table 有个column叫 id， 我们的query是
select id from table1. 但是如果你不想叫id了，就可以重新命名，如叫 systemID 就可以这样写
select id as systemId from table1;

还有一个用法就是在create table 或 procedure 的时候，as 是个关键字。
例如
create table test as select * from table1
这时候就会create 一个table test，他是完全copy table table1里的全部数据。

create procdure name as （is）
begin
end;
具体可以参考 如何建立procedure。 这个时候 as 和is可以互换

5.MySQL函数

官网：https://dev.mysql.com/doc/refman/8.0/en/functions.html

5.1 常用函数

-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回一个0~1之间的随机数
SELECT SIGN(0) -- 判断一个数的符号 0-0 负数返回-1，正数返回1

-- 字符串函数
SELECT CHAR_LENGTH('即使再小的帆也能远航') -- 字符串长度
SELECT CONCAT('学','习','数据库') -- 拼接字符串
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱') -- 查询，从第一个位置替换3个字符长度
SELECT LOWER('KuangShen') -- 小写字母
SELECT UPPER('Kuangshen') -- 大写字母
SELECT INSTR('kuangshen','h') -- 返回第一次出现的字符串的索引in string
SELECT REPLACE('坚持就能成功','坚持','努力') -- 替换出现的指定字符串
SELECT SUBSTR('坚持就能成功',4,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()）
SELECT COUNT(studentname)FROM student;	-- Count(字段)，会忽略所有的null值
SELECT COUNT(*) FROM student; -- Count(*),不会忽略null值，本质是计算行数
SELECT COUNT(1) FROM student; -- Count(1),不会忽略null值，本质是计算行数

image-20220727171437399

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加密后的值转变成加密前的值

-- ===========测试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','123456'),(3,'wangwu','123456')

image-20220727182618999

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

image-20220727182640028

UPDATE testmd5 SET pwd=MD5(pwd) -- 加密全部的密码

image-20220727182826889

-- 插入的时候加密
INSERT INTO testmd5 VALUES(4,'xiaoming',MD5('123456'))

image-20220727183141268

-- 如何校验：将用户传递进来的密码，进行md5加密，然后比对加密后的值
SELECT * FROM testmd5 WHERE `name`='xiaoming' AND pwd=MD5('123456')

image-20220727183347165

5.4函数自定义

https://www.csdn.net/tags/MtTaEg4sMDE3My1ibG9n.html

6.事务

6.1什么是事务

要么都成功，要么都失败

————

如A给B转账200元，可分为两步

  1. SQL执行 A给B转账 A原先有1000 —>200 B原先有200
  2. SQL执行 B收到A的前 A变为800 —>B接收 B变为400

————

将一组SQL放在一个批次中去执行~

事务(Transaction)原则：ACID原则 原子性(Atomicity)，一致性(Consistency)，隔离性(Isolation)，持久性(Durability) （脏读，幻读…）

参考链接：https://blog.csdn.net/dengjili/article/details/82468576

A（Atomicity）原子性

原子性很容易理解，也就是说事务里的所有操作要么全部做完，要么都不做，事务成功的条件是事务里的所有操作都成功，只要有一个操作失败，整个事务就失败，需要回滚。

比如银行转账，从A账户转100元至B账户，分为两个步骤：（1）从A账户取100元；（2）存入100元至B账户

这两步要么一起完成，要么一起不完成，如果只完成第一步，第二步失败，钱会莫名其妙少了100元。

C（Consistency）一致性

一致性也比较容易理解，也就是说数据库要一直处于一致的状态，事务的运行不会改变数据库原本的一致性约束。

例如现有完整性约束a + b = 10，如果一个事务改变了a，那么必须得改变b，使得事务结束后依然满足a + b = 10，否则事务失败。

I（Isolation）独立性（隔离性）

所谓的独立性是指并发（多个用户同时操作）的事务之间不会互相影响，如果一个事务要访问的数据正在被另外一个事务修改，只要另外一个事务未提交，它所访问的数据就不受未提交事务的影响。

比如现有有个交易是从A账户转100元至B账户，在这个交易还未完成的情况下，如果此时B查询自己的账户，是看不到新增加的100元的。

D（Durability）持久性

持久性是指一旦事务提交后，它所做的修改将会永久的保存在数据库上，即使出现宕机也不会丢失。

隔离所导致的一些问题：

脏读：

指一个事务读取另外一个事务未提交的数据.

不可重复读：

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

虚读（幻读）：

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

执行事务

-- mysql 是默认开启事务自动提交的
SET autocommit = 0 /* 关闭 */
SET autocommit = 1 /* 开启（默认的） */


-- ==============事务的完整执行流程==============
-- 手动处理事务
SET autocommit = 0 -- 关闭自动提交
-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的sql都在同一个事务内

INSERT xx 
INSERT xxx
-- (开启后有任意条操作执行失败，事务都提交不上去)

-- 提交：持久化(成功！)
COMMIT
-- 回滚：回到原来的样子(失败！)
ROLLBACK

-- 事务结束
SET autocommit = 1 -- 开启自动提交

-- 了解	（可以中途设置一个事务的保存点）
SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点
-- ==================================================

模拟场景

-- 转账
CREATE DATABASE shop CHARSET=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)

SELECT * FROM `account` -- 查看表

image-20220728152519049

-- 模拟转账：事务
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; -- 恢复默认值

-- 注意要逐条执行，一起执行可能会报错

提交事务成功后：

image-20220728153006527

image-20220728173552488

7.索引

MySQL官方对索引的定义为：**索引（Index）是帮助MySQL高效获取数据的数据结构。**如使用索引前要花0.5s，使用索引后只需0.000001s

提取句子主干，就可以得到数据的本质：索引是数据结构

7.1索引的分类

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

  • 主键索引（PRIMARY KEY）
    • 唯一的标识，主键不可重复，只能有一个列作为主键，如student表中的studentid每个数据的studentid不能相同
  • 唯一索引（UNIQUE KEY）
    • 避免重复的列出现，唯一索引可以重复，多个列都可以标识为 唯一索引
  • 常规索引（KEY/INDEX）
    • 默认的，index。key关键字来设置
  • 全文索引（FullText）
    • 在特定的数据库引擎下才有，MyISAM
    • 作用是快速定位数据
-- 索引的使用
-- 方法1.在创建表的时候给字段增加索引
-- 方法2.创建完毕后，增加索引

-- 显示所有的索引信息
SHOW INDEX FROM student

image-20220728155039309

-- 增加一个索引
ALTER TABLE  school.`student` ADD FULLTEXT INDEX `studentName`(`studentName`);

image-20220728155055199

-- EXPLAIN 分析sql执行的状况

EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('张');

7.2测试索引

100W 条数据测试

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),'19224305@qq.com',
		CONCAT('18',FLOOR(RAND()*(999999999-100000000))),
		FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
		SET i=i+1;
	END WHILE;
	RETURN i;
END;


SELECT mock_data()-- 执行此函数 生成一百万条数据

DROP FUNCTION mock_data; -- 用于删除函数 函数创建调用失败后，先删除再重新创建

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ECChXrGj-1687539943000)(https://gitee.com/MyStarOrbit/cloudimages/raw/master/https://gitee.com/MyStarOrbit/cloudimages/image-20220728172005391.png)]

SELECT * FROM app_user WHERE `name`='用户9999'-- 0.993 sec
SELECT * FROM app_user WHERE `name`='用户9999'-- 1.098 sec
SELECT * FROM app_user WHERE `name`='用户9999'-- 0.788 sec

EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999'

explain分析执行情况后发现搜了99W才找

image-20220728172110945

-- id_表名_字段名
-- CREATE INDEX 索引名 on 表（字段）
CREATE INDEX id_app_user_name ON app_user(`name`);
SELECT * FROM app_user WHERE `name`='用户9999'; -- 0.001sec

创建索引后发现只查了一次：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QuYpGkFW-1687539943001)(https://gitee.com/MyStarOrbit/cloudimages/raw/master/https://gitee.com/MyStarOrbit/cloudimages/image-20220728172757413.png)]

小结：索引在小数据量的时候，用处不大，但是在大数据的时候，区别十分明显~

7.3索引原则

  • 索引不是越多越好

  • 不要对进程变动数据加索引

  • 小数据量的表不需要加索引

  • 索引一般加在常用来查询的字段上！

索引的数据结构

Hash类型的索引

Btree：InnoDB的默认数据结构~

阅读：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

8.数据库备份

8.1用户管理

SQL yog可视化管理

创建用户：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-phaRhWmG-1687539943001)(https://gitee.com/MyStarOrbit/cloudimages/raw/master/https://gitee.com/MyStarOrbit/cloudimages/image-20220728175456293.png)]

权限选择：可以选择全局权限，也可以对具体数据库单独授权

image-20220728175546473

选择用户进行连接：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uX7y5Fb8-1687539943002)(https://gitee.com/MyStarOrbit/cloudimages/raw/master/https://gitee.com/MyStarOrbit/cloudimages/image-20220728175804489.png)]

连接完成：

用户管理：

image-20220728180027359

SQL命令操作

用户表：mysql.user

本质：对这张表进行增删改查

-- 创建用户   identified by被识别
-- CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER kuangshen IDENTIFIED BY'123456'

image-20220728181105289

-- 修改密码（修改当前用户密码）
SET PASSWORD = PASSWORD('123456')

-- 修改密码（修改指定用户密码）
SET PASSWORD FOR kuangshen=PASSWORD('111111')

-- 重命名 RENAME USER 原来的名字 TO 新的名字
RENAME USER kuangshen TO kuangshen2

注意测试连接时将%改为localhost或者127.0.0.1否则无法测试。

image-20220728181625850

-- 用户授权ALL PRIVILEGES 全部的权限， 库.表
-- ALL PRIVILEGES 除了给别人授权，其他的都能干
GRANT ALL PRIVILEGES ON *.* TO kuangshen

赋予权限后N变为Y

image-20220728182331994

-- 查看权限
SHOW GRANTS FOR kuangshen -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost -- 查看root的权限

-- ROOT用户权限：GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
-- WITH GRANT OPTION可以授予 授予权

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

-- 删除用户
DROP USER kaungshen

8.2MySQL备份

为什么要备份：

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

MySQL数据库备份的方式

  • 直接拷贝物理文件 date文件夹

  • 在Sqlyog等这种可视化工具中手动导出

    • 在想要导出的表或者库中，右键，选择备份或导出

  • 使用命令行导出 mysqldump 命令行使用

# mysql -h 主机 -u 用户名 -p 密码 数据库 	表名	>物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql

# mysql -h 主机 -u 用户名 -p 密码 数据库 	表1 表2 表3	>物理磁盘位置/文件名

# mysql -h 主机 -u 用户名 -p 密码 数据库> 物理磁盘位置/文件名

# 导入
# 登陆的情况下，切换到指定的数据库
# source 备份文件
mysql -uroot -p123456
use school;
source d:/a.sql;

mysql -u用户名 -p密码 库名<备份文件

image-20220728192056536

假设你要备份数据库，防止数据丢失。

把数据库给朋友！sql文件给别人即可！

9.规范数据库设计

9.1为什么需要设计

当数据库比较复杂的时候，我们就需要设计了

糟糕的数据库设计：

  • 数据冗余，浪费空间
  • 数据库插入和删除都会麻烦，会出现异常【屏蔽使用物理外键】
  • 程序的性能差

良好的数据库设计：

  • 节省内存空间
  • 保证数据库的完整性
  • 方便我们开发系统

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

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

设计数据库的步骤:（个人博客）

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

可设计：BBS（论坛系统） crm（管理系统） 引用Ant Design

9.2.数据库的规约-三大范式

为什么需要数据规范化？

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

三大范式

第一范式（1NF）

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

第二范式（2NF）

前提：满足第一范式

每张表只描述一件事情

第三范式（3NF）

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

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

（规范数据库的设计）

规范性 和 性能的问题

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

  • 考虑商业化的需求和目标，（成本，用户体验！）数据库的性能更加重要
  • 在规范性能的问题的时候，需要适当的考虑一下 规范性！
  • 有时故意给某些表增加一些冗余的字段。（从多表查询变为单表查询）
  • 有时故意增加一些计算列（从大数据量降低为小数据量的查询：索引）

9.3计算列（生成列）

  1. https://tonydong.blog.csdn.net/article/details/104119208?spm=1001.2101.3001.6650.11&utm_medium=distribute.pc_relevant.none-task-blog-2~default~CTRLIST~default-11-104119208-blog-46006075.pc_relevant_multi_platform_whitelistv3&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2~default~CTRLIST~default-11-104119208-blog-46006075.pc_relevant_multi_platform_whitelistv3&utm_relevant_index=14
  2. https://blog.csdn.net/qq_41956139/article/details/104249295?spm=1001.2101.3001.6650.1&utm_medium=distribute.pc_relevant.none-task-blog-2~default~CTRLIST~default-1-104249295-blog-46006075.pc_relevant_multi_platform_whitelistv3&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2~default~CTRLIST~default-1-104249295-blog-46006075.pc_relevant_multi_platform_whitelistv3&utm_relevant_index=2
  3. https://blog.csdn.net/weixin_42830697/article/details/103500806?spm=1001.2101.3001.6661.1&utm_medium=distribute.pc_relevant_t0.none-task-blog-2~default~CTRLIST~default-1-103500806-blog-104249295.pc_relevant_multi_platform_whitelistv1_exp2&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-2~default~CTRLIST~default-1-103500806-blog-104249295.pc_relevant_multi_platform_whitelistv1_exp2&utm_relevant_index=1
-- 创建
CREATE DATABASE test CHARACTER SET utf8 COLLATE utf8_general_ci;
USE test;
CREATE TABLE t_circle(
   id INTEGER PRIMARY KEY,
   X NUMERIC(20,10) NOT NULL,
   Y NUMERIC(20,10) NOT NULL,
   radius NUMERIC(20,10) NOT NULL, -- 半径
   perimeter NUMERIC(20,10) AS (2 * 3.14159265 * radius) -- 周长
);
ALTER TABLE t_circle ADD AREA NUMERIC(20,10) 
AS(3.14159265 * radius * radius) STORED; -- 面积
/*其中，perimeter 是一个虚拟的生成列；area 是一个存储的生成列。MySQL 生成列还支持NOT NULL、UNIQUE、主键、CHECK以及外键约束，但是不支持DEFAULT默认值。

MySQL 生成列的表达式必须遵循以下规则：

允许使用常量、确定性的内置函数以及运算符。确定性函数意味着对于表中的相同数据，多次调用返回相同的结果，与当前用户无关。非确定性的函数包括 CONNECTION_ID()、CURRENT_USER()、NOW() 等。
不允许使用存储函数和自定义函数。
不允许使用存储过程和函数的参数。
不允许使用变量（系统变量、自定义变量或者存储程序中的本地变量）。
不允许子查询。
允许引用表中已经定义的其他生成列；允许引用任何其他非生成列，无论这些列出现的位置在前面还是后面。
不允许使用 AUTO_INCREMENT 属性。
不允许使用 AUTO_INCREMENT 字段作为生成列的基础列。
如果表达式的运算导致截断或者为函数提供了不正确的输入，CREATE TABLE 将会失败。
另外，如果表达式的结果类型与字段定义中的数据类型不同，将会执行隐式的类型转换。
*/

接下来我们运行一些数据测试：

INSERT INTO t_circle(id, x, y, radius) VALUES (1, 2, 2, 5);
SELECT * FROM t_circle;
id|x           |y           |radius      |perimeter    |area         |
--|------------|------------|------------|-------------|-------------|
 1|2.0000000000|2.0000000000|5.0000000000|31.4159265000|78.5398162500|

INSERT INTO t_circle(id, x, y, radius ,perimeter) VALUES (2, 0, 0, 1, 6.28318530);

SQL Error [3105] [HY000]: The value specified for generated column 'perimeter' in table 't_circle' is not allowed.

第一个插入语句没有指定生成列的值，由数据库自动计算；第二个插入语句为 perimeter 提供了数据，执行失败；不过可以再创建表设置字段的时候使用DEFAULT关键字设置默认值。

10.JDBC

10.1数据库驱动

驱动：声卡，显卡，数据库

image-20220729165351158

10.2JDBC

SUN公司为了简化 开发人员的（对数据库的统一）操作，提供了一个（Java操作数据库）的规范，俗称JDBC

这些规范的实现由具体的厂商去做~

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

image-20220729170543655

– 狂神说是业务级别的MySQL学习

– 运维级别需深入学习

除了java自带的两个包：java.sql、javax.sql，还需要导入一个数据库驱动包

10.3第一个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.右键新建lib库目录，导入数据库驱动（导入架包）

导入完毕

image-20220729174619373

3.编写测试代码

package com.starry.lesson01;

import java.sql.*;

// 我的第一个JDBC程序
public class JdbcFristDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");//固定写法，加载驱动
        //2.用户信息和url
        //useUnicode=true&characterEncoding=utf8&useSSL=true支持中文编码，设置中文字符集，设置安全编码
        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作为执行sql的对象
        Statement statement = connection.createStatement();

        //5.执行SQL的对象 去 执行SQL，可能存在结果，查看返回结果
        String sql = "SELECT * FROM users";
        //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();
    }
}

步骤总结：

1.加载驱动

2.连接数据库DriverManager

3.获得执行sql的对象 Statement

4.获得返回的结果集

5.释放连接

DriverManager

//DriverManager.registerDriver(new com.mysql.jdbc.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=true";

//mysql默认端口号3306
//协议//主机地址+端口号//数据库名?参数1&参数2&参数3
//oralce --1521
//jdbc:oralce:thin:@localhost:1521:sid

Statement执行SQL的对象 PerpareStatement执行SQL的对象

	//编写SQL
	String sql = "SELECT * FROM users";
	statement.executeQuery();//查询操作返回 ResultSet
	statement.execute();//执行任何SQL
	statement.executeUpdate();//更新、插入、删除。都是用这个，返回一个受影响的行数

ResultSet查询的结果集：封装了所有的查询结果

获得指定的数据类型

        resultSet.getObject();//在不知道列类型的情况下使用
	//如果知道列的类型就使用指定的类型
        resultSet.getString();
        resultSet.getInt();
        resultSet.getFloat(); 
        resultSet.getDate();
... ...

遍历，指针

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

释放资源

//耗资源，用完要关掉！
resultSet.close();
statement.close();
connection.close();

10.4statement对象

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

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

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

CRUD操作-create

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

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

CRUD操作-delete

使用executeUpdate(String sql)方法完成数据删除操作，可直接用excute，示例操作：

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

CRUD操作-update

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

CRUD操作-query

使用excuteQuery(String sql)方法完成数据查询操作，可直接用excute，示例操作：

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

代码实现

目的类属性和功能分离，方法就是配置文件+工具类

知识点：面向对象、JDBC、异常处理、IO、集合

1.提前工具类

package com.starry.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 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 (IOException e){
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

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

2.编写增删改的方法，excuteUpdate

package com.starry.lesson02;

import com.starry.lesson02.utils.JdbcUtils;

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

public class Testlnsert {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();//获取数据库连接
            st = conn.createStatement();//获得SQL的执行对象
            String sql = " INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)\n" +
                    " VALUES(4,'starry','123456','1078904721@qq.com','2022.7.29')";
        int i = st.executeUpdate(sql);
        if(i>0){
            System.out.println("插入成功！");
        }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JdbcUtils.release(conn,st,rs);
        }

    }
}


package com.starry.lesson02;

import com.starry.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();//获得SQL的执行对象
            String sql = " DELETE FROM `users` WHERE id=4";
            int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("删除成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JdbcUtils.release(conn,st,rs);
        }
    }
}


package com.starry.lesson02;

import com.starry.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 {
            conn = JdbcUtils.getConnection();//获取数据库连接
            st = conn.createStatement();//获得SQL的执行对象
            String sql = " UPDATE users SET `NAME`='starry',`email`='1078904721@qq.com' 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.查询

package com.starry.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();

           //SQL
            String sql="select * from users where id = 1";

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

            rs = st.executeQuery(sql);//查询完毕后会返回一个结果集
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

SQL注入的问题

Sql存在漏洞，会被攻击导致数据泄露，SQL会被拼接 通过or来拼接

package com.starry.lesson02.utils;

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) {
        //login("starry","123456"); //正常登陆
        login("'or'1=1","'or'1=1");//SQL注入登陆
    }

    //登陆业务
    public static void login(String username,String password){
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();

            //SELECT * FROM users WHERE `name`= 'starry'AND`password`='123456';
            //SELECT * FROM users WHERE `name`= ‘‘or'1=1'AND`password`=''or'1=1';
            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) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

10.5PreparedStatement对象

PreparedStatement 可以防止SQL注入。效率更好！

1.新增

package com.starry.lesson03;

import com.starry.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

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

        try {
            conn = JdbcUtils.getConnection();

            //区别
            //使用？ 占位符代替参数
            String sql = "insert into `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)values(?,?,?,?,?)";
           st = conn.prepareStatement(sql);//预编译SQL：先写sql，然后不执行

            //手动给参数赋值
            st.setInt(1,4);
            st.setString(2,"qinjiang");
            st.setString(3,"123456");
            st.setString(4,"24734673@qq.com");
            //注意点：      sql.Date  数据库用的
            //             util.Date    Java用的  new Date().getTime() 获得时间戳
            st.setDate(5,new java.sql.Date(new Date().getTime()));

            //执行
            int i = st.executeUpdate();
            if(i>0){
                System.out.println("插入成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JdbcUtils.release(conn,st,null);
        }

    }
}

2.删除

package com.starry.lesson03;

import com.starry.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

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

        try {
            conn = JdbcUtils.getConnection();

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

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

            //执行
            int i = st.executeUpdate();
            if(i>0){
                System.out.println("删除成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JdbcUtils.release(conn,st,null);
        }
    }
}

3.修改

package com.starry.lesson03;

import com.starry.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

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

        try {
            conn = JdbcUtils.getConnection();

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

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

            //执行
            int i = st.executeUpdate();
            if(i>0){
                System.out.println("更改成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JdbcUtils.release(conn,st,null);
        }
    }
}

4.查询

package com.starry.lesson03;

import com.starry.lesson02.utils.JdbcUtils;

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 = JdbcUtils.getConnection();

             String sql = "select * from users where id = ?";

             st = conn.prepareStatement(sql);//预编译

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

             st.executeQuery();//执行
             if(rs.next()){
                 System.out.println(rs.getString("NAME"));
             }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }

    }
}

5.防止SQL注入

package com.starry.lesson03;

import com.starry.lesson02.utils.JdbcUtils;

import java.sql.*;

public class SQL注入 {
    public static void main(String[] args) {
        //login("小明","123456"); //正常登陆
        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 = JdbcUtils.getConnection();
            //PreparedStatement防止SQL注入的本质，把传递进来的参数当做字符
            //假设其中存在转义字符，比如说 ' 会被直接转义
            String sql="select * from users where `NAME`=? and `PASSWORD`=?";//Mybatis框架

            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) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

10.6使用IDEA连接数据库

image-20220730162017514

image-20220730162442244

连接成功后，可以选择数据库

image-20220730162732829

双击查看数据库

image-20220730162919480

更新（修改）数据

image-20220730163046586

编写数据库

image-20220730163224960

image-20220730163516205

10.7事务

要么都成功，要么都失败

ACID原则

原子性：要么全部完成，要么都不完成

一致性：总数不变

隔离性：多个进程互不干扰

持久性：一旦提交不可逆，持久化到数据库了

隔离性的问题：

脏读：一个事务读取了另一个没有提交的事务

不可重复读：在一个事务内，重复读取了表中的数据，表数据发生了改变

虚读（幻读）：在一个事务内，读取到了别人插入的数据，导致前后读出来的结果不一致

代码实现

1.开启事务 conn.setAutoCommit(false);

2.一组业务执行完毕，提交事务

3.可以在catch语句汇总显式地定义 回滚语句，但默认失败就会回滚

package com.starry.lesson04;

import com.starry.lesson02.utils.JdbcUtils;

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

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

        try {
            conn = JdbcUtils.getConnection();
            //关闭数据库的自动提交，自动会开启事务
            conn.setAutoCommit(false);


            String sql1="update account set money = money-100 where name='A'";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();

            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 e1){
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

image-20220730165319269

10.8数据库连接池

数据库连接—执行完毕—释放

连接–释放 十分浪费系统资源

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

举例：如银行办理业务

如果银行给你一个人办理事务，先开门，然后服务，结束关门。下面再服务下面一个人就重复步骤，开门关门就显得非常麻烦。

现在开门之后准备一些业务员，这些业务员等待客户，不管有没有客户都在等待服务，除非服务器关闭，业务员才下班。

-------开门—业务员：等待 – 服务—

常用连接数：10个

最小连接数：10个 剩下5个备用

最大连接数：15个 业务最高承载上限

等待超时：100ms

编写连接池，实现一个接口DateSource

开源数据源实现

DBCP

C3P0

Druid：阿里巴巴

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

DBCP

需要用到的jar包

commons-dbcp-1.4、commons-pool-1.6

C3P0

需要用到的jar包

c3p0-0.9.5.5、mchange-commons-java-0.2.19

结论

无论使用什么数据源，本质还是一样的，DateSource接口不会变，方法就不会变

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

数据库

mysql

学习

MySQL数据库学习（保姆级教程）（1.7W字） 的相关文章

随机推荐

  • opencv图像灰度重心算法

    原文 http blog csdn net moses1213 article details 44679603 导师交给的项目 其中一步就是求光斑的重心 网上有很多关于重心的代码 大体是利用cvFindContour函数找出图像的轮廓 然

  • 开源项目 xijia-plus 启动教程 (通用后管理系统)

    一 说明 xijia plus 是什么 xijia plus 是一个 通用后管理系统 脚手架 采用 springboot vue 进行开发 可以在该脚手架进行业务的快速开发 xijia plus 可以做什么 如果你想快速开发一个项目 可以用

  • Graphpad Prism9.5.1 安装教程 (含Win/Mac版)

    GraphPad Prism GraphPad Prism是一款非常专业强大的科研医学生物数据处理绘图软件 它可以将科学图形 综合曲线拟合 非线性回归 可理解的统计数据 数据组织结合在一起 除了最基本的数据统计分析外 还能自动生成统计图 安

  • 【程序员必须要掌握哪些算法】

    一个程序员一生中可能会邂逅各种各样的算法 但总有那么几种 是作为一个程序员一定会遇见且大概率需要掌握的算法 今天就来聊聊这些十分重要的 必抓 算法吧 你可以从以下几个方面进行创作 仅供参考 一 引言 算法作为程序员的核心技能之一 在软件开发

  • 安卓面试之轻松战胜内存优化问题

    熟悉如何内存优化 无疑是安卓工程师进阶的一个必要条件 同时也是面试的重点和难点 面试常见问题 1 如何优化内存 2 如何加载10M大小的图片 3 如何线上监控内存 为什么要优化内存 移动设备中 内存是非常重要的资源 如果内存使用不当 轻则出

  • 利用短时傅里叶变换（STFT）对信号进行时频谱分析和去噪声

    利用短时傅里叶变换 STFT 对信号进行时频谱分析和去噪声 1 背景 傅里叶变换 TF 对频谱的描绘是 全局性 的 不能反映时间维度局部区域上的特征 人们虽然从傅立叶变换能清楚地看到一整段信号包含的每一个频率的分量值 但很难看出对应于频率域

  • 基于Spring Gateway路由判断器实现各种灰度发布场景

    文章目录 1 灰度发布实现 1 1 按随机用户的流量百分比实现灰度 1 2 按人群划分实现的灰度 1 2 1 通过Header信息实现灰度 1 2 2 通过Query信息实现灰度 1 2 3 通过RemoteAdd判断来源IP实现灰度 2

  • django中models field详解

    本文参考自 django官方文档models field 在model中添加字段的格式一般为 field name field type field options 一 field options 所有字段共用 1 null 默认为Fals

  • 滤波器拓扑结构：Sallen-key和Multiple Feedback

    在一些关于滤波器设计的地方 总可以看到Sallen key和Multiple Feedback这两个词组 但不清楚什么意思 查了查资料 顺带在此处记录一下 Sallen key 麻省理工学院林肯实验室的R P Sallen and E L

  • Android Studio第一次安装虚拟机时报错Emulator:ERROR: Unknown AVD name[ ], use -list-avds to see valid list.

    安装完虚拟机后点击启动报错 虚拟化已开启 解决办法 1 修改环境变量ANDROID SDK HOME路径指到platforms路径下 例如 D androidSDK platforms 2 重启Android Studio 3 重新安装虚拟

  • 学习笔记：CentOS7安装Docker

    一 检查CentOS 系统的内核版本 Docker 要求 CentOS 系统的内核版本高于 3 10 通过 uname r 命令查看当前的内核版本 二 检查并清除系统残余项 并安装Docker依赖环境 1 卸载Docker 可选 如果之前安

  • 百度新闻资讯类信息爬虫--统计一年内关键词新闻的条数

    背景 通过百度词条搜索 来查找300个关键词 在一年内发布新闻的条数 最终效果实现如下 实现思路 实现思路依然是 先根据多页的url 来找到规律 构建起一页的url def format url url params dict None g

  • [转]信息安全相关理论题(三)

    21 静态分析是运行程序后进行调试 A 对 B 错 您的答案 标准答案 B 22 安卓反编译后会出现 符号字节码表示是匿名内部类 A 对 B 错 您的答案 标准答案 A 23 反编译安卓应用后 一般应该先查看哪一个smali文件的代码 A

  • JAVA反射机制及应用场景

    往往当我们面对一项新的知识时 我们往往需要知道三个方面 它是什么 它能做什么 它比原有知识强在哪里 我们该怎么使用它 当你能够解决这些问题时 便意味着你已经对这项知识入门了 一 是什么 Java Reflaction in Action有这

  • TOGAF9.2第I部分 第2章核心概念

    本章提供的核心概念适用TOGAF标准 2 1 什么是TOGAF标准 TOGAF标准是一个架构框架 它提供了协助接受 生产 使用和维护企业架构的方法和工具 它基于支持最佳实践和可重用的现有架构资产集的迭代过程模型 2 2 TOGAF标准中的架

  • 学习笔记——机器学习（第二章)

    机器学习 第二章 还有很多细节部分 我正在完善和补充 Emmm 若有不足 还请包涵 1 2 3 4 5 6 7 8 9 10 11 12 13 14

  • 致性哈希算法的优化----关于如何保正在环中增加新节点时，命中率不受影响

    背景 09年初 我们做了一个memcached的智能客户端库 业务只要将这个库链上 就能跟memcached服务器通信 并且实现了一致性哈希的分布式算法 后端memcached服务器可以无限制扩展 而且客户端能对memcached做自动故障

  • cmake系列-动态库的生成与链接

    运行系统 Ubuntu20 04 运行环境 python 2 7 17 系统不一样 遇到的问题可能不一样 该方法不一定见效 问题描述 工作中时常需要调用同事写的 so文件作为一些功能的接口 那么如何将cmake文件进行动态库生成和调用呢 实

  • redissonclient类_Redisson入门教程

    Redisson入门 Author RickyDate 2017 04 24 Redisson概述 Redisson是架设在Redis基础上的一个Java驻内存数据网格 In Memory Data Grid 充分的利用了Redis键值数据

  • MySQL数据库学习（保姆级教程）（1.7W字）

    1 初识MySQL JavaEE 企业级Java开发 Web 前端 页面 展示 数据 后台 连接点 连接数据库JDBC 链接前端 控制 控制视图跳转 和给前端传递数据 数据库 存数据 Txt Excel Word 只会写代码 学好数据库 基

热门标签