Add a column to an existing MySQL table
MySQL tables are easy to extend with additional columns.
To add a column called email to the contacts table created in Create a basic MySQL table with a datatype of VARCHAR(80), use the following SQL statement:
ALTER TABLE contacts ADD email VARCHAR(60);
This first statement will add the email column to the end of the table. To insert the new column after a specific column, such as name, use this statement:
ALTER TABLE contacts ADD email VARCHAR(60) AFTER name;
If you want the new column to be first, use this statement:
ALTER TABLE contacts ADD email VARCHAR(60) FIRST;
Also see ...
Delete a column from an existing MySQL table
H3The SQL command in this recipe removes a column and the column's data from an existing MySQL table./H3PTo delete the column span style="font weight: bold"col_stuff/span from the table span style="font weight: bold"table_things/span, use the following SQL command: br / br /div cl
H3The SQL command in this recipe removes a column and the column's data from an existing MySQL table./H3PTo delete the column span style="font weight: bold"col_stuff/span from the table span style="font weight: bold"table_things/span, use the following SQL command: br / br /div cl
Modify an existing MySQL column
H3The best laid plans of mice and DBAs oft go awry, so it is sometimes necessary to change the characteristics of a column after it exists and contains data. Beware whenever you make changes to your database always make a backup first./H3PAfter a week of using the contacts table created in
H3The best laid plans of mice and DBAs oft go awry, so it is sometimes necessary to change the characteristics of a column after it exists and contains data. Beware whenever you make changes to your database always make a backup first./H3PAfter a week of using the contacts table created in
MySQL dump import -> Error 1217
H3When using innodb tables and foreign key constraints, importing a mysqldump can sometimes generate foreign key errors. /H3PAdd to the beginning of the dump file: SET FOREIGN_KEY_CHECKS=0; br / br /Add to the end of the dump file: SET FOREIGN_KEY_CHECKS=1; br / br /This disables forei
H3When using innodb tables and foreign key constraints, importing a mysqldump can sometimes generate foreign key errors. /H3PAdd to the beginning of the dump file: SET FOREIGN_KEY_CHECKS=0; br / br /Add to the end of the dump file: SET FOREIGN_KEY_CHECKS=1; br / br /This disables forei
Show or list tables in a MySQL database
H3Once you have selected a database, you can list the tables in it by using the show command. /H3PTo view all of the tables in the selected database, use: br / br /[code]SHOW TABLES; br /[code] br /To view all of the tables in a different database that isn't selected: br / br /&
H3Once you have selected a database, you can list the tables in it by using the show command. /H3PTo view all of the tables in the selected database, use: br / br /[code]SHOW TABLES; br /[code] br /To view all of the tables in a different database that isn't selected: br / br /&
Use regular expressions in MySQL SELECT statements
H3A very cool and powerful capability in MySQL and other databases is the ability to incorporate regular expression syntax when selecting data. The regular expresion support in MySQL is extensive. This recipe reviews regular expression use in MySQL and lists the supported regular expression meta
H3A very cool and powerful capability in MySQL and other databases is the ability to incorporate regular expression syntax when selecting data. The regular expresion support in MySQL is extensive. This recipe reviews regular expression use in MySQL and lists the supported regular expression meta