为了实现一个简单的删除,折腾了我一上午.情况如下:

我有两张表,一张是车辆信息表(LOG_Vehicle_Info),一张是车辆附加信息表(LOG_Vehicle_Extra_Info)表.在车辆信息表里,每一条记录都有在车辆附加信息表里有着一一对应的附加信息.他们之间的关系是一对一的关系.车辆附加信息PK为EV_Id,在车辆信息表中有FK(EV_Id).

现在需要对特定的车辆信息进行删除,当然,该信息所对应的附加信息也得要删除才行.

原始方法:

为了删除附加信息表中记录,我们需要知道附加信息的ID编号,但这个编号我们只能通过车辆信息表才能找到,所以实现方法如下:

通过车辆号码(车辆信息表的PK)查询出需要删除的车辆的附加信息ID编号(EV_ID),然后做两次删除.因为现在我们知道了附加信息表的的主键EV_Id.

为了实现删除这一操作,我需要向数据库执行一次查询,然后通过查询的结果作为条件进行两次的数据库删除,为了得到查询结果,C++代码就有好几行,所以这种方法不但麻烦而且效率还不高.

最新方法:

介绍这种方法之前,需要说明一下外键的概念,"外键主要功能是为了加强两表中的一行或者多行之间的联系,数据库中,它使数据保持完整性,即:同一数据,在不同的地方引用,当这一数据作相关的改变后,那么引用他的所有数据都会自动同步.",所以有了外键就好办多了,而且这一切都是数据库系统自动完成的,不需要我们过多的干预.

这里我的思想是,由于车辆附件信息表中的主键EV_Id是车辆信息表中的外键,那么只要删除了车辆附加信息表中的内容,那么所有在车辆信息表中引用该记录的行将会被数据库系统自己删除.

SQL语句如下:

DELETE
	[LOG_Vehicle_Extra_Info]//注意是删除车辆附加信息表,而不是车辆信息表
FROM
	[LOG_Vehicle_Info]
WHERE
	LOG_Vehicle_Info.EV_Id = LOG_Vehicle_Extra_Info.EV_Id
AND
	LOG_Vehicle_Info.Vehicle_CardNo= 'vvv'

 删除车牌号为vvv的车辆信息.

要注意这里的语法:

DELETE
	[LOG_Vehicle_Extra_Info]//注意是删除车辆附加信息表,而不是车辆信息表
FROM

我们删除车辆附加信息表,这样,一旦在车辆信息表中的外键EV_Id的记录不存在的话,那么数据库系统将会自己删除该记录.如果我们

DELETE
	[LOG_Vehicle_Info]//是删除车辆信息表,而不是车辆附加信息表
FROM

这样写,那么我们只是删除了车辆信息表中的记录,而它所对应的附加信息依然存在,由于我们只是删除引用附加信息的表的记录,而不是删除了附加信息的记录,因为这里并没有破坏数据的完整性定义,所以数据库系统不会去删除附加信息表的.

技巧:在们删除多表时,我们只需要删除关联数据表,那么所有引用此关联数据表所对应表的记录将会被系统自动删除.

机器人 2007年7月26日 于 北京 (困死了,马上就去睡午觉)

本文内容包括。

（1） 注册远程SQL服务器到本地

（2） 执行数据的导入导出功能

（3） 注册服务器不成功的处理

一。打开 企业管理器 (Enterprise Manager) 工具

二。将一个远程sql服务器注册到本地管理。新建一个SQL server注册

三。

四。

五。

六。

七。

八。

九。

十。

（2） 数据的导入导出功能

一。导入数据，意指将从本地库里把数据导进入到指定的库里。

导出数据，意指将指定的库里的数据导出到本地的库里。

二。选者一个数据库源，意指从何处复制数据到新的库里。

三。指定一个目的库，意指将数据导入哪里去。

四。

五。

六。

七。

八。

九。

（3）注册服务器不成功的处理。（在网络不是很顺畅的时候，可以加大服务器连接超时时间）

1。

2。

来源:网络

1. To start your database model diagram, do one of the following:

Create a new model diagram from scratch

1. On the File menu, point to New, point to Database, and then click Database Model Diagram.
2. On the Database menu, point to Options, and then click Document. In the Database Document Options dialog box, select the symbol set you want to use and other table and relationship options, and then click OK.

Use a reverse engineered model diagram as a starting point

1. On the File menu, point to New, point to Database, and then click Database Model Diagram.
2. On the Database menu, click Reverse Engineer.
3. On the first screen of the Reverse Engineer Wizard, do the following:
   • Select the Microsoft Office Visio database driver for your database management system (DBMS). If you have not already associated the Visio database driver with a particular 32-bit ODBC data source, do so now.
   • Select the data source of the database you’re updating. If you have not already created a data source for the existing database, do so now.

   Notes

   When you create a new source, its name is added to the Data Sources list.

   • When you are satisfied with your settings, click Next.
4. Follow the instructions in any driver-specific dialog boxes.

For example, in the Connect Data Source dialog box, type a user name and password, and then click OK.

5. Select the check boxes for the type of information you want to extract, and then click Next.
6. Select the check boxes for the tables (and views, if any) that you want to extract, or click Select All to extract them all, and then click Next.
7. If you selected the Stored Procedures check box in step 5, select the procedures that you want to extract, or click Select All to extract them all, and then click Next.
8. Select whether you want the reverse engineered items added automatically to the current page.
9. Review your selections to verify that you are extracting the information you want, and then click Finish.

The wizard extracts the selected information and displays notes about the extraction process in the Output window.

10. Create a diagram of your model by
    • Dragging the tables you want to view from the Tables and Views window onto the drawing page.
    • Selecting a table, and then on the Database menu, clicking Show Related Tables to view the tables related to the selected table.

Import and refine an existing model

1. On the File menu, point to New, point to Database, and then click Database Model Diagram.
2. On the Database menu, point to Import, and then click Import <model type>.
3. Type the path and file name for the model you want to import, or click the Browse button to locate the model file, and then click Open.
4. Click OK in the Import dialog box.

Visio imports the file and displays its progress in the Output window. The imported tables are displayed in the Tables and Views window.

5. In the Tables and Views window, select the tables you want to model, and then drag them onto the drawing page.
2. Once you start a database model diagram, the real work of refining it begins. You can add

Tables

1. Open the database model diagram.
2. From the Entity Relationship or Object Relational stencil, drag an Entity shape onto the drawing page.

Columns

1. Open the database model diagram.
2. Double-click the table to which you want to add a column, and then in the Database Properties window, in the Categories list, click Columns.
3. Do one of the following:
   • Click the Physical Name field of a blank line, and then type the name of the column. Press the ENTER or DOWN ARROW key and repeat for each additional column that you want to add to the table.
   • Click Add. To add several columns to the table, click Add several times.

The default name for the new column follows the naming conventions you specified in the Database Modeling Preferences dialog box. To change the name of the column, click the default column name, and then type a new name for the column. Repeat to add additional columns to the table.

4. Click either Physical Data Types or Portable Data Types to display the data types your database model uses in the Data Type field.
5. To change the data type for a column, click the column’s Data Type field, and then select a data type from the list, or simply begin typing the data type, including any parameters, and press ENTER. For example, you can type decimal(8,2) or char(30).
6. To prevent null values, select the Req’d check box.
7. To specify that the column is a primary key, select the PK check box.

The database model diagram is updated.

Note  You can press the TAB key to move from cell to cell, and you can use the SPACEBAR to select and clear the Req’d and PK check boxes.

There are more column properties in addition to those that appear when you click the Columns category. To edit additional column properties, click Edit.

Foreign key relationships

1. Open the database model diagram.
2. Click the Connector tool on the Standard toolbar. Position the Connector tool over the center of the parent table so that the table is outlined and drag to the center of the child table. When the outline appears on the child table, release the mouse button. Both connection points appear red, and any primary keys in the parent table are added as foreign keys to the child table.
3. Define the relationship by editing its properties.

How

1. Open the database model diagram.
2. Double-click the relationship whose properties you want to edit to open the Database Properties window.
3. In the Database Properties window, click the category that contains the information you want to edit, and then make your changes.

The database model diagram is updated.

Data types

1. Open the database model diagram.
2. Double-click the table that contains the column to which you want to add a composite data type, and then in the Database Properties window, in the Categories list, click Columns.
3. Click the name of the column to which you want to add a composite data type, and then click Edit.
4. In the Column Properties dialog box, click the Data Type tab, and then select the composite data type from the User Defined Type list.
5. When you are satisfied with your settings, click OK.

Tip

You can also add a composite data type to a table by clicking the Data Type box for the column to which you want to add a composite data type and either clicking the composite data type or typing the name of the composite data type.

3. To make other refinements to your diagram, you can

Create indexes

1. Open the database model diagram.
2. Double-click the table to which you want to add an index, and then in the Database Properties window, in the Categories list, click Indexes.
3. Click New.
4. In the Create Index dialog box, type a name for the index, and then click OK.
5. In the Index Type list, select an option to create a unique or non-unique index.
6. In the Available Columns list, select the name of each column you want to include in this index, and then click Add >.
7. In the Indexed Columns list, select the Asc check box to create an index that has an ascending sort order, or clear it to create one that has a descending sort order.

The database model diagram is updated.

Create views

From the Entity Relationship or Object Relational stencil, drag a View shape onto the drawing page.

Set extended properties for tables and views

Double-click the table or view whose extended properties you want to set, and then in the Database Properties window, in the Categories list, click Extended.

4. You can also add code for the following:

Check clauses

1. Double-click the table to which you want to add a check clause, and then in the Database Properties window, in the Categories list, click Check.
2. Click Add to open the Code Editor.
3. On the Properties tab, type a name for the check clause.
4. On the Body tab, enter SQL statements for the check clause. When you’re finished, click OK.

The check clause is added to the Local Code list in the Code window.

Stored procedures

1. If the Code window is not already open, on the Database menu, point to View, and then click Code to open the Code window.

Note  This command is available only in the Database Model Diagram template.

2. Click Global Code, and then click New to open the Code Editor.
3. On the Properties tab, type a name for the code item. Under Type, click Stored Proc, Function, or Raw DDL.
4. On the Body tab, type SQL statements for your code.
5. When you’re finished, click OK.

The code is added to the Global Code list in the Code window.

Triggers

1. Double-click the table to which you want to add a trigger, and then in the Database Properties window, in the Categories list, click Triggers.
2. Click Add to open the Code Editor.
3. On the Properties tab, type a name for the trigger.
4. On the Body tab, enter SQL statements for the trigger. When you are finished, click OK.

The trigger is added to the Local Code list in the Code window.

5. If the physical database was changed after you reverse engineered it, you can update your diagram to reflect those changes.

一台机器上备份了一个数据库,把备份后的文件打击拷到另一台机器上准备还原时却出了错,提示是:"设备激活错误,物理文件名:d:\program files\microsoft sqlserver\MSSQL\finance_data.mdf"可能有误.文件finance_DATA无法还原为d:\program files\microsoft sqlserver\MSSQL\finance_data.mdf.请使用WITH MOVE 选项来标识该文件的有效位置…"

解决方法：

目录不对

–解决第一个问题的详细步骤:

1.企业管理器中的方法:

–右键"数据库"

–所有任务

–还原数据库

–"还原为数据库库"中输入还原后的数据库名

–还原选择"从设备"–选择设备–添加–添加你的备份文件–确定,回到数据库还原的界面

–备份号–选择内容–选择你要恢复那次备份的内容

–选项–将"移至物理文件名"中的物理文件名修改为你的数据文件要存放的文件名

–如果要还原的数据库已经存在,选择"在现有数据库上强制还原"-

-确定

2.用SQL语句的方法(假设你的备份文件名为: c:\xx.bak

–列出备份文件中的逻辑文件名

restore filelistonly from disk='c:\xx.bak'

–用语句恢复,根据上面列出的逻辑文件名使用move选项

restore database 恢复后的数据库名
from disk='c:\xx.bak'
with move '逻辑数据文件名1' to 'c:\物理数据文件名1'
,move '逻辑数据文件名2' to 'c:\物理数据文件名2'
...
,move '逻辑数据文件名n' to 'c:\物理数据文件名n'

例如：

restore database automis
from disk='d:\autoback'
with move 'autoshool_Data' to 'D:\Program Files\Microsoft SQL Server\MSSQL\data\automis_Data.MDF'
,move 'autoshool_Log' to 'D:\Program Files\Microsoft SQL Server\MSSQL\data\automis_Log.LDF'

ODBC(Open DataBase Connectivity,开放数据库互连)是微软公司开放式服务结构（WOSA,Windows Open Services Architecture）中有关数据库的一个组成部分，它建立了一组规范，关提供了一组对数据库访问的标准API（应用程序编程接口）．这些API利用SQL来完成其大部分任务．ODBC本身也提供了对SQL语言的支持，用户可以直接将SQL语句付给ODBC．

ODBC的结构是建立在客户机／服务器（C/S）体系结构之上，它包含如下４部分．

1.      应用程序（Application）

应用程序即用户的应用，它负责用户与用户接口之间的交互操作，调用ODBC函数以给出SQL请求并提取结果以及进行错误处理．

2.      ODBC驱动程序管理器（Driver Manager）

ODBC驱动程序管理器可以为应用程序加载和调用驱动程序，它可以同时管理多个应用程序和多个驱动程序．它的功能是通过间接调用函数和使用动态链接库（DLL）来实现，因此它一般包含在扩展名为”DLL”的文件中．

3.      ODBC驱动程序（Driver）．

ODBC驱动程序执行ODBC函数调用，呈送SQL请求给指定的数据源，并将结果返回给应用程序．驱动程序也负责访问数据源的必要软件层交互，软件层包括与底层网络或文件系统接口的软件．

4.      数据源．

数据源由数据集和与其相关的环境组成，包括操作系统、包括操作系统、DBMS和网络（如果存在）．ODBC通过引入＂数据源＂的概念解决了网络拓扑结构和主机的大范围差异问题，这样，用户看到的是数据源的名称而不必关心其内容．

ODBC结构各部分之间的关系图如下：

应用程序要访问一个数据库，首先必须用ODBC管理器注册一个数据源，管理器根据数据源提供的数据库位置、数据库类型及ODBC驱动程序等信息，建立起ODBC写具体数据库的联系，这样，只要应用程序将数据源名提供给ODBC，ODBC就能建立起写相应数据库的连接．

在ODBC中，ODBC　API不能直接访问数据库，必须通过驱动程序管理器与数据库交换信息．驱动程序管理器负责将应用程序对ODBC API的调用传递给正确的驱动程序，而驱动程序在执行相应的操作后，将结果通过驱动管理器返回给应用程序．

2007年6月3日　机器人于　北京　（学习整理）