혼공스를 시작한 지 얼마 되지도 않은 것 같은데. . .
벌써 마지막 주차라니 뭔가. . 기분이 요상하네요ㅜ-ㅜ
마지막 공부도 레츠 고~
스토어드 프로시저 사용 방법
스토어드 프로시저는 SQL 프로그래밍 기능을 추가해서 일반 프로그래밍 언어와 비슷한 효과를 낼 수 있다.
스토어드 프로시저 기본
스토어드 프로시저의 개념과 형식
스토어드 프로시저(저장 프로시저)란 MySQL에서 제공하는 프로그래밍 기능이다.
또한 스토어드 프로시저는 쿼리 문의 집합으로도 볼 수 있으며, 어떠한 동작을 일괄 처리하기 위한 용도로도 사용된다.
자주 사용하는 일반적인 쿼리는 스토어드 프로시저로 묶어 호출을 해 사용하는 것이 훨씬 편리하다! ^_~
스토어드 프로시저의 형식을 보여주겟다 - !
DELIMITER $$
CREATE PROCEDURE 스토어드_프로시저_이름 ( IN 또는 OUT 매개변수 )
BEGIN
이 부분에 SQL 프로그래밍 코드를 작성
END $$
DELIMITER ;
$$는 $ 한 개만 사용해도 되지만 명확하게 표현하기 위해 두 개를 사용한다.
##, %%, &&, // 등을 사용해도 괜찮다~ :D
세미콜론으로 SQL의 끝을 표시하고 $$는 스토어드 프로시저의 끝으로 사용한다.
스토어드 프로시저를 아직 만들기만 하였지 호출을 하지 않았다.
스토어드 프로시저를 호출하는 형식은
CALL 스토어드_프로시저_이름();
이다! -3-
필요하다면 괄호 안에 매개변수를 넣어 사용할 수 있다!
스토어드 프로시저의 생성
USE market_db;
DROP PROCEDURE IF EXISTS user_proc;
DELIMITER $$
CREATE PROCEDURE user_proc()
BEGIN
SELECT * FROM member;
END $$
DELIMITER ;
CALL user_proc();
스토어드 프로시저의 삭제
DROP PROCEDURE user_proc;
을 통해 스토어드 프로시저를 삭제할 수 있다.
스토어드 프로시저 실습
매개변수의 사용
스토어드 프로시저에서는 실행 시 입력 매개변수를 지정할 수 있다.
입력 매개변수를 지정하는 형식은
IN 입력_매개변수_이름 데이터_형식
이며, 입력 매개변수가 있는 스토어드 프로시저를 실행하기 위해서는
CALL 프로시저_이름(전달_값);
이렇게 작성해주면 된다. ~-~
스토어드 프로시저에서 처리된 결과를 출력 매개변수를 통해 얻을 수 있다.
출력 매개변수의 형식은
OUT 출력_매개변수_이름 데이터_형식
이며, 출력 매개변수에 값을 대입하기 위해서는 주로 SELECT ~ INTO 문을 사용한다.
출력 매개변수가 있는 스토어드 프로시저를 실행하기 위해서는
CALL 프로시저_이름(@변수명)
SELECT @변수명;
이라 작성하면 된다. (*￣3￣)╭
입력 매개변수의 활용
USE market_db;
DROP PROCEDURE IF EXISTS user_proc1;
DELIMITER $$
CREATE PROCEDURE user_proc1(IN userName VARCHAR(10))
BEGIN
SELECT * FROM member WHERE mem_name = userName;
END $$
DELIMITER ;
CALL user_proc1('에이핑크');
'에이핑크'를 입력 매개변수로 전달하였다.
두 개의 입력 매개변수가 있는 스토어드 프로시저를 만들면
DROP PROCEDURE IF EXISTS user_proc2;
DELIMITER $$
CREATE PROCEDURE user_proc2(IN userNumber INT, IN userHeight INT)
BEGIN
SELECT * FROM member WHERE mem_number > userNumber AND height > userHeight;
END $$
DELIMITER ;
CALL user_proc2(6, 165);
이러한 코드가 완성되며, 모두 한 줄로 써도 상관 없다.
인원을 6으로, 평균 키를 165로 전달하였다. 이 값이 3번째 줄에 대입된다.
인원이 6을 초과하고, 키가 165를 초과하는 가수 그룹이 조회되게 된다. ^_^
출력 매개변수의 활용
DROP PROCEDURE IF EXISTS user_proc3;
DELIMITER $$
CREATE PROCEDURE user_proc3(IN txtValue CHAR(10), OUT outValue INT)
BEGIN
INSERT INTO noTable VALUES(NULL, txtValue);
SELECT MAX(id) INTO outValue FROM noTable;
END $$
DELIMITER ;
이 스토어드 프로시저는 noTable이라는 이름의 테이블에 넘겨 받은 값을 입력하고,
id 열의 최대값을 알아내는 기능을 한다. id 열의 최대값은 결국 방금 입력한 행의 순차 번호이다.
코드를 설명하자면, 출력 매개변수인 outValue를 지정하였고,
INSERT INTO 구문으로 outValue에 id 열의 최대값을 지정하였다.
noTable의 구조를 먼저 확인하기 위해서 ~ DESC 문으로 테이블을 확인한다.
하지만 이러면 에러가 발생하며 noTable이 없다고 한다. ＼（〇_ｏ）／
확인해 보니 스토어드 프로시저를 만드는 시점에는 아직 존재하지 않는 테이블을 사용해도 된다.
하지만 CALL로 실행하는 시점에는 사용한 테이블이 있어야 한다.~
그래서 이제 noTable을 만들도록 하겠당 （￣︶￣）↗
CREATE TABLE IF NOT EXISTS noTable( id INT AUTO_INCREMENT PRIMARY KEY, txt CHAR(10));
CALL user_proc3('테스트1', @myValue);
SELECT CONCAT('입력된 ID 값 ==>', @myValue);
이렇게 noTable을 만들어주었으며, 출력 매개변수 위치에 @변수명 형태로 변수를 전달해 주어 호출하였다!
CALL 두 문장을 계속 실행하면 값도 하나 씩 증가한다. ^_^
SQL 프로그래밍의 활용
가수 그룹의 데뷔 연도가 2015년 이전이면 '고참 가수', 2015년 이후(2015년 포함)이면 '신인 가수'를 출력하는
스토어드 프로시저를 만들어 보겠다 ^3^
DROP PROCEDURE IF EXISTS ifelse_proc;
DELIMITER $$
CREATE PROCEDURE ifelse_proc(
IN memName VARCHAR(10)
)
BEGIN
DECLARE debutYear INT; -- 변수 선언
SELECT YEAR(debut_date) into debutYear FROM member
WHERE mem_name = memName;
IF (debutYear >= 2015) THEN
SELECT '신인 가수네요. 화이팅 하세요.' AS '메시지';
ELSE
SELECT '고참 가수네요. 그동안 수고하셨어요.' AS '메시지';
END IF;
END $$
DELIMITER ;
CALL ifelse_proc('오마이걸');
매개변수로 가수 그룹의 이름을 넘겨 받고, 데뷔 연도를 저장할 변수를 준비하였다.
DECLARE는 변수를 선언할 때 사용된다. :D
넘겨 받은 가수 이름으로 조회한 후 debut_date 중에서 YEAR() 함수로 연도만 추출에서 변수 debutYear에 저장한다.
이번에는 여러 번 반복하는 while 문을 활용해 보겠당.
DROP PROCEDURE IF EXISTS while_proc;
DELIMITER $$
CREATE PROCEDURE while_proc()
BEGIN
DECLARE hap INT; -- 합계
DECLARE num INT; -- 1부터 100까지 증가
SET hap = 0; -- 합계 초기화
SET num = 1;
WHILE (num <= 100) DO -- 100까지 반복
SET hap = hap + num;
SET num = num + 1;
END WHILE;
SELECT hap AS '1~100 합계';
END $$
DELIMITER ;
CALL while_proc();
마지막으로는 동적 SQL을 활용해 보겠다아.
동적 SQL은 이름 그대로 다이나믹하게 SQL이 변경된다.
이 예제는 테이블을 조회하는 기능을 한다.
테이블은 고정된 것이 아닌, 테이블 이름을 매개변수로 전달 받아서 해당 테이블을 조회한다~ ;_;
DROP PROCEDURE IF EXISTS dynamic_proc;
DELIMITER $$
CREATE PROCEDURE dynamic_proc(
IN tableName VARCHAR(20)
)
BEGIN
SET @sqlQuery = CONCAT('SELECT * FROM ', tableName);
PREPARE myQuery FROM @sqlQuery;
EXECUTE myQuery;
DEALLOCATE PREPARE myQuery;
END $$
DELIMITER ;
CALL dynamic_proc('member');
넘겨 받은 테이블 이름을 @sqlQuery 변수에 SELECT 문으로 문자열을 생성해 뒀다.
결국 SELECT * FROM member 가 생성된 것이다.
스토어드 함수와 커서
스토어드 함수는 MySQL에서 제공하는 내장 함수 외에 직접 함수를 만드는 기능을 제공한다.
스토어드 함수는 스토어드 프로시저와 모양이 비슷하지만 세부적으로 다르며, 특히 용도가 다르다.
RETURNS 예약어를 통해서 하나의 값을 반환해야 하는 특징을 갖는다.
커서는 스토어드 프로시저 안에서 한 행씩 처리할 때 사용하는 프로그래밍 방식이다.
스토어드 함수
스토어드 함수의 개념과 형식
DELIMITER $$
CREATE FUNCTION 스토어드_함수_이름(매개변수)
RETURNS 반환형식
BEGIN
이 부분에 프로그래밍 콛딩
RETURN 반환값;
END $$
DELIMITER;
SELECT 스토어드_함수_이름();
스토어드 함수는 다음과 같은 형식으로 구성할 수 있다.
스토어드 함수의 형식은 스토어드 프로시저와 굉장히 유사하지만, 차이점이 있다.
1. 스토어드 함수는 RETURNS 문으로 반환할 값의 데이터 형식을 지정하고,
본문 안에서는 RETURN 문으로 하나의 값을 반환해야 한다.
2. 스토어드 함수의 매개변수는 모두 입력 매개변수이다. 그리고 IN을 붙이지 않는다.
3. 스토어드 프로시저는 CALL로 호출하지만, 스토어드 함수는 SELECT 문 안에서 호출된다.
4. 스토어드 프로시저 안에서는 SELECT 문을 사용할 수 있지만, 스토어드 함수 안에서는 사용할 수 없다.
5. 스토어드 프로시저는 여러 SQL 문이나 숫자 계산 등의 다양한 용도로 사용하지만,
스토어드 함수는 어떤 계산을 통해서 하나의 값을 반환하는 데 주로 사용된다.
스토어드 함수의 사용
스토어드 함수를 사용하려면 먼저 SQL로 스토어드 함수 생성 권한을 허용해 줘야 한다.
SET GLOBAL log_bin_trust_function_creators = 1;
USE market_db;
DROP FUNCTION IF EXISTS sumFunc;
DELIMITER $$
CREATE FUNCTION sumFunc(number1 INT, number2 INT)
RETURNS INT
BEGIN
RETURN number1 + number2;
END $$
DELIMITER ;
SELECT sumFunc(100, 200) AS '합계';
첫 번째 문장으로 허용해준 후,
숫자 두 개의 합계를 계산하는 스토어드 함수를 만들어 주었다.
2개의 정수형 매개변수를 전달 받고, 이 함수가 반환하는 데이터 형식을 정수로 지정하였다.
RETURN 문으로 정수형 결과를 변환하였다.
이번에는 데뷔 연도를 입력하면, 활동 기간이 얼마나 되었는지 출력해 주는 함수를 만들겠다.
DROP FUNCTION IF EXISTS calcYearFunc;
DELIMITER $$
CREATE FUNCTION calcYearFunc(dYear INT)
RETURNS INT
BEGIN
DECLARE runYear INT; -- 활동 기간(연도)
SET runYear = YEAR(CURDATE()) - dYear;
RETURN runYear;
END $$
DELIMITER ;
SELECT calcYearFunc(2010) AS '활동 횟수';
데뷔 연도를 매개변수로 받아 현재 연도 - 데뷔 연도를 계산하여 활동한 횟수를 구하였다.
만약 필요하다면 SELECT ~ INTO ~로 저장했다가 사용할 수도 있다.
함수의 반환값을 각 변수에 저장한 후, 그 차이를 계산해서 출력했다. 즉, 데뷔 연도와 활동 횟수 차이가 출력되었다.
SELECT calcYearFunc(2007) INTO @debut2007;
SELECT calcYearFunc(2013) INTO @debut2013;
SELECT @debut2007-@debut2013 AS '2007과 2013 차이';
함수는 주로 테이블을 조회한 후, 그 값을 계산할 때 사용된다.
회원 테이블에서 모든 회원이 데뷔한 지 몇 년이 되었는지 보겠당. :P
SELECT mem_id, mem_name, calcYearFunc(YEAR(debut_date)) AS '활동 횟수'
FROM member;
함수의 삭제는
DROP FUNCTION calcYearFunc;
를 사용한다. ^_~
커서로 한 행씩 처리하기
커서는 테이블에서 한 행씩 처리하기 위한 방식이다.
커서의 기본 개념
커서는 첫 번째 행을 처리한 후에 마지막 행까지 한 행씩 접근해서 값을 처리한다.
커서는 일반적으로 커서 선언하기 - 반복 조건 선언하기 - 커서 열기 - 데이터 가져오기 - 데이터 처리하기 - 커서 닫기
의 작동 순서로 처리되며 데이터 가져오기 - 데이터 처리하기 부분은 반복된다.
커서는 대부분 스토어드 프로시저와 함께 사용된다.
커서의 단계별 실습
회원(가수 그룹)의 평균 인원수를 구하는 스토어드 프로시저를 작성하겠다.
이번에는 커서를 활용하여 한 행씩 접근하며 회원의 인원수를 누적시키는 방식으로 처리해보겠다. ~_~
1. 사용할 변수 준비하기
회원의 평균 인원수를 계산하기 위해서 각 회원의 인원수(memNumber), 전체 인원의 합계(totNumber),
읽은 행의 수(cnt) 변수 세 개를 준비한다.
전체 인원의 합계와 읽은 행 수를 누적시키기 위해 DEFAUT 문을 사용해 초기값을 0으로 설정한다.
행의 끝을 파악하기 위한 변수 endOfRow를 준비했다. 처음엔 당연히 행의 끝이 아니니 FALSE로 초기화했다.
DECLARE memNumber INT;
DECLARE cnt INT DEFAULT 0;
DECLARE totNumber INT DEFAULT 0;
DECLARE endOfRow BOOLEAN DEFAULT FALSE;
2. 커서 선언하기
커서라는 것은 결국 SELECT 문이다.
회원 테이블(member)을 조회하는 구문을 커서로 만들어 놓으면 된다.
DECLARE memberCursor CURSOR FOR
SELECT mem_number FROM member;
3. 반복 조건 선언하기
행의 끝에 다다르면 앞에서 선언한 endOfRow 변수를 TRUE로 설정한다.
DECLARE CONTINUE HANDLER 는 반복 조건을 준비하는 예약어이다.
그리고 FOR NOT FOUND는 더 이상 행이 없을 때 이어진 문장을 수행한다.
즉, 행이 끝나면 endOfRow에 TRUE를 대입한다.
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET endOfRow = TRUE;
4. 커서 열기
간단히 OPEN으로 열면 된다.
OPEN memberCursor;
5. 행 반복하기
cursor_loop: LOOP
이 부분을 반복
END LOOP cursor_loop
cursor_loop는 반복할 부분의 이름을 지정한 것이다.
그런데 이 코드는 무한 반복하기 때문에 코드 안에 반복문을 빠져나갈 조건이 필요하다.
앞에서 행의 끝에 다다르면 endOfRow를 TRUE로 변경하기로 했다.
그러므로 반복되는 부분에는 다음 코드가 필수로 들어가야 한다.
LEAVE는 반복할 이름을 빠져나간다.
결국 행의 끝에 다다르면 endOfRow가 TRUE로 변경되고 반복하는 부분을 빠져나가게 된다.
IF endOfRow THEN
LEAVE cursor_loop;
END IF;
FETCH는 한 행씩 읽어오는 것이다.
2번 단계에서 커서를 선언할 때 인원수(mem_number) 행을 조회했으므로 memNumber 변수에는 각 회원의 인원수가 한 번에 하나 씩 저장된다.
SET 부분에서 읽은 행의 수(cnt)를 하나씩 증가시키고, 인원 수도 totNumber에 계속 누적시켰다.
cursor_loop: LOOP
FETCH memberCursor INTO memNumber;
IF endOfRow THEN
LEAVE cursor_loop;
END IF;
SET cnt = cnt + 1;
SET totNumber = totNumber + memNumber;
END LOOP cursor_loop;
반복을 빠져나오면 회원의 평균 인원수를 계산한다.
SELECT (totNumber/cnt) AS '회원의 평균 인원 수';
6. 커서 닫기
모든 작업이 끝났으면 커서를 닫는다.
CLOSE memberCursor;
커서의 통합 코드
USE market_db;
DROP PROCEDURE IF EXISTS cursor_proc;
DELIMITER $$
CREATE PROCEDURE cursor_proc()
BEGIN
DECLARE memNumber INT;
DECLARE cnt INT DEFAULT 0;
DECLARE totNumber INT DEFAULT 0;
DECLARE endOfRow BOOLEAN DEFAULT FALSE;
DECLARE memberCursor CURSOR FOR
SELECT mem_number FROM member;
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET endOfRow = TRUE;
OPEN memberCursor;
cursor_loop: LOOP
FETCH memberCursor INTO memNumber;
IF endOfRow THEN
LEAVE cursor_loop;
END IF;
SET cnt = cnt + 1;
SET totNumber = totNumber + memNumber;
END LOOP cursor_loop;
SELECT (totNumber/cnt) AS '회원의 평균 인원 수';
CLOSE memberCursor;
END $$
DELIMITER ;
CALL cursor_proc();
평균 인원수 결과도 맞게 나왔당! ( •̀ ω •́ )✧
자동 실행되는 트리거
트리거는 자동으로 수행하여 사용자가 추가 작업을 잊어버리는 실수를 방지해 준다.
즉, 트리거를 사용하면 데이터에 오류가 생기는 것을 막을 수 있다! ^_^
이런 것을 데이터의 무결성이라고 부르기도 한다.
트리거 기본
트리거의 개요
트리거란 테이블에 INSERT나 UPDATE 또는 DELETE 작업이 발생하면 실행되는 코드이다.
만약 어떠한 사람이 회사에서 퇴사를 한다면 테이블에서 지우면 되지만,
이렇게 되면 그 사람의 정보를 얻을 수 있는 방법이 사라지기 때문에~ 이를 자동으로 별도의 장소에 보관해 주는 것이
트리거의 대표적인 용도이다. ^3^ (트리거.. 정말 간편하군!)
트리거의 기본 작동
트리거는 테이블에서 DML문(INSERT, UPDATE, DELETE 등)의 이벤트가 발생할 때 작동한다.
테이블에 미리 부착되는 프로그램 코드라고 생각하면 된다.
트리거는 스토어드 프로시저랑 문법이 비슷하지만, CALL 문으로 실행 시킬 수는 없고, 오직 테이블의 이벤트가
발생할 경우에만 자동으로 실행된다.
또한 트리거에는 IN, OUT 매개변수를 사용할 수 없다!
USE market_db;
CREATE TABLE IF NOT EXISTS trigger_table (id INT, txt VARCHAR(10));
INSERT INTO trigger_table VALUES(1, '레드벨벳');
INSERT INTO trigger_table VALUES(2, '잇지');
INSERT INTO trigger_table VALUES(3, '블랙핑크');
DROP TRIGGER IF EXISTS myTrigger;
DELIMITER $$
CREATE TRIGGER myTrigger
AFTER DELETE
ON trigger_table
FOR EACH ROW
BEGIN
SET @msg = '가수 그룹이 삭제됨'; -- 트리거 실행 시 작동되는 코드들
END $$
DELIMITER ;
테이블을 만들어준 후 트리거를 부착해 보았다.
트리거의 이름을 myTrigger로 설정하고, AFTER DELETE는
이 트리거는 DELETE 문이 발생된 이후에 작동하라는 의미이다. ^o^
트리거를 부착할 테이블을 trigger_table로 정하고 각 행마다 적용 시킨다는 의미인 FOR EACH ROW를 적어준다.
이는 트리거에서 항상 작성된다고 보면 된다!
이제 트리거를 부착한 테이블에 값을 삽입하고 수정해 보겠당. *^____^*
SET @msg = '';
INSERT INTO trigger_table VALUES(4, '마마무');
SELECT @msg;
UPDATE trigger_table SET txt = '블핑' WHERE id = 3;
SELECT @msg;
DELETE FROM trigger_table WHERE id = 4;
SELECT @msg;
이렇게 작성하면 INSERT 문과 UPDATE 문에는 결과가 아무것도 나오지 않는다.
왜냐하면 우리는 DELETE에만 작동하는 트리거를 부착 시켜 두었기 때문에! DELETE 문에서는 올바른 값이 도출된당.
트리거 활용
트리거는 테이블에 입력/수정/삭제되는 정보를 백업하는 용도로 활용할 수 있다.
USE market_db;
CREATE TABLE singer (SELECT mem_id, mem_name, mem_number, addr FROM member);
위 코드는 회원 테이블의 열을 간단히 아이디, 이름, 인원, 주소 4개의 열로 구성된 가수 테이블로 복사해서 진행하였당.
CREATE TABLE ~(SELECT~) 문은 테이블을 복사해서 새로운 테이블을 만들어주는 것이다! :P
CREATE TABLE backup_singer
( mem_id CHAR(8) NOT NULL,
mem_name VARCHAR(10) NOT NULL,
mem_number INT NOT NULL,
addr CHAR(2) NOT NULL,
modType CHAR(2), -- 변경된 타입. '수정' 또는 '삭제'
modDate DATE, -- 변경된 날짜
modUser VARCHAR(30) -- 변경한 사용자
);
가수 테이블에 INSERT나 UPDATE 작업이 일어나는 경우,
변경되기 전의 데이터를 저장할 백업 테이블을 미리 생성해 두었다.
백업 테이블에는 수정 또는 삭제인지 구분할 변경된 타입(modType), 변경된 날짜(modDate), 변경한 사용자(modUser) 를 추가하였다.
이제 본격적으로 변경(UPDATE)와 삭제(DELETE)가 발생할 때 작동하는 트리거를 singer 테이블에 부착할 것이다.
먼저 변경(UPDATE)이 발생했을 때 작동하는 트리거를 만들겠다. (●ˇ∀ˇ●)
DROP TRIGGER IF EXISTS singer_updateTrg;
DELIMITER $$
CREATE TRIGGER singer_updateTrg -- 트리거 이름
AFTER UPDATE -- 변경 후에 작동하도록 지정
ON singer -- 트리거에 부착할 테이블
FOR EACH ROW
BEGIN
INSERT INTO backup_singer VALUES ( OLD.mem_id, OLD.mem_name, OLD.mem_number, OLD.addr, '수정', CURDATE(), CURRENT_USER() );
END $$
DELIMITER ;
OLD 테이블은 UPDATE나 DELETE가 수행될 때, 변경되기 전의 데이터가 잠깐 저장되는 임시 테이블이다.
OLD 테이블에 UPDATE 문이 작동되면 이 행에 의해서 업데이트 되기 전의 데이터가 백업 테이블에 입력된다.
즉, 원래 데이터가 보존된다!!! o(*^＠^*)o
CURDATE()는 현재 날짜를, CURRENT_USER()는 현재 작업 중인 사용자를 알려준다.
이번에는 삭제(DELETE)가 발생했을 때 작동하는 트리거를 생성하겠다.
DROP TRIGGER IF EXISTS singer_deleteTrg;
DELIMITER $$
CREATE TRIGGER singer_deleteTrg -- 트리거 이름
AFTER DELETE -- 삭제 후에 작동하도록 지정
ON singer -- 트리거에 부착할 테이블
FOR EACH ROW
BEGIN
INSERT INTO backup_singer VALUES ( OLD.mem_id, OLD.mem_name, OLD.mem_number, OLD.addr, '삭제', CURDATE(), CURRENT_USER() );
END $$
DELIMITER ;
변경 트리거와 아주 유사한 것을 볼 수 있다~
이제 데이터를 변경해 보겠당.
UPDATE singer SET addr = '영국' WHERE mem_id = 'BLK';
DELETE FROM singer WHERE mem_number >= 7;
SELECT * FROM backup_singer;
한 건의 데이터를 업데이트하고, 여러 건을 삭제하였당.
백업 테이블을 조회해 봤더니 잘 보관되어 있는 것을 확인할 수 있다. (o゜▽゜)o☆
이번에는 모든 행 데이터를 삭제하겠당.
DELETE 대신 TRUNCATE TABLE 문으로 삭제를 해볼 것이다. 이 두 개는 동일한 효과를 낸다는 것을 기억하자.
하지만 이는 작동되지 않는데, 그 이유는 DELETE 트리거는 오직 DELETE 문에만 작동하기 때문이다!!!!
* 트리거가 사용하는 임시 테이블 *
- 조금 더 알아보기 편 -
테이블에 INSERT, UPDATE, DELETE 작업이 수행되면 임시로 사용되는 시스템 테이블이 두 개가 있다.
그것은 바로 NEW와 OLD이다. 두 테이블은 MySQL이 알아서 관리하므로 신경 쓸 필요는 없다.
NEW 테이블은 INSERT 문이 실행되면 새 값이 테이블에 들어가기 전 NEW 테이블에 잠깐 머문다.
DELETE의 작동에서는 삭제될 예전 값이 삭제되기 전 OLD 테이블에 잠깐 들어가 있는다.
그래서 삭제된 후에도 OLD.열 이름 형식으로 예전 값에 접근할 수 있는 것이당.
UPDATE(새 값, 예전 값)을 사용하면 NEW 테이블과 OLD 테이블을 모두 사용한다는 것을 알아두면 된다!
파이썬 개발 환경 준비
오! 학교에서 배우는 파이썬을 이용해서 SQL을 사용한다니 너무 신기해서 기대가 되엇숨돠 ㅎㅎ ^_~
우선 파이썬은 기존에 설치되어 있었고, pymysql만 설치해 주었어요!
파이썬의 기본 문법을 살짝 익히고 있기 때문에 그냥 다음 차례로 넘어가겠습니당. （*＾-＾*）
파이썬과 MySQL의 연동
파이썬에서 데이터 입력
파이썬에서 데이터를 입력하려면
MySQL 연결하기 - 커서 생성하기 - 테이블 만들기 - 데이터 입력하기 - 입력한 데이터 저장하기 - MySQL 연결 종료하기
순서로 이루어진다. 그리고 데이터 입력하기 부분은 반복한다.
1. MySQL 연결하기
우선 데이터베이스와 연동을 해야 한다. conn이라는 변수를 데이터베이스와 연결된 연결자로 사용하였다.
연결이 성공해도 아무런 메세지도 나오지 않는다!
pymysql.connect(host=서버IP주소, user=사용자, password=암호, db=데이터베이스, charset=문자세트)
2. 커서 생성하기
커서는 데이터베이스에 SQL 문을 실행하거나 실행된 결과를 돌려받는 통로라고 생각하면 된다!
cur = conn.cursor()
3. 테이블 만들기
테이블을 만드는 SQL 문을 커서이름.execute() 함수의 매개변수로 넘겨주면 SQL 문이 데이터베이스에 실행된다.
즉, 파이썬에서도 MySQL 워크벤치에서 사용한 것과 동일한 SQL 문을 사용해 주면 된당. ^_^
cur.execute("CREATE TABLE userTable (id char(4), userName char(15), email char(20), birthYear int) ")
4. 데이터 입력하기
데이터는 필요한 만큼 반복해서 입력한다. 데이터 입력도 SQL 문을 사용해야 하므로
커서이름.execute() 함수를 사용한다.
cur.execute("INSERT INTO userTable VALUES('hong', '홍지윤', 'hong@naver.com', 1996)" )
.
.
.
데이터는 마음대로 입력
5. 입력한 데이터 저장하기
앞에서 입력한 데이터들은 아직 데이터베이스에 완전히 저장된 것은 아니다.
임시로 저장된 상태였으며 이를 확실하게 저장하는 것을 커밋이라고 부른다.
conn.commit()
6. MySQL 종료하기
이제 데이터베이스를 닫으면 끝이다.
conn.close()
MySQL의 데이터 조회를 위한 파이썬 코딩 순서
MySQL 연결하기 - 커서 생성하기 - 데이터 조회하기 - 조회한 데이터 출력하기(반복) - MySQL 연결 종료하기
데이터를 조회하는 순서는 데이터 입력 순서와 비슷하지만 세 번째와 네 번째가 다르다.
3번에서는 커서에 SELECT로 조회한 결과를 한꺼번에 저장해 둔다.
그리고 4번에서는 조회한 데이터를 fetchone() 함수로 한 행씩 접근한 후 출력한다.
또한 조회하는 것은 데이터를 입력하거나 변경하는 것이 아니므로 굳이 커밋(저장)을 해줄 필요는 없다!
GUI 응용 프로그램
GUI는 윈도에 그래픽 환경으로 제공되는 화면을 통틀어서 말한다.
이를 도와주는 것이 tkinter라는 라이브러리이다.
GUI 기본 프로그래밍
기본 윈도의 구성
tkinter는 파이썬에서 GUI 관련 모듈을 제공해주는 표준 윈도 라이브러리로 윈도 화면이 필요할 때는 꼭 써줘야 한다.
Tk()는 기본이 되는 윈도를 반환하는데, 이를 root라는 변수에 넣었다.
Tk()를 루트 윈도라고도 부르며, 꼭 필요한 요소이다. 이 행이 실행될 때 윈도가 출력되는 것이다. :P
root.mainloop() 함수를 실행한다. 이는 앞으로 윈도에 키보드 누르기, 마우스 클릭 등 다양한 작업이 일어날 때
이벤트를 처리하기 위해 필요한 부분이다!
윈도에 제목을 지정하고, 크기도 설정할 수 있다!
라벨
라벨은 문자를 표현할 수 있는 위젯으로, label(부모윈도, 옵션 ...) 형식을 사용한다.
위젯은 윈도에 나오는 버튼, 텍스트, 라디오 버튼, 이미지 등을 통합해서 지칭하는 용어이다.
그리고 모든 위젯들은 pack() 함수를 사용해야 화면에 나타난다.
버튼
버튼은 마우스로 클릭하면 지정한 작업이 실행되도록 사용되는 위젯으로, Button(부모윈도, 옵션 ...) 형식을 사용한다.
앞에서 익힌 라벨과 차이점이 있다면, command 옵션으로 사용자가 버튼을 눌렀을 때 지정한 작업을 처리해야 한다는 것이다. 나머지 옵션은 라벨과 거의 동일하다!
프레임, 엔트리, 리스트 박스
프레임은 화면을 여러 구역으로 나눌 때 사용한다..
엔트리는 입력 상자를 표현하고, 리트스 박스는 목록을 표현한다.
연결하는 데... 에러가 나서 개념만 정리하구. . 코드 실행은 하지 못햇어요ㅠ-ㅠ
원래는 추가 숙제도 항상 했었는데. . 제가 어느 부분을 실수해서 그 부분을 잡지를 못 해가지구... 못 햇습니다.
아쉽지만. . . 이렇게 제 SQL 공부는 마무리가 되엇네용.
오늘 블로그는 매우 길었는데 공부를 재밌게 했어요! 읽어주셔서 감쟈합니당. ^_^
'레쭈고 혼공스 (MySQL)' 카테고리의 다른 글
| 혼공스 5주 차 (oﾟvﾟ)ノ (8) | 2024.08.03 |
|---|---|
| 혼공스 4주 차 UwU (9) | 2024.07.24 |
| 혼공스 3주 차 (*￣3￣)╭ (1) | 2024.07.19 |
| 혼공스 2주 차 (*￣3￣)╭ (0) | 2024.07.14 |
| 혼공스 1주 차 ^^ (1) | 2024.07.06 |