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
인덱스의 특징
- 빠른 검색과 함께 효율적인 레코드 접근 가능 (목적)
- 인덱스는 테이블에서 한 개 이상의 속성을 이용하여 (별도의 자료구조)생성 (정렬)
- 데이터에서 수정, 삭제 등의 변경이 발생하면 인덱스 재구성 해야 됨
- 검색에서는 이득을 보지만 등록, 수정, 삭제에서는 손해
- PK, FK등은 자동으로 인덱스 생성 됨
- 거꾸로 특정 컬럼에 인덱스를 추가해도 검색이 개선되지 않고 오히려 더 느려짐 (예: 성별 'M', 'F')
- 분포도가 20% 이상이 되면 별로
- 순서대로 정렬된 속성과 데이터의 위치만 보유하므로 테이블보다 작은 공간 차지
- 저장된 값들은 테이블의 부분집합이 됨
- 일반적으로 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) 더 공부할 것
'LG 유플러스 유레카 > 데이터베이스 활용' 카테고리의 다른 글
[26일 차] 데이터베이스 활용 (Ch06, Ch07) (0) | 2025.03.04 |
---|---|
[24일 차] 데이터베이스 활용 (Ch03, Ch04) (0) | 2025.02.27 |
[23일 차] 데이터베이스 활용 (Ch03) (0) | 2025.02.26 |