[컴퓨터활용능력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