Mysql

JavaEE：企业级开发 web

1. 前端：页面（展示数据）
2. 后台：连接点（链接数据库JDBC；链接前端，控制页面跳转，给前端传递数据）
3. 数据库：存数据（Txt、Excel、word）

我的数据库连接密码：admin（非常重要，如果记不住真的要重新连接好久。。。。）

一、相关概念

1、数据库(DataBase，DB)：存储数据，管理数据。是一种软件，安装在操作系统（windows、linux、mac…）之上的

2、分类

关系型数据库：（SQL）

• MySQL、Oracle、Sql Server、DB2，SQLlite
• 通过表和表之间、行和列之间的关系进行数据的存储

非关系型数据库：（NoSQL） Not only SQL

• Redis、MongDB
• 对象存储，通过对象的自身属性来决定

SQL和No SQL的区别

（1）数据存储方式不同：

• SQL的存储是表格式的，数据存储在表的行和列中，不同的数据表可以关联协作
• NO SQL是一大块组合在一起的，其通常存储在数据集中，就像文档、键值对或图结构等，对于数据的格式十分灵活没有固定的表结构

（2）可扩展性不同

• SQL扩展性低
• NO SQL由于数据之间没有耦合性，所以非常容易水平扩展

（3）数据一致性

• SQL要求满足ACID原则，强调数据的强一致性
• NO SQL一般强调的是数据最终的一致性，从NO SQL中读到的有可能还是处于一个中间态的数据。

3、DBMS（DataBase Managment System）：数据库管理系统

它是一种数据库管理软件，能科学有效的管理我们的数据。MySQL就是一种数据库管理系统，其操作数据所用的语言叫SQL语句

4、navicat 是一款数据库的可视化工具

二、SQL语句

不区分大小写、语句结尾要加分号；

--连接数据库
mysql -uroot -padmin
--修改用户密码
update mysql.user set anthentication_string=password('admin') where user='root' and Host='localhost';
--刷新权限
flush privileges;
-------------------------------------------
--创建一个数据库，库名是test
create database test;
--查看所有的数据库
show databases;
--切换数据库  use 数据库名
use school;
--查看一个数据库下所有的表（前提是一定要使用了一个数据库，即采用use命令进入了一个库）
show tables;
--显示数据库中某个表的信息   
describe student;   --这里的student是school数据库下一个表的名字
--  单行注释（--是SQL的本来的注释）
/**/  多行注释

1、数据库定义语言DDL

Date Definition Language主要由create、alter、drop、truncate四个关键字完成

数据库

新建、删除、使用、查看

--新建数据库（其中IF NOT EXISTS并不是必须的 ）
CREATE DATABASE If NOT EXISTS test;
--删除数据库（IF EXISTS并不是必须）
DROP DATABASE If EXISTS test;
--使用数据库（其中school是一个数据库名）
USE school;
--查看所有数据库
SHOW DATABASES;

数据库的列类型

1. 数值

​ tinyint 十分小的数据 1个字节

​ smallint 较小的数据 2个字节

​ mediumint 中等大小的数据 3个字节

​ int 标准的整数 4个字节 常用的

​ bigint 较大的数据 8个字节

​ float 浮点数 4个字节

​ double 浮点数 8个字节

​ decimal 字符串形式的浮点数 金融计算的时候，一般使用decimal

2. 字符串

​ char 字符串固定大小 0~255

​ varchar 可变字符串 0~65535 常用的变量 String

​ tinytext 微型文本 2^8-1

​ text 文本串 2^16-1 保存大文本

3. 时间日期

​ date YYY-MM-DD 日期

​ time HH:mm:ss 时间格式

​ datetime YYY-MM-DD HH:mm:ss 最常用的时间格式

​ timestamp 时间戳 表示1970.1.1到现在的毫秒数 较为常用

​ year 年份

4. null：没有值，未知

​ 注意不要使用null进行计算

数据库的约束

1. Unsigned：无符号整数，声明了该列不能为负数

2. zerofill：0填充。当出现不足的位数时，采用0填充，如int(3)，输出5后变成了005

3. 自增：设置唯一的主键，必须是整数类型，可以自定义设计主键自增的起始值和步长

4. 非空：NULL/not NULL

   ​ 设置为NULL：如果不填写值，默认就是null

   ​ 设置为not NULL：如果不赋值，就会报错，此时添加一个默认值

5. 默认：设置默认的值

   ​ 如sex，设置默认的’男’，那么所有的sex默认的都是‘男’

注意：在正式的项目中，每个表都必须存在以下5个字段，表示一个记录存在的意义

/*
id		主键
verson    乐观锁
is_delete     伪删除
gmt_create     创建时间
gmt_update    修改时间
*/

数据表

新建数据表

/*
	建立一个student表，包含学生的信息
	学号、姓名、登录密码、性别、出生日期、家庭地址、邮箱
	---------
	注意点：
	使用英文括号()、表的名字 和 字段 尽量使用``括起来（TAB键上面的那个键）、注释中的字符串用英文下的单引号''括起来
	PRIMARY KEY 一个表只有唯一的一个主键、所有的语句后面都要加上英文逗号,最后一句除外
*/
CREATE TABLE IF NOT EXISTS `student`(
		`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学生学号',
		`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '学生姓名',
		`password` 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] `表名`(
	`字段名` 列类型 [属性] [索引] [注释]，
	`字段名` 列类型 [属性] [索引] [注释]，
	....
	`字段名` 列类型 [属性] [索引] [注释]
) [表类型] [字符集设置] [注释];

修改数据表

--创建新数据表
CREATE table if not EXISTS `teacher`(
		`id` int(10) not null comment '教师编号'
)ENGINE=INNODB DEFAULT charset=utf8;

--修改表名  ALTER TABLE	旧表名 RENAME as 新表名;
ALTER TABLE `teacher` RENAME as `teacher1`;

--增加字段 ALTER TABLE 表名 ADD 字段名 类类型;
	--一次添加一个
	ALTER TABLE `teacher1` ADD age int(3);  
   	--一次添加多个
	ALTER table `teacher` add(     
			name VARCHAR(5) COMMENT '教师姓名',
			age int(3) not null DEFAULT 0 comment '教师年龄'
	);

--修改表的字段（修改字段名字、修改字段属性）
	--修改字段属性 ALTER table 表名 MODIFY 字段名 新字段属性
	ALTER table `teacher1` MODIFY age VARCHAR(10);  --将age的字段属性由int改为varchar
   		--modify可以连着写
		alter table teacher modify id varchar(10),modify  age varchar(3);
	--修改字段名 ALTER TABLE 表名 CHANGE 旧字段名  新字段名 新/旧字段属性;
	ALTER TABLE `teacher1` CHANGE age  age1 int(1);  --同时修改age的名字和属性，由age变成age1，由int变为varchar

--删除表中的字段 ALTER TABLE 表名 DROP 字段名;
ALTER TABLE `teacher1` DROP age1;

--查看表的结构 desc 表名
desc `teacher`;

删除数据表

--删除表 DROP TABLE IF EXISTS 表名
DROP TABLE IF EXISTS `teacher1`;  --表结构和数据均被删除

--‘截断’某个表，删除表里的全部数据，但是保留表结构 truncate 表名;
truncate teacher;

MyISAM与InnoDB的区别

	MyISAM	InnoDB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约为MyISAM的2倍

INNODB在数据库中只有一个*.frm文件

MYISAM对应的文件：

• *.frm文件、
• *.MYD文件
• *.MYI文件

2、数据库操作语言DML

Data Manipulation Language主要有insert、update、delete组成

外键约束 Foreign Key

建立两个表之间的约束关系，如主表是这个学校的老师，从表是这个学校的学生，每一个学生对应一个老师，因此学生表与老师表就建立了约束关系。即这个学生的老师必须是老师表里已经存在的老师

• 方式一、创建表的时候增加外键（麻烦）

--新建一个老师表
create table if not exists teacher(
		id int(3) not null auto_increment comment '教师编号', 
		name varchar(5) comment '教师姓名',
		primary key(id)
)engine=innodb charset=utf8;

--新建一个学生表，学生的数学老师与教师表中的id具有外键约束
	--定义外键key 
	--给这个外键添加约束 reference 引用
create table if not exists student(
		id int(10) not null auto_increment comment '学生学号',
		name varchar(5) comment '学生姓名',
		age int(2) comment '学生年龄',
		math_teacher int(3) not null comment '学生的数学老师',
		primary key(id),
		key FK_teacher (math_teacher),  --定义外键的名字
		constraint FK_teacher foreign key (math_teacher) references teacher(id)  --确定外键的参考约束
)engine=innodb charset=utf8;

• 方式二、创建表成功后，采用修改表语句，添加外键约束（推荐）

--alter table 表名 add constraint 约束名 foreign key (作为外键的列) references 哪个表(哪个字段);
alter table student add constraint FK_teacher foreign key (math_teacher) references teacher(id);

！！！注意：删除具有外键约束的表时，必须先删除从表，才能删除主表。或者定义主表与从表之间的级联删除on delete cascade或者on delete set nulll用于指定当主表记录被删除时，从表中参照该主表的处的值自动变为null

以上都是物理外键，不建议使用，后面会利用程序去实现外键。

添加insert

insert into 表名（字段名） values(值)

--单个插入
insert into student(name) values('张三');
--多个插入
insert into student(sex,birthday,address) values ('女','1998-01-01','上海');
--全部插入，并且每次可以多插入几条
insert into student values
	(null,'李四','男','2000-01-01','南京'),
	(null,'小翠','女','1999-01-01','无锡');
--由于第一天语句运行了三次，所以student表显示如下：

删除delete

delete from 表名 where 条件

--将id=2 和 id=3的行数据删除
delete from student where id=2;
delete from student where id=3;

delete和truncate的区别

--相同点：删除整个表中所有的记录，不删除表结构
delete from 表名;  --不添加where约束即可
truncate 表名；

不同点：

• truncate 重新设置自增列，计数器归零
• truncate 不影响事务

create table test(
		id int(10) not null auto_increment,
		var varchar(10),
		primary key(id)
)engine=innodb charset=utf8;

insert into test values
			(null,'aaa'),
			(null,'bbb'),
			(null,'ccc');  --id自增到3了
--使用delete删除			
delete from test;
insert into test values(null,'ddd');  --此时id=4

--使用truncate删除
truncate test;
insert into test values(null,'ddd');  --此时id=1

delete删除问题：

• InnoDB：重启数据库后，自增列会从1开始（数据存储在内存中，断点即失）
• MyISAM：重启数据库后，继续从上一个自增量开始（数据存储在文件中，不会丢失）

修改update

update 表名 set column_name=value where 条件

--修改单个属性
update student set birthday='2001-01-01' where name='张三';  --将name为张三的行的birthday改为2001-01-01

--修改多个属性
update student set name='王五',sex='男',address='合肥' where id=6;

--修改整列
update student set name='哈哈';

where 条件

检索数据中符合条件的值

操作符	含义	范围	结果
=	等于	5=6	false
<>或!=	不等于	5=6	true
< 或<=	小于/小于等于	5<6	true
> 或 >=	大于/大于等于	5>6	false
between … and…	两者之间，闭区间	[2,5]
and	与 &&
or	或 ||

--between and
update student set name='张三' where id between 4 and 6;

--and 
update student set name='李四' where name='张三' and sex='女';

3、数据库查询语言DQL

Date Query Language 主要由select组成

--SELECT 语法，注意：下面写的顺序是不可以改变的，如limit不能写到where上面
select [ALL | distinct]
{* | table.* | table.field1 [as '别名1'],table.field2 [as '别名2']...}
from table_name [as '表别名']
	[left | right | inner join table_name2 [as '表别名2']]  --连表查询
	[where ...]   --指定结果需满足的条件
	[group by...]  --指定结果按照哪几个字段来分组
	[having]   --过滤分组的记录必须满足的次要条件
	[order by...]   --指定查询记录按一个或多个条件排序  
	[limit {[offset,] row_count | row_countOFFSET offset}];  --指定查询的记录从哪条到哪条

基础查表

CREATE TABLE IF NOT EXISTS `student`(
		`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学生学号',
		`name` VARCHAR(30)  COMMENT '学生姓名',
		`password` VARCHAR(20)  COMMENT '密码',
		`sex` VARCHAR(2)  COMMENT '学生性别',
		`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
		`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
		`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
		`result` int(10) comment '成绩',
		PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=UTF8;

insert into student values
	(null,'张三','0001','男','2000-01-01','北京','123@','99'),
	(null,'李四','0002','男','2000-01-02','上海','124@','95'),
	(null,'王五','0003','男','2000-01-03','广州','125@','87'),
	(null,'王翠','0004','女','2000-01-04','深圳','126@','100'),
	(null,'小刘','0005','男','2000-01-05','南京','127@','78'),
	(null,'小陈','0006','女','2000-01-06','合肥','128@','92'),
	(null,'小崔','0001','男','2000-01-07','天津','129@','88');
	
--查询整个表
select * from student;

--查询表的指定字段
select name,address from student;
--给字段起别名 也可以给student表其别名
select name as 学生姓名, address as 学生地址 from student;

--concat 函数： concat(a,b)将a和b字符串拼接出来
select concat('姓名：',name) as 学生姓名 from student;

去除重复数据 distinct

--去除重复数据 select distinct 字段名 from 表名；
select distinct number from result;

其他查询

--查询系统版本（函数）
select version();  --输出：5.7.30-log
--计算结果（表达式）
select 100*3+1 as 计算结果;  --输出：301
--学生成绩都加1分
select id,result+1 as 提分后成绩 from student; 

where条件查询

逻辑查询：与 或 非

--与
--查询考试成绩在90-100分之间的学生  三种写法
select id,result from student where result>=90 and result<=100;  --and
select id,result from student where result>=90 && result<=100;  --&&
select id,result from student where result between 90 and 100;  --between and

--非
--查询成绩不为满分的学生
select id,result from student where result!=100;
select id,result from student where not result=100;

模糊查询：比较运算符

运算符	语法	描述
is null	a is null	a为null，结果为真
is not null	a is not null	a不为null。结果为真
between	a between b and c	若a在bc之间，结果为真
like	a like b	SQL匹配，如果a匹配b，则结果为真
in	a in(a1,a2,…)	假设a在a1，a2…之间，则结果为真

-----like的操作-------------
--查询姓王的同学 ：% 表示任意个字符、  _ 表示一个字符
select id,name from student where name like '王%';  --查询所有姓王的同学
select id,name from student where name like '王_';  --查询只有2个字的王姓同学
select id,name from student where name like '王__';  --后面跟了两个_，表示查3个字的王姓同学
select id,name from student where name like '%翠%';  --查询名字里有翠的同学

--in的操作，要有具体的值，不能使用%这些了
select id,name from student where id in(4,7);  --查询id为4和7的学生
select id,name,address from student where address in('北京','上海');  --查询地址是北京和上海的学生

--NOT null 的操作
select id,name,password from student where `password` is not null;  --查询所有password不为空的学生

--null 的操作
select id,name,password from student where `password` is null;  --查询所有password为空的学生
/*注意：一行中不写任何东西，等价于空字符串''，不代表null*/

多表查询 join on

操作	描述
inner join	返回共同拥有的
left join	返回左表中有的，即使右表中没有匹配值
left join	返回右表中有的，即使左表中没有匹配值

如上图所示，student表中id=13的同学在result_math中没有，result_math表中id=14的人在student中没有

/*思路：
1. 分析需求：分析查询的字段都在哪些表里
2. 确定使用哪种链接 7种
确定交叉点（这2个表中哪个数据是相同的）
*/
--查询学号、成绩、姓名、年龄
/*
1. 分析：学号，姓名，年龄在student表中，成绩在result表中。因此我们主要查询student表，然后外接result表即可
2. 交叉点：student表和result表都有学生id，因此这个id即为交叉点
*/
--inner join  只查询到id=12的学生
select s.id,name,result from 
student as s
inner join result_math as r
on s.id=r.id;

--right join 100分的学生也查出来了，因为使用的是s.id，故此时的id显示为null
select s.id,name,age,result from 
student as s right join result_math as r
on s.id=r.id;
-----------此时采用r.id，显示了id=14
select r.id,name,age,result from 
student as s right join result_math as r
on s.id=r.id;           

--right join 
select s.id,name,age,result from 
student as s left join result_math as r
on s.id=r.id;

--查询缺考的学生，即无成绩的人
select s.id,name,age,result from 
student as s left join result_math as r
on s.id=r.id
where result is NULL;

/* student表中有id,name，age，sex,
   result_math表中有id、result、dubject_id
   subject_tab表中有subject_id，subject_name
   现在要查询学生的id,name,result,subject_name
*/
--多表查询
/*分析：student和result_math中相同的是id，result_math和subject中相同的是subject_id
*/
select id,name,result,`subject` from 
student s inner join result_math r on s.id=r.id
inner join subject_tab sub on r.subject_id=sub.sybject_id;

自连接查询：自己和自己的表链接

核心：一张表拆为两张一样的表

新建一个数据库表如下，其中pid表示父类id，categoryID表示自己所属的id

categoryID	pid	categoryName
2	1	信息技术
3	1	软件开发
4	3	数据库
5	1	美术设计
6	3	web开发
7	5	ps技术
8	2	办公信息

拆开：

父类表：寻找pid为1的categoryName，表示一级目录

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

子类表：查找categoryID为2,3,4下的目录

pid	categoryID	categoryName
2	8	办公信息
3	4	数据库
3	6	web开发
5	7	ps技术

因此这两个表之间的关系是：子类表的pid是父类表的categoryID

--选出上面换的关系
select a.categoryName as '父表', b.categoryName as '子表'
from category as a, category as b
where a.categoryID=b.pid;

分页 limit、排序 order by

--排序 order by  升序 asc  降序  desc
select * from student where address='南京市' order by age desc;  --查询家庭住址为南京市的所有学生并按照年龄降序排序

--排序 limit (n-1)*pagesize,pagesize
/* 	 pagesize：当前页面大小，即当前页面显示的数据条数
  	 n：当前页，即第n页
	(n-1)*pagesize：起始值，即数据显示的起始值
	总页数：数据总数%页面大小==0 ? 数据总数/页面大小 : (数据总数/页面大小)+1
*/
select * from student   --查询年龄小于25的学生，并按照年龄大小升序排序，且显示从0开始的3条数据
where age<25
order by age asc
limit 0,3;

--查询 java第一学年 课程成绩排名 前5的学生，并且分数大学80分的学生信息（id,name,subject_name,result）
select student_id,student_name,subject_name,student_result  --查询学号，姓名，学科明，成绩
from student s   --student表中查询student_id,student_name
inner join result r on s.student_id=r.studet_id   --result中查询student_result
inner join subject sub on r.subject_id=sub.subject_id  --subject中查询subject_name
where subject_name='java第一学年' and student_result>=80  --课程是：java第一学年。分数是：80分以上
order by student_result desc   --前5名，所以采用降序排序
limit 0,5;   --选择前五名

子查询

子查询就是在一个查询语句中嵌套另一个子查询，子查询可以支持多层嵌套

--子查询  查询年龄大于学号为1的学生的信息
select id,name,age   --查询id,name,age信息
from student s   --从student表中
where age>(select age from student where id=1);  --年龄大于学号为1的学生的年龄

MYSQL中的函数

常用函数

--============  常用函数  ==============
-- 数字运算
select abs(-10);  --绝对值 10
select ceiling(9.5)  --向上取整 10
select floor(9.4)   --向下取整 9
select rand()       --返回一个0~1间的随机数
select sing(10)     --判断一个数的符号  0-0 负数返回-1，正数返回1 

--字符串
select char_length('hello');   --字符串长度 5
select concat('我爱','世界');  --拼接字符串  我爱世界
select insert('我爱世界',2,1,'超级爱');  --替换字符串  insert(str1,indext1,len,str2);在str1字符串中，将index1处开										始，往后的len个字符，替换成str2 我超级爱世界
select lower('Hello World');  --小写  hello world
select upper('Hello World');  --大写  HELLO WORLD
select instr('Hello World','l');  --返回第一次出现子串的索引  instr(str,substr) 在str中返回第一次出现substr的索引 3
select replace('Hello World','World','china');  --替换出现的字符  replace(str,str1.str2) 在str中，将str1出现的字符													串改为str2字符串  Hello china
select substr('Hello World',4,5);   --返回指定的子字符串  substr(str,index,len) 在str在，从index开始返回len个长度的										子串  lo Wo
select reverse('Hello World');  --反转  dlroW olleH
--查询所有姓 王 的同学并将其改为 朱 
select replace(name,'王','朱') from student where name 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();   --查询版本

聚合函数（常用）

函数名称	描述
count()	计数
sum()	求和
avg()	平均值
max()	最大值
min()	最小值
…	…

--===============  聚合函数  ==================
--count统计表中所有的数据
/*count(字段名)、count(*)、count(1)的区别
count(字段名)会忽略所有的null值
count(*) 不会忽略null值，本质是计算行数，但是使用*表示所有的列都会操作
count(1)不会忽略null值，本质是计算行数，但是只对一列进行操作
执行效率上：
1. 列名为主键，则count(主键)比count(1)块；反之列名不为主键，则count(1)比count(字段名)快
2. 多个列并且没有主键，则count(1)比count(*)快；反之，单个列，count(*)最快
3. 如果有主键，则count(主键)最快
*/
select count(name) from student;
select count(*) from student;
select count(1) from student;

select sum(res) as 总分 from result;
select avg(res) as 平均分 from result;
select max(res) as 最大分 from result;
select min(res) as 最小分 from result;

分组group by和过滤 having

--============  分组 group by /   过滤 having===========
--查询 不同课程 的平均分，最高分，最低分
	--核心：根据不同的课程分组
select subject_name，avg(student_result) as 平均分, max(student_result) as 最大分, min(student_result) as 最低分   --要查询的内容
from result as r    --从esult表中查询 student_result
inner join `subject` as sub   --从`subject`表中查询subject_name
on r.subject_id=sub.    --r和sub共有的是subject_id
group by r.subject_id;   --按照subject_id来分组    

--在加一个条件，平均分要大于80
select subject_name，avg(student_result) as 平均分, max(student_result) as 最大分, min(student_result) as 最低分   --要查询的内容
from result as r    --从esult表中查询 student_result
inner join `subject` as sub   --从`subject`表中查询subject_name
on r.subject_id=sub.    --r和sub共有的是subject_id
group by r.subject_id   --按照subject_id来分组
having 平均分>80;    --过滤80分以下的

4、数据库控制语言DCL

Date Control Language主要由commit、rollback、savepoint完成，在事务中讲到

5、MD5密码加密

--=============   MD5 加密测试==================
--新建表
create table if not exists testmd5(
		id int(10) not null comment '用户ID号',
		name varchar(225) not null comment '用户名字',
		pwd varchar(255) not null comment '用户密码'
)engine=innodb default charset=utf8;

--插入数据  明文密码
insert into  testmd5 values(1,'张三','123456'),(2,'李四','526389'),(3,'王五','156894');
--加密密码
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');

三、事务

1、概念

事务是指满足ACID特性的一组操作，可以通过Commit提交一个事务，也可以使用Rollback回滚一个事务

2、ACID特性

1. 原子性 A：事务里有多个操作，事务的操作要么全部成功Commit，要么全部失败Rollback。回滚可以用回滚日志来实现
2. 一致性 C：事务前后的数据完整性要保持一致。（开启一个事务以后，在这个事务中，多次读取同一个数据，结果不变）
3. 隔离性 I：并发访问时，数据库为每一个用户开启事务，每个用户事务之间互相隔离，操作数据互不干扰
4. 持久性 D：一旦事务提交，则其结果会永远保存在数据库中，即使系统发生崩溃，也不会丢失事务处理的结果。系统崩溃后，可用重做日志进行恢复。

参考博客：https://blog.csdn.net/dengjili/article/details/82468576

3、并发一致性问题

• 脏读：一个事务读取了另一个事务未提交的数据
• 不可重复读：在事务内读取表中的一行数据，多次读取的结果不同
• 虚读（幻读）：在一个事务内读取到了别的事务插入的数据，导致前后读取不一致
• 丢失修改：一个事务的更新操作被另外一个事务的更新操作替换

--==========  事务  ====================
--mysql默认开启事务
set autocommit=0; --关闭 默认提交
set autocommit=1; --开启 默认提交

------手动处理事务
set autocommit=0;   --关闭自动提交
--事务开启
start transaction;   --从这往后，所有的sql语句都在同一个事务中

--提交事务：持久化（成功！）
commit;

--回滚事务：回到原来的样子（失败！）
rollback;

--事务结束
set autocommit=1;  --开启自动提交

savepoint 保存点名;   --设置一个事务的保存点
rollback to savepoint 保存点名;   --回滚到保存点
release savepoint 保存点名;  --撤销一个保存点

--==========  模拟事务 ======================
--新建一个数据库
create database if not exists shop;
use shop;

--新建表  #decimal(a,b) 表示有9位数，小数点后有2位
create table if not exists account(
		id int(10) not null auto_increment,
		`name` varchar(50) not null,
		money decimal(9,2) not null,    
		primary key(id)
)engine=innodb default charset=utf8;

--插入数据
insert into account(`name`,money) values ('A',1000.00),('B',2000.00);

--模拟转账：事务
set autocommit=0;  --关闭自动提交
start transaction;  --开启事务

update account set money=money-500 where `name`='A';   --A转出500元  
update account set money=money+500 where `name`='B';   --B转进500元

commit;  --提交事务
rollback;  --回滚事务
set autocommit=1;  --开启自动提交
/*上述在执行update的过程中，如果不到commit，数据库里A、B的money值都不会改变，只有commit以后才会改变*/

参考《事务》一文

四、索引

1、索引的分类

• 主键索引（primary key）：唯一的标识，主键不可重复，只能有一个列作为主键
• 唯一索引（unique key）：保证指定列不出现重复值，但可以出现多个null值
• 常规索引（key/index）：默认的
• 全文索引（FullText）：在特定的数据库引擎下才有，MyISAM中不支持

/*
创建索引的方式
	1.create table的时候写就加上索引
	2.alter table 处加上索引
	3.create index 加上索引
*/
--alter方式：alter table 表名 add 索引类 索引名字 （字段名）
alter table student add unique Uni_name (`name`);  --Uni_name是键的名字，()里是列名
--create方式：create index 索引名 on 表名（字段名）
create index id_student_age on student(age);  --索引名一般的命名规则：id_表名_

--索引删除 drop index 索引名 on 表名
drop index id_student_age on student

2、测试索引：降低查询速度

--显示所有的索引信息
show index from student;
--使用explain 分析sql执行的情况
explain select * from student;

--=============== 测试索引 =============================
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(50) default '' comment '手机号',
		gender tinyint(4) unsigned default '0' comment '性别（0：男，1：女）',
		`password` varchar(100) not null comment '密码',
		age tinyint(4) default '0' comment '年龄',
    primary key(id)
)engine=innodb default charset=utf8mb4 comment='app用户表';

--插入100万条数据  使用循环实现
DELIMITER $$
create function mock_date()
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),'123456@qq.com',concat('187',FLOOR(RAND()*10000000)+10000000),
		FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
		set i=i+1;
	end while;
	RETURN i;
end;
--执行插入语句
select mock_date();

--查询数据，未加索引
select * from app_user where `name`='用户99999';  --用时0.512s
select * from app_user where `name`='用户9999';   --用时0.503s

explain select * from app_user where `name`='用户9999';   --共查询了993797条数据

--添加索引后 
create index id_app_user_name on app_user(`name`);

select * from app_user where `name`='用户99999';   --用时0.001s
select * from app_user where `name`='用户9999';   --用时0.001s
  
explain select * from app_user where `name`='用户9999';  --共查询了1条数据

从上面的例子可以看出：索引在小数据量时，用处不明显，但是在大数据的时候，区分十分明显

3、索引原则

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

4、索引的数据结构

索引的数据结构：参考博客http://blog.codinglabs.org/articles/theory-of-mysql-index.html

B+tree：InnoDB默认的数据结构、MongoDB采用的是BTree结构

MyISAM

MyISAM叶节点的data域存放的是数据记录的地址。无论是主键索引还是其他类型的索引，其data域存放的都是指向该行数据记录的物理地址，并不是真正的数据文件。假设以Col1为主键，则MyISAM索引文件如下图所示，（Mysql中逻辑上相邻的记录其物理地址未必相连）

假设不存在主键索引，只在Col2上建立一个辅助索引，那么此时MyISAM的索引结构如下：

从上面两个图可以看出，主键索引和辅助索引没有什么不同，其叶节点的data中存放的都是数据地址，唯一的不同是主键索引要key不同，辅助索引的key可以重复。

InnoDB

（1）InnoDB采用主键索引时，主键索引的叶节点的date域中保存的是完整的数据记录，以Col1为主键，则InnoDB主索引示意图如下

可以看出此时date域中存放的不在是地址指针了，而是真实的数据。

针对上图每一个叶节点，可以看到Col1、Col2、Col3的数据都有，说明叶节点包含了完整的数据，这种索引叫做***聚集索引***。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

（2）当InnoDB中采用辅助索引时，辅助索引的data域存储的是相应主键的值，不在是整个数据记录了。假设在Col3上定义一个辅助索引，则结构如下图所示：

此时数据查询的过程要经过两个步骤：a、先检索辅助索引得到主键值；b、然后利用主键值到主索引中检索获得记录。

举个栗子

例如在上表中查询（30,91，Eric）这条记录：

（1）如果是MyISAM，无论采用那一列作为主键索引或辅助索引，都只会在data域获得OFX3这个地址值，然后通过这个地址值找到（30,91，Eric）

（2）如果是InnoDB，采用主键索引Col1，则查询到Col1=30后，直接从其data域中得到Col2=91,Clo3=Eric

​ 如果采用辅助索引，如Col3，则先找到Col3=Eric后，从其data域中得到Col1=30，再重复上面的步骤

注意：多列索引的存在有时也能得到和主键相同的效果

比如，现在只要查找（91，Eric），那么我们可以将Col2和Col3设置成同一索引，此时按照辅助索引规则查询，就可得到最终的结果，不需要取出其data域中的Col1=30再进行查询了，因为我们要查的数据只有Col2和Clo3

这里又会产生一个问题：多列索引查找规则。

多列索引查找规则

需要满足最左前缀原则，比如(a,b,c)是联合索引，那么在查找的过程中，首先按照a查找，找到所有满足条件的数据，然后按照b查找，找到所有既满足a又满足b的数据，最后按照c查找，找到既满足ab又满足c的数据。

联合索引的生效原则是从前往后依次生效，如果中间某个索引没有使用，那么断点前面的索引起作用，断点后面的索引不起作用。比如对于(a,b,c)联合索引来说，其查找的过程中必须保证a的存在，只有(a)、(a,b)、(a,b,c)会真正的起到联合索引的作用。

举个栗子：

create table if not exists test(
		id int(10) not null auto_increment,
		a int(10) not null,
		b int(10) not null,
		c int(10) not null,
		primary  key(id)   --主键是id
)engine=InnoDB default charset=utf8;
--随机生成1000条数据
delimiter $$
create function mock_date()
returns int 
begin 
	declare i int default 0;
	declare num int default 1000;
	while i<num do
		insert into test values (null,rand()*10,rand()*100,rand()*100);
		set i=i+1;
		end while;
		return i;
end;
--插入数据
select mock_date();

--指定(a,b,c)是联合索引
create index id_test on test(a,b,c); 

--==============  测试联合索引 （等值查询）==========================
select * from test where a=5 and b=5 and c=81;   --都起作用（这里a,b,c换位置不影响结果，Mysql会自动按照a,b,c的顺序查找）此处只查到了一条数据，说明这个表里a=5 b=5 c=81的记录只有1条
explain select * from test where a=7 and b=5 and c=10;  --查询了1条数据

select * from test where a=5 and b=5;  --只有a,b起作用，查到了3条记录，说明这哥表里s=5 b=5的数据有3条
explain select * from test where a=10 and b=5;   --查询了3条语句

select * from test where a=5 and c=81;   --只有a起作用，因为b是断点
explain select * from test where a=5 and c=81;   --查询了102条数据

select * from test where b=5 and c=81;  --b,c均不起作用，因为a是断点
explain select * from test where b=5 and c=81;  --查询了1000条数据

--================  测试联合索引 （范围查询）  ========================
select * from test where a=1 and b>10 and c=12;  --a有用，b是用到了索引，但是因为b是范围值，相当于是断点，因此c没有
explain select * from test where a=1 and b>10 and c=12;  --81条

select * from test where a>5 and b=10 and c=64;  --a用到了索引，但是a相当于断点，所以b,c无用
explain select * from test where a>5 and b=10 and c=64;  --458

select * from test where a=1 order by b;  --a有用，b用到了索引
explain select * from test where a>5 and b=10 and c=64;  --458

/*以上注意：“有用”和“用到了索引”是不同的概念。只有等值查询的时候索引才“有用”，其余的查询只是“用到了索引”而已，还是相当于是断点*/

五、其他

1、数据库备份

• 使用navicate可视化工具导出
• 使用dos命令行导出

-- mysqldump -h 主机 -u root -p 密码 数据表 表名 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -padmin school student > D:/a.sql   --一次导出一张表
mysqldump -hlocalhost -uroot -padmin school student result > D:/b.sql  --一次导出多个表
mysqldump -hlocalhost -uroot -padmin school > D:/.sql --导出整个数据库

--导入
 --登录情况下
 source d:/a.sql
 
 --没登录情况下
 mysql -u root -padmin student d:/a.sql

2、规范数据库设计

三大范式：

第一范式：原子性，保证每一列不可再分

第二范式：前提是满足第一范式，然后每张表只描述一件事情

第三范式：前提是满足第一、第二范式，需要确保数据表中的每一列数据都和主键直接相关，而不是间接相关

参考博客：https://www.cnblogs.com/wsg25/p/9615100.html

3、Mysql日志

日志类型	日志内容
错误日志(Erroe Log)	mysql 启动、停止和运行过程中出现的异常
普通查询日志(Generl Query Log)	记录客户端连接数据库后所有执行的语句
二进制日志(Binary Log)	当数据库发生改变时，产生该日志
中继日志(Relay Log)	从库收到主库的数据更新时产生该日志
慢查询日志(Slow query log)	SQL语句执行超过指定时间时产生该日志
DDL日志（元日志）	执行DDL语句操作元数据时产生该日志

中继日志：为了复制数据库而存在。

六、JDBC

1、JDBC：JAVA连接数据库

package mysql_test;

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

public class JdbcFirstDemo {
	public static void main(String[] args) throws SQLException, ClassNotFoundException {
		//1.加载驱动
		Class.forName("com.mysql.cj.jdbc.Driver");  //固定写法，注意新版本的jdbc不能写成com.mysql.jdbc.Driver
		
		//2.用户信息和url
        /*	school：表示使用的数据库名字
        	useUnicode=true  表示使用的字符集是Unicode，因此可使用中文
        	characterEncoding=utf8  表示编码方式为utf8
        	useSSL=true  表示使用安全连接方式
        	serverTimezone=UTC  设置全球标准时间，在jdbc 6.0版本以上必须设置，否则会报错
        */
		String url="jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC";
		String username="root";
		String password="admin";
		
		//3.链接成功，获取数据库。  此处的connection即表示一个数据库
		Connection connection=DriverManager.getConnection(url,username,password);
		
		//4.创建 执行sql的对象
		Statement statement=connection.createStatement();
		
		//5.执行sql语句，如果有结果，则查看返回的结果
		String sql="SELECT * FROM student where id<3";
		
		ResultSet resultset=statement.executeQuery(sql);  //返回结果集，结果集中封装了我们全部查询出来的结果
		while(resultset.next()) {
			System.out.println("id="+resultset.getObject("id"));
			System.out.println("age="+resultset.getObject("age"));
			System.out.println("address="+resultset.getObject("address"));
			System.out.println("name="+resultset.getObject("name"));
			System.out.println("======================");
		}
		
		//6.关闭连接
		resultset.close();
		statement.close();
		connection.close();
	}
}

步骤总结：

1. 加载驱动
2. 连接数据库 DriverManager
3. 获取执行sql的对象 Statement
4. 执行sql语句，获得返回的结果集
5. 释放连接

注意：需要导入mysql-connector-java的jar包。参考《eclipse导入外部jar包的步骤》

代码解释

/*
	DriverManager的作用
	1.加载驱动
	2.连接数据库
*/
//DriverManager.registerDiver(new com.mysql.cj.jdbc.Driver())
Class.forName("com.mysql.cj.jdbc.Driver");

//连接数据库
Connection connection=DriverManager.getConnection(url,username,password);
//connection表示数据库，因此可以执行事务的一些操作
connection.rollback();  //事务回滚
connection.commit();  //事务提交
connection.setAutoCommit(true 或 false);   //设置事务的自动提交方式

/*
	Statement 执行sql的对象  PrepareStatement也是执行sql的对象
*/
//创建执行sql的对象
Statement statement=connection.createStatement();
ResultSet resultset=statement.executeQuery();  //执行查询操作，结果返回ResultSet
statement.execute();   //执行任何sql
statement.executeUpdate();   //更新、插入、删除操作，都用这个语句，返回一个受影响的行数

/*
	ResultSet 查询结果集：封装所有的查询结果
*/
resultset.getObject();  //在不知道列类型下使用
//在已知列类型下使用
resultset.getInt();   
resultset.getFloat();
resultset.getString();
.....
//指针，遍历的作用
resultset.beforeFirst();  //移动到最前面
resultset.afterLast();  //移动到最后
resultset.next();   //移动到下一个数据
resultset.previous();    //移动到前一行
resultset.absolute(row); //移动到指定行

2、简化上述连接代码

步骤1：在当前项目目录下新建一个mysql.ini文件，保存信息数据库的driver、url、user、password信息

！！！注意：如果需要更改数据库，则需要在url中将school改为要使用的数据库名

步骤2：封装一个类 JdbcUtils 来驱动数据库

package mysql_test;

import java.sql.Statement;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
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 {
			Properties properties=new Properties();
			properties.load(new FileInputStream("mysql.ini")); //这里因为mysql.ini在项目目录文件下，如果在其他处，需要输入完整的路径名
			
			driver=properties.getProperty("driver");
			url=properties.getProperty("url");
			username=properties.getProperty("username");
			password=properties.getProperty("password");
	
			//加载驱动
			Class.forName(driver);   //需要捕获ClassNotFoundException异常，因此下面直接用一个大异常类Exception
		} catch (Exception e) {
			e.printStackTrace();
		} 
	}
	
	//获取数据库连接对象
	public static Connection getConnection() throws SQLException {
		return DriverManager.getConnection(url,username,password);
	}
	
	//释放连接的资源
	public static void release(Connection connection,Statement statement,ResultSet resultSet) {
		if(connection!=null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(statement!=null) {
			try {
				statement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(resultSet!=null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}

步骤3：新建一个类，使用封装好的 JdbcUtils 类来驱动数据库，然后在这个类中执行sql语句

package mysql_test;

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

public class Demo1 {
	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 test(id,`name`,age) values (1,'张三',25)"; //插入
			//String sql="update test set `name`='李四'";   修改
            //String sql="delete from test where id=1";   删除
            
			int i=statement.executeUpdate(sql);  //insert语句属于executeUpdate下的操作，会返回数据库被影响的行数
			if(i>0) {
				System.out.println("插入成功");
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JdbcUtils.release(connection, statement, resultSet);
		}
	}
}

如果需要执行其他的sql语句，只需要在Demo1类中，修改String sql语句以及executeUpdate即可，这样大大减少了每一次创建的过程。

• 增删改（insert、update、delete）都是使用executeUpdate()
• 查询（select）使用executeQuery()

3、SQL注入

SQL注入会导致数据库不安全，容易被攻击

//SQL注入问题
package mysql_test;

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

public class Demo2 {
	
	//登录业务：只有当输入的name和password正确才能登录成功
	public static void login(String name,String password) {
		Connection conn=null;
		Statement stmt=null;
		ResultSet set=null;
		
		try {
			conn=JdbcUtils.getConnection();
			stmt=conn.createStatement();
			
			String sql="select * from test where `name`='"+name+"' and `password`='"+password+"'";
			
			set=stmt.executeQuery(sql);
			
			while(set.next()) {
				System.out.println("name="+set.getString("name"));
				System.out.println("password="+set.getString("password"));
				System.out.println("=========================");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JdbcUtils.release(conn, stmt, set);
		}
	}
	public static void main(String[] args) {
		//正常登录
		//login("张三","123456");   //这里只有输入test表中已经有的姓名和密码，才能正确执行
		
		//SQL注入
		login(" 'or '1=1"," 'or'1=1");
	}
}
//正常输入时的输出结果：name=张三，password=123456
/*SQL注入时输出结果：可以看出将整个数据库的用户和密码数据都盗取了
name=张三
password=123456
=========================
name=李四
password=123321
=========================
name=王五
password=654321
=========================
*/

解决SQL注入

使用PreparedStatement对象，而且效率会更好

//PreparedStatement的使用方法
package mysql_test;

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

public class PrepareStatementTest {
	public static void main(String[] args) {
        
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet set=null;
		
		try {
			conn=JdbcUtils.getConnection();
			
			//PreparedStatement与Statement的区别之处
			//1.先写sql语句，使用？占位符代替参数
			String sql="select * from test where id=? or `name`=?"; 
			
			//2.预编译sql语句，不执行，预编译结果传给PreparedStatement对象
			pstmt=conn.prepareStatement(sql);
			
			//3.手动给参数赋值
			pstmt.setInt(1, 2);  //第一个？赋值为2
			pstmt.setString(2, "张三");    //第二个？赋值为"张三"
			
			//4.执行
			set=pstmt.executeQuery();
			
			while(set.next()) {
				System.out.println("id="+set.getInt("id"));
				System.out.println("name="+set.getString("name"));
				System.out.println("==================");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JdbcUtils.release(conn, pstmt, set);
		}
	}
}

PreparedStatement防止SQL注入的本质是：将传递进来的参数当做字符，如果其中存在转义字符，比如说’’，会直接被转义忽略，因此判断的就是最终的字符是否符合要求。

//PreparedStatement防止SQL注入
package mysql_test;

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

public class Demo3 {
	
	public static void login(String name,String password) {
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet set=null;
		
		try {
			conn=JdbcUtils.getConnection();
			String sql="select * from test where `name`=? and `password`=?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, name);
			pstmt.setString(2, password);
			set=pstmt.executeQuery();
			while(set.next()) {
				System.out.println("name="+set.getString("name"));
				System.out.println("password="+set.getString("password"));
				System.out.println("=========================");
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JdbcUtils.release(conn, pstmt, set);
		}
	}
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		//login("张三","123456");
		//SQL注入
		login("'' or 1=1","123456");
	}
}
/*
	正常登录，输出：name=张三，password=123456
	非正常登录，输出：无结果，但也不报错。因此很好的防止了SQL注入问题
*/

4、JDBC操作事务

package mysql_test;

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

public class Demo4 {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet set=null;
		
		try {
			conn=JdbcUtils.getConnection();
			//关闭数据库自动提交，自动开启事务
			conn.setAutoCommit(false);
			
            //A账户减少200元
			String sql1="update account set money=money-200 where name='A'";
			pstmt=conn.prepareStatement(sql1);
			pstmt.executeUpdate();
			
            //B账户增加200元
			String sql2="update account set money=money+200 where name='B'";
			pstmt=conn.prepareStatement(sql2);
			pstmt.executeUpdate();
			
			//业务完毕，提交事务
			conn.commit();
			System.out.println("成功！");
			
		} catch (SQLException e) {
			try {
				conn.rollback();     //如果失败，则回滚事务。实际上可以不写，因为JDBC默认会回滚事务
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}finally {
			JdbcUtils.release(conn, pstmt, set);
		}
	}
}

步骤总结：

1. 开启事务：conn.setAutoCommit(false);
2. 一组业务执行完毕，提交事务：conn.commit();
3. 可以在catch语句中显式定义回滚语句，但默认识别就会回滚

5、数据库连接池

作用

由于每次都要连接getConnection()才能得到connection，然后又释放connection，这个过程十分耗费资源，因此创建一个数据库连接池，里面存放一些已经连接好的connection，使得需要用的时候直接使用，不需要getConnection()，而用完以后，就放回池子里，不close掉。类似于线程池

参数

• 最小连接数
• 最大连接数
• 等待超时：ms（当业务量大于最大连接数时，会等待）

实现方式

• 编写连接池，实现一个接口DataSource
• 使用开源数据源，如DBCP、C3P0。使用这些开源数据库连接池后，我们在项目中开发中就不需要编写连接数据库的代码了

数据源DBCP

​ 1.导入commons dbcp和commons pool 两个jar包。如果导入这两个包以后，运行程序出现java.lang.NoClassDefFoundError错误，那么需要再导入一个commons logging的jar包。

​ 2.编写dbcp的属性文件，在当前项目下新建一个dbcpconfig.properties文件，输入配置语句，网上找到的配置文件如下图所示，但是有一些需要修改的地方。

最终配置文件如下，school是我要用的数据库名字，我的数据库密码为admin

#连接设置
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC
username=root
password=admin

#<!-- 初始化连接 -->
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 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

​ 3.创建DBCP工具类

package mysql_test;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;

public class DbcpUtils {
	private static DataSource dataSource;
	static {
		try {
			Properties properties=new Properties();
			properties.load(new FileInputStream("dbcpconfig.properties"));  
			
			//创建数据源
			dataSource=BasicDataSourceFactory.createDataSource(properties);  
		} catch (Exception e) {
			e.printStackTrace();
		} 
	}
	//获取连接对象
	public static Connection getConnection() throws SQLException {
		return dataSource.getConnection();    //从数据源中获取连接
	}
	
	//释放连接的资源
	public static void release(Connection connection,Statement statement,ResultSet resultSet) {
		if(connection!=null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(statement!=null) {
			try {
				statement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(resultSet!=null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}

​ 4.使用DBCP工具类

package mysql_test;

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

public class DbcpTest {
	public static void main(String[] args) {
		Connection connection=null;
		PreparedStatement pstmt=null;
		ResultSet set=null;
        
		try {
			connection=DbcpUtils.getConnection();
			
			String sql="insert into student (age,address,`name`) values (?,?,?)";
			pstmt=connection.prepareStatement(sql);
			
			pstmt.setInt(1, 19);
			pstmt.setString(2, "武汉市");
			pstmt.setString(3, "杨树");
			
			int i=pstmt.executeUpdate();
			if(i>0) {
				System.out.println("插入成功");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DbcpUtils.release(connection, pstmt, set);
		}
	}
}

数据源C3P0

1.导入c3p0-0.9.5.2.jar和mechange-comments-java-0.2.11.jar两个包

2.配置c3p0的xml文件。在当前项目下新建一个文件，名字为c3p0config.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/school?serverTimezone=UTC&amp;useUnicode=true&amp;useUnicode=true</property>
		<property name="user">root</property>
		<property name="password">admin</property>
		
		<property name="initialPoolSize">10</property>
		<property name="maxPoolSize">100</property>
		<property name="minPoolSize">60</property>
		<property name="maxStatements">200</property>
	</default-config>
    
     <!--C3P0默认配置，如果在代码中"ComboPooledDataSource ds=new ComboPooledDataSource("MySQL");"这样写表示使用的是C3P0中名字为MySQL的配置-->
	<name-config name="MySQL">
		<property name="diverClass">com.mysql.cj.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql://localhost:3306/school?serverTimezone=UTC&amp;useUnicode=true</property>
		<property name="user">root</property>
		<property name="password">admin</property>
		
		<property name="maxPoolSize">20</property>
		<property name="minPoolSize">5</property>
		<property name="initiaPoolSize">10</property>	
	</name-config>>
</c3p0-config>

在xml文件中，可以写多个c3p0的配置，只要名字不同即可，这样可以使得jdbc连接多个数据库，比如有org的数据库就可以新建一个name，里面写上org数据库的链接方式即可。

在url中需要更改使用的数据库名，同时采用&amp来表示之前写的&；在password中该为自己的密码。

3.创建工具类

package mysql_test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0Utils {
	private static ComboPooledDataSource source=null;
	static {
		try {
			//创建数据源，选择使用默认的配置方式
			//方式1，如果不使用上面的xml文件的话，需要手动配置
			source = new ComboPooledDataSource();
			source.setDriverClass("com.mysql.cj.jdbc.Driver");
			source.setJdbcUrl("jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC");
			source.setUser("root");
			source.setPassword("admin");
			source.setMaxPoolSize(100);
			source.setMinPoolSize(10);
			
			//方式2、自动配置，使用了上面的xml文件
            /*
			  	由于xml文件会自动读取，因此Properties属性文件的读取都可以省略
				Properties properties=new Properties();
				properties.load(new FileInputStream("c3p0config.properties"));
			*/
			//source = new ComboPooledDataSource();   使用xml中的默认配置
            //source = new ComboPooledDataSource("MySQL");    使用xml中名字为MySQL的配置
		} catch (Exception e) {	
			e.printStackTrace();
		}
	}
	//获取连接
	public static Connection getConnection() throws SQLException {
		return source.getConnection();
	}
	//释放资源
	public static void release(Connection connection,Statement statement,ResultSet resultSet) {
		if(connection!=null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(statement!=null) {
			try {
				statement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(resultSet!=null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}

4.使用工具类

package mysql_test;

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

public class C3P0Test {
	public static void main(String[] args) {
		Connection connection=null;
		PreparedStatement pstmt=null;
		ResultSet set=null;
		
		try {
			connection=C3P0Utils.getConnection();
			
			String sql="insert into student (age,address,`name`) values (?,?,?)";
			
			pstmt=connection.prepareStatement(sql);
			
			pstmt.setInt(1, 29);
			pstmt.setString(2, "扬州市");
			pstmt.setString(3, "催芽");
			
			int i=pstmt.executeUpdate();
			if(i>0) {
				System.out.println("插入成功");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			C3P0Utils.release(connection, pstmt, set);
		}
	}
}

DBCP与C3P0的区别

DBCP无法自动回收空闲连接，c3p0有自动回收空闲连接的功能

对数据连接的处理方式不同：dbcp提供最大连接数，c3p0提供最大空闲时间。前者当连接数超过最大连接时，所有连接都会断开；后者当连接超过最大空闲连接时间时，当前连接就会断开

	DBCP	C3P0
数据连接的处理方式	提供最大连接数	提供最大空闲时间
什么时候连接断开	连接数超过最大连接数	超过最大空闲连接时间
资源是否释放	手动释放资源	自动回收连接
效率	比较高

Mysql

JavaEE：企业级开发 web

1. 前端：页面（展示数据）
2. 后台：连接点（链接数据库JDBC；链接前端，控制页面跳转，给前端传递数据）
3. 数据库：存数据（Txt、Excel、word）

我的数据库连接密码：admin（非常重要，如果记不住真的要重新连接好久。。。。）

一、相关概念

1、数据库(DataBase，DB)：存储数据，管理数据。是一种软件，安装在操作系统（windows、linux、mac…）之上的

2、分类

关系型数据库：（SQL）

• MySQL、Oracle、Sql Server、DB2，SQLlite
• 通过表和表之间、行和列之间的关系进行数据的存储

非关系型数据库：（NoSQL） Not only SQL

• Redis、MongDB
• 对象存储，通过对象的自身属性来决定

SQL和No SQL的区别

（1）数据存储方式不同：

• SQL的存储是表格式的，数据存储在表的行和列中，不同的数据表可以关联协作
• NO SQL是一大块组合在一起的，其通常存储在数据集中，就像文档、键值对或图结构等，对于数据的格式十分灵活没有固定的表结构

（2）可扩展性不同

• SQL扩展性低
• NO SQL由于数据之间没有耦合性，所以非常容易水平扩展

（3）数据一致性

• SQL要求满足ACID原则，强调数据的强一致性
• NO SQL一般强调的是数据最终的一致性，从NO SQL中读到的有可能还是处于一个中间态的数据。

3、DBMS（DataBase Managment System）：数据库管理系统

它是一种数据库管理软件，能科学有效的管理我们的数据。MySQL就是一种数据库管理系统，其操作数据所用的语言叫SQL语句

4、navicat 是一款数据库的可视化工具

二、SQL语句

不区分大小写、语句结尾要加分号；

--连接数据库
mysql -uroot -padmin
--修改用户密码
update mysql.user set anthentication_string=password('admin') where user='root' and Host='localhost';
--刷新权限
flush privileges;
-------------------------------------------
--创建一个数据库，库名是test
create database test;
--查看所有的数据库
show databases;
--切换数据库  use 数据库名
use school;
--查看一个数据库下所有的表（前提是一定要使用了一个数据库，即采用use命令进入了一个库）
show tables;
--显示数据库中某个表的信息   
describe student;   --这里的student是school数据库下一个表的名字
--  单行注释（--是SQL的本来的注释）
/**/  多行注释

1、数据库定义语言DDL

Date Definition Language主要由create、alter、drop、truncate四个关键字完成

数据库

新建、删除、使用、查看

--新建数据库（其中IF NOT EXISTS并不是必须的 ）
CREATE DATABASE If NOT EXISTS test;
--删除数据库（IF EXISTS并不是必须）
DROP DATABASE If EXISTS test;
--使用数据库（其中school是一个数据库名）
USE school;
--查看所有数据库
SHOW DATABASES;

数据库的列类型

1. 数值

​ tinyint 十分小的数据 1个字节

​ smallint 较小的数据 2个字节

​ mediumint 中等大小的数据 3个字节

​ int 标准的整数 4个字节 常用的

​ bigint 较大的数据 8个字节

​ float 浮点数 4个字节

​ double 浮点数 8个字节

​ decimal 字符串形式的浮点数 金融计算的时候，一般使用decimal

2. 字符串

​ char 字符串固定大小 0~255

​ varchar 可变字符串 0~65535 常用的变量 String

​ tinytext 微型文本 2^8-1

​ text 文本串 2^16-1 保存大文本

3. 时间日期

​ date YYY-MM-DD 日期

​ time HH:mm:ss 时间格式

​ datetime YYY-MM-DD HH:mm:ss 最常用的时间格式

​ timestamp 时间戳 表示1970.1.1到现在的毫秒数 较为常用

​ year 年份

4. null：没有值，未知

​ 注意不要使用null进行计算

数据库的约束

1. Unsigned：无符号整数，声明了该列不能为负数

2. zerofill：0填充。当出现不足的位数时，采用0填充，如int(3)，输出5后变成了005

3. 自增：设置唯一的主键，必须是整数类型，可以自定义设计主键自增的起始值和步长

4. 非空：NULL/not NULL

   ​ 设置为NULL：如果不填写值，默认就是null

   ​ 设置为not NULL：如果不赋值，就会报错，此时添加一个默认值

5. 默认：设置默认的值

   ​ 如sex，设置默认的’男’，那么所有的sex默认的都是‘男’

注意：在正式的项目中，每个表都必须存在以下5个字段，表示一个记录存在的意义

/*
id		主键
verson    乐观锁
is_delete     伪删除
gmt_create     创建时间
gmt_update    修改时间
*/

数据表

新建数据表

/*
	建立一个student表，包含学生的信息
	学号、姓名、登录密码、性别、出生日期、家庭地址、邮箱
	---------
	注意点：
	使用英文括号()、表的名字 和 字段 尽量使用``括起来（TAB键上面的那个键）、注释中的字符串用英文下的单引号''括起来
	PRIMARY KEY 一个表只有唯一的一个主键、所有的语句后面都要加上英文逗号,最后一句除外
*/
CREATE TABLE IF NOT EXISTS `student`(
		`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学生学号',
		`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '学生姓名',
		`password` 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] `表名`(
	`字段名` 列类型 [属性] [索引] [注释]，
	`字段名` 列类型 [属性] [索引] [注释]，
	....
	`字段名` 列类型 [属性] [索引] [注释]
) [表类型] [字符集设置] [注释];

修改数据表

--创建新数据表
CREATE table if not EXISTS `teacher`(
		`id` int(10) not null comment '教师编号'
)ENGINE=INNODB DEFAULT charset=utf8;

--修改表名  ALTER TABLE	旧表名 RENAME as 新表名;
ALTER TABLE `teacher` RENAME as `teacher1`;

--增加字段 ALTER TABLE 表名 ADD 字段名 类类型;
	--一次添加一个
	ALTER TABLE `teacher1` ADD age int(3);  
   	--一次添加多个
	ALTER table `teacher` add(     
			name VARCHAR(5) COMMENT '教师姓名',
			age int(3) not null DEFAULT 0 comment '教师年龄'
	);

--修改表的字段（修改字段名字、修改字段属性）
	--修改字段属性 ALTER table 表名 MODIFY 字段名 新字段属性
	ALTER table `teacher1` MODIFY age VARCHAR(10);  --将age的字段属性由int改为varchar
   		--modify可以连着写
		alter table teacher modify id varchar(10),modify  age varchar(3);
	--修改字段名 ALTER TABLE 表名 CHANGE 旧字段名  新字段名 新/旧字段属性;
	ALTER TABLE `teacher1` CHANGE age  age1 int(1);  --同时修改age的名字和属性，由age变成age1，由int变为varchar

--删除表中的字段 ALTER TABLE 表名 DROP 字段名;
ALTER TABLE `teacher1` DROP age1;

--查看表的结构 desc 表名
desc `teacher`;

删除数据表

--删除表 DROP TABLE IF EXISTS 表名
DROP TABLE IF EXISTS `teacher1`;  --表结构和数据均被删除

--‘截断’某个表，删除表里的全部数据，但是保留表结构 truncate 表名;
truncate teacher;

MyISAM与InnoDB的区别

	MyISAM	InnoDB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约为MyISAM的2倍

INNODB在数据库中只有一个*.frm文件

MYISAM对应的文件：

• *.frm文件、
• *.MYD文件
• *.MYI文件

2、数据库操作语言DML

Data Manipulation Language主要有insert、update、delete组成

外键约束 Foreign Key

建立两个表之间的约束关系，如主表是这个学校的老师，从表是这个学校的学生，每一个学生对应一个老师，因此学生表与老师表就建立了约束关系。即这个学生的老师必须是老师表里已经存在的老师

• 方式一、创建表的时候增加外键（麻烦）

--新建一个老师表
create table if not exists teacher(
		id int(3) not null auto_increment comment '教师编号', 
		name varchar(5) comment '教师姓名',
		primary key(id)
)engine=innodb charset=utf8;

--新建一个学生表，学生的数学老师与教师表中的id具有外键约束
	--定义外键key 
	--给这个外键添加约束 reference 引用
create table if not exists student(
		id int(10) not null auto_increment comment '学生学号',
		name varchar(5) comment '学生姓名',
		age int(2) comment '学生年龄',
		math_teacher int(3) not null comment '学生的数学老师',
		primary key(id),
		key FK_teacher (math_teacher),  --定义外键的名字
		constraint FK_teacher foreign key (math_teacher) references teacher(id)  --确定外键的参考约束
)engine=innodb charset=utf8;

• 方式二、创建表成功后，采用修改表语句，添加外键约束（推荐）

--alter table 表名 add constraint 约束名 foreign key (作为外键的列) references 哪个表(哪个字段);
alter table student add constraint FK_teacher foreign key (math_teacher) references teacher(id);

！！！注意：删除具有外键约束的表时，必须先删除从表，才能删除主表。或者定义主表与从表之间的级联删除on delete cascade或者on delete set nulll用于指定当主表记录被删除时，从表中参照该主表的处的值自动变为null

以上都是物理外键，不建议使用，后面会利用程序去实现外键。

添加insert

insert into 表名（字段名） values(值)

--单个插入
insert into student(name) values('张三');
--多个插入
insert into student(sex,birthday,address) values ('女','1998-01-01','上海');
--全部插入，并且每次可以多插入几条
insert into student values
	(null,'李四','男','2000-01-01','南京'),
	(null,'小翠','女','1999-01-01','无锡');
--由于第一天语句运行了三次，所以student表显示如下：

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

删除delete

delete from 表名 where 条件

--将id=2 和 id=3的行数据删除
delete from student where id=2;
delete from student where id=3;

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

delete和truncate的区别

--相同点：删除整个表中所有的记录，不删除表结构
delete from 表名;  --不添加where约束即可
truncate 表名；

不同点：

• truncate 重新设置自增列，计数器归零
• truncate 不影响事务

create table test(
		id int(10) not null auto_increment,
		var varchar(10),
		primary key(id)
)engine=innodb charset=utf8;

insert into test values
			(null,'aaa'),
			(null,'bbb'),
			(null,'ccc');  --id自增到3了
--使用delete删除			
delete from test;
insert into test values(null,'ddd');  --此时id=4

--使用truncate删除
truncate test;
insert into test values(null,'ddd');  --此时id=1

delete删除问题：

• InnoDB：重启数据库后，自增列会从1开始（数据存储在内存中，断点即失）
• MyISAM：重启数据库后，继续从上一个自增量开始（数据存储在文件中，不会丢失）

修改update

update 表名 set column_name=value where 条件

--修改单个属性
update student set birthday='2001-01-01' where name='张三';  --将name为张三的行的birthday改为2001-01-01

--修改多个属性
update student set name='王五',sex='男',address='合肥' where id=6;

--修改整列
update student set name='哈哈';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5rKwoFeq-1594887381025)(C:\Users\24973\AppData\Roaming\Typora\typora-user-images\image-20200705160600882.png)] [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lNHEbQQN-1594887381027)(C:\Users\24973\AppData\Roaming\Typora\typora-user-images\image-20200705160716579.png)]

where 条件

检索数据中符合条件的值

操作符	含义	范围	结果
=	等于	5=6	false
<>或!=	不等于	5=6	true
< 或<=	小于/小于等于	5<6	true
> 或 >=	大于/大于等于	5>6	false
between … and…	两者之间，闭区间	[2,5]
and	与 &&
or	或 ||

--between and
update student set name='张三' where id between 4 and 6;

--and 
update student set name='李四' where name='张三' and sex='女';

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

3、数据库查询语言DQL

Date Query Language 主要由select组成

--SELECT 语法，注意：下面写的顺序是不可以改变的，如limit不能写到where上面
select [ALL | distinct]
{* | table.* | table.field1 [as '别名1'],table.field2 [as '别名2']...}
from table_name [as '表别名']
	[left | right | inner join table_name2 [as '表别名2']]  --连表查询
	[where ...]   --指定结果需满足的条件
	[group by...]  --指定结果按照哪几个字段来分组
	[having]   --过滤分组的记录必须满足的次要条件
	[order by...]   --指定查询记录按一个或多个条件排序  
	[limit {[offset,] row_count | row_countOFFSET offset}];  --指定查询的记录从哪条到哪条

基础查表

CREATE TABLE IF NOT EXISTS `student`(
		`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学生学号',
		`name` VARCHAR(30)  COMMENT '学生姓名',
		`password` VARCHAR(20)  COMMENT '密码',
		`sex` VARCHAR(2)  COMMENT '学生性别',
		`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
		`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
		`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
		`result` int(10) comment '成绩',
		PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=UTF8;

insert into student values
	(null,'张三','0001','男','2000-01-01','北京','123@','99'),
	(null,'李四','0002','男','2000-01-02','上海','124@','95'),
	(null,'王五','0003','男','2000-01-03','广州','125@','87'),
	(null,'王翠','0004','女','2000-01-04','深圳','126@','100'),
	(null,'小刘','0005','男','2000-01-05','南京','127@','78'),
	(null,'小陈','0006','女','2000-01-06','合肥','128@','92'),
	(null,'小崔','0001','男','2000-01-07','天津','129@','88');
	
--查询整个表
select * from student;

--查询表的指定字段
select name,address from student;
--给字段起别名 也可以给student表其别名
select name as 学生姓名, address as 学生地址 from student;

--concat 函数： concat(a,b)将a和b字符串拼接出来
select concat('姓名：',name) as 学生姓名 from student;

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

去除重复数据 distinct

--去除重复数据 select distinct 字段名 from 表名；
select distinct number from result;

其他查询

--查询系统版本（函数）
select version();  --输出：5.7.30-log
--计算结果（表达式）
select 100*3+1 as 计算结果;  --输出：301
--学生成绩都加1分
select id,result+1 as 提分后成绩 from student; 

where条件查询

逻辑查询：与 或 非

--与
--查询考试成绩在90-100分之间的学生  三种写法
select id,result from student where result>=90 and result<=100;  --and
select id,result from student where result>=90 && result<=100;  --&&
select id,result from student where result between 90 and 100;  --between and

--非
--查询成绩不为满分的学生
select id,result from student where result!=100;
select id,result from student where not result=100;

模糊查询：比较运算符

运算符	语法	描述
is null	a is null	a为null，结果为真
is not null	a is not null	a不为null。结果为真
between	a between b and c	若a在bc之间，结果为真
like	a like b	SQL匹配，如果a匹配b，则结果为真
in	a in(a1,a2,…)	假设a在a1，a2…之间，则结果为真

-----like的操作-------------
--查询姓王的同学 ：% 表示任意个字符、  _ 表示一个字符
select id,name from student where name like '王%';  --查询所有姓王的同学
select id,name from student where name like '王_';  --查询只有2个字的王姓同学
select id,name from student where name like '王__';  --后面跟了两个_，表示查3个字的王姓同学
select id,name from student where name like '%翠%';  --查询名字里有翠的同学

--in的操作，要有具体的值，不能使用%这些了
select id,name from student where id in(4,7);  --查询id为4和7的学生
select id,name,address from student where address in('北京','上海');  --查询地址是北京和上海的学生

--NOT null 的操作
select id,name,password from student where `password` is not null;  --查询所有password不为空的学生

--null 的操作
select id,name,password from student where `password` is null;  --查询所有password为空的学生
/*注意：一行中不写任何东西，等价于空字符串''，不代表null*/

多表查询 join on

操作	描述
inner join	返回共同拥有的
left join	返回左表中有的，即使右表中没有匹配值
left join	返回右表中有的，即使左表中没有匹配值

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-frweiAFu-1594887381031)(C:\Users\24973\AppData\Roaming\Typora\typora-user-images\image-20200705204605522.png)][外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7PX97DLE-1594887381032)(C:\Users\24973\AppData\Roaming\Typora\typora-user-images\image-20200705204619583.png)]

如上图所示，student表中id=13的同学在result_math中没有，result_math表中id=14的人在student中没有

/*思路：
1. 分析需求：分析查询的字段都在哪些表里
2. 确定使用哪种链接 7种
确定交叉点（这2个表中哪个数据是相同的）
*/
--查询学号、成绩、姓名、年龄
/*
1. 分析：学号，姓名，年龄在student表中，成绩在result表中。因此我们主要查询student表，然后外接result表即可
2. 交叉点：student表和result表都有学生id，因此这个id即为交叉点
*/
--inner join  只查询到id=12的学生
select s.id,name,result from 
student as s
inner join result_math as r
on s.id=r.id;

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

--right join 100分的学生也查出来了，因为使用的是s.id，故此时的id显示为null
select s.id,name,age,result from 
student as s right join result_math as r
on s.id=r.id;
-----------此时采用r.id，显示了id=14
select r.id,name,age,result from 
student as s right join result_math as r
on s.id=r.id;           

--right join 
select s.id,name,age,result from 
student as s left join result_math as r
on s.id=r.id;

--查询缺考的学生，即无成绩的人
select s.id,name,age,result from 
student as s left join result_math as r
on s.id=r.id
where result is NULL;

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

/* student表中有id,name，age，sex,
   result_math表中有id、result、dubject_id
   subject_tab表中有subject_id，subject_name
   现在要查询学生的id,name,result,subject_name
*/
--多表查询
/*分析：student和result_math中相同的是id，result_math和subject中相同的是subject_id
*/
select id,name,result,`subject` from 
student s inner join result_math r on s.id=r.id
inner join subject_tab sub on r.subject_id=sub.sybject_id;

自连接查询：自己和自己的表链接

核心：一张表拆为两张一样的表

新建一个数据库表如下，其中pid表示父类id，categoryID表示自己所属的id

categoryID	pid	categoryName
2	1	信息技术
3	1	软件开发
4	3	数据库
5	1	美术设计
6	3	web开发
7	5	ps技术
8	2	办公信息

拆开：

父类表：寻找pid为1的categoryName，表示一级目录

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

子类表：查找categoryID为2,3,4下的目录

pid	categoryID	categoryName
2	8	办公信息
3	4	数据库
3	6	web开发
5	7	ps技术

因此这两个表之间的关系是：子类表的pid是父类表的categoryID

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

--选出上面换的关系
select a.categoryName as '父表', b.categoryName as '子表'
from category as a, category as b
where a.categoryID=b.pid;

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

分页 limit、排序 order by

--排序 order by  升序 asc  降序  desc
select * from student where address='南京市' order by age desc;  --查询家庭住址为南京市的所有学生并按照年龄降序排序

--排序 limit (n-1)*pagesize,pagesize
/* 	 pagesize：当前页面大小，即当前页面显示的数据条数
  	 n：当前页，即第n页
	(n-1)*pagesize：起始值，即数据显示的起始值
	总页数：数据总数%页面大小==0 ? 数据总数/页面大小 : (数据总数/页面大小)+1
*/
select * from student   --查询年龄小于25的学生，并按照年龄大小升序排序，且显示从0开始的3条数据
where age<25
order by age asc
limit 0,3;

--查询 java第一学年 课程成绩排名 前5的学生，并且分数大学80分的学生信息（id,name,subject_name,result）
select student_id,student_name,subject_name,student_result  --查询学号，姓名，学科明，成绩
from student s   --student表中查询student_id,student_name
inner join result r on s.student_id=r.studet_id   --result中查询student_result
inner join subject sub on r.subject_id=sub.subject_id  --subject中查询subject_name
where subject_name='java第一学年' and student_result>=80  --课程是：java第一学年。分数是：80分以上
order by student_result desc   --前5名，所以采用降序排序
limit 0,5;   --选择前五名

子查询

子查询就是在一个查询语句中嵌套另一个子查询，子查询可以支持多层嵌套

--子查询  查询年龄大于学号为1的学生的信息
select id,name,age   --查询id,name,age信息
from student s   --从student表中
where age>(select age from student where id=1);  --年龄大于学号为1的学生的年龄

MYSQL中的函数

常用函数

--============  常用函数  ==============
-- 数字运算
select abs(-10);  --绝对值 10
select ceiling(9.5)  --向上取整 10
select floor(9.4)   --向下取整 9
select rand()       --返回一个0~1间的随机数
select sing(10)     --判断一个数的符号  0-0 负数返回-1，正数返回1 

--字符串
select char_length('hello');   --字符串长度 5
select concat('我爱','世界');  --拼接字符串  我爱世界
select insert('我爱世界',2,1,'超级爱');  --替换字符串  insert(str1,indext1,len,str2);在str1字符串中，将index1处开										始，往后的len个字符，替换成str2 我超级爱世界
select lower('Hello World');  --小写  hello world
select upper('Hello World');  --大写  HELLO WORLD
select instr('Hello World','l');  --返回第一次出现子串的索引  instr(str,substr) 在str中返回第一次出现substr的索引 3
select replace('Hello World','World','china');  --替换出现的字符  replace(str,str1.str2) 在str中，将str1出现的字符													串改为str2字符串  Hello china
select substr('Hello World',4,5);   --返回指定的子字符串  substr(str,index,len) 在str在，从index开始返回len个长度的										子串  lo Wo
select reverse('Hello World');  --反转  dlroW olleH
--查询所有姓 王 的同学并将其改为 朱 
select replace(name,'王','朱') from student where name 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();   --查询版本

聚合函数（常用）

函数名称	描述
count()	计数
sum()	求和
avg()	平均值
max()	最大值
min()	最小值
…	…

--===============  聚合函数  ==================
--count统计表中所有的数据
/*count(字段名)、count(*)、count(1)的区别
count(字段名)会忽略所有的null值
count(*) 不会忽略null值，本质是计算行数，但是使用*表示所有的列都会操作
count(1)不会忽略null值，本质是计算行数，但是只对一列进行操作
执行效率上：
1. 列名为主键，则count(主键)比count(1)块；反之列名不为主键，则count(1)比count(字段名)快
2. 多个列并且没有主键，则count(1)比count(*)快；反之，单个列，count(*)最快
3. 如果有主键，则count(主键)最快
*/
select count(name) from student;
select count(*) from student;
select count(1) from student;

select sum(res) as 总分 from result;
select avg(res) as 平均分 from result;
select max(res) as 最大分 from result;
select min(res) as 最小分 from result;

分组group by和过滤 having

--============  分组 group by /   过滤 having===========
--查询 不同课程 的平均分，最高分，最低分
	--核心：根据不同的课程分组
select subject_name，avg(student_result) as 平均分, max(student_result) as 最大分, min(student_result) as 最低分   --要查询的内容
from result as r    --从esult表中查询 student_result
inner join `subject` as sub   --从`subject`表中查询subject_name
on r.subject_id=sub.    --r和sub共有的是subject_id
group by r.subject_id;   --按照subject_id来分组    

--在加一个条件，平均分要大于80
select subject_name，avg(student_result) as 平均分, max(student_result) as 最大分, min(student_result) as 最低分   --要查询的内容
from result as r    --从esult表中查询 student_result
inner join `subject` as sub   --从`subject`表中查询subject_name
on r.subject_id=sub.    --r和sub共有的是subject_id
group by r.subject_id   --按照subject_id来分组
having 平均分>80;    --过滤80分以下的

4、数据库控制语言DCL

Date Control Language主要由commit、rollback、savepoint完成，在事务中讲到

5、MD5密码加密

--=============   MD5 加密测试==================
--新建表
create table if not exists testmd5(
		id int(10) not null comment '用户ID号',
		name varchar(225) not null comment '用户名字',
		pwd varchar(255) not null comment '用户密码'
)engine=innodb default charset=utf8;

--插入数据  明文密码
insert into  testmd5 values(1,'张三','123456'),(2,'李四','526389'),(3,'王五','156894');
--加密密码
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');

三、事务

1、概念

事务是指满足ACID特性的一组操作，可以通过Commit提交一个事务，也可以使用Rollback回滚一个事务

2、ACID特性

1. 原子性 A：事务里有多个操作，事务的操作要么全部成功Commit，要么全部失败Rollback。回滚可以用回滚日志来实现
2. 一致性 C：事务前后的数据完整性要保持一致。（开启一个事务以后，在这个事务中，多次读取同一个数据，结果不变）
3. 隔离性 I：并发访问时，数据库为每一个用户开启事务，每个用户事务之间互相隔离，操作数据互不干扰
4. 持久性 D：一旦事务提交，则其结果会永远保存在数据库中，即使系统发生崩溃，也不会丢失事务处理的结果。系统崩溃后，可用重做日志进行恢复。

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

参考博客：https://blog.csdn.net/dengjili/article/details/82468576

3、并发一致性问题

• 脏读：一个事务读取了另一个事务未提交的数据
• 不可重复读：在事务内读取表中的一行数据，多次读取的结果不同
• 虚读（幻读）：在一个事务内读取到了别的事务插入的数据，导致前后读取不一致
• 丢失修改：一个事务的更新操作被另外一个事务的更新操作替换

--==========  事务  ====================
--mysql默认开启事务
set autocommit=0; --关闭 默认提交
set autocommit=1; --开启 默认提交

------手动处理事务
set autocommit=0;   --关闭自动提交
--事务开启
start transaction;   --从这往后，所有的sql语句都在同一个事务中

--提交事务：持久化（成功！）
commit;

--回滚事务：回到原来的样子（失败！）
rollback;

--事务结束
set autocommit=1;  --开启自动提交

savepoint 保存点名;   --设置一个事务的保存点
rollback to savepoint 保存点名;   --回滚到保存点
release savepoint 保存点名;  --撤销一个保存点

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

--==========  模拟事务 ======================
--新建一个数据库
create database if not exists shop;
use shop;

--新建表  #decimal(a,b) 表示有9位数，小数点后有2位
create table if not exists account(
		id int(10) not null auto_increment,
		`name` varchar(50) not null,
		money decimal(9,2) not null,    
		primary key(id)
)engine=innodb default charset=utf8;

--插入数据
insert into account(`name`,money) values ('A',1000.00),('B',2000.00);

--模拟转账：事务
set autocommit=0;  --关闭自动提交
start transaction;  --开启事务

update account set money=money-500 where `name`='A';   --A转出500元  
update account set money=money+500 where `name`='B';   --B转进500元

commit;  --提交事务
rollback;  --回滚事务
set autocommit=1;  --开启自动提交
/*上述在执行update的过程中，如果不到commit，数据库里A、B的money值都不会改变，只有commit以后才会改变*/

参考《事务》一文

四、索引

1、索引的分类

• 主键索引（primary key）：唯一的标识，主键不可重复，只能有一个列作为主键
• 唯一索引（unique key）：保证指定列不出现重复值，但可以出现多个null值
• 常规索引（key/index）：默认的
• 全文索引（FullText）：在特定的数据库引擎下才有，MyISAM中不支持

/*
创建索引的方式
	1.create table的时候写就加上索引
	2.alter table 处加上索引
	3.create index 加上索引
*/
--alter方式：alter table 表名 add 索引类 索引名字 （字段名）
alter table student add unique Uni_name (`name`);  --Uni_name是键的名字，()里是列名
--create方式：create index 索引名 on 表名（字段名）
create index id_student_age on student(age);  --索引名一般的命名规则：id_表名_

--索引删除 drop index 索引名 on 表名
drop index id_student_age on student

2、测试索引：降低查询速度

--显示所有的索引信息
show index from student;
--使用explain 分析sql执行的情况
explain select * from student;

--=============== 测试索引 =============================
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(50) default '' comment '手机号',
		gender tinyint(4) unsigned default '0' comment '性别（0：男，1：女）',
		`password` varchar(100) not null comment '密码',
		age tinyint(4) default '0' comment '年龄',
    primary key(id)
)engine=innodb default charset=utf8mb4 comment='app用户表';

--插入100万条数据  使用循环实现
DELIMITER $$
create function mock_date()
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),'123456@qq.com',concat('187',FLOOR(RAND()*10000000)+10000000),
		FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
		set i=i+1;
	end while;
	RETURN i;
end;
--执行插入语句
select mock_date();

--查询数据，未加索引
select * from app_user where `name`='用户99999';  --用时0.512s
select * from app_user where `name`='用户9999';   --用时0.503s

explain select * from app_user where `name`='用户9999';   --共查询了993797条数据

--添加索引后 
create index id_app_user_name on app_user(`name`);

select * from app_user where `name`='用户99999';   --用时0.001s
select * from app_user where `name`='用户9999';   --用时0.001s
  
explain select * from app_user where `name`='用户9999';  --共查询了1条数据

从上面的例子可以看出：索引在小数据量时，用处不明显，但是在大数据的时候，区分十分明显

3、索引原则

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

4、索引的数据结构

索引的数据结构：参考博客http://blog.codinglabs.org/articles/theory-of-mysql-index.html

B+tree：InnoDB默认的数据结构、MongoDB采用的是BTree结构

MyISAM

MyISAM叶节点的data域存放的是数据记录的地址。无论是主键索引还是其他类型的索引，其data域存放的都是指向该行数据记录的物理地址，并不是真正的数据文件。假设以Col1为主键，则MyISAM索引文件如下图所示，（Mysql中逻辑上相邻的记录其物理地址未必相连）

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

假设不存在主键索引，只在Col2上建立一个辅助索引，那么此时MyISAM的索引结构如下：

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

从上面两个图可以看出，主键索引和辅助索引没有什么不同，其叶节点的data中存放的都是数据地址，唯一的不同是主键索引要key不同，辅助索引的key可以重复。

InnoDB

（1）InnoDB采用主键索引时，主键索引的叶节点的date域中保存的是完整的数据记录，以Col1为主键，则InnoDB主索引示意图如下

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

可以看出此时date域中存放的不在是地址指针了，而是真实的数据。

针对上图每一个叶节点，可以看到Col1、Col2、Col3的数据都有，说明叶节点包含了完整的数据，这种索引叫做***聚集索引***。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

（2）当InnoDB中采用辅助索引时，辅助索引的data域存储的是相应主键的值，不在是整个数据记录了。假设在Col3上定义一个辅助索引，则结构如下图所示：

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

此时数据查询的过程要经过两个步骤：a、先检索辅助索引得到主键值；b、然后利用主键值到主索引中检索获得记录。

举个栗子

例如在上表中查询（30,91，Eric）这条记录：

（1）如果是MyISAM，无论采用那一列作为主键索引或辅助索引，都只会在data域获得OFX3这个地址值，然后通过这个地址值找到（30,91，Eric）

（2）如果是InnoDB，采用主键索引Col1，则查询到Col1=30后，直接从其data域中得到Col2=91,Clo3=Eric

​ 如果采用辅助索引，如Col3，则先找到Col3=Eric后，从其data域中得到Col1=30，再重复上面的步骤

注意：多列索引的存在有时也能得到和主键相同的效果

比如，现在只要查找（91，Eric），那么我们可以将Col2和Col3设置成同一索引，此时按照辅助索引规则查询，就可得到最终的结果，不需要取出其data域中的Col1=30再进行查询了，因为我们要查的数据只有Col2和Clo3

这里又会产生一个问题：多列索引查找规则。

多列索引查找规则

需要满足最左前缀原则，比如(a,b,c)是联合索引，那么在查找的过程中，首先按照a查找，找到所有满足条件的数据，然后按照b查找，找到所有既满足a又满足b的数据，最后按照c查找，找到既满足ab又满足c的数据。

联合索引的生效原则是从前往后依次生效，如果中间某个索引没有使用，那么断点前面的索引起作用，断点后面的索引不起作用。比如对于(a,b,c)联合索引来说，其查找的过程中必须保证a的存在，只有(a)、(a,b)、(a,b,c)会真正的起到联合索引的作用。

举个栗子：

create table if not exists test(
		id int(10) not null auto_increment,
		a int(10) not null,
		b int(10) not null,
		c int(10) not null,
		primary  key(id)   --主键是id
)engine=InnoDB default charset=utf8;
--随机生成1000条数据
delimiter $$
create function mock_date()
returns int 
begin 
	declare i int default 0;
	declare num int default 1000;
	while i<num do
		insert into test values (null,rand()*10,rand()*100,rand()*100);
		set i=i+1;
		end while;
		return i;
end;
--插入数据
select mock_date();

--指定(a,b,c)是联合索引
create index id_test on test(a,b,c); 

--==============  测试联合索引 （等值查询）==========================
select * from test where a=5 and b=5 and c=81;   --都起作用（这里a,b,c换位置不影响结果，Mysql会自动按照a,b,c的顺序查找）此处只查到了一条数据，说明这个表里a=5 b=5 c=81的记录只有1条
explain select * from test where a=7 and b=5 and c=10;  --查询了1条数据

select * from test where a=5 and b=5;  --只有a,b起作用，查到了3条记录，说明这哥表里s=5 b=5的数据有3条
explain select * from test where a=10 and b=5;   --查询了3条语句

select * from test where a=5 and c=81;   --只有a起作用，因为b是断点
explain select * from test where a=5 and c=81;   --查询了102条数据

select * from test where b=5 and c=81;  --b,c均不起作用，因为a是断点
explain select * from test where b=5 and c=81;  --查询了1000条数据

--================  测试联合索引 （范围查询）  ========================
select * from test where a=1 and b>10 and c=12;  --a有用，b是用到了索引，但是因为b是范围值，相当于是断点，因此c没有
explain select * from test where a=1 and b>10 and c=12;  --81条

select * from test where a>5 and b=10 and c=64;  --a用到了索引，但是a相当于断点，所以b,c无用
explain select * from test where a>5 and b=10 and c=64;  --458

select * from test where a=1 order by b;  --a有用，b用到了索引
explain select * from test where a>5 and b=10 and c=64;  --458

/*以上注意：“有用”和“用到了索引”是不同的概念。只有等值查询的时候索引才“有用”，其余的查询只是“用到了索引”而已，还是相当于是断点*/

五、其他

1、数据库备份

• 使用navicate可视化工具导出
• 使用dos命令行导出

-- mysqldump -h 主机 -u root -p 密码 数据表 表名 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -padmin school student > D:/a.sql   --一次导出一张表
mysqldump -hlocalhost -uroot -padmin school student result > D:/b.sql  --一次导出多个表
mysqldump -hlocalhost -uroot -padmin school > D:/.sql --导出整个数据库

--导入
 --登录情况下
 source d:/a.sql
 
 --没登录情况下
 mysql -u root -padmin student d:/a.sql

2、规范数据库设计

三大范式：

第一范式：原子性，保证每一列不可再分

第二范式：前提是满足第一范式，然后每张表只描述一件事情

第三范式：前提是满足第一、第二范式，需要确保数据表中的每一列数据都和主键直接相关，而不是间接相关

参考博客：https://www.cnblogs.com/wsg25/p/9615100.html

3、Mysql日志

日志类型	日志内容
错误日志(Erroe Log)	mysql 启动、停止和运行过程中出现的异常
普通查询日志(Generl Query Log)	记录客户端连接数据库后所有执行的语句
二进制日志(Binary Log)	当数据库发生改变时，产生该日志
中继日志(Relay Log)	从库收到主库的数据更新时产生该日志
慢查询日志(Slow query log)	SQL语句执行超过指定时间时产生该日志
DDL日志（元日志）	执行DDL语句操作元数据时产生该日志

中继日志：为了复制数据库而存在。

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

六、JDBC

1、JDBC：JAVA连接数据库

package mysql_test;

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

public class JdbcFirstDemo {
	public static void main(String[] args) throws SQLException, ClassNotFoundException {
		//1.加载驱动
		Class.forName("com.mysql.cj.jdbc.Driver");  //固定写法，注意新版本的jdbc不能写成com.mysql.jdbc.Driver
		
		//2.用户信息和url
        /*	school：表示使用的数据库名字
        	useUnicode=true  表示使用的字符集是Unicode，因此可使用中文
        	characterEncoding=utf8  表示编码方式为utf8
        	useSSL=true  表示使用安全连接方式
        	serverTimezone=UTC  设置全球标准时间，在jdbc 6.0版本以上必须设置，否则会报错
        */
		String url="jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC";
		String username="root";
		String password="admin";
		
		//3.链接成功，获取数据库。  此处的connection即表示一个数据库
		Connection connection=DriverManager.getConnection(url,username,password);
		
		//4.创建 执行sql的对象
		Statement statement=connection.createStatement();
		
		//5.执行sql语句，如果有结果，则查看返回的结果
		String sql="SELECT * FROM student where id<3";
		
		ResultSet resultset=statement.executeQuery(sql);  //返回结果集，结果集中封装了我们全部查询出来的结果
		while(resultset.next()) {
			System.out.println("id="+resultset.getObject("id"));
			System.out.println("age="+resultset.getObject("age"));
			System.out.println("address="+resultset.getObject("address"));
			System.out.println("name="+resultset.getObject("name"));
			System.out.println("======================");
		}
		
		//6.关闭连接
		resultset.close();
		statement.close();
		connection.close();
	}
}

步骤总结：

1. 加载驱动
2. 连接数据库 DriverManager
3. 获取执行sql的对象 Statement
4. 执行sql语句，获得返回的结果集
5. 释放连接

注意：需要导入mysql-connector-java的jar包。参考《eclipse导入外部jar包的步骤》

代码解释

/*
	DriverManager的作用
	1.加载驱动
	2.连接数据库
*/
//DriverManager.registerDiver(new com.mysql.cj.jdbc.Driver())
Class.forName("com.mysql.cj.jdbc.Driver");

//连接数据库
Connection connection=DriverManager.getConnection(url,username,password);
//connection表示数据库，因此可以执行事务的一些操作
connection.rollback();  //事务回滚
connection.commit();  //事务提交
connection.setAutoCommit(true 或 false);   //设置事务的自动提交方式

/*
	Statement 执行sql的对象  PrepareStatement也是执行sql的对象
*/
//创建执行sql的对象
Statement statement=connection.createStatement();
ResultSet resultset=statement.executeQuery();  //执行查询操作，结果返回ResultSet
statement.execute();   //执行任何sql
statement.executeUpdate();   //更新、插入、删除操作，都用这个语句，返回一个受影响的行数

/*
	ResultSet 查询结果集：封装所有的查询结果
*/
resultset.getObject();  //在不知道列类型下使用
//在已知列类型下使用
resultset.getInt();   
resultset.getFloat();
resultset.getString();
.....
//指针，遍历的作用
resultset.beforeFirst();  //移动到最前面
resultset.afterLast();  //移动到最后
resultset.next();   //移动到下一个数据
resultset.previous();    //移动到前一行
resultset.absolute(row); //移动到指定行

2、简化上述连接代码

步骤1：在当前项目目录下新建一个mysql.ini文件，保存信息数据库的driver、url、user、password信息

！！！注意：如果需要更改数据库，则需要在url中将school改为要使用的数据库名

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

步骤2：封装一个类 JdbcUtils 来驱动数据库

package mysql_test;

import java.sql.Statement;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
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 {
			Properties properties=new Properties();
			properties.load(new FileInputStream("mysql.ini")); //这里因为mysql.ini在项目目录文件下，如果在其他处，需要输入完整的路径名
			
			driver=properties.getProperty("driver");
			url=properties.getProperty("url");
			username=properties.getProperty("username");
			password=properties.getProperty("password");
	
			//加载驱动
			Class.forName(driver);   //需要捕获ClassNotFoundException异常，因此下面直接用一个大异常类Exception
		} catch (Exception e) {
			e.printStackTrace();
		} 
	}
	
	//获取数据库连接对象
	public static Connection getConnection() throws SQLException {
		return DriverManager.getConnection(url,username,password);
	}
	
	//释放连接的资源
	public static void release(Connection connection,Statement statement,ResultSet resultSet) {
		if(connection!=null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(statement!=null) {
			try {
				statement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(resultSet!=null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}

步骤3：新建一个类，使用封装好的 JdbcUtils 类来驱动数据库，然后在这个类中执行sql语句

package mysql_test;

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

public class Demo1 {
	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 test(id,`name`,age) values (1,'张三',25)"; //插入
			//String sql="update test set `name`='李四'";   修改
            //String sql="delete from test where id=1";   删除
            
			int i=statement.executeUpdate(sql);  //insert语句属于executeUpdate下的操作，会返回数据库被影响的行数
			if(i>0) {
				System.out.println("插入成功");
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JdbcUtils.release(connection, statement, resultSet);
		}
	}
}

如果需要执行其他的sql语句，只需要在Demo1类中，修改String sql语句以及executeUpdate即可，这样大大减少了每一次创建的过程。

• 增删改（insert、update、delete）都是使用executeUpdate()
• 查询（select）使用executeQuery()

3、SQL注入

SQL注入会导致数据库不安全，容易被攻击

//SQL注入问题
package mysql_test;

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

public class Demo2 {
	
	//登录业务：只有当输入的name和password正确才能登录成功
	public static void login(String name,String password) {
		Connection conn=null;
		Statement stmt=null;
		ResultSet set=null;
		
		try {
			conn=JdbcUtils.getConnection();
			stmt=conn.createStatement();
			
			String sql="select * from test where `name`='"+name+"' and `password`='"+password+"'";
			
			set=stmt.executeQuery(sql);
			
			while(set.next()) {
				System.out.println("name="+set.getString("name"));
				System.out.println("password="+set.getString("password"));
				System.out.println("=========================");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JdbcUtils.release(conn, stmt, set);
		}
	}
	public static void main(String[] args) {
		//正常登录
		//login("张三","123456");   //这里只有输入test表中已经有的姓名和密码，才能正确执行
		
		//SQL注入
		login(" 'or '1=1"," 'or'1=1");
	}
}
//正常输入时的输出结果：name=张三，password=123456
/*SQL注入时输出结果：可以看出将整个数据库的用户和密码数据都盗取了
name=张三
password=123456
=========================
name=李四
password=123321
=========================
name=王五
password=654321
=========================
*/

解决SQL注入

使用PreparedStatement对象，而且效率会更好

//PreparedStatement的使用方法
package mysql_test;

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

public class PrepareStatementTest {
	public static void main(String[] args) {
        
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet set=null;
		
		try {
			conn=JdbcUtils.getConnection();
			
			//PreparedStatement与Statement的区别之处
			//1.先写sql语句，使用？占位符代替参数
			String sql="select * from test where id=? or `name`=?"; 
			
			//2.预编译sql语句，不执行，预编译结果传给PreparedStatement对象
			pstmt=conn.prepareStatement(sql);
			
			//3.手动给参数赋值
			pstmt.setInt(1, 2);  //第一个？赋值为2
			pstmt.setString(2, "张三");    //第二个？赋值为"张三"
			
			//4.执行
			set=pstmt.executeQuery();
			
			while(set.next()) {
				System.out.println("id="+set.getInt("id"));
				System.out.println("name="+set.getString("name"));
				System.out.println("==================");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JdbcUtils.release(conn, pstmt, set);
		}
	}
}

PreparedStatement防止SQL注入的本质是：将传递进来的参数当做字符，如果其中存在转义字符，比如说’’，会直接被转义忽略，因此判断的就是最终的字符是否符合要求。

//PreparedStatement防止SQL注入
package mysql_test;

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

public class Demo3 {
	
	public static void login(String name,String password) {
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet set=null;
		
		try {
			conn=JdbcUtils.getConnection();
			String sql="select * from test where `name`=? and `password`=?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, name);
			pstmt.setString(2, password);
			set=pstmt.executeQuery();
			while(set.next()) {
				System.out.println("name="+set.getString("name"));
				System.out.println("password="+set.getString("password"));
				System.out.println("=========================");
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JdbcUtils.release(conn, pstmt, set);
		}
	}
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		//login("张三","123456");
		//SQL注入
		login("'' or 1=1","123456");
	}
}
/*
	正常登录，输出：name=张三，password=123456
	非正常登录，输出：无结果，但也不报错。因此很好的防止了SQL注入问题
*/

4、JDBC操作事务

package mysql_test;

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

public class Demo4 {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet set=null;
		
		try {
			conn=JdbcUtils.getConnection();
			//关闭数据库自动提交，自动开启事务
			conn.setAutoCommit(false);
			
            //A账户减少200元
			String sql1="update account set money=money-200 where name='A'";
			pstmt=conn.prepareStatement(sql1);
			pstmt.executeUpdate();
			
            //B账户增加200元
			String sql2="update account set money=money+200 where name='B'";
			pstmt=conn.prepareStatement(sql2);
			pstmt.executeUpdate();
			
			//业务完毕，提交事务
			conn.commit();
			System.out.println("成功！");
			
		} catch (SQLException e) {
			try {
				conn.rollback();     //如果失败，则回滚事务。实际上可以不写，因为JDBC默认会回滚事务
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}finally {
			JdbcUtils.release(conn, pstmt, set);
		}
	}
}

步骤总结：

1. 开启事务：conn.setAutoCommit(false);
2. 一组业务执行完毕，提交事务：conn.commit();
3. 可以在catch语句中显式定义回滚语句，但默认识别就会回滚

5、数据库连接池

作用

由于每次都要连接getConnection()才能得到connection，然后又释放connection，这个过程十分耗费资源，因此创建一个数据库连接池，里面存放一些已经连接好的connection，使得需要用的时候直接使用，不需要getConnection()，而用完以后，就放回池子里，不close掉。类似于线程池

参数

• 最小连接数
• 最大连接数
• 等待超时：ms（当业务量大于最大连接数时，会等待）

实现方式

• 编写连接池，实现一个接口DataSource
• 使用开源数据源，如DBCP、C3P0。使用这些开源数据库连接池后，我们在项目中开发中就不需要编写连接数据库的代码了

数据源DBCP

​ 1.导入commons dbcp和commons pool 两个jar包。如果导入这两个包以后，运行程序出现java.lang.NoClassDefFoundError错误，那么需要再导入一个commons logging的jar包。

​ 2.编写dbcp的属性文件，在当前项目下新建一个dbcpconfig.properties文件，输入配置语句，网上找到的配置文件如下图所示，但是有一些需要修改的地方。

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

最终配置文件如下，school是我要用的数据库名字，我的数据库密码为admin

#连接设置
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC
username=root
password=admin

#<!-- 初始化连接 -->
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 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

​ 3.创建DBCP工具类

package mysql_test;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;

public class DbcpUtils {
	private static DataSource dataSource;
	static {
		try {
			Properties properties=new Properties();
			properties.load(new FileInputStream("dbcpconfig.properties"));  
			
			//创建数据源
			dataSource=BasicDataSourceFactory.createDataSource(properties);  
		} catch (Exception e) {
			e.printStackTrace();
		} 
	}
	//获取连接对象
	public static Connection getConnection() throws SQLException {
		return dataSource.getConnection();    //从数据源中获取连接
	}
	
	//释放连接的资源
	public static void release(Connection connection,Statement statement,ResultSet resultSet) {
		if(connection!=null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(statement!=null) {
			try {
				statement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(resultSet!=null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}

​ 4.使用DBCP工具类

package mysql_test;

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

public class DbcpTest {
	public static void main(String[] args) {
		Connection connection=null;
		PreparedStatement pstmt=null;
		ResultSet set=null;
        
		try {
			connection=DbcpUtils.getConnection();
			
			String sql="insert into student (age,address,`name`) values (?,?,?)";
			pstmt=connection.prepareStatement(sql);
			
			pstmt.setInt(1, 19);
			pstmt.setString(2, "武汉市");
			pstmt.setString(3, "杨树");
			
			int i=pstmt.executeUpdate();
			if(i>0) {
				System.out.println("插入成功");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DbcpUtils.release(connection, pstmt, set);
		}
	}
}

数据源C3P0

1.导入c3p0-0.9.5.2.jar和mechange-comments-java-0.2.11.jar两个包

2.配置c3p0的xml文件。在当前项目下新建一个文件，名字为c3p0config.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/school?serverTimezone=UTC&amp;useUnicode=true&amp;useUnicode=true</property>
		<property name="user">root</property>
		<property name="password">admin</property>
		
		<property name="initialPoolSize">10</property>
		<property name="maxPoolSize">100</property>
		<property name="minPoolSize">60</property>
		<property name="maxStatements">200</property>
	</default-config>
    
     <!--C3P0默认配置，如果在代码中"ComboPooledDataSource ds=new ComboPooledDataSource("MySQL");"这样写表示使用的是C3P0中名字为MySQL的配置-->
	<name-config name="MySQL">
		<property name="diverClass">com.mysql.cj.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql://localhost:3306/school?serverTimezone=UTC&amp;useUnicode=true</property>
		<property name="user">root</property>
		<property name="password">admin</property>
		
		<property name="maxPoolSize">20</property>
		<property name="minPoolSize">5</property>
		<property name="initiaPoolSize">10</property>	
	</name-config>>
</c3p0-config>

在xml文件中，可以写多个c3p0的配置，只要名字不同即可，这样可以使得jdbc连接多个数据库，比如有org的数据库就可以新建一个name，里面写上org数据库的链接方式即可。

在url中需要更改使用的数据库名，同时采用&amp来表示之前写的&；在password中该为自己的密码。

3.创建工具类

package mysql_test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0Utils {
	private static ComboPooledDataSource source=null;
	static {
		try {
			//创建数据源，选择使用默认的配置方式
			//方式1，如果不使用上面的xml文件的话，需要手动配置
			source = new ComboPooledDataSource();
			source.setDriverClass("com.mysql.cj.jdbc.Driver");
			source.setJdbcUrl("jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC");
			source.setUser("root");
			source.setPassword("admin");
			source.setMaxPoolSize(100);
			source.setMinPoolSize(10);
			
			//方式2、自动配置，使用了上面的xml文件
            /*
			  	由于xml文件会自动读取，因此Properties属性文件的读取都可以省略
				Properties properties=new Properties();
				properties.load(new FileInputStream("c3p0config.properties"));
			*/
			//source = new ComboPooledDataSource();   使用xml中的默认配置
            //source = new ComboPooledDataSource("MySQL");    使用xml中名字为MySQL的配置
		} catch (Exception e) {	
			e.printStackTrace();
		}
	}
	//获取连接
	public static Connection getConnection() throws SQLException {
		return source.getConnection();
	}
	//释放资源
	public static void release(Connection connection,Statement statement,ResultSet resultSet) {
		if(connection!=null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(statement!=null) {
			try {
				statement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(resultSet!=null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}

4.使用工具类

package mysql_test;

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

public class C3P0Test {
	public static void main(String[] args) {
		Connection connection=null;
		PreparedStatement pstmt=null;
		ResultSet set=null;
		
		try {
			connection=C3P0Utils.getConnection();
			
			String sql="insert into student (age,address,`name`) values (?,?,?)";
			
			pstmt=connection.prepareStatement(sql);
			
			pstmt.setInt(1, 29);
			pstmt.setString(2, "扬州市");
			pstmt.setString(3, "催芽");
			
			int i=pstmt.executeUpdate();
			if(i>0) {
				System.out.println("插入成功");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			C3P0Utils.release(connection, pstmt, set);
		}
	}# Mysql

JavaEE：企业级开发  web

1. 前端：页面（展示数据）
2. 后台：连接点（链接数据库JDBC；链接前端，控制页面跳转，给前端传递数据）
3. 数据库：存数据（Txt、Excel、word）

我的数据库连接密码：**admin**（非常重要，如果记不住真的要重新连接好久。。。。）



### 一、相关概念

1、数据库(DataBase，DB)：存储数据，管理数据。是一种软件，安装在操作系统（windows、linux、mac....）之上的

2、分类

**关系型数据库：（SQL）**

- MySQL、Oracle、Sql Server、DB2，SQLlite
- 通过表和表之间、行和列之间的关系进行数据的存储

**非关系型数据库：（NoSQL） Not only SQL**

- Redis、MongDB
- 对象存储，通过对象的自身属性来决定

**SQL和No SQL的区别**

（1）数据存储方式不同：

- SQL的存储是表格式的，数据存储在表的行和列中，不同的数据表可以关联协作
- NO SQL是一大块组合在一起的，其通常存储在数据集中，就像文档、键值对或图结构等，对于数据的格式十分灵活没有固定的表结构

（2）可扩展性不同

- SQL扩展性低
- NO SQL由于数据之间没有耦合性，所以非常容易水平扩展

（3）数据一致性

- SQL要求满足ACID原则，强调数据的强一致性
- NO SQL一般强调的是数据最终的一致性，从NO SQL中读到的有可能还是处于一个中间态的数据。

3、**DBMS（DataBase Managment System）：数据库管理系统**

它是一种数据库管理软件，能科学有效的管理我们的数据。**MySQL就是一种数据库管理系统**，其操作数据所用的语言叫SQL语句

4、navicat 是一款数据库的可视化工具

### 二、SQL语句

不区分大小写、语句结尾要加分号；

```SQL
--连接数据库
mysql -uroot -padmin
--修改用户密码
update mysql.user set anthentication_string=password('admin') where user='root' and Host='localhost';
--刷新权限
flush privileges;
-------------------------------------------
--创建一个数据库，库名是test
create database test;
--查看所有的数据库
show databases;
--切换数据库  use 数据库名
use school;
--查看一个数据库下所有的表（前提是一定要使用了一个数据库，即采用use命令进入了一个库）
show tables;
--显示数据库中某个表的信息   
describe student;   --这里的student是school数据库下一个表的名字
--  单行注释（--是SQL的本来的注释）
/**/  多行注释

1、数据库定义语言DDL

Date Definition Language主要由create、alter、drop、truncate四个关键字完成

数据库

新建、删除、使用、查看

--新建数据库（其中IF NOT EXISTS并不是必须的 ）
CREATE DATABASE If NOT EXISTS test;
--删除数据库（IF EXISTS并不是必须）
DROP DATABASE If EXISTS test;
--使用数据库（其中school是一个数据库名）
USE school;
--查看所有数据库
SHOW DATABASES;

数据库的列类型

1. 数值

​ tinyint 十分小的数据 1个字节

​ smallint 较小的数据 2个字节

​ mediumint 中等大小的数据 3个字节

​ int 标准的整数 4个字节 常用的

​ bigint 较大的数据 8个字节

​ float 浮点数 4个字节

​ double 浮点数 8个字节

​ decimal 字符串形式的浮点数 金融计算的时候，一般使用decimal

2. 字符串

​ char 字符串固定大小 0~255

​ varchar 可变字符串 0~65535 常用的变量 String

​ tinytext 微型文本 2^8-1

​ text 文本串 2^16-1 保存大文本

3. 时间日期

​ date YYY-MM-DD 日期

​ time HH:mm:ss 时间格式

​ datetime YYY-MM-DD HH:mm:ss 最常用的时间格式

​ timestamp 时间戳 表示1970.1.1到现在的毫秒数 较为常用

​ year 年份

4. null：没有值，未知

​ 注意不要使用null进行计算

数据库的约束

1. Unsigned：无符号整数，声明了该列不能为负数

2. zerofill：0填充。当出现不足的位数时，采用0填充，如int(3)，输出5后变成了005

3. 自增：设置唯一的主键，必须是整数类型，可以自定义设计主键自增的起始值和步长

4. 非空：NULL/not NULL

   ​ 设置为NULL：如果不填写值，默认就是null

   ​ 设置为not NULL：如果不赋值，就会报错，此时添加一个默认值

5. 默认：设置默认的值

   ​ 如sex，设置默认的’男’，那么所有的sex默认的都是‘男’

注意：在正式的项目中，每个表都必须存在以下5个字段，表示一个记录存在的意义

/*
id		主键
verson    乐观锁
is_delete     伪删除
gmt_create     创建时间
gmt_update    修改时间
*/

数据表

新建数据表

/*
	建立一个student表，包含学生的信息
	学号、姓名、登录密码、性别、出生日期、家庭地址、邮箱
	---------
	注意点：
	使用英文括号()、表的名字 和 字段 尽量使用``括起来（TAB键上面的那个键）、注释中的字符串用英文下的单引号''括起来
	PRIMARY KEY 一个表只有唯一的一个主键、所有的语句后面都要加上英文逗号,最后一句除外
*/
CREATE TABLE IF NOT EXISTS `student`(
		`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学生学号',
		`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '学生姓名',
		`password` 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] `表名`(
	`字段名` 列类型 [属性] [索引] [注释]，
	`字段名` 列类型 [属性] [索引] [注释]，
	....
	`字段名` 列类型 [属性] [索引] [注释]
) [表类型] [字符集设置] [注释];

修改数据表

--创建新数据表
CREATE table if not EXISTS `teacher`(
		`id` int(10) not null comment '教师编号'
)ENGINE=INNODB DEFAULT charset=utf8;

--修改表名  ALTER TABLE	旧表名 RENAME as 新表名;
ALTER TABLE `teacher` RENAME as `teacher1`;

--增加字段 ALTER TABLE 表名 ADD 字段名 类类型;
	--一次添加一个
	ALTER TABLE `teacher1` ADD age int(3);  
   	--一次添加多个
	ALTER table `teacher` add(     
			name VARCHAR(5) COMMENT '教师姓名',
			age int(3) not null DEFAULT 0 comment '教师年龄'
	);

--修改表的字段（修改字段名字、修改字段属性）
	--修改字段属性 ALTER table 表名 MODIFY 字段名 新字段属性
	ALTER table `teacher1` MODIFY age VARCHAR(10);  --将age的字段属性由int改为varchar
   		--modify可以连着写
		alter table teacher modify id varchar(10),modify  age varchar(3);
	--修改字段名 ALTER TABLE 表名 CHANGE 旧字段名  新字段名 新/旧字段属性;
	ALTER TABLE `teacher1` CHANGE age  age1 int(1);  --同时修改age的名字和属性，由age变成age1，由int变为varchar

--删除表中的字段 ALTER TABLE 表名 DROP 字段名;
ALTER TABLE `teacher1` DROP age1;

--查看表的结构 desc 表名
desc `teacher`;

删除数据表

--删除表 DROP TABLE IF EXISTS 表名
DROP TABLE IF EXISTS `teacher1`;  --表结构和数据均被删除

--‘截断’某个表，删除表里的全部数据，但是保留表结构 truncate 表名;
truncate teacher;

MyISAM与InnoDB的区别

	MyISAM	InnoDB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约为MyISAM的2倍

INNODB在数据库中只有一个*.frm文件

MYISAM对应的文件：

• *.frm文件、
• *.MYD文件
• *.MYI文件

2、数据库操作语言DML

Data Manipulation Language主要有insert、update、delete组成

外键约束 Foreign Key

建立两个表之间的约束关系，如主表是这个学校的老师，从表是这个学校的学生，每一个学生对应一个老师，因此学生表与老师表就建立了约束关系。即这个学生的老师必须是老师表里已经存在的老师

• 方式一、创建表的时候增加外键（麻烦）

--新建一个老师表
create table if not exists teacher(
		id int(3) not null auto_increment comment '教师编号', 
		name varchar(5) comment '教师姓名',
		primary key(id)
)engine=innodb charset=utf8;

--新建一个学生表，学生的数学老师与教师表中的id具有外键约束
	--定义外键key 
	--给这个外键添加约束 reference 引用
create table if not exists student(
		id int(10) not null auto_increment comment '学生学号',
		name varchar(5) comment '学生姓名',
		age int(2) comment '学生年龄',
		math_teacher int(3) not null comment '学生的数学老师',
		primary key(id),
		key FK_teacher (math_teacher),  --定义外键的名字
		constraint FK_teacher foreign key (math_teacher) references teacher(id)  --确定外键的参考约束
)engine=innodb charset=utf8;

• 方式二、创建表成功后，采用修改表语句，添加外键约束（推荐）

--alter table 表名 add constraint 约束名 foreign key (作为外键的列) references 哪个表(哪个字段);
alter table student add constraint FK_teacher foreign key (math_teacher) references teacher(id);

！！！注意：删除具有外键约束的表时，必须先删除从表，才能删除主表。或者定义主表与从表之间的级联删除on delete cascade或者on delete set nulll用于指定当主表记录被删除时，从表中参照该主表的处的值自动变为null

以上都是物理外键，不建议使用，后面会利用程序去实现外键。

添加insert

insert into 表名（字段名） values(值)

--单个插入
insert into student(name) values('张三');
--多个插入
insert into student(sex,birthday,address) values ('女','1998-01-01','上海');
--全部插入，并且每次可以多插入几条
insert into student values
	(null,'李四','男','2000-01-01','南京'),
	(null,'小翠','女','1999-01-01','无锡');
--由于第一天语句运行了三次，所以student表显示如下：

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

删除delete

delete from 表名 where 条件

--将id=2 和 id=3的行数据删除
delete from student where id=2;
delete from student where id=3;

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

delete和truncate的区别

--相同点：删除整个表中所有的记录，不删除表结构
delete from 表名;  --不添加where约束即可
truncate 表名；

不同点：

• truncate 重新设置自增列，计数器归零
• truncate 不影响事务

create table test(
		id int(10) not null auto_increment,
		var varchar(10),
		primary key(id)
)engine=innodb charset=utf8;

insert into test values
			(null,'aaa'),
			(null,'bbb'),
			(null,'ccc');  --id自增到3了
--使用delete删除			
delete from test;
insert into test values(null,'ddd');  --此时id=4

--使用truncate删除
truncate test;
insert into test values(null,'ddd');  --此时id=1

delete删除问题：

• InnoDB：重启数据库后，自增列会从1开始（数据存储在内存中，断点即失）
• MyISAM：重启数据库后，继续从上一个自增量开始（数据存储在文件中，不会丢失）

修改update

update 表名 set column_name=value where 条件

--修改单个属性
update student set birthday='2001-01-01' where name='张三';  --将name为张三的行的birthday改为2001-01-01

--修改多个属性
update student set name='王五',sex='男',address='合肥' where id=6;

--修改整列
update student set name='哈哈';

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

where 条件

检索数据中符合条件的值

操作符	含义	范围	结果
=	等于	5=6	false
<>或!=	不等于	5=6	true
< 或<=	小于/小于等于	5<6	true
> 或 >=	大于/大于等于	5>6	false
between … and…	两者之间，闭区间	[2,5]
and	与 &&
or	或 ||

--between and
update student set name='张三' where id between 4 and 6;

--and 
update student set name='李四' where name='张三' and sex='女';

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

3、数据库查询语言DQL

Date Query Language 主要由select组成

--SELECT 语法，注意：下面写的顺序是不可以改变的，如limit不能写到where上面
select [ALL | distinct]
{* | table.* | table.field1 [as '别名1'],table.field2 [as '别名2']...}
from table_name [as '表别名']
	[left | right | inner join table_name2 [as '表别名2']]  --连表查询
	[where ...]   --指定结果需满足的条件
	[group by...]  --指定结果按照哪几个字段来分组
	[having]   --过滤分组的记录必须满足的次要条件
	[order by...]   --指定查询记录按一个或多个条件排序  
	[limit {[offset,] row_count | row_countOFFSET offset}];  --指定查询的记录从哪条到哪条

基础查表

CREATE TABLE IF NOT EXISTS `student`(
		`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学生学号',
		`name` VARCHAR(30)  COMMENT '学生姓名',
		`password` VARCHAR(20)  COMMENT '密码',
		`sex` VARCHAR(2)  COMMENT '学生性别',
		`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
		`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
		`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
		`result` int(10) comment '成绩',
		PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=UTF8;

insert into student values
	(null,'张三','0001','男','2000-01-01','北京','123@','99'),
	(null,'李四','0002','男','2000-01-02','上海','124@','95'),
	(null,'王五','0003','男','2000-01-03','广州','125@','87'),
	(null,'王翠','0004','女','2000-01-04','深圳','126@','100'),
	(null,'小刘','0005','男','2000-01-05','南京','127@','78'),
	(null,'小陈','0006','女','2000-01-06','合肥','128@','92'),
	(null,'小崔','0001','男','2000-01-07','天津','129@','88');
	
--查询整个表
select * from student;

--查询表的指定字段
select name,address from student;
--给字段起别名 也可以给student表其别名
select name as 学生姓名, address as 学生地址 from student;

--concat 函数： concat(a,b)将a和b字符串拼接出来
select concat('姓名：',name) as 学生姓名 from student;

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

去除重复数据 distinct

--去除重复数据 select distinct 字段名 from 表名；
select distinct number from result;

其他查询

--查询系统版本（函数）
select version();  --输出：5.7.30-log
--计算结果（表达式）
select 100*3+1 as 计算结果;  --输出：301
--学生成绩都加1分
select id,result+1 as 提分后成绩 from student; 

where条件查询

逻辑查询：与 或 非

--与
--查询考试成绩在90-100分之间的学生  三种写法
select id,result from student where result>=90 and result<=100;  --and
select id,result from student where result>=90 && result<=100;  --&&
select id,result from student where result between 90 and 100;  --between and

--非
--查询成绩不为满分的学生
select id,result from student where result!=100;
select id,result from student where not result=100;

模糊查询：比较运算符

运算符	语法	描述
is null	a is null	a为null，结果为真
is not null	a is not null	a不为null。结果为真
between	a between b and c	若a在bc之间，结果为真
like	a like b	SQL匹配，如果a匹配b，则结果为真
in	a in(a1,a2,…)	假设a在a1，a2…之间，则结果为真

-----like的操作-------------
--查询姓王的同学 ：% 表示任意个字符、  _ 表示一个字符
select id,name from student where name like '王%';  --查询所有姓王的同学
select id,name from student where name like '王_';  --查询只有2个字的王姓同学
select id,name from student where name like '王__';  --后面跟了两个_，表示查3个字的王姓同学
select id,name from student where name like '%翠%';  --查询名字里有翠的同学

--in的操作，要有具体的值，不能使用%这些了
select id,name from student where id in(4,7);  --查询id为4和7的学生
select id,name,address from student where address in('北京','上海');  --查询地址是北京和上海的学生

--NOT null 的操作
select id,name,password from student where `password` is not null;  --查询所有password不为空的学生

--null 的操作
select id,name,password from student where `password` is null;  --查询所有password为空的学生
/*注意：一行中不写任何东西，等价于空字符串''，不代表null*/

多表查询 join on

操作	描述
inner join	返回共同拥有的
left join	返回左表中有的，即使右表中没有匹配值
left join	返回右表中有的，即使左表中没有匹配值

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

如上图所示，student表中id=13的同学在result_math中没有，result_math表中id=14的人在student中没有

/*思路：
1. 分析需求：分析查询的字段都在哪些表里
2. 确定使用哪种链接 7种
确定交叉点（这2个表中哪个数据是相同的）
*/
--查询学号、成绩、姓名、年龄
/*
1. 分析：学号，姓名，年龄在student表中，成绩在result表中。因此我们主要查询student表，然后外接result表即可
2. 交叉点：student表和result表都有学生id，因此这个id即为交叉点
*/
--inner join  只查询到id=12的学生
select s.id,name,result from 
student as s
inner join result_math as r
on s.id=r.id;

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

--right join 100分的学生也查出来了，因为使用的是s.id，故此时的id显示为null
select s.id,name,age,result from 
student as s right join result_math as r
on s.id=r.id;
-----------此时采用r.id，显示了id=14
select r.id,name,age,result from 
student as s right join result_math as r
on s.id=r.id;           

--right join 
select s.id,name,age,result from 
student as s left join result_math as r
on s.id=r.id;

--查询缺考的学生，即无成绩的人
select s.id,name,age,result from 
student as s left join result_math as r
on s.id=r.id
where result is NULL;

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

/* student表中有id,name，age，sex,
   result_math表中有id、result、dubject_id
   subject_tab表中有subject_id，subject_name
   现在要查询学生的id,name,result,subject_name
*/
--多表查询
/*分析：student和result_math中相同的是id，result_math和subject中相同的是subject_id
*/
select id,name,result,`subject` from 
student s inner join result_math r on s.id=r.id
inner join subject_tab sub on r.subject_id=sub.sybject_id;

自连接查询：自己和自己的表链接

核心：一张表拆为两张一样的表

新建一个数据库表如下，其中pid表示父类id，categoryID表示自己所属的id

categoryID	pid	categoryName
2	1	信息技术
3	1	软件开发
4	3	数据库
5	1	美术设计
6	3	web开发
7	5	ps技术
8	2	办公信息

拆开：

父类表：寻找pid为1的categoryName，表示一级目录

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

子类表：查找categoryID为2,3,4下的目录

pid	categoryID	categoryName
2	8	办公信息
3	4	数据库
3	6	web开发
5	7	ps技术

因此这两个表之间的关系是：子类表的pid是父类表的categoryID

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

--选出上面换的关系
select a.categoryName as '父表', b.categoryName as '子表'
from category as a, category as b
where a.categoryID=b.pid;

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

分页 limit、排序 order by

--排序 order by  升序 asc  降序  desc
select * from student where address='南京市' order by age desc;  --查询家庭住址为南京市的所有学生并按照年龄降序排序

--排序 limit (n-1)*pagesize,pagesize
/* 	 pagesize：当前页面大小，即当前页面显示的数据条数
  	 n：当前页，即第n页
	(n-1)*pagesize：起始值，即数据显示的起始值
	总页数：数据总数%页面大小==0 ? 数据总数/页面大小 : (数据总数/页面大小)+1
*/
select * from student   --查询年龄小于25的学生，并按照年龄大小升序排序，且显示从0开始的3条数据
where age<25
order by age asc
limit 0,3;

--查询 java第一学年 课程成绩排名 前5的学生，并且分数大学80分的学生信息（id,name,subject_name,result）
select student_id,student_name,subject_name,student_result  --查询学号，姓名，学科明，成绩
from student s   --student表中查询student_id,student_name
inner join result r on s.student_id=r.studet_id   --result中查询student_result
inner join subject sub on r.subject_id=sub.subject_id  --subject中查询subject_name
where subject_name='java第一学年' and student_result>=80  --课程是：java第一学年。分数是：80分以上
order by student_result desc   --前5名，所以采用降序排序
limit 0,5;   --选择前五名

子查询

子查询就是在一个查询语句中嵌套另一个子查询，子查询可以支持多层嵌套

--子查询  查询年龄大于学号为1的学生的信息
select id,name,age   --查询id,name,age信息
from student s   --从student表中
where age>(select age from student where id=1);  --年龄大于学号为1的学生的年龄

MYSQL中的函数

常用函数

--============  常用函数  ==============
-- 数字运算
select abs(-10);  --绝对值 10
select ceiling(9.5)  --向上取整 10
select floor(9.4)   --向下取整 9
select rand()       --返回一个0~1间的随机数
select sing(10)     --判断一个数的符号  0-0 负数返回-1，正数返回1 

--字符串
select char_length('hello');   --字符串长度 5
select concat('我爱','世界');  --拼接字符串  我爱世界
select insert('我爱世界',2,1,'超级爱');  --替换字符串  insert(str1,indext1,len,str2);在str1字符串中，将index1处开										始，往后的len个字符，替换成str2 我超级爱世界
select lower('Hello World');  --小写  hello world
select upper('Hello World');  --大写  HELLO WORLD
select instr('Hello World','l');  --返回第一次出现子串的索引  instr(str,substr) 在str中返回第一次出现substr的索引 3
select replace('Hello World','World','china');  --替换出现的字符  replace(str,str1.str2) 在str中，将str1出现的字符													串改为str2字符串  Hello china
select substr('Hello World',4,5);   --返回指定的子字符串  substr(str,index,len) 在str在，从index开始返回len个长度的										子串  lo Wo
select reverse('Hello World');  --反转  dlroW olleH
--查询所有姓 王 的同学并将其改为 朱 
select replace(name,'王','朱') from student where name 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();   --查询版本

聚合函数（常用）

函数名称	描述
count()	计数
sum()	求和
avg()	平均值
max()	最大值
min()	最小值
…	…

--===============  聚合函数  ==================
--count统计表中所有的数据
/*count(字段名)、count(*)、count(1)的区别
count(字段名)会忽略所有的null值
count(*) 不会忽略null值，本质是计算行数，但是使用*表示所有的列都会操作
count(1)不会忽略null值，本质是计算行数，但是只对一列进行操作
执行效率上：
1. 列名为主键，则count(主键)比count(1)块；反之列名不为主键，则count(1)比count(字段名)快
2. 多个列并且没有主键，则count(1)比count(*)快；反之，单个列，count(*)最快
3. 如果有主键，则count(主键)最快
*/
select count(name) from student;
select count(*) from student;
select count(1) from student;

select sum(res) as 总分 from result;
select avg(res) as 平均分 from result;
select max(res) as 最大分 from result;
select min(res) as 最小分 from result;

分组group by和过滤 having

--============  分组 group by /   过滤 having===========
--查询 不同课程 的平均分，最高分，最低分
	--核心：根据不同的课程分组
select subject_name，avg(student_result) as 平均分, max(student_result) as 最大分, min(student_result) as 最低分   --要查询的内容
from result as r    --从esult表中查询 student_result
inner join `subject` as sub   --从`subject`表中查询subject_name
on r.subject_id=sub.    --r和sub共有的是subject_id
group by r.subject_id;   --按照subject_id来分组    

--在加一个条件，平均分要大于80
select subject_name，avg(student_result) as 平均分, max(student_result) as 最大分, min(student_result) as 最低分   --要查询的内容
from result as r    --从esult表中查询 student_result
inner join `subject` as sub   --从`subject`表中查询subject_name
on r.subject_id=sub.    --r和sub共有的是subject_id
group by r.subject_id   --按照subject_id来分组
having 平均分>80;    --过滤80分以下的

4、数据库控制语言DCL

Date Control Language主要由commit、rollback、savepoint完成，在事务中讲到

5、MD5密码加密

--=============   MD5 加密测试==================
--新建表
create table if not exists testmd5(
		id int(10) not null comment '用户ID号',
		name varchar(225) not null comment '用户名字',
		pwd varchar(255) not null comment '用户密码'
)engine=innodb default charset=utf8;

--插入数据  明文密码
insert into  testmd5 values(1,'张三','123456'),(2,'李四','526389'),(3,'王五','156894');
--加密密码
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');

三、事务

1、概念

事务是指满足ACID特性的一组操作，可以通过Commit提交一个事务，也可以使用Rollback回滚一个事务

2、ACID特性

1. 原子性 A：事务里有多个操作，事务的操作要么全部成功Commit，要么全部失败Rollback。回滚可以用回滚日志来实现
2. 一致性 C：事务前后的数据完整性要保持一致。（开启一个事务以后，在这个事务中，多次读取同一个数据，结果不变）
3. 隔离性 I：并发访问时，数据库为每一个用户开启事务，每个用户事务之间互相隔离，操作数据互不干扰
4. 持久性 D：一旦事务提交，则其结果会永远保存在数据库中，即使系统发生崩溃，也不会丢失事务处理的结果。系统崩溃后，可用重做日志进行恢复。

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

参考博客：https://blog.csdn.net/dengjili/article/details/82468576

3、并发一致性问题

• 脏读：一个事务读取了另一个事务未提交的数据
• 不可重复读：在事务内读取表中的一行数据，多次读取的结果不同
• 虚读（幻读）：在一个事务内读取到了别的事务插入的数据，导致前后读取不一致
• 丢失修改：一个事务的更新操作被另外一个事务的更新操作替换

--==========  事务  ====================
--mysql默认开启事务
set autocommit=0; --关闭 默认提交
set autocommit=1; --开启 默认提交

------手动处理事务
set autocommit=0;   --关闭自动提交
--事务开启
start transaction;   --从这往后，所有的sql语句都在同一个事务中

--提交事务：持久化（成功！）
commit;

--回滚事务：回到原来的样子（失败！）
rollback;

--事务结束
set autocommit=1;  --开启自动提交

savepoint 保存点名;   --设置一个事务的保存点
rollback to savepoint 保存点名;   --回滚到保存点
release savepoint 保存点名;  --撤销一个保存点

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

--==========  模拟事务 ======================
--新建一个数据库
create database if not exists shop;
use shop;

--新建表  #decimal(a,b) 表示有9位数，小数点后有2位
create table if not exists account(
		id int(10) not null auto_increment,
		`name` varchar(50) not null,
		money decimal(9,2) not null,    
		primary key(id)
)engine=innodb default charset=utf8;

--插入数据
insert into account(`name`,money) values ('A',1000.00),('B',2000.00);

--模拟转账：事务
set autocommit=0;  --关闭自动提交
start transaction;  --开启事务

update account set money=money-500 where `name`='A';   --A转出500元  
update account set money=money+500 where `name`='B';   --B转进500元

commit;  --提交事务
rollback;  --回滚事务
set autocommit=1;  --开启自动提交
/*上述在执行update的过程中，如果不到commit，数据库里A、B的money值都不会改变，只有commit以后才会改变*/

参考《事务》一文

四、索引

1、索引的分类

• 主键索引（primary key）：唯一的标识，主键不可重复，只能有一个列作为主键
• 唯一索引（unique key）：保证指定列不出现重复值，但可以出现多个null值
• 常规索引（key/index）：默认的
• 全文索引（FullText）：在特定的数据库引擎下才有，MyISAM中不支持

/*
创建索引的方式
	1.create table的时候写就加上索引
	2.alter table 处加上索引
	3.create index 加上索引
*/
--alter方式：alter table 表名 add 索引类 索引名字 （字段名）
alter table student add unique Uni_name (`name`);  --Uni_name是键的名字，()里是列名
--create方式：create index 索引名 on 表名（字段名）
create index id_student_age on student(age);  --索引名一般的命名规则：id_表名_

--索引删除 drop index 索引名 on 表名
drop index id_student_age on student

2、测试索引：降低查询速度

--显示所有的索引信息
show index from student;
--使用explain 分析sql执行的情况
explain select * from student;

--=============== 测试索引 =============================
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(50) default '' comment '手机号',
		gender tinyint(4) unsigned default '0' comment '性别（0：男，1：女）',
		`password` varchar(100) not null comment '密码',
		age tinyint(4) default '0' comment '年龄',
    primary key(id)
)engine=innodb default charset=utf8mb4 comment='app用户表';

--插入100万条数据  使用循环实现
DELIMITER $$
create function mock_date()
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),'123456@qq.com',concat('187',FLOOR(RAND()*10000000)+10000000),
		FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
		set i=i+1;
	end while;
	RETURN i;
end;
--执行插入语句
select mock_date();

--查询数据，未加索引
select * from app_user where `name`='用户99999';  --用时0.512s
select * from app_user where `name`='用户9999';   --用时0.503s

explain select * from app_user where `name`='用户9999';   --共查询了993797条数据

--添加索引后 
create index id_app_user_name on app_user(`name`);

select * from app_user where `name`='用户99999';   --用时0.001s
select * from app_user where `name`='用户9999';   --用时0.001s
  
explain select * from app_user where `name`='用户9999';  --共查询了1条数据

从上面的例子可以看出：索引在小数据量时，用处不明显，但是在大数据的时候，区分十分明显

3、索引原则

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

4、索引的数据结构

索引的数据结构：参考博客http://blog.codinglabs.org/articles/theory-of-mysql-index.html

B+tree：InnoDB默认的数据结构、MongoDB采用的是BTree结构

MyISAM

MyISAM叶节点的data域存放的是数据记录的地址。无论是主键索引还是其他类型的索引，其data域存放的都是指向该行数据记录的物理地址，并不是真正的数据文件。假设以Col1为主键，则MyISAM索引文件如下图所示，（Mysql中逻辑上相邻的记录其物理地址未必相连）

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

假设不存在主键索引，只在Col2上建立一个辅助索引，那么此时MyISAM的索引结构如下：

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

从上面两个图可以看出，主键索引和辅助索引没有什么不同，其叶节点的data中存放的都是数据地址，唯一的不同是主键索引要key不同，辅助索引的key可以重复。

InnoDB

（1）InnoDB采用主键索引时，主键索引的叶节点的date域中保存的是完整的数据记录，以Col1为主键，则InnoDB主索引示意图如下

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

可以看出此时date域中存放的不在是地址指针了，而是真实的数据。

针对上图每一个叶节点，可以看到Col1、Col2、Col3的数据都有，说明叶节点包含了完整的数据，这种索引叫做***聚集索引***。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

（2）当InnoDB中采用辅助索引时，辅助索引的data域存储的是相应主键的值，不在是整个数据记录了。假设在Col3上定义一个辅助索引，则结构如下图所示：

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

此时数据查询的过程要经过两个步骤：a、先检索辅助索引得到主键值；b、然后利用主键值到主索引中检索获得记录。

举个栗子

例如在上表中查询（30,91，Eric）这条记录：

（1）如果是MyISAM，无论采用那一列作为主键索引或辅助索引，都只会在data域获得OFX3这个地址值，然后通过这个地址值找到（30,91，Eric）

（2）如果是InnoDB，采用主键索引Col1，则查询到Col1=30后，直接从其data域中得到Col2=91,Clo3=Eric

​ 如果采用辅助索引，如Col3，则先找到Col3=Eric后，从其data域中得到Col1=30，再重复上面的步骤

注意：多列索引的存在有时也能得到和主键相同的效果

比如，现在只要查找（91，Eric），那么我们可以将Col2和Col3设置成同一索引，此时按照辅助索引规则查询，就可得到最终的结果，不需要取出其data域中的Col1=30再进行查询了，因为我们要查的数据只有Col2和Clo3

这里又会产生一个问题：多列索引查找规则。

多列索引查找规则

需要满足最左前缀原则，比如(a,b,c)是联合索引，那么在查找的过程中，首先按照a查找，找到所有满足条件的数据，然后按照b查找，找到所有既满足a又满足b的数据，最后按照c查找，找到既满足ab又满足c的数据。

联合索引的生效原则是从前往后依次生效，如果中间某个索引没有使用，那么断点前面的索引起作用，断点后面的索引不起作用。比如对于(a,b,c)联合索引来说，其查找的过程中必须保证a的存在，只有(a)、(a,b)、(a,b,c)会真正的起到联合索引的作用。

举个栗子：

create table if not exists test(
		id int(10) not null auto_increment,
		a int(10) not null,
		b int(10) not null,
		c int(10) not null,
		primary  key(id)   --主键是id
)engine=InnoDB default charset=utf8;
--随机生成1000条数据
delimiter $$
create function mock_date()
returns int 
begin 
	declare i int default 0;
	declare num int default 1000;
	while i<num do
		insert into test values (null,rand()*10,rand()*100,rand()*100);
		set i=i+1;
		end while;
		return i;
end;
--插入数据
select mock_date();

--指定(a,b,c)是联合索引
create index id_test on test(a,b,c); 

--==============  测试联合索引 （等值查询）==========================
select * from test where a=5 and b=5 and c=81;   --都起作用（这里a,b,c换位置不影响结果，Mysql会自动按照a,b,c的顺序查找）此处只查到了一条数据，说明这个表里a=5 b=5 c=81的记录只有1条
explain select * from test where a=7 and b=5 and c=10;  --查询了1条数据

select * from test where a=5 and b=5;  --只有a,b起作用，查到了3条记录，说明这哥表里s=5 b=5的数据有3条
explain select * from test where a=10 and b=5;   --查询了3条语句

select * from test where a=5 and c=81;   --只有a起作用，因为b是断点
explain select * from test where a=5 and c=81;   --查询了102条数据

select * from test where b=5 and c=81;  --b,c均不起作用，因为a是断点
explain select * from test where b=5 and c=81;  --查询了1000条数据

--================  测试联合索引 （范围查询）  ========================
select * from test where a=1 and b>10 and c=12;  --a有用，b是用到了索引，但是因为b是范围值，相当于是断点，因此c没有
explain select * from test where a=1 and b>10 and c=12;  --81条

select * from test where a>5 and b=10 and c=64;  --a用到了索引，但是a相当于断点，所以b,c无用
explain select * from test where a>5 and b=10 and c=64;  --458

select * from test where a=1 order by b;  --a有用，b用到了索引
explain select * from test where a>5 and b=10 and c=64;  --458

/*以上注意：“有用”和“用到了索引”是不同的概念。只有等值查询的时候索引才“有用”，其余的查询只是“用到了索引”而已，还是相当于是断点*/

五、其他

1、数据库备份

• 使用navicate可视化工具导出
• 使用dos命令行导出

-- mysqldump -h 主机 -u root -p 密码 数据表 表名 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -padmin school student > D:/a.sql   --一次导出一张表
mysqldump -hlocalhost -uroot -padmin school student result > D:/b.sql  --一次导出多个表
mysqldump -hlocalhost -uroot -padmin school > D:/.sql --导出整个数据库

--导入
 --登录情况下
 source d:/a.sql
 
 --没登录情况下
 mysql -u root -padmin student d:/a.sql

2、规范数据库设计

三大范式：

第一范式：原子性，保证每一列不可再分

第二范式：前提是满足第一范式，然后每张表只描述一件事情

第三范式：前提是满足第一、第二范式，需要确保数据表中的每一列数据都和主键直接相关，而不是间接相关

参考博客：https://www.cnblogs.com/wsg25/p/9615100.html

3、Mysql日志

日志类型	日志内容
错误日志(Erroe Log)	mysql 启动、停止和运行过程中出现的异常
普通查询日志(Generl Query Log)	记录客户端连接数据库后所有执行的语句
二进制日志(Binary Log)	当数据库发生改变时，产生该日志
中继日志(Relay Log)	从库收到主库的数据更新时产生该日志
慢查询日志(Slow query log)	SQL语句执行超过指定时间时产生该日志
DDL日志（元日志）	执行DDL语句操作元数据时产生该日志

中继日志：为了复制数据库而存在。

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

六、JDBC

1、JDBC：JAVA连接数据库

package mysql_test;

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

public class JdbcFirstDemo {
	public static void main(String[] args) throws SQLException, ClassNotFoundException {
		//1.加载驱动
		Class.forName("com.mysql.cj.jdbc.Driver");  //固定写法，注意新版本的jdbc不能写成com.mysql.jdbc.Driver
		
		//2.用户信息和url
        /*	school：表示使用的数据库名字
        	useUnicode=true  表示使用的字符集是Unicode，因此可使用中文
        	characterEncoding=utf8  表示编码方式为utf8
        	useSSL=true  表示使用安全连接方式
        	serverTimezone=UTC  设置全球标准时间，在jdbc 6.0版本以上必须设置，否则会报错
        */
		String url="jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC";
		String username="root";
		String password="admin";
		
		//3.链接成功，获取数据库。  此处的connection即表示一个数据库
		Connection connection=DriverManager.getConnection(url,username,password);
		
		//4.创建 执行sql的对象
		Statement statement=connection.createStatement();
		
		//5.执行sql语句，如果有结果，则查看返回的结果
		String sql="SELECT * FROM student where id<3";
		
		ResultSet resultset=statement.executeQuery(sql);  //返回结果集，结果集中封装了我们全部查询出来的结果
		while(resultset.next()) {
			System.out.println("id="+resultset.getObject("id"));
			System.out.println("age="+resultset.getObject("age"));
			System.out.println("address="+resultset.getObject("address"));
			System.out.println("name="+resultset.getObject("name"));
			System.out.println("======================");
		}
		
		//6.关闭连接
		resultset.close();
		statement.close();
		connection.close();
	}
}

步骤总结：

1. 加载驱动
2. 连接数据库 DriverManager
3. 获取执行sql的对象 Statement
4. 执行sql语句，获得返回的结果集
5. 释放连接

注意：需要导入mysql-connector-java的jar包。参考《eclipse导入外部jar包的步骤》

代码解释

/*
	DriverManager的作用
	1.加载驱动
	2.连接数据库
*/
//DriverManager.registerDiver(new com.mysql.cj.jdbc.Driver())
Class.forName("com.mysql.cj.jdbc.Driver");

//连接数据库
Connection connection=DriverManager.getConnection(url,username,password);
//connection表示数据库，因此可以执行事务的一些操作
connection.rollback();  //事务回滚
connection.commit();  //事务提交
connection.setAutoCommit(true 或 false);   //设置事务的自动提交方式

/*
	Statement 执行sql的对象  PrepareStatement也是执行sql的对象
*/
//创建执行sql的对象
Statement statement=connection.createStatement();
ResultSet resultset=statement.executeQuery();  //执行查询操作，结果返回ResultSet
statement.execute();   //执行任何sql
statement.executeUpdate();   //更新、插入、删除操作，都用这个语句，返回一个受影响的行数

/*
	ResultSet 查询结果集：封装所有的查询结果
*/
resultset.getObject();  //在不知道列类型下使用
//在已知列类型下使用
resultset.getInt();   
resultset.getFloat();
resultset.getString();
.....
//指针，遍历的作用
resultset.beforeFirst();  //移动到最前面
resultset.afterLast();  //移动到最后
resultset.next();   //移动到下一个数据
resultset.previous();    //移动到前一行
resultset.absolute(row); //移动到指定行

2、简化上述连接代码

步骤1：在当前项目目录下新建一个mysql.ini文件，保存信息数据库的driver、url、user、password信息

！！！注意：如果需要更改数据库，则需要在url中将school改为要使用的数据库名

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

步骤2：封装一个类 JdbcUtils 来驱动数据库

package mysql_test;

import java.sql.Statement;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
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 {
			Properties properties=new Properties();
			properties.load(new FileInputStream("mysql.ini")); //这里因为mysql.ini在项目目录文件下，如果在其他处，需要输入完整的路径名
			
			driver=properties.getProperty("driver");
			url=properties.getProperty("url");
			username=properties.getProperty("username");
			password=properties.getProperty("password");
	
			//加载驱动
			Class.forName(driver);   //需要捕获ClassNotFoundException异常，因此下面直接用一个大异常类Exception
		} catch (Exception e) {
			e.printStackTrace();
		} 
	}
	
	//获取数据库连接对象
	public static Connection getConnection() throws SQLException {
		return DriverManager.getConnection(url,username,password);
	}
	
	//释放连接的资源
	public static void release(Connection connection,Statement statement,ResultSet resultSet) {
		if(connection!=null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(statement!=null) {
			try {
				statement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(resultSet!=null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}

步骤3：新建一个类，使用封装好的 JdbcUtils 类来驱动数据库，然后在这个类中执行sql语句

package mysql_test;

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

public class Demo1 {
	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 test(id,`name`,age) values (1,'张三',25)"; //插入
			//String sql="update test set `name`='李四'";   修改
            //String sql="delete from test where id=1";   删除
            
			int i=statement.executeUpdate(sql);  //insert语句属于executeUpdate下的操作，会返回数据库被影响的行数
			if(i>0) {
				System.out.println("插入成功");
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JdbcUtils.release(connection, statement, resultSet);
		}
	}
}

如果需要执行其他的sql语句，只需要在Demo1类中，修改String sql语句以及executeUpdate即可，这样大大减少了每一次创建的过程。

• 增删改（insert、update、delete）都是使用executeUpdate()
• 查询（select）使用executeQuery()

3、SQL注入

SQL注入会导致数据库不安全，容易被攻击

//SQL注入问题
package mysql_test;

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

public class Demo2 {
	
	//登录业务：只有当输入的name和password正确才能登录成功
	public static void login(String name,String password) {
		Connection conn=null;
		Statement stmt=null;
		ResultSet set=null;
		
		try {
			conn=JdbcUtils.getConnection();
			stmt=conn.createStatement();
			
			String sql="select * from test where `name`='"+name+"' and `password`='"+password+"'";
			
			set=stmt.executeQuery(sql);
			
			while(set.next()) {
				System.out.println("name="+set.getString("name"));
				System.out.println("password="+set.getString("password"));
				System.out.println("=========================");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JdbcUtils.release(conn, stmt, set);
		}
	}
	public static void main(String[] args) {
		//正常登录
		//login("张三","123456");   //这里只有输入test表中已经有的姓名和密码，才能正确执行
		
		//SQL注入
		login(" 'or '1=1"," 'or'1=1");
	}
}
//正常输入时的输出结果：name=张三，password=123456
/*SQL注入时输出结果：可以看出将整个数据库的用户和密码数据都盗取了
name=张三
password=123456
=========================
name=李四
password=123321
=========================
name=王五
password=654321
=========================
*/

解决SQL注入

使用PreparedStatement对象，而且效率会更好

//PreparedStatement的使用方法
package mysql_test;

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

public class PrepareStatementTest {
	public static void main(String[] args) {
        
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet set=null;
		
		try {
			conn=JdbcUtils.getConnection();
			
			//PreparedStatement与Statement的区别之处
			//1.先写sql语句，使用？占位符代替参数
			String sql="select * from test where id=? or `name`=?"; 
			
			//2.预编译sql语句，不执行，预编译结果传给PreparedStatement对象
			pstmt=conn.prepareStatement(sql);
			
			//3.手动给参数赋值
			pstmt.setInt(1, 2);  //第一个？赋值为2
			pstmt.setString(2, "张三");    //第二个？赋值为"张三"
			
			//4.执行
			set=pstmt.executeQuery();
			
			while(set.next()) {
				System.out.println("id="+set.getInt("id"));
				System.out.println("name="+set.getString("name"));
				System.out.println("==================");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JdbcUtils.release(conn, pstmt, set);
		}
	}
}

PreparedStatement防止SQL注入的本质是：将传递进来的参数当做字符，如果其中存在转义字符，比如说’’，会直接被转义忽略，因此判断的就是最终的字符是否符合要求。

//PreparedStatement防止SQL注入
package mysql_test;

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

public class Demo3 {
	
	public static void login(String name,String password) {
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet set=null;
		
		try {
			conn=JdbcUtils.getConnection();
			String sql="select * from test where `name`=? and `password`=?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, name);
			pstmt.setString(2, password);
			set=pstmt.executeQuery();
			while(set.next()) {
				System.out.println("name="+set.getString("name"));
				System.out.println("password="+set.getString("password"));
				System.out.println("=========================");
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JdbcUtils.release(conn, pstmt, set);
		}
	}
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		//login("张三","123456");
		//SQL注入
		login("'' or 1=1","123456");
	}
}
/*
	正常登录，输出：name=张三，password=123456
	非正常登录，输出：无结果，但也不报错。因此很好的防止了SQL注入问题
*/

4、JDBC操作事务

package mysql_test;

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

public class Demo4 {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet set=null;
		
		try {
			conn=JdbcUtils.getConnection();
			//关闭数据库自动提交，自动开启事务
			conn.setAutoCommit(false);
			
            //A账户减少200元
			String sql1="update account set money=money-200 where name='A'";
			pstmt=conn.prepareStatement(sql1);
			pstmt.executeUpdate();
			
            //B账户增加200元
			String sql2="update account set money=money+200 where name='B'";
			pstmt=conn.prepareStatement(sql2);
			pstmt.executeUpdate();
			
			//业务完毕，提交事务
			conn.commit();
			System.out.println("成功！");
			
		} catch (SQLException e) {
			try {
				conn.rollback();     //如果失败，则回滚事务。实际上可以不写，因为JDBC默认会回滚事务
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}finally {
			JdbcUtils.release(conn, pstmt, set);
		}
	}
}

步骤总结：

1. 开启事务：conn.setAutoCommit(false);
2. 一组业务执行完毕，提交事务：conn.commit();
3. 可以在catch语句中显式定义回滚语句，但默认识别就会回滚

5、数据库连接池

作用

由于每次都要连接getConnection()才能得到connection，然后又释放connection，这个过程十分耗费资源，因此创建一个数据库连接池，里面存放一些已经连接好的connection，使得需要用的时候直接使用，不需要getConnection()，而用完以后，就放回池子里，不close掉。类似于线程池

参数

• 最小连接数
• 最大连接数
• 等待超时：ms（当业务量大于最大连接数时，会等待）

实现方式

• 编写连接池，实现一个接口DataSource
• 使用开源数据源，如DBCP、C3P0。使用这些开源数据库连接池后，我们在项目中开发中就不需要编写连接数据库的代码了

数据源DBCP

​ 1.导入commons dbcp和commons pool 两个jar包。如果导入这两个包以后，运行程序出现java.lang.NoClassDefFoundError错误，那么需要再导入一个commons logging的jar包。

​ 2.编写dbcp的属性文件，在当前项目下新建一个dbcpconfig.properties文件，输入配置语句，网上找到的配置文件如下图所示，但是有一些需要修改的地方。

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

最终配置文件如下，school是我要用的数据库名字，我的数据库密码为admin

#连接设置
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC
username=root
password=admin

#<!-- 初始化连接 -->
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 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

​ 3.创建DBCP工具类

package mysql_test;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;

public class DbcpUtils {
	private static DataSource dataSource;
	static {
		try {
			Properties properties=new Properties();
			properties.load(new FileInputStream("dbcpconfig.properties"));  
			
			//创建数据源
			dataSource=BasicDataSourceFactory.createDataSource(properties);  
		} catch (Exception e) {
			e.printStackTrace();
		} 
	}
	//获取连接对象
	public static Connection getConnection() throws SQLException {
		return dataSource.getConnection();    //从数据源中获取连接
	}
	
	//释放连接的资源
	public static void release(Connection connection,Statement statement,ResultSet resultSet) {
		if(connection!=null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(statement!=null) {
			try {
				statement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(resultSet!=null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}

​ 4.使用DBCP工具类

package mysql_test;

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

public class DbcpTest {
	public static void main(String[] args) {
		Connection connection=null;
		PreparedStatement pstmt=null;
		ResultSet set=null;
        
		try {
			connection=DbcpUtils.getConnection();
			
			String sql="insert into student (age,address,`name`) values (?,?,?)";
			pstmt=connection.prepareStatement(sql);
			
			pstmt.setInt(1, 19);
			pstmt.setString(2, "武汉市");
			pstmt.setString(3, "杨树");
			
			int i=pstmt.executeUpdate();
			if(i>0) {
				System.out.println("插入成功");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DbcpUtils.release(connection, pstmt, set);
		}
	}
}

数据源C3P0

1.导入c3p0-0.9.5.2.jar和mechange-comments-java-0.2.11.jar两个包

2.配置c3p0的xml文件。在当前项目下新建一个文件，名字为c3p0config.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/school?serverTimezone=UTC&amp;useUnicode=true&amp;useUnicode=true</property>
		<property name="user">root</property>
		<property name="password">admin</property>
		
		<property name="initialPoolSize">10</property>
		<property name="maxPoolSize">100</property>
		<property name="minPoolSize">60</property>
		<property name="maxStatements">200</property>
	</default-config>
    
     <!--C3P0默认配置，如果在代码中"ComboPooledDataSource ds=new ComboPooledDataSource("MySQL");"这样写表示使用的是C3P0中名字为MySQL的配置-->
	<name-config name="MySQL">
		<property name="diverClass">com.mysql.cj.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql://localhost:3306/school?serverTimezone=UTC&amp;useUnicode=true</property>
		<property name="user">root</property>
		<property name="password">admin</property>
		
		<property name="maxPoolSize">20</property>
		<property name="minPoolSize">5</property>
		<property name="initiaPoolSize">10</property>	
	</name-config>>
</c3p0-config>

在xml文件中，可以写多个c3p0的配置，只要名字不同即可，这样可以使得jdbc连接多个数据库，比如有org的数据库就可以新建一个name，里面写上org数据库的链接方式即可。

在url中需要更改使用的数据库名，同时采用&amp来表示之前写的&；在password中该为自己的密码。

3.创建工具类

package mysql_test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0Utils {
	private static ComboPooledDataSource source=null;
	static {
		try {
			//创建数据源，选择使用默认的配置方式
			//方式1，如果不使用上面的xml文件的话，需要手动配置
			source = new ComboPooledDataSource();
			source.setDriverClass("com.mysql.cj.jdbc.Driver");
			source.setJdbcUrl("jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC");
			source.setUser("root");
			source.setPassword("admin");
			source.setMaxPoolSize(100);
			source.setMinPoolSize(10);
			
			//方式2、自动配置，使用了上面的xml文件
            /*
			  	由于xml文件会自动读取，因此Properties属性文件的读取都可以省略
				Properties properties=new Properties();
				properties.load(new FileInputStream("c3p0config.properties"));
			*/
			//source = new ComboPooledDataSource();   使用xml中的默认配置
            //source = new ComboPooledDataSource("MySQL");    使用xml中名字为MySQL的配置
		} catch (Exception e) {	
			e.printStackTrace();
		}
	}
	//获取连接
	public static Connection getConnection() throws SQLException {
		return source.getConnection();
	}
	//释放资源
	public static void release(Connection connection,Statement statement,ResultSet resultSet) {
		if(connection!=null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(statement!=null) {
			try {
				statement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(resultSet!=null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}

4.使用工具类

package mysql_test;

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

public class C3P0Test {
	public static void main(String[] args) {
		Connection connection=null;
		PreparedStatement pstmt=null;
		ResultSet set=null;
		
		try {
			connection=C3P0Utils.getConnection();
			
			String sql="insert into student (age,address,`name`) values (?,?,?)";
			
			pstmt=connection.prepareStatement(sql);
			
			pstmt.setInt(1, 29);
			pstmt.setString(2, "扬州市");
			pstmt.setString(3, "催芽");
			
			int i=pstmt.executeUpdate();
			if(i>0) {
				System.out.println("插入成功");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			C3P0Utils.release(connection, pstmt, set);
		}
	}
}

DBCP与C3P0的区别

DBCP无法自动回收空闲连接，c3p0有自动回收空闲连接的功能

对数据连接的处理方式不同：dbcp提供最大连接数，c3p0提供最大空闲时间。前者当连接数超过最大连接时，所有连接都会断开；后者当连接超过最大空闲连接时间时，当前连接就会断开

	DBCP	C3P0
数据连接的处理方式	提供最大连接数	提供最大空闲时间
什么时候连接断开	连接数超过最大连接数	超过最大空闲连接时间
资源是否释放	手动释放资源	自动回收连接
效率	比较高

}

##### DBCP与C3P0的区别

DBCP无法自动回收空闲连接，c3p0有自动回收空闲连接的功能

对数据连接的处理方式不同：dbcp提供最大连接数，c3p0提供最大空闲时间。前者当连接数超过最大连接时，所有连接都会断开；后者当连接超过最大空闲连接时间时，当前连接就会断开

|                    | DBCP                 | C3P0                 |
| ------------------ | -------------------- | -------------------- |
| 数据连接的处理方式 | 提供最大连接数       | 提供最大空闲时间     |
| 什么时候连接断开   | 连接数超过最大连接数 | 超过最大空闲连接时间 |
| 资源是否释放       | 手动释放资源         | 自动回收连接         |
| 效率               | 比较高               |                      |