지구정복

[SQL] 12/02 | 제약조건(not null, unique, 기본키, 외래키), 뷰(뷰사용, 뷰조인, 인라인뷰), 인덱스, 데이터베이스 사용자, DCL, 데이터베이스 백업(덤프, 복원) 본문

데이터 엔지니어링 정복/SQL

[SQL] 12/02 | 제약조건(not null, unique, 기본키, 외래키), 뷰(뷰사용, 뷰조인, 인라인뷰), 인덱스, 데이터베이스 사용자, DCL, 데이터베이스 백업(덤프, 복원)

eeaarrtthh 2020. 12. 2. 15:36
728x90
반응형

제약조건(Constraint)

데이터에 (입력/수정/삭제) 조건 - 데이터 오류가 나지않게하기 위해 - 데이터의 일관성을 위해

=> 프로그램을 이용해서 검사할 수도 있음.

 

제약조건을 설정하는 방법은 컬럼단위 제약조건과

테이블 단위 제약조건절에 주는 방법 두 가지가 있다.

 

information_schema.table_constraints : 관리가자 사용하는 테이블 (제약조건 정보가 들어가 있음)

어떠한 데이터베이스에서도 위 테이블에 접근할 수 있다.

desc information_schema.table_constraints;
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG | varchar(512) | NO   |     |         |       |
| CONSTRAINT_SCHEMA  | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_NAME    | varchar(64)  | NO   |     |         |       |
| TABLE_SCHEMA       | varchar(64)  | NO   |     |         |       |
| TABLE_NAME         | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_TYPE    | varchar(64)  | NO   |     |         |       |
+--------------------+--------------+------+-----+---------+-------+
6 rows in set (0.014 sec)

 

1. NOT NULL / NULL

필수 입력항목

하나의 테이블에 NOT NULL조건의 컬럼을 만든다.

CREATE TABLE DEPT_N1(
DEPTNO INT(2) NOT NULL,
DNAME VARCHAR(2),
LOC VARCHAR(2)
);

DESC DEPT_N1;
+--------+------------+------+-----+---------+-------+
| Field  | Type       | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| DEPTNO | int(2)     | NO   |     | NULL    |       |
| DNAME  | varchar(2) | YES  |     | NULL    |       |
| LOC    | varchar(2) | YES  |     | NULL    |       |
+--------+------------+------+-----+---------+-------+
3 rows in set (0.010 sec)

 

2. UNIQUE

중복을 배제한 고유값

CREATE TABLE DEPT_U1(
DEPTNO INT(2) UNIQUE,
DNAME VARCHAR(14),
LOC VARCHAR(13)
);

DESC DEPT_U1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int(2)      | YES  | UNI | NULL    |       |
| DNAME  | varchar(14) | YES  |     | NULL    |       |
| LOC    | varchar(13) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.010 sec)

DEPTNO의 KEY에 UNI를 알 수 있다.

중복된 값을 넣을 수 없다는 의미이다.

 

중복돼서 값을 집어넣으면 에러가 나온다.

INSERT INTO DEPT_U1 VALUES(10, '개발', '서울');
Query OK, 1 row affected (0.006 sec)

MariaDB [TEST1]> INSERT INTO DEPT_U1 VALUES(20, '연구', '경기');
Query OK, 1 row affected (0.009 sec)

MariaDB [TEST1]> INSERT INTO DEPT_U1 VALUES(10, '연구', '경기');
ERROR 1062 (23000): Duplicate entry '10' for key 'DEPTNO'    <----- 에러

 

실제 시스템 테이블에서 확인해보자.

SELECT CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'TEST1';

+-----------------+--------------+------------+-----------------+
| CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+-----------------+--------------+------------+-----------------+
| DEPTNO          | test1        | dept_u1    | UNIQUE          |
+-----------------+--------------+------------+-----------------+
1 row in set (0.048 sec)

1열부터 제약조건 이름은 DEPTNO, 테이블 스키마는 TEST1, 테이블명은 DEPT_U1, 제약조건타입은 UNIQUE임을

알 수 있다.

 

이번에는 테이블 단위 제약조건절을 이용해서 제약조건을 설정해본다.

CREATE TABLE DEPT_U2(
DEPTNO INT(2),
DNAME VARCHAR(14),
LOC VARCHAR(13),
CONSTRAINT DEPT_U2_DEPTNO_UK UNIQUE(DEPTNO)
);

DESC DEPT_U2;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int(2)      | YES  | UNI | NULL    |       |
| DNAME  | varchar(14) | YES  |     | NULL    |       |
| LOC    | varchar(13) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.009 sec)


--시스템에서 확인
SELECT CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'TEST1';

+-------------------+--------------+------------+-----------------+
| CONSTRAINT_NAME   | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+-------------------+--------------+------------+-----------------+
| DEPTNO            | test1        | dept_u1    | UNIQUE          |
| DEPT_U2_DEPTNO_UK | test1        | dept_u2    | UNIQUE          |
+-------------------+--------------+------------+-----------------+
2 rows in set (0.045 sec)

 

NULL은 중복을 허용한다.

유니크키에다가 NULL을 집어넣어 보자.

INSERT INTO DEPT_U1 VALUES(NULL, '연구', '경기');
INSERT INTO DEPT_U1 VALUES(NULL, '연구', '경기');

SELECT * FROM DEPT_U1;
+--------+-------+------+
| DEPTNO | DNAME | LOC  |
+--------+-------+------+
|     10 | 개발  | 서울 |
|     20 | 연구  | 경기 |
|   NULL | 연구  | 경기 |
|   NULL | 연구  | 경기 |
+--------+-------+------+
4 rows in set (0.001 sec)

 

 

3. 기본키(Primary Key)

UNIQUE KEY + NOT NULL 이 합쳐진 테이블의 고유키이다.

테이블당 반드시 1개가 있어야 한다.

CREATE TABLE DEPT_P1(
DEPTNO INT(2) PRIMARY KEY,
DNEMA VARCHAR(14),
LOC VARCHAR(13)
);

DESC DEPT_P1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int(2)      | NO   | PRI | NULL    |       |
| DNEMA  | varchar(14) | YES  |     | NULL    |       |
| LOC    | varchar(13) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.009 sec)


SELECT CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'TEST1';

+-------------------+--------------+------------+-----------------+
| CONSTRAINT_NAME   | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+-------------------+--------------+------------+-----------------+
| PRIMARY           | test1        | dept_p1    | PRIMARY KEY     |
| DEPTNO            | test1        | dept_u1    | UNIQUE          |
| DEPT_U2_DEPTNO_UK | test1        | dept_u2    | UNIQUE          |
+-------------------+--------------+------------+-----------------+
3 rows in set (0.046 sec)

이제 기본키 제약조건이 걸린 컬럼에 중복값과 NULL값을 집어넣어 보자.

INSERT INTO DEPT_P1 VALUES(10, '개발', '서울');

INSERT INTO DEPT_P1 VALUES(10, '연구', '경기');
ERROR 1062 (23000): Duplicate entry '10' for key 'PRIMARY'  --에러

INSERT INTO DEPT_P1 VALUES(NULL, '연구', '경기');
ERROR 1048 (23000): Column 'DEPTNO' cannot be null --에러


SELECT * FROM DEPT_P1;
+--------+-------+------+
| DEPTNO | DNEMA | LOC  |
+--------+-------+------+
|     10 | 개발  | 서울 |
+--------+-------+------+
1 row in set (0.000 sec)

테이블 단위 제약조건으로 기본키를 설정해보자.

CREATE TABLE DEPT_P2(
DEPTNO INT(2),
DNAME VARCHAR(14),
LOC VARCHAR(13),
CONSTRAINT DEPT_P2_DEPTNO_PK PRIMARY KEY(DEPTNO)
);

DESC DEPT_P2;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int(2)      | NO   | PRI | NULL    |       |
| DNAME  | varchar(14) | YES  |     | NULL    |       |
| LOC    | varchar(13) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.009 sec)

 

4. 외래키(Foreign Key)

참조키 또는 외래키

반드시 다른 테이블의 기본키나 유니크키를 참조해서 만들 수 있다.

CREATE TABLE DEPT_P(
DEPTNO INT(2) PRIMARY KEY,
DNAME VARCHAR(14),
LOC VARCHAR(13)
);

CREATE TABLE EMP_F(
EMPNO INT(4),
DNAME VARCHAR(10),
JOB VARCHAR(9),
DEPTNO INT(2),
CONSTRAINT EMP_F_DEPTNO_FK FOREIGN KEY(DEPTNO) REFERENCES DEPT_P (DEPTNO)
);

DESC EMP_F;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| EMPNO  | int(4)      | YES  |     | NULL    |       |
| DNAME  | varchar(10) | YES  |     | NULL    |       |
| JOB    | varchar(9)  | YES  |     | NULL    |       |
| DEPTNO | int(2)      | YES  | MUL | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.009 sec)


------
SELECT CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'TEST1';

+-------------------+--------------+------------+-----------------+
| CONSTRAINT_NAME   | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+-------------------+--------------+------------+-----------------+
| PRIMARY           | test1        | dept_p     | PRIMARY KEY     |
| PRIMARY           | test1        | dept_p1    | PRIMARY KEY     |
| PRIMARY           | test1        | dept_p2    | PRIMARY KEY     |
| DEPTNO            | test1        | dept_u1    | UNIQUE          |
| DEPT_U2_DEPTNO_UK | test1        | dept_u2    | UNIQUE          |
| EMP_F_DEPTNO_FK   | test1        | emp_f      | FOREIGN KEY     |    <-- 외래키
+-------------------+--------------+------------+-----------------+
6 rows in set (0.049 sec)

EMP_F 테이블에서 DEPTNO를 외래키로 만드려면 DEPT_P에서 DEPTNO컬럼이 기본키나 유니크키가 되어야 한다.

 

이때 DEPT_P 테이블에 DEPTNO가 없으면 EMP_F테이블에서도 데이터를 집어넣을 수 없다.

하지만 NULL값은 집어넣을 수 있다.

INSERT INTO EMP_F VALUES(1000, '홍길동', 'CLERK', '10');
ERROR 1452 (23000): Cannot add or update a child row: 
a foreign key constraint fails (`test1`.`emp_f`, CONSTRAINT `EMP_F_DEPTNO_FK` 
FOREIGN KEY (`DEPTNO`) REFERENCES `dept_p` (`DEPTNO`))

INSERT INTO EMP_F VALUES(1000, '홍길동', 'CLERK', NULL);

SELECT * FROM EMP_F;
+-------+--------+-------+--------+
| EMPNO | DNAME  | JOB   | DEPTNO |
+-------+--------+-------+--------+
|  1000 | 홍길동 | CLERK |   NULL |
+-------+--------+-------+--------+
1 row in set (0.002 sec)

이때 DEPT_P테이블에 DEPTNO 데이터를 집어넣고 다시 EMP_F테이블에 데이터를 집어넣으면 들어가진다.

INSERT INTO DEPT_P VALUES(10, '개발', '서울');
INSERT INTO EMP_F VALUES(1000, '홍길동', 'CLERK', '10');

SELECT * FROM EMP_F;
+-------+--------+-------+--------+
| EMPNO | DNAME  | JOB   | DEPTNO |
+-------+--------+-------+--------+
|  1000 | 홍길동 | CLERK |   NULL |
|  1000 | 홍길동 | CLERK |     10 |
+-------+--------+-------+--------+
2 rows in set (0.000 sec)

데이터를 지울때도 마찬가지이다.

DEPT_P 테이블에서 10번 부서를 지우려고하면 참조중이서 지울 수 없다고 한다.

DELETE FROM DEPT_P WHERE DEPTNO = 10;
ERROR 1451 (23000): Cannot delete or update a parent row: 
a foreign key constraint fails (`test1`.`emp_f`, 
CONSTRAINT `EMP_F_DEPTNO_FK` FOREIGN KEY (`DEPTNO`) REFERENCES `dept_p` (`DEPTNO`))

테이블을 지울때도 마찬가지이다.

DROP TABLE DEPT_P;
--에러

 

따라서 참조되고있는 테이블이나 데이터를 지울 때는 먼저 참조를 하고 있는 테이블이나 데이터를 지운 다음에

참조되고 있는 것을 지우면 된다.

 

데이터를 수정할 때도 마찬가지이다.

UPDATE DEPT_P SET DEPTNO = 20 WHERE DEPTNO=10;
--에러

 

ALTER구문으로 제약조건 설정하기

 

DROP TABLE EMP_F;
DROP TABLE DEPT_P;

ALTER TABLE DEPT_P ADD CONSTRAINT PRIMARY KEY(DEPTNO);

DESC DEPT_P;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int(2)      | NO   | PRI | NULL    |       |
| DNAME  | varchar(14) | YES  |     | NULL    |       |
| LOC    | varchar(13) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.010 sec)

제약조건 삭제하기

ALTER TABLE DEPT_P DROP PRIMARY KEY;

DESC DEPT_P;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int(2)      | NO   |     | NULL    |       |
| DNAME  | varchar(14) | YES  |     | NULL    |       |
| LOC    | varchar(13) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.007 sec)

유니크키를 만들 수 있다.

ALTER TABLE DEPT_P ADD CONSTRAINT UNIQUE(DEPTNO);

 

ALTER 구문을 이용해서 EMP_F 테이블을 만들고 외래키를 설정해보자.

CREATE TABLE EMP_F(
EMPNO INT(4),
DNAME VARCHAR(10),
JOB VARCHAR(9),
DEPTNO INT(2)
);

ALTER TABLE EMP_F ADD CONSTRAINT FOREIGN KEY (DEPTNO) REFERENCES DEPT_P(DEPTNO);

DESC EMP_P;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| EMPNO  | int(4)      | YES  |     | NULL    |       |
| DNAME  | varchar(10) | YES  |     | NULL    |       |
| JOB    | varchar(9)  | YES  |     | NULL    |       |
| DEPTNO | int(2)      | YES  | MUL | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.009 sec)

ALTER구문으로 EMP_F의 외래키를 지워보자.

먼저 외래키 제약조건의 이름을 알기 위해 SYSTEM 테이블에서 이름을 확인한다.

SELECT CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'TEST1';
+-------------------+--------------+------------+-----------------+
| CONSTRAINT_NAME   | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+-------------------+--------------+------------+-----------------+
| DEPTNO            | test1        | dept_p     | UNIQUE          |
| PRIMARY           | test1        | dept_p1    | PRIMARY KEY     |
| PRIMARY           | test1        | dept_p2    | PRIMARY KEY     |
| DEPTNO            | test1        | dept_u1    | UNIQUE          |
| DEPT_U2_DEPTNO_UK | test1        | dept_u2    | UNIQUE          |
| emp_f_ibfk_1      | test1        | emp_f      | FOREIGN KEY     | <--지울 외래키 재약조건이름
+-------------------+--------------+------------+-----------------+
6 rows in set (0.045 sec)


ALTER TABLE EMP_F DROP CONSTRAINT emp_f_ibfk_1;

 

 

 

뷰(View)

SELECT문에 별명을 준 것으로 생각하면 된다.

즉 가상의 테이블이다.

선언은 아래와 같다.

CREATE VIEW 뷰이름 AS 서브쿼리;

EMP 테이블을 이용해서 EMP_V1 뷰를 만들어보자.

CREATE VIEW EMP_V1 AS SELECT * FROM EMP;

DESC EMP_V1;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| EMPNO    | int(4)       | NO   |     | NULL    |       |
| ENAME    | varchar(10)  | YES  |     | NULL    |       |
| JOB      | varchar(9)   | YES  |     | NULL    |       |
| MGR      | smallint(6)  | YES  |     | NULL    |       |
| HIREDATE | date         | YES  |     | NULL    |       |
| SAL      | decimal(7,2) | YES  |     | NULL    |       |
| COMM     | decimal(7,2) | YES  |     | NULL    |       |
| DEPTNO   | int(2)       | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+


SELECT * FROM EMP_V1;
+-------+--------+-----------+------+------------+---------+--------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM   | DEPTNO |
+-------+--------+-----------+------+------------+---------+--------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 2010-12-17 | 1600.00 | 300.00 |     30 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 2011-02-20 | 1600.00 | 300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 2011-02-22 | 1250.00 | 500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 2011-04-02 | 2975.00 |   NULL |     20 |
|  7698 | BLAKE  | MANAGER   | 7839 | 2011-05-01 | 2850.00 |   NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 2011-06-09 | 2450.00 |   NULL |     30 |
|  7788 | SCOTT  | ANALYST   | 7566 | 2017-07-13 | 3000.00 |   NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 2011-11-17 | 5000.00 |   NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 2011-09-08 | 1500.00 |   0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 2017-07-13 | 1100.00 |   NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 2011-12-03 |  950.00 |   NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 2011-12-03 | 3000.00 |   NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 2012-01-23 | 1300.00 |   NULL |     10 |
+-------+--------+-----------+------+------------+---------+--------+--------+

뷰를 조회하는 방법은 아래와 같다.

SHOW FULL TABLES;
+------------------+------------+
| Tables_in_sample | Table_type |
+------------------+------------+
| dept             | BASE TABLE |
| dept2            | BASE TABLE |
| emp              | BASE TABLE |
| emp_v1           | VIEW       |
| salgrade         | BASE TABLE |
+------------------+------------+

SHOW FULL TABLES WHERE TABLE_TYPE='VIEW';
+------------------+------------+
| Tables_in_sample | Table_type |
+------------------+------------+
| emp_v1           | VIEW       |
+------------------+------------+

부분적인 컬럼을 가지고 뷰를 만들 수 있다.

CREATE VIEW EMP_V2
AS SELECT ENAME, SAL, HIREDATE
FROM EMP
WHERE DEPTNO = 10;

SELECT * FROM EMP_V2;
+--------+---------+------------+
| ENAME  | SAL     | HIREDATE   |
+--------+---------+------------+
| KING   | 5000.00 | 2011-11-17 |
| MILLER | 1300.00 | 2012-01-23 |
+--------+---------+------------+

뷰를 만든 CREATE문장을 보고싶으면 아래 쿼리를 사용한다.

SHOW CREATE VIEW EMP_V2;
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View   | Create View                                                                                                                                                                                                          | character_set_client | collation_connection |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| emp_v2 | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `emp_v2` AS select `emp`.`ENAME` AS `ENAME`,`emp`.`SAL` AS `SAL`,`emp`.`HIREDATE` AS `HIREDATE` from `emp` where `emp`.`DEPTNO` = 10 | euckr                | euckr_korean_ci      |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+

뷰에다가 새로운 컬럼명을 사용해보자.

CREATE VIEW EMP_V3
AS SELECT ENAME NAME, SAL SALARY, HIREDATE DATE
FROM EMP;

SELECT * FROM EMP_V3;
+--------+---------+------------+
| NAME   | SALARY  | DATE       |
+--------+---------+------------+
| SMITH  | 1600.00 | 2010-12-17 |
| ALLEN  | 1600.00 | 2011-02-20 |
| WARD   | 1250.00 | 2011-02-22 |
| JONES  | 2975.00 | 2011-04-02 |
| BLAKE  | 2850.00 | 2011-05-01 |
| CLARK  | 2450.00 | 2011-06-09 |
| SCOTT  | 3000.00 | 2017-07-13 |
| KING   | 5000.00 | 2011-11-17 |
| TURNER | 1500.00 | 2011-09-08 |
| ADAMS  | 1100.00 | 2017-07-13 |
| JAMES  |  950.00 | 2011-12-03 |
| FORD   | 3000.00 | 2011-12-03 |
| MILLER | 1300.00 | 2012-01-23 |
+--------+---------+------------+

조인을 사용할 수 있다.

CREATE VIEW EMP_DEPT
AS SELECT EMPNO, ENAME, E.DEPTNO, DNAME, LOC
FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;

SELECT * FROM EMP_DEPT;
+-------+--------+--------+------------+----------+
| EMPNO | ENAME  | DEPTNO | DNAME      | LOC      |
+-------+--------+--------+------------+----------+
|  7369 | SMITH  |     30 | SALES      | CHICAGO  |
|  7499 | ALLEN  |     30 | SALES      | CHICAGO  |
|  7521 | WARD   |     30 | SALES      | CHICAGO  |
|  7566 | JONES  |     20 | RESEARCH   | DALLAS   |
|  7698 | BLAKE  |     30 | SALES      | CHICAGO  |
|  7782 | CLARK  |     30 | SALES      | CHICAGO  |
|  7788 | SCOTT  |     20 | RESEARCH   | DALLAS   |
|  7839 | KING   |     10 | ACCOUNTING | NEW YORK |
|  7844 | TURNER |     30 | SALES      | CHICAGO  |
|  7876 | ADAMS  |     20 | RESEARCH   | DALLAS   |
|  7900 | JAMES  |     30 | SALES      | CHICAGO  |
|  7902 | FORD   |     20 | RESEARCH   | DALLAS   |
|  7934 | MILLER |     10 | ACCOUNTING | NEW YORK |
+-------+--------+--------+------------+----------+

 

이제 EMP_SALGRADE 뷰를 만들고 컬럼명은 사원번호, 사원이름, 급여, 연봉, 호봉을 가지도록 만들어보자.

CREATE VIEW EMP_SALGRADE
AS SELECT EMPNO 사원번호, ENAME 사원이름, TRUNCATE(SAL, 0) 급여, 
          TRUNCATE(SAL*12+IFNULL(COMM,0), 0) 연봉, GRADE 호봉
FROM EMP E INNER JOIN SALGRADE S
ON E.SAL BETWEEN S.LOSAL AND S.HISAL;

SELECT * FROM EMP_SALGRADE;
+----------+----------+------+-------+------+
| 사원번호 | 사원이름 | 급여 | 연봉  | 호봉 |
+----------+----------+------+-------+------+
|     7369 | SMITH    | 1600 | 19500 |    3 |
|     7499 | ALLEN    | 1600 | 19500 |    3 |
|     7521 | WARD     | 1250 | 15500 |    2 |
|     7566 | JONES    | 2975 | 35700 |    4 |
|     7698 | BLAKE    | 2850 | 34200 |    4 |
|     7782 | CLARK    | 2450 | 29400 |    4 |
|     7788 | SCOTT    | 3000 | 36000 |    4 |
|     7839 | KING     | 5000 | 60000 |    5 |
|     7844 | TURNER   | 1500 | 18000 |    3 |
|     7876 | ADAMS    | 1100 | 13200 |    1 |
|     7900 | JAMES    |  950 | 11400 |    1 |
|     7902 | FORD     | 3000 | 36000 |    4 |
|     7934 | MILLER   | 1300 | 15600 |    2 |
+----------+----------+------+-------+------+

뷰 자체를 조인할 수 있다.

DROP VIEW SAL_GRADE;

CREATE VIEW EMP_SAL
AS SELECT EMPNO, ENAME, SAL, COMM
FROM EMP;

CREATE VIEW EMP_SALGRADE
AS SELECT EMPNO 사원번호, ENAME 사원이름, TRUNCATE(SAL, 0) 급여, TRUNCATE(SAL*12+IFNULL(COMM,0), 0) 연봉, GRADE 호봉
FROM EMP_SAL E INNER JOIN SALGRADE S
ON E.SAL BETWEEN S.LOSAL AND S.HISAL;

SELECT * FROM EMP_SALGRADE;
+----------+----------+------+-------+------+
| 사원번호 | 사원이름 | 급여 | 연봉  | 호봉 |
+----------+----------+------+-------+------+
|     7369 | SMITH    | 1600 | 19500 |    3 |
|     7499 | ALLEN    | 1600 | 19500 |    3 |
|     7521 | WARD     | 1250 | 15500 |    2 |
|     7566 | JONES    | 2975 | 35700 |    4 |
|     7698 | BLAKE    | 2850 | 34200 |    4 |
|     7782 | CLARK    | 2450 | 29400 |    4 |
|     7788 | SCOTT    | 3000 | 36000 |    4 |
|     7839 | KING     | 5000 | 60000 |    5 |
|     7844 | TURNER   | 1500 | 18000 |    3 |
|     7876 | ADAMS    | 1100 | 13200 |    1 |
|     7900 | JAMES    |  950 | 11400 |    1 |
|     7902 | FORD     | 3000 | 36000 |    4 |
|     7934 | MILLER   | 1300 | 15600 |    2 |
+----------+----------+------+-------+------+

이번에는 서브쿼리를 이용해서 뷰를 만들어보자.(인라인 뷰)
FROM 절에 서브쿼리를 사용하는 것을 인라인뷰라고 한다.

CREATE VIEW EMP_SALGRADE2
AS SELECT EMPNO 사원번호, ENAME 사원이름, TRUNCATE(SAL, 0) 급여, TRUNCATE(SAL*12+IFNULL(COMM,0), 0) 연봉, GRADE 호봉
FROM (SELECT EMPNO, ENAME, SAL, COMM FROM EMP) E INNER JOIN SALGRADE S
ON E.SAL BETWEEN S.LOSAL AND S.HISAL;

SELECT * FROM EMP_SALGRADE2;
+----------+----------+------+-------+------+
| 사원번호 | 사원이름 | 급여 | 연봉  | 호봉 |
+----------+----------+------+-------+------+
|     7369 | SMITH    | 1600 | 19500 |    3 |
|     7499 | ALLEN    | 1600 | 19500 |    3 |
|     7521 | WARD     | 1250 | 15500 |    2 |
|     7566 | JONES    | 2975 | 35700 |    4 |
|     7698 | BLAKE    | 2850 | 34200 |    4 |
|     7782 | CLARK    | 2450 | 29400 |    4 |
|     7788 | SCOTT    | 3000 | 36000 |    4 |
|     7839 | KING     | 5000 | 60000 |    5 |
|     7844 | TURNER   | 1500 | 18000 |    3 |
|     7876 | ADAMS    | 1100 | 13200 |    1 |
|     7900 | JAMES    |  950 | 11400 |    1 |
|     7902 | FORD     | 3000 | 36000 |    4 |
|     7934 | MILLER   | 1300 | 15600 |    2 |
+----------+----------+------+-------+------+

 

뷰를 수정해보자.

10번 부서만 모아 놓은 뷰를 20번 부서 사원만 모아놓은 뷰로 수정해보자.

CREATE VIEW EMP_V4
AS SELECT *
FROM EMP
WHERE DEPTNO = 10;

SELECT * FROM EMP_V4;
+-------+--------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+------+--------+
|  7839 | KING   | PRESIDENT | NULL | 2011-11-17 | 5000.00 | NULL |     10 |
|  7934 | MILLER | CLERK     | 7782 | 2012-01-23 | 1300.00 | NULL |     10 |
+-------+--------+-----------+------+------------+---------+------+--------+

--뷰 수정
ALTER VIEW EMP_V4
AS SELECT *
FROM EMP
WHERE DEPTNO = 20;

SELECT * FROM EMP_V4;
+-------+-------+---------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB     | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+---------+------+------------+---------+------+--------+
|  7566 | JONES | MANAGER | 7839 | 2011-04-02 | 2975.00 | NULL |     20 |
|  7788 | SCOTT | ANALYST | 7566 | 2017-07-13 | 3000.00 | NULL |     20 |
|  7876 | ADAMS | CLERK   | 7788 | 2017-07-13 | 1100.00 | NULL |     20 |
|  7902 | FORD  | ANALYST | 7566 | 2011-12-03 | 3000.00 | NULL |     20 |
+-------+-------+---------+------+------------+---------+------+--------+

뷰 삭제하기

여태까지 만든 모든 뷰를 제거하기

SHOW FULL TABLES;
+------------------+------------+
| Tables_in_sample | Table_type |
+------------------+------------+
| dept             | BASE TABLE |
| dept2            | BASE TABLE |
| emp              | BASE TABLE |
| emp_dept         | VIEW       |
| emp_sal          | VIEW       |
| emp_salgrade     | VIEW       |
| emp_salgrade2    | VIEW       |
| emp_v1           | VIEW       |
| emp_v2           | VIEW       |
| emp_v3           | VIEW       |
| emp_v4           | VIEW       |
| salgrade         | BASE TABLE |
+------------------+------------+

DROP VIEW EMP_DEPT;
DROP VIEW EMP_SAL;
DROP VIEW EMP_SALGRADE;
DROP VIEW EMP_SALGRADE2;
DROP VIEW EMP_V1;
DROP VIEW EMP_V2;
DROP VIEW EMP_V3;
DROP VIEW EMP_V4;

SHOW FULL TABLES;
+------------------+------------+
| Tables_in_sample | Table_type |
+------------------+------------+
| dept             | BASE TABLE |
| dept2            | BASE TABLE |
| emp              | BASE TABLE |
| salgrade         | BASE TABLE |
+------------------+------------+

 

인라인 뷰(Inline View)

FROM절 안에 SELECT절이 들어간 것을 의미

이때 마리아디비에서는 테이블 별칭을 붙여줘야 한다.

SELECT *
FROM (SELECT EMPNO, ENAME, SAL, COMM
	FROM EMP) E;
+-------+--------+---------+--------+
| EMPNO | ENAME  | SAL     | COMM   |
+-------+--------+---------+--------+
|  7369 | SMITH  | 1600.00 | 300.00 |
|  7499 | ALLEN  | 1600.00 | 300.00 |
|  7521 | WARD   | 1250.00 | 500.00 |
|  7566 | JONES  | 2975.00 |   NULL |
|  7698 | BLAKE  | 2850.00 |   NULL |
|  7782 | CLARK  | 2450.00 |   NULL |
|  7788 | SCOTT  | 3000.00 |   NULL |
|  7839 | KING   | 5000.00 |   NULL |
|  7844 | TURNER | 1500.00 |   0.00 |
|  7876 | ADAMS  | 1100.00 |   NULL |
|  7900 | JAMES  |  950.00 |   NULL |
|  7902 | FORD   | 3000.00 |   NULL |
|  7934 | MILLER | 1300.00 |   NULL |
+-------+--------+---------+--------+

 

인덱스(Index)

효율적인 데이터(순서) 저장

index(색인 - 목차)

오름차순이나 내림차순이 아니라 다른 방법으로 정렬해놓아야 효율적이고 빠른 검색이 가능하다.

인덱스는 기본 데이터가 아닌 가상적인 컬럼을 만들어서 순서화돼서 접근이 쉽게끔 만들어주는 것을 의미.

 

B-Tree 인덱스

밸런스드 트리이다.

데이터베이스의 인덱싱 알고리즘에서 가장 일반적이고 범용적이다.

 

트리구조로 최상위에 하나의 '루트노드'가 존재하고

하위에 자식노드가 붙어 있는 형태이다.

 

보통 중간값 정렬방법이라고 하고, 기존 테이블과는 다른 저장구조를 가진다.

따라서 새로운 인덱스테이블을 만든 것을 의미한다.

 

만드는 방법

1. 자동 INDEX

    PRIMARY KEY, UNIQUE KEY를 설정하면 자동으로 INDEX가 설정된다.

2. 수동 INDEX

 

DEPT 테이블의 인덱스 확인하기

SHOW INDEX FROM DEPT;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| dept  |          0 | PRIMARY  |            1 | DEPTNO      | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

 

새로운 테이블을 만들고 인덱스를 조회해보자.

CREATE TABLE DEPT_I (
DEPTNO INT(2),
DNAME VARCHAR(14),
LOC VARCHAR(13)
);

SHOW INDEX FROM DEPT_I;
Empty set (0.006 sec)

이제 기본키 제약조건을 준 다음 확인해보면 인덱스 테이블이 자동으로 생성된 것을 확인할 수 있다.

ALTER TABLE DEPT_I ADD CONSTRAINT PRIMARY KEY(DEPTNO);

SHOW INDEX FROM DEPT_I;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| dept_i |          0 | PRIMARY  |            1 | DEPTNO      | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

다시 기본키 제약조건을 삭제하고 확인하면 인덱스테이블은 사라진다.

ALTER TABLE DEPT_I DROP PRIMARY KEY;

SHOW INDEX FROM DEPT_I;
Empty set (0.006 sec)

 

이번에는 수동으로 인덱스를 만들어보자.

CREATE INDEX DEPT_I_IDX ON DEPT_I (DNAME);

SHOW INDEX FROM DEPT_I;
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| dept_i |          1 | DEPT_I_IDX |            1 | DNAME       | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

인덱스를 지워보자.

DROP INDEX DEPT_I_IDX ON DEPT_I;

SHOW INDEX FROM DEPT_I;
Empty set (0.006 sec)

이번에는 유니크키를 DEPT_I 테이블의 DEPTNO 컬럼에 설정하고 인덱스를 만들어보자.

ALTER TABLE DEPT_I MODIFY DEPTNO INT(2) NULL;

ALTER TABLE DEPT_I ADD CONSTRAINT UNIQUE KEY (DEPTNO);

SHOW INDEX FROM DEPT_I;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| dept_i |          0 | DEPTNO   |            1 | DEPTNO      | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

 

만약 INSERT나 UPDATE할 때는 INDEX테이블은 잠시 중단된다.

따라서 INSERT나 UPDATE한 이후에는 인덱스테이블은 다시 만들어야 한다.(인덱스 리빌딩)

보통 사용을 하지않는 새벽에 실시한다.

보통 은행이 새벽때 서비스 중지하는 것은 백업 및 인덱스재구성을 해주기 때문이다.

 

인덱스를 주기적으로 리빌드하는 이유는 아래 블로그에 자세히 나와있다.

인덱스 주기적으로 리빌드 하는 이유( Why does index rebuild in regulary?) (tistory.com)

 

인덱스 주기적으로 리빌드 하는 이유( Why does index rebuild in regulary?)

테이블의 삽입/삭제는 인덱스에도 같이 반영됩니다. 그러나, 이런 행위 를 index rebuild라고 말하지는 않습니다. index rebuild라 함은 인덱스를 구성하는 전체 건에 대해 새로 인덱스를 생성하는 것을

swconsulting.tistory.com

 

데이터베이스 사용자 관리

먼저 MYSQL 데이터베이스 테이블에서 USER 테이블을 조회해보자.

USE MYSQL;

DESC USER;
+------------------------+---------------------+------+-----+----------+-------+
| Field                  | Type                | Null | Key | Default  | Extra |
+------------------------+---------------------+------+-----+----------+-------+
| Host                   | char(60)            | NO   |     |          |       |
| User                   | char(80)            | NO   |     |          |       |
| Password               | longtext            | YES  |     | NULL     |       |
| Select_priv            | varchar(1)          | YES  |     | NULL     |       |
| Insert_priv            | varchar(1)          | YES  |     | NULL     |       |
| Update_priv            | varchar(1)          | YES  |     | NULL     |       |
| Delete_priv            | varchar(1)          | YES  |     | NULL     |       |
| Create_priv            | varchar(1)          | YES  |     | NULL     |       |
| Drop_priv              | varchar(1)          | YES  |     | NULL     |       |
| Reload_priv            | varchar(1)          | YES  |     | NULL     |       |
| Shutdown_priv          | varchar(1)          | YES  |     | NULL     |       |
| Process_priv           | varchar(1)          | YES  |     | NULL     |       |
| File_priv              | varchar(1)          | YES  |     | NULL     |       |
| Grant_priv             | varchar(1)          | YES  |     | NULL     |       |
| References_priv        | varchar(1)          | YES  |     | NULL     |       |
| Index_priv             | varchar(1)          | YES  |     | NULL     |       |
| Alter_priv             | varchar(1)          | YES  |     | NULL     |       |
| Show_db_priv           | varchar(1)          | YES  |     | NULL     |       |
| Super_priv             | varchar(1)          | YES  |     | NULL     |       |
| Create_tmp_table_priv  | varchar(1)          | YES  |     | NULL     |       |
| Lock_tables_priv       | varchar(1)          | YES  |     | NULL     |       |
| Execute_priv           | varchar(1)          | YES  |     | NULL     |       |
| Repl_slave_priv        | varchar(1)          | YES  |     | NULL     |       |
| Repl_client_priv       | varchar(1)          | YES  |     | NULL     |       |
| Create_view_priv       | varchar(1)          | YES  |     | NULL     |       |
| Show_view_priv         | varchar(1)          | YES  |     | NULL     |       |
| Create_routine_priv    | varchar(1)          | YES  |     | NULL     |       |
| Alter_routine_priv     | varchar(1)          | YES  |     | NULL     |       |
| Create_user_priv       | varchar(1)          | YES  |     | NULL     |       |
| Event_priv             | varchar(1)          | YES  |     | NULL     |       |
| Trigger_priv           | varchar(1)          | YES  |     | NULL     |       |
| Create_tablespace_priv | varchar(1)          | YES  |     | NULL     |       |
| Delete_history_priv    | varchar(1)          | YES  |     | NULL     |       |
| ssl_type               | varchar(9)          | YES  |     | NULL     |       |
| ssl_cipher             | longtext            | NO   |     |          |       |
| x509_issuer            | longtext            | NO   |     |          |       |
| x509_subject           | longtext            | NO   |     |          |       |
| max_questions          | bigint(20) unsigned | NO   |     | 0        |       |
| max_updates            | bigint(20) unsigned | NO   |     | 0        |       |
| max_connections        | bigint(20) unsigned | NO   |     | 0        |       |
| max_user_connections   | bigint(21)          | NO   |     | 0        |       |
| plugin                 | longtext            | NO   |     |          |       |
| authentication_string  | longtext            | NO   |     |          |       |
| password_expired       | varchar(1)          | NO   |     |          |       |
| is_role                | varchar(1)          | YES  |     | NULL     |       |
| default_role           | longtext            | NO   |     |          |       |
| max_statement_time     | decimal(12,6)       | NO   |     | 0.000000 |       |
+------------------------+---------------------+------+-----+----------+-------+

DESC DB;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(80)      | NO   | PRI |         |       |
| Select_priv           | enum('N','Y') | NO   |     | N       |       |
| Insert_priv           | enum('N','Y') | NO   |     | N       |       |
| Update_priv           | enum('N','Y') | NO   |     | N       |       |
| Delete_priv           | enum('N','Y') | NO   |     | N       |       |
| Create_priv           | enum('N','Y') | NO   |     | N       |       |
| Drop_priv             | enum('N','Y') | NO   |     | N       |       |
| Grant_priv            | enum('N','Y') | NO   |     | N       |       |
| References_priv       | enum('N','Y') | NO   |     | N       |       |
| Index_priv            | enum('N','Y') | NO   |     | N       |       |
| Alter_priv            | enum('N','Y') | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
| Event_priv            | enum('N','Y') | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
| Delete_history_priv   | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+

데이터베이스 사용자의  호스트명, 유저아이디, 패스워드 출력하기

SELECT HOST, USER, PASSWORD FROM USER;
+-----------------+-------------+-------------------------------------------+
| Host            | User        | Password                                  |
+-----------------+-------------+-------------------------------------------+
| localhost       | mariadb.sys |                                           |
| localhost       | root        | *B573D3EA036614123CB0B52313A6AD58E1F114C0 |
| desktop-8cuq26f | root        | *B573D3EA036614123CB0B52313A6AD58E1F114C0 |
| 127.0.0.1       | root        | *B573D3EA036614123CB0B52313A6AD58E1F114C0 |
| ::1             | root        | *B573D3EA036614123CB0B52313A6AD58E1F114C0 |
| %               | root        | *B573D3EA036614123CB0B52313A6AD58E1F114C0 |
+-----------------+-------------+-------------------------------------------+

이번에는 새로운 사용자를 만들어보자.

로컬호스트를 %로 하면 어디서든 접근이 가능한 사용자이다.

CREATE USER TESTER1@localhost IDENTIFIED BY '1234';

SELECT HOST, USER, PASSWORD FROM USER;
+-----------------+-------------+-------------------------------------------+
| Host            | User        | Password                                  |
+-----------------+-------------+-------------------------------------------+
| localhost       | mariadb.sys |                                           |
| localhost       | root        | *B573D3EA036614123CB0B52313A6AD58E1F114C0 |
| desktop-8cuq26f | root        | *B573D3EA036614123CB0B52313A6AD58E1F114C0 |
| 127.0.0.1       | root        | *B573D3EA036614123CB0B52313A6AD58E1F114C0 |
| ::1             | root        | *B573D3EA036614123CB0B52313A6AD58E1F114C0 |
| %               | root        | *B573D3EA036614123CB0B52313A6AD58E1F114C0 |
| localhost       | TESTER1     | *A4B6157319038724E3560894F7F932C8886EBFCF | <--생성완료
+-----------------+-------------+-------------------------------------------+

이제 접속 테스트를 해본다.

mysql -u TESTER1 -p
Enter password: ****

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 10.5.8-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

이번에는 자신의 아이피를 이용해서 접속해보자.

mysql -h 192.168.219.000 -u TESTER1 -p
Enter password: ****
ERROR 1045 (28000): Access denied for user 'TESTER1'@'DESKTOP-8CUQ26F' (using password: YES)

접속이 안되는 것을 확인할 수 있다.

현재 TESTER1은 localhost이기때문에 그렇다.

 

이번에는 사용자를 %로 만들어서 외부에서 접근이 가능하게 만들어보자.

CREATE USER TESTER2@'%' IDENTIFIED BY '1234';

SELECT HOST, USER, PASSWORD FROM USER;
+-----------------+-------------+-------------------------------------------+
| Host            | User        | Password                                  |
+-----------------+-------------+-------------------------------------------+
| localhost       | mariadb.sys |                                           |
| localhost       | root        | *B573D3EA036614123CB0B52313A6AD58E1F114C0 |
| desktop-8cuq26f | root        | *B573D3EA036614123CB0B52313A6AD58E1F114C0 |
| 127.0.0.1       | root        | *B573D3EA036614123CB0B52313A6AD58E1F114C0 |
| ::1             | root        | *B573D3EA036614123CB0B52313A6AD58E1F114C0 |
| %               | root        | *B573D3EA036614123CB0B52313A6AD58E1F114C0 |
| localhost       | TESTER1     | *A4B6157319038724E3560894F7F932C8886EBFCF |
| %               | TESTER2     | *A4B6157319038724E3560894F7F932C8886EBFCF | <--생성완료
+-----------------+-------------+-------------------------------------------+

mysql -h 192.168.219.000 -u TESTER2 -p
Enter password: ****
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 21
Server version: 10.5.8-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

접속이 되는 것을 확인할 수 있다.

 

SHOW GRANTS FOR TESTER1@LOCALHOST;
+----------------------------------------------------------------------------------------------------------------+
| Grants for TESTER1@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `TESTER1`@`localhost` IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |
+----------------------------------------------------------------------------------------------------------------+

SHOW GRANTS FOR TESTER2@'%';
+--------------------------------------------------------------------------------------------------------+
| Grants for TESTER2@%                                                                                   |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `TESTER2`@`%` IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |
+--------------------------------------------------------------------------------------------------------+

 

사용자 지우기

DROP USER TESTER2@'%';

mysql -u tester2 -p
1234

show databases;

사용자를 지우더라도 로그아웃 전까지는 사용자를 로그인하고 사용할 수 있다.

EXIT 명령어 이후에 다시 로그인하려고하면 로그인이 되지 않는다.

 

DCL

이번에는 특정 데이터베이스만 사용할 수 있도록 사용자에게 권한을 주거나 회수할 수 있다.

GRANT 권한 ON 데이터베이스명;

REVOKE 권한 ON 데이터베이스명 

--먼저 사용자 만들기
CREATE USER TESTER2@'%' IDENTIFIED BY '1234';

SHOW GRANTS FOR TESTER2@'%';
--이 사용자는 ROOT가 만든 테이블을 볼 수도 없고 사용할 수 없다.

--사용자한테 권한 주기
GRANT ALL PRIVILEGES ON SAMPLE.* TO TESTER2@'%';

--권한 뺏기
REVOKE ALL ON SAMPLE.* FROM TESTER2@'%';

 

이제 새로운 사용자에게 EMP 테이블의 SELECT권한을 주고 회수해보자.

GRANT SELECT ON SAMPLE.EMP TO TESTER2@'%';

-----새로운 CMD 창에서
mysql -u TESTER2 -p
1234

USE SAMPLE;

SELECT * FROM EMP;
+-------+--------+-----------+------+------------+---------+--------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM   | DEPTNO |
+-------+--------+-----------+------+------------+---------+--------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 2010-12-17 | 1600.00 | 300.00 |     30 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 2011-02-20 | 1600.00 | 300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 2011-02-22 | 1250.00 | 500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 2011-04-02 | 2975.00 |   NULL |     20 |
|  7698 | BLAKE  | MANAGER   | 7839 | 2011-05-01 | 2850.00 |   NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 2011-06-09 | 2450.00 |   NULL |     30 |
|  7788 | SCOTT  | ANALYST   | 7566 | 2017-07-13 | 3000.00 |   NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 2011-11-17 | 5000.00 |   NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 2011-09-08 | 1500.00 |   0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 2017-07-13 | 1100.00 |   NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 2011-12-03 |  950.00 |   NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 2011-12-03 | 3000.00 |   NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 2012-01-23 | 1300.00 |   NULL |     10 |
+-------+--------+-----------+------+------------+---------+--------+--------+

-----ROOT CMD창에서
REVOKE SELECT ON SAMPLE.EMP FROM TESTER2;

 

이번에는 TESTER4 사용자(비밀번호: 1234)를 만들고 DEPT 테이블에 SELECT, INSERT 권한만 부여해보자.

--ROOT CMD창
CREATE USER TESTER4@'%' IDENTIFIED BY '1234';

GRANT SELECT, INSERT ON SAMPLE.DEPT TO TESTER4@'%';

--TESTER4 CMD창
mysql -u TESTER4 -p
1234

USE SAMPLE;

INSERT INTO DEPT VALUES(50, '영업', '서울');

SELECT * FROM DEPT;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     50 | 영업       | 서울     |
+--------+------------+----------+

--DELETE문은 권한이 없으므로 에러가 난다.
DELETE FROM DEPT WHERE DEPTNO = 50;
ERROR 1142 (42000): DELETE command denied to user 'TESTER4'@'localhost' for table 'dept'

 

데이터베이스 백업(덤프)

덤프
	- 데이터베이스에 대한 백업(Back Up) / 리스토어(Restore)
	이를 자바프로그램으로 만들 수도 있다.

	*mysqldump : 백업명령어
	*mysql 리스토러

백업하기

sample 데이터베이스에 대한 모든 내용을 c드라이브 sql폴더에 sample.sql이란 파일로 백업받기

exit

mysqldump -u root -p sample > C:\sql\sample.sql
Enter password: *******

백업파일 실행시키기

리스토어

exit

mysql -u root -p sample < c:\sql\sample.sql
Enter password: *******

mysql -u root -p
Enter password: *******

use sample;

show tables;
+------------------+
| Tables_in_sample |
+------------------+
| dept             |
| dept2            |
| dept_i           |
| emp              |
| salgrade         |
+------------------+

 

특정 테이블만 백업받기

mysql -u root -p sample emp > c:\sql\emp.sql

 

백업받은 emp 테이블을 다시 리스토어 시켜보자.

DROP TABLE EMP;

EXIT

mysql -u root -p sample < c:\sql\emp.sql

 

728x90
반응형
저작자표시 동일조건

'데이터 엔지니어링 정복 > SQL' 카테고리의 다른 글

[ORACLE SQL] 프로그래머스 - 없어진 기록찾기  (0) 2021.04.11
[SQL] 프로그래머스-입양시각구하기(2)  (0) 2021.04.11
[SQL] 12/01 | 서브쿼리(단일행, 복수행), 조인(CARTESIAN, INNER, OUTER, EQUIL, NON-EQUIL, SELF), DDL(CREATE, ALTER, DROP), DML(INSERT, UPDATE, DELETE), 연습문제  (0) 2020.12.01
[SQL] 11/30 | SELECT문(WHERE, LIKE, IS NULL, IN, BETWEEN, %, _, NOT, ORDER BY, LIMIT), 내장함수(단일행함수, 그룹함수), GROUP BY절, 연습문제  (0) 2020.11.30
[SQL] 11/27 | 데이터베이스 개념, mariaDB설치, 기본명령어(show, use, desc), select문(alias, 연산, distinct, where, 날짜비교, 아스키코드비교)  (0) 2020.11.30
'데이터 엔지니어링 정복/SQL' Related Articles more
Comments