MySQL 내장함수

 

- 산술연산 ( + - * / )
- mod(값, 값2) : 값을 값2로 나눈 나머지
- round(값, 자리수) : 반올림
- ceil(값) : 올림 / floor(값): 내림 => 이 둘은 자리수 지정 X
- sqrt : 제곱근 / pow : 제곱

 

문자열함수

- concat(str, str2) : 문자열 연결 (Java처럼 +연결연산자 없음.)
- length(str) : 문자열의 길이 (영어1, 한글은 2)
- char_length(str) : 글자수로 인지 (한글 영어 무조건 1)
- substr(str, start, count) : 시작 위치부터 개수만큼 추출(1부터 시작)
- left(str, 개수) : 왼쪽에서 개수만큼 추출
- right(str, 개수) : 오른쪽에서 개수만큼 추출
- mid(str, 시작위치, 개수) : 지정 위치부터 개수만큼 추출
- replace(str, old, new) : 문자열에서 old문자를 찾아 new 문자로 변경
- insert(str, 위치, 길이, 삽입할 문자) : 문자에서 위치의 길이만큼 지우고 삽입
- ucase : 대문자로변환 / lcase : 소문자로 변환
- space(숫자) : 숫자만큼 공백추가
- trim(str) : 공백제거 / ltrim / rtrim 
- trim(both '-' from '----------abcd------')
    - both(양쪽) / leading(앞) / trailing(뒤)
- lpad(문자, 총길이, 채울문자)/ rpad : 앞,뒤쪽에 총길이만큼 채울 문자를 삽입
- repeat(문자열,횟수) : 문자열을 횟수만큼 반복
- reverse(문자열) : 문자열을 거꾸로 출력

-- ex) 010101-1234567 => 010101-1******
select rpad( left('010101-1234567', 8),14,'*');

 

 

날짜함수

- adddate / subdate : 날짜를 기준으로 ~후, ~전의 날짜
    - adddate(date, interval 5 day)
    - interval 뒤쪽 값 : year, month, day, hour, minute, second 가능
- curdate(), curtime(), now(), sysdate() : 오늘날짜 / 시간
- addtime/subtime : 시간을 더하고 뺀 결과
    - addtime('날짜/시간', '시:분:초')
- year(날짜) / month / day / hour / minute / second : 년월일시분초 추출
- date(날짜) / time(시간) : 날짜/시간 추출
- datediff(date1, date2) / timediff : date2에서 date1까지 남은기간 (d-day)
- dayofweek(date) : 요일 (일=1, 월=2...)
- monthname(date) : 월을 영어로 리턴
- dayofyear(date) : 1년중 몇일이 경과했는지 리턴
- last_day(date) : 해당 날의 월이 몇일까지 있는지 확인
- time_to_sec(time) : 시간을 초단위로 변환

 

논리함수

- if(조건식, 참, 거짓)
- if(조건식, 참, if(조건식, 참, 거짓));

select if(10 > 5, 't', 'f');

 

- case ~ when ~ then
case
when 조건 then 값
when 조건 then 값
when 조건 then 값
else 값
end;

🟢DB는 if문보다는 case when then을 많이 사용한다.(특히 오라클에선 더 많이 사용함)

 

 

사용자 정의 변수 사용

SET @변수이름 := 값;

 

 

정보함수

- user(), current_user(), session_user() : 현재 사용자
- database(), schema() : 사용중인 db명 리턴

 

📢 실습(1)

student 테이블에서 다음 값을 처리

 

1. 등급을(ranking) 칼럼을 추가

alter table student add ranking varchar(10);

mysql> select * from student;
+------+--------+------+---------+------------+-------+-------+---------+
| num  | name   | age  | address | major      | score | grade | ranking |
+------+--------+------+---------+------------+-------+-------+---------+
|  112 | 김순이 |   22 | suwon   | korea      |    94 |     2 | NULL    |
|  113 | 한예리 |   22 | busan   | korea      |    84 |     2 | NULL    |
|  114 | 한재희 |   22 | seoul   | computer   |    64 |     2 | NULL    |
|  555 | 신영호 |   23 | busan   | management |    75 |     3 | NULL    |
| 1001 | 홍길동 |   23 | suwon   | korea      |    89 |     3 | NULL    |
| 1003 | 이순신 |   22 | suwon   | computer   |    57 |     2 | NULL    |
| 1004 | 박웅   |   23 | incheon | english    |    67 |     3 | NULL    |
| 1005 | 이정희 |   21 | incheon | math       |    97 |     1 | NULL    |
| 1006 | 최명화 |   23 | seoul   | korea      |    67 |     3 | NULL    |
| 1007 | 정지호 |   22 | jeju    | computer   |    77 |     2 | NULL    |
| 1008 | 김주호 |   23 | seoul   | english    |    84 |     3 | NULL    |
| 1009 | 김동주 |   24 | incheon | computer   |    99 |     4 | NULL    |
+------+--------+------+---------+------------+-------+-------+---------+

 

 

2. score에 따라 ranking A/B/C 값을 추가

80 이상이면 A / 60 이상이면 B / 나머지는 C

update student set ranking =

case

when score>=80 then 'A'

when score>=60 then 'B'

else 'C'

end;

mysql> select * from student;
+------+--------+------+---------+------------+-------+-------+---------+
| num  | name   | age  | address | major      | score | grade | ranking |
+------+--------+------+---------+------------+-------+-------+---------+
|  112 | 김순이 |   22 | suwon   | korea      |    94 |     2 | A       |
|  113 | 한예리 |   22 | busan   | korea      |    84 |     2 | A       |
|  114 | 한재희 |   22 | seoul   | computer   |    64 |     2 | B       |
|  555 | 신영호 |   23 | busan   | management |    75 |     3 | B       |
| 1001 | 홍길동 |   23 | suwon   | korea      |    89 |     3 | A       |
| 1003 | 이순신 |   22 | suwon   | computer   |    57 |     2 | C       |
| 1004 | 박웅   |   23 | incheon | english    |    67 |     3 | B       |
| 1005 | 이정희 |   21 | incheon | math       |    97 |     1 | A       |
| 1006 | 최명화 |   23 | seoul   | korea      |    67 |     3 | B       |
| 1007 | 정지호 |   22 | jeju    | computer   |    77 |     2 | B       |
| 1008 | 김주호 |   23 | seoul   | english    |    84 |     3 | A       |
| 1009 | 김동주 |   24 | incheon | computer   |    99 |     4 | A       |
+------+--------+------+---------+------------+-------+-------+---------+

 

3. ranking별 인원수 출력

select ranking, count(*) from student group by ranking;

+---------+----------+
| ranking | count(*) |
+---------+----------+
| A       |        6 |
| B       |        5 |
| C       |        1 |
+---------+----------+

 

 

4. 전체 student 데이터 출력

- name의 형식을 => 홍O동 (글자의 두번째 이름에 동그라미 표현)

select num, insert(name, 2, 1, 'O'), age, address, major, score, grade, ranking from student;

+------+-------------------------+------+---------+------------+-------+-------+---------+
| num  | insert(name, 2, 1, 'O') | age  | address | major      | score | grade | ranking |
+------+-------------------------+------+---------+------------+-------+-------+---------+
|  112 | 김O이                   |   22 | suwon   | korea      |    94 |     2 | A       |
|  113 | 한O리                   |   22 | busan   | korea      |    84 |     2 | A       |
|  114 | 한O희                   |   22 | seoul   | computer   |    64 |     2 | B       |
|  555 | 신O호                   |   23 | busan   | management |    75 |     3 | B       |
| 1001 | 홍O동                   |   23 | suwon   | korea      |    89 |     3 | A       |
| 1003 | 이O신                   |   22 | suwon   | computer   |    57 |     2 | C       |
| 1004 | 박O                     |   23 | incheon | english    |    67 |     3 | B       |
| 1005 | 이O희                   |   21 | incheon | math       |    97 |     1 | A       |
| 1006 | 최O화                   |   23 | seoul   | korea      |    67 |     3 | B       |
| 1007 | 정O호                   |   22 | jeju    | computer   |    77 |     2 | B       |
| 1008 | 김O호                   |   23 | seoul   | english    |    84 |     3 | A       |
| 1009 | 김O주                   |   24 | incheon | computer   |    99 |     4 | A       |
+------+-------------------------+------+---------+------------+-------+-------+---------+

 

 

5. student 테이블의 성적이 1~3등까지만 출력

select * from student order by score desc limit 3;

+------+--------+------+---------+----------+-------+-------+---------+
| num  | name   | age  | address | major    | score | grade | ranking |
+------+--------+------+---------+----------+-------+-------+---------+
| 1009 | 김동주 |   24 | incheon | computer |    99 |     4 | A       |
| 1005 | 이정희 |   21 | incheon | math     |    97 |     1 | A       |
|  112 | 김순이 |   22 | suwon   | korea    |    94 |     2 | A       |
+------+--------+------+---------+----------+-------+-------+---------+

 

 

6. 학과의 인원수가 3명 미만인 학과를 출력

select major, count(*) as '인원수' from student group by major having count(*) <3;

+------------+--------+
| major      | 인원수 |
+------------+--------+
| management |      1 |
| english    |      2 |
| math       |      1 |
+------------+--------+

 

 

📢 실습(2)

test2 테이블에서 진행

--1. score 칼럼 추가 기본값을 0으로 설정
alter table test2 add score int default 0;

--2. 학과가 korean 학생들을 출력
select * from test2 where dep='korean';

--3. 학과가 korean 인 튜플을 korea로 변경
update test2 set dep='korea' where dep='korean';

--4. score의 값을 update
update test2 set score=
case
when num=110 then 88
when num=111 then 98
when num=222 then 74
when num=333 then 65
when num=444 then 43
when num=555 then 99
when num=666 then 34
when num=777 then 89
when num=888 then 92
when num=999 then 71
end;

--5. score가 80이상인 학생들만 출력
select * from test2 where score>=80 order by score desc;

--6. 학과별 인원수 출력 (학과별로 오름차순)
select dep, count(*) from test2 group by dep order by dep; 

--7. 학과별 성적 평균 (소수점 2자리 반올림) 컬럼명 평균
select dep, round(avg(score), 2) as '평균' from test2
group by dep;

--8. 주소별 인원수 출력 (주소별로 오름차순)
select addr,count(*) c from test2 group by addr order by addr;

--9. 컴퓨터 학과만 성적 합계 출력 
select dep, sum(score) from test2 where dep='컴퓨터공학과';                         
select dep, sum(score) from test2 group by dep having dep = '컴퓨터공학과';    --둘 다 가능

--10. 성적이 70점이상인 학생들의 num, name, dep, score만 출력
select num, name, dep, score from test2 where score >=70 order by score desc;