اضافه کردن فیلد در زمان اجرا

[ramin_mash]

سلام دوستان
من بانک اکسس دارم شامل تعداد جدول و فیلدهای مختلف از طرفی تعدادی dbfدارم که با نام جدولهای بانکم یکی هست
می خواهم از بانک اکسس هر فیلدی که در DBF وجود ندارد را شناسایی کنم و در DBF ایجاد کنم تونستم نوع فیلد و سایز را شناسایی کنم ولی نمی تونم در DBFفیلد ایجاد کنم
از دستور زیر هم استفاده کردم
able2.FieldDefs.Add(s1,f2.DataType,f2.Size,false);
ولی چون قبلا جدول ایجاد شده نمی تونم craeteTable استفاده کنم
لطفا راهنمایی کنید

[mehdi_mohamadi]

از دستور Alter table استفاده کنید
---------------
موفق باشید

[ramin_mash]

داخل sql نمی خوام
تو دلفی بعد از این دستور دستو table1.craetetable را داره ولی در مورد بروز رسانی چیزی ندیدم
چکار باید کرد؟

[Hamid_PaK]

فکر نکنم بدون کوئری بشه این کار رو کرد !
همونطور که دوستمون گفت باید از کوئری و ALTER TABLE استفاده کنید ، راهنمای SQL رو مطالعه کنید.


Altering Table Structure
ALTER TABLE is a versatile statement in MySQL, and you can use it for many purposes. We've already seen some of its capabilities in this chapter (for changing storage engines and for creating and dropping indexes). You can also use ALTER TABLE to rename tables, add or drop columns, change column data types, and more. In this section, we'll cover some of its features. The complete syntax for ALTER TABLE is described in Appendix E.

ALTER TABLE is useful when you find that the structure of a table no longer reflects what you want to do with it. You might want to use the table to record additional information, or perhaps it contains information that has become superfluous. Maybe existing columns are too small. Perhaps it turns out that you've defined them larger than you need and you'd like to make them smaller to save space and improve query performance. Here are some situations in which ALTER TABLE is valuable:

You're running a research project. You assign case numbers to research records using an AUTO_INCREMENT column. You didn't expect your funding to last long enough to generate more than about 50,000 records, so you made the data type SMALLINT UNSIGNED, which holds a maximum of 65,535 unique values. However, the funding for the project was renewed, and it looks like you might generate another 50,000 records. You need to make the type bigger to accommodate more case numbers.

Size changes can go the other way, too. Maybe you created a CHAR(255) column but now recognize that no value in the table is more than 100 characters long. You can shorten the column to save space.

You want to convert a table to use a different storage engine to take advantage of features offered by that engine. For example, MyISAM tables are not transaction-safe, but you have an application that needs transactional capabilities. You can convert the affected tables to be InnoDB or BDB tables, because those storage engines are transactional.

The syntax for ALTER TABLE looks like this:

ALTER TABLE tbl_name action [, action] ... ;



Each action specifies a modification that you want to make to the table. Some database systems allow only a single action in an ALTER TABLE statement. MySQL allows multiple actions, separated by commas.

Tip: If you need to remind yourself about a table's current definition before using ALTER TABLE, issue a SHOW CREATE TABLE statement. This statement also can be useful after ALTER TABLE to see how the alteration affected the table definition.

The following examples show some of the capabilities of ALTER TABLE.

Changing a column's data type. To change a data type, you can use either a CHANGE or MODIFY clause. Suppose that the column in a table mytbl is SMALLINT UNSIGNED and you want to change it to MEDIUMINT UNSIGNED. Do so using either of the following commands:

ALTER TABLE mytbl MODIFY i MEDIUMINT UNSIGNED;
ALTER TABLE mytbl CHANGE i i MEDIUMINT UNSIGNED;



Why is the column named twice in the command that uses CHANGE? Because one thing that CHANGE can do that MODIFY cannot is to rename the column in addition to changing the type. If you had wanted to rename i to j at the same time you changed the type, you'd do so like this:

ALTER TABLE mytbl CHANGE i j MEDIUMINT UNSIGNED;



The important thing with CHANGE is that you name the column you want to change and then specify a complete column definition, which includes the column name. That is, you must include the name in the definition, even if it's the same as the old name.

To rename a column, use CHANGE old_name new_name followed by the column's current definition.

You can assign character sets to individual columns, so it's possible to use the CHARACTER SET attribute in a column's definition to change its character set:

ALTER TABLE t MODIFY c CHAR(20) CHARACTER SET ucs2;



An important reason for changing data types is to improve query efficiency for joins that compare columns from two tables. Indexes often can be used for comparisons in joins between similar column types, but comparisons are quicker when both columns are exactly the same type. Suppose that you're running a query like this:

SELECT ... FROM t1, t2 WHERE t1.name = t2.name;



If t1.name is CHAR(10) and t2.name is CHAR(15), the query won't run as quickly as if they were both CHAR(15). You can make them the same by changing t1.name using either of these commands:

ALTER TABLE t1 MODIFY name CHAR(15);
ALTER TABLE t1 CHANGE name name CHAR(15);



Converting a table to use a different storage engine. To convert a table from one storage engine to another, use an ENGINE clause that specifies the new engine name:

ALTER TABLE tbl_name ENGINE = engine_name;



engine_name is a name such as MyISAM, MEMORY, BDB, or InnoDB. Lettercase of the name does not matter.

Changing storage engines can be useful when you upgrade your MySQL installation to a newer version that provides additional table-handling features. For example, if you inherit an old pre-3.23 database, its tables will be in ISAM format. To change them to MyISAM tables, use this statement for each one:

ALTER TABLE tbl_name ENGINE = MyISAM;



Doing this allows you to take advantages of the capabilities that MyISAM offers that ISAM does not. For example, MyISAM tables are binary portable, so you can move them to other machines by copying table files directly, even if the machines have different hardware architectures. In addition, MyISAM tables have better indexing characteristics than ISAM.

Another reason to change a storage engine is to make it transaction-safe. Suppose that you have a MyISAM table and discover that an application that uses it needs to perform transactional operations, including rollback in case failures occur. MyISAM tables do not support transactions, but you can make the table transaction-safe by converting it to an InnoDB or BDB table:

ALTER TABLE tbl_name ENGINE = InnoDB;
ALTER TABLE tbl_name ENGINE = BDB;



When you convert a table to use a different engine, the allowable or sensible conversions may depend on the feature compatibility of the old and new types:

Suppose that you have a MyISAM table that includes a BLOB column. You cannot convert the table to MEMORY format because MEMORY tables do not support BLOB columns.

If you have a MyISAM table that includes FULLTEXT indexes or uses spatial data types, you cannot convert it to another engine because only MyISAM supports those features.

There are circumstances under which you should not use ALTER TABLE to convert a table to use a different storage engine:

MEMORY tables are held in memory and disappear when the server exits. It is not a good idea to convert a table to type MEMORY if you require the table contents to persist across server shutdowns.

If you use a MERGE table to group a collection of MyISAM tables together, you should avoid using ALTER TABLE to modify any of the MyISAM tables unless you make the same change to all of them, and to the MERGE table as well. The proper functioning of a MERGE table depends on its having the same structure as all of its constituent MyISAM tables.

An InnoDB table can be converted to use another storage engine. However, if the table has foreign key constraints, they will be lost because only InnoDB supports foreign keys.

Renaming a table. Use a RENAME clause that specifies the new table name:

ALTER TABLE tbl_name RENAME TO new_tbl_name;



Another way to rename tables is with RENAME TABLE. The syntax looks like this:

RENAME TABLE old_name TO new_name;



One thing that RENAME TABLE can do that ALTER TABLE cannot is rename multiple tables in the same statement. For example, you can swap the names of two tables like this:

RENAME TABLE t1 TO tmp, t2 TO t1, tmp TO t2;



If you qualify a table name with a database name, you can move a table from one database to another by renaming it. Either of the following statements move the table t from the sampdb database to the test database:

ALTER TABLE sampdb.t RENAME TO test.t;
RENAME TABLE sampdb.t TO test.t;



You cannot rename a table to a name that already exists.

If you rename a MyISAM table that is part of a MERGE table, you must redefine the MERGE table accordingly.


یا حق ...