KOSA fullstack 교육 (SELECT, ORDER BY, DISTINCT, IFNULL, LIMIT)

1. SELECT 구문의 정렬 

 

오늘부터는 workbench를 이용해서 작업한다. 

 

여러 줄을 실행하고 싶을 때는 Mac 기준 -> command+shift+enter

 

-- 원하는 컬럼만 명시하는 projection
SELECT empno, ename, job
FROM emp
WHERE deptno = 10;

-- emp 테이블에서 업무가 SALESMAN인 사원의 이름과 업무, 급여 검색 
SELECT ename, job, sal
FROM emp
WHERE job = 'SALESMAN';

-- emp 테이블에서 sal이 2500달러 이상을 받는 직원의 직원번호, 이름, 업무, 급여를 검색 
SELECT empno, ename, job, sal
FROM emp
WHERE sal>= 2500;

-- emp테이블에서 sal이 1200크고, 2500미만인 사의의 급여를 받는 직원을 검색
SELECT ename, job, sal
FROM emp
WHERE sal >= 1200 AND sal < 2500;

SELECT ename, job, sal
FROM emp
WHERE sal BETWEEN 1200 AND 2500;

-- 에러는 안나지만, 안나옴. 문법은 맞기 때문
SELECT ename, job, sal
FROM emp
WHERE sal >= 2500 AND sal < 1200;

 

 

-- emp테이블에서 업무가 점원이거나 영업사원인 직원을 검색 
-- 단, 급여가 1000달러 미만의 직원은 제외 

SELECT ename, job, sal
FROM emp
WHERE (job = 'SALESMAN' OR job = 'CLERK')
AND sal > 1000;

-- emp 테이블에서 직원의 번호, 이름, 입사년도, 급여를 검색
-- 단, 20번 부서의 사원은 제외

SELECT empno, ename, hiredate, sal, deptno
FROM emp
WHERE deptno <> 20;

-- emp 테이블에서 급여순으로 정렬
-- order by sal 

SELECT empno, ename, sal
FROM emp
ORDER BY sal DESC;

-- emp 테이블에서 직원의 급여순으로 정렬, 10번 부서는 제외 
SELECT empno, ename, sal
FROM emp
WHERE deptno != 10
ORDER BY sal DESC;

-- emp 테이블에서 COMM 값으로 정렬, 내림차순으로 정렬 
/*
MySQL에서는 null값이 가장 작은 값
반면에 Oracle에서는 null값이 가장 큰 값
그렇다고해서 null 값이 비교할 수 있는 일종의 숫자는 아니다.
*/
SELECT empno, ename, comm, sal
FROM emp
WHERE comm is not null
ORDER BY comm DESC;

 

null의 잘못된 의미

• 아무것도 없는 값
• 0에 해당하는 값 아니다!!!!!

null이란,

• 자격이 없거나
• 미확정일때 null을 기입한다. 
• 연산할 수 없고, 비교의 대상도 되지 않는다. 

 

-- 직원중에서 입사일이 가장 빠른 사원순으로 검색 
SELECT empno, job, sal, hiredate
FROM emp
ORDER BY hiredate DESC;

-- 직원의 이름, 급여, 급여+300을 출력
/*
컬럼에 연산이 적용되는 경우 Alias가 필요하다.
보통은 AS 생략하고 사용한다. 
*/
SELECT ename, sal, sal+300 AS 급여인상분
FROM emp;
/*
3번째 열을 기준으로 정렬한다.
*/
SELECT ename, sal, sal+300 급여인상분
FROM emp
ORDER BY 3 DESC;

/*
컬럼에 연산이 적용되는 경우 Alias가 필요
Alias에 공백이 있을 때 " " 묶어준다.
이때 정렬에서 Alias 사용 안된다.
*/
SELECT ename, sal, sal+300 "인상된 급여"
FROM emp
ORDER BY "인상된 급여" DESC;

/*
Distinct 중복을 제거할 때 사용하는 키워드
1) SELECT 절 뒤에 바로 나온다. 
2) 정렬이 안된다. 별도로 정렬을 해야한다.
3) 중복값을 제거하는 연산은 서버내부에서 연산하는데 많은 시간이 걸린다.
데이터가 아주 많을때 어마어마한 성능 저하를 하는 것이 DISTINCT이다. 
만약 불가피하게 사용해야 하는 경우라면 DISTINCT를 대체하는 방법을 생각하거나
대상이 되는 테이블의 크기를 최소화시켜놓고 사용하는 방법을 고민해야 한다. 
*/
-- emp 테이블에서 부서번호를 검색 
-- 오름차순 정렬 

SELECT DISTINCT deptno
FROM emp
ORDER BY deptno;

 

 

-- emp 테이블에서 사원의 이름, 업무, 급여, 연봉을 검색
-- 연봉은 Alias AnnualSalary라고 칭하고, 연봉순으로 정렬
-- null값은 연산이 안된다. 비교도 안된다. 
-- null --> 0 으로 치환하고 연산을 진행 
-- (ifnull) | nvl()
-- MySQL 에서는 null 값은 가장 작은 값으로 취급된다.
SELECT ename, job, sal, sal*12 AnnualSalary
FROM emp
ORDER BY AnnualSalary;

-- ifnull 사용 버전
-- ifnull(comm,0)
-- comm이 null이 아니면 comm값 자체가 반환되고, null이라면 0이 반환 
SELECT ename, job, sal, sal*12+ifnull(comm,0) AnnualSalary
FROM emp
ORDER BY AnnualSalary;

-- emp 테이블에서 상사번호를 검색 
SELECT DISTINCT empno 
FROM emp
ORDER BY 1;

-- emp 테이블에서 사원 이름, 업무, 급여, 연봉 검색
-- 부서번호 10번 부서와 20번 부서의 사원만을 대상
-- 연봉이 높은 사람부터 검색되도록 정렬
-- 연봉의 alias를 '연봉'으로 지정
SELECT ename, job, sal, sal*12+ifnull(comm,0) 연봉
FROM emp
WHERE deptno = 10 OR deptno = 20
ORDER BY 4 DESC;

/*
LIMIT
출력하는 행의 갯수를 제한
limit 0,5 : 시작은 0부터 5개를 제한함
limit 5 : 5개만 출력함
limit는 무조건 상위로 자른다!!
order by 절보다 더 나중에 돌아간다. 즉, 실행이 끝난 후 작동
*/

-- emp 테이블에서 급여를 가장 많이 받는 사람 TOP3(3명만) 검색
SELECT ename, sal
FROM emp
ORDER BY sal DESC
LIMIT 3;

 

LIMIT 절을 사용할 때 한가지 주의할 점이 있다.

LIMIT절의 offset이 클 경우 성능에 큰 문제를 일으킬 수 있다.

아래의 경우를 보자. 

SELECT * FROM stable ORDER BY sal LIMIT 2000000,10;

--> 10 rows (2.57 sec)

성능에 치명적이다...

 

최대한 offset 0으로 쓰자 !

해결책)

SELECT * FROM stable
WHERE SAL < 3450
ORDER BY SAL DESC
LIMIT 10;