|
금요일, 9월 04, 2009
DB에 빈값이 들어오다니 ...
처녀,총각 SQL 입문
여자 프로그래머의 신랑감 찾기
SELECT 키큰놈,잘생긴놈,MAX(돈많은놈)
FROM 총각
WHERE 키 > 175 AND 나이 < 30
ORDER BY 돈많은놈 DESC;
남자 프로그래머의 신랑감 찾기
SELECT 키크고,예쁘고,MAX(돈많은)
FROM 처녀
WHERE 키 > 168 AND 체형='날씬' AND 나이 < 28
ORDER BY 돈많은 DESC;
SELECT HEIGHT,GOODLOOKING,MAX(RICH)
FROM NOTMARRIED
WHERE HEIGHT > 175 AND AGE < 30
ORDER BY RICH ASC;
--댓글---------------------------------------------------
| 어눌한 |
2003/10/30 18:42
키 큰놈이랑 잘생긴놈이랑 그룹으로 묶어 줘야 할것 같은데..ㅡㅡa
흠 그것까지 원한다면 .. 정말 도적X ... 노처녀로 늙어야 하겠지요 .... ^^;
{type:1, commentNo: "2", modifySeq: "2", emoticonNo: "11", isSecret: "false", blogOwner: "true", isReply : "false"}
group by 가 빠졌네요..ㅋㅋㅋ
[오라클] 세그먼트 확인
세그먼트 정보 확인
-----------------------------------------
col segment_name format a12
col tablespace_name format a14
select segment_name, tablespace_name, segment_ID, FILE_ID
from dba_rollback_segs
MS SQL Server 2000의 TOP과 Oracle의 ROWNUM을 이용한 갯수제한
오라클 DB를 위한 솔루션
TOP 키워드는 ANSI SQL standard 가 이니다. 그래서 오라클에서는 지원하지 않는다. 하지만 다행스럽게도 우리는 오라클 stored procedure에 대하여서도 동일한 솔루션을 얻을수 있다. 오라클에서는 가상컬럼인 ROWNUM을 지원한다. 오라클로부터 조회된 각각의 ROW들에는 조회된 순서대로 ROW number가 assign 된다. (첫번째 row에대하여 1부터 시작한다.) 우리는 ROWNUM을 사용해서 퀴리로부터 가져올 row수를 제한할 수 있다.
예를 들어, stored procedure로부터 첫 11개 row를 가져오고 싶으면, 오라클에서는 아래와같은 코드를 사용해서 가져온다.
SELECT * FROM MyTable WHERE ROWNUM < 11;
page navigation 을 구현하기 위해 Oracle stored procedure로 primary key값을 전달한다.
Select *
From tbl_SiteList
Where siteID > @SiteID and ROWNUM < 11
Order by siteID
Oracle OLEDB driver로부터 오라클 스토어드프로시져를 access할때의 제한사항을 명심해야한다. ADO 2.1의 Oracle OLEDB provider 는 오라클 Stored procedure로부터 REF 커서타입을 리턴하지 못한다. 데이터를 가져오기 위해서는 PL/SQL 테이블을 선언해야만 한다. 그러나 ADO 2.5에서는 REF CURSOR 타입을 리턴할수 있다. 다음의 코드는 오라클 package와 procedure를 생성하는 예제이다.
CREATE OR REPLACE PACKAGE PageNavi AS
TYPE Nav_cursor IS REF CURSOR ;
Procedure Prc_Forward (SiteID IN NUMBER, Rtn_cursor IN OUT Nav_cursor);
END curspkg_join;
/
CREATE OR REPLACE PACKAGE BODY PageNavi AS
Procedure Prc_Forward (SiteID IN NUMBER, Rtn_cursor IN OUT Nav_cursor)
IS
local_cursor Nav_cursor;
BEGIN
IF SiteID <> 0
THEN
OPEN local_cursor FOR
Select *
from tbl_SiteList
where siteID > SiteID and ROWNUM < 11
order by siteID;
END IF;
Rtn_cursor := local_cursor;
END Prc_Forward;
END PageNavi;
/
위 코드는 SiteID 의 조건에 해당하는 레코드셋을 리턴할 것이다. 레코드의 갯수는 ROWNUM 에 의해 걸러진다. 그러면 이제 ADO 2.5를 통해서 package를 부를수 있고, 결과를 ASP page에 보여줄 수 있다.
MYSQL에서는 limit로 이러한 것을 지원한다.
select * from table_01 where secret=0 order by hit desc limit 10;
TOP 키워드는 ANSI SQL standard 가 이니다. 그래서 오라클에서는 지원하지 않는다. 하지만 다행스럽게도 우리는 오라클 stored procedure에 대하여서도 동일한 솔루션을 얻을수 있다. 오라클에서는 가상컬럼인 ROWNUM을 지원한다. 오라클로부터 조회된 각각의 ROW들에는 조회된 순서대로 ROW number가 assign 된다. (첫번째 row에대하여 1부터 시작한다.) 우리는 ROWNUM을 사용해서 퀴리로부터 가져올 row수를 제한할 수 있다.
예를 들어, stored procedure로부터 첫 11개 row를 가져오고 싶으면, 오라클에서는 아래와같은 코드를 사용해서 가져온다.
SELECT * FROM MyTable WHERE ROWNUM < 11;
page navigation 을 구현하기 위해 Oracle stored procedure로 primary key값을 전달한다.
Select *
From tbl_SiteList
Where siteID > @SiteID and ROWNUM < 11
Order by siteID
Oracle OLEDB driver로부터 오라클 스토어드프로시져를 access할때의 제한사항을 명심해야한다. ADO 2.1의 Oracle OLEDB provider 는 오라클 Stored procedure로부터 REF 커서타입을 리턴하지 못한다. 데이터를 가져오기 위해서는 PL/SQL 테이블을 선언해야만 한다. 그러나 ADO 2.5에서는 REF CURSOR 타입을 리턴할수 있다. 다음의 코드는 오라클 package와 procedure를 생성하는 예제이다.
CREATE OR REPLACE PACKAGE PageNavi AS
TYPE Nav_cursor IS REF CURSOR ;
Procedure Prc_Forward (SiteID IN NUMBER, Rtn_cursor IN OUT Nav_cursor);
END curspkg_join;
/
CREATE OR REPLACE PACKAGE BODY PageNavi AS
Procedure Prc_Forward (SiteID IN NUMBER, Rtn_cursor IN OUT Nav_cursor)
IS
local_cursor Nav_cursor;
BEGIN
IF SiteID <> 0
THEN
OPEN local_cursor FOR
Select *
from tbl_SiteList
where siteID > SiteID and ROWNUM < 11
order by siteID;
END IF;
Rtn_cursor := local_cursor;
END Prc_Forward;
END PageNavi;
/
위 코드는 SiteID 의 조건에 해당하는 레코드셋을 리턴할 것이다. 레코드의 갯수는 ROWNUM 에 의해 걸러진다. 그러면 이제 ADO 2.5를 통해서 package를 부를수 있고, 결과를 ASP page에 보여줄 수 있다.
MYSQL에서는 limit로 이러한 것을 지원한다.
select * from table_01 where secret=0 order by hit desc limit 10;
MySQL TIP
1. Backup & Restore
1.1 Backup
1.2 Restore
1.2.1 Bulk Load
2. image upload
3. CREATE TABLE 구문 보기
4. 한번 쿼리로 여러 개 컬럼 UPDATE 하기
5. 설치 후 계정 정리하기
6. 주민번호로 연령별 통계 구하기
select (substring(substring(now(),1,4)-concat(ceiling(substring(ResRegNum2,1,1)/2)+18,substring(ResRegNum1,1,2)),1,1)*10) as age,count(ID) from member group by age;
1. 단원은 데이터베이스 마이그레이션의 핵심 사항만을 정리한 것이다.
기초적인 문법은 생략을 한다.
1.1 Backup
1.1.1 탭으로 분리된 데이터 만들기(Export with tab separated data)
mysql> select * into outfile '/tmp/babo.txt' from info_dong;
데이터베이스에서 자료만 뽑아서 사용할 때가 많다. 특히 마이그레이션할 때는 필수적이라고도 할 수 있다. 위의 방법은 간단하게 탭으로 분리된 데이터를 만드는 구문이다.
이렇게 하면 필요한 컬럼만 뽑아서 탭으로 분리된 데이터로만 저장이 된다. Mysqldump와 같이 테이블 스키마가 포함되어 있는 스크립트를 전부 저장할 필요도 없이 데이터만 저장하여 다른 DB에서 이용할 수 있게 한다.
1.2 Restore
1.2.1 Bulk Load
구문
LOAD DATA INFILE ‘파일명’
[REPLACE | IGNORE]
INTO TALE tbl_name
[FIELDS
[TERMINATED BY ‘\t’] 또는 [TERMINATED BY ‘,’]
[ENCLOSED BY ‘”’];
]
1.2.2 Tab으로 분리된 데이터 올리기
Mysql>load data infile “c:/mysql/book1.txt” replace into table dept teminated by ‘\t’;
unique 컬럼에 중복된 열이 있을 때는 replace를 하면 덮어쓰고, ignore를 하면 무시된다.
1.2.3 comma(,)로 분리된 데이터 올리기
Mysql>load data infile “c:/mysql/book1.txt” into table dept teminated by ‘,’;
1.2.4 기타 문장부호가 들어간 데이터 올리기
“강민주”,”3”
“강병기”,”34”
“이지연”,”31”
과 같은 형식으로 되어 있다면
Mysql> load data infile “c:/mysql/book1.txt” into table dept
FIELDS teminated by ‘,’ ENCLOSED BY’”’;
1.2.5 필드인 맨 처음 라인을 제외하고 입력할 때
Mysql> load data infile “c:/mysql/book1.txt” into table dept
FIELDS teminated by ‘,’ ENCLOSED BY’”’;
IGNORE 1 LINES;
2. 그림 올리기
CREATE TABLE images (
id NOT NULL auto_increment primary key,
image blob,
description varchar(200)
);
02.jpg란 파일을 올려보자 ….
Mysql>INSERT INTO TABLE(image,description) VALUES(LOAD_FILE(‘02.jpg’), “2번”);
이 파일을 수정하려면
Mysql>UPDATE images set image=LOAD_FILE(“/tmp/picture”) where id=1;
3. CREATE TABLE 구문 보기
info_device란 테이블이 존재하면
Mysql>show create table check\G
4. 한번 쿼리로 여러 개 컬럼 UPDATE 하기
dept란 테이블이 존재한다면
Mysql>desc dept;
Field Type Null Key Default Extra
--------- ----------- ------ ------ ------- ------
dept_no int(11) PRI 0
dept_name varchar(10)
zipcode varchar(7)
address varchar(30)
local_no char(3)
tel1 int(11) 0
tel2 varchar(4)
Mysql>select * from dept;
dept_no dept_name zipcode address local_no tel1 tel2
------- --------- ------- ----------------------- -------- ------ ------
10 관리부 135-240 123-45번지 우리빌딩 3층 02 504 5185
20 전산부 135-240 123-45번지 우리빌딩 4층 02 576 0186
30 영업부 135-240 123-45번지 우리빌딩 1층 02 678 1234
40 생산부 150-040 17가 12번지 (주)우리 02 553 4980
20번의 전산부를 15번으로 수정하고 부서명, 우편번호도 수정을 해준다.
잘못된 결과를 되돌림하기 위해 트랜잭션을 걸어준다.
Mysql>start transaction;
Mysql>update dept set
dept_no =’15’,
dept_name=’전산실’,
zipcode=’145-150’
where dept_no=20;
Mysql>select * from dept;
dept_no dept_name zipcode address local_no tel1 tel2
------- --------- ------- ----------------------- -------- ------ ------
10 관리부 135-240 123-45번지 우리빌딩 3층 02 504 5185
15 전산부 145-150 123-45번지 우리빌딩 4층 02 576 0186
30 영업부 135-240 123-45번지 우리빌딩 1층 02 678 1234
40 생산부 150-040 17가 12번지 (주)우리 02 553 4980
이상이 없으면 commit; 하고 이상이 있으면 rollback;을 해준다.
5. 설치 후 계정 정리하기
Mysql>use Mysql;
Mysql>show tables;
area
columns_priv
db
func
host
tables_priv
user
계정 정보는 user 안에 들어가 있다.
Database changed
Mysql>update user set password=password(‘pppp’) where user=’root’;
Mysql>delete from user where user=’ ‘;
Mysql>delete from user where user=’‘;
Mysql>flush privileges;
6. 업그레이드
Mysql은 비교적 잦은 판올림이 되어 종종 업데이터 해주어야 하는 경우가 있다. 우선
ⓐ mysql/data 디렉토리를 다른 곳에 통째로 복사해 둔다.
ⓑ 실행 창에서 –-remove 옵션을 사용해 서버를 삭제한다. 검은 화면이 보였다가 사라진다.
ⓒ [제어판]-[프로그램 추가/삭제]에서 “MySQL server”를 선택하고 제거한다.
ⓓ 윈도우를 재부팅한다.
설치 디렉토리인 c:\mysql 디렉토리를 삭제하고 c:\WINNT\my.ini 도 삭제를 한다음 재부팅한다.
재설치
1. Mysql 설치
2. 설치 후 작업
3. 환경설정
아래의 winmysqladmin.exe를 실행한다.
my.ini Setup에서 language= 부분을 수정하고 왼쪽의 Save Medification을 클릭한다.
이 작업을 마치면 C:\Windows(또는 WINNT) my.ini가 생성된다.(LINUX에서는 my.conf) 또한 서비스에 가서 확인해 보면 Mysql이 시작됨으로 설정되어 있음을 알 수 있다.
패스워드를 가지지 않으면 권한이 제한되므로 이제는 계정에 접속을 해서 패스워드를 변경해 줄 일만 남았다.
4. 계정변경
c:\Inetpub\mysql\bin>mysql -uroot –p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.0-alpha-max-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use mysql # 데이터베이스 선택
Database changed
mysql> UPDATE user SET password=PASSWORD('alswn')
-> WHERE user='root';
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> flush privileges; # 환경갱신
Query OK, 0 rows affected (0.00 sec)
mysql> quit # 재접속을 위한 빠져나가기
Bye
c:\Inetpub\mysql\bin>mysql -uroot –p
Enter password: *****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.1.0-alpha-max-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use mysql
Database changed
mysql> show tables;
6 rows in set (0.00 sec)
mysql> SELECT host,user,password
-> FROM user
-> WHERE user='root';
+-----------+------+---------------+
| host . | user | password |
+-----------+------+----------------+
| localhost . | root | 4d6c7dd77f752921 |
| % ...| root | 4d6c7dd77f752921 |
+-----------+------+----------------+
2 rows in set (0.01 sec)
mysql>
6. 주민번호로 연령별 통계 구하기
SELECT (substring(substring(now(),1,4)-concat(ceiling(substring(ResRegNum2,1,1)/2) + 18,substring(ResRegNum1,1,2)),1,1)*10) AS age,count(ID)
FROM member
GROUP BY age;
ResRegNum1 주민번호 첫 6자리
ResRegNum2 주민번호 두 7자리
ceiling(substring(ResRegNum2,1,1)/2)+18
이 부분에서 먼저 주민번호 7자리 중에서 첫번째 1자리를 가져 옵니다. 이게 1900년에 태어났는지 그 이후에 태어 낫는지 알수 있는 키가 되기 때문이죠.그리고 그걸 2로 나눈 몫을 무조건 올려주면 정수가 나오는데 이거 에다가 18 을 더해 주면 몇년도에 태어 났는지 알수가 있게 되는겁니다..한번 자기 주민번호 가지고 계산 해보시길...
substring(ResRegNum1,1,2)
이걸로 이제 나머지 년도를 구해서 concat로 더해 주면 정말 그 사람이 태어난 년도가 나오게 되겠죠...여기까진 태어난 년도 계산이고...
substring(now(),1,4)
이건 지금 년도 얻어오고 아까 그년도를 뺀다음에 다시 앞의 글자 하나 가져 와서 10을 곱하면 비로소 연령대가 나오게 되는겁니다
1.1 Backup
1.2 Restore
1.2.1 Bulk Load
2. image upload
3. CREATE TABLE 구문 보기
4. 한번 쿼리로 여러 개 컬럼 UPDATE 하기
5. 설치 후 계정 정리하기
6. 주민번호로 연령별 통계 구하기
select (substring(substring(now(),1,4)-concat(ceiling(substring(ResRegNum2,1,1)/2)+18,substring(ResRegNum1,1,2)),1,1)*10) as age,count(ID) from member group by age;
1. 단원은 데이터베이스 마이그레이션의 핵심 사항만을 정리한 것이다.
기초적인 문법은 생략을 한다.
1.1 Backup
1.1.1 탭으로 분리된 데이터 만들기(Export with tab separated data)
mysql> select * into outfile '/tmp/babo.txt' from info_dong;
데이터베이스에서 자료만 뽑아서 사용할 때가 많다. 특히 마이그레이션할 때는 필수적이라고도 할 수 있다. 위의 방법은 간단하게 탭으로 분리된 데이터를 만드는 구문이다.
이렇게 하면 필요한 컬럼만 뽑아서 탭으로 분리된 데이터로만 저장이 된다. Mysqldump와 같이 테이블 스키마가 포함되어 있는 스크립트를 전부 저장할 필요도 없이 데이터만 저장하여 다른 DB에서 이용할 수 있게 한다.
1.2 Restore
1.2.1 Bulk Load
구문
LOAD DATA INFILE ‘파일명’
[REPLACE | IGNORE]
INTO TALE tbl_name
[FIELDS
[TERMINATED BY ‘\t’] 또는 [TERMINATED BY ‘,’]
[ENCLOSED BY ‘”’];
]
1.2.2 Tab으로 분리된 데이터 올리기
Mysql>load data infile “c:/mysql/book1.txt” replace into table dept teminated by ‘\t’;
unique 컬럼에 중복된 열이 있을 때는 replace를 하면 덮어쓰고, ignore를 하면 무시된다.
1.2.3 comma(,)로 분리된 데이터 올리기
Mysql>load data infile “c:/mysql/book1.txt” into table dept teminated by ‘,’;
1.2.4 기타 문장부호가 들어간 데이터 올리기
“강민주”,”3”
“강병기”,”34”
“이지연”,”31”
과 같은 형식으로 되어 있다면
Mysql> load data infile “c:/mysql/book1.txt” into table dept
FIELDS teminated by ‘,’ ENCLOSED BY’”’;
1.2.5 필드인 맨 처음 라인을 제외하고 입력할 때
Mysql> load data infile “c:/mysql/book1.txt” into table dept
FIELDS teminated by ‘,’ ENCLOSED BY’”’;
IGNORE 1 LINES;
2. 그림 올리기
CREATE TABLE images (
id NOT NULL auto_increment primary key,
image blob,
description varchar(200)
);
02.jpg란 파일을 올려보자 ….
Mysql>INSERT INTO TABLE(image,description) VALUES(LOAD_FILE(‘02.jpg’), “2번”);
이 파일을 수정하려면
Mysql>UPDATE images set image=LOAD_FILE(“/tmp/picture”) where id=1;
3. CREATE TABLE 구문 보기
info_device란 테이블이 존재하면
Mysql>show create table check\G
4. 한번 쿼리로 여러 개 컬럼 UPDATE 하기
dept란 테이블이 존재한다면
Mysql>desc dept;
Field Type Null Key Default Extra
--------- ----------- ------ ------ ------- ------
dept_no int(11) PRI 0
dept_name varchar(10)
zipcode varchar(7)
address varchar(30)
local_no char(3)
tel1 int(11) 0
tel2 varchar(4)
Mysql>select * from dept;
dept_no dept_name zipcode address local_no tel1 tel2
------- --------- ------- ----------------------- -------- ------ ------
10 관리부 135-240 123-45번지 우리빌딩 3층 02 504 5185
20 전산부 135-240 123-45번지 우리빌딩 4층 02 576 0186
30 영업부 135-240 123-45번지 우리빌딩 1층 02 678 1234
40 생산부 150-040 17가 12번지 (주)우리 02 553 4980
20번의 전산부를 15번으로 수정하고 부서명, 우편번호도 수정을 해준다.
잘못된 결과를 되돌림하기 위해 트랜잭션을 걸어준다.
Mysql>start transaction;
Mysql>update dept set
dept_no =’15’,
dept_name=’전산실’,
zipcode=’145-150’
where dept_no=20;
Mysql>select * from dept;
dept_no dept_name zipcode address local_no tel1 tel2
------- --------- ------- ----------------------- -------- ------ ------
10 관리부 135-240 123-45번지 우리빌딩 3층 02 504 5185
15 전산부 145-150 123-45번지 우리빌딩 4층 02 576 0186
30 영업부 135-240 123-45번지 우리빌딩 1층 02 678 1234
40 생산부 150-040 17가 12번지 (주)우리 02 553 4980
이상이 없으면 commit; 하고 이상이 있으면 rollback;을 해준다.
5. 설치 후 계정 정리하기
Mysql>use Mysql;
Mysql>show tables;
area
columns_priv
db
func
host
tables_priv
user
계정 정보는 user 안에 들어가 있다.
Database changed
Mysql>update user set password=password(‘pppp’) where user=’root’;
Mysql>delete from user where user=’ ‘;
Mysql>delete from user where user=’‘;
Mysql>flush privileges;
6. 업그레이드
Mysql은 비교적 잦은 판올림이 되어 종종 업데이터 해주어야 하는 경우가 있다. 우선
ⓐ mysql/data 디렉토리를 다른 곳에 통째로 복사해 둔다.
ⓑ 실행 창에서 –-remove 옵션을 사용해 서버를 삭제한다. 검은 화면이 보였다가 사라진다.
ⓒ [제어판]-[프로그램 추가/삭제]에서 “MySQL server”를 선택하고 제거한다.
ⓓ 윈도우를 재부팅한다.
설치 디렉토리인 c:\mysql 디렉토리를 삭제하고 c:\WINNT\my.ini 도 삭제를 한다음 재부팅한다.
재설치
1. Mysql 설치
2. 설치 후 작업
3. 환경설정
아래의 winmysqladmin.exe를 실행한다.
my.ini Setup에서 language= 부분을 수정하고 왼쪽의 Save Medification을 클릭한다.
이 작업을 마치면 C:\Windows(또는 WINNT) my.ini가 생성된다.(LINUX에서는 my.conf) 또한 서비스에 가서 확인해 보면 Mysql이 시작됨으로 설정되어 있음을 알 수 있다.
패스워드를 가지지 않으면 권한이 제한되므로 이제는 계정에 접속을 해서 패스워드를 변경해 줄 일만 남았다.
4. 계정변경
c:\Inetpub\mysql\bin>mysql -uroot –p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.0-alpha-max-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use mysql # 데이터베이스 선택
Database changed
mysql> UPDATE user SET password=PASSWORD('alswn')
-> WHERE user='root';
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> flush privileges; # 환경갱신
Query OK, 0 rows affected (0.00 sec)
mysql> quit # 재접속을 위한 빠져나가기
Bye
c:\Inetpub\mysql\bin>mysql -uroot –p
Enter password: *****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.1.0-alpha-max-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use mysql
Database changed
mysql> show tables;
6 rows in set (0.00 sec)
mysql> SELECT host,user,password
-> FROM user
-> WHERE user='root';
+-----------+------+---------------+
| host . | user | password |
+-----------+------+----------------+
| localhost . | root | 4d6c7dd77f752921 |
| % ...| root | 4d6c7dd77f752921 |
+-----------+------+----------------+
2 rows in set (0.01 sec)
mysql>
6. 주민번호로 연령별 통계 구하기
SELECT (substring(substring(now(),1,4)-concat(ceiling(substring(ResRegNum2,1,1)/2) + 18,substring(ResRegNum1,1,2)),1,1)*10) AS age,count(ID)
FROM member
GROUP BY age;
ResRegNum1 주민번호 첫 6자리
ResRegNum2 주민번호 두 7자리
ceiling(substring(ResRegNum2,1,1)/2)+18
이 부분에서 먼저 주민번호 7자리 중에서 첫번째 1자리를 가져 옵니다. 이게 1900년에 태어났는지 그 이후에 태어 낫는지 알수 있는 키가 되기 때문이죠.그리고 그걸 2로 나눈 몫을 무조건 올려주면 정수가 나오는데 이거 에다가 18 을 더해 주면 몇년도에 태어 났는지 알수가 있게 되는겁니다..한번 자기 주민번호 가지고 계산 해보시길...
substring(ResRegNum1,1,2)
이걸로 이제 나머지 년도를 구해서 concat로 더해 주면 정말 그 사람이 태어난 년도가 나오게 되겠죠...여기까진 태어난 년도 계산이고...
substring(now(),1,4)
이건 지금 년도 얻어오고 아까 그년도를 뺀다음에 다시 앞의 글자 하나 가져 와서 10을 곱하면 비로소 연령대가 나오게 되는겁니다
ADO 객체 속성과 메소드
출처 : http://www.dragoneye.co.kr/lecture/asp/ado2.asp
|
ADO객체활용에 들어가기 전에 ADO객체에 대해 전반적인 것을 살펴보자. |
| Connection 객체 |
|
1. 데이타소스에 연결하기 위해 사용하지만 연결로 끝나는 것이 아니라 SQL명령을 실행한다. Select문일 경우 그 결과로 레코드셋을 가져올 수 있다 2. 서로다른 SQL문을 여러번 실행할 경우 한번 생성된 Connection객체를 넘겨주는 것이 더 효율적이다. *입력(Insert쿼리), 수정(Update쿼리), 삭제(Delete쿼리), 조회(Select쿼리)에 사용. |
| ||
| 1) 연결및 실행 | ||
| ConnectionString | 데이타소스연결시 필요한 정보 | |
| Open | 데이타소스에 연결을 한다 | |
| Close | 기존 열린 연결을 종료한다 | |
| Execute | SQL문,저장프로시저를 실행한다 | |
| 2) 트랜잭션 | ||
| Attributes | 기존 트랜잭션의 종료후 새 트랜잭션의 시작여부 제어 메서드 | |
| BeginTrans | 새 트랜잭션 시작 | |
| CommitTrans | 수정내용을 저장하고 트랜잭션 종료 | |
| RollbackTrans | 수정내용을 취소하고 트랜잭션 종료 | |
| 3) 기타 | ||
| CommandTimeout | 명령을 시도한 다음 지연시간(초단위) 기본값은 30초 | |
| ConnectionTimeout | 연결을 시도한 다음 지연시간(초단위) 기본값은 30초 | |
| Mode | 읽기 또는 읽기-쓰기 등 연결의 허용 범위 | |
| Provider | 연결의 제공자 | |
| DefaultDatabase | 접속하려는 D/B가 여럿일 경우 기본으로 하는 D/B 지정 | |
| Version | 사용하고 있는 ADO의 버전 | |
| Command 객체 |
| 이 객체만 이용하여 연결,명령을 수행할 수 있다 .기초에서는 생략한다. 나중에 중급수준에서 저장프로시저를 사용할 때 한번 다뤄보기 바란다. 저장프로시저의 사용에 편리한 parameter 속성이 있다. |
| ||
| 1) 속성(Properties) | ||
| ActiveConnection | Command객체에서 사용하는 Connection객체 | |
| CommandText | 실행될 명령어를 나타내는 택스트(SQL/저장프로시저) | |
| CommandTimeout | 실행될 명령어를 위한 대기시간(초단위) 기본값은 30초 | |
| CommandType | CommandText에 넣은 명령타입 지정 | |
| CommandTimeout | 실행될 명령어를 위한 대기시간(초단위) 기본값은 30초 | |
| Prepared | 명령어를 실행하기 전에 준비하거나 컴파일 여부를 지정 | |
| 2) 메소드(Method) | ||
| Execute | CommandText에 넣은 명령을 실행 | |
| CreateParameters | Parameters컬렉션에 새로운 Parameters객체 생성 | |
| Recordset 객체 |
|
1. ASP에서 데이타베이스를 다루는 대부분을 차지하는 것이 Recordset객체이며,이는 SQL문 실행결과 생성된 레코드셋을 다루는 객체이다. 2. 레코드셋은 table형태이며 실제 data를 다룰 때는 레코드 단위로 처리하게 되며, |
| ||
| 1) 열기.닫기 메소드 | ||
| Open | 레코드셋 열기 | |
| Close | 레코드셋 닫기 | |
| 2) 생성.삭제,수정 | ||
| AddNew | 기존 트랜잭션의 종료후 새 트랜잭션의 시작여부 제어 메서드 | |
| Delete | 새 트랜잭션 시작 | |
| Update | 수정내용을 저장하고 트랜잭션 종료 | |
| 3) 이동 메소드 | ||
| Move | 특정레코드로 이동 | |
| MoveFirst | 처음 레코드로 이동 | |
| MoveLast | 마지막 레코드로 이동 | |
| MovePrevious | 이전 레코드로 이동 | |
| MoveNext | 다음 레코드로 이동 | |
| 3) 프로퍼티 | ||
| AbsolutePage | 현재 레코드가 있는 페이지 | |
| AbsolutePosition | 현재 레코드의 순서 위치 | |
| ActiveConnection | 현재 레코드셋이 사용하고 잇는 Connection객체 | |
| BOF | 레코드셋의 시작, 현재 레코드위치가 이 위치이면 True | |
| EOF | 레코드셋의 끝, 현재 레코드위치가 이 위치이면 True | |
| CursorType | 레코드셋을 열 때 사용할 커서의 종류 | |
| LockType | 편집중 Lock타입을 지정 | |
| PageSize | 한페이지에 나타낼 레코드의 수를 지정 | |
| PageCount | 레코드셋에 있는 데이타의 페이지 수 | |
| RecordCount | 레코드셋에 있는 레코드의 수 | |
| * 먼저 Connection객체와 recordset객체를 익히도록... * 레코드셋(Recordset)이란? 레코드셋은 데이타 저장소로 물리적 DB의 table에 있는 자료를 직접 엑세스하는게 아니라 쿼리의 결과로 나온 메모리상의 가상테이블이라고 할 수 있는 이 레코드셋에 접근하여 자료를 다루는 것이다. 당연히 이 레코드셋에 담겨있지 않으면 접근할 수 없다. |
CursorType 및 상수
출처 : http://www.dragoneye.co.kr/lecture/asp/ado3.asp
|
앞에 나온 속성 중에서 CursorType 및 상수에 대해 알아본다 |
|
앞에서 레코드셋 즉, 데이타를 다루는 단위가 레코드라고 했다. 그러면 무엇으로 현재레코드를 알 수 있을까? 바로 레코드포인트라는 것이 있어서 항상 현재레코드를 가리키도록 되어 있다. 우리는 이 레코드포인트를 이동하면서 필요한 데이타 작업을 할 수 있다. 이때 레코드포인트를 이동시켜 주는 것이 Cursor이다. 윈도우나 워드에서 화면/메뉴를 이동할 때 쓰는 Cursor와 비슷하다. 이는 레코드셋을 열기 전에 지정되어야 하며 4가지가 있다. 지정하지 않으면 ForwardOnly(0)이 지정된다. |
| 커서타입 상수 | 값 | 내 용 |
| adOpenForwardOnly | 0 | 디폴트 타입,읽기만 가능,가장 빠르다,전방향이동만 가능 순서대로 한번씩 참조할 경우 편리하다 |
| adOpenKeyset | 1 | 읽기/쓰기 가능,양방향 이동 가능, 다른 사용자 추가,삭제는 볼 수 없다 |
| adOpenDynamic | 2 | 읽기/쓰기 가능,모든 이동 가능,다른 사용자 추가,삭제,수정을 볼 수 있다,성능상 많은 부담이 있다 |
| adOpenStatic | 3 | 읽기/쓰기 가능,양방향 이동 가능, 본인외 다른 레코드셋 추가,삭제는 볼 수 없다 |
|
| 1. 상수값을 사용하는 방법 | |
|
Set Rs=Server.CreateObject("ADODB.Recordset") 또는 Set Rs=Server.CreateObject("ADODB.Recordset") | |
| 2. 상수를 사용하는 방법 ADO상수를 사용하려면 (즉,adOpenForwardOnly와 같이) 상수를 정의해둔 상수화일을 Server Side Include로 adovbs.inc화일을 포함하여야 한다 | |
|
<!-- #include file="폴더\adovbs.inc" --> 또는 <!-- #include file="common\adovbs.inc" --> |
| *만약 레코드셋 객체를 통해 수정/삭제/등록을 하고자 하면 반드시 레코드셋에 대한 락 수준을 지정을 해야한다.(ADO연습 데이타 등록 부분 - 레코드셋 객체를 통한 데이타 등록 참고) |
| 락타입 상수 | 값 | 내 용 |
| adLockReadOnly | 1 | 읽기전용(수정불가) |
| adLockPessimistic | 2 | 레코드단위, 편집 시작 순간부터 |
| adLockOptimistic | 3 | 레코드 단위, update 메서드 순간 |
| adLockBatchOptimistic | 4 | 배치갱신모드 요구 |
|
ADO에서 사용하는 상수는 이외에도 많이 있다. 이 상수값들은 파일하나로 정의되었으며 위치는 c:\program files\common files\system\ado아래에 있다. 사용을 위해 웹사이트(가상디렉토리)내에 폴더를 만들어 복사해서 두고 쓰면 된다. |
ADO 사용을 위한 준비 (ODBC/OLEDB)
출처 : http://www.dragoneye.co.kr/lecture/asp/ado4.asp
| ADO가 데이타베이스를 다루는 객체라는 것은 앞에서 설명드렸으니까 이 데이타베이스를 다루려면 데이타소스에 연결해야한다. 연결방법으로는 ODBC와 OLEDB 중에 선택해야 한다. ODBC는 다시 DSN 설정과 비설정으로 나눠지므로 선택의 폭은 총 3가지인데 나머지는 대략 살펴보고 OLEDB는 자세히 살펴보기 바란다. 연결에 필요한 정보를 살펴보면 1) 어떤 연결단자를 사용할 것인지(앞에서 ODBC와 OLEDB가 있다고 했음) 2) 어떤 종류의 데이타베이스이며, 3) 위치가 어디인지, 4) ID와 비밀번호는 무엇인지가 필요하다 결국 사용자가 asp문서를 클릭한다는 것은 asp문서속에 프로그래머가 준비해준 디비에 들어가는 정보(열쇠)를 갖고 준비해준 쿼리를 실행시켜 asp문서의 결과를 보는 셈이다. |
| (1) 연결단자로 ODBC를 사용하면서 DSN을 설정하여 연결하는 방법 |
| 제어판->(win2000경우 관리도구)-> 데이타원본 -> 시스템DSN 에서 [추가]를 선택하여 DSN을 추가한다 제대로 설정이 되었다면 이 DSN(Data Source Name)이 데이타 베이스의 종류,이름, 위치 정보를 가지게 된다. 이렇게 DSN이 설정되면 앞으로 이 Name(DSN)을 사용해서 데이타베이스에 연결하게 된다 *물론 Access의 경우는 ID와 비밀번호가 없다. *이때 준 Name을 잘 기억해야겠다 . 소스중에 적어 주어야 하므로... Connection,Recordset객체는 뒤에서 다룰 것이므로 연결문자만 참조하기 바란다. |
*데이타베이스가 TestDB이고, DSN설정에서 별칭을 Test라고 했다면
1) Connection 객체에서
2) Recordset 객체에서
*SQL Server경우는 User ID와 Password의 값을 넣어준다 *물론 Access의 경우는 DSN뿐이므로 "Test"로 단축가능하다. 다른 데이타베이스를 사용할 경우 및 코드를 일목요연하게 하려면 변수를 사용할 수 있다. 1)Connection 객체에서 (변수에 두고 사용)
2)Recordset 객체에서 (변수에 두고 사용)
|
| (2) 연결단자로 ODBC를 사용하면서 DSN을 설정하지 않고 연결하는 방법 |
*DSN에 설정에 사용했던 정보들을 연결문자열에 그대로 사용한다
1) Connection 객체에서 (변수에 두고 사용)
2) Recordset 객체에서 (변수에 두고 사용)
*위에서 ODBC드라이브이름으로 Access Driver를 ,Access D/B의 경로와 이름을 지정해 주었다
1) Connection 객체에서 (변수에 두고 사용)
2) Recordset 객체에서 (변수에 두고 사용)
* M/S SQL Server의 경우 SQL 서버이름(여기서는 SON)과 D/B이름(여기서는 TestDB)과 User ID(여기서는 표준사용자 sa)와 Password를 지정하였다 |
| (3) 연결단자로 OLEDB를 사용하는 경우- 권장합니다 |
* OLEDB는 연결문자열을 사용한다
1) Connection 객체에서
2) Recordset 객체에서
*위에서 OLEDB제공자로 Microsoft.Jet.OLEDB.4.0을 지정하였고 D/B경로 및 이름 을 지정하였다
1) Connection 객체에서 (변수에 두고 사용)
2) Recordset 객체에서 (변수에 두고 사용)
*위에서 OLEDB제공자로 SQLOLEDB를 지정하였고 SQL서버이름(SON)과 D/B이름 및 USer ID와 Password를 지정하였다 |
| ASP에서 주로 사용하는 데이타베이스인 Access 및 SQL Server는 ODBC 및OLEDB 모두 이용할 수 있다. ODBC를 익힌 다음 실제 작업에서는 OLEDB를 사용하면 된다. |
OLEDB로 연결하는 방법
출처 : http://www.dragoneye.co.kr/lecture/DBCON/sql_oledb.asp
Data Source : DB명 / Initial Catalog : Table명
localhost(또는 DB서버의 Netbios명) 또는 원격지 DB 211.233.xxx.yyy 에 연결을 하고자 한다. localhost는 EC001이라는 Netbios명을 가지고 있다고 하면
1. 소스중에 넣기
<%
Set DB=Server.CreateObject("Adodb.Connection")
DB.open "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=dbName;User ID=kim;Password=1234"
%>
2. 변수를 사용하기
<%
Connstring= "Provider=SQLOLEDB;Data Source=EC001;Initial Catalog=Sales;User ID=kim;Password=1234"
Set DB=Server.CreateObject("Adodb.Connection")
DB.open Connstring
%>
3. 인클루드 사용 (권장하는 방법입니다)
♡ 변수를 저장할 페이지 : INC.ASP 파일(임의로 정함)
<%
Connstring= "Provider=SQLOLEDB;Data Source=EC001;Initial Catalog=Sales;User ID=kim;Password=1234"
%>
♡ 사용할 페이지
<!-- #include file="inc.asp" -->
<%
Set DB=Server.CreateObject("Adodb.Connection")
DB.open Connstring
%>
원격지 서버에서 다른 port를 사용할 때
DB.Open "Provider=sqloledb;" & _
"Network Library=DBMSSOCN;" & _
"Data Source=xxx.xxx.xxx.xxx,1433;" & _
"Initial Catalog=myDatabaseName;" & _
"User ID=myUsername;" & _
"Password=myPassword"
상기에서 1433번 외에 다른 port가 지정될 것이다. 그것을 수정해 주면 된다.
SQLXMLOLEDB을 이용한 OLE DB Provider
Microsoft SQLXML 기능을 ADO를 통해 구현하기 위해 만들었다. SQLXMLOLEDB provider는 rowset provider가 아니며, ADO의 write to an output stream"모드에서만 명령을 실행한다.
DB.Open "Provider=SQLXMLOLEDB.3.0;" & _
"Data Provider=SQLOLEDB;" & _
"Data Source=mySqlServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"User Id=myUserName;" & _
"Password=myUserPassword"
용량이 커진 LDF 삭제
DB의 LDF가 적당한 크기일 때는
DBCC SHRINKDATABSE
DBCC SHRINKfile
을 통해 줄여줄 수 있지만, 크기가 50GB를 넘어가면 아예 삭제를 해주는 것이 좋다.
// DB 이동
use TGM <--- DB명
// 로그파일 삭제
BACKUP LOG TGM WITH NO_LOG
파일 자체는 줄어들지 않았는데, 아래의 명령을 주면 파일이 1MB로 줄어든다.
// 로그파일 축소
DBCC SHRINKFILE ('Toursoft_Log', truncateonly)
1MB로 크기가 줄어든 것을 확인할 수 있다.
로그파일은 EM에서 확인해 보면 된다.
// 캐쉬삭제
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
캐쉬를 정리해 주자.
피드 구독하기:
글 (Atom)