MySQL Table Commands
A MySQL database consists of one or more data tables. It’s rare that you will find MySQL table commands in PHP scripts with one exception being installer scripts where you will execute create table commands.
Creating a Table
SQL Command for creating a MySQL table has following syntax. Here ENGINE and CHARSET are optional.
CREATE TABLE table_name (column_definitions) ENGINE CHARSET;
Following is an example that you can run in command line after creating and selecting a database. You can also run this as a MySQLi query in PHP by passing it to mysqli_query() function without the semicolon at the end.
CREATE TABLE `employee` ( `id` int NOT NULL AUTO_INCREMENT, `first_name` varchar(40) NOT NULL, `last_name` varchar(40) DEFAULT NULL, `age` tinyint DEFAULT NULL, `joined_date` date DEFAULT NULL, `records` text, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
- There aren’t standard naming conventions for MySQL table and field names. But you will find usually they are lower case, words are separated by underscore (Ex: first_name) and are in singular form (employee instead of employees).
- Even though it’s optional, it’s better to enclose table and field names with backtick (`) character. It prevents conflicts with MySQL reserved words. However it’s a good practice not to use MySQL reserved words for your table and field names.
- Immediately after field name, you have to specify the data type of the field. Make sure you choose most appropriate data type for each field.
- After specifying the data type, you can mention field properties such as NOT NULL (always have to have a value) and AUTO_INCREMENT (MySQL will assign an incrementing value automatically). To distinguish from field names and data types, usually field properties are written in upper case.
- Almost all the tables you find will have a primary key which will be used when dealing with related tables (like fetching records from more than one table). Values entered in primary key column have to be unique and it’s possible to mention more than one column for primary key by separating them with commas. In that case, it’s called a composite primary key and value combinations in composite fields have to be unique.
- Indexes (not used in this example) can be specified to increase query speed in cases where number of records in a table is going to be high.
- Storage engine determines the functionalities of a table. For an example, InnoDB allows you to have constraints among tables. Default storage engine is InnoDB (You can change this by editing MySQL configuration file). You can use other storage engines as necessary.
- Charset mainly determines what type of characters and symbols you can store in table columns. Default charset is latin1. You can use a different one if necessary.
Retrieving the Table Definition of an Existing Table
You can retrieve the table definition of an existing table using SHOW CREATE TABLE as below. It will be useful when you already know a similar table and want to use its definition as a template for a new table.
SHOW CREATE TABLE employee;
You can list field names and their properties of a table using DESCRIBE as below. It will be useful when you want to see table properties in a tabular format.
Viewing Existing Table List of a Database
Following is the command to list down the existing tables of a database.
Deleting a Table
Use DROP TABLE command as below to delete a table.
DROP TABLE employee;
Changing Table Schema
You can use ALTER TABLE command to change a table schema (structure and definitions). Following is how you can add a new column to employee table.
ALTER TABLE `employee` ADD `middle_name` varchar(40) DEFAULT NULL AFTER `first_name`;
Deleting All the Records of a Table
You can use TRUNCATE TABLE command to delete all the records of a table and reset its auto-increment counter.
TRUNCATE TABLE employee;
You can also use following command to delete all the records from a table. But it won’t reset auto-increment counter.
DELETE FROM employee;
Copying a Table
To copy both schema and data of an existing table to a new table, use following command.
CREATE TABLE retired_employee SELECT * FROM employee;
If you only need to copy table schema, use following command.
CREATE TABLE retired_employee LIKE employee;
Foreign Key Constraints
Foreign key constraints are helpful when you want to maintain better data integrity. If the storage engine is InnoDB, MySQL can impose some constraints between two related tables via related columns.
Think about two tables as employee and user where employee table’s id field is related to user table’s employee_id (That is each time you fill a row of user table, you fill the employee_id field corresponds to employee table).
Then you can impose a foreign key constraint to automatically delete relevant row from user table when corresponding record from employee table is deleted. You can define a foreign key constraint with table definition like below.
CREATE TABLE `user` ( `id` int NOT NULL AUTO_INCREMENT, `employee_id` int NOT NULL, `username` varchar(40) NOT NULL, `password` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`), FOREIGN KEY (`employee_id`) REFERENCES `employee` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
To add a foreign key constraint to an existing table, use ALTER TABLE command as below.
ALTER TABLE `user` ADD CONSTRAINT FOREIGN KEY (`employee_id`) REFERENCES `employee` (`id`) ON DELETE CASCADE;