数据库实验二之数据查询

实验二数据查询

一．实验目的

**1.**理解数据库中数据的其他查询方法和应用；

**2.**学会各种查询要求的实现；

**3.**学会各种查询的异同及相互之间的转换方法。

二. 实验内容

在实验1的基础上，练习其它查询语句的使用，包括计算列、求和、最大、最小值、各类选择条件、字符匹配、分组和排序，连接查询、嵌套查询及EXISTS查询等，体会各种查询的异同及相互之间的转换，体会各种查询的执行过程，为简单综合应用打下良好的基础。

  查询供应商号码为S1的供应商的名称SNAME，所在城市CITY

```
  查询颜色为红色的零件号码
```

  查询工程所在地为天津的工程名称JNAME

  查询供应商号和名称，分别用小写字母和大写字母表示供应商代码

  查询零件的详细信息，用结果属性名用中文显示。

  查询供应工程J1零件P1的供应商号SNO

  查询供应商的名称和所在城市，并按照所在城市升序排序，同一个城市的按照供应商的名称降序排序

  查询使用供应商S1所供应零件的工程号码

```
  查询各种零件的平均重量
```
查询零件的总个数
查询所有以“螺”字开头的零件的零件号、零件名和颜色
查询各个供应商供应的零件P3总数量
供应工程J1红色零件的供应商号SNO
工程项目J2使用的各种零件的名称及数量
使用上海产的零件的工程的名称
所有项目的使用零件的情况（无论有没有使用零件），包括项目代码、项目名称、零件代码和零件数量
供应商S1和S3供应的相同的零件编号
没有使用天津产的零件的工程号码
没有使用天津供应商生产的红色零件的工程号
至少用了供应商S1所供应的全部零件的工程号Jno

三. 实验过程与代码

  查询供应商号码为S1的供应商的名称SNAME，所在城市CITY

SELECT SNAME,CITY
FROM s
WHERE sno='s1';

```
  查询颜色为红色的零件号码
```

SELECT PNO 零件号码
FROM p
WHERE color='红';

  查询工程所在地为天津的工程名称JNAME

SELECT JNAME
FROM j
WHERE city='天津';

  查询供应商号和名称，分别用小写字母和大写字母表示供应商代码

--1)    小写字母表示供应商代码：
SELECT LOWER(SNO)小写字母表示供应商代码,SNAME 名称
FROM s
--2)    大写字母表示供应商代码：
SELECT UPPER(SNO)大写字母表示供应商代码,SNAME 名称
FROM s

  查询零件的详细信息，用结果属性名用中文显示。

SELECT PNO 零件代码,PNAME 零件名,COLOR 颜色,WEIGHT 重量 
FROM p

  查询供应工程J1零件P1的供应商号SNO

SELECT SNO
FROM spj
WHERE JNO='J1'AND PNO='P1';

  查询供应商的名称和所在城市，并按照所在城市升序排序，同一个城市的按照供应商的名称降序排序

SELECT SNAME,CITY
FROM s
ORDER BY CITY,SNAME DESC;

如果排序结果感觉跟习惯不同，原因是MySQL编码格式造成的，们只需要将utf的编码格式换成gb2312（如下图所示），排序规则换成gb2312_chinese_ci就可以实现看似正常一点的排序结果了

  查询使用供应商S1所供应零件的工程号码

SELECT DISTINCT JNO
FROM spj
WHERE SNO='S1';

```
  查询各种零件的平均重量
```

--按名字分类
SELECT PNAME,avg(WEIGHT)平均重量
FROM p
GROUP BY PNAME

查询零件的总个数

SELECT COUNT(*) 零件总数
FROM p

查询所有以“螺”字开头的零件的零件号、零件名和颜色

SELECT PNO 零件号,PNAME 零件名,COLOR 颜色
FROM p
WHERE PNAME LIKE'螺%';

查询各个供应商供应的零件P3总数量

SELECT  SNO,SUM(QTY)零件P3总数量
FROM spj
WHERE PNO='P3'
GROUP BY SNO
    --“=”可以用“in”来代替：
SELECT  SNO,SUM(QTY)零件P3总数量
FROM spj
WHERE PNO in ('P3')
GROUP BY SNO
    --“not in”也可以实现：
SELECT  SNO,SUM(QTY)零件P3总数量
FROM spj
WHERE PNO NOT in('P1','P2','P4','P5','P6')
GROUP BY SNO
--但是，怎样显示所有供应商呢（没有供应P3的显示为空）?这就需要进行s表与spj表的连接了
SELECT s1.SNO,SUM(QTY)零件P3总数量
FROM s s1 LEFT OUTER JOIN (
    SELECT * 
    FROM spj 
    WHERE PNO='P3') s2 ON s1.SNO=s2.SNO
GROUP BY SNO

供应工程J1红色零件的供应商号SNO

--左外连接：
SELECT SNO
FROM p LEFT OUTER JOIN spj ON p.PNO=spj.PNO
WHERE spj.JNO='J1' AND p.color='红'
--等值连接：
SELECT sno
FROM p,spj
WHERE p.pno=spj.pno AND jno='j1' AND color='红'
--嵌套查询
SELECT sno
FROM spj
WHERE jno='j1' AND pno in(
                        SELECT pno
                        FROM p
                            WHERE color='红')

工程项目J2使用的各种零件的名称及数量

--左外连接：
SELECT p.PNAME,spj.QTY 
FROM spj LEFT OUTER JOIN p ON spj.PNO=P.PNO 
WHERE JNO='J2';
--等值连接：
SELECT PNAME 零件名,QTY 数量
FROM spj,p  
WHERE  spj.PNO=P.PNO and JNO='J2';

使用上海产的零件的工程的名称

--三个表的左外连接：
SELECT  DISTINCT j.JNAME
FROM spj LEFT OUTER JOIN s ON spj.SNO=s.SNO LEFT OUTER JOIN j ON spj.JNO=j.JNO
WHERE s.CITY='上海';
--三层嵌套查询：
SELECT DISTINCT JNAME
FROM j
WHERE jno in(SELECT jno 
            FROM spj 
            WHERE sno in(SELECT sno 
                         FROM s 
                         WHERE city='上海'));
--三个表的等值连接：
SELECT DISTINCT JNAME
FROM j,s,spj
WHERE j.JNO=spj.JNO AND spj.SNO=s.SNO AND s.CITY='上海'

所有项目的使用零件的情况（无论有没有使用零件），包括项目代码、项目名称、零件代码和零件数量

SELECT  J1.JNO 项目代码,J1.JNAME 项目名称,SPJ1.PNO 零件代码,SPJ1.QTY 零件数量
FROM j J1  LEFT OUTER JOIN spj SPJ1 ON SPJ1.JNO=J1.JNO 
ORDER BY J1.JNO;

供应商S1和S3供应的相同的零件编号

--内连接：
SELECT DISTINCT spj1.PNO 
FROM spj spj1  INNER JOIN  spj spj2 ON spj1.PNO=spj2.PNO 
WHERE spj1.SNO='S1' AND spj2.SNO='S3';
--旧式内连接：
SELECT DISTINCT `FIRST`.PNO
FROM spj FIRST,spj SECOND
WHERE `FIRST`.PNO=`SECOND`.PNO AND `FIRST`.SNO='S1'AND `SECOND`.SNO='S3'

没有使用天津产的零件的工程号码

--EXISTS
SELECT JNO 
FROM j  
WHERE NOT EXISTS (
                        SELECT * 
                        FROM spj 
                        WHERE SNO IN (
                                    SELECT SNO 
                                    FROM s 
                                    WHERE CITY='天津') AND j.JNO=spj.JNO);
--嵌套查询：
SELECT jno
FROM j
WHERE jno not in(
        SELECT  DISTINCT JNO
        FROM spj
        WHERE sno in(SELECT sno 
                    FROM s
                    WHERE CITY='天津'));

没有使用天津供应商生产的红色零件的工程号

--Exist方法：
SELECT JNO 
FROM j  
WHERE NOT EXISTS (
                    SELECT * 
                    FROM spj 
                    WHERE SNO IN (
                                SELECT SNO 
                                FROM s 
                                WHERE CITY='天津' ) AND PNO IN (
                                                    SELECT PNO 
                                                    FROM p 
                                                    WHERE COLOR='红') AND 
                                                    j.JNO=spj.JNO);
--嵌套查询：
SELECT jno
FROM j
WHERE jno not in(
                SELECT  DISTINCT JNO
                FROM spj
                WHERE sno in(SELECT sno 
                                    FROM s
                                    WHERE CITY='天津')
                   AND pno in(SELECT pno 
                            FROM p
                            WHERE color='红'));

至少用了供应商S1所供应的全部零件的工程号Jno

SELECT DISTINCT JNO
FROM spj spj1
WHERE NOT EXISTS
(SELECT * 
 FROM spj spj2 
 WHERE spj2.SNO='S1' AND NOT EXISTS
(SELECT * 
 FROM spj spj3 
 WHERE spj3.JNO=spj1.JNO AND spj3.PNO=spj2.PNO)
);

四. 实验总结

查询结果列别名，只需要在select 列名后面+空格，+列别名就可以了。
```
 列表项后面＋空格+[表头名]，也可以用as代替空格。
```
用小写字母表示字符串用LOWER(SNO)，用大写字母表示字符串用UPPER(SNO)。
MySQL的字符集和排列规则不同，排序结果是不同的。
求平均值的函数：avg()
统计个数的函数：count()
模糊查询，用LIKE，不是“=”，%代表任意数量的字符，_(下划线)代表一个字符的位置。
等值连接和左外连接都是将几个表全连接起来，再根据条件查询。
很多多表查询用嵌套查询非常方便，可以一层一层分析，比较容易。注意：当查询结果属于一个表里的时候才可以用嵌套查询。
Exist查询的原理可以认为类似一个for循环，比如在18题当中，从j1到j7依次带入查询，通过exist判断是否符合条件，符合天津产的零件的条件，exist为true，not exist为false，不输出，不符合天津产的零件的条件时，not exist为true，就可以输出。（仅仅是理解方法，表述不一定恰当）。

标签：数据库 database mysql

本文转载自: https://blog.csdn.net/pzcxl/article/details/124661754
版权归原作者 想简单点了 所有，如有侵权，请联系我们删除。

数据库实验二之数据查询

发表评论

“数据库实验二之数据查询”的评论:

关于作者

overfit同步小助手

相关阅读

文章导航