241218 [멋쟁이사자처럼 부트캠프 TIL 회고] Back-End school

SQL 쿼리

인덱스

• B-Tree(Balanced Tree) 또는 Hash(해시 테이블) 방식
• 기본 키는 기본적으로 인덱스 생성
• 값의 분포가 넓은 컬럼에 적합
• 인덱스가 없다면 테이블의 모든 행을 살펴봐야함
  • 인덱스가 있으면 책의 목차처럼 원하는 데이터가 있는 위치를 빠르게 찾을 수 있음
  • 해시 인덱스: 해시 인덱스는 정확한 값을 찾을 때 빠르다. 예를 들어, "이름"이 "홍길동"인 사람을 찾을 때, 해시 인덱스는 이름을 해시 값으로 변환하여 정확한 위치를 바로 찾음.
  • B-Tree 인덱스 (주로 사용되는 인덱스): 데이터를 정렬된 트리 형태로 저장. 예를 들어, 숫자나 날짜를 정렬해놓고, 그 정렬된 인덱스를 사용해서 데이터를 빠르게 찾을 수 있다.
    • B-Tree에서 데이터를 찾을 때는 이진 검색(Binary Search) 방식처럼 작동. 즉, 찾고자 하는 데이터가 어디에 있을지 반으로 나누어가며 빠르게 찾을 수 있다.

-- index 조회 
SHOW INDEX FROM 테이블명 

-- index 생성
CREATE index 인덱스명 on 테이블명(컬럼명);

-- index 삭제
DROP index 인덱스명 on 테이블명 

---

형 변환

MySQL 타입
BINARYCHAR
DATE
DATETIME
SIGNED {INTEGER}
TIME
UNSIGNED {INTEGER}

-- 형변환 cast(값 as 데이터타입) 
-- 형변환시 주의
select cast(1-2 as unsigned); -- unsigned 부모없는 정수 표현 - 값은 표현 할 수 없으므로 오버플로우 발생. 
select 1-2;
select cast(-1 as unsigned), cast(-1 as signed);
select cast('1234' as signed) + 50;
select '1234' + 50;   --  원래는 안돼야 하는데 mysql이 그냥 변환해서 해줌.  

-- convert(값, 타입) 
select convert(123,char);

 

지금까지 본 함수들은 단일 행 함수.

그룹 함수

여러 행의 결과가 하나

COUNT(expr) : non-NULL인 row의 숫자를 반환
COUNT(DISTINCT expr,[expr...]) : non-NULL인 중복되지 않은 row의 숫자를 반환
COUNT(*) : row의 숫자를 반환
AVG(expr) : expr의 평균 값을 반환
MIN(expr) : expr의 최소 값을 반환
MAX(expr) : expr의 최대 값을 반환
SUM(expr) : expr의 합계를 반환
GROUP_CONCAT(expr) : 그룹에서 concatenated한 문자를 반환
VARIANCE(expr) : 분산
STDDEV(expr) : expr의 표준 편차를 반환

select avg(sal), sum(sal) from emp;

GROUP BY

select sum(sal) from emp group by deptno;

-- 그룹 함수를 사용 할 때 참여할 수 있는 칼럼: 그룹핑에 참여한 컬럼만 SELECT 절에 들어올 수 있음
select ename,sum(sal) from emp group by deptno; -- 오류를 일으킴
select job,deptno,sum(sal) from emp group by job,deptno order by 1;

-- jop별 사원수와 ,평균 급여
select job,count(*),avg(sal) from emp group by job;

HAVING

• group에 대한 조건

-- 20번 부서의 job별 평균 급여를 알고 싶어요 -- 조건(전체 데이터에 대한 조건은 where)
select avg(sal) from emp where dept=20 group by job;

-- 부서별 총 급여액이 9000이 넘는 부서만 보고싶어요 --조건(대상-> 그룹핑한 데이터는 having)
select avg(sal) from emp group by job having sum(sal)>9000;

-- 부서별 평균 금액을 알고싶어요, 단 부서별 평균 급여가 2500보다 작은 부서만 보고싶고, 급여가 4500 이상이거나 1000미만인 사원은 제외하고 평균 금액 
select deptno, avg(sal) a from emp where sal<4500 and sal>=1000 group by deptno having a <2500;

---

JOIN

• 하나 이상의 테이블에서 데이터를 검색

Cross Join(Cartesian Join)

• 모든 가능한 쌍
• 조건이 생략되어 한 테이블의 모든 행과 다른 테이블의 모든 행이 JOIN됨
• (JOIN하는 테이블의 수 - 1) 개의 JOIN 조건이 필요

Inner Join

• 조건을 만족한 튜플만 나타남

Outer Join

• 짝이 없는 튜플도 NULL과 함께 나타남
  • Left Outer Join: 왼쪽의 모든 튜플은 결과 테이블에 나타남
  • Right Outer Join: 오른쪽의 모든 튜플은 결과 테이블에 나타남

select * from employees e LEFT [OUTER] JOIN departments d ON (e.department_id = d.department_id);

Theta Join

• 기본키와 외래키의 관계가 없어도 다른 조건에 의해서 조인을 할 수 있음

select e.ename, e.sal,s.grade from emp e,salgrade s 
where e.sal between s.min_salary and s.max_salary;

Equi-Join

• 컬럼에 있는 값이 정확하게 일치하는 경우에 = 연산자를 사용하여 JOIN

SELECT 테이블명.컬럼명, 테이블명.컬럼명. …
FROM 테이블1, 테이블2
WHERE 테이블1.컬럼1 = 테이블2.컬럼2

Natural Join

• 이름이 같은 컬럼이 두 테이블에 존재하면 그 이름을 기준으로 조인 조건을 만든다

select e.ename, d.dname from emp e natural join dept d;

• Natural join의 문제점: 조인하고자 하는 두 테이블에 같은 이름이 칼럼이 많을 때 특정한 칼럼으로만 조인하고 싶다면 USING절을 사용해서 기술

select * from employees join departments using(department_id);

• 공통된 이름의 칼럼이 없는 경우
  • WHERE 절에 일반조건 만 쓸 수 있게함, 조인 조건은 ON에 두어 보다 의미를 명확 히 하고 알아보기도 쉽다
  • ON 부분을 where절에서 작성가능

select * from employees e join departments d on(e.department_id = d.department_id);

Self Join

• 자기 자신과 JOIN

-- 사원의 사번과, 이름, 매니저 사번, 매니저 이름을 알고 싶다. 
select e.empno 사번, e.ename 사원이름, 
	m.empno 매니저사번, m.ename 매니저이름
from emp e, emp m
where e.mgr = m.empno;

select e.empno 사번, e.ename 사원이름, 
	m.empno 매니저사번, m.ename 매니저이름
from emp e join emp m on (e.mgr = m.empno);

select e.empno 사번, e.ename 사원이름, 
	m.empno 매니저사번, m.ename 매니저이름
from emp e left join emp m on (e.mgr = m.empno);

---

SubQuery

• 하나의 SQL 질의문 속에 다른 SQL 질의문이 포함
• 주의점: 서브쿼리가 먼저 독자적으로 잘 실행 되야 함

SELECT ename
FROM emp
WHERE sal >( SELECT sal
FROM emp
WHERE ename = 'SCOTT' );

 

Single-Row Subquery

• Subquery의 결과가 한 ROW
• Single-Row Operator 사용해야 함: = , > , >=, < , <=, <>

//emp테이블에서 이름으로 정렬했을 때 첫번째 나오는 이름의 이름, 급여, 부서번호를 출력하시오
SELECT ename, sal, deptno
FROM emp
WHERE ename = (SELECT MIN(ename) FROM emp);

//사원의 평균 급여보다 작은 급여를 받는 사원의 이름과 급여를 출력
SELECT ename, sal
FROM emp
WHERE sal < (SELECT AVG(sal)FROM emp);

//부서이름이 SALES인 부서의 사원 이름과 부서 번호를 출력하시오.
SELECT ename, deptno
FROM emp
WHERE deptno = (SELECT deptno
FROM dept
WHERE dname = 'SALES');

 

Multi-Row Query

• Subquery의 결과가 둘 이상의 Row
• Multi-Row에 대한 연산을 사용해야 함: ANY, ALL, IN, EXIST...

IN

//부서별 이름 순서가 첫번째 사원 이름, 급여, 부서 번호를 출력하시오
SELECT ename, sal, deptno FROM emp
WHERE ename IN (SELECT MIN(ename) //IN은 or여러개와 같음 
FROM emp GROUP BY deptno);

 

Any (or)

• 다수의 비교값 중 한개라도 만족하면 true
• IN 과 다른점은 비교 연산자를 사용한다는 점

SELECT ename, sal, deptno
FROM emp
WHERE ename = ANY (SELECT MIN(ename)
FROM emp GROUP BY deptno);

//950보다 큰 값은 모두 출력
SELECT * FROM emp WHERE sal >ANY(950, 3000, 1250)

 

All (and)

• 전체 값을 비교하여 모두 만족해야만 true

//결과가 없음
SELECT * FROM emp WHERE sal =ALL(950, 3000, 1250)

SELECT * FROM emp
WHERE sal <ALL( select e.sal from emp e where e.deptno in (30, 10));

---

Correlated Query

• Outer Query와 Inner Query가 서로 연관되어 있음
  1. Outer query의 한 Row를 얻는다
  2. 해당Row를 가지고 Inner Query를 계산
  3. 계산 결과를 이용 Outer query의 WHERE절을 evaluate
  4. 결과가 참이면 해당 Row를 결과에 포함

-- 사원의 이름, 급여, 부서 번호를 출력하시오. 단 사원의 급여가 그 사원이 속한 부서의 평균 급여보다 큰 경우만 출력하시오.
SELECT o.ename, o.sal, o.deptno
FROM emp o
WHERE o.sal > (SELECT AVG(i.sal)
FROM emp i
WHERE i.deptno = o.deptno);

-- 각 부서별로 최고 급여를 받는 사원을 출력하시오.
SELECT deptno, empno, ename, sal
FROM emp
WHERE (deptno,sal) IN (SELECT deptno, max(sal)
FROM emp GROUP BY deptno);

SELECT e.deptno, e.empno, e.ename, e.sal
FROM emp e,
(SELECT s.deptno, max(s.sal) msal
FROM emp s GROUP BY deptno) m
WHERE e.deptno = m.deptno AND e.sal = m.msal;

-- 틀린 코드
SELECT deptno, empno, ename, sal
FROM emp e
WHERE e.sal = (SELECT max(sal)
FROM emp WHERE deptno = e.deptno);

---

-- LPAD 
SELECT LPAD(컬럼명,자리수,부족한 자리수를 표시할 문자) FROM 테이블명
SELECT employee_id, LPAD(cast(salary as char), 10, '*') FROM employees;
//salary는 숫자타입 문자인 *와 타입이 맞지 않으므로 salary를 문자로 형변환

-- TRIM, LTRIM, RTRIM 공백 제거 ( 앞뒤로만 제거 가능, 중간에 있는 공백은 제거 못함)
SELECT LTRIM(' hello '), RTRIM(' hello ');
SELECT TRIM(' hi '),TRIM(BOTH 'x' FROM 'xxxhixxx');

-- - ABS(x) : x의 절대값을 구한다.
SELECT ABS(2), ABS(-2);

-- MOD(n,m) % : n을 m으로 나눈 나머지 값을 출력
SELECT MOD(234,10), 253 % 7, MOD(29,9);

--  CEILING(x) : x보다 작지 않은 가장 작은 정수 출력 
SELECT CEILING(1.23), CEILING(-1.23);

--  ROUND(x) : x에 가장 근접한 정수를 반환
SELECT ROUND(-1.23), ROUND(-1.58), ROUND(1.58);

-- ROUND(x,d) : x값 중에서 소수점 d자리에 가장 근접한 수로 반환
SELECT ROUND(1.298,1),ROUND(1.298,0);

-- POW(x,y) POWER(x,y) : x의 y 제곱 승을 반환
SELECT POW(2,2),POWER(2,-2);

-- SIGN(x) : x=음수이면 -1을, x=0이면 0을, x=양수이면1을 출력
SELECT SIGN(-32), SIGN(0), SIGN(234);

-- GREATEST(x,y,...) : 가장 큰 값을 반환
SELECT GREATEST(2,0),GREATEST(4.0,3.0,5.0),GREATEST("B","A","C")

-- LEAST(x,y,...) : 가장 작은 값을 반환
SELECT LEAST(2,0),LEAST(34.0,3.0,5.0),LEAST("b","A","C");

-- CURDATE(),CURRENT_DATE : 오늘 날짜를 YYYYMM-DD나YYYYMMDD 형식으로 반환
SELECT CURDATE(),CURRENT_DATE;

-- CURTIME() CURRENT_TIME : 현재 시각을 HH:MM:SS나 HHMMSS 형식으로 반환
SELECT CURTIME(),CURRENT_TIME;

-- NOW() SYSDATE() CURRENT_TIMESTAMP : 오늘 현시각을 YYYY-MM-DD HH:MM:SS나 YYYYMMDDHHMMSS 형식으로 반환
SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP;

-- 날짜형 함수 - DATE_FORMAT(date,format) : 입력된 date를 format형식으로 반환
SELECT DATE_FORMAT(CURDATE(),'%W %M %Y');

-- PERIOD_DIFF(p1,p2) : YYMM이나 YYYYMM으로 표기되는 p1과 p2의 차이 개월을 반환
//오늘까지 근무한 근무개월 수와 직원 이름을 출력하시오.
SELECT concat(first_name, ' ', last_name) AS name,
PERIOD_DIFF( DATE_FORMAT(CURDATE(), '%Y%m'),
DATE_FORMAT(hire_date, '%Y%m') )
FROM employees

DATE_ADD(date,INTERVAL expr type)
DATE_SUB(date,INTERVAL expr type)
ADDDATE(date,INTERVAL expr type)
SUBDATE(date,INTERVAL expr type) : 날짜 date에 type 형식으로 지정한 expr값을
더하거나 뺀다. DATE_ADD()와 ADDDATE()는 같은 동작이고, DATE_SUB()와
SUBDATE()는 같은 의미