3. SQL part1 - intro, DDL SQL, DML SQL

Yona·2021년 10월 26일
CSSSUdb
0

🌙 CS_DataBase

목록 보기
6/10

database language 분류

분류방식 1

  • DDL
    • data definition language
    • 데이터베이스 스키마에 대한 조작
    • 스키마 생성, 스키마 삭제, 스키마 변경 등
    • DDL 실행 결과는 Data directory에 저장/관리
      된다
  • DML
    • data manipulation language
    • query language라고도 불림
    • 인스턴스를 조작하는 언어
    • 인스턴스의 생성, 조회, 삭제, 변경 등
  • DCL
    • data control language
    • 스키마, 인스턴스 제외 다른 객체 조작
    • 컨트롤 (transaction, session, security, auth 등)

분류방식2

  • procedural language
    • 처리 방법 및 절차(how to do)를 명시
  • non-procedural(declarative) language
    • 방법 및 절차에 대한 언급 없이 원하는 데이터(what to do)만을 명시
    • non-proc lang이 더 진보된 언어이다 (간편하니까)

relational database language

  • pure language
    • 이론적. 실제로 구현되진 X
    • relational algebra, tuple relational calculus, domain relational calculus 등등
  • language supported in real system
    • 이론을 실제로 구현한 것
    • SQL, QUEL, LDL 등

SQL 개요

  • intro

    • Structured Query Language 약자
    • 사실상의 표준 (de facto and de jure standard)
    • DDL, DML, DCL 제공

  • history

    • IBM Sequel 에서 1970년대개발
    • 초기 이름은 system R
    • 표준화 단체는 ISO, ANSI, W3C, OMG 등

  • 특징

    • 대소문자 구분 하지 않음 (case insensitive)
      • ""이나 ''안에선 대소문자 구분

  • domain types

    • char(n)
      • fixed-length char
    • varchar(n)
      • variable-length char. n is maximum length
    • int
    • small int
    • numeric(p,d)
      • 소숫점 자리수 지정 (유효숫자, 소숫점다음숫자갯수)
      • numeric(5,2) = xxx.xx 형태
    • real, double precision
    • float(n)
      • least n digits에서 precision

DDL SQL

relation(=table)들의 집합 specification 뿐 아니라, 각 relation에 대한 정보도 specificate

  • 종류
    • 관계 스키마
    • 속성의 도메인
    • 무결성 제약
    • 관계에 연관되는 인덱스
    • 관계 저장을 위한 디스크 상의 물리적 구조
    • 관계에 연관되는 보안 및 권한 부여/취소

create table

  • intergrity constraints
    자주 사용되는 무결성 제약
    • not null
    • primary key(A1, ..., An)
    • foreign key(A1, ..., An) references R
    • check (Ak in (...))
  • 예시 2
// foreign table 변형
Create table department(
deptName	varchar(20) primary key, 
chairman	char(5) 
building	varchar(30),
budget		numeric(10,0),
foreign key (chairman) references professor(pID) // 참조하는 테이블 뿐만 아니라 속성을 명시해도 된다
// foreign key임을 나중에 명시
);

// 특정값 Constraint
Create table student (
sID       		char(5) primary key,
name   		varchar(20) not null, // null 값이 들어갈 수 없다 
gender		char(1),
deptName 	varchar(20),
GPA		numeric(3,2),
totalCredit	integer,
foreign key (deptName) references department),
check (gender in (‘F’, ‘M’))) // student 관계의 gender 속성은 반드시 “F” 또는 “M” 값을 가져야 하는 제약
;  
// primary key는 2개 속성 이상으로 조합 가능
// primary key는 외부 key 도 가능
// foreign key는 나중에 명시해줘도 됨
Create table teaches (
pID        	char(5),
cID	 	char(5),
semester  	char(10),
year       	numeric(4,0),
classroom       char(5),
primary key 	(pID, cID, semester, year), // 여러 속성이 primary key가 된다
foreign key 	(pID) references professor, // foreign key임을 나중에 명시
foreign key 	(cID) references course,
foreign key 	(classroom) references room);

drop/alter table

  • DDL의 기본 키워드
    • CREATE / ALTER / DROP
  • 종류
    • drop : 스키마 삭제
    • alter : 스키마 변경
    • add : 스키마 추가
    • drop : 스키마를 없앰
    • delete : 모든 튜플 없앰
    • Truncate : 모든 튜플 없앰
  • 예시
    • Alter table r add A D;
      • 기존 r 테이블이 D 도메인을 갖는데, A 도메인을 추가한다.
    • Alter table r drop A;
      • 기존 r 테이블이 A 도메인을 갖는데, 삭제한다.
    • Drop table students;
      • 학생 테이블을 없앤다
    • Delete from student;
      • 학생 테이블의 모든 튜플 삭제
    • Truncate table student;
      • 학생 테이블의 모든 튜플 삭제

DML SQL

  • 종류
    • select
    • insert
    • delete
    • update

Insertion

기본 : Insert .. into .. 

// 새로운 튜플을 추가한다
Insert into course values (’437’, ’Advanced Databases’, ’CS’, 4);

// null 사용 가능 
Insert into course values ('777', 'undecided', 'CS', null);

// select-from-where절을 사용가능하다!!
Insert into professor select * from professor; // professor 테이블의 모든 튜플을 professor 테이블에 삽입한다.
// 튜플 갯수가 2배가 됨

Deletion

기본 : Delete from ...

// 모든 튜플 삭제
Delete from professor; // professor 테이블의 모든 튜플 삭제

// where절 사용 가능 
Delete from professor where deptName='EE'; // professor 테이블에서 EE학과의 교수를 모두 삭제해라

// in : in() 괄호에 포함이 되면 true
Delete from professor where deptName in (select deptName from department where building='Vision Hall'); 
// 해당 조건에 만족하는 professor 테이블의 모든 튜플을 삭제해라 : 'vision hall'에 위치한 학과(department 테이블에서 확인) 소속의 교수 

// 기호 사용
Delete from professor where salary < (select avg(salary) from professor) // professor 테이블의 Salary들의 평균값보다 더 작은 연봉을 가진 튜플들을 Professor 테이블에서 삭제

교수가 삭제됨에 따라 평균 봉급 값도 변할 수가 있는데,
SQL 시스템에서는 터플 삭제에 따른 평균값이 변하지 않는다. 그 대신 query 초기에 평균값을 계산하고 이를 근거로 삭제할 터플을 구한 다음 평균값 재계산 없이 터플을 삭제한다. (=똑똑하다)

Update

기본 : Update 테이블이름 set 요소이름=... (where 조건)

예제1) Increase salaries of professors whose salary is over 7000 by 3% and all others receive a 5% raise

// 기본
Update professor
  set salary = salary*1.03
  where salary > 7000;
Update professor
  set salary = salary*1.05
  where salary <= 7000;

// case - end 활용
Update professor
set selary = case
		when salary <= 7000 then salary*1.05
                else salary*1.03
            end;

예제2) Update totalCredit values for all students


//방법1)
Update student S
set S.totalCredit = 
	(select sum(credit)
    from takes natural join course
    where S.sID=takes.sID and grade <> 'F' and grade is not null); // grade 속성이 'F' | NULL 이 아닌 과목의 crdit 속성값의 합을 구하여, totalCredit 속성 값으로 갱신
    
// 보정작업
Update student
set totalCredit = 0
where totalCredit is null;
  • <> 기호 : not equal
profile
Yona
Sometimes you win, sometimes you learn 🏃‍♀️
이전 포스트

2. relational model - additional relational algebra

다음 포스트

SQL part1) select

0개의 댓글