Day03-1. SQL 기본 - SELECT, 관계연산자, DISTINCT, LIMIT

[참고] 주석 만들기

-- 한줄 주석

/*여러줄 주석*/

 

테이블 만들기

CREATE TABLE usertbl -- 회원 테이블
( userID  	CHAR(8) NOT NULL PRIMARY KEY, -- 사용자 아이디(PK)
  name    	VARCHAR(10) NOT NULL, -- 이름
  birthYear   INT NOT NULL,  -- 출생년도
  addr	  	CHAR(2) NOT NULL, -- 지역(경기,서울,경남 식으로 2글자만입력)
  mobile1	CHAR(3), -- 휴대폰의 국번(011, 016, 017, 018, 019, 010 등)
  mobile2	CHAR(8), -- 휴대폰의 나머지 전화번호(하이픈제외)
  height    	SMALLINT,  -- 키
  mDate    	DATE  -- 회원 가입일
);
CREATE TABLE buytbl -- 회원 구매 테이블(Buy Table의 약자)
(  num 		INT AUTO_INCREMENT NOT NULL PRIMARY KEY, -- 순번(PK)
   userID  	CHAR(8) NOT NULL, -- 아이디(FK)
   prodName 	CHAR(6) NOT NULL, --  물품명
   groupName 	CHAR(4)  , -- 분류
   price     	INT  NOT NULL, -- 단가
   amount    	SMALLINT  NOT NULL, -- 수량
   FOREIGN KEY (userID) REFERENCES usertbl(userID)
);

INSERT INTO usertbl VALUES('LSG', '이승기', 1987, '서울', '011', '1111111', 182, '2008-8-8');
INSERT INTO usertbl VALUES('KBS', '김범수', 1979, '경남', '011', '2222222', 173, '2012-4-4');
INSERT INTO usertbl VALUES('KKH', '김경호', 1971, '전남', '019', '3333333', 177, '2007-7-7');
INSERT INTO usertbl VALUES('JYP', '조용필', 1950, '경기', '011', '4444444', 166, '2009-4-4');
INSERT INTO usertbl VALUES('SSK', '성시경', 1979, '서울', NULL  , NULL      , 186, '2013-12-12');
INSERT INTO usertbl VALUES('LJB', '임재범', 1963, '서울', '016', '6666666', 182, '2009-9-9');
INSERT INTO usertbl VALUES('YJS', '윤종신', 1969, '경남', NULL  , NULL      , 170, '2005-5-5');
INSERT INTO usertbl VALUES('EJW', '은지원', 1972, '경북', '011', '8888888', 174, '2014-3-3');
INSERT INTO usertbl VALUES('JKW', '조관우', 1965, '경기', '018', '9999999', 172, '2010-10-10');
INSERT INTO usertbl VALUES('BBK', '바비킴', 1973, '서울', '010', '0000000', 176, '2013-5-5');

INSERT INTO buytbl VALUES(NULL, 'KBS', '운동화', NULL, 30,   2); -- NULL은 AUTO_INCREMENT
INSERT INTO buytbl VALUES(NULL, 'KBS', '노트북', '전자', 1000, 1);
INSERT INTO buytbl VALUES(NULL, 'JYP', '모니터', '전자', 200,  1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '모니터', '전자', 200,  5);
INSERT INTO buytbl VALUES(NULL, 'KBS', '청바지', '의류', 50,   3);
INSERT INTO buytbl VALUES(NULL, 'BBK', '메모리', '전자', 80,  10);
INSERT INTO buytbl VALUES(NULL, 'SSK', '책'    , '서적', 15,   5);
INSERT INTO buytbl VALUES(NULL, 'EJW', '책'    , '서적', 15,   2);
INSERT INTO buytbl VALUES(NULL, 'EJW', '청바지', '의류', 50,   1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '운동화', NULL, 30,   2);
INSERT INTO buytbl VALUES(NULL, 'EJW', '책'    , '서적', 15,   1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '운동화', NULL, 30,   2);

 

usertbl

buytbl

 

WHERE절

• 조회하는 결과에 특정한 조건을 줘서 원하는 데이터만 보고 싶을 때 사용한다.

SELECT 필드이름 FROM 테이블이름 WHERE 조건식

예시

SELECT * FROM usertbl WHERE name = '김경호'; -- where 조건절

 

관계 연산자의 사용

SELECT userID, Name From usertbl where birthyear >= 1970; -- 비교연산자

SELECT userId, Name FROM usertbl where birthYear >= 1970 AND height >= 182; 
SELECT userId, Name FROM usertbl where birthYear >= 1970 OR height >= 182;

 

BETWEEN, AND, LIKE

-- 두 개 같은 말이다.
select name, height from usertbl WHERE height >=180 And height <= 183;
select name, height from usertbl WHERE height between 180 And 183;

-- 주소 하나라도 걸린 사람 다 나온다. 역시 두개 같은 말
select name, addr FROM usertbl where addr ='경남' OR addr='전남' or addr='경북';
select name, addr FROM usertbl where addr IN ('경남', '전남', '경북'); -- in 잘 파악하세요

• IN은 연속적인 값이 아닌 이산적인 값을 위해 사용한다.

 

LIKE : 문자열 내용을 검색

-- ~ 인 사람 LIKE
select name, height FROM usertbl where name like '김%'; -- 성이 김인 사람
SELECT name, height FROM usertbl where name like '_종신'; -- 이름이 종신인 사람

 

 

ANY / ALL / SOME 그리고 서브쿼리

• 서브쿼리란 쿼리문 안에 또 쿼리문이 들어 있는 것.
• EX. 김경호보다 키가 크거나 같은 사람의 이름과 키를 출력하려면 김경호의 키를 직접 입력

-- 서브쿼리
SELECT name, height FROM usertbl WHERE height > 177; -- 현재는 김경호
-- WHERE 절에 들어가는 것이 SUBQUERY

 

이렇게 위처럼 직접 쓰지 말고, 쿼리를 통해서 직접 사용하라.

SELECT name, height FROM usertbl
	WHERE height > (SELECT height FROM usertbl WHERE name = '김경호'); -- 서브쿼리
-- 위의 것과 이게 실행 결과 같음.

 

지역이 경남 사람의 키보다 키가 크거나 같은 사람을 추출해보기

=> 김범수(173)과 윤종신(170?????) 이 경남, 조용필 제외하고 모두 출력되어야 하는데.

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

에러 나온다!!

-- 에러, 두개의 값이 출력. 누구랑 비교해야 할지를 모른다.
-- 해결책은 ANY 또는 ALL

 

일단 ANY로 고치기

SELECT name, height FROM usertbl
	WHERE height >= ANY (SELECT height FROM usertbl WHERE addr ='경남'); 
    -- 결과값 중 가장 작은 값과 비교 (170)

 

ALL로 고치기

SELECT name, height FROM usertbl
	WHERE height >= ALL (SELECT height FROM usertbl WHERE addr ='경남'); 
    -- 결과값 중 가장 큰 값과 비교 (173)

 

정리하자면,

• ANY는 서브쿼리의 여러 결과 중 한 가지만 만족해도 되며
• ALL은 서브쿼리의 여러 개의 결과를 모두 만족시켜야 한다.
• SOME은 ANY와 동일한 의미로 사용한다.

>= ANY 대신에 = ANY 를 사용하면?

SELECT name, height FROM usertbl
	WHERE height = ANY (SELECT height FROM usertbl WHERE addr ='경남'); 
    -- ANY 다음의 서브쿼리 결과와 동일한 값인 173, 170에 해당되는 사람만 출력됨.

 

=ANY(서브쿼리) 는 IN(서브쿼리)와 동일한 의미

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

 

 

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

• 디폴트 값은 오름차순, 내림차순으로 나타내려면 가장 뒤에 DESC
• 그런데 ORDER BY에 나온 열이 SELECT 다음에 꼭 있을 필요는 없다.
• SELECT userID FROM usertbl ORDER BY height 처럼 써도 됨.
• 하지만 MySQL에서는 되도록 사용을 지양하길 바람(성능 떨어뜨릴 수 있다.)

SELECT name, mDate from usertbl ORDER BY mDate; -- defalut 오름차순
SELECT name, mDate from usertbl ORDER BY mDate desc; -- 내림차순   
-- 같은 경우가 나오면, 뒤에 것을 기준으로 놓는다. 우선순위 있음

SELECT name, height from usertbl order by height desc, name asc;
-- 키로 우선 내림차순, 그 이후에 이름으로 오름차순(성시경이승기)     

SELECT addr From usertbl;
SELECT addr FROM usertbl ORDER BY addr;

 

 

DISTINCT : 중복된 것은 하나만 남기는 작업

SELECT distinct addr From usertbl;
SELECT distinct addr From usertbl order by addr; -- 중복제거, 주소 있는 사람만 나옴.

 

 

LIMIT : 출력하는 개수를 제한

USE employees;
SELECT emp_no, hire_date FROM employees
	ORDER BY hire_date ASC;
SELECT emp_no, hire_date FROM employees
	ORDER BY hire_date ASC
    LIMIT 5; 

SELECT emp_no, hire_date FROM employees
	ORDER BY hire_date ASC
    LIMIT 0, 5; -- LIMIT 5 OFFSET 0과 동일.

- 범위가지고 장난칠 수 있으니 주의