6. SQL

SQL의 역사 SEQUEL(Structured English QUEry Language)에 연유 표준 SQL 현재 1974년, IBM 연구소에서 발표 IBM은 'SYSTEM R' 의 인터페이스로 설계 구현 실험적 관계 데이타베이스 시스템 인터페이스 표준 SQL 1986년, SQL-86 또는 SQL1 1992년 개정, SQL/92, SQL-92 또는 SQL2 다음 버전 : SQL3, SQL-99 현재 상용 DBMS인 DB2와 SQL/DS의 데이타 언어로 사용 ORACLE, INFORMIX, SYBASE 등과 같은 다른 회사에서도 채택 미국 표준 연구소(ANSI)와 국제 표준 기구(ISO)에서 관계 데이타베이스의 표준 언어로 채택 금오공과대학 컴퓨터공학부 컴퓨터공학전공

SQL(Structured Query Language) 구조화 질의어 종합 데이타베이스 언어 역할 단순히 검색만을 위한 데이타 질의어가 아님 데이타 정의어(DDL), 데이타 조작어(DML), 데이타 제어어(DCL)의 기능 모두 제공 금오공과대학 컴퓨터공학부 컴퓨터공학전공

SQL의 특징 관계 대수의 특징포함 + 확장된 관계 해석 기초 고급 비 절차적 데이타 언어 SQL의 표준화 사용자 친화적인 인터페이스 제공 SQL의 표준화 상용 RDBMS간의 전환 용이 관계 데이타베이스를 접근하는 데이타베이스 응용 프로그램 작성 기능 제공 온라인 터미널을 통해 대화식 질의어로 사용 가능 금오공과대학 컴퓨터공학부 컴퓨터공학전공

SQL의 특징(2) 응용 프로그램에 삽입된 형태로도 사용 가능 개개의 레코드 단위로 처리하기 보다는 레코드 집합 단위로 처리 Java, COBOL, C/C++ 등과 같은 범용 프로그래밍 언어로 된 응용 프로그램 개개의 레코드 단위로 처리하기 보다는 레코드 집합 단위로 처리 선언적 언어 SQL 명령문에는 데이타 처리를 위한 접근 경로(access path)에 대한 명세가 불필요 Note 관계 모델의 공식적 용어 대신 일반적인 용어 사용 릴레이션 – 테이블, 투플 – 행, 애트리뷰트 – 열 금오공과대학 컴퓨터공학부 컴퓨터공학전공

스키마 스키마 CREATE SCHEMA UNIVERSITY AUTHORIZATION SHLEE ; 하나의 응용(사용자)에 속하는 테이블과 기타 구성요소 등의 그룹 스키마 이름으로 식별( -> 데이터베이스 스키마) 스키마 소유자와 계정을 나타내는 허가 식별자 스키마의 각 요소(테이블, 뷰, 도메인, 기타 내용)에 대한 서술자 포함 CREATE SCHEMA UNIVERSITY AUTHORIZATION SHLEE ; 실제로 CREATE SCHEMA 보다 CREATE DATABASE 명령문을 씀 금오공과대학 컴퓨터공학부 컴퓨터공학전공

카탈로그 카탈로그 Information_schema 한 SQL 시스템내의 스키마들의 집합 무결성 제약조건은 같은 카탈로그에 있는 스키마에 속한 테이블들간에만 정의 Information_schema 각 카탈로그는 Information_schema를 포함 각 카탈로그에 속한 모든 스키마에 대한 정보 제공 사용자의 입장에서는 Information_schema가 카탈로그의 역할 금오공과대학 컴퓨터공학부 컴퓨터공학전공

도메인 정의문- 일반 형식 CREATE DOMAIN 도메인_이름 데이타_타입 [ 기본 값_정의 ] [ 도메인_제약조건_정의리스트 ]; ex) CREATE DOMAIN Dept CHAR(4) DEFAULT '???’ CONSTRAINT VALID-DEPT CHECK( VALUE IN ('COMP', 'ME', 'EE', 'ARCH', '???')); ALTER DOMAIN 도메인_이름 <변경 내용> DROP DOMAIN 도메인_이름 RESTRICT | CASCADE ; Restrict: 삭제될 도메인이 참조되지 않는 경우 만 수행 Cascade: 삭제될 도메인을 참조하고 있는 모든 것들도 삭제 금오공과대학 컴퓨터공학부 컴퓨터공학전공

데이터타입 숫자 문자 스트링 비트 스트링 날짜 시간 INTEGER, SMALLINT : 정수 FLOAT(n), REAL, DOUBLE PRECISION : 실수 DECIMAL(i, j), NUMERIC(i, j) : 정형 숫자 문자 스트링 CHAR(n) : 고정 길이 문자 VARCHAR(n) : 가변 길이 문자 비트 스트링 BIT(n), BIT VARYING(n) 날짜 DATE : YY-MM-DD 시간 TIME : hh:mm:ss TIMESTAMP : DATE와 TIME 포함 INTERVAL : DATE, TIME, TIMESTAMP 포함 금오공과대학 컴퓨터공학부 컴퓨터공학전공

테이블의 종류 기본 테이블 뷰(view) 임시 테이블 원래 DDL에 의해 만들어지는 테이블 독자적으로 존재 가능 어떤 기본 테이블로부터 유도되어 만들어지는 가상 테이블(virtual table) 임시 테이블 DDL에 의해 만들어지는 것이 아님 질의문 처리 과정의 중간 결과로 만들어지는 테이블 금오공과대학 컴퓨터공학부 컴퓨터공학전공

기본 테이블의 생성 일반형식 CREATE TABLE 기본테이블이름 ({열이름 데이타타입 [NOT NULL] [DEFAULT 값],}+ [PRIMARY KEY (열이름_리스트),] {[UNIQUE (열이름_리스트),]}* {[FOREIGN KEY(열이름_리스트) REFERENCES 기본테이블[(열이름_리스트)] [ON DELETE 옵션] [ON UPDATE 옵션] ,]} * [CONSTRAINT 이름] [CHECK(조건식)]); []: 옵션, {}+ : 한번 이상 반복, {}* : 0번 이상 반복 금오공과대학 컴퓨터공학부 컴퓨터공학전공

ON DELETE 옵션 ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } 행이 참조 관계를 가지고 참조된 행이 부모 테이블에서 삭제될 경우에 테이블의 행에 수행될 작업을 지정합니다. 기본값은 NO ACTION입니다. NO ACTION 데이터베이스 엔진 에서 오류가 발생하며 부모 테이블의 행에 대한 삭제 작업이 롤백됩니다. CASCADE 부모 테이블에서 행을 삭제한 경우 참조 테이블에서 해당 행이 삭제됩니다. SET NULL 부모 테이블에서 해당 행을 삭제하면 외래 키를 구성하는 모든 값이 NULL로 설정됩니다. 이 제약 조건을 실행하려면 외래 키 열이 Null을 허용해야 합니다. SET DEFAULT 부모 테이블에서 해당 행을 삭제하면 외래 키를 구성하는 모든 값이 기본값으로 설정됩니다. 이 제약 조건을 실행하려면 모든 외래 키 열에 기본 정의가 있어야 합니다. 열에 Null 값을 사용할 수 있고 명시적 기본값이 설정되어 있지 않은 경우 해당 열의 암시적 기본값은 Null입니다. 금오공과대학 컴퓨터공학부 컴퓨터공학전공

테이블 생성의 예 CREATE TABLE ENROL ( Sno DSNO NOT NULL, Cno DCNO NOT NULL, Grade INTEGER, PRIMARY KEY(Sno,Cno), FOREIGN KEY(Sno) REFERENCES STUDENT(sno) ON DELETE CASCADE ON UPDATE CASCADE, /* NO ACTION, CASCADE, SET NULL, SET DEFAULT */ FOREIGN KEY(Cno) REFERENCES COURSE ON DELETE CASCADE ON UPDATE CASCADE, CHECK(Grade >= 0 AND Grade <= 100)); /* 무결성 제약 조건 */ 대소문자 구별이 없음 CREATE TABLE ENROL6 ( Sno char(20) NOT NULL, Cno char(20) NOT NULL, Grade INTEGER, PRIMARY KEY(Sno,Cno), FOREIGN KEY(Sno) REFERENCES STUDENT(sno) ON DELETE CASCADE ON UPDATE CASCADE, CHECK(Grade > 0) ); 금오공과대학 컴퓨터공학부 컴퓨터공학전공

기본 테이블의 제거 기본 테이블의 제거 스키마 제거 일반 형식 DROP TABLE 기본_테이블_이름 { RESTRICT | CASCADE } ; /* COURSE를 사용하는 ENROL도 같이 삭제 */ DROP TABLE COURSE CASCADE; 스키마 제거 일반형식 DROP SCHEMA 스키마_이름 { RESTRICT | CASCADE }; DROP SCHEMA UNIVERCITY CASCADE; DROP TABLE 테이블 명 [CASCADE CONSTRAINT]: 　　● 모든 데이터가 테이블에서 삭제된다. 　　● 트랜잭션은 자동 커밋된다. 　　● 해당테이블의 모든 인덱스가 삭제된다. 　　● CASCADE CONSTRAINTS 옵션은 종속 무결성 제약조건을 삭제한다. 　　● 이 명령은 롤백할 수 없다. ♣ 테이블 삭제 DROP TABLE 명령은 Oracle7 테이블의 정의를 삭제한다. 테이블을 삭제하면 테이블의 모든 데이터 및 관련된 모든 인덱스가 없어진다. CASCADE CONSTRAINTS 옵션은 종속적인 참조 무결성 제약조건도 삭제할 것이다. 금오공과대학 컴퓨터공학부 컴퓨터공학전공

기본 테이블의 변경 기본 테이블의 변경 일반형식 ALTER TABLE 기본_테이블_이름 ([ADD 열_이름 데이타_타입] [DEFAULT 기본 값] | [DROP 열_이름] [CASCADE] | [ALTER 열_이름 (DROP DEFAULT | SET DEFAULT 기본 값)]); 예 ALTER TABLE ENROL ADD Final CHAR DEFAULT 'F'; ALTER TABLE ENROL DROP Grade CASCADE; 금오공과대학 컴퓨터공학부 컴퓨터공학전공

대학(University) 관계 데이타베이스  example 대학(University) 관계 데이타베이스 학번 (Sno) 이름 (Sname) 학년 (Year) 학과 (Dept) 100 나 수 영 4 컴퓨터 200 이 찬 수 3 전기 300 정 기 태 1 400 송 병 길 500 박 종 화 2 산공 학생 (STUDENT) 과목번호 (Cno) 과목이름 (Cname) 학점 (Credit) 학과 (Dept) C123 프로그래밍 3 컴퓨터 C312 자료구조 C324 화일구조 C413 데이타베이스 E412 반 도 체 전자 담당교수 (PRname) 김성국 황수관 이규찬 이일로 홍봉진 과목 (COURSE) 금오공과대학 컴퓨터공학부 컴퓨터공학전공

대학(University) 관계 데이타베이스(cont’d)  example 대학(University) 관계 데이타베이스(cont’d) 학번 (Sno) 100 200 300 400 500 과목번호 (Cno) C413 E412 C123 C312 C324 성적 (Grade) A B C 중간성적 (Midterm) 90 95 85 75 80 65 기말성적 (Final) 등록 (ENROL) 금오공과대학 컴퓨터공학부 컴퓨터공학전공

SQL 데이타 조작문 데이타 검색 기본 구조 SELECT 열_리스트 FROM 테이블_리스트 WHERE 조건; 기본 구조 SELECT 열_리스트 FROM 테이블_리스트 WHERE 조건; 예 SELECT Sname, Sno FROM STUDENT WHERE Dept = '컴퓨터'; SELECT STUDENT.Sname, STUDENT.Sno FROM STUDENT WHERE STUDENT.Dept = '컴퓨터'; 실행 결과 Sname Sno 나수영 100 정기태 300 송병길 400 금오공과대학 컴퓨터공학부 컴퓨터공학전공

데이타 검색 (1) 폐쇄 시스템(closed system) SQL과 이론적 관계 모델의 차이점 기존 테이블 처리 결과가 또 다른 테이블이 되는 시스템 중첩 질의문(nested query)의 이론적 기초 SQL과 이론적 관계 모델의 차이점 SQL의 테이블 한 테이블 내에 똑같은 레코드(행) 중복 가능 기본 키를 반드시 가져야 하는 것은 아님 이론상 SQL의 테이블 ≠ 투플의 집합 같은 원소의 중복을 허용하는 다중 집합(multiset) 또는 백(bag) → DISTINCT 명세 : 집합과 같은 결과를 만듦 금오공과대학 컴퓨터공학부 컴퓨터공학전공

데이타 검색 (2) 일반적인 형식 검색 결과에 레코드의 중복 제거 테이블의 열 전부를 검색하는 경우 SELECT [ALL | DISTINCT] 열_리스트 FROM 테이블_리스트 [WHERE 조건] [GROUP BY 열_리스트 [HAVING 조건] ] [ORDER BY 열_리스트 [ASC | DESC] ]; 검색 결과에 레코드의 중복 제거 SELECT DISTINCT Dept FROM STUDENT; 테이블의 열 전부를 검색하는 경우 SELECT * FROM STUDENT; 실행 결과 Dept 컴퓨터 전기 산공 금오공과대학 컴퓨터공학부 컴퓨터공학전공

데이타 검색 (3) 조건 검색 순서를 명세하는 검색 산술식과 문자 스트링이 명세된 검색 SELECT Sno, Sname FROM STUDENT WHERE Dept = '컴퓨터' AND Year = 4; 순서를 명세하는 검색 SELECT Sno, Cno FROM ENROL WHERE Midterm≥ 90 ORDER BY Sno DESC, Cno ASC; 산술식과 문자 스트링이 명세된 검색 SELECT Sno AS 학번, '중간시험 = ' AS 시험, Midterm + 3 AS 점수 FROM ENROL WHERE Cno = 'C312'; 실행 결과 학번 시험 점수 300 중간시험 = 93 400 중간시험 = 93 500 중간시험 = 88 금오공과대학 컴퓨터공학부 컴퓨터공학전공

데이타 검색 (4) 복수 테이블로부터의 검색(조인) SELECT S.Sname, S.Dept, E.Grade FROM STUDENT S, ENROL E /* 테이블 이름 S와 E가 일시적으로 투플변수로 사용*/ WHERE S.Sno = E.Sno AND E.Cno = 'C413'; 금오공과대학 컴퓨터공학부 컴퓨터공학전공

동일 조인 (equi join) 릴레이션 학생과 등록의 동일 조인 예 학번 (Sno) 이름 (Sname) 학년 (Year) 학과 (Dept) 100 나 수 영 4 컴퓨터 200 이 찬 수 3 전기 300 정 기 태 1 400 송 병 길 500 박 종 화 2 산공 학생 (STUDENT) 학번 (Sno) 100 200 300 400 500 과목번호 (Cno) C413 E412 C123 C312 C324 성적 (Grade) A B C 중간성적 (Midterm) 90 95 85 75 80 65 기말성적 (Final) 등록 (ENROL) 금오공과대학 컴퓨터공학부 컴퓨터공학전공

동일 조인 (equi join) 학생.학번 이름 학년 학과 등록.학번 과목번호 성적 중간성적 기말성적 100 100 200 300 300 300 400 400 400 400 500 나수영 나수영 이찬수 정기태 정기태 정기태 송병길 송병길 송병길 송병길 박종화 4 4 3 1 1 1 4 4 4 4 2 컴퓨터 컴퓨터 전기 컴퓨터 컴퓨터 컴퓨터 컴퓨터 컴퓨터 컴퓨터 컴퓨터 산공 C413 E412 C123 C312 C324 C413 C312 C324 C413 E412 C312 A A B A C A A A B C B 90 95 85 90 75 95 90 95 80 65 85 95 95 80 95 75 95 95 90 85 75 80 금오공과대학 컴퓨터공학부 컴퓨터공학전공

릴레이션 학생과 등록의 자연 조인 예 학생 N등록 학번 이름 학년 학과 과목번호 성적 중간성적 기말성적 100 100 200 300 300 300 400 400 400 400 500 나수영 나수영 이찬수 정기태 정기태 정기태 송병길 송병길 송병길 송병길 박종화 4 4 3 1 1 1 4 4 4 4 2 컴퓨터 컴퓨터 전기 컴퓨터 컴퓨터 컴퓨터 컴퓨터 컴퓨터 컴퓨터 컴퓨터 산공 C413 E412 C123 C312 C324 C413 C312 C324 C413 E412 C312 A A B A C A A A B C B 90 95 85 90 75 95 90 95 80 65 85 95 95 80 95 75 95 95 90 85 75 80 금오공과대학 컴퓨터공학부 컴퓨터공학전공

데이타 검색 (5) FROM 절에 조인 명세 SELECT Sname, Dept, Grade FROM STUDENT JOIN ENROL ON (STUDENT.Sno=ENROL.Sno) WHERE ENROL.Cno = 'C413'; SELECT Sname, Dept, Grade FROM STUDENT JOIN ENROL USING(Sno) WHERE ENROL.Cno = 'C413'; SELECT Sname, Dept, Grade FROM STUDENT NATURAL JOIN ENROL WHERE ENROL.Cno = 'C413'; 금오공과대학 컴퓨터공학부 컴퓨터공학전공

자기 자신의 테이블에 조인하는 검색 SELECT S1.Sno, S2.Sno FROM STUDENT S1, STUDENT S2 WHERE S1.Dept = S2.Dept AND S1.Sno < S2.Sno; 같은 과에 존재하는 학생들 중에서 작은 학번과 큰 학번의 리스트 mysql> SELECT S1.Sno, S2.Sno -> FROM STUDENT S1, STUDENT S2 -> WHERE S1.Dept = S2.Dept -> AND S1.Sno < S2.Sno; +-----+-----+ | Sno | Sno | | 100 | 300 | | 100 | 400 | | 300 | 400 | 3 rows in set (0.00 sec) SELECT S1.Sno, S2.Sno FROM STUDENT S1, STUDENT S2 WHERE S1.Dept = S2.Dept AND S1.Sno < S2.Sno; 금오공과대학 컴퓨터공학부 컴퓨터공학전공

데이타 검색 (6) 집계 함수(aggregate function)를 이용한 검색 mysql> SELECT COUNT(*) AS '학생수' -> FROM STUDENT; +--------+ | 학생수 | | 5 | 1 row in set (0.02 sec) mysql> mysql> SELECT COUNT(DISTINCT Cno) -> FROM ENROL^KWHERE Sno = 300; +---------------------+ | COUNT(DISTINCT Cno) | | 3 | 1 row in set (0.00 sec) mysql> SELECT AVG(Midterm) AS '중간평균' -> FROM ENROL -> WHERE Cno = 'C413'; +----------+ | 중간평균 | | 88.3333 | 집계 함수(aggregate function)를 이용한 검색 집계 함수: COUNT, SUM, AVG, MAX, MIN SELECT COUNT(*) AS 학생수 FROM STUDENT; SELECT COUNT(DISTINCT Cno) FROM ENROL WHERE Sno = 300; SELECT AVG(Midterm) AS 중간평균 FROM ENROL WHERE Cno = ‘C413’; 금오공과대학 컴퓨터공학부 컴퓨터공학전공

데이타 검색 (7) GROUP BY를 이용한 검색 HAVING을 사용한 검색 SELECT Cno, AVG(Final) AS 기말평균 FROM ENROL GROUP BY Cno; HAVING을 사용한 검색 SELECT Cno, AVG(Final) AS 평균 FROM ENROL GROUP BY Cno HAVING COUNT(*) >= 3; SELECT Cno, AVG(Final) AS '기말평균' FROM ENROL GROUP BY Cno; SELECT Cno, AVG(Final) AS '평균' FROM ENROL GROUP BY Cno HAVING COUNT(*) >= 3; 금오공과대학 컴퓨터공학부 컴퓨터공학전공

데이타 검색 (8) 부속 질의문(Subquery)를 사용한 검색 부속 질의문 다른 질의문에 중첩(nested)되어 사용된 검색문 형태 : SELECT-FROM-WHERE-GROUP BY-HAVING 중첩 질의문 : 부속 질의문을 포함하고 있는 질의문 IN 다음에 사용 : 집합의 멤버십 연산자(∈)로 해석 SELECT Sname FROM STUDENT WHERE Sno IN (SELECT Sno FROM ENROL WHERE Cno = 'C413'); 금오공과대학 컴퓨터공학부 컴퓨터공학전공

데이타 검색 (9) 부속 질의문을 사용한 검색(cont’d) SELECT Sname FROM STUDENT WHERE Sno NOT IN (SELECT Sno FROM ENROL WHERE Cno = ‘C413’); SELECT Sname, Dept FROM STUDENT WHERE Dept = (SELECT Dept FROM STUDENT WHERE Sname = ‘정기태’); 금오공과대학 컴퓨터공학부 컴퓨터공학전공

부속 질의문을 사용한 검색(cont’d) 데이타 검색 (10) SELECT Sno, Cno FROM ENROL WHERE Final > ALL (SELECT Final FROM ENROL WHERE Sno = 500); 금오공과대학 컴퓨터공학부 컴퓨터공학전공

LIKE를 사용하는 검색 데이타 검색 (11) LIKE 서브 스트링 패턴(substring pattern) 비교 연산자 % 어떤 길이의 어떤 문자 스트링과 매칭 _ 문자 하나와 매칭 SELECT Cno, Cname FROM COURSE WHERE Cno LIKE 'C%'; 금오공과대학 컴퓨터공학부 컴퓨터공학전공

데이타 검색 (12) NULL을 사용한 검색 NULL 누락된 정보(missing information) 값은 있지만 모르는 값(unknown value) 해당되지 않는 값(unapplicable value) 의도적으로 유보한 값(withheld value) NULL이 추가된 3-값 논리(3-VL: 3-value logic) 논리값으로 보면 참(true)도 거짓(false)도 아닌 미정(unknown) AND T    F    U T F U F    F    F U    F    U OR T    F    U T F U T    T    T T    U    U NOT   T F U 금오공과대학 컴퓨터공학부 컴퓨터공학전공

데이타 검색 (13) NULL을 사용한 검색 (cont’d) SELECT Sno, Sname FROM STUDENT WHERE Dept IS NULL; “열_이름 IS [NOT] NULL”의 형식만 허용 널값 : 조건식에서 비교연산자와 같이 사용 → 항상 거짓 Year의 값이 널인 경우 다음은 모두 거짓이거나 불법 Year > 3 Year ≤ 3 Year = 3 Year ≠ 3 Year = NULL (불법적 형식) Year ≠ NULL (불법적 형식) 금오공과대학 컴퓨터공학부 컴퓨터공학전공

데이타 검색 (14) EXISTS를 사용하는 검색 과목 ‘C413’에 등록한 학생의 이름을 검색하라. SELECT Sname FROM STUDENT WHERE EXISTS (SELECT * FROM ENROL WHERE Sno = STUDENT.Sno AND Cno = 'C413'); Note : EXISTS 이하 SELECT 문이 참(공집합이 아님)일 때 본 SELECT 문을 실행 금오공과대학 컴퓨터공학부 컴퓨터공학전공

EXISTS를 사용하는 검색(cont’d) 데이타 검색 (15) EXISTS를 사용하는 검색(cont’d) SELECT Sname FROM STUDENT WHERE NOT EXISTS (SELECT * FROM ENROL WHERE Sno = STUDENT.Sno AND Cno = 'C413'); 금오공과대학 컴퓨터공학부 컴퓨터공학전공

데이타 검색 (16) UNION이 관련된 검색 SELECT Sno FROM STUDENT WHERE Year = 1 UNION SELECT Sno FROM ENROL WHERE Cno = 'C324'; Note : 중복되는 투플은 제거 금오공과대학 컴퓨터공학부 컴퓨터공학전공

데이타의 갱신 (1) 일반적인 형식 하나의 레코드 변경 복수의 레코드 변경 UPDATE 테이블 SET { 열_이름 = 산술식} ’+ [WHERE 조건]; 하나의 레코드 변경 UPDATE STUDENT SET Year = 2 WHERE Sno = 300; 복수의 레코드 변경 UPDATE COURSE SET Credit = Credit + 1 WHERE Dept = '컴퓨터'; 금오공과대학 컴퓨터공학부 컴퓨터공학전공

데이타의 갱신 (2) 부속 질의문을 이용한 변경 UPDATE ENROL SET Final = Final + 5 WHERE Sno IN ( SELECT Sno FROM STUDENT WHERE Dept = '컴퓨터'); UPDATE STUDENT SET Dept = (SELECT Dept FROM COURSE WHERE Cname = ‘데이타베이스’) WHERE Year = 4; 금오공과대학 컴퓨터공학부 컴퓨터공학전공

데이타의 삽입 (1) 일반 형식 INSERT INTO 테이블 [(열_이름_리스트)] VALUES (열값_리스트); INSERT INTO 테이블 [(열_이름_리스트)] SELECT문; 금오공과대학 컴퓨터공학부 컴퓨터공학전공

데이타의 삽입 (2) 레코드의 직접 삽입 INSERT INTO STUDENT(Sno, Sname, Year, Dept) VALUES (600, '박상철', 1, '컴퓨터'); INSERT INTO STUDENT VALUES (600, '박상철', 1, '컴퓨터'); INSERT INTO STUDENT(Sno, Sname, Year) VALUES (600, '박상철', 1); 금오공과대학 컴퓨터공학부 컴퓨터공학전공

데이타의 삽입 (3) 부속 질의문을 이용한 레코드 삽입 INSERT INTO COMPUTER(Sno, Sname, Year) SELECT Sno, Sname, Year FROM STUDENT WHERE Dept = '컴퓨터'; 금오공과대학 컴퓨터공학부 컴퓨터공학전공

데이타의 삭제 (1) 일반 형식 하나의 레코드 삭제 DELETE FROM 테이블 [WHERE 조건]; DELETE FROM STUDENT WHERE Sno = 100; Note : 기본키와 참조무결성 문제 금오공과대학 컴퓨터공학부 컴퓨터공학전공

데이타의 삭제 (2) 복수의 레코드 삭제 부속 질의문을 사용한 삭제 DELETE FROM ENROL; DELETE FROM ENROL WHERE Cno = 'C413' AND Final < 60 AND ENROL.Sno IN ( SELECT Sno FROM STUDENT WHERE Dept = '컴퓨터'); 금오공과대학 컴퓨터공학부 컴퓨터공학전공

SQL 뷰 하나 또는 둘 이상의 기본 테이블(base table)로부터 유도되어 만들어지는 가상 테이블 (Virtual table) 외부 스키마는 뷰와 기본 테이블들의 정의로 구성됨 기본 테이블을 들여다보는 '유리창'(window) 동적임 물리적인 구현이 아님 뷰의 정의만 시스템 카탈로그(SYSVIEWS)에 SELECT-FROM-WHERE의 형태로 저장됨 뷰에 대한 변경 → 테이블에 대한 변경 금오공과대학 컴퓨터공학부 컴퓨터공학전공

뷰의 생성 (1) 일반형식 CREATE VIEW 뷰_이름[(열_이름 리스트)] AS SELECT문 [WITH CHECK OPTION]; WITH CHECK OPTION : 갱신이나 삽입 연산 시 조건 확인 예 CREATE VIEW CSTUDENT(Sno, Sname, Year) AS SELECT Sno, Sname, Year FROM STUDENT WHERE Dept = '컴퓨터’ WITH CHECK OPTION; /* Dept != ‘컴퓨터’인 학생은 삽입 불가능*/ mysql> CREATE VIEW CSTUDENT(Sno, Sname, Year) -> AS SELECT Sno, Sname, Year -> FROM STUDENT -> WHERE Dept = '컴퓨터' -> WITH CHECK OPTION; Query OK, 0 rows affected (0.09 sec) mysql> show tables; +--------------------+ | Tables_in_sampledb | | course | | cstudent | | enrol | | enrol1 | | enrol2 | | enrol3 | | enrol4 | | enrol6 | | student | 9 rows in set (0.02 sec) mysql> select * from cstudent; +-----+--------+------+ | Sno | Sname | Year | | 100 | 나수영 | 4 | | 300 | 정기태 | 1 | | 400 | 송병길 | 4 | 3 rows in set (0.05 sec) mysql> 금오공과대학 컴퓨터공학부 컴퓨터공학전공

기본 테이블 학생(STUDENT)의 컴퓨터과 학생(CSTUDENT) 뷰 뷰의 생성 (2) 기본 테이블 학생(STUDENT)의 컴퓨터과 학생(CSTUDENT) 뷰 컴퓨터과 학생 (CSSTUDENT) 학번 (Sno) 이름 (Sname) 학년 (Year) 학과 (Dept) 100 나 수 영 4 컴퓨터 200 이 찬 수 3 전기 300 정 기 태 1 컴퓨터 400 송 병 길 4 컴퓨터 500 박 종 화 2 산공 금오공과대학 컴퓨터공학부 컴퓨터공학전공

뷰의 생성 (3) 예 (cont’d) CREATE VIEW DEPTSIZE(Dept, Tstdn) AS SELECT Dept, COUNT(*) FROM STUDENT GROUP BY Dept; AS SELECT : 열의 이름 상속 상속 불가한 경우나 열 이름이 중복될 경우 반드시 열 이름 명세 CREATE VIEW HONOR(Sname, Dept, Grade) AS SELECT STUDENT.Sname, STUDENT.Dept, ENROL.Final FROM STUDENT, ENROL WHERE STUDENT.Sno = ENROL.Sno AND ENROL.Final > 90; 두 개 이상 테이블 조인 mysql> CREATE VIEW HONOR(Sname, Dept, Grade) -> AS SELECT STUDENT.Sname, -> STUDENT.Dept, ENROL.Final -> FROM STUDENT, ENROL -> WHERE STUDENT.Sno = ENROL.Sno -> AND ENROL.Final > 90; Query OK, 0 rows affected (0.03 sec) mysql> select * from honor ; +--------+--------+-------+ | Sname | Dept | Grade | | 나수영 | 컴퓨터 | 95 | | 정기태 | 컴퓨터 | 95 | | 송병길 | 컴퓨터 | 95 | 4 rows in set (0.00 sec) mysql> 금오공과대학 컴퓨터공학부 컴퓨터공학전공

뷰의 생성 (4) 예 (cont’) CREATE VIEW COMHONOR AS SELECT Sname FROM HONOR WHERE Dept = '컴퓨터'; 정의된 뷰를 이용하여 또 다른 뷰 정의 금오공과대학 컴퓨터공학부 컴퓨터공학전공

뷰의 제거 (1) 일반형식 예 Note : “Propagated Destroys” DROP VIEW 뷰_이름 { RESTRICT | CASCADE }; RESTRICT 다른 곳에서 참조되고 있지 않는 한 데이타베이스에서 제거 CASCADE 이 뷰가 사용된 다른 모든 뷰나 제약 조건이 함께 제거 예 DROP VIEW DEPTSIZE RESTRICT; Note : “Propagated Destroys” 기본 테이블이 제거되면 그 위에 만들어진 인덱스나 뷰도 자동적으로 제거됨 금오공과대학 컴퓨터공학부 컴퓨터공학전공

뷰의 조작연산 (1) 기본 테이블에 사용 가능한 어떤 검색(SELECT)문도 뷰에 사용가능 변경(삽입, 삭제, 갱신) 연산은 제약 열 부분 집합 뷰(column subset view) CREATE VIEW STUDENT_VIEW1 AS SELECT Sno, Dept FROM STUDENT; → 기본키 포함 : 이론적으로 삽입, 삭제, 갱신, 검색 가능 CREATE VIEW STUDENT_VIEW2 AS SELECT Sname, Dept FROM STUDENT; → 기본키 불포함 : 이론적으로 검색만 가능 행 부분 집합 뷰(row subset view) CREATE VIEW STUDENT_VIEW3 AS SELECT Sno, Sname, Year, Dept FROM STUDENT WHERE Year=4; 금오공과대학 컴퓨터공학부 컴퓨터공학전공

뷰의 조작연산 (2) 조인 뷰(join view) CREATE VIEW HONOR(Sname, Dept, Grade) AS SELECT STUDENT.Sname, STUDENT.Dept, ENROL.Final FROM STUDENT, ENROL WHERE STUDENT.Sno = ENROL.Sno AND ENROL.Final > 95; 통계적 요약 뷰(statistical summary view) CREATE VIEW COSTAT(Cno, Avpoint) AS SELECT Cno, AVG(Midterm) FROM ENROL GROUP BY Cno; 금오공과대학 컴퓨터공학부 컴퓨터공학전공

뷰 이론적으로 변경이 가능한 뷰 실제로 변경이 가능한 뷰 뷰의 조작연산 (3) 뷰는 제한적인 갱신만 가능함 뷰에게 갱신 기능을 부가하는 것이 과연 올바른 것인가? 뷰 이론적으로 변경이 가능한 뷰 실제로 변경이 가능한 뷰 금오공과대학 컴퓨터공학부 컴퓨터공학전공

변경 연산이 허용되지 않는 경우 뷰의 조작연산 (4) ① 뷰의 열이 상수나 산술 연산자 또는 함수가 사용된 산술 식으로 만들어질 경우 ② 집계 함수(COUNT, SUM, AVG, MAX, MIN)가 관련되어 정의된 경우 ③ DISTINCT, GROUP BY, HAVING이 사용되어 정의된 경우 ④ 두 개 이상의 테이블이 관련되어 정의된 경우 ⑤ 변경할 수 없는 뷰를 기초로 정의된 경우 금오공과대학 컴퓨터공학부 컴퓨터공학전공

뷰의 장단점 뷰의 장점 뷰의 단점 논리적 독립성을 제공 (확장, 구조 변경) 데이타의 접근을 제어 (보안) 사용자의 데이타 관리를 단순화 여러 사용자에 다양한 데이타 요구를 지원 뷰의 단점 정의를 변경할 수 없음 삽입, 삭제, 갱신 연산에 제한이 많음 금오공과대학 컴퓨터공학부 컴퓨터공학전공

6-2. MySQL

http://www.mysql.com/  설치 다운로드 후 한글 코드 설정 암호 설정 나머지는 디폴트로 금오공과대학 컴퓨터공학부 컴퓨터공학전공

 수행 방법 콘솔 방식 GUI 방식 금오공과대학 컴퓨터공학부 컴퓨터공학전공

로그인과 종료 mysql –h 서버명 –u 유저 –p 비밀번호 데이타베이스명 quit or bye 금오공과대학 컴퓨터공학부 컴퓨터공학전공

기본 명령어 mysql> show databases; +--------------------+ | Database | | information_schema | | mysql | | sampledb | | test | 4 rows in set (0.03 sec) mysql> use sampledb; Database changed mysql> show tables; | Tables_in_sampledb | | course | | professor | | student | 3 rows in set (0.06 sec) mysql> desc course; +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | | Cno | int(10) unsigned | NO | PRI | NULL | | | Sno | int(10) unsigned | NO | PRI | NULL | | | mid | int(10) unsigned | NO | | NULL | | | final | varchar(45) | NO | | NULL | | 4 rows in set (0.00 sec) 금오공과대학 컴퓨터공학부 컴퓨터공학전공

함수 정리 mysql> select now(); +---------------------+ | now() | | 2009-05-11 13:12:35 | 1 row in set (0.00 sec) mysql> select now(), user(), version(), database(); +---------------------+----------------+------------------+------------+ | now() | user() | version() | database() | | 2009-05-11 13:13:01 | root@localhost | 5.1.34-community | sampledb | mysql> 금오공과대학 컴퓨터공학부 컴퓨터공학전공

사용자 관리 1 mysql> use mysql; Database changed mysql> insert into user(host, user, password) -> values('localhost','user4',''); ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value mysql> insert into mysql.user -> (user, host, password, ssl_cipher, x509_issuer, x509_subject) -> values('user4','%',password('1234'),'','',''); Query OK, 1 row affected (0.02 sec) mysql> select user from user; +-------+ | user | | user1 | | user2 | | user4 | | root | 4 rows in set (0.00 sec) 금오공과대학 컴퓨터공학부 컴퓨터공학전공

사용자 관리 2 mysql> flush privileges; Query OK, 0 rows affected (0.09 sec) mysql> update user -> set password = password('abcd') -> where user = 'root' -> ; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> 금오공과대학 컴퓨터공학부 컴퓨터공학전공

데이타베이스 생성 1 mysql> drop database sampledb; Query OK, 3 rows affected (0.16 sec) mysql> create database sampledb; Query OK, 1 row affected (0.00 sec) mysql> grant all on sampledb.* to user5@localhost identified by '1234'; Query OK, 0 rows affected (0.02 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> show databases; +--------------------+ | Database | | information_schema | | mysql | | sampledb | | test | 4 rows in set (0.00 sec) 금오공과대학 컴퓨터공학부 컴퓨터공학전공

데이타베이스 생성 2 mysql> select database(); +------------+ | database() | | NULL | 1 row in set (0.00 sec) mysql> use sampledb; Database changed | sampledb | mysql> 금오공과대학 컴퓨터공학부 컴퓨터공학전공

테이블 생성 mysql> create table enrol -> ( -> sno char(10) not null, -> cno char(10) not null, -> grade char(2), -> midterm integer, -> final integer -> ); Query OK, 0 rows affected (0.08 sec) mysql> show tables; +--------------------+ | Tables_in_sampledb | | enrol | 1 row in set (0.00 sec) mysql> desc enrol; +---------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | | sno | char(10) | NO | | NULL | | | cno | char(10) | NO | | NULL | | | grade | char(2) | YES | | NULL | | | midterm | int(11) | YES | | NULL | | | final | int(11) | YES | | NULL | | 5 rows in set (0.00 sec) mysql> drop table enrol; Query OK, 0 rows affected (0.03 sec) mysql> show tables; Empty set (0.00 sec) mysql> 금오공과대학 컴퓨터공학부 컴퓨터공학전공

실습 : 셸을 이용한 스키마 생성 금오공과대학 컴퓨터공학부 컴퓨터공학전공

실습: 셸을 이용한 데이터의 입력 금오공과대학 컴퓨터공학부 컴퓨터공학전공

실습: 셀을 이용한 SQL 문장의 실행 mysql> s o u r c e p r o f d a t a . s q l insert into prof (pcode,pname,pdept,pphone) values ('P001','김 구','컴퓨터공학과','0001'); values ('P002','안창호','컴퓨터공학과','0002'); values ('P003','이육사','국문학과','0003'); values ('P004','박종화','국문학과','0004'); values ('P005','심 훈','사학과','0005'); values ('P006','한용운','사학과','0006'); mysql> s o u r c e p r o f d a t a . s q l ERROR 1062: 중복된 입력 값 'P001': key 1 Query OK, 1 row affected (0.06 sec) Query OK, 1 row affected (0.00 sec) mysql> 금오공과대학 컴퓨터공학부 컴퓨터공학전공

스키마 생성 스크립트 create table enrol ( use sampledb; sno char(10) not null, cno char(10) not null, grade char(2), midterm integer, final integer, primary key(sno, cno), foreign key(sno) references student(sno) on delete cascade on update cascade, foreign key(cno) references course(cno) on update cascade ); show tables; desc course; desc enrol; desc student; use sampledb; drop table enrol; drop table course; drop table student; create table student ( sno char(10) not null, sname char(10) not null, year integer, dept char(40), primary key(sno) ); create table course ( cno char(10) not null, cname char(40) not null, credit integer, prname char(10), primary key(cno) use sampledb; drop table enrol; drop table course; drop table student; create table student ( sno char(10) not null, sname char(10) not null, year integer, dept char(40), primary key(sno) ); create table course cno char(10) not null, cname char(40) not null, credit integer, prname char(10), primary key(cno) create table enrol grade char(2), midterm integer, final integer, primary key(sno, cno), foreign key(sno) references student(sno) on delete cascade on update cascade, foreign key(cno) references course(cno) on update cascade show tables; desc course; desc enrol; desc student; 금오공과대학 컴퓨터공학부 컴퓨터공학전공

데이터 생성 스크립트 delete from enrol; insert into enrol values (100,'C413', 'A', 90, 95), (100,'E412', 'A', 95, 95), (200,'C123', 'B', 85, 80), (300,'C312', 'A', 90, 95), (300,'C324', 'C', 75, 75), (300,'C413', 'A', 95, 90), (400,'C312', 'A', 90, 95), (400,'C324', 'A', 95, 90), (400,'C413', 'B', 80, 85), (400,'E412', 'C', 65, 75), (500,'C312', 'B', 85, 80); select * from enrol; delete from student; insert into student(sno, sname, year, dept) values (100,'나수영', 4, '컴퓨터'), (200,'이찬수', 3, '전기'), (300,'정기태', 1, '컴퓨터'), (400,'송병길', 4, '컴퓨터'), (500,'박종화', 2, '산공'); select * from student; delete from course; insert into course values ('C123', '프로그래밍', 3, '컴퓨터', '김성국'), ('C312', '자료구조', 3, '컴퓨터', '황수관'), ('C324', '화일구조', 3, '컴퓨터', '이규찬'), ('C413', '데이타베이스', 3, '컴퓨터', '이일로'), ('E412', '반도체', 3, '전자', '홍봉진'); select * from course; delete from student; insert into student(sno, sname, year, dept) values (100,'나수영', 4, '컴퓨터'), (200,'이찬수', 3, '전기'), (300,'정기태', 1, '컴퓨터'), (400,'송병길', 4, '컴퓨터'), (500,'박종화', 2, '산공'); select * from student; delete from course; insert into course values ('C123', '프로그래밍', 3, '컴퓨터', '김성국'), ('C312', '자료구조', 3, '컴퓨터', '황수관'), ('C324', '화일구조', 3, '컴퓨터', '이규찬'), ('C413', '데이타베이스', 3, '컴퓨터', '이일로'), ('E412', '반도체', 3, '전자', '홍봉진'); select * from course; delete from enrol; insert into enrol values (100,'C413', 'A', 90, 95), (100,'E412', 'A', 95, 95), (200,'C123', 'B', 85, 80), (300,'C312', 'A', 90, 95), (300,'C324', 'C', 75, 75), (300,'C413', 'A', 95, 90), (400,'C312', 'A', 90, 95), (400,'C324', 'A', 95, 90), (400,'C413', 'B', 80, 85), (400,'E412', 'C', 65, 75), (500,'C312', 'B', 85, 80); select * from enrol; 금오공과대학 컴퓨터공학부 컴퓨터공학전공

MySQL Engines

MySQL & Storage Engines MySQL supports several storage engines that act as handlers for different table types In fact, the original term WAS table type Different storage engines offer different “properties” to tables in the areas of Transactional capabilities Locking Backup and recovery Optimization Pricing (licenses and support) A single database can support tables of any combination of storage engine 금오공과대학 컴퓨터공학부 컴퓨터공학전공

Displays status information about your server's storage engines SHOW ENGINES Displays status information about your server's storage engines mysql> show engines; +------------+---------+---------------------------------------------------------------+ | Engine | Support | Comment | | MyISAM | YES | Default engine as of MySQL 3.23 with great performance | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | | BerkeleyDB | NO | Supports transactions and page-level locking | | BLACKHOLE | NO | /dev/null storage engine (anything you write to it disappears) | | EXAMPLE | NO | Example storage engine | | ARCHIVE | YES | Archive storage engine | | CSV | NO | CSV storage engine | | ndbcluster | NO | Clustered, fault-tolerant, memory-based tables | | FEDERATED | NO | Federated MySQL storage engine | | MRG_MYISAM | YES | Collection of identical MyISAM tables | | ISAM | NO | Obsolete storage engine | 금오공과대학 컴퓨터공학부 컴퓨터공학전공

MyISAM (the default) Default engine in many MySQL installations (3.2x & 4) Pros Provides the best combination of performance and functionality Cons It lacks transaction capabilities Uses table-level locking Does not support foreign keys 금오공과대학 컴퓨터공학부 컴퓨터공학전공

InnoDB http://www.linuxplanet.com/linuxplanet/tutorials/6034/7/ Supports all of the database functionality of MyISAM engine PLUS Full transaction capabilities (with full ACID (Atomicity, Consistency, Isolation, and Durability) compliance) Row level locking of data Caching and indexing structure where both indexes and data are cached in memory as well as being stored on disk Supports foreign key and referential integrity constraints LinuxPlanet’s Advice InnoDB를 추천 The InnoDB Engine is provided by Innobase Oy http://www.linuxplanet.com/linuxplanet/tutorials/6034/7/ 금오공과대학 컴퓨터공학부 컴퓨터공학전공

ACID의 특성 원자성(atomicity) 일관성(consistency) 독립성(isolation) 트랜잭션 안의 모든 명령은 모두 수행되거나 하나도 수행되지 않는다. (all or nothing) 일관성(consistency) 트랜잭션 하나만 독립적으로 수행되면 데이터베이스의 일관성을 유지해야 한다는 의미. 독립성(isolation) 두 트랜잭션이 동시에 발생해도 서로 간섭이 일어나지 않는다.  동시성 제어기 지속성(durability) 트랜잭션이 commit 되고 나면 그 결과는 유지되어야 한다.  회복관리자 (recovery manager)

Use to create a named one, two or many field (search) index CREATE INDEX Use to create a named one, two or many field (search) index The index is created immediately The ordering of fields is significant Important: Any index may speed up retrieval, but always slows down inserts, deletes and some updates It also increases overhead! 금오공과대학 컴퓨터공학부 컴퓨터공학전공

CREATE INDEX Examples mysql> CREATE INDEX DName on -> DepartmentCopy(Name); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> CREATE UNIQUE INDEX DMgr on -> DepartmentCopy(Manager); Query OK, 4 rows affected (0.00 sec) mysql> CREATE INDEX DLoco on -> DepartmentCopy(Location, Name); Primary Key 와 Unique 인덱스    - Primary Key는 NULL을 포함 할 수 없음    - 오직 하나의 Primary Key는 한 테이블에만 허락됨.    - Primary Key는 유니크(unique) 인덱스 타입임    - 유니크(unique) 인덱스가 항상 Primary Key는 아님.    - NOT NULL과 PRIMARY KEY 키워드 사용   금오공과대학 컴퓨터공학부 컴퓨터공학전공

Showing Indexes (1 of 3) mysql> show indexes from departmentcopy; *************************** 1. row ****** Table: departmentcopy Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: DeptNum Collation: A Cardinality: 4 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: …… 금오공과대학 컴퓨터공학부 컴퓨터공학전공

To remove an index: Removing an Index mysql> DROP INDEX DMgr on DepartmentCopy; Query OK, 4 rows affected (0.17 sec) Records: 4 Duplicates: 0 Warnings: 0 금오공과대학 컴퓨터공학부 컴퓨터공학전공

과제 텍스트 북에 있는 모든 SQL 문장을 MySQL로 입력하고 결과를 출력하여 제출할 것 스크린 캡쳐하지 말고 텍스트로 캡쳐하여 정리할 것 범위: 6.1, 6.2, 6.3 금오공과대학 컴퓨터공학부 컴퓨터공학전공

6-3 Embedded SQL 및 JDBC

Agenda Database Access with JDBC More Details References Installations Connecting and querying the database Complete example More Details References

Embedded SQL Embedded SQL(삽입 SQL) 중요성 종류 이중 모드(dual mode) 원리 프로그래머의 주요 수단 종류 Static Embedded SQL Dynamic Embedded SQL Static과 Dynamic의 차이점은? 임베디드에서 Dynamic SQL을 사용한다는 의미는? 이중 모드(dual mode) 원리 터미널에서 대화식으로 사용할 수 있는 모든 SQL 문 → 응용 프로그램(Interactive and Embedded form)에서 사용 가능

Static SQL 명령문 앞에 EXEC SQL을 붙임 삽입 SQL 실행문은 호스트 실행문이 나타나는 어느 곳에서나 사용 가능 Embedded SQL 포함하는 응용 프로그램의 특징 명령문 앞에 EXEC SQL을 붙임 삽입 SQL 실행문은 호스트 실행문이 나타나는 어느 곳에서나 사용 가능 SQL문에 사용되는 호스트 변수는 콜론(:)을 앞에 붙임 EXEC SQL DECLARE문으로 사용할 테이블을 선언 호스트변수 SQLSTATE를 포함 피드백 정보 SQLSTATE = “000000” : 성공 ≠ “000000” : 경고 (warning) 또는 에러 호스트 변수와 대응하는 필드의 데이타 타입이 일치

Static SQL 호스트 변수와 데이타베이스 필드의 이름은 같아도 됨 SQLSTATE 변수에 반환된 값 검사 기본 형태 호스트 변수와 데이타베이스 필드의 이름은 같아도 됨 SQLSTATE 변수에 반환된 값 검사 응용 프로그램에서의 삽입 SQL EXEC SQL BEGIN DECLARE SECTION; int sno; char sname[21]; char dept[7]; char SQLSTATE[6]; EXEC SQL END DECLARE SECTION; sno = 100; EXEC SQL SELECT Sname, Dept INTO :sname, :dept FROM STUDENT WHERE Sno = :sno; IF SQLSTATE = '000000' THEN ... ; ELSE ... ;

커서가 필요 없는 데이타 연산 (1) 단일 레코드 검색(Singleton SELECT) 갱신 EXEC SQL SELECT Sname, Dept INTO :sname, :dept FROM STUDENT WHERE Sno = :sno; 갱신 EXEC SQL UPDATE ENROL SET Final = Final + :new WHERE Cno = 'C413';

커서가 필요 없는 데이타 연산 (2) 삭제 삽입 EXEC SQL DELETE FROM ENROL WHERE Sno = :sno; 삽입 EXEC SQL INSERT INTO STUDENT(Sno,Sname,Dept) VALUES (:sno,:sname,:dept);

커서 커서(cursor) SELECT 문과 호스트 프로그램 사이를 연결 활동 세트(active set) : SELECT 문으로 검색된 여러 개의 레코드 실행 시에는 활동 세트에 있는 레코드 하나를 지시

커서 복수 레코드의 검색 예 EXEC SQL DECLARE C1 CURSOR FOR /*커서 C1의 정의*/ SELECT Sno, Sname, Year FROM STUDENT WHERE Dept = :dept; EXEC SQL OPEN C1; /*질의문의 실행*/ DO /* C1으로 접근되는 모든 STUDENT 레코드에 대해 */ EXEC SQL FETCH C1 INTO :sno,:sname,:year; /*다음 학생 레코드의 채취*/ . . . . . . END; EXEC SQL CLOSE C1; /*커서 c1의 활동 종료 */

커서 변경 삭제 EXEC SQL UPDATE STUDENT SET Year = :year WHERE CURRENT OF C1; You use the CURRENT OF cursor_name clause in a DELETE or UPDATE statement to refer to the latest row fetched from the named cursor. 삭제 EXEC SQL DELETE FROM STUDENT

Dynamic SQL 온라인 응용(프로그램) 온라인 응용의 수행 과정 터미널로 데이타베이스 접근을 지원하는 응용 프로그램 터미널에서 명령문을 접수 입력된 명령문 분석 데이타베이스에 적절한 SQL문으로 지시 터미널에 메시지/결과를 돌려보냄

Dynamic SQL (2) 기본 명령어 PREPARE EXECUTE 예 varchar dynamicSQL[256]; //호스트 변수 dynamicSQL = “DELETE FROM ENROL WHERE Cno = 'C413' AND Final  60”; EXEC SQL PREPARE objSQL FROM :dynamicSQL; EXEC SQL EXECUTE objSQL;

Dynamic SQL (3) 목적 코드로 변환시키는 PREPARE 문에는 어떤 종류의 SQL문도 포함 가능 Note : 스트링으로 표현되는 SQL문에는 호스트 변수 사용 불가 → 물음표(?) 매개변수의 사용 :cno와 :grade의 값이 DELETE 문의 Cno와 Final 값으로 전달 dynamicSQL = “DELETE FROM ENROL WHERE Cno = ? AND Final  ? ”; EXEC SQL PREPARE objSQL FROM : dynamicSQL; . . . . . . . . cno=‘C413’; /* ? ? 에 대응하는 이 값들은 */ grade=60; /* 터미널로부터 입력 받을 수 있음*/ EXEC SQL EXECUTE objSQL USING :cno,:grade;

Dynamic Embedded SQL의 예: JDBC 자바와 RDB의 표준 프로그래밍 인터페이스 ODBC 이전에는 언어별로 DBMS별로 Embedded SQL의 사용방법이 모두 다름 ODBC의 자바 버전 산업 표준 실제로 이것을 주로 사용 C Oracle C Oracle java DB2 java ODBC DB2 Cobol SQL-Server Cobol SQL-Server

전체 설치 Four stages: Install and configure the database Download and configure the JDBC Create a connection to the database Access the database

Database Install Download the MySQL database from: http://www.mysql.com/downloads/ Install it Create a specific database: create database mytest; Create a user account: grant all on mytest.* to eran identified by ‘1234’

JDBC Install Download Connector/J from: http://www.mysql.com/downloads/api-jdbc.html Unzip it In order the library to be found, either: Copy the .jar file to: $JAVA_HOME/jre/lib/ext Or, add a classpath to the JDBC: C:\> set CLASSPATH=\path\to\mysql-connector-java-[version]-bin.jar;%CLASSPATH%

JDBC Programming Steps Connect Register the driver Create a connection to the database Query Create a statement Query the database Process Results Get a result set Assign results to Java variables Close Close the result set Close the statement Close the connection

Example – Database Management mysql> create database mytest; mysql> use mytest; mysql>grant all on *.* to eran@localhost identified by '1234'; mysql>create table phones (name varchar(255) not null unique key, phone varchar(25) not null); mysql>describe phones; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | | name | varchar(255) | | PRI | | | | phone | varchar(25) | | | | | mysql> insert into phones values ('Eran Toch', '+972-4-9831894'); Query OK, 1 row affected (0.11 sec) Creating the DB Creating user account Creating the ‘phones’ table Is everything alright? Let’s see… Inserting some data

Example – Test Client public class Test { public static void main(String[] args) { SQLConnect connect = new SQLConnect(); connect.createConnection(); String allPhones = connect.getPhones(); connect.closeConnection(); System.out.println("phones:"); System.out.println(allPhones); } Output phones: +972-4-9831894

Example – Connection Importing java.sql.* that contains all the classes we need import java.sql.*; public class SQLConnect { Connection conn = null; Statement stmt = null; ResultSet rs = null; public SQLConnect(){} public void createConnection(){ try{ Class.forName("com.mysql.jdbc.Driver").newInstance(); conn = DriverManager.getConnection ("jdbc:mysql://localhost/mytest?user=testmaster&password=1234"); } catch (SQLException E){ System.out.println(E); Connection, Statement and ResultSet are defined as class variables Dynamically loading the specific JDBC driver. The runtime environment must know where the library is located! Connecting to the database using the url

Example – Access and Query Creating a statement public String getPhones(){ String output = ""; try { stmt = conn.createStatement(); rs = stmt.executeQuery("SELECT * FROM phones"); if (rs != null){ while (rs.next()){ output += rs.getString("phone") + "\n"; } catch (Exception E){ System.out.println(E.getMessage()); Creating a ResultSet, based on a SQL statement Going through the ResultSet by using rs.next(). Remember – you need to call the next method before you start reading from the ResultSet Reading a field from the ResultSet

Example – Cleaning off finally { if (rs != null) { try { rs.close(); } catch (SQLException sqlEx) {} rs = null; if (stmt != null) { stmt.close(); stmt = null; return output; Cleaning off is best done in the “finally” clause Cleaning off ResultSet Cleaning off Statement, after the ResultSet public void closeConnection(){ if (conn != null){ try { conn.close(); } catch (SQLException sqlEx){} conn = null;

Executing SQL (1/2) Statement object Can be obtained from a Connection object Sends SQL to the database to be executed Statement has three methods to execute a SQL statement: executeQuery() for QUERY statements Returns a ResultSet which contains the query results executeUpdate() for INSERT, UPDATE, DELETE, or DDL statements Returns an integer, the number of affected rows from the SQL execute() for either type of statement Statement statement = connection.createStatement();

Executing SQL (2/2) Execute a select statement Execute a delete statement Statement stmt = conn.createStatement(); ResultSet rset = stmt.executeQuery ("select RENTAL_ID, STATUS from ACME_RENTALS"); Statement stmt = conn.createStatement(); int rowcount = stmt.executeUpdate ("delete from ACME_RENTAL_ITEMS where rental_id = 1011"); 앞에서 설명한 method들의 예제 입니다.

The PreparedStatement Object A PreparedStatement object holds precompiled SQL statements Use this object for statements you want to execute more than once A PreparedStatement can contain variables (?) that you supply each time you execute the statement // Create the prepared statement PreparedStatement pstmt = con.prepareStatement(“ UPDATE table1 SET status = ? WHERE id =?”) // Supply values for the variables pstmt.setString (1, “out”); pstmt.setInt(2, id); // Execute the statement pstmt.executeUpdate();

Transactions and JDBC (1/2) Transaction: more than one statement that must all succeed (or all fail) together Ex) updating several tables due to customer purchase If one fails, the system must reverse all previous actions Also can’t leave DB in inconsistent state halfway through a transaction COMMIT = complete transaction ROLLBACK = cancel all actions

Transactions and JDBC (2/2) The connection has a state called AutoCommit mode If AutoCommit is true, then every statement is automatically committed If AutoCommit is false, then every statement is added to an ongoing transaction Default: true con.setAutoCommit(false); try { PreparedStatement pstmt = con.prepareStatement( "update BankAccount set amount = amount + ? where accountId = ?"); pstmt.setInt(1,-100); pstmt.setInt(2, 13); pstmt.executeUpdate(); pstmt.setInt(1, 100); pstmt.setInt(2, 72); con.commit(); catch (SQLException e) { con.rollback(); } 트랜젝션을 사용하기 위해서는 connection객체의 setAutoCommit(false)를 호출하여야 합니다. AutoCommit 모드가 true이면 질의가 전달되는 즉시 바로 실행됩니다. AutoCommit 모드가 false인 경우 commit()을 호출해야 commit되지 않은 질의들이 모두 수행됩니다. 이 예제에서는 질의 수행 도중 예외가 발생하면 rollback()을 호출하여 질의 결과를 되돌립니다. 이렇게 해서 트랜젝션의 atomicity를 보장 할 수 있습니다.

References Exception handling in the Java tutorial: http://java.sun.com/docs/books/tutorial/essential/exceptions/index.html JDBC Tutorial: http://java.sun.com/docs/books/tutorial/jdbc/ MySQL Tutorial: http://www.mysql.com/documentation/mysql/bychapter/ MySQL JDBC Connector/J Tutorial: http://www.mysql.com/documentation/connector-j/ Using Microsoft Access with JDBC: http://www.javaworld.com/javaworld/javaqa/2000-09/03-qa-0922-access.html