ms-SQL 세 번째 수업

create database 서일

use 서일

create table 부서
(
    부서번호 char(1) constraint pk_부서_부서번호 primary key,
    부서명 varchar(20) constraint nn_부서_부서명 not null,
    부서위치 varchar(20) constraint uk_부서_부서위치 unique
)

create table 사원
(
    사번 char(5) primary key,
    이름 varchar(10) constraint nn_사원_이름 not null,
    기본급 int default 1000000,
    전화 varchar(20) constraint uk_사원_전화 unique,
    부서 char(1) constraint fk_사원_부서 references 부서(부서번호),
    constraint ck_사원_사번 check(사번 like '[A-Za-z]%'),
    constraint ck_사원_기본급 check(기본급 between 1000000 and 10000000),
    constraint ck_사원_전화번호 check(전화 like '02%'),
)

create table 급여
(
    사번 char(5) references 사원(사번),
    급여일 datetime default getdate(),
    기본급 int default 1000000 constraint nn_급여_기본급 not null,
    세금 as (기본급 * 0.05),
    실수령액 as (기본급-(기본급*0.05)),
    constraint pk_급여_사번급여일 primary key(사번, 급여일)
)

drop table 부서
drop table 사원
drop table 급여

insert into 부서 values ('A', '인사부', '배양관 204호')

insert into 사원 values ('A1125', '홍길동', 2500000,'021','A')

select * from 부서
select * from 사원
select * from 급여

insert into 급여(사번, 기본급) values('A1125', 1000000)

sp_help 급여

alter : 구조를 변경

필드 : 필드를 추가, 필드 삭제, 필드 수정(필드 데이터 타입, 크기)
*alter 문으로 할 수 없는 일 : 필드명 수정

제약조건 : 제약조건을 추가, 삭제
* 제약조건은 수정할 수 없다!

alter table 테이블명 add 필드명 데이터타입 [제약조건]
alter table 테이블명 alter column 필드명 데이터타입
alter table 테이블명 drop column 필드명

1) 부서 테이블에 필드 추가
 부서장 명, 고정길이 10자
 
alter table 부서 add 팀장이름 char(10)
 
2) 사원 테이블에 성별 필드 추가
 고정2, 체크제약조건도 함께 추가 남/여
 
alter table 부서 add 성별 char(2) check (성별 in ('남', '여'))
 
3) 부서장 명, 가변형 20으로 변경

alter table 부서 alter column 팀장이름 varchar(20)

select * from 부서

update 부서 set 팀장이름='울랄라세션' where 부서명='인사부'

4) 부서장명 필드 삭제

alter table 사원 drop column 팀장이름

alter table 부서 alter column 팀장이름 varchar(6)

5) 성별 필드 삭제

alter table 사원 drop column 성별

제약조건 관련 문법

alter table 테이블명 add 테이블수준의제약조건설정
alter table 테이블명 drop constraint 제약조건명

1) 사원테이블 성별에 걸려있는 check 제약조건 삭제

alter table 사원 drop constraint CK__사원__성별__3C69FB99

2) 사원 테이블에 있는 핸드폰 번호 필드 추가
 가변형 20
 
alter table 사원 add 핸드폰번호 varchar(20)
 
3) 핸드폰 번호에 제약조건을 추가
 010으로 시작하는 것만 입력 가능하도록
 
alter table 사원 add constraint ck_사원_핸드폰번호 check(핸드폰번호 like ('010%'))

sp_help 사원

부서 -> 사원 -> 급여

*자식레코드가 있더라도 부모레코드를 지우고자 한다면

 on delete cascade 옵션을 넣어주면 된다.
 
 
 
create table 게시판
(
 일련번호 int identity(1,1) not null,
 제목 varchar(5) not null,
 내용 varchar(50)
)

alter table 게시판 add 작성자 varchar(10)

alter table 게시판 add 작성일 datetime default getdate()

alter table 게시판 add constraint ck_게시판_제목 check (제목 like '[a-z]%')

alter table 게시판 drop constraint ck_게시판_내용

alter table 게시판 add constraint ck_게시판_내용 check (내용 not like '%퀙%' or 내용 not like '%짱나%')

alter table 게시판 drop constraint ck_게시판_내용

insert into 게시판(제목, 내용, 작성자) values ('hi', 'hello', 'asdf')
insert into 게시판(제목, 내용, 작성자) values ('test', 'ip ni da', 'asdf')

alter table 게시판 alter column 제목 varchar(100)

alter table 게시판 drop column 작성자

sp_help 게시판

alter table 게시판 drop constraint ck_게시판_제목

alter table 게시판 drop constraint ck_게시판_내용