멋쟁이 사자처럼 19회차 ( 03 / 26 )

[학습목표]

1. 테이블의 레코드 관계를 이해하고 설명할 수 있다. 2. 문자열 함수를 활용하여 데이터에서 필요한 문자열을 추출 , 변환, 가공하여 원하는 형태로 출력할 수 있다. 3. 숫자 함수를 활용하여 계산, 반올림, 절삭 등 다양한 수치 연산을 수행하고, 그 결과를 리턴값으로 활용할 수 있다. 4. 날짜 및 시간 함수를 활용하여 기간 계산, 날짜 추출 , 포맷 변경 등의 결과를 도출하고 이를 리턴값으로 활용할 수 있다.

 

---

 테이블의 레코드 관계

1) 1 : 1 관계

두 테이블이 레코드가 일대일로 연결되는 단계 

ex) 사용자 테이블과 상세 정보 테이블 , 주민등록증 테이블과 사용자 테이블

 

2) 1 : N 관계 

한 테이블의 레코드가 다른 테이블의 여러 레코드와 연결되는 관계

ex) 고객 테이블과 주문 테이블과 주문 상세 테이블, 부서와 사원 테이블

 

3) N : M 관계

두 테이블의 레코드가 서로 여러 개의 레코드와 연결되는 관계

ex) 학생 테이블과 강의 테이블 , 상품 테이블과 주문 테이블

---

 문자열 함수

문서 참조 : https://dev.mysql.com/doc/refman/8.4/en/string-functions.html

MySQL :: MySQL 8.4 Reference Manual :: 14.8 String Functions and Operators

 

CONCAT()	문자열 연결	CONCAT(ENAME, '-', JOB)
SUBSTR(), SUBSTRING()	문자열 잘라내기	SUBSTR(JOB, 1, 3)
UPPER()	대문자 변환
LOWER()	소문자 변환
LENGTH()	바이트 기준 길이	LENGTH('가') ➔ 3
CHAR_LENGTH()	문자 기준 길이	CHAR_LENGTH('가') ➔ 1
REVERSE()	문자열 뒤집기	REVERSE('SMITH') ➔ 'HTIMS'
LEFT()	왼쪽부터 N글자	LEFT(ENAME, 1) ➔ 'S'
RIGHT()	오른쪽부터 N글자	RIGHT(ENAME, 2) ➔ 'TH'
REPLACE()	특정 문자 치환	REPLACE(ENAME, 'A', 'X') ➔ 'XLLEN'
TRIM()	앞뒤 공백 제거	TRIM(' ALLEN ') ➔ 'ALLEN'
LPAD()	왼쪽에 문자 채우기	LPAD(ENAME, 10, '*') ➔ '*****SMITH'
RPAD()	오른쪽에 문자 채우기	RPAD(ENAME, 8, '!') ➔ 'SMITH!!!'
INSTR()	특정 문자 위치 찾기	INSTR(ENAME, 'E') ➔ 2
LOCATE()	특정 문자 위치 찾기	LOCATE('A', ENAME) ➔ 2
DATE_FORMAT()	날짜 포맷 변경	DATE_FORMAT(HIREDATE, '%Y년 %m월 %d일')
GROUP_CONCAT()	그룹 내 문자열 합치기	GROUP_CONCAT(ENAME) ➔ 'SMITH,ALLEN,WARD'
TO_BASE64()	Base64 인코딩	TO_BASE64('abc') ➔ 'YWJj'
FROM_BASE64()	Base64 디코딩	FROM_BASE64('YWJj') ➔ 'abc'
WEIGHT_STRING()	정렬용 문자 가중치 반환	ORDER BY WEIGHT_STRING(ENAME)

 

 

1) GROUP_CONCAT : 그룹된 값을 (,)로 연결해서 하나의 문자열을 만들어 준다.

[형식]

GROUP_CONCAT([DISTINCT] expr [,expr ...]

[ORDER BY {unsigned_integer | col_name | expr}

[ASC | DESC] [,col_name ...]]

[SEPARATOR str_val]) : STRING

 

ex)

SELECT GROUP_CONCAT(ENAME)  AS RES
FROM EMP;

SELECT  GROUP_CONCAT(ENAME SEPARATOR ' |  ') as message
 FROM EMP;

 

 

 

2) INSTR 함수

 

-- Q3-2. 사원의 이름 중 INSTR 이용해서 A글자를 찾아서 전체 소문자로 출력하자. INSTR(ENAME ,'A') >0

-- INSTR 은 특정 패턴을 찾아 비교할 때 사용하는 함수

SELECT ENAME ,

CASE

WHEN INSTR(ENAME,'A') >0 THEN (LOWER(ENAME))

ELSE ENAME

END AS 결과

FROM EMP;

 

 

3) SUBSTRING, SUBSTR 

SELECT SUBSTRING('Quadratically',-3,1); 
SELECT SUBSTRING('Quadratically',2);   #2번째 위치 부터 나머지   
SELECT SUBSTR('Quadratically',2);

 

4) TRIM

[형식]

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr  FROM] str)

 

ex)

-- Q13. 사원의 이름에서 공백을 제거하고 출력하자. TRIM
mysql> SELECT TRIM('  bar   ');
        -> 'bar'
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
        -> 'barxxx'
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
        -> 'bar'
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
        -> 'barx'
SELECT ENAME , LENGTH(ENAME) , TRIM(ENAME)
FROM EMP;

 

5) REPLACE

 

키값 조심해야 한다!!

PRIMARY KEY or a UNIQUE index -> 테이블에 새로운 행을 삽입할 떄 (PRIMARY KEY or a UNIQUE index 값이 충돌할 때)

기존 행을 삭제하고 새로운 행을 삽입한다. 중복키 처리 방식이 insert 하고 다르다.

새로운 행을 테이블에 삽입 -> PRIMARY KEY OR A UNIQUE INDEX 값이 중복된 값이 있다.

-> 기존 중복행 삭제 -> 새로운 행을 테이블에 삽입

데이터 중복 되지 않을 경우는 일반 INSERT INTO 문인 추가가 된다.

HELP REPLACE -> 행의 데이터 교체 키워드를 설명한다.

 

[형식]

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    { {VALUES | VALUE} (value_list) [, (value_list)] ...
      |
      VALUES row_constructor_list
    }

 

ex)

-- Q24. 사원의 이름에서 모든 'L' 문자를 제거하여 출력하자. REPLACE
-- ex): 'SMITH', 'AEN', 'WARD', ...
SELECT ENAME , REPLACE(ENAME, 'L' , '') FROM EMP;

 

 

---

 숫자 함수

ROUND(숫자, 자릿수)	반올림 (자릿수 생략하면 정수로 반올림)	ROUND(123.456, 2) ➔ 123.46
TRUNCATE(숫자, 자릿수)	버림 (반올림X, 자릿수까지 표시)	TRUNCATE(123.456, 2) ➔ 123.45
CEIL(숫자)	무조건 올림	CEIL(4.1) ➔ 5
FLOOR(숫자)	무조건 내림	FLOOR(4.9) ➔ 4
ABS(숫자)	절대값	ABS(-10) ➔ 10
MOD(숫자, 나누는수)	나머지	MOD(10, 3) ➔ 1
DIV	몫 계산	10 DIV 3 ➔ 3
POWER(숫자, 제곱수)	거듭제곱	POWER(3, 2) ➔ 9
SQRT(숫자)	제곱근 계산	SQRT(25) ➔ 5
LOG(숫자)	자연로그 (밑 e)	LOG(100) ➔ 4.60517
LOG10(숫자)	밑 10 로그	LOG10(1000) ➔ 3
RAND()	0~1 사이 임의 난수	RAND() ➔ 0.3456
IFNULL(컬럼, 대체값)	NULL 처리	IFNULL(COMM, 0)
SUM() OVER()	윈도우 함수로 전체 합계 계산	SAL / SUM(SAL) OVER()
FORMAT(숫자, 자릿수)	숫자를 천 단위 콤마 포함해서 포맷팅	FORMAT(1234567.89, 2) ➔ 1,234,567.89

 

ex) 

--  사원의 봉급(SAL)을 3으로 나눈 결과를 출력하되, 소수점 이하를 모두 표시하도록 설정하자.
-- ex): 봉급 1000 -> 1000 / 3 = 333.3333
SELECT SAL,
    ROUND(SAL / 3, 4) AS "3으로 나눈 값"
FROM EMP;

 

-- 사원의 봉급(SAL)을 기준으로 가장 작은 두 자리 숫자를 만들어 출력하자.
-- ex): 봉급 1234 -> MOD(1234, 100) = 34
SELECT SAL,
    CASE 
        WHEN MOD(SAL, 100) < 10 THEN CONCAT('0', MOD(SAL, 100))
        ELSE MOD(SAL, 100)
    END AS RES
FROM EMP;

 

 SELECT  TRUNCATE(4.567, 2); # 4.56
 SELECT  TRUNCATE(4.567, 0); # 4
 SELECT  TRUNCATE(-3.958, 2); # - 3.95

 SELECT  CEIL(4.567); # 올림해서 5를 리턴  
 SELECT  CEIL(4.567, 0); #    -- 오류난다.  
 SELECT  CEIL(-3.958, 2); #    -- 오류난다.

 

---

 날짜 및 시간 함수

- 현재 날짜 및 시간

NOW(): 현재 날짜와 시간을 리턴.
CURDATE(): 현재 날짜를 리턴.
CURTIME(): 현재 시간을 리턴.

- 날짜 및 시간 추출
YEAR(date): 날짜에서 연도를 추출.
MONTH(date): 날짜에서 월을 추출.
DAY(date): 날짜에서 일을 추출.
HOUR(time): 시간에서 시를 추출.
MINUTE(time): 시간에서 분을 추출.
SECOND(time): 시간에서 초를 추출.
DAYNAME(date): 날짜의 요일 이름을 리턴.
DAYOFWEEK(date): 날짜의 요일을 숫자로 리턴. (1=일요일, 7=토요일).

 

- 날짜 계산

DATE_ADD(date, INTERVAL value unit): 날짜에 지정된 시간 간격을 추가.
DATE_SUB(date, INTERVAL value unit): 날짜에서 지정된 시간 간격 리턴.
DATEDIFF(date1, date2): 두 날짜 간의 차이를 일수로 리턴.

 

- 날짜 형식 변환

DATE_FORMAT(date, format): 날짜를 지정된 형식으로 리턴. 예: '%Y-%m-%d', '%d/%m/%Y'.
STR_TO_DATE(string, format): 문자열을 날짜 형식으로 변환

 

- 시간 계산

ADDDATE(date, INTERVAL value unit): 날짜에 시간 간격을 추가 (DATE_ADD와 유사).
SUBDATE(date, INTERVAL value unit): 날짜에서 시간 간격 리턴 (DATE_SUB와 유사).
TIMESTAMPADD(unit, value, datetime): 주어진 단위와 값을 날짜에 추가
TIMESTAMPDIFF(unit, datetime1, datetime2): 두 날짜/시간 간의 차이를 리턴.

 

- 현재 시간 관련 함수

UTC_DATE(): 현재 UTC 날짜를 리턴.
UTC_TIME(): 현재 UTC 시간을 리턴.
UTC_TIMESTAMP(): 현재 UTC 날짜와 시간을 리턴.

 

- 날짜 및 시간 파트

LAST_DAY(date): 주어진 월의 마지막 날짜를 리턴.
QUARTER(date): 날짜가 속한 분기를 리턴. (1-4).
WEEK(date, [mode]): 날짜가 속한 주 번호를 리턴. mode는 주의 시작일을 설정.

 

- 날짜 및 시간의 파트 조합

DATE(date): 날짜 부분만 리턴.
TIME(time): 시간 부분만 리턴.
TIMESTAMP(date, time): 날짜와 시간을 결합하여 리턴.

 

- 현재 날짜 및 시간 포맷

FORMAT(date, format): 날짜를 특정 포맷으로 리턴.
TIME_FORMAT(time, format): 시간을 특정 포맷으로 리턴.

 

ex)

-- emp 테이블에서 현재 날짜 기준으로 입사일이 10년 이상 된 사원의 목록을 출력하라.
-- 예: ID, 이름, 입사일
SELECT empno, ename, hiredate
FROM emp
WHERE hiredate <= DATE_SUB(CURDATE(), INTERVAL 10 YEAR);

-- dept 테이블에서 현재 날짜와 가장 가까운 과거의 부서 설립일을 출력하라.
-- 예: 부서번호, 설립일
SELECT deptno, loc
FROM dept
WHERE loc <= CURDATE()
ORDER BY loc DESC
LIMIT 1;