DB 내용 입력중

줄바꿈 문자가 필요할 경우

 

print '이름 : ' + CHAR(13) + CHAR(10) + '김또깡'

 

이렇게 하면 된다.

줄바꿈 문자는  CHAR(13) + CHAR(10)

 

 

CHAR can be used to insert control characters into character strings.

The following table shows some frequently used control characters.

Control character

Value

Tab

char(9)

Line feed

char(10)

Carriage return

char(13)

 

 

Carriage Return & Line Feed

10진수 16진수 ASCII

10        0x0A    LF

13        0x0D    CR

캐리지 리턴 : 커서의 위치를 현재 줄의 맨 처음으로 보냄

라인 피드 :  커서를 다음 줄로 옮김.

유닉스는 LF만으로 개행을 표현하고 윈도우즈는 CR/LF의 조합으로 개행을 표현한다.

 

 

 

참고 : http://msdn.microsoft.com/en-us/library/ms187323.aspx

반응형
저작자표시 비영리 변경금지


데이터를 암/복호화 하기위해 AES암호화를 사용하고있다.
헌데 java, C# 코드는 찾기 쉬운데 .. MSSQL에서 직접 사용할 수 있는 sp가 없더라..
그래서 만들어봤다.

.net 코드로 만든 클래스 라이브러리를 MSSQL에서 바로 호출이 가능하다.
방법은 이렇다.

1. 코드를 작성한다. (C#으로 되어있는 AES 암/복호화 코드를 copy해왔다.)  컴파일하여 .dll 파일을 얻는다.

using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Security.Cryptography;

namespace AESCrypto
{
    public class CAesCrypto
    {
        public static void Encrypt(SqlString textToEncrypt, SqlString key, out SqlString strValue)
        {
            try
            {
                RijndaelManaged rijndaelCipher = new RijndaelManaged();
                rijndaelCipher.Mode = CipherMode.CBC;
                rijndaelCipher.Padding = PaddingMode.PKCS7;

                rijndaelCipher.KeySize = 0x80;
                rijndaelCipher.BlockSize = 0x80;
                string strKey = key.ToString();
                byte[] pwdBytes = Encoding.UTF8.GetBytes(strKey);
                byte[] keyBytes = new byte[0x10];
                int len = pwdBytes.Length;
                if (len > keyBytes.Length)
                {
                    len = keyBytes.Length;
                }
                Array.Copy(pwdBytes, keyBytes, len);
                rijndaelCipher.Key = keyBytes;
                rijndaelCipher.IV = keyBytes;
                ICryptoTransform transform = rijndaelCipher.CreateEncryptor();
                string strTextToEncrypt = textToEncrypt.ToString();
                byte[] plainText = Encoding.UTF8.GetBytes(strTextToEncrypt);
                strValue = Convert.ToBase64String(transform.TransformFinalBlock(plainText, 0, plainText.Length));

            }
            catch (Exception ex)
            {
                strValue = ex.ToString();
                throw new Exception(ex.Message);
            }
        }

        public static void Decrypt(SqlString textToDecrypt, SqlString key, out SqlString strValue)
        {
            try
            {
                RijndaelManaged rijndaelCipher = new RijndaelManaged();
                rijndaelCipher.Mode = CipherMode.CBC;
                rijndaelCipher.Padding = PaddingMode.PKCS7;

                rijndaelCipher.KeySize = 0x80;
                rijndaelCipher.BlockSize = 0x80;
                string strTextToDecrypt = textToDecrypt.ToString();
                byte[] encryptedData = Convert.FromBase64String(strTextToDecrypt);

                string strKey = key.ToString();
                byte[] pwdBytes = Encoding.UTF8.GetBytes(strKey);
                byte[] keyBytes = new byte[0x10];
                int len = pwdBytes.Length;
                if (len > keyBytes.Length)
                {
                    len = keyBytes.Length;
                }
                Array.Copy(pwdBytes, keyBytes, len);
                rijndaelCipher.Key = keyBytes;
                rijndaelCipher.IV = keyBytes;
                byte[] plainText = rijndaelCipher.CreateDecryptor().TransformFinalBlock(encryptedData, 0, encryptedData.Length);

                strValue = Encoding.UTF8.GetString(plainText);
            }
            catch (Exception ex)
            {
                strValue = ex.ToString();
                throw new Exception(ex.Message);
            }
        }
    }
}

 

2. MSSQL에서 CLR을 사용할수 있게 변경한다.

-- CLR을 사용할수 있도록 변경
exec sp_configure 'clr enabled', 1
reconfigure with override 


3. 해당 DB에 권한을 부여한다.

 ALTER DATABASE [DATABASE_NAME] SET
 TRUSTWORTHY ON


4. 1에서 만든 DLL로 ASSEMBLY를 생성한다.

CREATE ASSEMBLY AESCrypto
FROM 'D:\AESCrypto.dll'     <- dll의 물리적 경로예제
WITH PERMISSION_SET = UNSAFE 


4.1. 소유자가 달라서 안되면...소유자를 변경한다.
 - 해당메시지는 DB를 만든 사용자와 복원한 사용자가 다를때 나타난다.

 /*
master 데이터베이스에 기록된 데이터베이스 소유자 SID가 데이터베이스 '[DATABASE_NAME]'에 기록된 데이터베이스 소유자 SID와 다릅니다.
ALTER AUTHORIZATION 문을 사용하여 데이터베이스 '[DATABASE_NAME]'의 소유자를 다시 설정하여 이 문제를 해결해야 합니다.
*/
SELECT owner_sid FROM sys.databases WHERE database_id=DB_ID()
SELECT sid FROM sys.database_principals WHERE name=N'dbo'
EXEC sp_changedbowner '[USER_ID]'
ALTER AUTHORIZATION ON [DATABASE_NAME] TO [domain\user]


5. 프로시져를 생성한다.

CREATE PROCEDURE usp_AESCryptoEncrypt
    @strText        NVARCHAR(100),
    @strKey         NVARCHAR(50),
    @strEnc        NVARCHAR(200) OUTPUT
    AS     EXTERNAL     NAME AESCrypto.[AESCrypto.CAesCrypto].Encrypt

--
CREATE PROCEDURE usp_AESCryptoDecrypt
    @strText          NVARCHAR(100),
    @strKey        NVARCHAR(50),
    @strDec        NVARCHAR(200) OUTPUT
    AS     EXTERNAL     NAME AESCrypto.[AESCrypto.CAesCrypto].Decrypt

6. 사용해본다.

DECLARE @strEnc NVARCHAR(200), @return_value int
EXEC @return_value = [dbo].[usp_AESCryptoEncrypt]
@strText
, @strKey
, @strEnc = @strEnc OUTPUT

 

7. 참고 사이트 및 호환성 문제.

* 참고 :  http://zmeun.tistory.com/46

* 호환성 문제.

출처 : http://livelock.tistory.com/entry/DB-%ED%98%B8%ED%99%98%EC%84%B1-%EB%AC%B8%EC%A0%9C

SQL Server 2000->2005 마이그레이션 등을 수행한 후,
 2005부터 지원되는 SQL문을 사용하려고 하는 경우, 다음과 같은 메시지가 나타날 때가 있다.
 
Msg 325, Level 15, State 1, Line 6
'PIVOT' 근처의 구문이 잘못되었습니다. 이 기능을 사용하려면 현재 데이터베이스의 호환성 수준 값을 더 높게 설정해야 합니다. 저장 프로시저 sp_dbcmptlevel에 대해서는 도움말을 참조하십시오.

에러 메시지에서 볼 수 있듯이, 호환성과 관련된 문제이다.
따라서 다음과 같은 구문을 실행하여 호환성 문제를 해결한다. 

EXEC sp_dbcmptlevel '[Database 명]', [호환성 번호]

 호환성 번호는 80: SQL Server 2000, 90: SQL Server 2005 등이다.

 

 

 

 

반응형

* tempDB 를 tempDB2 로 변경하기

ALTER DATABASE tempDB
SET single_user
go

ALTER DATABASE tempDB
MODIFY NAME = tempDB2
go

ALTER DATABASE tempDB
SET multi_user

반응형
mssql 도 rownum 된다.

페이징에 사용한 예>
- 한 페이지에 10개씩 출력되는 게시판의 첫번째 페이지.
- TEST_TABLE ( name varchar(20), regdate datetime) 라고 가정하고.
 
SELECT *
FROM ( 
SELECT Row_Number() OVER (ORDER BY regdate DESC) AS rownum, *
FROM TEST_TABLE
) T1
WHERE rownum BETWEEN 1 AND 10

이렇게 하면된다.

아래는 온라인 설명서 이다.

SQL Server 2005 온라인 설명서(2008년 11월)
ROW_NUMBER(Transact-SQL)

업데이트: 2006년 7월 17일

결과 집합 파티션 내의 행 일련 번호를 반환합니다. 각 파티션의 첫 번째 행은 1로 시작합니다.

항목 링크 아이콘Transact-SQL 구문 표기 규칙

구문
복사
ROW_NUMBER ( )     OVER ( [ <partition_by_clause> ] <order_by_clause> )
인수
<partition_by_clause>

FROM 절이 생성한 결과 집합을 ROW_NUMBER 함수가 적용되는 파티션으로 나눕니다. PARTITION BY 구문은 OVER 절(Transact-SQL)을 참조하십시오.

<order_by_clause>

파티션에서 ROW_NUMBER 값이 행에 할당되는 순서를 결정합니다. 자세한 내용은 ORDER BY 절(Transact-SQL)를 참조하십시오. 순위 함수에 <order_by_clause>가 사용된 경우 정수는 열을 나타낼 수 없습니다.

반환 유형

bigint

주의

ORDER BY 절은 지정된 파티션 내에서 행에 고유 ROW_NUMBER가 할당되는 순서를 결정합니다.

예
ms186734.note(ko-kr,SQL.90).gif참고:
OVER 절의 ORDER BY는 ROW_NUMBER를 정렬합니다. 'Row Number'가 아닌 열 기준으로 정렬하는 SELECT 문에 ORDER BY 절을 추가하면 결과 집합은 외부 ORDER BY를 기준으로 정렬됩니다.

다음 예에서는 연간 판매 실적에 따라 AdventureWorks의 영업 사원에 대한 ROW_NUMBER를 반환합니다.

복사
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Contact c 
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a 
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;

다음 예에서는 행 번호 50부터 60까지의 행을 OrderDate순으로 반환합니다.

복사
USE AdventureWorks;
GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
) 
SELECT * 
FROM OrderedOrders 
WHERE RowNumber BETWEEN 50 AND 60;

다음 예에서는 PARTITION BY 인수를 사용하는 방법을 보여 줍니다.

복사
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,ROW_NUMBER() OVER 
    (PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS 'Row Number'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Contact c 
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a 
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;

 
출처 : http://msdn.microsoft.com/ko-kr/library/ms186734(SQL.90).aspx
반응형



출처 : http://lael.be/75
반응형

SELECT DATEPART(dw, GETDATE()) 

결과 >
1 - 일
2 - 월
3 - 화
4 - 수
5 - 목
6 - 금
7 - 토
반응형

한참 찾았다. 흠.

SELECT time_to_sec( timediff( now(), stime ) );  
/* stime 부터 지금 까지 초과한 시간을 초로 환산한 결과임.  정수로 출력. */
반응형


instr 은 있는데 ..  뒤에서 부터 찾는 instrb 가 없어서 만들어봤다.

DELIMITER $$

DROP FUNCTION IF EXISTS `dbname`.`uft_InStrB` $$
CREATE DEFINER=`username`@`%` FUNCTION `uft_InStrB`(v_str varchar(1000), v_index varchar(100)) RETURNS int
BEGIN
  BEGIN
    DECLARE x_index int;

     SET v_str = REVERSE(v_str);

     SELECT instr(v_str, v_index) into x_index;

     SET x_index = (Length(v_str) - x_index) + 1;

    RETURN x_index;
  END;
END $$

DELIMITER ;

예>
SELECT uft_instrb ('123_123_23', '_');    //결과 :  8

반응형
1. 백업

C:\>mysqldump -uroot -p[비밀번호] [백업할DB명] > [백업파일명].sql

이렇게 하면 [백업파일명].sql 파일이 생성된다.
이 파일에는 테이블 생성 및 데이터 insert 등의 sql 문장이 가득 들어있다.

2. 복구

C:\>mysql -uroot -p[비밀번호] [복구할DB명] < [복구할파일명].sql

이렇게 하면 복구 된다.  참 ~ 쉽다.

전체를 백업할 때는 mysql 데이터베이스도 함께 백업 받아두어야 한다.


실제 예>
1. 백업

C:\> mysqldump -uroot -p1111 test > test_backup.sql

test 데이터베이스가 test_backup.sql 파일로 백업된다.

2. 복구

C:\> mysql -uroot -p1111 test < test_backup.sql

test 데이터베이스에 백업한 내용이 복구 된다.


- 헉~ 데이블과 데이터만 백업되네요.. 프로시저, 함수 등은 백업이 안되네요.. 다른 방법이 있을텐데.. 찾으면 다시 올릴게요
반응형

Rand() 함수가 존재하며 그 결과값은 0부터 1 사이의 값을 리턴한다. (예: 0.43325987654098)

따라서 0 부터 9 사이의 난수를 발생하려면...

SELECT  Floor( Rand() * 10 );

참고로 Floor( 값 ) 는 값보다 작은 정수 중 가장 큰 수를 반환한다.
반응형

+ Recent posts

티스토리툴바