In the previous tutorial, we learned about how to create a database through a PHP application. Now we will get to the next step, we will look at the MySQL tutorial to create tables in a database through a PHP application. Let's see first, what is a table.
In a relational database, a table is a collection of data elements that store data in the form of
The place where a row and a column intersects is called a
cell. We will now look at the procedure and the SQL query to create a table in an SQL database and will also analyze the Query and its content. After that we will look at the different ways to implement the table creation Query, using different PHP Database extensions.
The SQL query is a bit complex than the database one but, it is not very difficult.
In the query, you should know all the
data types used in the MySQL version for a particular type of data because it is necessary to specify data types of the columns. The data types indicate the type of data to be entered in the column. For example, A
Name column will have
CHAR data type,
date column will have a
DATE data type, etc. Other than that, the maximum size of the data to be entered is also specified in the query.
After these data types, if you want to add some
CONSTRAINTS to the columns, then you can specify
those too. Constraints are some conditions which the data has to follow to get accepted in the table.
For example: If the constraint
NOT NULL is applied to a column then that column should not be left empty, otherwise, the record will not be stored.
'CREATE TABLE' statement is used to create a table in a database.
Syntax: CREATE TABLE table_name ( column1_name data_type constraints, column2_name data_type constraints, .... );
Let's use this syntax in an example, then you will understand it clearly. Observe the example yourself, and then we will also explain to you all the constraints and data types used in the SQL query:
EXPLANATION: The SQL Query given above has some constraints and datatypes.
This query creates a table named
persons. This table will have the columns
After naming the columns, the size of the data to be stored is specified, like you can see
name VARCHAR(50) and
phone VARCHAR(15) indicates the maximum characters the column can hold.
birth_date don't have any size specified because these won't require more than the default size of the data types.
You will learn more about it in the SQL tutorial. Let's see some keywords and constraints used in this example.
|INT||The INTEGER data type accepts numeric values. It stores any integer value between -2147483648 to 2147483647.|
|DECIMAL||It stores decimal values with exact precision i.e. with decimals included.|
|CHAR||It stores fixed-length strings of alphabetic characters with a maximum size of 8000 characters.|
|VARCHAR||It holds a variable length string that can contain alphabets, numbers, and special characters with a maximum size of 8000 characters.|
|TEXT||It Stores strings with a maximum size of 2GB data.|
|DATE||It stores date values in the YYYY-MM-DD format.|
|DATETIME||It stores combined date/time values in YYYY-MM-DD HH:MM:SS format.|
|PRIMARY KEY||This constraint sets the column as the primary key of the table.|
|NOT NULL||It indicates that the value stored in this column can never be NULL.|
|UNIQUE||This constraint indicates that all the values will be distinct from each other.|
|AUTO_INCREMENT||It indicates that the values will be incremented by one with each record entered. It is used with numeric values specially with primary keys.|
Now we will see the implementation of SQL table creation query with each extension of MySQL, i.e.,