[SQLD] 2과목 SQL 기본 및 활용

1절. 관계형 데이터베이스 개요

​

• SQL 문장의 종류

1. DML(데이터 조작어) : (SELECT, INSERT, UPDATE, DELETE) 데이터베이스에 들어있는 데이터를 조회하거나 변형할 때 사용하는 명령어다.
2. DDL(데이터 정의어): (CREATE ALTER DROP RENAME) 테이블과 같은 데이터 구조를 정의하는데 사용되는 명령어다.
3. DCL(데이터 제어어): (GRANT REVOKE) 데이터베이스에 접근하고 사용하는 권한에 관한 명령어다.
4. TCL(트랜잭션 제어어) : (COMMIT ROLLBACK) 트랜잭션 별로 제어하는 명령어다.

​

2절. DDL

• 데이터 유형

1. CHARACTER(S) : 고정길이 문자열 정보(남는 자리는 공백으로 채움)
2. VARCHAR : 가변길이 문자열 정보(남는 자리가 없음)
3. NUMERIC : NUMBER(oracle) , SQL server는 10가지 이상으로 구분
4. DATE : 날짜와 시각 정보

​

• CREATE TABLE

CREATE TABLE table_name (

column1 datatype constraint,

column2 datatype constraint,

column3 datatype constraint,

....

);

​

외부 테이블을 활용할 때, AS SELECT로 테이블을 작성할 수 있다. 

 

CREATE TABLE new_table_name AS

SELECT column1, column2,...

FROM existing_table_name

WHERE ....;

​

주의사항: 테이블 명은 숫자로 시작할 수 없고 _, $, # 의 특수문자만 허용한다.

​

+ CONSTRAINT 제약조건 : 테이블 생성시 제약조건을 걸 수 있다.

 

종류

1. NOT NULL : NULL값의 입력을 금지한다.
2. UNIQUE : 모든 칼럼 값이 중복이 없다.
3. PRIMARY KEY : NOT NULL + UNIQUE , 중복 없이 NULL도 없다.
4. FOREIGN KEY : 외부 테이블과의 관계를 정의한다.
5. CHECK : 입력할 수 있는 값의 범위를 제한한다.
6. DEFAULT : 입력하지 않았을 때 기본적으로 입력되는 값을 정의한다.
7. CREATE INDEX : 인덱스를 생성한다.

​

CREATE TABLE에서 CONSTRAINT를 정의할 수도 있지만 만들어진 테이블에 ALTER TABLE로 제약조건을 바꿀 수도 있다. (ADD CONSTRAINT, DROP CONSTRAINT)

EX) SQL SERVER

ALTER TABLE Persons

ADD CONSTRAINT df_City DEFAULT 'Sandnes' FOR City;

​

EX) ORACLE

ALTER TABLE Persons

MODIFY City DEFAULT 'Sandnes';

​

공통) FK 추가

ALTER TABLE Orders

ADD CONSTRAINT FK_PersonOrder

FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

​

CONSTRAINT 삭제

ALTER TABLE Orders

DROP CONSTRAINT FK_PersonOrder;

​

만든 뒤에 생성된 테이블 구조 확인을 위해 다음 명령어를 사용한다.

ORACLE: DESC 테이블명;

SQL SERVER: EXEC sp_help 'dbo.테이블명' ;

​

• ALTER TABLE

1. ADD COLUMN 칼럼 추가

ALTER TABLE table_name

ADD column_name datatype;

​

2. DROP COLUMN 칼럼 삭제

ALTER TABLE table_name

DROP COLUMN column_name;

​

3. RENAME COLUMN

ALTER TABLE table_name

RENAME COLUMN old_name to new_name;

​

4. MODIFY COLUMN (+ CONSTRAINT도 같이 외우기)

SQL SERVER

ALTER TABLE table_name

ALTER COLUMN column_name datatype;

​

ORACLE

ALTER TABLE table_name

MODIFY column_name datatype;

칼럼을 바꿀 때는 해당 칼럼이 NULL만 가지고 있어야 데이터 유형 변경이 가능하다.

NULL값이 없을 때에만 NOT NULL조약을 추가할 수 있고 DEFAULT 제약은 변경 이후 값에만 적용된다.

​

• DROP TABLE

DROP TABLE table_name;

​

• TRUNCATE TABLE

TRUNCATE TABLE table_name;

​

DROP과 TRUNCATE의 차이: DROP은 테이블 자체가 삭제되고 TRUNCATE는 테이블 정의는 남고 데이터만 삭제되는 것이다. TRUNCATE는 초기 상태로 되돌리는 것이라고 생각하면 된다.

​

3절. DML

• INSERT

INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);

​

만약 테이블 전체에 삽입하고 싶다면 각 칼럼에 해당하는 값을 전부 적어줘야함.

INSERT INTO table_name

VALUES (value1, value2, value3, ...);

​

• UPDATE

UPDATE table_name

SET column1 = value1, column2 = value2, ...

WHERE condition;

​

SET에는 바꾸고 싶은 값으로 적어주고 WHERE을 통해 바꿀 행을 제한한다. WHERE절이 없다면 전체 값이 한번에 바뀌게 된다.

​

• DELETE

DELETE FROM table_name WHERE condition;

​

TRUNCATE, DROP은 DDL이지만 DELETE는 DML이라 로그가 남는다. (롤백 가능)

​

• SELECT

SELECT [ALL/DISTINCT] column1, column2, ...

FROM table_name;

기본으로 ALL이지만 DISTINCT로 설정해주면 중복값을 제외하고 출력한다.

​

+ ALIAS: 별명 지정. 칼럼이나 테이블에 별명을 붙여 쉽게 쓸 수 있도록 한다. AS는 생략가능

SELECT column_name (AS) alias_name

FROM table_name;

​

+ 합성 연산자 (CONCATENATION)

CONCAT(string1, string2) , '+' (SQL server), '\\' (oracle) 로 두 문자를 연결하여 출력할 수 있다.

​

4절. TCL

트랜잭션이란 밀접히 관련되어 분리될 수 없는 한 개 이상의 SQL문장들의 모임으로 분할할 수 없기에 전부 적용하거나 전부 취소하게 된다.

​

• 트랜잭션의 특성

1. 원자성: 정의된 연산은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은 상태로 남아있어야 한다. (all or nothing)
2. 일관성: 트랜잭션이 실행되기 전의 데이터베이스 내용이 잘못되어 있지 않다면 트랜잭션이 실행된 이후에도 데이터베이스의 내용이 잘못이 있으면 안 된다.
3. 고립성: 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다.
4. 지속성: 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장된다.

​

• COMMIT

트랜잭션을 성공적으로 실행시키고 데이터베이스에 적용한다.

• ROLLBACK

트랜잭션을 취소하고 되돌린다. 마지막 COMMIT까지 열려있는 트랜잭션을 모두 롤백한다.

​

-ORACLE은 DBMS가 내부적으로 트랜잭션을 실행하며 DML 문장 수행 후 COMMIT/ROLLBACKD을 해야 트랜잭션이 종료된다. SQL SERVER는 AUTO COMMIT으로 따로 COMMIT/ROLLBACK을 하지 않아도 된다. 만일 ROLLBACK이 필요하면 의도적으로 트랜잭션을 열어야 한다.

-ORACLE은 DDL 실행시 자동적으로 COMMIT된다. SQL SERVER는 DDL이후 자동 커밋되지 않는다.

​

• SAVEPOINT

SAVEPOINT를 만들면 롤백가능한 지점이 생겨 원하는 만큼 일부만 롤백 가능하다. 복수의 SAVEPOINT 설정 가능하고 만일 같은 이름으로 설정한다면 나중에 만든 것만 유효하다.

​

SAVEPOINT savepoint_name ;

ROLLBACK TO savepoint_name ;

​

5절. WHERE 조건절

SELECT column1, column2, ...

FROM table_name

WHERE condition;

​

• 연산자

1. 비교 연산자 : >. <. =. >=.<=, <>
2. SQL 연산자: BETWEEN A AND B, IN(list), LIKE '%', IS NULL
3. 논리 연산자: AND, OR, NOT

​

연산자 우선 순위: () > NOT > 비교연산자 > AND > OR (AND가 먼저라는 건 꼭 외우기)

​

+ TOP/ ROWNUM 쿼리 : 상위 몇 개만 추출해서 출력

SQL SERVER)

SELECT TOP number|percent column_name(s)

FROM table_name

WHERE condition;

​

- TOP 3 : 3개만 / TOP 50 percent : 상위 50퍼 까지

​

ORACLE)

SELECT *

FROM (SELECT column_name(s) FROM table_name ORDER BY column_name(s))

WHERE ROWNUM <= number ;