In this tutorial you will learn about the MySQL Create Tables and its application with practical example.
MySQL Create table
MySQL CREATE TABLE is used to create a table within a database.The table creation command requires:
- Name of the table
- Names of fields
- Definitions for each field
MySQL storage engine represents each table by following files
| File | Purpose |
|---|---|
| table_name.frm | Table format (definition) file. |
| table_name.MYD | Data file. |
| table_name.MYI | Index file. |
The CREATE TABLE statement Syntax.
|
1 2 3 4 5 6 7 |
CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, column_name3 data_type, .... ) |
Example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
<?php $con = mysql_connect("localhost","root",""); if (mysql_query("CREATE DATABASE employee",$con)) { echo "Database created"; } else { echo "Error creating database: " . mysql_error(); } $sql = "CREATE TABLE `employee`.`emp_info` ( `empID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `empName` VARCHAR( 50 ) NOT NULL , `empCode` VARCHAR( 50 ) NOT NULL , `empSalary` VARCHAR( 10 ) NOT NULL , `empDept` VARCHAR( 50 ) NOT NULL ) ENGINE = MYISAM ;"; mysql_query($sql,$con);?> |
Here few items need explanation:
- Constraint NOT NULL is used because we do not want this field to be NULL. if we try to create a entry with NULL value then MySQL Engine will through an error.
- AUTO_INCREMENT tells to MySQL Insert new record with next available incremented value.
- Constraint PRIMARY KEY is used to define a column as primary key. Multiple columns need to be separated by comma to define a primary key.
- The UNIQUE constraint used to uniquely identify each record in a table.The UNIQUE and PRIMARY KEY constraints both assures for uniqueness for a field or set of fields. A PRIMARY KEY constraint by default has a UNIQUE constraint defined for it.We can have many UNIQUE constraints for a table, but only one PRIMARY KEY per table.
The statement above will create ’emp_info’ table which have columns like following
| Column Name | Data Type | Size |
|---|---|---|
| empID | Int | |
| empName | Varchar | 50 |
| empCode | Varchar | 50 |
| empSalary | Varchar | 50 |
| empDept | Varchar | 50 |
