본문 바로가기

DataBase/SQL

[SQL] 작업 스케줄러 금번 순위를 산정하는 집계쿼리를 만들면서 해당 집계가 하루에 한번 정해진 시간에 이뤄지도록 해달라는 요청을 받았다. 해서... 집계쿼리를 만들어 놓고 매일 아침 출근하면 일단 쿼리를 실행 시키다.... (개발중이니까.. 머... 이래두 괜찮..)정리를 해야 겠다는 생각이 들어서 방법을 모색하게 되었다. MSSQL 2008에는 '윈도우의 작업 스케줄러'와 흡사한 기능이 있다. 이름 하여 'SQL Server 에이전트' 이다. 스케줄에 등록하기 전에 준비 되어 있어야 할 것은 ... 1. 집계쿼리. (매일 아침 손수 실행 시켰던 집계쿼리를 SP로 등록) 2. 없음. 방법은 아래와 같다. 1. sql server 에이전트 활성화위의 그림에서 sql server 에이전트의 속성으로 들어간다. 2. 속성창에서 서.. 더보기
[SQL] 동적쿼리와 like 구문. 지난 번에 동적 쿼리에 대해서 포스팅 한 적이 있는데.금번에 지난 번 처럼 동적 쿼리를 사용하여야 하고 like 구문을 사용해야 하는 상황에 직면하게 되었다. 요청은 간단하다. 1. 대상인 닉네임 첫 글자를 받는다. 2. 이로 대상을 찾는 SP가 필요하다. 그러하다. 간단한데 일반적인 like 구문은 select * from table_user where name like '홍길%'; 이러한 식이기 때문에 '홍길'을 인자로 받아야 하는 입장에서는 동적쿼리를 고민하지 않을 수 없다. 머 대단한 건 아니고... 방법은 DECLARE 임시변수 nvarchar(17)SET 임시변수 = convert(nvarchar(17), 전달받는 문자열) + '%'SELECT * FROM 테이블 WHERE 컬럽 LIKE 임시.. 더보기
[SQL] 컬럼에 제약 조건 추가. 금번 내용은 지난 번에 'ALTER TABLE'이라는 제목으로 포팅한 적이 있는 내용의 연장선상에 있는 내용이다. 이 내용을 추가 하게된 건, 이 번에 마주한 어떠한 문제 때문이었다.문제의 내용은 음수값이 들어가면 안되는 컬럼에 음수값이 들어가는 문제가 발생한 것이다. 사실 개발중인 프로젝트에서 편의성을 위해서 제공한 치트키의 오동작으로 인해서 발생한문제이니 그쪽에서 처리를 해야 하지만, 일단의 DataBase의 안전성을 위해서 제약 조건을 추가 하기로 했다. 방법은 간단하다. check constraint을 컬럼에 추가해 주면 된다. 테이블에도 추가 하는 방법이 있는데 이는 다음 포스트로 미루기로 하고, 일단 컬럼에 추가하는 방법은 ALTER TABLE 테이블 명 ADD CONSTRAINT 제약조건 명.. 더보기
[SQL] 인덱스 (클러스터, 비클러스터) 개념 인덱스. 1. 개념 A. 간단한 비유로 일반적으로 책 뒤쪽에 위치하는 ‘찾아보기’를 들 수 있다.B. 일 예로, ‘홍길동전’에서 ‘율도국’이라는 단어를 찾는다고 가정해보자. 만일 이 책에 ‘찾아보기’가 없다면 책을 첫 장부터 훑어야 할 것이다. 그런데 찾아보기가 있다면 한번에 찾아 갈 수 있다. 이 것이 인덱스의 기본적인 개념이다. 2. 인덱스 생성시 발생되는 특징A. 검색 속도 향상. i. 시스템의 부하를 줄여, 시스템 전체 성능향상에 기여 가능. B. 인덱스를 위한 추가 공간이 필요.C. 생성에 시간이 소요 될 수 있음.D. INSERT, UPDATE, DELETE가 자주 발생한다면 성능이 많이 하락할 수 있다. 3. 종류A. 클러스터형 인덱스(Clustered Index). i. 테이블당 한 개만 .. 더보기
[SQL] 테이블 변수 이번에 요청을 받은 것은, 1. A, B, C라는 변수를 전달 받아 TABLE1에 업데이트를 한다.2. TABLE2에 해당 데이터가 존재하지 않는다면 INSERT 한다. 라는 것을 요청 받았다. 실상 테이블을 이런식으로 설계를 하면 안되지만, 어쩔 수 없는 기획적인 또한구조적인 사정이 있으니, 일단 가능한 방법을 찾아 보도록 했다. 이 경우 테이블2에 해당 변수가 존재하는지를 알아 볼려면 별도로SELECT를 3번을 하고 INSERT를 최고 3번을 해야 한다. 좀더 간단히 할 수 있을 것 같다는 생각이 들었다. SQL에서 배열을 사용할 수 있다면 이 변수를 하나의 배열에 넣고 LOOP를 돌면서검사하고 INSERT를 할 수 있을꺼 같다. 과연 이런 형태가 가능한가? 일단 배열의 사용은 불가능하다. 이와 비슷.. 더보기
[SQL] ALTER TABLE 어려운 것도 아닌데 자꾸 깜박해서 여기에 적어 놓는다. SQL 에서는 이렇게. 1. 추가. ALTER TABLE 테이블 명 ADD 컬럼명1-1 속성을 줄때 ALTER TABLE 테이블 명 ADD 컬럼명 INT DEFAULT 0 NOT NULL -> INT형으로 NULL을 허용하지 않고 기본값은 0이라는 컬럼을 추가한다. 2. 삭제 ALTER TABLE 테이블 명 DROP 컬럼명 3. 제약조건 추가. ALTER TABLE 테이블 명 ADD CONSTRAINT 제약조건(EX : PRIMARY KEY 컬럼명) 4. 제약조건 제거 ALTER TABLE 테이블 명 DROP CONSTRAINT 제약조건(EX : PRIMARY KEY 컬럼명) 5. 컬럼명 수정 EXEC SP_RENAME '테이블명.기존컬럼명', '변.. 더보기
[SQL] SELECT INTO (임시 테이블 만들기) 사용법 사용법 SELECT * FROM tableName SELECT * INTO temp_tableName(새로만들 테이블) FROM tableName 위에 사용법으로 테이블을 만들게 되면 영구적으로 테이블이 생기게 된다. 물론 DROP 명령으로 지워주면 되긴 하지만 잠시만 쓰고 지울 것이라면 굳이 영구 테이블을 만들지 않고 임시테이블을 만들어 사용하면 된다. 임시 테이블은 전역임시 테이블, 세션 임시 테이블 두개로 나뉜다. 만드는방법사용할 수 있는 범위삭제되는 시기일반 테이블CREATE TABLE다른 세션에서도DROP TABLE세션 임시 테이블 #tablename 해당 세션에서만 세션이 끊어질 때 전역 임시 테이블 ##tablename 다른 세션에서도 세션이 끊어질 때 tempdb의 일반 테이블 CREATE.. 더보기
[SQL] 동적쿼리 금번 작업을 하면서 고민을 하게 만드는 요청을 받았다. 요청 사항을 정리하면 1. 정렬 DB에서 한다.1-1. 정렬할 대상(컬럼)은 서버로 부터 전달 받는다.1-2. 정렬할 대상(컬럼)에 대해서 오름/내림 차순에 대한 결정도 서버로 부터 받는다. 2. 일정 갯수만을 반환한다.2-1. 페이지 개념을 적용한다. 2-2. 정렬된 값에서 요청 받은 페이지의 약속된 열만큼을 반환해야 한다. ※ 이 글을 읽는 분들은 더 좋은 해답이 있다면 언제든 조언을 부탁드립니다. 일단 서버로 부터 받는 인자는 @정렬기준(대상컬럼) TINYINT, @정렬방법(오름/내림) TINYINT, @페이지크기 INT, @페이지넘버 INT를 받는다. 동적쿼리는 문자열을 만들어서 그것을 실행(EXEC) 하는 것이기 때문에 지역 변수들은 NVA.. 더보기
[SQL] IN구문 사용기 얼마전 어느 정도 분량의 데이터를 삽입해줘야 하는 상황이 발생했다.문제는.... 1. 대상 테이블이 IDENTITY가 걸려 있다. 2. 이미 기존에 들어가 있는 데이터가 존재한다. 3. 넣어야 할 데이터가 다른 DB(ORACLE)에 존재한다. 잠시 생각해 보니 DB가 오라클에서 MS로 변경된 걸 모르고 오라클에 신규 데이터를삽입한 사람의 멱을 잡을 수 없으니 일단 변경된 리스트를 받는 선에서 끝내기로 하고어떻게 손쉽게 할 수 있을까를 생각하니. 쓸만한 것이 IN 구문. ※ 더 손쉬운 방법이 있다면 이 글을 보시는 분이라면 누구든 알려주세요. IN 구문의 사용법은 간단하다 다음과 같은 문법을 가진다. SELECT * FROM 테이블명 WHERE 조건을 걸 대상 컬럼 IN ( '값1', '값2', '값3',.. 더보기
[SQL] DB복구후 DB와 사용자 매핑 1. 사용자 생성 Microsoft SQL Server Management Studio 실행> 데이터베이스 > 보안 > 로그인 (속성) > 새로그인 - 로그인이름 (계정)- 'SQL Server 인증' 의 암호- '암호정책 강제 적용' 체크 안함- '암호만료 강제 적용' 체크 안함- 기본데이터베이스 : 사용할 DB선택[확인] 선택 ※ 참고 : DB에 권한을 가진 사용자가 있습니다. > 데이터베이스 > [DB명] > 보안 > 사용자에서 확인이 가능하며 이 사용자명으로 생성하시면 됩니다. 만약 이곳에 사용자가 없을 경우 임의로 사용자를 아무거나 만듭니다. 사용자 이름 : neulwon // 접근 허용할 계정 로그인 이름 : neulwon // 접근 허용할 계정 기본 스키마 : dbo 이사용자가 선택한 스키마.. 더보기
[SQL] 날짜 변경 (DATEADD) 하루 지난 날짜 즉 어제 날짜를 가져와야 할 일이 생겨 찾아보니.DATEADD(대상, 가감, 날짜);이 함수를 사용해서 날짜를 변경해서 가져올 수 있다. 사용 예는 다음과 같다. DECLARE @yesterday char(10) = convert(varchar(10),dateadd(d,-1,GETDATE()),121);DECLARE @tomorrow char(10) = convert(varchar(10),dateadd(d,+1,GETDATE()),121); 더보기
[SQL] 컬럼 속성 변경 ms-sqlserver에서 table 또는 필드 속성변경은 alter문을 사용하여 합니다. alter table table명 alter column 필드명 datatype ex) test table의 fld필드를 varchar -> int로 변경 alter table test alter column fld int 참고) 실제 입력 되어있는 자료중 int형식으로 변경할 수 없는 경우 (''aaa''값과 같이)에는 sqlserver는 에러를 냅니다. alter table table명 add 필드명 datatype ex) test table의 fld1을 varchar 데이타타입 8자리로 추가하는 경우 alter table test add fl.. 더보기
[SQL] 트랜젝션 롤백 오류대응 방안 저장 프로시저를 작성하고 테스트 하던중 첫 에러가 발생하여 ROLLBACK이 일어나던 중 예정된 에러 메시지가 아니라. "EXECUTE 후 트랜잭션 개수는 COMMIT 또는 ROLLBACK TRANSACTION 문이 없음을 나타냅니다" 와 같은 문구가 나타났다. 해결 방법은 예외 상황에서 IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRAN END 와 같이 ROLLBACK을 감싸 주면 해결된다. 더보기
[SQL] SET XACT_ABORT ON의 의미 Transact-SQL 문에서 런타임 오류가 발생할 경우 SQL Server에서 현재 트랜잭션을 자동으로 롤백할 것인지 여부를 지정 CREATE PROC [dbo].[SP_TEMP] @ID INTAS SET NOCOUNT ONSET LOCK_TIMEOUT 60000SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSET XACT_ABORT ON -- 이런 식으로 추가됨. IF (@ID IS NULL ) RETURN -1 BEGIN TRY BEGIN TRAN .... 본문 생략.... 더보기
[SQL] ISNULL()함수 사용법 NULL을 지정된 값으로 변환한다. 지난 번에 Oracle에서 이와 같은 기능을 하는 NVL을설명한 적이 있는데, NVL과 동일한 기능을 수행하는 SQL 함수가 ISNULL 이다. SQL Server에서는 ISNULL ( check_expression , replacement_value ) Oracle에서는 NVL ( check_expression , replacement_value ) [출처] SQL 함수의 모든것 3- 변환 함수(ISNULL / NVL) |작성자 사랑지기 참고로 ISNULL도 뷰가 존재하지 않는 대상에 대해서는 0으로 값을 치환하지 못하기 때문에,ISNULL(MAX(컬럼), 0) 과 같이 집계함수를 같이 사용하면 없는 값에 대해서 뷰를 만들어 반환을가능하게 해준다. 집계함수의 사용은 .. 더보기
[SQL] DISTINCT와 PARTITION BY구문 사용법 흠... 예를 들어서 회원의 거주 지역 테이블이 있다고 가정해보면 다음과 같은 테이블이 존재 할 수 있다. 거주지-----서울경기경기서울경남전남경기 7개의 행이라면 지역을 파악하는데 어려움이 없지만, 기본적으로 DB에 저장되는 회원의수는 수 만이 넘어갈 것이기에 이 경우는 Order by 절을 사용한다고 해도 답이 안나온다. 이 경우 SELECT DISTINCT 거주지 FROM 테이블;과 같이 하면, 다음과 같은 결과를 얻을 수 있다. 거주지------서울경기경남전남 이와 같은 효과로 GROUP BY로 낼 수 있다.SELECT 거주지 FROM 테이블 GROUP BY 거주지;와 같이 한다면 말이다. 이 차이점은... 흠 좀 더 알아봐야 겠다. PARTITION BY는 DISTINCT를 무시한다.해서, 테이.. 더보기
[SQL] between구문 사용법 작업을 하다가 생각나는 것을 바로바로 포스팅 하다 보니 참 두서가 없다. select 문을 사용하던 중, 일정 범위 내의 값을 가져와야 하는 상황이 있었다.그럴때 사용할 수 있는 문법이 between 이다. 예시) SELECT 컬럼1, 컬럼2 FROM TEST_TABLE WHERE ID = 1111 AND 컬럼1 BETWEEN 9 AND 13; 또는 SELECT 컬럼1, 컬럼2 FROM TEST_TABLE WHERE 컬럼1 BETWEEN 100 AND 300; 이런 식으로 사용할 수 있다. 더보기
[SQL] Group by 컬럼 사용 팁 쿼리를 작성하다가 다음 같은 오류를 만났다. SQL Error : ORA-00979 : GROUP BY 표현식이 아닙니다[출처] Group by 팁 - 에러 : SQL Error : ORA-00979 : GROUP BY 표현식이 아닙니다.|작성자 최환수 이 오류는 집계 함수를 사용할 때, 집계 함수에 쌓이지 않는 컬럼들은 전부 group by 리스트에있어야 하는데 ... 이 중에 빠진게 있다는 말이 되겠다. ( 집계 합수 : sum(), count()... 등등 ) 다음의 예를 들어 보면 쉽게 이해가 될 것이라 사료된다. SELECT a.ID, a.column1, sum(NVL(b.VALUE, 0))FROM TEST_TABLE a LEFT JOIN TEST_TABLE b ON ( a.ID= b.ID AN.. 더보기
[SQL] 테이블 삭제시 제약 조건제거 테이블을 삭제 할때 기본적으로 DROP TABLE 테이블명이와 같이 하면 되지만, 종속된 제약 조건이 있을 경우, 삭제가 안될 수 있다.그래서 넣어주는 옵션이. CASCADE CONSTRAINTS이다. DROP TABLE 테이블명 [CASCADE CONSTRAINTS] ;이과 같이 하면, 종속된 제약 조건이 삭제된다. 더보기
[SQL] select쿼리 사용시 유용한 like구문 사용법 select 필드1 from 테이블 where 필드2 like '단어%' 라고 실행을 하면 앞자리에 단어가 포함된 값을 찾아 온다. example1 쿼리 - select 유저이름, 전화번호 from 유저정보테이블 where 유저이름 like '홍%' 결과 홍길동 010-xxx-xxxx 홍아무개 010-xxx-xxxx example2 쿼리 - select 유저이름, 전화번호 from 유저정보테이블 where 유저이름 like '%길%' 결과 홍길동 010-xxx-xxxx 김길동 010-xxx-xxxx 다음과 같은 결과를 얻을 수 있다. 더보기