6 장 SQL
목차 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) 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
1 SQL SQL(Structured Query Language) SQL SQL은사용자와데이터베이스시스템간에의사소통을하기위한언어 데이터베이스와 SQL 문 129 Page SQL 데이터베이스에저장된데이터를조회, 입력, 수정, 삭제하는등의조작이나테이블을비롯한다양한객체 ( 시퀀스, 인덱스등 ) 를생성및제어하는역할 4
6 SQL 대화식 SQL 1-2 SQL 의유형별종류 화면에명령을넣고, 결과가바로화면으로나오는방식 내포된 SQL Java, COBOL, C, C++ 등과같은고급프로그래밍언어사이에 SQL 문을끼어넣는방식 131 Page 5
6 SQL 1-2 SQL 의유형별종류 DDL, DML, DCL 131 Page 6
6 SQL 1-2 SQL 의유형별종류 SQL 명령문의유형 유형 명령문 DQL:Data Query Language( 데이터검색 ) SELECT( 데이터검색시사용 ) DML:Data Manipulation Language ( 데이터조작어 )- 데이터변경시사용 DDL:Data Definition Language ( 데이터정의어 )- 객체생성및변경시사용 TCL:Transaction Control Language ( 트랜잭션처리어 ) INSERT ( 데이터입력 ) UPDATE ( 데이터수정 ) DELETE ( 데이터삭제 ) CREATE ( 데이터베이스생성 ) ALTER ( 데이터베이스변경 ) DROP ( 데이터베이스삭제 ) RENAME ( 데이터베이스객체이름변경 ) TRUNCATE ( 데이터베이스저장공간삭제 ) COMMIT ( 트랜잭션의정상적인종료처리 ) ROLLBACK ( 트랜잭션취소 ) SAVEPOINT ( 트랜잭션내에임시저장점설정 ) DCL:Data Control Language ( 데이터제어어 ) GRANT ( 데이터베이스에대한일련의권한부여 ) REVOKE ( 데이터베이스에대한일련의권한취소 ) 7
데이터정의어 (DDL) 133 Page DDL 명령문의종류 SQL 문 내용 CREATE DROP ALTER 데이터베이스및객체생성 데이터베이스및객체삭제 기존에존재하는데이터베이스객체를변경 8
2-1 스키마 SQL Server Management Studio(SSMS) 의각창 136 Page 9
2-1 스키마 SQL Server Management Studio 의각창과역할 개체탐색기 서버탐색, 개체작성및로그보기등을수행 쿼리편집기 개체들을액세스하거나생성하는쿼리를작성하는부분이며작성된스크립트는별도의스크립트 (.sql) 파일로저장가능 결과및메시지창 개체를 SELECT 하면결과를표나텍스트등으로보여주며개체를생성하거나삭제, 업데이트등의결과표시 10
2-1 스키마 SSMS 를이용한데이터베이스생성하기 1) 쿼리편집기열기 2) 쿼리를실행할대상서버 ( 인스턴스 ) 를선택쿼리편집기에다음과같이입력한후 < 실행 > 을클릭 135 Page 예 USE master GO 11
2-1 스키마 3) 쿼리분석기를실행한후아래 T-SQL 구문을작성 예 CREATE DATABASE EduManager - 데이터베이스생성 136 Page 12
2-2 테이블생성 (CREATE TABLE) CREATE TABLE 형식 ( 교재p137) CREATE TABLE 테이블이름 ( { 열이름데이터타입 [NOT NULL] [DEFAULT 값 ], }+ [PRIMARY KEY ( 열이름 _ 리스트 ),] {[UNIQUE ( 열이름 _ 리스트 ),] }* {[FOREIGN KEY( 열이름 _ 리스트 ) REFERENCES 기본테이블 [( 열이름 _ 리스트 )] [ON DELETE 옵션 ] [ON UPDATE 옵션 ],] } * [CONSTRAINT 이름 ] [CHECK( 조건식 )] ) ; - [ ] 로묶인부분은생략가능한부분이고, { } 로묶인부분은중복가능부분을나타냄 - + 는 1 번이상, * 는 0 번이상반복을나타냄 - + 나 * 앞에, 는요소들이여러번반복되어형식될수있음을나타냄 13
CREATE TABLE 2 데이터정의어 (DDL) 2-2 테이블생성 (CREATE TABLE) - CREATE TABLE문다음에는테이블이름을기술 - 칼럼 ( 열, 속성 ) 에대해서칼럼이름과함께데이터타입과제약조건을명시 - CREATE TABLE 문은세미콜론 ; 으로끝나야함 이름을주는기본규칙 1. 테이블이름과칼럼은 A~Z 까지의문자, 0~9 까지의숫자, 그리고 $, #, _(Under Bar) 를사용할수있다. 그러나공백은사용할수없다. 2. 테이블의칼럼은 30 자를초과할수없고, 예약어를사용할수없다. 3. 한테이블안에서칼럼이름은같을수없으며다른테이블에서의칼럼이름과는같을수있다. - 1개의테이블에는여러개의칼럼이존재하므로각칼럼들은콤마, 로구분되고, 각칼럼의데이터타입은반드시지정되어야함 14
2-1 스키마 SQL Server 의대표데이터타입의종류 ( 교재 p138) 이름 범위및설명 비고 bigint -2 63 (-9,223,372,036,854,775,808) ~ 2 63-1(9,223,372,036,854,775,807) 정수형숫자 (8바이트) int -2 31 (-2,147,483,648) ~ 2 31-1(2,147,483,647) 정수형숫자 (4바이트) smallint -2 15 (-32,768) ~ 2 15-1(32,767) 정수형숫자 (2바이트) tinyint 0 ~ 255 1바이트양의정수 bit 0,1 0 또는 1 decimal - 10 38 +1 ~ 10 38-1 고정소수점숫자 numeric - 10 38 +1 ~ 10 38-1 고정소수점숫자 money -922,337,203,685,477.5808 ~ 922,337,203,685,477.00 통화 (8바이트) smallmoney - 214,748.3648 ~ 214,748.3647 통화 (4 바이트 ) 15
2-1 스키마 SQL Server 의대표데이터타입의종류 float[n] - 1.79E+308 ~ -2.23E-308, 0 과 2.23E-308 ~ 1.79E+308 부동소수점숫자 (4~8 바이트 ) real - 3.40E+38 ~ 1.18E-38, 0 과 1.18E-38 ~ 3.40E+38 datetime 1753/1/1 ~ 9999/12/31 날짜와시간 smalldatetime 1900/1/1 ~ 2079/6/6 날짜와시간 부동소수점숫자 (4 바이트 ) char[n] 1~8000 바이트고정길이문자 varchar[n] 1~8000 바이트가변길이문자 ( 실제데이터크기 ) text 최대 2 32-1(2,147,483,647) 자 가변길이문자 ( 최대 2,147,483,647 문자 ) nchar 1~4000 바이트유니코드고정길이문자 (n 바이트 *2) nvarchar 1~4000 바이트유니코드가변길이문자 ( 실제데이터크기 *2) ntext 최대 2 30-1(1,073,741,823) 자유니코드가변길이문자 binary[n] 1~8000 바이트고정길이이진데이터 varbinary 1 부터 8,000 가변길이이진데이터 16
2-1 스키마 SQL Server 의대표데이터타입의종류 이름 범위및설명 image cursor 최대 2 31-1(2,147,483,647) 자 커서에대한참조가들어있는변수또는저장프로시저 OUTPUT 매개변수의데이터형식 sql_variant table timestamp uniqueidentifier XML text, ntext, image, timestamp 및 sql_variant 를제외한, SQL Server 2005 에서지원하는여러가지데이터형식의값을저장하는데이터형식 결과집합을저장할수있는특별한데이터형식 데이터베이스내에서자동으로생성된고유이진숫자를표시하는데이터형식 16 바이트 GUID XML 데이터를저장하는데이터형식 17
2-2 테이블생성 (CREATE TABLE) 과정 정보를저장하는 COURSE 테이블을생성 ( 교재 p139) 1: 2: 3: 4: 5: 6: 7: CREATE TABLE COURSE ( COU_ID INT -- 과정번호 COU_NAME VARCHAR(20) NOT NULL, -- 과정명 TEA_NAME VARCHAR(20) NULL, -- 강사명 PRIMARY KEY(COU_ID) -- 과정번호를기본키로지정 ) 18
2-2 테이블생성 (CREATE TABLE) 학생 정보를저장하는 STUDENT 테이블을생성 1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: CREATE TABLE STUDENT ( STU_ID INT NOT NULL, -- 학생번호, 널값허용안함 STU_NAME VARCHAR(20) NULL, -- 학생이름 AGE INT NULL, -- 나이 STU_EMAIL VARCHAR(20) NULL, -- 이메일 COU_ID INT NULL, -- 소속된과정의과정번호 PRIMARY KEY(STU_ID), -- 학생번호를기본키로지정 -- -- 과정번호를외래키로지정 FOREIGN KEY(COU_ID) REFERENCES COURSE(COU_ID) ) 19
SQL 주석문 2 데이터정의어 (DDL) 2-2 테이블생성 (CREATE TABLE) Tip 각줄에기술한문장이무엇인지를개발자에게설명하기위해서주석문을작성하는데, 주석문은 - - 로시작하며실행을시켰을경우주석처리한문장은실행에서제외된다. 20
2-2 테이블생성 (CREATE TABLE) SQL Server 에서테이블생성하기 - 과정 테이블생성 140 Page 21
2-2 테이블생성 (CREATE TABLE) SQL Server 에서테이블생성하기 - 학생 테이블생성 22
2-3 테이블변경 (ALTER TABLE) ALTER TABLE 명령 ( 교재 p141) - 기존테이블에속성을추가 / 삭제 / 변경 ALTER TABLE 테이블명 ([ADD 칼럼명데이터 _ 타입 ] [DEFAULT 값 ] [DROP 칼럼명 ] [CASCADE] [ALTER 칼럼명 (DROP DEFAULT SET DEFAULT 값 )]); - 학생 (STUDENT) 테이블에성별을구분하기위한칼럼을추가 1: 2: ALTER TABLE STUDENT ADD GENDER VARCHAR DEFAULT 'M'; 23
2-3 테이블변경 (ALTER TABLE) 테이블구조변경 142 Page 24
2-4 테이블제거 (DROP TABLE) DROP TABLE 명령 ( 교재p142) 예 DROP TABLE COURSE; 테이블제거실패 - FOREIGN KEY( 외래키 ) 제약조건에의해참조되는테이블은삭제할수없음 142 Page 25
2-4 테이블제거 (DROP TABLE) DROP TABLE 명령 1: 2: 3: DROP TABLE STUDENT DROP TABLE COURSE GO 테이블제거성공 143 Page 26
데이터무결성제약조건 (Data Integrity Constraint Rule)( 교재 P144) 데이터무결성제약조건 (Data Integrity Constraint Rule) 테이블에부적절한자료가입력되는것을방지하기위해서테이블을생성할때각칼럼 ( 속성 ) 에대해서형식을지정하는규칙 정확성 ( 무결 ) 을유지 무결점 데이터베이스내에데이터의정확성을유지하는것을의미 27
기본키제약조건 - 과정 (COURSE) 테이블에서과정들을구분하기위해서 과정번호 속성을기본키로지정 - 과정번호 칼럼은기본키로설정되어있으므로반드시입력되고, 유일한값만을가질수있음즉, 과정번호 칼럼은널값을저장할수없고, 중복된값을저장할수없음 - 데이터베이스에서제공하는다양한무결성제약조건들중의한가지 NOT NULL 을명시 - 특정칼럼에널값을허용하지않도록하려는제약조건 28
과정테이블의기본키제약조건 ( 기본키값의중복 ) 과정번호 (COU_ID) 칼럼은중복된값을저장할수없는기본키이다. COURSE 테이블에 10 번과정이이미존재하고있기때문에 10 번과정을추가하려고했을때에러가발생한다. 145 Page 29
과정테이블의기본키제약조건 (NULL 값삽입 ) 145 Page 30
제약조건을 5 가지 ( 교재 P146) - 특정속성에대해제약을지정하기위해서속성단위로제약조건을명시 NULL 값을허용하지않음 중복된값을허용하지않고, 항상유일한값을갖도록함 NULL 값과중복된값을허용하지않음 NOT NULL 조건과 UNIQUE 조건을결합한형태임 참조되는테이블의칼럼값이존재하면허용함 저장가능한데이터값의범위나조건을지정해설정한값만을허용함 31
필수입력을위한 NOT NULL 제약조건 - 부정확한회원정보 147 Page NOT NULL 제약조건 - 해당칼럼에데이터를추가하거나수정할때 NULL 값이저장되지않게제약을지정하는것 - 주민번호와이름과같이자료가꼭입력되어야하는경우사용 32
NOT NULL 제약조건을설정하지않고테이블생성하기 - 회원테이블을생성 CREATE TABLE member( id varchar(20), pwd varchar(20), name varchar(20), email varchar(20), hp varchar(20) ) 33
NOT NULL 제약조건을설정하지않고테이블생성하기 - SSMS 에서 NULL 값을허용한테이블생성 148 Page 34
NOT NULL 제약조건을설정하지않고테이블생성하기 - 테이블의데이터확인 SELECT * FROM member - SSMS 에서 member 테이블의내용확인 148 Page 35
NOT NULL 제약조건을설정하지않고테이블생성하기 - 제약조건이불충분한테이블로의미없는정보입력 INSERT INTO member INSERT 문에자료가삽입될칼럼을지정하지않았으므로기본칼럼순서대로 VALUES 절의자료가삽입됨. 칼럼에 NULL 값이저장됨 VALUES(NULL,NULL,NULL, moon@nate.com, 1063385361 ) SELECT * FROM member 36
NOT NULL 제약조건을설정하지않고테이블생성하기 - SSMS 에서제약조건이불충분한테이블로의미없는정보입력 149 Page 37
제약조건지정형식 ( 교재P149) column_name data_type constraint_type NOT NULL 제약조건을설정하여테이블생성하기 - NOT NULL 제약조건을설정 DROP TABLE member GO member 테이블이존재하면다시생성되지않기에일단 member 테이블제거 CREATE TABLE member( id varchar(20) NOT NULL, pwd varchar(20) NOT NULL, name varchar(20) NOT NULL, email varchar(20) NULL, hp varchar(20) NULL ) 테이블생성시칼럼에 NOT NULL 제약조건설정 38
필수입력을위한 NOT NULL 조건지정 - SSMS 에서필수입력을위한 NOT NULL 조건지정 150 Page 39
필수입력을위한 NOT NULL 조건지정 - 생성한 member 테이블에데이터를추가 INSERT INTO member INSERT문에자료가삽입될칼럼을지정하지않았으므로기본칼럼순서대로 VALUES 절다음에기술한값을저장 VALUES(NULL, NULL, NULL, moon@nate.com, 01063385361 ) 필수입력을위한 NOT NULL 조건지정 - 필수입력요구 40
필수입력을위한 NOT NULL 조건지정 - member 테이블에아이디와패스워드, 이름에 NULL 값이아닌값을지정 INSERT INTO member VALUES( moon, Y, 문종헌, moon@nate.com, 01063385361 ) SELECT * FROM member 필수입력을위한 NOT NULL 조건지정 - 정확한정보입력 41
유일한값만을저장하기위한 UNIQUE 제약조건 ( 교재 p152) - 특정칼럼에대해자료가중복되지않는유일한값이수록되게하는것 동일한아이디로는구분불가능 ㆍ id 칼럼에 UNIQUE KEY 제약조건을지정하면중복된값을저장할수없음 42
UNIQUE 제약조건을설정하여테이블생성하기 - member 테이블을생성하되아이디를유일키로지정 - 제약조건은칼럼명과자료형을기술한후에연이어서 UNIQUE를기술 DROP TABLE member GO CREATE TABLE member( id varchar(20) UNIQUE, pwd varchar(20) NOT NULL, name varchar(20) NOT NULL, email varchar(20) NULL, hp varchar(20) NULL ) GO 테이블생성시칼럼에 UNIQUE 를기술하면해당칼럼은중복된값을허용하지않는제약조건이설정된다. 43
UNIQUE 제약조건을설정하여테이블생성하기 - SSMS 에서 UNIQUE 제약조건설정 44
UNIQUE 제약조건을설정하여테이블생성하기 - 생성한 member 테이블에데이터를추가 INSERT INTO member VALUES( moon, Y, 문종헌, moon@nate.com, 01063385361 ) SELECT * FROM member UNIQUE 제약조건을설정하여테이블생성하기 - 데이터삽입 45
UNIQUE 제약조건을설정하여테이블생성하기 - moon 이란자료를입력하였는데다시동일한아이디를입력 UNIQUE 제약조건을설정하여테이블생성하기 - 아이디중복허용안함 INSERT INTO member VALUES( moon, SOL, 오한솔, five@nate.com, 01011113121 ) 46
개체의무결성을위한 PRIMARY KEY 제약조건 ( 교재 P155) 기본키 (PRIMARY KEY) 제약조건 ㆍ UNIQUE 제약조건과 NOT NULL 제약조건을모두갖는것 PRIMARY KEY 는 NOT NULL 조건과 UNIQUE 조건을결합한형태로서 NULL 을허용하지않고중복된값을허용하지않는다. 테이블에하나만선언할수있다. 47
PRIMARY KEY 제약조건설정하기 - 칼럼명과자료형을기술한후에연이어서 PRIMARY KEY 를기술 CREATE TABLE member( id varchar(20) CONSTRAINT PK_id PRIMARY KEY, pwd varchar(20) CONSTRAINT NN_pwd NOT NULL, name varchar(20) CONSTRAINT NN_name NOT NULL, email varchar(20) NULL, hp varchar(20) NULL ) GO 48
PRIMARY KEY 제약조건설정하기 - 기본키제약조건설정 156 Page 49
PRIMARY KEY 제약조건설정하기 - 기본키로지정된아이디에동일한값을저장 INSERT INTO member VALUES( moon, Y, 문종헌, moon@nate.com, 01063385361 ) INSERT INTO member VALUES( moon, SOL, 오한솔, five@nate.com, 01011113121 ) 50
PRIMARY KEY 제약조건설정하기 - 중복허용하지않는기본키제약조건 157 Page 51
PRIMARY KEY 제약조건설정하기 - 기본키로지정된아이디에 NULL 값을저장 INSERT INTO member VALUES(NULL, Y, 문종헌, moon@nate.com, 01063385361 ) PRIMARY KEY 제약조건설정하기 - 널값허용하지않는기본키제약조건 157 Page 52
참조의무결성을위한 FOREIGN KEY 제약조건 ( 교재 P158) 참조의무결성 ㆍ테이블사이의관계에서발생하는개념 FOREIGN KEY ㆍ일반적으로업무규칙에서주종관계가있는두테이블간에사용되며종속되는테이블의키칼럼이주가되는테이블의 PRIMARY KEY 또는 UNIQUE 칼럼을참조함 참조의무결성을위한 FOREIGN KEY 제약조건 - 과정 테이블 53
참조의무결성을위한 FOREIGN KEY 제약조건 - 학생 테이블 [ 학생테이블과과정테이블의관계 ] ㆍ만일, 과정테이블에존재하지않는과정번호존재 - 이치에맞지않음 - 무결해야한다는조건에위배됨 ㆍ과정테이블에존재하는과정번호만존재 - 참조의무결성 - 학생테이블의과정번호칼럼에외래키제약조건명시 54
참조의무결성을위한 FOREIGN KEY 제약조건 - 참조의무결성을위한외래키제약조건을설정할때중요한개념 160 Page 55
ERD 를보고데이터베이스를구현할때 - 과정 이나 학생 과같은개체는테이블로, 수강 관계는참조무결성을위한외래키제약조건 - 학생은교육센터내에존재하는과정에수강해야한다. 참조의무결성을위한부모, 자식테이블관계 161 Page 56
과정테이블과학생테이블관계 COU_ID 가부모키가되려면기본키혹은 UNIQUE 제한조건으로지정되어야함 학생테이블에서외래키인 COU_ID 는과정테이블의기본키인 COU_ID 를참조하므로과정테이블에존재하는과정번호만입력가능함. 과정테이블내의 COU_ID 를부모키라고함 161 Page 57
- 참조무결성을유지하기위해서학생 (STUDENT) 테이블의과정번호 (COU_ID) 를외래키로지정 CREATE TABLE STUDENT ( STU_ID INTEGER, -- 학생번호 STU_NAME VARCHAR(20) NULL, -- 학생명 AGE INTEGER NULL, -- 나이 STU_EMAIL VARCHAR(20) NULL, -- 이메일 COU_ID INTEGER NULL, -- 소속된과정의과정번호 PRIMARY KEY(STU_ID), -- 학생번호를기본키로지정 -- -- 과정번호를외래키로지정 FOREIGN KEY(COU_ID) REFERENCES COURSE(COU_ID) ) GO 58
무결성제약조건에위배 COURSE 테이블에는 10, 20, 30, 40 만존재하기에 50 번과정에새로운학생을추가할수없다. 162 Page 59
칼럼값의조건지정을위한 CHECK 제약조건 ( 교재 P163) CHECK 제약조건 ㆍ입력되는값을체크하여설정된값이외의값이들어오면오류메시지와함께명령이수행되지못하게하는것ㆍ조건으로데이터의값의범위나특정패턴의숫자나문자값을설정 CHECK 제약조건설정하기 CREATE TABLE STUDENT ( STU_ID INTEGER, -- 학생번호, 널값허용안함 STU_NAME VARCHAR(20) NULL, -- 학생명 AGE INTEGER NULL CHECK(AGE < 30), -- 나이 STU_EMAIL VARCHAR(20) NULL, -- 이메일 COU_ID INTEGER NULL, -- 소속된과정의과정번호 GENDER VARCHAR NULL DEFAULT M, -- 성별 PRIMARY KEY(STU_ID), -- 학생번호를기본키로지정 -- -- 과정번호를외래키로지정 FOREIGN KEY(COU_ID) REFERENCES COURSE(COU_ID) ) GO 60
CHECK 제약조건 164 Page 61
CHECK 제약조건설정하기 - 생성한테이블에데이터를추가 INSERT INTO STUDENT VALUES(116, 장희성, 34, shine@nate.com, 10, M ) CHECK 제약조건설정하기 - check 제약조건위배 164 Page 62