Introducing MySQL SQL 55 Note that the DATA DIRECTORY option in the ALTER TABLE as well as in the CREATE TABLE command is available only on MyISAM tables underMySQL 4.0. Deleting Tables If you are absolutely sure that you want to get rid of a table permanently, use the command DROP TABLE
. Here is a simple example of using DROP TABLE: mysql> create table test (id int); Query OK, 0 rows affected (0.05 sec) mysql> insert into test values(1); Query OK, 1 row affected (0.02 sec) mysql> drop table test; Query OK, 0 rows affected (0.03 sec) Notice that the table will be dropped without any reservation by the database server. It is vital that you type in the table name accurately because once a table has been dropped, it is no longer available. Transactions One of the most powerful aspects of MySQL is its ability to use transactions. A transaction is an atomic action that must either succeed or fail. This means that in a transaction consisting of three different queries a SELECT, an INSERT, and an UPDATE if any of these operations fail, the other commands must be rolled back to their original state. The current MySQL system includes two different table types that allow for transactions: InnoDB and BDB. In order for a database table to use transactions, the table must be created using a TYPE clause or the table must be altered with an ALTER TABLE command also using the TYPE clause. Once you create a table to handle transactions, you must inform the MySQL system that you want to use transactions. You can accomplish this by using the autocommit database server variable. By default, this variable is set to a value of 1, meaning that the database server will automatically commit the query once it executes. To start a transaction, the autocommit variable must be set to 0. For example: mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) Now you have the ability to execute SQL statements that will be either committed to the database or rolled back. Start with either the BEGIN or BEGIN WORK statement:
Hint: This post is supported by Gama web hosting php services
This entry was posted
on Saturday, December 30th, 2006 at 11:13 pm and is filed under developing.
You can follow any responses to this entry through the RSS 2.0 feed.
Responses are currently closed, but you can trackback from your own site.