45.MS_SQL 2008 - 도시락 주문 테이블

--사람

Create Table Members

(

       MemberID Int Identity(1,1) Primary Key, --일련번호

       Name VarChar(25) Not Null  --이름

)

Go

 

--메뉴

Create Table Menu

(

       MenuID Int Identity(1,1) Primary Key,  --일련번호

       MenuName VarChar(50) Not Null,  --메뉴명

       Price Int Not Null  --가격

)

Go

 

--주문

Create Table Orders

(

       ItemID Int Identity(1, 1) Primary Key,  --일련번호

       MemberID Int References Members(MemberID),  --회원번호

       MenuID Int References Menu(MenuID),  --메뉴번호

       Status Bit Default(0),  --옵션: 0(기본), 1(곱빼기)

       Quantity TinyInt,  --수량

       OrderedDate SmallDateTime Default(GetDate()), --주문일시

       etc VarChar(4000) Null  --기타

)

 

--6개예시문

--[!] 멤버

Insert Members Values('홍길동');

Go

Insert Members Values('백두산');

Go

Insert Members Values('한라산');

Go

Select * From Members Order By MemberID Desc

Go

Select * From Members Where MemberID = 1

Go

Update Members Set Name = '임꺽정' Where Name = '홍길동'

Go

Delete Members Where MemberID = 1 And (1 <> 1)

Go

Select * From Members Where Name Like '홍%'

Go

 

--[!] 메뉴

Insert Into Menu Values('돈까스', '3000')

Go

Insert Into Menu Values('돈까스, 밥빼기', '2500')

Go

Insert Into Menu Values('돈까스, 반찬추가', '3500')

Go

Select * From Menu Order By MenuName Asc

Go

Select * From Menu Where MenuID = 1

Go

Update Menu Set Price = '5000' Where MenuName Like '돈까스%'

Go

Delete Menu Where MenuID = 1 And (1 <> 1)

Go

Select * From Menu Where MenuName = '돈%'

Go

 

--[!] 주문

Insert Into Orders Values(1, 1, 0, 1, GETDATE(), '')

Go

Insert Into Orders Values(2, 2, 1, 1, GETDATE(), '')

Go

Insert Into Orders Values(1, 3, 0, 1, GETDATE(), '')

Go

Select * From Orders Order By MemberID

Go

Select * From Orders Where MemberID = 1

Go

Update Orders Set Quantity = 2 Where MemberID = 1

Go

Delete Orders Where MemberID = 1 And (1 <> 1)

Go

Select * From Orders Where etc = '기'

 

 

--뷰: [주문자목록]

--홍길동, 돈까스, 3000, 1개, 2009-09-21

Create View [주문자목록]

       With Encryption --숨기고싶을때

As

       SELECT Members.Name, Menu.MenuName,

Menu.Price, Orders.Quantity,

Orders.OrderedDate, Orders.etc

       FROM   Members INNER JOIN

Orders ON Members.MemberID

= Orders.MemberID INNER JOIN

Menu ON Orders.MenuID = Menu.MenuID

       --Order By OrderedDate Desc

Go

 

Select * From [주문자목록] Order By OrderedDate Desc

--주문서작성: 전화걸기

--돈까스, 10개, 30000

--돈까스스, 2개, 7000

--치킨마요, 1개, 2500

Select m.MenuName, Sum(o.Quantity), Sum(m.Price)

From Menu m, Orders o

Where m.MenuID = o.MemberID

       And YEAR(OrderedDate) = YEAR(GetDate())

       And Month(OrderedDate) = Month(GetDate())

       And Day(OrderedDate) = Day(GetDate())

Group By m.MenuName

Go

 

--[1] 저장프로시저

--18개저장프로시저만들기

--입력(Add, Insert, Write), 출력, 상세, 수정, 삭제, 검색

 

Create Proc dbo.AddMember

       @Name VarChar(25)

As

       Insert Members Values(@Name)

Go

 

Create Proc dbo.GetMember

As

       Select * From Members Order By MemberID Desc

Go

 

Create Proc dbo.GetMemberByMemberID

       @MemberID Int

As

       Select * From Members Where MemberID = @MemberID

Go

 

Create Proc UpdateMember

       @Name VarChar(25),

       @MemberID Int

As

       Update Members Set Name = @Name

Where MemberID = @MemberID

Go

 

Create Proc dbo.DeleteMember

       @MemberID Int

As

       Delete Members Where MemberID = @MemberID

Go

 

Create Proc dbo.SearchMember

       @SearchQuery VarChar(50)

As

       Declare @sql VarChar(500) -- 홍-> ' + 변수+ '

       Set @sql = '

             Select * From Members Where Name Like '''

+ @SearchQuery + '%'''

       Exec(@sql)

Go

SearchMember '백'

Go

 

 

--[!]메뉴관련저장프로시저개만들기

Create Proc dbo.AddMenu

       @MenuName VarChar(25),

       @Price Int

As

       Insert Menu Values(@MenuName, @Price)

Go

 

Create Proc dbo.GetMenu

As

       Select * From Menu Order By MenuID Desc

Go

 

Create Proc GetMenuByMenuID

       @MenuID Int

As

       Select * From Menu Where MenuID = @MenuID

Go

 

Create Proc UpdateMenu

       @MenuName VarChar(25),

       @MenuID Int

As

       Update Menu Set MenuName = @MenuName

Where MenuID = @MenuID

Go

 

Create Proc dbo.DeleteMenu

       @MenuID Int

As

       Delete Menu Where MenuID = @MenuID

Go

 

Create Proc dbo.SearchMenu

       @SearchQuery VarChar(50)

As

       Declare @sql VarChar(500) -- 홍-> ' + 변수+ '

       Set @sql = '

             Select * From Menu Where MenuName Like '''

+ @SearchQuery + '%'''

       Exec(@sql)

Go

 

--[!]주문관련저장프로시저개

Create Proc AddOrder

       @MemberID Int,

       @MenuID Int,

       @Status Bit,

       @Quantity TinyInt,

       @OrderedDate SmallDateTime,

       @Etc VarChar(4000)

As

       Insert Into Orders Values

(@MemberID, @MemberID, @Status,

@Quantity, @OrderedDate, @Etc)

Go

 

Create Proc GetOrders

       @ItemID Int

As

       Select *From Orders Where ItemID = @ItemID

Go

 

--일별주문상제

 

Create Proc GetOrdersByDate

       @Year Int,

       @Month Int,

       @Day Int

As

       Select m.MenuName, SUM(o.Quantity),

SUM(o.Quantity * m.Price)

       From Menu m, Orders o

       Where m.MenuID = o.MenuID

             And YEAR(@Year) = YEAR(GETDATE())

             And MONTH(@Month) = MONTH(GETDATE())

             And DAY(@Day) = DAY(GETDATE())

       Group By m.MenuName

Go

 

Create Proc UpdateOrder

       @ItemID Int,

       @MemberID Int,

       @MenuID Int,

       @Status Bit,

       @Quantity TinyInt,

       @OrderedDate SmallDateTime,

       @Etc VarChar(4000)

As

       Update Orders Set MemberID = @MemberID,

MenuID = @MenuID, Status = @Status,

Quantity = @Quantity,

OrderedDate = @OrderedDate,

Etc = @Etc

Where ItemID = @ItemID

Go

---

45Bob.sql