實驗二 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)
4、（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 SUBSTRING(sname,2,1) = '方';
(5)SELECT
MAX(weight) AS max_weight,
MIN(weight) AS min_weight,
AVG(weight) AS avg_weight
FROM P;
(6)SELECT
COUNT(DISTINCT category) AS num_categories,
AVG(weight) AS avg_weight
FROM p
WHERE pname = '螺絲刀';
(7)SELECT p.pname, spj.qty
FROM spj
JOIN p ON spj.pno = p.pno
WHERE spj.sno = 's1';
(8)SELECT p.pname, spj.qty
FROM spj
JOIN p ON spj.pno = p.pno
WHERE spj.jno = 'j1';
(9)SELECT DISTINCT j.jname
FROM j
LEFT JOIN spj ON j.jno = spj.jno
LEFT JOIN p ON spj.pno = p.pno
WHERE p.pname != '螺絲刀' OR p.color != '紅';
(10)SELECT DISTINCT s.sname
FROM s
LEFT JOIN spj ON s.sno = spj.sno
LEFT JOIN p ON spj.pno = p.pno
WHERE p.pname != '螺絲刀' OR p.color != '紅';
(11)SELECT jno
FROM spj
GROUP BY jno
HAVING SUM(qty) > 500;
(12)SELECT j.jname
FROM j
JOIN spj ON j.jno = spj.jno
JOIN p ON spj.pno = p.pno
GROUP BY j.jname
HAVING COUNT(DISTINCT p.pname) > 3;
(13)SELECT j.jname
FROM j
JOIN spj ON j.jno = spj.jno
GROUP BY j.jname
HAVING COUNT(DISTINCT spj.pno) = (SELECT COUNT(DISTINCT pno) FROM p);
(14) SELECT s.sname
FROM s
JOIN spj ON s.sno = spj.sno
WHERE spj.jno = 'j1'
GROUP BY s.sname
HAVING COUNT(DISTINCT spj.pno) >= (
SELECT COUNT(DISTINCT pno)
FROM spj
WHERE jno = 'j1'
);
(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 p ON spj.pno = p.pno
JOIN j ON spj.jno = j.jno
WHERE spj.sno = (SELECT sno FROM s WHERE sname = '東方紅');