2020-10-21 TIL

MariaDB, MySQL

DBMS 접속 방식

• 원격(인터넷)을 이용해서 데이터 서버에 루트로 직접 접속하는 것은 불가능하다.
• 루트 암호를 알면 데이터 서버에 접속이 가능해서 데이터가 노출될 수 있다.
• 이런 것을 막기 위해서 vpn(가상 프로토콜)을 연결한다.
  • 접속 가능한 아이피 어드레스를 지정한다.
    • 특정 사람만 데이터 서버에 접속하도록 한다.
      • 루트 암호는 차단시킨다.
• 이론적으로 원격 루트 접속이 가능하게 하려면 등록을 해놔야 한다.
• 하지만 실무에서는 이것조차 허락하지 않는다.
  • 데이터 서버에 어플리케이션 서버(서버 앱)을 설치하고 local PC에서 어플리케이션 서버로 접속 요청을 한다.
    • 그러면 어플리케이션 서버가 데이터 베이스에 접속을 해서 요청을 실행하고 결과를 리턴한다.

운영체제

• 유닉스

• https://upload.wikimedia.org/wikipedia/commons/thumb/7/77/Unix_history-simple.svg/1920px-Unix_history-simple.svg.png

• service <- windowsOS
• Daemon <- Unix / Linux
  • -> Back-end 프로그램
    • 실행 상태를 .log 파일

MySQL

• 클라이언트 프로그램

MySQL 설치 후 설정

mysql 서버에 접속하기

로컬 MySQL 서버에 접속

mysql -u root -p Enter password: 암호입력

원격 MySQL 서버에 접속

mysql -h 서버주소 -u root -p Enter password: 암호입력

mysql root 암호 변경

alter user ‘root’@’localhost’ identified by ‘1111’;

MySQL 사용자 추가

CREATE USER ‘사용자아이디’@’서버주소’ IDENTIFIED BY ‘암호’;

로컬에서만 접속할 수 있는 사용자를 만들기:

CREATE USER ‘study’@’localhost’ IDENTIFIED BY ‘1111’; => 이 경우 stidu 사용자는 오직 로컬(서버를 실행하는 컴퓨터)에서만 접속 가능한다. => 다른 컴퓨터에서 실행하는 MySQL 서버에 접속할 수 없다는 것을 의미한다.

원격에서만 접속할 수 있는 사용자를 만들기:

CREATE USER ‘study’@’%’ IDENTIFIED BY ‘1111’; => 이 경우 study 사용자는 원력에서만 접속 가능하다.

MySQL 사용자 목록 조회

select user from 데이터베이스명.테이블명; select user from mysql.user;

MySQL 데이터베이스 생성

CREATE DATABASE 데이터베이스명 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

CREATE DATABASE studydb DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

MySQL 사용자에게 데이터베이스 사용 권한 부여

GRANT ALL ON 데이터베이스명.* TO ‘사용자아이디’@’서버주소’; GRANT ALL ON studydb.* TO ‘study’@’localhost’;

데이터베이스 목록 조회

show databases;

사용자 교체

quit (프로그램 종료 후) mysql -u study -p (다시 실행)

기본으로 사용할 데이터베이스 지정하기

use 데이터베이스명 use studydb;

데이터베이스의 전체 테이블 목록 조회

show tables;

MariaDB [(none)]> select Host, User from mysql.user;

+———–+————-+ | Host | User | +———–+————-+ | % | study | <- 원격 스터디 사용자 | localhost | mariadb.sys | | localhost | root | | localhost | rsh | | localhost | study | <- 로컬 스터디 사용자 +———–+————-+ 5 rows in set (0.007 sec)

• mysql 파일에서 user라는 테이블의 Host, User 라는 항목을 보여달라.

설정이 완료되면 루트 사용자는 종료하고 스터디 사용자를 사용한다.

• MariaDB [(none)]> show databases; +——————–+ | Database | +——————–+ | information_schema | | studydb | +——————–+ 2 rows in set (0.001 sec)

SQL

• dbms에 보내는 명령
• DDL : Data Definition Language
  • 데이터를 저장할 구조(테이블)를 준비
    • table, view, function, trigger, procedure, index (DBMS 객체) 관리하는 명령
• DML : Data Manipulation Language
  • Data insert, update, delete 명령
• DQL : Data Query Language
  • select 명령 (데이터 조회 명령)
• https://mariadb.com/kb/en/documentation/

DDL

• 데이터베이스(database) = 스키마(schema)
• 테이블(table)
• 뷰(view)
• 트리거(trigger)
  • 특정 조건에서 자동으로 호출되는 함수
  • 특정 조건? SQL 실행 전/후 등
  • OOP 디자인 패턴에서 옵저버에 해당한다.
• 함수(function)
• 프로시저(procedure)

• 인덱스(index)

• MariaDB [(none)]> create table test01 ( -> name varchar(50) not null, -> kor int not null, -> eng int not null, -> math int not null, -> sum int not null, -> aver float not null -> ); ERROR 1046 (3D000): No database selected

• MariaDB [(none)]> show databases; +——————–+ | Database | +——————–+ | information_schema | | mysql | | performance_schema | | studydb | +——————–+ 4 rows in set (0.002 sec)

• MariaDB [(none)]> create table studydb.test01 ( -> name varchar(50) not null, -> kor int not null, -> eng int not null, -> math int not null, -> sum int not null, -> aver float not null -> ); Query OK, 0 rows affected (0.031 sec)

• MariaDB [(none)]> drop table studydb.test01; <- 삭제 Query OK, 0 rows affected (0.008 sec)

• MariaDB [(none)]> use studydb; <- 어떤 db를 사용할 지 설정 Database changed

테이블 생성

• MariaDB [studydb]> create table test01 ( <- 테이블을 만듬 -> name varchar(50) not null, -> kor int not null, -> eng int not null, -> math int not null, -> sum int not null, -> aver float not null -> ); Query OK, 0 rows affected (0.018 sec)

테이블 확인

• MariaDB [studydb]> show tables; +——————-+ | Tables_in_studydb | +——————-+ | test01 | +——————-+ 1 row in set (0.000 sec)

테이블 정보 보기

• MariaDB [studydb]> describe test01; +——-+————-+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +——-+————-+——+—–+———+——-+ | name | varchar(50) | NO | | NULL | | | kor | int(11) | NO | | NULL | | | eng | int(11) | NO | | NULL | | | math | int(11) | NO | | NULL | | | sum | int(11) | NO | | NULL | | | aver | float | NO | | NULL | | +——-+————-+——+—–+———+——-+ 6 rows in set (0.003 sec)

• MariaDB [studydb]> desc test01; <- 줄임말 +——-+————-+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +——-+————-+——+—–+———+——-+ | name | varchar(50) | NO | | NULL | | | kor | int(11) | NO | | NULL | | | eng | int(11) | NO | | NULL | | | math | int(11) | NO | | NULL | | | sum | int(11) | NO | | NULL | | | aver | float | NO | | NULL | | +——-+————-+——+—–+———+——-+ 6 rows in set (0.003 sec)

테이블 삭제

• MariaDB [studydb]> drop table test01; Query OK, 0 rows affected (0.003 sec)

• MariaDB [studydb]> show tables; <- 테이블을 보면 없다고 나온다. Empty set (0.000 sec)

null 허용과 not null (무결성 제약 조건)

• MariaDB [studydb]> create table test1( -> no int not null, -> name varchar(20) -> ); Query OK, 0 rows affected (0.021 sec)

• MariaDB [studydb]> desc test1; +——-+————-+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +——-+————-+——+—–+———+——-+ | no | int(11) | NO | | NULL | | | name | varchar(20) | YES | | NULL | | +——-+————-+——+—–+———+——-+ 2 rows in set (0.003 sec)

• MariaDB [studydb]> insert into test1(no, name) values(1, ‘aaa’); Query OK, 1 row affected (0.001 sec)

• MariaDB [studydb]> insert into test1(no, name) values(null, ‘bbb’); ERROR 1048 (23000): Column ‘no’ cannot be null

• MariaDB [studydb]> insert into test1(no, name) values(3, null); Query OK, 1 row affected (0.001 sec)

• MariaDB [studydb]> select * from test1; +—-+——+ | no | name | +—-+——+ | 1 | aaa | | 3 | NULL | +—-+——+ 2 rows in set (0.000 sec)

기본값 지정

• MariaDB [studydb]> create table test1( -> no int not null, -> name varchar(20) default ‘noname’, -> age int default 20 -> ); Query OK, 0 rows affected (0.018 sec)

• MariaDB [studydb]> insert into test1(no, name, age) values(1, ‘aaa’, 30); Query OK, 1 row affected (0.002 sec)

• MariaDB [studydb]> select * from test1; +—-+——+——+ | no | name | age | +—-+——+——+ | 1 | aaa | 30 | +—-+——+——+ 1 row in set (0.000 sec)

• MariaDB [studydb]> insert into test1(name, age) values(‘aaa’, 30); ERROR 1364 (HY000): Field ‘no’ doesn’t have a default value

• MariaDB [studydb]> insert into test1(no, age) values(3, 30); Query OK, 1 row affected (0.002 sec)

• MariaDB [studydb]> insert into test1(no, name) values(4, ‘ddd’); Query OK, 1 row affected (0.002 sec)

• MariaDB [studydb]> insert into test1(no) values(5); Query OK, 1 row affected (0.001 sec)

• MariaDB [studydb]> select * from test1; +—-+——–+——+ | no | name | age | +—-+——–+——+ | 1 | aaa | 30 | | 3 | noname | 30 | | 4 | ddd | 20 | | 5 | noname | 20 | +—-+——–+——+ 4 rows in set (0.000 sec)

컬럼에 디폴트 옵션이 있는 경우,

• 컬럼 값을 생략하면 디폴트 옵션으로 지정한 값이 사용된다.

• 컬럼 값을 null로 지정하면 기본 값이 사용되지 않는다.

• MariaDB [studydb]> insert into test1(no, age, name) values(6, null, null); Query OK, 1 row affected (0.002 sec)

• MariaDB [studydb]> select * from test1; +—-+——–+——+ | no | name | age | +—-+——–+——+ | 1 | aaa | 30 | | 3 | noname | 30 | | 4 | ddd | 20 | | 5 | noname | 20 | | 6 | NULL | NULL | +—-+——–+——+ 5 rows in set (0.000 sec)

컬럼 타입

• https://mariadb.com/kb/en/data-types/
• int
  • 4바이트 크기의 정수 값 저장
  • 기타 tinyint(1), smallint(2), mediumint(3), bigint(8)
• float
  • 부동소수점 저장
• numeric = decimal
  • 전체 자릿수와 소수점 이하의 자릿수를 정밀하게 지정할 수 있다.
  • numeric(n,e) : 전체 n 자릿수 중에서 소수점은 e 자릿수다.

• numeric : numeric(10, 0) 과 같다.

• TINYINT(M) [ 옵션 UNSIGNED , ZEROFILL ] ★★

  정수형으로 총 1Byte 저장공간을 차지하는 데이터 타입으로 -128에서 127 사이의 숫자를 저장하기 위한 데이터 타입이다. UNSIGNED 옵션을 적용하면 0에서 255까지의 숫자를 저장한다.

• SMALLINT(M) [ 옵션 UNSIGNED , ZEROFILL ]

  정수형으로 총 2Byte 저장공간을 차지하는 데이터 타입으로 -32768에서 32767 사이의 숫자를 저장하기 위한 데이터 타입이다. UNSIGNED 옵션을 적용하면 0에서 65535까지의 숫자를 저장한다.

• MEDIUMINT(M) [ 옵션 UNSIGNED , ZEROFILL ]

  정수형으로 총 3Byte 저장공간을 차지하는 데이터 타입으로 -8388608에서 8388607사이의 숫자를 저장하기 위한 데이터 타입이다. UNSIGNED 옵션을 적용하면 0에서 16777215까지의 숫자를 저장한다.

• INT(M) [ 옵션 UNSIGNED , ZEROFILL ] ★★★

  정수형으로 총 4Byte 저장공간을 차지하는 데이터 타입으로 INTEGER 라고도 사용한다. -2147483648에서 2147483647 사이의 숫자를 저장하기 위한 데이터 타입으로 UNSIGNED 옵션을 적용하면 0에서 4294967295까지의 숫자를 저장한다.

• char(n)
  • 최대 n개의 문자를 저장.
  • 0 <= n <= 255
  • 고정 크기를 갖는다.
  • 한 문자를 저장하더라도 n자를 저장할 크기를 사용한다.
  • 메모리 크기가 고정되어서 검색할 때 빠르다.
• varchar(n)
  • 최대 n개의 문자를 저장.
  • 0 ~ 65535 바이트 크기를 갖는다.
  • n 값은 문자집합에 따라 최대 값이 다르다.
  • 한 문자에 1 바이트를 사용하는 ISO-8859-n 문자집합인 경우, 최대 65535 이다.
  • 만약 UTF-8로 지정된 경우 n은 최대 21844까지 지정할 수 있다. (문자 크기인지 바이트 크기인지 확인)
  • UTF-8로 저장해야 한글을 사용할 수 있다. (국제표준 사용)
  • 가변 크기를 갖는다.
  • 한 문자를 저장하면 한 문자 만큼 크기의 메모리를 차지한다.
  • 메모리 크기가 가변적이라서 데이터 위치를 찾을 때 시간이 오래 걸린다. 그래서 검색할 때 위치를 계산해야 하기 때문에 검색 시 느리다.
  • 빈 문자열도 값이다. null이 아니다.
  • char 는 값을 저장하나 안하나 5칸 차지 = 크기가 균등 = 찾는 속도가 빠름
    • 우편번호나 ISBN처럼 크기가 정해진 데이터를 저장할 때 쓰기도 한다
  • varchar 는 값에 따라 크기가 다르다 = 쓰지 않으면 메모리를 적게 차지 = 찾는 속도가 느림
• select * from test1 where c1=’abc’;
  • DBMS 중에는 고정 크기인 컬럼의 값을 비교할 때 빈자리까지 검사하는 경우도 있다. (ex : 오라클)
  • 즉 c1=’abc’에서는 데이터를 찾지 못하고, c1=’abc ‘여야만 데이터를 찾는 경우가 있다.
  • 그러나 mysql은 고정크기 컬럼이더라도 빈자리를 무시하고 데이터를 찾는다. (ex : mariaDB)
• text(65535), mediumtext(약 1.6MB), longtext(약 2GB)
  • 긴 텍스트를 저장할 때 사용하는 컬럼 타입이다.
  • 오라클의 경우 long 타입과 CLOB(character large object) 타입이 있다.
• date
  • 날짜 정보를 저장할 때 사용한다.
  • 년,월,일 정보를 저장한다.
  • 오라클의 경우 날짜 뿐만 아니라 시간 정보도 저장한다.
  • mariadb는 날짜만 저장한다.
• time
  • 시간 정보를 저장할 때 사용한다.
  • 시, 분, 초 정보를 저장한다.
• datetime
  • 날짜와 시간 정보를 함께 저장할 때 사용한다.
• 불린 타입
  • 보통 true, false를 의미하는 값을 저장할 때는 정수 1 또는 0으로 표현한다.
  • 또는 문자로 Y 또는 N으로 표현하기도 한다.
  • 회원 탈퇴를 하면 게시글이 다 지워지지 않도록 컬럼에 불린 타입을 사용하여 회원이 가입 상태인지 탈퇴 상태인지 표시한다.
  • ㄱ 회원이 게시글을 올리고 ㄴ, ㄷ, ㄹ 회원이 댓글을 달았을 때 ㄱ 회원이 탈퇴하면 게시글이 사라진다. 하지만 ㄴ, ㄷ, ㄹ 회원이 달아놓은 댓글은 그대로 남아있다.
  • 불린 타입에는 true, false, 문자 ‘1’, ‘0’, 숫자 1, 0을 집어넣을 수 있다.
  • 불린 타입으로 지정하면 내부적으로는 컬럼을 생성할 때 tinyint(1)로 설정한다.
    • 숫자 컬럼인 경우 값을 설정할 때 문자로 표현할 수 있다.
    • 즉, 문자열을 숫자로 바꿀 수 있으면 된다.

키 컬럼 지정

• 테이블:
  • no, name, email, id, pwd, jumin, tel, postno, basic_addr, gender, fax
• key vs candidate key
  • key
    • 데이터를 구분할 대 사용하는 컬럼들의 집합
    • 예)
      • {email}, {jumin}, {id}, {name, tel}, {tel, basic_addr, gender, name}, {name, jumin}, {email, id} {id, name, email} …
  • candidate key (후보키 = 최소키)
    • key 들 중에서 최소 항목으로 줄인 키
    • 예)
      • {jumin}, {email}, {id}, {name, tel}
• alternate key vs primary key
  • primary key (주 키)
    • candidate key 중에서 DBMS 관리자가 사용하기로 결정한 키
    • 예) DBMS 관리자가 id 컬럼의 값을 데이터를 구분하는 키로 사용하기로 결정했다면
    • 주 키는 {id} 가 된다.
      • 테이블의 데이터를 구분할 때 사용하는 컬럼들이다.
      • 줄여서 PK라고 표시한다.
      • PK 컬럼을 지정하지 않으면 데이터가 중복될 수 있다.
    • 주 키로 선택되지 않은 모든 candidate key는 alternate key 가 된다.
    • pk는 not null 이다.
    • pk를 여러개 지정했을 때 pk 들이 다 똑같지 않으면 저장 가능하다. 하나라도 값이 다르면 오류가 생기지 않는다.
      • 두 개 이상의 컬럼을 묶어서 PK로 선언하고 싶다면 각 컬럼에 대해서 개별적으로 PK를 지정해서는 안된다.
      • 여러 개의 컬럼을 묶어서 PK로 지정하려면 별도의 문법을 사용해야 한다.
        • constraint 제약조건이름_pk primary key(컬럼명, 컬럼명, …)
    • 여러 개의 컬럼을 묶어서 PK로 사용하면 데이터를 다루기가 불편하다. 즉 데이터를 찾을 때 마다 name과 age 값을 지정해야 한다.
    • 그래서 실무에서는 이런 경우 ‘학번’처럼 임의의 값을 저장하는 컬럼을 만들어 PK로 사용한다.
    • 학번이 중복되지 않는다면 동일 데이터를 저장할 수 있게 되는 경우 사용하는 문법이 uniuqe이다.
      • K는 아니지만 PK처럼 중복을 허락하지 않는 컬럼을 지정할 때 사용한다.
      • 그래서 PK를 대신해서 사용할 수 있는 key라고 해서 “대안키(alternate key)”라고 부른다.
      • unique = alternate key(대안키)
        • no int primary key, constraint test1_uk unique (name, age)
        • constraint test1_pk primary key(no), constraint test1_uk unique (name, age)
  • alternate key (대안키)
    • candidate key 중에서 primary key로 선택된 키를 제외한 나머지 키
    • 비록 primary key는 아니지만 primary key 처럼 데이터를 구분하는 용도로 대신 사용할 수 있다고 해서 대안 키(alternate key)라 부른다.
• artificial key (인공키)
  • primary key로 사용하기에 적절한 컬럼을 찾을 수 없다면
  • 예) 게시글 : 제목, 내용, 작성자, 등록일, 조회수
    • 이런 경우에 key로 사용할 컬럼을 추가한다.
    • 보통 일련번호를 저장할 정수 타입의 컬럼을 추가한다.
      • 예) 게시글 : 게시글 번호
    • 대부분의 SNS 서비스들은 일련의 번호를 primary key로 사용한다.
    • 왜?
      • 회원 탈퇴의 경우
        • 아이디도 제거한다.
        • 아이디를 지우면 그 아이디와 연결된 게시글을 지워야 한다.
        • 그런데 회원 아이디 대신 일련 번호를 사용하면
        • 그 회원이 쓴 게시글은 일련 번호와 묶인다.
        • 따라서 아이디가 삭제되더라도 해당 글은 계속 유효하게 처리할 수 있다.
      • 이메일 변경,
        • primary key 값은 다른 데이터에서 사용하기 때문에,
          • 예) 게시글을 젖아할 때 회원 이메일을 저장한다고 가정하자.
        • pk 값을 변경하면 그 값을 사용한 모든 데이터에 영향을 끼친다.
        • 그래서 pk 값을 다른 데이터에서 사용한 경우, DBMS는 pk 값을 변경하지 못하도록 통제한다.
        • 이렇게 값이 변경될 수 있는 경우에는 pk로 사용하지 말라
        • 대신 회원 번호와 같은 임의의 키(인공 키)를 만들어 사용하는 것이 좋다.
  • 실제적으로는 인공키를 주로 사용한다.