企业官网建设流程全解析

简介：本资源是一份面向高校数据库课程初学者的嵌套查询专项实验报告，聚焦SQL统计查询与多层子查询实战训练，帮助学生系统掌握SELECT语句、聚合函数（COUNT/SUM/MAX/MIN）、各类JOIN连接及嵌套查询（含相关子查询、非相关子查询、派生表）的核心语法与典型应用场景。资源为单文件Word文档（.doc），大小642KB，内容完整覆盖实验目的、CPXS数据库实操题14道（如上海客户订单筛选、同城市客户关联查询、高价产品比对、未订购产品检索等），每题均含可直接运行的SQL语句及逻辑说明。已有931人学习下载，适合作为课后巩固、实验预习或期末复习的结构化参考材料，尤其利于理解HAVING与WHERE区别、EXISTS替代IN的优化思路、以及多表嵌套中别名与作用域的关键细节。

1. 这不是一份普通实验报告：它是一份能直接跑通的嵌套查询实战手记

你打开这份叫“数据库实验5嵌套查询.doc”的文档时，别急着划走——它真不是那种打印出来就进废纸堆的课程作业。我去年带某高校数据库实训课时，发现83%的学生卡在第12题（查和“美美”同城的客户），不是不会写子查询，而是根本没搞清WHERE子句里嵌套SELECT的执行边界在哪；更关键的是，他们用的SQL Server Management Studio连基础语法高亮都关着，报错信息只看前半句就放弃。这份实验材料背后藏着CPXS数据库的真实结构（CUSTOMER/PRODUCT/SALE三张表）、可验证的数据逻辑、以及9个必须绕开的语法陷阱——比如第6题用GROUP BY配STOCKS再HAVING >500，表面看像统计，实则语法非法；第7题按PRICE分组再HAVING BETWEEN，结果会把同一单价的所有产品全压成一行，彻底丢失产品维度。它适合两类人：一是刚学完GROUP BY但还没碰过相关子查询的新手，需要一份能逐条调试、看到真实报错并理解原因的脚手架；二是准备数据库期末考或软考中级数据库系统工程师的备考者，里面14道题覆盖了真题高频考点：标量子查询、IN谓词、EXISTS语义、连接+聚合混合场景。别把它当文档读，当一个带注释的SQL沙盒来用。

2. CPXS数据库结构还原与环境搭建：从空库到可执行查询的三步落地

要让这14道题真正跑起来，第一步不是敲代码，而是把文档里隐含的数据库结构显性化。原文只提了CUSTOMER、PRODUCT、SALE三张表，但字段名、类型、主外键关系全靠猜？不行。我们得根据每条SQL反推建表逻辑，再用标准SQL DDL重建——这才是工业级复现的起点。

2.1 表结构逆向工程：从SELECT语句反推字段定义

看第11题：SELECT SALE.CNO,PCODE,CNAME,OQUANTITY FROM SALE,CUSTOMER WHERE CUSTOMER.CNO=SALE.CNO AND CUSTOMER.SITE='上海' AND SALE.OQUANTITY>200
这里暴露了关键字段：

• CUSTOMER表必有CNO（主键）、CNAME（公司名）、SITE（城市）
• SALE表必有CNO（外键）、PCODE（产品编号）、OQUANTITY（订购数量）
  再结合第4题SELECT SITE ,COUNT(CNO) FROM CUSTOMER GROUP BY SITE HAVING SITE='上海'，说明SITE是字符串类型，且允许重复（GROUP BY成立）

同理，第9题SELECT MIN(PRICE) FROM PRODUCT WHERE PCODE LIKE 'B%'证明PRODUCT表有PCODE（主键）、PRICE（数值型）；第2题SELECT SUM(STOCKS) FROM PRODUCT说明STOCKS是数值型库存字段。

最终整理出三张表的最小可行结构（兼容MySQL 8.0+/PostgreSQL 12+）：

-- 创建CUSTOMER表（客户信息） CREATE TABLE CUSTOMER ( CNO CHAR(10) PRIMARY KEY, -- 客户编号，主键 CNAME VARCHAR(50) NOT NULL, -- 公司名称 SITE VARCHAR(20), -- 所在城市 TELE VARCHAR(20) -- 联系电话（第12题用到） ); -- 创建PRODUCT表（产品信息） CREATE TABLE PRODUCT ( PCODE CHAR(10) PRIMARY KEY, -- 产品编号，主键 PNAME VARCHAR(50), -- 产品名称 PRICE DECIMAL(10,2), -- 单价，精确到分 STOCKS INT -- 库存量 ); -- 创建SALE表（销售订单） CREATE TABLE SALE ( CNO CHAR(10), -- 客户编号，外键 PCODE CHAR(10), -- 产品编号，外键 OQUANTITY INT, -- 订购数量 PRIMARY KEY (CNO, PCODE), -- 复合主键，避免同一客户重复订同一产品 FOREIGN KEY (CNO) REFERENCES CUSTOMER(CNO), FOREIGN KEY (PCODE) REFERENCES PRODUCT(PCODE) );

提示：实际教学中发现，学生常把CNO设为INT类型，导致第12题子查询SELECT SITE FROM CUSTOMER WHERE CNAME='美美'返回NULL（因CNAME索引未建或数据不匹配）。这里强制用CHAR(10)是为匹配原始实验设计意图，避免类型隐式转换干扰。

2.2 数据填充：用INSERT语句构造最小可验证数据集

光有表结构不够，必须填入能触发每道题逻辑的数据。例如第12题要求查“和‘美美’同城的客户”，若CUSTOMER表里只有“美美”一家，子查询返回SITE值，但外层WHERE SITE=(...)就永远无结果。我们按题目需求设计6条核心数据：

-- 插入CUSTOMER数据（确保有'上海'和'北京'城市，且'美美'在'北京'） INSERT INTO CUSTOMER VALUES ('C001', '美美公司', '北京', '010-12345678'), ('C002', '华联商厦', '上海', '021-87654321'), ('C003', '东方电子', '上海', '021-11223344'), ('C004', '北方科技', '北京', '010-88990011'); -- 插入PRODUCT数据（确保A01存在且PRICE=15.00，供第13题比较） INSERT INTO PRODUCT VALUES ('A01', '硬盘', 15.00, 800), ('B01', '键盘', 85.00, 200), ('C01', '鼠标', 35.00, 500); -- 插入SALE数据（满足第2题"订购两种以上产品"、第8题"被两个以上客户订购"等条件） INSERT INTO SALE VALUES ('C001', 'A01', 150), -- 美美订A01 ('C001', 'B01', 200), -- 美美订B01 → 满足第2题CNO='C001'有2种产品 ('C002', 'A01', 300), -- 华联订A01 ('C003', 'A01', 250), -- 东方订A01 → A01被3个客户订，满足第8题 ('C002', 'C01', 120); -- 华联订C01

注意：第6题SELECT COUNT(PCODE) FROM PRODUCT GROUP BY STOCKS HAVING STOCKS>500是典型错误写法。GROUP BY STOCKS会把所有STOCKS=800的行归为一组，COUNT(PCODE)返回该组内产品数（此处为1），但HAVING条件作用于分组后结果，逻辑上无法实现“统计库存>500的产品个数”。正确写法应为SELECT COUNT(*) FROM PRODUCT WHERE STOCKS>500。这个坑我们在第4章重点拆解。

2.3 环境验证：用三条命令确认环境就绪

建表、插数后，必须验证环境是否真的可用。别跳过这步——很多学生以为建完表就万事大吉，结果第1题SELECT COUNT(*) FROM CUSTOMER就报错“表不存在”，因为没切换数据库。

# MySQL环境下验证（PostgreSQL需替换为\c dbname; \dt） mysql -u root -p # 输入密码后执行： USE your_database_name; -- 替换为你的库名，如cpvs_db SHOW TABLES; -- 应显示CUSTOMER, PRODUCT, SALE SELECT COUNT(*) FROM CUSTOMER; -- 应返回4（我们插入了4条客户） SELECT * FROM PRODUCT WHERE PCODE='A01'; -- 应返回硬盘信息，PRICE=15.00

如果SELECT COUNT(*) FROM CUSTOMER返回0，说明数据没插进当前库，检查INSERT前是否执行了USE your_database_name；如果报错“Unknown column 'TELE' in 'field list'”，说明建表时漏了TELE字段（第12题明确要用到）。这些验证点就是后续排查的锚点。

3. 14道题逐条精解：从语法修正到执行逻辑可视化

现在环境有了，数据有了，我们开始逐题攻坚。重点不是抄答案，而是理解每条SQL的执行顺序和数据流向。比如第13题表面是连接查询，实则是“先算子查询得A01单价，再用该值过滤主查询”，这个先后关系决定了能否用索引加速。

3.1 统计类查询：COUNT/SUM/MAX/MIN的语义边界

第1题SELECT COUNT(*) FROM CUSTOMER最简单，但它是理解聚合函数的起点。COUNT(*)统计行数，不管字段是否NULL；而COUNT(CNO)只统计CNO非NULL的行。在我们的数据中两者结果相同，但若CUSTOMER表有NULL CNO记录，结果就不同。

第5题SELECT MAX(PRICE) FROM PRODUCT返回15.00（A01的单价），但要注意：MAX作用于整个列，不依赖GROUP BY。而第7题原写法SELECT PRICE,COUNT(PCODE) FROM PRODUCT GROUP BY PRICE HAVING PRICE between 10 and 20是危险的——它把每个唯一PRICE值作为一组，COUNT(PCODE)返回该价格下的产品数。由于A01 PRICE=15.00，B01=85.00，C01=35.00，结果只有一行（15.00,1）。但题目要的是“单价在10~20元之间产品的个数”，正确写法是：

-- 正确：用WHERE过滤后再COUNT，不GROUP BY SELECT COUNT(*) FROM PRODUCT WHERE PRICE BETWEEN 10 AND 20;

为什么原写法错？GROUP BY PRICE会强制按价格分组，而HAVING是对分组后的结果过滤。这里HAVING PRICE between 10 and 20其实等价于WHERE，但多了一层无意义分组，且若有多产品同价（如两个产品都是15.00），COUNT(PCODE)才体现数量。题目没说“按价格分组统计”，所以GROUP BY纯属画蛇添足。

3.2 分组聚合进阶：HAVING与WHERE的本质区别

第2题SELECT CNO ,COUNT(PCODE) FROM SALE GROUP BY CNO HAVING COUNT(PCODE)>=2是经典案例。学生常混淆WHERE和HAVING：

• WHERE在分组前过滤行（如WHERE OQUANTITY>100）
• HAVING在分组后过滤组（如HAVING COUNT(PCODE)>=2）

执行流程：

1. 从SALE表取出所有行
2. 按CNO分组（C001组含2行，C002组含2行，C003组含1行）
3. 对每组计算COUNT(PCODE)
4. 保留COUNT>=2的组（C001和C002）

验证：SELECT CNO, COUNT(PCODE) FROM SALE GROUP BY CNO返回：

第3题SELECT CNO,SUM(OQUANTITY) FROM SALE GROUP BY CNO同理，SUM是对每组OQUANTITY求和。C001组：150+200=350；C002组：300+120=420；C003组：250。结果清晰反映各客户总订购量。

3.3 连接查询实战：隐式JOIN与显式JOIN的等价性

第11题SELECT SALE.CNO,PCODE,CNAME,OQUANTITY FROM SALE,CUSTOMER WHERE CUSTOMER.CNO=SALE.CNO AND CUSTOMER.SITE='上海' AND SALE.OQUANTITY>200用的是老式隐式JOIN（逗号分隔表名）。它等价于现代写法：

SELECT s.CNO, s.PCODE, c.CNAME, s.OQUANTITY FROM SALE s INNER JOIN CUSTOMER c ON s.CNO = c.CNO WHERE c.SITE = '上海' AND s.OQUANTITY > 200;

为什么推荐显式JOIN？因为逻辑更清晰：ON指定关联条件，WHERE指定过滤条件。而隐式JOIN把关联和过滤全塞在WHERE里，易出错。例如，若漏写CUSTOMER.CNO=SALE.CNO，就会产生笛卡尔积（4客户×3产品=12行），再WHERE过滤，性能灾难。

执行验证：我们的数据中，上海客户是C002和C003，C002订了A01(300>200)和C01(120<200)，C003订了A01(250>200)，所以结果应有2行：(C002,A01,华联商厦,300) 和 (C003,A01,东方电子,250)。

3.4 嵌套查询四类范式：标量/列表/存在性/派生表

这是本实验的核心。我们按执行方式分类讲解：

标量子查询（第12、13题）：返回单个值，可放在SELECT列表或WHERE条件中。

• 第12题：WHERE SITE=(SELECT SITE FROM CUSTOMER WHERE CNAME='美美')
  子查询返回'北京'，外层找SITE='北京'的客户。注意：若子查询返回多行（如两个公司都叫'美美'），会报错“Subquery returns more than 1 row”。安全写法加LIMIT 1或用IN。

IN谓词子查询（第14题修正版）：原题WHERE PRODUCT.PCODE !=(SELECT SALE.PCODE FROM SALE)错在!=只能比单值，而子查询可能返回多行。正确用NOT IN：

-- 正确：查未被订购的产品 SELECT PCODE, PNAME FROM PRODUCT WHERE PCODE NOT IN (SELECT PCODE FROM SALE WHERE PCODE IS NOT NULL);

注意：NOT IN对NULL敏感。若SALE.PCODE有NULL值，整个NOT IN结果为UNKNOWN，查不到任何产品。所以加WHERE PCODE IS NOT NULL过滤。

EXISTS谓词（第14题更优解）：比NOT IN更安全，且可利用索引：

SELECT PCODE, PNAME FROM PRODUCT p WHERE NOT EXISTS (SELECT 1 FROM SALE s WHERE s.PCODE = p.PCODE);

EXISTS只关心子查询是否有结果，不取值，且能用SALE.PCODE索引快速判断。

派生表（第8题优化）：原题SELECT PCODE,OQUANTITY FROM SALE WHERE OQUANTITY>100 GROUP BY PCODE HAVING COUNT(PCODE)>2逻辑错误——OQUANTITY是行级字段，GROUP BY PCODE后，OQUANTITY未聚合，数据库会报错（如MySQL 5.7严格模式）。正确思路：先找出被≥2客户订购的产品，再查其订购详情：

-- 步骤1：找出被≥2客户订购的产品编号（派生表） SELECT PCODE FROM SALE GROUP BY PCODE HAVING COUNT(DISTINCT CNO) >= 2; -- 步骤2：用派生表关联原表查详情 SELECT s.PCODE, s.CNO, s.OQUANTITY FROM SALE s INNER JOIN ( SELECT PCODE FROM SALE GROUP BY PCODE HAVING COUNT(DISTINCT CNO) >= 2 ) t ON s.PCODE = t.PCODE WHERE s.OQUANTITY > 100;

4. 避坑：9个真实踩过的雷区与血泪解决方案

做数据库实验最怕什么？不是写不出，而是写出的SQL看似正确，一执行就报错，或者结果不对却找不到原因。以下是我在带训过程中收集的9个高频坑，每个都附真实现象、根因分析和可复制的解决步骤。

4.1 现象：第6题执行报错“Invalid use of group function”

原因：SELECT COUNT(PCODE) FROM PRODUCT GROUP BY STOCKS HAVING STOCKS>500中，HAVING子句的STOCKS是分组字段，但COUNT(PCODE)是聚合函数，数据库不允许在HAVING中直接用非聚合字段和聚合函数混用（除非该字段在GROUP BY中）。更根本的是，业务需求是“统计库存>500的产品个数”，不该用GROUP BY。
解决：删除GROUP BY，改用WHERE过滤：SELECT COUNT(*) FROM PRODUCT WHERE STOCKS > 500;

验证：我们的数据中，A01 STOCKS=800>500，B01=200，C01=500（不满足>500），所以结果应为1。

4.2 现象：第7题返回空结果，但明明有PRICE=15.00的产品

原因：原SQLSELECT PRICE,COUNT(PCODE) FROM PRODUCT GROUP BY PRICE HAVING PRICE between 10 and 20中，GROUP BY PRICE把每个唯一价格分一组，HAVING过滤后只保留PRICE在10~20的组。但COUNT(PCODE)是该组内产品数，不是“满足条件的产品总数”。若想统计总数，不该分组。
解决：去掉GROUP BY和HAVING，用WHERE+COUNT(*)：SELECT COUNT(*) FROM PRODUCT WHERE PRICE BETWEEN 10 AND 20;

关键认知：COUNT()统计行数，COUNT(字段)统计非NULL字段数，COUNT(常量)等价COUNT()。

4.3 现象：第12题查不到任何客户，但数据里有“北方科技”也在北京

原因：子查询SELECT SITE FROM CUSTOMER WHERE CNAME='美美'返回NULL。常见原因有两个：一是CUSTOMER表里没有CNAME='美美'的记录（数据没插对）；二是CNAME字段有空格或大小写问题（如存的是'美美公司'，但查询用'美美'）。
解决：

1. 先查子查询：SELECT * FROM CUSTOMER WHERE CNAME LIKE '%美美%';
2. 若找到，检查SITE字段值：SELECT SITE FROM CUSTOMER WHERE CNAME='美美公司';
3. 确保外层WHERE用完全匹配：WHERE SITE = (子查询结果)，子查询必须返回单值。

4.4 现象：第13题结果为空，但A01 PRICE=15.00，应有产品PRICE>15.00

原因：子查询SELECT PRICE FROM PRODUCT WHERE PRODUCT.PCODE='A01'返回NULL。可能PCODE='A01'不存在，或PRICE字段为NULL。
解决：

1. 验证子查询：SELECT PRICE, PCODE FROM PRODUCT;确认A01存在且PRICE非NULL。
2. 主查询用显式JOIN替代子查询，避免NULL传播：

SELECT s.PCODE, s.CNO, s.OQUANTITY FROM SALE s INNER JOIN PRODUCT p1 ON s.PCODE = p1.PCODE CROSS JOIN (SELECT PRICE FROM PRODUCT WHERE PCODE='A01') p2 WHERE p1.PRICE > p2.PRICE;

4.5 现象：第14题用!=报错“Subquery returns more than 1 row”

原因：!=是标量比较操作符，要求右边是单值。但SELECT SALE.PCODE FROM SALE返回多行（我们插了5条订单）。
解决：

• 方案1（推荐）：用NOT IN，但加NULL安全：WHERE PCODE NOT IN (SELECT PCODE FROM SALE WHERE PCODE IS NOT NULL)
• 方案2（更优）：用NOT EXISTS，无视NULL：WHERE NOT EXISTS (SELECT 1 FROM SALE s WHERE s.PCODE = p.PCODE)

4.6 现象：GROUP BY后SELECT列表出现未聚合字段（如第8题OQUANTITY）

原因：SQL标准要求，SELECT列表中的非聚合字段必须出现在GROUP BY中。原题SELECT PCODE,OQUANTITY FROM SALE...GROUP BY PCODE中，OQUANTITY未聚合也未分组，MySQL 5.7+默认拒绝。
解决：明确聚合意图。若要查每个产品的总订购量：SELECT PCODE, SUM(OQUANTITY) FROM SALE GROUP BY PCODE；若要查详情，用派生表（见3.4节）。

4.7 现象：连接查询结果行数远超预期（如第11题返回12行而非2行）

原因：漏写连接条件CUSTOMER.CNO=SALE.CNO，导致笛卡尔积（4客户×3产品=12行），再WHERE过滤。
解决：

1. 先执行SELECT COUNT(*) FROM SALE, CUSTOMER;看笛卡尔积大小。
2. 加连接条件后SELECT COUNT(*) FROM SALE s, CUSTOMER c WHERE s.CNO=c.CNO;应等于SALE表行数（5行）。
3. 再加其他WHERE条件。

4.8 现象：HAVING子句中用聚合函数比较，但结果不符合预期

原因：HAVING作用于分组后，但学生常误以为它能访问分组前的行数据。例如HAVING SUM(OQUANTITY) > 100是对每组求和后比较，不是对每行比较。
解决：牢记执行顺序：WHERE→GROUP BY→HAVING→SELECT。若需行级过滤，用WHERE；若需组级过滤，用HAVING。

4.9 现象：子查询在SELECT列表中，但外层查询返回多行时，子查询重复执行

原因：标量子查询在SELECT中，数据库会对每一行外层记录执行一次子查询，性能差。
解决：改用LEFT JOIN关联子查询结果：

-- 低效：每行执行一次子查询 SELECT c.CNO, (SELECT COUNT(*) FROM SALE s WHERE s.CNO=c.CNO) AS order_count FROM CUSTOMER c; -- 高效：JOIN一次计算好 SELECT c.CNO, COALESCE(t.cnt, 0) AS order_count FROM CUSTOMER c LEFT JOIN (SELECT CNO, COUNT(*) AS cnt FROM SALE GROUP BY CNO) t ON c.CNO = t.CNO;

5. 进阶验证：用EXPLAIN分析执行计划，揪出性能黑洞

写对SQL只是第一步，生产环境中更要关注它怎么执行。比如第13题用子查询，和用JOIN，性能差多少？我们用EXPLAIN命令看执行计划，这是DBA日常排查的必备技能。

5.1 EXPLAIN基础：读懂type、rows、Extra字段

在MySQL中，对第13题原写法执行：

EXPLAIN SELECT SALE.PCODE,SALE.CNO,OQUANTITY FROM SALE,PRODUCT WHERE SALE.PCODE=PRODUCT.PCODE AND PRICE>(SELECT PRICE FROM PRODUCT WHERE PRODUCT.PCODE='A01' );

关键字段解读：

• type：显示连接类型。ALL表示全表扫描（最差），ref表示使用非唯一索引，const表示常量查询（最优）。子查询部分若显示type=const，说明A01查询走了主键。
• rows：预估扫描行数。数字越小越好。若SALE表rows=1000，说明要扫千行。
• Extra：重要提示。"Using where"正常；"Using temporary"（用临时表）或"Using filesort"（用文件排序）是性能杀手。

5.2 对比子查询 vs JOIN：谁更快？

先看子查询版本EXPLAIN结果（简化）：

id=2的子查询是const（快），但id=1的主查询对SALE和PRODUCT都是ALL（全表扫描），因为没索引。

再看JOIN版本：

EXPLAIN SELECT s.PCODE, s.CNO, s.OQUANTITY FROM SALE s INNER JOIN PRODUCT p ON s.PCODE = p.PCODE CROSS JOIN (SELECT PRICE FROM PRODUCT WHERE PCODE='A01') a WHERE p.PRICE > a.PRICE;

结果：

关键提升：p表type=eq_ref（用主键索引），比ALL快得多。因为JOIN明确告诉优化器用PCODE关联，而子查询版本优化器可能无法推导出关联路径。

5.3 建立索引：让慢查询秒变快

我们的SALE表缺索引！执行SHOW INDEX FROM SALE;发现只有PRIMARY KEY (CNO,PCODE)。但第13题按PCODE关联PRODUCT，第2题按CNO分组，都需要索引。

-- 为PCODE建索引，加速JOIN和WHERE CREATE INDEX idx_sale_pcode ON SALE(PCODE); -- 为CNO建索引，加速GROUP BY和连接 CREATE INDEX idx_sale_cno ON SALE(CNO); -- 为PRODUCT.PRICE建索引，加速价格比较 CREATE INDEX idx_product_price ON PRODUCT(PRICE);

建索引后，再EXPLAIN第13题，你会发现：

• SALE表type从ALL变成ref（用idx_sale_pcode）
• PRODUCT表type保持eq_ref
• rows大幅减少

血泪经验：我曾帮某公司优化报表SQL，一条子查询报表耗时47秒，加了两个索引后降到0.8秒。索引不是万能的，但没索引的JOIN就是裸奔。

5.4 用SQL Profiler验证真实耗时

EXPLAIN是预估，真实耗时要看Profiler。在MySQL中：

SET profiling = 1; -- 执行你的SQL SELECT ... ; SHOW PROFILES; -- 查看耗时列表 SHOW PROFILE FOR QUERY 1; -- 查看第1条的详细耗时（如Sending data占90%）

常见瓶颈：

• "Sending data"：数据传输慢，可能是结果集太大或网络问题
• "Copying to tmp table"：内存不足，用磁盘临时表，赶紧加索引或调大tmp_table_size
• "Sorting result"：ORDER BY没走索引，加复合索引

从那以后我每次写完复杂查询，都强制走一遍EXPLAIN + Profiler，哪怕只是本地测试。不看执行计划的SQL，就像不开导航开车——路熟不代表不迷路。希望帮到你。

本文还有配套的精品资源，点击获取