SQL 재활 훈련 1일차 - 테스트 용 MySQL 설치, Database, Table, Insert

놀고 싶은데, 왜 다들 공부하는거야·2024년 12월 30일

목록 보기

1/9

MySQL 설치

mysql 실행

docker pull mysql
docker run --name mysql -e MYSQL_ROOT_PASSWORD=hello -d -p 3306:3306 mysql:latest

mysql 종료

docker rm mysql -f

mysql 접속

docker exec -it mysql bash
mysql -u root -p

Creating Database

---------Database Server-------------
|                                   |
|   ---Dogs DB---   ---User DB---   |
|   |           |   |           |   |
|   -------------   -------------   |
-------------------------------------

database server은 여러 db들로 이루어져 있는 것이다.

show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

database 생성

CREATE DATABASE <name>;

CREATE DATABASE pet_sqlop;

sqlOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| pet_sqlop           |
| sys                |
+--------------------+

database 삭제

DROP DATABASE <name>;

DROP DATABASE pet_sqlop;

database 접속
table을 만들거나, table에 데이터를 추가, 삭제, 수정하기 위해서는 database에 먼저 접속해야한다.

CREATE DATABASE chicken_coop;
USE chicken_coop;

현재 접속 중인 database 확인

SELECT database();
+--------------+
| database()   |
+--------------+
| chicken_coop |
+--------------+

Tables

relational database는 여러 table들로 구성되어 있다. table은 database 안에서 구조화된 형식(structured format)으로 data를 담고 있는 것이다.

cats table

Name	Breed	Age
Blue	Scottisql Fold	1
Rocket	Persian	3
Monty	Tabby	10
Sam	Munchkin	5

Name, Breed, Age는 header또는 columns라고 한다. column은 table에서 데이터 category를 만들 때 사용되는 것이다. 이 column에 해당하는 각 data들을 row라고 한다. 즉, | Sam | Munchkin | 5 |은 row라고 하는 것이다. 위의 경우 4개의 row들로 이루어져 있는 것이다.

column은 각 열마다 정해진 data type들이 있다. 가령, Name은 문자형이고 Age는 숫자만 가능하도록 만드는 것이다.

Numeric Types: INT, DECIMAL, NUMERIC, FLOAT, DOUBLE
String Types: CHAR, VARCHAR, TEXT, ENUM
Date Types: DATE, DATETIME, TIMESTAMP

모든 type들을 외울 필요가 없다. docs에 적혀있는 것을 바탕으로 쓰면 된다.

table 생성

CREATE TABLE tablename
(
    column_name data_type,
    column_name data_type
);

table 생성 예제

CREATE TABLE cats (
    name VARCHAR(50),
    age INT
);
 
CREATE TABLE dogs (
    name VARCHAR(50),
    breed VARCHAR(50),
    age INT
);

table 확인

sqlOW TABLES;
+--------------------+
| Tables_in_pet_sqlop |
+--------------------+
| cats               |
| dogs               |
+--------------------+

column 확인

sqlOW COLUMNS FROM <table>

sqlOW COLUMNS FROM cats;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(100) | YES  |     | NULL    |       |
| age   | int          | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

column 확인 2

DESCRIBE <table>
DESC <table>

DESC cats;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(100) | YES  |     | NULL    |       |
| age   | int          | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

table 삭제

DROP TABLE <table>;

DROP TABLE cats;

삭제하면 내용물인 row들도 모두 삭제되므로 조심하도록 하자.

comments 남기기

-- To list all tables in DB
sqlOW TABLES;

--을 붙이고 띄어쓰기하면 된다.

INSERT

table에 row를 추가해보도록 하자.

insert row

INSERT INTO cats(col1, col2) VALUES (col1_val, col2_val);

데이터를 넣을 때는 column과 value의 순서가 맞아야 한다.

다음과 같이 쓰면 된다.

CREATE TABLE cats(name VARCHAR(50), age INT);

INSERT INTO cats(name, age) VALUES ('Jetson', 7);
Query OK, 1 row affected (0.01 sec)

row 확인, query하기

SELECT <column> FROM <table>;

column 대신에 *로 모든 column에 해당하는 값들을 가져올 수 있다.

SELECT * FROM cats;

+--------+------+
| name   | age  |
+--------+------+
| Jetson |    7 |
+--------+------+

multiple insert

INSERT INTO cats(col1, col2) 
VALUES (col1_val1, col2_val1)
       (col1_val2, col2_val2)
       (col1_val3, col2_val3);

위와 같이 해당하는 column에 대해서, 복수 개의 row를 넣고 싶다면 한번에 가능하다.

INSERT INTO cats (name, age) 
VALUES 
  ('Meatball', 5), 
  ('Turkey', 1), 
  ('Potato Face', 15);

SELECT * FROM cats;
+-------------+------+
| name        | age  |
+-------------+------+
| Jetson      |    7 |
| Meatball    |    5 |
| Turkey      |    1 |
| Potato Face |   15 |
+-------------+------+
4 rows in set (0.00 sec)

잘 들어간 것을 볼 수 있다.

table 생성부터 row값 추가, 확인

CREATE TABLE people (
    first_name VARCHAR(20),
    last_name VARCHAR(20),
    age INT
);

INSERT INTO people(first_name, last_name, age) VALUES('Tina', 'Belcher', 13);
INSERT INTO people(first_name, last_name, age) VALUES('Bob', 'Belcher', 42);

SELECT * FROM people;
+------------+-----------+------+
| first_name | last_name | age  |
+------------+-----------+------+
| Tina       | Belcher   |   13 |
| Bob        | Belcher   |   42 |
+------------+-----------+------+

DROP TABLE people;

NULL, NOT_NULL

DESC로 table 확인 시에 Null이라는 항목이 있는 것을 볼 수 있다.

DESC cats;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

Null이 YES, YES로 되어있는데, 이는 해당 column의 값으로 NULL을 허용하겠다는 것이다. 헷갈리면 안되는 것은 Null과 zero value(default 값)은 완전히 다른 것이다. Null은 정말로 누락된 값을 의미하는 것이다.

NULL값 확인하기

INSERT INTO cats(name) VALUES('Todd');

SELECT * FROM cats;
+-------------+------+
| name        | age  |
+-------------+------+
| Jetson      |    7 |
| Meatball    |    5 |
| Turkey      |    1 |
| Potato Face |   15 |
| Todd        | NULL |
+-------------+------+

Todd의 age가 NULL인 것을 확인할 수 있다.

name도 그렇고 ,age도 NULL을 허용해서는 안된다. 이를 table에서 column에서 강제적으로 처리할 수 있다.

CREATE TABLE cats2 
(
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL
);

INSERT INTO cats2(name) VALUES('Bilbo');
ERROR 1364 (HY000): Field 'age' doesn't have a default value

age column이 누락되어 error가 발생하는 것을 볼 수 있다.

cats2 table

DESC cats2;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(100) | NO   |     | NULL    |       |
| age   | int          | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

Null이 No인 것을 볼 수 있다.

text에서의 특수 문자 사용

기본적으로 mysql은 text를 표현할 때는 ''이든 `""이든 상관없지만 다른 database 제품 군을 사용하면 이를 엄밀히 구분한다.

그런데, 만약 ''가 text안에 있다면 어떻게 해야할까?

CREATE TABLE sqlops (
    name VARCHAR(50)
);

INSERT INTO sqlops(name) VALUES ('mario's pizza');

위와 같은 경우가 위험한 경우인 것이다.

그래서 내부의 text안에 \를 붙여주는 것이다.

INSERT INTO sqlops(name) VALUES ('mario\'s pizza');

이렇게 sql문에서 사용되는 특수한 문자가 text안에 있다면 위와 같이 \을 붙여서 문제를 벗어날 수 있다.

Default value

DESC로 cats table의 정의를 확인하면 Default라는 column이 있는 것을 볼 수 있다.

DESC cats;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

현재 Default 값은 NULL이며, 값이 채워지지 않으면 NULL값이 들어간다는 것이다. 이는 Null을 YES로 설정했기 때문에 자동으로 설정된 값이다.

default value를 설정하고 싶다면 다음과 같이 table 생성 시에 할 수 있다.

CREATE TABLE cats3
(
    name VARCHAR(100) DEFAULT 'unnamed',
    age INT DEFAULT 99
);

이제 name의 default 값이 unnamed로 설정되었고, age의 default 값이 99으로 설정한 것이다.

INSERT INTO cats3(age) VALUES(2);

+---------+------+
| name    | age  |
+---------+------+
| unnamed |    2 |
+---------+------+

설정되지 않은 name column의 값이 unnamed로 설정된 것을 볼 수 있다.

DEFAULT는 NOT NULL과 함께 동작할 수 있는데, NOT NULL이 없다면 column의 value로 NULL을 넣어줄 수 있다. 가령 다음이 가능한 것이다.

INSERT INTO cats3(name) VALUES(NULL);

SELECT * FROM cats3;
+---------+------+
| name    | age  |
+---------+------+
| unnamed |    2 |
| NULL    |   99 |
+---------+------+

그래서 다음과 같이 DEFAULT와 함께 나란히 NOT NULL을 쓸 수 있다.

CREATE TABLE cats4
(
    name VARCHAR(100) NOT NULL DEFAULT 'unnamed',
    age INT NOT NULL DEFAULT 99
);

이제 name은 NULL을 넣을 수 없고, default값으로 unnamed을 가진다.

DESC cats4;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(100) | NO   |     | unnamed |       |
| age   | int          | NO   |     | 99      |       |
+-------+--------------+------+-----+---------+-------+

이제 table의 정의를 확인하면 Null이 불가능하고, Default 값이 모두 설정된 것을 볼 수 있다.

Key

cats4 table을 확인하면 Key column이 비어있는 것을 볼 수 있다.

DESC cats4;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(100) | NO   |     | unnamed |       |
| age   | int          | NO   |     | 99      |       |
+-------+--------------+------+-----+---------+-------+

key란 무엇인가? 사실 table의 row는 unique해야하는 특성이 있다. 이는 relational database의 table의 개념이 집합이기 때문이다. 집합 안에 중복되는 원소가 있는 것은 있을 수 없다. 따라서, 이 row들을 식별할 수 있도록 id값을 주어야하는데, 이것이 바로 key이다.

Name	Breed	Age	CatID
Monty	Tabby	1	1
Monty	Tabby	1	2
Monty	Tabby	1	3
Monty	Tabby	1	4

key에는 여러 개념들이 존재하는데 다음과 같다.
1. Super Key: 하나 또는 여러 개의 attribute들로 이루어져 row를 식별할 수 있는 key들로 유일성을 보장한다. 가령, (학번), (학번, 학생 이름), (학번, 학생 주소) 등이 있다.
2. Candidate Key: super key가 유일성을 만족하였으므로, 이 중에서 최소성을 만족하는 key들로 구성된다. 가령, (학번, 학생 이름)에서 '학생 이름'을 제거해도 (학번)만으로 유일성을 만족하므로, (학번)만 남기는 것이다. 이렇게 super key 중에 일부 attribute를 제거해서 key를 구성해도 유일성이 지켜지면 candidate key이다.
3. Primary Key: candidate key 중에 선택된 key값으로 row의 식별자 값이다.
4. Alternate Key: candidate key 중에 primary key 값으로 선택되지 못한 key 값들이다.
5. Foreign Key: 다른 table의 primary key 값을 table에서 참조할 때 foreign key라고 한다.
6. Composite Key: 하나의 attribute(column)만으로는 key가 될 수 없어서, 여러 key들을 합쳐만든 key를 일컫는다. 이는 super key, candidate key, primary key 모두 될 수 있다.

-----Super Key------------------
| -------Candidate Key-------  |
| |                         |  |
| | -----Primary Key------- |  |
| | |                     | |  |
| | ----------------------- |  |
|                              |
--------------------------------

table을 생성할 때 primary key가 되는 column을 선택할 수 있다.

CREATE TABLE unique_cats (
    cat_id INT NOT NULL PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

cat_id에 PRIMARY KEY라는 제약 조건을 주었다. 이제 cat_id가 하나의 PRIMARY KEY가 되어서 동일한 값을 넣을 수 없다.

참고로, 위와 같이 cat_id오른쪽에 있는 제약조건이 너무 길어지게 되는 경우에 가독성이 안좋아지므로, 따로 PRIMARY KEY 제약조건을 아래에 적어주는 방법도 있다.

CREATE TABLE unique_cats (
    cat_id INT NOT NULL,
    name VARCHAR(100),
    age INT,
    PRIMARY KEY(cat_id)
);

PRIMARY KEY로 제약조건을 아래에 추가하면서 cat_id를 할당시키는 것이다.

DESC unique_cats;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| cat_id | int          | NO   | PRI | NULL    |       |
| name   | varchar(100) | YES  |     | NULL    |       |
| age    | int          | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+

cat_id의 key가 PRI으로 표현된 것을 알 수 있다.

이제 중복된 key값을 넣어보도록 하자.

INSERT INTO unique_cats(cat_id, name, age) VALUES (1, 'biugo', 2);
INSERT INTO unique_cats(cat_id, name, age) VALUES (1, 'biugo', 3);
ERROR 1062 (23000): Duplicate entry '1' for key 'unique_cats.PRIMARY'

Duplicate entry라는 말이 나온다. cat_id 값을 1이 아닌 다른 값으로 바꿔주어야 된다.

INSERT INTO unique_cats(cat_id, name, age) VALUES (2, 'biugo', 3);

성공하는 것을 볼 수 있다.

그런데, 매번 PRIMARY KEY값을 넣어주고 싶지 않을 때가 있다. 왜냐하면 PRIMARY KEY에 해당하는 column 값은 유저가 직접 입력하는 값이 아니라, system을 위해 존재하는 column값이기 때문에 어떠한 값이든 상관없다. 즉, 자동으로 증가해도 문제가 없다는 것이다.

그래서 AUTO INCREMENT라는 제약조건이 있다.

먼저 기존 unique_cats table을 없애도록 하자.

DROP TABLE unique_cats;

다음로 AUTO_INCREMENT라는 기능을 column에 추가하도록하면 된다.

CREATE TABLE unique_cats (
    cat_id INT AUTO_INCREMENT,
    name VARCHAR(100),
    age INT,
    PRIMARY KEY(cat_id)
);

다음으로 tuple(row)를 추가해보도록 하자.

INSERT INTO unique_cats(name, age) VALUES('Boingo', 1);

SELECT * FROM unique_cats;
+--------+--------+------+
| cat_id | name   | age  |
+--------+--------+------+
|      1 | Boingo |    1 |
+--------+--------+------+

cat_id 값을 주지 않아도 자동으로 설정된 것을 볼 수 있다.

한 번 더 INSERT해보도록 하자.

INSERT INTO unique_cats(name, age) VALUES('Boingo', 1);
INSERT INTO unique_cats(name, age) VALUES('Boingo', 1);
INSERT INTO unique_cats(name, age) VALUES('Boingo', 1);
INSERT INTO unique_cats(name, age) VALUES('Boingo', 1);
INSERT INTO unique_cats(name, age) VALUES('Boingo', 1);

SELECT * FROM unique_cats;
+--------+--------+------+
| cat_id | name   | age  |
+--------+--------+------+
|      1 | Boingo |    1 |
|      2 | Boingo |    1 |
|      3 | Boingo |    1 |
|      4 | Boingo |    1 |
|      5 | Boingo |    1 |
|      6 | Boingo |    1 |
|      7 | Boingo |    1 |
+--------+--------+------+

PRIMARY KEY인 cat_id값을 설정하지 않아도 계속해서 증가하는 것을 볼 수 있다.

이제 DESC로 unique_cats table을 확인해보도록 하자.

DESC unique_cats;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| cat_id | int          | NO   | PRI | NULL    | auto_increment |
| name   | varchar(100) | YES  |     | NULL    |                |
| age    | int          | YES  |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+

놀고 싶은데, 왜 다들 공부하는거야

R3의 망령

다음 포스트