山西农业大学20240910

Jia_dongyao

已于 2024-09-12 08:14:00 修改

阅读量386

点赞数 3

分类专栏：山西农业大学文章标签： java 数据库开发语言

于 2024-09-10 11:33:41 首次发布

本文链接：https://blog.csdn.net/2401_86991268/article/details/142084825

版权

山西农业大学专栏收录该内容

13 篇文章 2 订阅

订阅专栏

10-数据库-续

一. 关联查询 - 续

1. N张表关联

N张表关联查询, 至少要有N-1个连接条件
举例:
2张表关联, 1个连接条件
3张表关联, 2个连接条件
…

# N张表关联
# 查看"范传奇"所带班级学生都有谁?列出学生姓名, 年龄, 班级名称, 老师名字
# 1. 数据来源于哪几张表?
# 2. 连接条件: 3张表, 至少有2个连接条件
    -- 班级表与学生表的关系: s.class_id =  c.id
    -- 班级表与老师表的关系: c.teacher_id = t.id
# 3. 过滤条件: 老师的名字为:"范传奇"
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='范传奇';

SELECT name,age,class_id FROM student;
SELECT id,name,teacher_id FROM class;
SELECT id,name FROM teacher;

# 查看1年级1班的同学的名字和来自的城市?
SELECT s.name,l.name,c.name
FROM class c,student s,location l
WHERE s.class_id = c.id
AND s.location_id = l.id
AND c.name='1年级1班';

2. 关联查询和聚合函数

将关联查询和结果集应用聚合函数进行统计

# 关联查询和聚合函数
# 查看范传奇所带班级的学生共有多少人? (count)
# 1. 查询出参与统计的记录 : 范传奇所带班级的所有学生学生的信息
     -- 1.1 数据来源于哪些表?
        -- FROM teacher t,class c,student s
     -- 1.2 连接条件?
        -- s.class_id = c.id
        -- c.teacher_id = t.id
     -- 1.3 过滤条件
        -- t.name = '范传奇'
# 2. 统计记录的数据量,在DQL语句中添加聚合函数 (COUNT(*))
SELECT COUNT(*)
FROM teacher t,class c,student s
WHERE s.class_id = c.id
AND c.teacher_id = t.id
AND t.name = '范传奇';

# 查看教语文的老师的平均薪资
# 1. 查询出参与统计的记录 : 查询教语文的老师的工资
    -- 1.1 数据来源于哪些表?
        -- FROM teacher t,subject sj
    -- 1.2 连接条件?
        -- t.subject_id = sj.id

    -- 1.3 过滤条件
        -- sj.name = '语文'
# 2. 统计记录的平均数,在DQL语句中添加聚合函数 (AVG)
SELECT AVG(t.salary)
FROM teacher t,subject sj
WHERE t.subject_id = sj.id
AND sj.name = '语文';
# 查看教每门课老师的平均工资(GROUP BY)?列出平均工资和科目名称
SELECT AVG(salary),sj.name
FROM teacher t,subject sj
WHERE t.subject_id = sj.id
GROUP BY sj.name;
# 仅仅查看平均工资高于6000的那些科目的老师的平均工资是多少?列出平均工资和科目名称
SELECT AVG(salary),sj.name
FROM teacher t,subject sj
WHERE t.subject_id = sj.id
GROUP BY sj.name
HAVING AVG(salary)>6000;
# 查看工资最高的老师的班级里的学生共有多少人?
SELECT COUNT(*)
FROM teacher t,class c,student s
WHERE t.id = c.teacher_id
AND c.id = s.class_id
AND t.salary = (SELECT MAX(salary) FROM teacher);

3. 连接查询

3.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 student s,class c
WHERE s.class_id = c.id  #连接条件
AND c.name='1年级1班'; # 过滤条件
-- 改为内连接
SELECT s.name,s.age,c.name
FROM student s
JOIN class c ON s.class_id = c.id # ON子句中书写连接条件
WHERE c.name='1年级1班'; # WHERE子句中写过滤条件

# 查看教英语的老师都有谁?
SELECT sj.name,t.name,t.salary
FROM teacher t
JOIN subject sj ON t.subject_id = sj.id
WHERE sj.name = '英语';
# 查看每个班级名以及对应班主任?
SELECT c.name,t.name
FROM class c
JOIN teacher t ON c.teacher_id = t.id;
# 查看王克晶所带班级的女同学都有谁?列出老师姓名,班级名字,学生名字,学生性别
SELECT t.name,c.name,s.name,s.gender
FROM teacher t
JOIN class c ON t.id = c.teacher_id
JOIN student s ON c.id = s.class_id
WHERE t.name='王克晶'
AND s.gender='女';

3.2 外连接

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

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

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

左外连接案例

# 外连接
# 查看所有班级的信息和对应班主任的信息, 如果该班级没有班主任也要将班级信息列出来
SELECT c.name,t.name
FROM class c
JOIN teacher t ON c.teacher_id = t.id;
# 因为class表中有三个班级的teacher_id为99,而teacher表中没有班级id为99
# 的记录, 因为,这三条记录由于没有满足连接条件,所以在结果集中没有查询出来
SELECT id,name FROM teacher; # id:1-20
SELECT teacher_id,name FROM class; # 班级表中有3个老师编号为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 teacher t
RIGHT JOIN class c 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;
-- 修改为左外连接, 效果不变
SELECT c.name,c.floor,t.name,t.salary
FROM teacher t
LEFT JOIN class c ON c.teacher_id = t.id;

在这里插入图片描述

3.3 自连接

自连接指当前表中的一条记录可以对应自己的多条记录
具有相同属性的一组数据之间又存在上下集的树状结构数据
例如:

一个公司的人员组织结构, 大家都是员工, 员工存在上下级
电商项目中的分类

# 自连接
# 查看'刘苍松'的下属都有谁?
# teacher表中记录了所有老师的信息, 而manager字段记录了老师的上级老师的id
# manager 字段记录了teacher主键字段id的值
SELECT t.name,m.name
FROM teacher t    # 将teacher看做两张表, t表存老师,m保存领导
JOIN teacher m ON t.id = m.manager
AND t.name='刘苍松';
-- 修改为 关联查询
SELECT * FROM teacher;

# 查看3年级3班的班长是谁?(student表中 team_leader记录了班长的学生id)
# 班长的特点: team_leader的值与id值相同的学生应该就是这个班的班长
SELECT * FROM student;

SELECT s.name,c.name
FROM class c JOIN student s ON c.id = s.class_id
WHERE c.name = '3年级3班'
AND s.team_leader = s.id;

二. JDBC

JDBC是Java官方提供的一套API, 用与连接各种数据库; JDBC提供了一组用于执行SQL语句的Java方法, 以及连接到数据库和处理查询结果的一些工具和类

1. JDBC核心接口

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

注意: 不同的DBMS都会提供具体的实现类, 并打包为jar, 这个jar包被称为连接该DBMS的驱动(Driver)

2. JDBC使用流程

2.0 前期准备

2.1 pom.xml中添加依赖

方式1: Maven仓库地址: https://mvnrepository.com
方式2:

在pom.xml中添加MYSQL数据库的依赖, 并刷新maven

<!--    指定JDK版本-->
    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
    <dependencies>
        <!-- 连接MySQL数据库的依赖 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.15</version>
        </dependency>
    </dependencies>