[node.js] node + 외부 db - mysql 세팅, db, table 생성, 다루기

[세팅 환경]

- OS : RHEL 7.2(Amazon instance)

- mysql version : 5.7.18 MySQL Community Server

=> 환경 확인 : mysql 접속 후 SELECT VERSION();

[설치 확인]

(1) root 사용자 로그인 : su - 

(2) mysql 서버 패키지 설치 확인하기 : yum info mysql-community-server 

- mysql-community-server 설치하면 의존성있는 패키지 설치됨

[초기 설정하기]

(1) 설치 후 mysql 명령어를 입력하면 Access denied 가 뜸 : 패스워드 설정을 해줘야함

- /var/log/mysqld.log 를 열고 temorary password가 있음

- 임시 비밀번호 찾기 : grep 'temporary password' /var/log/mysqld.log

=> 외부 접속 관련한 설정은 /etc/my.cnf 에서 설정하면됨 : 다음 포스팅에서 할 것

(2) 로그인하기 

- 입력 후 방금 찾은 임시비밀번호 입력 : $ mysql -u root -p 

(3) root 계정 비밀번호 변경

- ALTER USER 'root'@'localhost' IDENTIFIED BY ‘새로운비밀번호’

- validate_password 플러그인이 자동으로 설치되어있는데, 비밀번호 설정 규칙이 있음 

=> 적어도 하나의 대문자, 소문자 하나, 숫자 하나, 특수문자 하나 포함되어야하며, 총 길이 8자 이상이 되어야함

(4) 종료 후 변경한 비밀번호로 다시 로그인 하기

- mysql > QUIT 입력

- mysql > select user() : mysql 유저 목록 보기

[데이터베이스와 테이블 생성하기 그리고 다루기]

(1) 생성되어있는 데이터베이스 목록 조회하기

- mysql > show databases;

=> mysql 데이터베이스는 사용자 권한 관련 테이블을 가지고 있음

(2) 데이터베이스 생성하기

- mysql > create database 데이터베이스명;

- sql 명령어와 달리 데이터베이스 이름은 대소문자 구분을 해야함

(3) mysql 세션 시작할 때 원하는 데이터베이스에서 작업을 하도록 설정해야함

- 세션 요청 시 데이터베이스 명시해서 시작하기 : shell > mysql -h 호스트 -u 사용자명 -p  데이터베이스명

- 세션 요청이 끝난 후 데이터베이스 선택하기 : mysql > use 데이터베이스명;

(4) 설정 해놓은 상태 데이터베이스의 테이블 항목 보기

- mysql > show tables;

(5) 테이블 구조 짜기 : 테스트로 애완동물 테이블 짜기

- 매번 바뀌어야하는 수를 테이블의 컬럼으로 두는 것보다 상징 값(고정값?)을 두고 mysql에서 제공하는 연산 기능으로 연산하는 것이 좋음

=> 예를 들어 나이를 기록하기보다 태어난 날을 저장해두고 현재의 날과 계산하여 나이를 기록하거나….(mysql 추천 사항)

=> 직접 계산해서 저장하는 것이 아니라 age 컬럼을 계산하는건 저장되어있는 생일과 현재 날짜를 계산하여 저장되도록 하면 됨

=> 아래 13번 항목 참고

- 컬럼 : 이름, 소유자, 성별, 출생 및 사망 

- 컬럼명과 데이터타입(길이) : name VARCHAR(20), owner VARCHAR(20), sex CHAR(1), birth DATE, death DATE

=> VARCHAR : 가변형, 길이(byte가 아닌 글자수)가 20일 필요x 65535 까지 지정 가능, 255자 이하는 1byte, 이후부터는 2byte

=> CHAR : 고정형, 최대길이 255, 데이터에 상관없이 공간을 확보해둠

=> 이후 수정을 위해 ALTER TABLE문을 제공함

- id 값을 설정해주는 것이 좋음 : 기본적으로 테이블의 레코드가 추가될 때 자동적으로 부여되도록(증가되는 값)

(6) test 데이터베이스 내에 pet 테이블 생성하기

- mysql > create table pet (name varchar(20), owner varchar(20), sex char(1), birth date, death date);

- 데이터베이스 테이블 목록 조회 : mysql > show tables;

(7) pet 테이블 컬럼 조회하기

- mysql > describe pet;

(8) 빈 테이블에 데이터 추가하기 

- 기본적으로 넣으려고 하는 데이터를 파일화해서 넣을 수 있음 : https://dev.mysql.com/doc/refman/5.7/en/loading-tables.html

-  쿼리문 사용하기 : mysql > insert into 테이블명 values (테이블 컬럼 순서대로, 넣으면, 됨);

=> insert into pet values(‘marry’, ‘jinbro’, ‘m’, ‘2017-06-28’, null);

=> 문자열이나 문자, 날짜는 ‘’(따옴표)로 묶음

=> 지정하지않는 값은 null 처리

(9) 테이블에 저장된 레코드 가져오기 

- select 옵션 : 옵션에는 어떤 데이터(컬럼)를 가져올 것인지

- from 옵션 : 옵션에는 어떤 테이블에서 가져올 것인지

- where 옵션 : 옵션에는 데이터에 대한 조건 처리

- 테이블 모든 레코드의 모든 컬럼 가져오기 : mysql > select * from 테이블명

=> mysql > select * from pet

- 특정 레코드 선택하기 + 모든 컬럼

=> mysql > select * from pet where name = ‘marry’;

=> mysql > select * from pet where birth >= ‘2017-01-01’;

=> mysql > select * from pet where owner = 'jinbro' and birth >= '2017-01-01’;

=> mysql > select * from pet where owner = ‘jinbro’ or owner = ‘jimin’;

=> mysql > select * from pet where (owner = ‘jinbro’ or owner = ‘jimin’)  and birth >= ‘2017-01-01’;

- 특정 컬럼에 해당하는 레코드 선택하기

=> select name, owner from pet;

=> select distinct owner from pet; (owner 컬럼 조회한 후 겹치는 항목있으면 1개로 줄이기)

=> select name, birth from pet where owner=‘jinbro';

(10) 테이블에 저장된 레코드 수정하기

- update 를 사용함 : mysql > update 테이블명 set 컬럼명 = 값 where 조건

=> 모두 변경하려면 where 생략하면됨

=> mysql > update pet set death = ‘2017-07-03’ where name name = ‘marry’;

=> 결과 확인

(11) 테이블에 저장된 레코드 삭제하기

- delete를 사용함 : mysql > delete from 테이블명 where 조건

=> 모든 데이터를 삭제하려면 where 조건 생략하면됨

=> update 쿼리만 테이블 선택하는 from을 생략

(12) 테이블 레코드 선택 후 정렬하기

- order by 사용함 : mysql > select * from 테이블명 order by 컬럼

=> select name, birth from pet order by birth (순차정렬 - 오름차순)

=> 문자열을 정렬할 때 대소문자를 구분하지않고 정렬 : a와 A를 같은 문자로 처리

=> select name, birth from pet order by birth desc (desc를 옵션으로 주면 역순정렬 - 내림차순)

=> select name, birth from pet order by name, birth; (복합 정렬 : name으로 정렬한 결과 내에서 birth로 정렬)

=> select name, birth from pet order by name, birth desc; (내림차순 적용은 birth에만 적용됨)

(13) 레코드 중 Date 관련 데이터 계산 : mysql에서 기본적으로 제공함, 참고자료에 관련 링크가 있음

- timestampdiff(단위, 날짜1, 날짜2)

=> select curdate(); (현재 날짜 찍어보기)

=> select name, birth, curdate(), timestampdiff(year, birth, curdate()) as age from pet 

     => 총 4개의 컬럼을 선택한 것 : name, birth, curdate(), age

    => age는 테이블에 저장된 값이 아니라 mysql에서 제공하는 function에 의해 만들어진 컬럼 : as age

    => from pet : pet 테이블로 부터 총 4개의 컬럼을 선택해서 가져옴

    => 정렬을 하려면 order by 컬럼명 을 붙여주면 됨 : age로도 정렬 가능함, 테이블에 저장되는 컬럼은 아니지만 현재 선택에서는 생성된 것

    => 조건을 추가하려면 where 을 추가해주면 됨

=> select name, birth, curdate(), timestampdiff(year, birth, curdate()) as age from pet where death is not null 

    => where 컬럼명 is not null : 해당 컬럼이 null이 아닌 것만 선택되도록 조건

    => where 컬럼명 is null : 해당 컬럼이 null인 것만 선택되도록 조건

    => 일반적으로 프로그래밍 언어에서 사용하는 연산자를 사용하지 못함 : is null / is not null 사용

- 날짜에서 특정값(년/월/일)만 가져올 때 사용

=> select name, birth, year(birth) as year from pet;

    => year 자리에 year / month / day 로 바꾸면 됨

=> select name, birth from pet where month(birth) = 5; (생일이 5월인 펫만 가져오기)

- 현재 시간(년/월/일/시간) 찍기

=> select now();

(14) 컬럼 null 검사

- is null / is not null 사용 : mysql > select 컬럼 is null from 테이블명;

=> select death is null from pet;

    => null이 맞다면 1, null이 아니라면 0

=> select death is not null from pet;

    => null이 아니라면 1, null이 맞다면 0

- null 검사는 프로그래밍 언어에서 일반적으로 사용하는 비교연산자를 사용 못함

- 0은 false를 뜻하지 null과는 아무런 상관없음

(15) 패턴을 통해서 레코드 가져오기

- mysql > select * from 테이블명 where 컬럼명 like ‘문자%’;

=> 컬럼에서 문자로 시작하는 레코드만 가져옴

=> %를 앞으로 옮기면 문자로 끝나는 레코드만 가져옴

=> mysql > select * from pet where name like ‘%문자%’; (문자를 포함하는 레코드를 가져옴)

=> mysql > select * from pet where name like ‘____’ ( _ 의 개수 == 글자수로 취급해서 글자수에 맞는 레코드를 가져옴)

- 확장 정규표현식을 사용할 경우에는 like가 아닌 regexp를 사용함 : https://dev.mysql.com/doc/refman/5.7/en/pattern-matching.html

(16) 테이블에 저장된 레코드 수 가져오기 : count() function 사용, row를 카운팅함 

- 모든 레코드 수 가져오기 : mysql > select count(*) from 테이블명;

=> mysql > select count(*) from pet;

- 특정 컬럼을 기준으로 그룹시켜서 카운트하기 : group by 컬럼명 사용

=> mysql > select owner, count(*) as count from pet group by owner order by count;

    => as는 count() function으로 생성된 컬럼의 별칭을 정하는 것

    => order by 기준으로 as 별칭을 사용

=> mysql > select sex, count(*) from pet where birth >= ‘2016-01-01’ group by sex

    => 조건을 충족하는 레코드 그룹핑

    => group by 사용해서 그룹됨으로써 출력할 수 없는 컬럼이 포함되었는지 확인하면서 쿼리문 작성하기

[주의할 점]

- mysql 버젼에 따라 쿼리문이 다를 수 있음 : 실습 진행한 부분은 5.7.18임

- 구글링해서 막 가져다쓰기보다 mysql documents를 보는 것이 더 빠름 : 버젼별로 정리해놨음

[더하기]

- mysql 쿼리문 응용, 테이블 간 관계 맺기

- mysql 서버 호스트 설정 및 호스팅하기 : node mysql 모듈을 가지고 접근해서 레코드 select, insert, update, delete 해보기

- mysql query 에러 핸들링해보기 : 없는 조건일 때 핸들링

[참고자료]

- mysql, mysql on linux : https://dev.mysql.com/doc/refman/5.7/en/linux-installation-yum-repo.html

- mysql, mysql 5.7 tutorials : https://dev.mysql.com/doc/refman/5.7/en/tutorial.html

- mysql, functions ref manual : https://dev.mysql.com/doc/refman/5.7/en/functions.html