[MySQL] 데이터 조작어 DML

<2. DB 연결 웹 애플리케이션 - DML>

[boostcourse full-stack] http://www.edwith.org/boostcourse-web

컬럼이 두개 이상 보이게 하고싶은 경우 ,을 추가한다.

select deptno,name from department; => deptno, name 두개의 칼럼을 출력시킨다.

테이블 구조(어떤 컬럼이 있는지)를 확인할 때는 desc department;(테이블명) 을 입력하여 확인한다.

---

* 별칭 부여하기

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

as는 명시적으로 넣은 것. as없이 공백으로만 넣어도 된다.

---

* 문자열 결합 함수 - concat

Ex) employee 테이블에서 사번(empno)과 부서번호(deptno)를 하나의 칼럼으로 출력하시오. (두개를 - 로 연결한다.)

SELECT concat( empno, '-', deptno) AS '사번-부서번호' 
FROM employee;

---

* 기준을 정하여 정렬 - order by

정렬하고 싶을 경우에는 order by를 끝에 추가시키고 조건을 붙인다.

ASC, DESC를 생략하면 기본적으로 ASC로 정렬된다.

첫 번째 칼럼(empno)을 기준으로 정렬하고 싶을 때 1로도 표현할 수 있다.

select empno, name from employee order by 1 desc;

---

* 특정 행 검색 - where절

조건을 부여하여 값을 가져올 수 있다. 산술, 비교연산자를 추가할 수 있다.

Ex) employee 테이블에서 부서번호가 30인 사원이름과 부서번호를 출력하시오.

select name, deptno from employee where deptno = 30;

---

* IN 키워드

복수의 조건을 나타낼 때 유용하다.

Ex) employee 테이블에서 부서번호가 10또는 30인 사원이름과 부서번호를 출력하시오.

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

아래와 같이 IN 대신 논리연산자를 사용할 수 있다. (and, or)

select name, deptno from employee where deptno = 10 and deptno = 10;

---

* LIKE 키워드

와일드 카드(%, _)를 사용하여 특정 문자를 포함한 값에 대한 조건을 처리한다.

% 는 0에서 부터 여러 개의 문자열을 나타낸다.

_ 는 단 하나의 문자를 나타내는 와일드 카드이다.

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

(이름에 A가 들어가 있으면 모두 출력된다.)

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

---

* SELECT 구문 (함수의 사용)

• 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의 개월 차이를 반환

• LOWER('STRING'), UPPER('string') : 소문자, 대문자 변환

• SUBSTRING : 문자 분리해내기, SUBSTRING ('HelloWorld', 3, 2)이면 ll이 반환 (index는 1부터 시작한다.)

• LPAD(왼쪽), RPAD(오른쪽) : 출력 시 오른쪽, 왼쪽에 원하는 문자를 채울 때 사용

• LTRIM(왼쪽), RTRIM(오른쪽), TRIM : 공백 없애기

- 함수 사용 예제

• UCASE, UPPER

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

from 다음에 테이블이 없을 경우에는 테이블에서 조회하는 것이 아님

• 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                                       |
  +-----------------+-----------------+

• LPAD, RPAD

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

• 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                                       |
+----------------+-----------------------------------+

• 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         | 
+----------------+------------+-------------+

---

* SELECT 구문 (CAST - 형변환)

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 |
+----------------------------+

---

* SELECT 구문 (그룹함수)

여러 개의 칼럼의 값을 가지고 하나의 결과값을 만든다.

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

SELECT AVG(salary) , SUM(salary)
FROM employee
WHERE deptno = 30;

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

SELECT deptno, AVG(salary) , SUM(salary)

FROM employee

group by deptno;

 

group by를 사용하면 부서별(deptno)로 그룹지어 반환한다.

---

* 데이터 입력 (INSERT문)

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

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

필드명을 지정해주는 방식은 디폴트 값이 세팅되는 필드는 생략할 수 있다.

필드명을 지정해주는 방식은  추 후, 필드가 추가/변경/수정 되는 변경에 유연하게 대처 가능

필드명을 생략했을 경우에는 모든 필드 값을 반드시 입력해야 한다.

기본키에는 NULL값이 들어갈 수 없으므로 반드시 채워주어야 한다. 기본키값이 입력되지 않았을 경우 insert할 수 없다.

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

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

---

* 데이터 수정(UPDATE문)

 UPDATE  테이블명
        SET  필드1=필드1의값, 필드2=필드2의값, 필드3=필드3의값, …
   WHERE  조건식

조건식을 통해 특정 row만 변경할 수 있다.

조건식을 주지 않으면(where절 생략할 경우) 전체 로우에 영향을 미치니 조심해서 사용해아 한다.

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

update ROLE

set description = 'CTO'

where role_id = 200;

---

* 데이터 삭제(DELETE문)

 DELETE
      FROM  테이블명
WHERE  조건식
    

조건식을 통해 특정 row만 삭제할 수 있다.

조건식을 주지 않으면 전체 로우에 영향을 미치니 조심해서 사용해야 한다.

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

delete from ROLE where role_id = 200;

where절을 주지 않을 경우 모든 데이터가 삭제되니 조심해야 한다.