Oracle

솨니·2023년 10월 8일

db oracle

DB

목록 보기

1/1

Data

데이터 : 자료, 정보

자료 : 원석 → 필요에 의해 수집 했지만, 특정 목적을 위해 평가하거나 정제하지 않은 값이나 사실 또는 자료 자체
정보 : 보석 → 수집한 데이터를 목적을 위해 분석하거나 가공하여 가치를 부여하거나 새로운 의미를 이끌어 낼 수 있는 결과

⇒ 잘 수집된 데이터를 분석하고 가공하면 새로운 가치를 만들어 낼 수 있는 정보를 얻을 수 있다.

효율적인 데이터 관리 조건

정확한 분석과 비용과 시간을 필요로 한다. 효율적으로 수집, 통합, 체계적으로 관리, 분석하기 위해 조건이 필요하다.

데이터를 통합하여 관리
일관된 방법으로 관리
데이터 누락 및 중복 제거
여러 사용자(응용프로그램 포함)가 공동으로 실시간 사용 가능

DB

💡 조건을 만족하며 목적을 위해 여러 사람이 공유하여 사용 가능하며, 효율적인 관리와 검색을 위해 구조화된 데이터의 집합. 파일로 존재.

파일 시스템 방식 : 중복되는 정보들이 흩어져 있다면 문제가 발생할 수 있다. → 각 응용 프로그램의 일련된 데이터를 한곳에 모아 관리하면 응용 프로그램 별로 데이터를 직접 관리할 때 발생할 수 있는 데이터의 오류, 누락, 중복 등의 문제를 해결할 수 있다.

시간의 지남에 따라 데이터는 거대해지고 복잡해진다. 각각의 응용 프로그램을 직접 관리한다면 비효율적이게 될 것이다.

⇒ 서비스의 역할분담 : 서비스 요청을 받는 영역, 서비스 처리에 필요한 데이터를 다루는 영역, 처리한 데이터를 제공하는 영역으로 나누면 효율을 높일 수 있다.

DBMS (database management system)

효율적으로 데이터를 관리하여 서비스 제공의 효율을 높이기 위한 관리 시스템 소프트웨어

공유
보안

위의 요소는 DBMS를 사용해야하는 주된 이유이다.

Data Model

컴퓨터에 데이터를 저장하는 방식

계층형 : Tree구조 → 부모, 자식 관계. 1:N의 일대다 관계의 데이터구조. 부품관리 시스템에 사용된다 → 수직 관계에 적합하지만, 어떠한 경우에도 형제 관계를 표현할 수 없다. 실제 현실의 데이터는 구조가 매우 복잡하고 방대하다.
네트워크형 : graph구조 → 부모, 자식 관계에서 자식 개체가 여러 부모 개체를 가질 수 있음. 데이터가 늘어남에 따라 다루기가 더 어려워지고 느려진다.
객체 지향형 : OOP처럼 데이터를 독립된 객체로 구성하고 관리. → 상속, 오버라이딩 가능
관계형 : 현대에 가장 많이 사용되는 모델. 데이터 간 관계에 초점을 둠. 각 데이터의 독립 특성만을 규정하여 데이터 묶음을 나누어, 중복이 발생할 수 있는 데이터는 별개의 relation으로 정의한 후 각 데이터를 연결함
- 데이터를 나누는 기준(핵심 구성 요소)
  - 개체 : DB에서 데이터화 하려는 사물, 개념의 정보 단위. RDBMS의 table 개념과 대응됨
  - 속성 : 개체를 구성하는 데이터의 가장 작은 논리적 단위. 데이터의 종류, 특성, 상태 등을 정의. RDMBS의 column 개념과 대응됨
  - 관계 : 개체 간의 또는 속성 간의 연관성을 나타냄. RDBMS에서 table 간의 관계를 foreign key 등으로 구현하여 사용

SQL (structure query language)

RDBMS에서 데이터를 다루고 관리하는 데 사용하는 언어. → 물어보고 결과를 얻는다 : 데이터 추출

RDBMS의 구성요소

객체 관계형 데이터 모델 매니지먼트 시스템

테이블

2차원 표 형태로 데이터를 저장하고 관리함

table - relation
row - tuple || record
column - attribute || field

key : 수 많은 데이터를 구별하는 유일한 값.

테이블을 구성하는 여러 열 중에서 특별한 의미를 지닌 하나 이상의 열의 조합을 의미

종류별로 데이터를 구별하거나 테이블 간의 연관 관계를 표현할 때 키로 지정한 열을 사용

키
- 기본키(primary key) : 한 테이블 내에서 중복되지 않는 값만 가질 수 있는 키 ⇒ 하나 이상의 열의 조합으로 만들 수 있다.
  - 속성
    1. 테이블에 저장된 행을 식별할 수 있는 유일한(중복되지 않는) 값이어야 함
    2. 중복된 값은 없어야 함
    3. NULL 값을 가질 수 없음
- 대체키(alternate key) : 후보키에 속해 있으며, 기본키로 지정되지 않은 열. → 기본키가 될 수 있는 모든 키.
- 외래키(foreign key) : 특정 테이블에 포함되어 있으며 다른 테이블의 기본키로 지정된 키. 중복 데이터의 저장 문제를 최소화 할 수 있다. 엑셀처럼 열을 병합하는 것이 불가능 하지만, 외래키를 통해 유사한 효과를 얻을 수 있다.
- 외래키를 통해 너무 엄격하게 정의 한다면 프로그램 제작과 테스트 진행에 걸림돌이 될 수도 있음
- 복합키(composite key) : 여러 열을 조합하여 기본키 역할을 할 수 있게 만든 키. 하나의 열만으로 행을 식별하는 것이 불가능하기에 두 개 이상의 열 값을 함께 조합하여 사용한다.

Oracle DB

버전

대부분 기업이 2007년에 나온 11g 버전을 사용중이며, DB를 한 번 구축하면 버전 업그레이드가 쉽지 않다. → 서비스의 바탕이 되는 DB를 최신 버전으로 바꾸면 데이터 관리에 문제가 생길 수 있으며, 저전체 서비스의 마비도 초래할 수 있다. 문제가 없다면 기존 DB버전을 그대로 유지 한다.

자료형(data type)

VARCHAR2(길이) : $4*10^3$ byte 만큼의 가변 길이 문자열 데이터 저장 가능.
NUMBER(전체 자릿수, 소수점 이하 자릿수) : ±38 자릿수의 숫자 저장 가능. NUMBER(p, s)
DATE : 날짜 형식 저장. 세기, 연, 월, 일, 시, 분, 초 저장 가능
CHAR(길이) : $4*10^3$ byte 만큼의 고정 길이 문자열 데이터 저장 가능
NVARCHAR2(길이) : $4*10^3$ byte 만큼의 가변 길이 국가별 문자 세트 데이터 저장 가능
BLOB : 최대 크기 4GB의 대용량 이진 데이터 저장 가능
CLOB : 최대 크기 4GB의 대용량 텍스트 데이터 저장 가
BFILE : 최대 크기 4GB의 대용량 이진 데이터 파일 저장 가능

객체

Oracle DB 내에서 데이터를 저장하고 관리하기 위한 논리 구조를 가진 구성 요소

객체	설명
table	데이터를 저장하는 장소
index	테이블의 검색 효율을 높이기 위해 사용
view	하나 이상의 선별된 데이터를 논리적으로 연결하여 하나의 테이블처럼 사용하게 가능
sequence	일련 번호 생성
synonym	오라클 객체의 별칭을 지정
procedure	프로그래밍 연산 및 기능 수행이 가능(반환 값 없음)
function	프로그래밍 연산 및 기능 수행이 가능(반환 값 있음)
package	관련 있는 프로시저와 함수를 보관
trigger	데이터 관련 작업의 연결 및 방지 관련 기능 제공

SQL

SQL 형식
- DRL : Select / 4~9
- DML : Insert, Update, Date / 10
- TCL : Commit, Rollback, Savepoint / 11
  
  → 개발자 영역
- DDL : Create, Alter, Drop / 12~14
- DCL : Grant, Revoke / 15
  
  → 개발자 영역x, 설계 관리 영역.

대소문자를 구분하지 않는다.

기본 설정

오라클 압축파일 풀고 설치

내pc → 관리 → 서비스 및 응용 프로그램 → 서비스 → oracle service xe 상태: 실행중, 시작 유형: 자동

oracle xetnslistener 실행중 자동

cmd → sqlplus 입력

계정이 없기 때문에 기본 관리자 계정으로 로그인

sys : 주 관리자
system : 보조 관리자

비밀번호는 설치할 때 설정한 비밀번호

[ 시스템 관리자 작업 ]

1. 오라클 사용자 계정 scott 생성

create user scott

identified by tiger

default tablespace users;

2. 생성한 scott 계정에게 필요한 권한 부여

grant connect, resource, create view to scott;

================================================================

SQL 학습을 위한 Script, Demobld.sql 생성(명령창 밖에서 수행)

3. demobld.sql 파일을 oracle 설치 디렉토리(c드라이브) 에서 oraclexe 폴더에 저장

4. sql 파일에서 DROP CREATE 사이에 2줄 추가해서 저장 

DROP TABLE DUMMY; 

ALTER SESSION SET NLS_DATE_FORMAT = ‘DD-MON-YYYY’;

ALTER SESSION SET NLS_LANGUAGE = ‘AMERICAN’

CREATE TABLE EMP

================================================================

[ scott 계정 작업 ]

명령창에 conn scott 입력 → 비밀번호 tiger

5. Demobld.sql 스크립트 실행

@ C:\oraclexe/demobld.sql → 종료되고 명령창으로 탈출함. → scott계정으로 로그인 하기 위해

명령창에 sqlplus 입력후

아이디 : scott 비밀번호 : tiger

6. 샘플 테이블 및 데이터 확인

select tname from tab;

→ 현재 로그인한 사용자 소유의 table 확인가능

dept table의 모든 정보를 조회할 때 : select * from dept;

dept(부서 department) loc(location 지역)

emp(사원테이블)

select * from emp; 

⇒ 계단 현상 나오면 set linesize 200 으로 조절 가능

페이징 현상 → 14줄이 기본임 → set pagesize 50 

mgr ( 관리자의 사원 번호 )

`만약 no rows selected 가 뜨면 sql에 2줄 추가할 때 오타가 있거나 탈출해서 @부분부터 진행하면 된다.`

→ C:\oraclexe\app\oracle\product\11.2.0\server\sqlplus\admin 에서 glogin.sql 파일에서 아래 내용을 추가해준다

```sql
SET LINESIZE 200
SET PAGESIZE 50

// 하면 매 번 도스창에서 실행시킬 필요 없다.

오라클 기본 날짜 형식

RR/MM/DD → ex) xx81/02/22

RR	xx
0~49	20
50~99	19

comm(세일즈맨)

Select

select 명령의 특성

selection : 행에 대한 제한 ⇒ where

select * # *은 모든 칼럼을 의미함
from emp
where deptno = 30;

projection : 열에 대한 제한 ⇒ select

select ename, sal, hiredate
from emp
where deptno = 20;

join : 두 개 이상의 테이블을 사용하여 데이터 조회 ⇒ from

select ename, job, sal, dname, loc
from emp join dept on emp.deptno = dept.deptno
where job = 'CLERK';

select 문장의 구성

생략 불가능

SELECT
FROM

생략 가능

WHERE
GROUP BY
HAVING
ORDER BY

select 절

SELECT [ALL / DISTINCT] { * | COLUMN_LIST }

# ALL : default 값
# DISTINCT : 중복된 행값 제거
# [ ] : optional (생략 가능)

select * from dept;
select deptno, loc from dept;
select all job from emp;
select distinct job from emp;
select all job, deptno from emp;

# from 은 절마다 개행하는게 기본
=> 
select all job, deptno 
from emp;

표현식

열과 연산식

select ename, sal, sal * 12
from emp;

# sal * 12 : 연산식

select ename, sal, comm, sal * 12 + comm
from emp;

# sal * 12 + comm : 연봉 계산식
# 연산식 중에 null 값이 들어간 사원들은 (sal * 12 + comm)값이 출력되지 않는다.
# null 값을 영향을 주지 않는 값으로 바꿔준다. -> null value 함수 : nvl

select comm, nvl(comm, 0)
from emp;

# 결과 값
=>
select ename, sal, comm, nvl(comm, 0), sal * 12 + nvl(comm, 0)
from emp;

별칭

select ename 사원이름, sal 급여, comm 상여금, sal * 12 + nvl(comm, 0) 연봉
from emp;

select ename 사원이름, job 업무, sal 급여, comm 상여금, sal * 12 + comm 연봉
from emp;

ANSI/ISO SQL 표준방식 (as)

select ename as 사원이름, job as 업무, sal as 급여, comm as 상여금,
sal * 12 + comm as 연봉
from emp;

select ename as Ename, job as Job, sal as Salary, comm as Commition,
sal * 12 + comm as Annsal
from emp;
# 대문자로 보여진다.

select ename as "Ename", job as "Job", sal as "Salary", comm as "Commition",
sal * 12 + comm as "Annual Salary"
from emp;
# 공백 구분

연결 연산자 ||

select ename, job, ename || job
from emp;

select ename || job, ename || sal, sal || hiredate
from emp;

select ename || ' is a ' || job as "사원과 업무"
from emp;

order by 절

ORDER BY {COLUMN_NAME | COLUMN_LIST} [ASC | DESC] # ASC(오름차순) 가 DEFAULT 값

#오라클에서는 null 값을 제일 큰 값으로 정렬한다.

select ename, job, sal, hiredate
from emp
order by sal;

select ename, job, sal, deptno
from emp
order by deptno;

# 여러 칼럼 이용 가능. 중복 행이 발생했을 때 사용.
select ename, job, sal, deptno
from emp
order by deptno, sal;

# 1차 정렬은 오름차순, 2차 정렬은 내림차순
select ename, job, sal, deptno
from emp
order by deptno, sal, desc;
# 1차 정렬에 중복이 없다면 2차 정렬은 의미가 없다.
# 여러 개의 데이터라면 각각의 컬럼 별로 개별적으로 정렬해줘야 한다.

# 계산식도 사용할 수 있다.
select ename, job, sal, comm, sal * 12 + nvl(comm, 0) ann_sal
from emp;

select ename, job, sal, comm, sal * 12 + nvl(comm, 0) ann_sal
from emp
order by sal * 12 + nvl(comm, 0);

# alias 사용 가능
select ename, job, sal, comm, sal * 12 + nvl(comm, 0) ann_sal
from emp
order by ann_sal;

2차 정렬은 성능을 떨어뜨리는 요소가 되기도 한다. 되도록이면 안쓰는 것이 좋은 방법

WHERE

select *
from emp
where ename = 'scott';

SELECT *
FRMO EMP
WHERE ENAME = 'SCOTT';

# 후자만 출려된다 이유는 ?
# 대소문자 구분이 아니라 DB에 저장되어 있는 scott 사원은 없다. SCOTT 사원이 있는 것이다.
# 값을 불러올 땐 정확하게 불러와야 한다.(대소문자 및 날짜 형식 등)
# table 명 column 명은 소문자, 키워드는 대문자를 권장한다.
# 각 절마다 개행을 권장한다.

SELECT *
FRMO emp
WHERE ename = 'SCOTT';

select *
from emp
where job = 'MANAGER';

select *
from emp
where sal > 3000;

select *
from emp
where hiredate >= '82/01/01'; 

# 날짜 값을 입력할 때 4자리를 써주는 것을 더욱 권장함.
select *
from emp
where hiredate >= '1982/01/01';

select *
from emp
where hiredate >= '2082/01/01'; # 가져올 수 없음

비교연산자

등가 비교 연산자

연산자	사용법	의미
=	A = B	A 값이 B 값과 같을 경우 true,
다를 경우 false 반환
!=	A != B	A 값이 B 값과 다를 경우 true,
같을 경우 false 반환
<>	A <> B	A 값이 B 값과 다를 경우 true,
같을 경우 false 반환
^=	A ^= B	A 값이 B 값과 다를 경우 true,
같을 경우 false 반환

# 1982년 전에 입사한 사람
select *
from emp;
where hiredate < '1982/01/01';
# 날짜는 0시0분0초 기준이므로 1981/12/31 보다 작거나 같다로 하면 안된다.

# 부서 번호가 30이 아닌 것
select *
from emp;
where deptno != 30;

# alias을 사용할 수 없다.
select ename, job, sal, comm, sal * 12 + nvl(comm, 0) ann_sal
from emp
where ann_sal >= 30000;

# 계산식은 사용할 수 있다.
select ename, job, sal, comm, sal * 12 + nvl(comm, 0) ann_sal
from emp
where sal * 12 + nvl(comm, 0) >= 30000;

논리 연산자
- AND
  
  피연산자 true false
  true true false
  false false false
- OR
  
  피연산자 true false
  true true true
  false true false

피연산자	true	false
true	true	false
false	false	false

피연산자	true	false
true	true	true
false	true	false

# and가 더 우선순위가 높기 때문에 or와 쓸 때 주의해야 함
select *
from emp
where job = 'MANAGER' or job = 'CLERK' and sal > 2000;

# 위 와 전혀 다른 의미이다.
select *
from emp
where (job = 'MANAGER' or job = 'CLERK') and sal > 2000;

=> 출력 값은 같지만 의미가 전혀 다르기 때문에 같은 query 라고 보면 안된다.
DB에 저장된 값에 적절하게 사용하지 못한 조건 값이기 때문에 같게 나온 것이다.

SQL 연산자

IN (목록 연산자)

select *
from emp;
where job = 'PRESIDENT' or job = 'ANALYST' or job = 'CLERK';

# or 연산자 대신 쓰는 걸 권장한다.
# where 절이 길어지고 and와 같이 쓰일 때 우선순위 이슈가 있다.
select *
from emp
where job IN ('PRESIDENT', 'ANALYST', 'CLERK');

==================================================================================

# 업무가 'PRESIDENT', 'ANALYST', 'CLERK' 인 사원 중 81년도에 입사한 사원 정보를
# 출력하는 쿼리를 작성하세요.

select *
from emp
where job IN ('PRESIDENT', 'ANALYST', 'CLERK') and (hiredate >= '1981/01/01' and hiredate < '1982/01/01');

set linesize 50
desc emp

NUMBER(P[ , S]) P : ~38 VARCHAR2(size) : 가변 길이 문자 데이터 타입, size : byte 수. ~8000byte. DATE

BETWEEN A AND B (범위 연산자)

select *
from emp
where sal >= 1300 and sal <= 3000;

select *
from emp
where sal between 1300 and 3000;

select *
from emp
where ename between 'C' and 'G';

LIKE (검색 연산자)
- _ : 하나의 문자를 대체 ‘_AMES’
- % : 없거나 하나이거나 여러 문자를 대체 ‘%ORACLE%
```
select *
from emp;

# 사원 이름 중 두번째 글자가 I인 데이터
select *
from emp
where ename LIKE '_I%'; 

select *
from emp
where hiredate LIKE '1982%';

insert into emp(empno, ename, job, sal)
values(9990, 'X_Y', 'CLERK', 2000);

insert into emp(empno, ename, job, sal)
values(9991, 'XZY', 'SALESMAN', 1500);

select *
from emp
where ename LIKE 'X_Y';

select *
from emp
where ename LIKE 'X\_Y' escape '\';

delete from emp
where empno >= 9990;
```
- 성능 : LIKE 연산자와 와일드카드를 활용한 SELECT문은 와일드 카드를 어떻게 사용하느냐에 따라 데이터를 조회해 오는 시간에 차이가 난다.
  
  데이터의 규모가 커지면 행 수가 어마무시한 테이블을 여러 개 조합해서 데이터를 조회하는 경우가 많은데, 이 부분에서 조회 성능 관련 부분이 주요한 이슈이다.

IS NULL (널 값 비교 연산자)
```
select *
from emp
where comm is null;
```

SQL 연산자와 NOT

select *
from emp
where job NOT IN ('PRESIDENT', 'ANALYST', 'CLERK');

select *
from emp
where sal NOT between 1300 and 3000;

select *
from emp
where ename NOT LIKE '_I%'; 

select *
from emp
where comm is not null;

집합연산자

select deptno from dept;
select deptno from emp;

select deptno from dept
union
select deptno from emp;

select deptno from dept
union all
select deptno from emp;

select deptno from dept
intersect
select deptno from emp;

select deptno from dept
minus
select deptno from emp;

select deptno, dname from dept
union
select empno, ename from emp;

# 칼럼의 갯수가 서로 일치해야 한다.
select deptno, dname from dept # 2개
union
select empno, dname, job from emp; # 3개

# 각 칼럼에 매칭되어지는 데이터 타입이 일치 해야 함. ex) deptno와 empno 이런 식으로..
select deptno, dname from dept
union
select empno, sal from emp; 

select deptno, dname from dept
union
select empno as 번호, ename as 이름 from emp;

# 아래처럼 첫번째에 as가 들어가야 한다.
select deptno as 번호, dname as 이름 from dept
union
select empno, ename from emp;

# 첫 번째 쿼리에 order by 절이 올 수 없다.
select deptno as 번호, dname as 이름 from dept order by deptno
union
select empno, ename from emp;

# 첫 번째 쿼리에서 alias를 사용하면 order by 절에서 컬럼명을 사용할 수 없고
# alias를 사용해야 함.
select deptno as 번호, dname as 이름 from dept 
union
select empno, ename from emp order by deptno;

select deptno, dname from dept
union
select empno, ename from emp order by deptno;

select deptno as 번호, dname as 이름 from dept
union
select empno, ename from emp order by 번호;


1. EMP 테이블에서 부서번호가 10, 20인 사원의 모든 정보를 출력하는 SELECT 문장을 작성하시오.
   단 부서와 이름순으로 정렬하여라.
select *
from emp
where deptno=10 or deptno=20
order by deptno, ename;

2. EMP 테이블에서 1981년에 입사한 사원 중 10번과 30 번 부서에 속한 사원의 모든 정보를 출력하는 SELECT 문을 작성하여라.
select *
from emp
where (hiredate >= '1981/01/01' and hiredate < '1982/01/01') and (deptno = 10 or deptno = 30);

3. EMP 테이블에서 상여금이 급여보다 10% 이상 많은 모든 사원에 대하여 이름, 급여, 상여금을 출력하는 SELECT 문을 작성하여라.
select ename, sal, comm
from emp
where comm >= sal * 1.1;

4. EMP 테이블에서 업무가 "CLERK"이거나 "ANALYST"이고 급여가 1000, 3000, 5000이 아닌 모든 사원의 정보를 출력하는
   SELECT 문을 작성하여라.
select *
from emp
where job in('CLERK','ANALYST') and sal not in(1000, 3000, 5000);

5. EMP 테이블에서 이름에 L이 두 자가 있고 부서가 30이거나 또는 관리자 번호가 7782인 사원의 모든 정보를 출력하는 S
   ELECT 문을 작성하여라.
select *
from emp
where ename LIKE '%L%L%' and (deptno =30 or mgr = 7782);

6. EMP 테이블에서 현재 급여에 15%가 증가된 급여를 사원번호, 이름, 업무, 급여, 증가된 급여(New Salary), 
   증가액(Increase)를 출력하는 SELECT 문장을 기술하시오.
select empno, ename, job, sal, sal * 1.15 - sal as "New Salary", sal * 1.15 - sal as "Increase"
from emp;

함수

함수의 종류

사용자 정의 함수 ⇒ PL/SQL
오라클 함수 (내장 함수)
- 단일행 함수 : 행 단위 동작
- 그룹 함수 : 연산이 되어지는 값 전체에 대해서 한 번의 연산을 수행하여 하나의 값을 반환 단일행 함수와 그룹 함수는 함께 사용 되어질 수 없다.

단일행 함수

사용

select ename, lower(ename)
from emp;

# 단 한 번만 동작함
# 인덱스 사용 가능
select *
from emp
where ename = upper('scott');

# 행이 만약 1억개라면 lower함수는 1억번 동작함
# 컬럼에 대한 함수를 사용하면 인덱스가 있어도 사용할 수 없다.
# => 테이블 풀 스캔 해야한다. 
select *
from emp
where lower(ename) = 'scott';

# 같은 결과는 얼마든지 나올 수 있다. 어플리케이션의 성능에 DB가 영향을 많이 준다.
# 쿼리를 잘 짜야 좋은 성능을 가져올 수 있다.
# 첫 번째 쿼리가 더 좋은 쿼리다.
# 성능을 고려해야하기 때문에 SELECT가 어렵다.

select upper('getbravelee')
from emp;

# dual : 오라클 관리자(sys) 소유의 테이블
# 함수 공부할 때, 테이블과 관련 없는 내용을 입력할 때 dual 사용하면 편리하다.

select upper('getbravelee')
from dual;

select 9 * 9
from dual;

종류

문자 함수

UPPER
LOWER
INITCAP
LENGTH
LENGTHB
SUBSTR
INSTR
REPLACE : 문자열
TRANSLATE : 개별 문자
LPAD
RPAD
CONCAT : 연결 연산자로 대체 가능
TRIM
LTRIM

RTRIM

select loc, initcap(loc)
from dept;
select lengthb('ABCD'), lengthb('우라나라')
from dual;
select replace('010-1234-5678', '-', ' ')
from daul;
select translate('010-1234-5678', '-', ' ')
from daul;
col new_sal format at10
select sal, translate(sal, '01012345678', '영일이삼사오육칠팔구') as new_sal
from emp;
// translate는 문자 하나하나를 변환
select ename, job, replace(job, 'CLERK', '점원')
from emp;
// replace는 문자열로 변환
select 'oracle', lpad('oracle', 10, '#'), rpad('oracle', 10, '#')
from dual;
select ename, job, concat(concat(ename, ' : '), job)
from emp;
select ename, job, ename || ' : ' || job
from emp;
select 	'[' || trim(' _Oracle_ ') || ']' as trim,
	'[' || trim(both from' _Oracle_ ') || ']' as both_trim,
	'[' || trim(leading from' _Oracle_ ') || ']' as leading_trim,
	'[' || trim(trailing from' _Oracle_ ') || ']' as trailing_trim
from dual;
select 	'[' || trim(' _Oracle_ ') || ']' as trim,
	'[' || trim(both from' _Oracle_ ') || ']' as both_trim,
	'[' || ltrim(' _Oracle_ ') || ']' as ltrim,
	'[' || rtrim(' _Oracle_ ') || ']' as rtrim
from dual;
select 	'[' || rtrim(ltrim(' _Oracle_ ')) || ']' as lrtrim,
	'[' || ltrim(' _Oracle_ ') || ']' as ltrim,
	'[' || rtrim(' _Oracle_ ') || ']' as rtrim
from dual;
select 	'[' || trim('_' from '_Oracle_') || ']' as trim,
	'[' || trim(both '_' from'_Oracle_') || ']' as both_trim,
	'[' || trim(leading '_' from'_Oracle_') || ']' as leading_trim,
	'[' || trim(trailing '_' from'_Oracle_') || ']' as trailing_trim
from dual;
select 	'[' || rtrim(ltrim('_Oracle_', '_'), '_') || ']' as lrtrim,
	'[' || ltrim('_Oracle_', '_') || ']' as ltrim,
	'[' || rtrim('_Oracle_', '_') || ']' as rtrim
from dual;

숫자 함수

ROUND
TRUNC
CEIL
FLOOR

MOD

select round(1234.5678), round(1234.5678, 0), round(1234.5678, 1), round(1234.5678, 2)
from dual;
// 소수점 이하 n번째자리까지 유효하도록 반올림
select round(1234.5678), round(1234.5678, -1), round(1234.5678, -2)
from dual;
// 양수 n번째 자리에서 반올림
select trunc(1234.5678), trunc(1234.5678, 0), trunc(1234.5678, 1), trunc(1234.5678, 2)
from dual;
select trunc(1234.5678), trunc(1234.5678, -1), trunc(1234.5678, -2)
from dual;
select ceil(1234.5678), floor(1234.5678)
from dual;
select 14/4, trunc(14/4), mod(14, 4)
from dual;

날짜 함수

SYSDATE
ADD_MONTHS
MONTHS_BETWEEN
NEXT_DAY
LAST_DAY
ROUND : 일→월, 월→년

TRUNC

// 매개변수가 없는 함수는 ()를 쓰지 않음
select sysdate, sysdate+1, sysdate-1
from dual;
select ename, job, hiredate, trunc(sysdate - hiredate) as "총 근무 일수"
from emp;
select sysdate - (sysdate - 100)
from dual;
select ename, hiredate, add_months(hiredate, 3) new_date
from emp;
select ename, hiredate, trunc(months_between(sysdate, hiredate)) "총 근무 월 수"
from emp;
select sysdate, next_day(sysdate, '월요일')
from dual;
select sysdate, last_day(sysdate)
from dual;
select ename, hiredate, round(hiredate, 'MONTH'), round(hiredate, 'YEAR')
from emp;
select ename, hiredate, trunc(hiredate, 'MONTH'), trunc(hiredate, 'YEAR')
from emp;

# 날짜 - 날짜 : 일수 차이
# 날짜 + 날짜 : 연산 불가, 지원x
# 날짜 +- 숫자 : 날짜 데이터보다 숫자만큼의 일수 이후 또는 이전 날짜

변환 함수

TO_CHAR : 숫자 또는 날짜 데이터를 문자데이터로
TO_DATE : 문자 데이터를 날짜 데이터로. db에 저장된 시간 값을 포함하는 날짜 값을 변경하거나 새로운 값을 입력할 때 to_date를 사용하지 않으면 날짜 값을 지정할 수 없다.

TO_NUMBER : 숫자형식의 문자열을 데이터를 숫자타입의 데이터로. 암시적 형변환 덕분에 잘 사용안함.

# 날짜가 아니라 날짜 형식의 문자열이기 때문에 오류
select '23/08/01', round('23/08/01', 'YEAR')
from dual;

select '23/08/01', round(to_date('23/08/01'), 'YEAR')
from dual;

# 숫자데이터나 날짜 데이터를 그대로 사용하면 오라클에서는 오라클에서 정해진
# 형식으로만 보여줌
# 데이터를 가공해서 보여주기 위해 변환 함수를 사용한다.

select sysdate, to_char(sysdate, 'RR/MM/DD')
from dual;

select sysdate, to_char(sysdate, 'YYYY-MM-DD')
from dual;

select sysdate, to_char(sysdate, 'YYYY"년" MM"월" DD"일"')
from dual;
# 시간
select sysdate, to_char(sysdate, 'YYYY"년" MM"월" DD"일" HH24:MI:SS')
from dual;
# 오후 오전
select sysdate, to_char(sysdate, 'YYYY"년" MM"월" DD"일" AM HH24:MI:SS')
from dual;

select sysdate, to_char(sysdate, 'YYYY"년" MM"월" DD"일" AM HH24:MI:SS Q"분기" DDD')
from dual;

select ename, sal, to_char(sal) new_sal
from emp;
# 9는 숫자의 하나의 자릿 수를 의미
select ename, sal, to_char(sal, '999') new_sal
from emp;
# 소수점 표시
select ename, sal, to_char(sal, '99,999.99') new_sal
from emp;

select ename, sal, to_char(sal, '99,999.99$') new_sal
from emp;
# 빈자리를 0으로 채움. 자릿수 맞춤
select ename, sal, to_char(sal, '099,999.99$') new_sal
from emp;

select `1234`, to_number('1234')
from dual;

select ename, job, hiredate, to_char(hiredate, 'YYYY-MM-DD HH24:MI:SS') new_hiredate
from emp;

update emp
set hiredate = to_date('80/12/17 14:24:36', 'RR/MM/DD HH24:MI:SS')
where ename = 'SMITH';

일반 함수
- NVL
- NVL2

select ename, job, sal, comm, sal * 12 + nvl(comm, 0) as ann_sal1, 
nvl2(comm, sal * 12 + comm, sal * 12) as ann_sal2
from emp;

DECODE

# job 칼럼에서 CLERK이면 급여 10% 상승, SALESMAN이면 5% 상승 나머지는 그대로
select ename, job, sal, decode(job, 'CLERK', sal * 1.1, 
'SALESMAN', sal * 1.05,
sal) new_sal
from emp;

CASE문

단순 CASE

select ename, job, sal, case job when 'CLERK' then sal * 1.1
when 'SALESMAN' then sal * 1.05,
else sal 
end as update_sal
from emp
order by job;

검색된 CASE

select ename, job, sal, case when SAL >= 3000 then '1등급'
when SAL >= 1500 then '2등급'
else '3등급'
end as sal_grade
from emp
order by job;

[ 연습문제 ]
1. EMP 테이블에서 이름, 입사일, 입사일로부터 6개월 후 돌아오는 월요일 구하여 출력하는 SELECT 문장을 기술하시오.

2. EMP 테이블에서 이름, 입사일, 입사일로부터 현재까지의 월수, 급여, 입사일부터 현재까지의 급여의 총계를 출력하는 SELECT 문장을 기술하시오.

select ename, hiredate, trunc(months_between(sysdate, hiredate)) total_months,
			 trunc(months_between(sysdate, hiredate)) * sal total_salary
from emp;

3. 다음의 결과를 출력하는 쿼리를 작성하세요.
	Dream Salary
	------------------------------------------------------------
	KING earns $5,000.00 monthly but wants $15,000.00
	BLAKE earns $2,850.00 monthly but wants $8,550.00
	CLARK earns $2,450.00 monthly but wants $7,350.00
	. . . . . . . . . .
	14 rows selected

select ename || ' earns ' || to_char(sal, '9,999.99$') || ' monthly but wants ' || 
			 to_char(sal * 3, '99,999.99$') as "Dream Salary"
from emp;
=> ltrim(to_char(sal * 3, '99,999.99$')) as "Dream Salary"

4. EMP 테이블에서 모든 사원의 이름과 급여(15자리로 출력 좌측의 빈곳은 "*"로 대치)를 출력하는 SELECT 문장을 기술하시오.

select ename, lpad(sal, 15, '*') new_sal
from emp;

5. EMP 테이블에서 모든 사원의 정보를 이름, 업무, 입사일, 입사한 요일을 출력하는 SELECT 문장을 기술하시오.

select ename, job, hiredate, to_char(hiredate, 'DAY') weekday
from emp;

6. EMP 테이블에서 이름의 길이가 6자 이상인 사원의 정보를 이름, 이름의 글자수, 업무를 출력하는 SELECT 문장을 기술하시오.

select ename, length(ename) name_length, job
from emp
where length(ename) >= 6;

7. EMP 테이블에서 모든 사원의 정보를 이름, 업무, 급여, 상여금, 급여 + 상여금을 출력하는 SELECT 문장을 기술하시오.

select ename, job, sal, nvl2(comm, sal + comm, sal) as "Sal + Comm"
from emp;

8. 다음의 결과를 출력하는 쿼리를 작성하세요.

col "Ename and Salary" format a80 # 첫번째 공백 줄 발생 제거 됨
select rpad(ename, 10, ' ') || lpad('*', trunc(sal / 100), '*') as "Ename and Salary"
from emp;

그룹 함수
1. 사용
- ```
select sal
from emp;

select sum(sal), max(sal), min(sal), avg(sal), count(sal)
from emp;

select count(job), count(distinct job), count(all job)
from emp;

select avg(comm)
from emp;

# count 외는 null 값을 제외한 연산 수행
select sum(comm), count(comm), count(*)
from emp;

select sum(comm) / count(comm), sum(comm) / count(*)
from emp;

select avg(comm), avg(nvl(comm, 0))
from emp;

select lower(ename), sum(sal)
from emp;

select ename, sum(sal)
from emp;
```
  💡 where 와 having 의 역할 - where 절은 disk io 를 결정한다. where 절 조건을 만족하는 행만 선택적으로 메모리에 저장 - where 절에서는 그룹 함수를 사용할 수 없다. - having 은 이미 메모리에 올라온 것 중에서 어떤 행을 사용자에게 최종적으로 제공할지를 결정 ```bash 1) select job, deptno, sum(sal) 2) from emp 3) where deptno in (10, 30) 4) group by job, deptno 5) having sum(sal) >= 4000 6) order by job, deptno; # dbms가 select 문장을 수행하는 기본 순서 ( 데이터 처리 방식 ) # 단, 순서가 절대적이진 않다. # 쿼리 최적화기에 의해 달라질 수도 있음 2 - 3 - 4 - 5 - 1 - 6 ```

Join

공통의 column이 존재 해야 함 ( 칼럼의 이름이 같아야 한다는 의미가 아니다)

⇒ 값의 종류가 일치 (데이터 타입이 같아야 한다는 아님, 크기는 다를 수 있다.) + 값의 범위 일치

# 표준sql join (권장)
select ename, job, sal, dname, loc
from emp inner join dept
		 on emp.deptno = dept.deptno;

# 예전 오라클 방식
select ename, job, sal, dname, loc
from emp, dept
where emp.deptno = dept.deptno;

표준 SQL 조인

조인의 종류

NATURAL JOIN (X)

select ename, job, sal, dname, loc
from emp natural join dept

INNER JOIN (내부 조인)

조인의 조건을 명시하는 방법

USING (X)

select ename, job, sal, dname, loc
from emp inner join dept
		 on emp.deptno = dept.deptno;

# USING은 권장하지 않는다.
select ename, job, sal, dname, loc
from emp inner join dept using (deptno);

select *
from emp inner join dept
		 on emp.deptno = dept.deptno;

select ename, job, sal, hiredate, dname, loc
from emp inner join dept
		 on emp.deptno = dept.deptno;

# error
select ename, job, sal, hiredate, deptno, dname, loc
from emp inner join dept
		 on emp.deptno = dept.deptno;

select ename, job, sal, hiredate, dept.deptno dname, loc
from emp inner join dept
		 on emp.deptno = dept.deptno;

# Table Alias #
select emp.ename, emp.job, emp.sal, emp.hiredate, dept.deptno, dept.dname, dept.loc
from emp inner join dept
		 on emp.deptno = dept.deptno;
# => table 명 대신에 alias를 사용해야한다 필히 !
select e.ename, e.job, e.sal, e.hiredate, d.deptno, d.dname, d.loc
from emp e inner join dept d 
		 on e.deptno = d.deptno;

# self join #
select worker.ename, worker.mgr, manager.empno, manager.ename
from emp worker inner join emp manager
		 on worker.mgr = manager.empno;

select worker.ename || '의 관리자는 ' || manager.ename || '이다' as "사원과 관리자"
from emp worker inner join emp manager
		 on worker.mgr = manager.empno;
# 셀프조인 규칙 
# 모든 칼럼 앞에 table alias를 붙여줘야한다.
# 자기참조 테이블

OUTER JOIN (외부 조인)

select ename, job, sal, dname
from emp, dept
where emp.deptno = dept.deptno(+);

select ename, job, sal, dname
from emp, dept
where emp.deptno(+) = dept.deptno(+);

delete from emp
where ename = 'LONGLEE';

CROSS JOIN (X)

select *
from emp, dept
order by ename, dept.deptno;

Multi-table JOIN

((((A JOIN B) JOIN C) JOIN D) JOIN E)

select ename, job, sal, grade, dname
from emp, salgrade, dept
where sal between losal and hisal and emp.deptno = dept.deptno;

테이블과 테이블 사이의 관계

EMP ← 소속 → DEPT ⇒ m : 1 의 관계 (다대일)

dept는 부모이고 emp는 자식이다 ⇒ 하나의 부모가 여러 자식을 갖는다. 하나의 부모가 여러 자식 뒤에 붙는다. ⇒ 그렇지 않고 하나의 부모뒤에 여러 자식이 붙게되면 데이터 손실이 발생.

1 ———— 1 / many ⇒ 한 명의 사원은 하나의 부서에 속해야 한다.

1 / many —— 1 ⇒ 하나의 부서는 여러 사원을 소속할 수 있다.

worker와 manager는 어떤 관계일까요 ?

worker와 manager 중 어떤 테이블이 부모일까요 ?

문제1] 뉴욕과 달라스에서 근무하는 사원들의 부서명과 평균 급여를 출력하는 코드를 작성하세요.

select dname, avg(sal)
from emp inner join dept
		 on emp.deptno = dept.deptno
where loc in ('NEW YORK', 'DALLAS')
group by dname;

문제2] 사원 중에서 월급을 가장 많이 받는 사원의 이름, 업무, 입사일, 급여 정보를 출력하는 쿼리를 작성하세요.

# 서브쿼리를 사용해서 풀어야 함... where 절은 group by 사용 못함 ~

서브쿼리

group by 절을 제외한 모든 곳에서 사용 가능

order by 절을 가질 수 없음

문제2] 사원 중에서 월급을 가장 많이 받는 사원의 이름, 업무, 입사일, 급여 정보를 출력하는 쿼리를 작성하세요.

select ename, job, hiredate, sal
from emp
where sal = (select max(sal) from emp);

서브쿼리의 종류
1. 서브쿼리의 실행 결과의 형태
  1. 단일 행 서브쿼리
  2. 다중 행 서브쿼리
  3. 다중 열 서브쿼리
2. 서브쿼리의 동작 방식에 따라
  1. 일반 서브쿼리
  2. 상관(상호연관) 서브쿼리
    
    동작방식 : ?

문제1] EMP 테이블에서 각 업무별 평균 급여가 전체 사원의 평균 급여 이상인 경우의 값을 출력하는 쿼리를 작성하세요.

select job, avg(sal)
from emp
group by job
having avg(sal) >= (select avg(sal) from emp); 

문제2] 시카고에 근무하는 사원의 이름, 업무, 입사일, 부서번호 정보를 출력하는 코드를 작성하세요.

# 서브쿼리
-- 다음 서브쿼리를 이용한 쿼리는 잘못된 쿼리입니다.
# 시카고에 존재하는 부서가 하나라서 문제가 되지 않지만, 2개 이상이라면 문제가 된다.
# 만약 아래를 추가한다면 #

inser into dept
values (50, '개발부', 'CHICAGO');

# '=' 하나의 값과 하나의 값만 비교한다.
# 서브 쿼리의 값이 2개 이상이라서 문제가 된다.
=> 다중행 서브쿼리는 비교연산자와 사용될 수 없다.
# 다중행 연산자와 사용되어야 한다.

select ename, job, hiredate, deptno
from emp
where deptno = (select deptno from dept where loc = 'CHICAGO');

# 조인
-- 다음 서브쿼리를 이용한 쿼리는 올바른 쿼리입니다.
select ename, job, hiredate, dept.deptno
from emp inner join dept on emp.deptno = dept.deptno
where loc = 'CHICAGO';

문제3] MARTIN 사원과 동일한 급여를 받는 사원의 이름, 업무, 입사일, 부서번호 정보를 출력하는 코드를 작성하세요.

# 잘못된 답안
select ename, job, hiredate, deptno
from emp
where sal = (select sal from emp where ename = 'MARTIN');

=> 단일행 서브쿼리는 하나의 행만 반환해야 함
select sal from emp where ename = 'MARTIN';
에서 동명이인이 있을 수 있음. => 단일행인지 구분(검사)해야 함
=> 다중행 연산자로

# 올바른 답안
select ename, job, hiredate, deptno
from emp
where sal IN (select sal from emp where ename = 'MARTIN');

문제4] EMP 테이블에서 한 명 이상의 부하직원을 가지는 관리자의 이름, 업무, 입사일, 급여, 부서번호를 출력하는 쿼리를 작성하세요.

select ename, job, hiredate, sal, deptno
from emp
where empno IN (select mgr from emp);

-- 위의 관리자 정보를 출력하는 쿼리를 조인을 이용하여 출력하는 코드를 작성하세요.
select distinct manager.ename, manager.job, manager.hiredate, manager.sal, manager.deptno
from emp worker inner join emp manager on worker.mgr = manager.empno;

문제5] EMP 테이블에서 아무런 부하 직원이 한 명도 없는 평사원의 이름, 업무, 입사일, 급여, 부서번호를 출력하는 쿼리를 작성하세요.

1. EMP 테이블에서 BLAKE와 같은 부서에 있는 사원의 이름과 입사일자를 출력하는 SELECT문을 작성하세요.

2. EMP 테이블에서 평균 급여 이상을 받는 모든 사원에 대해서 사원번호와 이름을 출력하는 SELECT문을 작성하세요. 단, 급여가 많은 순으로 출력하세요.

3. EMP 테이블에서 이름에 "T"가 있는 사원과 같은 부서에서 근무하는 모든 사원에 대해 사원번호, 이름, 급여를 출력하는 SELECT문을 작성하세요. 단 사원번호 순으로 출력하세요.

4. EMP 테이블에서 KING에게 보고하는 모든 사원의 이름과 급여를 출력하는 SELECT문을 작성하세요.

5. EMP 테이블에서 KING에게 보고하는 모든 사원의 이름과 급여를 출력하는 SELECT문을 작성하세요.

6. EMP 테이블에서 SALES부서 사원의 이름, 업무를 출력하는 SELECT문을 작성하세요.

7. EMP 테이블에서 SALES부서 사원의 이름, 업무를 출력하는

8. EMP 테이블에서 부서 10번 부서의 사원과 같은 업무를 맡고 있는 사원의 이름과 업무를 

9. EMP 테이블에서 FORD와 업무도 얼급도 같은 사원의 모든 정보를

10. EMP 테이블에서 업무가 JONS와 같거나 월급이 FORD 이상인 사원의 정보를 이름, 업무, 부서번호, 급여를

11. EMP 테이블에서 SCOTT 또는 WARD와 월급이 같은 사원의 정보를 이름, 업무, 급여를 출력하는

12. EMP 테이블에서 CHICAGO에서 근무하는 사원의 업무별 평균 급여와 최대, 최소 급여를 출력하는

13. EMP 테이블에서 부서별로 월급이 자신의 부서 평균 월급보다 높은 사원을 부서번호, 이름, 급여를 출력하는

14. 사원의 이름, 업무, 입사일, 부서번호, 부서명을 출력하는 쿼리를 작성하세요. 단, 조인은 사용하지 않습니다.

Create

create table dept2 (
      deptno number(2)
,     dname  varchar2(14)
,     loc    varchar2(13)
);

desc dept2;

create table dept3 (
			 empno number(4)
,      ename varchar2(10)
,      job   varchar2(9)
,      mgr   number(4)
,      hiredate date
,      sal   number(7,2)
,      comm  number(7,2)
,      deptno number(2)
);

CTAS (Create Table As Sub-query)

create table manager
as
select *
from emp
where empno in (select mgr from emp);

select *
from manager;

============================================

create table copy_dpet
as
select * from dept;

select * from copy_dept;

============================================

문제] CTAS 기능을 사용하여 EMP 테이블과 동일한 구조를 가지는 비어있는 테이블 EMP_EMPTY 를 만드는 쿼리를 작성하세요.

create table emp_empty
as
select * from emp
where 1 = 0; # 거짓 ->  테이블의 구조만 가져와 테이블 형성할 수 있다. 데이터 저장 x

desc emp_empty

select * from emp_empty;

============================================

create table emp_alter
as
select * frmo emp;

alter table emp_alter
add hp varchar2(15);

set linesize 50
desc emp_alter;
set linese 200

alter table emp_alter
rename column hp to tel;

set linesize 50
desc emp_alter;
set linese 200

alter table emp_alter
rename 

alter table emp_alter
modify tel number(10);

set linesize 50
desc emp_alter;
set linese 200

alter table emp_alter
modify job number(10);

alter table emp_alter
modify job char(8);

alter table emp_alter
modify job char(9);

set linesize 50
desc emp_alter;
set linese 200

VARCHA2() # 괄호 안은 BYTE 수.
'우리나라' 에서 한글 한 글자는 3BYTE 이다.
VARCHAR2(10) 이라면 최대 10BYTE까지 저장가능. -> 10BYTE 크기를 할당한다는 뜻은 아님
한글 3글자까지 저장가능.

select length('우리나라') from dual;
=> 4
select lengthb('우리나라') from dual;
=> 12

제약 조건

테이블 열에 저장될 데이터의 특성, 조건을 지정. COLUMN 단위로 지정.

NOT NULL

create table emp2 (
			 empno    number(4)
,      ename    varchar2(10)   not null
,      job      varchar2(9)    not null
,      mgr      number(4)
,      sal      number(7,2)    not null
,      comm     number(7,2)
,      hiredate date
,      deptno   number(2)
);

col table_name format a10
col constraint_name format a15
constraint_type format a15

select table_name, constraint_name, constraint_type
from user_constraints
where table_name = 'EMP2';

desc emp2;

=========================================================

# 명명규칙(권장)
테이블명_컬럼명_제약조건의약어

create table emp2 (
			 empno    number(4)
,      ename    varchar2(10)   constraint emp2_ename_nn not null
,      job      varchar2(9)    constraint emp2_job_nn not null
,      mgr      number(4)
,      sal      number(7,2)    constraint emp2_sal_nn not null
,      comm     number(7,2)
,      hiredate date
,      deptno   number(2)
);

select table_name, constraint_name, constraint_type
from user_constraints
where table_name = 'EMP2';

desc emp2;

=========================================================

insert into emp2
values (1111, 'LONGLEE', 'MANAGER', NULL, 3000, NULL, SYSDATE, 40);

# 위에서 sal 컬럼이 not null 인데 아래 코드에서 sal 컬럼 값이 null로 바뀜에 제약조건 위반
insert into emp2
values (2222, 'SCOTT', 'CLERK', NULL, NULL, NULL, SYSDATE, 20);

UNIQUE

drop table emp2;

create table emp2 (
			 empno    number(4)      constraint emp2_empno_uq unique
,      ename    varchar2(10)   constraint emp2_ename_nn not null
,      job      varchar2(9)    
,      mgr      number(4)
,      sal      number(7,2)    
,      comm     number(7,2)
,      hiredate date
,      deptno   number(2)
);

col table_name format a10
col contraint_name format a15
constraint_type format a15
select table_name, constraint_name, constraint_type
from user_constraints
where table_name = 'EMP2';

desc emp2;

insert into emp2
values (1111, 'LONGLEE', 'MANAGER', NULL, 3000, NULL, SYSDATE, 40);

# 위에서 sal 컬럼이 not null 인데 아래 코드에서 sal 컬럼 값이 null로 바뀜에 제약조건 위반
insert into emp2
values (NULL, 'SCOTT', 'CLERK', NULL, 2500, NULL, SYSDATE, 20);

# 안됨 이유 뭐지..?
insert into emp2
values (1111, 'SMITH', 'ANALYST', NULL, 2000, NULL, SYSDATE, 30);

PRIMARY KEY 자동으로 인덱스 생성

drop table emp2;

create table emp2 (
			 empno    number(4)      constraint emp2_empno_pk primary key
,      ename    varchar2(10)   constraint emp2_ename_nn not null
,      job      varchar2(9)    
,      mgr      number(4)
,      sal      number(7,2)    
,      comm     number(7,2)
,      hiredate date
,      deptno   number(2)
);

col table_name format a10
col contraint_name format a15
constraint_type format a15
select table_name, constraint_name, constraint_type
from user_constraints
where table_name = 'EMP2';

desc emp2;

insert into emp2
values (1111, 'LONGLEE', 'MANAGER', NULL, 3000, NULL, SYSDATE, 40);

# null 오류
insert into emp2
values (NULL, 'SCOTT', 'CLERK', NULL, 2500, NULL, SYSDATE, 20);

# 중복을 허용하지 않기 때문에 오류
insert into emp2
values (1111, 'SMITH', 'ANALYST', NULL, 2000, NULL, SYSDATE, 30);

FOREIGN KEY

drop table dept2;
create table dept2 (
       deptno     number(2)      constraint dept2_deptno_pk primary key
,      dname      varchar2(10)
,      loc        varchar2(10)
);

drop table emp2;
create table emp2 (
			 empno    number(4)      constraint emp2_empno_pk primary key
,      ename    varchar2(10)   constraint emp2_ename_nn not null
,      job      varchar2(9)    
,      mgr      number(4)      constraint emp2_deptno_fk references emp2 (empno)
,      sal      number(7,2)    
,      comm     number(7,2)
,      hiredate date
,      deptno   number(2)      constraint emp2_deptno_fk references dept2 (deptno)
);

select table_name, constraint_name, constraint_type
from user_constraints
where table_name IN ('EMP2', 'DEPT2');

CHECK

당근맛집

# default 안하면 오늘날짜로 된다.
create table board (
	boardno 	number(4) 	constraint board_autor_pk primary key	
,	autor 	varchar2(15) 	constraint board_autor_nn not null
, 	title 	varchar2(15)
,	contents 	varchar2(1500) 	constraint board_contents_nn not null	
,	area 	varchar2(15) 	constraint board_area_nn not null
,	picture 	varchar2(300)
,	writedate	 date 		constraint board_writedate_nn not null
);

insert into board
values (1111, '맛집헌터',  '부산맛집', '부산역 밀면 맛있어요', '중구', NULL, SYSDATE);

# number이면 9로 한자리 표현, varchar이면 a숫자로 표현
col boardno format 9999
col author format a15
col title format a15
col contents format a15
col area format a15
col picture format a15
col writedate format a15

select * from board;

sqlplus 
scott/tiger
@ C:oraclexe\demobld

원래대로 되돌아가

Date Dictionary

데이터베이스를 구성하고 운영하는데 필요한 정보

데이터 사전 뷰

USER_XXXX : 현재 DB에 접속한 사용자가 소유한 객체정
ALL_XXXX : 현재 DB에 접속한 사용자가 소유한 객체 또는 다른 사용자가 소유한 객체 중 사용 허가를 받은 객체, 즉 사용 가능한 모든 객체 정보
DBA_XXXX : DB 관리를 위한 정보
V$_XXXX : DB 성능 관련 정보

emp table은 메모리가 아니라 file system(하드디스크)에 있는 file로 존재

메모리는 전원이 꺼지면 사라지는 휘발성.

emp table의 data는 where 절 조건에 만족하는 행만 찾아서 메모리로 올려서 사용됨.

파일 시스템에 있는 데이터는 파일시스템에서 직접 열어서 찾을 수 없다 → emp table에 있는 모든 정보를 메모리에 올려야 한다(풀스캔) → 컬럼에 인덱스를 만들면, 인덱스는 그 컬럼에 값 정보를 가지고 있다

where 절에서 참조 되어지는 컬럼에 인덱스가 존재하면 물리적 위치정보(row id)를 이용해서 행에 대한 정보만 이용 할 수 있다. 그렇지 않으면 테이블 풀스캔뿐이다.(성능 저하)

create table
select emp

create user longlee
identified by 6789
default tablespace temp
temporary tablespace temp
quota 10m on user;

grant create session to longlee;

conn longlee/6789

conn system/6789

revoke create session from longlee;

=========================================

drop user longlee;
drop user scott cascade;
create user scott
identified by tiger;

create user longlee
identified by 6789;

grant connect, resource, create view to scott;
grant create session to longlee;

conn scott/tiger

@ C:\oraclexe\demobld.sql # demobld 실행
# longlee/6789 로 로그인
sqlplus longlee/6789

select *
from scott.emp;

conn scott/tiger

grant select on emp to longlee;

conn longlee/6789

select *
from scott.emp;

솨니

세상을 동그랗게 바라보기