11/30 - SQL(2) : where, order by, limit, 내장함수, 그룹화(group by, having)

접속

-- 로컬

mysql -u 사용자명 -p

mysql -u 사용자명 -p!123456 (되긴 하는데 암호가 그대로 보이기때문에 절대 X)

-- 원격

mysql -u 사용자명 -p 데이터베이스명-h ip

 

show databases;

use 데이터베이스명;

 

이걸 한번에 

mysql -u 사용자명 -p 데이터베이스명

 

 

작업관리자 > 서비스에서 저렇게 중지하고 접속하려고 하면 안된다.

 

* 데이터베이스 / 테이블 / 컬럼에 대한 내용 파악

show databases;

show tables;

describe 테이블명;

+ 한글 설명

=> ERD(Entity Relation Diagram) => 그림

                  => 그림을 그리는 tool

 

 

* DML        - 데이터를 다루는 SQL

                 select   - 데이터 보기만 가능 / 변경 불가

                 insert

                 update

                 delete

   DDL         - 데이터 저장소를 다루는 SQL

                 create

                 alter

                 drop

                 rename

                 truncate

 

 

 

* 형식

select 컬럼절 ...           // 컬럼제한

from 테이블명

where 조건절              // 행제한

 

 

* 예시

(1) 부서 20번 부서인 사원에 대한 사원번호, 사원명, 급여, 부서번호..

select empno, ename, sal, deptno

-- 1. *

-- 2. alias

-- 3. distinct

from emp 

where deptno = 20;

-- 1. 비교연산자, 논리연산자

-- 2. '문자열'

-- 3. '문자열' - ascii 코드값으로 비교

 

(2) 사원명 s로 시작하는 사원에 대한 사원번호, 사원명, 급여, 부서번호

select empno, ename, sal, deptno

from emp

where ename >= 's' ;

 

(3) 20번 부서에서 사원명 s로 시작하는 사원에 대한 사원번호, 사원명, 급여, 부서번호

select empno, ename, sal, deptno

from emp

where deptno = 20 and ename >= 's' and ename < 't';

↓

select empno, ename, sal, deptno
from emp
where ename between 's' and 't';

 

---

 

 

 

 

 

 

 

DML - select

---

1. where 절 

 

1-1. in

(1) 직책이 clerk, manager, analy 사원에 대한 사원번호, 사원이름, 직책

select empno, ename, job
from emp
where job in ('clerk', 'manager','analyst');

 

(2) 부서번호 10,20 부서인 사원에 대한 사원번호, 사원이름, 직책, 부서번호

select empno, ename, job, deptno 

from emp
where deptno in (10,20);

 

1-2. between

(1) 급여가 1000 이상 2000 이하인 사원에 대한 사원번호, 사원이름, 급여

select empno, ename, sal
from emp
where sal between 1000 and 2000;

(숫자는 1000이상 2000이하가 되는데 문자일 경우 하한 문자 이상 상한 문자 미만이 된다.)

 

 

* 부분 문자열 (문자열의 형식 ; pattern)

~ 시작하는 

~ 으로 끝나는

~ 포함 되어진

 

 

 

1-3. like

_           -> 어떤 글자 한 자            // 글자수 조건에 많이 씀

%         -> 갯수에 상관없이 0개 여러자

_a        -> 1a .. 2a

%a      -> a, 11a, 22a, 333a  => a로 끝나는 문자열

a%      => a로 시작하는 문자열

%a%  =>  a가 포함 된어진 문자열

 

 

(1) s 로 시작하는 사원에 대한 정보

select empno, ename
from emp
where ename like 's%';

 

 

(2) h로 끝나는 사원에 대한 정보

select empno, ename
from emp
where ename like '%h';

 

(3) co가 들어가는 사원에 대한 정보

select empno, ename, sal, deptno
from emp
where ename like '%co%';

 

(4) 이름이 4글자인 사원에 대한 정보

select empno, ename, deptno
from emp
where ename like '____';

 

(5) 입사일자가 2월인 사원에 대한 정보

 select empno, ename, deptno, hiredate
from emp
where hiredate like '____-02-__';

(혹은 where hiredate like '%02_%';)

 

 

 

1-4. not

이름이 4글자가 아닌 사원에 대한 정보

select empno, ename, sal

from emp

where not ename like '____';

 

select empno, ename, job, deptno

from emp

where deptno not in(10,20);

 

select empno, ename, sal

from emp

where not sal between 1000 and 2000;

 

select empno, ename, comm

from emp

where comm is not null;

 

 

 

 

 

 

 

 

2. 정렬  -  order by

순서 - order by 컬럼명

            오름   asc      (생략가능)

            내림   desc

 

 

 

컬럼 하나를 내림차순

컬럼 두개 오름차순

 

 

 

 

 

 

3. 갯수 제한  -  limit

위에서부터 정해준 갯수만큼 보여준다.

 

 

 

(1) 사원번호 순으로 정렬해서 상위 5개 사원에 대한 사원번호, 사원 이름 출력

 

 

 

 

4. 내장함수

Built-in Functions - MariaDB Knowledge Base

Built-in Functions

< 단일행 함수 > 

4-1. Information Function

select user();

select database();

select version();

 

4-2. Numeric(Math) Function

abs()

ceil()

floor()

round()

truncate()

power()

mode()

 

 

4-3. String Function

length()

char_length()

concat()

instr()

left() , right() , mid()

 

 

활용문제) 20번 부서의 사원에 대한 연봉정보(sal * 12)

ex) Clark 님의 연봉은 29400원 입니다.

 

 

 

활용 문제) s로 시작하는 사원이름 검색

 

 

select substring('mysql database study',5, 5);

select replace('mysql database study', 'study', '스터디');

select insert('mysql database study', 7,4, '데이터베이스'); 

select lcase('ABC'), ucase('abc');

select reverse('ABC');

select Itrim('           mysql study');

select rpad('hi', 4,'?');

 

4-4. Date and Time Function

 select now(), sysdate(), current_timestamp();

select curdate(), current_date();

select curtime(), current_time();

select now(), now()+1;

select now(), date_add(now(),interval 2 second);

select now(), date_add(now(),interval 2 day);

select now(), date_sub(now(),interval 2 day);

select now(), year(now());

select now(), year(now()), year('20201130');

select now(), year(now()), year('2020-11-30');

select now(), date_format(now(), '%Y-%m-%d');

select now(), date_format(now(), '%Y:%m-%d');

 

활용문제) 30번 부서의 사원에 대한 정보를 표시하는데 입사일자를 년도:월:일 형태로 표시하시오.

select hiredate, date_format(hiredate,'%Y:%m:%d')
from emp
where deptno = 30;

시간데이터도 볼수 있다.

select hiredate, date_format(hiredate,'%H:%i:%S')
from emp
where deptno = 30;

 

 

4-5. Comparison Function

greatest()

least()

ifnull()

 

select greatest(100,101,102);

select least(100,101,102);

select greatest(sal, 0) from emp;     // 0보다 큰 sal 

select if(1=2, '참', '거짓');

select if(1!=2, '참', '거짓');

 

select ename, sal, if(sal < 2000, '적음','많음')
from emp
where deptno=30;

 

select ifnull(null, '참');

select ifnull(0, '참');

이걸로 피연산자로 null이 들어가서 결과가 그냥 null이 되어버리는 경우를 막을 수 있다.

예를 들어,

select sal, comm, sal*12+comm from emp;

이랬던 것이 ifnull을 사용하면

select sal, comm, sal*12+ifnull(comm,0) from emp;

 

 

1) switch

case 값

when 값 then 결과

when 값 then 결과

else 결과 end;

 

select case 1
when 1 then 'one'
when 2 then 'two'
else 'more' end;

select empno, ename, job, sal,
case job
when 'analyst' then sal*1.1
when 'clerk' then sal*1.2
when 'president' then sal*1.3
else sal
end
from emp;

 

sql을 메모장에 쳐서 가져다가 쓸 수도 있다. sql 스크립트

 

 

2) if else

case 값

when 조건 then 결과

when 조건 then 결과

else 결과 end;

 

 

 

4-6. 그룹함수(여러개의 행 => 한 개 결과)

count  -  행의 결과  -  데이터의 갯수

select count(sal) from emp;       // null을 제외하고 센다.

select count(comm) from emp;

select count(*) from emp;         // null도 다 센다. (대부분 이걸 써줌)

 

1) sum

 

2) avg

 

3) std

 

select avg(comm), sum(comm)/count(*) from emp;

값이 다른 걸 아래 두개의 방법으로 해결!

select avg(comm), sum(comm)/count(comm) from emp;

select avg(ifnull(comm,0)) , sum(comm)/count(ifnull(comm,0))  from emp;

값이 달랐던 이유는 count(*)만 null도 같이 세기 때문.

 

4) max

5) min

 select max(sal) , min(sal) from emp;

 

 

 

 

4-7. 그룹화

1) group by  

            +  그룹함수

 

 

활용문제) 부서별 평균 급여

 

 

활용문제) 직책별 최고 급여

 

 

 활용문제) 부서별 직책별 최고 급여

 

 

2) having

그룹함수에 대한 조건은 having을 쓴다.

where 못쓴다.

having절을 group by 다음에 와야함.

 

그룹함수는 그룹화 다음에 이루어진다. 그래서 where 절 안에 들어갈수 없다.

having 절엔 그룹함수가 들어가고

where에는 컬럼 / 단일행 함수까지 들어간다.

 

 

 

 

 

※ 문제

1. 데이터베이스의 목록을 확인할 수 있는 SQL 문장을 기술하십시오.
show databases;

2. 테이블의 목록을 확인할 수 있는 SQL 문장을 기술하십시오.
show tables;

3. 테이블의 구조를 확인할 수 있는 SQL 문장을 기술하십시오.
desc 테이블명;

* 현재 사용하는 있는 데이터베이스의 이름을 출력하는 SQL 문장을 기술하십시오.
select database();

* 현재 사용자 이름을 출력하는 SQL 문장을 기술하십시오.
select user();

4. 사원 테이블에서 직원들의 연봉(SAL*12+comm)을 계산하여, 컬럼명은 "사원연봉"으로 출력하는 SQL 문장을 기술하십시오.
 select sal*12+ifnull(comm,0) 사원연봉 from emp;    -> alias 띄어쓰기 안됨
select sal*12+ifnull(comm,0) '사원 연봉' from emp;   -> 띄어쓰기 하려면 '' 안에 alias 입력

5. 사원 테이블을 이용하여 다음과 같은 결과를 얻을 수 있는 SQL 문장을 기술하십시오.
select concat(ename, '의 업무는', job, '이고 급여는',truncate(sal,0),'만원입니다') 사원정보 from emp;

사원정보
-----------------------------------------------------------------------
SMITH의 업무는 CLERK이고 급여는 800만원입니다
ALLEN의 업무는 SALESMAN이고 급여는 1600만원입니다
WARD의 업무는 SALESMAN이고 급여는 1250만원입니다
JONES의 업무는 MANAGER이고 급여는 2975만원입니다
MARTIN의 업무는 SALESMAN이고 급여는 1250만원입니다
BLAKE의 업무는 MANAGER이고 급여는 2850만원입니다
CLARK의 업무는 MANAGER이고 급여는 2450만원입니다
SCOTT의 업무는 ANALYST이고 급여는 3000만원입니다
KING의 업무는 PRESIDENT이고 급여는 5000만원입니다
TURNER의 업무는 SALESMAN이고 급여는 1500만원입니다
ADAMS의 업무는 CLERK이고 급여는 1100만원입니다
JAMES의 업무는 CLERK이고 급여는 950만원입니다
FORD의 업무는 ANALYST이고 급여는 3000만원입니다
MILLER의 업무는 CLERK이고 급여는 1300만원입니다

6. 사원 테이블에서 입사일이 2011년도인 사원의 사원번호, 사원이름, 입사일, 업무, 급여를 검색하는 SQL 문장을 기술하십시오.
select empno, ename, hiredate, job, sal
from emp
where hiredate like '2011-%';

7. 사원 테이블에서 입사일이 2011년이고 업무가 'SALESMAN'이 아닌 사원의 사원번호, 사원이름, 입사일, 업무, 급여를 검색하는 SQL 문장을 기술하십시오.
select empno, ename, hiredate, job, sal
from emp
where hiredate like '2011-%' and job != 'salesman';

8. 사원 테이블의 사원번호, 사원이름, 입사일, 업무, 급여를 급여가 높은 순으로 정렬하고, 급여가 같으면 입사일이 빠른 사원으로 정렬하는 SQL 문장을 기술하십시오.
select empno, ename, hiredate, job, sal
from emp
order by sal, hiredate;

9. 사원 테이블에서 사원이름의 세 번째 알파벳이 'N'인 사원의 사원번호, 사원이름을 검색하는 SQL 문장을 기술하십시오.
select empno, ename
from emp
where ename like '__N%';

10. 사원 테이블에서 연봉(SAL*12)이 35000 이상인 사번, 사원명, 연봉을 검색하는 SQL 문장을 기술하십시오.
select empno, ename, sal
from emp
where sal*12 >=35000;

11. 사원 테이블의 사원명에서 2번째 문자부터 3개의 문자를 추출하여 출력하는 SQL 문장을 기술하십시오.
select substring(ename, 2,3)
from emp;

12. 사원 테이블에서 입사일이 12월인 사원의 사번, 사원명, 입사일을 검색하는 SQL 문장을 기술하십시오.
select empno, ename, hiredate
from emp
where hiredate like '%-12-%';

13. 다음과 같은 결과를 출력하는 SQL 문장을 기술하십시오.
  EMPNO ENAME 입사일
---------- ---------- ----------
7369 SMITH 1980-12-17
7499 ALLEN 1981-02-20
7521 WARD 1981-02-22
...
7934 MILLER 1982-01-23

14 개의 행이 선택되었습니다.

select empno, ename, hiredate 입사일
from emp;

* 다음과 같은 결과를 출력하는 SQL 문장을 기술하십시오.
EMPNO ENAME 급여
---------- ---------- --------------------
7369 SMITH *******800
7499 ALLEN ******1600
7521 WARD ******1250
...
7934 MILLER ******1300

14 개의 행이 선택되었습니다.

select empno, ename, truncate(sal,0) 급여
from emp;