이 글은 혼자 공부하는 SQL(저자 : 우재남)의 책과 유튜브 영상을 참고하여 개인적으로 정리하는 글임을 알립니다.

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

두 테이블을 엮어야만 원하는 형태가 나오는 경우도 많다.

인터넷 마켓 데이터베이스의 회원 테이블과 구매 테이블을 예로 들 수 있다.

회원 테이블에는 회원의 이름과 연락처가 있고, 구매 테이블에는 회원이 구매한 물건이 있다.

물건을 배송하려면 회원 테이블의 회원 이름과 연락처, 구매 테이블의 회원이 구매한 물건에 대한 정보가 함께 필요하다.

이렇게 두 테이블을 엮어서 하나의 배송을 위한 정보를 추출하는 것이 대표적인 join이다.

내부 조인(Inner Join)

두 테이블을 연결할 때 가장 많이 사용되는 것이 내부 조인이다.

그냥 조인이라 부르면 내부 조인을 의미하는 것이다.

두 테이블의 조인을 위해서는 기본키(PRIMARY KEY, PK)와 외래키(FOREIGN KEY, FK) 관계로 맺어져야 하고, 이를 일대다 관계라고 한다.

일대다 관계의 이해

두 테이블의 조인을 위해서는 테이블이 일대다(one to many) 관계로 연결되어야 한다.

데이터베이스의 테이블은 하나로 구성되는 것보다는 여러 정보를 주제에 따라 분리해서 저장하는 것이 효율적이다.

이 분리된 테이블은 서로 관계를 맺고 있다.

이러한 대표적인 사례가 인터넷 마켓 데이터베이스(market_db)의 회원 테이블과 구매 테이블이다.

아래와 같이 morket_db에서 회원 테이블의 아이디와 구매 테이블의 아이디는 일대다 관계이다.

일대다 관계란 한쪽 테이블에는 하나의 값만 존재해야 하지만, 연결된 다른 테이블에는 여러개의 값이 존재할 수 있는 관계를 말한다.

예를 들어, 회원 테이블에서 블랙핑크의 아이디는 BLK로 1명(1, one) 밖에 없다. 그래서 회원 테이블의 아이디를 기본 키로 지정했다.

구매 테이블의 아이디에서 3개의 BLK를 찾을 수 있다. 즉, 회원은 1명이지만 이 회원은 구매를 여러 번(다, many)할 수 있는 것이다.

그래서 구매 테이블의 아이디는 기본 키가 아닌 외래 키로 설정했다.

일대다 관계는 주로 기본 키와 외래 키 관계로 맺어져 있다. 그래서 일대다 관계를 PK-FK 관계라 부르기도 한다.

내부 조인의 기본

내부 조인의 형식은 아래와 같다.

구매 테이블에는 물건을 구매한 회원의 아이디와 물건 등의 정보만 있다.

이 물건을 배송하기 위해서는 구매한 회원의 주소 및 연락처를 알아야 한다.

이 회원의 주소, 연락처를 알기 위해 정보가 있는 회원 테이블과 결합하는 것이 내부 조인이다.

SELECT * FROM buy INNER JOIN member ON buy.mem_id = member.mem_id;

이렇게 쿼리를 작성하면, 구매 테이블과 회원 테이블이 mem_id를 기준으로 결합이 된다.

즉, 구매 테이블의 모든 행이 회원 테이블과 결합이 된다.

구매 테이블에서 GRL이라는 아이디를 가진 사람이 구매한 물건을 발송하기 위해 아래와 같이 조인해서 이름/주소/연락처 등을 검색할 수 있다.

SELECT * 
FROM buy
INNER JOIN member
ON buy.mem_id = member.mem_id
WHERE buy.mem_id = 'GRL';

내부 조인의 간결한 표현

이것은 열이 너무 많아 복잡해 보이므로 필요한 아이디/이름/구매 물품/주소/연락처만 추출하려면 아래와 같이 퀴리를 작성한다.

SELECT buy.mem_id, mem_name, prod_name, addr, CONCAT(phone1, phone2) AS '연락처' 
FROM buy
INNER JOIN member
ON buy.mem_id = member.mem_id;

별칭

FROM 절에 나오는 테이블의 이름 뒤에 별칭을 줄 수 있다.

SELECT B.mem_id, M.mem_name, B.prod_name, M.addr, 
CONCAT(M.phone1, M.phone2) AS '연락처' 
FROM buy B
INNER JOIN member M
ON B.mem_id = M.mem_id;

결과는 똑같이 나오는 것을 확인할 수 있다.

내부 조인의 활용

구매한 회원의 아이디/이름/구매한 제품/주소를 정렬하여 출력하면 아래와 같다.

SELECT M.mem_id, M.mem_name, B.prod_name, M.addr
FROM buy B
 INNER JOIN member M
 ON B.mem_id = M.mem_id
ORDER BY M.mem_id;

구매한 회원의 구매 기록과 더불어 구매하지 않은 회원의 이름/주소가 같이 검색되도록 하려면 아래와 같이 검색되도록 하려면 외부 조인을 사용해야 한다.

즉, 지금까지 사용한 내부 조인은 두 테이블에 모두 있는 내용만 조인되는 방식이다.

만약, 양쪽 중에 한 곳이라도 내용이 있을 때 조인하려면 외부 조인을 사용해야 한다.

구매 이력이 있는 회원들에게 감사문 보내기
구매를 한 이력이 있는 회원들에게 감사문을 보내려면, 구매를 몇번 했는지와 상관없이 한 번만 구매를 했으면 감사문을 보낼 대상이 된다.
즉, 중복을 제거해야 한다.

SELECT DISTINCT M.mem_id, M.mem_name, M.addr
FROM buy B
INNER JOIN member M
ON B.mem_id = M.mem_id
ORDER BY M.mem_id;​

외부 조인(Outer Join)

내부 조인은 두 테이블에 모두 데이터가 있어야만 결과가 나온다.

이와 달리 외부 조인은 한쪽에만 데이터가 있어도 결과가 나온다.

• LEFT OUTER JOIN: 왼쪽 테이블의 모든 값이 출력되는 조인
• RIGHT OUTER JOIN: 오른쪽 테이블의 모든 값이 출력되는 조인
• FULL OUTER JOIN: 왼쪽 또는 오른쪽 테이블의 모든 값이 출력되는 조인

외부 조인의 기본

외부 조인은 두 테이블을 조인할 때 필요한 내용이 한쪽 테이블에만 있어도 결과를 추출할 수 있다.

외부 조인의 형식은 아래와 같다.

내부 조인에서 해결하지 못한 전체 회원의 구매 기록(구매 기록이 없는 회원의 정보도 함께)출력을 외부조인으로 쿼리를 작성하면 아래와 같다.

SELECT M.mem_id, M.mem_name, B.prod_name, M.addr
FROM member M
LEFT OUTER JOIN buy B
ON M.mem_id = B.mem_id
ORDER BY M.mem_id;

member 테이블이 왼쪽 테이블, buy 테이블이 오른쪽 테이블인 상태이다.

RIGHT OUTTER JOIN으로 동일한 결과를 출력하려면 아래와 같이 단순히 왼쪽과 오른쪽 테이블의 위치만 바꾸면 된다.

SELECT M.mem_id, M.mem_name, B.prod_name, M.addr
FROM buy B
RIGHT OUTER JOIN member M
ON M.mem_id = B.mem_id
ORDER BY M.mem_id;

buy 테이블이 왼쪽 테이블, member 테이블이 오른쪽 테이블인 상태이다.

FULL OUTTER JOIN은 왼쪽 외부 조인과 오른쪽 외부 조인이 합쳐진 것이다.

왼쪽이든 오른쪽이든 한쪽에 들어 있는 내용이면 출력한다.

자주 사용하지 않는다.

외부 조인의 활용

회원으로 가입만 하고, 한 번도 구매한 적이 없는 회원의 목록을 추출하는 쿼리는 아래와 같다.

SELECT DISTINCT M.mem_id, B.prod_name, M.mem_name, M.addr
FROM member M
LEFT OUTER JOIN buy B
ON M.mem_id = B.mem_id
WHERE B.prod_name IS NULL
ORDER BY M.mem_id;

IS NULL 구문은 널(NULL) 값인지 비교한다.

외부 조인 정리

기타 조인

내부 조인이나 외부 조인처럼 자주 사용되지는 않지만 가끔 유용하게 사용되는 조인으로 상호 조인과 자체 조인도 있다.

상호 조인(Cross Join)

한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능입니다. 상호 조인 결과의 전체 행 개수는 두 테이블의 각 행의 개수를 곱한 수만큼 됩니다. 카티션 곱(CARTESIAN PRODUCT)라고도 한다.

회원 테이블의 첫 행은 구매 테이블의 모든 행과 조인된다.

나머지 행도 마찬가지이다.

즉, 회원 테이블의 첫 행이 구매 테이블의 12개 행과 결합된다.

또 회원 테이블의 두 번째 행이 구매 테이블의 12개 행과 결합된다.

이런 식으로 회원 테이블의 모든 행이 구매 테이블의 모든 행과 결합된다.

최종적으로 회원 테이블의 10개 행과 구매 테이블의 12개 행을 곱해서 총 120개의 결과가 생성된다.

상호 조인은 아래와 같은 특징을 갖는다.

• ON 구문을 사용할 수 없다.
• 결과의 내용은 의미가 없다.(랜덤으로 조인하기 때문)
• 상호 조인의 주 용도는 테스트하기 위해 대용량의 데이터를 생성할 때 쓰인다.

자체 조인(Self Join)

내부 조인, 외부 조인, 상호 조인은 모두 2개의 테이블을 조인했다.

자체 조인은 자신이 자신과 조인한다는 의미이다.

그래서 자체 조인은 1개의 테이블을 사용한다.

예를 들어, 관리 이사는 직원이므로 직원 열에 속한다.

그러면서 동시에 경리부장과 인사부장의 상관이어서 직속 상관 열에도 속한다.

만약, 직원 중 경리부장의 직속상관인 관리이사의 사내 연락처를 알고 싶다면 EMP열과 MANAGER 열을 조인해야 한다.

CREATE TABLE emp_table (emp CHAR(4), manager CHAR(4), phone VARCHAR(8));
INSERT INTO emp_table VALUES('대표', NULL, '0000');
INSERT INTO emp_table VALUES('영업이사', '대표', '1111');
INSERT INTO emp_table VALUES('관리이사', '대표', '2222');
INSERT INTO emp_table VALUES('정보이사', '대표', '3333');
INSERT INTO emp_table VALUES('영업과장', '영업이사', '1111-1');
INSERT INTO emp_table VALUES('경리부장', '관리이사', '2222-1');
INSERT INTO emp_table VALUES('인사부장', '관리이사', '2222-2');
INSERT INTO emp_table VALUES('개발팀장', '정보이사', '3333-1');
INSERT INTO emp_table VALUES('개발주임', '정보이사', '3333-1-1');

이렇게 만든 테이블은 아래와 같은 구조를 갖는다.

이제 관리 이사의 사내 전화번호를 알고 싶다면 아래와 같은 쿼리를 작성하면 된다.

SELECT A.emp "직원" , B.emp "직속상관", B.phone "직속상관연락처"
FROM emp_table A
INNER JOIN emp_table B
ON A.manager = B.emp
WHERE A.emp = '경리부장';

즉, 아래와 같이 2개의 테이블이 조인 되는 것처럼 구성된 것이다.

이렇든 하나의 테이블에 같은 데이터가 있지만 2개 이상의 열로 존재할 때 자체 조인을 할 수 있다.

이 글은 혼자 공부하는 SQL(저자 : 우재남)의 책과 유튜브 영상을 참고하여 개인적으로 정리하는 글임을 알립니다.

테이블을 만들 때는 데이터 형식을 설정해야 한다.

데이터 형식에는 크게 숫자형, 문자형, 날짜형이 있다.

또 세부적으로는 여러 개로 나뉘기도 한다.

이렇게 다양한 데이터 형식이 존재하는 이유는 실제로 데이터 형태가 다양하기 때문이다.

각 데이터에 맞는 데이터 형식을 지정함으로써 효율적으로 저장할 수 있다.

데이터 형식

정수형

정수형은 소수점이 없는 숫자, 즉 인원 수, 가격, 수량 등에 많이 사용된다.

정수형의 크기와 범위는 아래와 같다.

UNSIGNED 예약어를 사용하면, 음수를 표현했던 범위를 온전히 양수로 표현할 수 있다.

UNSIGNED 예약어는 모든 정수에 사용 가능하다.

문자형

문자형은 글자를 저장하기 위해 사용하며, 입력할 최대 글자수를 지정해야 한다.

문자형에는 BINARY, VARBINARY도 있지만 잘 사용하지 않는다.

CHAR는 고정길이 문자형이라고 부른다.

즉, 자릿수가 고정되어 있다.

예를 들어 CHAR(10)에 '가나다' 3글자만 저장해도 10자리를 모두 확보한 후에 앞에 3자리를 사용하고 뒤의 7자리는 낭비하게 된다.

이와 달리 VARCHAR는 가변길이 문자형으로, VARCHAR(10)에 '가나다' 3글자를 저장할 경우 3자리만 사용한다.

VARCHAR가 CHAR보다 공간을 효율적으로 운영할 수 있지만, MySQL 내부적으로 속도면에서는 CHAR로 설정하는 것이 조금 더 좋다.

대량의 데이터 형식

TINYTEXT, MEDIUMTEXT, TINYBLOB, MEDIUMBLOB 등도 있지만 잘 사용하지 않는다.

실수형

실수형은 소수점이 있는 숫자를 저장할 때 사용한다.

날짜형

날짜형은 날짜 및 시간을 저장할 때 사용한다.

날짜 또는 시간을 입력할 때는 문자와 마찬가지로 작은 따옴표로 묶어줘야 한다.

변수

변수의 사용

SQL도 다른 일반적인 프로그래밍 언어처럼 변수를 선언하고 사용할 수 있다.

SET @myVar1 = 5 ;
SET @myVar2 = 4.25 ;
SELECT @myVar1 + @myVar2 ;

SET @txt = '가수 이름==> ' ;
SET @height = 166;
SELECT @txt , mem_name FROM member WHERE height > @height ;

PREPARE와 EXECUTE

아래의 코드는 오류가 발생한다.

SET @count = 3;
SELECT mem_name, height FROM member ORDER BY height LIMIT @count;

이유는 LIMIT에는 변수를 사용할 수 없기 때문이다.

이를 해결하는 것이 PREPARE와 EXECUTE이다.

PREPARE는 실행하지 않고 SQL 문만 준비해 놓고 EXECUTE에서 실행하는 방식이다.

SET @count = 3;
PREPARE mySQL FROM 'SELECT mem_name, height FROM member ORDER BY height LIMIT ?';
EXECUTE mySQL USING @count;

1. PREPARE에서 SELECT LIMIT ? 문을 실행하지 않고 MySQL이라는 이름으로 준비만 해놓는다.
2. EXECUTE로 mySQL에 저장된 SELECT문을 실행할 때, USING으로 물음표에 @count 변수에 값을 대입하는 것이다.

데이터 형 변환

문자형을 정수형으로 바꾸거나, 반대로 정수형을 문자형으로 바꾸는 것을 데이터의 형 변환이라고 부른다.

형 변환에는 직접 함수를 사용해서 변환하는 명시적 형 변환과 별도의 지시 없이 자연스럽게 변환되는 묵시적 형 변환이 있다.

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

데이터 형식을 변환하는 함수는 CAST(), CONVERT(), CAST(), CONVERT()는 형식만 다를 뿐 동일한 기능을 한다.

SELECT AVG(price) '평균 가격' FROM buy;

가격은 실수보다 정수로 표현하는 것이 좋다.

아래와 같이 CAST()나 CONVERT() 함수를 사용해서 정수로 표현할 수 있다.

CAST()나 CONVERT() 함수 안에 올 수 있는 데이터 형식은 CHAR, SIGNED, UNSIGNED, DATE, TIME, DATETIME 등이다.

SIGNED는 부호가 있는 정수, UNSIGNED는 부호가 없는 정수를 의미한다.

SELECT CAST(AVG(price) AS SIGNED)  '평균 가격'  FROM buy ;
-- 또는
SELECT CONVERT(AVG(price) , SIGNED)  '평균 가격'  FROM buy ;

날짜는 다양한 구분자를 날짜형으로 변경할 수도 있다.

SELECT CAST('2022$12$12' AS DATE);
SELECT CAST('2022/12/12' AS DATE);
SELECT CAST('2022%12%12' AS DATE);
SELECT CAST('2022@12@12' AS DATE);

SQL의 결과를 원하는 형태로 표현할 때도 사용할 수 있다.

가격(price)과 수량(amount)을 곱한 실제 구매액을 표시하는 SQL문은 아래와 같이 작성할 수 있다.

SELECT num, CONCAT(CAST(price AS CHAR), 'X', CAST(amount AS CHAR) ,'=' ) '가격X수량',
price*amount '구매액' 
FROM buy ;

가격과 수량은 정수지만, CAST() 함수를 통해 문자로 바꾸었다.

CONCAT() 함수는 문자를 이어주는 역할을 한다.

묵시적인 변환

SELECT '100' + '200' ; -- 문자와 문자를 더함 (정수로 변환되서 연산됨)

SELECT CONCAT('100', '200'); -- 문자와 문자를 연결 (문자로 처리)

SELECT CONCAT(100, '200'); -- 정수와 문자를 연결 (정수가 문자로 변환되서 처리)

이 글은 혼자 공부하는 SQL(저자 : 우재남)의 책과 유튜브 영상을 참고하여 개인적으로 정리하는 글임을 알립니다.

데이터베이스와 테이블을 만든 후에는 데이터를 변경하는, 즉 입력/수정/삭제하는 기능이 필요하다.

예를 들어, 새로 가입한 회원을 테이블에 입력할 때는 INSERT 문을, 회원의 주소나 연락처가 변경되어 정보를 수정할 때는 UPDATE문을 사용한다. 회원이 탈퇴해서 회원을 삭제할 때는 DELETE문을 사용한다.

데이터 입력 : INSERT

INSERT문의 기본 문법

테이블에 행 데이터를 입력하는 기본적인 SQL문은 INSERT이다.

테이블 이름 다음에 나오는 열은 생략이 가능하다.

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

USE market_db; //데이터베이스 선택
CREATE TABLE hongong1 (toy_id  INT, toy_name CHAR(4), age INT); //테이블 생성
INSERT INTO hongong1 VALUES (1, '우디', 25); //테이블에 데이터 추가

만약 아이디와 이름만 입력하고 나이는 입력하고 싶지 않다면 아래와 같이 테이블 이름 뒤에 입력할 열의 이름을 써줘야 한다. 이 경우 생략한 나이(age) 열에는 아무것도 없다는 의미의 NULL 값이 들어간다.

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

열의 순서를 바꿔서 입력하고 싶을 때는 열 이름과 값을 원하는 순서에 맞춰 써주면 된다.

INSERT INTO hongong1(toy_name,age, toy_id) VALUES ('제시', 20, 3);

자동으로 증가하는 AUTO_INCREMENT

AUTO_INCREMENT는 열을 정의할 때 1부터 증가하는 값을 자동으로 입력해준다.

INSERT에서는 해당 열이 없다고 생각하고 입력하면 된다.

단, 주의해야 할 점은 AUTO_INCREMENT로 지정하는 열은 꼭 PRIMARY KEY(기본키)로 지정해줘야 한다.

우선 간단한 테이블을 만든다(아이디(toy_id) 열을 자동 증가로 설정)

CREATE TABLE hongong2 ( 
toy_id  INT AUTO_INCREMENT PRIMARY KEY, 
toy_name CHAR(4), 
age INT);

이후에 3개의 데이터를 추가해 주고, 행과 열을 확인해 보면, 자동으로 아이디가 증가했음을 알 수 있다.

INSERT INTO hongong2 VALUES (NULL, '보핍', 25);
INSERT INTO hongong2 VALUES (NULL, '슬링키', 22);
INSERT INTO hongong2 VALUES (NULL, '렉스', 21);
SELECT * FROM hongong2;

계속 데이터를 추가하다 보면 현재 어느 숫자까지 증가되었는지 확인하려면 아래의 SQL문을 이용하면 된다.

SELECT LAST_INSERT_ID();

만약 AUTO_INCREMENT로 입력되는 다음의 값을 100부터 시작하도록 변경하고 싶다면 아래와 같은 SQL문을 실행하면 된다.(ALTER TABLE 뒤에는 테이블 이름을 입력하고, 자동 증가를 100부터 시작하기 위해 AUTO_INCREMENT를 100으로 지정)

ALTER TABLE hongong2 AUTO_INCREMENT=100;
INSERT INTO hongong2 VALUES (NULL, '재남', 35);
SELECT * FROM hongong2;

ALTER TABLE
테이블을 변경하라는 의미이다. 테이블의 옆 이름 변경, 새로운 열 정의, 열 삭제 등의 작업을 한다.

이번에는 처음으로 입력되는 값을 1000으로 지정하고 다음 값은 1003, 1006, 1009,...으로 3씩 증가하도록 설정하는 방법은 시스템 변수인 @@auto_increment_increment를 변경시켜야 한다.

테이블을 새로 만들고 자동 증가의 시작값은 1000으로 지정하고, 증가 값은 3으로 하기 위해 @@auto_increment_increment를 3으로 지정했다.

CREATE TABLE hongong3 ( 
   toy_id  INT AUTO_INCREMENT PRIMARY KEY, 
   toy_name CHAR(4), 
   age INT);
ALTER TABLE hongong3 AUTO_INCREMENT=1000;
SET @@auto_increment_increment=3;

이후 데이터를 추가한 후 확인해 보면 아래와 같은 결과가 나온다.

INSERT INTO hongong3 VALUES (NULL, '토마스', 20);
INSERT INTO hongong3 VALUES (NULL, '제임스', 23);
INSERT INTO hongong3 VALUES (NULL, '고든', 25);
#위 세 줄의 코드는 아래의 코드 한 줄과 같은 의미이다.
#INSERT INTO hongong3 VALUES (NULL, '토마스', 20), (NULL, '제임스', 23), (NULL, '고든', 25);
SELECT * FROM hongong3;

다른 테이블의 데이터를 한 번에 입력하는 INSERT INTO ~ SELECT

많은 양의 데이터를 지금까지 했던 방식으로 직접 타이핑해서 입력하려면 오랜 시간이 걸릴 것이다.

다른 테이블에 이미 데이터가 입력되어 있다면 INSERT INTO ~ SELECT 구문을 사용해 해당 테이블의 데이터를 가져와서 한 번에 입력할 수 있다.

주의할 점은 SELECT 문의 열 개수는 INSERT 할 테이블의 열 개수와 같아야 한다.

즉, SELECT의 열이 3개라면 INSERT 될 테이블의 열도 3개여야 한다.

먼저 MySQL을 설치할 때 함께 생성된 world 데이터베이스의 city 테이블의 개수를 조회해 보면, 4079개가 나온다.

SELECT COUNT(*) FROM world.city;

world.city 테이블의 구조를 확인하려면 DESC 명령으로 확인할 수 있다.

DESC world.city;

데이터도 5건 정도 살펴보자

SELECT * FROM world.city LIMIT 5;

이 중에서 도시 이름(Name)과 인구(Population)를 가져와 보자.

먼저, 가져오기 전에 새로운 테이블을 먼저 만들어야 한다.(DESC로 확인한 열 이름과 데이터 형식을 사용)

CREATE TABLE city_popul ( city_name CHAR(35), population INT);

INSERT INTO city_popul
    SELECT Name, Population FROM world.city;
    
SELECT * FROM city_popul;

데이터 수정 : UPDATE

UPDATE문의 기본 문법

UPDATE는 기존에 입력되어 있는 값을 수정하는 명령이다.

MySQL Workbench 설정 변경
워크벤치에서는 기본적으로 UPDATE 및 DELETE를 허용하지 않기 때문에 설정을 변경해야 한다.
1. 먼저 기존에 열린 쿼리 창을 모두 종료한다.
2. Edit -> Preferences메뉴를 실행한다.

3. 위 그림처럼 밑 줄 친 곳의 체크 박스를 해제하고 OK를 누른다.
4. 워크벤치를 재시작한다.

앞에 생성한 city_popul 테이블의 도시 이름 중에서 Seoul을 서울로 변경해 보자.

USE market_db;
UPDATE city_popul
    SET city_name = '서울'
    WHERE city_name = 'Seoul';
SELECT  * FROM city_popul WHERE  city_name = '서울';

아래는 도시 이름인 NewYork을 뉴욕으로 바꾸면서 인구는 0으로 설정하는 명령이다.

UPDATE city_popul
    SET city_name = '뉴욕', population = 0
    WHERE city_name = 'New York';
SELECT  * FROM city_popul WHERE  city_name = '뉴욕';

WHERE가 없는 UPDATE문
UPDATE 문에서 WHERE 절은 문법상 생략이 가능하지만, WHERE 절을 생략하면 테이블의 모든 행의 값이 변경된다.
이러한 경우는 드물뿐더러, 끔찍한 결과를 초래할 수 있으니 주의하자

데이터 삭제 : DELETE

DELETE는 행 단위로 삭제하며 형식은 아래와 같다.

city_popul 테이블에서 New로 시작하는 도시를 삭제하기 위해 아래와 같이 실행한다.

DELETE FROM city_popul 
    WHERE city_name LIKE 'New%';

만약 New로 시작하는 도시 모두를 삭제하는 것이 아니라 상위 몇 건만 삭제하려면 LIMIT 구문과 함께 사용하면 된다.

DELETE FROM city_popul 
    WHERE city_name LIKE 'New%'
    LIMIT 5; #상위 5건만 삭제

DELETE, DROP, TRUNCATE의 차이
DELETE문은 삭제가 오래 걸린다.
TRUNCATE문은 DELETE와 동일한 효과를 내지만 속도가 매우 빠르다. 하지만 WHERE 문을 사용할 수 없다는 것을 주의해야 한다.
DROP문은 테이블 자체를 삭제한다.
반면에 TRUNCATE문과 DELETE는 빈 테이블을 남긴다.
따라서 테이블 자체를 삭제할 때는 DROP를 쓰고,  테이블의 구조는 남겨놓고 싶다면 TRUNCATE로 삭제하는 것이 효율적이다.

DELETE FROM big_table1;
DROP TABLE big_table2;
TRUNCATE TABLE big_table3;​

이 글은 혼자 공부하는 SQL(저자 : 우재남)의 책과 유튜브 영상을 참고하여 개인적으로 정리하는 글임을 알립니다.

1. ORDER BY : 결과의 정렬을 위함
2. LIMIT : 결과의 개수를 제한
3. DISTINCT : 중복된 데이터를 제거
4. GROUP BY : 지정한 열의 데이터들을 같은 데이터끼리 묶어서 결과를 추출, 주로 집계 함수와 함께 사용됨, HAVING 절을 통해 조건식을 추가할 수 있음
5. HAVING : WHERE 절과 비슷해 보이지만, GROUP BY 절과 함께 사용됨

기본적인 SELECT 절의 형식은 아래와 같다.

이 중에서 몇 가지 생략은 가능하지만 순서는 무조건 지켜야 한다.

SELECT ~ FROM ~ ORDER BY ~ LIMIT는 가능하지만

SELECT ~ FROM ~ LIMIT ~ ORDER BY는 불가능하다.

member 테이블

buy 테이블

ORDER BY 절

결과가 출력되는 순서를 조절한다.

member 테이블에서 데뷔일자가 빠른 순서대로 출력하려면 아래처럼 작성한다.

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

내림차순으로 정렬하고 싶다면, 뒤에 DESC를 붙여준다. (오름차순은 ASC)

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

ORDER BY 절은 WHERE절과 같이 사용할 수 있다.

아래의 코드는 키가 164 이상인 그룹을 내림차순으로 정렬하는 코드이다.

SELECT mem_id, mem_name, debut_date, height
   FROM member 
   WHERE height  >= 164
   ORDER BY height DESC;

정렬 기준은 1개 열이 아니라 여러 개 열로 지정할 수 있다.

잇지와 트와이스의 키가 167로 동일한데 트와이스가 데뷔일자가 오래되었으니, 트와이스를 잇지보다 위로 정렬하고 싶다면 아래의 코드로 작성하면 된다.

SELECT mem_id, mem_name, debut_date, height
   FROM member 
   WHERE height  >= 164
   ORDER BY height DESC, debut_date ASC;

정렬 기준이 여러개일 때
첫 번째 정렬 기준으로 정렬 된 후, 두 번째 정렬 기준으로 정렬된다.
위 예제에서 167로 동일한 결과가 있을 때 두 번째 정렬 기준으로 정렬되는 것이다.

LIMIT

LIMIT는 출력하는 개수를 제한한다.

아래의 코드는 member 테이블을 조회하는데 전체 중 앞에서 3건만 조회하는 코드이다.

SELECT * FROM member LIMIT 3;

LIMIT 형식은 LIMIT 시작, 개수이다. 지금과 같이 LIMIT 3만 쓰면 LIMIT 0, 3과 동일하다

즉, 0번째부터 3건이라는 의미이다.

필요하다면 중간부터 출력도 가능하다.

다음과 같이 평균 키가 큰 순으로 정렬하되, 3번째부터 2건만 조회할 수 있다.

SELECT mem_name, height
   FROM member
   ORDER BY height DESC
   LIMIT 3,2;

DISTINCT

조회된 결과에서 중복된 데이터를 1개만 남기는 구문이다.

이렇게 여러 개 중복된 데이터를 한 개로 남기는 코드는 아래와 같다.

SELECT DISTINCT addr FROM member;

GROUP BY 절

GROUP BY 절은 말 그대로 그룹으로 묶어주는 역할을 한다.

위와 같이 그룹별로 합계나 평균 최소값등을 구하고 싶을 때는 집계 함수를 사용한다.

집계 함수

각 mem_id별로 구매한 개수를 합쳐서 출력하기 위해서는 SUM()과 GROUP BY 절을 사용하면 된다.

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

별칭을 사용하여 결과를 보기 좋게 할 수 있다.

SELECT mem_id "회원 아이디", SUM(amount) "총 구매 개수"
   FROM buy GROUP BY mem_id;

별칭
별칭에 작은따옴표를 사용해도 되지만, 작은따옴표는 INSERT 등에서 문자를 입력할 때 사용하므로 별칭에는 큰 따옴표를 사용하는 것이 좋다.

회원이 구매한 금액의 총합을 출력하려면 아래와 같이 코드를 입력하면 된다.

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;

연락처가 있는 회원만 카운트하려면 전화번호의 열 이름을 지정하면 된다. 그러면 NULL 값인 항목은 제외하고 카운트하여 결국 연락처가 있는 회원의 인원만 나온다.

SELECT COUNT(phone2) "연락처가 있는 회원" FROM member;

HAVING 절

SUM()으로 회원별 총구매액을 구하면 아래와 같다.

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

총구매액이 1000 이상인 회원에게만 사은품을 증정한다고 하면 아래와 같이 작성하면 된다.

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

WHERE과 HAVING
HAVING은 WHERE와 비슷한 개념으로 조건을 제한하는 것이지만, 집계 함수에 대해서 조건을 제한하는 것이라고 생각하면 된다.
HAVING절은 꼭 GROUP BY 절 다음에 나와야 한다.

만약 총구매액이 큰 사용자부터 나타내려면 ORDER BY를 사용하면 된다.

SELECT mem_id "회원 아이디", SUM(price*amount) "총 구매 금액"
   FROM buy 
   GROUP BY mem_id   
   HAVING SUM(price*amount) > 1000
   ORDER BY SUM(price*amount) DESC;

이 글은 혼자 공부하는 SQL(저자 : 우재남)의 책과 유튜브 영상을 참고하여 개인적으로 정리하는 글임을 알립니다.

member 테이블

buy 테이블

USE문

USE market_db;

market_db 데이터 베이스를 사용(선택)하겠다는 뜻

기본적인 SELECT ~ FROM ~ WHERE문

SELECT * FROM member WHERE mem_name = '블랙핑크';

mem_name이 블랙핑크인 결과만 추출

SELECT * FROM member WHERE mem_number = 4;

mem_number가 4인 결과만 추출

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

관계 연산자

SELECT mem_id, mem_name FROM member WHERE height <= 162;

키가 162 이하인 mem_id와 mem_name을 추출

논리 연산자

SELECT mem_name, height, mem_number FROM member WHERE height >= 165 AND mem_number > 6;

SELECT mem_name, height, mem_number FROM member WHERE height >= 165 OR mem_number > 6;

BETWEEN ~ AND

SELECT mem_name, height FROM member WHERE height >= 163 AND height <= 165;

위 코드와 아래의 코드는 같은 뜻이다.

SELECT mem_name, height FROM member WHERE height BETWEEN 163 AND 165;

163과 165사이의 height과 mem_name 데이터를 추출하라는 뜻이다.

IN()

SELECT mem_name, addr FROM member WHERE addr = '경기' OR addr = '전남' OR addr = '경남';

위 코드는 아래 코드와 같다.

SELECT mem_name, addr FROM member WHERE addr IN('경기', '전남', '경남');

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

LIKE(문자열의 일부 글자 검색)

문자열의 일부 글자를 검색하려면 LIKE를 사용한다.

SELECT * FROM member WHERE mem_name LIKE '우%';

mem_name이 '우'로 시작되긴 하는데 정확히 기억 안 난다면 위와 같이 작성하면 된다.

언더바(_)

SELECT * FROM member WHERE mem_name LIKE '__핑크';

위처럼 언더바를 두 개 사용하면 앞 두 글자는 상관하지 않고 검색하게 된다.

서브 쿼리

SELECT 안에 또 다른 SELECT가 들어갈 수 있다. 이것을 서브 쿼리 또는 하위 쿼리라고 부른다.

mem_name이 에이핑크인 회원의 평균 height보다 큰 데이터를 검색하고 싶다고 가정하면

SELECT * FROM member WHERE height > (SELECT height FROM member WHERE mem_name = '에이핑크');

위와 같이 에이핑크의 평균 height인 164보다 높은 데이터를 추출할 수 있다.

이 글은 혼자 공부하는 SQL(저자 : 우재남)의 책과 유튜브 영상을 참고하여 개인적으로 정리하는 글임을 알립니다.

데이터베이스 삭제

drop database if exists mk_db; -- 만약 mk_db가 있으면 삭제해라

데이터베이스 mk_db가 있으면 삭제하라는 뜻이다.

테이블 생성

create table member -- member 테이블 생성
(
	mem_id char(8) not null primary key, -- char형 8글자, null 허용 않음, 기본키
	mem_name char(10) not null, -- char형 10글자, null 허용 않음
	mem_number int not null, -- int형, null 허용 않음
	addr char(2) not null, -- char형 2글자, null 허용 않음
	phone1 char(3), -- char형 3글자
	phone2 char(8), -- char형 8글자
	height smallint, -- 2바이트짜리 int형
	debut_date date -- 날짜
);
create table buy -- buy 테이블 생성
(
	num int auto_increment not null primary key, -- 자동 숫자지정
    mem_id char(8) not null,
    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) -- 외래키(두 테이블을 연결하는데 사용)
);

아래는 만들어진 테이블

행 삽입

insert into member values('TWC', '트와이스', 9, '서울', '02', '11111111', 167, '2015,10,19'); -- 데이터 삽입
insert into buy values(null, 'TWC', '지갑', '트와이스', 30, 2); -- 데이터 삽입

각각 member 테이블과 buy 테이블에 행을 삽입하는 코드이다.

아래는 결과

이 글은 혼자 공부하는 SQL(저자 : 우재남)의 책과 유튜브 영상을 참고하여 개인적으로 정리하는 글임을 알립니다.

위와 같은 테이블을 생성했다고 하자. 테이블 이름은 member이다.

테이블의 모든 행 조회하기

SELECT의 기본 형식은 SELECT 열 이름 FROM 테이블 이름 [WHERE 조건]이다.

*는 모든 열을 의미한다. *을 열 이름에 넣으면 모든 열을 보여달란 뜻이 된다.

이렇게 코드를 입력하면

member 테이블의 모든 행이 출력된다.

테이블 중 특정 열만 출력하기

member 테이블에서 이름과 주소만 출력하려면 아래와 같이 입력하면 된다.

이렇게 입력하면 아래와 같은 결과가 나온다.

특정 데이터에 대한 정보만 추출하기

아이유에 대한 정보만 추출하려면 WHERE 다음에 특정 조건을 입력하면 된다.

where에 member_name이 아이유인 사람만 추출해 달라는 코드이다.

코드의 결과는 아래와 같다.

이 글은 혼자 공부하는 SQL(저자 : 우재남)의 책과 유튜브 영상을 참고하여 개인적으로 정리하는 글임을 알립니다.

데이터베이스(Database)를 한 마디로 정의한다면 데이터의 집합이라고 한다.

DBMS의 정의

데이터베이스를 데이터의 집합이라고 정의한다면, 데이터베이스를 관리하고 운영하는 소프트웨어를 DBMS(Database Management System)이라고 한다.

데이터베이스는 여러 명의 사용자나 응용 프로그램과 공유하고 동시에 접근이 가능해야 한다.

엑셀과 같은 프로그램은 데이터의 집합을 관리하고 운영한다는 면에서 DBMS로 볼 수 있지만, 대용량 데이터를 관리하거나 여러 사용자와 공유하는 개념과는 거리가 있어 DBMS라고 부르지 않는다.

SQL

DBMS는 데이터의 집합인 데이터베이스를 잘 관리하고 운영하기 위한 시스템 또는 소프트웨어를 말하는데, DBMS에 데이터를 구축, 관리하고 활용하기 위해서 사용되는 언어가 SQL(Structured Query Language)이다.

SQL을 사용하면 DBMS를 통해 중요한 정보들을 입력, 관리하고 추출할 수 있다.

SQL이 데이터베이스를 조작하는 언어이긴 하지만 일반적인 프로그래밍 언어와는 조금 다른 특성을 갖는다.

SQL은 특정 회사에서 만드는 것이 아니라 국제표준화기구에서 SQL에 대한 표준을 정해서 발표하고 있다.

이를 표준 SQL이라고 한다.

하지만 SQL을 사용하는 DBMS를 만드는 회사가 여러 곳이기 때문에 표준 SQL이 각 회사 제품의 특성을 모두 포용하지 못한다는 것이다.

그래서 DBMS를 만드는 회사에서는 되도록 표준 SQL을 준수하되, 각 제품의 특성을 반영한 SQL을 사용한다.

3가지 DBMS 제품 모두 표준 SQL을 포함하고 있지만, 추가로 자신만의 기능을 가지고 있다.

오라클은 PL/SQL, SQL Server는 T-SQL, MySQL은 SQL로 부른다.

따라서 표준 SQL을 배우면 나중에 다른 DBMS를 접해도 쉽게 배울 수 있다.

DBMS의 분류

DBMS의 유형은 계층형, 망형, 관계형, 객체지향형, 객체관계형 등으로 분류된다.

현재 사용되는 DBMS 중에는 관계형 DBMS가 가장 많이 사용된다.

관계형 DBMS

관계형 DBMS(Relational DBMS)는 줄여서 RDBMS라고 부른다. 대부분 DBMS가 RDBMS 형태로 사용된다.

RDBMS의 데이터베이스는 테이블이라는 최소 단위로 구성되며, 이 테이블은 하나 이상의 행과 열로 이루어져 있다.

위 테이블은 3개의 행과 5개의 열로 구성되어 있다.(4행 5열이 아니라 3행 5열이다(4행 5열 중 1행은 데이터가 아니므로))

• 열(column) : 테이블의 세로를 뜻함. 위 테이블은 5열로 이뤄져 있다.
• 열 이름 : 각 열을 구분하기 위한 이름이다. 열 이름은 각 테이블 내에서는 서로 달라야 하며, ID, PW, Name.. 등이 열 이름이다.
• 행 : 실질적인 진짜 데이터를 말한다. 열 이름을 제외한 행을 말한다. 행 데이터라고 부르기도 한다. 위 테이블에서는 3행으로 이뤄져 있다.
• 데이터 형식 : 열에 저장될 데이터 형식을 말한다. 위 테이블에서 이름은 숫자가 아닌 문자 형식이어야 한다. 데이터 형식은 테이블을 생성할 때 열 이름과 함께 지정해 준다.
• 기본 키(Primary Key) : 기본 키 열은 각 행을 구분하는 유일한 열을 말한다. 위 테이블에서 다른 것은 중복되거나 생략 될 수 있지만 ID는 중복되거나 생략될 수 없기 때문에 ID가 기본 키이다. 테이블에는 열이 여러 개 있지만 기본 키는 1개만 지정해야 하며, 1개의 열에 지정한다.

RDBMS에서는 모든 데이터가 테이블에 저장된다. 이 구조가 가장 기본적이고 중요한 구성이기 때문에 테이블만 제대로 파악하면 RDBMS를 어느 정도 이해했다고 할 수 있다.