SQL(2)
2020-02-22
해당 Post는 DML에 대해 정리한 파일입니다.
DML (Data Manipulation Language)
데이터 조작어(Data Manipulation Language, DML)의 종류
데이터 조작어는 모두 동사로 시작합니다.
시작하는 동사에 따라서 다음과 같은 4가지 조작어가 있습니다.
SELECT – 검색
INSERT - 등록
UPDATE - 수정
DELETE - 삭제
SELECT 구문 예제(전체 데이터 검색)
전체 데이터 검색
SELECT 뒤에 * 를 기술함으로써 나타낼 수 있습니다.
> mysql> select * from department;
+--------+------------+----------+
| deptno | name | location |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
Desc(Describe의 약자)
특정 테이블에 어떤 컬럼이 있는지 조회하는 명령어 입니다.
mysql> desc department;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| deptno | int | NO | PRI | NULL | |
| name | varchar(14) | YES | | NULL | |
| location | varchar(13) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
SELECT 구문 예제(컬럼에 Alias부여하기)
컬럼에 대한 ALIAS(별칭)을 부여해서 나타내는 칼럼의 HEADING을 변경할 수 있습니다.
mysql> select deptno 부서번호 ,name 부서명 from department;
+----------+------------+
| 부서번호 | 부서명 |
+----------+------------+
| 10 | ACCOUNTING |
| 20 | RESEARCH |
| 30 | SALES |
| 40 | OPERATIONS |
+----------+------------+
4 rows in set (0.00 sec)
ALIAS(별칭)는 as로 표현이 가능하나 생략이 가능합니다.
mysql> select empno as 직원의사번, name as 이름, job as 직업 from employee;
+------------+--------+-----------+
| 직원의사번 | 이름 | 직업 |
+------------+--------+-----------+
| 7369 | SMITH | CLERK |
| 7499 | ALLEN | SALESMAN |
| 7521 | WARD | SALESMAN |
| 7566 | JONES | MANAGER |
| 7654 | MARTIN | SALESMAN |
| 7698 | BLAKE | MANAGER |
| 7782 | CLARK | MANAGER |
| 7788 | SCOTT | ANALYST |
| 7839 | KING | PRESIDENT |
| 7844 | TURNER | SALESMAN |
| 7876 | ADAMS | CLERK |
| 7900 | JAMES | CLERK |
| 7902 | FORD | ANALYST |
| 7934 | MILLER | CLERK |
+------------+--------+-----------+
14 rows in set (0.00 sec)
concat으로 구문 붙이기
concat을 이용하면 여러개의 컬럼을 붙여서 사용이 가능합니다.
mysql> select concat(empno, '-', deptno)as '사번-부서번호' from employee;
+---------------+
| 사번-부서번호 |
+---------------+
| 7782-10 |
| 7839-10 |
| 7934-10 |
| 7369-20 |
| 7566-20 |
| 7788-20 |
| 7876-20 |
| 7902-20 |
| 7499-30 |
| 7521-30 |
| 7654-30 |
| 7698-30 |
| 7844-30 |
| 7900-30 |
+---------------+
14 rows in set (0.00 sec)
SELECT 구문 예제(중복행의 제거)
중복되는 행이 출력되는 경우, DISTINCT 키워드로 중복행을 제거
mysql> select deptno from employee;
+--------+
| deptno |
+--------+
| 10 |
| 10 |
| 10 |
| 20 |
| 20 |
| 20 |
| 20 |
| 20 |
| 30 |
| 30 |
| 30 |
| 30 |
| 30 |
| 30 |
+--------+
14 rows in set (0.00 sec)
위에서 distinct를 추가한 모습입니다.
mysql> select distinct deptno from employee;
+--------+
| deptno |
+--------+
| 10 |
| 20 |
| 30 |
+--------+
3 rows in set (0.00 sec)
SELECT 구문 예제(정렬하기)
ORDER BY 절을 이용해서 정렬이 가능합니다.
mysql> select empno, name from employee;
+-------+--------+
| empno | name |
+-------+--------+
| 7369 | SMITH |
| 7499 | ALLEN |
| 7521 | WARD |
| 7566 | JONES |
| 7654 | MARTIN |
| 7698 | BLAKE |
| 7782 | CLARK |
| 7788 | SCOTT |
| 7839 | KING |
| 7844 | TURNER |
| 7876 | ADAMS |
| 7900 | JAMES |
| 7902 | FORD |
| 7934 | MILLER |
+-------+--------+
14 rows in set (0.00 sec)
기존 테이블에서 이름을 기준으로 정렬한다면 아래와 같습니다.
mysql> select empno, name from employee order by name;
+-------+--------+
| empno | name |
+-------+--------+
| 7876 | ADAMS |
| 7499 | ALLEN |
| 7698 | BLAKE |
| 7782 | CLARK |
| 7902 | FORD |
| 7900 | JAMES |
| 7566 | JONES |
| 7839 | KING |
| 7654 | MARTIN |
| 7934 | MILLER |
| 7788 | SCOTT |
| 7369 | SMITH |
| 7844 | TURNER |
| 7521 | WARD |
+-------+--------+
14 rows in set (0.00 sec)
desc를 이용하면 역순으로 정렬도 가능합니다.
mysql> select empno, name from employee order by name desc;
+-------+--------+
| empno | name |
+-------+--------+
| 7521 | WARD |
| 7844 | TURNER |
| 7369 | SMITH |
| 7788 | SCOTT |
| 7934 | MILLER |
| 7654 | MARTIN |
| 7839 | KING |
| 7566 | JONES |
| 7900 | JAMES |
| 7902 | FORD |
| 7782 | CLARK |
| 7698 | BLAKE |
| 7499 | ALLEN |
| 7876 | ADAMS |
+-------+--------+
14 rows in set (0.00 sec)
SELECT 구문 예제(특정 행 검색- where절)
where문은 프로그래밍에서 if문과 같다고 생각하면 됩니다.
mysql> select * from employee;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | name | job | boss | hiredate | salary | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1983-01-12 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
mysql> select * from employee where empno = 7934;
+-------+--------+-------+------+------------+---------+------+--------+
| empno | name | job | boss | hiredate | salary | comm | deptno |
+-------+--------+-------+------+------------+---------+------+--------+
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-------+------+------------+---------+------+--------+
1 row in set (0.00 sec)
mysql> select * from employee where deptno = 10;
+-------+--------+-----------+------+------------+---------+------+--------+
| empno | name | job | boss | hiredate | salary | comm | deptno |
+-------+--------+-----------+------+------------+---------+------+--------+
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+------+--------+
3 rows in set (0.00 sec)
SELECT에서 글자 검색
설명보다는 예시가 이해하기 편합니다.
mysql> select * from employee where name like 'A%';
+-------+-------+----------+------+------------+---------+--------+--------+
| empno | name | job | boss | hiredate | salary | comm | deptno |
+-------+-------+----------+------+------------+---------+--------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1983-01-12 | 1100.00 | NULL | 20 |
+-------+-------+----------+------+------------+---------+--------+--------+
2 rows in set (0.00 sec)
mysql> select * from employee where name like '%A%';
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | name | job | boss | hiredate | salary | comm | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7876 | ADAMS | CLERK | 7788 | 1983-01-12 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
+-------+--------+----------+------+------------+---------+---------+--------+
7 rows in set (0.00 sec)
mysql> select * from employee where name like '_A%';
+-------+--------+----------+------+------------+---------+---------+--------+
| empno | name | job | boss | hiredate | salary | comm | deptno |
+-------+--------+----------+------+------------+---------+---------+--------+
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
+-------+--------+----------+------+------------+---------+---------+--------+
3 rows in set (0.00 sec)
% 는 이후 단어들에 대해서 모든 조건을 검색한다.
_는 ‘_A’라면 A앞에 단 한글자만 올 수 있도록 조건을 정한다.
SELECT 구문 예제(함수의 사용)
mysql> select upper('seoUL'), ucase('Seoul'), lower('seOUL'), lcase('SeOul');
+----------------+----------------+----------------+----------------+
| upper('seoUL') | ucase('Seoul') | lower('seOUL') | lcase('SeOul') |
+----------------+----------------+----------------+----------------+
| SEOUL | SEOUL | seoul | seoul |
+----------------+----------------+----------------+----------------+
1 row in set (0.00 sec)
upper, ucase = 대문자로 변경
lower, lcase = 소문자로 변경
mysql> select substring('happy Day',4,4);
+----------------------------+
| substring('happy Day',4,4) |
+----------------------------+
| py D |
+----------------------------+
1 row in set (0.00 sec)
substring은 단어 추출하는 것을 도와줍니다.
mysql> select lpad(name, 10,'+'),rpad(name,10,'*') from employee;
+--------------------+-------------------+
| lpad(name, 10,'+') | rpad(name,10,'*') |
+--------------------+-------------------+
| +++++SMITH | SMITH***** |
| +++++ALLEN | ALLEN***** |
| ++++++WARD | WARD****** |
| +++++JONES | JONES***** |
| ++++MARTIN | MARTIN**** |
| +++++BLAKE | BLAKE***** |
| +++++CLARK | CLARK***** |
| +++++SCOTT | SCOTT***** |
| ++++++KING | KING****** |
| ++++TURNER | TURNER**** |
| +++++ADAMS | ADAMS***** |
| +++++JAMES | JAMES***** |
| ++++++FORD | FORD****** |
| ++++MILLER | MILLER**** |
+--------------------+-------------------+
14 rows in set (0.00 sec)
lpad, rpad는 좌측 우측의 글자를 고정시켜서 ‘내용’을 빈 공간에 추가시켜서 테이블로 보여줍니다.
이외 다른 함수들
- FLOOR(x) : x보다 크지 않은 가장 큰 정수를 반환합니다. BIGINT로 자동 변환합니다.
- CEILING(x) : x보다 작지 않은 가장 작은 정수를 반환합니다.
- ROUND(x) : x에 가장 근접한 정수를 반환합니다.
- POW(x,y) POWER(x,y) : x의 y 제곱 승을 반환합니다.
- GREATEST(x,y,…) : 가장 큰 값을 반환합니다.
- LEAST(x,y,…) : 가장 작은 값을 반환합니다.
- CURDATE(),CURRENT_DATE : 오늘 날짜를 YYYY-MM-DD나 YYYYMMDD 형식으로 반환합니다.
- CURTIME(), CURRENT_TIME : 현재 시각을 HH:MM:SS나 HHMMSS 형식으로 반환합니다.
- NOW(), SYSDATE() , CURRENT_TIMESTAMP : 오늘 현시각을 YYYY-MM-DD HH:MM:SS나 YYYYMMDDHHMMSS 형식으로 반환합니다.
- DATE_FORMAT(date,format) : 입력된 date를 format 형식으로 반환합니다.
- PERIOD_DIFF(p1,p2) : YYMM이나 YYYYMM으로 표기되는 p1과 p2의 차이 개월을 반환합니다.
SELECT 구문(그룹함수)
mysql> select * from employee;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | name | job | boss | hiredate | salary | comm | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1983-01-12 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
mysql> select avg(salary), sum(salary) from employee where deptno=30;
+-------------+-------------+
| avg(salary) | sum(salary) |
+-------------+-------------+
| 1566.666667 | 9400.00 |
+-------------+-------------+
1 row in set (0.00 sec)
mysql> select avg(salary), sum(salary) from employee group by deptno;
+-------------+-------------+
| avg(salary) | sum(salary) |
+-------------+-------------+
| 2916.666667 | 8750.00 |
| 2175.000000 | 10875.00 |
| 1566.666667 | 9400.00 |
+-------------+-------------+
3 rows in set (0.00 sec)
mysql> select deptno,avg(salary), sum(salary) from employee group by deptno;
+--------+-------------+-------------+
| deptno | avg(salary) | sum(salary) |
+--------+-------------+-------------+
| 10 | 2916.666667 | 8750.00 |
| 20 | 2175.000000 | 10875.00 |
| 30 | 1566.666667 | 9400.00 |
+--------+-------------+-------------+
3 rows in set (0.00 sec)
group 함수를 이용하면 원하는 결과를 하나씩이 아닌 한번에 보여줄 수 있습니다.
데이터 삭제 실습(DELETE문)
delete from ROLE where role_id = 200;(예시 날아감)
- where절을 안줄 경우 모든 데이터가 삭제되니 조심해야 합니다.