데이터베이스 3 - SQL

db 다운

mysql community

https://dev.mysql.com/downloads/workbench/

 

 

DDL 데이터 정의어

- DB 구조 정의

 

테이블 생성

create table 테이블이름

(

  속성명 데이터타입 [not null] [default] [primary key],

   foreign key(속성명) references 참조테이블명(속성명),

   primary key(속성명),

   unique(속성명),

   constraint 제약조건명 check(조건),,

  [on delete 옵션],

  [on update 옵션]

);

- on delete/on update : 삭제나 업데이트시 옵션 지정 가능

 

create table option

- set null : 널 설정

- cascade : 관련 데이터 삭제

- no action : 조치 x

- set default : 디폴트값 설정

 

테이블 생성 예시

기본키, 고유키 등록 예시

 

외래키 등록 예시

 

제약조건 등록 예시

 

테이블 삭제 drop table

- drop table 테이블명 {restrict | cascade};

- restrict :참조하는 뷰나 제약조건 있을 시 삭제 불가

- cascade : 참조하는 뷰나 제약조건 있을시 그것들도 같이 삭제

ex) "취미'라는 이름의 테이블 삭제 참조하는 뷰나 제약조건이 있을 경우 삭제

 

기존 테이블 변경 alter table

alter table 테이블이름

[add 열이름 데이터타입] [default 값] |

[drop 열이름] [무결성제약조건] [restrict | cascade]

[modify 열이름 [데이터타입] (drop default | set default 값)];

1. 열추가/기본값 설정

2. 열 삭제

3. 열 변경

4. 열 변경/기본값 설정

 

스키마 생성

- create 스키마명 authorization 사용자아이디;

 

뷰 생성

- 뷰를 정의

- create view 뷰명[속성명 ...] as select문

 

DCL 데이터 제어어

- 권한 제어/데이터 복원 적용 등

- GRANT 권한 부여, REVOKE 권한 회수

 

1. 테이블 권한 부여

  -> grant 권한 on 테이블명 to 사용자계정명 [with grant option]

  -> revoke [grant option for] 권한 on 테이블명 from 사용자

- 권한 종류 : all, select, insert, delete, update, alter

- with grant option :  타 사용자에게 재부여

 

2. 사용자 등급 지정 및 해제

- 사용자 등급 : dba, resource db와 테이블 생성 가능자, connect 일반 사용자

- grant 사용자등급 to 사용자 [identified by 암호];

- revoke 사용자등급 from 사용자

 

 

 

commit

- 데이터베이스 변경사항 반영 명령

- commit;

 

rollback

- commit 하기전 변경사항 취소

- robback [to 세이브포인트지점];

 

savepoint

- 롤백할 지점 설정

- savepoint [세이브포인트명];

0. 오토커밋부터 끄기 - 커밋 시 세이브 포인트가 사라지고, 롤백할수없으므로 우선 오토커밋 설정부터끈다.

1. 사원 행 추가, 세이브포인트 s1 지정

2. 1번 사원 호봉 15로 변경

 

3. savepoint s1로 롤벡 -> 호봉이 12로 돌아옴

 

 

데이터 조작어

- db 사용자가 데이터 사용하는 언어. 검색, 삽입, 삭제, 수정 등

- select [* or 속성명] from 테이블/뷰명 where 조건

- update 테이블명 set 속성=값 where 조건

- delete from 테이블명 where 조건

- insert into 테이블명[(속성들)] values(값들);

 

1. select 명령

select [all|distinct] [열 목록 as 새이름] from 테이블 목록

[where 조건]

[group by 열목록]

[having 그룹 조건]

[order by 열목록 [asc|desc]];

- all : 중복되는 행 그대로 표시

- distinct : 중복되는행 삭제 표시

- as : 열 이름을 새이름으로 대치

- asc : 오름차순 (기본값)

- desc : 내림차순 (지정해야함)

 

조건 1. 호봉 20보다 큰경우

조건 2. 이름이 길동으로 끝나는 사람

조건 3. 이름이 김으로 시작하는사람

 

조건 4. 호봉으로 그룹

 

조건 5. 그룹핑 조건

조건 6. 호봉이 11보다 크고 30보다 작은 사람

조건 7. 호봉이 null 인 경우

 

as - 열대치

distinct - 호봉 열 데이터의 중복된 행 제거

 

정렬 - 호봉 내림차순 정렬

특정 튜플 갱신 update

 

 

 

집계함수*그룹 함수)

- count(속성명) : 해당 그룹 속성 개수

- sum(속성명) : 해당 그룹 속성 합

- avg(속성명) : 해당 그룹 속성 평균 

- max(속성명)

- stddev(속성명)

- variance(속성명)

 

 

호봉 그룹별 인원 수

 

인원이 2이상인 호봉 그룹 사원 수