use testA
CREATE TABLE userTbl
( userID char(8) NOT NULL PRIMARY KEY,
name nvarchar(10) NOT NULL,
birthYear int NOT NULL,
addr nchar(2) NOT NULL,
mobile1 char(3),
mobile2 char(8),
height smallint,
mDate datetime
);
CREATE TABLE buyTbl
( num int IDENTITY NOT NULL PRIMARY KEY,
userid char(8) NOT NULL
FOREIGN KEY REFERENCES userTbl(userid),
prodName nchar(6) NOT NULL,
groupName nchar(4),
price int NOT NULL,
amount smallint NOT NULL
);
INSERT INTO userTbl VALUES('YJS', '유재삭', 1972, '서울', '011', '1111111', 178, '2008-8-8');
INSERT INTO userTbl VALUES('KHD', '강후동', 1970, '경북', '011', '2222222', 182, '2007-7-7');
INSERT INTO userTbl VALUES('KKJ', '김국징', 1965, '서울', '019', '3333333', 171, '2009-9-9');
INSERT INTO userTbl VALUES('KYM', '김용먼', 1967, '서울', '011', '4444444', 177, '2005-5-5');
INSERT INTO userTbl VALUES('KJD', '김제둥', 1974, '경남', NULL , NULL , 173, '2003-3-3');
INSERT INTO userTbl VALUES('NHS', '남히석', 1971, '충남', '016', '6666666', 180, '2004-4-4');
INSERT INTO userTbl VALUES('SDY', '신동업', 1971, '경기', NULL , NULL , 176, '2010-10-10');
INSERT INTO userTbl VALUES('LHJ', '이휘저', 1972, '서울', '011', '8888888', 180, '2009-4-4');
INSERT INTO userTbl VALUES('LKK', '이강규', 1960, '경남', '018', '9999999', 170, '2008-8-8');
INSERT INTO userTbl VALUES('PSH', '박수흥', 1970, '서울', '019', '0000000', 183, '2011-5-5');
INSERT INTO buyTbl VALUES('KHD', '운동화', NULL , 30, 2);
INSERT INTO buyTbl VALUES('KHD', '노트북', '전자', 1000, 1);
INSERT INTO buyTbl VALUES('KYM', '모니터', '전자', 200, 1);
INSERT INTO buyTbl VALUES('PSH', '모니터', '전자', 200, 5);
INSERT INTO buyTbl VALUES('KHD', '청바지', '의류', 50, 3);
INSERT INTO buyTbl VALUES('PSH', '메모리', '전자', 80, 10);
INSERT INTO buyTbl VALUES('KJD', '책' , '서적', 15, 5);
INSERT INTO buyTbl VALUES('LHJ', '청바지', '의류', 50, 1);
INSERT INTO buyTbl VALUES('PSH', '운동화', NULL, 30, 2);
INSERT INTO buyTbl VALUES('LHJ', '책', '서적', 15, 1);
INSERT INTO buyTbl VALUES('PSH', '운동화', NULL, 30, 2);
INSERT INTO buyTbl VALUES('LHJ', '책' , '서적', 15, 1);
INSERT INTO buyTbl VALUES('PSH', '운동화', NULL , 30, 2);
SELECT * FROM userTbl;
SELECT * FROM buyTbl;
create table aaa
(
id int constraint pk_aaa_id primary key,
--제약조건의 이름을 주려면 컨스트레인트 주고 이름주면 됨
--이런것을 컬럼수준의 제약조건이라고 한다.
name varchar(20),
tel varchar(20)
--(또다른 제약조건 방법: id에 프라이머리키 쓰지말고 맨밑에 primary key(id)라고 주면
--이것을 테이블 수준의 제약조건이라고 한다. 이때에는 위에 primary key를 지워준다.
--이름 붙일때는 constraint pl_aaa_id primary key(id) 문법
) ;
insert into aaa values (1001,'홍길동', '111111');
select * from aaa
drop table bbb
execute sp_help aaa --스토어드 프로시저 sp_help 그 테이블의 정보를 출력
--(맨 밑에 constraint-제약조건 주목해서 볼 것)
--항상 스토어드 프로시저를 출력할때는 execute를 쓸것. 줄여서 exec
create table bbb(
id2 int,
addr varchar(20),
pid int,
constraint pk_bbb_id2 primary key(id2),
constraint fk_bbb_pid foreign key(pid)
references aaa(id)
)
exec sp_help bbb
--만일 프라이머리키가 두개 이상라면 무조건 테이블 수준의 정의밖에 안된다. p414
--alter table로 제약조건 지우기
alter table bbb
drop constraint fk_bbb_pid; --드래그로 갖다 끌 수 있다.
alter table bbb
drop constraint pk_bbb_id2 --프라이머리키 제약조건도 삭제할 수 있다.
--alter table로 제약조건 재설정하기
alter table bbb
add
constraint pk_bbb_pid2 primary key(id2)
alter table bbb
add
constraint fk_bbb_pid foreign key(pid)
references aaa(id) ---포린키 설정하기
select * from usertbl
alter table usertbl
add
homepage varchar(50) --컬럼 새로 생성, not null은 쓸 수 없다. not null 제약조건에 걸림
alter table usertbl
drop column homepage --'홈페이지' 컬럼 지우기
alter table usertbl
add
homepage varchar(50) default 'http://mem.or.kr' not null
--not null을 쓰고 싶을 때는 'default 제약 조건'을 써주면 된다. 디폴드값을 명시함으로서 해결
--이상태에서 hopmepage 컬럼을 지우력 하면 안 지워진다. 컬럼을 지우기 전에 디폴트 제약조건을 지운다.
alter table usertbl
drop constraint DF__userTbl__homepag__5CD6CB2B --homepage 지우려면 not null 제약조건을 먼저 지운다.
exec sp_help usertbl
--alter table로 데이터 형식 변경하기
insert into usertbl values
('kkk', '박차고나온놈이무척샘이나',2012,'서울','111','1111',50,getdate())
--이름이 10글자를 넘으므로 오류난다.
alter table usertbl
alter column
name nvarchar(15) --name의 데이터형을 바꿀 수 있다. 15글자까지 넣을수 있도록 변경
select * from usertbl
--유니크 제약 조건(제약 조건을 건 값이 '유일'해야한다. 프라이머리키 이외에 p417
--체크 제약 조건
create table ccc
(
money int check(money>=0),
height int check (height>=0 and height <=300),
addr varchar(50) check(addr like '대%') -- 이 조건이 하나라도 맞지 않으면 인서트 인투가 안됨.
)
insert into ccc values
(300, 170, '서울') --안들어감. 주소가 항상 '대'로 시작해야 하므로.
insert into ccc values
(300, 170, '대한민국 설')
select * from ccc
---같은 내용 약간 다름
use testA;
select * from usertbl;
select * from buytbl;
create table aaa
(
id int constraint pk_id primary key, --constraint를 붙여주면 테이블의 이름을 사용자가 넣을 수 있다.. / 컬럼수준 명세
name varchar(20),
tel varchar(50)
);
create table aaa
(
id int,
name varchar(20),
tel varchar(50),
constraint pk_id primary key(id) -- 테이블 수준의 제약조건
);
select * from aaa;
exec sp_help aaa; -- 테이블 상태확인 스토어드 프로시저
drop table aaa;
create table bbb
(
id int,
name varchar(20),
pid int
constraint pk_bbb_id primary key(id), -- 테이블 수준 / primary key 제약조건
constraint fk_bbb_pid foreign key(pid) references aaa(id) -- 테이블수준 / foreign key 제약조건
);
select * from bbb;
exec sp_help bbb;
alter table bbb -- 테이블 변경문
drop constraint fk_bbb_pid; -- 서브 명령어 foreign key 제약조건 삭제
alter table bbb
add constraint fk_bbb_pid foreign key(pid) references aaa(id); -- foreign key 제약조건 삽입
select * from usertbl;
exec sp_help usertbl;
alter table usertbl
drop constraint DF__userTbl__homepag__2B3F6F97;
alter table usertbl
add
homepage varchar(50) default 'http://www.seoil.ac.kr' null;
alter table usertbl
add
email varchar(30) not null;
--디폴트 제약조건 포함 지우는 방법
exec sp_help usertbl;
alter table usertbl
drop constraint df_usertbl_hompage;
alter table usertbl
drop column homepage;
--열의 데이터 형식변경
exec sp_help usertbl;
alter table usertbl
alter column
name nvarchar(20); --10글자에서 20글자로 바꾸는 과정
-- 이름변경은 sp_rename 프로시저 활용
-- unique 제약조건
create table ccc
(
id int unique
);
insert into ccc values(1);
select * from ccc;
--check 제약 조건
create table ddd
(
money int check(money >= 0),
age int check(age >= 0 and age <= 120),
addr varchar(20) check(addr like '김%')
);
insert into ddd
values(100, 200, '서울');
insert into ddd
values(100,20,'서울');
insert into ddd
values(100,20,'김서울');