SQLsever数据导入导出实验

1.创建数据库TCP-H

（1）右键“数据库”，点击“新建数据库”即可

（2）用sql语言创建，此处以创建数据库DB_test为例，代码如下：

use master; go --检查在当前服务器系统中的所有数据里面是否有该数据库 IF EXISTS(SELECT * FROM sysdatabases WHERE NAME='DB_test') --如果有删除该数据库 DROP DATABASE DB_test -- 创建该数据库 CREATE DATABASE DB_test ---DB_Yun 数据名称 ON PRIMARY ( NAME='DB_test', --主数据文件的逻辑名 FILENAME='D:developsqldataDB_test.mdf', --主数据文件存储位置 SIZE=16MB, --主数据文件的初始大小 FILEGROWTH=8MB, --每次增容时增加的容量大小 MAXSIZE=UNLIMITED --增量速度 unlimited无限制 ) LOG ON( NAME='DB_test.LDF', --日志文件 FILENAME='D:developsqldataDB_test.ldf', --日志文件存储位置 SIZE=16MB, --日志文件初始大小 FILEGROWTH=8MB, --日志文件每次增加的容量大小 MAXSIZE=UNLIMITED

（3）创建成功

2.进行数据的导入

（1）下载csv文件

（2）进行导入，导入代码如下

use [TPC-H]; /*drop table PART;*/ CREATE TABLE PART (P_PARTKEY int primary key, P_NAME varchar(55), P_MFGR char(25), P_BRAND char(10), P_TYPE varchar(25), P_SIZE int, P_CONTAINER char(10), P_RETAILPRICE decimal, P_COMMENT varchar(23)) TRUNCATE TABLE PART; BULK INSERT PART FROM"D: part.csv" WITH (FIELDTERMINATOR=',', ROWTERMINATOR=' ') /*drop table REGION;*/ CREATE TABLE REGION (R_REGIONKEY INT PRIMARY KEY, R_NAME CHAR(25), R_COMMENT VARCHAR(152)) TRUNCATE TABLE REGION; BULK INSERT REGION FROM"D: egion.csv" WITH (FIELDTERMINATOR=',', ROWTERMINATOR=' ') /*drop table NATION*/ CREATE TABLE NATION (N_NATIONKEY INT PRIMARY KEY, N_NAME CHAR(25), N_REGIONKEY INT FOREIGN KEY REFERENCES REGION(R_REGIONKEY), N_COMMENT VARCHAR(152)) TRUNCATE TABLE NATION; BULK INSERT NATION FROM"D: ation.csv" WITH (FIELDTERMINATOR=',', ROWTERMINATOR=' ') /*drop table SUPPLIER*/ CREATE TABLE SUPPLIER (S_SUPPKEY int PRIMARY KEY, S_NAME char(25), S_ADDRESS varchar(40), S_NATIONKEY INT FOREIGN KEY REFERENCES NATION(N_NATIONKEY), S_PHONE CHAR(15), S_ACCTBAL DECIMAL, S_COMMENT VARCHAR(101)) TRUNCATE TABLE SUPPLIER; BULK INSERT SUPPLIER FROM"D: supplier.csv" WITH (FIELDTERMINATOR=',', ROWTERMINATOR=' ') /*drop table PARTSUPP*/ CREATE TABLE PARTSUPP (PS_PARTKEY INT FOREIGN KEY REFERENCES PART(P_PARTKEY), PS_SUPPKEY INT FOREIGN KEY REFERENCES SUPPLIER(S_SUPPKEY), PS_AVAILQTY INT, PS_SUPPLYCOST DECIMAL, PS_COMMENT VARCHAR(199), PRIMARY KEY(PS_PARTKEY,PS_SUPPKEY)) TRUNCATE TABLE PARTSUPP; BULK INSERT PARTSUPP FROM"D: partsupp.csv" WITH (FIELDTERMINATOR=',', ROWTERMINATOR=' ') /*drop table CUSTOMER*/ CREATE TABLE CUSTOMER (C_CUSTKEY INT PRIMARY KEY, C_NAME VARCHAR(25), C_ADDRESS VARCHAR(40), C_NATIONKEY INT FOREIGN KEY REFERENCES NATION(N_NATIONKEY), C_PHONE CHAR(15), C_ACCTBAL DECIMAL, C_MKTSEGMENT CHAR(10), C_COMMENT VARCHAR(117)) TRUNCATE TABLE CUSTOMER; BULK INSERT CUSTOMER FROM"D: customer(1).csv" WITH (FIELDTERMINATOR=',', ROWTERMINATOR=' ') /*drop table ORDERS*/ CREATE TABLE ORDERS (O_ORDERKEY INT PRIMARY KEY, O_CUSTKEY INT, O_ORDERSTATUS CHAR(1), O_TOTALPRICE DECIMAL, O_ORDERDATE DATE, O_ORDERPRIORITY CHAR(15), O_CLERK CHAR(15), O_SHIPPRIORITY INT, O_COMMENT VARCHAR(79), FOREIGN KEY(O_CUSTKEY) REFERENCES CUSTOMER(C_CUSTKEY)) TRUNCATE TABLE ORDERS; BULK INSERT ORDERS FROM"D: orders.csv" WITH (FIELDTERMINATOR=',', ROWTERMINATOR=' ') /*drop table LINEITEM*/ CREATE TABLE LINEITEM (L_ORDERKEY INT FOREIGN KEY REFERENCES ORDERS(O_ORDERKEY), L_PARTKEY INT FOREIGN KEY REFERENCES PART(P_PARTKEY), L_SUPPKEY INT FOREIGN KEY REFERENCES SUPPLIER(S_SUPPKEY), L_LINENUMBER INT, L_QUANTITY DECIMAL, L_EXTENDEDPRICE DECIMAL, L_DISCOUNT DECIMAL, L_TAX DECIMAL, L_RETURNFLAG CHAR(1), L_LINESTATUS CHAR(1), L_SHIPDATE DATE, L_COMMITDATE DATE, L_SHIPINSTRUCT CHAR(25), L_SHIPMODE CHAR(50), L_COMMENT VARCHAR(60), PRIMARY KEY(L_ORDERKEY,L_LINENUMBER)) TRUNCATE TABLE LINEITEM; BULK INSERT LINEITEM FROM"D: lineitem.csv" WITH (FIELDTERMINATOR=',', ROWTERMINATOR=' ')

（3）导入成功

3.进行数据的导出

（1）下载安装bcp工具

（2）使用导出语句

bcp [TPC-H].[dbo].[PART] out “D:学校d大二下数据库实验一数据文件 ile.csv” -c -t -T -S . -U sa -P 1234

（3）导出成功