CREATE DATABASE SaleManagerDB ON PRIMARY ( NAME=‘SaleManagerDB’, FILENAME=‘E:\SQL练习题\项目_超市管理系统4Day6\SaleManagerDB.mdf’, SIZE=10MB, FILEGROWTH=10MB ) LOG ON ( NAME=‘SaleManagerDB_log’, FILENAME=‘E:\SQL练习题\项目_超市管理系统4Day6\SaleManagerDB_log.ldf’, SIZE=10MB, FILEGROWTH=10MB ) GO
-------------------------------------------以下二个表建立主外键
GO –商品库存表 CREATE TABLE ProductInventory ( ProductId VARCHAR(20) PRIMARY key NOT NULL,–商品ID TotalCount int NOT NULL,–总数量 MinCount int NOT NULL,–最小库存 MaxCount int NOT NULL,–最大库存 Statusld int NOT NULL–库存状态 ) –ProductInventory设外键–FK_StudenId外键名字 ALTER TABLE ProductInventory ADD CONSTRAINT FK_Statusld FOREIGN KEY (Statusld) REFERENCES InventoryStatus(Statusld) GO –商品库存状态表 CREATE TABLE InventoryStatus ( Statusld int PRIMARY key CHECK(Statusld=1 OR Statusld=-1 OR Statusld=2 OR Statusld=-2) NOT NULL,–管理员姓名 StatusDesc VARCHAR(50) NOT NULL,–1正常，-1低于库存，2高于库存，-2清仓 ) GO
-------------------------------------------以下三个表建立主外键
GO –商品销售统计表–流水表 CREATE TABLE SalesListDetall ( Id INT PRIMARY key IDENTITY(1000000,1) NOT NULL,–（1000000,1） SerialNum VARCHAR(50), --流水号 ProductId VARCHAR(50) NOT NULL, --商品编号 ProductName VARCHAR(50) NOT NULL, --商品名称 UnitPrice NUMERIC(10, 2) NOT NULL,–商品单价 Discount INT, --折扣 Quantity INT NOT NULL,–销售数量 SubTotalMoney NUMERIC(10, 2) --小计金额 ) –主外键设置 ALTER TABLE SalesListDetall ADD CONSTRAINT FK_SerialNum FOREIGN KEY (SerialNum) REFERENCES SalesList(SerialNum)–主键 GO –销售主表（发票）–打印流水表 CREATE TABLE SalesList ( SerialNum VARCHAR(50) PRIMARY KEY NOT NULL ,–流水号系統生成 TotalMoney NUMERIC(10, 2) NOT NULL,–购物总价 RealReceive NUMERIC(10, 2) NOT NULL,–实际收款 ReturnMoney NUMERIC(10, 2) NOT NULL,–找零 SalesPersonId INT, --销售员ID SaleDate SMALLDATETIME NOT NULL --时间 ) –主外键设置 ALTER TABLE SalesList ADD CONSTRAINT FK_SalesPersonId FOREIGN KEY (SalesPersonId) REFERENCES SelesPerson(SalesPersonId)–主键 –前台收银员登录 CREATE TABLE SelesPerson ( SalesPersonId INT PRIMARY key IDENTITY(10000,1) NOT NULL,–销售ID 10000 SPName VARCHAR(50) NOT NULL,–销售员 LoginPwd VARCHAR(50) NOT NULL,–销售员登录密码 )
–添加前台员 INSERT INTO SelesPerson VALUES(‘张三’,‘123’), (‘李四’,‘123’), (‘王麻子’,‘123’)
SELECT * FROM SelesPerson GO
-------------------------------------------以下三个表建立主外键
GO –商品表 CREATE TABLE Products ( ProductId VARCHAR(50) PRIMARY KEY NOT NULL,–商品编号商品条码 ProductName VARCHAR(50) NOT NULL,–商品名称 UnitPrice NUMERIC(5,2) NOT NULL,–单价 Unit VARCHAR(50) NOT NULL,–计量单位 Discount INT NULL,–折扣 Categoryld INT NOT NULL–商品分类 ) SELECT * FROM Products –主外键设置 ALTER TABLE Products ADD CONSTRAINT FK_Categoryld FOREIGN KEY (Categoryld) REFERENCES ProductCategory(CategoryId)–主键 GO CREATE TABLE ProductCategory --商品分类表 ( CategoryId INT PRIMARY KEY IDENTITY(1,1) NOT NULL,–IDENTITY(1,1),–商品分类ID1,1 CategoryName VARCHAR(20) NOT NULL–商品分类 ) SELECT * FROM ProductCategory GO CREATE TABLE ProductStorage–商品入库表 ( StorageId INT PRIMARY KEY IDENTITY(100000,1) NOT NULL, --IDENTITY(100000,1), ProductId VARCHAR(50), --商品ID AddedCount INT NOT NULL,–入库数量 CurrentTime SMALLDATETIME NOT NULL–入库时间 ) –主外键设置 ALTER TABLE ProductStorage ADD CONSTRAINT FK_ProductId FOREIGN KEY (ProductId) REFERENCES Products(ProductId)–主键 GO
-------------------------------------------以下的表都是单独的
GO CREATE TABLE SMMembers --会员表 ( MemberId INT PRIMARY KEY IDENTITY(100200300,1) NOT NULL,–IDENTITY(100200300,1),–会员卡号 MemberName VARCHAR(50) NOT NULL,–会员姓名 Points INT NOT NULL,–会员积分10元=1 PhoneNumber VARCHAR(200) NOT NULL,–联系电话 MemberAddress TEXT NOT NULL,–联系地址 OpenTime DATETIME, --开户时间 MemberStatus INT NOT NULL–会员卡状态1正，0冻，-1注销 ) GO CREATE TABLE LoginLogs --职员登录表 ( LogId INT PRIMARY KEY IDENTITY(1,1) NOT NULL ,–IDENTITY(1,1),–1,1 LoginId INT NOT NULL,–登录者ID SPName VARCHAR(50) NULL,–登录人姓名 ServerName VARCHAR(100) NULL,–登录服务器名 LoginTime DATETIME NOT NULL,–登录时间 ExitTime DATETIME NULL–退出时间 ) GO CREATE TABLE SysAdmins --管理员表 ( LoginId INT PRIMARY KEY IDENTITY(10000,1) NOT NULL,–登录账号2000 LoginPwd VARCHAR(20) ,–登录密码 AdminName VARCHAR(20), --管理员名 AdminStatus BIT ,–当前状态1启0禁 Roleld INT --角色类型1超级2一般 ) GO CREATE TABLE ProductUnit --商品计量单位表 ( Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,–1,1 Unit VARCHAR(20) NOT NULL --商品计量单位 ) SELECT * FROM ProductUnit GO
------------------------数据写入表中------------------ –添加前台员 INSERT INTO SelesPerson VALUES(‘张三’,‘123’), (‘李四’,‘123’), (‘王麻子’,‘123’) ----会员表 INSERT INTO SMMembers SELECT ‘震雷削’,0,‘13825138120’,‘陕西安康’,‘2020-4-06’,1UNION SELECT ‘贺栋’,0,‘15029063866’,‘地球中国’,‘2020-4-06’,1UNION SELECT ‘蔚楠’,0,‘15029063865’,‘陕西渭南’,‘2020-4-06’,1UNION SELECT ‘邵琦’,0,‘15029063866’,‘陕西’,‘2020-4-06’,1UNION SELECT ‘李志蛋’,0,‘15029063866’,‘陕西咸阳’,‘2020-4-06’,1 TRUNCATE TABLE SMMembers –管理员表 TRUNCATE TABLE SysAdmins INSERT INTO SysAdmins SELECT 123456,‘姜子牙’,‘False’,2UNION SELECT 123456,‘李靖’,‘True’,2UNION SELECT 123456,‘雷震子’,‘True’,1UNION SELECT 123456,‘哪吒’,‘True’,1UNION SELECT 123456,‘木吒’,‘False’,1UNION SELECT 123456,‘金吒’,‘True’,1 SELECT * FROM SysAdmins –品计量单位表 INSERT INTO ProductUnit VALUES (‘箱’),(‘个’),(‘瓶’),(‘盒’),(‘本’),(‘袋’),(‘只’),(‘条’),(‘桶’),(‘打’),(‘听’),(‘罐’),(‘张’),(‘块’) –商品分类表 INSERT INTO ProductCategory VALUES (‘饮料’),(‘副食’),(‘面食’),(‘肉类’),(‘米类’),(‘酒类’),(‘烟类’),(‘文具类’),(‘玩具类’),(‘日用品’) –商品表 TRUNCATE TABLE Products–复原数据 SElECT * FROM Products INSERT INTO Products SELECT ‘6002504003331’,‘康师傅统一牛肉面’,‘40.00’,‘箱’,0,3 UNION SELECT ‘6002504003332’,‘老坛酸菜牛肉面’,‘45.00’,‘箱’,0,3 UNION SELECT ‘6002504003333’,‘雪花啤酒’,‘60.00’,‘箱’,0,6 UNION SELECT ‘6002504003334’,‘燕京啤酒’,‘61.00’,‘箱’,0,6 UNION SELECT ‘6002504003335’,‘可口可乐’,‘6.00’,‘瓶’,0,1 UNION SELECT ‘6002504003336’,‘百世可乐’,‘5.50’,‘瓶’,0,1 UNION SELECT ‘6002504003337’,‘统一鲜橙多’,‘5.00’,‘瓶’,0,1 UNION SELECT ‘6002504003338’,‘法式小面包’,‘8.00’,‘袋’,0,2 UNION SELECT ‘6002504003339’,‘黑人牙膏’,‘6.80’,‘盒’,0,10 UNION SELECT ‘6002504003341’,‘平原小米’,‘102.00’,‘袋’,0,5 UNION SELECT ‘6002504003342’,‘纯棉毛巾’,‘9.90’,‘条’,0,10 –UPDATE Products SET ProductName=REPLACE(ProductName,‘雪花’,‘燕京’) –商品库存状态表 INSERT INTO InventoryStatus VALUES (-2,‘已清仓’),(-1,‘低于库存’),(1,‘正常’),(2,‘高于库存’) –商品库存表 TRUNCATE TABLE ProductInventory–复原数据 INSERT INTO ProductInventory SELECT ‘6002504003331’,240,300,500,1 UNION SELECT ‘6002504003332’,240,300,500,1 UNION SELECT ‘6002504003333’,800,200,1000,1 UNION SELECT ‘6002504003334’,790,200,1000,1 UNION SELECT ‘6002504003335’,180,200,500,1 UNION SELECT ‘6002504003336’,180,200,500,1 UNION SELECT ‘6002504003337’,300,200,500,1 UNION SELECT ‘6002504003338’,100,200,400,1 UNION SELECT ‘6002504003339’,100,150,300,1 UNION SELECT ‘6002504003341’,160,100,200,1 UNION SELECT ‘6002504003342’,100,150,250,1
GO
–查看 SELECT * FROM SelesPerson WHERE SalesPersonId=10000
–登录的存储过程 CREATE PROC SelesLogeIn @LoginId INT, @LoginPwd NVARCHAR(50) AS SELECT * FROM SelesPerson WHERE SalesPersonId=@LoginId AND LoginPwd=@LoginPwd
–登录日志的存储过程 CREATE PROC WriteLog @LoginId INT, @SPName VARCHAR(50), @ServerName VARCHAR(100) AS INSERT INTO LoginLogs(LoginId,SPName,ServerName,LoginTime) VALUES(@LoginId,@SPName,@ServerName,GETDATE()) SELECT @@IDENTITY–最后的标识列（返回记录当前的Id值）
–退出日志 CREATE PROC ExitSysWriteLog @LogId INT AS UPDATE LoginLogs SET ExitTime=GETDATE() WHERE LogId=@LogId
SELECT * FROM LoginLogs SELECT * FROM [dbo].[Products] SELECT * FROM [dbo].[SalesListDetall]
–获取系统时间存储过程 CREATE PROC GetSysTime AS SELECT GETDATE() EXEC GetSysTime
–获取Products商品表的存储过程 –查商品表中的所有字段 –ProductCategory商品分类表 CREATE PROC GetProductWithId @ProductId VARCHAR(20)–创建局部变量的商品Id AS SELECT ProductId,ProductName,UnitPrice,Unit,Discount,Products.Categoryld,ProductCategory.CategoryName FROM Products INNER JOIN ProductCategory ON Products.Categoryld=ProductCategory.CategoryId WHERE ProductId=@ProductId –GetProductWithId中的ID查找对应的商品表 EXEC GetProductWithId ‘6002504003331’
----------------------------------------4个----------------------------------------------------
–销售主表（发票）的数据：先写入数据库 –以发票的形式记录消费的客户的小票数据 CREATE PROC AddSalesList @SerialNum NVARCHAR(50), @TotalMoney NUMERIC(10,2), @RealReceive NUMERIC(10,2), @ReturnMoney NUMERIC(10,2), @SalesPersonId INT AS INSERT INTO SalesList(SerialNum,TotalMoney,RealReceive,ReturnMoney,SalesPersonId,SaleDate) VALUES(@SerialNum,@TotalMoney,@RealReceive,@ReturnMoney,@SalesPersonId,GETDATE())
–商品销售统计的数据：先写入数据库（在页面中的这些数据记录在数据库中） –商品销售统计表：就是顾客买的东西记录一下 CREATE PROC AddSalesListDetail @SerialNum NVARCHAR(50), @ProductId NVARCHAR(50), @ProductName NVARCHAR(50), @UnitPrice NUMERIC(10,2), @Discount FLOAT, @Quantity INT, @SubTotalMoney NUMERIC(10,2) AS INSERT INTO SalesListDetall(SerialNum,ProductId,ProductName,UnitPrice,Discount,Quantity,SubTotalMoney) VALUES(@SerialNum,@ProductId,@ProductName,@UnitPrice,@Discount,@Quantity,@SubTotalMoney)
SELECT * FROM SalesListDetall
–修改会员积分：更新会员的积分 CREATE PROC RefreshMemberPint @Points INT, @memberId INT AS UPDATE SMMembers SET Points=Points+@Points WHERE MemberId=@memberId
–通过ID获取会员 –CREATE PROC GetMemberById –@MemberId INT –AS –SELECT * FROM SMMembers WHERE MemberId=@MemberId – exec GetMemberById ‘100200302’
–管理者的登录存储过程 –通过Id和pwd找到管理者的所有内容 CREATE PROC SysAdminLogin @logId int, @LogPwd VARCHAR(20) AS SELECT * FROM SysAdmins WHERE LoginId=@logId AND LoginPwd=@LogPwd exec SysAdminLogin 10001,123456 SELECT * FROM SysAdmins
–管理者修改密码的存储过程 CREATE PROC UpdateSysPwd @logId int, @LogPwd VARCHAR(20) AS UPDATE SysAdmins SET LoginPwd=@LogPwd WHERE LoginId=@logId
–1.商品分类表ProductCategory：通过存储过程获取商品分类表的所有的数据 CREATE PROC GetProductCategory AS select * from ProductCategory order by CategoryId
–2.商品计量单位表ProductUnit：通过存储过程获取商品计量单位表的所有的数据 CREATE PROC GetProductUnit AS select * from ProductUnit order by Id
–3.商品表Products：给商品表添加内容 CREATE PROC InsertProduct @ProductId NVARCHAR(50), @ProductName NVARCHAR(50), @UnitPrice NUMERIC(10,2), @Unit NVARCHAR(10), @discount float, @categoryId int AS INSERT INTO Products VALUES(@ProductId,@ProductName,@UnitPrice,@Unit,@discount,@categoryId)
–4.商品库存表ProductInventory：根据商品ID，添加商品的数量，添加商的状态品最大值和最小值，添加商品 CREATE PROC InsertInventory @ProductId NVARCHAR(50), @MinCount int, @MaxCount int AS INSERT INTO ProductInventory VALUES(@ProductId,0,@MinCount,@MaxCount,1) –查 SElECT * FROM ProductInventory
–5.查询所有商品表；并给商品表的Id排序 CREATE PROC GetAllProduct AS select * from Products –查 SElECT * FROM Products
–6.商品数量表的存储过程：修改商品数量表的数量 CREATE PROC InventoryIn @productId NVARCHAR(50), @count int AS UPDATE ProductInventory SET TotalCount=@count where ProductId = @productId
–7.执行的是下面的方法（这是以前弄好的存储过程，在ADO.net要使用所以给这里写一遍）
–获取Products商品表的存储过程 –查商品表中的所有字段 –ProductCategory商品分类表 CREATE PROC GetProductWithId @ProductId VARCHAR(20)–创建局部变量的商品Id AS SELECT ProductId,ProductName,UnitPrice,Unit,Discount,Products.Categoryld,ProductCategory.CategoryName FROM Products INNER JOIN ProductCategory ON Products.Categoryld=ProductCategory.CategoryId WHERE ProductId=@ProductId –GetProductWithId中的ID查找对应的商品表 EXEC GetProductWithId ‘6002504003331’
–4Day14 –2.添加会员人员；@@IDENTITY立马获取会员序号 –SMMembers会员表 CREATE PROC AddMember @memberName NVARCHAR(50), @phoneNumber NVARCHAR(50), @memberAddress TEXT AS INSERT INTO SMMembers VALUES(@memberName,0,@phoneNumber,@memberAddress,GETDATE(),1) SELECT @@IDENTITY
–1.手机或会员ID都可获取会员（获取原来的手机会员的获取：通过Id编号或手机号；在C#中的作用是判断这个Id编号或手机号是否在数据库中可以拿到） –通过ID获取会员 CREATE PROC GetMemberById @MemberId INT, @PhoneNumber nvarchar(200) AS SELECT * FROM SMMembers WHERE MemberId=@MemberId OR PhoneNumber=@PhoneNumber
–3.获取数据库日志的所有内容数据条数 CREATE PROC GetTableCount @TableName NVARCHAR(20) AS DECLARE @sql NVARCHAR(200) SELECT @sql='SELECT COUNT(*) FROM '+@TableName EXEC(@sql) –查询 EXEC GetTableCount ‘[LoginLogs]’
–4.日志的所有内容查询 –数据库中记录收银人员和管理人员登录的【日志】的所有内容查询 CREATE PROC GetLoginLogs AS SELECT * FROM LoginLogs –查询日志所有内容 EXEC GetLoginLogs
–5.获取数据库日志：并进行分页查询 CREATE PROC GetLoginLogsByPage @pageCount INT,–每页数量 @pageIndex INT,–页码 @sortType INT AS IF(@sortType=1) BEGIN SELECT TOP(@pageCount)* FROM LoginLogs WHERE LogId NOT IN (SELECT TOP((@pageIndex-1)@pageCount) LogId FROM LoginLogs ORDER BY LogId ASC) ORDER BY LogId ASC END ELSE IF(@sortType=0) BEGIN SELECT TOP(@pageCount) FROM LoginLogs WHERE LogId NOT IN (SELECT TOP((@pageIndex-1)*@pageCount) LogId FROM LoginLogs ORDER BY LogId DESC) ORDER BY LogId DESC END –查询页数 exec GetLoginLogsByPage 10,3,1
SELECT TOP(10)* FROM LoginLogs WHERE LogId NOT IN (SELECT TOP(10) LogId FROM LoginLogs ORDER BY LogId ) ORDER BY LogId
–6.数据库中日志LoginLogs的模糊查询 CREATE PROC GetLoginLogBy @startname DATETIME, @endTime DATETIME, @where varchar(50), @check INT AS –按照时间区间查询 IF(@check=2) BEGIN set @endTime=DATEADD(DAY,1,@startname) END –同时按照时间区间查询 ELSE IF(@where<>’’ AND (@check=2 OR @check=1 )) BEGIN SELECT * FROM LoginLogs WHERE LoginTime>=@startname AND LoginTime<=@endTime AND ((CONVERT(nvarchar,LoginId) like ‘%’+@where+’%’ or SPName like ‘%’+@where+’%’ or ServerName like ‘%’+@where+’%’)) END –只按照条件查询 ELSE IF(@where<>’’ AND @check=0) BEGIN SELECT * FROM LoginLogs WHERE ((CONVERT(nvarchar,LoginId) like ‘%’+@where+’%’ or SPName like ‘%’+@where+’%’ or ServerName like ‘%’+@where+’%’)) END –查询时间之后的待条件 ELSE IF(@where<>’’ AND @check=-1) BEGIN SELECT * FROM LoginLogs WHERE(CONVERT(nvarchar,LoginId) like ‘%’+@where+’%’ or SPName like ‘%’+@where+’%’ or ServerName like ‘%’+@where+’%’ ) AND LoginTime>=@startname END –查询时间之后的不待条件 ELSE IF(@where=’’ AND @check=-1) BEGIN SELECT * FROM LoginLogs WHERE LoginTime>=@startname END
SELECT * FROM LoginLogs WHERE LoginTime>=‘2020/4/14 0:00:00’
–4Day15
–1.GetAllTables可以获取所有表的储存过程（可以获取不同的表） –管理者的所有内容获取，并给管理者内容添加储存过程 –营业员的表也可以获取 CREATE PROC GetAllTables @tableName NVARCHAR(20) AS DECLARE @sqlStr NVARCHAR(400) SET @sqlStr='SELECT * FROM '+@tableName EXEC(@sqlStr)
EXEC GetAllTables ‘[dbo].[SelesPerson]’
–3.添加管理者 CREATE PROC InsertAdmin @adminName NVARCHAR(50), @loginPwd NVARCHAR(50), @roleId INT AS INSERT INTO SysAdmins VALUES(@loginPwd,@adminName,1,@roleId) SELECT @@IDENTITY --返回数据
–4.修改管理系统 CREATE PROC UpdateAdmin @adminName NVARCHAR(50), @loginPwd NVARCHAR(50), @roleId INT, @loginId INT AS UPDATE [dbo].[SysAdmins] SET LoginPwd=@loginPwd,AdminName=@adminName,Roleld=@roleId WHERE LoginId=@loginId
–5.当前状态1启0禁的修改过程 –管理者的修改状态1启0禁 CREATE PROC SetSysAdmStatus @role BIT, --当前状态1启0禁（看是哪个0/1） @id INT --根据管理员Id AS UPDATE [dbo].[SysAdmins] SET AdminStatus=@role WHERE LoginId=@id
–6.营业员的添加储存过程 CREATE PROC InsertSales @spName NVARCHAR(50), @loginPwd NVARCHAR(50) AS INSERT INTO [dbo].[SelesPerson] VALUES(@spName,@loginPwd) SELECT @@IDENTITY–返回数据
–7.营业员的修改储存过程 CREATE PROC UpdateSaleInfor @saleName nvarchar(50), @loginPwd nvarchar(50), @loginId INT AS UPDATE [dbo].[SelesPerson] SET SPName=@saleName,LoginPwd=@loginPwd WHERE SalesPersonId=@loginId
–8.商品的各种数据形成多表链接：商品Products表，商品分类表ProductCategory，商品库存表ProductInventory等多表链接 CREATE PROC GetAllProducts as SELECT Products.ProductId,ProductName,UnitPrice,Unit,Discount,Products.Categoryld,ProductCategory.CategoryName,ProductInventory.TotalCount FROM Products INNER JOIN ProductCategory ON ProductCategory.CategoryId=Products.Categoryld INNER JOIN ProductInventory ON ProductInventory.ProductId=Products.ProductId –查询该表 EXEC GetAllProducts
— 商品的各种数据形成多表链接：商品Products表，商品分类表ProductCategory，商品库存表ProductInventory等多表链接的查询 CREATE PROC GetProductsByWhere @category INT, @where NVARCHAR(50) AS IF (@category=0 AND @where<>’’) BEGIN SELECT Products.ProductId,ProductName,UnitPrice,Unit,Discount,Products.Categoryld,ProductCategory.CategoryName,ProductInventory.TotalCount FROM Products INNER JOIN ProductCategory ON ProductCategory.CategoryId=Products.Categoryld INNER JOIN ProductInventory ON ProductInventory.ProductId=Products.ProductId WHERE Products.ProductId LIKE ‘%’+@WHERE+’%’ OR ProductName like ‘%’+@WHERE+’%’ END ELSE IF(@where<>’‘AND @category<>0) BEGIN SELECT Products.ProductId,ProductName,UnitPrice,Unit,Discount,Products.Categoryld,ProductCategory.CategoryName,ProductInventory.TotalCount FROM Products INNER JOIN ProductCategory ON ProductCategory.CategoryId=Products.Categoryld INNER JOIN ProductInventory ON ProductInventory.ProductId=Products.ProductId WHERE (Products.ProductId LIKE ‘%’+@WHERE+’%’ OR ProductName like ‘%’+@WHERE+’%’) AND Products.Categoryld=@category END ELSE IF(@where=’’ AND @category<>0) BEGIN SELECT Products.ProductId,ProductName,UnitPrice,Unit,Discount,Products.Categoryld,ProductCategory.CategoryName,ProductInventory.TotalCount FROM Products INNER JOIN ProductCategory ON ProductCategory.CategoryId=Products.Categoryld INNER JOIN ProductInventory ON ProductInventory.ProductId=Products.ProductId WHERE Products.Categoryld=@category END
–9.商品表：通过商品Id编号修改折扣Discount CREATE PROC SetProductDiscount @discount Float, @productId NVARCHAR(50) AS UPDATE Products SET Discount=@discount WHERE ProductId=@productId
–10.商品表修改名字、单价、商品分类、计量单位，通过Id商品编号修改商品表的内容 CREATE PROC SetProductInfor @productName NVARCHAR(50),–商品名称 @unitPrice NUMERIC(20,2),–单价 @categoryId int,–商品分类 @unit nvarchar(10),–计量单位 @productId NVARCHAR(50)–商品编号 AS UPDATE Products SET ProductName=@productName,UnitPrice=@unitPrice,Categoryld=@categoryId,Unit=@unit WHERE ProductId=@productId
–自己添加的内容 –商品库存表 CREATE PROC PIku AS –SElECT * FROM ProductInventory SElECT ProductInventory.ProductId,Products.ProductName,Products.Unit,ProductInventory.MinCount,ProductInventory.MaxCount,ProductInventory.Statusld,ProductInventory.TotalCount FROM ProductInventory INNER JOIN Products ON ProductInventory.ProductId=Products.ProductId
