CS/DB

[DB] Structured Query Language

heehminh 2023. 1. 22. 03:51
반응형

SQL Data Type
*SQL은 대, 소문자 구별하지 않는다!
pure relational algebra - removes all duplicates
multiple relational algebra, SQL - ratains duplicates
relation algebra to SQL

select A1, A2, .., An
from r1, r2, .., rm
where P

sql to relation algebra
→ π A1, A2, .., An ( σ p ( r1 x r2 x .. x rm ) )

// select엔 group by에 쓴 애트리뷰트 + aggregation만 쓸 수 있음 
select A1, A2, sum(A3)
from r1, r2, .., rm
where p
group by A1, A2

→ A1, A2 G sum(A3) ( σ p ( r1 x r2 x .. x rm) )

select A1, sum(A3)
from r1, r2, .., rm
where p
group by A1, A2

→ π A1, sumA3 ( A1, A2 G sum(A3) as sumA3 ( σ p ( r1 x r2 x .. x rm) )
projection 명시를 해주지 않으면 A1, A2, sumA3이 나옴

 

Structured Query Language

DDL (Data-definition Language)
데이터 정의어
CREATE, ALTER, DROP, RENAME, TRUNCATE - 테이블과 같은 데이터 구조를 정의하는데 사용되는 명령어들로 데이터 구조와 관련된 명령어 (생성, 변경, 삭제, 이름변경)


DML (Data-manipulation Language)
데이터 조작어
SELECT - 데이터베이스에 들어있는 데이터를 조회하거나 검색하기 위한 명령어
INSERT, UPDATE, DELETE - 데이터베이스의 테이블에 들어있는 데이터에 변형을 가하는 종류의 명령어


DCL (Data-Control Language)
데이터 제어어
GRANT, REVOKE - 데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령어


TCL (Transaction Control Language)
트랜잭션 제어어
논리적인 작업의 단위를 묶어서 DML에 의해 조작된 결과를 작업단위 별로 제어하는 명령어

DDL (Data-definition Language)

DDL은 rollback이 되지 않는다!

  1. CREATE TABLE: 테이블 생성
  2. ALTER TABLE: 테이블 스키마(등) 관련 변경
  3. DROP TABLE: 테이블 삭제
  4. RENAME: 이름 변경
  5. TRUNCATE: 테이블의 모든 데이터 삭제
  6. COMMENT: 테이블에 설명 추가
  7. CREATE TABLE
CREATE TABLE book // 테이블 이름 (
		bookno NUMBER(5),
		title VARCHAR(50),
		author VARCHAR(10), 
		pubdata DATE
); // **세미콜론!**

Subquery를 이용한 테이블 생성
create table temp_account like account*
Subquery의 결과와 동일한 테이블 생성됨
NOT NULL 제약조건만 상속됨

CREATE TABLE empSALES 
AS 
		// Subquery
		SELECT * FROM emp
		WHERE job = 'SALES';


Data type Description

VARCHAR2(size) 가변길이 문자열 (최대 4000byte)
CHAR(size) 고정길이 문자열 (최대 2000byte)
NUMBER(p, s) 가변길이 숫자, 전체 P자리 중 소수점 이하 S 자리
자리수 지정없으면 NUMBER(38)  
DATE 고정길이 날짜+시간
  • VARCHAR2 와 CHAR의 차이점
  • INT, FLOAT 등의 ANSI Type도 내부적으로 NUMBER(38)로 변환됨
  • 다른 데이터 타입 참고
  1. ALTER TABLE (ROLL BACK의 대상이 아니기때문에 주의하자!)
    1. 컬럼 추가 ADD (컬럼명 datatype)pubs 라는 새로운 칼럼이 옆에 붙음
    2. ALTER TABLE book ADD (pubs VARCHAR2(50));
    3. 컬럼 수정 MODIFY (컬럼명 데이터타입)
    4. ALTER TABLE book MODIFY (title VARCHAR2(100));
    5. 컬럼 삭제 DROP
    6. ALTER TABLE book DROP author;
    *DROP TABLE과 TRUNCATE의 차이점 주목! DROP TABLE-테이블 전체를 삭제하는 것, TRUNCATE- 테이블의 데이터만 삭제하는 것 (스키마와 같은 구조는 살아남음)
    1. 테이블 삭제 DROP TABLE테이블의 모든 데이터 + 스키마 삭제
    2. DROP TABLE book;
    3. (테이블의 모든) 데이터만 삭제
    4. TRUNCATE TABLE book;
    5. RENAME
    6. RENAME book TO article; // 전체테이블을 rename ALTER TABLE book RENAME COLUMN bookno TO bookid;

제약조건
(Integrity) Constraint
데이터베이스 테이블 레벨에서 특정한 규칙을 설절해둠
예상치 못한 데이터의 손실이나 일관성을 위반하는 데이터의 추가, 변경 등을 예방
칼럼 제약조건: [CONSTRAINT 이름] constraint_type
테이블 제약조건: [CONSTRAINT 이름] constraint_type(column,..)

  1. NOT NULL칼럼형태로만 제약조건을 정의할 수 있음 - 테이블 제약조건 불가
  2. CREATE TABLE book ( bookno NUMBER(5) NOT NULL // 칼럼 제약조건 이름을 생략한 경우 );
  3. NULL 값이 존재할 수 없음
  4. UNIQUE복합 컬럼에 대해서도 정의 가능 
  5. CREATE TABLE book ( // 방법1 bookno NUMBER(5) CONSTRANINT c_book_u UNIQUE // 방법2 bookno NUMBER(5) UNIQUE // 방법3 CONSTRAINT [제약조건명] UNIQUE (bookno) );
  6. 자동적으로 인덱스 생성
  7. 중복된 값을 허용하지 않음 (NULL은 존재할 수 있음)
  8. PRIMARY KEY테이블 당 하나만 존재할 수 있음 
    CREATE TABLE book (
		ssn1 NUMBER(9),
		ssn2 NUMBER(9),
	
		// 방법 1 
		PRIMARY KEY (ssn1, ssn2)

		// 방법 2
		CONSTRAINT [제약조건이름] PRIMARY KEY(ssn1, ssn2)
);
  9. 복합 컬럼에 대해서 정의 가능
  10. NOT NULL + UNIQUE (인덱스 자동 생성)
  11. FOREIGN KEY일반적으로 REFERENCE되는 테이블의 PK를 참조REFERENCE 되는 테이블의 레코드 삭제 시 동작 방식
    1. ON DELETE NO ACTION
    2. 해당하는 FK를 가진 참조행 삭제 시도시에 오류 발생 및 DELETE 문 ROLLBACK
    3. ON DELETE CASCADE
    4. 해당하는 FK를 가진 참조행도 삭제
    5. ON DELETE (default) → ON DELETE NO ACTION
    6. 지정하지 않을 시 NO ACTION이 기본값으로 사용됨
    7. ON DELETE SET NULL 
      CREATE TABLE book (
		...
		author_id NUMBER(10),
		CONSTRAINT c_book_fk FOREIGN KEY (author_id)
		REFERENCES author(id)
		ON DELETE SET NULL
);
    8. 해당하는 FK를 NULL로 바꿈, NULL이 허용되어야 가능
    9. ON DELETE SET DEFAULT해당 컬럼에 DEFAULT가 정의되어 있어야 함
    10. 해당 컬럼에 NULL이 허용되고, DEFAULT 값이 정의되어 있지 않은 경우, NULL이 묵시적 기본값
    11. 해당하는 FK를 DEFAULT로 설정된 값으로 바꿈
  12. REFERENCE 되는 테이블에 없는 값은 삽입 불가
  13. 참조 무결성 제약
  14. CHECK 
    CREATE TABLE book (
		rate NUMBER CHECK (rate IN (1, 2, 3, 4, 5))
);
  15. 임의의 조건 검사, 조건식이 참이어야 변경 가능

제약조건 추가

ALTER TABLE emp ADD CONSTRAINT emp_mgr_fk
		FOREIGN KEY (mgr) REFERENCES emp(empno);

NOT NULL은 추가 못함

DML (Data-manipulation Language)


Data Manipulation Language
INSERT INTO … VALUES …
UPDATE … SET …
DELETE … FROM

  1. Add new row(s)
  2. INSERT INTO 테이블이름 [(컬럼리스트)] VALUES (값리스트);
  3. Modify existing rows
  4. UPDATE 테이블이름 SET 변경내용 [WHERE 조건];
  5. Remove existing rows
  6. DELETE FROM 테이블이름 [WHERE 조건];

트랜잭션의 대상 - 트랜잭션은 DML의 집합으로 이루어짐
INSERT

  • 묵시적 방법: 컬럼 이름, 순서 지정하지 않음, 테이블 생성시 정의한 순서에 따라 값 지정
  • INSERT INTO dept VALUES (777, 'MARKETING', NULL);
  • 명시적 방법: 컬럼 이름 명시적 사용, 지정되지 않은 컬럼 NULL/Default 자동 입력
  • INSERT INTO dept(dname, deptno) VALUES ('MARKETING', 777);
  • Subquery 이용: 타 테이블로부터 데이터 복사 (테이블은 이미 존재하여야 함)
  • INSERT INTO deptusa SELECT deptno, dname FROM dept WHERE country = 'USA';

UPDATE

  • 조건을 만족하는 레코드를 변경 
  • UPDATE emp SET sal = sal + 100, comm = 0 WHERE deptno = 10;
  • 10번 부서원의 월급 100인상, 수수료 0으로 변경
  • WHERE 절이 생략되면 모든 레코드에 적용 
    UPDATE emp SET sal = sal * 1.1;
  • 모든 직원의 월급 10% 인상
  • Subquery를 이용한 변경 
    UPDATE emp SET sal = (SELECT MAX(sal) FROM emp)
WHERE job = (SELECT job FROM emp WHERE
ename = 'SCOTT');
  • 답당업무가 ‘SCOTT’과 같은 사람들의 월급을 부서 최고액으로 변경

DELETE

  • 조건을 만족하는 레코드 삭제
  • DELETE FROM emp WHERE ename = 'SCOTT';
  • 조건이 없으면 모든 레코드 삭제 
    DELETE FROM emp;
  • 모든 직원 정보 삭제
  • Subquery를 이용한 DELETE 
    DELETE FROM emp WHERE deptno = 
		(SELECT deptno FROM dept WHERE dname = 'SALES');
  • ‘SALES’ 부서의 직원 모두 삭제

DELETE와 TRUNCATE의 차이점 DELETE는 Rollback 가능 (대량의 log를 유발), TRUNCATE보다 느림

SELECT

SELECT [ALL | DISTINCT] 열_리스트
[FROM 테이블_리스트]
[WHERE 조건]
[GROUP BY 열_리스트 [HAVING 조건]]
[ORDER BY 열_리스트 [ASC | DESC]];

projection- 원하는 컬럼 선택 selection- 원하는 튜플 선택
SELECT *| {[DISTINCT] column | expression [alias], … }
FROM table;
*: 모든 컬럼 반환 (ALL)
DISTINCT: 중복된 결과 제거
SELECT 컬럼명: Projection
FROM: 대상 테이블
ALIAS: 컬럼 이름 변경 (as) A as A’
Expression: 기본적인 연산 및 함수 사용 가능
NULL
아무런 값도 정해지지 않았음을 의미, 어떠한 데이터 타입에도 사용 가능
NOT NULL이나 Primary Key 컬럼에는 사용할 수 없음 (UNIQUE + NOT NULL)

           → Primary Key: NOT NULL + UNIQUE

NULL을 포함한 산술식은 일반적으로 NULL
[연산1]

SELECT sal, comm, (sal+comm)*12 FROM emp;

NVL(expr1, expr2)
expr1이 NULL이면 expr2를 반환한다 (데이터 타입이 호환가능하여야 함)
[연산2]

SELECT sal, comm, (sal + NVL(comm, 0))*12 FROM emp;

Alias (컬럼의 제목을 변경)
SELECT ename name FROM emp;
SELECT ename as name FROM emp;
SELECT ename “as” FROM emp; // 컬럼 이름을 as 로
SELECT (sal+comm) “Annual Salary” FROM emp; // 큰 따옴표를 사용하여 alias 내에 공백이나 특수문자 포함 가능

WHERE

  • IN: 집합에 포함되는가?
  • LIKE: 문자열 부분 검색

GROUP BY

SELECT deptno, AVG(sal) FROM emp;

반응형