mysql 다양한 기초 문법

● 함수 : 복잡한 명령을 간단히 처리하기 위해서 만들어준 기능.

1. 단일함수 - 숫자함수, 날짜함수,문자열함수,논리함수 등
-> 하나의 데이타를 입력받아 처리하는 함수

 

2. 그룹함수 - 여러개의 데이타를 입력받아 처리하는 함수
 : count(), max(), min(), sum(), avg()

-------------------------------------------------------------------------------------

 

※ 단일함수 이용예제

ex) 대문자를 소문자로 만들어 주는 함수 : lcase, lower
mysql> select lower(pname) from person;

 

ex) 소문자를 대문자로 만들어 주는 함수 : ucase, upper
mysql> select upper(pname) from person;

 

ex) 사원의 이름을 출력하되 2번째 문자부터 4번째 문자까지만 출력
mysql> select substring(pname,2,3) from person;

 

ex) 사원의 이름을 출력하되, 첫번째 문자 대문자로 출력
mysql> select upper(substring(pname,1,1)) from person ;

 

ex) 사원의 이름을 출력하되, 첫번째 문자를 대문자로, 나머지는 소문자로 출력
mysql> select concat(upper(substring(pname,1,1)),lower(substring(pname,2,10))) from person ;

 

ex)사원의 이름과 이름 철자의 갯수를 출력
mysql> select pname,length(pname) from person;

 

ex) 사원의 이름 중 앞의 3문자만 출력
mysql> select pname,left(pname,3) from person;

 

ex) 123.45를 소숫점 1번째 자리에서 반올림하여 출력
mysql> select round(123.45,1) ;

 

ex) 123.45를 소숫점을 버리고 출력
mysql> select floor(123.45);

 

ex) 현재 날짜를 출력
mysql> select curdate();

 

ex) 사원의 사번, 이름, 급여와 급여가 3500 이상이면 good으로 3500미만이면 poor로 출력되도록 검색
mysql> select pno,pname,pay if(pay>=3500,'good','poor') result from person;
php에서 if문을 이용해서 사용한다.

ex)123456를 암호화하여 출력
mysql> select password('123456');

※ 그룹함수 이용예제
- 그룹함수를 사용할 경우 그룹함수만을 출력테이타로 사용한다.
- 그룹함수는 null를 데이타로 취급하지 않는다.

ex) person에 저장된 데이타의 갯수를 출력
mysql> select pno, count(pno) from person; =>사용불가(그룹함수만을 출력데이타로 사용해야 하기 때문)
mysql> select count(pno) from person; => 10 출력
mysql> select count(manager) from person; => 9 (데이타 1개가 null이기 때문)
mysql> select count(*) from person; => 갯수 파악할 경우 사용

 

ex) 사원의 급여 중 가장 적은 급여 출력
mysql> select min(pay) from person;

 

ex) 입사일이 최근인 사원의 입사일 출력
mysql> select max(startdate) from person;

 

ex) 사원 급여 합계를 출력
mysql> select sum(pay) from person;

 

ex) 사원 급여 평균을 출력
mysql> select avg(pay) from person;

 

ex) 10번 부서에 근무하는 사원들의 평균 급여를 출력
mysql> select avg(pay) from person where dno=10;

 

ex) 사원의 급여 + 보너스의 평균을 출력
mysql> select avg(pay+bonus) from person; => bonus에 null값이 있으므로 잘못된 값이 출력됨.
mysql> select avg(pay+ifnull(bonus,0)) from person;

- group by  : 그룹함수를 사용한 경우 select에서 사용하는 문장
전체데이타를 하나의 그룹으로 보는 것이 아니라 그룹을 세분화한다.

형식)
select 그룹함수(필드명) from 테이블명 where 조건문 group by 필드명;
=> group by의 필드값이 값을 것을 하나의 그룹으로 인식한다.
=> group by의 필드는 select에서 출력이 가능하다.
=> 출력데이타를 여러개 둘 수 있다.

 

ex) 부서별 인원을 파악하여 출력
mysql> select count(*) from person group by dno;

 

ex) 부서별 인원을 파악하여 부서와 인원 출력
mysql> select dno,count(*) from person group by dno;

 

ex) 업무별 급여 평균을 구하여 업무 및 평균급여 출력
mysql> select job,avg(pay) from person group by job;

- having : 그룹함수에 의한 조건문을 제시하기 위해 사용

형식)
select 그룹함수(필드명) from 테이블명 where 조건문 group by 필드명 having 그룹함수조건문;

 

ex) 부서별 급여 평균을 구하여 부서 및 평균급여를 출력하되, 평균급여가 3000이상인 부서만 검색하여 출력.
mysql> select dno,avg(pay) from person group by dno having avg(pay)>=3000;

- order by : 데이타를 정렬(sort)하여 출력해주는 문장
형식)
select 출력데이타 from 테이블명 where 조건문 group by 필드명 having 그룹함수조건문 order by 필드명 asc(또는 desc), 필드명 asc (또는 desc),...;

※ 정렬순서
asc : 오름차순. 일반적으로 생략하여 사용
desc : 내림차순 정렬

 

ex) person에서 사번,이름,급여를 출력하되 급여순으로 오름차순으로 정렬하여 출력
mysql> select pno,pname,pay from person order by pay asc;
mysql> select pno,pname,pay from person order by pay; (asc 생략가능)

 

ex) person에서 사번,이름,급여를 출력하되 입사일순로 내림차순으로 정렬하여 출력
mysql> select pno,pname,pay from person order by startdate desc;

 

ex) person에서 사번,이름,급여,부서번호를 출력하되 부서순로 오름차순으로 정렬하고 부서가 같은 경우 급여순으로 내림차순 정렬하여 출력
mysql> select pno,pname,pay,dno from person order by dno,pay desc;
mysql> select pno,pname,pay,dno from person order by dno asc,pay desc;

 

ex) person에서 사번,이름,연봉을 출력하되 연봉순으로 내림차순정렬. 단 연봉은 급여*12 + 보너스로 하고 필드명은 yearpay로 한다.
mysql> select pno,pname,pay*12+ifnull(bonus,0) yearpay from person order by yearpay desc;

 

ex) person에서 사번,이름,연봉을 출력하되 업무가 salesman인 사원을 연봉순으로 내림차순정렬. 단 연봉은 급여*12 + 보너스로 하고 필드명은 yearpay로 한다.
mysql> select pno,pname,pay*12+ifnull(bonus,0) yearpay from person where job='salesman' order by yearpay desc;

mysql> select pno,pname,pay*12+ifnull(bonus,0) yearpay from person where job='salesman' order by 3 desc;

 

3 : 출력되는 3번째 필드를 정렬의 기준으로 하라는 의미.

 

ex) person에서 업무별 평균급여를 구하여 업무,평균급여를 출력하되 평균급여가 4000이상인 업무만 출력. 단, 평균급여의 내림차순 정렬.
mysql> select job,avg(pay) avgpay from person group by job having avg(pay)>=4000 order by avg(pay) desc;
mysql> select job,avg(pay) avgpay from person group by job having avgpay>=4000 order by avgpay desc;

 

ex) person에서 업무가 president를 제외한 업무별 평균급여를 구하여 업무,평균급여를 출력하되 평균급여가 4000이상인 업무만 출력. 단, 평균급여의 내림차순 정렬.
mysql> select job,avg(pay) avgpay from person where job!='president' group by job having avgpay>=4000 order by avgpay desc;

- limit : 출력 갯수를 제어하여 출력
형식)
select 출력데이타 from 테이블명 where 조건문 
  group by 필드명 having 그룹함수조건문 
  order by 필드명 asc(또는 desc), 필드명 asc (또는 desc),...
  limit 시작위치,갯수;
※ 시작위치 생략시 처음으로 인식. 행의 번호는 0번 부터 시작한다는 것을 유의할 것.

 

ex) person에서  사번,이름,급여를 출력하되 처음부터 5명만 출력
mysql> select pno,pname,pay from person limit 5;

 

ex) person에서  사번,이름,급여를 출력하되 두번째부터 5명만 출력
mysql> select pno,pname,pay from person limit 1,5;

 

[출처] 인간승리 | 인간승리 (http://bigl2929.textcube.com/31)