24 Feb 2020
Mysql 01
Mysql 01
DML(Data Manipulation Language)
- 데이터 조작어로 검색 및 수정하기 위한 수단 제공
Select, insert, update, delete, merge
DDL(Data Definition Language)
- 데이터 구조를 생성 변경, 삭제등의 기능을 제공
create, alter, drop, rename
DCL(Data Control Language)
- 데이터에 대한 권한 관리 및 트랜잭션 제어
grant, revoke
[DBMS 접속]
mysql -u [user id] -p
> mysql -u root -p
Database생성
[DB확인]
show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys |
| world |
+--------------------+
[DB 생성] (hellodb)
CREATE database hellodb;
[DB 사용] (hellodb)
use hellodb;
[현재 DB의 Table 확인]
show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| emp |
+-------------------+
[테이블 생성]
- VARCHAR : 문자열
- DATE : 날짜 형식
- -> 다중라인
CREATE TABLE [테이블 명](
[컬럼명 1] [데이터 타입],
[컬럼명 2] [데이터 타입],
...
[컬럼명 n] [데이터 타입]
);
CREATE TABLE emp(
id INT,
addr VARCHAR(30)
);
CREATE TABLE emp(
id INT,
name VARCHAR(25),
mailid VARCHAR(8),
start_date DATE, -- DATE는 날짜 형식
manager_id INT,
title VARCHAR(25),
dept_id INT,
salary INT,
commission_pct INT
);
[테이블 확인]
describe [table name];
> describe emp;
desc [table name]; -- desc로 줄여서 사용해도 된다.
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(25) | YES | | NULL | |
| mailid | varchar(8) | YES | | NULL | |
| start_date | date | YES | | NULL | |
| manager_id | int | YES | | NULL | |
| title | varchar(25) | YES | | NULL | |
| dept_id | int | YES | | NULL | |
| salary | int | YES | | NULL | |
| commission_pct | int | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+
[테이블 삭제]
DROP TABLE [table name];
> DROP TABLE emp;
> Query OK, 1 row affected
[데이터 삽입]
INSERT INTO [table name] VALUES (
'data', 'data', ... , 'data'
);
INSERT INTO emp VALUES(
1, '손예진', 'son123', '1990-03-03', NULL, '사장', 118, 5000, NULL
); -- 각 컬럼의 타입에 맞는 순서로 삽입
> Query OK, 1 row affected
[테이블 데이터 모두 조회]
SELECT * FROM [table name];
> SELECT * FROM emp;
+------+-----------+--------+------------+------------+--------+---------+--------+----------------+
| id | name | mailid | start_date | manager_id | title | dept_id | salary | commission_pct |
+------+-----------+--------+------------+------------+--------+---------+--------+----------------+
| 1 | 손예진 | son123 | 1990-03-03 | NULL | 사장 | 118 | 5000 | NULL |
+------+-----------+--------+------------+------------+--------+---------+--------+----------------+
[테이블 특정 ROW(RECORD) 조회]
SELECT * FROM [table name] WHERE [조건절];
> SELECT * FROM emp WHERE name = '전지영';
+------+-----------+----------+------------+------------+--------+---------+--------+----------------+
| id | name | mailid | start_date | manager_id | title | dept_id | salary | commission_pct |
+------+-----------+----------+------------+------------+--------+---------+--------+----------------+
| 6 | 전지영 | murguhar | 1993-05-23 | 2 | 과장 | 110 | 2400 | NULL |
+------+-----------+----------+------------+------------+--------+---------+--------+----------------+
[테이블 특정 컬럼 조회]
SELECT [컬럼명, 컬럼명, ...] FROM [테이블 명];
> SELECT name, start_date FROM emp;
+-----------+------------+
| name | start_date |
+-----------+------------+
| 손예진 | 1990-03-03 |
| 손명성 | 1990-03-08 |
| 이은선 | 1991-06-17 |
| 서의홍 | 1991-08-27 |
| 김심선 | 1992-01-02 |
| 전지영 | 1993-05-23 |
| 김정미 | 1994-01-18 |
| 신기웅 | 1993-08-27 |
| 이상헌 | 1994-03-24 |
| 최순철 | 1993-09-17 |
| 최정선 | 1993-05-21 |
| 안은경 | 1993-03-12 |
| 김석우 | 1995-03-15 |
| 박성규 | 1992-03-17 |
| 신종필 | 1991-01-04 |
| 이기철 | 1996-12-05 |
| 구원상 | 1994-06-02 |
| 서정주 | 1994-05-21 |
| 안창환 | 1997-03-02 |
| 윤정숙 | 1996-09-12 |
| 장현철 | 1996-07-24 |
| 장혜숙 | 1995-03-08 |
| 강연범 | 1994-06-11 |
| 오은경 | 1996-07-08 |
| 이용호 | 1998-12-07 |
+-----------+------------+
[NULL을 제외한 조회]
SELECT [컬럼명] FROM [테이블 명] WHERE [컬럼명] IS NOT NULL;
> SELECT name, commission_pct FROM emp WHERE commission_pct IS NOT NULL;
+-----------+----------------+
| name | commission_pct |
+-----------+----------------+
| 최정선 | 10 |
| 안은경 | 13 |
| 김석우 | 10 |
| 박성규 | 15 |
| 신종필 | 18 |
+-----------+----------------+
[정규 표현식]
- _ : 하나의 문자에 대응
- % : 문자열에 대응 (하나 이상의 문자)
- LIKE : ~와 같은의 의미로 사용
-- 성이 최씨인 사람의 이름과 부서ID 조회
> SELECT name, dept_id FROM emp WHERE name LIKE '최%';
+-----------+---------+
| name | dept_id |
+-----------+---------+
| 최순철 | 113 |
| 최정선 | 102 |
+-----------+---------+
-- 이름이 혜숙인 사람의 이름과 부서ID 조회
> SELECT name, dept_id FROM emp WHERE name LIKE '%혜숙';
+-----------+---------+
| name | dept_id |
+-----------+---------+
| 장혜숙 | 113 |
+-----------+---------+
-- 메일 id가 6자리인 사람의 이름과 mail id 조회
> SELECT name, mailid FROM emp WHERE mailid LIKE '______';
+-----------+--------+
| name | mailid |
+-----------+--------+
| 손예진 | son123 |
| 손명성 | 2xxsig |
| 최순철 | mhavel |
| 최정선 | cmagee |
| 신종필 | adumas |
| 구원상 | gsmith |
| 안창환 | vpatel |
| 장혜숙 | echang |
| 강연범 | rpatel |
| 오은경 | bdancs |
+-----------+--------+
[ALIAS (별칭) 활용]
- 컬럼명에 원하는 이름을 붙여 조회
- 컬럼의 이름이 바뀌는 것은 아니며 원하는 내용으로 조회하는 것.
- 단, 결과를 (재)사용 시 조회된 결과 이름으로
SELECT [컬럼명] AS [별칭] FROM [테이블 명];
> SELECT name AS 이름, dept_id AS 부서번호, salary AS 급여 FROM emp;
+-----------+--------------+--------+
| 이름 | 부서번호 | 급여 |
+-----------+--------------+--------+
| 손예진 | 118 | 5000 |
| 손명성 | 110 | 3000 |
| 이은선 | 102 | 3500 |
| 서의홍 | 101 | 3000 |
| 김심선 | 118 | 3200 |
| 전지영 | 110 | 2400 |
| 김정미 | 111 | 2400 |
| 신기웅 | 112 | 2100 |
| 이상헌 | 113 | 2300 |
| 최순철 | 113 | 2300 |
| 최정선 | 102 | 2450 |
| 안은경 | 103 | 2500 |
| 김석우 | 104 | 2650 |
| 박성규 | 105 | 2650 |
| 신종필 | 106 | 2400 |
| 이기철 | 110 | 2400 |
| 구원상 | 110 | 940 |
| 서정주 | 111 | 1200 |
| 안창환 | 111 | 795 |
| 윤정숙 | 112 | 750 |
| 장현철 | 112 | 850 |
| 장혜숙 | 113 | 800 |
| 강연범 | 106 | 795 |
| 오은경 | 113 | 860 |
| 이용호 | 113 | 1100 |
+-----------+--------------+--------+
[중복을 제거하여 조회]
SELECT DISTINCT [컬럼명] FROM [테이블 명];
> SELECT DISTINCT dept_id FROM emp;
-- 중복된 결과를 제외하고 부서 번호의 종류만 알고 싶다.
+---------+
| dept_id |
+---------+
| 118 |
| 110 |
| 102 |
| 101 |
| 111 |
| 112 |
| 113 |
| 103 |
| 104 |
| 105 |
| 106 |
+---------+
[정렬된 상태로 조회]
- ASC : 오름 차순, 디폴트 -> 생략 가능.
- DESC : 내림 차순
-- 입사일을 오름 차순으로 이름, 부서 번호, 입사일 조회
> SELECT name AS 이름, dept_id AS '부서 번호', start_date AS 입사일
FROM emp ORDER BY 입사일 ASC;
-- 공백을 포함해서 별칭을 지어주기 위해서는 ' '를 이용하여 묶어줘야 한다.
-- 그냥 하면 부서 번호 전체가 아닌 부서 만 인식을 하기 때문에 에러가 발생한다.
-- ERROR 1064 (42000): You have an error in your SQL syntax
+-----------+---------------+------------+
| 이름 | 부서 번호 | 입사일 |
+-----------+---------------+------------+
| 손예진 | 118 | 1990-03-03 |
| 손명성 | 110 | 1990-03-08 |
| 신종필 | 106 | 1991-01-04 |
| 이은선 | 102 | 1991-06-17 |
| 서의홍 | 101 | 1991-08-27 |
| 김심선 | 118 | 1992-01-02 |
| 박성규 | 105 | 1992-03-17 |
| 안은경 | 103 | 1993-03-12 |
| 최정선 | 102 | 1993-05-21 |
| 전지영 | 110 | 1993-05-23 |
| 신기웅 | 112 | 1993-08-27 |
| 최순철 | 113 | 1993-09-17 |
| 김정미 | 111 | 1994-01-18 |
| 이상헌 | 113 | 1994-03-24 |
| 서정주 | 111 | 1994-05-21 |
| 구원상 | 110 | 1994-06-02 |
| 강연범 | 106 | 1994-06-11 |
| 장혜숙 | 113 | 1995-03-08 |
| 김석우 | 104 | 1995-03-15 |
| 오은경 | 113 | 1996-07-08 |
| 장현철 | 112 | 1996-07-24 |
| 윤정숙 | 112 | 1996-09-12 |
| 이기철 | 110 | 1996-12-05 |
| 안창환 | 111 | 1997-03-02 |
| 이용호 | 113 | 1998-12-07 |
+-----------+---------------+------------+
- ’ ‘, “ “ : 모두 문자열로 인식 (자바와 다르다.)
-- Q1. emp 테이블에서 이름, 부서 번호, 입사일을 부서 번호 순으로 조회
> SELECT name AS 이름, dept_id AS '부서 번호', start_date AS 입사일
FROM emp ORDER BY dept_id;
+-----------+---------------+------------+
| 이름 | 부서 번호 | 입사일 |
+-----------+---------------+------------+
| 서의홍 | 101 | 1991-08-27 |
| 이은선 | 102 | 1991-06-17 |
| 최정선 | 102 | 1993-05-21 |
| 안은경 | 103 | 1993-03-12 |
| 김석우 | 104 | 1995-03-15 |
| 박성규 | 105 | 1992-03-17 |
| 신종필 | 106 | 1991-01-04 |
| 강연범 | 106 | 1994-06-11 |
| 손명성 | 110 | 1990-03-08 |
| 전지영 | 110 | 1993-05-23 |
| 이기철 | 110 | 1996-12-05 |
| 구원상 | 110 | 1994-06-02 |
| 김정미 | 111 | 1994-01-18 |
| 서정주 | 111 | 1994-05-21 |
| 안창환 | 111 | 1997-03-02 |
| 신기웅 | 112 | 1993-08-27 |
| 윤정숙 | 112 | 1996-09-12 |
| 장현철 | 112 | 1996-07-24 |
| 이상헌 | 113 | 1994-03-24 |
| 최순철 | 113 | 1993-09-17 |
| 장혜숙 | 113 | 1995-03-08 |
| 오은경 | 113 | 1996-07-08 |
| 이용호 | 113 | 1998-12-07 |
| 손예진 | 118 | 1990-03-03 |
| 김심선 | 118 | 1992-01-02 |
+-----------+---------------+------------+
-- Q2. emp 테이블에서 1994년에 입사한 사람의 이름, 입사일, 연봉을 많은 순으로 조회
-- 날짜도 문자열, WHERE절 후에 ORDER BY
> SELECT name AS 이름, start_date AS 입사일, salary AS 연봉
FROM emp WHERE start_date LIKE '1994%' ORDER BY salary DESC;
+-----------+------------+--------+
| 이름 | 입사일 | 연봉 |
+-----------+------------+--------+
| 김정미 | 1994-01-18 | 2400 |
| 이상헌 | 1994-03-24 | 2300 |
| 서정주 | 1994-05-21 | 1200 |
| 구원상 | 1994-06-02 | 940 |
| 강연범 | 1994-06-11 | 795 |
+-----------+------------+--------+
[조건절 (WHERE)]
- 산술 비교 연산자
- AND : 논리 AND 연산
- OR : 논리 OR 연산
-- 1995년 이후 입사한 직원의 이름과 입사일을 조회
> SELECT name AS 이름, start_date AS 입사일
FROM emp WHERE start_date >= '1995-01-01';
+-----------+------------+
| 이름 | 입사일 |
+-----------+------------+
| 김석우 | 1995-03-15 |
| 이기철 | 1996-12-05 |
| 안창환 | 1997-03-02 |
| 윤정숙 | 1996-09-12 |
| 장현철 | 1996-07-24 |
| 장혜숙 | 1995-03-08 |
| 오은경 | 1996-07-08 |
| 이용호 | 1998-12-07 |
+-----------+------------+
-- 1995년 이전에 입사한 직원의 이름, 입사일, 급여를 조회하되 급여가 2400 이상인 결과만 조회
> SELECT name AS 이름, start_date AS 입사일, salary AS 급여
FROM emp WHERE start_date < '1995-01-01' AND salary >= 2400;
+-----------+------------+--------+
| 이름 | 입사일 | 급여 |
+-----------+------------+--------+
| 손예진 | 1990-03-03 | 5000 |
| 손명성 | 1990-03-08 | 3000 |
| 이은선 | 1991-06-17 | 3500 |
| 서의홍 | 1991-08-27 | 3000 |
| 김심선 | 1992-01-02 | 3200 |
| 전지영 | 1993-05-23 | 2400 |
| 김정미 | 1994-01-18 | 2400 |
| 최정선 | 1993-05-21 | 2450 |
| 안은경 | 1993-03-12 | 2500 |
| 박성규 | 1992-03-17 | 2650 |
| 신종필 | 1991-01-04 | 2400 |
+-----------+------------+--------+
- IN : 입력 데이터가 포함된 조건
-- 부서 번호가 110, 112, 118에 속한 사원의 이름, 부서 번호, 메일 아이디를 조회
> SELECT name AS 이름, dept_id AS '부서 번호', mailid AS '이메일'
FROM emp WHERE dept_id IN (110, 112, 118);
> SELECT name AS 이름, dept_id AS '부서 번호', mailid AS '이메일'
FROM emp WHERE dept_id = 110 OR dept_id = 112 OR dept_id = 118;
+-----------+---------------+-----------+
| 이름 | 부서 번호 | 이메일 |
+-----------+---------------+-----------+
| 손예진 | 118 | son123 |
| 손명성 | 110 | 2xxsig |
| 김심선 | 118 | aropebur |
| 전지영 | 110 | murguhar |
| 신기웅 | 112 | bbiri |
| 이기철 | 110 | emaduro |
| 구원상 | 110 | gsmith |
| 윤정숙 | 112 | cnewman |
| 장현철 | 112 | amarkari |
+-----------+---------------+-----------+
- LIKE 연산자
- % : 여러개의 문자열
- _ : 하나의 문자 – 와일드 카드
-- 1995년에 입사한 직원의 이름, 연봉을 조회하세요.
> SELECT name AS 이름, salary AS 연봉 FROM emp WHERE start_date LIKE '1995%';
-- 산술 연산자로 변환
> SELECT name AS 이름, salary AS 연봉
FROM emp WHERE start_date >= '1995-01-01' AND start_date <= '1995-12-31';
+-----------+--------+
| 이름 | 연봉 |
+-----------+--------+
| 김석우 | 2650 |
| 장혜숙 | 800 |
+-----------+--------+
[MySQL 함수]
- UCASE, UPPER
SELECT UCASE('mysql'), LCASE('MYSQL');
-- 부서 번호가 110인 직원의 이름, 이메일을 대문자로 조회
> SELECT name AS 이름, UCASE(mailid) AS '이메일' FROM emp WHERE dept_id = 110;
+-----------+-----------+
| 이름 | 이메일 |
+-----------+-----------+
| 손명성 | 2XXSIG |
| 전지영 | MURGUHAR |
| 이기철 | EMADURO |
| 구원상 | GSMITH |
+-----------+-----------+
- substring : 특정 위치 기준으로 문자열을 잘라낼 때 활용
-- 'hello mysql' 문자열의 2번째부터 7개를 추출
> SELECT SUBSTRING('hello mysql', 2, 7);
+--------------------------------+
| SUBSTRING('hello mysql', 2, 7) |
+--------------------------------+
| ello my |
+--------------------------------+
-- 'hello mysql' 문자열의 3번째부터 5개 추출
> SELECT SUBSTRING('hello mysql', 3, 5);
+--------------------------------+
| SUBSTRING('hello mysql', 3, 5) |
+--------------------------------+
| llo m |
+--------------------------------+
- LPAD, RPAD : 특정 빈 자리를 채울 때 활용
-- 왼쪽 기준으로 5자리로 표현하되 빈 공간은 *로 출력
-- _ _ _ o h -> * * * o h
> SELECT LPAD('oh', 5, '*');
+--------------------+
| LPAD('oh', 5, '*') |
+--------------------+
| ***oh |
+--------------------+
-- 오른쪽으로 7자리를 표현하되 빈 공간은 @로 출력
-- y e s _ _ _ _ -> y e s @ @ @ @
> SELECT RPAD('yes', 7, '@');
+---------------------+
| RPAD('yes', 7, '@') |
+---------------------+
| yes@@@@ |
+---------------------+
-- 직원의 급여를 조회하되, 4자리 기준으로 빈 공간은 * 출력
> SELECT name AS 이름, LPAD(salary, 4, '*') AS 급여 FROM emp;
+-----------+--------+
| 이름 | 급여 |
+-----------+--------+
| 손예진 | 5000 |
| 손명성 | 3000 |
| 이은선 | 3500 |
| 서의홍 | 3000 |
| 김심선 | 3200 |
| 전지영 | 2400 |
| 김정미 | 2400 |
| 신기웅 | 2100 |
| 이상헌 | 2300 |
| 최순철 | 2300 |
| 최정선 | 2450 |
| 안은경 | 2500 |
| 김석우 | 2650 |
| 박성규 | 2650 |
| 신종필 | 2400 |
| 이기철 | 2400 |
| 구원상 | *940 |
| 서정주 | 1200 |
| 안창환 | *795 |
| 윤정숙 | *750 |
| 장현철 | *850 |
| 장혜숙 | *800 |
| 강연범 | *795 |
| 오은경 | *860 |
| 이용호 | 1100 |
+-----------+--------+
- TRIM, LTRIM, RTRIM : 공백(space) 제거하기 위한 함수
> SELECT LTRIM(' coffee ') AS L, RTRIM(' court ')AS R;
+-------------+-----------+
| L | R |
+-------------+-----------+
| coffee | court |
+-------------+-----------+
> SELECT name AS 이름, TRIM(TRAILING '*' FROM RPAD(salary, 10, '*')) AS 급여
FROM emp;
+-----------+--------+
| 이름 | 급여 |
+-----------+--------+
| 손예진 | 5000 |
| 손명성 | 3000 |
| 이은선 | 3500 |
| 서의홍 | 3000 |
| 김심선 | 3200 |
| 전지영 | 2400 |
| 김정미 | 2400 |
| 신기웅 | 2100 |
| 이상헌 | 2300 |
| 최순철 | 2300 |
| 최정선 | 2450 |
| 안은경 | 2500 |
| 김석우 | 2650 |
| 박성규 | 2650 |
| 신종필 | 2400 |
| 이기철 | 2400 |
| 구원상 | 940 |
| 서정주 | 1200 |
| 안창환 | 795 |
| 윤정숙 | 750 |
| 장현철 | 850 |
| 장혜숙 | 800 |
| 강연범 | 795 |
| 오은경 | 860 |
| 이용호 | 1100 |
+-----------+--------+
> SELECT name AS 이름, TRIM(LEADING '*' FROM RPAD(salary, 10, '*')) AS 급여
FROM emp;
+-----------+------------+
| 이름 | 급여 |
+-----------+------------+
| 손예진 | 5000****** |
| 손명성 | 3000****** |
| 이은선 | 3500****** |
| 서의홍 | 3000****** |
| 김심선 | 3200****** |
| 전지영 | 2400****** |
| 김정미 | 2400****** |
| 신기웅 | 2100****** |
| 이상헌 | 2300****** |
| 최순철 | 2300****** |
| 최정선 | 2450****** |
| 안은경 | 2500****** |
| 김석우 | 2650****** |
| 박성규 | 2650****** |
| 신종필 | 2400****** |
| 이기철 | 2400****** |
| 구원상 | 940******* |
| 서정주 | 1200****** |
| 안창환 | 795******* |
| 윤정숙 | 750******* |
| 장현철 | 850******* |
| 장혜숙 | 800******* |
| 강연범 | 795******* |
| 오은경 | 860******* |
| 이용호 | 1100****** |
+-----------+------------+
-- '*'로 빈 공간을 채운 RPAD로부터 '*'을 읽어라, 그리고 TRIM해라.
> SELECT TRIM(BOTH '*' FROM '***abc***') AS A,
TRIM(BOTH '*' FROM '***b*****') AS B,
TRIM(BOTH '*' FROM '12abcd***') AS C,
TRIM(BOTH '*' FROM '******def') AS D;
+------+------+--------+------+
| A | B | C | D |
+------+------+--------+------+
| abc | b | 12abcd | def |
+------+------+--------+------+
> SELECT TRIM(BOTH '*' FROM '***abc***') AS A,
TRIM(BOTH '*' FROM '*@*b**@**') AS B,
TRIM(BOTH '*' FROM '12abcd***') AS C,
TRIM(BOTH '*' FROM '******def') AS D;
+------+--------+--------+------+
| A | B | C | D |
+------+--------+--------+------+
| abc | @*b**@ | 12abcd | def |
+------+--------+--------+------+
-- 중간에 다른 문자가 나오면 읽는 걸 멈춘다.
- TRIM(LEADING …) : 왼쪽에서 읽어 들여라.
- TRIM(TRAILING …) : 오른쪽에서 읽어 들여라.
- TRIM(BOTH …) : 양쪽에서 읽어들여라.
- Mysql Interative Mode
- 테이블 없이 연산 결과만 확인(상호작용)
- ABS(n) : 절대값 함수
> SELECT ABS(100) AS '100', ABS(-100) AS '-100';
-- 100, -100이 숫자이기 때문에 컬럼명으로 쓸 수 없다.
+-----+------+
| 100 | -100 |
+-----+------+
| 100 | 100 |
+-----+------+
- MOD(n, m) : n을 m으로 나눈 나머지
> SELECT MOD(123, 4), MOD(27, 3), (27 % 3);
+-------------+------------+----------+
| MOD(123, 4) | MOD(27, 3) | (27 % 3) |
+-------------+------------+----------+
| 3 | 0 | 0 |
+-------------+------------+----------+
- FLOOR(n) : n보다 크지 않은 정수를 반환 (소수점 내림)
- CEILING(n) : n보다 작지 않은 정수를 반환(소수점 올림)
> SELECT FLOOR(3.14), FLOOR(-3.14), CEILING(3.14), CEILING(-3.14);
+-------------+--------------+---------------+----------------+
| FLOOR(3.14) | FLOOR(-3.14) | CEILING(3.14) | CEILING(-3.14) |
+-------------+--------------+---------------+----------------+
| 3 | -4 | 4 | -3 |
+-------------+--------------+---------------+----------------+
- ROUND(n) : n에 가까운 정수를 반환 (소수점 반올림)
> SELECT ROUND(3.14), ROUND(3.68), ROUND(-3.14), ROUND(-3.68), ROUND(3.5), ROUND(-3.5);
+-------------+-------------+--------------+--------------+------------+-------------+
| ROUND(3.14) | ROUND(3.68) | ROUND(-3.14) | ROUND(-3.68) | ROUND(3.5) | ROUND(-3.5) |
+-------------+-------------+--------------+--------------+------------+-------------+
| 3 | 4 | -3 | -4 | 4 | -4 |
+-------------+-------------+--------------+--------------+------------+-------------+
- SIGN(n) : 부호 정보를 반환
- 음수는 -1, 0은 0, 양수는 1
> SELECT SIGN(-3.14), SIGN(0), SIGN(3.14);
+-------------+---------+------------+
| SIGN(-3.14) | SIGN(0) | SIGN(3.14) |
+-------------+---------+------------+
| -1 | 0 | 1 |
+-------------+---------+------------+
- GREATEST(n, m, …) : 인자 중 가장 큰 값을 반환
- LEAST(n, m, …) : 인자 중 가장 작은 값을 반환
- 실수형이 포함된 경우 실수형으로 비교 및 반환
- 단, 문자를 비교를 하면 아스키코드로 변환하는 것이 아니라 단순히 맨 마지막 또는 맨 앞에 오는 문자를 출력
(아스키 코드의 크기 기준이 아니다.)
> SELECT GREATEST(3.14, 5, 2) AS 'GREATEST', LEAST(3, 5, 2) AS 'LEAST';
+----------+-------+
| GREATEST | LEAST |
+----------+-------+
| 5.00 | 2 |
+----------+-------+
> SELECT GREATEST('a', 'A', 'B') AS 'GREATEST', LEAST('a', 'A', 'B') AS 'LEAST';
+----------+-------+
| GREATEST | LEAST |
+----------+-------+
| B | a |
+----------+-------+
> SELECT GREATEST('a', 'A', 'b') AS 'GREATEST', LEAST('a', 'A', 'B') AS 'LEAST';
+----------+-------+
| GREATEST | LEAST |
+----------+-------+
| b | a |
+----------+-------+
> SELECT GREATEST('a', 'B', 'b') AS 'GREATEST', LEAST('a', 'A', 'B') AS 'LEAST';
+----------+-------+
| GREATEST | LEAST |
+----------+-------+
| b | a |
+----------+-------+
> SELECT GREATEST('a', 'A') AS 'GREATEST', GREATEST('A', 'a') AS 'GREATEST';
+----------+----------+
| GREATEST | GREATEST |
+----------+----------+
| A | a |
+----------+----------+
- CURDATE(), CURRENT_DATE() : 오늘 날짜를 반환
- CURTIME(), CURRENT_TIME() : 현재 시간을 반환
- SYSDATE(), NOW(), CURRENT_TIMESTAMP() : 오늘 날짜와 현재 시간을 반환
- DATE_FORMAT(DATE, FORMAT) : 입력된 날짜(DATE)를 원하는 양식(FORMAT) 반환
> SELECT CURDATE(), CURRENT_DATE();
+------------+----------------+
| CURDATE() | CURRENT_DATE() |
+------------+----------------+
| 2020-02-24 | 2020-02-24 |
+------------+----------------+
> SELECT CURTIME(), CURRENT_TIME();
+-----------+----------------+
| CURTIME() | CURRENT_TIME() |
+-----------+----------------+
| 15:01:40 | 15:01:40 |
+-----------+----------------+
> SELECT SYSDATE(), NOW(), CURRENT_TIMESTAMP();
+---------------------+---------------------+---------------------+
| SYSDATE() | NOW() | CURRENT_TIMESTAMP() |
+---------------------+---------------------+---------------------+
| 2020-02-24 15:02:59 | 2020-02-24 15:02:59 | 2020-02-24 15:02:59 |
+---------------------+---------------------+---------------------+
> SELECT DATE_FORMAT(CURDATE(), '%Y %M %W') AS '오늘의 날짜';
+----------------------+
| 오늘의 날짜 |
+----------------------+
| 2020 February Monday |
+----------------------+
> SELECT DATE_FORMAT(CURDATE(), '%y %m %w') AS '오늘의 날짜';
+------------------+
| 오늘의 날짜 |
+------------------+
| 20 02 1 |
+------------------+
> SELECT DATE_FORMAT(CURDATE(), '%y. %m. %w') AS '오늘의 날짜';
+------------------+
| 오늘의 날짜 |
+------------------+
| 20. 02. 1 |
+------------------+
- PERIOD_DIFF(m1, m2) : 두 날짜의 차이(달)를 반환
> SELECT name AS 이름, start_date AS 입사일, PERIOD_DIFF(DATE_FORMAT(CURDATE(), '%y%m'),
DATE_FORMAT(start_date, '%y%m')) AS 근속월수 FROM emp;
+-----------+------------+--------------+
| 이름 | 입사일 | 근속월수 |
+-----------+------------+--------------+
| 손예진 | 1990-03-03 | 359 |
| 손명성 | 1990-03-08 | 359 |
| 이은선 | 1991-06-17 | 344 |
| 서의홍 | 1991-08-27 | 342 |
| 김심선 | 1992-01-02 | 337 |
| 전지영 | 1993-05-23 | 321 |
| 김정미 | 1994-01-18 | 313 |
| 신기웅 | 1993-08-27 | 318 |
| 이상헌 | 1994-03-24 | 311 |
| 최순철 | 1993-09-17 | 317 |
| 최정선 | 1993-05-21 | 321 |
| 안은경 | 1993-03-12 | 323 |
| 김석우 | 1995-03-15 | 299 |
| 박성규 | 1992-03-17 | 335 |
| 신종필 | 1991-01-04 | 349 |
| 이기철 | 1996-12-05 | 278 |
| 구원상 | 1994-06-02 | 308 |
| 서정주 | 1994-05-21 | 309 |
| 안창환 | 1997-03-02 | 275 |
| 윤정숙 | 1996-09-12 | 281 |
| 장현철 | 1996-07-24 | 283 |
| 장혜숙 | 1995-03-08 | 299 |
| 강연범 | 1994-06-11 | 308 |
| 오은경 | 1996-07-08 | 283 |
| 이용호 | 1998-12-07 | 254 |
+-----------+------------+--------------+
- CAST(형 변환)
> SELECT CAST('123' as unsigned);
+-------------------------+
| CAST('123' as unsigned) |
+-------------------------+
| 123 |
+-------------------------+
> SELECT CAST('12ab' as unsigned);
-- 숫자가 아닌 것 까지 읽어 들이지만 숫자가 아니기 때문에 ab 반환 X
+--------------------------+
| CAST('12ab' as unsigned) |
+--------------------------+
| 12 |
+--------------------------+
> SELECT CAST('ab12' as unsigned);
-- 시작이 숫자가 아닌 문자로 되어있기 때문에 읽어들인 것이 없다고 판단하여 0이 반환
+--------------------------+
| CAST('ab12' as unsigned) |
+--------------------------+
| 0 |
+--------------------------+
-- 숫자가 아닌 데이터를 만날 때까지 읽어들여 변환
> SELECT CAST(1-2 as signed) AS 'signed', CAST(1-2 as unsigned) AS 'unsigned';
-- unsigned : 부호 비트도 크기의 비트로 사용.
-- 4바이트 기준이 아니라 8바이트 기준.
-- 기본 연산을 BIGINT(8byte) 기준으로 한다.
+--------+----------------------+
| signed | unsigned |
+--------+----------------------+
| -1 | 18446744073709551615 |
+--------+----------------------+
> SELECT CAST(SYSDATE() as DATE), SYSDATE();
+-------------------------+---------------------+
| CAST(SYSDATE() as DATE) | SYSDATE() |
+-------------------------+---------------------+
| 2020-02-24 | 2020-02-24 17:30:15 |
+-------------------------+---------------------+
- COUNT(expr) : NULL이 아닌 ROW의 개수 반환
- COUNT(DISTINCT, expr) : NULL이 아닌 ROW의 개수 반환(중복 제외)
- COUNT(*) : ROW의 개수 반환(NULL 포함)
- AVG(expr) : 평균 값을 반환
- MAX(expr) : 최대값을 반환
- MIN(expr) : 최소값을 반환
- SUM(expr) : 합계를 반환
-- 부서 번호 111인 부서의 급여 합계와 평균을 조회
> SELECT SUM(salary), AVG(salary) FROM emp WHERE dept_id = 111;
+-------------+-------------+
| SUM(salary) | AVG(salary) |
+-------------+-------------+
| 4395 | 1465.0000 |
+-------------+-------------+
-- 부서 번호가 111인 인원 수를 조회
> SELECT COUNT(*) FROM emp WHERE dept_id = 111;
+----------+
| COUNT(*) |
+----------+
| 3 |
+----------+
-- commission을 받는 직원 수 조회
> SELECT COUNT(commission_pct) FROM emp;
+-----------------------+
| COUNT(commission_pct) |
+-----------------------+
| 5 |
+-----------------------+
- SELECT 형태
SELECT (DISTINCT) [컬럼명] (ALIAS) [별칭]
FROM [테이블]
WHERE [조건절]
GROUP BY [컬럼]
HAVING [조건절]
ORDER BY [컬럼 or 표현식] (ASC, DESC);
- 수행 순서
- FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY
- 표 내용을 찾아가서 분류를 하고, 뽑고, 뽑은 후 정렬.
-- 급여가 1000 이상인 사원의 이름과 급여를 조회
> SELECT name AS '이름', salary AS '급여'
FROM emp
WHERE salary >= 1000
GROUP BY title
HAVING title = '사원';
+-----------+--------+
| 이름 | 급여 |
+-----------+--------+
| 이기철 | 2400 |
+-----------+--------+
-- 그룹화된 결과 중 title = '사원'
-- 조건에 맞는 데이터가 추출!(가장 위.)
-- 직책 이름으로 나눠져 있다.
-- 먼저, 급여가 1000 이상인 걸로 조건이 걸려있다.
-- 나누어 졌던 결과를 가지고 가장 위에 있는 결과를 가져온다.
-- group -> 사원인 애들을 묶고 난 후 가장 위에 있는 데이터를 가져온다.
-- 급여가 1000 이상이고, 직책이 사원인 데이터를 출력하고 싶다면 굳이 group이 필요가 없다.
-- where절을 사용해서 처리하면 된다.
-- 직급별 직급, 평균 연봉, 인원 수 조회 하되, 급여가 1000이상인 직원만 조회하세요
> SELECT title AS '직급', AVG(salary) AS '평균 급여', COUNT(*) AS '인원 수'
FROM emp
WHERE salary >= 1000
GROUP BY title;
+--------------------+---------------+------------+
| 직급 | 평균 급여 | 인원 수 |
+--------------------+---------------+------------+
| 사장 | 5000.0000 | 1 |
| 기획부장 | 3000.0000 | 1 |
| 영업부장 | 3500.0000 | 1 |
| 총무부장 | 3000.0000 | 1 |
| 인사부장 | 3200.0000 | 1 |
| 과장 | 2300.0000 | 5 |
| 영업대표이사 | 2530.0000 | 5 |
| 사원 | 1566.6667 | 3 |
+--------------------+---------------+------------+
Til next time,
Jeong-Yoon
at 00:00
