文章目录
MySQL
参考:https://www.bilibili.com/video/BV1NJ411J79Wp=45&vd_source=5f5ea5ad3feec587cf827af102313eae
1. 数据库分类
关系型数据库:(SQL)
- MySQL、Oracle、SQLServer,DB2,SQLite
- 通过表和表之间,行和列之间的关系进行数据的存储
非关系型数据库:(NoSQL)Not Only
- Redis,MongoDB
- 对象存储,通过对象自身的属性来决定
DBMS(数据库管理系统)
- 数据库的管理软件,科学有效的管理我们的数据,维护和获取数据
- MySQL,数据库管理系统!
2. MySQL
-
MySQL是一个**关系型数据库管理系统**
-
过去:由瑞典[MySQL AB](https://baike.baidu.com/item/MySQL AB/2620844) 公司开发
-
现在:属于 Oracle 旗下产品。
-
MySQL 是最流行的关系型数据库管理系统之一
-
在 WEB 应用方面,MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一。
体积小、速度快、总体拥有成本低,中小型网站或大型网站,集群!
2.1 Navicat建库
3. SQL命令
mysql关键字不区分大小写
3.1 连接数据库
mysql -u用户名 -p密码 -- 连接数据库
update mysql.user set authentication_string=password('新密码') where user='用户名' and Host ='localhost'; -- 修改密码
flush privileges; -- 刷新权限
-- ----------------------------------------------------------------------
-- 所有的语句都使用分号;结尾
show databases; -- 查看所有的数据库
use school; -- 切换数据库 use 数据库名
show tables; -- 查看数据库中所有的表
describe student; -- 显示表中所有的信息
create database westos; -- 创建数据库
exit; -- 退出连接
-- sql单行注释
/*sql多行注释*/
数据库 xxx 语言 CRUD 增删改查
-
DDL 定义
-
DML 操作
-
DQL 查询
-
DCL 控制
4. 操作数据库
操作数据库 > 操作数据库中的表 > 操作数据库中表的数据
4.1 操作数据库
1、创建数据库
CREATE DATABASE westos; -- 创建数据库
CREATE DATABASE IF NOT EXISTS westos; -- 如果不存在则创建
2、删除数据库
DROP DATABASE westos; -- 删除数据库
DROP DATABASE IF EXISTS westos; -- 如果存在则删除
3、使用数据库
-- tab键的上面,如果你的表名或者字段名是一个特殊字符,就需要带``
USE school
4、查看数据库
SHOW DATABASE; -- 查看所有的数据库
4.2 数据库的列类型
数值
- tinyint 十分小的数据 1字节
- smallint 较小的数据 2字节
- mediumint 中等大小的数据 3字节
- int 标准的整数 4字节 常用的
- bigint 较大的数据 8字节
- float 浮点数 4字节 (精度问题)
- double 浮点数 8字节 (精度问题)
- decimal 字符串的浮点数 金融计算的时候,一般用decimal
字符串
- char 字符串,固定大小的 0~255
- **varchar 可变字符串 0~65535 ** 常用的变量
- tinytext 微型文本 2 8 − 1 2^8-1 28−1
- text 文本串 2 1 6 − 1 2^16-1 216−1 保存大文本
时间日期
- date YYYY-MM-DD,日期
- time HH:mm:ss,时间格式
- datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
- timestamp 时间戳 也较为常用
- year 年份表示
null
- 没有值/未知
- 不要使用NULL进行运算,结果为NULL
4.3 数据库的字段属性
Unsigned
:
- 无符号的整数
- 声明了该列不能声明为负数
zerofill
:
- 0 填充
- 不足的位数使用 0 来填充, 如: int(3) 5 —> 005
AUTO_INCREMENT(自增)
:
- 通常理解为自增,自动在上一条记录的基础上 +1 (默认)
- 通常用来设计唯一的主键 index 必须是整数类型
- 可以自定义设计主键自增的起始值和步长
NULL/Not NULL
:
- 假设设置为Not NULL,如果不给他赋值,就会报错
- NULL,如果不填写值,默认为NULL
default 默认
:
- 设置默认的值
- 如果不指定该列的值,则会有默认的值
4.4 创建数据库表
-- 注意:使用英文(),表的 名称 和 字段 尽量使用 `` 括起来
-- AUTO_INCREMENT 自增
-- 字符串用单引号括起来
-- 所有语句后面加,最后一个字段不用加
-- PRIMARY KEY 主键,一般一个表只有一个唯一的主键
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
格式
CREATE TABLE [IF NOT EXISTS] `表名`(
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释]
)[表类型][字符集设置][注释]
常用命令
SHOW CREATE DATABASE schoo1 --查看创建数据库的语句
SHOW CREATE TABLE student --查看student数据表的定义语句
DESC student --显示表的结构
4.5 数据表的类型
关于数据库引擎
INNODB 默认使用
MYISAM 早些年使用的
MYISAM | INNODB | |
---|---|---|
事物支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间大小 | 较小 | 较大,约为2倍 |
常规使用操作:
- MYISAM 节约空间,速度较快
- INNODB 安全性高,事务的处理,多表多用户操作
在物理空间存在的位置
所有的数据库文件都存在data目录下,一个文件夹对应一个数据库
本质还是文件的存储
MySQL引擎在物理文件上的区别:
- INNODB 在数据库表中只有一个*.frm文件,以及上级目录下的 ibdata 文件
- MYISAM 对应的文件
- *.frm 表结构的定义文件
- *.MYD 数据文件 (data)
- *.MYI 索引文件 (index)
设置数据库表的字符节编码
MySQL的默认编码是Latin1,不支持中文
MySQL的utf8是utfmb3,只有三个字节,节省空间但不能表达全部的UTF-8。所以推荐使用utf8mb4。
4.6 修改删除表
修改
-
change 可以更改列名 和 列类型
-
modify 只能更改列属性 只需要写一次列名, 比change 省事点
-- 修改表名:ALTER TABLE I旧表名RENAME AS新表名
ALTER TABLE teacher RENAME AS teacher1
-- 增加表的字段:ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher1 ADD age INT(11)
-- 修改表的字段(重命名,修改约束!)ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- 修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
ALTER TABLE teacher1 CHANGE age age1 INT(1) -- 字段重名名
-- 删除表的字段:ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher1 DROP age1
删除
-- 删除表
DROP TABLE IF EXISTS teacher1
所有的创建好删除操作尽量加上判断,以免报错
注意点:
- 字段名,使用 `` 包裹
- 注释 – /* */
- sql关键字大小写不敏感
5. MySQL的数据管理
5.1 外键
-
SQL 外键(Foreign Key)用于将两个表连接在一起,让两个表的数据保持同步。
-
外键由表中的一个字段或者多个字段构成,一个表的外键用来指向另一个表的主键(Primary Key)。包含外键的表称为从表,被指向的表称为主表。从表的数据受到主表的约束,向从表中插入或者更新数据时,外键的值必须存在于主表的主键中。
方式一,在创建表的时候,增加约束(麻烦,比较复杂)
CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'id',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 学生表的 gradeid 字段 要去引用年级表的 gradeid
-- 定义外键 key
-- 给这个外键添加约束 (执行引用) references 引用
CREATE TABLE IF NOT EXISTS student
`id` INT(4) NOT NULL AUTO_.INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pWd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`SeX` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`emai1` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY(`id`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
删除有外键关系的表时,必须先删除引用别人表(从表),再删除被引用的表(主表)
方式二:创建表之后,添加外键约束
CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'id',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 学生表的 gradeid 字段 要去引用年级表的 gradeid
-- 定义外键 key
-- 给这个外键添加约束 (执行引用) references 引用
CREATE TABLE IF NOT EXISTS student
`id` INT(4) NOT NULL AUTO_.INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pWd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`SeX` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`emai1` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY(`id`),
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 创建表的时候没有外键关系
ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY (作为外键的列) REFERENCES 哪个表(哪个字段)
以上都是物理外键,数据库级别的外键,不建议使用(避免数据库过多 造成困扰,这里了解即可)
最佳实践
- 数据库就是单纯的表,只用来存数据,只有行(数据)和列(字段)
- 我们想使用多张表的数据,想使用外键(程序去实现)
5.2 DML语言
DML:数据操作语言
- insert
- update
- delete
5.3 添加
insert
-- 插入语句
-- INSERT INTO 表名([字段名1,字段2,字段3]) VALUES('值1'),('值2'),('值3')
INSERT INTO `grade`(`gradename`) VALUES('大四')
-- 由于主键自增我们可以省略(如果不写表的字段,他们就会一一匹配
-- 一般写插入语句,我们一定要数据和字段一一对应
-- 插入多个字段
INSERT INTO `grade`(`gradename`)
VALUES('大一'),('大二')
语法:INSERT INTO 表名([字段名1,字段2,字段3]) VALUES('值1'),('值2'),('值3',......)
注意事项:
- 字段和字段之间使用英文逗号隔开
- 字段是可以省略的,但是后面的值必须要一一对应,不能少
- 可以同时插入多条数据,valuses后面的值,需要使用(),(),()隔开
5.4 修改
update 修改谁(条件) set原来的值 = 新值
-- 修改
UPDATE `grade` SET `gradename`='高三' WHERE gradeid=1;
-- 不指定的条件下,会改动所有的表
UPDATE `grade` SET `gradename`='高三' ;
-- 修改多个属性,逗号隔开
UPDATE`student` SET `name`='王五',`emai1`='24736743gqg.com' WHERE id=1;
-- 通过多个条件定位数据
UPDATE`student` SET `name`='王五123' WHERE `name`='张三' AND `sex`= `男`;
语法:UPDATE 表名 SET column_name=value WHERE 条件
条件:where 字句,运算符
操作符 | 含义 | 范围 | 结果 |
---|---|---|---|
= | 等于 | 5=6 | false |
<> 或 != | 不等于 | 5<>6 | true |
> | |||
< | |||
<= | |||
>= | |||
BETWEEN … and … | []闭合区间,在某个范围内 | [2,5] | |
AND | && | ||
OR | || |
注意事项:
- column_name是数据库的列,尽量戴上``
- 条件,筛选的条件,如果没有指定,则会修改啊所有列
- value,是一个具体的值,也可以是一个变量
- 多个设置的属性之间,是用英文逗号隔开
UPDATE `student` SET `birthday`=CURRENT._TIME WHERE `name`='长江7号' AND sex='女'
5.5 删除
delete
语法:delete form 表名 [where 条件]
-- 删除数据(避免这样写,会全部删除)
DELETE FROM `student`
-- 删除指定数据
DELETE FROM `student` WHERE id=1;
TRUNCATE 命令
作用:完全清空一个数据库表,表的结构和索引约束不会变
-- 清空student表
TRUNCATE `student`
TRUNCATE 与 delete 的区别
- 相同点:都能删除数据,都不会删除表的结构
- 不同:
- TRUNCATE 重新设置 自增列 计数器回归零
- TRUNCATE 不会影响事务
delete删除的问题
,重启数据库,现象:
- Innodb 自增列会从1开始(存在内存中,断电即失)
- MyISAM 继续从上一个自增量开始(存在文件中,不会丢失)
6. DQL查询数据
6.1 DQL
(Data Query LANGUAGE:数据查询语言)
- 所有的查询操作都用它 SELECT
- 简单的查询,复杂的查询都能做
- 数据库中最核心的语言
- 使用频率最高的语言
6.2 指定查询字段
-- 查询全部的学生
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 别名 表名 AS 别名
去重 distinct
作用:去除SELECT查询出来的结果中重复的数据,只显示一条
-- 查询哪些同学参加了考试,成绩
SELECT * FROM result -- 查询全部的考试成绩
SELECT `studentno` FROM result -- 查询有那些同学参加了考试
SELECT DISTINCT `studentno` FROM result -- 发现重复数据,去重
数据库的列
SELECT VERSION() -- 查询系统版本(函数)
SELECT 100*3-1 AS 计算结果 -- 计算(表达式)
SELECT @@auto_increment_increment -- 查询自增的步长(变量)
-- 学生考试成绩加一
SELECT `studentno`,`studentresult`+1 AS 提分后 FROM result
数据库中的表达式:文本值,列,Null,函数,计算表达上,系统变量
select 表达式
from 表
6.3 where条件子句
作用:检索数据中符合条件
的值
搜索的条件:由一个或多个表达式组成,结果为布尔值
逻辑运算符
尽量使用英文字母
-- ================where====================
SELECT studentNo,`StudentResult` FROM result
-- 查询考试成绩在95~100分之间
SELECT studentNo,`StudentResult` FROM result
WHERE StudentResult>=95 AND StudentResult<=100
and
&&
SELECT studentNo,`studentResult` FROM result
WHERE StudentResult>=95 &studentResult<=100
-- 模糊查询(区间)
SELECT studentNo,`studentResult` FROM result
WHERE StudentResult BETWEEN 95 AND 100
-- 除了1000号学生之外的同学的成绩
SELECT studentNo,`studentResult` FROM result
WHERE studentNo!=1000;
-- != not
SELECT studentNo,`studentResult` FROM result
WHERE NOT studentNo=1000
模糊查询:比较运算符
-- ===========模糊查询=============
-- 查询姓刘的同学
-- 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(具体的一个或者多个值)=====
-- 查询1001,1002,1003号学员
SELECT `studentNo` , `studentName`FROM `student`
WHERE StudentNo IN (1001,1002,1003);
-- 查询在北京的学生
SELECT `studentNo` , `studentName`FROM `student`
WHERE `Address` IN('安徽','河南洛阳');
-- ====null not nu11====
-- 查询地址为空的学生 nu11''
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
6.4 联表查询
JOIN 对比
SQL JOIN 子句用于把来自两个或多个表的行结合起来,基于这些表之间的共同字段。
最常见的 JOIN 类型:SQL INNER JOIN(简单的 JOIN)。 SQL INNER JOIN 从多个表中返回满足 JOIN 条件的所有行。
数据库在通过连接两张或多张表来返回记录时,都会生成一张中间的临时表,然后再将这张临时表返回给用户。
在使用 left join 时,on 和 where 条件的区别如下:
-
on 条件是在生成临时表时使用的条件,它不管 on 中的条件是否为真,都会返回左边表中的记录。
-
where 条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有 left join 的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。
-- ======联表查询join ====================
-- 查询参加了考试的同学(学号,姓名,科目编号,分数)
SELECT *FROM student
SELECT *FROM result
/*思路
1.分析需求,分析查询的字段来自哪些表,(连接查询)
2.确定使用哪种连接查询? 7种
确定交叉点(这两个表中哪个数据是相同的)
判断的条件:学生表中的 studentNo = 成绩表的 studentNo
*/
SELECT s.studentNo,studentName,SubjectNo,studentresult
FROM student AS s
INNER JOIN result AS r
ON s.studentNo = r.studentNo
操作 | 描述 |
---|---|
Inner join | 如果表中至少有一个匹配,就返回行 |
left join | 会从左表中返回所有的值,即使右表中没有匹配 |
right join | 会从右表中返回所有的值,即使左表中没有匹配 |
full join | 只要其中一个表中存在匹配,则返回行 |
自连接
自己的表和自己的表连接,核心:一张表拆为两张一样的表即可
- 自连接的基本语法如下:
SELECT a.column_name, b.column_name...
FROM table1 a, table1 b
WHERE a.common_field = b.common_field;
在这里,WHERE子句可以根据您的需要给定的任意表达式。
举例:
- 考虑下面的两个表中,(a)CUSTOMERS表如下:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
- 现在,让我们用自连接这个表,如下所示:
SQL> SELECT a.ID, b.NAME, a.SALARY
FROM CUSTOMERS a, CUSTOMERS b
WHERE a.SALARY < b.SALARY;
- 这将产生以下结果:
+----+----------+---------+
| ID | NAME | SALARY |
+----+----------+---------+
| 2 | Ramesh | 1500.00 |
| 2 | kaushik | 1500.00 |
| 1 | Chaitali | 2000.00 |
| 2 | Chaitali | 1500.00 |
| 3 | Chaitali | 2000.00 |
| 6 | Chaitali | 4500.00 |
| 1 | Hardik | 2000.00 |
| 2 | Hardik | 1500.00 |
| 3 | Hardik | 2000.00 |
| 4 | Hardik | 6500.00 |
| 6 | Hardik | 4500.00 |
| 1 | Komal | 2000.00 |
| 2 | Komal | 1500.00 |
| 3 | Komal | 2000.00 |
| 1 | Muffy | 2000.00 |
| 2 | Muffy | 1500.00 |
| 3 | Muffy | 2000.00 |
| 4 | Muffy | 6500.00 |
| 5 | Muffy | 8500.00 |
| 6 | Muffy | 4500.00 |
+----+----------+---------+
6.5 分页和排序
排序
-- ==========分页 limit 和排序 order by =============
-- 排序:升序 ASC , 降序 DESC
-- ORDER BY 通过哪个字段排序,怎么排
-- 查询的结果, 成绩降序排序
SELECT s.`studentNo`,`studentname`,`subjectname`,`studentresult`
FROM student s
INNER JOIN `result` r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.`subjectno` = '数据库结构*-1'
ORDER BY studentresult DESC
-- 为什么分页?
-- 缓解数据库压力,给人体验更好 瀑布流
-- 分页,每页显示五条送数据
-- 语法: limit 当前页,页面的大小
SELECT s.`studentNo`,`studentname`,`subjectname`,`studentresult`
FROM student s
INNER JOIN `result` r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.`subjectno` = '数据库结构*-1'
ORDER BY studentresult DESC
LIMIT 0,5
-- 第1页 limit 0,5
-- 第2页 limit 5,5
-- 第3页 limit 10,5
-- 第3页 limit 10,5
-- 第n页 limit (n-1)*pageSize,pageSize
-- pageSize:页面大小
语法:limit(查询起始下标,pagesize)
6.6子查询
where(这个值是计算出来的)
本质:在 where 中嵌套一个子查询语句
where(select * from)
-- ======= where ==============
-- 查询 数据库结构-1 的所有考试结果(学号,科目编号,成绩),降序排列
-- 方式一:使用连接查询
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM `result` r
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '数据库结构-1'
ORDER BY studentresult DESC
-- 方式二:使用子查询(有里及外)
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM `result` r
WHERE subjectno = (
SELECT subjectno FROM `subject`
WHERE subjectname = '数据库结构-1'
)
ORDER BY studentresult DESC
6.7 Select小结
顺序很重要:
- select 去重 要查询的字段 from 表 (注意:表和字段可以取别名)
- xxx join 要链接的表 on 等值判断
- where (具体的值,子查询语句)
- Group By (通过哪个字段来分组)
- Having (过滤分组后的信息,条件和 where 是一样的,位置不同)
- Order By … (通过哪个字段排序)【升序/降序】
- Limit startIndex,pageSize
7. MySQL函数
官网:https://dev.mysql.com/doc/refman/8.0/en/built-in-function-reference.html
需要的时候查就完了
7.1 常用函数
-- =========== 常用函数===================
-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回0-1之间的随机数
SELECT SIGN() -- 判断一个数的符号
-- 字符串函数
SELECT CHAR_LENGTH('hello world') -- 字符串长度
SELECT CONCAT('我','爱','你') -- 拼接字符串
SELECT INSERT(str,pos,len,newstr) -- 查询,替换
SELECT LOWER(str) -- 转小写
SELECT UPPER(str) -- 转大写
SELECT REPLACE(str,from_str,to_str) -- 替换出线的指定字符串
SELECT SUBSTR(str FROM pos FOR len) -- 返回指定的子字符串
SELECT REVERSE(str) -- 反转
-- 查询姓 周的同学,名字 邹
SELECT REPLACE(stuname,'周','邹') FROM student
WHERE studentname LIKE '周%'
-- 时间和日期函数
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前时间
SELECT LOCALTIME() -- 本地时间
SELECT SYSDATE() -- 系统时间
SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())
-- 系统
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()
7.2 聚合函数
函数 | 作用 |
---|---|
AVG() | 返回某列的平均值 |
COUNT() | 返回某列的行数 |
MAX() | 返回某列的最大值 |
MIN() | 返回某列的最小值 |
SUM() | 返回某列的和··· |
-- ===========聚合函数===================
-- 都能够统计表中的数据(想查询一个表中有多少个记录,就使用这个count() )
SELECT COUNT(`BornDate`) FROM student; -- Count(字段),会忽略所有的nul1值
SELECT COUNT(*) FROM student; -- count (*),不会忽略 nu11 值,本质计算行数
SELECT COUNT(1) FROM result; -- Ccount(1),不会忽略忽略所有的nu11值本质计算行数
SELECT SUM( `studentResult`) AS 总和 FROM result
SELECT AVG( `studentResult`) AS 平均分 FROM result
SELECT MAX( `studentResult`) AS 最高分 FROM result
SELECT MIN( `studentResult`) AS最低分 FROM result
7.3 数据库级别的MD5加密
-- =========== 测试MD5 =============
CREATE TABLE `testmd5`(
`id` INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 明文密码
INSERT INTO testmd5 VALUES
(1,'张三','123456'),
(2,'李四','123456'),
(3,'王五','123456');
-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id = 1
UPDATE testmd5 SET pwd=MD5(pwd) -- 加密全部的
-- 插入的时候加密
INSERT INTO testmd5 VALUES(4,'小明',MD5('123456'))
-- 如何校验:将用户传递进来的密码,进行MD5加密,然后对比
SELECT * FROM testmd5 WHERE `name` = '小明' AND pwd = MD5('123456')
8. 事务
8.1 什么是事务
要么都成功,要么都失败
- SQL 执行 A 给 B 转账 A 1000 --200–> B 200
- SQL 执行 B 收到 A 的钱 A 800 ---------> B 400
将一组SQL放在一个批次中去执行
事务原则:ACID原则
参考博客链接:https://blog.csdn.net/dengjili/article/details/82468576
事务具有以下四个标准属性,通常用缩略词 ACID 来表示:
- **原子性:**要么都成功,要么都失败
- **一致性:**事务前后的数据完整性要保持一致
- **隔离性:**事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。
- **持久性:**事务一旦提交,则不可逆,被持久化到数据库中了
隔离所导致的一些问题
脏读:
指一个事务读取了另外一个事务未提交的数据。
不可重复读:
在一个事务内读取表中的某一行数据,多次读取结果不同。(这个不一定是错误,只是某些场合不对)
虚读(幻读):
是指在一个事务内读取到了别的事务插入的数据,导致前后读取数量总量不一致。
控制事务
有四个命令用于控制事务:
- **COMMIT:**提交更改;
- **ROLLBACK:**回滚更改;
- **SAVEPOINT:**在事务内部创建一系列可以 ROLLBACK 的还原点;
- **SET TRANSACTION:**命名事务
事务流程
-
开始事务
开始事务有以下两种命令,选择其一即可:
COMMIT;
或者
START TRANSACTION;
该命令用来标记一个事务的起始点。
-
提交事务使用如下命令:
COMMIT;
提交事务意味着真正执行事务包含的 SQL 语句,并把对数据库的修改写入到磁盘上的物理数据库中。COMMIT 意味着事务结束,并且执行成功。
-
回滚事务
回滚意味着撤销尚未保存到物理数据库中的操作,具体语法格式如下:
ROLLBACK;
事务执行过程中如果发生了某种故障,事务不能继续执行,就可以撤销事务,此时对数据库的修改并不会保存到物理数据库中。撤销意味着事务结束,并且执行失败。
-
回滚标记点
ROLLBACK
命令默认回滚整个事务,也即事务中的所有修改操作都无效。但是 SQL 允许回滚事务的一部分,此时需要在事务中设置一个标记点,在该标记点之后的 SQL 语句将被回滚,之前的 SQL 语句将被成功执行。设置标记点使用
SAVEPOINT
命令,具体语法如下:SAVEPOINT point_name;
point_name
为标记点名字。回滚到标记点使用 ROLLBACK TO 命令,具体语法如下:
ROLLBACK TO point_name;
9. 索引
MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构
9.1 索引的分类
- 主键索引(PRIMARY KEY)
- 唯一的标识:主键不可重复,只有一列作为主键
- 唯一索引(UNIQUE KEY)
- 避免重复的列出现,唯一索引可以重复,多个列都可以标识为唯一索引
- 常规索引(KEY/INDEX)
- 默认的,index、key关键字来设置
- 全文索引(FullText)
- 在特定的数据库引擎下才有
- 快速定位数据
显示索引信息
使用 SHOW INDEX
命令来列出表中的相关的索引信息。可以通过添加 \G
来格式化输出信息。
mysql> SHOW INDEX FROM table_name\G
........
常规索引
-
创建索引
这是最基本的索引,它没有任何限制。它有以下几种创建方式:
CREATE INDEX indexName ON table_name (column_name)
如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length。
-
修改表结构(添加索引)
ALTER table tableName ADD INDEX indexName(columnName)
-
创建表的时候直接指定
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );
唯一索引
它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:
-
创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))
-
修改表结构
ALTER table mytable ADD UNIQUE [indexName] (username(length))
-
创建表的时候直接指定
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );
使用ALTER 命令添加和删除索引
有四种方式来添加数据表的索引:
- **ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)😗*该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。
- ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。
- ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引,索引值可出现多次。
- **ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)😗*该语句指定了索引为 FULLTEXT ,用于全文索引。
使用 ALTER 命令添加和删除主键
主键作用于列上(可以一个列或多个列联合主键),添加主键索引时,你需要确保该主键默认不为空(NOT NULL)。实例如下:
mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
你也可以使用 ALTER 命令删除主键:
mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;
删除主键时只需指定PRIMARY KEY,但在删除索引时,你必须知道索引名。
9.2 索引原则
- 索引不是越多越好
- 不要对进程变动数据加索引
- 小数据量的表不需要索引
- 索引一般加在常用来查询的字段上
索引的数据结构
详见:https://www.jianshu.com/p/1775b4ff123a
10. 权限管理与备份
10.1 用户管理
SQL 可视化管理
SQL命令操作
用户表:mysql.user
本质:对这张表进行增删改查
-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER zhangsan IDENTIFIED BY '123456'
-- 修改密码(修改当前用户密码)
SET PASSWORD = PASSWORD('111111')
-- 修改密码(修改指定用户密码)
SET PASSWORD FOR zhangsan = PASSWORD('111111')
-- 重命名 RENAME USER 原来名字 TO 新名字
RENAME USER zhangsan TO zhangsan2
-- 用户授权 ALL PRIVILEGES 全部的权限
-- ALL PRIVILEGES 除了给别人授权,什么都能干
GRANT ALL PRIVILEGES ON *.* TO zhangsan2
-- 查询权限
SHOW GRANTS FOR zhangsan2 -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost
-- root用户的权限:GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' with GRANT OPTION
-- 撤销权限 REVOKE 哪些权限, 在哪个库撤销,给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM zhangsan2
-- 删除用户
DROP USER zhangsan2
mysql 5.7.9以后废弃了password字段和password()函数;
authentication_string:字段表示用户密码,而authentication_string字段下只能是mysql加密后的41位字符串密码。
所以需要用一下方式来修改root密码:
参考:https://blog.csdn.net/wolf131721/article/details/93004013
ALTER user 'root'@'localhost' IDENTIFIED BY 'newpassword';
MySql 从8.0开始修改密码有了变化,在user表加了字段authentication_string,修改密码前先检查authentication_string是否为空
-
如果为空,直接修改
ALTER user 'root'@'localhost' IDENTIFIED BY 'root'; -- 修改密码为root
-
如果不为空
use mysql; update user set authentication_string='' where user='root';-- 将字段置为空 ALTER user 'root'@'localhost' IDENTIFIED BY 'root';-- 修改密码为root
-
如果出现如下错误
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement mysql> GRANT ALL PRIVILEGES ON *.* TO IDENTIFIED BY '123' WITH GRANT OPTION;
需要执行
flush privileges;
再执行
ALTER user 'root'@'localhost' IDENTIFIED BY 'root';-- 修改密码为root
10.2 数据库备份
MySQL备份的方式:
-
直接拷贝物理文件
-
在SQL可视化工具中手动导出
-
使用命令行导出
mysqldump
命令行导出# mysqldump -h 主机 -u 用户名 -p 密码 数据库 (表1 表2 表3) > 物理磁盘位置/文件名 mysqldump -h localhost -u root -p 123456 school student > D:/a.sql # 导入 # 登录的情况下,切换到指定的数据库 # source 备份文件 source d:/a.sql mysql -u 用户名 -p 密码 库名 < 备份文件
11. 规范数据库设计
软件开发中,关于数据库的设计:
- 分析需求:分析业务和需要处理的数据库需求
- 概要设计:设计关系图 E-R 图
设计数据库的步骤:(个人博客)
- 收集信息,分析需求
- 用户表(用户登录注销,用户个人信息,写博客,创建分类)
- 分类表(文章分类,谁创建的)
- 文章表(文章的信息)
- 友链表(友链信息)
- 自定义表(系统的信息,某个关键的字,或者一些主字段)
- 标识实体(需求落实到字段)
- 标识实体 之间的关系
- 写博客:user -> blog
- 创建分类:user -> category
- 关注: user -> user
- 友链: links
- 评论: user - user -bolg
11.1 三大数据库范式
详见:https://blog.csdn.net/weixin_44355591/article/details/106194714
第一范式(1NF)
- 原子性:保证每一列不可再分
第二范式(2NF)
- 前提:满足第一范式
- 第二范式要求确保表中每列与主键相关,而不能只与主键的某部分相关(主要针对联合主键),主键列与非主键列遵循完全函数依赖关系,也就是完全依赖(每张表只描述一件事)
第三范式(3NF)
- 前提:满足第二范式
- 第三范式确保主键列之间没有传递函数依赖关系,也就是消除传递依赖(确保数据表中的每一列数据都和主键直接相关,而不能间接相关)
12. JDBC
JDBC的全称是Java数据库连接(Java Database connect),它是一套用于执行SQL语句的Java API。应用程序可通过这套API连接到关系数据库,并使用SQL语句来完成对数据库中数据的查询、更新和删除等操作
需要将 mysql-connector-java-8.0.28.jar 导入library
测试代码:
package com.swust.lesson01;
import com.mysql.cj.jdbc.Driver;
import java.sql.*;
public class JdbcDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver"); // 固定写法,加载驱动
// 2.用户信息和url
// userUnicode=true&characterEncoding=utf8&useSSL=true
String url = "jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&characterEncoding=utf8&useSSL=true";
String username = "root";
String password = "123456";
// 3.连接成功,数据库对象
Connection connection = DriverManager.getConnection(url, username, password);
// 4.执行SQL的对象
Statement statement = connection.createStatement();
// 5.执行SQL的对象 去 执行sQL,查看返回结果
String sql = "SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql); // 返回的结果集,封装了我们所有查询出来的结果
while (resultSet.next()){
System.out.println("id=" + resultSet.getObject("id"));
System.out.println("name=" + resultSet.getObject("NAME"));
System.out.println("pwd=" + resultSet.getObject("PASSWORD"));
System.out.println("email=" + resultSet.getObject("email"));
System.out.println("birth=" + resultSet.getObject("birthday"));
}
// 6. 释放连接
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结:
- 加载驱动
- 连接数据库 DriverManager
- 获得执行sql的对象 Statement
- 获得返回的结果集
- 释放连接
DriverManager
// DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
Class.forName("com.mysql.cj.jdbc.Driver"); // 固定写法,加载驱动
Connection connection = DriverManager.getConnection(url, username, password);
//connection 代表数据库
//数据库自动提交
//事务提交
//事务回滚
connection.setAutoCommit();
connection.commit();
connection.rollback();
URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&characterEncoding=utf8&useSSL=true";
// mysql -- 3306
// 协议://主机地址:端口号/数据库名?参数1&参数2&参数3
// oracle -- 1521
// jdbc:oracle:thin:@localhost:1521:sid
Statement 执行SQL的对象 PreparedStatement 执行SQL的对象
String sql = "SELECT * FROM users"; // 编写sql
statement.executeQuery(); // 查询操作,返回Result
statement.execute(); // 执行任何SQL
statement.executeUpdate(); // 更新、插入、删除,返回一个受影响的行数
ResultSet 查询结果集:封装了所有的查询结果
resultSet.getObject(); // 在不知道列类型的情况下使用
// 如果知道就用指定类型
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
resultSet.getObject();
......
遍历,指针
resultSet.beforeFirst(); // 移动到最前面
resultSet.afterLast(); // 移动到最后面
resultSet.next(); // 移动到下一个数据
resultSet.previous(); // 移动到前一行
resultSet.absolute(row); // 移动到指定行
释放资源
resultSet.close();
statement.close();
connection.close();
12.1 statement对象
Jdbc中的statement
对象用于向数据库发送SQL语句,想完成对数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句即可
代码实现
-
提取工具类
driver=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&characterEncoding=utf8&useSSL=true username=root password=123456
package com.swust.lesson02.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 inputStream = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties"); Properties properties = new Properties(); properties.load(inputStream); driver = properties.getProperty("driver"); url = properties.getProperty("url"); username = properties.getProperty("username"); password = properties.getProperty("password"); // 1. 驱动只用加载一次 Class.forName(driver); }catch (Exception e) { e.printStackTrace(); } } // 获取连接 public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url, username, password); } // 释放连接资源 public static void release(Connection conn, Statement st, ResultSet rs) { if(rs != null){ try { rs.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(st != null){ try { st.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if(conn != null){ try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } }
-
编写增删改查,
executeUpdate
package com.swust.lesson02; import com.swust.lesson02.utils.JdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestInsert { public static void main(String[] args) { Connection connection = null; Statement statement = null; ResultSet resultSet = null; try { connection = JdbcUtils.getConnection(); // 获取数据库连接 statement = connection.createStatement(); // 获得sql的执行对象 String sql = "INSERT INTO users(id ,`NAME`,`PASSWORD`,`email`,`birthday`)"+ "VALUES(5,'zhangsan','123456','123456@qq.com','2022-01-01')"; // 增加 // String sql = "DELETE users WHERE id = 5"; // 删除 int i = statement.executeUpdate(sql); if(i>0){ System.out.println("插入成功!"); // System.out.println("删除成功!"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(connection,statement,resultSet); } } }
SQL注入问题
Sql 注入攻击是通过将恶意的 Sql 查询或添加语句插入到应用的输入参数中,再在后台 Sql 服务器上解析执行进行的攻击,它目前黑客对数据库进行攻击的最常用手段之一。SQL会被拼接 or
12.2 PreparedStatement 对象
PreparedStatement 可以防止 SQL 注入,且效率更高
-
新增
package com.swust.lesson03; import com.swust.lesson02.utils.JdbcUtils; import java.sql.ResultSet; import java.util.Date; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; public class TestInsert { public static void main(String[] args) { Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { connection = JdbcUtils.getConnection(); // 区别 // 使用 ? 占位符代替参数 String sql = "INSERT INTO users(id ,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES (?,?,?,?,?)"; preparedStatement = connection.prepareStatement(sql); // 预编译SQL ,先写sql, 然后不执行 // 手动给参数赋值 preparedStatement.setInt(1,6); // id preparedStatement.setString(2,"lisi"); // NAME preparedStatement.setString(3,"123456"); // PASSWORD preparedStatement.setString(4,"123456@qq.com"); // email // 注意点: sql.Date 数据库 java.sql.Date // util.Date java new Date().getTime() 获得时间戳 preparedStatement.setDate(5,new java.sql.Date(new Date().getTime())); // 执行 int i = preparedStatement.executeUpdate(); if (i > 0){ System.out.println("插入成功!"); } } catch (SQLException throwables) { throwables.printStackTrace(); } finally { JdbcUtils.release(connection,preparedStatement, null); } } }
-
删除
package com.swust.lesson03; import com.swust.lesson02.utils.JdbcUtils; import java.sql.ResultSet; import java.util.Date; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; public class TestDelete { public static void main(String[] args) { Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { connection = JdbcUtils.getConnection(); // 区别 // 使用 ? 占位符代替参数 String sql = "Delete from users where id = ?"; preparedStatement = connection.prepareStatement(sql); // 预编译SQL ,先写sql, 然后不执行 // 手动给参数赋值 preparedStatement.setInt(1,6); // 执行 int i = preparedStatement.executeUpdate(); if (i > 0){ System.out.println("删除成功!"); } } catch (SQLException throwables) { throwables.printStackTrace(); } finally { JdbcUtils.release(connection,preparedStatement, null); } } }
-
更新
package com.swust.lesson03; import com.swust.lesson02.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 connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { connection = JdbcUtils.getConnection(); // 区别 // 使用 ? 占位符代替参数 String sql = "update users set `NAME` = ? where id = ?"; preparedStatement = connection.prepareStatement(sql); // 预编译SQL ,先写sql, 然后不执行 // 手动给参数赋值 preparedStatement.setString(1,"wangwu"); preparedStatement.setInt(2,5); // 执行 int i = preparedStatement.executeUpdate(); if (i > 0){ System.out.println("更新成功!"); } } catch (SQLException throwables) { throwables.printStackTrace(); } finally { JdbcUtils.release(connection,preparedStatement, null); } } }
-
查询
package com.swust.lesson03; import com.swust.lesson02.utils.JdbcUtils; import java.sql.*; public class TestSelect { public static void main(String[] args) { Connection connection = null; PreparedStatement preparedStatement = null; ResultSet resultSet = null; try { connection = JdbcUtils.getConnection(); String sql = "select * from users where id = ?"; preparedStatement = connection.prepareStatement(sql); preparedStatement.setInt(1,1); // 执行 resultSet = preparedStatement.executeQuery(); if(resultSet.next()){ System.out.println(resultSet.getString("NAME")); } } catch (SQLException throwables) { throwables.printStackTrace(); } finally { JdbcUtils.release(connection,preparedStatement,resultSet); } } }
-
PreparedStatement防注入的原因:
之所以PreparedStatement能防止注入,是因为它把单引号转义了,变成了’,这样一来,就无法截断SQL语句,进而无法拼接SQL语句,基本上没有办法注入了。
12.3 使用IDEA连接数据库
点击Test Connection 测试
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Di6IS6AP-1655897795101)(https://s2.loli.net/2022/06/22/ouHs5GDNpK8LeQa.png)]
如果出现 Server returns invalid timezone. Go to ‘Advanced’ tab and set ‘serverTimezone’ property manually. 错误
解决方法: 在 mysql 目录下的 my.ini 中 修改时区:
[mysqld] 下 添加 default-time-zone=‘+08:00’
成功
点击应用,等待加载完毕
选择数据库:
双击数据库:
修改:
写sql
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3JUH18Oe-1655897795111)(https://s2.loli.net/2022/06/22/pzX2APGj6WaDgEF.png)]
12.4 JDBC操作事务
代码实现
- 开启事务
connection.setAutoCommit(false);
- 一组业务执行完毕,提交事务
- 可以在 catch 语句中 显示定义 回滚语句 ,但是默认失败就会回滚 (显示定义不写也行)
package com.swust.lesson04;
import com.swust.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransaction1 {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();
// 关闭数据库的自动提交,自动会开启事务
connection.setAutoCommit(false);// 开启事务
String sql1 = "update account set money = money-100 where name = 'A'";
preparedStatement = connection.prepareStatement(sql1);
preparedStatement.executeUpdate();
String sql2 = "update account set money = money+100 where name = 'B'";
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
// 业务完毕,提交事务
connection.commit();
System.out.println("成功!");
} catch (SQLException throwables) {
// 如果失败,默认回滚
// try {
// connection.rollback(); // 如果失败则回滚事务
//
// } catch (SQLException e) {
// e.printStackTrace();
// }
throwables.printStackTrace();
} finally {
JdbcUtils.release(connection,preparedStatement, null);
}
}
}
12.5 数据库连接池
数据库连接 ---- 执行完毕 ---- 释放
连接 — 释放 十分浪费系统资源
池化技术:准备一些预先的资源,过来就连接预先准备好的
什么是数据库连接池:https://blog.csdn.net/guobinhui/article/details/85157805
开源数据源实现
DBCP
C3p0
Druid:阿里巴巴
使用了这些数据库连接池之后,我们在项目开发中就不需要编写连接数据库的代码了
DBCP
需要用到的jar包
commons-dbcp-1.4
commons-pool-1.6
dbcp-2需要额外导入commons-logging的jar包
配置文件dbcp.properties:
#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456
#初始化连接
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
工具类:
package com.swust.lesson05.utils;
import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils_DBCP {
private static BasicDataSource dataSource = null;
static {
try {
InputStream inputStream = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfige.properties");
Properties properties = new Properties();
properties.load(inputStream);
// 创建数据源 工厂模式 -- 》 创建
dataSource = BasicDataSourceFactory.createDataSource(properties);
}catch (Exception e) {
e.printStackTrace();
}
}
// 获取连接
public static Connection getConnection() throws SQLException {
return dataSource.getConnection(); // 从数据源中获取连接
}
// 释放连接资源
public static void release(Connection conn, Statement st, ResultSet rs) {
if(rs != null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(st != null){
try {
st.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
测试代码:
package com.swust.lesson05;
import com.swust.lesson05.utils.JdbcUtils_DBCP;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
public class TestDBCP {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils_DBCP.getConnection();
// 区别
// 使用 ? 占位符代替参数
String sql = "INSERT INTO users(id ,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES (?,?,?,?,?)";
preparedStatement = connection.prepareStatement(sql); // 预编译SQL ,先写sql, 然后不执行
// 手动给参数赋值
preparedStatement.setInt(1,7); // id
preparedStatement.setString(2,"lisi"); // NAME
preparedStatement.setString(3,"123456"); // PASSWORD
preparedStatement.setString(4,"123456@qq.com"); // email
// 注意点: sql.Date 数据库 java.sql.Date
// util.Date java new Date().getTime() 获得时间戳
preparedStatement.setDate(5,new java.sql.Date(new Date().getTime()));
// 执行
int i = preparedStatement.executeUpdate();
if (i > 0){
System.out.println("插入成功!");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JdbcUtils_DBCP.release(connection,preparedStatement, null);
}
}
}
C3P0
需要用到的jar包
c3p0-0.9.5.5.jar
mchange-commons-java-0.2.19.jar
配置文件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">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的命名配置
如果在代码中ComboPooledDataSource ds=new ComboPooledDataSource("MySQL");这样写就表示使用的是name是MySQL
-->
<name-config name="MySQL">
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true</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.swust.lesson05.utils;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;
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 JdbcUtils_C3P0 {
private static ComboPooledDataSource dataSource = null;
static {
try {
// 代码版配置
// dataSource = new ComboPooledDataSource();
// dataSource.setDriverClass();
// dataSource.setUser();
// dataSource.setPassword();
// dataSource.setJdbcUrl();
//
// dataSource.setMaxPoolSize();
// dataSource.setMinPoolSize();
// 配置文件写法
dataSource = new ComboPooledDataSource("MySQL");
}catch (Exception e) {
e.printStackTrace();
}
}
// 获取连接
public static Connection getConnection() throws SQLException {
return dataSource.getConnection(); // 从数据源中获取连接
}
// 释放连接资源
public static void release(Connection conn, Statement st, ResultSet rs) {
if(rs != null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(st != null){
try {
st.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
测试代码:
package com.swust.lesson05;
import com.swust.lesson05.utils.JdbcUtils_C3P0;
import com.swust.lesson05.utils.JdbcUtils_DBCP;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
public class TestC3P0 {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils_C3P0.getConnection();
// 区别
// 使用 ? 占位符代替参数
String sql = "INSERT INTO users(id ,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES (?,?,?,?,?)";
preparedStatement = connection.prepareStatement(sql); // 预编译SQL ,先写sql, 然后不执行
// 手动给参数赋值
preparedStatement.setInt(1,7); // id
preparedStatement.setString(2,"lisi"); // NAME
preparedStatement.setString(3,"123456"); // PASSWORD
preparedStatement.setString(4,"123456@qq.com"); // email
// 注意点: sql.Date 数据库 java.sql.Date
// util.Date java new Date().getTime() 获得时间戳
preparedStatement.setDate(5,new java.sql.Date(new Date().getTime()));
// 执行
int i = preparedStatement.executeUpdate();
if (i > 0){
System.out.println("插入成功!");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JdbcUtils_C3P0.release(connection,preparedStatement, null);
}
}
}
总结
无论用什么数据源,本质还是一样的,DataSource接口不会变,方法就不会变