[MariaDB] 무작정 시작하기 (4) - TABLE 생성 및 CRUD

2020/08/07 - [DATABASE/MariaDB] - [MariaDB] 무작정 시작하기 (1) - 설치 및 실행

2020/08/07 - [DATABASE/MariaDB] - [MariaDB] 무작정 시작하기 (2) - DATABASE 명령어

2020/08/07 - [DATABASE/MariaDB] - [MariaDB] 무작정 시작하기 (3) - 유저 및 권한 관리

 

 

 

시작하기전에

  이제 MariaDB를 사용할 준비를 맞쳤으니 실제 TABLE을 생성하고 데이터를 CRUD(입력, 조회, 수정, 삭제)를 처리해보도록 하겠다. 이번 포스트에서 생성할 테이블을 Oracle의 예제인 scott/tiger의 테이블을 사용할 것이다. 

 

ToDo

  - root 계정으로 'SCOTT' DATABASE를 생성하고 dochi 계정에 권한 부여.

  - dochi 계정으로 TABLE 생성 및 DATA 입력하고 데이터 조회.

 

 

 

1. SCOTT DATABASE 생성

1-1. ROOT 계정으로 접속

"C:\Program Files\MariaDB 10.5\bin\mysql.exe" -uroot -p

 

1-2. DATABASE 생성 및 권한 부여

-- DATABASE 생성
CREATE DATABASE SCOTT;

-- dochi 계정에 권한 부여
-- CREATE: TABLE 생성
-- ALTER: TABLE 변경
-- SELECT: DATA 조회
-- INSERT: DATA 입력
-- UPDATE: DATA 수정
-- DELETE: DATA 삭제
GRANT CREATE, ALTER, SELECT, INSERT, UPDATE, DELETE ON SCOTT.* TO 'dochi'@'localhost';

-- 권한 조회
SHOW GRANTS FOR 'dochi'@'localhost';

-- 권한적용
FLUSH PRIVILEGES;

 

1-3. 실행결과

 

 

 

2. 접속 USER 변경

2-1. USER 계정으로 접속

"C:\Program Files\MariaDB 10.5\bin\mysql.exe" -udochi -p

 

2-2. DATABASE 조회 및 사용

-- DATABASE 조회
SHOW DATABASES;

-- 'SCOTT' DATABASE로 변경
USE SCOTT;

-- TABLE 조회
SHOW TABLES;

 

2-3. 실행 결과

 

 

 

3. TABLE 생성

3-1. TABLE 생성

CREATE TABLE EMP(
   EMPNO      INTEGER NOT NULL,
   ENAME       VARCHAR(10),
   JOB        VARCHAR(9),
   MGR        INTEGER,
   HIREDATE   VARCHAR(12),
   SAL        DECIMAL(7, 2),
   COMM       DECIMAL(7, 2),
   DEPTNO     INTEGER
);
 
CREATE TABLE DEPT(
   DEPTNO     INTEGER NOT NULL,
   DNAME      VARCHAR(14),
   LOCATION   VARCHAR(13)
);

CREATE TABLE SALGRADE(
   GRADE      INTEGER NOT NULL,
   LOSAL      INTEGER NOT NULL,
   HISAL      INTEGER NOT NULL
);

CREATE TABLE BONUS (
   ENAME      VARCHAR(10) NOT NULL,
   JOB        VARCHAR(9) NOT NULL,
   SAL        DECIMAL(7, 2),
   COMM       DECIMAL(7, 2)
);

 

3-2. PK 생성

ALTER TABLE EMP 
   ADD CONSTRAINT EMP_PK
   PRIMARY KEY (EMPNO);

ALTER TABLE DEPT
   ADD CONSTRAINT DEPT_PK
   PRIMARY KEY (DEPTNO);

ALTER TABLE SALGRADE
   ADD CONSTRAINT SALGRADE_PK
   PRIMARY KEY (GRADE);

ALTER TABLE BONUS
   ADD CONSTRAINT BONUS_PK
   PRIMARY KEY (ENAME, JOB);

ALTER TABLE EMP
   ADD CONSTRAINT DEPT
   FOREIGN KEY (DEPTNO)
   REFERENCES DEPT (DEPTNO);

ALTER TABLE EMP
   ADD CONSTRAINT MGR
   FOREIGN KEY (MGR)
   REFERENCES EMP (EMPNO);

 

3-3. DATA 추가

INSERT INTO DEPT VALUES
  (10, 'ACCOUNTING', 'NEW YORK'),
  (20, 'RESEARCH',   'DALLAS'),
  (30, 'SALES',      'CHICAGO'),
  (40, 'OPERATIONS', 'BOSTON');
 
INSERT INTO EMP VALUES
  (7839, 'KING',   'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10),
  (7566, 'JONES',  'MANAGER',   7839, '1981-04-02', 2975, NULL, 20),
  (7788, 'SCOTT',  'ANALYST',   7566, '1982-12-09', 3000, NULL, 20),
  (7876, 'ADAMS',  'CLERK',     7788, '1983-01-12', 1100, NULL, 20),
  (7902, 'FORD',   'ANALYST',   7566, '1981-12-03', 3000, NULL, 20),
  (7369, 'SMITH',  'CLERK',     7902, '1980-12-17',  800, NULL, 20),
  (7698, 'BLAKE',  'MANAGER',   7839, '1981-05-01', 2850, NULL, 30),
  (7499, 'ALLEN',  'SALESMAN',  7698, '1981-02-20', 1600,  300, 30),
  (7521, 'WARD',   'SALESMAN',  7698, '1981-02-22', 1250,  500, 30),
  (7654, 'MARTIN', 'SALESMAN',  7698, '1981-09-28', 1250, 1400, 30),
  (7844, 'TURNER', 'SALESMAN',  7698, '1981-09-08', 1500,    0, 30),
  (7900, 'JAMES',  'CLERK',     7698, '1981-12-03',  950, NULL, 30),
  (7782, 'CLARK',  'MANAGER',   7839, '1981-06-09', 2450, NULL, 10),
  (7934, 'MILLER', 'CLERK',     7782, '1982-01-23', 1300, NULL, 10);
 
INSERT INTO SALGRADE VALUES
  (1,  700, 1200),
  (2, 1201, 1400),
  (3, 1401, 2000),
  (4, 2001, 3000),
  (5, 3001, 9999);
  
COMMIT;

 

3-4. TABLE 생성 확인

 

 

 

4. 데이터 조회 예제

4-1. MANAGER의 모든 정보를 출력하시오.

-- 1. 업무가 'MANAGER'인 사원의 모든 정보를 출력하시오.
SELECT *
  FROM SCOTT.EMP T1
 WHERE 1=1
   AND T1.JOB = 'MANAGER'
;

 

4-2. MANAGER의 사번이 7698인 사원의 사번과 이름을 출력하시오.

-- 2. MANAGER의 사번이 7698인 사원의 사번과 이름을 출력하시오.
SELECT T1.EMPNO
     , T1.ENAME
  FROM SCOTT.EMP T1
 WHERE 1=1
   AND T1.MGR = 7698
;

 

4-3. MANAGER의 이름이 'KING'인 사원의 사번과 이름을 출력하시오.

-- 3. MANAGER의 이름이 'KING'인 사원의 사번과 이름을 출력하시오.
SELECT T1.EMPNO
     , T1.ENAME
  FROM SCOTT.EMP T1
 WHERE 1=1
   AND T1.MGR = (
       SELECT E1.EMPNO
         FROM SCOTT.EMP E1
        WHERE 1=1
          AND E1.ENAME = 'KING'
       )
;

 

4-4. MANAGER들의 사번, 이름, 부서번호, 부서명을 출력하시오.

-- 4. MANAGER들의 사번, 이름, 부서번호, 부서명을 출력하시오.
SELECT T1.EMPNO
     , T1.ENAME
     , T1.DEPTNO
     , T2.DNAME
  FROM SCOTT.EMP T1
 INNER JOIN SCOTT.DEPT T2
    ON T2.DEPTNO = T1.DEPTNO
 WHERE 1=1
   AND T1.JOB = 'MANAGER'
;

 

4-5. 각 부서의 부서번호, 부서명, 사원수, 평균 연봉을 출력하시오.(단, KING은 제외)

-- 5. 각 부서의 부서번호, 부서명, 사원수, 평균 연봉을 출력하시오.( 단, KING은 제외 )
SELECT T1.DEPTNO
     , MAX(T1.DNAME) AS DNAME
     , COUNT(1) AS EMP_CNT
     , AVG(T2.SAL) AS AVG_SAL
  FROM SCOTT.DEPT T1
 INNER JOIN SCOTT.EMP T2
    ON T2.DEPTNO = T1.DEPTNO
 WHERE 1=1
   AND T2.ENAME != 'KING'
 GROUP BY T1.DEPTNO
;

 

4-6. SALES부서의 평균 연봉을 구하시오. (단, 최대, 최소 연봉은 제외)

-- 6. SALES부서의 평균 연봉을 구하시오. ( 단, 최대, 최소 연봉은 제외 )
WITH SALES_EMP_OBJ AS (
	SELECT T1.DEPTNO
         , T2.EMPNO
         , T2.SAL
         , ROW_NUMBER() OVER( PARTITION BY T1.DEPTNO ORDER BY T2.SAL DESC ) AS MAX_SAL
         , ROW_NUMBER() OVER( PARTITION BY T1.DEPTNO ORDER BY T2.SAL ASC ) AS MIN_SAL
      FROM SCOTT.DEPT T1
     INNER JOIN SCOTT.EMP T2
        ON T2.DEPTNO = T1.DEPTNO
     WHERE 1=1
       AND T1.DNAME = 'SALES'
)
SELECT AVG(T1.SAL) AS SALSE_AVG_SAL
  FROM SALES_EMP_OBJ T1
 WHERE 1=1
   AND 1 NOT IN ( T1.MAX_SAL, T1.MIN_SAL )
;

 

 

 

마치며

  - 지금까지 MariaDB를 설치하고 DATABASE, USER, TABLE을 생성하고 데이터 입력과 조회까지 기본적인 것들은 모두 다루어보았다. 사실상 문법은 ANSI를 사용하기 때문에 RDBMS를 접해본 사람이라면 쉽게 익숙해질 수 있을 것이다. 특히, MySQL이랑은 같은 소스로 만들어졌기 때문에 사용법은 동일하다고 볼 수 있다.

  - 예전에 Oracle에서 반복 순환 WITH절을 구현했었는데, MariaDB에서는 'WITH RECURSIVE'로 작성을 해야하는것 같다. 이 부분에 대해서는 조금 더 다루어보고 정리를 할 필요가 있을것 같다.

  - 이제 MariaDB랑도 친해졌으니 MongoDB를 걷어내고 MariaDB로 전환을 해봐야겠다.