SQL 기초 DAY02

개-발뚜-발·2023년 10월 10일

쿼리

목록 보기

7/7

select : 조회용 sql
데이터베이스에서 실행하고자 하는 명령의 종류.

@ CRUD : 데이터 기본사항 처리 로직
1. CREATE : 데이터 추가 / INSERT - DML
2. READ : 데이터 조회 / SELECT
3. UPDATE : 데이터 수정 / UPDATE
4. DELETE : 데이터 삭제 / DELETE

연산자
비교 연산자
<, >, <=, >= : 크기를 비교하는 부등호
= : 같다
!=, <> : 같지않다

EMPLOYEE 테이블에서 부서코드가 'D9'이 아닌 직원들의 모든 정보 조회
SELECT *
from EMPLOYEE
where DEPT_CODE <> 'D9';
EMPLOYEE 테이블에서 급여가 350이상 550이하인 직원의 사번, 사원명,부서코드, 직급코드, 급여

SELECT
EMP_ID as 사번
,EMP_NAME as 사원명
,DEPT_CODE as 부서코드
,JOB_CODE as 직급코드
,SALARY as 급여
from EMPLOYEE
where SALARY >=3500000 and SALARY <=5500000;

-- BETWEEN A AND B 사용
SELECT
EMP_ID as 사번
,EMP_NAME as 사원명
,DEPT_CODE as 부서코드
,JOB_CODE as 직급코드
,SALARY as 급여
from EMPLOYEE
where SALARY BETWEEN 3500000 and 5500000;
where SALARY NOT BETWEEN 3500000 and 5500000; -- 두 조건을 제외한 경우

LIKE : 입력한 숫자, 문자가 포함된 정보를 조회할 때 사용하는 연산자.
'_' : 임의의 한 문자
'%' : 몇자리 문자든 관계없이

EMPLOYEE테이블에서 사원이름 가운데 '중'이 들어가는 사원정보 조회

SELECT *
from EMPLOYEE
where EMPNAME LIKE '중_';

EMPLOYEE테이블에서 주민번호를 기준으로 여성인 사원의 정보 조회(주민번호에 2)

SELECT *
from EMPLOYEE
where EMP_NO LIKE '%-2%';

실습1
사원 중 이메일 아이디가 5글자 초과하는 사원의 사원명,사번,이메일 정보 조회

SELECT
EMP_NAME as 사원명
,EMP_ID as 사번
,EMAIL as 이메일
FROM EMPLOYEE
where email like '__%@%';

사원 중 이메일4번째자리가 인 사원정보조회
ESCAPE문자를 선언하여 그 뒤에 오는 문자를 특수문자가 아닌 일반문자로 선언하여 사용.
SELECT *
FROM EMPLOYEE
where email like '____#%@%' ESCAPE '#'; -- !, #등 다른문자도 상관없음.

in( 연산자)
in(값1, 값2, 값3....)
안에 있는 값 중 하나라도 일치하는 경우 해당하는 값을 조회한다.

부서코드가 D1이거나 D6인 직원정보조회 DEPT_CODE = 'D1' or DEPT_CODE = 'D6'

SELECT *
FROM EMPLOYEE
where DEPT_CODE in ('D1', 'D6');

부서코드가 D1이거나 D6이 아닌 직원정보조회

SELECT *
FROM EMPLOYEE
where DEPT_CODE not in ('D1', 'D6');

[함수(funtion)]

문자관련 함수
LENGTH / CHAR_LENGTH : 문자열의 길이를 계산하는 함수
LENGTH : BYTE의 길이(영어1, 한글3)
CHAR_LENGTH : 글자 수

SELECT LENGTH ('Hello'), CHAR_LENGTH('hello') ;
-- from절 생략. 테이블에서 가져오는 값이 아닌 직접 입력해주는 값이기 떄문에

INSTR() : 주어진 값에서 원하는 문자가 몇번쨰 인지 찾아 반환

select instr('ABCDE', 'A');
SELECT instr('ABCDE', 'F');

SUBSTR(컬럼명|값, 시작순번[,가져올개수]) : 주어진 문자열에서 특정 부분만 꺼내오는 함수

실습2. EMPLOYEE 테이블에서 사원들의 이름, 이메일을 조회하되 이메일은 아이디부분

SELECT EMP_NAME, SUBSTR(EMAIL ,1, INSTR(EMAIL, '@')-1)
from EMPLOYEE;

LPAD / RPAD - 빈칸을 지정한 문자로 채우는 함수

SELECT
LPAD(EMAIL, 20, "#"),
RPAD(EMAIL, 20, "-")
from EMPLOYEE;

LTRIM : 좌측 공백만 지워줌. / RTRIM : 우측 공백만 지워줌.
현재 부여된 컬럼이나, 값으로 부터 공백만 찾아 지워주는 함수

SELECT LTRIM(' He llo '); -- 우측 공백은 지워지지않음. / 가운데 공백은 제외
select RTRIM(' Hel lo '); -- 좌측 공백은 지워지지않음. / 가운데 공백은 제외

TRIM : 주어진 칼럼이나 값을 양끝 기준으로 특정문자를 지워주는 함수

SELECT TRIM(' Hello '); -- 좌우 공백이 지워짐.

-- 양 끝의 특정 값을 지우고자 할 떄
SELECT TRIM('0' FROM '000Hello00000'); -- 양쪽 0이 모두 사라짐

LOWER / UPPER 주어진 문자를 소문자, 대문자 형식으로 변경

SELECT LOWER('AbcD'), UPPER('AbcD');

CONCAT : 여러 문자열을 하나의 문자열로 합치는 함수

SELECT CONCAT ("mysql", "재밋어요");

SELECT CONCAT(RPAD(SUBSTR(EMP_NAME,1,1),3,'*'),'님')
from EMPLOYEE;

REPLACE() : 주어진 문자열에서 특정 문자를 변경할 때 사용하는 함수
SELECT REPLACE ('HELLO WORLD', 'HELLO', 'BYE');

실습3
EMPLOYEE 테이블에서 사원의 주민번호를 확인하여 생년, 생월, 생일을 각각 조회
예시 : 이름 | 생년 | 생월 | 생일 / 홍길동 00년 00월 00일

SELECT
EMP_NAME as 이름
,CONCAT(SUBSTR(EMP_NO,1,2),"년") as 생년
,CONCAT(SUBSTR(EMP_NO,3,2),"월") as 생월
,CONCAT(SUBSTR(EMP_NO,5,2),"일") as 생일
from EMPLOYEE

SELECT *
from EMPLOYEE e

실습4
EMPOYEE 테이블에서 사원의 사번, 사원명, 이메일, 주민번호를 조회
이 때, 이메일은 '@'전까지, 주민번호는 7짜리 이후는 '*' 처리

SELECT
EMP_ID as 사번
,EMP_NAME as 사원명
,SUBSTR(EMAIL ,1, INSTR(EMAIL, '@')-1) as 이메일
,CONCAT(SUBSTR(EMP_NO, 1, 7),'***') as 주민번호
from EMPLOYEE;

실습5
EMPLOYEE 테이블에서 현재 근무하는 여성의 사번,사원명,직급코드 조회.
**ENT_YN : 현재 근무여부 / where에 함수 사용 가능.

SELECT
EMP_ID as 사번
,EMP_NAME as 사원명
,DEPT_CODE as 직급코드
from EMPLOYEE
where SUBSTR(EMP_NO,8,1) = '2'
and ENT_YN = 'Y';

다중 행 함수(Multiple Row Function)
조건절에 만족하는 모든 행을 다 찾고나서 모든 로우를 한번에 연산.
그룹 함수(Group Function)
SUM(), AVG(), MAX(), MIN(), COUNT()

날짜 처리 함수
SYSDATE(), NOW() : 현재 컴퓨터의 날짜 반환

SELECT SYSDATE(); -- 실제동작시점
SELECT NOW(); -- 쿼리실행시점

두 날짜 사이의 차
DATEDIFF : 단순 일 차이
TIMESTAMPDIFF : 연, 분기, 월 주, 일 시, 분 초를 지정하여 차이

SELECT
HIRE_DATE AS "입사일"
,DATEDIFF(NOW(),HIRE_DATE) "입사 후 일 수"
FROM EMPLOYEE;

SELECT
HIRE_DATE "입사일"
,TIMESTAMPDIFF(MONTH,HIRE_DATE,NOW())"입사 후 일 수(년도)"
FROM EMPLOYEE;

YEAR : 연
QUARTER : 분기
MONTH : 월
WEEK : 주
DAY : 일
HOUR : 시
MINUTE : 분
SECOND : 초

EXTRACT(YEAR|MONTH|DAY FROM 날짜데이터)

원하는 년, 월, 일 데이터를 추출한다.

SELECT
EXTRACT(YEAR FROM HIRE_DATE)
,EXTRACT(MONTH FROM HIRE_DATE)
,EXTRACT(DAY FROM HIRE_DATE)
FROM EMPLOYEE;

DATE FORMAT() : 날짜 포맷 변경

SELECT
HIRE_DATE
,DATE_FORMAT(HIRE_DATE,'%Y%m%d%h%i%s')
,DATE_FORMAT(HIRE_DATE,'%Y/%m/%d %H:%i:%s')
,DATE_FORMAT(NOW(),'%y/%m/%d %H:%i:%s')
FROM EMPLOYEE;

STR_TO_DATE() -- STRING 형식의 데이터만 날짜로 변환해줌.

SELECT
STR_TO_DATE('20231010', '%Y%m%d')
,STR_TO_DATE('231010', '%y%m%d');

IF(조건, 참, 거짓)
조건에 AND, OR등을 통해 여러 조건식을 만들 수 있음

현재 근무하는 직원들의 성별을 남,여로 구분지어 출력하기

SELECT
EMP_NAME
,EMP_NO
,SUBSTR(EMP_NO,8,1)
,IF(SUBSTR(EMP_NO,8,1) = '1',"남","여") 성별
FROM EMPLOYEE
ORDER BY 성별;

실습6
EMPOLOYEE 테이블에서 모든 직원의
사번,사원명,부서코드,직급코드,근무여부,관리자 여부를 조회
이 때, 근무여부 : ENT_YN이 'Y'면 퇴사자 / 'N'이면 근무자
관리자여부 : MANAGER_ID가 있으면 사원 / 없으면 관리자

SELECT
EMP_ID 사번
,EMP_NAME 사원명
,DEPT_CODE 부서코드
,JOB_CODE 직급코드
,IF(ENT_YN = 'N','근무자','퇴사자') 근무여부
,IF(IFNULL(MANAGER_ID,0) = 0, "관리자","사원") 관리자여부
FROM EMPLOYEE;

CASE 문
자바의 IF, SWITCH처럼 사용 가능한 함수 표현식
사용법
CASE WHEN(조건식1)THEN 결과값1
WHEN(조건식2)THEN 결과값2
ELSE 결과값3
END

실습6 / CASE를 이용하여 문제 해결

SELECT
EMP_ID 사번,
EMP_NAME 사원명,
DEPT_CODE 부서코드,
JOB_CODE 직급코드,
CASE
WHEN ENT_YN = 'Y' THEN '퇴사자'
WHEN ENT_YN = 'N' THEN '근무자'
END "근무여부",
CASE
WHEN MANAGER_ID IS NULL THEN '관리자' ELSE '사원'
END "관리자 여부"
FROM EMPLOYEE;