db 기본

AI·2025년 9월 29일

https://dev.mysql.com/doc/refman/8.4/en/functions.html

프론트에서 관리

select abs(-78), abs(78);

select concat(last_name, ' ', first_name) full_name from employees;
select upper(last_name), lower(last_name) from employees;
select length('abc'), length('안녕'), char_length('abc'), char_length('안녕'); -- 3,6,3,2

시간 관련

select orderid, orderdate, adddate(orderdate, interval 10 day) from orders;
select orderid, orderdate, adddate(orderdate, interval 10 month) from orders;
select orderid, orderdate, adddate(orderdate, interval 10 year) from orders;
select orderid, orderdate, adddate(orderdate, interval -10 day) from orders;

select sysdate(); -- 함수가 호출될 때마다 값 변경됨
select now(); -- 쿼리가 실행될때마다 동일한 값
insert into orders(orderdate, adate, bdate) value(now(), now(), now());

입력 일시, 수정 일시, 입력자 id, 수정자 id - now() 사용
sysdate() - 로그 기록용으로 주로 사용

되도록 null value가 안생기도록 => not null, default value
=> null check

-- Mybook 스키마 생성 -- MySQL
CREATE TABLE Mybook (
  bookid      INTEGER,
  price       INTEGER 
);
-- Mybook 데이터 생성
INSERT INTO Mybook VALUES(1, 10000);
INSERT INTO Mybook VALUES(2, 20000);
INSERT INTO Mybook VALUES(3, NULL);
COMMIT;

select price+100 from mybook;
select sum(price), avg(price), count(*), count(price) from mybook;
-- ifnull(mysql) / nvl(oracle)
select * from customer;
select custid, name, address, ifnull(phone, '연락처없음') from customer;

view

create view vorders as 
select o.orderid, c.custid, c.name, b.bookid, b.bookname, o.saleprice, o.orderdate 
	from customer c, orders o, book b
	where c.custid = o.custid
    and b.bookid = o.bookid;

select * from vorders;
==
select a.* from(
select o.orderid, c.custid, c.name, b.bookid, b.bookname, o.saleprice, o.orderdate 
	from customer c, orders o, book b
	where c.custid = o.custid
    and b.bookid = o.bookid
) a;

데이터가 들어가 있는게 아니라 쿼리만 관리하는 거임 => 쿼리 안에 있던 내용이 바뀌면 view도 변화함

보안때문에 view를 쓰는 경우도 있음

case when-then-else-end

use hr;
select department_id, sum(salary) from employees
	where department_id in (60,90)
    group by department_id;
-- row 1개 
select sum(case when department_id = 60 then salary else 0 end) sum60, 
	sum(case when department_id = 90 then salary else 0 end) sum90 
    from employees
	where department_id in (60,90);

exists

-- in
select * from customer where customer_id 
	in (select customer_id from customer_order); -- 1건

-- exists
select * from customer 
	where exists (select customer_id from customer_order); -- 2건
    
select * from customer 
	where exists 
    (select customer_id from customer_order where order_id=2); -- 0건
    
select c.* from customer c 
	where exists 
    (select co.customer_id from customer_order co 
    where co.customer_id=c.customer_id); -- 1건

A exists(B) B가 1건 이라도 존재한다면, A 쿼리문 실행
=> corelated sub query에 사용

not

select * from customer where customer_id 
	not in (select customer_id from customer_order);
select c.* from customer c where not exists 
	(select co.customer_id from customer_order co 
    where co.customer_id=c.customer_id);

not in은 join을 통해 index를 활용할 수 있음.
=> null이 아닌 것만 사용
not exists는 null이 있어도 가능

select * from customer where customer_id not in 
	(select customer_id from blacklist 
    where customer_id is not null);
    
select c.* from customer c where not exists 
	(select b.customer_id from blacklist b 
    where b.customer_id=c.customer_id);

corelated sub query가 매줄마다 실행을 함에도 속도 차이가 많이 안남
=> 옵티마이저(Optimizer)가 매우 똑똑해졌기 때문

데이터 건수가 많으면 in이 좋지 못 함

join은 나누어진 테이블 정보를 가져가서 더 많은 정보를 얻기 위해서
corelated sub query는 true, false로 조건을 따지는 것 = 필터링을 하는 것 => 데이터가 많아지면 특정 데이터에서만 조건 처리를 할 수 있으니 더 빨라질 수 있다고 생각했지만 그렇지는 않음. 중첩 반복문 같은 느낌

파일 추가하는 법>

server -> Data Import

Import from Self-Contained File에서 파일 선택 -> 스키마 선택 후

start import 클릭

위 화면이 나오고 스키마에서 refresh해서 뜨면 완료

index

-- index
select count(*) from jdbc_big; -- 1,000,000
select * from jdbc_big; -- 1000개(limit수)
select * from jdbc_big where col_1=12341; -- pk로 조회, cost:1
select * from jdbc_big where col_2='표상섭'; -- full table scan, cost:100,603

create table jdbc_big2 as select * from jdbc_big;
insert into jdbc_big2 select * from jdbc_big;
select count(*) from jdbc_big2; -- 8,000,000

select * from jdbc_big2 where col_2='박승아'; -- cost:809,463 => 7,656

만들다가 에러남

Executing:
ALTER TABLE `test`.`jdbc_big2` 
ADD INDEX `idx_col_2` (`col_2` ASC) VISIBLE;
;

Operation failed: There was an error while applying the SQL script to the database.
ERROR 2013: Lost connection to MySQL server during query
SQL Statement:
ALTER TABLE `test`.`jdbc_big2` 
ADD INDEX `idx_col_2` (`col_2` ASC) VISIBLE

=> 해결법

값 3600으로 크게 변경

pk는 자동으로 index 생성

다른 테이블의 pk = fk => 이걸 설정을 안하면 유령 데이터가 생기게 됨

ERD 만드는 법

FK를 만들면 자동으로 index 생성
FK 생성 후에 키가 겹치게 insert할 경우, 무결성이 지켜짐

insert into orders values (11,6,100, now());
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`madang`.`orders`, CONSTRAINT `FK_CUSTID` FOREIGN KEY (`custid`) REFERENCES `customer` (`custid`))

점선 = non-identifying FK - orders에서 pk가 아님

삭제 하려고 해도 에러

delete from customer where custid = 1;
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`madang`.`orders`, CONSTRAINT `FK_CUSTID` FOREIGN KEY (`custid`) REFERENCES `customer` (`custid`))

delete from customer where custid = 5; -- orders에 없는 데이터면 삭제 가능

bookid도 FK로 만들기

index & FK에 대해 다양하게 검색 및 학습

https://velog.io/@bienlee/데이터베이스-키Key와-인덱스Index에-대해
https://ittrue.tistory.com/331#google_vignette

외래 키는 데이터의 '무결성'을, 인덱스는 '검색 성능'을 담당

인덱스>

목적: SELECT 쿼리의 WHERE 절이나 JOIN 구문의 성능 향상
장점: 검색 속도 향상
단점: 데이터 INSERT, UPDATE, DELETE 시 인덱스도 함께 수정해야 하므로 약간의 성능 저하가 발생하며, 추가적인 저장 공간이 필요
인덱스가 없다면 "Full Table Scan"을 해야해서 시간 차이가 많이 남

사용하는 경우:

대량의 데이터를 검색하는 경우
인덱스를 사용하여 검색 속도를 향상. 대량의 데이터를 전체 스캔하는 것은 매우 느리고 부하가 발생하기 때문에 이 경우에 인덱스를 사용하여 검색하는 것이 효율적.
정렬된 결과를 출력하는 경우
인덱스를 사용하여 데이터를 정렬하면 매우 빠르게 정렬된 결과를 출력할 수 있음. 따라서 데이터를 정렬하는 경우에는 인덱스를 사용하는 것이 좋음.
조인 연산을 수행하는 경우
인덱스를 사용하여 연산 속도를 향상시킬 수 있음. 인덱스를 생성하여 조인 대상 테이블의 데이터를 빠르게 검색하는 것이 좋음.
유니크한 값을 가져오는 경우
인덱스는 유니크한 값을 가지고 있는 필드에 대해 중복되지 않는 값을 빠르게 검색할 수 있음. 이러한 경우 인덱스를 사용하여 검색 속도를 빠르게 할 수 있음.
검색 빈도가 높은 경우
검색 빈도가 높은 필드에 대해서 인덱스를 생성하여 검색 속도를 향상시키는 것이 좋음.

구현 방식:
B-Tree 인덱스 - 범위 검색에 유리
Hash Table - 특정 값의 검색에 유리
작동 원리:
'키(key)'와 '포인터(pointer)'를 사용하여 데이터의 물리적 위치를 빠르게 찾음

인덱스는 키를 기준으로 정렬하며
각 키는 테이블의 특정 행을 가리키는 포인터를 가짐
=> 이진 검색(Binary Search) 또는 트리 검색(Tree Search)를 수행하여 O(Log n))로 수행

PK의 경우 인덱스가 자동으로 생성
FK나 일반열의 경우 개발자가 명시적으로 생성

최적화 방식:

쿼리 분석
인덱스 분할(Partitioning)
인덱스 커버링(Index Covering)

외래키 fk

목적: 두 테이블 간의 관계를 정의하고, 참조하는 데이터만 입력/수정/삭제되도록 하여 데이터의 정합성을 유지

외래키가 설정된 컬럼은 다른 테이블을 조회하거나(JOIN), 데이터가 변경될 때 참조 테이블을 확인하는 작업이 매우 빈번하게 일어남. 이때 인덱스가 없으면 심각한 성능 저하가 발생.

기본키 pk

자동으로 생성되는 특별한 종류의 인덱스
모든 기본키는 인덱스이지만, 모든 인덱스가 기본키는 아님

역할:
유일성(Unique): 모든 행이 서로 다른 값을 가져야 함. 중복은 허용되지 않음.
개체 무결성(Not Null): 절대 비어 있을 수 없음. 모든 행은 반드시 값을 가져야 함.

번외) 실무에서 fk를 잘 안쓰다고 하는 이유는?

대규모 트래픽 환경에서의 성능 저하와 시스템 복잡성 증가

분산 시스템이나 초당 수만 건의 데이터 쓰기가 발생하는 서비스에서 두드러지는 경향이라고 함
INSERT, UPDATE, DELETE 할 때마다 참조 무결성 검사가 추가로 발생하기에 대량 데이터 입력이나 부모 테이블 변경시 성능이 저하될 수 있음

여러 데이터베이스에 데이터를 나누어 저장하는 샤딩(Sharding)이나 분산 데이터베이스 환경을 도입할 때, DB가 복잡해짐

데이터의 일관성을 맞추기 위한 서버 간 통신이 필요해지며, 이는 시스템의 응답 속도를 저하하고 관리 포인트를 기하급수적으로 늘림. 이 때문에 대규모 분산 환경에서는 데이터 정합성을 데이터베이스(DB)가 아닌 애플리케이션 로직에서 처리하는 방식을 선호한다고 함

강한 결합(Tight Coupling)으로 유연성을 떨어뜨림

스키마 변경의 어려움: 테이블 구조를 변경하거나 마이그레이션할 때 FK 제약 조건 때문에 작업 순서가 매우 중요하여 관련 테이블을 모두 고려해야 해서 작업이 복잡해지고 실수할 가능성이 커짐.
테스트의 어려움: 테스트 데이터를 생성하거나 삭제할 때도 참조 관계를 모두 맞춰야 하므로 번거로움.
순환 참조: 두 테이블이 서로를 참조하는 순환 참조 구조가 만들어질 경우, 데이터 입력이나 삭제가 매우 까다로워지는 문제가 발생할 수 있음.