2008년 07월 01일
오라클 기본문법 사용방법.
원하는 문자열이 포함된 테이블만 검색
select table_namefrom tabs
where table_name like 'KS%';
테이블 개수만 출력
select count(table_name) from tabs;
- 간단한 기능을 실행하는 문장들
공백, AS, ""로 별명 부여 가능 (""를 사용 출력시 공백,대소문자 구분 가능)
||''|| => 칼럼사이에 입력, 하나의 칼럼처럼 연결 출력, ''사이에 내용 입력 가능
칼럼 값에 산술연산자를 적용하여 계산된 결과 출력 가능
SET HEADING OFF; => 칼럼 제목 없이 출력
SET LINESIZE 100 => 행의 크기 설정 (100)
SET PAGESIZE 20 => 출력 페이지 크기 설정 (20), 20행 이상 칼럼 제목 재출력
DESC : 내림차순 정렬 , ASC : 오름차순 정렬(기본 값)
SUBSTR - 문자열의 일부 추출 (칼럼명,시작위치,찾을문자열개수)
* 시작위치가 음수일 경우 뒤에서부터
ROUND(반올림) / TRUNC(내림) / MOD(나머지)
ROUND(123.17,1) => 123.2
TRUNC(123.17,1) => 123.1
MOD(12,10) => 2
- SELECT 문 - 여러가지 조건을 주어 검색 가능
- 기본 문장
SELECT [필드이름] FROM [테이블이름]
- DISTINCT : 중복 없이 검색
예) 제품 구입을 한번이라도 한 적이 있는 고객 검색SELECT DISTINCT cid
FROM ksdb_sales;
- WHERE : 조건 검색
예) 이름이 '이민우'인 학생 정보 검색
SELECT * FROMksdb_student WHERE name='이민우';
예) Email 주소가 없는 온라인 고객 검색
SELECT * FROM ksdb_customer_onlineinfo
WHERE email is null;
예) 고객 중 1980년 이전 출생자 이면서 여자인 고객의 결혼 여부 검색
SELECT cid,name,bday,married FROM ksdb_customer_info
WHERE bday <'1980-1-1' AND sex='1';
연산자
=
>
<
>=
<=
<>
AND
OR
NOT
의미
같다
크다
작다
크거나
같다
작거나
같다
같지
않다
논리곱
논리합
부정
- LIKE 연산자 : 원하는 문자열 검색
예) 주소가 서울인 고객 검색
SELECT cid, name, address, tel FROM ksdb_customer_info
WHERE address LIKE '서울%';
예) 주민등록상으로 남자인 고객 검색
SELECT cid, name, job, tel, sid FROM ksdb_customer_info
WHERE sid LIKE '_______1%';
=> %나 _를 문자로 인식하게 할 경우 앞에 '/'입력
* 가나% :앞에 '가나'가 오는 모든 문자열
* %가나% : 어느 위치든 '가나'가 포함되어 있는 모든 문자열
* %가나 : 뒤에 '가나'가 오는 모든 문자열
* ' _ ' 는 자리 수를 의미
* ___가 : 네번째에 '가'가 오는 문자열
* __가___나 : 세번째에 '가', 일곱번째에 '나'가 오는 문자열
- IN 키워드 : 한 필드에서 검색하고자 하는 값이 많을 때 묶어서 사용
예) 직업이 공무원이거나 회사원인 고객 검색 SELECT cid, name, job FROM ksdb_customer_info
WHERE job IN('공무원','회사원');
*** IN 키워드를 사용하여 검색할 때 '%'이용한 검색은 불가능
예) 주소가 서울, 경기인 고객을 검색하고자 할 때,
SELECT cid, name, address, tel FROM ksdb_customer_info
WHERE address IN('서울%','경기%');
'%'가 문자로 인식되어 문자그대로 '서울%'과 '경기%'를 검색,
오류가 나지 않고 '선택된 레코드가 없습니다'라고 나오게 된다.
(값이 '가%'인 자료가 있을 때 IN키워드를 이용하여 '가%'를 검색하면 '가%'가 검색되어 출력됨)
- GROUP BY : 한 필드에서 같은 자료끼리 그룹화, 각 그룹에 대해 원하는 결과 출력
- HAVING : 그룹화 한 후 조건 검색
예) 직업별 고객 빈도중 같은 직업이 3명 이상인 직업 검색
SELECT job, count(*) as freq FROM ksdb_customer_info
GROUP BY job
HAVING count(*)>2;
*** freq는 출력 시 이름이기 때문에 HAVING 절에서 count(*) 대신 freq를 쓸 경우 오류 발생
- ORDER BY : 필드의 데이터를 정렬하여 검색
예) 온라인 고객을 가입일, 아이디 순으로 검색 => 오름차순으로 정렬됨
SELECT cid, lid, jday FROM ksdb_customer_onlineinfo
ORDER BY jday, lid;
예) 고객별로 제품을 구매한 정도를 내림차순으로 검색
SELECT cid, count(*) as freq FROM ksdb_sales
GROUP BY cid
ORDER BY count(*) DESC;
- rownum 조건 : 레코드 수 제한
예) 교과목 성적을 상위레코드 3개에서 내림차순으로 검색SELECT * FROM ksdb_score
WHERE rownum<4
ORDER BY score DESC;
*** 구매를 가장 많이한 고객을 3순위까지 검색
SELECT cid, count(*) as freq FROM ksdb_sales
WHERE rownum<4
GROUP BY CID
ORDER BY count(*) DESC;
잘못된 결과 출력 :
CID FREQ
------ ----------
100004 1
100011 1
100010 1구매를 많이 한 순으로 검색시 :
CID FREQ
------ ----------
100002 11
100001 9
100010 7
100009 6
100011 6
100004 5
100003 3
100006 2
100007 1=> 3행까지라는 조건에서 카운트하고 그 결과를 오름차순으로 정렬하기 때문에 위와같은 잘못된 결과가 출력된다.
그러나 where 문을 order 문 아래에 쓸 경우 오류가 발생,
따라서 이 문제를 해결하려면 rank 분석함수를 사용해야 한다.
- JOIN : 여러 테이블을 조합해서 원하는 데이터 추출
SELECT [필드리스트] FROM 테이블_이름
[join_type] JOIN 테이블_이름
ON 조건
- INNER JOIN : 두 테이블에 같이 존재하는 레코드 연결
예) 고객번호, 이름, 로그인ID, Email 검색
=> 고객 정보는 ksdb_customer_info 테이블에,
온라인 고객 정보는 ksdb_customer_onlineinfo 테이블에 존재
같은 정보로 들어있는 cid 필드를 연결해서 레코드 검색
SELECT c.cid, name, lid, email
FROM ksdb_customer_info c
INNER JOIN ksdb_customer_onlineinfo co
ON c.cid=co.cid
ORDER BY c.cid;
*** ksdb_customer_info.cid=ksdb_customer_onlineinfo.cid
간단한 별명으로 표시
ksdb_customer_info => c , ksdb_customer_onlineinfo => co
예) 학생들의 과목별 성적 검색
=> 세개의 테이블 연결
ksdb_Student : ID/ Name/ Dept/ Grade/ PID
ksdb_Score : ID/ SubjectID/ Score
ksdb_Curriculum : SubjectID/ SubjectName
SELECT s.id, name, subjectname, score
FROM ksdb_student s
INNER JOIN ksdb_score sc
ON s.id=sc.id
INNER JOIN ksdb_curriculum c
ON sc.subjectid=c.subjectid
ORDER BY s.id;
OUTER JOIN : 기준테이블 설정, 기준테이블의 레코드는 모두 포함하고 연관테이블 정보 추가 LEFT OUTER JOIN / RIGHT OUTER JOIN
예) 고객 정보에 온라인 정보 추가하여 검색
SELECT c.cid, name, lid, email
FROM ksdb_customer_info c
LEFT JOIN ksdb_customer_onlineinfo co
ON c.cid=co.cid;
SELECT c.cid, name, lid, email
FROM ksdb_customer_onlineinfo co
RIGHT JOIN ksdb_customer_info c
ON co.cid=c.cid;
- UNION : 테이블 연결 검색
예) 서울 영등포지점과 경기 수원지점에 있는 물품을 중복없이 모두 검색SELECT pid, name FROM ksdb_ydp
UNION
SELECT pid, name FROM ksdb_sw;
* 집합연산자
UNION : 합집합(중복제외) / UNION ALL : 합집합(중복포함)
MINUS : 차집합 / INTERSECT : 교집합
- IN, EXISTS : 중첩 질의
- IN : 다른 테이블에서 어떤 조건으로 검색된 레코드의 정보 추출
예) 점수가 90점이상인 학생의 이름과 학년 검색SELECT id, name, grade FROM ksdb_student
WHERE id IN (SELECT id FROM ksdb_scoreWHERE score>=90);
- EXISTS : 존재 여부를 이용한 검색
예) 서울 영등포지점에는 있지만 경기 수원지점에는 없는 물품 검색SELECT pid, name FROM ksdb_ydp y
WHERE NOT EXISTS (SELECT pid FROM ksdb_sw sWHERE y.pid=s.pid);
- 함수
수학 함수
문자열 함수
날짜 함수
ABS : 절대값
SIN, COS, ... : 삼각함수
CEIL, FLOOR, ROUN : 올림, 내림, 반올림
EXP : 지수
dbms_random.value(0,1) : 0에서 1사이의 난수
LENGTH : 문자열 길이
LOWER, UPPER : 소문자, 대문자로 변환
LTRIM, RTRIM : 문자열 좌측, 우측 공백 제거
REPLACE : 특정 문자열을 다른 문자열로 바꿈
SUBSTR : 문자열의 특정 부분을 얻음
To_char : 숫자 값을 문자열로 변환
SYSDATE : 시스템의 날짜와 시각 반환
Extract(YEAR, MONTH, DAY from date) : 날짜에서 년, 월, 일 반환
- 예제
- 예1) Sales 테이블에서 2001년 4월의 고객별 구매회수가 3회 이상인 고객 검색
SELECT cid, count(*) as freq FROM ksdb_sales
WHERE extract(year from sday)=2001 and
extract(month from sday)=4
GROUP BY cid
HAVING count(*) >=3;
SELECT cid, count(*) as freq FROM ksdb_sales
WHERE sday>='2001-4-1' and sday<'2001-5-1'
GROUP BY cid
HAVING count(*) >=3;
CID FREQ
------ ----------
100009 4
100001 7
100002 5
100003 3
- 예2) 과목별 평균 점수를 오름차순으로 검색
SELECT c.subjectname, avg(s.score) as Mean
FROM ksdb_curriculum c
INNER JOIN ksdb_score s
ON c.subjectid=s.subjectid
GROUP BY c.subjectname
ORDER BY avg(s.score) DESC;
SUBJECTNAME MEAN
-------------------- ----------
재무관리 93
데이터베이스 92.3333333
통계소프트웨어 89
정보통신개론 89
생산관리 83
*** GROUP BY 문을 INNER JOIN 문 앞에 썼을 경우 오류
INNER JOIN ksdb_score s
*
4행에 오류:
ORA-00933: SQL 명령어가 올바르게 종료되지 않았습니다
- 예3) 1학년을 제외한 학생들의 정보를 학년, 학과, 지도교수 순으로 검색
SELECT * FROM ksdb_student
WHERE grade <> '1'
ORDER BY grade, dept, pid;
ID NAME DEPT GRADE PID
--------- ---------- --------------- ---------- ----
200172058 이민우 경영 2 5233
200140133 홍영준 산업공학 2 2044
200040394 김지선 산업공학 3 2042
200020182 김지선 통계 3 1031
200020183 박수현 통계 3 1031
# by | 2008/07/01 12:17 | 데이터베이스 | 트랙백
