[Database] -SQL

📕SQL

• Structured Query Language (SQL)
• Declarative
• includes DDL and DML

📕DML 

• Data Manipulation Language
  • select
    • 정보를 검색해서 찾아옴.
  • Insert, UPDATE, DELETE
    • 데이터베이스 안에 있는 데이터를 수정

📕DDL

• Data Definition Language
  • CREATE, ALTER, DROP, RENAME, TRUNCATE
    • 스키마의 구조를 바꾸거나, 제약을 변경하거나 생성함.

📕DCL

• Data Control Language
  • GRANT, REVOKE
    • 사용자의 데이터베이스 접근을 컨트롤함

📕TCL

• Transaction Control Language
  • COMMIT, ROLLBACK, SAVEPOINT
    • 데이터 수정의 논리적 흐름을 컨트롤함.

 

 

📕CREATE

• SQL 내에서 data definition의 중요 커맨드
  • show + 
    • database, schema, table, view, assertion, trigger, etc.
• MYSQL은 CREATE를 통해서 데이터베이스와 스키마를 만든다. (대소문자 구분 X)
• 새로운 릴레이션을 만드는 방법
  • 이름을 주고, 그것의 속성과 데이터 타입을 명시함.
  • 제약 명시 : 속성, tuple, key

CREATE TABLE employee (
ssn Int,
name varchar(30)
gender char(1)
Address varchar(100)
);

 

 

💡DBMS에서는 유저가 스스로 그들의 domain과 UDT(User Defined TYpes)를 정의할 수 있지만, MYSQL은 그것을 허락하지 않는다.

 

📕Domain

• 이름은 속성 명시와 함께 사용된다.
• 수많은 속성에서 사용되는 도메인에 있는 data type 바꾸는 게 훨씬 쉽다.
• 스키마의 가독성을 향상해준다.
• ex)
  • CREATE DOMAIN SSN_TYPE AS CHAR(9);

📕MYSQL Built-in DATA Types

 

 

📕Specifying Constraints in SQL

• Basic constraints
  • Relation model은 3개 타입의 제약을 SQL에 지원한다.
    • Key Constraint
      • 고유키는 반드시 유니크해야 한다.
    • Entity integrity Constraint
      • 고유키의 값은 NULL이 될 수 없다.
    • Referential integrity Constraints
      • 외래 키는 참조되는 쪽에서 그 값이 존재해야 한다.

 

📕Specifying Attribute Constraints

• Default value of an attribute 
  • DEFALUT <value>
  • Null 은 특정 속성에 허용되지 않는다. (Data type is not null 일 때)
• Check clause
  • 테이블을 만드는 동안 아래 제약이 추가될 수 있다.
  • Dnumber INT not null check ( Dnumber > 0 and Dnumber <21);

📕Specifying Keys

• PRIMARY KEY clause
  • 릴레이션에 고유키를 구성하는 속성을 하나 더 지정
  • Dnumber INT PRIMARY KEY
• UNIQUE clause
  • 대체 가능한 (secondory) key를 지정한다.
  • Dname varchar(15) UNIQUE;

Specifying Referential Integrity Constraints

• FOREIGN KEY Clause
  • Default Operation : 위반 시 작업 거부
• 참조 트리거 작동 clause
  • Options은 set null, cascade , set default를 포함한다.
  • DBMS에서는 set null or set default를 DELETE and ON UPDATE로 사용한다.
  • CASCADE option 은 "relationship" 릴레이션에 적합하다.

📕외래 키를 만드는 법

Alter table 참조하는 테이블 이름 add foreign key (외래키로 연결하고 싶은 attribute) REFERENCES 
참조당하는 테이블 (PK)

📗제약조건

• ON DELETE
  • 참조되는 테이블의 값이 삭제될 경우의 동작을 설정하는 것
• ON UPDATE 
  • 참조되는 테이블의 값이 수정될 경우의 동작을 설정
• 설정할 수 있는 동작들
  • CASACDE 
    • 참조되는 테이블에서 데이터를 삭제하거나 수정하면, 참조하는 테이블에서도 삭제와 수정이 같이 이루어짐.
  • SET NULL
    
    • 참조되는 테이블에서 데이터를 삭제하거나 수정하면, 참조하는 테이블의 데이터는 NULL로 변경됩니다.
  • SET DEFAULT 
    • 참조되는 테이블에서 데이터를 삭제하거나 수정하면, 참조하는 테이블의 데이터는 필드의 기본값으로 설정된다.
  • NO ACTION
    • 참조되는 테이블에서 데이터를 삭제하거나 수정해도, 참조 하는 테이블의 데이터는 변경되지 않습니다.
  • RESTRICT
    • 참조되는 테이블에 데이터가 남아있으면, 참조되는 테이블의 데이터를 삭제하거나 수정할 수 없다.

코드 예시

Alter table department add foreign key (mgr_ssn) references employee (ssn) on DELTE SET NULL on UPDATE CASCADE;

 

 

📕INSERT DELETE and UPDATE 

• 데이터베이스를 수정하는 데 사용되는 3개의 명령어
  • INSERT, DELETE , UPDATE
• INSERT : relation(테이블)에 새로운 tuble(row)를 삽입.
• UPDATE : relation(테이블)에 존재하는 tuple(row)를 수정.
• DELETE : relation(테이블)에 존재하는 tuple(row)를  삭제.

 

📗INSERT

insert into department values('daegue', 1, '8888555666','1981-01-04');

• 처음에 create table 할 때 만든 attribute에 순서에 맞게 value를 넣어야 함.
• 예를 들어 1번은 지역, 2번은 d_id, 3번은 ssn, 4번은 입사날짜라고 가정할 때 각자 data type에 맞는 정보가 들어가야 insert가 실행된다.

 

📗DELETE 

• DELETE 명령어는 relation에 있는 튜플을 지운다.

DELETE FROM EMPLOYEE
where lname = 'smith'; //employee 테이블에서 lname이 smith인 직원에 튜플을 모두 지운다.

• 만약 where 절을 생략하면 table에서 모든 tuple이 제거된다.

DELETE FROM EMPLOYEE;

• 참조 무결성을 지켜야 한다.
  
  • 만약 PK를 지웠다면 PK를 참조하는 FK는 유저에 의해 업데이트돼야 한다.

📗DELETE

• UPDATE 명령어는 relation에 있는 하나 이상의 튜플의 attribute의 속성을 수정한다.

UPDATE PROJECT
set plocation = 'BEllaire', dnum=5
where pnum = 10;

• 산술 연산도 가능하다.

UPDATE EMPLOYEE 
set salary = salary * 1.2
where dno=4;

update employee 
set salry = salary * 1.5
where dno in (select dnumber from department where dname = 'RESEARCH');

 

📕BASIC Retrival Queries in SQL

• SELECT
  • 데이터 베이스로부터 정보를 검색하는 가장 기본적인 문법
  • The result is a table
• SQL은 허락한다 table reuslt(select로부터 얻은) 두 개 이상의 튜플(그들의 attribute values 안에서는 동일하다)?
  • Unlike relation model
  • Multiset or bag behavior
  • Tuple-id may be used as a key

📗SELECT

• 기본 형태

SELECT <attributes>
FROM <tables>
WHERE <conditions>;
//where이 없으면 table로 부터 모든 tuple을 찾음.

• attributes : 검색된 value가 있는 attribute의 이름 배열
• tables : 검색된 attribute가 있는 table의 이름.
• conditions : 찾고자 하는 식별 가능한 튜플의 사용되는 조건절

 

📗Select example

30시간 이상 프로젝트에 일한 직원들의 ssn을 검색하기

select essn from works_on
where hours>=30;

project=2에서 일하는 사람들의 Fname, Lname을 검색해라.

select fname, lname
from works_on, employee
where ssn=ESSN and pno =2;

 

📕Specify an asterisk (*)

• 선택된 튜플에 모든 attribute values의 값을 검색해준다.
• relation 이름 뒤에 온다. ex) Employee *

 

department number가 5에서 일하는 모든 직원들을 검색해라

select * from employee
where dno =5;

 

릴레이션이 다르지만 attribute의 이름이 같은 경우는 릴레이션. 속성으로 명시해서 작성한다.

select employee.fname, address 
from employee, department
where departmant.dname = 'Research' and Department.Dnumber = Employee.Dno;

릴레이션.속성 말고 릴레이션 뒤에 AS 이름을 붙여서 사용할 수 도 있다.

예를 들어서 한 릴레이션을 쿼리에서 두 번 사용해야 하는 경우가 있다.

아래 쿼리는 직원의 이름과 그 직원의 상사의 이름을 검색하는 쿼리다.

상사와 직원의 테이블은 같기에 서로 구분해 주려고 relation에 별칭을 붙였다.

select E.fname, E.lname , S.lname
from employee E, employee S
where E.super_ssn = S.ssn;

E와 S는 employee 테이블에 있는 tuple을 가진다.

 

📕WHERE

• Boolean 조건을 명시하는데 그 조건이 참이 되어야 tuple에서 정보를 검색할 수 있다.
• Comparison operators : =, >, >=, <, <=, <>
• logical operators : AND, OR, NOT
• Special Operators
  • IN
  • BETWEEN
  • LIKE
  • IS NULL

 

📕Set

• SQL은 자동적으로 쿼리 결과에서 복제된 tuple을 제거하지 않는다.
  • 복제된 tuple을 제거하거나 check 하는 것이 비용적으로 비싸다.
• 하지만 우리는 Select 절에서 DISTINCT를 사용해서 중복을 제거할 수 있다.

모든 직원의 임금을 검색해라

1)
select all salary,lname,fname
from employee 
order by salary asc;

2)
select distinct salary
from employee
order by salary asc;

1) 결과 : 모든 직원의 임금이 검색된다.

 

2) 결과 : 모든 임금의 경우의 수가 검색된다.( 중복을 없앰)

 

📕Order By 

• DESC : 내림차순으로 결과를 봄.
• ASC : 오름차순으로 결과를 봄

📕UNION, EXCEPT, INTERSECT

• 집합 연산
  • UNION(합집합), EXCEPT(차집합), INTERSECT(교집합)

📕Like

• 문자열 패턴 매칭 할 때 사용됨.
• % : 0개 이상의 임의 문자를 %로 대체함.
• underscore(_)는 한 글자를 대체한다.

select ssn, fname, lname, from employee where name like '%b';
select fname from employee where fname like 'J_';

 

📕BETWEEN

• comparison operator

select *
from employee 
where salary between 40000 and 50000 and dno=5;

 

📕IN

• 연산자를 사용하면 지정된 속성 값이 지정된 값 집합의 값과 하위 쿼리에 의해 일치하는지 확인할 수 있습니다.

📕Arithmetic Operations

• 기본적인 4척 연산은 select 안에서 사용 가능하다. (+ , - , * , /)

📕Aggregate functions

• sum <attr>
• min <attr>
• max <attr>
• avg <attr>
• count(*)
• count(DISTINCT <attr>)
• group by 절과 함께 사용해야 한다.

 

📕Group by

• 특정 필드 값의 하위 집합을 정의하고 하위 집합에 집계 함수를 적용할 수 있습니다.
• 그룹화 기준: 집계 함수를 각 하위 집합에 독립적으로 적용합니다. (sum, avg, max 등등)
• group by 절은 여러 가지의 attribute를 가질 수 있다.

select pno, max(hours)
from works_on
group by pno;

 

📕HAVING 

• Group by 절 뒤에 위치하고 조건을 걸어준다.

select pno, Max(hours)
from works_on
group by pno
having MAX(hours) > 21;

• where  VS Having
  • where은 모든 필드에 조건을 걸지만 having은 group by 한 필드에만 조건을 걸 수 있다.