[SQL] SQL 기본 정리 - MySQL 한번에 기본 끝내기

목차

 

MySQL 설치

1-1 MySQL 설치하기

2-1 MySQL 기본 문법 특성

 

데이터 베이스 만들기

3-1 Database 생성

3-2 Database 삭제

3-3 USE DATABASE

 

테이블 만들기

4-1 TABLE 생성

5-1 ALTER

5-2 TABLE 삭제

5-3 TABLE 생성 - 열 생성

5-4 Primary Key/Id (PK)

5-5 Foreign key/Table

 

데이터 수정하기

6-1 INSERT
6-2 SELECT

6-2-A 불러올 데이터수 정하기
6-2-B 불러올 열 정하기
6-2-C 칼럼 이름 바꾸기 AS
6-2-D 순서 바꿔 나열 Order By (오름차순 내림차순)
6-3 DISTINCT

 

데이터 수정/선택하기 - 2 

7-1 데이터 업데이트/데이터 필터링

7-2 여러 필터링 추가하기 OR/AND/BETWEEN

7-3 데이터 지우기

7-4 JOIN

7-5 JOIN종류 INNER/LEFT/RIGHT

 

 

 

이번 포스트에서는 SQL의 기본 문법을 정리해보았습니다. 

 

위 목차를 CTRL + F를 사용해서 검색하여 사용하실 수 있습니다. 

 

 

MySQL 설치

 

1-1 MySQL 설치하기

 

SQL을 사용하기 전 아래 링크에서 MySQL을 다운로드해야 합니다.

MySQL :: MySQL Downloads

다운로드를 하기 전 먼저 회원가입을 해야 합니다. 

아래 *항목들을 작성해주세요. 개인정보는 자세하게 쓰지 않아도 완료할 수 있습니다. 

다운로드가 끝나면 My SQL workbench를 실행해 주세요 

workbench를 켜주시면 홈 화면에서 +를 

새로운 커넥션을 만들어줍니다. 플러스를 눌러주시면 setup화면이 나옵니다.

ok를 누르면 아래와 같은 화면이 나옵니다. 

2-1 MySQL 기본 문법 특성

 

대소문자 구분 X

sql은 대소문자를 구분하지 않습니다. 그렇지만 열(column)과 테이블 이름은 소문자 키워드는 소문 자하는 습관을 가지시면 가독성 높은 코드를 쓸 수 있습니다. 

 

//sql은 대소문자를 구분하지않습니다.
SELECT
seLect
SeLeCt

; 활용

한파일에 여러 query가 들어가는 경우 세미콜론이 나눠주기 때문에 필요하게 됩니다. 커맨드가 끝날 때마다 쓰는 습관을 들이는 것을 추천합니다.

 

SELECT * FROM table;

 

 

데이터베이스 만들기

 

3-1 Database 생성

 

우선 데이터베이스는 데이터들을 저장하고 접근할 때 유저가 원하는 정보를 제공해주는 곳입니다. 여기에 여러 데이터가 테이블 형식으로 들어있습니다. 

 

데이터 베이스를 생성하는 방법입니다.

 

1. 아래 함수를 입력해줍니다.( TEST는 데이터 베이스의 이름)

 

CREATE DATABASE test;

 

2. 함수를 실행합니다. 실행하는 법은 2가지가 있습니다. 

• 첫 번째는 아래 아이콘을 클릭하면 모든 쿼리 안에 작성된 모든 코드를 실행합니다.

• 두 번째는 커서로 하이라이트 한 부분만 실행합니다.

3. 위와 같이 만들어준 후 데이터베이스가 만들어졌는지 확인해야 합니다. Navigator tab에서 Schemas를 선택하면 아래와 같이 test database가 만들어진 것을 볼 수 있습니다. 

 

3-2 Database 삭제

 

이제 위에서 만들어준 데이터베이스를 삭제해보겠습니다. 그리고 코드를 실행하면 

 

DROP DATABASE test;

 

아래 사진과 같이 test database가 없어집니다. 

 

3-3 USE DATABASE

 

데이터베이스를 사용하기 위해서 sql에게 database를 사용한다고 명령해야 합니다. USE뒤에 쓸 데이터베이스의 이름을 붙여주면 됩니다. 아래 예제에서는 바로 만든 record_company database를 사용하겠습니다. 

 

CREATE DATABASE record_company;
USE record_company;

 

 

테이블 만들기

 

4-1 TABLE 생성

 

테이블을 만들어주겠습니다 테이블의 이름은 test입니다. 

CREATE DATABASE record_company;
USE record_company;
CREATE TABLE test;

 

테이블을 만들어주면 이제 테이블에 열(column)들을 만들어줘야 합니다. 

column

열들은 데이터의 속성을 나타냅니다. 예를 들어 보겠습니다. 위 예시 테이블을 보시면 열로 d, firstName, lastName, sex가 모두 열입니다. 그 아래로 데이터 들을 보실 수 있습니다. 우리가 새로운 나이 열(column)을 추가하고 싶다면 어떻게 해야 할까요? 새로운 열을 추가하는 방법을 배워도록하겠습니다. 

 

각 열(column)은 데이터 타입을 가지고 있습니다. INT, VARCHAR BOOLEAN, DOUBLE 등등 이 있습니다.

 

아래 코드는 열을 추가하는 코드입니다. 위에서 배운 문법대로 이름들은 소문자, 속성들은 대문자로 써주겠습니다. 

 

CREATE DATABASE record_company;
USE record_company;
CREATE TABLE test(
	test_column INT   
);

 

• CREATE TABLE로 test라는 이름의 테이블을 만들어주었습니다.
• 그리고 test_column이라는 이름의 속성을 만들고 데이터 타입을 INT로 만들어주었습니다.
• 괄호 () 안에 있는 코드들은 CREATE TABLE test와 하나의 코드임으로 세미콜론을 괄호 () 끝에 써줍니다. 

그리고 실행해주겠습니다. 

위와 같이 record_company라는 데이터가 생성이 되고 Tables 아래에 test라는 이름의 테이블이 생성된 걸 볼 수 있습니다. 

 

그리고 그 아래에 test_column이라는 이름의 새로운 열도 생성되었습니다. 그리고 이 test_column의 데이터 타입은 INT입니다. 

 

5-1 ALTER

 

위처럼 테이블을 만들어주고 코드를 실행 후 데이터를 추가했다고 가정해 봅시다. 그 후에 열(column)을 추가하려면, 코드를 다시 실행해야 되는데 그렇게 되면 칼럼 안에 있는 모든 데이터가 날아갑니다. 

 

기존 데이터를 유지하고 TABLE에 column을 추가하고 싶을 때 ALTER을 사용해주면 됩니다. 

 

CREATE DATABASE record_company;
USE record_company;
CREATE TABLE test(
	test_column INT
);
ALTER TABLE test
ADD	
another_column
VARCHAR(255);

 

• ALTER TABLE test는 수정할 테이블의 이름은 test입니다.
• 그리고 another_column이라는 열을 추가해주겠습니다.
• 이 열의 데이터 타입은 VARCHAR 즉 STRING과 같습니다.
• 최대 길이를 255로 설정하였습니다. 
• 세미콜론은 코멘드가 끝날 때 꼭 써줘야 합니다. 

이제 다시 이 코드를 실행해주겠습니다. 만약 record_company, test_column까지 만들어져 있는 상태라면, 3-1 Database 생성에서 배운 코드 실행 방법 중, 하이라이트 부분만 실행하도록 하는 실행하는 방법을 사용하겠습니다. 

ALTER TABLE TEST부터 VARCHAR(255);까지 커서로 드래그해주고 실행합니다.

그리고 execute 해주면 아래와 같이 column이 추가된 걸 보실 수 있습니다. 

 

5-2 TABLE 삭제

 

데이터베이스를 삭제하는 것과 같이 TABLE도 삭제 가능합니다. 

 

DROP TABLE test;

 

아래와 같이 Tables아래에 column들이 사라집니다.

 

5-3 TABLE 생성 - 열 생성

 

아래 코드를 작성해보겠습니다.

 

CREATE DATABASE record_company;
USE record_company;
CREATE TABLE bands(
	name VARCHAR(255) NOT NULL
);

 

• 위에서 test 테이블을 지워줬으므로 bands라는 이름의 새로운 테이블을 만들어보겠습니다.
• 그리고 bands라는 이름의 테이블 아래에 name의 이름과 varchar의 데이터 타입을 가진 열을 만들었습니다.
• NOT NULL을 설정함으로써, NULL값이 올 수 없게 되므로 만약 값이 없으면 에러를 반환합니다. 

(아직 코드를 실행하지 말아 주세요)그런데 문제가 있습니다. 만약 같은 이름의 밴드가 있다면 어떻게 될까요?

 

5-4 Primary Key/Id (PK)

 

모든 테이블에는 Primary Key/Id만을 위한 열을 꼭 만들어줘야 합니다. 왜냐하면 모든 데이터는 고유의 Primary Key나 ID가 있어야 하기 때문입니다. 그래야 같은 이름의 밴드가 있더라도 Primary key로 데이터를 구분할 수 있게 됩니다. 

 

이예 제에서는 우리가 밴드를 추가할 때마다, 자동으로 id가 생성되도록 만들어주겠습니다. 자동으로 생성하게 만드는 방법은 AUTO_INCREMENT를 사용해주는 겁니다.

 

아래 예제를 살펴보겠습니다.

 

CREATE DATABASE record_company;
USE record_company;
CREATE TABLE bands(
	id INT NOT NULL AUTO_INCREMENT,
	name VARCHAR(255) NOT NULL,
    PRIMARY KEY(id)
    
);

 

• 위 코드를 작성하시면 record_company database가 만들어져 있다면 아래 create TABLE bands만 아래 사진과 같이 실행해 주면 됩니다. 
• 우선 id라는 column을 만들어주었고 INT를 데이터 타입으로 가지고 있는 것을 볼 수 있습니다. 
• ID에 null값은 올 수 없고 1씩 증가하며 자동으로 id를 생성해주는 AUTO_INCREAMENT를 보실 수 있습니다. 
• 그다음 PRIMARY KEY(id) 코드의 뜻은 id라는 이름의 column을 PRIMARY KEY를 설정하는데 쓰겠다는 뜻입니다. 

그리고 실행해주시면 아래와 같이 ID와 NAME을 열로 가진 band이름의 테이블이 생긴 것을 확인하실 수 있습니다.  그리고  primary key를 구별하기 위한 index도 확인하실 수 있습니다. 

 

5-5 Foreign key/Table

 

foreign Key를 예제를 위해 새로운 테이블을 하나 더 만들어보겠습니다. 앨범에는 3가지 열이 있습니다. id, name, release year (출시 날짜)

 

CREATE DATABASE record_company;
USE record_company;
CREATE TABLE bands(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY(id)
    
);
CREATE TABLE albums (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    release_year INT,
    --FOREIGN KEY만들기
    band_id INT NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(band_id) REFERENCES bands(id)
);

 

• primary key가 각 테이블에 필요한 이유가 여기서 나옵니다. 
• albums의 데이터들은 primary key를 가지고 있고, bands 테이블 안에 있는 데이터들도 primary key를 가지고 있습니다.  두 테이블을 연결하기 위해 우리는 albums 열(column)에 bands 테이블의 primary key를 album 테이블에 넣음으로써(albums의 열 중 band_id ) 어떤 밴드가 이 앨범을 연주했는지 알게 하도록 하겠습니다.   
• 그리고 이제 코드로 연결해주기 위해 FOREIGN KEY와 REFERENCES를 활용해주면 됩니다 
• 이제 실행해보겠습니다. 빨간 박스 따라서 실행해봅시다. 

초록 박스를 확인해보면 record_company/Tables/albums/Foreignkey을 확인해보면 band키에 링크되어있는 파일이 생긴 걸 볼 수 있습니다.

 

 

데이터 수정하기

 

6-1 INSERT

 

열이 하나 있는 테이블에 데이터 추가하기

 

이제 테이블에 열들을 추가하는 것을 다룰 수 있게 되었습니다. 데이터는 어떻게 넣어야 할까요?

 

INSERT를 사용하시면 데이터를 추가하실 수 있습니다. 위에서 만들었던 bands 테이블에 데이터를 추가해보겠습니다. 

 

INSERT INTO bands (name)
VALUES('Iron Madian');

INSERT INTO bands(name)
VALUES('Deuce'), ('Avenged Sevenfold'),('Ankor');

 

VALUES('추가할 값') 여기서 추가하는 값은 bands 테이블에 name을 뜻합니다. 

 

그런데 왜 id값은 넣어주지 않는 걸까요? 왜냐하면 위에 테이블을 만들 때 id를 auto_increment로 설정해주었기 때문에 데이터가 추가되면 id는 자동 생성 되게 됩니다. 

 

2번째 예시처럼 한 번에 여러 데이터를 추가할 수도 있습니다. 

 

 

열이 여러 개 있는 테이블에 데이터 추가하기

 

albums 테이블 안에는 name, release_year, band_id 열(column)들이 있습니다. 이렇게 여러 개 열이 있는 경우는 아래와 같이 데이터를 추가하시면 됩니다. 

INSERT INTO albums (name, release_year, band_id)
VALUES ('The Number of the Beasts', 1985, 1),
('Power Slave', 1984, 1),
('Nightmare',2018, 2),
('Nightmare', 2010, 3),
('Test Album', NULL, 3);

SELECT * FROM albums;

 

• INSERT INTO [테이블 이름] (열 1, 열 2, 열 3) VALUES('열 1 해당 데이터', '열 2 해당 데이터', '열 3 해당 데이터')
• 해주시고 SELECT * FROM으로 albums (select는 다음 문단에서 다룹니다. ) 테이블을 선택해주시면 아래와 같은 결과가 나옵니다. 

데이터 id 5를 보시면 테스트 앨범의 release year이 null값으로 반환된 걸 보실 수 있습니다. 이 이유는, 위에서 null값으로 지정해줬기 때문입니다.  

 

6-2 SELECT

 

이제 위에서 추가한 항목을 확인해보겠습니다. SELECT를 활용해서 확인할 수 있습니다. 

 

SELECT * FROM bands;

 

이 코드를 실행해보시면 

 

위와 같이 bands 테이블의 열인 id와 name을 볼 수 있습니다. 

 

6-2-A 불러올 데이터수 정하기

 

SELECT의 활용법은 다양합니다 처음 두 데이터만 불러올 수도 있습니다. 

 

SELECT * FROM bands LIMIT 2;

 

맨 앞 2개만 나오게 됩니다. 

 

6-2-B 불러올 열 정하기

 

id를 뺀 name 열만 가져와보도록 하겠습니다. 

 

SELECT name FROM bands;

 

위와 같이 name값만 불러온 걸 볼 수 있습니다. 

 

6-2-C 칼럼 이름 바꾸기 AS

 

열 이름 또한 바꿀 수 있습니다. AS를 써주면 됩니다. 

 

SELECT  id AS 'ID', name AS 'Band Name'
FROM bands;

 

실행시키면 아래와 같이 바뀝니다. 

id-> ID, name -> Band Name으로 바뀌게 됩니다. 

 

6-2-D 순서 바꿔 나열 Order By (오름차순 내림차순)

 

• 오름차순

ORDER BY를 사용해주면 순서를 변경하여 나열할 수 있습니다.

 

SELECT * FROM bands ORDER BY name;

 

이름 순으로 나열하면 a-z순서로 나열됩니다. 

• 내림차순

뒤에 DESC(Decending)만 추가해주면 내림차순으로 나열하게 됩니다. 

 

SELECT * FROM bands ORDER BY name DESC;

아래 보시면 Z-A순으로 나열된 것을 보실 수 있습니다. 

 

6-3 DISTINCT

저희가 만들었던 album테이블을 보겠습니다.

 

위처럼 같은 이름을 가진 데이터가 있을 수 있습니다. 중복된 이름을 보지 않으시겠다면 DISTINCT를 사용해주면 됩니다. 

 

SELECT DISTINCT name FROM albums;

 

이렇게 하면 중복된 이름을 가려서 보여줍니다. 

Nightmare가 2개였었는데 하나만 보여주는 걸 확인하실 수 있습니다. 

 

 

데이터 수정/선택하기 - 2

 

7-1 데이터 업데이트/데이터 필터링

 

UPDATE

만약 우리가 앨범 테이블에서 앨범 중에서 연도를 잘못 넣었다고 가정해봅시다. 

 

데이터를 맞는 데이터로 수정하거나 업데이트해야 할 때 UPDATE를 써주면 됩니다. 

 

UPDATE albums
SET release_year = 1982
WHERE id = 1;

 

id가 1인 데이터에 release_year을 1982로 업데이트해주게 됩니다. 만약 마지막에 where id=1을 써주지 않으면 모든 데이터에  release_year을 1982로 바꾸게 됩니다. 

id가 1인 the number of the beasts의 release_year가 1982로 업데이트된 걸 확인할 수 있습니다. 

 

WHERE로 INT 데이터 필터링

위와 같이 WHERE을 잘 활용하면, 원하는 데이터를 선택하여 나열하고 싶을 때 유용합니다. 

 

만약 2000년 전에 발매된 곡만 나열하고 싶다면 아래처럼 WHERE을 사용하면 됩니다. 

 

SELECT * FROM albums
WHERE release_year <2000;

 

WHERE로 STRING 데이터 필터링

필터링하는 방법은 정말 많지만 그중 일부만 다뤄보겠습니다.

 

먼저 STRING에서 특정 값이 있을 때입니다. 

 

이름에서 "er"이 들어간 string을 모두 나열하고 싶을 때 쓰는  코드입니다. 

 

SELECT * FROM albums
WHERE name LIKE '%er%';

 

위에 코드를 해석하면

 

SELECT * FROM albums
WHERE name LIKE :  albums테이블에서 name 열(column)에서 찾습니다.

 

% er% %가 의미하는 것은 앞 뒤에 몇 글자가 있던 상관없이 er을 포함하면 찾는 것입니다. 

 

 

name에서 er을 포함한 string들이 나열됩니다.

 

7-2 여러 필터링 추가하기 OR/AND/BETWEEN/IS NULL

 

OR

위에서 er을 포함한 모든 string을 찾는 필터링을 적용해봤습니다. 그런데 er을 포함하거나 '또는' band_id를 2를 가진 데이터들을 찾고 싶다면 필터링을 하나 더 추가해줘야 합니다. 

 

SELECT * FROM albums
WHERE name LIKE '%er%' OR band_id = 2;

 

이 코드를 실행해주면 er을 포함한 string2개와 band id 가 2인 데이터까지 SELECT 된 걸 볼 수 있습니다. 

AND

AND도 있습니다. 

 

SELECT * FROM albums
WHERE release_year = 1984 AND band_id = 1;

 

release_year이 1984이면서 band_id가 1인 값을 찾습니다. 

 

BETWEEN

정해진 숫자 사이의 데이터들을 찾을 수도 있습니다. BETWEEN을 이용해서 2000년도부터 2018년 사이 앨범들을 리스트 해보겠습니다.

 

SELECT * FROM albums 
WHERE release_year BETWEEN 2000 AND 2018;

결과는 아래와 같습니다.

IS NULL

NULL인 데이터를 찾아낼 수도 있습니다. 

 

SELECT * FROM albums
WHERE release_year IS NULL;

 

release_year에서 null인 값을 찾습니다. 

 

7-3 데이터 지우기

 

DELETE FROM albums WHERE id = 5;

 

 

albums 테이블에서 id가 5인 값을 지웁니다. 

 

SELECT * FROM albums로 테이블을 확인해보면 

 

ID가 5였던 test 데이터가 없어진 걸 볼 수 있습니다. 

 

7-4 JOIN

 

SQL을 사용하면서 가장 자주 쓰면서 중요한 JOIN에 대해서 알아보겠습니다. 

 

먼저 예시를 보겠습니다. 

위에 albums 테이블을 보시면 끝에 band id가 들어간 것을 볼 수 있습니다. 이걸 bands 테이블에 band id와 연결하려 합니다. 

 

SELECT * FROM bands
JOIN albums ON bands.id = albums.band_id

 

SELECT * FROM bands에서 먼저 bands 테이블을 선택해주었습니다. 

 

그리고 bands테이블을 albums 테이블에 JOIN(연결)되게 만들었습니다. 

 

그리고 어디에 연결될 건지 알려줘야 합니다. 그래서 ON을 씁니다. 

 

ON bands.id: bands의 id 열을 의미합니다 

 

albums.band_id: albums테이블에 band_id 열을 의미합니다.

 

그리고 두 개가 같다고 해줬기 때문에 이제 이 코드를 실행하면 아래와 같이 두 개의 다른 테이블에서 같은 band id끼리 매칭 합니다.  

 

테이블이 연결되는 순서는 먼저 쓴 테이블이 앞에 나오고 나중에 쓴 테이블이 뒤에 나옵니다.

 

 

7-5 JOIN종류 INNER/LEFT/RIGHT

 

JOIN은 여러 가지가 종류가 있습니다. 그리고 A와 B 두 테이블을 집합이라 생각한다면 둘의 교집합, 합집합 , 차집합 등등으로 표현할 수 있습니다. 

 

 

A와 B를 두 테이블이라 생각한다면, 이해하기 편하실 겁니다. 

 

JOIN 종류	설명
INNER	A와 B 데이터에 교집합을 찾을때 iNNER을 사용합니다.
LEFT	B가 가지고있지않는 A 테이블에 데이터 + A와 B의 교집합
RIGHT	반대로 A가 가지고있지않는 B 테이블에 데이터 + A와 B의 교집합

 

예시로 살펴보겠습니다. 

 

그럼 어떤 테이블이 A고 어떤 테이블을 B로 써줘야 할까요? 우선 A위치에 있는 테이블은 왼쪽에 있으므로 LEFT 테이블입니다. B는 RIGHT 테이블입니다. 

 

먼저 써주는 테이블이 A(LEFT) 테이블이고 나중에 써주는 테이블이 B(RIGHT) 테이블 이게 됩니다. 

 

 

bands테이블을 먼저 써줬으므로 bands가 A(LEFT) albums 테이블이 B(RIGHT) 고됩니다. 

 

INNER JOIN의 예시입니다.

 

SELECT * FROM bands
INNER JOIN albums ON band.id = albums.band_id;

 

INNER JOIN을 썼을 때 결과는 그냥 JOIN을 썼을 때와 같습니다. 

 

 

두 테이블 모두 있는 교집합만 보여줍니다. 

 

LEFT JOIN의 예시입니다. 

 

SELECT * FROM bands
LEFT JOIN albums ON bands.id = albums.band_id;

 

실행시키면 아래와 같이 bands테이블에만 있던 ankor도 포함되어 나타내는 걸 확인할 수 있습니다. albums 테이블에는 Ankor에 관한 데이터가 없기 때문에 null로 표시됩니다. 

 

함수 적용

 

다양한 함수가 있지만 이번 예제에서는 평균(average)을 구해보겠습니다. 

 

AVG

 

SELECT AVG(release_year) FROM albums;

 

SELECT AVG(release_year) : 평균을 구할 열은 release_year이라고 알려주었습니다. 

 

FROM albums; : 그 열은 albums 테이블에 있다는 뜻입니다.  

SUM

 

AVG 썼던 위치에 SUM 만 넣어주면 됩니다. 

SELECT SUM(release_year) FROM albums;

모든 release_year에 sum값을 구해줍니다. 

COUNT

COUNT 또한 자주 쓰이는 함수중 하나입니다. 우리는 밴드가 몇 개의 앨범을 출시했는지 확인하려 합니다. 

우선 앨범 테이블을 사용해서 알아보겠습니다. 

 

 

위 테이블을 보시면 첫 두곡의 밴드 id가 같은 걸 보실 수 있습니다. 

 

SELECT band_id, COUNT(band_id) FROM albums
GROUP BY band_id;

 

band_id 개수를 세기 때문에 결과로 2가 나옵니다.