PostgreSQL 윈도우 사용기 - SQL

select course_idfrom sectionwhere semester = 'Fall' and course_id in (select course_idfrom sectionwhere semester = 'Spring');1. Table 생성

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

create table section (course_id varchar(8), sec_id varchar(8) not null, semester varchar(6) check (semester in ('Fall', 'Winter', 'Spring', 'Summer')), year numeric(4,0) check (year > 1701 and year < 2100), building varchar(15), room_number varchar(7), time_slot_id varchar(4), primary key (course_id, sec_id, semester, year), foreign key (course_id) references course on delete cascade, foreign key (building, room_number) references classroom on delete set null );

on delete cascade 는 referenced 되는 private key 가 사라졌을 때, 열 전체를 삭제한다는 옵션.

on delete set null 은 referenced 되는 private key 가 사라졌을 때, 그 부분을 null 로 채운다는 옵션.

​

2. 제거, 삭제

​

제거, 삽입, 변경 등에서 where 문 등에서 서브쿼리문이 나오면, 그 문을 모두 처리한 후 데이터를 수정한다.

1	DROP TABLE tablename;

1 2 3 4 5 6

-- only catched row are deleted DELETE FROM weather WHERE city = 'Hayward'; -- make empty table DELETE FROM tablename;

3. 삽입, 변경

1 2 3 4 5 6 7

-- implicit order by table INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27'); -- explicit order, recommended INSERT INTO weather (date, city, temp_hi, temp_lo) VALUES ('1994-11-29', 'Hayward', 54, 37);

1 2 3 4

insert into instructor select ID, name, dept_name, 19000 from student where dept_name = '하하하';

1 2 3

UPDATE weather SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2 WHERE date > '1994-11-28';

4. join

1 2 3 4 5 6 7 8

select name, title from (instructor natural join teaches) --natural join 은 이름만 같으면 됨 join course using (course_id) -- a join b using (a) 는 세타 조인이라 using 구문이 필요함 left join -- 왼쪽 기준 오른쪽이 없으면 null right join -- 오른쪽 기준 왼쪽이 없으면 null full join -- left, right 합친거 outer join -- 그냥 relation

5. copy

1 2 3 4 5 6 7 8

-- show directory of server show data_directory; -- apply with server directory not client directory, -- you should put a file in client folder' -- you don't need ' to indicate string in txt file copy weather from 'weather.txt' using delimiters ','; \copy weather from 'weather.txt' using delimiters ',';

6. where

​

1. like

1 2 3

select dept_name hw_database-> from department hw_database-> where building like '%관';

where 문 등 bool 값이 필요할 때 사용.

% 는 정규식에서 [a-zA-Z]* 비슷하게 그냥 아무 스트링이 온다는 의미

_ 는 정규식에서 . 와 같은 의미

ecape 문자가 지원되어서 %, _ 를 \%ㅡ, \_ 를 이용해 표현가능

​

2. between

1 2 3

select * from instructor where salary not between 50000 and 100000;

ㅈㄱㄴ

not 유무 가능

​

3. in

​

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

-- nested subquery, select course_id from section where semester = 'Fall' and course_id in ( select course_id from section where semester = 'Spring'); -- apply with enum set select distinct name from instructor where name not in ('Mozart','Einstein'); -- apply with set select count (distinct ID) from takes where(course_id, sec_id, semester, year) in ( select course_id, sec_id, semester, year from teaches where teaches.ID = '10101');

유사품 not in 도 있음

where 문 작성 시 다른 table 에 있거나 없는 원소인지 판단할 때 쓰임

집합 관련 연산으로도 처리가능하나 이게 더 간단함. 특히 임시로 만든 set 일 땐.

단 distinct 는 적용 안됨.

​

4. > from, > all

1 2 3 4 5 6 7 8 9 10 11 12 13

select name from instructor where salary > some ( select salary from instructor where dept_name = '철학과'); select name from instructor where salary > all( select salary from instructor where dept_name = '철학과');

위의 in 과 비슷하다. some 이나 all 뒤에 오는 집합의 원소 중 하나라도 관계식이 해당되면 some, 모두 해당되어야하면 all 이다. > = < >= <= 모두 가능하다.

​

5. exists

1 2 3 4 5

select course_id from section where exists ( select * from takes);

not exists 도 있음.

집합이 존재하느냐에 따라 bool 값을 줌

이걸 잘 써먹으면 포함관계라던가 그런것도 표현가능함.

​

7. order by

1 2 3

select * from instructor order by salary desc, name asc;

위에서부터 오름차순이 asc 으로 디폴트 값이라 생략해도 된다.

적은 순서대로 우선순위가 적용된다.

​

8. 집합 계산

​

union, intersect, except 구문이 제공되며 기본적으로 중복된 행을 제거한다.

중복이 싫으면 union all 처럼 뒤에 all 을 적는다.

​

9. Aggregate Function

​

1. 잡 펑션

​

sum, count, avg 등 제공

1 2 3 4 5 6 7

select count (distinct ID) as num from teaches where semester = 'Spring'; -- error, we cannot put before * distinct using count -- count(*) also count null select count(distinct *) from teaches;

where 절 등에서 내용이 비게 된 relation 에 대해서 func 을 수행해야할 경우

count 는 0, 나머지는 null 이 들어간 relation 을 만든다.

​

2. group by

1 2 3

select dept_name, avg(salary) as avg_salary from instructor group by dept_name;

같은 그룹으로 묶어서 개별적으로 aggregation 을 진행한다.

주의해야할 것은 group by 를 쓴 후에는 return table 로 쓸 column 이 오직 dept_name 과 aggregate_func 으로만 이루어 질 수 있다는 것이다.

​

3. having

1 2 3 4

select dept_name, avg(salary) as avg_salary from instructor group by dept_name having dept_name is not null and count(id) > 5;

​

group by 가 나와야지만 사용할 수 있으며, 그것이 만든 그룹들에 대해서 where 과 비슷한 역할을 함.

사실 from 에 서브쿼리를 넣으면 having 을 대체할 수 있음

group by 에서 select 의 제한처럼, having 에는 group by 에 있는 속성이나 아니면 aggregate func 을 사용해야한다. 다른 말로 하면 aggregate func 을 having 에서 쓸 때는 제한이 없단 이야기다. 왜냐하면 그룹을 대상으로 계산을 해야하기 때문이다.

​

​

10. null, unknown

​

1. null

​

덧셈같은 수치계산에선 null 이 들어가면 무조건 null 이 나온다.

조건문에선 null 없이 결과를 알 수 있으면 예를 들어 true | null 이면 예측가능한 결과값을 내놓는다.

아닌 경우에는 null 을 내놓는다.

비교문에서 null 이 들어가면 unknown 을 내놓는다.

​

select distinct 문처럼 멀티셋에서 그냥 셋으로 바뀔 때 주의해야한다.

{(A, null), (A, null), (B, null)} 이런 경우 앞의 두개는 같은 것이 되어 하나만 남는다.

​

aggregate func 에서는 count(*) 을 제외하고 null 이 들어간 행을 모두 무시한다.

​

​

2. unknown

​

그러므로 bool 값이 true, false, unknown 을 정의역으로 갖는다.

where 문에 unknown 값이 들어가면 그건 버린다.

하지만 is null, is unknown 으로 캐치 가능하다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14

-- below we make same one SELECT max(temp_lo) FROM weather; SELECT city FROM weather WHERE temp_lo = max(temp_lo); -- wrong SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather); SELECT city, max(temp_lo) FROM weather GROUP BY city; SELECT city, max(temp_lo) FROM weather GROUP BY city HAVING max(temp_lo) < 40;

11. Correlated Subquery

1 2 3 4 5 6

select course_id from section as S where semester = 'Fall' and exists( select * from section as T where semester = 'Spring' and S.course_id = T.course_id);;

바깥쪽에서 from 의 table 을 서브쿼리에서 지시 가능하다.

이 경우 where 에서 검사하고 있는 하나의 튜플이 지시된다..

위의 예에서라면 서브쿼리에서 가르키는 S의 대상은 계속 바뀌면서 적용된다.

별명 붙이는건 본질이 아니니 착각하지 말 것.

​

​

12. With

​

1. from 정레 sql 구문이 들어옴

​

1 2 3 4 5 6 7

select dept_name, avg_salary from ( select dept_name, avg(salary) from instructor group by dept_name) as dept_avg(dept_name, avg_salary) where avg_salary > 40000;

대신 무조건 as 로 별명을 지어야함.

또한 lateral 이라는 구문 없이는 Correlated Subquery 못씀

​

2. with

1 2 3 4 5

with max_budget(value) as (select max(budget) from department) select dept_name from department, max_budget where department.budget = max_budget.value;

위에 from 절에 있는 nested relation 구문들을 위로 뺄 수 있다.

​

13. 스칼라 서브쿼리

1 2 3 4 5

select name from instructor where salary * 10 > ( select budget from department where department.dept_name = instructor.dept_name);