EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial Table in MySQL
 

Table in MySQL

Priya Pedamkar
Article byPriya Pedamkar

Updated May 10, 2023

Table-in-MySQL

 

 

Introduction to Table in MySQL

Tables are the structured format of saving records in a database as this is easy to access and can be maintained by drawing relations between multiple tables. In the table in MySQL, there are two kinds of tables, called parent and child tables. There must be a primary key in each table that uniquely identifies each record. In the child table, the primary key is one attribute from the parent table, which may also be called a foreign key as this key establishes a relationship between 2 tables. If a common field exists between 2 tables, we can join both tables using multiple types of join commands like inner join, outer join, and left & right join. This table can consists many kind of datatypes as INT(), SMALLINT(), BIGINT(), CHAR(), VARCHAR(), DATE(), TIME(), TIMESTAMP(), BOOLEAN() etc. We can perform many table operations, such as joining, subquery, etc.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

How to Create a Table in MySQL?

There are many kinds of SQL languages.

This is categorized into:

Table-in-MySQL

Creating a table, altering, and dropping a table comes under DDL.

Commands Description Sample Query
CREATE It is used to create a table or database. CREATE table employee;
ALTER Used to modify or change values in the table. ALTER TABLE table_name

ADD COLUMN col_name;

RENAME Rename the table or database name. ALTER TABLE table_name

RENAME COLUMN col_name TO new_col_name;

DROP This removes records of a table as well as the structure of a table. This can’t be rolled back/undo. DROP TABLE IF EXISTS table_name;
TRUNCATE This empties the records only and leaves the structure for future records. TRUNCATE TABLE employee;

Let’s create a customer table.

Code:

CREATE TABLE (IF NOT EXIST) 'tablename'
'Fieldname' datatype (optional parameter)
PRIMARY KEY ('Fieldname');

OR

CREATE TABLE (IF NOT EXIST) 'Tablename'
'Fieldname' datatype PRIMARY KEY;
  • “CREATE TABLE” is responsible for creating a new table in the database.
  • “IF NOT EXIST” is not mandatory to give. It checks whether the name we want to give to the new table already exists in the database.
  • “Datatype” is referred to which kind of datatype we want to assign the attribute. E.g., numeric, string, date, etc.
  • At the end of the line also, we can derive the constraints like primary key, unique key, not null, etc.

Best practices:

  • We should use upper case for SQL keywords like SELECT, DELETE, DROP, etc.
  • It would be best always to end the SQL query with a semicolon.
  • We should always avoid spaces between the name of the table or field names. Instead, we should use underscore like student_table, customer_order, etc.

Let’s create the below ‘customer’ table in the database:

Cust_id First_name Last_name Contact
1009 Ajinkya Rahane 8746874464
1005 Hardik Pandya 5435555426
1007 Jasprit Bumrah 9875986763
1002 Virat Kohli 7487687648
1010 Ravi Sashtri 9759878404
1006 Krunal Pandya 9874989859
1012 Bhubaneswar Kumar 3547687379
1004 Virendra Shewag 8765876876
1003 Sachin Tendulkar 9878749867
1008 Anil Kumble 9856876755
1001 Rohit Sharma 2986746767
1011 Rahul Dravid 5876874676

Step 1: Create the table with these fields.

Code:

CREATE TABLE customer (
Cust_id INT(10),
First_name VARCHAR(20) not null,
Last_name VARCHAR(20) not null,
Contact INT(10),
PRIMARY KEY (Cust_id)
);

Step 2: Insert all records.

Code:

INSERT INTO customer
VALUES
(1009, 'Ajinkya','Rahane', 8746874464),
(1005, 'Hardik', 'Pandya', 5435555426),
(1007, 'Jasprit', 'Bumrah', 9875986763),
(1002, 'Virat', 'Kohli', 7487687648),
(1010, 'Ravi', 'Sashtri', 9759878404),
(1006, 'Krunal', 'Pandya', 9874989859),
(1012, 'Bhubaneswar', 'Kumar', 3547687379),
(1004, 'Virendra', 'Shewag', 8765876876),
(1003, 'Sachin', 'Tendulkar', 9878749867),
(1008, 'Anil', 'Kumble', 9856876755),
(1001, 'Rohit', 'Sharma', 2986746767),
(1011, 'Rahul', 'Dravid', 5876874676);

Output:

Table in MySQL

How to Alter the Table in MySQL?

Alter is basically for manipulating a table, like adding a new column, changing the name of an existing column, dropping a current column, renaming the table name, and adding constraints.

Example #1

We are adding a new column.

Code:

ALTER TABLE customer
ADD COLUMN email VARCHAR(50);

Output:

Table in MySQL

Example #2

Code:

ALTER TABLE customer
ADD COLUMN age INT(5)
DEFAULT 30;

Output:

ALTER

Example #3

Code:

ALTER TABLE customer
DROP COLUMN email ;

Output:

DROP COLUMN detail

Example #4

Code:

ALTER TABLE customer
RENAME COLUMN Cust_id TO Customer_id;

Output:

ALTER TABLE customer

How to Delete a Table in MySQL?

There are three kinds of commands for deletion: DROP, TRUNCATE, and DELETE.

DROP table deletes the whole records as well as the structure/index of the table.

Syntax:

DROP TABLE Table_name;

TRUNCATE table deletes only the records from the table by keeping the structure for further use.

Syntax:

TRUNCATE TABLE Table_name;

DELETE is used with the WHERE clause to delete the records from the table.

Syntax:

DELETE FROM customer
WHERE first_name LIKE 'A%';

Output:

DELETE is used with WHERE clause

(It deleted all the records where first_name starts with ‘A’)

Conclusion

Those tables in SQL give advantages to keeping data in structural form so that we can use those records and perform many operations efficiently. A unique field can identify each row for better accessibility to particular records. In the database, using those tables, we can establish many relationships with different tables/data by creating some foreign keys. This helps us store large amounts of data with easy, secure, and faster accessibility.

Recommended Articles

We hope that this EDUCBA information on “Table in MySQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. Distinct Keywords in SQL
  2. SQL Server Constraints
  3. SELECT in MySQL
  4. MySQL Aggregate Function

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW