Table in MySQL

Introduction on 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

1. DDL (Data definition language)
2. DML (Data manipulation language)
3. DQL (Data query language)
4. DCL (Data control language)
5. TCL (Transaction control language)

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

Let’s create a customer table,

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

Step 1:

Creating the table with these fields

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

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 MqSQL?

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

ALTER TABLE customer
ADD COLUMN email VARCHAR(50);

Output:

Example #2

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

Output:

Example #3

ALTER TABLE customer
DROP COLUMN email ;

Output:

Example #4

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 on Table in MySQL. Here we discuss how to create a table in MySQL, how to alter the table, and how to delete a table for example. You can also go through our other suggested articles to learn more–

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