sql문(DDL)으로 테이블을 생성했는 데(아주 많이) 이 테이블들이
master 데이타베이스에 생겨서 어떻게 할 수 없을까 고민했었는 데,
무엇이 문제였는 지 알게 해준 고마운 글이다. 역시 기본이 부족하다....
[출처]http://blog.naver.com/blue7red/100045287422
-----------------------------------------------------------------
1.기본적인 T-SQL구문
[with <common_table_expression>]
select select_list [ into new_table ]
[from table_source]
[where search_condition]
[group by group_by_expression]
[having search_expression]
[order by order_expression [asc | desc] ]
(1)기본적인 쿼리들
exec sp_helpdb; //데이터베이스 정보를 볼 수 있다.
use AdventurWorks; // 데이터베이스 선택
exec sp_tables @table_type ="'table'"; //선택된 데이터베이스의 테이블 정보를 보여준다.
exec sp_columns
@table_name = 'Department',
@table_owner='HumanResources';
// 소유자가 HumanResources이고 테이블이름이 Department인 테이블의 컬럼정보를 보여준다.
select name,groupname from HumanResources.Department; // 조회쿼리실행
(2)select ...from....
회원 테이블생성 :구매 테이블 생성
1:다의 관계 - 회원은 한번만 가입하지만 한 회원당 구매에 관한 기록은 여럿이 될 수 있기 때문이다.
USE master
CREATE DATABASE sqlDB
GO
USE sqlDB
CREATE TABLE userTbl -- 사용자테이블
( userID nchar(8) NOT NULL PRIMARY KEY, -- 사용자아이디
name nvarchar(10) NOT NULL, -- 이름
birthYear int NOT NULL, -- 출생년도
addr nchar(4) NOT NULL, -- 지역(경기,서울,경남,전북식으로2글자만입력)
mobile1 nchar(3), -- 휴대폰의국번(011, 016, 017, 018, 019, 010 등)
mobile2 nchar(8), -- 휴대폰의나머지전화번호(하이픈제외)
height smallint -- 신장
)
GO
CREATE TABLE buyTbl -- 사용자구매테이블
( num int IDENTITY NOT NULL PRIMARY KEY, -- 순번(PK)
userid nchar(8) NOT NULL --아이디(FK)
FOREIGN KEY REFERENCES userTbl(userid),
prodName nchar(6) NOT NULL, -- 물품명
groupName nchar(4) , -- 분류
price int NOT NULL, -- 단가
amount smallint NOT NULL -- 수량
)
GO
INSERT INTO userTbl VALUES(N'PJS', N'박지성', 1983, N'서울', N'011', N'1111111', 181)
INSERT INTO userTbl VALUES(N'PJY', N'박주영', 1986, N'경기', N'011', N'2222222', 178)
INSERT INTO userTbl VALUES(N'JJJ', N'조재진', 1986, N'충북', N'019', N'3333333', 179)
INSERT INTO userTbl VALUES(N'LCS', N'이천수', 1983, N'인천', N'011', N'4444444', 179)
INSERT INTO userTbl VALUES(N'AJH', N'안정환', 1979, N'강원', NULL , NULL , 182)
INSERT INTO userTbl VALUES(N'KNI', N'김남일', 1977, N'경북', N'016', N'6666666', 183)
INSERT INTO userTbl VALUES(N'LYP', N'이영표', 1983, N'전북', NULL , NULL , 178)
INSERT INTO userTbl VALUES(N'SKH', N'설기현', 1978, N'서울', N'011', N'8888888', 182)
INSERT INTO userTbl VALUES(N'SJK', N'송종국', 1979, N'경기', N'018', N'9999999', 178)
INSERT INTO userTbl VALUES(N'CJC', N'최진철', 1975, N'제주', N'019', N'0000000', 185)
GO
INSERT INTO buyTbl VALUES(N'PJY', N'운동화', NULL , 30, 2)
INSERT INTO buyTbl VALUES(N'PJY', N'노트북', N'전자', 1000, 1)
INSERT INTO buyTbl VALUES(N'LCS', N'모니터', N'전자', 200, 1)
INSERT INTO buyTbl VALUES(N'CJC', N'모니터', N'전자', 200, 5)
INSERT INTO buyTbl VALUES(N'PJY', N'청바지', N'의류', 50, 3)
INSERT INTO buyTbl VALUES(N'CJC', N'메모리', N'전자', 80, 10)
INSERT INTO buyTbl VALUES(N'AJH', N'책' , N'서적', 15, 5)
INSERT INTO buyTbl VALUES(N'SKH', N'책' , N'서적', 15, 2)
INSERT INTO buyTbl VALUES(N'SKH', N'청바지', N'의류', 50, 1)
INSERT INTO buyTbl VALUES(N'CJC', N'운동화', NULL , 30, 2)
INSERT INTO buyTbl VALUES(N'SKH', N'책' , N'서적', 15, 1)
INSERT INTO buyTbl VALUES(N'CJC', N'운동화', NULL , 30, 2)
GO
SELECT * FROM userTbl
SELECT * FROM buyTbl
USE master
BACKUP DATABASE sqlDB TO DISK = 'C:\sqlDB.bak'
(3)특정한 조건의 데이터만 조회하는 select ..from ...where...
SELECT * FROM userTbl
SELECT * FROM userTbl WHERE name = '박지성'
SELECT userID, Name FROM userTbl WHERE birthYear >= 1981 AND height >= 180
SELECT userID, Name FROM userTbl WHERE birthYear >= 1981 OR height >= 180
SELECT Name, height FROM userTbl WHERE height >= 180 AND height <= 183
SELECT Name, height FROM userTbl WHERE height BETWEEN 180 AND 183 // 위 구문과 동일하다.
SELECT Name, height FROM userTbl WHERE height=178 OR height=180 OR height=182
SELECT Name, height FROM userTbl WHERE height IN (178, 180, 182) // 위 구문과 동일하다.
SELECT height FROM userTbl WHERE Name = '박지성'
SELECT Name, height FROM userTBL WHERE height > 181
SELECT Name, height FROM userTbl
WHERE height > (SELECT height FROM userTbl WHERE Name = '박지성') //위 두 구문과 동일하다.(하위쿼리)
SELECT Name, height FROM userTbl
WHERE height >= (SELECT height FROM userTbl WHERE mobile1 = '019') // 하위쿼리가 두 개 이상인 경우는 문제가 발생
//
이럴 때는 아래처럼 any를 써야 한다.
SELECT Name, height FROM userTbl
WHERE height >= ANY (SELECT height FROM userTbl WHERE mobile1 = '019') //179이상
SELECT Name, height FROM userTbl
WHERE height = ANY (SELECT height FROM userTbl WHERE mobile1 = '019') //height in (179,82)와 동일하다.
SELECT Name, height FROM userTbl
WHERE height IN (SELECT height FROM userTbl WHERE mobile1 = '019') // 위아래 구문은 동일하다.
//179이상이고 182이상이어야 한다. 즉 ALL은 두 가지 결과값을 모두 만족해야 한다.
SELECT Name, height FROM userTbl
WHERE height >= ALL (SELECT height FROM userTbl WHERE mobile1 = '019') //182이상
//정렬
SELECT Name, height FROM userTbl ORDER BY height // 기본적으로 오름차순
SELECT Name, height FROM userTbl ORDER BY height DESC //내림차순
SELECT Name, height FROM userTbl ORDER BY height DESC, name ASC //키가 같은 경우에는 이름은 내림차순으로 출력
//중복제거
SELECT addr FROM userTbl
SELECT addr FROM userTbl ORDER BY addr
SELECT DISTINCT addr FROM userTbl
//상위 N개만 출력
USE AdventureWorks
SELECT CreditCardID FROM Sales.CreditCard
WHERE CardType = 'Vista'
ORDER BY ExpYear, ExpMonth
SELECT TOP(10) CreditCardID FROM Sales.CreditCard
WHERE CardType = 'Vista'
ORDER BY ExpYear, ExpMonth
//1%만 보기
SELECT TOP(SELECT COUNT(*)/100 FROM Sales.CreditCard ) CreditCardID
FROM Sales.CreditCard
WHERE CardType = 'Vista'
ORDER BY ExpYear, ExpMonth
//0.1%만 보기
SELECT TOP(0.1)PERCENT CreditCardID FROM Sales.CreditCard
WHERE CardType = 'Vista'
ORDER BY ExpYear, ExpMonth
//퍼센트계산에 의한 중복발생시 중복허용 - 장학생 선발시
SELECT TOP(0.1)PERCENT WITH TIES CreditCardID, ExpMonth, ExpYear FROM Sales.CreditCard
WHERE CardType = 'Vista'
ORDER BY ExpYear, ExpMonth
//일정 샘플 추출 - 대용량에서 의미가 있다.
USE AdventureWorks
SELECT * FROM Sales.SalesOrderDetail TABLESAMPLE(5 PERCENT) //페이지단위의 5퍼센트
//조회시 수가 일정하지 않다.
SELECT TOP(5000) * FROM Sales.SalesOrderDetail TABLESAMPLE(5 PERCENT) //일정수만 뽑기
//기존의 테이블을 복사해서 새로운 테이블생성
USE sqlDB
SELECT * INTO buyTbl2 FROM buyTbl
SELECT * FROM buyTbl2
SELECT userid, prodName INTO buyTbl3 FROM buyTbl
SELECT * FROM buyTbl3
(4)group by 및 having 그리고 집계함수
집계함수 - 각각 인자는 없다.
avg(평균), min(최소값) , max(최대값) , count(행의 개수),
count_max(int형을 초과하는 행의 개수도 셀 수 있다. 결과값은 bigint형도 가능하다)
stdev(표준편차)
[with <common_table_expression>]
select select_list [ into new_table ]
[from table_source]
[where search_condition]
[group by group_by_expression]
[having search_expression]
[order by order_expression [asc | desc] ] //세 가지의 순서가 중요하다.
USE sqlDB
SELECT userid, price, amount FROM buyTbl ORDER BY userid
//그룹으로 묶기
SELECT userid, SUM(amount) FROM buyTbl GROUP BY userid //사용자별 구매수
SELECT userid AS [사용자아이디], SUM(amount) AS [총구매개수]
FROM buyTbl GROUP BY userid
SELECT userid AS [사용자아이디], SUM(price*amount) AS [총구매액] //사용자별 구매액
FROM buyTbl GROUP BY userid
//평균
SELECT AVG(amount) AS [평균구매개수] FROM buyTbl //정수형의 평균이므로 반올림때문에 잘못나올 수 있다.
SELECT AVG(amount*1.0) AS [평균구매개수] FROM buyTbl // 실수형으로 변환해서 구해라
-- 또는
SELECT AVG(CAST(amount AS DECIMAL(10,6))) AS [평균구매개수] FROM buyTbl //실수형으로 변환
SELECT userid, AVG(amount*1.0) AS [평균구매개수] FROM buyTbl
GROUP BY userid
//최대,최소
SELECT Name, MAX(height), MIN(height) FROM userTbl //문제가 있는 쿼리- 쿼리가 실패한다.
SELECT Name, MAX(height), MIN(height) FROM userTbl GROUP BY Name //문제가 있는 쿼리
SELECT Name, height
FROM userTbl
WHERE height = (SELECT MAX(height)FROM userTbl)
OR height = (SELECT MIN(height)FROM userTbl)
SELECT COUNT(*) FROM userTbl
SELECT COUNT(mobile1) FROM userTbl //휴대폰이 있는 사용자의 수
//집계함수와 일반SQL의 성능비교
-프로파일러 실행 - 새추적 - 실행
USE AdventureWorks
GO
SELECT * FROM Sales.Customer
GO
SELECT COUNT(*) FROM Sales.Customer // 위의 구문보다 훨씬 빠르다.(몇 백 배이상)
GO
//SQL프로그래밍을 이용한 경우와 집계함수의 성능비교
-집계함수를 사용하는 것이 SQL프로그래밍보다 성능이 더 좋다.
USE sqlDB
//집계함수
SELECT num, price, amount INTO #tmpTbl FROM buyTbl // #이 붙으면 임시테이블이다.
GO
INSERT INTO #tmpTbl
SELECT a.price, a.amount FROM #tmpTbl a, #tmpTbl b //데이터건수를 만들기위해서
GO
SELECT sum(price*amount) from #tmpTbl
//SQL프로그래밍
DECLARE @total bigint -- @total은 총구매액을 누적시킬변수
DECLARE @priceXamount int -- @priceXamount는 각행의 가격과 수량을 곱한값을 저장할 변수
DECLARE @num int -- @num 은구매테이블의 순번열의 증가
DECLARE @count int -- @count는 전체행 숫자
SET @total=0
SET @num=1
SELECT @count=COUNT(*) FROM #tmpTbl
SET @count = @count + 1
WHILE @count <> @num
BEGIN
SELECT @priceXamount = price * amount FROM #tmpTbl
WHERE num = @num
SET @num = @num + 1
SET @total = @total + @priceXamount
END
PRINT @total
(5)having절
SELECT userid AS [사용자], SUM(price*amount) AS [총구매액]
FROM buyTbl
GROUP BY userid
SELECT userid AS [사용자], SUM(price*amount) AS [총구매액] //
이 구문은 실행되지 않는다.
FROM buyTbl
WHERE SUM(price*amount) > 1000
GROUP BY userid
SELECT userid AS [사용자], SUM(price*amount) AS [총구매액]
FROM buyTbl
GROUP BY userid
HAVING SUM(price*amount) > 1000 //합계가 1000이상인 사람별로 그룹을 묶는다.(순서가 중요하다)
//내림차순으로
SELECT userid AS [사용자], SUM(price*amount) AS [총구매액]
FROM buyTbl
GROUP BY userid
HAVING SUM(price*amount) > 1000
ORDER BY SUM(price*amount) ASC
(6)결과의 요약
compute - 전체를 나열 한 후에, 그 집계결과를 출력
compute by - 각 소그룹단위로 집계함수를 사용
rollup/grouping()/cube - 총합 또는 중간합계가 필요시에
단점-체계가 흐트러진다. rollup으로 보완가능하다.
SELECT *, price*amount AS [가격] FROM buyTbl
COMPUTE SUM(price * amount)
COMPUTE AVG(price * amount)
SELECT *, price * amount FROM buyTbl
ORDER BY groupName
COMPUTE SUM(price * amount) BY groupName
COMPUTE AVG(price * amount) BY groupName
SELECT *, price * amount FROM buyTbl
ORDER BY groupName
COMPUTE SUM(price * amount) BY groupName
COMPUTE AVG(price * amount) BY groupName
COMPUTE SUM(price * amount)
COMPUTE AVG(price * amount)
SELECT num, groupName, SUM(price * amount) AS [비용]
FROM buyTbl
GROUP BY groupName, num
WITH ROLLUP //각각 ,소합 ,총합(하나의 테이블로 나온다)
SELECT groupName, SUM(price * amount) AS [비용]
FROM buyTbl
GROUP BY groupName
WITH ROLLUP
SELECT groupName, SUM(price * amount) AS [비용]
, GROUPING(groupName) AS [추가행여부]
FROM buyTbl
GROUP BY groupName
WITH ROLLUP
//cube는 다차원으로 조절이 가능하다.
USE sqlDB
CREATE TABLE cubeTbl(prodName NCHAR(3), color NCHAR(2), amount INT)
GO
//컴퓨터끼리 그룹핑가능,검정끼리 그룹핑가능 -다차원 그룹핑가능예제
INSERT INTO cubeTbl VALUES('컴퓨터', '검정', 11)
INSERT INTO cubeTbl VALUES('컴퓨터', '파랑', 22)
INSERT INTO cubeTbl VALUES('모니터', '검정', 33)
INSERT INTO cubeTbl VALUES('모니터', '파랑', 44)
GO
SELECT prodName, color, SUM(amount) AS [수량합계]
FROM cubeTbl
GROUP BY prodName, color
WITH CUBE
(7)with절과 cte
-with절 : cte(common table expression)을 표현하기 위한 구문이다.
cte는 기본의 뷰,파생테이블,임시테이블을 대신할 수 있으며 더 간결하게 표현된다.
ansi-sql99표준으로 제안되었다.
재귀적 cte와 비재귀적 cte로 구분된다.
비재귀적 cte - 단순한 형태로 복잡한 쿼리문을 단순화시키는데 적합하다.
구문형식
with cte_테이블이름(열이름)
as
(<쿼리문>)
select 열이름 from cte_테이블이름;
SELECT userid AS [사용자], SUM(price*amount) AS [총구매액]
FROM buyTbl GROUP BY userid
USE sqlDB
//비재귀적 CTE예제
WITH abc(userid, total) //userid와 total이라는 컬럼을 가진 새로운 테이블 abc생성
AS
(SELECT userid, SUM(price*amount)
FROM buyTbl GROUP BY userid )
SELECT * FROM abc ORDER BY total DESC ;
SELECT addr, MAX(height) FROM userTbl GROUP BY addr
WITH cte_userTbl(addr, maxHeight)
AS
( SELECT addr, MAX(height) FROM userTbl GROUP BY addr)
WITH cte_userTbl(addr, maxHeight)
AS
( SELECT addr, MAX(height) FROM userTbl GROUP BY addr)
SELECT AVG(maxHeight*1.0) AS [각지역별최고키의평균] FROM cte_userTbl
SELECT * FROM userTbl
WITH abc(userid, total)
AS
(SELECT userid, SUM(price*amount)
FROM buyTbl GROUP BY userid )
SELECT * FROM abc ORDER BY total DESC
SELECT * FROM userTbl
GO
WITH abc(userid, total)
AS
(SELECT userid, SUM(price*amount)
FROM buyTbl GROUP BY userid )
SELECT * FROM abc ORDER BY total DESC
-- 또는
SELECT * FROM userTbl ;
WITH abc(userid, total)
AS
(SELECT userid, SUM(price*amount)
FROM buyTbl GROUP BY userid )
SELECT * FROM abc ORDER BY total DESC
재귀적 cte - 자기자신을 반복적으로 호출하는 의미
-회사의 조직도 등이 대표적인 예
구문형식
with cte_테이블이름(열이름)
as
(
<쿼리문1: select * from 테이블A>
union all
<쿼리문2: select * from 테이블A join cte_테이블이름>
)
select * from cte_테이블이름;
사장 - 레벨 0
이사 - 레벨 1
부장 - 레벨 2
사원 - 레벨 3
USE sqlDB
CREATE TABLE empTbl (emp NCHAR(3), manager NCHAR(3), department NCHAR(3))
GO
INSERT INTO empTbl VALUES('나사장',NULL,NULL)
INSERT INTO empTbl VALUES('김재무','나사장','재무부')
INSERT INTO empTbl VALUES('김부장','김재무','재무부')
INSERT INTO empTbl VALUES('이부장','김재무','재무부')
INSERT INTO empTbl VALUES('우대리','이부장','재무부')
INSERT INTO empTbl VALUES('지사원','이부장','재무부')
INSERT INTO empTbl VALUES('이영업','나사장','영업부')
INSERT INTO empTbl VALUES('한과장','이영업','영업부')
INSERT INTO empTbl VALUES('최정보','나사장','정보부')
INSERT INTO empTbl VALUES('윤차장','최정보','정보부')
INSERT INTO empTbl VALUES('이주임','윤차장','정보부')
WITH empCTE(empName, mgrName, dept, level)
AS
(
SELECT emp, manager, department , 0
FROM empTbl
WHERE manager IS NULL -- 상관이 없는 사람이 바로 사장
UNION ALL
SELECT AA.emp, AA.manager, AA.department, BB.level+1
FROM empTbl AS AA INNER JOIN empCTE AS BB
ON AA.manager = BB.empName
)
SELECT FROM empCTE ORDER BY dept, level
//ㄴ의 개수로 조직도와 비슷하게 구현
WITH empCTE(empName, mgrName, dept, level)
AS
(
SELECT emp, manager, department , 0
FROM empTbl
WHERE manager IS NULL -- 사장
UNION ALL
SELECT AA.emp, AA.manager, AA.department, BB.level+1
FROM empTbl AS AA INNER JOIN empCTE AS BB
ON AA.manager = BB.empName
)
SELECT replicate(' ㄴ', level) + empName AS [직원이름], dept [직원부서]
FROM empCTE ORDER BY dept, level //레벨별로 L개수를 이름앞에 추가
//부장,차장,과장급까지만 출력
WITH empCTE(empName, mgrName, dept, level)
AS
(
SELECT emp, manager, department , 0
FROM empTbl
WHERE manager IS NULL -- 사장
UNION ALL
SELECT AA.emp, AA.manager, AA.department, BB.level+1
FROM empTbl AS AA INNER JOIN empCTE AS BB
ON AA.manager = BB.empName
WHERE level < 2
)
SELECT replicate(' ㄴ', level) + empName AS [직원이름], dept [직원부서]
FROM empCTE ORDER BY dept, level
(8)기본T_SQL 구문
<1> T-SQL의 분류
DML - 데이터 조작언어 - INSERT,UPDATE,DELETE,SELECT ,트랜잭션을 발생시킨다(SELECT는 예외) 대상은 테이블의 행
DDL - 데이터 정의 언어 - CREATE , DROP,ALTER , 트랙잭션을 발생시키지 않는다.
DCL - 데이터 제어 언어 - 사용자에게 권한부여시에 주로 사용된다. GRANT,REVOKE,DENY
<2>데이터변경
-데이터삽입
INDENTITY지정시에는 입력값을 생략가능하다. DEFAULT값
대량의 데이터삽입 : insert into 테이블이름 (열이름1,열이름2, ...) select ....
USE tempDB
CREATE TABLE testTbl1 (id int, userName nchar(3), age int);
GO
INSERT INTO testTbl1 VALUES (1, '희동구', 25)
INSERT INTO testTbl1(id, userName) VALUES (2, '아드복') //입력컬럼 지정
INSERT INTO testTbl1(userName, age, id) VALUES ('홍명보', 31, 3) //입력순서지정
CREATE TABLE testTbl2
(id int IDENTITY, userName nchar(3),
age int, nation nchar(4) DEFAULT '대한민국');
GO
INSERT INTO testTbl2 VALUES ('구엘루', 30, DEFAULT) //디폴트값 입력
SET IDENTITY_INSERT testTbl2 on //아이덴터티 직접입력하겠다.
GO
INSERT INTO testTbl2(id, userName,age,nation) VALUES (11,'희동구', 29, '네덜란드') //컬럼지정해야한다.
INSERT INTO testTbl2 VALUES (12,'아드복', 31, '네덜란드')
SET IDENTITY_INSERT testTbl2 OFF //아인덴터티를 자동으로 입력하겠다.
INSERT INTO testTbl2 VALUES ('아드복', 31, '네덜란드')
SELECT * FROM testTbl2
USE tempDB
CREATE TABLE testTbl3 (id int, Fname nvarchar(50), Lname nvarchar(50), tel nvarchar(25))
GO
INSERT INTO testTbl3
SELECT ContactID, FirstName, LastName, Phone
FROM AdventureWorks.Person.Contact //컬럼을 맞추어서 쿼리해서 데이터 삽입
<3>데이터수정
-데이터 업데이트는 where 절은 조심해야된다.
UPDATE testTbl3
SET tel = '없음'
WHERE Fname = 'Kim'
USE sqlDB
UPDATE buyTbl SET price = price * 1.5
SELECT * FROM buyTbl
USE tempDB
<4>데이터 삭제
데이터 삭제는 where절은 조심해야된다.
DELETE testTbl3 WHERE Fname = 'Kim'
USE tempDB
SELECT * INTO bigTbl1 FROM AdventureWorks.Sales.SalesOrderDetail
SELECT * INTO bigTbl2 FROM AdventureWorks.Sales.SalesOrderDetail
SELECT * INTO bigTbl3 FROM AdventureWorks.Sales.SalesOrderDetail
GO
DELETE FROM bigTbl1 //데이터 삭제
DROP TABLE bigTbl2 //테이블을 통째로 없앰
TRUNCATE TABLE bigTbl3 //데이터를 깨끗이 삭제
//위 셋의 성능상의 차이:truncate 테이블이 제일빠르다. drop도 truncate가 비슷한 성능이다. delete는 엄청느리다.
delete는 트랜잭션이 발생하기때문에 느리다.truncate은 트랜잭션이 발생하지 않기 때문에 빠르다.
EXEC sp_tables @table_type = "'TABLE'" ;
2. SQL Server의 데이터 형식
(1)SQL 서버2005에서 지원하는 데이터형식의 종류
<1>숫자형 데이터 형식
BIT 1바이트 0 또는 1 Boolean형인 참 거짓에 사용
INT 4바이트 약-21억 ~ +21억 정수
SMALLINT 2바이트 -32768~ 32767 정수
TINYINT 1바이트 0~255 양의 정수
BIGINT 8바이트 -2의 63승 ~ +2의 63승 -1 정수
DECIMAL 5~17바이트 N/A 고정 정밀도(p)와 배율(s)을 가진 숫자형.
예)decimal(5,2)는 전체자릿수를 5자리로 하되, 그 중 소수점 이하를 2자리로 하겠다
NUMERIC 5~17바이트 N/A DECIMAL과 동일한 데이터형이다.
REAL 4 바이트 FLOAT(24)와 동일하다.
FLOAT 4~8바이트 P가 25미만이면 4바이트,25이상이면 8바이트를 가진다.
MONEY 8바이트 -2의 63승 ~ +2의 63승 -1 화폐단위로 사용된다.
SMALLMONEY 4바이트 약-21억 ~ +21억 화폐단위로 사용된다.
<2>문자형 데이터 형식
CHAR 0~8000 고정길이 문자형
NCHAR 0~80000 글자로는 4000자,유니코드 고정길이 문자열
VARCHAR 0 ~ 2의 21승 -1 (2기가) 가변길이 문자형
NVARCHAR 0~2의 21승 -1 2의 20승-1까지의 문자를 표현 할 수 있다.
TEXT(N) 0~2의 21승 -1 이전버전에서는 8000자가 넘는 글자의 경우에 사용하였음
SQL서버2005에서는 VARCHAR(MAX)를 대신 사용할 것을 권장한다.
NTEXT(N) TEXT의 유니코드형
BINARY(N) 0~8000 고정길이의 이진 데이터 값
VARBINARY(N) 0~2의 21승 -1 가변길이의 이진 데이터 값. SQL서버2005이전 버전은 0~8000까지 가능했었다.
IMAGE 0~2의 21승 -1 SQL서버2005이전 버전에서는 8000자가 넘는 이진 데이터의 경우에 사용하였으나
SQL서버2005에서는 VARBINARY(MAX)를 대신 사용할 것을 권장한다.
<3>시간과 날짜 데이터형식
DATETIME 8바이트 1753 1/1~9999/12/31까지 저장,정확도는 1/1000초 단위까지
SAMLLDATETIME 4바이트 1900/1/1~2079/1/6까지 저장.정확도는 분 단위까지
<4>기타 데이터 형식
TIMESTAMP 8바이트 VARBINARY(8)과 동일하며 데이터베이스 내에서 자동으로 생성된 고유이진 숫자를 표시한다.
SQL 서버 내부적으로 사용되는 것이라서 신경을 쓸 필요가 없다.
ROWVERSION 8바이트 TIMESTAMP와 동일하다
SYSNAME 128바이트 NVARCHAR(128)과 동일하며 데이터베이스 개체의 이름에 사용된다. 내부적으로 사용된다.
CURSOR 1바이트 T-SQL 커서를 변수로 처리한다.
TABLE 테이블 자체를 저장.임시 테이블과 비슷한 기능
UNIQUEIDENTIFIER 16바이트 복제에서 사용되는 자료형으로 유일성을 보장하기 위한 GUID값을 지정한다.
SQL_VARIANT 다른 데이터형식의 저장이 가능한 데이터형(예외도 있다)
XML XML데이터를 저장하기 위한 형식.SQL 서버2005에서 새로 나왔다.
(2)사용자정의 데이터형식
사용자정의 데이터 형식이란 기존의 데이터 형식에 별칭을 붙으는 것이다.
사용자의 편의성때문에 주로 사용된다.
//nchar(10)을 typeName으로 정의하면 아래와 같다.
//스키마가 dbo이고 null을 허용한다.
EXEC sp_addtype 'typeName', 'nchar(10)', 'NULL', 'dbo'
(3)SQL2005에서 달라진 점
LOB(Large Object)를 저장하기 위해서 이전버전에 지원하던 TEXT,NTEXT,IMAGE 대신
VARCHAR(MAX),NVARCHAR(MAX),VARBINARY(MAX)형식을 지원한다.
기존의 방식은 변수나 출력 파라미터를 선언할 수 없었으며 문자열 함수를 사용할수 없었으나
새로운 버전에서는 VARCHAR(MAX)등은 VARCHAR(N)형식과 동일하게 사용이 가능하다.
(4)유니코드 데이터
전세계의 어느 언어를 저장하든지 서로 충돌하는 상황이 발생하지 않는다.
유니코드의 상수를 지정하기 위해서는 N'문자열'과 같은 형식을 사용할 것이 좋다.
(5)변수의 사용
주의할 사항: 변수는 지역변수로서의 의미가 있으므로 사용할 때는 한 번에 사용해야 한다.
USE master
RESTORE DATABASE sqlDB FROM DISK = 'C:\sqlDB.bak' WITH REPLACE
DECLARE @myVar1 INT
DECLARE @myVar2 SMALLINT, @myVar3 DECIMAL(5,2) //소수점 둘째자리까지
DECLARE @myVar4 NCHAR(20)
SET @myVar1 = 5
SET @myVar2 = 3
SET @myVar3 = 4.25
SET @myVar4 = '축구선수이름==> '
SELECT @myVar1
SELECT @myVar2 + @myVar3
USE sqlDB
SELECT @myVar4 , Name FROM userTbl WHERE height > 182 //변수와 컬럼을 혼용해서 사용할 수 있다.
USE sqlDB
DECLARE @myVar1 INT
SET @myVar1 = 3
SELECT TOP(@myVar1) Name, height FROM userTbl ORDER BY height
(6)데이터 형식과 관련된 시스템 함수들
종류:
명시적 형변환 -cast ,convert를 사용하는 방법
암시적 형변환 (문자 + 정수 = 정수 , 문자 + 실수 = 실수) - 사용자의 착오로 인해서 엉뚱한 결과가 나올 수 있다.
USE sqlDB
SELECT AVG(amount) AS [평균구매개수] FROM buyTbl
SELECT AVG( CAST(amount AS FLOAT) ) AS [평균구매개수] FROM buyTbl
//실수형으로 캐스팅해야 적당한 값을 얻을 수 있다.
-- 또는
SELECT AVG( CONVERT(FLOAT, amount) ) AS [평균구매개수] FROM buyTbl
SELECT price, amount, price/amount AS [단가/수량] FROM buyTbl
SELECT price, amount, CAST(CAST(price AS FLOAT)/amount AS DECIMAL(10,2))
AS [단가/수량] FROM buyTbl
DECLARE @myVar1 char(3)
SET @myVar1 = '100'
SELECT @myVar1 + '200' -- 문자와문자를더함(정상)
SELECT @myVar1 + 200 -- 문자와정수를더함(정상: 정수로 암시적형변환)
SELECT @myVar1 + 200.0 -- 문자와실수를더함(정상: 실수로 암시적형변환)
DECLARE @myVar1 char(3)
SET @myVar1 = '100'
SELECT @myVar1 + '200' -- 문자와문자를더함(정상)
SELECT CAST(@myVar1 AS INT) + 200 -- 문자와정수를더함(정상:암시적형변환)
SELECT CAST(@myVar1 AS DECIMAL(5,1)) + 200.0 -- 문자와실수를더함(정상:암시적형변환)
DECLARE @myVar2 DECIMAL(10,5)
SET @myVar2 = 10.123450
SELECT CAST(@myVar2 AS NCHAR(5))
DECLARE @myVar3 DECIMAL(10,5)
SET @myVar3 = 10.12345
SELECT CAST(@myVar3 AS INT)
DECLARE @myVar4 DECIMAL(10,5)
SET @myVar4 = 10.12345
SELECT CAST(@myVar4 AS DECIMAL(10,2))
(7)스칼라 함수
-단일 값에 적용되어 단일 값의 결과 함수
-종류로는 구성함수,커서함수,날짜/시간함수,수치함수,문자열함수
<구성함수> - 현재 구성에 대한 정보를 알 수 있다.
@@LANGID
@@LANGUAGE
@@SERVERNAME
@@SERVICENAME
@@SPID
@@VERSION
<날짜 및 시간함수>
GETDATE
DATEADD
DATEDIFF
DATENAME
DAY,MONTH,YEAR
<수치 연산 함수>
ABS
ROUND
RAND
SQRT
POWER
<메타데이터함수>
COL_LENGTH
DB_ID
DB_NAME
OBJECT_ID
OBJECT_NAME
<문자열함수>
ASCII
CHAR
UNICODE
NCHAR
CHARINDEX
LEFT
RIGHT
SUBSTRING
LEN
LOWER
UPPER
LTRIM
RTRIM
REPLACE
REPLICATE
SPACE
REVERSE
STR
STUFF
--- 구성함수
select @@LANGID
SELECT @@SPID AS 'ID', SYSTEM_USER AS [로그인사용자], USER AS [사용자]
-- 날짜 및 시간 함수
SELECT GETDATE()
SELECT DATEADD(day, 100, '2006/10/10')
SELECT DATEDIFF(week, GETDATE(), '2022/10/19')
SELECT DATENAME(weekday, '2022/10/19')
SELECT MONTH('2022/10/19')
-- 수치 연산 함수
SELECT ABS(-100)
SELECT ROUND(1234.5678, 2), ROUND(1234.5678, -2)
SELECT RAND()
SELECT POWER(3,2)
-- 메타 데이터 함수
USE sqlDB
SELECT COL_LENGTH('userTbl','name')
SELECT DB_ID(N'AdventureWorks'); SELECT DB_NAME(6);
SELECT OBJECT_ID(N'sqlDB.dbo.userTbl');
SELECT OBJECT_NAME(2089058478);
-- 문자열 함수
SELECT ASCII('A'), CHAR(65)
SELECT UNICODE('가'), NCHAR(44032)
SELECT CHARINDEX('Server' , 'SQL Server 2005')
SELECT LEFT('SQL Server 2005', 3), RIGHT('SQL Server 2005', 4)
SELECT SUBSTRING(N'대한민국화이팅', 3, 2)
SELECT LEN('SQL Server 2005')
SELECT LOWER('abcdEFGH'), UPPER('abcdEFGH')
SELECT LTRIM(' 공백앞뒤두개 '), RTRIM(' 공백앞뒤두개 ')
SELECT REPLACE ('SQL Server 2005', 'Server' , '서버')
SELECT REPLICATE ('SQL', 5)
SELECT REVERSE ('SQL Server 2005')
SELECT STUFF ('SQL 서버 2005', 5, 2, 'Server')SELECT SPACE(5)
(8)MAX형 데이터 형식을 이용한 데이터 입력
USE tempDB
CREATE TABLE maxTbl
( col1 VARCHAR(MAX),
col2 NVARCHAR(max))
INSERT INTO maxTbl VALUES( REPLICATE('A',1000000), REPLICATE('가',1000000)) //백만개의 A와 가 입력
SELECT LEN(col1) AS [VARCHAR(MAX)], LEN(col2)AS [NVARCHAR(MAX)] FROM maxTbl //컬럼의 길이
//백만개를 넣었지만 A는 8000개만 가는 4000개만 입력된다.
DELETE FROM maxTbl
//아래처럼하면 100만 개가 제대로 들어간다.
INSERT INTO maxTbl VALUES(
REPLICATE( CAST('A' AS VARCHAR(MAX)) ,1000000 ) ,
REPLICATE( CONVERT(VARCHAR(MAX) , '가' ) ,1000000) )
SELECT LEN(col1) AS [VARCHAR(MAX)], LEN(col2)AS [NVARCHAR(MAX)] FROM maxTbl
UPDATE maxTbl SET col1 = REPLACE( (SELECT col1 FROM maxTbl),'A','B'),
col2 = REPLACE( (SELECT col2 FROM maxTbl),'가','나')
SELECT REVERSE((SELECT col1 FROM maxTbl)) //거꾸로 출력
SELECT SUBSTRING((SELECT col2 FROM maxTbl),999991, 10)//999991부터 열글자 출력
UPDATE maxTbl SET
col1 = STUFF( (SELECT col1 FROM maxTbl),999991, 10, REPLICATE('C',10)),
col2 = STUFF( (SELECT col2 FROM maxTbl),999991, 10, REPLICATE('다',10))
UPDATE maxTbl SET col1.WRITE('DDDDD',999996, 5), col2.WRITE('라라라라라',999996, 5)
SELECT REVERSE((SELECT col1 FROM maxTbl))
SELECT REVERSE((SELECT col2 FROM maxTbl))
(9)순위 함수
-순번를 처리하기 위한 함수
USE sqlDB
SELECT ROW_NUMBER( ) OVER(ORDER BY height DESC)[키큰 순위], //키큰순서로 정렬해서 번호를 매긴다.
name, addr, height
FROM userTbl
ORDER BY height DESC
USE sqlDB
SELECT ROW_NUMBER( ) OVER(ORDER BY height DESC, name ASC)[키큰순위],//키큰순서로 내림차순,이름순으로 오름차순
name, addr, height
FROM userTbl
ORDER BY height DESC
USE sqlDB
SELECT addr,
ROW_NUMBER( ) OVER(PARTITION BY addr
ORDER BY height DESC, name ASC)[지역별키큰순위],
name, height
FROM userTbl
ORDER BY addr, height DESC
//동순위 처리 1등이 세명이더라도 다음 순위는 2등
USE sqlDB
SELECT DENSE_RANK( ) OVER(ORDER BY height DESC)[키큰순위],
name, addr, height
FROM userTbl
ORDER BY height DESC
//동순위처리 - 건너뛴다. 1등이 세명이면 다음 순위는 4등
USE sqlDB
SELECT RANK( ) OVER(ORDER BY height DESC)[키큰순위],
name, addr, height
FROM userTbl
ORDER BY height DESC
//그룹개수로 나눈다. 키큰순으로 두개의 그룹으로 나누기 나머지는 존재하면 정렬의 앞으로 배정
만약에 7명을 두 반으로 나눈다면 A반에 4명 B반에 3명으로 나누어진다.
USE sqlDB
SELECT NTILE(2) OVER(ORDER BY height DESC) [반번호],
name, addr, height
FROM userTbl
ORDER BY height DESC
USE sqlDB
SELECT NTILE(4) OVER(ORDER BY height DESC) [반번호],
name, addr, height
FROM userTbl
ORDER BY height DESC
3.조인
(1)조인개념
: 두 개 이상의 테이블을 서로 묶어서 하나의 결과집합으로 만들어 내는 것
(2)inner join
inner join:조인 중 가장 많이 사용된다.일반적으로 조인이라고 하면 inner join을 말한다.
1대 다의 관계에서 많이 사용한다.
특징 : 한쪽이라도 데이터가 없는 경우에는 나오지 않는다.
구문형식
select <열목록>
from <첫 번째 테이블>
inner join <두 번째 테이블>
on <조인될 조건>
[where 검색조건]
USE sqlDB
SELECT *
FROM buyTbl
INNER JOIN userTbl
ON buyTbl.userid = userTbl.userid //조인조건
WHERE buyTbl.userid = 'LCS' //조인된 후의 조건
//문제가 있는 쿼리 - 어떤 테이블의 컬럼인지를 표시하지 않았기 때문에 오류가 발생한다.
//테이블이름.컬럼형식으로 적어야 컬럼이 특정될 수 있다.
SELECT userid, name, prodName, addr, mobile1 + mobile2 AS [연락처]
FROM buyTbl
INNER JOIN userTbl
ON buyTbl.userid = userTbl.userid
SELECT buyTbl.userid, name, prodName, addr, mobile1 + mobile2
FROM buyTbl
INNER JOIN userTbl
ON buyTbl.userid = userTbl.userid
SELECT buyTbl.userid, name, prodName, addr, mobile1 + mobile2 AS [연락처]
FROM buyTbl, userTbl
WHERE buyTbl.userid = userTbl.userid
SELECT buyTbl.userid, userTbl.name, buyTbl.prodName,
userTbl.addr, userTbl.mobile1 + userTbl.mobile2 AS [연락처]
FROM buyTbl
INNER JOIN userTbl
ON buyTbl.userid = userTbl.userid
//얼리어싱을 이용한 컬럼 특정 - 코드의 간결화에 좋다.
SELECT B.userid, U.name, B.prodName, U.addr, U.mobile1 + U.mobile2 AS [연락처]
FROM buyTbl B
INNER JOIN userTbl U
ON B.userid = U.userid
SELECT B.userid, U.name, B.prodName, U.addr, U.mobile1 + U.mobile2 AS [연락처]
FROM buyTbl B
INNER JOIN userTbl U
ON B.userid = U.userid
WHERE B.userid = 'LCS'
SELECT U.userid, U.name, B.prodName, U.addr, U.mobile1 + U.mobile2 AS [연락처]
FROM userTbl U
INNER JOIN buyTbl B
ON U.userid = B.userid
WHERE B.userid = 'LCS'
//전체회원의 목록을 보고자 했으나 구매한 회원의 목록만 나오게 된다. 이것이 INNER JOIN의 특징이다.
SELECT U.userid, U.name, B.prodName, U.addr, U.mobile1 + U.mobile2 AS [연락처]
FROM userTbl U
INNER JOIN buyTbl B
ON U.userid = B.userid
ORDER BY U.userid
//구매한 적이 있는 회원을 중복제거해서 출력하면 아래와 같다.
SELECT DISTINCT U.userid, U.name, U.addr
FROM userTbl U
INNER JOIN buyTbl B
ON U.userid = B.userid
ORDER BY U.userid
SELECT U.userid, U.name, U.addr
FROM userTbl U
WHERE EXISTS (
SELECT *
FROM buyTbl B
WHERE U.userid = B.userid )
(3)3개 컬럼의 내부조인(inner join)
학생 - 학생이 가입한 클럽
클럽 - 학생이 가입한 클럽
USE sqlDB
CREATE TABLE stdTbl //학생테이블
( stdName nvarchar(10) NOT NULL PRIMARY KEY,
addr nchar(4) NOT NULL
)
GO
CREATE TABLE clubTbl //클럽테이블
( clubName nvarchar(10) NOT NULL PRIMARY KEY,
roomNo nchar(4) NOT NULL
)
GO
CREATE TABLE stdclubTbl //학생과 가입한 클럽
( num int IDENTITY NOT NULL PRIMARY KEY,
stdName nvarchar(10) NOT NULL
FOREIGN KEY REFERENCES stdTbl(stdName),
clubName nvarchar(10) NOT NULL
FOREIGN KEY REFERENCES clubTbl(clubName),
)
GO
INSERT INTO stdTbl VALUES ('박지성','서울')
INSERT INTO stdTbl VALUES ('박주영','경기')
INSERT INTO stdTbl VALUES ('조재진','충북')
INSERT INTO stdTbl VALUES ('이천수','인천')
INSERT INTO stdTbl VALUES ('안정환','강원')
GO
INSERT INTO clubTbl VALUES ('수영','101호')
INSERT INTO clubTbl VALUES ('바둑','102호')
INSERT INTO clubTbl VALUES ('축구','103호')
INSERT INTO clubTbl VALUES ('봉사','104호')
GO
INSERT INTO stdclubTbl VALUES ('박지성','바둑')
INSERT INTO stdclubTbl VALUES ('박지성','축구')
INSERT INTO stdclubTbl VALUES ('조재진','축구')
INSERT INTO stdclubTbl VALUES ('이천수','축구')
INSERT INTO stdclubTbl VALUES ('이천수','봉사')
INSERT INTO stdclubTbl VALUES ('안정환','봉사')
GO
//3개컬럼의 조인
SELECT S.stdName, S.addr, C.clubName, C.roomNo
FROM stdTbl S
INNER JOIN stdclubTbl SC
ON S.stdName = SC.stdName
INNER JOIN clubTbl C
ON SC.clubName = C.clubName
ORDER BY S.stdName
SELECT C.clubName, C.roomNo, S.stdName, S.addr
FROM stdTbl S
INNER JOIN stdclubTbl SC
ON SC.stdName = S.stdName
INNER JOIN clubTbl C
ON SC.clubName = C.clubName
ORDER BY C.clubName
(4)outer join
-내부조인은 조건이 만족되지 않는 행은 출력되지 않지만
외부조인은 조건인의 조건에 만족되지 않는 행까지 포함해서 출력한다.
//왼쪽 테이블은 조인조건을 만족하지 않더라도 출력해라
USE sqlDB
SELECT U.userid, U.name, B.prodName, U.addr, U.mobile1 + U.mobile2 AS [연락처]
FROM userTbl U
LEFT OUTER JOIN buyTbl B
ON U.userid = B.userid
ORDER BY U.userid
/* 모드를 80으로 변환해야 실행됨.
SELECT U.userid, U.name, B.prodName, U.addr, U.mobile1 + U.mobile2
FROM userTbl U, buyTbl B
WHERE U.userid *= B.userid
ORDER BY U.userid
*/
SELECT U.userid, U.name, B.prodName, U.addr, U.mobile1 + U.mobile2 AS [연락처]
FROM buyTbl B
RIGHT OUTER JOIN userTbl U
ON U.userid = B.userid
ORDER BY U.userid
SELECT U.userid, U.name, B.prodName, U.addr, U.mobile1 + U.mobile2 AS [연락처]
FROM userTbl U
LEFT OUTER JOIN buyTbl B
ON U.userid = B.userid
WHERE B.prodName IS NULL
ORDER BY U.userid
SELECT U.userid, U.name, U.addr, U.mobile1 + U.mobile2 AS [연락처]
FROM userTbl U
WHERE NOT EXISTS (
SELECT *
FROM buyTbl B
WHERE U.userid = B.userid )
ORDER BY U.userid
(5)세개 컬럼의 외부조인
USE sqlDB
SELECT S.stdName, S.addr, C.clubName, C.roomNo
FROM stdTbl S
LEFT OUTER JOIN stdclubTbl SC
ON S.stdName = SC.stdName
LEFT OUTER JOIN clubTbl C
ON SC.clubName = C.clubName
ORDER BY S.stdName
SELECT C.clubName, C.roomNo, S.stdName, S.addr
FROM stdTbl S
LEFT OUTER JOIN stdclubTbl SC
ON SC.stdName = S.stdName
RIGHT OUTER JOIN clubTbl C
ON SC.clubName = C.clubName
ORDER BY C.clubName
//풀조인
SELECT S.stdName, S.addr, C.clubName, C.roomNo
FROM stdTbl S
FULL JOIN stdclubTbl SC
ON S.stdName = SC.stdName
FULL JOIN clubTbl C
ON SC.clubName = C.clubName
ORDER BY S.stdName
(6)크로스 조인(cross join-상호조인)
-한 쪽 테이블의 모든 행들과 다른 쪽 테이블의 모든 행을 조인시키는 기능
-주로 테스트용도로 대용량의 테이블을 생성시에 사용된다.
USE sqlDB
SELECT *
FROM buyTbl
CROSS JOIN userTbl
SELECT *
FROM buyTbl , userTbl
12만개 * 3만개 = 36억 건이 만들어지게된다.
USE AdventureWorks
SELECT CAST(COUNT(A) AS CHAR(10)) + COUNT_BIG(*) AS [데이터개수]
FROM Sales.SalesOrderDetail A
CROSS JOIN Sales.SalesOrderHeader B
(7)self join
-자기 자신과 자기자신이 조인한다는 의미
-주로 조직도 등에서 많이 활용된다.
-두개의 테이블이 존재한다고 생각하면 간단하다.
//우대리의 상관인 이부장이 소속된 부서는 어디인가?
USE sqlDB
SELECT A.emp AS [부하직원] , B.emp AS [직속상관], B.department AS [직속상관부서]
FROM empTbl A
INNER JOIN empTbl B
ON A.manager = B.emp
WHERE A.emp = '우대리'
USE AdventureWorks
SELECT E.EmployeeID AS [직원번호],
E.ManagerID AS [관리자번호],
C.FirstName + ' ' + C.MiddleName + ' ' + C.LastName AS [관리자이름],
C.EmailAddress AS [관리자메일]
FROM HumanResources.Employee E -- 직원테이블
INNER JOIN HumanResources.Employee M --관리자테이블
ON E.ManagerID = M.EmployeeID
/* Where 구문에 있는 직원테이블의 2번직원의 관리자(6번)과 같은 조건
(결국, HumanResources.Employee 테이블의 직원번호 6번 행이 추출됨 */
INNER JOIN Person.Contact C
ON M.ContactID = C.ContactID
/* 위의 조인에서 추출된 행(직원번호 6번행)의 ContactID와 Person.Contact테이블의 ContactID가 같은 것을 추출함
--> 결국 [그림 6-45]와 같아짐 */
WHERE E.EmployeeID = 2
(8)union,union all
-두 쿼리의 결과를 행으로 합치는 것을 말한다.
union - 중복 제거
union all - 중복허용
주의사항 :
두 개의 select 문장의 컬럼은 열의 개수가 같고
데이터 형식도 각 열단위로 호환되는 데이터 형식이어야 한다.
USE sqlDB
SELECT stdName, addr FROM stdTbl
UNION ALL
SELECT clubName, roomNo FROM clubTbl
4.SQL프로그래밍
(1)if...else
if <부울 표현식>
begin
sql문장
end
else
begin
sql문장
end
DECLARE @var1 INT -- @var1 변수 선언
SET @var1=100 -- 변수에 값 대입
IF @var1 = 100 -- 만약 @var1 이 100이라면,
BEGIN
PRINT '@var1 이100 이다'
END
ELSE
BEGIN
PRINT '@var1 이100이아니다.'
END
(2)case
case
when(조건1) then 값1
when(조건2) then 값2
DECLARE @point INT, @credit NCHAR(1)
SET @point = 100
SET @credit =
CASE
WHEN (@point >= 90) THEN 'A'
WHEN (@point >= 80) THEN 'B'
WHEN (@point >= 70) THEN 'C'
WHEN (@point >= 60) THEN 'D'
ELSE 'F'
END
PRINT '취득점수==> ' + CAST(@point AS NCHAR(3))
PRINT '학점==> ' + @credit
//회원관리
USE master
RESTORE DATABASE sqlDB FROM DISK = 'C:\sqlDB.bak' WITH REPLACE
USE sqlDB
SELECT userid, sum(price*amount) AS [총구매액]
FROM buyTbl
GROUP BY userid
ORDER BY sum(price*amount) DESC
SELECT B.userid, U.name, sum(price*amount) AS [총구매액]
FROM buyTbl B
INNER JOIN userTbl U
ON B.userid = U.userid
GROUP BY B.userid, U.name
ORDER BY sum(price*amount) DESC
SELECT B.userid, U.name, sum(price*amount) AS [총구매액]
FROM buyTbl B
RIGHT OUTER JOIN userTbl U //유저는 모두 출력되도록 하기 위해서 외부조인을 사용하였다.
ON B.userid = U.userid
GROUP BY B.userid, U.name
ORDER BY sum(price*amount) DESC
SELECT U.userid, U.name, sum(price*amount) AS [총구매액]
FROM buyTbl B
RIGHT OUTER JOIN userTbl U
ON B.userid = U.userid
GROUP BY U.userid, U.name
ORDER BY sum(price*amount) DESC
SELECT U.userid, U.name, sum(price*amount) AS [총구매액],
CASE
WHEN (sum(price*amount) >= 2000) THEN N'최우수고객'
WHEN (sum(price*amount) >= 1000) THEN N'우수고객'
WHEN (sum(price*amount) >= 1 ) THEN N'일반고객'
ELSE N'유령고객'
END AS [고객등급]
FROM buyTbl B
RIGHT OUTER JOIN userTbl U
ON B.userid = U.userid
GROUP BY U.userid, U.name
ORDER BY sum(price*amount) DESC
(3)while과 break,continue,return
DECLARE @i INT -- 1에서100까지증가할변수
DECLARE @hap BIGINT -- 더한값을누적할변수
SET @i = 1
SET @hap = 0
WHILE (@i <= 100)
BEGIN
SET @hap = @hap + @i
SET @i = @i + 1
END
PRINT @hap
DECLARE @i INT -- 1에서100까지증가할변수
DECLARE @hap BIGINT -- 더한값을누적할변수
SET @i = 1
SET @hap = 0
WHILE (@i <= 100)
BEGIN
IF (@i % 7 = 0)
BEGIN
PRINT '7의배수: ' + CAST (@i AS NCHAR(3))
SET @i = @i + 1
CONTINUE
END
SET @hap = @hap + @i
IF (@hap > 1000) BREAK
SET @i = @i + 1
END
PRINT '합계=' + CAST(@hap AS NCHAR(10))
DECLARE @i INT -- 1에서100까지증가할변수
DECLARE @hap BIGINT -- 더한값을누적할변수
SET @i = 1
SET @hap = 0
WHILE (@i <= 100)
BEGIN
IF (@i % 7 = 0)
BEGIN
PRINT '7의배수: ' + CAST (@i AS NCHAR(3))
SET @i = @i + 1
CONTINUE
END
SET @hap = @hap + @i
IF (@hap > 1000) GOTO endprint
SET @i = @i + 1
END
endprint:
PRINT '합계=' + CAST(@hap AS NCHAR(10))
(4)TRY...CATCH
SQL 2005서버에서 처음으로 지원한다.
begin try
//SQL문장
end try
begin catch
//예외가 발생한 경우의 처리
end catch
USE sqlDB
BEGIN TRY
INSERT INTO userTbl VALUES('AJH', '우주희', 1988, '서울', NULL, NULL, 170)
PRINT N'정상적으로입력되었다.'
END TRY
BEGIN CATCH
PRINT N'오류가발생했다.'
END CATCH
BEGIN TRY
INSERT INTO userTbl VALUES('AJH', '우주희', 1988, '서울', NULL, NULL, 170)
PRINT N'정상적으로입력되었다.'
END TRY
BEGIN CATCH
PRINT N'***오류가발생했다***'
PRINT N'오류번호: '
PRINT ERROR_NUMBER()
PRINT N'오류메시지: '
PRINT ERROR_MESSAGE()
PRINT N'오류상태코드: '
PRINT ERROR_STATE()
PRINT N'오류심각도: '
PRINT ERROR_SEVERITY()
PRINT N'오류발생행번호: '
PRINT ERROR_LINE()
PRINT N'오류발생프로시저/트리거: '
PRINT ERROR_PROCEDURE()
END CATCH