국비교육

국비지원 46일차(SQL- DML 문법)

재밌는개발러 2023. 10. 8. 00:03

※저번시간에 SQL 종류와 DDL(데이터 정의어)에 관해서 알아보았다.

이번시간에는 DML(데이터 조작어) 의 개념 및 예제 들에 대해 자세히 알아볼 것입니다.

 

※DML(데이터 조작어) 

1. select (검색)

 1)  select문 사용법
    select 컬럼명, 칼럼명... from 테이블이름

 

@select문 기본예제

-- 현재 계정이 가지고 있는 모든 테이블 정보
select table_name from user_tables;

-- emp 테이블에서 empno, ename, deptno 만 출력
-- 별칭설정: '컬럼명 as 별칭' 또는 '컬럼명 별칭'
--           '* as 별칭' 는 안됨
select empno as 직원번호, ename 직원이름, deptno  from emp;

 2) where 절
    특정 조건 선택시 where 절 이용
    조건 여러개일때는 and나 or 사용
    and와 or 같이 사용 시 괄호 잘 써야 함.(괄호가 먼저 동작)
    (괄호 없을시 and가 먼저 동작)  

 

@select문 where절 예제

-- deptno가 30번인 직원들 출력
select empno as 직원번호, ename 직원이름, deptno, sal  
    from emp where deptno=30;
    
-- deptno가 30번 이고, sal 이 1500 이상인 직원들 출력

select empno as 직원번호, ename 직원이름, deptno, sal  
    from emp where deptno=30 and sal >= 1500;
    
-- deptno 가 10, 20인 직원들 출력
select empno as 직원번호, ename 직원이름, deptno, sal  
    from emp WHERE deptno=10 or deptno=20;
    
-- 아래 두개는 and와 or을 같이 쓰는 예제.
-- 괄호 때문에 둘의 결과가 다름
select empno as 직원번호, ename 직원이름, deptno, sal  
from emp 
WHERE deptno=10 or (deptno=20 and sal>=1500);

select empno as 직원번호, ename 직원이름, deptno, sal  
from emp 
WHERE (deptno=10 or deptno=20) and sal>=1500;


-- emp 테이블에는 없는 '연봉' 출력
select empno as 직원번호, ename 직원이름, deptno, sal , sal * 12 as 연봉
from emp 
WHERE (deptno=10);

 3) order by 절

    오름차순(asc 기본값 - 작은 수에서 올라감), 내림차순(desc - 큰 수에서 내려감)
    사용법: order by 컬럼명, 칼럼명...
    where절은 컬럼명 여러 개일 때 and나 or로 구분
     order by절은 , 로 구분
    필요할때만 사용하기(코스트가 발생하기 때문)

 

@select문 order by 절 예제

-- deptno 는 작은것부터 나열, sal은 큰수부터 나열.
select * from emp order by deptno asc, sal asc;

-- deptno 가 30인 직원들을 sal 높은순으로 나열
select * from emp where deptno=30 order by sal desc;

 4) group by 절

   그룹화: group by절
            group by절은 집계함수와 같이 사용해야 한다.
            
    count(): 테이블의 row 수 구하기
    max(컬럼명): 해당 칼럼에서 제일 큰 값
    min(컬럼명): 해당 칼럼에서 제일 작은 값
    avg(컬럼명): 해당 칼럼에서 평균값
    sum(컬럼명): 해당 칼럼들의 합

 

- having절 - (group by 한 결과에 where절 추가한 것과 같은 결과)
    *group by절 사용시에만 사용 가능
     group by절의 결과값의 범위 제한할 때 사용

 

- 소수점 자리 구하기 
    TO_CHAR(값, 포멧) - 포맷은 0과 9 사용
    포멧팅의 숫자번째마다 실제값의 숫자번째를 의미하고,
    (포멧팅)9의 의미는 실제값의 그 자리에 숫자가 없으면 화면에 표시 안 함
    0의 의미는 실제값에 그 자리에 숫자가 없으면 0으로 표시
    ex) TO_CHAR(avg(sal), 'FM9990.00') FM은 (FORMAT의 약자)
        포멧:FM999990.00, 실제값: 1234.56, 결과-> 1234.56
        포멧:FM000000.00, 실제값: 1234.56, 결과-> 001234.56

 

@select문 group by 절 예제

-- deptno 와 mgr 그룹화하여 해당그룹화 내용의 갯수를구하라
select deptno, count(*) from emp 
 group by deptno, mgr order by deptno asc;
 
 -- 최고 월급 얼만지 구하기
select max(sal) from emp;
-- 전체 월급 얼만지 구하기
select sum(sal) from emp;
-- 평균 월급 얼만지 구하기
select avg(sal) from emp;

@select문 having 절 예제

-- 각 부서의 최대급여, 최소급여,각부서의 평균 급여(XXXX,XX)는 2000이상인것만 나타내라.
select deptno, max(sal), min(sal) , RTRIM(TO_CHAR(avg(sal), 'FM9999999.99')) avg
from emp group by deptno HAVING avg(sal) >= 2000 order by deptno;

 

@select문 TO_CHAR(값, 포멧) 예제

-- 각 부서의 최대급여, 최소급여, 평균 급여(XXXX,XX) 구하기
select deptno, max(sal), min(sal) , RTRIM(TO_CHAR(avg(sal), 'FM9999999.99')) 
from emp group by deptno order by deptno;
select deptno, max(sal), min(sal) , TO_CHAR(avg(sal), 'FM0000000.99') 
from emp group by deptno order by deptno;

 

5). 연산자

- IN(or개념 여러개중 1개 고르는 거)

-- IN 연산자
select * from emp 
where job='CLERK' or  job='SALESMAN' or  job='MANAGER';
select * from emp 
where job IN ('CLERK','SALESMAN','MANAGER');

 

- between A and B(and개념 A~B사이에 값 고르기)

-- between 연산자
select * from emp where 1500 <= sal and sal <= 2500;
select * from emp where sal between 1500 and 2500;

 

- like(%를 이용해서 % 부분은 그 위치에 모든 문자가 출력)

-- *like 연산자: %를 이용해서 %부분은그위치에 모든문자가 출력
--              대소문자 구분
select * from emp where job='CL';
select * from emp where job like '%L%
';
select * from emp where ename like '%LL%';

 

- null 값 체크 예제(is not null => 비어있지 않은 값)

-- Null 값 체크 : is not null
select * from emp;
select * from emp where comm is not null;
select * from emp where comm is null;

6) 결합연산자

  1) union: 합집합(결괏값의 중복이 있으면 제거)
  2) union all: 합집합(결과값의 중복이 있어도 모두 출력)
  3) minus: 차집합(먼저 작성한 select의 결과에서 빼기)
  4) intersect: 교집합(먼저 작성한 select 결과에서 두 번째 select와 같은 것만 출력)

 

@ 결합연산자 예제

/*아래 두개 select문은 deptno=10 으로 동일한 테이블의 결합하고 있음.
    UNION 의 경우 동일한 결과가 두번 출력되지 않고 (결과3개 나옴)
    UNION ALL 의 경우는 그냥 그런거 신경 안쓰고 다 나온다.(결과 6개 나옴)
*/
select deptno, ename, job, sal, comm from emp where deptno=10
UNION
select deptno, ename, job, sal, comm from emp where deptno=10;

select deptno, ename, job, sal, comm from emp where deptno=10
UNION ALL
select deptno, ename, job, sal, comm from emp where deptno=10;

/* minus - 첫번째 select 결과에서 두번째select(deptno=10) 의 결과를 뺀거  */
select ename, job, sal, comm, deptno from emp
MINUS
select ename, job, sal, comm, deptno from emp where deptno=10;


/* intersect- 첫번째 select 결과에서 두번째select(deptno=10)의 결과와 겹치는거만 출력 */
select ename, job, sal, comm, deptno from emp
INTERSECT
select ename, job, sal, comm, deptno from emp where deptno=10;

7) 숫자함수, 문자함수, 날짜함수

- 숫자함수

  round: 반올림
  ceil: 올림
  floor: 내림
  trunc: 특정 위치 이하 버림(소수점, 정수 부분 다 가능)
      trunc(값, 자릿수) => 자릿수: 양수는 '소수점 몇 자리'를 의미
                                       음수는 정수자리를 버리진 않고 0으로만 바꿈
                                 ex) trunc(123.4567, 2) => 123.45
                                       trunc(123.4567, -1) => 120
       mod: % 같은 거. ex) mod(분자, 분모)  
                                       mod(나눠지는 수, 나누는 수)  
                                       mod(10, 3) => 10 % 3 => 1

@ 숫자함수 예제   

select round(123.4567),ceil(123.4567),floor(123.4567), mod(10, 3) from dual;
select trunc(123.4567), trunc(123.4567, 2), trunc(123.4567, -1) from dual;

 

- 문자함수

   upper: 모두 대문자
   lower: 모두 소문자
   initcap: 각 단어의 첫 문자 뜰만 대문자
   length: 문자열 길이
   count: row개수
   substr: 문자열 자르기 ex) substr(job, 3,2) => job에서 세 번째 글자부터 2개 잘라오기
   concat: 문자열 합치기 ex) select concat(empno, ename) from emp;

 

 @ 문자함수 예제   

select lower(job), initcap(job) from emp;
select * from emp where ename=upper('allen');
select ename, length(ename) from emp;
select count(*) from emp;
select job, substr(job, 3,2) from emp;
select concat(empno, ename) from emp;

 

- 날짜 함수

  sysdate - 월, 년 바뀔 때 알아서 다 해줌
  *날짜포맷 변경방법: 메뉴바 > 도구 > 환경설정 > 데이터 베이스 > NLS에서 변경 가능

 

 @ 날짜함수 예제   

-- 날짜 더하기 빼기
select sysdate as 오늘, (sysdate+5) as 오일후 from dual;
-- 월 더하기 빼기 (add_months)
select sysdate as 오늘, add_months(sysdate, -3) as 세달전 from dual;
-- 두 날짜 사이 간격(월) (months_between)
select sysdate as 오늘, trunc(months_between(sysdate, add_months(sysdate, 3))) as 날짜간격 from dual;

-- 포멧 변경
/* - 형변환
    TO_CHAR: Date형을 문자형으로 변환
    TO_DATE: 문자형을 Date형으로 변환
    TO_NUMBER
*/
select TO_DATE(sysdate, 'yyyy-mm-dd') from emp; 
select TO_CHAR(sysdate, 'yyyy-mm-dd') from emp; 

select TO_CHAR(hiredate, 'yyyy-mm-dd') as 고용일, TO_CHAR(sysdate) as 오늘, (TO_DATE(hiredate) - TO_DATE(sysdate)) as 날짜간격 from emp;
select TO_CHAR(hiredate, 'yyyy-mm-dd') as 고용일, TO_CHAR(sysdate) as 오늘, (abs(TO_DATE(sysdate, 'yyyy-mm-dd')-TO_DATE(hiredate, 'yyyy-mm-dd'))) as 날짜간격 from emp;
 
-- 근속년수 구하기
select 
    TO_CHAR(hiredate, 'yyyy-mm-dd') as 고용일, 
    TO_CHAR(sysdate) as 오늘, 
    ceil(trunc(abs(months_between(TO_DATE(sysdate), TO_DATE(hiredate)))) / 12) as 근속년수
from emp;

 

@ 날짜, 시간 format 예제

/*  날짜 포멧 */
select TO_CHAR(SYSDATE,'yyyymmdd') from dual;
select TO_CHAR(SYSDATE,'yyyy-mm-dd') from dual;
select TO_CHAR(SYSDATE,'yyyy') from dual;
select TO_CHAR(SYSDATE,'yy') from dual;
select TO_CHAR(SYSDATE,'mm') from dual; -- 월 기본 숫자 두자리
select TO_CHAR(SYSDATE,'mon') from dual; -- 문자형태의 월 
select TO_CHAR(SYSDATE,'day') from dual; -- 문자형태의 요일 
select TO_CHAR(SYSDATE,'dy') from dual; --  문자형태의 요일(한자리)
select TO_CHAR(SYSDATE,'d') from dual; -- 요일: 1(일요일)~7(토요일)
select TO_CHAR(SYSDATE,'dd') from dual; -- 날짜
select TO_CHAR(SYSDATE,'ddd') from dual; -- 날짜(365일중에 몇번째 일인지 구해줌)
select TO_CHAR(SYSDATE,'ww') from dual; -- 1년 (53주) 중에 몇번째 주인지 출력
select TO_CHAR(SYSDATE,'w') from dual; -- 이번달 중에 몇번째 주인지 출력
select TO_CHAR(SYSDATE,'dl') from dual; -- 날짜가 기본 포멧팅으로 되서 출력 ex) 2023년 9월 27일 수요일

-- 시간 포멧
select TO_CHAR(SYSDATE,'am') from dual;
select TO_CHAR(SYSDATE,'pm') from dual;
select TO_CHAR(SYSDATE,'hh') from dual; -- 12시간제
select TO_CHAR(SYSDATE,'hh24') from dual; -- 24시간제
select TO_CHAR(SYSDATE,'mi') from dual; -- 분(기본 두자리수. 00~59)
select TO_CHAR(SYSDATE,'ss') from dual; -- 초(기본 두자리수. 00~59)

2. insert into(생성)

 -  클래스에 정보 삽입 
    insert into 테이블명 values (칼럼 1 값, 칼럼 2 값,....); 
    insert into 테이블명 (칼럼명 1, 칼럼명 2,...) values (칼럼 1 값, 칼럼 2 값,....); 

* 클래스에 해당하는 칼럼명이 다 추가되게끔 생성해야되고 컬럼명 순서에 맞게 기입해야 된다.

 - value 값과 해당 클래스의 칼럼명 순서와 갯수가 일치하게끔 작성해야된다 

 - 컬럼명 1개라도 기입 안 할 시 오류가 난다.

insert into classes values ('C-001','자바', '2023-10-04', '2023-12-31', 'D강의장');
insert into classes values ('C-002','C', '2023-10-04', '2023-12-31', '501강의장');
insert into classes values ('C-003','파이선', '2023-10-04', '2023-12-31', '302강의장');

 

3. update (수정)

 -  수정(update문) 
    update 테이블명 set 칼럼명=값 where 칼럼명=값;
    *주의: where 없으면 싹 다 바뀜

 update student set s_name='진' where s_seqno='S-002';

 

4. delete (내용삭제)

-  삭제(delete문) 
    delete from 테이블명;
    delete from 테이블명 where 컬럼명=값;

delete from student where s_seqno='S-001';