Java

数据库
什么是数据库？
数据库(Database)：存储数据的仓库
高效地存储和处理数据的介质(介质主要是两种：磁盘和内存)


数据库的分类
基于存储介质的不同：分为关系型数据库(SQL)和非关系型数据库(NoSQL:Not Only SQL)


不同数据库阵营的产品
关系数据库
大型：Oracle、DB2
中型：SQL Server/MySQL
小型：Access等
非关系型数据库
Memcached、MongoDB、Redis


两种数据库阵营的区别
关系型数据库：安全(保存磁盘，基本不可能丢失)，容易理解，比较浪费空间(二维表)
非关系型数据库：效率高，不安全(断电丢失)


什么是关系型数据库？
是一种建立在关系模型(数字模型)上的数据库
关系模型：一种所谓建立在关系上的模型
关系模型包含三个方面：
数据结构：用于解决数据存储的问题，二维表(有行有列)
操作指令集合：所有SQL语句
完整性约束：表内数据约束(字段与字段)、表与表之间约束(外键)




关系型数据库的设计
从需要存储的数据需求中分析，如果是一类数据(实体)应该设计成一张二维表：表是由表头和数据部分组成
表头：即字段名，用来规定数据的名字
数据部分：实际存储的数据单元


关系型数据库，需要维护实体内部、实体与实体之间的联系


关键字说明
数据库：database
数据库系统：DBS(Database System):是一种虚拟系统，将多种内容关联起来的称呼
DBS = DBMS + DB
DBMS：Database Management Sustem,数据库管理系统，专门管理数据库
DBA：Database Administrator,数据库管理员
行/记录：row/record
列/字段：column/field




交互方式
1.客户端连接认证：连接服务器、认证身份(mysql.exe -hPup)
2.客户端发送SQL指令
3.服务器接收SQL指令，并处理SQL指令，返回操作结果
4.客户端接受结果，并显示结果
5.断开连接




SQL
SQL： (Structured Query Language,结构化查询语言)
SQL分为三个部分：
DDL： (Data Definition Language,数据定义语言)用来维护存储数据的结构(数据库、表)，代表指令：create、drop、alter等
DML： (Data Manipulation Language,数据操作语言)用来对数据进行操作(数据表中的内容)，代表指令：insert,delete,update等。其中DML内部又单独进行了一个分类：DQL(Data Query Language,数据查询语言)如select
DCL： (Data Control Language,数据控制语言)主要是负责权限管理(用户)，代表指令：grant/revoke等




库操作
查看数据库
查看所有数据库:show databases;
查看指定部分的数据库(模糊查询):show databases like 'pattern';
pattern是匹配模式
%表示匹配多个字符
_表示匹配单个字符
查看数据库的创建语句：show create database 数据库名字；


新增数据库
create database 数据库名字【库选项】；
库选项：用来约束数据库，分为两个选项
字符集设定：charset/character set 具体字符集(数据存储的编码格式，常用的有：GBK和UTF8)
校对集设定：collate具体校对集(数据比较的规则)
数据库名字不能用关键字(已经被系统使用的字符)或者保留字(将来系统可能会用到的字符)


创建结果：
在数据库系统中，增加了对应的数据库信息
会在保存数据的文件夹下(Data目录)，创建一个对应数据库名字的文件夹
每个数据库下都有一个opt文件，保存了库选项




库操作
更新数据库
数据库名字不可以修改
数据库的修改权限库选项，即字符集和校对集(校对集依赖字符集)
alter database 数据库名字【库选项】;
charset/character set [=] 字符集
collate 校对集
删除数据库
drop database 数据库名字






表操作
新增数据库
create table [if not exists] 表名(
字段名字 数据类型
.........
字段名字 数据类型
) [表选项];

表选项：控制表的表现
字符集：charset/character set 具体字符集； -- 保证表中数据存储的字符集
校对集：collate 具体校对集
存储引擎：engine 具体的存储引擎(inbodb和myisam)


方案一：
显式地指定表所属的数据库：
create table 数据库名.表名()；
方案二：
隐式地指定表所属数据库
进入数据库环境：use 数据库名字；




查看数据库
查看所有表：show tables;
查看部分表(模糊查询)：show tables like 'pattern';
pattern是匹配模式
%表示匹配多个字符
_表示匹配单个字符
查看表的创建语句：show create table 表名;
查看表结构(表中的字段信息)：
desc/describe/show columns from 表名;


修改数据表
修改表本身
修改表名：rename table 旧表名 to 新表名;
修改表选项(字符集、校对集、存储引擎都可以修改):
alter table 表名 表选项 [=] 值；
修改字段


删除数据表
drop table 表名1，表名2.....;




字段操作
新增字段：alter table 表名 add [column]字段名 数据类型[列属性][位置];
first:第一个位置
after:在哪个字段之后：after 字段名;


修改字段：alter table 表名 modify 字段名 数据类型[列属性][位置];


重命名字段：alter table 表名 change 旧字段 新字段名 数据类型[列属性][位置];


删除字段：alter table 表名 drop 字段名;




数据操作
新增数据
方案一：给全表字段插入数据，不需要指定字段列表，要求数据的值出现的顺序必须与表中设计的字段出现的顺序一致，凡是非数值数据，都需要使用引号(建议是单引号)包裹
insert into 表名 values(值列表)[,(值列表)];


方案二：给部分字段插入数据，需要选定字段列表，字段列表出现的顺序与字段的顺序无关，但是值列表的顺序必须与选定的字段顺序一致
insert into 表名(字段列表) values(值列表)[,(值列表)];




查看数据
查看所有数据：select * from 表名 [where 条件];
查看指定字段、指定条件的数据：select字段列表from 表名[where 条件];




更新数据
update 表名 set 字段 = 值[where 条件];
建议都有where，否则就是更新全部


删除数据
删除是不可逆的，谨慎删除
delete from 表名 [where 条件];




校对集问题
校对集：数据比较的方式，有三种格式
_bin:binary,二进制比较，就是取出二进制位，一位一位的比较，区分大小写
_cs:case sensitive,大小写敏感，区分大小写
_ci:case insensitice,大小写不敏感，不区分大小写


查看数据库所支持的校对集:show collation;


校对集应用
使用utf8的_bin和_ci来验证不同校对集的效果
根据某个字段进行排序：order by 字段名 [asc/desc]




数据类型
数据类型(列类型)：对数据进行统一的分类，从系统的角度出发，为了能够使用统一的方式进行管理，更好地利用有限的空间


SQL中将数据类型分成三类
数据类型
字符串类型
时间日期类型


数据型
整数型：存放整型数据
tinyint：迷你整型，使用1个字节存储，表示的状态最多为256种
smallint：小整型，使用2个字节存储，表示的状态最多为65536种
mediumint：中整型，使用3个字节存储
int：标准整型，使用4个字节存储
bigint：大整型，使用8个字节存储


  类型 字节 有符号 无符号
TINYINT 1 -128~127 0~255
SMALLINT 2 -2^15~2^15-1 0~2^16-1
MEDIUMINT 3 -2^23~2^23-1 0~2^24-1
INT/INTEGE 4 -2^31~2^31-1 0~2^32-1
BIGINT 8 -2^63~2^63-3 0~2^64-1




数值型
小数型：带有小数点或者范围超出整型的数值类型
浮点型：小数点浮动，精度有限，而且会丢失精度
定点型：小数点固定，精度固定，不会丢失精度




时间日期类型
datetime：时间日期，格式是YYYY -mm-ddHH:ii:ss, 表示的范围是从1000到9999年，有0值(0000-00-00 00:00:00)
date:日期，就是datetime中的date部分
time:时间(段)，指定的某个区间之间，比如-时间到+时间，或者过去的某个时间到将来的某个时间
timestamp:时间戳，但并不是时间戳，只是从1970年开始的，YYYY-mm-dd HH:ii:ss格式与datetime完全一致
year:年份，有两种形式：year(2)和year(4)




字符串类型
字符串类型分为：char、varchar、text、blob、enum、set
char(定长字符串):磁盘(二维表)在定义结构的时候，就已经确定了最终数据的存储长度
char(L):L代表Length，可以存储的长度，单位为字符，最大长度值可以为255
varchar(变长字符串)：在分配空间的时候，按照最大的空间分配，但是实际上最终用了多少，是根据具体的数据来确定
varchar(L):L表示字符长度，理论长度是65536个字符，但是会多出1到2个字节，来确定存储的实际长度


文体字符串：如果数据量非常大，通常超过255个字符就会使用文本字符串
文本字符串根据存储的数据的格式分为：
text:存储文字，存储二进制数据的文件路径
blob:存储二进制数据(通常不用)


枚举字符组(enum):事先把所有可能出现的结果都设计好，实际上存储的数据必须是规定好的数据中的一个
枚举的使用方式：
enum(可能出现的元素列表);
例如：enum('男','女','不男不女','妖怪','保密');
使用：存储数据，只能存储上面定义好的数据


集合字符串：跟枚举类似，实际存储的是数值，而不是字符串，但集合是多选
集合使用方式：
set(元素列表)
可以使用元素列表中的多个元素，使用逗号分隔




MySQL记录长度
MySQL中规定：任何一条记录最长不能超过65535个字节
varchar永远达不到理论值
utf8下varchar的实际顶配：21844字符
GBK下varchar的实际顶配：32766字符




列属性


列属性：真正约束字段的是数据类型，但是这种约束很单一，需要有一些额外的约束，来更加保证数据的合法性，这就需要列属性
列属性包括：NULL/NOT NULL、default、Primary key、unique key、
auto_increment、comment


空属性：
两个值:Null(默认的)、NOT NULL(不为空)


列描述：comment，描述，没有实际含义，是专门用来描述字段，会根据表创建语句保存，用来给程序员(数据库管理员)来进行了解的


默认值：default，某一种数据会经常性地出现某个具体的值，可以在一开始就指定好，在需要真实数据的时候，用户可以选择性地使用默认值


主键：primary key,主要的键，一张表只能由一个字段可以使用相应的键，用来唯一的约束该字段里面的数据，不能重复，这种称之为主键
增加主键：
方案一:在创建表的时候，直接在字段之后，跟primary key关键字(主键本向不允许为空)
方案二:在创建表的时候，在所有的字段之后，使用primary key关键字(主键字段列表)来创建主键，如果有多个字段作为主键，可以是复合主键
方案三：当表已经创建好之后，额外追加主键，可以通过修改表字段属性，也可以直接追加
alter table 表名 add primary key(字段列表);


主键约束：主键对应的字段中的数据不允许重复，一旦重复，数据操作(增和改)失败
更新主键&删除主键
alter table 表名 drop primary key;
主键分类：业务主键、逻辑主键
create table my_student(
id int primary ket auto_increment comment'逻辑主键：自增长',
number char(10)not null comment'学号'，
name varchar(10) not null
)


自动增长:auto_increment,当对应的字段，不给值，或给默认值、或给NULL值，会自动的被系统触发，系统会从当前字段中已有的最大值基础上，再进行+1操作，得到一个新的不同的数据
特点：
任何一个字段要做自增长，前提必须本身就是一个索引
自增长字段必须是数字，而且是整型
一张表最多只能有一个自增长


修改自增长：alter table 表名 auto_increment=值;
查看自增长对应的变量：show variables like 'auto_increment';

删除自增长：alter table 表名 modify 字段类型;


唯一键(unique key)
默认允许自动为空，而且可以多个为空
增加唯一键
方案一：在创建表的时候，字段之后直接跟unique/unique key
方案二：在所有的字段之后增加unique key(字段列表);
方案三：在创建表之后增加唯一键




更新唯一键&删除唯一键
错误的删除方法：alter table 表名 drop unique key;
alter table 表名 drop index 索引名字;


索引：系统根据某种算法，将已有的数据或未来可能新增的数据，单独建立一个文件，文件能够实现快速的匹配数据，并且能够快速地找到对应表中的记录


索引的意义
提升查询数据的效率
约束数据的有效性、唯一性等


MySQL中提供了多种索引
主键索引：primary key
唯一索引：unique key
全文索引：fulltext index 
普通索引：index
创建
create [unique] index 索引名 on 表名(字段名(长度));
alter 表名 add [unique] index [索引名] on (字段名(长度));


删除：drop index [索引名] on 表名;






范式
范式：Normal Format，是一种离散数学中的知识，是为了解决一种数据的存储与优化的问题，它的终极目标是为了减少数据的**
范式是一种分层结构的规范，分为六层：
1NF,2NF,3NF,....6NF
1NF是最底层，要求最低
6NF是最高层，要求最严格






高级数据操作
新增数据
基本语法：insert into 表名[(字段列表)] values(值列表);


主键冲突(Duplicate key)
当主键存在冲突的时候，可以选择性地进行处理，进行更新和替换
更新操作：insert into 表名[(字段列表)]
values(值列表) on duplicate key update 字段=新值;
替换：replace insert into 表名[(字段列表)] values (值列表);




表创建的高级操作
从已有表创建新表(复制表结构)：create table 表名 like 数据库.表名;


蠕虫复制：先查出数据，然后将查出的数据新增一遍
insert into 表名[(字段列表)] select 字段列表/* from 数据表名;
蠕虫复制的意义
从已有表拷贝数据到新表中
可以迅速地让表中的数据膨胀到一定的数量级，用来测试表的压力以及效率


更新数据
基本语法：update 表名 set 字段=值[where条件];
高级语法：update 表名 set 字段=值[where条件] [limit 更新数量];


删除数据
delete from 表名 [where条件] [limit 数量];
truncate 表名; -- 先删除该表，后新增该表


查询数据
基础语法：select 字段列表/* from 表名 [where条件];
完整语法：select [select 选项] 字段列表 [字段别名]/*from 数据源 [where条件子句] [group by字句] [having 子句] [order by子句] [limit 子句];




高级数据查询
select选项：select对查出来的结果的处理方式
all:默认的，保留所有的结果
distinct:去重，查出来的结果，将重复给去除
字段别名
字段名 [as] 别名;
数据源：单表数据源、多表数据源、查询语句
单表数据源:select * from 表名;
多表数据源:select * from 表名1，表名2，...;
子查询：select * from (select 语句)as 表名;


where子句：返回结果0或1,0代表false，1代表true
判断条件
比较运算符：>、<、>=、<=、!=、<>(不等于)、=、like、between、and、in/not in
逻辑运算符：&&(and)、||(or)、！(not)


group by子句
基本语法:group by 字段名;
统计函数:
count():统计分组后的记录数，每一组有多少记录
max():统计每组中最大的值
min():统计每组中最小的值
avg():统计平均值
sum():统计和


多字段排序
group_concat(字段);
回溯统计
with rollup;
having字句
与where子句一样，是进行条件判断的
having能够使用字段别名
order by子句
基本语法：order by 字段名 [asc|desc]


limit子句
方案一：只用来限制长度，即数据量：limit 数据量;
方案二：限制起始位置，限制数量：limit起始位置，长度;
limit offset,length;
length:每页显示的数据量，基本不变
offset = (页码-1)*每页显示量




连接查询（join）分类：内连接、外连接、自然连接、交叉连接
使用方式：左表 join 右表
交叉连接（cross join）
基本语法：左表 cross join 右表;-- 等价于：from 左表,右表;
内连接（[inner] join）

基本语法：左表 [inner] join 右表 on 左表.字段=右 表.字段;
on表示连接条件


外连接（outer join）

left join：左外连接（左连接），以左表为主表

right join：右外连接（右连接），以右表为主表

基本语法：左表 left/right join 右表 on 左表.字段=右表.字段;
自然连接（natural join）

自然内连接：左表 natural join 右表;

自然外连接：左表 natural left/right join 右表;

模拟自然连接：左表 left/right/inner join 右表 using(字段名);




增加外键
创建表的时候增加外键：在所有的表字段之后，使用foreign key(外键字段) references 外部表(主键字段)
在新增表之后增加外键：修改表结构，使用alter table 表名 add [constraint 外键名字] foreign key(外键字段) references 父表(主键字段);
修改外键&删除外键

alter table 表名 drop foreign key 外键名;




外键条件
外键要存在，首先必须保证表的存储引擎是innodb

列类型必须与父表的主键类型一致
一张表中的外键名字不能重复
增加外键的字段数据已经存在，必须保证数据与父表主键要求对应


外键约束
有三种约束模式

district：严格模式（默认的）
cascade：级联模式
set null：置空模式


语法：foreign key(外键字段) references 父表(主键字段) on delete 模式 on update 模式;






视图（view）：是一种有结构，但是没结果的虚拟表




创建视图
基本语法：create view 视图名字 as select 语句;
创建单表视图：基表只有一个
创建多表视图：基表来源至少两个




查看视图

show tables [like] / desc 视图名 / show create table 视图名;


修改视图
alter view 视图名字 as 新的select语句;




删除视图


drop view 视图名字;




新增数据

多表视图不能新增数据
可以向单表视图插入数据，但是视图中包含的字段必须有基表中所有不能为空、或没有默认值的字段
视图是可以向基表插入数据的
删除数据
多表视图不能删除数据
单表视图可以删除数据


更新数据

更新限制：with check option;


视图算法：系统对视图以及外部查询视图的select语句的一种解析方式
视图
算法分三种
undefined：未定义（默认的）

temptable：临时表算法

merge：合并算法
算法指定：
在创建视图的时候create algorithm=指定算法 view 视图名字 as select语句;




数据备份与还原
方式：


数据表备份


单表数据备份
SQL备份
增量备份
数据表备份

存储引擎：innodb、myisam innodb：只有表结构，数据全部存储到 ibdata1 文件中


myisam：表、数据和索引全部单独分开存储


单表数据备份
备份：
select */字段列表 into outfile 文件所在路径 from 数据源;
高级备份：
select */字段列表 into outfile 文件所在路径 fields 字段处理 lines 行处理 from 数据源;
fields：
字段处理，enclosed by（默认''）、terminated by（默认'\t'）、escaped by（默认'\\'）


lines：
行处理，starting by（默认''）、terminated by（默认'\r\n'）




单表数据备份


数据还原：

load data infile 文件所在路径

into table 表名[(字段列表)] 

fields 字段处理 

lines 行处理;


SQL备份

备份：mysqldump.exe

mysqldump/mysqldump.exe -hPup 数据库名字 [数据表名字1 [数据表名字2...]] > 外部文件路径
整库备份：

mysqldump/mysqldump.exe p 数据库名字 > 外部文件路径




SQL还原数据
方案一：使用mysql.exe客户端还原

mysql.exe/mysql -hPup 数据库名字 < 备份文件目录


方案二：使用SQL指令还原

source 备份文件所在路径
增量备份
备份的是系统日志文件






事务transaction


事务操作分两种：自动事务（默认）、手动事务




手动事务的操作流程
开启事务：start transaction;
进行事务操作

关闭事务

提交事务：commit;同步数据表，表示操作成功

回滚事务：rollback;直接清空日志表，表示操作失败


事务操作原理：
事务开启之后，所有的操作都会临时保存到事务日志，而事务日志只有在得到commit命令才会同步到数据表，其他任何情况都会清空，比如rollback、断电、断开连接




回滚点
设置回滚点语法：savepoint 回滚点名字;

回到回滚点语法：rollback to 回滚点名字;


自动事务处理

show variables like 'autocommit';
关闭自动提交：set autocommit=off/0;


事务的四大特性：ACID

A：Atomic，原子性
C：Consistency，一致性

I：Isolation，隔离性

D：Durability，持久性
锁机制




变量


系统变量


autocommit、auto_increment_increment、character_set_client、character_set_connection、character_set_results……
查看系统变量：show variables;

查看具体变量值：select @@变量名;
修改系统变量

会话级别：set 变量名=值;或者 set @@变量名=值;

全局级别：set global 变量名=值;




自定义变量
定义变量：set @变量名=值;
查看变量：select @变量名;
变量赋值


边赋值，边查看结果：select @变量名=字段名 from 数据源;


只有赋值，不看结果：select 字段列表 from 表名 into 变量列表;




要素：事件类型、触发时间、触发对象
事件类型：增删改，即insert、delete、update三种类型
触发时间：前后，即before、after
触发对象：表中的每一条记录
创建触发器
基本语法：


create trigger 触发器名字 触发时间 事件类型 on 表名 for each row 
begin
-- 触发器内容;
end
自定义符号




查看触发器

查看所有触发器，或者模糊匹配：show triggers [like 'pattern'];
查看触发器创建语句：show create trigger 触发器名字;
触发器表：information_schema.triggers
使用触发器
修改触发器&删除触发器

drop trigger 触发器名字;
触发器记录


OLD代表旧记录，NEW代表新记录，删除的时候是没有NEW的，插入的时候是没有OLD
使用方式：old.字段名/new.字段名




代码执行结构


代码执行结构有三种：顺序结构、分支结构、循环结构


分支结构：
基本语法：

if 条件判断 then
-- 满足条件要执行的代码;
else
-- 不满足条件要执行的代码;
end if;


循环结构：while循环

while 条件判断 do

-- 满足条件要执行的代码
-- 变更循环条件

end while;


循环控制

iterate（迭代）、leave（离开）
使用方式：iterate/leave 循环名字;
循环名字:while 条件 do
-- 循环体

-- 循环控制
iterate/leave 循环名字;
end while;






函数


函数分为两类：系统函数和自定义函数


系统函数：
substring：字符串截取，以字符为单位
char_length：字符长度

length：字节长度

instr：判断字符串是否在某个具体的字符串中存在，存在返回位置，失败返回0
lpad：左填充，将字符串按照某个指定的填充方式，填充到指定长度，长度单位是字符

insert：找到目标位置，把指定长度的字符串替换成目标字符串
strcmp：字符串比较




自定义函数

函数要素：函数名、参数列表（形参和实参）、返回值、函数体（作用域）

创建函数：

create function 函数名([形参列表]) returns 数据类型
begin
-- 函数体

-- 返回值：return 类型;
end
调用方式：select 函数名([实参列表]);


查看函数

查看所有函数：show function status [like 'pattern'];
查看函数的创建语句：show create function 函数名;
修改函数&删除函数

drop function 函数名;
函数参数

function 函数名(形参名字 字段类型) returns 数据类型
作用域

全局变量：使用set关键字定义，使用@符号标志

局部变量：使用declare关键字声明，没有@符号




存储过程procedure


创建过程：
create procedure 过程名字([形参列表])
begin
-- 过程体


end
查看过程：
查看所有过程：show procedure status [like  'pattern'];

查看过程创建语句：show create procedure 过程名;




存储过程


调用过程：

call 过程名称([实参列表]);
修改过程&删除过程：


drop procedure 过程名;
过程参数：
in：数据只是从外部传入给内部使用，可以是数值，也可以是变量

out：只允许过程内部使用，不用外部数据，给外部使用的
inout：外部可以在内部使用，内部修改也可以给外部使用
基本使用：


create procedure 过程名(in 形参名 数据类型,out 形参名 数据类型,inout 形参名 数据类型);


调用：out和inout类型的参数必须传入变量，而不能是数值
正确的调用：传入变量