ToBe끝판왕

[ SQL ] 제약조건( Unique / Primary Key / Foreign Key / Alter ) , 뷰( View , B-tree ) , 인덱스( Index ), 사용자권한( grant, revoke ) , DCL , 데이터베이스 백업( 덤프 / 복원 ) 본문

■ 프로그래밍 SKILLS/SQL

[ SQL ] 제약조건( Unique / Primary Key / Foreign Key / Alter ) , 뷰( View , B-tree ) , 인덱스( Index ), 사용자권한( grant, revoke ) , DCL , 데이터베이스 백업( 덤프 / 복원 )

업그레이드중 2022. 5. 28. 12:16
반응형

 

 

제약조건

 

•  테이블 생성 시, 데이터에 ( 입력 / 수정 / 삭제 ) 조건 - 제약조건( constraint )

•  테이블에 입력될 데이터 형식을 제한하기 위해 쓰인다.

•  데이터베이스에서 데이터의 정확성과 신뢰도를 보장한다.

•  제약조건은 프로그램이 검사할 수 있다. ( 데이터베이스에서 한번 더 검사를 함 )

 

 

 

▶  NOT NULL / NULL

•  자동적으로 하나의 열( Column )은 NULL 값들을 가질 수 있다.

•  어떤 열이 NULL 값을 허용하지 않도록 명시하는 제약조건을 정의할 수 있다.

•  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.012 sec)

 

 

▶  UNIQUE

•  서로 다른 두 레코드의 특정한 열이 동일한 값을 가지는 것을 방지한다. ( 중복값 방지 )

•  UNIQUE 는 값에 대한 중복은 허용하지 않지만 NULL은 허용한다.

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.013 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'

 

 

▶  기본키( Primary Key )

•  데이터베이스 테이블에서 각 행 또는 레코드를 고유하게 식별하는 테이블의 필드( filed ) 이다.

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

•  UNIQUE KEY + NOT NULL 이 합쳐졌다고 할 수 있다.

•  반드시 고유한 값을 가진다.

•  기본키로 설정된 열은 NULL값을 가질 수 없다.

•  다중필드를 기본키로 사용할 때, 이를 복합키 라고 한다.

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)
기본키( Primary key ) 제약조건이 걸린 컬럼에 중복값과 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)

 

 

▶  외래키( 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.010 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.052 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.004 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.001 sec)


// 데이터를 지울 때( 에러 )
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;



=> 참조되고 있는 테이블이나 데이터를 지울 때는 먼저 참조 하고있는 테이블이나 데이터를
   지운다음 참조되고있는 것을 지워야 한다.

 

 

▶  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.014 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.009 sec)


// 유니크키 만들기
ALTER TABLE DEPT_P ADD CONSTRAINT UNIQUE(DEPTNO);


// 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.010 sec)


// 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 )

 

•  기본 테이블로부터 유도된 하나 이상의 기본테이블이나 다른 뷰를 이용하여 생성되는 가상테이블

•  저장장치 내에 물리적으로 존재하지는 않지만, 사용자에게 있는것처럼 간주되어진다.

•  조인문의 사용최소화로 사용상의 편의성을 최대화 한다.

 

 

▶  뷰( view ) 선언

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 |
+-------+--------+-----------+------+------------+---------+--------+--------+

 

 

▶  뷰( view ) 조회

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


// view만 나온다.
SHOW FULL TABLES WHERE TABLE_TYPE='VIEW';
+------------------+------------+
| Tables_in_sample | Table_type |
+------------------+------------+
| emp_v1           | VIEW       |
+------------------+------------+


// view를 생성할때 쳤던 문장들
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      |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+

 

 

▶  인라인 뷰

•  서브쿼리를 이용해서 뷰를 만드는 것

•  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 |
+----------+----------+------+-------+------+

 

 

▶  뷰( view ) 수정

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 |
+-------+-------+---------+------+------------+---------+------+--------+

 

 

▶  뷰( view) 삭제

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 |
+------------------+------------+

 

 

인덱스

 

•  검색속도를 높이기 위해 사용하는 하나의 기술

•  테이블 내 데이터를 정렬한 뒤, 필요로 하는 데이터를 빠르게 가지고 오는게 가능해 진다.

 

 

▶ B - tree

•  인덱스를 이루고 있는 자료구조의 일종

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

•  중간값 정렬방법이라고도 불린다.

 

 

 

▶  자동 INDEX

 

•  DEPT 테이블의 인덱스(  Index ) 확인

SHOW INDEX FROM DEPT;

 

•  새 테이블 생성 뒤, 인덱스( Index ) 조회

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

SHOW INDEX FROM DEPT_I;
Empty set (0.008 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.008 sec)

 

 

 

▶  수동 INDEX

 

•  수동으로 인덱스 생성

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.007 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      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

 

 

▶  인덱스( Index ) 리빌딩

•  만약 INSERT , UPDATE 할때는 INDEX 테이블은 잠시 중단되게 된다.

 

•  따라서 INSERT 나  UPDATE 한 이후에는 인덱스 테이블은 다시 만들어야 하는것

   보통 새벽에 많이 실시된다. ( ex) 은행이 새벽에 서비스 잠시 중지하는 것 )

 

 

데이터베이스 권한

 

•  특정 데이터베이스만 사용할 수 있게 만든다.

•  권한부여

  -  grant 권한 on 데이터베이스명

•  권한 박탈

  -  revoke 권한 on 데이터베이스명

 

 

▶  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@'%';

 

 

▶  모든 권한

사용자가 tester2일 때
갖고있는 권한 확인 후, sample 데이터베이스에 대한 모든 권한 부여

show grants for tester2@'%';
grant all privileges on sample.* to tester2@'%';


// 모든 데이터베이스의 모든 권한을 부여
grant all privileges on *.* to tester2@'%';


// sample 데이터에 대한 모든 권한을 삭제 후, 갖고있는 권한 확인
revoke all on sample.* from tester2@'%';
show grants for tester2@'%';

 

 

▶  부분권한

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 : 리스토어 명령어

 

 

▶  백업( Back up ) 하기

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

exit

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

 

 

▶  백업 ( Back up ) 파일 실행 / 리스토어

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         |
+------------------+

 

 

▶  특정 테이블만 백업 / 백업받은 emp 테이블 리스토어

// emp 테이블 백업
mysql -u root -p sample emp > c:\sql\emp.sql


// 백업받은 emp 테이블 다시 리스토어
DROP TABLE EMP;

EXIT

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

 

 

 

 

반응형
저작자표시 비영리 변경금지

'■ 프로그래밍 SKILLS > SQL' 카테고리의 다른 글

[ 프로그래머스 / SQL ] SELECT문 - 역순 정렬하기  (0) 2022.06.09
[ 프로그래머스 / SQL ] SELECT문 - 모든 레코드 조회하기  (0) 2022.06.09
[ SQL ] 서브쿼리( 단일행, 복수행 ) / Join(inner, outer) / DDL(create, alter, drop) / insert / update / delete ...  (0) 2022.05.13
[ SQL ] SELECT문 ( WHERE / LIKE / IS NULL / IN / BETWEEN / .. 등 ) , 단일행함수, 그룹함수, Group by절  (0) 2022.05.12
[ SQL ] MariaDB설치, 데이터베이스 개념, 기본 명령어  (0) 2022.05.12
'■ 프로그래밍 SKILLS/SQL' Related Articles more
Comments