6 장. 데이터베이스언어 SQL SQL 에서의단순질의하나이상의릴레이션을포함하는질의부질의 (subquery) 릴레이션전체를다루는연산데이터베이스변경 SQL 에서릴레이션스키마의정의뷰정의 2

Transcription

1 6 장 : 데이터베이스언어 SQL Prof. Jin Hyun Son jhson@cse.hanyang.ac.kr Department of Computer Science & Engineering Hanyang University

2 6 장. 데이터베이스언어 SQL SQL 에서의단순질의하나이상의릴레이션을포함하는질의부질의 (subquery) 릴레이션전체를다루는연산데이터베이스변경 SQL 에서릴레이션스키마의정의뷰정의 2

3 SQL 의발전 SQL 발전과정 Sequel System R 프로젝트 (1970 년대중반 ); 추후 SQL 로이름변경 SQL-86, SQL-89 SQL-92 SQL2 라고도불림 SQL-99 표준화진행중에는 SQL3 라고불리었음객체-관계특징, 기타새로운기능들 SQL4 3

4 Example Schema Movie Database Schema Movie (title, year, length, incolor, studioname, producerc#) MovieStar (name, address, gender, birthdate) StarsIn (movietitle, movieyear, starname) MovieExec (name, address, cert#, networth) Studio (name, address, presc#) 4

5 SQL 에서의단순질의 SQL 질의의기본적인형태 한릴레이션에서어떤조건을만족하는튜플들을검색기본적인세개의키워드 : SELECT, FROM, WHERE FROM 절 : 질의의대상이되는하나이상의릴레이션들 WHERE 절 : 튜플이질의결과에포함되기위해만족해야하는조건 SELECT 절 : 결과로생성할애트리뷰트들을기술 ( 예 ) 1990 년 Disney 스튜디오에서제작된모든영화를찾아라. SELECT * FROM Movie WHERE studioname = Disney AND year = 1990; 5

6 SQL 에서의단순질의 ( 계속 ) SQL 에서의프로젝션 릴레이션을일부애트리뷰트들만으로프로젝션 ( 예 ) 1990 년도에 Disney 스튜디오에서제작된모든영화들의제목과 상영시간을찾아라. SELECT title, length FROM Movie WHERE studioname = Disney AND year = 1990; title length Pretty Woman 119 6

7 SQL 에서의단순질의 ( 계속 ) 다른애트리뷰트이름을사용 SELECT title AS name, length AS duration FROM Movie WHERE studioname = Disney AND year = 1990; name duration Pretty Woman 119 SQL 은키워드와애트리뷰트의이름에대해서는대소문자를 구별하지않는다. 단지인용부호안에서만대문자와소문자를 구분한다. 7

8 SQL 에서의단순질의 ( 계속 ) SELECT 절의항목 산술연산자적용가능상수사용가능 SELECT title,length* AS duration, hrs. AS inhours FROM Movie WHERE studioname = Disney AND year = 1990; title duration inhours Pretty Woman hrs. 8

9 참고 : SQL 질의를읽거나 / 작성하는요령 SELECT-FROM-WHERE 질의읽기 FROM 절 어떤릴레이션이사용되는가? WHERE 절 튜플은어떤조건을만족해야하는가? SELECT 절 질의의결과는무엇인가? SELECT-FROM-WHERE 질의작성 읽기와같은순서 FROM 절, WHERE 절, SELECT 절 9

10 SQL 에서의단순질의 ( 계속 ) SQL 에서의선택연산 WHERE 절에선택조건을명시 여섯개의일반적인비교연산자 : =, <>, >, >=, <= 산술연산자 : +, -, * 등문자열의접합연산자 (concatenation operator) SQL 에서문자열은주위에단일인용부호를두어나타낸다. 정수와실수부울값들은논리연산자인 AND, OR, NOT 으로결합가능 10

11 SQL 에서의단순질의 ( 계속 ) ( 예 ) 1970년이후에만들어진모든흑백영화의제목을찾아라. SELECT title FROM Movie incolor는부울타입 WHERE year > 1970 AND NOT incolor ( 예 ) MGM 스튜디오에서제작된영화중 1970 년이후의것이거나상영시간이 90 분이안되는영화의제목을찾아라. SELECT title FROM Movie WHERE (year > 1970 OR length < 90) AND studioname = MGM 11

12 참고사항 SQL 질의와관계대수 SELECT L FROM R Þ p L (s C (R)) WHERE C Keyword 의대소문자 SQL에서 keyword는대소문자구별없음 FROM, from, FrOm: 모두 O.K. 인용부호내에서는구별됨 FROM 과 from 은다른문자열 12

13 SQL 에서의단순질의 ( 계속 ) 문자열비교 문자열비교는사전식 (lexicographic) 순서에기반 ( 예 ) at < bar s LIKE p 간단한패턴부합 (match) 을기반으로한문자열비교 s 는문자열타입애트리뷰트, p 는패턴패턴 : 특수문자인 % 와 _ 을선택적으로가진문자열 % : 0 이상의길이를가진임의의문자열 _ : 임의의한문자 13

14 SQL 에서의단순질의 ( 계속 ) ( 예 ) 제목이 Star 로시작하는영화를모두찾아라. SELECT title FROM Movie WHERE title LIKE Star% /* 결과 : {Star Wars,...} */ ( 예 ) 제목에소유격 ( s) 을갖는모든영화를찾아라. SELECT title FROM Movie 연속된두개의어포스트로피는하나의어포스트로피표시 WHERE title LIKE % s% /* 결과 : {Rogan s Run,...} */ 14

15 SQL 에서의단순질의 ( 계속 ) LIKE 수식에서이스케이프 (escape) 문자 키워드 ESCAPE 와사용하고자하는이스케이프문자 ( 예 ) s LIKE x%%x% ESCAPE x /* % 로시작해서 % 로끝나는모든문자열 */ 15

16 SQL 에서의단순질의 ( 계속 ) 날짜와시간 날짜와시간은특수한데이터타입으로지원 날짜타입 : DATE ( 예 ) 날짜상수 : DATE 시간타입 : TIME ( 예 ) 시간상수 : TIME 15:00:02.5 날짜 + 시간타입 : TIMESTAMP ( 예 ) 날짜 + 시간상수 : TIMESTAMP :00:00 비교연산자를이용하여날짜와시간비교가능 16

17 SQL 에서의단순질의 ( 계속 ) 널 (null) 값 값을정의할수없는경우 알려지지않은값 (value unknown) 값이무엇인지알수없는경우 ( 예 ) 어떤영화스타의생일을모를때 적용불가능한값 (value inapplicable) 적합한값이존재하지않는경우 ( 예 ) MovieStar 에서미혼인영화스타의 spouse 값 두 NULL 값은서로동일한값이아님 17

18 SQL 에서의단순질의 ( 계속 ) NULL에대한연산산술연산자 ( 즉,, + 등 ) 에 NULL을사용 결과는 NULL ( 예 ) x 의값이 NULL 이면, x + 3 은 NULL 비교연산자 ( 즉, = 이나 >) 에 NULL 을사용 결과는 UNKNOWN ( 예 ) x 의값이 NULL 이면, x > 3 은 UNKNOWN 두 NULL 값은서로동일한값이아니다. 18

19 SQL 에서의단순질의 ( 계속 ) NULL 은상수가아니다. NULL 을피연산자로사용할수없음 ( 예 ) NULL + 3, NULL = 5 : 허용안됨 NULL 과관련한프레디키트 IS NULL, IS NOT NULL ( 예 ) 변수 x 의값이 NULL 인지아닌지확인» x IS NULL» x IS NOT NULL 19

20 SQL 에서의단순질의 ( 계속 ) NULL 값이사용된진리표 X y x AND y x OR y NOT x TRUE UNKNOWN UNKNOWN TRUE FALSE FALSE UNKNOWN FALSE UNKNOWN TRUE UNKNOWN TRUE UNKNOWN TRUE UNKNOWN UNKNOWN FALSE FALSE UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN 20

21 참고 : NULL 과관련해주의할점 NULL 값을가진튜플이있을때의질의결과 WHERE 조건이 TRUE 인튜플만결과에포함 ( 예 ) 다음질의를생각해보자. SELECT * FROM Movie WHERE length <= 120 OR length > 120 모든 Movie 튜플이결과에포함되는것은아님 length 값이 NULL 인튜플» WHERE 조건이 UNKNOWN» 따라서, 결과에포함되지않음 즉, length 가 NULL 이아닌모든튜플을찾아라 21

22 SQL 에서의단순질의 ( 계속 ) 결과의정렬 (ordering) 출력의순서 임의의애트리뷰트의값에기반을둘수있음 정렬된출력 다음의절을추가 : ORDER BY < 애트리뷰트리스트 > 디폴트는오름차순키워드 DESC 를붙이면내림차순 22

23 SQL 에서의단순질의 ( 계속 ) ( 예 ) 1990 년 Disney 에서제작된영화들을, 상영시간이짧은순서로, 상영시간이같을경우, 영화제목의알파벳순서로찾아라. SELECT * FORM Movie WHERE studioname = Disney AND year = 1990 ORDER BY length, title; 23

24 둘이상의릴레이션을포함하는질의 SQL 에서카티션프로덕트와조인 둘이상의릴레이션을결합하는간단한방법 FROM 절에각릴레이션을나열 ( 예 ) Movie (title, year, length, incolor, studioname, producerc#), MovieExec (name, address, cert#, networth) 영화 Star Wars 의제작자의이름과주소를찾아라. SELECT name, address FROM Movie, MovieExec WHERE title = Star Wars AND producerc# = cert# 24

25 둘이상의릴레이션을포함하는질의 ( 계속 ) 애트리뷰트이름의모호성 (disambiguity) 제거 여러릴레이션들을포함하는질의 둘이상의애트리뷰트가같은이름을가질수있다. R.A 의기호를사용 (R; 릴레이션, A; 애트리뷰트 ) ( 예 ) 주소가같은스타와제작자의쌍을찾아라. SELECT MovieStar.name, MovieExec.name FROM MovieStar, MovieExec WHERE MovieStar.address = MovieExec.address 모호성이없는경우에도 R.A 와같이사용가능 25

26 둘이상의릴레이션을포함하는질의 ( 계속 ) 튜플변수 한릴레이션에서서로다른튜플들을지정하고자할때 릴레이션의별명 (alias) 인튜플변수 (tuple variable) 를사용 ( 예 ) 같은주소를가진스타들을찾아라. SELECT Star1.name, Star2.name FROM MovieStar AS Star1, MovieStar AS Star2 WHERE Star1.address = Star2.address AND Star1.name < Star2.name star1.name < star2.name: 동일한스타이름을가진모든쌍이질의결과에포함되는것을방지. 또한같은주소를가진스타들의쌍을단한번만생성하도록해준다. 26

27 둘이상의릴레이션을포함하는질의 ( 계속 ) 여러릴레이션들을포함하는질의의해석 내포된루프 (nested loop) 여러개의튜플변수들이있을때, 각튜플변수에대해루프를갖는중첩루프로생각 27

28 둘이상의릴레이션을포함하는질의 ( 계속 ) LET the tuple variables in the FROM clause range over relations R1, R2,, Rn; FOR each tuple t1 in relation R1 DO FOR each tuple t2 in relation R2 DO... FOR each tuple tn in relation Rn DO IF the where clause is satisfied when the values from t1, t2,, tn are substituted for all attribute references THEN evaluate the attributes of the select clause according to t1, t2,, tn and produce the tuple of values that results. 내포된루프에따른알고리즘 28

29 둘이상의릴레이션을포함하는질의 ( 계속 ) ( 예 ) 영화 Star Wars 의제작자의이름을찾아라. SELECT name FROM Movie, MovieExec WHERE title = Star Wars AND producerc# = cert# 병렬배정 (parallel assignments) 릴레이션에있는튜플들의모든가능한배정을고려즉, ( 임의의순서로 ) 병렬적으로배정된다고생각각배정에대해, WHERE 절이 true 인지판단 WHERE 절이 true 인배정은질의결과로고려즉, SELECT 절의애트리뷰트에사용 29

30 둘이상의릴레이션을포함하는질의 ( 계속 ) 관계대수로의변환 FROM 절의릴레이션들로카티션프로덕트 WHERE 절의조건으로선택연산자적용 SELECT 절의애트리뷰트들로프로젝션 SELECT name, address FROM Movie, MovieExec WHERE title = Star Wars AND producerc# = cert# p name, address (s title = Star Wars AND producerc# = cert# (Movie MovieExec)) 30

31 둘이상의릴레이션을포함하는질의 ( 계속 ) 질의들의합집합, 교집합, 차집합 키워드 : UNION, INTERSECT, EXCEPT ( 예 ) 재산이 $10,000,000 보다많고영화임원인모든여자스타들의이름과주소를찾아라. (SELECT name, address FROM MovieStar WHERE gender = F ) INTERSECT (SELECT name, address FROM MovieExec WHERE networth > ); 31

32 부질의 부질의 다른질의의일부분으로사용된질의 부질의는릴레이션을결과로생성하는수식 WHERE 절에서사용 FROM 절에서사용가능 부질의의결과가단일상수일때 WHERE 절에서다른애트리뷰트와직접비교가능 두개의부질의가하나의질의를형성 UNION, INTERSECT, EXCEPT 32

33 부질의 ( 계속 ) ( 예 ) Los Angeles 에위치한영화회사의사장이제작한 영화의이름과제작연도를찾아라. Movie (title, year, length, incolor, studioname, producerc#) Studio (name, address, presc#) SELECT title, year FROM Movie WHERE producerc# IN (SELECT presc# FROM Studio WHERE address LIKE %Los Angeles% ) 33

34 부질의 ( 계속 ) 스칼라값을생성하는부질의 select-from-where 문에의해하나의값만생성될때 그문장은하나의상수처럼사용될수있다. ( 예 ) Star Wars 의제작자를찾아라. SELECT name single-row SELECT FROM MovieExec (singleton SELECT) WHERE cert# = (SELECT producerc# FROM Movie WHERE title = Star Wars ); 34

35 부질의 ( 계속 ) 릴레이션이비교대상이되는조건 부질의결과가조건에사용 부울값을결과로생성하는프레디키트 EXISTS R R: 릴레이션, s: 스칼라값 R 에튜플이하나라도존재하면 (iff) 참 s IN R s 가 R 에있는값중어느하나와일치하면 (iff) 참 35

36 부질의 ( 계속 ) s > ALL R s 가단항 (unary) 릴레이션 R 의모든값보다크면 (iff) 참 > 연산자대신다른비교연산자사용가능 s > ANY R s 가단항릴레이션 R 의값중적어도하나보다크면 (iff) 참 > 연산자대신다른비교연산자사용가능 EXIST 와 IN 은프레디키트인반면 ALL 과 ANY( 또는 SOME) 은 정량자 (quantifier) 이다. 36

37 부질의 ( 계속 ) 튜플이비교대상이되는조건 SQL 에서하나의튜플은스칼라값들의리스트로표현 (123, foo ), (name,address, networth) 튜플 t 와릴레이션 R 이같은요소들로구성되어있으면, t 와 R 은비교가능 t IN R t <> ANY R 릴레이션 R 의원소와튜플을비교할때, R 의애트리뷰트에대한표준순서에따라애트리뷰트들을비교 37

38 부질의 ( 계속 ) ( 예 ) Movie(title, year, length, incolor, studioname, producerc#) StarsIn(movieTitle, movieyear, starname) MovieExec(name, address, cert#, networth) Harrisonford 가출연한영화제작자의이름을찾아라. SELECT name FROM MovieExec WHERE cert# IN 중복 (duplicates) 없음 (SELECT producerc# FROM Movie WHERE (title, year) IN (SELECT movietitle, movieyear FROM StarsIn WHERE starname = Harrison Ford )); 38

39 부질의 ( 계속 ) ( 예 계속 ) 내포된질의는하나의 select-from-where 문으로변환가능 SELECT name FROM MovieExex, Movie, StarsIn WHERE cert# = producerc# AND title = movietitle AND year = movieyear AND starname = Harrison Ford ; 중복발생가능 FROM 절 : 주질의나부질의에사용된릴레이션들 WHERE 절 : IN 은조인 (Join) 으로대체 39

40 부질의 ( 계속 ) ( 예 계속 ) 중복발생 Harrison Ford Star Wars Steven Spielberg Harrison Ford Indiana Jones Steven Spielberg (H.F., t1, y1) (H.F., t2, y2) (t1, y1, c1) (t2, y2, c1) (c1, n1) StarsIn Movie MovieExec 40

41 부질의 ( 계속 ) 상호관련된부질의 (correlated subquery) 부질의의외부에서선언된튜플변수를, 그내부에서사용하는부질의 ( 예 ) Movie(title, year, length, incolor, studioname, producerc#) 둘이상의영화에사용된영화제목을찾아라.» 제목은같으면서제작연도가다른튜플들 SELECT title FROM Movie AS Old WHERE year < ANY (SELECT year FROM MOVIE WHERE title = Old.title) 애트리뷰트이름에대한영역규칙 (scoping rules) 에주목하라. 41

42 부질의 ( 계속 ) FROM 절에부질의사용 부질의의결과릴레이션으로튜플변수선언이튜플변수를 WHERE 절의조건에사용 ( 예 ) Harrisonford 가출연한영화의제작자이름을찾아라. SELECT Name FROM MovieExec, (SELECT producerc# FROM Movie, StarsIn WHERE title = movietitle AND year = movieyear AND starname = Harrison Ford ) Prod WHERE cert# = Prod.producerC#; 42

43 부질의 ( 계속 ) SQL 조인수식 (Join expressions) 조인수식은그자체가질의일수도있고, 또는부질의로도사용될수있다. CROSS JOIN: 카티션프로덕트 Movie CROSS JOIN StarsIn JOIN ON: 세타조인 Movie JOIN StarsIn ON title = movietitle AND year = movieyear; NATURAL JOIN: 자연조인 MovieStar NATURAL JOIN MovieExec 43

44 부질의 ( 계속 ) FROM 절의조인수식 SELECT title, year, starname FROM Movie JOIN StarsIn ON title = movietitle AND year = movieyear; 44

45 부질의 ( 계속 ) 외부조인 (outerjoin) 허상튜플을결과에추가 * 허상튜플 (dangling tuple): 조인되지못한튜플 조인되지않은경우애트리뷰트들을 NULL 값으로처리 자연외부조인 NATURAL [LEFT RIGHT FULL] OUTER JOIN JOIN ON 을사용한외부조인 [LEFT RIGHT FULL] OUTER JOIN...ON 45

46 부질의 ( 계속 ) ( 예 ) MovieStar(name, address, gender, birthdate) MovieExec(name, address, cert#, networth) MovieStar NATURAL FULL OUTER JOIN MovieExec; name address gender birthdate cert# networth Mary T. Moore Tom hanks George Lucas Maple St. Cherry Ln. Oak Rd. F M NULL 9/9/99 8/8/88 NULL NULL $100 NULL $200 스타지만임원이아닌 ( 예 : Tom hanks), 또는 임원이지만스타가아닌 ( 예 : George Lucas) 사람들에대한정보도함께얻는다. 46

47 부질의 ( 계속 ) ( 예 ) Movie NATURAL LEFT OUTER JOIN MovieExec;» 처음두튜플은결과에포함» 세번째튜플은결과에포함되지않음 ( 예 ) Movie NATURAL RIGHT OUTER JOIN MovieExec;» 처음과세번째튜플은결과에포함» 두번째튜플은결과에포함되지않음 47

48 부질의 ( 계속 ) ( 예 ) Movie FULL OUTER JOIN StarsIN ON title = movietitle AND year = movieyear» On 조건을만족하지않는 Movie 튜플과 StarsIn 튜플들도 NULL 이 적절히사용되어결과에포함 48

49 참고 : SQL 의부울수식에서사용되는프레디키트 기본적인비교프레디키트 : =, <>, <, >, <=, >= LIKE 프레디키트 title LIKE %love% IN 프레디키트튜플값 IN [ 부질의 ( 값1, 값2,, 값n)] IS NULL, IS NOT NULL, IS TRUE, IS FALSE, IS UNKNOWN 단항프레디키트 studioname IS NULL; cost = 100 IS TRUE EXISTS와 UNIQUE 단항프레디키트 EXISTS subquery, UNIQUE subquery ANY( 또는 SOME) 와 ALL: 비교프레디키트와같이사용되는정량자 49

50 릴레이션전체를다루는연산 중복 (duplicates) 제거 SQL 시스템은일반적으로중복을제거하지않는다. 중복제거 : 키워드 DISTINCT SELECT DISTINCT name 중복제거비용 일반적으로비용이크다. 릴레이션정렬 (sorting) 등을이용 DISTINCT 적용시신중할것 50

51 릴레이션전체를다루는연산 ( 계속 ) 합집합, 교집합, 차집합에서의중복 합집합, 교집합, 차집합연산들은기본적으로중복을제거 중복제거방지 : 키워드 ALL R UNION ALL S (SELECT title, year FROM Movie) UNION ALL (SELECT movietitle AS title, movieyear AS year FROM StarsIn); R INTERSECT ALL S R EXCEPT ALL S 51

52 릴레이션전체를다루는연산 ( 계속 ) 집단값 (aggregation) 집단값연산자 집단화값을생성하는다섯개의연산자 COUNT: 값들의개수 SUM: 한열에있는값들의합 AVG: 한열에있는값들의평균 MIN: 한열에있는값중최소값 MAX: 한열에있는값중최대값 52

53 릴레이션전체를다루는연산 ( 계속 ) ( 예 ) SELECT COUNT(*) FROM MovieExec; * 는튜플전체를나타낸다. * 는집단값연산자중 COUNT 에만사용할수있는용법 ( 예 ) SELECT COUNT(DISTINCT name) FROM MovieExec; ( 예 ) SELECT AVG(netWorth) FROM MovieExec; 53

54 릴레이션전체를다루는연산 ( 계속 ) 그룹화 (grouping): GROUP BY 절 어떤열의값에따라튜플들을그룹화 GROUP BY 다음에는그룹화애트리뷰트들을나열한다. SELECT studioname, SUM(length) FROM Movie GROUP BY studioname; 그룹의집단값 그룹화애트리뷰트 집단값을가진 SELECT 절에집단값이아닌상태로기술될수있는애트리뷰트는그룹화애트리뷰트 ( 즉, GROUP BY 절에나열된애트리뷰트 ) 뿐이다. 54

55 릴레이션전체를다루는연산 ( 계속 ) 그룹에대한조건 : HAVING 절 그룹에대한조건기술 집단값의특성을사용 ( 예 ) 1930 년이전에적어도하나의영화를제작한적이 있는제작자에대해, 제작자의이름과 그제작자가 제작한영화상영시간의합계를구하라. SELECT name, SUM(length) FROM MovieExec, Movie WHERE producerc# = cert# GROUP BY name HAVING MIN(year) < 1930; 그룹화애트리뷰트는 HAVING 절에집단값이아닌상태로도사용가능 ( 예 ) name LIKE John% 55

56 릴레이션전체를다루는연산 ( 계속 ) SQL 질의에서절들의순서 SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY WHERE 절 : 조건을만족하는튜플 HAVING 절 : 조건을만족하는그룹 해석순서 WHERE 절을만족하는튜플들만선택이튜플들은 GROUP BY 절에있는애트리뷰트들로그룹화 HAVING 절을만족하는그룹들만선택 56

57 데이터베이스변경 데이터베이스변경 (modification) 삽입 INSERT, DELETE, UPDATE INSERT INTO R(A1,, An) VALUES (v1,, vn); 키워드 INSERT INTO, 릴레이션이름 R, 괄호로묶인애트리뷰트들의리스트키워드 VALUES, 그리고튜플수식 ( 예 ) INSERT INTO StarIn(movieTitle, movieyear, starname) VALUES( The Maltese Falcon, 1942, Sydney Greenstreet ); 57

58 데이터베이스변경 ( 계속 ) ( 예 ) INSERT INTO StarsIn VALUES( The Maltese Falcon,1942, Sydney Greenstreet ); ( 예 ) Movie 릴레이션에는언급되고있지만, Studio 릴레이션에는나타나지않은모든스튜디오를 Studio(name, address, presc#) 릴레이션에추가하라. INSERT INTO studio(name) SELECT DISTINCT studioname FROM Movie WHERE studioname NOT IN(SELECT name FROM Studio); F 추가된튜플의 address 와 presc# 애트리뷰트에는 NULL 값이사용 58

59 데이터베이스변경 ( 계속 ) 삭제 DELETE FROM R WHERE < 조건 >; 키워드 DELETE FROM 릴레이션이름, 예를들어 R, 키워드 WHERE, 그리고조건 ( 예 ) DELETE FROM StarsIn WHERE movietitle = The Maltese Falcon AND movieyear = 1942 AND starname = Sydney Greenstreet ; 59

60 데이터베이스변경 ( 계속 ) 갱신 UPDATE R SET < 새로운값 > WHERE < 조건 >; 키워드 UPDATE, 릴레이션의이름, 예를들어 R, 키워드 SET, 식 (formula) 들의리스트, 키워드 WHERE, 그리고조건 ( 예 ) 영화임원이스튜디오사장인경우 Pres. 라는직함을이름앞에붙여라. UPDATE MovieExec SET name = Pres. name WHERE cert# IN (SELECT presc# FROM Studio); 60

61 SQL 에서릴레이션스키마의정의 데이터정의언어와데이터조작언어데이터정의언어 (DDL: Data Definition Language) 데이터베이스에있는정보의구조를표현 데이터조작언어 (DML: Data Manipulation Language) 질의와변경 61

62 SQL 에서릴레이션스키마의정의 ( 계속 ) SQL 시스템에서사용되는주요데이터타입 INT, INTEGER, SHORTINT FLOAT (or REAL), DOUBLE PRECISION, DECIMAL DECIMAL(n,d) 십진수 n 개, 오른쪽으로부터 d 위치에소수점 ( 예 ) DECIMAL(6, 2): NUMERIC: DECIMAL 과유사 CHAR(n), VARCHAR(n) 고정길이또는가변길이의문자열 62

63 SQL 에서릴레이션스키마의정의 ( 계속 ) BIT(n), BIT VARYING(n) 고정길이또는가변길이의비트열 BOOLEAN TRUE, FALSE, UNKNOWN DATE 와 TIME 특별한형태의문자열 63

64 SQL 에서릴레이션스키마의정의 ( 계속 ) 테이블선언 : CREATE TABLE table-name CREATE TABLE 릴레이션이름 ; 애트리뷰트, 애트리뷰트타입들의리스트 CREATE TABLE MovieStar ( name CHAR(30), address VARCHAR(255), gender CHAR(1), birthdate DATE ); 테이블삭제 : DROP TABLE table-name DROP TABLE R; 64

65 SQL 에서릴레이션스키마의정의 ( 계속 ) 릴레이션스카마의변경 : ALTER TABLE table-name ALTER TABLE 테이블이름, 키워드 ADD, 애트리뷰트이름과데이터타입테이블이름, 키워드 DROP, 애트리뷰트이름 ALTER TABLE MovieStar ADD phone CHAR(16); ALTER TABLE MovieStar DROP birthdate; 65

66 SQL 에서릴레이션스키마의정의 ( 계속 ) 디폴트값 구체적인값이주어지지않았을때는 NULL 값이사용 키워드 DEFAULT 와특정값기술가능 gender CHAR(1) DEFAULT?, birthdate DATE DEFAULT DATE ALTER TABLE MovieStar ADD phone CHAR(16) DEFAULT unlisted ; 66

67 SQL 에서릴레이션스키마의정의 ( 계속 ) 도메인 어떤데이터타입을나타내는새로운이름 도메인정의 : CREATE DOMAIN CREATE DOMAIN < 이름 > AS < 타입기술 >; CREATE DOMAIN MovieDomain AS VARCHAR(50) DEFAULT unknown ; 도메인은애트리뷰트의타입으로사용될수있다. title MovieDomain 67

68 SQL 에서릴레이션스키마의정의 ( 계속 ) 도메인에지정된디폴트값을변경 : ALTER DOMAIN ALTER DOMAIN MovieDomain SET DEFAULT no such title ; 도메인을삭제 : DROP DOMAIN DROP DOMAIN MovieDomain; 68

69 SQL 에서릴레이션스키마의정의 ( 계속 ) 색인 애트리뷰트 A 에대한색인 애트리뷰트 A 에특정값을가지는튜플들을효과적으로찾을수있도록지원해주는자료구조 ( 예 ) B + -tree, 해쉬테이블 색인은 SQL 표준 (SQL-99 까지 ) 에포함되어있지않다. 그러나대부분의상용 DBMS 가색인을지원 69

70 SQL 에서릴레이션스키마의정의 ( 계속 ) 색인의생성 : CREATE INDEX CREATE INDEX < 인덱스이름 > ON < 테이블이름 ( 애트리뷰트리스트 )> CREATE INDEX YearIndex ON Movie(year); CREATE INDEX KeyIndex ON Movie(title, year); 색인의삭제 : DROP INDEX DROP INDEX YearIndex; 색인의장단점 : 질의처리속도가빨라진다. 그러나삽입, 삭제, 갱신연산은복잡해진다. 70

71 SQL 에서릴레이션스키마의정의 ( 계속 ) 색인선택 (Selection of Indexes) 색인이있으면, 질의처리속도향상변경 ( 삽입, 삭제, 갱신 ) 속도저하 질의또는변경의비용 접근하는디스크블록의수 데이터블록접근비용 + 색인블록접근비용 71

72 SQL 에서릴레이션스키마의정의 ( 계속 ) ( 예 ) StarsIn(movieTitle, movieyear, starname) (Q1) SELECT movietitle, movieyear FROM StarsIn WHERE starname = s; (Q2) SELECT starname FROM StarsIn WHERE movietitle = t AND movieyear = y; (I1) INSERT INTO StarsIn VALUES(t, y, s); 72

73 SQL 에서릴레이션스키마의정의 ( 계속 ) ( 예 : 계속 ) 다음과같이가정하자. StarsIn 은 10개의디스크블록에저장평균적으로, 한스타는 3 편의영화, 한영화에는 3 명의스타가출연특정스타나특정영화에대한튜플들 10 개의디스크블록들에분산 즉, 특정영화의 3 튜플들접근 : 3 디스크블록접근 색인읽기 (read): 디스크블록 1 번접근색인변경 (modification) 에는디스크블록두번접근데이터삽입 : 디스크블록 2 번접근적당한디스크블록을찾고, 그블록을다시기록 73

74 SQL 에서릴레이션스키마의정의 ( 계속 ) ( 예 : 계속 ) Q1, Q2, I1 의빈도 Q1: 전체의 p1 % Q2: 전체의 p2 % I1: 전체의 1-p1-p2 % 색인 : 디스크블록 1 번데이터 : 디스크블록 3 번색인 : 디스크블록 2 번데이터 : 디스크블록 2 번 Action Q1 Q2 I1 평균비용 f 1 (Q1)+ f 2 (Q2)+ f 3 (I1) No Index p1+8p2 Star Index p2 Movie Index p1 Both Indexes p1-2p2 질의및삽입에따른비용 10p1+4p2+4(1-p1-p2) 74

75 SQL 에서릴레이션스키마의정의 ( 계속 ) ( 예 : 계속 ) p1, p2 의값에따른비용 p1 = p2 = 0.1 : 삽입이많은경우 2 + 8p1 + 8p2 가최소색인이필요없음 p1 = p2 = 0.4 : 질의 Q1 과 Q2 가많은경우 6-2p1-2p2 가최소두색인모두필요 p1 = 0.5, p2 = 0.1 : 질의 Q1 이많은경우 4 + 6p2 가최소 starname 에대한색인필요 75

76 뷰 CREATE TABLE 문을이용하여정의된릴레이션은실제로 DB 내에존재하는반면, 뷰 (view) 는물리적으로존재하지않는다. 뷰의선언 CREATE VIEW < 뷰이름 > AS < 뷰정의 > < 뷰정의 > 는하나의질의로간주될수있다. CREATE VIEW ParamountMovie AS SELECT title, year FROM Movie WHERE studioname = Paramount ; 76

77 뷰 ( 계속 ) 테이블은물리적으로튜플들이존재하는릴레이션 기본 (base) 테이블또는기본릴레이션이라한다. 저장된 (stored) 테이블또는저장된릴레이션이라고도한다. 뷰는가상 (virtual) 릴레이션이다. 질의처리도중에임시로생성되는릴레이션은임시 (temporary) 릴레이션이라한다. 77

78 뷰 ( 계속 ) 뷰에대한질의 해당튜플들을기본릴레이션으로부터가져온다. 뷰에대한질의를기본테이블에대한질의로변환 뷰정의질의에 AND 로새로운조건추가 SELECT title FROM ParamountMovie WHERE year = 1979; ð SELECT title FROM Movie 뷰 기본테이블 질의변경 (query modification) WHERE studioname = Paramount AND year = 1979; 78

79 뷰 ( 계속 ) 기본릴레이션과뷰를포함한질의 ( 예 ) Paramount 에서제작한영화에출연한스타들을찾아라. SELECT DISTINCT starname FROM ParamountMovie, StarsIn WHERE title = movietitle AND year = movieyear; 여러릴레이션들로정의된뷰 ( 예 ) 영화제목과그영화의제작자로이루어진뷰. CREATE VIEW MovieProd AS SELECT title, name FROM Movie, MovieExec WHERE producerc# = cert#; 79

80 뷰 ( 계속 ) 애트리뷰트이름의변경 뷰에서애트리뷰트들의이름을새로부여할수있다. CERATE VIEW MovieProd(movieTitle, prodname) AS SELECT title, name FROM Movie, MovieExec WHERE producerc# = cert# 80

81 뷰 ( 계속 ) 뷰의변경 뷰는갱신가능 (updatable) 할수도, 가능하지않을수도있다. SQL 에서는, 대체로하나의릴레이션 R로부터정의된뷰에대해서만변경을허용한다. 그리고, 다음을만족해야한다. SELECT 절에는충분한애트리뷰트들이있어야한다. ( 예1) 키애트리뷰트들은 NULL이아니어야한다. NOT NULL 제약을위반해서는안됨. ( 예2) 뷰를통해삽입된튜플은그뷰를통해볼수있어야함 WHERE 절에있는부질의는릴레이션 R을포함해서는안됨. correlated subquery 허용안함 81

82 뷰 ( 계속 ) ( 예 ) 뷰 ParamountMovie 에다음과같은튜플을삽입한다고하자. INSERT INTO ParamountMovie VALUES( Star Trek, 1979) studioname 애트리뷰트가뷰의애트리뷰트에포함되어있지않으므로, 뷰에 삽입되는튜플이 Movie 테이블에반영될때 studioname 값에는 NULL 이 들어간다. 이튜플은 ParamountMovie 의조건을만족하지않는다. 즉, ParamountMovie 뷰에나타나지않는다. 따라서뷰 ParamountMovie 는다음과같이수정되어야한다 : CREATE VIEW ParamountMovie AS SELECT studioname, title, year FROM Movie WHERE studioname = Paramount ; 82

83 뷰정의 ( 계속 ) ( 예 ) 갱신가능뷰로부터튜플을삭제 DELETE FROM ParamountMovie WHERE title LIKE %Trek% ; ( 예 ) 갱신가능뷰에대한갱신 UPDATE ParamountMovie SET year = 1979 WHERE title = Star Trek the Movie ; 뷰의삭제 DROP VIEW ParamountMovie; 83

84 참고 : 뷰의갱신가능성 Movie(title, year, length, incolor, studioname, producerc#) MovieExec(name, address, cert#, networth) CREATE VIEW MovieProd AS SELECT title, name FROM Movie, MovieExec WHERE producerc# = cert#; 다음과같은튜플을뷰 MovieProd에삽입하려한다고하자 : ( Greatest Show on Earth, cecil B. DeMille ) Movie와 MovieExec의키는 NULL이면안된다. 조인이이루어지는애트리뷰트들에 NULL 값이들어간다. 두 NULL 값은동일하지않음에주목하라. 84

85 뷰 ( 계속 ) 뷰를포함하는질의의해석 기본적인개념 : 뷰에대한질의의수식트리를기본테이블에대한질의의수식트리로변환 뷰에대한질의의수식트리 Q 기본테이블에대한질의의수식트리 V W 뷰에대한참조 뷰에대한참조를뷰의정의로대체 (Q: 질의, V, W: 뷰 ) 뷰정의 85

86 뷰 ( 계속 ) CREATE VIEW ParamountMovie AS SELECT title, year FROM Movie WHERE studioname = Paramount ; 뷰 Paramount 에대한질의 π title, year σ studioname = Paramount 이뷰를정의하는질의의수식트리 Movie 86

87 뷰 ( 계속 ) SELECT title FROM ParamountMovie WHERE year = 1979; π title σ year=1979 ParamountMovie π title 질의에대한수식트리 ó year=1979 π title π title, year σ studioname = Paramount σ year=1979 AND studioname = Paramount Movie Movie 기본테이블을사용한질의의수식트리 단순화된질의 87