SQL Server資料庫操作

標籤：

 通過SQL Server建庫語句等，採用純程式碼方式建立資料庫，建立資料表，以及進行相應資料庫操作，包括檢索，插入，刪除，修改。

以下通過一個例題說明資料庫操作。

某倉儲超市採用POS（Point of Sale）收銀機負責前台的銷售收款，為及時掌握銷售資訊，並依此指導進貨，擬建立商品進、銷、存資料庫資訊管理系統。經過系統需求分析、概念結構設計和邏輯結構設計，可以簡化得到如下一組關係模式（其中     表示主鍵，    表示外鍵）：

積分卡（使用者編號，使用者名稱，累積消費金額，積分點）

銷售詳單（銷售流水號，商品編碼，數量，金額，使用者編號，收銀員，時間）

銷售日匯總（日期，商品編碼，數量）

存貨表（商品編碼，數量）

進貨表（送貨號碼，商品編碼，數量，日期）

商品（商品編碼，商品名稱，單價）

請在SQL Server的查詢分析器中按要求完成如下各題：

1、 建立名為Supermarket的資料庫，資料檔案名取為：Supermarket_data.mdf，記錄檔名取為：Supermarket_log.ldf。

 

2、 按表1-6要求建立6張資料表，並為每張表設定主鍵碼和外鍵碼（若有的話）。

表1 Integralcard積分卡資訊表

列名	資料類型	可否為空白	說明
User_id	char（10）	Not null	使用者編號
User_name	varchar（20）	Not null	使用者名稱
Cumulative_consumption	numeric（8，2）	Not null	累計消費金額
Integral_point	numeric（5，0）	Not null	積分點

表2 Salesdetails 銷售詳單資訊表

列名	資料類型	可否為空白	說明
sales_id	char（10）	Not null	銷售流水號
commodity_code	char（10）	Not null	商品編碼
number	numeric（4，0）	null	數量
amount	numeric（9，2）	null	金額
User_id	char（10）	Not null	使用者編號
cashier	varchar（20）	null	收銀員
sd_time	datetime	null	時間

表3 Salesdatesummary 銷售日匯總資訊表

列名	資料類型	可否為空白	說明
sds_date	datetime	Not null	日期
commodity_code	char（10）	Not null	商品編碼
number	numeric（4，0）	null	數量

表4 Inventorylist存貨資訊表

列名	資料類型	可否為空白	說明
commodity_code	char（10）	Not null	商品編碼
number	numeric（4，0）	null	數量

表5 Purchasetable進貨資訊表

列名	資料類型	可否為空白	說明
delivery_number	char（10）	Not null	送貨號碼
commodity_code	char（10）	Not null	商品編碼
number	numeric（4，0）	null	數量
pt_date	datetime	Not null	日期

表6 Commodity商品資訊表

列名	資料類型	可否為空白	說明
commodity_code	char（10）	Not null	商品編碼
commodity_name	varchar（10）	Not null	商品名稱
commodity_price	numeric（7，2）	Not null	商品單價

 

3、在建好的6張表中，利用物件總管分別輸入和更新若干條記錄，要求主鍵碼不可為空和重複，外鍵碼只能取另一張表的主鍵碼之一。

 

4、針對該資料庫的6張表，完成如下10個查詢請求：

（1）查詢使用者編號為’yh23001011’的使用者的使用者名稱、累積消費金額和積分點；

 

（2）查詢’張三’使用者所購的全部商品的商品編碼、商品名稱、單價、數量和金額；

 

（3）查詢2016年4月各類商品銷售數量的熱門排行榜，要求顯示商品編號、商品名稱和數量（按降序排列）；

 

（4）根據銷售詳單中的銷售流水號’xs80020001’和商品編碼’sp03004561’，對存貨表中的數量進行更新；

 

（5）根據進貨表中的送貨號碼’sh00012288’和商品編碼’sp03006677’， 對存貨表中的數量進行更新；

 

（6）統計2016年4月中每一天的銷售金額，要求顯示日期、銷售金額（按降序排列）。

 

5、針對該資料庫的6張表，定義如下2個視圖：

（1）定義一個商品存貨的視圖Commodity_Inventorylist，屬性包括商品編碼、商品名稱、單價和數量；

 

（2）定義一個使用者購買商品的詳細清單User_Purchase_Details, 屬性包括使用者編號、使用者名稱、商品編碼、商品名稱、單價和數量。

SQL Server 2008 R2上實現過程如下：

--建立資料庫--建立名為Supermarket的資料庫，資料檔案名取為：Supermarket_data.mdf，記錄檔名取為：Supermarket_log.ldf。USE master--使用系統GOCREATE DATABASE Supermarket --建立資料庫ON PRIMARY--主檔案(NAME='Supermarket_data', --檔案名稱FILENAME='D:\SQLProject\Supermarket_data.mdf',--路徑SIZE=5MB,--初始大小MAXSIZE=100MB,--最大容量FILEGROWTH=10%--增長速度)LOG ON--記錄檔(NAME='Supermarket_log',FILENAME='D:\SQLProject\Supermarket_log.ldf',SIZE=5MB,FILEGROWTH=0)GO--建立你資料庫表--模式（其中     表示主鍵，    表示外鍵）：--積分卡（使用者編號，使用者名稱，累積消費金額，積分點）--銷售詳單（銷售流水號，商品編碼，數量，金額，使用者編號，收銀員，時間）--銷售日匯總（日期，商品編碼，數量）--存貨表（商品編碼，數量）--進貨表（送貨號碼，商品編碼，數量，日期）--商品（商品編碼，商品名稱，單價）use Supermarketgo--積分卡（使用者編號，使用者名稱，累積消費金額，積分點）create table Integralcard(User_id char(10) primary key not null,User_name varchar(20) not null,Cumulative_consumption numeric(8,2) not null,Integral_point numeric(5,0) not null)go--銷售詳單（銷售流水號，商品編碼，數量，金額，使用者編號，收銀員，時間）use Supermarketgocreate table Salesdetails(sales_id char(10) not null,commodity_code char(10) not null foreign key(commodity_code) references Commodity(commodity_code)on delete cascade,number numeric(4,0) null,amount numeric(9,2) null,User_id char(10) not null foreign key(User_id) references Integralcard(User_id)on delete cascade,cashier varchar(20) null,sd_time datetime null)go--外鍵ALTER TABLE SalesdetailsADD CONSTRAINT Salesdetails_KEY PRIMARY KEY(sales_id,commodity_code,User_id)Go--銷售日匯總（日期，商品編碼，數量）use Supermarketgocreate table Salesdatesummary(sds_date datetime not null,commodity_code char(10) not null  foreign key(commodity_code) references Commodity(commodity_code)on delete cascade,number numeric(4,0) null)goALTER TABLE SalesdatesummaryADD CONSTRAINT Salesdatesummary_KEY PRIMARY KEY(commodity_code)Go--存貨表（商品編碼，數量）use Supermarketgocreate table Inventorylist(commodity_code char(10) not null foreign key(commodity_code) references Commodity(commodity_code)on delete cascade,number numeric(4,0) null)goALTER TABLE InventorylistADD CONSTRAINT Inventorylist_KEY PRIMARY KEY(commodity_code)Go--進貨表（送貨號碼，商品編碼，數量，日期）use Supermarketgocreate table Purchasetable(delivery_number char(10) not null,commodity_code char(10) not null foreign key(commodity_code) references Commodity(commodity_code)on delete cascade,number numeric(4,0) null,pt_date datetime not null)go--外鍵ALTER TABLE PurchasetableADD CONSTRAINT Purchasetable_KEY PRIMARY KEY(delivery_number,commodity_code)Go--商品（商品編碼，商品名稱，單價）use Supermarketgocreate table Commodity(commodity_code char(10) primary key not null,commodity_name varchar(10) not null,commodity_price numeric(7,2) not null)go-------------------------------------------------查詢使用者編號為’yh23001011’的使用者的使用者名稱、累積消費金額和積分點use Supermarketselect User_name as 姓名,Cumulative_consumption as 累計消費金額,Integral_point as 積分點 from Integralcardwhere User_id='yh23001011'go--查詢’張三’使用者所購的全部商品的商品編碼、商品名稱、單價、數量和金額；use Supermarketselect com.commodity_code,com.commodity_name,com.commodity_price,sal.number,sal.amount from Commodity com,Salesdetails sal,Integralcard cawhere com.commodity_code=sal.commodity_code and sal.User_id=ca.User_id and ca.User_name='張三'go--查詢2016年4月各類商品銷售數量的熱門排行榜，要求顯示商品編號、商品名稱和數量（按降序排列）use Supermarketselect com.commodity_code,com.commodity_name,sal.number,sal.sd_timefrom Commodity com,Salesdetails salwhere com.commodity_code=sal.commodity_code and sal.sd_time between '2016-04-01' and '2016-04-30' order by sal.number descgo--查看結果use Supermarketselect number from Inventorylistwhere Inventorylist.commodity_code='sp03004561'go--根據銷售詳單中的銷售流水號’xs80020001’和商品編碼’sp03004561’，對存貨表中的數量進行更新use Supermarketupdate Inventorylistset number= number - (select sal.number from Salesdetails salwhere sal.sales_id='xs80020001' and sal.commodity_code='sp03004561')go--查看更新結果use Supermarketselect number from Inventorylistwhere Inventorylist.commodity_code='sp03004561'go--查看結果use Supermarketselect number from Inventorylistwhere Inventorylist.commodity_code='sp03006677'go--根據進貨表中的送貨號碼’sh00012288’和商品編碼’sp03006677’， 對存貨表中的數量進行更新use Supermarketupdate Inventorylistset Inventorylist.number=Inventorylist.number +(select pur.number from Purchasetable purwhere pur.delivery_number='sh00012288' and pur.commodity_code='sp03006677')go--查看更新結果use Supermarketselect number from Inventorylistwhere commodity_code='sp03006677'go--統計2016年4月中每一天的銷售金額，要求顯示日期、銷售金額（按降序排列）use Supermarketselect sal.sds_date,salemoney=com.commodity_price*sal.numberfrom Commodity com,Salesdatesummary salwhere com.commodity_code=sal.commodity_code and sal.sds_date>='2016-04-01' and sal.sds_date<='2016-04-30'go--5--定義一個商品存貨的視圖Commodity_Inventorylist，屬性包括商品編碼、商品名稱、單價和數量use Supermarketgocreate view Commodity_Inventorylist(commodity_code,commodity_name,commodity_price,number)as select com.commodity_code,com.commodity_name,com.commodity_price,inve.number from Commodity com,Inventorylist invewhere com.commodity_code=inve.commodity_codego--定義一個使用者購買商品的詳細清單User_Purchase_Details, 屬性包括使用者編號、使用者名稱、商品編碼、商品名稱、單價和數量use Supermarketgocreate view User_Purchase_Details(User_id,User_name,commodity_code,commodity_name,commodity_price,number)as select inte.User_id,inte.User_name,com.commodity_code,com.commodity_name,com.commodity_price,sal.numberfrom Integralcard inte,Salesdetails sal,Commodity comwhere inte.User_id=sal.User_id and sal.commodity_code=com.commodity_codego

資料庫實現：

SQL Server資料庫操作