database[트랜잭션/commit/rollback]

트랜잭션
-세부업무(DML : insert, upadte, delete)가 모두 성공해야 전체를 성공으로 간주하는 논리적
업무수행 단위(원자성)

트랜잭션 수행 후 commit 하지 않으면 나중에 전부 rollback할 수 있다.

commit을 일종의 게임세이브라고 생각해도 될 것 같다.

단, 실수를 해도 커밋을 해버리면 되돌릴 수 없으니 확실할 때만 해야 될 듯 하다.
====================

●데이터베이스 목록 조회
=show datebases;

●원하는 데이터베이스 선택
=use DB명;

●선택한 데이터 베이스에 어떤 테이블이 있는지 조회
=show tables;

●이름에 A가 들어가는 사원명, 급여 출력
=select ename, sal from emp where ename LIKE '%A%';

●이름이 A로 시작하는 사원명, 급여 출력
=select ename, sal from emp where ename LIKE 'A%';

[ANSI SQL에서 지원하는 집계함수]
●총 갯수  count
=select count(*) from emp;
+----------+
| count(*)  |
+----------+
|       13   |
+----------+
=select count(ename) as 총사원수 from emp;
// as 뒤에 넣는 문장을 컬럼명으로 지정할 수 있다.
+----------+
| 총사원수 |
+----------+
|       13   |
+----------+

●평균  AVG
=select avg(sal) as 급여평균 from emp;
+-----------+
| 급여평균  |
+-----------+
| 2132.6923 |
+-----------+

●합  SUM
=select sum(sal) as 급여합 from emp;
+--------+
| 급여합  |
+--------+
|  27725  |
+--------+

●최댓값  max
=mysql> select max(sal) as 최고월급 from emp;
+----------+
| 최고월급  |
+----------+
|     5000  |
+----------+

●최솟값  min
=select min(sal) as 최소월급 from emp;
+----------+
| 최소월급  |
+----------+
|      800   |
+----------+

●최소월급을 받는 사원의 월급과 사원명 출력
=select ename, sal from emp where sal=(select min(sal) from emp);
//subquery문 : 쿼리문 안에 또 다른 쿼리문을 포함시키는 것
이때 서브쿼리문은 소괄호로 감싸야한다. 소괄호 안에 있는 쿼리문을 먼저 수행해야 하기 때문!
+-------+------+
| ename | sal  |
+-------+------+
| SMITH |  800 |
+-------+------+

●ALLEN과 부서가 같은 사원들의 이름, 입사일, 부서번호 출력
=select ename, hiredate, empno from emp where deptno=(select deptno from emp where ename='ALLEN');

●이름에 T가 들어가는 사원들 급여의 합
=select sum(sal) as 급여합 from emp where ename LIKE '%T%';

●최대급여를 받는 사원보다 입사일이 늦은 사원의 이름, 입사일을 출력 단, 입사일을 기준으로 오름차순으로 정렬
=select ename, hiredate from emp where hiredate>(select hiredate from emp where sal=(select max(sal) from emp)) order by hiredate asc;
+-------+---------------------+
| ename | hiredate                  |
+-------+---------------------+
| JAMES | 1981-12-03 00:00:00  |
| FORD  | 1981-12-03 00:00:00  |
| SCOTT | 1987-04-19 00:00:00  |
| ADAMS | 1987-05-23 00:00:00 |
+-------+---------------------+

●length 함수
select ename as 사원명, length(ename) as 이름길이 from emp;
+--------+----------+
| 사원명 | 이름길이 |
+--------+----------+
| SMITH    |        5 |
| ALLEN    |        5 |
| WARD    |        4 |
| JONES    |        5 |
| MARTIN  |        6 |
| BLAKE     |        5 |
| CLARK    |        5 |
| SCOTT    |        5 |
| KING      |        4 |
| TURNER  |        6 |
| ADAMS  |        5 |
| JAMES   |        5 |
| FORD    |        4 |
+--------+----------+

●사원의 원래이름과 소문자로 변환한 이름 출력(LCASE or LOWER)
select ename, lower(ename) from emp;
+--------+--------------+
| ename  | lower(ename) |
+--------+--------------+
| 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         |
+--------+--------------+

●사원의 이름 중 KING이라는 사원의 이름을 왕으로 출력(replace 함수)
select replace(ename, 'KING', 'KINGDOM') from emp;
형식: replace(조사대상, '어떤 문자열을?', '어떻게 바꿀건데?')
+-----------------------------------+
| replace(ename, 'KING', 'KINGDOM') |
+-----------------------------------+
| SMITH                              |
| ALLEN                              |
| WARD                              |
| JONES                              |
| MARTIN                            |
| BLAKE                              |
| CLARK                              |
| SCOTT                              |
| KINGDOM                          |
| TURNER                            |
| ADAMS                             |
| JAMES                              |
| FORD                               |
+-----------------------------------+

●사원명 앞에 name: 붙게 이름을 출력(concat함수)
형식 : concat('문자열1', '문자열2', '문자열3'...)

 select concat('name:', ename) from emp;
+------------------------+
| concat('name:', ename) |
+------------------------+
| name:SMITH             |
| name:ALLEN              |
| name:WARD              |
| name:JONES              |
| name:MARTIN            |
| name:BLAKE              |
| name:CLARK             |
| name:SCOTT             |
| name:KING               |
| name:TURNER           |
| name:ADAMS            |
| name:JAMES             |
| name:FORD              |
+------------------------+