本文详细介绍了MySQL的安装、配置、数据库和数据表的管理，以及数据操作语言（DML）、数据查询语言（DQL）的使用。讨论了事务处理的ACID原则和四种事务隔离级别，讲解了索引的原理和使用，以及JDBC连接数据库的基本操作。此外，还探讨了数据库设计的规范性和防止SQL注入的方法。
MySQL
一、简介
1.1、安装
-
解压
-
把文件夹放到电脑环境目录下
-
配置环境变量
-
新建mysql配置文件ini
[mysql] # 设置mysql客户端默认字符集 default-character-set=utf8 [mysqld] #设置3306端口 port = 3306 # 设置mysql的安装目录 basedir=D:\mysql\mysql-5.6.33-winx64\ # 设置mysql数据库的数据的存放目录 datadir=D:\mysql\mysql-5.6.33-winx64\data\ # 允许最大连接数 max_connections=200 # 服务端使用的字符集默认为8比特编码的latin1字符集 character-set-server=utf8 # 创建新表时将使用的默认存储引擎 default-storage-engine=INNODB -
启动管理员下的CMD，运行Mysql安装
-
安装mysql服务：mysqld -install
-
初始化数据库：mysqld --initialize-insecure --user=mysql
-
启动mysql，修改密码：set password for root@localhost = password(‘123456’);
-
如果ini文件中有跳过密码检查，要删除或者注释掉
-
删除数据库：sc delete mysql
1.2、什么是数据库
数据库（DB，DataBase）
概念：数据仓库，软件，安装在操作系统（windows，linux，mac…）之上，可以存储大量数据
1.3、数据库分类
关系型数据库（SQL）：有行和列
- MySQL、Oracle、Sql Server、DB2、SQLite
- 通过表和表之间，行和列之间的关系进行数据存储
非关系型数据库（No SQL）：键值对
- Redis，MongDB
- 非关系型数据库，对象存储，通过对象自身属性来决定
DBMS（数据库管理系统）
- 数据库管理软件，科学有效的管理数据，维护和获取数据
1.4、MySQL三层结构
DBMS（数据库管理系统） -> 数据库 -> 数据表
-
所谓安装数据库就是在主机装一个数据库管理系统（DBMS），管理程序可以管理多个数据库（DataBaseManageSystem）
-
一个数据库可以建多个表，用来保存信息
表的一行称之为一条记录，在Java程序中往往用对象表示
1.5、连接数据库
命令行：所有语句以分号结尾
mysql -uroot -p123456
update mysql.user set authenticantion_string=password('123456') where user='roor' and Host='localhost' --修改密码
flush privileges; --刷新权限
exit; --退出连接
-- 单行注释
/*
多行注释
*/
DDL：数据库定义语言
DML：数据库操作语言
DQL：数据库查询语言
DCL：数据库控制语言
二、操作数据库
操作数据库 > 操作数据表 >操作数据
mysql不区分大小写，如果表名或者字段名是特殊字符需要带 ``
2.1、操作数据库
增删改查数据库
create database if not exists 数据库名;
drop database 数据库名;
use database 数据库名;
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
- tinytext 微型文本 2^8-1
- text 文本串 2^16-1
时间日期
java.util.Data
- date YYYY-MM-DD 日期格式
- time HH:mm:ss 时间格式
- datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
- timestamp 时间戳 1970.1.1到现在的毫秒数
null
空值，不要使用null进行运算
2.3、数据库字段属性
Unsigned：无符号整数
- 声明之后该列不能为负数
zerofill：0填充
- 不足的位数用0填充
auto_increment：自增，自动在上一条记录的基础上加一
- 通常用来设置唯一的主键，且必须是整数类型
not NULL：非空
default：默认值报错可能需要用’'引起来
2.4、操作数据表
除了最后一条其余语句句末加 , 号，表名尽量用``括起来
2.4.1.新建表
CREATE TABLE `teacher` (
`id` int(4) NOT NULL AUTO_INCREMENT COMMENT '教师编号',
`name` varchar(20) DEFAULT '匿名' COMMENT '教师姓名',
`sex` char(2) DEFAULT '女' COMMENT '性别',
`birthday` datetime DEFAULT NULL COMMENT '出生日期',
`email` varchar(200) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
show CREATE table teacher;查看建表语句
DESC teacher查看表结构
2.4.2.修改表
ALTER TABLE teacher RENAME AS myteacher; --修改表名
ALTER TABLE teacher ADD `age` INT(11); --增加字段
ALTER TABLE teacher MODIFY age VARCHAR(11); --修改字段约束，不能重命名
ALTER TABLE teacher CHANGE age `year` int(3); --重命名
ALTER TABLE teacher DROP year; --删除字段
2.4.3.删除表
DROP TABLE teacher;
2.5、数据表类型
2.5.1.MySQL引擎
| MYISAM | INNODB | |
|---|---|---|
| 事务支持（一个成功其他失败，统统不提交） | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间大小 | 较小 | 较大，余额为MYISAM两倍 |
ps：数据行锁定：在进行表查询时，类似java会对表中内容上锁，MYISAM对整张表上锁，INNODB对单行上锁，因此INNODB效率更高。
区别
-
MYISAM：节约空间，速度快
-
INNODB：安全性高，事务处理，多表多用户操作
-
存储方式：
数据库所有内容都存在data文件夹下
- INNODB
- 对应数据库名文件夹下的 *.frm 文件以及上级目录下的 ibdata1文件
- MYISAM
- *.frm 表结构定义文件
- *.MYD 数据文件（data）
- *.MYI 索引文件（index）
- INNODB
2.5.2.编码
MySQL默认编码Latin1，不支持中文
修改方式：
- 在建表时设置字符集编码：
CHARSET=utf-8 - 在my.ini中配置默认编码：
character-set-server=utf-8(不建议，本地设置了到其他地方可能出现编码问题)
三、MySQL数据管理
3.1、外键（了解）
方式一：建表的时候添加约束
-- 学生表的 gradeid 字段 去引用年级表的 gradeid 字段
-- 定义外键key
-- 给外键添加约束（执行引用） references引用
CREATE TABLE `student` (
`id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT '学员ID\r\n',
`name` varchar(100) DEFAULT NULL COMMENT '学员姓名',
`age` int(3) DEFAULT NULL COMMENT '学员年龄',
`gradeid` varchar(255) NOT NULL COMMENT '学员年级',
PRIMARY KEY (`id`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
方式二：修改表添加外键约束
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
以上都是物理外键，数据库级别的，不建议使用。
3.2、DML语言**（背）**
数据操作语言
insert
-- 插入字段需要字段名和数值对应，插入多个字段用 , 号隔开
INSERT INTO `student` (`name`,`age`) VALUES('Darwin','22'),('Alex','21'),('Sya','23')
update
需要加条件，否则改动整列数据
-- 修改名字
update `student` set `name`='Alexander' where `name`='Alex';
-- 不指定条件情况下修改的是整列数据
update `student` set `name`='Darwin';
-- 修改多列
update `student` set `name`='Alexander',`age`='121' where `name`='Alex';
条件：where语句
| 操作符 | 含义 |
|---|---|
| = | 等于 |
| <>或!= | 不等于 |
| between… and … | 在某个范围内 |
| AND | 多个条件 |
| OR | 多个条件任意一个成立即可 |
delete
delete from 表名 where 条件
-- 删除指定数据，不加条件就是删除整表数据
DELETE FROM `student` WHERE `id`=2;
truncate table 表名
- 相同点：都能删除数据
- 不同：
- truncate 重新设置自增列，计数器会清零
- truncate 不会影响事物
在不同的引擎中使用delete：
- InnoDB自增列会从1开始，因为InnoDB引擎下断电即失，从现存记录里最大的自增
- MyISAM从现有记录的自增量开始
四、DQL语言（数据库查询语言）
select 语法
|：表示或者，{}：必填项，[]：可选项
SELECT [去重]
｛*|表.*| [表.列名1 [ AS 别名1],表.列名2 [AS 别名2],[... ...] ] ｝
FROM 表名 [AS 别名]
[左连接|右连接|内连接 表名2] -- 连接查询
[WHERE ...] -- 指定结果需要满足的条件
[GROUP BY ...] -- 指定结果按照那几个字段来分组
[HAVING] -- 过滤分组记录必须满足的次要条件
[ORDER BY ...] -- 通过哪个字段排序【升序/降序】
[LIMIT {起始位置，每页显示数据}] -- 指定分页查询记录起始位置以及每页展示数据
实例代码：
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);
4.1、指定查询字段
-- 查询全部的学生 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_name) AS '新姓名' FROM student;
语法：SELECT 字段,. . . FROM 表
4.2、去重复、操作数据库的列
distinct [dɪˈstɪŋkt]
不同的;明显的;清晰的;清楚的;明白的;有区别的;不同种类的;确定无疑的;确切的
-- 查询哪些同学参加了考试
SELECT * FROM result; -- 查询全部成绩
SELECT DISTINCT `student_no` FROM result -- 去重
语法：SELECT DISTINCT 字段 FROM 表名;
SELECT VERSION(); -- 查询系统版本（函数）
SELECT 100*3-41 AS '计算结果'; -- 计算（表达式）
SELECT @@auto_increment_increment; -- 查询自增步长（变量）
-- 学员考试所有成绩加一分
SELECT `student_no`,`student_result` AS '提分前',`student_result`+1 AS '提分后' FROM `result`;
数据库中的表达式：文本、列、Null、函数、计算表达式、系统变量
语法：select 表达式 from 表
4.3、where条件子句
作用：检索数据中符合条件的值
条件：由一个或者多个表达式组成
1.逻辑运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| and && | A and B A&&B | 逻辑与，全真为真 |
| or || | A or B A||B | 逻辑或，一真为真 |
| NOT ！ | NOT A !A | 逻辑非，真为假假为真 |
-- 查询成绩在95~100
SELECT * FROM `result` WHERE `student_result`>=95 && `student_result`<=100;
-- 模糊查询(区间)
SELECT * FROM `result` WHERE `student_result` BETWEEN 95 AND 100;
-- 除了学号等于1000意外的所有学生
SELECT * FROM `student` WHERE student_no != 1000
SELECT * FROM student WHERE NOT student_no=1000
2.比较运算符（模糊查询）
| 运算符 | 语法 | 描述 |
|---|---|---|
| IS NULL | A is null | 空即是真 |
| IS NOT NULL | A is not null | 非空为真 |
| BETWEEN | A between B and C | 若A在[B,C]间为真 |
| LIKE | A like B | 如果AB中有共同元素，结果为真 |
| IN | A in (A1,A2,A3…) | 如果A在A1~An某个值中，结果为真 |
-- 模糊查询
-- 查询刘姓同学
-- 使用like结合 %（表示0到任意字符）， _（一个字符）
SELECT * FROM `student` WHERE student_name LIKE '刘%';
-- 查询刘姓同学名字后面只有一个字的
SELECT * FROM `student` WHERE student_name LIKE '刘_';
-- 查询刘姓同学名字后面有两个字的
SELECT * FROM `student` WHERE student_name LIKE '刘__';
-- 查询名字中带有德字的同学
SELECT * FROM `student` WHERE student_name LIKE '%德%';
-- 查询学号1001~1003的学院
SELECT* FROM `student` WHERE student_no=1000 or student_no=1002 or student_no=1003;
SELECT * FROM `student` WHERE student_no in (1001,1002,1003);
-- 查询地址为空的学生
-- 注意！！！
-- 空字符串 和 null 不一样
SELECT * FROM `student` WHERE address='';
SELECT * FROM `student` WHERE address IS NULL;
4.4、联表查询
联表查询中，join on（连接查询）和 join where（等待查询）没有太大区别
查询思路：
要查什么数据就select什么，从哪张表查就from什么，没有的数据就join表，on什么条件，具体哪种join看查询需求
案例代码：
-- 联表查询
SELECT s.student_no,student_name,subject_no,student_result
FROM student AS s
INNER JOIN result AS r
ON s.student_no=r.student_no;
-- RIGHT JOIN
SELECT s.student_no,student_name,subject_no,student_result
FROM student AS s
RIGHT JOIN result AS r
ON s.student_no = r.student_no;
-- LEFT JOIN
SELECT s.student_no,student_name,subject_no,student_result
FROM student AS s
LEFT JOIN result AS r
ON s.student_no = r.student_no;
-- 查询缺考的同学
-- 联表查询
SELECT s.student_no,student_name,subject_no,student_result
FROM student AS s
INNER JOIN result AS r
ON s.student_no=r.student_no;
-- RIGHT JOIN
SELECT s.student_no,student_name,subject_no,student_result
FROM student AS s
RIGHT JOIN result AS r
ON s.student_no = r.student_no;
-- LEFT JOIN
SELECT s.student_no,student_name,subject_no,student_result
FROM student AS s
LEFT JOIN result AS r
ON s.student_no = r.student_no
WHERE student_result IS NULL;
-- 查询参加考试的学生信息（学号，姓名，科目，成绩）
SELECT s.student_no,student_name,subject_name,student_result
FROM student AS s
RIGHT JOIN result AS r
ON s.student_no = r.student_no
INNER JOIN `subject` AS sub
ON r.subject_no = sub.subject_no;
-- 查询学生学号，姓名，C语言成绩
SELECT s.student_no,s.student_name,sub.subject_name,r.student_result
FROM student AS s
INNER JOIN result AS r
ON s.student_no = r.student_no
INNER JOIN `subject` AS sub
ON sub.subject_no = r.subject_no
WHERE sub.subject_name = 'C语言-1';
| 操作 | 描述 |
|---|---|
| Inner join | 两表有共同匹配项，就返回行 |
| Left join | 即使右表没有匹配数据，也会从左表返回所有值 |
| Right join | 即使左表没有匹配数据，也会从右表返回所有值 |
自连接查询
表和表自己连接，核心：一张表分为两张表
本张表中每个字段都有自己的id，和一个父id，通过子数据的父id去绑定对应的父数据自己的id即可实现
案例代码：
-- 创建表
-- 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`;
父表：
| categoryId | categoryName |
|---|---|
| 2 | 信息技术 |
| 3 | 软件开发 |
| 5 | 美术设计 |
子表
| pid（父Id） | categoryId | categoryName |
|---|---|---|
| 3 | 4 | 数据库 |
| 2 | 8 | 办公信息 |
| 3 | 6 | web开发 |
| 5 | 7 | 美术设计 |
测试代码：
-- 自连接
-- 经测试，只能用inner join，因为不管左或右都有多余数据
SELECT f.category_id,f.category_name,c.category_id,c.pid,c.category_name
FROM category AS f
INNER JOIN category AS c
ON c.pid = f.category_id;
4.5、分页和排序
分页：limit
语法：limit (n-1)*pagesize,pagesize
n代表当前页
(n-1)*pagesize：显示数据起始位置
pagsize：每页显示数据数量
排序：order by
升序asc，降序desc
-- 查询 高等数学-1 倒数5名同学的成绩
SELECT s.student_no,student_name,subject_name,student_result
FROM student AS s
INNER JOIN result AS r
ON s.student_no = r.student_no
INNER JOIN `subject` AS sub
ON sub.subject_no = r.subject_no
WHERE subject_name = '高等数学-1'
ORDER BY student_result ASC
LIMIT 0,5
-- 查询 C语言-1 排名前十，并且分数大于80的学生信息
SELECT s.student_no AS '学生学号',student_name AS '学生姓名',subject_name AS '科目名',student_result AS '成绩'
FROM student AS s
INNER JOIN result AS r
ON s.student_no = r.student_no
INNER JOIN `subject` AS sub
ON sub.subject_no = r.subject_no
WHERE subject_name = 'C语言-1' AND r.student_result > 80
ORDER BY student_result desc
limit 0,10
4.6、子查询
where值是固定的，如果希望值是计算出来的需要通过子查询
本质：在where 语句中嵌套子查询
-- 查询C语言-1 的所有考试结果结果（学号，科目编号，成绩）降序
-- 方式一：使用连接查询
SELECT r.student_no,r.subject_no,student_result
FROM result AS r
INNER JOIN `subject` AS sub
ON r.subject_no = sub.subject_no
WHERE subject_name = 'C语言-1'
ORDER BY student_result DESC
-- 方式二：使用子查询
-- 在where条件中新建查询 科目名为 C语言的科目编号，并赋给成绩表的科目编号
SELECT student_no,subject_no,student_result
FROM result AS r
WHERE r.subject_no = (
SELECT subject_no FROM `subject`
WHERE subject_name = 'C语言-1'
)
ORDER BY student_result DESC
-- 查询高等数学-2 分数不小于80分的学号和姓名
SELECT DISTINCT s.student_no,student_name
FROM student AS s
INNER JOIN result AS r
ON r.student_no = s.student_no
WHERE r.student_result >= 80 AND r.subject_no = (
SELECT subject_no FROM `subject`
WHERE subject_name = '高等数学-1'
)
4.7、分组(Group By) 和 过滤(Having)
where判断只适用于分组前，分组后使用过滤添加查询条件
-- 查询不同课程的平均分，最高分，最低分
-- 核心 ：根据不同的课程分组
SELECT subject_name,AVG(student_result) AS 平均分,MAX(student_result),MIN(student_result)
FROM result AS r
INNER JOIN `subject` AS sub
ON r.subject_no = sub.subject_no
GROUP BY r.subject_no
HAVING 平均分 > 80
五、MySQL函数
5.1、常用函数
官方文档：https://dev.mysql.com/doc/refman/5.7/en/built-in-function-reference.html
mysql中有大量函数可以使用
-- ===常用函数===
-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回一个0 ~1之间的随机数
SELECT SIGN(-100) -- 返回参数符号
-- 字符串函数
SELECT CHAR_LENGTH('阿米豆腐') -- 返回字符串函数
SELECT CONCAT('南五','阿米','豆腐') AS 字符串 -- 拼接字符串
SELECT INSERT ('我爱天安门',3,4,'斯台普斯') -- 从某个位置插入（替换）某个长度
SELECT LOWER('Darwin') -- 转小写
SELECT UPPER('Darwin') -- 转大写
SELECT INSTR('Darwin','w') -- 返回某个字符第一次出现的位置
SELECT REPLACE('我爱天安门','天安门','斯台普斯') -- 替换指定字符串
SELECT SUBSTR('我爱天安门',3,5) -- 截取指定字符串，起止位置
SELECT REVERSE('赵兄托我办点事') -- 反转字符串
-- 查询周姓同学，换位邹
SELECT REPLACE(student_name,'周','邹') FROM student
WHERE student_name LIKE '周%'
-- 时间和日期函数
SELECT CURRENT_DATE -- 获取当前日期
SELECT NOW() -- 获取当前时间：时分秒
SELECT LOCALTIME() -- 获取本地时间：时分秒
SELECT SYSDATE() -- 获取系统时间
SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
-- 系统
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()
5.2、聚合函数
聚合函数对一组值执行计算并返回单一的值。除了 COUNT(*) 外，聚合函数都会忽略 Null 值。 聚合函数经常与 SELECT 语句的 GROUP BY 子句一起使用。
| 函数名 | 描述 |
|---|---|
| COUNT() | 计数 |
| SUM() | 求和 |
| AVG() | 平均值 |
| MAX() | 最大值 |
| MIN | 最小值 |
-- == 聚合函数 ==
-- Count(字段) 只包括列名的一列，统计结果时会忽略null值（不是空字符串）
SELECT COUNT(student_name) FROM student;
-- Count(*) 包括了所有的列，相当于行数，在统计结果时，不会忽略null值
SELECT COUNT(*) FROM student;
-- Count(1) 包括了忽略的所有列，用1表示代码行，在统计结果时，不会忽略null值
SELECT COUNT(1) FROM student;
SELECT SUM(student_result) AS 'Total' FROM result;
SELECT AVG(student_result) AS 'Average' FROM result;
SELECT MAX(student_result) AS 'Max' FROM result;
SELECT MIN(student_result) AS 'Min' FROM result;
-- 查询不同课程的平均分，最高分，最低分
-- 核心 ：根据不同的课程分组
SELECT subject_name,AVG(student_result) AS 平均分,MAX(student_result),MIN(student_result)
FROM result AS r
INNER JOIN `subject` AS sub
ON r.subject_no = sub.subject_no
GROUP BY r.subject_no
HAVING 平均分 > 80
5.3、数据库级别的 MD5加密
MD5主要增强算法复杂度和不可逆性
-- ==== 测试 MD5 ====
CREATE TABLE testmd5(
id INT(4) NOT NULL auto_increment,
`name` VARCHAR(20),
`pwd` varchar(14),
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
-- 插入时加密
INSERT INTO testmd5(`name`,pwd) VALUES ('马涛',MD5('111111'));
-- 如何校验：将用户传递进来的密码进行md5加密，比较加密后的值
SELECT * FROM testmd5 WHERE pwd = MD5('123456') AND `name` = '李明亮';
六、事务（Transition）
事务是数据库最小的工作单元，即将SQL放到一个批次中执行，要么都成功，要么都失败
6.1、事物原则：ACID原则
例：A有800，B有200，A给B转200
A：800-200 = 600
B：200+200=400
原子性（Atomicity）
- 一起成功或者一起失败
- 案例中体现为：A转出200和B收到200必须同时实现
一致性（Consistency）
-
针对事务操作前后状态一致（最终一致性）
-
案例中体现为：不管怎么转账，两人资金总数1000，不会凭空产生也不会凭空消失
隔离性（Isolation）
-
针对多个用户同时操作，主要是排除其他事务影响
-
案例中体现为：如果A给B转钱的同时，C也给B转钱，两个事物相互独立
持久性（Durability)
-
事务的结束状态不会随着外界导致数据丢失，事务没有提交
-
案例中体现为：
-
操作前A：800，B：200
-
操作后A：600，B：400
-
如果操作前（事务没有提交），服务器宕机或者断电，重启后A应为800，B为200
-
如果操作后（事务没有提交），服务器宕机或者断电，重启后A应为600，B为400
-
支持事务的数据库必须具有以上四个特性
6.2、隔离导致的问题
类似线程中的同步问题
脏读：
- 指一个事务读取到另一个事务未提交的数据
- 后继事务先执行了，导致前驱事务将后继事务的数据覆盖（脏写）
- 案例中体现为：
- A给B转200，C给B转100
- A的事务还没操作完，C的事务读取到B只有200，于是B变成了300
- A的事务操作完B只有400块钱，少了100
不可重复读
- 在一个事务读取表中某一行数据，多次读取不同结果（不一定是错误的，只是场合不对）即同一事务前后两次读取数据不一样
- 例：
- A首先读取了一条数据，执行逻辑时，B将数据修改了，A再次读取发现数据不匹配
幻读
- 指在事务内读到了别的事务插入的数据，导致前后读取不一致（一般是多一条数据）
- 幻读和不可重复度类似，但是幻读强调集合的增减，而不是单条数据更新
- 例：
- A根据条件索引获得了M条数据，事务B改变了这M条数据或添加了N条数据，导致A再次搜索发现有N+M条数据，产生了幻觉
第一类更新丢失
事务A和事务B都对数据进行更新，但是由于某种原因事务A回滚，以至于已经提交的事务B更新后的数据被覆盖。
第二类更新丢失
跟第一类更新丢失类似，两个事务同时对数据进行更新，但事务A的更新把已提交的事务B的更新数据给覆盖了。
6.4、事务隔离级别
为了解决上述问题，主流关系型数据库都会提供四种事务隔离级别，由低到高分别是：读未提交 --> 读已提交 --> 可重复读 --> 串行化。隔离级别越高，性能损失越高，mysql默认可重复读
读未提交
最低隔离级别，所有事物都能看到其他事物未提交的事务执行结果，只能防止第一类更新丢失，不能解决脏读，可重复 读，幻读
读已提交
一个事务的更新操作只有在该事物提交后，另一事务才能读取到同一笔数据更新后的结果。可防止脏读和第一类更新丢失
可重复读
一个事务多次读取同一数据，在这个事务未结束前，其他事物不能访问该数据。防止脏读、不可重复读、第一/二类更新丢失，还是会出现幻读
串行化
要求事务序列化执行，每个事务挨个执行，不能并发。可解决所有问题，但是可能导致大量超时和锁竞争，一般不推荐
扩展：回滚机制
在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进程进行回收。
6.5、执行事务
mysql默认开启事务自动提交，如果想手动处理事务，要先关闭
-- 手动处理事务
SET autocommit = 0 -- 关闭事务自动提交
-- 事务开启
START TRANSACTION -- 标记事务开启，从此之后sql都在同一个事务内
-- 提交：持久化（成功）
COMMIT
-- 回滚：回到原来的样子（失败）
ROLLBACK
-- 事务结束
SET autocommit = 1
SAVEPOINT 保存点名 -- 设置事务的保存点
ROLLBACK TO 保存点名 -- 回滚到保存点
RELEASE 保存点名 -- 删除保存点
测试手动处理事务
- 例子中的两个update语句作为一组事务
- Navicat需要在
COMMIT提交事务之后才能看到数据变化
-- 案例
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
use shop
CREATE TABLE account(
`id` int(3) NOT NULL auto_increment,
`name` VARCHAR(20),
`money` DECIMAL(9,2),
PRIMARY KEY(`id`)
)ENGINE = INNODB CHARSET = utf8
INSERT INTO account(`name`,`money`)
VALUES('A',2000),('B',10000)
-- 模拟事务：转账
SET autocommit = 0; -- 关闭自动提交事务
START TRANSACTION -- 开启事务
UPDATE account SET money = money - 500 WHERE `name` = 'A'; -- A减500
UPDATE account SET money = money + 500 WHERE `name` = 'B'; -- B加500
COMMIT; -- 提交事务
ROLLBACK; -- 回滚
SET autocommit = 1; -- 恢复默认值
七、索引
定义：索引（index）是帮助MySQL高效获取数据的数据结构
索引是数据结构
7.1、索引分类
- 主键索引（PRIMARY KEY）
- 唯一表示，主键不可重复，只能由一个列作为主键
- 唯一索引（UNIQUE KEY）
- 避免重复列出现，唯一索引可以重复，多个列都可以表示为唯一索引
- 常规索引（KEY / INDEX）
- 默认的，可以用index关键字或者key关键字来设置
- 全文索引（FullText）
- 特定的数据库引擎才有（MYISAM）
- 快速定位数据
- 索引的使用
- 在创建表的时候增加索引
- 创建表之后增加索引
基础语法
-- 索引的使用
-- 显示所有索引信息
SHOW INDEX FROM student
-- 增加一个全文索引 索引名(列名)
ALTER TABLE school.student ADD FULLTEXT INDEX student_name(student_name);
-- 分析sql语句
EXPLAIN SELECT * FROM student; -- 非全文索引
-- MATCH 匹配列名，against匹配字段名
-- 全文索引在数据少时没有用
SELECT * FROM student WHERE MATCH(student_name) against ('马');
EXPLAIN SELECT * FROM student WHERE MATCH(student_name) against ('马');
7.2、测试索引
添加索引相当于给列中每条数据增加了唯一定位，就不需要遍历
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`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,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表';
-- 自定义函数插入一百万条数据
DELIMITER $$ -- 写函数之前必写
/*
第一个语句 delimiter 将 mysql 解释器命令行的结束符由”;” 改成了”$$”，
让存储过程内的命令遇到”;” 不执行
*/
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i<num DO
INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`)
VALUES(CONCAT('用户',i),'19224305@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();
drop FUNCTION mock_data;
-- 创建索引 id_表名_字段名
SELECT * FROM app_user WHERE id = 99999;
-- 创建索引在内存中新建一棵树
-- CREATE INDEX 索引名 on 表（字段名）
CREATE INDEX id_app_user_name ON app_user(`name`);
SELECT * FROM app_user WHERE id = 99999;
添加索引前查询时间在1s左右，添加后0.01s左右
索引在小数据量用处不大，大数据效果明显
7.3、索引原则
- 索引并非越多越好
- 不适用于 经常变动的数据加索引
- 小数据量不需要加索引
- 一般用于经常查询的字段
索引的数据结构
Hash类型
Btree类型（InnoDB默认数据结构）
参考：http://blog.codinglabs.org/articles/theory-of-mysql-index.html
八、权限管理和备份
8.1、用户管理
SQL命令操作
- mysql数据库下的user表用来储存用户信息
- 操作用户的本质就是操作user表
-- 创建用户CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER darwin IDENTIFIED BY '111111'
-- 修改当前用户密码
SET PASSWORD = PASSWORD('111111')
-- 修改指定用户密码
SET PASSWORD FOR darwin = PASSWORD('111111');
-- 重命名
RENAME USER darwin TO zhong;
-- 用户授权 *.*：所有权限
GRANT ALL PRIVILEGES ON *.* TO zhong
-- 查询权限（查询root需要在root后加@localhost）
SHOW GRANTS FOR zhong
-- 撤销权限 REVOKE 权限 ON 何种权限 FROM 用户
REVOKE ALL PRIVILEGES ON *.* FROM zhong
-- 删除用户
DROP USER zhong
8.2、MySQL备份
方式：
-
直接拷贝物理文件
-
使用可视化工具导出数据库
-
使用命令行
# mysqldump -h 主机 -u用户名 -p密码 数据库 表名 > 物理磁盘位置：文件名 C:\Users\Administrator>mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql # 如果导出多张表只需在加入别的表名，cmd命令行没有逗号，用空格隔开 C:\Users\Administrator>mysqldump -hlocalhost -uroot -p123456 school student subject result >D:/a.sql # 导出数据库 C:\Users\Administrator>mysqldump -hlocalhost -uroot -p123456 school >D:/a.sql # 导入 # 登录情况下，切换数据库后使用source命令 source D:/a.sql C:\Users\Administrator>mysqldump -hlocalhost -uroot -p123456 school source < D:/a.sql
九、规范数据库设计
良好数据库设计：
- 节约内存空间
- 保证数据库完整性
- 方便开发
糟糕的数据库设计：
- 数据冗余，浪费空间
- 插入删除很麻烦
- 程序性能差
9.1、设计步骤
以个人博客为例
分析需求：分析业务逻辑和处理数据库的需求
概要设计：E-R图
设计数据库：
收集信息分析需求
- 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
- 分类表（文章分类，谁创建的）
- 评论表
- 友链表（友情链接信息）
-
自定义表（系统信息，某个关键的字，或者一些主字段）
key:value -
关注表（粉丝数）
-
说说表（发表心情， id…content…create_time）
-
标记实体（把需求落实到每个字段）
-
标记实体之间的关系
- 写博客：user --> blog
- 创建分类：user --> category
- 关注：user --> user
- 友链：links
- 评论：user --> user --> blog
9.2、三大范式
为什么需要数据规范化？
- 信息重复
- 异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 丢失有效信息
第一范式（1NF）
原子性：保证每一列不可再分
上表中，家庭信息和学校信息不满足原子性，应分隔开
第二范式(2NF)
前提：满足第一范式
每张表只描述一件事情
上表中，产品和订单应该是两件区分开的事件，应该分位产品表和订单表
第三范式(3NF)
前提满足第一和第二范式
确保每张表中每一列数据和主键直接相关
班主任和学号毫无瓜葛，应该分割为学生表和班主任表，班主任为主键
规范性和性能问题
关联查询的表不超过三张（阿里）
- 考虑商业化需求和目标（成本，用户体验更重要），因此着重性能
- 适当考虑规范性
- 故意给表增加冗余字段（由多表查询变为单表查询）
- 故意增加计算列
十、JDBC（重点）
10.1、驱动
因为应用程序不能直接操作数据库，所以需要驱动（驱动程序）让他们进行相应的通信。
驱动：声卡、显卡等
10.2、JDBC
SUN公司为了简化开发人员对数据库的统一操作，提供了一个Java操作数据库的规范，俗称JDBC。
没有什么是加一层解决不了的
因此这些规范就交给厂商做，开发人员只需要学会使用JDBC接口操作即可
java.sql
javax.sql
还需要导入一个数据库驱动包 mysql-connector-java-5.1.47.jar
10.3、第一个JDBC程序
-
创建测试数据库
CREATE DATABASE jdbcstudy CHARACTER SET utf8 COLLATE utf8_general_ci; USE jdbcstudy; CREATE TABLE users( `id` INT PRIMARY KEY, `name` VARCHAR(40), `password` VARCHAR(40), `email` VARCHAR(60), `birthday` DATE ); INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`) VALUES(1,'张三','123456','zs@sina.com','1980-12-04'), (2,'李四','123456','lisi@sina.com','1981-12-04'), (3,'王五','123456','wangwu@sina.com','1982-12-04'); -
导入数据库驱动
新建lib目录，将jar包复制到lib目录下，邮件目录选择 add as library 添加为库
-
编写测试代码
package com.darwin.lesson01;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
// 我的第一个jdbc程序
public class Jdbc01 {
public static void main(String[] args) throws Exception {
// 1.加载驱动
Class.forName("com.mysql.jdbc.Driver"); //固定写法：加载驱动
// 2.用户数据和url
// 用问号连接参数
// useUnicode：支持中文编码；characterEncoding：设定中文字符集为utf8；useSSL：使用安全连接
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
String username = "root";
String password = "123456";
// 3.连接成功，数据库对象 connection
Connection connection = DriverManager.getConnection(url, username, password);
// 4.执行SQL的对象 statement
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获取连接数据库的对象connection
- connection获取执行sql的对象 statement
- statement活的返回的结果集resultSet
- 释放连接
注意点：
- 使用安全连接时，如果mysql版本高于驱动就设置为false
代码解释
A：DriverManager
为什么获取到Driver类的对象不使用？
因为注册驱动的本质是去加载driver类的静态代码块，只要声明类了，初始化driver类就将代码块执行了
// 1.加载驱动
//DriverManager.registerDriver(new Driver()); 本来应这样写
Class.forName("com.mysql.jdbc.Driver"); //固定写法：加载驱动
B：URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
jdbc:mysql：类似链接中的https
localhost:3306：地址和端口号
jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true"：连接的数据库名?参数1&参数2&参数3
C：Connection对象
connection代表数据库
//数据库自动提交，事务回滚，事务提交
connection.setAutoCommit(true);
connection.rollback();
connection.commit();
D：Statement对象
执行sql的对象
String sql = ""; //编写sql
statement.executeQuery(); //查询操作，返回字符集resultset
statement.execute();
statement.executeUpdate(); //更新、插入、删除，返回一个受影响的函数
E：ResultSet
查询的结果集，封装了所有查询结果
resultSet.getObject(); //在不知道列具体类型时使用
// 知道数据类型就是用固定类型
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
resultset是一个链表，可以通过以下代码移动指针位置
resultSet.beforeFirst();// 移动到最前面
resultSet.afterLast();//移动到最后面
resultSet.next();//移动到下一个数据
resultSet.previous();//移动到前一行
resultSet.absolute(row);//移动到指定行
10.4、statement对象
JDBC中statement对象用于向数据库发送SQL语句，完成对应的增删改查
使用statement.executeUpdate完成增删改查之后会返回一个记录（几行数据发生改变的）整数
CRUD
为了节约资源，创建数据库配置文件db.properties通过工具类JdbcUtil.java读取配置文件，避免每次都需要配置数据库
db.propertise：
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456
工具类：
package com.darwin.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");
// 驱动只用加载一次
Class.forName(driver);
} catch (IOException | ClassNotFoundException e){
e.printStackTrace();
}
}
// 获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
//释放资源
public static void release(Connection con, Statement st, ResultSet rs){
if(rs != null){
try {
rs.close();
} catch (SQLException 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.darwin.lesson02.utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TInsert {
public static void main(String[] args) {
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
con = JdbcUtils.getConnection();
st = con.createStatement();
String insert = "INSERT INTO users(id,`name`,`password`,email,birthday)" +
"VALUES(4,'Darwin','111111','ex@qq.com','1999-05-28')";
int i = st.executeUpdate(insert);
if (i>0){
System.out.println("执行成功"+i);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
JdbcUtils.release(con,st,rs);
}
}
}
删除操作：
public class TDelete {
public static void main(String[] args) {
Connection con = null;
Statement st = null;
ResultSet rs = null;
try{
con = JdbcUtils.getConnection();
st = con.createStatement();
String delete = "DELETE FROM users WHERE `name` = 'Darwin';";
int i = st.executeUpdate(delete);
if (i>0){
System.out.println("删除执行成功");
}
}catch (SQLException throwables) {
throwables.printStackTrace();
}finally{
JdbcUtils.release(con,st,rs);
}
}
修改操作：
public class TDelete {
public static void main(String[] args) {
Connection con = null;
Statement st = null;
ResultSet rs = null;
try{
con = JdbcUtils.getConnection();
st = con.createStatement();
String delete = "DELETE FROM users WHERE `name` = 'Darwin';";
int i = st.executeUpdate(delete);
if (i>0){
System.out.println("删除执行成功");
}
}catch (SQLException throwables) {
throwables.printStackTrace();
}finally{
JdbcUtils.release(con,st,rs);
}
}
查询操作：
public static void main(String[] args) {
Connection con = null;
Statement st = null;
ResultSet rs = null;
try{
con = JdbcUtils.getConnection();
st = con.createStatement();
String select = "SELECT `name`,`birthday` FROM users WHERE `name` = 'Darwin';";
rs = st.executeQuery(select);
while(rs.next()){
System.out.println("姓名："+rs.getString("name"));
System.out.println("生日："+rs.getString("birthday"));
}
}catch (SQLException throwables) {
throwables.printStackTrace();
}finally{
JdbcUtils.release(con,st,rs);
}
}
SQL注入问题
web应用程序对用户输入数据的和反省没有判断或过滤不严
SQL会被拼接
package com.darwin.lesson02.utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SQL注入 {
public static void main(String[] args) {
login("'or '1=1","'or '1=1");
}
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 select = "SELECT * FROM `users` WHERE `name` = '" + username + "'AND `password` = '"+password+"';";
rs = st.executeQuery(select);
while(rs.next()){
System.out.println("姓名："+rs.getString("name"));
System.out.println("密码："+rs.getString("password"));
}
}catch (SQLException throwables) {
throwables.printStackTrace();
}finally{
JdbcUtils.release(con,st,rs);
}
}
}
当查询条件为' 'or '1=1'时，所有用户数据都被查出来了
10.5、PreparedStatement对象
PreparedmentStatement对象防止SQL注入
本质：把传递进来的参数当做字符，假设其中有 ’ 号会被直接转义，不存在拼接
区别：
PrepareStatement需要预编译sql语句，然后在获取对象时传入
PrepareStatement在获取对象时使用
connection.preparestatement而Statement使用connection.createStatementString insert = "INSERT INTO users(id,`name`,`password`,email,birthday)" + "VALUES(?,?,?,?,?)";传值时，PrepareStatement可以用占位符，然后用
preparestatement.set方法赋值，更为便捷高效
查询：
public static void main(String[] args) {
Connection con = null;
PreparedStatement st = null;
ResultSet rs = null;
try{
con = JdbcUtils.getConnection();
String select = "SELECT `name`,`birthday` FROM users WHERE `id` = ?;";
st = con.prepareStatement(select);
st.setInt(1,1);
rs = st.executeQuery();
while(rs.next()){
System.out.println("姓名："+rs.getString("name"));
System.out.println("生日："+rs.getString("birthday"));
}
}catch (SQLException throwables) {
throwables.printStackTrace();
}finally{
JdbcUtils.release(con,st,rs);
}
}
新增：
package com.darwin.lesson03;
import com.darwin.lesson02.utils.JdbcUtils;
import java.sql.*;
public class TInsert {
public static void main(String[] args) {
Connection con = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
con = JdbcUtils.getConnection();
// 使用?占位符代替参数
//预编译SQL后执行
String insert = "INSERT INTO users(id,`name`,`password`,email,birthday)" +
"VALUES(?,?,?,?,?)";
st = con.prepareStatement(insert);
//preparedstatement里的set方法，给预编译sql中的占位符赋值
//setXXX(下标,所赋的值)
st.setInt(1,5);
st.setString(2,"Alex");
st.setString(3,"222222");
st.setString(4,"1024@qq.com");
st.setDate(5,new Date(new java.util.Date().getTime()));
int i = st.executeUpdate();
if (i>0){
System.out.println("执行成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
JdbcUtils.release(con,st,rs);
}
}
}
删除：
package com.darwin.lesson03;
import com.darwin.lesson02.utils.JdbcUtils;
import java.sql.*;
public class TDelete {
public static void main(String[] args) {
Connection con = null;
PreparedStatement st = null;
ResultSet rs = null;
try{
con = JdbcUtils.getConnection();
String delete = "DELETE FROM users WHERE `name` = ?;";
st = con.prepareStatement(delete);
st.setString(1,"Alex");
int i = st.executeUpdate();
if (i>0){
System.out.println("删除执行成功");
}
}catch (SQLException throwables) {
throwables.printStackTrace();
}finally{
JdbcUtils.release(con,st,rs);
}
}
}
防注入：
package com.darwin.lesson03;
import com.darwin.lesson02.utils.JdbcUtils;
import java.sql.*;
public class Wrong {
public static void main(String[] args) {
login("'or '1=1","'or 123456");
}
public static void login(String username,String password) {
Connection con = null;
PreparedStatement st = null;
ResultSet rs = null;
try{
con = JdbcUtils.getConnection();
String select = "SELECT * FROM `users` WHERE `name` = ? AND `password` = ?;";
st = con.prepareStatement(select);
st.setString(1,username);
st.setString(1,password);
rs = st.executeQuery();
while(rs.next()){
System.out.println("姓名："+rs.getString("name"));
System.out.println("密码："+rs.getString("password"));
}
}catch (SQLException throwables) {
throwables.printStackTrace();
}finally{
JdbcUtils.release(con,st,rs);
}
}
}
10.6、IDEA连接数据库
连接后在架构（schemas）选择数据库
10.7、JDBC处理事务
使用connection.setAutoCommit(false);会自动关闭自动提交，并开启事务
package com.darwin.lesson04;
import com.darwin.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TTransition {
public static void main(String[] args){
Connection con = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
con = JdbcUtils.getConnection();
// 关闭数据库自动提交后会自动开启事务
con.setAutoCommit(false);
String sql1 = "update account set money = money - 100 where name = 'A'";
st = con.prepareStatement(sql1);
st.executeUpdate();
// 让事务失败
// int x = 1/0;
String sql2 = "update account set money = money + 100 where name = 'B'";
st = con.prepareStatement(sql2);
st.executeUpdate();
// 业务完成提交事务
con.commit();
con.setAutoCommit(true);
boolean autoCommit = con.getAutoCommit();
System.out.println(autoCommit);
System.out.println("Over");
} catch (SQLException e) {
e.printStackTrace();
// 失败则回滚
try {
con.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
}finally {
JdbcUtils.release(con,st,rs);
}
}
}
10.9、数据库连接池
数据库连接 --> 执行完毕 --> 释放
整个过程是十分浪费资源的
池化技术：预先准备资源，过来就连接，链接完再放回连接池
设置最小连接数：10、最大连接数：15、等待超时：100ms，当达到最大连接数后续就需要等待，超时则取消
编写连接池，只用实现接口DataSource
开源的数据源连接池实现类：
- DBCP
- C3P0
- Druid
DBCP
DBCP和JDBC的本质区别在于：
- 两者工作模式不同
- JDBC需要频繁的连接以及释放
- DBCP准备了池
- 连接方式不同
- JDBC需要读取出配置信息（driver、url、username、password）后手动加载驱动并配置连接
- DBCP只需要读取配置文件后，创建工厂对象，用工厂去创建连接
需要用到的jar包：
commons-dbcp-1.4、commons-pool-1.6
2.0以上的版本需要commons-logging
dbcpconfig.properties：配置文件
#所有连接名都是dbcp定义的，不能修改
#连接设置
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
JdbcUtils_DBCP：工具类
package com.darwin.lesson05.utils;
import com.darwin.lesson02.utils.JdbcUtils;
import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils_DBCP {
private static DataSource dataSource = null;
static{
try{
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("dbctconfig.properties");
Properties properties = new Properties();
properties.load(in);
//工厂模式
dataSource = BasicDataSourceFactory.createDataSource(properties);
} catch (Exception e){
e.printStackTrace();
}
}
// 从数据源中获取连接
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
//释放资源
public static void release(Connection con, Statement st, ResultSet rs){
if(rs != null){
try {
rs.close();
} catch (SQLException 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();
}
}
}
}
C3P0
可以定义多套配置文件，XML文件不用读取
需要用到的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.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.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>
工具类
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCC3P0Utils {
private static DataSource dataSource = null;
//private static ComboPooledDataSource dataSource = null;
static {
try {
//代码的方式配置
// dataSource = new ComboPooledDataSource();
// dataSource.setDriverClass();
// dataSource.setJdbcUrl();
// dataSource.setUser();
// dataSource.setPassword();
// 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 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();
}
}
}
}
结论：
无论是用什么数据源，本质始终实现DataSource接口
扫一扫
68
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
