42.MS_SQL 2008 - 입력,출력,상세,수정,삭제,검색,뷰,인덱스 복습

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,'95354237','준철이','Godffs',100000000000000,'반품불가')

Insert Products Values(2,'test2','test','test',155,'test')

Insert Products Values(3,'test3','test','test',255,'test')

 

--출력

Select *From Products --전체출력

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 '준%'

Go

 

--뷰

Create View dbo.TopProduct

As

       Select *From Products Where UnitCost >= 200

Go

Select *From TopProduct

Go

 

 

--저장프로시저화

--입력: Add, Write, Insert

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-01.jpg', 200, '좋은노트북'

Go

 

--출력

Create Proc dbo.GetProducts

As

       Select *From Products--전체출력

Go

GetProducts

Go

 

--상세

Create Proc dbo.GetProductByProductID

       @ProductID Int

As

       Select *From Products Where ProductID = @ProductID

Go

Exec GetProductByProductID 4

Go

 

--수정: Update, Modify, Edit

Create Proc UpdateProduct

       @ModelName VarChar(50),

       @UnitCost Money,

       @Description VarChar(3800),

       @ProductID Int

As

       Update Products

       Set

             ModelName = @ModelName,

             UnitCost = @UnitCost,

             Description = @Description

       Where

             ProductID = @ProductID

Go

Update Product '니컴퓨터', 300, '내컴에서니컴으로', 2

Go

 

--삭제: Delete

Create Proc DeleteProduct

       @ProductID Int

As

       Delete Products Where ProductID = @ProductID

Go

 

--검색: Get~By~, Search, Find

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 Unique NonClustered Index On Products (ModelName)

--같은명의상품명이있을경우의예제

Go

---

42SQLStudysql.sql