[필기정리]Day56-1 - select 명령문과 공통요소 : Literal, round, truncate, floor 등

# 리터럴(Literal)
리터럴(literal)은 고정되거나 변경할 수 없는 값으로 상수(constant)라고도 한다. 
리터럴은 select 명령문에서 행을 선택하는 조건에 사용되거나

insert 명령문에서 새로운 행에 값을 지정하기 위해서 사용된다.

각 리터럴은 테이블에서 열과 같은 특별한 자료형을 가지고 있다.

SQL은 다음과 같은 형식의 리터럴을 제공한다.

① 정수 리터럴
② 십진 리터럴
③ 부동 소수점 리터럴
④ 영수치 리터럴
⑤ 날짜 리터럴

① 정수 리터럴
    정수 리터럴(interger literal)은 전체가 정수이거나 소수점이 없는 정수로써

    양의 부호(+) 또는 음의 부호(-)를 가질 수 있다.

    ex) 38, +12, -3404, -16

② 십진 리터럴
    십진 리터럴(decimal literal)은 소수점을 가지고 있거나 가지지 않는 수로서

    필요하다면 양 또는 음의 부호를 사용할 수 있다.

    각 정수 리터럴은 십진 리터럴 정의에 따른다.

    ex) 49, 18.47, -3400, -16, 0.83459, -349
 

소수점 앞에 있는 숫자의 수를 정밀도(percision)라 하며 소수점 뒤에 있는 숫자의 수를 크기(scale)라 한다.

십진 리터럴 123.45는 5개의 정밀도와 2개의 크기를 가지고 있다.

MySQL의 Number 데이터형에서는 123.45 값을 처리하기 위해서는

Size를 5자리 이상, Scale을 2자리 이상으로 정의하여 사용해야 한다.

suchi float(5, 2) not null  // 123.45, 12.3 값이 입력 가능

③ 부동 소수점 리터럴
    부동 소수점 리터럴(floating point literal)은 지수를 가지고 있는 십진 리터럴이다. 

    ex) 49, 18.47, 4E-3

④ 영수치 리터럴
    영수치 리터럴(alphanumeric literal)은

    인용부호(')로 감싼 0 또는 그 이상의 영수치 문자로 구성된 문자열이다.

    여기서 인용부호는 리터럴에 포함되지 않고 문자열의 시작과 끝을 나타낸다.

영수치 리터럴 구성 시 사용되는 문자

모든 영문자의 소문자(a-z)

모든 영문자의 대문자(A-Z)

모든 숫자(0-9)

특수 기호(+, =, ?, _)

⑤ 날짜 리터럴
    날짜 리터럴(date literal)은 연도(year), 달(month), 일(day)로 구성하여 날짜를 표현한다.

    이와 같은 날짜를 구성하는 3개의 요소는 슬래쉬('/')로 구분되어 있는데,

    앞에 0은 생략할 수 있으며, 년도가 2자리 수치를 사용한다면 19가 두 자리 수치 앞에 생략된 것이다.

- 날짜 리터럴 한글 버전 입력 값, 영문 버전 입력 값

1980/12/08 80/12/08 8 December 1980 
1995/06/19 95/06/19 19 June 1995 
99/1/1 96/01/01 1 January 1999 

 

# 수식
   수식(expression)은 하나 이상의 연산자와 필요할 경우 괄호를 사용하여 하나의 값을 표현한다.

   이러한 값은 영수치, 수치 또는 날짜 자료형을 가질 수 있다.

   

   수식은 다양한 형식을 취할 수 있다.

   SQL에서는 수식을 사용하는데,

   예를 들면 select 명령문의 select와 where 절에서 수식이 사용된다.

   다음 예제는 1학년 신입생의 경우에만 입학금을 내고 재학생의 경우에는 입학금을 내지 않으므로

   등록금총액(fee_total)은 "입학금(fee_enter) + 수업료(fee_price)"가 된다.

 

   그러나, 입학금이 NULL인 경우에는 가산을 할 수 없으므로

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

Q1. 등록금 총액을 변경하라.
A.

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

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

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

 

Q3. 우편번호가 150-051인 동이름과 지역전화번호(동이름과 지역번호는 합쳐서)를 출력하라.

use haksa_database; 
select post_no, concat(post_dong, ddd) from post where post_no = '150-051';

# 숫자 처리 함수
- round, truncate 함수
  round 함수는 숫자를 소수점 이하 자릿수에서 반올림한다. (자릿수는 양수, 0, 음수를 갖을 수 있다.)

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

round(column_name or value, n)

  truncate함수는 숫자를 소수점 이하 자릿수에서 버린다.

  n은 자릿수를 소숫점 이하로 정하며,

  해당숫자가 자릿수보다 소숫점이 모자랄 경우 0값으로 대치한다.

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

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

truncate(column_name or value, n)

Q1. 

select round(123456.789, 2), truncate(123456.789, 2);

A. 결과

round(123456.789, 2) 
123456.79

truncate(123456.789, 2)
123456.78

앞의 예제 round(123456.789) 함수는 소수점 아래 두 번째 자리에서 반올림한 결과이고, 

truncate() 함수는 소수점 아래 두 번째 자리에서 절삭한 결과이다.

 

Q2.

select round(12345678.901, -3), truncate(12345678.901, -3);

A. 결과

round(12345678.901, -3)  
12346000

truncate(12345678.901, -3) 
12345000

위의 round(12345678.901) 함수는 소수점을 기준으로 왼쪽 3자리는 0을 채워지고,

네 번째 자리에 반올림한 결과이고,

truncate(12345689.901)함수는 소수점을 기준으로 왼쪽 3자리를 0으로 채워 절삭한 결과이다.

 

// Tip : truncate vs floor

- truncate : 특정 위치를 지정하여 버릴 수 있다.

- floor : 소수점 아래의 수를 무조건 절삭하여 버린다.

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

floor(column_name or value)

 

ceil 함수는 소수점 아래의 수는 무시하고 무조건 올림을 하여 정수를 반환한다. (올림)

ceil(column_name or value)

두 함수는 뒤의 인수에 대해 무조건 정수를 반환하도록 하므로,

round나 truncate 함수의 소수 자리를 나타내주던 두 번째 인수를 쓰지 않는다.

Q. 

select floor(123456.789), ceil(123456.123);

A. 

floor(123456.789) 
123456

ceil(123456.123) 
123457 

위의 결과를 살펴보면, trunc와 floor 함수는 거의 동일한 결과를 반환하지만, 

round와 ceil 함수는 그 차이를 분명히 해야 한다. 

round는 반올림하기 위한 기준(0.5이상)이어야 인수 보다 큰 다음 정수를 나타내지만, 

ceil은(0.5미만)이라도 인수보다 큰 다음 정수를 반환한다.

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

mod(column_name1 or value1(분자), column_name2 or value2(분모)

 

ex)

select mod(10, 3), mod(5, 8);

 

mod 함수에서 두 번째 인수가 첫 번째 인수보다 크거나 0(zero)일 경우 결과 값은 첫 번째 인수를 반환한다.

특히 일반 수식 계산은 0(zero)으로 나눌 경우 에러가 출력되지만,

mod 함수에서 두 번째 인수가 0(zero)인 경우 null을 출력한다. (5.8 버전 이전에는 에러가 발생하지 않았음)

- abs함수 : 절대값을 출력하는 함수이다.
ex)

select abs(124), abs(-124);

위의 예제는 124와 -124의 절대값을 구하는 식이다.

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

  단, 음수는 양수로 처리 된다.
ex)

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

첫 번째는 2의 4승값을 구하고, 

두 번째는 소수점을 포함한 음수지만 양수로 처리되어 나오며, 

세 번째는 소수점을 포함한 1.5의 6승값을 구한 것이다.

- greatest, least함수
  greatest 함수는 주어진 숫자중 가장 큰 수를 반환하는 것이고, 

  least는 반대로 가장 작은 수를 반환한다.

ex)

select greatest(15, 45, 32, 65), least(15, 45, 32, 65);

위의 예제를 보면 greatest는 4개의 숫자중 가장 큰 65를 반환하지만,

least는 4개의 숫자중 가장 작은 15를 반환한다.

Q. 재학생들의 등록 금액에 비례한 장학금의 비율은 몇 % 인가?
A. 결과 : 80.33%

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

 

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

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

 

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

문자열 조작 함수	설명
substring	지정된 위치에서 지정된 길이만큼 문자열 추출
concat	두 문자열을 연결시켜 합침
length	문자열의 길이를 정수값으로 반환
instr	문자열에서 특정 문자의 위치를 반환
lpad	왼쪽에 지정된 문자를 지정된 길이만큼 채워줌
rpad	오른쪽에 지정된 문자를 지정된 길이만큼 채워줌

대소문자 변환해주는 문자열 조작 함수	설명
lower	문자열을 모두 소문자로 바꿔 줌
upper	문자열을 모두 대문자로 바꿔 줌
initcap	문자열에 속한 각 단어별로 첫 글자를 대문자로, 나머지 부분은 소문자로 바꿔 줌

Q1.

- 'Data'와 'Base' 문자열을 합치시오.
- 'Korea'의 1번째 글자부터 3번째 글자까지 구하면?
- 'lee_woo'의 글자수는?
- 'Korea'에서 'e'의 위치는?
- 전체 문자열 15개를 만드는데 'Korea' 왼쪽은 '*'로 채우시오.
- 전체 문자열 15개를 만드는데 'Korea' 오른쪽은 '#'로 채우시오.
- 'Korea'를 소문자로
- 'Korea'를 대문자로
- 'KOREA UNIVERSITY'의 각 단어의 첫 글자를 대문자로(오라클)

A.

concat('Data', 'Base') ---> DataBase 
substring('Korea', 1, 3) ---> Kor 
length('lee_woo') ---> 7 
instr('Korea', 'e') ---> 4 
lpad('Korea', 15, '*') ---> ***********Korea 
rpad('Korea', 15, '#') ---> Korea########## 
lower('Korea') ---> korea 
upper('Korea') ---> KOREA 
INITCAP ('KOREA UNIVERSITY') ---> Korea University (오라클) // MySQL에서 사용 불가

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

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

A.

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

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

A.

select stu_no, stu_name, stu_ename, length(rtrim(stu_ename)) "영문이름 길이" from student where grade = 2;

student 테이블에서 각 학생에 대한 스칼라 함수 length(...)의 값이 결정된다. 

이 함수의 전달 인수 자체는 trim이라는 함수이다. 

rtrim 함수는 영수치 값에서 우측에 있는 불필요한 공백을 모두 제거한다. 

예제에서 문자의 수가 계산되기 전에 이름으로부터 모든 공백은 제거된다. 

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

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

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

select stu_no, stu_name, post_no from student where substring(post_no, 1, 3) = '550';

 

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

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

A.

select s.stu_no, s.stu_name, s.post_no, concat(trim(p.post_address), trim(s.address)) "주소" from student s, post p where s.post_no = p.post_no and stu_no = '20001021';

우편번호 테이블에서 451-800에 해당하는 주소 값(경기도 평택시 팽성읍)과 

학생신상 테이블에서 '20001021'인 학생의 주소 값(안정리 주공 APT 107동 504호)을 조합하여 

이상길 학생의 주소를 완성하여 출력하였다.