java学习笔记- 数据库

原创已于 2022-09-01 22:03:26 修改 · 418 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #java #学习

于 2022-08-29 21:34:46 首次发布

Java基础专栏收录该内容

13 篇文章

订阅专栏

本文介绍数据库的概念、分类及常见数据库类型，涵盖RDBMS与NoSQL数据库的特点，并深入讲解MySQL的安装、配置与使用方法，包括数据库的创建、管理、查询、事务处理等方面的知识。

数据库

进行数据存储的仓库、支持数据的持久化（永久性存储）

数据库的分类

RDBMS（关系型数据库）
NoSQL (Not Only SQL)非关系型数据库

RDBMS 常见的数据库种类

MySQL : 是一个开源的免费的数据库、支持商用。
Oracle : 商用收费
SQL Server : 微软的产品
DB2 : IBM 商用数据库

NoSQL 常见的数据库种类

Redis : 是一个键值对、支持高并发的、基于单线程的内存型非关系型数据库
Memcache : 基于键值对结构的非关系型数据库
Mongdb : 基于 文档结构 的非关系型数据库

MySQL 数据库的安装

下载地址

新建一个MySQL文件夹
将官方下载的压缩包下的bin已经bin的同级文件解压到MySQL文件夹
新建一个my.ini配置文件，并且编辑以下代码

[mysqld]

basedir = D:\SQL\MySQL
datadir = D:\SQL\MySQL\data
port = 3306

以管理员身份打开命令提示符，初始化数据库

cd /d D:\SQL\MySQL\bin
//查看mysqld 所有的命令
mysqld --verbose --help
//初始化 mysql 数据库
mysqld --initialize-insecure --user-mysql

将 mysql 做成开机自启

mysqld install 服务名

服务名可以不提供、如果不提供，默认是mysql

将 mysql 命令所在的位置，添加到环境变量 path 中（不是必须的，主要为了使用控制台）
在管理员模式下启动数据库

net start 服务名

链接MySQL 数据库

mysql -uroot -p -P -h

-u 设置用户名，超级管理员默认是root
-p 设置密码，如果密码为空可以不设置
-P 设置端口号，如果是3306，可以不设置
-h 设置IP ，如果是本机，可以不设置

MySQL数据库的卸载

停止 mysql数据库服务器

net stop 服务名

将mysql服务从window 中删除

mysql remove 服务名
或者
sc delete 服务名

删除环境变量
从磁盘中将整个MySQL删除

数据库的组成

DBMS（Database Manage System） : 全称数据库管理系统

数据库常见的命令

学习数据库，主要学习是如何通过命令操作数据库
关系型数据库是一个结构化容器，支持SQL（结构化查询语言）

SQL命令的分类

DDL (数据定义语言)
DML (数据操纵语言-增删改)
DQL (数据查询语言)
DCL (数据控制语言)

DDL

主要操作数据库的结构，例如:建库、建表、建索引、建视图、删除表、删除库、删除索引、删除视图等等。

常见的命令有create、drop、alter

创建一个数据库(database)

create database [if not exists] <databaseName>;

每次创建的数据库，在磁盘上的表现就是文件夹

查看MySQL 下所有的数据库

show databases [like '%databaseName%'];

删除MySQL 下指定的数据库

drop database [if exists] <databaseName>;

切换数据库

ues <databaseName>;

MySQL数据库中常见的数据类型

整数类型
- tinyint : 相当于Java中的 byte 类型
- smallint : 相当于Java中的 short 类型
- int / integer : 相当于Java中的 int 类型
- bigint : 相当于java中的 long 类型
小数类型
- float : 单精度
- double : 双精度
- decimal : 表示数字、可以解决在计算的过程中精度损失问题
字符串类型
- char(n) : 固定长度的字符串，n代表字符串的长度，如果字符串长度不够会默认补空白符
- varchar(n) : 可变长度的字符串，n 代表允许的最大长度（比较常用）
- text : 用来存储内容较长的字符串，一般情况下，如果字符串长度超过4000，推荐使用text
- longtext : 比text更大的容器
boolean类型
1 代表 true ，0 代表 false
日期类型
- date : 表示年月日、对应于java中的LocalDate 或者 Date
- datetime : 对应于Java中的LocalDateTime 或者 DateTime
- time : 表示时分秒，对应java中的LocalTime
字节类型
- blob : 用来存储字节类型的数据
- longblob : 用来存储字节型的数据

表Table的基本操作

创建表

表是用来进行存储数据的，表由表头和数据组成，表头有多个字段组成数据由记录组成

语法:

create table <tableName>(
    <columnName> <columnType> [约束] [注释]
);

删除表

drop table <tableName>;

查看表结构

desc <tableName> ;

查看建表的语句

show create table <tableName>;

查看某个数据库下的所有表

show tables [like '%xxx%'];

范式 NF

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库
这些不同的规范要求被称为不同的范式，各种范式呈递递次规范，越高的范式数据库沉余越小

-1NF : 表中的没以列不可再分，是原子性的，关系型数据库的表默认都满足1NF -2NF : 表中的所有非主属性都依赖于主属性，消除部分依赖的问题 -3NF : 表中的所有的非属性，都完全依赖于主属性组、消除
传递依赖问题。三范式要求表中必须提供主键，保证数据唯一

表结构的修改

alter table <tableName> ...;

添加一个字段

alter table <tableName> add [column] <columnName> <columnType> [约束] [注释];

删除一个字段

alter table <tableName> drop [column] <columnName>;

修改字段类型/长度

alter table <tableName> modify [column] <columnName> <columnType> [约束] [注释];

如果表中有数据、在修改字段类型的时候，有可能会修改失败

修改字段名称

alter table <tableName> change <oldName> <newName> <columnType> [约束] [注释];

修改表名

alter table <tableName> rename to <newName>;
或者
rename table <oleTableName> to <nweTableName> ;

DML的操作

数据操纵语言，指的是对表中的数据的增、删和改操作

添加数据 insert into

方式一:(不推荐)

insert into <tableName> values ( val...)

val值的顺序报销和建表的时候字段的顺序保持一致，且个数和字段个数必须相同
缺点: 1、值顺序和表字段顺序必须一致 2、表中所有字段必须插入值

方式二:(推荐)

insert into <tableName>(column...) values (val...)

column设置插入的字段列表，多个值用逗号分割，val插入值的顺序、个数和column定义的顺序、个数保持一致即可

方式三:(批量插入)

insert into <tableName>(column...) values
(val1...),
(val2...),
(val3...),
...
(valn...);

优点:可以同时插入多条记录缺点 : SQL语句过长

删除数据 delete

delete from <tableName> [where 条件];

**不推荐使用 delete from 表删除所有数据，如果确实需要删除表中所有数据，推荐使用 truncate table tableName **

truncate table 和 delete 删除表中所有数据的时候的区别

truncate会删除表中所有的数据和占用的空间
delete只删除数据但是可以按照条件删除

修改数据 update

update <tableName> set <columnName> = <val>,...<columnName> [where];

DQL

数据查询语言，主要用来查询数据，使用的命令是 select

常见的查询方式

SimpleQuery

*代表所有字段

1.简单查询 : select xxx; xxx可以是函数也可以是简单的运算主要可以做集群间的心跳检测

-- 查询当前时间
select now();
-- 查询当前数据库版本
select version();
-- 查询当前用户
select user();

2.基于表的查询

select column... from <tableName>;

查询的 column…多个列用逗号分割
如果要查询所有的列

3.条件查询

select column... from <tableName> where 条件;

条件使用where关键字
关系条件 > , >= , < , <= , !=（不等） ,=（等于） , <>(不等)
逻辑条件 and(逻辑与) or(逻辑或)
枚举 in
模糊 like
非/空值 is[not] null

% : 用来匹配0-n个字符
_ : 用来匹配一个字符

4.分组查询 group by

聚合函数
- count() : 统计个数 如果按照字段统计，那么字段为空时不参加统计
- max() : 求最大值
- min() : 求最小值 -sum() : 求和
- ayg() : 去平均值当统计的字段的主键字段时执行效率: count(id)>count(1)>count()
  当统计的字段的普通字段时执行效率: count(1)>count(id)>count()

分组对查询的列有要求a）查询的列必须出现在聚合函数中 b）查询的列出现在group by之后
如果查询的列出现了聚合函数和普通的列，那么该 SQL应该需要用到分组

5.分组筛选查询 having

having 必须在分组后才能使用，而 where 是对表中的所有数据进行过滤，在执行效率上比having高，在分组前使用
从执行顺序上，where 优于 having 先执行。能用where筛选数据尽量不要用having筛选

6.排序 order by

asc : 升序排列
desc : 降序排列

7.分页 limit

select * from <tableName> limit [<offset>,] <rows>;

offset : 偏移量，默认从0开始不传默认是0
rows : 用来设置每页显示的条数
page : 代表页码
pages : 代表总页数
total : 代表总条数

offset = (page - 1) * rows;
offset = (total - 1) / rows + 1;

SubQuery(子查询)

子查询可以更好的去查询想要的数据、是一种比较特殊的嵌套查询

1.基于列的子查询

在查询的列中包含应该查询

-- 查询 ID =1000 的 学号 、姓名和系名

子查询的结果必须是单列、单值
-2.基于条件的子查询

在查询条件上、包含应该查询语句

关系条件子查询
关系条件子查询的结果是单列、单值的
in 条件子查询
in 条件子查询的结果是单列、多值的
exists 子查询
exists子查询有结果就代表存在，否则代表不存在返回的是一张表
嵌套查询

将查询的结果当作表进行查询
嵌套查询要求必须给表起别名

with查询

当一个表的结果在 SQL 被多次使用可以通过 with 别名 as() 将结果建立成一个临时表

关联查询

select ... from A 表
    [left/riegt/inner] join B表 on 关联条件

where ...

inner join : 内链接查询、可以省略 inner 关键字

将两张表具有关联关系的数据进行关联起来

left outer join : 左外链接查询，可以省略 outer 关键字

以左边的表为主表来关联右边的表将左边的表相关数据全部返回

right outer join : 右外链接查询，可以省略 outer 关键字

以右边的表为主表，关联左边的表，将右边的表的相关数据全部返回

关联查询一定要给表起别名

笛卡尔积

将多张表合并在一起查询

性能极低 : 两张表会进行交叉组合、形成一个超级大表，表的数据量rows= A表的rows×B表的rows

集合查询

并集 union / union all

将两个SQL 使用 union / union all 合并在一块
union 会对两个SQL 查询的结果进行去重
union all 不会去重，仅仅将两个SQL结果进行合并
distinct 去重

交集
差集

MySQL只支持并集

索引

为了提高查询效率
索引是添加在字段上的
索引存储数据的结果采用 B+Tree

索引的种类

唯一索引 : 主键约束、唯一约束采用的就是唯一索引
普通索引 :可以给任何需要的字段添加
组合索引 : 将多个字段联合起来构建一个索引

SQL会不会走组合索引采用最左原则

什么样的字段适合添加索引

1、主键自带索引
2、键适合添加索引
3、外键适合添加索引（添加了外键约束会自带索引）
4、经常出现咋where条件上的字段
5、字段对应的值重复率不高适合添加索引
6、经常用来做分组的字段适合添加索引

索引的操作

添加索引

create [unique] index <indexName> on <tableName>(columnName,...)

alter table <tableName> add index <indexName>(columnName,...)

删除索引

drop [unique] index <indexName> on <tableName>(columnName,...)

alter table <tableName> drop index <indexName>(columnName,...)

视图 View

视图View是真实表的映射、是一张真实的表、是一条SQL命令
视图不储存数据、存储的是SQL查询命令

视图的作用

1、隐藏真实的表、从而保证数据的安全
2、简化查询

视图的操作

创建视图

create view <viewName> as select ;

删除视图

drop view <viewName> ;

事务

事务是用来保证业务操作的完整性、一个业务要么全部成功、要么全部失败，不允许部分称为、部分失败

事务的四大特性 ACID

A : 原子性（Atomicity） --> 整个业务不可分割
C : 一致性（Consistency）–> 保证最终数据的一致
I : 隔离性（Isolation） --> 解决数据库并发操作引起的问题
D : 持久性（Durability） --> 保证数据永久性

事务的隔离级别

Read uncommitted : 读未提交

可以读取到另一个事务未提交的数据

Read committed : 读已提交

读取到另一个事务已经提交的数据

Repeatable read : 可重复读
Serializable : 序列化读

隔离级别产生的问题

Read uncommitted : 会存在脏读、不可重复读、幻读
Read committed : 会存在不可重复读、幻读
Repeatable read : 会存在幻读
Serializable : 不会产生任何问题、但是效率最低

MySQL内置函数

数学函数

ceil(x) : 向上取整
floor(x) : 向下取整
round(x) : 四舍五入
round(x,y) : 四舍五入保留y位小数
rand() : 返回一个0-1的随机小数

字符串函数

char_length(x) : 获取字符串长度
length(x) : 获取字符串字节长度
concat(x,y) : 拼接x和y
concat_ws(sep,x,y) : 按照指定的分隔符sep 拼接x和y
upper(x) : 转成大写
lower(x) : 转成小写
left(x,n) : 从左开始截取n个长度
right(x,n) :从右开始截取n个长度
substring / substr (x,offset,len) : 截取字符串，offset从1开始

日期函数

now() : 获取当前系统时间
date_formant(date,‘%Y-%m-%d %H:%i:%s’) : 格式化日期
date_add(date,interval n unit ) : 增加日期 n是一个数字 unit是一个日期单位
date_sub(data,interval n unit ) : 减少日期
year(date) : 获取年份
mouth(date) : 获取月份
dayofmonth(date) : 获取日
datediff():

条件函数

ifnull(val,defaultValue) : 如果val is null ，则使用 defaultValue
if(exp,v1,v2) : 如果exp条件成立则返回v1 否则返回v2

表达式

case

DCL

数据控制语言，主要是对数据库权限进行管理

User用户

MySQL 数据库使用的用户都被存储在mysql.user表中

核心字段

Host : 设置账号链接的主机IP，如果设置为localhost，那么代表只允许本机链接

如果希望任意计算机都可以使用您的账户链接这个数据库可以将Host的值设置为%

User : 用户名
max_connections : 最大的链接数
plugin : 密码的管理方式
- 在MySQL8.0版本，值默认是 caching_sha2_password
- 在低版本中，默认值都是 mysql_native_password
authentication_string : 密码
- 在低版本中，没有该字段
password_expired : 密码是否过期
password_lifetime : 密码的过期时间，只有在 password_expired 的值为 Y 时才生效
account_locked : 账户是否被锁

账户操作

创建用户

create user <userName> identified by '密码';

此时用户没有任何权限

修改密码

alter user <userName> identified by '新密码';

锁定/解锁账户

alter user <userName> account lock/unlock;

删除用户

drop user <userName>;

DCL常见的命令

grant : 授权

grant 权限列表 on <databaseName>.<tableName> to 用户@'%';

多个权限用逗号分割，例如 select,insert,update,delete,create,drop
如果要授予所有权限，可以使用all来表示
databaseName 代表数据库的名字，如果要授予使用数据库权限，可以用*
tableName 代表表名，如果要授予全部表权限也可以用*

revoke : 撤销权限

revoke 权限列表 on <databaseName>.<tableName> from 用户@'%';