mysql 문법 12월 9일 목요일

1.3 조건절

연산자: >, <, =, >=,<=, and, or, like, is null, in, between

*IS NULL

데이터값이 NULL인것을 뽑아내려고 할때.

*IS NOT NULL

NULL이 아닌값을 뽑아냄

1.4 Order BY : 정렬

ORDER BY 컬럼명 정렬방법(ASC, DESC),...

select * from employee order by pay asc;(오름차순)

select * from employee order by pay desc;(내림차순)

select * from employee limit(paging) 시작인덱스,길이;

select count(컬럼명) from 테이블명; 

=>게시물 갯수를 알고싶을때

select max(컬럼명) from 테이블명;

=>컬럼에서 가장큰것을 뽑아준다.

select min(컬럼명) from 테이블명;

=>컬럼에서 가장 작은것을 뽑아준다.

select distinct(중복제거) 컬럼명 from 테이블명;

=>중복되는 데이터값을 제거한다.

1.5 Group BY : GROUPING 한 결과를 보고싶을때

GROUP BY 컬럼명, ...

ex)select job,count(job) from employee group by job;

ex)select job,max(pay) from employee group by job;

1.6 sub query

mysql> select saname, (select dept_name from dept where dept_no = employee.dept_no) as deptname from employee;

1.7 JOIN(INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN)

SELECT 컬럼명,... FROM 테이블A INNER JOIN 테이블B ON 조건

*함수

분기형SQL1

1.CASE WHEN 조건 THEN 값 END

2.CASE WHEN 조건 THEN 값 ELSE 값 END

ex)select saname,case when job is null then '계약직' else job end from employee;

= 

select saname, ifnull(job,'계약직') job from employee;

select ifnull(max(no),0)+1 from exam3;