数据查询的一些问题

数据查询

---

1. 消息 208，级别 16，状态 1，第 1 行
   对象名 ‘Salvaging’ 无效。

   思路:没有更改数据库

2. 根据时间的范围查询记录

   select * from Salvaging where end_date>'2011-1-3' and prj_status=1
   

3. 查询“观澜站光缆抢修”工程中使用的所有物资名称及物资规格。（实验二第19 题）

   🔥涉及三个表，三表连接的问题

   • 物资名称，物资的规格在Stock表
   • 根据“观澜站光缆抢修”，需要查询Salvaging表 工程名只有该表有
   • 想要知道抢修用了什么，就要Out_Stock

   SELECT mat_name,speci FROM Stock s1,Out_Stock s2,Salvaging s3 WHERE s1.mat_num=s2.mat_num and s2.prj_num=s3.prj_num and s3.prj_name='观澜站光缆抢修’
   

4. 查询每个抢修工程的工程号、工程名以及所使用的物资种类数。没有使用任何物资的工程，输出的物资种类数为0。（实验二第20题）

   【关键词】分组，统计，左外连接

   第一步：看出涉及两个表，一个是Salvaging，另一个是Out_Stock

   第二步：考虑怎么连接，是外连接还是自然连接显然是外连接，因为需要保证输出的物质类数为零

   第三步：分组用GROUP BY，统计用COUNT(s2.prj_num)

   第四步：按照工程名和工程号分组

   SELECT  s1.prj_num,prj_name,COUNT(s2.prj_num)
   	FROM Salvaging s1 LEFT JOIN Out_Stock s2
   	ON s1.prj_num=s2.prj_num
   	GROUP BY s1.prj_num,prj_name
   

5. 查询同时被工程号为“20100015”及“20100016”的工程所使用的物资编号。（请写出两种不同的SQL语句）（自身连接）

   第一种：自身连接

   select s1.mat_num from Out_Stock s1,Out_Stock s2 where s1.mat_num=s2.mat_num and s1.prj_num=20100015 and s2.prj_num=20100016
   

   第二种：嵌套查询

   select mat_num from Out_Stock where prj_num=20100016 and  mat_num in (select mat_num from Out_Stock where  prj_num=20100015)
   

6. 查询与规格为“bvv-120”、且名称的前几个字符为“户外_真空”的物资存储在同一个仓库的物资名称、规格与数量。（请写出三种不同的SQL语句）

   【关键词】嵌套，自身连接 模糊查询

   第一步：涉及Stock 表，单表。并且涉及自身连接（或者嵌套查询）

   第一种：嵌套查询

   SELECT mat_name,speci,amount FROM Stock WHERE warehouse in (select warehouse FROM Stock WHERE speci='bvv-120' and mat_name like '户外\_真空%' ESCAPE'\')
   

   第二种：自身连接

   SELECT s2.mat_name,s2.speci,s2.amount FROM Stock s1,Stock s2 WHERE s1.warehouse=s2.warehouse and s1.speci='bvv-120' and s1.mat_name like '户外\_真空%' ESCAPE'\'
   

   第三种：EXISTS谓词子查询(值得学习)

   SELECT mat_name,speci,amount FROM Stock S1 WHERE  EXISTS ( SELECT  * FROM Stock WHERE  S1.warehouse=warehouse AND speci='bvv-120' AND mat_name like '户外\_真空%'  ESCAPE '\'  )
   

   EXISTS谓词子查询：只返回true 或false

7. 查询其他仓库比“供电局1#仓库”的所有物资库存量少的物资名称、规格和数量。、

   【关键词】all谓词子查询

   SELECT mat_name,speci,amount
   FROM Stock s1
   WHERE amount < all
   	(SELECT amount
   	FROM Stock 
   	WHERE warehouse = '供电局1#仓库')6
       and warehouse <> '供电局1#仓库'
   

8. 使用exists函数，查询没有使用“m002”号物资的工程项目名称

   【关键词】 EXISTS子查询

   SELECT prj_name
   FROM Salvaging s1
   WHERE not EXISTS
   	(SELECT *
   	FROM Out_Stock 
   WHERE Out_Stock.prj_num=s1.prj_num and mat_num='m002')
   

9. 🔥请写出“被所有的工程都使用了的物资编号、物资名称及规格”的关系代数表达式以及对应的SQL语句（请写出三种不同的SQL语句）

   【关键词】除法 投影 连接； NOT EXIST全称量词；

   关系代数表达式:

   Πmat_num,prj_num(Out_stock)÷Πprj_num(Salvaging) ⋈ Πmat_num,mat_name,speci(Stock)

   第一种：

   SELECT mat_num,mat_name,speci FROM Stock s1
   WHERE not exists (SELECT * FROM Salvaging s2 WHERE not exists
   (SELECT * FROM Out_Stock WHERE mat_num=s1.mat_num and prj_num=s2.prj_num))
   

   第二种：

   SELECT mat_num,mat_name,speci FROM Stock s1
   WHERE (SELECT count(*) FROM Out_Stock WHERE mat_num=s1.mat_num)
   = (SELECT count(*) FROM Salvaging) 
   

   第三种：

   select mat_num,mat_name,speci from Stock 
   where mat_num in(select mat_num from Out_Stock group by mat_num having COUNT(*)
   =(select count(*) from Salvaging))
   

10. 使用TOP语句，查询抢修工程salvaging表中第4条到第7条记录。

    select TOP 4 * from Salvaging where prj_num not in (select top 3 prj_num from Salvaging ORDER BY prj_num) ORDER BY prj_num
    

11. 分页的实现。假设每个页面显示3条记录，请输出显示在第2页（页编号从1开始）的stock表中的记录。

    【关键词】 分页 OFFSET FETCH

    SELECT * FROM Stock ORDER BY mat_num OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;
    

OFFSET子句指定要跳过的行数；

FETCH子句指定在处理**OFFSET子句后要返回的行数。**

12. 已知每页显示3条记录，请分别使用TOP和OFFSET/FETCH实现out_stock表的第二页的记录输出。

    第一：OFFSET/FETCH

    SELECT * FROM out_stock ORDER BY mat_num OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;
    

    第二：TOP

    【关键字】prj_num+mat_num

    SELECT TOP 3 * FROM Out_Stock 
    WHERE prj_num+mat_num NOT IN(
             SELECT TOP 3 prj_num+mat_num FROM Out_Stock
             ORDER BY prj_num,mat_num)
    ORDER BY prj_num,mat_num
    
    

13. 设有如下脚本，请使用左外连接（LEFT JOIN）实现 a-b（a与b的差集）的输出。输出结果为：

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YAEw0oIM-1590755448274)(F:\my study\数据库课程\笔记\数据库一系列问题\ZW8VZ3B5GBDQ6BD{DD$]H6-1590754690516.jpg)]

    【关键词】差运算 ；左外连接

    --创建表a
    CREATE TABLE a(
      id INT IDENTITY(1,1),
      oname VARCHAR(50),
      odesc VARCHAR(50)
    );
    --创建与表a具有相同结构的表b
    SELECT * INTO b FROM a;
    --插入记录
    INSERT INTO a(oname,odesc)
    VALUES('n1','o1'),('n2','o2'),('n3','o3');
    INSERT INTO b(oname,odesc)
    VALUES('n2','o2'),('n3','o3'),('n4','o4');
    

    差运算的查询代码（左连接时，应对除了id外的所有字段进行比较）：

   SELECT a.oname,a.odesc
   FROM a LEFT JOIN b 
   ON a.oname = b.oname AND a.odesc = b.odesc 
   WHERE b.id IS NULL

相同的如果左连接，不会出现NULL。如果不相同的话，根据左连接，bid为NULL