| Contents at a Glance Chapter 1 Select: Single Table Chapter 2 Where Chapter 3 Order By Chapter 4 Select: Multiple Tables Chapter 5 Aggregates Chapter 6 Select: New Tables Chapter 7 Except/Intersect/Union Chapter 8 Group By Chapter 9 System Functions Chapter 10 Subqueries Chapter 11 Delete/Insert/Update Chapter 12 Views/User-Defined Functions Chapter 13 Stored Procedures/Programming Chapter 14 Data Definition Language (DDL) and Data Control Language (DCL) Chapter 15 After You Finish Index |
1. 사용할 데이터베이스 다운로드
- 마이크로소프트에서 제공하는 테스트용 DB 를 다운로드 받는다.
- 구글에서 adventureworksDW2008 database download 검색
2. 다운로드 받은 백업 DB 를 SSMS 에서 설치
------------------------------------------------------
Chapter 1 Select: Single Table
Chapter 11 Delete / Insert / Update
| ○ Select Into 기존에 없는 새 테이블을 만들어서 데이터 입력 기존에 있는 테이블에 데이터를 넣으려면 Insert Select |
select *
into Scenario
from DimScenario
-- Scenario 테이블을 만들어서
DimScenario 테이블에 있는 모든 data 를 Scenario 테이블에 복사해 넣는다
-- 새로 만들어지는 Scenario 테이블은 PK, FK 등의 key 속성은 복사하지 않지만
IDENTITY 속성은 복사한다.
delete Scenario
-------------------
select *
into Scenario
from DimScenario
-- [결과] error (데이터는 삭제되었지만 Scenario 라는 테이블이 존재하므로)
| ○ Truncate Table : 테이블은 남기고 데이터만 삭제 Drop Table : 테이블째로 삭제 |
truncate table Scenario
| ○ Insert Into ... Select 1/3 - 기존에 있는 테이블에 데이터 입력 - Identity 칼럼이 있는 경우 주의 |
insert into Scenario
select * from DimScenario
-- 결과
메시지 8101, 수준 16, 상태 1, 줄 15
테이블 'Scenario'에 있는 ID 열의 명시적 값은 열 목록이 사용되고 IDENTITY_INSERT가 ON일 때만 지정할 수 있습니다.
-- 설명
현재 Scenario 는 빈 테이블인데, 이 중 ScenarioKey 칼럼이 IDENTITY 속성.
IDENTITY 속성인 칼럼에 복사를 하려하면 위처럼 error 발생.
| ○ Insert Into ... Select 2/3 - 일부 칼럼만 복사 |
insert into Scenario
select ScenarioName from DimScenario
-- 결과
3 rows affected
-- 설명
존재하고 있는 Scenario 테이블에 DimScenario 테이블로 부터 ScenarioName 칼럼을 복사해 넣는다
| ○ Insert Into ... Select 3/3 - 일부 칼럼만 복사 - source 칼럼과 destination 칼럼 모두 명시 |
insert into Scenario (ScenarioName)
select ScenarioName from DimScenario
-- 설명
존재하고 있는 Scenario 테이블의 ScenarioName 칼럼에
DimScenario 테이블로 부터 ScenarioName 칼럼을 복사해 넣는다
| ○ delete 테이블명 / truncate table 테이블명 차이 - delete : identity 칼럼 번호를 계속 이어간다 - truncate : identity 칼럼 번호를 1 로 리셋 |
-- DELETE / truncate 차이
drop table Scenario
select *
into Scenario
from DimScenario
select * from DimScenario
select * from Scenario
delete Scenario
-- truncate table Scenario -- 이 부분만 바꾸어서 실행
insert into Scenario
select ScenarioName from DimScenario
select * from Scenario
| delete 결과 | truncate 결과 |
 마지막 테이블 ScenarioKey 칼럼 4 부터 시작 |
 마지막 테이블 ScenarioKey 칼럼 1 부터 시작 |
| ○ Delete 를 하고 identity 번호를 다시 1 부터 넣는 방법 - dbcc checkident() |
drop table Scenario
select *
into Scenario
from DimScenario ;
select * from DimScenario; -- 첫번째 결과
select * from Scenario; -- 두번째 결과
delete Scenario;
dbcc checkident(Scenario, reseed, 0);
insert into Scenario
select ScenarioName from DimScenario;
select * from Scenario; -- 세번째 결과
identity 칼럼은 일반적인 insert 문이나 update 문으로는 insert 도, update 도 할 수 없다.
| ○ Identity 칼럼에 특정 숫자를 insert 하자 - set identity_insert Scenario on (off 로 하면 다시 원래대로 못쓰게 함) - 이때, 원하는 칼럼리스트를 넣어주어야 한다. |
set identity_insert Scenario on
--
insert into Scenario -- 실패 (칼럼명이 없다)
values(90,'Forecast 2011')
--
insert into Scenario (ScenarioKey, ScenarioName) -- 성공 (칼럼명이 있다)
values(99,'Forecast 2011')
--
set identity_insert Scenario off -- 다시 원래대로
--
select * from Scenario
| ○ Identity 칼럼은 중간에 번호가 비어도 마지막 번호부터 이어간다. |
select * from [dbo].[Scenario] -- 결과 1
-------------------
insert into Scenario (ScenarioName)
values ('Forecast 2012')
insert into Scenario (ScenarioName)
values ('Forecast 2013')
-------------------
select * from Scenario -- 결과 2
◆◆◆◆◆◆◆◆◆◆◆◆◆◆
◆◆◆◆◆◆◆◆◆◆◆◆◆◆
Chapter 12 Vuews / User-Defined Functions
** 여기서는 table function 은 다루지 않고 scalar function 만 다룬다.
1. Join Select 문을 View 로
-- 테이블 3개를 join 한 select 문
select EnglishProductName, EnglishProductSubcategoryName, EnglishProductCategoryName
from DimProduct as P
inner join DimProductSubcategory as S
on P.ProductSubcategoryKey = S.ProductSubcategoryKey
inner join DimProductCategory as C
on S.ProductCategoryKey = C.ProductCategoryKey
where EnglishProductCategoryName = 'Bikes' or
EnglishProductCategoryName = 'Accessories'
| 1. View 만들기 - Create View 뷰이름 as + select 문 |
-- 위 SQL 문을 VIEW 로 만든다.
create view MyView as -- 이 부분만 새로 들어간다
select EnglishProductName, EnglishProductSubcategoryName, EnglishProductCategoryName
from DimProduct as P
inner join DimProductSubcategory as S
on P.ProductSubcategoryKey = S.ProductSubcategoryKey
inner join DimProductCategory as C
on S.ProductCategoryKey = C.ProductCategoryKey
where EnglishProductCategoryName = 'Bikes' or
EnglishProductCategoryName = 'Accessories'
| 2. 위에서 만들어진 VIEW 를 테이블처럼 사용한다 - SELECT |
select * from MyView
| 3. VIEW 수정 - Alter view 뷰이름 as + select 문 |
-- 위 VIEW 를 수정한다.
alter view MyView as -- Alter VIEW
select EnglishProductName, EnglishProductSubcategoryName, EnglishProductCategoryName
from DimProduct as P
inner join DimProductSubcategory as S
on P.ProductSubcategoryKey = S.ProductSubcategoryKey
inner join DimProductCategory as C
on S.ProductCategoryKey = C.ProductCategoryKey
where EnglishProductCategoryName = 'Clothing' or
EnglishProductCategoryName = 'Components'
--
select * from MyView
| 4. VIEW 삭제 - Drop view 뷰이름 |
drop view MyView
◆◆◆◆◆◆◆◆◆◆◆◆◆◆
◆ User-Defined Function
| 1. 함수 만들기 - create function 함수명(@파라미터 데이터타입) returns 데이터타입 as begin 함수수식 return end |
-- 테스트를 위한 base 쿼리를 만든다.
select EnglishProductName as [Product], round(ListPrice,2) as [Price],
ceiling(ListPrice * 0.15) as [Tax]
from DimProduct
where ListPrice is not null
** 여기서 Tax 구하는 부분을 함수로 만들어본다.
use AdventureWorksDW2008R2
go
create function Tax (@price money)
returns int
as
begin
return ceiling(@price * 0.15)
end
-- 입력 파라미터 의 데이터타입 : money
리턴 파라미터의 데이터타입 : int (그대로 money 써도 되지만 연습을 위해)
| 2. 함수 사용 - select 스키마명.함수(입력파라미터) |
select dbo.Tax(20)
-- select Tax(20) : error
select EnglishProductName as [Product], round(ListPrice,2) as [Price],
dbo.Tax(ListPrice) as Tax
from DimProduct -- 함수 사용
where ListPrice is not null
| 3. 함수 삭제 - Drop function 함수명 |
drop function Tax -- 위 예제에서 사용한 Tax 함수를 삭제한다
| 4. 예제] 정수 3개를 받아서 모두 곱한 값을 리터하는 함수(CalculateVolume) 작성 |
create function CalculateVolume (@x int, @y int, @z int)
returns int
as
begin
return @x * @y * @z
end
--
select dbo.CalculateVolume(12,5,3) as Volume -- 결과 1
--
select dbo.CalculateVolume(12.9,5,3) as Volume -- 결과 2
-- [모두 확인후 함수 삭제]
drop function CalculateVolume
위 결과 1 과 결과 2 는 모두 180 이다.
- 12.9 * 5 * 3 = 193.5
- 입력값 데이터 타입이 모두 int 이므로 소수점 이하는 모두 잘라서 입력된다.
◆◆◆◆◆◆◆◆◆◆◆◆◆◆
◆◆◆◆◆◆◆◆◆◆◆◆◆◆
Chapter 13 Stored Procedure / Programming
| 1. WHILE 문 - END WHILE 은 없다 - Begin ... End 는 가능 |
-- begin ... end 없는 경우
declare @counter int
set @counter = 1
while @counter < 3
set @counter = @counter + 1
print @counter
-- 결과
3
-- 설명 : 아래 두 문장이 한 세트 / print @counter 는 while 에 속하지 않는다.
while @counter < 3
set @counter = @counter + 1
-- begin ... end 있는 경우
declare @counter int
set @counter = 1
print @counter
while @counter < 3
begin
set @counter = @counter + 1
print @counter
end
-- 결과
1
2
3
| 2. RETURN - 프로그램실행을 끝낸다. |
print 'starting'
return
print 'finished'
-- 결과
starting
| 3. If ... Else - Then / End If 없음 프로그램실행을 끝낸다. |
if 1 <> 0
print 'true'
print 'got here'
else
print 'false'-- 결과
error
-- 설명
if 1 <> 0 에는 print 'true' 만 적용된다. 따라서 else 부분이 문제가 된다.
위 프로그램을 실행시키려면 Begin ... End 를 사용한다.
if 1 <> 0
begin
print 'true'
print 'got here'
end
else
print 'false'
-- 결과
-- true
-- got here if 1 <> 0
begin
print 'true'
print 'got here'
end
else
print 'false'
print 'got here too'
/* ----------------------
결과
true
got here
got here too
---------------------- */여기서 if 문은 begin ~ print 'false' 까지.
if 1 <> 0
begin
print 'true'
print 'got here'
end
else
begin
print 'false'
print 'got here too'
end
/* ----------------------
결과
true
got here
---------------------- */
| 4. Case ... When ... End - Then 사용 가능 |
declare @minute tinyint
set @minute = datepart(mi,getdate())
declare @OddEven bit
set @OddEven = @minute %2
select
case @OddEven
when 0 then 'Even'
when 1 then 'Odd'
else 'Unknown'
end as [Minute]
/* ----------------------
결과
Odd (or Even)
---------------------- */
| 5. Select 문에서 변수 사용 |
| nchar(1) 타입으로 maritalstatus 라는 변수를 선언하고 이 변수에 'M' 을 넣고 DimCustomer 테이블에서 MaritalStatus 가 변수 maritalstatus 의 값을 가지는 데이터 select |
declare @maritalstatus nchar(1)
set @maritalstatus = 'M'
select * from DimCustomer
where MaritalStatus = @maritalstatus
| Stored Procedure 로 넘어가기 위한 Base 쿼리 |
select FirstName + ' ' + LastName as [Full Name], MaritalStatus, Gender
from DimCustomer
| 1. Create Proc 프로시저명 as Begin ... End |
-- first stored procedure
create proc GetCustomers as
select FirstName + ' ' + LastName as [Full Name], MaritalStatus, Gender
from DimCustomer
-- better
create proc GetCustomers as
begin
select FirstName + ' ' + LastName as [Full Name], MaritalStatus, Gender
from DimCustomer
end
| 2. Alter Proc 프로시저명 as Begin ... End |
alter proc GetCustomers as
begin
-- [Full Name] 을 Fullname 으로 변경
select FirstName + ' ' + LastName as [Fullname], MaritalStatus, Gender
from DimCustomer
end
| 3. 실행 : exec procedure명 |
alter proc GetCustomers as
begin
select FirstName + ' ' + LastName as [Full Name], MaritalStatus, Gender
from DimCustomer
where MaritalStatus = 'S' and Gender = 'F'
end
--------------------------
-- 실행
exec GetCustomers
| 4. procedure 에 내부 변수 사용 |
-- where 절에 변수 사용
alter proc GetCustomers as
begin
declare @maritalstatus nchar(1) -- 내부 변수
declare @gender nvarchar(1) -- 내부 변수
set @maritalstatus = 'S'
set @gender = 'F'
select FirstName + ' ' + LastName as [Full Name],
MaritalStatus,
Gender
from DimCustomer
where MaritalStatus = @maritalstatus
and Gender = @gender
end
-- 실행
exec GetCustomers
/* --------------------------------
결과 :
MaritalStatus = S 이고 Gender = F 인 row 출력
-------------------------------- */
| 5. procedure 에 input parameter 사용 |
alter proc GetCustomers
@maritalstatus nchar(1), -- 외부 변수
@gender nvarchar(1) -- 외부 변수
as
begin
select FirstName + ' ' + LastName as [Full Name],
MaritalStatus,
Gender
from DimCustomer
where MaritalStatus = @maritalstatus
and Gender = @gender
end
-- 실행 1
exec GetCustomers -- error
-- 실행 2
exec GetCustomers 'S', 'F'
/* ------------------------
실행 2 의 결과 :
MaritalStatus = S Gender = F 인 row select
/* ------------------------
| 6. procedure 에 input parameter 사용 + 변수에 default 값 주기 |
alter proc GetCustomers
@maritalstatus nchar(1) = 'S',
@gender nvarchar(1) = 'F'
as
begin
select FirstName + ' ' + LastName as [Full Name],
MaritalStatus,
Gender
from DimCustomer
where MaritalStatus = @maritalstatus
and Gender = @gender
end
-- 실행 1
exec GetCustomers
-- 실행 2
exec GetCustomers 'M', 'M'
/* ---------------------------
실행 1 결과 :
default 로 설정된 F / S 값이 들어간 row select
실행 2 결과 :
입력된 파라미터 M /M 값이 들어간 row select
--------------------------- */
| 7. Output Parameter 사용 |
alter proc GetCustomers
@maritalstatus nchar(1) = 'S',
@gender nvarchar(1) = 'F',
@count int out -- out parameter 선언
as
begin
select FirstName + ' ' + LastName as [Full Name],
MaritalStatus,
Gender
from DimCustomer
where MaritalStatus = @maritalstatus
and Gender = @gender
set @count = @@ROWCOUNT
end
/*******************************
-- 실행 1
exec GetCustomers
-- 실행 2
exec GetCustomers 'M', 'F'
-- 실행 3
exec GetCustomers @count out
-- 실행 4
declare @count int
exec GetCustomers 'M', 'M', @count out
select @count as [Records]
-- 실행 5 p. 265
declare @count int
exec GetCustomers , , @count out
select @count as [Records]
-- 실행 6 p. 265
declare @count int
exec GetCustomers default, default, @count out
select @count as [Records]
-- 실행 7 p. 265
declare @count int
declare @return int
exec @return = GetCustomers default, default, @count out
select @count as [Records]
select @return as [Return]
*******************************/
| 실행 결과 -- 결과 1 프로시저 또는 함수 'GetCustomers'에 매개 변수 '@count'이(가) 필요하지만 제공되지 않았습니다. -- 결과 2 프로시저 또는 함수 'GetCustomers'에 매개 변수 '@count'이(가) 필요하지만 제공되지 않았습니다. -- 결과 3 스칼라 변수 "@count"을(를) 선언해야 합니다. -- 결과 4 Records ---------- 1 5266 -- 결과 5 ',' 근처의 구문이 잘못되었습니다. -- 결과 6 Records ---------- 1 4388 -- 결과 7 Records ------------ 1 4388 Return ------------ 1 0 |
| 8. Output Parameter + Return in Procedure |
-- p. 267
alter proc GetCustomers
@maritalstatus nchar(1) = 'S',
@gender nvarchar(1) = 'F',
@count int out
as
begin
declare @err int
select FirstName + ' ' + LastName as [Full Name],
MaritalStatus,
Gender
from DimCustomer
where MaritalStatus = @maritalstatus
and Gender = @gender
set @count = @@ROWCOUNT
if @count = 0
return -99
else
return 0
end
/****************************************
declare @count int
declare @return int
exec @return = GetCustomers 'X', 'Y', @count out
-- X, Y 를 넣으면 row 는 0개 select
select @count as [Records]
select @return as [Return]
****************************************/
| 결과 : Records ------------ 1 0 Return ------------ 1 -99 |
| 9. Procedure 에서 다른 Procdure 호출 |
-- p. 268
create proc CallGetCustomers as
begin
declare @count int
declare @return int
exec @return = GetCustomers 'S', 'F', @count out
select @count as [Records]
select @return as [Return]
end
/***********************************
실행 :
exec CallGetCustomers
***********************************/
| 결과 : Records ------------ 1 4388 Return ------------ 1 0 |
| 10. Try ... Catch |
-- p. 269
alter proc CallGetCustomers as
begin
declare @count int
declare @return int
begin try
exec @return = GetCustomers 'S', 'F', @count out
// GetCustomers 'S', 'F', @count out 호출하면 리턴값 = 0
end try
begin catch
set @count = 0
set @return = -100
print ERROR_MESSAGE()
end catch
select @count as [Records]
select @return as [Return]
end
/******************************
exec CallGetCustomers
******************************/
| 결과 : Records ------------ 1 4388 Return ------------ 1 0 설명 : Try block 에서 문제가 발생하면 Catch block 이 trigger 된다 |
| 만일 GetCustomers 에서 from DimCustomer 을 from XXXX 라고 하고 실행하면 결과 : Return ------------ 1 -100 |
| 11. Procedure 호출부분에 input parameter 넣어주기 |
-- p. 271
alter proc CallGetCustomers
@maritalstatus nchar(1) = 'S',
@gender nvarchar(1) = 'F'
as
begin
declare @count int
declare @return int
begin try
-- input parameter 에 상수가 아닌 변수를 넣어준다
exec @return = GetCustomers @maritalstatus, @gender, @count out
end try
begin catch
set @count = 0
set @return = -100
print ERROR_MESSAGE()
end catch
select @count as [Records]
select @return as [Return]
end
/*****************************************
-- 실행 1
exec CallGetCustomers 'X', 'Y' -- 결과 row 개수 = 0
-- 실행 2
exec CallGetCustomers default, default
-- 실행 3
exec CallGetCustomers 'S', 'M'
*****************************************/
| 결과 1 : Records ------------ 1 0 Return ------------ 1 -99 -------------------------------- 결과 2 : Records ------------ 1 4388 Return ------------ 1 0 -------------------------------- 결과 3 : Records ------------ 1 4085 Return ------------ 1 0 |
'[prog] SQL - MSSQL' 카테고리의 다른 글
| SQL SERVER - SSMS tip (0) | 2020.10.15 |
|---|---|
| MSSQL 날짜변환 / 날짜계산CONVERT / DATETIME (0) | 2020.10.11 |
| 자주 사용하는 쿼리문들 (Queries Frequently Used) (0) | 2020.09.17 |
| SQL SERVER / 서버의 DB 를 로컬로 복사(가져오기) (0) | 2020.09.09 |
