SQL_8일차

서창민·2023년 3월 20일
sql
0

SQL

목록 보기
8/10
post-thumbnail

23.03.20 월 8일차

오늘은 SQL을 배웠던 내용들을 다시금 정리해보는 시간을 가졌다.
다양한 함수와 연산기호들이 있었고, 각각의 형태에 맞추어 사용을 해야했다.
형식을 각각 생각할 수 있도록 다시 복습해보도록 하자.

SQL 정리

오라클의 데이터 형식

  • 날짜와 시간 데이터 형식
--테이블 생성
create table p242
(
    idx number(4),
    today date 
    --데이터형(날짜형)
);
-- 값 입력
insert into p242(idx, today)
values('1001', '2023-03-20'); -- 2023/03/20 출력
insert into p242(idx, today) 
values('1002', '2023/03/20'); -- 2023/03/20 출력
insert into p242(idx, today)
values('1002', '20230320'); -- 2023/03/20 출력
insert into p242(idx, today)
values('1002', sysdate); -- 2023/03/20 출력
-- sysdate : 시스템 날짜 
-- to_char를 사용해 값을 출력
select idx, today from p242; -- 2023/03/20 출력
select idx, to_char(today, 'YYYY/MM/DD') from p242; -- 2023/03/20 출력
select idx, to_char(today, 'YYYY-MM-DD') from p242; --2023/03/20 출력
select idx, to_char(today, 'YYYY년 MM월 DD일') from p242; --오류
이유 : 정해진 형태만 사용가능, 한글과 혼용 불가능
  • 유니코드 데이터 형식(NVARCHAR2())

create table p244
(
    idx number(4),
    name nvarchar2(5)
);
-- 값 입력
insert into p244(idx, name)
values('1001', '가나다라마'); --1001, 가나다라마 출력
insert into p244(idx, name)
values('1001', '12345');-- 1001, 12345 출력
-- 데이터형의 입력 가능한 최대 크기 초과
insert into p244(idx, name)
values('1001', '가나다라마바');--오류
insert into p244(idx, name)
values('1001', '123456');--오류
-- 테이블 확인
select idx, name from p244;
  • 숫자 데이터형식(NUMBER())
create table p249
(
    idx number(4),
    age number(5,2) 
    --유효숫자 5자리중 소수점 2자리까지 표현
);
-- 값 입력
insert into p249(idx, age)
values('1001', '11.1'); -- 1001,11.1 출력
insert into p249(idx, age)
values('1001', '11.2'); -- 1001,11.2 출력
insert into p249(idx, age)
values('1001', '11.3'); -- 1001,11.3 출력
insert into p249(idx, age)
values('1001', '11.4'); -- 1001,11.4 출력
insert into p249(idx, age)
values('1001', '11.5'); -- 1001,11.5 출력
insert into p249(idx, age)
values('1001', '11.1'); -- 1001,11.1 출력
-- 테이블 값 확인
select idx, age from p249;
-- 평균 구하기
select avg(age) from p249;
  • cast 함수
    : cast (컬럼명 as 데이터형식)
-- 숫자형 데이터의 결과값 데이터형 수정
select cast(avg(age) as number(3,1)) from p249; 
-- 소수점 첫번째 자리까지 출력
select cast(MAX(age) as number(3)) from p249; 
-- 소수점 표현 없을시 반올림
  • Dual 테이블의 정의
    : 오라클 자체에서 제공되는 테이블
    : 간단하게 함수를 이용해서 계산 결과값을 확인 할 때 사용하는 테이블
  • cast를 통한 날짜 출력
    : 다양한 구분자로 표현 가능
    : 단! 문자와는 함께 사용 불가능
select cast('2023$12$12' as date) from dual; -- 2023/12/12 출력
select cast('2023/12/12' as date) from dual; -- 2023/12/12 출력
select cast('2023-12-12' as date) from dual; -- 2023/12/12 출력
select cast('2023!12!12' as date) from dual; -- 2023/12/12 출력
-- to_char(숫자,'형식')
: 숫자를 다양한 문자 형식으로 변환
select to_char(12345, '$9999,999') from dual;
--  $12,345 출력
select to_char(12345, '$000,999') from dual;
--  $012,345출력
  • 테이블 만들기
create table p250
(
    idx number(4),
    kor number(3),
    eng number(3)
);
  • 값 삽입
insert into p250(idx, kor, eng)
values('1001', '90', '100');
insert into p250(idx, kor)
values('1001', '88');
  • 값 확인
select idx, kor, eng, kor+eng from p250;
-- null 값이 포함된 다른 칼럼값과 연산시 Null 값 표기
select sum(kor), avg(eng) from p250;
-- 하나의 컬럼에 null 값이 포함되도 연산시 문제없음
  • 컬럼 이름에 문자추가하여 합치기
-- || : 여러개의 문자형을 이어줄때 사용
select '번호:' || idx || '국어:' || kor || 
        '영어' || eng || '합계:' || (kor+eng) 전체내용 from p250;
--번호:1001국어:90영어100합계:190 출력
select '번호:' || idx || '합계:' || (kor+eng) 전체내용 from p250;
-- 번호:1001합계:190 출력

형변환 종류

  • 명시적 형변환
    : 함수를 사용하여 명시적으로 형을 변환시켜주는 것
  1. to_number()
  2. to_char()
  3. cast 등등
  • 묵시적 형변환(암시적형변환)
    : 데이터 형을 연결시 자동으로 형이 변환되는 것
  1. '100' + '100' -- 문자와 문자를 더함(정수로 변환되어 연산)
  2. '100' + 100 -- 문자와 숫자를 연결(문자가 정수형으로 변환되어 처리)
  3. 100 + '100' -- 숫자와 문자를 연결(정수가 문자형으로 변환되어 처리)
  • 암시적인 형 변환 목록
  • 숫자형
select '100' + 200 from dual; 
-- 정수로 출력 300
select '100' + '200' from dual; 
-- 정수로 출력 300
  • 문자형
select 'A' || 'B' from dual;  
--AB 출력
select 'A' || 'B' || 'C'  as 결과값 from dual; 
-- ABC 출력 
select '10' || '20' || '30'  as 결과값 from dual; 
-- 102030 출력 
--오라클은 숫자 형태만 더하기 연산 가능
select '100' + 'A' from dual; --오류
select 'A' + '100' from dual; --오류
select 'A' + 'B' from dual; --오류

오라클 내장함수(문자열)

  • CONCAT(문자,문자)
    : DBA 쪽은 사용하나 웹쪽에서는 사용성이 떨어짐
    : 2개의 문자형을 이어주는 함수 (최대 2개)
select  concat('10' , '20')  as 결과값 from dual; 
-- 1020 출력 
select  concat('10' , '20', '30')  as 결과값 from dual;  
--오류(최대 2개까지 가능)
  • 아스키(ASCII) 영문자
    : 영문자의 아스키코드 값 출력
select ascii('A'), ascii('Z'), ascii('a'), ascii('z') from dual; 
-- 65, 90, 97, 122 출력
  • LENGTH 문자열
    : 문자열의 길이 값 출력
select length('가나다'), length('abc') from dual; 
--3,3 출력
  • LENGTHB 문자열
    : 문자열 길이의바이트 수 표시
select lengthb ('가나다'), lengthb('abc') from dual; 
-- 9,3 출력
  • LOWER(영문자)
    : 전체 소문자로 변경
select LOWER('ABcdEf') FROM dual; 
--abcdef 출력
  • UPPER(영문자)
    : 전체 대문자로 변경
select UPPER('ABcdEf') FROM dual;
-- ABCDEF 출력
  • INITCAP(영문자)
    : 단어의 첫글자를 대문자로 변경
select INITCAP('seo chang min') FROM dual; 
-- Seo Chang Min 출력
  • INSTR(기준문자열, 부분문자열, 찾을 시작 위치 )
    : 기준문자열에서 부분문자열을 찾아서 문자열 시작 위치를 반환
    : 찾을 내용이 없을경우에는 0값을 출력
    : 시작위치 생략은 1과 같음
select INSTR('이것이 이것이 이것이', '이것', 2) FROM dual; 
-- 5 출력
select INSTR('이것이 이것이 이것이', '우리') FROM dual;  
--  0 출력
  • REPLACE (기준문자열, 부분_문자열 포함단어, 바꿀단어)
    : 문자열에 포함된 단어를 변경할 문자로 적용 
select REPLACE('이것이 oracle 이다', '이것이' , 'This is') from dual; 
-- This is oracle 이다 출력
select REPLACE('이것이 oracle 이다. 이것이', '이것이' , 'This is') from dual; 
-- This is oracle 이다 This is출력 (중복되는 단어는 모두 적용)
select REPLACE('어머니는 짜장면을 싫다고 하셨어', '짜장면' , '자장면') from dual;
-- 어머니는 자장면을 싫다고하셨어 출력
  • TRANSLATE ( 문자열, 대상문자, 변환문자 )
    : 대상문자와 변환문자는 1:1로 변환이되며
    : 대상문자에는 있는데 변환문자에는 없으면 해당 문자는 제거
select TRANSLATE('1234567890', '12345', '일이삼사오') from dual;
-- 일이삼사오67890 출력
select TRANSLATE('010-1234-5678', '12345', '일이삼사오') from dual;
-- 0일0-일이삼사-오678 출력
  • SUBSTR( 문자열, 시작위치 , 길이 )
    : 시작    위치 부터 길이만큼 문자를 반환
    : 길이 생략시 문자열 끝까지 반환
select SUBSTR('대한민국만세', 1, 3) from dual;
--대한민 출력
select SUBSTR('대한민국만세', 2, 3) from dual;
--한민국 출력
  • LPAD, RPAD(문자열, 길이, 채울 문자열)
    : 문자열 길이만큼 늘린 후에 빈곳을 채울_문자열로 채운다
    : 길이는 바이트 단위로 크기 지정
select LPAD('이것이', 10, '##') from dual;
-- ####이것이 출력
select LPAD('ABCD', 10, '##') from dual;
-- ######ABCD 출력
  • LTRIM, RTRIM (문자열 / 제거할문자)
    : 문자열의 왼쪽/오른쪽의 제거할    문자를 제거한다.
select LTRIM('     이것이'), length('     이것이'), 
        length(LTRIM('     이것이'))  from dual;
 -- 이것이 , 8, 3 출력
select LTRIM('$$$$이것이', '$') "LTRIM함수" from dual;
 -- 이것이 출력
  • TRIM(제거할방향 제거할문자 from 문자열)
    : TRIM 문자열 만 사용하면 앞 뒤의 공백을 제거한다.
select TRIM(' 이 것 이 ') "TRIM함수" from dual;
 -- 이 것 이 출력
  • REGEXP_COUNT(문자열, 문자)
    : 문자열에서 문자 갯수를 센다
select regexp_count('이것이 오라클이다.' , '이') from dual; 
-- 3 출력
select regexp_count('우리집 우리아빠 우리엄마 우리딸. ' , '우리') from dual; 
-- 4 출력
  • 반올림 CEIL, FLOOR, ROUND (숫자)
    : 올림, 내림, 반올림
select ceil(4.1), floor(4.7), round(4.5) from dual;
-- 5 , 4, 5
  • ADD_MONTHS (날짜, +/-개월)+/- 일수
    : 날짜를 기준으로 개월을 더하거나 뺀 값을 구한다
select ADD_MONTHS('2023-03-20', 5) from dual;
-- 23/08/20 출력
select ADD_MONTHS('2023-03-20', -5) from dual;
-- 22/10/20 출력
select ADD_MONTHS('2023-03-20', 5) + 100 from dual;
-- 23/11/28 출력
select ADD_MONTHS('2023-03-20', 5) - 100 from dual;
-- 23/05/12 출력
  • TO_DATE (날짜) +/- 일수
    : 날짜를 기준으로 일수를 더하거나 뺀 값을 구한다
select TO_DATE('2023-03-20') +100 from dual;
-- 23/06/28 출력
select TO_DATE('2023-03-20') -100 from dual;
-- 22/12/10 출력
  • MONTHS_BETWEEN(날짜1, 날짜2)
    : 두 날짜 사이의 개월 수를 계산(소수점단위) 하여 반환한다.
select MONTHS_BETWEEN(sysdate, '20100609') "개월수"from dual;
--153.371125672043010752688172043010752688 출력
select round(MONTHS_BETWEEN(sysdate, '20100609'),1) "개월수" from dual;
--153.4 출력(소수점 첫번째 자리로 반올림)
select cast(MONTHS_BETWEEN(sysdate, '20100609') as number(4,1) ) from dual;
--153.4 출력(숫자 데이터형을 소수점 첫번째 자리까지 변경)
  • 순위함수, 분반함수
-- userTBL7 만들기 
-- 아이디, 이름 , 생년월일, 키, 가입일
create table userTBL7
 (
    userID char(8) not null primary key,
    username nvarchar2(10) not null,
    birthYear Date,
    height number(3),
    mdate date
 );
 --칼럼 값 입력
insert into userTBL7(userID, username, birthYear, height, mDate)
values('LSG', '이승기', '19871211', '182', '2008-08-08');
insert into userTBL7(userID, username, birthYear, height, mDate)
values('KBS', '김범수', '19781212', '173', '2012-04-04');
insert into userTBL7(userID, username, birthYear, height, mDate)
values('KKH', '김경호', '19711213', '177', '2007-07-07');
insert into userTBL7(userID, username, birthYear, height, mDate)
values('JYP', '조용필', '19501214', '166', '2009-04-04');
insert into userTBL7(userID, username, birthYear, height, mDate)
values('SSK', '성시경', '19791215', '186', '2013-12-12');
insert into userTBL7(userID, username, birthYear, height, mDate)
values('LJB', '임재범', '19631216', '182', '2009-09-09');
insert into userTBL7(userID, username, birthYear, height, mDate)
values('YJS', '윤종신', '19691217', '170', '2005-05-05');
insert into userTBL7(userID, username, birthYear, height, mDate)
values('EJW', '은지원', '19721218', '174', '2014-03-03');
insert into userTBL7(userID, username, birthYear, height, mDate)
values('JKW', '조관우', '19651219', '172', '2010-10-10');
insert into userTBL7(userID, username, birthYear, height, mDate)
values('BBK', '바비킴', '19731220', '176', '2013-05-05');
--칼럼 값 확인
select * from userTBL7;
-- 칼럼/데이터형 추가
alter table userTBL7
add 반번호 number(1);
alter table userTBL7
add addr nchar(2);
-- 추가 확인
select * from userTBL7;
--추가된 칼럼 레코드 값 추가
update  userTBL7 set 반번호='1' , addr='서울'
where username = '성시경';
update  userTBL7 set 반번호='1' , addr='서울'
where username = '임재범';
update  userTBL7 set 반번호='1' , addr='서울'
where username = '이승기';
update  userTBL7 set 반번호='1' , addr='전남'
where username = '김경호';
update  userTBL7 set 반번호='1' , addr='서울'
where username = '바비킴';
update  userTBL7 set 반번호='2' , addr='경북'
where username = '은지원';
update  userTBL7 set 반번호='2' , addr='경남'
where username = '김범수';
update  userTBL7 set 반번호='2' , addr='경기'
where username = '조관우';
update  userTBL7 set 반번호='2' , addr='경남'
where username = '윤종신';
update  userTBL7 set 반번호='2' , addr='경기'
where username = '조용필';
-- 추가 값입력 확인
select * from userTBL7;
-- 정렬하기
select * from userTBL7
order by 반번호, addr, username; --생략시 asc(오름차순)
--키큰 순위 매기기 
select row_number() over (order by height desc) "키큰순위",
         username, addr, height
from userTBL7;
--키가 같은 경우 이름순으로 오름차순 정렬
select row_number() over (order by height desc, username asc) "키큰순위",
         username, addr, height
from userTBL7;
  • PARTITION BY 칼럼명 
--지역별 키큰 순위 매기기
select row_number() over (partition by addr order by height desc, username asc) "키큰순위",
         username, addr, height
from userTBL7;
--반 별 키큰 순위 매기기
select row_number() over (partition by 반번호 order by height desc, username asc) "키큰순위",
         username, 반번호, addr, height
from userTBL7;
  • DENSE_RANK()
-- 키가 같은 경우 동일 순위 만들기 
select DENSE_RANK() over ( order by height desc) "키큰순위",
         username, 반번호, addr, height
from userTBL7; -- 3등존재
  • RANK() 
select RANK() over ( order by height desc) "반번호",
         username, 반번호, addr, height
from userTBL7; -- 3등 미존재
  • NTILE()
-- 키를 기준으로 반별로 나누기
select NTILE(2) over ( order by height desc) "반번호",
         username, addr, height
from userTBL7; -- 2개의 반으로 나누기
-- 이름 기준으로 반별로 나누기
select NTILE(4) over ( order by username asc) "반번호",
         username,  addr, height
from userTBL7; -- 4개의 반으로 나누기
--뷰테이블 만들기
create view v_userTBL7_height
as
select row_number() over (order by height desc, username asc) "키큰순위",
         username, addr, height
from userTBL7;
--뷰테이블 생성확인
select * from v_userTBL7_height;
-- 5번째 키가 큰 순위까지 보이기
select * from v_userTBL7_height
where 키큰순위 <= 5;

JOIN

--사용할 테이블
select * from userTBL;
select * from buyTBL;
  • INNER JOIN
select * from userTBL u join buyTBL b
on u.고객이름 = b. 고객이름; 
select u.고객이름, 구매한물건, 단가, 수량 
from userTBL u join buyTBL b 
-- 컬럼을 정할때 조인한 테이블이 어디인지 명시필요
on u.고객이름 = b. 고객이름;

--LEFT OUT JOIN

select u.고객이름, 구매한물건, 단가, 수량 
from userTBL u left join buyTBL b 
on u.고객이름 = b. 고객이름; 
--가입만 진행한 고객의 수 
select count(*) "가입만 한 고객" 
from userTBL u left join buyTBL b 
on u.고객이름 = b. 고객이름
where 구매한물건 is null; 
--물건을 구매한 이력이 있는 고객 수
select count(distinct(u.고객이름)) "구입 이력이 있는 고객" 
from userTBL u left join buyTBL b 
on u.고객이름 = b. 고객이름
where 구매한물건 is not null;
  • 서브쿼리를 사용하여 위와 물건을 구매한 이력이 있는 고객 조인테이블 만들기
    : 3가지 방법
-- LEFT JOIN 사용
select distinct u.고객이름, 주소
from userTBL u left join buyTBL b 
on u.고객이름 = b. 고객이름
where 구매한물건 is not null; 
-- 서브쿼리문 사용(EXISTS) : 데이터가 많을때 사용
select u.고객이름, 주소 
from userTBL u 
where exists 
    (
        select * from buyTBL b
        where u.고객이름 = b.고객이름
    ) ;
-- 수업때 배운 서브쿼리 사용 (where in) : 데이터가 적을때 사용
select u.고객이름, 주소 
from userTBL u 
where 고객이름 in
    (
        select distinct u.고객이름 
        from buyTBL b join userTBL u 
        on u.고객이름 = b.고객이름
    ) ;    
--주소와 연락처를 합치기( 두개의 컬럼을 하나로 합치기)
select 주소 | | 연락처 as "연락처" from userTBL;
--서울에 살고있는 고객의 구매한물건을 구해라(Null 값은 제외)
select distinct 구매한물건 "구입 이력이 있는 고객" 
from userTBL u left join buyTBL b 
on u.고객이름 = b. 고객이름
where 주소='서울' and 구매한물건 is not null; 
-- 여러 테이블의 INNER JOIN
create table stdTBL
(
    stdName NCHAR(5) NOT NULL PRIMARY KEY ,
    addr      NCHAR(2) NOT NULL
);

create table clubTBL
(
    clubName NCHAR(5) NOT NULL PRIMARY KEY,
    roomNo   NCHAR(4) NOT NULL
);

create sequence stdclubSEQ;

create table stdclubTBL
(
    idNum   NUMBER(5)   NOT NULL PRIMARY KEY,
    stdName  NCHAR(5)    NOT NULL ,
    clubName    NCHAR(5)    NOT NULL ,
    FOREIGN KEY (stdName) REFERENCES stdTBL(stdName),
    FOREIGN KEY (clubName) REFERENCES clubTBL(clubName)
);

INSERT INTO stdTBL VALUES('김범수', '경남');
INSERT INTO stdTBL VALUES('성시경', '서울');
INSERT INTO stdTBL VALUES('조용필', '경기');
INSERT INTO stdTBL VALUES('은지원', '경북');
INSERT INTO stdTBL VALUES('바비킴', '서울');
INSERT INTO clubTBL VALUES('수영', '101호');
INSERT INTO clubTBL VALUES('바둑', '102호');
INSERT INTO clubTBL VALUES('축구', '103호');
INSERT INTO clubTBL VALUES('봉사', '104호');
INSERT INTO stdclubTBL VALUES(stdclubSEQ.NEXTVAL,'김범수', '바둑');
INSERT INTO stdclubTBL VALUES(stdclubSEQ.NEXTVAL,'김범수', '축구');
INSERT INTO stdclubTBL VALUES(stdclubSEQ.NEXTVAL,'조용필', '축구');
INSERT INTO stdclubTBL VALUES(stdclubSEQ.NEXTVAL,'은지원', '축구');
INSERT INTO stdclubTBL VALUES(stdclubSEQ.NEXTVAL,'은지원', '봉사');
INSERT INTO stdclubTBL VALUES(stdclubSEQ.NEXTVAL,'바비킴', '봉사');

select * from stdTBL;
select * from clubTBL;
select * from stdclubTBL;
-- STDNAME을 기준으로 정렬하며 INNER JOIN
select * from stdTBL st join stdclubTBL sc
on st.STDNAME = sc.STDNAME join clubTBL ct
on sc.CLUBNAME = ct.CLUBNAME
order by st.STDNAME;
-- CLUBNAME을 기준으로 정렬하며 INNER JOIN
select  ct.ClubNAME, roomNo , st.stdName, addr 
from stdTBL st join stdclubTBL sc
on st.STDNAME = sc.STDNAME join clubTBL ct
on sc.CLUBNAME = ct.CLUBNAME
order by ct.CLUBNAME;
-- LEFT OUTER JOIN
select  * 
from stdTBL st left join stdclubTBL sc
on st.STDNAME = sc.STDNAME left join clubTBL ct
on sc.CLUBNAME = ct.CLUBNAME
order by ct.CLUBNAME;
-- FULL OUTER JOIN
select  * 
from stdTBL st FULL join stdclubTBL sc
on st.STDNAME = sc.STDNAME FULL join clubTBL ct
on sc.CLUBNAME = ct.CLUBNAME
order by ct.CLUBNAME;
  • CROSS JOIN
    :테이블 1의 레코드 * 테이블 1의 레코드
--표현방법 1
select * from stdTBL
cross JOIN stdclubTBL;
--표현방법 2
select * from stdTBL, stdclubTBL;
  • UNION /ALL, NOT IN, IN ( 시험에 출제 多 )
    : 두 쿼리의 결과를 행으로 합치는 것
    : 두 쿼리 각각 컬럼의 갯수가 같아야한다.
    : 두 쿼리 각각 컬럼의 데이터 형은 호환되어야 한다.
    : UnIon 은 결과를 중복제거한다.
    : Union All 은 모든 값을 출력한다.
select stdName, addr from stdTBL
UNION
select clubname, roomNo from clubTBL;

select stdName, addr from stdTBL
UNION ALL
select clubname, roomNo from clubTBL;
  • CASE
    : 다중 분기문 (2개 이상의 IF문 사용시 대체 가능) 
select stdName, addr, 
    case 
        when addr='서울' or addr='경기'
            then '수도권'
    else
        '비수도권'
    end as "지역구분"
from stdTBL;
--case문을 활용해 키를 기준으로 4개의 분류로 나누기
select USERNAME, HEIGHT, 
    case 
        when HEIGHT >= 180 then '매우크다'
        when HEIGHT >= 175 then '크다'
        when  HEIGHT >= 170 then '보통'
     else
        '작다'
    end as "키구분"
from userTBL7
order by Height desc;
  • 테이블
    : 제약조건이란 무결성을 지키기 위한 제한된 조건
    : 테이블 삭제는 Drop문 사용
    : NULL = NULL값을 허용, NOT NULL = NULL값을 허용 X(입력불가)
profile
서창민
Back-end Developer Preparation Students
이전 포스트

SQL_7일차

다음 포스트

SQL_9일차

0개의 댓글