SQL(Structured Query Language) 1: DDL,DML,TCL

1. 데이터베이스

여러 사람이 공유해 사용할 목적으로 통합하여 관리되는 일정한 형태로 관리하는 데이터들을 저장

e.g. Maria DB, Amazon Redshift, Oracle DB 

데이터베이스의 종류에 따라 사용 방법이 조금씩 다름 하지만! 데이터베이스에서 검색과 분석에 사용되는 기본 사용 방법은 데이터베이스 종류와 상관없이 동일(약간 다를 수 있다.)

데이터베이스 종류

• 비관계형 데이터 베이스
  • NoSQL(Not Only SQL)  구조적 유연성
  • 데이터 저장,검색에 특화
  • 분산 환경에 더 빠르게 처리
  • 복잡한 데이터 구조 표현
  • e.g. MongoDB, CouchDB
• 관계형 데이터 베이스(RDB)
  • 고유한 키(key)와 값(value)들의 간단한 관계를 테이블화 (출처 위키백과)
  • 각 테이블간 관계 설정
  • 스키마 정의
  • 정형화된 데이터
  • 구조적 안전성
  • 하나 이상의 테이블로 이루어지며 서로 연결된 데이터를 가지고 있음 관계형 데이터베이스는 SQL을 통해 제어 가능
  • e.g. Mysql, Oracle, MS SQL ,MariaDB ,SQLite

 

2. SQL(Structured Query Language)

관계형 데이터 베이스에 정의,조작, 제어를 하기위해 사용하는 표준 언어

• DDL(Data Definition Language) 데이터 정의어
• DML(Data Manipulation Language) 데이터 조작어
• DCL(Data Control Language) 데이터 제어어
• TCL(Transaction Control Language)  트랜잭션 제어어

특징

• case-insensitive syntax의 대문자나 소문자 구분하지 않는다. 하지만 가독성을 위해서 대문자 추천
  • 예. select == SELECT
• ;(semicolon)을 마지막에 붙여야 한다.
• 실행 순서 FROM-WHERE-GROUP BY-HAVING-SELECT-ORDER BY
  • https://sqlbolt.com/lesson/select_queries_order_of_execution
• mysql에서는  having으로 alias사용 가능

2.1 DDL(Data Definition Language, 데이터 정의어)

데이터 베이스의 객체(사용자,테이블, 인덱스,뷰, 트리거,프로시저, 사용자 정의 함수)  를 정의/변경/제거

(1) CREATE 생성

데이터 형을 정의하여 테이블 형성 

제야조건 정의

• PRIMARY KEY  기본키 지정
• NOT NULL   NULL 입력 금지
• CHECK       사용자 정의한 값으로 범위 제한
• FOREIGN KEY ...REFERENCES ...   외래키 지정(참조 무결정 제약조건)
• DEFAULT     기본값 지정

CREATE TABLE 테이블명 
  (컬럼1 VARCHAR(10) NOT NULL, 
  컬럼2 NUMBER(10),
  컬럼3 DATE
  PRIMARY KEY(컬럼2)
  );

(2) ALTER 

이미 정의한 컬럼, 제약조건 제거, 변경 가능하고 새로운 컬럼,제약조건 추가 가능

ALTER TABLE 테이블명 ADD (추가컬럼 CHAR(1));

ALTER TABLE 테이블명 DROP 제거컬럼;

ALTER TABLE 테이블명 MODIFY (변경컬럼 INT(1) DEFAULT 0 NOT NULL);

ALTER TABLE 테이블명 RENAME 변경전컬럼명 TON 변경할컬럼명;

RENAME 변경전테이블명 TO 변경할테이블명;

 

ALTER TABLE 테이블명 ADD CONSTRAINT 참조무결성제약조건명
     FOREIGN KEY(생성할외래키) REFERENCES 참조할테이블(참조할컬럼명);

ALTER TABLE 테이블명 DROP CONSTRAINT 제거할외래키;

(3) DROP

데이터베이스 내 테이블 완전히 제거 ( 데이터, 객체 제거)

DROP TABLE 테이블명 ;

(4) TRUNCATE

데이터베이스 내 데이터 완전히 제거

ROLLBACK으로 복구 불가

TRUNCATE TABLE 테이블명 ;

 

 

2.2 DML(Data Manipulation Language, 데이터 조작어)

데이터 검색, 등록,삭제, 갱신을 위한 조작 언어

DML 실행 후 영구 저장하려면 Commit, 취소하려면 Rollback

 

(1) INSERT 등록

 INSERT INTO 테이블 (attr1, attr2 ... attrN ) VALUES (값 1, 값2, ..., 값N);

 INSERT INTO 테이블 VALUES (값 1, 값2, ..., 값N);

생성된 테이블에 칼럼 명시하지 않는 경우 순서대로 넣어야함.

한번에 여러 필드의 값들 입력도 가능

(2) UPDATE 갱신

조건에 맞는 지정한 칼럼에 값 변경

UPDATE 테이블 SET attr = 변경할값 WHERE 조건;

 

(3) DELETE 삭제

조건에 맞는 필드 삭제

DELETE FROM 테이블 WHERE 조건;

조건을 명시하지 않으면 테이블 내의 모든 필드가 사라진다. drop과 다른점은 정의된 구조가 사라지는 것은 아님 

DELETE FROM 테이블;

 

(4) SELECT 검색(기본 형태)

전 어트리뷰트 포함해서 검색(전체 테이블 출력)

SELECT *
FROM 테이블;

 

작성 순서 

SELECT 
FROM
WHERE
ORDER BY
GROUP BY
HAVING

실행순서

FROM > WHERE > GROUP BY > HAVING >SELECT > ORDER BY 

2.3 TCL(Transaction Control Language, 트랜잭션 제어어)

 데이터의 무결성 보장하기 위해 COMMIT , ROLLBACK사용

(1)COMMIT

변경된 데이터를 테이블에 영구적 반영

자동 커밋

• 오라클 : DDL 실행하면 실행 전, 후 시점에서 자동 커밋
  • DML +DDL해서 자동 커밋 가능

쿼리
COMMIT;

(2)ROLLBACK

변경 전으로 복구

쿼리
ROLLBACK;

(3)SAVEPOINT

현 시점~ SAVEPOINT지점까지의 데이터만 변경,취소하여 다시 실행 가능

SAVEPOINT 세이브포인트명
쿼리
ROLLBACK TO 세이브포인트명 ;

3.1 정렬 ORDER BY

SELECT *
FROM 테이블
ORDER BY 기준 [ASC/DESC];;

ASC 기본 오름차순 정렬

DESC 내림차순

3.2 그룹화 GROUP BY

집계 함수를 사용하여 그룹별 계산

 GROUP BY 내 alias 사용 불가

소그룹 집계

SELECT count(*) , sum(attr1)
FROM 테이블
GROUP BY 그룹 기준;

그룹에 조건을 주어 소그룹 집계

SELECT count(*) , sum(attr1)
FROM 테이블
GROUP BY 그룹 기준
HAVING sum(attr1) > 1000;

where 과의 차이 : 실행 순서 차이

 

3.3 JOIN

중복 최소화하여 데이터간의 관계를 형성

join ~on A=B ,join ~on B=A 에서 방향 바뀌어도 상관 무.

출처: Data School

(1) INNER JOIN 

특정 조인 기준을 정하지 않으면 cross join하게 된다.

SELECT *
FROM 테이블
INNER JOIN 연결할 테이블;

특정 기준에 따라서 일치하는 값만 반환

SELECT *
FROM 테이블1
INNER JOIN 연결할 테이블2
ON 테이블2.PK=테이블1.FK ;

(2) Outer Join :  LEFT/RIGHT JOIN

왼쪽/오른쪽 테이블 기준으로 조인

SELECT *
FROM 테이블1
LEFT JOIN 연결테이블2
ON 테이블1.PK=테이블2.FK ;

SELECT *
FROM 테이블1
RIGHT JOIN 연결테이블2
ON 테이블1.PK=테이블2.FK ;

 

left ,right 조인을 동일하게 표현하게 가능하나  컬럼 순서가 상이

SELECT *
FROM 테이블1
LEFT JOIN 연결테이블2
ON 테이블1.PK=테이블2.FK ;

SELECT *
FROM 테이블1
RIGHT JOIN 연결테이블2
ON 테이블1.PK=테이블2.FK ;

on,where 차이

FROM-ON-JOIN-WHERE-GROUP BY -HAVING-SELECT - ORDER BY

2.5 서브쿼리

하나의 (메인)쿼리 안에 포한된 또 하나의 (서브)쿼리

서브쿼리는 메인쿼리의 컬럼 사용가능하지만 그 역은 성립하지 않음

서브쿼리

메인 쿼리 실행 전 한 번만 실행

서브 쿼리 중첩

() 소괄호내에 사용

서브쿼리 내에 ORDER BY사용 불가

항상 연산자 오른쪽에 작성

1) 스칼라 쿼리

SELECT절에 사용

한 행/ 컬럼만 반환

Join 사용한 것과 같은 결과

SELECT *, (SELECT 변수1
            FROM table1 as A
            WHERE A.fk=B.pk ) as 새로운 이름
FROM table1 as B

2)단일행 쿼리

 결과 1개 이하의 값을 메인 쿼리 전달

단일행 비교 연산자 : =, <>, <=,<,>,>=

3) 다중행 쿼리

서브쿼리 결과가 2개 이상 반환

다중행 연산자

• IN
  • 하나라도 만족하면 반환
• 비교연산자 ANY
  • 하나라도 만족
    • > ANY () : 최소값보다 큰 모든 건이  조건 만족
    • <ANY(): 최대값보다  큰 모든 값이 조건 만족
• 비교연산자 ALL
  • 모든 값을 만족
    • > ALL(): 최대값보다 큰 모든 값이 조건 만족
    • > ALL(): 최소값보다 큰 모든 값이 조건 만족
• EXISTS
  • 서브쿼리 결과를 만족하는 값이 1건이라도 존재하는지 여부 확인하는 조건

다중칼럼 쿼리

SELECT *
FROM 테이블1
WHERE 변수1 >(SELECT 변수1
            FROM 테이블1
            WHERE 조건변수 )

4) Inline view

FROM절에 서브 쿼리 사용해서 임시적인 동적인 뷰 생성(저장 X)

SELECT *
FROM 테이블1
WHERE 변수1 >(SELECT 변수1
            FROM 테이블1
            WHERE 조건변수 )

출처: SQL 전문가 가이드 ,  이경오의 SQL+SQLD 비밀노트