Java 阶段三 Day04 数据库查询（二）

JAVA救救我

已于 2023-09-11 17:23:21 修改

阅读量72

点赞数

分类专栏： Java 学习文章标签：数据库 java oracle

于 2023-09-08 11:28:13 首次发布

本文链接：https://blog.csdn.net/weixin_44693429/article/details/132748069

版权

Java 学习专栏收录该内容

24 篇文章 0 订阅

订阅专栏

本文详细介绍了Java阶段三中SQL查询的子查询、关联查询（包括一对一、一对多、多对多关系及连接条件）、JDBC基础与使用流程，重点讲解了内连接、外连接、自连接和JDBC核心接口，以及如何使用PreparedStatement执行预编译SQL语句。

摘要由CSDN通过智能技术生成

数据库查询

四、子查询（SubQuery）

4.1 引言

研讨在一个SQL语句中的DQL语句，该DQL被称为子查询。

4.2 应用场景

DQL中使用子查询
- 在SELECT子句中，将当前子查询结果作为一个字段展示
- 在WHERE子句中，将当前子查询结果作为过滤条件使用(最常用的场景)
DML中使用:将一个查询结果集用于增删改操作

4.3 子查询分类

单行单列子查询，该子查询的结果集只有一个值
多行单列子查询，该子查询结果集是多个值

4.3.1 单行单列子查询

示例

查看比左大凯工资高的都有谁

# 在数据库中，被嵌套的DQL需要使用()括起来
SELECT name,salary
FROM worker
WHERE salary>(SELECT salary FROM worker WHERE name='左大凯');

查看哪些老师的工资是高于平均工资的

SELECT name,salary
FROM worker
WHERE salary>(SELECT AVG(salary) FROM worker);

4.3.2 多行单列子查询

多行单列子查询是可以检索出若干个值。因此作为过滤条件使用时，一般配合:IN，NOT IN使用。
因为等于是不能同时等于好几个值的，只能同于其中之一。

示例

查看与“左大凯”和“孙大振”在同一个班的学生有哪些

SELECT name,class_id
FROM student
WHERE class_id IN (SELECT class_id FROM student WHERE name IN('左大凯','孙大振'))

五、关联查询[重要]

联合多张表查询数据，查询结果集中的字段来自与多张表；
表与表中的记录会产生对应关系，用于我们联合查询。

5.1 关联关系分类

一对一
A表的一条记录仅唯一对应B表中的一条记录，反过来B表中的一条记录也仅唯一对应A表中的一条
记录
一对多
A表中的一条记录对应B表中的多条记录，但是反过来B表中的一条记录仅能唯一对应A表中的一条
记录
多对多
A表中的一条记录能够对应B表中的多条记录；
同时B表中的一条记录也能对应A表中的多条记录

5.2 关于连接条件

在DQL中我们会指定连接条件，用来让数据库在查询中知道两张表中记录与记录的对应关系，从而查询出对应的记录。
关联查询中连接条件通常不可以忽略或缺失，否则会产生笛卡尔积

5.3 语法及示例

语法

SELECT 字段,...
FROM 表A,表B[,表C,...]
WHERE A表与B表的连接条件
AND [其他表的连接条件]
AND 过滤条件
# 注意:连接条件要同时满足，且如果还有过滤条件时，也要与过滤条件同时满足

示例

查看每个老师以及其负责课程科目名

SELECT t.name,t.age,s.name
FROM teacher t,subject s
WHERE t.subject_id=s.id

不指定连接条件，会产生笛卡尔积
```
SELECT t.name,t.age,s.name
FROM teacher t,subject s;
```
笛卡尔积的产生
当不指定连接条件时，数据库在进行关联查询时，仍然会用A表一条记录与B表每条记录连接一次，并产生结果集中的一条记录；此时的数据量为A表记录数与B表记录数的乘积。
当表中数据量大时，这样的结果集开销巨大，甚至可能导致服务器宕机，因此要尽量避免。

查看班级的名称和对应的班主任(老师)是谁

# 确定数据来自哪些表，确定FROM子句
# class表和teacher表
# FROM class c,teacher t
# 2:当表明确了，就要确定连接条件
# 班级表中teacher_id的值记录了班主任的id应当对应teacher表的老师id
# c.teacher_id = t.id
SELECT c.name,t.name
FROM class c,teacher t
WHERE c.teacher_id = t.id;

5.4 N张表关联

N张表关联查询，至少要有N-1个连接条件

示例

查看"左大凯"所带班级的学生都有谁?

要列出:学生名字，年龄，班级名称，老师名字

# 数据来自哪些表
# student s,class c,teacher t
# 连接条件
# 3张表关联至少要有2个连接条件
# 班级表与学生表的关系:c.id=s.class_id
# 班级表与老师表的关系:c.teacher_id=t.id
# 过滤条件
# 老师的名字"左大凯"
SELECT s.name,s.age,c.name,t.name
FROM student s,class c,teacher t
WHERE s.class_id=c.id
AND c.teacher_id=t.id
AND t.name='左大凯';

5.5 关联查询与聚合函数

将关联查询的结果集进行统计
示例

查看左大凯所带班级的学生信息

SELECT s.name
FROM teacher t,class c,student s
WHERE t.id=c.teacher_id
AND s.class_id=c.id
AND t.name='左大凯';

在上述查询出参与统计记录的DQL中添加聚合函数

SELECT COUNT(*)
FROM teacher t,class c,student s
WHERE t.id=c.teacher_id
AND s.class_id=c.id
AND t.name='左大凯';

仅查看平均工资高于6000的那些科目的老师平均工资是多少?列出平均工资和科目名称

SELECT AVG(t.salary),su.name
FROM teacher t,subject su
WHERE t.subject_id=su.id
GROUP BY su.name
HAVING AVG(salary)>6000;

5.6 主键与外键

5.6.1 主键外键说明

主键
一张表中通常第一个字段为主键字段,用来唯一标识表中的一条记录.主键要求的条件是非空且唯一。
外键
一张表中一个字段保存了另一张表中主键字段的值,那么这个字段就是外键字段。

在关联关系中，两张表通常就是使用主外键进行关联的。并且在关联查询中总是用等值连接主键与外键来建立两张表中记录的对应关系。

通常：定义外键字段的表在关联关系中处于"多"的一方。

例如：学生与班级表。学生表中有一个字段class_id保存着class表主键id字段的值。此时class_id字段就是外键字段.因此班级表与学生表存在一对多的关系，即：班级表的一条记录可以对应学生表的多条记录。

5.6.2 外键约束

如果给某张表的某个字段施加外键约束，此时外键约束要求必须指明该外键对应的是哪张表的主键字段。

外键约束要求:

外键字段保存的值必须是主键字段存在的值或NULL

外键约束引发的问题:

外键字段不能保存主键字段没有的值
不能轻易删除主键字段表中记录，因为如果外键中存在着要删除的主键记录，此时相当于删除掉主键自己则外键字段保存了主键字段没有的值。
- 如果想删除主键字段记录，则需要现将该主键的值对应的所有表中外键是该值得记录更新为NULL，此时才可以将其删除，因此会带来大量的DML操作。

结论：关联关系中通常不适用外键约束。

5.7 多对多关系

多对多关系：A表与B表双向看待都是一对多，就是多对多关系。

多对多关系需要提供一张关联关系表来维系多对多关系。该表保存着两张产生关系表的外键字段。
在这里插入图片描述
示例

查看学习语文的学生都有谁，列出学生姓名、科目名称、学生成绩?

SELECT s.name,su.name,tss.score
FROM subject su,student s,t_stu_subject_score tss
WHERE su.id=tss.subject_id
AND s.id=tss.stu_id
AND su.name='语文';

查看’左大凯’都学了哪门课程以及成绩，列出学生姓名、科目名称、学生成绩?

SELECT s.name,su.name,tss.score
FROM student s,subject su,t_stu_subject_score tss
WHERE su.id=tss.subject_id
AND s.id=tss.stu_id
AND s.name='左大凯';

六、连接查询[重要]

6.1 内连接

内连接是关联查询的另一种写法，只会查找到符合条件的记录，结果和表关联查询是一样的。
在这里插入图片描述
语法

SELECT 子句
FROM 表A
JOIN 表B ON A与B的连接条件
[JOIN 表C ON A与C或B与C的连接条件
 ...
]
WHERE 过滤条件

示例

查看1年级1班的学生信息？列出学生名字,年龄,所在班级

SELECT s.name,s.age,c.name
FROM class c
JOIN student s ON c.id=s.class_id # ON子句中书写连接条件
WHERE c.name='1年级1班';			  # WHERE子句中写过滤条件

查看教英语的老师都有谁?

SELECT t.name,su.name
FROM teacher t
JOIN subject su ON t.subject_id=su.id
WHERE su.name='英语';

查看每个班级名以及对应的班主任名字?

SELECT c.name,t.name
FROM teacher t
JOIN class c ON t.id=c.teacher_id;

6.2 外连接

外连接也用于关联查询，特点:可以将不满足连接条件的记录也查询出来

左外连接：

以JOIN左侧表为驱动表，该表中所有记录都要体现在结果集中，右侧表不满足连接条件的记录对应的字段全部为NULL。
右外连接

以JOIN右侧表作为驱动表，该表中的记录都要体现在结果集中，左侧表不满足连接条件的字段都补NULL

示例

查看所有班级信息和对应的班主任信息，如果该班没有班主任也要将班级信息列出来.

# 因为class表中有三个班级的teacher_id为99,而teacher表中没有id为99的记录.因此这三条班级记录由于不满足连接条件,所以在结果集中没有被查询出来.

# 需求是要将class表中记录完整展现(不满足连接条件的记录也要展现)
SELECT c.name,c.floor,t.name,t.salary
FROM class c LEFT JOIN teacher t ON c.teacher_id=t.id;

查看所有班级信息和对应的班主任信息，如果该老师不带班,也要将老师信息列出来
```
SELECT c.name,c.floor,t.name,t.salary
FROM class c RIGHT JOIN teacher t ON c.teacher_id=t.id;
```

6.3 自连接

自连接是指当前表中的一条记录可以对应自己的多条记录；

具有相同属性的一组数据之间又存在上下级的树状结构数据。

示例

查看’左大凯’的义父都有谁?

SELECT p.name,f.name
FROM people p,father f   # 将people当做两张表看待,p表示左大凯  f表示义夫
WHERE p.father=f.name
AND p.name='左大凯';

内连接写法
SELECT p.name,f.name
FROM people p
JOIN father f ON p.father=f.name
WHERE p.name='左大凯';

七、JDBC

JDBC（Java Database Connectivity）是Java官方提供的一套API，用于连接各种数据库。它提供了一组用于执行SQL语句的Java方法，以及连接到数据源和处理查询结果的一些工具和类。

准备

USE tedu;
DROP TABLE IF EXISTS userinfo;
CREATE TABLE userinfo(
	id INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(30),
	password VARCHAR(30),
	nickname VARCHAR(30),
	age INT
)CHARSET=UTF8;

7.1 JDBC核心接口

Connection
表示数据库的连接
Statement
用来执行SQL语句的语句对象
PreparedStatement
用来执行预编译SQL语句的语句对象
ResultSet
用来表示查询结果集

7.2 JDBC使用流程

pom.xml 中添加MySQL依赖，并刷新Maven

加载JDBC的驱动程序

Class.forName("com.mysql.jdbc.Driver");

建立数据库的连接

Connection connection = DriverManager.getConnection(url, user, password);

创建SQL语句执行对象

Statement statement = connection.createStatement();

执行SQL语句
```
statement.executeUpdate("SQL语句");
```
获取查询结果

7.2.1 pom.xml中添加依赖

Maven仓库地址：https://mvnrepository.com/

pom.xml中添加MySQL数据库依赖，并刷新maven

<dependencies>
    <!-- 连接MySQL数据库的依赖 -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.15</version>
    </dependency>
</dependencies>

7.3 JDBC方法总结

加载JDBC驱动程序

Class.forName("com.mysql.cj.jdbc.Driver");

创建数据库连接对象

Connection connection = DriverManager.getConnection(url, user, password);
url：数据库连接的URL地址
user：数据库用户名
password：数据库密码

执行静态SQL语句(DML语句)

创建SQL语句执行对象

Statement statement = connection.createStatement();

执行SQL语句

int i = statement.executeUpdate("DML语句");

执行动态SQL语句(DML语句)

创建SQL语句执行对象

String sql = "UPDATE student SET name = ? WHERE id = ?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1, "Tom");
statement.setInt(2, 1);

执行SQL语句
```
statement.executeUpdate();
```

执行查询语句

// 1.执行查询语句
String sql = "SELECT name,gender FROM student WHERE id = ?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setInt(1, 1);

ResultSet resultSet = statement.executeQuery(sql);

// 2.处理查询结果
while (resultSet.next()) {
    String name = resultSet.getString("name");
    String gender = resultSet.getString("gender");
}