[혼공학습단 12기] 혼자 공부하는 SQL - 2주차
[혼공학습단] - [혼공학습단 12기] 혼자 공부하는 SQL - 1주차
- SELECT ~ FROM ~ WHERE
SELECT문: 구축이 완료된 테이블에서 데이터를 추출하는 기능을 한다. (기존 데이터를 변경하지는 않음)
SELECT 열이름
FROM 테이블 이름
WHERE 조건식
| DATABASE TABLE 1 / TABLE 2 / TABLE 3 |
SELECT | 조회 / 결과 |
* market_db에서 구매 테이블(buy)의 아이디는 FK(Foreign Key, 외래 키)로 지정되어 있다. 회원 테이블(member)의 아이디와 구매 테이블(buy)의 아이디를 연결하는 기능을 수행한다.
DROP DATABASE는 market_db를 삭제하는 문장이다. market_db.sql을 처음 실행할 때는 필요없지만, market_db.sql을 다시 실행할 일이 있을 경우에 사용한다.
- 회원 테이블(member) 만들기
DROP DATABASE IF EXISTS market_db; -- 만약 market_db가 존재하면 우선 삭제한다.
CREATE DATABASE market_db;
USE market_db;
CREATE TABLE member -- 회원 테이블
( mem_id CHAR(8) NOT NULL PRIMARY KEY, -- 사용자 아이디(PK)
mem_name VARCHAR(10) NOT NULL, -- 이름
mem_number INT NOT NULL, -- 인원수
addr CHAR(2) NOT NULL, -- 지역(경기,서울,경남 식으로 2글자만입력)
phone1 CHAR(3), -- 연락처의 국번(02, 031, 055 등)
phone2 CHAR(8), -- 연락처의 나머지 전화번호(하이픈제외)
height SMALLINT, -- 평균 키
debut_date DATE -- 데뷔 일자
);
1) USE 문은 market_db 데이터 베이스를 선택하는 문장
동일하게 실행하려면, [SCHEMAS] 패널에서 shop_db 데이터 베이스를 더블클릭해서 선택한다.
2) SQL에서 하이픈(-) 2개가 연속되면, 그 이후는 주석(remark)으로 취급된다.
코드의 설명으로 처리되어 없는 것과 마찬가지이다.
- 구매 테이블(buy) 만들기
CREATE TABLE buy -- 구매 테이블
( num INT AUTO_INCREMENT NOT NULL PRIMARY KEY, -- 순번(PK)
mem_id CHAR(8) NOT NULL, -- 아이디(FK)
prod_name CHAR(6) NOT NULL, -- 제품이름
group_name CHAR(4) , -- 분류
price INT NOT NULL, -- 가격
amount SMALLINT NOT NULL, -- 수량
FOREIGN KEY (mem_id) REFERENCES member(mem_id)
);
1) AUTO_INCREMENT 는 자동으로 숫자를 입력해준다는 의미이다. 순번은 자동으로 1,2,3... 으로 증가한다.
- 데이터 입력하기
INSERT INTO member VALUES('TWC', '트와이스', 9, '서울', '02', '11111111', 167, '2015.10.19');
INSERT INTO member VALUES('BLK', '블랙핑크', 4, '경남', '055', '22222222', 163, '2016.08.08');
INSERT INTO member VALUES('WMN', '여자친구', 6, '경기', '031', '33333333', 166, '2015.01.15');
INSERT INTO member VALUES('OMY', '오마이걸', 7, '서울', NULL, NULL, 160, '2015.04.21');
INSERT INTO member VALUES('GRL', '소녀시대', 8, '서울', '02', '44444444', 168, '2007.08.02');
INSERT INTO member VALUES('ITZ', '잇지', 5, '경남', NULL, NULL, 167, '2019.02.12');
INSERT INTO member VALUES('RED', '레드벨벳', 4, '경북', '054', '55555555', 161, '2014.08.01');
INSERT INTO member VALUES('APN', '에이핑크', 6, '경기', '031', '77777777', 164, '2011.02.10');
INSERT INTO member VALUES('SPC', '우주소녀', 13, '서울', '02', '88888888', 162, '2016.02.25');
INSERT INTO member VALUES('MMU', '마마무', 4, '전남', '061', '99999999', 165, '2014.06.19');
INSERT INTO buy VALUES(NULL, 'BLK', '지갑', NULL, 30, 2);
INSERT INTO buy VALUES(NULL, 'BLK', '맥북프로', '디지털', 1000, 1);
INSERT INTO buy VALUES(NULL, 'APN', '아이폰', '디지털', 200, 1);
INSERT INTO buy VALUES(NULL, 'MMU', '아이폰', '디지털', 200, 5);
INSERT INTO buy VALUES(NULL, 'BLK', '청바지', '패션', 50, 3);
INSERT INTO buy VALUES(NULL, 'MMU', '에어팟', '디지털', 80, 10);
INSERT INTO buy VALUES(NULL, 'GRL', '혼공SQL', '서적', 15, 5);
INSERT INTO buy VALUES(NULL, 'APN', '혼공SQL', '서적', 15, 2);
INSERT INTO buy VALUES(NULL, 'APN', '청바지', '패션', 50, 1);
INSERT INTO buy VALUES(NULL, 'MMU', '지갑', NULL, 30, 1);
INSERT INTO buy VALUES(NULL, 'APN', '혼공SQL', '서적', 15, 1);
INSERT INTO buy VALUES(NULL, 'MMU', '지갑', NULL, 30, 4);
1) CHAR, VARCHAR, DATE 형은 작은 따옴표로 값을 묶어주고, INT 형은 작은 따옴표 없이 그냥 넣어주면 된다.
2) 첫번째 열인 순번(num)은 자동으로 입력되므로, 그자리에 NULL을 입력해준다.
- 데이터 조회하기
SELECT * FROM member;
SELECT * FROM buy;
- USE 문
USE 데이터베이스_이름;
SELECT 문을 실행하려면 사용할 데이터베이스를 지정해야 한다.
지정 후, 다시 USE 문을 사용하거나 다른 DB를 사용하겠다고 명시하지 않으면 그대로 수행된다.
* 워크벤치를 재시작하거나 쿼리창을 새로 열면 다시 실행필요
- SELECT 문의 기본 형식
SELECT 열_이름
FROM 테이블_이름
WHERE 조건식
GROUP BY 열_이름
HAVING 조건식
ORDER BY 열_이름
LIMIT 숫자
이를 활용하여,
USE market_db;
SELECT * FROM member;
테이블에서 데이터를 가져올 때 사용하는 예약어로 select를 사용하고, *는 모든 열을 말한다. 즉, member 테이블에서 모든 열의 내용을 가져와라는 의미이다.
원래 테이블의 전체 이름은 데이터베이스_이름.테이블_이름 형식인데, 여기에서는 market_db.member 이다.
SELECT * FROM market_db.member;
회원 테이블의 이름 열만 가져오려면 다음과 같이 입력한다.
SELECT mem_name FROM member;
여러개의 열을 가져오고 싶으면 콤마(,)로 구분하면 되는데 열 이름의 순서는 원래 테이블을 만들 때 순서에 맞출 필요가 없고 보고 싶은 순서대로 열을 나열하면 된다.
SELECT addr, debut_date, mem_name FROM member;
*열이름에 별칭(alias)를 지정할 수 있는데, 열 이름 다음에 지정하고 싶은 별칭을 입력하면 된다. 별칭에 공백이 있으면 큰 따옴표로 묶어준다.
SELECT addr 주소, debut_date "데뷔 일자", mem_name FROM member;
- 특정한 조건만 조회하기
SELECT 열_이름 FROM 테이블_이름 WHERE 조건식;
예를 들면, SELECT * FROM member WHERE mem_name = '블랙핑크'; 이름이 블랙핑크 인 결과만 출력한다.
열이름 = 값은 열의 값에 해당하는 결과만 출력해준다.
SELECT * FROM member WHERE mem_number = 4; 인원수가 4명인 회원을 조회한다.
- 관계 연산자, 논리 연산자 사용
select mem_id, mem_name
from member
where height <= 162;
select mem_name, height, mem_number
from member
where height >= 165 and mem_nuber > 6;
select mem_name, height, mem_number
from member
where height >= 165 or mem_nuber > 6;
select mem_name, height
from member
where height >= 163 and height < 165;
select mem_name, height
from member
where height between 163 and 165;
select mem_name, addr
from member
where addr = '경기' or addr = '전남' or addr = '경남';
select mem_name, addr
from member
where addr in ('경기', '전남', '경남');
select *
from member
where mem_name like '우%';
select *
from member
where mem_name like '__핑크';
in()의 경우, 조건식에서 여러 문자 중 하나에 포함 되는지 비교할 때 사용한다.
%는 문자열에서 여러문자에 대응하는 기호이고, -는 문자열에서 한 문자에 대응하는 기호이다.
- 서브쿼리
select mem_name, height from member
where height > (select height from member where mem_name = '에이핑크');에이핑크의 평균키보다 평균키(height)가 큰 회원 조회
- order by 절
결과의 값이나 개수에 대해서는 영향을 미치지 않지만, 출력되는 순서를 조절한다. 데뷔일자가 빠른 순서대로 출력하는 쿼리는 다음과 같다. 늦은 순서대로 출력하려면 제일 뒤에 desc를 붙인다.
select mem_id, mem_name, debut_date
from member
order by debut_date;
order by 절과 where 절은 함께 사용할 수 있는데, 구문의 순서가 틀리면 오류가 발생한다.
order by절이 where 절 다음에 나와야 한다.
select mem_id, mem_name, debut_date, height
from member
where height >= 164
order by height desc;
정렬 기준을 여러개 열로 지정하려면, 첫번 째 지정 열로 지정한 후에 동일한 경우에 다음 지정 열로 정렬할 수 있다.
select mem_id, mem_name, debut_date, height
from member
where height >= 164
order by height desc, debut_date asc;
평균키가 큰 순서대로 정렬하되, 평균 키가 같으면 데뷔 일자가 빠른 순서로 정렬한다.
- 출력의 개수를 제한하려면 LIMIT를 사용
보통은 먼저 정렬한 경우 앞에서 몇건을 추출한다.
select mem_name, height
from member
order by height desc
limit 3,2;평균 키가 큰 순서로 정렬하되, 3번째부터 2건만 조회한다.
- 중복된 결과를 제거하려면 DISTINCT를 사용
distinct는 조회된 결과에서 중복된 데이터를 1개만 남긴다.
중복되는 지역을 제외하고 출력할 때 다음과 같이 쿼리를 작성한다.
select distinct addr from member;
- GROUP BY 절
그룹으로 묶어주는 역할(데이터를 그룹화 하는 기능)을 수행하는데, 구매 테이블에서 회원이 구매한 물품의 개수를 구할 때, 한 회원이 여러 건의 물건 구매가 있었다면 총 개수를 알려준다.
select mem_id, sum(amount) from buy group by mem_id;
결과를 알아보기 쉽게 별칭을 사용한다.
select mem_id "회원 아이디", sum(amount) "총 구매 개수"
from buy group by mem_id;
만약, 회원이 구매한 금액의 총합을 알고 싶으면 다음과 같이 출력한다.
select mem_id "회원 아이디", sum(price*amount) "총 구매 금액"
from buy group by mem_id;전체 회원이 구매한 물품 개수의 평균을 구하려면 다음과 같이 출력한다.
select avg(amount) "평균 구매 개수" from buy;
각 회원이 한 번 구매 시 평균 구매 개수는 다음과 같다.
select mem_id, avg(amount) "평균 구매 개수"
from buy
group by mem_id;
함께 사용하는 집계 함수는 다음과 같다.
| SUM() | 합계 |
| AVG() | 평균 |
| MIN() | 최소값 |
| MAX() | 최대값 |
| COUNT() | 행의 개수 |
| COUNT(DISTINCT) | 중복은 1개만 인정한 행의 개수 |
- GROUP BY 절
총 구매액이 1000 이상인 회원을 찾아 사은품을 증정하려면 다음과 같이 having 절을 사용한다.
where 절과 비슷한 개념으로 조건을 제한하는데 사용하지만, 집계함수에 대해서 조건을 제한한다.
having 절이 나오는 순서는 group by 절의 다음에 해당한다.
-- 오류 발생 --
select mem_id "회원 아이디", sum(price*amount) "총 구매 금액"
from buy
where sum(price*amount) > 1000;
group by mem_id;
-- having 절을 사용함 --
select mem_id "회원 아이디", sum(price*amount) "총 구매 금액"
from buy
group by mem_id
having sum(price*amount) > 1000;
* group by와 관련된 조건절은 having을 사용해야 한다.
만약 총 구매액이 큰 사용자부터 나타내려면 order by sum(price*amount) desc; 를 추가한다.
- 연습문제
1. select 문에서 사용되는 열을 차례대로 나열하면,
select - from - where - order by - limit 순이다.
2. 1) select * from member order by height; 키 순서대로 정렬한다.
2. 2) select * from member limit 5,2; 5번째부터 2건만 조회한다.
2. 3) select distinct phone1 from member; 연락처가 있는 회원의 연락처를 조회한다.
(phone1의 열이름을 지정하면, null값인 항목은 제외하고 연락처를 조회한다.)
3. order by 절에서 오름차순을 위한 예약어는 asc이고, 내림차순을 위한 예약어는 desc이다.
4. limit에 대한 문법이 틀린 것은 limit 0,3,5이다.
* LIMIT 5 OFFSET 3 은 가능하다.
LIMIT 숫자 : 출력할 행의 수 / OFFSET 숫자 : 출력할 열의 수 (몇 번째)
만약, 10 행씩 출력할 경우, 다음과 같이 사용한다.
1페이지 : select * from member ORDERS LIMIT 10 OFFSET 0;
2페이지 : select * from member ORDERS LIMIT 10 OFFSET 10;
5. distinct 예약어는 조회된 결과에서 중복된 것은 1개만 남기고, select 문의 열이름 앞에 붙인다.
6. group by에서 사용되는 집계함수가 아닌 것은 having()이다.
- 데이터 변경을 위한 sql 문
새로 가입한 회원을 테이블에 입력할 때 insert문을 사용하고, 주소나 연락처가 변경되어 정보를 수정할 때는 update문을 사용하며, 탈퇴로 회원 삭제가 필요하면 delete문을 사용한다.
- insert 문
insert into 테이블 [(열1, 열2, ...)] values (값1, 값2, ...)
테이블 이름 다음에 나오는 열은 생략이 가능한데, 열 이름을 생략할 경우에 values 다음에 나오는 값들의 순서와 개수는 테이블을 정의할 때의 열 순서 및 개수와 동일해야 한다.
use market_db;
create table hongong (toy_id int, toy_name char(4), age int);
insert into honging values (1, '우디', 25);
만약, 나이는 입력하고 싶지 않다면 생략한 나이열에 null 값이 들어간다.
*auto_incrememt는 열을 정의할 때 1부터 증가하는 값을 입력해ㅐ준다. insert에서 사용할 때는 해당 열이 없다고 생각하고 입력하면 되는데, auto_increment로 지정하는 열은 꼭 primary key로 지정해주어야 한다.
create table hongong2 (
toy_id int auto_increment primary key,
toy_name char(4),
age int);
데이터 입력 시 자동증가하는 부분은 null 값으로 채운다.
insert into hongong2 values (null, '몽키', 12);
insert into hongong2 values (null, '스투키', 20);
insert into hongong2 values (null, '그루지', 5);
어느 숫자까지 증가되었는지 확인할 때는 다음을 사용한다.
select last_insert_id();
- update 문
기존에 입력된 값을 수정할 때 사용한다.
update 테이블_이름
set 열1=값1, 열2=값2, ...
where 조건 ;
use market_db;
update city_popul
set city_name = '서울'
where city_name = 'Seoul';
select * from city_popul where city_name = '서울';
- delete 문
테이블의 행 데이터를 삭제해야 하는 경우 사용한다.
delete from 테이블 이름 where 조건;
delete from city_popul
where city_name like 'New%';
'new'로 시작하는 11건의 도시가 모두 지워진다. 만약 상위 몇건만 삭제하려면 limit 구문과 같이 사용한다.
* delete문은 삭제가 오래 걸리는 반면, drop 문은 테이블 자체를 삭제한다. truncate 문은 delete와 동일한 효과를 내지만 속도가 매우 빠르다.