devbox
小舞很执着
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

MySql笔记（狂神）_狂神mysql笔记

作者：小舞很执着 | 2024-07-17 16:04:37

赞

踩

狂神mysql笔记

MySQL

1、初识MySQL

javaEE ： 企业级Java开发 web

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

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

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

1.1、什么是数据库

数据库（DB ， DataBase）

概念：是一个数据仓库，是安装在操作系统（windows，linux，max…）上的软件,可以存储大量的数据，500万条都可以，使用后面会卡，使用SQL语句操作。

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

1.3、数据库分类

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

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

非关系型数据库：（NoSQL）NOT Only 不全是sql

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

DBMS（数据库管理系统）

  • 数据库管理软件，科学有效的管理我们的数据。维护和获取数据。
  • MySQL，可以看成一个数据库管理系统，因为它可以操作和管理数据。

1.3、MySQL简介

MySQL是一个**关系型数据库管理系统****，**由瑞典[MySQL AB](https://baike.baidu.com/item/MySQL AB/2620844?fromModule=lemma_inlink) 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

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

MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型和大型网站的开发都选择 MySQL 作为网站数据库。

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

稳定5.7

8.0

安装建议：

1、尽量不要使用exe，注册表

2、使用压缩包

1.4、下载MySQL

1.5、下载可视化工具

1、sqlyog

下载可以百度

下载完无脑安装，安装之后需要注册密钥，我找到一个网站：

https://www.shuzhiduo.com/A/6pdD1GLRzw/

里面实测可以使用

后面配置的话就是新建一个连接，名字随便，然后主机名写localhost

然后写MySQL设置的密码，剩下默认的就好 端口号是：3306

配置好就这样子：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4femDlGT-1675761729945)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221020164650856.png)]

其中系统默认有四个数据库，别动，然后自己创建自己的数据库就好了。

2、第二个是NaciviCat15 听说挺好用

后面实测之后在看。

安装自己在网上找教程

1.6、使用

1、

我们在可视化工具中每点击一次，也就是操作表，后台就会生成一条对应的sql语句来操作数据库，所以可视化工具的实质还是在使用sql语句操作数据库，只是我们使用者不需要写。后台的sql语句可以在历史记录（日志）中查看。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uSfHQD5g-1675761729946)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221020165611580.png)]

NaciviCat15的方法是ctrl+L 或者工具–>历史日志

sqlyog可以直接看到

​

2、

创建数据库

连接好数据库之后我们可以右键我们数据库，然后选择新建数据库

之后按照下图的选项就可以

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-USVOs4ZS-1675761729947)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221020215207584.png)]

然后进去之后选择要创建的列名属性什么的：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0CdaNmAy-1675761729947)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221020171837088.png)]

3、

创建表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Nropso9z-1675761729947)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221020171407610.png)]

1.7、使用命令行使用Mysql

打开cmd，输入命令

mysql -uroot -p+密码
或者可以直接到-p结束，下一行输密码，

  • 1
  • 2

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lyf0yBAU-1675761729948)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221020172133070.png)]


-- 所有的语句都要使用;结尾  

show databases; --查询所有的数据库

use school; --切换数据库   use + 数据库名
show tables; --查看数据库中所有的表
describe student; --查看表中的所有信息（这个是查看表结构的）   ----describe + 表名  select是查看表中详细信息的  select * from xxx;




  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SploYJw9-1675761729948)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221020172725794.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rY9aUA0H-1675761729948)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221020173111967.png)]

create database westos;  --创建数据库

exit --直接退出

-- 代表注释

  • 1
  • 2
  • 3
  • 4
  • 5

写sql代码在Navicat里面写了 点击新建查询 然后就可以写了

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mAvUJMkR-1675761729949)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221020174342612.png)]

数据库xxx语言 CRUD 增删改查

DDL 定义

DML 操作

DQL 查询

DCL 控制

2、操作数据库

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

2.1、操作数据库

1、创建数据库

CREATE DATABASE [IF NOT EXISTS] westos;
--创建数据库  带[]里面的语句是可选的，如果重名就退出

  • 1
  • 2

2、操作数据库

 -- 如果 你的表名或者字段名是一个特殊字符，就需要带  ``(飘号，在tab的上面)
use `school`;

  • 1
  • 2

3、删除数据库

drop database if exists hello;




  • 1
  • 2
  • 3
  • 4

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-F6gK0EUL-1675761729949)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221020213618122.png)]

4、查看数据库

show databases;

  • 1

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-c5Nigiu6-1675761729949)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221020214456952.png)]

学习思路：可以对比sql语句和Navicat的操作

  • 对照Navicat的可视化记录查看sql
  • 固定的语法或关键字必须要强行记住

2.2、数据库的类型

数值

  • tinyint 十分小的数据 占1个字节
  • smallint 较小的数据 占2个字节
  • mediumint 中等大小的字节 占3个字节
  • int 标准的数据 4个字节 （常用）
  • big 较大的数据 8个字节
  • float 单精度浮点数 4个字节
  • double 双精度浮点数 8个字节 （精度问题）
  • decimal 字符串的浮点数 金融计算的时候 一般使用decimal

字符串

  • char 字符串固定大小 0~255
  • varchar 可变长字符串 0~65535 （常用的变量）string
  • tinttext 微型文本 2^8 - 1
  • text 文本串 2^16 -1 (保存大文本)

时间日期

java.util.Data

  • data YYYY-MM-DD 日期格式
  • time HH: mm:ss 时间格式
  • datetime YYYY-MM-DD HH: mm:ss 最常用的时间格式
  • timestamp 时间戳 19701.1.到现在的毫秒数！ 也常用
  • year 年份表示

null

没有值 未知

注意：不要使用NULL进行运算 结果为NULL

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

Unsigned:

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JGoVzst5-1675761729950)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221020222640652.png)]

zerofii：

  • 0填充的
  • 不足的位数，用0来填充，int(3), 5—>005

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-t2580sQP-1675761729950)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221020234556970.png)]

自增：

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ItyGEbZT-1675761729950)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221020234615684.png)]

非空： Null not null

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2OFGu5sR-1675761729951)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221020234648645.png)]

默认：

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sMtOe4U4-1675761729951)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221020234712604.png)]

注意：varchar类型需要用单引号写，不然会报错；

/*
每一个表，都必须存在以下五个字段  （阿里规范）
id   主键
`version`  乐观锁
is_delete   伪删除
gmt_create    创建时间
gmt_update     修改时间
*/


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

2.4、建表

CREATE TABLE `xxx`.`stu1`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(20) NOT NULL DEFAULT '昭昭' COMMENT '姓名',
  `pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
  PRIMARY KEY (`id`)
)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
 -- 目标：创建一个school数据库
 -- 创建学生表（列，字段） 使用sql创建
 -- 学号int   登陆密码varchar（20） 姓名，性别varchar（2）  	   出生日期（datatime），家庭住址，email
 
 
 -- 注意点：使用英文的()  表的名称和字段用``括起来（反引号）
 -- auto_increment   自增
 -- 字符串使用 单引号括起来！
 -- 所有的语句后面都加,（英文的）,最后一个不用加
 -- commit  备注
 -- default 默认值   default null   默认值为null
 -- not null  不能为空
 -- primary key 主键  一般一个表只有一个唯一的主键 可以写在最后面也可以写前面
 -- engine 引擎
 CREATE TABLE IF NOT EXISTS `stu2` (
			`id` int not null auto_increment COMMENT '学号',
			`name` varchar(20) NOT NULL DEFAULT '昭昭' COMMENT '姓名',
			`pwd` varchar(20) not null default '123456' COMMENT '密码',
			`sex` varchar(2) not null default '男' COMMENT '性别',
			`brithyday` datetime default null COMMENT '出生日期',
			`address` varchar(100) default null COMMENT '家庭住址',
			`email` varchar(60) default null COMMENT '电子邮箱',
			primary key (`id`)
			
			
 )engine=innodb default charset=utf8;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
 -- 格式：
CREATE TABLE [IF NOT EXISTS] `表名`(
	`属性名`  类型   [] [][]...  [注释],
    `属性名`  类型   [] [][]...  [注释],
    `属性名`  类型   [] [][]...  [注释],
    `属性名`  类型   [] [][]...  [注释],
    [主键设置]
    
)[引擎] [字符集设置] [注释] 

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

常用命令

show create database zhaozhao;  
 -- 查看创建将数据库的语句

  • 1
  • 2

CREATE DATABASE zhaozhao /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci / /!80016 DEFAULT ENCRYPTION=‘N’ */

  -- 查看stu2数据表的定义语句 
 show create table stu2;

  • 1
  • 2

CREATE TABLE stu2 (
id int NOT NULL AUTO_INCREMENT COMMENT ‘学号’,
name varchar(20) NOT NULL DEFAULT ‘昭昭’ COMMENT ‘姓名’,
pwd varchar(20) NOT NULL DEFAULT ‘123456’ COMMENT ‘密码’,
sex varchar(2) NOT NULL DEFAULT ‘男’ COMMENT ‘性别’,
brithyday datetime DEFAULT NULL COMMENT ‘出生日期’,
address varchar(100) DEFAULT NULL COMMENT ‘家庭住址’,
email varchar(60) DEFAULT NULL COMMENT ‘电子邮箱’,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

 
--  显示表的结构
 DESC `stu2`;

  • 1
  • 2
  • 3

2.5、数据表的类型

 -- 关于数据库引擎
 /*
 INNODB  默认引擎
 MYISAM  早些年使用的
 */

  • 1
  • 2
  • 3
  • 4
  • 5
MYISAMINNODB
事务支持不支持支持
数据行锁定不支持支持
外键约束不支持支持
全文索引支持不支持（Mysql5.6之后支持全英文索引）
表空间大小较小大，约是前者两倍

常规使用操作：

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

在物理空间存在的位置

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

本质上还是以文件形式存储的

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

  • innoDB 在数据库表中只有一个*.frm文件，以及上级目录的ibdata1文件里
  • MYISAM 对应文件
    • *.frm 表结构的定义文件 MySQL８.０之后删除了.frm 文件
    • *.MYD 数据文件 （data）
    • *.MYI 索引文件 （index）

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

charset=utf8

  • 1

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

MySQL的默认编码是Latin1，支不支持中文 （MySQL8.0之后默认编码是utf8mb4）

2.6、修改和删除表

 -- 修改表
	alter table `stu` rename as `stu1`;
	
	
	-- 语法
	alter table `旧表名` rename as `新表明`;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

在Navice写sql代码时候记得每句后面加英文分号。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KZKOHRRB-1675761729951)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221021151339239.png)]

	-- 增加表的字段  alter table `表名` add 属性名 数据类型;
	alter table `stu1` add score int(11);

  • 1
  • 2

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Qv1alvVQ-1675761729951)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221021152146809.png)]

-- 修改的字段（修改字段，重命名）
	alter table `stu1` modify score char(2); -- 修改字段
	alter table `stu1` change score score1 int(11);-- 重命名

  • 1
  • 2
  • 3

sql语句中modify和change的区别：

change用来字段重命名，不能修改字段类型和约束；

modify不用来字段重命名，只能修改字段类型和约束；

可参考博客： https://www.cnblogs.com/mood-butterfly/p/6509472.html

我自己也试过了 change确实不能单独修改字段的类型 但是如果是在重命名的时候就可以。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dyarsLZt-1675761729952)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221021154038175.png)]

前两个都成功了 最后一个语句会报错。

删除

	-- 删除表的字段
	alter table `stu1` drop score1;
	
	-- 语法
	 -- alter table `表名` drop 字段名

  • 1
  • 2
  • 3
  • 4
  • 5
DROP TABLE if EXISTS stu1; -- 删除表

  • 1

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

注意点：

  • `` 字段名，使用这个包裹
  • 注释 – /**/
  • sql 关键字大小写不敏感
  • 所有符号都用英文！！！

3、MySQL数据管理

3.1、外键（了解）

外键就是两个表有联系，其中一个表的列元素，跟另外一个表有联系，而且另外一个表的主键刚好就是这个有联系的列元素，这就需要外键来建立他们的关系。

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


 -- 学生表的gradeid字段 要去引用年级表的gradeid
 -- 定义外键key
 -- 给这个外键添加约束  （执行引用） references 引用

CREATE TABLE IF NOT EXISTS `stu3` (
			`id` int not null auto_increment COMMENT '学号',
			`name` varchar(20) NOT NULL DEFAULT '昭昭' COMMENT '姓名',
			`pwd` varchar(20) not null default '123456' COMMENT '密码',
			`sex` varchar(2) not null default '男' COMMENT '性别',
			`brithyday` datetime default null COMMENT '出生日期',
			`address` varchar(100) default null COMMENT '家庭住址',
			`email` varchar(60) default null 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;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
 CREATE TABLE `grade` (
	`gradeid` INT(10) NOT NULL auto_increment COMMENT '年级id',
	`gradename` VARCHAR(23) NOT NULL COMMENT '年级名称',
	PRIMARY KEY (`gradeid`)
 )engine=innodb default charset=utf8;

  • 1
  • 2
  • 3
  • 4
  • 5

可视化工具在这里设置外键

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vVyRQ7Jy-1675761729952)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221021201100092.png)]

删除有外键的关系的表也比较麻烦，要先删除引用别人的表（从表），

在删除被引用的表（主表）。

方法二 创建表的时候没有外键 然后添加外键约束


 CREATE TABLE `grade` (
	`gradeid` INT(10) NOT NULL auto_increment COMMENT '年级id',
	`gradename` VARCHAR(23) NOT NULL COMMENT '年级名称',
	PRIMARY KEY (`gradeid`)
 )engine=innodb default charset=utf8;





CREATE TABLE IF NOT EXISTS `stu3` (
			`id` int not null auto_increment COMMENT '学号',
			`name` varchar(20) NOT NULL DEFAULT '昭昭' COMMENT '姓名',
			`pwd` varchar(20) not null default '123456' COMMENT '密码',
			`sex` varchar(2) not null default '男' COMMENT '性别',
			`brithyday` datetime default null COMMENT '出生日期',
			`address` varchar(100) default null COMMENT '家庭住址',
			`email` varchar(60) default null COMMENT '电子邮箱',
			`gradeid` int(10) not null comment '学生的年级',
			primary key (`id`)
			
			
			
 )engine=innodb default charset=utf8;


 
 
 -- 创建表的时候没有外键关系  这里是grade 和stu3
	ALTER TABLE `stu3` 
	ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);
	
	
	-- 公式：
	-- alter table `表名` add constraint `约束名` forgeign key (`作为外键的名`) references `表名` （`表的字段`）;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36

以上的操作都市物理外键，数据库级别的外键，删除比较麻烦（关系复杂的时候），不建议使用，（避免数据库过多造成困扰）

最佳实践

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

3.2、DML语言（重点）

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

DML语言：数据操作语言

  • insert 添加
  • update 修改
  • delete 删除

3.3、添加

insert

-- 插入语句  
 -- INSERT into `表名`（[字段1,字段2,字段3...]）values ('值1','值2','值3'...);
 insert into `grade` (`gradename`) VALUES ('大四');
 
 -- 由于主键自增我们可以省略（如果不写表的字段，会一一匹配）
 insert into `grade` VALUES ('大四');
 
 -- 一般写插入语句，我们一定要数据和字段一一对应
 -- 插入多个字段
 INSERT INTO `grade` (`gradename`) VALUES('大二'),('大一');
 
 INSERT into `stu2` (`name`) VALUES ('张三');
 
 -- 插入多个数据
 INSERT into `stu2` (`name`,`pwd`,`sex`) VALUES ('zz','99999','女');
 
  INSERT into `stu2` (`name`,`pwd`,`sex`) VALUES ('zz','99999','女'),('bb','32313','女'),('qq','4234234','女'),('aaa','334234','男');

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

注意：

1、插入的时候，如果是很多字段后面values值在一个括号里写用逗号隔开，注意类型，如果是同一个字段想要一次多次赋值的话，就需要每个值用单独的括号写用逗号隔开，也就是前者只插入一行的数据用一个括号，后者插入多行数据，用多个括号隔开，插入几行就几个括号。

2、字段是可以省略的，但是后面的值必须要一一对应（全部写）

3.4、修改

 
 -- 修改语句
 -- 修改学院的名字  带了条件
 update `stu2` set `name`='昭昭啊' where id=1;
 
 -- 不带指定条件的话，会默认修改所有的
 UPDATE `stu2` set `name` = 'zbq';
 
 -- 语法： 
 -- update `表名` set `字段名`= '要修改的值' where 条件;
 
 
  -- 修改多个属性，使用逗号隔开
  update `stu2` set `name`='昭昭啊',email='3321472502@qq.com' where id=1;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

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

操作符会返回布尔值

操作符含义范围结果
=等于5=6false
<>或!=不等于5<>6true
>大于5>6false
<小于5<6true
>=大于等于5>=6false
<=小于等于5<=6true
between…and…在一个范围[5,6]
and且&&5>1 and 1>2false
or或||5>1 or 1>2true

语法：update 表名 set colnum_name = value,[colnum_name = value,...] where [条件]

注意：

  • colnum_name 是数据库的列 ，尽量带上``
  • 条件，筛选的条件，如果没有指定，则会修改所有列的属性
  • value，是一个具体的值，也可以是一个变量
  • 多个设置的属性，使用英文逗号隔开
 
 UPDATE `stu2` set `brithyday` = CURRENT_TIME WHERE id=1;

  • 1
  • 2

3.5、删除

delete 命令

语法：delete from 表名 where 条件

	  DELETE FROM stu2 where id=2;
	  --  删除数据

  • 1
  • 2

TRUNCATE 命令

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

 -- 清空stu2表
 truncate stu2;
 

  • 1
  • 2
  • 3

delete 和 truncate的区别

  • 相同点：都能删除数据，都不会删除表结构
  • 不同：
    • truncate 重新设置自增列 计数器会归零 不影响自增
    • truncate 不会影响事务


  • 1

delete删之前：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6SXdKLSk-1675761729952)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221022164854812.png)]

删之后：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SY3yKSCF-1675761729953)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221022165024268.png)]

自动增量没有改变，重新插入数据之后，自动增量会接着之前的继续写

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tKQa4Whn-1675761729953)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221022165141553.png)]

如果使用truncate会重置自增，则表id就会重新从1开始自增

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Gx36G24O-1675761729953)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221022170138845.png)]

delete存在的问题：（8.0已修复）

  • innoDB 引擎 数据库重启后，自增列会从1开始，因为存在内存中的，断电即失
  • MyISAM 引擎 数据库重启后，不会出现这个问题 ，是因为数据是存在文件中的不会丢失，会从上一个自增量开始。

4、DQL 查询语言（重点）

4,1、DQL

（Date Query Language：数据库查询语言）

  • 所有的查询都需要 Select
  • 见到那的查询，复杂的查询都可以做
  • 数据库中最核心的语言，最重要的语句
  • 使用频率最高的语言

4.2、指定查询字段

-- 查询全部学生   select  `字段`   from  表;
SELECT * from student;

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

-- 别名，给结果起一个名字 ，as  可以给字段起别名，也可以给表起别名

SELECT `studentno` as 学号 ,`studentname` as 学生姓名 FROM student;

-- 函数  concat(a,b) 拼接
SELECT CONCAT('姓名:',studentname) as 新名字 from student;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

as可以省略

语法： select 字段1，… from 表

有时候，列名不是见名知义 可以使用as 起别名

select 字段 as 别名 from 表

去重 distinct

-- 查询一下有哪些同学参加考试（有成绩）

-- 查询全部的考试成绩
select * from result;
-- 查询有那些学生参加了考试
SELECT `studentno` FROM result;

-- 发现重复数据 去重
SELECT DISTINCT `studentno` FROM result;


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

作用：去除select查询出来的结果出现的重复的数据，只会显示一条。

数据库的列 (表达式)

-- 查看版本   （函数）
SELECT VERSION();

-- 计算  （表达式）
SELECT 1+100 as `sum`;


-- 查询自增的步长  （变量）
SELECT @@auto_increment_increment;

-- 学生考试成绩+1分 查看
SELECT `studentresult`+1 as new_result FROM result; 

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

总结：select 后面的不一定要跟字段 也可以跟表达式：包括：文本值，列，NULL，函数，计算表达式，系统变量…

4.3、where条件子句

作用：检索数据库中符合条件的内容。

搜索的条件是由一个或多个条件组成，返回结果时布尔值

逻辑运算符

运算符语法描述
and &&a and b a&& b逻辑与
or ||a or b a||b逻辑或
Not !Not a !a逻辑非
-- 查询成绩在95 ~100
SELECT * from result 
WHERE studentresult>=95 and studentresult<=100;

-- 模糊查询  between  and

SELECT `studentno`,`studentresult` from result 
WHERE studentresult BETWEEN 95 and 100;

-- 除了1000号学生之外的成绩
SELECT `studentno`,`studentresult` from result
WHERE NOT `studentno`=1000;
-- !=
SELECT `studentno`,`studentresult` from result
WHERE `studentno`!=1000;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

模糊查询：比较运算符

运算符语法描述
IS NULLa is null如果操作符为null，则结果为真
IS NOT NULLa is not null如果操作符为not null，结果为真
BETWEENa between b and c若a在b和c之间 则结果为真
Likea like bsql匹配 a匹配b 则结果为真
INa in b

-- 查询姓张的同学   liek  结合  %(多个字符)  _（一个字符）
  SELECT studentname from student 
	WHERE studentname like '张%';


-- 查询姓张的同学 名字只有俩字的
SELECT studentname from student 
	WHERE studentname like '张_';


-- 查询姓张的同学 名字只有三字的

SELECT studentname from student 
	WHERE studentname like '张__';

-- 查询姓张的同学 名字中间有佳字的


SELECT studentname from student 
	WHERE studentname like '_佳_';

SELECT studentname from student 
	WHERE studentname like '%佳%';

-- in  

-- 查询 1001 1002 1003号学生
  SELECT `studentno`,studentname from student
	WHERE studentno IN (1001,1002,1003);
	
	-- 查询地址在西安的同学

SELECT `studentno`,studentname from student
WHERE address in ('西安','铜川','杭州');


-- null  not null
-- 查询学生地址为空的或者''的
SELECT `studentno`,studentname from student
WHERE address='' or address is null;

-- 查询有出生日期的同学
SELECT studentname,borndate from student
WHERE borndate is not null;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45

注意：

% 和 _ 只在like生效 in（）里面要写确定的值

4.4、联表查询 Join


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

/*
思路：
1、需求分析，分析查询的字段来自哪些表，是否需要联表查询
2、确定使用哪种联表查询  7种
判断的条件  （连接两个表的条件）：student.studentno = result.studentno
*/

SELECT s.studentno,studentname,subjectno,studentresult
from student as s
INNER JOIN result as r
WHERE s.studentno=r.studentno;

-- right join 

SELECT s.studentno,studentname,subjectno,studentresult
from student as s
RIGHT JOIN result as 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;


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
操作描述
inner join如果表中有一个匹配的值，就返回行
left join从左表中返回值 ，即使右表没有匹配对应的值
right join从右表中返回值 ，即使左表没有匹配对应的值

join （连接的表） on （连接的条件）

join on 叫做连接查询

where 叫等值查询

  • left join(左连接)：返回包括左表中的所有记录和右表中连接字段相等的记录。
  • right join(右连接)：返回包括右表中的所有记录和左表中连接字段相等的记录。
  • inner join(等值连接/内连接)：只返回两个表中连接字段相等的行。
-- 查询缺考的同学
SELECT s.studentno,studentname,subjectno,studentresult
from student as s
LEFT JOIN result as r
ON s.studentno=r.studentno
WHERE studentresult is null;


-- 练习  ：查询参加了考试的学生信息（姓名，学号，科目名，分数）
SELECT s.studentno,studentname,subjectname,studentresult 
from student as s
RIGHT JOIN result as r
on s.studentno=r.studentno
INNER JOIN `subject`as su
on su.subjectno = r.subjectno;
 
-- 要查询哪几个数据，从哪几个表中查，怎么连接 from 表  xxx join on 条件
-- 假设存在多张表查询，慢慢查 先连接两个表 查询的结果继续连接   一步步写语句

-- from a LEFT JOIN b   以a表为基准
-- from a RIGHT JOIN b  以b表为基准


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22

自连接

自连接：就是将一个表拆分成两个表在进行连接，这个表的两个列参数是有一定的关系的，一个是一个的父级 通过他们的关系将他们连接在一起


-- 自连接
SELECT a.`categoryname` as '父栏目',b.`categoryname` as '子栏目'
FROM `category` as a, `category` as b
where a.categoryid = b.pid;

  • 1
  • 2
  • 3
  • 4
  • 5

练习巩固：

-- 查询学员所属的年级  （学生姓名，学号，年级名称）
SELECT `studentname`,`studentno`,`gradename`
from student s
INNER JOIN `grade` g
on s.gradeid=g.gradeid;


-- 查询科目所属的年级 （科目名称，年纪名称）
SELECT `subjectname`,`gradename`
FROM `subject` sub
INNER JOIN grade g
on g.gradeid=sub.gradeid;


-- 练习  ：查询参加了 数据库结构-1 的学生信息（姓名，学号，科目名）

SELECT `studentname`,s.`studentno`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
on s.studentno=r.studentno
INNER JOIN `subject` sub
on sub.subjectno = r.studentno
WHERE subjectname='数据库结构-1';


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24

4.5、分页和排序

排序：


-- ==========分页  limit和排序   order by===============
-- 排序：升序：asc  降序：desc
-- order by 通过哪个字段排，怎么排
-- 查询结果根据成绩排序
-- 升序
SELECT `studentname`,s.`studentno`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
on s.studentno=r.studentno
INNER JOIN `subject` sub
ORDER BY studentresult asc;

降序
SELECT `studentname`,s.`studentno`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
on s.studentno=r.studentno
INNER JOIN `subject` sub
ORDER BY studentresult desc;


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21

分页

-- 分页 每页只显示五条数据
-- 语法  limit 起始值页，页面的大小
-- LIMIT 0,5    1~5
SELECT `studentname`,s.`studentno`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
on s.studentno=r.studentno
INNER JOIN `subject` sub
ORDER BY studentresult asc
LIMIT 0,5

-- 第一页 limit 0，5
-- 第二页  limit 5，5
-- 第三页  limit 10，5
-- 第n页  limit (n-1)*5,5      (n-1)*pagesize ,pagesize(页面大小)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

语法: limit (n-1)*pagesize,pagesize pagesize代表页面大小 n代表页数 第一个参数是起始值，第二个数是页面大小。

练习：

-- 查询  高等数学-2 课程成绩排名前十的学生，并且分数大于80分的学生信息

SELECT `studentname`,`studentresult`,`subjectname`
FROM student s
INNER JOIN result r
on s.studentno=r.studentno
INNER JOIN `subject` sub
on r.subjectno=sub.subjectno
WHERE subjectname='高等数学-2' and studentresult>80
ORDER BY studentresult desc
LIMIT 0,10;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

4.6、子查询

where(这个值是计算出来的)

本质在where语句中嵌套一个子查询语句


-- where
-- 查询   高等数学-2 的所有考试结果（学号，科目表好，成绩），降序排序

-- 方式一使用连接查询
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM result r
INNER JOIN `subject` sub
on r.subjectno=sub.subjectno
where subjectname='高等数学-2'
ORDER BY studentresult desc
-- 方式二：使用子查询  (由里即外)
-- 思路 我们可以查出来在subject表中科目是'高等数学-2'的所有学生学号，在针对这些学号查询对应的信息
SELECT `studentno`,`subjectno`,`studentresult`
FROM result
WHERE subjectno=(
SELECT subjectno 
FROM `subject`
WHERE `subjectname`='高等数学-2'
	
);



sql
-- 查询课程为 高等数学-2 且分数不小于80的同学的学号和姓名
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
on s.studentno=r.studentno
INNER JOIN `subject` sub
on sub.subjectno=r.subjectno
WHERE subjectname='高等数学-2' and studentresult>=80;


-- 查询分数不小于80分的学生姓名和学号

SELECT DISTINCT s.`studentno`,`studentname`
FROM	student s
INNER JOIN result r
on r.subjectno=s.studentno
WHERE studentresult>=80;

-- 在这个基础上增加一个科目，高等数学-2
-- 查询 高等数学-2的编号
SELECT DISTINCT s.`studentno`,`studentname`
FROM	student s
INNER JOIN result r
on r.subjectno=s.studentno
WHERE studentresult>=80 and subjectno=(
SELECT `subjectno`
FROM `subject` sub
where subjectname='高等数学-2'
);

-- 在改造
SELECT studentno,studentname FROM student
WHERE studentno IN (
SELECT studentno FROM result WHERE studentresult>=80 AND subjectno=(
SELECT subjectno FROM `subject` WHERE subjectname='高等数学-2'
)
);


-- 练习  查询 C语言-1前五名同学的成绩的信息  （学号，姓名，成绩）

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65

5、MySQL函数

5.1、常用函数

-- ========常用函数========
SELECT ABS(-6) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.3) -- 向下取整
SELECT RAND() -- 返回一个0~1的随机数
SELECT SIGN(10) -- 判断正负  0--0  负数返回-1 正数返回1


-- 字符串
SELECT CHAR_LENGTH('喜喜阿萨大大飒飒的') -- 字符串长度
SELECT CONCAT('我','哎','你')   -- 拼接字符串
SELECT INSERT('老子爱编程',1,2,'俺') -- 替换字符串  从1开始，不吃从零开始
SELECT LOWER('shenssSSSS') -- 变小写
SELECT UPPER('sdasdasd')  -- 变大写
SELECT REPLACE('你说你想在海边买一所房子','海边','花果山')  -- 替换
SELECT SUBSTR('你问他是黑色的吗',4,6) -- 截取字符串，二三个参数代表从第几个开始包括这个（从1开始），截几个字
-- 查询性周的同学  改姓为邹
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()   -- 版本


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40

4.7、分组和过滤


-- 查询不同课程的平均分，最高分，最低分，平均分大于80
-- 核心分组
SELECT subjectname,avg(studentresult),max(studentresult),min(studentresult)
FROM result r
INNER JOIN `subject` sub
on sub.subjectno = r.subjectno
GROUP BY r.subjectno -- 通过什么字段来分组
HAVING avg(studentresult)>80

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

5.2、聚合函数

函数名称描述
COUNT（）计数
SUM（）求和
AVG（）平均值
MAX（）最大值
MIN（）最小值
……
-- ============聚合函数=========
-- 都能够统计表中的数据（想查询一个表中有多少行可是使用count（））
SELECT COUNT(borndate) FROM student -- count（字段）会忽略所有的null值
SELECT COUNT(*) FROM student -- count（*） 不会忽略null值 本质 计算行数
SELECT COUNT(1) FROM student -- count（1） 不会忽略null值 本质 计算行数

SELECT SUM(studentresult) as 总和 FROM result;
SELECT avg(studentresult) as 平均数 FROM result 
SELECT max(studentresult) as 最大值 FROM result
SELECT min(studentresult) as 最小值 FROM result


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

5.3、数据库级别的MD5加密

什么时MD5

是一种不可逆的加密算法 具体的MD5值是一样的

MD5破解网站的原理，有字典暴力破解，一个个匹配，进行加密后在进行匹配

-- ============测试md5加密===========

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

-- 明文密码
INSERT into testmd5 VALUES(1,'张三','123456'),(2,'李四','123456'),(3,'王五','123456'),(4,'张麻子','123456'),(5,'陈老六','123456');

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

UPDATE testmd5 set pwd=MD5(pwd);   -- 加密全部pwd

-- 插入数据时加密

INSERT into testmd5 VALUES(6,'小黄',MD5('123456'))

-- 如何校验：将用户传进来的密码进行MD5加密然后在进行匹配
SELECT * FROM testmd5 WHERE `name`='张麻子' AND pwd=MD5('123456')

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23

6、事务

6.1、什么是事务

要么都成功，要么都失败

例子： 转账：

SQL1：A给B转账 起始：（A：1000 B：200） 执行：A—>B 200

SQL2：B接收A的转账 结束：（A ：800 B：400）

以上两条SQL语句必须同时执行，要不然就会发生错误 导致A的钱减少了 B的钱没增加

原理：将一组SQL放在同一个批次去执行

概念：在数据库系统中，一个事务是指：由一系列数据库操作组成的一个完整的逻辑过程。例如银行转帐，从原账户扣除金额，以及向目标账户添加金额，这两个数据库操作的总和，构成一个完整的逻辑过程，不可拆分。这个过程被称为一个事务，具有ACID特性。

事务原则:ACID原则：原子性，一致性，隔离性，持久性 （脏读，幻读…）

  • Atomicity（原子性）：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
  • Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
  • Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
  • Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

解读：

  • 原子性：事务的发生是同时进行的，不会一方先执行，如果有错误，会恢复之前的状态
  • 一致性：最终一致性，事务结束后总的数据是不变的，比如银行的钱，两个人加起来总数不会发生改变
  • 隔离性:多个用户并发访问数据库时，数据库为每个用户开启的事务，不会被其他的事务所干扰，事物之间要保持隔离
  • 持久性：如果事务未提交的话，发生宕机或断电情况会恢复到最开始的状态（恢复原状），但是如果提交了，发生宕机或断电情况会保存之前的状态（持久化到数据库）。

隔离导致的问题：

  • 脏读：一个事务读取了另一个事务未提交的数据。
  • 不可重复读：在一个事务内多次读取的数据结果不同（不一定是错误）
  • 幻读（虚读）：一个事务内读取到了别的事务插入的数据，导致前后读取的结果不同

执行事务

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

-- 手动处理事务

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

-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的sql都在同一个事务内



insert xx
INSERT xx
-- 提交：持久化存储（成功）
commit

-- 回滚：回到原来的样子（失败）
ROLLBACK


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



-- 了解
-- 设置事务的保存点
SAVEPOINT 保存点名 -- 设置一个事务的保存

ROLLBACK TO SAVEPOINT 保存点名   -- 回滚到保存点
RELEASE SAVEPOINT 保存点名  -- 撤销保存点


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35

转账例子：


-- 转账

CREATE DATABASE `shop` CHARACTER SET utf8 COLLATE utf8_general_ci;

use shop;

CREATE TABLE `account`(
	`id` INT(4) 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  -- 开启自动提交

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31

7、索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

7.1、索引的分类

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

  • 主键索引 （PRIMARY KEY）
    • 唯一的标识，主键不可重复，只能有一个列作为主键
  • 唯一索引 （UNIQUE KEY）
    • 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
  • 常规索引 （KEY/INDEX）
    • 默认的，index关键字或者key关键字设置
  • 全文索引 （FULL TEXT）
    • 在特定的数据库引擎下才有，MYISAM
    • 快速定位数据

7.2、测试索引

-- 插入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`,`eamil`,`phone`,`gender`)VALUES(CONCAT('用户',i),'19224305@qq.com','123456789',FLOOR(RAND()*2));
		SET i=i+1;
	END WHILE;
	RETURN i;
END;

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




DELIMITER $$ -- 写函数之前必须要写，标志
CREATE FUNCTION mock_data()
RETURNS INT
DETERMINISTIC
BEGIN
		DECLARE num INT DEFAULT 1000000;
		DECLARE i INT DEFAULT 0;
		
		WHILE i<num DO
		-- 插入语句
			INSERT into app_user(`name`,`eamil`,`phone`,`gender`,`password`,`age`)
			VALUES(CONCAT('用户',i),'332211456@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;



INSERT into app_user(`name`,`eamil`,`phone`,`gender`,`password`,`age`)
VALUES(CONCAT('用户',1),'332211456@qq.com',
CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),
FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100)
);



SELECT mock_data(); -- 执行函数 创建一百万条数据


 
SELECT * FROM app_user WHERE `name`='用户999999';   -- > 时间: 0.499s

SELECT * FROM app_user WHERE `name`='用户999999';  --  0.505s

SELECT * FROM app_user WHERE `name`='用户999999';  -- > 时间: 0.501s

EXPLAIN SELECT * FROM app_user WHERE `name`='用户999999';
-- 创建索引 
-- CREATE INDEX 索引名  on 表(字段)
CREATE INDEX id_app_user_name on app_user(`name`);  
-- 创建索引之后查询速度明显上升了

SELECT * FROM app_user WHERE `name`='用户999999';  -- > 时间: 0s

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


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68

添加索引之后，速度明显提升，内部原因是因为查找的时候并不是一条条查找了，添加索引之后，索引给每条数据都添加了一个特殊的标记，只是我们看不到，查找的时候，会直接匹配到标记，一次就能查询出来。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ClxQnrwb-1675761729954)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221026162652964.png)]

索引在小数据量的时候，影响不大，但是在大数据的时候，区别十分明显。

7.3、索引原则

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

Hash类型的索引

Btree：InnoDB的默认数据结构

8、权限管理和备份

8.1、用户管理

可视化工具操作

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NkkkQyx7-1675761729954)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221026164638023.png)]

SQL命令操作

用户表：mysql.user

本质：对这个表进行操作

-- 创建用户   CREATE USER 用户名 identified by '密码';
CREATE USER zhaozhao identified by '123456';

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

-- 修改密码（修改指定用户密码）
SET PASSWORD FOR zhozhao = PASSWORD('123456');

-- 重命名   RENAME USER 旧名字 to 新名字;
RENAME USER zhaozhao to zhaozhao1;

-- 用户授权  ALL PRIVILEGES  全部的权限 on *.*代表所有数据库所有表   库.表
-- ALL PRIVILEGES 除了给别人授权的权限没有，剩下都有
GRANT ALL PRIVILEGES on *.*;

-- 查询权限
SHOW GRANTS for zhaozhao; -- 查看指定权限
SHOW GRANTS FOR root@localhost; -- 查看root主机权限



-- 撤销权限 REVOKE 什么权限  on 库.表 from 用户名
REVOKE ALL PRIVILEGES on *.* FROM zhaozhao;

-- 删除用户
DROP USER zhaozhao;



  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29

8.2、MySQL备份

为什么要备份

  • 保证数据库的数据不丢失
  • 数据转移

MySQL数据库备份的方式

  • 直接拷贝物理文件
  • 在可视化工具中手动导出 （Navicat）
    • 在表中右键选择转储文件，一般选择结构和数据导出。
    • [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3xoUy1eE-1675761729954)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221028143212712.png)]
  • 使用命令行导出， mysqldump 命令行
# 格式： mysqldump -h主机 -u用户名 -p密码 数据库 表名 > 物理磁盘/文件名

mysqldump -hlocalhost -uroot -p753951 school student >d:/a.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
#导多个表  mysqldump -h主机 -u用户名 -p密码 数据库 表名1 表名2 > 物理磁盘/文件名
#到数据库 mysqldump -h主机 -u用户名 -p密码 数据库 > 物理磁盘/文件名

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

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13

要备份数据库，以防数据丢失。

sql文件给别人即可。

9、规范数据库设计

9.1、为什么需要设计数据库

当数据库比较复杂是，我们需要设计数据库

糟糕的数据库的设计：

  • 数据冗余，浪费空间
  • 数据插入和删除都会麻烦，异常（频闭使用物理外键）
  • 程序的性能查

良好的数据库设计：

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

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

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

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

  • 收集信息，分析需求
    • 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
    • 分类表：（文章分类，创作者）
    • 文章表：（文章的信息：标题，内容。。。）
    • 评论表（关于评论的人和回复的人，以及在哪个文章评论的）
    • 友链表（href属性，一个链接）
    • 自定义表（系统信息，某个关键字，或者主段字，不想重复写，使用 key ：value的形式存在）
    • 说说表：（发表心情：id content create_time。。。）
  • 标识实体（把需求落实到每个字段）
  • 标识实体之间的关系
    • 写博客的表：user–>blog
    • 创建分类：user -->category
    • 关注： user-user
    • 友链：links
    • 评论：user–user—blog

9.2、三大范式

为什么需要数据范式化

  • 信息重复
  • 更新异常
  • 插入异常
    • 无法正常显示信息，可能是几个表插入信息了，有几个表没插入
  • 删除异常
    • 丢失有效的信息

三大范式

第一范式（1NF）

原子性：保证每一列都不可再分，小心产生歧义

第二范式（2NF）

前提：满足第一范式

每张表只描述一件事情

第三范式（3NF）

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

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

（规范数据库的设计）

规范性和性能的问题

关联查询的表不能超过三张表（阿里规范）

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

10、JDBC

10.1、数据库驱动

驱动：声卡，显卡…

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gNS2FYkp-1675761729955)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221101083744895.png)]

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

10.2、JDBC

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

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

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hKO4vxdU-1675761729955)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221101084210328.png)]

java.sql

javax.sql

还需要导入数据库驱动包 mysql connector

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
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

1、创建一个普通项目

2、导入数据库驱动

  • 创建一个lib目录，将jar包放入该目录中
  • 右键导入的jar包，选择add as library

3、编写测试代码

package com.zhaozhao.lesson01;

import java.sql.*;

/***
 * 我的第一个JDBC代码
 */
public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 加载驱动
        Class.forName("com.mysql.jdbc.Driver");//固定写法，加载驱动
        //用户信息和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 = "753951";

        //3.连接成功，创建一个数据库对象   connection 代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);


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

        //5.执行sql的对象去执行sql，可能存在结果，查看返回结果
        String sql = "SELECT * FROM users;";

        ResultSet resultSet = statement.executeQuery(sql); //  resultSet  返回结果集

        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("birthday:" + resultSet.getObject("birthday"));
        }

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

    }
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45

步骤总结：

1、加载驱动

2、连接数据库 DriverManager

3、获得执行sql的对象 Statement

4、获得返回的结果集

5、释放连接

10.4、statement对象

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

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

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

CRUD操作

create

是以哦那个executeUpdate（String sql）方法完成添加数据，

示例：

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

delete

使用executeUpdate（String sql）方法完成数据删除操作，示例：

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

update

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

示例：

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

read

使用executeQuery（String sql）方法完成数据查询操作，示例操作：

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

1、写一个工具类

package com.zhaozhao.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 | 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 throwables) {
                throwables.printStackTrace();
            }
        }
        if (st!=null){
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (conn!=null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }


}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66

2、增删改：

增：

package com.zhaozhao.lesson02;

import com.zhaozhao.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();//获得执行SQL的对象
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,email,birthday)VALUES(5,'小冰','6865573','123123@qq.com','2002-01-05');";
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("插入成功！！！");
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(conn,st,rs);

        }
    }
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33

删：

package com.zhaozhao.lesson02;

import com.zhaozhao.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 throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(conn,st,rs);

        }
    }
    }



  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34

改：

package com.zhaozhao.lesson02;

import com.zhaozhao.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`='昭昭',email='33214567@qq.com' WHERE id=1;";
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("更新成功！！！");
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(conn,st,rs);

        }
    }
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33

3、查询

package com.zhaozhao.lesson02;

import com.zhaozhao.lesson02.utils.jdbcUtils;

import javax.swing.event.TreeWillExpandListener;
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;";
            rs = st.executeQuery(sql);//查询完毕会返回一个结果集
            //遍历并输出查询结果
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(conn,st,rs);
        }
    }
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36

4、SQL注入

package com.zhaozhao.lesson02;

import com.zhaozhao.lesson02.utils.jdbcUtils;

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("昭昭","123456");
        login("'or '1=1","' or '1=1");

    }
    public static void login(String username,String password){
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = jdbcUtils.getConnection();
            st = conn.createStatement();

            //sql
            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 throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(conn,st,rs);
        }
    }
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OegwskG1-1675761729955)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221102174905456.png)]

10.5、PreparedStatement对象

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

1、新增

package com.zhaozhao.lesson03;

import com.zhaozhao.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 pst = null;

        try {
            conn = jdbcUtils.getConnection();

            //与Statement区别
            //使用？代替参数，占位符
            String sql = "insert into users(id,`NAME`,`PASSWORD`,email,birthday) values(?,?,?,?,?)";

            pst = conn.prepareStatement(sql);//预编译sql，先写sql，不执行

            //手动给参数赋值
            //set+参数类型（）方法 有两个参数，第一个是下标（下标是从1开始的），第二个是值
            pst.setInt(1,4);
            pst.setString(2,"喜喜");
            pst.setString(3,"123456");
            pst.setString(4,"13565357@qq.com");
            //data java.sql.date()是数据库的时间
            //  java.util   Date().getTime()是java的时间戳
            pst.setDate(5,new java.sql.Date(new Date().getTime()));


            //执行
            int i = pst.executeUpdate();
            if (i>0){
                System.out.println("插入成功！！");


            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(conn,pst,null);
        }

    }
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52

2、删除

package com.zhaozhao.lesson03;

import com.zhaozhao.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 pst = null;

        try {
            conn = jdbcUtils.getConnection();

            //与Statement区别
            //使用？代替参数，占位符
            String sql = "delete from users where id=?";

            pst = conn.prepareStatement(sql);//预编译sql，先写sql，不执行

            //手动给参数赋值
            //set+参数类型（）方法 有两个参数，第一个是下标（下标是从1开始的），第二个是值
            pst.setInt(1,4);
            //执行
            int i = pst.executeUpdate();
            if (i>0){
                System.out.println("删除成功！！");


            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(conn,pst,null);
        }
    }
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44

3、更新

package com.zhaozhao.lesson03;

import com.zhaozhao.lesson02.utils.jdbcUtils;

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

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

        try {
            conn = jdbcUtils.getConnection();

            //与Statement区别
            //使用？代替参数，占位符
            String sql = "update users set `NAME` = ? where id=?;";

            pst = conn.prepareStatement(sql);//预编译sql，先写sql，不执行

            //手动给参数赋值
            //set+参数类型（）方法 有两个参数，第一个是下标（下标是从1开始的），第二个是值
            pst.setString(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,null);
        }
    }
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47

4、查询

package com.zhaozhao.lesson03;

import com.zhaozhao.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 pst = null;
        ResultSet rs = null;
        try {
            conn = jdbcUtils.getConnection();
            String sql = "select * from users where id=?";
            //编译
            pst = conn.prepareStatement(sql);
            pst.setInt(1,1);
            //执行
            rs = pst.executeQuery();
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
            }




        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(conn,pst,rs);
        }

    }
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38

5、防止sql注入

package com.zhaozhao.lesson03;

import com.zhaozhao.lesson02.utils.jdbcUtils;

import java.sql.*;

public class SQL注入 {
    public static void main(String[] args) {
        //login("昭昭","123456");
        login("'' or 1=1","123456");

    }
    public static void login(String username,String password){
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = jdbcUtils.getConnection();
            //PreparedStatement防止sql注入的本质：把传递进来的参数当作字符
            //假设其中存在转义字符，就直接忽略了，比如‘’会被直接转义

            //sql
            String sql = "select * from users where `NAME`=? AND `password`=?";
            st = conn.prepareStatement(sql);
            st.setString(1,username);
            st.setString(2,password);

            rs = st.executeQuery();
            //遍历并输出查询结果
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("password"));
                System.out.println("=================");
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils.release(conn,st,rs);
        }
    }
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44

10.6、使用IDEA连接数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rSr6PPvC-1675761729956)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221104142012800.png)]

写sql的地方

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-K1MCZoO3-1675761729956)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221104144415558.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UpU7Tmxt-1675761729956)(C:\Users\昭昭\AppData\Roaming\Typora\typora-user-images\image-20221104144736023.png)]

10.7事务

要么都成功要么都失败，几件事情是绑定在一起的

ACID原则

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

一致性：总数不变

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

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

隔离性的问题：

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

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

幻读：在一个事务内，读取到了别人插入的数据，导致前后读取得不一样。

代码实现

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

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

3、可以在catch语句中显示的定义回滚语句，但是默认失败就会回滚

package com.zhaozhao.lesson04;

import com.zhaozhao.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 throwables) {
            throwables.printStackTrace();
            try {
                conn.rollback();//如果失败就回滚
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }finally {
            jdbcUtils.release(conn,st,rs);
        }
    }
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48

10.8、数据库连接池

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

连接----释放是十分浪费资源的

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

最小连接数：10

最大连接数：15

超过15会等待，等待超时：100ms

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

实现类：

开源数据源

DBCP

C3P0

Druid：阿里

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

bdcp

需要的jar包

commons-dbcp-1.4.jar

commons-pool-1.6.jar

测试代码：

package com.zhaozhao.lesson05;


import com.zhaozhao.lesson05.utils.jdbcUtils_dbcp;

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

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

        try {
            conn = jdbcUtils_dbcp.getConnection();

            //与Statement区别
            //使用？代替参数，占位符
            String sql = "insert into users(id,`NAME`,`PASSWORD`,email,birthday) values(?,?,?,?,?)";

            pst = conn.prepareStatement(sql);//预编译sql，先写sql，不执行

            //手动给参数赋值
            //set+参数类型（）方法 有两个参数，第一个是下标（下标是从1开始的），第二个是值
            pst.setInt(1,4);
            pst.setString(2,"喜喜");
            pst.setString(3,"123456");
            pst.setString(4,"13565357@qq.com");
            //data java.sql.date()是数据库的时间
            //  java.util   Date().getTime()是java的时间戳
            pst.setDate(5,new java.sql.Date(new Date().getTime()));


            //执行
            int i = pst.executeUpdate();
            if (i>0){
                System.out.println("插入成功！！");


            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils_dbcp.release(conn,pst,null);
        }
    }
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52

c3p0

需要的jar包：

mchange-commons-java-0.2.20.jar

c3p0-0.9.5.5.jar

测试代码：

package com.zhaozhao.lesson05;


import com.zhaozhao.lesson05.utils.jdbcUtils_c3p0;
import com.zhaozhao.lesson05.utils.jdbcUtils_dbcp;

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

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

        try {
            conn = jdbcUtils_c3p0.getConnection();

            //与Statement区别
            //使用？代替参数，占位符
            String sql = "insert into users(id,`NAME`,`PASSWORD`,email,birthday) values(?,?,?,?,?)";

            pst = conn.prepareStatement(sql);//预编译sql，先写sql，不执行

            //手动给参数赋值
            //set+参数类型（）方法 有两个参数，第一个是下标（下标是从1开始的），第二个是值
            pst.setInt(1,5);
            pst.setString(2,"xx");
            pst.setString(3,"123456");
            pst.setString(4,"13565357@qq.com");
            //data java.sql.date()是数据库的时间
            //  java.util   Date().getTime()是java的时间戳
            pst.setDate(5,new java.sql.Date(new Date().getTime()));


            //执行
            int i = pst.executeUpdate();
            if (i>0){
                System.out.println("插入成功！！");


            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils_c3p0.release(conn,pst,null);
        }
    }
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53

总结：

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

.jdbcUtils_dbcp;

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

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

    try {
        conn = jdbcUtils_dbcp.getConnection();

        //与Statement区别
        //使用？代替参数，占位符
        String sql = "insert into users(id,`NAME`,`PASSWORD`,email,birthday) values(?,?,?,?,?)";

        pst = conn.prepareStatement(sql);//预编译sql，先写sql，不执行

        //手动给参数赋值
        //set+参数类型（）方法 有两个参数，第一个是下标（下标是从1开始的），第二个是值
        pst.setInt(1,4);
        pst.setString(2,"喜喜");
        pst.setString(3,"123456");
        pst.setString(4,"13565357@qq.com");
        //data java.sql.date()是数据库的时间
        //  java.util   Date().getTime()是java的时间戳
        pst.setDate(5,new java.sql.Date(new Date().getTime()));


        //执行
        int i = pst.executeUpdate();
        if (i>0){
            System.out.println("插入成功！！");


        }


    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }finally {
        jdbcUtils_dbcp.release(conn,pst,null);
    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35

}




> c3p0

需要的jar包：

mchange-commons-java-0.2.20.jar

c3p0-0.9.5.5.jar

测试代码：

```java
package com.zhaozhao.lesson05;


import com.zhaozhao.lesson05.utils.jdbcUtils_c3p0;
import com.zhaozhao.lesson05.utils.jdbcUtils_dbcp;

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

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

        try {
            conn = jdbcUtils_c3p0.getConnection();

            //与Statement区别
            //使用？代替参数，占位符
            String sql = "insert into users(id,`NAME`,`PASSWORD`,email,birthday) values(?,?,?,?,?)";

            pst = conn.prepareStatement(sql);//预编译sql，先写sql，不执行

            //手动给参数赋值
            //set+参数类型（）方法 有两个参数，第一个是下标（下标是从1开始的），第二个是值
            pst.setInt(1,5);
            pst.setString(2,"xx");
            pst.setString(3,"123456");
            pst.setString(4,"13565357@qq.com");
            //data java.sql.date()是数据库的时间
            //  java.util   Date().getTime()是java的时间戳
            pst.setDate(5,new java.sql.Date(new Date().getTime()));


            //执行
            int i = pst.executeUpdate();
            if (i>0){
                System.out.println("插入成功！！");


            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            jdbcUtils_c3p0.release(conn,pst,null);
        }
    }
}


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67

总结：

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

druid

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/小舞很执着/article/detail/840809
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号