4.2 SQL 개요 SQL 개요 SQL은 IBM 연구소에서 1974년에 System R이라는 관계 DBMS 시제품을 연구할 때 관계 대수와 관계 해석을 기반으로, 집단 함수, 그룹화, 갱신 연산 등을 추가하여 개발된 언어 1986년에 ANSI(미국 표준 기구)에서 SQL.

Presentation on theme: "4.2 SQL 개요 SQL 개요 SQL은 IBM 연구소에서 1974년에 System R이라는 관계 DBMS 시제품을 연구할 때 관계 대수와 관계 해석을 기반으로, 집단 함수, 그룹화, 갱신 연산 등을 추가하여 개발된 언어 1986년에 ANSI(미국 표준 기구)에서 SQL."— Presentation transcript:

1 4.2 SQL 개요 SQL 개요 SQL은 IBM 연구소에서 1974년에 System R이라는 관계 DBMS 시제품을 연구할 때 관계 대수와 관계 해석을 기반으로, 집단 함수, 그룹화, 갱신 연산 등을 추가하여 개발된 언어 1986년에 ANSI(미국 표준 기구)에서 SQL 표준을 채택함으로써 SQL이 널리 사용되는데 기여 사용자는 SQL을 사용하여 관계 데이터베이스에 릴레이션을 정의하고, 관계 데이터베이스에서 정보를 검색, 갱신하며, 여러 가지 무결성 제약조건들을 명시할 수 있음 SQL은 현재 DBMS 시장에서 관계 DBMS가 압도적인 우위를 차지하는데 중요한 요인의 하나 다양한 상용 관계 DBMS마다 지원하는 SQL 기능에 다소 차이가 있음 본 책에서는 SQL2를 따름 4장. 관계 대수와 SQL

2 4.2 SQL 개요(계속) SQL 개요(계속) SQL은 비절차적 언어(선언적 언어)이므로 사용자는 자신이 원하는 바(what)만 명시하며, 원하는 것을 처리하는 방법(how)을 명시하지는 않음 관계 DBMS는 사용자가 입력한 SQL문을 번역하여 사용자가 요구한 데이터를 찾는데 필요한 모든 과정을 담당 두 가지 인터페이스 대화식 SQL(interactive SQL), 내포된 SQL(embedded SQL) SQL의 구성요소 데이터 정의어, 데이터 조작어, 데이터 제어어 4장. 관계 대수와 SQL

3 4.3 데이터 정의어와 무결성 제약조건 4장. 관계 대수와 SQL

4 4.3 데이터 정의어와 무결성 제약조건(계속) 데이터 정의어 스키마의 생성과 제거
SQL2에서는 동일한 데이터베이스 응용에 속하는 릴레이션, 도메인, 제약조건, 뷰, 권한 등을 그룹화하기 위해서 스키마 개념을 지원 CREATE SCHEMA MY_DB AUTHORIZATION kim; 이제부터 스키마 내에 릴레이션 등을 생성할 수 있다 DROP SCHEMA MY_DB RESTRICT: 구성요소 포함 시 거절(디폴트) DROP SCHEMA MY_DB CASCADE: 스키마 내 모든 구성요소 제거 CREATE SCHEMA MovieSchema CREATE TABLE MovieStar ... CREATE VIEW MovieProd ... CREATE ASSERTION RichPresident ... 4장. 관계 대수와 SQL

5 4.3 데이터 정의어와 무결성 제약조건(계속) 릴레이션 정의 4장. 관계 대수와 SQL

6 4.3 데이터 정의어와 무결성 제약조건(계속) 4장. 관계 대수와 SQL

7 4.4 SELECT문(계속) 날짜와 시간 날짜와 시간은 특별한 데이터 형
날짜 상수는 DATE라는 키워드 다음에 인용부에 둘러싸인 특별한 형식의 문자열로 표현 DATE ‘ ’ (5/14/1948 , 14 May 1948) 시간 상수는 유사하게 TIME 키워드 다음에 인용부로 둘러싸인 문자열로 표현 TIME ’15:00:02.5’ 날짜와 시간을 겸한 TIMESTAMP TIMESTAMP ‘ :00:00’ 숫자나 문자열 비교에서 사용하는 것과 유사한 비교 연산을 날짜나 시간을 비교할 때 사용 4장. 관계 대수와 SQL

8 4.3 데이터 정의어와 무결성 제약조건(계속) 릴레이션 제거 DROP TABLE DEPARTMENT RESTRICT or CASCADE; ALTER TABLE ALTER TABLE EMPLOYEE ADD PHONE CHAR(13); 속성 추가,삭제, 제약조건 추가,삭제, 속성 디폴트값 지정,제거 인덱스 생성 CREATE UNIQUE INDEX EMPINDEX ON EMPLOYEE(EMPNO); DROP INDEX EMPINDEX 도메인 생성 CREATE DOMAIN DEPTNAME CHAR(10) DEFAULT ‘개발’; 한번 정의된 도메인은 여러 테이블에서 그 도메인 이름만으로 일반 데이터 타입과 같이 사용 DROP DOMAIN DEPTNAME RESTRICT or CASCADE 4장. 관계 대수와 SQL

9 4.3 데이터 정의어와 무결성 제약조건(계속) 제약조건 4장. 관계 대수와 SQL

10 4.3 데이터 정의어와 무결성 제약조건(계속) 참조 무결성 제약조건 유지 무결성 제약조건의 추가 및 삭제
ON DELETE NO ACTION ON DELETE CASCADE ON DELETE SET NULL ON DELETE SET DEFAULT ON UPDATE NO ACTION ON UPDATE CASCADE ON UPDATE SET NULL ON UPDATE SET DEFAULT 무결성 제약조건의 추가 및 삭제 ALTER TABLE STUDENT ADD CONSTRAINT STUDENT_PK PRIMARY KEY (STNO); ALTER TABLE STUDENT DROP CONSTRAINT STUDENT_PK; 4장. 관계 대수와 SQL

11 4.3 데이터 정의어와 무결성 제약조건(계속) 4장. 관계 대수와 SQL

12 4.3 데이터 정의어와 무결성 제약조건(계속) 속성기반 CHECK 제약조건
SQL 질의에서 WHERE절 다음에 오는 조건들 어느 것이나 가능 속성기반 CHECK 제약조건은 튜플이 해당 속성에 대하여 새로운 값을 가질 때마다 조사된다 예제 : 사장 번호가 적어도 6자리 숫자이어야 한다고 하자 presC# INT REFERENCES MovieExec(cert#) CHECK (presC# >= ) 조사될 속성 이외의 다른 요소가 변경될 때 복잡한 조건에 오류가 생길 수 있다 예제 : 참조하는 값이 존재해야 한다는 속성기반 CHECK 제약조건을 통해 참조 무결성 제약조건과 유사한 것을 만들어 본다면 Studio(name, address, presC#) , MovieExec(name, address, cert#, netWorth) presC# INT CHECK (presC# IN (SELECT cert# FROM MovieExec)) 릴레이션 MovieExec 에서 한 스튜디오 사장에 관한 튜플을 삭제할 경우, 위의 CHECK 제약조건에서는 이를 알 수 없다 4장. 관계 대수와 SQL

13 4.3 데이터 정의어와 무결성 제약조건(계속) 튜플기반 CHECK 제약조건 테이블 R 의 튜플에 대한 제약조건을 선언하려면
CREATE TABLE 문을 통해 테이블을 정의할 때, 키워드 CHECK 다음에 조건들을 괄호로 묶어 정의 WHERE 절에 나올 수 있는 어떤 조건들도 가능 튜플기반 CHECK 제약조건은 튜플이 삽입되거나 갱신될 때 조사된다 튜플기반 CHECK는 속성기반 CHECK와 마찬가지로 다른 릴레이션에 감춰져 있다 예제 : 다음의 제약조건은 배우가 남성이면, 이름이 ‘Ms.’로 시작되어서는 안된다 CREATE TABLE MovieStar ( name CHAR(30) PRIMARY KEY, address VARCHAR(255), gender CHAR(1), birthdate DATE CHECK (gender=’F’ OR name NOT LIKE ‘Ms.%)); 4장. 관계 대수와 SQL

14 4.3.5 데이터 조작어 4장. 관계 대수와 SQL

15 4.4 SELECT문 SELECT문 관계 데이터베이스에서 정보를 검색하는 SQL문으로 가장 자주 사용됨
관계 대수의 selection, projection, 조인 등을 결합한 것 select A1 A2 ... An {projection} from r1 r2 ... rm {Cartesian product} where P {selection predicate} 여러 질의들의 결과를 보이기 위해서 그림 4.8의 관계 데이터베이스 상태를 사용함 4장. 관계 대수와 SQL

16 4.4 SELECT문(계속) 기본적인 SQL 질의 개념적 순서 4장. 관계 대수와 SQL
SELECT절과 FROM절만 필수적인 절이고, 나머지는 선택 사항 개념적 순서 FROM 절 rel 들의 카티션 곱 WHERE 절 조건에 맞는 투플들 검색 GROUP BY 절로 2번 결과 투플들 그룹화 HAVING 절로 각 그룹에 조건 적용해 일부 그룹 검색 남은 그룹에 집단 함수 적용하고 SELECT 절에 열거된 속성만 ORDER BY 정렬 4장. 관계 대수와 SQL

17 4.4 SELECT문(계속) 별칭(alias) 릴레이션의 모든 애트리뷰트나 일부 애트리뷰트들을 검색
서로 다른 릴레이션에 동일한 이름을 가진 애트리뷰트가 속해 있을 때 애트리뷰트의 이름을 구분하는 방법 EMPLOYEE.DNO 투플 변수(tuple variable) 사용 FROM EMPLOYEE AS E, DEPARTMENT AS D 릴레이션의 모든 애트리뷰트나 일부 애트리뷰트들을 검색 4장. 관계 대수와 SQL

18 4.4 SELECT문(계속) 상이한 값들을 검색 4장. 관계 대수와 SQL

19 4.4 SELECT문(계속) 특정한 투플들의 검색 문자열 비교 %: 0개 이상의 문자열과 대치 ㅡ: 임의의 한개 문자와 대치
4장. 관계 대수와 SQL

20 4.4 SELECT문(계속) 다수의 검색 조건 아래와 같은 질의는 잘못되었음
WHERE절에 AND, OR, NOT을 사용해 여러 조건들로 이루어진 부울식을 표현했을 때 논리 연사자들의 우선 순위 4장. 관계 대수와 SQL

21 4.4 SELECT문(계속) 부정 검색 조건 4장. 관계 대수와 SQL

22 4.4 SELECT문(계속) 범위를 사용한 검색 4장. 관계 대수와 SQL

23 4.4 SELECT문(계속) 리스트를 사용한 검색 4장. 관계 대수와 SQL

24 4.4 SELECT문(계속) SELECT절에서 산술 연산자(+, -, *, /) 사용 4장. 관계 대수와 SQL

25 4.4 SELECT문(계속) 널값 널값을 포함한 다른 값과 널값을 +, - 등을 사용하여 연산하면 결과는 널
COUNT(*)를 제외한 집단 함수들은 널값을 무시함 어떤 애트리뷰트에 들어 있는 값이 널인가 비교하기 위해서 ‘DNO=NULL’은 안됨 올바른 표현 다음과 같은 비교 결과는 모두 거짓 YEAR 값이 NULL이라고 가정 YEAR > 300, YEAR = 300, YEAR <> 300 YEAR = YEAR, YEAR <> YEAR 4장. 관계 대수와 SQL

26 4.4 SELECT문(계속) 진리값 UNKNOWN
NULL 값이 발생한 경우, 비교 연산의 결과는 세 번째 진리값: UNKNOWN TRUE를 1, FALSE를 0, UNKNOWN을 1/2이라 생각 다음 그림은 피연산자 x와 y 에 대한 9 가지 서로 다른 진리값 조합에 세 가지 논리 연산자를 적용한 결과 두 진리값의 AND는 최소값, 두 진리값의 OR는 최대값, 진리값 v의 NOT은 1-v x y x AND y x OR y NOT x TRUE UNKNOWN FALSE 4장. 관계 대수와 SQL

27 4.4 SELECT문(계속) ORDER BY절 사용자가 SELECT문에서 질의 결과의 순서를 명시하지 않으면 DBMS가 투플들을 검색한 임의의 순서(또는 기본 키의 오름차순)로 나타냄 ORDER BY절에서 하나 이상의 애트리뷰트를 사용하여 검색 결과를 정렬 ORDER BY절은 SELECT문에서 가장 마지막에 사용되는 절 디폴트 정렬 순서는 오름차순(ASC) DESC를 지정하여 정렬 순서를 내림차순으로 지정할 수 있음 널값은 오름차순에서는 가장 마지막에 나타나고, 내림차순에서는 가장 앞에 나타남 SELECT절에 명시한 애트리뷰트들을 사용해서 정렬해야 함 4장. 관계 대수와 SQL

28 4.4 SELECT문(계속) 집단 함수 데이터베이스에서 검색된 여러 투플들의 집단에 적용되는 함수
각 집단 함수는 한 릴레이션의 한 개의 애트리뷰트에 적용되어 단일 값을 반환함 SELECT절과 HAVING절에만 나타날 수 있음 COUNT(*)를 제외하고는 모든 집단 함수들이 널값을 제거한 후 남아 있는 값들에 대해서 집단 함수의 값을 구함 COUNT(*)는 결과 릴레이션의 모든 행들의 총 개수를 구하는 반면에 COUNT(애트리뷰트)는 해당 애트리뷰트에서 널값이 아닌 값들의 개수를 구함 키워드 DISTINCT가 집단 함수 앞에 사용되면 집단 함수가 적용되기 전에 먼저 중복을 제거함 4장. 관계 대수와 SQL

29 4.4 SELECT문(계속) 4장. 관계 대수와 SQL

30 4.4 SELECT문(계속) 그룹화 GROUP BY절에 사용된 애트리뷰트에 동일한 값을 갖는 투플들이 각각 하나의 그룹으로 묶임 이때 사용된 애트리뷰트를 그룹화 애트리뷰트(grouping attribute)라고 함 각 그룹에 대하여 결과 릴레이션에는 하나의 투플이 생성됨 SELECT절에는 각 그룹마다 하나의 값을 갖는 애트리뷰트, 집단 함수, 그룹화에 사용된 애트리뷰트들만 나타날 수 있음 다음 질의는 그룹화를 하지 않은 채 EMPLOYEE 릴레이션의 모든 투플에 대해서 사원번호와 모든 사원들의 평균 급여를 검색하므로 잘못됨 SELECT EMPNO, AVG(SALARY) FROM EMPLOYEE; 4장. 관계 대수와 SQL

31 4.4 SELECT문(계속) 4장. 관계 대수와 SQL

32 4.4 SELECT문(계속) HAVING 어떤 조건을 만족하는 그룹들에 대해서만 집단 함수를 적용할 수 있음
각 그룹마다 하나의 값을 갖는 애트리뷰트를 사용하여 각 그룹이 만족해야 하는 조건을 명시함 HAVING절은 그룹화 애트리뷰트에 같은 값을 갖는 투플들의 그룹에 대한 조건을 나타내고, 이 조건을 만족하는 그룹들만 질의 결과에 나타남 HAVING절에 나타나는 애트리뷰트는 반드시 GROUP BY절에 나타나거나 집단 함수에 포함되어야 함 만일 HAVING절은 사용하지만 GROUP BY절을 생략하면 WHERE절을 만족하는 투플들의 집합을 하나의 그룹으로 취급한다 4장. 관계 대수와 SQL

33 4.4 SELECT문(계속) 4장. 관계 대수와 SQL

34 4.4 SELECT문(계속) 집합 연산 집합 연산을 적용하려면 두 릴레이션이 합집합 호환성을 가져야 함
SELECT 문장과 달리, 집합 연산은 보통 중복을 제거한다. 중복 제거를 방지하려면, UNION, INTERSECT, 또는 EXCEPT 뒤에 키워드 ALL 사용 UNION(합집합), EXCEPT(차집합), INTERSECT(교집합) UNION ALL(합집합), EXCEPT ALL(차집합), INTERSECT ALL(교집합) 4장. 관계 대수와 SQL

35 4.4 SELECT문(계속) 조인 조인은 두 개의 릴레이션으로부터 연관된 투플들을 결합
조인의 일반적인 형식은 아래의 SELECT문과 같이 FROM절에 두 개 이상의 릴레이션들이 열거되고, 두 릴레이션에 속하는 애트리뷰트들을 비교하는 조인 조건이 WHERE절에 포함됨 조인 조건은 두 릴레이션 사이에 속하는 애트리뷰트 값들을 비교 연산자로 연결한 것 가장 흔히 사용되는 비교 연산자는 = 4장. 관계 대수와 SQL

36 4.4 SELECT문(계속) 조인(계속) 조인 조건을 생략했을 때와 조인 조건을 틀리게 표현했을 때는 카티션 곱이 생성됨
조인 조건이 명확해지도록 애트리뷰트 이름 앞에 릴레이션 이름이나 투플 변수를 사용하는 것이 바람직 두 릴레이션의 조인 애트리뷰트 이름이 동일하다면 반드시 애트리뷰트 이름 앞에 릴레이션 이름이나 투플 변수를 사용해야 함 4장. 관계 대수와 SQL

37 4.4 SELECT문(계속) 4장. 관계 대수와 SQL

38 4.4 SELECT문(계속) 자체 조인(self join)
한 릴레이션에 속하는 투플을 동일한 릴레이션에 속하는 투플들과 조인하는 것 실제로는 한 릴레이션이 접근되지만 FROM절에 두 릴레이션이 참조되는 것처럼 나타내기 위해서 그 릴레이션에 대한 별칭을 두 개 지정해야 함 4장. 관계 대수와 SQL

39 4.4 SELECT문(계속) 4장. 관계 대수와 SQL

40 4.4 SELECT문(계속) 4장. 관계 대수와 SQL

41 4.4 SELECT문(계속) 중첩 질의(nested query)
Subquery, 즉 부질의 또는 중첩질의(nested query)는 SELECT, INSERT, UPDATE, DELETE문의 안에 중첩되어 사용되어지는 SELECT문 즉 하나의 문장으로 문제를 해결하기 위해서, 복잡한 질의문을 여러 개의 논리적인 단계로 나누는 것을 말함 부질의가 사용되는 여러 방법들: 1) 부질의는 단일 상수(단일 값)를 반환하고, 이 상수는 WHERE 절에서 다른 값과 비교될 수 있다 2) 부질의는 릴레이션을 반환하고, 이 릴레이션은 WHERE 절에서 다양한 방법으로 쓰일 수 있다 3) 디스크에 저장된 릴레이션 처럼, 부질의 결과에 의한 릴레이션들도 FROM 절에 쓰임 4장. 관계 대수와 SQL

42 4.4 SELECT문(계속) 한 개의 스칼라값이 반환되는 경우 4장. 관계 대수와 SQL

43 4.4 SELECT문(계속) 한 개의 애트리뷰트로 이루어진 릴레이션이 반환되는 경우
중첩 질의의 결과로 한 개의 애트리뷰트로 이루어진 다수의 투플들이 반환될 수 있음 외부 질의의 WHERE절에서 IN, ANY(SOME), ALL, EXISTS와 같은 연산자를 사용 키워드 IN은 한 애트리뷰트가 값들의 집합에 속하는가를 테스트할 때 사용됨 한 애트리뷰트가 값들의 집합에 속하는 하나 이상의 값들과 어떤 관계를 갖는가를 테스트하는 경우에는 ANY를 사용 한 애트리뷰트가 값들의 집합에 속하는 모든 값들과 어떤 관계를 갖는가를 테스트하는 경우에는 ALL을 사용 EXISTS R의 경우 만약 R이 공집합이면 FALSE 공집합이 아니면 TRUE를 반환 4장. 관계 대수와 SQL

44 4.4 SELECT문(계속) 4장. 관계 대수와 SQL

45 4.4 SELECT문(계속) 4장. 관계 대수와 SQL

46 4.4 SELECT문(계속) 상관 중첩 질의(correlated nested query)
중첩 질의의 WHERE절에 있는 프레디키트에서 외부 질의에 선언된 릴레이션의 일부 애트리뷰트를 참조하는 질의 상관 중첩 질의에서는 외부 질의를 만족하는 각 투플이 구해진 후에 중첩 질의가 수행되므로 상관 중첩 질의는 외부 질의를 만족하는 투플 수만큼 여러 번 수행될 수 있음 4장. 관계 대수와 SQL

47 4.4 SELECT문(계속) 여러 애트리뷰트들로 이루어진 릴레이션이 반환되는 경우
외부 질의의 WHERE절에서 IN, ANY(SOME), ALL, EXISTS와 같은 연산자를 사용 4장. 관계 대수와 SQL

48 4.4 SELECT문(계속) FROM 절의 중첩 질의
Harrison Ford가 출연한 영화들의 모든 제작자 이름을 찾는다 SELECT name FROM MovieExec, (SELECT producerC# FROM Movie, StarsIn WHERE title = movieTitle AND year = movieYear AND starname = ‘Harrison Ford’ ) Prod WHERE cert# = Prod.producerC#; 4장. 관계 대수와 SQL

49 4.5 INSERT, DELETE, UPDATE문 INSERT문 기존의 릴레이션에 투플을 삽입
참조되는 릴레이션에 투플이 삽입되는 경우에는 참조 무결성 제약조건의 위배가 발생하지 않으나 참조하는 릴레이션에 투플이 삽입되는 경우에는 참조 무결성 제약조건을 위배할 수 있음 릴레이션에 한 번에 한 투플씩 삽입하는 것과 한 번에 여러 투플들을 삽입할 수 있는 것으로 구분 릴레이션에 한 번에 한 투플씩 삽입하는 INSERT문 INSERT INTO 릴레이션(애트리뷰트1, ..., 애트리뷰트n) VALUES (값1, ..., 값n); 4장. 관계 대수와 SQL

50 4.5 INSERT, DELETE, UPDATE문(계속)
INTO 릴레이션(애트리뷰트1, ..., 애트리뷰트n) SELECT FROM WHERE ...; 4장. 관계 대수와 SQL

51 4.5 INSERT, DELETE, UPDATE문(계속)
삭제 연산은 한 릴레이션으로부터 한 개 이상의 투플들을 삭제함 참조되는 릴레이션의 삭제 연산의 결과로 참조 무결성 제약조건이 위배될 수 있으나, 참조하는 릴레이션에서 투플을 삭제하면 참조 무결성 제약조건을 위배하지 않음 DELETE문의 구문 DELETE FROM 릴레이션 WHERE 조건; 4장. 관계 대수와 SQL

52 4.5 INSERT, DELETE, UPDATE문(계속)
한 릴레이션에 들어 있는 투플들의 애트리뷰트 값들을 수정 기본 키나 외래 키에 속하는 애트리뷰트의 값이 수정되면 참조 무결성 제약조건을 위배할 수 있음 UPDATE문의 구문 UPDATE 릴레이션 SET 애트리뷰트 = 값 또는 식[, …] WHERE 조건; 4장. 관계 대수와 SQL