[DB-MariaDB] 개발자 지망생 스터디 - 14일차

Maria DB

[1] 개요

• SQL에 기반을 둔 RDBMS(관계형 데이터 베이스)로 Open Source 형태로 제공
• MySQL 개발자가 만들어서 MySQL 과 거의 유사
• SQL 도 거의 차이가 없음
• 작업 단위
  • 데이터 베이스 > 테이블
  • 하나의 데이터베이스는 여러 유저가 공유

데이터베이스 사용

• 로컬 데이터 베이스 - 임시 저장
• 외부 데이터베이스 - 서버

>> SQLite 나 Access는 로컬에 설치해서 사용 : 데이터를 빠르게 또는 OffLine 상태에서 사용하기 위한 목적

>> 그 이외의 데이터베이스는 대부분의 경우 외부에 설치해서 애플리케이션 서버를 통해 사용하거나 직접 사용(DBA 나 Operator)

 

[2] 데이터베이스 서버 설치

1. OS에 직접 설치

• windows - https://mariadb.org/downlaod
• Mac(brew install mariadb) 이나 리눅스는 패키지 관리자를 이용해서 설치

2. 가상화컨테이너에 설치

• docker와 같은 가상화 컨테이너에 설치
• docker site에서 회원가입
• docker 다운로드 및 설치
  • Windows에서는 WSL2를 추가로 설치해야함

Docker는 회원가입을 하고 설치

Windows에서는 Docker를 사용

• Mariadb image 다운로드
  • 터미널 : docker pull mariadb 버전(버전은 생략하면 최신 버전)
• mariadb 컨테이너 생성
  • docker run --name mariadb -d -p 외부에서 접속할 포트번호 : MariaDB 포트번호 -e MYSQL_ROOT_PASSWORD=루트비밀번호 컨테이너이름
• 컨테이너 실행 확인
  • docker ps
• 컨테이너 중지, 시작 및 재시작
  • docker stop 컨테이너 이름
  • docker start 컨테이너 이름
  • docker restart 컨테이너 이름
• 컨테이너 삭제 (실행 중인 컨테이너를 삭제할 때는 뒤에 -f 옵션 추가)
  • docker rm 컨테이너 이름

3. 데이터베이스 접속 도구 설치

• DBeaver(Open Source 이고 여러 데이터베이스 접속 가능) 설치 (https://dbeaver.io/)
• 금융 분야는 주로 토드나 오렌지 같은 접속 도구를 사용하는 것도 나쁘지 않음

4. 데이터베이스 서버 실행 및 접속 확인

• 데이터베이스 접속도구에서 연결
  • HOST : localhost
  • PORT : 3306 (설치 할때 변경했으면 수정)
  • DATABASE : mysql (기본 제공)
  • USER NAME : root(기본 제공)
  • PASSWORD : 설치시 사용한 비밀번호

 

5. 데이터베이스 외부 접속 허용

1. 권한 설정
   • GRANT all privileges on 사용할데이터베이스이름 TO '계정'@'접속할IP'
     • 모든 데이터베이스를 사용하게 하고자 하는 경우는 *.*
     • 모든 곳에서 접속하도록 할 때는 %를 설정하고 로컬에서만 접속하도록 할때는 localhost
     • 권한 설정 명령은 설정하고 적용 명령을 수행 : FLUSH privileges;
     • root 계정을 모든 곳에서 접속하도록 설정 : GRANT all privileges on *.* TO 'root'@'%';
       FLUSH privileges;
2. 서버 설정(Windows에 직접 설치했으면 이 과정은 생략)
   • /etc/mysql/mariadb.conf.d/50-server.cnf 파일의 bind-address 부분을 허용할 IP로 변경을 해주어야 하는데 0.0.0.0 이면 모든 곳에서 접속가능
     실제 서버 설정이라면 Application Server의 IP 만 허용함.
   • docker는 직접 파일을 수정할 수 없기 때문에 터미널에서 컨테이너의 bash로 접속
     • docker exec -it 컨테이너이름 bash 명령으로 접속한 후
       • apt update
       • apt upgrade
       • apt install vim
     • vim /etc/mysql/mariadb.conf.d/50-server.cnf 명령으로 수정
     • 텍스트가 열리면 i 를 눌러 수정모드로 진입해 수정하고 esc를 눌러 수정모드를 빠져나온 후 :wq 명령으로 저장하고 나와야함
       • bind adress 부분을 0.0.0.0 으로 수정
     • mariadb 재시작 (docker에서는 컨테이너 재시작)

6. SQL 작성 규칙

• SQL의 예약어는 대소문자 구분을 하지 않음
• 테이블 이름이나 컬럼 이름은 대소문자를 구분하는 데이터베이스도 있고 구분하지 않는 데이터베이도 있음
  • MariaDB 나 MySQL은 구분함
• 값을 작성할 때는 대소문자 구분을하는데 MariaDB는 대소문자 구분을 하지 않는 경우도 있음
• 숫자 데이터는 따옴표를하지 않고 문자는 작은 따옴표를 해서 표현하는데 MariaDB나 MySQL은 큰 따옴표도 허용함
• 명령문의 마지막은 ; 인데 접속도구에서는 해도 되고 않아도 되지만 절차적프로그램을 할 때는 명확하게 해주어야 함
  • 프로그래밍 언어에서 SQL을 사용할 떄는 ;을 하면 안됨

7. 데이터베이스 관련 명령어

1) 데이터 베이스 생성

• create database 데이터베이스이름, #이미 존재하는 이름이면 에러
  • 일반적으로 프로젝트를 진행할 때 마다 데이터베이스를 생성

2) 데이터베이스 확인

• show databases;

3) 데이터베이스 사용 - 항상 MySQL 이나 MariaDB 에서는 SQL을 사용하기 전에 데이터베이스 사용 설정을 먼저

• use 데이터베이스 이름;

4) 데이터베이스 삭제

• drop database 데이터베이스 이름

5) 데이터베이스에 존재하는 테이블 확인

• show tables;

8.

9. SQL 분류

1) DDL : 구조에 관련된 명령어로 일반적으로 DBA의 명령어 → 명령 취소 불가능

• CREATE : 구조 생성
• ALTER : 구조 변경
• DROP : 구조 삭제
• TRUNCATE : 테이블 내의 데이터 삭제
• RENAME : 구조 이름 변경

2) DQL - 검색 관련 명령어

• SELECT

3) DML : 데이터 관련 명령어  → 명령 취소 가능

• INSERT
• UPDATE
• DELETE

4) TCL : 트랜잭션 관련 명령어 → 명령 취소 불가능

• COMMIT : 현재까지 작업 내용을 원본에 반영
• ROLLBACK : 작업 내용을 취소
• SAVEPOINT : 취소할 지점을 만드는 명령

5) DCL : 제어 명령 → 명령 취소 불가능, 운영자의 언어

• GRANT : 권한 부여
• REVOKE : 권한 회수

6) 개발자에게 중요도

• DQL > DML > TCL > DDL > DCL

10. SELECT

• 데이터 조회 명령어로 원본에 아무런 영향을 주지 않음
• 원본에서 데이터를 복제해서 리턴함

1) 샘플 데이터 구조

• EMP 테이블
  EMPNO : 사원번호로 정수 4자리이고 기본키
  ENAME : 사원이름으로 문자
  JOB : 직무로 문자
  MGR : 관리자의 사원번호
  HIREDATE : 입사일로 날짜 형식
  SAL : 급여로 실수 7자리 소수 2자리
  COMM : 상여금으로 실수 7자리 소수 2자리
  DEPT : 부서 번호로 정수 2자리이고 DEPT 테이블의  DEPTNO를 참조
• DEPT 테이블
  DEPTNO : 부서번호로 정수 2자리이고 기본키
  DNAME : 부서이름으로 문자
  LOC : 위치로 문자
• SALGRADE 테이블
  GRADE : 호봉으로 숫자이고 기본키
  LOSAL : 호봉의 최저 급여로 숫자
  HISAL : 호봉의 최고 급여로 숫자
• TCITY 테이블
  NAME : 도시이름으로 문자열이고 기본키
  AREA : 면적으로 정수
  POPU : 인구수로 정수
  METRO : 대도시 여부로 문자
  REGION : 지역으로 문자
• TSTAFF 테이블
  NAME : 직원으로 문자이고 기본키
  DEPART : 부서이름으로 문자열
  GENDER : 성별로 문자열
  JOINDATE : 입사일로 문자열
  GRADE : 직무로 문자열
  SALARY : 급여로 정수
  SCORE : 고과 점수로 실수

2) SELECR 용어

• Selection : 테이블의 행을 선택할 떄 사용하는 것
• Projection : 테이블의 열을 선택할 때 사용하는 것
• Join : 공유 테이블 양쪽의 열에 대해서 링크를 생성해서 다른 테이블의 데이터를 가져와서 합치는 것

3) MariaDB에서의 SELECT 구조

SELECT

5 - SELECT : 데이터를 열 단위로 조회하기 위한 열 이름이나 계산식을 나열

1 - FROM : 데이터를 조회할 테이블을 나열

2 - [WHERE : 데이터를 행 단위로 분할하기 위한 조건]

3 - [GROUP BY : 데이터를 그룹화시키기 위한 열 이름 이나 계산식을 나열]

4 - [HAVING :데이터를 행 단위로 분할하기 위한 조건]

6 - [ORDER BY : 데이터를 정렬하기 위한 열이름이나 계산식 또는 SELECT 절의 번호와 정렬 방법]

7 - [LIMIT : 데이터의 위치 와 개수를 지정해서 가져오기 위한 절로 표준은 아님

• FROM을 수행하게 되면 원본 데이터베이스에서 테이블 단위로 복제를 해와서 작업을 수행
  • 여기서 다른 이름을 명명하는 것은 별명이 아니라 이름을 바꾸는 것임

4) SELECT 구문의 가장 기본적인 구조

• 테이블의 모든 데이터 조회 : 컬럼의 순서는 테이블을 만들 때 작성한 순서대로 리턴
• 직접테이블을 생성한 경우가 아니라면 *은 사용하지 않는 것이 좋음
  
  • SELECT *
    FROM 테이블이름
• 특정 컬럼만 추출
  • SELECT 절에 필요한 컬럼만 나열
  • SELECT 컬럼 이름 나열
    FROM 테이블 이름

5) SELECT 절에서의 별명

• SELECT 절에서는 컬럼에 별명을 부여할 수 있음
• 하나의 공백을 두고 별명을 설정하면 되는데 이 때 공백 자리에 AS를 추가해도 됨
• 별명에 공백이나 특수문자 또는 대문자가 있으면 " "로  묶어야 함.
• SELECT 절의 별명을 ORDER BY에서 사용 가능하고 프로그래밍 언어에서도 별명을 가지고 데이터를 가져옴
• 계산식이나 그룹 함수의 결과를 조회하고자 할 때는 별명을 부여하는 것이 좋음
  • SELECT name as 이름
    FROM

6) 계산식 출력

• FROM 절을 제외한 모든 곳에서 계산식 사용이 가능
• 계산식은 가상의 컬럼이고 FROM은 실제 테이블을 가져오는 것이므로 FROM 절에는 계산식을 사용할 수 없음
• tCity 테이블에서 name 과 popu dp 10000을 곱한 결과를 조회
  • SELECT name, popu * 10000 AS "인구(명)"
    FROM tCity;
• 단순 계산식은 FROM을 생략해도 됨
  • SELECT 60 * 60 * 24;

7) concat 함수

• 2개 이상의 문자열을 합쳐주는 함수
• 2개 이상의 컬럼이나 연산식을 하나로 합쳐서 출력하기 위해서 사용
• MyBatis와 같은 SQL Mapper Framework 에서 like를 사용하기 위해서는 알아두어야 함

8) DISTINCT

• SELECT 절의 맨 앞에 한번만 기재해서 컬럼의 중복된 값을 제거하는 역할
• 컬럼 이름이 하나이면 그 컬럼의 값이 중복된 것만 제거
• 컬럼이 2개 이상이면 모든 값이 일치하는 경우에 제외

9) ORDER BY

• 조회된 데이터를 정렬하기 위한 절
• ORDER BY 컬럼이름 [ASC | DESC] 나열
  • ASC 는 오름차순이고 DESC는 내림차순인데 기본은 ASC 오름차순이 기본임
• 오름차순의 기본 순서
  • 숫자 : 작은 것에서 큰 것으로
  • 날짜 : 빠른 것에서 늦은 것으로
  • 문자 : 알파벳 순
  • NULL이 가장 마지막
• 컬럼이름 대신 SELECT 절의 조회 순서로 설정하는 것이 가능함
• SELECT 절에서 만든 별명 사용 가능
• 2개 이상의 필드 나열 가능한데 첫 번째 필드로 정렬하고 동일한 값이 있는 경우 두 번째 필드의 정렬 조건을 확인
• 계산식을 이용한 정렬 가능
• 권장하지는 않지만 정렬 기준 필드를 출력하지 않아도 됨
• tCity 테이블의 데이터를 region 별로 정렬하고 동일한 값이 있으면 name의 내림차순으로 정렬을 하고 region, name, area, popu 컬럼을 조회

SELECT region, name, area, popu
FROM tCity
ORDER BY region, name DESC;

• SELECT 구문의 결과가 2개 이상의 행이 될 것 같은 경우에는 ORDER BY를 이용해서 정렬을 해주는 것이 좋음

10) WHERE

• 테이블의 데이터를 행 단위로 분할하기 위한 조건을 설정하는 절
• SELECT, UPDATE, DELETE 구문와 함께 사용
• 비교 연산자
  • =
  • >
  • <
  • >=, NOT 컬럼이름 <
  • <=, NOT 컬럼이름 >
  • <>, !=, ^=, NOT 컬럼이름 =
• tCity 테이블에서 name이 서울인 데이터의 모든 컬럼을 조회

SELECT *
FROM tCity
WHERE name = '서울';

 

• tCity 테이블에서 metro가 y인 데이터의 모든 컬럼을 조회

SELECT *
FROM tCity
WHERE metro = 'y';
-- 'Y' 로 조회를 해도 결과는 같음
-- MariaDB의 경우 대소문자를 구별하지 않는 경우가 있을 수 있기 때문에 유의해야 함

SELECT *
FROM tCity
WHERE BINARY(metro) = 'Y';
-- 대소문자 구별하기 : 조회를 할 때 컬럼 이름을 BINARY로 묶어주거나 컬럼을 만들 때 자료형 뒤에 BINARY를 추가해주어야 함.

 

• tCity 테이블에서 popu의 값이 100 이상인 데이터의 모든 컬럼을 조회

SELECT *
FROM tCity
WHERE popu >= 100;
-- 크다 작다 크거나 같다 작거나 같다 조건이 있는 경우 테스트를 할 때 경계값과 경계값 양쪽의 데이터를 반드시 테스트 하여야 함.
-- Boundary Value Analysys(경계값 분석 기법)

 

• NULL(아직 알려지지 않은 값으로 표현) 비교
  • NULL은 일반 연산자로 비교 안됨
  • IS NULL 과 IS NOT NULL로 비교
  • 데이터베이스에서 NULL을 저장하는 방법은 공간에 NULL을 대입하는 개념이 아니고 NULL을 저장할 수 있는 컬럼에는 데이터를 저장할 수 있는 공간에 하나의 공간을 추가해서 그 공간에 NULL 여부를 표시하기 때문임
• tStaff 테이블에서 score 가 NULL 인 데이터 조회

SELECT *
FROM tStaff;

SELECT *
FROM tStaff
WHERE score = NULL;
-- 조회 되지 않음

SELECT *
FROM tStaff
WHERE score IS NULL;

 

• 논리 연산자 AND 와 OR 제공
  • AND는 두 개의 조건이 모두 일치하는 경우에만 조회하는데 앞의 조건이 일치하지 않으면 뒤의 조건은 확인하지 않음
  • OR는 두 개의 조건 중 하나의 조건만 일치해도 조회되는데 앞의 조건이 일치하면 뒤의 조건은 확인하지 않음
  • AND 가 OR 보다 우선 순위가 높음
• tCity 테이블에서 popu가 100만 이상이고 area 가 700 이상인 데이터의 모든 컬럼을 조회

SELECT *
FROM tCity
WHERE popu >= 100 AND area >= 700;

• NOT
• LIKE
  • 부분 일치하는 데이터를 조회
  • 와일드 카드 문자
    • _ : 하나의 문자와 매칭
      • SELECT * FROM EMP WHERE ENAME LIKE '_____N';
        • EMP 테이블에서 '_' 5개는 5글자 뒤에 N이 붙는 ENAME의 정보를 조회해줌
    • % : 글자 수 상관 없음
      • SELECT * FROM tCity WHERE name LIKE '%천%';
        • tCity 테이블에서 name에 천이 포함된 데이터를 조회
      • SELECT * FROM tCity WHERE name NOT LIKE '%천%';
        • tCity 테이블에서 name에 천이 포함되지 않은 데이터를 조회
      • SELECT * FROM tCity WHERE name LIKE '%천';
        • tCity 테이블에서 name에 천으로 끝나는 데이터를 조회
      • SELECT * FROM tCity WHERE name LIKE '천%';
        • tCity 테이블에서 name에 천으로 시작하는 데이터를 조회
    • [ ] : 문자를 나열하면 문자 중 하나와 일치
    • [^] : 문자를 나열하면 문자에 포함되지 않는 (불일치)
    • 와일드 카드 문자를 검색하고자 하는 경우는 ESCAPE 이용
      
      • WHERE SALE LIKE '%30#%%' ESCAPE '#';
        
        • SALE에 30% 가 포함된 데이터 조회
        • '#' 뒤에 한 문자는 있는 그대로 해석

💡 생각해보기

 

웹을 개발할 때,

검색을 예로 들면 공백과 공백이 있는 문장의 경우 좌우를 바꿔서 검색을 했을 때 같은 결과를 나타내도록 생각을 해보아야 함.

 

예를 들면,

Google 검색에 'Hello' 검색과 '    Hello'와 '       Hello       '의 결과는 같아야하며,

'Hello adele', 'adele Hello', 'adel Hello', 'Hello adel' 는 검색자가 같은 의미로 한 검색이기 때문에 같은 검색이 나올 수 있도록 생각을 해보아야 한다.

 

• BETWEEN ~ AND
  • BETWEEN A AND B 형태로 작성하는 A 부터 B 까지의 데이터 조회
  • 숫자, 날짜, 문자열 모두 사용이 가능
  • 단순 AND 로도 가능
  • tCity 테이블에서 popu 가 50 ~ 100 사이인 데이터 조회
    
    • SELECT * FROM tCity WHERE popu BETWEEN 50 AND 100;
  • 문자의 크기 비교는 맨 앞 글자부터 순서대로 하나씩 비교
  • tCity 테이블에서 name이 'ㅊ' 이 포함되고 시작되는 문자 데이터 조회
    • SELECT * FROM tCity WHERE name >= '차' AND name < '카';
    • 음악 재생 목록 INDEX에 사용될 수 있음
  • tStaff 에서 joindate 가 2015년 1월 1일 부터 2017년 12 31일 사이인 데이터를 조회
    • SELECT * FROM tStaff WHERE joindate BETWEEN '20150101' AND '20171231'
• IN 연산자
  • IN (값을 나열) : 나열된 값에 포함되는 경우 조회
  • tCity 테이블에서 region 이 경상 이나 전라인 데이터 조회
    • SELECT * FROM tCity WHERE region = '경상' OR region = '전라';
    • SELECT * FROM tCity WHERE region IN('경상','전라');

11) LIMIT

• 행의 개수 제한에 이용 - TOP N
  • LIMIT [건너뛸 행의 개수], 조회할 개수
• 최근에는 LIMIT 개수 OFFSET 건너뛸 행의 개수
• ORDER BY 와 같이 사용되는 경우가 많음
  • tCity 테이블에서 popu 가 큰 4개의 데이터 조회
    • SELECT * FROM tCity ORDER BY popu desc LIMIT 4;
  • tCity 테이블에서 popu 가 큰 5번째 데이터부터 2개 조회
    1. SELECT * FROM tCity ORDER BY popu desc LIMIT 4,2;
    2. SELECT * FROM tCity ORDER BY popu desc LIMIT 2 OFFSET 4;
    3. SELECT * FROM tCity ORDER BY popu desc OFFSET 4 ROWS FETCH NEXT 2 ROWS ONLY;