• []생략가능
• 대문자 => 명령어
• <> => 개인적으로 입력할거
• '' => 사용한곳은 ''생략하지말고 그냥 ''로 감싸서 적기

0. 팁

mysql에서는 DB에 대한 권한을 유저에게 부여하는 방식이라서
DB먼저 생성 후 유저 생성하고 권한부여하는 순서로 실행해야 한다.

0.1 time_zone

0.1.1 time_zone 검색

SHOW VARIABLES LIKE '%time_zone%';

0.1.2 time_zone 수정

SET time_zone = 'system';`... `'Asia/Seoul'

0.2 세션 아이디 보기

기본적으로 DB에 접속하면 DB에서 세션을 하나 생성함, 즉 연결 하나당 세션이 하나씩 생긴다.

// 세션 아이디 리스트 출력하기
SHOW processlist;

1. SQL

1.1 DDL ( Data Definition Language )

데이터 정의 언어
데이터베이스 or 테이블 구조나 관계를 생성하는 데 사용

1. CREATE
2. DROP
3. TRUNCATE
4. ALTER

1.2 DCL ( Data Control Language )

데이터 제어 언어
유저에게 권한을 부여할 때 사용

1. GRANT
2. REVOKE

1.3 TCL (Transaction Control Language)

추후에 공부 후 정리

1.4 DML ( Data Manipulation Language )

데이터 조작 언어
테이블의 데이터 조회, 생성, 수정, 삭제에 사용

1. SELECT
2. INSERT
3. UPDATE
4. DELETE

2. DataBase 관련 명령어

2.1 DataBase 생성 + (문자열세팅)

CREATE DATABASE <database-name> DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

기본적으로 column들은 table설정을 따라가고 테이블은 DataBase설정을 따라가기 때문에
애초에 DataBase에서 문자 설정을 해주면 table과 column을 설정할 필요가 없다

2.2 DataBase 제거

DROP DATABASE <database-name>;

3. User 관련 명령어

3.1 유저 생성

// 형식.. host자리엔 호스트 적어줘야함 ( ex)'localhost', '%' )
CREATE user <user-name>[@'<host>' IDENTIFIED BY <db-password>];

// 사용
CREATE user testUser@'localhost' IDENTIFIED BY '1234';

testUser란 이름은 가진 유저를 생성하고, 접근 가능한 IP은 localhost이며, 비밀번호는 1234임

3.2 유저 삭제

// 형식
drop user <user-name>@'<host>';

// 사용
DROP user testUser@'localhost';

3.3 유저 조회

use mysql;
SELECT host, user FROM user;

• host: 접근 가능한 클라이언트의 IP를 의미 ( %, localhost )
• user: 해당 유저를 의미

3.4 유저 권한 부여

// 형식
GRANT ALL PRIVILEGES ON <db-name>.<table-name> TO <user-name>@'<host-name>' [IDENTIFIED BY '<password>'];

// 사용
GRANT ALL PRIVILEGES ON testDB.* TO 'testUser'@'localhost' [IDENTIFIED BY '비밀번호'];

localhost로 접근한testUser에게 testDB라는 데이터베이스에 모든 테이블의 모든 권한을 줌

• *사용시 모든것에 권한부여
• 같은 유저라도 host마다 다른 권한을 부여받을 수 있음

3.5 유저 권한 적용

flush privileges;

3.6 권한 삭제

// 형식
REVOKE ALL PRIVILEGES ON <db-name>.* FROM <user-name>@'<host-name>';

// 사용
REVOKE ALL PRIVILEGES ON testDB.* FROM testUser@'%';

3.7 유저 권한 확인

// 형식
SHOW GRANTS FOR <user-name>[@'<host-name>'];

// 사용
SHOW GRANTS FOR testUser@'localhost';	// localhost로 접근했을 때 권한 보기
SHOW GRANTS FOR testUser;				// 모든 호스트에 대한 권한 보기

3.8 비밀번호 부여 및 변경 및 확인

// 현재암호 확인인데 암호화되어있어서 해석불가능...
SELECT host, user, authentication_string FROM user;

// 비밀번호변경형식
ALTER user <user-name>@'<host-name>' IDENTIFIED WITH mysql_native_password BY '변경할비밀번호';

// 비밀번호변경인데 안됨
ALTER user 'testUser'@'host' IDENTIFIED WITH mysql_native_password BY '12345';

4. 자료형

4.1 숫자

4.2 문자

VARCHAR()로 지정하면 크기에 맞게 알아서 TINYTEXT형식들 중에 하나로 변환된다.

4.2.1 CHAR(n)

고정형 문자

• nbyte

4.2.2 VARCHAR(n)

가변형 문자

• nbyte

4.2.3 TINYTEXT

가변형 문자

• 최대 256byte

4.2.4 TEXT

가변형 문자

• 최대 65635byte

...나머지 더 있지만 너무 커서 생략

4.3 시간

4.3.1 DATETIME

4byte
시간 값이 숫자로 변환돼서 들어간다. ( 1970/01/01 이후 )

4.3.2 TIMESTAMP

8byte
입력된 시간에서 time_zone에 의존해서 시간이 맞춰짐

4.3.3 현재시간 / 업데이트시간

DEFAULT CURRENT_TIMESTAMP [ON UPDATE CURRENT_TIMESTAMP]

생성된 현재시간을 테이블에 넣고
ON UPDATE는 업데이트 될 때 마다 시간을 넣음

5. 테이블 명령어

5.1 테이블 생성

// 형식
CREATE TABLE 테이블명 [IF NOT EXISTS] ( column정의 );

// 사용
CREATE TABLE testTable (
  _id INTEGER NOT NULL COMMENT '식별자',
  name VARCHAR(20) NOT NULL COMMENT '이름',
  createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '생성시간',
  updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '수정시간',
  PRIMARY KEY (id),
  UNIQUE KEY uk_id_name (id, name)
)ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8;

5.2 테이블 형태 검색

// 테이블 형태만 검색 형식
DESC <table-name>;

// 사용
DESC testTable;

// 테이블 정의 검색
SHOW CREATE TABLE <table-name>;

// 사용
SHOW CREATE TABLE testTable;

5.3 테이블 문자열 세팅값 변경

// 예시
ALTER TABLE `<database-name>`.`<table-name>` CHARACTER SET = <문자형태>, COLLATE = <문자형태>

// 사용
ALTER TABLE `testDB`.`testTable` CHARACTER SET = utf8, COLLATE = utf8_general_ci

5.4 테이블 이름 변경

// 형식
ALTER TABLE <current-table-name> RENAME <change-table-name>;

// 사용
ALTER TABLE testTable RENAME test;

5.5 테이블정보보기

show table status;

5.6 테이블 백업

create table 백업테이블명 like 백업할테이블명;          // ( 명세만 복사됨 )
insert into 백업테이블명 select * from 백업할테이블명;  // ( 값까지 복사 )

6. 컬럼 명령어

// 형식
ALTER TABLE <table-name> [ADD | MODIFY | CHANGE | DROP] COLUMN column명

// 사용
ALTER TABLE testTable ADD COLUMN age VARCHAR(10);              // 추가
ALTER TABLE testTable MODIFY COLUMN age INTEGER;               // 수정
ALTER TABLE testTable CHANGE COLUMN age birth VARCHAR(40);     // 이름과 컬럼내용 수정
ALTER TABLE testTable DROP COLUMN birth;                       // 컬럼삭제

단 컬럼을 수정할 때는 연관되어있는지, 자료형이 맞는지, 기본값유무에 따라서 확실하게 수정해줘야함

6.1 컬럼 추가

// 형식
ALTER TABLE <table-name> ADD <column-name> [<data-type> <options>];

// 사용
ALTER TABLE testTable ADD COLUMN age VARCHAR(10) NOT NULL;

6.2 컬럼 삭제

// 형식
ALTER TABLE <table-name> DROP COLUMN <column-name>

// 사용
ALTER TABLE testTable DROP COLUMN birth;

6.3 컬럼 타입 변경

// 형식
ALTER TABLE <table-name> MODIFY <column-name> <type>;

// 사용
ALTER TABLE testTable MODIFY name VARCHAR(100);	// 기존 디폴트값은 사라짐

6.4 컬럼 디폴트값 변경

// 형식
ALTER TABLE <table-name> ALTER COLUMN <column-name> set default <default-value>;

// 사용
ALTER TABLE testTable ALTER COLUMN name set default '무명';

6.5 컬럼 위치 변경

// 형식
ALTER TABLE <table-name> MODIFY <column-name> <type> AFTER <front-column-name>;

// 사용
ALTER TABLE testTable MODIFY id integer AFTER name;

기존 테이블 순서가 id, name이라면 이후에 name, id순으로 바뀜

6.6 컬럼 이름 변경

// 형식
ALTER TABLE <table-name> CHANGE <기존-column-name> <바꿀-column-name> <type>;

// 사용
ALTER TABLE testTable CHANGE name fullname varchar(100);

6.7 컬럼 세팅값 변경

ALTER TABLE <table-name> [ENGINE | AUTO_INCREMENT | COMMENT | RENAME]
//                        기본엔진,     시작숫자,       주석,    이름

7. 데이터

7.1 데이터 추가

// 형식
INSERT INTO <table-name>(<column-list>) VALUES(<data-list>);

// 사용
INSERT INTO testTable(_id, name) VALUES(1, 'john');

7.2 데이터 수정

// 형식
UPDATE <table-name> SET <change-data> WHERE <condition>;

// 사용
UPDATE testTable SET name = 'blue' WHERE _id = 1;

testTable에서 _id가 1인 컬럼의 name을 blue로 변경함

7.3 데이터 검색

# 형식
SELECT * FROM <table-name>;

# 사용
SELECT * FROM testTable;

7.4 데이터 모두 지우기

# 데이터만 모두 or 조건으로 지움
DELETE FROM TABLE <table-name> [WHERE <condition>]

# 데이터와 테이블의 세팅값을 모두 초기화 ( auto_increment )
TRUNCATE TABLE <table-name>;

7.5 기존 데이터를 이용해서 데이터 추가하기 ( update + select )

# update와 select를 같이 사용
# 바로 예시 User.name, Student.name이 존재한다고 가정
UPDATE User SET name = (SELECT name FROM Student WHERE id = 1) WHERE id = 1;

# 1번 Student의 name을 1번 User의 name에 넣음

7.6 기존 데이터를 이용해서 데이터 추가하기 ( insert + select )

# insert와 select를 같이 사용
# 바로 예시 User.name, Student.name이 존재한다고 가정
INSERT INTO User(name) SELECT name FROM WHERE id < 5;

# 1~5번의 Student.name을 User.name에 넣음

8. 조건

8.1 LIKE

# %는 앞뒤로 몇 글자가 와도 상관없음
# _는 한 글자만 인정함
SELECT * FROM Student WHERE name [NOT] LIKE '%주%';
SELECT * FROM Student WHERE name [NOT] LIKE '_주_';

8.2 IN

# name이 주민성, 배주성인 사람 찾기
SELECT * FROM Student WHERE name [NOT] IN ('주민성', '배주성');

# (name = '주민성' or name = '배주성' 과 같음)

8.3 BETWEEN

# id가 10~20인 사람 찾기
SELECT * FROM Student WHRER id [NOT] BETWEEN 10 and 20;

# (id >= 10 and id <= 20 과 같음)

8.4 DISTINCT

# 중복을 제거한다.
SELECT DISTINCT(addr) FROM Student;

# addr이 중복되는 데이터는 전부 하나로 표현함

8.5 COUNT

# 서울특별시에 사는 사람들이 몇 명인지 계산
SELECT COUNT(addr) FROM Student WHERE addr = '서울 특별시';

8.6 ORDER BY [RAND()]

# addr 기준으로 먼저 정렬하고 이후에 같은 addr을 가진 사람이 있다면 name을 기준으로 정렬
SELECT * FROM Student ORDER BY addr, name [DESC];

# 기준 없이 랜덤으로 정렬
SELECT * FROM Student ORDER BY RAND();

8.7 LIMIT

# 0 ~ N번째 까지 출력됨
SELECT * FROM Student LIMIT N;

# X번째부터 Y개 출력됨
SELECT * FROM Student LIMIT X, Y;

8.8 GROUP BY

column이 같은 값끼리 하나의 그룹을 만드는 것을 의미한다.

# addr이 같은 학생끼리 묶어서 하나의 그룹으로 만들고 count()함수로 개수를 센다.
SELECT addr AS '지역', COUNT(addr) AS '학생수' FROM Student GROUP BY addr;

# 위 예시에서는 addr을 그룹으로 묶었으므로 addr자체를 출력하는 것은 상관없지만,
# 다른 컬럼인 id, name같은 것은 addr을 기준으로 그룹으로 묶여있어서 각자를 출력할 수 없게 된다.
# 즉 아래와 같은 구문은 에러가 나게 된다.
SELECT name FROM Student GROUP BY addr;

# 하지만 그룹으로 묶은 것에 특수한 연산들을 하면 오류없이 출력이 된다.
SELECT AVG(id), COUNT(name) FROM Student GROUP BY addr;

# 그룹으로 묶인 것들의 id평균과 name의 개수를 구하는 것을 의미하는 식이다.

8.9 HAVING

GROUP BY에서 조건을 걸 때 사용한다.

# addr을 기준으로 그룹하고 그것을 카운팅한 값이 25개 이상인 것을 오름차순으로 정렬하라는 의미이다.
SELECT addr AS '지역', COUNT(addr) AS cnt FROM Student GROUP BY addr HAVING cnt > 25 ORDER BY cnt DESC;

# cnt를 한글로 작성하면 HAVING이나 ORDER BY에서 사용하면 오류가 발생한다.

8.10 case ~ when ~ then

if ~ else 구문과 유사함

# addr이 경기도면 1, 경상남도면 2, 나머지는 3
select distinct(addr),
	(case when addr like '경기도%' then '1'
				when addr like '경상남도%' then '2'
                else '3' end)
	from Student;

# 아래처럼 값을 조건 없이 바로 비교할 때는 첫 번째 when보다 앞에 사용해서 뒤에 반복적인 사용을 줄일 수 있다.
select distinct(addr),
	(case addr when '경기도 용인시' then '1'
				when '경상남도 진주시' then '2'
                else '3' end)
	from Student;

9. 추가 정보

9.1 관계

9.1.1 ( 1 : 1 )

예를 들면 국가와 대통령의 관계가 1:1 관계다. ( 딱히 예시가 떠오르는 게 없네요... )
국가는 반드시 하나의 대통령이 있어야 하고, 대통령도 반드시 하나의 국가에 속해야 한다.

1:1 관계에서는 두 개의 테이블 중에 하나의 테이블에만 다른 테이블을 참조하는 키를 가지면 된다.

9.1.2 ( 1 : N )

예를 들면 유저와 게시글의 관계가 1:N 관계다.
공동작업이 없다는 가정하에 유저는 여러 개의 게시글을 작성할 수 있지만, 게시글은 하나의 유저에 종속적이다.

1:N 관계에서는 1에서 N의 테이블을 참조하는 키를 가져야 한다.

9.1.3 ( N : M )

예를 들면 유저와 게시글의 좋아요 관계가 N:M 관계다.
유저는 여러 개의 게시글에 좋아요를 누를 수 있고, 게시글도 여러 명의 유저에게 좋아요를 받을 수 있다.

N:M 관계에서는 중간 테이블이 필연적으로 생기게 된다.
만약 중간 테이블이 없다면 각 테이블에 각 테이블을 참조하는 키를 가져야 하는데 두 개 이상의 참조가 발생할 경우에는 한 컬럼에 여러 정보를 넣거나 중복되는 컬럼을 생성해야 처리가 가능하다.
( 유저가 두 개의 게시글에 좋아요를 누를 경우 유저의 게시글아이디컬럼에 두 개의 정보를 넣거나, 같은 유저를 두개의 컬럼으로 나눠서 각각 게시글 아이디를 넣어줘야 한다. DB의 규칙을 어기거나, 불필요하게 반복적인 데이터가 들어가는 형태가 된다. )

이런 불필요한 처리가 발생하므로 중간 테이블을 만들고 해당 테이블에 각 테이블을 참조하는 키를 맵핑하는 방식으로 구현해야 한다.

9.2 index

인덱스값은 메모리에 올려두는 값이다. ( 메모리에 올라가있으므로 검색속도↑ )

mysql 기준으로 인덱스를 하나의 페이지 단위로 저장한다.
하나의 페이지의 크기는 16KB(16 * 1024BYTE)이다.

페이지에 저장된 인덱스를 기준으로 처리를 할 경우에는 메모리에 존재, 특정 값만 검색하므로 더 빠르고 효율적으로 처리를 할 수 있다.

만약에 인덱스가 아닌 값을 기준으로 SELECT를 하는 경우 해당 테이블을 full scan해야 하므로 더 오래 걸리고 효율성이 떨어진다.

하지만 그렇다고 인덱스를 남발하면 인덱스 페이지에서 검색할 양이 늘어나므로 비효율적인 연산이 늘어날 수 있으므로 적당하게 조절해서 사용하는 것이 중요하다.

• primary key, foregin key, unique key는 자동적으로 인덱스로 등록된다.

9.2.1 index 검색

SHOW INDEX FROM <table-name>;

9.2.2 index 생성

ALTER TABLE <table-name> ADD INDEX <index-name> (<column-name> <정렬방법(ASC, DESC)>);

9.2.3 index 삭제

ALTER TABLE <table-name> DROP INDEX <index-name>;

9.2.4 테이블의 키 검색

select * from information_schema.table_constraints where table_name = '<table-name>';

9.3 FOREIGN KEY

다른 테이블과의 관계를 정의할 때 사용한다.

• 예시
  예시는 author.id와 book.author_id의 관계를 설정
  book.author_id가 author.id를 참조한다.

9.3.1 테이블 정의 시 선언

# 형식
[CONSTRAINT] [<index-name>] FOREIGN KEY (<참조할-column-name>) REFERENCES <참조당할-table-name>(<참조당할-column-name>) [ON (DELETE | UPDATE) (RESTRICT | CASCADE | SET NULL | NOACTION | SET DEFAULT)];

# 사용
# book을 만들 때 사용... book이 author를 참조하는 형식이라서
# author를 먼저 만들고 이후에 book을 만들 때 foreign key를 지정해야한다.
CONSTRAINT fk_author_book FOREIGN KEY (author_id) REFERENCES author(id) ON DELETE CASCADE;

9.3.2 테이블 정의 후 선언

# 형식
ALTER TABLE <참조할-table-name> ADD CONSTRAINT [<index-name>] FOREIGN KEY(<참조할-column--name>) REFERENCES <참조당할-table-name>(<참조당할-column-name>) [ON (DELETE | UPDATE) (RESTRICT | CASCADE | SET NULL | NOACTION | SET DEFAULT)];

# 사용
ALTER TABLE book ADD CONSTRAINT pk_author_book FOREIGN KEY (author_id) REFERENCES author(id) ON DELETE CASCADE;

기본적으로 ON DELETE를 자주사용하므로 DELETE기준으로 설명함
1. RESTRICT : 기본값으로 참조된 데이터가 삭제될경우 에러표시
2. CASCADE : 참조된 데이터가 삭제될경우 같이 삭제
4. SET NULL : 참조된 데이터가 삭제될경우 NULL로 대체
5. NO ACTION : 참조된 데이터가 삭제될경우 그냥 그대로 놔둠
6. SET DEFAULT : 참조된 데이터가 삭제될경우 DEFAULT로 대체

10. JOIN

식별자값을 이용해서 다른 테이블과 합쳐진 결과를 출력하는 명령어

• 예시로 사용할 테이블 ( book.authorId -> author.id의 foreign key )

	book테이블				author테이블
id	name	authorId		id	name	age
1	반지의 제왕	1		1	J.R.R. 톨킨	21
2	해리포터	2		2	JK 롤링	33
3	호빗	1		3	아가사 크리스티	31
4	너의 이름은.	4		5	히가시노 게이고	55

10.1 INNER JOIN

둘 다 값이 존재하는 결괏만 보아서 보여주는 명령어다.

# 형태
SELECT * FROM <table-name> JOIN <table-name> on <condition>;

# 예시
SELECT a.name as '저자명', b.name as '도서명' FROM author AS a [INNER] JOIN book AS b on a.id = b.authorId;

• 결과

저자명	도서명
J.R.R. 톨킨	반지의 제왕
J.R.R. 톨킨	호빗
JK 롤링	해리포터

10.2 LFET [OUTER] JOIN

좌측에 적은 값 즉, 먼저 적은 값을 기준으로 모든 값을 합쳐서 보여주고 없으면 매칭되는 상대가 없으면 NULL로 채워서 보여준다.

# 형태
SELECT * FROM <table-name> LEFT [OUTER] JOIN <table-name> on <condition>;

# 예시
SELECT a.name as '저자명', b.name as '도서명' FROM author AS a LEFT JOIN book AS b on a.id = b.authorId;

• 결과

저자명	도서명
J.R.R. 톨킨	반지의 제왕
J.R.R. 톨킨	호빗
JK 롤링	해리포터
아가사 크리스티	NULL
히가시노 게이고	NULL

10.3 RIGHT [OUTER] JOIN

LEFT JOIN에서 기준점만 바뀐 연산입니다.

# 형태
SELECT * FROM <table-name> RIGHT [OUTER] JOIN <table-name> on <condition>;

# 예시
SELECT a.name as '저자명', b.name as '도서명' FROM author AS a RIGHT JOIN book AS b on a.id = b.authorId;

• 결과

저자명	도서명
J.R.R. 톨킨	반지의 제왕
J.R.R. 톨킨	호빗
JK 롤링	해리포터
NULL	너의 이름은.

11. method

sum, avg, mod, count, max, min, stdev, var_samp, cast, convert, now, str_to_date, concat, concat_ws, group_concat, if

12. 트랜젝션

민감하거나 중요한 정보를 다룰 경우에 세이브포인트를 만들어서 작업할 수 있는 방법

# auto commit 임시적으로 취소
start TRANSACTION;

# 각종 작업 ( 데이터 가공 처리 )

# 정상적으로 끝났을 경우 적용
COMMIT;

# 문제가 발생해서 최신 커밋으로 돌아가기
ROLLBACK;

마무리

추가로 공부하면 계속 추가할 예정

참고한사이트

• 유튜브 시니어코딩 - mysql강의