Java二十五篇： 数据库
不忘初心110
前提常识：
内存条 临时记忆 速度比较快 不能永久保存数据
硬盘 文件夹 文件 U盘 持久化数据信息 可以对于数据进行增删改查操作（能够同时操作大批量数据）
市面上常见的数据库
Oracle 收费型数据库 性能高 服务额外收费 贵
MySql 开源免费数据库 Sun公司 Oracle收购 6.0以后开始收费
SqlServer 微软数据库 收费数据库
DB2 IBM公司提供数据库 收费的 银行系统
SQLite 嵌入式小型数据库 移动端 Android系统
分类：四大类
1DDL(Data Definition Language)数据定义语言
用来定义数据库对象：数据库 表 列 Create
2DML(Data Manipulation Language)数据操作语言
对于数据的增删改 insert delete update
3DQL(Data Query Language)数据操作语言
对于数据的查询 select
4DCL(Data Control Language)数据控制语言
定义数据库访问权限 定义新的用户及权限 密码等等
Grant
数据库四大类的语言
DDL:CRUD create drop alter show
操作数据库
1.创建
*创建数据库
createdatabase 数据库名称;
*先判断是否存在 不存在才创建
createdatabaseifnotexists 数据库名称;
*边创建边确定数据库编码集
createdatabase 数据库名称 characterset utf8;
createdatabaseifnotexists 数据库名称 characterset utf8;
2.查询
showdatabases;查询所有的数据库
showcreatedatabase 数据库名称; 查询某个数据库的编码集
3.修改
alterdatabase 数据库名称 characterset 新的字符编码集;
*修改原有数据库的字符编码集 （在录入数据前改好）
4.删除
dropdatabase 数据库名称;
*删除数据可
dropdatabaseifexists 数据库名称;
*先判断是否存在 再删除
5.使用数据库
use 数据库名称;
*操作数据之前 先确定数据库
selectdatabase();
*查询当前使用的数据库名
操作表
6.创建表（同时应该创建列）
createtable 表名(列名1 数据类型1,列名2 数据类型2,.。。);
如果定义varchar 就一定要在后面小括号中 定义长度 varchar(255)
int、double、date(yyyy-MM-dd)、dateTime(yyyy-MM-dd HH:mm:ss)
char、float
复制一张表
createtable 新表名 like 旧表名;
7.查询表
showtables;
*查询当前数据库中所有的表
desc 表名;
*查询当前表的结构
8.修改
altertable 表名 renameto 新表名;
*修改表名
altertable 表名 add 列名 类型;
*添加一列
altertable 表名 change 列名 新列名 新类型;
*修改列名
altertable 表名 modify 列名 新类型;
*修改列的类型
9.删除
altertable 表名 drop 列名;
*删除表中列
droptable 表名;
*删除表
droptableifexists 表名;
*先判断 再删除
DML：数据的增删改操作 insert into delete update
1.添加数据
insertinto 表名(列名1,列名2,。。。) values (值1,值2,。。。);
注意：
列名要和值一一对应
除了形式为数字的值 其他都应该加''或者""
如果整行添加 可以省略列名 inster into 表名 values(值1,值2,。。。);
如果有自增列
**insertinto 表名 values(null,值2,值3,。。);
insertinto 表名 (列名2,列名3,。。。) values(值2,值3,。。。);
2.删除操作
deletefrom 表名;
删除整张表
deletefrom 表名 [where goodName = "笔记本"];
注意：
做删除操作一定要编写删除条件 否则整表删除
如果确实要删除整表信息 truncatetable 表名; 删除整表 重新复制一张原表结构 效率高
deletefrom 表名 where goodName = "笔记本"orid = 1;
多行删除
3.修改数据
update 表名 set 列名 = 值;
修改所有的行此列的值
update 表名 set 列名 = 值 where 条件;
update goods set goodName = "书包"where goodName = "文具盒";
同时改变多行中某一列的值 可以在条件中添加多行的查询
update goods set goodName = "书包"where goodName = "文具盒"orid = 3;
同时改变多列的值
update goods set goodName = "书包",price = 20.8 where...
批量新增
INSERTINTO
goods
VALUES
(2,'钢笔',5,1)
,(3,'作业本',0.5,1)
,(4,'文具盒',10.6,1)
,(5,'篮球',58.8,2)
,(6,'羽毛球',3,2)
,(7,'羽毛球拍',80,2)
,(8,'面包',5,3)
,(9,'牛奶',4.5,3)
,(10,'辣条',0.5,3);
DQL:数据的查询操作 select
select * from 表名;
* 查询整张表
selectid,goodName,price from 表名;
* 查询表中的某些列
select 列1,列2,列3,...from 表名 [where 条件];
* 查询满足条件的行中的某些列
-- 查询中去除列中重复的数据
SELECTDISTINCTtypeFROM goods;
使用去重查询 不应该再和其他列一起操作
-- 如果希望以某个值代替price列中的null
SELECT
idasID,goodName [as] 商品名,IFNULL(price,0)+2as 价钱
FROM
goods;
DCL：对于数据库用户权限的相关设置
查询数据库用户：
USE mysql;
SELECT * FROM`user`;
新增用户：
-- 新增数据库管理
CREATEUSER'用户名'@'localhost'IDENTIFIEDBY'密码';
修改密码
UPDATEUSERSETPASSWORD = PASSWORD('333') WHEREUSER = 'abc';
FLUSHPRIVILEGES; -- 刷新操作
-- 简化方案
SETPASSWORDFOR'abc'@'localhost' = PASSWORD('1234567');
当遗忘了root密码 如何重置root密码
1.使用管理员方式打开CMD-> net stop mysql 停止mysql服务
2.使用无验证方式登入mysql mysqld --skip-grant-tables
3.打开新的cmd窗口 直接输入mysql 回车 登入mysql mysql>
4.use mysql;
5.UPDATE USERSETPASSWORD = PASSWORD('新密码') WHEREUSER = 'root';
6.关闭两个窗口
7.开启服务 net start mysql
8.使用root及新密码登陆
删除用户
-- 删除用户
DROPUSER'用户名'@'localhost';
权限管理：
查询权限
-- 查询权限
SHOWGRANTSFOR'用户名'@'localhost';
-- 列表式赋权限
GRANTSELECT,UPDATE,INSERT,DELETEON test1.users TO'abc'@'localhost';
-- 列表式移除权限
REVOKESELECT,UPDATE,INSERT,DELETEON test1.users FROM'abc'@'localhost';
-- 拥有所有权限
GRANTALLON *.* TO'abc'@'localhost';
-- 删除所有权限
REVOKEALLON *.* FROM'abc'@'localhost';
** grant引导赋权限的语句 revoke引导删除权限语句
** 使用列表时赋予的权限 只能使用列表式删除 使用all赋予的权限 只能使用all删除
约束
约束
概念：对表中的数据进行限定，保证数据的正确性、有效性、完整性。
分类：
主键约束+自增列
非空约束
唯一约束
外键约束
主键约束：
给表中每一行添加唯一标识 一般设为int类型 进行自增
* createtable 表名(
idint primary key-- 设置id列为本表的主键列
,namevarchar(255) -- 姓名
,stuType int-- 专业
)
效果：不允许重复 不能为null 非空且唯一
删除表中的主键列
ALTERTABLE students DROP PRIMARY KEY;
在表创建后设置主键列 **设置前 要保证内部已有数据是符合要求的
ALTERTABLE students MODIFY stuId int PRIMARY KEY;
添加主键 设置自增 ** 自增列只能添加在主键上
&&&& createtable 表名(
idint primary key auto_increament -- 将主键设为自增
,namevarchar(255) -- 姓名
,stuType int-- 专业
)
单独添加自增
ALTERTABLE students MODIFY stuId int auto_increment;
有主键自增的表添加数据 自增列的值用NULL代替
INSERTINTO
students
VALUES(NULL,'王五',1);
删除自增
ALTERTABLE students MODIFY stuId int;
非空约束：
在设计表时 给表中某些列设置非空约束
* createtable 表名(
idint primary key-- 设置id列为本表的主键列
,namevarchar(255) notnull-- 姓名 设置非空约束
,stuType int-- 专业
)
效果：不允许重复 不能为null 非空且唯一
单独添加非空约束
ALTERTABLE students MODIFY stuName VARCHAR(255) NOTNULL;
删除非空约束
ALTERTABLE students MODIFY stuName VARCHAR(255);
唯一约束：
在设计表时 给表中某些列设置唯一约束
* createtable 表名(
idint primary key-- 设置id列为本表的主键列
,namevarchar(255) notnull-- 姓名 设置非空约束
,tel varchar(255) unique-- 电话号码 设置唯一约束
,stuType int-- 专业
)
单独添加唯一约束
ALTERTABLE students MODIFY stuTel VARCHAR(255) UNIQUE;
ALTERTABLE students ADDUNIQUE(stuTel);
删除唯一约束 通过删除这个列的索引(index)来删除唯一约束
ALTERTABLE students DROPINDEX stuTel;
-- ALTER TABLE students MODIFY stuTel VARCHAR(255); -- 错误方式外键约束：
至少涉及到两张表 描述表与表之间列的取值的依赖关系
设置 从表 的某一列的外键 指向 主表的主键列
* createtable 表名(
idint primary key-- 设置id列为本表的主键列
,namevarchar(255) notnull-- 姓名 设置非空约束
,tel varchar(255) unique-- 电话号码 设置唯一约束
,stuType int-- 专业
,CONSTRAINT 外键名称 FOREIGNKEY(从表列) REFERENCES 主表(主键列);
)
-- 设置从表的某个列的外键
ALTERTABLE students ADDCONSTRAINTtype【外键名称】 FOREIGNKEY(stuType) REFERENCES classType(classId);
-- 删除外键
ALTERTABLE students DROPFOREIGNKEY 外键名称;
数据库的设计
表与表之间的关系 外键约束
* 一对一
一对一的情况一般建议描述在一张表中
例如：学生和学号 人和身份证
如何描述一对一的关系：
任意一方添加外键约束 指向另一方的主键
（无所谓方向）
* 一对多
建议拆成两张或更多表
例如：学生（多）对班级（一） 人（多）对家庭住址（一）
给多表的列添加外键 指向一表的主键列（必须有方向性）
* 多对多
建议添加第三张中间表
例如：玩家（多）对 游戏（多）
添加一张表 这张表至少包含两个列 这两个列分别添加外键 指向另外两张多表的主键
（演示）
如何使用设计软件来添加外键关系
选择从表右键选择设计 在上方选择外键
1.起个外键名称 2.当前从表的要做主外键的列 3.数据库的名称 4.主表名 5.主表主键 后面是否级联
范式（设计多张表）
概念：设计数据库时 应该遵循的一些规范 要遵循后面的范式要求 就必须遵循前面的所有范式要求 各种范式呈递次规范 满足越高的范式的数据库 冗余越小 越优秀
目前数据库遵循六大范式：第一范式1NF 第二范式2NF 第三范式3NF 巴斯-科德范式BCNF 第四范式4NF 第五范式5NF 123845
第一范式1NF：（各列不可再拆分）
数据库表的每一列都是不可分割的原子数据项 而不能是集合 数组 记录等非原子数据项
第二范式2NF：（每张表应该选择一个合适的主键）
非码属性必须完全依赖于候选码（主键）（在1NF基础上消除非主属性对主码的部分函数依赖）
间接依赖到主键 满足二范式
第三范式3NF：（其他列能够直接依赖于主键）
任何非主属性不依赖于其他的非主属性（其他列只依赖于主键 包括间接依赖都不行）（在1NF和2NF基础上）
第三范式就是属性不依赖于其它非主属性，也就是在满足2NF的基础上，任何非主属性不得传递依赖于主属性。
事务 一次要执行的一系列操作的总称
通过添加事务 可以控制这一系列操作的成功或失败 有一个sql失败则全部还原到开启事务之前 只有全部的sql成功 才会正常提交（提交还是回滚的主动权在程序员手里）
语法：
STARTTRANSACTION;开启事务（在数据库编码中）
事务中的一系列操作
如果其中的操作都符合需求 则 可以使用COMMIT; 真正的去正常提交更新
如果其中的操作有不符合需求的 则可以使用ROLLBACK;使数据回滚到开启事务之初。
事务提交的两种方式：
自动提交：MySql 每条SQL就是一个事务 默认开启和提交
手动提交：Oracle 需要显式开启事务和提交事务
查询事务提交方式：
select @@autocommit;
1:自动提交
0:手动提交
SET @@autocommit =0;-- 设置提交方式为手动提交
SELECT @@autocommit;-- 查询提交方式
验证：直接执行修改语句 会发现数据库中数据并没有被修改
只能通过添加事务及提交操作 才能实现修改
自动提交的SQL 只需要添加事务及提交事务 即可变为手动提交 不需要单独修改提交方式
事务四大特性
1.原子性：我们定义好的一个事务 就应该是不可分割的最小操作单位 要么同时成功 要么同时失败
2.持久性：只有当事务执行了commit或者rollback后 数据才会持久的保存在数据库中
3.隔离性：事务与事务之间是相互独立的（理想状态）
4.一致性：事务操作前后 数据总量是不变的 1》不可重复读 2》数据完整性
读取异常：查询 当一方开启事务操作数据后 没有提交或者回滚之前 查询到的数据和真实数据是不同的 这就造成了读取异常。
1.脏读:一个事务读取到另一个事务还没有保存的数据
2.不可重复读：在同一个事务中两次读取到的信息不一致
3.幻读：一个事务在操作增删改（DML）数据时 另一个事务添加了一条数据 则第一个事务查询不到自己的修改 出现幻觉
事务隔离级别：
1.read uncommited 读未提交
问题：脏读、不可重复读、幻读
2.read commited 读已提交 （oracle默认隔离级别）
问题：不可重复读、幻读
3.repeatable read 可重复读 （MySql默认隔离级别）
问题：幻读 （暂不演示）
4.serializable 串行化
解决以上所有问题 暂停其他事务 效率最低
查看隔离级别
select @@tx_isolation;
设置隔离级别
set global transaction isolation level 隔离级别名称;
修改完隔离级别后 要重新创建连接
锁
悲观锁
事务中的业务大概率会出现并发异常时 我们处于一种悲观状态 可以使用悲观锁
先关闭默认提交模式
SET @@autocommit =0;-- 设置提交方式为手动提交
STARTTRANSACTION;
-- 加锁
select money from`Users`whereid = 1forupdate;
update`Users`set money =50000whereid = 1 ;
-- 在关闭这个事务之前 其他的事务依然查询的是数据库中真实的数据 （没有脏读）
-- 对比事务隔离级别serializable 在第二个事务中查询也会被暂停 而使用悲观锁 第二个事务是可以查询的
-- 如果其他事务使用的是DML（增删改）操作 会被暂停 出于等待 直到第一个事务提交或者回滚 其他事务才能进行（DML）
乐观锁
评估事务中发生并发异常的可能性比较低 但又不完全保证不会发生 使用乐观锁
乐观锁是通过SQL本身的语法去进行校验
-- 在修改之前查询一下数据 在修改的同时回去判断这个值和刚才查询到的数据是否一致 如果一致则继续执行更新 否则不更新
SELECT MONEY FROMUSERSWHEREID = 1;
UPDATEUSERSSET MONEY = 500WHEREID = 1AND MONTY = 前面语句查出来的值;
合成一句
UPDATE`Users`SET money = 500
WHEREid = 1
AND`Users`.money = (SELECT m1 FROM (SELECT money AS m1 FROM`Users`WHEREid = 1 ) AS t1);
Every derived table must have its own alias 给虚拟表命名
总结：
1、对数据库要有一定的认知：关系型数据库和非关系型数据库
2、数据库的四大类型语言要有一定的了解
1DDL(Data Definition Language)数据定义语言
用来定义数据库对象：数据库 表 列 Create
2DML(Data Manipulation Language)数据操作语言
对于数据的增删改 insert delete update
3DQL(Data Query Language)数据操作语言
对于数据的查询 select
4DCL(Data Control Language)数据控制语言
定义数据库访问权限 定义新的用户及权限 密码等等
Grant
3、在设计数据库的时候要应用：约束和范式，考虑表与表之间的关系，是一对一、一对多、多对多
怎么加索引才合适、外键等，先有一定的设计才去建表创建属性
4、在数据库的层面要对事务一定的认知和理解
要知道什么是事务隔离级别、什么是脏读、不可重复读、幻读和串行化，这也是面试常常被问到的。
5、对数据库的锁要有自己的理解：悲观锁、乐观锁
事务中的业务大概率会出现并发异常时 我们处于一种悲观状态 可以使用悲观锁
评估事务中发生并发异常的可能性比较低 但又不完全保证不会发生 使用乐观锁
