집합 연산

union 합집합

select * from sample71_a

union

select * from sample71_b; 두개의 테이블이 합쳐서 나온다 중복제거

열의 자료형이 일치해야 한다

select a from sample71_a 세개의 테이블의 결합

union

select b from sample71_b

union

select age from sample31;

select a as c from sample71_a

union

select b as c from sample71_b order by c; 별명을 붙여서 정렬한다

union all 중복을 제거하지 않고 합치기(union은 기본이 distict중복제거)

oracle 교집합 intersect 차집합minus(except)

테이블 결합

곱집합 적집합 카티전곱 요소종류1 x 요소종류2 의 개수가 된다

cross join 교차결합 곱집합으로

select * from sample72_x, sample72_y; 복수테이블 지정시 교차결합

select 상품.상품명, 재고수.재고수 from 상품, 재고수

  where 상품.상품코드 = 재고수.상품코드    결합조건

    and 상품.상품분류 = '식료품';                  검색조건

inner join 내부결합

select 상품.상품명, 재고수.재고수             

  from 상품 inner join 재고수                   

    on 상품.상품코드 = 재고수.상품코드      on으로 결합조건

  where 상품.상품분류 = '식료품';

foreign key 외부키 : 다른 테이블의 기본키를 참조하는 열

외부결합 left join right join(기준이 left or right)

select 상품3.상품명, 재고수.재고수

  from 상품3 left join 재고수

    on 상품3.상품코드 =  재고수.상품코드

  where 상품3.상품분류 = '식료품';

select 상품3.상품명, 재고수.재고수       구식방법은 되도록 사용하지 않는다

  from 상품3, 재고수

  where 상품3.상품코드 = 재고수.상품코드(+) (oracle:부재가능한 테이블의 열에 +)

    and 상품3.상품분류 = '식료품';

관계형 모델

릴레이션은 테이블

attribute(속성)은 열

tuple(튜플)은 행

union(합집합) union

difference(차집합) except

intersection(교집합) intersect

cartesian product(곱집합) from복수 테이블, cross join

selection(선택) 튜플의 추출은 where 데이터 검색

projection(투영) 반환될 열 지정

join(결합) 내부결합

데이터베이스 객체 테이블 뷰 인덱스 등 DB에서 정의하는 모든 것

기존 이름과 예약어 불가 / 숫자 시작X / 언더스코어_ 이외의 기호 사용 불가

한글은 더블쿼트(mysql백쿼트)로 둘러싼다 / 시스템 허용길이 준수

스키마라는 그릇에 담기므로 객체 이름이 같아도 스키마가 다르면 허용

mysql에서는 데이터베이스가 스키마

oracle에서는 데이터베이스와 데이터베이스 사용자가 계층적 스키마

이름이 충돌하지 않도록 기능하는 그릇(스키마나 테이블)을 네임스페이스

select, insert, delete, update 는 dml

테이블 생성

create table sample62 ( 

  no integer not null,

  a varchar(30),

  b date);

테이블 삭제 drop table

테이블 유지 데이터만 삭제할시 truncate table 테이블명

테이블 변경 

alter table 테이블명 변경명령

alter table sample62 add newcol integer; 열 추가

alter table sample62 modify newcol varchar(20); 열 속성 변경

alter table sample62 change newcol c; 열 이름 변경

alter table sample62 drop c; 열 삭제하기

제약

create table sample631(

  no integer not null, 널이 아니도록 제약

  sub_no integer not null,

  name varchar(30) unique, 유일한 값이도록 제약

  primary key(no, sub_no) 기본키가 되도록(유일) 제약

  //constraint pkey_sample primary key(no, sub_no) 제약에 이름짓기 contraint

);

제약 추가하기 제거하기

alter table sample631 modify c varchar(30) not null; 널이 아니도록 제약 추가

alter table sample631 modify c varchar(30); 널 제약 제거

alter table sample631 add constraint pkey_sample631 primary key(a); 기본키 추가

alter table sample631 drop constraint pkey_sample631; 기본키 제거

alter table sample631 drop primary key; 기본키 제거

복수의 열로 기본키 구성하기 a와 b가 둘다 동일하지 않으면 다름

인덱스 이진트리로 검색(항목 하부선택지3 좌:작다 중:같다 우:크다) 띄엄띄엄검색

create index isample65 on sample62(no); (여러 개의 열 지정 가능) 인덱스 생성

drop index isample65 on sample62; 인덱스 제거 sample62는 테이블명

검색은 테이블에서 수행

explain sql명령 index를 사용해서 검색을 하는지 확인가능

뷰 가상테이블

create view sample_view_67 as select * from sample54; 뷰 생성

뷰에서 검색가능

drop view sample_view_67; 뷰 제거

집계함수 count(집합) / sum(집합) / avg(집합) / min(집합) / max(집합)

select count(*) from sample51 where name='a'; count만 * 사용가능

select count(no), count(name) from sample51; null값은 제외하고 카운트 됨

ALL중복 상관없이 모두 조회, DISTINCT 중복제거하고 조회 기본은 ALL

select count(ALL name), count(DISTINCT name) from sample51;

select sum(quantity) from sample51; 합계

select avg(quantity), sum(quantity)/count(quantity) from sample51; 동일한 평균

select avg(case when quantity is null then 0 else quantity end) 

as avgnull0 from sample51; 널값을 0으로 변환해서 평균

select min(quantity),max(quantity),min(name),max(name) from sample51;

select * from 테이블명 group by 열1, 열2, ... 집계함수로 넘겨줄 인자를 그룹화

group by 할시 중복이 제거되어 distinct와 같은 효과

select name,count(name),sum(quantity) from sample51 group by name;

네임의 값마다 집계가 된다.

내부처리 순서 where -> group by -> having -> select-> order by

where에서는 집계함수 사용불가 대신 select에서 having으로 대체

having에서도 별명 사용불가

select name, count(name) from sample51 group by name having count(name) = 1;

name의 갯수가 1개인 모든 항목의 이름과 갯수를 검색

group by 지정한 열 이외의 열은 집계함수 사용하지 않고는 select에 포함불가

select min(no), name, sum(quantity) from sample51 group by name;

서브쿼리 : 쿼리 안에 (select)문 추가

delete from sample54 where a=(select min(a) from sample54); 

a가 가장 작은 행 삭제

서브쿼리 패턴

1. 하나의 값 반환 80 select min(a) from sample54;

2. 복수의 행, 하나의 열 select no from sample54;

3. 하나의 행, 복수의 열 select min(a),max(no) from sample54;

4. 복수의 행, 복수의 열 select no, a from sample54;

1번 패턴은 스칼라값 단일 값 스칼라 서브쿼리

스칼라 서브쿼리는 where에서 사용가능

select

    (select count(*) from sample51) as sq1,

    (select count(*) from sample54) as sq2 from dual; 

(oracle from dual 을 제외하면 mysql)

update sample54 set a=(select max(a) from sample54);

select * from (select * from samplee54) as sq; 중첩구조, 내포구조, nested구조

오라클에서는 as 제외 

오라클에서 limit 대체 명령

select * from (select * from sample54 order by a desc) sq where rownum <= 2;

insert into sample541 values

( (select count(*) from sample51), (select count(*) from sample54) );

insert into sample541 select 1,2; 동일 insert into sample541 values(1,2);

insert into sample542 select * from sample543; 테이블 복사

상관서브쿼리 

exist(select 명령) 반환 값의 존재 여부 확인

update sample551 set a='있음' where 

    exists (select * from sample552 where no2 = no);

update sample551 set a='없음' where

    not exists (select * from sample552 where no2 = no);

열에 테이블명 붙이기

update sample551 set a='있음' where

    exists (select * from sample552 where sample552.no2 = sample551.no);

열명 in 집합

select * from sample551 where no in (3,5);

select * from sample551 where no in (select no2 from sample 552);

NOT IN은 집합에 값이 포함되어있지 않으면 참 / NULL체크는 불가