[Oracle Database 19c] Oracle 19c SQL Cheat Sheet | 오라클 19c 쿼리 컨닝페이퍼

Oracle 19c SQL Cheat Sheet

 

오라클 19c 쿼리 컨닝페이퍼

 

 

 

- 본 포스트에서는 오라클 데이터베이스 19c 버전에서의 기본적인 쿼리 문법에 대해 알아본다.

- 본 포스트에서는 각각의 SQL Statement 의 아주 "기초적인" 문법만을 다루며,
  해당 Statement 의 Syntax 전체를 설명하지는 않는다.

 

- 본 포스트는 아래 섹션별로 쿼리를 분류하고 있다:

 

• DDL
  - CREATE TABLE
  - ALTER TABLE
  - DROP TABLE
  - CREATE INDEX
  - ALTER INDEX
  - DROP INDEX
   
• DML
  - SELECT
  - SELECT INTO
  - INSERT
  - DELETE
  - UPDATE
  - CREATE SEQUENCE
  - ALTER SEQUENCE
  - DROP SEQUENCE
   
• DCL
  - CREATE USER
  - ALTER USER
  - GRANT
   
• TCL
  - COMMIT
  - ROLLBACK

 

---

DDL (Data Definition Language; 데이터 정의 언어)

 

CREATE TABLE

CREATE TABLE [table name] (
    <column-name-1> <type> ,
    <column-name-2> <type> ,
             ...           ,
    <column-name-n> <type>
);

 

ALTER TABLE

-- ADD COLUMN
ALTER TABLE <table-name>
ADD ( <column-name-1> <type>, ...<column-name-n> <type> );

-- MODIFY COLUMN
ALTER TABLE <table-name>
MODIFY ( <column-name-1> <new-type> );

-- DROP COLUMN
ALTER TABLE <table-name> DROP COLUMN <column-name>;

 

DROP TABLE 

DROP TABLE <table-name>;

 

CREATE INDEX

CREATE INDEX <index-name> ON <table-name> (
    <column-name-1>, <column-name-2>, ..., <column-name-n>
);

 

ALTER INDEX

-- COLLECTING STATISTICS ON INDEX
ALTER INDEX <index-name> REBUILD COMPUTE STATISTICS;

-- RENAME INDEX
ALTER INDEX <index-name> RENAME TO <new-index-name>;

 

DROP INDEX

DROP INDEX <index-name>;

 

---

DML (Data Manipulation Language; 데이터 조작 언어)

 

SELECT

SELECT
    <column-name-1>, <column-name-2>, ..., <column-name-n>
FROM
    <table-name-1>, <table-name-2>, ..., <table-name-n>
WHERE
    <condition-expr>
GROUP BY
    <grouping-column-name-1>, ..., <grouping-column-name-n>
HAVING
    <grouping-expr>
ORDER BY
    <order-column-name-1>, ..., <order-column-name-n>
;

 

SELECT INTO

SELECT
    <column-name-1>, <column-name-2>, ..., <column-name-n>
INTO
    <new-table-name>
FROM
    <table-name-1>, <table-name-2>, ..., <table-name-n>
WHERE
    <condition-expr>
GROUP BY
    <grouping-column-name-1>, ..., <grouping-column-name-n>
HAVING
    <grouping-expr>
ORDER BY
    <order-column-name-1>, ..., <order-column-name-n>
;

INSERT

-- INSERT ALL VALUES
INSERT INTO <table-name>
VALUES ( <value-1>, ..., <value-n> ); 

-- INSERT SOME VALUES
INSERT INTO <table-name> ( <column-name-1>, ..., <column-name-n> )
VALUES ( <value-1>, ..., <value-n> );

DELETE

DELETE FROM <table-name>
      WHERE <cond-expr>;

UPDATE

UPDATE <table-name>
   SET <column-name>=<value>
 WHERE <cond-expr>

CREATE SEQUENCE

CREATE SEQUENCE <sequence-name>
       MINVALUE <min-value>
       MAXVALUE <max-value>
     START WITH <start-value>
   INCREMENT BY <step-value>
          CACHE <cache-value>

ALTER SEQUENCE

-- ALTER MINVALUE
ALTER SEQUENCE <sequence-name> MINVALUE <new-min-value>;

-- ALTER MAXVALUE
ALTER SEQUENCE <sequence-name> MAXVALUE <new-max-value>;

-- ALTER INCREMENT
ALTER SEQUENCE <sequence-name> INCREMENT BY <new-step-value>;

-- SET CYCLE OR NOCYCLE
ALTER SEQUENCE <sequence-name> <CYCLE | NOCYCLE>;

-- ALTER CACHE
ALTER SEQUENCE <sequence-name> CACHE <new-cache-value>;

 

DROP SEQUENCE

DROP SEQUENCE <sequence-name>;

 

---

DCL (Data Control Language; 데이터 제어 언어)

 

CREATE USER

CREATE USER <user-name> IDENTIFIED BY <user-password>;

 

ALTER USER

ALTER USER <user-name> IDENTIFIED BY <new-user-password>;

GRANT

GRANT <privilege> TO <user-name>;

 

---

TCL (Transaction Control Language; 트랜잭션 제어 언어)

 

COMMIT

COMMIT;

 

ROLLBACK

ROLLBACK;

 

---

Reference: Oracle, SQL Language Reference, URL, 2023년 4월 3일 검색

 

Reference: Wikibooks, Oracle Database/SQL Cheatsheet, URL, 2023년 4월 3일 검색