TIL 정리_114(SQL 문법/ORACLE)

**ORACLE 데이터베이스의 문법을 기준으로 정리하였습니다

 

접속 

계정 생성

CREATE USER 계정명 IDENTIFIED BY 비밀번호;

 

계정 삭제

DROP USER 계정명 CASCADE;

 

비밀번호 변경

ALTER USER  계정명 IDENTIFIED BY 변경할 비밀번호;

 

---

DDL

테이블 생성 CREATE

CREATE TABLE 테이블명;

 

기존 테이블을 이용하여 새로운 테이블을 정의

CREATE TABLE 신규테이블명 AS SELECT 속성명[.속성명, ---] FROM 기존 테이블명;

 

 

제약조건을 포함하여 테이블 생성

CREATE TABLE 테이블명 (

NO NUMBER(4) PRIMARY KEY,

NAME VARCHAR2(20) NOT NULL,

JUMIN VARCHAR2(13) NOT NULL UNIQUE);

 

 

**제약조건 Constraint

 

조건 이름	의미
NOT NULL(nn)	해당 조건이 설정된 컬럼에는 NULL 값이 입력되지 못하도록 한다
UNIQUE(nk)	해당 조건이 설정된 컬럼에는 중복된 값이 입력되지 못하도록 한다
PRIMARY KEY(pk)	해당 조건은 NOT NULL + UNIQUE의 특징을 가진다 테이블 내에서 데이터들끼리 유일성을 보장하는 컬럼에 설정한다 **테이블당 1개만 설정할 수 있다**
FOREIGN KEY(fk)	다른 테이블의 컬럼을 참고하여 검사한다
CHECK(ck)	해당 조건에서는 설정된 값만 입력을 허용하고 나머지는 거부된다

 

 

테이블 생성 후 추가(수정) ALTER 

ALTER TABLE 테이블명 ADD CONSTRAINT 테이블명_컬럼명_제약조건 제약조건(컬럼);

 

예시

EMP 테이블의 NO컬럼

> ALTER TABLE EMP ADD CONSTRAING EMP_NO_PK PRIMARY KEY(NO);

 

 

제약조건 중 NOT NULL을 설정할때는 ADD 대신 MODIFY를 사용한다

> ALTER TABLE 테이블명 MODIFY(컬럼명 CONSTRAINT 테이블명_컬럼_제약조건 제약조건);

 

예시

EMP 테이블의 NO컬럼

>  ALTER TABLE EMP MODIFY(NO CONSTRAINT EMP_NO_NN NOT NULL)

 

 

테이블 컬럼 이름 변경 ALTER & RENAME

ALTER TABLE 테이블명 RENAME COLUMN 컬럼명 TO 바꾼컬럼명 

 

 

컬럼 삭제 DROP

ALTER TABLE 테이블명 DROP COLUMN 컬럼명 

 

제약조건 삭제

ALTER TABLE 테이블명 DROP CONSTRAINT 테이블명_컬럼명_제약조건 

 

**DROP은 FLASHBACK을 사용하여 복원가능하다

FLASHBACK TABLE 테이블명 TO BEFORE DROP;

 

완전히 지우고자 할 때는 PURGE를 사용한다 

PURGE RECYCLEBIN;

 

 

 

---

 

DML

- 테이블의 데이터를 다룬다 

 

 

INSERT 추가

INSERT INTO 테이블명 (COLUMN1, COLUMN2, ---) VALUES(VALUE1, VALUE2, ---);

모든 테이블에 추가하는 경우 COLUMN을 생략하고 VALUE 값만 설정해도 된다 

 

UPDATE 수정

UPDATE 테이블명 SET COLUMN(컬럼명) = VALUE WHERE 조건;

 

DELETE 삭제

DELETE FROM 테이블명 WHERE 조건;

**실제 사용하는 테이블을 삭제를 할 때 반드시 WHERE절로 조건을 정하고 삭제하기를 권장**

 

MERGE 병합

MERGE INTO 테이블명1 USING 테이블명2(참조) ON (병합 조건절) //컬럼 병합시 테이블명 뒤에 컬럼명을 붙인다

WHEN MATCHED THEN UPDATE SET 업데이트 내용

WHERE 조건

WHEN NOT MATCHED THEN INSERT VALUES(VALUE1, VALUE2, ---); 

 

 

WHERE절

원하는 조건만 골라서 검색할 수 있다 

문자의 경우 ' '를 붙여야 하며, WHERE절에서는 대소문자를 구분한다(오라클 데이터베이스는 대소문자 구분X)

부적절한 식별자라는 메세지가 출력된다면 따옴표를 확인

 

SELECT 컬럼1, 컬럼2, 컬럼3(전체 컬럼인 경우 *을 붙인다) FROM 테이블명 WHERE 조건

 

예시 (EMP 테이블, 컬럼 : EMPNO, ENAME, SAL)

SELECT EMPNO, ENAME, SAL FROM EMP WHERE ENAME = 'SMITH'; //강조한 부분 대소문자 구분

 

 

조건절에 사용가능한 연산자

연산자 

 

연산자 종류	설명
=	비교 대상에서 같은 조건을 검색한다
!=, <>	비교 대상에서 같지 않은 조건을 검색한다
>	비교 대상에서 큰 조건을 검색한다
>=	비교 대상에서 크거나 같은 조건을 검색한다
<	비교 대상에서 작은 조건을 검색한다
<=	비교 대상에서 작거나 같은 조건을 검색한다
BETWEEN a AND b	A와 B 사이에 있는 범위 값을 모두 검색한다 (속도면에서 권장하지 않음, 비교연산자 권장)
IN(a, b, c)	A이거나 B이거나 C인 조건을 검색한다 괄호안의 조건이 맞으면 조회한다, 여러 조건을 간편하게 검색할 수 있다
LIKE	특정 패턴을 가지고 있는 조건을 검색한다
IS NULL / IS NOT NULL	NULL값을 검색한다 / NULL이 아닌 값을 검색한다
A AND B	A 조건과 B 조건을 모두 만족하는 값만 검색한다
A OR B	A 조건이나 B 조건 중 한가지라도 만족하는 값을 검색한다
NOT A	A가 아닌 모든 조건을 검색한다

 

 

**like 연산자

% : 글자수 제한이 없으며 어떤 글자가 와도 상관없다

_(underscore) : 글자수는 한 글자, 어떤 숫자가 와도 상관없다

사용 예 : like '___12%'

 

 

 

 

집합 연산자

 

연산자 종류	내용
UNION	두 집합의 결과를 합쳐서 출력한다. 중복 값을 제거하고 정렬한다
UNION ALL	두 집합의 결과를 합쳐서 출력한다. 중복 값을 제거하지 않고 정렬하지 않는다
INTERSECT	두 집합의 교집합의 결과를 출력한다. 정렬되어 출력된다
MINUS	두 집합의 차집합 결과를 출력한다

 

 

---

 

DCL

권한 할당 GRANT

GRANT CREATE 권한명 TO 계정명;

 

 

권한명

 

권한	기능
CREATE USER	계정 생성 권한
DROP USER	계정 삭제 권한
DROP ANY TABLE	임의 테이블 삭제 권한
CREATE SESSION	데이터베이스 접속 권한
CREATE TABLE	테이블 생성 권한
CREATE VIES	뷰 생성 권한
CREATE SEQUENCE	시퀀스 생성 권한
CREATE PRODEDURE	함수 생성 권한

 

 

권한 조회

GRANT SELECT ON 테이블명 TO 사용자; //권한 부여

SELECT * FROM 계정.테이블명;

> SCOTT 계정의 EMP 테이블의 경우 SELECT * FROM SCOTT.EMP;

 

권한 삭제

REVOKE SELECT ON 테이블명 FROM 사용자;

 

 

---

++

데이터 조회 desc 테이블명/ asc 테이블명

 

컬럼의 길이 4자리까지 출력 

COL 컬럼이름 FOR 9999

 

문자 길이를 8바이트까지 들어가게 설정

COL 컬럼이름 FOR a8

 

폭 조정

가로 set line 200

세로 set pagesize 50

 

DISTINCT : 중복된 값을 제거하고 출력한다

SELECT DISTINCT 컬럼명 FROM 테이블명; 

 

연결연산자로 컬럼을 붙여서 조회하기

SELECT 컬럼1, 컬럼2 FROM 테이블명;

-> SELECT 컬럼1 || 컬럼2 FROM 테이블명;