Entry

Database: Language: SQL: Table: Structure: Operation: Change: How to add fields in a table?

Apr 4th, 2005 10:01
Knud van Eeden,

----------------------------------------------------------------------
--- Knud van Eeden --- 05 April 2005 - 00:06 am ----------------------

Database: Language: SQL: Table: Structure: Operation: Change: How to 
add fields in a table?

---

Use the command

 USE <your databasename>

 ALTER TABLE <your tablename> ADD COLUMN <your columnname> <your 
column 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       |       |
+--------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE tableStudy ADD COLUMN columnStudyId INT 
AUTO_INCREMENT NOT NULL PRIMARY KEY;

Query OK, 3 rows affected (0.58 sec)
Records: 3  Duplicates: 0  Warnings: 0


mysql> show fields from tableStudy;

+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| columnStudy   | varchar(50) | YES  |     | NULL    |                |
| columnNumber  | int(11)     |      | MUL | 0       |                |
| columnStudyId | int(11)     |      | PRI | NULL    | auto_increment |
+---------------+-------------+------+-----+---------+----------------+
3 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

----------------------------------------------------------------------