SQL

[Structured Query Language]

여러 DMBS의 DATA를 관리하기 위한 공통적인 Language

DBMS [Data Base Management System]

Persistence Data 즉, 비 휘발성 메모리 (HDD, USB)등의 데이터를 Java I/O를 통해 File에 Data를 통해 간단한 처리하는 것은 간단하지만, 대량의 Data 복잡한 Data들은 처리하기에 피곤할 뿐더러, FIle System으로 Data를 관리할 때 예를 들어 수십 명의 사용자가 동시에 회원 가입을 한다면 동기화 문제가 발생한다.

⇒ 이러한 정보를 관리해주는 시스템이다.

이번 과정에서는 DBMS의 공통적이고 표준화 된 사용법인 SQL에 대해서만 학습할 예정이다.

RDBMS [Relational DBMS]

하나의 Table에서 Data를 관리하기에는 모든 정보를 일일히 관리해 주어야 한다는 번거로움이 있어, 기존 Table이 참조하는 새로운 Table을 key와 value로 생성하여 기존 Table에서 key값을 참조하는 관계를 가지고 있는 DBMS이다. 가장 효율적이고 많이 사용하고 있는 DBMS의 한 종류이다.

RDBMS에서 Data 관리 단위를 Table이라고 하며 Table은 metrics 구조(excel 시트 생각)이다.

Table의 가로를 row/record라고 명칭하고, 세로를 column이라고 명칭한다.

row/record 와 column이 교차하는 지점들을 cell/field라고 명칭한다.

ERD [Entity Relational Diagram]

우리가 UML을 통한 Object Modeling 하여 만든 것을 Class Diagram이라고 명칭 하는 것처럼 Table들의 관계를 Modeling 한 것을 현재 수준에서는 Entity(=Table) Relational Diagram이라고 명칭 한다.

DDL [Data Definition Language]

⇒ Data 정의어, DB를 정의하는 언어이며, 생성, 수정, 삭제하는 등의 데이터의 전체의 골격을 결정하는 역할을 하는 언어 입니다.

CREATE TABLE UserTable
(
    UserID    CHAR(8)      NOT NULL   CONSTRAINT PK_UserID PRIMARY KEY,
	  Email     NVARCHAR(50) NOT NULL   CONSTRAINT UK_UserTable_Email UNIQUE,
		Number    INT          NOT NULL   CONSTRAINT CK_VALUE CHECK 
                                      (Number >= 1 AND Number <= 10),
		BirthYear INT          NOT NULL   DEFAULT YEAR(GETDATE()),
    Addr      NCHAR(2)     NOT NULL   DEFAULT N'서울',
    Height    SMALLINT     NOT NULL   DEFAULT 172
);

CONSTRAINT => 
"PK__UserTable__3214EC27060DEAE8" 등과 같이 알아볼 수 없는 이름을 가지게 된다.
그렇기 때문에 CONSTRAINT를 이용하여 Naming을 해준다.

DATA TYPE (일부분)

• CHAR(n) [1~4G] 고정 길이 문자 데이터
  
• VARCHAR2(n) [1~2G] 가변 길이 문자 데이터
  
• LONG [1~4G] 가변 길이 문자 데이터
  
• CLOB 단일 바이트 가변 길이 문자 데이터
  
• DATE
• BFILE 가변 길이 외부 파일에 저장된 이진 데이터
  
• NUMBER(p,s) 전체 p자리 중 소수점 이하 s자리
  
• RAW(n) n BYTE의 원시 이진 데이터
  
• LONG RAW 가변 길이 원시 이진 데이터
  
• BLOB 가변 길이 이진 데이터
  

무결성 ⇒ 제약조건

└ 결함이 없는

└ **관계의 무결성** 

1. UNIQUE라는 제약 조건

   • 중복 INSERT시 ERROR

2. NOT NULL 제약 조건

   • NULL이 들어가면 참조할 수 없기 때문에

개체의 무결성
FOREIGN KEY
- 참조하고 있는 Data만 받게 해준다.

CHECK( )
- 사용자 정의 제약 조건

• Primary key 제약 조건 테이블에 존재하는 많은 행의 데이터를 구분할 수 있는 식별자 기본 키에 입력되는 값은 중복될 수 없으며, NULL 값이 입력될 수 없다.
  
• Foreign key 제약 조건 외래 키 테이블이 참조하는 기준 테이블의 열은 반드시 PK이거나 UNIQUE 제약 조건이 설정 두 테이블 사이의 관계를 선언함으로써, 데이터의 무결성을 보장해 주는 역할
  
• UNIQUE 제약 조건 중복되지 않는 유일함을 부여하고 싶을 때 사용하는 제약 조건

  💡 Primary Key 와의 차이점

  • UNIQUE는 유일하게 하나의 개체에 대해서 NULL을 허용한다.
  • PK는 테이블에 하나만 존재 가능하나, UNIQUE 제약 조건은 여러 개 설정이 가능하다.
  • PK는 자동으로 클러스터 형 인덱스가 된다.

• CHECK 제약 조건 특정 조건들로 이루어진 수식을 통해 입력되는 데이터를 검증
  
• DEFAULT 정의 DEFAULT는 값을 입력하지 않았을 때, 자동으로 입력되는 기본 값을 정의
  

💡 SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 정의하거나 변경 또는 삭제할 때 사용하는 언어
데이터 베이스 관리자나 데이터베이스 설계자가 사용

DML [Data Manipulation Language]

Data 조작어 정의된 DB에 입력된 레코드를 조회하거나 수정하거나 삭제하는 등의 역할을 하는 언어를 말합니다.

INSERT INTO t/n VALUES();
=>COLUMN 순으로 저장
	문자열: '문자열' 작은따옴표만 사용!!
  날짜 : '2019-12-30'
  ex) 회원가입, 게시판 글쓰기

UPDATE t/n SET c/n = 수정할 값 WHERE 조건;
	--조건을 만족하는 값을 수정

DELETE FROM t/n WHERE 조건;
  --조건을 만족하는 값을 삭제

💡 DB 사용자가 응용 프로그램이나 질의어를 통하여 저장된 데이터를 실질적으로 처리하는데 사용하는 언어 이다.
DB 사용자와 DB 관리 시스템 간의 인터페이스를 제공한다.

QUERY [SELECT]

• SELECT

  c/n [, c/n]

  FROM t/n [, t/n]

  [WHERE 조건]

  [GROUP BY c/n [, c/n]]

  [HAVING 조건]

  [ORDER BY c/n [, c/n]]

  💡 WHERE 조건에 들어가는 Keyword

  1. 논리 연산자 AND, OR, NOT
  2. 논리 비교 연산자 <, >, <=, >=, =, <>
  3. SQL 연산자
     • BETWEEN A AND B
     • IN( value [,value] )
     • LIKE '문자'
       ex) LIKE '김%' ⇒ (0~N)
       LIKE '김_' ⇒ (1)
     • IS NULL, IS NOT NULL
       NULL의 사칙 연산 NULL
       NULL의 비교는 FALSE

JOIN

EquiJoin

• 가장 일반적으로 사용하는 Equality Condition(=)에 의한 조인이다
• Equi join의 성능을 높이기 위해서는 Index 기능을 사용하는 것이 좋다.

내가 한개의 테이블의 열의 정보를 알고있고 그것에 대한 다른 테이블의 정보를 알고 싶을때

SELECT e.empno, e.ename, d.dname
FROM dept d, emp e  //AS(ALIAS) d,e
WHERE d.deptno = e.deptno;

(,) 대신 INNER JOIN을 사용 할 수 있으며, INNER는 생략 가능하다. Join 조건은 ON 절에 온다.
-- INNER JOIN절을 이용하여 조인하는 예제
SELECT e.empno, e.ename, d.dname
FROM dept d 
INNER JOIN emp e
ON d.deptno = e.deptno;

NATURAL JOIN을 사용 하면 동일한 컬럼을 내부적으로 모두조인 하므로, ON절이 생략 가능하다.
-- NATURAL JOIN절을 이용하여 조인하는 예제
SELECT  e.empno, e.ename, d.dname
FROM  dept d 
NATURAL JOIN emp e;

NATURAL JOIN의 단점은 동일한 이름을 가지는 칼럼은 모두 조인이 되는데, 
USING 문을 사용하면 컬럼을 선택해서 조인을 할 수가 있다
-- JOIN~USING절을 이용하여 조인하는 예제
SELECT e.empno, e.ename, deptno 
FROM emp e 
JOIN dept d 
USING (deptno);

Non-Equi Join

• 테이블의 어떤 column도 Join할 테이블의 column에 일치하지 않을 때 사용하고, 조인조건은 동등( = )이외의 연산자를 갖는다.
• BETWEEN AND, IS NULL, IS NOT NULL, IN, NOT IN
• >, >=, <, <=, <>
• 거의 사용하지 않는다

-- emp 테이블과 salgrade 테이블의 Non-Equi Join 예제
SELECT e.ename,e.sal,s.grade
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;

Self Join

• Equi Join과 같으나 하나의 테이블에서 조인이 일어나는 것이 다르다.
• 같은 테이블에 대해 두 개의 alias를 사용하여 FROM절에 두 개의 테이블을 사용하는 것 처럼 조인한다.

-- 사원의 매니저명을 조회하는 Self Join 예제
SELECT e.ename, a.ename "Manager"
FROM emp e, emp a
WHERE e.empno = a.mgr;

Outer join

: Equi Join은 조인을 생성하려는 두 개의 테이블의 한쪽 컬럼에서 값이 없다면 데이터를 반환하지

못한다.

동일 조건에서 조인 조건을 만족하는 값이 없는 행들을 조회하기 위해 Outer Join을 사용 한다.

• LEFT OUTER JOIN 왼쪽 에 있는 테이블의 모든 결과를 가져온 후 오른쪽 테이블의 데이터를 매칭하고, 매칭되는 데이터가 없는 경우 NULL을 표시한다.

  SELECT *
  FROM EMPLOYEE E LEFT OUTER JOIN DEPARTMENT D 
  ON E.DEPARTMENTID = D.DEPARTMENTID;
  
  <ORACLE>
  SELECT *
  FROM EMPLOYEE E, DEPARTMENT D
  WHERE E.DepartmentID = D.DepartmentID(+);

• RIGHT OUTER JOIN 오른쪽에 있는 테이블의 모든 결과를 가져온 후 왼쪽의 테이블의 데이터를 매칭하고, 매칭되는 데이터가 없는 경우 NULL을 표시한다

  SELECT *
  FROM EMPLOYEE E RIGHT OUTER JOIN DEPARTMENT D
  ON E.DepartmentID = D.DepartmentID;
  
  <ORACLE>
  SELECT *
  FROM EMPLOYEE E , DEPARTMENT D
  WHERE E.DepartmentID(+) = D.DepartmentID;

• FULL OUTER JOIN
  

Full Outer Join은 LEFT OUTER JOIN과 RIGHT OUTER JOIN을 합친 것이다.

양쪽 모두 조건이 일치하지 않는 것들까지 모두 결합하여 출력한다.

SELECT *
FROM EMPLOYEE E FULL OUTER JOIN DEPARTMENT D
ON E.DepartmentID = D.DepartmentID;

MySql에서는 Full Outer Join 키워드가안되므로 이런식으로 해준다.(물론 방법은 다양하지만..)
SELECT *
FROM EMPLOYEE E LEFT OUTER JOIN department D
ON E.DepartmentID = D.DepartmentID
UNION
SELECT *
FROM EMPLOYEE E RIGHT OUTER JOIN DEPARTMENT D
ON E.DEPARTMENTID = D.DEPARTMENTID;

DCL [Data Control Language]

Data 제어어, DB에 접근하거나 객체에 권한을 주는 등의 역할을 하는 언어를 입니다.

TCL [Transaction Control Language]

COMMIT, ROLLBACK

DUAL

존재하지 않는 가상의 TABLE에서 값을 출력해 준다.

SELECT SYSDATE FROM DUAL

FUNCTIONS (일부분)

1. Single Row Function

   1. 문자형

      CONCAT(a,b) ⇒ ab

      SUBSTR(abcd, 2, 3) ⇒ bcd

      LENGTH(abcd) ⇒ 4

   2. 숫자형

      ROUND( ) ⇒ 반올림
      TRUNC( ) ⇒ 버림
      MOD( ) ⇒ 나머지

   3. 날짜형

      MONTH_BETWEEN(a,b) ⇒ a와 B의 차이 return

   4. 변환형 (casting)

      문자로 변환 TO_CHAR( )

      숫자로 변환 TO_NUMBER( )

      날짜로 변환 TO_DATE( )

NVL(NULL, VALUE)

⇒ NULL이면 VALUE값으로 변환

NVL2(NULL, VALUE1, VALUE2)

⇒ NULL이면 VALUE2 아니면 VALUE1

2. Multi Row Function
   SUM( ), AVG( ), MAX( ), MIN( ), ...

💡 어떻게 현재 위치를 알고 해당하는 나라에 대한 날짜, 시간, 원화 등의 정보를 알아올까?
⇒ OS가 알고 있는 Locate Info를 DBMS 설치 시 가져오기 때문이다.