20.07.14 SQL 명령어

문법

-- : 한줄 주석

 

/*

    여러줄 주석

*/

 

SQL 명령을 입력할 때 명령이 끝났다라는 것을 알리기 위해 ; 붙여준다.

Ctrl + Enter : 현재 커서가 있는 곳의 명령문을 DBMS로 보내고 결과를 받기

 

-- tab : sys : 관리하는 table 중에 tab이 있다.

 

SELECT * < *은 모든 칼럼

 

대문자로 쓰는 애들은 KEYWORD

 

--------------

환경 설정

도구 > 환경설정 > UTF-8

도구 > 환경설정 > 코드편집기 > 글꼴 (디투코딩)

도구 > 환경설정 > 코드편집기 > 행여백 > 행번호 표시 체크

---

GIT IGNORE

---

SELECT (CRUD의 READ)

-- 현재 오라클에 접속된 사용자(sys)가 관리하는 table들이 있는데

-- 그 중에서 tab이라는 이름의 table정보를 가져와서 나에게 보여달라

-- 오라클의 tab table은 현재 접속된 사용자가 관리하는

-- DB Object(객체)들의 정보를 보관하고 있는 table

SELECT *

FROM tab;

 

-- ALL_ALL_TABLES 오라클 system 데이터 사전의 자세한 정보를 보관하는 table

SELECT *

FROM all_all_tables;

-- SELCET 키워드는 FROM 절을 포함하는 명령문 형태로 작성을 하며

-- DBMS가 보관하고 있는 데이터를 table형식으로  보여달라 라는 명령

-- DBMS의 DML(Database Manuplation Lang)중에서

--  Read(조회)를 수행하는 명령

--  CRUD 중에서 R : Read, Retrive 를 수행하는 명령문

SELECT *

FROM 주소록;

 

• 데이터를 Table 형식으로 보여줄 때 모든 칼럼을 다 포함하여 보여달라
• SELECTION type으로 table 보기

SELECT * FROM tbl_student;

projection 지정

• SELECT문을 수행할 때 보고자하는 칼럼들을 나열하는 것

SELECT

    st_num,

    st_dpet,

    st_name,

    st_age

FROM tbl_student;

SELECT 문을 이용한 4칙연산 구현

SELECT 3 + 4 FROM DUAL;

SELECT 30 * 40 FROM DUAL;

SELECT 30 / 2 FROM DUAL;

SELECT 30 - 10 FROM DUAL;

---

사용자를 추가하는 순서

1. table space : 데이터를 저장할 물리적 공간을 설정
2. user : 사용자를 생성하고, 물리적 저장공간과 연결

---

TableSpace 생성 (Create)

• TableSpace는 오라클에서 Data를 저장할 물리적 공간을 설정하는 것
• myTS : 앞으로 SQL을 통해서 사용할 TableSpace의 Alis(이름)
• '.../MyTS.dbf' : 저장할 파일 이름
• Size : 오라클에서는 성능의 효율성을 주기위해
• 일단 빈 공간을 일정 부분 설정하게 된다.
• 크기는 최초에 저장할 데이터의 크기등을 계산하여 설계하고 설정한다.
• 너무 작으면, 효율성이 떨어지고, 너무 크면 불필요한 공간을 낭비한다.

AUTO...NEXT

CREATE TABLESPACE myTS

DATAFILE 'C:/bizwork/workspace/oracle_data/myTS.dbf'

SIZE 1M AUTOEXTEND ON NEXT 500K;

• 만약 초기에 지정한 Size공간에 데이터가 가득 차면
• 자동으로 용량을 늘려서 저장할 수 있도록 만들어라

 

• Size의 1M : 기본크기를 1024 * 1024 byte 크기로 지정하라.
• Size를 지정할 때 1MB라고 하지 않는다.
• Next 500K : 자동으로 확장 (늘리기)를 1024 * 500 크기로 설정
• Next를 지정할 때 500KB라고 하지 않는다.

새로 만들면 윈도우에서는 파일이 만들어지는데

오라클 입장에서는 파일이 아니고 빈공간(저장할 공간)

DDL (Data Definition Language) :

• CREATE로 시작되는 명령문 : 데이터 선언, 생성( 추가와는 다름) (DBA가 사용)

---

질의작성기에서 코드를 작성할 때 약속

• DBMS의 SQL문은 특별한 일부 경우를 제외하고 대소문자 구별을 하지 않는다.
• DBMS, SQL, 오라클과 관련된 키워드는 모두 대문자로 작성할 것
• 변수, 값, 내용 등은 소문자로 사용하며
• 특별히 대소문자를 구분해야하는 경우는 별도 공지를 할 것

 

DROP :

• DDL명령의 CREATE와 반대되는 개념의 명령문
• DROP 명령은 데이터를 물리적으로 완전 삭제하는 개념이므로 매우 신중하게 사용해야 한다.

DROP TABLESPACE myTS -- myTS tableSpace를 삭제하면서

INCLUDING CONTENTS AND DATAFILES -- 연관된 정보와 data file도 같이 삭제하고

CASCADE CONSTRAINTS; -- 그리고 설정된 권한, 역할 등이 있으면 그들도 같이 삭제하라. (내용 등을 같이 삭제하라는)

 

DCL : Data Control Language ( DBA 가 쓰는)

• 새로운 사용자를 만들면 기본값이 모두 가능한 DBMS가 있고
• 불가능한 DBMS가 있다 <- 불가능한 Oracle
• 오라클은 아예 접속 자체가 안된다.
• 기본 값은 사용자 CRUD할 권한이 없다.  보안때문에 접속도 안되는 경우가 있음. 회사마다 policy가 있어서 복잡.
• 우리가(DBA) 사용자한테 권한을 세세하게 부여해줘야 한다.

 

• 권한 부여 GRANT

GRANT CONNECT TO user1; // 로그인만 할 수 있도록 권한을 부여

• 권한 제거 REVOKE

REVOKE CONNECT FROM user1; // 로그인 할 수 있는 권한 제거

GRANT 와 REVOKE < 한 형제간

• RESOURCE : 오라클에서 유저에게 줄 수 있는 권한 중 상당히 많은 일을 수행할 수 있는 권한
• 현재 시스템에 설치된 모든 TableSpace를 대상으로 무제한(TableSpace가 허용하는 범위) 저장이 가능한 권한
• RESOURCE 권한은 Standard, Enterprice DBMS에서는 함부로 부여해서는 안된다.
• CONNECT와 RESOURCE 권한을 부여하게 되면 거의 DBA 수준의 권한을 갖게된다.

GRANT CONNECT, RESOURCE TO user1;

 

• DBA 권한(Roll)은 SysDBA보다 한단계 낮은 권한 등급을 가지며
• 일반적으로 자신이 생성한 Table등 DB Object에만 접근하여 명령을 수행한다.

>>>> 관리자.sql🙂

---

DB에 데이터를 저장하기

• DB를 대상으로 업무를 수행할 때 데이터가 있어야만 여러가지 일을 수행할 수 있다.
• CRUD 중에서 가장 먼저 수행해야 할 명령이 Create형 명령
• DML CRUD Create는 DDL CREATE 명령과 구분을 해야한다.

 

DDL 의 CREATE ( DBA가 쓰는)

• 생성이라는 개념이고 운영체제와 밀접한 관련이 있거나, 물리적인 요소가 많다.
• CREATE TABLESPACE 저장공간,
• CREATE USER 사용자 정도이며 Schema 등을 생성하는 명령 절차

 

DML (Data Manipulation Language, Data Management Language)에서의 Create

• 물리적 저장공간에 실제 발생된 Data를 추가 저장하기
• 아직 저장되지 않은 논리적 개념의 데이터를 로컬 스토리에 보관하는 개념
• DML의 Create형 명령 : "INSERT"

 

RDBMS(Relationship DataBase Management System, 관계형 데이터베이스 시스템)

• 데이터를 추가하려면, 먼저 데이터 저장 공간에 대한 정의를 수행해야한다.
• 데이터 저장공간을 논리적으로 Entity, 물리적으로는 TABLE이라고 한다.

Entity로 설계 (논리적)

↓

 

TABLE 물리적

TABLE

• 표준 SQL(ANSI SQL)에서 데이터를 추가하는 저장 공간
• 저장할 데이터의 각 필드(칼럼) 항목의 type을 결정
• 저장할 데이터의 최대 길이(크기)를 결정
• 데이터의 Key라는 것을 지정

 

PRIMARY KEY

• 데이터의 Key중에 가장 중요한 항목
• 데이터를 조회할 때 이 항목으로 조건으로 부여하면 유일한 값이 추출되는 항목
• PK는 절대 중복값이 있어서는 안된다.
• PK는 절대 NULL 값이 있어서는 안된다.
• 후보키 : 칼럼들중에 PK 하나 골라 내고 싶을 때,
• 후보키를 전체 묶어서 슈퍼키라고 말한다.
•  

Table 명명 규칙

• 보통 tbl_ 접두사로 시작한다.
• snake_case 형식으로 이름 지정

 

칼럼(Field) 명명 규칙

• 보통 table이름을 줄여서 접두사 시작
• snake_case 형식으로 이름 지정

 

문자열 칼럼의 type

• CHAR, VARCHAR2
• CHAR : 고정길이 문자열, 칼럼에 저장되는 데이터의 길이가 모두 같은 경우에 주로 사용이 된다.
• 저장되는 데이터가 설정한 크기보다 작으면 남는 공간을 공백으로 채운다

 

• VARCHAR2 : 가변길이 문자열, 칼럼에 저장되는 데이터의 길이가 일정하지 않을  때,
• 저장되는 데이터가 설정한 크기보다 작으면 저장공간을 줄여서 저장
• 저장되는 데이터가 설정한 크기보다 크면 오류 발생

DML의 Create

INSERT INTO tbl_student -- tbl_student table에 데이터를 추가하겠다.

    (st_num, st_name, st_dept, st_grade, st_tel, st_addr, st_age) -- 칼럼들 나열

VALUES

    ('10001','홍길동','무역학과',3,'010-111-1111','서울특별시',33); -- 데이터들

 

데이터 무결성 유지

• 데이터를  INSERT할 때 혹시 실수로 PK 칼럼에 이미 저장된 데이터를 또 INSERT 하려고 하면 UNIQUE 오류가 발생하면서 데이터를 저장하지 않는다.
• 이로써 PK 칼럼에 값이 중복되는 것을 방지하여 데이터 무결성을 유지!

 

추출하여 확인 : 조회(Reatrive), 읽기(Read)

SELECT *

FROM tbl_student ;

 

-- 두개의 칼럼을 나열했는데 VALUES를 1개만 지정하면 INSERT 오류 발생

INSERT INTO tbl_student (st_num, st_name) VALUES('10007');

-- 두개의 칼럼을 나열했는데 VALUSE에 3개를 지정하면 INSERT 오류 발생

INSERT INTO tbl_student (st_num, st_name) VALUES('10007','장길산',33);

-- 학번만 데이터를 지정하고 INSERT를 수행

-- st_name 칼럼이 NOT NULL constraint 설정이 되어 있기 때문에

-- st_name 칼럼과 데이터는 반드시 지정해서 INSERT를 수행해야 한다.

INSERT INTO tbl_student (st_num) VALUES('10007');

-- PK 칼럼과 NOT NULL 칼럼은 반드시 데이터를 지정해서 INSERT를 수행해야 한다.

INSERT INTO tbl_student (st_num, st_name) VALUES('10007','이자홍');

CRUD에서 Update

-- tbl_student table에 있는 모든 데이터(모든 행, row, record)의

-- st_dept 칼럼의 값을 컴공과로 변경해라

-- 데이터의 개수에 관계없이 명령이 수행되어 버린다.

-- 아래의 UPDATE 명령은 매우 위험하고 신중하게 사용해야 한다.

UPDATE tbl_student

SET st_dpet = '컴공과';

• UPDATE 명령은 특별한 경우가 아니면 항상 PK를 기준으로 하자!

UPDATE tbl_student

SET st_dpet = '무역과'

WHERE st_num = '10004'; << PK

 

-- 학생 이름(st_name) 칼럼의 값이 성춘향인 모든 데이터를 찾아서

-- 그 데이터들의 st_dept 칼럼 값을 음악과로 변경해라

-- 이 명령의 결과로 만약 성춘향 학생의 데이터가 1개밖에 없어서

-- 원하는 결과를 얻었더라도, 가급적 사용하지 말아야 하는 명령이다.

-- 어떤 이유로 SELECT 수행했을 때 성춘향 데이터가 1개밖에 보이지 않았더라고

-- 만에 하나 감춰진(보이지 않는) 영역에 성춘향 데이터가 또 존재를 한다면

-- 이 명령을 수행하는 순간 이 table의 데이터는 무결성을 잃게된다.

UPDATE tbl_student

SET st_dpet = '음악과'

WHERE st_name = '성춘향';

CRUD에서 마지막 Delete 명령

• DELETE 명령도 UPDATE 명령과 마찬가지로 반드시 WHERE절을 동반하는 형태로 명령을 수행해야 한다.

DELETE FROM tbl_student

WHERE st_num = '10004';

COMMIT

• INSERT, UPDATE, DELETE 명령을 수행한 후 명령을 확정 짓는 명령
• COMMIT 이후에는 ROLLBACK으로 해당 명령을 취소할 수 없다.
• COMMIT을 하면 하나의 트랜잭션이 된다.

ROLLBACK

• 명령 취소
• INSERT, UPDATE, DELETE 명령 수행을 취소하는 명령

---

LIKE 연산자

• 문자열 칼럼에서 특정한 문자를 포함한 검색 기능을 구현할 때 사용하는 연산자

SELECT *

FROM tbl_student

WHERE st_name LIKE '%몽%';

DESC : 테이블의 구조 확인 명령

DESC tbl_student;

 

문자열 데이터를 저장하는 칼럼

• CHAR
• 데이터의 길이가 고정된 칼럼, 코드(검색하는 용도의 칼럼) 등을 저장하는 칼럼
• VARCHAR2
• 가변 문자열, 데이터의 길이가 일정하지 않은 칼럼
• 크기를 지정할 때 가장 길이가 긴 데이터를 기준으로 삼아서 정한다.
• nVARCAHR2
• 가변 문자열, 유니코드 문자열을 수용하는 칼럼
• 영문자 1글자, 한글등 1글자를 같은 크기로 취급한다.

BETWEEN

• 시작값과 종료값이 포함된 범위를 조회하는 코드는
• BETWEEN 연산자를 사용하여 조회하는 코드로 변환하여 사용할 수 있다.

SELECT st_num, st_name, st_dept, st_grade

FROM tbl_student

WHERE st_num BETWEEN '20010' AND '20020';

>>>> USER1.sql