MySQL 몰아보기
Q & A
- Command에서 Password 입력
-p 옵션에 공백없이 암호입력mysql -u ID -pPW_NO_SPACE
유용한 예
- Table 사이즈/ ROW수 구하기
SELECT TABLE_NAME AS "Tables",
round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"
FROM information_schema.TABLES
WHERE table_schema = "db_ac"
ORDER BY (data_length + index_length) DESC;
SELECT table_name, table_rows FROM information_schema. tables WHERE table_schema = 'db_ac' ORDER BY table_name;
[통합]
SELECT TABLE_NAME AS "Tables",
round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB",
table_rows
FROM information_schema.TABLES
WHERE table_schema = "db_ac"
ORDER BY (data_length + index_length) DESC;
+--------------------+------------+------------+
| Tables | Size in MB | table_rows |
+--------------------+------------+------------+
| R_FLOAT_INF | 23626.00 | 178128772 |
| R_FLOAT_LOC | 7744.98 | 39105811 |
| R_AP_STAT_TRAFFIC | 630.56 | 2465270 |
| R_ALARM_HISTORY | 355.74 | 615238 |
| R_EVENT_HISTORY | 308.44 | 4654510 |
- 조건별 카운트 : CASE WHEN ... THEN ... END
그룹별로 각각의 조건에 맞는 카운티을 한다.
SELECT
COUNT(CASE WHEN count1 THEN 1 END) as count1,
COUNT(CASE WHEN count2 THEN 1 END) as count2,
COUNT(CASE WHEN count3 THEN 1 END) as count3,
COUNT(CASE WHEN count4 THEN 1 END) as count4,
COUNT(CASE WHEN count5 THEN 1 END) as count5,
COUNT(CASE WHEN count6 THEN 1 END) as count6,
COUNT(CASE WHEN count7 THEN 1 END) as count7,
COUNT(CASE WHEN count8 THEN 1 END) as count8
FROM
....
GROUB BY XXX
- 조건별 값 지정 : IF(조건, 참 값, 거짓 값)
SELECT
IF(LENGTH( name ) <= 4, nickname, SUBSTR(name, 1, 4)) as new_name
- Select 결과 이용 검색
// 사용 예> 영어, 국어 점수를 각각의 테이블로 관리할 때 같은 ID별로 국어, 영어 점수를 한꺼번에 찾을 때
select userid, point as 국어, (select point from T_영어 where ID=t1.userid) AS 영어
FROM T_국어 t1
- 중복 라인 찾기
[다중 컬럼 중복]
select D_MAC, D_DETECTED, COUNT from
(select D_MAC, D_DETECTED, count(D_MAC) as COUNT from R_FLOAT_INF
where D_DETECTED >= UNIX_TIMESTAMP('2019-10-30 16:48:35')
AND D_DETECTED <= UNIX_TIMESTAMP('2019-10-30 16:48:35')
AND D_MAC='ea:42:45:cf:70:15' GROUP BY D_MAC, D_DETECTED
)
AS TMP_TBL where COUNT > 1;
[단일 컬럼]
select D_MAC, D_DETECTED, D_SS_ID, count(D_MAC) as COUNT from R_FLOAT_INF
GROUP BY D_DETECTED HAVING COUNT(D_DETECTED) > 1 limit 3;
ERR> 다중 칼럼
select D_MAC, D_DETECTED, D_SS_ID, count(D_MAC) as COUNT from R_FLOAT_INF
GROUP BY D_MAC, D_DETECTED HAVING COUNT(D_MAC, D_DETECTED) > 1;
우회> 다중 칼럼 => 하나의 값으로 변환 : HAVING COUNT(CONCAT(D_MAC, D_DETECTED))
select D_MAC, D_DETECTED, FROM_UNIXTIME(D_DETECTED,'%Y-%m-%d %H:%i:%s'), D_SS_ID, count(D_MAC), COUNT(CONCAT(D_MAC, D_SS_ID, D_DETECTED)) as COUNT from R_FLOAT_INF
where D_DETECTED >= UNIX_TIMESTAMP('2019-10-30 20:19:30') AND D_SS_ID < 10
GROUP BY D_MAC, D_DETECTED
HAVING COUNT(CONCAT(D_MAC, D_SS_ID, D_DETECTED)) > 1
ORDER BY COUNT(CONCAT(D_MAC, D_SS_ID, D_DETECTED)) DESC
LIMIT 3;
- GROUP BY
Group by절에는 집계함수를 쓴 컬럼을 제외한 나머지 컬럼을 적어 줍니다.
[출처] Mysql Group by, Distinct, Alias 사용법(Step7)|작성자 Brian story
- GROUP BY 절은 데이터들을 원하는 그룹으로 나눌 수 있다.
- 나누고자 하는 그룹의 컬럼명을 SELECT절과 GROUP BY절 뒤에 추가하면 된다.
- 집계함수와 함께 사용되는 상수는 GROUP BY 절에 추가하지 않아도 된다. (개발자 분들이 많이 실수 함)
집계 함수와 상수가 함께 SELECT 절에 사용되는 예이다.
-- 부서별 사원수 조회
SELECT '2005년' year, deptno 부서번호, COUNT(*) 사원수
FROM emp
GROUP BY deptno
ORDER BY COUNT(*) DESC;
YEAR 부서번호 사원수
------ ---------- ----------
2005년 30 6
2005년 20 5
2005년 10 3
부서별로 그룹하여 부서번호, 인원수, 급여의 평균, 급여의 합을 조회
SELECT deptno, COUNT(*), ROUND(AVG(sal)) "급여평균",
ROUND(SUM(sal)) "급여합계"
FROM emp
GROUP BY deptno;
DEPTNO COUNT(*) 급여평균 급여합계
-------- ---------- ---------- ----------
30 6 1567 9400
20 5 2175 10875
10 3 2917 8750
업무별로 그룹하여 업무, 인원수, 평균 급여액, 최고 급여액, 최저 급여액 및 합계를 조회
SELECT job, COUNT(empno) "인원수", AVG(sal) "평균급여액",
MAX(sal) "최고급여액", MIN(sal) "최저급여액",
SUM(sal) "급여합계"
FROM emp
GROUP BY job;
JOB 인원수 평균급여액 최고급여액 최저급여액 급여합계
----------- -------- ---------- ---------- ---------- ----------
CLERK 4 1037.5 1300 800 4150
SALESMAN 4 1400 1600 1250 5600
PRESIDENT 1 5000 5000 5000 5000
MANAGER 3 2758.33333 2975 2450 8275
ANALYST 2 3000 3000 3000 6000
GROUP BY 절은 집계 함수 없이도 사용 될 수 있다.(DISTINCT와 용도가 비슷해 짐)
- GROUP BY는 말 그대로 그룹을 나누는 역할
-- GROUP BY를 이용한 부서번호 조회 예
SELECT deptno
FROM emp
GROUP BY deptno;
DEPTNO
------
30
20
10
- DISTINCT와 GROUP BY절
- DISTINCT : 주로 UNIQUE(중복을 제거)한 컬럼이나 레코드를 조회
- GROUP BY : 데이터를 그룹핑해서 그 결과를 가져오는 경우
- 동일한 형태의 작업 => 일부 작업의 경우 DISTINCT로 동시에 GROUP BY로도 처리
- DISTINCT를 사용해야 할지, GROUP BY를 사용해서 데이터를 조회하는 것이 좋을지 고민되는 경우들이 가끔 있다.
-- DISTINCT를 사용한 중복 데이터 제거
SELECT DISTINCT deptno FROM emp;
-- GROUP BY를 사용한 중복 데이터 제거
SELECT deptno FROM emp GROUP BY deptno;
DEPTNO
------
30
20
10
GROUP BY와 DISTINCT는 각자 고유의 기능이 있다
- 집계함수를 사용하여 특정 그룹으로 구분 할 때는GROUP BY 절
- 특정 그룹 구분없이 중복된 데이터를 제거할 경우에는 DISTINCT 절
-- 아래와 같은 기능은 DISTINCT를 사용하는 것이 훨씬 효율적이다.
SELECT COUNT(DISTINCT d.deptno) "중복제거 수", COUNT(d.deptno) "전체 수"
FROM emp e, dept d
WHERE e.deptno = d.deptno;
-- 집계 함수가 필요한 경우는 GROUP BY를 사용해야 한다.
SELECT deptno, MIN(sal)
FROM emp
GROUP BY deptno;
- HAVING 절
- WHERE 절에서는 집계함수를 사용 할 수 없다.
- HAVING 절은 집계함수를 가지고 조건비교를 할 때 사용한다.
- HAVING절은 GROUP BY절과 함께 사용이 된다.
사원수가 다섯 명이 넘는 부서와 사원수를 조회
SELECT b.dname, COUNT(a.empno) "사원수"
FROM emp a, dept b
WHERE a.deptno = b.deptno
GROUP BY dname
HAVING COUNT(a.empno) > 5;
월급이 5000을 초과하는 JOB에 대해서 JOB과 월급여 합계를 조회
단, 판매원(SALES)은 제외하고 월 급여 합계로 내림차순 정렬
SELECT job, SUM(sal) "급여합계"
FROM emp
WHERE job != 'SALES' -- 판매원은 제외
GROUP BY job -- 업무별로 Group By
HAVING SUM(sal) > 5000 -- 전체 월급이 5000을 초과하는
ORDER BY SUM(sal) DESC; -- 월급여 합계로 내림차순 정렬
JOB 급여합계
------------------ ----------
MANAGER 8275
ANALYST 6000
- 결과를 화일로 저장
INTO OUTFILE 'PATH'
into OUTFILE>
select * from R_CALL_COUNTER into OUTFILE '/tmp/SelectAll.R_CALL_COUNTER';
select * from R_AP_INF into OUTFILE '/tmp/SelectAll.R_AP_INF';
Check & Move>
tftp -p -l SelectAll.R_CALL_COUNTER -r firstpw/SelectAll.R_CALL_COUNTER 192.168.88.203
tftp -p -l SelectAll.R_AP_INF -r firstpw/SelectAll.R_AP_INF 192.168.88.203
각 각 md5sum 으로 Valid check
- 상수
select 'All-AP' as ap_label, .........
- 통계
count()
sum()
- 시간
- Unixtime -> Daytime
FROM_UNIXTIME(D_DETECTED,'%Y-%m-%d %H:%i:%s')
- 현재 Unixtime
UNIX_TIMESTAMP()
- Day(time) -> UnixTime
UNIX_TIMESTAMP('2009-05-15 20:11:22')
- 현재 Daytime
now()
- 가감
DATE_ADD(now(), INTERVAL -1 day)
year month day hour minute second
- Format
DATE_FORMAT(regdate, "%Y%m%d %H%i%s")
select date_format(start_time, '%Y-%m-%d %H:%i:%s') as date_time
- 분할
DAYOFWEEK(date) : 요일을 정수로 반환(1=일요일, 7=토요일)
DAYOFMONTH(date): 월의 몇번째 날인지를 정수로 반환
DAYOFYEAR(date) : 년중 몇번째 날인지를 정수로 반환
MONTH(date) : 월을 1∼12의 정수로 반환
DAYNAME(date) : 요일 이름을 문자로 반환
MONTHNAME(date) : 월 이름을 문자로 반환
YEAR(date) : 년도를 4자리 정수로 반환
select DATE_FORMAT(DATE_ADD(now(), INTERVAL -1 year) , "%Y-%m-%d %H:%i:%s") as "LAST YEAR";
+---------------------+
| LAST YEAR |
+---------------------+
| 2020-06-16 15:29:37 |
+---------------------+
MariaDB [db_ac]> select D_DETECTED, FROM_UNIXTIME(D_DETECTED,'%Y-%m-%d %H:%i:%s') as DATE
from db_ac.R_FLOAT_LOC where DATE_FORMAT(DATE_ADD(now(), INTERVAL -10 month)
, "%Y-%m-%d %H:%i:%s") >= FROM_UNIXTIME(D_DETECTED,'%Y-%m-%d %H:%i:%s') limit 10;
+------------+---------------------+
| D_DETECTED | DATE |
+------------+---------------------+
| 1595586236 | 2020-07-24 19:23:56 |
| 1595586236 | 2020-07-24 19:23:56 |
| 1595586236 | 2020-07-24 19:23:56 |
| 1595586236 | 2020-07-24 19:23:56 |
| 1595586236 | 2020-07-24 19:23:56 |
| 1595586236 | 2020-07-24 19:23:56 |
| 1595586236 | 2020-07-24 19:23:56 |
| 1595586236 | 2020-07-24 19:23:56 |
| 1595586236 | 2020-07-24 19:23:56 |
| 1595586236 | 2020-07-24 19:23:56 |
+------------+---------------------+
MariaDB [db_ac]> select D_DETECTED, FROM_UNIXTIME(D_DETECTED,'%Y-%m-%d %H:%i:%s') as DATE
from db_ac.R_FLOAT_LOC where UNIX_TIMESTAMP(DATE_ADD(now(), INTERVAL -10 month))
>= D_DETECTED limit 10;
+------------+---------------------+
| D_DETECTED | DATE |
+------------+---------------------+
| 1595586236 | 2020-07-24 19:23:56 |
| 1595586236 | 2020-07-24 19:23:56 |
| 1595586236 | 2020-07-24 19:23:56 |
| 1595586236 | 2020-07-24 19:23:56 |
| 1595586236 | 2020-07-24 19:23:56 |
| 1595586236 | 2020-07-24 19:23:56 |
| 1595586236 | 2020-07-24 19:23:56 |
| 1595586236 | 2020-07-24 19:23:56 |
| 1595586236 | 2020-07-24 19:23:56 |
| 1595586236 | 2020-07-24 19:23:56 |
+------------+---------------------+
- replace(필드명, 찾는것, 바꿀것) : AP123 -> 123 -> ord
- CAST() : type casting
select CAST(replace(node_name, 'AP', '') AS UNSIGNED) as ord FROM guidb.nodelist order by ord;
- CONCAT : 문자 결합
- CONCAT_WS : 다중 문자 결합
주의) 멤버들중 NULL이거나 ""면 문제 발생
SET @id := (SELECT id FROM YOUR_TABLE ORDER BY id DESC LIMIT 1);
INSERT INTO YOUR_TABLE (username) VALUES(
CONCAT("ADMIN", @id + 1, ':', username)
);
CONCAT_WS(' ', IFNULL(name,'''), IFNULL(type,'''))
CONCAT_WS(' ', IFNULL(name,'''), IFNULL(type,'''))
- DB 변수
mysqladmin -u -p variables
| Variable_name | Value
| aria_block_size | 8192
| aria_checkpoint_interval | 30
| aria_checkpoint_log_activity | 1048576
| aria_force_start_after_recovery_failures | 0
| aria_group_commit | none
| aria_group_commit_interval | 0
| aria_log_file_size | 1073741824
| aria_log_purge_type | immediate
| aria_max_sort_file_size | 9223372036853727232
- CONCAT : 문자 결합
- CONCAT_WS : 다중 문자 결합
주의) 멤버들중 NULL이거나 ""면 문제 발생
SET @id := (SELECT id FROM YOUR_TABLE ORDER BY id DESC LIMIT 1);
INSERT INTO YOUR_TABLE (username) VALUES(
CONCAT("ADMIN", @id + 1, ':', username)
);
CONCAT_WS(' ', IFNULL(name,'''), IFNULL(type,'''))
CONCAT_WS(' ', IFNULL(name,'''), IFNULL(type,'''))
SET @id := (SELECT id FROM YOUR_TABLE ORDER BY id DESC LIMIT 1);
INSERT INTO YOUR_TABLE (username) VALUES(
CONCAT("ADMIN", @id + 1, ':', username)
);
CONCAT_WS(' ', IFNULL(name,'''), IFNULL(type,'''))
CONCAT_WS(' ', IFNULL(name,'''), IFNULL(type,'''))
INSERT
- 새로운 Column이 추가된 후에 기존의 Insert값을 활용할 때
맨 뒤에 새 컬럼 추가시>
기존 INSERT문에서 COLUMN을 지정하지 않았다면 null로 자동 입력된다.
맨 뒤가 아닌 곳에 새 컬럼 추가시>
해당 위치에 명시적으로 null,을 추가한다.
SELECT
- 결과를 화일로 저장
INTO OUTFILE 'PATH'
into OUTFILE>
select * from R_CALL_COUNTER into OUTFILE '/tmp/SelectAll.R_CALL_COUNTER';
select * from R_AP_INF into OUTFILE '/tmp/SelectAll.R_AP_INF';
Check & Move>
tftp -p -l SelectAll.R_CALL_COUNTER -r firstpw/SelectAll.R_CALL_COUNTER 192.168.88.203
tftp -p -l SelectAll.R_AP_INF -r firstpw/SelectAll.R_AP_INF 192.168.88.203
각 각 md5sum 으로 Valid check
- COLUMN NAME 확인
information_schema
desc
information_schema> select COLUMN_NAME from information_schema.columns where table_name='R_CALL_COUNTER';
+----------------+
| COLUMN_NAME |
+----------------+
| D_AP_ID |
| D_TIME_KEY |
desc > desc R_CALL_COUNTER;
+----------------+------------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+------------+-------+
| D_AP_ID | int(11) | NO | PRI | NULL | |
| D_TIME_KEY | int(10) unsigned | NO | PRI | NULL | |
| D_DATE | date | NO | | 0000-00-00 | |
order by>
asc - 오름차순 : Default
desc - 내림차순
select * from R_EVENT_HISTORY order by D_EV_OCC_TIME desc limit 3;
+---------------------+----------------------+
| D_EV_OCC_TIME | D_EV_STR |
+---------------------+----------------------+
| 2019-01-18 12:44:30 | AP 2 UP |
| 2019-01-18 12:44:27 | AP 2 Update Start |
| 2019-01-18 12:44:22 | AP 2 Upgrade Failure |
+---------------------+----------------------+
BACKUP & RESTORE
- DB & Table 정보
mysql
show databases;
use guidb;
show tables;
- backup
Export Database(backup):
mysqldump -u root -p [database_name] > [dump_filename].sql
Ex>
/storage> mysqldump guidb > guidb.sql
특정 테이블만>
mysqldump [-u root -p] database_name TABLE_NAME > TABLE_NAME.sql
- restore
Import Database:
mysql -u root -p [database_name] < [dump_filename].sql
각종 조건들
- Definitions
value: {expr | DEFAULT} value_list: value [, value] ... assignment: col_name = value assignment_list: assignment [, assignment] ...
- Total syntax
Syntax> https://dev.mysql.com/doc/refman/8.0/en/select.html
SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] SQL_NO_CACHE [SQL_CALC_FOUND_ROWS] select_expr [, select_expr ...] [FROM table_references [PARTITION partition_list] [WHERE where_condition] [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]] [HAVING where_condition] [WINDOW window_name AS (window_spec) [, window_name AS (window_spec)] ...] [ORDER BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [INTO OUTFILE 'file_name' [CHARACTER SET charset_name] export_options | INTO DUMPFILE 'file_name' | INTO var_name [, var_name]] [FOR {UPDATE | SHARE} [OF tbl_name [, tbl_name] ...] [NOWAIT | SKIP LOCKED] | LOCK IN SHARE MODE]]
Syntax> https://dev.mysql.com/doc/refman/8.0/en/update.html
UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET assignment_list [WHERE where_condition] [ORDER BY ...] [LIMIT row_count] value: {expr | DEFAULT} assignment: col_name = value assignment_list: assignment [, assignment] ...
Syntax> https://dev.mysql.com/doc/refman/8.0/en/insert.html
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (partition_name [, partition_name] ...)] [(col_name [, col_name] ...)] {VALUES | VALUE} (value_list) [, (value_list)] ... [ON DUPLICATE KEY UPDATE assignment_list] INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (partition_name [, partition_name] ...)] SET assignment_list [ON DUPLICATE KEY UPDATE assignment_list] INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (partition_name [, partition_name] ...)] [(col_name [, col_name] ...)] SELECT ... [ON DUPLICATE KEY UPDATE assignment_list]
Syntax> https://dev.mysql.com/doc/refman/8.0/en/delete.html
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias] [PARTITION (partition_name [, partition_name] ...)] [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]
'프로...Linux' 카테고리의 다른 글
| ubuntu 18 신규 설치 (0) | 2023.12.22 |
|---|---|
| [Util-Script] mytop (0) | 2019.10.18 |
| [Threads] 속성 및 내용 정리 (0) | 2019.09.19 |
| [Process] Zombie (0) | 2019.05.28 |
| [Perl] 인코딩 변환 (0) | 2019.04.03 |