[ 2강 ] 학생정보 관련 실습 및 IF함수 익히기

[리눅이]

mysql_2강수업_스키마.txt

891B

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 함수를 이용하여 다음과 같이 출력한다.

st101	Tom	2학년
st102	Jane	3학년

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;