sql select문

계정 → 관리자 : sys, system
       ↘

            일반용 : scott , HR 
                          -> sample schema 설치해야 생김 (연습용 자료)

리눅스 -> $ sqlplus scott/tiger
          프로그램이름  계정  암호

오라클 -> sql> Conn scott/tiger

 

/home/oracle 디렉토리에 test_data.sql 넣고 scott 계정으로 로그인

sql에 적용방법 : scott> @/home/oracle/test_data.sql

 

sql로그인 하기

$ sqlplus scott/tiger

scott>

 

 -데이터베이스가 종료되어 있어서 연습용 계정으로 접속 안되고 에러 나는 경우

 

sys/oracle as sysdba 로 관리자 계정으로 접속후 startup으로 db시작

그 후 연습용 계정으로 로그인 sql> conn scott/tiger

 

현재 접속해 있는 계정 확인 = show user

set sqlprompt "_user>" 명령어 치면 프롬포트 모양이 계정이름으로 변경됨

 

로그인 할때 ORA-28000: the account is locked 라는 메세지가 나오면

관리자 계정으로 접속해 락 해제해주고 연습용 계정으로 로그인

sql> alter user scott identified by tiger account unlock ;

sql> conn scott/tiger

 

오라클 Table

행 - column

열 - row

 

/ -> 제일 마지막으로 쳤던 쿼리 입력

 

칼럼 길이 조정 하는 방법

데이터가 숫자일 경우

COL empno FOR 9999 : empno 라는 칼럼의 길이를 숫자 4자리까지 들어가게 설정

  (한자리당 9 하나씩)

데이터가 문자일 경우

COL ename FOR a8 : ename 라는 칼럼의 길이를 8바이트 까지 들어가게 설정    

 

 

 

표현식 사용하여 출력

select 구문뒤 '(홀따옴표) 로 묶어서 사용

(리터럴 안에 홀 따옴표가 있을 경우 홀따옴표 1개를 출력하기 위해 2개를 사용)

ex) 교수님 's 매너 최고!!를 출력하기 위해 '교수님 ''s 매너 최고!!'

 

별칭 사용

select 칼럼명 " " 를 사용

ex) name 의 칼럼명을 이름으로 출력하기 위해 select name "이름"

 

Alias 연습문제 1 > emp 테이블을 사용하여 empno 를 입사번호, ename 을 사원이름, job을 직급으로 별명을 설정하여 출력

> select empno "입사번호" , ename "사원이름" , job "직급" from emp ;

 

Alias 연습문제 2 > dept 테이블을 사용하여 deptno 를 부서번호, dname 부서명, loc를 부서의 지역으로 별명을 설정하여 출력

> select deptno "부서번호" , dname "부서명" , loc "부서의 지역" from dept ;

 

 

DISTINCT 명령어 - 중복된 값을 제거하고 출력

정렬 <-이전 10g R2 이후-> 정렬x  hash함수 사용

hash = 입력값이 같으면 출력값이 같고, 입력값이 다르면 출력값이 다르게 나오는 함수

DISTINCT 함수는 컬럼이 많으면 사용하면 안됨 , SELECT문 뒤에 와야한다.

 

연결 연산자

|| 사용

 

연결 연산자 문제 1 > 아래 그림과 같이 출력

> select name||' 의 ID는 '||ID||' 이고 , 체중은 '||weight||' 입니다. ' "ID 와 체중 "FROM student ;

 

 

연결 연산자 문제 2 > 아래 그림과 같이 출력

> select ''||ENAME||'('||JOB||') ,'||ENAME||''''||JOB||'''' "이름과 직업" FROM emp ;

 

원하는 조건만 골라내기 - WHERE

SELECT [Column or Expression]

FROM [Table or View]

WHERE 원하는 조건

※where 절은 반드시 from 절 아래에 와야 함

- 숫자 조회시 그대로 쓰면 되지만 문자와 날짜를 조회 할 때는 해당 문자나 날짜에 홀따옴표를 붙여야 함

- 문자는 대소문자 구분을 하고 날짜는 대소문자 구분 없음

 

기본 산술연산자 사용

일반적으로 사용되는 산수 계산 잘 됨. (사칙연산 할때 우선순위 조심)

 

다양한 연산자 활용

 

 

BETWEEN 사용하여 사이 값 출력

ex)2000 ~ 3000 사이인 사람들의 empno,ename,sal 출력

BETWEEN - SELECT empno,ename,sal FROM emp WHERE sal BETWEEN 2000 AND 3000 ;

연산자 활용 - SELECT empno,ename,sal FROM emp WHERE sal >= 2000 AND SAL <= 3000 ;

※위와같이 특정 구간의 값을 검색할 때 BETWEEN 연산자보다 비교 연산자를 사용하여 쓰는 것이 훨씬 빠르다.

 

IN 연산자로 여러 조건을 간편하게 검색

SELECT empno,ename,deptno

   2  FROM emp

   3  WHERE deptno IN (10,20) ;

- emp 테이블에서 deptno 가 10,20 인 모든 사원들의 empno, ename, deptno 출력

 

LIKE 연산자로 비슷한 것들 모두 찾기

 

   

 

LIKE 연산자를 쓸 때 문자나 날짜를 처리하기 위해 홀따옴표를 사용

 - % : 글자수 제한 없고 (0개 포함) 어떤 글자가 와도 상관 없음

 - _ (Underscore) : 글자수는 한글자만 올 수 있고 어떤 글자가 와도 상관없음

 

ex) 이름 글자수가 3글자 인데 성은 "박" 이고 마지막 글자가 "주" 인 사람을 찾는다면

      WHERE name LIKE '박_주' ;  이런식으로 검색

※LIKE 연산자를 쓸 때 % 나 _ 를 가장 먼저 쓰면 안된다. 먼저 쓰게 될 경우 인덱스 때문에 속도가 느리게 검색된다.

 

값이 무엇인지 모를 경우 - IS NULL / IS NOT NULL

NULL 값은 값이 얼마인지 모른다는 의미

ex) 1 ~100 사이의 값이 들어와야 하는 곳에 값이 안들어오면 NULL, 초과해서 들어오면 N/A 

 

NULL값을 조회하고 싶으면 IS NULL 

NULL값을 제외하고 조회하고 싶으면 IS NOT NULL

 

 

 

검색조건이 두 개 이상일 경우 조회

두 조건을 동시에 만족해야 하는 경우 AND, 두 조건중 하나만 만족해도 되는 경우 OR

 

 

정렬하여 출력하기 - ORDER BY 절

정렬의 기본값은 오름차순. (날짜는 최근 날짜가 더 큼)

오름차순 = ASC  내림차순 = DESC

 

ename 순서대로 오름차순 정렬             

SELECT ename,sal,hiredate

   2 FROM emp ;

   3 ORDER BY ename ;

 

dept 순으로 오름차순으로 선 정렬후 동일 dept가 있을시 sal 값으로 내림차순으로 정렬

SELECT ename,sal,hiredate,deptno

   2 FROM emp

   3 ORDER BY deptno ASC , sal DESC ;

 

ORDER BY 2,1 ; 이런식으로 숫자로 사용할 때 도 있는데 이 숫자의 의미는 SELECT 절에 오는 컬럼의 순서를 뜻함. 즉 2 란 sal을 뜻 하고 1 은 ename을 뜻함.

sal로 먼저 정렬 후 동일한 sal 값이 있을 경우 ename으로 한번 더 정렬을 하라는 의미

SELECT ename, sal, hiredate

  2 FROM emp

  3 WHERE SAL > 1000

  4 ORDER BY 2,1 ;

 

집합 연산자

연산자 종류	내 용
UNION	두 집합의 결과를 합쳐서 출력. 중복 값 제거하고 정렬함
UNION ALL	두 집합의 결과를 합쳐서 출력. 중복 값 제거 안하고 정렬 안 함
INTERSECT	두 집합의 교집합 결과를 출력함. 정렬함
MINUS	두 집합의 차집합 결과를 출력함. 정렬함. 쿼리의 순서 중요함

 

※ 위 4가지 집합 연산자를 사용할 경우 주의 사항

- 두 집합의 SELECT 절에 오는 칼럼의 개수가 동일해야 함

- 두 집합의 SELECE 절에 오는 칼럼의 데이터 형이 동일해야 함

- 두 집합의 칼럼명은 달라도 상관없음