Syntax SQL CREATE TABLE Statement
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( columns )
);
Example(1)
CREATE TABLE Employee(
EmployeeID INT NOT NULL,
FirstName VARCHAR (20) NOT NULL,
LastName VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS VARCHAR (20) NOT NULL,
SALARY DECIMAL (18, 2),
PRIMARY KEY (EmployeeID))
SQL CREATE TABLE Statement creates a table called "Employee" in the management database that contains different datatype columns:
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
EmployeeID | int(11) | NO | PRI | NULL | |
FirstName | varchar(20) | NO | NULL | ||
LastName | varchar(20) | NO | NULL | ||
AGE | int(11) | NO | NULL | ||
ADDRESS | varchar(20) | NO | NULL | ||
SALARY | decimal(18,2) | YES | NULL |
DESC Employee;
We can also copy data from another table using CREATE TABLE.
Syntax for coping data from another table using CREATE TABLE
CREATE TABLE new_tablename AS
SELECT column list
FROM existing_tablename
WHERE ....;
CREATE TABLE Employee_details AS
SELECT FirstName, ADDRESS
FROM Employee;
SQL statement creates a new table called "Employee_details " for this table data copied from Employee
<!DOCTYPE html>
<html lang="en">
<head>
<title>How to use SQL CREATE TABLE Statement</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<br>
<br>
<div class="text-center">
<h1>SQL CREATE TABLE Statement</h1>
</div>
<br>
<div class="well">
<?php
$connect = mysqli_connect("localhost", "root", "", "management");
if($connect === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}
$sql_1 = "CREATE TABLE Employee(
EmployeeID INT NOT NULL,
FirstName VARCHAR (20) NOT NULL,
LastName VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS VARCHAR (20) NOT NULL,
SALARY DECIMAL (18, 2),
PRIMARY KEY (EmployeeID))";
$sql_2="CREATE TABLE Employee_details AS SELECT FirstName, ADDRESS FROM Employee;"
if(mysqli_query($connect, $sql_1)){
echo "<br><h1 class='text-success'>Employee table created successfully.</h1>";
} else{
echo "ERROR: Could not able to execute $sql_1. " . mysqli_error($connect);
}
mysqli_close($connect);
?>
</div>
<br>
</div>
</body>
</html>