2025.03.04 SQL 공부

GROUP BY

 

대상 칼럼을 기준으로 그룹으로 묶고, 집계함수를 이용하여 값을 나타낸다.

 

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

 

SELECT mem_id, SUM(amount) FROM buy GROUP BY mem_id;

buy 테이블에서 mem_id로 그룹화 시켜 mem_id와 amount의 합 조회

 

SELECT mem_id '회원 아이디', SUM(price*amount) '총 구매 금액' FROM buy GROUP BY mem_id;

buy 테이블에서 mem_id로 그룹화 시켜 mem_id를 '회원 아이디'로, price*amount의 값을 '총 구매 금액'이라는 별칭으로 바꾸어 조회

 

SELECT AVG(amount) '평균 구매 개수' FROM buy;

buy 테이블에서 amount의 평균을 구해 '평균 구매 개수' 라는 별칭으로 조회

 

 

COUNT()의 괄호 안에는 칼럼명 또는 *을 쓸수 있는데, 칼럼명을 쓰면 null 값을 세지 않고, *을 쓰면 모든 행을 센다. 또는 PK키는 null이 올수 없기 때문에 PK키를 가진 칼럼명을 쓰면 *과 동일한 결과를 얻을수 있다.

---

 

 

 

 

HAVING

 

GROUP BY와 함께 쓰이며, WHERE과 비슷하게 조건식을 나타내지만 GROUP BY와 함께 쓸수 없는 WHERE을 대신하여 쓰이고, GROUP BY의 집계함수를 조건식에 쓸수있다.

 

SELECT mem_id '회원 아이디', SUM(price*amount) '총 구매 금액'

FROM buy

GROUP BY mem_id

HAVING SUM(price*amount) > 1000;

 

buy테이블에서 mem_id로 그룹화 시키고, price*amount의 합이 1000 초과인 mem_id와 SUM(price*amount) 조회

---

 

 

데이터 변경을 위한 SQL문

 

CREATE : INSERT
UPDATE : UPDATE

DELETE : DELETE

 

INSERT INTO 테이블명 (열1, 열2, ...) VALUES(값1, 값2, ...);

 

INSERT 테이블명 VALUES(값1, 값2, ...);

 

INSERT 테이블명 (열2, 열3) VALUES(값2, 값3);

 

위의 방법은 모두 INSERT 문으로 쓸수 있는 방법들이다.

 

첫번째 방법은 열의 이름과 값을 매치시켜 넣는 방법으로 열과 값의 개수가 동일하고 정확히 매치되어야 한다.

 

두번째 방법은 열의 개수를 알때 값만 넣는 방법으로, 이 방법도 값의 개수를 열에 맞춰야 한다.

 

세번째 방법은 특정 열의 이름을 지정하고 값을 넣는 방법으로, 열의 개수와 상관없이 값을 넣을 열의 개수와 값의 개수만 맞으면 된다. 

 

테이블을 생성할때 칼럼명에 AUTO INCREMENT를 설정하면 자동으로 숫자 1부터 +1씩 하여 숫자를 부여하는데, 이를 id로 쓸경우에는 INSERT로 값을 넣을때 그 부분에 NULL을 넣으면 된다.

 

(열1 AUTO INCREMENT, 열2, 열3, 열4)

VALUES(NULL, 값2, 값3, 값4)

 

 

SELECT를 이용해서 INSERT 하기

 

다른 테이블에 이미 입력된 값을 SELECT를 통해 가져와서 새 테이블에 집어넣는 방법이다.

 

INSERT INTO test_table SELECT name, addr from test.member;

 

test DB안의 member 테이블에 있는 이름과 주소를 가져와서 test_table에 삽입

 

단 이 경우에는 test_table에는 name이랑 addr이란 칼럼이 존재하고, 데이터타입이 일치해야한다.

---

 

 

 

UPDATE

 

입력된 데이터를 수정한다.

 

업데이트와 딜리트는 기본적으로 WHERE절을 반드시 사용해야 한다.

 

UPDATE 테이블 SET 칼럼1 = 값1, 칼럼2, 값2, ... WHERE 조건;

 

테이블에서 조건에 해당하는 열의 값을 변경한다.

 

 

만약 WHERE 절이 없으면 모든 행에 대해서 칼럼1 = 값1, 칼럼2 = 값2가 적용된다.

---

 

 

DELETE

 

입력된 데이터를 삭제한다.

 

업데이트와 동일하게 WHERE절을 반드시 사용해야 한다.

 

DELETE FROM 테이블 WHERE 조건;

 

테이블에서 조건에 해당하는 행을 삭제한다.

 

WHERE이 없으면 테이블의 모든 데이터를 삭제한다.(테이블은 유지)

 

 

데이터를 지우는 방법에는 3가지가 있는데 DELETE, DROP, TRUNCATE이다.

 

DELETE는 행을 하나하나씩 삭제해서 수행속도가 느리고 테이블 자체는 유지한다.

 

DROP은 값을 지우지 않고 테이블 자체를 삭제시켜 속도가 빠르다.

 

TRUNCATE는 테이블은 남기고 데이터만 지우지만 속도가 빠르다.

---

 

 

 

데이터 형식

 

SQL에서는 자바와 비슷한 데이터타입을 사용한다,

 

정수형 데이터 타입에는 TINYINT, SMALLINT, INT, BIGINT가 있으며 각각 자바의 BYTE, SHORT, INT, LONG에 대응하고 자바와 동일하게 대부분 INT가 주로쓰인다.

 

-128~127의 값 범위를 가지는 TINYINT는 UNSIGNED를 적용하면 0~255까지 표현하도록 할수있다.

 

 

문자형에는 CHAR과 VARCHAR가 있고, 이 둘의 차이는 CHAR은 할당한 문자의 크기를 다 채우지 않아도 같은 양의 용량을 차지하는 반면에 VARCHAR은 설계된 크기보다 적으면 남은 용량을 반납한다.

 

그렇기 때문에 CHAR은 용량이 고정인 대신 빠르고, VARCHAR은 가변인 대신 남은 양을 반납하기 때문에 느리다.

 

자릿수가 고정인 경우에는 CHAR이, 동적인 경우에는 VARCHAR이 효율적이지만 최근에는 VARCHAR을 많이 쓴다.

 

대량의 데이터에는 TEXT와 BLOB이라는 데이터 타입을 사용한다.

 

TEXT는 자막이나 책등의 텍스트를 담는데 적합하고, BLOB은 2진수 파일인 이미지, 영상, 실행파일 등을 담는데 적합하다.

 

 

실수형은 자바와 동일하게 FLOAT과 DOUBLE을 사용한다.

 

날짜의 경우에도 DATE, TIME, DATETIME을 사용한다.

---

 

 

변수 선언

 

SQL에서는

 

SET @변수이름 = 값; 의 형태로 변수를 선언하고,

 

SELECT @변수이름; 의 형태로 변수의 값을 출력한다.

 

서브쿼리와 동일하게 변수를 이용해서 조건식의 값을 동적으로 만들수도 있고, LIMIT 값을 동적으로 할수도 있다.

---

 

 

데이터 형 변환

 

문자를 정수로 바꾸거나 정수를 문자로 바꾸는것을 형변환 이라고 한다.

 

함수를 이용한 명시적 형변환

 

CAST(값 AS 데이터_형식 [(길이)]) 또는 CONVERT(값, 데이터_형식 [(길이)])

 

 

SELECT CAST('2025$01$01' AS DATE);

 

날짜가 2025-01-01로 형변환된다.

 

 

암시적형변환(자동)

 

SELECT '100' + '200';

 

문자형 2개를 더했지만 암시적으로 +를 인식하고 숫자로 바꾸어 300이 나온다.

 

SELECT CONCAT('100', '200');

 

이 경우에는 CONCAT이 문자형을 붙이기 때문에 값은 100200이 된다.

 

---

 

JOIN

 

두개의 테이블을 하나로 묶어서 하나의 결과를 만들어 내는 것이다.

 

 

내부 조인(INNER JOIN)

 

가장 많이 사용되는 조인으로, 보통 그냥 조인이라 부르면 내부 조인을 의미한다.

 

 

SELECT 테이블1.칼럼1, 테이블1.칼럼2, 테이블2.칼럼1

FROM 테이블1

JOIN 테이블2

ON 테이블1.칼럼2 = 테이블2.칼럼2; -- 테이블1.칼럼2와 테이블2.칼럼2는 겹치는 부분이다.

 

SELECT A.name, A.id, B.department

FROM member A

INNER JOIN company B

ON A.id = B.id;

 

이렇게 표현할 수 있다. 내부 조인의 경우 JOIN 앞에 INNER는 생략 가능하다.

 

최종적으로는 두 테이블에서 name, id, department 칼럼을 가져와서 하나의 테이블 처럼 만들어서 조회하는것과 같은 결과를 보여준다.

 

내부 조인의 특징은 조회하는 두 테이블의 칼럼에 서로 데이터가 존재하는 부분만 조인을 한다.

 

만약 A 테이블의 id가 1번부터 5번까지 있고, B 테이블의 id가 1번부터 7번까지 있다면, 서로 데이터가 존재하는 1번부터 5번까지만 출력되고 나머지 6번, 7번은 표시되지 않는다.

 

 

 

외부 조인(OUTER JOIN)

 

내부 조인과 다르게 두 테이블에 데이터가 존재하지 않아도 출력되며, 이 경우 데이터가 없으면 NULL로 표현된다.

 

외부 조인은 LEFT, RIGHT, FULL 과 함께 OUTER를 붙여서 사용하며, OUTER 앞에 붙이는 종류에 따라 결과가 달라진다.

 

LEFT, RIGHT, FULL은 각각 메인이 될 테이블을 의미하며, 편의상 from에 붙는 테이블이 왼쪽, join에 붙는 테이블을 오른쪽, full은 둘다 라고 생각하면 편하다.

 

SELECT A.name, A.id, B.department

FROM member A

LEFT OUTER JOIN company B

ON A.id = B.id;

 

이 경우 레프트 아우터 조인이기 때문에 레프트 즉 from에 있는 A를 기준으로 테이블이 조인된다.

 

기준이 된 A는 모든 행(5줄)이 출력되고, B는 A를 따라서 5줄만 출력되게 된다.

 

이 경우에는 내부 조인과 결과가 같게 나온다.

 

SELECT A.name, A.id, B.department

FROM member A

RIGHT OUTER JOIN company B

ON A.id = B.id;

 

라이트 아우터 조인이 되게 되면 결과가 달라지는데

 

이 경우에는 join에 있는 B를 기준으로 테이블이 만들어지며, 기준인 B는 완전히 출력되고(7줄), A는 가지고 있는 5줄과 나머지 2줄은 null을 포함해서 출력되게 된다.

 

 

FULL OUTER JOIN은 기준을 A와 B 모두에게 준다 그렇기 때문에 이 경우에는 B가 더 많은 행을 가지고 있기 때문에 라이트 조인과 같은 결과가 나온다.

 

 

 

자체 조인

 

하나의 테이블을 마치 2개의 테이블인것 처럼 사용해서 조인을 하는 방법이다.

 

하나의 테이블인데 테이블 내에 있는 정보가 칼럼을 하나 더 두고 같이 붙이면 좋을것 같을때 주로 사용한다.

 

예를 들면 회사의 member 테이블에서 한 테이블 안에 관리자와 사원의 전화번호가 저장되어 있는데 물리적으로 멀리 있어서 참고하기 힘들때 사원 옆에 관리자 이름과 전화번호를 붙여두면 편하지 않을까? 싶은 경우에 사용한다.

 

 

SELECT A.emp '직원', B.emp '관리자', B.phone '관리자 전화번호'

FROM emp_table A

JOIN emp_table B

ON A.manager = B.emp

WHERE A.emp = '팀장';

 

하나의 emp 테이블을 마치 2개 인것처럼 나눠서 직원과 관리자를 다른 테이블인것 처럼 구분해서 조인을 시킨것이다.

 

이러면 직원, 관리자, 관리자 전화번호 칼럼이 붙어있어서 가독성이 좋아지게 된다.