SQL sever数据导入导出实验

1.创建数据库TCP-H1右键“数据库”点击“新建数据库”即可2用sql语言创建此处以创建数据库DB_test为例代码如下use master; go --检查在当前服务器系统中的所有数据里面是否有该数据库 IF EXISTS(SELECT * FROM sysdatabases WHERE NAMEDB_test) --如果有删除该数据库 DROP DATABASE DB_test -- 创建该数据库 CREATE DATABASE DB_test ---DB_Yun 数据名称 ON PRIMARY ( NAMEDB_test, --主数据文件的逻辑名 FILENAMED:developsqldataDB_test.mdf, --主数据文件存储位置 SIZE16MB, --主数据文件的初始大小 FILEGROWTH8MB, --每次增容时增加的容量大小 MAXSIZEUNLIMITED --增量速度 unlimited无限制 ) LOG ON( NAMEDB_test.LDF, --日志文件 FILENAMED:developsqldataDB_test.ldf, --日志文件存储位置 SIZE16MB, --日志文件初始大小 FILEGROWTH8MB, --日志文件每次增加的容量大小 MAXSIZEUNLIMITED3创建成功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 FROMD: 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 FROMD: 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 FROMD: 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 FROMD: 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 FROMD: 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 FROMD: 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 FROMD: 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 FROMD: 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 12343导出成功