-- 형식변환관련함수

--[0] 에러발생

Select 1234 + '안녕'

--[1] 정수형-> 문자열

Select '1234' + '안녕'

--[2] 정수형-> 문자열: Str()

Select Str(1234) + '안녕'

--[3] Cast(As)

Select Cast(1234 As VarChar) + '안녕'

Select Cast(1234 As VarChar(10)) + '안녕'

--[4] Convert(,)

Select Convert(VarChar, 1234) + '안녕'

Select Convert(VarChar(10), 1234) + '안녕'

/*

형변환함수는 Cast와Convert 중 아무거나써도된다.

*/

-- 한줄메모장

-- 테이블구조만들기

Create Table dbo.Memos

(

       Num Int Identity(1, 1) Primary Key,

       Name VarChar(25) Not Null,

       Email VarChar (50) Null,

       Title VarChar(150) Not Null,

       PostDate SmallDateTime Default(GetDate()),

       PostIP VarChar(15) Null

)

Go

-- 테이블구조변경

Alter Table Memos

(

       -- SSMS 사용(SQL Server Management Studio)

)

Go

-- 테이블삭제

--Drop Table Memos

Go

-- 입력

Insert Into Memos(Name, Email, Title, PostDate, PostIP)

Values('홍길동, 'h@h.com', '안녕', Default', '127.0.0.1')

Go

-- 검색

Select * From Memos Where 1 = 1 Order By Num Desc

Go

-- 업데이트

Update Memos Set Name = '백두산' Where Num > 0

Go

-- 삭제

Delete Memos Where 1 <> 1

Go

-- T-SQL문법

-- 1부터100까지3의배수또는4의배수의합을구하는프로그램

Declare @sum Int

Declare @i Int

Set @sum = 0

Set @i = 1

While @i <= 100

Begin

       if @i % 3  = 0 Or @i % 4 = 0

       Begin

             Set @sum = @sum + @i

       End

       Set @i = @i + 1

End

Select @sum

Print @sum

Go

-- 문자열관련함수

Declare @s VarChar(20)

Set @s = ' Abc Def Fed Cba '

-- 전체출력

Select @s

-- 문자열의길이

Select Len(@s) -- 뒤에오는공백제외

-- 공백제거

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

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

Select Upper(@s) -- 모두대문자로표시

Select Lower(@s) -- 모두소문자로표시

Select Left(@s, 6) -- 왼쪽에서6자출력: Abc D

Select Right(@s, 6) -- 오른쪽에서6자출력: d Cba

-- 거꾸로출력

Select Reverse(@s)

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

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

-- 특정문자열반복

Select Replicate('안녕', 10)

-- 공백(Space)을여러개출력

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

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

Select Str(12345) + '6789'

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

Select SubString(@s, 6, 3)

-- 특정문자열의위치값검색

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

-- 퀴즈) 아래와 같이 출력되도록 하려면???

-- 파일명: test

-- 확장자: gif

Declare @dir VarChar(255)

Set @dir = 'C:\Home\test.gif'

Select '[1 ]' + @dir  -- 전체출력

Declare @fullname VarChar(255)

Set @fullname = @dir

Set @fullname = Reverse(@dir)

Select '[2 ]' + @fullname  -- 거꾸로출력: fig.tset\emoH\:C

Set @fullname = Left(@fullname, CharIndex('\', @fullname) - 1)

Select '[3 ]' + @fullname

Set @fullname = Reverse(@fullname) -- 전체파일명

Declare @name VarChar(255)

Declare @ext VarChar(255)

Set @name = Left(@fullname, CharIndex('.', @fullname) - 1)  -- .(점) 앞에까지Left

Select '[4] ' + @name

Set @ext = Right(@fullname, Len(@fullname) - CharIndex('.', @fullname))  -- 전체길이.까지길이

Select '[5] ' + @ext

-- 수학관련함수

-- 절대값

Select Abs(-10) -- Abs() 함수: 절대값

Go

-- PI

Select PI() -- 3.14 : 파이값

Go

-- 배승

Select Power(2, 10) -- 2의10제곱근

Go

Select Power(2, 20)

Go

-- 반올림

Select ROUND(3.1415, 3) -- 반올림: 3.1415를소수점3째자리까지(반올림했을때)

Go

-- 절삭, 절하

Select Ceiling(-123.56) -- 가장가까운큰정수값

Go

Select Ceiling(123.56) -- 가장가까운큰정수값

Go

-- 랜덤

Select Rand() -- 랜덤값: 임의의수: 0~1까지임의의float 값을반환

Go

-- 집계함수

-- 샘플테이블

Create Table dbo.Score

(

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

       Kor Int Not Null,                       -- 국어점수

       Eng Int Null                            -- 영어점수

)

Go

-- 샘플데이터입력

Insert Score Values(100, 90)

Insert Score Values(80, 75)

Insert Score Values(85, 90)

Insert Score Values(85, NULL)

-- 전체출력

Select * From Score

Go

-- 국어점수짝수점수의총점/평균/카운트

Select SUM(Kor) From Score Where Kor % 2 = 0  -- Sum() : 합계

Select Count(Kor) From Score Where Kor % 2 = 0 -- Count() : 건수

Select Avg(Kor) From Score Where Kor % 2 = 0  -- Avg(): 평균

Select Max(Kor) From Score Where Kor % 2 = 0  -- Max() : 최대값

Select Min(Kor) From Score Where Kor % 2 = 0  -- Min : 최소값

--

Select Count(*) From Score       -- 4

Select Count(Kor) From Score     -- 4

Select Count(Eng) From Score     -- 3 : NULL 값은제외해서카운트

--[!] 테이블제약조건

--[0] tempdb 사용

Use TempDB

Go

--[1] 실습용테이블생성: 간단한줄공지사항(뉴스) 게시판

-- 글번호, 카테고리, 제목, 링크, 타겟, 작성일

Create Table Categories

(

       CategoryID           --[!] 카테고리일련번호

             Int            --[0] 데이터형식

             Identity(1, 1) --[1] 자동증가값: 1부터1씩증가

             Primary Key,   --[2] 기본키(고유한식별자(Identifier)) : 레코드구분하는유일한값

                            --    기본키는NOT NULL 포함, UNIQUE도포함

       CategoryName        --[!] 카테고리이름

             VarChar(25)

             Not Null --[3] 널값제약: NOT NULL : 반드시값입력, NULL(널허용)

)

Go

Create Table dbo.FrontBoards

(

       ItemID                     --[!] 일련번호

             Int Identity(1, 1) Primary Key,

       Title

             VarChar(150) Not Null

             Unique,          --[4] 유일키제약: 테이블에서유일한값(중복불가)

       Url VarChar(255)

             Null,               --[5] 널제약: NULL : 값을입력하지않아도무관

       [Target] VarChar(10) Null 

             Check               --[6] 체크제약: 주어진데이터만입력허용

             (

                    Target In('_top', '_blank', '_parent', '_self')

             ),

       CreatedDate DateTime

             Default(GetDate()),        --[7] 기본값제약: 레코드생성시기본값부여

       CategoryID Int

             References Categories(CategoryID)       --[8] 외래키: 다른테이블의기본키와동일한값

)           

Go

--[1] 예시데이터입력: 자동증가값체크

Insert Into Categories Values('공지');

insert Categories Values('뉴스');

Select * From Categories Order By CategoryID Asc

--[2] 기본값(Default) 제약조건

Insert Into FrontBoards

Values ('닷넷코리아', 'http://www.dotnetkorea.com', '_blank', Default, 1);

Insert Into FrontBoards(Title, Url, Target, CategoryID)

Values('비주얼아카데미', 'http://www.VisualAcademy.com', '_self', 2);

Select * From FrontBoards

--[3] 널값체크

--Insert Into FrontBoards(Url) Values('http://www.JavaCampus.com');

--[4] 유일키제약조건확인

--Insert Into FrontBoards

--Values ('닷넷코리아', 'http://www.dotnetkorea.com', '_blank', Default, 1);

--[5] 체크제약조건

--Insert Into FrontBoards

--Values ('자바캠퍼스', 'http://www.JavaCampus.com', '_main', Default, 1);

--[6] 외래키(Foreign Key)

--Insert Into FrontBoards

--Values ('자바캠퍼스', 'http://www.JavaCampus.com', '_blank', Default, 3);

-- 1부터100까지3의배수또는4의배수의합을구하는프로그램

--[1] Input

Declare @sum Int

Declare @i Int

Set @sum = 0

--[2] Process

Set @i = 1                                    --[1] 초기식

While @i <= 100

Begin

       if @i % 3 = 0 Or @i % 4 = 0            --[3] 조건식

       Begin

             Set @sum = @sum + @i             --[4] 실행문

       End

       Set @i = @i + 1                        --[2] 증감식

       if @i > 100                            --[!] 멈춤/계속

             Break

       Else

             Continue    

End

--[3] Output

Select @sum

print @sum

use tempdb

go

declare @num int

set @num = 10

-- 조건처리

if @num % 2 = 0

       Select '짝수'

else

       Select '홀수'

go

Declare @Data Int

Set @Data = 10

If @Data % 2 <> 0

Begin

       Select '홀수'

       Print '홀수'

End

Else

Begin

       Select '짝수'

       Print '짝수'

End

--[0] 산술연산자

Select 3 + 4

Select 3 - 4

--[1] %연산자: 나머지연산자라고도함

Select 3 % 4 -- 3 나누기4를했을경우. 몫: 0, 나머지: 3, 이때나머지값반환

--[2] 문자열연결연산: '+' 연산자를사용

Select '안녕' + '하세요' --안녕하세요

--[3] 비교

-- >, >=, <, <=

Select * From Memos Where 3 > 4 -- 조건이거짓

Go

-- 같은지(=), 다른지(<>)

Select * From Memos Where 3 = 4 -- 조건이거짓

Go

Select * From Memos Where 3 <> 4 -- 조건이참

Go

-- 기교성코드(?) : 무조건실행: SQL 인젝션(해킹)

Select * From Memos Where 1 = 1

Go

--[4] 논리

-- Or(||) : 하나라도참이면, 참

Select * From Memos Where (3 = 4) Or (3 <> 4) -- 거짓Or 참-> 참

Go

-- And(&&) : 둘다참일때에만, 참

Select * From Memos Where (3 = 4) And (3 <> 4) -- 거짓And 참-> 거짓

Go

-- Not : 'Not'단어를그대로사용한다.

--[5] Null 값체크

--[A] 테이블생성

Create Table NullTest

(

       Name VarChar(25) Not Null,

       Age Int Null

)

Go

--[B] 데이터입력

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

Insert Into NullTest Values('백두산', NULL)

Insert Into NullTest(Name) Values('임꺽정')

--[C] 조건출력

-- 나이가있는사람출력: Is Not Null, <> 사용불가

Select * From NullTest Where Age Is Not Null

-- 나이가기록되지않은데이터출력, = 사용불가

Select * From NullTest Where Age Is Null

-- 변수(Variable) : 변수선언부터변수출력까지블럭씌우고실행(F5)

-- 변수선언

Declare @Num Int

-- 변수초기화

Set @Num = 1234

-- 변수사용

Set @Num = @Num * 10

-- 변수출력

Select @Num

-- 샘플테이블생성및변수를사용해서데이터입력

Create Table Products

(

       ModelName VarChar(100) Not Null,

       UnitPrice Int Not Null

)

Declare @ModelName VarChar(100)

Declare @UnitPrice Int

Set @ModelName = '쉽게배우는SQL Server'

Set @UnitPrice = 20000

Insert Into Products Values(@ModelName, @UnitPrice)

Select * From Products

-- SQL 문장을변수에담아놓고, 동적으로실행(동적쿼리문)

Declare @sql VarChar(255)

Set @sql = 'Select * From Products'

Exec(@sql)

/*

EXECUTE(Transact-SQL): Transact-SQL 일괄처리내에서또는시스템저장프로시저, 사용자정의저장프로시저, 스칼라반환사용자정의함수또는확장 저장프로시저와같은모듈에서문자열이나명령문자열을실행합니다.

*/