SQL 기본(SQL developer 사용하기)

테이블

---

테이블이란 데이터베이스의 구조를 정의하는 방식을 의미한다. sql에서 테이블은 열과 행으로 이뤄진 표를 생각하면 된다. 각 열은 하위 데이터 종류를 나타내고 행은 각 열의 데이터를 담은 하나의 기본단위로 레코드라고 부른다.

쉽게 생각하면 테이블은 표, 레코드는 표를 구성하는 하나하나의 데이터이다.

 

테이블과 레코드를 만들어보기 위해 오라클 db와 sql developer을 사용했다.

이 프로그램은 gui에서 작성할 수 있어서 학습용으로 용이하다.

 

sql과 developer에 대한 주의사항

• 작성 시, 자동완성 기능 지원
• 대소문자를 구분하지 않으나, 컨벤션으로 기본 문법은 대문자, 개발자가 직접작성하는 부분은 소문자를 사용한다.
• 작성 후에는 상단의 실행버튼이나 단축키 ctrl enter를 눌러 실행한다.
• 작성 후에 디렉토리 익스플로러를 새로고침 시, 변경사항이 갱신된다.
• 워크시트에서 특정 열을 선택하고 실행하면 그 부분만 실행된다. 전체 실행하려면 전체 블록 실행  혹은 워크시트 전체 실행(f5)을 해야 한다.
• 코드 중 특정 쿼리문이 실패하더라도 다음 구문 실행
• 데이터 추가할 때 문자열 리터럴로 작은 따옴표 사용한다. 큰 따옴표는 에러가 난다.

 

테이블 생성, 검색 삭제. 커밋, 주석

-- 테이블 생성
-- 명령어 대상타입 이름 (
-- 열 이름 열 타입(최대 글자 제한)
)
CREATE TABLE book(
book_id NUMBER(4),
book_name VARCHAR2(20),
book_location VARCHAR2(20)
);

-- 모든 테이블 검색
SELECT * FROM tab;

-- 테이블 삭제
DROP TABLE users;
DROP TABLE book;

-- 커밋
commit;

• tab 테이블은 모든 테이블의 이름과 테이블 타입, 클러스터 식별자 등을 저장하는 시스템 테이블이다.
• 테이블 삭제나 생성은 한번에 하나씩만 가능하다.
• commit | 워크 시트 상에서 db 변경 사항을 최종적으로 반영한다. GUI환경에서는 커밋이 없어도 반영된 것처럼 보이지만 실제 반영을 위해서는 커밋이 필요하다. 단, 테이블생성이나 삭제 같은건 커밋이 필요없고 INSERT,UPDATE 등은 필요하다. 
  •  sql 워크시트는 기존에 gui 상 존재하는 테이블들에게 명령문을 수행함. 그러니, 중복 생성 에러 조심

• 주석 처리 | "--" 으로 처리한다

 

제약 조건

테이블의 컬럼에는 데이터의 무결성과 일관성을 위해서 최대 글자 길이 제한 같은 조건을 거는 경우가 많다.

그 중에서 CONTRAINT를 알아보자.

아까의 book 테이블에서 id 컬럼은 고유한 값을 가져야한다.

이럴 때는 다음같이 작성한다.

-- 제약조건을 가진 테이블 생성
CREATE TABLE book(
book_id NUMBER(4) CONSTRAINT pk_book_book_id PRIMARY KEY,
book_name VARCHAR2(20),
book_location VARCHAR2(20)
);

• 형식 | 컬럼 데이터타입 CONTRAINT 제약조건명 제약조건 종류
  • 제한조건명 | 복잡한 제약 조건의 조합을 직관적으로 이해할 수 있도록 명칭을 부여한다. 의도를 이해하고 문서화하며 디버깅하는 데 용이하다. 컨벤션의 하나는 "제약조건명의 약자_테이블_컬럼명" 으로 작성한다.
  • 제약조건 종류
    • Primary Key (기본 키):
      • 테이블의 레코드 간에 고유한 값으로 지정한다.
      • 기본 키 제약 조건은 NULL이나 중복 값을 허용하지 않는다.
    • Foreign Key (외래 키):
      • 다른 테이블의 기본 키를 참조하는 열을 지정한다.
      • 외래 키는 참조된 테이블의 기본 키 값이나 NULL 값을 참조할 수 있다.
    • Unique Constraint (고유 제약 조건):
      • 기본 키와 유사하지만, NULL 값을 허용한다.
    • Check Constraint (검사 제약 조건):
      • 특정 열에 저장될 수 있는 값의 범위나 조건을 지정한다.
      • 예시 | 나이 열에는 음수 값이나 100보다 큰 값이 들어갈 수 없도록 제약을 설정
      1. Not Null Constraint (NULL 제약 조건):
         • 특정 열에 NULL 값을 허용하지 않도록 지정한다.

 

레코드

---

테이블을 구성하는 데이터의 집합 단위로, SQL에서는 행에 해당한다.

 

sequence

오라클 db의 객체 중 하나로 값이 자동으로 증가하거나 감소한다.

id처럼 고유한 기본 키를 설정할 때 일반적으로 사용한다.

-- 시퀀스 생성
CREATE SEQUENCE book_seq;

-- 시퀀스 삭제
DROP SEQUENCE book_seq;

레코드 추가, 수정, 삭제

--레코드 추가
-- 문자열 입력 시, 작은 따옴표 사용할 것
INSERT INTO book(book_id,book_name,book_location)
VALUES (book_seq.nextval,'book1','001-00005');



--데이터 수정
-- book 테이블에서 이름이 book2인 레코드를 찾아서 book_location를 001-00099로 변경

UPDATE book SET book_location = '001-00005'

WHERE book_name ='book2';



-- 데이터 삭제
DELETE FROM book
WHERE book_id=2;

-- 찾는 행이 없으면 오류 없이 업데이트가 일어나지 않음

COMMIT;

 

"SQL 오류: ORA-01950: 테이블스페이스 'USERS' 에 대한 권한이 없습니다. " 라고 뜰 때 해결법
이 오류는 공통 사용자 계정에 insert 권한이 없어서 발생한다. 이 권한은 제한적으로 부여하거나 전적인 삽입 권한을 줄 수 있는데, 후자를 선택하면 아래처럼 해결한다.


명령줄 프롬프트(cmd)를 실행해서 sqlplus 실행 후 시스템 계정으로 접속한다.
다음 명령을 실행한다.
ALTER USER [유저명] DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;

 

테이블 내 특정 레코드 검색

-- 테이블 내 레코드의 모든 컬럼 검색
SELECT * FROM book;

-- 테이블 내 레코드의 특정 컬럼만 추출
SELECT book_id ,book_name FROM book;

-- 추출한 컬럼명을 변경
SELECT book_name AS 책이름,book_id AS 고유_키 FROM book;

-- 레코드 검색 조건 설정
SELECT * FROM book
WHERE book.book_id =4;

 

쿼리 조건문

-- 조건문 OR
SELECT * FROM book
WHERE book.book_id>5 OR book.book_name = 'book2';
-- 컬럼 검색 시, 테이블과 구분하기 위해 접두사로 테이블명 사용 가능

-- 조건문 between and
SELECT * FROM book
WHERE book_id BETWEEN 2 AND 5;

-- 조건문 like
select * from book
where book.book_name LIKE '%ok%';

-- 부분일치를 찾을 때 사용
-- %는 와읻드 카드, 모든 문자를 갯수 상관없이 검색

 

정렬

-- 정렬
SELECT * FROM book ORDER BY book_id DESC;

-- 문자열도 정렬가능
SELECT * FROM book ORDER BY book.book_location ASC;

 

 

참고 자료

참고. sql developer 단축키 설정

https://backhero.tistory.com/3