看看我的数据库设计
CREATE TABLE Brands
(
BrandID INT IDENTITY CONSTRAINT Pk_Brands Primary Key
,Beandname Varchar(200)
)
CREATE TABLE InstrumentType
(
InstrumentTypeID INT IDENTITY CONSTRAINT Pk_InstrumentType Primary Key
,InstrumentName Varchar(200)
)
CREATE TABLE Products
(
ProductsID INT IDENTITY CONSTRAINT Pk_Products Primary Key
,BrandID INT
,InstrumentTypeID INT
,ProductImage Varchar(100)
)
ALTER TABLE [dbo].[Products] WITH CHECK ADD CONSTRAINT [FK_Products__Brands] FOREIGN KEY([BrandID])
REFERENCES [dbo].[Brands] ([BrandID])
GO
ALTER TABLE [dbo].[Products] WITH CHECK ADD CONSTRAINT [FK_Products_InstrumentTypesID] FOREIGN KEY([InstrumentTypeID])
REFERENCES [dbo].[InstrumentType] ([InstrumentTypeID])
GO
初始样本数据
INSERT INTO Brands (Beandname)
SELECT 'Ibanez' UNION ALL
SELECT 'Fender' UNION ALL
SELECT 'PRS' UNION ALL
SELECT 'ESP' UNION ALL
SELECT 'Warwick'
Go
INSERT INTO InstrumentType(InstrumentName)
SELECT 'Guitar' UNION ALL
SELECT 'Bass'
INSERT INTO Products(InstrumentTypeID,BrandID,ProductImage)
SELECT 1 , 1,'xyz.jpg' UNION ALL
SELECT 2 , 1,'hyz.jpg' UNION ALL
SELECT 1 , 2,'abc.jpg' UNION ALL
SELECT 2 , 2,'fgh.jpg' UNION ALL
SELECT 1 , 3,'yui.jpg' UNION ALL
SELECT 1 , 4,'mbm.jpg' UNION ALL
SELECT 2 , 5,'omo.jpg'
SELECT * FROM Brands
SELECT * FROM InstrumentType
SELECT * FRom Products
使用以下查询获取结果
SELECT
p.ProductsID
--,p.BrandID
,B.Beandname
--,p.InstrumentTypeID
,i.InstrumentName
,p.ProductImage
FROM Products P
INNER JOIN Brands B
On B.BrandID=p.BrandID
INNER JOIN InstrumentType I
On I.InstrumentTypeID=P.InstrumentTypeID
通过将参数传递到以下 Sp 将数据插入产品表
CREATE Procedure Usp_Products_Insert
(
@InstrumentTypeID INT,
@BrandID INT,
@ProductImage VARCHAR(100)
)
AS
BEGIN
IF EXISTS(
SELECT 1 FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME='Products'
)
Begin
INSERT INTO Products(
InstrumentTypeID,
BrandID,
ProductImage)
SELECT @InstrumentTypeID,
@BrandID,
@ProductImage
END
END
EXEC Usp_Products_Insert @InstrumentTypeID=1
,@BrandID=2
,@ProductImage='TTTT.Jpg'
SELECT * FRom Products