MySQL01

初识MySQL

链接数据库

命令行连接

#链接数据库
mysql -u root -p   631062568 
#创建数据库
create database westos; 

#所有的语句都要用；结尾

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

#切换数据库  use 数据库名
use student 

#显示某个数据库中所有的表的信息
describe student; 

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

exit -- 退出连接
# 也可以注释
-- 单行注释
/*
多行注释
*/

操作数据库

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

操作数据库

创建数据库

CREATE DATABASE if not exists `kuang_school`;

删除数据库

DROP DATABASE [IF EXISTS] westos;

使用数据库

-- tab 键上面，如果你的表名或者字段名是一个特殊字符，就需要带上``
use kuang_school   -- use database_name

查看数据库

SHOW DATABASES -- 查看所有的数据库

数据库的列类型

数值

tinyint 十分小的数据 1个字节
smallint 较小的数据 2个字节
mediumint 中等大小的数据 3个字节
int 标准整数 4个字节常用
bigint 较大的数据 8个字节
float 浮点数 4个字节
double 浮点数 8个字节 float 和 double 存在精度问题
decimal 字符串形式的浮点数有关钱, 使用decimal

字符串

char 固定大小的字符串 0-255
varchar 长度可变的字符串 0-65535 常用的变量对应Java的 String 类型
tinytext 微型文本 2^8 - 1
text 文本串 2^16 - 1 保存大文本

时间日期

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

null

没有值, 未知
不要使用NULL进行运算结果肯定为NULL

数据库的字段属性 --> 重点

字段 --> 列属性

Unsigned

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

zerofull

0填充
不足的位数使用0来填充 int(3), 5 --> 005

auto_increment

默认自动在上一条的记录的基础上 +　１
通常与主键一起使用
可以自定义设计主键自增的起始值和步长

NULL 和 NOT NULL

假设设置成 not null , 必须赋值
null 如果不填写数据, 默认为null

default

设置默认的值

comment

注释

拓展

一张表中一般要有的五个字段 --> 表示一个记录存在的意义

id 主键

version 乐观锁

is_delete 伪删除

gmt_create 创建时间

gmt_update 修改时间

创建数据库表

注意事项:

/*  创建表的注意点
    使用英文(), 表的名字 和 字段名 尽量用``括起来
    字符串类型  使用'' 括起来
    所有的语句后面加 , 最后一个不用加
    primary key 主键  一般一张表中只有一个唯一的主键
*/
CREATE TABLE IF NOT EXISTS `student` (
  `id` INT (4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR (20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `password` VARCHAR (20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHAR (2) NOT NULL DEFAULT '未知' COMMENT '性别',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  `address` VARCHAR (100) DEFAULT '未知' COMMENT '住址',
  `email` VARCHAR (50) DEFAULT '未知' COMMENT '邮箱',
  PRIMARY KEY (id)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

格式

CREATE TABLE [IF NOT EXISTS] `表名`(
    `字段名` 列类型 [属性] [索引] [注释],
    `字段名` 列类型 [属性] [索引] [注释],
    `字段名` 列类型 [属性] [索引] [注释],
    PRIMARY KEY (id)
    )[表类型] [字符集设置] [注释];

-- 查看创建数据库的语句
show create database `kuang_school`;

-- 查看创建表的语句
show create table `student`;

-- 显示表结构
describe `student`;

数据表的类型

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

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

常规使用操作:

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

在物理空间存在的位置

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

本质还是文件的储存

MYSQL引擎在物理文件上去区别

innoDB 在数据库表中只有一个*.frm文件, 以及上级目录下的 ibdata1 文件
MYISAM 对应的文件
- *.frm 表结构的定义文件
- *.MYD 数据文件 (data)
- *.MYI 放置索引文件 (index)

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

CHARSET = utf8;

不设置的话, 会是mysql默认的字符集编码 (不支持中文)

修改和删除表中字段

修改字段

-- 修改表名
alter table `old_table` rename as `new_table`;

-- 增加表的字段
alter table `table_name` add `table_name` int(3);

-- 修改表的字段  -->  字段重命名  修改约束
-- 重命名只用change change的功能多一点
alter table `table_name` change `field_oldname` `field_newname` int(3); -- 字段重命名
alter table `student` change `id` `studentId` varchar(11);  -- 字段约束可以修改

ALTER TABLE `teacher` CHANGE `name` `studentName` VARCHAR(11);
ALTER TABLE `teacher` CHANGE `studentName` `studentName` INT(11);

-- 只修改字段约束 使用modify
alter table `table_name` modify `field_name` int(3); -- 修改字段约束
alter table `student` modify `studentId` varchar(11);

删除字段

-- 删除表的字段
alter table if exists `table_name` drop `field_name`;
alter table  if exists `student` drop `studentId`;

创建和删除操作尽量加上存在判断

注意

所有的字段名表名使用飘包裹起来
注释使用 -- 空格
sql关键字大小写不敏感建议大家写小写

MYSQL数据管理

外键 --> 了解

方式一

CREATE TABLE `grade`(
	`gradeId` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年纪ID',
	`gradeName` VARCHAR(20) NOT NULL COMMENT '年级名称',
	PRIMARY KEY (`gradeId`)
)ENGINE = INNODB CHARSET = utf8;

-- 学生表的 gradeId字段  要去引用年级表的 gradeId
-- 定义外键
-- 给这个外键添加约束
CREATE TABLE `student` (
  `gradeId` INT(11) NOT NULL COMMENT '学生年纪',
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT '未知' COMMENT '性别',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  `address` VARCHAR(100) DEFAULT '未知' COMMENT '住址',
  `email` VARCHAR(50) DEFAULT '未知' COMMENT '邮箱',
  PRIMARY KEY (`id`),
  KEY `FK_gradeId` (`gradeId`),
  CONSTRAINT `FK_gradeId` FOREIGN KEY (`gradeId`) REFERENCES `grade`(`gradeId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

删除有外键关系的表先删除从表再删除主表

给创建过的表添加外键

CREATE TABLE `grade`(
	`gradeId` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年纪ID',
	`gradeName` VARCHAR(20) NOT NULL COMMENT '年级名称',
	PRIMARY KEY (`gradeId`)
)ENGINE = INNODB CHARSET = utf8;

-- 学生表的 gradeId字段  要去引用年级表的 gradeId
-- 定义外键
-- 给这个外键添加约束
CREATE TABLE `student` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT '未知' COMMENT '性别',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  `address` VARCHAR(100) DEFAULT '未知' COMMENT '住址',
  `email` VARCHAR(50) DEFAULT '未知' COMMENT '邮箱',
  PRIMARY KEY (`id`),
) ENGINE=INNODB DEFAULT CHARSET=utf8;	
	
-- 创建表的时候没有外键  后面添加外键  不建议在表创建过之后添加外键
-- alter table `表` add constraint
-- `约束名` foreign key(`想要作为外键的列`) references `哪个表`(`哪个字段`);

ALTER TABLE `student` ADD CONSTRAINT
`FK_gradeId` FOREIGN KEY(`gradeId`) REFERENCES `grade`(`gradeId`);

以上的操作都是物理外键 --> 数据库级别的外键, 我们不建议使用 (避免数据库过多造成困扰) 了解即可但是需要记住手敲

最佳实践

数据库就是单纯的表只用来存数据, 只有行是数据) 和列(是字段)
我们想使用多长表的数据, 想使用外键用程序去实现

DML语言 --> 背会

数据库意义: 数据存储数据管理

DML 语言: 数据操作语言

insert --> 添加
update --> 修改
delete --> 删除

添加

CREATE TABLE `grade`(
	`gradeId` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年纪ID',
	`gradeName` VARCHAR(20) NOT NULL COMMENT '年级名称',
	PRIMARY KEY (`gradeId`)
)ENGINE = INNODB CHARSET = utf8;

CREATE TABLE `student` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT '未知' COMMENT '性别',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  `address` VARCHAR(100) DEFAULT '未知' COMMENT '住址',
  `email` VARCHAR(50) DEFAULT '未知' COMMENT '邮箱',
  PRIMARY KEY (`id`),
) ENGINE=INNODB DEFAULT CHARSET=utf8;	

insert into `student`(`id`,`name`,`password`,`sex`,`birthday`,`address`,`email`) values
				   (1,'frank','123456','man','2024-05-18 11:16:41','China-Shanghai','123456@qq.com'),
				   (2,'tom','123456','man','2024-05-18 11:18:41','China-Shanghai','123456@qq.com')

insert into `student` values
						(1,'frank','123456','man','2024-05-18 11:16:41','China-Shanghai','123456@qq.com'),
						(2,'tom','123456','man','2024-05-18 11:18:41','China-Shanghai','123456@qq.com')

语法 --> insert into 表名(字段名1,字段名2,字段名3) values

(1,'name','address'),

(2,'name','address')

insert into 表名 values

(1,'name','address'),

(2,'name','address')

注意事项 -->

字段与字段之间使用逗号隔开
字段可以省略但是如果不写字段要默认一一对应
可以同时插入多条数据 values后面使用逗号将 () 隔开 --> values(),(),()

修改

-- 修改学员的名字
update `student` set `name` = '小刘' where `id` = 1;

-- 在不指定where条件下, 会改动所有的表数据
update `student` set `name` = '小刘';

-- 语法: 
update `table_name` set `field_name` = `value` where `field_name` = 1 ;

-- 修改多个字段信息  用逗号隔开
update `table_name` set `field_name` = value,`field_name` = value,`field_name` = value where `field_name` =1;

-- 通过多个where 条件定位某一个数据
update `table_name` set `field_name` = `value` where `field_name` = '' and `field_name` = '';
update `student` set `name` = `frank` where `name` = '小刘' and `sex` = '男';

操作符返回布尔值

操作符	含义	形式	结果
=	等于	id = 1	true
<> 或 !=	不等于	id <> 23	true
>
<
>=
<=
between...and...	在某个范围内	[2.5]
and	&&	id > 2 and id< 5	true
or	\|\|	id > 20 or id < 10	true

注意

field_name 是数据库的列字段，尽量带上 `` 标记

如果没有指定where筛选的条件，则会修改所有的列

value 可以是一个具体的值，也可以是一个变量
update `student` set `birthday` = current_time where `name` = '小刘' and `sex` = '男';
多个要修改的字段数据之间使用逗号隔开

删除

delete命令 -->

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

语法 --> delete from table_name where id = 1;

-- 删除表中全部数据
delete from `student`;

-- 删除指定数据
delete from `student` where `id` = 1;

truncate命令 -->

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

语法 --> truncate table table_name

truncate table `student`;

delete 和 truncate 区别

相同点：都能删除数据，都不会删除表结构
不同：

truncate 会重新设置自增列计数器会归零不会影响业务

delete 自增列计数器不会清零还是从原来删除前的数据开始计算 + 1

了解：使用delete 删除后的问题重启数据库后会出现的现象

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

DQL查询数据 --> 最重点

DQL Date Query Language 数据查询语言

所有的查询操作都用它 select
简单的查询，复杂的查询它都可以做
数据库最核心的语言。最重要的语句
使用频率最高的语句

Select完整语法 -->

SELECT语法

selet[选项] 
字段列表[字段别名] 
from 数据源
[left | right | inner join `table_name`]	-- 联合查询
[where条件字句]	-- 指定结果需要满足的条件
[group by 字句]	-- 指定结果按照哪几个字段来分组
[having 字句]		-- 过滤分组的记录必须满足的次要条件
[order by 字句]	-- 指定查询记录按一个或多个条件排序
[limit 字句]		-- 指定查询的记录从哪条至哪条

指定查询字段

-- 查询全部的学生 
-- select `field_name` from `table_name`;
select * from `student`;

-- 查询指定字段
select `studentID`,`studentName` from `student`;

-- 给查询出来的字段起一个新名字  as
select `studentID` as `学号`,`studentName` as `学生姓名` from `student`;

-- 拼接字符串函数  Concat(a,b)
-- 字段名默认变成  concat('姓名：',`studentName`)
select concat('姓名：',`studentName`) from `student`;
select concat('姓名：',`studentName`) as `新名字` from `student`;

在列字段的名字不是见名知意的情况下, 使用as 可以给列字段起别名

字段名 as 别名 表名 as 别名

去重 distinct 重要

作用: 去除select查询结果中重复的数据 , 重复数据只显示一条

select * from `score`;

-- 查询那些学生参加了考试
select `studentID` from `score`;

-- 发现重复数据 需要去重
select distinct `studentID` from `score`

数据库的列 (表达式)

-- 查询系统版本    (函数)
select version();

-- 计算	(表达式)
select 100*3 - 1 as `计算结果`;

-- 查询自增步长  (变量)
select @@auto_increment;

-- 查看  学员考试成绩 + 1  结果
select `studentID`,`studentScore` + 1 as `提分后` from `score`;

数据库中的表达式 --> 文本值列 NULL 函数计算表达式系统变量......

select 表达式 from 表

where条件子句

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

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

逻辑运算符

运算符	语法	描述
and &&	a and b a && b	逻辑与
or \|\|	a or b a \|\| b	逻辑或
not !	not a ! a	逻辑非

-- 查询考试成绩在  95 - 100 分之间的数据
select `studentID`,`studentScore` from `score`
where `studentScore` >= 95 and `studentScore` <= 100;

select `studentID`,`studentScore` from `score`
where `studentScore` >= 95 && `studentScore` <= 100;

-- 模糊查询  (区间)
select `studentID`,`studentScore` from `score`
where `studentScore` between 95 and 100;

-- 除了1000号学生之外的学生的成绩
select `studentID`,`studentScore` from `score`
where `studentID` != 1000;

select `studentID`,`studentScore` from `score`
where not `studentID` = 1000;

模糊查询 --> 比较运算符

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

-- like结合  % (代表0到任意个字符)    _  (代表一个字符)
-- 查询姓刘的同学
select `studentID`,`studentName` from `student`
where `studentName` like '刘%';

-- 查询姓刘的同学, 名字后面只有一个字的
select `studentID`,`studentName` from `student`
where `studentName` like '刘_';

-- 查询姓刘的同学, 名字后面有两个字的
select `studentID`,`studentName` from `student`
where `studentName` like '刘__';

-- 查询名字中间带  嘉  字的同学
select `studentID`,`studentName` from `student`
where `studentName` like '%嘉%;

-- in  (具体的一个或者多个值)  不能和like里面的 % _ 联用
-- 查询 1001, 1002, 1003 号学生

select `studentID`,`studentName` from `student`
where `studentName` in (1001,1002,1003)

-- 查询在北京的学生
select `studentID`,`studentName` from `student`
where `address` in ('北京')

select `studentID`,`studentName` from `student`
where `address` = '北京';

-- null   not null
-- 查询地址为空的学生  null
select `studentID`,`studentName` from `student`
where `address` = '';

select `studentID`,`studentName` from `student`
where `address` = '' or `address` is null;

-- 查询有出生日期的同学
select `studentID`,`studentName` from `student`
where `birthday` is not null;

-- 查询出生日期为空的同学
select `studentID`,`studentName` from `student`
where `birthday` is null;

联表查询

Join

七种Join -->

查询参加了考试的同学 (学号 , 姓名 , 科目编号 , 分数) 前两个是student表后两个是result表

思路 -->
分析需求 , 分析查询的字段来自那些表 (链接查询)

确定要使用哪种链接查询 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		查询
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`;
where 和 on 的区别

join (连接的表) on (判断的条件) 连接查询是一个具体的语法

where 等值查询

-- 左右表的确定
select `s.studentNO`,`studentName`,`SubjectNo`,`StudentResult`
from `student` as `s` left join `result` as `r`  -- 左表  xxxx join  右表
on `s.studentNO` = `r.studentNO`;

Join对比 -->

操作	描述
inner join	如果表中至少有一个匹配 , 就返回行 --> 那张表有这个字段就返回 , 重复的话加上表前缀
left join	返回左表中所有的值 , 即使右表中没有匹配
right 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

查询参加了考试的同学信息 (学号 , 学生姓名 , 科目名 , 分数) 前

思路 -->
分析需求 , 分析查询的字段来自那些表 student result subject (链接查询)

确定要使用哪种链接查询 7种链接查询

确定交叉点 (这两张表中哪个数据是相同的)
判断的条件 --> 学生表中的 studentNo = 成绩表中的 studentNo
select `s.studentNo`,`studentName`,`SubjectName`,`StudentResult`
from `student` as `s`
right join `result` as `r`
on `r.studentNo` = `s.studentNo`
inner join `subject` as `sub`
on `r.SubjectNo` = `sub.SubjectNo`

总结 -->

思路 : 先要明确要查询那些数据 select.....

从那几张表中查 from 表 xxxx join 连接的表 on 交叉条件

如果存在一种涉及多张表的查询, 慢慢来先查询两张表然后慢慢增加查询语句
from `table_a` left join `table_b`  -- 左连接以左边的表为主
from `table_a` right join `table_b` -- 右连接以右边的表为主

自连接 (了解)

自己的表和自己的表连接核心 --> 一张表拆成两张一样的表即可

父类

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

子类

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

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

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

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

-- 查询科目所属的年级 (科目名称 年级)
SELECT `SubjectName`,`GradeName`
FROM `subject` AS `sub`
INNER JOIN `grade` AS `g`
ON sub.`GradeID` = g.`GradeID`;		

-- 查询参加 高等数学1考试的同学的信息 学号 姓名 科目名 分数
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` AS `s`
INNER JOIN `result` AS `r`
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` AS `sub`
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE `subjectName` = '高等数学1';

分页和排序

排序

-- 排序  升序 ASC	降序 DESC
-- order by 通过哪个字段排序 怎么排
-- 查询的结果 根据成绩 降序排列
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` AS `s`
INNER JOIN `result` AS `r`
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` AS `sub`
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE `subjectName` = '高等数学1';
ORDER BY `StudentResult` ASC

分页

-- 为什么要分页
-- 缓解数据库压力  给人的体验更好  瀑布流  图片 抖音  资源可以无线往下刷

-- 分页  每页只显示条数据
-- 语法  limit 起始值 页面的大小
-- 网页应用  当前页  当前页大小  总页数
-- Limit 0,5	1~5
-- Limit 1,5	6~10

SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` AS `s`
INNER JOIN `result` AS `r`
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` AS `sub`
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE `subjectName` = '高等数学1'
ORDER BY `StudentResult` ASC
LIMIT 3,1

--			起始页
-- 第一页  Limit 0,5  	(1-1)*5
-- 第二页  Limit 5,5	(2-1)*5
-- 第三页  Limit 10,5	(3-1)*5
-- 第N页   Limit (n-1)*pageSize
-- pageSize 页面大小  (n-1)*pageSize 起始值  n 当前页
-- 总页数 = 数据总数/页面大小

语法 --> limit 查询起始值,pageSize

-- 思考
-- 查询  高等数学1 课程排名前三 并且分数大于80的学生的信息  (学号 姓名 课程名称 分数)
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` AS `s`
INNER JOIN `result` AS `r`
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` AS `sub`
ON sub.`SubjectNo` = r.`SubjectNo`
WHERE `SubjectName` = '高等数学1' AND `StudentResult` >= 80
ORDER BY `StudentResult` DESC
LIMIT 0,2

子查询

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

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

where (select * from )

-- 方式二  使用子查询(由里及外)
/*
查询高等数学1的 课程编号
SELECT `SubjectNo`
FROM `subject` 
WHERE `SubjectName` = '高等数学1'
*/

SELECT `StudentNo`,`SubjectNo`,`StudentResult`
FROM `result`
WHERE `SubjectNo` = (
	SELECT `SubjectNo` FROM `subject` 
	WHERE `SubjectName` = '高等数学1'
)
ORDER BY `StudentResult` DESC

-- 分数不小于80分的学生的学号和姓名
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM `student`  s
INNER JOIN `result` r
ON r.StudentNo = s.StudentNo
WHERE `StudentResult` >= 80

/*
查询 高等数学1的编号
SELECT `SubjectNo` FROM `subject`
WHERE `SubjectName` = '高等数学1'
*/
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` = '高等数学1'
)


-- 在这个基础上增加一个 高等数学科目
-- 查询课程为 高等数学1 且 分数不小于80的同学的 学号和姓名

SELECT s.`StudentNo`,`StudentName`
FROM `student` s
INNER JOIN `result` r
ON r.StudentNo = s.StudentNo
INNER JOIN `subject` AS sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE `SubjectName` = '高等数学1' 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` = '高等数学1'
	)
)

分组和过滤

-- 查询不同课程的平均分 最高分 最低分
-- 核心  根据不同的课程分组
SELECT 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 AVG(StudentResult) > 80;

MySQL函数

常用函数

-- ==============  常用函数  =========================

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

-- 字符串函数
SELECT CHAR_LENGTH('即使再小的帆也能远航')  -- 返回字符串的长度
SELECT CONCAT('小','刘')  -- 拼接字符串
SELECT INSERT('小刘学java',1,2,'苦逼')  -- 实现替换字符串功能
-- 从第一个字符开始 替换之后的两个字符[1,2]
SELECT LOWER('JERRY');  -- 转小写
SELECT UPPER('xiaoliu'); -- 转大写
SELECT INSTR('xiaoliu','o') -- 返回第一次出现的子串的索引
SELECT REPLACE('小刘坚持学习Java','坚持','努力') -- 替换出现的指定字符串
SELECT SUBSTR('小刘坚持学习Java',7,4) -- 截取字符串(源字符串,截取位置,截取长度)
-- 如果只写截取的开始位置 那么直接截取到最后
SELECT REVERSE('小刘坚持学习java中') -- 反转字符串

-- 查询姓frank的同学 姓名换成jims
SELECT REPLACE(studentName,'frank','jims') FROM student
WHERE studentName LIKE 'frank%'

-- 时间和日期函数 (记住)

-- 获取当前日期
SELECT CURRENT_DATE()
SELECT CURDATE()

-- 获取当前时间  YYYY-MM-DD HH:mm:ss
SELECT NOW();

-- 获取本地时间
SELECT LOCALTIME()

-- 返回系统时间
SELECT SYSDATE()

SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())

-- 系统
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()

聚合函数 --> 重要

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

-- ================ 聚合函数  =============
-- 都能够统计表中的数据  (查询一张表中由多少个记录)

SELECT COUNT(studentName) 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;

-- 查询不同课程的平均分 最高分 最低分
-- 核心  根据不同的课程分组
SELECT 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 AVG(StudentResult) > 80;

数据库级别的MD5加密

什么是MD5?

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

MD5不可逆具体的值在MD5中是一样的

MD5破解网站原理 --> 背后有一个字典根据MD5加密后的值查询到加密前的值


-- ===========  测试MD5 加密  ===============

CREATE TABLE `textMD5`(
	`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 `textMD5` VALUE(1,'frank','123456'),
			   (2,'mark','123456'),
			   (3,'jerry','123456')

-- 加密
UPDATE `textMD5` SET pwd = MD5(pwd) WHERE id = 1;
UPDATE `textMD5` SET pwd = MD5(pwd);	-- 加密全部的密码

-- 插入数据的时候加密
INSERT INTO `textMD5` VALUES(4,'xiaoming',MD5('123456'));

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

事务

什么是事务?

要么都成功要么都失败

1 SQL执行 A 给 B 转账

2 SQL执行 B 收到 A 的钱

A给B转账的时候服务器崩了 A转出去钱但是B没有收

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

事务原则 --> ACID原则原子性一致性隔离性持久性 (脏读幻读...)

原子性(Atomicity)

要么都成功要么都失败

一致性(Consistency)

事务前后的数据完整性要保证一致

持久性(Durability) -- 事务提交

事务一旦提交则不可逆被持久化到数据库中

隔离性(Isolation)

事务的隔离性是多个用户并发访问数据库时数据库为每一个用户单独开启是事务不能被其他事务的操作数据所干扰多个并发事务之间要相互隔离

隔离所导致的一些问题

脏读

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

不可重复读

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

虚读（幻读）

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

执行事务

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

-- 手动处理事务
SET autocommit = 1  -- 关闭自动提交

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

INSERT xx
INSERT xx

-- 提交: 持久化  (成功)
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',1000.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; -- 恢复默认值

索引

索引的分类

主键索引 (PRIMARY KEY)
- 主键不可重复只能有一个列作为主键
唯一索引 (UNIQUE KEY)
- 避免重复列出现唯一索引可以有多个
常规索引 (KEY/INDEX)
- 默认的 index key 关键字来设置
全文索引 (FullText)
- 在特定的数据库引擎下才有 MyISAM
- 可以快速定位数据

基础语法

-- 索引的使用
-- 1. 在创建表的时候给字段加上索引
-- 2. 创建完毕后增加索引

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

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

-- EXPLAIN 分析SQL执行的状况

EXPLAIN SELECT * FROM student; -- 非全文索引

SELECT * FROM student WHERE MATCH(studentName) AGAINST('frank');  -- 查询student表中studentName中与frank相匹配的行数据

EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('frank');

测试索引

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

-- 索引名 命名规则
-- id_表名_字段名
-- xx_表名_字段名

-- 第三种创建索引的方式
create index `xx_表名_字段名` on `表名`(`字段名`) 

-- 创建一个树  给所有数据添加一个索引  查找数据的时候  直接对应查抄某个索引即可

索引原则

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

索引的数据结构

Hash类型的索引

Btree --> Innodb默认数据结构

权限管理和备份

用户管理

SQL命令操作

/* 用户和权限管理 */ ------------------
用户信息表：mysql.user

-- 刷新权限
FLUSH PRIVILEGES

-- 增加用户 CREATE USER kuangshen IDENTIFIED BY '123456'
CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串)
  - 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。
  - 只能创建用户，不能赋予权限。
  - 用户名，注意引号：如 'user_name'@'192.168.1.1'
  - 密码也需引号，纯数字密码也要加引号
  - 要在纯文本中指定密码，需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的混编值，需包含关键字PASSWORD

-- 重命名用户 RENAME USER kuangshen TO kuangshen2
RENAME USER old_user TO new_user

-- 设置密码
SET PASSWORD = PASSWORD('密码')    -- 为当前用户设置密码
SET PASSWORD FOR 用户名 = PASSWORD('密码')    -- 为指定用户设置密码

-- 删除用户 DROP USER kuangshen2
DROP USER 用户名

-- 分配权限/添加用户
GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password']
  - all privileges 表示所有权限
  - *.* 表示所有库的所有表
  - 库名.表名 表示某库下面的某表

-- 查看权限   SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR 用户名
   -- 查看当前用户权限
  SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR CURRENT_USER();

-- 撤消权限
REVOKE 权限列表 ON 表名 FROM 用户名
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名    -- 撤销所有权限

权限解释

-- 权限列表
ALL [PRIVILEGES]    -- 设置除GRANT OPTION之外的所有简单权限
ALTER    -- 允许使用ALTER TABLE
ALTER ROUTINE    -- 更改或取消已存储的子程序
CREATE    -- 允许使用CREATE TABLE
CREATE ROUTINE    -- 创建已存储的子程序
CREATE TEMPORARY TABLES        -- 允许使用CREATE TEMPORARY TABLE
CREATE USER        -- 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW        -- 允许使用CREATE VIEW
DELETE    -- 允许使用DELETE
DROP    -- 允许使用DROP TABLE
EXECUTE        -- 允许用户运行已存储的子程序
FILE    -- 允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX     -- 允许使用CREATE INDEX和DROP INDEX
INSERT    -- 允许使用INSERT
LOCK TABLES        -- 允许对您拥有SELECT权限的表使用LOCK TABLES
PROCESS     -- 允许使用SHOW FULL PROCESSLIST
REFERENCES    -- 未被实施
RELOAD    -- 允许使用FLUSH
REPLICATION CLIENT    -- 允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE    -- 用于复制型从属服务器（从主服务器中读取二进制日志事件）
SELECT    -- 允许使用SELECT
SHOW DATABASES    -- 显示所有数据库
SHOW VIEW    -- 允许使用SHOW CREATE VIEW
SHUTDOWN    -- 允许使用mysqladmin shutdown
SUPER    -- 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。
UPDATE    -- 允许使用UPDATE
USAGE    -- “无权限”的同义词
GRANT OPTION    -- 允许授予权限


/* 表维护 */

-- 分析和存储表的关键字分布
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE 表名 ...
-- 检查一个或多个表是否有错误
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
-- 整理数据文件的碎片
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

MySQL备份

数据库备份必要性

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

MySQL数据库备份方法

mysqldump备份工具
数据库管理工具,如SQLyog
直接拷贝数据库文件和相关配置文件

mysqldump客户端

作用 :

转储数据库
搜集数据库进行备份
将数据转移到另一个SQL服务器,不一定是MySQL服务器

![962a72b9116137006f6773e7185779d5](C:\Users\qian\Documents\WeChat Files\wxid_5ovcg5vr4xnb22\FileStorage\Temp\962a72b9116137006f6773e7185779d5.png)

-- 导出
1. 导出一张表 -- mysqldump -uroot -p123456 school student >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
2. 导出多张表 -- mysqldump -uroot -p123456 school student result >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)
3. 导出所有表 -- mysqldump -uroot -p123456 school >D:/a.sql
　　mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
4. 导出一个库 -- mysqldump -uroot -p123456 -B school >D:/a.sql
　　mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql)

可以-w携带备份条件

-- 导入
1. 在登录mysql的情况下：-- source D:/a.sql
　　source 备份文件
2. 在不登录的情况下
　　mysql -u用户名 -p密码 库名 < 备份文件

规范数据库设计

为什么需要数据库设计

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

糟糕的数据库设计 :

数据冗余,存储空间浪费
数据更新和插入的异常
程序性能差

良好的数据库设计 :

节省数据的存储空间
能够保证数据的完整性
方便进行数据库应用系统的开发

软件项目开发周期中数据库设计 :

需求分析阶段: 分析客户的业务和数据处理需求
概要设计阶段:设计数据库的E-R模型图 , 确认需求信息的正确和完整.

设计数据库步骤

收集信息
与该系统有关人员进行交流 , 座谈 , 充分了解用户需求 , 理解数据库需要完成的任务.
标识实体[Entity]
标识数据库要管理的关键对象或实体,实体一般是名词
标识每个实体需要存储的详细信息[Attribute]
标识实体之间的关系[Relationship]

三大范式

问题 : 为什么需要数据规范化?

不合规范的表设计会导致的问题：

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

三大范式

第一范式 (1st NF)

第一范式的目标是确保每列的原子性,如果每列都是不可再分的最小数据单元,则满足第一范式

第二范式(2nd NF)

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。

第二范式要求每个表只描述一件事情

第三范式(3rd NF)

如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式.

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

规范性和性能的问题

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

考虑商业化的需求和目标 (成本用户体验) 数据库性能比规范化数据库更重要
在规范性能问题的时候需要适当考虑一下规范性
通过在给定的表中添加额外的字段 (增加冗余字段的方法) 大量减少需要从中搜索信息所需的时间 (从多表查询变成单表查询)
通过在给定的表中增加插入一些计算列,以方便查询 (从大数据量降低为小数据量的查询 --> 索引)

JDBC (重点)

数据库驱动

驱动 --> 声卡显卡数据库

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

JDBC

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

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

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

java.sql

javax.sql

还需要导入一个数据库驱动包 mysql-connector-java-5.1.47.jar

第一个JDBC程序

创建测试数据库

CREATE DATABASE `jdbc_study` CHARSET utf8 COLLATE utf8_general_ci;

USE `jdbc_study`;

CREATE TABLE users(
    `id` INT(11),
    `name` VARCHAR(30),
    `password` VARCHAR(40),
    `email` VARCHAR(60),
    `birthday` DATE,
    PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

INSERT INTO `users`(`id`,`name`,`password`,`email`,`birthday`)
VALUES(1,'frank','123456','frank@sina.com','2000-1-1'),
      (2,'jerry','123456','jerry@sina.com','2003-1-1'),
      (3,'sam','123456','sam@sina.com','2004-1-1');

1 创建一个普通项目

2 导入数据库驱动

3 编写测试代码

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

        //2. 连接信息  用户信息和url
        String url = "jdbc:mysql://localhost:3306/jdbc_study?useUnicode=true&characterEncoding=utf8&useSSL=false";
        String username = "root";
        String password = "631062568";

        //3. 连接成功  返回数据库对象  Connection 代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);

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

        //5. 用执行SQL的对象去执行SQL  可能存在结果,需要查看返回结果
        String sql = "SELECT * FROM users";
        ResultSet resultSet = statement.executeQuery(sql);  // 返回的结果集,结果集中封装了我们全部的查询出来的结果

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

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

    }
}

步骤总结 -->

1 加载驱动

2 连接数据库

DriverManager.getConnection(url, username, password);

3 获得执行的sql对象

connection.createStatement();

4 获得返回的结果集

statement.executeQuery(sql);

5 释放连接

DriverManager

// DriverManager.registerDriver(new com.mysql.jdbc.Driver())
Class.forName("com.mysql.jdbc.Driver");  // 固定写法 加载驱动

// connection 代表数据库
Connection connection = DriverManager.getConnection(url, username, password);

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

URL

String url = "jdbc:mysql://localhost:3306/jdbc_study? useUnicode=true&characterEncoding=utf8&useSSL=false";

// mysql  3306
//  URL格式  -->  jdbc:mysql://主机地址:端口号/数据库名?参数1&参数2&参数3

// oralce 1521
// 				  jdbc:oracle:thin:@;localhost:1521:sid

statement 执行SQL的对象 prepareStatement 也是执行SQL的对象

String sql = "SELECT * FROM users";  // 编写sql语句

statement.executeQuery();	// 查询  查询操作返回ResultSet
statement.execute();	// 执行任何SQL
statement.executeupdate();	// 增删改  都是只用这个  返回一个受影响的行数

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

获得指定的数据类型

// 在不知道字段类型的情况下使用
resultSet.getObject();
// 在知道字段类型的情况下使用
resultSet.getInt();
resultSet.getString();  // varchar
resultSet.getDate();	// 日期类型
resultSet.getBigDecimal() // 钱

遍历指针

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

释放资源必须做

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

statement对象

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

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

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

CRUD操作 --> create

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

CRUD操作 --> delete

Statement statement = connection.createStatement();
String sql = "delete from users where id = 1";
int num = statement.executeUpdate(sql);
if(num > 0){
	System.out.println("插入成功!!!");
}

CRUD操作 --> update

Statement statement = connection.createStatement();
String sql = "update users set name = '' where name = ''";
int num = statement.executeUpdate(sql);
if(num > 0){
	System.out.println("插入成功!!!");
}

CRUD操作 --> read

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

代码实现

1、提取工具类

public class JdbcUtils {

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

    static {

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

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


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



        }catch (Exception e){
            e.printStackTrace();
        }
    }

    //获取连接
    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 exception) {
                exception.printStackTrace();
            }
        }

        if (st != null){
            try {
                st.close();
            } catch (SQLException exception) {
                exception.printStackTrace();
            }
        }

        if (conn != null){
            try {
                conn.close();
            } catch (SQLException exception) {
                exception.printStackTrace();
            }
        }

    }
}

2、编写代码

增 insert

public class TestInsert {
    public static void main(String[] args) {

        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;


        try {

            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            String sql = "INSERT INTO users(id,`name`,`password`,email,birthday)\n" +
                    "VALUES(4,'xiaoliu','123456','123@qq.com','2024-6-29')";

            int i = st.executeUpdate(sql);

            if (i > 0) {
                System.out.println("插入成功！");
            }


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

    }
}

删 delete

public class TestDelete {
    public static void main(String[] args) {

        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;


        try {

            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            String sql = "DELETE FROM `users` WHERE id = 4";

            int i = st.executeUpdate(sql);

            if (i > 0) {
                System.out.println("删除成功！");
            }


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

    }
}

改 update

public class TestUpdate {

    public static void main(String[] args) {

        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;


        try {

            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            String sql = "UPDATE users SET `name` = '小刘',email = '631062568@qq.com' WHERE id = 1";

            int i = st.executeUpdate(sql);

            if (i > 0) {
                System.out.println("更新成功！");
            }


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

    }
}

查 select

public class TestSelect {
    public static void main(String[] args) {

        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {

            conn = JdbcUtils.getConnection();
            st = conn.createStatement();

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

            rs = st.executeQuery(sql);


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

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

SQL 注入问题

sql存在漏洞，会被攻击导致数据泄露 SQL会被or拼接

public class SQL {

    public static void main(String[] args) {

        // 正常的登录
        //login("小刘","123456");

        //sql注入登录
        login(" 'or'1=1"," 'or' 1=1");


    }

    // 登录业务
    public static void login(String username,String password){
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {

            conn = JdbcUtils.getConnection();
            st = conn.createStatement();

            String sql = "SELECT * FROM users WHERE name = '" + username + "' AND password ='" + password +"'";


            rs = st.executeQuery(sql);


            if (rs.next()){
                System.out.println(rs.getString("name"));
                System.out.println(rs.getString("password"));
                System.out.println("====================================");
            }

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

}

PreparedStatement对象

preparedStatement可以防止SQL注入。效率更高

1、增

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(?,?,?,?,?)";

            // 预编译SQL，先写sql，然后不执行
            st = conn.prepareStatement(sql);

            // 手动给参数赋值
            st.setInt(1,4);
            st.setString(2,"小刘");
            st.setString(3,"123");
            st.setString(4,"123@qq.com");

            // 注意：sql.Date  数据库    java.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 exception) {
            exception.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }

    }



}

2、删

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 = ?";

            // 预编译SQL，先写sql，然后不执行
            st = conn.prepareStatement(sql);

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

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

    }




}

3、更新

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 = ?;";

            // 预编译SQL，先写sql，然后不执行
            st = conn.prepareStatement(sql);

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

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

    }

}

4、查

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);

            rs = st.executeQuery();

            if (rs.next()){
                System.out.println(rs.getString("name"));
            }
        } catch (SQLException exception) {
            exception.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

5、防止SQL注入

public class SQL {

    public static void main(String[] args) {

        // 正常的登录
        //login("小刘","123456");

        //sql注入登录
        //login(" 'or'1=1"," 'or' 1=1");

        // 改良过后写sql注入
        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注入的本质：把桩底进来的参数当作字符
            // 假设其中存在转义字符，比如说'' 会被直接转义
            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();


            if (rs.next()){
                System.out.println(rs.getString("name"));
                System.out.println(rs.getString("password"));
                System.out.println("====================================");
            }

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

}

使用IDEA连接数据库

事务

要么都成功，要么都失败

ACID原则

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

一致性：总数不变，数据不变且正确

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

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

隔离性的问题：

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

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

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

代码实现

1、开启事务

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

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

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 exception) {
            try {
                conn.rollback();    // 如果失败则回滚事务
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            exception.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

数据库连接池

数据库连接 -- 执行完毕 -- 释放从连接到释放十分的浪费资源

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

最小连接数：10

最大链接数：15

等待超时：100ms

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

开源数据源实现（拿来即用）

DBCP

C3P0

Druid：阿里巴巴

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

DBCP

需要用到的jar包

commons-dbcp-1.4、commons-pool-1.6

C3P0

需要导入jar包

c3p0-0.9.5.5.jar、mchange-commons-java-0.2.19.jar

结论

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

Druid

Aoache

posted @ 2025-02-26 17:02 LYQ学Java 阅读(37) 评论(0) 收藏举报

刷新页面返回顶部

LyqStudyJava

MySQL01

初识MySQL

链接数据库

操作数据库

操作数据库

数据库的列类型

数据库的字段属性 --> 重点

创建数据库表

数据表的类型

修改和删除表中字段

MYSQL数据管理

外键 --> 了解

DML语言 --> 背会

添加

修改

删除

DQL查询数据 --> 最重点

DQL Date Query Language 数据查询语言

指定查询字段

where条件子句

联表查询

分页和排序

子查询

分组和过滤

MySQL函数

常用函数

聚合函数 --> 重要

数据库级别的MD5加密

事务

什么是事务?

索引

索引的分类

测试索引

索引原则

权限管理和备份

用户管理

MySQL备份

规范数据库设计

为什么需要数据库设计

三大范式

规范性和性能的问题

JDBC (重点)

数据库驱动

JDBC

第一个JDBC程序

statement对象

PreparedStatement对象

使用IDEA连接数据库

事务

数据库连接池

公告