[컴퓨터활용능력1급]수업 정리 7

<쿼리-조건, 질의, 질문> 
: 하나 이상의 테이블/쿼리에서 원하는 정보를 얻어내는 것, 요약, 계산 만들기


선택쿼리 : 요약 쿼리
- 요약 클릭
- 묶는 방법=그룹화(~~별, 부분합 기준) 설정 (같은 단어나 성격끼리만 묶임)
- 숫자나 계산이 필요한 필드는 함수를 선택, 합계, 평균, 개수


선택 쿼리 : 쿼리 속성 이용
- 중복값 제외 : 쿼리 속성에서 고유값 지정(distinct)
- 상위/하위 몇 개 또는 몇 % 선택이나 지정 : 쿼리 속성에서 상위값에서 지정


선택 쿼리 : 매개변수 쿼리
- 매개변수 메시지는 조건영역에 [ ]안에 입력
- 마침표는 입력하면 안되고, 큰 따옴표 안에도 들어갈 수 없다.

예) Like "*" & [주연배우 이름을 입력하세요] & "*"



Not In(SQL문) : (   )안에 있는 자료는 제외하고
- Not In (select 전화번호 from 대여)
- 불일치 검색 쿼리, 삭제 쿼리, 중복 데이터 검색 쿼리, 업데이트 쿼리, 추가 쿼리에 활용


크로스탭 쿼리
- 엑셀의 피벗테이블과 비슷
- 한 개의 테이블이나 쿼리만 이용하는 경우 : 크로스탭 쿼리 마법사 사용이 편리
- 두 개이상의 테이블이나 쿼리를 이용하는 경우 : 쿼리디자인에서 쿼리 유형 중에 크로스 탭 쿼리 종류 선택하고 시작
- 행머리글 3개까지 가능-첫열,둘째열(필기 문제)
- 열머리글 1개만 가능-첫행
- 요약(합계, 총계)열은 행머리글 다음에 나오거나 행머리글 갯수에 포함


업데이트/추가/삭제 쿼리(문제에 그림이 없으면)
- 쿼리 디자인을 실행하고, 디자인 창에서 업데이트/추가/삭제 쿼리 유형을 먼저 선택
- 쿼리 결과는 지정 테이블에 반영되는 것이므로 테이블을 열어서 확인
- 쿼리 실행 전에 원본 확인
- 실행 부분을 여러 번 실행이 불필요하므로 한 번만 실행
- 수정시 마우스 오른쪽 디자인 보기로 실행


<프로시저 쿼리 실행>

Docmd.OpenQuery "쿼리명"  : 쿼리를 실행

Me.requery    : 폼 새로고침의 개념처럼 변형된 자료를 다시 적용



<데이터베이스 언어> SQL 
: 데이터베이스 정의, 조작, 제어를 하는 언어

- DDL(데이터베이스 정의어)
Create(테이블 생성), Drop(테이블 삭제),
Alter(필드 추가/삭제/변경)

- DML(데이터베이스 조작어)
Select(레코드 검색), Insert(레코드 삽입), 
Update(레코드 수정), Delete(레코드 삭제)

- DCL(데이터베이스 제어어)
Grant(권한 부여), Revoke(권한 박탈), 
Rollback(원상태로 복구), Commit(완료)



select [Distinct] */필드명1, 필드명2...              
from 테이블명
[where 조건]
[group by 필드명/그룹명] [having 조건]
[order by 정렬필드명] [asc]/desc;
//
select 이름, 주소, 학과/ * 
from 학생
where 성별='여'
group by 학과 having count(*)>=20
order by 학년 desc;


: 학생 테이블에서 학과 인원이 20명 이상인 학과의 여학생 이름과 주소, 학과 필드(모든 필드)만 학년에 따라 내림차순해서 표시(추출, 검색)

[Distinct] : 중복되는 필드는 한 번만 (고유값)


where 등급 in(1, 3)  -> where 등급=1 or  등급=3


where 등급 not in(1, 3)  -> where 등급<>1 or  등급<>3


where 나이>=20 and 나이<=29 

-> where 나이 between 20 and 29



<와일드카드, 만능문자 : 검색 조건>
? = _  : 한 글자
* = % : 모든 글자


이름  like '이%'  : 이, 이호, 이순신, 이~~~~
        like '이_' : 이호

이름  = '이순신'

email like '%@%' and not like '% %'

where 이름 like '이%'  


where 날짜>=#2022-1-1# AND 날짜<=#2022-12-31#

where 날짜 Between #2022-1-1# and #2022-12-31#



insert into 테이블명(필드명1, 필드명2...)
values(값1, 값2...);
//
insert into 학생(이름, 주소...)
values('홍길동', '서울',....);

insert into 학생
values('홍길동', '서울',....);

: 학생테이블에 홍길동 학생의 데이터를 추가
   (홍길동이라는 학생이름, 주소가 서울....)





update 테이블명 
set 필드명=값
where 조건
//
update  학생 
set  주소='부산'
where  이름='홍길동';

update  회원
set  가입일=date()
where  가입일 is null;


: 홍길동 학생의 주소를 부산으로 변경
: 회원 테이블의 가입일이 알 수 없으면(비었으면) 오늘 날짜로 입력




delete * 
from 테이블명
where 조건
//
delete * 
from 학생
where  이름='홍길동';

: 학생테이블에서 홍길동의 자료를 삭제


drop table 학생
: 학생테이블을 제거


docmd.RUNSQL "sql문"




DCL(데이터베이스 제어어)
Grant(권한 부여), Revoke(권한 박탈), 
Rollback(원상태로 복구), Commit(완료)


Grant all on 학생 Table to 김부장 [with grant option]

: 김부장한테 학생테이블이 대한 모든 권한을 준다(다른 사람한테 권한을 줄 수 있음).


Revoke All  [grant option for] on 학생 Table to 김부장  

: 김부장한테 부여된 학생테이블에 대한 모든 권한을 박탈(다른 사람한테 부여한 권한도 함께)




<연산자, 함수>


- 산술/대입 연산자 : +, -, *, /, ^, mod, &

13 mod 2


- 논리/비교 연산자 : and, or, not, >, >=, <, <=, =, <>, like, is 

a>80 and b>=70 access
>=0 and <=100

A or B or C

in(A, B, C) : A, B, C 중에 하나만

not like ~~~
not null
is not null

not in(A, B, C) : A, B, C는 제외하고


like   : 유사연산자, 와일드카드(*)와 같이 사용

이름 = "홍길동"
이름 like "홍*"


is  : 비교, null은 =(등호) 대신 is 사용
is null 
select  *
from  사원
where  입사일 is null
order by  사번 asc/desc

is not null



<SQL 집계 함수>

sum([필드명])
avg([필드명])
count(*)  : 모든 필드를 개수체크
max([필드명])
min([필드명])



<날짜/시간 함수>


=date() : 현재 날짜
=now() : 현재 날짜와 시간
=time() : 현재 시간

=year(), month(), day(), hour(), minute(), second()


=weekday([날짜], 1) 일요일이 1번 ~ 토요일 7번 : 요일번호
=weekday([날짜], 2) 월요일이 1번 ~ 일요일 7번 : 요일번호


=dateadd("단위", 숫자, 날짜) : 날짜에 숫자(년/월/일)를 더한 날짜


=datediff("단위", 시작일, 종료일)  : 경과일/월/년 수

단위 : yyyy, q, m, y, d, w, ww, h, n, s

나이 구하기 => datediff("yyyy", [출생일], [현재일])



=dateserial(년, 월, 일) : 날짜 형태로 표현

생년월일 : dateserial(left([주민번호],2), mid([주민번호],3,2), mid([주민번호],5,2))



<문자 처리 함수>

=left([필드명], 글자수)
=right([필드명], 글자수)
=mid([필드명], 시작위치번호, 글자수)

=len([필드명]) :글자수
데이터 유효성 검사 : len([상품코드]) = 5
상품코드를 5글자로 작성해라.


trim([필드명]) : 하나만 남기고 모든 공백 제거
ltrim([필드명]) : 왼쪽 공백 제거
rtrim([필드명]) : 오른쪽 공백 제거



=lcase(텍스트): 소문자
 
=ucase(텍스트) : 대문자


 
=instr([주민번호], "-")  : 결과가 7 , 텍스트의 시작위치 번호 추출

데이터 유효성 검사 : instr([주민번호], " ") = 0
 주민번호는 공백문자가 입력되지 않도록 해라         (0은 찾는 문자가 없을 경우가 0값으로 돌려줌)


space(6)      : 공백문자를 6번 반복

string(6, "*") = ****** : 텍스트 개수만큼 반복



=replace(텍스트, "현재 있는 글자", "새 글자") - access
  : 치환(스펠링부분만 고려) 

=replace("excel2007", "2007", "2022")  : excel2022
=replace("김 길 동", " ", "")   : 김길동





<선택 함수>


=iif(명제, 참일때 값, 거짓일때 값) :명제가 참이면 참일때 값, 거짓이면 거짓일때 값

iif(mid([주민번호], 8, 1)=1, "남", "여")
iif(mid([주민번호], 8, 1)=1 or mid([주민번호], 8, 1)=3, "남", "여")
iif(mid([주민번호], 8, 1) mod 2, "남", "여")


=choose(값, 1일때 값, 2일때 값~~~~29일때 값) : 경우를 선택하는 함수
choose(mid([주민번호], 8, 1), "남", "여", "남", "여")



=iif([성적]>=80, "상", iif([성적]>=60, "중", "하"))


=switch(조건1, 조건1에 값, 조건2, 조건2에 값, 조건3, 조건3에 값)

=switch([성적]>=80, "상", [성적]>=60, "중", [성적]<60, "하")



<자료 형식 변환 함수>

cdate(인수)  : 날짜로 변환
cint(인수)  : 정수로 변환
val(인수)  : 텍스트를 숫자로 변환

cstr(인수)  : 인수를 텍스트로 변환
str(숫자)  : 숫자만 텍스트로 변환



<자료 형식 평가 함수>

=isdate(인수)  : 날짜인지 확인
=isnull(인수)  : null 값인지 확인, 비었니?
=iserror(인수)

true  : -1, 1
false  : 0


<숫자 계산 함수>

=rnd() : 0~1의 난수, 무작위 실수


=round(수, 자릿수) : 반올림, 자릿수는 결과기준, 
일의 자릿수 0, 소수점이하는 양수, 십의 자리 이상은 음수

=abs(수) : 절대값
=int(수) : 정수




<지정된 형식으로 표현하는 함수>

=Format(값, "형식") : 지정된 형식으로 표현

format([비율], "0.0%")  -> 15.0%
format(date(), "mm/dd")  -> 10/25


**\135,000으로 표시 (메시지 상자 형식 표현-vb)

msgbox format(금액, "\\#,##0") & "입니다."


<액세스의 도메인 함수>

=dsum("필드명", "도메인명", "조건") : 도메인(테이블/쿼리) 조건에 맞는 것만 합계
큰따옴표는 꼭 입력~!!!
필드명 = 테이블이나 쿼리(도메인)의 열이름
도메인 = 테이블, 쿼리의 이름
조건 : "도메인 필드명=컨트롤명/값"

davg("필드명", "도메인", "조건") : 도메인(테이블/쿼리) 조건에 맞는 것만 평균
dcount("필드명", "도메인", "조건"): 도메인(테이블/쿼리) 조건에 맞는 것만 갯수
dmax("필드명", "도메인", "조건"): 도메인(테이블/쿼리) 조건에 맞는 것 중에 최대값
dmin("필드명", "도메인", "조건"): 도메인(테이블/쿼리) 조건에 맞는 것 중에 최소값
dlookup("필드명", "도메인", "조건") : 도메인(테이블/쿼리) 조건에 맞는 것을 추출


성적테이블에서 여학생의 수학점수의 평균
davg("필드명", "도메인", "조건")

=davg("수학", "성적", "성별='여'")


성적테이블에서 해당 학생(텍스트 컨트롤에 써있는 이름)의 총점을 조회하세요.
=dlookup("총점", "성적", "이름=txt조건")


dsum("매출액", "판매현황", "상품명='모자'")
   : 판매현황 테이블에서 모자의 매출액의 합계
  조건 값의 텍스트 형식은 작은 따옴표안에 표현 

dsum("매출액", "판매현황", "상품명=컨트롤명")


조건 :  "상품명='모자'"   - 텍스트는 작은따옴표
"수량>=10"  - 숫자는 X
"매출일>=#2019-1-1#"  - 날짜는 #안에

"수량>=10 and 수량<=20"
"상품코드 like 'a*'" - *, ? 와일드카드 사용하면 like와 함께 사용
         
"지역코드=txt조건"
"지역코드=cmb지역코드"
"지역명=cmb지역명"





=dsum("매출액", "판매현황", "지역코드=txt지역코드")
=davg("매출액", "판매현황", "지역코드=txt지역코드")


조건 : "판매현황.지역코드=지역매출.txt지역코드"
 
       "테이블.필드명=폼.텍스트상자 컨트롤명"
       "테이블.필드명=폼.콤보상자 컨트롤명"
       "쿼리.필드명=폼.텍스트상자 컨트롤명"
       "쿼리.필드명=폼.콤보상자 컨트롤명"


조건부 서식 -> 식이 선택하고 -> 식 작성시에는 맨 앞에 '='는 제외

left([교번], 1)=8