SQL (1)
* 뉴렉처의 오라클 SQL 강의를 듣고 작성한 글입니다.
SQL(Structured Query Language) : DBMS에게 질의하는 명령어,
DBMS: Database + Management System
Database와 DBMS의 개요
중복을 없애는 방법으로 결함을 줄인다.
DDL : create/alter/drop
DML : select/insert/update/delete
DCL : grant/revoke
오라클 SQL 데이터 형식 (문자)
영문자 : 1byte
한글 : 3byte
- CHAR (모든 데이터 길이가 같을 경우 적합)
ex) PHONE CHAR(13) / 010-1234-5678
BIRTHDAT CHAR(10) / 2000-01-02
- VARCHAR (가변길이에 적합하다.)
ex) ID VARCHAR(50)
- NCHAR (고정적으로 전세계 언어 수용 가능, 많은 데이터를 쓰게 된다.)
ex) GENDER NCHAR(2)
- NVARCHAR (가변적으로 전세계 언어 수용 가능, 많은 데이터를 쓰게 된다.)
- LONG (2Gbyte)
- CLOB 대용량 텍스트 데이터 타입 (최대 4Gbyte), 요즘 LONG 대신 많이 쓰인다.
- NCLOB 대용량 텍스트 유니코드 데이터 타입 (최대 4Gbyte)
오라클 SQL 데이터 형식 (숫자, 날짜 형식)
NUMBER : 정수와 실수 모두 사용 가능
NUMBER(4) : 최대 4자로 이루어진 숫자
NUMBER(6,2) : 소수점 2자리를 포함하는 최대 6자리의 숫자 (소수점 둘째 자리에서 반올림)
NUMBER(6,-2) : 소수점 -2자리에서 반올림하는 최대 6자리의 숫자
NUMBER NUMBER(38,*)
NUMBER(*,5) NUMBER(38,5)
ex) AGE NUMBER(3)
DATE : 년월일까지
TIMESTAMP : 년월일시분초까지
NLS_TIMESTAMP_FORMAT 파라미터에 명시된 값을 따름
TIMESTAMP WITH TIME ZONE NLS_TIMESTAMP_TZ_FORMAT 파라미터에 명시된 값을 따름
TIMESTAMP WITH LOCAL TIME ZONE NLS_TIMESTAMP_FORMAT 파라미터에 명시된 값을 따름
SELECT * FROM NLS_DATABASE_PARAMETERS
테이블 수정
테이블 생성하기 - CREATE / DROP / ALTER
- 수정
ALTER TABLE 테이블명 MODIFY 수정 내용;
ex) ALTER TABLE MEMBER MODIFY ID NVARCHAR(50);
- 삭제
ALTER TABLE 테이블명 DROP 삭제 내용;
ex) ALTER TABLE MEMBER DROP COLUMN AGE;
- 추가
ALTER TABLE 테이블명 ADD 추가 내용;
ALTER TABLER MEMBER ADD EMAIL VARCHAR(50);
쿼리문을 작성하지 않아도 SQL Developer에서 테이블 편집 도구를 클릭하여
데이터 유형, 크기 등을 추가, 삭제, 변경할 수 있다.
데이터 조작 (INSERT/SELECT)
INSERT INTO <테이블> VALUES <값목록>
모든 필드 값을 입력하기
ex) INSERT INTO MEMBER VALUES ('newlec', '111', '홍길동' ....);
원하는 필드만, 원하는 순서대로 입력하기
ex) INSERT INTO MEMBER(ID, PWD) VALUES('newlec', '111');
INSERT INTO MEMBER(PWD, ID) VALUES('111', 'newlec');
안 들어간 컬럼에는 NULL 값이 들어간다.
SELECT * FROM MEMBER; = 모든 컬럼을 꺼내온다.
데이터 조작하기 update/delete
UPDATE MEMBER SET PWD='111' WHERE ID='dragon';
트랜잭션 처리를 위한 COMMIT/ROLLBACK
트랜잭션 : 업무 실행단위/논리 명령단위/ 개념상의...
COMMIT : 모든 세션에서 볼 수 있게 된다. 트랜잭션을 완료.
ROLLBACK : 철회하다. 트랜잭션을 보류.
연산을 통한 데이터 조회 (산술연산자)
ex) 공지사항의 조회수를 조회하시오. 단 기존 값에 1을 더해서 조회하시오.
SELECT HIT+1 HIT FROM NOTICE;
SELECT 1+'3' FROM DUAL(더미테이블, 의미없는 테이블); = 4
문자열을 더하기 위한 연산자 : ||
SELECT 1 || '3' FROM DUAL; = 13
SELECT 1 + 'a' FROM DUAL; = 오류
ex) 모든 회원의 이름을 조회하시오. 단 이름은 ID를 붙여서 나타내시오. 예: 홍길동(hong)
SELECT NAME || '(' || ID || ')' FROM MEMBER;
데이터 필터링 위한 비교연산자
ex) 게시글 중에서 작성자가 'newlec'인 게시글만 조회하시오.
SELECT * FROM NOTICE WHERE WRITER_ID = 'newlec';
ex) 게시글 중에서 조회수가 3이 넘는 글만 조회하시오.
SELECT * FROM NOTICE WHERE HIT > 3;
ex) 게시글 중에서 내용을 입력하지 않은 게시글을 조회하시오.
SELECT * FROM NOTICE WHERE CONTENT = 'NULL' (X)
SELECT * FROM NOTICE WHERE CONTENT IS NULL;
관계연산자
NOT, AND, OR, BETWEEN, IN
ex) 조회수가 0,1,2인 게시글을 조회하시오.
SELECT * FROM NOTICE WHERE HIT=0 OR HIT=1 OR HIT=2;
SELECT * FROM NOTICE WHERE 0 <= HIT AND HIT <= 2; (조금 더 간단한 답)
SELECT * FROM NOTICE WHERE HIT BETWEEN 0 AND 2 (더 간단한 답)
ex) 조회수가 0,2,7인 게시글을 조회하시오.
SELECT * FROM NOTICE WHERE HIT IN (0,2,7);
ex) 조회수가 0,2,7이 아닌 게시글을 조회하시오.
SELECT * FROM NOTICE WHERE HIT NOT IN (0,2,7);
패턴 비교 연산자
ex) 회원 중에서 '박'씨 성을 조회하시오.
SELECT * FROM MEMBER WHERE NAME LIKE '박%'; (=을 쓰면 안 된다.)
ex) 회원 중에서 '박'씨이고 이름이 외자인 회원을 조회하시오.
SELECT * FROM MEMBER WHERE NAME LIKE '박_';
ex) 회원 중에서 '박'씨 성을 제외한 회원을 조회하시오.
SELECT * FROM MEMBER WHERE NAME NOT LIKE '박%';
ex) 회원 중에서 이름에 '도'자가 들어간 회원을 조회하시오.
SELECT * FROM MEMBER WHERE NAME LIKE '%도%';
정규식을 이용한 패턴 연산
Regular Espression : 사람들이 만들어둔 패턴 문자열을 볼 수 있는 사이트다.
제목에 번화번호가 포함된 게시글을 조회하시오.
SELECT * FROM NOTICE WHERE REGEXP_LIKE(TITLE, '^01[016-9] ~)
ROWNUM 그리고 행 제안하기
ex) 회원 목록에서 상위 5명만 조회하시오.
SELECT * FROM MEMBER WHERE ROWNUM BETWEEN 1 AND 5;
//다른 번호 구하기
SELECT * FROM (SELECT ROWNUM NUM, MEMBER.*(멤버테이블에 있는 모든 것) FROM MAMBER)
WHERE NUM BETWEEN 1 AND 5
ex) 회원 목록에서 6~10번째 회원을 구하시오.
SELECT * FROM (SELECT ROWNUM NUM, NOTICE.* FROM NOTICE)
WHERE NUM BETWEEN 6 AND 10
중복 값 제거하기
DISTINCT
SELECT DISTINCT AGE FROM MEMBER;
DISTINCT는 한 컬럼에만 사용 가능하다.
문자열 내장 함수
1) 문자열 추출함수 SUBSTR(문자열, 시작위치, 길이)
ex) 모든 학생의 이름과 출생 월만을 조회하시오
SELECT NAME, SUBSTR(BIRTHDAY , 6, 2) MONTH FROM MEMBER;
ex) 회원 중에서 전화번호가 011로 시작하는 회원의 모든 정보를 출력하세요.
SELECT * FROM MEMBER WHERE SUBSTR(PHONE, 1, 3) = '011';
SELECT * FROM MEMBER WHERE PHONE LIKE '011%';
ex) 회원 중에서 생년 월이 7,8,9월인 회원의 모든 정보를 출력하시오.
SELECT * FROM MEMBER WHERE SUBSTR(BIRTHDAY, 6, 2) IN ('07','08','09');
ex) 전화번호를 등록하지 않은 회원 중에서 생년 월이 7,8,9월인 회원의 모든 정보를 출력하시오.
SELECT * FROM MEMBER WHERE PHONE IS NULL AND SUBSTR(BIRTHDAY, 6, 2) IN ('07','08','09');
2) 문자열 덧셈 함수
SELECT CONCAT('홍', '길동') FROM DUAL;
3) 문자열 트림 함수
SELECT LTRIM(" HELLO ') FROM DUAL; (왼쪽)
SELECT RTRIM(" HELLO ') FROM DUAL; (오른쪽)
SELECT TRIM(" HELLO ') FROM DUAL; (양쪽)
4) 문자열 소문자 또는 대문자로 변경하기
SELECT LOWER('NeWIEC') FROM DUAL;
SELECT UPPER('NeWIEC') FROM DUAL;
5) 문자열 대치 함수 REPLACE(문자열, 찾는 문자열, 대치할 문자열) / TRANSLATE()
SELECT REPLACE('WHERE WE ARE', 'WE', 'YOU') FROM DUAL;
SELECT TRANSLATE('WHERE WE ARE', 'WE', 'YOU') FROM DUAL;