Dramatic Blog

--NEWID(); //(8-4-4-4-12 = 32자리16진수가CPU와네트워크카드의조합으로만들어냄)

--//임시비번발행, 학부모암호제공, 복권번호생성

SELECT NEWID(); --6B353F11-F5AA-4E0A-BA1A-4A248F7F6936

SELECT SUBSTRING('A3CDDSDF23324',1,4); --결과값A3CD

SELECT SUBSTRING(NEWID(),1,36) ;--에러발생, NEWID()가기본적으로숫자타입

SELECT LEFT(NEWID(),36); --NEWID()를문자열로변환

SELECT REPLACE(SUBSTRING(LEFT(NEWID(),36),1,36),'-',''); --결과값FB2D397F01B34F95A79E89DD7B8126F7

CREATE TABLE T_USER (

  ID VARCHAR(36) NOT NULL PRIMARY KEY

, NAME VARCHAR(100)

);

INSERT T_USER VALUES (NEWID(), '홍길동');

SELECT *

FROM T_USER;

UPDATE T_USER SET NAME='홍길동' WHERE ID ='8205D0A4-2810-4669-ABB1-73F8DA60441B'

SELECT *

FROM T_USER;

ALTER TABLE T_USER ADD PASSWORD VARCHAR(20);

SELECT *

FROM T_USER;

UPDATE T_USER SET PASSWORD = LEFT(NEWID(),8) WHERE NAME='홍길동';

SELECT *

FROM T_USER;

SELECT *

FROM T_USER;

SELECT SUBSTRING(REPLACE(NEWID(),'-','')+REPLACE(NEWID(),'-',''),20,10);

SELECT NEWID();

SELECT TOP 100 NEWID() FROM T_USER;

SELECT *

FROM T_USER;

DELETE FROM T_USER;

SELECT *

FROM T_USER;

INSERT T_USER VALUES ('ABCD2','홍길동',NULL);

CREATE TABLE T_USER2 (

  ID VARCHAR(10) NOT NULL

, NAME VARCHAR(20) NOT NULL

);

INSERT T_USER2 VALUES ('ABCD1','홍길동');

INSERT T_USER2 VALUES ('ABCD2','홍길동');

INSERT T_USER2 VALUES ('ABCD3','홍길동');

SELECT *

FROM T_USER2;

SELECT ID,COUNT(*), NAME

FROM T_USER2

GROUP BY ID, NAME

HAVING COUNT(*) > 1;

--중복되는데이터값뽑아내기

DELETE FROM T_USER;

SELECT TOP 100 NEWID() FROM T_USER;

SELECT *

FROM T_USER;

SELECT TOP 100 NEWID() FROM T_CUSTOMER;

SELECT *

FROM T_CUSTOMER

ORDER BY NEWID(); --랜덤하게정렬하는방법

SELECT TOP 1 NEWID(), CUST_NM FROM T_CUSTOMER ORDER BY NEWID(); --1등당첨자뽑기

SELECT *

FROM T_SALARY;

SELECT *

FROM T_SALARY

WHERE 급여> 300;

SELECT * FROM T_MEMBER;

SELECT * FROM T_MEMBER_JOIN;

SELECT * FROM T_CAFE;

SELECT

  T.NAME AS 이름

, T.ID AS 아이디

, C.NAME AS 카페명

, C.CATEGORY AS 카테고리

, CASE J.PERMISSION

       WHEN 'O' THEN '0.으뜸청지기'

       WHEN 'C' THEN '1.청지기'

       WHEN 'H' THEN '2.리더회원'

       WHEN 'M' THEN '3.우대회원'

       WHEN 'D' THEN '4.정회원'

       ELSE '5.준회원'

  END AS 등급

FROM

  T_MEMBER_JOIN J

    JOIN T_MEMBER T ON J.fk_MEMBER = T.SEQ

       JOIN T_CAFE C ON J.fk_CAFE = C.SEQ;

CREATE VIEW v_MEMBER_VIEW

AS

SELECT

  T.NAME AS 이름

, T.ID AS 아이디

, C.NAME AS 카페명

, C.CATEGORY AS 카테고리

, CASE J.PERMISSION

       WHEN 'O' THEN '0.으뜸청지기'

       WHEN 'C' THEN '1.청지기'

       WHEN 'H' THEN '2.리더회원'

       WHEN 'M' THEN '3.우대회원'

       WHEN 'D' THEN '4.정회원'

       ELSE '5.준회원'

  END AS 등급

FROM

  T_MEMBER_JOIN J

    JOIN T_MEMBER T ON J.fk_MEMBER = T.SEQ

       JOIN T_CAFE C ON J.fk_CAFE = C.SEQ;

SELECT * FROM v_MEMBER_VIEW;

/*

IDENTITY로테이블생성시에는

사용자가UPDATE할경우SET IDENTITY_INSERT 를

해야만한다.

*/

CREATE TABLE T_SN (

  T_SSN INT NOT NULL IDENTITY(100,1)

, T_NAME VARCHAR(100)

, PRIMARY KEY (T_SSN)

);

INSERT T_SN VALUES ('홍길동');

INSERT T_SN VALUES ('홍길동');

INSERT T_SN VALUES ('홍길동');

SELECT *

FROM T_SN;

--TRIGGER를이용한IDENTITY 구현하기

CREATE TABLE T_MEM_INSERT(

  MEM_NO INT NOT NULL PRIMARY KEY

, MEM_NAME VARCHAR(30) NOT NULL

, MEM_ID VARCHAR(30) NULL

);

INSERT T_MEM_INSERT VALUES(1, '홍길동', 'ABCD');

INSERT T_MEM_INSERT VALUES(2, '홍길동', 'ABCE');

INSERT T_MEM_INSERT VALUES(3, '홍길동', 'ABCF');

SELECT MEM_NO, MEM_NAME, MEM_ID FROM INSERTED

INSTEAD OF INSERT [UPDATE, DELETE]

CREATE TRIGGER tr_MEM_INSERT

ON DBO.T_MEM_INSERT

INSTEAD OF INSERT

AS

BEGIN

       SET NOCOUNT ON

       BEGIN TRAN

       BEGIN TRY

             DECLARE @GET_SN INT

             DECLARE @MAX_SN INT

             SELECT @GET_SN =MEM_NO FROM INSERTED

             IF @GET_SN IS NULL OR

                    EXISTS (SELECT * FROM T_MEM_INSERT WHERE MEM_NO=@GET_SN)

             BEGIN

                    SELECT @MAX_SN = MAX(MEM_NO) FROM T_MEM_INSERT

                    INSERT T_MEM_INSERT (MEM_NO, MEM_NAME, MEM_ID)

                           SELECT @MAX_SN +1, MEM_NAME, MEM_ID FROM INSERTED

             END

             ELSE BEGIN

                    INSERT T_MEM_INSERT (MEM_NO, MEM_NAME, MEM_ID )

                           SELECT MEM_NO, MEM_NAME, MEM_ID FROM INSERTED

             END

       END TRY

       BEGIN CATCH

             IF @@TRANCOUNT  > 0

             ROLLBACK TRAN

       END CATCH

       IF @@TRANCOUNT > 0

          COMMIT TRAN

END

------------------------------------------------

/*

--트랜잭션

BEGIN TRAN (BEGIN TRANSACTION)

COMMIT TRAN (COMMIT , COMMIT TRANSACTION)

BEGIN TRAN

ROLLBACK TRAN(ROLLBACK, ROLLBACK TRANSACTION)

------------------------------------------------

*/

SELECT *

FROM T_MEM_INSERT;

INSERT T_MEM_INSERT (MEM_NAME, MEM_ID) VALUES ('홍길동','ABCD005');

SELECT *

FROM T_MEM_INSERT;

UPDATE T_MEM_INSERT SET MEM_NO=7 WHERE MEM_NO =6

SELECT *

FROM T_MEM_INSERT;

--테이블복제후데이터값입력하기

SELECT * INTO T_CUSTOMER5

FROM T_CUSTOMER;

INSERT T_CUSTOMER5(cust_nm, jumin_num, cust_addr, cust_hobby, cust_alias, former_dept, current_dept, cust_memo)

       SELECT cust_nm, jumin_num, cust_addr, cust_hobby, cust_alias, former_dept, current_dept, cust_memo FROM T_CUSTOMER;

SELECT *

FROM T_CUSTOMER5;