Many tables are created in a MySQL database.CREATE TABLE table_name This statement is used to create a table in the database.
Here the table named ‘Course’ is taken and three fields id, course_name and course_year are taken in it.
Example or Creating Table Structure
CREATE TABLE Course( id INT(2) AUTO_INCREMENT PRIMARY KEY, course_name VARCHAR(150) NOT NULL, course_year VARCHAR(50) NOT NULL )
Here INT (11), VARCHAR (150) and VARCHAR (50) are the data types of MySQL.
Below are all the data types of MySQL.
Data Types for MySQL
The data types in MySQL are divided into three types.
1.Numeric data types
2.Date and Time Data Types
3.String data types
Few Data Types are used in MySQL.
INT:- Here the value of integer is signed (negative) or unsigned (positive). Here the length of INT is from 1 to 11 digits. When no length is given for INT, it gets ’11’ this default length. For eg. INT (11)
VARCHAR:- The length of characters in varchar ranges from 1 to 255. For eg. VARCHAR (250)
TEXT:- The length of characters in text is up to 54435. If the length is not given, it is at the default there.
MEDIUMTEXT:- characters in text have a length of 15666415. If the length is not given, it is at the default there.
LONGTEXT:- characters in text have a length of 5683409843. If the length is not given, it is at the default there.
DATE:- Here the date ‘YYYY-MM-DD’ is given in this format. It can be dated here from 1000-01-01 to 9999-12-30. For eg. 2080-10-11
When data types and their length are given on columns, then they are also given attributes.
AUTO_INCREMENT:- Here value is increased by 1-1 every once.
NOT NULL:- null value is not allowed here. There should be some value on every record.
UNSIGNED:- Here only positive value and zero are allowed.
DEFAULT default_value:- Default value can be set here. For eg. DEFAULT 0
PRIMARY KEY:- Here a value cannot be assigned again. Here AUTO_INCREMENT does not create duplicate value from this attribute.
Example for Creating Table using MySQLi
<?php
$server = "localhost";
$user = "root";
$password = "";
$db = "expertstutorials";
$conn = mysqli_connect($server, $user, $password, $db);
if($conn){
echo "Connected successfully.";
}
else{
echo "Connection failed : ".mysqli_connect_error();
}
$table_create = "CREATE TABLE course(
id INT(2) AUTO_INCREMENT PRIMARY KEY,
course_name VARCHAR(50) NOT NULL,
course_year VARCHAR(50) NOT NULL
)";
if (mysqli_query($conn, $table_create)) {
echo "Table Created successfully.";
} else {
echo "Table Creation failed : " . mysqli_error($conn);
}
mysqli_close($conn);
?>
Example for Creating Table using PDO
<?php
$server = 'localhost';
$user = 'root';
$password = '';
$db = 'expertstutorials';
try{
$conn = new PDO("mysql:host=$server;dbname=$db", $user, $password);
echo "Connected successfully.";
$table_create = "CREATE TABLE course(
id INT(2) AUTO_INCREMENT PRIMARY KEY,
course_name VARCHAR(50) NOT NULL,
course_year VARCHAR(50) NOT NULL
)";
$conn->exec($table_create);
echo "Table Created successfully.";
}
catch(PDOException $e){
echo "Table Creation failed : " . $e->getMessage();
}
$conn = null;
?>