[MySQL] DML(Data Manipulation Language)

데이터 조작어(DML)에는 다음의 4가지가 있다.

• SELECT – 검색
• INSERT - 등록
• UPDATE - 수정
• DELETE - 삭제

SELECT

예제) departments 테이블의 모든 데이터를 출력하시오.

select * from departments;

어떤 column이 있는지는 "desc"로 확인!

desc departments;

예제) employee 테이블에서 직원의 사번(empno), 이름(name), 직업(job)을 출력하시오.

select empno, name, job from employee;

예제) employee 테이블에서 직원의 사번(empno), 이름(name), 직업(job)을 alias를 부여하여 출력하시오.

select empno 사번, name 이름, job 직업 from employee;

select empno as 사번, name as 이름, job as 직업 from employee;

예제) employee 테이블에서 사번과 부서번호를 하나의 칼럼으로(사번-부서번호) 출력하시오. (concat, alias 사용)

select concat('empno', '-', 'deptno') as '사번-부서번호' from employee;

예제) 사원 테이블의 모든 부서번호 출력하시오. (사원 수 만큼 출력)

select deptno from employee;

예제) 사원 테이블의 부서번호를 중복되지 않게 출력하시오.

select distinct deptno from employee;

예제) employee 테이블에서 직원의 사번(empno), 이름(name), 직업(job)을 출력하시오. (단, 이름을 기준으로 내림차순 정렬)

select empno, name, job from employee order by name desc;

select empno, name, job from employee order by 2 desc;            ( ⇒ 숫자 사용 가능! employee 테이블에서 2번째 column을 기준으로 한다는 뜻)

예제) employee 테이블에서 고용일(hiredate)이 1981년 이전의 사원이름과 고용일을 출력하시오.

select name, hiredate from employee where hiredate < '1981-01-01';

예제) employee 테이블에서 부서번호가 30인 모든 데이터를 출력하시오.

select * from employee where deptno = 30;

예제) employee 테이블에서 부서번호가 10 또는 30인 사원이름과 부서번호를 출력하시오. (IN 키워드 사용)

select name, deptno from employee where deptno in (10, 30);

혹은

select name, deptno from employee where deptno = 10 or deptno = 30;

예제) employee 테이블에서 부서번호가 30이고 급여가 1500 미만인 사원이름과 부서번호를 출력하시오. (IN 키워드 사용)

select name, deptno from employee where deptno = 30 and salary < 1500;

LIKE 키워드

• 와일드 카드를 사용하여 특정 문자를 포함한 값에 대한 조건을 처리
• % 는 0에서부터 여러 개의 문자열을 나타냄
• _ 는 단 하나의 문자를 나타내는 와일드 카드

예제) employee 테이블에서 이름이 'A'로 시작하는 모든 데이터를 출력하시오.

select * from employee where name like 'A%';

예제) employee 테이블에서 이름이 'N'으로 끝나는 모든 데이터를 출력하시오.

select * from employee where name like '%N';

예제) employee 테이블에서 이름에 'A'가 포함된 사원의 이름(name)과 직업(job)을 출력하시오.

select name, job from employee where name like '%A%';

예제) employee 테이블에서 이름의 두번째 글자가 'A'인 모든 데이터를 출력하시오.

select * from employee where name like '_A%';

함수

• UCASE, UPPER
  예제) select upper('SEoul'), ucase('seOUL');
  예제) select lower(name) from employee;

• LCASE, LOWER
• substring
  예제) select substring('happy day', 3, 2);

• LPAD, RPAD
  예제) select lpad(name, 10, '+') from employee;

• TRIM, LTRIM, RTRIM
  예제) select ltrim(' hello '), rtrim(' hello ');
  예제) select trim(' hi '), trim(both 'x' from 'xxxhixxx');

• ABS(x) : x의 절대값을 구한다.
  예제) select abs(2), abs(-2);

• MOD(n,m) % : n을 m으로 나눈 나머지 값을 출력한다.
  예제) select mod(234,10), 253%7, mod(29,9);

• 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의 차이 개월을 반환한다.

cast 형변환

예제) select cast(now() as date);

예제) select cast(1-2 as unsigned);

예제) select cast(1-2 as signed);

그룹함수

예를 들어,

select concat(name,'aaaa') from employee;

를 입력하면, employee 테이블 내의 여러 데이터가 한꺼번에 출력된다. 이러한 함수들을 '단일 함수'라고 한다.

이와는 다르게 여러 데이터에서 단 하나의 값만을 보여주는 함수를 '그룹 함수'라고 한다.

예제) select count(*) from employee;

예제) select avg(salary) from employee;

예제) select count(comm) from employee;

예제) employee 테이블에서 부서번호가 30인 직원의 급여 평균과 총합계를 출력하시오.

select avg(salary), sum(salary) from employee where deptno = 30;

'~~별'이라고 하면, group by를 떠올리자. 어떤 column을 기준으로 grouping할 것인지 지정하는 것이다.

예제) employee 테이블에서 부서별 직원의 부서번호, 급여 평균과 총합계를 출력하시오.

select deptno, avg(salary), sum(salary) from employee group by deptno;

INSERT (데이터 입력)

insert는 다음과 같이 사용한다.

INSERT INTO 테이블명(필드1, 필드2, 필드3, 필드4, … ) 

        VALUES ( 필드1의 값, 필드2의 값, 필드3의 값, 필드4의 값, … )

INSERT INTO 테이블명

        VALUES ( 필드1의 값, 필드2의 값, 필드3의 값, 필드4의 값, … )

• 필드명을 지정할 때, default 값이 세팅된 필드는 생략할 수 있다.
• 필드명을 지정해주는 방식은 추후, 필드가 추가/변경/수정 되는 변경에 유연하게 대처 가능하다.
• 필드명을 생략했을 경우에는 모든 필드 값을 반드시 입력해야 한다.

예제) ROLE테이블에 role_id는 200, description에는 'CEO'로 한건의 데이터를 저장하시오.

먼저 show tables;로 테이블 목록 확인!

desc role;로 role 테이블의 정보 확인!

insert into role (role_id, description) values (200, 'CEO');

insert into role values(200, 'CEO');

select * from role;

을 입력하면 '200, CEO' row가 추가된 것을 확인할 수 있다.

이 때, insert into role(description) values('aaa');

를 입력하면, 'role_id'에 default 값이 없다는 에러가 발생한다. 위의 캡쳐화면에서 볼 수 있듯이, role_id는 primary이므로 null이어서는 안된다.

따라서, 반대로 insert into role(role_id) values(201);

를 입력하면 이상없이 insert가 된다.

UPDATE (데이터 수정)

update는 다음과 같이 사용한다.

UPDATE  테이블명

        SET  필드1=필드1의값, 필드2=필드2의값, 필드3=필드3의값, …

  WHERE  조건식

• 조건식을 통해 특정 row만 변경할 수 있다.
• 조건식을 주지 않으면 전체 row가 수정되니 조심해야 한다.

예제) ROLE테이블에 role_id가 200일 경우 description을 'CTO'로 수정하시오.

update role set description = 'CTO' where role_id = 200;

DELETE (데이터 삭제)

delete는 다음과 같이 사용한다.

 DELETE

      FROM  테이블명

WHERE  조건식

• 조건식을 통해 특정 row만 삭제할 수 있다.
• 조건식을 주지 않으면 전체 row가 삭제되니 조심해야 한다.

예제) ROLE테이블에서 role_id는 200인 정보를 삭제하시오.

delete from role where role_id = 200;