Database Concepts Chapter 3. SQL

데이터베이스 이론 및 실습 정재윤 교수님 수업 (19-1)

 

SQL Defined

- SQL is comprised of

>> A data definition language (DDL) : used to define database structures

>> A data manipulation language (DML) : data definition and updating

 

SQL for Data definition

- Create : To create database objects

- Alter : To modify the structure and/or characteristics of database objects

- Drop : to delete database objects

 

create case 1

- 제약 조건으로 PRIMARY KEY

>> CONSTRAINT 제약_조건_이름 PRIMARY KEY(Column_name)

create case 2

- composite primary key를 primary key로 주기

- NOT NULL 이외에 들어가는 것

>> NOT NULL DEFAULT 100  ----->  기본 값으로 100, ', '를 안 썼다.

>> NOT NULL UNIQUE ----> 말 그대로 unique 한 값이라는 것

 

- data type에 들어가는 것

>> Numeric (8,2) ---> 전체 자리 수는 8자리이며, 소수점 부분의 길이는 2

ex) 12345 -> 123.45로 변경, 12345678 -> 123456.78로 변경

 

- Surrogate key 넣어 주기

>> NOT NULL IDENTITY(1,1) -----> 1부터 시작해서 차례대로 1씩 증가한다.

>> NOT NULL IDENTITY(1000,100) -------> 1000부터 시작해서 차례대로 100씩 증가한다. 

 

FOREIGN KEY 주기

>> constraint 제약_이름 FOREIGN KEY(column_name) REFERENCE foreign_key를_primary_key로_하는_table(column_name)

지-저-분

- related table 사이에서 참조 무결성을 지키기 위한 제약 조건

>> reference ~~~ ON DELETE CASCADE  ---> 고객이 탈퇴하면, 고객 id를 foreign key로 갖고 있는 우리 테이블에서도 지운다!

>> reference ~~~ ON UPDATE CASCADE ---> 고객 정보가 바뀌면, 우리도 바꿔줘!

* foreign key가 다른 테이블에서 surrogate key이면 당연히 update cascade를 해주면 안 된다. 

* on update casecade가 아니라면 on update no action이라고 명시할 수는 있지만 기본 옵션이기 때문에 굳이 안 해줘도 괜찮다. 

 

- 이미 create 했는데 제약 조건을 추가하고 싶을 때 => ALTER을 사용한다. 

* 만약 기존에 primary key가 존재하면 삭제한 다음에 제약 조건을 추가해야 한다. 

- constaint를 수정하고 싶다 -> 먼저 삭제해야 한다.

>> 예시

 

- foreign key도 동일한 방법으로 한다. 

- CHECK Constraint

>> column에 제약조건을 넣어 주는 것

 

 

 

 

Data 추가하고 싶다 -> Insert into

- 방법 1 : 모든 column에 대해 한 번에 넣는다. -> table_name values()

- 방법 2: 특정 column만 넣고 나머지는 null 값을 준다. -> table_name(column_name) values()

Select is the best known SQL statement

- a query pulls information from one or more relations and creates (temporarily) a new relation

 

- where문에 사용하는 operator

* not equal은 처음 봤다 <>

* between도 있다.

(10과 45도 포함하는 듯)

 

- 특정 문자열을 찾고 싶을 때 wildcard => %(여러 문자열), _ (하나의 문자열)

정렬하기

- 내림차순 : DESC

- 오름차순 : ASC

* default는 ASC

- 정렬을 두 개로 할 수 있음

ORDER BY Department DESC, Lastname ASC

>> department로 내림차순, lastname으로 오름차순

 

잡다한 Bulit-in function

- count, min, max, sum, avg

 

- 이런 것도 가능하다. 

SELECT ProjectID, ProjectName, MaxHours, (18.5*MaxHours) as MaxProjectCost

FROM PROJECT;

# NOTE : Does not allow column names to be mixed with bulit-in functions, except in certain users of the SQL group by clause

SELECT MaxHours, SUM(MaxHours)   ===> error

FROM PROJECT

WHERE ProjectID <= 1200;

이건 확인 한 번 해봐야 할 듯, 교재 175쪽

 

HAVING

- 이게 참 헷갈릴 수 있음! having vs where

- having은 group by랑 짝꿍이라고 생각하면 좋음

- having에는 위에서 봤던 bulit-in function을 적용할 수 있음

- where은 조건이 들어갈 때 column 그 자체에만 적용 가능함(특정한 연산이 안됨)

WHERE MaxHours < AVG(MaxHours) ===> error

-  where -> group by -> having 이 순서로 사용한다. where이 항상 제일 먼저!

 

JOIN

- inner join

- left join

- right join 

# join을 중첩해서 사용할 때, 나는 너무 바보처럼 했다! 간단하게 해도 되는구나

ex)

SELECT   ProjectName, FirstName

FROM     (EMPLOYEE AS E JOIN ASSIGNMENT AS A ON E.EmployeeNumber = A.EmployeeNumber)
                  JOIN PROJECT AS P ON A.ProjectID = P.ProjectID

ORDER BY P.ProjectID, A.EmployeeNumber

 

 

 

 

Modifying Data using SQL

- Insert : add a new row in a table

- Update : update the data in a table that matches the specified criteria 

- Delete : delete the data in a table that matches the specified criteria

 

* update 사용

* Delete 사용

- delete 말고 더 강력한 drop이 있음

>> drop을 사용하면 permanently remove the object and all data

ALTER을 이용해서 Column 변경/추가 하기

 

ALTER TABLE PROJECT

     ADD CurrentTotalHours Numeric(8,2) NULL;

 

만약에 바로 NOT NULL을 사용하면 에러가 난다=> 값이 없으니까!

따라서 기본 값을 넣어줘야 함

 

ALTER TABLE PROJECT

     ADD COLUMN CurrenTotalHours Numeric(8,2) NOT NULL DEFAULT 1;

 

Column이 필요 없다?

 

ALTER TABLE PROJECT

     DELETE COLUMN CurrenTotalHours