Create Table in MySQL

MySQL, an esteemed open-source relational database management system, excels in storing, organizing, and retrieving data. This article focuses on the CREATE TABLE command, which is essential for forming new tables in a database. It involves specifying columns, data types, and constraints. Essential in building scalable web applications, MySQL's table structure defines data organisation, comprising rows and columns. We'll explore the syntax and parameters of the CREATE TABLE command, a fundamental aspect of table creation in MySQL.

MySQL CREATE TABLE Statement

The command CREATE TABLE in MySQL creates a new table with a specified name in the current database. The statement specifies the columns, their data types, and constraints (if any) for the table.

The following is a basic example of the CREATE TABLE statement in MySQL:

Query for Creating Students Table:

In the above example, we are creating a new table called students. The table has three columns: id, name, and age. The id column is defined as an integer with a maximum length of 11 digits, and it is set to auto-increment. The name column is defined as a variable-length string with a maximum length of 255 characters. The age column is defined as an integer with a maximum length of 11 digits. The PRIMARY KEY constraint is set on the id column, which ensures that each row in the table has a unique value for the id column.

Syntax

The syntax for the CREATE TABLE in MySQL is as follows:

Syntax:

In the syntax, table_name is the name of the table that you want to create. The column1_definition, column2_definition, and so on specify the columns of the table, along with their data types, sizes, and constraints. The table_constraints specify additional constraints on the table, such as the PRIMARY KEY constraint or the FOREIGN KEY constraint. Finally, the ENGINE parameter specifies the storage engine for the table.

Parameters

Let's take a closer look at the parameters of CREATE TABLE in MySQL:

• table_name: The name of the table you want to create. It should be unique within the database.
• column_definition: Defines a column in the table. It includes the name of the column, its data type, size, and constraints. You can specify multiple columns in a single CREATE TABLE statement.
• data_type: The data type of the column. MySQL supports a wide range of data types, including integers, strings, dates, and more.
• size: The maximum size of the column, in bytes or characters. The size parameter is optional for some data types.
• constraints: Constraints are used to enforce rules on the data in the table. Common constraints include PRIMARY KEY, FOREIGN KEY, NOT NULL, and UNIQUE.
• ENGINE: The storage engine for the table. MySQL supports multiple storage engines, including InnoDB, MyISAM, and Memory. The storage engine determines how the data is stored and retrieved from the table. Thus, the decision of selecting the appropriate storage engine is a crucial choice that will have an impact on future development.

The following are some of the commonly used data types and constraints in MySQL:

Data Types

• INT: An integer data type that can hold numbers.
• VARCHAR: A variable-length string data type that can hold a string of characters with a maximum length.
• TEXT: A data type that can hold large amounts of text data.
• DATE: A data type that can hold date values in the format YYYY-MM-DD.
• TIMESTAMP: A data type that can hold a timestamp value in the format YYYY-MM-DD HH:MM: SS.

Constraints

• PRIMARY KEY: A constraint that enforces a unique value for a column and allows it to be used as a primary key.
• FOREIGN KEY: A constraint that references a column in another table and enforces referential integrity between the tables.
• NOT NULL: A constraint that requires a column to have a value, and disallows NULL values.
• UNIQUE: A constraint that enforces a unique value for a column, but does not allow it to be used as a primary key.

CREATE TABLE using another MySQL TABLE

MySQL also allows you to create a new table based on an existing table. You can use the command CREATE TABLE in MySQL with the AS keyword to create a new table that is a copy of the existing table, with or without data.

The following syntax is used to create a table using another MySQL table:

Syntax:

In this syntax, we are creating a new table called new_table based on the existing table called existing_table. The SELECT * statement selects all the columns from the existing table, and the AS keyword creates a new table with the same columns and data types.

Let us look at a practical usage of this syntax. Let us first create two tables, customers and orders, and insert some sample data into them using the following queries:

Query to Create Customers Table:

Query to Insert Data into Customers Table:

Query to View Data Stored in the Customers Table:

Output:

Query to Create Orders Table:

Query to Insert Data into Orders Table:

Query to View Data Stored in the Orders Table:

Output:

Next, let's use the CREATE TABLE AS SELECT statement to create a new table called customer_orders, which combines data from both the customers and orders tables:

Query to Create customers_orders Table:

In this example, the SELECT statement is used to retrieve data from the customers and orders tables and combine it into a single result set. The JOIN clause is used to match customer IDs in the customer's table with customer IDs in the orders table. The resulting data is then stored in a new table called customer_orders.

Finally, let's view the data in the new customer_orders table:

Query to View Data in the customers_orders Table:

Output:

As you can see, the customer_orders table combines data from the customers and orders tables, providing a convenient way to view customer order information in a single table.

Modes of Creating MySQL TABLE

MySQL Command Line Client

The MySQL command-line client is a command-line interface for interacting with MySQL databases. It allows you to execute SQL statements, create tables, and manage databases.

To create a new table using the MySQL command-line client, follow these steps:

1. Open the MySQL command-line client and connect to your database server.
2. Select the database where you want to create the table using the USE statement.
3. Execute the CREATE TABLE statement with the appropriate syntax and parameters.

The following is an example of creating a table using the MySQL command-line client:

In the above example, we are creating a new table called mytable in the mydatabase database. The table has three columns: id, name, and age.

• The id column is defined as an integer with a maximum length of 11 digits and is set to auto-increment.
• The name column is defined as a variable-length string with a maximum length of 255 characters.
• The age column is defined as an integer with a maximum length of 11 digits.
• The PRIMARY KEY constraint is set on the id column, which ensures that each row in the table has a unique value for the id column.

MySQL Workbench

MySQL Workbench is a graphical user interface (GUI) tool for managing MySQL databases. It allows you to create, modify, and execute SQL statements, as well as design database schemas and visualize data.

To create a new table using MySQL Workbench, follow these steps:

1. Open MySQL Workbench and connect to your database server.
2. Click on the Schema tab in the left-hand pane, and select the database where you want to create the table.
3. Right-click on the database, and select Create Table... from the context menu.
4. Enter your table's column names, data types, and constraints in the table editor.
5. Click the Apply button to create the table.