.Net Project & 재태크 재무 관리

--------------------

--실습-1

--------------------

/*

       데이터베이스이름, 스키마이름, 테이블이름, 필드이름이 정확히기억나지 않거나,   
           각이름의철자가확실하지않을때찾아서조회하는방법

       을실습하도록하자.  
           지금조회하고자하는내용이AdventureWorks.HumanResources.Department
       테이블의Name 및GroupName 열이라고가정

*/

--[1] 쿼리창을연다

--[2] 현 인스턴스에 어떤 데이터베이스가있는지조회한다.

       Exec sp_help; --존재하고있는데이터베이스정보조회

       --다음과같은방식으로저장프로시저정보를조회가능

       Exec AddBlogOption 5 --매개변수를통하여값이전달되면저장된프로시저에의해값이저장완료된다.   

--[3] 데이터베이스지정

             Use Test --Test 데이터베이스를사용하겠다는것을의미한다.

--[4] 테이블정보를조회

             Exec sp_tables @table_type = "'TABLE'"

--[5] 테이블의열에무엇이있는지확인해보자

             Exec sp_columns

                    @table_name = 'TT_BlogOption',

                    @table_owner = 'dbo';                  

--[6] 데이터조회

             Select UserNum As[사용자번호], BlogTitle As[블로그제목], ProfileImage As [프로필이미지], PostListView AS [리스트뷰] From dbo.TT_BlogOption

--------------------                         

--실습-2

--------------------

--[1] 기존의쿼리창을닫고, 새로운쿼리창을연다, 
          이번에입력할쿼리는앞으로다른장에서도거의비슷하게많이사용되며

      입력한쿼리를따로저장해놓으면나중에편리할것이다.

--[2] 데이터베이스생성

             Use master

             Go

             Create Database sqlDB

             Go          

--[3] 테이블생성

Use Test

Create Table userTb1 --회원테이블

(

       userID char(8) Not Null Primary Key,    --사용자아이디

       name nvarchar(10) Not Null,         --이름

       birthYear int Not Null,             --출생년도

       addr nchar(2) Not Null,             --지역

       mobile1 char(3),                    --휴대폰국번(011,016,017,019,010 등)

       mobile2 char(8),                    -- 휴대폰의나머지전화번호('-' 제외)

       height smallint,                    --키

       mDate date                          --회원가입일 

)

Go

Select *From dbo.buytb1 --미리생성해둔테이블을사용할것이다

--[3-1] userTb1 회원테이블에데이터삽입

Insert Into userTb1 Values ('KYW','김용원','1980','서울','011','1234567','180','2010-02-13')

Insert Into userTb1 Values ('HBW','황병욱','1980','서울','010','1238888','183','2010-02-13')

Insert Into userTb1 Values ('YYW','윤원용','1980','서울','019','1239875','178','2010-02-13')

Insert Into userTb1 Values ('KKK','김용먼','1980','서울','011','1234567','180','2010-02-13')

Insert Into userTb1 Values ('CCC','윤원먼','1980','경기','019','1239875','178','2010-02-13')

--[3-2] 각각의데이터를확인해보자

Select *From userTb1

Select *From buytb1

--특정조건의데이터만조회하는<Select ~ From ~ Where>

--기본적인Where : 조회할때특정한조건을줘서, 조회결과로원하는데잍터만보고싶을때사용가능하다

-- Select [필드이름] From [테이블이름] Where [조건식]

--다음의조건식을통하여'김용원' 출력

       Select *From userTb1 Where name = '김용원'

--관계연산자사용: 1970년이후에출생했고, 신장이180 이상인사람의아이디와이름을조회하자

       Select userID, name From userTb1 Where birthYear > 1970 And height >= 180

       Select userID, name From userTb1 Where birthYear >= Any(Select birthYear From userTb1 Where birthYear > 1970 And height >= 180)

--Between And, In('',''), Like 조건절사용

       --키가180 ~ 183 이상인사람을조회하자

       Select *From userTb1 Where height Between 180 And 183 or height > 170   

       --키가정확인179 또는180 또는182인사람의정보를확인하자

     Select Top 1 *From user1 Where height > 179 or height > 180 or height > 182

       --키가정확인179 또는180 또는182인사람의정보를확인하자(IN 조건절사용)

             Select  *From userTb1 Where name like '김%' or name like '황%'

             Select *From userTb1 Where height In(178,180,182)

--위내용과같은조건은'김'씨이면그뒤에무엇이든(%) 허용한다는의미이다.

--즉, '김'이제일앞글자인것들을추출한다. 한글자와매치하기위해서는'_'를사용하며, 다음은맨앞글자가한글자이고,

--그다음이'용원'인사람을조회하여준다

--Any, All, Some, 하위쿼리

--하위쿼리(서브쿼리:SubQuery) : 간단히말하면쿼리문안에또쿼리문잉들어있는것을뜻한다.

                                                     -- 예를들어유재석보다키가큰사람의이름과그키를출력하려면Where 조건에유재석의키를직접적용

             --Ex) [김용원] 보다키가크거나같은사람을출력하자할경우사용

             Select name, height From userTb1 Where height >= (Select height From userTb1 Where name = '김용원')

             --Ex) 키같은사람중에'019' 번호를가진사람출력하고할경우사용

             Select name, height From userTb1 Where height = Any(Select height From userTb1 Where mobile1 = '019' )

             order by name Desc

/*

order by절: 결과물에대해영향을미치지는않지만결과가출력되는순서를조절하는구문이며,

                           원하는순서대로결과를정렬하여출력가능하며, 가입한순서대로회원들을출력가능하다.

                           ASc : 오름차순// DESC : 내림차순정렬

*/

                           Select *From userTb1 order by name Desc

                           --ex-1) 주소가서울이면서동시에국번이'011'인회원과키가같은회원의정보를출력

                           Select *From userTb1 Where addr = '서울' And mobile1 = Any(Select mobile1 From userTb1 Where mobile1 = '011')

/*

Distinct, Top(N), Tablesample 절: 회원테이블에서회원들의거주지역이몇군데인지출력해보자

*/

--현재가지고있는전체주소목록을보여주고있다.

Select addr From userTb1 order by addr

--Distinct 중복되는데이터는하나만표시하여출력가능

Select distinct addr From userTb1 order by addr

--회원의전화번호국번의종류가몇개인지출력하되, 정렬해서출력하는SQL 작성

Select Distinct count(mobile1) As [국번갯수], mobile1 As [국번],
       mobile2 AS [전화번호] From userTb1

       Group by mobile2, mobile1

       order by COUNT(mobile1) Desc

Go

--테이블에서물품별소합및색상별소합을모두보고싶다면CUBE를사용가능

Create Table cubeTb2

(

       prodName NCHar(3),

       corlor Nchar(2),

       amount Int

)

Go    

-- 데이터 사입

Insert Into cubeTb2 Values('컴퓨터','검정','11')

Insert Into cubeTb2 Values('컴퓨터','파랑','22')

Insert Into cubeTb2 Values('모니터','검정','33')

Insert Into cubeTb2 Values('모니터','파랑','44')                   

Select Top 1 *From cubeTb2

--CUBE 사용

Select prodName, corlor, Sum(amount) As [수량합계] From cubeTb2                              

       Group by CUBE(prodName, corlor) 

--Roll Up

Select prodName, corlor, Sum(amount) As [수량합계] From cubeTb2                              

       Group by Rollup(prodName, corlor)      

--With절과CTE

/*

       CTE를표현하기위한구문이다/ 기존의뷰, 파생테이블,
           임시테이블등으로사용되던것을대신할수있으며, 더간결한식으로보이는장점이

       있으며, 더간결한식으로보이는장점이존재하고있습니다.

       비재귀적CTE

       말그대로재귀적이지않은CTE이며, 단순한형태,
           복잡한쿼리문장을단순화시키는데사용하기에적합합니다.

       우선비재귀적CTE를살펴보도록하겠습니다.

*/

Select *From user1

Select *From buytb1

Select userid AS [사용자], SUM(price*amount) AS [구매합계],  COUNT(*) AS [인원수] From buytb1

       Group by userid

       Having COUNT(*) >= 5

       order by userid Desc

------------------------------- 

--재귀적CTE를이용한구현예제

-------------------------------

--[1] 우선위의테이블정의하고설계하자

Create Table empTb1

(

       emp nchar(3),

       manager nchar(3),

       department nchar(3)

)

Go    

Insert Into empTb1 Values('나사장',Null,Null)

Insert Into empTb1 Values('김재무','나사장','재무부')

Select *From empTb1

--[2]CTE 구문에따라서쿼리문작성

With empCTE(empName, mgrName, dept, level)

       AS

       (

             Select emp, manager, department, 0

             From empTb1

             Where manager Is Null --상관이없으면바로사장

             Union All

             Select AA.emp, AA.manager, AA.department, BB.level+1

             From empTb1 As AA Inner Join empCTE AS BB

                    On AA.manager = BB.empName

       )

       Select *From empCTE order by dept, level            

/*

       T-Sql 분류

       DML : 데이터를조작(선택, 삽입, 수정, 삭제)하는데사용하는언어

             DML 구문이사용되는대상은테이블의행이다, 그러므로 
             DML사용하기위해서는꼭이전에테이블이정의되어있어야한다

       DDL : 데이터베이스, 테이블, 뷰, 인덱스등의데이터베이스개체를생성/삭제/
                     변경하는역활을한다.

             DDL 종류로는Create, Drop, Alter                                               존재하며DDL은트랜잭션을발생시키지않는다는것을명심하여야할것이다.                

       DCL : 사용자에게어떤권한을부여하거나빼앗을때주로사용하는구문으로, Grant/Revoke/Deny 등이해당된다.         

*/

/*

Create/alter/drop/Insert/Select/Update/Delete 문연습

*/

--[1] 테이블생성

Create Table dbo.Sample

(

       Num Int Identity(1,1) Primary Key,

       Name VarChar(25),

       Age Int Default 21,

)

Go

Create Table dbo.Sample1

(

       Num Int Identity(1,1) Primary Key,

       Name VarChar(25),

       Age Int Default 21,

)

Go

--[2] 테이블구조수정

--Alter Table dbo.Sample

--(

--)

--Go

--[3] 테이블구조삭제

Drop Table dbo.Sample

Go

--[4] 데이터입력

Insert Into Sample Values('홍길동','21')

Go

--[5] 데이터출력

Select *From Sample

Go

--[6] 데이터수정

Update Sample

       Set Name = '김용원', Age = '20'

       Where Num = 1

Go

--[7] 데이터삭제

Delete Sample

Where Name like '%용%'

Go    

--[8] newSample 생성(Sample테이블복사)

Select *Into newSample From Sample

Select *From Sample1 Where Num > 10

Select *From Sample1 Where Name like '%홍%'   

Select *From Sample1 Where Num In(2,4,6)

Select *From Sample1 Where (Num % 2 = 0)

Select Avg(Age) From Sample1

--[9] Begin Tran ~ Commit Tran

Begin Tran

       update Sample1

       Set Name = '백수산' , Age = 100

       Where Num = 2

--RollBack Tran

Commit Tran

---------

--집계함수

----------

--샘플데이블

Create Table Score

(

       Num Int Identity(1,1) Not Null Primary Key,

       Kor Int Not Null,

       Eng Int Not Null,

)

Go

--Input 데이터 삽입

Insert Into Score Values (100,90)

Insert Into Score Values (80,90)

Insert Into Score Values (70,90)

Insert Into Score Values (95,90)

Insert Into Score Values (40,90)

--전체출력

Select *From Score

--국어점수총점/ 평균/카운터

Select SUM(Kor) From Score Where Kor % 2 = 0

Select COUNT(Kor) From Score

Select AVG(Kor) From Score Where Kor % 2 = 0

--

Select SUM(Kor), AVG(Eng), COUNT(*) From Score

Select MAX(Kor) From Score --최대값

Select MIN(Kor) From Score --최소값     

--문자열관련함수

Declare @s VarChar(20)

       Set @s = ' Abc Def Red Cba '

---

--전체출력

Select @s AS [전체출력]           

--문자열의길이

Select Len(@s) As [문자열길이] --뒤에오는공백제외

--선택공백제거

Select LTRIM(@s) --선행공백제거

Select RTRIM(@s) --후행(오른쪽) 공백제거

--모두대문자로표시

Select UPPER(@s)

--모두소문자로표시

Select LOWER(@s)

--왼쪽에서6자출력: ABc D

Select LEFT(@s, 6)

--오른쪽에서6자출력: d Cba

Select RIGHT(@s, 6)

--거꾸로출력

Select REVERSE(@s)

--특정문자열찾아서있으면변환(치환)

Select REPLACE(@s, 'Abc','초콜릿')

--특정문자열반복

Select REPLICATE('안녕',10)

--공백(Spage)여러개출력

Select '[' + SPACE(10) + ']'

--정수형을문자열로변환

Select STR(12345) + '6789'

--문자열검색: 어디부터몇자검색

Select SUBSTRING(@s, 6,3) --Def

--특정문자열의위치값검색: 검색할문자의시퀀스포함

Select CHARINDEX('Def', @s) --6번째위치에존재한다.

------------------

--사용자정의함수

------------------

--Hap (3,5) -> 8과같이합을구하는간단한함수

Create Function Hap

(

       @a Int,

       @b Int

)

Returns Int --변환값에대한데이터인식

As

       Begin

             --[1] Input

             Declare @result Int

             --[2] Process

             Set @result = @a + @b

             --[3] Output

             Return @result

       End

Go

--함수호출

Select dbo.Hap(3,5) As [합]

Go

--이미 있는기능모방

Select POWER(2,10)

Create Function MyPower(@n Int, @c Int)

Returns Int

As

       Begin

             Declare @result Int

             Set @result = 1

             Declare @i Int

             Set @i = 1

             While @i <= @c

             Begin

                    Set @result = @result * @n

                    Set @i = @i + 1

             End

             Return @result     

       End

Go

Select dbo.MyPower(2,20) --1024

Select ABS(-10)

--함수응용

Create Function Even(@n Int)

Returns Int

As

       Begin

             Declare @sum Int          

             Declare @i Int

                    Set @sum = 0

                    Set @i = 1

                           While @i <= @n

                           Begin

                                 if @i % 2 = 0

                                 Begin

                                        Set @sum = @sum + @i

                                 End

                                 Set @i = @i + 1

                           End

                           Return @sum

       End

Go

Select Even(10)

Go

       조인Join : 두개이상의테이블을서로묶어서하나의결과집합으로만들어내는것을의미

                 분리된테이블은서로관계를맺고있으며, 그 중
                           에서간단하지만가장많이사용되는보편적인관계

                 를맺고있습니다.

       Inner Join : 조인중에가장일반적으로많이사용

       Outer Join : 외부조인이라고하며, 조인조건에만족되지안흔행까지포함시키고있으며,

                           사실현실적으로그리자주사용되지는않지만,
                                종종유용하게사용되는방식이므로꼭알아둘필요는있다.

       Cross Join : 상호조인이라고하며, 
                                한쪽테이블의모든행들과다른쪽테이블의모든행을조인시키는기능을한다.

                    회원테이블의처리행이구매테이블의모든행과조인되고,
                                그것을회원테이블의모든행이반복하는것이다.

                    (결과개수는두테이블개수를곱한개수가된다.)

      Self Join : 자체조인라고하며,
                             별도의구문이있는것이아니라자기자신과자기자신이조인한다는의미를포함하고있다.

*/

Select *From user1

Select *From buytb1

Select name, addr, height From user1

Union All

Select  prodname, groupname, price
                 From buytb1     

 Create Table tblPeople
(
    Name NVarChar(10) Primary Key,
    Age Int Not Null,
    Male Bit Not Null
);

Insert Into tblPeople Values('정우성',36,1);
Insert Into tblPeople Values('배용준',63,1);
Insert Into tblPeople Values('고쇼용',12,1);
Insert Into tblPeople Values('김태희',33,1);

Select *From tblPeople

Create Table tblSale
(
   OrderNo Int Identity(1,1) Primary Key,
   Customer NVarChar(10) Not Null References tblPeople(Name),
   Item NVarChar(20) Not Null,
   oDate DateTime Not Null
);

Insert Into tblSale(Customer,Item,oDate) Values
                                                  ('정우성','면도기','2008/03/01');
Insert Into tblSale(Customer,Item,oDate) Values
                                                  ('김태희','핸드폰','2008/03/03');
Insert Into tblSale(Customer,Item,oDate) Values
                                                  ('김태희','휘발류','2008/03/04');
Insert Into tblSale(Customer,Item,oDate) Values
                                                  ('고쇼용','화장품','2008/03/05');

Select *From tblSale

 --[14]쇼핑카트 비우기
Create Procedure ShoppingCartEmpty
(
    @CartID VarChar(50) --현재 접속자
)
As
   Delete From ShoppingCart
 Where
   CartID = @CartID
Go
-------------------------------------------------------------------
--[15] 퀴즈 (하루가 지난) 쇼핑 카트 ?? : 관리자 모드에서 사용
-------------------------------------------------------------------
Delete From ShoppingCart
Where
 DATEDIFF(dd,DateCreated, GetDate()) > 1 --시간차가 1일 이상인 조건
Go

Create Proc ShoppingCartRemoveAbandoned
As
 Delete From ShoppingCart
 Where
  DATEDIFF(DD, DateCreated, GETDATE()) > 1
Go

 --[16] 고객등록 : Register.aspx에서 사용
Create Procedure CustomerAdd
(
     @CustomerName VarChar(50),
     @Phone1 VarChar(4),
     @Phone2 VarChar(4),
     @Phone3 VarChar(4),
     @Mobile1 VarChar(4),
     @Mobile2 VarChar(4),
     @Mobile3 VarChar(4),
     @Zip VarChar(7),
     @Address VarChar(100),
     @AddressDetail VarChar(100),
     @Ssn1 VarChar(6),
     @Ssn2 VarChar(7),
     @EmailAddress VarChar(50),
    @MemberDivision Int,
 --위에 매개 변수는 Customers관련, 아래 매개변수는 Membership 관련
    @UserID VarChar(25),
    @Password VarChar(100),
    @BirthYear VarChar(4),
    @BirthMonth VarChar(2),
    @BirthDay VarChar(2),
    @BirthStatus VarChar(2),
    @Gender Int,
    @Job VarChar(20),
    @Wedding Int,
    @Hobby VarChar(100),
    @Homepage VarChar(100),
    @Intro VarChar(400),
    @Mailing Int,
    @Mileage Int, 
    @CustomerID Int Output
)   
As
 Begin Tran CustomerAdd --별칭을 붙여서 트랜잭션 걸기
 --첫번째 인서트문...
 Insert Into Customers (
      CustomerName, Phone1, Phone2, Phone3, Mobile1, Mobile2,
      Mobile3, zip, Address, AddressDetail, Ssn1, Ssn2,
      EmailAddress, MemberDivision
  )
  Values (
     @CustomerName, @Phone1, @Phone2, @Phone3, @Mobile1,
     @Mobile2,@Mobile3, @zip, @Address, @AddressDetail, @Ssn1,  
     @Ssn2,@EmailAddress, @MemberDivision
  )
  --바로 위에서 실행된 쿼리문의 Identity 값 반환 
  Select @CustomerID = @@Identity 

-- 위의 내용과 같은 방식 (이와 같은 방식으로 사용 가능)
-- Select @CustomerID = MAX(CustomerID) From Customers
----------------------------------------------------------------------
 Insert Into MemberShip
 Values (
  @CustomerID, @UserID, @Password, @BirthYear, @BirthMonth, @BirthDay,
  @BirthStatus, @Gender, @Job, @Wedding,
  @Hobby, @Homepage, @Intro, @Mailing,
  0, GETDATE(), @Mileage, GETDATE()
 ) 
 Select @CustomerID
 
 If @@ERROR > 0 
--만약 에러가 발생했다면 2개 인서트 모두 리턴
         RollBack Tran CustomersAdd
    Commit Tran CustomerAdd   
Go