SQL 문법 예시

SQL 관련 정리 : https://dobby-the-house-elf.tistory.com/86

 

DDL

1. CREATE

CREATE TABLE My_table(
 my_field1 INT,
 my_field2 VARCHAR(50),
 my_field3 DATE NOT NULL,
 PRIMARY KEY (my_field1, my_field2)
 [CONSTRAINT foreignKey]
 FOREIGN KEY my_field1
 REFERENCES otherTable id
);

 

2. ALTER

ALTER TABLE My_table ADD COLUMN my_field4 INT NOT NULL;

 

3. TRUNCATE

TRUNCATE TABLE My_table;

 

4. DROP

DROP TABLE My_table;

 

 

DML

1. INSERT

INSERT INTO 테이블_또는_뷰_이름 (컬럼1, [컬럼2, ...]) values (값1, [값2, ...])

INSERT INTO 테이블_또는_뷰_이름 values (값1, [값2, ...])

 

2. UPDATE

UPDATE table_name SET column_name = value [, column_name = value ...] [WHERE condition]

UPDATE T SET C1 = 9, C3 = 4 WHERE C2 = 'a'

UPDATE T SET C1 = C1 + 1 WHERE C2 = 'a'

 

3. DELETE

DELETE FROM 테이블_또는_뷰_이름 [WHERE 조건]

DELETE FROM pies WHERE flavour='Lemon Meringue';

DELETE FROM trees WHERE height < 80;

DELETE FROM mytable;

 

4. SELECT

 SELECT [ALL | DISTINCT] 컬럼명 [,컬럼명...]
 FROM 테이블명 [,테이블명...]
 [WHERE 조건식]
 [GROUP BY 컬럼명 [HAVING 조건식]]
 [ORDER BY 컬럼명]
 GROUP BY 컬럼명[,컬럼명...]
 ORDER BY 컬럼명[,컬럼명...]

컬럼명 as 컬럼별명으로 컬럼을 다른 이름으로 표시할 수 있다.

 

DCL

1. GRANT

GRANT CREATE SESSION TO username;

GRANT CREATE TABLE TO username;

GRANT DROP ANY TABLE TO username

 

2. REVOKE

REVOKE CREATE SESSION TO username;

REVOKE CREATE TABLE TO username;

REVOKE DROP ANY TABLE TO username

 

3. BEGIN, COMMIT, ROLLBACK, SAVEPOINT

BEGIN;
  INSERT INTO tbl VALUES (1);
SAVEPOINT savepoint_example;
  INSERT INTO tbl VALUES (2);
ROLLBACK TO SAVEPOINT savepoint_example;
  INSERT INTO tbl VALUES (3);
COMMIT;

1과 3이 삽입된다.