SQL 기본 문법 정리

날아 2023. 6. 21. 19:02

Column : 테이블 상에서 단일 종류의 데이터. Ex) Member 테이블에서 name, phone 등등

Row : 테이블의 컬럼들의 값의 조합 Ex) Member 테이블에서 한 회원의 관련한 정보는 한 Row가 될 수 있다.

PK : 행을 구분할 수 있게 하는 컬럼 Ex) Member 테이블의 Mno (회원번호)

FK : 다른 테이블의 PK

시퀀스 : 자동으로 고유한 숫자값을 생성해주며 주로 기본 키 값을 생성하기 위해 사용

인덱스 : 쿼리 속도를 향상 (빨리 찾을 수 있게 정렬되어 있다)

스키마 : 객체의 집합으로서 테이블의 소유자를 의미

SQL 명령어의 기본부류

<DML> 테이블에 새로운 행을 입력,기존의 행을 수정,데이터 삭제 등 데이터 조작에 관한 명령어

Select (R) : 얘는 DML 아니다

Insert(C), Update(U), Delete(D)

<DDL> 구조를 만들거나 구조변경, 삭제 등 데이터 구조에 관한 명령어

Create, Alter, Drop, Rename, Truncate, Comment

<Transaction> 논리적인 작업의 단위로 DML에 의해 조작된 결과를 다루는 명렁어 / 여러개의 명령이 하나의 작업일 때

Commit, Rollback, SavePoint

Select (Distinct) 칼럼명 (Alias) From 테이블명 Order by 칼럼이나 표현식 (ASC 또는 DESC);

칼럼에 대한 Alias부여

Select name, salary*18 (AS) 연봉 From s_emp;

검색결과를 나타날 때 칼럼에 대한 별명을 지정하여 나타나는 칼럼의 Heading을 바꾸어 줄 수 있다.

Alias는 특별히 산술연산을 수행하는 경우에 유용히 사용 가능하다.

As단어는 생략 가능

Alias를 영문으로 부여한 경우에 대소문자를 구별하거나 두 단어가 구성된 경우는 이중따옴표(“”)를 써야한다.

칼럼의 합성

합성연산자(ㅣㅣ)를 사용하여 칼럼을 다른 칼럼이나 값과 연결하여 하나의 칼럼으로 출력할 수 있다.

Select namell ‘ ‘ lltitle From s_emp;

정렬순서의 지정 Order by

특정 행의 검색 where

Where절을 사용함으로써 조건에 맞는 특정 행을 선택할 수 있다.

Where 형식 연산자 값

Where title = ‘사원’

Where salary between 1000 and 2000 (1000 <= salary <= 2000)

Select (Distinct) 칼럼명 (Alias) 3

From 테이블명 1

Where 조건식 2

Order by 칼럼이나 표현식 (ASC 또는 DESC);4

Where절에 사용되는 연산자

논리연산자 And or Not

논리비교연자 < , <=, <> 등등

SQL비교연산자 Between, In, Like

IN : 목록 리스트에서 검색

Where dept_id IN (110, 113);

Like : 패턴 검색

% : 0 또는 여러 문자를 대신 할 때

_ : 한 문자를 대신 할 때

Where last_name like ‘C%e_’;

그룹함수의 종류 및 사용 목적

COUNT(a) a의 행의 개수를 구함

AVG(a) a의 평균을 구함

SUM(a) a의 합계를 구함

MIN(a) a의 최솟값을 구함

MAX(a) a의 최대값을 구함

STDDEV(a) a의 표준편차를 구함

VARIANCE(a) a의 분산을 구함

COUNT(*)를 제외한 모든 그룹함수를 NULL값을 고려하지 않는다.

COUNT(*)는 행 전체의 개수를 고려하기 때문에 NULL값을 고려하는 것이다.

NULL을 세게하려면 NVL(salary,0) : null이었던 값을 0으로 반환

Group By 와 Having절

Group By : 전체 데이터를 소그룹으로 나눌 칼럼을 명시한다.

Having : Group에 대한 조건을 기술한다.

Select (Distinct) 칼럼명, Group 함수5

From 테이블명 1

Where 조건식 2

Group By 칼럼명 3

Having 조건식 4

Order by 칼럼이나 표현식 (ASC 또는 DESC); 6

Select dept_id, AVG(salary)

From s_emp

Where title = ‘사원’

Group By dept_id;

Group에 대한 조건은 Where절에서 기술할 수 없고, Having절에서 기술하여야 한다.

Having과 where 차이

유사점은 둘 모두 데이터 세트검색을 필터링 할 때 사용할 수 있다는 점이다.

차이는 having은 그룹 전체, 즉 그룹을 나타내는 결과 집합의 행에만 적용된다. where는 개별행에 적용된다.

Having은 SQL select문이 집계 값이 지정된 조건을 충족하는 행만 반환하도록 지정하는 SQL 절이다. (집계 함수는 having 절과 함께 사용할 수 있다.)

Where는 단일 테이블에서 데이터를 가져오거나 여러 테이블과 결합하여 조건을 지정하는데 사용되는 SQL 절이다.

JOIN

JOIN이란 하나 이상의 테이블로부터 연관된 데이터를 검색해오는 방법이다.

Equijoin 칼럼간의 값들이 서로 정확히 일치하는 경우에 사용 (일반적으로 PK, FK관계에 의하여 성립)

Non-Equoijoin 한 칼럼의 값이 다른 칼럼의 값과 정확히 일치하지 않는 경우에 사용 (=연산자 외의 다른 연산자를 사용하여 JOIN)

Outer Join JOIN 조건을 만족하지 않는 경우에도 모든 행들을 다 보려는 경우에 사용

Self Join 같은 테이블에 있는 행들을 JOIN하고자 하는 경우에 사용

Equijoin

Select 테이블명.칼럼명, 테이블명.칼럼명

FROM 테이블1, 테이블2

Where 테이블1.칼럼1 = 테이블2.칼럼2;

Select s_emp.name, s_dept.name

From s_emp, s_dept

Where s_emp.dept_id = s_dept.id;

Non-Equijoin

Select e.name, e.salary, g.grade 급여등급

FROM s_emp e, salgrade g

Where e.salary BETWEEN g.losal and g.hisal;

Outer Join

Select e.name 사원명, e.id, c.name 고객명

From s_emp e, s_customer c

Where e.id (+) = c.sales_rep_id

Order by 2;

(+)를 사용하는 위치는 JOIN할 데이터가 부족한 쪽에 위치시킨다.

OUTER JOIN에서 IN이나 OR를 사용할 수 없다.

Self Join

상사테이블에서 사원이 누군지 볼 때

Select w.id 사번, w.name 사원명, m.id 부서장사번, m.name 부서장명

From s_emp w, s_emp m

Where w.manager_id = m.id;

SET 연산자

SELECT 칼럼 1, 칼럼 2

FROM 테이블 1

SET 연산자

SELECT 칼럼 1, 칼럼 2

FROM 테이블2

ORDER by;

UNION 각 쿼리결과의 합집합 (중복부분 제거)

UNION ALL 각 쿼리결과의 합집합에 공통부분을 더함 (중복부분 제거 x)

INTERSECT 각 쿼리결과의 교집합

MINUS 첫번째 쿼리 결과와 두번째 쿼리 결과의 차집합

첫번째 SELECT 구문에서 기술된 칼럼들과 두번째 SELECT 구문에서 기술된 칼럼들은 그 개수와 타입이 일치해야 한다.

FROM절 뒤에 기술된 테이블은 서로 다를수도, 같을수도 있다.

칼럼의 HEADING은 첫번째 SELECT 구문의 컬럼명이 출력된다.

ORDER BY절은 마지막에 한 번만 기술한다.

Join과 Union의 차이

Join과 Union은 두 개 이상의 테이블을 합치는데 사용하는데 결과물은 다르게 표시된다.

차이점은 데이터가 결합되는 방식이다.

Join : 새로운 열로 결합 (수평결합)

두개의 테이블 결합 시, 첫번째 테이블의 데이터는 동일한 행의 두번째 테이블 열과 함께 한 열 세트에 표시

Union: 새로운 행으로 결합 (수직결합)

두개의 테이블 결합 시, 첫번째 테이블의 데이터는 한 행 세트에 있고 두번째 테이블 데이터는 다른 세트에 (select 컬럼 수는 맞춰서 진행해야 한다.)

JOIN에서 ON과 WHERE의 차이

On이 where보다 먼저 실행되어 join을 하기 전에 필터링을 하고, where는 join을 한 후 필터링한다.

SubQUERY

서브쿼리 : 하나의 SELECT문 안에 포함되어 있는 또 다른 SELECT 문장

SELECT 검색할 컬럼들 스칼라서브쿼리

FROM 테이블명 뷰서브쿼리

WHERE 형식 연산자 (SELECT 검색할 컬럼들

FROM 테이블명 서브쿼리

…);

서브쿼리는 괄호로 묶여있어야 한다.

서브쿼리구문에서는 ORDER BY절을 포함할 수 없다.

서브쿼리는 연산자의 오른쪽에 나타나야한다.

제약조건

NOT NULL 컬럼이 널 값을 가질 수 없음

UINQUE 컬럼이 테이블의 모든 행에서 고유한 값을 가져야함

PRIMARY KEY

FOREIGN KEY

CHECK 컬럼값이 반드시 참이어야 하는 조건

TRANSACTION CONTROL (데이터 제어어DCL)

COMMIT 아직 저장되지 않은 모든 데이터 변경 사항을 데이터베이스에 저장하고 현재의 트랜잭션을 종료

SAVEPOINT 이름 현재의 트랜잭션에 savepoint를 지정

ROLLBACK 아직 저장되지 않은 모든 데이터 변경사항을 취소하고 현재의 트랜잭션을 종료

COMMIT, ROLLBACK 이전의 데이터 상태

단지 버퍼에만 영향을 받았기 때문에 데이터의 이전 상태는 복구될 수 있다.

현재 사용자는 SELECT 문으로 작업의 결과를 확인할 수 있다.

다른 사용자는 현재 사용자가 수행한 작업의 결과를 볼 수 없다.

변경된 행은 LOCK이 설정되어서 다른 USER가 변경할 수 없다.

COMMIT후의 데이터 상태

데이터에 대한 변경사항은 데이터베이스에 저장된다

이전 데이터는 영원히 잃어버리게 된다.

모든 사용자는 결과를 볼 수 있다.

관련된 행에 대한 LOCK이 풀리고 다른 사용자들이 행을 조작할 수 있다.

ROLLBACK후의 데이터 상태

데이터에 대한 변경사항은 취소된다.

이전 데이터는 다시 재저장된다.

관련된 행에 대한 LOCK이 풀리고 다른 사용자들이 행을 조작할 수 있다.

데이터 정의어(DDL)

CREATE 새로운 테이블 생성

DROP 기본 테이블의 구조 및 모든 행을 삭제

ALTER 기존 테이블을 변경 (컬럼의 추가, 수정 / 제약조건 추가, 삭제, 활성화, 비활성화)

TRUNCATE 기존 테이블의 구조는 남기고 모든 행을 삭제

RENAME Object의 이름을 바꿈

COMMENT 테이블이나 컬럼에 주석문 달기

이 명령어들은 실행시 자동 커밋후에 롤백될 수 없으므로 주의해서 실행해야한다.

컬럼의 추가

ALTER TABLE 테이블명

ADD (컬럼 datatype [DEFAULT 형식] [CONSTRAINT 정의]);

컬럼의 수정

ALTER TABLE 테이블명

MODIFY (컬럼 datatype [DEFAULT 형식] [CONSTRAINT 정의]);

OBJECT

테이블

시퀀스 자동으로 고유한 숫자값을 생성해주며 주로 기본키 값을 생성하기 위해 사용

인덱스

뷰 하나 또는 그 이상의 테이블로부터 논리적으로 데이터를 주출한 부분집합으로 논리적이고 가상적인 테이블

시노님 OBJECT의 또 다른 이름으로 ALIAS 역할

프로그램유닛

인덱스의 개념

테이블의 데이터를 좀 더 빠르게 검색하기 위해 사용하는 데이터 베이스 Object이다.

오라클 서버가 최적화 방법에 따라 어떤 인덱스를 사용할 것인지, 혹은 사용하지 않을 것인지를 결정한다.

B*Tree의 검색방법으로 디스크 입출력(I/O) 횟수를 줄인다. (LinkedList로 노드 연결)

오라클 서버가 인덱스를 자동적으로 사용하고 유지보수 한다.

인덱스는 논리적, 물리적으로 테이블과는 독립적이다.

인덱스는 자동으로 생성되기도 하고 사용자가 필요에 의해 만들기도 한다.

유니크가 걸리면 자동 인덱스가 만들어진다.

인덱스를 사용하지 않으면 테이블을 처음부터 끝까지 검색하는 방법인 풀 테이블 스캔이 이루어진다.

인덱스의 구조

인덱스는 테이블의 각 행에 대응하는 주소(ROWID)와 인덱스 컬럼값으로 구성된다.

오라클 서버는 인덱스에 대해 B*Tree 구조를 이용한다.

인덱스를 만드는 때

조건절(Where)이나 조인 조건에서 컬럼을 자주 이용할 때

컬럼이 넓은 범위 값을 가질 때

많은 NULL값을 갖는 컬럼일 때

테이블의 데이터가 많고 그 테이블에서 조회되는 행의 수가 전체의 10-15%정도일 때

인덱스를 만들지 않아야 할 때

테이블이 작을 때

컬럼이 조회의 조건으로 사용되는 경우가 별로 없을 때

대부분의 조회가 행의 10-15% 이상을 검색한다고 예상될 때

테이블이 자주 변경될 때

* 인덱스가 검색속도를 증가시키지만 항상 빠른것은 아니다. 인덱스가 유지되기 위해서는 그만큼 저장공간이 필요하고, 테이블의 수정시에 인덱스도 변경되어야 하므로 서버의 성능을 저하시킬 수 있다.

View란

하나의 Select문을 저장

ADVANCED SQL

NVL FUNCTION

NVL (형식1, 형식2)

형식 1 : NULL값을 포함하고 있는 컬럼이나 식

형식 2 : 변경하려는 값

DECODE FUNCTION

값을 비교하여 해당하는 값을 돌려주는 함수

DECODE (형식, 비교값 1, 결과치1, 비교값2, 결과치… 기본치)

형식 : 컬럼이나 값

비교값1 : 형식이 비교값1에 맞는지 비교

결과값1 : 형식이 비교값 1에 맞을 때 갖는 값

기본치 : 형식이 비교값에 맞지 않을 때 가지는 값. 생략될 경우 NULL이다.

SELECT name, salary, DECODE(TRUNC(SALARY/1000), 0, ‘E’, 1, ‘D’, ‘A’) 급여등급

FROM S_EMP;

Multi Row Comparision Operator

NOT IN

ANY 서브쿼리에 의하여 리턴되는 값 중 어느 하나와 비교 / 비교군이 여러개일 때 하나라도 있으면 검색중지

ALL 서브쿼리에 의하여 리턴되는 모든 값들을 비교 / 모든조건 만족

EXISTS 서브쿼리에서 만족되는 값이 발견되면 True

NOT EXISTS 서브쿼리에서 만족되는 값이 발견되지 않으면 True

트리거

트리거는 특정 테이블에 대한 이벤트 반응해 DML 문이 수행되었을 때, 데이터베이스에서 자동으로 동작하도록 작성된 프로그램이다.

사용자가 직접 호출하는 것이 아닌, 데이터베이스에서 자동적으로 호출한다.

이상 현상의 종류

삽입 이상 : 자료를 삽입할 때 특정 속성에 해당하는 값이 없어 null을 입력해야 하는 상황

갱신 이상 : 중복된 데이터 중 일부만 수정되어 데이터 모순이 일어나는 현상

삭제 이상 : 어떤 정보를 삭제하면 의도하지 않은 다른 정보까지 삭제되는 현상

SQL Injection

공격자가 악의적인 의도를 갖는 SQL 구문을 삽입하여 데이터베이스를 비정상적으로 조작하는 코드 기법

방어법 1 : 입력값을 검증하여 사용자의 입력이 쿼리에 동적으로 영향을 주는 경우 입력된 값이 개발자가 의도한 값인지 검증

방어법 2: 저장 프로시저 사용 (사용하고자 하는 쿼리에 미리 형식을 지정하는 것. 지정된 형식의 데이터가 아니면 쿼리가 실행되지 않는다.)

RDBMS vs NoSQL

RDBMS : 모든 데이터를 2차원 테이블 형태로 표현

장점 – 스키마에 맞춰 데이터를 관리하기 때문에 데이터의 정합성을 보장

단점 – 시스템이 커질수록 쿼리가 복잡해지고 성능 저하

NoSQL : 데이터간 관계를 정의하지 않고 스키마가 없다. 컬렉션의 형태로 데이터를 관리

장점 – Key-Value 형태로 데이터를 관리해 자유롭게 데이터 관리

단점 – 중복 발생이 가능하며 중복된 데이터가 변경된경우 모든 컬렉션에서 수정을 수행해야 한다. 스키마가 존재하지 않아 데이터 구조 결정이 어렵다.

DB튜닝

DB의 구조나 DB 자체, 운영체제 등을 조정하여 DB시스템의 전체적인 성능을 개선하는 작업

튜닝은 DB 설계 튜닝 -> DBMS 튜닝 -> SQL 튜닝단계로 진행