MySQL

最新推荐文章于 2023-12-09 13:56:26 发布
最新推荐文章于 2023-12-09 13:56:26 发布
阅读量2k 收藏
点赞数 1
文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/ALeonsuoqiu/article/details/109522741
版权

1、初始MySQL

JavaEE: 企业级Java开发 web

前端（页面: 展示数据）

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

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

1.1 为什么学习数据库

  1. 岗位需求
  2. 现在的世界，大数据时代，的数据着得天下
  3. 被迫需求：存数据
  4. 数据库是所有软件中核心的存在 DBA （Datebase Admin）

1.2 什么是数据库

数据库（DB，DataBase）

概念：数据仓库，软件，安装在操作系统之上的！500万

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

1.3 数据库分类

关系型数据库：（SQL）
  1. MYSQL ，Oracle Sql Server ,DB2 ,SQLite
  2. 通过表和表之间，行和列之间的关系进行数据的存储
非关系型数据库：(NoSQL)
  1. Redis, mongDB
  2. 非关系型数据库 ，对象存储，通过对象的自身属性来决定
DBMS（数据库管理系统）
  1. 数据库的管理软件，科学有效的管理我们的数据。维护和获取数据
  2. MySQL

1.4 MySQL简介

MySQL是一个关系型数据库

前世： 瑞典MySQL AB公司

今生： Oracle旗下产品

MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统)

开源的数据库软件

体积小 速度快 总体拥有成本低 找人成本比较低 所有人必须会

1.5 Sqlyong软件安装和使用

mysql -uroot -p123456  --链接数据库
----------------------------------
--所有sql语句都以 ；号结尾
mysql> show databases; --查询所有数据库

mysql> use school;
Database changed --切换数据库

mysql> show tables; --查看所有表

mysql> describe student --查看表中的所有信息

--表示单行注释（sql 本来的注释） /* */（sql 多行注释）

数据库xxx语言 CRUD 增删改查

DDL 定义

DML 操作

DQL 查询

DCL 控制

2、操作数据库

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

2.1 操作数据库(了解)

  1. 创建数据库

    create database [if not exists] westos;

  2. 删除数据库

    drop database if exists westos;

  3. 使用数据库

    use westos; -- 如果表明或者字段名是特殊字段 加`

  4. 查看数据库

    show databases;

2.2 数据库的列类型

数值
tinyint    十分小的数据      1个字节
smallint   较小的数据        2个字节
mediumint  中等大小的字节     3个字节
int        标准的整数        4个字节
bigint        较大的数据        8个字节
float      浮点数           4个字节
double     浮点数			 8个字节
decimal    字符串形式的浮点数 金融计算的时候，一般会使用

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

时间日期
java.uitl.Date
date        YYYY-MM-DD ,日期
time		HH:mm:ss    时间日期
datatime    YYYY-MM-DD HH:mm:ss 
timestamp   时间戳， 1970.1.1到现在的毫秒数！
year        年份	

null 
没有值，未知
不能使用null进行运算 结果为null

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

Unsigned：

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

zerofill：

  • 0填充的
  • 不足的位数，使用0来填充，int（2） ，5 ---- 05

自增：

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

**非空：**NUll not null

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

默认：

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

2.4 创建数据库表（重点）

-- 要求 创建一个school的数据库
-- 创建 学生表（列，字段） 使用SQL创建
-- 学号int 登录密码 varchar（20） 姓名，性别varchar（20） 出生日期（datetime） 家庭住址，email
USE school;
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 '出生日期',
	`homeadd` VARCHAR(10) DEFAULT NULL COMMENT '家庭住址',
	`email` INT(10) DEFAULT NULL COMMENT '电子邮件',
	PRIMARY KEY(`id`)
)ENGINE INNODB DEFAULT CHARSET=utf8;

格式总结：

create table [if not exists] `表明`(
	`字段` 列类型 [属性] [注释],
    `字段` 列类型 [属性] [注释],
    ……
    `字段` 列类型 [属性] [注释],
)[表类型] [字符集设置] [注释]

常用命令：

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

2.5 数据库表的类型

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

常规的使用操作：

  • myisam 节约空间 速度较快
  • InnoDB 安全性高 ，事务处理，外键多表多用户操作

在物理空间存在的位置

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

本质还是文件的存储

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

  • InnoDB 在数据库表中只有一个 *.frm文件 以及目录上一层ibdata1文件
  • myisam 对应文件
    • *.frm 表结构的定义文件 （show create table student）
    • *.MYD 数据文件 （data）
    • *.MYI 索引文件（idnex）

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

charset=utf8

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

MySQL的默认编码是Latin1,不支持中文

在my.ini中配置默认的编码

character-set-server=utf8

2.6 修改删除表

修改

-- 修改表名  alter table 旧表名 rename as 新表名
ALTER TABLE student1 RENAME AS student;

-- 增加表的字段 alter table 表名 add 需要添加的字段名 列属性
ALTER TABLE student ADD age INT(11) DEFAULT '10';

-- 修改表的字段（重命名，约束）
ALTER TABLE 表名 MODIFY 字段名 列属性 -- 修改约束
ALTER TABLE student MODIFY age VARCHAR(10) DEFAULT '12'; -- 修改约束

ALTER TABLE 表名 CHANGE 旧字段名 新字段名  列属性 -- 修改约束
ALTER TABLE student CHANGE age age1 INT(11) DEFAULT '10'; -- 重命名

-- 删除表的字段 ALTER TABLE 表名 DROP 字段名;
ALTER TABLE student DROP age1;

删除

-- 删除表:  DROP TABLE IF EXISTS 数据库表名;
DROP TABLE IF EXISTS student;

3、MySQL数据库管理

3.1 外键（了解）

方法一：

-- 创建一个年级表
CREATE TABLE IF NOT EXISTS `grade`(
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
	`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
	PRIMARY KEY(`gradeid`)
)ENGINE INNODB DEFAULT CHARSET=utf8;


DROP TABLE student;

CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '学生编号',
	`name` VARCHAR(50) NOT NULL COMMENT '学生名字',
	`gradeid` INT(10) COMMENT '学生的年级',
	PRIMARY KEY(`id`),
	-- 定义key 
	KEY `FK_gradeid`(`gradeid`),
	CONSTRAINT  `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE INNODB DEFAULT CHARSET=utf8;

方法二：

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

CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '学生编号',
	`name` VARCHAR(50) NOT NULL COMMENT '学生名字',
	`gradeid` INT(10) NOT NULL COMMENT '学生年级',
	PRIMARY KEY (`id`)
)ENGINE INNODB DEFAULT CHARSET=utf8;

-- 创建表的时候没有索引关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_greadeid`  FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

3.2 DML语言（全部记住）

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

DML 语言：数据操作语言

  • insert
  • update
  • delete

3.3 添加

insert

-- 插入语句
-- insert into 表名([字段1，字段2，……])values('值1'),('值2')……
INSERT INTO `grade`(`gradename`)VALUES('大一');

-- 由于主键会自增，所以我们可以省略（如果不写字段，他会一一匹配）
INSERT INTO `grade` VALUES('大二');

-- 插入多个字段 
INSERT INTO `grade`(`gradename`)
VALUES('大三'),('大四');

INSERT INTO `student`(`name`,`password`,`sex`)
VALUES('梁宏华1','123456','女'),
('测试插入多条数据1','123456','男'),
('测试插入多条数据2','123456','男')

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

注意事项：

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

3.4 修改

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

-- 修改学员名字 带了where 条件 
UPDATE `student` SET `name`='张三' WHERE id=1;

-- 不指定情况的下，会改动所有的表！
UPDATE `student` SET `name`='王五';

-- 修改多个属性，使用逗号隔开
UPDATE `student` SET `name`='张三',`password`='1234656666',`sex`='男',`email`='1611657345@qq.com' WHERE id=1;

UPDATE `student` SET `name`='张三fafa' WHERE `id` BETWEEN 2 AND 5;

-- 通过多个条件定位数据
UPDATE `student` SET `name`='张三12222' WHERE `name`='张三fafa' AND sex='男';

语法：UPDATE 表名 SET column_name=value,[column_name=value]…… where [条件]

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

操作符会返回布尔值

操作符含义范围结果
=等于5=6false
<>或则 ！=不等于5<>6true
>大于5>6false
<小于5<6true
>=大于等于5>=6false
<=小于等于5<=6true
BETWEEN … AND …在某个值区间[2,5]
AND我和你5>1 and 1>2false
OR我或你||5>1 or 1>2true

语法：UPDATE 表名 SET column_name=value,[column_name=value]…… where [条件]

注意点：

  • column_name 是数据库的列，尽量带上``
  • 条件，筛选的条件，如果没有指定，则会修改所有的列
  • value，是一个具体的值，也可以是一个变量
UPDATE `student` SET `birthday`=CURRENT_TIME  WHERE `name`='张三fafa' AND sex='男';

3.5 删除

Delete

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

-- 删除数据（避免这样写，会全部删除）
DELETE FROM `student`

-- 删除指定数据
DELETE FROM `student` WHERE id=1;

truncate 命令

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

delete 和truncate的区别

  • 相同点：都能删除数据
  • 不同点：
    • truncate 重新设置自增列 计数会归零
    • truncate 不会影响事务
-- 测试delete 和truncate的区别
CREATE TABLE `test`(
	`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '测试id',
	`name` VARCHAR(50) NOT NULL COMMENT '测试名字',
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 插入测试数据
INSERT INTO `test`(`name`) VALUES('测试1'),('测试2'),('测试3'),('测试4');

-- 使用delete删除表数据，实验发现 自增不会被影响
DELETE FROM `test`; 

-- 使用truncate删除数据,实验发现，自增从零开始了
TRUNCATE TABLE `test`;

了解即可：delete 重启数据库 现想

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

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

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

  • 所有的查询在操作都用他 select
  • 简单的查询，复杂的查询它都能做到
  • 数据库中最核心的语言，最重要的语句
  • 使用频率最高的语句

4.1 DQL

select 完整语法

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

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`

语法：select 字段1... from 表

有的时候，列名不是那么见名思意，所以要起别名 语法 字段 AS 别名 表名 AS 别名

去重 distinct

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

-- 查询哪些同学参加了考试，成绩表`result`
SELECT * FROM `result` -- 查询全部的考试成绩
SELECT `studentno` FROM `result` -- 查看哪些同学参加了考试
SELECT DISTINCT `studentno` FROM `result` -- 发现了重复数据 需要去重 distinct

数据库的列（表达式）

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

-- 给学员考试成绩加一分 查看
SELECT `studentno`,`studentresult` AS 原来的分数,`studentresult`+1 AS 加分后 FROM `result`

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

select 表达式 from 表

4.3 where 条件子句

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

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

逻辑运算符

运算符语法描述
and &&a and b a&&b逻辑与，两个为真，则为真
or ||a or b a||b逻辑或，一个为真，结果为真
Not ！Not a ！a逻辑非，真为假，假为真
-- =============================== where  ====================================
-- 查询学生成绩
SELECT `studentno`,`studentresult` FROM `result`
-- 查询出成绩在95 ~ 100之间的
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult`>=95 AND `studentresult`<=100

-- 模糊查询（区间）
SELECT `studentno`,`studentresult` FROM `result` 
WHERE  `studentresult` BETWEEN  95 AND 100

SELECT `studentno`,`studentresult` FROM `result` 
WHERE  `studentresult` BETWEEN 60 AND 80

-- 除了分数不是70的以外
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult` !=70

模糊查询

运算符语法描述
is nulla is null如果操作符为null，结果为真
is not nulla is not null如果操作符为不null，结果为真
betweena between b and c若a 在b和c 之间，结果为真
Likea like bSQL匹配，如果a匹配b，结果为真
ina in （a1,a2,a3）假设a在a1，或者a2…其中的某一个值中，结果为真
-- =============================== 模糊查询  ====================================
-- 查询姓赵的同学
-- 通过like %(0到任意字符)   _(一个字符)
SELECT `studentno`,`studentname` FROM `student`
WHERE  `studentname` LIKE '%强%'

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

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

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

-- ====== in  具体的一个和多个值======
-- 查询1000，1001的同学
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentno` IN(1000,1001)

-- 查询在广东深圳的同学
SELECT `studentno`,`studentname` FROM `student`
WHERE `address` IN('广东深圳')

-- 查询在广东深圳和北京朝阳的同学
SELECT `studentno`,`studentname` FROM `student`
WHERE `address` IN('广东深圳','北京朝阳')

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

-- 查询有出生日期的同学 不为空
SELECT `studentno`,`studentname` FROM `student`
WHERE `borndate` IS NOT NULL

-- 查询没有出生日期的同学 为空
SELECT `studentno`,`studentname` FROM `student`
WHERE `borndate` IS NULL

4.4 联表查询

JOIN 对比

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

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

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

/*思路
1.分析需求，分析查询的字段来源于哪几张表（联表查询）
2.确定使用哪种连接查询？ 7种
确定交叉点（这两张表哪些数据是相同的）
判断条件： 学生表中的studentNO =成绩表中的studentNO
*/
-- ==== inner join===
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
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`

-- ==== right join 查询不显示数据为空的===
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.`studentno` = r.`studentno`
操作描述
inner join如果表中至少有一个匹配，就返回行
left join会从左表返回所有的值，即使右表没有匹配
right join会从右边表返回所有的值，即使左表没有匹配
-- =============================== 联表查询  ====================================
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM `student`
SELECT * FROM `result`

/*思路
1.分析需求，分析查询的字段来源于哪几张表（联表查询）
2.确定使用哪种连接查询？ 7种
确定交叉点（这两张表哪些数据是相同的）
判断条件： 学生表中的studentNO =成绩表中的studentNO
*/
-- ==== inner join===
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
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`

-- ==== right join 查询不显示数据为空的===
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.`studentno` = r.`studentno`

-- 查询缺考的同学
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` AS s
LEFT JOIN `result` AS r
ON s.`studentno` = r.`studentno`
WHERE studentresult IS NULL

-- 思考题 （查询参加了考试的同学信息：学号,学生姓名,科目名,分数）
/*思路
1.分析需求，分析查询的字段来源于哪几张表（联表查询） `student`  `subject` `result`
2.确定使用哪种连接查询？ 7种  参加了的话用 right join
确定交叉点（这两张表哪些数据是相同的）
判断条件： 学生表中的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`

自链接(了解)

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

父类：

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

子类：

categoryidpidcategoryname
43数据库
63web开发
75ps技术
82办公信息

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

父类子类
信息技术办公技术
软件开发数据库
软件开发web开发
美术设计ps技术
-- 查询父类对应的子类关系
SELECT a.`categoryname` AS '父栏目', b.`categoryname` AS '子栏目'
FROM  `category` AS a,  `category` AS b
WHERE a.`categoryid` = b.`pid`

4.5 分页和排序

排序

-- 排序： 升序ASC   降序DESC
-- order by 通过那个字段排序 怎么排

-- 查询的结果根据成绩降序 排序
SELECT s.`studentno` AS '学号',`studentname` AS '学生姓名',`studentresult` AS '成绩'
FROM `student` s 
RIGHT JOIN `result` AS r 
ON s.`studentno` = r.`studentno` 
ORDER BY `studentresult` DESC

分页

-- 100万条数据
-- 为什么要用分页？
-- 缓解数据库压力，给人良好的体验，瀑布流（图片 抖音）

-- 分页，每页只显示，2条数据
-- 语法： limit 起始页，页面的大小
-- limit 0,2 1~2
-- limit 1,2 2~3
SELECT s.`studentno` AS '学号',`studentname` AS '学生姓名',`studentresult` AS '成绩'
FROM `student` s 
RIGHT JOIN `result` AS r 
ON s.`studentno` = r.`studentno` 
ORDER BY `studentresult` DESC
LIMIT 0,2 

-- 第一页 limit 0,2  （1-1）* 2
-- 第二页 limit 2,2  （2-1）* 2 
-- 第三页 limit 4,2  （3-1）* 2
-- 第N页 limit 0,2   （n-1）* pageSize，pageSize
-- 【pageSize：页面大小】
-- 【(n-1)*pageSize:起始值】
-- 【n当前页】
-- 【数据总数/页面大小 = 总页数】

语法：limit (查询起始下标，pageSize)

练习:

-- 思考 查询 高等数学-1 课程成绩排名前三的同学，并且分数要大于80的学生信息（学号，姓名，课程名称，分数）
SELECT s.`studentno` AS '学号',`studentname` AS '姓名',`subjectname` AS '课程名称',`studentresult` AS '分数'
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub 
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` =  '高等数学-1' AND studentresult>=80
ORDER BY r.`studentresult` DESC  
LIMIT 0,3

4.6 子查询

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

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

-- ======================== where    =========================

-- 1.查询 高等数学-1 的所有考试结果（学号，科目编号，成绩 ）降序排序
SELECT  `studentno`,r.`subjectno`,`studentresult`
FROM `result` AS r
INNER JOIN `subject` AS sub
ON r.`subjectno` = sub.`subjectno`
WHERE sub.`subjectname` = '高等数学-1'
ORDER BY r.`studentresult` DESC


-- 方式二：使用子查询（）
SELECT  `studentno`,`subjectno`,`studentresult`
FROM `result` AS r
WHERE `subjectno` = (
	-- 查询数据库为 高等数学-1的所有成绩
	SELECT `subjectno`
	FROM `subject` AS sub
	WHERE subjectname = '高等数学-1'
)
ORDER BY r.`studentresult` DESC


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

-- 方法二
SELECT DISTINCT s.`studentno`,`studentname`
FROM `student` AS s 
WHERE studentno = (
      -- 查询学生成绩大于80的
      SELECT DISTINCT `studentno` 
      FROM `result` AS r
      WHERE `studentresult` > 80
)

4.7 分组过滤

-- 查询不同课程的平均分，最高分，最低分 条件 平均分大于80的
-- 核心 根据不同科目分组
SELECT `subjectname`AS '科目',AVG(`studentresult`) AS '平均分',MAX(`studentresult`)'最高分',MIN(`studentresult`) '最低分'
FROM `result` AS r
INNER JOIN `subject` AS sub
ON r.`subjectno` = sub.`subjectno`
GROUP BY sub.`subjectname`
HAVING AVG(`studentresult`)>80

4.8 seslect 小结

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

5、MySQL函数

5.1 常用函数

-- ====================常用函数=====================
-- 获取当前日期
SELECT CURRENT_DATE();

-- 获取当前时间
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 USER()
SELECT VERSION()

5.2 聚合函数(常用)

count计数
avg平均数
sum总和
max最大值
min最小值
-- 统计表中的数据
SELECT COUNT(1) FROM result -- 不会忽略null值 本质计算行数
SELECT COUNT(*) FROM result -- 不会忽略null值 本质计算行数
SELECT COUNT(studentno) FROM result  -- count字段 会忽略null值

SELECT SUM(`studentresult`) FROM `result`
SELECT AVG(`studentresult`) FROM `result`
SELECT MAX(`studentresult`) FROM `result`
SELECT MIN(`studentresult`) FROM `result`

-- 查询不同课程的平均分，最高分，最低分 条件 平均分大于80的
-- 核心 根据不同科目分组
SELECT `subjectname`AS '科目',AVG(`studentresult`) AS '平均分',MAX(`studentresult`)'最高分',MIN(`studentresult`) '最低分'
FROM `result` AS r
INNER JOIN `subject` AS sub
ON r.`subjectno` = sub.`subjectno`
GROUP BY sub.`subjectname`
HAVING AVG(`studentresult`)>80

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

-- ========== 测试MD5 加密 ==========
CREATE TABLE IF NOT EXISTS `testmd5`(
	`id` INT(10) NOT NULL COMMENT 'id',
	`name` VARCHAR(30) NOT NULL COMMENT '名字',
	`pwd` VARCHAR(20) NOT NULL COMMENT '密码',
	PRIMARY KEY(`id`)
)ENGINE=INNODB CHARSET=utf8

-- 插入数据
--  明文密码
INSERT INTO `testmd5` 
VALUES(1,'zhangsan','123456'),
(2,'lisi','123456'),
(3,'wangwu','123456'),
(4,'zhaoliu','123456')

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

ALTER TABLE testmd5 CHANGE pwd pwd VARCHAR(50) -- 重命名

INSERT INTO `testmd5` 
VALUES(5,'zhangsan1',MD5('123456'))

SELECT * FROM `testmd5` WHERE `name` = 'zhangsan1' AND `pwd` = MD5('123456')

6、事务

6.1 什么是事务？

要么都成功，要么都失败（同生共死）

1、SQL执行 A:1000 ------>200 B:200

2、SQL执行 A:800 B:400

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

事务原则： ACID 原子性 一致性 隔离性 持久性 （脏读 幻读 不可重复读 …）

原子性（Atomicity）：

原子性是指事务一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生

一致性（Consistency）:

事务前后数据的完整性必须保持一致

隔离性（lsolation）：

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

持久性（Durability）：

持久性是指，当事务一旦被提交，它对数据库的数据的改变就是永久性的，接下来数据库发生故障也不会对他有影响

隔离所导致的问题

脏读：

指一个事务读到另一个事务未提交的数据

不可重复读：

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

虚读(幻读)

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
（一般是行影响，多了一行）

执行事务

-- ======================== 事务 =========================
-- 什么是事务？ACID 原子性 一致性 隔离性 持久性

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

-- 手动提交事务

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

-- 提交 commit 持久化数据

-- 回滚 rollback 回到原来的状态

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

模拟场景

-- 转账
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'
UPDATE `account` SET `money` = `money` + 500 WHERE `name` = 'B'

-- 提交
COMMIT 

-- 回滚
ROLLBACK

7、索引

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构

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

7.1 索引的分类

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

  • 主键索引（PRIMAPY KEY）
    • 唯一的标识，主键不可重复，只有一个列作为主键 非空
  • 唯一索引（UNIQUE KEY）
    • 避免重复列的出现，唯一索引可以重复，多个列都可以标识为唯一索引
  • 常规索引（KEY INDEX）
    • 默认的 index key 关键字来设置
  • 全文索引（FullText）
    • 在特定的数据库引擎才有 MyISAM
    • 快速定位数据

基础语法

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

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

-- 增加一个全文索引 （索引名） 列名
ALTER TABLE school.student ADD FULLTEXT INDEX `studentname`(`studentname`)

-- explain 分析sql执行的状况
EXPLAIN SELECT * FROM student -- 非全文索引

EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('梁')

7.2 测试索引

-- 测试查询时间
SELECT * FROM `app_user` WHERE `name` = '用户9999'; -- 0.589 sec
SELECT * FROM `app_user` WHERE `name` = '用户9999'; -- 0.602 sec
SELECT * FROM `app_user` WHERE `name` = '用户9999'; -- 0.611 sec

-- 分析sql 执行的状况
EXPLAIN  SELECT * FROM `app_user` WHERE `name` = '用户9999'; -- rows：1000671 
 
-- id_表名_字段名
-- create index 索引名 on 表（字段名）
-- 创建索引 
CREATE INDEX id_app_user_name ON `app_user`(`name`)

SELECT * FROM `app_user` WHERE `name` = '用户9999'; -- 0.002 sec
SELECT * FROM `app_user` WHERE `name` = '用户9999'; -- 0.002 sec
SELECT * FROM `app_user` WHERE `name` = '用户9999'; -- 0.003 sec

-- explain 分析sql执行状况
EXPLAIN SELECT * FROM `app_user` WHERE `name` = '用户9999'; -- rows：1

索引在数据量少的时候 用处不大 但是大数据的时候 区别特别明显

7.3 索引原则

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

索引的数据结构

Hash 类型的索引

Btree ：innoDB 的默认的数据结构

8、权限管理和数据备份

8.1 用户管理

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

SQL命令

用户表：mysql.user

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

-- 创建用户  create user 用户名 identified by '密码'
CREATE USER lhh IDENTIFIED BY '123465'

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

-- 修改密码（修改指定用户密码） %:允许远程链接 
SET PASSWORD FOR lhh = PASSWORD('111111')

-- 重命名 RENAME USER 原来的用户名 TO 新的用户名
RENAME USER lhh1 TO lhh2

SELECT * FROM USER

-- 给用户授予权限 (GRANT ALL PRIVILEGES ON 库.表 TO 用户名)
GRANT ALL PRIVILEGES ON *.* TO lhh2

-- 查看权限
SHOW GRANTS FOR lhh2 -- 查看指定用户权限

-- 撤销权限 revoke 哪些权限 在那个库撤销 给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM lhh2

-- 删除用户
DROP USER lhh2

8.2 MySQL 备份

为什么要备份：

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

MySQL数据库备份的方法

  • 直接拷贝物理文件（data）
  • 在Sqlyog 这种可视化工具中手动导出
    • [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZgC8cMH8-1604589156520)(C:\Users\LHH\AppData\Roaming\Typora\typora-user-images\image-20201020000601861.png)]
  • 使用命令行导出 mysqldump 命令行
    • [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fYOgmMdT-1604589156521)(C:\Users\LHH\AppData\Roaming\Typora\typora-user-images\image-20201020001312335.png)]

导入

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

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

9、规范数据库设计

9.1 为什么需要设计

糟糕的数据库设计

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

良好的数据库设计

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

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

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

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

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

三大范式

第一范式（1NF）

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

第二范式（2NF）

前提：满足第一范式

每张只描述一件事情

第三范式（3NF）

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

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

规范性 和 性能的问题

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

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

10、JDBC(重点)·

10.1 数据库驱动

驱动：声卡 显卡 数据库

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

10.3 JDBC

由于各个数据库厂商有各自的驱动，所以需要统一

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

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

需要导入的jar包

java.sql

javax.sql

还需要导入 数据库驱动包

mysql-connector-java-5.1.47.jar

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,'zhansan','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、导入数据库驱动

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

3、编写测试代码

package lhh.study01;

import java.sql.*;

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

        // 2.建立链接 用户信息和url
            /*
            * useUnicode=true 使用useUnicode编码 让他支持中文
            * characterEncoding=UTF8 设置他的字符集为utf-8
            * useSSL=true 使用安全链接
            * */
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=UTF8&useSSL=true";
        String username = "root";
        String password = "123456";

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

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

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

        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("password="+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、加载驱动 Class.forName

2、建立链接 DriverManager.getConnetion

3、获取执行SQL的对象

4、获取结果集

5、释放资源

DriverManager

// 1.加载驱动
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver"); //固定写法 加载驱动
Connection connection = DriverManager.getConnection(url, username, password);

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

URL

// 2.建立链接 用户信息和url
            /*
            * useUnicode=true 使用useUnicode编码 让他支持中文
            * characterEncoding=UTF8 设置他的字符集为utf-8
            * useSSL=true 使用安全链接
            * */
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=UTF8&useSSL=true";

//mysql -- 3306
// 协议://主机地址:端口号/数据库？参数1&参数2&参数3

//oralce -- 1521
// jdbc:oralce:thin:@localhost:1521:sid

Statement 执行SQL的对象 PrepareStatement 执行SQL对象

statement.executeQuery(); //查询操作 返回resultSet
statement.execute(); //执行任何SQL
statement.executeLargeUpdate(); //更新 插入 删除 都用这个 返回一个受影响的行数

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

获得指定的数据类型

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

遍历 指针

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

释放资源

// 6.释放资源
resultSet.close();
statement.close();
connection.close();

10.4 statement对象

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

statement对象的executeUpdate方法，用于向数据库发送增删改的sql语句，executeUodae执行后，会返回一个整数（即增删改语句导致了数据库几行数据发生了变化）

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

CRUD操作-create

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

String sql1 = "INSERT INTO `users`(id,`name`,`password`,email,birthday)VALUES(4,'lhh',123456,'1611657345@qq.com','1979-12-04')";
int num = statement.executeUpdate(sql1);
if(num>0){
   System.out.println("插入成功！！！");
}

CRUD操作-delete

String sql2 = "DELETE FROM `users` WHERE id= 4 ";
int num2 = statement.executeUpdate(sql2);
if(num2>0){
   System.out.println("删除成功！！！");
}

CRUD操作-update

String sql3 = "UPDATE `users` SET `name`='lhh1' WHERE `name`='lhh'";
int num3  = statement.executeUpdate(sql3);
if(num3>0){
    System.out.println("修改成功！！！");
}

代码实现

1、提取工具栏

package lhh.study02.utils;

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

                // 驱动只用加载一次
                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 con, 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(con!=null){
                try {
                    con.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
}

2、编写增删改的代码 executeUpdate

package lhh.study02.utils;

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

public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            Connection connection = JdbcUtils.getConnection();  //获取数据库链接
            Statement statement = connection.createStatement(); //获取SQL的执行对象
            String sql1 = "INSERT INTO `users`(id,`name`,`password`,email,birthday)VALUES(4,'lhh',123456,'1611657345@qq.com','1979-12-04')";
            int num1 = statement.executeUpdate(sql1);
            if(num1>0){
                System.out.println("插入成功！！！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }

    }
}


package lhh.study02.utils;

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

public class TestDelete {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            Connection connection = JdbcUtils.getConnection();  //获取数据库链接
            Statement statement = connection.createStatement(); //获取SQL的执行对象
            String sql1 = "delete from users where id=4";
            int num1 = statement.executeUpdate(sql1);
            if(num1>0){
                System.out.println("删除成功！！！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }

    }
}


package lhh.study02.utils;

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

public class TestUpdate {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            Connection connection = JdbcUtils.getConnection();  //获取数据库链接
            Statement statement = connection.createStatement(); //获取SQL的执行对象
            String sql1 = "UPDATE users SET  `NAME`='lhh?' WHERE id = 4";
            int num1 = statement.executeUpdate(sql1);
            if(num1>0){
                System.out.println("修改成功！！！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }

    }
}

查询：executeQuery

package lhh.study02.utils;

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

public class TestSelect {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            Connection connection = JdbcUtils.getConnection();  //获取数据库链接
            Statement statement = connection.createStatement(); //获取SQL的执行对象
            String sql1 = "select * from users";
            ResultSet resultSet = statement.executeQuery(sql1);
            while(resultSet.next()){
                System.out.println("name："+resultSet.getString("name"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }

    }
}

SQL注入问题

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

package lhh.study02.utils;

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

public class SQL注入 {
    public static void main(String[] args) {
        //login("lhh","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 {
            Connection connection = JdbcUtils.getConnection();  //获取数据库链接
            Statement statement = connection.createStatement(); //获取SQL的执行对象

            // SELECT `name`,`password` FROM `users` WHERE `name`='lhh' AND `password` = '123456';
            String sql1 = "SELECT `name`,`password` FROM `users` WHERE `name`='"+username+"' and `password`='"+password+"'";

            ResultSet resultSet = statement.executeQuery(sql1);
            while(resultSet.next()){
                System.out.println(resultSet.getString("name"));
                System.out.println(resultSet.getString("password"));

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

10.5 PreparedStatement对象

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

1、新增

package lhh.study03;

import lhh.study02.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 con = null;
        PreparedStatement pst = null;
        try {
            Connection connection = JdbcUtils.getConnection();

            //区别
            //使用？ 占位符替代参数
            String sql = "INSERT INTO `users`(id,NAME,PASSWORD,email,birthday)VALUES(?,?,?,?,?)";
            pst = connection.prepareStatement(sql);// 预编译sql 先写sql 不执行

            //手动给参数赋值
            pst.setInt(1,7);
            pst.setString(2,"梁宏华");
            pst.setString(3,"123456");
            pst.setString(4,"1611657345@qq.com");
            pst.setDate(5,new java.sql.Date(new Date().getTime()));

            //执行sql 并返回影响行数
            int i = pst.executeUpdate();
            System.out.println(i>0?"插入成功": "插入失败");

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(con,pst,null);
        }
    }
}

2、删除

package lhh.study03;

import lhh.study02.utils.JdbcUtils;

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

public class TestDelete {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement pst = null;
        try {
            Connection connection = JdbcUtils.getConnection();

            //区别
            //使用？ 占位符替代参数
            String sql = "DELETE FROM `users` WHERE id = ?";
            pst = connection.prepareStatement(sql);// 预编译sql 先写sql 不执行

            //手动给参数赋值
            pst.setInt(1,6);

            //执行sql 并返回影响行数
            int i = pst.executeUpdate();
            System.out.println(i>0?"删除成功": "删除失败");

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(con,pst,null);
        }
    }

}

3、更新

package lhh.study03;

import lhh.study02.utils.JdbcUtils;

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

public class TestUpdate {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement pst = null;
        try {
            Connection connection = JdbcUtils.getConnection();

            //区别
            //使用？ 占位符替代参数
            String sql = "UPDATE `users` SET `name` = ? WHERE id = ?";
             pst = connection.prepareStatement(sql);// 预编译sql 先写sql 不执行

            //手动给参数赋值
            pst.setString(1,"prepared");
            pst.setString(2,"1");

            //执行sql 并返回影响行数
            int i = pst.executeUpdate();
            System.out.println(i>0?"修改成功": "修改失败");

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(con,pst,null);
        }
    }
}

4、查询

package lhh.study03;

import lhh.study02.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 con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            Connection connection = JdbcUtils.getConnection();

            //区别
            //使用？ 占位符替代参数
            String sql = "select * from users ";
            pst = connection.prepareStatement(sql);// 预编译sql 先写sql 不执行

            //执行sql 并返回结果集
            ResultSet resultSet = pst.executeQuery();
            while (resultSet.next()){
                System.out.println("名字："+resultSet.getString("name"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(con,pst,rs);
        }
    }
}

5、防止SQL注入

    package lhh.study02.utils;

    import java.sql.*;

    public class SQL注入 {
        public static void main(String[] args) {
            //login("lhh","123456");
           // login(" 'or '1=1  "," 'or '1=1  ");
        }
        // 登录业务
        public static void login(String username,String password){
            Connection conn = null;
            PreparedStatement st = null;
            ResultSet rs = null;
            try {
                Connection connection = JdbcUtils.getConnection();  //获取数据库链接


                String sql1 = "SELECT `name`,`password` FROM `users` WHERE `name`=? and `password`=? ";
                st = connection.prepareStatement(sql1);

                //解析占位符
                st.setString(1,username);
                st.setString(2,password);
                ResultSet resultSet = st.executeQuery();

                while(resultSet.next()){
                    System.out.println(resultSet.getString("name"));
                    System.out.println(resultSet.getString("password"));

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

10.6 使用IDEA链接数据库

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

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

CREATE TABLE account(
                        id Int PRIMARY KEY AUTO_INCREMENT,
                        NAME VARCHAR(40),
                        money FLOAT
);

/*插入测试数据*/
insert into account(name,money) values('A',1000);
insert into account(name,money) values('B',1000);
insert into account(name,money) values('B',1000);

10.7 事务

要么都成功，要么都是失败

ACID原则

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

一致性：最总总数不变

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

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

隔离性的问题

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

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

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

代码实现

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

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

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

package lhh.study04;

import lhh.study02.utils.JdbcUtils;

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

/**
 * @author LHH
 */
public class TestTransaction01 {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

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

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

            String sql2 = "update account set money = money+100 where name ='B'";
            pst = con.prepareStatement(sql2);
            pst.executeUpdate();

            //业务完毕 提交事务
            con.commit();

            System.out.println("成功");



        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(con,pst,null);
        }
    }
}

10.8 数据库链接池

数据库链接---- 执行完毕 ---- 释放

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

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

最小链接数：10

最大连接数：15

等待超时：100ms

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

开源数据源实现

DBCP

C3P0

DRuid:阿里巴巴

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

C3P0

需要用到的jar包

c3p0-0.9.1.2.jar mchange-commons-java-0.2.11.jar

代码

package lhh.study05.utils;

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

public class Test_C3P0 {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement pst = null;
        try {
            Connection connection = JdbcUtils_C3P0.getConnection();

            //区别
            //使用？ 占位符替代参数
            String sql = "INSERT INTO `users`(id,NAME,PASSWORD,email,birthday)VALUES(?,?,?,?,?)";
            pst = connection.prepareStatement(sql);// 预编译sql 先写sql 不执行

            //手动给参数赋值
            pst.setInt(1,8);
            pst.setString(2,"梁宏华");
            pst.setString(3,"123456");
            pst.setString(4,"1611657345@qq.com");
            pst.setDate(5,new java.sql.Date(new Date().getTime()));

            //执行sql 并返回影响行数
            int i = pst.executeUpdate();
            System.out.println(i>0?"插入成功": "插入失败");

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils_C3P0.release(con,pst,null);
        }
    }
}


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

        <property name="acquiredIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </default-config>
    </c3p0-config>

package lhh.study05.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

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

public class JdbcUtils_C3P0 {
       private static ComboPooledDataSource dataSource = null;
       static {
           try {

               //创建数据源
               dataSource = new ComboPooledDataSource();

           } catch (Exception e) {
               e.printStackTrace();
           }
       }
       // 获取链接
       public static  Connection getConnection() throws SQLException {
           return dataSource.getConnection();
       }

       //释放链接
       public static void release(Connection con, 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(con!=null){
               try {
                   con.close();
               } catch (SQLException throwables) {
                   throwables.printStackTrace();
               }
           }
       }
}

System.out.println(i>0?“插入成功”: “插入失败”);

    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }finally {
        JdbcUtils_C3P0.release(con,pst,null);
    }
}

}




```xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!--
c3p0的缺省（默认）配置
如果在代码中"ComboPooledDataSource ds=new ComboPooledDataSource();"这样写就表示使用的是c3p0的缺省（默认）-->
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy</property>
        <property name="user">root</property>
        <property name="password">123456</property>

        <property name="acquiredIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </default-config>
    </c3p0-config>

package lhh.study05.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

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

public class JdbcUtils_C3P0 {
       private static ComboPooledDataSource dataSource = null;
       static {
           try {

               //创建数据源
               dataSource = new ComboPooledDataSource();

           } catch (Exception e) {
               e.printStackTrace();
           }
       }
       // 获取链接
       public static  Connection getConnection() throws SQLException {
           return dataSource.getConnection();
       }

       //释放链接
       public static void release(Connection con, 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(con!=null){
               try {
                   con.close();
               } catch (SQLException throwables) {
                   throwables.printStackTrace();
               }
           }
       }
}
lhhgoodman
关注
MySQL安装配置教程（超级详细、保姆级）
Soloversion的博客
03-26 42万+
一、 下载MySQL Mysql官网下载地址https://downloads.mysql.com/archives/installer/ 1. 选择想要安装的版本，本篇文章选择的是5.7.31版本，下面的那个文件，点击Download下载 二、 安装MySQL 1. 选择设置类型 双击运行mysql-installer-community-5.7.31.0.msi 这里选择是自定义安装，所以直接选择“Custom”，点击“Next” “Developer Default”是开发者默认
unsigned 无符号是什么？
Jambo!的博客
12-28 1086
unsigned是啥？
mysql数据库基础：数据类型介绍
lalala_dxf的博客
06-28 1606
1、如何设置无符号和有符号 2、如果插入的数据超过整型的取值范围，会报out of range异常，并且插入临界值。3、如果不设置长度，会有默认长度（1）M和D的含义 M：整数部分位数+小数部分位数 D：保留的小数位数（四舍五入） （2）M和D都可以省略 （3）如果时decimal定点型，则M默认为10，D默认为0 如果是float和double类型，则会根据插入的数值的精度来决定精度 （4）定点型的精确度较高，如果要求插入数值的精度较高，如货币运算等则考虑使用定点型。要求插入的值必须属于列表中指定的值之一
MySQL 字段属性解读：理解无符号（UNSIGNED）数值类型的含义与作用
最新发布
andy_68147772的博客
12-09 2970
在MySQL中，无符号（UNSIGNED）是一个属性，你可以在创建或修改表时为整数类型的列指定它。无符号属性意味着该列只能存储非负整数（0和正整数），而不是默认的有符号整数，后者可以存储负数、零和正数。使用无符号类型的好处是它可以扩大正数的存储范围，因为它不需要为负数分配空间。如果尝试插入一个负数到无符号列中，MySQL 会产生一个错误或将负数转换为最接近的有效值（通常是 0）。类型的列（默认是有符号的）可以存储从 -2147483648 到 2147483647 的整数。，那么这个列就不能存储负数。
mysql 改列名_mysql -1
weixin_29867607的博客
01-13 111
0 MySQL的数据类型1 数据库创建删除 使用2 数据表的显示 创建 删除 清空 修改表3 数据表内容的 查找 增加 删除 改4 数据库 用户以及权限的设置0 MySQL的数据类型数值、时间和字符串bit[(M)] 二进制位（101001），m表示二进制位的长度（1-64），默认m＝1tinyint[(m)][unsigned][zerofill] ...
MySQL - MySQL 8.0(二)基本操作：数据
写虫师的博客
07-16 616
文章目录 DDL（数据定义语言） 创建数据库 创建表 查看表结构 克隆表结构 TRUNCATE TABLE 删除表 删除数据库 DML（数据操作语言） INSERT DELETE UPDATE SELECT REPLACE、 INSERT、 ON DUPLICATE KEY UPDATE 加载示例数据 下载&解压 加载数据 验证数据
mysql jdbc连接驱动jar包（适用于mysql5.7版本，亲测可用）
08-17
MySQL JDBC连接驱动是Java应用程序与MySQL数据库之间通信的桥梁，使得开发者可以使用Java语言来执行SQL语句，处理数据库中的数据。本驱动适用于MySQL 5.7版本，并且经过实际测试，确认可用。这个jar包是完全免费的，...
mysql-connector-java-8.0.27
12-24
MySQL Connector/J 8.0.27 是MySQL数据库与Java应用程序之间的重要桥梁，它是一个实现了JDBC（Java Database Connectivity）接口的驱动程序，允许Java开发者在应用中无缝地访问MySQL数据库。MySQL Connector/J使得...
mysql驱动jar 文件适用MySQL5.7
01-18
MySQL驱动jar文件是Java应用程序与MySQL数据库之间通信的关键组件，主要功能是提供Java Database Connectivity (JDBC) API，使得Java开发者能够通过编写Java代码来执行SQL语句，从而操作MySQL数据库。标题提到的...
mysql驱动（适用于mysql5.7版本）
12-07
MySQL驱动是Java应用程序连接到MySQL数据库的关键组件，它允许程序通过Java Database Connectivity (JDBC) API与MySQL服务器进行通信。本压缩包包含了两个适用于MySQL 5.7版本的驱动文件：`mysql-connector-java-...
mysql 数据库unsigned的用法
热门推荐
cai_and_luo的博客
05-11 1万+
在mysql数据库中，unsigned表面含义是 '无符号’的意思,unsigned既为非负数，用此类型可以增加数据长度. 例如，如果 int最大是2147683647，那 tint unsigned 最大 就可以到 2147683647 * 2 与unsigned类似的还有binary,unsigned 属性只针对整型，而binary属性只用于char 和varchar。 当设置为unsigned时候，报错BIGINT UNSIGNED value is out of ran..
mysql中 signed 与 unsigned  详解
qq_45445841的博客
01-16 1万+
mysql中 signed 与 unsigned详解 两者区别 unsigned是mysql自定义的类型，表示无符号数值即非负数。signed为整型默认属性。 区别1：起到约束数值的作用。 区别2：可以增加数值范围。 以tinyint为例，它的取值范围-128-127，加不加signed都默认此范围。加上unsigned表示范围0-255，其实相当于把负数那部分加到正数上...
mysql的unsigned_mysql数据库UNSIGNED字段属性使用问题
weixin_42437984的博客
01-18 1420
在研究laravel默认的users表的时候，发现主键id的字段属性是UNSIGNED，无符号型，也就是说有这个属性的数值类型都是正数，可描述的数据量就大了一倍，主要适合id这种必然是正整数，且不需要做数值运算的场景。我查了一下，对于mysql数据库使用UNSIGNED有一些不同意见，有篇文章(http://www.cnblogs.com/blankqdb/archive/2012/11/03/b...
Archery – SQL审核查询平台
weixin_45129599的博客
12-15 593
本页目录 Archery使用说明需要调整的SQL文件修正记录如下禁止指定存储引擎:’XXX表名’列 ‘created_by’ 不允许为null(表 ‘XXX表名’). 此处无需处理！表 ‘base_dict’ 禁止设置排序规则!允许的排序规则建议自增列初始值置为 1列 ‘id’ 需要设置注释(表’XXX表名’).自增列建议设置无符号标志unsigned(表’XXX表名’). 这个有待考证表 ‘XX...
无符号unsigned带来的问题探讨
2020
03-30 1347
C语言有符号无符号数问题 题目:用下图代码计算数组array中所有元素的和,其中元素的数量由参数length给出. 问题: 试分析这段程序,当参数length=0时,运行结果/返回值为多少? 运行这段代码, 当参数length=0时,实际会产生什么结果? 请解释实际运行结果产生的原因. 修改代码以得出正确结果. 一:先讨论参数为有符号类型. 此函数显然为叠加求数组元素的和,在不发...
MySQL 的两个特殊属性 unsigned与 zerofill
荒的博客
05-07 572
1 unsigned 　　unsigned 就是将数字类型无符号化， 例如 int 型的范围：-2^31 ~2^31 - 1，而unsigned int的范围：0 ~ 2^32。看起来unsigned 是个不错的类型，尤其是用在自增或者没有负数的情况。但是在实际使用中会出现一些意外的情况。 如上图，我们新建了一个测试表，两个字段均为unsigned int型，插入两个值，然后做减法运算时报错，并不是我们想要的结果，正常情况 1-2=-1，但是当数据类型为unsigned时，MySQL 运算的结果也.
关于自增id 你可能还不知道
weixin_34319817的博客
04-07 236
导读：在使用MySQL建表时，我们通常会创建一个自增字段(AUTO_INCREMENT)，并以此字段作为主键。本篇文章将以问答的形式讲述关于自增id的一切。 注： 本文所讲的都是基于Innodb存储引擎。 1.MySQL为什么建议将自增列id设为主键？ 如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键，则InnoDB会选择第一个不包...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值