【JavaWeb】数据库相关

JavaWeb整体介绍

什么是JavaWeb?

• Web：全球广域网，也称为万维网（www），能够通过浏览器访问的网站
• JavaWeb：是用Java技术来解决相关web互联网领域的技术栈

JavaWeb介绍

1. 网页：展现数据
2. 数据库：存储和管理数据
3. JavaWeb程序：逻辑处理

数据库相关概念

数据库

• 存储数据的仓库，数据是有组织的进行存储
• 英文：DataBase,简称DB

数据库管理系统

• 管理数据库的大型软件
• 英文：DataBase Management System，简称DBMS

SQL

• 英文：Structured Query Language，简称SQL，结构化查询语言
• 操作关系型数据库的编程语言
• 定义操作所有关系型数据库的统一标准

常见的关系型数据库管理系统

• Oracle：收费的大型数据库，Oracle公司的产品
• MySQL：开源免费的中小型数据库。后来Sun公司收购了MySQL，而Sun公司又被Oracle收购
• SQL Server：Microsoft公司收费的中型的数据库。C#、.net等语言常使用
• PostgreSQL：开源免费的中小型数据库
• DB2：IBM公司的大型收费数据库产品
• SQLite：嵌入式的微型数据库。如：作为Android内置数据库
• MariaDB：开源免费的中小型数据库

MYSQL数据库

MySQL数据模型

关系型数据库

关系型数据库是建立在关系模型基础上的数据库，简单说，关系型数据库是由多张能互相连接的二维表组成的数据库

• 优点

1. 都是使用表结构，格式一致，易于维护。
2. 使用通用的SQL语言操作，使用方便，可用于复杂查询。
3. 数据存储在磁盘中，安全。

SQL

• 英文：Structured Query Language，简称SQL
• 结构化查询语言，一门操作关系型数据库的编程语言
• 定义操作所有关系型数据库的统一标准
• 对于同一个需求，每一种数据库操作的方式可能会存在一些不一样的地方，我们称为“方言”

SQL通用语法

1. SQL语句可以单行或多行书写，以分号结尾。
2. MySQL数据库中的SQL语句不区分大小写，关键字建议使用大写。
3. 注释

• 单行注释：-- 注释内容 或 #注释内容（MySQL特有）
• 多行注释：/* 注释 */

Show DataBases; -- 查询所以数据库名称

SQL分类

• DDL：操作数据库、表等
• DML：对表中的数据进行增删改
• DQL：对表中数据进行查询
• DCL：对数据库进行权限控制

DDL-操作数据库

1. 查询

SHOW DATABASES;

2. 创建

• 创建数据库

CREATE DATABASE 数据库名称;

• 创建数据库（判断，如果不存在则创建）

CREATE DATABASE IF NOT EXISTS 数据库名称;

3. 删除

• 删除数据库

DROP DATABASE 数据库名称;

• 删除数据库（判断，如果存在则删除）

DROP DATABASE IF EXISTS 数据库名称;

4. 使用数据库

• 查看当前使用的数据库

SELECT DATABASE();

• 使用数据库

USE 数据库名称;

DDL-操作表

1. 查询表

• 查询当前数据库下所有表名称

SHOW TABLES;

• 查询表结构

DESC 表名称;

2. 创建表

CREATE TABLE 表名 (
	字段名1 数据类型1，
	字段名2 数据类型2，
	...
	字段名n 数据类型n
);

注意：最后一行末尾，不能加逗号

3. 修改表

• 修改表名

ALTER TABLE 表名 RENAME TO 新的表名;

• 添加一列

ALTER TABLE 表名 ADD 列名 数据类型;

• 修改数据类型

ALTER TABLE 表名 MODIFY 列名 新数据类型;

• 修改列名和数据类型

ALTER TABLE 表名 CHANGE 新列名 新数据类型;

• 删除列

ALTER TABLE 表名 DROP 列名;

5. 删除表

• 删除表

DROP TABLE 表名;

• 删除表时判断表是否存在

DROP TABLE IF EXISTS 表名;

数据类型

MySQL支持多种类型，可以分为三类：

• 数值
• 日期
• 字符串
  
  score double(总长度,小数点后保留的位数)

DML-操作数据

添加数据

1. 给指定列添加数据

INSERT INTO 表名(列名1,列名2,...) VALUES(值1,值2,...);

2. 给全部列添加数据

INSERT INTO 表名 VALUES(值1,值2,...);

3. 批量添加数据

INSERT INTO 表名(列名1,列名2,...) VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...),...;
INSERT INTO 表名 VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...),...;

修改数据

1. 修改表数据

UPDATE 表名 SET 列名1=值1,列名2=值2,... [WHERE 条件];

注意：修改语句中如果不加条件，则所有数据都将修改！

删除数据

1. 删除表数据

DELETE FROM 表名 [WHERE 条件];

注意：删除语句中如果不加条件，则所有数据都将删除！

DQL-基础查询

查询语法

SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段 HAVING 分组后条件 ORDER BY 排序字段 LIMIT 分页限定

基础查询

1. 查询多个字段

SELECT 字段列表 FROM 表名;
SELECT * FROM 表名; -- 查询所有数据

2. 去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

3. 起别名

AS: AS 也可以省略

条件查询

1. 条件查询语法

SELECT 字段列表 FROM 表名 WHERE 条件列表;

2. 条件

符号	功能
>	大于
<	小于
>=	大于等于
<=	小于等于
=	等于
<>或!=	不等于
BETWEEN … AND …	在某个范围之内（都包含）
IN(…)	多选一
LIKE 占位符	模糊查询 _单个任意字符 %多个任意字符
IS NULL	是NULL
IS NOT NULL	不是NULL
AND 或 &&	并且
OR 或 ||	或者
NOT 或 !	非，不是

DQL-排序查询

1. 排序查询语法

SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1 [排序方式1], 排序字段名2 [排序方式2] ...;

排序方式：

• ASC：升序排序（默认值）
• DESC：降序排序

注意：如果有多个排序条件，当前边的条件值一样时，才会根据第二条件进行排序

DQL-聚合函数

1. 概念：
   将一列数据作为一个整体，进行纵向计算。
2. 聚合函数分类

函数名	功能
count(列名)	统计数量（一般选用不为null的列）
max(列名)	最大值
min(列名)	最小值
sum(列名)	求和
avg(列名)	平均值

3. 聚合函数语法

SELECT 聚合函数名(列名) FROM 表名;

注意：null值不参与所有聚合函数运算

DQL-分组查询

1. 分组查询语法

SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 HAVING [分组后条件过滤];

注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义

where 和 having 区别：

• 执行时机不一样：where 是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过滤。
• 可判断的条件不一样：where不能对聚合函数进行判断，having可以。

执行顺序：where → 聚合函数 → having

DQL-分页查询

1. 分页查询语法

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询条目数;

• 起始索引：从0开始
  计算公式：起始索引 = (当前页码-1) * 每页显示的条数
  tips：
• 分页查询 limit 是MySQL数据库的方言
• Oracle分页查询使用 rownumber
• SQLServer分页查询使用 top

约束

约束的概念和分类

1. 约束的概念

• 约束是作用于表中列上的规则，用于限制假如表的数据
• 约束的存在保证了数据库中数据的正确性、有效性和完整性

2. 约束的分类

约束名称	描述	关键字
非空约束	保证列中所有数据不能有null值	NOT NULL
唯一约束	保证列中所有数据各不相同	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
检查约束	保证列中的值满足某一条件	CHECK
默认约束	保存数据时，未指定值则采用默认值	DEFAULT
外键约束	外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性	FOREIGN KEY
MySQL不支持检查约束

自动增长关键字：auto_increment

非空约束

1. 概念

• 非空约束用于保证列中所有数据不能有null值

2. 语法

• 添加约束

-- 创建表时添加非空约束
CREATE TABLE 表名(
	列名 数据类型 NOT NULL,
	...
);

-- 建完表后添加非空约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;

• 删除约束

ALTER TABLE 表名 MODIFY 字段名 数据类型;

唯一约束

1. 概念

• 唯一约束用于保证列中所有数据各不相同

2. 语法

• 添加约束

-- 创建表时添加唯一约束
CREATE TABLE 表名(
	列名 数据类型 UNIQUE [AUTO_INCREMENT],
	-- AUTO_INCREMENT: 当不指定值时自动增长
	...
);
CREATE TABLE 表名(
	列名 数据类型,
	...
	[CONSTRAINT] [约束名称] UNIQUE(列名)
);

-- 建完表后添加唯一约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 UNIQUE;

• 删除约束

ALTER TABLE 表名 DROP INDEX 字段名;

主键约束

1. 概念

• 主键是一行数据的唯一标识，要求非空且唯一
• 一张表只能有一个主键

2. 语法

• 添加约束

-- 创建表时添加唯一约束
CREATE TABLE 表名(
	列名 数据类型 PRIMARY KEY [AUTO_INCREMENT],
	...
);
CREATE TABLE 表名(
	列名 数据类型,
	...
	[CONSTRAINT] [约束名称] PRIMARY KEY(列名)
);

-- 建完表后添加主键约束
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

• 删除约束

ALTER TABLE 表名 DROP PRIMARY KEY;

默认约束

1. 概念

• 保存数据时，未指定值采用默认值

2. 语法

• 添加约束

-- 创建表时添加默认约束
CREATE TABLE 表名(
	列名 数据类型 DEFAULT 默认值,
	...
);

-- 建完表后添加默认约束
ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;

• 删除约束

ALTER TABLE 表名  ALTER 列名 DROP DEFAUL;

外键约束

1. 概念

• 外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性

2. 语法

• 添加约束

-- 创建表时添加外键约束
CREATE TABLE 表名(
	列名 数据类型,
	...
	[CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名)
);

-- 建完表后添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称  FOREIGN KEY(外键字段名称) REFERENCES 主表名称(主表列名称);

• 删除约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

数据库设计

数据库设计

1. 软件的研发步骤
   
2. 数据库设计概念

• 数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型。
• 建立数据库中的表结构以及表与表之间的关联关系的过程。
• 有哪些表？表里有哪些字段？表和表之间有什么关系？

3. 数据库设计的步骤
   ① 需求设计（数据是什么？数据具有哪些属性？数据与属性的特点是什么）
   ② 逻辑分析（通过ER图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统）
   ③ 物理设计（根据数据库自身的特点把逻辑设计转换为物理设计）
   ④ 维护设计（1. 对新的需求进行建表；2. 表优化）
4. 表设计

• 一对一
• 一对多（多选一）
• 多对多

1. 数据库设计设计什么？

• 有哪些表
• 表里有哪些字段
• 表与表之间是什么关系

2. 表关系有哪几种？

• 一对一
• 一对多（多对一）
• 多对多

多表关系设计

表关系之一对多

• 实现方式：在多的一方建立外键，指向一的一方的主键

表关系之多对多

• 实现方式： 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

表关系之一对一

一对一多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能

• 实现方式：在任意一方加入外键，关联另一方主键，并且设置外键为唯一（UNIQUE）

多表查询

• 笛卡尔积：有A，B两个集合，取A，B所有的组合情况
• 多表查询：从多张表查询数据
  • 连接查询
    • 内连接：相当于查询A B交集数据
    • 外连接：
      - 左外连接：相当于查询A表所有数据和交集部分数据
      - 右外连接：相当于查询B表所有数据和交集部分数据
  • 子查询

内连接

1. 内连接查询语法

-- 隐式内连接
SELECT 字段列表 FROM 表1,表2... WHERE 条件;

-- 显示内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;

内连接相当于查询A B交集数据

外连接

1. 外连接查询语法

-- 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;

-- 显示内连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;

• 左外连接：相当于查询A表所有数据和交集部分数据
• 右外连接：相当于查询B表所有数据和交集部分数据

子查询

1. 子查询概念

• 查询中嵌套查询，称嵌套查询为子查询

2. 子查询根据查询结果不同，作用不同

• 单行单列：作为条件值，使用= != > < 等进行条件判断

SELECT 字段列表 FROM 表 WHERE 字段名 = (子查询);

• 多行单列：作为条件值，使用in等关键字进行条件判断

SELECT 字段列表 FROM 表 WHERE 字段名 in (子查询);

• 多行多列：作为虚拟表

SELECT 字段列表 FROM (子查询) WHERE 条件;

事务

事务简介

• 数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令
• 事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败
• 事务是一个不可分割的工作逻辑单元

事务四大特征

• 原子性（Atomicity）：事务是不可分割的最小操作单位，要么同时成功，要么同时失败
• 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态
• 隔离性（Isolation）：多个事务之间，操作的可见性
• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

MySQL事务默认自动提交

-- 查看事务的默认提交方式
SELECT @@autocommit;
-- 1 自动提交  0 手动提交
-- 修改事务提交方式
set @@autocommit = 0;