MYSQL Table

What is the table in MYSQL? How to create the table in MYSQL and what are the commands to create the table in MySQL.

What is MYSQL Table

MySql Table is used to create a table, with names, rows, and columns. For creating any table name of the table, the name of the field, and also the definition for each table should be fixed before.

How to create a table in MYSQL

Syntax:
CREATE TABLE table_name (column_name column_type);

Example:

CREATE TABLE Emp_tbl(
emp_id INT NOT NULL AUTO_INCREMENT,
emp_Firstname VARCHAR(100) NOT NULL,
emp_Surname VARCHAR(100) NOT NULL,
PRIMARY KEY (emp_id)
);

With the above query, any user can create an employee table, where in the first line employee id is displayed which cannot be null.

If you are trying to create any table with a null value, it will display you the error message, that can’t create the table. Autoincrement, is used in the above query, to instruct the system to use the next available Id.

Things to remember in MYSQL table

  • NOT NULL is used with field attributes because we do not want this field should be NULL.
  • AUTO_INCREMENT is used to tell to go ahead and add the next available number to the ID field.
  • PRIMARY KEY is used to assign a column as a primary key. We can use many columns separated by comma to define a primary key.

What is Alter in the MYSQL table

Alter is an MYSQL command. That is used to alter or modify the table. Like, you can add the column, drop a column, add multiple columns, rename column, rename table.

In short, you can use MySql alter table for rename, delete or modify table or ay fields in table. Alter statements can always be applied with ADD, DROP, Or MODIFY commands.

Syntax: 
ALTER TABLE Table_name;

ADD/DROP/MODIFY column_name column_definition
[FIRST ! AFTER column_name];

With the above syntax, you can use add, drop, or modify commands with alter statement if you want to add any column, add more than one column in the table, rename any column, or rename the table, drop column in the table, modify a column in the table.

Example:

ALTER TABLE Emp_tbl
DROP emp_Surname;

How to change the database table in MYSQL

RENAME command is used to change the table in MySQL. below is the syntax and example to rename the existing table in MYSQL.

Syntax:

ALTER TABLE table_name
RENAME TO new_table_name;
Example:-
ALTER TABLE emp_tbl
RENAME TO EMPLOYEE_table;

What is Truncate in MySql

It is used in the database when you want to remove or delete the complete data without disturbing the structure.
Syntax:-
TRUNCATE TABLE table_name;
Example:

TRUNCATE TABLE EMPLOYEE_table;

How to Drop a table in MYSQL

MySql drop command can be used in the database when you want to delete the table and its structure.
Syntax:-
DROP TABLE table_name;
Example:-

DROP TABLE EMPLOYEE_table;

PHP Script to create the table

<html>
<head>
<title>Creating MySQL Tables</title>
</head>
<body>
<?php
$dbhost = 'localhost'; // modify as per you host name
$dbuser = 'root'; // modify as per user name
$dbpass = ' ' ''; // modify as per your password
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully<br />';
$sql = "CREATE TABLE emp (".
"emp_id INT NOT NULL, ".
"emp_Firstname VARCHAR (100), ".
"emp_Surname VARCHAR(100), ".
"PRIMARY KEY (emp_id)); ";
mysql_select_db( 'STUDENT' ); // modify as per your database name
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
die('Could not create table: ' . mysql_error());
}
echo "Table created successfully\n";
mysql_close($conn);
?>
</body>
</html>