MS-SQL 데이터베이스 만들기 [ create table , create database ]

--데이터베이스생성.
/*
testdb
d:\test
데이터파일 이름 testdb.mdf
처음크기 : 5mb
증가단위 : 1mb
최대크기 :10mb
로그파일 이름 testdblog.ldf

*/

create database testdb
on primary
( name='testdata'
, filename='d:\test\testdb.mdf'
, size=5mb
, maxsize=10mb
, filegrowth=1mb
)
log on
( name='testlog'
, filename='d:\test\testdblog.ldf'
, size=6
, maxsize=10mb
, filegrowth=10%
)

--select * from test01

 

drop database testdb

create table test01
(
 empno int,
 ename char(10)
)

exec sp_help test01

--alter 열추가
alter table test01
add hiredata datetime

--교체
alter table test01
alter column ename char(5)

--삭제
alter table test01
drop column hiredata

--@@ 대소문자구분

create table test01
(
 eid int
, ename char(10)
)

 

insert test01 values(1,'aaa')
insert test01 values(2,'AAA')
insert test01 values(3,'Aaa')
insert test01 values(4,'aAa')
insert test01 values(5,'aaA')
insert test01 values(6,'AAa')
insert test01 values(7,'aAA')
insert test01 values(8,'AaA')

select * from test01
where ename = 'aaa'

create table test02
(
 eid int
, ename char(5) collate korean_wansung_cs_ai
)

insert test02 values(1,'aaa')
insert test02 values(2,'AAA')
insert test02 values(3,'Aaa')
insert test02 values(4,'aAa')
insert test02 values(5,'aaA')
insert test02 values(6,'AAa')
insert test02 values(7,'aAA')
insert test02 values(8,'AaA')

select * from test02 where ename = 'Aaa'

 

exec sp_help emp
select * from emp

--테이블명 수정.
exec sp_rename test02 , test05

exec sp_rename 'test05.ename' , 'sname'

exec sp_renamedb test408 , test

create table test03
(
 eid int
, ename char(10)
)

exec sp_help test03

alter table test03
alter column eid char(5)

alter table test03
alter column ename int

insert test03 values('12345',345)

alter table test03
alter column eid char(3)

alter table test03
alter column ename char(10)

create table test04
(
 eid int
, ename char(10)
)

insert test04 values (1,'a');
insert test04 values (2,'bbb');
insert test04 values (3,'cccc');

alter table test04
alter column ename int

insert test01 values(1,'aaa')
insert test01 values(2,'bbb')
insert test01 values(null,'ccc')
insert test01 values(3,null)

select * from test01

2. unique
/*
유일한 값, 중복되는 값이 없다.
테이블에 여러 컬럼에 선언이 가능하다.
null이 한번만 가능하다.
*/

create table test02
(
 eid int unique
, ename char(10)
)

insert test02 values(1,'aaa')
insert test02 values(2,'bbb')
insert test02 values(1,'ccc') --에러
insert test02 values(null,'bbb')
insert test02 values(null,'ddd') --에러
insert test02 values(3,'aaa')

exec sp_help test02
exec sp_helpconstraint test02

create table test03
(
 eid int constraint uq_test03 unique
, ename char(10)
)

alter table test03
drop constraint uq_test03

alter table test03
add constraint uq_test03_01 unique(eid)

exec sp_helpconstraint test03

3. default

create table test04
(
 eid int
, ename char(10)
, city char(10) constraint df_test04 default '서울'
)

insert test04 values(1,'aaa','대구')
insert test04 values(2,'bbb','인천')
insert test04 values(3,'ccc','광주')
insert test04 values(3,'ccc','광주')
insert test04 (eid, ename) values(4,'ddd')
insert test04 (eid, ename)values(5,'eee')
insert test04 (eid, ename)values(6,'fff')

select * from test04

4. check
/*
컬럼에 원하는 특정한 값만 입력을 받고싶을 때 사용
insert,update에서만 검사한다.
*/

create table test05
(
 eid int
, ename char(10)
, deptno int constraint ch_test05 check(deptno in(10,20,30))
)

insert test05 values(1,'aaa',10)
insert test05 values(2,'bbb',20)
insert test05 values(3,'ccc',30)
insert test05 values(4,'ddd',20)
insert test05 values(5,'eee',50) --에러

alter table test05
drop constraint ch_test05

select * from test05

alter table test05 with nocheck
add constraint ch_test05 check (deptno in(10,20,30))

exec sp_helpconstraint test05

create table test06
( eid int
, ename char(10)
, deptno int
)

alter table test06
alter column eid int not null

alter table test06
add constraint uq_test06 unique(ename)

exec sp_helpconstraint test06

alter table test06
add constraint df_test06 default 10 for deptno

5. primary key
/*
테이블당 하나의 제약 조건만 선언 가능하다.
not null 속성, unique 속성을 갖고 있다.
*/

create table test07
(
 eid int constraint pk_test07 primary key
, ename char(10)
)

insert test07 values (1,'aaa')
insert test07 values (2,'bbb')
insert test07 values (3,'ccc')
insert test07 values (1,'ddd')--에러
insert test07 values (null,'eee')--에러

create table test08
( eid int
, ename char(10)
)

exec sp_help test08

alter table test08
alter column eid int not null

alter table test08
add constraint pk_test08 primary key(eid)

alter table test08
drop constraint pk_test08

6.foreign key
/*
primary key 를 참조하는 제약 조건
*/

create table pktest
( eid int
, ename char(10)
, deptno int constraint pk_pktest primary key
)

add constraint pk_pktest primary key

create table fktest
( saname char(10)
, sadept int constraint fk_fktest foreign key
 references pktest(deptno)
)

insert pktest values(1,'aaa',10)
insert pktest values(2,'aaa',20)
insert pktest values(3,'aaa',30)
insert pktest values(3,'aaa',40)

insert fktest values('tom',10)
insert fktest values('bill',20)
insert fktest values('kim',30)
insert fktest values('lee',40)
insert fktest values('park',50)--에러

alter table fktest
drop constraint fk_pktest

alter table fktest
drop constraint fk_fktest

alter table pktest
add constraint pk_pktest primary key(sadept)

alter table fktest
add constraint fk_fktest foreign key(sadept)
references pktest(deptno)