본문 바로가기
프로...Linux

[mysql] SQL 예제

by 크크다스 2019. 1. 17.
반응형

; Font :  monospace / 10pt


참고>  http://tcpschool.com/mysql/intro


https://dev.mysql.com/doc/refman/8.0/en/select.html



----------------------------------------------------------------------------------------------------------------------------------------------------------

[UPDATE....SET]


- 실행순서는 순차적이라서 바로전의 결과를 입력값으로 사용할 수 있다.


DB값> A=10, B=20, C=300


UPDATE ... SET A=B, B=C, C=A


A = Old B

B = Old C

C = old B <=== A가 아니라 .... 이유> 앞에 A는 이미 B로 변경되었으므로 참조시점에는 old B 가 됨.




----------------------------------------------------------------------------------------------------------------------------------------------------------

[수식 예]


- 실행순서는 순차적이라서 바로전의 결과를 입력값으로 사용할 수 있다.


-1 % 3 => -1

09     => 9  ... 8진수가 아님


X IN (...) : SELECT 가능

NOT EXIST (...) : SELECT 가능


----------------------------------------------------------------------------------------------------------------------------------------------------------

[각 종 조건들]


- CONCAT : 문자 결합


SET @id := (SELECT id FROM YOUR_TABLE ORDER BY id DESC LIMIT 1);

INSERT INTO YOUR_TABLE (username) VALUES(

   CONCAT("ADMIN", @id + 1)

);



----------------------------------------------------------------------------------------------------------------------------------------------------------

[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 |       |



[INSERT]


- 새로운 Column이 추가된 후에 기존의 Insert값을 활용할 때


맨 뒤에 새 컬럼 추가시>


기존 INSERT문에서 COLUMN을 지정하지 않았다면 null로 자동 입력된다.


맨 뒤가 아닌 곳에 새 컬럼 추가시>


해당 위치에 명시적으로 null,을 추가한다.




 ---------------------------------------------------------------------------------------------------------------------------------------------------------


[각 종 조건들]


- 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]



- COLUMN NAME 확인

information_schema

desc

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 |

+---------------------+----------------------+


Multiful columns : 차순을 각각 정해 줘야 한다.


select D_DATE_START, D_TIME_START from R_AP_STAT_TRAFFIC order by D_DATE_START desc , D_TIME_START desc limit 10;
+--------------+--------------+
| D_DATE_START | D_TIME_START |
+--------------+--------------+
| 2019-12-17   | 16:30:00     |
| 2019-12-17   | 16:30:00     |
| 2019-12-17   | 16:30:00     |
| 2019-12-17   | 16:30:00     |
| 2019-12-17   | 16:30:00     |
| 2019-12-17   | 16:30:00     |
| 2019-12-17   | 16:30:00     |
| 2019-12-17   | 16:30:00     |
| 2019-12-17   | 16:30:00     |
| 2019-12-17   | 16:30:00     |
+--------------+--------------+




- Backup & Restore DB


mysql

show databases;

use guidb;

show tables;



mysqldump 


[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



Examples


select count(*) from R_FLOAT_INF order by D_DETECTED desc limit 3;

// replace() : AP123 -> 123 -> ord
// CAST() : type casting
select CAST(replace(node_name, 'AP', '') AS UNSIGNED) as ord FROM guidb.nodelist order by ord;

// date_format() : Set Format
+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| start_time    | datetime         | YES  |     | NULL    |                |
+---------------+------------------+------+-----+---------+----------------+
select date_format(start_time, '%Y-%m-%d %H:%i:%s') as date_time

// AS : 참조 필드명 변경
// GROUP BY : 
select rf_id AS id from guidb.apterminal where ap=? GROUP BY rf_id order by rf_id;

// 상수 필드
select 'All-AP' as ap_label, .........

select sum(assoc_req) as assoc_req 
FROM stat_call_count_hourly t1  
WHERE date_time >= '2019-03-01 23:39:00' 
GROUP BY date_label 
ORDER BY date_time DESC
LIMIT  100, 100;



함수들



// 통계
count()
sum()

// 문자열
replace(필드명, 찾는것, 바꿀것)

// 시간
- 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")


- 분할

DAYOFWEEK(date)   : 요일을 정수로 반환(1=일요일, 7=토요일)

DAYOFMONTH(date): 월의 몇번째 날인지를 정수로 반환

DAYOFYEAR(date)   : 년중 몇번째 날인지를 정수로 반환

MONTH(date)           : 월을 1∼12의 정수로 반환

DAYNAME(date)       : 요일 이름을 문자로 반환

MONTHNAME(date)  : 월 이름을 문자로 반환

YEAR(date)              :  년도를 4자리 정수로 반환


// DB 관련
LAST_INSERT_ID() 

// 기타
CAST()



유용한 예





// 단일 컬럼이 중복되는 라인 찾기
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;

// 다중 컬럼이 중복되는 라인 찾기

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 결과를 이용한 예
select  userid, point as 국어, (select point from T_영어 where ID=t1.userid) AS 영어 FROM T_국어 t1 
// 사용 예> 영어, 국어 점수를 각각의 테이블로 관리할 때 같은 ID별로 국어, 영어 점수를 한꺼번에 찾을 때



중복되지 않을 경우에만 INSERT
INSERT INTO dev_usage 
SELECT * FROM 
( SELECT 'AP40' as MN , '84:2E:27:AF:56:75' as MAC ) as temp 
WHERE 
NOT EXISTS ( SELECT MAC FROM dev_usage WHERE MAC = '84:2E:27:AF:56:75' ) 
LIMIT 1 ;





참고링크 : Oracle SQL 강좌 


GROUP BY절

  • - GROUP BY 절은 데이터들을 원하는 그룹으로 나눌 수 있다.
  • - 나누고자 하는 그룹의 컬럼명을 SELECT절과 GROUP BY절 뒤에 추가하면 된다.
  • - 집계함수와 함께 사용되는 상수는 GROUP BY 절에 추가하지 않아도 된다. (개발자 분들이 많이 실수 함)
  • - 아래는 집계 함수와 상수가 함께 SELECT 절에 사용되는 예이다.
?
1
2
3
4
5
6
7
8
9
10
11
12
-- 부서별 사원수 조회
SELECT '2005년' year, deptno 부서번호, COUNT(*) 사원수
  FROM emp
 GROUP BY deptno
 ORDER BY COUNT(*) DESC;
 
 
YEAR     부서번호     사원수
------ ---------- ----------
2005년         30          6
2005년         20          5
2005년         10          3

아래 예제는 부서별로 그룹하여 부서번호, 인원수, 급여의 평균, 급여의 합을 조회하는 예제이다.

?
1
2
3
4
5
6
7
8
9
10
11
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

아래 예제는 업무별로 그룹하여 업무, 인원수, 평균 급여액, 최고 급여액, 최저 급여액 및 합계를 조회하는 예제이다.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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는 말 그대로 그룹을 나누는 역할을 한다.
?
1
2
3
4
5
6
7
8
9
10
11
-- GROUP BY를 이용한 부서번호 조회 예
SELECT deptno
  FROM emp
 GROUP BY deptno;
 
 
DEPTNO
------
    30
    20
    10

DISTINCT와 GROUP BY절

  • - DISTINCT와 GROUP BY 개념에 대해서 좀 더 이해를 해보자.
  • - DISTINCT는 주로 UNIQUE(중복을 제거)한 컬럼이나 레코드를 조회하는 경우 사용한다.
  • - GROUP BY는 데이터를 그룹핑해서 그 결과를 가져오는 경우 사용한다.
  • - 하지만 두 작업은 조금만 생각해보면 동일한 형태의 작업이라는 것을 쉽게 알 수 있으며, 일부 작업의 경우 DISTINCT로 동시에 GROUP BY로도 처리될 수 있는 쿼리들이 있다.
  • - 두 기능 모두 Oracle9i까지는 sort를 이용하여 데이터를 만들었지만, Oracle10g 부터는 모두 Hash를 이용하여 처리한다.
  • - 그래서 DISTINCT를 사용해야 할지, GROUP BY를 사용해서 데이터를 조회하는 것이 좋을지 고민되는 경우들이 가끔 있다.

아래의 예제는 동일한 결과를 반환한다.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
-- 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 절을 사용 하도록 하자

?
1
2
3
4
5
6
7
8
9
10
11
-- 아래와 같은 기능은 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절과 함께 사용이 된다.

아래 예제는 사원수가 다섯 명이 넘는 부서와 사원수를 조회하는 예제이다.

?
1
2
3
4
5
6
7
8
9
10
SELECT b.dname, COUNT(a.empno) "사원수"
  FROM emp a, dept b
 WHERE a.deptno = b.deptno
 GROUP BY dname
HAVING COUNT(a.empno) > 5;
 
 
DNAME          사원수
------------ -------
SALES              6

아래 예제는 전체 월급이 5000을 초과하는 JOB에 대해서 JOB과 월급여 합계를 조회하는 예이다. 단 판매원(SALES)은 제외하고 월 급여 합계로 내림차순 정렬하였다.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
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


반응형
저작자표시 비영리 변경금지

'프로...Linux' 카테고리의 다른 글

[SCRIPT] 쉘 스크립트 예제  (0) 2019.02.02
[ssh] ssh 접속만 허용하고 scp나 리모트 명령은 막는 방법  (0) 2019.01.23
[svn] multiple svn server 구동  (0) 2019.01.16
[Util] sysdig - 사용자 감시  (0) 2019.01.15
[TS/Make] "aclocal-1.11: command not found"  (0) 2019.01.14

관련글

티스토리툴바