06. SQL 명지대학교 ICT 융합대학 김정호.

Presentation on theme: "06. SQL 명지대학교 ICT 융합대학 김정호."— Presentation transcript:

1 06. SQL 명지대학교 ICT 융합대학 김정호

2 SQL(Structured Query Language) 개요
SEQUEL(Structured English QUEry Language) 1974년, IBM 연구소에서 발표 IBM은 'SYSTEM R' 의 인터페이스로 설계 구현 실험적 관계 데이타베이스 시스템의 인터페이스 SQL 표준화 1986년, SQL-86 또는 SQL1 1992년 개정, SQL/92, SQL-92 또는 SQL2 1999년 : SQL3, SQL-99 2003년 : SQL4, SQL-2003, 객체 개념을 지원하는 기능 추가 현재 미국 표준 연구소(ANSI)와 국제 표준 기구(ISO)에서 관계 데이타베이스의 표준 언어로 채택 DB2와 SQL/DS, ORACLE, INFORMIX, SYBASE 등과 같은 모든 주요 제품에서 채택

3 SQL(Structured Query Language) 개요
구조화된 질의어 종합 데이타베이스 언어 역할 단순히 검색만을 위한 데이타 질의어가 아님 데이타 정의어(DDL), 데이타 조작어(DML), 데이타 제어어(DCL)의 기능 모두 제공

4 SQL DDL(데이터 정의문) 스키마와 카탈로그 스키마(schema) 카탈로그(catalog)
하나의 응용(사용자)에 속하는 테이블과 기타 구성요소 등의 그룹 포함 내용 스키마 이름(schema name), 스키마를 소유하는 허가권자 (authorization identifier), 테이블, 뷰, 도메인, 기타 내용에 대한 기술자(descriptor) 예제 CREATE SCHEMA UNIVERSITY AUTHORIZATION KIM ; 카탈로그(catalog) SQL 시스템 내에서의 한 스키마 집합 하나의 특별한 스키마 Information schema를 포함 그 카탈로그에 있는 모든 스키마에 대한 정보를 제공

5 도메인 정의문 일반 형식 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: 이 도메인을 참조하고 있는 뷰나 제약조건도 삭제

6 도메인 정의문 데이타 타입 숫자 문자 스트링 비트 스트링 날짜 시간 INT(EGER), 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 포함

7 기본 테이블의 생성 테이블의 종류 기본 테이블(base table) 가상 테이블 (virtual table)
CREATE TABLE 문으로 만들어지는 테이블 DBMS의 화일로 생성되고 저장 가상 테이블 (virtual table) CREATE VIEW 문으로 만들어지는 테이블 어떤 기본 테이블로부터 유도되어 만들어지는 테이블 독자적으로 존재 불가 임시 테이블(temporary table) 질의문 처리 과정의 중간 결과로 만들어지는 테이블 DDL 문으로 만들어지는 것이 아님

8 기본 테이블의 생성 일반형식 CREATE TABLE 테이블이름
({열이름 데이타타입 [NOT NULL] [DEFAULT 값],}+ [PRIMARY KEY (열이름_리스트),] {[UNIQUE (열이름_리스트),]}* {[FOREIGN KEY(열이름_리스트) REFERENCES 기본테이블[(열이름_리스트)] [ON DELETE 옵션] [ON UPDATE 옵션] ,]} * [CONSTRAINT 이름] [CHECK(조건식)]);

9 기본 테이블의 생성 PRIMARY KEY UNIQUE NOT NULL FOREIGN KEY
기본 키와 개체 무결성 제약조건을 명세 UNIQUE 대체 키를 명세, 후보 키 NOT NULL 애트리뷰트 값 제약조건 FOREIGN KEY 외래 키와 참조 무결성 제약조건을 명세 SQL에서는 참조된 테이블의 후보 키도 외래 키로 될 수 있음

10 기본 테이블의 생성 옵션에는 SET NULL, SET DEFAULT, CASCADE가 있음 ON DELETE SET NULL
ON UPDATE CASCADE 참조 투플(열 값)이 갱신되면 갱신된 값이 파급적으로 갱신됨 CHECK: 제약조건을 명세

11 기본 테이블의 생성 예 CREATE TABLE ENROL ( Sno INTEGER NOT NULL, Cno CHAR(6) NOT NULL, Grade INTEGER, PRIMARY KEY(Sno,Cno), FOREIGN KEY(Sno) REFERENCES STUDENT(sno) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY(Cno) REFERENCES COURSE ON DELETE CASCADE ON UPDATE CASCADE, CHECK(Grade  0 AND Grade  100));

12 기본 테이블의 제거와 변경 기본 테이블의 제거 스키마 제거 일반 형식
DROP TABLE 기본_테이블_이름 { RESTRICT | CASCADE } ; RESTRICT: 참조하는 뷰나 제약조건이 있으면 실행 실패 CASCADE: 참조 뷰나 제약조건도 모두 삭제 DROP TABLE COURSE CASCADE; 스키마 제거 일반형식 DROP SCHEMA 스키마_이름 { RESTRICT | CASCADE }; DROP SCHEMA UNIVERCITY CASCADE;

13 기본 테이블의 제거와 변경 기본 테이블의 변경 일반형식 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; DROP 열_이름 RESTRICT: 참조하는 뷰나 제약조건이 있으면 실행 실패 CASCADE: 참조 뷰나 제약조건도 모두 삭제

14 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)

15 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)

16 SQL DML(데이터 조작문) 데이터 검색 기본 구조 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

17 데이터 검색 폐쇄 시스템(closed system) SQL과 이론적 relational model의 차이점
테이블 처리 결과가 또 다시 테이블이 되는 시스템 중첩 질의문(nested query)을 구성할 수 있는 이론적 기초 SQL과 이론적 relational model의 차이점 SQL의 테이블 한 테이블 내에 똑같은 레코드(행) 중복 가능 기본 키를 반드시 가져야 하는 것은 아님 이론상 SQL의 테이블은 투플의 집합이 아님 같은 원소의 중복을 허용하는 다중 집합(multiset) 또는 백(bag) → DISTINCT 명세 : 집합과 같은 결과를 만듦

18 데이터 검색 일반적인 형식 검색 결과에 레코드의 중복 제거 테이블의 열 전부를 검색하는 경우
SELECT [ALL | DISTINCT] 열_리스트 FROM 테이블_리스트 [WHERE 조건] [GROUP BY 열_리스트 [HAVING 조건]] [ORDER BY 열_리스트 [ASC | DESC]]; 검색 결과에 레코드의 중복 제거 SELECT DISTINCT Dept FROM STUDENT; 테이블의 열 전부를 검색하는 경우 SELECT * FROM STUDENT;

19 데이터 검색 조건 검색 순서를 명세하는 검색 산술식과 문자 스트링이 명세된 검색(이름 변경)
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';

20 데이터 검색 복수 테이블로부터의 검색(조인) 자기 자신의 테이블에 조인하는 검색
SELECT S.Sname, S.Dept, E.Grade FROM STUDENT S, ENROL E WHERE S.Sno = E.Sno AND E.Cno = 'C413'; 자기 자신의 테이블에 조인하는 검색 같은 학과 학생의 학번을 쌍으로 검색하라. 단 첫 번째 학번은 두 번째 학번 보다 작다. SELECT S1.Sno, S2.Sno FROM STUDENT S1, STUDENT S2 WHERE S1.Dept = S2.Dept AND S1.Sno < S2.Sno;

21 데이터 검색 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';

22 데이터 검색 집계 함수(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’;

23 데이터 검색 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;

24 데이터 검색 LIKE를 사용하는 검색 LIKE % _
서브 스트링 패턴(substring pattern) 비교 연산자 % 어떤 길이의 어떤 문자 스트링도 관계 없음을 의미 _ 문자 하나를 의미 SELECT Cno, Cname FROM COURSE WHERE Cno LIKE 'C%';

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

26 데이터 갱신 부속 질의문을 이용한 변경 UPDATE ENROL SET Final = Final WHERE Sno IN ( SELECT Sno FROM STUDENT WHERE Dept = '컴퓨터'); UPDATE STUDENT SET Dept = (SELECT Dept FROM COURSE WHERE Cname = ‘데이타베이스’) WHERE Year = 4;

27 데이터 삽입 일반 형식 INSERT INTO 테이블 [(열_이름_리스트)] VALUES (열값_리스트);
INSERT INTO 테이블 [(열_이름_리스트)] SELECT 문;

28 데이터 삽입 레코드의 직접 삽입 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);

29 데이터 삽입 부속 질의문을 이용한 레코드 삽입 INSERT INTO COMPUTER(Sno, Sname, Year) SELECT Sno, Sname, Year FROM STUDENT WHERE Dept = '컴퓨터';

30 데이터 삭제 일반 형식 하나의 레코드 삭제 Note : 참조 무결성 문제 발생 가능 DELETE FROM 테이블
[WHERE 조건]; 하나의 레코드 삭제 FROM STUDENT WHERE Sno = 100; Note : 참조 무결성 문제 발생 가능

31 데이터 삭제 복수의 레코드 삭제 부속 질의문을 사용한 삭제 DELETE FROM ENROL;
DELETE FROM ENROL WHERE Cno = 'C413' AND Final < AND ENROL.Sno IN (SELECT Sno FROM STUDENT WHERE Dept = '컴퓨터');

32 Question ?