MySQL SELECT. 02 문법

2) 특정한 조건의 데이터만 조회하는 <SELECT ... FROM ... WHERE>

 기본적인 WHERE 절: 조회하는 결과에 특정한 조건을 줘서 원하는 데이터만 보고 싶을 때 사용

형식 : SELECT 필드_이름 FROM 테이블_이름 WHERE 조건식 ;

예) SELECT * FROM userrbl WHERE name  = '김경호' ;

 

1) 관계 연산자 사용

예)  1970년 이후에 출생하고 신장이 182 이상인 사람의 아이디와 이름 조회

SELECT userID, name FROM USERTBL WHERE birthYear>=1970 and height>=182;

 

예) 1970년 이후에 출생했거나 , 신장이 182 이상인 사람의 아이디와 이름을 조회

SELECT userID, name FROM USERTBL WHERE birthYear>=1970 or height>=182;

 

 

2)BETWEEN ... AND 와 IN() 그리고 LIKE

예)키가 180 ~ 183인 사람의 이름과 키를 조회

SELECT name, height from usertbl where height between 180 and 183;

 

예) 지역이 '경남' , '전남', '경북' 인 사람의 이름과 키를 조회

SELECT name, height from usertbl where addr in ('경남', '전남','경북'); 

 

문자열의 내용을 검색하기 위해서는 LIKE 연사자를 사용할 수 있음

SELECT name, height FROM usertbl WHERE name LIKE '김%';

--맨 첫 번째 글자가 김 으로 시작되지만 뒤에 글자는 상관없음

그리고 한 글자와 매치하기 위해서는 언더바(_)를 사용함 

SELECT name, height FROM usertbl WHERE name LIKE '_종신';

 

3) ANY/ALL/SOME 그리고 서브쿼리(하위쿼리)

김경호보다 키가 크거나 같은 사람의 이름과 키를 출력

SELECT name, height FROM usertbl WHERE height  >= 177;

177이라는 키를 직접 써주는 것이 아니라면 서브쿼리 표현

:

SELECT name, height FROM usertbl

WHERE height >= (SELECT height FROM usertbl WHERE name = '김경호'); () 에 표현된 것이 서브쿼리

 

예) 지역이 '경남' 의 키보다 키가 크거나 같은 사람의 이름과 키를 조회

SELECT name, height FROM usertbl WHERE height >= ANY 
    (SELECT height FROM usertbl WHERE addr = '경남');

 

결론적으로 ANY 는 서브쿼리 여러 개의 결과 중 한 가지만 만족해도 되고,

ALL은 서브쿼리의 여러개의 결과를 모두 만족시켜야 함 

참고로 SOME은 ANY와 동일한 의미로 사용됨

SELECT name, height FROM usertbl

WHERE heigt = ANY (SELECT height FROM usertbl WHERE addr = '경남')'

이 쿼리문과 동일한 의미의 쿼리문

SELECT name, height FROM usertbl

WHERE heigt IN (SELECT height FROM usertbl WHERE addr = '경남');

 

4) 원하는 순서대로 정렬하여 출력 : ORDER BY

ORDER BY 절은 결과물에 대해 영향을 미치지는 않지만, 결과가 출력되는 순서를 조절하는 구문

 

예)먼저 가입한 순서로 회원들의 이름과  가입날짜  조회하기

SELECT name, mDate FROM usertbl ORDER BY mDate;

 

기본적으로 오름차순(ASC) 으로 정렬됨.

 

예) 내림차순으로 정렬하기 위해서는 열_이름 뒤에 DESC 적기

SELECT name, mDate FROM usertbl ORDER BY mDate DESC;

 

예) 키가 큰 순서로 정렬하되 만약 키가 같은 경우에 이름 순으로 정렬해서 나오는 데이터의 이름과 키를 출력

SELECT name, height FROM usertbl ORDER BY height DESC , name ASC;

 

5) 중복된 것은 하나만 남기는 DISTINCT

회원 테이블에서 회원들이 거주지역이 몇 군데인지 출력하기

SELECT addr FROM usertbl ; > 이 결과는 중복값 나옴 나오는 갯수 10개 

SELECT DISTINCT addr FROM usertbl; > 이 결과는 5개 

 

6) 출력하는 개수를 제한하는 LIMIT

USE employees;

SELECT emp_no, hire_date FROM employees

       ODER BY hire_date ASC LIMIT 5;

LIMIT절은 'LIMIT 시작, 개수' 또는 'LIMIT 개수 OFFSET 시작' 형식으로도 사용할 수 있음

또는 

SELECT emp_no, hire_date FROM employees

       ODER BY hire_date ASC LIMIT  0,5;

 

7) 테이블을 복사하는 CREATE TBLE ... SELECT

 · CREATE TABLE ... SELECT 구문은 테이블을 복사해서 사용할 경우에 주로 사용됨

CREATE TABLE 새로운테이블_이름 (SELECT 복사할 열_이름 FROM 기존테이블_이름);

예) CREATE TABLE buytbl2 (SELECT * FROM buytbl);

>테이블 자체를 통으로 복사

필요하다면 지정한 일부 열만 복사할 수도 있음

CREATE TABLE buytbl3 (SELECT userID, prodName FROM buytbl);

 

3. GROUP BY 및 HAVING 그리고 집계함수

1) GROUP BY : 그룹으로 묶어주는 역할

 · 구매 테이블에서 사용자가 구매한 물품의 개수 출력하기 

USE sqldb;

SELECT UserID, amount FROM buytbl ORDER BY userID; 를

SELECT USERID, sum(amount) FROM buytbl GROUP BY userID; 로 그룹화 시켜서 표현

 

SELECT userID AS '사용자 아이디' , sum(amount)  AS '총 구매 개수' FROM  buytbl GROUP BY useID;

 

SELECT userID AS '사용자 아이디' , sum(price*amount)  AS '총 구매 액' FROM  buytbl GROUP BY userID;

 

2) 집계 함수 

 · AVG() : 평균을 구함

 · MIN() : 최소값 구함

 · MAX() : 최대값 구함

 · COUNT() : 행의 개수를 셈

 · COUNT(DISTINCT) : 행의 개수를 셈(중복은 1개만 인정)

 · STDEV() : 표준편차를 구함

 · VAR_SAMP() : 분산을 구함

 

예) 전체 구매자가 구매한 물품의 개수의 평균을 구하기

SELECT userID , AVG(amount) AS ' 평균 구매 개수' FROM buytbl;

예) 각 사용자 별로 한 번 구매 시 물건을 평균 몇 개 구매했는지 평균을 구하기 (GROUP BY 사용)

SELECT userID , AVG(amount) AS ' 평균 구매 개수' FROM buytbl GRUOP BY userID;

예)usertbl에서 가장 큰 키와 가장 작은 키의 회원 이름과 키를 출력

SELECT name, height FROM usertbl

          WHERE height = (SELECT MAX(height) FROM usertbl)

          OR height = (SELECT MIN(height) FROM usertbl);

예) 휴대폰이 있는 사용자 수 더하기

SELECT COUNT(mobile1) AS '후대폰 있는 사용자' FROM usertbl;

 

3) HAVING 절

예) 집계함수를 적용해서 사용자별 총 구매액에 대한 쿼리문

 SELECT userID AS '사용자' , SUM(price * amount) AS '총 구매액'

            FROM buytbl GROUP BY userID;

그런데 이 중에서 총 구매액이 1000 이상인 사용자에게만 사은품을 증정하고 싶다면 

SELECT userID AS '사용자' , SUM(price * amount) AS ' 총 구매액'

            FROM buytbl WHERE SUM(price * amount) > 1000 GROUP BY userID;

> 오류가 난다 

그럼 

SELECT userID AS '사용자' , SUM(price * amount) AS ' 총 구매액'

            FROM buytbl GROUP BY userID HAVING SUM(price * amount) >1000;

>HAVING 절 이용해서 하자

 

4) ROLL UP

 총합 또는 중간 합계가 필요하다면 GROUP BY절과 함께 WITH ROLLUP문 사용하면 됨

SELECT num , groupName , SUM(price * amount) AS '비용'

FROM buytbl GROUP BY groupName, num WITH ROLLUP;