[DB] 한 번에 끝내는 SQL & Database Part 3

 stored procedure는 코드 덩어리 저장가능 

프로그래밍의 함수문법이랑 같다

이제 앞으로 CALL procedure이름() 만 실행하면 BEGIN/END 사이에 저장해놨던 코드가 실행

 

procedure 생성하는 SQL 명령어

DROP PROCEDURE IF EXISTS 데이터베이스명.get_all;

DELIMITER $$
$$
CREATE PROCEDURE 데이터베이스명.get_all()
BEGIN
  SELECT * FROM product where 가격 > 5000;
END 
$$
DELIMITER ;

 

 

변수 문법

SET @변수명 = 저장할값; 

SET @변수명 := 저장할값; 

SELECT @변수명 := 저장할값; 

 

@변수에는 숫자, 문자, binary, NULL 이런 자료 1개만 간단하게 저장할 수 있다

 

procedure 안에서 변수만들어쓰기

CREATE PROCEDURE mart.var_test() 
BEGIN 
  DECLARE 변수1 INT;
  DECLARE 변수2 VARCHAR(100);
  DECLARE 변수3 INT DEFAULT 123;
END

1. DECLARE 변수명 데이터타입; 여기는 변수명에 @ 안써도 됨.

2. 다만 BEGIN 바로 밑에 넣어야 함

3. DEFAULT 어쩌구는 변수만들 때 초기값을 넣어주는 부분. 선택사항임

4. DECLARE 로 만든 변수에 데이터 저장시엔 똑같이 SET 키워드로 변수에 마음대로 데이터 저장가능

 

DECLARE 변수 vs @변수 차이점 

1. 변수를 사용할 수 있는 범위의 차이

@변수는 user variable 이라고 부르는데 

한 번 만들어놓으면 DBMS 프로그램 끌 때 까지 (연결 종료 까지) 남아있다.

그리고 작성하는 SQL 파일 모든 곳에서 전역으로 사용가능

그래서 가끔은 @변수를 똑같은 이름으로 중복선언하는 실수도 발생

 

반면 DECLARE 변수는 변수를 만든 procedure 안에서만 사용할 수 있다. 

그래서 마음대로 만들어도 안전 

 

2. 변수가 언제 사라지는지도 차이가 있습니다.

@변수는 DB연결 종료시 사라짐

DECLARE 변수는 procedure 실행 종료시 바로 사라짐

 

구멍뚫기 문법 

CREATE PROCEDURE mart.get_all(구멍 INT)
BEGIN
    SELECT * FROM product WHERE 가격 > 구멍;
END

CALL get_all(6000)

 

파라미터 문법 세부사항 

CREATE PROCEDURE mart.get_all( 구멍1 INT, 구멍2 varchar(100) )
BEGIN
    SELECT * FROM product WHERE 가격 > 구멍1 OR 상품명 = 구멍2;
END

 

OUT 파라미터 : 역으로 procedure 안에서 출현한 숫자나 문자나 테이블을 밖으로 가져올 수도 있다

CREATE PROCEDURE mart.get_all(OUT 구멍 INT)
BEGIN
    SET 구멍 = 20;
END

 

 

 

날짜 저장하려면 

 

MySQL의 경우 이런 것 중에 고르면 됨 

DATE	YYYY-MM-DD 형식	9999년까지 저장가능
DATETIME	YYYY-MM-DD hh:mm:ss 형식	9999년까지 저장가능
TIMESTAMP	YYYY-MM-DD hh:mm:ss 형식	2038년까지 저장가능

 

TIMESTAMP는 현재 DB시간 기록용 말고는 잘 안씀

테이블 만들 때 DATETIME(6) 이렇게 1~6 숫자를 넣으면 초단위 소수점 6자리까지 기록

 

날짜 포맷 마음대로 바꾸기

SELECT date_format(now(), '%Y년 %d일이고 %m월인데요')

 

 

function 만들 때 필요한 문법 : 무조건 어떤 데이터를 RETURN 해야한다는게 procedure와 가장 큰 차이점

CREATE FUNCTION DB이름.함수이름(구멍 INT) 
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN 100;
END

- 긴 쿼리문을 자주 재사용하고 싶을 때는 procedure 

- 계산기능을 만들었는데 그걸 자주 재사용하고 싶을 때는 function 

- procedure는 CALL 문법으로 소환해야함, function은 CALL 문법 필요없음 

- procedure는 RETURN (그니까 OUT 파라미터) 없어도 됩니다. function은 무조건 RETURN 있어야함 

- procedure는 쿼리문 중간에 갑자기 사용불가능, function은 자유롭게 거의 아무데서나 사용가능 

 

직접 SQL 코드짜서 function 만들고 싶으면 

DROP FUNCTION IF EXISTS 함수명;
DELIMITER $$

CREATE FUNCTION 함수명() 
RETURNS INT
BEGIN 
  어쩌구
  RETURN 저쩌구;
END 
$$
DELIMITER ;

 

 

MySQL의 function 안에서는 SELECT 문법으로 출력하는건 금지 

SELECT한걸 변수에 집어넣어주는 SELECT INTO 문법 이런건 허용

서브쿼리용도로 쓰는 (SELECT 어쩌구) 이런 것도 허용

테이블 출력만 금지임 

 

조건에 따라 다른 코드를 실행하고 싶으면 IF THEN ELSE

다른 DBMS는 BEGIN/END라고 대충 적으면 그 안에서 IF 문법을 사용할 수 있는 경우가 있는데 

MySQL은 procedure 아니면 function 안에서만 IF를 사용가능. 

IF 조건식1 THEN 
  조건식1이 참이면 실행할 쿼리문;
ELSEIF 조건식2 THEN 
  조건식2가 참이면 실행할 쿼리문;
ELSE 
  그게 아니면 실행할 쿼리문;
END IF;

 

 

컬럼을 잘라내서 tree 형태로 정렬해둔걸 전문용어로 index라고 하는데,

=, >, >=, <, <=, BETWEEN, LIKE 연산자를 사용할 때 index가 사용 

LIKE 사용시엔 시작이 %기호가 아닐 때만 (LIKE 어쩌구% 일 때만) index를 사용

 

하지만 index는 자동생성되진 않음 

자동으로 index가 적용되는 컬럼이 있음 : primary key 컬럼은 기본적으로 검색이 매우 빠르기 때문에 index 만들 필요는 없다.

테이블에 index 생성하려면

SQL 문법으로 index 만들고 싶으면 

CREATE INDEX 인덱스이름작명 ON 테이블명 (컬럼명);

 

다중컬럼 index 생성은

CREATE INDEX 인덱스이름작명 ON 테이블명 (컬럼명1, 컬럼명2);

 

 

Full text search를 위한 index 

Full text index를 이용해 검색하려면 MATCH() AGAINST() 이런 특별한 문법이 필요

SELECT * FROM library WHERE MATCH(서명) AGAINST('부동산');

SELECT * FROM library WHERE MATCH(서명) AGAINST('부동산' IN BOOLEAN MODE);

IN BOOLEAN MODE에선 * 기호를 이용가능한데

* 기호를 넣어주면 %기호와 유사하게 동작

여러 단어를 넣으면 OR 연산. 

+ 기호를 앞에 붙이면 해당단어가 꼭 들어가있는 것만 찾아줌 ( 대충 AND 연산이랑 비슷함 )

- 기호를 앞에 붙이면 해당단어를 걸러줌 ( 대충 NOT 연산자랑 비슷함 )

 

 

 IN NATURAL LANGUAGE MODE + ngram parser index를 적용하면

검색어를 2개 단어 단위로 쪼개서 하나라도 일치하는 모든 결과를 검색

장점1. 띄워쓰기 틀려도 index는 똑같이 잘 생성

장점2. "식투" 이상하게 검색해도 "주식투자"나옴

CREATE FULLTEXT INDEX 인덱스이름작명 ON 테이블명(컬럼명) WITH PARSER ngram;

 

 

Transaction 기능

START TRANSACTION;
이거 INSERT 해주세요~;
저거 UPDATE 해주세요~;
COMMIT;

START TRANSACTION;
이거 INSERT 해주세요~;
저거 UPDATE 해주세요~;
ROLLBACK;

COMMIT 말고 ROLLBACK이라는 명령어를 마지막에 실행하면

처리가 보류되었던 쿼리문들이 실행취소

 

START TRANSACTION;
테이블에 있는 A 계좌에 -1000원 하기 ~~;
테이블에 있는 B 계좌에 +1000원 하기 ~~;
하나라도 row 변경에 실패하면 ROLLBACK 하고 별문제없으면 COMMIT ;

 

SQL 문법으로 성공시 COMMIT, 에러시 ROLLBACK 만들기 

DROP PROCEDURE IF EXISTS 테스트.transaction_test; 
DELIMITER $$ 
CREATE PROCEDURE 테스트.transaction_test() 
BEGIN 

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN 
    ROLLBACK;
  END;
  
  START TRANSACTION;
    INSERT INTO 테스트.teacher VALUES (10, '테스트', '테스트','ㄴㅇㄹ'); 
  COMMIT; 
  
END$$ 
DELIMITER ; 

CALL 테스트.transaction_test();

 

 

Trigger 사용법

Trigger는 INSERT/UPDATE/DELETE 실행시 자동으로 함께 실행되는 코드를 뜻

데이터베이스에 INSERT UPDATE DELETE 하기 전에

자동으로 실행하고 싶은 코드가 있으면 Trigger 메뉴 들어가서 설정

 

- 데이터를 다른 테이블에 반영하고 싶을 때

- 데이터 넣기 전에 데이터를 깔끔하게 정제하고 싶을 때 

- 테이블 변경기록 (로그)를 다른 테이블에 저장해두고 싶을 때

- 테이블의 통계를 다른 테이블에 저장해두고 싶을 때 

 

DROP TRIGGER IF EXISTS db명.트리거이름;
DELIMITER $$
CREATE TRIGGER db명.트리거이름
AFTER INSERT ON db명.테이블명 
FOR EACH ROW 
BEGIN

  실행할코드~~

END $$
DELIMITER ;

 

OLD, NEW 문법

OLD는 그래서 UPDATE 전, DELETE 전의 자료가 담겨있고,

NEW는 그래서 UPDATE 후, INSERT 후의 자료

 

 

엔티티 관계도 쉽게 잘그리는 법 (ERD)

https://www.lucidchart.com/pages/

Intelligent Diagramming | Lucidchart

 

 

서버에서 DB 사용하려면 (nodejs 환경)

그래서 웹서비스를 운영하는데 DB 입출력 어떻게 합니까

 

 

그래서 고객이 무슨 짓을 할 지 모르기 때문에 DB를 다룰 땐 보통은 3 tier architecture 를 사용합니다.

이게 뭐냐면 우선 서버프로그램, 고객용프로그램, 데이터베이스 이렇게 3개를 각각 분리해놓고

 

데이터 저장은 고객용프로그램 -> 서버 -> DB 이런 순서로

데이터 출력은 DB -> 서버 -> 고객용프로그램 이런 순서로

 

 

1. nodejs를 구글에 검색해서 LTS라고 표기된 버전을 설치

 

2. VSCode 에디터로 코드짤 작업폴더를 오픈(VSCode 없으면 설치하쇼)

 

3. 작업폴더에 server.js 파일

 

4. 에디터 상단에서 Terminal을 열어서 

npm init -y 입력합니다. 프로젝트마다 1회만 하면 되는 기본 셋팅임

npm install mysql2

입력해서 MySQL조작을 도와주는 라이브러리를 설치

mysql2 말고 mysql도 있는데 일반적으론 mysql2 설치. 문법이랑 기능은 거의 똑같은데 mysql2가 더 빠를 뿐 

 

5. mysql2 기본셋팅

mysql2는 MySQL 쉽게 연결해주는 라이브러리

const mysql = require('mysql2');

const connection = mysql.createConnection({
host : 'localhost',  
user : 'DB접속아이디',
password : 'DB접속비번',
database : '접속원하는Database명'
});

connection.connect(function(err) {
  if (err) {
    console.error('연결실패 :' + err.stack);
    return;
  }
  console.log('연결된듯');
});

server.js에 이런 코드 작성하고

터미널에서 node server.js 실행하면 연결

 

서버 -> DB 이렇게 SQL 쿼리를 날리고 싶으면

connection.query('SELECT 어쩌구', function (에러, 결과, 필드) {
  if (에러){ console.log(에러) }
  console.log('result : ', 결과);
});

 

SQL injection 공격 해결방법

connection.query('SELECT * FROM table1 WHERE 상품명 = ' + connection.escape('유저가입력한상품명'), function (에러, 결과, 필드) {
  
});

connection.query('SELECT * FROM users WHERE id = ?', ['유저가입력한거'], function (error, results, fields) {

});

유저가 입력한 모든 자료는 connection.escape() 안에 넣거나, [ ] 안에 넣기

 

 

 

 

 

 

/강의 출처: 코딩애플/