實驗一

實驗步驟：

1：創建資料庫“shiyan2”；
2：創建資料表’s’,’p’,’j’；
3：創建資料表’spj’并設定’sno’,’pno’,’jno’三列為外鍵，分別關聯于s表的’sno’，   p表的’pno’，j表的’jno’列；
4：分別向資料表’s’,’p’,’j’,’spj’中插入資料項；
5：建立三個表S，P，J，SPJ；
6：找出所有供應商的名字和所在城市；
7：找出所有零件的名稱，顏色，重量；
8：找出使用供應商S1所供應零件的工程號碼；
9：找出工程專案J2使用的各種零件的名稱和數量；
10：找出上海廠商供應的所有零件號碼；
11：找出使用上海廠的零件的工程名稱；
12：找出沒有使用天津產的零件的工程號碼；
13：把全部紅色零件的顏色改成藍色；
14：由S5供給J4的零件P6改為由S3供應；
15：從供應商關系中洗掉S2的記錄，并從供應關系中洗掉相應的記錄；
16：將（S2，J6，P4，200）插入供應情況關系中；
17：建視圖并進行相關查找操作，

實驗代碼：

    
CREATE DATABASE shiyan2;
USE shiyan2
CREATE TABLE S
(SNO CHAR (9) PRIMARY KEY,
SNAME CHAR(20),
STATUS INT,
CITY CHAR(20),
);
USE shiyan2
CREATE TABLE P
(PNO CHAR (9) PRIMARY KEY,
PNAME CHAR(20),
COLOR CHAR(20),
WEIGHT INT,
);
USE shiyan2
CREATE TABLE J
(JNO CHAR (9) PRIMARY KEY,
JNAME CHAR(20),
CITY CHAR(20),
);
USE shiyan2
CREATE TABLE SPJ
(SNO CHAR (9),
PNO CHAR (9),
JNO CHAR (9),
QTY INT,
PRIMARY KEY(SNO,PNO,JNO),
FOREIGN KEY (SNO)REFERENCES S(SNO),
FOREIGN KEY (PNO)REFERENCES P(PNO),
FOREIGN KEY (JNO)REFERENCES J(JNO),
);
INSERT INTO S(SNO,SNAME,STATUS,CITY)VALUES('S1','精益',20,'天津');
INSERT INTO S(SNO,SNAME,STATUS,CITY)VALUES('S2','盛錫',10,'北京');
INSERT INTO S(SNO,SNAME,STATUS,CITY)VALUES('S3','東方紅',30,'北京');
INSERT INTO S(SNO,SNAME,STATUS,CITY)VALUES('S4','豐泰盛',20,'天津');
INSERT INTO S(SNO,SNAME,STATUS,CITY)VALUES('S5','為民',30,'上海');
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT)VALUES('P1','螺母','紅',12);
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT)VALUES('P2','螺栓','綠',17);
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT)VALUES('P3','螺絲刀','藍',14);
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT)VALUES('P4','螺絲刀','紅',14);
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT)VALUES('P5','凸輪','藍',40);
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT)VALUES('P6','齒輪','紅',30);
INSERT INTO J(JNO,JNAME,CITY)VALUES('J1','三建','北京');
INSERT INTO J(JNO,JNAME,CITY)VALUES('J2','一汽','長春');
INSERT INTO J(JNO,JNAME,CITY)VALUES('J3','彈簧廠','天津');
INSERT INTO J(JNO,JNAME,CITY)VALUES('J4','造船廠','天津');
INSERT INTO J(JNO,JNAME,CITY)VALUES('J5','機車廠','唐山');
INSERT INTO J(JNO,JNAME,CITY)VALUES('J6','無線電廠','常州');
INSERT INTO J(JNO,JNAME,CITY)VALUES('J7','半導體廠','南京');
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S1','P1','J1',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S1','P1','J3',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S1','P1','J4',700);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S1','P2','J2',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S2','P3','J1',400);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S2','P3','J2',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S2','P3','J4',500);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S2','P3','J5',400);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S2','P5','J1',400);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S2','P5','J2',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S3','P1','J1',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S3','P3','J1',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S4','P5','J1',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S4','P6','J3',300);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S4','P6','J4',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S5','P2','J4',100);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S5','P3','J1',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S5','P6','J2',200);
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S5','P6','J4',500);
/*1：找出所有供應商的名字和所在城市*/
SELECT SNAME,CITY
FROM S;
/*2：找出所有零件的名稱，顏色，重量*/
SELECT PNAME,COLOR,WEIGHT
FROM P;
/*3：找出使用供應商S1所供應零件的工程號碼*/
SELECT JNO
FROM SPJ
WHERE SNO='S1';
/*4：找出工程專案J2使用的各種零件的名稱和數量*/
SELECT PNAME,QTY
FROM P,SPJ
WHERE P.PNO=SPJ.PNO AND JNO='J2';
/*5：找出上海廠商供應的所有零件號碼*/
SELECT DISTINCT PNO 
FROM S,SPJ
WHERE S.SNO=SPJ.SNO AND CITY='上海';
/*6：找出使用上海廠的零件的工程名稱*/
SELECT DISTINCT JNAME
FROM SPJ,S,J
WHERE SPJ.SNO=S.SNO AND SPJ.JNO=J.JNO AND S.CITY='上海';
/*7：找出沒有使用天津產的零件的工程號碼*/
SELECT JNO
FROM SPJ
WHERE JNO NOT IN (SELECT DISTINCT  JNO
FROM SPJ,S
WHERE S.SNO=SPJ.SNO AND S.CITY='天津');
/*8：把全部紅色零件的顏色改成藍色*/
UPDATE P
SET COLOR='藍'
WHERE COLOR='紅';
/*9：由S5供給J4的零件P6改為由S3供應*/
UPDATE SPJ
SET SNO='S3'
WHERE SNO='S5' AND JNO='J4' AND PNO='P6';
/*10：從供應商關系中洗掉S2的記錄，并從供應關系中洗掉相應的記錄*/
DELETE  
FROM S
WHERE SNO='S2';

DELETE 
FROM SPJ
WHERE SNO='S2';
/*11：將（S2，J6，P4，200）插入供應情況關系*/
INSERT INTO S(SNO,SNAME,STATUS,CITY)VALUES('S2','盛錫',10,'北京');
INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES('S2','P4','J6',200);
/*12:建視圖*/
CREATE VIEW V_SPJ
AS
SELECT SNO,PNO,QTY
FROM SPJ,J
WHERE JNAME='三建' AND SPJ.JNO=J.JNO;
/*1：*/
SELECT PNO,QTY
FROM V_SPJ;
/*2：*/
SELECT SNO,PNO,QTY
FROM V_SPJ
WHERE SNO='S1';

實驗二

實驗步驟：

1．運行給定的SQL命令，創建eshop1資料庫；
2．查詢products表中p_price（商品價格）在800以上的商品詳細資訊；
3．查詢products表中p_quantity（商品數量）在20和50之間的商品編號、商品名稱和商品數量；
4．查詢orders表中各會員購買商品的總量，并以漢字列標題形式輸出會員帳號，商品總額；
5．查詢members表中家庭地址為“湖南”的會員詳細資訊；
6．查詢members表中年齡大于30且性別為“男”的會員詳細資訊；
7．查詢orders表各商品銷售總量前3名的商品編號和銷售總量；
8．查詢orders表中購買過商品的會員帳號，要求去掉重復行；
9．查詢orders表已確認、已支付和已配送的訂單詳細資訊；
10．查詢性別為“男”的會員詳細資訊，查詢結果按月薪降序排列；
11．查詢購買商品號為’0910810004’總人數；
12．查詢2018年6月6日前，所有商品的訂購總量，要求輸出商品號和訂購總量；
13．查詢所有會員的平均月薪，最高月薪和最低月薪；
14．查詢所有會員購買商品的種類和，要求輸出會員號和商品種類和；
15．查詢購買了商品號為“0910810004”的會員號和姓名，并以漢字標題顯示；
16．使用簡單查詢家庭地址為“湖南株洲”的會員以及年齡在30歲以上的會員詳細資訊；
17．將members表和orders表之間的左向外聯接包括所有會員的資訊，包括沒有購買商品的會員，

實驗代碼：

CREATE DATABASE eshop1 
ON
(
  NAME=eshop1_dat,
  FILENAME='c:\data\eshop1_dat.mdf',
  SIZE=10,
  MAXSIZE=50,
  FILEGROWTH=5
)
LOG ON
(
  NAME=eshop1_log,
  FILENAME='c:\data\eshop1_log.ldf',
  SIZE=10,
  MAXSIZE=25,
  FILEGROWTH=5
)
GO
USE eshop1
GO
CREATE TABLE orders (
	M_account VARCHAR(20) NOT NULL ,
	P_no VARCHAR(20) NOT NULL ,
	O_quantity INT NOT NULL ,
	O_date DATETIME NOT NULL ,
	O_confirm_state BIT NOT NULL,
	O_pay_state BIT NOT NULL ,
	O_send_state BIT NOT NULL)
GO
CREATE TABLE members (
	M_account VARCHAR(20) NOT NULL,
	M_name VARCHAR(20) NOT NULL ,
	M_birth VARCHAR(20)  NULL ,
	M_sex CHAR(2)  NULL ,
	M_address VARCHAR(50) NULL ,
	M_salary decimal(7,1)  NULL ,
	M_password VARCHAR(20) NOT NULL 
) 
GO
CREATE TABLE products (
	P_no VARCHAR(20) NOT NULL,
	P_name VARCHAR(50) NOT NULL ,
	p_date DATETIME NULL ,
	P_quantity INT NOT NULL ,
	P_price SMALLMONEY NOT NULL ,
	P_information VARCHAR(500) NULL ,
) 
GO
INSERT INTO members VALUES('Jinjin', '津津有味', 1982-04-14,'女', '北京市', 8200.0, 'jinjin')
INSERT INTO members VALUES('Lfz', '劉法治',  1976-08-26, '男','天津市', 4500.0, 'lfz0826')
INSERT INTO members VALUES('liuzc518', '劉志成', 1972-05-18,  '男','湖南株洲', 3500.0, 'liuzc518')
INSERT INTO members VALUES('Wangym', '王詠梅',  1974-08-06,'女', '湖南長沙', 4000.0, 'wangym0806')
INSERT INTO members VALUES('Zhangzl', '張自梁', 1975-04-20,'男', '湖南株洲', 4300.0, 'zhangzl')
INSERT INTO members VALUES('zhao888', '趙愛云', 1972-02-12,'男', '湖南株洲', 5500.0, 'zhao888')
INSERT INTO products VALUES('0130810324', '清華同方電腦', '2005-12-11', 7, 8000.0, '優惠多多')
INSERT INTO products VALUES('0140810330', '洗衣粉', '2005-05-31', 1000, 8.6, '特價銷售')
INSERT INTO products VALUES('0140810332', '紅彤彤臘肉', '2005-05-20', 43, 15.0, '是一種衛生食品')
INSERT INTO products VALUES('0140810333', '力士牌香皂', '2005-05-06', 22, 6.0, '是一種清潔用品')
INSERT INTO products VALUES('0240810330', '電動自行車', '2005-05-31', 10, 1586.0, '價廉物美')
INSERT INTO products VALUES('0240810333', '自行車', '2005-05-31', 10, 586.0, '價廉物美')
INSERT INTO products VALUES('0910810001', '愛國者MP3', '2005-05-31', 100, 450.0, '價廉物美')
INSERT INTO products VALUES('0910810002', '商務通', '2005-05-20', 10, 850.0, '價廉物美')
INSERT INTO products VALUES('0910810003', '名人好記星', '2005-05-31', 100, 550.0, '價廉物美')
INSERT INTO products VALUES('0910810004', '奧美嘉 U盤', '2005-05-31', 100, 350.0, '價廉物美')
INSERT INTO orders VALUES('jinjin', '0910810004', 2, '2005-06-06', 1, 0, 0)
INSERT INTO orders VALUES('jinjin', '0910810004', 1, '2005-08-09', 1, 1, 1)
INSERT INTO orders VALUES('lfz', '0910810001', 1, '2005-08-09', 0, 0, 0)
INSERT INTO orders VALUES('lfz', '0910810004', 2, '2005-06-06', 1, 1, 1)
INSERT INTO orders VALUES('lfz', '0910810004', 2, '2005-08-09', 1, 1, 1)
INSERT INTO orders VALUES('liuzc518', '0140810324', 1, '2005-10-09', 0, 0, 0)
INSERT INTO orders VALUES('liuzc518', '0910810001', 1, '2005-10-09', 1, 1, 0)
INSERT INTO orders VALUES('liuzc518', '0910810004', 2, '2005-10-09', 1, 1, 0)
INSERT INTO orders VALUES('wangym', '0910810001', 1, '2005-08-09', 1, 0, 0)
INSERT INTO orders VALUES('zhao888', '0240810333', 2, '2005-06-06', 1, 1, 0)
SELECT*
FROM products
WHERE P_price>800;
SELECT P_no,P_name,P_quantity
FROM products
WHERE P_quantity>20 and P_quantity<50;
SELECT M_account,O_quantity
FROM orders;
SELECT *
FROM members
WHERE M_address='湖南株洲';
SELECT *
FROM members
WHERE DATEDIFF (yy,M_birth,GETDATE())>30 AND M_sex='男';
SELECT top 3 P_no,SUM(O_quantity) 
FROM orders
GROUP BY (P_no) ORDER BY sum(O_quantity) DESC;
SELECT DISTINCT M_account
FROM orders;
SELECT*
FROM orders
WHERE O_pay_state=1 AND O_send_state=1 AND O_confirm_state=1;
SELECT*
FROM members
WHERE M_sex='男' ORDER BY M_salary DESC;
SELECT COUNT(*)
FROM orders
WHERE P_no='0910810004';
SELECT P_no,SUM(O_quantity)
FROM orders
WHERE O_date<'2018-6-6' GROUP BY P_no;
SELECT AVG(M_salary)+MAX(M_salary)+MIN(M_salary)
FROM members;
SELECT M_account,COUNT(DISTINCT P_no)
FROM orders 
GROUP BY M_account;
SELECT  members.m_account,members.m_name
FROM products,orders,members
WHERE products.p_no=orders.p_no
AND orders.m_account=members.m_account
AND products.p_no='0910810004';
SELECT *
FROM members
WHERE DATEDIFF (YEAR ,M_birth,GETDATE())>30 AND M_address='湖南株洲';
SELECT members.*, orders.*
FROM members
LEFT OUTER JOIN orders
ON members.m_account = orders.m_account;