나만보는 코딩공부

뷰(VIEW) (가상 테이블)

데이터 엑세스를 제한하고 복합한 질의를 쉽게 작성하기 위해서 테이블을 재생성하며

데이터 접근을 제한하고 그 질의를 쉽게 작성할 수 있도록 도와준다.

VIEW 생성

CREATE [OR REPLACE] VIEW 뷰이름 AS 쿼리;

ex)

CREATE OR REPLACE VIEW emp10_view

as SELECT empno, id_number, ename name, sal * 12 ann_salary FROM emp WHERE deptno = 10;

VIEW를 통한 데이터 변경

일반적으로 Views는 조회용으로 많이 사용되지만 아래와 같이 데이터를 변경할 수 있다.

ex)

UPDATE emp10_view SET name='SCOTT' WHERE id_number=7839;
SELECT * FROM emp10_view; --변경된 데이터 확인한다.
SELECT * FROM emp;  --emp테이블의 KING이 SCOTT으로 변경된다.

INSERT INTO emp10_view (id_number,name,ann_salary)
VALUES (8000,'JOHN',19000);
-> 가상 열(ann_salary)은 사용할 수 없습니다. 오류 발생한다.

INSERT INTO emp10_view (id_number,name)
VALUES (8000,'JOHN');
->가상 열을 제외하면 삽입 가능하다.

SELECT * FROM emp10_view;
->10번 부서만 보여지게 제한이 걸려서 삽입한 것이 안 보여진다.

SELECT * FROM emp;
-> emp테이블에 1행이 추가되어있다.

with read only (읽기 전용 뷰를 생성하는 옵션)

create or replace view emp20_view
as SELECT empno id_number,ename name,sal*12 ann_salary
   FROM emp
   WHERE deptno=20
with read only;   

UPDATE emp20_view SET name='DAVID' WHERE id_number=7902;
읽기 전용이기 때문에 수정 불가능하다.

view 삭제
drop view emp10_view;

Sequence : 유일한 값을 생성해주는 오라클 객체
 시퀀스를 생성하면 기본키와 같이 순차적으로 증가하는 컬럼을
 자동적으로 생성할 수 있다. 보통 primary key 값을 생성하기
 위해서 사용한다.

create sequence test_seq
start with 1
increment by 1
maxvalue 100000;
시작 값이 1이고 1씩 증가하고, 최대값 100000이 되는 시퀀스 생성

currval : 현재 값을 반환
nextval : 현재 시퀀스 값의 다음 값 반환

SELECT test_seq.nextval FROM dual;
SELECT test_seq.currval FROM dual;

시퀀스의 수정
alter sequence sequence_name
increment by n
maxvalue n | nomaxvalue
minvalue n | nominvalue
cycle | nocycle

start with는 수정할 수 없음

시퀀스 삭제
drop sequence sequence_name;

인덱스(index)

인덱스는 원하는 정보의 위치를 빠르고, 정확하고, 지능적으로 알아낼수 있는 방법을 제공한다.

테이블의 컬럼에 대한 제약 조건을 설정할 때 primary key, unique로 설정하면 oracle은 자동으로 이 컬럼에 대해 unique index를설정한다.  

인덱스 만들기

자동 : 데이블 정의에 PRIMARY KEY 또는 UNIQUE 제약 조건을 정의
      하면 고유 인덱스가 자동으로 생성
수동 : 사용자가 열에 고유하지 않은 인덱스를 생성하여 행에 대한
      액세스 시간을 줄일 수 있다.

유일한 값을 가지는 컬럼에 인덱스 설정 : unique index
CREATE UNIQUE INDEX dname_idx ON dept (dname);

유일한 값을 가지지 않는 컬럼에 인덱스 설정 : non unique index
CREATE INDEX emp_ename_idx ON emp (ename);

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

인덱스를 생성하지 않아야 할 경우
- 테이블이 작은 경우
- 열이 질의의 조건으로 자주 사용되지 않은 경우
- 대부분의 질의가 테이블에 있는 행의 2% ~ 4% 이상을 검색할 경우
- 테이블이 자주 갱신되는 경우
- 인덱스화된 열이 표현식의 일부로 참조되는 경우

동의어 : 동의어(객체의 다른 이름)를 생성하여 객체 액세스를 단순화한다.
- 다른 사용자가 소유한 테이블을 쉽게 참조한다.
- 긴 객체 이름을 짧게 만든다.
CREATE SYNONYM synonym_name FOR object;

동의어 생성
CREATE SYNONYM emp20 FOR emp20_view;
SELECT * FROM emp20_view;
SELECT * FROM emp20;

동의어 삭제
DROP SYNONYM emp10;

데이터베이스 트랜잭션

트랜잭션 : 데이터 처리의 한 단위이다. 데이터가 일관되게 변경하는 DML문장으로 구성된다.

오라클 서버에서 발생하는 SQL문들을 하나의 논리적인 작업단위로서 성공하거나

실패하는 일련의 SQL문을 트랜잭션이라 할 수 있다.

1) 트랜잭션의 시작 : 실행 가능한 SQL문장(DML)이 제일 처음 실행될때

2) 트랜잭션의 종료 :  COMMIT(변경사항 저장) & ROLLBACK(변경사항 취소)

1. DDL이나 DCL문장의 실행(자동 COMMIT)

2. 기계장애 또는 시스템 충돌

Deadlock발생

사용자가 정상종료

COMMIT과 ROLLBACK의 장점 

1. 데이터의 일관성을 제공한다.

2. 데이터를 영구적으로 변경하기 전에 데이터 변경을 확인하게 한다.

3. 관련된 작업을 논리적으로 그룹화 한다.

4. COMMIT 과 ROLLBACK 문장으로 트랜잭션의 논리를 제어한다.

테이블 : 기본적인 데이터를 저장하는 저장 단위이다.

레코드(record, row) -> 테이블의 데이터는 행에 저장한다.

칼럼(column) -> 테이블의 각 칼럼은 데이터를 구별할 수 있는 속성을 표현한다.

데이터 딕셔너리 : Oracle Server가 생성 및 유지 관리하는 테이블의 collection

-사용자가 소유한 테이블의 이름

SELECT table_name FROM user_tables;

-사용자가 소유한 개별 객체 유형

SELECT DISTINCT object_type FROM user_objects;

-사용자가 소유한 테이블, 뷰, 동의어 및 시퀸스

SELECT * FROM user_catalog;

테이블 생성예시

create table employee(

 empno number(6),

 name varchar2(30) not null,

 salary number(8,2),

 hire_date date default sysdate,

 constraint employee_pk primary key (empno)

);

칼럼의 구조에 대한 상제 정보를 조회할떄는 describe 또는 desc 사용한다

ex)

DESCRIBE employee;

DESC employee;

테이블의 관리

add연산자 : 테이블에 새로운 칼럼을 추가한다.

alter table employee add (addr varchar2(50));

제약 조건 추가시

alter table employee add constraint employee_pk primary key (empno);

modify연산자 : 테이블의 칼럼을 수정하거나 not null 컬럼으로 변경할 수 있다.

alter table employee modify (salary number(10,2) not null);

drop 연산자 : 컬럼의 삭제

alter table employee drop column name;

컬럼 명 변경

alter table employee rename column salary to sal;

테이블명 변경

rename employee to employee2;

테이블의 삭제

drop table employee2;

테이블 생성시 옵션

-on delete cascade : 부모 테이블의 컬럼을 삭제하면 자식 테이블의 자식 데이터를 모두 삭제

INSERT문 : 테이블에 행을 삽입한다.

ex)

1. 전체 데이터를 삽입한다(전체 칼럼 명시할떄)

INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)

VALUES ( 8000, 'DENIS', 'SALESMAN', 7698, '99/01/22', 1700, 200, 30);

2. 전체 데이터를 명시할 떄는 칼럼명을 생략할 수 있다.

INSERT INTO emp

VALUES ( 8000, 'DENIS', 'SALESMAN', 7698, '99/01/22', 1700, 200, 30);

3.특정 컬럼의 값을 입력하지 않을 경우

INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)

VALUES ( 8000, 'DENIS', 'SALESMAN', 7698, '99/01/22', 1700, 30); -- 200의 값을 입력하지 않았다

4. 값이 입력되지 않는 칼럼을 제외하지 않았을 경우

INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)

VALUES ( 8000, 'DENIS', 'SALESMAN', 7698, '99/01/22', 1700, NULL, 30); -- 200의값자리에 NULL값 삽입

5.날짜의 삽입

INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)

VALUES ( 8000, 'DENIS', 'SALESMAN', 7698, TO_DATE('99/01/22','YY/MM/DD'),  1700, 200, 30);

UPDATE문 : 행 단위로 데이터를 갱신

ex)

사원 번호가 8000인 사원의 이름(ename), 급여(sal)를 각각 MARIA, 2500으로 변경

UPDATE emp SET ename='MARIA', SAL=2500 WHERE empn0 = 8000;

(주의사항)WHERE절을 명시하지 않으면 전체 행의 데이터를 변경하게 된다.

DELETE : 행을 삭제한다.

ex)

사원번호(empno)가 8000인 사원의 행을 삭제

DELETE FROM emp WHERE empno = 8000;

(주의사항)WHERE절을 명시하지 않으면 전체 행이 삭제된다.

집합 연산자

Union(합집합, 중복값 제거) : 두 테이블의 결합을 나타내며, 결합시키는 두 테이블의 중복되지 않은 값들을 반환한다.

ex)

SELECT deptno FROM emp

UNION

SELECT deptno FROM dept;

-union all : union과 같으나 두 테이블의 중복되는 값까지 반환한다.

-intersect: 두 행의 집합 중 공통된 행을 반환한다.

SELECT deptno FROM emp

intersect

SELECT deptno FROM dept;

서브쿼리(Subquery): 다른 하나의 SQL문장의 절에 nested된 select문장

단일행 서브쿼리: 오직 한개의 행(값)을 반환한다.

다중 행 서브쿼리: 하나 이상의 행을 반환하는 서브쿼리이다.

IN연산자의 사용

ex)

부서별(deptno)로 가장 급여를 적게 받는 사원과 동일한 급여를 받는 사원의

사원번호(empno), 사원이름(ename), 급여(sal)를 출력한다.

SELECT empno, ename, sal FROM emp

WHERE sal IN (SELECT MIN(sal) FROM emp GROUP BY deptno);

ANY연산자의 사용: 서브쿼리의 결과값 중 어느 하나의 값이라도 만족이되면 결과값을 반환

ex)

직업(job)이 SALEMAN인 사원보다 급여가 많은 사원의 이름(ename) 급여(sal)을 출력하시오.

SELECT ename, sal FROM emp WHERE sal > ANY(SELECT sal FROM emp WHERE jon='SALESMAN');

ALL연산자의 사용: 서브쿼리의 결과와 모든 값이 일치

ex)

부서번호가 20인 사원들보다 급여가 높은 사원의

사원번호(empno), 이름(ename), 급여(sal), 부서번호(deptno)를 출력하시오

SELECT empno, ename, sal, deptno FROM emp WHERE sal > ALL(SELECT sal FROM emp WHERE deptno=20);

다중열 서브쿼리 : 서브쿼리의 결과가 두개 이상의 칼럼으로 반환되어 메인 쿼리에 전달하는 쿼리

ex)

부서별(deptno)로 가장 급여(sal)를 적게 받는 사원의

번호(empno), 이름(ename), 급여(sal), 부서번호(deptno)를 출력하시오

SELECT empno, ename, sal, deptno FROM emp

WHERE (deptno, sal) IN (SELECT deptno, MIN(sal) FROM emp GROUP BY deptno);

분석함수 

RANK() : 순위를 표현할 때 사용하는 함수

RANK(조건값) WITHIN GROUP (ORDER BY 조건값 컬럼명 [ASC][DESC]);

특정 데이터의 순위 확인하기

(주의사항) RANK뒤에 나오는 데이터와 ORDER BY 뒤에 나오는 데이터는 같은 컬럼이어야 한다.

ex)

SELECT ename FROM emp GROUP BY ename;

SELECT RANK('SMITH') WITHIN GROUP ( ORDER BY ename) "RANK" FROM emp;

전체 순위보기

RANK() OVER (ORDER BY...)

OVER절 다음 순위를 만들려면 정렬(ORDER BY)는 필수이며

그룹을 나누어(PARTITION BY)순위를 만드는 경우는 선택사항이다.

JOIN : 둘 이상의 테이블을 연결하여 데이터를 검색하는 방법

           보통 둘 이상의 행들의 공통된 값 Primary Key 또는 Foreign Key값을 사용하여 조인한다.

Cartesian Product(카디션 곱)  : 검색하고자 했던 데이터 뿐만 아니라 조인에 사용된 

                                                   테이블들의 모든 데이터가 반환 되는 현상이다.

ex)

SELECT * FROM emp, dept; -- 조인 조건없이 잘못 조인된 경우이다

[ORACLE 전용]

동등 조인(Equal Join)

조건절 Equality Condition(=)에 의하여 조인이 이루어진다.

ex)

SELECT emp.name, dept.dname FROM emp, dept

WHERE emp.deptno = dept.deptno;

테이블에 알리아스 부여하기

ex)

SELECT e.name, d.dname FROM emp e, dept d

WHERE e.deptno = d.deptno;

컬럼명을 호출할 떄 테이블명 또는 테이블 일리아스를 생략

ex)

SELECT ename, e.deptno, dname FROM emp e, dept d

WHERE e.deptno = d.deptno;

추가적인 조건 명시하기

ex)

SELECT e.ename, d.dname FROM emp e, dept d

WHERE e.deptno = d.deptno AND e.sal BETWEEN 3000 AND 4000

비동등 조인(Non Equal Join)

테이블의 어떤 컬럼도 join할 테이블의 컬럼과 일치하지 않을 때 사용하고

조인 조건은 동등(=)이외의 연산자를 갖음(between and, is null, is not null, not in)

외부조인(Outer Join)

동등 조인을 할 떄 누락된 데이터가 있을 경우 누락된 데이터를 표시할 때 외부조인만 사용

누락된 행의 반대 테이블의 조인을 표시하는 칼럼에 (+)표시

SELECT a.ename 사원이름, m.ename 관리자이름

FROM emp a, emp m

WHERE a.mgr = e.empno(+);

ORACLE전용 끝

[표준SQL]

내부조인(Inner Join)

ex)

SELECT emp ename, dept deptno

FROM emp INNER JOIN dept

ON emp.deptno = dept.deptno;

SELECT emp ename, dept deptno

FROM emp JOIN dept

ON emp.deptno = dept.deptno;

부가적인 조건의 사용 (WHERE 사용)

SELECT e.ename, d.dname FROM emp e JOIN dept d

ON e.deptno = d.deptno

WHERE e.ename = 'ALLEN';

만약 조인 조건에 사용된 컬럼의 이름이 같다면 다음과 같이 USING절을 사용하여 조인 조건을 정의할 수 있다.

(주의)USING에 사용된 컬럼은 테이블명 또는 테이블 알리아스를 붙이지 않는다.

ex)

SELECT e.ename, deptno --d.deptno라고 하면 오류가 발생한다.

FROM emp e JOIN dept d 

USING(deptno);

SELECT ename, deptno --d.deptno라고 하면 오류가 발생한다.

FROM emp JOIN dept 

USING(deptno);

외부조인(Outer Join)

누락된 행의 방향을 표시한다.

ex)

SELECT e.ename name, m.ename manager_name

FROM emp e LEFT OUTER JOIN emp m

ON e.mgr = m.empno;

변환함수
TO_CHAR : 숫자->문자,날짜->문자
TO_NUMBER : 문자->숫자
TO_DATE : 문자->날짜

TO_CHAR
날짜 -> 문자로 변환
SELECT TO_CHAR(날짜,포맷문자) FROM dual;


숫자->문자 변환
실제 자리수와 일치
SELECT TO_CHAR(1234,9999) FROM dual;
SELECT TO_CHAR(1234,'9999') FROM dual;
SELECT TO_CHAR(1234,'0000') FROM dual;
##으로 출력 오류 발생
SELECT TO_CHAR(1234,0000) FROM dual;

자리수가 모자람->####
SELECT TO_CHAR(1234,999) FROM dual;
SELECT TO_CHAR(1234,'999') FROM dual;
SELECT TO_CHAR(1234,'000') FROM dual;

실제 자리수 보다 많은 자리수 지정
SELECT TO_CHAR(1234,99999) FROM dual;  -- 공백1234
SELECT TO_CHAR(1234,'99999') FROM dual; -- 공백1234
SELECT TO_CHAR(1234,'00000') FROM dual; -- 01234

소수점 자리
SELECT TO_CHAR(1234,9999.99) FROM dual; -- 1234.00
SELECT TO_CHAR(1234,'9999.99') FROM dual; -- 1234.00
SELECT TO_CHAR(1234,'0000.00') FROM dual; -- 1234.00

반올림해서 소수점 둘째자리까지 표시
SELECT TO_CHAR(25.897,'99.99') FROM dual; -- 25.90
인상된 급여를 소수점 첫째자리까지 표시
SELECT TO_CHAR(sal*1.15,'9,999.9') FROM emp;

통화표시
$
SELECT TO_CHAR(1234,'$0000') FROM dual; --$1234
지역통화 표시
SELECT TO_CHAR(1234,'L0000') FROM dual; -- \(원화)1234

TO_DATE
문자 -> 날짜
SELECT TO_DATE('22-10-31','YYYY-MM-DD') FROM dual;

TO_NUMBER
문자 -> 숫자
SELECT TO_NUMBER('100','999') FROM dual;
SELECT TO_NUMBER('100') FROM dual; --포맷 형식 생략 가능

일반 함수
NVL(value1,value2) : value1이 null이면 value2를 씀.    value1과 value2의 자료형이 일치
                     
NVL2(value1,value2,value3) : value1이 null인지 평가. null이면 value3 , null 아니면 value2. 자료형이 일치하지 않아도 됨

NULLIF(value1,value2) : 두개의 값이 일치하면 NULL, 두개의 값이 일치하지 않으면 value1

ex)
SELECT NULLIF(LENGTH(ename),LENGTH(job)) "NULLIF" FROM emp;                        

COALESCE(value1,value2,value3,....) : null값이 아닌 값을 사용
                                      (자료형 일치)
                                      

CASE 컬럼 WHEN 비교값 THEN 결과값
         WHEN        THEN
         WHEN        THEN
         (ELSE 결과값)
END



DECODE : 오라클 전용, = 비교만 가능
         DECODE(컬럼,비교값,반환값,
                    비교값,반환값,
                    비교값,반환값,
                    반환값)
FROM dual;
                    
ex)
SELECT ename,sal,job,
       DECODE(TRUNC(sal/1000),5,'A',
                              4,'A',
                              3,'B',
                              2,'C',
                              1,'D',
                              'F') "Grade"
FROM emp ORDER BY "Grade",sal DESC;                              

그룹함수
AVG() : NULL을 제외한 모든 값들의 평균을 반환, NULL값은 평균 계산에서 무시됨
SELECT ROUND(AVG(sal)) FROM dual;

COUNT() : NULL을 제외한 값을 가진 모든 레코드의 수를 반환.  COUNT(*) 형식을 사용하면 NULL도 계산에 포함
SELECT COUNT(*) FROM dual;

MAX() : 레코드 내에 있는 여러 값 중 가장 큰 값을 반환
SELECT MAX(sal) FROM dual;
SELECT MAX(ename) FROM emp;
SELECT MAX(hiredate) FROM emp;

MIN() : 레코드 내에 있는 여러 값 중 가장 작은 값을 반환
SELECT MIN(sal) FROM dual;

SUM() : 레코드들이 포함하고 있는 모든 값을 더하여 반환
SELECT SUM(sal) FROM 여미;

null값까지 카운트하고 싶을 경우
SELECT COUNT(NVL(sal,0)) FROM dual;

GROUP BY
SELECT절에 집합함수 적용시 개별 컬럼을 지정할 수 없다.
개별 컬럼을 지정할 경우에는 반드시 GROUP BY 절에 지정된 컬럼만 가능

ex)

SELECT deptno, MAX(sal) FROM emp GROUP BY deptno;
SELECT MAX(sal),MIN(sal),deptno FROM emp 
GROUP BY deptno ORDER BY deptno;


HAVING절 : 그룹 처리 결과를 제한하고자 할 때 HAVING절 사용
(WHERE절에는 집합함수를 사용해서 조건 체크할 수 없고 HAVING절에서 가능)
알리아스 사용X

[오류발생]
SELECT deptno,ROUND(AVG(sal)) FROM emp
WHERE ROUND(AVG(sal))>=2000
GROUP BY deptno;

[정상구문]
SELECT deptno,ROUND(AVG(sal)) FROM emp
GROUP BY deptno HAVING ROUND(AVG(sal))>=2000;

숫자 함수

CEIL(실수) : 올림 처리한 정수값을 반환

ex)

SELECT CEIL(1.4) FROM dual; (결과값 = 2)

FLOOR(실수) : 버림(절삭) 처리한 정수값을 반환

ex)
SELECT FLOOR(1.7) FROM dual; (결과값 = 1)

ROUND(대상숫자, 지정 자릿수) : 반올림 처리한 정수 값을 반환

ex)

'''

SELECT ROUND(3.621592, 2) FROM dual; (결과값 = 3.62)

지정 자릿수 설정 안할시 정수값으로 반환

SELECT ROUND(3.621592) FROM dual; (결과값 = 4)

'''

TRUNC(대상숫자, 지정자릿수) ; 절삭(지정자릿수 설정시 실수 형태 유지)

ex)

'''
SELECT TRUNC(45.926, 2) FROM dual; -- (결과값 = 45.92)

SELECT TRUNC(45.926) FROM dual; -- (결과값 = 45(정수값))

'''

MOD(대상숫자, 나눌숫자) : 나머지값

ex)

SELECT MOD(17, 2) FROM dual; --(결과값 = 1)

날짜 

SYSDATE : ORACLE서버의 현재 날씨와 시간을 반환
SELECT SYSDATE FROM dual;

날짜 연산

ex)

'''
SELECT ename, SYSDATE - hiredate FROM emp; --( hiredate 부터 몇일이 흘렀는지 계산)
SELECT ename, ROUND((SYSDATE - hiredate)/7) AS WEEKS FROM emp; --(  hiredate 부터 몇주가 흘렀는지 계산)
'''

날짜 함수

MONTHS_BETWEEN : 두 날짜 간의 월 수

ex)

'''
SELECT MONTHS_BETWEEN('2022/10/23', '2010/10/23') FROM dual; --(결과값 : 144)
SELECT MONTHS_BETWEEN( '2010/10/23', '2022/10/23') FROM dual; --(결과값 : -144)(미래의 날짜가 앞으로 와야함)

'''

ADD_MONTHS : 특정 날짜의 월에 정수를 더한 다음 해당 날짜를 반환하는 함수

ex)
SELECT ADD_MONTHS('2022/01/01', 8) FROM dual; --(결과값 : 22/09/01)

NEXT_DAY : 지정된 요일의 다음날짜

ex)
SELECT NEXT_DAY('2022/10/04', '월요일') FROM dual;--(결과값 22/10/10) 

(2022/10/04는 화요일) (22/10/10일은 월요일)

1(일요일) ~ 7(토요일)
SELECT NEXT_DAY('2022/10/04', 2) FROM dual; --(결과값 22/10/10)

논리연산자 (AND, OR, NOT)을 사용하여 조건정의

AND 연산자의 사용 : 구성 요소 조건이 모두 TRUE이면 TRUE 반환

ex)

'''
SELECT empno, ename, job, sal FROM emp
WHERE sal>=2000 AND job LIKE '%MAN%';
'''

OR 연산자의 사용: 조건중 하나가 TRUE면 TRUE를 반환

ex)

'''
SELECT empno, ename, job, sal FROM emp
WHERE sal>= 2000 OR job LIKE '%MAN%';
'''

NOT 연산자의 사용 : 부정

ex)
'''
SELECT ename, job FROM emp
WHERE job NOT IN ('CLERK', 'SALESMAN');
'''

ORDER BY절 : 정렬

알리아스 사용가능

ex)

'''
SELECT * FROM emp ORDER BY sal; -- (default : 오름차순)
SELECT * FROM emp ORDER BY sal ASC; --(오름차순)
SELECT * FROM emp ORDER BY sal DESC; -- 내림차순

기준이 되는 컬럼에 중복값이 있으면 추가 컬럼을 이용해서 2차정렬 가능하다.
SELECT * FROM emp ORDER BY sal DESC, ename DESC; -- 내림차순

"

열 알리아스를 기준으로 정렬

ex)
SELECT empno, ename, sal*12 annsal FROM emp ORDER BY annsal; -- 오름차순

열의 숫자위치를 사용하여 정렬

ex)

'''
SELECT ename, job, deptno, hiredate FROM emp ORDER BY 1; -- ename을 기준으로 정렬
SELECT ename, job, deptno, hiredate FROM emp ORDER BY 2; -- job을 기준으로 정렬
SELECT * FROM emp ORDER BY 2; --테이블의 생성된 순서대로 열숫자 부여

'''

-NULLS FIRST 또는 NULLS LAST 키워드 사용하여 반환된 행 중 NULL값을 포함하는 행이
정렬 순서상 맨 처음에 나타나거나 마지막에 나타나도록 제어

ex)
'''
SELECT * FROM emp ORDER by comm ASC NULLS FIRST; --널을 최상위로 올리고 그다음 오름차순 정렬
SELECT * FROM emp ORDER by comm DESC NULLS LAST; -- 널을 최하위로 내리고 그다음 내림차순 정렬
'''

함수

문자함수

대소문자 조작 함수 - LOWER, UPPER, INITCAP

ex)
'''
SELECT LOWER('HELLO') FROM dual; -- 대문자 --> 소문자
SELECT UPPER('hello') FROM dual; --소문자 --> 대문자
SELECT INITCAP('hello wORLD') FROM dual; -- 첫글자는 대문자 나머지는 소문자
'''

문자 조작함수

ex)

'''
CONCAT(문자열1, 문자열2) : 문자열1과 문자열2를 연결하여 하나의 문자열로 변환
SELECT CONCAT('Hello', 'World') FROM dual; (결과값 = HelloWorld)
SELECT CONCAT(ename, job) FROM emp;

'''

SUBSTR(대상문자열, 인덱스) : 대상문자열에서 지정한 인덱스부터 문자열 추출

주의사항: 인덱스가 1부터시작
ex)

'''
SELECT SUBSTR('Hello World', 3) FROM dual; -- 결과값 = Hel
SELECT SUBSTR('Hello World',3, 3) FROM dual; -- 인덱스 3부터 3개만 추출 (결과값 = llo)
SELECT SUBSTR('Hello World', -3) FROM dual; -- 뒤에서 3번쨰부터 끝까지 추출 (결과값 = rld)
SELECT SUBSTR('Hello World', -3, 2) FROM dual; -- 뒤에서 3번째부터 2개만 추출 (결과값 = rl)

'''

ENGTH(대상문자열) : 문자열의 개수

ex)

SELECT LENGTH('Hello World') FROM dual;(결과값 = 11)

INSTR(대상 문자열, 검색문자) : 검색문자의 위치값 검색

ex)

'''

SELECT INSTR('Hello World', 'e') FROM dual;(결과값 = 2)

검색문자가 없을 경우 0
SELECT INSTR('Hello World', 'E') FROM dual; -- 결과값 = 0

SELECT INSTR('Hello World', 'o') FROM dual; -- 중복시 앞에 문자 위치값 검색
SELECT INSTR('Hello World', 'o', 6) FROM dual; -- (대상문자열, 검색문자, 검색인덱스:해당위치부터 검색)
SELECT INSTR('Hello World', 'o', 1,2) FROM dual; -- {대상 문자열, 검색문자, 검색인덱스, 반복횟수)
'''

LPAD(대상문자열, 총길이, 문자): 지정한 길이의 문자열을 출력하는데 공백은 왼쪽에 지정한 문자로 채움

ex)
SELECT LPAD('Hello', 10, '*') FROM dual; --결과값 = Hello*****

RPAD(대상문자열, 총길이, 문자): 지정한 길이에 문자열을 출력하는데 공백은 오른쪽에 지정한 문자로 채움

ex)
SELECT RPAD('Hello', 10, '*') FROM dual; -- 결과값 = *****Hello

TRIM : 문자열에서 공백이나 특정 문자를 제거한 다음 값을 반환

방향 : leading (왼쪽), trailing (오른쪽), both(default)(양쪽)
ex)

'''
SELECT TRIM(LEADING 'h' FROM 'habchh') FROM dual; --결과 abchh
SELECT TRIM(BOTH 'h' FROM 'hahchh') FROM dual; --결과 ahc
SELECT TRIM(TRAILING 'h' FROM 'hahchh') FROM dual; --결과 hahc

'''

REPLACE(대상문자열, OLD, NEW) : 대상문자열에서 OLD문자를 NEW문자로 대체

ex)
SELECT REPLACE('010.1234.5678', '.', '-') FROM dual; -- 결과값 = 010-1234-5678

함수의 중첩
ex)
SELECT ename, LOWER(SUBSTR(ename, 1, 3)) FROM emp; -- ename출력|ename소문자로 3번째문자까지 출력

SELECT문

데이터베이스로 부터 저장되어 있는 데이터를 검색시 사용

테이블에 저장되어 있는 전체 데이터를 행 단위로 검색

ex)

'''

SELECT * FROM table;

'''

dual : 함수 또는 계산의 결과를 볼 때 사용할 수 있는 공용(Public)테이블

ex)

'''

SELECT SYSDATE FROM dual;
SELECT 7 + 10 FROM dual;
SELECT ASCII('A') FROM dual;
SELECT ASCII(0) FROM dual;

'''

산술식: 산술 연산자(+, -,*, /)를 이용하여 숫자 및 날짜 데이터로 표현식을 작성

null값을 포함하는 산술식은 null로 계산

ex)

'''

SELECT ename, sal, sal + 300 FROM table;

SELECT ename, sal, (sal + 300)*3 FROM TABLE

'''

알리아스: 열의 이름을 바꾸는것으로 열 이름과 알리아스 사이에 선택 사항인 as키워드 사용가능

알리아스에 큰따옴표를 사용하는 경우는

-대소문자 구별시

-공백포함시

-_,#등 특수문자 사용시(_는 ""없이 중간에 올 수 있으나 맨앞에 오면 오류 발생)

-숫자로 시작할 경우

ex)

'''

SELECT sal*12 ASal FROM table;
SELECT sal*12 AS ASal FROM table;
SELECT sal*12 "Annual Salary" FROM table;

'''

연결 연산자: 열이나 문자열을 다른 열에 연결하며 두개의 세로선( || )을 사용한다.

ex)

'''

SELECT ename || ' has $' || sal FROM table;

'''

distinct : 중복행 삭제 역할을 한다.

ex)

'''

SELECT DISTINCT deptno FROM table;
SELECT DISTINCT(deptno) FROM table;

'''

WHERE절

비교연산자 또는 논리연산자를 이용해서 검색하며 알리아스는 사용이 불가하다.

ex)

'''

숫자
SELECT * FROM emp WHERE deptno=10;
문자열
SELECT * FROM emp WHERE ename='PRACT';
날짜
SELECT * FROM emp WHERE hiredate='21/10/28';

'''

between ~ and ~ : 두 값 사이를 나타낸다. (지정한 값 포함)

ex)

'''

SELECT * FROM emp WHERE sal BETWEEN 1000 AND 1500;

SELECT * FROM emp WHERE ename NOT BETWEEN 'KING' AND 'SMITH';

'''

In: 값 목록 중의 값과 일치

ex)

'''

SELECT * FROM emp WHERE sal IN (1300,2450,3000);
SELECT * FROM emp WHERE sal NOT IN (1300,2450,3000);
SELECT * FROM emp WHERE ename IN ('ALLEN','FORD');

'''

LIKE : LIKE 연산자를 사용하여 패턴 일치한 정보를 검색한다.
%는 0개 이상의 문자를 나타낸다
_은 한 문자를 나타낸다.

ex)

'''

SELECT * FROM emp WHERE ename LIKE '%S%';--(S가 처음,중간,끝에 오는 이름)
SELECT * FROM emp WHERE ename NOT LIKE '%S%';
SELECT * FROM emp WHERE hiredate LIKE '%22';
SELECT * FROM emp WHERE ename LIKE 'FOR_';--(FOR 다음에 꼭 한 글자)

'''

NULL 조건 사용

ex)

'''
SELECT ename,job,comm FROM emp WHERE comm IS NULL; --(comm=null 불가)
SELECT ename,job,comm FROM emp WHERE comm IS NOT NULL;--(com!=null 불가)

'''