컴활1급 실기 - 2과목 데이터베이스 메모

컴퓨터활용능력시험 1급 실기 - 2과목 데이터베이스 

 

 

 

1. 액세스 개요

 

엑세스는 DBMS의 하나로 기본 데이터 확장자는 .accdb

 

테이블, 쿼리, 폼, 보고서(인쇄전용), 매크로, 모듈(프로그램 작성)

 

 

쿼리

 

선택쿼리 : 데이터의 변경없이 쿼리 결과 반환 (select)

실행쿼리 : 데이터를 변경하거나 복사하는 쿼리 

 

폼

 

쿼리로 할 작업을 시각적으로 좀 더 쉽고 효율적으로 작업할 수 잇도록 작업화면을 만드는 개체

폼에서는 매크로나 프로시저를 특정 이벤트에 연결할 수 도 있음

 

보고서

 

통계분석 처리 가능

테이블이나 쿼리 레코드를 원본으로 사용하여 다양한 형태로 작성 가능

 

 

 

2. 테이블 작성

 

 

일련번호 형식 : 오라클 rownum이나 mysql increment같은 기능

 

OLE 개체 형식 : 개체 입력 가능, 다른 프로그램에서 만든 개체 입력할 수 있는 형식

필드 크기는 1GB

 

하이퍼링크 형식

 

첨부파일 형식 : 한꺼번에 여러 파일 첨부 가능

 

계산 형식 : 필드에 입력된 수식의 결과를 표시하기 위한 형식

 

 

테이블

 

만들기 - 테이블 디자인

 

테이블 도구 디자인 탭에서 테이블 구조 작성 가능

 

작성된 테이블의 구조를 알고자하면 좌측 목록의 테이블에서 우클릭 - 디자인보기

 

테이블 우클릭 - 구조 복사 가능

 

구조에서 필드 순서를 바꾸려면 필드를 선택한 상태에서 머리부분을 한번 더 눌러 드래그 해주면 된다.

 

필드 우클릭으로 테이블 너비, 높이 조정 가능 (자동맞춤 기능 선택 가능)

 

필드 숨김 설정 가능

 

필드 고정 : 우측으로 이동하여 뒤쪽에 위치한 필드를 볼 때도 고정시켜놓은 필드는 화면 보기에서 밀리지 않는다.

(고정해놓으면 고정된 필드가 맨 좌측으로 이동함)

 

필드 일반속성 

 

캡션은 화면에 보여지는 이름

 

입력마스크로 입력받는 형식 지정가능

 

날짜는 첫째 줄 형식에서 포맷 선택 가능

 

필수 탭에서 not null 설정 가능

 

 

필드 속성

 

1. 입력마스크

 

2. 사용자 지정 형식

사용자 지정 기호

 

9와 # 모두 숫자나 공백 입력가능하나 9는 기호사용 X, #은 기호 사용 O

 

 

3. 유효성 검사

 

데이터 종류나 범위 지정하여 입력데이터를 제한

 

IN ( "상", "중", "하" )

 

BETWEEN 1 AND 100

 

LEN([필드이름]) = 6  //필드 이름은 대괄호로 묶는다

 

<> 0 : 0이 아닌 값

 

0 or >=100  

 

LIKE  "  " : 포함된 것 

 

 

4. 조회 속성

 

필드의 구조보기에서 일반 속성 옆에 위치한 조회속성

 

콤보상자 

 

컨트롤 표시 - 콤보상자 설정 가능

 

콤보 상자 설정 후 행 원본 유형 설정에서 테이블이나 쿼리에서 선택 목록을 불러올 것인지 

값 목록으로 직접 입력할 것인지 지정 가능

 

값 목록으로 직접 지정한 경우 아래 행원본에 세미콜론으로 구분하려 목록 구성

 

데이터 형식의 조회마법사에서 원하는 값 입력하여 목록 만들어도 된다.

 

 

홈 - 정렬 및 필터 : 정렬제거

각 필드 우클릭해서 각 필드를 기준으로 정렬 가능

 

 

웬만한 데이터 조건은 필드 - 우클릭 에서 조정 가능

 

홈 - 정렬 및 필터 - 고급필터/정렬

 

대충 수식만 입력하면 sql도 자동으로 입력해준다 ㅠ  노* >> LIKE "노*"

"조건:" 행 이하에 같은 행에 입력하면 and 조건, 다른 행에 입력하면 or 조건

 

 

고급필터 설정해주고 적용/ 해제는 정렬및 필터의 필터 적용/해제 버튼으로 

 

 

연습문제는 시험 전에 한번에 할것

 

 

 

5. 관계 설정, 외부데이터

 

1. 관계 설정

 

데이터베이스 도구 - 관계 설정

 

관계 탭

참조하는 테이블의 필드(외래키) 참조되는 테이블의 (기본키)필드로 드래그 하면 관계편집 창이 열린다.

 

 

위와 같이 설정하고 사원정보 테이블을 보면 행머리에 + 기호가 생겨 

조인된 개인정보 테이블의 해당 레코드를 볼 수 있다.

 

데이터를 외래키 설정을 할때는 참조하는 기본키와 데이터 형식, 필드 크기를 맞춰줘야한다.

 

2. 외부데이터 가져오기

 

텍스트파일, 스프레드시트, 데이터베이스등의 데이터를 액세스 테이블로 만드는 작업

 

 

필드 포함 안함 설정 가능

 

 

3. 외부데이터 내보내기

 

형식 변경하여 출력

 

 

6. 폼

 

1. 폼 작성의 기본

 

데이터 입력 수정 삭제 조회 등 작업 편리하게 할 수 있도록 제공하는 개체

폼을 이용하여 원하는 데이터만 표시하여 보기 가능

 

폼에서 데이터를 수정하면 원본에 반영된다.

 

구성요소

 

폼 머리글 : 폼 제목 // 폼보기에서는 상단에 매번 표시되지만 인쇄미리보기에서는 첫페이지에만

페이지 머리글  //페이지 머리글 바닥글은 폼보기에서는 안보이고 인쇄 미리보기에서 확인 가능

 

본문

 

 

하위 폼

 

페이지 바닥글

폼 바닥글

폼 선택기

구역선택기

컨트롤

 

폼 하단에서 다른 레코드로 이동 가능

 

홈 탭이나 디자인 탭의 맨 좌측의 보기 버튼으로 폼 보기 가능

디자인 보기에서는  폼 구조 화면 볼 수 있음

 

 

 

2. 폼 만들기

 

만들기 - 폼

 

폼 마법사로 폼 만들기

바운드 : 테이블, 쿼리와 연결되어있는 폼

언바운드 : 연결 x

 

 

 

 

폼 디자인으로 폼 만들기

 

폼 디자인 탭 - 도구 그룹 - 기존 필드 추가 - 우측에 생긴 창에서 '모든 테이블 표시'

넣고자하는 테이블의 필드를 더블 클릭하거나 드래그하여 폼에 추가

 

필드 컨트롤의 색상같은건 폼 형식 탭에서 설정할 수 있다.

 

비뚤빼뚤하게 배치된 (필드)컨트롤은

폼 디자인 탭 - 정렬 - 크기 및 순서 조정 그룹 - 맞춤 으로 보기 좋게 정렬해준다.

 

 

 

만들기 - 기타 폼 - 폼분할 : 위쪽은 폼 형태, 하단엔 레코드 목록을 테이블 형태로 보여줌

(왜 만들기탭에 있는 건지는 모르겠음)

 

 

 

 

 

3. 폼 속성 : 폼 크기, 색, 화면위치, 동작 등과 같은 폼의 전반적인 사항 정의 // 화면 우측에 표시됨

 

폼 디자인 도구 - 디자인 - 도구 - 속성 시트 클릭

 

바로가기 메뉴의 속성 선택

 

폼 선택기(폼여백)나 구역선택기, 컨트롤 등을 더블 클릭

 

속성시트 - 데이터 - 행원본 칸의 우측 [...] 버튼 : 쿼리 작성기

 

하단 조건 행부터 각 필드의 조건 입력

 

 

컨트롤 그룹

 

 

-하위 폼

 

디자인 - 컨트롤 그룹 - 하위 폼/하위 보고서 버튼

 

 

 

하위 폼에 테이블을 넣을건지 기존 폼을 넣을 건지 선택

 

(기존폼을 넣을 경우 기존폼과 하위폼을 어떤 필드로 연결할건지도 묻는다.)

 

-텍스트[가 나] : 언바운드 칸에 수식 입력 가능 , 필드 이름은 대괄호로 묶는다 예) =Len([이름])

 

-레이블[단순 텍스트 입력]

 

 

 

7. 함수

 

 

 

DATEADD(형식, 값, 날짜) : DATEADD("M", 3, "2019-12-31")

 

DATEDIFF 

DATEPART : 인자로 주어진 날짜에서 부분만 출력

 

연 YYYY

일 Y : 일년 중 몇번째 날짜인지를 출력

일 D

요일 W

주 WW

 

보고서 선택기 우클릭으로 탭순서 조정 가능

 

 

STRCOMP는 같으면 0(FALSE) 다르면 -1(TRUE) 반환 // 다르면 참! 

다른 논리 함수들과 헷갈리지 말것  

 

 

 

도메인 계산 함수 : 도메인(테이블)에서 조건에 맞는 자료를 대상으로 한 인수(필드)의 계산 값 반환

 

(인수, 도메인, 조건) >> ( 필드, 원본테이블이나 원본쿼리, 조건 )

 

DSUM("납부금", "현황", "성별='남'") : 현황이라는 테이블에서 성별이 남자인 납부금의 합계 

 

 

 

현황 쿼리를 폼의 레코드 원본으로 설정하기

 

폼 디자인 보기 - 속성시트 - 선택유형 : 폼 / 속성시트 - 데이터 - 레코드원본 - '현황' 선택

 

속성시트 설정의 상단에서 각각의 컨트롤 속성 설정 가능, 데이터 - 컨트롤 원본에서 컨트롤과 연결할 필드 설정 가능

 

 

폼에서 특정 데이터에 조건부 서식 설정

 

폼 디자인도구 - 형식 - 컨트롤서식 그룹 - 조건부서식  - [새규칙] - 식이  예) LEFT([고객이름],1)="김"

 

 

 

 

8. 연습문제 스킵

 

 

9. 보고서 : 테이블이나 SQL의 데이터를 요약하여 출력하기 위한 개체

 

1. 보고서 구성

 

보고서 머리글

페이지 머리글

(그룹 머리글)

 

본문

 

(그룹 바닥글)

페이지 바닥글

보고서 바닥글

 

보고서 선택기

구역선택기

컨트롤

 

 

2. 만들기 - 보고서 그룹

 

보고서 : 빠른 시간 안에 간단한 보고서 자동으로 만들어줌

보고서 디자인 : 수동 작성

새 보고서 : 레이아웃 보기 상태에서 필드를 추가하여 보고서 작성

보고서 마법사

 

레이블 : 편지봉투 주소레이블 인쇄용 보고서로 작성

 

업무문서양식마법사 :

우편 엽서 마법사 

 

 

3. 보고서 만들기

 

보고서 마법사 - ~ - 인쇄 미리보기 닫기

디자인 보기에서 수정

 

보고서 마법사의 정렬 순서 정하는 부분에서 [요약 옵션] 버튼으로

숫자 정보 필드의 합계, 평균, 최소, 최대의 요약정보를 담을 수 있음

 

레이아웃보기에서 레이아웃 수정하면 더 편할 수 있다.

 

디자인 탭에서 선 긋기도 가능

 

 

페이지 바닥글이나 보고서 바닥글에 일반적으로 단가의 합계나 총 계를 많이 씀

 

> 텍스트 상자를 이용하여 단가 합계 넣어주기, 언바운드에 =SUM([단가])  수식 작성,

속성시트에서 통화 형식 지정

 

 

 

11. 조회

 

1. 필터

 

문자 : 문자를 따옴표로 감싸줘야함

 

Me.filter = " 제품명 = 'txt제품명' "

 

: 현재 개체의 필터 오브젝트에 " 제품명 = 'txt제품명' " 을 입력하고 싶은데

txt제품명은 필드 변수이므로 문자열과 연결하여 쓰려면 앤퍼센트로 연결해야한다 제품명과 작은따옴표는 문자열이므로 " "로 감싸준다.

 

Me.filter = " 제품명 = ' "  & txt제품명 &  " ' "

 

 

숫자 

 

Me.filter = " 제품명 =" & txt점수

 

 

날짜 : 날짜는 #으로 감싸줘야함 <<??왜

 

Me.filter = "날짜= #" & txt날짜 & "#" 

 

filterOn은 필터에 적용된 정의 조건을 폼이나 보고서에 적용할지 지정하는 개체

 

Me.filterOn = true //필터속성 적용

 

 

 

제품명찾기 버튼을 누르면 입력한 제품명과 같은 제품 정보를 찾아 표시되도록하기 

 

- 제품명찾기 버튼의 속성시트 - 이벤트 - On Click - [ ... ] (작성기 선택) - 코드작성기

 

private sub cmd제품명찾기_Click()   //버튼의 클릭이벤트 함수 틀이 자동으로 작성돼있으니 

 //이 블럭 안에 함수 식 작성하면 된다.

 

ME.FILTER="제품명=' " &txt제품명& " ' "

me.filterOn = true

 

end sub

 

 

2. <전체보기1>버튼을 클릭하면 폼에 모든 제품의 정보가 표시되도록 하시오.

 

-<전체보기1>버튼의 속석시트 - 이벤트 - On Click - [ ... ] - 코드작성기 

 

 

private sub cmd전체보기_Click() 

 

ME.FILTERON=false

 

end sub

 

 

 

2. RecordSource  // filter와 기능면에서 별 차이 없는듯

 

sql문은 ""로 묶어준다.

 

문자열은 ''로 묶어줘야하므로

 

 Me.recordsource = "  select * from 테이블 where 필드1 like '  * " & txt문자열 변수 &  "  *  '  "

 

like는 포함하는 문자열 찾을 때 사용

 

정리 ) 쿼리문은 ""로, 문자열은 ''로, 변수는 &로 감싼다.

 

 

연습 ) 이메일 일부를 입력하면 테이블에서 일치하는 레코드를 찾아서 보여줌

 

1. 폼의 속성시트의 데이터 - 레코드 원본을 확인한다.  ( 레코드 원본 : 제품별 판매현황 )

 

2. 버튼 - 속성시트 이벤트 - 클릭 함수 블럭 안에서 

 

me.recordsource = " select * from  제품별 판매현황 where 제품명 like ' * "  & txt이메일 &  " * ' "

 

 

 

 

3. recordsetclone : 주어진 쿼리나 폼의 recordsource 속성에 의해 지정된 테이블의 사본이다.

 

findfirt : 조건에 맞는 첫번째 레코드를 찾아 현재 레코드로 설정시킨다.

 

me.recordsetclone.findfirst "조건" : 현재 폼의 레코드를 복사한 후 조건에 맞는 첫번째 자료를 찾는다

 

Bookmark : 특정 레코드를 유일하게 식별하는 표식 

 

me.bookmark = me.recordsetclone.bookmark : 위에서 찾은 레코드의 북마크를 현재 폼의 북마크에 대입

 

여기서도 역시나 조건부분의 문자열부분은 ""로 감싸고 변수는 엔퍼센트로 감싼후 문자열변수이므로 ' '로 감싼다.

 

북마크는 데이터를 걸러서 보여주는게 아니라 해당되는 고유한 데이터를 표시만 해줌

굳이 recordsource 안쓰고 recordsetclone 사용하는 이유

 

현재폼에서 직접 레코드를 처음으로가서 하나씩 비교해서 조건식의 값을 만족하는 데이터가 있는지 확인해도 되지만 메모리에 가상의 복사본을 만들어서 거기서 찾아서 현재폼의 레코드 위치를 이동하는 것이 속도면이나 시각적인 면에서보면 훨씬 좋은 방법이기 때문입니다.

 

 

4. ADO  //ODBC 같은거라고 생각하면된다. 데이터베이스와 연결하여 데이터를 이용하기 위해 사용하는 오브젝트

//ActiveX Data Object 의 약자

 

Dim rs as ADODB.Recordset // 결과변수 rs 를 ado데이터베이스의 레코드 셋 메서드를 이용하여 선언

 

set rs = New ADODB.Recordset  // 변수 rs를 new 키워드를 사용하여 recordset 객체를 생성한다.

 

DoCmd.OpenForm "제품정보"  // 명령.제품정보 폼을 엽니다.

 

rs.ActiveConnection = CurrentProject.Connection 

//ado 객체를 담은 rs라는 변수의 연결에 현재 프로젝트의 연결을 대입해준다 

  : 데이터베이스와 현재 프로젝트가 연결된다.

 

rs.Open "select * from 제품판매별현황 where 판매번호 = " & txt판매번호 

 

// 연결된 데이터베이스를 열어 조건에 만족한 레코드를 불러와 rs 레코드에 할당.

   이하는 rs로 받아온 데이터를 폼에 옮기는 작업

 

Forms!제품정보. txt판매번호 = rs!판매번호 // 제품정보 폼의 판매번호 칸에 rs의 판매번호를 넣겠다.

.

.

.

 

rs.close //열었으니 닫아준다.

 

정리 ) 1. rs생성하여 2. ado 객체 넣고 3. 일단 폼을 연다(사용자가 볼 화면)  4. rs로 db연결  

   5. 조건에 맞는 db 연결 객체 오픈  6. rs에 담긴 레코드를 폼으로 옮김  7.  db 연결객체 닫아줌

 

 

 

13. 쿼리

 

퀴리 마법사

만들기 - 쿼리 그룹 - 쿼리 마법사

 

단순 쿼리 마법사

 

크로스 탭 쿼리 : 함수를 사용하여 테이블을 요약하는 쿼리 // 엑셀의 피벗테이블처럼 데이터 요약

 

중복 데이터 검색 쿼리 : 선택한 필드에 중복된 데이터가 있는 경우에만 데이터를 가져옴

 

중복된 정보가 있는 필드 선택 > 함께 표시할 필드 선택 // 중복이 없는 레코드는 가져오지 않음

 

불일치 검색 쿼리 : 두 테이블 비교하여 어느 한쪽을 기준으로 다른 한족에는 없는 레코드가 있는지 파악

 

쿼리 디자인

 

만들기 - 쿼리 그룹 - 쿼리 디자인

 

추가 / 업데이트 / 삭제 / 테이블 생성 / 요약(그룹화) / 계산  쿼리

 

쿼리 디자인을 누르면 쿼리 창이 생성되고 쿼리의 원본이 될 목록창이 뜬다.

 

테이블이나 쿼리를 선택해주면 필드명이 입력된 창이 생기고, 하단에 디자인할 쿼리 표가 생긴다.

 

필드명을 선택하면 하단의 쿼리 표에 입력된다. 

 

필드를 새로 작성할 수도 있다 예 ) 금액 : [단가]*[수량]  --> 금액 필드가 생성된다.

 

쿼리도구 디자인 - 표시/숨기기 그룹 - 요약

: 각 필수의 요약정보 (합계,)

 

매개변수를 받아 처리하기

 

조건 부분에 대괄호로 묶어 매개변수 받는 메시지 입력 예) [제품명을 입력하세요.]

 

쿼리 디자인이 끝나면 디자인탭 좌측의 실행 버튼 클릭

 

>> 이 때 매개변수를 받는 창이 뜨고 대괄호 안의 메시지가 사용된다.

 

제품명을 입력하면 해당되는 레코드로만 구성된 테이블이 만들어진다.

 

다시 디자인 탭으로 돌아와서 디자인 탭의 쿼리유형 그룹 - 테이블 만들기 를 클릭하면

쿼리 디자인에 해당되는 결과물 테이블이 만들어진다.

 

특정 필드값 변경하기 : 쿼리유형 그룹 - 업데이트

 

업데이트를 누르면 하단 표에 업데이트 행이 생긴다. 

조건 부분에 원본 데이터의 필드 "값 "을, 

업데이트 행 부분에 바꿀 필드 "값"을 입력하고 실행하면 필드값이 바뀐다.

 

특정 필드 값 삭제하기 : 쿼리유형 그룹 - 삭제

 

하단 삭제 행에 조건 이라고 쓰고 조건에 "삭제할 필드 값" 을 입력한다.

 

 

 

쿼리에 대한 속성도 속성 시트에서 설정 : 디자인탭의 속성시트, 하단 쿼리 디자인 표에서 우클릭 - 속성 

 

필드에 해당되는 셀의 서식 지정 가능

 

 

 

15. 처리

 

이벤트 프로시저 : 속성시트 - 이벤트 - [...] - 코드작성기

 

Docmd 개체 : 매크로함수를 비주얼베이직에서 실행하기 위한 개체

 

Docmd "폼이름", 보기형태, 필터이름 , "조건절" //뒤 세가지는 생략 가능(쉼표로만 구분)

 

 

MsgBox( "메시지" , 버튼종류 + 아이콘(생략가능) , "대화상자타이틀" )

 

버튼 종류 : vbOKonly / vbOKCancel / vbAborRetryorIgnore / vbYesNoCancel /vbRetryCancel

 

 

아이콘 종류 : vbCritic / vbQuestion / vbExclamation / vbInformation

 

 

 

 

0. 매크로 사용하라는 언급 없으면 코드작성

 

1. 속성시트에서 각 컨트롤 이름 확인

 

2. 폼같은 데이터를 열 경우에 조건을 지정하기 위해서는 폼에 위치한 컨트롤의 이름도 알아야한다.

    폼 디자인 - 디자인 보기에서 컨트롤 이름 확인

 

3. 표시돼야할 컨트롤에서 이벤트 설정

 

문제에서 요구하는 대로 차근차근 시행

 

4. 속성시트 - 이벤트 탭에서 제시된 함수 찾아 [...] 코드작성 

 

 

 

매크로

 

이벤트 - [...] -  매크로

 

또는

 

만들기 - 매크로 - 문제에서 제시하는 매크로 추가 (하나의 매크로에 두개 이상의 기능 추가 가능)

 

~의 형태로 여시오 --> 보기형식에서 지정

 

where 조건절에는 컨트롤의 명확한 경로를 입력해준다 예) [분류] = [form]![판매현황]![txt분류]

 

속성시트 - 이벤트 - 더블클릭 - 목록에 있는 만들어둔 매크로 선택