本系列文章为【狂神说 Java 】视频的课堂笔记,若有需要可配套视频学习。
1. 简介
数据库(DB,Database)是安装在操作系统上的存储数据的软件。
关系型数据库(RDB)以行列形式存储数据。
非关系型数据库(NoSQL)以对象形式存储数据。
数据库管理系统(DBMS)是数据库的管理软件,可以管理其中的数据。
MySQL是一个**关系型数据库管理系统**,由瑞典[MySQL AB](https://baike.baidu.com/item/MySQL AB/2620844) 公司开发,属于 Oracle 旗下产品。
MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一。
MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。
官网:https://www.mysql.com/
下载地址:https://dev.mysql.com/downloads/mysql/
安装建议:尽量使用压缩包安装,使用 exe 安装会导致 MySQL 进入注册表,导致卸载问题。
2. 安装 MySQL
(1) 解压压缩包到环境目录下
(2) 配置环境变量
(3) 新建 MySQL 配置文件 my.ini
[mysqld]
basedir=mysql的压缩目录
datadir=数据存放目录
port=3306
# 第一次登录MySQL,跳过密码验证,MySQL8 不需要
skip-grant-tables
(4) 管理员模式下启动 cmd,运行命令。
(5) 安装 MySQL 服务
mysqld -install
(6) 初始化数据库文件
mysqld --initialize-insecure --user=mysql
(7) 启动并进入 MySQL
# 启动 mysql 服务
net start mysql
# 第一次登录,密码可为空
mysql -u root -p
(8) 修改密码为 123456
update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';
(9) 注释掉配置文件跳过密码验证的代码,MySQL8 略。
(10) 重启 MySQL 服务,连接测试。
net stop mysql
net start mysql
mysql -u root -p123456
3. 安装和使用 SQLyog
安装 SQLyog
https://blog.csdn.net/wangpeng1201/article/details/113058809
使用 SQLyog
新建数据库
新建表
查看表,添加一条数据
4. 命令行操作
连接数据库
mysql -u root -p
在此页面下即可运行 sql 语句。
修改用户密码
# password content 为密码,修改密码需要管理员身份
update mysql.user set authentication_string=password('password content') where user='root' and Host='localhost';
刷新权限
flush privileges;
5. 操作数据库
查看数据库
show database;
创建数据库
-- []内容可选
create database [if not exists] 数据库名;
删除数据库
-- []内容可选
drop database [if exists] 数据库名;
切换数据库
use 数据库名;
查看所有表
show tables;
查看表数据
describe 表名;
数据库引擎
MYISAM | INNODB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间 | 较小 | 较大,约为 MYISAM 2倍 |
存储文件 | *.frm(表结构文件),*.MYD(表数据文件),*.MYI(索引文件) | *.frm,*.ibd |
6. 字段类型
数值类型
类型 | 大小 | 说明 |
---|---|---|
tinyint | 1 字节 | 小的整数 |
smallint | 2 字节 | 较小的整数 |
mediumint | 3 字节 | 中等大小的整数 |
int | 4 字节 | 标准整数 |
bigint | 8 字节 | 大的整数 |
float | 4 字节 | 单精度浮点数 |
double | 8 字节 | 双精度浮点数 |
decimal | 无限大 | 字符串形式的浮点数,用于高精度计算 |
字符类型
类型 | 大小 | 说明 |
---|---|---|
char | 0~255 | 大小固定 |
varchar | 0~65535 | 大小可变 |
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.1.1 至现在的毫秒数 | |
year | 年份 |
yyyy-MM-dd HH:mm:ss 年-月-日 时:分:秒,M 是“ 月 ”,m 是“ 分 ” ,h 是12小时制,H 是24小时制。
null
没有值或未知,不要用于计算,无意义
7. 字段属性
属性 | 说明 |
---|---|
UNSIGNED | 无符号整数,该列不能为负数。 |
ZEROFILL | 零填充。 |
AUTO_INCREMENT | 自增,整数类型自动增加值大小,初始值和步长可自定义 |
NULL 或 NOT NULL | 字段值是否可以为空,默认为 NULL。 |
DEFAULT | 默认值,设置字段默认值。 |
实际项目中,每个表都必须存在下面几个字段:
属性 | 说明 |
---|---|
id | 主键 |
version | 乐观锁 |
is_delete | 伪删除 |
gmt_create | 记录创建时间 |
gmt_modified | 记录修改时间 |
8. 操作表结构
创建表
/*[]内容可省略建表内容最后一行省略','
*/
CREATE TABLE [IF NOT EXISTS] `表名` (`字段名` 列类型 [属性] [索引] [注释],...`字段名` 列类型 [属性] [索引] [注释],PRIMARY KEY(`id`)[KEY `外键名`(`外键字段`)][CONSTRAINT `外键名` FOREIGN KEY(`外键名`) REFERENCES 主表表名(`主表主键字段名`)]
)[引擎] [字符集] [注释];
CREATE TABLE IF NOT EXISTS `student` (`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',`birthday` DATE DEFAULT NULL COMMENT '出生日期',`address` VARCHAR(100) DEFAULT NULL COMMENT '邮箱',PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
查看表结构
DESC 表名;
DESC student;
查看建表语句
SHOW CREATE TABLE 表名;
SHOW CREATE TABLE student;
修改表名
ALTER TABLE 原表名 RENAME AS 新表名;
增加表字段
ALTER TABLE 表名 ADD 字段名 类型 [属性];
修改表字段
-- 修改字段约束,不可修改字段名
ALTER TABLE 表名 MODIFY 字段名 字段类型 [属性];
-- 修改字段名,也可修改约束
ALTER TABLE 表名 CHANGE 原字段名 新字段名 字段类型 [属性] ;
删除字段
ALTER TABLE 表名 DROP 字段名;
删除表
DROP TABLE [IF EXISTS] 表名;
外键
外键是本表中的其他表的主键。
主表是被参考的表,从表是外键所在表,从表数据依赖于主表。
设置外键的条件:
- 要设置外键的字段不能为本表(从表)主键。
- 外键所参考的字段必须为(主表)主键。
- 外键和所参考的字段必须具有相同的数据类型和约束。
添加外键约束
ALTER TABLE 从表表名
ADD CONSTRAINT `从表外键名` FOREIGN KEY(`从表外键字段名`) REFERENCES 主表(`主表主键字段名`);
ALTER TABLE student
ADD CONSTRAINT `FK_GADEID` FOREIGN KEY(`gadeid`) REFERENCES gade(`id`);
阿里规范禁止数据库级别的级联外键,要求在程序逻辑构建表关系。
9. 操作表内容
DML
INSERT
-- []内容可省略
INSERT INTO 表名[(`字段1`,`字段2`,...)]
VALUES('值1','值2',...),('值1','值2',...),...;
INSERT INTO student(`name`,`pwd`,`sex`,`birthday`,`address`,`gadeid`)
VALUES('赵一','111111','男','1111-1-1','111111@qq.com',1),('钱二','222222','女','2222-2-2','222222@qq.com',2);
字段与值必须一一对应,字段可省略但不建议,一般只会省略自增属性的字段。
UPDATE
-- 值可为数据,也可为变量,表达式或函数
-- 若无条件语句,更新作用于所有记录
UPDATE 表名 SET `字段1`=值1,[`字段2`=值2,...]
[WHERE 条件];
WHERE
操作符 | 说明 |
---|---|
= | 等于 |
<> 或 != | 不等于 |
> | 大于 |
< | 小于 |
>= | 大于等于 |
<= | 小于等于 |
BETWEEN a AND b | [a,b] 区间 |
AND | 与,都为真才为真 |
OR | 或,都为假才为假 |
DELETE
删除表记录,不影响自增字段增量
-- 无条件语句删除所有表记录
DELETE FROM 表名
[WHERE 条件];
TRUNCATE
删除表记录,自增字段增量归零,不影响事务。
TRUNCATE 表名
DQL
建库语句
DROP DATABASE IF EXISTS `school`;
-- 创建一个school数据库
CREATE DATABASE IF NOT EXISTS `school`;
-- 使用school数据库
USE `school`;
-- 创建学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(`student_no` INT(4) NOT NULL COMMENT '学号',`login_pwd` VARCHAR(20) DEFAULT NULL,`student_name` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',`sex` TINYINT(1) DEFAULT NULL COMMENT '性别,0或1',`grade_id` INT(11) DEFAULT NULL COMMENT '年级编号',`phone` VARCHAR(50) NOT NULL COMMENT '联系电话',`address` VARCHAR(255) NOT NULL COMMENT '地址',`born_date` DATETIME DEFAULT NULL COMMENT '出生时间',`email` VARCHAR (50) NOT NULL COMMENT '邮箱账号',`identity_card` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',PRIMARY KEY (`student_no`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;-- 创建年级表
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(`grade_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',`grade_name` VARCHAR(50) NOT NULL COMMENT '年级名称',PRIMARY KEY (`grade_id`)
) ENGINE=INNODB DEFAULT CHARSET = utf8;-- 创建科目表
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(`subject_no`INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',`subject_name` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',`class_hour` INT(4) DEFAULT NULL COMMENT '学时',`grade_id` INT(4) DEFAULT NULL COMMENT '年级编号',PRIMARY KEY (`subject_no`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;-- 创建成绩表
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(`student_no` INT(4) NOT NULL COMMENT '学号',`subject_no` INT(4) NOT NULL COMMENT '课程编号',`exam_date` DATETIME NOT NULL COMMENT '考试日期',`student_result` INT (4) NOT NULL COMMENT '考试成绩')ENGINE = INNODB DEFAULT CHARSET = utf8;-- 插入学生数据 其余自行添加 这里只添加了2行
INSERT INTO `student` (`student_no`,`login_pwd`,`student_name`,`sex`,`grade_id`,`phone`,`address`,`born_date`,`email`,`identity_card`)
VALUES
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233');-- 插入年级数据
INSERT INTO `grade` (`grade_id`,`grade_name`) VALUES(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');-- 插入科目数据
INSERT INTO `subject`(`subject_no`,`subject_name`,`class_hour`,`grade_id`)VALUES
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);-- 插入成绩数据 这里仅插入了一组,其余自行添加
INSERT INTO `result`(`student_no`,`subject_no`,`exam_date`,`student_result`)
VALUES
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);
SELECT 语法
-- {}必选,[]可选
SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]-- 联合查询[left | right | inner join table_name2]-- 指定结果需满足的条件[WHERE ...] -- 指定结果按照哪几个字段来分组[GROUP BY ...] -- 过滤分组的记录必须满足的次要条件[HAVING] -- 指定查询记录按一个或多个条件排序[ORDER BY ...] -- 指定查询的记录从哪条至哪条[LIMIT {[offset,]row_count | row_countOFFSET offset}];
-- 查询全部的学生 SELECT 字段 FROM 表名;SELECT * FROM student;-- 查询指定字段SELECT student_name, student_no FROM student;-- 别名,给结果起一个名字 AS 可以给字段起别名,也可以给表起别名SELECT student_name AS '学号', student_no AS '姓名' FROM student;-- 函数 concat(a,b)SELECT CONCAT('姓名:', student_no) AS '新姓名' FROM student;
去重及数据库表达式
DISTINCT
-- 查询全部的考试成绩SELECT * FROM result;-- 查询有哪些同学参加了考试SELECT `student_no` FROM result;-- 发现重复数据,去重SELECT DISTINCT `student_no` FROM result;
表达式
-- 查询系统版本(函数)SELECT VERSION();-- 用来计算(表达式)SELECT 100*3 -1 ;-- 查询自增的步长(变量)SELECT @@auto_increment_increment;-- 学员考试成绩 +1 查看SELECT `student_no`,`student_result` + 1 AS '提分后' FROM result;
数据库中的表达式: 文本值,列,Null,函数,计算表达式,系统变量
select 表达式
from 表名
条件查询
条件查询语句由 SELECT
+ WHERE
+ 条件表达式构成,表达式的结果为布尔值。
逻辑运算符
运算符 | 语法 | 描述 |
---|---|---|
and 或 && | a and b 或 a&&b | 逻辑与 |
or 或 ` | ` | |
not 或 ! | not a 或 !a | 逻辑非 |
-- and
SELECT student_no,student_result FROM result
WHERE student_result>95 AND student_result<=100;
-- &&
SELECT student_no,student_result FROM result
WHERE student_result>95 && student_result<=100;-- BETWEEN AND
SELECT student_no,student_result FROM result
WHERE student_result BETWEEN 95 AND 100;-- !=
SELECT student_no,student_result FROM result
WHERE student_no != 1000;
-- not
SELECT student_no,student_result FROM result
WHERE NOT student_no = 1000;
模糊查询
运算符 | 语法 | 描述 |
---|---|---|
IS NULL | a is null | 如果操作符为null,结果为真 |
IS NOT NULL | a is not null | 如果操作符不为null,结果为真 |
BWTWEEN...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到任意个字符)
-- _(代表1)-- 查询姓刘的同学
SELECT `student_no`,`student_name` FROM `student`
WHERE student_name LIKE '刘%';-- 查询姓刘的同学,名字后面只有一个字的
SELECT `student_no`,`student_name` FROM `student`
WHERE student_name LIKE '刘_';-- 查询姓刘的同学,名字后面有两个字的
SELECT `student_no`,`student_name` FROM `student`
WHERE student_name LIKE '刘__';-- 查询名字中间有嘉字的同学
SELECT `student_no`,`student_name` FROM `student`
WHERE student_name LIKE '%%嘉%';-- in (具体的一个或者多个值)
-- 查询学号1001,1002,1003号学号
SELECT `student_no`,`student_name` FROM `student`
WHERE student_no IN ('1001','1002','1003');
-- 查询在北京的学生
SELECT `student_no`,`student_name` FROM `student`
WHERE address IN ('北京');-- null
-- 查询地址为空的学生
SELECT `student_no`,`student_name` FROM `student`
WHERE address = '' OR address IS NULL;-- not null
-- 查询有出生日期的同学 不为空
SELECT `student_no`,`student_name` FROM `student`
WHERE born_date IS NOT NULL;-- 查询没有出生日期的同学 为空
SELECT `student_no`,`student_name` FROM `student`
WHERE born_date IS NULL;
联表查询
-- =========联表查询==================-- 查询参加了考试的同学(学号,姓名,科目编号,分数)
SELECT * FROM student;
SELECT * FROM result;-- join on 连接查询
-- where 等值查询-- inner join
SELECT st.`student_no`,st.`student_name`,re.`subject_no`,re.`student_result`
FROM student AS st
INNER JOIN result AS re ON
st.`student_no`=re.`student_no`;-- right join
SELECT st.`student_no`,st.`student_name`,re.`subject_no`,re.`student_result`
FROM student st
RIGHT JOIN result re ON
st.`student_no`=re.`student_no`;-- left join
SELECT st.`student_no`,st.`student_name`,re.`subject_no`,re.`student_result`
FROM student st
LEFT JOIN result re ON
st.`student_no`=re.`student_no`;-- 查询缺考的同学
SELECT st.`student_no`,st.`student_name`,re.`subject_no`,re.`student_result`
FROM student st
LEFT JOIN result re ON
st.`student_no`=re.`student_no`
WHERE re.`student_result` IS NULL;-- 查询了参加考试的同学信息(学号,学生姓名,科目名称,分数)
SELECT stu.`student_no`,stu.`student_name`,sub.`subject_name`,res.`student_result`
FROM `student` stu
RIGHT JOIN `result` res
ON res.`student_no`=stu.`student_no`
INNER JOIN `subject` sub
ON res.`subject_no`=sub.`subject_no`;-- 查询学员所属的年级(学号,学生的姓名,年级名称)
SELECT `student_no`,`student_name`,`grade_name`
FROM student stu
INNER JOIN `grade` gra
ON stu.`grade_id`=gra.`grade_id`;-- 查询了参加数据结构-1考试的同学信息(学号,学生姓名,科目名称,分数)
SELECT stu.`student_no`,stu.`student_name`,sub.`subject_name`,res.`student_result`
FROM student stu
INNER JOIN `result` res
ON stu.`student_no` = res.`student_no`
INNER JOIN `subject` sub
ON res.`subject_no`=sub.`subject_no`
WHERE sub.`subject_name`='数据结构-1';-- 我要查询哪些数据 select ...
-- 从哪几个表中查 from 表 XXX join 连接的表 on 交叉条件
-- 假设存在一种多张表查询,慢慢来,先查询两张表然后再慢慢增加
操作 | 描述 |
---|---|
inner join | 如果表中至少有一个匹配,就返回行 |
left join | 会从左边中返回所有的值,即使右表中没有匹配 |
right join | 会从右边中返回所有的值,即使左表中没有匹配 |
自连接
自己和自己连接,核心:一张表拆为两张一样的表
-- 创建表
-- unsigned 无符号
-- auto_increment=9 自增的起始值
DROP TABLE IF EXISTS `category` ;
CREATE TABLE `category` (`category_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',`pid` INT(10) NOT NULL COMMENT '父id',`category_name` VARCHAR(50) NOT NULL COMMENT '主题名字',PRIMARY KEY (`category_id`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;-- 插入值
INSERT INTO `category`(`category_id`,`pid`,`category_name`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');SELECT * FROM `category`;
category_id | pid | category_name |
---|---|---|
2 | 1 | 信息技术 |
3 | 1 | 软件开发 |
4 | 3 | 数据库 |
5 | 1 | 美术设计 |
6 | 3 | web开发 |
7 | 5 | ps技术 |
8 | 2 | 办公信息 |
父类 |
pid | category_name |
---|---|
1 | 信息技术 |
1 | 软件开发 |
3 | 数据库 |
1 | 美术设计 |
3 | web开发 |
5 | ps技术 |
2 | 办公信息 |
子类
category_id | category_name |
---|---|
2 | 信息技术 |
3 | 软件开发 |
4 | 数据库 |
5 | 美术设计 |
6 | web开发 |
7 | ps技术 |
8 | 办公信息 |
操作:查询父类对应的子类关系
父类 | 子类 |
---|---|
信息技术 | 办公信息 |
软件开发 | 数据库 |
软件开发 | web开发 |
美术设计 | ps技术 |
-- 查询父子信息,把一张表看为两个一模一样的表
SELECT a.`category_name` AS '父栏目',b.`category_name` AS '子栏目'
FROM `category` AS a, `category` AS b
WHERE a.`category_id`=b.`pid`;
分页和排序
排序
-- 排序: 升序 ASC 降序 DESC
-- 语法:ORDER BY 通过那个字段排序,怎么排
-- 查询的结果根据成绩降序 排序
SELECT stu.`student_no`,stu.`student_name`,sub.`subject_name`,res.`student_result`
FROM student stu
INNER JOIN `result` res
ON stu.`student_no` = res.`student_no`
INNER JOIN `subject` sub
ON res.`subject_no`=sub.`subject_no`
WHERE sub.`subject_name`='数据结构-1'
ORDER BY `student_result` DESC;
分页
-- 100w
-- 为什么要分页
-- 缓解数据库压力,给人更好的体验 瀑布流
-- 分页,每页只显示五条数据
-- 语法 : limit 起始值,页面的大小
-- 网页应用:当前,总的页数,每页大小
-- LIMIT 0,5 1~5
-- LIMIT 1,5 2~6
-- LIMIT 6,5
SELECT stu.`student_no`,stu.`student_name`,sub.`subject_name`,res.`student_result`
FROM student stu
INNER JOIN `result` res
ON stu.`student_no` = res.`student_no`
INNER JOIN `subject` sub
ON res.`subject_no`=sub.`subject_no`
WHERE sub.`subject_name`='数据结构-1'
ORDER BY `student_result` DESC
LIMIT 1,5;
-- 第一页 limit 0,5 (1-1)*5
-- 第二页 limit 5,5 (2-1)*5
-- 第三页 limit 10,5 (3-1)*5
-- 第N页 limit 10,5 (n-1)*pageSize,pageSize
-- pageSize,页面大小
-- (n-1)*pageSize,起始值
-- n,当前页
-- 总页数 = (数据总数%页面大小==0)? (数据总数/页面大小) : (数据总数/页面大小 + 1)-- 查询科目高等数学-2,课程成绩排名前十的学生,并且分数要大于60的学生信息(学号,姓名,课程名称,分数)
SELECT stu.`student_no`,stu.`student_name`,sub.`subject_name`,res.`student_result`
FROM student stu
INNER JOIN `subject` sub
ON stu.`grade_id`=sub.`grade_id`
INNER JOIN `result` res
ON sub.`subject_no`=res.`subject_no`
WHERE sub.`subject_name`='高等数学-2'
AND res.`student_result`>60
ORDER BY res.`student_result`
LIMIT 0,10;
子查询和嵌套查询
where(这个值是计算出来的)
本质:在where语句中嵌套一个子查询语句
-- 1.查询数据库结构-1的所有考试结果(学号,科目名,成绩),降序排列
-- 方式1:使用连接查询
SELECT res.`student_no`,res.`subject_no`,res.`student_result`
FROM `result` res
INNER JOIN `subject` sub
ON res.`subject_no`=sub.`subject_no`
WHERE sub.`subject_name`='高等数学-2'
ORDER BY res.`student_result` DESC;-- 使用子查询(由里及外)
SELECT res.`student_no`,res.`subject_no`,res.`student_result`
FROM `result` res
WHERE res.`subject_no` = (SELECT sub.`subject_no`FROM `subject` subWHERE sub.`subject_name`='高等数学-2'
)
ORDER BY res.`student_result` DESC;-- 分数不小于80分的学生的学号和姓名
SELECT DISTINCT stu.`student_no`,stu.`student_name`
FROM student stu
INNER JOIN result res
ON stu.`student_no`=res.`student_no`
WHERE res.`student_result` > 80;-- 在这个基础上增加一个科目,查询课程为高等数学-2,且分数不小于80分的学生的学号和姓名
SELECT DISTINCT stu.`student_no`,stu.`student_name`
FROM student stu
INNER JOIN result res
ON stu.`student_no`=res.`student_no`
WHERE res.`student_result` > 80
AND res.`subject_no`=(
SELECT sub.`subject_no` FROM `subject` sub
WHERE sub.`subject_name`='高等数学-2'
);SELECT DISTINCT stu.`student_no`,stu.`student_name`
FROM student stu
INNER JOIN result res
ON stu.`student_no`=res.`student_no`
INNER JOIN `subject` sub
ON res.`subject_no`=sub.`subject_no`
WHERE sub.`subject_name`='高等数学-2'
AND res.`student_result` > 80;-- 再次改造(由里及外)
SELECT DISTINCT `student_no`,`student_name` FROM student WHERE student_no IN (SELECT student_no FROM result WHERE `student_result` > 80 AND subject_no = (SELECT subject_no FROM `subject` WHERE `subject_name`='高等数学-2')
);
10.MySQL函数
官网:参考手册
常用函数
SELECT ABS(-8); -- 绝对值
SELECT CEILING(9.4) ;-- 向上取整
SELECT FLOOR(9.4);-- 向下取整
SELECT RAND(); -- 返回一个0~1之间的随机数
SELECT SIGN(-10); -- 判断一个数的符号,0 返回0 负数返回-1 正数返回1-- 字符串函数
SELECT CHAR_LENGTH('哈哈'); -- 字符串长度
SELECT CONCAT('我','爱','你'); -- 拼接字符串
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱'); -- 插入,替换
SELECT LOWER('ZYY'); -- 小写字母
SELECT UPPER('zyy'); -- 大写字母
SELECT INSTR('zyy','y'); -- 返回第一次出现的子串的索引
SELECT REPLACE('坚持就能成功','坚持','努力'); -- 替换出现的指定字符串
SELECT SUBSTR('坚持就能成功', 5, 2); -- 返回指定的子字符串(源字符串,截取的位置,截取的长度)
SELECT REVERSE('清晨我上马'); -- 反转-- 时间和日期函数(记住!)
SELECT CURRENT_DATE(); -- 获取当前日期
SELECT CURDATE(); -- 获取当前日期
SELECT NOW(); -- 获取当前的时间
SELECT LOCALTIME(); -- 获取本地时间
SELECT SYSDATE(); -- 获取系统时间SELECT YEAR(NOW()); -- 年
SELECT MONTH(NOW()); -- 月
SELECT DAY(NOW()); -- 日
SELECT HOUR(NOW()); -- 时
SELECT MINUTE(NOW()); -- 分
SELECT SECOND(NOW()); -- 秒-- 系统
SELECT SYSTEM_USER();
SELECT USER();
SELECT VERSION();
聚合函数
函数名称 | 描述 |
---|---|
count() | 计数 |
sum() | 求和 |
avg() | 平均值 |
max() | 最大值 |
min() | 最小值 |
-- 聚合函数
-- 都能统计 表中数据-- count(字段) 会忽略所有的null值(想查询一个表中有多少个记录,就使用这个count())
SELECT COUNT(student_name) FROM student;
-- COUNT(*) 不会忽略所有的null值 本质计算行数
SELECT COUNT(*) FROM student;
-- COUNT(1) 不会忽略所有的null值 本质计算行数
SELECT COUNT(1) FROM student;SELECT SUM(student_result) AS '总和' FROM result;
SELECT AVG(student_result) AS '平均分' FROM result;
SELECT MAX(student_result) AS '最高分' FROM result;
SELECT MIN(student_result) AS '最低分' FROM result;-- 查询不同课程的平均分,最高分,最低分
SELECT sub.subject_name AS '课程',
AVG(res.student_result) AS '平均分',
MAX(res.student_result) AS '最高分',
MIN(res.student_result) AS '最低分'
FROM result res
INNER JOIN `subject` sub
ON res.`subject_no`=sub.`subject_no`
GROUP BY res.`subject_no`
HAVING AVG(res.student_result) >80;
md5加密
什么是MD5?
主要增加算法复杂度和不可逆性。
MD5不可逆,具体的值的md5是一样的
MD5破解网站的原理,背后有一个字典,MD5加密后的值,加密前的值
CREATE TABLE `testmd5`(`id` INT(4) NOT NULL,`name` VARCHAR(20) NOT NULL,`pwd` VARCHAR(50) NOT NULL,PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;-- 明文密码
INSERT INTO `testmd5`(`id`,`name`,`pwd`)
VALUES
(1,'张三','123456'),
(2,'李四','123456'),
(3,'王五','123456');SELECT * FROM `testmd5`;-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id=2;-- 插入的时候加密
INSERT INTO `testmd5`(`id`,`name`,`pwd`)
VALUES
(4,'小明',MD5('123456'));-- 如何校验,将用户传递进来的密码,进行MD5加密,然后比对加密后的值SELECT * FROM `testmd5` WHERE `name`='小明' AND pwd = MD5('123456');
11. 事务
参考博客链接:事务ACID理解
ACID
原子性(Atomicity)
要么都成功,要么都失败
一致性(Consistency)
事务前后的数据完整性要保持一致
下图操作前和操作后的总和都是1000
隔离性(Isolation)
事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。
持久性(Durability)
事务一旦移交不可逆,被持久化到数据库中
隔离所导致的一些问题
脏读:
1、在事务A执行过程中,事务A对数据资源进行了修改,事务B读取了事务A修改后的数据。
2、由于某些原因,事务A并没有完成提交,发生了RollBack操作,则事务B读取的数据就是脏数据。
这种读取到另一个事务未提交的数据的现象就是脏读(Dirty Read)。
不可重复读:
事务B读取了两次数据资源,在这两次读取的过程中事务A修改了数据,导致事务B在这两次读取出来的数据不一致。
这种**在同一个事务中,前后两次读取的数据不一致的现象就是不可重复读(Nonrepeatable Read)。**
虚读(幻读)
事务B前后两次读取同一个范围的数据,在事务B两次读取的过程中事务A新增了数据,导致事务B后一次读取到前一次查询没有看到的行。
幻读和不可重复读有些类似,但是幻读强调的是集合的增减,而不是单条数据的更新。
第一类更新丢失
事务A和事务B都对数据进行更新,但是事务A由于某种原因事务回滚了,把已经提交的事务B的更新数据给覆盖了。这种现象就是第一类更新丢失。
第二类更新丢失
其实跟第一类更新丢失有点类似,也是两个事务同时对数据进行更新,但是事务A的更新把已提交的事务B的更新数据给覆盖了。这种现象就是第二类更新丢失。
事务隔离级别
为了解决以上的问题,主流的关系型数据库都会提供四种事务的隔离级别。事务隔离级别从低到高分别是:读未提交,读已提交,可重复读,串行化。事务隔离级别越高,越能保证数据的一致性和完整性,但是执行效率也越低,所以在设置数据库的事务隔离级别时需要做一下权衡,mysql默认是可重复读
读未提交
读未提交(Read Uncommitted),是最低的隔离级别,**所有的事务都可以看到其他未提交的事务的执行结果。**只能防止第一类更新丢失,不能解决脏读,可重复读,幻读,所以很少应用于实际项目。
读已提交
读已提交(Read Committed),在该隔离级别下,**一个事务的更新操作只有在该事务提交之后,另外一个事务才可能读取到同一笔数据更新后的结果。**可以防止脏读和第一类更新丢失,但是不能解决可重复和幻读的问题。
可重复读(重要)
可重复读(Repeatable Read),mysql默认的隔离级别。在该隔离级别下,一个事务多次读同一个数据,在这个事务还没有结束时,其他事务不能访问该数据(包括了读写),这样就可以在同一个事务内两次读到的数据是一样的。可以防止脏读、不可重复读、第一类更新丢失,第二类更新丢失的问题,不过还是会出现幻读。
串行化
串行化(Serializable),这是最高的隔离级别。它要求事务序列化执行,事务只能一个接着一个的执行,不能并发执行。在这个级别,可以解决上面提到的所有并发问题,但是可能导致大量的超时现象和锁竞争,通常不会用这个隔离级别。
总结
扩展:回滚机制
在mysql中,恢复机制是通过回滚日志(undo log)实现的,所有的事务进行的修改都会先记录到这个回滚日志中,然后在堆数据库中的对应进行写入。
mysql的事务是由redo和undo的,redo操作的所有信息都是记录到重做日志(redo_log)中,也就是说当一个事务做commit操作时,需要先把这个事务的操作写到redo_log中,然后在把这些操作flush到磁盘上,当出现故障时,只需要读取redo_log,然后在重新flush到磁盘就行了。
而对于undo就比较麻烦,mysql在处理事务时,会在数据共享表空间里申请一个段就做segment段,用保存undo信息,当在处理rollback,不是完完全全的物理undo,而是逻辑undo,也就是说会之前的操作进行反操作(对于每个insert,回滚时会执行delete;对于每个delete,回滚时会执行insert;对于每个update,回滚时会执行一个相反的update,把数据改回去。),但是这些共享表空间是不进行回收的。这些表空间的回收需要由mysql的master thread进程进行回收。
测试
事务执行流程
-- 事务
-- mysql 是默认开启事务自动提交-- 手动处理事务
SET autocommit = 0; -- 关闭自动提交
-- 事务开启
START TRANSACTION; -- 标记一个事务的开始,从这个之后的sql都在同一个事务内INSERT XX
INSERT XX-- 提交 : 持久化
COMMIT;
-- 回滚 : 回到的原来的样子(失败)
ROLLBACK;
-- 事务结束
SET autocommit = 1; -- 开启自动提交
-- 了解
SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点
模拟转账
-- 转账-- 创建数据库
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci;-- 使用shop数据库
USER `shop`;-- 建表
CREATE TABLE `account`(`id` INT(3) NOT NULL AUTO_INCREMENT,`name` VARCHAR(100) NOT NULL,`money` DECIMAL(9,2) NOT NULL,PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;-- 初始化数据
INSERT INTO account(`name`,`money`)
VALUES('A',2000.00),
('B',10000.00);-- 模拟转账
SET autocommit = 0; -- 关闭自动提交START TRANSACTION; -- 开启事务 (一组事务)UPDATE account SET `money`=`money`-500 WHERE `name`='A'; -- A减500
UPDATE account SET `money`=`money`+500 WHERE `name`='B'; -- B加500COMMIT; -- 提交事务,就会被持久化了ROLLBACK; -- 回滚SET autocommit = 1; -- 恢复自动提交
12. 索引
简介
CodingLabs - MySQL索引背后的数据结构及算法原理
Msql官方对索引的定义为:索引(index)是帮助MySQL高效获取数据的数据结构。
提取句子主干,就可以得到索引的本质:索引是数据结构。
索引的分类
在一个表中,主键索引只能有一个,唯一索引可以有多个
- 主键索引(primary key)
- 唯一的标识,主键不可重复,只能有一个列作为主键
- 唯一索引 (unique key)
- 避免重复的列出现,可以重复,多个列都可以标示为唯一索引
- 常规索引(key/index)
- 默认的 index 或者key关键字来设置
- 全文索引(FullText)
- 在特定的数据库引擎下才有,myisam
- 快速定位数据
基础语法
-- 索引的使用-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后,增加索引-- 显示所有的索引信息
SHOW INDEX FROM student;-- 新增一个索引 (索引名) 列名ALTER TABLE `student` ADD UNIQUE KEY `UK_IDENTITY_CARD` (`identity_card`);
ALTER TABLE `student` ADD KEY `K_STUDENT_NAME`(`student_name`);ALTER TABLE `student` ADD FULLTEXT INDEX `FI_PHONE` (`phone`);-- explain 分析sql执行的状况EXPLAIN SELECT * FROM student; -- 非全文索引EXPLAIN SELECT * FROM student WHERE MATCH(`phone`) AGAINST('138'); -- 全文索引
【MySQL优化】——看懂explain_漫漫长途,终有回转;余味苦涩,终有回甘-CSDN博客_explain
测试索引
创建数据
CREATE TABLE app_user (`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',`name` VARCHAR(50) DEFAULT '' COMMENT '用户昵称',`email` VARCHAR(50) NOT NULL COMMENT '用户邮箱',`phone` VARCHAR(20) DEFAULT '' COMMENT '手机号',`gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别(0:男 1:女)',`password` VARCHAR(100) NOT NULL COMMENT '密码',`age` TINYINT(4) DEFAULT '0' COMMENT '年龄',`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',`update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'-- 查看创建函数的功能是否开启
show variables like '%func%';-- 开启创建函数的功能
set global log_bin_trust_function_creators=1;-- 插入100万数据b (函数)DELIMITER $$ -- 写函数之前必须要写,标志
CREATE FUNCTION mock_data()
RETURNS INT
BEGINDECLARE num INT DEFAULT 1000000;DECLARE i INT DEFAULT 0;WHILE i<num DOINSERT INTO school.app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('用户',i),'123345@qq.com',CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));SET i = i+1;END WHILE;RETURN i;
END;-- 执行函数
SELECT mock_data();SELECT * FROM app_user;
测试
-- 加索引前
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.440 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';-- 创建索引
-- id_表名_字段名 索引名
-- CREATE INDEX 索引名 ON 表名(`字段名`);
CREATE INDEX id_app_user_name ON app_user(`name`);-- 加索引后
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.002 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';
索引在小数据量的时候,用处不大,但是再大数据的时候,区分十分明显
创建索引的原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引一般加载常用来查询的字段上
索引的数据结构
Hash类型的索引
bree :innodb的默认数据结构
CodingLabs - MySQL索引背后的数据结构及算法原理
13. 权限管理
sql yog 可视化管理
sql 命令
用户表:mysql.user
本质:读这张表进行增删改查
-- 创建用户
CREATE USER zyy IDENTIFIED BY '123456';-- 修改密码(修改当前用户密码)
SET PASSWORD = PASSWORD('123456');-- 修改密码(修改指定用户密码)
SET PASSWORD FOR zyy = PASSWORD('123456');-- 重命名 RENAME 原名子 zyy TO 新名字;
RENAME USER zyy TO newzyy;-- 用户授权 ALL PRIVILEGES 全部的权限,库,表-- ALL PRIVILEGES 除了给别人授权不行,其他都能干GRANT ALL PRIVILEGES ON *.* TO newzyy;-- 查询权限SHOW GRANTS FOR newzyy; -- 查看指定用户的权限SHOW GRANTS FOR root@localhost; -- 查看root用户的权限-- 撤销权限 REVOKE哪些权限,在哪个库,给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM newzyy;-- 删除用户
DROP USER newzyy;
14. MySQL备份
为什么要备份?
- 保证重要的数据不丢失
- 数据转移
mysql数据库备份的方式
-
直接拷贝物理文件
-
在sqlyog这种可视化工具中手动导出
-
在想要导出的表或者库中,右键,
-
-
使用命令行导出 mysqldump 命令行使用
# 一张表 mysqldump -h主机 -u用户名 -p密码 数据库 表名 >物理磁盘位置/文件名 mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql# 多张表 mysqldump -h主机 -u用户名 -p密码 数据库 表名1 表名2 >物理磁盘位置/文件名 mysqldump -hlocalhost -uroot -p123456 school student result >D:/a.sql# 数据库 mysqldump -h主机 -u用户名 -p密码 数据库 >物理磁盘位置/文件名 mysqldump -hlocalhost -uroot -p123456 school >D:/a.sql# 导入 # 登录的情况下,切换到指定的数据库 mysql -uroot -p123456 use school source D:/a.sql # source 备份文件 # 也可以这样 mysql -u用户名 -p密码 库名<备份文件
假设你要备份数据库,防止数据丢失。
把数据库给别人,直接给sql即可。
15. 规范数据库设计
如何设计一个项目的数据库
为什么需要设计
当数据库比较复杂的时候,我们就需要设计了
糟糕的数据库设计
- 数据冗余,浪费空间
- 数据库插入和删除都会麻烦、异常(屏蔽使用物理外键)
- 程序的性能差
良好的数据库设计
- 节省内存空间
- 保证数据库的完整性
- 方便我们开发系统
软件开发中,关于数据库的设计
- 分析需求,分析业务和需要处理的数据库的需求
- 概要设计:设计关系图E-R图
设计数据库的步骤(个人博客)
-
收集信息,分析需求
-
用户表(用户登录注销,用户的个人信息,写博客,创建分类)
-
分类表(文章分类,谁创建的)
-
文章表(文章信息)
-
评论表
-
友链表(友情链接信息)
-
自定义表(系统信息,某个关键的字,或者一些主字段)
key:value
-
关注表(粉丝数)
-
说说表(发表心情, id…content…create_time)
-
-
标识实体(把需求落到每个字段)
-
标识实体之间的关系
- 写博客:user --> blog
- 创建分类:user --> category
- 关注:user --> user
- 友链:links
- 评论:user --> user --> blog
(bbs / crm)
数据库三大范式(了解)
为什么需要数据规范化?
- 信息重复
- 更新异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 丢失有效的信息
三大范式
第一范式(1NF)
原子性:保证每一列不可再分
第二范式(2NF)
前提:满足第一范式
第二范式需要确保数据库表中的每一列都和主键相关,而不能只与主键的某一部分相关(主要针对联合主键而言)。
每张表只描述一件事情
第三范式(3NF)
前提:满足第一范式和第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。
规范数据库的设计
规范性和性能的问题
关联查询的表不得超过三张表
- 考虑商业化的需求和目标(成本,用户体验)数据库的性能更加重要
- 在规范性能的问题的时候,需要适当的考虑一下规范性
- 故意给某些表增加一些冗余的字段。(从多表查询中变为单表查询)
- 故意增加一些计算列(从大数据库降低为小数据量的查询:索引)
16. 数据库驱动和JDBC
数据库驱动
驱动:声卡,显卡,数据库
我们的程序会通过数据库驱动,和数据库打交道!
JDBC
SUN公司为了简化开发人员的(对数据库的统一)操作,提供了一个(java操作数据库的)规范,俗称JDBC
这些规范的实现由具体的厂商去做~
对于开发人员来说,我们只需要掌握JDBC接口的操作即可!
java.sql
javax.sql
还需要导入一个数据库驱动包 mysql-connector-java-5.1.47.jar
17. 第一个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,'张三','123456','zs@sina.com','1980-12-04'),
(2,'李四','123456','lisi@sina.com','1981-12-04'),
(3,'王五','123456','wangwu@sina.com','1982-12-04');
创建一个普通项目
导入数据库驱动(jar包)
编写测试代码
package com.why.jdbc;import java.sql.*;public class JdbcDemo01 {public static void main(String[] args) throws ClassNotFoundException, SQLException, SQLException {// 1.加载驱动// DriverManager.registerDriver(new com.mysql.jdbc.Driver());// 5.0+// Class.forName("com.mysql.jdbc.Driver");// // 8.0+Class.forName("com.mysql.cj.jdbc.Driver");//2.用户信息和URL// useSSL=true可能会报错// 5.0+// String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";// 8.0+String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8";String userName = "root";String passWord = "981030";//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();}
}
步骤总结:
- 加载驱动
- 连接数据库
DriverManager
- 获取执行 SQL 的对象
Statement
- 获得返回的结果集
- 释放连接
18. JDBC中对象解释
DriverManager
//1.加载驱动
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//推荐这种写法加载驱动
Class.forName("com.mysql.jdbc.Driver");Connection connection = DriverManager.getConnection(url, userName, passWord);
// connection代表数据库
// 数据库设置自动提交
// 事务提交
// 事务回滚
connection.setAutoCommit(true);
connection.commit();
connection.rollback();
URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";// mysql默认端口3306
// 协议://主机地址:端口号/数据库名?参数1&参数2&参数3
// oracle默认端口1521
// jdbc:oracle:thin:@localhost:1521:sid
Statement / PreparedStatement
Statement
、 PreparedStatement
执行 sql 对象
String sql = "SELECT * FROM users";//编写SQLstatement.executeQuery();//执行查询 返回ResultSet
statement.executeUpdate();//新增,删除,修改,都用这个,返回受影响的行数
statement.execute();//执行任何SQL
ResultSet
ResultSet 查询的结果集,封装了所有的查询结果
获得指定的数据类型
//在不知道列类型的情况下使用
resultSet.getObject();
//如果知道列类型,就使用指定的类型
resultSet.getString();
resultSet.getInt();
resultSet.getDouble();
resultSet.getBigDecimal();
resultSet.getFloat();
resultSet.getDate();
//...
遍历,指针
resultSet.beforeFirst();//移动到最前面
resultSet.afterLast();//移动到最后面
resultSet.next();//移动到下一个数据
resultSet.previous();//移动到前一行
resultSet.absolute(row);//移动到指定行
释放资源
resultSet.close();
statement.close();
connection.close();//消耗资源
19. statement
jdbc 中的 statement 对象用于向数据库发送 SQL 语句,想完成对数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句即可。
Statement对象的executeUpdate方法,用于向数据库发送增、删、改的SQL语句,executeUpdate执行完后,将会返回一个整数(即增删改语句导致了数据库几行数据发送了变化)。
Statement.executeQuery方法用于向数据库发送查询语句,executeQuery方法返回代表查询结果的ResultSet对象。
CRUD操作-create
使用executeUpdate(String sql)方法完成数据添加操作,示例操作:
Statement statement = connection.createStatement();
String sql = "insert into user(...) values(...)";
int num = statement.executeUpdate(sql);
if (num > 0) {System.out.println("插入成功~");
}
CRUD操作-delete
Statement statement = connection.createStatement();
String sql = "delete from user where id=1";
int num = statement.executeUpdate(sql);
if (num > 0) {System.out.println("删除成功~");
}
CRUD操作-update
Statement statement = connection.createStatement();
String sql = "update user 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";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {//根据获取列的数据类型,分别调用resultSet的相应方法映射到java对象中
}
代码实现
数据库配置文件
# 5.0+
# driver=com.mysql.jdbc.Driver
# url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
# 8.0+
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8
username=root
password=123456
提取工具类
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();}}/*** 获取连接* @return Connection* @throws SQLException 异常*/public static Connection getConnection() throws SQLException {return DriverManager.getConnection(url, username, password);}/*** 释放资源* @param con 连接对象* @param st 操作对象* @param rs 结果集*/public static void release(Connection con, Statement st, ResultSet rs) {if (rs != null) {try {rs.close();} catch (SQLException e) {e.printStackTrace();}}if (st != null) {try {st.close();} catch (SQLException e) {e.printStackTrace();}}if (con != null) {try {con.close();} catch (SQLException e) {e.printStackTrace();}}}
}
executeUpdate
import com.why.stat.utils.JdbcUtils;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;public class TestInsert {public static void main(String[] args) {Connection con = null;Statement st = null;ResultSet rs = null;try {// 获取数据库连接con = JdbcUtils.getConnection();// 获取st = con.createStatement();String sql = "INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`)\n" +"VALUES (5,'赵一','123456','qianqi@sina.com','1988-12-04')";int res = st.executeUpdate(sql);if (res > 0) {System.out.println("插入成功!");}} catch (SQLException e) {e.printStackTrace();} finally {JdbcUtils.release(con, st, rs);}}
}
import com.why.stat.utils.JdbcUtils;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;public class TestDelete {public static void main(String[] args) {Connection con = null;Statement st = null;ResultSet rs = null;try {con = JdbcUtils.getConnection();st = con.createStatement();String sql = "DELETE FROM users WHERE `id`=5";int res = st.executeUpdate(sql);if (res > 0) {System.out.println("删除成功!");}} catch (SQLException e) {e.printStackTrace();} finally {JdbcUtils.release(con, st, rs);}}
}
import com.why.stat.utils.JdbcUtils;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;public class TestUpdate {public static void main(String[] args) {Connection con = null;Statement st = null;ResultSet rs = null;try {con = JdbcUtils.getConnection();st = con.createStatement();String sql = "UPDATE users SET birthday='1111-11-11' WHERE id=1";int num = st.executeUpdate(sql);if (num > 0) {System.out.println("更新成功!");}} catch (SQLException e) {e.printStackTrace();} finally {JdbcUtils.release(con, st, rs);}}
}
executeQuery
import com.why.stat.utils.JdbcUtils;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;public class TestSelect {public static void main(String[] args) {Connection con = null;Statement st = null;ResultSet rs = null;try {con = JdbcUtils.getConnection();st = con.createStatement();String sql = "SELECT * FROM users WHERE id=1";rs = st.executeQuery(sql);while (rs.next()) {System.out.println("id="+rs.getInt("id"));System.out.println("name="+rs.getString("name"));}} catch (SQLException e) {e.printStackTrace();} finally {JdbcUtils.release(con, st, rs);}}
}
20. sql注入问题
sql 存在漏洞,会被拼接,被攻击,导致数据泄露。
import com.why.stat.utils.JdbcUtils;import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;/*** TODO** @author why* @since 2021/10/9 9:43*/
public class TestSqlIn {public static void main(String[] args) {// 正常登录// login("张三","123456");// sql注入login("' or '1=1","' or '1=1");}/*** 登录业务* @param userName 用户名* @param password 密码*/public static void login(String userName, String password) {Connection con = null;Statement st = null;ResultSet rs = null;try {con = JdbcUtils.getConnection();st = con.createStatement();String sql = "SELECT * FROM users WHERE `name`='"+userName+"' AND `password`='"+password+"'";// SELECT * FROM users WHERE `name`='' or '1=1' AND `password`='123456'System.out.println(sql);rs = st.executeQuery(sql);while (rs.next()) {System.out.print("id="+rs.getInt("id"));System.out.print(" name="+rs.getString("name"));System.out.println(" password="+rs.getString("password"));}} catch (SQLException e) {e.printStackTrace();} finally {JdbcUtils.release(con, st, rs);}}
}
导致结果:无需用户名和密码验证即可获取到全部的用户信息
21. PreparedStatement
PreparedStatement
可以防止SQL注入,效率更高
executeUpdate
import com.why.stat.utils.JdbcUtils;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;public class TestInsert {public static void main(String[] args) {Connection con = null;PreparedStatement st = null;ResultSet rs = null;try {con = JdbcUtils.getConnection();// 使用?占位符代替参数String sql = "INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`) VALUES (?,?,?,?,?)";// 预编译SQL,暂不执行st = con.prepareStatement(sql);// 手动给参数赋值st.setInt(1, 5);st.setString(2, "赵一");st.setString(3, "123456");st.setString(4, "111111@qq.com");st.setDate(5, new java.sql.Date(new java.util.Date().getTime()));int res = st.executeUpdate();if (res > 0) {System.out.println("插入成功!");}} catch (SQLException e) {e.printStackTrace();} finally {JdbcUtils.release(con, st, rs);}}
}
import com.why.stat.utils.JdbcUtils;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;public class TestDelete {public static void main(String[] args) {Connection con = null;PreparedStatement st = null;ResultSet rs = null;try {con = JdbcUtils.getConnection();// 使用?占位符代替参数String sql = "DELETE FROM users WHERE `id`=?";// 预编译SQL,暂不执行st = con.prepareStatement(sql);// 手动给参数赋值st.setInt(1, 5);int res = st.executeUpdate();if (res > 0) {System.out.println("删除成功!");}} catch (SQLException e) {e.printStackTrace();} finally {JdbcUtils.release(con, st, rs);}}
}
import com.why.stat.utils.JdbcUtils;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;public class TestUpdate {public static void main(String[] args) {Connection con = null;PreparedStatement st = null;ResultSet rs = null;try {con = JdbcUtils.getConnection();// 使用?占位符代替参数String sql = "UPDATE users SET birthday=? WHERE id=?";// 预编译SQL,暂不执行st = con.prepareStatement(sql);// 手动给参数赋值st.setDate(1, new java.sql.Date(new java.util.Date().getTime()));st.setInt(2, 1);int res = st.executeUpdate();if (res > 0) {System.out.println("修改成功!");}} catch (SQLException e) {e.printStackTrace();} finally {JdbcUtils.release(con, st, rs);}}
}
executeQuery
import com.why.stat.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 st = null;ResultSet rs = null;try {con = JdbcUtils.getConnection();// 使用?占位符代替参数String sql = "SELECT * FROM users WHERE id=?";// 预编译SQL暂不执行st = con.prepareStatement(sql);// 手动给参数赋值st.setInt(1, 1);// 执行rs = st.executeQuery();while (rs.next()) {System.out.println("id="+rs.getInt("id"));System.out.println("name="+rs.getString("name"));}} catch (SQLException e) {e.printStackTrace();} finally {JdbcUtils.release(con, st, rs);}}
}
防止 sql 注入
import com.why.stat.utils.JdbcUtils;import java.sql.*;public class TestSqlIn {public static void main(String[] args) {System.out.println("正常登录");login("张三","123456");System.out.println("sql注入");login("' or '1=1","' or '1=1");}/*** 登录业务* @param userName 用户名* @param password 密码*/public static void login(String userName, String password) {Connection con = null;PreparedStatement st = null;ResultSet rs = null;try {con = JdbcUtils.getConnection();// PreparedStatement 防止SQL注入的本质,把传递进来的参数当做字符// 假设其中存在转义字符,比如说'会被直接转义String sql = "SELECT * FROM users WHERE `name`=? AND `password`=?";st = con.prepareStatement(sql);st.setString(1, userName);st.setString(2, password);rs = st.executeQuery();while (rs.next()) {System.out.print("id="+rs.getInt("id"));System.out.print(" name="+rs.getString("name"));System.out.println(" password="+rs.getString("password"));}} catch (SQLException e) {e.printStackTrace();} finally {JdbcUtils.release(con, st, rs);}}
}
22. 使用idea连接数据库
create table account
(id int primary key auto_increment,name varchar(40),money float
);insert into account(name, money)
values ('A', 1000),('B', 1000),('C', 1000);
23. JDBC操作事务
要么都成功,要么都失败
ACID原则
原子性:要么全部成功,要么全部失败
一致性:总数不变
隔离性:多个进程互不干扰
持久性:一旦提交不可逆,持久化到数据库了
隔离性的问题:
脏读:一个事务读取了另外一个没有提交的事务
不可重复读:在同一个事务内,重复读取表中数据,表数据发生了改变
幻读:在一个事务内,读取到了别人插入的数据,导致前后读出来的结果不一致
代码实现
- 开启事务
con.setAutoCommit(false);
- 一组业务执行完毕,提交事务
- 可以在catch语句中显示的定义回滚语句,但是默认失败就会回滚
import com.why.stat.utils.JdbcUtils;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;/*** TODO** @author why* @since 2021/10/9 10:56*/
public class TestTransaction {public static void main(String[] args) {Connection con = null;PreparedStatement ps = null;ResultSet rs = null;try {con = JdbcUtils.getConnection();// 关闭自动提交 自动会开启事务con.setAutoCommit(false);//开启事务// A 转 B 100元String sql1 = "update account set money=money-100 where name='A'";ps = con.prepareStatement(sql1);ps.executeUpdate();// 报错// int x = 1/0;String sql2 = "update account set money=money+100 where name='B'";ps = con.prepareStatement(sql2);ps.executeUpdate();// 业务完毕,提交事务con.commit();System.out.println("A 转 B 100元 成功!");} catch (SQLException e) {e.printStackTrace();// 默认失败也会回滚,可省略try {// 失败回滚con.rollback();} catch (SQLException ex) {ex.printStackTrace();}} finally {JdbcUtils.release(con, ps, rs);}}
}
24. 连接池
数据库连接 --> 执行完毕 --> 释放
连接-- 释放 是十分浪费系统资源的
池化技术:准备一些预先的资源,过来就连接预先准备好的
最小连接数:10(常用连接)
最大连接数:100 (业务最高承载上线)
等待超时:100ms
编写连接池,实现一个接口DataSource
开源数据源实现:DBCP,C3p0,Druid(阿里巴巴)
使用了这些数据库连接池之后,项目开发中就不需要编写连接数据库的代码了
DBCP
需要用到的jar包:commons-dbcp-1.4,commons-pool-1.6
配置文件 dbcp.properties
# 连接设置 8.0+
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8
username=root
password=981030# 初始化连接
initialSize=10# 最大连接数量
maxActive=50# 最大空闲连接
maxIdle=20# 最小空闲连接
minIdle=5# 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒
maxWait=60000
# JDBC驱动建立连接时附带的连接属性属性的格式必须为这样:【属性名=property;】
# 注意:user 与 password 两个属性会被明确地传递,因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8# 指定由连接池所创建的连接的自动提交(auto-commit)状态。
defaultAutoCommit=true# driver default 指定由连接池所创建的连接的只读(read-only)状态。
# 如果没有设置该值,则“setReadOnly”方法将不被调用。(某些驱动并不支持只读模式,如:Informix)
defaultReadOnly=# driver default 指定由连接池所创建的连接的事务级别(TransactionIsolation)。
# 可用值为下列之一:(详情可见javadoc。)NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_COMMITTED
工具类
import org.apache.commons.dbcp.BasicDataSourceFactory;import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;public class DbcpUtils {private static DataSource dataSource = null;static {try {InputStream in = DbcpUtils.class.getClassLoader().getResourceAsStream("dbcp.properties");Properties properties = new Properties();properties.load(in);//创建数据源 工厂模式dataSource = BasicDataSourceFactory.createDataSource(properties);} catch (IOException e) {e.printStackTrace();} catch (ClassNotFoundException e) {e.printStackTrace();} catch (Exception e) {e.printStackTrace();}}/*** 获取连接* @return Connection* @throws SQLException 异常*/public static Connection getConnection() throws SQLException {//从数据源中获取连接return dataSource.getConnection();}/*** 释放资源* @param con 连接* @param st 操作* @param rs 结果*/public static void release(Connection con, Statement st, ResultSet rs) {if (rs != null) {try {rs.close();} catch (SQLException e) {e.printStackTrace();}}if (st != null) {try {st.close();} catch (SQLException e) {e.printStackTrace();}}if (con != null) {try {con.close();} catch (SQLException e) {e.printStackTrace();}}}
}
测试类
package com.why.source;import com.why.source.utils.DbcpUtils;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;public class TestDbcp {public static void main(String[] args) {Connection con = null;PreparedStatement st = null;ResultSet rs = null;try {con = DbcpUtils.getConnection();// 使用?占位符代替参数String sql = "INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`) VALUES (?,?,?,?,?)";// 预编译SQL,暂不执行st = con.prepareStatement(sql);// 手动给参数赋值st.setInt(1, 5);st.setString(2, "赵一");st.setString(3, "123456");st.setString(4, "zhaoyi@sina.com");st.setDate(5, new java.sql.Date(new java.util.Date().getTime()));int num = st.executeUpdate();if (num > 0) {System.out.println("插入成功!");}} catch (SQLException e) {e.printStackTrace();} finally {DbcpUtils.release(con, st, rs);}}
}
C3P0
需要用到的jar包:c3p0-0.9.5.5.jar,mchange-commons-java
配置文件c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config><!--c3p0的缺省(默认)配置如果在代码中ComboPooledDataSource ds=new ComboPooledDataSource();这样写就表示使用的是c3p0的缺省(默认)--><default-config><property name="driverClass">com.mysql.cj.jdbc.Driver</property><property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC</property><property name="user">root</property><property name="password">981030</property><property name="acquiredIncrement">5</property><property name="initialPoolSize">10</property><property name="minPoolSize">5</property><property name="maxPoolSize">20</property></default-config><!--c3p0的命名配置如果在代码中ComboPooledDataSource ds=new ComboPooledDataSource("MySQL");这样写就表示使用的是name是MySQL--><name-config name="MySQL"><property name="driverClass">com.mysql.jdbc.Driver</property><property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC</property><property name="user">root</property><property name="password">123456</property><property name="acquiredIncrement">5</property><property name="initialPoolSize">10</property><property name="minPoolSize">5</property><property name="maxPoolSize">20</property></name-config>
</c3p0-config>
工具类
package com.why.source.utils;import com.mchange.v2.c3p0.ComboPooledDataSource;import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;/*** TODO** @author why* @since 2021/10/9 13:29*/
public class C3p0Utils {private static DataSource dataSource = null;//private static ComboPooledDataSource dataSource = null;static {try {//配置文件写法dataSource = new ComboPooledDataSource("MySQL");} catch (Exception e) {e.printStackTrace();}}/*** 获取连接* @return Connection* @throws SQLException 异常*/public static Connection getConnection() throws SQLException {//从数据源中获取连接return dataSource.getConnection();}/*** 释放资源* @param con 连接* @param st 操作* @param rs 结果*/public static void release(Connection con, Statement st, ResultSet rs) {if (rs != null) {try {rs.close();} catch (SQLException e) {e.printStackTrace();}}if (st != null) {try {st.close();} catch (SQLException e) {e.printStackTrace();}}if (con != null) {try {con.close();} catch (SQLException e) {e.printStackTrace();}}}
}
测试类
package com.why.source;import com.why.source.utils.C3p0Utils;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;/*** TODO** @author why* @since 2021/10/9 13:30*/
public class TestC3p0 {public static void main(String[] args) {Connection con = null;PreparedStatement st = null;ResultSet rs = null;try {con = C3p0Utils.getConnection();//使用?占位符代替参数String sql = "INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`) VALUES (?,?,?,?,?)";//预编译SQL,先写SQL,然后不执行st = con.prepareStatement(sql);//手动给参数赋值st.setInt(1, 6);st.setString(2, "钱二");st.setString(3, "123456");st.setString(4, "qianer@qq.com");st.setDate(5, new java.sql.Date(new java.util.Date().getTime()));int num = st.executeUpdate();if (num > 0) {System.out.println("插入成功!");}} catch (SQLException e) {e.printStackTrace();} finally {C3p0Utils.release(con, st, rs);}}
}
无论用什么数据源,本质还是一样的,DataSource接口不会变,方法就不会变