了解数据库

一，概述

数据库DB：(DataBase)
按一定格式存储数据的一些文件的组合存储数据的仓库，实际上就是一堆文件，这些文件存储了具有特定格式的数据
DBMS：(DataBase Management System)
数据库管理系统，常见的有：MySQL、Oracle、DB2、Sybase、SqlSever…
是专门用来管理数据库中数据的，数据库管理系统可以对数据库当中的数据进行增删改查
SQL：结构化查询语言，是一门标准通用的语言。标准的SQL语言适合于所有的数据库产品。
SQL属于高级语言。只要能看懂英语单词的，写出来的SQL语句，可以读懂什么意思。
SQL语句在执行的时候，实际上内部也会先进行编译，然后再执行SQL。（SQL语句的编译由DBMS完成）

编写sql语句，DBMS负责执行SQL语句，通过执行SQL语句来操作DB当中的数据，来进行数据的增删改查。
DBMS -(执行)-> SQL -(操作)-> DB

常用操作：
用户名：root
查看mysql中有哪些数据库：show databases;
创建数据库：create database bjpowernode;
退出：exit;
使用：use;
查看某个数据库下由哪些表： show tables;
查看当前使用的是哪个数据库：select databases();
不见  ;    不执行
终止一条命令的输入   \c

二，表

数据库当中是以表格的形式表示数据的: table
行（row）：被称为数据/记录（data）
列(column)：被称为字段(每一个字段都有数据类型)

三，SQL语句分类

DQL（数据查询语言）: 查询语句，凡是select语句都是DQL。
DML（数据操作语言）：insert delete update，对表当中的数据进行增删改。
DDL（数据定义语言）：create drop alter(修改)
修改表的结构，而不是表中的数据。
TCL（事务控制语言）：commit提交事务，rollback回滚事务。
DCL（数据控制语言）: grant授权、revoke撤销权限等。

导入数据：
source D:\course\05-MySQL\resources\bjpowernode.sql

bjpowernode.sql，这个文件以sql结尾，这样的文件被称为“sql脚本”。什么是sql脚本呢？
当一个文件的扩展名是.sql，并且该文件中编写了大量的sql语句，
我们称这样的文件为sql脚本。
注意：直接使用source命令可以执行sql脚本。
sql脚本中的数据量太大的时候，无法打开，请使用source命令完成初始化。

查看表中数据：
select * from 表名

不看表中数据，只看表的结构：
desc 表名； (describe)

四，DQL

1,简单查询(查询一个字段)
select
字段名
from
表名;
2，查询两个/多个字段
使用逗号隔开
3，查询所有字段* 效率低

给查询结果的列重命名
只是将显示的查询结果显示为别名，  原表列名不变
在所有数据库中，单引号是标准

五，条件查询

语法格式：
select
字段,字段…
from
表名
where
条件;

执行顺序：先from，然后where，最后select

1，between and在使用的时候必须左小右大。
2，在数据库当中NULL不是一个值，代表什么也没有，为空。
空不是一个值，不能用等号衡量。
必须使用 is null或者is not null
3，or或者(and语句优先级比or高)
注意：当运算符的优先级不确定的时候加小括号。
4，in相当于多个or
in后面的值不是区间，是具体的值。
5，模糊查询like ?
%代表任意多个字符，_代表任意1个字符。

六，排序

1，按照工资升序，找出员工名和薪资？
select
ename,sal
from
emp
order by
sal;
2，默认是升序。怎么指定升序或者降序呢？asc表示升序，desc表示降序。
select ename , sal from emp order by sal asc; // 升序
select ename , sal from emp order by sal desc; // 降序
按照工资的降序排列，当工资相同的时候再按照名字的升序排列。
select ename,sal from emp order by sal desc , ename asc;
3，关键字顺序不能变
select
字段 3
from
表名 1
where
条件 2
order by
… 4

七，数据处理函数/单行处理函数(一个输入对应一个输出，一行一行处理)

1，lower() 转换小写
2，upper() 转换大写
3，substr(被截取的字符串 起始下标 截取的长度) 取子串

4，concat() 进行字符串的拼接
5，length() 取长度
6，trim() 去前后空白
7，str_to_data() 将字符串转换成日期
8，date_format() 将日期转换为具有特定格式的字符串
9，format() 对数字进行格式化
10，round() 四舍五入
=ROUND(3.19, 1) 将 3.19 四舍五入到一个小数位 (3.2)
11，rand() 生成随机数
12，ifnull() 空处理函数

ifnull(可能为NULL的数据,被当做什么处理)
如果数据为空时，把这个数据当作哪个值

八，分组函数/多行处理函数(多个输入对应一个输出)

count 计数
sum 求和
avg 平均值
max 最大值
min 最小值
分组函数自动忽略NULL
所有的分组函数都是对“某一组”数据进行操作的。
1，分组函数在使用的时候必须先进行分组，然后才能用
SQL语句当中有一个语法规则，分组函数不可直接使用在where子句当中。
因为分组函数在使用的时候必须先分组之后才能使用，where执行的时候，还没有分组，所以where后面不能出现分组函数。
2，count()和count(具体的某个字段)，他们有什么区别？
count():不是统计某个字段中数据的个数，而是统计总记录条数。（和某个字段无关）
count(comm): 表示统计comm字段中不为NULL的数据总数量。
3，分组函数也能组合起来用：
select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;
4，步骤
从某张表中查询数据
先经过where条件，筛选出有价值的数据
对这些有价值的数据进行分组
分组之后可以使用having继续筛选
select查询出来
排序输出
5，去重
select distinct job from emp; // distinct关键字去除重复记录。

九，分组查询(先进行分组，然后对每一组的数据进行操作)

group by ： 按照某个字段或者某些字段进行分组。
having : having是对分组之后的数据进行再次过滤，必须和group by联合使用
分组函数一般都会和group by联合使用，这也是为什么它被称为分组函数的原因。
并且任何一个分组函数（count sum avg max min）都是在group by语句执行结束之后才会执行的。
当一条sql语句没有group by的话，整张表的数据会自成一组。
规则：当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段。
完整的DQL语句：
select 5
…
from 1
…
where 2
…
group by 3
…
having 4
…
order by 6
…

十，连接查询(跨表查询，多张表联合起来查询数据)

单表查询：从一张表中单独查询
在实际开发中，大部分的情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终的结果。
在实际开发中，一般一个业务都会对应多张表，比如：学生和班级，起码两张表。

1，连接查询的分类

根据表的连接方式来划分，包括：
内连接：（AB连接，AB两张表无主次关系）
等值连接
非等值连接
自连接
外连接：（在外连接当中，两张表连接，产生了主次关系）
左外连接（左连接）
右外连接（右连接）
全连接（a,b都主表）
笛卡尔积现象：
当两张表进行连接查询的时候，没有任何条件进行限制，
最终的查询结果条数是两张表记录条数的乘积。
怎么避免笛卡尔积现象？当然是加条件进行过滤

2，内连接之等值连接 特点：条件是等量关系。

3，内连接之非等值连接 特点：连接条件中的关系是非等量关系。自连接 特点：一张表看做两张表。自己连接自己。

4，外连接

什么是外连接，和内连接有什么区别？
内连接：
假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。
AB两张表没有主副之分，两张表是平等的。
外连接：
假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。
外连接的分类？
左外连接（左连接）：表示左边的这张表是主表。 left join
右外连接（右连接）：表示右边的这张表是主表。 right join
外连接最重要的特点是：主表的数据无条件的全部查询出来。

5，三张表连接查询

格式：
select

from
	a
join
	b       on        a和b的连接条件
join
	c       on         a和c的连接条件；

案例：找出每一个员工的部门名称、工资等级、以及上级领导。
select
e.ename ‘员工’,d.dname,s.grade,e1.ename ‘领导’
from
emp e
join
dept d
on
e.deptno = d.deptno
join
salgrade s
on
e.sal between s.losal and s.hisal
left join
emp e1
on
e.mgr = e1.empno;

十一，子查询

1，什么是子查询？子查询都可以出现在哪里？
select语句当中嵌套select语句，被嵌套的select语句是子查询。
子查询可以出现在哪里？
select
…(select).
from
…(select).
where
…(select).
2，where子句中使用子查询

案例：找出比最低工资高的员工姓名和工资
(1)查询最低工资
select min(sal) from emp;
(2)找出大于最低工资800的
select * from emp where sal > 800;
(3)合并一二步
select * from emp where sal > (select min(sal) from emp);

3，from后面嵌套子查询，from子句中的子查询，可以将子查询的查询结果当作一张临时表

案例：找出每个部门平均薪水的等级
第一步：找出每个部门平均薪水（按照部门编号分组，求sal的平均值）
select deptno,avg(sal) as avgsal from emp group by deptno;
第二步：将以上的查询结果当做临时表t，让t表和salgrade s表连接，条件是：t.avgsal between s.losal and s.hisal
select 
	t.*,s.grade
from
	(select deptno,avg(sal) as avgsal from emp group by deptno) t
join
	salgrade s
on
	t.avgsal between s.losal and s.hisal;

4，在select后面嵌套子查询。
案例：找出每个员工所在的部门名称，要求显示员工名和部门名

select 
	e.ename,d.dname
from
	emp e
join
	dept d
on
	e.deptno = d.deptno;
在select后面嵌套子查询：
select 
	e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname 
from 
	emp e;

十二，union合并查询结果集以及limit 是将查询结果集的一部分取出来，通常使用在分页查询当中

union合并查询结果集

案例：找出工作岗位是SALESMAN和MANAGER的员工？
第一种：select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
第二种：select ename,job from emp where job in('MANAGER','SALESMAN');
第三种：union
select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMAN';

两张不相干的表中的数据拼接在一起显示？
select ename from emp
union
select dname from dept;

limit 是将查询结果集的一部分取出来，通常使用在分页查询当中(百度一页显示10条记录)

查询不会对数据库中的数据进行修改，只是一种显示数据
语法：
limit startIndex, length
startIndex表示起始位置，从0开始，0表示第一条数据。
length表示取几个

案例：取出工资前5名的员工（思路：降序取前5个）
	select ename,sal from emp order by sal desc;
	取前5个：
		select ename,sal from emp order by sal desc limit 0, 5;

limit是sql语句最后执行的一个环节：
select 5
…
from 1
…
where 2
…
group by 3
…
having 4
…
order by 6
…
limit 7
…;

通用的标准分页sql

每页显示3条记录：
第1页：0, 3
第2页：3, 3
第3页：6, 3
第4页：9, 3
第5页：12, 3

每页显示pageSize条记录：
第pageNo页：(pageNo - 1) * pageSize, pageSize

pageSize是什么？是每页显示多少条记录
pageNo是什么？显示第几页

java代码{
	int pageNo = 2; // 页码是2
	int pageSize = 10; // 每页显示10条
	
	limit (pageNo - 1) * pageSize, pageSize
}

十三，表

数据库命名规范：所有的标识符全部小写，单词和单词之间使用下划线进行连接

1，创建表(DDL)

1，建表语句的语法格式：
create table 表名(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型,
…
);
2，MySQL当中字段的数据类型
int 整数型(java中的int)
bigint 长整型(java中的long)
float 浮点型(java中的float double)
char 定长字符串(String)
varchar 可变长字符串(StringBuffer/StringBuilder)
char和varchar怎么选择？
在实际的开发中，当某个字段中的数据长度不发生改变的时候，是定长的，例如：性别、生日等都是 采用char。
当一个字段的数据长度不确定，例如：简介、姓名等都是采用varchar。

date		日期类型 （对应Java中的java.sql.Date类型）
BLOB		二进制大对象（存储图片、视频等流媒体信息） Binary Large OBject 

（对应java中的Object）
往BLOB类型的字段上插入数据的时候，例如插入一张图片，视频等，需要使用IO流
CLOB 字符大对象（存储较大文本，比如，可以存储4G的字符串。） Character Large OBject
（对应java中的Object）
3，删除表
drop table 表名;

2，insert语句插入数据（DML）

语法格式：
insert into 表名(字段名1,字段名2,字段名3,…) values(值1,值2,值3,…)
要求：字段的数量和值的数量相同，并且数据类型要对应相同。
注意：
当一条insert语句执行成功之后，表格当中必然会多一行记录。
即使多的这一行记录当中某些字段是NULL，后期也没有办法在执行
insert语句插入数据了，只能使用update进行更新。

insert插入多条记录

insert into t_student
(no,name,sex,classno,birth)
values
(3,‘rose’,‘1’,‘gaosi2ban’,‘1952-12-14’),(4,‘laotie’,‘1’,‘gaosi2ban’,‘1955-12-14’);

日期

date短日期：只包括年月日信息 %Y%m%d
datetime长日期：包括年月日 时分秒信息 %Y%m%d %h:%i:%s
now()函数，获取系统当前时间，并且获取的时间带有时分秒信息

insert插入日期

str_to_date函数可以把字符串varchar转换成日期date类型数据，通常使用在插入insert方面，因为插入的时候需要一个日期类型的数据
%Y%m%d提供的日期字符串为这个格式
date_format 可将日期类型转换成特定格式的字符串
格式： date_format(日期类型数据,‘日期格式’)
这个函数通常用在查询日期方面，设置展示的日期格式

3，update(DML)

语法格式：
update 表名 set 字段名1=值1,字段名2=值2… where 条件;

注意：没有条件限制会导致整张表数据全部更新。

4，表的复制

将一个查询结果当作一张表新建，可以完成表的快速复制
语法：
create table 表名 as select语句;
将查询结果当做表创建出来。

5，将查询结果插入到一张表中

mysql> insert into dept1 select * from dept;

6，快速删除表中数据

delete (DML)

语法格式：
delete from 表名 where 条件;
删除数据方式较慢
表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放
缺点：删除效率比较低
优点：支持回滚

truncate(DDL)

删除大表中的数据:
truncate table 表名; // 表被截断，不可回滚。永久丢失。
删除效率较高，表被一次截断，物理删除
缺点：不支持回滚
优点：快捷
删除表中数据，表还在
增删改查：CRUD操作
Create（增） Retrieve（检索） Update（修改） Delete（删除）

十四，约束(Constraint)

1，约束

在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的
合法性、有效性、完整性。
常见的约束有哪些呢？
非空约束(not null)：约束的字段不能为NULL
唯一约束(unique)：约束的字段不能重复
主键约束(primary key)：约束的字段既不能为NULL，也不能重复（简称PK）
外键约束(foreign key)：…（简称FK）
检查约束(check)：注意Oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束。

2，非空约束 not null只有列级约束，没有表级约束

create table t_user(
id int,
username varchar(255) not null,
password varchar(255)
);

source命令直接执行sql脚本文件
xxx.sql文件被称为sql脚本文件
sql脚本文件中编写了大量的sql语句
我们执行sql脚本文件的时候，该文件中所有的sql语句会全部执行

3，唯一性约束（unique）

唯一约束修饰的字段具有唯一性，不能重复。但可以为NULL。

案例：给两个列或者多个列添加unique
	drop table if exists t_user;
	create table t_user(
		id int, 
		usercode varchar(255),
		username varchar(255),
		unique(usercode,username) // 多个字段联合起来添加1个约束unique 【表级约束】
	);
name和email两个字段联合起来具有唯一性

在mysql中，如果一个字段同时被not null 和unique约束的话，该字段自动变成主键字段

4，主键约束

主键值是每一行记录的唯一标识(身份证号)
任何一张表都应该有主键，没有主键表无效
主键的特征：not null+unique (主键值不能是null，同时也不能重复)

给一张表添加主键约束：
	create table t_user(
		id int primary key,  // 列级约束
		username varchar(255),
		email varchar(255)
	);
表级约束：primary key(id)
复合主键：primary key(id,name)

一张表主键约束只能添加一个(身份证号)
主键值一般是定长的

主键有什么作用？

- 表的设计三范式中有要求，第一范式就要求任何一张表都应该有主键。
- 主键的作用：主键值是这行记录在这张表当中的唯一标识。（就像一个人的身份证号码一样。）

主键的分类

根据主键字段的字段数量来划分：
	单一主键（推荐的，常用的。）
	复合主键(多个字段联合起来添加一个主键约束)（复合主键不建议使用，因为复合主键违背三范式。）
根据主键性质来划分：
	自然主键：主键值最好就是一个和业务没有任何关系的自然数。（这种方式是推荐的）
	业务主键：主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键，拿着身份证号码作为主键。（不推荐用）
				  最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候，主键值可能也需要
				  随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复

mysql提供主键值自增

drop table if exists t_user;
create table t_user(
	id int primary key auto_increment, // id字段自动维护一个自增的数字，从1开始，以1递增。
	username varchar(255)
);

5，外键约束

外键约束的相关术语：
外键约束: foreign key
外键字段：添加有外键约束的字段
外键值：外键字段中的每一个值。
外键可以为NULL。

外键字段引用其他表的某个字段的时候，被引用的字段必须是主键吗？

注意：被引用的字段不一定是主键，但至少具有unique约束。

案例：设计数据库表，用来维护学生和班级的信息

    t_class 班级表
	cno(pk)		cname
	--------------------------------------------------------
	101		        高三100班
	102		        高三102班

	t_student 学生表
	sno(pk)		sname				classno(该字段添加外键约束fk)
	------------------------------------------------------------
	1				zs1				101
	2				zs2				101
	3				zs3				102
	4				zs4				102
	5				zs5				102

将以上表的建表语句写出来：

t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表。t_class表叫做父表。

顺序要求：
	删除数据的时候，先删除子表，再删除父表。
	添加数据的时候，先添加父表，在添加子表。
	创建表的时候，先创建父表，再创建子表。
	删除表的时候，先删除子表，在删除父表。

十五，存储引擎

不同的存储引擎，表存储数据的方式不同
1，完整的建表语句
CREATE TABLE t_x (
id int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
建表的时候可以指定存储引擎，也可以指定字符集。
mysql默认使用的存储引擎是InnoDB方式，默认采用的字符集是UTF8
InnoDB最大特点：支持事务，保证数据的安全(效率不高，不能压缩)
查看当前mysql支持的存储引擎：show engines \G

MyISAM存储引擎

MyISAM这种存储引擎不支持事务。
MyISAM是mysql最常用的存储引擎，但是这种引擎不是默认的。
MyISAM采用三个文件组织一张表：
xxx.frm（存储格式的文件）
xxx.MYD（存储表中数据的文件）
xxx.MYI（存储表中索引的文件）
{索引是一本书的目录，缩小扫描范围}
优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。
缺点：不支持事务。

InnoDB存储引擎

优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。

	表的结构存储在xxx.frm文件中
	数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法转换成只读。
	这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制。
	InnoDB支持级联删除和级联更新。

sql级联删除功能：在删除主表时，自动删除副表(外键约束)相应内容
删除包含主键值的行的操作，该值由其它表的现有行中的外键列引用。在级联删除中，还删除其外键值引用删除的主键值的所有行。
外键约束对子表的含义:
如果在父表中找不到候选键,则不允许在子表上进行insert/update

外键约束对父表的含义:
在父表上进行update/delete以更新或删除在子表中有一条或多条对应匹配行的候选键时,父表的行为取决于：在定义子表的外键时指定的on update/on delete子句

InnoDB和MyISAM的区别

事务 InnoDB支持事务、回滚、事务安全和奔溃恢复。而MyISAM不支持，但查询的速度要比InnoDB更快
主键 InnoDB规定，如果没有设置主键，就自动的生成一个6字节的主键，而MyISAM允许没有任何索引和主键的存在，索引就是行的地址
外键 InnoDB支持外键，而MyISAM不支持
表锁 InnoDB支持行锁和表锁，而MyISAM只支持表锁
全文索引 InnoDB不支持全文索引，但是可以用插件来实现相应的功能，而MyISAM是本身就支持全本索引
行数 InnoDB获取行数时，需要扫全表。而MyISAM保存了当前表的总行数，直接读取即可。
总结：
MyISAM只适用于查询大于更新的场景，如果你的系统查询的情况占绝大多数（例如报表系统）就可以使用MyISAM来存储，除此之外，都建议使用InnoDB。

MEMORY存储引擎(memory内存存储引擎)

数据存储在内存中(一断电就消失)
表数据及索引被存储在内存中（目的就是查询快）

十六，事务（Transaction）

1，一个事务就是一个完整的业务逻辑单元，不可再分

比如：银行账户转账，从A账户向B账户转账10000.需要执行两条update语句：
	update t_act set balance = balance - 10000 where actno = 'act-001';
	update t_act set balance = balance + 10000 where actno = 'act-002';
以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败。
要想保证以上的两条DML语句同时成功或者同时失败，那么就需要使用数据库的“事务机制”。

2，只有DML语句才会有事务这一说，其他都与它无关（insert delete update）

为什么？因为它们这三个语句都是和数据库表当中的“数据”相关的。
事务的存在是为了保证数据的完整性，安全性。

3，假设所有的业务都能使用1条DML语句搞定，还需要事务机制吗？

不需要事务。
一个事务其实就是多条DML语句共同联合起来才能完成某件事，所以需要事务的存在，如果任何一件复杂的事都能一条DML语句搞定，那么事务则没有存在的价值了

4，事务四大特性 ACID

A: 原子性：事务是最小的工作单元，不可再分。
C: 一致性：事务必须保证多条DML语句同时成功或者同时失败，保证数据一致性
I：隔离性：事务A与事务B之间具有隔离。
                   (事物A在操作一张表的时候，另一个事物B也操作这张表)
D：持久性：事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上(事务最终结束的一个保障)

5，事务的隔离性

事务隔离性存在隔离级别，理论上隔离级别包括4个：
查看隔离级别：select @@transaction_isolation;

设置全局隔离级别：
set global transaction isolation level REPEATABLE READ;
set global transaction isolation level  READ COMMITTED;
set global transaction isolation level READ UNCOMMITTED;
set global transaction isolation level SERIALIZABLE;

第一级别：读未提交（read uncommitted）

	     事务A可读取到事务B未提交的数据
	     问题：脏读（Dirty Read）现象：表示读到了脏的数据。
	     隔离级别一般是理论上的，大多数数据库隔离级别都2档起步

第二级别：读已提交（read committed） oracle数据库默认的隔离级别是：读已提交。

		事务A只能读取到事务B提交后的数据
		这种隔离级别解决了: 脏读现象
		读已提交存在的问题：不可重复读
		{不可重复读：在事务开启之后，第一次读到的数据是3条，当前事务还没结束，可能第二次再读      取的时候，读到的数据是4条，3不等于4称为不可重复读}

第三级别：可重复读（repeatable read一直读的备份数据） mysql数据库默认的隔离级别是：可重复读。

	    事务A开启之后，不管是多久，每一次在事务A中读取的数据都是一致的，即使事务B将数据已经修改，并且已经提交了，事务A读取到的数据还是没有发生改变，这就是可重复读
	    
	    问题：可能出现幻影读，每一次读取的数据都是幻象，不够真实
	    举例：早上九点开启了事务，只要事务不结束，到晚上九点，读到的数据还是那样，读到的是假象，不够绝对真实
		这种隔离级别解决了：不可重复读问题。

第四级别：序列化读/串行化读（serializable）

		解决了所有问题。
		每一次读取的数据最真实，效率最低。需要事务排队。(事务排队，不能并发)

十七，索引

1，什么是索引？

该字段很少DML(因为DML之后，索引需要重新排序)
索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制，一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引
索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制
先通过目录(索引)去定位一个大概的位置，然后直接定位到这个位置，做局域性扫描，缩小扫描的范围，快速的查找，这种查找方式属于通过索引检索，效率较高

(1)查询一张表的时候有两种检索方式

第一种方式：全表扫描
第二种方式：根据索引检索（效率很高）
在mysql数据库中索引也是需要排序的，并且这个索引的排序和TreeSet数据结构相同，TreeSet(TreeMap)底层是一个自平衡的二叉树，在mysql当中索引是一个B-Tree数据结构(遵循左小右大原则存放，采用中序遍历方式遍历取数据)
在任何数据库当中主键上都会自动添加索引对象

(2)建议不要随便添加索引，因为索引也需要维护

1，在mysql中，一个字段如果有unique约束的话，会自动创建索引对象

2，在任何数据库中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号

3，在mysql中，索引是一个单独的对象，不同的存储引擎以不同的形式存在

MyISAM存储引擎–>索引–>.MYI文件
InnoDB-------------------------->tablespace这样的表空间中
MEMORY------------------------>内存
索引在mysql当中都是以树的形式存在(自平衡二叉树：B-Tree)

4，索引实现原理：就是缩小扫描的范围，避免全表扫描(表中字段不会动，索引对象会排序)

select *from t_user where id = 101;
通过idIndex索引对象定位到101
通过101得出物理编号：0x6666,此时马上sql语句转换
select *from t_user where 物理编号=0x6666;

2，创建索引及索引的分类

创建索引

创建索引对象：
	create index 索引名称 on 表名(字段名);
删除索引对象：
	drop index 索引名称 on 表名;

sql语句是否使用索引：
explain SELECT * from emp where ename=‘KING’;

索引的分类

单一索引：给单个字段添加索引
复合索引: 给多个字段联合起来添加1个索引
主键索引：主键上会自动添加索引
唯一索引：有unique约束的字段上会自动添加索引

3，索引的失效

(1)模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的。

select ename from emp where ename like ‘%A%’;

(2)使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有索引，才会走索引，如果其中一百年一个字段没有索引，那么另一个字段上的索引也会失效

select id,name from kaka where name=‘kaka’ o rname=‘ka’;

(3)使用复合索引的时候，没有使用左侧的列查找，索引失效

create index emp_job_sal_index on emp(job,sal);

(4)在where当中索引列(字段)参加了运算，索引失效

select * from emp where sal=800;
select * from emp where sal+1=800;

(5)在where当中索引列使用函数

select * from emp where lower(ename)=‘switch’;

十八，视图(view)站在不同的角度去看待同一份数据

同一张表的数据，通过不同的角度去看待

创建视图 删除视图

创建视图 ：
create view myview as select empno,ename from emp;
删除视图：
drop view myview;
注意：只有DQL语句才能以视图对象的方式创建出来。

create view viewName as 这里的语句必须是DQL语句;

用视图做什么?

我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致原表被操作
视图的特点：通过对视图的操作，会影响到原表数据

视图在实际开发中的作用?

假设有一条非常复杂的sql语句，而这条sql语句需要在不同位置上反复使用
可以把这条复杂的sql语句以视图对象的形式新建
在需要编写这条sql语句的位置直接使用视图对象，可简化开发，并利用后期维护，因为修改的时候只需修改视图对象所映射的sql语句

DBA常用命令

将数据库当中的数据导出

在windows的dos命令窗口中执行：（导出整个库）
mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123

在windows的dos命令窗口中执行：（导出指定数据库当中的指定表）
	mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot –p123

数据导入

注意：需要登陆到mysql数据库服务器上
1，创建数据库：
create database bjpowernode;
2，使用数据库：
use bjpowernode;
3，然后初始化数据库：
source D:\bjpowernode.sql

十九，数据库设计三范式

数据库设计范式：数据库表的设计依据，教我们怎么进行数据库表的设计
设计数据库表时，按三范式进行，可避免表中数据的冗余，空间的浪费

第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分。
第二范式：建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖。
第三范式：建立在第二范式的基础之上，所有非主键字段直接依赖主键，不能产生传递依赖。

第一范式(最核心的范式，所有表设计都要满足){一对一，外键唯一unique}

必须有主键，并且每一个字段都是 原子性不可再分(联系方式=邮箱地址+电话)

第二范式{学生和老师多对多的关系：多对多，三张表，关系表两个外键}

建立在第一范式的基础之上，要求所有非主键字段必须完全依赖主键，不要产生部分依赖

    t_student学生表
	sno(pk)		sname
	-------------------
	1				张三
	2				李四
	3				王五

	t_teacher 讲师表
	tno(pk)		tname
	---------------------
	1				王老师
	2				张老师
	3				李老师

	t_student_teacher_relation 学生讲师关系表
	id(pk)		sno(fk)		tno(fk)
	----------------------------------
	1				1				3
	2				1				1
	3				2				2
	4				2				3
	5				3				1
	6				3				3

第三范式{一对多，两张表，多的表加外键}

第三范式建立在第二范式的基础之上，要求所有非主键字段必须直接依赖主键，不要产生传递依赖
{一个教室中有多个学生
一年一班依赖班级编号01，
01依赖主键1001，
产生了传递依赖}

二十，JDBC

1，数据库驱动

驱动：声卡，显卡，数据库

我们的程序会通过数据库驱动，和数据库打交道！

2，JDBC

SUN 公司为了简化开发人员的（对数据库的统一）操作，提供了一个(Java操作数据库的)规范，JDBC

这些规范的实现由具体的厂商去做

对于开发人员来说，我们只需要掌握JDBC的接口操作即可

java.sql

javax.sql

还需要导入数据库驱动包

3，第一个JDBC程序

创建测试数据库
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;
USE jdbcStudy;
CREATE TABLE users(
id INT PRIMARY KEY,
NAME VARCHAR(40),
PASSWORD VARCHAR(40),
email VARCHAR(60),
birthday DATE
);
INSERT INTO users(id,NAME,PASSWORD,email,birthday)
VALUES(1,‘zhansan’,‘123456’,‘zs@sina.com’,‘1980-12-04’),
(2,‘lisi’,‘123456’,‘lisi@sina.com’,‘1981-12-04’),
(3,‘wangwu’,‘123456’,‘wangwu@sina.com’,‘1979-12-04’)

1,创建一个普通项目
2,导入数据库驱动

3,编写测试代码

package com.company;

import java.sql.*;

public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1,加载驱动
        Class.forName("com.mysql.jdbc.Driver");//固定写法，加载驱动
        //2,用户信息和url    useUnicode=true&characterEncoding=utf8&&useSSL=true
        String url ="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&&useSSL=false";
        String username = "root";
        String password = "123456";
        //3,连接成功，数据库对象,connection代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);
        //4,执行sql的对象，statement执行sql的对象
        Statement statement = connection.createStatement();
        //5,执行sql的对象 去 执行sql，可能存在结果，查看返回结果
        String sql="select * from users";
        ResultSet resultSet = statement.executeQuery(sql);//返回的结果集,结果集中封装了我们全部的查询出来的结果
        while(resultSet.next()){
            System.out.println("id="+resultSet.getObject("id"));
            System.out.println("NAME="+resultSet.getObject("NAME"));
            System.out.println("PASSWORD="+resultSet.getObject("PASSWORD"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("birthday="+resultSet.getObject("birthday"));
        }
        //6,释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

步骤总结：
1.加载驱动
2.连接数据库 DriverManager
3.获取执行SQL的对象 Statement
4.获得返回的结果集
5.释放连接

4，JDBC中对象解释

DriverManager

//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName(“com.mysql.jdbc.Driver”);//固定写法

//3,连接成功，数据库对象,connection代表数据库
Connection connection = DriverManager.getConnection(url, username, password);

//connection代表数据库
//数据库设置自动提交
//事务提交
//事务回滚
connection.rollback();
connection.commit();
connection.setAutoCommit();

URL

String url ="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&&useSSL=false";

//mysql 默认3306
//协议://主机地址:端口号/数据库名？参数1&参数2&参数3

//Oracle 1521
//jdbc:oralce:thin:@localhost:1521:sid

statement 执行SQL的对象 PrepareStatement 执行SQL的对象

statement.executeQuery(); //查询操作返回 ResultSet
statement.execute(); //执行任何sql
statement.executeUpdate();//更新，插入，删除，返回一个受影响的行数

ResultSet 查询的结果集，封装了所有的查询结果

ResultSet resultSet = statement.executeQuery(sql);//返回的结果集,结果集中封装了我们全部查询的结果

resultSet.getObject();//在不知道列类型下使用
//如果知道列的类型，就使用指定的类型
resultSet.getString();
resultSet.getInt();

遍历,指针

    resultSet.next(); //移动到下一个
    resultSet.afterLast();//移动到最后
    resultSet.beforeFirst();//移动到最前面
    resultSet.previous();//移动到前一行
    resultSet.absolute(row);//移动到指定行

释放内存

    resultSet.close();
    statement.close();
    connection.close();//这些连接很占用内存

5，statement对象

Jdbc中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。

Statement.executeQuery方法用于向数据库发生查询语句，executeQuery方法返回代表查询结果的ResultSet对象。
Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sq|语句， executeUpdate执行完后， 将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)。

CRUD操作-create

使用executeUpdate(String sql)方法完成数据添加操作，示例操作：

 Statement statement = connection.createStatement();
        String sql = "insert into user(...) values(...)";
        int num = statement.executeUpdate(sql);
        if(num>0){
            System.out.println("插入成功");
        }

CRUD操作-delete

使用executeUpdate(String sql)方法完成数据删除操作，示例操作：

Statement statement = connection.createStatement();
        String sql = "delete from user where id =1";
        int num = statement.executeUpdate(sql);
        if(num>0){
            System.out.println("删除成功");
        }

CURD操作-update

使用executeUpdate(String sql)方法完成数据修改操作，示例操作：

Statement statement = connection.createStatement();
        String sql = "update user set name ='' where name = ''";
        int num = statement.executeUpdate(sql);
        if(num>0){
            System.out.println("修改成功");
        }

CURD操作-read

使用executeUpdate(String sql)方法完成数据查询操作，示例操作：

Statement statement = connection.createStatement();
        String sql = "select * from  user where id =1";
        ResultSet rs= statement.executeQuery(sql);
        if(rs.next()){
            //根据获取列的数据类型，分别调用rs的相应方法映射到java对象中
        }

代码实现

1，提取工具类

public class jdbcUtils {
    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;
    static{
        try {
            InputStream inputStream = jdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(inputStream);//从输入流中读取属性列表（键和元素对）。
            driver=properties.getProperty("driver");
            url=properties.getProperty("url");
            username=properties.getProperty("username");
            password=properties.getProperty("password");
            //1,驱动只加载一次
            Class.forName(driver);
        } catch (IOException e) {
            e.printStackTrace();
        }catch (ClassNotFoundException e){
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() throws Exception{
        return  DriverManager.getConnection(url, username, password);
    }
    //3.释放资源
    public static void release(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {

        if(resultSet!=null){
            resultSet.close();
        }
        if (statement!=null){
            statement.close();
        }
        if(connection!=null){
            connection.close();
        }
    }

2，编写增删改的方法，exectueUpdate

public class insertTest {
    public static void main(String[] args) throws SQLException {
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try{
            connection=jdbcUtils.getConnection();//获取数据库连接
            statement=connection.createStatement();//获得sql的执行对象
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +
                    "VALUES(5,'赵六','123456','233223@qq.com','2020-01-01')";
            int i=statement.executeUpdate(sql);
            if(i>0){
                System.out.println("插入成功~");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            jdbcUtils.release(connection,statement,resultSet);

        }
    }
}

3.查询 executeQuery

public class testQuery {
    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="select * from users where id=5";
            resultSet=statement.executeQuery(sql);//查询完毕会返回一个结果集
            while(resultSet.next()){
                System.out.println(resultSet.getString("NAME"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}

6，SQL注入问题(Statement对象不安全)

sql存在漏洞，会被攻击导致数据泄露 {SQL会被拼接 or}

public class SQL注入 {
    public static void main(String[] args) {
        //login("lisi","123456");//正常登录
        login("'or' 1=1","'or' 1=1");
    }
    //登录业务
    public static void login(String username,String password){


        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try{
            connection= jdbcUtils.getConnection();
            statement=connection.createStatement();
            //SQL            select * from users where name='lisi' AND password='123456';
            String sql = "select * from users where NAME='"   + username +   "'  AND PASSWORD='"+ password +"'" ;
            resultSet=statement.executeQuery(sql);//查询完毕会返回一个结果集
            while(resultSet.next()){
                System.out.println(resultSet.getString("NAME"));
                System.out.println(resultSet.getString("password"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}

7，PreparedStatement对象(安全)

PreparedStatement 可以防止SQL注入 ，效率更高。
1，新增

public class insertTest2 {
    public static void main(String[] args) throws SQLException {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        try{
            connection= jdbcUtils.getConnection();
            //区别
            //使用? 占位符代替参数
            String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +
                    "values(?,?,?,?,?)";
            //预编译sql，先写sql，然后不执行
            preparedStatement=connection.prepareStatement(sql);
            //手动设置参数赋值
            preparedStatement.setInt(1,4);
            preparedStatement.setString(2,"小四");
            preparedStatement.setString(3,"12323838");
            preparedStatement.setString(4,"123456789@qq.com");
            //注意点： sql.Date    数据库   java.sql.Date()
            //       util.Date    Java   new Date().getTime()  获得时间戳
            preparedStatement.setDate(5,new java.sql.Date(new Date().getTime()));
            //执行
            int i=preparedStatement.executeUpdate();
            if(i>0){
                System.out.println("输入成功~");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            jdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }
}

2，删除

public class DeleteTest2 {
    public static void main(String[] args) throws SQLException {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        try{
            connection=jdbcUtils.getConnection();
            //区别
            //使用? 占位符代替参数
            String sql="delete from users where id=?";
            preparedStatement=connection.prepareStatement(sql);//预编译sql，先写sql，然后不执行
            //手动给参数赋值
            preparedStatement.setInt(1,5);
            //执行
            int i=preparedStatement.executeUpdate();
            if(i>0){
                System.out.println("删除成功~");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            jdbcUtils.release(connection,preparedStatement,null);
        }
    }
}

3，查询

public class queryTest2 {
    public static void main(String[] args) throws SQLException {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        try{
            connection= jdbcUtils.getConnection();
            String sql="select * from users where id=?";//编写sql
            preparedStatement=connection.prepareStatement(sql);//预编译
            preparedStatement.setInt(1,1);//传递参数
            resultSet=preparedStatement.executeQuery();//执行
            if(resultSet.next()){
                System.out.println(resultSet.getString("name"));
                System.out.println(resultSet.getString("password"));

            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
            jdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }
}

4，修改

public class updateTest2 {
    public static void main(String[] args) throws SQLException {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        try{
            connection= jdbcUtils.getConnection();
            //区别
            //使用? 占位符代替参数
            String sql="update users set name=? where id=?";
            preparedStatement=connection.prepareStatement(sql);//预编译sql，先写sql，然后不执行
            //手动给参数赋值
            preparedStatement.setString(1,"六一");
            preparedStatement.setInt(2,1);
            //执行
            int i=preparedStatement.executeUpdate();
            if(i>0){
                System.out.println("更新成功~");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            jdbcUtils.release(connection,preparedStatement,null);
        }
    }
}

5，登录：
PreparedStatement防止sql注入的本质：把传递进来的参数当作字符
//假设其中存在转义字符，会被直接转义{比如 ‘ ’ 会被转义}

public class SQL注入 {
    public static void main(String[] args) {
        //login("lisi","123456");//正常登录
        login("'' or 1=1","123456");
    }
    //登录业务
    public static void login(String username,String password){


        Connection connection=null;
        PreparedStatement preparedStatement =null;
        ResultSet resultSet=null;
        try{
            connection= jdbcUtils.getConnection();
            //SQL            select * from users where name='lisi' AND password='123456';
            String sql = "select * from users where NAME=?AND PASSWORD=?";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,username);
            preparedStatement.setString(2,password);

            resultSet=preparedStatement.executeQuery();//查询完毕会返回一个结果集
            while(resultSet.next()){
                System.out.println(resultSet.getString("NAME"));
                System.out.println(resultSet.getString("password"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}

8，使用IDEA连接数据库

连接成功后，可以选择数据库

双击数据库，查看数据库内容

修改内容，并提交


执行sql语句

9，JDBC操作事务

ACID原则

原子性：要么全部完成，要么都不完成

一致性：结果总数不变

隔离性：多个进程互不干扰

持久性：一旦提交不可逆，持久化到数据库了

隔离性的问题：

脏读： 一个事务读取了另一个没有提交的事务

不可重复读：在同一个事务内，重复读取表中的数据，表发生了改变

虚读（幻读）：在一个事务内，读取到了别人插入的数据，导致前后读出来的结果不一致

代码实现

1,开启事务conn.setAutoCommit(false);
2,一组业务执行完毕，提交事务
3,可以在catch语句中显示的定义回滚，但是默认失败会回滚

public class transactionTest {
    public static void main(String[] args) throws SQLException {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        try{
            connection= jdbcUtils.getConnection();
            //关闭数据库的自动提交,自动会开启事务
            connection.setAutoCommit(false);
            String sql1="update account set money=money-100 where name='A' ";
            preparedStatement=connection.prepareStatement(sql1);
            preparedStatement.executeUpdate();

            String sql2="update account set money=money+100 where name='B' ";
            preparedStatement=connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();
            //业务完毕，提交事务
            connection.commit();
            System.out.println("操作成功~");
        }catch(Exception e){
            //如果失败则回滚事务
            connection.rollback();
            e.printStackTrace();
        }finally {
            jdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }
}

10，数据库连接池

数据库连接–执行完毕–释放

连接–释放 十分浪费资源

池化技术： 准备一些预先的资源，过来就连接预先准备好的

开门----业务员：等待------服务--------关门(服务器关闭)
需要几个服务员：
常用连接数 100
最少连接数：100
最大连接数 ： 120 业务最高承载上限
最大连接数用完了，排队等待
等待超时：100ms

编写连接池，实现一个接口 DateSource

开源数据源实现(拿来即用)

DBCP
C3P0
Druid: 阿里巴巴
使用了这些数据库连接池之后，我们在项目开发中就不需要编写连接数据库的代码了

DBCP需要用到的jar包

public class DBCP_JDBCUtil {
    private static DataSource dataSource = null;
    //通过配置文件加载驱动,只用加载一次即可，再静态代码块中实现
    static{
        try{
            InputStream inputStream = DBCP_JDBCUtil.class.getClassLoader().getResourceAsStream("dbcp.properties");
            Properties properties = new Properties();
            properties.load(inputStream);
            //创建数据源 工厂模式 - - - > 创建
            //使用BasicDataSourceFactory数据连接池工厂类创建一个连接池
			//并将从dbcp.propertie读取出的键值对放入连接池中
            dataSource = BasicDataSourceFactory.createDataSource(properties);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //建立连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    //释放资源
    public static void releaseResource(Connection connection, Statement statement, ResultSet resultSet){
       if(resultSet!=null){
            resultSet.close();
        }
        if (statement!=null){
            statement.close();
        }
        if(connection!=null){
            connection.close();
        }
    }
}

C3P0

结论：无论使用什么数据源，本质是不变的，DateSource接口不会变，方法就不会变