SQL server 2005(5) - 데이터 검색2

use master

create database mydb99
use mydb99
go
create table mytable00
(
  location varchar(20) null,
  userid  varchar(10)  not null,
  username  varchar(10)  not null,
  jumin varchar(15) not null,
  age  int  null,
  sex  varchar(1),
  zip  varchar(10) not null,
  addr1  varchar(50) null,
  addr2  varchar(50) null,
  addr3  varchar(50) null,
  tel  varchar(15) null,
  job  varchar(20) null,
  schol  varchar(15)  null,
  point  int  null,
  regdate  datetime  not null
)

 

sp_help mytable00
select*from mytable00

 

텍스트 파일의 데이터를 sql server의 테이블로 추가하는 예

bulk insert mydb99..mytable00
from 'c:\TextDB.txt'
with
(
       fieldterminator=',',
       rowterminator = '\n'
)

select*from mytable00

 

BCP "testDB.dbo.고객정보" out "C:\연습.txt" -c -q -Usa -P1  -> db에 있는 파일을 txt파일로
BCP "testDB.dbo.고객정보" in "C:\연습.txt" -c -q -Usa -P1  -< txt파일을 db로...
dos에서 실행

BCP "testDB.dbo.고객정보" out "C:\연습.txt" -c -q -Usa -P1

select*into mytable01 from testdb..고객정보
where 0=1
select*from mytable01

BCP "testDB.dbo.고객정보" in "C:\연습.txt" -c -q -Usa -P1

sql 쿼리 분석기에서 다음을 실행하여 파일을 삭제한다.

exec master..xp_cmdshell'c:\연습.txt'
exec master..xp_cmdshell'dir c:\'

sp_help

sp_helpdb

use mydb99
go
select*from mytable00 where userid='ip6440'

update mytable00 set username='김예린'
where userid='ip6440'

select*from mytable00 where userid='ip6440'

update mytable00 set addr1='인천광역시', addr2='동구'
where userid='ip6440'
select*from mytable00 where userid='ip6440'

use testdb
go
select*into mydb99..mytable03 from 회사정보

use mydb99
go
sp_help mytable03

select*from mytable03

 

select*from mytable03 where dept like '경영%'

update mytable03 set pay=pay*1.12
where dept like '경영%'
select*from mytable03 where dept like '경영%'

use testdb
go
select*into mydb99..mytable04 from 고객정보
use mydb99
select*from mytable04   - 고객정보
select*from mytable03   - 회사정보

select*from mytable04 where addr1 like '제주%' AND age>=40
select*from mytable03 where userid in(select userid from mytable04 where addr1='제주도' AND age>=40)

update mytable03 set pay=pay+50000
where userid in(select userid from mytable04 where addr1='제주도' AND age>=40)
select*from mytable03 where userid in(select userid from mytable04 where addr1='제주도' AND age>=40)

select*
From OpenDataSource('Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\ExcelDB.xls";User ID=Admin;password=;
Extended properties=Excel 5.0')...엑셀고객

update OpenDataSource('Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\ExcelDB.xls";User ID=Admin;password=;
Extended properties=Excel 5.0')...엑셀고객
set username='김예린'
where userid='ip6440'

delete mytable03 where userid='ip6440'
select*from mytable03

delete mytable03 where userid in(select userid from mytable04 where addr1='제주도' AND age>=40)
select*from mytable03 where userid in(select userid from mytable04 where addr1='제주도' AND age>=40)

테이블의 모든 행을 삭제하는 예
delete mytable01   --> log파일에 기록하며 삭제(1,2번이 지워졌으면 다음에 기록하면 3,4행 부터 입력됨) 복원O
or
truncate table mytable04    ----> 지울때 log파일을 만들지 않음 복원X

 

DB안에 생성된 TABLE을 검색하는 명령어

SP_HELP
보다 상세하게...

SELECT*FROM INFORMATION_SCHEMA.COLUMNS