MySQL 총정리

SELECT

SELECT * FROM mydb.users; // 정확하게
SELECT * FROM users; // 생략 가능

// WHERE
SELECT * FROM post
WHERE view > 300 and writer = "kong";

// 중복제거 (post에서 writer를 모두 선택하는데 중복이 있으면 제거한다
SELECT DISTINCT writer FROM post;

// ORDER BY (정렬)
SELECT * FROM post ORDER BY view; // default ASC : 오름차순
SELECT * FROM post ORDER BY view DESC; //DESC: 내림차순

INSERT

INSERT INTO users(id,name,age) values(default,'kong', 24);
INSERT INTO users values(default,'kong', 24); //column 생략가능

UPDATE

UPDATE users SET name='ha' WHERE id=2; //특정 row 수정
UPDATE users SET name='Pul'; // 모두 수정

DELETE

DELETE FROM users WHERE name='Pul'; //특정 row 지우기
DELETE FROM post; //테이블 데이터 다지우기

---

Create DB

CREATE DATABASE Kong;
use Kong;

Create Table

CREATE TABLE post
(
    id INT,
    title VARCHAR(50),
        writer VARCHAR(50),
    createDate DATE,
    view INT
);

constraint (제약조건) 이란?

• 데이터의 무결성을 지키기 위해 데이터를 입력받을 때 실행되는 검사규칙을 의미한다.
• CREATE로 테이블을 생성할때나 ALTER 구문을 이용해 필드를 추가할때도 설정 할 수 있다.

1. NOT NULL : 해당 필드는 NULL 값을 저장할 수 없게 됩니다.
2. UNIQUE : 해당 필드는 서로 다른 값을 가져야만 합니다.
3. PRIMARY KEY : 해당 필드가 NOT NULL과 UNIQUE 제약 조건의 특징을 모두 가지게 됩니다.
4. FOREIGN KEY : 하나의 테이블을 다른 테이블에 의존하게 만듭니다.
5. DEFAULT : 해당 필드의 기본값을 설정합니다.

🥴 Tip DATE 자료형은 YYYY-MM-DD DATETIME 자료형은 YYYY-MM-DD:HH:MM:SS

• NOW() 사용시 DATETIME이 생성됨

ALTER DATABASE

• CHARACTER(문자집합)
• COLLATE(컬렉션) : 콜레이션(collation)이란 데이터베이스에서 검색이나 정렬과 같은 작업을 할 때 사용하는 비교를 위한 규칙의 집합을 의미합니다

위 두가지를 보통 수정함

자주 사용되는 대표적인 CHARACTER SET은 다음과 같습니다
1. utf8 : UTF-8 유니코드를 지원하는 문자셋 (1~3바이트)
2. euckr : 한글을 지원하는 문자셋 (1~2바이트)

자주 사용되는 대표적인 COLLATE는 다음과 같습니다
1. utf8_bin
2. utf8_general_ci (기본 설정)
3. euckr_bin
4. euckr_korean_ci

• COLLATE에서 ci는 case-insensitive를 의미하며, 대소문자를 구분하지 않게 설정됩니다

예시)

ALTER DATABASE Kong CHARACTER SET=utf8 COLLATE=utf8_bin;

ALTER TABLE

• ADD (필드추가)

ALTER TABLE post
ADD phone INT;

• DROP (필드삭제)

ALTER TABLE post
DROP phone;

• MODIFY COLUMN

ALTER TABLE post
MODIFY COLUMN phone VARCHAR(30); //INT 에서 VARCHAR 로 변경

🥴 Tip INT형의 필드는 0 이맨앞에 올경우 제거한다. 예를들어서 전화번호를 01012345678 으로 저장하면 12345678 로 만앞의 0을 제거하고 저장함

DROP

• 데이터베이스와, 테이블을 삭제한다.

DROP DATABASE ha; // DB삭제
DROP TABLE memo; // TABLE 삭제

//IF EXISTS 를 사용하면 존재하지 않아서 발생하는 에러를 미리 방지할 수 있다

DROP DATABASE IF EXISTS ha;
DROP TABLE IF EXISTS memo;

TRUNCATE

• 테이블은 그대로 두고 데이터만 다 삭제한다

TRUNCATE TABLE post;

---

타입

숫자 타입

고정 소수점 타입

DECIMAL(M,D) : M는 소수부분을 포함한 총자릿수, D는 소수 부분 자릿수
DECIMAL(4,2) : 10.00, 10.23 이런식으로 표현 (DECIMAL = NUMERIC)

부동 소수점 타입

FLOAT(P)
FLOAT(M,D)
DOUBLE(M,D)

문자열 타입

CHAR(M) : 설정한 크기보다 작은 길이의 문자열이 입력되면, 나머지 공간을 공백으로 채워 길이를 M과 같게 만듭니다
VARCHAR(M) : 실제 입력된 문자열의 길이만큼만 저장하고 사용합니다

BLOB : Binary Large Object를 의미하며, 다양한 크기의 바이너리 데이터를 저장할 수 있는 타입

TEXT
TEXT는 VARCHAR와 비슷하지만, VARCHAR와는 달리 기본값을 가질 수 없습니다
TEXT는 BLOB과도 비슷하지만, BLOB과는 달리 문자열의 대소문자를 구분

날짜와 시간 타입

**DATE** : 'YYYY-MM-DD'
**DATETIME** : 'YYYY-MM-DD HH:MM:SS'

**TIMESTAMP** : 날짜와 시간을 나타내는 타임스탬프를 저장할 수 있는 타입
TIMESTAMP 타입의 필드는 사용자가 별다른 입력을 주지 않으면,
데이터가 마지막으로 입력되거나 변경된 시간이 저장됩니다

** 입력받은 데이터가 유효한 날짜와 시간이 아니면, 세 타입 모두 0을 저장합니다.
** 범위를 초과한 시간은 '-838:59:59'이나 '838:59:59'로 자동 변환되어 저장

**YEAR** : YEAR(2)는 2자리의 연도를 저장할 수 있으며, YEAR(4)는 4자리의 연도를 저장
1자리 또는 2자리 숫자로 저장하면, 1부터 69까지는 2001년부터 2069년까지가 되고, 70부터 99까지는 1970년부터 1999년까지가 됩니다

연산자와 함수

산술연산자

+, -, *, /
DIV : 나눈 후 소수 부분을 버림
% or MOD : 나눈 후 그 나머지를 반환

대입연산자

• MYSQL에서는 = 는 두가지의미이다. ( 대입, 비교)
• := 는 무조건 대입연산자로 쓰인다.
• SET 문이나 UPDATE 문의 SET 절에서 사용되면, 왼쪽 피연산자에 오른쪽 피연산자를 대입하는 대입 연산자로 해석
• SET 문이나 UPDATE 문의 SET 절 이외에서 사용되면, 왼쪽 피연산자와 오른쪽 피연산자를 비교하는 비교 연산자로 해석

=, :=
첫 번째 대입 연산자(=)와 달리 두 번째 대입 연산자(:=)는 절대로 비교 연산자로 해석되지 않습니다

비교연산자

• 참(true)이면 1을 반환하고 거짓(false)이면 0 을 반환한다.

IS : 왼쪽 피연산자와 오른쪽 피연산자가 같으면 참을 반환함.(오른쪽 피연산자가 불리언 값인 TRUE, FALSE, UNKNOWN 값일 때 사용함)
IS NOT

IS NULL : 피연산자의 값이 NULL이면 참을 반환함.
IS NOT NULL

IN() : 피연산자의 값이 인수로 전달받은 리스트에 존재하면 참을 반환함.
NOT IN()
ex) 5 IN (2, 3, 4, 5); // 5가 2, 3, 4, 5중에 포함되는지를 비교함.

논리연산자

• AND = &&
• OR = ||
• XOR
• NOT = !

패턴매칭

LIKE

LIKE
NOT LIKE
% , _

ex)
SELECT * FROM Reservation
WHERE Name NOT LIKE '장%';

REGEXP

• 정규식을 토대로 하는 패턴 매칭

다중 테이블 연산

JOIN

• INNER JOIN(= JOIN, INNER JOIN, CROSS JOIN) : MySQL에서 는 같은 의미

SELECT * 
FROM post
JOIN user ON post.writer = user.name and writer='kong';

// 아래의 WHERE 절의 결과는 위 JOIN과 같음

SELECT *
FROM post, user
WHERE post.writer = user.name
AND post.writer = 'kong';

• LEFT JOIN : 좌측 테이블을 기준으로 가져옴, ON 조건에 만족하지 않으면 null로 채워서 가져옴

SELECT * FROM post
LEFT JOIN user ON post.writer = user.name and writer='kong';

• RIGHT JOIN : LEFT JOIN 과 반대

SELECT * FROM post
RIGHT JOIN user ON post.writer = user.name and writer='kong';

UNION

• UNION은 중복을 제거함
• UNION ALL 은 중복되는 레코드도 모드 출력함

SELECT title as writer
FROM post
UNION
SELECT name as writer
FROM user;

SELECT title as writer
FROM post
UNION ALL
SELECT name as writer
FROM user;

Sub Query

• 서브쿼리(subquery)란 다른 쿼리 내부에 포함되어 있는 SELECT 문을 의미합니다.

SELECT * FROM post
WHERE writer IN (SELECT name FROM user WHERE name LIKE'%k%');

INDEX

• 인덱스는 자주 사용되는 필드 값으로 만들어진 원본 테이블의 사본이라고 생각할 수 있습니다
• MySQL은 데이터를 검색할 때 첫 번째 필드부터 차례대로 테이블 전체를 검색합니다. 따라서 테이블이 크면 클수록 데이터를 탐색하는 시간도 많이 늘어나게 됩니다
• 하지만 인덱스를 사용하면 테이블 전체를 읽지 않아도 되므로, 검색과 질의에 대한 처리가 빠르게 이루어집니다
• 인덱스가 설정된 필드 값을 포함한 데이터의 삽입, 삭제, 수정 작업이 원본 테이블에서 이루어질 경우, 인덱스도 함께 수정되어야 합니다. 따라서 인덱스가 설정된 테이블의 처리 속도가 느려질 수 있습니다
• 그러므로 인덱스는 수정보다는 검색이 자주 사용되는 테이블에서 사용하는 것이 더 좋습니다

// 인덱스 생성하기(특정필드)
CREATE INDEX ViewIdx
ON post(view);

// 인덱스 정보 보기
SHOW INDEX
FROM post;

// 인덱스 추가 (그냥 CREATE해도 추가되던데..)
ALTER TABLE 테이블이름
ADD INDEX 인덱스이름 (필드이름)

// 인덱스 제거
1. 
ALTER TABLE post
DROP INDEX ViewIdx;

2.
DROP INDEX TitleIdx
ON post;

MySQL 내장 함수

문자열함수

SELECT LENGTH('1234');
SELECT CONCAT('oracle ','Cor');
SELECT LOCATE('abc', 'ababcDEFabc'); //SQL에서는 index가 0이 아니라 1부터 시작함을 주의

SELECT LEFT('hello my name is kong',5); // hello
SELECT RIGHT('hello my name is kong',4); // kong

SELECT UPPER('hello my name is kong');
SELECT LOWER('hello my name is kong');

SELECT REPLACE('hello my kong name is kong','kong','ha'); //모두 바꿈

SELECT TRIM('  hello my kong    name is kong     ');
SELECT TRIM(LEADING 'h' FROM 'hello my kong    name is kong     '); //뒷 문자열 앞에 오는 특정 문자를 제거함

SELECT FORMAT(1000000,3); //세 자리마다 쉼표(,)를 사용하는 '#,###,###.##' 형식으로 변환
단, 데이터형식이 숫자가 아닌 문자열 타입으로 변형됨

수학함수

SELECT FLOOR(10.99);
SELECT CEIL(10.1);
SELECT ROUND(10.5);
SELECT SQRT(4); //제곱근 양수 반환
SELECT POW(2,2); //거듭제곱
SELECT EXP(3); //e의 거듭제곱
SELECT LOG(3); //자연로그 값

SELECT SIN(PI());
SELECT COS(PI());
SELECT TAN(PI());

SELECT ABS(-10);
SELECT RAND()*100; 0~100

날짜와 시간 함수

SELECT NOW(); //'YYYY-MM-DD HH:MM:SS'
SELECT CURDATE();
SELECT CURTIME();

SELECT DATE(CURTIME()); //날짜반환
SELECT MONTH(CURTIME()); //월 반환
SELECT DAY(CURTIME()); //일 반환

SELECT HOUR(CURTIME());
SELECT MINUTE(CURTIME());
SELECT SECOND(CURTIME());

SELECT MONTHNAME(CURTIME());
SELECT DAYNAME(CURTIME());

SELECT DAYOFWEEK(CURTIME()); //일주일중 몇번째 날인지 일=1, 토=7
SELECT DAYOFMONTH(CURTIME()); //그달에서 몇번째 일인지 0~31
SELECT DAYOFYEAR(CURTIME()); //그 해에 몇번째 일인지 1~366

그룹함수

SELECT COUNT(*) FROM post;
SELECT MIN(view) FROM post;
SELECT MAX(view) FROM post;
SELECT SUM(view) FROM post;
SELECT AVG(view) FROM post;

//GROUTP BY ,HAVING

SELECT COUNT(*),writer FROM post GROUP BY writer;
SELECT COUNT(*),writer FROM post GROUP BY writer HAVING COUNT(*) >=3;