[25일 차] 데이터베이스 활용(Ch04)

---

25/02/28 (금)

오 벌써 또 금요일입니다.

2월의 마지막날이기도 하군요.

오늘따라 이상하게 더 피곤하지만 힘내보겠습니다!

---

 

1.  강의

CH04 | SQL 고급

4.1) 내장 함수, NULL

1. SQL 내장 함수

숫자 함수

• ABS 함수

select abs(-78), abs(78) from dual;
select @@squl_safe_updates;

 

• ROUND 함수

select custid, round(sum(saleprice)/count(*), -2) from orders group by custid;

---

문자함수

• REPLACE 함수
• LENGTH, CHAR_LENGTH 함수

 -- 한글 uft-8(3byte), uft-16(4byte), euc-kr(2byte)
 -- '축구의 역사' : 한글5+공백1 = 16
select bookname, length(bookname), char_length(bookname) from book where bookid in (1, 10);

 

• SUBSTR 함수

select substr(name, 1, 1) '성' ...

---

날짜, 시간 함수

select adddate('2025-02-28', INTERVAL 5 DAY);

 

• STR_TO_DATE 함수, DATE_FORMAT 함수
  • 많이 사용 안한다

 

• SYSDATE 함수

INSERT INTO orders VALUES(sysdate());
INSERT INTO orders VALUES(now());

---

2. NULL 값 처리

• null 허락 X : not null with default value
• null 허락 O : null check logic

 

• NULL 값에 대한 연산과 집계 함수

-- null에 대한 연산 결과는 null
select price + 100 from mybook;

 

• NULL 값을 확인하는 방법 - IS NULL, IS NOT NULL

select * from mybook where price is null;
select * from mybook where price is not null;

 

• IFNULL 함수

select bookid, ifnull(price,0) from mybook;

---

3. 행번호 출력

 

4.2) 부속질의

1. 중첩질의 - WHRER 부속질의

• CASE WHEN THEN

-- 1개의 row에 2개의 colum으로 표현 
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, NOT EXISTES(존재 연산자)

# EXISTS
select * from customer where customer_id in (select customer_id from customer_order); -- 1건
select * from customer where exists (select customer_id from customer_order); -- 2건
select * from customer c where exists (select co.customer_id from customer_order co where c.customer_id = co.customer_id);
-- 왼쪽 subquery의 customer_order가 100건이면 오른쪽 customer 1건에 대해 왼쪽 100과 비교를 하다가 1건이라도 나오면
-- 더 이상 따지지 않고 true처리

# NOT EXISTS
select * from customer c where not exists (select co.customer_id from customer_order co where c.customer_id = co.customer_id);
-- 왼쪽 subquery의 customer_order가 100건이면 오른쪽 customer 1건에 대해 왼쪽 100과 비교를 하다가 1건이라도 나오면
-- 더 이상 따지지 않고 false처리

# NOT IN, NOT EXISTS WITH NULL
-- NOT IN은 WHERE 조건문에 NULL이 아닌지(is not null) 알려줘야한다
-- 1번은 blacklist에 없는데 not in 계산 1 != 2 && 1 != null가 true여야 하는데 null 연산에서 false가 되면서 1번이 안나온다
select * from customer where customer_id not in (select customer_id from blacklist); -- 0건 (1번이 나와야 되는데 0건)
select * from customer where customer_id not in (select customer_id from blacklist where customer_id is not null);

-- NOT EXISTS은 NULL이 있어도 괜찮다
select * from customer c where not exists (select b.customer_id from blacklist b where c.customer_id = b.customer_id);

-- not in : index 이용 X, null에 대한 고려 O
-- not exists : index 이용 O, null에 대한 고려 X

 

4.3) 뷰

1. 뷰의 생성

-- 보고서 query
select o.orderid, o.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;
 
-- View를 이용한 보고서
-- View를 생성하는 시점에 데이터까지 생성X, query만 보관
create view VOrders as
select o.orderid, o.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 custid, name from VOrders; -- 일부

• 편리성 및 재사용성 (요즘 아님)
• 보안성, 독립성
• 은행, 통신 회사 인사팀, 영업팀 (전체 데이터가 필요)...콜센터(제한적인 데이터만 필요)
• 중요 데이터가 포함된 테이블 중 상담에 필요한 일부 컬럼만 콜센터가 사용하도록 함
• 위 경우, 테이블을 콜센터에 직접 노출 X -> 일부 컬럼으로 view 만들어서 제공

 

4.4) 인덱스

1. 데이터베이스의 물리적 저장

• 넘어간다

---

2. 인덱스와 B-tree

인덱스의 특징

1. 빠른 검색과 함께 효율적인 레코드 접근 가능 (목적)
2. 인덱스는 테이블에서 한 개 이상의 속성을 이용하여 (별도의 자료구조)생성 (정렬)
3. 데이터에서 수정, 삭제 등의 변경이 발생하면 인덱스 재구성 해야 됨
4. 검색에서는 이득을 보지만 등록, 수정, 삭제에서는 손해
5. PK, FK등은 자동으로 인덱스 생성 됨
6. 거꾸로 특정 컬럼에 인덱스를 추가해도 검색이 개선되지 않고 오히려 더 느려짐 (예: 성별 'M', 'F')
   • 분포도가 20% 이상이 되면 별로
7. 순서대로 정렬된 속성과 데이터의 위치만 보유하므로 테이블보다 작은 공간 차지
8. 저장된 값들은 테이블의 부분집합이 됨
9. 일반적으로 B-tree 형태의 구조를 가짐

-- test db에 jdbc_big 테이블 생성
select count(*) from jdbc_big;
select * from jdbc_big;
-- 100만건 데이터를 이용해서 더 큰 테이블 생성
create table jdbc_big_2 as select * from jdbc_big;
select count(*) from jdbc_big_2;
-- jdbc_big_2를 이용해서 jdbc_big  더 크게 insert upto 1000,0000
insert into jdbc_big (col_2, col_3, col_4) select col_2, col_3, col_4 from jdbc_big_2;

select * from jdbc_big where col_1 = 84563;

---

FOREIGN KEY (FK)

• customer, orders, book 테이블에 orders의 custid는 customer, bookid는 book의 key
• RDB의 핵심인 데이터의 무결성을 유지하는 핵심 개념
• orders에 customer의 custid를 FK로 지정하는 설정
• 올바른 테스트를 위해 정상 데이터로 초기화

 

2. 실습

2.1) 프로그래머스

 

2.2) 강사님 코드

오전 수업

-- 내장함수
select abs(-78), abs(78)  from dual; 
select @@sql_safe_updates;
select custid, round(sum(saleprice)/count(*), -2)  from orders  group by custid;
-- 한글  utf-8 (3byte), utf-16(4byte), euc-kr(2byte)
-- '축구의 역사' : 한글 5개 + space 1개 (5x3 + 1) = 16
select bookname,  length(bookname), char_length(bookname)   from book where bookid in (1, 10);
-- adddate
select adddate('2025-02-28', interval 5 day );
-- sysdate
select sysdate();
-- null 에 대한 입장
-- null 을 허락 X  <= not null with default value
-- null 을 허락 O  <= null check logic
-- null 연산
-- null 에 대한 연산 결과는 null
select price + 100 from mybook;
select * from mybook;
-- null 여부 is null, is not null
select * from mybook where price is null;
select * from mybook where price is not null;
-- ifnull() 오라클 nvl()
select bookid, price from mybook;
select bookid, ifnull(price, 0) price from mybook;
-- case when then else 
-- hr schema
select salary, department_id from employees where department_id in (60, 90);
-- employee table 에서 departmemt_id 가 60, 90 인 사원의 salary 합
-- 결과가 2개의 row
select department_id, sum(salary)
  from employees
 where department_id in (60, 90)
  group by department_id;
-- 1개의 row 에 2개의 컬럼으로 표현  
-- select 60부서 sum, 90부서 sum  
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
	CREATE TABLE customer (
	  customer_id int NOT NULL,
	  customer_nm varchar(45) NOT NULL,
	  PRIMARY KEY (customer_id)
	);
	CREATE TABLE customer_order (
	  order_id int NOT NULL,
	  customer_id int DEFAULT NULL,
	  product_id int DEFAULT NULL,
	  order_price int DEFAULT NULL,
	  PRIMARY KEY (order_id)
	);
insert into customer values ('1', '홍길동');
insert into customer values ('2', '이길동');
insert into customer_order values ('11', '1', '111', '1000');
select * from customer where customer_id in ( select customer_id from customer_order ); -- 1건
select * from customer where exists ( select customer_id from customer_order ); -- 2건
-- 왼쪽 subquery 의 customer_order 가 100 건이면 오른쪽 customer 1건에 대해 왼쪽 100 과 비교를 하다가 1건이라도 나오면
--  더이상 따지지 않고 true 처리
select * from customer c where exists ( select co.customer_id from customer_order co where c.customer_id = co.customer_id );
-- not exists
select * from customer where customer_id not in ( select customer_id from customer_order ); -- 1건
-- 왼쪽 subquery 의 customer_order 가 100 건이면 오른쪽 customer 1건에 대해 왼쪽 100 과 비교를 하다가 1건이라도 나오면
--  더이상 따지지 않고 false 처리
select * from customer c where not exists ( select co.customer_id from customer_order co where c.customer_id = co.customer_id );
-- null 을 포함한 테이블  blacklist
create table blacklist (
  blacklist_id int not null,
  customer_id int null,
  customer_nm varchar(45) null,
  PRIMARY KEY (blacklist_id)
);
insert into blacklist values (1, 2, '이길동');
insert into blacklist values (2, null, '박길동');
-- not in not exists with null
select * from blacklist;
-- 1번은 blacklist 에 없는데 not in 계산 1 != 2 && 1 != null 가 true 이어야 하는데 null 연산에서 false 가 되면서 1번이 안나온다.
select * from customer where customer_id not in ( select customer_id from blacklist ); -- 0건 ( 1번이 나와야 되는데 0건)
-- null 을 제외한 not in 처리 필요
select * from customer where customer_id not in ( select customer_id from blacklist where customer_id is not null ); -- 0건 ( 1번이 나와야 되는데 0건)
-- 1번은 1 == 2 확인 후 1 == null 확인해서 모두 false 이므로 1 통과, 2번은 2 = 2에서 true 가 되므로 중단하고 false로 미통과
select * from customer c where not exists ( select b.customer_id from blacklist b where c.customer_id = b.customer_id );
-- not in : index 이용 X, null 에 대한 고려
-- not exists :  index 이용 O, null 에 대한 고려 X
-- 뷰
CREATE TABLE  Customer (
  custid      INTEGER PRIMARY KEY,
  name        VARCHAR(40),
  address     VARCHAR(50),
  phone       VARCHAR(20)
);
INSERT INTO Customer VALUES (1, '박지성', '영국 맨체스터', '000-5000-0001');
INSERT INTO Customer VALUES (2, '김연아', '대한민국 서울', '000-6000-0001');
INSERT INTO Customer VALUES (3, '김연경', '대한민국 경기도', '000-7000-0001');
INSERT INTO Customer VALUES (4, '추신수', '미국 클리블랜드', '000-8000-0001');
INSERT INTO Customer VALUES (5, '박세리', '대한민국 대전',  NULL);
-- 보고서 query
select o.orderid, o.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;
--  View  를 이용한 보고서
-- View  를 생성하는 시점에 데이터까지 생성 X, query 만 보관
create view VOrders as
select o.orderid, o.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 custid, name from vorders; -- 일부
-- 은행, 통신 회사 인사팀, 영업팀 (전체 데이터가 필요).....콜센터( 제한적인 데이터만 필요 )
-- 중요 데이터가 포함된 테이블 중 상담에 필요한 일부 컬럼만 콜센터가 사용하도록 한다.
-- 위 경우, 테이블을 콜센터에 직접 노출 X  => 일부 컬럼으로  view  만들어서 제공
-- 인덱스
-- 빠른 검색 목적,
-- 1. 별도의 자료구조를 생성(정렬)
-- 2. 새로운 데이터가 추가되거나, 기존 데이터가 변경 또는 삭제되면 재구성
-- 3. 검색에서는 이득을 보지만, 등록,수정,삭제에서는 손해본다.
-- 4. PK, FK 등은 자동으로 인덱스가 생성된다.
-- 5. 거꾸로 특정 컬럼에 인덱스를 추가해도 검색이 개선되지 않고 오히려 더 느려진다.( 예; 성별 'M', 'F')
--     분포도가 20% 이상이 되면 별로....
-- 아래 query 실행계획 비교
select * from orders where orderid = 3;
select * from orders where saleprice = 3;
-- test db 에 jdbc_big 테이블 생성
select count(*) from jdbc_big;
select * from jdbc_big;
-- 100만건 데이터를 이용해서 더 큰 테이블 생성
create table jdbc_big_2 as select * from jdbc_big;
select count(*) from jdbc_big_2;
-- jdbc_big_2 를 이용해서 jdbc_big 더 크게 insert  upto 1000,0000
insert into jdbc_big ( col_2, col_3, col_4 ) select col_2, col_3, col_4 from jdbc_big_2;
select * from jdbc_big limit 10;
select * from jdbc_big where col_1 = 63;
select * from jdbc_big where col_2 = '홍길동';
select * from jdbc_big where col_3 = '2343';
-- Foreign Key (FK)
-- customer, orders, book 테이블에 orders 의 custid 는 customer, bookid 는 book 의 key
-- RDB 의 핵심인 데이터의 무결성을 유지하는 핵심 개념
-- orders 에 customer 의 custid 를 FK 로 지정하는 설정
-- 올바른 테스트를 위해 정상 데이터로 초기화
INSERT INTO Customer VALUES (1, '박지성', '영국 맨체스터', '000-5000-0001');
INSERT INTO Customer VALUES (2, '김연아', '대한민국 서울', '000-6000-0001');
INSERT INTO Customer VALUES (3, '김연경', '대한민국 경기도', '000-7000-0001');
INSERT INTO Customer VALUES (4, '추신수', '미국 클리블랜드', '000-8000-0001');
INSERT INTO Customer VALUES (5, '박세리', '대한민국 대전',  NULL);
INSERT INTO Orders VALUES (1, 1, 1, 6000, STR_TO_DATE('2024-07-01','%Y-%m-%d'));
INSERT INTO Orders VALUES (2, 1, 3, 21000, STR_TO_DATE('2024-07-03','%Y-%m-%d'));
INSERT INTO Orders VALUES (3, 2, 5, 8000, STR_TO_DATE('2024-07-03','%Y-%m-%d'));
INSERT INTO Orders VALUES (4, 3, 6, 6000, STR_TO_DATE('2024-07-04','%Y-%m-%d'));
INSERT INTO Orders VALUES (5, 4, 7, 20000, STR_TO_DATE('2024-07-05','%Y-%m-%d'));
INSERT INTO Orders VALUES (6, 1, 2, 12000, STR_TO_DATE('2024-07-07','%Y-%m-%d'));
INSERT INTO Orders VALUES (7, 4, 8, 13000, STR_TO_DATE( '2024-07-07','%Y-%m-%d'));
INSERT INTO Orders VALUES (8, 3, 10, 12000, STR_TO_DATE('2024-07-08','%Y-%m-%d'));
INSERT INTO Orders VALUES (9, 2, 10, 7000, STR_TO_DATE('2024-07-09','%Y-%m-%d'));
INSERT INTO Orders VALUES (10, 3, 8, 13000, STR_TO_DATE('2024-07-10','%Y-%m-%d'));

 

3. 마무리

3.1) WorkShop

• SYSDATE() vs NOW()의 차이점 확인하기
  • NOW() : 쿼리가 수행되는 시간에 고정
  • SYSDATE() : 함수가 호출될때마다 시간 변함

 

3.2) 더 공부할 것