Umesh Chandra

What is a MySQL database?

MySQL Database is a client/server system that consists of a multithreaded SQL server that supports different back ends, several different client programs and libraries, administrative tools, and a wide range of application-programming interfaces (APIs).

Okay then How do we create a Database ?

The MySQL CREATE DATABASE Statement

The CREATE DATABASE statement is used to create a new SQL database.

Syntax:

CREATE DATABASE databasename;

CREATE DATABASE Example

The following SQL statement creates a database called “Database_Example”:

CREATE DATABASE testDB;

---

The MySQL DROP DATABASE Statement

The DROP DATABASE statement is used to drop an existing SQL database.

Syntax

DROP DATABASE databasename;

DROP DATABASE Example

The following SQL statement drops the existing database “Database_Example”:

DROP DATABASE Database_Example;

---

MySQL CREATE TABLE Statement

The MySQL CREATE TABLE Statement

The CREATE TABLE statement is used to create a new table in a database.

Syntax

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

column1, column2, column3 , …. specify the names of the column of the table

datatype represents the type of the data that column can hold

those can be INT, CHAR(), VARCHAR(), DATE(), TIME() — etc.

For more information about the datatypes, go to MySQL Data Types – I

MySQL CREATE TABLE Example

The following example creates a table called “Employee” which contains six columns:

• Employee_id
• Name
• Gender
• Department_id
• Project_id
• Dat_of_joining

CREATE TABLE EMPLOYEE_21BCE1782(
  employee_id INT,
  name VARCHAR(10),
  gender CHAR(1),
  dept_id INT,
  project_id INT,
  Date_of_Joining DATE
);

The employee_id, dept_id, project_id are of datatypes INT and it will hold only the integers.

The name is of datatype VARCHAR and it will hold characters up to maximum of length 255 characters.

gender is of datatype CHAR and it will hold characters. The length of CHAR is fixed to the length that we create in the table.

For example,

If we create a column with datatype of char(3) and inserted a data with value ‘A’ with length 1, As we are inserting in CHAR(3) it will store the inserted data as length 3 by adding the extra spaces in the data. whereas VARCHAR cannot do like that. It is the only difference between CHAR() and VARCHAR()

The empty “Employees” table will now look like this:

Employee_id	Name	Gender	Dept_id	Project_id	Date_of_Joining

---

Create Table Using Another Table

A copy of an existing table can also be created using CREATE TABLE.

The new table gets the same column definitions. All columns or specific columns can be selected.

If you create a new table using an existing table, the new table will be filled with the existing values from the old table.

Syntax

CREATE TABLE new_table_name AS
    SELECT column1, column2,...
    FROM existing_table_name
;

The following SQL creates a new table called “Department”

CREATE TABLE Department AS
SELECT Name, Dept_id
FROM Employee;

---

The MySQL DROP TABLE Statement

The DROP TABLE statement is used to drop an existing table in a database.

Syntax

DROP TABLE table_name;

Dropping a table will result in loss of complete information stored in the table!

MySQL DROP TABLE Example

The following SQL statement drops the existing table “Department”:

DROP TABLE DEPARTMENT;

---

MySQL TRUNCATE TABLE

The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table itself.

Syntax

TRUNCATE TABLE table_name;

MySQL TRUNCATE Example

The following SQL statement truncate the existing table “Employee”:

TRUNCATE TABLE Employee;

---

MySQL DELETE TABLE

The DELETE TABLE statement is used to delete particular records inside a table, but not the table itself.

Syntax:

DELETE FROM table_name WHERE condition;

The DELETE command is used to delete particular records from a table. But The TRUNCATE command is used to delete the complete data from the table.