SELECT 데이터 검색

SQL 문 SELECT 데이터 검색 INSERT UPDATE DML(데이터 조작어) DELETE CREATE ALTER DDL(데이터 정의어) DROP TRUNCATE GRANT REVOKE DCL(데이터 제어어) Database

SELECT PAY, PAY*1.1 FROM PERSONNEL; SELECT PNAME, 12*(PAY+100) FROM PERSONNEL; 산술식 사용, FROM절을 제외한 모든 절에서 사용 가능 연산자 우선 순위, 괄호 사용 기본 SELECT 문 SELECT * FROM PERSONNEL; 테이블에 있는 데이터의 모든 열을 표시 SELECT PNO, PNAME, PAY FROM PERSONNEL; 테이블의 특정 열을 표시, 쉼표로 구분 Database

SELECT PAY+300 ASNEWPAY FROM PERSONNEL; SELECT PAY+300 “New Pay” FROM PERSONNEL; SELECT ‘pay : ’|| PAY FROM PERSONNEL; 열 별칭 정의, AS 키워드 대소문자, 공백 구분시 큰따옴표 사용 연결연산자, 리터럴 문자열 사용 SELECT BONUS, BONUS+200 FROM PERSONNEL; 산술식에서 열의 값이 null이면 결과는 null. Database

SELECT DISTINCT DNO FROM PERSONEL; DESCRIBE PERSONNEL; 중복 행 제거 테이블 구조 표시(약어: DESC) Database

ASC DESC 오름차순, 기본값 내림차순 ORDER BY 절 1. ORDER BY 절을 사용하여 행을 정렬 2. SELECT 문의 가장 끝에 위치 3. 열 별칭, 열 위치 사용 가능 4. SELECT 목록에 없는 열도 기준 가능 5. 여러 열을 기준으로 질의 결과 정렬 Database

SELECT PNAME, JOB FROM PERSONNEL ORDER BY PNAME ASC; SELECT PNAME, JOB FROM PERSONNEL ORDER BY PNAME, JOB DESC; SELECT PNAME, JOB, PAY*12 N_PAY FROM PERSONNEL ORDER BY N_PAY; PNAME 기준으로 오름차순, ASC는 생략 가능 PNAME 기준으로 오름차순, JOB 기준으로 내림차순 정렬 열 별칭을 기준으로 정렬 Database

1. 데이터베이스 객체 2. 조인 3. 기본 함수

1. 데이터베이스 객체 • Table : 기본 저장 단위, r*c • Index : 질의의 성능 향상 • View : 하나 이상 테이블 데이터의 부분집합(논리적) • Synonym : 객체의 다른 이름 • Sequence : 자동 증가 값(기본키 생성) Database

테이블, 열 이름 지정 규칙 • 문자로 시작 • 최대 30byte(영문 30자, 한글 15자) • 영문자, 숫자, _, $, # • 다른 객체의 이름과 중복되지 않도록 • Oracle-Server의 예약어와 중복되지 않도록 • 대소문자를 구분하지 않음 Database

CREATE TABLE 문 사용 • Create table권한 필요 • Create table dept ( deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13) ); 데이터 유형 및 크기 열 이름 Database

SUB QUERY 구문 사용 1 • Create table권한 필요 • CREATE TABLE DEPT30 AS SELECT empno, ename, sal*12 “Salary” FROM EMP WHERE DEPT=30; (empno, ename, salary) 열 별칭 명시 Database

SUB QUERY 구문 사용 2 • 테이블 복사 • CREATE TABLE NEW_EMP AS SELECT * FROM EMP; WHERE 0=1; • 제약 조건은 전달되지 않음 • 계정 소유의 테이블 확인(user_tables) 레코드까지 복사 컬럼만 복사 Database

데이터 유형 Database

VARCHAR2 VS CHAR • MSG1 VARCHAR2(100) • MSG2 CHAR(100) • MSG1=‘ORACLE’ • MSG2=‘ORACLE’ • LENGTH(MSG1)=5 • LENGTH(MSG2)=100 Database

NUMBER(P, S)=1234567.89 1,234,567.89 • NUMBER : 제한없음(최대 40자리) • NUMBER(9) : 전체9자리(소수점 첫째자리에서 반올림) • NUMBER(9,2) : 전체9자리, 소수점 이하 2자리 • NUMBER(9,1) • 전체9자리, 소수점 이하 1자리(둘째자리에서 반올림) • NUMBER(7,-2) : 소수점 기준 왼쪽 2번째에서 반올림 • NUMBER(3,5) • 소수점 5자리, 소수점 이하 맨 앞에 ‘0’이 (5-3=2)개 붙음 • P<S일때는 1보다 작은 실수를 입력할 때 사용 • Ex) 0.00123, 0.0012 1,234,568 1,234,567.89 1,234,567.9 1,234,600 Database

기본 값 정의 • 행 삽입 수행시 열의 기본값 정의 • 리터럴값, 표현식, SQL함수 가능 • 다른 열의 이름 불가 • 유형은 해당 열의 데이터 유형과 일치 • 널 값 입력 방지 • Hiredate DATE DEFAULT SYSDATE • Gender CHAR(4) DEFAULT ‘남자’ Database

테이블 수정(ALTER TABLE 문) • 새 컬럼 추가 (ADD) • 기존 컬럼 수정(MODIFY) • 새 컬럼의 기본값 정의(DEFAULT) • 컬럼 삭제(DROP) • 제약 조건 수정(추가, 삭제 작업만) Database

테이블 수정(ALTER TABLE 문) • alter table dept add gender char(1); • alter table dept modify gender number(1); • alter table dept modify gender default ‘male’; • alter table dept drop column gender; Database

테이블 삭제 • 삭제(DROP TABLE) • DROP ANY TABLE 권한 • 뷰와 시노님은 유지(사용 불가) • 보류 중인 트랜잭션 모두 커밋 • 자동 커밋(DDL) • 절단(TRUNCATE TABLE) • 모든 행 제거 후 저장공간까지 해제 • DELETE TABLE 권한 • 롤백 정보를 생성하지 않음(DDL문) • 외래키의 부모인 경우 절단 불가 Database

VIEW • VIEW 사용 목적 • 데이터 액세스 제한 • 복잡한 질의의 단순화 • 데이터 독립성 제공 • 동일한 데이터로부터 다양한 결과 도출 Database

단순 뷰, 복합 뷰 • Simple view • 한 테이블에서만 데이터 추출 • 함수나 데이터 그룹을 포함하지 않음 • 뷰를 통한 DML작업 가능 • Complex view • 여러 테이블에서 데이터 추출 • 함수나 데이터 그룹을 포함 • 뷰를 통한 DML작업 제한 Database

단순 뷰, 복합 뷰 • Create view salvu as select empno, ename, sal from emp where sal >= 2000; • Create view dept_sum as select dname, sum(sal) as s_sal from emp, dept where emp.deptno=dept.deptno; Database

뷰 수정 • CREATE OR REPLACE 절 사용 • 동일한 이름의 뷰가 있어도 다시 생성 • 이전 뷰 권한 삭제 후 다시 부여 Database

뷰를 통한 DML 작업 1 • 뷰에서 행을 제거할 수 없는 경우 • 그룹 함수 • GROUP BY 절 • DISTINCT 키워드 • ROWNUM 키워드 Database

뷰를 통한 DML 작업 2 • 뷰에서 행을 수정할 수 없는 경우 • 그룹 함수 • GROUP BY 절 • DISTINCT 키워드 • ROWNUM 키워드 • 표현식에 의해 정의된 열 Database

뷰를 통한 DML 작업 3 • 뷰에서 행을 추가할 수 없는 경우 • 그룹 함수 • GROUP BY 절 • DISTINCT 키워드 • ROWNUM 키워드 • 표현식에 의해 정의된 열 • 뷰에 의해 선택되지 않은 열에 NN조건 Database

뷰를 통한 DML 작업 4 • WITH CHECK OPTION • Create or replace view dno20 as select * from emp where deptno=20 with check option constraint emp20_ck; • WITH READ ONLY • Create or replace view dno20 as select * from emp where deptno=20 with read only; 뷰의 조건 안에서 DML 작업 허용 DML 작업 거부 Database

뷰 제거 • 기반 테이블은 영향을 받지 않음 • DELETE문은 테이블에 영향 • 뷰의 생성자/DROP ANY VIEW 권한 • DROP VIEW NEW_EMP; Database

TOP-N 질의 • 최대/최소값 집합 • 정렬 + ROWNUM + WHERE 조합 • 정렬 : 최대값은 내림차순 정렬 • ROWNUM : 반환되는 행에 순차값 할당 • 조건절 : 반환할 행 개수 제한 Database

TOP-N 질의 • 최대/최소값 집합 • 정렬 + ROWNUM + WHERE 조합 • 정렬 : 최대값은 내림차순 정렬 • ROWNUM : 반환되는 행에 순차값 할당 • 조건절 : 반환할 행 개수 제한 • Emp테이블에서 급여 상위 5명 출력 • Select rownum, ename, sal from emp whererownum<=5 order by sal desc; Database

INDEX • 행 검색 속도를 높이기 위해 사용 • 디스크 입출력 부하를 줄임 • 인덱스화된 테이블과 독립적으로 존재 • 오라클 서버가 자동 유지 관리 • 테이블 삭제시 같이 삭제됨 Database

인덱스 생성 방법 • 자동 • PRIMARY KEY • UNIQUE 제약 조건 • 수동 • 사용자가 직접 생성 • 열에 고유하지 않은 인덱스 Database

인덱스 생성이 필요한 경우 • 열에 광범위한 값이 포함되는 경우 • 열에 널 값이 많이 포함된 경우 • 조건절/조인 조건에서 하나 이상의 열이 함께 자주 사용되는 경우 • 큰 테이블에서 대부분의 쿼리에 의해 검색되는 행이 2%~4% 미만인 경우 Database

인덱스 생성이 필요하지 않은 경우 • 테이블이 작은 경우 • 조건절로 자주 사용되지 않는 경우 • 대부분 쿼리가 테이블 행의 4%이상 검색 • 테이블이 자주 갱신되는 경우 • 인덱스도 같이 갱신되므로 Database

인덱스 확인 및 제거 • 확인 • USER_INDEXES 뷰 • USER_IND_COLUMNS 뷰 • 제거 • 인덱스 소유자/DROP ANY INDEX 권한 • DROP INDEX 문 Database

동의어(SYNONYM) • 객체의 다른 이름 • 객체의 단순화 • 다른 사용자가 소유한 테이블을 쉽게 참조 • 긴 객체 이름을 짧게 Database

동의어 생성 및 제거 PUBLIC : 모든 사용자가 액세스 • 동의어 생성 • CREATE SYNONYM D_SUM FOR DEPT_SUM_VIEW; • 제거 • DROP SYNONYM D_SUM; Database

SEQUENCE (사전적 의미 : 속발, 연속) • 고유 번호를 자동으로 생성 • 공유 가능한 객체 • 기본 키 값 생성시 사용 • 응용 프로그램 코드 대체 • 메모리 캐시 → 액세스 효율 Database

시퀀스 생성(CREATE SEQUENCE) 시퀀스 객체명 • Create sequence seq Increment by 2 Start with 100 {Maxvalue n | Nomaxvalue } {Minvalue n | Nominvalue } {Cycle | Nocycle} {Cache n | Nocache}; 간격(def:1) 초기값(def:1) 최대값 최소값 반복여부(def:no) 미리 할당(def:20) Database

시퀀스 확인 및 사용 • USER_SEQUENCES 테이블 • Select * from user_sequences; • NEXTVAL • 새 시퀀스 번호 생성 • 연속적 시퀀스 번호 추출 • CURRVAL • 마지막으로 생성한 시퀀스 번호 참조 Database

시퀀스 사용 • personnel의 pno에 값 넣기 • Create sequence pnum start with 1500 nocache; • Insert into personnel(pno, pname) values(pnum.nextval, ‘PAUL’); Database

시퀀스 수정 • ALTER SEQUENCE 문 • 시퀀스 소유자 혹은 ALTER권한 • 작업 이후 시퀀스 번호에만 영향 • 재시작 : 삭제 후 다시 생성 • 일부 검증 수행 • EX: CURRVAL <= MAXVALUE Database

시퀀스 제거 • DROP SEQUENCE 문 • 시퀀스 소유자 • DROP ANY SEQUENCE 권한 • 더 이상 참조할 수 없음 Database

2. 조인(테이블간 관계) • 조건절 • 내부 조인(INNER JOIN) • 외부 조인(OUTER JOIN) • 자체 조인(SELF JOIN) Database

조건절 : WHERE • 반환되는 행을 제한 • From절 다음에 위치 • 구성 • 열 이름 • 비교 조건 • 열 이름, 상수 또는 값 목록 Database

GROUP BY ~ HAVING • 행 집합에 작용하여 그룹당 하나의 결과 생성 • 그룹 함수의 종류 • AVG,COUNT,MAX,MIN,SUM, … • SELECT DNO, COUNT(*) FROM PERSONNEL GROUP BY DNO HAVING PAY >= 2000; Database

비교 조건 • =, >, >=, <, <=, <>, !=, ^= • BETWEEN ~ AND ~ • 두 값 사이(지정한 값 포함) • IN(A, B, …) • 값 목록 중의 값과 일치 • LIKE : 문자 패턴 일치 • IS NULL : 널 값 비교 Database

BETWEEN 조건 사용 • 값의 범위에 따라 행을 표시 • SELECT * FROM PERSONNEL WHERE PAY BETWEEN 2000 AND 3000; (작은 값을 먼저 기술) Database

IN 조건 사용 • 값이 목록에 있는지 확인 • SELECT ENAME,JOB,SAL FROM EMP WHERE JOB IN(‘CLERK’, ‘MANAGER’); Database

SELECT 데이터 검색