151001 자주 사용하는 SQL 정리
01 Oct 2015
- 1. 서론
- 2. DATABASE, TABLE 생성, 데이타 INSERT
- 3. 데이타 검색, 조작
- 3-1. 컬럼정해서 SELECT
- 3-2. 범위정해서 SELECT
- 3-3. IN 그룹안에서 조건걸어서 SELECT
- 3-4. 부정절 조건으로 SELECT
- 3-5. LIKE 를 이용한 패턴 SELECT
- 3-6. LIKE를 이용한 패턴 SELECT
- 3-7. 평균값을 구함
- 3-8. 그룹핑하고 카운트를 구함
- 3-9. 그룹핑하고 카운트를 구하며, 그 그룹의 조건으로 필터링
- 3-10. IN 그룹을 SELECT쿼리로 중첩쿼리 만들어서 사용.
- 3-11. 명시적인 INNER JOIN
- 3-12. 암시적인 INNER JOIN
- 3-13. 암시적인 INNER JOIN 에 컬럼이름 커스토마이즈
- 3-14. 명시적인 OUTER JOIN
- 3-15. 두 결과를 병합 UNION
- 3-16. 스키마가 똑같은 테이블을 만들어서 모든내용 복사
- 3-17. 데이타 업데이트
- 3-18. 데이타 삭제
- 3-19. 테이블에서 컬럼추가, 컬럼삭제
- 3-20. 저장프로시저 생성/실행/삭제
- 3-21. 트렌젝션을 설정하고 도중에 실패한 경우 롤백까지
- 3-22. 인덱스 생성/확인/삭제
1. 서론
주로 클라개발을 해서 SQL문을 항상 대강 알고 사용하다가 요즘 시간이 좀 나서 자주쓰는 구문 중심으로 제가 참고할 목적으로 정리해 봤습니다. 얄팍하게 SQL을 익히기에 마침 좋은 책이 한권 있어서 따라해가며 실습했고 저는 SQL SERVER 에서 실습했습니다. SQL SERVER에서 실습했지만 ANSI SQL을 주로 사용해서 다른 DBMS에서도 실습하는데 큰 문제가 없을것 같습니다.
하루 10분씩 핵심만 골라 마스터하는 SQL 핸드북
http://www.yes24.com/24/goods/2509957
짧고 간결하게 좋은책 써준 Ben Forta 님께 감사를 드립니다.
실습은 크게 3단계로 이뤄집니다.
1-1. DBMS 설치, 설정, 구동
이 부분은 각각 환경구축하시고 쿼리를 테스트할 환경을 설정하면 되겠습니다. SQL SERVER 경우는 SQL SERVER를 설치하고 SSMS(SQL Server Management Studio)에서 실습했습니다. SQL SERVER 인스턴스 추가설치/삭제 하는 파워쉘 스크립트를 첨부합니다. https://github.com/HyundongHwang/SqlServerStudy/blob/master/%EC%84%9C%EB%B2%84%20%EC%9D%B8%EC%8A%A4%ED%84%B4%EC%8A%A4%20%EC%B6%94%EA%B0%80.ps1 https://github.com/HyundongHwang/SqlServerStudy/blob/master/%EC%84%9C%EB%B2%84%20%EC%9D%B8%EC%8A%A4%ED%84%B4%EC%8A%A4%20%EC%82%AD%EC%A0%9C.ps1
1-2. DATABASE 생성, TABLE 생성, 샘플데이타 INSERT
교재에서 사용하게되는 쇼핑몰 DB를 구축하는 단계입니다. DB백업파일을 RESTORE할수도 있겠지만 각 DBMS마다 조금씩 달라서 생성, 삽입 구문을 SQL스크립트로 첨부합니다. https://github.com/HyundongHwang/SqlServerStudy/blob/master/SQL%20%ED%95%B8%EB%93%9C%EB%B6%81%20%ED%85%8C%EC%9D%B4%EB%B8%94%20%EC%83%9D%EC%84%B1%2C%20%EB%8D%B0%EC%9D%B4%ED%83%80%20%EC%82%BD%EC%9E%85.sql
1-3. 본격적인 실습
이제 모든 실습준비가 끝났으므로 기본적인 SELECT부터 고급기능까지 실습해 볼수 있습니다. 전체 실습의 SQL구문과 그 출력결과를 SQL스크립트로 작성해서 첨부합니다. https://github.com/HyundongHwang/SqlServerStudy/blob/master/SQL%20%ED%95%B8%EB%93%9C%EB%B6%81%20test.sql
2. DATABASE, TABLE 생성, 데이타 INSERT
2-1. DATABASE 생성
깨끗하게 hhddb를 생성하기 위해 사용포인트를 다른DB로 옮기고 기존 DB를 삭제하고 생성
USE tempdb; DROP DATABASE hhddb; CREATE DATABASE hhddb; USE hhddb;
2-2. TABLE 생성
각 테이블 생성 Create Customers table
CREATE TABLE Customers ( cust_id char(10 ) NOT NULL , cust_name char(50 ) NOT NULL , cust_address char( 50) NULL , cust_city char(50 ) NULL , cust_state char(5 ) NULL , cust_zip char(10 ) NULL , cust_country char( 50) NULL , cust_contact char( 50) NULL , cust_email char(255 ) NULL );
Create OrderItems table ```sql CREATE TABLE OrderItems ( order_num int NOT NULL , order_item int NOT NULL , prod_id char(10 ) NOT NULL , quantity int NOT NULL , item_price decimal( 8,2 ) NOT NULL );
… … …
> 모든 테이블이 잘 생성되었는지 확인
```sql
SELECT * FROM sys.tables ;
name object_id principal_id schema_id parent_object_id type type_desc ...
----------- ----------- ------------ ----------- ---------------- ---- -----------...
Customers 245575913 NULL 1 0 U USER_TABLE ...
OrderItems 261575970 NULL 1 0 U USER_TABLE ...
Orders 277576027 NULL 1 0 U USER_TABLE ...
Products 293576084 NULL 1 0 U USER_TABLE ...
Vendors 309576141 NULL 1 0 U USER_TABLE ...
(5개 행이 영향을 받음)
2-3. 데이타 INSERT
각 테이블에 데이타 INSERT Populate Customers table
INSERT INTO Customers(cust_id , cust_name, cust_address, cust_city , cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000001' , 'Village Toys' , '200 Maple Lane' , 'Detroit' , 'MI' , '44444' , 'USA' , 'John Smith', 'sales@villagetoys.com');
INSERT INTO Customers(cust_id , cust_name, cust_address, cust_city , cust_state, cust_zip, cust_country, cust_contact)
VALUES('1000000002' , 'Kids Place' , '333 South Lake Drive' , 'Columbus' , 'OH' , '43333' , 'USA', 'Michelle Green');
INSERT INTO Customers(cust_id , cust_name, cust_address, cust_city , cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000003' , 'Fun4All' , '1 Sunny Place' , 'Muncie' , 'IN' , '42222' , 'USA' , 'Jim Jones', 'jjones@fun4all.com' );
INSERT INTO Customers(cust_id , cust_name, cust_address, cust_city , cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000004' , 'Fun4All' , '829 Riverside Drive' , 'Phoenix' , 'AZ' , '88888' , 'USA' , 'Denise L. Stephens', 'dstephens@fun4all.com');
INSERT INTO Customers(cust_id , cust_name, cust_address, cust_city , cust_state, cust_zip, cust_country, cust_contact)
VALUES('1000000005' , 'The Toy Store' , '4545 53rd Street' , 'Chicago' , 'IL' , '54545' , 'USA' , 'Kim Howard');
Populate Vendors table
INSERT INTO Vendors(vend_id , vend_name, vend_address, vend_city , vend_state, vend_zip, vend_country)
VALUES('BRS01' ,'Bears R Us', '123 Main Street','Bear Town' ,'MI', '44444', 'USA');
INSERT INTO Vendors(vend_id , vend_name, vend_address, vend_city , vend_state, vend_zip, vend_country)
VALUES('BRE02' ,'Bear Emporium', '500 Park Street','Anytown' ,'OH', '44333', 'USA');
INSERT INTO Vendors(vend_id , vend_name, vend_address, vend_city , vend_state, vend_zip, vend_country)
VALUES('DLL01' ,'Doll House Inc.', '555 High Street','Dollsville' ,'CA', '99999', 'USA');
INSERT INTO Vendors(vend_id , vend_name, vend_address, vend_city , vend_state, vend_zip, vend_country)
VALUES('FRB01' ,'Furball Inc.', '1000 5th Avenue','New York' ,'NY', '11111', 'USA');
INSERT INTO Vendors(vend_id , vend_name, vend_address, vend_city , vend_state, vend_zip, vend_country)
VALUES('FNG01' ,'Fun and Games', '42 Galaxy Road','London' , NULL, 'N16 6PS', 'England');
INSERT INTO Vendors(vend_id , vend_name, vend_address, vend_city , vend_state, vend_zip, vend_country)
VALUES('JTS01' ,'Jouets et ours', '1 Rue Amusement','Paris' , NULL, '45678', 'France');
각 테이블에 데이타 INSERT Populate Customers table
INSERT INTO Customers(cust_id , cust_name, cust_address, cust_city , cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000001' , 'Village Toys' , '200 Maple Lane' , 'Detroit' , 'MI' , '44444' , 'USA' , 'John Smith', 'sales@villagetoys.com');
INSERT INTO Customers(cust_id , cust_name, cust_address, cust_city , cust_state, cust_zip, cust_country, cust_contact)
VALUES('1000000002' , 'Kids Place' , '333 South Lake Drive' , 'Columbus' , 'OH' , '43333' , 'USA', 'Michelle Green');
INSERT INTO Customers(cust_id , cust_name, cust_address, cust_city , cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000003' , 'Fun4All' , '1 Sunny Place' , 'Muncie' , 'IN' , '42222' , 'USA' , 'Jim Jones', 'jjones@fun4all.com' );
INSERT INTO Customers(cust_id , cust_name, cust_address, cust_city , cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000004' , 'Fun4All' , '829 Riverside Drive' , 'Phoenix' , 'AZ' , '88888' , 'USA' , 'Denise L. Stephens', 'dstephens@fun4all.com');
INSERT INTO Customers(cust_id , cust_name, cust_address, cust_city , cust_state, cust_zip, cust_country, cust_contact)
VALUES('1000000005' , 'The Toy Store' , '4545 53rd Street' , 'Chicago' , 'IL' , '54545' , 'USA' , 'Kim Howard');
Populate Vendors table
INSERT INTO Vendors(vend_id , vend_name, vend_address, vend_city , vend_state, vend_zip, vend_country)
VALUES('BRS01' ,'Bears R Us', '123 Main Street','Bear Town' ,'MI', '44444', 'USA');
INSERT INTO Vendors(vend_id , vend_name, vend_address, vend_city , vend_state, vend_zip, vend_country)
VALUES('BRE02' ,'Bear Emporium', '500 Park Street','Anytown' ,'OH', '44333', 'USA');
INSERT INTO Vendors(vend_id , vend_name, vend_address, vend_city , vend_state, vend_zip, vend_country)
VALUES('DLL01' ,'Doll House Inc.', '555 High Street','Dollsville' ,'CA', '99999', 'USA');
INSERT INTO Vendors(vend_id , vend_name, vend_address, vend_city , vend_state, vend_zip, vend_country)
VALUES('FRB01' ,'Furball Inc.', '1000 5th Avenue','New York' ,'NY', '11111', 'USA');
INSERT INTO Vendors(vend_id , vend_name, vend_address, vend_city , vend_state, vend_zip, vend_country)
VALUES('FNG01' ,'Fun and Games', '42 Galaxy Road','London' , NULL, 'N16 6PS', 'England');
INSERT INTO Vendors(vend_id , vend_name, vend_address, vend_city , vend_state, vend_zip, vend_country)
VALUES('JTS01' ,'Jouets et ours', '1 Rue Amusement','Paris' , NULL, '45678', 'France');
...
...
...
3. 데이타 검색, 조작
3-1. 컬럼정해서 SELECT
내림차순으로 정렬
USE hhddb;
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC;
prod_id prod_price prod_name
---------- --------------------------------------- ----------------------------------------
BR03 11.99 18 inch teddy bear
RYL01 9.49 King doll
RYL02 9.49 Queen doll
BR02 8.99 12 inch teddy bear
BR01 5.99 8 inch teddy bear
RGAN01 4.99 Raggedy Ann
BNBG01 3.49 Fish bean bag toy
BNBG02 3.49 Bird bean bag toy
BNBG03 3.49 Rabbit bean bag toy
3-2. 범위정해서 SELECT
SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10 ;
prod_name prod_price
---------------------------------------- ---------------------------------------
8 inch teddy bear 5.99
12 inch teddy bear 8.99
King doll 9.49
Queen doll 9.49
3-3. IN 그룹안에서 조건걸어서 SELECT
SELECT prod_name, prod_price
FROM Products
WHERE vend_id IN ('DLL01' , 'BRS01' )
ORDER BY prod_name;
prod_name prod_price
---------------------------------------- ---------------------------------------
12 inch teddy bear 8.99
18 inch teddy bear 11.99
8 inch teddy bear 5.99
Bird bean bag toy 3.49
Fish bean bag toy 3.49
Rabbit bean bag toy 3.49
Raggedy Ann 4.99
(7개 행이 영향을 받음)
3-4. 부정절 조건으로 SELECT
SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_id;
prod_name
----------------------------------------
8 inch teddy bear
12 inch teddy bear
18 inch teddy bear
King doll
Queen doll
(5개 행이 영향을 받음)
3-5. LIKE 를 이용한 패턴 SELECT
와일드카드 문자 이용
SELECT prod_name
FROM Products
WHERE prod_name LIKE 'F%y';
prod_name
----------------------------------------
Fish bean bag toy
3-6. LIKE를 이용한 패턴 SELECT
와일드카드문자 + 정규식이용
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%';
cust_contact
----------------------------------------
John Smith
Michelle Green
Jim Jones
(3개 행이 영향을 받음)
3-7. 평균값을 구함
AVG 함수 사용
SELECT AVG (prod_price) AS avg_price
FROM Products;
avg_price
---------------------------------------
6.823333
3-8. 그룹핑하고 카운트를 구함
GROUP BY COUNT 함수 사용
SELECT vend_id, COUNT(*) as num_prod
FROM Products
GROUP BY vend_id;
vend_id num_prod
---------- -----------
BRS01 3
DLL01 4
FNG01 2
3-9. 그룹핑하고 카운트를 구하며, 그 그룹의 조건으로 필터링
GROUP BY COUNT 함수 HAVING
- HAVING은 그룹에 대한 WHERE라고 생각하면 된다.
SELECT vend_id, COUNT(*) as num_prod
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT (*) >= 2;
vend_id num_prod
---------- -----------
BRS01 3
FNG01 2
3-10. IN 그룹을 SELECT쿼리로 중첩쿼리 만들어서 사용.
SELECT cust_id
FROM Orders
WHERE order_num IN
(
SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01'
);
cust_id
----------
1000000004
1000000005
3-11. 명시적인 INNER JOIN
SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors. vend_id = Products .vend_id;
vend_name prod_name prod_price
---------------------------------------- ---------------------------------------- ---------------------------------------
Doll House Inc. Fish bean bag toy 3.49
Doll House Inc. Bird bean bag toy 3.49
Doll House Inc. Rabbit bean bag toy 3.49
Bears R Us 8 inch teddy bear 5.99
Bears R Us 12 inch teddy bear 8.99
Bears R Us 18 inch teddy bear 11.99
Doll House Inc. Raggedy Ann 4.99
Fun and Games King doll 9.49
Fun and Games Queen doll 9.49
3-12. 암시적인 INNER JOIN
SELECT prod_name, vend_name, prod_price , quantity
FROM OrderItems, Products, Vendors
WHERE Products. vend_id = Vendors .vend_id
AND OrderItems .prod_id = Products.prod_id
AND order_num = 20007;
prod_name vend_name prod_price ...
---------------------------------------- ---------------------------------------- -----------...
18 inch teddy bear Bears R Us 11.99 ...
Fish bean bag toy Doll House Inc. 3.49 ...
Bird bean bag toy Doll House Inc. 3.49 ...
Rabbit bean bag toy Doll House Inc. 3.49 ...
Raggedy Ann Doll House Inc. 4.99 ...
3-13. 암시적인 INNER JOIN 에 컬럼이름 커스토마이즈
SELECT c. cust_name AS '고객이름', c .cust_contact AS '고객주소'
FROM Customers AS c, Orders AS o, OrderItems AS oi
WHERE c. cust_id = o .cust_id
AND oi .order_num = o.order_num
AND prod_id = 'RGAN01' ;
고객이름 고객주소
---------------------------------------- ----------------------------------------
Fun4All Denise L. Stephens
The Toy Store Kim Howard
3-14. 명시적인 OUTER JOIN
OUTER JOIN 은 명시적으로 JOIN기준축을 설정해 주어야 한다.
SELECT Customers. cust_id, Orders .order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers. cust_id = orders .cust_id;
cust_id order_num
---------- -----------
1000000001 20005
1000000001 20009
1000000002 NULL
1000000003 20006
1000000004 20007
1000000005 20008
3-15. 두 결과를 병합 UNION
UNION 과정에서 중복데이타는 제거된다.
(
SELECT cust_name , cust_contact
FROM Customers
WHERE cust_state IN ( 'IL', 'IN', 'MI')
)
UNION
(
SELECT *
FROM Customers
WHERE cust_name = 'Fun4All'
);
cust_id cust_name cust_address cust_city ...
---------- ---------------------------------------- ---------------------------------------- -------------...
1000000001 Village Toys 200 Maple Lane Detroit ...
1000000003 Fun4All 1 Sunny Place Muncie ...
1000000004 Fun4All 829 Riverside Drive Phoenix ...
1000000005 The Toy Store 4545 53rd Street Chicago ...
3-16. 스키마가 똑같은 테이블을 만들어서 모든내용 복사
DROP TABLE CustomersNew;
CREATE TABLE CustomersNew
(
cust_id char(10 ) NOT NULL ,
cust_name char(50 ) NOT NULL ,
cust_address char( 50) NULL ,
cust_city char(50 ) NULL ,
cust_state char(5 ) NULL ,
cust_zip char(10 ) NULL ,
cust_country char( 50) NULL ,
cust_contact char( 50) NULL ,
cust_email char(255 ) NULL
);
INSERT INTO CustomersNew
SELECT *
FROM Customers;
SELECT *
FROM CustomersNew;
(5개 행이 영향을 받음)
cust_id cust_name cust_address cust_city ...
---------- ---------------------------------------- ---------------------------------------- ----------...
1000000001 Village Toys 200 Maple Lane Detroit ...
1000000002 Kids Place 333 South Lake Drive Columbus ...
1000000003 Fun4All 1 Sunny Place Muncie ...
1000000004 Fun4All 829 Riverside Drive Phoenix ...
1000000005 The Toy Store 4545 53rd Street Chicago ...
(5개 행이 영향을 받음)
3-17. 데이타 업데이트
UPDATE Customers
SET cust_email = 'kim@thetoystore.com'
WHERE cust_id = '1000000005';
SELECT *
FROM Customers
WHERE cust_id = '1000000005';
(1개 행이 영향을 받음)
cust_id cust_name cust_address cust_city ...
---------- ---------------------------------------- ---------------------------------------- ----------...
1000000005 The Toy Store 4545 53rd Street Chicago ...
(1개 행이 영향을 받음)
3-18. 데이타 삭제
SELECT * FROM CustomersNew
WHERE cust_id = '1000000005';
cust_id cust_name cust_address cust_city ...
---------- ---------------------------------------- ---------------------------------------- ------------...
1000000005 The Toy Store 4545 53rd Street Chicago ...
...
(1개 행이 영향을 받음) ...
DELETE FROM CustomersNew
WHERE cust_id = '1000000005';
(1개 행이 영향을 받음) ...
SELECT * FROM CustomersNew
WHERE cust_id = '1000000005';
cust_id cust_name cust_address cust_city ...
---------- ---------------------------------------- ---------------------------------------- ------------...
(0개 행이 영향을 받음)
3-19. 테이블에서 컬럼추가, 컬럼삭제
테이블의 모든 컬럼보기
SELECT *
FROM INFORMATION_SCHEMA .COLUMNS
WHERE TABLE_NAME = 'Vendors';
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ...
----------------------- ------------- ----------- --------------...
hhddb dbo Vendors vend_id ...
hhddb dbo Vendors vend_name ...
hhddb dbo Vendors vend_address ...
hhddb dbo Vendors vend_city ...
hhddb dbo Vendors vend_state ...
hhddb dbo Vendors vend_zip ...
hhddb dbo Vendors vend_country ...
...
(7개 행이 영향을 받음) ...
alter table Vendors
add vend_phone char (20);
SELECT *
FROM INFORMATION_SCHEMA .COLUMNS
WHERE TABLE_NAME = 'Vendors';
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ...
----------------------- ------------- ----------- --------------...
hhddb dbo Vendors vend_id ...
hhddb dbo Vendors vend_name ...
hhddb dbo Vendors vend_address ...
hhddb dbo Vendors vend_city ...
hhddb dbo Vendors vend_state ...
hhddb dbo Vendors vend_zip ...
hhddb dbo Vendors vend_country ...
hhddb dbo Vendors vend_phone ...
...
(8개 행이 영향을 받음) ...
alter table Vendors
drop column vend_phone;
SELECT *
FROM INFORMATION_SCHEMA .COLUMNS
WHERE TABLE_NAME = 'Vendors';
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ...
----------------------- ------------- ----------- --------------...
hhddb dbo Vendors vend_id ...
hhddb dbo Vendors vend_name ...
hhddb dbo Vendors vend_address ...
hhddb dbo Vendors vend_city ...
hhddb dbo Vendors vend_state ...
hhddb dbo Vendors vend_zip ...
hhddb dbo Vendors vend_country ...
(7개 행이 영향을 받음)
3-20. 저장프로시저 생성/실행/삭제
CREATE PROCEDURE sp_new_order @cust_id CHAR( 10)
AS
DECLARE @order_num INTEGER;
SELECT @order_num = MAX (order_num)
FROM Orders ;
SELECT @order_num = @order_num + 1;
INSERT INTO Orders( order_num, order_date , cust_id)
VALUES( @order_num, GETDATE(), @cust_id );
RETURN @order_num ;
GO
SELECT * FROM sys.procedures ;
name object_id principal_id schema_id parent_object_id type type_desc ...
---------------------------------------- ----------- ------------ ----------- ---------------- ---- ---------------------------------------- ...
sp_new_order 869578136 NULL 1 0 P SQL_STORED_PROCEDURE ...
SELECT * FROM Orders ;
order_num order_date cust_id
----------- ----------------------- ----------
20005 2004-05-01 00:00:00.000 1000000001
20006 2004-01-12 00:00:00.000 1000000003
20007 2004-01-30 00:00:00.000 1000000004
20008 2004-02-03 00:00:00.000 1000000005
EXECUTE sp_new_order '1000000001';
EXECUTE sp_new_order '1000000001';
EXECUTE sp_new_order '1000000001';
SELECT * FROM Orders ;
order_num order_date cust_id
----------- ----------------------- ----------
20005 2004-05-01 00:00:00.000 1000000001
20006 2004-01-12 00:00:00.000 1000000003
20007 2004-01-30 00:00:00.000 1000000004
20008 2004-02-03 00:00:00.000 1000000005
20009 2004-02-08 00:00:00.000 1000000001
20010 2015-10-01 14:20:39.107 1000000001
20011 2015-10-01 14:20:39.113 1000000001
DROP PROCEDURE sp_new_order;
3-21. 트렌젝션을 설정하고 도중에 실패한 경우 롤백까지
이 예제에서는 작업진행하다가 중간에 Orders 테이블에서 삭제시 FK제약사항 때문에 예외가 발생하고 이 때문에 롤백됨.
BEGIN TRANSACTION ;
SAVE TRANSACTION start_point;
SELECT *
FROM OrderItems;
order_num order_item prod_id quantity item_price
----------- ----------- ---------- ----------- ---------------------------------------
20005 1 BR01 100 5.49
20005 2 BR03 100 10.99
20006 1 BR01 20 5.99
20006 2 BR02 10 8.99
20006 3 BR03 10 11.99
20007 1 BR03 50 11.49
20007 2 BNBG01 100 2.99
20007 3 BNBG02 100 2.99
20007 4 BNBG03 100 2.99
20007 5 RGAN01 50 4.49
20008 1 RGAN01 5 4.99
20008 2 BR03 5 11.99
20008 3 BNBG01 10 3.49
20008 4 BNBG02 10 3.49
20008 5 BNBG03 10 3.49
20009 1 BNBG01 250 2.49
20009 2 BNBG02 250 2.49
20009 3 BNBG03 250 2.49
DELETE OrderItems
WHERE order_num = 20005;
SELECT *
FROM OrderItems;
order_num order_item prod_id quantity item_price
----------- ----------- ---------- ----------- ---------------------------------------
20006 1 BR01 20 5.99
20006 2 BR02 10 8.99
20006 3 BR03 10 11.99
20007 1 BR03 50 11.49
20007 2 BNBG01 100 2.99
20007 3 BNBG02 100 2.99
20007 4 BNBG03 100 2.99
20007 5 RGAN01 50 4.49
20008 1 RGAN01 5 4.99
20008 2 BR03 5 11.99
20008 3 BNBG01 10 3.49
20008 4 BNBG02 10 3.49
20008 5 BNBG03 10 3.49
20009 1 BNBG01 250 2.49
20009 2 BNBG02 250 2.49
20009 3 BNBG03 250 2.49
SELECT *
FROM Orders;
order_num order_date cust_id
----------- ----------------------- ----------
20005 2004-05-01 00:00:00.000 1000000001
20006 2004-01-12 00:00:00.000 1000000003
20007 2004-01-30 00:00:00.000 1000000004
20008 2004-02-03 00:00:00.000 1000000005
20009 2004-02-08 00:00:00.000 1000000001
DELETE Orders
WHERE order_num = 20005;
(1개 행이 영향을 받음)
SELECT *
FROM Orders;
order_num order_date cust_id
----------- ----------------------- ----------
20006 2004-01-12 00:00:00.000 1000000003
20007 2004-01-30 00:00:00.000 1000000004
20008 2004-02-03 00:00:00.000 1000000005
20009 2004-02-08 00:00:00.000 1000000001
DELETE Orders
WHERE order_num = 20006;
IF @@ERROR <> 0
BEGIN
PRINT N'에러 발생함 start_point 로 롤백함...' ;
ROLLBACK TRANSACTION start_point;
END
메시지 547, 수준 16, 상태 0, 줄 22
DELETE 문이 REFERENCE 제약 조건 "FK_OrderItems_Orders"과(와) 충돌했습니다. 데이터베이스 "hhddb", 테이블 "dbo.OrderItems", column 'order_num'에서 충돌이 발생했습니다.
문이 종료되었습니다.
에러 발생함 start_point 로 롤백함...
SELECT *
FROM Orders;
order_num order_date cust_id
----------- ----------------------- ----------
20005 2004-05-01 00:00:00.000 1000000001
20006 2004-01-12 00:00:00.000 1000000003
20007 2004-01-30 00:00:00.000 1000000004
20008 2004-02-03 00:00:00.000 1000000005
20009 2004-02-08 00:00:00.000 1000000001
COMMIT TRANSACTION ;
3-22. 인덱스 생성/확인/삭제
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Products';
CREATE INDEX idx_prod_name
ON Products( prod_name);
SELECT *
FROM sys.indexes
WHERE name LIKE 'idx_%';
DROP INDEX idx_prod_name
ON Products;
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ...
-------------- ------------- ----------- -------------...
hhddb dbo Products prod_id ...
hhddb dbo Products vend_id ...
hhddb dbo Products prod_name ...
hhddb dbo Products prod_price ...
hhddb dbo Products prod_desc ...
(5개 행이 영향을 받음)
object_id name index_id type type_desc is_unique data_space_id ...
----------- -------------- ----------- ---- -------------- --------- ------------- ...
293576084 idx_prod_name 2 2 NONCLUSTERED 0 1 ...
(1개 행이 영향을 받음)
...