软件工程日报（6.7）

今日学习内容：

（1）查询所有供应商情况，先按城市升序排列，城市相同按供应商名称降序排列。
Sql
SELECT * FROM s ORDER BY city ASC, sname DESC;
（2）查询所有零件情况，先按零件名称升序排列，零件名称相同按重量降序排列。
Sql
SELECT * FROM p ORDER BY pname ASC, weight DESC;
（3）查询项目名中含有“厂”的项目情况。
Sql
SELECT * FROM j WHERE jname LIKE '%厂%';
（4）查询供应商名称中第二个字为“方”的供应商情况。
Sql
SELECT * FROM s WHERE sname LIKE '_方%';
（5）查询所有零件中的最大、最小、平均重量。
Sql
SELECT MAX(weight) AS 最大重量, MIN(weight) AS 最小重量, AVG(weight) AS 平均重量 FROM p;
（6）查询零件中名为“螺丝刀”的零件的种类数、平均重量。
Sql
SELECT COUNT(*) AS 种类数, AVG(weight) AS 平均重量 FROM p WHERE pname = '螺丝刀';
（7）查询供应商 S1 所供应的各种零件的名称和数量。
Sql
SELECT p.pname, spj.qty FROM spj JOIN p ON spj.pno = p.pno WHERE spj.sno = 'S1';
（8）查询工程 J1 所使用的各种零件的名称和数量。
Sql
SELECT p.pname, spj.qty FROM spj JOIN p ON spj.pno = p.pno WHERE spj.jno = 'J1';
（9）查询没有使用红色螺丝刀的工程名称。
Sql
SELECT DISTINCT j.jname FROM j LEFT JOIN spj ON j.jno = spj.jno
LEFT JOIN p ON spj.pno = p.pno WHERE p.color != '红色' OR p.pname != '螺丝刀';
（10）查询没有供应红色螺丝刀的供应商名称。
Sql
SELECT DISTINCT s.sname FROM s LEFT JOIN spj ON s.sno = spj.sno
LEFT JOIN p ON spj.pno = p.pno WHERE p.color != '红色' OR p.pname != '螺丝刀';
（11）查询所用零件数量超过 500 的工程项目号。
Sql
SELECT jno FROM spj GROUP BY jno HAVING SUM(qty) > 500;
（12）查询所用零件种类超过 3 种的工程项目名称。
Sql
SELECT j.jname FROM j JOIN spj ON j.jno = spj.jno GROUP BY j.jname HAVING COUNT(DISTINCT spj.pno) > 3;
（13）查询使用了全部零件的工程项目名称。（这个查询较为复杂，假设“全部零件”指的是至少使用了每个种类的零件一次）
Sql
WITH AllParts AS (
SELECT COUNT(DISTINCT pno) AS total_parts FROM p
),
ProjectParts AS (
SELECT jno, COUNT(DISTINCT pno) AS used_parts
FROM spj GROUP BY jno
)
SELECT j.jname FROM j JOIN ProjectParts pp ON j.jno = pp.jno, AllParts ap
WHERE pp.used_parts = ap.total_parts;
（14）查询至少供应了工程 J1 所使用的全部零件的供应商名称。（同样，这里假设“全部零件”指J1使用的每种零件至少供应一次）
Sql
WITH J1Parts AS (
SELECT pno FROM spj WHERE jno = 'J1'
),
SupplierCoverage AS (
SELECT sno, COUNT(DISTINCT pno) AS supplied_parts
FROM spj WHERE sno IN (SELECT DISTINCT sno FROM spj WHERE jno = 'J1')
GROUP BY sno
)
SELECT s.sname FROM s JOIN SupplierCoverage sc ON s.sno = sc.sno, J1Parts jp
WHERE sc.supplied_parts = (SELECT COUNT(*) FROM J1Parts);
（15）查询供应情况，显示内容为供应商名称、零件名、工程名称、数量。
Sql
SELECT s.sname, p.pname, j.jname, spj.qty FROM spj
JOIN s ON spj.sno = s.sno JOIN p ON spj.pno = p.pno JOIN j ON spj.jno = j.jno;
（16）查询“东方红”供应商供应情况，显示供应的零件名、工程名称、数量。
Sql
SELECT p.pname, j.jname, spj.qty FROM spj
JOIN s ON spj.sno = s.sno AND s.sname = '东方红'
JOIN p ON spj.pno = p.pno JOIN j ON spj.jno = j.jno;

首先，我们为机车厂工程项目创建一个供应情况的视图，命名为V_Supplier_Part_Info，该视图将包含供应商代码（Sno），零件代码（Pno）以及供应数量（Qty）。

Sql
CREATE VIEW V_Supplier_Part_Info AS
SELECT spj.sno, spj.pno, spj.qty
FROM spj
JOIN j ON spj.jno = j.jno
WHERE j.jname = '机车厂工程项目';
接下来，基于这个视图完成指定的查询任务。

（1）查询机车厂工程项目使用的各种零件代码及其数量。
Sql
SELECT Pno, SUM(Qty) AS TotalQuantity
FROM V_Supplier_Part_Info
GROUP BY Pno;
这条SQL语句从视图V_Supplier_Part_Info中选取所有的零件代码（Pno）并根据零件代码分组，计算每种零件的总供应数量。

（2）查询除供应商 S2 的供应情况。
Sql
SELECT *
FROM V_Supplier_Part_Info
WHERE sno <> 'S2';
此SQL语句从视图V_Supplier_Part_Info中选取所有记录，但排除了供应商代码为'S2'的记录，以展示除S2以外的供应商供应情况。

请确保在执行这些SQL命令之前，你的数据库环境已经正确设置了上述提到的表和数据，且已正确创建了视图。

1. 连接到服务器
启动SSMS后，会弹出“连接到服务器”对话框，输入服务器名称（可以是localhost或.SQLExpress等本地实例名），选择身份验证方式（Windows身份验证或SQL Server身份验证），如果选择SQL Server身份验证还需要输入用户名和密码，然后点击“连接”。
2. 创建数据库
右键点击“数据库”，选择“新建数据库…”。
在新建数据库对话框中，输入数据库名称，可以根据需要设置数据库文件（.mdf）和日志文件（.ldf）的路径、初始大小、自动增长等属性，然后点击“确定”。
3. 管理数据表
展开数据库 -> 表，右键点击“表”，选择“新建表”来创建新表，并在表设计器中定义列、约束、索引等。
右键现有表，可以进行“设计”（编辑表结构）、“查看数据”、“新建查询”（对表进行查询或修改数据）等操作。
4. 执行SQL查询
在对象资源管理器中找到目标数据库，右键选择“新建查询”或者直接使用快捷键（Ctrl+N），这会在新的查询窗口打开。
在查询窗口中编写SQL语句，如SELECT、INSERT、UPDATE、DELETE等，然后点击工具栏上的“执行”按钮或按F5运行查询。
5. 用户和权限管理
在“安全性”节点下，可以管理登录名、用户、角色等。
右键“登录名”或“用户”，选择“新建登录名”或“新建用户”来进行创建，同时可以在属性中分配服务器角色或数据库角色来控制访问权限。
6. 备份与恢复
右键数据库，选择“任务” -> “备份…”或“还原…”来进行数据库的备份或恢复操作。
在备份或还原界面中，根据提示选择备份设备、指定备份类型（完整、差异、事务日志等），设置备份文件路径等，然后执行备份或恢复操作。
7. 作业和维护计划
在“管理”菜单下，可以找到“SQL Server代理”来管理作业、警报和操作员。
通过“新建作业”可以安排定期执行的任务，如数据库备份、数据清理等维护任务。
8. 性能监控与优化
使用“活动监视器”查看当前SQL Server的性能指标，如CPU、内存、I/O使用情况。
利用“数据库引擎优化顾问”进行数据库性能分析和索引建议。