Let you know software development in advance (36): How to expand data table fields ?, Software Development Field

Let you know software development in advance (36): How to expand data table fields ?, Software Development Field

Part 1 Database SQL language

How to expand data table fields?

 

Abstr]

Communication Software often deals with databases. Expansion of data table fields is common due to changes in requirements or program optimization and upgrade. This requires developers to be familiar with the operation process of extending data table fields.

Based on the author's database experience, this article takes the actual SQL program as an example to describe how to expand data table fields, which provides a reference for related development work.

【Key words]

Extended SQL development for database data tables

 

I. Preface

In actual software development projects, the expansion of data table fields includes the following two aspects:

First,Extension of Original Field Values. For example, the original table has a field "result", indicating the result. The previous values are 0 and 1. Now you need to extend the value range and add a result value of 2. For this type of extension, the data table structure does not need to be dynamic. You only need to let the relevant module know the extension with this value.

Second,New Field. That is, the existing fields in the original table cannot meet the current requirements. One or more fields need to be added, which involves changing the structure of the data table.

This article mainly discusses the process and operation method of field extension in the second case. All scripts in this article are based on the Sybase Database.

 

2. Data Table field extension process

When adding a field, you cannot simply delete the table or recreate the table, because in the actual software running environment, almost every data table contains a lot of data. If the table is deleted no matter which of the following conditions, some important data may be lost, which may cause adverse effects or even complaints from customers.

In the actual software development project, the expansion process of the data table Field 1 is shown.

Figure 1 Process for extending data table fields

 

Iii. Example of data table field extension

There is an employee information table that contains three fields: employee ID, name, and age:

Create table tb_employeeinfo

(

Workid int default (0) not null, -- workid

Name varchar (50) default ('') not null, -- name

Age int default (0) not null -- age

)

Go

 

Create unique index idx1_tb_employeeinfo on tb_employeeinfo (workid)

Go

 

Print 'create table tb_employeeinfo OK'

Go

 

Now we need to extend the address field on the basis of the original table to record the residential address of the employee. This field can be blank.

The execution SQL script for the whole field extension is as follows:

-- Step 1: create a backup table

If exists (select * from sysobjects where name = 'tb _ employeeinfobak ')

Drop table tb_employeeinfobak

Go

Create table tb_employeeinfobak

(

Workid int default (0) not null, -- workid

Name varchar (50) default ('') not null, -- name

Age int default (0) not null -- age

)

Go

 

Create unique index idx1_tb_employeeinfobak on tb_employeeinfobak (workid)

Go

 

Print 'create table tb_employeeinfobak OK'

Go

 

-- Step 2: insert the content of the original table to the backup table

Insert into tb_employeeinfobak (workid, name, age) select workid, name, age from tb_employeeinfo

Go

 

-- Step 3: delete the original table

If exists (select * from sysobjects where name = 'tb _ employeeinfo ')

Drop table tb_employeeinfo

Go

 

-- Step 4: Create a new table

Create table tb_employeeinfo

(

Workid int default (0) not null, -- workid

Name varchar (50) default ('') not null, -- name

Age int default (0) not null, -- age

Address varchar (100) null -- address

)

Go

 

Create unique index idx1_tb_employeeinfo on tb_employeeinfo (workid)

Go

 

Print 'create table tb_employeeinfo OK'

Go

 

-- Step 5: insert the backup table content to the new table

Insert into tb_employeeinfo (workid, name, age) select workid, name, age from tb_employeeinfobak

Go

 

-- Step 6: Delete the backup table

If exists (select * from sysobjects where name = 'tb _ employeeinfobak ')

Drop table tb_employeeinfobak

Go

 

After the preceding six steps, fields in the data table are extended and the original data is not lost.

 

Iv. Summary

This article takes the actual SQL script as an example to introduce in detail the whole process of extending data table fields, which provides a useful reference for the development of related software development activities.

 

 

(My microblogging: http://weibo.com/zhouzxi? Topnav = 1 & wvr = 5, No.: 245924426, welcome !)

 

How to add reserved fields to a data table

Alter table name add field name attribute
It doesn't matter if you add one. You don't have any data in it.
Even if the type and length of the field are modified, you can alter it again. As long as the field does not store data, you can use it if you want to use it, it doesn't matter if you don't want to use it and drop it again.

How to add fields to a database?

Simple Method:

1) create a new database (after adding a field)
2) import from the old database, import the same field, blank new field, or fixed value.