MySQL 메모 및 Tip

1. 데이터베이스(스키마)

데이터베이스 : 데이터를 저장하는 공간

= 스키마 : MySQL안의 데이터베이스. 데이터베이스와 동일한 용어라고 생각해도 된다.

 

데이터베이스 만들기(선)  -> 테이블 만들기(후)

데이터베이스를 먼저 만들어야 그 안에 테이블을 생성할 수 있고 그 테이블 안에서 데이터의 입력/수정/삭제/조회 등이 가능하다.

 

SHOW DATABASES;

USE mydata;

SHOW TABLES;

DESC customer_db;

 

 

2. 테이블 구조 변경하기

1. 테이블에 새로운 컬럼 추가
ALTER TABLE mytable ADD COLUMN model_type varchar(10) NOT NULL;

2. 테이블 컬럼 타입 변경
ALTER TABLE mytable MODIFY COLUMN name varchar(20) NOT NULL;

3. 테이블 컬럼 이름 변경(타입도 변경가능)
ALTER TABLE mytable CHANGE COLUMN modelnumber model_num varchar(10) NOT NULL;

4. 테이블 컬럼 삭제
ALTER TABLE mytable DROP COLUMN series;

 

 

 

3. 잊고 있던 구문 정리

 

1) LIKE 활용하여 조건에 맞는 데이터 검색하기

와일드카드 '%' , '_'(under bar) 활용 가능. 이 중 언더바 활용 문법을 잊고 있던 것을 캐치, 잊지 않게 메모함.

 

ex) 이름이 '오'로 시작하고 뒤에 2글자가 붙는 데이터 검색하기

SELECT * FROM mytable WHERE name LIKE '오__';

 

** 주의 : %는 LIKE와 함께 쓰일 때 해당 의미를 갖는다. 만약 LIKE와 함께 쓰이지 않고 WHERE 조건 절에서 = 과 함께 쓰인다면 의미가 달라진다. 무심코 실수 않게 주의하기.

SELECT * FROM mytable WHERE name LIKE '오%'; // 이름이 '오'로 시작하는 데이터 검색

SELECT * FROM mytable WHERE name = '오%'; // 이름이 '오%' 자체인 데이터 검색

 

 

4. SQL DCL(Data Control Language)

[1]  mysql 사용자 확인, 추가, 비밀번호 변경, 삭제 등 가능 (말그대로 Control에 관련된 것들)

# mysql -u root -p

mysql > use mysql;

 

1) 로컬에서만 접속 가능한 계정 생성

  mysql > create user '유저아이디'@localhost identified by '비밀번호'; 

  ex) create user 'luha'@localhost identified by 'helloworld' ;

 

2) 모든 호스트에서 접속 가능한 계정 생성

  mysql > create user '유저아이디'@'%' = '비밀번호';

  ex) create user 'luha'@'%' = 'helloworld';

 

3) 계정 비밀번호 변경

  mysql > SET PASSWORD FOR '유저아이디'@'%' = '비밀번호';

  ex) SET PASSWORD FOR 'luha'@'%' = 'newPassword';

 

[2] mysql 접속 허용 권한 관련 : 권한 확인, 부여(모든/특정 권한) , 삭제 등

1) 현재 부여된 권한 확인

mysql > SHOW GRANTS for 아이디;

ex) SHOW GRANTS for 'luha'@'%'

 

2) 사용자 권한 부여 : grant 명령어 사용. 생성된 사용자에게 권한을 부여하는 과정을 거친다.

 

  2-1. 전체 권한 할당하기

  mysql > GRANT ALL privileges on [데이터베이스명.테이블명] to [계정명]@'[접속 ip 주소]' identified by '[비밀번호]';

 

  ex) 로컬에서만 접속 허용

  mysql > GRANT ALL on [데이터베이스명.테이블명] to 'root'@localhost;

 

  2-2.  일부 권한 할당하기

  mysql > GRANT [할당 권한] ON [데이터베이스명.테이블명] TO [계정명]@'[접속 IP 주소]' identified by '[비밀번호]';

 

  ex) GRANT SELECT, UPDATE ON DATABASE.TABLE to 'root'@localhost;

 

3) 권한 회수

REVOKE [할당 권한] ON [데이터베이스명].[테이블명] FROM [계정명]@'[접속 IP 권한]';

 

 

5. SELECT 관련 팁

 

1. LIMIT 과 DISTINCT의 적절한 활용

 

LIMIT 키워드는 MySQL에서 쿼리 결과의 행 수를 제한하는데 사용한다. 쿼리로부터 반환되는 레코드의 수를 특정 갯수로 제한할 수 있으며 페이지네이션을 구현하거나 몇 개의 샘플 레코드만 필요한 경우 등에 사용할 수 있다.

 

* Tip 하나!

대용량의 데이터가 있는 테이블에서 단순히 SELECT * FROM table_name; 으로 데이터를 조회하려고 하면 결과가 출력되기까지 오랜 시간이 걸린다. 테이블에 있는 데이터 샘플을 보고 해당 테이블에 대한 정보를 얻거나 구조를 파악하려고 하는 경우에 LIMIT 키워드를 적절히 활용하면 빠른 결과 데이터 조회가 가능하다.

// TB_INDEPTH_CNS 테이블에서 처음 10개의 행을 반환한다.
SELECT * FROM TB_INDEPTH_CNS
LIMIT 10;

// TB_INDEPTH_CNS 테이블에서 5번째 행부터 시작하여 10개의 행을 반환한다.
SELECT * FROM TB_INDEPTH_CNS
LIMIT 6, 10;

 

* Tip 둘!

페이지네이션에서의 사용

LIMIT은 데이터 페이지네이션 구현에 매우 유용하다. 각 페이지마다 10개의 아이템을 보여주고 싶은 경우,

첫 번째 페이지에는 LIMIT 0, 10

두 번째 페이지에는 LIMIT 10, 10

세 번째 페이지에는 LIMIT 20, 10

과 같이 쿼리를 설정할 수 있다.

 

 

 

DISTINCT 키워드는 중복된 결과를 제거하고 쿼리에서 고유한 값만을 반환하는데 사용된다. DISTINCT 키워드는 SELECT 문 바로 뒤에 위치하며 하나 이상의 열을 지정 가능하다. 지정된 모든 열의 조합에 대해 고유한 결과만을 반환한다. 데이터 분석이나 데이터 정제 과정에서 중복 값을 제거하고자 할 때 유용하게 사용될 수 있다.

//customers 테이블에서 중복 없이 모든 고유한 country 값 반환
SELECT DISTINCT country FROM customers;

 

주의!!

DISTINCT 사용 시에는 성능에 주의해야 한다. 대규모 데이터셋에서 고유한 값을 찾으려면 전체 데이터셋을 스캔하거나 정렬해야 할 수 있기 때문이다.

 

 

 

2. GROUP BY

GROUP BY 절은 선택된 열에 기반하여 결과 집합을 그룹화한다. 주로 집계함수(AVG, COUNT, MAX, MIN, SUM 등) 와 함께 사용된다. 

// customer_id 별로 그룹화 => 각 고객이 몇 번 주문했는지 계산
SELECT customer_id, COUNT(*)
FROM orders
GROUP BY customer_id;

 

주의!!

SELECT 절에 열(컬럼)이 포함되어 있는 경우, 해당 열(컬럼)은 반드시 GROUP BY 절에 나타나야 한다.(집계함수 내에 있는 열 제외)

 

집계 함수(AVG, COUNT, MAX, MIN, SUM 등)는 여러 행의 데이터를 요약하여 단일 값을 반환한다. GROUP BY 절을 사용하면, 그룹별 해당 집계 함수 기능에 해당하는 요약 정보를 얻을 수 있다. 만약, SELECT 문에 집계 함수가 아닌 어떤 열이 포함되고 그 열이 GROUP BY 절에도 나타나지 않으면 각 그룹에 대한 어떤 값을 선택할지 결정할 수 없으므로 잘못된 구문이 된다. 요약하면, 집계 함수가 아닌 열을 SELECT 문에 포함시키려면, 해당 열이 GROUP BY 절에도 나타나야 한다고 할 수 있다.

 

예를 들어 고객별 주문 건수의 합을 구하는 상황을 가정해보자.

 

[ 잘못된 쿼리 예시 ]

SELECT customer_id, order_date, COUNT(order_id)
FROM orders
GROUP BY customer_id;

 

위의 쿼리에서 order_date는 집계 함수에도, GROUP BY 절에도 나타나지 않은 열이다. customer_id별로 여러 주문 건수와 각각의 주문 날짜가 여러 개 존재할 수 있으므로 어떤 order_date값을 선택해야 할지 결정할 수 없다. 따라서 위의 쿼리는 대부분의 경우 오류가 반환되는 잘못된 문장이다.

 

[ 올바른 쿼리 예시 1 ]

SELECT customer_id, COUNT(order_id)
FROM orders
GROUP BY customer_id;

 

위의 쿼리는 customer_id별로 주문 건수의 합을 구하고 있다. customer_id는 GROUP BY 절에 포함되어 있으므로 고객별 주문 건수의 총계가 그룹화되어 올바르게 반환된다.

 

[ 올바른 쿼리 예시 2 ]

SELECT customer_id, order_date, COUNT(order_id)
FROM orders
GROUP BY customer_id, order_date;

 

해당 쿼리는 order_date도 GROUP BY 절에 포함시켰다. customer_id와 order_date의 조합으로 그룹화하여 1) 고객별 2) 각 날짜에 대한 주문 수를 반환하는 올바른 문장이다.

 

위의 두 예시들을 통해 SELECT 문에 포함된 모든 열이 GROUP BY 절에 나타나야 한다는 규칙을 이해할 수 있다. 집계 함수로 처리되지 않는 열을 SELECT 문에 포함시킬 때는 해당 열을 GROUP BY 절에 명시하여 결과가 명확하게 반환될 수 있도록 해야 할 것이다.

 

GROUP BY와 집계함수는 같이 못쓴다.(WHERE 절에 조건으로)

GROUP BY와 WHERE절을 아예 못쓰는게 아니라 집계함수를 WHERE 절 조건으로 못쓴다는 것.

GROUP BY와 집계함수를 쓰려면 GROUP BY 뒤의 HAVING절에 집계함수를 써야 한다.

SELECT provider, COUNT(*) 
FROM products
GROUP BY provider
HAVING COUNT(*)>=50;

// from products 뒤에 WHERE COUNT(*)>=50 이런 식으로 쓰면 안된다는 말.

 

 

 

[ 예 시 ] 올바른 쿼리문. 키워드별 위치, 순서 등 살펴보기

SELECT customer, count(*)
FROM orders
WHERE customer != '홍길동'
GROUP BY customer HAVING COUNT(*)>=20
ORDER BY COUNT(*) DESC
LIMIT 10;