21. Server (3 - DB Server mariadb)

0. DB Server
  - Database server; 데이터베이스를 모아놓은 서버
  - 스마트폰의 개발 및 보급에 다라 인터넷의 활용이 급속도로 늘어나면서 무수히 많은 데이터 생성 
     > 많은 데이터들을 효율적으로 저장하고 관리하기 위해 database 사용
  - CentOS 6버전까지는 mysql이라는 DB server를 무료로 사용했으나
     mysql이 유료화되면서 CentOS 7버전부터 mysql의 마이너 카피인 mariadb 사용

  - 정보
    패키지 : mariadb-*
    데몬 : mariadb
    방화벽 : mysql
    설정파일 : /etc/my.cnf (DB server 동작에 대한 설정)
        > DB Server 내부 mysql이라는 database에서 DB server 내부 설정 제어

 

  - 설치
    yum -y install mariadb-*
    systemctl restart mariadb

  - 접속
    mysql -u <DB 계정> -p [<database 이름>]

    mysql -u root -p mysql

  - 용어
    SQL문 : DB Server에서 사용하는 명령어
    database: table들의 집합체
    table : 실제 데이터가 들어있는 최소 단위, field와 value로 구성
    field : table에 저장되는 데이터들의 종류 (카테고리), column이라고도 부른다
    value : field에 맞춰서 table에 저장되는 데이터

  1. SQL 기본 명령어

    MariaDB [mysql]> system clear;    : clear 역할  == 단축기 ctrl + L
    MariaDB [mysql]> select version(), current_date, current_time;
+---------------------+------------------+-----------------+
| version()            | current_date | current_time |
+---------------------+------------------+-----------------+
| 5.5.68-MariaDB | 2022-06-27   | 11:33:38       |
+---------------------+------------------+-----------------+
    ;로 문장을 마무리해야 실행됨
    중간에 공백에서 엔터로 줄넘김을 하더라도 ;이 나오기 전까지는

    문장이 실행되지 않기 때문에 적절하게 줄바꿈을 하면서 입력 할 수 있다

  * show
    show databases; : 데이터베이스들의 목록 출력
    show tables; : 현재 내가 사용 중인 데이터베이스의 테이블들 목록 출력
       > table중 user 테이블은 db서버 사용자 계정 및 권한을 관리하는 테이블
       > table중 db 테이블은 특정 database의 권한을 지정해 줌

  * create database <db 이름> : db 생성
    create database dbitbank;

  * use <db이름> : 사용하고자 하는 db로 이동

   MariaDB [dbitbank]> create table tbitbank (num int(3), name char(20));
   ()안에 숫자는 3byte라는 뜻 (0~999)  20byte 19글자 
   name type(size)  disk에 저장하는 것 / 다른 프로그래밍 RAM에 데이터를 저장하는 것과 다르다

   MariaDB [dbitbank]> show tables;
+--------------------------+
| Tables_in_dbitbank |
+--------------------------+
| tbitbank                   |
+--------------------------+

    MariaDB [dbitbank]> desc tbitbank; : 테이블 상세보기
+---------+-----------+--------+------+-----------+-------+
| Field    | Type     | Null    | Key | Default  | Extra |
+---------+-----------+--------+------+-----------+-------+
| num    | int(3)      | YES  |         | NULL    |          |
| name  | char(20) | YES  |         | NULL    |          |
+---------+-----------+--------+------+-----------+-------+

    Null - Yes/No : filed에서 null값을 허용하는지 여부
    Key     : PRI(primary key) 중복값을 허용하지 않게 만든다
    Defualt     : 생략하면 NULL 값이 들어간다.

 

  * drop 
    MariaDB [dbitbank]> drop table tbitbank; : table 삭제
    MariaDB [dbitbank]> drop database dbitbank; : database 삭제
     (사용중이어도 삭제가능)

  * select [필드명] from [테이블명]

    MariaDB [mysql]> desc user;
    Host에서 접속하는 User의 비밀번호는 Password다
    user 테이블의 sql문_priv 모두 권한 관련 된 것

    MariaDB [mysql]> select host, user, password from user;
+-------------+------+--------------+
| host         | user | password |
+-------------+------+--------------+
| localhost  | root  |                 |
| server      | root  |                 |
| 127.0.0.1 | root  |                 |
| ::1            | root  |                 |
| localhost  |         |                 |
| server      |         |                 |
+-------------+------+--------------+

  * delete from [테이블명] where [조건]
    MariaDB [mysql]> delete from user where user=' ';

  * update [테이블명] set [field]=[바꿀데이터] where [조건]
    MariaDB [mysql]> update user set password=password('itbank') where user='root';
+-------------+------+------------------------------------------------------------------------+
| host         | user | password                                                                       |
+-------------+------+------------------------------------------------------------------------+
| localhost  | root | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
| server      | root | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
| 127.0.0.1 | root | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
| ::1            | root | *E1B74369B47EB3AD8EF858144E1A0364E3259329 |
+------------+-------+-------------------------------------------------------------------------+

    set [field]=[데이터] = 앞뒤로 띄어쓰지 않는다
    password()함수 : () 값을 암호화함

  * insert into [테이블](columns) values (값) columns, 값 개수 동일
    MariaDB [mysql]> insert into user(host, user, password) 
                                 values ('localhost', 'useritbank', password('itbank'));

    MariaDB [mysql]> delete from db;
    MariaDB [mysql]> select host, db, user from db;
                                 Empty set (0.00 sec)
    MariaDB [mysql]> insert into db values ('localhost', 'dbitbank', 'useritbank',
                             →  'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y',
                             →  'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y');        SQL문 권한 y가 19개

     MariaDB [mysql]> exit
     Bye
     [root@server ~]# systemctl restart mariadb : 데몬 재실행해야 변경된 것 적용

     새롭게 만든 useritbank로 로그인
     [root@server ~]# mysql -u useritbank -p

     MariaDB [(none)]> show databases;
+----------------------------+
| Database                   |
+----------------------------+
| information_schema  |
| dbitbank                     |
+----------------------------+
     허용된 권한에서만 볼 수 있다.

2. [실습] DB server 

 

DB server 내부 itbank 관련된 모든 내용 삭제 후 진행
D/B : dbtest
user : usertest
root로 로그인 하여 진행

---

usertest로 로그인하여 진행
table : tbtest
field : num, name, addr, phone
values : 3개 이상

* 확인은 show databases, select * from tbtest 결과를 한 화면에 띄워서 

---

Step1. 기존 db 지우고 새로운 db 생성
   MariaDB [mysql]> drop databaes dbitbank;
   MariaDB [mysql]> create database dbtest;

Step2. 기존 user 정보 지우고 새로운 user 정보 생성
   MariaDB [mysql]> select user from user
   MariaDB [mysql]> delete from user where user='useritbank';
   MariaDB [mysql]> insert into user(host, user, password) 
                                 values ('localhost', 'usertest', password('itbank'));

Step3. 기존 db 정보 지우고 새로 생성한 db 정보 삽입
   MariaDB [mysql]> delete from db;
   MariaDB [mysql]> insert into db values ('localhost', 'dbtest', 'usertest',
                           → 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y',
                           → 'y', 'y', 'y', 'y', 'y', 'y', 'y');
   MariaDB [mysql]> exit
   [root@server ~]# systemctl restart mariadb

Step4. 새로운 유저로 로그인
   [root@server ~]# mysql -u usertest -p
   Enter password:
   MariaDB [(none)]> show databases;
+---------------------------+
| Database                  |
+---------------------------+
| information_schema |
| dbtest                        |
+---------------------------+
   MariaDB [(none)]> use dbtest
   Database changed

Step5. 테이블 생성
   MariaDB [dbtest]> create table tbtest (num int(3), name char(20), addr char(64), phone int(12));
   MariaDB [dbtest]> desc tbtest;
+---------+------------+-------+------+-----------+--------+
| Field   | Type       | Null   | Key | Default  | Extra |
+---------+------------+-------+------+-----------+--------+
| num    | int(3)      | YES  |         | NULL    |          |
| name  | char(20) | YES  |         | NULL    |          |
| addr    | char(64) | YES  |         | NULL    |          |
| phone | int(12)    | YES  |         | NULL    |          |
+---------+------------+-------+------+-----------+--------+

Step6. 테이블에 값 삽입
   MariaDB [dbtest]> insert into tbtest values(1, 'a', 'aaa', 01012341234);
   MariaDB [dbtest]> insert into tbtest values(2, 'b', 'bbb', 01032123454);
   MariaDB [dbtest]> insert into tbtest values(3, 'c', 'ccc', 01024523544);

   ( ) 하나가 value 이므로 ( ), (  ), (  ) 여러 개 한번에 삽입 가능
   한글은 데이터가 ???로 나온다.
   int형으로 데이터 넣을 시 앞에 0은 생략된다, -이후(전화번호 같은 것 쓸 때)는 나오지 않는다

Step7. 실습 내용 확인
   MariaDB [dbtest]> show databases;
+----------------------------+
| Database                   |
+----------------------------+
| information_schema  |
| dbtest                         |
+----------------------------+
MariaDB [dbtest]> select * from tbtest;
+-------+--------+--------+-----------------+
| num  | name | addr  | phone           |
+-------+--------+--------+-----------------+
|    1    | a        | aaa   | 1012341234 |
|    2    | b        | bbb   | 1032123454 |
|    3    | c        | ccc    | 1024523544 |
+------+---------+--------+-----------------+

3. alter문으로 table 내부의 field 제어

  1) field의 데이터 type 변경
     alter table [테이블명] modify [필드 명] [바꿀 데이터 타입] 
     MariaDB [dbtest]> alter table tbtest modify phone char(14);
     이렇게 해도 데이터가 자동으로 바뀌지 않아서 데이터도 다시 입력해주어야 함.
     MariaDB [dbtest]> update tbtest set phone='010-1234-1234' where num=1;

  2) field 추가
     alter table [테이블명] add [추가할 필드명] [추가할 field의 type] after [기존 필드명]
    MariaDB [dbtest]> alter table tbtest add old int(3) after name;
    after를 사용하지 않으면 자동으로 맨뒤에 추가 after를 사용하면 기존 field 뒤쪽에 추가

  3) field 명 수정
    alter table [테이블명] change [기존 필드명] [바꿀 필드명] [자료형 - 기존 or 바꿀 것]
    MariaDB [dbtest]> alter table tbtest change old age int(3);

  4) field 삭제
    alter table [테이블명] drop [삭제할 필드명]
    MariaDB [dbtest]> alter table tbtest drop addr;

4. mariadb에서 한글 데이터를 입력 할 수 있도록 설정 변경하는 법

    MariaDB [mysql]> show variables like 'c%'; : c로 시작하는 모든 파일 % = * 역할

+----------------------------------+-------------------------------------+
| Variable_name                  | Value                                   |
+----------------------------------+-------------------------------------+
| character_set_client          | utf8                                      |
| character_set_connection | utf8                                      |
| character_set_database    | latin1                                   |
| character_set_filesystem   | binary                                  |
| character_set_results        | utf8                                      |
| character_set_server         | latin1                                   |
| character_set_system        | utf8                                     |
| character_sets_dir              | /usr/share/mysql/charsets/ |
| collation_connection           | utf8_general_ci                  |
| collation_database             | latin1_swedish_ci               |
| collation_server                  | latin1_swedish_ci               |
| completion_type                 | NO_CHAIN                         |
| concurrent_insert               | AUTO                                  |
| connect_timeout                | 10                                        |
+----------------------------------+-------------------------------------+

    latin1을 utf8로 바꿔줄 것 이를 위해

    [root@server ~]# vi /etc/my.cnf
    1 [mysqld]
    10 character-set-server = utf8
    11 collation-server = utf8_general_ci
     mysqld 영역에 위의 두 줄 구문 추가 저장 후 나간다
    [root@server ~]# systemctl restart mariadb

     MariaDB [mysql]> show variables like 'c%';
+----------------------------------+-------------------------------------+
| Variable_name                  | Value                                   |
+----------------------------------+-------------------------------------+
| character_set_client          | utf8                                      |
| character_set_connection | utf8                                      |
| character_set_database    | utf8                                      |
| character_set_filesystem   | binary                                  |
| character_set_results        | utf8                                      |
| character_set_server         | utf8                                      |
| character_set_system        | utf8                                     |
| character_sets_dir              | /usr/share/mysql/charsets/ |
| collation_connection           | utf8_general_ci                  |
| collation_database             | latin1_swedish_ci               |
| collation_server                  | latin1_swedish_ci               |
| completion_type                 | NO_CHAIN                         |
| concurrent_insert               | AUTO                                  |
| connect_timeout                | 10                                        |
+----------------------------------+-------------------------------------+

    변경된 것을 확인 가능
    이제부터 만든 database는 한글을 지원, 기존 database는 한글 사용하지 못함

    MariaDB [dbtest]> status; :db 전체 정보 출력

     Db     characterset:    latin1

     alter를 이용하여 database 설정 변경
    MariaDB [dbtest]> alter database dbtest default character set utf8;
    MariaDB [dbtest]> commit;

    이제부터 database가 만든 table은 한글을 지원, 기존 table은 여전히 한글을 사용하지 못함

    MariaDB [dbtest]> alter table tbtest modify name char(20) char set utf8;
    table에 한글을 사용할 field에 설정 변경

    이미 입력된 데이터는 여전히 ???로 나오지만 새로 업데이트하는 데이터들은 한글 지원 가능

    MariaDB [dbtest]> update tbtest set name='이성당' where num=1;

+-------+-------------+-------+---------------------+
| num  | name      | age    | phone                |
+-------+-------------+-------+---------------------+
|    1    | 이성당    |   11    | 010-1111-1111   |
|    2    | b             |   18    | 010-3212-3454 |
|    3    | c             | NULL | 010-2452-3544 |
+-------+-------------+-------+----------------------+