MySQL – Create Table
MySQL is a well-known free and open-source database application. In addition to its high performance and ease of use, it also provides data security.
Creating tables is one of the most significant processes in MySQL.
This guide will explain how to create a table in MySQL, insert data, and query the table.
Prerequisites
- Access to a terminal window or command line
- Using MySQL on a system (learn how to check the version of MySQL on a system)
- The MySQL root or admin user account
Create a Table in MySQL Shell
In MySQL, data is stored in columns and rows as defined during the creation of the table.
MySQL’s general syntax for creating tables is as follows:
CREATE TABLE [IF NOT EXISTS] table_name( column_definition1, column_definition2, ........, table_constraints );
Step 1: Log into the MySQL Shell
1.Log into the MySQL shell using a terminal window. Log in either as root or as an existing MySQL user account.
Your username should be replaced with username/root.
mysql -u username\root -p
2.Type the password for your account.
Step 2: Create a Database
1.By using the CREATE statement, you can create a database:
CREATE DATABASE employees;
2.The next step is to verify that the database has been created by listing all databases. Make use of the SHOW statement:
SHOW DATABASES;
A list of databases is printed out, as well as information regarding the time it took to execute a query:
3.Using the USE statement, select the database you wish to modify:
use employees;
Step 3: Create a Table
In order to create a table, you must specify the following information:
- Column names – For our table, the id, name, role, and salary columns will be created.
- Varchar of the columns containing characters – Indicates the maximum number of characters to be stored.
- The integer of the columns containing numbers – Describes numeric variables with whole numbers.
- Not null rule – Indicates that each new record must contain data for the column.
- Primary key – Identifies the record by a column in the table.
The following query would be used to create a table named “employees” containing columns for first name, last name, email address, and salary:
CREATE TABLE employees ( id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(30) NOT NULL, last_name VARCHAR(30) NOT NULL, email VARCHAR(50) UNIQUE NOT NULL, salary DOUBLE NOT NULL );
2.To verify that the table has been created using the DESCRIBE command, please follow these steps:
DESCRIBE employees;
The terminal prints out the following information regarding the table:
- Field – Identifies the column name.
- Type – Indicates the column’s data type (varchar for characters, int for numbers).
- Null – Indicates whether the column may contain null values.
- Key – Displays the primary column of the table.
- Default – The default value of the column is displayed.
- Extra – Provides additional information on the columns.
3.Insert employee information in column order – ID, name, role, and salary. Use the INSERT command:
INSERT INTO employees( first_name, last_name,email,salary) VALUES ('Ravi', 'V', 'ravi@gmail.com',100000);
4.In the same manner, repeat the previous step for the second employee. You can display the table using the SELECT command as follows:
SELECT * FROM employees;
OUTPUT