우울한하루

MSSQL MYSQL 차이점 비교

NULL값 비교

MSSQL : ISNULL

MYSQL : IFNULL

CASE문

MSSQL : CASE

MYSQL : IF

문자열 합치기

MSSQL : +

MYSQL : concat

MSSQL 뷰, 인덱션, 트랜잭션 정리

테이블과 뷰

1. 테이블과 뷰

* 제약조건 : 테이블 무결성을 지키기 위해 제한된 조건을 의미

즉 입력 시 특정 조건을 충족시키게 만든다.

1) PRIMARY KEY(기본키) : 중복X, NULL X

ex) UserID char(8) NOT NULL PRIMARY KEY

2) FORGIN KEY(외래키) : 두 테이블 사이의 관계 선언, 데이터 무결성 보장

다른 테이블에 의존하게 됨

기준 테이블의 열은 반드시 Primary Key이거나 Unique 제약 조건 설정

3) UNIQUE : 중복되지 않는 유일한 기술. PRIMARY와 차이, NULL 허용

(단 한개만 허용)

4) CHECK 제약 조건 : 입력되는 데이터를 점검하는 기능

5) DEFAULT는 값 입력 X, 자동 입력되는 디폴트 값

* 임시 테이블 : 임시로 잠깐 사용하는 테이블

테이블을 생성할 때 테이블 이름 앞에 #, ## 붙이면 된다.

2. 데이터베이스 캐체의 이름과 외부 서버 개체의 접근

 * 데이터베이스 개체

  테이터베이스의 정식 명칭 : 서버 이름. 데이터베이스.이름.스키마이름.개체이름

  스키마는 데이터베이스 내에 있는 개체들을 관리하기 위한 묶음

  스키마 != 소유자

  개체를 생성할 대 아무것도 붙이지 않으면 자동 디폴트 스키마인 dbo

  스키마란?

    데이터베이스 안에 있는 네임스페이스. 

3. 뷰

   * 뷰 란? 테이블과 비슷한 역활을 하도록 SELECT 문을 이용해 만드는 개체

   ex ) CREATE VIEW v_userTb1

  AS

     SELECT userid, name, addr, from UseTb1

  GO

     SELECT * FROM v_userTb1

   * 뷰의 종류? 

      표준 뷰 : 한개 또는 그 이상의 테이블을 이용해 만든 뷰.

      분할 뷰 : 여러 대의 서버에 있는 테이블들을 조인해 하나의 테이블 처럼 보이게 만든

      인덱싱 뷰 : 실제 데이터가 존재하느 뷰. 클러스터 인덱스 정렬에 의해 정렬된 결과를 보여주는 뷰.

      시스템 뷰 : 말 그대로 시스템에 접근 못하도록 만든 뷰

인덱스

 1. 인덱스의 개요와 활용

    인덱스란? 목차와 같은 것. 데이터를 좀 더 빠르게 찾을 수 있도록 해주는 도구.

                  단점은 필요없는 인덱스를 만들면 공간만 차지하고 느려짐

    장 ? 검색속도가 무척 빨라진다. (항상 그렇진 않다)

          그결과 시스템의 부하가 줄어들어, 결국 시스템 전체의 성능이 향상

    단 ? 인덱스 추가 공간 필요.

          인덱스 생성 시 시간이 소요될 수 있고 데이터 변경 작업이 자주 일어나면 성능 나빠짐

 2. 인덱스의 종류

     클러스터형 인덱스 : 순서대로 정렬. 인덱스 자체가 책 내용.

     비클러스터형 인덱스 : 찾아보기가 별도로 있음.

     클러스터 인덱스는 테이블 당 하나만 생성.

     비클러스터는 테이블 당 여러개 생성 가능.

     PRIMARY KEY 로 지정하면 자동적으로 클러스터형 인덱스

 3. 인덱스 내부동작

     B-Tree => 트리 형식을 사용함 => 그래서. 트리.노드.부모누드. 위치 등이 변경 된다면 느려짐.

 4. 인덱스 특징

    1) 클러스터 인덱스

       - 클러스터형 인덱스를 생성할 때는 데이터페이지 전체를 다시 정렬.

       - 그러므로 시스템 부하가 있을 수 있다.

       - 검색속도 빠르다. 업데이트 느림.

       - 테이블 한개에 생성 가능.

   2) 비클러스터형 인덱스

       - 그냥 페이지는 두고 인덱스 구성(목차, 찾아보기 처럼 되어있다.)

       - 찾기는 느려도, 데이터 변경은 빠름.

       - 남용하면 더 느려진다. 적절히 사용할 것.

 5. 인덱스를 생성해야 하는 경우는?

     - where 절에 사용되는 열에 인덱스를 만든다. 

     - where 절에 결과가 자주사용하는 경우에야 가치가 있다.

     - 데이터 중복도 높은 열 x

     - 외래키가 있는 열

트랜잭션과 잠금

 * 트랜잭션이란 ? 전부 되거나, 전부 안되거나. 원자성. 일관성. 격리성. 영속성

 * 트랜잭션의 문법과 종류?

   1. 자동 커밋 트랜잭션 : 각 쿼리마다 자동적으로 붙여지는 것.

   2. 명시적 트랜잭션 : 직접 BEGIN TRAN  ~ COMMIT TRAN 사용

   3. 암시적 트랜잭션 : SET IMPLICIT_TRANSACIONSON

   4. 일괄 처리 범위 트랜잭션

2) 잠금

   사용자가 데이터를 사용하면 다른 사용자가 변경 못하게 만듬.

3) 블로킹 : 잠금이 생성되어 다른 개체 접근 못함.

저장 프로시저

 저장 프로시저란 ? 프로그래밍 기능 제공.

                            쿼리 문의 집합. 일괄 처리하기 위한 용도, 모듈화

 특징?

  1. SQL 성능을 향상시킬 수 있다. > 최적화. 컴파일 내용이 캐시에 저장. 다시 가져다 쓰므로 속도가 빨라짐.

  2. 모듈식 프로그래밍 기능 => 언제든 실행 가능.

  3. 보안 강화. 프로시저 접근 권한

  4. 네트워크 전송량 줄임

MSSQL 기초 정리

mssql 책 보고 중요한 부분 정리한 것.

1. 기본적인 T-SQL 구문

  1) SELECT : 테이블 데이터 조회 할 때 사용

  2) WHERE : 조건을 줘서 결과를 조회.

  3) BETWEEN , AND : ~사이에 조건문.

      ex) Select Name, height From UserTb1 Where height Betwwen 180 AND 183

  4) ANY, ALL, SOME, 하위쿼리

     하위쿼리 ? 쿼리문안에 쿼리문이 들어있는 것

     ANY : 하위쿼리의 결과 중 하나만 만족시키면 됨.

     ALL : 하위쿼리의 결과를 모두 만족시켜야 함.

     SOME == ANY 동일한 의미

   5) ORDER BY 절 : 결과물의 출력순서

       오름차순 : ASCENDING            내림차순 : DESCENDING

   6) 중복된 데이터 한건만 보여준다.

       DISTINCT     ex) SELECT DISTINCT addr FROM userTb1

   7) Having 절 : where 과 비슷한 개념. 조건을 제한하되 집계함수에 대해서 제한

   8) JOIN : 두 개 이상의 테이블을 서로 묶어서 하나의 결과집합으로 만들어내는 것을 의미

      InnerJoin : 조건에 맞는 두개의 테이블 정보를 묶어서 보여줌

      ex ) SELECT <열목록>

            FROM <첫번째 테이블>

            INNER JOIN <두번째 테이블>

            ON <조인될 조건>

            WHERE <검색 조건>

      OuterJoin : 조건에 맞는 두개의 테이블 정보를 보여주는데, 없는 데이터를 NULL로 표시해서 보여줌(InnerJoin과 차이점)

      ex) SELECT <열목록>

           FROM <첫번째 테이블>

           ON <조인될 조건>

           WHERE <검색조건>

      CROSS JOIN : 모든 행을 조인시키는 기능

      SELF JOIN : 자체조인, 자기 자신과 조인 시킨다.

   2. 데이터베이스 생성과 관리

     1) mdf, ndf : 뎅터파일, 실제 데잍와 그행들이 저장됨

        ldf : 트랜잭션 로그파일

     2) SQL 작동 방식

        1. 사용자가 조회  OR 변경을 한다.

        2. 실제데이터

        3. 트랜잭션 로그로 남긴다.

        4. 결과를 사용자에게 보여준다.

     3) 데이터 파일

        데이터 파일(mdf or ndf) : 데이터가 순차적으로 저장되는 것x

        트랜잭션 로그파일(ldf) : 데이터가 순차적으로 저장됨.

     * SELECT addr FROM userTb1 =>실행결과

       1. 사용자는 1번처럼 논리적인 데이터베이스에 저장된 데이터를 조회

       2. 내부적으로 2번처럼 데이터 파일을 검색

       3. 4번 조회결과 응답

       select는 조회 결과가 남지 않는다.

     * Update userTb1 SET addr = '서울' where userid = 'js'

      1. 로그를 먼저 실행, 그리고 물리적인 데이터 변경

      로그파일 기록 이유 => 데이터 무결성 (성능이 감소하더라도)

    4) 트랜잭션의 개념과 작동원리

        트랜잭션 ? 하나의 논리적인 작업 단위로 수행되는 인력의 작업

        Update는 트랜잭션을 각각 하나씩 발생시킴

        Update를 세번 사용하면 3번 트랜잭션

        본래 자동 커밋모드이므로 묶어줘야 함

     5) Begin Tran 실행

        Begin Tran ~ Commit Tran

     6) 컴퓨터가 정전되었다면? 데이터가 일부로그에 남겨져 있다면 롤백. 다 있다면 작업 수행

     7) 시스템 데이터베이스와 예제 데이터베이스

        master DB : 가장 중요한 데이터베이스, 문제가 생긴다면 Server구동이 안될 수도 있다. 디스크 공간. 시스템 구성 설정 게정 정보 등이 있다.

        model DB : 사용자 DB 생성 시 기본 틀

        MS DB : 예약된 작업(백업, 복제) 등을 함

        tempDB : 임시 데이터베이스(내용이 시작할 때마다 사라짐)

     8) 데이터베이스 구조

        최소 다음 2개의 파일이 필요.

        주 데이터베이스 : 확장명 *.mdf 실제 데이터가 저장되는 곳

        트랜잭션 로그파일 : 확장명 .ldf , 복구에 사용

    9) 파일 그룹의 개념 : 물리적으로 독립되어야 의미가 있다. 같은 하드디스크에서 읽고 쓰면 느려진다.

MSSQL JOIN 정리

조인이란 두개 이상의 테이블을 조건에 맞게 합쳐서 새로운 테이블을 보여주는 것이다.

여러가지 join 방법이 있겠지만 INNER JOIN과 OUTER JOIN만 정리하였다.

사용법

SELECT A별칭.<행1>, B별칭.<행2>... FROM A A별칭 INNER JOIN B B별칭 ON 조건 

일단 테이블은 CharacterLv, CharacterItem 테이블이 있다.

INNER JOIN이란?

보여주겠다고 한 행과, 조건에 알맞은 데이터가 있을 시에 포함되는 것 만 보여주는 것이다.

예를 들어

CharacterIndex, Lv, ItemIndex, ItemCount 행을 보여주면서 조건에 맞는 값만 보여줍니다.

마치 AND 같습니다.

(조건 값<CharacterIndex>이 맞다면 여러개 보여질 수 있습니다.)

<INNER JOIN 결과>

LEFT OUTER JOIN 이란?

LEFT OUTER JOIN 은 조건에 맞는 값만 보여주지만 조왼쪽에 있는 테이블은 전부 보여줍니다.

이 때 오른쪽 테이블에 해당하는 값이 없다면 null로 표기해줍니다.

그래서 어떤 테이블을 왼쪽에 놓는냐에 따라서 결과 값이 다릅니다.

<LEFT OUTER JOIN 결과>

위와 달리 CharacterItem 테이블을 왼쪽에 놓았을 경우.

CharacterItem 테이블 값이 전부 나옵니다.