MySQL Developing And Programming Blog

Working with MySQL SQL 54 column name, increasing the field size of a particular column, or dropping/adding primary keys. First, let s add a new column to our acc table: mysql> ALTER TABLE account ADD access int; Query OK, 8 rows affected (0.11 sec) Records: 8 Duplicates: 0 Warnings: 0 This query adds a new column called access to the account table and uses a column type of int. The ADD clause of ALTER TABLE has a few options. The full definition is ALTER TABLE
ADD [COLUMN] [FIRST|AFTER ] By using FIRST or AFTER, you ensure that the new column is specifically placed within the table definition. The default placement is at the end of the current table definition. What if you wanted to change a column s data type? For example: mysql> ALTER TABLE account CHANGE access access varchar(15); Query OK, 8 rows affected (0.11 sec) Records: 8 Duplicates: 0 Warnings: 0 This query changes the access column to a varchar(15). Notice how the column name had to be used twice. The CHANGE clause doesn t know if you are changing the name of the column, the type, or both, so it requires that you specify the column name. MySQL includes a clause called MODIFY that assumes the name isn t going to change: mysql> ALTER TABLE account MODIFY access varchar(15); Query OK, 8 rows affected (0.11 sec) Records: 8 Duplicates: 0 Warnings: 0 If you want to remove a primary key currently defined on a table, use the following query: mysql> ALTER TABLE account DROP PRIMARY KEY; Query OK, 8 rows affected (0.11 sec) Records: 8 Duplicates: 0 Warnings: 0 A new primary key can be added with the following query: mysql> ALTER TABLE account ADD primary key(acc_id); Query OK, 8 rows affected (0.11 sec) Records: 8 Duplicates: 0 Warnings: 0 Placing Tables on Specific Drives When you are building a large database system, you probably want to disperse the actual tables across disk drives. This is possible using the DATA DIRECTORY clause of the ALTER TABLE command. For example: ALTER TABLE account DATA DIRECTORY=”/usr/local/databases/account”

Hint: If you are looking for very good and affordable webspace to host and run your tomcat hosting application check Sandzak.com tomcat web hosting provider