How to create a table in MySql

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:

  1. Table name: This is the name of the table you want to create.
  2. Column names and data types: This defines the columns in the table and their data types.
  3. Constraints: These are rules you can apply to the columns in the table to ensure data integrity.

create table mySql syntax

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,

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:

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:

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:

  1. Use backticks () around table and column names to avoid conflicts with reserved words. For example, name` is a better column name than name.
  2. 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.
  3. 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.
  4. 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.
  5. 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:

Create Table in Mysql with primary key

MySql create table if not exists

Syntax of create table if not exist is
CREATE TABLE IF NOT EXISTS table_name(............);

To modity the student table with CREATE TABLE IF NOT EXISTS the query is as below

Create table … Like statement with syntax and examples

Syntax

Suppose you have an existing table called users that has the following structure:

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:

The new table, however, does not contain any data from the users table.

Additional columns or data

 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.

Suppose you have an existing table called orders that has the following structure:

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:

You can also specify the columns you want to include in the new table instead of using the wildcard *

Create table with auto increment

how To create a table with a FOREIGN KEY constraint

Syntax

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:

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:

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.

mySql create table current_timestamp

Person table in MySql

Student table in MySql

Reference

MySql