MSSQL Implementation Written by TransactSQL n GRANT DENY
MS-SQL Implementation 강의 노트 Written by 남현주
• Transact-SQL 구성요소 n 데이터 제어 언어 -GRANT, DENY, REVOKE 문 (Data Control Language, DCL) n 데이터 정의 언어 -CREATE, ALTER, DROP 문 (Data Definition Language, DDL) n 데이터 조작 언어 -SELECT, INSERT, UPDATE, DELETE (Data Manipulation Language, DML) n 변수, 연산자, 함수, 처리 제어 언어, 주석
데이터베이스 객체 • 데이타베이스 객체 이름 붙이기(4 part name) Server_name. database_name. owner_name. object_na me Ex) select * from instructor 1. pubs. dbo. titles select * from titles 중간의 값만 표기를 생략할 경우는. 으로 표시
• 컬럼에 대한 처리 예) SELECT * FROM titles – titles테이블의 전체 자료 가져오기 SELECT title_id, title, price, pub_id FROM titles – 테이블의 일부 자료만 가져오기 SELECT title_id, pub_id, price, title FROM titles – 원하는 컬럼 순으로 가져오기
예) SELECT ‘책 번호: ’, title_id, pub_id, price, title FROM titles – 임의의 컬럼 만들기 SELECT Title_no = title_id, [출판사 번호] = pub_id, price, title FROM titles – 컬럼의 제목 바꾸기(1) SELECT title_id as Title_no , pub_id as ‘출판사 번호’, price, title FROM titles – 컬럼의 제목 바꾸기(2) SELECT title_id Title_no , pub_id [출판사 번호], price, title FROM titles – 컬럼의 제목 바꾸기(3)
• 데이터 변경 SELECT title_id as Title_no, pub_id as [출판사 번호], price, Convert(char(30), title) as [줄인 제목] FROM titles – 자료를 변형시킴(1) SELECT title_id as Title_no, pub_id as ‘출판사 번호’, price * 1. 1 as ‘가격(부가세 포함)’ FROM titles – 자료를 변형시킴(2) * 주의 : SELECT 10/3 은?
날짜 함수) GETDATE() – 현재의 날짜와 시간 SELECT CONVERT(varchar(30), GETDATE(), 102) DATEADD() – datepart부분에 지정한 숫자 값을 더한다. SELECT DATEADD(DD, 100, GETDATE()) – 오늘로부터 100일 후는? (SELECT GETDATE()) + 100 는 가능할까요? )
* NULL SELECT title_id, price FROM titles WHERE price IS NULL 주의) SELECT title_id, price FROM titles WHERE price = NULL
* 정 렬 SELECT title_id, qty FROM sales ORDER BY title_id, qty 주의) SELECT title_id, qty FROM sales ORDER BY title_id, qty DESC
*범 위 SELECT title_id, qty FROM sales WHERE qty BETWEEN 10 AND 20 (WHERE qty >= 10 AND qty <= 20) *목 록 SELECT title_id, qty FROM sales WHERE title_id IN (‘BU 1032’, ’BU 1111’, ’MC 3021’) (WHERE title_id =‘BU 1032’ OR title_id =’BU 1111’ OR title_id =’MC 3021’)
* 문자열 비교 SELECT title_id, title FROM titles WHERE title LIKE ‘%computer%’ 패턴 : % , _ , [ ] , [ ^ ]
LIKE와 패턴 매칭 예) 표현식 해당되는 것 해당되지 않는 것 book_ books, booka, booky book, booked book% book, books, booked abook, atbooks, adbooked %book% abook, abooks, book brook, brooks [st]ing sing, ting ving, k, v, vin [b-f]ing bing, cing, ding, eing , fing Mike, Many, Mickey aing, b, f, ging M[^c]% Mc. Donald, Mc. Athur, M, Mc
/* 성능향상을 위한 제안 */. NOT 연산자를 사용하지 말 것 SELECT title_id, qty FROM sales WHERE qty NOT BETWEEN 10 AND 20. 연산자 앞에는 컬럼만 오게 할 것 SELECT title_id, price FROM titles WHERE price * 1. 1 < 20. 00. 적절한 ( )와 띄어쓰기 : 가독성을 좋게한다.
* GROUP BY/ HAVING (조건) : SELECT문 – WHERE (예선탈락) GROUP BY – HAVING (본선탈락) SELECT title_id, sum(qty) FROM sales WHERE qty < 40 GROUP BY title_id HAVING sum(qty) >= 30
* GROUP BY ALL : WHERE절에서 제외된 것도 결과에 포함 시킬 수 있다. SELECT title_id, sum(qty) FROM sales WHERE qty > 40 GROUP BY ALL title_id
* COMPUTE/ COMPUTE BY… SELECT type, title_id, price FROM titles ORDER BY type COMPUTE avg(price) BY type 그러나, 결과 값이 새로운 행으로 나타나기 때문에 즉, 테이블의 구조를 깨어지게 하므로 ROLLUP을 사 용하는 것이 낫다.
* ROLLUP 과 CUBE 가지고 있는 데이터를 분석할 용도로 쓰일 수 있다. SELECT type, pub_id, AVG(price) FROM titles GROUP BY type, pub_id WITH ROLLUP
INNER JOIN) 가장 일반적인 JOIN이다. 디폴트임 예) SELECT title, price, pub_name FROM titles INNER JOIN publishers ON titles. pub_id = publishers. pub_id Equi. JOIN) 전체 컬럼을 가져와서 키가 되는 컬럼이 중복되게 하는 것
예) SELECT t. title_id, qty, title FROM titles t LEFT OUTER JOIN sales s ON t. title_id = s. title_id (T-SQL 문법 SELECT t. title_id, qty, title FROM titles t, sales s WHERE t. title_id *= s. title_id )
l ANSI 문법과 T-SQL 문법의 차이 팔린 적이 없는 책만 보고싶다면…? SELECT FROM ON WHERE t. title_id, qty, titles t LEFT OUTER JOIN sales s t. title_id = s. title_id qty IS NULL SELECT t. title_id, qty, title FROM titles t , sales s WHERE t. title_id *= s. title_id AND qty IS NULL
작가가 두 명 이상인 책 목록을 보고 싶다고 할 경우, 다음과 같이 자신을 조인할 수 있다. SELECT t 1. title_id, t 1. au_id, t 2. au_id FROM titleauthor t 1, titleauthor t 2 셀프 조인은 반드시 알리아스를 써야한다. WHERE t 1. title_id = t 2. title_id AND t 1. au_id < t 2. au_id ORDER BY t 1. title_id
• 부 질의(subquery) SELECT 혹은 INSERT, UPDATE, DELETE 문 안 에 들어가 있는 SELECT를 부질의라고 부른다. 쿼 리를 중첩하면 많은 편리함을 제공한다. SELECT title FROM titles WHERE title_id IN (SELECT title_id FROM sales)
• 상관 부 질의(Correlated subquery) SELECT stor_id, title_id, qty FROM sales s WHERE qty = (SELECT MAX(qty) FROM sales s 2 WHERE s 2. stor_id = s. stor_id) ORDER BY stor_id, title_id 가급적 부 질의와 상관 부 질의는 사용하지 않는게 성능향상을 위해 좋다.
• DELETE 1. DELETE 문의 기본 문법 예) DELETE t 1 WHERE id = 3 2. 트랜잭션에 대한 기초 개념 예) BEGIN TRAN DELETE t 1 SELECT * FROM t 1 ROLLBACK TRAN -- OR COMMIT TRAN
3. 다른 테이블을 기반으로 지우기 예) BEGIN TRAN DELETE t 2 FROM t 1 JOIN t 2 ON t 1. id = t 2. id SELECT * FROM t 1 SELECT * FROM t 2 COMMIT TRAN --ROLLBACK TRAN
4. TRUNCATE TABLE WHERE절 없이 모두 지울 때 사용 DELETE 트리거가 없는 테이블에서만 수행 예) begin tran truncate table test select * from test rollback
• UPDATE 1. UPDATE 문의 기본 문법 예) UPDATE authors SET authors. au_fname = 'Annie' WHERE au_fname = 'Anne' 2. 다른 테이블을 기반으로 수정하기 예) UPDATE titles SET ytd_sales = titles. ytd_sales + sales. qty FROM titles, sales WHERE titles. title_id = sales. title_id AND sales. ord_date = (SELECT MAX(sales. ord_date) FROM sales)
컬럼 레벨 제약 예) CREATE TABLE authors ( au_id int NOT NULL CONSTRAINT au_PK PRIMARY KEY, firstname char(30) NOT NULL, lastname char(30) NOT NULL, status char(10) NOT NULL CONSTRAINT cat_CHK CHECK(staus IN (‘CONTRACT’, ‘EMPLOY EE’)) )
테이블 레벨 제약 예) CREATE TABLE comp. Key( sale. Date smalldatetime not null , seq tinyint not null , qty tinyint not null , CONSTRAINT PKcomp. Key PRIMARY KEY (sales. Date, seq) )
* DEFAULT 구현의 2가지 방법 1. DEFAULT 제약 예) CREATE TABLE DFtest( city CHAR(10) NOT NULL DEFAULT '서울') INSERT DFtest VALUES (DEFAULT) 2. 절차적 방법의 DEFAULT 지울 때… 예) create default DFcity 먼저 unbind 한 후에, 디 폴트 삭제 as ‘Seoul’ go sp_bindefault DFcity, 't 1. city'
• RULE과 CHECK 제약 RULE은 절차적 방법, CHECK은 서술적 방법 1. CHECK제약 예) CREATE TABLE check. Exam( city char(10) CHECK ( city in ('서울', '대전', '강릉', '태백')) ) 2. RULE 예) CREATE RULE r. City AS @city in ('서울', '대전', '강릉', '태백')
4. IF … ELSE문 IF 조건 조건이 맞을 때 취할 내용 ELSE 조건이 맞지 않을 때 취할 내용 5. WHILE, BREAK, CONTINUE WHILE 조건 --1) BEGIN IF 조건 BREAK --2) ELSE CONTINUE --3) END --4)
6. EXEC(UTE) 스토어드 프로시저 실행시키기 SQL 문장을 다이나믹 하게 변화시키기 예) DECLARE @sql VARCHAR(255) SET @sql = ‘SELECT COUNT(*) FROM’ SET @sql = @sql + ‘ titles’ (응용 예, combobox 1. text) EXEC(@sql) SELECT COUNT(*) FROM titles
7. SET 문장들 SET IDENTITY_INSERT ON (강제로 값을 입력할 수 있다. ) SET NOCOUNT ON/OFF (메시지 ON/OFF) SET ROWCOUNT n (행의 제한) SELECT TOP n 과 내용의 차이는 없지만, INSERT, UPDATE, DELETE문에서도 적용된다.
3. 뷰의 생성 예) CREATE VIEW titleview AS SELECT title, au_ord, au_lname, price, ytd_sales, pub_id FROM authors a JOIN titleauthor ta ON (a. au_id = ta. au_id) JOIN titles t ON (t. title_id = ta. title_id) 참조 SELECT * FROM titleview * 뷰의 데이터 수정 뷰가 참조하는 기본 테이블 중 하나만 영향을 받으면, 뷰를 업데이트할 수 있다. 예) UPDATE titleview SET price = price * 1. 10 WHERE pub_id = '0736'
4. @@TRANCOUNT와 중첩 트랜잭션 T-SQL 확장 : 중첩 트랜잭션 허용 ANSI 92 : 중첩 트랜잭션 비허용 0 – out tran , 1 이상 – in tran COMMIT은 짝은 바로 위의 BEGIN TRAN, ROLLBACK 짝은 맨 처음의 BEGIN TRAN (즉, ROLLBACK 은 처음으로 전부 다 롤백 시킴) Begin tran Update… Select @@trancount Begin tran Update … Select @@trancount Commit tran Select @@trancount
5. SAVE TRANSACTION 지금까지의 트랜잭션을 임시 저장하는 것 SAVE TRAN은 @@TRANCOUNT를 증가시키지는 않는다. BEGIN TRAN … SAVE TRAN savepoint … … ROLLBACK TRAN savepoint COMMIT TRAN
5. 트랜잭션 고립화 수준 트랜잭션을 처리할 때 잠금의 수준을 어떻게 결정할 것 인지를 지정하는 것이다. 1. READ COMMITTED (level 1) default dirty read를 금지 2. READ UNCOMMITTED (level 0) Dirty read를 하겠다고 선언(no lock) 3. REPEATABLE READ(level 2) 트랜잭션이 끝날 때까지 잠금을 4. SERIALIZABLE phantom read 금지 * 설정방법… SET TRANSACTION ISOLATION LEVEL (books online 참고)
• 고려할 점 1. 뷰와 프로시저를 이용한 보안 2. 브로큰 체인 lusia. table 2 lusia. table 1 lusia. view 1 maria. view 2 pierre
• 인덱스와 관련된 내용들… 1. Show. Plan set showplan_text on set showplan_all on (QA) Show Query Plan 아이콘 set statistics io on set statistics time on 2. Optimizer Hint : 색인의 사용여부를 수동으로 결정함
8. 스토어드 프로시저의 RECOMPILE : 색인을 사용하지 말아야 할 경우, 즉 ad hoc 쿼리의 사 용이 더 빠른 경우 사용한다. 방법 3가지) 1. CREATE PROC p_name @ID INT… WITH RECOMPILE AS… 2. EXEC p_name 10000 WITH RECOMPILE 3. Sp_recompile table 1
- Slides: 107