210531

 

identity 장난치기

 

identity 속성을 임으로 껏다 킬 수 있다 

use tempdb;

create table testtbl2
(id int identity,
 userName nchar(3),
 age int,
 nation nchar(4) default '대한민국');

go 
insert into testtbl2 values('지민',25,default);

set identity_insert testtbl2 on
insert into testtbl2(id,[userName],[age],[nation])  values(11,'쯔위',18,'대만');
set identity_insert testtbl2 off
insert into testtbl2 values('즈윙',25,default);

set identity_insert testtbl2 on
insert into testtbl2(id,[userName],[age],[nation])  values(3,'또치',19,'우주인');

set identity_insert testtbl2 off
insert into testtbl2 values('마이콜',25,default);

select * from testtbl2; -- 자동으로 넣으면 id 의 가장 큰 값에다가 1 을 추가해준다. 

결과

identity 를 자동으로 넣어 주면 항상 가장 큰 숫자에다가 1을 더해서 입력해준다.

 

SEQUENCE 

- identity 와 비슷하다. 

create table testtbl3 // test3 테이블 생성
(id int,
 userName nchar(3),
 age int,
 nation nchar(4) default '대한민국');

create sequence idSEQ // seqence 값 설정
	start with 1
	increment by 1;	
go


insert into testtbl3 values(next value for idSEQ,'지민',25,default);

alter sequence idSEQ  // seqence 값 재설정
	RESTART WITH 12;
INSERT INTO testtbl3 VAlues(next value for idSEQ,'미나',25,'일본');


select * from testtbl3;

결과

SEQUENCE CYCLE

id 가 반복하면서 입력 되도록 설정

 

CREATE TABLE testTbl4(id INT);
GO
CREATE SEQUENCE cycleSEQ  
  START WITH 100
  INCREMENT BY 100
  MINVALUE 100
  MAXVALUE 300
  CYCLE;  // id 반복 설정 
GO
INSERT INTO testTbl4 VALUES (NEXT VALUE FOR cycleSEQ);
INSERT INTO testTbl4 VALUES (NEXT VALUE FOR cycleSEQ);
INSERT INTO testTbl4 VALUES (NEXT VALUE FOR cycleSEQ);
INSERT INTO testTbl4 VALUES (NEXT VALUE FOR cycleSEQ);
GO
SELECT * FROM testTbl4;

 

 

SEQUENCE auto

CREATE SEQUENCE autoSEQ
  START WITH 1
  increment by 1;
go 

CREATE TABLE testTbl5
(
	id INT default (next value for autoSEQ),
	userName nchar(3)
);

insert into testTbl5(userName) values('지민');
insert into testTbl5(userName) values('쯔위');
insert into testTbl5(userName) values('미나');

select * from testTbl5;

 

identity 처럼 자동으로 id 값 기입 방법

 

대량으로 데티터 입력

1.

INSERT INTO testTbl5(userName) VALUES 
  ('미나1'),
  ('미나2'),
  ('미나3'),
  ('미나4'),
  ('미나5');

2. into

use tempdb
select [BusinessEntityID] as id,[FirstName] as Fname,[LastName] as Lname 
into testTbl7
from AdventureWorks.Person.Person;

select * from testTbl7;

결과

UPDATE SET

update testTbl7	
	set Lname = '없음'
	where Fname = 'kim';


select * from testTbl7
where Lname = '없음' ;

DELETE

delete top(5) testTbl7 where Fname = 'kim';
select * from testTbl7
where Lname = '없음' ;

상위 5 항목 삭제

10 개 -> 5개로 줄어듬

 

MERGE

use sqlDB
select userID,name,addr into memberTbl from userTbl;
select * from memberTbl;

create table changeTBL 
(
	chageType Nchar(4),
	userID char(8),
	name nvarchar(10),
	addr nchar(2)

);

go 
insert into changeTBL Values
('신규가입','CHO','초아','미국'),
('주소변경','LSG',NULL,'제주'),
('주소변경','LJB',NULL,'영국'),
('회원탈퇴','BBK',NULL,NULL),
('회원탈퇴','SSK',NULL,NULL);



select * from changeTBL;

merge memberTbl as M
	using changeTBL as C
	on M.userID = C.userID
when not matched and chageType = '신규가입' then  --userID 가 다르고 chageType 이 신규가입이면 실행
	insert(userID,name,addr) values(C.userID,C.name,C.addr)
when matched and chageType = '주소변경' then
	update set M.addr =C.addr
when matched and chageType = '회원탈퇴' then
	delete;


select * from memberTbl;