Oracle sql

9 분 소요

DATABASE

SQL 종류

DML(데이터 조작언어)
- Data를 조작하기 위해 사용하는 언어
- DML 만 COMMIT, ROLLBACK 가능
- INSERT / SELECT / UPDATE / DELETE - CRUD
  - DQL(데이터 질의 언어)
    - 데이터 검색(추출)을 위한 언어
    - SELECT : 데이터 검색
DDL(데이터 정의언어)
- 데이터 구조를 변경,삭제 하기 위해 사용하는 언어
- CREATE / ALTER(MODIFY, DROP, ADD)/ DROP
DCL(데이터 제어 언어)
- GRANT / REVOKE
TCL (트랜잭션 언어)
- 일괄처리. INSERT, UPDATE, DELETE 에서만 사용
- COMMIT, ROLLBACK(커밋 시 롤백 불가)

VALUE TYPE

문자
- CHAR
  - CHAR (50) 으로 생성 시 50개의 글자를 사용하지않아도 50을 모두 저장함
  - 고정길이 데이터에만 사용하는게 좋음
- VARCHAR2
  - VARCHAR2(50) 으로 생성 시 2개의 글자만 사용 되면 구분자로 지정해서 저장함
  - 가변길이 데이터는 해당 타입 사용
  - CHAR 에 비해 구분자를 사용하여 저장하기때문에 검색 시 느림
- NCHAR or NVARCHAR2
  - 모든 나라의 언어 사용가능(encoding UTF8 지정)
  - 2 or 3 byes 사용
- CLOB
  - 대용량 텍스트 데이터 타입(최대 4G)
- NCLOB
  - 대용량 텍스트 유니코드 데이터 타입(최대 4G)

숫자

NUMBER

정수와 실수 모두 표현

NUMBER(4) // 최대 4자로 이루어진 숫자
NUMBER(6,2) // 소수점 2자리를 포함하는 최대 6자리의 숫자
NUMBER(6,-2) // 소수점 -2자리에서 반올림하는 최대 6자리 숫자
NUMBER // 

날짜
- DATE
  - 날짜만 보여줌
- TIMESTAMP
  - 날짜와 시,분,초 까지모두 표현함.

연산자

산술 연산자(+,-,*,/)

+ 연산자는 숫자만 더함

문자열 덧셈은 || 기호를 사용하여 덧셈 할것.(‘둘’ ‘리’)

‘3’ + 1 의 값은 4로 자동캐스팅되어 계산됨. (‘A’+1 은 에러를 발생함.)

비교 연산자(=,!=,^=,<>,>,<,>=,<=, IS NULL, IS NOT NULL)
- NULL 조회 시는 WHERE 컬럼명 IS NULL
- NULL이 아닌 값 조회 시 WHERE 컬럼명 IS NOT NULL

관계연산자(NOT, AND, OR, BETWEEN, IN)

BETWEEN : 사이에있는 값을 도출해 낼때 사용

-- 조회수 0,1,2 게시글
SELECT * FROM NOTICE WHERE HIT BETWEEN 0 AND 2;

IN : 연속되지 않는 각각 다른 범위의 결과를 도출해낼때 사용
```
-- 조회수 0,2,7 게시글 조회
SELECT * FROM NOTICE WHERE HIT IN (0,2,7);
```

NOT : 부정연산자

SELECT * FROM NOTICE WHERE HIT NOT IN(0,2,7);

패턴 연산자(LIKE, %, _)

LIKE : 부분적으로 일치하는 컬럼을 찾을때 사용
‘_’ : 글자숫자 정해 조회할때 사용
’%’ : 포함된 문자를 조회할때 사용

-- '둘'으로 시작하는 사람 조회
SELECT * FROM MEMBER WHERE NAME LIKE '둘%';
-- '둘'이 이름에 포함되어있는 사람 조회
SELECT * FROM NAME WHERE LIKE '%둘%';
-- '리'로 끝나는 사람 조회
SELECT * FROM NAME WHERE LIKE '%리';
-- '둘'로 시작하는 이름의 3글자 사람 조회
SELECT * FROM NAME WHERE LIKE '둘__';

정규식을 이용한 패턴 비교

정규표현식 사이트

숫자 비교 정규식
- ^ : 시작
- $ : 끝
- [] : 하나의 글자
- - : 범위를 나타냄
- \d : [0-9] 와 동일
- {..} : 반복되는 문자또는 숫자를 나타낼때사용
```
-- 전화번호 비교 패턴
^01[0-9] - \d{3,4} - \d{4}$ 
```

오라클에선 정규표현식 사용해 비교 시 REGEXP_LIKE 함수 사용

  -- ^, $ 를 사용하면 포함된게 아닌 무조건 0으로 시작하고 숫자 4자리로 끝나야함을 나타냄. 포함을 뜻하기 위해선 ^,$ 를 지우고 사용
  SELECT * FROM NOTICE WHERE REGEXP_LIKE(TITLE, '01[0-9] - \d{3,4} - \d{4}');

문자열 비교를 위한 정규식
- \w : 모든 문자 표현(영문 대소문자,숫자)
- ? : 없어도 되고 있어도 되는것
- * : 0개 이상
- + : 1개 이상
- : 또는
- \D : [^0-9] 의 의미(숫자가 위치하면 안된다는 부정의미)
```
-- 이메일 비교 패턴
SELECT * FROM NOTICE WHERE REGEXP_LIKE(EMAIL, '\D\w+@\D\w+.(com|net|org)');
```

DCL

GRANT : 권한(부여할 권한 하나하나), 롤(권한 묶음)을 사용자에게 부여

GRANT [권한|롤] TO [사용자|롤|PUBLIC] [WITH ADMIN OPTION]
-- public 해당 권한을 별도의 권한없이 사용할 수 있도록 함.
-- with admin option : 권한을 부여받은 사용자가 다시 다른사용자에게 권한을 부여할 수 있도록 함.

-- create session 권한 부여, connect 롤을 부여
grant create session to qwerty;
grant connect to qwerty;

-- 테이블 생성 권한 부여
grant create table to qwerty;
  
-- tablespace 공간 사용부여
alter user qwerty quota unlimited on users;

-- data dictionary 에서 권한/ 롤 조회
select * from dba_sys_privs where grantee IN ('CONNECT', 'RESOURCE');

-- 특정 테이블 권한 부여
grant select,insert,update, delete on kh.tb_coffee to qwerty;

시스템 권한 종류

종류	내용
CREATE USER	데이터 베이스 유저 생성 권한
SELECT ANY TABLE	모든 유저의 테이블 조회 권한
CREATE ANY TABLE	모든 유저의 테이블 생성 권한
CREATE SESSION	데이터베이스 접속 권한
CREATE TABLE	테이블 생성 권한
CREATE VIEW	뷰 생성 권한
CREATE PROCED USER	프로시저 생성 권한
CREATE SEQUENCE	시퀀스 생성 권한
SYSDBA	데이터베이스를 관리하는 최고 권한
SYSOPER	데이터베이스를 관리하는 권한

REVOKE : 주어진 권한 회수

revoke insert, update, delete on kh.tb_coffee from qwerty;

DD의 종류 : ???_복수형객체명 USER_TABLES, ALL_TABLES
- 1.USER_XXX : 사용자 소유의 객체
- 2.ALL_XXX : 사용자 소유의 객체 포함, 사용권한을 부여받은 객체
- 3.DBA_XXX : 관리자소유의 객체(일반 사용자 조회불가)
```
-- 권한조회
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'QWERTY';
-- 롤 조회
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'QWERTY';
-- 테이블 관련 권한 조회
SELECT * FROM DBA_TAB_PRIVS WHERE OWNER ='KH';
```

DDL

데이터베이스 객체에 대해 생성(CREATE),수정(ALTER),삭제(DROP) 하는 명령어
- 자동 COMMIT 되므로 주의해서 실행

CREATE

객체 생성
컬럼명 자료형 [기본값] [제약조건]

서브쿼리를 이용한 생성은 CREATE TABLE 테이블명 AS(SUB-QUERY); 와 같이 AS 키워드 붙임.

CREATE TABLE MEMBER(
  ID VARCHAR2(20) NOT NULL,
  PASSWORD VARCHAR2(30) NOT NULL,
  NAME VARCHAR2(50),
  REG_DATE DATE DEFAULT SYSDATE
);

주석

TABLE

주석확인

DATA DICTIONARY 에서 확인

SELECT *
FROM USER_TAB_COMMENTS 
WHERE table_name = 'EMPLOYEE';

주석 생성(수정,삭제 함수는 없음. 다시쓰면 바뀜)

-- 내용 '' 으로 두면 NULL 들어감
COMMENT ON TABLE MEMBER IS '내용'; 

COLUMN

컬럼 주석 확인

SELECT *
FROM USER_COL_COMMENTS
WHERE TABLE_NAME = 'MEMBER';

COMMENT ON COLUMN MEMBER.ID IS '회원아이디';
COMMENT ON COLUMN MEMBER.PASSWORD IS '비밀번호';
COMMENT ON COLUMN MEMBER.NAME IS '회원이름';
COMMENT ON COLUMN MEMBER.REG_DATE IS '회원가입일';

제약조건

데이터의 무결성을 보장받기 위해 사용

무결성이란 데이터베이스에 저장된 값들이 정확하고 일관성 있는 데이터임을 나타내는 의미, 무결성이 보장되도록 여러 객체에 규정을 부여하고 운영(필요없는 데이터는 걸러내고 필요한 데이터만 남아있는 상태)
제약조건 작성방법
NOT NULL 을 제외한 나머지 제약조건은 제약조건명은 반드시 작성
- 컬럼레벨
  - 컬럼명 기술한 같은줄에 작성, NOT NULL 은 컬럼레벨만 작성 가능
    CREATE TABLE MEMBER( EMAIL VARCHAR2(100) CONSTRAINT UQ_MEMBER_EMAIL UNIQUE, );
- 테이블레벨
  - 별도의 줄에 작성, 먼저 생성 한뒤 따로 작성
    CREATE TABLE MEMBER( EMAIL VARCHAR2(50), CONSTRAINT UQ_MEMBER_EMAIL UNIQUE (EMAIL) );

제약조건 확인(Data Dictionary)

SELECT *
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'EMPLOYEE';

--컬럼명 확인
SELECT * FROM USER_CONS_COLUMNS 
  WHERE TABLE_NAME = 'EMPLOYEE';

SELECT UC.TABLE_NAME, UCC.COLUMN_NAME, 
      UC.CONSTRAINT_NAME, UC.CONSTRAINT_TYPE, 
      UC.SEARCH_CONDITION
FROM USER_CONSTRAINTS UC 
  JOIN USER_CONS_COLUMNS UCC
  ON UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
  WHERE UC.TABLE_NAME = 'EMPLOYEE';

NOT NULL (C)

데이터를 입력받을때 NULL 을 허용하지않으므로, 반드시 값이 입력되어야 함.(필수값)

CREATE TABLE TEST(
  ID VARCHAR2(50) NOT NULL,
  EMAIL VARCHAR2(200) NULL
)
--수정
ALTER TABLE TEST MODIFY EMAIL VARCHAR2(200) NOT NULL;

-- 제거
ALTER TABLE 테이블명 MODIFY 컬러명 NULL;
ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;

DEFAULT

입력값을 지정하지 않았을때 열의 값이 기본값으로 입력되도록 지정하는 값

CREATE TABLE TEST(
  PWD VARCHAR2(200) DEFAULT '111'
)
-- 수정
ALTER TABLE TEST MODIFY EMAIL VARCHAR2(200) DEFAULT '222';

CHECK (C)

domain 안에서만 값의 범위를 정해놓는 제약조건 (성별, 퇴사여부,점수 등)

CHECK 제약을 사용해 조건에 맞는 데이터만 입력받을 수있음.(NULL 허용)

CREATE TABLE TEST(
  PHONE VARCHAR2(200) CHECK(PHONE LIKE '^01[01]-\d{3,4}-\d{4}$) NOT NULL')
)
-- 제약조건 추가방법(정규표현식 사용시 REGEXP_LIKE 함수 사용해야함)
ALTER TABLE MEMBER_COPY ADD CONSTRAINT MEMBE_PHONE_CHK 
CHECK(REGEXP_LIKE(PHONE, '^01[01]-\d{3,4}-\d{4}$'));

UNIQUE (U)

중복되는 데이터가 존재할 수 없음(NULL 존재할수있음. 비교대상이아님, DBMS 마다 처리방식 다름)

주민번호, 이메일

CREATE TABLE MEMBER(
EMAIL VARCHAR2(100) CONSTRAINT UQ_MEMBER_EMAIL UNIQUE,
);
CREATE TABLE MEMBER(
EMAIL VARCHAR2(50) NOT NULL,
CONSTRAINT UQ_MEMBER_EMAIL UNIQUE (EMAIL)
);

PRIMARY KEY (P)

기본키. 행(레코드)의 식별자 컬럼을 지정
- NOT NULL + UNIQUE, 테이블당 한개 사용 가능
테이블 행에 대한 고유 식별자 역할을 하는 제약조건
- 다른 행(레코드) 구분하기위한 용도, 중복 또는 NULL 허용하지 않음.(테이블당 하나) ``` CREATE TABLE MEMBER( PHONE char(11) not null, CONSTRAINT PK_MEMBER_ID PRIMARY KEY(ID), CONSTRAINT UQ_MEMBER_EMAIL UNIQUE(EMAIL) );
– 만들어진 테이블에 추가 ALTER TABLE 테이블명 ADD PRIMARY KEY (컬럼명1, 컬럼명2, 컬럼명3 …) – 기본키 삭제 ALTER TABLE 테이블명 DROP PRIMARY KEY – 조회 SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = ‘테이블명’ – 이름 재지정 ALTER TABLE 테이블명 RENAME CONSTRAINT 변경 전 이름 TO 변경 후 이름; ```

FOREIGN KEY (R)

외래키. 두 테이블간의 부모자식 참조관계를 자식테이블에서 설정(참조무결성을 보장)

참조하고있는 부모테이블에 존재하는 값또는 NULL 값 자식테이블에서 사용가능

부모테이블의 참조컬럼은 반드시 PK, UQ제약조건이 걸려야있어야 함.

-- 테이블 생성
CONSTRAINTS 외래키명 FOREIGN KEY 컬럼명 REFERENCES 참조 컬럼(테이블)
-- 생성 후
ALTER TABLE 테이블명 ADD CONSTRAINT 외래키명 FOREIGN KEY 참조컬럼
REFERENCES 참조 테이블명(참조 컬럼)
-- 외래키 삭제
ALTER TABLE 테이블명 DROP CONSTRAINT 제약명;

삭제옵션
- 부모테이블의 행을 삭제할 때, 참조하고 있는 자식테이블 행에 대한 처리
  - ON DELETE RESTRICTED(기본값) - 자식테이블 참조행이 있는 경우 부모행 삭제 RESTRICTED(제한) > 자식행 삭제 후 부모행 삭제
  - ON DELETE CASCADE - 참조되는 부모 테이블의 행에대한 삭제를 허용함. 참조되는 부모테이블 값이 삭제되면 연쇄적으로 자식 테이블 값 역시 삭제됨.
  - ON DELETE SET NULL - 참조되는 부모테이블의 행에 대한 삭제를 허용함. 부모테이블의 값이 삭제되면 해당 참조하는 자식테이블의 값들은 NULL 값으로 설정
- 식별관계 : 참조하는 컬럼값(PK, QU)을 자식테이블에서 PK로 사용하는경우
```
CREATE TABLE SHOP_NICKNAME(
    MEMBER_ID VARCHAR(20),
    NICKNAME VARCHAR(100),
    CONSTRAINT PK_SHOP_NICKNAME_MEMBER_ID PRIMARY KEY(MEMBER_ID),
    CONSTRAINT FK_SHOP_NICKNAME_MEMBER_ID FOREIGN KEY(MEMBER_ID) 
                                        REFERENCES SHOP_MEMBER(ID)
);
```
- 비식별관계 : 참조하는 컬럼값(PK, QU)을 자식테이블에서 PK로 사용하지 않는경우
```
create table shop_buy (
    no number,
    member_id varchar2(20),
    product_name varchar2(100),
    constraint pk_shop_buy_no primary key(no),
    constraint fk_shop_buy_member_id foreign key(member_id)
                                    references shop_member(id)
--                                   ON DELETE RESTRICTED (기본값)
--                                   ON DELETE SET NULL
--                                   ON DELETE CASCADE   
);
```
  ALTER, DROP SUB 명령어

TABLE의 컬럼/제약조건 대해서 다음 명령어 실행

ADD 컬럼/제약조건

-- not null 제약조건 add가 modify sub 명령어를 사용해야 함.
ALTER TABLE TB_PRODUCT ADD PRICE NUMBER DEFAULT 0 NOT NULL;
ALTER TABLE TB_PRODUCT ADD CONSTRAINT PK_PRODUCT_NO PRIMARY KEY(NO);

MODIFY 컬럼(제약조건 수정 불가)
- 자료형, 컬럼의 DEFAULT 값 변경 가능
- 데이터가 있는경우 자료형변경 제한적으로 가능, 실제 저장된 데이터보다 큰 사이즈로만 가능
```
ALTER TABLE TB_PRODUCT MODIFY NAME VARCHAR2(1000);
```

RENAME 컬럼/제약조건

alter table 테이블명 rename column 변경전 컬럼 to 변경 후 컬럼;

DROP 컬럼/제약조건
```
DROP TABLE 컬럼/제약조건명;
```

DML

실행 순서
- FROM > WHERE > GROUP BY > HAVING > ORDER BY > SELECT

INSERT

값 추가 시 사용
- 레코드 미지정 시 테이블에있는 모든 컬럼 값 기재해야함.
  - 제약조건에 따라 컬럼에 값을 넣지않으면 NULL 값이 들어감.
- 컬럼, 예약어는 대소문자를 비교하지않지만, 레코드값을 비교할때는 대소문자를 비교함.
- 컬럼 지정 시 원하는 필드만 원하는 순서대로 입력 가능
```
INSERT INTO MEMBER(ID,PWD) VALUES('ID','1234');
INSERT INTO MEMBER(PWD,ID) VALUES('1234','idval');
```

UPDATE

값 수정 시 사용

UPDATE 시 WHERE 조건절을 사용하지않으면 모든 값이 바뀜.

UPDATE 테이블명 SET 컬럼명='변경값', 컬럼명='변경값' WHERE 레코드='값';
UPDATE MEMBER SET PWD='333', NAME='둘리' WHERE ID='idval';

DELETE

들어있는 값 삭제 시 사용
- DELETE 시 WHERE 조건절을 사용하지않으면 모든 값이 삭제됨.
```
DELETE 테이블명 WHERE 데이터명='값'; 
```

SELECT

컬럼 별칭 사용 시 별칭에 공백사용 시 큰 따옴표("") 사용 as 생략 가능하며, 별칭 명은 대소문자를 구분함.
```
SELECT id "new id",pwd as "pass" FROM MEMBER;
```

TCL 트랜잭션

작업이 완료될때까지 임시저장소에서 테스트를 하는동안 다른세션이 건드리지 못하도록 묶어놓는것.
- 작업이 완료되면 commit하여 다른 세션도 변경사항을 확인할수 있도록 해줌
- 작업을 취소하려면 rollback 테스트 하던 내용을 저장하지않고 넘김
컬럼 변동이 있을 시 자동으로 commit 되므로 주의할것.

행을 제한하기(TOP-N 분석)

ROWNUM : 각 행에대한 일련번호. 데이터 추가 시 1씩 증가하며 자동으로 부여. 테이블행에 대한 rownum 은 변경불가. where 절, inline-veiw 를 통해 테이블형태가 바뀌는 경우는 새로부여됨.
rowid: 특정 레코드(행)에 접근하기 위한 논리적 주소값, 데이터 추가 시 자동으로 부여
rownum 의 완벽한 결과는 where 절이 끝난 이후에 얻을 수 있음.

1부터 순차적으로는 처리하지 않는다면 inline-view 레벨을 하나 더 사용해야함.

-- ROWNUM 을 지정해주지 않았기 때문에 조회에서는 ROWNUM 이 1로 고정
-- 고정된 값으로 값을 비교하기 때문에 해당 조회의 결과는 아무것도 출력이 되지않음.
SELECT * FROM MEMBER WHERE ROWNUM = 2;
-- SELECT 시 ()를 이용해 ROWNUM의 값을 묶어 서브쿼리로 먼저 ROWNUM 을 지정한뒤 조회하면 됨.
SELECT * FROM(SELECT ROWNUM NUM, MEMBER.* FROM MEMBER)
WHERE NUM = 2;    사용예시
-- 부서별 평균급여 랭킹에 4~6위 조회
select E.*
from (
    select rownum R, E.* from 
        (
        select trunc(avg(salary)) avg, nvl(dept_code,'린턴')
        from employee
        group by dept_code
        order by 1 desc
        ) E
) E
where R between 4 and 6;

ROWNUM 은 *(모든값) 에 포함되기 때문에 참고할 테이블 값.* 으로 지정해서 사용
SELECT 시 ROWNUM 이 생기기 때문에 subQuery 에 사용하는 ROWNUM 은 별칭을 사용해서 조회
중복된 값 제거 후 조회
```
SELECT DISTINCT AGE FROM MEMBER;
```

Twitter Facebook LinkedIn

know701