MSSQL

len()
rtrim(), ltrim()
getdate()
substring()
isnull
nullif
coalesce
case when
convert
create table 000 ()
insert into 000 () values( )
update 000 set 0=0
inner join
left outer join
right outer join
full outer join
cross join
on과 where 차이점 확인
내일은 using 확실히 알기!!
row_number() over(partition by 000 order by 000)
rank() over(partition by 000 order by 000) -- 같으면 죽복순위 그다음은 +중복수
dense_rank() over(partition by 000 order by 000) -- 같으면 죽복순위 그다음은 +1
NATURAL 절과 USING 절은 함께 사용할 수 없다.

-- 인덱스 생성

create index sts_aa_idx on sts (aa)
unique

--인덱스 삭제

drop index sts.sts_aa_idx
drop index jaemin.ind

--칼럼 추가/속성변경

alter table sts add ff varchar(8) default 'N'
alter table sts alter column ff varchar(10)

-- 주키 설정

ALTER TABLE sco ADD CONSTRAINT TEST_TABLE_PK PRIMARY KEY (일련번호, 이름)

DDL : CREATE, DROP, TRUNCATE, ALTER
DML : SELECT, UPDATE, DELETE, INSERT
DCL : GRANT, REVOKE, (ROLLBACK, COMMIT)
TCL : ROLLBACK, COMMIT

-- 외래키 설정

ALTER TABLE 테이블명 ADD CONSTRAINT FK명 FOREIGN KEY (FK설정할 컬럼) REFERENCES 참조할 테이블(참조할 테이블의 컬럼) 옵션

Master : 참조 대상 테이블 / Child : FK 생성 테이블
CASCADE : Master 삭제 시 Child 같이 삭제 CONSTRAINT FK_TEMP2_FID FOREIGN KEY (FID) REFERENCES TEMP(ID) ON DELETE CASCADE
SET NULL : Master 삭제 시 Child 해당 필드 NULL 값 CONSTRAINT FK_TEMP2_FID FOREIGN KEY (FID) REFERENCES TEMP(ID) ON DELETE SET NULL
SET DEFAULT : Master 삭제 시 Child 해당 필드 DEFAULT 값 CONSTRAINT FK_TEMP2_FID FOREIGN KEY (FID) REFERENCES TEMP(ID) ON DELETE SET DEFAULT
NO ACTION : 참조무결성 위반하는 액션은 되지 않음 CONSTRAINT FK_TEMP2_FID FOREIGN KEY (FID) REFERENCES TEMP(ID) ON DELETE NO ACTION

-- 외래키 삭제

alter table 테이블명 drop constraint 외래키이름

-- index 정보 확인

EXEC SP_HELPINDEX A_Table

-- 테이블 컬럼 정보 확인

select * from information_schema.columns
where table_name = 'jaemin'

-- 테이블 제약조건 확인

select

• from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
  where table_name = 'jaemin'

-- fuction

CREATE FUNCTION dd
( @P_PRAMETER1 VARCHAR(100) )
RETURNS VARCHAR(100) AS
BEGIN
declare @rtn varchar(100);
--set @rtn=select convert(varchar(4),getdate(),120);
select @rtn=convert(varchar(4),getdate(),120);
RETURN @rtn;
END;

select dbo.func1('dd')

CREATE FUNCTION f_tb
( @P_PRAMETER1 VARCHAR(100) )
RETURNS @aa table (a varchar(30), b int) AS
BEGIN

if(3=1)
    insert into @aa values('dd',2);
else
    insert into @aa values('dd',3);

RETURN;   

END;

select * from dbo.f_tb('3')

--TO_CHAR

SELECT CONVERT(VARCHAR(10) , GETDATE(),120);
SELECT CONVERT(VARCHAR(16) , GETDATE(),120);

-- Create

Create Table TEST1(
Name Varchar(50)
);

-- Insert

insert into test2(
name
)
values(
'lee'
);

-- Update

update test1
set name = 'leejaemin';

-- left outer join

select * from test1 a left outer join test2 b on (a.name=b.name)
where
b.name is null;

-- Full outer join

select * from test1 a full outer join test2 b on (a.name=b.name);

-- Cross join

select * from test1 a cross join test2 b;

-- case when

select case when 1=1 then 'T' else 'F' end;

-- VIEW 생성

CREATE VIEW jaemin_view AS
SELECT * from jaemin

-- rollup

select
sss,num, count(*) cnt
from jaemin
group by
rollup(sss,num)

-- cube

select
sss,num, count(*) cnt
from jaemin
group by
cube(sss,num)

-- grouping sets

--각각 group by 해서 union all
select
sss,num, count(*) cnt
from jaemin
group by
grouping sets(sss,num)

-- with val

WITH VAL AS(SELECT '' A )
SELECT * FROM VAL