Entry
Database: Language: SQL: Table:Structure: Operation:Change: How to change fields in a table? [ALTER]
May 22nd, 2005 03:07
Knud van Eeden,
----------------------------------------------------------------------
--- Knud van Eeden --- 05 April 2005 - 00:32 am ----------------------
Database: Language: SQL: Table:Structure: Operation:Change: How to
change fields in a table? [ALTER]
---
You use this when you want to alter the field structure of the table
---
Use the command:
ALTER TABLE <your tablename> CHANGE <your old columnname> <your new
columnname> <your new column attributes>
---
or similarly to change the attributes of an existing columnname:
ALTER TABLE <your tablename> CHANGE <your old columnname> <your old
columnname> <your old columnname new attributes>
---
or also (starting from MySql v3.22.16 or higher) you can use:
ALTER TABLE <your tablename> MODIFY <your old columnname> <your old
columnname new attributes>
---
---
e.g.
--- cut here: begin --------------------------------------------------
mysql> use test1;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| dddtablename |
| table1 |
| table2 |
| table3 |
| table4 |
| table5 |
| tablename |
| tablestudy |
+-----------------+
8 rows in set (0.00 sec)
mysql> select * FROM tableName;
+--------------+------------+
| columnNumber | columnName |
+--------------+------------+
| 1 | John |
| 2 | Vanessa |
| 3 | Bella |
+--------------+------------+
3 rows in set (0.00 sec)
mysql> select * FROM tableStudy;
+------------------+--------------+
| columnStudy | columnNumber |
+------------------+--------------+
| Physics | 1 |
| Mathematics | 2 |
| Computer science | 3 |
+------------------+--------------+
3 rows in set (0.00 sec)
mysql> show fields from tableName;
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| columnNumber | int(11) | | PRI | NULL | auto_increment |
| columnName | varchar(50) | YES | | NULL | |
+--------------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> show fields from tableStudy;
+---------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+----------------+
| columnStudy | varchar(50) | YES | | NULL | |
| columnNumber | int(11) | | MUL | 0 | |
+---------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> ALTER TABLE tableStudy CHANGE columnStudy columnStudyOverview
VARCHAR(50);
Query OK, 3 rows affected (0.41 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> show fields from tableStudy;
+---------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+-------------+------+-----+---------+-------+
| columnStudyOverview | varchar(50) | YES | | NULL | |
| columnNumber | int(11) | | MUL | 0 | |
+---------------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
--- cut here: end ----------------------------------------------------
---
---
Internet: see also:
---
Database: Language: SQL: Overview: Can you give an overview of links
about SQL?
http://www.faqts.com/knowledge_base/view.phtml/aid/32811/fid/54
----------------------------------------------------------------------
