파이썬/제어문/저장 프로시저/if, case, while문/함수/트리거/터널링-원격DB서버접속 (교육 56일차)

##########
📌 파이썬 📌
##########

함수는 1개 이상의 명령어를 모아 놓은 것이고 함수를 실행하면 여러 개 모아놓은 명령어들이 실행된다.

함수 종류
라이브러리 함수 : 파이썬을 설치하면 제공되는 함수 (print, input)
패키지 함수 : 패키지를 설치하면 제공되는 함수
사용자 정의 함수 : 사용자가 직접 만들어서 사용하는 함수

함수 정의 : 함수를 만드는 것을 말한다.
함수 호출 : 함수를 실행하는 것을 말한다.

인수, 인자, argument :
-함수로 전달되는 값을 말한다.
-함수 호출 부분에 있다. e.g.) max(1,2), print('Hello')
-함수를 호출할 때 인수를 줄 수도 있고 안줄 수도 있다.
-인수를 준다라는 것은 함수에 매개변수가 있다라는 것을 의미한다.
-인수를 안준다라는 것은 함수에 매개변수가 없다라는 것을 의미한다.

매개변수, parameter :
-함수 내부에서 함수로 전달된 값을 저장한 변수를 말한다.
-함수 정의 부분에 있다.
-함수를 정의할 때 매개변수가 있을 수도 있고 없을 수도 있다.

비주얼 스투디오 코드 실행
윈도우 키 + R -> code 엔터

# 함수 정의 형식 :
# def 함수명(매개변수):
#     실행문
#        :
#     return 값

# 함수 호출 형식:
# 함수명()
# 함수명(인수 or 인자)

-- function1.py --

# 함수 정의
def printValue1():  # 매개 변수가 없는 경우
    a = 1
    print(a)

def printValue2(a): # 매개 변수가 있는 경우
    print(a)

# 함수 호출
printValue1()  # 매개 변수가 없는 함수를 호출할 때 인수를 쓰지 않는다.
printValue2(1) # 매개 변수가 있는 함수를 호출할 때 인수를 써준다.
printValue2(2)
printValue2(3)

-- function1.py --

-- function2.py --

# 함수를 호출할 때 값을 두 개 던져서 호출한다.
# 함수안에서는 받은 두 값을 더해서 다시 호출한쪽으로 돌려준다. (리턴값)
# 함수명은 hap이라는 이름으로 정의한다.
# 함수 정의 형식 :
def hap(x,y):
    sum = x + y
    return sum

print(hap(2,3))    
-- function2.py --

-- function3.py --

"""
파일명: function3.py
프로그램 설명: 구구단을 출력하는 프로그램
"""
# gugudan 함수 정의
# 함수명: gugudan
# 매개변수: 없음
# 리턴값: 없음
# 함수 기능: 구구단 2단 출력
def gugudan():
    dan = 2
    i = 1
    while i <= 9:
        print(f"2 x {i} = {dan * i}")
        i = i + 1

gugudan()

-- function3.py --

-- function4.py --

"""
파일명: function4.py
프로그램 설명: 구구단을 출력하는 프로그램
"""
# gugudan 함수 정의
# 함수명: gugudan
# 매개변수: 있음
# 리턴값: 없음
# 함수 기능: 구구단 2단 출력
def gugudan(dan):

    i = 1
    while i <= 9:
        print(f"{dan} x {i} = {dan * i}")
        i = i + 1

gugudan(4)

-- function4.py --

###########
📌 제어문 📌
###########

제어문이란 프로그램의 실행 흐름을 제어할 때 사용하는 문장이다.

while문은 반복할 때 사용하는 반복문이다.

형식 1 : 
while 조건식:
    실행문   <-- 참일 때 실행할 실행문
       :

형식 2 :
while 조건식:
    실행문   <-- 참일 때 실행할 실행문
       :
else:        
    실행문   <-- 거짓일 때 실행할 실행문
       :

-- whileTest1.py --

# 초기값
# 조건식
# 실행문
# 증감식

i = 1
print(i)
i = i + 1  # 1증가
print(i)
i = i + 1  # 1증가
print(i)
i = i + 1  # 1증가
print(i)
i = i + 1  # 1증가
print(i)

# i를 증가하면서 반복하는 형태
# 1 ~ 5까지 출력하는 코드
# 1 ~ 5까지 관계연산자로 표현하면 ?
# i <= 5, i < 6, 5 >= i, 6 > i 

i = 1          # 초기값
while i <= 5:  # 조건식
    print(i)   # 실행문
    i = i + 1  # 증감식

print('프로그램 종료')

-- whileTest1.py --

if 문
if문은 조건문으로 프로그램의 흐름을 제어할 때 사용하는 제어문이다.


예를 들어서 

우산을 챙긴다.
구두를 신는다.
외출한다.

if 밖에 비가오는가 ? :
    우산을 챙긴다.
    구두를 신는다.
else:
    운동화를 신는다.

외출한다.


if문은 4가지 종류가 있다.
단일 if문, if~else문, 다중if문, 중첩if문
단일 if문 형식 : 
if 조건식 :
    실행문  <-- 조건식이 참이면 실행문이 실행된다.
      :

if ~ else 문 :
if 조건식 :
    실행문1  <-- 조건식이 참이면 실행문1이 실행된다.
       :
else:
    실행문2  <-- 조건식이 거짓이면 실행문2가 실행된다.
       :

다중 if문 형식 :
if 조건식1 :
    실행문1   <-- 조건식1이 참이면 실행문1이 실행된다.
      :
elif 조건식2: <-- 조건식1이 거짓이면 조건식2를 실행한다. 
    실행문2   <-- 조건식2가 참이면 실행문2가 실행된다.
      :     
elif 조건식3: <-- 조건식2가 거짓이면 조건식3을 실행한다. 
    실행문3   <-- 조건식3이 참이면 실행문3이 실행된다.
      :
elif 조건식4: <-- 조건식3이 거짓이면 조건식4를 실행한다. 
    실행문4   <-- 조건식4가 참이면 실행문4가 실행된다.
      :
else:         <-- 조건식4가 거짓이면 이 부분이 실행한다.  (생략 가능)
    실행문5   <-- 조건식4가 기짓이면 실행문5가 실행된다.
      :

중첩 if문
if 조건식 :
    실행문  <-- 조건식이 참이면 실행문이 실행된다.
      :     
    if 조건식 :
        실행문
          :

자료형의 참과 거짓의 판단 기준
         True              False
숫자     1(0이 아닌 숫자)  0          
문자열   "test"            ""
리스트   [1,2,3]           []
튜플     (1,2,3)           ()
딕셔너리 {"a" : "b"}       {}
불리언   True              False

-- ifTest1.py --

i = 1
if i <= 5:
    print('5보다 작습니다.')

if i <= -1:
    print('-1보다 작습니다.')
else:
    print('-1보다 큽니다.')

-- ifTest1.py --

-- ifTest2.py --

i = 1
if i == 5:
    print('5와 같습니다.')
elif i == 4:
    print('4와 같습니다.')    
elif i == 3:
    print('3과 같습니다.')        
elif i == 2:
    print('2와 같습니다.')        
elif i == 1:
    print('1와 같습니다.')        
else:
    print('1 ~ 5가 아닙니다.')   

print('프로그램 종료')        

-- ifTest2.py --

# 모듈 사용하기
# 함수, 클래스를 모아둔 파이썬 파일이다.
# 형식 : 
# import 모듈명
# import 모듈명 as 별명
# from 모듈명 import *
# from 모듈명 import 모듈함수

import random
# fruits = ['바나나', '수박', '딸기', '포도', '사과']
# print(fruits)  # ['바나나', '수박', '딸기', '포도', '사과']
# # 리스트에서 아무 값 1개를 무작위로 출력한다.
# print(random.choice(fruits))
# fruits.append('복숭아')
# fruits.append('토마토') 
# print(fruits)  # ['바나나', '수박', '딸기', '포도', '사과', '복숭아', '토마토']
# print(random.sample(fruits, 3))  # 전체중에서 랜덤으로 3개 출력한다.  ['딸기', '토마토', '사과']
# random.shuffle(fruits)  # 과일 섞기
# print(fruits)
# 로또 번호 : 1 ~ 45 (6개 숫자가 일치해야 한다.)
lottoNumber = [i for i in range(1,46)]
# print(lottoNumber)
# random.shuffle(lottoNumber)  # 숫자 섞기
# print(lottoNumber)


# 사용자에게 숫자를 입력 받아서 개수만큼 로또번호를 반복해서 출력한다.
# 엔터치면 1번만 출력한다.
# 반복은 for문을 이용한다.
#
# 실행 결과
# 몇 번 반복하시겠습니까 ? 3
# 1. [...]
# 2. [...]
# 3. [...]
#
# 몇 번 반복하시겠습니까 ? 
# 1. [...]

-- lotto.py --

"""
파일명 : lotto.py
프로그램 설명 :  랜덤 모듈을 이용한 로또 번호 추출기
"""

import random

lottoNumber = [i for i in range(1,46)]

count = input('몇 번 반복하시겠습니까 ? ')
count = int(count) if count.isdigit() else 1

i = 1
while i <= count:
    random.shuffle(lottoNumber)  # 숫자 섞기
    a = random.sample(lottoNumber, 6)  # 6개 숫자 뽑기
    a.sort()  # 6개의 숫자를 오름차순으로 정렬한다.
    if count >= 10:
        print(f'{i:>2}. {a}')
    else:
        print(f'{i}. {a}')
    i = i + 1

-- lotto.py --

실습> 매개변수가 있는 저장 프로시저 생성하기

# arg_test 프로시저 정의
# 프로시저명: arg_test
# 매개변수: 있음 p_id
# 프로시저 기능: naver_db.member 테이블에서 p_id에 해당하는 사용자를 검색한다.

1. 프로시저 정의
매개변수가 있는 저장 프로시저 arg_test를 생성한다.

DELIMITER //
CREATE PROCEDURE arg_test(p_id varchar(32))
BEGIN
    SELECT * FROM member WHERE id = p_id;
END //
DELIMITER ;

2. 프로시저 호출
MariaDB [naver_db]> CALL arg_test('shlee');

+----+-------+-----------+------+----------+----------------------------+----------+------+
| no | id    | name      | sex  | post_num | address                    | tel      | age  |
+----+-------+-----------+------+----------+----------------------------+----------+------+
|  4 | shlee | 이상훈    | M    | 503-200  | 광주시 남구 도금동         | 838-4347 |   32 |
+----+-------+-----------+------+----------+----------------------------+----------+------+
1 row in set (0.00 sec)


Query OK, 0 rows affected (0.00 sec)

MariaDB [naver_db]> CALL arg_test('hnjang');

+----+--------+-----------+------+----------+-----------------------------+----------+------+
| no | id     | name      | sex  | post_num | address                     | tel      | age  |
+----+--------+-----------+------+----------+-----------------------------+----------+------+
| 13 | hnjang | 장한나    | W    | 441-081  | 광주시 서구 화정4동         | 845-4547 |   58 |
+----+--------+-----------+------+----------+-----------------------------+----------+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

실습> 프로시저에서 변수 사용하기

DELIMITER //
CREATE PROCEDURE var_test()
BEGIN
	DECLARE name VARCHAR(32);
	DECLARE age INT DEFAULT 1;
	SET name = '홍길동';
	SELECT name, age;
END //
DELIMITER ;

CALL var_test

실습> if문

일반적인 프로그램언어의 if와 동일하지만 키워드들이 약간씩 차이가 있다.

DELIMITER //
CREATE PROCEDURE naver_db.if_test(num int)
BEGIN
	if num > 0 then
		SELECT '0보다 큼' as result;
	elseif num < 0 then
		SELECT '0보다 작음' as result;
	else
		SELECT '0과 같음' as result;
	end if;
END //
DELIMITER ;

CALL if_test(10);
CALL if_test(-5);
CALL if_test(0);

실습> case문

C언어: switch ~ case문
다중 if문과 비슷하다.

DELIMITER //
CREATE PROCEDURE naver_db.case_test(num int)
BEGIN
	case num
	when 1 then
		SELECT '월요일';
	when 2 then
		SELECT '화요일';
	when 3 then
		SELECT '수요일';
	else
		SELECT '잘못입력';
	end case;	
			
END //
DELIMITER ;

CALL case_test(1);
CALL case_test(2);
CALL case_test(3);
CALL case_test(4);

실습> WHILE문

DELIMITER //
CREATE PROCEDURE naver_db.while_test(count int)
BEGIN

	DECLARE tmp INT DEFAULT 0;
	
	WHILE tmp < count DO
		SELECT tmp;
		SET tmp = tmp + 1;
	END WHILE;			
END //
DELIMITER ;

CALL while_test(1);
CALL while_test(2);
CALL while_test(3);
CALL while_test(4);

실습> C 언어의 함수

# yum -y install gcc
# vi hap.c 
#include <stdio.h>

int hap(int x, int y)
{
    int sum;
    sum = x + y;
    return sum;
}

int main()
{
    printf("%d \n", hap(1,2));
    return 0;
}
# gcc -o hap hap.c
# ./hap
3

실습> 함수

프로시저        VS      함수
리턴값 X                리턴값 O
CALL을 이용해서 호출    CALL을 이용하지 않고 호출한다.

-- 저장 함수 정의
DELIMITER //
CREATE FUNCTION func_test(num1 int, num2 int) RETURNS int
BEGIN
	RETURN num1 + num2;
END //
DELIMITER ;

-- 저장 함수 호출
SELECT func_test(10, 20);
-- SELECT CALL func_test(10, 10);  CALL은 프로시저를 호출할 때 사용한다.

-- 저장 함수 확인
SHOW FUNCTION STATUS;
SHOW CREATE FUNCTION func_test;

-- 저장 함수 삭제
DROP FUNCTION func_test;

실습> 함수 사용하기

MariaDB [naver_db]> DELIMITER //
MariaDB [naver_db]> CREATE FUNCTION func_test(num1 int, num2 int) RETURNS int
    -> BEGIN
    -> RETURN num1 + num2;
    -> END //
Query OK, 0 rows affected (0.00 sec)

MariaDB [naver_db]> DELIMITER ;

MariaDB [naver_db]> CREATE TABLE jumsu(id int);
Query OK, 0 rows affected (0.00 sec)

MariaDB [naver_db]> INSERT INTO jumsu VALUES(1);
Query OK, 1 row affected (0.00 sec)

MariaDB [naver_db]> INSERT INTO jumsu VALUES(func_test(10,20));  -- 함수를 호출해서 값을 저장한 경우
Query OK, 1 row affected (0.00 sec)

MariaDB [naver_db]> SELECT * FROM jumsu;
+------+
| id   |
+------+
|    1 |
|   30 |  <-- func_test() 함수에 의해서 값이 저장된 경우
+------+
2 rows in set (0.00 sec)

실습> 저장함수 sum_function

sum_function 프로시저 정의
함수명: sum_function
매개변수: 있음, 정수 2개(num1, num2)
리턴값: 있음, 정수
기능: 숫자(정수) 두 개를 입력 받아서 작은 수에서 큰 수까지의 합을 연산하는 함수 작성

백준 알고리즘

DELIMITER //
CREATE FUNCTION sum_function(num1 int, num2 int) RETURNS int
BEGIN
	DECLARE tmp INT;
	if num1 > num2 then
		SET tmp = num1;
		SET num1 = num2;
		SET num2 = tmp;
	end if;
	
	SET tmp = 0;
	while num1 <= num2 do
		SET tmp = tmp + num1;
		SET num1 = num1 + 1;
	end while;
	return tmp;
END //
DELIMITER ;

BP(Break Point) 설정 -> F5(디버깅 시작) -> F11(Step info) -> 코드 분석

-- function5.py --

def sum_function(num1, num2):  
    if num1 > num2:
        tmp = num1
        num1 = num2
        num2 = tmp

    tmp = 0
    while num1 <= num2:
        tmp = tmp + num1
        num1 = num1 + 1
	
    return tmp

print(sum_function(10,20))

-- function5.py --

#vi function5.c
-- function5.c --

#include <stdio.h>

int sum_function(int num1, int num2)
{
    int tmp;
    if(num1 > num2)
    {
        tmp = num1;
        num1 = num2;
        num2 = tmp;
    }

    tmp = 0;
    while(num1 <= num2)
    {
        tmp = tmp + num1;
        num1 = num1 + 1;
    } 

    return tmp;
}

int main()
{
    printf("%d\n", sum_function(10,20));
    return 0;
}

-- function5.c --

# gcc -o function5 function5.c 
# ./function5 
165

실습> 트리거

Trigger: 방아쇠(n)

-- 트리거 생성
CREATE TRIGGER ...

-- 트리거 확인
SHOW TRIGGERS;

-- 트리거 삭제
DROP TRIGGER 트리거명;

USE naver_db
CREATE TABLE trig_test
(
    event_time char(16),
    count int(10) unsigned
);

INSERT INTO trig_test VALUES
('INSERT_BEFORE', 0),
('INSERT_AFTER',  0),
('UPDATE_BEFORE', 0),
('UPDATE_AFTER',  0),
('DELETE_BEFORE', 0),
('DELETE_AFTER',  0);

MariaDB [naver_db]> DESC trig_test;

+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| event_time | char(16)         | YES  |     | NULL    |       |
| count      | int(10) unsigned | YES  |     | NULL    |       |
+------------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

MariaDB [naver_db]> SELECT * FROM trig_test;

+---------------+-------+
| event_time    | count |
+---------------+-------+
| INSERT_BEFORE |     0 |
| INSERT_AFTER  |     0 |
| UPDATE_BEFORE |     0 |
| UPDATE_AFTER  |     0 |
| DELETE_BEFORE |     0 |
| DELETE_AFTER  |     0 |
+---------------+-------+
6 rows in set (0.00 sec)

-- UPDATE 트리거 생성

DELIMITER //
CREATE TRIGGER trig_test AFTER UPDATE ON member
FOR EACH ROW
BEGIN
	UPDATE trig_test SET count=count+1
	WHERE event_time = 'UPDATE_AFTER';
END //
DELIMITER ;

MariaDB [naver_db]> DELIMITER //
MariaDB [naver_db]> CREATE TRIGGER trig_test AFTER UPDATE ON member
    -> FOR EACH ROW
    -> BEGIN
    -> UPDATE trig_test SET count=count+1
    -> WHERE event_time = 'UPDATE_AFTER';
    -> END //
Query OK, 0 rows affected (0.01 sec)

MariaDB [naver_db]> DELIMITER ;

MariaDB [naver_db]> SHOW TRIGGERS\G
*************************** 1. row ***************************
             Trigger: trig_test
               Event: UPDATE
               Table: member
           Statement: BEGIN
UPDATE trig_test SET count=count+1
WHERE event_time = 'UPDATE_AFTER';
END
              Timing: AFTER
             Created: NULL
            sql_mode: 
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)


MariaDB [naver_db]> SELECT * FROM member;
+----+---------+--------------+------+----------+---------------------------------------------+--------------+------+
| no | id      | name         | sex  | post_num | address                                     | tel          | age  |
+----+---------+--------------+------+----------+---------------------------------------------+--------------+------+
|  1 | yjhwang | 황영주       | M    | 100-011  | 서울시 중구 충무로1가                       | 234-8879     |   35 |
|  2 | khshul  | 설기형       | M    | 607-010  | 부산시 동래구 명륜동                        | 764-3784     |   33 |
|  3 | chpark  | 박철호       | M    | 503-200  | 광주시 남구 지석동                          | 298-9730     |   34 |
|  4 | shlee   | 이상훈       | M    | 503-200  | 광주시 남구 도금동                          | 838-4347     |   32 |
|  5 | jyjang  | 장영숙       | W    | 503-201  | 부산시 영도구 봉래동5가                     | 399-9809     |   24 |
|  6 | yjbae   | 배용진       | M    | 606-065  | 서울시 은평구 응암4동                       | 857-5683     |   30 |
|  7 | hbpark  | 박혜빈       | W    | 122-014  | 경기도 과천시 중앙동                        | 234-7677     |   22 |
|  8 | mskim   | 김문수       | M    | 427-760  | 경기도 시흥시 신천동                        | 370-6003     |   63 |
|  9 | bkcha   | 차범길       | M    | 429-020  | 대전시 서구 둔산1동                         | 432-9877     |   49 |
| 10 | kskim   | 김길수       | M    | 302-121  | 경기도 수원시 장안구 파장동                 | 324-5875     |   54 |
| 11 | srkim   | 김수련       | M    | 440-747  | 대구시 달서구 신당동                        | 987-3688     |   23 |
| 12 | srlee   | 이성현       | M    | 704-701  | 경기도 수원시 권선구 매산로1가              | 243-6844     |   36 |
| 13 | hnjang  | 정한나       | W    | 441-081  | 광주시 서구 화정4동                         | 845-4547     |   58 |
| 14 | mylee   | 이명연       | W    | 502-791  | 광주시 서구 쌍촌동                          | 837-9432     |   33 |
| 15 | yskim   | 김영숙       | W    | 429-010  | 경기도 시흥시 대야동                        | 374-8438     |   53 |
| 16 | khuser  | 케이         | M    | 111-222  | 서울시 강남구 역삼동                        | 02-1111-2222 |   17 |
| 17 | kuser1  | 케이유저     | W    | NULL     | NULL                                        | NULL         |   20 |
+----+---------+--------------+------+----------+---------------------------------------------+--------------+------+
17 rows in set (0.00 sec)

MariaDB [naver_db]> SELECT * FROM trig_test;
+---------------+-------+
| event_time    | count |
+---------------+-------+
| INSERT_BEFORE |     0 |
| INSERT_AFTER  |     0 |
| UPDATE_BEFORE |     0 |
| UPDATE_AFTER  |     0 |
| DELETE_BEFORE |     0 |
| DELETE_AFTER  |     0 |
+---------------+-------+
6 rows in set (0.00 sec)

UPDATE 방아쇠가 당겨졌기 때문에 trig_test 총알이 발사 되었다.
MariaDB [naver_db]> UPDATE member SET sex='A' WHERE no=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

UPDATE_AFTER 컬럼이 1이 증가되었다.
MariaDB [naver_db]> SELECT * FROM trig_test;
+---------------+-------+
| event_time    | count |
+---------------+-------+
| INSERT_BEFORE |     0 |
| INSERT_AFTER  |     0 |
| UPDATE_BEFORE |     0 |
| UPDATE_AFTER  |     1 |  <--  UPDATE trig_test SET count=count+1 WHERE event_time = 'UPDATE_AFTER';
| DELETE_BEFORE |     0 |
| DELETE_AFTER  |     0 |
+---------------+-------+
6 rows in set (0.00 sec)

MariaDB [naver_db]> UPDATE member SET sex='A' WHERE sex='M';
Query OK, 10 rows affected (0.00 sec)
Rows matched: 10  Changed: 10  Warnings: 0

MariaDB [naver_db]> SELECT * FROM trig_test;
+---------------+-------+
| event_time    | count |
+---------------+-------+
| INSERT_BEFORE |     0 |
| INSERT_AFTER  |     0 |
| UPDATE_BEFORE |     0 |
| UPDATE_AFTER  |    11 |
| DELETE_BEFORE |     0 |
| DELETE_AFTER  |     0 |
+---------------+-------+
6 rows in set (0.00 sec)

MariaDB [naver_db]> DROP TRIGGER trig_test;
Query OK, 0 rows affected (0.00 sec)

MariaDB [naver_db]> SHOW TRIGGERS\G
Empty set (0.00 sec)

실습> 회원 탈퇴 trigger 생성하기

1. 테이블 생성
트리거가 실행되면서 저장할 out_member 테이블을 생성한다.

CREATE TABLE out_member
(
    no int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '번호',
    id varchar(32) NOT NULL UNIQUE COMMENT '아이디',
    name varchar(32) NOT NULL COMMENT '이름'
)ENGINE=InnoDB DEFAULT CHARSET='utf8';

MariaDB [naver_db]> DESC out_member;

+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| no    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| id    | varchar(32)      | NO   | UNI | NULL    |                |
| name  | varchar(32)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set, 3 warnings (0.00 sec)

2. 트리거 생성
out_mem_trigg 트리거를 생성한다.

DELIMITER //
CREATE TRIGGER out_mem_trigg BEFORE DELETE ON member
FOR EACH ROW
BEGIN
    INSERT INTO out_member VALUES('', OLD.id, OLD.name);
END //
DELIMITER ;

SELECT * FROM member;
SELECT * FROM out_member;

3. 회원 탈퇴 1
이상훈 회원이 탈퇴하면서 member 테이블에서 삭제될 때 미리 생성된 DELETE 트리거가 실행되고(DELETE 방아쇠가 당겨지고)
사용자 아이디와 이름이 out_member 테이블에 저장된다.(총알이 자동으로 발사되서 out_member에 자동으로 저장된다.)
MariaDB [naver_db]> DELETE FROM member WHERE id='shlee';
MariaDB [naver_db]> SELECT * FROM out_member;

+----+-------+-----------+
| no | id    | name      |
+----+-------+-----------+
|  1 | shlee | 이상훈    |
+----+-------+-----------+
1 row in set (0.00 sec)

4. 회원 탈퇴 2
장한나 회원이 탈퇴하면서 member 테이블에서 삭제될 때 미리 생성된 DELETE 트리거가 실행되고(DELETE 방아쇠가 당겨지고)
사용자 아이디와 이름이 out_member 테이블에 저장된다.(총알이 자동으로 발사되서 out_member에 자동으로 저장된다.)
MariaDB [naver_db]> DELETE FROM member WHERE id='hnjang';
MariaDB [naver_db]> SELECT * FROM out_member;

+----+--------+-----------+
| no | id     | name      |
+----+--------+-----------+
|  1 | shlee  | 이상훈    |
|  2 | hnjang | 장한나    |
+----+--------+-----------+
2 rows in set (0.00 sec)

실습> 터널링을 이용한 원격 DB서버 접속하기

             SSH 채널
      3307              3306
[Client]------------------[Server]
   .1                        .20
         200.200.200.0/24

!!! 중요 !!!
DBMS Client 프로그램들은 모두 SSH 터널을 제공한다.
DBeaver:
-SSH 터널 탭에서 설정한다.

Heidi SQL:
-네트워크 유형: MariaDB or MySQL (SSH tunnel)
-SSH 터널 탭에서 설정한다.

SSH 터널링 설정 전

SSH 터널링 설정 후

SSH 터널링 설정 전

SSH 터널링 설정 후

1. IP주소 설정
공통으로 사용할 IP주소를 설정한다.

# ip address add 200.200.200.20/24 dev ens33

2. SSH 사용자 생성
터널링을 연결할 사용자를 추가한다.

# useradd -g users sshuser
# passwd sshuser

3. mysql 사용자 삭제
root@localhost 만 빼고 모두 삭제한다.
각자 사용자가 다르므로 자신의 값을 확인한 후 삭제한다.

# mysql mysql

MariaDB [mysql]> SELECT host,user,password FROM user;

+---------------+----------+-------------------------------------------+
| host          | user     | password                                  |
+---------------+----------+-------------------------------------------+
| localhost     | root     | *8232A1298A49F710DBEE0B330C42EEC825D4190A |  <-- 이것만 남기고 나머지 사용자는 모두 삭제한다.
| 200.200.200.1 | root     | *8232A1298A49F710DBEE0B330C42EEC825D4190A |  <-- 삭제
| localhost     | kuser1   |                                           |  <-- 삭제
| localhost     | kuser2   | *8232A1298A49F710DBEE0B330C42EEC825D4190A |  <-- 삭제
| 200.200.200.% | root     | *8232A1298A49F710DBEE0B330C42EEC825D4190A |  <-- 삭제
| localhost     | user1    | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |  <-- 삭제
| localhost     | shopuser | *8232A1298A49F710DBEE0B330C42EEC825D4190A |  <-- 삭제
+---------------+----------+-------------------------------------------+
7 rows in set (0.00 sec)

DROP USER root@200.200.200.1;
DROP USER kuser1@localhost;
DROP USER kuser2@localhost;
DROP USER root@'200.200.200.%';
DROP USER user1@localhost;
DROP USER shopuser@localhost;

MariaDB [mysql]> SELECT host,user,password FROM user;

+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *8232A1298A49F710DBEE0B330C42EEC825D4190A |
+-----------+------+-------------------------------------------+
1 row in set (0.00 sec)

MariaDB [mysql]> exit

4. 로컬 포트 오픈
/etc/my.cnf 파일에 bind-address=127.0.0.1을 추가한다.

# vi /etc/my.cnf
[mysqld]
  :
  :(생략)
bind-address=127.0.0.1   

DBMS를 재시작하고 포트를 확인하면 내부에서만 열려있고 외부에서는 접속 할 수 없는 상태가 된다.

# systemctl restart mariadb
# ss -nlt|grep 3306
LISTEN     0      50     127.0.0.1:3306                     *:* 

5. SSH 포트 포워딩 연결
윈도우에서 ssh 명령어로 접속을 한다.
-L: 로컬 포워딩
3307: Local Port
127.0.0.1: 자기 자신
3306: Remote Port
sshuser@200.200.200.20: sshuser로 200.200.200.20으로 접속
-f -N: 백그라운드 프로세스

C:\Users\user>ssh
usage: ssh [-46AaCfGgKkMNnqsTtVvXxYy] [-B bind_interface]
           [-b bind_address] [-c cipher_spec] [-D [bind_address:]port]
           [-E log_file] [-e escape_char] [-F configfile] [-I pkcs11]
           [-i identity_file] [-J [user@]host[:port]] [-L address]
           [-l login_name] [-m mac_spec] [-O ctl_cmd] [-o option] [-p port]
           [-Q query_option] [-R address] [-S ctl_path] [-W host:port]
           [-w local_tun[:remote_tun]] destination [command]

cmd창에서 연결을 하고 연결이 되면 X 버튼을 눌러서 종료한다.

C:\Users\user>ssh -L 3307:127.0.0.1:3306 sshuser@200.200.200.20 -f -N
sshuser@200.200.200.20's password:

3307 포트가 나오면 DBMS 서버와 연결이 잘 되어있는 상태이다.

C:\Users\user2>netstat -na|findstr 3307

TCP    127.0.0.1:3307         0.0.0.0:0              LISTENING
 TCP    [::1]:3307             [::]:0                 LISTENING

6. DB서버 접속
C:\Users\user>mysql -h localhost -P 3307 -u root -pP@ssw0rd
MariaDB [(none)]> SELECT user();

+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.001 sec)

MariaDB [(none)]> SHOW PROCESSLIST;

+----+------+-----------------+------+---------+------+-------+------------------+----------+
| Id | User | Host            | db   | Command | Time | State | Info             | Progress |
+----+------+-----------------+------+---------+------+-------+------------------+----------+
|  5 | root | localhost:52994 | NULL | Query   |    0 | NULL  | SHOW PROCESSLIST |    0.000 |
+----+------+-----------------+------+---------+------+-------+------------------+----------+
1 row in set (0.001 sec)

MariaDB [(none)]> exit

윈도우에서 ssh 프로세스 종료

실습> 아래 내용을 확인하기

법제처:
https://www.moleg.go.kr/
-정보통신망법 검색
-개인정보보호법 검색

KISA
https://isms.kisa.or.kr/

개인정보보호에 대해서 알아보기
-개인정보보호 포털: http://privacy.go.kr

구글 검색
-정보보안 업무 처리 지침 pdf
-보안 업무 취급 규정 pdf