5 mysql 함수 & 문제

1학년 신입생의 경우에만 입학금을 내고 재학생의 경우에는 입학금을 내지 않으므로 등록금 총액은 입학금 (fee_enter) + 수업료 (fee_price) 가 된다. 그러나 입학금이 null 인경우에는 가산을 할 수 없으므로

ifnull (fee_enter,0) 과 같이 ifnull 함수를 사용하여 null 값을 0으로 변환하여 가산을 할 수 있다.

등록금의 총액을 변경하여라

use haksa;

select * from fee;

update fee set fee_total = ifnull(fee_enter, 0) + fee_price;

납입금 총액은 등록금 총액 - 장학금 총액 이다. 납입금 총액을 변경하라

update fee set fee_pay = fee_total - ifnull(jang_total,0);

- 영수치함수

우편번호가 150- 051인 동이름과 지역전화번호를 출력하라

select post_dong, ddd from post where post_no = '150-051';

-숫자처리 함수

ROUND, TRUNCATE 함수

ROUND 함수는 숫자를 소수점 이하 자릿수에서 반올림 한다. 자릿수를 생략하면 소숫점이 5 이상일 때 반올림/자릿수를 지정하면 지정한 자리수에서 반올림한다.

TRUNCATE 함수

TRUNCATE 함수는 숫자를 소수점 이하 자릿수에서 버린다. n은 자릿수를 소숫점 이하로 정하며, 해당숫자가 자릿수보다 소숫점이 모자랄 경우 0값으로 대치한다.

만일 자릿수를 소숫점 이전으로 정하면 소숫점 이하는 버리고, n의 자릿수만큼 0 값으로 처리된다.

TRUNCATE 함수는 반드시 자릿수를 명시해 주어야 한다.

select round(123456.789,2);

소수점 아래 두 번째 자리에서 반올림한 결과이다

select truncate(123456.789,2);

소수점 아래 두번째 자리에서 삭제한 결과이다.

select round(12345678.901,-3);

소수점을 기준으로 왼쪽 3자리는 0으로 채워지고, 네번째 자리에 반올림한 결과다

select truncate(12345678.901,-3);

소수점 기준으로 왼쪽 3자리를 0으로 채워 절삭한 결과다

floor, ceil 함수

floor 함수는 위에서 다룬 trunc 함수와 유사하여  소수점 아래의 수를 무조건 절삭하여 정수 값을 반환하고, ceil 함수는 소수점 아래의 수는 무시하고 무조건 올림을 하여 정수를 반환한다.

두 함수는 뒤의 인수에 대해 무조건 정수를 반환 하므로 두번째 인수는 쓰지 않는다.

select floor(123456.789), ceil(123456.123);

mod 함수 :  첫번째 인수를 두번째 인수로 나누어 나머지를 반환한다.

select mod(10,3) , mod(5.8)

두번째 인수가 첫 번째 인수보다 크거나 0일 경우 결과 값은 첫번째 인수를 반환한다.

일반 수식 계산은 0으로 나눌 경우 에러가 출력되지만, mod 함수에서는 두 번째 인수가 0이라도 에러를 출력하지 않는다.

abs 함수 절대값을 구하는 식이다.

select abs(124),  abs(-124)

pow 함수 또는 power 함수

제곱의 값을 구하는 함수이며 소숫점이 있는 경우에도 실행이 된다.

select pow(2,4), pow(-2.5 ,2), pow(1.5, 6)

첫번째는 2의 4승을 구한 값이고, 두번째는 소숫점을 포함한 음수지만 양수로 처리되어 나오며, 세번째는 소수점을 포함한 1.5의 6승 값을 구한 것이다.

greatest, least 함수 가장 큰 값과 가장 작은 값을 구하는 함수

select greatest(1,2,3,4,5,6), least(1,2,3,4,5,6);

문제: 재학생들의 등록금액에 비례한 장학금의 비율은 몇 % 인가?

select fee_total ""등록금액"", jang_total ""장학금액"", ifnull(jang_total,0) / fee_total* 100 ""비율"" 

from fee;

select fee_total ""등록금액"", jang_total ""장학금액"", round(ifnull(jang_total,0) / fee_total* 100,2) ""비율"" 

from fee;"

스칼라 함수

스칼라 함수는 연산을 수행하기 위해서 사용된다.

스칼라 함수는 전달 인수를 사용하지 않거나 하나 이상의 전달 인수를 사용한다.

문자 처리 함수는 문자열 조작에 관한 함수들이 있다.

concat 함수는 두 문자열을 연결시켜 합쳐주고

substring 함수는 지정된 위치에서 지정된 길이만큼의 문자열을 추출한다.

length 함수는 문자열의 길이를 정수 값으로 반환한다.

instr 함수는 문자열에서 특정 문자의 위치를 반환하고 lpad 함수는 왼쪽에 지정된 문자를 지정된 길이 만큼 채워주고, rpad 함수는 오른쪽에 지정된 문자를 지정된 길이만큼 채워준다.

문자 처리 함수의 또 한 종류는 대소문자를 변화해주는 문자열 조작에 관한 함수들이 있다.

lower 함수는 문자열을 모두 소문자로 바꾸어 주고, upper 함수는 문자열을 모두 대문자로 바꾸어준다. intcap 함수는 문자열에 속한 각 단어별로 첫 글자를 대문자로, 나머지 부분은 소문자로 바꾸어 준다.

select concat('data','base');

select substring('korea',1,3);

select length('my life');

select instr('korea','e');

select LPAD('KOREA',15,'*');

select RPAD('KOREA',20,'*');

select lower('Korea');

select upper('Korea');

문제-학번이 20001001인 학생의 학번, 이름, 영문이름을 출력하라

- 단 영문이름은 대문자로 출력하라

"select * from student where stu_no = 20001001;

select stu_no, stu_name, upper(stu_ename) from student where stu_no = 20001001;

"

문제- 2학년 학생의 번호와 이름, 영문이름, 그리고 영문이름의 길이를 나타내어라

"

select stu_no, stu_ename, stu_name, length(stu_ename) '길이' from student where grade = 2;

"

select stu_no, stu_ename, stu_name, length(rtrim(stu_ename)) '길이' from student where grade = 2;

문제 - 영문이름의 길이가 정확히 12자인 각 학생의 번호와 영문이름을 출력하라

"

select stu_no, stu_name from student where length(rtrim(stu_ename)) = 12;

"

현주소의 우편번호가 550으로 시작하는 전남여수시에 거주하는 학생의 학번과 이름, 우변번호를 나타내어라

select student.stu_no, student.stu_name, student.post_no, post.post_address

from student, post

where student.post_no = post.post_no

and substring(post.post_no,1,3) = 550;

학번이 20001021 인 학생의 학번과 이름, 우편번호, 주소를 출력하라.

단, 주소출력에는 공백부분을 삭제하여 출력하고 concat 함수를 이용한다.

"

select student.stu_no, student.stu_name, student.post_no, concat(rtrim(post.post_address), rtrim(student.address))  

from student, post

where student.post_no = post.post_no 

and student.stu_no = 20001021;"

날짜및 시간처리

now() , sysdate() 현재 날짜와 시간을 반환

curdate() , current_date(); 현재 날짜를 반환 한다.

curtime(), current_time() 현재 시간을 반환 한다.

dayofmonth(date) 몇일인지를 리턴한다.

dayofweek(date), weekday(date) 숫자로 요일을 리턴 한다.

select now() , sysdate(); --  현재 날짜와 시간을 반환

select curdate() , current_date(); -- 현재 날짜를 반환 한다.

select curtime(), current_time()  -- 현재 시간을 반환 한다.

select dayofmonth(now());

select dayofweek(now()), weekday(now())  -- 숫자로 요일을 리턴 한다.

select dayofmonth(now()) , dayofweek(now());

select dayofyear(now()); 1년중 몇일이 지났는지 리턴 한다.

date_add 와 date_sub

date_add 함수는 날짜에서 기준값 만큼 더한 값이고, date_sub 함수는 날짜에서 기준값 만큼 뺀값을 나타 낸다.

기준값( year, month, day, hour, minute, second

"select date_add(now(), interval 3 day), date_sub(now(), interval 3 day);

"

year, month 함수

year 함수는 날짜의 연도를 출력하면 month는 날짜의 월을 출력한다.

"select year(now()), month(now());

"

date_format(날짜, '형식')

문제 - 교수테이블에서 교수코드, 교수이름, 임용일자를 년도(4자리), 월(영문) , 일(0이 포함된 날짜) 형식으로 출력하라.

select prof_code, prof_name,  date_format(Create_date,'%Y %M %d') from professor;

문제

- 영문이름의 길이가 정확히 11자인 학생의 학번과 영문이름을 출력하라

select stu_no, stu_ename from student where length(stu_ename)='11';

등록일자가 2006년 2월 18 일인 학생의 학번과 등록년도, 학기, 등록일자를 출력하라

select stu_no, fee_year, fee_date 

from fee 

where fee_date = '2006-02-18';

학적 테이블에서 학번과 이름, 주민등록번호를 출력하라 . 단 여학생은 제외시키고

"select stu_no, stu_name, id_num 

from student 

where substring(id_num,8,1) = 1; 

"