MS-SQL
데이타
검색
Like 패턴 주의점
연산자 1.
SELECT 문의 연산 함수 1) Ascii() - 문자열의 제일 왼쪽 문자의 아스키 코드 값을 반환(Integer) 예) SELECT Ascii('abcd') >> 결과는 a의 아스키 코드값인 97 반환 2) Char() - 정수 아스키 코드를 문자로 반환(Char) 예) SELECT Char(97) >> 결과는 a 반환 3) Charindex() - 문자열에서 지정한 식의 위치를 반환 예) SELECT Charindex('b','abcde') >> 결과 : 2 -- 인수값이 3개일때 마지막은 abcde 에서의 문자열 검색 시작위치를 말하며 2인경우는 bcde 라는 문자열에 대해서 검색 3인 경우는 cde 라는 문자열에 대해서 검색 하게 된다. 4) Difference() - 두 문자식에 SUONDEX 값 간의 차이를 정수로 반환 예) SELECT Difference('a','b') 5) Left() - 문자열에서 왼쪽에서부터 지정한 수만큼의 문자를 반환 예) SELECT Left('abced',3) 결과 >> 3 6) Len() - 문자열의 길이 반환 예) SELECT Len('abced') 결과>>5 7) Lower() - 대문자를 소문자로 반환 예) SELECT Lower('ABCDE') 결과 >> abcde 8) Ltrim() - 문자열의 왼쪽 공백 제거 예) SELECT Ltrim(' AB CDE') 결과>> AB CDE 9)Nchar() - 지정한 정수 코드의 유니코드 문자 반환 예) SELECT Nchar(20) 결과 >> 10) Replace - 문자열에서 바꾸고 싶은 문자 다른 문자로 변환 예) SELECT Replace('abcde','a','1') 결과>>1bcde 11) Replicate() - 문자식을 지정한 횟수만큼 반복 예) SELECT Replicate('abc',3) 결과>> abcabcabc 12) Reverse() - 문자열을 역순으로 출력 예) SELECT Reverse('abcde') 결과>> edcba 13) Right() - 문자열의 오른쪽에서 부터 지정한 수 만큼 반환(Left() 와 비슷 ) 예) SELECT Right('abcde',3) 결과>> cde 14)Rtrim() - 문자열의 오른쪽 공백 제거 예) SELECT Rtrim(' ab cde ') 결과>> ' ab cde' <-- 공백구분을위해 ' 표시 15) Space() - 지정한 수만큼의 공백 문자 반환 예) SELECT Space(10) 결과 >> ' ' -- 그냥 공백이 나옴 확인을 위해서 SELECT 'S'+Space(10)+'E' 결과 >> S E 16) Substring() - 문자,이진,텍스트 또는 이미지 식의 일부를 반환 예) SELECT Substring('abcde',2,3) 결과>> bcd 17)Unicode() - 식에 있는 첫번째 문자의 유니코드 정수 값을 반환 예)SELECT Unicode('abcde') 결과 >> 97 18)Upper() - 소문자를 대문자로 반환 예) SELECT Upper('abcde') 결과>> ABCDE ※ 기타 함수 Tip 19) Isnumeric - 해당 문자열이 숫자형이면 1 아니면 0을 반환 >> 숫자 : 1 , 숫자X :0 예) SELECT Isnumeric('30') 결과 >> 1 SELECT Isnumeric('3z') 결과 >> 0 20) Isdate() - 해당 문자열이 Datetime이면 1 아니면 0 SELECT Isdate(getdate()) 결과 >> 1 SELECT Isdate('aa') 결과 >> 0 ※ 날짜및 시간함수 정리 getdate() >> 오늘 날짜를 반환(datetime) 1> DateAdd() - 지정한 날짜에 일정 간격을 + 새 일정을 반환 예) SELECT Dateadd(s,2000,getdate()) 2> Datediff() - 지정한 두 날짜의 간의 겹치는 날짜 및 시간 범위 반환 예)SELECT DateDiff(d,getdate(),(getdate()+31)) 3> Datename() -지정한 날짜에 특정 날짜부분을 나타내는 문자열을 반환 예) SELECT Datename(d,getdate()) 4> Datepart() -지정한 날짜에 특정 날짜부분을 나타내는 정수를 반환 예) SELECT Datepart(d,getdate()) ** 돌려주는값(약어) >> Datename , Datepart 은 결과 값은 같으나 반환 값의 타입이 틀림. 5> Day() -지정한 날짜에 일 부분을 나타내는 정수를 반환 예) SELECT Day(getdate()) -- 일 반환 SELECT Month(getdate()) -- 월 반환 SELECT Year(getdate()) -- 년 반환 4. 형변환 함수 그룹화 함수
데이타 편집
INSERT INTO 테이블명 VALUES (값1, 값2, ...) /* 모든 필드에 데이타를 넣을 때 */ - UPDATE~SELECT UPDATE A
오브젝트 1. Table(테이블) CREATE TABLE 데이타베이스이름.소유자이름.테이블이름 (칼럼 데이타형 제약, ...) /* 테이블 만들기 */ 2. View(뷰) 3. Stored Procedure(저장 프로시저) CREATE PROC 프로시저명 AS SQL문 /* 저장 프로시저 */ 에제
DECLARE @EmpNum int, @t_name VARCHAR(20) SET @t_name = '강우정' 이런식으로 다중입력도 가능함. SELECT @no = no, @name = name, @level = level 4. Trigger(트리거) CREATE
TRIGGER 트리거명 on 테이블명 FOR INSERT AS SQL문 /* INSERT 작업이 수행될때 */ 5. Cursor(커서) SET NOCOUNT on /* SQL문의 영향을 받은 행수를 나타내는 메시지를 숨긴다 */ 보안과 사용자 권한 1. SA(System Administrator) 2. DBO(Database Owner) 3. DBOO(Database Object Owner) 4. USER(일반 사용자) [SQL 서버 2005 실전 활용] ① 더 강력해진 T-SQL : http://blog.naver.com/dbwpsl/60041936511 -- ANY (OR 기능) WHERE 나이 >= (SELECT 나이 FROM .......) -- GROUP BY ALL (WHERE 절과 일치 하지 않는 내용은 NULL 로 표시) SELECT 주소, AVG(나이) AS 나이 FROM MEMBER WHERE 성별='남' GROUP BY ALL 주소 -- 모든 주소는 나오며 성별에 따라 나이 데이터는 NULL -- WITH ROLLUP SELECT 생일, 주소, SUM(나이) AS 나이 FROM MEMBER GROUP BY 생일, 주소 WITH ROLLUP -- 생일 과 주소를 요약행이 만들어짐 -- WITH CUBE (위의 예제를 기준으로, 주소에 대한 별도 그룹 요약데이터가 하단에 붙어나옴) -- GROUPING(컬럼명) ROLLUP 또는 CUBE 의 요약행인지 여부 판단(요약행이면 1 아니면 0) SELECT 생일, 주소, GROUPING(생일) AS 생일요약행여부 -- COMPUTE (GROUP BY 와 상관없이 별도의 테이블로 요약정보 생성) SELECT 생일, 나이 FROM MEMBER COMPUTE SUM(나이), AVG(나이) -- PIVOT (세로 컬럼을 가로 변경) EX) 학년/ 반 / 학생수 1 1 40 1 2 45 2 1 30 2 2 40 3 1 10 3 2 10 위와 같이 SCHOOL 테이블이 있다면 SELECT 그룹할컬럼명, [열로변환시킬 행] FROM 테이블 PIVOT( SUM(검색할열) FOR 옆으로만들 컬럼명 IN([열로변환시킬 행]) ) AS 별칭 --실제 쿼리는 SELECT 학년, [1반], [2반] FROM SCHOOL PIVOT( SUM(학생수) FOR 반 IN([1반], [2반]) ) AS PVT -- UNPIVOT (가로 컬럼을 세로로) SELECT 학년, 반, 학생수 FROM SCHOOL UNPIVOT( FOR 반 IN( [1반], [2반] ) ) AS UNPVT -- RANK (순위) SELECT 컬럼명, RANK() OVER( ORDER BY 순위 기준 컬럼명) AS 순위 FROM 테이블 -- PARTITION BY (그룹별로 순위 생성) SELECT 컬럼명, RANK() OVER( PARTITION BY 그룹기준컬러명 ORDER BY 순위기준컬럼명) AS 순위 FROM 테이블 -- FULL OUTER JOIN (LEFT 조인과 RIGHT 조인을 합한것) 양쪽 어느 하나라도 데이가 있으면 나옴 -- ROW_NUMBER (순차번호 생성) SELECT ROW_NUMBER() OVER( ORDER BY 기준열) AS 번호, 컬럼명 FROM 테이블 자료형 (데이터타입) MSSQL 서버에서 사용하는 데이터 타입(자료형)은 두가지가 있다. 1. 시스템에서 제공하는 System data type 내가 생각해도 참 깔끔하게 정리를 잘 해놨다. -_-;; 성능향상을 위해서라면 가능한 작은 자료형을 사용하도록 하자. 불필요하게 int를 쓰는 경우가 흔한데, 사용될 데이터의 범위를 생각해 본 후, 가장 작은 범위의 자료형을 사용하도록 하자. 2. 사용자가 정의 하는 User data type 사용자 정의 자료형이 왜 필요한가? C언어를 비로한 몇 가지 언어에서 나타나는 사용자 정의 데이터 유형과 같다. 프로젝트에 참가하는 사람들이 동일한 데이터 타입을 사용하고자 원하거나, 한 컬럼에 대한 데이터 유형을 더 쉽게 사용하려고 할 때 적용시킬 수 있다. 사용 방법 sp_addtype [새로운 타입 이름], '[SQL 데이터 타입]' 예 sp_addtype empID, 'CHAR(10)' sp_addtype empNO, 'CHAR(12)' * 참고로 자료형을 바꾸는 함수로는 CONVERT() 가 있다. 사용방법 SELECT CONVERT(CHAR(30), title) FROM BOOKS --> title 라는 컬럼을 CHAR(30) 으로 변환하여 가져오는 것이다. SELECT CONVERT(VARCHAR(10), 35) --> 35 라는 숫자를 VARCHAR(10) 으로 변환한다. 흐름 제어문의 종류 흐름 제어문이란 언어의 처리 순서를 변경하거나 변수를 선언하는 등의 문장을 말한다. ○ GOTO 라벨 - GOTO 를 만나면 라벨 부분으로 무조건 건너뛴다. 라벨은 라벨: 으로 정의한다. 예) DECLARE... SET... table_label1: . . IF .... GOTO table_label1 . --> GOTO table_label1 을 만나면 table_label1: 부분으로 건너 뛴다. ○ RETURN - RETURN 은 무조건 수행을 중지 하고 원래 호출된 곳으로 돌아간다. ○ IF / ELSE - 이름만 들어도 알만한 문법이다. 주의 할 점은 조건문 안의 SQL문장이 둘 이상이라면 BEGIN / END 로 묶어 준다. 예) IF @begin > @end BEGIN SELECT * FROM 테이블1 WHERE 조건 RETURN END ELSE SELECT * FROM......... ○ WHILE / BREAK / CONTINUE - WHILE 다음에 조건으로 반복을 하게 되고, BREAK 를 만나면 무조건 WHILE 을 벗어나고, CONTINUE 를 만나면 무조건 WHILE 로 돌아간다. 예) WHILE 조건 BEGIN 반복하는 동안 실행할 문장들... IF 조건 BREAK IF 조건 CONTINUE END ○ EXEC[UTE] - EXEC 와 EXECUTE 는 같은 의미이다. - 두가지 용도로 사용되는데, - 첫 번째, 스토어드 프로시저를 실행할 때 사용한다. 예) EXEC stored_procedure - 두 번재, SQL 문장을 동적으로 변화시키며 수행할 수 있다. 예) DECLARE @sql VARCHAR(255) SET @sql = 'SELECT COUNT(*) FROM ' SET @sql = @sql + 'titles ' EXEC(@sql) --> 실제 수행되는 문장은 SELECT COUNT(*) FROM titles 가 된다. ○ CASE - 단순 CASE 예) SELECT CASE type WHEN 'a' THEN 'Apple' WHEN 'b' THEN 'Banana' ELSE 'No Data' END AS 과일 , price FROM titles - 검색된 CASE 예) SELECT title_id , qty AS '수량' , CASE WHEN qty >= 50 THEN 'A' WHEN qty >= 30 THEN 'B' ELSE 'C' END AS '등급' FROM titles NULLIF : 표현식 1과, 2를 비교 >> 표현식 1과, 2를 비교 두 표현식이 같으면 NULL 을 리턴, 같지 않으면 표현식 1을 리턴 COALESCE : 뒤에 오는 표현식중에 처음으로 오는 NULL 이 아닌 값을 리턴 SELECT COALESCE(NULL, 3, 4) -- 3
리턴 SELECT COALESCE(NULL,NULL, NULL)--문법오류 사용예 : 하나만 값을 가지고 있는 컬럼에서 비교해서 값을 가져올때 매우 좋다 SET : 세성 옵션 (한번설정하면 세션이 끊어 질때까지 유용) ===================================================================================== SET nocount OFF SET rowcount [n] ex) SET rowcount 4 SET rowcount 0 : 보여줄 목록의 행수를 선택한다. 목록의 정렬의 임의로 설정되므로 필요한 순서가 있다면 ORDER BY 를 사용해야 한다. ============================== 유니크 키 넣기 ============================== http://blog.daum.net/gksdy2000/8006521 |