實驗二 SQL 語言的使用
一、實驗目的：
掌握使用 SQL 語言進行各種查詢的操作和檢視的操縱方法。
二、實驗要求：
在現有的資料庫上進行各種查詢操作，對檢視的建立、使用等操作。
三、實驗步驟：
1、 開始→程式→Microsoft SQL Server→SQL Server Management Studio。
2、 在“連線到伺服器”對話方塊中，選擇“Windows 身份驗證”，點選“連線”，進
入 SQL Server Management Studio 操作介面。
3、單擊“新建查詢”按鈕，在上部的空白區匯入檔案“實驗二 SQL 語句.doc”中
的 SQL 語句，然後單擊工具欄上 “執行”按鈕（紅色歎號）。建立起實驗要操
作的資料庫 test1，並在庫中建立表、資料。下部的空白區顯示該語句的執行情
況。（注：以下操作均在上部的空白區輸入 SQL 語句，單擊執行後，下部的空
白區顯示該語句的執行情況。）
4、在資料庫 test1 中進行下列查詢操作，將查詢語句與結果寫入實驗報告。
（1）查詢所有供應商情況，先按城市升序排列，城市相同按供應商名稱降序排列。
（
2）查詢所有零件情況，先按零件名稱升序排列，零件名稱相同按重量降序排列。
（
3）查詢專案名中含有“廠”的專案情況。
（
4）查詢供應商名稱中第二個字為“方”的供應商情況。
（
5）查詢所有零件中的最大、最小、平均重量。
（
6）查詢零件中名為“螺絲刀”的零件的種類數、平均重量。
（
7）查詢供應商 S1 所供應的各種零件的名稱和數量。
（
8）查詢工程 J1 所使用的各種零件的名稱和數量。
（
9）查詢沒有使用紅色螺絲刀的工程名稱。
（10）查詢沒有供應紅色螺絲刀的供應商名稱。（11）查詢所用零件數量超過 500 的工程專案號。
（12）查詢所用零件種類超過 3 種的工程專案名稱。
（13）查詢使用了全部零件的工程專案名稱。
（14）查詢至少供應了工程 J1 所使用的全部零件的供應商名稱。
（15）查詢供應情況，顯示內容為供應商名稱、零件名、工程名稱、數量。
（16）查詢“東方紅”供應商供應情況，顯示供應的零件名、工程名稱、數量。
5、請為機車廠工程專案建立一個供應情況的檢視，包括供應商程式碼，零件程式碼，
供應數量。針對該試圖完成如下查詢並將 SQL 語句寫入實驗報告。
（
1）查詢機車廠工程專案使用的各種零件程式碼及其數量。
（
2）查詢處供應商 S2 的供應情況。
6、 在 SQL Server Management Stdio 中瞭解關於資料庫管理的各種直接操作的方
法。將瞭解的內容寫入實驗報告。
說明：具體表結構和資料以及要做的各種資料操作可以靈活掌握，可以針對
其它資料庫進行操作，具體情況由任課老師作出明確要求。
四、實驗報告注意事項及要求：
1、實驗報告一律用“石家莊鐵道大學實驗報告紙”書寫。
2、實驗報告中明確寫明姓名、班級、學號、實驗時間、實驗名稱等各項表頭。
3、實驗報告各項完整：實驗名稱、實驗內容、實驗完成情況（包括實驗設計思想、
主要技術問題的處理方法、實驗過程等）、其他(說明、分析、建議、體會等)。附錄二：實驗二 SQL 語句
1、 建立資料庫：
create database test1;
2、 建立二維表：
Create table s(
Sno char(2) primary key,
sname varchar(10) not null,
status int,
city varchar(10));
create table p(
pno char(2) primary key,
pname varchar(10) not null,
color varchar(6),
weight int);
create table j(
jno char(2) primary key,
jname varchar(20) not null,
city varchar(10));
create table spj(
sno char(2) foreign key references s(sno),
pno char(2) foreign key references p(pno),
jno char(2) foreign key references j(jno),
qty int,
primary key(sno,pno,jno));
3、 為各二維表插入資料：
insert into s values('s1','精益',20,'天津');
insert into s values('s2','盛德',10,'北京');insert into s values('s3','東方紅',30,'北京');
insert into s values('s4','豐泰盛',20,'天津');
insert into s values('s5','為民',30,'上海');
insert into p values('p1','螺母','紅',12);
insert into p values('p2','螺栓','綠',17);
insert into p values('p3','螺絲刀','藍',14);
insert into p values('p4','螺絲刀','紅',14);
insert into p values('p5','凸輪','藍',40);
insert into p values('p6','齒輪','紅',30);
insert into j values('j1','三建','北京');
insert into j values('j2','一汽','長春');
insert into j values('j3','彈簧廠','天津')
insert into j values('j4','造船廠','天津')
insert into j values('j5','機車廠','唐山');
insert into j values('j6','無線電廠','常州');
insert into j values('j7','半導體廠','南京');
insert into spj values('s1','p1','j1',200);
insert into spj values('s1','p1','j3',100);
insert into spj values('s1','p1','j4',700);
insert into spj values('s1','p2','j2',100);
insert into spj values('s2','p3','j1',400);
insert into spj values('s2','p3','j2',200);
insert into spj values('s2','p3','j4',500);
insert into spj values('s2','p3','j5',400);
insert into spj values('s2','p5','j1',400);
insert into spj values('s2','p5','j2',100);
insert into spj values('s3','p1','j1',200);
insert into spj values('s3','p3','j1',200);insert into spj values('s4','p5','j1',100);
insert into spj values('s4','p6','j3',300);
insert into spj values('s4','p6','j4',200);
insert into spj values('s5','p2','j4',100);
insert int`o spj values('s5','p3','j1',200);
insert into spj values('s5','p6','j2',200);
insert into spj values('s5','p6','j4',500)
- 開啟 SQL Server Management Studio。
- 連線到測試資料庫 test1。
- 在 New Query 視窗中輸入以下 SQL 語句進行查詢： （1）查詢所有供應商情況，先按城市升序排列，城市相同按供應商名稱降序排列。
SELECT * FROM s ORDER BY city ASC, sname DESC
（2）查詢所有零件情況，先按零件名稱升序排列，零件名稱相同按重量降序排列。
SELECT * FROM p ORDER BY pname ASC, weight DESC
（3）查詢專案名中含有“廠”的專案情況。
SELECT * FROM j WHERE jname LIKE '%廠%'
（4）查詢供應商名稱中第二個字為“方”的供應商情況。
SELECT * FROM s WHERE sname LIKE '_方%'
（5）查詢所有零件中的最大、最小、平均重量。
SELECT MAX(weight), MIN(weight), AVG(weight) FROM p
（6）查詢零件中名為“螺絲刀”的零件的種類數、平均重量。
SELECT COUNT(*) AS '種類數' ,AVG(weight) AS '平均重量' FROM p WHERE pname = '螺絲刀'
（7）查詢供應商 S1 所供應的各種零件的名稱和數量。
SELECT p.pname, spj.qty FROM spj JOIN p ON spj.pno = p.pno WHERE spj.sno = 's1'
（8）查詢工程 J1 所使用的各種零件的名稱和數量。
SELECT p.pname, spj.qty FROM spj JOIN p ON spj.pno = p.pno WHERE spj.jno = 'j1'
（9）查詢沒有使用紅色螺絲刀的工程名稱。
SELECT DISTINCT j.jname FROM spj JOIN j ON spj.jno = j.jno JOIN p ON spj.pno = p.pno WHERE p.pname <> '螺絲刀' OR p.color <> '紅'
（10）查詢沒有供應紅色螺絲刀的供應商名稱。
SELECT s.sname FROM s WHERE NOT EXISTS ( SELECT * FROM spj JOIN p ON spj.pno = p.pno WHERE s.sno = spj.sno AND p.pname = '螺絲刀' AND p.color = '紅' )
（11）查詢所用零件數量超過 500 的工程專案號。
SELECT spj.jno FROM spj GROUP BY spj.jno HAVING SUM(qty) > 500
（12）查詢所用零件種類超過 3 種的工程專案名稱。
SELECT j.jname FROM spj JOIN j ON spj.jno = j.jno GROUP BY spj.jno, j.jname HAVING COUNT(DISTINCT spj.pno) > 3
（13）查詢使用了全部零件的工程專案名稱。
SELECT j.jname FROM j WHERE NOT EXISTS ( SELECT * FROM p WHERE NOT EXISTS ( SELECT * FROM spj WHERE spj.jno = j.jno AND spj.pno = p.pno ) )
（14）查詢至少供應了工程 J1 所使用的全部零件的供應商名稱。
SELECT s.sname FROM spj JOIN p ON spj.pno = p.pno JOIN s ON spj.sno = s.sno WHERE NOT EXISTS ( SELECT * FROM spj WHERE spj.jno = 'j1' AND spj.pno NOT IN ( SELECT pno FROM spj WHERE jno = 'j1' ) AND spj.sno = s.sno )
（15）查詢供應情況，顯示內容為供應商名稱、零件名、工程名稱、數量。
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）查詢“東方紅”供應商供應情況，顯示供應的零件名、工程名稱、數量。
SELECT 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 WHERE s.sname = '東方紅'
- 為機車廠工程專案建立一個供應情況的檢視，包括供應商程式碼，零件程式碼，供應數量。
CREATE VIEW supply_view AS SELECT spj.sno, spj.pno, spj.qty FROM spj JOIN j ON spj.jno = j.jno WHERE j.jname = '機車廠'
（1）查詢機車廠工程專案使用的各種零件程式碼及其數量。
SELECT p.pno, SUM(spj.qty) as quantity FROM spj JOIN p ON spj.pno = p.pno WHERE spj.jno = 'j5' GROUP BY p.pno
（2）查詢處供應商 S2 的供應情況。
SELECT p.pname, spj.qty FROM spj JOIN p ON spj.pno = p.pno WHERE spj.sno = 's2'
- 關於資料庫管理的各種直接操作的方法，您可以瞭解以下內容：
- 建立新的資料庫
- 建立/刪除表
- 修改表結構
- 插入/更新/刪除資料
- 建立/刪除索引
- 執行儲存過程/函式
- 建立/刪除觸發器
- 管理使用者和許可權
以上是一些常見的操作，您可以根據實際需求進行學習和使用。