mysql as, order by,where/sum,avg,min,max

 

 

create table student (

user_id varchar(50) not null,

user_name char(50) not null,

age tinyint default '0',

gender enum('M','F'),

year enum('1','2','3'),

hakjum char(1));

 

hakjum 컬럼 삭제

alter table student drop hakjum;

다음 데이터 입력

user_id	user_name	age	gender	year
a1	Tom	15	M	2
a2	Jane	16	F	3
a3	Yakima	14	M	1
a4	Yong	14	Null	1
a5	Minyo	0	F	Null
a6	Kang	15	Null	2
a7	Kim	0	M	Null
a8	Miranda	15	F	2

insert into student values(‘a1’,‘Tom’,15,‘M’,‘2’);

insert into student values(‘a2’,‘Jane’,16,‘F’,‘3’);

insert into student values(‘a3’,‘Yakima’,14,‘M’,‘1’);

insert into student(user_id,user_name,age,year) values(‘a4’,‘Yong’,14,‘1’);

insert into student(user_id,user_name,gender) values(‘a5’,‘Minyo’,‘F’);

insert into student(user_id,user_name,age,year) values(‘a6’,‘Kang’,15,‘2’);

insert into student(user_id,user_name,gender) values(‘a7’,‘Minyo’,‘M’);

insert into student values(‘a8’,‘Miranda’,15,‘F’,‘2’);

 

select * from student;

select sum(age) as ‘age sum’ from student; //age의 합, as는 별명으로 ' '안에 있는 이름으로 출력된다.

select avg(age) as ‘age avg’ from student;//age의 평균

select min(age) as ‘age min’ from student;//age의 최소값

select max(age) as ‘age max’ from student;//age의 최대값

 

select user_name as ‘nm’, user_id as ‘id’ from student;

 

@ order by 절 - 정렬

select * from student order by age desc; // 역순정렬

select * from student order by age asc; // 순차적정렬

@ Where 절

select * from student where year = ‘1’; //year='1'인 값을 출력한다.

select * from student where age >= 15;

select * from student where age < 15;

select * from student where gender is null; // is not null  유일하게 where절에서 글자가 나온 것으로 gender에서 null인 값(is null)과 null이 아닌 값(is not null)을 찾는 것을 뜻한다.

select * from student where year is null; // is not null