[新정보처리기사 실기] SQL 응용 (1/2)

1.SQL -DDL

2.SQL -DCL

3.SQL -DML

4.DML-SELECT-1

5.DML-SELECT-2

6.프로시저

7.트리거

8.사용자 정의 함수

9.제어문

10.커서

 

 

1.SQL -DDL

 

a) DDL(데이터 정의어)의 개념

- DDL는 DB 구조, 데이터 형식,접근 방식 등 DB 구축하거나 수정할 목적으로 사용하는 언어

- DDL은 번역한 결과가 데이터 사전이라는 특별한 파일에 여러 개의 테이블로 저장 됨

 

b) Create Schema= 데이터베이스의 구조와 제약조건에 관한 전반적인 명세 정의

Create Schema 스키마명 Authorization 사용자_id

//소유권자의 사용자 id가 홍길동인 스키마 '대학교'를 정의
Create Schema 대학교 Authorization 홍길동

 

c) Create Domain =속성이 취할 수 있는 값을 미리 정의

Create Domain 도메인명 [as] 데이터_타입
			[default 기본값]
            [constaint 제약조건명 check (범위값)];

Create Domain sex_char(1)
			default '남'
            constaint valid_sex check(value in('남','여');

 

d) Create Table

Create table 테이블명(
	속성명 데이터타입 [defalut 기본값][not nul],...
    [primary key(기본키_속성명)],
  	[unique(대체키_속성명)],
    [foreign key(외래키 속성명)],
    	references 참조테이블(기본키_속성명)]
        [on delete 옵션]
        [on update 옵션],
    [constraint 제약조건명] [check (조건식)]

Create table 학생(
	이름 varchar(15) not null,
    학번 char(8) ,
    전공 char(5) ,
    성별 sex,
    생년월일 date,
    primary key(학번),
  	foreign key(전공) references 학과(학과코드)
        on delete set Null
        on update cascade,
    constraint 생년월일제약 check (생년월일 >='1980-01-01'));
    

 

e) Create View =테이블 내를 참조하여 사용자에게 보여줄 부분만 보여주는 가상 테이블

Create view 뷰명[(속성명,속성명)]
as select 문장

Create view 안산고객(성명,전화번호)
as 
select 성명,전화번호
from 고객
where 주소='안산시'

 

f) Create Index= 검색시간을 단축시키키 위해 만든 보조적인 데이터 구조

Create [unique] index 인덱스명
on 테이블명[속성명 [asc|desc]]
[cluster];

Create unique index 고객번호_idx
on 고객(고객번호 desc);

 

g) Alter Table

alter table 테이블 add 속성명 데이터타입 [default '기본값'];
alter table 테이블 alter 속성명 [set default'기본값'];
alter table 테이블 drop column 속성명 [cascade];

alter table 학생 add 학년 varchar(3);
alter table 학생 alter 학번 varchar(10) not null;
alter table 학생 drop column 방과후과목;

 

h) Drop

drop schema 스키마명 [cascade | restricted]
drop domain 도메인명 [cascade | restricted]
drop table 테이블명 [cascade | restricted]
drop view  뷰명 [cascade | restricted]
drop index 인덱스명 [cascade | restricted]
drop constraint 제약조건명 [cascade | restricted]

 

 

2.SQL -DCL

 

a) DCL(데이터 제어어의 개념)

 -DCL은 데이터의 보안,무결성 회복,병행 제어 등을 정의하는데 사용하는 언어이다.

 -DCL은 데이터베이스 관리자가 데이터를 관리하는 목적으로 사용한다.

 

b) GRANT/REVOKE

grant 사용자등급 to 사용자_id [identified by 암호];
revoke 사용자등급 from 사용자_id

grant resource to Nabi
revoke connect from Nabi
# *resource=데이터 베이스 및 테이블 생성 권한
# *connect= 데이터베이스 정보 검색 권한

 

grant 권한 on 개체 to 사용자 [with grant options]; 
revoke [grant option for] 권한 on 개체 from 사용자 [cascade];

grant all on 고객 to Nabi with grant options
revoke grant option for update  on 고객 from star;

 

c) commit

 -트랜잭션이 성공적으로 끝나면 데이터베이스가 새로운 일관성 상태를 가지기 위해 데이터베이스에 반영

 

d) rollback

 - 아직 commit되지 않은 모든 내용들을 취소하고 데이터베이스를 이전 상태로 되돌리는 명령어

 

e) savepoint

 - 트랜잭션 내에 rollback 할 위치인 저장점을 지정하는 명령어이다.

 

 

 

3.SQL -DML

 

a)DML (데이터 조작어)의 개념

=DML은 데이터베이스 사용자가 응용 프로그램이나 질의어를 통해 저장된 데이터를 실질적으로 관리하는데 사용되는 언어이다.

= DML은 데이터베이스 사용자와 데이터베이스 관리 시스템 간의 인터페이스를 제공한다.

 

b)삽입문

insert into 테이블명([속성명1,속성명2,..])
	values(데이터1,데이터2,..)

insert into 고객(고객번호,이름)
	values(고객번호,이름)

 

c)삭제문

DELETE from 테이블명 [where 조건];

delete from 고객 where 고객번호='41'

 

d)갱신문

update 테이블명
set 속성명=데이터
[where 조건];

update 고객
set 핸드폰번호=010-2222-3212''
where 고객번호='41'

 

 

4,5 .DML-SELECT-1

select 	[predicate] [테이블명.]속성명 [as 별칭] 
		[,그룹함수(속성명) [as 별칭])
		[,window함수 over(partition by 속성명1,속성명2,... order by 속성명n)]
from 테이블명[, 테이블명]
[where 조건]
[group by 속성명,속성명,..]
[having 조건]
[order by 속성명 [ASC | DESC]];

select * from 사원;
select 사원.* from 사원;
select 이름,부서,생일 from 사원;
select 사원.이름,사원.부서, from 사원;
select distinct 주소 from 사원;
select * from 사원 where 부서='기획';
select 이름,주소 from 사원 where 이름= (select 절)

* predicate = ALL,DISTINCT,DISTINCTROW

* window함수=count(속성명),sum(속성명),avg(속성명), max,min,stddev,variance