SQL(1) - DML

1. SQL이란?

- Structured Query Language

 

 

 

2. 기본적인 문법

1) 하나의 릴레이션에서 튜플들을 검색

(1) SELECT : 표시할 attributes

(2) FROM : 참조할 릴레이션

(3) WHERE : 조건(=, <>(부정), <, >, <=, >=, +, -, *, /, AND, OR, NOT)

※ SELECT -> FROM -> WHERE 순서로 해석 및 작성됨

※ || : 문자열 연결 - 예) SELECT ename ||' 의 업무는'||job||'입니다.' FROM emp;

 

 

2) 대수 언어 -> SQL 언어 변환 예시

대수 언어 

-> ㅠtitle,year(σstudioName='Disney' AND year=1990(Movies))

SQL 언어

-> SELECT title, year FROM Movies WHERE studioName='Disney' AND year=1990;

※ 속성값이 문자열일 경우에는 따옴표로 감싼다.

 

 

3) AS 키워드의 사용

(1) Rename : 예) SELECT title AS name ...

(2) 연산 : 예) SELECT length*0.0167 AS length (대수 언어 - ㅠlength*0.0167->length)

(3) 상수값을 가지는 컬럼 추가 : 예) SELECT ... 'hrs.' AS inHours

 

 

4) 대소문자 구분

(1) 대소문자 구분을 안하는 경우 : 키워드, 속성명, 릴레이션명, 별명

(2) 대소문자 구분을 하는 경우 : 문자열 속성값

 

 

5) 문자열의 비교

(1) 일반 문자열 : 다른 언어와 같이 알파벳 순으로 한다. - 예) 'at'<'bar, 'fodder'<'foo'

(2) Bit 문자열 : 크기순(길이순x)으로 한다. - 예) 이진수 표기법 -> B'011', 16진수 표기법 -> X'7ff'

(3) 패턴 비교 : LIKE 키워드를 %, _ 기호와 함께 사용한다. %는 모든 문자열 허용, _는 모든 문자 허용

예1) WHERE title LIKE 'Star ____'; : Star + 4개의 char -> 예) {Star Wars, Star Trek 등}

예2) WHERE title LIKE '%''s%'; : string + 's + string -> 예) {Rogan's Run 등} (주의 : '표시 -> '를 2번 연속으로 표기)

예3) WHERE title LIKE 'x%%x%' ESCAPE 'x'; : % + string + % -> x를 escape 문자로 지정하여 x의 뒤에 오는 %를 일반 문자로 지정. 가운데 %는 모든 문자열을 의미함.

예4)

 

 

6) 날짜 타입

(1) DATE : 예) DATE '1948-05-14'

(2) TIME : 예) TIME '15:00:02.5'

(3) TIMESTAMP : 예) TIMESTAMP '1945-05-14 12:00:00'

※ 비교는 날짜 순으로

 

 

7) NULL/UNKNOWN 값

- 값을 알 수 없거나, 적용할 수 없는 값이거나, 아직 입력 전일 때 사용함

- NULL에 값을 대입할 수 없음

- SQL문으로 NULL을 설정할 수 있음

- x=NULL일 때 -> [x+3=NULL, x>3=UNKNOWN, x IS NULL=TRUE, x IS NOT NULL=FALSE]

※ NULL 체크를 위해 IS NULL, IS NOT NULL 연산이 제공됨

- 확실한 경우에만 TRUE/FALSE가 결정됨

- a=NULL이면, WHERE a<=100 OR a>100의 경우에 선택될까? -> (X), NULL이 아닌 모든 값이 선택됨

 

 

8) ORDER BY (ORDERED BY 아님!)

- 정렬할 기준 필드명들을 지정할 수 있고, 2개 이상의 필드명의 경우 ,(콤마)를 경계로 작성함

- 각 필드명 오른쪽에 오름차순인 ASC 키워드(기본값), 내림차순인 DESC 키워드를 작성할 수 있음

예제

Retrieve the movies produced by Disney in 1990. Order the output by length(shortest first), and among movies of equal length, alphabetically.

Disney에서 1990년에 만들어진 영화들을 길이가 짧은 순으로, 길이가 같다면 알파벳 순으로 산출하라.

-> SELECT * FROM Movies WHERE studioName='Disney' AND year=1990 ORDER BY length, title;

 

 

 

3. 2개 이상의 릴레이션을 포함하는 쿼리

1) FROM에 여러 릴레이션을 명시

(1) 두 릴레이션에 있는 attribute 명이 하나도 겹치지 않을 경우

- 예) SELECT name, address FROM Movies, MovieExec WHERE title='Star Wars' AND producerC#=cert#;

-> 대수 언어 : ㅠname,address(σtitle='Star Wars' AND producerC#=cert#(Movies x MovieExec))

 

(2) 두 릴레이션에 있는 attribute 명이 겹칠 경우

- 릴레이션.속성으로 표시

- 예) SELECT MovieStar.name, MovieExec.name FROM MovieStar, MovieExec WHERE MovieStar.address = MovieExec.address;

 

3) 한 릴레이션을 여러 개의 릴레이션인 것처럼 처리해야 하는 경우

- AS 키워드 사용

- 예) 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은 동일 스타가 출력되는 것(게다가 동일한 2개의 튜플이 출력됨)을 방지하기 위함

 

 

2) INTERSECT(교집합) 예시

예) (SELECT name, address FROM MovieStar WHERE gender='F') INTERSECT (SELECT name, address FROM MovieExec WHERE netWorth>10000000); (순 자산이 1000만 이상인 영화의 임원인 모든 여배우의 이름과 주소를 출력)

= SELECT name,address FROM MovieStar, MovieExec WHERE MovieStar.name=MovieExec.name AND MovieStar.address=MovieExec.address AND gender='F' AND netWorth>10000000;

 

 

3) 서브쿼리

- 서브쿼리는 부분 릴레이션이다.

(1) IN (~들 중 하나인)

- WHERE 키워드와 사용하며, 튜플(자료형) 혹은 SELECT 서브 쿼리 안에 값이 있는지 여부를 확인함

※ 튜플(자료형) : (value, value, value, ...)

- 예) SELECT title, year FROM Movies WHERE producerC# IN (SELECT presC# FROM Studio WHERE address LIKE '%Los Angeles%'); (영화의 프로듀서가 로스 앤젤레스에 위치한 스튜디오들의 한 회장인 영화제목과 연도를 출력)

= SELECT title, year FROM Movies, Studio WHERE producerC#=presC# AND address LIKE '%Los Angeles';

※ 스칼라값 : 서브 쿼리가 리스트가 아닌 단 하나의 값을 반환할 때는 IN이 아닌 =를 쓰면 된다.

 

(2) EXISTS (~들 중 하나인)

- 서브 쿼리의 결과가 한 건이라도 존재하면 TRUE 반환

- 앞에 필드명이 붙지 않음

- SELECT name FROM MovieExec WHERE EXISTS (SELECT producerC# FROM Movies WHERE producerC#=cert# AND title='Star Wars);

※ 서브쿼리 이전에 MovieExec가 이미 선언(FROM) 되었으므로, 서브쿼리에서는 MovieExec을 작성하지 않아도 된다.

 

(3) ALL, ANY

- ANY : 튜플(자료형) 혹은 서브쿼리에서 하나라도 만족하면 됨. OR들을 하나로 뭉친 것

예) SELECT * FROM emp WHERE sal>ANY(950, 3000, 1250)
- ALL : 튜플(자료형) 혹은 서브쿼리에서 모두 만족해야 됨. AND들을 하나로 뭉친 것

예) SELECT * FROM emp WHERE sal=ALL(950, 3000, 1250) -> 결과가 하나도 없을 것

※ 스칼라값 : >,<,=,>=,<= 모두 사용 가능하다.

 

(4) 튜플과 predicates의 비교

- T (<>) (IN/EXISTS/ALL/ANY) R

- 요소의 수가 같다면 비교할 수 있다.

 

 

4. Correlated Subquery

- 메인쿼리의 값을 서브쿼리가 사용하고, 서브쿼리의 값을 받아서 메인쿼리가 계산하는 구조의 쿼리

- 동일한 테이블을 다른 테이블과 비교하듯 사용

 

예제) 2개 이상의 영화에서 사용된 영화 이름을 찾아라

= SELECT TITLE FROM MOVIES M1, MOVIES M2 WHERE M1.TITLE=M2.TITLE AND M1.YEAR<M2.YEAR;

 

 

5. FROM 절에서의 서브쿼리

- 서브쿼리에 꼭 별명을 명시해야 한다.

 

예제) Harrision ford가 출연하는 영화를 제작한 프로듀서의 이름을 찾아라

 

 

6. JOIN

- 보통 FROM 절에 사용됨

1) Cartesian product : 테이블1 CROSS JOIN 테이블2

2) Theta-join : 테이블1 JOIN 테이블2 ON 조건

3) Theta-Outerjoin 테이블1 [LEFT/RIGHT/FULL] OUTER JOIN 테이블2 ON 조건

4) Natural join : 테이블1 NATURAL JOIN 테이블2

5) Natural Outerjoin : 테이블1 NATURAL [LEFT/RIGHT/FULL] OUTER JOIN 테이블2

 

 

7. Boolean 표현

1) 관계 연산자 : =,<>(!=의 의미),>,<,<=,>=

2) LIKE : ex) title LIKE '%love%'

3) IN, NOT IN

4) IS NULL, IS NOT NULL, IS TRUE, IS FALSE, IS UNKNOWN - ex) studioName IS NULL, cost=100 IS TRUE

5) EXISTS :  튜플 존재 여부 - ex) EXISTS 서브쿼리

6) UNIQUE : 중복 튜플 존재 여부 - ex) UNIQUE 서브쿼리

7) ANY, SOME, ALL

 

 

8. Full-Relation Operations

- 릴레이션 전체에 적용되는 연산들

 

1) DISTINCT

- attrribute 단위의 중복 제거

- 비싼 연산

ex) SELECT DISTINCT genre, studioName From Movies;

 

2) [UNION, INTERSECT, EXCEPT] ALL

- 중복 허용 연산

ex) (SELECT title, year FROM Movie) UNION ALL (SELECT movieTitle AS title, movieYear AS year FROM StarsIn);

 

3) Aggregation Operators

- COUNT, SUM, AVG, MIN, MAX 등의 연산이 있음

※ COUNT(DISTINCT a) : 중복을 제거한 후 COUNT

 

4) GROUP BY

- 그룹핑 연산으로, Aggregation 연산과 함께 사용됨

- SELECT문에서는 오직 그룹핑된 attribute들만 나타낼 수 있다.

예) SELECT studioName, SUM(length) FROM Movies GROUP BY studioName;

-> 관계 대수 : ㅠstudioName, sumLength(rstudioName, SUM(length)->sumLength(Movies))

 

5) NULL에 대한 규칙들

- NULL은 Aggregation 연산에서 무시된다.

- NULL은 Grouping 연산에 포함될 수 있다. 즉, NULL으로도 그룹을 형성할 수 있다.

- 튜플이 없는 릴레이션(empty bag)에 대하여 COUNT는 0이 나오고, 나머지 Aggregation 연산에서는 NULL값이 나온다.

예) 릴레이션 R이 (NULL, NULL) 튜플 하나를 가지고 있다고 할 때, SELECT A, COUNT(B) FROM R GROUP A는 (NULL, 0)을 반환하고, SELECT A, SUM(B) FROM R GROUP A는 (NULL, NULL)을 반환한다.

 

6) HAVING

- 이미 Grouping된 Aggregated 속성에 대한 조건을 붙이는 키워드

- GROUP BY의 뒷쪽에 온다.

- WHERE은 튜블에 대한 조건, HAVING은 그룹에 대한 조건

예) SELECT name, SUM(length) FROM MovieExec, Movie WHERE producerC#=cert# GROUP BY name HAVING MIN(year)<1930;

 

 

9. 키워드의 순서 매우 중요!

- 명시 순서 : SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY

- 계산 순서 : FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY

 

 

10. 데이터베이스 수정 연산

1) 삽입 연산

- 형식 : INSERT INTO 테이블 (속성명1, 속성명2,...) VALUES (속성값1, 속성값2);

예) INSERT INTO StarsIn (movieTitle, movieYear, starName) VALUES ('The Maltese Falcon', 1942, 'Sydney Greenstreet');

- 해당 연산에서 테이블에 속성값을 모두 포함한다면 속성명 리스트를 생략해도 됨

예) INSERT INTO StarsIn VALUES ('The Maltese Falcon', 1942, 'Sydney Greenstreet');

- 테이블에 속성값을 모두 포함하지 않는다면, 포함되지 않은 속성의 속성값은 NULL이 됨

- 테이블에 여러 튜플들을 삽입할 수 있음 -> VALUES문 대신 서브쿼리를 넣는다.

 

2) 삭제 연산

- 형식 : DELETE FROM 테이블 WHERE 조건

 

3) 변경 연산

- 형식 :  UPDATE 테이블 SET 속성명1=속성값1, 속성명2=속성값2,... WHERE 조건

※ ||  : CONCATENATE 연산