|
--상품리스트 출력
Create Table [dbo].[Products]
(
[ProductID] int identity (1,1) Primary Key Not Null,
[CategoryID] Int Not Null, --카테고리
[ModelNumber] VarChar (50), --상품코드
[ModelName] VarChar(50), --상품명
[ProductImage] VarChar (50), --이미지명
[UnitCost] money Not Null, --단가
[Description] VarChar (3800) --상품설명
)
Go
Insert Products Values (1,'좋은책','test','test.jpg',55,'test')
Insert Products Values (2,'내 컴퓨터','test','test.jpg',155,'test')
Insert Products Values (3,'니 컴퓨터','test','test.jpg',255,'test')
--출력
select *from [dbo].[Products] Order By ProductID Asc
--출력
Select *from Products Order By ProductID Desc
--상세
Select *From Products Where ProductID = 1
--수정
Update Products Set UnitCost = 100 Where ProductID = 1
--삭제
Delete Products Where 1 <> 1
--검색
Select *From Products Where ModelName Like '책%'
--View
Create View dbo.TopProduct
As
Select *From Products Where UnitCost >= 200
Go
Select *From TopProduct
Go
-------------------------------------------------
--저장 프로시저 나타내보기
-------------------------------------------------
Create Proc dbo.AddProduct
@CategoryID Int,
@ModelNumber VarChar(50),
@ModelName VarChar(50),
@ProductImage VarChar(50),
@UnitCost Money,
@Description VarChar(3800)
As
Insert Products
Values(@CategoryID, @ModelNumber, @ModelName, @ProductImage, @UnitCost, @Description)
Go
AddProduct 2, 'COM-02', '노트북','COM-1.jpg', 200, '좋은 노트북'
Go
--출력 : Get, List
Create Proc dbo.GetProducts
As
Select *from Products Order By ProductID Desc
Go
--상세 : Get, View
Create Proc dbo.GetProductByProductID
@ProductID int
As
Select *From Products Where ProductID = @ProductID
Go
Exec GetProductByProductID 4
Go
--수정 : Update, Modify, Edit
Create Proc UpdateProc
@ModelName VarChar(50),
@UnitCost Money,
@Description VarChar(3800),
@ProductID int
As
Update Products
Set
ModelName = @ModelName,
UnitCost = @UnitCost,
Description = @Description
Where
ProductID = @ProductID
Go
UpdateProc '개 컴퓨터', 300, '개 컴에서 용컴으로', 2
Go
--삭제 : Delete
Create Proc DeleteProduct
@ProductID int
As
Delete Products Where ProductID = @ProductID
Go
--검색 : Get~By, Search, Find
-- 매개변수화 ? 바뀔부분 => ' + 변수 + 'C
Create Proc SearchProducts
@SearchQuery VarChar(50)
As
--Declare @SearchQuery VarChar(50)
--Set @SearchQuery = '집'
Declare @sql VarChar(500)
Set @sql = '
Select *From Products Where ModelName Like ''' + @SearchQuery + '%'' '
Print @sql --확인
Exec(@sql) --실행
Go
SearchProducts '니'
--인덱스
Create NonClustered Index idxModelName On Products (ModelName)
Go | 
