MySQL多表查询（实验报告）

最新推荐文章于 2024-08-25 09:19:41 发布

林羡羡

最新推荐文章于 2024-08-25 09:19:41 发布

阅读量2.3k

点赞数 37

文章标签： mysql 数据库 android

本文链接：https://blog.csdn.net/2301_79218588/article/details/138036912

版权

一、实验名称： 多表数据查询

二、实验日期： 2024年 4 月 20 日

三、实验目的：

掌握MySQL多表数据查询；

四、实验用的仪器和材料：

硬件：PC电脑一台；

配置：内存，2G及以上硬盘250G及以上

软件环境：操作系统 windows7以上

数据库环境：MySQL5.7或MySQL8.0.20

五、实验步骤和方法

【案例场景】：

图书管理系统中，书籍管理员经常需要查询学生和教师借阅图书的信息，学生和教师也经常需要查询书籍信息。在实际应用中，查询操作非常重要。

【实验目的】

掌握内连接查询
熟悉外连接查询
掌握子查询
掌握集合查询

【相关知识点】

复习：单表查询

MySQL使用SELECT语句进行数据查询操作，SELECT语句的基本语法如下：

SELECT [ALL|DISTINCT]

〈列名〉[AS 别名1] [{，〈列名〉[ AS 别名2]}]

FROM〈表名1或视图名1〉[[AS] 表1别名]

[WHERE〈检索条件〉]

[GROUP BY <列名1>[HAVING <条件表达式>]]

[ORDER BY <列名2>[ASC|DESC]]

[limit 子句]

内连接

内连接是比较常用的一种数据连接查询方式。它使用比较运算符进行多个基表间数据的比较操作，并列出这些基表中与连接条件相匹配的所有的数据行。一般用INNER JOIN或JOIN关键字来指定内连接，它是连接查询默认的连接方式。

常用的内连接方式有等值连接、非等值连接和自连接。

（1）等值连接

等值连接就是在连接条件中使用等于号（=）比较运算符来比较连接列的列值，其他情况为非等值连接。等值查询结果中列出被连接表中的所有列，并且包括重复列。它是从关系R与S的广义笛卡尔积中选取A，B属性值相等的那些元组。等值连接要求相等的分量，不一定是公共属性，并且不要求相等属性值的属性名相同。

（2）自连接

对同一个表同样也可以进行连接查询，这种连接查询方式就称为自连接。对一个表使用自连接方式时，需要为该表定义一个别名，其他内容与两个表的连接操作完全相似，只是在每次列出这个表时便为它命名一个别名。

2、外连接

若要创建一个查询，以返回一个或多个表中的所有行（无论在另外的表中是否含有匹配的行），则需要使用外连接。

外连接与内连接不同，在查询时所用的基表有主从表之分。使用外连接时，以主表中每行数据去匹配从表中的数据行，如果符合连接条件则返回到结果集中；如果没有找到匹配行，则主表的行仍然保留，并且返回到结果集中，相应的从表中的数据行被填上NULL值后也返回到结果集中。

根据返回行的主从表形式不同，外连接可以分为三种类型：左外连接、右外连接和完全外连接。对应SQL：LEFT/RIGHT/FULLOUTER JOIN，但MySQL不支持完全外连接。

左外连接

左外连接是指返回所有的匹配行并从关键字JOIN左边的表中返回所有不匹配行。由此可知，即使不匹配，JOIN关键字左边的表中数据也将被保留，所以在左外连接中JOIN关键字左边的表为主表，右边的表为从表。使用左边连接的一般语法结构为：

SELECT select_list

FROM table1 LEFT OUTER JOIN table2 ON join_conditions

右外连接

与左外连接相反，右外连接返回所有的匹配行并从关键字JOIN右边的表中返回所有不匹配的行。因此，在右外连接中JOIN关键字右边的为主表，而关键字左边的为从表，右外连接返回结果与左外连接相同，即不满足匹配的结果集在相应列中添加NULL值。

使用右外连接的语句结构为：

SELECT select_list

FROM table1 RIGHT OUTER JOIN table2 ON join_conditions

3、子查询

在MySQL 中，一个SELECT...FROM....WHERE...语句称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句中或HAVING短语的条件中的查询称为嵌套查询，也称为子查询。

当查询条件比较复杂或者一个查询结果依赖另一个查询结果时，可以使用子查询。

在子查询中可以使用IN关键字、EXISTS关键字和比较运算符来连接表。

使用IN 关键字

语法格式如下：

SELECT select_list

FROM table_sourcde

WHERE expression IN|NOT IN (subquery)

比较运算符

带有比较运算符的子查询是指主查询与子查询之间用比较运算符进行比较。当内层子查询返回结果为单值时，可以使用=、>、<、>=、<=、！=或<>等运算符。当内层子查询返回的结果为多指时，可以使用带ANY或ALL的比较运算符。

>ANY	大于子查询结果中的某个值
>ALL	大于子查询结果中的所有值
<ANY	小于子查询结果中的某个值
<ALL	小于子查询结果中的所有值
>=ANY	大于等于子查询结果中的某个值
>=ALL	大于等于子查询结果中的所有值
<=ANY	小于等于子查询结果中的某个值
<=ALL	小于等于子查询结果中的所有值
=ANY IN 等效	等于子查询结果中的某个值
=ALL	等于子查询结果中的所有值
!=(或<>)ANY	不等于子查询结果中的某个值
！=(或<>) 与 NOT IN 相同	不等于子查询结果中的任何一个值

（3）使用关键字EXISTS

EXISTS谓词的查询不返回任何数据，只产生逻辑值“true”或“false”。若内层子查询的结果非空，则外层的where子句返回真值，否则，返回假值。

引入EXISTS运算符时，SQL Server将检查是否存在于子查询相匹配的数据。检索所有的行，当检索到至少一行满足子查询的WHERE条件时，就终止对行的检索。

语法格式如下：

WHERE [NOT] EXISTS (subquery)

4、集合

UNION ALL：返回两个输入中所有行的结果集。

UNION：剔除UNION ALL中重复的行。

（1）并运算union: union运算自动去除重复数据，而union all不去除重复数据，union all效率更高。Union前后的select语句所查询的列的数量必须一致，且对应每列的数据类型也应该一致。

练习：

实验3创建的数据库cs，及在cs数据库中根据下图创建的超市管理系统中的四张表。实验6已分别向四张表插入数据。

（1）ygxx（员工信息表）

（2）bmxx（部门信息表）

（3）spxx（商品信息表）

（4）supplier（供应商信息表）

再创建第五张表供应商品表spsupp，建表语句如下：

create table spsupp

(ssbh char(4) primary key, # 供应商品编号

spbh char(8) not null, # 商品编号

gysbh char(80) not null, # 供应商编号

price float, # 供应价格

sl int, #供应数量

sj datetime ); #供应时间

insert into supplier values

('10000001','格力公司','佛山','528200','07576326535','dw','330625325','工商银行','jary',null), ('10000002','美的公司','佛山','528201','07576326536','dt','330625326','工商银行','jarat',null),

('10000003','宝洁公司','广州','520660','0206326538','dp','330625328','工商银行','teey',null);

insert into spsupp values

('1001','10000001','10000001',2500,300,'2020-10-1'),

('1002','10000001','10000002',2380,500,'2020-10-2'),

('1003','10000003','10000003',30,1500,'2020-10-3');

查询“业务”部门所有员工的员工编号，员工姓名，职务和权限级别。

查询供应空调的所有供应商的供应商编号，供应商名称，地址及电话号码。

查询供应时间在2020-10-1日至2020-10-2日之间供应商品的商品名称，所属类别，供应商名称。

小提示：查询供应时间在2020-10-1日至2020-10-2日之间

select * from spsupp where sj between STR_TO_DATE('2020-09-01 00:00:00','%Y-%m-%d %H:%i:%s') and STR_TO_DATE('2020-10-02 23:59:59,'%Y-%m-%d %H:%i:%s');（了解）

查询员工信息表中姓名姓刘的，且所属部门不在业务部门的员工编号、员工姓名、权限级别及所属部门编号。

使用子查询查询“技术”部门所有员工的员工编号，员工姓名，职务和权限级别。

使用子查询查询供应时间在2020-10-1日至2020-10-2日之间供应商品的所有供应商的供应商编号，供应商名称，地址及电话号码。

分别查询所属类别为“文具”和“电器”的商品编号，商品名称，所属类别，再使用union语句将两条语句的查询结果进行合并。

六、数据记录和计算：

指从实验中测到的数据以及计算结果。

查询“业务”部门所有员工的员工编号，员工姓名，职务和权限级别

 SELECT
         ygxx.ygbh,
         ygxx.name,
         ygxx.zw,
         ygxx.qxjb
      FROM
         ygxx
     WHERE
         ygxx.ssbmbh IN (
             SELECT
                 bmxx.bmbh
             FROM
                 bmxx
             WHERE
                 bmxx.bmmc = '业务'
         );

查询供应空调的所有供应商的供应商编号，供应商名称，地址及电话号码：

SELECT 
    supplier.gysbh,
    supplier.gysmc,
    supplier.dz,
    supplier.dhhm 
FROM  
    supplier 
INNER JOIN 
    spsupp ON supplier.gysbh = spsupp.gysbh
INNER JOIN 
    spxx ON spsupp.spbh = spxx.spbh
WHERE 
    spxx.spmc = '空调';

这里还是有点疑惑：为啥MySQL的工作台运行不出来，打开cmd才能运行出来。真是奇怪：在之前的实验中遇到几次了，明明连接好了数据库并且语句也正确。

查询供应时间在2020-10-1日至2020-10-2日之间供应商品的商品名称，所属类别，供应商名称：

SELECT 
    supplier.gysbh,
    supplier.gysmc,
    supplier.dz,
    supplier.dhhm
FROM  
    supplier 
WHERE 
    supplier.gysbh IN (
        SELECT 
            spsupp.gysbh
        FROM  
            spsupp 
        WHERE 
            spsupp.spbh IN (
                SELECT 
                    spxx.spbh
                FROM  
                    spxx 
                WHERE 
                    spsupp.sj BETWEEN '2020-10-01' AND '2020-10-02'
            )
    );

查询员工信息表中姓名姓刘的，且所属部门不在业务部门的员工编号、员工姓名、权限级别及所属部门编号。

SELECT 
    ygxx.ygbh,
    ygxx.name,
    ygxx.qxjb,
    ygxx.ssbmbh
FROM 
    ygxx
WHERE 
    ygxx.name LIKE '刘%'
    AND ygxx.ssbmbh NOT IN (
        SELECT 
            bmxx.bmbh 
        FROM 
            bmxx 
        WHERE 
            bmxx.bmmc = '业务'
    );

使用子查询查询“技术”部门所有员工的员工编号，员工姓名，职务和权限级别。

SELECT 
    ygxx.ygbh,
    ygxx.name,
    ygxx.zw,
    ygxx.qxjb 
FROM 
    ygxx 
WHERE 
    ygxx.ssbmbh IN (
        SELECT 
            bmxx.bmbh 
        FROM 
            bmxx 
        WHERE 
            bmxx.bmmc = '技术'
    );

使用子查询查询供应时间在2020-10-1日至2020-10-2日之间供应商品的所有供应商的供应商编号，供应商名称，地址及电话号码;

SELECT 
    supplier.gysbh,
    supplier.gysmc,
    supplier.dz,
    supplier.dhhm
FROM  
    supplier 
WHERE 
    supplier.gysbh IN (
        SELECT 
            spsupp.gysbh
        FROM  
            spsupp 
        WHERE 
            spsupp.spbh IN (
                SELECT 
                    spxx.spbh
                FROM  
                    spxx 
                WHERE 
                    spsupp.sj BETWEEN '2020-10-01' AND '2020-10-02'
            )
    );

分别查询所属类别为“文具”和“电器”的商品编号，商品名称，所属类别，再使用union语句将两条语句的查询结果进行合并:

-- 查询所属类别为“文具”的商品信息
SELECT 
    spbh,
    spmc,
    sslb
FROM 
    spxx 
WHERE 
    sslb = '文具'

UNION

-- 查询所属类别为“电器”的商品信息
SELECT 
    spbh,
    spmc,
    sslb
FROM 
    spxx 
WHERE 
    sslb = '电器';

七、实验结果或结论：即根据实验过程中所见到的现象和测得的数据，作出结论。

MySQL的多表查询是指在一个SQL语句中同时查询多个表的数据，并根据表之间的关联条件进行数据的匹配和筛选。多表查询可以通过使用JOIN子句来实现，常见的JOIN类型包括INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL JOIN。在多表查询中，需要明确指定表之间的关联条件，以便数据库能够正确地将相关数据进行匹配。关联条件通常是通过使用ON子句来指定的，ON子句中可以使用等值比较、不等值比较等条件来连接两个表。通过WHERE子句对查询结果进行了进一步的筛选。

八、备注或说明：可写上实验成功或失败的原因，实验后的心得体会、建议等。

在进行多表查询时，需要注意以下：表之间的关联条件：多表查询通常需要通过关联条件将多个表连接起来。确保关联条件正确并且能够准确地匹配到对应的数据。查询字段的选择：确定需要查询的字段，并且确保这些字段在多个表中都存在。同时，避免查询过多的字段，以提高查询效率。表的顺序：多表查询的顺序也是需要考虑的因素。通常情况下，应该将结果集较小的表放在前面，以减少查询的数据量。使用合适的连接方式：根据实际需求选择合适的连接方式，如内连接、外连接等。不同的连接方式会对查询结果产生不同的影响。