Java与数据库基础-优快云博客

1、前置知识

1.1 java的版本介绍

JavaSE Java Standard Edition
Java Java的标准版本，提供了很多的核心API；lang util io 包等

javaEE Java enterprise edition
java 的企业版本，针对网络应用做出了很多的扩展API , servlet Listener Filter Jsp

javaME Java Micro endition
java 的微小版本，适合硬件设备的嵌入设备的编程。例如，机顶盒，打卡机等。有用户界面和网络编程，但是整体的性能比较差。
现在机顶盒一般用的是Android

1.2 web开发的常见模式

c/s client/server

客户端/服务器模式：
缺点：推广费劲，软件的开发成本高。
优点：客户端可以分担服务器的压力

B/s browser/server

浏览器/服务器模式
缺点：对服务器的要求比较高，压力比较大。
优点：开发成本低，开发的周期较短，无序下载额外的内容。

1.3数据的存储方式

运行内存存储：
运行过程中的存储方式：
读写速度块，但是不够持久

磁盘内存存储：
将数据存储到硬盘之中
读写速度慢，但是持久化存储，成本更加低。

能存储的类容
文本文件：无规则，不方便存储
excle：有规则，但是他面向的是用户，不能直接使用程序或者语言直接读取文件。
数据库：数据库的内部格式是二维表格，一种有格式的持久话的存储方式。面向的程序，不能直接对其中的数据进行操作，只能通过sql语句进行对数据的操作，更加适合web应用存储数据!

1.4 数据库的分类

数据库的存储方式：

数据库内部格式就是二维表格！一种有格式的持久化存储方式！
数据库面向的不是用户！必须通过SQL语句进行数据库的操作！更加合适web应用存储数据！

数据库的分类：

网状结构数据库：以节点形式存储数据和访问数据
层次结构数据库：IBM[IMS]。定向有序的树状结构实现存储和访问。
关系结构数据库：Oracle、MySQL、DB2、SQL Server，以表格(Table)形式存储，多表之间建立关联关系，通过分类、合并、连接、选取等方式实现访问。
非关系型数据库：MongoDB、Redis，使用哈希表，表中以键值（key-value）的方式实现特定的键和一个指针指向的特定数据
ElastecSearch

数据库管理系统(DBMS)：指的是一种操作和管理数据库的大型软件，用于建立、使用、维护数据库，对数据库进行统一的管理和控制，保证数据库的安全性和完整性。

数据库文件不能直接操作，如果想要管理数据库文件，就需要使用数据库管理系统（软件 DataBase Management System）

数据库管理系统的分类

关系型数据库的管理系统:Oricle ,MySQL 、SQLLite

非关系型数据库管理系统：Redis MongoDB es

常见的数据库管理系统：

Oracle：可以运行在UNIX、Windows等主流操作系统，支持所有的工业标准，并获得了最高级别的ISO标准安全性认证。
DB2：IBM公司的，满足中大公司的需要
SQL Server：微软退出的。
SQLLite：手机端的数据库
Mysql：免费、适合中小型企业

关系型数据库的常见存储单位

库：最大的数据库的容器单位，内部包含表。
表：一类数据库的集合，对应的是Java的类。
行(元组):一个数据实体，对应的是Java的具体对象。
列(域)：真正用于存储数据属性的（类型）！对应的是Java的具体对象的属性。
注：在创建库表的时候，应该根据对应的类创建对应的库表，类的属性要与表的列值完全一致。

2、sql语句的介绍

sql概念：结构化查询语言，用于存取数据、更新、查询和管理关系数据库系统的程序设计语言。

各数据库厂商都支持ISO的SQL标准。普通话
各数据库厂商在标准的基础上做了自己的扩展。方言
经验之谈：通常对数据库的操作为C(create） R(Read) U(update) D(delete) 。即增删改查。

SQL语句的分类：

数据定义语言DDL：create, drop,alter （针对的是数据库以及数据库中的表）
create （创建） drop （删除） alter （修改）

数据查询语言DQL: select （针对的是关于表中数据的操作）
数据操作语言DML：update 、delete 、inset （针对的是表中的数据）
update（修改）、delete（删除）、inset（插入）

事务处理语言TPL：commit rollback（用于操作数据库的事务的语言）
数据控制语言DCL：grant 、revoke
基本命令：

服务器的默认IP地址是本机的IP地址：127.0.0.1
数据库的默认端口号：3306
以上两个在登录的时候通常是不写的
#1、开启和关闭服务：
net start/stop mysql57(服务器的名称，我的是MySQL57);
#2、登录连接MySQL
mysql -h 服务器的IP地址 -u用户名 -p密码 -P端口号
#3、退出MySQL
exit

3、sql语句的使用

3.1DDL 操作数据库以及表格 create, drop,alter

3.1.1操作数据库：

添加一个新的数据库：
create database 数据库的名称；
create database 数据库的名称 character set 编码格式

修改数据库
alter database 数据库的名字 character set 编码格式

查看数据库
show databases (查看所有的数据库)
select database() （查看当前使用的数据库）

切换数据库
use 数据库的名称

删除数据库
drop database 数据库的名称

3.1.2操作数据库表

创建数据库表格

create table 表名（

列名类型约束 comment “列的含义” ， //列与列之间用，隔开

列名类型约束 comment “列的含义” //约束以及comment可以不用写。

）charset= utf8 ;

修改数据库表格都是关于修改的关键字（alter）

表中添加列

alter table 表名 add 列名类型

表中删除列

alter table 表名 drop 列名

修改

修改列名

alter table 表名 change 原列名新的列名新的类型 （不推荐修改列的类型，因为原来的表中如果存在数据，那么修改数据类型容易造成错误）

修改列的类型

alter table 表名 modify 列名新的数据类型

修改表名

alter table 旧的表名 rename 新的表名

查看数据库表

desc
desc 表名 查看当前的表中包含的列

show
show tables 查看当前的库中包含哪些表

删除数据库表

drop table 表名

3.1.3数据库中的常用数据类型

数字类型

int
double(M, D)(多少位,小调点后面的位数) 注意有效15位！
decimal(M , D )(多少位,小调点后面的位数)注意有效25位！

字符类型

char(0-255)
varchar(0-65535)

时间类型

date 年-月-日

time 时:分:秒

datetime 年-月-日时:分:秒

timestamp 自动时间赋值年-月-日时:分:秒时间戳

时间格式就是字符串！真正开发存储时间字符串存时间戳

3.2DML 操作的是数据 insert 、update 、 delete

插入数据

insert into 表名 (列名，列名，…) value/values (值,值,值),(值,值,值);
value 一行数据
values 多行数据
列名 — 值需要一一对应（位置，类型）

insert into 表名 value/values(值,值,值),(值,值,值);
全部插入！顺序按照之前创建表的列顺序！

修改数据

update 表名 set 列 = 新值 ,列 = 新值; 全表修改
update 表名 set 列 = 新值 ,列 = 新值 where 列 = 值;

删除数据

delete from 表名; 全表删除，但是不会改变他的结构格式，例如自增的不会清零
delete from 表名 where 条件; 条件删除
**truncate table 表名; **
该删除分为两个步骤：
删除表格
创建一个和他结构一样的表，他的一切都是新的

3.3DQL

3.3.1DQL查询介绍

查询的基础介绍

介绍：数据库执行DQL语句不会对数据进行改变，而是让数据库发送一个结果集给客户端，不会影响原来的数据，返回的是一张虚拟的表格。
执行的步骤：查询时，会在内存中生成一张临时的数据表，将满足要求的数据进行记录
首先，光标会移动到首行
进行判断，看数据是否匹配
若是不满足要求，则跳转下一行
将所有满足条件的数据返回到客户端

DQL的基础语法

select * from 表名 ；查询全表
关键字：
where 条件 ;
group by 列名；对结果按照列名进行分组
order by 列名；对结果进行排序 asc(升序，默认) / desc(降序)
having 条件；
limit 开始行数，查询的行数；

基本查询（全表查询）

查询所有列： select * from 表名 ；
查询指定的列： select 列名 from 表名；
条件查询
条件查询的基础语法：select * from 表名 where 条件 ；

逻辑符号：

= 、!= 、<>(这是不等于)、 < 、 >、 <=、 >=
between 值 and 值 ；两值之间的所有
in ( set ) ; 取是set的值，set值可以是多个
is null ；是null
and ；
or ；
not ；注：is not null 不是空，不能是not is null
注意：null 不能用 = 进行比较，null代表的是未知的数，不一定是字符null 。在orical 中，只要是空的地方，会自己填入null
SELECT * FROM stu WHERE sid <> 's_1001' AND sid <> 's_1002' AND sid <> 's_1003' ;
SELECT * FROM stu WHERE sid NOT IN ('s_1001' ,'s_1002' ,'s_1003') ;
SELECT * FROM stu WHERE sid NOT BETWEEN 's_1001' AND 's_1003';
模糊查询

关键字：like
关键的占位符：**%**代表可变的长度 _ 代表单个
#例如张姓开头的所有人
select * from stu where s_name like  '张%' ;
#例如姓名中含有   蒯   的所有人
select * from stu where s_name like '%蒯%' ;
分支查询

关键字

case

when 条件 then 值

when 条件 then 值

else 值

end as 别名

注意：分支查询也是查询，在 from前，即分支查询写在列的位置！可以添加原数据表中不存在的数据！也是开发中比较常用的查询手段！
时间查询

常用的函数：

sysdate(); 当前系统时间（日、月、年、时、分、秒）now() 函数也是获取当前系统的时间
curdate(); 获取当前日期
curtime(); 获取当前时间
week(date); 获取指定日期为一年中的第几周
year(date) ; 获取指定日期的年份
hour(Time); 获取指定时间的小时值
minute(Time); 获取时间的分钟值
datediff(date1,date2) ; 获取DATE1 和 DATE2 之间相隔的天数 ,该方法比较重要
adddate(date , n) ; 计算DATE 加上 N 天后的日期

时间函数的使用：

他放在列的位置，没有后续的追加，直接输出时间

也可以作为值

亦可以作为条件
#做为值进行逻辑运算
SELECT ename , 
ROUND(( DATEDIFF(NOW() ,hiredate ) / 365),1) AS workage FROM emp;
#作为条件进行查询
select * from 表名 where 列名 = 时间函数
数学函数：
ceil() ; 向上取整
floor() ; 向下取整
round() ; 四舍五入
聚合函数
sum() ;

avg() ;

max() ;

min () ;

count() ; 统计总的行数，计数时 null 值不计入
mod( 要取余的数 , 取余的数) ; 取余
mod(6 , 4)    结果为2
字段控制

关键字：

distinct ；去重

ifnull ( 列的名字，如果为null时的值) ；

数字类型+null = null ，因此有的需要去掉null值

as 列的别名 ；取别名

也可以用空格代表as

字段控制根据字面理解，用在对数据进行操作的时候

排序查询

排序的关键字 order by 列名 asc/ desc ;

asc 升序默认为升序
desc 降序

排序不依托于where，他和where的顺序为：

where在前，order by 在后。

比如在一个班级中，需要女生进行排队。

分组查询

关键字：group by 列名，列名 … ;

语法：select 分组列,聚合函数 from 表名 group by 列名;

作用：根据给定的条件，将数据分组。

顺序：

where 条件 group by 列名 order by 列名 asc/desc

分组条件查询

分组后的筛选，对数据进行过滤。
关键字：having 聚合函数
语法：group by 列 having 聚合函数逻辑运算符值;

限定查询

关键字：LIMIT 起始行，查询行数()；

分页查询的基础

数据库中的数据是从0开始的

查询的总结

查询中各个语句的顺序

SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组 HAVING 过滤条件 ORDER BY 排序列（asc|desc）LIMIT 起始行，总条数

3.3.2约束限制

实体约束

主键：主键就是唯一并且不为null的列

关键字：primary key

主键的分类：

自然主键：本身实体具备的属性，例如身份证号。
自定义主键：专门为了主键而创建，为了区分数据

主键的使用

主键约束

create table 表名(

主键名 int primary key

)

联合约束

create table 表名（

列名 int ,

列名 int，

primary key (列名,列名)

）

联合约束也可以只写一个列，作用和主键约束一样

唯一约束

概念：唯一（即不可以重复）但是可以为null的列

关键字：unique

和主键的区别：唯一约束可以为null

自增长

自动增长，给主键数值列添加自动增长。从 1 开始，每次加 1。不能单独使用，必须和主键配合。

关键字：auto_increment
当主键是字符串类型时 varchar(64) primary key uuid
UUID 是通用唯一识别码（Universally Unique Identifier）的缩写，是一种软件建构的标准，亦为开放软件基金会开放软件基金会/1223731)组织在分布式计算分布式计算/85448)环境领域的一部分。其目的，是让分布式系统中的所有元素，都能有唯一的辨识信息，而不需要通过中央控制端来做辨识信息的指定。如此一来，每个人都可以创建不与其它人冲突的UUID。在这样的情况下，就不需考虑数据库创建时的名称重复问题。

域约束

非空约束
列名类型 not null unique
默认值约束
列名类型 default 'xxx’

不为负数约束
unsigned 默认数字类型有负数！加上此约束没有负数部分！

参照引用完整性约束（主外键约束）

主键在另外的表中作为外键，外键可以重复

主外键：

在引用的表中添加一列，这一列是被引用的主键（主键可以代表一行数据，即一个实例）引用的列，叫外键。

主外键约束：外键的内容不能超过主键的值

的添加主外键约束

直接添加：

constraint 约束名称（一个库内不能重复） foreign key (外键的列名) references 主表的表名(主键的列名)

外部修改：

alter table 子表名 add constraint 约束名称（一个库内不能重复） foreign key(外键) references 主表(主键)

添加约束的表：

被引用的表称为主表，引用表称为子表

创建的时候需要先创建主表，再创建子表，然后再添加约束，添加约束的时候，添加在子表中。

如果想强硬删除主表：需要设置外键失效！针对的是整个数据库。

设置失效 SET FOREIGN_KEY_CHECKS = 0 ，设置生效 SET FOREIGN_KEY_CHECKS = 1

3.3.3 多表查询

表的关系

一对一
有主外键，有子表、主表，必须添加主外键的约束，
需要给子表的外键添加唯一约束（unique）或者把外键当作子表主键使用

一对多
有主外键，有子表、主表，必须添加主外键的约束，
一对一，一对多都是先创建主表，再创建子表，添加约束条件，但是一对多的时候，外键可以重复，添加一个非空约束。

多对多
没有主表和子表的区分，但是需要创建一个中间表，用来联系两个表，中间表最少有三列，一个中间表主键，两个外键，外键最好添加非空约束。
纵向查询
将多个结果集垂直拼接

不要求有主外键的约束，但是列数以及列的类型需要一样

关键字：union /union all 前者会自动去重，后者不会
语法：
SELECT * FROM jiaoxue
UNION ALL
SELECT * FROM xingzheng
UNION ALL
SELECT * FROM caiwu;
横向查询

将多个结果集水平拼接到一起！拼接的实体之间需要‘互相认领’
必须存在主外键关系 , 多对多的类型需要联合中间表进行查询
语法：
99查询（ mysql 独有）：from 表1,表2…
内连接： 表1 [inner] join 表2 on 主 = 外
外连接
左外连接：表1 left [outer] join 表2 on
右外连接：表1 right [outer] join 表2 on

自然连接（了解就行）

因为自然连接自动匹配‘主外键相等’ 匹配的原则两个表同名的列！

我们不能保证只有主外键名称一致!

自然内连接表1 natural join 表2

自然外连接

自然左外连接表1 natural left join 表2
自然右外连接表1 natural right join 表2

子查询

将一个查询的结果集作为另一个查询的条件或者虚拟表

语法：

单行单列：可以在 WHERE 的条件中作比较

单行多列： WHERE 列名 IN （set）；因为有多个数据

多行单列 WHERE ANY ALL () --> 单行单列 MAX MIN

多行多列 FROM 虚拟表 FROM (SELECT * FROM xx) 别名

自连接

所谓的自连接！就是需要查询的数据在一个表中！需要同一个表在一次查询中使用多次！这就用到自己连接自己！这就是所谓的自连接！

多表查询总结

内连接和99查询查询的方式和结果一致，只是形式不同，两个相互连接！on判断主外键！如果再有表再join即可！以此类推！

内连接两个表是平等的！查询的结果根据两个表的数据决定！

外连接可以通过left/right指明逻辑上的主表！当逻辑主表的数据在另一个表不存在的时候！结果也会显示主表的全部数据！

小技巧：开发中一旦使用了外连接！整条sql语句的方向是一致的！

查询的总结：

了解表的结构

分析需求

提取要查询的列

分析查询的条件

常见情况：

结果 ——> 一张表

条件 – > 一张表单表查询

结果 ——> 一张表

条件 – > 多张表子查询

结果 ——> 多张表

条件 – > 一张表/多张表 /没有条件连接查询

结果 ——> 多张表

条件 – > 已有表中没有的数据连接查询+子查询

3.4TPL

事务：

概念：事务就是数据库中的一系列操作的动作的集合，事务有着高度的一致性。成功需要事务中的每一条数据都成功，而只要任何一条数据失败，则整个事务都失败。成功则会改变数据，若失败，则回滚到事务开始之前的状态，不会改变数据库的数据；

事务的类型：

自动提交

mysql自动默认的有开始事务以及自动提交；

MySQL默认就是自动提交事务！每句SQL语句前后都会嵌套事务！

自动开启！自动提交！报错自动回滚！

显示提交：

非自动提交！手动的开启一个事务,加入数据库操作动作（sql）,手动提交或者回滚
取消自动提交每次执行完操作动作序列以后,手动提交或者回滚！

操作命令：

查看自动提交的
show variables like ‘%autocommit%’

关闭自动提交、
set autocommit = off ;
执行事务的sql语句
commit 或者 rollback

手动开启一个事务
start transaction ;
执行sql语句
commit 或者 rollback

事务的作用：

事务提供了一个在失败情况下数据回滚的机会！当一个业务的一系列动作中有一个失败！整个事务状态失败！调用回滚！将数据回滚到开启事务之前的状态！保证数据不会出错！保证数据安全！
事务之间有隔离性！保证并发事务操作数据库同一个表的时候！互相不受干扰！

3.4.2事务的ACID

Atomicity(原子性) 一个事务中的一系列动作是不可切割的！
Consistency（一致性） 事务开启和事务结束的前后数据总状态一致！
Isolation(隔离性) 事务和事务之间互相隔离！互不干扰！注意：只有并发情况才会考虑隔离性！
Durability(持久性) 事务一旦提交或者回滚！数据持久化改变！

3.4.3事务的隔离性

事务的隔离级别

READ UNCOMMITTED: 赃读. 重复读、虚读都有可能发生。
READ COMMITTED: 避免赃读。重复读、虚读有可能发生。（oracle默认的）
REPEATABLE READ:避免赃读、重复读。虚读有可能发生。（mysql默认）
SERIALIZABLE: 避免赃读、不可重复读、虚读。

事务的隔离级别操作命令

查看隔离级别 select @@tx_isolation;
修改隔离级别 set transaction isolation level 四个级别

事务的隔离级别造成的影响

脏读：一个事务读取了另一个事务没有提交的数据！ - READ UNCOMMITTED会发生脏读！
不可重复读：一个事务读取了另一个事务提交的修改数据！- READ COMMITTED或者以及下级别会发生该问题！提升到REPEATABLE READ或者以上级别会避免问题！
虚读（幻读）：一个事务读取了另一个事务已提交的插入数据！REPEATABLE READ或者一下级别会发生问题！提升到SERIALIZABLE以上级别可以避免！

总结：只有脏读是数据错误！不可重复读和虚读属于不符合事务的一致性，但是事务已经提交事务！数据确实持久化改变！不可重复读和虚读不算错误！可以不用避免！

隔离级别越高!数据越安全！性能越差！
建议使用第二个隔离级别！

3.5备份以及恢复

数据备份

mysqldump -u用户名 -p密码要备份的数据库>磁盘位置 .sql
数据恢复
 mysql -u用户名 -p密码 要恢复的数据库<磁盘位置 .sql

3.6 DCL

介绍：维护账号和账号权限管理！

创建用户

CREATE USER zhangsan IDENTIFIED BY ‘123’;

用户赋权

授权： grant 权限(all,select) on 库名.表名（*全部） to ‘用户名’
查看权限：show grants for ‘账号’

用户权限撤销

撤销权限：revoke 权限 on 库.表 from ‘用户名’;

删除用户

drop user ‘用户名’