MYSQL数据库
1、连接数据库
命令行连接
mysql -uroot -p123456 --连接数据库
updata mysql.user set authentication_string=password('123456') where user='root' and Host ='localhost'; --修改密码
flush privileges; --刷新权限
--------------------------------------------------
--所有的语句都使用 ; 结尾
show databases; --查看所有的数据库
mysql>use school --切换数据库 use 数据库名
Database changed
show tables; --查看数据库中的所有表
describe student --显示数据库中所有的表的信息
create database westos;--创建一个数据库
exit; --退出连接
--单行注释 /* */ (SQL)
数据库xxx语言
DDL 定义 DML 操作 DQL 查询 DCL 控制
2、操作数据库
操作数据库 > 操作数据库中的表 > 操作数据库中的表的数据
MYSQL关键字不区分大小写
2.1 操作数据库(了解)
1、创建数据库
CREATE DATAVASE [IF NOT EXISTS] westos;
2、删除数据库
DROP DATABASE [IF EXISTS] westos
3、使用数据库
--tab 键的上面,如果你的表的名或者字段名是一个特殊的字符,就需要带 ``
USE `school`
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 微型文本
- text 文本串
时间日期
- data YYYY-MM-DD, 日期格式
- time HH: mm: ss 时间格式
- datatime YYYY-MM_DD HH :mm : ss 最常用的格式
- **timetamp 时间戳 **
- year 年份
null
- 没有值,未知
- 注意:不要使用NULL进行运算,结果为NULL
2.3 数据库字段属性(重要)
Unsigned:
- 无符号的整数
- 声明了该数列不能为负数
zerofill:
- 0填充
- 不足的位数,使用零来填充
自增:
- 通常理解为自增,自动在上一条记录的基础上 +1(默认)
- 通常用来设计唯一的主键~ index,必须是整数类型
- 可以自定义设计主键自增的起始值和步长
非空 NULL not null
- 假设设置为 not null ,如果不给他赋值,就会报错
- NULL,如果不填写值,默认就是null
默认
- 设置默认值
- sex,如果不指定该列的值,则会有默认值
拓展
/*每一个表,都必须存在以下的五个字段!
id 主键
'version' 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
*/
2.4 数据的字段属性(重点)
-- 目标: 创建一个school数据库
-- 创建学生表(列 字段) 使用SQL 创建
-- 学号int 登录密码varchar(20) 姓名性别varchar(2) 出生日期(datatime)家庭住址 email
-- 注意点 使用英文() 表的名称和字段 尽量使用 `` 括起来
-- AUTO_INCREMENT 自增
-- 字符串使用 单引号括起来!
-- 所有的语句后面必须加英文的逗号 最后一个不用加
-- PRIMARY KEY 主键 一般一个表只有一个唯一的主键`student``student``student`
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 school ——查看创建数据库的语句
SHOW CREATE TABLE student ——查看student数据表的定义句
DESC student --显示表的结构
2.5 数据表的类型
--关于数据库引擎
/*
INNDOB 默认使用
MYISAM 以前使用
*/
MYISAM | INNDOB | |
---|---|---|
事物支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文检索 | 支持 | 不支持 |
表空间大小 | 较小 | 较大,约为2倍 |
常规使用操作:
- MYISAM 节约空间,速度较快
- INNODB 安全性高,事物的处理,多表多用户操作
在物理空间存储的位置
所有的数据库文件都存在在data目录下
本质上还是文件的存储
MYSQL引擎在物理文件上的区别
- INNODB 在数据库中只有一个*.frm文件,以及上级目录下的 ibdata1文件
- MYISAM 对应文件
*.frm 表结构的定义文件 *.MYD 数据文件(data) *.MYI 索引文件(index)
设置数据库表的字符集编码
CHARSET=utf8
不设置的话,会是MySQL默认的字符集编码(不支持中文)
MySQL的默认编码是Latin1,不支持中文
在my.ini中配置默认编码
character-set-server=utf8
2.6 修改删除表
修改
-- 修改表名: ALTER TABLE 旧表名 REMANE AS 新表名
ALTER TABLE teacher REMANE AS tacher1
-- 增加表的字段: 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 agel INT(1) -- 字段重新命名
-- 删除表的字段 ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher1 DROP age1
删除
-- 删除表(如果表不存在)
DROP TABLE EXISTS teacher1
-- 所有的创建表和操作尽量加上判断 以免报错
注意
字段名,使用这个包裹
- 注释 – /**/
- SQL对大小写不敏感 建议小写
- 所有的符号全用英文
3、MYSQL数据管理
3.1 外键(了解)
方法一: 在创建表的时候,增加约束
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
-- 学生表的 gardeid 字段 去引用年级表的 gardeid
-- 定义外键
-- 给这个外键添加约束 (执行引用)
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 '出身日期',
`gardeid` INT(10) NOT NULL COMMENT '学生年级',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` 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
-- 学生表的 gardeid 字段 去引用年级表的 gardeid
-- 定义外键
-- 给这个外键添加约束 (执行引用)
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 '出身日期',
`gardeid` INT(10) NOT NULL COMMENT '学生年级',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` 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
-- 创建表的时候没有外键
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
-- ALTER TABLE 表
-- ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 那个表(哪个字段)
上面是物理外键,数据库级别的外键,避免数据库过多
最佳实现
- 数据库就是单纯的表,只用来存数据,只有行(数据)和列(字段)
- 使用多张表的数据,想使用外键(程序实现)
3.2 DML语言
DML语言: 数据操作语言
- Insert
- update
- delete
3.3 添加 (Insert)
语法 INSERT INTO 表名([字段1,字段2,字段3]) VALUES('值1','值2','值3','值4'....)
-- 语法
-- INSERT INTO 表名([字段1,字段2,字段3]) VALUES('值1','值2','值3','值4'....)
-- 例如
-- INSERT INTO `grade`(`gradename`) VALUES('大三')
-- 由于主键自增我们可以省略(如果不写表的字段,他会一一匹配)
INSERT INTO `grade` VALUES ('大三')
-- 一般插入语句,一定要数据和字段一一对应
-- 插入多个字段
INSERT INTO `grade`(`gradename`) VALUES ('大三') , ('大二')
-- 例题
INSERT INTO `student`(`name`) VALUES('大三')
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('张三','aaaaa','男')
INSERT INTO `student`(`name`,`pwd`,`sex`)
VALUES ('张三','aaaaa','男'),VALUES('王五','ddaq','男')
注意事项:
- 字段和字段之间使用 英文逗号 隔开
- 字段可以省略,但是后面的值必须一一对应
- 可以同时插入多条数据,VALUES 后的值需要逗号隔开 VALUES (),()…
3.4 修改(update)
修改谁 (条件) set 原来值 = 新值
语法:UPDATE 表名 set conlum_name = value,[conlum_name = value, ....] where [条件]
-- 语法
-- UPDATE 表名 set conlum_name = value,[conlum_name = value, ....] where [条件]
-- 修改名字,带了简介
UPDATE `student` SET `name`='张三' WHERE id = 1;
-- 不指定条件的情况下,会改动所有表
UPDATE `student` SET `name`='张三'
-- 修改多个属性
UPDATE `student` SET `name`='张三',email='123456@qq.com' WHERE id = 1;
-- 通过多个条件定位数据
UPDATE `student` SET `name`='张三' WHERE `name`='wangwu' AND sex ='男'
条件:where 子句 运算符 id等于某个值 大于某个值 在某个区间修改…
操作符 | 含义 | 范围 | 结果 |
---|---|---|---|
= | 等于 | 5=6 | false |
<> 或 != | 不等于 | 5 !=6 | true |
> | 大于 | 5>6 | false |
< | 小于 | 5<6 | true |
>= | 大于等于 | 5>=6 | false |
<= | 小于等于 | 5<=6 | true |
BETWEEN … AND… | 闭合区间,在某个范围内 | [2,5] | |
AND | && 两个条件都成立 | 5>1 and 1>2 | false |
OR | || 只有一个条件成立 | 5>1 or 1>2 | true |
注意:
- conlum_name 数据的列,尽量带``
- 条件,筛选条件, 如果指定则会删除所有的列
- value 是一个具体的值,也可以是一个变量
- 多个设置的属性,使用英文逗号隔开
UPDATE `student` SET `time`=CURRENT_TIME WHERE `name`='wangwu' AND sex ='男'
3.5 删除(delete)
DELETE 命令
语法DELETE FROM 表名 [WHERE 条件]
-- 语法
-- DELETE FROM 表名 [WHERE 条件]
-- 删除数据(数据全部删除)
DELETE FROM `student`
-- 删除指定数据
DELETE FROM `student`WHERE id = 1;
DELETE删除的问题
,重启数据库现象
- InnoDB: 自增列会重1开始 (存在内存中的,断电即失)
- MyISAM : 继续从上一个自增量开始 (存在文件中,不会丢失)
TRUNCATE 命令
完全清空一个数据库表,表的结构和索引约束不变
-- 清空表 TRUNCATE 表名
-- 清空student表 TRUNCATE `student`
DELETE 和 TRUNCATE 区别
- 同:都能删除数据,都不会删除表结构
- 不同:
TRUNCATE: 重新设置 自增列 计数器会归零
TRUNCATE: 不会影响事务
-- 测试DELETE 和 TRUNCATE 区别
CREATE TABLE `test`(
`id` INT(4) NOT NULL AUTO_INCREMENT,
'coll' VARCHAR(20) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=UTF8
INSERT INTO `test` (`coll`) VALUES('1'),('2'),('3')
DELETE FROM `test` -- 不会影响自增
TRUNCATE TABLE `test` -- 自增会归零
4、DOL查询数据(重重点)
4.1 DQL(数据查询语言)
-
查询操作 Select
-
简单和复杂的查询 ; 使用频率最高
-
**数据库中核心语言,最重要的语句 **
-
SELECT完整语法
- selecte 去重 要查询的字段 from表
- xxx join 要连续的表 on 等值判断
- where (具体的值,子查询语句)
- Group By (通过哪个字段分组)
- Having (过滤分组信息,条件和where一样,位置不同)
- Order By (通过哪个字段排序) 升序或降序
- Limit,Startindex ,pagesize
SELECT [ALL | DTSTINCT] {* | table.* | [table.field1[as alias1][table.field1[as alias1]][,[...]} 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} -- 指定查询的记录从哪条至哪条
selecte 去重 要查询的字段 from表 xxx join 要连续的表 on 等值判断 where (具体的值,子查询语句) Group By (通过哪个字段分组) Having (过滤分组信息,条件和where一样,位置不同) Order By (通过哪个字段排序) 升序或降序 Limit,Startindex ,pagesize
4.2 查询指定字段
语法:SELECT 字段, .... FROM 表
-- 查询全部
SELECT * FROM 表
-- 查询指定字段
SELECT 字段 FROM 表
-- 别名 给字段和表起别名
SELECT 字段 AS 别名,字段 AS 别名, ... FROM 表 AS 别名
-- 函数 Concat(a,b)
SELECT CONCAT('姓名:',StudentName) AS 新名字 FROM 表
去重 (distinct)
-- 去除SELECT查询出来的结果中重复的数据,重复的数据只显示一条
-- 查询去重步骤
SELECT * FROM 表
SELECT 字段 FROM 表
SELECT DISTINCT 字段 FROM 表
数据库的列(表达式)
语法 select 表达式 from 表
-- 查询系统版本(函数)
SELECT VERSION()
-- 用来计算(表达式)
SELECT 10*3 AS 计算结果
-- 查询自增的步长(变量)
SELECT @@auto_increment_increment
-- 例如;: 学员考试成绩 +1分查看
SELECT `StudentNO`,`StudentResult`+1 AS '提分后' FROM result
数据库表达式: 文本值,列 ,Null, 函数,计算表达式,系统变量。。。
4.3 where 条件子句
检索数据中 符合条件 的值
搜索的条件由一个多个表达式组成!结果 布尔值
逻辑运算符
运算符 | 语法 | 描述 |
---|---|---|
and && | a and b a&&b | 与, 两个为真,则都为真 |
or || | a or b a||b | 或, 其中一个为真,则结果为真 |
Not ! | not a ! a | 非, 真为假,假为真 |
-- 例如:
-- 查询考试成绩在95-100之间的
-- and &&
SELECT `StudentNO`,`StudentResult` FROM RESULT
WHERE StudentResult>=95 AND StudentResult<=100
-- 模糊查询(区间)
SELECT `StudentNO`,`StudentResult` FROM RESULT
WHERE StudentResult BETWEEN 95 AND 100
-- != NOT (除1000学号之外的成绩)
SELECT `StudentNO`,`StudentResult` FROM RESULT
WHERE NOT StudentNO = 100 -- WHERE StudentNO! = 100
模糊查询 比较运算符
运算符 | 语法 | 描述 |
---|---|---|
IS NULL | a is null | 如果操作符为NULL,结果为真 |
IS NOT NULL | a is not null | 如果操作符不为NULL,结果为真 |
BETWEEN | a between b and c | 若a在b和c之间,则结果为真 |
LIKE | a like b | SQL匹配,如果a匹配b,则结果为真 |
IN | a in(a1,a2,a3,…) | 假设a1,或者a2…其中一个值,结果为真 |
-- 例如
-- =======like %(代表0到任何字符) _(一个字符)========
SELECT `StudentNO`,`StudentResult` FROM `student`
-- 查询姓刘
WHERE StudentName LIKE '刘%'
-- 查询姓刘 名字后面有一个字
WHERE StudentName LIKE '刘_'
-- 查询姓刘 名字后面有两个字
WHERE StudentName LIKE '刘__'
-- 查询名字中带有嘉字 %嘉%
WHERE StudentName LIKE '%嘉%'
-- ===== in (具体的一个值或者多个值) ========
SELECT `StudentNO`,`StudentResult` FROM `student`
-- 查询 1001,1002,1003号学员
WHERE StudentNO IN (1001,1002,1003)
-- 查询 在北京的
WHERE `Address` IN ('北京')
-- ======= null not null ===========
SELECT `StudentNO`,`StudentResult` FROM `student`
-- 查询地址为空 null ''
WHERE adress='' OR adrress IS NULL
-- 查询出生日期 不为空
WHERE `BornDate` IS NOT NULL
-- 查询没有出生日期 不为空
WHERE `BornDate` IS NULL
4.4 联表查询(join)
JOIN
语法 FORM 表 XXX Join 连接的表 on 交叉条件
-- 分析查询字段来自那些 表 (连接查询) select ...
-- 确定使用那种连接查询
-- 确定交叉点(这两个表中那个数据是相同的)
-- 判断条件: 表1 A = 表2 A
-- join(连接的表) on(判断的条件) 练级查询
-- where 等值查询
-- 多表查询时,先查询两表然后再慢慢增加
-- ========= inner join =============
SELECT 需要查询的内容(A.字段1,字段2,字段3,字段4,....) (表1,2中均有字段1)
FROM 表1 AS 别名1(A)
INNER JION 表2 AS 别名2(B)
WHERE A.字段1 = B.字段1
-- ==========Right join============
SELECT 需要查询的内容(A.字段1,字段2,字段3,字段4,....) (表1,2中均有字段1)
FROM 表1 AS 别名1(A)
RIGHT JION 表2 AS 别名2(B)
ON A.字段1 = B.字段1
-- ==========left join===============
SELECT 需要查询的内容(A.字段1,字段2,字段3,字段4,....) (表1,2中均有字段1)
FROM 表1 AS 别名1(A)
LEFT JION 表2 AS 别名2(B)
ON A.字段1 = B.字段1
操作 | 描述 |
---|---|
Inner join | 如果表中至少有一个匹配,就返回行 |
left join | 会从左表中返回所有值,即使在右表中没有匹配 |
right join | 会从右表中返回所有值,即使在左表中没有匹配 |
自连接
一张表拆为两张一样的表即可
-- 查询父子信息: 把一张表看为两个一模一样的表
SELECT a.`categoryName` AS '父栏目',b.``categoryName` AS '子栏目'`
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.`pid`
4.5 分页 (limit)和 排序 (order by)
排序
-- 排序: 升序 ASC , 降序 DESC
-- 语法
ORDER BY 需要排序的字段 ASC
分页
- 缓解数据库压力
-- limit 在所有语句最后
-- 语法
LIMIT 查询起始下标,页面大小(pageSize)
-- pageSize : 页面大小
-- (n-1)* pageSize: 起始值
-- n: 当前页面
-- 数据总数/页面大小 = 总页数
4.6 子查询和嵌套查询
子查询
在where语句中嵌套一个子查询语句
-- ========= where ===============
-- 方式一: 使用连接查询
SELECT 字段1, A.字段2, 字段3
FROM 表1 AS 别名1(A)
INNER JOIN 表2 AS 别名2
ON A.表2 AS 别名2.表2
WHERE 字段2 = '查询内容'
ORDER BY 字段三 DESC
-- 方式二: 使用子查询()
SELECT 字段1, 字段2, 字段3
FROM 表1
WHERE 字段 = ()
4.7 分组过滤
-- 查询不同表之间的平均值 ,最高值 ,最低值 平均分>80
-- 核心: (根据不同的课程分组)
SELECT 字段1, AVG(字段2) AS 平均值 ,MAX(字段2) AS 最高值,MIN(字段2) AS 最低值
FROM 表1 别名1
INNER JOIN 表2 别名2
ON 别名1.字段3 = 别名2.字段3
WHERE AVG(字段2)>80
GROUP BY 别名1.字段3 -- 通过什么字段来分组
5、MYSQL函数
5.1常用函数
-- 数学运算
SELECT ABS() -- 绝对值
SELECT CEILING() -- 向上取整
SELECT FLOOR() -- 向下取整
SELECT RAND() -- 返回一个 0~1之间的随机数
SELECT SING() -- 判断一个数的符号 负数返回-1 正数返回1
-- 字符串函数
SELECT CHAR_LENGTH(' ') -- 字符串长度
SELECT CONCAT('',' ','') -- 拼接字符串
SELECT INSERT('',1,2,'') -- 查询,从某个位置 开始替换某个长度
SELECT LOWER('') -- 小写字母
SELECT UPPER('') -- 大写字母
SELECT INSTR('','') -- 返回子字符串第一次出现的索引
SELECT REPLACE('','','') -- 替换出现的指定字符串
SELECT SUBSTR('', , ) -- 返回指定的字符串 (源字符串,截取的位置,截取的长度)
SELECT REVERSE('') -- 反转
-- 时间和日期函数
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURRENT() -- 获取当前的日期
SELECT NOW() -- 获取当前的时间
SELECT LOCALTIME() -- 本地时间
SELECT SYSDATE() -- 系统时间
-- 系统
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()
5.2 聚合函数(常用)
函数名称 | 描述 |
---|---|
COUNT() | 计数 |
SUM() | 求和 |
AVG() | 平均值 |
MAX() | 最大值 |
MIN() | 最小值 |
… | … |
-- COUNT() 计数 【能够统计表中的数据】
SELECT COUNT(字段) FROM 表; -- 会忽略所有的 null 值
SELECT COUNT(*) FROM 表; -- 不会忽略 null 值 ,本质 计算行数
SELECT COUNT(1) FROM 表; -- 不会忽略所有的 null 值 ,本质 计算行数
-- SUM() 求和
SELECT SUM(字段) AS总和 FROM 表;
-- AVG() 平均值
SELECT AVG(字段) AS 平均值 FROM 表;
-- MAX() 最大值
SELECT MAX(字段) AS 最大值 FROM 表;
-- MIN() 最小值
SELECT MIN(字段) AS 最小值 FROM 表;
5.3数据库级别的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 VALUES(1,'zhangsan','123'),(2,'qqqqq','123'),(3,'wwww','123')
-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id = 1
-- 加密全部密码
UPDATE testmd5 SET pwd=MD5(pwd)
-- 插入时加密
INSERT INTO testmd5 VALUES(4,'ss',MD5('123'))
-- 如何校验: 将用户传递进来的密码,进行md5加密。然后比对加密后的值
SELECT * FROM testmd5 WHERE `name`='ss' AND pwd=MD5('123')
6、 事务
- 要么成功,要么失败
- 将一组SQL放在一个批次中执行
事务原则 : ACID原则
-
原子性(Atomicity): 要么都发生,要么都不发生。
-
一致性 (Consistency) : 事务前后的数据完整要保持一致
-
隔离性(Isolation) :
指一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的,并发执行的各个事务之间不能互相干扰。 -
持续性(Durability) :
指一个事务一旦提交,它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。
事务的隔离级别
- 脏读 : 指一个事务读取了另外一个事务未提交的数据。
- 不可重复读 : 指一个事务读取了另外一个事务未提交的数据。
- 虚读(幻读): 是指在一个事务内读取到了别的事务插入的数据,导致前后读取数量总量不一致。
执行事务
SET autocommit = 0 /*关闭*/
SET autocommit = 1 /*开启*/
-- 手动处理事务
SET autocommit = 0 -- 关闭自动提交
-- 事务开启
START TRANSACTION -- 标记一个事务的开始,从这个之后的 sql 都在同一个事务内
INSERT xx
INSERT xx
-- 提交 : 持久化 (成功!)
COMMIT
-- 回滚: 回到原来的样子
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 开启自动提交
SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点
测试事务实现转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USER shop
CREATE TABLE `account`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO account(`name`,`money`)
VALUES ('A',2000.00),('B',1000.00)
-- 模拟转账
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION -- 开启一个事务
UPDATE account SET money=money-500 WHERE `name` = 'A' -- A减500
UPDATE account SET money=money+500 WHERE `name` = 'B' -- A加500
COMMIT; -- 提交事务
ROLLBACK; -- 回滚
SET autocommit = 1; -- 恢复默认值
7、 索引
- 官方介绍索引是帮助MySQL高效获取数据的数据结构
7.1 索引类型
分类
在一个表中,主键索引只能有一个,唯一索引可以有多个
-
主键索引(PRIMAPY KEY)
- 索引列中的值必须是唯一的,不允许有空值。
-
常规索引(KEY/INDEX)
- 默认的,index。key 关键字来设置
-
唯一索引 (UNIQUE KEY)
- 索引列中的值必须是唯一的,但是允许为空值。
- 避免重复列的出现,可以重复,多个列都可以标识位
-
全文索引(FullText)
- 只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。
- 字段长度比较大时,如果创建普通索引,在进行like模糊查询时效率比较低,这时可以创建全文索引。
- MyISAM和InnoDB中都可以使用全文索引。
- 快速定位诗句
基础语法
-- 索引的使用 -- 1. 在创建表的时候给字段增加索引 -- 2. 创建完毕,增加索引 -- 显示所有的索引信息 SHOW INDEX FROM 表 -- 增加一个全文索引 (索引名) 列名 ALTER TABLE 数据库名.表 ADD FULLTEXT INDEX 字段1(字段1); -- EXPLAIN 分析sql执行情况 EXPLAIN SELECT * FROM 表; -- 非全文索引 EXPLAIN SELECT * FROM 表 WHERE MATCH(字段) AGAINST('索引类型 ');
索引原则
SQL 命令操作
读这张表进行增删改查
-- 创建用户
CREATE USER 用户名 IDENTIFIED BY '密码'
-- 修改当前用户密码
SET PASSWORD = PASSWORD('修改的密码')
-- 修改指定用户密码
SET PASSWORD FOR 用户名 = PASSWORD('修改的密码')
-- 重命名(用户名)
RENAME USER 用户名 TO 新的用户名
-- 用户授权 全部权限 GRANT ALL PRIVIEGES ON *库 表* TO 要授权的用户名
GRANT ALL PRIVIEGES ON *.* TO 要授权的用户名
-- 查询权限
SHOW GRANTS FOR 用户名 -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost
-- 撤销权限 库.
PEVOKE ALL PRIVILEGES ON *.* FROM 要删除的名
-- 删除用户
DROP USER 用户
MYSQL 备份
-- 使用命令行导出 mysqldump 命令行使用
mysqldump -主机 -u用户名 -p密码 数据库 表名 >物理磁盘位置/文件名
mysqldump -主机 -u用户名 -p密码 数据库 表1 表2 表3 >物理磁盘位置/文件
mysqldump -主机 -u用户名 -p密码 数据库 >物理磁盘位置/文件
-- 导入
# 登录情况下,切换到指定的数据库
# source 备份文件
source 物理磁盘位置/文件
# 未登录下,要先登录再导入
mysql -u用户名 -p密码 库名< 备份文件
source 物理磁盘位置/文件
9、 规范数据库设计
9.1 数据库设计
数据库的设计
- 分析需求: 分析业务和需要处理的数据库的需求
- 概要设计: 设计关系图 E-R 图
设计数据库的步骤:(个人博客)
收集信息,分析需求
- 用户表 (用户登录注销,用户的个人信息,写博客,分类)
- 分类表 (文章分类,谁创建)
- 文章表 (文章信息)
- 评论表
- 友链表(友链信息)
- 自定义表 (系统信息,某个字的关键的字,或者一些主字段) key: value
标记实体 (把需求落地到每一个字段)
标记实体之间的关系
- 博客: user–> blog
- 创建分类: user–>category
- 关注: user->user
- 友链: links
- 评论: user-user-blog
9.2 三大范式
第一范式(1NF):
-
属性不可再分,即表中的每个列都不可以再进行拆分。
-
如下学生成绩表(score):
第二范式(2NF):
- 在满足1NF的前提下,表中不存在部分依赖,非主键列要完全依赖于主键。(主要是说在联合主键的情况下,非主键列不能只依赖于主键的一部分)
第三范式(3NF):
- 满足第二范式;且不存在传递依赖,即非主属性不能与非主属性之间有依赖关系,非主属性必须直接依赖于主属性,不能间接依赖主属性。(A -> B, B ->C, A -> C)
规范性和性能问题
关联查询的表不得超过三张表
- 考虑商业化的需求和目标,数据的性能尤其重要
- 在规范性能的问题的时候,需要适当的考虑一下 规范性
- 故意给某些表增加一些冗余的字段。(从多表查询中变为单表查询)
- 故意增加一些计算列(从大数据量降低为小数据量的查询: 索引)
10、JDBC(重)
数据库驱动
**程序会通过 数据库 驱动 **
JDBC (java操作数据数据库)
-
需要掌握JDBC接口操作
-
需要两个包 java.sql; javax.sql;
-
还需要导入一个数据库驱动包
10.1 第一个JDBC程序
1、创建测试数据库
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;
USE jdbcStudy;
CREATE TABLE users(
id INT PRIMARY KEY,
NAME VARCHAR(40),
PASSWORD VARCHAR(60),
email VARCHAR(60),
birthday DATE
);
INSERT INTO users(id,NAME,PASSWORD,email,birthday)
VALUES(1,'zhangsan','123456','za@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@lisi.com','1981-12-04'),
(3,'wangwu','123456','wangwu@wangwu.com','1979-12-04');
2、 导入数据库驱动
编译测试代码
package com.demo.lesson01;
import java.sql.*;
// 第一个JDBC 程序
public class JdbcFirstDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1. 加载驱动
Class.forName("com.mysql.jdbc.Driver"); // 固定写法,加载驱动
//2. 用户信息 和 url useUnicode=true&cahracterEncoding=utf8&&useSSL=true
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&cahracterEncoding=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();
}
}
步骤
- 加载驱动
- 连接数据库 DriverManager
- 获得执行SQL的对象 Statement
- 获得返回值
- 释放连接
JDBC的对象解释
DriverManager
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver"); // 固定写法,加载驱动
Connection connection = DriverManager.getConnection(url, username, passWord);
//Connection 代表数据库
//数据库设置自动提交
//事务提交
//事务回滚
connection.rollback();
connection.commit();
connection.setAutoCommit();
URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&cahracterEncoding=utf8&&useSSL=true";
//mysql -- 3306
//jdbc:mysql(协议)://主机地址:端口号、数据库名?参数1&参数2&参数3
//oralce --1521
//jdbc:oralce:thin:@localhost:1521:sid
**Statement 执行SQL对象 PrepareStatement 执行SQL的对象 **
String sql = "SELECT * FROM users";//编写SQL
statement.executeQuery(); //查询操作返回 ResultSet
statement.execute();//执行任何SQL
statement.executeUpdate();//更新,插入,删除,都是用这个,返回一个受影响的行数
ResultSet 查询结果集:封装了所有的结果
//在不知道类型的情况下使用
resultSet.getObject();
//如果知道列的类型就使用指定类型
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
resultSet.getObject();
.....
遍历 指针
resultSet.beforeFrist();//移动到最前面
resultSet.afterLast();//移动到最后面
resultSet.next();//移动到下一个数据
resultSet.previous();//移动到前一行
resultSet.absolute();//移动到指定行
释放资源
//6. 释放连接
resultSet.close();
statement.close();
connection.close();
10.2 statement对象
- **jdbc中的statement对象用于向数据库发送SQL语句,想完成对数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句即可 **
- Statement对象的executeUpdate方法,用于向数据库发送增,删,改的sql语句,executeUpdate执行完后,将返回一个整数(即增删改语句导致了数据库几行数据发生了变化)
- Statement.executeQuery方法用于向数据库发送查询语句,executeQuery方法返回代表查询结果的ResultSet对象。
CRUD操作-create
使用executeUpdate(String sql)方法完成数据库添加操作
// 示例操作:
Statement st = conn.createsStatement();
String sql = "insert into user(...) values(...)";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("插入成功!");
}
CRUD操作-delete
使用executeUpdate(String sql)方法完成数据库删除操作
// 示例操作:
Statement st = conn.createsStatement();
String sql = "delete from user where id=1";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("删除成功!");
}
CRUD操作-update
使用executeUpdate(String sql)方法完成数据库修改操作
// 示例操作:
Statement st = conn.createsStatement();
String sql = "update user set name ='' where name =''";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("修改成功!");
}
CRUD操作-read
使用executeUpdate(String sql)方法完成数据库查询操作
// 示例操作:
Statement st = conn.createsStatement();
String sql = "select * from user where id=1";
int num = st.executeQuery(sql);
if(num>0){
System.out.println("查询成功!");
}
代码实现
src下创建一个文件 db.properties
driver=com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&&useSSL=true
username=root
password=123456
1.src下,创建一个lesson包,放提取工具类 jdbcUtils
package com.demo.lesson02.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class jdbcUtils {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static {
try {
InputStream in = jdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(in);
driver=properties.getProperty("driver");
url=properties.getProperty("url");
username=properties.getProperty("username");
password=properties.getProperty("password");
//1.驱动只用加载一次
Class.forName(driver);
} catch (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 e) {
e.printStackTrace();
}
}
if (st!=null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
2. 编写增删改的方法 executeUpdate
-
增方法
TestInsertpackage com.demo.lesson02; import com.demo.lesson02.utils.jdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestInsert { public static void main(String[] args) { Connection conn = null; Statement st =null; ResultSet rs =null;
try { conn = jdbcUtils.getConnection();//获取数据库连接 st = conn.createStatement();//获得SQL的执行对象 String sql = "INSERT INTO users(id,`name`,`password`,`email`,`birthday`)" + "VALUES(4,'aaa','123456','aaa@sina.com','2001-01-01')"; int i = st.executeUpdate(sql); if (i>0){ System.out.println("插入成功!!"); } } catch (SQLException e) { e.printStackTrace(); }finally { jdbcUtils.release(conn,st,rs); }
}
}
* `删方法`**TestDelete**
```java
package com.demo.lesson02;
import com.demo.lesson02.utils.jdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDelete {
public static void main(String[] args) {
Connection conn = null;
Statement st =null;
ResultSet rs =null;
try {
conn = jdbcUtils.getConnection();//获取数据库连接
st = conn.createStatement();//获得SQL的执行对象
String sql = "DELETE FROM users WHERE id=4";
int i = st.executeUpdate(sql);
if (i>0){
System.out.println("删除成功!!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
jdbcUtils.release(conn,st,rs);
}
}
}
改方法
TestUpdate
package com.demo.lesson02;
import com.demo.lesson02.utils.jdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestUpdate {
public static void main(String[] args) {
Connection conn = null;
Statement st =null;
ResultSet rs =null;
try {
conn = jdbcUtils.getConnection();//获取数据库连接
st = conn.createStatement();//获得SQL的执行对象
String sql = "UPDATE users SET `name`='asdf',`email`='aaaaa@qq.com' WHERE id=1";
int i = st.executeUpdate(sql);
if (i>0){
System.out.println("更新成功!!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
jdbcUtils.release(conn,st,rs);
}
}
}
3. 编写查询的方法executeQuery
package com.demo.lesson02;
import com.demo.lesson02.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 conn = null;
Statement st =null;
ResultSet rs =null;
try {
conn=jdbcUtils.getConnection();
st =conn.createStatement();
//SQL
String sql = "select * from users where id=1";
rs = st.executeQuery(sql);//查询完毕会返回一个结果集
while (rs.next()){
System.out.println(rs.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
jdbcUtils.release(conn,st,rs);
}
}
}
SQL注入问题
-
SQL注入攻击是通过操作输入来修改SQL语句,用以达到执行代码对WEB服务器进行攻击的方法。简单的说就是在post/getweb表单、输入域名或页面请求的查询字符串中插入SQL命令,最终使web服务器执行恶意命令的过程。
-
sql存在漏洞 ,会被攻击导致数据泄漏, SQL会拼接 or
package com.demo.lesson02.utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SQLzhuru {
public static void main(String[] args) {
//正常查询
login("lisi","123456");
//SQL注入查询
login("‘or ’1=1","‘or’1=1");
}
//登录业务
public static void login(String username,String password){
Connection conn = null;
Statement st =null;
ResultSet rs =null;
try {
conn=jdbcUtils.getConnection();
st =conn.createStatement();
//SELECT * FROM users WHERE `name` = 'lisi' AND `password` = '123456';
//SELECT * FROM users WHERE `name` = ' ' or '1=1' AND `password` =''or '1=1';
String sql = "select * from users where `name` ='"+username+"' and `password` ='"+password+"'";
rs = st.executeQuery(sql);//查询完毕会返回一个结果集
while (rs.next()){
System.out.println(rs.getString("name"));
System.out.println(rs.getString("password"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
jdbcUtils.release(conn,st,rs);
}
}
}
10.3 PreparedStatement对象
-
PreparedStatement 可以防止SQL注入!
新增
package com.demo.lesson3; import com.demo.lesson02.utils.jdbcUtils; import java.sql.Connection; import java.util.Date; import java.sql.PreparedStatement; import java.sql.SQLException; public class TestInsert { public static void main(String[] args) { Connection conn = null; PreparedStatement st = null; try { conn = jdbcUtils.getConnection(); //区别: //使用? 占位符代替参数 String sql = "insert into users(id,`name`,`password`,`email`,`birthday`) values(?,?,?,?,?)"; st = conn.prepareStatement(sql); //预编译SQl 先写SQL 然后执行 //手动赋值 st.setInt(1,4); st.setString(2,"qqqq"); st.setString(3,"123456"); st.setString(4,"qqqq@qq.com"); //email // 注意; sql.date 数据库 java.sql.Date() // util.data java new Date().getTime() 获得时间戳 st.setDate(5,new java.sql.Date(new Date().getTime())); //执行 int i = st.executeUpdate(); if (i>0){ System.out.println("插入成功!"); } } catch (SQLException e) { e.printStackTrace(); }finally { jdbcUtils.release(conn,st,null); } } }
删除
package com.demo.lesson3; import com.demo.lesson02.utils.jdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.Date; public class TestDelete { public static void main(String[] args) { Connection conn = null; PreparedStatement st = null; try { conn = jdbcUtils.getConnection(); //区别: //使用? 占位符代替参数 String sql = "delete from users where id=?"; st = conn.prepareStatement(sql); //预编译SQl 先写SQL 然后执行 //手动赋值 st.setInt(1,4); //执行 int i = st.executeUpdate(); if (i>0){ System.out.println("删除成功!"); } } catch (SQLException e) { e.printStackTrace(); }finally { jdbcUtils.release(conn,st,null); } } }
更新
package com.demo.lesson3; import com.demo.lesson02.utils.jdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; public class TestUpdate { public static void main(String[] args) { Connection conn = null; PreparedStatement st = null; try { conn = jdbcUtils.getConnection(); //区别: //使用? 占位符代替参数 String sql = "update users set `name` =? where id=?"; st = conn.prepareStatement(sql); //预编译SQl 先写SQL 然后执行 //手动赋值 st.setString(1,"santian"); st.setInt(2,1); //执行 int i = st.executeUpdate(); if (i>0){ System.out.println("更新成功!"); } } catch (SQLException e) { e.printStackTrace(); }finally { jdbcUtils.release(conn,st,null); } } }
查询
package com.demo.lesson3; import com.demo.lesson02.utils.jdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class TestSelect { public static void main(String[] args) { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try { conn = jdbcUtils.getConnection(); String sql ="select * from users where id+?"; //编写SQL st = conn.prepareStatement(sql); // 预编译 st.setInt(1,1);//传递参数 //执行 rs = st.executeQuery(); if (rs.next()){ System.out.println(rs.getString("name")); } } catch (SQLException e) { e.printStackTrace(); }finally { jdbcUtils.release(conn,st,rs); } } }
防止SQL注入
package com.demo.lesson3; import com.demo.lesson02.utils.jdbcUtils; import java.sql.*; public class SQLzhuru { public static void main(String[] args) { login("lisi","123456"); //SQL注入查询 //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 { conn= jdbcUtils.getConnection(); // PreparedStatement 防止SQL注入的本质,把传递进来的参数当做字符 // 假设其中存在转义字符 ‘ 会被直接转义 String sql = "select * from users where `name` =? and `password` =?";//Mybatis st = conn.prepareStatement(sql); st.setString(1,username); st.setString(2,password); rs = st.executeQuery(); while (rs.next()){ System.out.println(rs.getString("name")); System.out.println(rs.getString("password")); } } catch (SQLException e) { e.printStackTrace(); }finally { jdbcUtils. release(conn,st,rs); } } }
10.4 事务
要么失败,要么成功
ACID原则
- 原子性 : 要么都完成 ,要么都不完成
- 一致性: 总数不变
- 隔离性: 多个进程互不干扰
- 持久性:一旦提交不可逆,持久化到数据库
隔离性问题
- 脏读: 一个事务读取了另一个没有提交的事务
- 不可重复读: 在同一个事务内,重复读取表中的数据,表数据发生了改变、
- 虚读(幻读):在一个事务内,读取到别人插入的数据,导致前后读出的数据结果不一致
代码实现
- 开启事务
conn.setAutoCommit(false);
- 一组业务执行完毕,提交事务
- 可以在catch 语句中显示的定义,回滚语句,但默认就会回滚
package com.demo.lesson4;
import com.demo.lesson02.utils.jdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransaction1 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs =null;
try {
conn= jdbcUtils.getConnection();
//关闭数据库的自动提交,自动开起事务
conn.setAutoCommit(false);
String sql1 = "update accout set money = money-100 where `name` ='A'";
st = conn.prepareStatement(sql1);
st.executeUpdate();
String sql2 = "update accout set money = money+100 where `name` ='B'";
st = conn.prepareStatement(sql2);
st.executeUpdate();
//业务完毕,提交事务
conn.commit();
System.out.println("成功!");
} catch (SQLException e) {
e.printStackTrace();
}finally {
jdbcUtils.release(conn,st,rs);
}
}
}
10.5 数据库连接池
- 数据库连接—执行完毕—释放
- 连接–释放
- 池化技术:准备一些预先的资源,过来就连接预先准备好的
连接方案
- DBCP C3P0 Druid
- 使用这些数据库连接池后,我们在项目开发中就不需要再编写连接数据库的代码!
#### DBCP
DBCP常见属性有:
- driver:数据库驱动,如 com.mysql.jdbc.Driver
- url:数据库连接,如 jdbc:mysql://localhost:3306/testDB
- username:数据库用户名,如 root
- password:数据库用户名口令,如 rootinitsize:初始化连接池大小
- minidle:最小空闲连接
- maxidle:最大空闲连接。以maxidle=15为例,它表示的在并发的情况下,连接池就必须从数据库中获取 15 个连接来供应程序使用,当应用连接关闭之后,由于 maxidle=10 并不是所有的连接都归还给了数据库,将会有 10 连接保存在连接池中,这时的状态就是空闲。
- maxactive:最大连接, 表示在并发情况下最大能从数据连接池中获取的连接数
- maxwait:最大等待时间
- removeabandoned:是否自动回收超时连接, boolean 类型( true/false )
DBCP数据库连接池的用法
- 创建项目在其中导入mysql的驱动以及DBCP相关的jar文件,然后在项目类路径下创建一个名为dbcpcfg.properties的DBCP配置文件
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/book?useSSL=true&serverTimezone=UTC&characterEncoding=utf8
username=root
password=root
initialSize=4
maxActive=6
maxIdle=5
minIdle=2
maxWait=6000
- 创建DBCP工具类
public class DBUtil {
private static DataSource dataSource;
private DBUtils() {
}
static{
try {
InputStream in = DBCPUtil.class.getClassLoader()
.getResourceAsStream("dbcpcfg.properties");//读取配置文件,初始化数据源
Properties props = new Properties();
props.load(in);
dataSource = BasicDataSourceFactory.createDataSource(props);
} catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}
public static DataSource getDataSource(){
return dataSource;
}
public static Connection getConnection(){
try {
return dataSource.getConnection();
} catch (SQLException e) {
throw new RuntimeException("get database connection failed");
}
}
}
- 测试代码:
public static void main(String[] args) {
for(int i =0;i<8;i++) {
Connection conn = DBUtil.getConnection();
System.out.println(i+": "+conn);
try {
// conn.close(); // ------ ①
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//运行测试代码,当将编号①处的代码注释起来时,在控制台中输出6个Connection,过了一会(大约6秒)程序报错关闭
//当让编号①处的代码执行时,程序会在控制台上输出10个Connection对象。
#### C3P0
- 导入jar包,在pom文件中添加如下内容:
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.2</version>
</dependency>
- 配置xml文件
<c3p0-config>
<named-config name="mysqlapp">
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://192.168.153.134:3306/new</property>
<property name="user">root</property>
<property name="password">123123</property>
<!-- 进行数据库连接池管理的基本信息 -->
<!-- 当数据库连接池中的连接数不够时,c3p0一次性向数据库服务器申请的连接数 -->
<property name="acquireIncrement">5</property>
<!-- c3p0数据库连接池中初始化时的连接数 -->
<property name="initialPoolSize">10</property>
<!-- c3p0数据库连接池维护的最少连接数 -->
<property name="minPoolSize">10</property>
<!-- c3p0数据库连接池维护的最多的连接数 -->
<property name="maxPoolSize">200</property>
<!-- c3p0数据库连接池最多维护的Statement的个数 -->
<property name="maxStatements">50</property>
<!-- 每个连接中可以最多使用的Statement的个数 -->
<property name="maxStatementsPerConnection">2</property>
</named-config>
</c3p0-config>
- 创建C3P0Util类
// C3p0连接池
public class C3p0Util {
private static DataSource dataSource = null;
static{
dataSource = new ComboPooledDataSource("mysqlapp");
}
//从连接池中获取连接
public static Connection getConnection(){
try {
return dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace(); }
return null;
}
//释放连接回连接池
public static void release(Connection conn, Statement stmt, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
rs = null;
}
if (stmt != null) {
try {
stmt.close();
} catch (Exception e) {
e.printStackTrace();
}
stmt = null;
}
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
conn = null;
}
}
}
- 测试代码
public static void main(String[] args) {
BaseDao2<Dog> dao = new BaseDao2<>();
System.out.println(Timestamp.valueOf(LocalDateTime.now()));
for(int i=0;i<100;i++){
Connection connection = getConnection();
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
System.out.println(Timestamp.valueOf(LocalDateTime.now()));
}
#### Druid
看下Druid 的数据库连接池的配置:
<property name="driverClassName" value="${db.driverClassName}"/>
<property name="url" value="${db.url}"/>
<property name="username" value="${db.username}"/>
<property name="password" value="${db.password}"/>
<property name="initialSize" value="5"/>
<property name="maxIdle" value="10"/>
<property name="minIdle" value="5"/>
<property name="maxActive" value="15"/>
<property name="removeAbandoned" value="true"/>
<property name="removeAbandonedTimeout" value="180"/>
<property name="maxWait" value="3000"/>
<property name="validationQuery">
<value>SELECT 1</value>
</property>
<property name="testOnBorrow">
<value>true</value>
</property>
</bean>
-
driverClassName 使用的JDBC驱动的完整有效的Java类名,如连接 mysql com.mysql.cj.jdbc.Driver
-
jdbcUrl 数据库的连接。如 jdbc:mysql://127.0.0.1:3306/mydatabase
-
username 你懂的,数据库的用户名,如 root
-
password 太直白了 ,数据库的用户密码,如 p123456
-
initialSize 连接池创建的时候,自动创建的数据库连接数量,建议 10-50足够
-
maxIdle 最大空闲连接:连接池中允许保持空闲状态的最大连接数量,超过的空闲连接将被释放,如果设置为负数表示不限制,建议设置和 与initialSize相同,减少释放和创建的性能损耗。
-
minIdle 最小空闲连接:连接池中容许保持空闲状态的最小连接数量,低于这个数量将创建新的连接,如果设置为0则不创建
-
maxActive 最大同时激活的连接数量。
-
maxWait 如果连接池中没有可用的连接,最大的等待时间,超时则没有可用连接,单位毫秒,设置-1时表示无限等待,建议设置为100毫秒
-
testxxx 在对连接进行操作时,是否检测连接的有效性,如 testOnBorrow 在申请连接的时候会先检测连接的有效性,执行validationQuery ,建议线上的把此配置设置为false,因为会影响性能。
-
validationQuery 检查池中的连接是否仍可用的 SQL 语句,drui会连接到数据库执行该SQL, 如果正常返回,则表示连接可用,否则表示连接不可用,建议 select 1 from dual
创建连接池
- pom.xml 加入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.6</version>
</dependency>
- 配置文件
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
<!-- 基本属性 url、user、password -->
<property name="driverClassName" value="${jdbc.driver}" />
<property name="url" value="${jdbc_url}" />
<property name="username" value="${jdbc_user}" />
<property name="password" value="${jdbc_password}" />
<!-- 配置监控统计拦截的filters -->
<property name="filters" value="stat" />
<!-- 配置初始化大小、最小、最大 -->
<property name="maxActive" value="20" />
<property name="initialSize" value="1" />
<property name="minIdle" value="1" />
<!-- 配置获取连接等待超时的时间 -->
<property name="maxWait" value="60000" />
<!-- 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 -->
<property name="timeBetweenEvictionRunsMillis" value="60000" />
<!-- 配置一个连接在池中最小生存的时间,单位是毫秒 -->
<property name="minEvictableIdleTimeMillis" value="300000" />
<property name="testWhileIdle" value="true" />
<property name="testOnBorrow" value="false" />
<property name="testOnReturn" value="false" />
<!-- 打开PSCache,并且指定每个连接上PSCache的大小 -->
<property name="poolPreparedStatements" value="true" />
<property name="maxOpenPreparedStatements" value="20" />
</bean>
<!--配置jdbcTemplate,如果userDao没有extends JdbcDaoSupport-->
<<bean id="jdbcTemplate" class="com.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
<bean id="userDao" class="com.caraway.dao.UserDao">
<property name="dataSource" ref="jdbcTemplate"/>
</bean>
- 代码实现
public static void main(String[] args) {
ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
UserDao userDao = (UserDao) context.getBean("userDao");
User user = new User();
user.setUsername("香菜");
user.setPassowrd("root");
userDao.saveUser(user);
}