SQL 기초(3)

1) Built-in Functions

1-1. 문자열 함수

• 코드/길이

  • ASCII(str), CHAR(n)
  • BIT_LENGTH(str), CHAR_LENGTH(str), LENGTH(str)

• 결합

  • CONCAT(a,b,...), CONCAT_WS(delim, a,b,...)

  SELECT CONCAT('호랑이','기린'), CONCAT_WS('-', '2023','05','31');

• 검색/위치

  • ELT(pos, ...), FIELD(str, ...), FIND_IN_SET(str, csv), INSTR(str, sub), LOCATE(sub, str)

• 포맷/진법

  • FORMAT(num, d), BIN(n), OCT(n), HEX(n)

• 부분/치환/반전

  • INSERT(str, pos, len, new), LEFT(str,n), RIGHT(str,n)
  • SUBSTRING(str, pos[,len]), SUBSTRING_INDEX(str, delim, count)
  • REPLACE(str, from, to), REVERSE(str)

• 대소문자/패딩/공백/반복

  • LOWER(str), UPPER(str)
  • LPAD(str, len, pad), RPAD(str, len, pad)
  • LTRIM(str), RTRIM(str), TRIM([LEADING|TRAILING|BOTH rem FROM] str)
  • REPEAT(str, count), SPACE(n)

1-2. 숫자 함수

• 기본

  • ABS(x), CEILING(x), FLOOR(x), ROUND(x)
  • TRUNCATE(x, d) -- d<0 이면 정수 자리에서 버림

• 연산/진법

  • MOD(a,b) / a % b / a MOD b
  • POW(a,b), SQRT(x)
  • CONV(val, fromBase, toBase)

• 난수/부호

  • RAND() (0≤x<1)
    정수 m~n(포함) → FLOOR(RAND() * (n - m + 1)) + m
  • SIGN(x) → { -1, 0, 1 }

1-3. 날짜·시간 함수

• 더하기/빼기

  • ADDDATE(date, INTERVAL expr unit), SUBDATE(...)
  • ADDTIME(dt, 'h:m:s'), SUBTIME(dt, 'h:m:s')

• 현재 시각 계열

  • CURDATE(), CURTIME(), NOW()/SYSDATE()

• 추출/변환

  • YEAR(d), MONTH(d), DAYOFMONTH(d)
  • HOUR(t), MINUTE(t), SECOND(t), MICROSECOND(t)
  • DATE(dt), TIME(dt)

• 차이/정보

  • DATEDIFF(d1, d2) (일수), TIMEDIFF(t1, t2)
  • DAYOFWEEK(d)(1=일), MONTHNAME(d), DAYOFYEAR(d)
  • LAST_DAY(d)

• 생성/분기/초 단위

  • MAKEDATE(year, dayOfYear), MAKETIME(h, m, s)
  • QUARTER(d), TIME_TO_SEC(t)

---

2) VIEW

2-1. 개념

• SELECT를 저장한 가상 테이블 (데이터는 원본 테이블에 저장)
• 읽기/권한 분리/복잡 쿼리 캡슐화에 유용

2-2. 생성/조회

CREATE VIEW hansik AS
SELECT menu_code, menu_name, menu_price, category_code, orderable_status
  FROM tbl_menu
 WHERE category_code = 4;

SELECT * FROM hansik;

• 원본 데이터 변경 시 VIEW 결과도 즉시 반영

2-3. VIEW를 통한 DML

• VIEW로 INSERT/UPDATE/DELETE 하면 베이스 테이블에 반영
• PK가 AUTO_INCREMENT가 아니면 INSERT 시 직접 값 지정 필요

INSERT INTO hansik VALUES (99, '수정과맛국밥', 5500, 4, 'Y');
UPDATE hansik SET menu_name='버터맛국밥', menu_price=5700 WHERE menu_code=99;
DELETE FROM hansik WHERE menu_code=99;

DML 불가(또는 제한) 케이스

• 뷰에 없는 컬럼 조작
• 뷰 밖 원본 컬럼이 NOT NULL인데 값 공급 불가
• 계산식 컬럼, JOIN, DISTINCT, GROUP BY/집계 포함 등

2-4. 수정/삭제

CREATE OR REPLACE VIEW hansik AS
SELECT a.menu_code AS '메뉴코드', a.menu_name '메뉴명', b.category_name '카테고리명'
  FROM tbl_menu a JOIN tbl_category b USING (category_code)
 WHERE b.category_name='한식';

DROP VIEW hansik;

---

3) INDEX

3-1. 개념 & 팁

• 검색 속도 향상 구조 (주로 WHERE/JOIN 컬럼에 생성)
• 부작용: 공간 사용, DML 시 인덱스 갱신 비용 → 선별적 생성 필요
• 활용 확인: EXPLAIN으로 실행 계획 체크

3-2. 생성/조회

-- 단일
CREATE INDEX idx_name ON phone (phone_name);

-- 복합(선두 컬럼 중요: (a,b) 인덱스는 a만 걸린 조건에도 사용 가능)
CREATE INDEX idx_name_price ON phone (phone_name, phone_price);

SHOW INDEX FROM phone;

3-3. 사용/검증

SELECT * FROM phone WHERE phone_name='iPhone14pro';
EXPLAIN SELECT * FROM phone WHERE phone_name='iPhone14pro';

3-4. 최적화(재구성) & 테이블 최적화

ALTER TABLE phone DROP INDEX idx_name;
ALTER TABLE phone ADD  INDEX idx_name (phone_name);

-- InnoDB: 테이블/인덱스 최적화
OPTIMIZE TABLE phone;

3-5. 삭제

DROP INDEX idx_name ON phone;
SHOW INDEX FROM phone;