SQLSERVER（项⽬实战）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
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 Sale
GO
INSERT INTO Customer
SELECT '001','杨婷','深圳','0755-********' UNION
SELECT '002','陈萍','深圳','0755-********' UNION
SELECT '003','李东','深圳','0755-********' UNION
SELECT '004','叶合','深圳','0755-********' UNION
SELECT '005','谭新','深圳','0755-********' GO
--创建Product表数据
USE Sale
GO
INSERT INTO Product
SELECT '00001','电视','3000.00','800'
UNION
SELECT '00002','空调','2000.00','500'
UNION
SELECT '00003','床','1000.00','300'
UNION
SELECT '00004','餐桌','1500.00','200'彭于晏图片
UNION
SELECT '00005','⾳响','5000.00','600'
UNION
SELECT '00006','沙发','6000.00','100'
GO
-
-创建Proln数据
USE Sale
GO
INSERT INTO ProIn
SELECT '2006-1-1','00001','10'
UNION
SELECT '2006-1-1','00002','5'
UNION
SELECT '2006-1-2','00001','5'
UNION
SELECT '2006-1-2','00003','10'
UNION
SELECT '2006-1-3','00001','10'
UNION
SELECT '2006-2-1','00003','20'
UNION
SELECT '2006-2-2','00001','10'
UNION
SELECT '2006-2-3','00004','30'
UNION
SELECT '2006-2-3','00003','20'
GO
-
-创建ProOut数据
USE Sale
USE Sale
GO
INSERT INTO ProOut
SELECT '2016-1-1','001','00001','10'
UNION
SELECT '2016-1-3','001','00001','5'
UNION
SELECT '2016-1-3','001','00001','5'
UNION
SELECT '2016-2-1','001','00001','10'
UNION
SELECT '2016-2-2','001','00001','10'
UNION
SELECT '2016-2-3','001','00001','20'
UNION
SELECT '2016-3-2','001','00001','10'
UNION
SELECT '2016-3-2','001','00001','30'
UNION
SELECT '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
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
GO25用英语怎么说
DECLARE @SUM INT
SET @SUM = (SELECT COUNT(*) FROM Product)
PRINT '共有'+CONVERT(char(2),@SUM)+'种产品'
-
-（创建与管理视图）
--创建视图V_Sale1，显⽰销售⽇期、客户编号、客户姓名、产品编号、产品名称、单价、销售单价和销售⾦额。
USE Sale
GO
CREATE VIEW V_Sale1
AS
SELECT SaleDate,Customer.CusNo,CusName,Product.ProNo,ProName,Price,Quantity,SaleMoney=(Price*Quantity)
FROM ProOut,Customer,Product
WHERE ProOut.ProNo = ProOut.ProNo AND
独字开头的成语
Customer.CusNo = ProOut.CusNo
GO
--创建视图V_Sale2，统计每种产品的销售数量和销售⾦额。统计结果包括产品编号、产品名称、单价、销售数量和销售⾦额。USE Sale
GO
CREATE VIEW V_Sale2
AS
SELECT Product.ProNo,ProName,'Quantity'=SUM(Quantity),'TotalMoney'=SUM(Price*Quantity)
FROM ProOut,Product
FROM ProOut,Product
WHERE ProOut.ProNo = Product.ProNo
GROUP BY Product.ProNo,Product.ProName
GO
--创建视图V_Sale3，统计销售⾦额在10万以下的产品信息。
USE Sale
GO
CREATE VIEW V_Sale3
AS
SELECT Product.ProNo,ProName
FROM Product,ProOut
WHERE Product.ProNo = ProOut.ProNo
GROUP BY Product.ProNo,Product.ProName
HAVING (SUM(Price * Quantity)<100000)
GO
--（创建与管理存储过程）
--1、创建存储过程P_Sale1，统计每种产品的销售数量和销售⾦额。
USE Sale
GO
CREATE PROCEDURE P_Sale1
AS
SELECT ProOut.ProNo,ProName,Price,'销售数量'=SUM(Quantity),'销售⾦额'=SUM(Price*Quantity)
FROM ProOut,Product
WHERE Product.ProNo=ProOut.ProNo
GROUP BY ProOut.ProNo,ProName,Price
GO
--执⾏语句
--EXEC P_Sale1
--GO
--2、创建存储过程P_Sale2，能够根据指定的客户统计，汇总该客户购买每种产品的数量和花费⾦额。USE Sale
GO
CREATE PROCEDURE P_Sale2
@CusNo nvarchar(3)
AS
SELECT Product.ProNo,ProName,Price,'产品数量'=SUM(Quantity),'花费⾦额'=SUM(Quantity*Price)
FROM Product,ProOut
WHERE Product.ProNo=ProOut.ProNo AND
ProOut.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
AS
SET @SaleMoney=
(SELECT SUM(Quantity*Price)
FROM Product,ProOut
WHERE Product.ProNo = ProOut.ProNo AND
Product.ProNo = @ProNo AND
SaleDate = @SaleDate
)
PRINT @SaleMoney
GO
--执⾏语句
-
-DECLARE @SaleMoney DECIMAL(8,2)
