데이터베이스 기초 – MySQL 기본 기능

본격적인 Database 실습을 하기 전에 MySQL 의 기본 기능을 짚고 넘어 가자.
MySQL 기본 기능

Database 의 이론에 대한 내용은 앞에서 다 했다.

그리고 실습을 하기 이전에 설치를 해야한다. : MySQL 설치


MySQL Query 실행 구조
mysql architecture

기초

  1. 표준 SQL의 확장
  2. Script
    1. 일련의 SQL 문들 – 저장 후 추후에 실행 가능함
    2. 다양한 함수들을 제공.
      1. https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html
        Select version();
        Select user();
        Select Rand();

    3. 변수 : 변수는 선언 없이 사용 가능
      Set @test1 = version();
      Set @test2 = Rand();
      Select @test1, @test2;
      

      MySQL 변수

String 연산, 산술 연산

  1. String 관련 다양한 함수들이 존재
    1. Concat, Length, Left, Right, Trim ….
      # MySQL Version을 받아오는 변수 선언
      Set @test1 = version();
      
      #Concat은 문자열을 이어주는 함수
      Select Concat('MySQL Version: ', @test1, ' – MySQL for beginners');
      
      #Length는 문자열의 길이를 가져옴
      Select @test1, Length(@test1);
      
      #나머지는 Reference 참조

      mysql concat

    2. 산술 기본 연산자
      +, -, *, /, %, DIV
    3. SET @a = 12;
      SET @b = 5;
      SELECT @a - @b AS 'Subtraction Result',
             @a * @b AS 'Multiplication Result',
             @a / @b AS 'Division Result',
             @a % @b as 'Remainder Result',
             @a div @b as 'Quotient Result';

      산술연산자

Comment

  1. One Line Comments  : #, --
  2. Multiline Comments  : /* Comment 내용  */
    SET @a = 12;
    SET @b = 5;
    SELECT @a - @b AS 'Subtraction Result',
     /*          @a * @b AS 'Multiplication Result',
                 @a / @b AS 'Division Result',
    */
    #            @a % @b as 'Remainder Result',
                 @a div @b as 'Quotient Result';
    

    MySQL Comment

Dates, Times

  1. Now(), Current_Timestamp()
  2. SELECT Current_Timestamp() as '현재 날짜 및 시간';
  3. Current_Time(), Current_Date()
  4. ADDDATE('2008-01-02', 31),
  5. ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');
    Dates, Times

MySQL의 시스템 정보

  1. 데이터베이스 생성
    1. Create Database <DB 명> ; Create Schema <DB 명> ;
    2. MySQL/MariaDB에서는 동일한 의미로 사용.
      Create Database, Schema
  2. 주요 객체 생성 관련 정보
    1. 시스템 관련 변수(@@변수명)들을 제시.
      1. 필요 시 수정 가능. 선택 후 마우스 우측 버튼 클릭.
    2. @@DataDir : 데이터베이스들이 저장된 폴더의 경로를 의미
    3. @@BaseDir : MySQL/MariDB 시스템이 설치된 폴더의 경로를 의미
    4. SELECT @@BaseDir, @@DataDir;
      @@datadir과 @@basedir
      @@datadir 실제 경로
      @@basedir 실제 경로
  3. My.INI 파일
    1. @@DataDir 의 경로에 위치
    2. Instance setting 정보: client section, server section 등 포함
  4. DB.Opt 파일
    1. 데이터베이스 생성 시 생성
    2. 데이터베이스의 option values들이 저장
    3. 많은 경우 default 값이 사용됨.

Storage Engines

  1. 대표적인 엔진: INNODB, MyISAM
    1. Storage engine: hidden layer for data reading and writing in DBMS
  2. MyISAM
    1. Default engine until version 5.4
      1. Datadir에 FRM(테이블 정의), MYD(데이터), MYI(인덱스) 파일들 생성:
    2. Read가 중요한 Application (예: internet app에 사용) 에 적합.
    3. Not ACID compliant.
  3. INNODB
    1. Fully ACID compliant, default engine since version 5.5.
      1. Datadir에 FRM(정의), IBD(데이터) 생성.
  4. mysql db : MyISAM과 INNODB가 사용

* select * from information_schema.tables where TABLE_SCHEMA = 'mysql';
select 결과

Types

  1. Integer types
    1. TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
    2. 각각 1, 2, 3, 4, 8 bytes의 크기.
    3. Display width 지정 가능. INT(4)  4자리보다 작은 수일 경우 공백 left padding
  2. DECIMAL
    1. 고정 소수점 기반, monetary data에 주로 사용
    2. 예) DECIMAL(5, 2) 소수점 이하 2자리인 총 5자리 수. 즉 -999.99 ~ 999.99범위
  3. FLOAT, DOUBLE
  4. Date, Datetime, Timestamp
  5. String ty
    1. CHAR, VARCHAR, BINARY, VARBINARY
    2. BLOB, TEXT for large volume of data,
    3. ENUM – 예) 참조: https://dev.mysql.com/doc/refman/5.7/en/enum.html
    4. SET – 예) 참조: https://dev.mysql.com/doc/refman/5.7/en/set.html

MySQL의 Transaction 맛보기

  1. 트랜잭션의 표현
    1. 시작 : START TRANSACTION;
    2. 종료
      1. COMMIT;
      2. ROLLBACK;
        CREATE TABLE t1 (a  int) engine =innodb;
        START TRANSACTION;   		#시작
           INSERT INTO t1 VALUES( 100 );  	# 일련의 명령문들
           INSERT INTO t1 VALUES( 200 );
           SELECT * FROM t1;
        COMMIT;				#성공적인 종료
        

        Transactions 1

        START TRANSACTION;   		#시작
           INSERT INTO t1 VALUES( 300 );  	# 일련의 명령문들
           INSERT INTO t1 VALUES( 400 );
           SELECT * FROM t1;
        ROLLBACK;			#작업을 취소시킴
        SELECT * FROM t1;
        

        Transactions 2

  2. 부분 처리 방지(ALL or NOTHING)의 예시
    1. Command Line Client도 MySQL 엔진에 대해서는 application임
    2. 다음 시나리오대로 진행
    3. Command Line Client에서 다음 내용을 진행
      CREATE TABLE t1 (a  int) engine =innodb;
      
      START TRANSACTION;   	
         INSERT INTO t1 VALUES( 100 );  	
         INSERT INTO t1 VALUES( 200 );
         SELECT * FROM t1;   	      
      
    4. 위의 내용 실행 후 비정상 종료를 가정해서 CLC를 닫음.
    5. 다시 CLC를 실행해서 t1 조회를 해 볼 것.
  3. MyISAM과 InnoDB의 차이
    1. 독립적인 각각의 SQL문은 transaction으로 간주됨.
    2. Command Line Client에서 다음 내용을 진행
      CREATE TABLE t1 (a  int, primary key(a)) engine = innodb;
      INSERT INTO t1 values(3);
      INSERT INTO t1 values (1), (2), (3);
      SELECT * FROM t1;

      innodb

      CREATE TABLE t1 (a  int, primary key(a)) engine =myisam;
      INSERT INTO t1 values(3);
      INSERT INTO t1 values (1), (2), (3);
      SELECT * FROM t1;

      myisam

    3. Engine을 innodb로 수정한 후 다시 실행 후 차이점 확인.

주요 참고 자료