[SQLD] SQL 기본 및 활용 (관리 구문)

기초 용어 정리

DataBase :데이터베이스

    - 데이터를 일정한 체계에 따라 통합하여 디스크나 메모리에 저장한 것

    - 응용 프로그램에 종속적이지 않아 일종의 미들웨어 형태

DBMS(Database Management System) : 데이터베이스 관리 시스템

    - 데이터베이스를 구축하고 관리할 수 있는 기능을 제공하는 시스템 소프트웨어

    - 엄밀하게는 OS위에 애플리케이션과 같이 설치하여 운영(시스템 소프트웨어와 응용 소프트웨어의 중간 미들웨어)

관계형데이터 베이스 (RDB) : Relational Database

    - 데이터의 일관성 문제를 근본적으로 해결한 데이터베이스 시스템

    - 요즘은 대부분 객체관계형 데이터베이스 (Object-Relational Database)

    - Oracle, PostgreSQL, MariaDB, MySQL, SQL Server 등이 있다

    - 관계형 데이터베이스는 2차원 구조의 행(ROW)와 열(Column)으로 표현

    - 한 열을 필드(속성)이라 하고 한 행을 레코드(튜플)이라 한다

SQL (Structured Query Language)

- 구조화된 질의 언어

- 데이터베이스의 구조를 정의하고, 데이터를 조작하며, 데이터를 제어할 수 있는 절차적 + 비절차적 언어

 

SQL 구분

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

- 스키마(데이터의 구조)를 정의하는 명령어

- ERD (Entity-Relationship Diagram) 으로 그려진 데이터 모델과 상호 변환이 가능

- ex) CREATE, ALTER, DROP, RENAME, TRUNCATE

 

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

- 테이블에 데이터를 조회, 입력, 수정, 삭제하는 명령어

- ex) SELECT, INSERT, UPDATE, DELETE, MERGE

 

* TRUNCATE, DROP, DELETE의 차이

- DROP : 스키마까지 완전히 삭제  / 저장공간을 릴리즈(재사용 가능)

- TRUNCATE : 스키마는 남기고 데이터만 삭제 / 저장공간을 릴리즈(재사용 가능)

- DELETE : 테이블의 데이터를 모두 삭제 / 저장공간을 릴리즈 x (재사용 불가)

 

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

- 사용자 접근 권한과 보안, 제어 등을 다룸

- ex) GRANT, REVOKE

 

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

- 트랜잭션에 대한 제어를 다루는 명령어 (DCL의 일부로 보기도 함)

- ex) COMMIT, ROLLBACK, SAVEPOINT 

 

 

SQL의 기본

---

1. SELECT절 

SELECT 문은 데이터를 조회시에 사용

- 여러 테이블에 동일한 명의 칼럼이 있으면 " . " 을 이용하여 테이블과 붙여 표기 한다

- ex) member.ID,c client.ID  

Table에는 Alias를 부여할 수도 있다

- Alias 는 From 절에서는 그냥 쓰고, Select절에서는 AS 문법을 사용한다

-  ex) SELECT A.회원번호 AS ID

          FROM 회원  A

 

 

SELECT문의 기본 순서

SELECT : 조회
FROM : 테이블 지정
WHERE : 조건에 맞는 행
GROUP BY :그룹핑
HAVING : 필터링(그룹기준 조건)
ORDER BY : 정렬

* DUAL : Orable에서 사용할 수 있는 임시 가상테이블

- 이런 식으로 사용 SELECT 85 + NULL FROM DUAL

 

=> 실행 순서는 F W G H S O  (Fuck why 구했소)

 

산술연산자

산술 연산자는 다른 언어와 동일하게 사용

(), *, /, %, +, -      => Oracle에서는 %대신 MOD 함수를 사용

 

* Null 과의 산술 연산 결과는 Null 이다

 

 

|| : 합성 연산자

- 문자열을 연결할 때 사용

- ex) SELECT FNAME || '   ' || LNAME AS FULL_NAME

  >> FNAME 과 LNAME을 공백을 가운데에 두고 합성하여  FULL_NAME을 형성

 

 

2. WHERE 절 

- 특정 조건을 추가하는 구문

- SELECT, DELETE, UPDATE 구문에 사용할 수 있다

    - INSERT 문에는 사용 불가

- FROM 절에 사용한 Alias는 사용할 수 있지만 SELECT 절에서 사용한 Alias는 사용 불가

    - F W G H S O

 

WHERE 절의 Null 사용

    - IS NULL, IS NOT NULL 만 사영긴,ㅇ

    - 칼럼 = NULL, 칼럼 ! =NULL (제대로 동작 x)

 

 

비교 연산자

    - 단일행

        - = , < , > , <= , >=, IS NULL

 

    - 다중행
        - IN

            - 리스트 중 동일한 값이 하나라도 있으면 참

        - EXISTS

            - EXISTS 는 서브 쿼리를 대상으로 만 사용 가능

            - 서브쿼리의 결과가 한 건 이라도 있으면 참

        - ALL

            - 리스트 각각의 원소와 비교하여 모두 참 이면 참

        - ANY

            - 리스트 각각의 원소와 비교하여 하나라도 참 이면 참

 

 

부정 비교연산자

두 값이 서로 다른 지 비교할 때 사용

- 단일행 부정 비교연산자

    - NULL : IS NOT NULL

    - 나머지 : != or ^= or <>

- 다중행 부정 비교 연산자

    - NOT IN : 리스트 중 동일한 값이 하나도 없으면 참을 반환

        - ex) WHERE C1 NOT IN (10,20, 30)

 

    - NOT EXISTS : 서브쿼리의 결과가 한 건도 없으면 참을 반환

        - ex) WHERE NOT EXISTS (SELECT * FROM TBL)

 

    - ALL 과 ANY 의 경우는 바로 앞에 NOT을 붙일 수 없다 -> 전체 조건식 앞에 NOT 을 붙여서 사용

 

 

SQL연산자

- BETWEEN A AND B : A보다 크거나 같고 B 보다 작거나 같으면 참으로 반환

-  LIKE : 패턴 문자열로 문자 검색시 사용

    - % : 0개 이상의 문자

    - _ : 임의의 문자 한개

        - ex) '%M%' : 중간에 M이 들어가는 문자열 (M이 들어가는 모든 문자열)

        - ex) "_M" : M으로 끝나는 두글자 문자

 

부정 SQL 연산자

- NOT BETWEEN A AND B : A보다 작거나 B보다 크면 참을 ㅂ반환

- NOT LIKE : 패턴과 매칭되는 것이 하나도 없으면 참을 반환

 

 

논리 연산자

- AND : AND로 연결된 모든 조건이 참

- OR : OR에 연결된 모든 조건 중에 하나라도 참

- NOT : 뒤에 오는 식의 참/거짓의 반대를 반환

 

3. GROUP BY 절

- GROUP BY는 부하가 매우 높은 연산이여서 가급적 WHERE절로 조건 필터링 후 사용하는 것이 좋음

- GROUP BY는 집계함수와 함께 많이 사용

    - 집계함수 자체가 그룹을 대상으로 연산하는 다중행 함수 이기 때문

    - GROUP BY가 없으면 전체 행을 하나의 그룹으로 보고 연산을 수행

    - GROUP BY에 DISTINT를 사용하면 해당 그룹에서 첫 번째 행에 대해서만 연산 수행

    - COUNT : 입력된 칼럽에서 NULL행을 제외한 행의 개수 반환

    - SUM : 입력된 칼럼의 합

    - AVG : 입력된 칼럼의  평균
    - MIN : 입력된 칼럼의 최솟값

    - MAX : 입력된 칼럼의 최댓값

칼럼명 앞에 DISTINCT를 사용하면 칼럼의 중복 제거

 

 

 

4. HAVING 절

HAVING절은 필터링할 조건을 명시하는 구문

- WHERE 절과 달리 집계함수를 사용할 수 있다

* GROUP BY와 함께 사용할 때 GROUP BY에 의해서 그룹핑 된 이후에 HAVING절 조건을 만족하는 그룹만 추출

 

 

5. ORDER BY 절

SELECT문으로 칼럼을 조회할 때, ORDER BY절을 사용해서 정렬을 수행할 수 있다

- ORDER BY 가 없으면 조회할 때나 삽입될 때 임의의 순서로 출력되거나 입력됨 (튜플의 무순서성)

- NULL의 경우 Oracle은 최댓값으로, SQL Server는 최솟값으로 처리

    - Oracle의 경우 NULLS FIRST, NULLS LAST로 처리 방법 지정 가능

- ORDER BY는 SELECT 문에 가장 마지막에 사용되므로 집계함수와 별명(Alias)를 사용할 수 있다

 

 

6. OVER 절

OVER 절은 데이터에서 특정 순서나 그룹을 정해서 집계하거나 분석할 때 사용하는 기능

- GROUP BY와 유사하지만 아래와 같은 차이가 있다

다음과 같은 예시 상황에서 사용가능하다

SELECT 번호
  , 날짜
  , 수량
  , SUM(수량) OVER(ORDER BY 날짜) AS 재고
FROM 창고

 

여기서 만약에 일정하게 group으로 보고 싶다 하면

OVER 절은 GROUP BY를 쓰는 것이 아닌 PARTION BY 를 사용해야 한다

SELECT Continent
	   ,SUM(GNP) OVER(PARTITION BY Continent)
FROM world.country;