Quick Links
MySQL Create Table : A table is used to organize data in the form of rows and columns and used for both storing and displaying records in the structure format. It is similar to worksheets in the spreadsheet application. A table creation command requires three things:
- Name of the table
- Name of the fields
- Definitions for each field
Syntax of MySQL Create Table:
CREATE TABLE table_name (column_name column_type);Now, we will create the following table in the Edusite database.
create table edusite_tbl(
tutorial_id INT NOT NULL AUTO_INCREMENT,
tutorial_title VARCHAR(100) NOT NULL,
tutorial_author VARCHAR(40) NOT NULL,
submission_date DATE,
PRIMARY KEY ( edusite_id )
);
Here, a few items need explanation −
- Field Attribute NOT NULL
- Field Attribute AUTO_INCREMENT
- Keyword PRIMARY KEY
Creating Tables from Command Prompt
It is easy to create a table from the my sql> prompt. You will use the SQL command CREATE TABLE to create a table.
Example
Here is an example, which will create tutorials_tbl −
root@host# mysql -u root -p
Enter password:*******
mysql> use Edusite;
Database changed
mysql> CREATE TABLE edusite_tbl(
-> edusite_id INT NOT NULL AUTO_INCREMENT,
-> edusite_title VARCHAR(100) NOT NULL,
-> edusite_author VARCHAR(40) NOT NULL,
-> submission_date DATE,
-> PRIMARY KEY ( edusite_id )
-> );
Query OK, 0 rows affected (0.16 sec)
mysql>
MySQL Create Table Using PHP Script
To create new table in any existing database you would need to use PHP function mysql_query(). You will pass its second argument with a proper SQL command to create a table.
Example
The following program is an example to create a table using PHP script −
<html>
<head>
<title>Creating MySQL Tables</title>
</head>
<body>
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully<br />';
$sql = "CREATE TABLE edusites_tbl( ".
"edusite_id INT NOT NULL AUTO_INCREMENT, ".
"edusite_title VARCHAR(100) NOT NULL, ".
"edusite_author VARCHAR(40) NOT NULL, ".
"submission_date DATE, ".
"PRIMARY KEY ( edusite_id )); ";
mysql_select_db( 'EDUSITES' );
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die('Could not create table: ' . mysql_error());
</body>
</html>