1. Select
- Selection: 테이블에서 전체 칼럼을 출력시켜주면 Selection이다.
- Projection: 테이블에서 일부 칼럼을 출력시켜주면 Projection이다.
- Join: 서로 다른 테이블을 연결해 한번에 데이터를 함께 검색할 수 있음.
1) 테이블 조회하기
[1] 이용할 데이터베이스를 선택한다.
use scottdb;[2] 현존하는 테이블에 대한 정보를 보여준다.
show tables;
[3] 해당 테이블에 대한 정보를 보여준다.
desc emp;
- char: 고정형 문자열이다. 공간이 낭비될 수 있지만 검색 속도가 varchar에 비해 빠르다.
ex) char(10): 10바이트 공간을 차지한다. 문자가 몇개가 들어오든 10바이트를 유지한다.
- varchar(variable character): 가변형 문자열로 메모리의 길이에 따라 가변적으로 길이를 정한다. 메모리 사용량을 줄일 수 있다.
ex) varchar(10): 10글자 쓸수 있는데 7만큼 쓰면 7칸만 메모리를 사용한다.
- datetime: 날짜 시간을 나타내주는 타입이다.
- Primary Key: 각 행의 데이터를 구별할 수 있도록 해준다. NOT NULL과 UNIQUE 제약 조건의 특징을 가진다.
- int: 숫자데이터로 정수형 데이터이다.
- double: 숫자데이터로 소수점 아래 15자리까지 표현이 가능하다.
2) emp 테이블 전체 출력하기(Selection)
[1]
select * from emp;
- 칼럼 전체가 출력되었다.(Selection)
3) 칼럼 일부 출력하기(Projection)
[1]
select empno from emp;
- emp 테이블에서 empno 칼럼에 있는 값을 전체출력한다.
[2]
select empno, job from emp;
- emp 테이블에서 empno칼럼, job칼럼에 있는 값 전체를 출력한다.
[3]
select empno 이름, job 담당업무 from emp;
-- 혹은
select empno as 이름, job as 담당업무 from emp;
- emp 테이블에서 empno칼럼, job칼럼에 있는 값 전체를 출력하면서 해당 칼럼의 별칭(alias)을 정해준다.
4) 개수 세기
[1]
select count(*) from emp;
- 전체 행 개수를 가지고 온다.
[2]
select count(comm) from emp;
- 해당 컬럼의 데이터 개수를 가지고 온다.
5) limit
[1]
select * from emp limit 5;
- select * from 테이블 이름 으로 전체를 출력할때 만약 데이터 개수가 1000만건이나 된다면 delay가 걸릴 수 있다.
- 데이터가 많을 경우 limit을 걸어서 데이터를 제한할 필요가 있다.
6) distinct
[1] distinct를 안썼을때
select job from emp;
[2] distinct를 썼을때
select distinct job from emp;
- distinct를 쓰면 중복된 값을 제거할 수 있다.
select count(distinct job) from emp;--> 결과
| 5 |
distince를 선언하고 count를 써서 업무 갯수를 출력할 수 있다.
7) 칼럼 연산
[1]
select ename, sal*12 from emp;
- 해당 칼럼에 있는 값들에 12를 곱해서 출력한다.
[2]
select sal, comm, (sal+comm)*12 from emp;
- sal 칼럼과 comm 칼럼에 있는 값을 더해준후 12를 곱해 출력한다.
- 단, null이 들어갔을 경우 어떤 연산을 하든 무조건 null로 출력한다.
[3]
select sal, comm, (sal+ifnull(comm, 0))*12 from emp;
- ifnull을 선언해 comm 칼럼에 있는 값들 중 null일시 0으로 처리하게 한다.
8) 조건 입력(WHERE)
WHERE: 조회하는 결과에 특정한 조건을 줘서 원하는 데이터만 보고 싶을 때 사용한다.
SELECT 필드이름 FROM 테이블 이름 WHERE 조건식;[1]
select ename, job, sal
from emp
where sal >= 1200;
- where를 선언해 한달 원급이 1200달러 이상인 ename, job, sal 컬럼의 값들을 출력시킨다.
[2]
select ename, job, hiredate
from emp
where job = 'CLERK';
- job 칼럼에 CLERK 값이 있는 행을 출력한다.
- 여담으로 SQL Server에서는 CLERK를 clerk로 입력해도 가능하다. 단, ORACLE에서는 대소문자를 철저히 구별한다.
[3]
select ename, job, hiredate
from emp
where job != 'CLERK';
- [2]하고 반대의 코드이다. 이번에는 CLERK 값이 없는 행을 출력시킨다.
[4]
select ename, job, hiredate
from emp
where hiredate = '1980-12-17';
- hiredate 칼럼에 1980-12-17 때의 열을 출력한다.
[5] like
select *
from emp
where ename like 'S%';
- ename칼럼에서 S로 시작하는 모든 것들을 출력한다.
- like는 특정문자가 포함되어 있는지 검색하는 역할을 한다.
[6]
select *
from emp
where comm is null;
- comm 칼럼에 NULL있는 데이터를 전부 출력한다.
- ==null이 아닌 is null로 선언해야 한다.
[7]
select *
from emp
where comm is not null;
- [6]번 문항의 반드이다. 이번에는 NULL이 없는 데이터들을 출력한다.
[8]
select ENAME, JOB, SAL
from emp
where not(SAL>=2000);
- SAL 칼럼에서 2000이상의 값을 부정한다.
- MySQL에서 NOT은 조건을 부정한다.
[9]
select ENAME, JOB, SAL
from EMP
where ename like '_A%';
- _다음에 A가 있다. 즉, 2번째 문자열이 무조건 A인 단어를 출력한다. %는 뒤에 무엇이 오든 상관없다.
[10]
select ENAME, JOB, SAL
from EMP
where ename like '%H';
- 가장 뒤에 H가 오는 문자를 조회한다. 앞에는 상관없다.
9) 2가지 조건 동시 입력(WHERE)
[1]
select ename, job
from emp
where job = 'CLERK' or job = 'SALESMAN';
- job 칼럼에 CLERK 혹은 SALESMAN 값이 있는 행을 출력한다.
[2] IN
select ename, job
from emp
where job in('CLERK', 'SALESMAN');
- IN: 여러 값을 OR 관계로 묶어 나열하는 조건을 WHERE 절에 사용할 때 쓴다.
[3]
select ename, job, hiredate
from emp
where hiredate >= '1980-12-01' and hiredate < '1981-06-01';
- hiredate 칼럼에서 1980-12-01부터 1981-06-01 전까지의 데이터를 출력한다.
[4]
select ename, job, hiredate
from emp
where hiredate between '1980-12-01' and '1981-06-01';
- [3]번 코드를 between을 통해서 작성한다. 양식은 between A and B이다. A와 B에 값을 넣어 범위를 정해줄 수 있다.
[5]
select ENAME, JOB, SAL
from emp
where SAL>=2000 and HIREDATE < '1981-06-01';
- sal이 2000이상이고 입사일이 1981년 6월 이전에 입사한 직원의 이름과 업무, 급여를 출력한다.
10) ORDER BY
ORDER BY: 결과가 출력되는 순서를 조절하는 구문이다.
[1]
select empno, ename, job
from emp
order by ename;
- ename 칼럼 기준으로 정렬한다. 알파벳 순서로 정려되어 있음을 알수 있다.
[2]
select empno, ename, job
from emp
order by ename desc;
- [1]번에 있는 코드를 내림차순으로 정렬한 것이다. desc를 입력시 내림차순으로 정렬된다.
[3]
select empno, ename, job
from emp
order by 2 desc;
- 2번째 칼럼 ename을 내림차순으로 정렬하도록 한다.
- 숫자를 써서 몇번째 칼럼인지 지정할 수 있다.
- 하지만 가독성이 낮아지고 칼럼이 추가될시 값을 바꾸어야해서 좋은 방법이 아니다.
11) CASE
[1]
SELECT ename, job, sal,
CASE job WHEN 'CLERK' THEN 1.10*sal
WHEN 'MANAGER' THEN 1.15*sal
WHEN 'PRESIDENT' THEN 1.20*sal
ELSE sal
END REVISED_SALARY
FROM emp;
- CASE를 통해 조건에 따른 결과를 출력할 수 있다.
- job 칼럼에서 CLERK일 경우 해당 열의 sal 값에 1.10을 곱하고, MANAGER는 11.5 그리고 PRESIDENT는 1.20을 공한다.
- 만약 해당 조건의 의외의 항목일 경우 해당 sal 값을 그대로 출력한다.
- END 옆에는 해당 칼럼의 별칭을 선언할 수 있다.
12) 문자열 함수
[1] trim
select trim(' Seoul, South Korea '); -- 공백제거
select trim(leading '0' from '0000002850');
-- ltrim()은 공백만 제거할 수 있으나 leading은 값을 특정할 수 있다.
select trim(trailing ',' from 'col1,col2,col3,');
-- rtrim()은 공백만 제거할 수 있으나 trailing은 값을 특정할 수 있다.--> 결과
| Seoul, South Korea |
| 2850 |
| col1,col2,col3 |
[2] substr
select substr('0123456',2); -- 2번째 숫자부터 시작한다.
select substr('0123456',2,4); -- 2번째 숫자부터 4개를 자른다.
select substr('0123456',-3); -- 뒤에서 3번째 글자부터 끝까지 자르기
select substr('0123456',-3,1); -- 뒤에서 3번째 글자부터 1글자 자르기
select substr('0123456' from -4 for 2); -- 뒤에서 4번째 글자부터 2글자 자르기
select substr('0123456', -4, 2); -- 뒤에서 4번째 글자부터 2글자 자르기--> 결과
| 123456 |
| 1234 |
| 456 |
| 4 |
| 34 |
| 34 |
[3] length
select length('choi');
select length('최'); -- UTF-8 한글의 경우 한글자당 3바이트를 사용해 3으로 계산됨
select char_length('choi');
select char_length('최'); -- Character(문자)별 길이 계산시 이용--> 결과
| 4 |
| 3 |
| 4 |
| 1 |
[4] lpad, rpad
select lpad('2500',9,'0'); -- 왼쪽으로 특정문자를 원하는 자리수만큼 넣는다.
select rpad('123-12-1',13,'*'); -- 오른쪽으로 특정문자를 원하는 자리수만큼 넣는다.--> 결과
| 000002500 |
| 123-12-1***** |
[5] concat
select concat('my','sql',8); -- 주어진 문자열을 연결한다.--> 결과
| mysql8 |
[6] lower, upper
select lower('Hello, World!'); -- 주어진 영문자를 소문자로 변환
select upper('Hello, World!'); -- 주어진 영문자를 대문자로 변환--> 결과
| hello, world! |
| HELLO, WORLD! |
[7] like
select 'MySql8' like 'sql'; -- like구문은 부분적으로 일치하는 칼럼을 조회시 사용된다.
select 'MySql8' like '%sql%';
with height_list as(
select 175 as height union all
select 183 as height union all
select 190 as height
)
select height like '18%' from height_list;--> 결과
| 0 |
| 1 |
| 0 |
| 1 |
| 0 |
[8] replace
select replace('apple apple apple','apple','banana'); -- 주어진 문자열에서 특정값을 찾아 변환--> 결과
| banana banana banana |
13) 조인
- 조인시킬 두 테이블
select * from emp;
select * from dept;
[1] CROSS JOIN
- 한쪽 테이블의 모든 행들과 다른 쪽 테이블의 모든 행을 조인시킨다.
select *
from emp cross join dept;
[2] INNER JOIN
- 가장 일반적인 조인이다.
- inner 생략이 가능하다.
- 두 테이블을 조인할 때, 두 테이블에 모두 지정한 열의 데이터가 있어야 한다.
select *
from emp, dept
where emp.deptno = dept.deptno;
-- 혹은
select *
from emp inner join dept
where emp.deptno = dept.deptno;--> 결과
[2-1] INNER JOIN
select E.empno, E.ename, E.deptno, D.dname
from emp E inner join dept D
on E.deptno = D.deptno;- where와 on 둘다 조건을 지정해 준다.
- on은 최초의 조인 조건을 지정할 때 쓰고, where는 추가 필터링이 필요시 추가한다.
--> 결과
ex 1)
select E.empno, E.ename, E.deptno, D.dname
from emp E inner join dept D
on E.deptno = D.deptno
where E.deptno = 20
and E.sal > 2000;- on에서는 emp 테이블과 dept 테이블의 deptno 칼럼이 같을때의 조건이다.
- where에서는 부서번호(deptno)가 20이고 급여(sal)가 이상일 때의 조건을 가지고 있다.
--> 결과
ex 2)
select * from salgrade;- 급여 등급에 관한 테이블이다.
--> 결과
- losal은 최소등급, hisal은 최대 등급, grade는 등급이다.
select E.ename, E.sal, S.grade
from emp E join salgrade S -- inner 생략가능
on E.sal between S.losal and S.HISAL;- 이름, 급여, 등급을 출력한다.
--> 결과
[3] RIGHT OUTER JOIN
- OUTER JOIN은 조인 조건에 만족되지 않는 행까지 포함시켜야 한다.
- RIGHT OUTER JOIN은 오른쪽 테이블의 것이 전부 출력되어야 한다.
- LEFT일 경우 왼쪽 테이블에 맞우어야 하고 FULL은 LEFT와 OUTER가 합쳐진 것이다.
select E.empno, E.ename, D.deptno, D.dname
from emp E right outer join dept D -- outer 생략가능
on E.deptno = D.deptno;--> 결과
[4] SELF JOIN
- 자기 자신과 자시 자신이 조인하는 것이다.
-- 사번, 이름, 매니저 사번, 매니저 이름
select E.EMPNO, E.ENAME, M.EMPNO, M.ENAME
from emp E join emp M
on E.mgr = M.empno;- 별칭이 E인 emp테이블의 사번과 이름, 별칭이 M인 emp테이블의 사번과 이름을 조인한다.
- 조건은 E의 매니저 번호(mgr), M의 직원번호가 같을 때이다.
--> 결과
[5] UNION
- 두 쿼리의 결과를 행으로 합치는 것이다.
- UNION은 중복된 열은 제거되고 데이터 정렬되어 나오지만, UNION ALL을 사용시 중복된 열까지 모두 출력된다.
select EMPNO, ENAME, JOB
from EMP
where JOB='CLERK'
UNION
select EMPNO, ENAME, JOB
from EMP
where JOB='SALESMAN';--> 결과
14) CASE 예제 1
-- 이름, 업무, 급여, 등급(5 기술사, 4 특급기사, 3 일반기사, 2 기능사, 일반)
SELECT E.ename, E.job, E.sal,
CASE s.grade WHEN 5 THEN '기술사'
WHEN 4 THEN '특급기사'
WHEN 3 THEN '일반기사'
WHEN 2 THEN '기능사'
ELSE '일반'
END '등급'
FROM emp E join salgrade S
on E.sal between S.losal and S.HISAL;- case조건을 선언해 등급에 따라 등급명칭을 부여한다.
--> 결과
15) 연산
select avg(sal) from emp; -- 평균
select count(*) from emp; -- 갯수
select sum(sal) from emp; -- 함계
select max(sal) from emp; -- 최대
select min(sal) from emp; -- 최소
select stddev(sal) from emp; -- 표준편차 계산(NULL 값 제외)
select variance(sal) from emp; -- 분산--> 결과
- 평균
| 2073.214285714286 |
- 갯수
| 14 |
- 합계
| 29025 |
- 최대
| 5000 |
- 최소
| 800 |
- 표준편차
| 1139.4886182952814 |
- 분산
| 1298434.3112244897 |
16) group by
- group by: 특정 칼럼을 기준으로 집계 함수를 사용한다.
[1]
select deptno, avg(sal)
from emp
group by deptno
order by avg(sal) desc;- deptno 칼럼을 기준으로 사용한다.
- GROUP BY 절의 칼럼은 SELECT 절에 존재해야 사용할 수 있다.
- SELECT 절에서 집계 함수를 제외한 칼럼을 GROUP BY 절에 기술한다고 생각하면 된다.
--> 결과
[2]
select E.deptno, D.dname ,avg(E.sal)
from emp E join dept D
on E.deptno = D.deptno
group by E.deptno, D.dname
order by avg(E.sal) desc;--> 결과
[3] having
- 평균급여가 2000달러 이상인 곳을 출력한다.,
select E.deptno, D.dname ,avg(E.sal)
from emp E join dept D
on E.deptno = D.deptno
-- where avg(E.sal)>2000
group by E.deptno, D.dname
-- group by 절이후에 where 가능, group by한 결과를 조건으로 범위설정시 where 못씀
having avg(sal)>2000
order by avg(E.sal) desc;- WHERE 절에서는 집계함수를 사용 할 수 없다.
- HAVING 절은 집계함수를 가지고 조건비교를 할 때 사용한다.
- HAVING절은 GROUP BY절과 함께 사용이 된다.
--> 결과
17) 서브쿼리
- 서브쿼리: 하나의 SQL 문에 포함되어 있는 또 다른 SQL 문을 말한다.
- 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능하지만 ORDER BY는 사용하지 못한다.
[1] scott 보다 월급이 더 많은 직원의 이름 출력
select sal
from emp
where ename='scott'; -- scott 월급--> 결과
| 3000 |
select ename
from emp
where sal >(
select sal
from emp
where ename='scott'
); -- 서브쿼리 이용--> 결과
| KING |
[2]
select ename, sal, deptno
from emp
where ename=(select min(ename) from emp);- 알파벳 순서상 가장 앞에 오는 이름에 대한 정보를 출력한다.
--> 결과
[3]
select ename, sal
from emp
where sal <(select avg(sal) from emp);- 평균 월급보다 적게 받는 사람들을 출력한다.
--> 결과
[4] 같은 조건 join문으로 바꾸기
select ename, deptno
from emp
where deptno = (
select deptno
from dept
where dname = 'SALES'
);↓ join문으로 변환하기
select E.ename, E.deptno
from emp E join dept D
on E.deptno = D.deptno
where D.dname = 'SALES';--> 결과
[5] IN
select ename, sal, deptno
from emp
where ename in (
select min(ename)
from emp
group by deptno
); -- =은 안되고 in이 가능- in에 있는 서브쿼리가 먼저 실행된다.
- 부서별로 알파벳이 빠른순서로 데이터를 출력한다.
--> 결과
[6]
select ename, sal, deptno
from emp E
where sal >(
select avg(sal)
from emp
where deptno = E.deptno
);- 부서번호를 통해서 평균 임금을 산출한다.
- 평균 임금보다 클 경우의 데이터를 출력한다.
--> 결과
[7]
select deptno, empno, ename, sal
from emp E
where E.sal = (
select max(sal)
from emp
where deptno= E.deptno
);- 부서별 최고 급여를 받는 직원의 부서코드, 사번, 이름, 급여를 출력
--> 결과
18) CTE(Common Table Expression)
- 쿼리를 통해 만들어낸 임시적인 데이터 세트이다.
- 기존의 뷰나 파생 테이블, 임시 테이블 등으로 사용되는 것들을 대신할 수 있고, 보다 더 간결한 표현을 사용할 수 있따는 장점이 있다.
WITH 테이블 이름 AS (테이블 만들 쿼리문)[1]
with max_sal as ( -- 가독성을 더 좋게 만들어줌
select deptno, max(sal)as msal
from emp
group by deptno
)
select E.deptno, E.empno, E.ename, E.sal
from emp E join max_sal
on E.deptno = max_sal.DEPTNO
where E.sal = max_sal.msal;- 부서별 최대임금을 받는 사람들을 뽑는다.
--> 결과
[2] 평균 급여가 가장 높은 부서코드와 평균급여를 출력
with avg_cte as (
select deptno, avg(sal) as avg_sal
from emp
group by deptno
)
select deptno, avg_sal
from avg_cte
where avg_sal = ( select max(avg_sal) from avg_cte);--> 결과
19) View
- 테이블과 동일하게 사용되는 개체이다.
- SQL에서 하나 이상의 테이블 에서 원하는 모든 데이터를 선택하여, 그들을 사용자 정의하여 나타낸 것이다.
[1]
create view AVG_VIEW as
select deptno, avg(sal) as avg_sal
from emp
group by deptno; -- 부서별로의 평균임금의 view AVG_VIEW를 생성한다.
select deptno, avg_sal
from AVG_VIEW
where avg_sal = (select max(avg_sal) from AVG_VIEW);
-- AVG_VIEW를 이용해 평균임금이 가장큰 부서번호와 평균임금을 출력한다.--> 결과
'비트교육센터 > MySql' 카테고리의 다른 글
| [비트교육센터] MySql 23일차 ERD 추가문제, JDBC, 싱글톤패턴 (0) | 2023.06.30 |
|---|---|
| [비트교육센터] MySql 22일차 ERD 실습 추가문제 (0) | 2023.06.29 |
| [비트교육센터] MySql 21일차 DDL, ERD (0) | 2023.06.29 |
| [비트교육센터] MySql 19일차 MySql Workbench 설치 (0) | 2023.06.26 |