[MySQL] w3schools 및 MySQL 워크밴치에서 Try mysql (SELECT문, INSERT INTO문, UPDATE문, DELETE문)

1. SQL

모든 서버 프로그램은 SQL문을 실행시킬 수 있게끔, 제공 해준다. 사용자가 뷰에서 카테고리를 생성하겠다고 입력하고 생성버튼 누르면 필요한 데이터를 서버로 보내게 될 것이고, 서버에서는 클라이언트에서 전송한 데이터를 받아서 내부적으로는 아래서 배우게될 sql문을 실행해서 클라에서 던져준 값들을 매핑해서 테이블에 넣으면 데이터베이스에 등록된다. 따라서 모든 쿼리문은 nodeJs나 Java에서 쓸 수 있는 것이다. 정말 어려운 프로젝트(통계 시스템 구축 같은 경우는, 100~200줄 기본)에서는 데이터 하나 조회하는데 쿼리문을 천줄 짜야할 수도 있다! 특히 Select 쿼리문은 잘못 짜면 1분 만에 조회할 수 있는 것을 찾는데 몇십분이 걸릴 수도 있다. 그러나 사실 통계 시스템같은 것이 아닌 이상 아무리 쿼리문을 많이 짜도 10줄이 넘지는 않을 것이다.

 

결국 데이터는 한건한건 데이터베이스에 들어가는 것이고, 나중에 데이터가 여러 테이블에 흩어져 있는데, 흩어져 있는 데이터를 내가 원하는 모양으로 가공할 수 있어야 한다. 

SQL은 데이터베이스를 조작하기 위한 표준 스크립트 언어이다.

SELECT : 데이터 조회

UPDATE : 데이터 수정

DELETE : 데이터 삭제

INSERT INTO : 데이터 추가

CREATE DATABASE : 데이터베이스(스키마) 생성

ALTER DATABASE : 데이터베이스(스키마) 수정

CREATE TABLE : 테이블 생성

ALTER TABLE : 테이블 수정

DROP TABLE : 테이블 삭제

데이터베이스는 검색 속도를 높이기 위해 컬럼 별로 인덱스를 생성할 수 있다. (메모리 차지하므로 꼭! 성능을 높여야 하는 경우만 인덱싱 처리를 해야 한다)

CREATE INDEX : 인덱스 생성

DROP INDEX : 인덱스 삭제

 

2. 쿼리문

(1) SELECT문

SELECT 뒤에 어떤 컬럼들을 가지고 올 것인지, FROM 뒤에 어떤 테이블에서 가져올 것인지

컬럼 전체 조회

SELECT * FROM 테이블명

 

컬럼 일부만 조회

SELECT 컬럼1, 컬럼2, 컬럼3, ... FROM 테이블명

ex) SELECT

 

custom_id와 custom_name만 필요한데, 굳이 모든 컬럼의 데이터를 다 가지고 오면, 그만큼 데이터베이스에서 서버로 전송하는 데이터 패킷 양이 많아질거고, 서버에서 클라이언트로 전달해주는 패킷 양이 많아질 것이다. 그럴 때는 가져오고 싶은 컬럼만 작성해주면 된다.

(2) SELECT문 조건절

특정 테이블에 등록된 데이터 중에서 어떤 조건을 만족하는 데이터만 뽑아오고 싶다면 ?

가령 멕시코에 사는 고객만 데려오고 싶다면

SELECT * FROM Customers WHERE Country = 'Germany';

SELECT * FROM Customers WHERE Country = 'UK' AND City = 'London';

조건에 맞는 데이터만 조회

SELECT * FROM 테이블명 WHERE 조건

(가져와라, 모든 컬럼을 특정 테이블에서, 어떤 조건에 있는 데이터들만)

 

조건 연산자(AND, OR, NOT)

SELECT * FROM 테이블명 WHERE 조건1 AND 조건2 AND 조건3

SELECT * FROM 테이블명 WHERE 조건1 OR 조건2 OR 조건3

SELECT * FROM 테이블명 WHERE NOT 조건

 

기타 예제

SELECT * FROM Customers WHERE Country = 'UK' AND City = 'London' or Country = 'France';

SELECT * FROM Customers WHERE Not Country = 'UK'

(3) 정렬

SELECT * FROM 테이블명 ORDER BY 칼럼명

칼럼명이 정렬된다.

조건에 맞는 데이터만 조회

SELECT 컬럼1, 컬럼2, 컬럼3, ... FROM 테이블명 ORDER BY 컬럼1, 컬럼2 ASC

SELECT 컬럼1, 컬럼2, 컬럼3, ... FROM 테이블명 ORDER BY 컬럼1, 컬럼2 DESC

SELECT 컬럼1, 컬럼2, 컬럼3, ... FROM 테이블명 ORDER BY 컬럼1 ASC, 컬럼2 DESC

SELECT * FROM Customers ORDER BY Address

SELECT * FROM 테이블명 ORDER BY 칼럼명 DESC

칼럼명이 역순으로(내림차순) 정렬된다.

SELECT * FROM Customers ORDER BY Address DESC

SELECT * FROM 테이블명 ORDER BY 칼럼명 ASC

칼럼명이 오름차순 정렬된다.

국가별로 정렬하고, 그 국가별 안에서는 PostalCode로 정렬하고 싶다.

SELECT * FROM Customers ORDER BY Country, PostalCode

이렇게 하면 국가를 먼저 정렬하고, 그 안에서 PostalCode로 정렬한다.

국가는 ASC로 하고, 그 안에서 다른 컬럼을 DESC로 할 수도 있다.

SELECT * FROM Customers ORDER BY Country DESC, PostalCode ASC

(4) INSERT INTO문

컬럼 일부만 추가

INSERT INTO 테이블명 (컬럼1, 컬럼2, 컬럼3, ...) 

VALUE (값1, 값2, 값3, ...)

 

컬럼 전체 추가 (컬럼 전체 값을 순서대로)

INSERT INTO 테이블명

VALUE (값1, 값2, 값3, ...)

 

INSERT INTO Categories (CategoryName, Description)
VALUES ('Computer', 'Notebook, Desktop')

등록되어 9개가 될 것이다.

mysql 에서 직접 해보도록 한다. 빨간 동그라미 클릭

이런 창이 뜬다.

INSERT INTO dev.product_category (category_name, category_description)
VALUES ('Computer', 'Notebook, Desktop')

번개모양 클릭하면 실행된다.

추가된 것을 확인할 수 있다!

실행하고 싶은 쿼리문 드래그하고 번개 누르면 그 쿼리문만 실행된다.

만약에 특정 컬럼만 데이터를 등록할 것이 아니고 전체 데이터를 다 등록하겠다면 컬럼 정의를 다 빼도 된다. 아래와 같이. 

INSERT INTO dev.product_category //이 뒤로 안 써도 됨
VALUES ('2', 'Computer', 'Notebook, Desktop') //대신에 밸류쪽에 컬럼의 순서대로 모든 데이터를 넣어줘야 함

INSERT INTO dev.product_category
VALUES (2, 'Mobile','SmartPhone,Tablet')

컬럼에 대한 정의가 빠지면 모든 컬럼 데이터가 들어가줘야하는 것이다. 근데 사실 Auto increment는 대체로 개발자가 직접 넣지 않는다!

잘 들어갔다.

(5) UPDATE 문

특정 조건을 갖는 데이터를 찾아서 컬럼 값 수정

UPDATE 테이블명

SET 컬럼1 = 값1, 컬럼2 = 값2, 컬럼3 = 값3,

WHERE 조건 (단 이때 조건은 primary key여야 한다)

 

예를 들면 카테고리 리스트가 2건이 떴는데, 2번째 카테고리 누르면(PK 클릭해서 들어옴) 해당 모달창 뜨고 상세정보 뜰텐데 where 절에 pk 딱 걸어주고 내가 원하는 필드만 수정해줄 수 있는 것이다.

UPDATE dev.product_category
SET category_description = 'Smartphone, Table, Watch'
WHERE product_category_id = 2

UPDATE dev.product_category
SET category_description = 'Smartphone, Table, Watch'
WHERE category_name = 'Mobile'

 

product_category_id가 2인 애 찾아내서 category_description를 Smartphone, Table, Watch로 바꾸어버린다. dev.product_category 테이블에서!

(5) DELETE 문

테이블에 있는 데이터가 사라진다. (drop은 테이블 자체가 사라지는 것)

그런데 순수하게 삭제하지 않고 active_yn을 두어서 active_yn 값만 바꾸는 식으로 삭제를 표시하는 (실제 데이타에서 사라지는 것이 아닌 것임) 식으로 업데이트 하는 경우도 있다. 사용자는 삭제되었다고 알고 있지만 테이블에는 고스란히 남아 있을 수 있다는 것이다. 그래서 삭제라는 행위가 이루어질 때, 의미가 없는 데이터면 진짜 날려버리기 위해 delete 문을 쓰는 것이고, 사용자의 실제 삭제 의사와는 상관없이 데이터는 계속 보관해놔야 의미있게 쓸 수 있기 때문에 보관하겠다면 화면상에서는 삭제되었을 지라도 실제 데이터베이스는 활성화 여부 칼럼 하나를 추가로 두어서 데이터가 남아있게끔 처리를 많이 하기도 한다.

특정 조건을 갖는 데이터를 찾아서 데이터 삭제

DELETE FROM 테이블명 WHERE 조건

테이블 전체 데이터 삭제

DELETE FROM 테이블명

DELETE FROM dev.product_category

위와 같이 쓰면... 테이블에 들어가 있는 모든 데이터가 사라진다.

다만 외래 키 제약 조건이 존재하는 경우는 안 된다. 테이블에 외래 키 제약 조건이 설정되어 있으면, 다른 테이블에 있는 레코드에 대한 참조 문제가 발생할 수 있기 때문에 이 경우에는 외래 키 제약 조건을 해제하거나 관련된 레코드를 먼저 삭제해야 한다. 

DELETE FROM dev.product_category WHERE product_category_id = 2

이렇게 하면 product_category_id = 2인 애만 지워진다.

잘 지워졌다!

INSERT INTO dev.product_category (category_name, category_description)
VALUES ('Coffee', 'caffeLatte, americano');

여기서 중요한 점은, 다시 추가하면

위와 같이 id가 1,2가 아니라 1,3이라는 것을 알 수 있다. 즉, 2번이 지워졌지만 데이터베이스는 몇번까지 늘어났었는지를 가지고 있는 것이다. 지웠다고 해서 그 흔적까지 사라지는 것은 아니라는 뜻이다. 

 

(6) SELECT문  LIKE 검색

포함해야하는 값이 있을 때, 사용자가 인풋박스에서 무엇을 조회하는 것은 대부분 이 like 쿼리문이 들어간다.

조건에 맞는 데이터만 조회

SELECT * FROM 테이블명 WHERE 조건 LIKE 패턴

WHERE 컬럼1 LIKE 'a%'	컬럼1의 값이 'a'로 시작하는 모든 값
WHERE 컬럼1 LIKE '%a'	컬럼1의 값이 'a'로 끝나는 모든 값
WHERE 컬럼1 LIKE '%a%' : 가운데든 끝이든 처음이든 a가 있다면	컬럼1의 값이 'a'가 들어가 있는 모든 값
WHERE 컬럼1 LIKE '_a%' : _ 하나가 한 문자를 의미한다	컬럼1의 값이 두번째 문자가 'a'인 모든 값
WHERE 컬럼1 LIKE 'a_%'	컬럼1의 값이 'a'로 시작하고 문자 길이가 2개 이상인 모든 값
WHERE 컬럼1 LIKE 'a__%'	컬럼1의 값이 'a'로 시작하고 문자 길이가 3개 이상인 모든 값
WHERE 컬럼1 LIKE 'a%o'	컬럼1의 값이 'a'로 시작하는 'o'로 끝나는 모든 값
WHERE 컬럼1 LIKE '__n%'	세번째 문자가 n
WHERE 컬럼1 LIKE '%m_'	끝에서 두번째 문자가 m

% 자리에 문자가 있을 수도 없을 수도 있다.

SELECT * FROM Categories WHERE CategoryName LIKE '%on%';

SELECT * FROM Categories WHERE CategoryName LIKE '%s';

SELECT * FROM Categories WHERE CategoryName LIKE '__n%';

SELECT * FROM Categories WHERE CategoryName LIKE '%e_';

(7) OR.. OR... 보다 In () SELECT문 IN 연산자

IN() 안의 값 중 하나를 만족하는 데이터만 조회

SELECT * FROM 테이블명 WHERE 컬럼1 IN (값1, 값2, 값3, ...)

SELECT * FROM 테이블명 WHERE 컬럼1 IN (SELECT문)

 

NOT IN() 안의 값이 아닌 데이터만 조회

SELECT * FROM 테이블명 WHERE 컬럼1 NOT IN (값1, 값2, 값3, ...)

아래와 같이 OR.. OR... OR.. 이 들어오는 경우는 사실 고정된 값이 아니고 사용자의 선택에 따라서 결괏값이 동적으로 바뀌는 경우이다. 혹은 체크박스에서 중복선택 후 조회 로직을 생각해볼 수 있다.

차라리 아래와 같이 In(괄호로 묶기)가 합리적이다. 논리적으로 똑같다. 

아래와 같은 NOT IN은 멕시코도 스페인도 UK도 아닌 값들만 가져오게 된다.

(8) SELECT문 BETWEEN 연산자

이상 이하

값1, 값2 사이의 데이터만 조회

SELECT * FROM 테이블명 WHERE 컬럼1 BETWEEN 값1 AND 값2

값1, 값2 사이 값이 아닌 데이터만 조회

SELECT * FROM 테이블명 WHERE 컬럼1 NOT BETWEEN 값1 AND 값2

SELECT * FROM Products WHERE Price BETWEEN 20 AND 30

가격이 20보다 크고 30보다 낮은 데이터

SELECT * FROM Products WHERE Price NOT BETWEEN 20 AND 30

20부터 30 사이의 가격을 제외한 데이터

(9) SELECT문 조회 건수 지정

LIMIT는 검색어 순위, 페이징 처리{(Page번호 -1)*한페이지에 보여주는 수}에 쓰인다.

조건에 맞는 데이터만 조회

SELECT * FROM 테이블명 LIMIT 건수

SELECT * FROM 테이블명 WHERE 조건 LIMIT 건수

가장 비싼 물건 5개를 가져오려면?

SELECT * FROM Products LIMIT 5 //5건만 가져오지만 최고가 5개는 아니다

가격 기준으로 Descending으로 정렬하고 LIMIT 5 걸어주면 된다.

SELECT * FROM Products ORDER BY Price DESC LIMIT 5

반대로 가장 낮은 가격의 물건 5개는 아래와 같이 작성해주면 된다.

SELECT * FROM Products ORDER BY Price ASC LIMIT 5

<페이징 처리 예시>

SELECT * FROM Products LIMIT 0, 5 // 앞글자에 페이징하고 싶은 숫자만큼 더하면 마치 페이징 처리 같아진다.
첫번째 숫자가 인덱스, 두번째 숫자가 갯수라고 생각하면 된다.

(10) SELECT문 MIN(), MAX()

단점이 키명이 바뀐다는 것이다. 그래서 Alias(별칭)을 주도록 한다.

클라와 조율해서 적절한 키명으로 바꾸면 된다.

최소값 조회

SELECT MIN(컬럼1) FROM 테이블 WHERE 조건

SELECT MIN(컬럼1) AS 별칭 FROM 테이블명 WHERE 조건

최대값 조회

SELECT MAX(컬럼1) FROM 테이블 WHERE 조건

SELECT MAX(컬럼1) AS 별칭 FROM 테이블명 WHERE 조건

SELECT MIN(Price) FROM Products

SELECT MAX(Price) FROM Products

SELECT ProductID, ProductName, MAX(Price) FROM Products

아래와 같이 별칭을 주면 Price라는 정상적인 키명이 나온다.

SELECT ProductID, ProductName, MAX(Price) AS Price FROM Products

(11) 평균

평균값 조회

SELECT AVG(컬럼1) FROM 테이블 WHERE 조건

SELECT AVG(컬럼1) AS 별칭 FROM 테이블명 WHERE 조건

SELECT AVG(Price) FROM Products

(12) 합계

평균값 조회

SELECT SUM(컬럼1) FROM 테이블 WHERE 조건

SELECT SUM(컬럼1) AS 별칭 FROM 테이블명 WHERE 조건

SELECT SUM(Price) FROM Products

(13) 총 갯수

건수 조회

SELECT COUNT(컬럼1) FROM 테이블 WHERE 조건

SELECT COUNT(컬럼1) AS 별칭 FROM 테이블명 WHERE 조건

SELECT COUNT(*) FROM Products

2022년 3월 1일 이후부터 3월 31일 이전 사이에 등록한 고객

SELECT COUNT(*) FROM Customers WHERE CreateDate >= '20220301' AND CreateDate <= '20220331'

프로덕트 레코드가 77이므로 맞음

(14) SELECT문 GROUP BY

GROUP BY는 GROUP BY컬럼명일 때, 컬럼을 기준으로 같은 컬럼값을 가진 아이들을 모두 그룹핑을 해준다.

국가별로 고객인 몇명인지 알고싶다면?

아래와 같이 국가마다 select count 노가다해야 할까?

SELECT COUNT(*) FROM Customers WHERE Country = 'Mexico'

SELECT COUNT(*) FROM Customers GROUP BY Country

그런데 어떤 항목의 카운트인지를 모르겠으므로 아래와 같이 작성해준다. 

SELECT Country, COUNT(*) AS CountryCustomerCount FROM Customers GROUP BY Country

어떤 국가인지 알기위해 SELECT Country해준 것.

동일한 값을 가지고 있는 데이터를 하나의 그룹으로 묶어서 COUNT(), MAX(), MIN(), SUM(), AVG() 같은 function 결과 조회

 

컬럼2 값이 동일한 그룹별로 데이터 수 조회

SELECT COUNT(컬럼1), 컬럼2 FROM 테이블 WHERE 조건 GROUP BY 컬럼2

 

국가별 통계, 각종 통계에 그룹별로 묶어서 데이터 수 조회할 때 아주 요긴하게 많이 쓰인다.

SELECT CategoryID, AVG(Price) FROM Products GROUP BY CategoryID

 

(15) JOIN문

두개 이상의 테이블에서 데이터를 조회할 때 사용

INNER JOIN(양쪽 전부 데이터가 NULL이 아니라, 양쪽다 있는 경우)을 가장 많이(90% 이상) 쓰게 된다. left join은 1번 테이블이 무조건 다 나옴(데이터 중에 null이 있더라도), right 는 left의 정반대

SELECT 테이블1.컬럼1, 테이블1.컬럼2, 테이블2.컬럼1 FROM 테이블1

INNER JOIN 테이블2 ON 테이블1.컬럼1=테이블2.컬럼1

가령... 아래 표에서 CategoryName을 알고 싶다면 Category 테이블도 필요하다.

Products.* : Products 테이블에 있는 모든 컬럼을 가지고 오겠다.

Categories.CategoryName : Categories 테이블에 있는 CategoryName 컬럼을 가지고 오겠다.

SELECT Products.*, Categories.CategoryName FROM Products INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID;

테이블을 두개 쓰고 있으므로 반드시 테이블명.컬럼명 형식을 지켜주어야 한다.

마지막 칼럼이 깔끔하게 추가되었다.

INNER JOIN : 양쪽에 같은 값이 있는 애를 찾아서 걔를 기준으로 데이터를 가지고 올 수 있게 해줌

SELECT T1.*, T2.CategoryName FROM Products T1, Categories T2 Where T1.categoryID = T2.categoryID

위와 같이 써도 Inner Join 과 똑같을 뿐더라 더 간결

SELECT T1.*, T2.CategoryName, T3.SupplierName FROM Products T1, Categories T2, Suppliers T3 WhERE T1.categoryID = T2.categoryID AND T1.SupplierID = T3.SupplierID