6 장 SQL (section 4-6)
목차 SECTION 01 SQL 의개요 1-1 SQL의역사 1-2 SQL의유형별종류 SECTION 02 데이터정의어 (DDL) 2-1 스키마 2-2 테이블생성 (CREATE TABLE) 2-3 테이블변경 (ALTER TABLE) 2-4 테이블제거 (DROP TABLE) 2-5 제약조건 SECTION 03 데이터조작어 (DML) 3-1 데이터검색 (SELECT) 3-2 데이터삽입 (INSERT) 3-3 데이터갱싞 (UPDATE) 3-4 데이터삭제 (DELETE) 2
목차 SECTION 04 뷰 4-1 뷰의생성 4-2 뷰의내부구조 4-3 뷰의수정과제거 SECTION 05 저장프로시저 5-1 저장프로시저생성하기 5-2 저장프로시저수정하기 5-3 저장프로시저제거하기 SECTION 06 트리거 6-1 트리거생성 6-2 DDL 트리거수정 3
4 뷰 (View) 뷰 (View) 뷰 ㆍ물리적인테이블에근거한논리적인가상테이블ㆍ기본테이블에서파생된객체로써기본테이블에대한하나의쿼리문 실질적으로데이터를저장하고있지않기때문 실질적으로데이터를저장하고있지않더라도사용자는마치테이블을사용하는것과동일하게뷰를사용할수있기때문 실제테이블에저장된데이터를뷰를통해서볼수있도록하는것사용자는주어진뷰를통해서기본테이블을제한적으로사용하게됨 197 Page 4
4 뷰 (View) 4-1 뷰의생성 뷰 (View) 기본형식 CREATE VIEW [ schema_name. ] view_name [ (column [,...n ] ) ] AS select_statement [ ; ] 뷰정의예 CREATE VIEW STU_VIEW30 AS SELECT 문앞에이두줄만추가하면뷰가생성 SELECT STU_ID, STU_NAME, COU_ID FROM STU_COPY WHERE COU_ID=30 5
4 뷰 (View) 뷰 (View) 생성 4-1 뷰의생성 199 Page 6
4 뷰 (View) 뷰의내용출력 4-1 뷰의생성 - SELECT * FROM 다음에테이블명대싞에뷰이름을기술 SELECT * FROM STU_VIEW30 - 출력결과 200 Page 7
4 뷰 (View) 4-2 뷰의내부구조 - SELECT 문이용하여뷰의내용출력 SELECT * FROM STU_VIEW30 - 저장프로시저를사용하여뷰의내용출력 EXEC SP_HELPTEXT STU_VIEW30 저장프로시저인 SP_HELPTEXT 를이용핚뷰의정의보기 201 Page 8
4 뷰 (View) 뷰의작동원리 4-2 뷰의내부구조 201 Page 1. 사용자가뷰에대해질의를하면 SP_HELPTEXT 저장프로시저에서뷰의형식조회 2 기본테이블에대한뷰의접근권한확인 3. 뷰에대한질의를기본테이블에대한질의로변홖 4. 기본테이블에대한질의를통해데이터를검색 5. 검색된결과출력 9
4 뷰 (View) 4-2 뷰의내부구조 뷰에 INSERT 문으로데이터추가하기 - STU_VIEW30 에행을하나추가 INSERT INTO STU_VIEW30 VALUES(200, 젂수빈, 30) - 뷰로데이터추가 203 Page 10
4 뷰 (View) 4-2 뷰의내부구조 INSERT 문으로뷰에새로운행을추가 SELECT * FROM STU_VIEW30 - 뷰로데이터조회 203 Page 11
4 뷰 (View) 4-2 뷰의내부구조 INSERT 문에의해서뷰에추가핚행이테이블에도존재확인 SELECT * FROM STU_COPY - 뷰로기본테이블에데이터추가 204 Page 12
4 뷰 (View) 4-2 뷰의내부구조 뷰의성격을파악하기위해서테이블과비교 SELECT * FROM STUDENT - 테이블과뷰의차이 STU_COPY 에추가한행의내용은 STUDENT 테이블에아무런영향을주지못한다. 두테이블은별개의저장공간에데이터를저장하고있기때문이다. 205 Page 13
4 뷰 (View) 4-3 뷰의수정과제거 뷰를만든후에뷰를수정핛때 - ALTER VIEW 문을이용하여수정 뷰가더이상필요가없을때 - DROP VIEW 문을이용 뷰수정하기 - 현재사용중인뷰를수정하려면 ALTER VIEW 를사용 - ALTER VIEW 기본형식 ALTER VIEW view_name [(column [,..n])] [WITH ENCRYPTION] AS select_statement 14
뷰수정 4 뷰 (View) 4-3 뷰의수정과제거 - 변경젂의뷰 1: 2: 3: 4: 5: - 뷰변경하기 ( 예제 ) ALTER VIEW STU_VIEW30 AS SELECT STU_ID, STU_NAME, AGE, STU_EMAIL, COU_ID FROM STU_COPY WHERE COU_ID=30 206 Page 207 Page 15
뷰의제거 4 뷰 (View) 4-3 뷰의수정과제거 - DROP VIEW 기본형식 DROP VIEW {view} [,..n] - STU_VIEW30 이란이름의뷰를삭제 DROP VIEW STU_VIEW30 - 뷰제거 SELECT * FROM STU_VIEW30 208 Page 16
5 저장프로시저 저장프로시저 ( 교재 p209) - 자주사용하는질의문을하나로묶어서저장 - 필요할때저장프로시저의이름을호출하는것만으로질의문을실행 17
5 저장프로시저 5-1 저장프로시저생성하기 저장프로시저형식 CREATE { PROC PROCEDURE } [schema_name.] procedure_name [ ; number ] [ { @parameter [ type_schema_name. ] data_type } [ VARYING ] [ = default ] [ [ OUT [ PUT ] ] ] ] [,...n ] [ WITH <procedure_option> [,...n ] ] [ FOR REPLICATION ] AS { <sql_statement> [;][...n ] <method_specifier> } [;] - EXEC 라는명령어를사용하여실행 EXEC procedure_name 18
5 저장프로시저 5-1 저장프로시저생성하기 저장프로시저생성하기 CREATE PROCEDURE usp_student AS SELECT STU_ID, STU_NAME, AGE, STU_EMAIL, COU_ID FROM STUDENT WHERE STU_EMAIL IS NOT NULL ORDER BY STU_NAME - 저장프로시저를실행 EXEC usp_student 19
5 저장프로시저 5-1 저장프로시저생성하기 저장프로시저생성하기 - 저장프로시저실행 211 Page 20
5 저장프로시저 5-2 저장프로시저수정하기 ALTER PROCEDURE - 저장프로시저수정하기 ALTER PROCEDURE usp_student AS SELECT STU_ID, STU_NAME, AGE, STU_EMAIL, COU_ID, SEX FROM STUDENT WHERE STU_EMAIL IS NOT NULL ORDER BY STU_NAME DESC EXEC usp_student 21
5 저장프로시저 5-2 저장프로시저수정하기 ALTER PROCEDURE - 저장프로시저수정및실행 212 Page 22
5 저장프로시저 5-3 저장프로시저제거하기 DROP PROCEDURE 문을사용 - 저장프로시저삭제하기 DROP PROCEDURE usp_student - 저장프로시저제거 EXEC usp_student 213 Page 23
6 트리거 ( 교재 p214) 트리거 (trigger) 의사젂적인의미 1. ( 총의 ) 방아쇠 ; =HAIR TRIGGER 2. 제동기, 제륜 ( 制輪 ) 장치 3. ( 연쇄반응 생리현상 일렦의사건등을유발하는 ) 계기, 유인, 자극 트리거 - 데이터베이스가미리정해놓은조건을만족하거나어떤작동이수행되면자동적수행 24
6 저장프로시저 6-1 트리거생성 CREATE TRIGGER 문의형식 CREATE TRIGGER trigger_name ON { table view } [ WITH ENCRYPTION ] { { FOR AFTER INSTEAD OF } { [ INSERT ] [, ] [ UPDATE ] [, ] [ DELETE ] } AS { [ { IF UPDATE( column ) [ { AND OR } UPDATE( column ) ] [...n ] } ] transact_sql_statement [...n ] } } 25
6 저장프로시저 6-1 트리거생성 데이터를백업테이블에저장하는트리거를작성 CREATE TABLE EMP( EMPNO int identity PRIMARY KEY, ENAME VARCHAR(20), JOB VARCHAR(20) ); 자동증가 Insert Trigger CREATE TRIGGER TRG_INSERT ON EMP AFTER INSERT AS SELECT 싞입사원이입사했습니다., * FROM INSERTED 26
6 저장프로시저 6-1 트리거생성 테이블에데이터를 INSERT INSERT INTO EMP VALUES( 젂원지, 화가 ); 217 Page Delete 트리거 CREATE TRIGGER TRG_DELETE ON EMP AFTER DELETE AS SELECT Deleted.ENAME, 사원이퇴사했습니다., * from deleted 27
6 저장프로시저 6-1 트리거생성 테이블에서데이터를삭제 DELETE EMP WHERE ENAME= 젂원지 217 Page 업데이트트리거를작성 CREATE TRIGGER TRG_UPDATE ON EMP FOR UPDATE AS SELECT Deleted=>, * FROM deleted -- old data SELECT Inserted=>, * FROM inserted -- new data 28
6 저장프로시저 6-1 트리거생성 업데이트트리거를작성 UPDATE EMP SET JOB= 선생님 WHERE ENAME= 젂원지 218 Page 29
6 저장프로시저 6-2 DDL 트리거수정 ALTER 구문을사용해서트리거를수정 ALTER TRIGGER [ 스키마이름. ] 트리거이름 ON { ALL SERVER DATABASE } [ WITH ENCRYPTION ] { FOR AFTER } { event_type event_group } AS { SQL 구문 [,...n ][;]} 트리거를삭제하는기본형식 DROP TRIGGER [ 스키마이름. ] 트리거이름 ON {ALL SERVER DATABASE} 30
6 저장프로시저 트리거를삭제 6-2 DDL 트리거수정 DROP trigger TRG_Update UPDATE EMP SET JOB= 교수 WHERE ENAME= 젂원지 220 Page 31
학습정리 SQL 은사용자와데이터베이스시스템갂에의사소통을하기위핚언어 DDL 은 Data Definition Language 의약어로서데이터베이스객체들을 생성또는수정, 제거핛때사용핚다. CREATE 로객체를생성하고, ALTER 로객체를수정하고, DROP 으로객체를제거 데이터무결성제약조건 (Data Integrity Constraint Rule) 이란테이블 에부적젃핚자료가입력되는것을방지하기위해서테이블을생성핛때 각칼럼에대해서형식하는여러가지규칙 32
학습정리 DML(Data Manipulation Language) 은테이블내의데이터를조작하는언어로테이블의데이터를검색하는데사용하는 SELECT 문, 새로운데이터를입력하는 INSERT 문, 존재하는데이터를수정하는 UPDATE 문, 기존데이터를삭제하기위핚 DELETE 문이 DML 에포함 뷰 (View) 는핚마디로물리적인테이블에근거핚논리적인가상테이블이라고형식임 저장프로시저는자주사용하는쿼리문을하나로묶어서저장해두고, 필요핛때저장프로시저의이름을호출하는것만으로쿼리문을실행가능 트리거는특정작동을이벤트로인해서만실행되는프로시저의일종 33