搭建网站的步骤和顺序,酒泉建设局网站,佛山网站设计制作公司,内设网站Sale数据库--#xff08;创建数据库#xff09;
--创建名为Sale的销售数据库。该数据表有一个名为Sale.mdf的主数据文件和名字为Sale_log.ldf的事务日志文件。
--主数据文件容量为4MB#xff0c;事务日志文件容量为10MB#xff0c;数据文件和日志文件的最大容量为20MB…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-22221111UNIONSELECT 002,陈萍,深圳,0755-22223333UNIONSELECT 003,李东,深圳,0755-22225555UNIONSELECT 004,叶合,深圳,0755-22227777UNIONSELECT 005,谭新,深圳,0755-22229999GO--创建Product表数据USE SaleGOINSERT INTO ProductSELECT 00001,电视,3000.00,800UNIONSELECT 00002,空调,2000.00,500UNIONSELECT 00003,床,1000.00,300UNIONSELECT 00004,餐桌,1500.00,200UNIONSELECT 00005,音响,5000.00,600UNIONSELECT 00006,沙发,6000.00,100GO--创建Proln数据USE SaleGOINSERT INTO ProInSELECT 2006-1-1,00001,10UNIONSELECT 2006-1-1,00002,5UNIONSELECT 2006-1-2,00001,5UNIONSELECT 2006-1-2,00003,10UNIONSELECT 2006-1-3,00001,10UNIONSELECT 2006-2-1,00003,20UNIONSELECT 2006-2-2,00001,10UNIONSELECT 2006-2-3,00004,30UNIONSELECT 2006-2-3,00003,20GO--创建ProOut数据USE SaleGOINSERT INTO ProOutSELECT 2016-1-1,001,00001,10UNIONSELECT 2016-1-3,001,00001,5UNIONSELECT 2016-1-3,001,00001,5UNIONSELECT 2016-2-1,001,00001,10UNIONSELECT 2016-2-2,001,00001,10UNIONSELECT 2016-2-3,001,00001,20UNIONSELECT 2016-3-2,001,00001,10UNIONSELECT 2016-3-2,001,00001,30UNIONSELECT 2016-3-3,001,00001,20GO--实施数据完整性
--使用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(Price0)
GO
ALTER TABLE ProIn
ADD CONSTRAINT CK_ProIn_Quantity CHECK(Quantity0)
GO
ALTER TABLE ProOut
ADD CONSTRAINT CK_ProOut_Quantity CHECK(Quantity0)
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,QuantitySUM(Quantity),TotalMoneySUM(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.ProNoProOut.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.ProNoProOut.ProNo ANDProOut.CusNo CusNo
GROUP BY Product.ProNo,ProName,Price
GO
--执行语句
--EXEC P_Sale2 CusNo001
--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
