Outsider's Dev Story

Stay Hungry. Stay Foolish. Don't Be Satisfied.

ERWin으로 DB모델링 하기

여기에 수준이 뻔하듯이 DB모델링의 방법이 아니라 ERWind사용하기에 관한 부분이다.

AllFusion  ERwin Data Modeler라는 프로그램이 있다. 이걸로 DB모델링을 하면 편하게 할 수 있고 관계나 그런것을 모두 생각하고 고려하면서 할수 있으며 다하고 나면 SQL 쿼리도 다 만들어주기 때문에 참 편하다.(이번에 처음 써봤다.)

머 그래두 Query를 다 알고 있는게 당연히 좋겠지만.....

사용자 삽입 이미지

처음 들어가서 새로 만들기를 하면 위와같은 창이 나오는데 기본적으로는 논리/물리를 다 만들것이므로 Logical/Physical을 선택한다. 물론 다른 용도라면 원하는 것만 선택하면 된다. 나중에 SQL쿼리를 만들어야 하므로 아래에 타겟 DB를 선택한다. 여기서는 MS SQL Server를 선택했다.

사용자 삽입 이미지

일단 논리DB부터 만든다. 말그대로 논리DB다 그냥 생각나고 필요한대로 한글로 테이블을 만든다. 상단에 ①은 논리/물리 보기모드이고 ②가 테이블을 만드는 아이콘이다. ③번은 관계(Relation)을 설정해 주는 부분으로 1:1 1:N 등이 있다. 아이콘 누르고 관계되는 2개의 테이블을 선택해 주면 된다. 기본적으로 1:N의 경우는 관계를 이어주면 PK가 N족의 FK로 등록되며 PK로 올라간다. PK가 되길 원하지 않는다면 바꿔주면 된다.

사용자 삽입 이미지

테이블에서 오른쪽마우스를 눌러서 Entity Properties로 들어가면 테이블의 이름을 바꾸어 줄수 있고 Attributes에 들어가면 컬럼을 주가해주거나 컬럼의 타입을 결정해 줄수 있다.

사용자 삽입 이미지
논리 DB가 다 만들어지면 이젠 물리DB를 만들면 된다. 상단에서 화면보기를 Physical로 바꾸어주면 물리DB가 된다. 처음에는 논리때처럼 한글로 나오는데 마우스오른클릭으로 Culumn으로 들어가서 Rename으로 실제 DB에서 사용할 이을으로 바꾸어준다.

물론 여기서 바뀌어도 논리쪽은 바뀌지 않는다. 바꿔가면서 비교해 주면서 바꾸면 된다. 논리에서도 타입을 정해주었지만 여기서 정확한 타입을 결정해 주고 NULL여부도 선택해 준다. 컬럼길이의 경우 타입정한 후에 위쪽 ()안에 써주면 된다. 오른쪽 Average쪽에 써넣는 것이 아니다.(처음에 한 실수.. ㅡ..ㅡ)

자동증가값을 사용하는 경우에는 아래쪽 Indetity를 선택하고 1,1이라고 써주면 자동증가 인덱스로 생성된다.

사용자 삽입 이미지

다 만들어주었으면 이제 진짜 DB를 생성해야한다. 물론 DB가 설치되어있어야 한다. 상단메뉴에서 Schema Generation으로 스키마를 만들어 줄 수 있다.

사용자 삽입 이미지

생성할 스키마의 세부항복에 대한 부분을 결정할 수 있다. 자세한 내용들은 나도 아직은 잘 모르겠고 Trigger부분만 모두 체크를 풀어준다. 그 후에 Generate 버튼을 누른다.

사용자 삽입 이미지

여기선 SQL Server를 사용하였으므로 SQL서버에 연결한다. SQL계정로그인하고 DB이름 써주고 서버이름은 여기서는 로컬이라서 그냥 쩜만 찍어주었다.

사용자 삽입 이미지

각 쿼리가 나타나면서 성공여부가 나타난다. 설정이나 관계등에서 잘못되었을때는 Failure 메시지가 나타난다. 문제를 해결한뒤에 다시 스키마를 만들면 된다. 이렇게하면 모델링한 디비를 쉽게 만들어 낼 수 있다.
2007/07/17 23:20 2007/07/17 23:20

SQL 쿼리 정리

이 글은 지극히 개인적인 용도이다.

쿼리는 보기 시작한지도 얼마 안되었고 DB도 여러가지 볼때마다 헷갈리고 써도 쓸때만 찾았다가 또 잊어먹고 하다보니 좀 헛된 검색과 낭비를 좀 막아보고자 모은다.... 아직 초기라 틀린 부분도 많고 좀 목적이 계속된 정리작업이 필요할 것을 염두에 두고 있는 포스팅이기 때문에 다른 사람들이 참고하기엔 무리가 있다고 본다.
(틀린 점도 꽤 있을것도 같기 때문에..)

원래는 SLQ 표준안인 ANSI 쿼리문을 기준으로 모아보려고 했는데 막상 하려니까 ANSI쿼리 정보를 많이 구할수 없어서 일단 작성하고 더 정리해 나가기로 했다.

오라클 / MS SQL

DESC 테이블명; 테의블 정의 보기

SELECT

SELECT * FROM 테이블명 WHERE 필드='조건' AND 필드2='조건2'
SELECT 필드1, 필드2, 필드3 FROM 테이블명
SELECT * FROM TAB; 존재하는 모든 테이블 보기
SELECT DISTINCT 필드1 FROM 테이블명; 중복제거하기
SELECT 필드1 FROM 테이블 WHERE 필드2 BETWEEN 00 AND 00; //00부터 00사이에서
SELECT 필드1 FROM 테이블 WHERE 필드2 IN (00, 00); //00과 00중에서
SELECT 필드1 FROM 테이블 WHERE 필드2 LIKE '%단어%';
SELECT 필드1 FROM 테이블 WHERE 필드2 IS NULL; or NoT IS NULL



UPDATE

UPDATE 테이블명 SET 필드='값', 필드2='값', 필드3='값' WHERE 필드 LIKE '조건'

INSERT

INSERT INTO 테이블명(필드1, 필드2) VALUES ('값', '값' );

비교연산자 : >, >=, <, <=, =, <>(not equal)

DROP TABLE 테이블명;

CREATE TABLE 테이블명 (
      필드명 타입 조건,
      id varchar2(15) primary key,
      pass varchar(15) not null,
      no number(5)
)


시퀀스 생성 : CREATE SEQUENCE 시퀀스이름
                   increment by 1
                   start with 1
                   nomaxvalue
                   nocycle
                   nocache;

외래키 생성 : ALTER TABLE 테이블명 ADD ( FOREIGN KEY (필드) REFERENCES 테이블명2);

칼럼 수정 : ALTER TABLE 테이블명 MODIFY (필드 타입);

컬럼 추가 : ALTER TABLE 테이블명 ADD ( 필드이름 필드타입 );

칼럼 리네임 : ALTER TABLE 테이블명 RENAME COLUMN 컬럼이름 TO 새이름;

Null값 처리하기 : SELECT isNULL(필드, '0') FROM 테이블

방금 인서트한 자동증가값 ID 가져오기 : SELECT @@IDENTITY (현재 세션에서만 사용가능)
현재 테이블의 가장큰 큰큰값 가져오기 : SELECT IDENT_CURRENT('테이블명')
수행된 SQL문에 의해 영향받은 행의 수 : SELECT @@ROWCOUNT

날짜 계산하기 : SELECT * FROM 테이블명 WHERE DATEADD(DAY, CONVERT(INT, 컬럼명), 날짜컬럼) > getdate()

테이블 정보보기 : EXEC sp_help 테이블
컬럼 정보보기 : EXEC sp_columns 테이블

앨리어스 사용 : SELECT 필드 AS A FROM 테이블 (별칭에 특수문자 있으면 [] 사용]

주석 : -- or /* */

변수의 선언 : DECLARE @변수명 자료형, @변수명 자료형
변수 할당 : SET @변수명 = 값
변수값 가져오기 : SELECT @변수명

일부분만 가져오기 : SELETC TOP 5 컬럼 FROM 테이블

범위정하기 SELECT 컬럼 FROM 테이블 WHERE 컬럼 BETWEEN 10 AND 20
                SELECT 컬럼 FROM 테이블 WHERE 컬럼 IN ('aaa', 'bbb', 'ccc')

패턴매칭 : SELECT 컬럼 FROM 테이블 WHERE 컬럼 LIKE '%제목%"
               _ : 어떤것이든 한 문자
               % : 없거나 아무 글자오거나
               [] : []안에 있는 글자들 - [b-f]
               [^] : ^다름에 있는 글자를 제외한 다른 것

중복제거 : SELECT DISTINCT 컬럼 FROM 테이블

GROUP BY : SELECT 컬럼1, sum(컬럼2) FROM 테이블 WHERE 조건 GROUP BY 컬럼1 HAVING sum(컬럼2) >= 30
(계산함수가 반드시 있어야 한다, GROUP BY ALL을 사용하면 WHERE제외된것 도 포함)

이너조인 : SELECT 컬럼 FROM 테이블 INNER JOIN 테이블 ON 컬럼=컬럼
아웃터조인 : SELECT 컬럼 FROM 테이블 LEFT OUTER JOIN 테이블 ON 컬럼=컬럼
                  (어느한쪽의 데이터를 모두 가져온다.)

IN : SELECT 컬럼 FROM 테이블 WHERE 컬럼2 IN ( SELECT 컬럼2 FROM 테이블 )
     (IN안의 하위질의는 DISTINCT가 붙은 것으로 동작)
EXISTS : SELECT 컬럼1 FROM 테이블  WHERE EXISTS ( SELECT * FROM 테이블 WHERE 조건 )
              (EXISTS는 하위쿼리가 어떤 결과라도 돌려지면 참)

임시테이블 만들기 : SELECT INTO SELECT * INTO 새테이블명 FROM 테이블 (다른세션에서도 가능, DROP해야 삭제됨)
                            SELECT INTO SELECT * INTO #새테이블명 FROM 테이블 (해당세션에서만 가능, 세션끊어지면 삭제됨)
                            SELECT INTO SELECT * INTO ##새테이블명 FROM 테이블 (다른세션에서도 가능, 세션끊어지면 삭제됨)

UNION : SELECT 컬럼1, 컬럼2 FROM 테이블
            UNION
            SELECT 컬럼1, 컬럼2 FROM 테이블

            (UNION은 컬럼의 자료형과 순서가 맞아야 한다. 중복데이터는 제거되며 UNION ALL을 하면 중복데이터도 가져온다.)

컬럼 추가 : ALTER TABLE 테이블명 ADD 새컬럼 VARCHAR(10) NULL
컬럼 변경 : ALTER TABLE 테이블명 ALTER COLUMN 컬럼 VARCHAR(10) NULL
컬럼 삭제 : ALTER TABLE 테이블병 DROP COLUMN 컬럼

삭제 : DELETE FROM 테이블명 WHERE 조건

업데이트 : UPDATE 테이블 SET 컬럼 = 값 WHRER 조건

트랜잭션 : BEGIN TRAN
               ROLLBACK
               COMMIT




덧) SELECT에 왠만하면 * 는 사용하지 않는다.

덧) WHERE절에서 연산자의 왼쪽은 가동하지 않는다. 컬럼을 가공하면 색인을 사용할 수 없다.
        WHERE 컬럼 * 3 < 10 말고 WHERE 컬럼 < 10 / 3 을 사용하자

2007/07/14 01:40 2007/07/14 01:40