MySQL is a widely used relational database management system. It is used by developers, database administrators, and data analysts to store and manage data. In this tutorial, we will discuss how to create a table in MySQL using the CREATE TABLE statement.
The CREATE TABLE statement is used to create a new table in a database. It consists of the following parts:
- Table name: This is the name of the table you want to create.
- Column names and data types: This defines the columns in the table and their data types.
- Constraints: These are rules you can apply to the columns in the table to ensure data integrity.
create table mySql syntax
1 2 3 4 5 | CREATE TABLE table_name ( column1 datatype constraint, column2 datatype constraint, ... ); |
Here’s what each of the parts of this statement mean:
CREATE TABLE
is the statement used to create a new table.table_name
is the name of the table you want to create.column1
,column2
, etc. are the names of the columns in the table.datatype
is the data type of each column, such as INT, VARCHAR, DATE, etc.constraint
is an optional constraint that you can add to a column, such as NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, etc.
For example,
1 2 3 | create table student(id int, first_name varchar(30),last_name varchar(30), semester varchar(20),address varchar(80)); |
if the table is created without any error then it will show the following statement
Above MySQL create statement create a student table with primary key id,
with auto-increment field value and two unique keys mobile_no and email_id,
at the time of creating the user add_date is added CURRENT_TIMESTAMP as the default value.
This table also uses mysql create table auto_increment
Result:
1 | Query OK, 0 rows affected (1.44 sec) |
This query will result in a table with the above fields. The first column will hold numbers while the other columns are types of varchar.
Here is an another example of a basic CREATE TABLE statement:
1 2 3 4 5 6 7 | CREATE TABLE `employees` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(50) NOT NULL, `age` INT(11) NOT NULL, `salary` DECIMAL(10,2) NOT NULL, PRIMARY KEY (`id`) ); |
In this example, we are creating a new table called “employees”. The table has four columns:
- “id”: This is an integer column with a maximum length of 11 digits. It is set to auto-increment, which means that MySQL will automatically assign a unique value to this column for each new row that is inserted.
- “name”: This is a varchar column with a maximum length of 50 characters. It cannot be null (i.e., it must contain a value).
- “age”: This is an integer column with a maximum length of 11 digits. It cannot be null.
- “salary”: This is a decimal column with a precision of 10 digits and a scale of 2 digits. It cannot be null.
The final line of the statement specifies that the “id” column is the primary key of the table.
Here are some additional tips and examples to help you create tables in MySQL:
- Use backticks (
) around table and column names to avoid conflicts with reserved words. For example,
name` is a better column name than name. - Use data types that are appropriate for your data. For example, use INT for integer values, VARCHAR for character strings, and DECIMAL for numbers with decimal places.
- Add constraints to your columns to ensure data integrity. For example, you can use the UNIQUE constraint to ensure that a column contains only unique values.
- Specify default values for columns if appropriate. For example, you can set a default value for a column to be used when no value is specified during insert.
- Use the COMMENT clause to add comments to your table and column definitions for documentation purposes.
Here is another example of a CREATE TABLE statement that includes some of these tips:
1 2 3 4 5 6 7 8 9 10 11 | CREATE TABLE `customers` ( `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Customer ID', `first_name` VARCHAR(50) NOT NULL COMMENT 'First name of customer', `last_name` VARCHAR(50) NOT NULL COMMENT 'Last name of customer', `email` VARCHAR(100) NOT NULL COMMENT 'Email address of customer', `phone` VARCHAR(20) DEFAULT NULL COMMENT 'Phone number of customer', `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Date and time the record was created', `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Date and time the record was last updated', PRIMARY KEY (`id`), UNIQUE KEY `email` (`email`) ); |
Create Table in Mysql with primary key
1 2 3 4 5 6 7 8 9 10 11 12 13 | create table student( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, fname varchar(100), lname varchar(30), address varchar(80), mobile_no varchar(12), email_id varchar(100), city varchar(50), add_date timestamp DEFAULT CURRENT_TIMESTAMP, primary key(id), unique key(mobile_no), unique key(email_id) )ENGINE=InnoDB DEFAULT CHARSET=utf8; |
MySql create table if not exists
Syntax of create table if not exist isCREATE TABLE IF NOT EXISTS table_name(............);
To modity the student table with CREATE TABLE IF NOT EXISTS the query is as below
1 2 3 4 5 6 7 8 9 10 11 12 13 | CREATE TABLE IF NOT EXISTS student( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, fname varchar(100), lname varchar(30), address varchar(80), mobile_no varchar(12), email_id varchar(100), city varchar(50), add_date timestamp DEFAULT CURRENT_TIMESTAMP, primary key(id), unique key(mobile_no), unique key(email_id) )ENGINE=InnoDB DEFAULT CHARSET=utf8 |
Create table … Like statement with syntax and examples
Syntax
1 2 | CREATE TABLE new_table_name LIKE existing_table_name; |
Suppose you have an existing table called users
that has the following structure:
1 2 3 4 5 | CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), age INT ); |
If you want to create a new table called customers
that has the same structure as the users
table, you can use the CREATE TABLE … LIKE statement like this:
1 2 | CREATE TABLE customers LIKE users; |
The new table, however, does not contain any data from the users
table.
Additional columns or data
1 2 3 4 5 6 7 8 | CREATE TABLE customers LIKE users COMMENT 'Customers table' ENGINE=InnoDB ROW_FORMAT=DYNAMIC INDEX (name) ALTER COLUMN age SET DEFAULT 18 RENAME COLUMN name TO customer_name; |
CREATE TABLE … SELECT Statement
The CREATE TABLE ... SELECT
statement in MySQL allows you to create a new table based on the results of a SELECT
statement.
1 2 3 4 5 | CREATE TABLE new_table_name AS SELECT column1, column2, ... FROM existing_table_name WHERE condition; |
Suppose you have an existing table called orders
that has the following structure:
1 2 3 4 5 6 | CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_name VARCHAR(50), order_date DATE, order_amount DECIMAL(10,2) ); |
If you want to create a new table called big_orders
that contains only the orders with an order amount greater than 1000, you can use the CREATE TABLE ... SELECT
a statement like this:
1 2 3 4 5 | CREATE TABLE big_orders AS SELECT * FROM orders WHERE order_amount > 1000; |
You can also specify the columns you want to include in the new table instead of using the wildcard *
1 2 3 4 5 | CREATE TABLE big_orders AS SELECT order_id, customer_name, order_amount FROM orders WHERE order_amount > 1000; |
Create table with auto increment
1 2 3 4 5 6 | CREATE TABLE table_name ( column1 datatype AUTO_INCREMENT PRIMARY KEY, column2 datatype, column3 datatype, ... )AUTO_INCREMENT = starting_value; |
1 2 3 4 5 6 7 | CREATE TABLE users ( user_id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50), password VARCHAR(50), email VARCHAR(50), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); |
1 2 3 4 5 6 7 | CREATE TABLE users ( user_id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50), password VARCHAR(50), email VARCHAR(50), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) AUTO_INCREMENT = 1001; |
1 |
how To create a table with a FOREIGN KEY
constraint
Syntax
1 2 3 4 5 6 7 | CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, ... FOREIGN KEY (column_name) REFERENCES referenced_table_name (referenced_column_name) ); |
1 2 3 4 5 6 7 8 9 10 11 12 13 | CREATE TABLE customer( customer_id INT PRIMARY KEY, customer_name VARCHAR(50), customer_address VARCHAR(50), customer_contact VARCHAR(50), ); CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, order_date DATE, order_amount DECIMAL(10,2), FOREIGN KEY (customer_id) REFERENCES customers (customer_id) ); |
You can also create a foreign key constraint that references a composite key, which is a primary key made up of multiple columns. Here’s an example of creating a table order_items
with a foreign key constraint that references a composite key in the orders
table:
1 2 3 4 5 6 7 8 | CREATE TABLE order_items ( order_id INT, item_id INT, item_name VARCHAR(50), item_quantity INT, PRIMARY KEY (order_id, item_id), FOREIGN KEY (order_id) REFERENCES orders (order_id) ); |
Create Temporary Table Statement in MySql
create a temporary table that exists only for the duration of the current session. Once the session ends, the temporary table is automatically dropped and its data is lost.
The syntax for the Create Temporary Table statement:
1 2 3 4 5 | CREATE TEMPORARY TABLE temp_table_name ( column1 datatype constraint, column2 datatype constraint, ... ); |
Here’s what each of the parts of this statement mean:
CREATE TEMPORARY TABLE
is the statement used to create a new temporary table.temp_table_name
is the name of the temporary table you want to create.column1
,column2
, etc. are the names of the columns in the table.datatype
is the data type of each column, such as INT, VARCHAR, DATE, etc.constraint
is an optional constraint that you can add to a column, such as NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, etc.
1 2 3 4 5 | CREATE TEMPORARY TABLE temp_users ( id INT PRIMARY KEY, name VARCHAR(50) ); INSERT INTO temp_users (id, name) VALUES (1, 'John'), (2, 'Jane'), (3, 'Bob'); |
mySql create table current_timestamp
Person table in MySql
1 2 3 4 5 6 7 8 9 10 11 12 13 | CREATE TABLE person ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), mobile_no VARCHAR(20), aadhar_no VARCHAR(20), email_id VARCHAR(50), PAN VARCHAR(20), driving_licence VARCHAR(20), gender ENUM('Male', 'Female', 'Other'), dob DATE, add_date TIMESTAMP, update_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); |
Student table in MySql
1 2 3 4 5 6 7 8 9 10 11 | CREATE TABLE student ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL, email VARCHAR(50) NOT NULL UNIQUE, phone VARCHAR(15), address VARCHAR(100), dob DATE, gender ENUM('Male', 'Female', 'Other'), registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); |
Reference