MySQL 데이터 조작어 DML(select, insert, update, delete)

SELECT 검색(조회)

기본구문

: SELECT(DISTINCT) 컬럼명(ALIAS) FROM 테이블명;

• 괄호안은 생략 가능
• SELECT 뒤 검색하고자하는 데이터(컬럼)를 콤마(,)로 구분하여 나열
  전체컬럼은 컬럼명 대신 '*' 를 사용
• DISTINCT 사용시 중복행 제거
• ALIAS 컬럼의 별칭 부여
• FROM 뒤 선택한 컬럼이 있는 테이블 명시
• 문자열 결합함수 concat 사용 시 두개의 컬럼을 하나의 컬럼으로 결합

--SELECT 예시

--DEPARTMENT테이블의 데이터 전체 조회
SELECT * FROM  DEPARTMENT;

--EMPLOYEE테이블의 특정 컬럼 조회
SELECT EMPNO,NAME,JOB FROM EMPLOYEE;

--컬럼에 별칭부여
select empno as 사번, name as 이름, job as 직업 from employee;
select empno 사번, name 이름, job 직업 from employee;

--사번과 부서번호를 하나의 컬럼으로 합성
SELECT concat( empno, '-', deptno) AS '사번-부서번호' 
FROM employee;

--중복 행 제거
select distinct deptno from employee;

 

ORDER BY (정렬하기)

: SELECT(DISTINCT) 컬렴명(ALIAS)

FROM 테이블명

ORDER BY 컬럼이나 표현식 (ASC or DESC);

• ASC : 오름차순 정렬, DEFAULT 값
• DESC : 내림차순 정렬

--ORDER BY 예제

--이름 기준으로 오름차순 정렬
select empno, name, job from employee order by name;
select empno, name, job from employee order by name asc;

--이름 기준으로 내림차순 정렬
select empno, name, job from employee order by name desc;

 

WHERE (조건 검색)

: SELECT(DISTINCT) 컬렴명(ALIAS)

FROM 테이블명

WHERE 조건식

ORDER BY 컬럼이나 표현식 (ASC or DESC);

• 조건식 : 컬럼이름이나 표현식의 상수, 연사자로 구성
• EX ) WHERE TITLE = 'Staff'
  TITLE이 'Staff'인 조건 
  WHERE salary BETWEEN 1000 AND 2000
  salary가 1000에서 2000사이인 조건

--WHERE 예제

--employee 테이블에서 부서번호가 30인 사원이름과 부서번호를 출력
select name, deptno from employee where deptno = 30;

--employee 테이블에서 고용일(hiredate)이 1981년 이전의 사원이름과 고용일을 출력
select name, hiredate from employee where hiredate < '1981-01-01';

--employee 테이블에서 부서번호가 10또는 30인 사원이름과 부서번호를 출력
select name, deptno from employee where deptno in (10, 30);

--LIKE : 특정 문자를 포함한 값에 대한 조건을 처리
-- % : 0에서부터 여러개의 문자열을 나타냄
-- _ : 단 하나의 문자를 나타냄
-- employee 테이블에서 이름에 'A'가 포함된 사원의 이름(name)과 직업(job)을 출력
select name, job from employee where name like '%A%';

 

SELECT구문에서 사용하는 함수

• UCASE('str'), UPPER('str') : 문자열을 대문자로 변환
• LCASE('str'), LOWER('str') : 문자열을 소문자로 변환
• substring('str',idx,cnt) : 문자열의 idx부터 cnt개 출력
• LPAD('str',num,'ch'), RPAD('str',num,'ch') : num크기의 길이를 두고
  str의 나머지 부분을 ch로 왼쪽 또는 오른쪽에 채워줌
• TRIM('str'), LTRIM, RTRIM : 문자열의 앞뒤, 왼쪽, 오른쪽 공백 제거
• ABS(x) : x의 절댓값 반환
• MOD(n,m) , % : n을 m으로 나눈 나머지 값 반환
• 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 형변환

: CAST (experession AS type) 또는

CONVERT (experssion, type)

 

MySQL type

• binary
• char
• date
• datetime
• signed(integer)
• time
• unsigned(integer)

--CAST 예제

--현재시간을 날짜형식으로 변환
select cast(now() as date);

--식을 unsigned로 변환
select cast(1-2 as unsigned);

 

그룹함수

• count(expr) : null이 아닌 row의 갯수를 반환
• count(distinct exper,[expr...]) : null이 아닌 중복되지 않은 row의 갯수를 반환
• count(*) : row의 전체 갯수를 반환
• avg(expr) : exper의 평균값을 반환
• min(expr) : 최소값을 반환
• max(expr) : 최대값을 반환
• sum(expr) : 합계를 반환
• group_concat(expr) : 그룹에서 concatenated한 문자를 반환
• variance(expr) : 분산
• stddev(expr) : 표준 편차를 반환

--그룹함수, group by 예제

-- employee 테이블에서 부서번호가 30인 직원의 급여 평균과 총합계 출력
SELECT AVG(salary) , SUM(salary)
FROM employee
WHERE deptno = 30;

--employee 테이블에서 부서별 직원의 부서번호, 급여 평균과 총합계를 출력
SELECT deptno, AVG(salary) , SUM(salary)
FROM employee
group by deptno;

 

INSERT (데이터 추가)

기본구문

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

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

• 디폴드 값이 세팅되는 필드는 생략가능 , 제약조건에 따라 생략 불가능 할 수 있음
• 필드명을 생략했을 경우 모든 필드 값을 반드시 입력해줘야 함

-- 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만 변경
• 조건식을 주지 않으면 전체 row가 영향을 미침

-- UPDATE 예제

--ROLE테이블에 role_id가 200일 경우 description을 'CTO'로 수정
update ROLE
set description = 'CTO'
where role_id = 200;

 

DELETE (데이터 삭제)

기본구문

: DELETE FROM 테이블명 WHERE 조건식

• 조건식을 통해 특정 row만 삭제
• 조건식을 주지 않으면 전체 row가 삭제

-- DELETE 예제

--ROLE테이블에서 role_id는 200인 정보를 삭제
delete from ROLE where role_id = 200;