SQL - DML
DML(select, insert, update, delete)
데이터 조작어(Data Manipulation Language, DML)의 종류
데이터 조작어는 모두 동사로 시작합니다.
시작하는 동사에 따라서 다음과 같은 4가지 조작어가 있습니다.
- SELECT – 검색
- INSERT - 등록
- UPDATE - 수정
- DELETE - 삭제
SELECT 구문의 기본문형
- select 구문의 기본문형
SELECT 구문 예제(전체 데이터 검색)
- 전체 데이터 검색
- SELECT 뒤에 * 를 기술함으로써 나타낼 수 있다.
예제 : departments 테이블의 모든 데이터를 출력하시오.
SELECT * FROM DEPARTMENT;
- select 구문 예제
SELECT 구문 예제(특정 컬럼 검색)
- SELECT 뒤에 컬럼을 콤마(,)로 구별해서 나열
예제 : employee 테이블에서 직원의 사번(empno), 이름(name), 직업(job)을 출력하시오.
어떤 칼럼이 있는지는 desc명령으로 확인
select empno, name, job from employee;
- select 구문 예제(특정 컬럼 검색)
SELECT 구문 예제(컬럼에 Alias부여하기)
- 컬럼에 대한 ALIAS(별칭)을 부여해서 나타내는 칼럼의 HEADING을 변경할 수 있다.
예제 : employee 테이블에서 직원의 사번(empno), 이름(name), 직업(job)을 출력하시오.
select empno as 사번, name as 이름, job as 직업 from employee;
- select 구문 예제(칼럼에 alias부여하기)
SELECT 구문 예제(컬럼의 합성(Concatenation))
- 문자열 결합함수 concat 사용
예제 : employee 테이블에서 사번과 부서번호를 하나의 칼럼으로 출력하시오.
SELECT concat( empno, '-', deptno) AS '사번-부서번호'
FROM employee;
- select 구문 예제(컬럼의 합성)
SELECT 구문 예제(중복행의 제거)
- 중복되는 행이 출력되는 경우, DISTINCT 키워드로 중복행을 제거
예제1 : 사원 테이블의 모든 부서번호 출력하시오. (사원 수 만큼 출력된다.)
select deptno from employee;
- select 구문 예제(중복행의 제거)
예제2 : 사원 테이블의 부서번호를 중복되지 않게 출력하시오.
select distinct deptno from employee;
- select 구문 예제(중복행의 제거)-2
SELECT 구문 예제(정렬하기)
- ORDER BY 절
SELECT 구문 예제(정렬하기)
예제 : employee 테이블에서 직원의 사번(empno), 이름(name), 직업(job)을 출력하시오.
단, 이름을 기준으로 오름차순 정렬합니다.
select empno, name, job from employee order by name;
select empno as 사번, name as 이름, job as 직업 from employee order by 이름;
- select 구문 예제(alias를 사용하지 않았을 경우)
- select 구문 예제(alias를 사용했을 경우)
SELECT 구문 예제(정렬하기)
예제 : employee 테이블에서 직원의 사번(empno), 이름(name), 직업(job)을 출력하시오.
단, 이름을 기준으로 내림차순 정렬합니다.
select empno, name, job from employee order by name desc;
- select 구문 예제(이름 기준 내림 차순 정렬하기 결과)
SELECT 구문 예제(특정 행 검색- where절)
- SELECT 구문 예제(특정 행 검색- where절)
- 산술비교 연산자
예제 : employee 테이블에서 고용일(hiredate)이 1981년 이전의 사원이름과 고용일을 출력하시오.
select name, hiredate from employee where hiredate < '1981-01-01';
- select 구문 예제(특정 행 검색-where절)
- 논리연산자
예제 : employee 테이블에서 부서번호가 30인 사원이름과 부서번호를 출력하시오.
select name, deptno from employee where deptno = 30;
- select 구문 예제(특정 행 검색-where절)-2
- IN 키워드
예제 : employee 테이블에서 부서번호가 10또는 30인 사원이름과 부서번호를 출력하시오.
select name, deptno from employee where deptno in (10, 30);
- select 구문 예제(특정 행 검색-where절)-3
- LIKE 키워드
- 와일드 카드를 사용하여 특정 문자를 포함한 값에 대한 조건을 처리
- % 는 0에서부터 여러 개의 문자열을 나타냄
- _ 는 단 하나의 문자를 나타내는 와일드 카드
예제 : employee 테이블에서 이름에 'A'가 포함된 사원의 이름(name)과 직업(job)을 출력하시오.
select name, job from employee where name like '%A%';
- select 구문 예제(특정 행 검색-where절)-4
SELECT 구문 예제(함수의 사용)
- 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 구문(함수의 사용)
- 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 구문(CAST 형변환)
- 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 구문(그룹함수)
- SELECT 구문(그룹함수)
SELECT 구문 예제(그룹함수)
예제 : employee 테이블에서 부서번호가 30인 직원의 급여 평균과 총합계를 출력하시오.
SELECT AVG(salary) , SUM(salary)
FROM employee
WHERE deptno = 30;
- SELECT 구문 예제(그룹함수)
SELECT 구문 예제(그룹함수와 groupby 절)
예제 : employee 테이블에서 부서별 직원의 부서번호, 급여 평균과 총합계를 출력하시오.
SELECT deptno, AVG(salary) , SUM(salary)
FROM employee
group by deptno;
- SELECT 구문 예제(그룹함수와 groupby 절)
데이터 입력 (INSERT문)
INSERT INTO 테이블명(필드1, 필드2, 필드3, 필드4, … ) VALUES ( 필드1의 값, 필드2의 값, 필드3의 값, 필드4의 값, … ) INSERT INTO 테이블명 VALUES ( 필드1의 값, 필드2의 값, 필드3의 값, 필드4의 값, … )- 필드명을 지정해주는 방식은 디폴트 값이 세팅되는 필드는 생력할 수 있습니다.
- 필드명을 지정해주는 방식은 추 후, 필드가 추가/변경/수정 되는 변경에 유연하게 대처 가능합니다.
- 필드명을 생략했을 경우에는 모든 필드 값을 반드시 입력해야 합니다.
데이터 입력 실습 (INSERT문)
예제 : 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만 변경할 수 있습니다.
- 조건식을 주지 않으면 전체 로우가 영향을 미치니 조심해서 사용하도록 합니다.
[예제11] user 번호가 1인 사용자의 이름을 영문으로 바꾸고, join_date을 현재 시간이 적용되도록 수정하시오.
데이터 수정 실습(UPDATE문)
예제 : ROLE테이블에 role_id가 200일 경우 description을 'CTO'로 수정하시오.
update ROLE set description = 'CTO' where role_id = 200;* where절을 안줄 경우 모든 데이터가 수정되니 조심해야 합니다.
- 데이터 수정 실습(UPDATE문)
데이터 삭제(DELETE문)
DELETE FROM 테이블명 WHERE 조건식- 조건식을 통해 특정 row만 삭제할 수 있습니다.
- 조건식을 주지 않으면 전체 로우가 영향을 미치니 조심해서 사용하도록 합니다.
[예제12] user 번호가 1인 사용자를 삭제하시오.
데이터 삭제 실습(DELETE문)
예제 : ROLE테이블에서 role_id는 200인 정보를 삭제하시오.
delete from ROLE where role_id = 200;* where절을 안줄 경우 모든 데이터가 삭제되니 조심해야 합니다.
- 데이터 삭제 실습(DELETE 문)