The CREATE TABLE statement is used to create a new table in a database.
In the previous chapter we have learned how to create a database on the database server. Now it's time to create some tables inside our database that will actually hold the data. A database table simply organizes the information into rows and columns.
The column parameters specify the names of the columns of the table.
The datatype parameter specifies the type of data the column can hold (e.g. varchar, integer, date, etc.).
The following example creates a table called "Persons" that contains five columns: PersonID, LastName, FirstName, Address, and City:
The PersonID column is of type int and will hold an integer.
The LastName, FirstName, Address, and City columns are of type varchar and will hold characters, and the maximum length for these fields is 255
Please check out the reference section SQL DB data types for the detailed information on all the data types available in popular RDBMS like MySQL, SQL Server, etc.
There are a few additional constraints (also called modifiers) that are set for the table columns in the preceding statement. Constraints define rules regarding the values allowed in columns.
NOT NULLconstraint ensures that the field cannot accept a
PRIMARY KEYconstraint marks the corresponding field as the table's primary key.
AUTO_INCREMENTattribute is a MySQL extension to standard SQL, which tells MySQL to automatically assign a value to this field if it is left unspecified, by incrementing the previous value by 1. Only available for numeric fields.
UNIQUEconstraint ensures that each row for a column must have a unique value.