Sale数据库--（创建数据库）
--创建名为Sale的销售数据库。该数据表有一个名为Sale.mdf的主数据文件和名字为Sale_log.ldf的事务日志文件。
--主数据文件容量为4MB，事务日志文件容量为10MB，数据文件和日志文件的最大容量为20MB，文件增量为1MB。
USE master
GO
CREATE DATABASE Sale
ON
PRIMARY
(NAME = Sale,FILENAME = 'D:\Sale.mdf',SIZE = 5MB,MAXSIZE = 20MB,FILEGROWTH = 1MB)
LOG ON
(NAME = Sale_log,FILENAME = 'D:\Sale_log.ldf',SIZE = 10MB,MAXSIZE = 20MB,FILEGROWTH = 1MB
)
GO--（创建数据表）
--创建客户信息表Customer
USE Sale
GO
CREATE TABLE Customer
(CusNo nvarchar(3) NOT NULL,CusName nvarchar(10) NOT NULL,Address nvarchar(20) NULL,Tel nvarchar(20) NULL
)
GO--创建产品表信息Product
USE Sale
GO
CREATE TABLE Product
(ProNo nvarchar(5) NOT NULL,ProName nvarchar(20) NOT NULL,Price Decimal(8,2) NOT NULL,Stocks Decimal(8,0) NOT NULL
)
GO--创建入库表信息ProIn
USE Sale
GO
CREATE TABLE ProIn
(InputDate DateTime NOT NULL,ProNo nvarchar(5) NOT NULL,Quantity Decimal(6,0) NOT NULL
)
GO--创建销售表ProOut结构
USE Sale
GO
CREATE TABLE ProOut
(SaleDate DateTime NOT NULL,CusNo nvarchar(3) NOT NULL,ProNo nvarchar(5) NOT NULL,Quantity Decimal(6,0) NOT NULL
)
GO--创建数据表数据
--创建Customer表数据USE SaleGOINSERT INTO CustomerSELECT '001','杨婷','深圳','0755-22221111'UNIONSELECT '002','陈萍','深圳','0755-22223333'UNIONSELECT '003','李东','深圳','0755-22225555'UNIONSELECT '004','叶合','深圳','0755-22227777'UNIONSELECT '005','谭新','深圳','0755-22229999'GO--创建Product表数据USE SaleGOINSERT INTO ProductSELECT '00001','电视','3000.00','800'UNIONSELECT '00002','空调','2000.00','500'UNIONSELECT '00003','床','1000.00','300'UNIONSELECT '00004','餐桌','1500.00','200'UNIONSELECT '00005','音响','5000.00','600'UNIONSELECT '00006','沙发','6000.00','100'GO--创建Proln数据USE SaleGOINSERT INTO ProInSELECT '2006-1-1','00001','10'UNIONSELECT '2006-1-1','00002','5'UNIONSELECT '2006-1-2','00001','5'UNIONSELECT '2006-1-2','00003','10'UNIONSELECT '2006-1-3','00001','10'UNIONSELECT '2006-2-1','00003','20'UNIONSELECT '2006-2-2','00001','10'UNIONSELECT '2006-2-3','00004','30'UNIONSELECT '2006-2-3','00003','20'GO--创建ProOut数据USE SaleGOINSERT INTO ProOutSELECT '2016-1-1','001','00001','10'UNIONSELECT '2016-1-3','001','00001','5'UNIONSELECT '2016-1-3','001','00001','5'UNIONSELECT '2016-2-1','001','00001','10'UNIONSELECT '2016-2-2','001','00001','10'UNIONSELECT '2016-2-3','001','00001','20'UNIONSELECT '2016-3-2','001','00001','10'UNIONSELECT '2016-3-2','001','00001','30'UNIONSELECT '2016-3-3','001','00001','20'GO--（实施数据完整性）
--使用ALTER TABLE语句为Sale数据库中增加主键和外键约束
USE Sale
GO
ALTER TABLE Customer
ADD CONSTRAINT PK_Customer PRIMARY KEY(CusNo)
GO
ALTER TABLE Product
ADD CONSTRAINT PK_Product PRIMARY KEY(ProNo)
GO
ALTER TABLE ProIn
ADD CONSTRAINT FK_ProIn_Product FOREIGN KEY(ProNo)
REFERENCES Product (ProNo)
GO
ALTER TABLE ProOut
ADD CONSTRAINT PK_ProOut_Customer FOREIGN KEY (CusNo)
REFERENCES Customer(CusNo)
GO
ALTER TABLE ProOut
ADD CONSTRAINT PK_ProOut_Product FOREIGN KEY (ProNo)
REFERENCES Product(ProNo)
GO--约束客户表Customer的CusNo列长度为3，产品表Product的ProNo列值长度为5
USE Sale
GO
ALTER TABLE Customer
ADD CONSTRAINT CK_Customer_CusNo CHECK(LEN(CusNo)=3)
GO
ALTER TABLE Product
ADD CONSTRAINT CK_Product_ProNo CHECK(LEN(ProNo)=5)
GO--对产品表Product的Stocks列、Price列、入库表ProIn的Quantity列、销售表ProOut的Quantity列值进行约束，使其值必须大于0
USE Sale
GO
ALTER TABLE Product
ADD CONSTRAINT CK_Product_Stocks CHECK(Price>0)
GO
ALTER TABLE ProIn
ADD CONSTRAINT CK_ProIn_Quantity CHECK(Quantity>0)
GO
ALTER TABLE ProOut
ADD CONSTRAINT CK_ProOut_Quantity CHECK(Quantity>0)
GO--对销售表ProOut的SaleDate列进行约束，当不输入值时，系统默认其值为系统当前日期。
USE Sale
GO
Create DEFAULT CurrentDate
AS GETDATE()
GO
EXEC sp_bindefault CurrentDate,'ProOut.SaleDate'
GO--（实现索引）
--用户按照CusName(客户姓名)查询客户信息，希望提高查询速度。
USE Sale
GO
CREATE INDEX IX_Customer
ON Customer(CusName)
GO--用户按照ProName(产品名称)查询客户信息，希望提高查询速度。
USE Sale
GO
CREATE INDEX IX_Product
ON Product(ProName)
GO--用户按照SaleDate(销售日期)查询客户信息，希望提高查询速度。
USE Sale
GO
CREATE INDEX IX_SaleDate
ON ProOut(SaleDate)
GO--（T-SQL语言编程基础）
USE Sale
GO
DECLARE @SUM INT
SET @SUM = (SELECT COUNT(*) FROM Product)
PRINT '共有'+CONVERT(char(2),@SUM)+'种产品'
--（创建与管理视图）
--创建视图V_Sale1，显示销售日期、客户编号、客户姓名、产品编号、产品名称、单价、销售单价和销售金额。
USE Sale
GO
CREATE VIEW V_Sale1
ASSELECT SaleDate,Customer.CusNo,CusName,Product.ProNo,ProName,Price,Quantity,SaleMoney=(Price*Quantity)FROM ProOut,Customer,ProductWHERE ProOut.ProNo = ProOut.ProNo ANDCustomer.CusNo = ProOut.CusNo
GO
--创建视图V_Sale2，统计每种产品的销售数量和销售金额。统计结果包括产品编号、产品名称、单价、销售数量和销售金额。
USE Sale
GO
CREATE VIEW V_Sale2
ASSELECT Product.ProNo,ProName,'Quantity'=SUM(Quantity),'TotalMoney'=SUM(Price*Quantity)FROM ProOut,ProductWHERE ProOut.ProNo = Product.ProNoGROUP BY Product.ProNo,Product.ProName
GO
--创建视图V_Sale3，统计销售金额在10万以下的产品信息。
USE Sale
GO
CREATE VIEW V_Sale3
ASSELECT Product.ProNo,ProNameFROM Product,ProOutWHERE Product.ProNo = ProOut.ProNoGROUP BY Product.ProNo,Product.ProNameHAVING (SUM(Price * Quantity)<100000)
GO--（创建与管理存储过程）
--1、创建存储过程P_Sale1，统计每种产品的销售数量和销售金额。
USE Sale
GO
CREATE PROCEDURE P_Sale1
ASSELECT ProOut.ProNo,ProName,Price,'销售数量'=SUM(Quantity),'销售金额'=SUM(Price*Quantity)FROM ProOut,ProductWHERE Product.ProNo=ProOut.ProNoGROUP BY ProOut.ProNo,ProName,Price
GO
--执行语句
--EXEC P_Sale1
--GO--2、创建存储过程P_Sale2，能够根据指定的客户统计，汇总该客户购买每种产品的数量和花费金额。
USE Sale
GO
CREATE PROCEDURE P_Sale2
@CusNo nvarchar(3)
ASSELECT Product.ProNo,ProName,Price,'产品数量'=SUM(Quantity),'花费金额'=SUM(Quantity*Price)FROM Product,ProOutWHERE Product.ProNo=ProOut.ProNo ANDProOut.CusNo = @CusNo
GROUP BY Product.ProNo,ProName,Price
GO
--执行语句
--EXEC P_Sale2 @CusNo='001'
--GO--3、创建存储过程P_Sale3，能够根据指定的产品编号和日期，以输出参数的形式得到该产品的销售金额。
USE Sale
GO
CREATE PROCEDURE P_Sale3
@ProNo nvarchar(5),@SaleDate DateTime,@SaleMoney DECIMAL(8,2) OUTPUT
ASSET @SaleMoney=(SELECT SUM(Quantity*Price)FROM Product,ProOutWHERE Product.ProNo = ProOut.ProNo ANDProduct.ProNo = @ProNo ANDSaleDate = @SaleDate)
PRINT @SaleMoney
GO
--执行语句
--DECLARE @SaleMoney DECIMAL(8,2)
--EXEC P_Sale3 '00001','2016-3-3',@SaleMoney
--GO
