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 in this there are two kinds of tables, called parent and child tables. There must be a primary key in each table which is a unique identification of 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 there is a common field that exists between 2 tables, we can join both the tables using multiple types of join commands like inner join, outer join, left & right join. This table can consists many kind of datatypes as INT(), SMALLINT(), BIGINT(), CHAR(), VARCHAR(), DATE(), TIME(), TIMESTAMP(), BOOLEAN() etc. Apart from this, we can perform many operations in tables as joining, subquery, etc.
How to Create a Table in MySQL?
There are many kinds of SQL language.
This is categorized into:
- DDL (Data Definition Language)
- DML (Data Manipulation Language)
- DQL (Data Query Language)
- DCL (Data Control Language)
- TCL (Transaction Control Language)
Creating a table, altering and dropping table comes under DDL.
Commands | Description | Sample Query |
CREATE | It 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, 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 the creation of a new table in the database.
- “IF NOT EXIST” is not mandatory to give. It basically checks whether the name we want to give to the new table already exists in the database or not.
- “Datatype” is referred to, which kind of datatype we want to assign the attribute. Eg: numeric, string, date, etc.
- At the end or at 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.
- We should always 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: Creating 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: Inserting 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:
How to Alter the Table in MySQL?
Alter is basically for doing manipulation in a table like adding a new column, changing the name of an existing column, dropping an existing column, rename table name, and for adding constraints.
Example #1
Adding a new column.
Code:
ALTER TABLE customer
ADD COLUMN email VARCHAR(50);
Output:
Example #2
Code:
ALTER TABLE customer
ADD COLUMN age INT(5)
DEFAULT 30;
Output:
Example #3
Code:
ALTER TABLE customer
DROP COLUMN email ;
Output:
Example #4
Code:
ALTER TABLE customer
RENAME COLUMN Cust_id TO Customer_id;
Output:
How to Delete a Table in MySQL?
There are 3 kinds of commands in the deletion purposes like 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 WHERE clause to delete the records from the table.
Syntax:
DELETE FROM customer
WHERE first_name LIKE ‘A%’;
Output:
(It deleted all the records where first_name starts with ‘A’)
Conclusion
Those tables in SQL give advantages to keep data in structural form so that we can use those records and can perform many operations easily. Each row can be identified by a unique field 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 storing large amounts of data with easy, secure, and faster accessibility.
Recommended Articles
This is a guide to Table in MySQL. Here we discuss how to create a table in MySQL, how to alter the table, and how to delete a table respectively. You can also go through our other suggested articles to learn more–
7 Online Courses | 8 Hands-on Projects | 73+ Hours | Verifiable Certificate of Completion
4.5
View Course
Related Courses