Access 노트

 

<데이터베이스 개요>
*데이터베이스란?
유용한 데이터의 집합으로, 예를 들어 학교에서는 학사관리를 위해 데이터들을 모아 두어야 하고,
도서관에서는 도서관리를 위해서도 데이터들을 모아서 관리해야 한다.
이렇게 다양한 데이터들을 관리하기 위해 데이터를 저장만 해두면 아무런 의미가 없다.
사용자가 원하는 정보를 쉽게 찾을 수 있어야 유용한 정보라 할 수 있다.
도서관에 수십만권의 도서에 대한 정보가 저장되어 있다 하더라도
원하는 도서에 대한 정보를 찾을 수 없다면 아무런 쓸모가 없게된다.
이렇게 검색에 용이하게 데이터를 저장하도록 하는 것이 바로 데이터베이스이고,
이를 관리하는 소프트웨어를 DBMS라고 한다.
DBMS로는 오라클, MS-SQL, Sybase, Mysql, 액세스 등이 있다.

*액세스의 주요 개체
 테이블, 쿼리, 폼, 보고서, 데이터 액세스 페이지, 매크로, 모듈
1)테이블:데이터베이스에서 가장 주요한 개체로 데이터 저장소라고 한다.
2)쿼리:테이블에 저장된 데이터를 가공하여 검색, 추출하는 기능을 하는 곳이다.
3)폼:테이블에 데이터를 직접 입력하는 것이 다소 번거로울 수 있으므로,
폼을 통해 필요한 필드만 보여주고 수정하는 것이 좋다.
폼은 사용자와의 인터페이스를 제공하는 곳이다.
4)보고서:프린터를 통해 원하는 데이터를 출력하고 문서화하는 곳이다.
요약 및 집계 기능이 있어 쉽고 빠르게 효율적인 보고서를 만들 수 있는 곳이다.
5)데이터 액세스 페이지:인터넷 또는 인트라넷에서 액세스 데이터베이스나 MS SQL Server
데이터베이스에 저장된 데이터로 작업하거나 볼 수 있는 웹 페이지를 작성하는 곳이다.
6)매크로:반복되는 작업을 자동화하기 위한 동작의 모음으로, VBA언어로 기록된다.
7)모듈:VB으로 데이터베이스에서 사용할 수 있는 Sub 및 Function프로시저를 작성할 수 있다.

*관계형데이터베이스(Relational DB) : 테이블과 테이블을 연결.

*SQL(구조적 질의 언어) : DB언어

<테이블 작성 실습>
1.데이터 형식
텍스트 : 문자나 숫자를 저장할 수 있으며 255자까지 가능.
            (계산이 필요없는 숫자, 텍스트)
메모 : 텍스트와 비슷한 기능을 제공하면, 최대 65536자까지.
숫자 : 계산에 사용할 숫자만 저장.
날짜/시간 : 날짜오 시간 저장
통화 : 계산에 사용할 수 있는 통화 값, 숫자, 소수자릿수 형식 등이 저장.
일련번호 : 일련 번호난 난수가 저장되며 업데이트 불가능.
OLE개체 : 액세스 이외의 프로그램에서 만들어 액세스 테이블에 연결.
포함되는 개체가 저장.(엑셀파일, 사운드파일, 이미지파일,...)

2.필드 속성
-필드 크기 : 필드의 최대 문자 수 설정
-형식 : 필드에 표시되는 형식
-입력 마스크 : 입력될 형식 설정
-캡션 : 필드에 대한 이름
-기본값 : 새로 추가될 레코드에 기본 입력되는 값
-유효성 검사 규칙 : 필드에 입력될 값을 제한(조건 설정)
-필수 : 필드에 값이 반드시 필요한지 여부를 지정.
-인덱스 : 검색 및 정렬 속도를 빠르게 하고 데이터가 고유하게 함.
-IME 모드 : 필드에 영문자만, 한글만 입력되게 설정.
-기본키(Primary Key) : 테이블에서 레코드를 유일하게 구별할 있는 값이 들어있는 필드 하나 이상을 의미함.
테이블을 다른 테이블의 외래 키에 연결하는 데 사용함.특징-> 1)유일한 값    2)Not null 이어야 함
   ex)주민번호, 학번, 고객코드, 도서코드,...
*Null : 알려지지 않은 데이터(아무 값도 정의하지 않은 상태)

*입력 마스크 : 필드에 서로 다른 방식으로 입력되는 것을 방지하고 지정한 형식에 맞추어 데이터를 더 쉽게 입력하기 위해 사용.
(입력 방식을 지정)
   ex)
   전화번호: (___)____-____

   \(999\)9999\-9999;0;_
   <마스크 문자>
     0(필),9(옵) : 숫자만 입력
     L(필),?(옵) : 문자만 입력
     A(필),a(옵) : 숫자/문자
     >(대),<(소) : 대.소문자 변환
     \ : 뒤에 나오는 문자를 그대로 표시
  
     ex) \서울00-00    ->서울__-__
          >LL-0000       ->__-____
          \\0000         ->\____
 
*유효성 검사 규칙 : 필드에 입력되는 값을 제한.(필드에 조건 설정)
  - >,<,>=,<=,=,<>
  - and,or,not,in,between~and,like
  - 함수
  ex) >=0 and <=100  =  between 0 and 100
       국민 or 삼성 or BC or LG  = in(국민,삼성,BC,LG)
       <>0, >=50000
       len([비밀번호])>=5

  ※와일드카드 문자 : ?, *
     77*, *1급, *컴퓨터*, *@*,  *김치*
     액세스는 와일드카드 문자를 사용하려면 반드시 like와 함께
      ex) like "*@*"
 
<테이블 관계 설정>
-테이블의 공통 필드 사이의 연결 방법으로, 일대일, 일대다, 다대다 관계.
-관계 설정을 위해서는 기본키가 있어야 참조 무결성유지 등을 유지할 수 있다.
*관계 유형 : 일대일,일대다,다대다
*참조 무결성
  무결성이란? 결함이 없음.
관련된 테이블간의 관계를 유효하게 하고 사용자의 실수로 데이터을 삭제하거나 변경하지 않도록 하기 위해 따르는 규칙.
관련된 테이블 사이에는 기본 키와 외래키에 의해 참조되며, 불일치의 데이터가 나타나지 않도록 하는 것,
참조 무결성 강화를 위한 조건으로는 기본키가 설정되어야 하며, 관련된 필드는 데이터 형식이 같아야 함.

<액세스 함수>
*계산함수
  형식) 함수명(계산할필드명)
  sum,avg,count,max,min
  ex) =sum(가격), =avg(대여금액), =count(*),

*조건함수
  iif(조건,참,거짓),...
  ex)=iif(반납일자>반납예정일,(반납일자-반납예정일)*200)
      =iif(평균<70,"재수강","")
      =iif(c성별=true,"남자","여자")

*도메인 함수 : 조건에 맞는 데이터만 계산
  dsum,davg,dcount,dmax,dmin,dlookup
  형식)함수명("계산할 필드명","테이블/쿼리","조건")
  ex) =dsum("가격","도서목록","종류='소설'")
       =dlookup("도서명","도서목록","도서코드='CS-111'")
*문자열 함수
  right,left,mid,len,...

*날짜/시간 함수
  date,now,time,year,month,day,hour,minute,second,... 
  date: 현재 날짜만, now: 현재 날짜와 시간까지.

*숫자 계산 함수
  round,int,abs,...
 
<쿼리>
쿼리 종류 : 선택, 매개변수, 실행, 크로스탭,...
*계산필드 생성/기존 필드명에 별명을 부여
  콜론(:)을 사용
  ex) 총점:기말고사+중간고사+수행평가
       학생이름:성명

*매개변수 쿼리 : 쿼리 실행시에 매개변수 값을 입력.
  작성법: 조건을 입력하는 란에 []or [메시지]를 입력
             단, .와 !는 사용 금지
  ex) like "*" & [검색할 도서명은?] & "*"
           
*쿼리에서 중복을 제거하는 명령
   distinct
*선택(체크박스)는 선택하면 true(-1), 선택되지 않으면 false(0)값을 가짐.

*실행쿼리 : 실제로 테이블에 변경을 가함.
                 삽입,삭제,갱신
                 필요한 필드만 입력.

*테이블 조인(Join) : left,right,inner
  1)inner join : 두 테이블에서 동일한 필드만
  2)left join : 왼쪽 테이블은 모두 다 표시, 오른쪽 테이블은 왼쪽 테이블과 일치하는 것만.

*select문
select [distinct] 필드명,...
from 테이블명
where 조건문
order by 필드명 asc/desc
group by 필드명,...

ex)도서목록 테이블에서 종류가 수필인 도서만 검색(도서명순으로 정렬)
select *
from 도서목록
where 종류='수필'
order by 도서명

*서브쿼리 : 기존 쿼리문에 있는 조건문에 select문이 들어감.반드시 괄호안에 넣어야 함.
ex)가장 비싼 도서의 이름과 가격은?
select 도서명,가격
from 도서목록
where 가격=(select max(가격) from 도서목록)

--------------------------
| 본점.제품코드:1,2,3,4         |
| 종로점.제품코드:1,3,5,7,9    |
--------------------------
ex)본점에서만 판매된 제품코드는?
select 제품코드,제품명
from 본점
where 제품코드 in(select 제품코드 from 종로점)

(참고) in : 교집합, not in : 차집합

<매크로>
*매크로 : 반복되는 과정을 자동화.
*매크로 함수의 종류:
openform,openreport,close,msgbox,applyfilter,runsql,outputto,requery,quit,gotorecord,gotocontrol,...

openform : 지정한 폼 열기
openreport : 지정한 보고서 열기
close : 지정한 개체 닫기
msgbox : 메시지 출력
applyfilter : 검색 
        폼이나 보고서의 원본이 되는 테이블이나 레코드를 제한 또는 정렬. 
gotocontrol : 지정한 필드나 컨트롤로 포커스를 이동
gotorecord : 해당 레코드로 이동
runsql : 실행 쿼리를 실행
requery : 개체 원본을 다시 쿼리
outputto : 내보내기
showallrecords : 필터를 제거하고 모든 레코드를 표시
findnext : 조건에 맞는 레코드들을 계속 찾는다

*폼/보고서의 특정 개체를 참조할 때
  forms!폼이름!개체명     ex)forms!검색!t조건
  reports!보고서이름       ex)reports!고객별대여목록

*하위폼/보고서의 특정 개체를 참조할 때
  상위폼이름.form!개체명  ex)납품내역입력.form!txt납품수량

*runsql함수
  실행쿼리(삽입,삭제,갱신)를 작성하는 매크로 함수.
1)삽입쿼리문
   insert into 테이블명(필드명,...) values(값,...)
예)insert into 강좌(강좌코드,강좌명) values('K150','알고리즘')
  insert into 강좌(강좌코드,강좌명,월수강료) values(t1,t2,t3)

2)삭제쿼리문
   delete */필드명 from 테이블명 where 조건
예) delete * from 강좌 where 강좌명=t2

3)갱신쿼리문
   update 테이블명 set 필드명=값/수식 where 조건
예)update 강좌 set 월수강료=월수강료*1.05

*매크로 함수 작성법
 1)매크로 작성기를 사용
 2)코드 작성기를 사용(이벤트 프로시저)
    코드 작성기에서 매크로 함수를 사용하려면 반드시
    docmd개체를 사용하여야 함.
    형식) docmd.매크로함수  인수,...
    예) docmd.close
         docmd.openform "고객등록",acNormal
         docmd.openform "고객별도서대여내역",acNormal,,"고객코드=forms!고객등록조회!t고객코드"

<검색>
※ 폼의 속성을 이용한 검색(조회) 이벤트 프로시저 작성하기.
폼 개체의 속성값 중 filter, recordsource,recordsetclone.findfirst를 이용하면
특정 조건의 레코드를 검색할 수 있습니다.

1. Filter 속성 사용
 형식)
   Me.Filter=조건식
   Me.FilterOn=True
★ 참고사항 :  filter 속성은 select쿼리문에서 where 뒤에 나오는 조건을 입력.
★ 참고사항 :  Me.FilterOn=False 를 지정하면 필터기능을 해제하게 됨.
★ 참고사항 : Me는 현재의 폼을 말함.

2.RecordSource 속성 사용
 형식)
   Me.RecordSource=“select문”
★ 참고사항 : recordsource 속성은 select문 전체를 입력함.

3.RecordsetClone, Bookmark, FindFirst속성 사용
 형식)
   Me.RecordsetClone.FindFirst “조건식”
   Me.Bookmark=Me.RecordsetClone.Bookmark
★ 참고사항 :RecordsetClone.FindFirst 는 위의 두 속성과는 결과가 다르게 나옴.
조건에 맞는 레코드를 레코드 선택기가 가르키고 있음.

* Me.RecordsetClone.FindFirst : 현재 폼(me)의 레코드 원본을 복사한 후 복사된 개체(recordsetclone)에서
해당 조건과 처음으로 일치하는 값을 찾는다(findfirst)
* Me.Bookmark : 현재 폼의 책갈피 속성(bookmark)에 복사된 개체(recordsetclone)에서
찾은 책갈피 속성(bookmark)를 지정한다.

예)고객 테이블에서 입력한 고객코드의 고객 검색
ⓐ
Me.Filter=“고객코드='” & C고객코드 & “'”
Me.FilterOn=True
ⓑ
Me.RecordSource="select * from 고객  where 고객코드='” & C고객코드 & “'”
ⓒ
Me.RecordsetClone.FindFirst “고객코드='” & C고객코드 & “'”
Me.Bookmark=Me.RecordsetClone.Bookmark

*폼 속성을 이용한 검색시 패턴 정리

1.검색 대상인 필드의 값이 문자인 경우
    ... "필드명='" & 개체이름 & "'"
2.검색 대상인 필드의 값이 날짜인 경우
    ... "필드명=#" & 개체이름 & "#"
3.검색 대상인 필드의 값이 숫자인 경우
    ... "필드명=" & 개체이름
4.입력한 문자를 포함(시작,끝)하는 것을 검색할 경우
   포함: ... "필드명 like '*" & 개체이름 & "*'"
   시작: ... "필드명 '" & 개체이름 & "*'"
      끝: ... "필드명 like '*" & 개체이름 & "'"

 

 

출처 : ☆오늘보다 더 나은 내일은 없다☆

글쓴이 : 쥬얼 원글보기

메모 :