목표 : http://www.sp.com/AdminMemo/Default/aspx

한줄 짜리 메모를 입력하고, 출력하는 웹 애플리케이션이 여러분들 서버에서 가동되도록 하자…

 

Windows Server 레벨

1. 설치 : hostname, ipconfig /all, ping, localhost/127.0.0.1 : 로컬 루프백 주소
2. 사용자 : sp / Pa$$w0rd, net user, net localgroup
3. NFTS : everyone, IUSR_컴퓨터명, Network Service
4. 공유 : UNC
5. 웹 : URL, WWW(80포트)
6. FTP : 21번포트
7. DNS : nslookup
8. 미디어서버 : MMS://
9. 백업/복원, 메일(전송 : SMTP(25번포트), 수신 : POP3(110번 포트))
10. 원격관리 : "Remote Desktop Users"

 

SQL Server 레벨

1. 설치 : (local)\SQLExpress, (local)
2. 데이터베이스 : sp
3. 로그인 : sp / Pa$$w0rd
4. UDL
5. 테이블 : AdminMemos
6. SQL문
7. T-SQL
8. 개체 : 테이블, 뷰, 저장프로시저
9. DTS
10. 백업 및 복원

 

Visual Studio 레벨

1. 설치
2. 새 웹 사이트
3. Web.config : SQL Server 레벨의 데이터베이스 연결문자열 설정
4. 웹 폼 생성
5. 입력 페이지 작성
6. 출력 페이지 작성

--[1] 테이블설계

Create Table dbo.AdminMemos

(

       Num Int Identity(1, 1) Not Null Primary Key,  -- 일련번호

       [Content] Text Not Null                       -- 내용

)

Go

--[2] 입력예시문: 글쓰기, 회원가입

Insert AdminMemos Values('저장프로시저연습')

Go

--[3] 출력예시문: 리스트, 회원리스트, 상품리스트

Select * From AdminMemos Order By Num Desc

Go

--[4] 상세예시문: 회원상세보기, 주문상세내역, 게시물보기

Select Num, Content From AdminMemos Where Num = 1

Go

--[5] 수정예시문: 회원정보수정, 게시물수정

Begin Tran

       Update AdminMemos

       Set Content = '저장프로시저연습'

       Where Num = 1

       -- RollBack Tran

Commit Tran

--[6] 삭제예시문: 회원탈퇴, 게시물삭제

Begin Tran

       Delete AdminMemos

       Where Num = 1

       --RollBack Tran

Commit Tran

--[7] 검색예시문: 회원검색, 상품검색

Select Num, Content

From AdminMemos

Where [Content] Like '%프로시저%'

Go

-- 간단한T-SQL 저장프로시저문법

--[8] 1부터100까지짝수일때에는Content에'안녕'을입력,

--    홀수일때에는'방가'를입력

Declare @i Int

Set @i = 1

While @i <= 100

Begin

       If @i % 2 = 0

       Begin

             Insert AdminMemos Values('안녕')

       End

       Else

       Begin

             Insert Into AdminMemos Values('방가')

       End

       Set @i = @i + 1

End

Go

--[9] 뷰생성: 전체레코드를조회

Create View dbo.GetAdminMemos

As

       Select Num, Content From AdminMemos

Go

-- 뷰로부터조회

Select * From GetAdminMemos Order By Num Desc

Go

--[10] 입력예시문-> 프로그래밍화(매개변수화) -> 입력저장프로시저

Create Procedure dbo.AddAdminMemo

As

       Insert AdminMemos Values('저장프로시저연습')

Go

--[11] 저장프로시저실행: Execute, Exec, 저장프로시저명

Execute AddadminMemo

Go

Exec AddAdminMemo

Go

AddAdminMemo

Go

--[12] 저장프로시저수정

Alter Procedure dbo.AddAdminMemo

       @Content VarChar(8000) -- Text?

As

       Insert AdminMemos Values(@Content)

Go

--[13] 매개변수가있는저장프로시저실행

Exec AddAdminMemo '안녕'

Go

Exec AddAdminMemo '방가'

Go

--[14] 출력: 긴SQL문을짧은하나의명령어(저장프로시저명)으로변환

Create Procedure dbo.ListAdminMemos

As

       Select * From AdminMemos Order By Num Desc

Go

--[15] 매개변수가없는저장프로시저테스트

Exec ListAdminMemos

Go

--[16] 상세: 넘겨주는값에따른결과생성

Create Proc dbo.ViewAdminMemos

       @Num Int

As

       Select Num, Content From AdminMemos Where Num = @Num

Go

--[17] 매개변수가있는저장프로시저실행

Exec ViewAdminMemos 100

Go

Exec ViewAdminMemos 5

Go

--[18] 넘겨져온번호에해당하는내용을넘겨져온값으로수정

Create Proc dbo.ModifyAdminMemos

       @Content VarChar(8000),

       @Num Int

As

       Begin Tran

             Update AdminMemos

             Set Content = @Content

             Where Num = @Num

             --RollBack Tran

       Commit Tran

Go

--[19] 위저장프로시저실행예시

Exec ModifyAdminMemos '반갑습니다.', 100

Go

Exec ViewAdminMemos 100

Go

--[20] 삭제저장프로시저생성

Create Proc dbo.DeleteAdminMemos

       @Num Int

As

       Begin Tran

             Delete AdminMemos

             Where Num = @Num

             --RollBack Tran

       Commit Tran

       -- Select * From AdminMemos Where Num = @Num

Go

--[21] 실행: 100번레코드삭제

DeleteAdminMemos 100

Go

--[22] 검색저장프로시저: 저장프로시저보다는동적쿼리문이라고부름.

Create Proc dbo.SearchAdminMemos

       @Content VarChar(50)

As

       Declare @strSql VarChar(255)

       Set @strSql = '

             Select Num, Content

             From AdminMemos

             Where [Content] Like ''%' + @Content + '%''

       '

       Exec(@strSql) -- 동적으로만든SQL문을실행하는명령어

Go

--[23] 동적쿼리문으로검색

SearchAdminMemos '안녕'

Go

SearchAdminMemos '방가'

Go

--[0] 테이블설계

Create Table dbo.Categories

(

    CategoryID Int Identity(1, 1) Not Null Primary Key,  --카테고리번호

    CategoryName VarChar(50),                            --카테고리명

    --

    SuperCategory Int Null,        --부모카테고리번호(확장용)

    Align SmallInt Default(0)      --카테고리보여지는순서(확장용)

)

Go

--[!] 4개SQL문연습

--[1] 입력: Add / Write

Insert Categories Values('컴퓨터', Null, Default)

Insert Into Categories Values('노트북', 1, 1)

Insert Categories(CategoryName, SuperCategory, Align) Values('핸드폰', Null, 2)

Insert Categories Values('신규', 3, 3)

--[1] 입력저장프로시저

Create Procedure dbo.AddCategory

(

       @CategoryName VarChar(50),

       @SuperCategory Int,

       @Align Int

)

As

       Insert Into Categories Values(@CategoryName, @SuperCategory, @Align)

Go

-- 프로시저로입력

Execute AddCategory '냉장고', 3, 1

Go

--[2] 출력저장프로시저

Create Proc dbo.GetCategories

As

       Select * From Categories Order By CategoryID Asc, Align Asc

Go

-- 실행

Exec GetCategories

Go

--[3] 상세저장프로시저

Create Proc dbo.GetCategoryByCategoryID

       @CategoryID Int

As

       Select * From Categories Where CategoryID = @CategoryID

Go

-- 실행

GetCategoryByCategoryID 1

Go

--[4] 수정저장프로시저

Create Procedure dbo.UpdateCategory

(

       @CategoryName VarChar(50),

       @CategoryID Int

)

As

       Update Categories

       Set

             CategoryName = @CategoryName

       Where

             CategoryID = @CategoryID

       Select * From Categories

Go

-- 실행: 1번카테고리명을'콤퓨타'로변경

UpdateCategory '콤퓨타', 1

Go

--[5] 삭제저장프로시저

Create Proc dbo.DeleteCategory

       @CategoryID Int

As

       Begin Tran                       -- 수정/삭제시예외처리

             Delete Categories

             Where CategoryID = @CategoryID

             Select @@RowCount   -- 삭제된데이터의개수: 1

             If @@Error > 0             -- 만약에에러개수가1이상이면, 롤백시켜라...

             Begin

                    RollBack Tran

             End

       Commit Tran         -- 여기까지에러없이왔다면실행완료

Go

Exec DeleteCategory 7

Go

--[6] 검색저장프로시저

-- 카테고리이름이모모모인것을검색?

Alter Proc dbo.FindCategory

       @CategoryName VarChar(50)

As

       Declare @strSql VarChar(500)  -- 검색어= ' + @검색어+ '

       Set @strSql =

             'Select * From Categories Where CategoryName Like ''%' + @CategoryName + '%'''

       Print @strSql

       Exec(@strSql)

Go

FindCategory '노트북'

Go

FindCategory '핸드폰'

Go

Alter Proc dbo.PrintString

       @Message VarChar(50)

As

       Declare @strSql VarChar(255)

       Set @strSql = '''@''' + @Message + '''@'''

       Print @strSql

Go

PrintString '안녕'

--카테고리(상품카테고리) 응용프로그램설계

--[0] 테이블설계

CREATE TABLE dbo.Categories

(

    CategoryID Int IDENTITY(1, 1) Not Null Primary Key,  --카테고리번호

    CategoryName VarChar(50),                            --카테고리명

    --

    SuperCategory Int Null,        --부모카테고리번호(확장용)

    Align SmallInt Default(0)      --카테고리보여지는순서(확장용)

)

Go

--[!] 4개SQL문연습

--[1] 입력: Add / Write

Insert Categories Values('컴퓨터', Null, Default)

Insert Into Categories Values('노트북', 1, 1)

Insert Categories(CategoryName, SuperCategory, Align) Values('핸드폰', Null, 2)

Insert Categories Values('신규', 3, 3)

--[2] 출력: Get / List

Select CategoryName From Categories Order By Align Asc

--[3] 상세: View / Get(-> 1개레코드에대해서상세보기)

Select * From Categories Where CategoryID = 1

--[4] 수정: Modify / Edit

Update Categories

Set

       CategoryName = '콤퓨타'

Where CategoryID = 1

--[5] 삭제: Delete

Delete Categories Where CategoryID = 2

--[6] 검색: Search / Find

Select * From Categories

Where

       CategoryName Like '%퓨%'

       And

       SuperCategory Is Null --널(NULL)값비교(-> 즉, 대분류만검색해라!)

--[7] 뷰(View) 생성: Select문전용

--[a] 대분류데이터를출력하는구문을줄여서출력

Select CategoryID, CategoryName

From Categories

Where SuperCategory Is Null

Go

--[b] 위구문을줄여주는뷰(View) 생성

Create View dbo.GetTopCategory

As

       Select CategoryID, CategoryName

       From Categories

       Where SuperCategory Is Null

Go

--[c] 뷰(가상테이블) 사용: 약간줄어들죠???

Select * From GetTopCategory

Order By CategoryName Asc

Go

--[d] 뷰(가상테이블) 수정: 암호화

sp_helptext GetTopCategory --(생성한)뷰구문보기

Go

--[!] 뷰구문수정: 암호화처리

Alter View dbo.GetTopCategory

With Encryption -- 개체암호화옵션

As

       Select CategoryID, CategoryName

       From Categories

       Where SuperCategory Is Null

Go

sp_helptext GetTopCategory --안보임

Go

--[!] 뷰구문수정: 스키마바인딩적용

Alter View dbo.GetTopCategory

With SchemaBinding -- Categories 테이블(=부모테이블) 변경불가능

As

       Select CategoryID, CategoryName

       From dbo.Categories

       Where SuperCategory Is Null

Go

--[!] 뷰에다가직접데이터입력

Insert Into GetTopCategory(CategoryName) Values('가전')

Go

Select * From Categories

Go

-- With Check Option 사용(하면기본적으로테이블에입력(수정)이되지않는다.)

Alter View dbo.GetTopCategory

As

       Select *

       From dbo.Categories

       Where SuperCategory Is Null -- 대분류만...

       With Check Option --조건절에해당하는데이터만입력/수정가능

Go

-- 에러: Identity값입력X, SuperCategory => Null 입력

Insert Into GetTopCategory Values('오디오', 5, 2) --

Go

-- 기본

Set Identity_Insert Categories On

-- 실행: 조건절이(대분류만가능토록되어있기에)

Insert Into GetTopCategory(CategoryName) Values('오디오') -- 에러난다.

Go

Insert Into GetTopCategory(CategoryID, CategoryName)

Values(7, '오디오') -- 실행됨

Go

Insert Into GetTopCategory(CategoryID, CategoryName, SuperCategory)

Values(8, '오디오', Null) -- 실행됨

Go

--[0] Tempdb에테스트용테이블생성및데이터입력

Use tempdb

Go

--Drop Table Orders

Create Table dbo.Orders

(

       [상품명] VarChar(10),

       [판매개수] Int

)

Go

Insert Orders Values('RADIO' ,5)

Insert Orders Values('TV' ,3)

Insert Orders Values('AUDIO' ,1)

Insert Orders Values('DVD' ,7)

Insert Orders Values('RADIO' ,3)

Insert Orders Values('DVD' ,8)

Insert Orders Values('TV' ,10)

--[1] 집계함수사용검색

Select * From Orders

Select Count([판매개수]) As [총판매횟수] From Orders    -- 7

Select Sum([판매개수]) As [총판매개수] From Orders      -- 37

Select Avg([판매개수]) As [총판매평균] From Orders      -- 5

Select Max([판매개수]) As [최대판매개수] From Orders     -- 10

Select Min([판매개수]) As [최소판매개수] From Orders -- 1

--[2] 아래구문을실행시켜보면다음과같은에러가나타난다.

-- 'Orders.상품명' 열이집계함수에없고Group By 절이없으므로

-- Select 목록에서사용할수없습니다.

Select [상품명], Avg([판매개수]) As [총판매평균] From Orders

--[3] 위와같이집계함수로계산되어지지않고그룹화되어출력시킬필드는반드시

-- Group By절에기록하여야한다.

Select [상품명], Avg([판매개수]) As [총판매평균]

From Orders

Group By [상품명]

--[4] 만약그룹별총판매평균이5이상인것만출력하고자할때에는

-- Having절에조건을추가하여야한다.

Select [상품명], Sum([판매개수]) As [총판매합계], Avg([판매개수]) As[총판매평균]

From Orders

Group By [상품명]

Having Avg([판매개수]) >= 5 -- 판매개수의평균이5이상인것만검색

--[5] 퀴즈: 각주문별상품판매량이5이상이고, 그룹별총판매평균이7이상인것만

-- [상품명]에오름차순정렬해서출력하시오.

Select [상품명], Sum([판매개수]) As [판매합계]

From Orders

-- Join (여기서는생략)

-- On (여기서는생략)

Where [판매개수] >= 5           -- 그룹전 조건   

Group By [상품명]

Having Avg([판매개수]) >= 7     -- 그룹화된 데이터 조건

Order By [상품명] Asc

--[1] 샘플테이블생성

Create Table dbo.Members

(

       Num Int Identity(1,1) Not Null Primary Key, -- 일련번호

       Name VarChar(25) Not Null,                  -- 이름

       Age TinyInt Null,                           -- 나이

       Address VarChar(100) Null                   -- 주소

)

Go

--[2] 예시데이터입력

Insert Into Members(Name, Age, Address)

Values('홍길동', 21, '서울')

Go

Insert Members Values('백두산', 100, '부산')

Go

Insert Members Values('한라산', 30, '부산')

Go

--[3] 데이터조회(출력)

Select * From Members

Go

--[!] 하위쿼리(서브쿼리)

-- 3번인데이터의나이보다큰데이터만출력

       --[a] 3번레코드의나이

Select Age From Members Where Num =3

       --[b] 3번레코드의나이보다큰데이터출력

Select * From Members Where Age > 30

       --[a] 3번레코드의나이

Declare @intAge Int

Select @intAge = Age From Members Where Num = 3

       --[b] 3번레코드의나이보다큰데이터출력

Select * From Members Where Age > @intAge

       --[!] 최종: 서브쿼리는기본적으로스칼라값(단일값)이반환되어져야함

Select * From Members

Where Age > (Select Age From Members Where Num = 3)

Go

-- 나이가평균이하인사람출력

Select * From Members Where Age <= (Select Avg(Age) From Members)

Go

-- 주소가'부산'인사람의나이보다크거나같은데이터출력

Select * From Members

Where Age >= (Select Age From Members Where Address = '부산')

Go -- 에러

-- Scalar 값이아닌다중레코드값은In 구문사용

Select * From Members

Where Age In (Select Age From Members Where Address = '부산')

Go

-- 위구문을개선: Or(Any, Some)연산, And(All)연산

Select * From Members

Where

       Age >= Any(Select Age From Members Where Address = '부산')

Go -- 30, 100

Select * From Members

Where

       Age >= All(Select Age From Members Where Address = '부산')

Go -- 100

--[6] 테이블삭제

Drop Table dbo.Members

Go

-- 조인(Join) : Left 조인

-- 카테고리테이블설계

Create Table dbo.Categories

(

       CategoryID Int Identity(1, 1) Not Null Primary Key, -- 카테고리번호

       CategoryName VarChar(25) Not Null  -- 카테고리명

)

Go

Insert Categories Values('가전')  -- 1번카테고리

Go

Insert Categories Values('컴퓨터') -- 2번카테고리

Go

Insert Categories Values('서적')

Go

--상품테이블설계

Create Table dbo.Products

(

       ProductID Int Identity(1, 1) Not Null Primary Key,   -- 상품고유번호

       ModelName VarChar(25) Not Null,        -- 상품명

       SellPrice Int Null,                    -- 가격

       CategoryID Int Null                    -- 카테고리(1,2,3)

)

Go

--[!] 외래키따로지정: 외부에서

Alter Table dbo.Products

Add Foreign Key(CategoryID) References Categories(CategoryID)

Go

-- 가전, 냉장고, 100

Insert Products Values('냉장고', 100, 1)

Go

-- 컴퓨터, 노트북, 200

Insert Products Values('노트북', 200, 2)

Go

Insert Products Values('데스크톱', 150, 2)

Go

-- 상품평(코멘트) 테이블설계

Create Table dbo.Reviews

(

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

       ProductID Int References Products(ProductID), -- 외래키

       Comment VarChar(3850)                         -- 내용

)

Go

Insert Into Reviews Values(1, '냉장실에서얼음이얼어요...')

Insert Into Reviews Values(2, '브랜드가모닝글로리')

Select * From Categories

Select * From Products

-- 상품리스트출력: 카테고리명, 상품명, 판매가

--[1] SQL Server 전용문법

Select CategoryName, ModelName, SellPrice

From Categories, Products

Where Categories.CategoryID = Products.CategoryID

Go

--[2] ANSI-SQL 공통문법

Select CategoryName, ModelName, SellPrice

From Categories Join Products

       On Categories.CategoryID = Products.CategoryID

Go

--[3] 상세표시: Join은기본적으로Inner Join이다.

Select

       Categories.CategoryName,

       Products.ModelName,

       Products.SellPrice

From Categories Inner Join Products

       On Categories.CategoryID = Products.CategoryID

Go

--[4] 축약표시-> SQL Server전용문법이다.

Select c.CategoryName, p.ModelName, p.SellPrice