10.14
(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使用情况。
利用“数据库引擎优化顾问”进行数据库性能分析和索引建议。