1. 학생정보 테이블 스키마
create table student (
userid varchar(20) not null,
username char(20) not null,
age tinyint default '0',
gender enum('M','F'),
grade enum('1','2','3') );
※ 테이블 설명
(1) char : character의 약자로 확정길이 문자열을 의미한다.
(보충) 세팅 된 공간 크기 보다 작은 값이 들어와도 확보된 크기는 변하지 않아서 메모리 공간의 낭비를 초래할 수 있다.
(2) varchar : character varying의 약자로 가변길이 문자열을 의미한다.
(보충) 세팅된 공간 크기 보다 작은 값이 들어오면 차지한 공간의 크기에 맞에 세팅 값이 변경된다.
(3) enum : enumeration의 약자로 허용된 값 열거를 의미하고 하나만 선택할 수 있다.
(4) not null : null 값을 저장할 수 없다는 의미로 절대 누락 될 수 없음
2. 아래의 내용처럼 데이터 입력
userid | username | age | gender | grade |
st101 | Tom | 15 | M | 2 |
st102 | Jane | 16 | F | 3 |
st103 | Yakima | 14 | M | 1 |
st104 | Yong | 14 | Null | 1 |
st105 | Minyo | 0 | F | Null |
st106 | Kang | 15 | Null | 2 |
st107 | Kim | 0 | M | Null |
st108 | Miranda | 15 | F | 2 |
▶ 데이터 입력
ex) insert into student (user_id,user_name,age,gender,grade) values('st101','Tom',15,'M',1);
▶ 통계 함수
(1) count() 함수 - 열 데이터 개수
(*) null이 아닌 항목의 개수를 return
select count(*) from student;
(2) sum() 함수 - 열 값들의 합계
select sum(age) from student;
(3) avg() 함수 - 열 값들의 평균
select avg(age) from student;
(3) min() 함수 - 열 값들 중 최소 값
select min(age) from student;
(4) max() 함수 - 열 값들 중 최대 값
select max(age) from student;
▶ Alias (출력 타이틀 변경)
select username as '이름', userid as '아이디' from student;
▶ Order by (순차적/역순 정렬)
(1) 역순정렬
select * from student order by age desc;
(2) 순차정렬
select * from student order by age asc;
▶ Distinct (데이터 중복 제거)
select age from student;
select distinct(age) from student; -- 단일 값만 출력
(Q1)
(1) 1학년들의 이름을 출력하시오.
(2) 15세 이상 이름과 나이를 출력하시오.
(3) 성별이 기록되어있지 않은 데이터의 아이디 값을 출력하시오. (null값 및 공백)
(Q2)
(1) 아이디 st101와 st102의 이름,연령,학년을 출력하시오.
(2) 15세 이상의 여학생들만 전체 출력하시오.
(3) 2학년 남학생들의 아이디와 이름만을 출력하시오.
(4) 남자학생들의 평균연령을 출력하시오. - 연령 값 0은 계산 제외 -
(5) 남학생들을 연령순으로 순차적 정렬을 하시오.
(Q3)
(1) 아이디 st105의 연령을 14세로 대입한다.
(2) 아이디 st107의 연령을 15세로 대입한다.
(3) 학년이 비워 있는 곳에 아래와 같은 기준으로 데이터를 채운다.
- 연령 14세 : 1 , 15세 : 2, 16세 : 3
(4) 성별이 비워있는 데이터의 값을 모두 남성 처리한다.
▣ if 함수
1. 성별을 출력하는데 남,여로 표기하여 출력한다.
SELECT if(gender='M','남','여') FROM student;
▣ case 함수
1. 성별 남,여 출력
SELECT userid as '아이디',
case
when gender='M' then '남'
when gender='F' then '여'
else '모름'
end as '성별'
FROM student ;
{ 풀어보기 }
1. concat 함수를 이용하여 다음과 같이 출력한다.
2. length 함수를 이용하여 이름의 길이를 출력한다.
Tom | 3 |
Jane | 4 |
Yakima | 6 |
Yong | 4 |
Monyo | 5 |
Kang | 4 |
Kang | 4 |
Miranda | 7 |
3. 함수들을 이용하여 이름(문자열길이) 출력을 하시오.
select concat(username,'(',length(username),')') '이름(길이)' from student;
이름(길이)
Tom(3) |
Jane(4) |
Yakima(6) |
Yong(4) |
Monyo(5) |
Kang(4) |
Kang(4) |
Miranda(7) |
4. 이름데이터를 두자리만 출력하시오.
SELECT userid '아이디', substring(username,1,2) '이름' FROM student;
아이디 이름
st101 | To |
st102 | Ja |
st103 | Ya |
st104 | Yo |
st105 | Mo |
st106 | Ka |
st107 | Ka |
st108 | Mi |
5. 아래의 결과 처럼 이름 뒤에 ** 을 붙여 출력하시오.
SELECT userid '아이디', concat( substring( username, 1, 2 ) , '**' ) '이름' FROM student
아이디 이름
st101 | To** |
st102 | Ja** |
st103 | Ya** |
st104 | Yo** |
st105 | Mo** |
st106 | Ka** |
st107 | Ka** |
st108 | Mi** |
6. 이름 글자 수가 5개 이상인 데이터를 출력하시오.
----- 풀이하기 ------
1. concat : 연결함수
select userid as '아이디',
username as '이름',
concat(grade,'학년') as '학년'
from student;
2. length : 문자열 길이
select username as '이름',
length(username) as '길이'
from student
order by length(username) asc;
3. concat + length
select
concat(username,'(',length(username),')')
as '이름(길이)' from student;
4.
select userid as '아이디',
left(username,2) as '이름'
from student;
5.
select userid as '아이디',
concat(left(username,2),'**') as '이름'
from student;
6. 이름길이 5자리 이상 출력
select username from student
where length(username) >= 5;