MariaDB - 데이터 검색 (날짜, 문자, 숫자, 집계)

Dev/Mysql(MariaDB)

MariaDB - 데이터 검색 (날짜, 문자, 숫자, 집계)

OK-가자 2021. 10. 1. 10:36

1.1 SELECT 구문의 기본문형

1.2 SELECT 구문 예제

전체 데이터 검색
- SELECT 뒤에 *를 기술함으로써 나타낼 수 있다
- 예제 : deaprtments 테이블의 모든 데이터를 출력.

SELECT *
    FROM departments;

특정 칼럼 검색
- SELECT 뒤에 컬럼을 콤마(,)로 구별해서 나열
- 예제 : employees 테이블에서 직원의 이름, 성별, 입사일을 출력

SELECT first_name, gender, hire_date
    FROM employees;

컬럼에 대한 ALIAS 부여
- 컬럼에 대한 ALIAS(별칭)을 부여해서 나타내는 칼럼의 HEADING을 변경할 수 있다.
- 예제 : employees 테이블에서 직원의 이름, 성별, 입사일을 출력
- SELECT first_name AS 이름, gender AS 성별, hire_date AS 입사일 FROM employees;
컬럼의 합성 (Concatenation)
- 문자열 결합함수 concat 사용
- 예제 : employees 테이블에서 직원의 전체이름, 성별, 입사일을 출력
- SELECT concat( first_name, ' ', last_name) AS 이름, gender AS 성별, hire_date AS 입사일 FROM employees;
중복행의 제거 (DISTINCT)
- 중복되는 행이 출력되는 경우, DISTINCT 키워드로 중복행을 제거
- 예제 1: titles 테이블에서 모든 직급의 이름 출력
- SELECT title FROM titles;
- 예제 2: titles 테이블에서 직급은 어떤 것들이 있는지 직급이름을 한 번씩만 출력
- SELECT DISTINCT title FROM titles;

2.1 ORDER BY 절

예제 : employees 테이블에서 직원의 전체이름, 성별, 입사일을 입사일 순으로 출력

SELECT concat( first_name, ' ', last_name) AS 이름,
           gender AS 성별, 
           hire_date AS 입사일
  FROM employees
 ORDER BY hire_date;

예제 : salaries 테이블에서 2001년 월급을 가장 높은순으로 사번, 월급순으로 출력

SELECT emp_no, salary
  FROM salaries
 WHERE from_date like '2001-%' 
 ORDER BY salary DESC

3.1 WHERE 절

산술비교 연산자
- 예제 : employees 테이블에서 1991년 이전에 입사한 직원의 이름,
- SELECT concat( first_name, ' ', last_name ) AS 이름, gender AS 성별, hire_date AS 입사일 FROM employees WHERE hire\_date < '1991-01-01'
- 성별, 입사일을 출력
- 예제 : employees 테이블에서 1989년 이전에 입사한 여직원의 이름,
- SELECT concat( first_name, ' ', last_name ) AS 이름, hire\_date AS 입사일 FROM employees WHERE gender='f' AND hire_date < '1989-01-01'
- 입사일을 출력
논리연산자
- 예제 : employees 테이블에서 1989년 이전에 입사한 여직원의 이름,3.2 SQL 비교 연산자
- SELECT concat( first_name, ' ', last_name ) AS 이름, hire\_date AS 입사일 FROM employees WHERE gender='f' AND hire_date < '1989-01-01'
- 입사일을 출력
IN
- 예제 : dept_emp 테이블에서 부서 번호가 d005나 d009에 속한 사원의
  사번, 부서번호 출력
- SELECT emp_no, dept_no FROM dept_emp WHERE dept_no in( 'd005', 'd009' )
LIKE
와일드 카드를 사용하여 특정 문자를 포함한 값에 대한 조건을 처리
% 는 0에서부터 여러 개의 문자열을 나타냄
_ 는 단 하나의 문자를 나타내는 와일드 카드
- 예제 : employees 테이블에서 1989년에 입사한 직원의 이름,과제: 예제를 산술비교 연산자를 사용한 SQL문으로 변경해 보세요
- SELECT concat( first_name, ' ', last_name ) AS 이름, hire\_date AS 입사일 FROM employees WHERE hire_date LIKE '1989%‘
- 입사일을 출력

4.1 문자형 함수

UCASE, UPPER

예제

mysql> SELECT UPPER('SEoul'), UCASE('seOUL');  
+----------------+----------------+  
| UPPER('SEoul') | UCASE('seOUL') |  
+----------------+----------------+  
| SEOUL | SEOUL |  
+----------------+----------------+

UCASE, UPPER

예제 : employees 테이블에서 last_name이 acton인 사원의 이름, 성별,
입사일 출력

SELECT concat(first_name, ' ',last_name), gender, hire\_date  
FROM employees  
WHERE last_name = 'ACTON'

결과는? UCASE나 UPPER 함수를 사용해서 결과가 나오도록 수정하세요.

LCASE, LOWER

예제

mysql> SELECT LOWER('SEoul'), LCASE('seOUL');  
+----------------+----------------+  
| LOWER('SEoul') | LCASE('seOUL') |  
+----------------+----------------+  
| seoul | seoul |  
+----------------+----------------+

substring

예제

mysql> SELECT SUBSTRING('Happy Day',3,2);  
+----------------+----------------+  
| SUBSTRING('Happy Day',3,2) |  
+----------------+----------------+  
| pp |  
+----------------+----------------+

substring
예제: employees 테이블에서 1989년에 입사한 직원의 이름, 입사일을 출력

SELECT concat( first_name, ' ', last_name ) AS 이름, hire_date AS 입사일 FROM employees WHERE substring( hire_date, 1, 4);

LPAD, RPAD

예제

mysql> SELECT LPAD('hi',5,'?'), LPAD('joe',7,'_');  
+------------------+-------------------+  
| LPAD('hi',5,'?') | LPAD('joe',7,'_') |  
+------------------+-------------------+  
| ???hi | ****joe |  
+------------------+-------------------+

mysql> SELECT RPAD('hi',5,'?'), RPAD('joe',7,'*');

예제 : salaries 테이블에서 2001년 급여가 70000불 이하의 직원만  
사번, 급여로 출력하되 급여는 10자리로 부족한 자리수는 \*로 표시

SELECT emp\_no, LPAD( cast(salary as char), 10, '\*')  
FROM salaries  
WHERE from\_date like '2001-%'  
AND salary < 70000

TRIM, LTRIM, RTRIM

예제  
mysql> SELECT LTRIM(' hello '), RTRIM(' hello ');  
+-------------------------------------+  
| LTRIM(' hello ') | RTRIM(' hello ') |  
+-------------------------------------+  
| 'hello ' | ' hello‘ |  
+-------------------------------------+

mysql> SELECT TRIM(' hi '),TRIM(BOTH 'x' FROM 'xxxhixxx');  
+----------------+--------------------------------+  
| TRIM(' hi ') | TRIM(BOTH 'x' FROM 'xxxhixxx') |  
+----------------+--------------------------------+  
| hi | hi |  
+----------------+--------------------------------+  
BOTH 대신에 LEADING, TRAILING으로 바꾸어 각각 테스트 해보세요.

예제 : salaries 테이블에 대한 LPAD 예제의 결과를 *생략하여 표시

SELECT emp\_no,  
TRIM( LEADING '_' FROM LPAD( cast(salary as char), 10, '_') ) FROM salaries  
WHERE from\_date like '2001-%'  
AND salary < 70000

4.2 숫자형 함수

ABS(x) : x의 절대값을 구한다.
예제

mysql> SELECT ABS(2), ABS(-2);

+--------+---------+  
| ABS(2) | ABS(-2) |  
+--------+---------+  
| 2 | 2 |  
+--------+---------+

MOD(n,m) % : n을 m으로 나눈 나머지 값을 출력한다.
예제

mysql> SELECT MOD(234,10), 253 % 7, MOD(29,9);  
+-------------+---------+-----------+  
| MOD(234,10) | 253 % 7 | MOD(29,9) |  
+-------------+---------+-----------+  
| 4 | 1 | 2 |  
+-------------+---------+-----------+

FLOOR(x) : x보다 크지 않은 가장 큰 정수를 반환한다. BIGINT로
자동 변환됨

예제

mysql> SELECT FLOOR(1.23), FLOOR(-1.23);  
+-------------+--------------+  
| FLOOR(1.23) | FLOOR(-1.23) |  
+-------------+--------------+  
| 1 | -2 |  
+-------------+--------------+

CEILING(x) : x보다 작지 않은 가장 작은 정수를 반환한다.

예제

mysql> SELECT CEILING(1.23), CEILING(-1.23);  
+---------------+----------------+  
| CEILING(1.23) | CEILING(-1.23) |  
+---------------+----------------+  
| 2 | -1 |  
+---------------+----------------+

ROUND(x) : x에 가장 근접한 정수를 반환한다.
예제

mysql> SELECT ROUND(-1.23), ROUND(-1.58), ROUND(1.58);

+--------------+--------------+-------------+  
| ROUND(-1.23) | ROUND(-1.58) | ROUND(1.58) |  
+--------------+--------------+-------------+  
| -1 | -2 | 2 |  
+--------------+--------------+-------------+

ROUND(x,d) : x값 중에서 소수점 d자리에 가장 근접한 수로 반환한다.

예제

mysql> SELECT ROUND(1.298,1),ROUND(1.298,0);  
+----------------+----------------+  
| ROUND(1.298,1) | ROUND(1.298,0) |  
+----------------+----------------+  
| 1.3 | 1 |  
+----------------+----------------+

POW(x,y) POWER(x,y) : x의 y 제곱 승을 반환한다.

예제

mysql> SELECT POW(2,2),POWER(2,-2);  
+----------+-------------+  
| POW(2,2) | POWER(2,-2) |  
+----------+-------------+  
| 4.000000 | 0.250000 |  
+----------+-------------+

SIGN(x) : x=음수이면 -1을, x=0이면 0을, x=양수이면 1을 출력한다.

예제

mysql> SELECT SIGN(-32), SIGN(0), SIGN(234);  
+-----------+---------+-----------+  
| SIGN(-32) | SIGN(0) | SIGN(234) |  
+-----------+---------+-----------+  
| -1        | 0        |        1 |  
+-----------+---------+-----------+

GREATEST(x,y,...) : 가장 큰 값을 반환한다.
예제

mysql> SELECT GREATEST(2,0),GREATEST(4.0,3.0,5.0),GREATEST("B","A","C");  
+---------------+-----------------------+-----------------------+  
| GREATEST(2,0) | GREATEST(4.0,3.0,5.0) | GREATEST("B","A","C") |  
+---------------+-----------------------+-----------------------+  
| 2             | 5.0                    | C                    |  
+---------------+-----------------------+-----------------------+

LEAST(x,y,...) : 가장 작은 값을 반환한다.
예제

mysql> SELECT LEAST(2,0),LEAST(34.0,3.0,5.0),LEAST("b","A","C");  
+------------+---------------------+--------------------+  
| LEAST(2,0) | LEAST(34.0,3.0,5.0) | LEAST("b","A","C") |  
+------------+---------------------+--------------------+  
| 0          | 3.0                  | A                 |  
+------------+---------------------+--------------------+

4.3 날짜형 함수

CURDATE(),CURRENT_DATE : 오늘 날짜를 YYYY-MM-DD나 YYYYMMDD 형식으로 반환한다.

예제

mysql> SELECT CURDATE(),CURRENT_DATE;
+------------+--------------+
| CURDATE()  |CURRENT_DATE |
+------------+--------------+
| 2007-11-12 | 2007-11-12   |
+------------+--------------+

CURTIME() CURRENT_TIME : 현재 시각을 HH:MM:SS나 HHMMSS 형식으예제
로 반환한다.

mysql> SELECT CURTIME(),CURRENT_TIME;
+-----------+--------------+
| CURTIME() | CURRENT_TIME|
+-----------+--------------+
| 14:31:51  | 14:31:51     |
+-----------+--------------+

NOW()
SYSDATE()
CURRENT_TIMESTAMP : 오늘 현시각을 YYYY-MM-DD HH:MM:SS 나 YYYYMMDDHHMMSS 형식으로 반환한다.

예제

mysql> SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP;
+---------------------+---------------------+---------------------+  
| NOW()               | SYSDATE()           | CURRENT_TIMESTAMP |
+---------------------+---------------------+---------------------+  
| 2007-11-12 09:33:38 | 2007-11-12 09:33:38 | 2007-11-12 09:33:38 |
+---------------------+---------------------+---------------------+

DATE_FORMAT(date,format) : 입력된 date를 format 형식으로 반환한다.
예제

mysql> SELECT DATE_FORMAT(CURDATE(),'%W %M %Y');  
+-----------------------------------+
| DATE_FORMAT(CURDATE(),'%W %M %Y') |
+-----------------------------------+  
| Monday November 2007 |
+-----------------------------------+

mysql> SELECT DATE_FORMAT(CURDATE(),'%Y.%m.%d');
+-----------------------------------+  
| DATE\_FORMAT(CURDATE(),'%Y.%m.%d') |
+-----------------------------------+  
| 2007.11.12 |
+-----------------------------------+

PERIOD_DIFF(p1,p2) : YYMM이나 YYYYMM으로 표기되는 p1과 p2의 차
이 개월을 반환 한다.

예제 : 각 직원들에 대해 직원이름과 근무개월수 출력

SELECT concat(first\_name, ' ', last\_name) AS name,  
PERIOD\_DIFF( DATE\_FORMAT(CURDATE(), '%Y%m'),  
DATE\_FORMAT(hire\_date, '%Y%m') )  
FROM employees

DATE_ADD(date,INTERVAL expr type)
DATE_SUB(date,INTERVAL expr type)
ADDDATE(date,INTERVAL expr type)
SUBDATE(date,INTERVAL expr type) :
날짜 date에 type 형식으로 지정한 expr값을 더하거나 뺀다. DATE_ADD()와 ADDDATE()는 같은 동작이고, DATE_SUB()와 SUBDATE()는 같은 의미이다.

예제 : 각 직원들은 입사 후 6개월이 지나면 근무평가를 한다. 각직원들에  
이름, 입사일, 최초 근무평가일은 언제인지 출력

4.4 CAST (형 변환)

CAST 함수는 type을 변경(지정)하는데 유용하다.

CAST 함수의 사용법 : CAST(expression AS type) 또는  
CONVERT(expression,type)

MySQL 타입: BINARY  
CHAR  
DATE  
DATETIME  
SIGNED {INTEGER}  
TIME  
UNSIGNED {INTEGER}

예제

mysql> select cast(now() as date);  
+---------------------+  
| cast(now() as date) |  
+---------------------+  
| 2003-09-25          |  
+---------------------+

1 row in set (0.00 sec)

mysql> select cast(1-2 as unsigned);
+-----------------------+
| cast(1-2 as unsigned) |
+-----------------------+
| 18446744073709551615 |
+-----------------------+

예제 (cont’d)

mysql> select cast(cast(1-2 as unsigned) as signed);
+---------------------------------------+
| cast(cast(1-2 as unsigned) as signed) |
+---------------------------------------+
| -1                                    |
+---------------------------------------+  
1 row in set (0.00 sec)

mysql> select cast(1 as unsigned) -2.0;
+--------------------------+
| cast(1 as unsigned) -2.0 |
+--------------------------+
| -1.0                     |
+--------------------------+

그룹함수

예제 : salaries 테이블에서 사번이 10060인 직원의 급여 평균과 총합계를 출력

SELECT AVG(salary) , SUM(salary)  
FROM salaries  
WHERE emp\_no = ‘10060’;

예제 : 이 예제 직원의 최저 임금을 받은 시기와 최대 임금을 받은 시기를 각 각 출력해보세요.

예제 : dept_emp 테이블에서 d008에 근무하는 인원수는

SELECT count(\*)  
FROM dept\_emp  
WHERE dept\_no = ‘d008’;