혼공 SQL - ch03(SQL 기본 문법)

목록

1. 포스팅 개요
2. 본론
       2-1. 기본 중에 기본 SELECTFROMWHERE
       2-2. 좀 더 깊게 알아보는 SELECT 문
       2-3. 데이터 변경을 위한 SQL문
3. AUTO_INCREMENT 실험
4. 요약

1. 포스팅 개요

혼자 공부하는 SQL 챕터 3를 요약 및 정리한 포스팅

• 간단한 AUTO_INCREMENT 실험

티스토리 마크다운 표는 별로네... 다음부터 스샷으로 올리도록 하겠다..

2. 본론

2-1. 기본 중에 기본 SELECTFROMWHERE

SELECT문은 구축이 완료된 테이블에서 데이터를 추출하는 기능을 함.

DROP DATABASE IF EXISTS 데이터베이스_이름

• 데이터베이스를 삭제하는 문장.

CREATE DATABASE 데이터베이스_이름

• 데이터베이스를 새로 만듬.

USE 데이터베이스_이름

• 데이터베이스를 선택하는 문장
• USE문 이후 다시 USE문을 사용하거나, 다른 DB를 사용하겠다고 명시하지 않으면, 모든 SQL문은 USE 문을 사용한 데이터베이스에 저장됨.

SELECT 문의 기본 형식

SELECT 열_이름
    FROM 테이블_이름
    WHERE 조건식
    GROUP BY 열_이름
    HAVING 조건식
    ORDER By 열_이름
    LIMIT 숫자

테이블 이름이 member이고, 속한 데이터베이스가 market_db라면,

select * from market_db.member; -- 데이터베이스_이름.테이블_이름

데이터베이스이름과 테이블이름을 같이 명시해줘야한다.

하지만, USE로 데이터베이스를 지정해주었다면,
테이블_이름만 명시해주면 된다.

WHERE절

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

SELECT 열_이름 FROM 테이블_이름 WHERE 조건식;
-- 또는
SELECT 열_이름
    FROM 테이블_이름
    WHERE 조건식;

관계 연산자, 논리 연산자의 사용

BETWEEN ~ AND

• 숫자의 범위를 조건식에서 사용할 때 편리하다.

WHERE 절을 다음과 같이 사용할 때, BETWEEN ~ AND를 쓰면 다음과 같다.

select mem_name, height 
from member
where height >= 163 and height <= 165; -- 아래와 같은 조건. 
-- where height BETWEEN 163 and 165;

IN()

• 조건식에서 여러 문자 중 하나에 포함되는지 비교할 때 간결함.

BETWEEN ~ AND 혹은 관계연산자를 통해서 범위를 지정할 수 없는 경우에 사용하는데,
다음과 같이 or을 일일이 써줘야하는 경우가 있다.

SELECT mem_name, addr
from member
where addr = '경기' or addr = '전남' or addr = '경남';

이 때 IN()을 사용하면 훨씬 간결히 할 수 있다.
다음 SQL문은 위와 동일하다.

SELECT mem_name, addr
from member
WHERE addr in('경기', '전남', '경남');

LIKE

• 문자열의 일부 글자를 검색할 때 사용.

예를 들어 이름의 첫 글자가 '김'으로 시작하는 회원은 다음과 같이 검색할 수 있다.
여기서 %는 이후의 값은 무엇이든 허용한다는 의미이다.

SELECT * from member
where mem_name LIKE '김%';

LIKE에 한 글자와 매치하기 위해서는 언더바(_)를 사용한다.

SELECT * FROM member
where mem_name LIKE '__핑크'; -- 언더바 2개

이렇게 select 문을 주면 앞의 두 글자 상관없이 '핑크'로 끝나는 항목들이 결과로 나온다.

서브 쿼리

• 하위 쿼리라고도 부름.

이름이 '에이핑크'인 회원의 평균 키보다 큰 회원을 검색하려고 할 때

1. 에이핑크의 평균 키를 알아낸다.

SELECT height from member where mem_name = '에이핑크'; -- 결과는 164

2. 평균키 보다 큰 회원을 조회한다.

select mem_name, height 
from member 
where height > 164;

여기서 다음과 같이 서브쿼리를 작성할 수 있다.

SELECT mem_name, height 
    from member
    where height > (SELECT height from member where mem_name = '에이핑크');

SQL문을 하나로 만들어 하나의 SQL만 관리하면 되므로 더 간단해진다.

2-2. 좀 더 깊게 알아보는 SELECT 문

ORDER BY

• 정렬을 위한 키워드
• 값이나 개수에 대해서는 영향 X
• 결과가 출력되는 순서를 조절
  • 기본값 ASC : 오름차순
  • DESC : 내림차순
• WHERE절 다음에 위치해야 함.

예시문

SELECT mem_id, mem_name, debut_date 
FROM member 
ORDER BY debut_date desc;  

위 문장은 member를 debut_date(데뷔일자)를 기준으로 내림차순 결과를 보여주는 실행문이다.

이 중 만약 동일한 값이 있을 때, 다른 특정한 기준을 가지고 순서를 바꾸고 싶다면, 정렬 조건을 하나 더 줄 수 있다.

예시문

SELECT mem_id, mem_name, debut_date, height
from member
where height >= 164
order by height desc, debut_date ASC;

LIMIT

• 결과의 개수를 제한하는 키워드
• 형식 : LIMIT 시작, 개수
  • LIMIT 개수 OFFSET 시작과 동일
• ORDER BY와 주로 함께 사용하며, ORDER BY절 다음에 사용.

예시 : member 테이블을 조회하는데, 3개의 데이터만 조회하고 싶을 때,

SELECT * 
FROM member 
LIMIT 3; -- LIMIT 0,3 | LIMIT 3 OFFSET 0 과 동일 // 0번째부터 3건이라는 의미

예시 : 회원이름과 평균키를 평균키 순으로 내림차순 정렬할 때, 3번째 데이터부터 2건 조회

SELECT mem_name, height
FROM member
ORDER BY height DESC
LIMIT 3,2; -- 3번째부터 2건 조회

DISTINCT

• 조회된 결과에서 중복된 데이터를 1개만 남기고 제거하는 키워드

예시 : 회원의 주소목록을 출력한다.

SELECT addr FROM MEMBER;

그럼 다음과 같이 결과가 나온는데, 중복되는 데이터가 있다.

addr
경기
경남
서울
경남
전남
서울
경북
서울
서울
경기

중복을 없애기 위해서 다음과 같은 실행문을 사용하면된다.

SELECT distinct addr FROM MEMBER;

결과는 다음과 같다.

addr
경기
경남
서울
전남
경북

DISTINCT는 조회할 열 이름 앞에 써준다.

GROUP BY

• 지정한 열의 데이터들을 같은 데이터끼리 묶어서 결과를 추출하도록 하는 키워드
• 주로 합계, 평균, 개수 등을 처리할 때 사용( 그래서 집계 함수과 함께 사용됨.)

함수명	설명
SUM()	합계를 구한다.
AVG()	평균을 구한다.
MIN()	최솟값을 구한다.
MAX()	최댓값을 구한다.
COUNT()	행의 개수를 구한다.
COUNT(DISTINCT)	행의 개수를 구한다.(중복은 1개만 인정한다.)

예시 : 회원별로 구매한 개수를 합치는 실행문

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;

예시 : 연락처가 있는 회원의 수 카운트

SELECT count(*) from member; -- 전체 회원 카운트 (10)

SELECT count(phone1) from member; -- 연락처가 있는 회원 카운트 (8)

예시 : 카테고리 개수 카운트(궁금해서 찍어 봄.)

SELECT DISTINCT group_name from buy; -- 카테고리명 중복 제거 후 목록으로 출력 -- 디지털,패션,서적
SELECT  count(distinct group_name) from buy; -- 카테고리명 중복 제거 후 카운트 출력 -- [distinct 열_이름] 사용.```

참고로 집계함수는 WHERE절과 같이 사용하면 구문 에러가 발생한다.

HAVING

• WHERE 절과 비슷하지만, GROUP BY와 함께 쓰임.
• WHERE절 처럼 조건을 제한하지만, 집계 함수에 대해서만 조건을 제한한다.
• GROUP BY절 다음에 위치해야한다.

예시 : 총 구매금액이 1000을 넘는 회원을 조회

SELECT mem_id "회원 아이디" , SUM(price*amount) "총 구매 금액"
FROM buy
GROUP BY mem_id
HAVING SUM(price*amount) > 1000; -- GROUP BY 다음에 위치
-- ORDER BY sum(price*amount) DESC; -- 금액이 높은 순대로 정렬할 때 사용.

2-3. 데이터 변경을 위한 SQL문

INSERT

데이터를 입력할 때 사용

INSERT 문의 기본 문법

INSERT INTO 테이블 [(열1, 열2, ...)] VALUES(값1, 값2, ...)

• 테이블 이름 다음에 나오는 열은 생략 가능함.
• 열 이름을 생략할 경우, VALUES 다음에 나오는 값들의 순서 및 개수는 테이블을 정의할 때의 열 순서 및 개수와 동일해야 함.

예시

CREATE TABLE hongong1 (toy_id INT, toy_name char(4), age INT);
-- 이와 같이 테이블이 만들어졌을 때, 열 이름을 생략하면 다음과 같이 하여 데이터를 입력할 수 있다.

INSERT INTO hongong1 VALUES(1, '우디', 25); 

위의 CREATE절로 테이블을 만들었을 때, 만약 id와 이름만 입력하고, 나이를 입력하고 싶지 않다면, 다음과 같이 INSERT문을 사용해주면 된다.

INSERT INTO hongong1(toy_id, toy_name) VALUES(2, '버즈');

열의 순서를 바꿔서도 입력 가능하다.
대신 열이름과 열에 대한 값의 순서를 동일하게 해야함.

INSERT INTO hongong(toy_name, age, toy_id) VALUES('쌩수', 6, 3);

결과

toy_id	toy_name	age
1	우디	25
2	버즈
3	쌩수	6

AUTO_INCREMENT

• 열을 정의할 때 1부터 증가하는 값을 자동으로 입력해줌.
• AUTO_INCREMENT로 지정하는 열은 반드시 PRIMARY KEY로 지정해줘야 함.

다음과 같이 테이블을 만들었을 때

CREATE TABLE hongong2 (
toy_id INT AUTO_INCREMENT PRIMARY KEY, -- AUTO_INCREMENT
toy_name char(4),
age int);

AUTO_INCREMENT로 선언한 PRIMARY KEY에 NULL값을 주어도, 값이 자동으로 입력됨.

INSERT INTO hongong2 VALUES(NULL, '보핍', 25);
INSERT INTO hongong2 VALUES(NULL, '이브이', 22);
INSERT INTO hongong2 VALUES(NULL, '썬더', 19);

SELECT * FROM hongong2;

마지막 SELECT 문 결과

toy_id	toy_name	age
1	보핍	25
2	이브이	22
3	썬더	19

이처럼 기본키(PRIMARY KEY)로 지정한 열이 자동으로 증가하면서 입력되었음을 알 수 있다.

AUTO_INCREMENT가 어디까지 증가했는지 확인하려면 다음 실행문을 사용하면 된다.

SELECT LAST_INSERT_ID();

AUTO_INCREMENT로 입력되는 다음 값을 조절할 수도 있다.
만약 100부터 시작되도록 하고 싶다면 다음과 같이 하면 된다.

ALTER TABLE hongong2 auto_increment=100; -- 100으로 수정

INSERT INTO hongong2 VALUES(NULL, '쌩수', 7); -- 입력하면 id가 100으로 됨.

결과는 다음과 같다.

toy_id	toy_name	age
1	보핍	25
2	이브이	22
3	썬더	19
100	쌩수	7

시작값을 1000으로 하고, 값이 1이 아니라, 3씩 증가시키려 할 때
이런 일은 드물겠지만, 책에서 소개해주기 때문에 기록해본다.

CREATE TABLE hongong3 (
toy_id int AUTO_INCREMENT PRIMARY KEY,
toy_name char(4),
age int);

ALTER TABLE hongong3 auto_increment=1000; -- 시작값을 1000으로 지정
SET @@auto_increment_increment=3; -- 증가값을 3으로 지정 -- 참고로 해당 변수는 시스템 변수이다.
-- 시스템 변수는 @@auto_increment_increment와 같이 앞에 '@@' 가 붙는다.
-- 특정 시스템 변수의 값을 확인하려면 다음과 같이 확인할 수 있다.
-- SELECT @@시스템_변수;
-- 전체 시스템 변수의 종룔르 알고 싶다면 다음과 같이 확인할 수 있다.
-- SHOW GLOBAL VARIABLES;

다음과 같이 데이터를 입력했을 때,

INSERT INTO hongong3 VALUES(NULL, '픽츄', 1);
INSERT INTO hongong3 VALUES(NULL, '피카츄', 13);
INSERT INTO hongong3 VALUES(NULL, '라이츄', 15);

SELECT * FROM hongong3; 으로 확인하면 다음과 같이 나온다.

한 줄로 작성 가능

오우.. 반복 학습 시킨 후, 줄일 수 있는 방법을 알려준다라.... 책 구성 짱이네

다음과 같이 입력한 세 줄을 한 줄로 입력가능하다.

INSERT INTO 테이블_이름 VALUES(값1, 값2, ...);
INSERT INTO 테이블_이름 VALUES(값1, 값2, ...);
INSERT INTO 테이블_이름 VALUES(값1, 값2, ...);

위 세 줄을 다음과 같이 한 줄로 줄일 수 있다.

INSERT INTO 테이블_이름 VALUES(값1, 값2, ...), (값3, 값4, ...), (값5, 값6, ...); 

INSERT 문을 일일이 입력하지 않고, 다른 테이블에 이미 입력된 데이터를 가져와서 입력을 해야할 때, SELECT문을 이용하여 다음과 같이 데이터를 입력하는 방법도 있다.

INSERT INTO 테이블_이름 (열_이름1, 열_이름2, ...)
SELECT 문 ;

단, SELECT 문의 열 개수와 INSERT할 테이블의 열 개수가 같아야 한다.

테이블의 구조를 출력해주는 기능 : DESC
DESC 명령어를 활용하면, TABLE이 어떻게 만들어졌는지 예상할 수 있다.

DESC 데이터베이스_이름.테이블_이름; -- 기본 구조

DESC hongong3; -- 결과는 아래

UPDATE

테이블의 행 데이터를 수정할 때 사용

기본 문법

UPDATE 테이블_이름
    SET 열1=값1, 열2=값2, ...
    WHERE 조건 ;

---

MySQL 워크벤치에서 UPDATE, DELETE 허용 해주기.

MySQL 워크벤치에서는 기본적으로 UPDATE 및, DELETE 구문을 허용해주지 않는다. 그래서 설정을 좀 변경해줘야 하는데, 다음과 같이 하면 된다.

Edit-Preferences 를 들어가면 Workbench Preferences가 나온다.
여기서,
SQL Editor-Other-'Safe Update (rejects UPDATE and DELETEs with no restrictions)' 를 체크해제 해준다.
애초에 괄호 문구가 update와 delete를 거부한다는 메세지네..;;
그리고 MySQL 워크벤치를 재실행해주면 된다.

---

※ UPDATE 문 사용시 주의사항 ※
WHERE절은 생략가능하다.
하지만, WHERE절을 생략하면, 모든 행의 값이 변경되는 참사가 벌어진다.
따라서 UPDATE를 사용할 때 WHERE절을 반드시 확인하자!

만약 여러 건이 수정될 것을 예상하는데, 그 중 상위 n건만 수정하고 싶다면, LIMIT을 붙여준다.

UPDATE 테이블_이름
SET 열1=값1, ... 
WHERE 조건
LIMIT n ;

조건에 맞는 데이터 중 상위 n건만 수정된다.

DELETE

테이블의 행 데이터를 삭제할 때 사용.

기본 문법

DELETE FROM 테이블_이름 WHERE 조건 ;

UPDATE와 마찬가지로 WHERE절을 생략하면, 모든 행이 삭제되는 참사가 벌어진다.
반드시 DELETE를 사용할 때도 WHERE절을 확인하자!!

UPDATE와 마찬가지로, 만약 여러 건이 삭제될 것을 예상하는데, 그 중 상위 n건만 삭제하고 싶다면, LIMIT을 붙여준다.

DELETE FROM 테이블_이름
WHERE 조건 
LIMIT n ;

조건에 맞는 데이터 중 상위 n건만 삭제된다.

DELETE VS DROP VS TRUNCATE

만약 테이블의 모든 행을 지울 때 다음과 같이 세 가지의 키워드가 있다.

1. DELETE
2. DROP
3. TRUNCATE

어떤 차이일까?

먼저, DELETE는 테이블은 놔두되, 테이블의 모든 행을 지운다. 대신 한 건씩 일일이 삭제되어 두 개의 키워드(DROP, TRUNCATE)보단 느리다.

DROP은 테이블 자체를 삭제한다. 그렇기 때문에 순식간에 삭제한다.
대신 테이블은 새로 만들어야 한다.

TRUNCATE는 결과적으로는 테이블은 놔두되, 모든 행을 지우는 DELETE와 같은 효과를 가지지만, DELETE보단 훨씬 빠르다는 장점이 있다.

따라서, 테이블의 데이터를 삭제할 때, 테이블 자체를 지우려면 DROP을, 테이블은 놔두고, 모든 행 데이터를 지우려면, TRUNCATE를 사용하자!!

3. AUTO_INCREMENT 실험

AUTO_INCREMENT부분에서 AUTO_INCREMENT를 100부터 주었고, INSERT를 입력하면, 100부터 증가하는 것을 확인했다.

id는 3 이후, 100으로 나오는데 만약 1로 수정하면 어떻게 될까?

ALTER TABLE hongong2 auto_increment=1;

성공적으로 명령문이 실행되었다.

그리고 INSERT문을 넣어보았다.

INSERT INTO hongong2 VALUES(NULL, '쓰앵수', 7);

역시 성공적으로 들어갔다.
결과는?

toy_id	toy_name	age
1	보핍	25
2	이브이	22
3	썬더	19
100	쌩수	7
101	쓰앵수	7

101로 들어간 것을 볼 수 있다.

🤔 "1이 있기 때문에 101로 들어간걸까?" 생각하여 4로 주고, 다시 넣어보았다.
그리고 역시 데이터 넣어보았다.

ALTER TABLE hongong2 auto_increment=4;
INSERT INTO hongong2 VALUES(NULL, '쓰앵수', 7);

결과는??

toy_id	toy_name	age
1	보핍	25
2	이브이	22
3	썬더	19
100	쌩수	7
101	쓰앵수	7
102	쓰앵수	7

102로 들어갔다.
크게 줬다가 적게 주었더라도, 최댓값으로 들어가는 것을 확인할 수 있다.

최근 인덱스를 확인해보면 다음과 같이 나온다.

SELECT LAST_INSERT_ID();

LAST_INSERT_ID()
103

4. 요약

SQL의 기본문법에 대해서 알아보았다.
SELECT문의 기본구조부터 시작해서 WHERE, ORDER BY, LIMIT, GROUP BY, HAVING 절을 알아보았다.

INSERT, UPDATE, DELETE를 알아보았고,
중간에 AUTO_INCREMENT에 대한 실험도 해보았다.

DELETE , DROP, TRUNCATE 중
테이블을 삭제할 때는 DROP을,
테이블은 놔두되, 모든 행 데이터를 삭제할 때는 TRUNCATE를 사용하자!