[MySQL] Query 문법

Database

데이터베이스 목록 표시

SHOW databases;

데이터베이스 생성

CREATE DATABASE [DB명];

데이터베이스 삭제

DROP DATABASE [삭제할 DB명];

데이터베이스에 접근(선택)

USE [선택할 DB명];

---

 

Table

테이블 목록 표시

SHOW TABLES;

테이블 설계 정보 확인

DESC [테이블명];

• sample

DESC categories;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(45) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

테이블 생성

CREATE TABLE [table명] (
  [column1] [datatype] [option],
  [column2] [datatype] [option],
  [column3] [datatype] [option],
  ...
  PRIMARY KEY ([PK로 지정할 column명]),
  FOREIGN KEY ([FK로 지정할 column명]) REFERENCES [참조할 table명] ([참조할 PK명])
);

※ foreign key는 필요한 경우에만 입력

• sample

CREATE TABLE drinks (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(45) NOT NULL,
  image_url VARCHAR(2000) NOT NULL,
  description VARCHAR(500) NOT NULL,
  is_new TINYINT,
  category_id INT NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (category_id) REFERENCES categories (id)
);
+-------------+---------------+------+-----+---------+----------------+
| Field       | Type          | Null | Key | Default | Extra          |
+-------------+---------------+------+-----+---------+----------------+
| id          | int           | NO   | PRI | NULL    | auto_increment |
| name        | varchar(45)   | NO   |     | NULL    |                |
| image_url   | varchar(2000) | NO   |     | NULL    |                |
| description | varchar(500)  | NO   |     | NULL    |                |
| is_new      | tinyint       | YES  |     | NULL    |                |
| category_id | int           | NO   | MUL | NULL    |                |
+-------------+---------------+------+-----+---------+----------------+

테이블 삭제

DROP TABLE [table명];

테이블에 column 추가

ALTER TABLE [table명] ADD [추가할 column명] [type] [필요한 option];

• sample

ALTER TABLE nutrition ADD drink_id INT NOT NULL;

column의 type 변경

ALTER TABLE [table명] MODIFY [column명] [변경할 type];

• sample

ALTER TABLE drinks MODIFY english_name varchar(200);

---

 

Records (Row, Data)

테이블 내의 전체 데이터 조회

SELECT * from [조회할 table명];

• sample

mysql> SELECT * from categories;
+----+----------------------+
| id | name                 |
+----+----------------------+
|  1 | 콜드 브루 커피          |
+----+----------------------+

중복을 제거하여 조회

SELECT DISTINCT name FROM a_user; 

테이블 내의 전체 데이터를 목록 형태로 조회

SELECT * from [조회할 table명]\\G

데이터가 길어서 표가 깨져서 가독성이 좋지 않을 때

;대신 \\G라는 meta command를 사용하면 데이터를 표가 아닌 목록 형태로 표시할 수 있다.

• sample

SELECT * from drinks\\G
*************************** 1. row ***************************
         id: 1
       name: 나이트로 바닐라 크림
  image_url: <https://image.istarbucks.co.kr/upload/store/skuimg/2021/04/[9200000002487]_20210426091745609.jpg>
description: 부드러운 목넘김의 나이트로 커피와 바닐라 크림의 매력을 한번에 느껴보세요!
     is_new: 1
category_id: 1
*************************** 2. row ***************************
         id: 2
       name: 아이스 커피
  image_url: <https://image.istarbucks.co.kr/upload/store/skuimg/2021/04/[106509]_20210430111852999.jpg>
description: 깔끔하고 상큼함이 특징인 시원한 아이스 커피
     is_new: 0
category_id: 2

특정 테이블 내의 특정 데이터만 조회

특정 데이터를 조회하기 위해 WHERE을 사용.

다양한 연산자를 사용하여 조건을 다양하게 표현할 수 있다.

WHERE절의 자세한 내용은 아래 링크 참고.

https://www.w3schools.com/sql/sql_where.asp

SELECT * FROM [table명] WHERE [column명][연산자][값];

• sample

SELECT * FROM users WHERE email='hello@gmail.com';
+----+-------------------------+-----------------+-------+----------+----------+
| id | created_at              | email           | name  | password | favorite |
+----+-------------------------+-----------------+-------+----------+----------+
|  2 | 2021-06-24 23:05:39.871 | hello@gmail.com | hello | gmail123 | NULL     |
+----+-------------------------+-----------------+-------+----------+----------+

지정 column의 전체 데이터 조회

SELECT [column명] FROM [table명];

• sample

SELECT kcal, caffeine FROM nutrition;
+------+----------+
| kcal | caffeine |
+------+----------+
|   75 |      245 |
|    5 |      140 |
|   10 |      150 |
|  600 |        0 |
|  190 |        0 |
|  105 |      110 |
|  205 |       60 |
|  500 |       15 |
+------+----------+

데이터 추가

INSERT INTO [table명] ([column명]) VALUES ([값]);

• sample

INSERT INTO categories (name) VALUES ('브루드 커피');
+----+----------------------+
| id | name                 |
+----+----------------------+
|  1 | 콜드 브루 커피          |
|  2 | 브루드 커피            |
+----+----------------------+

INSERT INTO drinks (name, image_url, description, is_new, category_id) VALUES ('나이트로 바닐라 크림', '<https://image.istarbucks.co.kr/upload/store/skuimg/2021/04/[9200000002487]_20210426091745609.jpg>', '부드러운 목넘김의 나이트로 커피와 바닐라 크림의 매력을 한번에 느껴보세요!', 1, 1);
*************************** 1. row ***************************
         id: 1
       name: 나이트로 바닐라 크림
  image_url: <https://image.istarbucks.co.kr/upload/store/skuimg/2021/04/[9200000002487]_20210426091745609.jpg>
description: 부드러운 목넘김의 나이트로 커피와 바닐라 크림의 매력을 한번에 느껴보세요!
     is_new: 1
category_id: 1

데이터 값 수정

UPDATE [table명] SET [column명]=[수정할 값] WHERE [조건식];

• sample

UPDATE drinks SET name="제주 까망 크림 프라푸치노" WHERE id = 4;

데이터 삭제

DELETE FROM [table명] WHERE [조건식];

• sample

DELETE FROM drink_allergy WHERE id = 11;

Foreign Key 추가

ALTER TABLE [table명] ADD FOREIGN KEY ([FK로 설정할 column명]) REFERENCES [참조할 table명(참조할 PK명)];

※ FK로 설정할 column에 값이 있어야 추가 가능.

• sample

ALTER TABLE nutrition ADD FOREIGN KEY (drink_id) REFERENCES drinks(id);

---

 

ORDER BY

• 결과값의 정렬
• ASC : 오름차순 정렬, default 값.
• DESC : 내림차순 정렬

# 성씨 별 몇 명이 있는지 집계 + 내림차순 정렬(desc)
select name, count(*) from users
group by name
order by count(*) desc

# 이메일을 오름차순으로 문자열 정렬
select * from users
order by email

GROUP BY

• 기준 속성으로 묶는 것

# 주차 별 오늘의 다짐 구하기
select week, count(*) from checkins 
group by week

# 오늘의 다짐 like 평균값 구하기 + 반올림
select week, round(avg(likes)) from checkins
group by week

HAVING

• 그룹별 집계된 결과 중 원하는 조건의 결과만 필터링
• group by 에서의 where과 같은 역할을 함

# 직군(job) 별 직원 수가 3명 이상인 직군과 직원수 구하기 
SELECT job, COUNT(*) cnt
  FROM emp
 WHERE deptno IN ('10', '20', '30')
 GROUP BY job
HAVING COUNT(*) > 2