SQL_DAY2

이정찬·2023년 1월 16일
sql
0

SQL

목록 보기
2/4

DAO

DB Access Object (비즈니스 로직, DB에 접근하는 오브젝트를 말한다.)

매우 중요하다. 프로그램 플로우를 클라이언트의 화면부터 본다면, 들어온 데이터 CRUD 요청에 대해서 SQL을 실질적으로 작동시켜주는 부분이기 때문이다. 그리고, DB에서 처리된 값을 클라이언트로 전달하는 역할도 한다.

DAO와 DB 사이에는 Mybatis라는 것이 있다. 나중에 다시 할 예정

요즘은 SQL문이 매우 간소화 되는 추세. Object로도 매칭 가능. ORM이라는 것으로 클래스 단위로 DB와 매핑 시킬 수도 있다.

논리 연산자

1일차 추가: SQL 비교연산자는 !=, <>이 있고, Oracle DB에서는 ^=도 된다.

AND, OR, NOT 등이 있고, 이들은 각각 서로간의 우선순위가 있다. NOT, AND, OR 순으로 우선 계산이 된다. 우선순위 앞으로 빼고 싶으면 괄호로 감싸자.

  • IN: 여러 값 중에서 어느 하나와 일치하는 지를 비교
-- 사원번호가 7369이거나 7521이거나 7782번인 사람을 조회
SELECT * FROM emp WHERE empno IN (7369, 7521, 7782);

-- 사원번호가 7369가 아니고, 7521도 아니고, 7782도 아닌 사람을 조회
SELECT * FROM emp WHERE empno NOT IN (7369, 7521, 7782);

IN을 한 번 쓰는 것이 성능적으로 좋기 때문에, OR, AND를 나열하는건 하지 말자.

SQL 실습

IF(${조건}, ${True일 때 출력할 값}, ${False일 떄 출력할 값})

이런식으로 SELECT절에 IF문 사용 가능하다.

단일행 함수

1. 문자함수

  • CONCAT(...): 컬럼과 컬럼, 컬럼과 문자열 연결, Oracle에서는 ||을 사용한다.
SELECT CONCAT('Good', ' Morning') AS Greeting FROM DUAL;
-- Greeting 컬럼의 Good Morning 데이터로 출력된다.
-- DUAL은 dummy table이다. MySQL은 FROM절 밑 안써도 에러가 안난다.

SELECT CONCAT(ename, ' is a ', job) AS Info FROM emp;
-- Info 컬럼의 모든 데이터가 '${이름} is a ${직업}' 으로 바뀐다.
  • LOWER(${컬럼명}), UPPER(${컬럼명})
SELECT ename, deptno FROM emp WHERE ename='blake';
-- 워크벤치는 대소문자 구분없이 출력이 된다. 그러나 엄밀히는 밑처럼 쓰자.

SELECT ename, deptno FROM emp WHERE LOWER(ename)='blake';
  • SUBSTR(${문자열 | 컬럼명}, ${추출을 시작할 인덱스, 1부터 시작}, ${시작 인덱스 포함 몇 글자})
    문자열 중 일부분을 추출할 떄 사용하는 함수. 시작 인덱스가 음수일때는 -1부터 맨 뒤에서 센다.
    마지막 매개변수를 넣지 않는다면, 그냥 끝까지 추출한다.
SELECT SUBSTR('HelloWorld', 6); -- World
SELECT SUBSTR('HelloWorld', 6, 3); -- Wor
SELECT SUBSTR('HelloWorld', -4, 2); -- or
SELECT SUBSTR(job, 1, 5) FROM emp WHERE job='SALESMAN'; -- SALES
SELECT SUBSTR(hiredate, 1, 4) AS 입사년도 FROM emp; -- 입사년도 컬럼에 년도만 출력
SELECT SUBSTR(hiredate, 6, 2) AS 월 FROM emp -- 월만 출력
  • LEFT(${문자열 | 컬럼명}, ${처음부터 몇 글자}), RIGHT(${문자열 | 컬럼명}, ${끝 부터 몇 글자})
    Oracle에는 없다.
SELECT LEFT(hiredate, 4) AS 입사년도 FROM emp -- 바로 위 년도 쿼리문과 같다.
  • LPAD(${문자열 | 컬럼명}, ${고정 문자열 크기}, ${채울 문자}), RPAD(${LPAD와 동일})
    왼쪽이나 오른쪽에 문자열 빈 공간 채우겠다.
    마지막 매개변수를 넣지 않는다면, Oracle은 default가 공백이지만, 다른데선 에러난다.
SELECT LPAD('abc', 6, '*'); -- ***abc
SELECT RPAD('abc', 6, '*'); -- abc***

SELECT empno, ename, deptno, LPAD(deptno, 5, ' '), LPAD(deptno, 5, '0') FROM emp;
  • INSTR(${문자열 | 컬럼명}, ${찾고자 하는 문자열})
    index string. 특정한 문자, 문자열이 처음 등장하는 위치의 시작 인덱스를 반환해주는 함수.
SELECT INSTR(job, 'MAN') AS 위치 FROM emp WHERE job='SALESMAN'; -- 6
SELECT ename FROM emp WHERE ename LIKE '%LL%';
  • REPLACE(${문자열 | 컬럼명}, ${바꿀 문자열}. ${새로운 문자열})
    문자열 대체
SELECT REPLACE('SALESMAN', 'MAN', 'PERSON') FROM emp;
  • TRIM(${문자열 | 컬럼명}), LTRIM(${동일}), RTRIM(${동일})
    사용자의 버릇 떄문에 공백이 같이 들어오는 경우가 잦다. TRIM() 함수를 사용하는 것을 생활화하자.
    가운데 공백은 안 된다. REPLACE()를 사용하자.
SELECT RTRIM('   Jame Gosling        ') AS msg; -- '   Jame Gosling'
SELECT LTRIM('   Jame Gosling        ') AS msg; -- 'Jame Gosling        '
SELECT TRIM('   Jame Gosling        ') AS msg; -- 'Jame Gosling'
SELECT REPLACE('   Jame Gosling   ', ' ', '') AS msg; -- 'JameGosling'
-- 4개 모두 따옴표는 없다. 그냥 쓴 것
  • 연습
-- EMP 테이블에서 사원의 이름이 N으로 끝나는 사람의 이름 업무 부서번호. 단, 3가지 이상의 방법으로
SELECT ename, job, deptno FROM emp WHERE ename LIKE '%N';
SELECT ename, job, deptno FROM emp WHERE SUBSTR(ename, -1, 1) = 'N';
SELECT ename, job, deptno FROM emp WHERE RIGHT(ename, 1) = 'N';
SELECT ename, job, deptno FROM emp WHERE INSTR(ename, 'N') = LENGTH(ename);
-- N이 여러개라면, 작동안한다

2. 숫자함수

  • ABS(${숫자}) : 절댓값
  • CEILING(${숫자}) : 소수 첫째자리에서 올림, 음수일 때 유의
  • FLOOR(${숫자}) : 소수 첫째자리에서 내림, 음수일 때 유의
  • ROUND(${숫자}, ${자릿 수}) : 반올림, 0은 소수 첫쨰자리에서 반올림. 1은 둘쨰자리에서 반올림, 음수 가능, default 0
  • TRUNCATE(${숫자}, ${자릿 수}) : 기능은 FLOOR()와 같지만, 반드시 자릿수를 지정해 주어야 한다. 안하면 에러

3. 날짜함수

현재 날짜(년, 월, 일)만 찍는 함수

  • CURDATE(), CURRENT_DATE() : 현재 날짜 출력
  • DATE('2023-01-16') : 날짜 변환 함수

현재 시각(시, 분, 초)만 찍는 함수

  • CURTIME(), CURRENT_TIME() : 현재 시간 출력

날짜, 시간 모두 찍는 함수

  • NOW(), SYSDATE() : 년, 월, 일, 시, 분, 초 모두 출력

자주 쓰는 함수

  • YEAR(), MONTH(), WEEKDAY() : 각각 맞는 숫자 반환. WEEKDAY는 월요일이 0부터 시작.
SELECT YEAR(CURDATE()); -- 2023
SELECT MONTH(CURDATE()); -- 1
SELECT WEEKDAY(CURDATE()); -- 0 (월요일)
SELECT DATE_FORMAT(CURDATE(), '%Y %m %d') AS today -- 2023 01 16
  • 날짜 계산 함수

날짜 + 숫자 = 날짜 (DATE_ADD())
날짜 - 숫자 = 날짜 (DATE_SUB())
날짜 - 날짜 = 기간(일) (DATEDIFF())

SELECT CURDATE() + 1 AS tomorrow;
SELECT CURDATE() - 1 AS yesterday; -- 한 달이 제대로 연산이 안된다. 이용 주의

SELECT DATE_ADD(CURDATE(), INTERVAL 100 DAY); -- 2023-04-26으로 정상 작동한다.
SELECT DATE_ADD(CURDATE(), INTERVAL 1 HOUR); -- 2023-01-16 01:00:00
SELECT DATE_ADD(NOW(), INTERVAL 1 HOUR); -- 2023-01-16 15:37:10
SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY); -- 2023-01-15

SELECT CURDATE() - DATE('2023-01-15') AS diff; -- 1은 나오지만, 30일 넘어가면 제대로 안나옴
SELECT DATEDIFF(CURDATE(), '2022-01-16') AS diff -- 365로 정상작동
-- DAY, MONTH, YEAR 모두 가능하다.

4. 변환함수

MySQL과 Oracle은 아예 다르다는 것에 유의한다. MySQL은 CONVERT()와 CAST()가 있으며, CONVERT()를 주로 사용한다.

  • CONVERT(expression, 데이터형식[ (길이) ])
SELECT (CURDATE() - CONVERT('2023-01-15', DATE)); -- 안됨
SELECT ename, job, IFNULL(CONVERT(mgr, CHAR), '상사없음') FROM emp WHERE job='PRESIDENT';
SELECT ename, sal, CONVERT(sal * 12 + IFNULL(comm, 12.2), SIGNED INTEGER) AS 연봉 FROM emp;

추가

SELECT @temp := ${쿼리문};

-- 이 방식으로 SQL문에 변수 선언 가능, 다만, 쿼리문 바깥에 쓰면 static 하게 작동한다. 
-- 쿼리문 내부에 선언해야 계속 변하는 변수로 처리된다.
profile
이정찬
개발자를 꿈꾸는 사람
이전 포스트

SQL_DAY1

다음 포스트

SQL_DAY3

0개의 댓글