힐링 ASMR 과 프로그래밍

오늘은 본디식당에서 점심을 먹었습니다.

국수류는 안먹어 보았지만 좀 비싼 감이 있고 몸국이나 따로국밥은 가격도 괜찮고 깔끔했습니다.(6,000원)

생긴지 별로 오래 되지 않아서 식당이 깨끗했고 도심에 있음에도 주차장이 있어서 이용하기 좋았습니다.

다 먹고 난 후 무료 아메리카노 머신이 있어서 따로 커피를 사먹지 안아서 더욱 좋았습니다.

MySQL CSV 파일 임포트 익스포트 하기

텍스트 파일로부터 데이터 가져오기 – 임포트

보통 파일로부터 데이터를 입력할 때에는 CSV(Comma Separated Values)형식과 같은 텍스트 파일을 사용합니다. 이런 형식의 파일을 읽어 들이는 것을 가져오기 또는 임포트(import)라고 합니다.

파일 가져오기.

파일에서 데이터를 가져올 때에는 LOAD DATA LOCAL INFILE이라는 명령을 사용합니다.

Load data local infile ‘파일명’ into table 테이블명 옵션설정;

Load data local infile 명령으로 데이터 형식을 지정하는 옵션

Fields terminated by 구분문자 기본설정은 ‘\t’: 탭

Lines terminated by 줄바꿈 문자 기본설정은 ‘\n’: 줄바꿈

Ignore 처음에 건너 뛸 행 lines 기본설정은 0

우선 test.csv 파일을 다음과 같이 입력한 후 생성합니다.

A1, 강백호, 41

A2, 김민우, 29

A3, 김우빈, 20

A4, 문성근, 24

A5, 박찬호, 47

A6, 이수만, 60

위의 레코드를 table_03에 가져오는 명령을 내려 보겠습니다.

select * from table_03;

load data local infile ‘d:/test/test.csv’

into table table_03 fields terminated by ‘,’;

select * from table_03;

데이터를 텍스트 파일에 내보내기 – 익스포트

가져오기와는 반대로 테이블에 있는 데이터를 CSV 파일 등의 텍스트 파일로 내보낼 수가 있습니다. 이처럼 데이터를 파일로 내보내는 것을 내보내기 또는 익스포트(export)라고 합니다.

명령문은 다음과 같습니다.

select * into outfile ‘파일명’ 옵션설정 from 테이블명;

옵션 설정에는 내보내기할 텍스트 파일의 형식을 지정합니다.

select * into outfile ‘d:/test/export.csv’

fields terminated by ‘,’ from table_03;

다음과 같이 익스포트 할 때 Access denied for user 라는 오류 메시지가 나올 때 다음과 같이 해결합니다.

우선 mysql 프롬프트 에서 빠져 나와서 root로 로그인후 파일 권한을 사용자 아이디에 부여합니다.

use mysql;

update user set file_priv = ‘y’ where user = ‘comster_user’;

flush privileges;

권한을 부여한 후 다시 사용자 아이디로 로그인한 후 실행해 보겠습니다.

위와 같이 csv 파일을 잘 export 합니다.

내보내기한 파일의 내용은 메모장과 같은 텍스트 편집기로 확인할 수 있습니다.

여기에서는 명령 프롬프트에서 type 명령을 사용해 보겠습니다. 먼저 MySQL 콘솔창을 종료하고 명령 프롬프트에 다음 명령을 실행합니다.

quit;

type d:\test\export.csv

MySQL 트랜잭션.

트랜잭션이란?

여러 단계의 처리를 하나의 처리처럼 다루는 기능을 트랜잭션(transaction)이라고 합니다.

트랜잭션의 실행 결과를 데이터베이스에 반영하는 것을 커밋(commit)이라 하고, 반영하지 않고 원래 상태로 되돌리는 것을 롤백(roll-back)이라고 합니다.

트랜잭션 사용하기.

트랜잭션을 시작할 때에는 start transaction 이라는 명령을 사용합니다.

뭔가 작업을 하고 원래 상태로 되돌릴 때는 rollback 명령어를 사용합니다.

작업후 작업한 것을 반영할 때는 commit 명령어를 사용합니다.

table_03을 트랜잭션을 시작한 후 삭제 했다가 롤백을 해보겠습니다.

Rollback 이 되어서 무사히 데이터가 복원되었습니다.

트랜잭션은 rollback;을 실행한 시점에서 확정됩니다. 다시 한번 테스트를 하려면 반드시 start transaction;으로 트랜잭션을 시작해야 합니다.

이번에는 테이블을 삭제한 후 commit; 를 실행해 보겠습니다.

start transaction;

delete from table_03;

commit;

select * from table_03;

위와 같이 commit; 명령 실행한 후 테이블이 비어 있는 것을 확인할 수 있습니다.

자동 커밋 기능.

일반적으로 MySQL에서 명령을 실행하면 그대로 반영됩니다. 즉, 사용자가 의식하지 않아도 모든 명령이 자동으로 commit되는 것입니다.

이처럼 명령을 실행하면 그대로 반영하는 기능을 자동 커밋 기능이라고 합니다.

자동 커밋 기능을 OFF로 설정하기.

자동 커밋 기능을 off로 하려면, 다음과 같이 set autocommit=0; 이라는 명령을 실행합니다.

set autocommit=0;

insert into table_03 values(‘test’, ‘테스트’, 30);

select * from table_03;

rollback;

select * from table_03;

위에서 보는 것과 같이 자동커밋 기능을 off로 설정해서 rollback; 을 했을 때 원래 상태로 돌아 가는 것을 확인할 수 있습니다.

자동 커밋 기능을 on으로 바꾸기.

Off로 설정된 자동 커밋 기능을 원래대로 되돌리는 방법은 다음 명령을 사용하여 자동 커밋 기능을 원래의 기본 설정으로 되돌려 놓습니다.

set autocommit=1;

자동 커밋 기능의 설정 상태를 확인 명령어.

select @@autocommit;

트랜잭션을 이용할 수 있는 범위.

트랜잭션으로 모든 명령을 롤백으로 되돌릴 수 있는 것은 아닙니다. 다음과 같은 명령은 자동 커밋됩니다.

drop database

drop table

drop

alter table

위의 명령은 트랜잭션을 실행해도 원래의 상태로 되돌릴 수가 없으니 주의해야 합니다.

저장 엔진이란?

MySQL의 기능은 크게 두 가지로 나눌 수 있습니다.

첫 번째는 접속 기능과 SQL문의 내용을 사전에 조사하는 기능, 즉 데이터베이스의 상위 부분이고, 두 번째는 그 상위 부분의 지시를 받아 실제로 검색이나 파일을 조작하는 기능의 하위 부분입니다. 이 하위 부분을 저장 엔진이라고 합니다.

저장 엔진의 종류.

저장 엔진 확인하기.

테이블의 저장 엔진을 확인해 보는 명령은 다음과 같습니다.

show create table 테이블명;

show create table table_02;

table_02는 InnoDB엔진이 사용되고 있습니다.

위와 같이 보기가 좀 불편할 때는 명령문 끝에 세미콜론 대신 \G 를 사용하면 보기가 쉽습니다.

이때 G는 대문자를 사용해야 합니다.

저장 엔진 변경하기.

테이블의 저장 엔진은 나중에 변경할 수도 있습니다.

지금 InnoDB로 되어 있는 것을 MyISAM으로 변경해 보겠습니다.

저장 엔진을 변경하는 명령문은 다음과 같습니다.

alter table 테이블명 engine=MyISAM;

alter table table_02 engine=MyISAM;

show create table table_02 \G

위에 보는 것과 같이 엔진이 잘 변경되었습니다.

트리거란(trigger)?

트리거(trigger)는 테이블에 대해 어떠한 처리를 실행하면 이에 반응하여 설정해 둔 명령이 자동으로 실행되는 구조를 말합니다. 이때, 이러한 과정이 총의 방아쇠를 당기는 것과 같다 하여 트리거(방아쇠)라고 합니다.

Insert나 update, delete등의 명령이 실행될 때, 사전에 트리거로 설정해 놓은 기능도 함께 실행할 수가 있습니다. 예를 들어, 테이블의 레코드를 변경하면 그것을 계기로 변경한 내용을 다른 테이블에 기록하도록 트리거를 작성할 수 있습니다.

예제로 테이블에 있는 레코드를 삭제하면, 삭제한 레코드가 다른 테이블에 복사되는 트리거를 만들어 보겠습다.

테이블 컬럼 구조만 복사해서 table_trigger 테이블을 만들어 둡니다.

select * from table_03;

create table table_trigger like table_03;

select * from table_trigger;

트리거 만들기.

트리거는 insert, update, delete등의 명령이 실행되기 직전이나 직후에 호출되어 실행됩니다.

또한 테이블에서 어떠한 처리를 하기 직전과 직후의 값은 다음과 같이 old.컬럼명 과 new.컬럼명으로 얻을 수 있습니다.

다음은 크리거를 만드는 명령문입니다.

create trigger 트리거명 before(또는 after) delete 등의 명령

on 테이블명 for each row

begin

           변경전(old.컬럼명) 또는 변경후(new.컬럼명)을 이용한 처리

end

트리거의 본체를 작성할 때 각 명령의 끝에는 세미콜론을 입력합니다. 따라서 미리 구분 문자를 //로 변경해 둡니다.

테이블 table_03의 레코드를 삭제하고, 삭제한 레코드를 테이블 table_trigger에 삽입하는 트리거 trigger_01을 작성해서 테이블 table_03에 있는 모든 레코드를 삭제해 보겠습니다.

delimiter //

create trigger trigger_01

before delete on table_03 for each row

begin

insert into table_trigger values(old.number, old.name, old.age);

end

//

delimiter ;

트리거가 생성되었습니다.

이제 테이블 table_03에 레코드를 모두 삭제하여 table_trigger에 삭제된 레코드가 저절로 입력되는지 확인해 보겠습니다.

delete from table_03;

select * from table_03;

select * from table_trigger;

table_03에는 레코드가 남아 있지 않고 table_trigger에는 삭제된 모든 레코드가 추가되어 있어 트리거가 잘 동작한 것을 확인할 수 있습니다.

설정한 트리거 확인하기.

현재 설정된 트리거를 확인할 때에는 다음 명령어를 사용합니다.

show triggers;

트리거 삭제하기.

트리거를 삭제할때는 다음 명령어를 사용합니다.

drop trigger 트리거명;

drop trigger trigger_01;

show triggers;

MySQL 저장함수.

MySQL 저장 함수란 무엇인가?

저장 함수는 저장 프로시저와 거의 흡사합니다. 저장 프로시저와 유일하게 다른 점은 실행 했을 때 값을 반환한다는 점입니다.

저장 함수를 사용하면 자신만의 함수를 만들 수가 있습니다. 저장 함수는 사용자 정의 함수라고도 합니다.

다음은 저장 함수를 작성하는 구문입니다.

create function 저장함수이름(인수명 자료형) returns 반환값의 자료형

begin

           sql 문;

           Return 반환값식;

end

저장 프로시저와 마찬가지로 괄호안에 인수를 대입할 수 있습니다. 인수를 대입하지 않아도 괄호는 입력해야 합니다.

저장 함수로 표준 체중 계산하기.

BMI(Body Mass Index) = 22가 표준 체중이라고 했을 때 다음과 같은 식이 성립합니다.

표준 체중 = 신장(cm 단위) * 신장(cm 단위) * 22/10000

이 식을 이용해서 저장 함수 function_01( )을 만들어 보겠습니다.

delimiter //

create function function_01(height int) returns double

begin

return height * height * 22/10000;

end

//

delimiter ;

select function_01(173);

위에 declare a double 은 변수 a를 double형으로 선언한 것이고 avg(age) into a 는 평균을 구한 값을  a에 할당한다는 의미 입니다.

저장 함수의 내용 표시하기.

저장 함수의 내용을 표시하는 명령어는 다음과 같습니다.

Show create function 저장함수명;

show create function function_avgAge;

저장 함수 삭제하기.

저장 함수를 삭제하는 방법은 다음 명령어를 사용합니다.

drop function 저장함수명;

drop function function_avgAge;

MySQL 저장 프로시저 활용하기.

저장 프로시저란 무엇인가?

여러 SQL 문을 하나의 SQL 문처럼 정리하여 call 명령으로 실행할 수 있게 만든 것을 저장 프로시저라고 합니다. 저장 프로시저는 일련의 절차를 정리해서 저장한 것입니다.

사전에 준비해 둔 많은 명령을 자동으로 실행할 수 있기 때문에 작업의 효율성도 높일 수 있습니다.

저장 프로시저 만들기.

저장 프로시저를 작성할 때에는 다음과 같이 create procedure라는 명령을 사용합니다.

create procedure 저장 프로시저명( )

begin

           sql 문1;

           sql 문2;

end

begin 에서 end 까지가 저장 프로시저의 본체입니다.

시작에 begin을 끝에 end를 붙여 저장 프로시저의 명령 범위를 확실히 하고 있습니다.

저장 프로시저의 본체는 평범한 sql문 입니다. 그래서 끝에 종료를 나타내는 세미콜론을 입력해야 합니다. 하지만 이렇게 되면 저장 프로시저를 작성하는 중간에 세미콜론을 입력하게 되어서 저장 프로시저가 완성되지 않은 상태에서 실행이 됩니다. 세미콜론이 입력되면 어떤 경우에서 건 일단 세미콜론 이전 단계까지 명령문을 실행하게 됩니다.

구분 문자(;) 변경하기.

명령문이 완성되지 않은 상태에서 실행되면 곤란합니다. 저장 프로시저에서 end를 입력하고 나서 프로시저 명령이 실행되도록 환경을 변경해야 합니다.

그러려면 저장 프로시저를 작성하기 전에 구분 문자를 세미콜론이 아닌 다른 문자로 변경해 둡니다. 일반적으로 //을 사용합니다.

구분 문자를 //으로 변경할 때에는 delimiter명령을 사용합니다.

delimiter //

구분 문자를 //으로 변경해 두면 저장 프로시저를 작성하는 도중에 세미콜론을 입력해도 문제없습니다. End 뒤에 //을 입력하면 create procedure 명령이 실행됩니다.

저장 프로시저를 모두 작성했으면 delimiter ; 로 구분 문자를 원래대로 되돌려 놓습니다.

프로시저를 호출할 때는 call 프로시저명; 을 사용합니다.

select * from table_sales; 와 select * from table_member을 실행하는 저장 프로시저를 생성하겠습니다.

delimiter //

create procedure procedure_01( )

begin

select * from table_sales;

select * from table_member;

end

//

delimiter ;

call procedure_01;

설정한 값 이상인 레코드만 표시하는 저장 프로시저 만들기.

인수를 대입해서 실행하는 저장 프로시저를 만들어 보겠습니다.

처리하고자 하는 데이터를 괄호( ) 안에 대입해서 저장 프로시저를 실행합니다.

다음은 저장 프로시저에 인수를 대입하는 방법입니다.

procedure 저장프로시저명(인수명 자료형)

table_sales에 sales가 인수 s 이상인 레코드를 표시하는 프로시저를 생성해서 호출해 보겠습니다.

delimiter //

create procedure procedure_02(s int)

begin

select * from table_sales where sales >= s;

end

//

delimiter ;

call procedure_02(100);

저장 프로시저의 내용 표시하기.

작성한 저장 프로시저의 내용을 표시할 때에는 다음 명령을 실행합니다.

show create procedure 저장프로시저명;

show create procedure procedure_01;

저장 프로시저 삭제하기.

저장 프로시저를 삭제할 때는 데이터베이스나 테이블, 뷰를 삭제할 때와 마찬가지로 drop 명령을 사용합니다.

명령문은 다음과 같습니다.

drop procedure 저장프로시저명;

drop procedure procedure_01;

굳이 맥이 없어도 인터넷만 연결되어 있으면 간단히 Swift 문법 코딩할 수 있는 요긴한 사이트를 소개 할까 합니다.

IBM Swift Sandbox 라는 사이트 입니다. 아래 링크를 클릭하면 이동합니다.

https://swift.sandbox.bluemix.net/#/repl

왼쪽에 간단히 Swift 코드를 입력하고 밑에 실행 버튼을 클릭하면 오른쪽에 결과가 출력됩니다.

간단히 Swift 문법 공부할 때 요긴 하겠죠? ^^

MySQL 뷰 이용하기.

뷰의 정의

뷰는 무엇일까요?

데이터를 추출할 때 select를 사용해서 다양한 방법으로 데이터를 추출했습니다.

이렇게 select한 결과를 가상 테이블에 저장한 것을 뷰라고 합니다.

뷰는 테이블이 아닙니다. 그러므로 뷰의 레코드와 컬럼에는 데이터가 존재하지 않고 데이터를 추출하기 위한 정보일 뿐입니다.

추출한 것을 뷰로 한번 실행해 두면 사용자 정의 형식의 테이블로 이용할 수가 있습니다.

사용자의 입장에서 보면 뷰와 테이블의 이용 방법에는 큰 차이가 없습니다.

뷰 생성하기.

뷰를 생성하는 방법은 다음과 같이 명령문을 사용합니다.

create view 뷰이름 as select 컬럼명 from 테이블명 where 조건;

결과적으로 어떠한 조건으로 컬럼을 모아 가상의 테이블을 만드는 것입니다.

지금까지 실행했던 많은 select의 결과를 create view ~ as를 이용해서 뷰를 만든다고 생각하면 됩니다.

뷰를 생성해 보겠습니다.

select * from table_member;

create view view_member

as select name, age from table_member;

select * from view_member;

뷰에서 컬럼 값 변경하기.

뷰는 참조 테이블의 일부분을 표시하고 있다고 할 수 있습니다. 따라서 참조 테이블의 값이 변경되면 뷰의 값도 변경됩니다.

뷰는 참조 테이블의 일부분을 표시하는 동시에 참조 테이블의 데이터 창구이기도 합니다. 그래서 뷰의 값이 변경되면 참조 테이블의 값도 변경됩니다.

다음 실습은 view_member 의 강백호를 서태웅으로 변경하여 참조 테이블과 뷰를 확인해 보겠습니다.

update view_member

set name = ‘서태웅’

where name = ‘강백호’;

select * from view_member;

select * from table_member;

위의 결과와 같이 뷰의 값을 변경하면 참조하는 테이블의 데이터도 변경된다는 것이 확인되었습니다.

조건을 설정해서 뷰 만들기.

2개의 테이블을 이용하여 where로 조건을 설정해서 뷰를 만들어 보겠습니다.

table_sales 의 sales 컬럼 조건이 90이상인 레코드만 추출하여 number, sales 와 table_member의 name로 구성된 뷰인 view_sales 를 만들어 보겠습니다.

select * from table_sales;

select * from table_member;

create view view_sales

as select A.number, A.sales, B.name

from table_sales as A

join table_member as B

using(number) where A.sales >= 90;

select * from view_sales;

뷰 확인하기.

어떤 뷰가 있는지 확인하려면 SHOW TABLES를 사용합니다.

show tables;

테이블과 마찬가지로 컬럼의 구조는 DESC로 확인합니다.

desc view_sales;

뷰의 조건에 일치하지 않으면 오류가 나게 설정하기.

뷰에서 insert를 하면 where 조건에 일치하지 않아도 관련 테이블에 데이터가 추가 됩니다.

하지만 조건을 설정한 뷰에 그 조건을 무시하고 데이터가 입력되면 곤란해지는 때도 있습니다. 또한 뷰에서 입력한 데이터를 뷰에서 확인할 수 없다면 곤란하겠지요. 이런 문제가 일어나지 않게 하려면, 뷰에서는 조건에 일치하지 않는 데이터는 입력할 수 없도록 설정할 수 있습니다.

WHERE 조건에 일치하지 않으면 입력할 수 없도록 설정하려면 create view로 뷰를 생성할 때, WITH CHECK OPTION을 추가합니다.

create view view_sales2

as select number, sales from table_sales

where sales > 100

with check option;

insert into view_sales2 values(‘A8’, 50);

뷰 덮어쓰기.

이미 같은 이름의 뷰가 존재할 때 덮어쓰기를 해서 뷰를 생성하는 방법입니다.

Create view를 실행할 때, 생성하려고 하는 이름의 뷰가 이미 존재하면 오류가 발생합니다. 이럴 때 create or replace view 와 같이 or replace를 추가하면 덮어쓰기를 할 수 있습니다. 즉, 이미 존재하는 같은 이름의 뷰를 삭제하고 새롭게 뷰를 생성하게 됩니다.

다음과 같이 사용합니다.

create or replace view view_sales

as select now();

select * from view_sales;

뷰의 컬럼 구조 변경하기.

뷰의 정의를 변경할 때는 alter view를 사용합니다.

명령문은 다음과 같습니다.

alter view 뷰이름 as select 컬럼명 from 테이블이름;

다음 명령을 실행하면 이미 존재하는 view_sales은 table_sales의 컬럼 name, age와 같은 구조로 변경됩니다.

alter view view_sales

as select number, sales from table_sales;

select * from view_sales;

뷰 삭제하기.

MySQL에서 무언가를 삭제할 때에는 drop 명령을 상용했습니다. 뷰를 삭제할 때에도 drop명령을 사용합니다. 데이터베이스나 테이블에 사용하는 명령과 같습니다.

drop view 뷰이름;

단 삭제할 뷰가 존재하지 않으면 오류가 발생합니다.

만약, 다음과 같이 if exists를 추가하면 삭제할 뷰가 존재하지 않아도 오류가 발생하지 않습니다.

drop view if exists view_sales;

MySQL SELECT한 레코드에서 또 SELECT하기 – 하위 질의

하위 질의란?

하위 질의는 서브 쿼리라고도 합니다. 하위 질의는 질의를 실행해서 추출한 데이터를 이용해서 다시 질의를 하는 것을 말합니다.

하위 질의를 이용해서 sales의 최대값 레코드를 표시해 보겠습니다.

select * from table_sales;

select * from table_sales

where sales in(select max(sales) from table_sales);

GROUP BY 함수

MAX(), AVG(), SUM()등 집계를 하는 함수를 GROUP BY 함수라고 하는데 그룹화했을 때 값을 처리하는 함수입니다. 단 GROUP BY 가 없는 경우에는 테이블 전체를 하나의 그룹으로 처리합니다.

평균 이상의 레코드 추출하기.

평균나이 이상인 레코드를 추출해 보겠습니다.

select * from table_member;

select * from table_member

where age >= (select avg(age) from table_member);

IN 사용하기 – 컬럼을 반환하는 하위 질의

이번에는 하위 질의가 컬럼을 반환하는 예입니다.

1단계의 하위 질의에서 조건에 일치하는 컬럼을 반환하고 2단계에서 그 컬럼을 포함한 값을 추출합니다.

명령문은 다음과 같습니다.

select 컬럼명 from 테이블명

where 컬럼명 in (select를 이용한 하위 질의로 추출한 컬럼);

table_sales 에서 sales 가 100이상인 number를 추출하고 table_member에서 해당하는 레코드를 표시해 보겠습니다.

select * from table_member

where number

in (select number from table_sales where sales >= 100)

EXISTS를 사용해서 존재하는 레코드를 대상으로 검색하기.

exists는 특정 컬럼이 아닌 대상이 되는 레코드가 존재하고 있다는 의미입니다.

Table_sales의 number와 table_member의 number가 양쪽에 모두 존재하는 레코드를 추출하고 table_member에서 해당하는 레코드를 표시해 보겠습니다..

select * from table_member as A

where exists (select * from table_sales as B

where A.number = B.number);

select * from table_sales as B where A.number = B.number 은 특정 컬럼이 아니라 조건에 맞는 레코드를 추출하는 것입니다. 그리고 exists를 사용해서 table_member에 존재하는 해당 레코드만 표시합니다.

NOT EXISTS 사용하기.

not exists 는 하위 질의로 추출되지 않은 레코드를 대상으로 합니다.

위의 exists 명령문에 not exists를 적용해서 비교해 보겠습니다.

select * from table_member as A

where not exists (select * from table_sales as B

where A.number = B.number);

하위 질의를 이용한 순위 정하기.

하위 질의를 사용하면 다양한 방법으로 순위를 매길 수 있습니다.

하위 질의를 사용해서 순위를 정하는 방법을 알아 보겠습니다.

1.     table_sales 컬럼 구조만 복사해서 table_rating 를 생성합니다.

2.     table_rating 에 연속 번호 기능을 설정한 rating를 추가합니다.

3.     하위 질의를 사용해서 table_sales의 컬럼 sales를 내림차순으로 정렬하고, number, sales, month를 table_rating에 추가합니다.

create table table_rating like table_sales;

alter table table_rating

add rating int auto_increment primary key;

insert into table_rating (number, sales, month)

(select number, sales, month from table_sales

order by sales desc);

select * from table_rating;