부트캠프23일차 (집합연산자 함수 날짜)

부트캠프(DB)

부트캠프23일차 (집합연산자 함수 날짜)

동곤일상 2025. 3. 5. 17:48

TOO BAD (feat. Anderson .Paak)

아티스트: G-DRAGON

앨범: Übermensch

발매일: 2025.02.25

1) 집합연산자

1-1) UNION (합집합)

1-2) intersect (교집합)

2) 함수

2-1)대소문자 관련함수(upper lower)

2-2)문자열 길이함수(length)

2-3)문자열 연결함수(concat)

2-4)부분 문자열 (substr , left , right)

2-5) instr (문자위치 인덱스 리턴)

2-6)문자추가함수 (lpad , rpad)

2-7) 문자 제거함수 (trim , ltrim , rtrim)

2-8)문자치환함수

2-9)round , truncate (반올림 , 버림)

2-10)ceil ,floor (근사함수)

2-11)mod , power ( 나머지 , 제곱)

3) 날짜함수

3-1) 현재날짜

3-2) 날짜 사이 일수 (datediff)

3-3 ) 날짜 요소 반환

3-4)date_add (더하기) , date_sub(뺴기)

3-5) 포맷팅 date_format , str_to_date

4) 예제

5) 정리

1)@@집합연산자 @@

1-1) UNION(합집합)

합집합 : union , union all
(주의 : 두개 select문의 갯수와 요소가 같아야함
첫번째 select구문의 결과로 나옴 )
union : 합집합 ,
union all : 두개 쿼리 문장의 결과를 합해 조회.(중복제거안됨)

//두개의쿼리를 모두 만족 -> 중복출력

예시

# 교수테이블에서 교수이름 학과코드 급여 연봉 조회하기
#보너스가 있는경우 급여 * 12 + bonus
#보너스가없는 경우 급여 *12
SELECT NAME , deptno ,salary,bonus, salary *12+bonus FROM professor
WHERE bonus IS NOT NULL
UNION  #(합집합)
SELECT NAME , deptno ,salary, bonus,salary *12 FROM professor
WHERE bonus IS  NULL;

-- 전공 1학과가 202학과이거나 , 전공2학과가 101학과인 학생의
-- 학번 이름 전공1 전공2 조회

SELECT studno , NAME , major1 , major2 
FROM student
WHERE major1 = 202 OR major2 = 101;

#union을 사용하는방법
SELECT studno , NAME , major1 , major2 
FROM student
WHERE major1 = 202 
UNION  #union all 사용시 위 아래의 조건 만족하는 학생이
					# 2번 출력
SELECT studno , NAME , major1 , major2 
FROM student
WHERE major2 = 101;


/*
	교수 중 급여가 450 이상인 경우 5%인상예정
	450 미만인경우 10% 인상 예정
	교수번호 교수명 현재급여 인상예정급여 조회
	인상예정급여가 큰순으로 조회
*/

SELECT NO , NAME , salary , salary*1.05 '인상예정급여'
FROM professor
WHERE salary >=450
UNION ALL #둘은 조건이 겹칠수가없으므로 Union all을해줘도 중복X
SELECT NO , NAME , salary , salary*1.1 '인상예정급여'
FROM professor
WHERE salary <450
ORDER BY '인상예정급여' DESC;

1-2) intersect (교집합)

교집합 : intersect
and 조건 연산자를 이용하는 경우가많음

-- 학생의 성이 김씨 학생 중 이름의 끝자가 훈인 학생의
# 이름 전공1코드 조회.
SELECT NAME , major1
FROM student
WHERE NAME LIKE'김%' AND NAME LIKE'%훈';

SELECT NAME , major1
FROM student
WHERE NAME LIKE'김%'
intersect
SELECT NAME , major1
FROM student
WHERE NAME LIKE'%훈'; 
# 두개의 쿼리가 중복되는 경우 출력

/*
	전공1학과가 202 , 전공2학과가 101인 학생의 학번 이름 전공1 전공2 조회
*/

#end 사용 (성능은 이게좋다 코드도 적고)
SELECT studno , NAME , major1 , major2
FROM student
WHERE major1 = 202 AND major2=101;

#intersect사용
SELECT studno , NAME , major1 , major2
FROM student
WHERE major1 = 202
intersect
SELECT studno , NAME , major1 , major2
FROM student
WHERE  major2=101;

2) @ 함수@

# 문자관련 단일행 함수

단일행 함수 : 하나의 레코드에서만 사용되는 함수
그룹함수 : 여러행에 관련된 기능을 처리하는 함수
group by , having 구문과 관련있는 함수

2-1) 대소문자 관련 함수 (Upper , Lower)

-- 학생의 전공1학과가 101인 학생의 이름 , id,대문자id,소문자id출력
SELECT  name ,id , UPPER(id) , LOWER(id)
FROM student 
WHERE major1 = 101;

2-2) 문자열 길이 함수 : Length , char_Length

-- length : 저장된 바이트 수 . 오라클(lengthb)
-- char_length : 문자열의 길이 . 오라클(length)

영문자 , 숫자의student 경우 : 바이트수 == 문자열
한글의 경우 : 문자열의 길이 *3 == 바이트 수 길이
한글을 저장하는 컬럼의 varchar자료형의크기 : 한글글자수*3

-- 학생의 이름,아이디,이름글자 수 , 이름 바이트수
--  ,id글자 수 ,id바이트수 조회
SELECT NAME , id , CHAR_LENGTH(NAME) , 
LENGTH(NAME),char_length(id),LENGTH(id)
FROM student ;

SELECT LENGTH("가나다라마바사아"),LENGTH("1234567890"),
LENGTH("ABCDEFGHI"); #24 , 10 , 9

2-3)문자열 연결 함수 : concat

-- 교수의 이름과 직급 연결해 조회
SELECT concat(NAME , POSITION,'님') 교수  FROM professor;

-- 학생정보를 홍길동 1학년 150cm 50kg 형태로 학생정보출력
-- 학년 순으로 정렬
SELECT 
CONCAT(NAME,' ',grade ,'학년',height, 'cm ' , weight , 'kg')'학생'

FROM student
ORDER BY grade;

2-4) 부분문자열 (substr,left , right)

부분 문자열 : substr
-- substr (컬럼명 / 문자열 , 시작인덱스 , 글자수)
-- substr( 컬럼명 / 문자열 , 시작인덱스) : 시작인덱스부터 문자열끝까지
-- left (컬럼명 / 문자열 , 글자수) : 왼쪽부터 글자수만큼 부분문자열로리턴
-- right( 컬럼명 / 문자열 , 글자수) : 오른쪽부터 글자수만큼 부분문자열로리턴

# 학생의 이름 2자만 조회
SELECT NAME , LEFT(NAME,2) , RIGHT(NAME,2) , SUBSTR(NAME,1,2),SUBSTR(NAME,2)
FROM student;

-- 학생의 이름과 주민번호 기준 생일출력
SELECT NAME , jumin , LEFT(jumin,6) , SUBSTR(jumin,1,6)
FROM student;

-- 학생 중 생일이 3월인 학생의 이름 생년월일 조회하기
-- 생일은 주민번호기준

SELECT NAME , LEFT(jumin,6)
FROM student
WHERE SUBSTR(jumin,3,2) = 03;

-- 2.학생 이름 학년 생년월일 조회
-- 단 생년월일은 주민번호 기준 , 형식을 99년99월99일로
-- 월 기준으로 정렬해 출력

SELECT NAME , grade ,
 CONCAT(LEFT(jumin,2),'년',SUBSTR(jumin,3,2),'월',SUBSTR(jumin,5,2),'일')'생년월일'
FROM student
ORDER BY SUBSTR(jumin,3,2);
#concat으로 묶어서 출력

2-5) instr (문자위치 인덱스 리턴)

-- 문자열에서 문자의 위치인덱스 리턴 : instr
-- instr(컬럼 |문자열 , 문자): 컬럼에서 문자의 위치인덱스값 리턴

-- 학생의 이름 ,전화번호 , 전화번호 중 ')' 의 위치값 출력
SELECT NAME , tel ,INSTR(tel,')') FROM student;

-- 문제 : 학생의 이름 , 전화번호 , 전화지역번호 출력하기
# 왼쪽에서 tel의 ')' 전까지 
# left(tel,instr(tel,')')-1) 출력해주면 됨
SELECT NAME ,tel,LEFT(tel,instr(tel,')')-1) '지역번호'
FROM student;

-- 학생 전화번호 모두 출력
SELECT DISTINCT LEFT(tel,instr(tel,')')-1) FROM student;

-- 문제
-- 교수테이블에서 교수이름 , url , hompage 조회
-- hompage : url 정보에서 http:// 이후의 문자열

SELECT p.`name`, p.url , SUBSTR(URL,INSTR(URL,'/')+2)'homepage'
FROM professor p;

SELECT p.`name`, p.url , SUBSTR(URL,CHAR_LENGTH('http://')+1) 'homepage'
FROM professor p;

2-6) 문자추가함수 ( lpad , rpad)

-- lpad (컬럼 , 전체자리수 , 추가문자) :
# 컬럼을 전체자리수 출력 시 빈자리만큼 좌측에 추가문자

-- rpad (컬럼 , 전체자리수 , 추가문자) :
# 컬럼을 전체자리수 출력 시 빈자리만큼 우측에 추가문자

-- 학생의 학번 이름 조회
-- 학번은 10자리로 빈자리는 오른쪽에 *
-- 이름은 10자리로 빈자리는 왼쪽에 # 
SELECT RPAD(studno , 10 , '*'),LPAD(NAME,10,'#')
FROM student;

-- 문제 : 교수테이블에서 이름과 직급 출력하기
-- 직급은 12자로 출력하고 , 빈자리 존재시 우측에 * 로 채움
# 직급의 자릿수가 모두 다를것임 3자리라면 우측에9개의*
# 4자리라면 우측에 8개의 * .....

SELECT NAME , RPAD(POSITION,12,'*')'직급'
FROM professor;

2-7) 문자제거 함수 : trim , rtrim , ltrim

-- trim(문자열) : 양쪽의 공백 제거
-- rtirm(문자열) : 오른쪽 공백 제거
-- rtirm(문자열) : 오른쪽 공백 제거

-- trim({LEADING '문자' |TRAILING '문자' |BOTH '문자'} from 문자열)
-- leading : 왼쪽문자제거
-- trailing :오른쪽문자제거
-- both : 양쪽문자 제거

select CONCAT('***','     양쪽공백 제거X     ','***');
SELECT CONCAT('***',TRIM('   양쪽공백 제거     '),'***');
SELECT CONCAT('***',RTRIM('   오른쪽공백 제거     '),'***');
SELECT CONCAT('***',LTRIM('   외쪽공백 제거     '),'***');
SELECT TRIM(BOTH'0' FROM '00011020300000');#양쪽의 0 제거
SELECT TRIM(LEADING'0' FROM'00001122400000');# 왼쪽의0 제거
SELECT TRIM(TRAILING'0' FROM'00001122400000');# 오른쪽의0 제거


-- 교수테이블에서 교수이름 url homepage 출력
-- homepage 는 url에서 http://이후의 문자열
SELECT NAME , URL  , TRIM(LEADING 'http://' FROM URL) homepage
FROM professor;

url로부터 왼쪽의 http:// 문자 자체를 제거해 출력했음

TRIM(LEADING ' http://' from url)

2-8) 문자치환함수 ( replace)

-- replace(컬럼명 , '문자1','문자2') : 컬럼의 값이 문자1을 문자2로치환

-- 학생의 이름 중 성만 # 으로 변경

SELECT name , REPLACE(NAME , SUBSTR(name,1,1),'#') '#이름'
FROM student;

-- 학생의 이름중 2번째 문자를 #으로 변경해 출력
SELECT name , REPLACE(NAME , SUBSTR(NAME,2,1),'#')'x#x'
FROM student;

-- 101학과 학생의 이름 , 주민번호 출력
-- 주민번호는 뒤에 6자리 *

SELECT NAME , REPLACE(jumin, right(jumin,6),'*******')
#jumin의 오른쪽부터 6자리를 ******로바꿈
FROM student s
WHERE s.major1=101; #밑에출력이있음

SELECT NAME , RPAD(SUBSTR(jumin,1,7),13,'*')
FROM student
WHERE major1=101;

SELECT NAME , concat(left(jumin,7),'******')
FROM student
WHERE major1=101;

2-9) round , truncate (반올림 , 버림)

-- 반올림 함수 : round
# round(숫자) : 소수점이하 첫번째자리에서 반올림해 정수형출력
-- round(숫자 ,자릿수) : 소수점 기준 10의자리 -1 소수점이하는 1 2, 3..

-- 버림 함수 : truncate
-- truncate (숫자 , 자릿수) : 소수점을 기준으로 10의자리 -1 소수점이하는 1, 2, 3

SELECT ROUND(12.3456,-1) r1 ,ROUND(12.3456) r2 ,round(12.3456,0) r3,
ROUND(12.3456,1) r4  ,ROUND(12.3456,2) r5  ,ROUND(12.3456,3) r6;

SELECT TRUNCATE(12.3456,-1) r1 ,TRUNCATE(12.3456,0) r2 ,TRUNCATE(12.3456,0) r3,
TRUNCATE(12.3456,1) r4  ,TRUNCATE(12.3456,2) r5  ,TRUNCATE(12.3456,3) r6;

# 문제 : score 테이블에서 학생의 학번 국어 수학 영어, 총점,평균조회
-- 평균은 소수점이하 2자리로 반올림

SELECT studno , kor , math ,eng , kor+math+eng 총점
,ROUND(kor+math+eng/3 , 2)평균
FROM score 
ORDER BY 총점 DESC;

# 교수의 급여 15% 인상해 정수로출력
--교수의 이름 , 정수로출력된 반올림 예상급여 , 절삭된 예상급여출력
SELECT NAME , salary , ROUND(salary*1.15) 반올림 
, TRUNCATE(salary*1.15,0) 버림 
FROM professor;  #이걸출력할게

2-10) ceil ,floor (근사함수)

-- 근사함수 : 가장 가까운 정수
-- ceil : 큰 근사정수
-- floor : 작은 근사정수

SELECT CEIL(12.3456),FLOOR(12.3456)
		,CEIL(-12.3456),FLOOR(-12.3456); 
		# 13 , 12 , -12, -13

2-11) mod , power

-- 나머지 함수 : mod (% 로도 가능)
-- 제곱함수 : power

\
SELECT 21/8 , 21%8,  MOD(21,8) , POWER(3,3);

3) 날짜함수

-- 날짜 관련 함수

3-1) 현재날짜

now() : 날짜와 시간 리턴
curdate() , current_date , current_date() : 오늘날짜 리턴

SELECT NOW() , CURDATE() , CURRENT_DATE , CURRENT_DATE();

3-2)날짜 사이의 일수 : datediff()

-- datediff(날짜1,날짜2) : 날짜1에서 날짜2의 일수 리턴

(1에서 2를 뺴는거임 (1이 더 미래의 시간이여야함))

SELECT NOW(),'2025-01-01',DATEDIFF(NOW(),'2025-01-01'),
		DATEDIFF('2025-12-31','2025-01-01');

-- 학생의  이름 생일 생일부터 현재까지의 일수 조회하기
SELECT NAME , birthday , DATEDIFF(NOW(),birthday) FROM student;

-- 학생의  이름 생일 생일부터 현재까지의 일수/365 :나이  조회하기
-- 나이는 절삭해 정수로출력
SELECT NAME , birthday , TRUNCATE(DATEDIFF(NOW(),birthday)/365,0)
FROM student;

-- 문제1 
-- 학생의 이름 , 생일 , 현재개월수 ,나이 출력
-- 개월수 :  반올림해 정수출력
-- 나이 : 일수 / 365 절삭해 정수출력

SELECT NAME,birthday,ROUND(DATEDIFF(NOW(),birthday)/30) 개월수,
			FLOOR(DATEDIFF(NOW(),birthday)/365) 나이
FROM student;

3-3) 날짜의 요소 반환

year(날짜) : 년도리턴
month(날짜) : 월 리턴
day(날짜) : 일 리턴
weekday(날짜) : 요일리턴 0:월 ~~6:일
dayofweek(날짜) : 요일 리턴 1:일 2:월 ~~7:토
week(날짜) : 일년 기준 몇번째 주
last_day(날짜) : 해당월의 마지막날짜

SELECT NAME , birthday ,  year(birthday) 생년,
		month(birthday) 생월 , day(birthday) 생일
FROM student;

SELECT WEEKDAY(NOW()),DAYOFWEEK(NOW()),WEEK(NOW()),LAST_DAY(NOW());

SELECT left(hiredate,INSTR(hiredate,'-')-1)
FROM professor; # 년 출력법2
#날짜는 2025-12-22 의 형식이라
#-앞에 까지만 출력시->년도만 출력!!! 

intstr(컬럼명,문자) : 문자의 최초인덱스반환

-- 문제
-- 교수이름 , 입사일(hiredate),입사년도 휴가보상일  , 올해의 휴가보상일 조회
-- 휴가보상일 : 입사월의 마지막일자 
SELECT `name` , hiredate , LAST_DAY(hiredate)'입사년 휴가보상일'
,LAST_DAY(NOW()) '올해 휴가보상일'
FROM professor ;

-- 문제
-- 교수 중 입사월이 1~3 월인 교수의 급여를 15% 인상예정
-- 교수이름 현재급여 인상예정급여 , 급여소급일 출력
-- 급여소급일 : 올해 입사월의 마지막일자
-- 인상 예정 급여 : 반올림해 정수로 출력
-- 인상예정 교수만 출력하기

SELECT NAME ,salary , round(salary*1.15,0) 인상예정급여, LAST_DAY(hiredate) 급여소급일  
FROM professor 
WHERE MONTH(hiredate)BETWEEN 1 AND 3
ORDER BY 인상예정급여 desc;

3-4) date_add , date_sub

date_add (날짜,옵션) : 날짜 이후
date_sub (날짜,옵션) : 날짜 이전

옵션
interval n day : n일
interval n hour : n 시간
interval n minute : n 분

-- 현재시간 기준 1년 이후의 날짜
SELECT NOW(),DATE_add(NOW(),INTERVAL 1 year)' 1년 후'; 
-- 현재시간 기준 1달 이후의 날짜
SELECT NOW(),DATE_add(NOW(),INTERVAL 1 month)' 1달 후 '; 

-- 현재시간 기준 1일 이후 날짜
SELECT NOW(),DATE_ADD(NOW(),INTERVAL 1 DAY) '1일후'; # 1일 이후!
SELECT NOW(),DATE_sub(NOW(),INTERVAL 1 DAY)' 1일전'; # 1일 이전!

-- 현재시간 기준 1시간 이후의 날짜
SELECT NOW(),DATE_add(NOW(),INTERVAL 1 HOUR)' 1시간 후 '; 
-- 현재시간 기준 1분 이후의 날짜
SELECT NOW(),DATE_add(NOW(),INTERVAL 1 MINUTE)' 1분후 '; 
-- 현재시간 기준 1초  이후의 날짜
SELECT NOW(),DATE_add(NOW(),INTERVAL 1 second)' 1초 후 ';

#문제 1
-- 교수번호 , 이름 , 입사일 , 정식입사일 조회
-- 정식입사일 : 입사일  3개월 후
SELECT NO , NAME , hiredate , DATE_ADD(hiredate,INTERVAL 3 MONTH) 정식입사일
FROM professor ;

#문제2
-- emp테이블에서 정식입사일은 입사일의 2개월이후 다음달 1일로함
-- 사원번호, 이름 , 입사일 , 정식입사일 출력
SELECT e.empno , e.ename , e.hiredate , 
date_Add(last_day(DATE_ADD(hiredate,INTERVAL 2 MONTH)),INTERVAL 1 DAY) 정식입사일
FROM emp e;
# 입사일에 2개월을 더해준후 date_add(hiredate,interval 2 month)사용
# 그 달의 마지막날짜를 구한다(last_day)
# 구한 마지막날자에 date_add(마지막날짜,interval 1 day)로 하루를 더해줌

문제2의 select문 결과

3-5) 날짜관련 변환함수 (포맷팅)

date_format : 날짜--> 형식화문자열
ex) %Y년%m월%d일%h:%i:%s
str_to_date : 형식화된 문자열 --> 날짜

형식화 문자열
%Y : 4자리 년도
%M : 영문 월
%m : 2자리 월
%d : 2자리 일자
%H , 0~23시
%h : 1+12시
%i : 분
%s : 초
%p : AM/PM
%W :요일
%a : 약자표시 요일

SELECT NOW(),DATE_FORMAT(NOW(), '%Y년%m월%d일%h:%i:%s %p %W') ;

-- 현재 년도 출력
SELECT YEAR(NOW())  년도1 , date_format(NOW(),'%Y');

-- 2025-12-31 일의 요일 출력
SELECT DATE_FORMAT('2025-12-31','%W');

-- 2025년12월25일의 요일
SELECT date_format(STR_TO_DATE('2025년12월25일','%Y년 %m월 %d일'),'%Y년 %m월 %d일 %W');
# 1. str-->date로 변환시켜줌(STR_TO_DATE,format형식)이용
#2. date_format(바뀐date , '%W')로 요일출력가능

-- 교수 명 , 직책 , 입사일 , 정식입사일 
-- 정식입사일 : 입사일의 3개월 후
-- 입사일 , 정식입사일을 yyyy년mm월dd일의 형식으로출력

SELECT NAME , POSITION , DATE_FORMAT(hiredate , '%Y년%m월%d일 요일:%a') 입사일,
	date_format(DATE_ADD(hiredate, INTERVAL 3 MONTH),'%Y년%m월%d일 요일:%a') 정식입사일
FROM professor;
#date_fomat을 통해 날짜를원하는 형식으로 바꿔서출력!!!
#				(%Y년%m월%d일 요일:%a)
# date_add로 기준날짜에 3개월을 더해줌과 동시에 
# date_format으로 형식을 맞춰줌

4) 예제문제들

-- 1. 교수테이블(professor)급여가 300 이상이면서 보너스(bonus)을 받거나 
--    급여가 450 이상인 교수 이름, 급여, 보너스을 출력하여라.
SELECT NAME , salary,bonus
FROM professor 
WHERE salary>=300 AND bonus IS NOT NULL
UNION
SELECT NAME , salary,bonus
FROM professor 
WHERE  salary>=450;

-- 
-- 2. 교수테이블에서 보너스가 없는 교수의 교수번호, 이름, 급여,  10% 인상급여를 출력하고
--    보너스가 있는 교수는 의 급여는 인상되지 않도록 인상 예상급여를 출력하기
--     단 인상급여의 내림차순으로 정렬하기
SELECT p.no , p.`name` , p.salary ,bonus, (p.salary*1.1) '10%인상급여',(p.salary*0) 인상예상급여
FROM professor p 
WHERE bonus IS NULL
UNION
SELECT p.no , p.`name` , p.salary , bonus,(p.salary*0)'10%인상급여',(p.salary*1.1) 인상예상급여
FROM professor p 
WHERE bonus IS not NULL
ORDER BY'10%인상급여' DESC;

-- 
-- 3. 학생의 생일이 97년 이후인 학생의 학번, 이름, 생일을 출력하기
SELECT studno , NAME , birthday
FROM student s
WHERE YEAR(s.birthday)>=1997;
# birthday의타입은 DATE이기 떄문에 year로 년도를 뽑고 1997과 비교

SELECT studno , NAME , birthday 
FROM student s
WHERE SUBSTR(birthday,1,4) >= 1997;  
#생년월일은 4자리니까 4자리를 substr로 가져온 후 1997보다크거나 작은것만 뽑는다.

-- 
-- 4. 학생 테이블을 읽어 
--    '학생이름의 생일은 yyyy-mm-dd  입니다. 축하합니다' 형태로 출력하기
SELECT CONCAT(NAME,'의 생일은',birthday,'입니다 축하합니다')
FROM student;
-- 5. 학생 테이블에서 학생 이름과키,몸무게, 표준체중을 출력하기
--    표준 체중은 키에서 100을 뺀 값에 0.9를 곱한 값이다.
SELECT  NAME ,height , weight , ((height -100) *0.9) 표준체중
FROM  student;
-- 
-- 6. 101 번 학과 학생 중에서 3학년 이상인 학생의  이름, 아이디, 학년을 출력하기
SELECT NAME , id , grade
FROM student
WHERE major1=101
INTERSECT
SELECT NAME , id , grade
FROM student
WHERE 3 <= grade;


-- 7. EMP 테이블에서 급여가 600에서 700 사이인 사원의  성명, 업무(job), 급여(salary), 부서번호(deptno)를 출력하여라.
SELECT e.ename  , e.job ,e.salary  , e.deptno
FROM emp e
WHERE e.salary BETWEEN 600 AND 700
ORDER BY e.salary DESC;


-- 8. EMP테이블에서 사원번호(empno)가 2001, 2005, 2008 인 사원의 사원번호, 성명, 업무(job), 급여, 입사일자(hiredate)를 출력하여라.
SELECT empno , e.ename  , e.job ,e.salary  , e.hiredate 
FROM emp e
WHERE empno=2001 or empno=2005 or empno = 2008;


-- 9. EMP 테이블에서 사원이름의 첫 글자가 ‘주’인 사원의 이름, 급여를 조회하라.
SELECT ename , salary
FROM emp
WHERE ename LIKE('주%');

-- 10. EMP 테이블에서 급여가 800 이상이고, 담당업무(JOB)이 차장인 
--    사원의 사원번호, 성명, 담당업무, 급여, 입사일자, 부서번호를 출력하여라.
	SELECT empno , ename , job , salary , hiredate , deptno
	FROM emp
	WHERE salary > 800 AND job='차장';

-- 11. 교수테이블에서 이메일이 있는 교수의 이름, 직책,   email, emailid 를  출력하기
--     emailid는 email의 @ 앞의 문자를 의미한다
SELECT NAME , POSITION , email , SUBSTR(email,1,INSTR(email,'@')-1)emailid
				# substr로 부분문자열을 반환시도
				#instr(email,'@')로 @의 위치를 알아낸다
				# 1번(처음) 부터 @가 있는 번호의 앞까지 뽑아온다
FROM professor 
WHERE email IS NOT NULL;

-- 12. 101번 학과 학생의 이름 중 두번째 글자만 '#'으로 치환하여 출력하기
SELECT REPLACE(NAME,SUBSTR(NAME,2,1),'#')
# replace(컬럼명 , 문자1 , 문자2) :문자1-->문자2 치환
FROM student
WHERE major1 = 101;


-- 13. 102번 학과 학생의 이름과 전화번호, 전화번호의 국번부분만#으로 치환하여 출력하기(단 국번은 3자리로 간주함.)
SELECT NAME , tel , REPLACE(tel,SUBSTR(tel,1,3),'###') 번호
FROM student
WHERE major1 = 102; #이거는 국번부분이2자리일경우 ) 이 사라짐


SELECT NAME , tel , CONCAT('#',SUBSTR(tel , INSTR(tel,')'))) 번호
						#  @@concat으로 붙이는 법@@ 
						#  # 을 앞에 넣고 , substr(tel,instr(tel, ')' ) )를 이용해
						#    )을 포함한 뒤의 문자만 반환함. ex. 02)33-22 --> #)33-22
						# 이렇게하면 국번이2자리여도 똑같이 # 으로가려짐
FROM student
WHERE major1 = 102;


-- 14. 교수테이블의의  email 주소의 @다음의 3자리를 ###으로 치환하여 출력하기  교수의 이름, email, #mail을 출력하기
SELECT NAME , email , REPLACE(email,SUBSTR(email,INSTR(email,'@')+1,3),'###')'#email'
							# 1. instr(email,'@')+1 로 @의 다음 번호를 가져옴
							# 2. substr( SUBSTR(email,INSTR(email,'@')+1,3))로 @이후의 3개의문자 반환
							# 3 .REPLACE(email,SUBSTR(email,INSTR(email,'@')+1,3),'###') 로 @이후3개의문자를 ###으로치환
FROM professor 
WHERE email IS NOT NULL;

-- 15. 교수테이블의  email 주소의 @앞의 3자리를 ###으로 치환하여 출력하기  교수의 이름, email, #mail을 출력하기
SELECT NAME , email , CONCAT('###',SUBSTR(email,INSTR(email,'@')))
FROM professor;
 
-- 16. 사원테이블에서 사원이름에 *를 왼쪽에 채운  6자리수 이름과, 업무와 급여를 출력한다.
SELECT LPAD(e.ename, 6, '*') ,  e.job , e.salary
FROM emp e;


-- 17. 교수들의 이름과 근무 개월 수를 출력하기
--     근무개월수는 현재 일을 기준으로  일자를 계산하여 30으로 나눈 후 개월 수는 절삭하여 정수로 출력하기
--     근무 개월 순으로 정렬하여 출력하기. 
SELECT NAME , floor(DATEDIFF(NOW(),hiredate)/30) 근무개월
		# datediff(날짜1 , 날짜2) : 날짜1에서날짜2를 뺀 일수
FROM professor 
ORDER BY 근무개월;
 
 
-- 18. 사용자 아이디에서 문자열의 길이가 7이상인   학생의 이름과  사용자 아이디를 출력 하여라
SELECT NAME , id ,  CHAR_LENGTH(id) id길이
FROM student
WHERE CHAR_LENGTH(id)>=7
ORDER BY id길이 ; 


-- 19. 교수테이블에서 이름과, 교수가 사용하는 email  서버의 이름을    출력하라. 
# 이메일 서버는 @이후의 문자를 말한다. 아이디의 이름도 출력해보자 @ 이전의 문자!
SELECT NAME , SUBSTR(email,INSTR(email,'@')+1) email서버 ,SUBSTR(email,1,INSTR(email,'@')-1) emaiId
FROM professor;





-- 20. 101번학과, 201번, 301번 학과 교수의 이름과  id를 출력하는데, id는 오른쪽을 $로 채운 후 
--       20자리로 출력하고  동일한 학과의 학생의   이름과 id를 출력하는데,  학생의 id는 왼쪽#으로 채운 후 20자리로 출력하라.
SELECT deptno,NAME , Rpad(id,20,'$')
 FROM professor
 WHERE deptno = 101 or deptno = 201 OR deptno = 301
 union
 SELECT major1,NAME , LPAD(id,20,'#')
 FROM student;


-- 21. 2025년 1월 10일 부터 2025년 5월 20일까지 개월수를 반올림해서 정수 출력하기
SELECT round(DATEDIFF('2025-05-20','2025-01-10')/30) 개월수 ;
# 2025-05-20 - 2025-01-10이라는 소리


-- 22. EMP 테이블에서 10번 부서 직원의 현재까지의 이름, 입사일, 근무 월수를 계산하여   출력하기.  
--     근무월수 : 근무일수/30 반올림하여 정수로 출력하기
SELECT e.ename , e.hiredate , round(DATEDIFF(NOW(),e.hiredate)/30) 근무월수
FROM emp e
WHERE e.deptno=10;

5) 정리

집합연산자
union : 합집합(중복X)
union all : (중복을 포함),2개의쿼리결과를 연결하여 조회
---> 2개의 select에서 조회되는컬럼의 수 같아야함
intersect : 교집합,  and 조건문으로 대부분가능

2.함수 : 단일행함수 : 하나의 레코드에서만 처리  where조건문에서 사용
그룹  함수 : 여러개의 레코드에서 처리되는 함수 . having조건문

3. 문자열 관련 함수
- 대소문자구분 : upper , lower
-문자열의 길이 : length(바이트 수) , char_length(문자열 길이)
- 부분 문자열 : substr(문자열 , 시작인덱스,[갯수]) , left(문자열,갯수) , right(문자열,갯수)
- 문자연결 함수 : concat
-문자의 위치값 : instr(문자열,문자) -> 문자열에서 문자의위치 인덱스 리턴 , 인덱스는 1부터시작
-문자 추가 : , rpad    or   lpad(문자열 , 전체자릿수 , 채울문자)
-문자제거 : trim (문자열) 양쪽공백제거
ltrim(문자열) 왼쪽공백제거
rtrim (문자열) 오른쪽공백 제거
trim(leading | trailing | both 제거할문자 from 문자열) : 지정문자제거
왼쪽   |   오른쪽 | 양쪽

-문자치환 : replace(문자열 , 문자1 , 문자2) : 문자열의 문자1을 문자2로치환
-그룹의 위치 : find_in_set(문자 , 문자열) : ,를 가진 문자열에서 문자가 몇번쨰위치인지 리턴

4.숫자관련 함수
-반올림 : round(숫자 , [소수점이하 자릿수])
- 버림 : truncate(숫자, 소숫점이하자릿수)
-나머지 : mod , % 연산가능
-제곱 : power(숫자1,숫자2) : 1의2제곱
-근사함수 : ceil : 큰 근사정수
floor : 작은 근사정수

5.날짜관련 함수
-현재일시 : now()
-현재일자 : curdate() ,current_date , current_date()
-년 월 일 : year month day ,
weekday(요일 (0)월~일) , dayofweek((1)일~토) , last_day(마지막날)
-이전/이후 : date_dadd/date_sub(날짜,interval 숫자[year|month|day|hour|minute|second])
-날짜 변환함수 : date_format 날짜 ->문자열
str_to_date 문자열 --> 날짜
%Y%m%d %h%i%s %W
년월일 시분초 요일

'부트캠프(DB)' 카테고리의 다른 글

부트캠프 27일차 (DDL,DML,VIEW,사용자관리) (0)	2025.03.11
부트캠프 26일차(서브쿼리 , DDL) (0)	2025.03.10
부트캠프 25일차 (join (0)	2025.03.07
부트캠프24일차 ( 조건함수 , 그룹함수) (1)	2025.03.06
자바/스프링 부트캠프 22일차( 시작,SELECT, WHERE ) (0)	2025.03.04

현재글부트캠프23일차 (집합연산자 함수 날짜)

web동곤

백엔드위주

스코프, `, 반복문, java, 기본, 인텔리제이, 유익, 이클립스, Method, Easy, Eclipse, 초보, Spring, 기초, 코딩, 꿀팁, 유익한, 형변환, 메서드, 자바,

Today :
Yesterday :

« 2025/04 »
일	월	화	수	목	금	토
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30