CREATE DATABASE sqlDB;
USE sqlDB;
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 -- 회원 구매 테이블
( 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', N'이승기', 1987, N'서울', '011', '11111111', 182, '2008-8-8');
INSERT INTO userTbl VALUES('KBS', N'김범수', 1979, N'경남', '011', '22222222', 173, '2012-4-4');
INSERT INTO userTbl VALUES('KKH', N'김경호', 1971, N'전남', '019', '33333333', 177, '2007-7-7');
INSERT INTO userTbl VALUES('JYP', N'조용필', 1950, N'경기', '011', '44444444', 166, '2009-4-4');
INSERT INTO userTbl VALUES('SSK', N'성시경', 1979, N'서울', NULL , NULL , 186, '2013-12-12');
INSERT INTO userTbl VALUES('LJB', N'임재범', 1963, N'서울', '016', '66666666', 182, '2009-9-9');
INSERT INTO userTbl VALUES('YJS', N'윤종신', 1969, N'경남', NULL , NULL , 170, '2005-5-5');
INSERT INTO userTbl VALUES('EJW', N'은지원', 1972, N'경북', '011', '88888888', 174, '2014-3-3');
INSERT INTO userTbl VALUES('JKW', N'조관우', 1965, N'경기', '018', '99999999', 172, '2010-10-10');
INSERT INTO userTbl VALUES('BBK', N'바비킴', 1973, N'서울', '010', '00000000', 176, '2013-5-5');
INSERT INTO buyTbl VALUES(NULL, 'KBS', N'운동화', NULL , 30, 2);
INSERT INTO buyTbl VALUES(NULL, 'KBS', N'노트북', N'전자', 1000, 1);
INSERT INTO buyTbl VALUES(NULL, 'JYP', N'모니터', N'전자', 200, 1);
INSERT INTO buyTbl VALUES(NULL, 'BBK', N'모니터', N'전자', 200, 5);
INSERT INTO buyTbl VALUES(NULL, 'KBS', N'청바지', N'의류', 50, 3);
INSERT INTO buyTbl VALUES(NULL, 'BBK', N'메모리', N'전자', 80, 10);
INSERT INTO buyTbl VALUES(NULL, 'SSK', N'책' , N'서적', 15, 5);
INSERT INTO buyTbl VALUES(NULL, 'EJW', N'책' , N'서적', 15, 2);
INSERT INTO buyTbl VALUES(NULL, 'EJW', N'청바지', N'의류', 50, 1);
INSERT INTO buyTbl VALUES(NULL, 'BBK', N'운동화', NULL , 30, 2);
INSERT INTO buyTbl VALUES(NULL, 'EJW', N'책' , N'서적', 15, 1);
INSERT INTO buyTbl VALUES(NULL, 'BBK', N'운동화', NULL , 30, 2);
SELECT FROM userTbl;
SELECT FROM buyTbl;
SELECT mobile1, COUNT(*) AS cnt
FROM usertbl
WHERE mobile1 IS NOT null
GROUP BY mobile1
ORDER BY cnt desc
LIMIT 3;
SELECT * FROM usertbl LIMIT 3;
SELECT YEAR(mDate), COUNT(*) AS cnt FROM usertbl
HAVING COUNT(mDate)
GROUP BY YEAR(mDate);
SELECT addr, AVG(height) AS avg_height
FROM usertbl
GROUP BY addr
HAVING AVG(height)>175
--교재 p.185~
-- where 조건 없이 출력하기
USE sqlDB;
SELECT FROM userTBL;
-- where 조건 포함 출력하기
SELECT FROM userTBL WHERE NAME='김경호';
-- 관계 연산자 사용 AND
SELECT userID, NAME FROM userTBL
WHERE birthYear>=1970 AND height>=182;
-- 관계 연산자 사용 OR
SELECT userID, NAME FROM userTBL
WHERE birthYear>=1970 or height>=182;
-- Between 예시1
SELECT NAME, height FROM userTBL
WHERE height>=180 AND height<=183;
-- Between 예시2 : 연속되는 경우 and만 써도 됨!!
SELECT NAME, height FROM userTBL
WHERE height BETWEEN 180 AND 183;
-- Between 예시3 : 연속되는 값이 아닌 경우
SELECT NAME, addr FROM userTBL
WHERE addr='경남' OR addr='전남' OR addr='경북';
-- Between 예시4 : 연속되는 값이 아닌 경우
SELECT NAME, addr FROM userTBL
WHERE addr IN('경남','전남','경북');
-- Like 연산자 예시1 : % -> 무엇이든 허용한다는 의미!
SELECT NAME, height FROM userTBL
WHERE NAME LIKE '김%';
-- Like 연산자 예시2
SELECT NAME, height FROM userTBL
WHERE NAME LIKE '%종신';
-- 서브쿼리 : 쿼리문 안에 또 쿼리문이 있는 것!
SELECT NAME, height FROM userTBL
WHERE height > 177;
-- 서브 쿼리 예시1 : 177이라는 키를 직접 써주지 않고 쿼리를 사용하기!
SELECT NAME, height from userTBL
WHERE height > (SELECT height FROM userTBL WHERE NAME='김경호');
-- 서브 쿼리 예시2 -> 2개의 값을 반환하기 때문에 오류!
/SELECT NAME, height FROM userTBL
WHERE height >= (SELECT height FROM userTBL WHERE addr='경남');/
-- ANY
SELECT NAME, height FROM userTBL
WHERE height >= ANY(SELECT height FROM userTBL WHERE addr='경남');
-- ALL
SELECT NAME, height FROM userTBL
WHERE height >= ALL(SELECT height FROM userTBL WHERE addr='경남');
-- =ANY == IN
SELECT NAME, height FROM userTBL
WHERE height = ANY(SELECT height FROM userTBL WHERE addr='경남');
-- IN
SELECT NAME, height FROM userTBL
WHERE height IN(SELECT height FROM userTBL WHERE addr='경남');
-- ORDER BY : 원하는 순서대로 정렬하여 출력
SELECT NAME, mDate FROM userTBL
ORDER BY mDate;
-- desc : 내림차순 / asc : 오름차순
SELECT NAME, mDate FROM userTBL
ORDER BY mDate DESC;
-- 키가 같을 경우에 이름순으로 정렬
SELECT NAME, mDate FROM userTBL
ORDER BY mDate DESC, NAME ASC;
-- DISTINCT : 중복된 것은 하나만 남기기
SELECT addr FROM userTBL ORDER BY addr;
SELECT DISTINCT addr FROM userTBL; --중복된 것은 한개씩만 보여주면서 출력
-- LIMIT : 출력 개수 제한
-- 테이블 복사 : CREATE TABLE ... SELECT
USE sqlDB;
CREATE TABLE buyTBL2 (SELECT FROM buyTBL);
SELECT FROM buyTBL2;
-- 테이블의 일부 열만 복사
CREATE TABLE buyTBL3
(SELECT userID, prodName FROM buyTBL);
SELECT * FROM buyTBL3;
-- 하지만 테이블을 복사해도
-- PK, FK는 복사되지 않음!
-- GROUP BY절 : 그룹으로 묶어주는 역할
SELECT userID, amount FROM buyTBL ORDER BY userID;
SELECT userID AS '사용자 아이디', SUM(amount) AS '총 구매 개수' FROM buyTBL GROUP BY userID;
-- 사용자별 구매액의 총합 구하기
SELECT userID AS '사용자 아이디', SUM(amount*price) AS '총 구매 액' FROM buyTBL
GROUP BY userID;
-- AVG 함수
SELECT AVG(amount) AS '평균 구매 개수' FROM buyTBL;
SELECT userID, AVG(amount) AS '평균 구매 개수' FROM buyTBL
GROUP BY userID;
SELECT NAME, MAX(height), MIN(height) FROM userTBL;
-- -> 결과 이름은 1개만 나옴
SELECT NAME, MAX(height), MIN(height)
FROM userTBL GROUP BY NAME;
-- -> 모두 나옴
SELECT NAME, height
FROM userTBL
WHERE height=(SELECT MAX(height)FROM userTBL)
OR height=(SELECT Min(height)FROM userTBL)
-- -> 키가 가장 작거나 큰 사람의 값만 나오도록 함!
-- 휴대폰이 있는 사용자 수 세기
SELECT COUNT(*) FROM userTBL; -- 10명의 사용자 존재
SELECT COUNT(mobile1) AS '휴대폰이 있는 사용자' FROM userTBL;
-- HAVING 절
-- 사용자별 총 구매액
USE sqlDB;
SELECT userID AS '사용자',SUM(priceamount) AS '총구매액'
FROM buyTBL
WHERE SUM(priceamount)>1000 -- 집계함수는 where 절에 포함될 수 없음!! -> HAVING 이용
GROUP BY userID;
-- HAVING 절 예시1
SELECT userID AS '사용자',SUM(priceamount) AS '총구매액'
FROM buyTBL
GROUP BY userID
HAVING SUM(priceamount)>1000 -- 합계가 1000 초과인 것만 보여줘
ORDER BY SUM(price*amount); -- 구매액이 적은 사용자부터 나타내줌
-- ROLLUP : 총합 또는 중간 합계
SELECT num, groupName, SUM(price*amount) AS '비용'
FROM buyTBL
GROUP BY groupName, num
WITH ROLLUP;
