728x90
1. MySQL 사용
1-1. 관계형데이터베이스 선택 기준
안정성
- DB Engine 자체적인 요인
Uber, Airbnb, 그리고 Shopify를 포함하여 5,717개의 회사가 RDBMS로 MySQL을 사용
성능과 기능
- 성능이나 기능은 돈이나 노력으로 어느정도 해결 가능.
커뮤니티(인지도)
- 필요한 지식과 경험을 얻는 데 중요한 부분
- DB-Engines.com에서 제공하는 2022년 8월 기준 DBMS의 순위에서 상위권 위치
- DB-Engine 랭킹 기준
- 웹 사이트 언급 횟수 (Number of mentions of the system on websites)
- 구글 트렌드 검색 빈도 (General interest in the system)
- 기술 토론 빈도 (Frequency of technical discussions about the system)
- 채용 시장(Number of job offers, in which the system is mentioned)
- 전문가 집단의 숫자 (Number of profiles in professional networks, in which the system is mentioned.)
- SNS 언급 횟수 (Relevance in social networks. We count the number of Twitter tweets, in which the system is mentioned.)
2. SQL Statements
2-1. 데이터베이스 객체
2-1-1. Database Object
2차원 테이블(Table), 뷰(View), 인덱스(Index) 등 데이터베이스 내에 정의하는 모든 것으로 데이터베이스 내에 실체를 갖는 것.
- 테이블(Table)
- row, column으로 이루어진 2차원 테이블
- 뷰(View)
- 하나 이상의 기본 테이블로부터 유도된 가상 테이블
- 저장장치 내에 물리적으로 존재하진 않지만 사용자에게는 있는 것처럼 간주
- 데이터 보정 작업, 처리 과정 시험 등 임시적인 작업을 위한 용도로 활용
- 인덱스(Index)
- 테이블의 행에 색인을 지정하여 데이터베이스를 통한 레코드 출력을 빠르게 함
## 객체 명명 규칙
- 기존 이름이나 예약어와 중복하지 않는다.
- 숫자로 시작할 수 없다.
- 언더스코어(_) 이외의 기호는 사용할 수 없다.
- 한글을 사용할 때는 더블쿼트(MySQL에서는 백쿼트)로 둘러싼다.
- 시스템이 허용하는 길이를 초과하지 않는다.
- 객체에 이름을 정할때는 객체가 담고 있는 정보를 명학하게 표현할 수 있는 이름을 선택하도록 한다
2-1-2. Schema
데이터베이스 내의 객체(table, view, index)를 담는 컨테이너
mysqld(서버 프로그램)에 접속한 후, show databases; 명령을 통해서 스키마 목록 확인 가능
$ mysql -u root -pmysql> SHOW DATABASES;
+--------------------------+
| Database |
+--------------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------------+3. DDL (Data Definition Language)
Schema 내의 객체를 정의하고 관리할 때 사용되는 쿼리문
3-1. 스키마 생성
CREATE DATABASE; DDL 명령을 통해 스키마 생성
mysql> CREATE DATABASE example;
mysql> SHOW DATABASES;
+--------------------------+
| Database |
+--------------------------+
| information_schema |
| mysql |
| performance_schema |
| example |
| sys |
+--------------------------+USE example; 위에서 생성한 example 스키마로 이동
SHOW TABLES; 스키마 내 테이블(table) 객체 목록을 출력
mysql> USE example;
mysql> SHOW TABLES;
Empty set (0.00 sec)DROP TABLE 테이블명; 테이블 삭제
mysql> DROP TABLE example;3-2. Table 생성
Ex) 1대다 관계의 users, posts table 형성
CREATE TABLE {테이블명} (컬럼명 , 자료형, 제약 조건)
- 자료형: INTEGER, VARCHAR, DATETIME 등
- 문자열형은 소괄호 안에 최대길이 설정
- NULL 해당 열에 null 값 가능/ NOT NULL null 값 불가능(무조건 데이터 입력)
- PRIMARY KEY 고유키 지정
- AUTO_INCREMENT 번호 자동 증가
- DEFAULT 자료의 기본값 지정
- ON UPDATE 자료의 수정이 발생하면 기록되는 값
- FOREIGN KEY (user_id) REFERENCES users (id) - 외래키 설정
mysql> CREATE TABLE users
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(200) NOT NULL,
profile_image VARCHAR(1000) NULL,
password VARCHAR(200) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
);mysql> CREATE TABLE posts
(
id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(100) NOT NULL,
content VARCHAR(2000) NULL,
user_id INT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
PRIMARY KEY (id),
FOREIGN KEY (user_id) REFERENCES users (id)
);- 테이블 조회 DESC 테이블명 (description 축약어)
mysql> DESC users; +---------------+---------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +---------------+---------------+------+-----+-------------------+-----------------------------+ | id | int | NO | PRI | NULL | auto_increment | | name | varchar(50) | NO | | NULL | | | email | varchar(200) | NO | | NULL | | | profile_image | varchar(1000) | YES | | NULL | | | password | varchar(200) | NO | | NULL | | | created_at | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED | | updated_at | timestamp | YES | | NULL | on update CURRENT_TIMESTAMP | +---------------+---------------+------+-----+-------------------+-----------------------------+
mysql> DESC posts;
+------------+---------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+-------------------+-----------------------------+
| id | int | NO | PRI | NULL | auto_increment |
| title | varchar(100) | NO | | NULL | |
| content | varchar(3000) | YES | | NULL | |
| user_id | int | NO | MUL | NULL | |
| created_at | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| updated_at | timestamp | YES | | NULL | on update CURRENT_TIMESTAMP |
+------------+---------------+------+-----+-------------------+-----------------------------+3-3. Table 변경
3-3-1. 열(column) 추가
users 테이블에 age 열 추가
ALTER TABLE 테이블명(users) / 행위(ADD) / 열 이름(age) / 자료형(INT) / 제약 조건(NOT NULL)
mysql> ALTER TABLE users ADD age INT NOT NULL;
Query OK, 0 rows affected (0.02 sec)mysql> desc users;
+---------------+---------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------+------+-----+-------------------+-----------------------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| email | varchar(200) | NO | | NULL | |
| profile_image | varchar(1000) | YES | | NULL | |
| password | varchar(200) | NO | | NULL | |
| created_at | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| updated_at | timestamp | YES | | NULL | on update CURRENT_TIMESTAMP |
| age | int | NO | | NULL | |
+---------------+---------------+------+-----+-------------------+-----------------------------+3-3-2. 열(column) 삭제
users 테이블에 profile_image 열 삭제
ALTER TABLE 테이블명(users) / 행위(DROP) / 열 이름(profile_image)
mysql> ALTER TABLE users DROP profile_image;
mysql> desc users;
+---------------+---------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------+------+-----+-------------------+-----------------------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| email | varchar(200) | NO | | NULL | |
| password | varchar(200) | NO | | NULL | |
| created_at | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| updated_at | timestamp | YES | | NULL | on update CURRENT_TIMESTAMP |
| age | int | NO | | NULL | |
+---------------+---------------+------+-----+-------------------+-----------------------------+3-3-3. 열(column) 수정
posts 테이블 content 열 자료형 변경
ALTER TABLE 테이블명(posts) / 행위(MODIFY) / 열 이름(content) / 자료형(TEXT) / 제약 조건(NULL)
mysql> ALTER TABLE posts MODIFY content TEXT NULL;
mysql> desc posts;
+------------+------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+-------------------+-----------------------------+
| id | int | NO | PRI | NULL | auto_increment |
| title | varchar(100) | NO | | NULL | |
| content | varchar -> text | YES | | NULL | |
| user_id | int | NO | MUL | NULL | |
| created_at | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| updated_at | timestamp | YES | | NULL | on update CURRENT_TIMESTAMP |
+------------+------------------+------+-----+-------------------+-----------------------------+3-3-4. 열(row) 삭제
열(row) 하나 삭제
DELETE FROM 테이블명 WHERE 조건;
모든 열 삭제
TRUNCATE TABLE 테이블명
mysql> DELETE FROM posts WHERE condition;mysql> TRUNCATE TABLE posts;4. DML(Data Manipulation Language)
데이터를 조작하는 쿼리문
4-1. INSERT Statement
INSERT INTO 테이블명 (column명, column명..) VALUES (value, value ..)
users 테이블에 사용자 데이터 추가 및 조회(DQL)
mysql> INSERT INTO users (name, email, password, age) VALUES ("Rebekah Johnson", "Glover12345@email.com", "password", 21);
mysql> INSERT INTO users (name, email, password, age) VALUES ("Fabian Predovic", "Connell12345@email.com", "password", 22);
mysql> SELECT * FROM users;
+----+-------------------+-----------------------------+------------+---------------------+------------+-----+
| id | name | email | password | created_at | updated_at | age |
+----+-------------------+-----------------------------+------------+---------------------+------------+-----+
| 1 | Rebekah Johnson | Glover12345@email.com | abcdeft123 | 2022-04-22 09:45:58 | NULL | 21 |
| 2 | Fabian Predovic | Connell12345@email.com | abcdeft123 | 2022-04-22 09:45:58 | NULL | 22 |
+----+-------------------+-----------------------------+------------+---------------------+------------+-----+4-2. UPDATE Statement
UPDATE 테이블명 SET column명='수정할 내용' [WHERE 조건]
users 테이블에 id=2인 사용자의 이메일 변경 및 조회
mysql> UPDATE users SET email='RainToday@email.com' WHERE id=2;
mysql> SELECT * FROM users;
+----+-------------------+-----------------------------+------------+---------------------+------------+-----+
| id | name | email | password | created_at | updated_at | age |
+----+-------------------+-----------------------------+------------+---------------------+------------+-----+
| 1 | Rebekah Johnson | Glover12345@email.com | abcdeft123 | 2022-04-22 09:45:58 | NULL | 21 |
| 2 | Fabian Predovic | RainToday@email.com | abcdeft123 | 2022-04-22 09:45:58 | NULL | 22 |
+----+-------------------+-----------------------------+------------+---------------------+------------+-----+4-3. DELETE Statement
DELETE FROM 테이블명 [WHERE 조건]
mysql> DELETE FROM users WHERE id = 1;
mysql> SELECT * FROM users;
+----+-------------------+-----------------------------+------------+---------------------+------------+-----+
| id | name | email | password | created_at | updated_at | age |
+----+-------------------+-----------------------------+------------+---------------------+------------+-----+
| 2 | Fabian Predovic | RainToday@email.com | abcdeft123 | 2022-04-22 09:45:58 | NULL | 22 |
+----+-------------------+-----------------------------+------------+---------------------+------------+-----+5. DQL (Data Query Language)
데이터를 쿼리하는데 사용되는 SQL문(데이터베이스 서버에 원하는 데이터 요청)
5-1. 예제 테이블
5-1-1. jobs
mysql> SELECT * FROM jobs;
+----+-----------+
| id | job |
+----+-----------+
| 1 | 개발자 |
| 2 | 경찰 |
| 3 | 소방관 |
| 4 | 교사 |
| 5 | 건축가 |
+----+-----------+5-1-2. users
mysql> SELECT * FROM users;
+----+-----------------+-----+--------+
| id | name | age | job_id |
+----+-----------------+-----+--------+
| 1 | Rebekah Johnson | 21 | 1 |
| 2 | Fabian Predovic | 22 | 1 |
| 3 | Elenor Gottlieb | 23 | 1 |
| 4 | Madge Ledner | 20 | 2 |
| 5 | Zelma Kunde | 19 | NULL |
+----+-----------------+-----+--------+5-2. 데이터 조회/ 검색
5-2-1. 모든 테이블 조회
mysql> SELECT * FROM users;
+----+-----------------+-----+--------+
| id | name | age | job_id |
+----+-----------------+-----+--------+
| 1 | Rebekah Johnson | 21 | 1 |
| 2 | Fabian Predovic | 22 | 1 |
| 3 | Elenor Gottlieb | 23 | 1 |
| 4 | Madge Ledner | 20 | 2 |
| 5 | Zelma Kunde | 19 | NULL |
+----+-----------------+-----+--------+5-2-2. 특정 Column 조회
SELECT 컬럼명, 컬럼명 FROM 테이블명;
mysql> SELECT users.name, users.age FROM users;
+-----------------+-----+
| name | age |
+-----------------+-----+
| Rebekah Johnson | 21 |
| Fabian Predovic | 22 |
| Elenor Gottlieb | 23 |
| Madge Ledner | 20 |
| Zelma Kunde | 19 |
+-----------------+-----+5-2-3. WHERE 절 활용 특정 조건 조회
SELECT 컬럼명, 컬럼명, .. FROM 테이블명 WHERE 조건;
- 복수조건은 Where 뒤에 AND 로 연결
- 특정 문자포함 조건 검색 - WHERE 컬럼명 LIKE '포함단어%' (위치 무관하여 포함된 경우 조회 - ‘%문자%’)
mysql> SELECT id, name, age FROM users WHERE name LIKE %ed%;
+----+-----------------+-----+
| id | name | age |
+----+-----------------+-----+
| 2 | Fabian Predovic | 22 |
| 4 | Madge Ledner | 20 |
+----+-----------------+-----+5-3. 테이블 결합
5-3-1. 결합 개념
5-3-1-1. 곱집합
두 집합의 요소를 각각 매칭하여 새롭게 요소를 만드는 것 - 집합들의 요소 개수의 곱만큼 요소 형성
5-3-2. 테이블 결합 조회(JOIN)
5-3-2-1. 교차 결합(Cross Join)
테이블을 곱집합 형태로 조회
SELECT 테이블.컬럼명, .. FROM 테이블1, 테이블2
mysql> SELECT
users.id,
users.name,
users.job_id,
jobs.job
FROM users, jobs;
+----+-----------------+--------+-----------+
| id | name | job_id | job |
+----+-----------------+--------+-----------+
| 5 | Zelma Kunde | NULL | 개발자 |
| 4 | Madge Ledner | 2 | 개발자 |
| 3 | Elenor Gottlieb | 1 | 개발자 |
| 2 | Fabian Predovic | 1 | 개발자 |
| 1 | Rebekah Johnson | 1 | 개발자 |
| 5 | Zelma Kunde | NULL | 경찰 |
| 4 | Madge Ledner | 2 | 경찰 |
| 3 | Elenor Gottlieb | 1 | 경찰 |
| 2 | Fabian Predovic | 1 | 경찰 |
| 1 | Rebekah Johnson | 1 | 경찰 |
| 5 | Zelma Kunde | NULL | 소방관 |
| 4 | Madge Ledner | 2 | 소방관 |
| 3 | Elenor Gottlieb | 1 | 소방관 |
| 2 | Fabian Predovic | 1 | 소방관 |
| 1 | Rebekah Johnson | 1 | 소방관 |
| 5 | Zelma Kunde | NULL | 교사 |
| 4 | Madge Ledner | 2 | 교사 |
| 3 | Elenor Gottlieb | 1 | 교사 |
| 2 | Fabian Predovic | 1 | 교사 |
| 1 | Rebekah Johnson | 1 | 교사 |
| 5 | Zelma Kunde | NULL | 건축가 |
| 4 | Madge Ledner | 2 | 건축가 |
| 3 | Elenor Gottlieb | 1 | 건축가 |
| 2 | Fabian Predovic | 1 | 건축가 |
| 1 | Rebekah Johnson | 1 | 건축가 |
+----+-----------------+--------+-----------+5-3-2-2. 내부 결합(Inner Join)
기준 테이블과 Join이 걸리는 테이블 양쪽 모두 결합 조건이 매칭되는 열(row)만 조회
SELECT 테이블.컬럼명, .. FROM 테이블 INNER JOIN 테이블2 ON 조건
mysql> SELECT
users.id,
users.name,
users.job_id,
jobs.job
FROM users INNER JOIN jobs ON users.job_id = jobs.id;
+----+-----------------+--------+-----------+
| id | name | job_id | job |
+----+-----------------+--------+-----------+
| 1 | Rebekah Johnson | 1 | 개발자 |
| 2 | Fabian Predovic | 1 | 개발자 |
| 3 | Elenor Gottlieb | 1 | 개발자 |
| 4 | Madge Ledner | 2 | 경찰 |
+----+-----------------+--------+-----------+5-3-2-3. 외부 결합(Outer Join)
- 외부결합 개념
5-3-2-3-1. LEFT (OUTER) JOIN & RIGHT (OUTER) JOIN
Left table을 기준으로 조건을 만족하는 row 를 조인되는 table의 자료와 함께 모두 출력하고 없는 자료들은 null 값이 입력된다. (Right 조인은 기준이 뒤에 조인되는 테이블)
- (Left join) users 기준 jobs 조인
mysql> SELECT
users.id,
users.name,
users.job_id,
jobs.job
FROM users LEFT JOIN jobs ON users.job_id = jobs.id;
+----+-----------------+--------+-----------+
| id | name | job_id | job |
+----+-----------------+--------+-----------+
| 1 | Rebekah Johnson | 1 | 개발자 |
| 2 | Fabian Predovic | 1 | 개발자 |
| 3 | Elenor Gottlieb | 1 | 개발자 |
| 4 | Madge Ledner | 2 | 경찰 |
| 5 | Zelma Kunde | NULL | NULL |
+----+-----------------+--------+-----------+- (Left join) jobs 기준 users 조인
mysql> SELECT
users.id,
users.name,
users.job_id,
jobs.job
FROM jobs LEFT JOIN users ON users.job_id = jobs.id;
+------+-----------------+--------+-----------+
| id | name | job_id | job |
+------+-----------------+--------+-----------+
| 1 | Rebekah Johnson | 1 | 개발자 |
| 2 | Fabian Predovic | 1 | 개발자 |
| 3 | Elenor Gottlieb | 1 | 개발자 |
| 4 | Madge Ledner | 2 | 경찰 |
| NULL | NULL | NULL | 소방관 |
| NULL | NULL | NULL | 교사 |
| NULL | NULL | NULL | 건축가 |
+------+-----------------+--------+-----------+5-3-2-3-2. FULL (OUTER) JOIN
조건에 맞는 두 테이블의 모든 값을 표현하고, 빈 자료는 null 값 할당
mysql> SELECT
users.id,
users.name,
users.job_id,
jobs.job
FROM users LEFT JOIN jobs ON users.job_id = jobs.id
UNION
SELECT
users.id,
users.name,
users.job_id,
jobs.job
FROM users RIGHT JOIN jobs ON users.job_id = jobs.id;
+------+-----------------+--------+-----------+
| id | name | job_id | job |
+------+-----------------+--------+-----------+
| 1 | Rebekah Johnson | 1 | 개발자 |
| 2 | Fabian Predovic | 1 | 개발자 |
| 3 | Elenor Gottlieb | 1 | 개발자 |
| 4 | Madge Ledner | 2 | 경찰 |
| 5 | Zelma Kunde | NULL | NULL |
| NULL | NULL | NULL | 소방관 |
| NULL | NULL | NULL | 교사 |
| NULL | NULL | NULL | 건축가 |
+------+-----------------+--------+-----------+참고자료
'DB > SQL' 카테고리의 다른 글
| [MySQL] dbmate 활용해서 DB쉽게 구성하기 (0) | 2022.08.28 |
|---|
