MySQL – Create Tables
Table of content
- MySQL Create Table Statement
- Creating Tables from Command Prompt
- Creating a Table from an Existing Table
- The IF NOT EXISTS clause
- Create table into MySQL Database Using a Client Program
”;
In the MySQL relational database system, SQL is used to store data in the form of structured tables. These tables consist of fields and records. A field represents a column that defines the type of data to be stored in a table, and a record is a row containing the actual data. MySQL provides various queries to interact with the data, allowing you to create tables, update them, delete them, etc.
MySQL Create Table Statement
To create a table in MySQL RDBMS in prompt, CREATE TABLE statement is used.
One can create any number of tables in an SQL Server database. However, a limit exists on the number of objects that can be present in a database. Including tables, views, indexes etc., a database cannot exceed 2,147,483,647 objects. Therefore, a single user-defined table can define a maximum of 1024 columns.
A MySQL query to create a table must define the structure of a table. The structure consists of the name of a table and names of columns in the table with each column”s data type. Note that each table must be uniquely named in a database.
To begin with, the table creation command requires the following details −
- Name of the table.
- Name of the columns.
- Definitions for each column.
Syntax
Following is the basic SQL syntax to create a MySQL table −
CREATE TABLE table_name( column1 datatype, column2 datatype, ..... columnN datatype, PRIMARY KEY( one or more columns ) );
Example
In the following query, we are creating a table named CUSTOMERS using the CREATE TABLE Statement −
CREATE TABLE CUSTOMERS ( ID INT AUTO_INCREMENT, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
Here, a few items need explanation −
-
Field Attribute AUTO_INCREMENT in MySQL automatically increments the value in the ID column by one for each new record you add. It starts from the next available number.
-
Field Attribute NOT NULL is being used because we do not want this field to be NULL. So, if a user tries to create a record with a NULL value in that field, then MySQL will raise an error.
-
Keyword PRIMARY KEY is used to define a column as a primary key. It ensures that every record in that column is unique. You can also use it for multiple columns by separating them with commas.
Output
When we execute the above query, the output is obtained as follows −
Query OK, 0 rows affected (0.03 sec)
Verification
Once we have finished creating the table, we can check whether it has been created successfully or not using the following query −
DESC CUSTOMERS;
The above query displays the structure of the CUSTOMERS table: column names, their datatypes, etc.
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
ID | int | NO | PRI | NULL | auto_increment |
NAME | varchar(20) | NO | NULL | ||
AGE | int | NO | NULL | ||
ADDRESS | char(25) | YES | NULL | ||
SALARY | decimal(18,2) | YES | NULL |
Creating Tables from Command Prompt
We can create a MySQL table from the command prompt by defining its structure and columns.
Following are the steps to perform to create a MySQL table from Command Prompt:
-
Firstly, open the command prompt and enter the following command: mysql -u root -p to access the MySQL database management system.
-
After entering the command, enter the password to log into the MySQL server.
-
Then, we can start creating a table using the respected SQL CREATE TABLE query.
Example
In the following example, we are creating a MySQL table named CUSTOMERS from command prompt.
CREATE TABLE CUSTOMERS ( NAME VARCHAR(20) NOT NULL, ID INT AUTO_INCREMENT, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
NOTE − MySQL does not terminate a command until you give a semicolon (;) at the end of SQL command.
Output
When we execute the above query, the output is obtained as follows −
Query OK, 0 rows affected (0.03 sec)
Verification
We can verify if the table has been created successfully or not using the following query −
mysql> DESC CUSTOMERS;
The above query will show the structure and description of the CUSTOMERS table −
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
ID | int | NO | PRI | NULL | auto_increment |
NAME | varchar(20) | NO | NULL | ||
AGE | int | NO | NULL | ||
ADDRESS | char(25) | YES | NULL | ||
SALARY | decimal(18,2) | YES | NULL |
Creating a Table from an Existing Table
We can create a duplicate table of an existing table including its structure and data, by using the SQL CREATE TABLE and SELECT statements. The duplicate table has the same columns and their definitions, and it also gets filled with the same data as the original table.
Note − As it is a completely new table, any changes made in it would not be reflected in the original table.
Syntax
Following is the syntax for creating a table from another table −
CREATE TABLE NEW_TABLE_NAME AS SELECT [column1, column2...columnN] FROM EXISTING_TABLE_NAME [WHERE CONDITION];
Here, column1, column2… are the fields of the existing table and the same would be used to create fields of the new table. And the WHERE clause is optional to use.
Example
Let us consider an existing table CUSTOMERS in the TUTORIALS database −
mysql> USE TUTORIALS; Database changed mysql> SELECT * FROM CUSTOMERS;
Following is the CUSTOMERS table −
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Ramesh | 32 | Ahmedabad | 2000.00 |
2 | Khilan | 25 | Delhi | 1500.00 |
3 | kaushik | 23 | Kota | 2000.00 |
4 | Chaitali | 25 | Mumbai | 6500.00 |
5 | Hardik | 27 | Bhopal | 8500.00 |
6 | Komal | 22 | Hyderabad | 4500.00 |
7 | Muffy | 24 | Indore | 10000.00 |
Now, Using the following query, we are creating a new table named SAMPLE with the same structure and records as CUSTOMERS.
CREATE TABLE SAMPLE AS SELECT * FROM CUSTOMERS;
Output
As we can see in the output, the table SAMPLE is created successfully.
Query OK, 7 rows affected (0.03 sec) Records: 7 Duplicates: 0 Warnings: 0
Verification
Using the following SELECT statement, let us verify if the new table SAMPLE contains the records or not.
SELECT * FROM SAMPLE;
As we can in the output below, the SAMPLE table has been created with all the records from the CUSTOMERS table. −
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Ramesh | 32 | Ahmedabad | 2000.00 |
2 | Khilan | 25 | Delhi | 1500.00 |
3 | kaushik | 23 | Kota | 2000.00 |
4 | Chaitali | 25 | Mumbai | 6500.00 |
5 | Hardik | 27 | Bhopal | 8500.00 |
6 | Komal | 22 | Hyderabad | 4500.00 |
7 | Muffy | 24 | Indore | 10000.00 |
The IF NOT EXISTS clause
If you try to create a table with an existing name an error will be generated −
CREATE TABLE Employee(Name VARCHAR(255)); ERROR 1050 (42S01): Table ''employee'' already exists
If you use the IF NOT EXISTS clause along with the CREATE statement as shown below a new table will be created and if a table with the given name, already exists the query will be ignored.
CREATE TABLE Test(Name VARCHAR(255)); Query OK, 0 rows affected (0.69 sec)
Create table into MySQL Database Using a Client Program
In addition to Create a table into MySQL Database using the MySQL query, we can also perform the CREATE TABLE operation using a client program.
Syntax
Following are the syntaxes to Create a table in various programming languages −
To Create a table in MySQL Database through a PHP program, we need to execute the CREATE TABLE statement using the mysqli function query() as −
$sql = "CREATE TABLE table_name (column_name column_type)"; $mysqli->query($sql);
To Create a table in MySQL Database through a Node.js program, we need to execute the CREATE TABLE statement using the query() function of the mysql2 library as −
sql = "CREATE TABLE table_name (column_name column_type)"; con.query(sql);
To Create a table in MySQL Database through a Java program, we need to execute the CREATE TABLE statement using the JDBC function executeUpdate() as −
String sql = "CREATE TABLE table_name (column_name column_type)"; statement.execute(sql);
To Create a table in MySQL Database through a Pyhton program, we need to execute the CREATE TABLE statement using the execute() function of the MySQL Connector/Python as −
sql = "CREATE TABLE table_name (column_name column_type)"; cursorObj.execute(sql);
Example
Following are the programs −
$dbhost = ''localhost''; $dbuser = ''root''; $dbpass = ''root@123''; $dbname = ''TUTORIALS''; $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname); if($mysqli->connect_errno ) { printf("Connect failed: %s<br />", $mysqli->connect_error); exit(); } printf(''Connected successfully.<br />''); $sql = "CREATE TABLE tutorials_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 ( tutorial_id )); "; if ($mysqli->query($sql)) { printf("Table tutorials_tbl created successfully.<br />"); } if ($mysqli->errno) { printf("Could not create table: %s<br />", $mysqli->error); } $mysqli->close();
Output
The output obtained is as follows −
Connected successfully. Table tutorials_tbl created successfully.
var mysql = require(''mysql2''); var con = mysql.createConnection({ host: "localhost", user: "root", password: "*****" }); //Connecting to MySQL con.connect(function (err) { if (err) throw err; console.log("Connected!"); console.log("--------------------------"); //Creating a table sql = "CREATE TABLE tutorials_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 ( tutorial_id ));" con.query(sql, function (err, result) { if (err) throw err; console.log("The table tutorials_tbl is created successfully!"); }); });
Output
The output produced is as follows −
Connected! -------------------------- The table tutorials_tbl is created successfully!
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class CreateTable { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/TUTORIALS"; String username = "root"; String password = "password"; try { Class.forName("com.mysql.cj.jdbc.Driver"); Connection connection = DriverManager.getConnection(url, username, password); Statement statement = connection.createStatement(); System.out.println("Connected successfully...!"); //Create new table...! String sql = "CREATE TABLE tutorials_tbl (ID Int Auto_Increment not null, tutorial_title Varchar(50) Not Null, tutorial_author Varchar(30) Not Null, Primary Key(ID))"; statement.executeUpdate(sql); System.out.println("Table created successfully...!"); ResultSet resultSet = statement.executeQuery("DESCRIBE tutorials_tbl"); while (resultSet.next()) { System.out.print(resultSet.getNString(1)); System.out.println(); } connection.close(); } catch (Exception e) { System.out.println(e); } } }
Output
The output obtained is as shown below −
Connected successfully...! Table created successfully...! ID tutorial_title tutorial_author
import mysql.connector #establishing the connection conn = mysql.connector.connect( user=''root'', password=''password'', host=''localhost'', database=''tut'' ) #Creating a cursor object cursor = conn.cursor() #Creating a table sql =''''''CREATE TABLE tutorials_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 ( tutorial_id ) )'''''' cursor.execute(sql) print ("The table tutorials_tbl is created successfully!") #Closing the connection conn.close()
Output
Following is the output of the above code −
The table tutorials_tbl is created successfully!
”;