[MySQL] MySQL 함수

20200428 수업

 

1. 문자 관련 함수 

- ASCII(문자) - 문자의 아스키 코드값 리턴
- CONCAT('문자열1','문자열2','문자열3'...) - 문자열(혹은 컬럼)들을 이어준다
- INSERT('문자열','시작위치','길이','새로운문자열') - 문자열의 시작위치부터 길이만큼 새로운 문자열로 대치 
- REPLACE('문자열','기존문자열','바뀔문자열') - 문자열 중 기존문자열을 바뀔 문자열로 바꾼다
- INSTR('문자열','찾는문자열') - 문자열 중 찾는 문자열의 위치값을 출력 

= 문자열 일부분 가져오기(LEFT, RIGHT, MID) ==
- LEFT('문자열',개수) - 문자열 중 왼쪽에서 개수만큼을 추출.
- RIGHT('문자열',개수) - 문자열 중 오른쪽에서 개수만큼을 추출
- MID('문자열',시작위치,개수) - 문자열 중 시작위치부터 개수만큼 출력 (SUBSTR or SUBSTRING과 동일)
 - SUBSTRING('문자열',시작위치,개수) - 문자열 중 시작위치부터 개수만큼 출력 

2. 숫자 관련 함수 

- ABS(숫자) - 절대값 출력   
- CEILING(숫자) - 값보다 큰 정수 중 가장 작은 수(올림)  
- FLOOR(숫자) - 소수점 버림[실수를 무조건 버림(음수일 경우는 제외)]
- ROUND(숫자,자릿수) - 숫자를 소수점 이하 자릿수에서 반올림.(자릿수는 양수,0,음수를 갖을 수 있다.) 
- TRUNCATE(숫자,자릿수) - 소수점 자리수 버림
- POW(X,Y) or POWER(X,Y) - X의 Y승 
- MOD (분자, 분모) - 분자를 분모로 나눈 나머지를 구한다.(연산자 %와 같음) 
- GREATEST(숫자1,숫자2,숫자3...) - 주어진 수 중 제일 큰 수 리턴
- LEAST(숫자1,숫자2,숫자3...) - 주어진 수 중 제일 작은 수 리턴
- INTERVAL(a,b,c,d.....) - a(숫자)의 위치 반환

3. 날짜 관련 함수 

- NOW() or SYSDATE() or CURRENT_TIMESTAMP() - 현재 날짜와 시간 출력 
- CURDATE() or CURRENT_DATE() -현재 날짜 출력 
- CURTIME() or CURRENT_TIME() -현재 시간 출력 
- DATE_ADD(날짜,INTERVAL 기준값) -날짜에서 기준값 만큼 더한다
- DATE_SUB(날짜,INTERVAL 기준값) -날짜에서 기준값 만큼 뺸다
- YEAR(날짜) -날짜의 연도 출력
- MONTH(날짜) -날짜의 월 출력
- MONTHNAME(날짜) -날짜의 월을 영어로 출력
- DAYNAME(날짜) -날짜의 요일일 영어로 출력
- DAYOFMONTH(날짜) -날짜의 월별 일자 출력
- DAYOFWEEK(날짜) -날짜의 주별 일자 출력(월요일(0),화요일(1)...일요일(6)) 
- WEEKDAY(날짜) -날짜의 주별 일자 출력(월요일(0),화요일(1)...일요일(6)) 
- DAYOFYEAR(날짜) -일년을 기준으로 한 날짜까지의 날 수
- WEEK(날짜) -일년 중 몇 번쨰 주
- FROM_DAYS(날 수) --00년 00월 00일부터 날 수 만큼 경과한 날의 날짜 출력
- TO_DAYS(날짜) --00 년 00 월 00일 부터 날짜까지의 일자 수 출력
- DATE_FORMAT(날짜,'형식') : 날짜를 형식에 맞게 출력

 

-- 이틀
-- 1. 문자관련 함수 : concat(), substr() 
SELECT concat(ename, 'is a ', job) Message FROM emp;
SELECT ename, SUBSTR(hiredate, 1,4) 년도 FROM emp;
SELECT ename, SUBSTR(hiredate, 6,2) 월 FROM emp;
SELECT ename, SUBSTR(hiredate, 9,2) 일 FROM emp;
select hiredate from emp;

SELECT ename, year(hiredate) 년도, month(hiredate) 월 FROM emp;
select hiredate from emp;

-- 2. 숫자 관련 함수 : abs(), mod(분자, 분모), round(), ceiling(), floor(), truncate()
-- BLAKE와 SMITH의 월급의 차이를 검색
SELECT abs((SELECT sal FROM emp WHERE ename='SMITH')-(SELECT sal FROM emp WHERE ename='BLAKE'))
월급차이
FROM emp;

-- 사원번호가 홀수인 사원의 정보를 검색... mod()
SELECT empno, ename FROM emp WHERE mod(empno, 2)=1;

-- 업무가 salesman인 모든 사원에 대해서 comm에 대한 급여 비율의 나머지를 검색하세요
SELECT ename, sal, comm, MOD(sal, comm) FROM emp
WHERE job='SALESMAN';

-- round(), ceiling(), floor(), truncate()
SELECT ROUND(45.923,2); -- 소수점 두번째 자리수까지 나타내라는 게 ,2임. 소수 셋째짜리에서 반올림.
SELECT ROUND(45.923); -- 자리수를 지정하지 않으면 정수 자리만 보여주겠다. 소수 첫째자리에서 반올림함. 
SELECT ROUND(45.923,0); -- 위에꺼랑 결과값 똑같음.
SELECT ROUND(45.923,-1); -- 소수점을 중심으로 일의 자리에서 반올림. 

-- SELECT floor(45.923,2);  floor는 자리수를 지정하면 안 된다.
SELECT floor(45.923); 

SELECT truncate(45.923,2);
SELECT truncate(45.923,0);
-- SELECT truncate(45.923); -- 자릿수를 지정해줘야 한다

-- 3. 날짜 관련 함수 : NOW, SYSDATE, CURDATE, current_time
SELECT NOW();
SELECT SYSDATE();
SELECT CURDATE(); -- 제일 많이 씀. 오늘 연월일만 나옴.
SELECT current_time(); -- 오늘 날짜인데 시분초만 나옴.

-- 막내 오세훈이 오늘까지 살아온 일수를 구하세요. 변환함수.
-- 문자를 날짜로 변환해야줘야 함.

SELECT datediff(curdate(),str_to_date('1997-06-19', '%Y-%m-%d')) 살아온날;
-- datediff는 두 날짜 사이의 차이를 리턴해줌.

-- emp 테이블에서 모든 사원들이 지금까지 근무한 일수
SELECT ename, datediff(curdate(), hiredate) 근무일수 From emp;

-- emp 테이블에서 모든 사원들이 지금까지 근무한 weeks
SELECT ename, floor(datediff(curdate(), hiredate)/7) Weeks FRom emp;
-- 이렇게 쿼리문에는 메소드가 중첩되어 사용되는 경우가 많다...

-- 오늘부터 그이를 만난 지 100일째 되는 날 검색
SELECT DATE_ADD(curdate(), INTERVAL 100 DAY);

-- 날짜 - 날짜 = 일수
-- 날짜 + 숫자 = 날짜 | 날짜 - 숫자 = 날짜

-- 4. 그룹함수 : COUNT(), SUM(), AVG(), MAX(), MIN()

SELECT COUNT(*) FROM emp;
-- 데이터가 많을 때는 오류를 발생시킴.. 별로 안 좋음. 아래 -1이 더 정확도가 있음.
SELECT COUNT(-1) FROM emp; 
-- -1은 맨 마지막을 말함. 맨 마지막으로 가서 그 행을 리턴함.
SELECT COUNT(empno) FROM emp;
SELECT COUNT(MGR) FROM emp; -- NULL값은 그룹함수 연산에서 제외됨.

SELECT MIN(hiredate) 가장빠른입사일, MAX(hiredate) 가장최근입사일 FROM emp;

SELECT SUM(sal) 사원총급여 FROM emp;
SELECT ROUND(AVG(sal)) 사원총평균급여 FROM emp;

-- 업무가 MANAGER인 사원의 평균 급여를 검색
SELECT ROUND(AVG(sal)) FROM emp WHERE job='MANAGER';

-- 모든 사원의 보너스의 평균을 검색
SELECT ROUND(AVG(comm)) From emp; 
-- null값은 연산에서 제외되어서 사람수 잘못 나눔... 잘못된 결과.
SELECT ROUND(AVG(ifnull(comm,0))) FROM emp; 
-- comm이 null일 때 0으로 치환... 이렇게 해야 함.

-- 사원 테이블에서 모든 부서의 갯수를 출력
SELECT COUNT(distinct(DEPTNO)) FROM emp;
-- 부서번호 중복을 제외하고

-- 부서번호가 10번이거나 20번인 사원의 인원수
SELECT COUNT(DEPTNO) FROM emp WHERE deptno = 10 or deptno = 20;
-- 사원번호가 7369이거나 7521이거나 7876인 사원의 정보를 검색
select * from emp where empno = 7369 or empno = 7521 or empno = 7876;
-- or로 연결될 때 꼭 알아야 하는 in 연산자
SELECT COUNT(DEPTNO) FROM emp WHERE deptno IN(10, 20);
SELECT * from emp WHERE EMPNO IN(7369, 7521, 7876);

-- 부서번호가 10번이 아니고 20번이 아닌 사원의 인원수
SELECT COUNT(deptno) FROM emp WHERE deptno != 10 and deptno != 20;
SELECT COUNT(deptno) FROM emp WHERE deptno NOT IN(10, 20); -- 여집합.

-- 각 부서의 평균급여를 검색... AVG()
-- 결과가 의미없는데 잘못된 것. 원래는 에러가 나야 함.
-- 그룹함수(AVG)에 적용되지 않은 컬럼(deptno)이 select절에 나열되면 반드시 grounp by 절 뒤에 명시해줘야 한다.
SELECT deptno, AVG(sal) FROM emp; 
-- 바르게 고치기
SELECT deptno, AVG(sal) FROM emp GROUP BY deptno; 

-- 부서별 급여의 총합
SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;

-- 입사연도별 사원의 인원수
SELECT year(HIREDATE) 입사년도, COUNT(*) 인원수 FROM emp GROUP BY year(HIREDATE);
SELECT year(HIREDATE) 입사년도, COUNT(*) 인원수 FROM emp GROUP BY 입사년도;

-- 부서별 평균급여를 구하는데 20번 부서는 제외하고 구하세요
SELECT deptno, round(avg(sal)) 
from emp 
where deptno!=20 
group by deptno
order by deptno;

-- 부서별 평균급여가 2000달러 이상인 부서의 부서번호와 평균급여를 구하세요.

-- 에러남...
SELECT deptno, round(avg(sal)) 
from emp 
where round(avg(sal))>=2000
-- 그루핑 하기 전에 돌아가는 건 where절에서 씀. 매우 중요!
-- where절에서는 그룹함수 못씀. 
-- 그루핑 하기 전에 평균을 구하는 게 아니라 
-- 그루핑 하고 나서 평균 구하려면 그건 where절에서 안 씀. 그건 having절
-- 미리 조건을 줄 때는 where절이고, 그루핑하고 나서 다시 추리고 나서 하려면 having절
group by deptno
order by deptno;

SELECT deptno, round(avg(sal)) 
from emp 
group by deptno
having round(avg(sal))>=2000 -- 그루핑하고 나서 having절! 순서!
order by deptno;

-- 정리!!!
-- 1. where절은 그룹함수 못 쓴다.
-- 2. having절은 반드시 group by절 뒤에 와야 한다.


-- 부서별 평균급여가 2000달러 이상인 부서의 부서번호와 평균급여를 구하세요.
-- 단 10번 부서는 제외합니다.(이건 where절)
SELECT deptno, round(avg(sal)) 
from emp 
where deptno != 10
group by deptno
having round(avg(sal))>=2000
order by deptno;
-- 순서를 잘 봐둘 것! 실행순서!

/*
(10번 연습해서 자기 것으로 만들 것!)

use scott;

테이블 생성
create table myTest(
		id tinyint not null,
        name varchar(10),
        addr varchar(50),
        birthDate date);
        
2. 테이블에 값(행) 입력
INSERT INTO myTest (id, name, addr, birthDate) VALUES(1, 'A', 'A', curdate());
INSERT INTO myTest (id, name, addr, birthDate) VALUES(2, 'B', 'B', '2020-01-11');

3. 테이블 값(행) 삭제
DELETE FROM myTest; 모든 행이 삭제됨...
DELETE FROM myTest WHERE id=1;

4. 테이블 컬럼값 수정
UPDATE myTest SET 수정할 컬럼명 = 컬럼값, 수정할 컬럼명 = 컬럼값 Where id = 1;
*/

SELECT * from emp;

CREATE TABLE test01(
	num int auto_increment primary key, 
    -- 내가 값을 안 주더라도 한줄씩 넣을 때마다 넘버링이 자동 증가.
    -- 굉장히 많이 씀.
    name varchar(20),
    age tinyint,
    height decimal(5,1), -- 전체 자리는 5자리이고 소수점 1자리까지만 표현.
    birthday datetime,
    hiredate date);
desc test01;

-- 데이터 추가하기
INSERT INTO test01 (name, age, height, birthday, hiredate) VALUES('홍길동',21,179.5,NOW(),now());
SELECT * FROM test01;

INSERT INTO test01 (name, age, height, birthday, hiredate) VALUES('강호동',44,182.5,NOW(),now());
INSERT INTO test01 (name, age, height, birthday, hiredate) VALUES('이영자',45,169.5,NOW(),now());
INSERT INTO test01 (name, age, height, birthday, hiredate) VALUES('김준현',37,179.5,NOW(),now());
SELECT * FROM test01;

-- 컬럼명 변경 : ALTER
ALTER TABLE test01 change birthday birthdate datetime;
desc test01;

-- 컬럼 데이터 타입 변경하기 : ALTER
ALTER TABLE test01 change birthdate birthdate date;
desc test01;

select * from test01;

-- num이 3번인 데이터를 삭제
DELETE from test01 where num = 3;
select * from test01;

-- num이 2번인 데이터의 키와 나이를 수정
-- 기본키는 update의 대상이 아니다... 기본키를 제외한 나머지 컬럼들이 수정의 대상.
UPDATE test01 SET height = 183, age = 45 where num = 2;
select * from test01;

DELETE from test01;

INSERT INTO test01 (name, age, height, birthdate, hiredate) VALUES('강호동',44,182.5,NOW(),now());
INSERT INTO test01 (name, age, height, birthdate, hiredate) VALUES('강호동2',44,182.5,NOW(),now());

truncate table test01; 
select * from test01;

drop table test01;

/*

delete from test01; 데이터 다 지움. 그러나 테이블의 구조는 남김.
truncate table test01; 데이터 다 지움. 테이블 구조 남김.
delete는 한줄씩 지워서 속도가 느림. truncate은 데이터 전체를 들어버리는 것.
속도적인 측면에서 비교가 안됨.
delete는 1~4번 delete하면 추가했을 때 5번부터 넘버링함.
하지만 truncate은 전체를 들어버리므로 1번부터 넘버링함.
전산 처리할 때 delete보다 truncate을 써야 함.
둘은 auto-increment와 속도 차이.

drop table test01; 구조조차도 안 남기고 모든 걸 지워버림.


SQL
::
DML(Data Manupulation Language) - 데이터 조작 랭귀지(insert, update, delete) 
DDL(Data Define Language) - 데이터 구조를 정의하는 랭귀지.(create, drop, alter)

*/