2020/04/07 - [DB] - [Oracle] SQL 명령어 정리 (1)

데이터 수정하기 : update

• 데이터 수정

-- 모든 행의 PWD를 '111' 로 변경하기
update member set pwd='111';
-- 모든 행의 pwd와 name 을 각각 '111' 과 '홍길동' 으로 변경하기
update member set pwd='111', name='홍길동';
-- 특정 행만 pwd와 name을 각각 '111'과 '홍길동'으로 변경하기
update member set pwd='111', name='홍길동' where id='newlec';
-- ID가 대문자이면 where id 를 대문자로 비교해야한다. 값은 대소문자를 비교하는것 같다.
update member set pwd='1234', name='roel' where id='ROEL';

데이터 삭제하기 : delete

• 데이터 삭제

-- 테이블의 모든 행을 삭제하기
delete member;
-- 특정 행만 삭제하기 ex) ROEL의 유저를 지워라.
delete member where id='ROEL';
commit;

is null , is not null

-- content 가 null 검색
select * from notice where content is null;
-- content 가 not null 검색
select * from notice where content is not null;

문자열 더하기

-- 자바는 310 되는데 sql은 13 이 된다. (형변환이 숫자우선이다.)
SELECT '3'+10 from dual;
-- err : 변환 할 수 없다.
SELECT 'A'+10 from dual;
 
-- 문자열 더하기 연산자
select 'A'|| 10 from dual;
 
-- 모든 회원의 이름을 조회
-- 단 이름은 ID를 붙여서 나타내시오.
select name||id name from member;
SELECT NAME || '(' || ID || ')' FROM MEMBER;

not , and , or , between , in

-- 조회수가 0,1,2인 게시글을 조회하시오 ( = 이 포함됨 )
select * from notice where hit BETWEEN 0 and 2;
-- 부등호가 포함이 안된다면 and 를 사용해야한다.
-- 조회수가 0,2,7인 게시글을 조회하시오
select * from notice where hit in (0,2,7);
-- 조회수가 0,2,7가 아닌 게시글을 조회하시오
select * from notice where hit not in (0,2,7);

like , %, _

--회원 중에서 박씨의 성을 조회하시오
select * from member where name like '박%';
-- 회원 중에 박씨인 외자인 박씨를 조회하시오.
select * from member where name like '박_';
-- 회원 중에 박씨인 성을 제외한 회원을 조회하시오
select * from member where name not like '박%';
-- 회원 중에서 이름에 '도' 자가 들어간 회원을 조회하시오
select * from member where name like '%도%';

정규식

-- 전화번호가 정규식이랑 맞는 사람만 출력
select * from member where REGEXP_LIKE (phone, '01[01]-\d{3,4}-\d{4}');
-- 전화번호가 정규식에 맞지 않는 사람만 출력
select * from member where not REGEXP_LIKE (phone, '01[01]-\d{3,4}-\d{4}');
-- 전화번호가 정규식에 맞지 않는 사람 이면서 성별이 여성인 사람만 출력
select * from member where not
REGEXP_LIKE (phone, '01[01]-\d{3,4}-\d{4}')
and gender like '여성';

function

• length(컬럼) : 문자열 길이
• substr(문자열,시작위치,가져올길이) : 문자열 자르기
• distinct (컬럼) : 중복 제거
• count(컬럼) : 컬럼 갯수

-- 나이 중복을 빼고 출력해라
select distinct(age) from member;
--STR의 1번째 글자부터 4개 자르기
SELECT SUBSTR(STR,1,4) FROM EXAMPLE;
--STR의 5번째 글자부터 5개 자르기
select SUBSTR(STR,5,5) FROM EXAMPLE;
--STR의 10번째 글자부터 끝까지 자르기
select SUBSTR(STR,10) FROM EXAMPLE;
--STR의 뒤에서 3번째 글자부터 3글자 자르기
select SUBSTR(STR,-7,3) FROM EXAMPLE
--STR의 뒤에서 3번째 글자부터 끝까지 자르기
select SUBSTR(STR,-3) FROM EXAMPLE
-- member 테이블에서 중복제거해서 성만 가져오기
select distinct(substr(name,1,1)) name from member;
-- 게시글 전체 수 조회
select count(id) from notice;
-- 작성자 별 게시글 수를 조회하시오
select writer_id, count(id) from notice group by writer_id;
 
-- 가입한 회원의 연령대별 회원 수를 조회하시오 ( 단 , 회원 수를 기준으로 역 정렬 할것)
--age/10 을 하면 자바처럼 정수 나누기 정수를 해도 실수가 나오기때문에 * 10을 해준다.
select
floor(age/10)10 || '대' ages,
count(floor(age/10)10) count
from member
where age is not null
group by floor(age/10)
order by floor(age/10)*10 asc;
 
-- where 절에서는 집계함수를 사용할 수 없다.
-- 회원별 게시글 수를 조회하시오
-- 단, 게시글 수가 2 이하인 레코드만 출력하시오
select writer_id, count(n.id) count from notice n
group by writer_id
having count(n.id) <2;

정렬하기

-- asc: 숫자 0 ~ 큰 수 , desc : 숫자는 null, 큰 수 ~ 0
-- 이름으로 오름차순
select * from member order by name asc;
-- 이름으로 내림차순
select * from member order by name desc;

부 조회 ( 서브 쿼리 )

• 서브쿼리 사용하는 경우 : 구절의 순서를 바꿔야 하는 경우

-- 서브쿼리를 활용해서 rownum를 num으로 만들고, num을 가지고 11부터 20까지 데이터를 가져온다.
select * from
(select rownum num, notice.*
from notice)
where num BETWEEN 11 and 20;
 
-- 가입한지 오래된 10명을 뽑는것
select * from (select * from member
order by regdate asc)
where rownum between 1 and 10;
 
-- 최신 가입한 10명을 뽑아내라.
select * from (select * from member
where regdate is not null
order by regdate desc )
where rownum between 1 and 10;
 
-- 최신 가입한 11부터 20까지의 사람을 뽑아서 가져와라.
select * from
(select rownum num, M.* from
(select * from member order by regdate desc) M)
where num between 11 and 20;
 
-- 나이가 30이상인 회원 목록을 조회
select * from member
where age >= 30;
 
-- 평균 나이 이상인 회원 목록을 조회
select * from member
where age>=(select avg(age) from member);