수업 17일차

galoo·2023년 7월 19일

HelloVision Dx Data School

목록 보기

17/72

✔ TCL

ATUO COMMIT을 해제하고 실습을 진행해보자.

-- select 구문은 트랜잭션과 아무런 연관성이 없음
-- 데이터의 변화와 상관이 없기 때문이다.
select * from DEPT;
-- DEPT 테이블에 데이터를 하나 삽입해보자.
-- dept 테이블에 데이터 1개 삽입, 이전 트랜잭션이 없어서 트랜잭션 생성
insert into DEPT (deptno, dname, loc) 
values (50, "Computing", "SEOUL");
select * from DEPT;
-- 철회: savepoint를 입력하지 않으면 트랜잭션 시작 전으로 복구
ROLLBACK;
-- 내가 insert 한 것이 없어졌어!
-- 트랜잭션 전으로 돌아가버렸다.
select * from DEPT;

insert 명령을 수행한 데이터가 없어졌다. COMMIT을 안했기 때문이다.

-- 다시 데이터를 넣어보자.
-- 트랜잭션이 다시 하나 생성된다.
insert into DEPT (deptno, dname, loc) 
values (50, "Computing", "SEOUL");
-- 이제 create 구문을 하나 써볼까?
-- DEPT 복제 테이블 하나 생성하기
create table DEPT_COPY 
as select * from DEPT;
-- 다시 ROLLBACK하기
ROLLBACK;
-- 이번에는 50이 나온다.
-- ROLLBACK을 했는데 왜 나왔지?
-- DDL(create, drop, alter, truncate, rename)이나
-- DCL(grant, revoke)를 수행하면 AUTO COMMIT이다.
-- why? : 관리자 영역의 명령어들이라 나중에 적용되면 안되기 때문이다.
-- 트랜잭션은 변경 내역을 반영하고 종료를 한다.
-- 물론 create한 테이블도 잘 보인다.
select * from DEPT;
select * from DEPT_COPY;

-- 트랜잭션 생성
insert into DEPT (deptno, dname, loc) 
values (60, "Computing1", "SEOUL1");
savepoint sv1;
insert into DEPT (deptno, dname, loc) 
values (70, "Computing2", "SEOUL2");
savepoint sv2;
insert into DEPT (deptno, dname, loc) 
values (80, "Computing3", "SEOUL3");
select * from DEPT;
-- sv1을 생성한 지점으로 이동
ROLLBACK TO sv1;
-- sv1으로 오게 된다면 sv2는 없는 것을 생각하자.
select * from DEPT;
-- 이대로 냅두면 lock이 걸린다. 
-- 왜냐면 내가 rollback이나 commit, ddel, dcl 한 적이 없음
-- 에러는 아니고 데드락에 걸려요 이걸 connect timeout을 걸어야 함
-- commit 수행 : 트랜잭션 종료
COMMIT;

✔ VIEW

개요

가상의 테이블
물리적으로 존재하지는 않지만 테이블처럼 사용 가능

목적

속도와 보안이다.

속도

VIEW를 생성하는 구문은 메모리에 적재시켜 두고 실행
SELECT * from ? ; 이 명령은 보조기억장치에 두고 실행한다. 이 명령을 실행하면 메모리에 올려서 cpu에서 작업하는것이다.
VIEW와 프로시저는 memory에 둔다. 그러기에 매우 빠르다.

보안

사용자에게 VIEW를 제공하면, 사용자는 테이블의 구조를 알 필요가 없다.

VIEW의 종류

from 절에 작성한 서브 쿼리인 INLINE VIEW
- 하나의 select 구문은 테이블 구조의 result set을 리턴함
- select ~ from (select 구문) 이름
- 괄호의 select 구문을 INLINE VIEW라고 한다.
- SQL 구문이 복잡해질 때, 메모리 부담을 줄이기 위해서 사용함

slsect *
from EMP, DEPT
where EMP.DEPTNO=DEPT.DEPTNO and JOB='CLERK';

select *
from (select * from EMP where JOB='CLERK') TEMP, DEPT 
where TEMP.DEPTNO=DEPT.DEPTNO;

select 구문으로 만든 일반적인 VIEW

VIEW 생성 구문

create [or replace] view 뷰이름
as
select 구문
[with check option]
[with read only]
MySQL은 버전에 따라서 OR REPLACE를 사용하지 못하는 경우가 있다.

WITH CHECK OPTION
- 뷰를 가지고 DML 작업을 할 때, select 구문에서 조회된 데이터만 가능하도록 하는 옵션인데, DB에 직접 접속했을때만 가능합니다.
- DB에서 제공하지 않는 접속 도구를 사용하면, 이 옵션은 수행되지 않습니다.
WITH READ ONLY
- 읽기 전용의 VIEW를 생성하는 것

특징

VIEW는 SELECT 구문을 가지고 있따가 호출하면 SELECT 구문을 수행해서 결과를 리턴합니다.
VIEW에 DML 작업이 가능
- VIEW를 만들 때, 2개 이상의 테이블을 가지고 생성하거나 NOT NULL 컬럼을 제외하고 만든 경우에는 DML 작업이 안됩니다.
- VIEW에 DML 작업을 수행하면 원본 테이블에 작업이 수행됩니다.
- VIEW에 DML 작업을 수행하지 못하도록 할려면 만들 때, READ ONLY 옵션을 추가해야 합니다.
VIEW는 구조 변경이 안됩니다.
- ALTER를 사용할 수 없습니다.

삭제

DROP VIEW 뷰이름;

-- emp 테이블에서 empno, ename, sal, comm만으로 구성된 뷰를 생성
create view KIM
as
select EMPNO, ENAME, SAL, COMM 
from EMP;
-- 사용은 테이블과 똑같다.
select * from KIM;
-- VIEW에 DML(삽입 삭제, 갱신) 작업은 가능한 경우도 있고 
-- 가능하지 않는 경우도 존재
DESC EMP;
-- view에 데이터 삽입
insert into KIM(EMPNO, ENAME, SAL, COMM) 
VALUES(999, 'MINO', 10000, 9000);
SELECT * FROM KIM;
-- 확인해 보면 EMP 테이블에 들어가있음
SELECT * FROM EMP;
-- VIEW 구조 확인
DESC KIM;
-- VIEW 삭제
DROP VIEW KIM;

✔ 임시 테이블

임시 테이블

일시적으로 테이블을 만들어서 사용하는 것인데, 현재 세션 내에서만 접근이 가능하며 세션이 종료되면 자동으로 소멸됩니다.
- 내 세션에서 작업을 한다면 다른 사람들은 내 세션에 없으면 못본다.

생성

create temporary table 테이블이름(내용);
- 기존에 존재하는 테이블 이름과 동일한 이름을 사용할 수 있는데, 이 경우에는 기존 테이블은 임시 테이블이 소멸될 때 까지 사용할 수 없다.
- 즉, 이름은 겹치지 않는 것이 좋다는 것이다.

-- 임시 테이블
create temporary table temp(
	name char(20)
);
select * from temp;
해당 세션을 종료하고 새 세션을 킨다면 사용 못하는 임시 테이블이다.

CTE(Common Table Expressions)

쿼리 실행 중에 메모리에 존재하는 테이블
임시 테이블은 세션을 종료할 때 까지 존재하기 때문에 중간에 내용을 변경해서 사용하려면 삭제하고 다시 만들고 하는 작업을 수행해야 하고, view는 실제 데이터를 갖는 것이 아니고 select 구문을 가지고 있어서 연산의 중간 결과를 가지는 형태로 사용하기에는 속도가 느립니다.
SQL 작업 중간에 임시 결과를 저장하기 위한 용도로 사용합니다.
SQL 수행 중에 데이터를 일시적으로 저장했다가 수행이 종료되면 자동으로 소멸됩니다.

기본 형식

WITH 테이블이름(컬럼이름 나열) AS (SELECT 구문)
이후 테이블 사용
- 컬럼 이름 나열부분을 생략하면 SELECT 구문의 컬럼 이름을 그대로 사용

SELECT 구문이 복잡한 경우, 동일한 SELECT 구문 여러 번 입력해야 하는 경우가 있는데, 이 때 CTE를 사용하면 편리하다.

2개 이상의 CTE를 사용하고자 하는 경우에는 콤마(,)로 구분

-- CTE : SQL 수행 중에만 일시적으로 
-- 메모리 공간을 할당받아서 사용하는 테이블
-- FROM 절에 쓰는 것 : INLINE VIEW
-- SUB QUERY는WHERE 뒤에 있는거
SELECT * 
FROM (SELECT NAME, SALARY, SCORE) FROM tStaff 
WHERE DEPART='영업부' AND GENDER='남') TEMP
WHERE SALARY>=(SELECT AVG(SALARY) FROM TEMP);
-- 이러면 WEHRE에 있는 TEMP가 뭔지 모른다.
-- 이와 유사한 작업을 INLINE VIEW에서 가능할거라 보이지만,
-- INLINE VIEW는 SUB QUERY보다 늦게 수행되기 때문에
-- INLINEEVESMS SUB QUERY에서 못쓴다.
WITH TEMP AS
(SELECT NAME, SALARY, SCORE FROM tStaff 
WHERE DEPART='영업부'AND GENDER='남')
SELECT * FROM TEMP WHERE SALARY>=(SELECT AVG(SALARY)FROM TEMP);

✔ STORED PROCEDURE

개요

자주 사용하는 구문을 함수처럼 하나로 묶어두고 이름만으로 사용하는 것
함수와 다른 점은 함수는 반드시 리턴을 해야 하지만, STORED PROCEDURE는 리턴을 하지 않을 수 도 있음

목적

속도
- 프로시저는 한 번 호출되면, 메모리에 적재된 상태로 다음 수행을 하기 때문에 실행 속도가 빠름
보안
- 외부에서는 데이터베이스 내부 구조를 알지 못하더라도 작업을 수행하는 것이 가능

단점

데이터베이스 종류마다 생성 방식이 다름
- 포트폴리오를 만들 때, ORM 사용을 않는다면, 프로시저를 이용하는 것이 좋습니다.

생성

DELIMITER 기호기호
		CREATE [OR REPLACE] 프로시저 이름()
        BEGIN
        	SQL 작성
        END 기호기호
DELIMITER

호출

CALL 프로시저이름(매개변수 나열)

-- 프로시저 생성
-- delimiter는 프로시저 종료를 알리기 위한 기호설정
-- 왜냐하면 안에 이미 세미콜론이 있기 때문에 모르기 때문이다.
-- 두개로 쓰는 이유는 하나로 만들면 데이터로 사용되는 것과
-- 혼동이 올 수 있기 때문이다.
-- dbeaver에서 수행할 때는 sql script 실행으로 실행해야 한다.
delimiter //
create procedure myproc(vuserid char(15), vname varchar(20), 
						vbirthyear int(11), vaddr char(100), 
						vmobile char(11), vmdate date)
		begin
			insert into usertbl
			values(vuserid, vname, vbirthyear,vaddr, 
            vmobile, vmdate);
		end//
delimiter ;
-- 지우기는 drop 입니다.
drop procedure myproc;
-- 실행하기
call myproc('mino', '이민호', 1998,'서울' ,
'01012341234', '1998-09-25');
select * from usertbl;

✔ Trigger

DML 작업이 발생했을 때 이 작업을 수행하지 않거나 이 작업이 수행되기 전이나 수행된 후에 다른 작업을 수행하도록 만드는 것
- 유효성 검사 같은거 아닐까?
- 네트워크로 연결된 서버를 이동할 때, 변경 가능성이 존재함
- ~~~ 하기 전에 ~~한다 는 유효성 검사
- ~~~하고 나서는 ~~한다는 로깅을 하는 것이다.

생성

create trigger 이름
timing [before|after] event [insert|update|delete]
on 테이블 이름
[for each row] -- 행단위? 아니면 한번만(한번만이면 생략가능)
[where 조건]
begin 
	수행할 내용
end
권한 문제로 트리거 생성이 안되면 관리자 계정으로 접속해서 명령을 수행하거나, 관리자 계정에서 set global log_bin_trust_function_creators=on; 명령어를 입력해주자.

username : root 이다. 관리자는

-- 트리거를 수행하기 위한 샘플 테이블 생성
create table emp01(
	empno int primary key,
	ename varchar(30) not null,
	job varchar(100)
);
create table sal01(
	salno int primary key auto_increment,
	sal float(7,2),
	empno int references emp01(empno) on delete cascade
);
-- emp01 테이블에 데이터 추가시, sal01테이블에 데이터가 
자동으로 추가되는 트리거
set global log_bin_trust_function_creators=on;
delimiter //
create trigger trg_01
after insert on emp01
for each row 
BEGIN 
	insert into sal01(sal,empno) values(100,NEW.empno);
END//
delimiter ;
insert into emp01 values(1,'mino','데이터분석가');
select * from emp01;
select * from sal01;

내용 안에서 삽입되는 데이터나 수정될 값을 사용하고자 하는 경우는 NEW.컬럼이름을 사용하면 되고, 수정되기 전의 값이나 삭제되는 값을 사용하고자 하는 경우는 OLD.컬럼이름을 사용하면 됩니다.

✔ Python과 MySQL 연동

프로그래밍 언어와 데이터베이스 연동 방법

데이터베이스 드라이버만을 이용해서 작업
- 인터페이스 : 드라이버
- SQL 직접 작성
데이터베이스와 드라이버 그리고 프레임워크를 이용하는 방식

SQL Mapper
- 소스코드와 SQL을 분리시켜서 수행하는 방식
- MyBatis가 대표적인 Framework(우리나라 SI 업계에서 주로 이용, 쉽기는 하지만 효율이 떨어짐)

ORM(Object Relation Model)
- 프로그래밍 언어의 인스턴스와 관계형 데이터베이스의 행을 매핑시켜서 SQL을 사용하지 않고도 데이터베이스 작업을 할 수 있도록 해주는 방식
- 데이터베이스를 변경해도 수정이 거의 발생하지 않습니다.
- 효율이 좋지만 어렵기 때문에 솔루션 업체에서 주로 이용
- 최근에는 거의 대부분 이 방식을 사용
- Django나 Java의 JPA등의 대표적인 ORM 프레임워크입니다.

데이터베이스 드라이버 만을 이용해서 작업

파이썬과 MySQL 연결을 위한 드라이버 패키지 다운로드 받아서 설치

pyMySQL 패키지 사용 : pip install pyMySQL

데이터베이스와 연결

연결을 위한 정보
- host IP 또는 도메인 : MySQL 서버가 설치된 곳의 IP나 도메인
- 자신의 컴퓨터는 localhost
- 포트번호 : 컴퓨터에서 서비스를 구분하기 위한 번호, MySQL MariaDB는 기본 3306
- 사용할 데이터베이스 이름 : MySQL에서는 DB이름이고, Oracle에서는 서비스 이름이나 SID
- user : 사용자 아이디
- passwd : 사용자 비밀번호
- charset : 인코딩 방식 (한글 - utf8)

MySQL 데이터베이스 연결하고 접속 종료

변수 = pymysql.connect(host=?,port=?,user=?,passwd=?,db=?,charset=?)
성공하면 메시지 x 실패하면 에러 메시지와 함께 예외 발생함
변수.close() : 접속 종료

# 파이썬 MySQL 드라이버 패키지 다운로드
pip install pyMySQL
import pymysql
try:
    con=pymysql.connect(host='localhost',port=3306,db='mino', user='root', passwd='12alsgh12!', charset='utf8')
    print(con)
except:
    print("예외 발생!", sys.exc_info())
finally:
    if con!=None:
        con.close()

DML 작업

<과정>

데이터베이스 연결 객체(con)가 cursor 함수를 호출해서 SQL 실행 객체를 리턴받습니다.
SQL 실행 객체.excute(SQL[,parameter 값 나열])
- 이때, parameter 값을 SQL에 삽입할 수 있고, 서식을 설정한 다음 대입해도 됩니다.
- 최근에는 보안 상의 문제로 서식을 설정해서 대입하는 것을 권장합니다.
con.commit()을 호출하면 원본에 반영됩니다.

데이터 삽입 작업

작업할 테이블 확인 : DBeaver 에서 확인
파이썬에서 SQL 작성

import pymysql
import sys
try:
    #db 연결객체 생성
    con=pymysql.connect(host='localhost',port=3306,db='mino', 
    user='--', passwd='--', charset='utf8')
    print(con)
    #sql 실행 객체 생성
    cursor=con.cursor()
    #sql 실행 - 값을 직접 sql에작성
    cursor.execute("insert into DEPT values(11,'비서','서울')")
    #sql 실행 - sql에 서식을 설정하고, 파라미터를 대입하는 코드 작성 : 권장
    cursor.execute("insert into DEPT values(%s,%s,%s)",
    (12,'기획','제주'))
    ### 이렇게 하고 dbeaver에서 select * from DEPT 보면 데이터 없음
    # 여기의 DEPT와 DBeaver의 DEPT와는 다른 것이다. (세션이 다르다.)
    # Commit을 해주자.
    con.commit()
except:
    print("예외 발생!", sys.exc_info())
finally:
    if con!=None:
        con.close()

DB 연결객체 생성 시, user 와 pwd는 기존 db와 맞아야 한다.
똑같이 쓰는 부분들이 존재한다. 이는 프레임워크가 채워줄 것이다.

DQL 작업

cursor 객체를 이용해서 select 구문 수행
하나의 데이터만 가져오고자 하는 경우는 cursor 객체를 가지고 fetchone 메서드를 호출하면 하나의 튜플로 데이터를 리턴합니다.
fetchall 메서드를 호출하면, tuple의 tuple로 모든 데이터를 리턴합니다.
fetchone 호출 경우
- 기본키를 조건으로 해서 조회하는 경우
- 그 이외의 경우는 fetchall을 호출함

import pymysql
import sys
try:
    #db 연결객체 생성
    con=pymysql.connect(host='localhost',port=3306,
    db='mino', user='-', passwd='-', charset='utf8')
    #sql 실행 객체 생성
    cursor=con.cursor()
    cursor.execute("select * from DEPT where DEPTNO=%s",(12))
    #검색 결과 중 하나의 데이터를 읽어오는 것
    record=cursor.fetchone()
    #검색된 결과가 없다면 None, 존재하면 Tuple
    #print(record)
    if record==None:
        print("검색된 데이터가 없음")
    else:
        for attr in record:
            print(attr)
except:
    print("예외 발생!", sys.exc_info())
finally:
    if con!=None:
        con.close()

BLOB

데이터베이스에 파일을 저장하는 방법
- 파일을 파일 서버(AWS의 S3 또는 Google Firebase의 File Storage 서비스) 에 별도로 젖아하고 그 경로를 저장하는 방법
- 파일의 이름과 내용을 모두 데이터베이스에 저장하는 방식
파일을 파일 서버에 별도로 저장하면 DB에 저장하는 것 보다 비용이 저렴하지만 파일을 사용하려고 할 때 마다 다운로드를 받아야 합니다.
- select 구문 만으로는 파일을 사용할 수 없습니다.
최근에는 대부분 파일을 데이터베이스에 저장하지는 않습니다.
파일의 내용을 데이터베이스에 저장하고자 하면, 데이터 타입을 blob or longblob로 설정해야 합니다.
- 파일의 내용을 저장할 때, 형식을 알아야 하기 때문에 파일 이름을 같이 저장
파이썬에서는 BLOB는 bytes와 매핑됩니다.
DB에 파일을 저장할 수 있는 테이블을 생성해야 한다.

create table blobtable(
	filename varchar(100),
	filecontent longblob
);
desc blobtable;

이제 한번 이미지 파일을 넣어보자.

import pymysql
import sys
try:
    #db 연결객체 생성
    con=pymysql.connect(host='localhost',port=3306,
    db='mino', user='-', passwd='-', charset='utf8')
    #sql 실행 객체 생성
    cursor=con.cursor()
    #삽입할 이미지 파일 내용 읽기
    f=open('galoo7.jpg','rb')
    galoo=f.read()
    f.close()
    # 데이터 삽입
    cursor.execute("insert into blobtable values(%s, %s)",
    ("galoo7.jpg",galoo))
    con.commit()
except:
    print("예외 발생!", sys.exc_info())
finally:
    if con!=None:
        con.close()

그럼 db에 저장된 파일을 로컬에 불러보자.

import pymysql
import sys
try:
    #db 연결객체 생성
    con=pymysql.connect(host='localhost',port=3306,
    db='mino', user='--', passwd='--', charset='utf8')
    #sql 실행 객체 생성
    cursor=con.cursor()
    #데이터 읽어오기
    cursor.execute("select * from blobtable")
    data=cursor.fetchone()
    #두 번쨰 데이터가 blob 이므로 두번째 데이터를 파일로 변경
    print(data[0])
    f=open(data[0],'wb')
    f.write(data[1])
    f.close()
    #select는 transaction이랑 상관 없어서 commit 할 필요가 없음
    #con.commit()
except:
    print("예외 발생!", sys.exc_info())
finally:
    if con!=None:
        con.close()

몇 번째가 이름이고 몇 번째가 사진인지 데이터 인덱스 잘 생각하기

✔ Oracle

Oracle

미국의 오라클 사에서 만든 관계형 데이터베이스
우리나라 대기업과 공공기관에서 가장 많이 사용하는 데이터베이스
버전은 Enterprise(ORCL), Standard, Express Edition(XE) 총 3개가 존재한다.
학습을 하는 경우는 Express Edition이면 충분합니다.
관리의 영역은 Enterprise로 해야 합니다.

설치

운영체제에 직접 설치

MAC에서는 설치가 안됨
Windows와 Linux에서 설치가 가능한데, 실제로는 Linux에 설치해서 사용하는 경우가 대부분이다.
Oracle Web Site에서 회원가입 후, 다운로드 받아서 설치

Docker에 설치

Windows는 설치에 아무런 제약이 없지만, MAC의 경우는 M1,M2 프로세서를 사용하는 경우는 별도의 프로그램을 설치하고 설치해야 합니다.
11g : docker run --name 컨테이너이름 -d -p 포트번호(1521):1521 jaspeen/oracle-xe-11g 이러면 관리자는 sys, system 이고, sid 는 xe가 되고 관리자는 system이고 비번은 oracle
21c : docker run --name 컨테이너이름 -d -p 포트번호(1521):1521 -e ORACLE_PASSWORD=관리자비밀번호 gvenzl/oracle-xe sid는 xe가 되고 관리자는 sys, system이며 비번은 설정한 값