Access 导入到 SQLServer

时间:2022-04-29 01:29:28

Access 导入到 SQLServer

 

 Access 导入到 SQLServer

 

 Access 导入到 SQLServer

 

 

1、关于数据库课程
->数据库概念:数据库的基本操作,基本T-SQL
->Ado.Net操作数据库:C#代码访问数据库的技术
->数据库高级内容:文件组,数据库权限系统,高级SQL应用,面试题目等

->课程共7天课,前两天T-SQL基础,后面三天Ado.Net 最后两天 数据库高级

2、数据库的概念
->数据库就是数据仓库。
->DBMS:数据库管理系统。SQLServer Oracle
3、数据库的特点
->安全性
->并发访问处理
->高效率
->海量数据存储
->数据完整性

4、数据库的基本组成
->数据库
->架构(表的分组)
->表
->列(Column字段,类型,长度,约束等)
->行(Row)
->主键:自动增长,GUID
->外键
5、数据库管理
->数据库安装
->数据库的实例名详解:区分安装在同一台机器上不同的MSSqlServer 服务的。

->混合身份验证:Windows身份验证、SQLServer身份验证
->启用SA账户(安装的时候可以直接设置启用)
->链接数据库:
->保证SQLServer服务器开启
->. 和 (local) 和 机器名一样,代表链接本机。
->ip地址:
->回环地址:127.0.0.1
->局域网ip地址,外网ip地址。

->SA

6、可视化创建数据库、表、主键
->创建数据库
->数据库文件默认位置:C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATA
(创建数据库最好放到此位置,避免权限问题。)
->表
->命名规范同类。
->列:
NChar,Nvarchar(32) N:Unicode,2个字节表示一个字符。
Char:一个字节表示一个字符。
->可能遇到的问题:“阻止保存要求重新创建表的更改”的问题,解决办法: 工具→选项→设计器→阻止表的更改

->主键:
7、数据库的字段类型详解
8、案例:创建School数据库

9、数据库的迁移方案
->分离 附加
->权限问题:
->启用Administrator账号
->把数据库文件放到默认的数据库文件存放目录。
->数据库文件兼容级别,设置成2005兼容

->脱机 附加

->备份 还原

->生成SQL脚本

10、SQL脚本
->结构化查询语言(Structured Query Language)
->DMLDDLDCL ->脚本:一般就是指比较简单的,不需要编译的可以直接解析执行的语言,常见的比如:SQL、JavaScript等

->T-SQL的特点
->跨DBMS,基本上所有的DBMS都对标准T-SQL兼容 ->大小写不敏感(仅仅是关键字不敏感,字符串数据是大小写敏感的)
->转义
->php
11、基本的DDL SQL脚本
->创建数据库(Create Database)
->创建表(包括主键、自动增长、是否可空)等
12、基本的DML SQL脚本
->简单的查询: Select 列名 From 表明 where 条件表达式
->插入数据: Insert Into 表名(列名) values(列值)
->删除数据:Delete
->修改数据:Update
13、案例:OA系统数据库设计
部门表:Department JQJB:警情级别
->部门的名字 DepName
->部门的老大 DepMaster
->部门的编号 主键:
->部门的描述
->删除标识
->上级部门的ID
select * from dep where ParentDepId =我的id
员工表、

内部员工消息表
->发送者
->发送的时间
->发送的内容
->接收
->接收时间
...
--------------------------------------------------------------
14、约束

15、最牛的注释:就是没有注释。

 

 

 

使用CREATE 语句创建索引

CREATE INDEX index_name ON table_name(column_name,column_name) include(score)

普通索引

CREATE UNIQUE INDEX index_name ON table_name (column_name) ;

非空索引

CREATE PRIMARY KEY INDEX index_name ON table_name (column_name) ;

主键索引
 
使用ALTER TABLE语句创建索引

alter table table_name add index index_name (column_list) ;
alter table table_name add unique (column_list) ;
alter table table_name add primary key (column_list) ;


删除索引

drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;

  

 

 

 

The execution was successful

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Warning)
Messages
Warning 0x80049304: Data Flow Task 1: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available. To resolve, run this package as an administrator, or on the system‘s console.
(SQL Server Import and Export Wizard)

- Prepare for Execute (Success)

- Pre-execute (Success)

- Executing (Success)

- Copying to [dbo].[MODEL_SPEC_VALUE] (Success)
15 rows transferred

Messages
Information 0x402090df: Data Flow Task 1: The final commit for the data insertion in "Destination 4 - Table1" has started.
(SQL Server Import and Export Wizard)

Information 0x402090e0: Data Flow Task 1: The final commit for the data insertion in "Destination 4 - Table1" has ended.
(SQL Server Import and Export Wizard)

Information 0x402090df: Data Flow Task 1: The final commit for the data insertion in "Destination 1 - PLANT_MODEL" has started.
(SQL Server Import and Export Wizard)

Information 0x402090df: Data Flow Task 1: The final commit for the data insertion in "Destination - MODEL_SPEC_VALUE" has started.
(SQL Server Import and Export Wizard)

Information 0x402090e0: Data Flow Task 1: The final commit for the data insertion in "Destination 1 - PLANT_MODEL" has ended.
(SQL Server Import and Export Wizard)

Information 0x402090e0: Data Flow Task 1: The final commit for the data insertion in "Destination - MODEL_SPEC_VALUE" has ended.
(SQL Server Import and Export Wizard)

- Copying to [dbo].[PLANT_MODEL] (Success)
16 rows transferred

- Copying to [dbo].[PRODC_CHECK_SEQ_RESULT] (Success)
26682 rows transferred

Messages
Information 0x402090df: Data Flow Task 1: The final commit for the data insertion in "Destination 2 - PRODC_CHECK_SEQ_RESULT" has started.
(SQL Server Import and Export Wizard)

Information 0x402090e0: Data Flow Task 1: The final commit for the data insertion in "Destination 2 - PRODC_CHECK_SEQ_RESULT" has ended.
(SQL Server Import and Export Wizard)

- Copying to [dbo].[PRODC_SN_CHECK_RESULT] (Success)
26614 rows transferred

Messages
Information 0x402090df: Data Flow Task 1: The final commit for the data insertion in "Destination 3 - PRODC_SN_CHECK_RESULT" has started.
(SQL Server Import and Export Wizard)

Information 0x402090e0: Data Flow Task 1: The final commit for the data insertion in "Destination 3 - PRODC_SN_CHECK_RESULT" has ended.
(SQL Server Import and Export Wizard)

- Copying to [dbo].[Table1] (Success)
0 rows transferred

- Copying to [dbo].[TB_AREA_SWIM] (Success)
3 rows transferred

- Copying to [dbo].[TB_LOGIN_ADMIN] (Success)
3 rows transferred

- Copying to [dbo].[TB_MMT_COUNTER_MAP] (Success)
7 rows transferred

- Copying to [dbo].[TB_SWIM_RECORD] (Success)
2 rows transferred

- Copying to [dbo].[TB_SWIMING_USER] (Success)
1 rows transferred

Messages
Information 0x402090df: Data Flow Task 2: The final commit for the data insertion in "Destination 7 - TB_MMT_COUNTER_MAP" has started.
(SQL Server Import and Export Wizard)

Information 0x402090df: Data Flow Task 2: The final commit for the data insertion in "Destination 5 - TB_AREA_SWIM" has started.
(SQL Server Import and Export Wizard)

Information 0x402090df: Data Flow Task 2: The final commit for the data insertion in "Destination 9 - TB_SWIMING_USER" has started.
(SQL Server Import and Export Wizard)

Information 0x402090df: Data Flow Task 2: The final commit for the data insertion in "Destination 6 - TB_LOGIN_ADMIN" has started.
(SQL Server Import and Export Wizard)

Information 0x402090df: Data Flow Task 2: The final commit for the data insertion in "Destination 8 - TB_SWIM_RECORD" has started.
(SQL Server Import and Export Wizard)

Information 0x402090e0: Data Flow Task 2: The final commit for the data insertion in "Destination 5 - TB_AREA_SWIM" has ended.
(SQL Server Import and Export Wizard)

Information 0x402090e0: Data Flow Task 2: The final commit for the data insertion in "Destination 7 - TB_MMT_COUNTER_MAP" has ended.
(SQL Server Import and Export Wizard)

Information 0x402090e0: Data Flow Task 2: The final commit for the data insertion in "Destination 9 - TB_SWIMING_USER" has ended.
(SQL Server Import and Export Wizard)

Information 0x402090e0: Data Flow Task 2: The final commit for the data insertion in "Destination 8 - TB_SWIM_RECORD" has ended.
(SQL Server Import and Export Wizard)

Information 0x402090e0: Data Flow Task 2: The final commit for the data insertion in "Destination 6 - TB_LOGIN_ADMIN" has ended.
(SQL Server Import and Export Wizard)

- Post-execute (Success)
Messages
Information 0x4004300b: Data Flow Task 1: "Destination - MODEL_SPEC_VALUE" wrote 15 rows.
(SQL Server Import and Export Wizard)

Information 0x4004300b: Data Flow Task 1: "Destination 1 - PLANT_MODEL" wrote 16 rows.
(SQL Server Import and Export Wizard)

Information 0x4004300b: Data Flow Task 1: "Destination 2 - PRODC_CHECK_SEQ_RESULT" wrote 26682 rows.
(SQL Server Import and Export Wizard)

Information 0x4004300b: Data Flow Task 1: "Destination 3 - PRODC_SN_CHECK_RESULT" wrote 26614 rows.
(SQL Server Import and Export Wizard)

Information 0x4004300b: Data Flow Task 1: "Destination 4 - Table1" wrote 0 rows.
(SQL Server Import and Export Wizard)

Information 0x4004300b: Data Flow Task 2: "Destination 5 - TB_AREA_SWIM" wrote 3 rows.
(SQL Server Import and Export Wizard)

Information 0x4004300b: Data Flow Task 2: "Destination 6 - TB_LOGIN_ADMIN" wrote 3 rows.
(SQL Server Import and Export Wizard)

Information 0x4004300b: Data Flow Task 2: "Destination 7 - TB_MMT_COUNTER_MAP" wrote 7 rows.
(SQL Server Import and Export Wizard)

Information 0x4004300b: Data Flow Task 2: "Destination 8 - TB_SWIM_RECORD" wrote 2 rows.
(SQL Server Import and Export Wizard)

Information 0x4004300b: Data Flow Task 2: "Destination 9 - TB_SWIMING_USER" wrote 1 rows.
(SQL Server Import and Export Wizard)