MySQL 총 정리 🧐

해당 게시글은 유튜브 이수안컴퓨터연구소의 MySQL 데이터베이스 한번에 끝내기를 보고 정리한 내용입니다.
혹시라도 잘못된 내용이 있다면 댓글로 남겨주세요 🥰

◾ 목차
- MySQL이란
- SQL의 분류
- MySQL 기초문법
- MySQL 내장함수

  MySQL이란  

- MySQL은 가장 널리 사용되고 있는 데이터베이스 관리 시스템 (RDBMS: Relational DBMS)

- 오픈소스이며, 다중 사용자와 다중 스레드를 지원

- 여러 프로그래밍 언어를 위한 다양한 API 제공

- 다양한 운영체제 지원 (유닉스, 리눅스, 윈도우 등) 

- 상업적으로 사용할 때는 상업용 라이센스 구입 필요 

 

  SQL의 분류  

1. DML - DATA MANIPULATION LANGUAGE

- 데이터 조작 언어

- 데이터를 조작하는 데 사용되는 언어 : 선택, 삽입, 수정, 삭제

- DML 구문이 사용되는 대상은 테이블의 행

- DML 사용하기 위해서는 꼭 그 이전에 테이블이 정의되어 있어야 함.

- SELECT, INSERT, UPDATE, DELETE가 해당

- 트랜잭션이 발생하는 SQL도 DML에 속함 

🔉 트랜잭션이란 ? 테이블의 데이터를 변경할 때 실제 테이블에 완전히 적용 X, 임시로 적용시키는 것 → 취소가능

2. DDL - DATA DEFINITION LANGUAGE

- 데이터 정의 언어

- 데이터베이스, 테이블, 뷰, 인덱스 등의 데이터베이스 개체를 생성/삭제/변경하는 역할

- CREATE, DROP, ALTER가 해당

- DDL은 트랜잭션 X

- ROLLBACK이나 COMMIT 사용 불가

- DDL문은 실행 즉시 MySQL에 적용

3. DCL - DATA CONTROL LANGUAGE

- 데이터 제어 언어

- 사용자에게 어떤 권한을 부여하거나 빼앗을 때 주로 사용하는 구문

 

  MySQL 기초 문법  

- 해당 문법을 실행할 때는 상단의 번개버튼 혹은 CTRL + ENTER를 누르면 된다.

- 하늘색 글씨는 실행 문법이 아닌 본인이 작성해야 하는 부분.

- 여러 실행문을 쓸 때는 마지막에 세미콜론(;)을 적어줘야 각각의 실행문으로 분리가 된다.

SHOW DATABASES

- 현재 서버에 어떤 DB가 있는지 확인

USE databaseName

- 사용할 데이터베이스 지정

- 지정해 놓은 후 특별히 다시 USE문 사용하거나 다른 DB를 사용하겠다고 명시하지 않는 이상 모든 SQL문은 지정 DB에서 수행

- workbench : Navigator - SCHEMAS - 데이터베이스 선택

SHOW TABLES

- 데이터베이스 내의 테이블 이름 보기

SHOW TABLE STATUS

- 데이터베이스 테이블 정보 조회

DESCRIBE tableName   DESC tableName

- 데이터베이스 테이블 열 조회

SELECT fieldName  FROM tableName 

- 요구하는 데이터를 가져오는 실행문 

SELECT * FROM tableName

- 해당 테이블의 전체 정보 조회

SELECT fieldName FROM tableName

- 해당 테이블의 특정 열 정보 조회

- 예시 :

SELECT Name FROM city / SELECT Name, Population FROM city

🔉 열은 1개 이상도 선택할 수 있다.

SELECT fieldName FROM tableName WHERE condition

- 요구하는 데이터 + 조건문

- WHERE문은 주로 관계 연산자와 함께 사용한다 (관련링크)

- 예시 :

SELECT * FROM city WHERE Population > 800;

SELECT * FROM city WHERE Population > 800 AND Population < 1000

( = SELECT * FROM city WHERE Population BETWEEN 800 AND 1000)

SELECT * FROM city WHERE CountryCode = 'KOR'

IN

- 특정 값을 얻기 위해 사용

- 예시 :

SELECT * FROM city WHERE Name IN ('Seoul', 'New York');

- city 테이블에서 서울, 뉴욕이 이름인 데이터만 보고 싶을 때

LIKE

- 문자열의 내용 검색을 위해 사용

- 문자뒤에 % - 무엇이든 % 허용

- 한 글자와 매치하기 위해서는 _ 사용

- 예시 : 

1. 한글자가 생각이 나지 않을 때 : LIKE + (_) 사용

SELECT * FROM city WHERE CountryCode LIKE 'K_R';

SELECT * FROM city WHERE CountryCode LIKE 'KO_';

 

2. 여러 글자가 생각이 나지 않을 때 : LIKE + (%) 사용

SELECT Name FROM country WHERE Name LIKE '%Ko%';

* 위치에 따라 %를 붙여줄 수 있음 (앞, 뒤 각각 혹은 모두 사용 가능)

* 해당 문법을 실행하면 받을 수 있는 값 : Hong Kong, South Korea, North Korea

* 이처럼 앞, 뒷글자의 글자 수는 상관 없이 알아서 검색해 준다.

Sub Query (서브쿼리)

- 쿼리문 안에 또 쿼리문이 들어 있는 것.

- 서브쿼리의 결과가 둘 이상이 되면 에러가 뜸!

서브 쿼리의 값이 1개이기 때문에 해당 쿼리는 실행이 된다 🧐

ANY | SOME

- 서브쿼리의 여러개의 결과 중 한 가지만 만족해도 가능

- SOME도 ANY와 같은 의미로 사용 가능.

- 예시 :

SELECT *
FROM city
WHERE Population  > ANY (  SELECT Population FROM city WHERE District = 'New York');

* 서브쿼리 : District이 NewYork인 도시의 인구를 출력

* 편의 상 Name도 함께 출력했습니다 !

* 모든 도시의 인구 중 해당 인구 수보다 높은 도시를 출력하면 된다. (이들 중 하나라도 만족하면 출력)

ALL

- 서브쿼리의 여러개의 결과 중 모두 만족하는 결과 출력

- 예시 :

SELECT *
FROM city
WHERE Population  > ALL (  SELECT Population FROM city WHERE District = 'New York');

* 결론적으로 ANY는 가장 작은 수 보다 높으면 출력, ALL은 가장 높은 수 보다 높으면 출력하게 된다.

ORDER BY

- 기본 : 오름차순 정렬 (ASC - 기본 값이어서 생략 가능)

- 결과가 출력되는 순서를 조절하는 구문

- 내림차순인 경우 뒤에 DESC를 적어줌.

- 예시 :

SELECT *
FROM city
ORDER BY Population DESC;

SELECT *
FROM city
ORDER BY CountryCode ASC, Population DESC;

* 구문 혼합 가능

SELECT *
FROM city

WHERE CountryCode = 'KOR'

ORDER BY CountryCode ASC, Population DESC;

* 조건문 함께 사용 가능

DISTINCT

- 중복 제거

- 테이블 크기가 클수록 효율적

- 예시 :

SELECT DISTINCT CountryCode FROM City;

도시에는 겹치는 CountryCode가 많으니까 그 중 중복은 제거됨 

예를 들어 우리나라의 모든 도시의 CountryCode는 KOR인데, 한번만 출력되는 것!

LIMIT

- 출력 개수를 제한

- 상위 N개만 출력하는 LIMIT N

- 서버의 처리량을 많이 사용해 서버의 전반적인 성능을 나쁘게 하는 악성 쿼리문을 개선할 때 사용

- 예시 :

SELECT Rank, MusicTitle FROM Balad ORDER BY Rank LIMIT 10

* 발라드 상위 랭크 10위의 랭크와 제목을 보여준다.

GROUP BY

- 그룹으로 묶어주는 역할 

SELECT CountryCode MAX(Population) AS 'Population' 
FROM city
GROUP BY CountryCode

예시 : 도시들을 국가코드로 묶어주고 그 중 최대 인구 값을 함께 리턴

-  AS  키워드를 사용하면 컬럼에 별칭을 지어줄 수 있다.

Aggregate Function (집계함수)

- AVG() : 평균

- MIN() : 최솟값

- MAX() : 최댓값

- COUNT() : 행의 개수

- COUNT(DISTINCT) : 중복 제외된 행의 개수

- STDEV() : 표준 편차

- VARIANCE() : 분산

SELECT COUNT(*) FROM city

- 전체 도시 구하기.

HAVING

- WHERE문과 비슷한 개념으로 조건을 제한해줌 

- 집계함수에 대해서 조건을 제한하는 편리한 개념으로 HAVING절은 GROUP BY 절 다음에 나와야 함.

SELECT CountryCode, MAX(Population)
FROM city
GROUP BY CountryCode
HAVING MAX(Population) > 80000

ROLLUP

- 총합 또는 중간합계가 필요한 경우 사용

- GROUP BY절과 함께 WITH ROLLUP 사용

SELECT CountryCode, Name, SUM(Population)
FROM city
GROUP BY CountryCode, Name WITH ROLLUP

- Name이 NULL로 되어있는 Population은 같은 CountryCode를 가진 도시의 총 인구수를 나타낸다. (중간합계)

JOIN

- 데이터베이스 내 여러 테이블의 레코드를 조합하여 하나의 테이블 혹은 결과 집합으로 표현

SELECT *
FROM city
JOIN country ON city.CountryCode = country.Code

SELECT *
FROM city
JOIN country ON city.CountryCode = country.Code
JOIN countrylanguage ON city.CountryCode = countrylanguage.CountryCode

* 한 개 이상의 테이블도 조인할 수 있음.

  MySQL 내장함수  

LENGTH()

- 전달받은 문자열의 길이를 반환

SELECT LENGTH('APPLE'); // 5

CONCAT()

- 전달받은 문자열을 결합하여 하나의 문자열로 반환

- 만약에 문자열 중 하나라도 NULL인 경우 NULL반환

SELECT CONCAT('HELLO','WORLD'); // HELLOWORLD
SELECT CONCAT('HELLO',NULL); // NULL

LOCATE()

- 찾는 문자열의 첫번 째 위치를 반환 

- 만약에 찾는 문자열이 없는 경우 0반환

- MySql은 시작 인덱스가 1부터임

SELECT LOCATE('HELLO', 'WORLDHELLOWORLD'); // 6

LEFT(), RIGHT()

- 지정한 개수만큼의 문자를 반환 (왼쪽, 오른쪽 기준으로 나뉨)

SELECT LEFT('HELLO WORLD',5), RIGHT('HELLO WORLD',5);

LOWER(), UPPER()

- 문자열이 소문자 / 대문자로 변경됨

SELECT
LOWER('HELLO WORLD'), // 'hello world'
UPPER('hello world') // 'HELLO WORLD'

REPLACE()

- 문자열에서 특정 문자열을 대체 문자열로 변경

SELECT REPLACE('HELLO BLOG','BLOG','WORLD');

예시 : BLOG 👉 WORLD

TRIM()

- 문자열의 앞, 뒤, 양쪽 모두에 있는 특정 문자를 제거

- 함께 사용하는 키워드

* BOTH : 전달받은 문자열의 양 끝에 존재하는 특정 문자를 제거 (DEFAULT 값)

* LEADING : 전달받은 문자열 앞에 존재하는 특정 문자를 제거

* TRAILING : 전달받은 문자열 뒤에 존재하는 특정 문자를 제거

- 제거할 문자를 명시하지 않으면, 공백을 제거함.

SELECT TRIM('       MYSQL     '),
TRIM(LEADING '+' FROM '+++HELLO+WORLD+++'),
TRIM(TRAILING '+' FROM '+++HELLO+WORLD+++');

FORMAT()

- 숫자 타입의 데이터를 세자리 형식마다 쉽표(,)를 사용하는 형식으로 변환

- 반환되는 데이터의 형식은 문자열 타입

- 두번째 인수로는 반올림할 소수 부분의 자릿수

SELECT FORMAT(122434534.36366,3); // '122,434,534.364'

예시 : 공통) 세자리 형식마다 쉼표 , 두번째 인수에 3이 들어갔기 때문에 소수 자릿수는 3자리에서 반올림!

FLOOR(), CEIL(), ROUND()

- FLOOR() : 내림 

- CEIL() : 올림

- ROUND() : 반올림

SELECT FLOOR(10.8), CEIL(10.2), ROUND(10.5); // 차례로 10 , 11 , 11

SQRT()

- 제곱근

SELECT SQRT(4); // 2

RAND()

- 0 ~ 1 사이의 실수를 무작위로 추출 

- 주로 ROUND()와 같이 사용 

SELECT ROUND(RAND() * 100, 0)

 

- 0 ~ 100 사이의 정수가 나옴. 반올림 후 소숫점 0이기 때문에

 

  🌟 MySQL 날짜 & 시간함수   

NOW()

- 현재 시간

CURDATE()

- 현재 날짜

CURTIME()

- 현재 시간

SELECT NOW() // 2022-05-16 14:10:44
SELECT CURDATE() // 2022-05-16
SELECT CURTIME() // 14:10:44

DATE (),MONTH(), DAY()

HOUR(), MINUTE(), SECOND()

- 날짜와 관련되며 주로 NOW()와 함께 씀

SELECT DATE(NOW())    // 2022-05-16	
SELECT MONTH(NOW())   // 5
SELECT DAY(NOW())     // 16
SELECT HOUR(NOW())    // 14
SELECT MINUTE(NOW())  // 17
SELECT SECOND(NOW())  // 33

MONTHNAME(), DAYNAME()

- 영어로 월, 요일을 알려줌

SELECT MONTHNAME(NOW()) // May
SELECT DAYNAME(NOW())   // Monday

DAYOFWEEK(), DAYOFMONTH(), DAYOFYEAR()

- DAYOFWEEK : 1 ~ 7 사이를 반환. 여기서 1은 일요일을 뜻함

- DAYOFMONTH : 0 ~ 31 사이를 반환

- DAYOFYEAR: 1 ~ 366 사이 반환

SELECT DAYOFWEEK(NOW())    //  2 = 월요일
SELECT DAYOFMONTH(NOW())   //  16 = 월 기준 16주
SELECT DAYOFYEAR(NOW())    //  136 = 1년 기준 136일

DATE_FORMAT()

- 날짜 정보 포맷 ( 표기 방식을 바꾸고 싶을 때 사용 )

SELECT DATE_FORMAT(NOW(),'%d%y%a%m%n%j') // 1622Mon05n136

* 날짜 시간의 형식화는 이곳에서 자세하게 볼 수 있다!

   MySQL 고급함수   

CREATE TABLE cityclone AS SELECT * FROM city

- 기존 테이블 데이터 복제 후 새로운 테이블 생성 (city의 데이터를 복제 후 cityclone이라는 새로운 테이블 생성)

CREATE DATABASE population

- 새로운 데이터베이스 생성

CREATE TABLE

- 두가지 방법이 있다.

1. workbench에서 클릭 방식으로 생성

1.1 스키마에서 데이터베이스 오른쪽 클릭

1.2 create tables 클릭

1.3 데이터타입 확인

 

2. 명령문으로 생성

 CREATE TABLE population 
 (
    id INT NOT NULL PRIMARY KEY // 컬럼 - 타입 - 옵션 순
 )

ALTER TABLE

// * 기본 포맷
ALTER TABLE 테이블이름
ADD 칼럼이름 타입

// 1) 칼럼 추가 : ALTER TABLE ~ ADD
ALTER TABLE Students
ADD Phone INT;

// 2) 칼럼 타입 변경 : ALTER TABLE ~ MODIFY
ALTER TABLE Students
MODIFY Phone VARCHAR(11);

// 3) 칼럼 제거 : ALTER TABLE ~ DROP
ALTER TABLE Students
DROP Phone

   INDEX   

 

 

- 인덱스(INDEX)는 주로 테이블에서 원하는 데이터를 검색할 때 사용한다.

- 주로 자주 사용하용 필드 값으로 만들어진 원본 테이블의 사본

- 왜일까? MYSQL은 데이터를 검색할 때 첫 번째 필드부터 차례대로 테이블 전체를 검색하기 때문!

- 장점 : 테이블을 전체 읽지 않아도 되기 때문에 검색 & 질의에 대한 처리가 빠르게 이루어짐.

- 단점 : 만약에 인덱스가 설정된 필드 값의 데이터에서 삽입, 삭제 수정 작업이 이루어질 경우, 인덱스도 함께 수정 되어야 함. ( 👉 두 작업이 함께 이루어지기 때문에 테이블의 처리속도가 느려질 수 있음 )

- 즉, 인덱스는 수정보다는 검색이 자주 사용되는 테이블에서 사용되는 것이 좋음!

// 1. INDEX 생성
// CREATE INDEX 인덱스이름On 테이블이름 (필드이름,필드이름2...);
CREATE INDEX NameIdx On Reservation (Name);

// 2. UNIQUE INDEX 생성
// CREATE UNIQUE INDEX 인덱스이름 On 테이블이름 (필드이름,필드이름2...);
CREATE UNIQUE INDEX IdIdx On Reservation (ID);

// 3. INDEX 정보 보기
SHOW INDEX FROM 테이블이름

// 4. INDEX 정렬
CREATE INDEX 인덱스이름 ON 테이블이름 (필드이름 DESC)
CREATE INDEX 인덱스이름 ON 테이블이름 (필드이름 ASC)

// 5. INDEX 삭제
ALTER TABLE 테이블이름 DROP INDEX 인덱스이름
DROP INDEX 인덱스이름 ON 테이블이름

   VIEW   

- 데이터베이스에 존재하는 일종의 가상테이블이다.

- 실제 테이블처럼 행과 열을 가지고 있지만, 실제로 데이터를 저장하고 있지는 않음.

- MySQL에서 뷰(view)는 다른 테이블이나 다른 뷰에 저장되어 있는 데이터를 보여주는 역할만을 수행함.

- 즉, 뷰를 사용하면 여러 테이블이나 뷰를 하나의 테이블처럼 볼 수 있음

👉 여러 테이블을 한번에 볼 때 사용

// 1. VIEW 생성
// CREATE VIEW 뷰이름 AS SELECT 필드이름1, 필드이름2, ... FROM 테이블이름 WHERE 조건
// Reservation 테이블에서 Name, ReserveDate, Dday 필드를 가지는 MyView라는 이름의 뷰를 생성하는 예제
CREATE VIEW MyView AS
SELECT Name, ReserveDate, ReserveDate - Curdate() AS Dday
FROM Reservation;

// 2. VIEW 수정
// ALTER VIEW 뷰이름 AS SELECT 필드이름1, 필드이름2, ... FROM 테이블이름
// 생성된 MyView라는 이름의 뷰를 ID와 Name 필드만 가지도록 수정하는 예제
ALTER VIEW MyView AS
SELECT ID, Name
FROM Reservation;

// 3. VIEW 삭제
// DROP VIEW 뷰이름
DROP VIEW MyView;

   INSERT INTO  

1. 새로운 레코드 추가

INSERT INTO 테이블이름(필드이름1, 필드이름2, 필드이름3, ...)
VALUES (데이터값1, 데이터값2, 데이터값3, ...)

INSERT INTO 테이블이름
VALUES (데이터값1, 데이터값2, 데이터값3, ...)

INSERT INTO Reservation(ID, Name, ReserveDate, RoomNum)
VALUES(5, '이순신', '2016-02-16', 1108);

- 필드 이름은 생략할 수 있으며, 없는 경우 스키마와 같은 순서대로 자동 대입됨.

- 단, 생략할 수 있는 필드가 있음

1. NULL을 저장할 수 있도록 설정된 필드

2. DEFAULT 제약 조건이 설정된 필드

3. AUTO_INCREMENT 키워드가 설정된 필드

- 직접 테이블에 입력후 하단 우측 Apply 버튼으로도 적용 가능.

 

2. 데이터 복사

INSERT INTO totalscore SELECT FROM mathscore

   UPDATE   

- 레코드의 내용을 수정

- 만약에 WHERE 절을 생략하게 된다면 모든 필드 값이 변경되기 때문에 주의가 필요!

UPDATE 테이블이름
SET 필드이름1=데이터값1, 필드이름2=데이터값2, ...
WHERE 필드이름=데이터값;

UPDATE Reservation
SET RoomNum = 2002
WHERE Name = '홍길동';

   DELETE   

- 레코드 삭제

// 1. 특정 필드의 값인 레코드를 삭제
DELETE FROM 테이블이름
WHERE 필드이름=데이터값;

// 2. 테이블의 모든 데이터 삭제
DELETE FROM 테이블이름; // 테이블은 남아있음. 테이블의 '데이터'만 삭제 됨.
DROP TABLE; // 테이블은 DROP문으로 삭제해야 삭제됨.

   DROP   

- 테이블 , 데이터베이스 삭제 시 사용

// 1. DATABASE 삭제
DROP DATABASE 데이터베이스이름
DROP DATABASE Hotel;

// 2. TABLE 삭제
DROP TABLE 테이블이름
DROP TABLE Reservation

// 3. TABLE 내 '데이터'만 삭제
TRUNCATE TABLE 테이블이름
TRUNCATE TABLE Reservation

- IF EXISTS 문을 사용하면 테이블, 혹은 데이터베이스가 없어서 생기는 에러를 방지할 수 있음

DROP DATABASE IF EXISTS Hotel;
DROP TABLE IF EXISTS Reservation;

 

개인적으로 TCP School에 정리가 잘 되어있으니 꼭 참고하시면 좋을 거 같다 🥰