-- 연산자 --
-- 비교 연산자
-- < , > , <= , >= : 크기를 비교
-- = : 같다
-- != , <> :같지않다.
-- EMPLOYEE 테이블에서 부서코드가 'D9'이 아닌
-- 직원들의모든 정보를 조회
SELECT * FROM EMPLOYEE
-- WHERE DEPT_CODE != 'D9';
WHERE DEPT_CODE <> 'D9';
-- EMPLOYEE 테이블에서 급여가 350만원 이상 550만원 이하인
-- 직원의 사번, 사원명 , 부서코드 , 직급코드 , 급여 정보 조회
SELECT EMP_ID , EMP_NAME , DEPT_CODE , JOB_CODE , SALARY
FROM EMPLOYEE
WHERE SALARY >= 3500000 and SALARY <= 5500000
-- order by SALARY ASC / desc ; 오름차순 내림차순 정렬
-- 2.BETWEEN A AND B
SELECT EMP_ID ,EMP_NAME , DEPT_CODE , JOB_CODE , SALARY
FROM EMPLOYEE
WHERE SALARY BETWEEN 3500000 AND 5500000 ;
-- 위와 동일한 정보를 조회하되 조건이 350만원 미만 , 550만원 초과인 직원
SELECT EMP_ID ,EMP_NAME , DEPT_CODE , JOB_CODE , SALARY
FROM EMPLOYEE
WHERE SALARY not BETWEEN 3500000 AND 5500000 ;
-- LIKE :
-- 입력한 숫자 , 문자가 포함된 정보를 조회할때 사용하는 연산자
-- '_' : 임의의 문자 하나
-- '%' : 몇자리 문자든 관계없이
-- EMPLOYEE 테이블에서 사원 이름에 가운데 글자가 '중' 이 들어가는 사원정보를 조회
select *
from EMPLOYEE
WHERE EMP_NAME like '_중_';
-- EMPLOYEE 테이블에서 주민등록번호 기준 여성인 사원의 정보만 조회
SELECT *
FROM EMPLOYEE
WHERE EMP_NO like '%-2%';
-- 사원 중 이메일 아이디가 5글자를 초과하는 사원의 사원명 사번 이메일 조회
SELECT EMP_NAME ,EMP_ID , EMAIL
from EMPLOYEE
where EMAIL like '______%@%';
-- 사원 중 이메일 네번째 자리가 '_'인 사원의 정보 조회하기
-- escape 문자를 선언하여 뒤에오는 특수문자를특수문자가아닌 일반문자로 선언
SELECT EMP_NAME ,EMP_ID , EMAIL
from EMPLOYEE
where EMAIL like '___*_%@%' escape '*';
-- 1n 연산자
-- in ( 값 1, 2 , 3 , ...)
SELECT *
from EMPLOYEE e
WHERE DEPT_CODE in( 'd1' , 'd6');
-- d1 도 d6도 아닌 부서의 직원정보
SELECT *
FROM EMPLOYEE e
where DEPT_CODE not in ('d1' , 'd6');
-- 함수 (FUNCTION)--
-- 문자 관련 함수
-- length . char_length
-- 문자열의 길이를 계산하는 함수
-- length () : byte 의 길이 (영문자 1바이트 ,한글 3 바이트 )
-- char_length : 글자의 수
SELECT LENGTH ('hello') , CHAR_LENGTH('hello');
SELECT LENGTH ("이창진") , CHAR_LENGTH("이창진") ;
-- instr : 주어진 값에서 원하는 문자가 몇번째인지 찾아 반환
SELECT INSTR ('ABCDE' , 'A'),
INSTR ('ABCDE' , 'C'),
INSTR ('ABCDE' , 'CD'),
INSTR ('ABCDE' , 'X' );
-- SUBSTR :주어진 문자열에서 특정 부분만 꺼내어 오는 함수
SELECT 'hello world' , SUBSTR('hello world', 3 , 5 ),SUBSTR('hello world ' . 7 ) ;
-- 3부터 5개를 가져올것
-- 실습 1 .
-- employee 테이블에서 사원들의 이름과 ㅡ 이메일주소 조회하되
-- 이메일은 아이디부분만조회하기
-- 조회결과 --
-- 홍길동 hong_gd
--
SELECT EMP_NAME , EMAIL
from EMPLOYEE ;
-- 이메일에서 골뱅이까지의 값을 찾겠다
SELECT instr (email , '@')
from EMPLOYEE ;
-- 1부터 골뱅이 위치까지 값을출력하겠다
SELECT emp_name ,
SUBSTR(email , 1 , instr ( email , '@')-1) 'EMAIL'
from EMPLOYEE ;
-- LPAD / RPAD
-- 빈칸을 지정한 문자로 채우는 함수
SELECT LPAD(EMAIL , 20 , '#') , RPAD(EMAIL , 20 , '-') FROM EMPLOYEE ;
-- LPAD = LEFT 에 빈칸을 채우고 RPAD = RIGHT에 빈칸을 채움
-- LTRIM / RTRIM
-- 값으로 부터 공백을 찾아 지워주는 함수
SELECT LTRIM(' HELLO ');
SELECT RTRIM('HELLO ');
-- TRIM
-- 주어진 문자열에서 양끝을 기준으로
-- 특정 문자를 지워주는 함수 ( 기본은 공백을 지움 )
SELECT TRIM(' DATE ');
-- 양쪽의 특정문자 지우기
SELECT trim('0' from '0000001230000');
-- leading 앞에있는 문자만 찾아 지운다 .
SELECT trim(leading '0' from '000001230000');
-- trailing 뒤에서부터 문자를 찾아 지운다 .
SELECT trim(TRAILING '0' from '000000123000000');
-- concat : 여러 문자열을 하나의 문자열로 합치는 함수
SELECT CONCAT('마이에스큐엘 ' ,'재밌어용 ^^ ');
-- replace : 주어진 문자열에서 특정 문자를 변경할때 사용하는 함수
SELECT REPLACE ('HELLO WORLD' , 'HELLO' , 'BYE');
-- 실습 2 --
-- EMPLOYEE 테이블에서
-- 사원의 주민번호를 확인하여
-- 생년 월 일을 각각 조회
SELECT emp_name '이름', CONCAT(substr(emp_no , 1 ,2 ), "년") "생년" ,
CONCAT(SUBSTR(EMP_NO ,3,2),"월") "월",
concat (SUBSTR(emp_no,5,2),"일") "일"
from EMPLOYEE ;
-- 실습 3 --
-- EMPLOYEE 테이블에서 모든사원의 사번 ,사원명 , 이메일 , 주민번호를 조회
-- 이때 , 이메일은 '@' 전까지 ,
-- 주민번호는 7번째자리 이후 '*' 처리하여 조회
SELECT EMP_ID "사번" , EMP_NAME "사원명" ,
SUBSTR(email , 1 , instr ( email , '@')-1) '이메일',
RPAD( SUBSTR(emp_no,1,8), 14 , '*') 주민번호
-- rpad 를 이용해 14자리까지 숫자의 오른쪽을 *로 채우겠다 , 어디서부터?
-- emp의 1 부터 8 까지 잘라내고 난 후부터
from EMPLOYEE ;
-- 실습 4.
-- employee 테이블에서 현재 근무하는
-- 여성 사원의 사번 , 사원명 , 직급코드를 조회 .
-- ent _ yn : 현재 근무여부 파악하는 컬럼
-- where 에서도함수 사용가능
SELECT EMP_ID 사번 , EMP_NAME 이름 , JOB_CODE 직급코드 , ENT_YN 퇴사여부
from EMPLOYEE e
WHERE ENT_YN = 'N'
and SUBSTR(EMP_NO , 8 , 1) = '2';
-- 주민번호의 8번째 숫자가 2 인경우 (여자인 경우)
-- 단일 행 함수 (single row function) --
-- 결과를 찾아 출력할때마다 각 행에 함수가 적용
-- 다중 행 함수 (multiple Row function) --
-- 조건절에 만족하는 모든 행을 다 찾음
-- 그룹 함수 (group function)
-- sum () , avg() , max() ,min () , count()
-- sum() : 조건에 맞는 row 별 해당 컬럼들의 합
SELECT sum(salary)
from EMPLOYEE e ;
-- avg () : 평균 , max() 컬럼중 최대값 ,min() 최소값 , count() : 갯수
SELECT avg (salary) , max(SALARY) , MIN(SALARY)
from EMPLOYEE e ;
-- 실습 5
-- employee테이블에서 해외영업 1부에 근무하는 모든 사람의평균 급여 가장높은 급여
-- 낮은 급여 급여 합계 조회
SELECT * from DEPARTMENT
SELECT avg (SALARY) , max( SALARY) , MIN(SALARY) , sum(SALARY)
from EMPLOYEE
WHERE DEPT_CODE = 'D5';
-- 에러남
SELECT max(salary) , emp_name from EMPLOYEE ;
SELECT COUNT(*) ,
COUNT(dept_code), -- DEPT_CODE 가있는것만 카운팅 (null 값 계산 안함)
count(DISTINCT DEPT_CODE)
-- 중복 제거
from EMPLOYEE e ;
select DISTINCT DEPT_CODE from EMPLOYEE ;
-- 날짜 처리 함수
-- SYSDATE() , NOW() : 현재 컴퓨터의 날짜를 반환하는 함수
select SYSDATE() , now();
-- 둘다 현재 날짜를 반환하지만 현재라고 하는 기준이 조금 다름.
SELECT now() , sleep (10) , SYSDATE();
-- now 는 쿼리 실행시점이 현재 / sysdate() 는 연산이 될때가 현재
-- 두 날짜사이의 차 구하기
-- DATEDIFF() : 단순 일 차이
-- timestampdiff 좀더 디테일한 단위 설정가능 (year/month/week/day/hour/minuet/second)
-- QUARTER -> 분기
SELECT HIRE_DATE ,
DATEDIFF(NOW() , HIRE_DATE) "입사 후 일 수 "
from EMPLOYEE ;
SELECT HIRE_DATE , TIMESTAMPDIFF(YEAR , HIRE_DATE,NOW()) "입사 후 년수"
from EMPLOYEE e ;
-- EXTRACT() : 지정한 날짜로 부터 값을 추출하는 함수
SELECT EXTRACT(YEAR FROM HIRE_DATE) 년,
EXTRACT(MONTH FROM HIRE_DATE)월 ,
EXTRACT(DAY FROM HIRE_DATE)일
FROM EMPLOYEE e ;
-- DATE_FORMAT() : 날짜 정보 포맷 변경
SELECT hire_date,
DATE_FORMAT(hire_date,'%Y/%m/%d/%h:%m:%s'),
DATE_FORMAT(now(),'%Y/%m/%d/%h:%m:%s')
from EMPLOYEE;
-- str_to_date () : 날짜 형식으로 바꿔주겠다 .
SELECT 20230322, str_to_date('20230322', '%Y%m%d'),
str_to_date('990322' , '%y%m%d' ); -- 00~69: 2000년대 70~99:1900년대로 계산
-- if () --
-- 자바의 삼항 연상자
-- () ? 값 1 : 값 2 ;
-- 현재 근무하는 직원들의 성별을 남 여로 구분짓기
SELECT EMP_NAME , EMP_NO ,
if(SUBSTR(EMP_NO,8,1) ='1','남','여') 성별
from EMPLOYEE ;
-- 실습 6
-- employee 테이블에서
-- 모든 직원의 사번 ,사원명 , 부서코드 , 직급코드 , 근무여부 , 관리자 여부를 조회
-- 만약 근무여부가 'Y' 퇴사자 , 'N' 근무자 ,
-- 관리자 사변(MANAGER_ID)이 있으면 사원 , 없으면 관리자
SELECT EMP_ID 사번 , EMP_NAME 사원명 ,DEPT_CODE 부서코드 ,JOB_CODE 직급코드 ,
if(ENT_YN= 'y','퇴사자','근무자') "근무 여부",
if(IFNULL( MANAGER_ID,0) = 0 , '관리자','사원')"관리자 여부"
from EMPLOYEE ;
-- 만약 매니저 아이디가 널이면 0으로 처리 0이면 관리자 , 아니면 사원
-- case 문
-- 자바의 if , switch 처럼 사용할 수 있는 함수 표현식
-- 사용방법
-- case
-- when (조건식1) then 결과 1
-- when (조건식2) then 결과 2
-- end '별칭'
SELECT EMP_ID 사번 , EMP_NAME 사원명 ,DEPT_CODE 부서코드 ,JOB_CODE 직급코드 ,
case
when ent_yn = 'y' then '퇴사자'
else '근무자'
end "근무여부" ,
case
when manager_id is null then '관리자'
else '사원'
end "관리자 여부"
from EMPLOYEE ;
-- 숫자 데이터 함수 --
-- ABS() : 절대값 표현
SELECT abs(10) , abs(-10);
-- mod() : 컬럼이나 값을 나눈 나머지 반환
SELECT mod(10,3) , mod(10,2) , mod(10,7);
-- round() : 지정한 숫자를 반올림할 때 사용하는 함수
SELECT ROUND(123.456, 0 ) ,ROUND(123.456 , 1) ,ROUND(123.456 , 2) , ROUND(123.456, -2);
-- ceil() : 소수점 첫째자리에서 올림
-- floor() : 소수점 이하 자리 숫자 버림
SELECT ceil(123.456), floor(123.456);
-- TRUNCATE() : 지정한 위치까지 숫자를 버리는 함수
select TRUNCATE(123.456,2);
-- dayofweek(냘짜)
-- 해당 날짜의 요일을 숫자로 구분
-- 1 : 일요일 ~ 7 : 토요일
SELECT dayofweek(now());
-- 입사일을 확인하야 무슨 요일인지 조회
SELECT emp_name,
case
when dayofweek(hire_date)=1 then '일요일'
when dayofweek(hire_date)=2 then '월요일'
when dayofweek(hire_date)=3 then '화요일'
when dayofweek(hire_date)=4 then '수요일'
when dayofweek(hire_date)=5 then '목요일'
when dayofweek(hire_date)=6 then '금요일'
when dayofweek(hire_date)=7 then '토요일'
end "요일"
from EMPLOYEE ;
-- date_add() : 특정 날짜를 기준으로 이후의 날짜
-- date_sub() : 특정 날짜를 기준으로 이전의 날짜
SELECT emp_name , hire_date ,
date_add(hire_date , interval 1 month),
date_sub(HIRE_DATE , INTERVAL 1 year)
from EMPLOYEE ;
-- 실습 7.
-- employee 테이블에서
-- 근무 년수가 20년 이상인 사원들의
-- 사번, 사원명 , 부서코드 ,입사일 조회
SELECT EMP_id , EMP_NAME , DEPT_CODE , HIRE_DATE
from EMPLOYEE
WHERE date_add(HIRE_DATE , interval 30 year) <= now();
-- 형 변환 함수
-- date_format() : 날짜 정보를 특정 포맷으로 변경하여 조회
SELECT DATE_FORMAT(now(), '%Y/%m/%d'),DATE_FORMAT(now(),'%T') ;
-- cast() , convert() : 주어진 값을 원하는 형식으로 변경
-- 숫자를 날짜로
SELECT cast(20230310 as date), convert(20230310 , date);
-- 숫자를 문자
SELECT cast(20230310 as char), CONVERT(20230310 , char);
-- 날짜를 문자
SELECT cast(now() as char);
-- select 문의 실행 순서
/*
* 5:select 컬럼명 , 계산식 , 함수식
* 1:from 테이블명
* 2:where 조건
* 3:group by 그룹을 묶을 컬럼명
* 4:having 그룹에 대한 조건식 , 함수식
* 6: order by 컬럼 [별칭] 순서
*
*/
-- order by 절
-- SELECT 를 통해 조회한 행의 결과를특정 기준에 맞춰 정렬하는 구문
SELECT EMP_ID , EMP_NAME 이름 , SALARY ,DEPT_CODE
FROM EMPLOYEE
-- ORDER BY EMP_NAME -- 기본값은 asc(오름차순)
-- order by dept_code , EMP_ID desc;
-- ㄴ 동일한 dept 코드 안에서는 id 순으로 또 정렬이 됨
-- order by 이름 desc;
-- 별칭으로도 가능함
order by 2;
-- 두번째 항목으로 정렬 (emp_name)
-- group by --
-- 평균급여 --
SELECT truncate(avg(salary), -3)
from EMPLOYEE e ;
-- 부서별 평균
SELECT truncate(avg(salary), -3)
from EMPLOYEE e
where DEPT_CODE = 'd6';
-- gruop by 절
-- 특정 컬럼이나 계산식을 하나의 그룹으로 묶어
-- 한 테이블 내에서 소그룹별로 조회하고자 할때 선언하는 구문
SELECT DEPT_CODE , TRUNCATE(avg(salary) , -3)
from EMPLOYEE
group by DEPT_CODE
order by 1;
-- 실습 8
-- employee 테이블에서
-- 부서 별 총인원 , 급여 합계, 급여 평균 , 최대 급여 , 최소 급여
-- 조회하여 부서코드 기준으로 오름차순 정렬.
-- 숫자 데이터는 100 의 자리까지 처리 (내림 truncate)
select dept_code ,
count(*),
sum(salary) ,
TRUNCATE(avg(salary), -2),
max(salary),
min(salary)
from EMPLOYEE
group by DEPT_CODE
order by 1;
-- 실습 9
-- employee 테이블에서
-- 직급코드 별 보너스를 받는 사원의 수를 조회하되 , 직급코드 순으로 내림차순 정렬
-- 직급 코드 , 보너스 받는 사원 수를 조회
SELECT JOB_CODE , count(bonus) -- 어차피 널값은세지 않음 보너스 있는사람만 카운트
from EMPLOYEE
group by JOB_CODE
order by 1 desc;
-- 남성 직원과 여성 직원의 수 조회
SELECT count(*) 직원수
from EMPLOYEE e
group by SUBSTR(EMP_NO,8,1);
SELECT DEPT_CODE , JOB_CODE , sum(salary)
from EMPLOYEE e
group by DEPT_CODE , job_code;
-- having 구문
-- gruop by 한 각 소그룹에 대한 조건을 설정 할때
-- 그룹함수와 함께 사용하는 조건 구문
select DEPT_CODE , avg(SALARY) 평균
from EMPLOYEE e
group by DEPT_CODE
having avg(SALARY)> 3000000;
-- 실습 10
-- 부서별 그룹의 급여 합계 중 900 만원을 초과하는 부서의 코드와
-- 급여 합 조회
SELECT DEPT_CODE , sum(SALARY) 합
from EMPLOYEE e
group by DEPT_CODE
having sum(SALARY) > 9000000;