MySQL

MySQL 연습해보기

스키마 디자인

DATABASE 생성

CREATE DATABASE Hoo;
USE Hoo;

TABLE 생성

CREATE TABLE teams (
  id INT NOT NULL,
  primary key(id)
);

CREATE TABLE members (
  id INT NOT NULL,
  primary key(id)
  name varchar(255),
  age INT NOT NULL,
  team_id INT NOT NULL
);

TABLE COLUMN 추가, 변경 삭제 해보기

-- 테이블 열(column) 추가
ALTER TABLE teams
ADD team_id INT NOT NULL

-- 테이블 열(column) 추가 및 디폴트 값 설정
ALTER TABLE teams
ADD teams_color varchar(20) 
DEFAULT 디폴트값;

-- 테이블 열(column) 이름 변경
ALTER TABLE teams
RENAME COLUMN team_id TO teamId

-- 테이블 열(column) 삭제
ALTER TABLE teams
DROP COLUMN teamId

-- 테이블 열(column) 순서 변경하기
ALTER TABLE teams MODIFY teamId TINYINT AFTER name;

TABLE COLUMN 값 추가하기

-- todo: 팀A id=1, name='hoomin', age=30, team_id=1
INSERT INTO members (id, name, age, team_id) value(1, 'hoomin', 30, 1);
INSERT INTO members (id, name, age, team_id) value(2, 'elina', 17, 1);
INSERT INTO members (id, name, age, team_id) value(3, 'yoonji', 24, 1);
INSERT INTO members (id, name, age, team_id) value(4, 'ming', 23, 1);
INSERT INTO members (id, name, age, team_id) value(5, 'jelly', 13, 1);

-- todo: 팀B
INSERT INTO members (id, name, age, team_id) value(6, 'kein', 30, 2);
INSERT INTO members (id, name, age, team_id) value(7, 'venom', 21, 2);
INSERT INTO members (id, name, age, team_id) value(8, 'zico', 27, 2);
INSERT INTO members (id, name, age, team_id) value(9, 'cherry', 15, 2);
INSERT INTO members (id, name, age, team_id) value(10, 'alley', 25, 2);
INSERT INTO members (id, name, age, team_id) value(11, 'peter', 40, 2);
INSERT INTO members (id, name, age, team_id) value(12, 'lisa', 19, 2);

-- todo: 팀 없는 사람
INSERT INTO members (id, name, age) value(13, 'oop', 16);
INSERT INTO members (id, name, age) value(14, 'xoxo', 22);
INSERT INTO members (id, name, age) value(15, 'coco', 33);
INSERT INTO members (id, name, age) value(16, 'hoomin', 11);
INSERT INTO members (id, name, age, team_id) value(25, 'hoomin', 33, 2);

INSERT INTO 남용하지 말고 한번에 작성하자!

INSERT INTO members (id, name,age, team_id) 
  VALUES
    (21, 'lala', 5, 1),
    (22, 'lulu', 4, 2),
    (23, 'momo', 7, NULL),
    (24, 'titi', 6, 2);

PK 값은 자동으로 만들어지는게 좋겠다!

===> AUTO_INCREMENT;

TABLE 열(COLUMN) 타입 변경하기

ALTER TABLE members MODIFY team_id INT;

ALTER TABLE members MODIFY id INT AUTO_INCREMENT;
ALTER TABLE teams MODIFY id INT AUTO_INCREMENT;

ALTER TABLE members MODIFY age TINYINT UNSIGNED;
ALTER TABLE members MODIFY team_id TINYINT DEFAULT 0;

완성된 테이블

SELECT * FROM teams;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
+----+------+

SELECT * FROM members;
+----+--------+------+---------+
| id | name   | age  | team_id |
+----+--------+------+---------+
|  1 | hoomin |   30 |       1 |
|  2 | elina  |   17 |       1 |
|  3 | yoonji |   24 |       1 |
|  4 | ming   |   23 |       1 |
|  5 | jelly  |   13 |       1 |
|  6 | kein   |   30 |       2 |
|  7 | venom  |   21 |       2 |
|  8 | zico   |   27 |       2 |
|  9 | cherry |   15 |       2 |
| 10 | alley  |   25 |       2 |
| 11 | peter  |   40 |       2 |
| 12 | lisa   |   19 |       2 |
| 13 | oop    |   16 |    NULL |
| 14 | xoxo   |   22 |    NULL |
| 15 | coco   |   33 |    NULL |
| 16 | hoomin |   11 |    NULL |
| 21 | lala   |    5 |       1 |
| 22 | lulu   |    4 |       2 |
| 23 | momo   |    7 |    NULL |
| 24 | titi   |    6 |       2 |
| 25 | hoomin |   33 |       2 |
+----+--------+------+---------+

이름이 hoomin 인 멤버 조회하기

SELECT * FROM members
WHERE name='hoomin';

이름이 hoomin인 멤버의 이름과, 해당 멤버의 팀 이름 조회하기

SELECT members.name, teams.name FROM members
INNER JOIN teams 
ON members.team_id=teams.id AND members.name='hoomin'

• AND 보다는 WHERE 을 쓰도록 하자!

SELECT members.name, teams.name FROM members
INNER JOIN teams 
ON members.team_id=teams.id 
WHERE members.name='hoomin';

팀에 속해있는 멤버만 조회하기

SELECT members.name, teams.name FROM members
INNER JOIN teams 
ON members.team_id=teams.id;

팀에 속해있는 멤버의 총 인원 수 조회하기

SELECT COUNT(teams.name) 팀에속한멤버들 FROM teams
JOIN members ON members.team_id=teams.id;

각 팀 별 멤버의 총인원 조회하기

• 팀이 없는 NULL 인 것도 나온다.

SELECT team_id 팀ID, COUNT(team_id) team_members FROM members
GROUP BY team_id 
ORDER BY team_members DESC;

• 팀ID 가 1이거나 2인 것만 필터링해서 조회하기

SELECT team_id 팀ID, COUNT(*) team_members FROM members
WHERE team_id IN (1, 2)
GROUP BY team_id;

• 팀ID 가 추가될 경우 위에 작성한 쿼리는 못쓴다. NULL이 아닌 것만 조회하기

SELECT team_id 팀ID, COUNT(*) team_members FROM members
WHERE team_id IS NOT NULL
GROUP BY team_id;

각 팀에 속한 멤버의 이름 쉼표로 구분해서 나열하기

GROUP_CONCAT 이라는 문법을 새로 알았다!

SELECT members.team_id, GROUP_CONCAT(members.name) AS 팀원 FROM members
WHERE team_id IS NOT NULL
GROUP BY members.team_id;

각 팀에 속한 멤버 중 19세이상인 멤버의 이름을 쉼표로 구분해서 나열하기

SELECT members.team_id, GROUP_CONCAT(members.name) AS 팀원 FROM members
WHERE team_id IS NOT NULL AND members.age > 18
GROUP BY members.team_id;