기초 SQL 문 SELECT 문과 같은 데이터베이스 조회 명령문을 이해한다.

Presentation on theme: "기초 SQL 문 SELECT 문과 같은 데이터베이스 조회 명령문을 이해한다."— Presentation transcript:

1 기초 SQL 문 SELECT 문과 같은 데이터베이스 조회 명령문을 이해한다.
INSERT, UPDATE, DELETE 문과 같은 데이터베이스 수정 명령문을 이해한다.

2 1. 데이터베이스 조회 명령문 2. 데이터베이스 수정 명령문

3 데이터베이스 조회 명령문>> SELECT 문 개요
■ 개념과 작동 원리 ■ 구문 ● BNF(Backus Normal Form) : 프로그래밍 언어의 구문을 정의하는데 많이 사용되는 표기법

4 데이터베이스 조회 명령문>> SELECT 문 개요
● 중요한 BNF 구성 요소들 구성 요소 설명 ::= 왼쪽의 항목은 오른쪽과 같이 정의된다. <element> 다른 곳에 정의되어 있는 항목. [ ] 생략 가능하다. { ... | ... } 여러 항목 중에서 하나를 선택할 수 있다. ...n 여러 번 반복될 수 있다. name, expression 이름, 연산식 등으로 대치시킬 곳. ● 오라클에서의 그래픽한 SQL 구문 표시

5 데이터베이스 조회 명령문>> SELECT 문 개요
● SELECT 문의 구문(BNF) SELECT statement ::= < query_expression > [ ORDER BY { order_by_expression | column_position [ ASC | DESC ] }   [ ,...n ] ] [ COMPUTE   { { AVG | COUNT | MAX | MIN | SUM } ( expression ) } [ ,...n ]   [ BY expression [ ,...n ]] ] [ FOR { BROWSE | XML { RAW | AUTO | EXPLICIT }   [ , XMLDATA ]   [ , ELEMENTS ]   [ , BINARY BASE64 ] } 계속..

6 데이터베이스 조회 명령문>> SELECT 문 개요
계속.. [ OPTION ( < query_hint > [ ,...n ]) ] < query expression > ::= { < query specification > | ( < query expression > ) } [ UNION [ ALL ] < query specification | ( < query expression > ) [...n ] ] < query specification > ::= SELECT [ ALL | DISTINCT ]    [ { TOP integer | TOP integer PERCENT } [ WITH TIES ] ]    < select_list > [ INTO new_table ] [ FROM { < table_source > } [ ,...n ] ] [ WHERE < search_condition > ] [ GROUP BY [ ALL ] group_by_expression [ ,...n ]    [ WITH { CUBE | ROLLUP } ] ] [ HAVING < search_condition > ]

7 데이터베이스 조회 명령문>> 열 제약 조건
■ SELECT 문을 사용할 때 열을 제약하는 각종 조건 ■ 모든 열(*) ● 가장 단순한 형태의 SELECT 문 USE pubs SELECT *   FROM stores ● 행과 열 제약이 전혀 없는 가장 기초적인 조회문으로서, 간단히 데이터를 테스트하거나 테이블 내의 데이터 전체를 결과 집합으로 필요로 할 때 많이 사용함 ● 현재 데이터베이스와 USE 문

8 데이터베이스 조회 명령문>> 열 제약 조건
■ 열 목록 ● 특정 열들의 목록을 지정 SELECT stor_id, stor_name, stor_address, city,state, zip   FROM stores ● (정의한) 순서대로, 또 모두 나열할 필요는 없음 SELECT stor_id, zip, state, city, stor_address

9 데이터베이스 조회 명령문>> 열 제약 조건
■ 열 별칭 지정 ● column AS alias SELECT stor_id AS '서점 ID', zip AS 우편번호, state AS 주,     city AS 시, stor_address AS '서점 주소'   FROM stores ● column alias: 첫 번째 방법에서 AS를 생략한 방법 SELECT stor_id '서점 ID', zip 우편번호, state 주,     city 시, stor_address '서점 주소' ● alias = column : 열 이름과 별칭의 위치가 서로 바뀌고, 가운데 ‘=’ 연산자가 들어가는 방법 SELECT UnitPrice, Quantity, Discount,     total_price = UnitPrice * Quantity * (1 - Discount)   FROM [Order Details]

10 데이터베이스 조회 명령문>> 열 제약 조건
■ 상수 열 ● 테이블 열 값 대신 모든 레코드에 동일한 상수 값을 출력하는 열 ● 문자 상수 열 SELECT UnitPrice AS '단가', Quantity AS '수량', Discount AS '할인율',     '합계:', UnitPrice * Quantity * (1 - Discount)   FROM [Order Details] ● 숫자 상수 열 SELECT 7 AS Lucky, firstname, lastname   FROM employees

11 데이터베이스 조회 명령문>> 열 제약 조건
● FROM 절이 없는 SELECT 문 SELECT 'Lucky', 7, '참 좋다' ● 변수 열을 사용하여 인위적으로 결과 집합을 만드는 경우 int /* 아래 할당문과 비슷한 처리를 한다고 가정함 */ = 1 = 2 AS AS '결과 집합'

12 데이터베이스 조회 명령문>> 행 제약 조건
■ WHERE 절에서 행을 제약하는 각종 조건들 ■ 비교 연산자 연산자 설명 = 같다. > 크다. < 작다. >= 크거나 같다. <= 작거나 같다. <> 같지 않다. ● [예 1] = 비교 연산자 USE Pubs SELECT *   FROM stores   WHERE stor_id = '7896' ● [예 2] <= 비교 연산자 SELECT *   FROM titleauthor   WHERE royaltyper <= 30

13 데이터베이스 조회 명령문>> 행 제약 조건
■ 논리 연산자 ● 연산식들을 논리적으로 결합하는 연산자 연산자 설명 NOT ～ 연산식이 FALSE이면 TRUE를 돌려줌(즉, 부정) ～ AND ～ 양쪽의 연산식을 둘 다 만족하면 TRUE를 돌려줌 ～ OR ～ 양쪽의 연산식 중 하나만 만족하면 TRUE를 돌려줌 ● 우선순위: NOT → AND → OR ● 결합 순서를 바꾸려면 “( )”로 둘러싸 주어야 함

14 데이터베이스 조회 명령문>> 행 제약 조건
● [예1] 논리 연산자들의 단순한 결합(연산자 우선순위 적용) : 결합 순서는 NOT → AND → OR 순 SELECT *   FROM titleauthor   WHERE NOT au_id = ' '     AND title_id = 'BU1032'     OR royaltyper = 40 ● [예2] “( )”로 논리 연산자들의 결합 순서를 바꿈 : 결합 순서는 OR → AND → NOT 순   WHERE NOT (     au_id = ' '     AND (       title_id = 'BU1032'       OR royaltyper = 40     )   )

15 데이터베이스 조회 명령문>> 행 제약 조건
■ 문자열 패턴 매칭(LIKE 연산자) ● LIKE 연산자는 문자열 패턴 매칭(pattern matching)을 해 주는 연산자로서, 소량의 문자열 검색에 많이 사용함 ● 구문: match_expression  LIKE  pattern match_expression에는 주로 열 또는 변수 이름이 오고, pattern에는 와일드카드 문자가 포함된 문자열 상수가 옴 ● 와일드카드 문자 와일드카드 문자 설명 % 문자가 0개 이상인 문자열 _ (밑줄) 단일 문자 [ ] 지정된 범위([a-f]) 또는 집합([abcdef])에 있는 단일 문자 [^] 지정된 범위([^a-f]) 또는 집합([^abcdef])에 없는 단일 문자

16 데이터베이스 조회 명령문>> 행 제약 조건
● [예1] SELECT au_lname, au_fname, phone   FROM authors   WHERE au_lname LIKE '%[CKR]a%'   ORDER BY au_lname ASC ● LIKE 연산자는 대량의 문자열 검색에는 부적합하며(많은 시간이 소요되므로), 이 경우에는 전체 텍스트 검색이 권장됨(“10장. 인덱스와 전체 텍스트 검색” 참고).

17 데이터베이스 조회 명령문>> 행 제약 조건
■ 범위 검색(BETWEEN 연산자) ● 최소 값과 최대 값 사이의 범위에 들어가는지 여부를 파악 ● 최소 값과 최대 값도 범위에 포함됨 ● 구문: range_expression  BETWEEN  min_value  AND  max_value range_expression에는 주로 열 또는 변수 이름이 옴 ● BETWEEN 연산자를 사용하는 것이 더 효율적 range_expression  >=  min_value  AND  range_expression  <= max_value ● range_expression은 정수 데이터 형을 많이 사용하는데, 문자 또는 날짜·시각 데이터 형도 간혹 사용한다. ● 주의: 날짜·시각 데이터 형에서는 자정을 기준으로 최소 값 또는 최대 값 날짜의 포함 여부를 결정하므로 해야 함

18 데이터베이스 조회 명령문>> 행 제약 조건
● [예1] 정수 데이터 형에 적용: SELECT *   FROM titleauthor   WHERE royaltyper BETWEEN 30 AND 40 ● [예2] 문자 데이터 형에 적용:   WHERE title_id BETWEEN 'BU1032' AND 'BU2075' ● [예3] 날짜·시각 데이터 형에 적용: 만약 ord_date가 ‘ :34:00.000’라면 결과 집합에서 제외됨(9월 13일 0시부터 0시까지만(24시까지가 아님!) 범위에 포함되기 때문이다).   FROM sales   WHERE ord_date BETWEEN '9/13/1994' AND '9/13/1994'

19 데이터베이스 조회 명령문>> 행 제약 조건
■ 목록 검색(IN 연산자) ● 목록 안의 값 중 하나와 일치하는지 여부를 파악하는데 사용 ● 구문: list_expression  IN  ( value_list ) ▸ list_expression에는 주로 열 또는 변수 이름이 온다. ▸ value_list에는 보통, 쉼표로 구분된 상수 값들이 온다.    (예) col IN (1, 2, 4, 9) ▸ value_list에 서브 쿼리가 올 수도 있고, 실제로 많이 사용한다(11장 참고). ● [예1] IN 연산자 사용 SELECT *   FROM stores   WHERE state IN ('CA', 'OR', 'WA') ● [예2] 비교 및 논리 연산자 사용 : 예1보다 훨씬 더 비효율적 SELECT *   FROM stores   WHERE state = 'CA'     OR state = 'OR'     OR state = 'WA'

20 데이터베이스 조회 명령문>> 행 제약 조건
■ NULL 비교(IS 연산자) ● NULL: 값이 없는 상태. 숫자 데이터 형의 0도 아니고, 문자 데이터 형의 공백(‘ ’)이나 빈 문자(‘’)도 아님. ● 주의: NULL을 일반적인 연산자와 같이 사용하면, 결과는 항상 NULL이 됨 NULL에 대한 연산을 하기 위해서는 특수한 연산자 IS 또는 IS NOT만 사용해야 함 ● [예1] NULL을 포함한 데이터 확인 : Price 열 값이 NULL인 행이 2개 있음(NULL이 아닌 행은 16개) SELECT Title, Price FROM Titles ● [예2] = NULL로 비교: 0개 행으로 결과가 나오는 것은 NULL을 =로 비교할 때 무조건 실패하기 때문   FROM Titles   WHERE Price = NULL

21 데이터베이스 조회 명령문>> 행 제약 조건
■ NULL 비교(IS 연산자) ● [예3] <> NULL로 비교: 0개 행으로 결과가 나오는 것은 NULL을 <>로 비교할 때도 무조건 실패하기 때문 SELECT Title, Price   FROM Titles WHERE Price <> NULL ● [예4] IS NULL로 비교: 정확한 결과가 나옴   WHERE Price IS NULL ● [예5] IS NOT NULL로 비교 : 정확한 결과가 나옴 SELECT Title, Price   WHERE Price IS NOT NULL

22 데이터베이스 조회 명령문>> 결과 집합 명령
■ 행 정렬(ORDER BY 절) ● 인덱스가 없다면 기본적으로 데이터는 행이 삽입된 순서대로 출력됨 ● 행들을 특정 열(들)을 기준으로 정렬하고자 할 때 ORDER BY 절을 사용 ● 구문: ORDER BY { order_by_expression [ ASC | DESC ] } [ ,...n] ] ▸ order_by_expression은 정렬할 열을 지정한다. 열의 형식은 열 이름 또는 그 별칭, 연산식 또는 그 별칭, 위치를 나타내는 정수 등으로 지정할 수 있다. ▸ ASC는 오름차순 정렬을, DESC는 내림차순 정렬을 지정하는 키워드다. 기본적으로 오름차순이므로 ASC는 생략해도 무관하다. ASC나 DESC는 하나의 열에만 적용된다. ▸ order_by_expression이 여러 개 나올 때는 첫 번째 것이 1차 정렬 키, 두 번째 것이 2차 정렬 키 등과 같이 적용된다.

23 데이터베이스 조회 명령문>> 결과 집합 명령
● [예1] 열 이름으로 정렬 SELECT royaltyper, title_id, au_id   FROM titleauthor   WHERE au_id <= ' '   ORDER BY royaltyper DESC, title_id ● [예2] 위치를 나타내는 정수로 정렬 : ORDER BY 절의 1은 열 목록의 첫 번째 열인 royaltyper를 가리키고, 2는 두 번째 열인 title_id를 가리킴(실행 결과는 같음)   ORDER BY 1 DESC, 2

24 데이터베이스 조회 명령문>> 결과 집합 명령
■ 중복 행 제거(DISTINCT) ● 결과 집합에  중복된 레코드들을 하나씩만 나타나게 하려면 열 목록에 DISTINCT를 사용하면 됨 ● 구문: DISTINCT column [ ,...n] ● column(열)이 여러 개 있을 때는 모든 열을 조합한 값이 중복되지 않도록 해 줌 DISTINCT가 포함된 SELECT 문에 ORDER BY 절이 있을 경우에는 정렬하는 열들이 열 목록에 반드시 나타나야 함 ● [예1] 열 하나에 적용 SELECT DISTINCT state   FROM stores

25 데이터베이스 수정 명령문>> INSERT 문 기초
■ 개념 ● INSERT 문은 테이블에 한 행을 삽입하는 명령문 ● 구문: INSERT [INTO] table_name   ( column_list )   VALUES ( value_list ) ▸ INSERT와 table_name 사이의 키워드 INTO는 생략할 수 있다. ▸ column_list는 쉼표로 구분된 하나 이상의 열들의 목록을 가리킨다.      (예) col1, col2, ... ▸ value_list는 쉼표로 구분된 하나 이상의 값들의 목록을 가리킨다.      (예) val1, val2, ... ▸ value_list의 각각의 값은 같은 위치의 열에 할당된다. ▸ 값은 숫자 상수, 문자열 상수, 연산식, 키워드(DEFAULT) 등이 될 수 있다.

26 데이터베이스 수정 명령문>> INSERT 문 기초
■ 작동 원리 ● INSERT 문의 실행이 성공하면, 위 그림처럼 기본적으로 테이블의 마지막 행 뒤에 새로운 행이 삽입됨

27 데이터베이스 수정 명령문>> INSERT 문 기초
● 테이블에 클러스터 인덱스가 있을 경우에는 기존의 행들 사이에 삽입될 수도 있음 ● 기본적인 INSERT 문은 반드시 한 행에 대해서 적용(한꺼번에 여러 행을 삽입할 수 없다) ● 기본적인 INSERT 문은 행 전체에 대해 적용(한 행의 일부분만 삽입할 수는 없다) ■ 기본 예 USE pubs INSERT stores   ( stor_id, stor_name, stor_address, city, state, zip )   VALUES ( '9001', 'Book Baron', '1236 S. Magnolia Ave.', 'Anaheim', 'CA', '92804' )

28 데이터베이스 수정 명령문>> INSERT 문 활용
■ 열 순서 변경 ● 삽입할 때 열들의 순서를 바꿈   INSERT stores   ( zip, state, city, stor_address, stor_name, stor_id )   VALUES ( '94705', 'CA', 'Berkeley', '2904 College Ave.',     'Avenue Books', '9002' ) ● 삽입된 행을 조회 SELECT *   FROM stores   WHERE stor_id = '9002' ● 바뀐 열과 값의 순서는 반드시 서로 일치해야 함

29 데이터베이스 수정 명령문>> INSERT 문 활용
■ 열 생략 ● 예:   INSERT stores ( stor_id )   VALUES ( '9003' ) ● 방금 삽입된 행 조회 SELECT *   FROM stores   WHERE stor_id = '9003' ● 주의: 아무 열이나 생략할 수는 없음. NULL이 허용되지 않고(NOT NULL), 기본값(DEFAULT)도 정의되어 있지 않는 열을 생략하면 오류가 발생함

30 데이터베이스 수정 명령문>> INSERT 문 활용
■ 값으로 DEFAULT 사용 ● 값 목록에서 특정 값 대신에 키워드 “DEFAULT”를 지정 → 해당 열에는 기본값 또는 NULL이 삽입됨 ● 예: INSERT employee ( emp_id, fname, minit, lname, job_id, job_lvl, pub_id, hire_date )   VALUES ( 'ZZZ12345M', 'Irum', DEFAULT, 'Seong', DEFAULT, DEFAULT, DEFAULT, DEFAULT ) ● 방금 삽입된 행 조회 SELECT *   FROM employee   WHERE emp_id = 'ZZZ12345M'

31 데이터베이스 수정 명령문>> INSERT 문 활용
■ 값으로 NULL 사용 ● 값 목록에서 NULL을 지정하면 기본값이 정의되어 있더라도 무조건 NULL이 삽입됨 ● 예: INSERT publishers ( pub_id, pub_name, city, state, country )   VALUES ( '9911', 'Bad Publisher', 'L.A.', 'CA', NULL ) SELECT *   FROM publishers   WHERE pub_id = '9911' ● NULL 대신에 DEFAULT를 사용해서 삽입 SELECT *   INSERT publishers ( pub_id, pub_name, city, state, country )   VALUES ( '9912', 'Good Publisher', 'L.A.', 'CA', DEFAULT )   WHERE pub_id = '9912'

32 데이터베이스 수정 명령문>> INSERT 문 활용
■ 값으로 NULL 사용 ● 값 목록에서 NULL을 지정하면 기본값이 정의되어 있더라도 NULL이 삽입됨 ● 예: INSERT publishers ( pub_id, pub_name, city, state, country )   VALUES ( '9911', 'Bad Publisher', 'L.A.', 'CA', NULL ) SELECT *   FROM publishers   WHERE pub_id = '9911' ● NULL 대신에 DEFAULT를 사용해서 삽입   INSERT publishers ( pub_id, pub_name, city, state, country )   VALUES ( '9912', 'Good Publisher', 'L.A.', 'CA', DEFAULT )   WHERE pub_id = '9912'

33 데이터베이스 수정 명령문>> INSERT 문 활용
■ DEFAULT VALUES 절 ● 모든 열에 “DEFAULT”를 적용하고자 할 경우 ● 예: USE Northwind INSERT Orders   DEFAULT VALUES ● 방금 삽입된 행 조회   SELECT *   FROM Orders   WHERE OrderID =

34 데이터베이스 수정 명령문>> UPDATE 문 기초
■ 개념 ● UPDATE 문은 테이블의 하나 이상의 열을 갱신하는 명령문 ● 구문: UPDATE table_name   SET column1 = value1     [ , column2 = value2, ... ]   [ WHERE search_condition ] ▸ columni = valuei 쌍은 필요한 만큼 반복될 수 있음(쌍들 사이는 ‘,’로 구분). ▸ WHERE 절(WHERE search_condition)은 생략할 수도 있지만, 이 경우 모든 행을 일괄적으로 갱신해 버리므로 주의해야 함(따라서 대부분의 경우에 WHERE 절을 사용함).

35 데이터베이스 수정 명령문>> UPDATE 문 기초
■ 작동 원리

36 데이터베이스 수정 명령문>> UPDATE 문 활용
■ 기본 예 USE pubs SELECT *   FROM roysched WHERE title_id = 'BU1032' UPDATE roysched   SET royalty = 8   WHERE title_id = 'BU1032'

37 데이터베이스 수정 명령문>> UPDATE 문 활용
■ 값으로 DEFAULT 사용 ● 해당되는 열의 값을 기본값 또는 NULL로 갱신 ● 예 SELECT country   FROM publishers   WHERE pub_id = '9911' UPDATE publishers   SET country = DEFAULT

38 데이터베이스 수정 명령문>> DELETE 문
■ 개념 ● 테이블의 하나 이상의 열을 삭제하는 명령문 ● 구문: DELETE [FROM] table_name   [ WHERE search_condition ] ▸ DELETE와 table_name 사이의 키워드 INTO는 생략할 수 있음 ▸ WHERE 절(WHERE search_condition)은 생략할 수도 있지만, 이 경우 모든 행을 일괄적으로 삭제해 버리므로 주의해야 함(따라서 대부분의 경우에 WHERE 절을 사용함).

39 데이터베이스 수정 명령문>> DELETE 문
■ 작동 원리 ● DELETE 문이 실행되면 WHERE 절에서 지정한 해당 행 전체가 삭제됨 ● WHERE 절을 만족하지 않으면 삭제는 발생하지 않으며, 만족할 경우에는 하나 또는 그 이상의 행이 삭제됨 ● DELETE 문은 행 단위로 적용되며, 행의 일부만 삭제할 수는 없음

40 데이터베이스 수정 명령문>> DELETE 문
■ 기본 예 ● 가장 기본적인 DELETE 문의 실행 예: DELETE stores   WHERE stor_id = '9001' ▸ stores 테이블에서 stor_id가 '9001'인 행을 모두 삭제함 ▸ stor_id는 주 키이고 값이 '9001'인 행이 있으므로, 실제로 한 행만 삭제됨