[SQL] 1. 오라클 서버접속, 기본 SELECT 구문

오라클 서버 접속

오라클계정

관리자용 : sys, system

일반용(연습용) : scott, HR (dbca 과정에서 sample schema 체크/ 안했으면 수동으로 설치해야함)

서버접속

리눅스 : $ sqlplus scott/tiger (프로그램명 계정/암호)

db 종료 되어있어 접속안되면 관리자 계정으로 접속해 db를 시작해줘야함

ERROR:

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0

• Enter user-name: sys/oracle as sysdba
• SQL>startup
• SQL> CONN scott/tiger

오라클을 처음 설치할 경우에는 보안 문제로 연습용 계정인 scott 계정을 사용 못하도록 막아두기 때문에 아래와 같은 에러가 발생할경우 관리자계정으로 접속해 계정을 풀어줘야함

ERROR:

ORA-28000: the account is locked

• Enter user-name: sys/oracle as sysdba
• SYS> ALTER USER scott IDENTIFED BY tiger ACCOUNT UNLOCK;

계정 확인: SQL>show user

프롬프트를 현재 접속해 있는 계정이름으로 변경: SQL> SET sqlprompt "_USER>"

데이터 가져오기

SELECT

SELECT [컬럼명 or 표현식] FROM [테이블명 or 뷰(view) 명

• SQL>SELECT * FROM emp; 모든 컬럼조회

DESC : 특정 테이블에 어떤 칼럼들이 있는지 조회(describe)

• SQL> DESC dept ;

모든 테이블 조회: SELECT * FROM TAB;

Column 변경

Column 길이 조정

• 숫자 데이터: SQL> COL 컬럼명 FOR 9999 (자리수만큼 9할당)
• 문자 데이터: SQL>COL 컬럼명 FOR a15 (자리수만큼 숫자할당)

화면 길이 조정

• 한 화면에 출력가능한 가로길이: SQL>SET LINE 200
• 한 화면에 출력가능한 세로길이: SQL>SET PAGES 50

표현식(Expression) = 리터럴 상수

• 컬럼명 이외에 원하는 내용으로 출력할수 있음.
• SELECT 구문 컬럼명 뒤에 '원하는내용'로 묶어서 사용
• '를 출력하려면 ''사용

기본 산술 연산자: +, -, *, / 사용해 출력가능 (우선순위 주의)

별칭(column alias) 사용: 일시적으로 출력

• 컬럼명 "별칭" // 별칭에 공백이나 특수문자, 대소문자 구분이 필요할 경우 쌍따옴표 필수
• 컬럼명 AS "별칭"
• 컬럼명 별칭 // 쌍따옴표 하지 않을경우는 모두대문자로 출력

연결(합성) 연산자(Concatenation): - || 서로 다른 컬럼을 하나의 컬럼처럼 연결해서 출력 *백업/복구 작업이나 데이터베이스 운영 관련 작업, 튜닝 작업등에 아주 많이 사용됨.

SQL>SELECT ename||'''s job is '||job " NAME AND JOB"
 2  FROM emp;

 NAME AND JOB
-----------------------------
SMITH's job is CLERK
ALLEN's job is SALESMAN
WARD's job is SALESMAN

DISTINCT

중복된 값 제거하고 출력

1개의 칼럼에만 적어도 모든 칼럼에 적용해 시간이 많이소요 (성능저하 발생) ​* 10g R1 까지는 정렬이용 R2부터는 hash 알고리즘 이용(성능개선)

반드시SELECT 다음에 와야 하며 그렇지 않을 경우 에러발생.(ORA-00936)

SQL>SELECT DISTINCT job, ename
 2  FROM emp
 3  ORDER BY 1,2;

JOB       ENAME
--------- ----------
ANALYST   FORD
ANALYST   SCOTT
CLERK     JAMES
CLERK     SMITH

WHERE

SELECT [컬럼 or 표현식] FROM [table or view] WHERE 조건

문자 & 날짜 조회 시 ' ' 사용해야함 (숫자이외에 모두 홑따옴표 사용)

• 문자의 경우 대소문자 구분 (날짜는 구분안함)
• 날짜의 경우 윈도우와 리눅스 날짜 형식 다름 * 윈도우) yy/mm/dd, 리눅스) dd-MON-yy

연산자 활용가능

• = 같은 조건 검색
• !=, <> 같지 않은 조건 검색
• > (<) 큰(작은) 조건 검색
• >= (<=) 크(작)거나 같은 조건 검색 숫자,문자,날짜 모두 비교가능(날짜의경우 최신날짜 일수록 값이큼)
• BETWEEN a AND b a와 b사이에 있는 범위값 검색 ( a, b값 모두 포함/ 한글은 b포함 안됨) * 속도면에서 위의 비교연산자가 더 빠름
• IN(a,b,c) a거나 b거나 c인 조건 검색 (속도빠름)
• LIKE 특정 패턴 있는 조건 검색, 아래의 두 기호와 함께쓰임. 홑따옴표필수
% : 글자수 제한X(0포함)
_ : 한글자 %나 _를 먼저 쓰면 SQL성능최악임(Index때문). 주의할것.
• IS NILL / IS NOT NULL Null 값/아닌값 검색 =연산 사용못함
• A AND B A,B 모두 만족하는 값 검색 (우선수위: 괄호, AND > OR)
• A OR B A,B 중 하나라도 만족하는 값 검색
• NOT A A가 아닌 모든 조건 검색

사용자에게 조건 입력받고 그 조건에 맞는 값 출력: & WHERE절 이외에 입력을 받아야 할곳에 모두 쓰일수있음

ORDER BY

오름차순ASC(default), 내림차순DESC 컬럼명 뒤에 명시

SQL 문장에서 가장 마지막에 사용

컬럼명대신 SELECT절에 오는 칼럼의 순서를 숫자로 사용 가능

많이 쓸수록 성능저하되므로 최대한 쓰지않는게 좋음. INDEX 사용 권장.

SET OPERATOR (집합 연산자)

UNION 합집합, 중복 값 제거, 정렬

UNION ALL 합집합, 중복 값 제거X, 정렬X

INTERSECT 교집합, (정렬동반(12c기준) 데이터 많으면 속도저하)

MINUS 차집합, 정렬(쿼리순서 중요)

• 두 집합의 SELECT 절에 오는 칼럼의개수와 데이터형이 동일해야함.
• UNION & UNION ALL 많이 연결할수록 성능 저하되므로 DECODE, CASE 등 다른 방법 사용해 작성할것.

기타

SQL>ed // vi 로 수정

SQL>/ // 마지막에 실행했던 sql 실행

SQL>SET verify OFF //설명부분 해제

[출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저