DDL DML DCL TCL

DDL DML DCL TCL

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

1. 데이터 정의어

   :데이터베이스의 구조를 만드는 명령어

   1. - CREATE TABLE
   2. - ALTER TABLE
   3. - DROP TABLE
   4. - CREATE INDEX
   5. - ALTER INDEX
   6. - DROP INDEX

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

1. 데이터 조작어

   :테이블 안의 데이터를 넣고, 바꾸고, 지우고, 조회하는 데 사용해요.

   1. - SELECT
   2. - SELECT INTO
   3. - INSERT
   4. - DELETE
   5. - UPDATE
   6. - CREATE SEQUENCE
   7. - ALTER SEQUENCE
   8. - DROP SEQUENCE

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

1. 데이터 제어어

   사용자 권한을 관리해요. 누가 무엇을 할 수 있는지 정해요.

   1. - CREATE USER
   2. - ALTER USER
   3. - GRANT
2. TCL (Transaction Control Language; 트랜잭션 제어 언어)
   1. - COMMIT
   2. - ROLLBACK

---

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

• CREATE / ALTER / DROP

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

• ROLLBACK