[컴퓨터활용능력 1급 실기] 쿼리(Query) (액세스)

쿼리(Query) (액세스)

개념

• 데이터베이스에서 특정한 테이블에 특정한 조건을 주어 검색하는 기능
• 데이터베이스에서 기본적이면서도 매우 중요한 요소
• 테이블을 데이터베이스 자체라고 한다면, 쿼리는 이 데이터베이스에서 필요한 것만을 뽑아내어 가공하는 형태라고 할 수 있음.
• 만들어진 쿼리는 테이블과 동일하게 폼이나 보고서에서 레코드 원본으로 사용됨.

 

• 결과를 표시하는 데 필요한 필드만 하단의 그리드 라인에 추가함.

 

상위 값 속성

• 지정한 조건을 만족하는 특정 수의 레코드 또는 특정 비율의 레코드를 반환하는 속성
• 상위 몇 개(TOP 10) 또는 상위 몇 %(TOP 10%)로 지정함.
• SQL 보기에서 SELECT 문 다음에 'TOP n', 'TOP n%'를 직접 입력해도 됨.
• 지정한 조건을 만족하는 동일한 값이 있을 경우 모두 표시함.
• 일반적으로 정렬된 필드에서 사용됨.

SELECT TOP 5 소속지점.소속지점코드, 소속지점.지점명, Avg(판매.판매수량) AS 판매수량평균
FROM 소속지점 INNER JOIN 판매 ON 소속지점.소속지점코드 = 판매.소속지점코드
GROUP BY 소속지점.소속지점코드, 소속지점.지점명
ORDER BY Avg(판매.판매수량) DESC;

 

묶는 방법

• 합계, 평균, 최소값, 최대값, 개수, 표준 편차, 분산, 처음 값, 마지막 값, 식, 조건

 

쿼리의 종류

쿼리 마법사

• 쿼리 종류
  • 단순 쿼리(선택 쿼리)
  • 크로스탭 쿼리
    • 스프레드시트의 피벗 테이블 형식으로 데이터를 요약하고 비교할 수 있도록 만드는 쿼리
    • 기본적으로 3개의 필드를 사용함.
      • 결과에 대한 열 이름을 나타내는 필드 (열 머리글)
      • 결과에 대한 행 이름을 나타내는 필드 (행 머리글)
      • 쿼리의 본문에 결과를 계산하는 데 사용하는 필드
    • 2개 이상의 테이블을 이용할 경우
      • 크로스탭 마법사를 사용할 수 없음.
      • 쿼리 디자인 보기 상태에서 작성해야함.
    • 예)

      • 

      • 

  • 중복 데이터 검색 쿼리
  • 불일치 검색 쿼리
    • 두 테이블을 비교하여 한 쪽 테이블에 있는데 다른 한 쪽 테이블에 없는 레코드를 검색하는 쿼리
    • 불일치 검색 쿼리 마법사를 이용하면 쉽게 작성할 수 있음.
      • 불일치 검색 쿼리 마법사에서는 '쿼리를 넣을 테이블'과 '관련이 있는 테이블'만 구분할 수 있으면 됨.
  • 매개변수 쿼리
    • '쿼리 매개 변수' 대화상자를 사용자에게 보여주어, 사용자가 검색에 사용할 자료를 입력하는 쿼리
    • 사용 방법
      • 예) '제품명' 필드의 조건에 '[제품명을 입력하세요]' 입력
      •  

        

      • 

 

조인의 종류

• 조인(Join)
  • 2개 이상의 테이블에 나뉘어 저장된 정보를 1개의 테이블처럼 합쳐 사용하기 위해 연결하는 방법을 정의하는 것
• 조인의 구분
  • 내부 조인(Inner Join)
    • 관계가 설정된 두 테이블에서 연결된 필드가 일치하는 행만 질의에 포함됨.
  • 외부 조인(Outer Join)
    • 왼쪽 외부 조인(Left Outer Join)
      • 왼쪽 테이블에서는 모든 레코드를 포함하고, 오른쪽 테이블에서는 조인된 필드가 일치하는 레코드만 질의에 포함되는 것
      • 화살표의 방향이 왼쪽에서 오른쪽으로 이동되듯이 표현됨.
    • 오른쪽 외부 조인(Right Outer Join)
      • 오른쪽 테이블에서는 모든 레코드를 포함하고, 왼쪽 테이블에서는 조인된 필드가 일치하는 레코드만 질의에 포함되는 것
      • 화살표의 방향이 오른쪽에서 왼쪽으로 이동되듯이 표현됨.

 

조건 입력

• '조건' 난에서 검색어로 문자를 사용하는 경우에는 반드시 문자 앞뒤에 " "를 붙여주어야 함.
• 날짜의 경우는 검색어 앞뒤에 #을 붙여주어야 함.

 

OR/AND 검색 조건

OR 검색 조건

• 2개 이상의 조건을 서로 다른 행에 입력하면 자동으로 OR 조건이 설정됨.
• 연산자 'OR'를 이용하여 조건을 설정함.

 

AND 검색 조건

• 같은 행에 2개 이상의 조건을 입력하면 AND 조건이 설정됨.
• 연산자 'AND'를 이용하여 조건을 설정함.

 

 

AND와 OR의 복합 조건

 

SQL문을 이용한 쿼리 작성하기

• 쿼리를 작성할 때 대부분은 사용이 편리한 쿼리 작성기를 사용하지만, 조회나 프로시저 부분에서 꼭 SQL을 사용해야 하는 경우가 발생하기 때문에 기본적인 SQL문의 사용법은 알고 있어야 함.
• SQL문은 입력 도중 오타나 띄어쓰기만 잘못해도 쿼리가 실행되지 않기 때문에 정확한 사용법을 준수해야 하지만, 모든 문법을 암기할 필요는 없음.
  • 문제 풀이 도중 SQL문이 생각나지 않을 때는 쿼리 작성기로 비슷한 쿼리를 작성한 다음, SQL 보기로 확인하고 복사 후 수정하여 사용하면 됨.

 

기본적인 SQL문

검색

• 테이블에서 조건에 맞는 레코드 중 지정한 필드(필드1, 필드2, ...)만을 검색함.

SELECT 필드1, 필드2, ...
FROM 테이블
WHERE 조건;

• 사용 예

SELECT 제품명, 판매단가
FROM 제품
WHERE 판매단가 >= 20000;

 

삽입

• '테이블2'에서 조건에 맞는 레코드의 추가필드1, 추가필드2, ... 를 '테이블1'의 필드1, 필드2, ...에 추가함.

INSERT INTO 테이블1(필드1, 필드2, ...)
SELECT 추가필드1, 추가필드2, ...
FROM 테이블2
WHERE 조건;

• 사용 예

INSERT INTO 하절기제품(제품코드, 제품명, 생산원가, 판매단가)
SELECT 제품코드, 제품명, 생산원가, 판매단가
FROM 추가제품
WHERE 생산원가 >= 4000 AND 판매단가 >= 5000;

 

삭제

• 테이블에서 조건에 맞는 레코드를 삭제함.

DELETE 필드
FROM 테이블
WHERE 조건;

• 사용 예

DELETE 제품명
FROM 하절기제품
WHERE 제품명 IN("반코트", "롱코트", "가죽잠바", "부츠");

 

수정

• 테이블에서 조건에 맞는 레코드에 대해서 지정한 필드의 값을 변경값으로 수정함.

UPDATE 테이블
SET 필드 = 변경값
WHERE 조건;

• 사용 예

UPDATE 하2
SET 판매단가 = 판매단가 * 1.3;

 

코드 작성 시 컨트롤의 분리 및 연결

분리하여 연결하지 않아도 되는 경우

• DoCmd를 이용하는 RunSQL(INSERT문, UPDATE문, DELETE문) 명령에서 SQL문이나 조건을 입력할 때는 컨트롤을 분리하여 연결하지 않고 입력해도 됨.

# 'txt상품코드', 'cmb브랜드', 'cmb분류', 'txt소비자가'의 값을 '상품_2' 테이블에 추가하기
DoCmd.RunSQL "INSERT INTO 상품_2(상품코드, 상품명, 브랜드코드, 분류코드, 소비자가) VALUES (txt상품코드, txt상품명, cmb브랜드, cmb분류, txt소비자가)"

# <상품_2> 테이블에 있는 레코드 중 'txt상품코드' 컨트롤에 입력된 상품코드와 동일한 레코드 삭제하기
DoCmd.RunSQL "DELETE * FROM 상품_2 WHERE 상품코드 = txt상품코드"

 

반드시 분리하여 연결해야 하는 경우

• DoCmd를 이용한 RunSQL문이지만, 폼의 컨트롤이 아닌 프로시저에서 선언한 변수(변수상품코드)를 사용한 경우에는 변수를 분리하여 연결해야 함.

# 테이블에 있는 레코드 중 변수로 지정된 '변수상품코드'에 입력된 상품코드와 동일한 레코드 삭제하기
DoCmd.RunSQL "DELETE * FROM 상품_2 WHERE 상품코드 = '" & 변수상품코드 & "'"

 

• Me.Filter를 사용하여 조회문을 입력할 때에는 컨트롤을 분리하고, 문자 데이터일 때 ' '를 입력해야 함.

# 현재 폼의 '고객코드'가 'cmb고객코드'의 값과 동일한 레코드만 표시하기
Me.Filter = "고객코드 = '" & cmb고객코드 & "'"

 

• RecordSource, RowSource에 SQL문을 입력할 때에는 컨트롤을 분리하고, 문자 데이터일 때 ' '를 입력해야 함.

# 'lst사원정보' 컨트롤에는 'cmb직위' 컨트롤에서 지정한 직위와 동일한 레코드만 표시하기
lst사원정보.RowSource = "SELECT * FROM 사원관리 WHERE 직위 = '" & cmb직위 & "'"

 

참고

• 쿼리에서는 조건을 지정하는 부분이 까다로울 수 있는데, 이는 액셀의 고급 필터나 데이터베이스 함수에서 조건을 지정하는 원리와 같기 때문에 액셀에서 충분히 연습했다면 쉽게 풀 수 있는 내용임.
  • 나머지는 마법사를 사용하거나 쿼리 편집기 사용법만 알면 쉽게 풀 수 있음.
  • 쿼리 문제도 액셀 2번의 계산 문제와 마찬가지로 문제를 일으면 바로 쿼리 편집기에 설정하는 모양이나 마법사의 단계가 떠오를 수 있도록 반복해서 학습해야 함.
• 쿼리나 프로시저를 작성한 후 결과를 확인하려면 이를 실행해야 하는데, 이 때의 실행이 테이블의 데이터에 변화를 준다 해도 채점과는 무관하니 염려하지 않아도 됨.