What is SQL?

 Structured Query Language

 lets you access and manipulate databases
 RDBMS is the basis for SQL
 SQL became a standard
American National Standards Institute (ANSI) in 1986,
International Organization for Standardization (ISO) in 1987.

What Can SQL do?

 SQL can execute queries against a database
 Retrieve, insert, update, delete data from a database
 SQL can create new databases
 SQL can create new tables in a database
 SQL can create stored procedures in a database
 SQL can create views in a database
 SQL can set permissions on tables, procedures, and views
ANSI support major commands SELECT, UPDATE, DELETE, INSERT, WHERE
case sensitive "A" and "a" are treated as different characters

We are going to learn MySQL

MySQL:- it is a SQL Database, It is a RDBMS
In database the data is stored by using 3 model
1. Hierarchical

2. Network
Full stack application

MySQL Create Database

 A database is used to store the collection of records in an organized form.

 It allows us to hold the data into tables-> rows, columns, and index

 We can access and manage the records through the database very easily.
 create a database mainly in two ways:

1. MySQL Command Line Client

2. MySQL Workbench

MySQL Command Line Client

syntax: IF NOT EXIST avoids an error when we create a database that
already exists.
 >create database if not exists database_name
or
 >create database database_name

 >show create database test3;

 >show databases ;
We can create tables bu using databases

use table3;

All the database names, table names, and table field names are case sensitive.
Case sensitive uppercase or lowercase dogha chalel.
MySQL Workbench

 It is a visual database designing or GUI tool .

 This visual tool supports SQL development, data modeling, data migration, and
comprehensive administration tools for server configuration, user administration,
backup, and many more.

 Data modeling is a process that helps you organize data into relational
tables or object types
 Data migration in MySQL refers to the process of transferring data from
one database to another

Seps in MYSQL extra

MySQL Show/List Databases

>show databases;

SHOW synonyms SCHEMAS

SHOW SCHEMAS == SHOW DATABASES

>show schemas
Pattern Matching clause (like and where )

DROP Database
rules or conditions
 delete database along with all the tables, indexes, and constraints permanently.

 If the database is not available in the MySQL server, the DROP DATABASE
statement throws an error.

MySQL allows us to drop/delete/remove a database mainly in two ways:

o MySQL Command Line Client

o MySQL Workbench

syntax:

 >drop database database_name;

 >drop database if exists database_name;

schema is the synonym for the database.

 >drop schema database_name;

 >drop schema if exists database_name;

Example:
COPY Database

Create a duplicate copy of an existing database.

very useful when accidentally our database is lost or failure.

three-step process:

1. First, the original database records are dumped (copied) to a temporary file that
holds the SQL commands for reinserting the data into the new database.

2. Second, it is required to create a new database.

3. Finally, the SQL file is processed, and the data will be copied into the new
database.

We need to follow these steps to copy a database to another database:

1. First, use the CREATE DATABASE statement to create a new database.

2. Second, store the data to an SQL file. We can give any name to this file, but it must
end with a .sql extension.
3. Third, export all the database objects along with its data to copy using
the mysqldump tool and then import this file into the new database.

For the demonstration, we will copy the testdb database to testdb_copy database

1. mysql> CREATE DATABASE testdb_copy;

2. mysql> SHOW DATABASES;
Now, open a DOS or terminal window to access the MySQL server on the command
line. For example, if we have installed the MySQL in the C folder, copy the following
folder and paste it in our DOS command. Then, press the Enter key.

1. C:\Users\javatpoint> CD C:\Program Files\MySQL\MySQL Server 8.0\bin

In the next step, we need to use the mysqldump tool to copy the database objects
and data into the SQL file. Suppose we want to dump (copy) the database objects and
data of the testdb into an SQL file located at D:\Database_backup folder. To do
this, execute the below statement:

1. mysqldump -u root -p testdb > D:\Database_backup\testdb.sql

2. Enter password: **********

The above statement instructs mysqldump tool to log in to the MySQL database
server using the username and password and then exports the database objects and
data of the testdb database to D:\Database_backup\testdb.sql. It is to note that
the operator (>) used for exporting the database from one location to another.

In the next step, we need to import the D:\Database_backup\testdb.sql file into

testdb_copy database. To do this, execute the below statement:

1. mysql -u root -p testdb_copy < D:\Database_backup\testdb.sql

2. Enter password: **********

It is to note that the operator (<) used for importing the database from one
location to another.
Finally, we can verify whether the above operation is successful or not by using
the SHOW TABLES command in the MySQL command-line tool:

1. mysql> SHOW TABLES;

In this output, we can see that all the objects and data from the testdb database to
testdb_copy database have successfully copied.
MySQL Keys
MySQL Unique Key

A unique key store into the column will be unique.

It means a column cannot stores duplicate values.

It can accept a null value, allowed only one null value per column.

Syntax

1. CREATE TABLE table_name(

2. col1 datatype,
3. col2 datatype UNIQUE,
4. ...
5. );

If we want to create more than one unique key column into a table, use the syntax as
below:

1. CREATE TABLE table_name(

2. col1 col_definition,
3. col2 col_definition, name of the unique
key.
4. ...
5. [CONSTRAINT constraint_name]
6. UNIQUE(column_name(s))
7. );

Unique Key Example

1. CREATE TABLE Student2 (

2. Stud_ID int NOT NULL UNIQUE,
3. Name varchar(45),
4. Email varchar(45),
5. Age int,
6. City varchar(25)
7. );
INSERT INTO Student2 (Stud_ID, Name, Email, Age, City)
1. VALUES (1, 'Peter', 'peter@javatpoint.com', 22, 'Texas'),
 2. (2, 'Suzi', 'suzi@javatpoint.com', 24, 'California'),
3. (3, 'Joseph', 'joseph@javatpoint.com', 23, 'Alaska');
4. mysql> INSERT INTO Student2 (Stud_ID, Name, Email, Age, City)
5. VALUES (1, 'Stephen', 'stephen@javatpoint.com', 22, 'Texas');

Output

If you want to define the unique key on multiple columns, use the query as below:

1. CREATE TABLE Student3 (

2. Stud_ID int,
3. Roll_No int,
4. Name varchar(45) NOT NULL,
5. Email varchar(45),
6. Age int,
7. City varchar(25),
8. CONSTRAINT uc_rollno_email Unique(Roll_No, Email)
9. );

In the output, we can see that the unique key value contains two columns that
are Roll_No and Email.
To verify this, execute the following statement:

1. mysql> SHOW INDEX FROM Student3;

Here, we can see that the unique constraint has successfully added into the table:

DROP Unique Key

The ALTER TABLE statement also allows us to drop the unique key from the table.
The following syntax is used to drop the unique key:

1. ALTER TABLE table_name DROP INDEX constraint_name;

In the above syntax, the table_name is the name of the table that we want to
modify, and constraint_name is the name of the unique key we are going to remove.

Example

This statement will remove the uc_rollno_email constraint from the table
permanently.

1. mysql> ALTER TABLE Student3 DROP INDEX uc_rollno_email;

We can execute the SHOW INDEX statement to very this.

Unique Key Using ALTER TABLE Statement

This statement allows us to do the modification into the existing table. Sometimes we
want to add a unique key to the column of an existing table; then, this statement is
used to add the unique key for that column.

Syntax

Following are the syntax of the ALTER TABLE statement to add a unique key:

1. ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE(colu

mn_list);

Example

This statement creates a table "Students3" that have no unique key column into the
table definition.

1. CREATE TABLE Student3 (

2. Stud_ID int,
3. Roll_No int,
4. Name varchar(45) NOT NULL,
 5. Email varchar(45),
6. Age int,
7. City varchar(25)
8. );

After creating a table, if we want to add a unique key to this table, we need to
execute the ALTER TABLE statement as below:

1. mysql> ALTER TABLE Student3 ADD CONSTRAINT uc_rollno_email UNIQUE(

Roll_No, Email);

We can see the output where both statements executed successfully.

To verify this, execute the following statement:

1. mysql> SHOW INDEX FROM Student3;

Here, we can see that the unique constraint has successfully added into the table:

MySQL Primary Key

MySQL primary key is a single or combination of the field, which is used to identify
each record in a table uniquely.

If the column contains primary key constraints, then it cannot be null or empty.

A table may have duplicate columns, but it can contain only one primary key. It
always contains unique value into a column.

When you insert a new row into the table, the primary key column can also use
the AUTO_INCREMENT attribute to generate a sequential number for that row
automatically. MySQL automatically creates an index named "Primary" after defining
a primary key into the table. Since it has an associated index, we can say that the
primary key makes the query performance fast.

Rules for Primary key

Following are the rules for the primary key:

 The primary key column value must be unique.

 Each table can contain only one primary key.
 The primary key column cannot be null or empty.
 MySQL does not allow us to insert a new row with the existing primary key.
 It is recommended to use INT or BIGINT data type for the primary key column.

We can create a primary key in two ways:

o CREATE TABLE Statement

o ALTER TABLE Statement

Let us discuss each one in detail.

Primary Key Using CREATE TABLE Statement

Syntax

1. CREATE TABLE table_name(

2. col1 datatype PRIMARY KEY,
3. col2 datatype,
4. ...
5. );

If we want to create more than one primary key column into the table, use the below
syntax:

1. CREATE TABLE table_name

2. (
3. col1 col_definition,
4. col2 col_definition,
5. ...
 6.
7. CONSTRAINT [constraint_name]
8. PRIMARY KEY (column_name(s))
9. );
Parameter Explanation

Parameter Name Descriptions

Table_name It is the name of the table that we are going to create.

Col1, col2 It is the column names that contain in the table.

Constraint_name It is the name of the primary key.

Column_name(s) It is the column name(s) that is going to be a primary key.

Primary Key Example

1. Mysql> CREATE TABLE Login(

2. login_id INT AUTO_INCREMENT PRIMARY KEY,
3. username VARCHAR(40),
4. password VARCHAR(55),
5. email VARCHAR(55)
6. );

Next, use the insert query to store data into a table:

1. mysql> INSERT INTO Login(login_id, username, password, email)

2. VALUES (1,'Stephen', 15343434532, 'stephen@javatpoint.com'),
3. (2, 'Joseph', 35435479495, 'Joseph@javatpoint.com');

4. mysql> INSERT INTO Login(login_id, username, password, email)

5. VALUES (1,'Peter', 15343434532, 'peter@javatpoint.com');

Output

In the below output, we can see that the first insert query executes successfully.
While the second insert statement fails and gives an error that says: Duplicate entry
for the primary key column.
If you want to define the primary key on multiple columns, use the query as below:

1. mysql> CREATE TABLE Students (

2. Student_ID int,
3. Roll_No int,
4. Name varchar(45) NOT NULL,
5. Age int,
6. City varchar(25),
7. Primary Key(Student_ID, Roll_No)
8. );

In the output, we can see that the primary key value contains two columns that
are Student_ID and Roll_No.

Primary Key Using ALTER TABLE Statement

This statement allows us to do the modification into the existing table.

When the table does not have a primary key, this statement is used to add the
primary key to the column of an existing table.
Syntax

Following are the syntax of the ALTER TABLE statement to create a primary key in
MySQL:

1. ALTER TABLE table_name ADD PRIMARY KEY(column_list);

Example

The following statement creates a table "Persons" that have no primary key column
into the table definition.

1. mysql> CREATE TABLE Persons (

2. Person_ID int NOT NULL,
3. Name varchar(45),
4. Age int,
5. City varchar(25)
6. );

After creating a table, if we want to add a primary key to this table, we need to
execute the ALTER TABLE statement as below:

1. mysql> ALTER TABLE Persons ADD PRIMARY KEY(Person_ID);

We can see the output where both statements executed successfully.

If the table needs to add the primary key into a table that already has data into the
column, then it must be sure to the column does not contains duplicates or null
values.
 DROP Primary Key

The ALTER TABLE statement also allows us to drop the primary key from the table.
The following syntax is used to drop the primary key:

1. ALTER TABLE table_name DROP PRIMARY KEY;

Example

1. mysql> ALTER TABLE Login DROP PRIMARY KEY;

Primary Key vs Unique Key

The following comparison chart explains some of the common differences between
both of them:

SN Primary Key Unique Key

1. It is a single or combination of the field, which It also determines each row of the table
is used to identify each record in a table uniquely in the absence of a primary
uniquely. key.

2. It does not allow to store a NULL value into the It can accept only one NULL value into
primary key column. the unique key column.

3. A table can have only one primary key. A table can have more than one unique
key.

4. It creates a clustered index. It creates a non-clustered index.

MySQL Foreign Key

 The foreign key is used to link one or more than one table together.

 It is also known as the referencing key.

 A foreign key matches the primary key field of another table.

 It means a foreign key field in one table refers to the primary key field of the other
table.

 It identifies each row of another table uniquely that maintains the referential
integrity in MySQL.

A foreign key makes it possible to create a parent-child relationship

MySQL defines the foreign key in two ways:

1. Using CREATE TABLE Statement

2. Using ALTER TABLE Statement

Syntax
CREATE TABLE OR ALTER TABLE

1. [CONSTRAINT constraint_name]
2. FOREIGN KEY [foreign_key_name] (col_name, ...)
3. REFERENCES parent_tbl_name (col_name,...)
4. ON DELETE referenceOption
5. ON UPDATE referenceOption

In the above syntax, we can see the following parameters:

constraint_name: It specifies the name of the foreign key constraint. If we have

not provided the constraint name, MySQL generates its name automatically.

col_name: It is the names of the column that we are going to make foreign key.

parent_tbl_name: It specifies the name of a parent table followed by column names

that reference the foreign key columns.

Refrence_option: It is used to ensure how foreign key maintains referential integrity

using ON DELETE and ON UPDATE clause between parent and child table.

MySQL contains five different referential options, which are given below:
CASCADE: It is used when we delete or update any row from the parent table, the
values of the matching rows in the child table will be deleted or updated
automatically.

SET NULL: It is used when we delete or update any row from the parent table, the
values of the foreign key columns in the child table are set to NULL.

RESTRICT: It is used when we delete or update any row from the parent table that
has a matching row in the reference(child) table, MySQL does not allow to delete or
update rows in the parent table.

NO ACTION: It is similar to RESTRICT. But it has one difference that it checks

referential integrity after trying to modify the table.

SET DEFAULT: The MySQL parser recognizes this action. However, the InnoDB and
NDB tables both rejected this action.

NOTE: MySQL mainly provides full support to CASCADE, RESTRICT, and SET NULL
actions. If we have not specified the ON DELETE and ON UPDATE clause, MySQL takes
default action RESTRICT.

Foreign Key Example

Let us understand how foreign key works in MySQL. So first, we are going to create a
database named "mysqltestdb" and start using it with the command below:

1. mysql> CREATE DATABASE mysqltestdb;

2. mysql> use mysqltestdb;

Next, we need to create two tables named "customer" and "contact" using the
below statement:

Table: customer

1. customer (
2. ID INT NOT NULL AUTO_INCREMENT,
3. Name varchar(50) NOT NULL,
4. City varchar(50) NOT NULL,
 5. PRIMARY KEY (ID)
6. );

Table: contact

1. CREATE TABLE contact (

2. ID INT,
3. Customer_Id INT,
4. Customer_Info varchar(50) NOT NULL,
5. Type varchar(50) NOT NULL,
6. INDEX par_ind (Customer_Id),
7. CONSTRAINT fk_customer FOREIGN KEY (Customer_Id)
8. REFERENCES customer(ID)
9. ON DELETE CASCADE
10. ON UPDATE CASCADE
11. );

Table Structure Verification

1. mysql> SHOW TABLES;

2. mysql> DESCRIBE customer;
3. mysql> DESCRIBE contact;

We will get the structure as below:

In the above output, we can see that the PRI in the key column of the customer table
tells that this field is the primary index value. Next, the MUL in the key column of the
contact value tells that the Customer_Id field can store multiple rows with the same
value.

Insert Data to the Table

Now, we have to insert the records into both tables. Execute this statement to insert
data into table customer:

1. INSERT INTO customer(Name, City) VALUES

2. ('Joseph', 'California'),
3. ('Mary', 'NewYork'),
4. ('John', 'Alaska');
1. INSERT INTO contact (Customer_Id, Customer_Info, Type) VALUES
2. (1, 'Joseph@javatpoint.com', 'email'),
3. (1, '121-121-121', 'work' ),
4. (1, '123-123-123', 'home'),
5. (2, 'Mary@javatpoint.com', 'email'),
6. (2, 'Mary@javatpoint.com', 'email'),
7. (2, '212-212-212', 'work'),
8. (3, 'John@javatpoint.com', 'email'),
9. (3, '313-313-313', 'home');
 1. FOREIGN KEY (Customer_Id) REFERENCES customer(ID)
2. ON DELETE CASCADE
3. ON UPDATE CASCADE.

It means if we delete any customer record from the customer table, then the related
records in the contact table should also be deleted. And the ON UPDATE CASCADE will
updates automatically on the parent table to referenced fields in the child table(Here,
it is Customer_Id).

Execute this statement that deletes a record from the table whose name is JOHN.

1. mysql> DELETE FROM customer WHERE Name='John';

Again, if we look at our tables, we can see that both tables were changed. It means
the fields with name JOHN will be removed entirely from both tables.
Now, test the ON UPDATE CASCADE. Here, we are going to update the Customer_Id
of Mary in the contact table as:

1. mysql> UPDATE customer SET id=3 WHERE Name='Mary';

Foreign Key example using SET NULL action

First, we have to create two table named Persons and Contacts, as shown below:

Table: Persons

1. CREATE TABLE Persons (

2. ID INT NOT NULL AUTO_INCREMENT,
3. Name varchar(50) NOT NULL,
4. City varchar(50) NOT NULL,
5. PRIMARY KEY (ID)
6. );

Table: Customers

1. CREATE TABLE Contacts (

2. ID INT,
3. Person_Id INT,
4. Info varchar(50) NOT NULL,
5. Type varchar(50) NOT NULL,
6. INDEX par_ind (Person_Id),
7. CONSTRAINT fk_person FOREIGN KEY (Person_Id)
 8. REFERENCES Persons(ID)
9. ON DELETE SET NULL
10. ON UPDATE SET NULL
11. );

Next, we need to insert the data into both tables using the following statement:

1. INSERT INTO Persons(Name, City) VALUES

2. ('Joseph', 'Texas'),
3. ('Mary', 'Arizona'),
4. ('Peter', 'Alaska');

1. INSERT INTO Contacts (Person_Id, Info, Type) VALUES

2. (1, 'joseph@javatpoint.com', 'email'),
3. (1, '121-121-121', 'work' ),
4. (2, 'mary@javatpoint.com', 'email'),
5. (2, '212-212-212', 'work'),
6. (3, 'peter@javatpoint.com', 'email'),
7. (3, '313-313-313', 'home');

Now, update the ID of the "Persons" table:

1. mysql> UPDATE Persons SET ID=103 WHERE ID=3;

If we look at our tables, we can see that both tables were changed. The rows with
a Person_Id=3 in the Contacts table automatically set to NULL due to the ON
UPDATE SET NULL action.

How to DROP Foreign Key

MySQL allows the ALTER TABLE statement to remove an existing foreign key
from the table. The following syntax is used to drop a foreign key:

1. ALTER TABLE table_name DROP FOREIGN KEY fk_constraint_name;

Here, the table_name is the name of a table from where we are going to remove the
foreign key. The constraint_name is the name of the foreign key that was added
during the creation of a table.

If we have not known the name of an existing foreign key into the table, execute the
following command:

1. mysql> SHOW CREATE TABLE contact;

It will give the output as below where we can see that the table contact has one
foreign key named fk_customer shown in the red rectangle.
Now, to delete this foreign key constraint from the contact table, execute the
statement as below:

1. mysql> ALTER TABLE contact DROP FOREIGN KEY fk_customer;

We can verify whether foreign key constraint removes or not, use the SHOW CREATE
TABLE statement. It will give the output as below where we can see that the foreign
key is no longer available in the table contact.

Define Foreign Key Using ALTER TABLE Statement

This statement allows us to do the modification into the existing table. Sometimes
there is a need to add a foreign key to the column of an existing table; then, this
statement is used to add the foreign key for that column.

Syntax

Following are the syntax of the ALTER TABLE statement to add a foreign key in the
existing table:

1. ALTER TABLE table_name

2. ADD [CONSTRAINT [symbol]] FOREIGN KEY
3. [index_name] (column_name, ...)
4. REFERENCES table_name (column_name,...)
5. ON DELETE referenceOption
6. ON UPDATE referenceOption

When we add a foreign key using the ALTER TABLE statement, it is recommended to
first create an index on the column(s), which is referenced by the foreign key.
Example

The following statement creates two tables, "Person" and "Contact", without having
a foreign key column into the table definition.

Table: Person

1. CREATE TABLE Person (

2. ID INT NOT NULL AUTO_INCREMENT,
3. Name varchar(50) NOT NULL,
4. City varchar(50) NOT NULL,
5. PRIMARY KEY (ID)
6. );

Table: Contact

1. CREATE TABLE Contact (

2. ID INT,
3. Person_Id INT,
4. Info varchar(50) NOT NULL,
5. Type varchar(50) NOT NULL
6. );

After creating a table, if we want to add a foreign key to an existing table, we need to
execute the ALTER TABLE statement as below:

1. ALTER TABLE Contact ADD INDEX par_ind ( Person_Id );

2. ALTER TABLE Contact ADD CONSTRAINT fk_person
3. FOREIGN KEY ( Person_Id ) REFERENCES Person ( ID ) ON DELETE CASCADE
ON UPDATE RESTRICT;
Foreign Key Checks

MySQL has a special variable foreign_key_checks to control the foreign key

checking into the tables. By default, it is enabled to enforce the referential integrity
during the normal operation on the tables. This variable is dynamic in nature so that it
supports global and session scopes both.

Sometimes there is a need for disabling the foreign key checking, which is very useful
when:

o We drop a table that is a reference by the foreign key.

o We import data from a CSV file into a table. It speeds up the import operation.
o We use ALTER TABLE statement on that table which has a foreign key.
o We can execute load data operation into a table in any order to avoid foreign key
checking.

The following statement allows us to disable foreign key checks:

1. SET foreign_key_checks = 0;

The following statement allows us to enable foreign key checks:

1. SET foreign_key_checks = 1;
MySQL Composite Key

A composite key in MySQL is a combination of two or more than two columns in a

table that allows us to identify each row of the table uniquely. It is a type
of candidate key which is formed by more than one column. MySQL guaranteed the
uniqueness of the column only when they are combined. If they have taken
individually, the uniqueness cannot maintain.

 Any key such as primary key, super key, or candidate key can be called
composite key when they have combined with more than one attribute.

 A composite key is useful when the table needs to identify each record with
more than one attribute uniquely.

 A column used in the composite key can have different data types.

 Thus, it is not required to be the same data type for the columns to make a
composite key in MySQL.

A composite key can be added in two ways:

1. Using CREATE Statement

2. Using ALTER Statement

Let us see both ways in detail.

Composite Key Using CREATE Statement

create a table "Product"

1. CREATE TABLE Product (

2. Prod_ID int NOT NULL,
3. Name varchar(45),
4. Manufacturer varchar(45),
5. PRIMARY KEY(Name, Manufacturer)
6. );

In the above statement, we have created a composite primary with the column
names Name and Manufacturer.

We can verify the same using the command as below:

1. DESCRIBE Product;

After the successful execution, we can see that the Key column has two PRI. It means
we have successfully added the composite primary key on Name and Manufacturer
columns.
Next, we need to insert the values into this table as given below:

1. INSERT INTO Product (Prod_ID, Name, Manufacturer)

2. VALUES (101, 'Soap', 'Hamam'),
3. (102, 'Shampoo', 'Teresme'),
4. (103, 'Oil', 'Daber Almond');

Next, execute the below command to show the table data:

1. SELECT * FROM Product;

It will give the output below:

Again execute the below insert statement to understand composite key more clearly:

1. INSERT INTO Product (Prod_ID, Name, Manufacturer)

2. VALUES (101, 'Soap', 'Hamam');
3.
4. INSERT INTO Product (Prod_ID, Name, Manufacturer)
5. VALUES (101, 'Soap', 'LUX');

error saying that: Duplicate entry for product.primary.

second insert statement, it will be added successfully into the table. It is because we
can insert any number of soap in the product column, but the manufacturer column
should be different.
Hence, we can say that the composite key always enforces the uniqueness of the
columns of that table, which has two keys.

Composite Key Using ALTER TABLE Statement

ALTER statement always used to do the modification into the existing table.
Sometimes it is required to add the composite key to uniquely identify each record of
the table with more than one attribute. In that case, we use an ALTER TABLE statement.

Let us first create a table "Student" using the below statement:

1. CREATE TABLE Student(

2. stud_id int NOT NULL,
3. stud_code varchar(15),
4. stud_name varchar(35),
5. subject varchar(25),
6. marks int
7. );

Now, execute the ALTER TABLE statement to add a composite primary key as follows:

1. ALTER TABLE Student add primary key(stud_id, subject);

We can verify the composite primary key added into a table or not using the following
command:

1. DESCRIBE Student;

In the output, we can see that the key column has PRI, which means we have
successfully added the composite primary key to stud_id and subject columns.
acno Cid Type balance
1 100 saving 50000
2 100 current 400000
3 100 Demat 400000
4 101 saving 600000
5 101

Acname Custid Email Mobile

Kishori 100 kkk@gmail.com 3333
Revati 101 rrrr@gmail.com 55555
Student

Sid Sname Address cpoursename

100
200

Marks

Studid courseid marks

100 java 67
100 C++ 67
200 java 66
200 C++ 88

roomno rloc email charges From date To date

1 xxx ff 4000 25 sept 30 sept
1 xxxx cc 4000 1 oct 4 th oct
1 xxx ff 4000 5 oct 10 oct

Employee table

empid ename address Adhar Email Mobile passport desg

card
22221

Primary key→ empid

Alternate key→ adhar num, email, passportno, mobile
Candidate key→ empid, adhar num, email, passportno, mobile
Keys in database
Primary key
 Minimal set of attributes, which identifies the row uniquely is called as primary
key,
 If the primary key is single attribute, then it is called as simple primary key
 But if it contains more than one attributes then it is called as composite primary
key
 It should not contain null values.

Alternate key—all candidate keys which are not chosen as primary key are
alternate key
Candidate key---Any minimal set of attributes which identifies the row uniquely is a
candidate key
Employee table
Empid , adhar num, email,passportno,mpbile
Super Key-→ any combination which identifies the row uniquely is called as super
key.
Unique key---→the attribute whose values should be unique, but it is not primary key,

It may contain null values at multiple places.

Foreign key--→any column which references other column of the same table or
different table, and the other column should be primary key.

MySQL We are using

SQL--→( Structured query language)
Plsql-→procedural structured query language
Types of statement

Type description statements

DQL Data query language select
DDL Data definition language Create, Alter,
drop,truncate
DML Data manipulation Insert, delete, update
language
TCL transaction control Commit, rollback, save
language point
DCL Data control language Grant, revoke
In mysql you can
use
Arithmetic operators
+, -, /, %
Relational operators
<, > , =, <=,>=
Logical operators
and , or, not
In databases string should be enclosed in single quotes
Every query should end with ;
operators in mysql
[Not] Between … Useful to check the range of values or dates
and
[not] in It is used to check with multiple values in the same column

MySQL CREATE TABLE

A table is used to organize data in the form of rows and columns and used for both
storing and displaying records in the structure format. It is similar to worksheets in the
spreadsheet application. A table creation command requires three things:

o Name of the table

o Names of fields
o Definitions for each field

MySQL allows us to create a table into the database mainly in two ways:

1. MySQL Command Line Client

2. MySQL Workbench

MySQL Command Line Client

MySQL allows us to create a table into the database by using the CREATE
TABLE command. Following is a generic syntax for creating a MySQL table in the
database.

1. CREATE TABLE [IF NOT EXISTS] table_name(

2. column_definition1,
3. column_definition2,
4. ........,
5. table_constraints
6. );

Parameter Explanation

The parameter descriptions of the above syntax are as follows:

Parameter Description

database_nam It is the name of a new table. It should be unique in the MySQL database that we
e avoids an error when we create a table into the selected database that already exi
 column_definiti It specifies the name of the column along with data types for each column. The c
on by the comma operator. The syntax of column
column_name1 data_type(size) [NULL | NOT NULL]

table_constrain It specifies the table constraints such as PRIMARY KEY, UNIQUE KEY, FOREIGN KEY,
ts

Example

Let us understand how to create a table into the database with the help of an
example. Open the MySQL console and write down the password, if we have set
during installation. Now open the database in which you want to create a table. Here,
we are going to create a table name "employee_table" in the
database "employeedb" using the following statement:

1. mysql> CREATE TABLE employee_table(

2. id int NOT NULL AUTO_INCREMENT,
3. name varchar(45) NOT NULL,
4. occupation varchar(35) NOT NULL,
5. age int NOT NULL,
6. PRIMARY KEY (id)
7. );
NOTE:
1. Here, NOT NULL is a field attribute, and it is used because we don't want this field to be
NULL. If we try to create a record with a NULL value, then MySQL will raise an error.
2. The field attribute AUTO_INCREMENT specifies MySQL to go ahead and add the next
available number to the id field. PRIMARY KEY is used to define a column's uniqueness. We
can use multiple columns separated by a comma to define a primary key.

Visual representation of creating a MySQL table:

We need to use the following command to see the newly created table:

1. mysql> SHOW TABLES;

It will look like the below output:

See the table structure:

We can use the following command to see the information or structure of the newly
created table:

1. mysql> DESCRIBE employee_table;

It will look like this:

Create Table Using MySQL Workbench
It is a visual GUI tool used to create databases, tables, indexes, views, and stored
procedures quickly and efficiently. To create a new database using this tool, we first
need to launch the MySQL Workbench and log in using the username and password
that you want. It will show the following screen:

Now do the following steps for table creation:

1. Go to the Navigation tab and click on the Schema menu. Here, we can see all the
previously created databases. Now we are ready to select the database in which a
table is created.

2. Select the database, double click on it, and we will get the sub-menu under the
database. These sub-menus are Tables, Views, Functions, and Stored Procedures, as
shown in the below screen.
3. Select Tables sub-menu, right-click on it, and select Create Table option. We can
also click on create a new table icon (shown in red rectangle) to create a table.

4. On the new table screen, we need to fill all the details to create a table. Here, we
will enter the table name (for example, employee_table) and use default collation
and engine.

5. Click inside the middle window and fill the column details. Here, the column name
contains many attributes such as Primary Key(PK), Not Null (NN), Unique Index (UI),
Binary(B), Unsigned Data type(UN), Auto Incremental (AI), etc. The following screen
explains it more clearly. After filling all the details, click on the Apply button.

6. As soon as you click on the Apply button, it will open the SQL statement window.
Again, click on the Apply button to execute the statement and Finish button to save
the changes.
7. Now, go to the Schema menu and select the database which contains the newly
created table, as shown in the screen below.
MySQL ALTER Table
MySQL ALTER statement is used when you want to change the name of your table or
any table field. It is also used to add or delete an existing column in a table.

The ALTER statement is always used with "ADD", "DROP" and "MODIFY" commands
according to the situation.

1) ADD a column in the table

Syntax:

1. ALTER TABLE table_name

2. ADD new_column_name column_definition
3. [ FIRST | AFTER column_name ];

Parameters
table_name: It specifies the name of the table that you want to modify.

new_column_name: It specifies the name of the new column that you want to add to the
table.

column_definition: It specifies the data type and definition of the column (NULL or NOT
NULL, etc).

FIRST | AFTER column_name: It is optional. It tells MySQL where in the table to create
the column. If this parameter is not specified, the new column will be added to the
end of the table.

Example:

In this example, we add a new column "cus_age" in the existing table "cus_tbl".

Use the following query to do this:

1. ALTER TABLE cus_tbl

 2. ADD cus_age varchar(40) NOT NULL;

Output:

See the recently added column:

1. SELECT* FROM cus_tbl;

Output:

2) Add multiple columns in the table

Syntax:

1. ALTER TABLE table_name

2. ADD new_column_name column_definition
3. [ FIRST | AFTER column_name ],
 4. ADD new_column_name column_definition
5. [ FIRST | AFTER column_name ],
6. ...
7. ;

Example:

In this example, we add two new columns "cus_address", and cus_salary in the
existing table "cus_tbl". cus_address is added after cus_surname column and
cus_salary is added after cus_age column.

Use the following query to do this:

1. ALTER TABLE cus_tbl

2. ADD cus_address varchar(100) NOT NULL
3. AFTER cus_surname,
4. ADD cus_salary int(100) NOT NULL
5. AFTER cus_age ;

See the recently added columns:

1. SELECT* FROM cus_tbl;

3) MODIFY column in the table
The MODIFY command is used to change the column definition of the table.

Syntax:

1. ALTER TABLE table_name

2. MODIFY column_name column_definition
3. [ FIRST | AFTER column_name ];

Example:

In this example, we modify the column cus_surname to be a data type of varchar(50)

and force the column to allow NULL values.

Use the following query to do this:

1. ALTER TABLE cus_tbl

2. MODIFY cus_surname varchar(50) NULL;
See the table structure:

4) DROP column in table

Syntax:

1. ALTER TABLE table_name

2. DROP COLUMN column_name;

Let's take an example to drop the column name "cus_address" from the table
"cus_tbl".

Use the following query to do this:

1. ALTER TABLE cus_tbl

2. DROP COLUMN cus_address;

Output:
See the table structure:

5) RENAME column in table

Syntax:

1. ALTER TABLE table_name

2. CHANGE COLUMN old_name new_name
3. column_definition
4. [ FIRST | AFTER column_name ]

Example:

In this example, we will change the column name "cus_surname" to "cus_title".

Use the following query to do this:

 1. ALTER TABLE cus_tbl
2. CHANGE COLUMN cus_surname cus_title
3. varchar(20) NOT NULL;

Output:

6) RENAME table
Syntax:

1. ALTER TABLE table_name

2. RENAME TO new_table_name;

Example:

In this example, the table name cus_tbl is renamed as cus_table.

1. ALTER TABLE cus_tbl

2. RENAME TO cus_table;

Output:
See the renamed table:

MySQL Show/List Tables

The show or list table is very important when we have many databases that contain
various tables. Sometimes the table names are the same in many databases; in that
case, this query is very useful. We can get the number of table information of a
database using the following statement:

1. mysql> SHOW TABLES;

The following steps are necessary to get the list of tables:

Step 1: Open the MySQL Command Line Client that appeared with a mysql>
prompt. Next, log in to the MySQL database server using the password that you
have created during the installation of MySQL. Now, you are connected to the MySQL
server, where you can execute all the SQL statements.

Step 2: Next, choose the specific database by using the command below:

1. mysql> USE database_name;

Step 3: Finally, execute the SHOW TABLES command.

Let us understand it with the example given below. Suppose we have a database
name "mystudentdb" that contains many tables. Then execute the below statement
to list the table it contains:

1. mysql> USE mystudentdb;

2. mysql>SHOW TABLES;

The following output explains it more clearly:

We can also use the FULL modifier with the SHOW TABLES query to get the type of
table (Base or View) that appears in a second output column.

1. mysql> SHOW FULL TABLES;

This statement will give the following output:

If we want to show or list the table name from different databases or database to
which you are not connected without switching, MySQL allows us to use the FROM or
IN clause followed by the database name. The following statement explains it more
clearly:

1. mysql> SHOW TABLES IN database_name;

The above statement can also be written as:

1. mysql> SHOW TABLES FROM database_name;

When we execute the below statements, we will get the same result:

1. mysql> SHOW TABLES FROM mystudentdb;

2. OR,
3. mysql> SHOW TABLES IN mystudentdb;

Output:

Show Tables Using Pattern Matching

Show Tables command in MySQL also provides an option that allows us to filter the
returned table using different pattern matching with LIKE and WHERE clause.

Syntax
The following are the syntax to use pattern matching with show table command:

1. mysql> SHOW TABLES LIKE pattern;

2. OR,
3. mysql> SHOW TABLES WHERE expression;

We can understand it with the example given below where percent (%) sign assumes
zero, one, or multiple characters:

1. mysql> SHOW TABLES FROM mystudentdb LIKE "stud%";

The above statement will give the following output:

Let us see another statement that returned the table names starting with "time":

1. mysql> SHOW TABLES IN mysql LIKE "time%";

The above query will give the following output:

Now, we are going to see how we can use the WHERE clause with the SHOW TABLES
command to list different types of tables (either Base or View type) in the selected
database:

1. mysql> SHOW TABLES FROM sakila WHERE table_type= "VIEW";

This statement gives the below output:

It is noted that if MySQL does not provide the privileges for accessing a Base table or
view, then we cannot get the tables in the result set of the SHOW TABLES command.

Here, we can also see another example of Show Tables statement with the WHERE
clause:

1. mysql> SHOW TABLES In mystudentdb WHERE Tables_in_mystudentdb= "empl

oyees";

It will give the following output:

MySQL Rename Table

Sometimes our table name is non-meaningful, so it is required to rename or change
the name of the table. MySQL provides a useful syntax that can rename one or more
tables in the current database.
Syntax
The following are the syntax used to change the name of the table:

1. mysql> RENAME old_table TO new_table;

Here, we have to make sure that new_table_name must not exist,

and old_table_name should be present in the database. Otherwise, it will throw an
error message. It is to ensure that the table is not locked as well as there are no
active transactions before executing this statement.

NOTE: If we use the RENAME TABLE statement, it is required to have ALTER and DROP TABLE
privileges to the existing table. Also, this statement cannot change the name of a temporary
table.

We can also use the MySQL RENAME TABLE statement to change more than one
table name with a single statement, as shown below:

1. RENAME TABLE old_tab1 TO new_tab1,

2. old_tab2 TO new_tab2, old_tab3 TO new_tab3;

From the MySQL 8.0.13 version, we can change the old table name locked with a
LOCK statement and also uses the WRITE LOCK clause. For example, following are the
valid statement:

1. mysql> LOCK TABLE old_tab_name1 WRITE;

2. RENAME TABLE old_tab_name1 TO new_tab_name1,
3. new_tab_name1 TO new_tab_name2;

Following statement are not permitted:

1. mysql> LOCK TABLE old_tab_name1 READ;

2. RENAME TABLE old_tab_name1 TO new_tab_name1,
3. new_tab_name1 TO new_tab_name2;

Before MySQL 8.0.13 version, we cannot change the table name that was locked with
the LOCK TABLE statement.

MySQL also use the RENAME TABLE statement for moving a table from one database
to other database, which is show below:

1. mysql> RENAME TABLE current_db.tablel_name TO other_db.tablel_name;

MySQL RENAME TABLE Example
Let us understand how the RENAME TABLE statement works in MySQL through the
various examples. Suppose we have a table named EMPLOYEE, and due to some
reason, there is a need to change it into the table named CUSTOMER.

Table Name: employee

Next, execute the following syntax to change the table name:

1. mysql> RENAME employee TO customer;

Output

We will see that the table named "employee" will be changed into a new table name
"customer":

In the above output, we can see that if we use the table name employee after
executing a RENAME TABLE statement, it will throw an error message.

How to RENAME Multiple Tables

RENAME TABLE statement in MySQL also allows us to change more than one table
name within a single statement. See the below statement:

Suppose our database "myemployeedb" having the following tables:

If we want to change the table name customer into employee and table name shirts
into garments, execute the following statement:

1. mysql> RENAME TABLE customer TO employee, shirts TO garments;

Output

We can see that the table name customer into employee and table name shirts into
garments have successfully renamed.

Rename table using ALTER statement

The ALTER TABLE statement can also be used to rename the existing table in the
current database. The following are the syntax of the ALTER TABLE statement:

1. ALTER TABLE old_table_name RENAME TO new_table_name;

See the following query that changes the existing table name garments into new table
name shirts:

1. mysql> ALTER TABLE garments RENAME TO shirts:

Output
Here, we can see that the table name garments renamed into table name shirts.

How to RENAME Temporary Table

A temporary table allows us to keep temporary data, which is visible and accessible in
the current session only. So, first, we need to create a temporary table using the
following statement:

1. mysql> CREATE TEMPORARY TABLE Students( name VARCHAR(40) NOT NU

LL, total_marks DECIMAL(12,2) NOT NULL DEFAULT 0.00, total_subjects INT U
NSIGNED NOT NULL DEFAULT 0);

Next, insert values into this table:

1. mysql> INSERT INTO Students(name, total_marks, total_subjects) VALUES ('J

oseph', 150.75, 2), ('Peter', 180.75, 2);

Next, run the show table command to check the temporary table:

1. mysql> SELECT * FROM Students;

Now, run the following command to change the name of the temporary table:

1. mysql> RENAME TABLE Students TO student_info;

It will throw an error message, as shown below:

Thus, MySQL allows ALTER table statement to rename the temporary table:

1. mysql> ALTER TABLE Students RENAME TO student_info;

Output

MySQL TRUNCATE Table

The TRUNCATE statement in MySQL removes the complete data without removing its
structure. It is a part of DDL or data definition language command. Generally, we
use this command when we want to delete an entire data from a table without
removing the table structure.

The TRUNCATE command works the same as a DELETE command without using
a WHERE clause that deletes complete rows from a table. However, the TRUNCATE
command is more efficient as compared to the DELETE command because it removes
and recreates the table instead of deleting single records one at a time. Since this
command internally drops the table and recreates it, the number of rows affected by
the truncate statement is zero, unlike the delete statement that returns the number of
deleted rows.

This command does not maintain the transaction log during the execution. It
deallocates the data pages instead of rows and makes an entry for the deallocating
pages instead of rows in transaction logs. This command also locks the pages instead
of rows; thus, it requires fewer locks and resources.

The following points must be considered while using the TRUNCATE command:

o We cannot use the WHERE clause with this command so that filtering of records is not
possible.
o We cannot rollback the deleted data after executing this command because the log
is not maintained while performing this operation.
o We cannot use the truncate statement when a table is referenced by a foreign key or
participates in an indexed view.
o The TRUNCATE command doesn't fire DELETE triggers associated with the table that is
being truncated because it does not operate on individual rows.

Syntax
The following syntax explains the TRUNCATE command to remove data from the
table:

1. TRUNCATE [TABLE] table_name;

In this syntax, first, we will specify the table name which data we are going to
remove. The TABLE keyword in the syntax is not mandatory. But it's a good practice
to use it to distinguish between the TRUNCATE() function and the TRUNCATE TABLE
statement.

MySQL Truncate Table Example

Let us demonstrate how we can truncate the table with the help of an example. First,
we are going to create a table named "customer" using the below statement:

1. CREATE TABLE customer (

2. Id int PRIMARY KEY NOT NULL,
3. Name varchar(45) NOT NULL,
4. Product varchar(45) DEFAULT NULL,
5. Country varchar(25) DEFAULT NULL,
6. Year int NOT NULL
7. );

Next, we will add values to this table using the below statement:
 1. INSERT INTO customer ( Id, Name, Product, Country, Year)
2. VALUES (1, 'Stephen', 'Computer', 'USA', 2015),
3. (2, 'Joseph', 'Laptop', 'India', 2016),
4. (3, 'John', 'TV', 'USA', 2016),
5. (4, 'Donald', 'Laptop', 'England', 2015),
6. (5, 'Joseph', 'Mobile', 'India', 2015),
7. (6, 'Peter', 'Mouse', 'England', 2016);

Now, verify the table by executing the SELECT statement whether the records
inserted or not:

1. mysql> SELECT * FROM customer;

We will get the output, as shown below:

Now, execute the following statement that truncates the table customer using the
TRUNCATE syntax discussed above:

1. mysql> TRUNCATE TABLE customer;

After the successful execution, we will get the following output:

As we can see, this query returns 0 rows are affected even if all the table records
are deleted. We can verify the deletion of the data by executing the SELECT
statement again. This command gives the following output that shows none of the
records present in the table:
How to Truncate Table with Foreign key?
If we perform the TRUNCATE operation for the table that uses a foreign key constraint,
we will get the following error:

1. ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key c
onstraint fails

In that case, we need to log into the MySQL server and disable foreign key checks
before executing the TRUNCATE statement as below:

1. SET FOREIGN_KEY_CHECKS=0;

Now, we are able to truncate tables. After execution, re-enable foreign key checks
as given below:

1. SET FOREIGN_KEY_CHECKS=1;
How to truncate all tables in MySQL?
The TRUNCATE statement in MySQL will delete only one table at a time. If we want to
delete more than one table, we need to execute the separate TRUNCATE statement.
The below example shows how to truncate multiple tables in MySQL:

1. TRUNCATE TABLE table_name1;

2. TRUNCATE TABLE table_name2;
3. TRUNCATE TABLE table_name3;

We can also use the below SQL query that generates several TRUNCATE TABLE
commands at once using the table names in our database:

1. SELECT Concat('TRUNCATE TABLE ', TABLE_NAME)

2. FROM INFORMATION_SCHEMA.TABLES
3. WHERE table_schema = 'database_name';
MySQL DESCRIBE TABLE
DESCRIBE means to show the information in detail. Since we have tables in MySQL, so
we will use the DESCRIBE command to show the structure of our table, such as
column names, constraints on column names, etc. The DESC command is a short
form of the DESCRIBE command. Both DESCRIBE and DESC command are equivalent
and case sensitive.

Syntax
The following are the syntax to display the table structure:

1. {DESCRIBE | DESC} table_name;

We can use the following steps to show all columns of the table:

Step 1: Login into the MySQL database server.

Step 2: Switch to a specific database.

Step 3: Execute the DESCRIBE statement.

Let us understand it with the help of an example that explains how to show columns
of the table in the selected database.

Login to the MySQL Database

The first step is to login to the database server using the username and password.
We should see the output as below image:

1. >mysql -u root -p
2. Enter password: **********
3. mysql>
Switch to a Specific Database
The next step is to open a particular database from which you want to display the
table information using the following query. After the execution of a query, we should
see the below output:

1. mysql> USE mysqltestdb;

Execute DESCRIBE Statement

It is the last step to display the table information. Before executing the DESCRIBE
statement, we can optionally display all the tables stored in our selected database
with the SHOW TABLES statement:

1. mysql> SHOW TABLES;

For example, if we want to show a customer table's structure, execute the below
statement. After successful execution, it will give the output as below image:

1. mysql> DESCRIBE customer;

We can also use the DESC statement for practice, which is a shorthand of the
DESCRIBE command. See the below output:

How to display table information in MySQL Workbench?

To display the column information of the table in MySQL Workbench, we first need to
launch the Workbench tool and login with the username and password to
the MySQL database server. We will get the following screen:

Now do the following steps to show the table information:

1. Go to the Navigation tab and click on the Schema menu. Here, we can see all
the previously created databases. Select any database under the Schema menu, for
example, mysqltestdb. It will pop up the multiple options that can be shown in the
following image.

2. Next, click on the "Tables" that shows all tables stored in the mysqltestdb
database. Select a table whose column information you want to display. Then, mouse
hour on that table, it will show three icons. See the below image:

Now, click the icon (i) shown in the red rectangular box. It will display the following
image:
Finally, click on the "Columns" menu to display the table structure.

MySQL SHOW COLUMNS Command

MySQL also allows the SHOW COLUMNS command to display table structure. It is a
more flexible way to get columns information of a table.

Syntax:

The following are the syntax of the SHOW COLUMNS command:

1. mysql> SHOW COLUMNS FROM table_name;

For example, if we execute the below query, we will get all columns information of a
table in a particular database:

1. mysql> SHOW COLUMNS FROM customer;

If we want to show the columns information of a table from another
database or not available in the current database, we can use the following query:

1. mysql> SHOW COLUMNS FROM database_name.table_name;

2.
3. OR
4.
5. mysql> SHOW COLUMNS FROM table_name IN database_name;

In the below image, we can see that we had used the mysqltestdb database. But we
had displayed the column's information of a table from another database without
switching to the current database.

If we want to display the more column information, we need to add FULL keyword
with the SHOW TABLES statement as follows:

1. mysql> SHOW FULL COLUMNS FROM table_name;

For example, the below SQL query lists all columns of the student_info table in
the mystudentdb database:

1. mysql> SHOW FULL COLUMNS FROM student_info;

After execution, we can see that this command adds the collation, privileges,
default, and comment columns to the result set.
MySQL EXPLAIN
The EXPLAIN keyword is synonyms to the DESCRIBE statement, which is used to
obtain information about how MySQL executes the queries. It can work
with INSERT, SELECT, DELETE, UPDATE, and REPLACE queries. From MySQL
8.0.19 and later versions, it can also work with TABLE statements. When we use this
keyword in queries, it will process the statement and provide the information about
how tables are joined, the order of the table, estimated partitions and rows.

Example

If we want to show the execution plan of a SELECT statement, we can use the query
as below:

1. mysql> EXPLAIN SELECT * FROM customer;

Output:

This query produces the following information:

MySQL DROP Table
MYSQL uses a Drop Table statement to delete the existing table. This statement
removes the complete data of a table along with the whole structure or definition
permanently from the database. So, you must be very careful while removing the
table because we cannot recover the lost data after deleting it.

Syntax
The following are the syntax to remove the table in MySQL:

1. mysql> DROP TABLE table_name;

2. OR,
3. mysql> DROP TABLE schema_name.table_name;

The full syntax of DROP TABLE statement in MySQL is:

1. DROP [ TEMPORARY ] TABLE [ IF EXISTS ] table_name [ RESTRICT | CASCAD

E ];

The above syntax used many parameters or arguments. Let us discuss each in detail:

Parameter Name Description

TEMPORARY It is an optional parameter that specifies to delete the temporary tables only.

table_name It specifies the name of the table which we are going to remove from the data

IF EXISTS It is optional, which is used with the DROP TABLE statement to remove the tab

RESTRICT and Both are optional parameters that do not have any impact or effect on this st
CASCADE for future versions of MySQL.

NOTE: It is to be noted that you must have a DROP privileges to execute the DROP TABLE
statement in the MySQL.
Example
This example specifies how we can drop an existing table from the database. Suppose
our database contains a table "orders" as shown in the image below:
To delete the above table, we need to run the following statement:

1. mysql> DROP TABLE orders;

It will remove the table permanently. We can also check the table is present or not as
shown in the below output:

If we try to delete a table that does not exist in the database, we will get an error
message as given below:

If we use the IF EXISTS clause with the DROP TABLE statement, MySQL gives the
warning message which can be shown in the below output:

How to DROP table in Workbench

1. To delete a table, you need to choose the table, right-click on it, and select the
Drop Table option. The following screen appears:
2. Select Drop Now option in the popup window to delete the table from the
database instantly.

MySQL DROP Multiple Table

Sometimes we want to delete more than one table from the database. In that case,
we have to use the table names and separate them by using the comma operator.
The following statement can be used to remove multiple tables:

1. DROP TABLE IF EXISTS table_name1, table_name2, table, ......., table_nameN;

MySQL TRUNCATE Table vs. DROP Table
You can also use the DROP TABLE command to delete the complete table, but it will
remove complete table data and structure both. You need to re-create the table again
if you have to store some data. But in the case of TRUNCATE TABLE, it removes only
table data, not structure. You don't need to re-create the table again because the
table structure already exists.

MySQL Temporary Table

MySQL has a feature to create a special table called a Temporary Table that allows
us to keep temporary data. We can reuse this table several times in a particular
session. It is available in MySQL for the user from version 3.23, and above so if we
use an older version, this table cannot be used. This table is visible and accessible
only for the current session. MySQL deletes this table automatically as long as the
current session is closed or the user terminates the connection. We can also use
the DROP TABLE command for removing this table explicitly when the user is not
going to use it.

If we use a PHP script to run the code, this table removes automatically as long as
the script has finished its execution. If the user is connected with the server through
the MySQL client, then this table will exist until the user closes the MySQL client
program or terminates the connection or removed the table manually.

A temporary table provides a very useful and flexible feature that allows us to achieve
complex tasks quickly, such as when we query data that requires a single SELECT
statement with JOIN clauses. Here, the user can use this table to keep the output and
performs another query to process it.

A temporary table in MySQL has many features, which are given below:

o MySQL uses the CREATE TEMPORARY TABLE statement to create a temporary table.
o This statement can only be used when the MySQL server has the CREATE TEMPORARY
TABLES privilege.
o It can be visible and accessible to the client who creates it, which means two different
clients can use the temporary tables with the same name without conflicting with each
other. It is because this table can only be seen by that client who creates it. Thus, the
user cannot create two temporary tables with the same name in the same session.
o A temporary table in MySQL will be dropped automatically when the user closes the
session or terminates the connection manually.
o A temporary table can be created by the user with the same name as a normal table in
a database. For example, if the user creates a temporary table with the name student,
then the existing student table cannot be accessible. So, the user performs any query
against the student table, is now going to refer to the temporary student table. When
the user removes a temporary table, the permanent student table becomes accessible
again.

Syntax of Creating Temporary Table

In MySQL, the syntax of creating a temporary table is the same as the syntax of
creating a normal table statement except the TEMPORARY keyword. Let us see the
following statement which creates the temporary table:

1. mysql> CREATE TEMPORARY TABLE table_name (

2. column_1, column_2, ..., table_constraints
3. );

If the user wants to create a temporary table whose structure is the same as an
existing table in the database, then the above statement cannot be used. Instead, we
use the syntax as given below:
 1. Mysql> CREATE TEMPORARY TABLE temporary_table_name SELECT * FROM
original_table_name LIMIT 0;
MySQL Temporary Table Example
Let us understand how we can create a temporary table in MySQL. Execute the
following statement that creates a temporary table in the selected database:

1. mysql> CREATE TEMPORARY TABLE Students( student_name VARCHAR(40)

NOT NULL, total_marks DECIMAL(12,2) NOT NULL DEFAULT 0.00, total_subject
s INT UNSIGNED NOT NULL DEFAULT 0);

We can see the below image:

Next, we need to insert values in the temporary table:

1. mysql>INSERT INTO Students(student_name, total_marks, total_subjects) VAL

UES ('Joseph', 150.75, 2), ('Peter', 180.75, 2);

After executing the above statement, it will give the below output:

Now, run the following query to get the result:

1. mysql> SELECT * FROM Students;

After the successful execution of the above statement, we will get the output as
below:
It is to be noted that when we run a SHOW TABLES command, then our temporary
table will not be shown on the list. Also, if we close the current session and then will
execute the SELECT statement, we will get a message saying that no data available in
the database, and even the temporary table will not exist.

A Temporary Table whose structure is based on a normal table

In this example, we are going to create a temporary table whose structure is based on
the already available tables in the database.

Suppose our database has the following table as permanent:

Here, the structure of a temporary table is created by using the SELECT statement
and merge two tables using the INNER JOIN clause and sorts them based on the price.
Write the following statement in the MySQL prompt:

1. CREATE TEMPORARY TABLE temp_customers

2. SELECT c.cust_name, c.city, o.prod_name, o.price
3. FROM orders o
4. INNER JOIN customer c ON c.cust_id = o.order_id
5. ORDER BY o.price DESC;

When we execute the above statement, we will get the following message:
Now, run the below command to see the temporary table:

1. mysql> SELECT * FROM temp_customers;

We can also perform queries from the above temporary table "temp_customers"
similar to the querying data from a permanent table. The following query explains it
more clearly:

1. Mysql> SELECT cust_name, prod_name, price FROM temp_customers;

After executing the above statement, it will give the output as below:

NOTE: It is noted that we can use IF NOT EXISTS keyword to avoid the "table already exists"
error.
How to Drop Temporary Table in MySQL
MySQL allows us to remove the temporary table using the DROP TABLE statement.
But, it"s a good practice to use the TEMPORARY keyword with the DROP TABLE
statement. This keyword helps us to avoid the mistake of deleting a permanent table
when the temporary table and permanent table have the same name in the current
session. So, it is recommended to use the following query for removing the temporary
table:

1. mysql> DROP TEMPORARY TABLE table_name;

This query will not remove a permanent table of the database that means it only
deletes a temporary table. If we try to delete a permanent table with this statement, it
will throw an error message saying that you are deleting a table is unknown. For
example, if we want to remove the above temporary table "temp_customers", we
need to use the following statement:

1. mysql> DROP TEMPORARY TABLE top_customers;

MySQL Copy/Clone/Duplicate Table
MySQL copy or clone table is a feature that allows us to create a duplicate table of an
existing table, including the table structure, indexes, constraints, default values, etc.
Copying data of an existing table into a new table is very useful in a situation like
backing up data in table failure. It is also advantageous when we need to test or
perform something without affecting the original table, for example, replicating the
production data for testing.

We can copy an existing table to a new table using the CREATE

TABLE and SELECT statement, as shown below:

1. CREATE TABLE new_table_name

2. SELECT column1, column2, column3
3. FROM existing_table_name;

From the above, first, it creates a new table that indicates in the CREATE TABLE
statement. Second, the result set of a SELECT statement defines the structure of a
new table. Finally, MySQL fills data getting from the SELECT statement to the newly
created table.

If there is a need to copy only partial data from an existing table to a new table,
use WHERE clause with the SELECT statement as shown below:

1. CREATE TABLE new_table_name

2. SELECT column1, column2, column3
3. FROM existing_table_name
4. WHERE condition;

We have to ensure that the table we are going to create should not already exist in
our database. The IF NOT EXISTS clause in MySQL allows us to check whether a
table exists in the database or not before creating a new table. So, the below
statement explains it more clearly:

1. CREATE TABLE IF NOT EXISTS new_table_name

2. SELECT column1, column2, column3
3. FROM existing_table_name
4. WHERE condition;

It is to be noted that this statement only copies the table and its data. It doesn't copy
all dependent objects of the table, such as indexes, triggers, primary key constraints,
foreign key constraints, etc. So the command of copying data along with its
dependent objects from an existing to the new table can be written as the following
statements:

1. CREATE TABLE IF NOT EXISTS new_table_name LIKE existing_table_name;

2.
3. INSERT new_table_name SELECT * FROM existing_table_name;

In the above, we can see that we need to execute two statements for copying data
along with structure and constraints. The first command creates a new
table new_table_name that duplicates the existing_table_name, and the second
command adds data from the existing table to the new_table_name.

MySQL Copy/Clone Table Example

Let us demonstrate how we can create a duplicate table with the help of an example.
First, we are going to create a table named "original_table" using the below
statement:

1. CREATE TABLE original_table (

2. Id int PRIMARY KEY NOT NULL,
3. Name varchar(45) NOT NULL,
4. Product varchar(45) DEFAULT NULL,
5. Country varchar(25) DEFAULT NULL,
6. Year int NOT NULL
7. );

Next, it is required to add values to this table. Execute the below statement:

1. INSERT INTO original_table( Id, Name, Product, Country, Year)

2. VALUES (1, 'Stephen', 'Computer', 'USA', 2015),
3. (2, 'Joseph', 'Laptop', 'India', 2016),
4. (3, 'John', 'TV', 'USA', 2016),
5. (4, 'Donald', 'Laptop', 'England', 2015),
6. (5, 'Joseph', 'Mobile', 'India', 2015),
7. (6, 'Peter', 'Mouse', 'England', 2016);

Next, execute the SELECT statement to display the records:

1. mysql> SELECT * FROM original_table;

We will get the output, as shown below:

Now, execute the following statement that copies data from the existing table
"original_table" to a new table named "duplicate_table" in the selected database.

1. CREATE TABLE IF NOT EXISTS duplicate_table

2. SELECT * FROM original_table;

After the successful execution, we can verify the table data using the SELECT
statement. See the below output:
Sometimes there is a need to copy only partial data from an existing table to a new
table. In that case, we can use the WHERE clause with the SELECT statement as
follows:

1. CREATE TABLE IF NOT EXISTS duplicate_table

2. SELECT * FROM original_table WHERE Year = '2016';

This statement creates a duplicate table that contains data for the year 2016 only.
We can verify the table using a SELECT statement, as shown below:

Suppose there a need to copy an existing table along with all dependent objects
associated with the table, execute the two statements that are given below:

1. mysql> CREATE TABLE duplicate_table LIKE original_table;

2. AND,
3. mysql> INSERT duplicate_table SELECT * FROM original_table;

Let us see how we can copy a table to a different database through an

example.
Suppose there is a situation to copy a table from a different database. In that case, we
need to execute the below statements:

1. CREATE TABLE destination_db.new_table_name

2. LIKE source_db.existing_table_name;
3.
4. INSERT destination_db.new_table_name
5. SELECT * FROM source_db.existing_table_name;

In the above, the first command creates a new table in the selected(destination)
database by cloning the existing table from the source database. The second
command copies data from the existing table to the new table in the selected
database.

The following demonstration explains it more clearly.

Suppose we have two databases named "mysqltestdb" and "mystudentdb" on the

MySQL Server. The mytestdb database contains a table named "original_table" that
have the following data:

Now, we are going to copy this table into another database named mystudentdb using
the following statement:

1. CREATE TABLE mystudentdb.duplicate_table

2. LIKE mysqltestdb.original_table;
3.
4. INSERT mystudentdb.duplicate_table
5. SELECT * FROM mysqltestdb.original_table;

After successful execution, we can verify the table in mystudentdb database using the
below command:

1. mysql> SELECT * FROM mystudentdb.duplicate_table;

In the below output, we can see that the table is successfully copied into one
database to another database.
MySQL REPAIR TABLE
How to Fix a Corrupted Table in MySQL?
MySQL Repair Table allows us to repair or fix the corrupted table. The repair table in
MySQL provides support only for selected storage engines, not for all. It is to ensure that we
have a few privileges like SELECT and INSERT to use this statement. Normally, we
should never use the repair table until disastrous things happen with the table. This
statement rarely gets all data from the MyISAM table. Therefore, we need to find why
our table is corrupted to eliminate the use of this statement.

When we execute the REPAIR TABLE statement, it first checks the table that we are
going to repair is required an upgradation or not. If required, it will perform
upgradation with the same rules as CHECK TABLE ... FOR UPGRADE statement works.
It is always good to keep our table's backup before performing the "table repair"
option because it might cause a loss of our data.

Syntax
The following is the syntax to repair a corrupted table in MySQL:

1. REPAIR [NO_WRITE_TO_BINLOG | LOCAL]

2. TABLE tbl_name [, tbl_name] ...
3. [QUICK] [EXTENDED] [USE_FRM]

Let us discuss the use of each option in detail.

NO_WRITE_TO_BINLOG or LOCAL: It's a place where the server is responsible for

writing the REPAIR TABLE statements for the replication slaves. We can optionally
specify the optional NO_WRITE_TO_BINLOG/LOCAL keyword to suppress the logging.

QUICK: The quick option allows the REPAIR TABLE statement for repairing only the
index file. It does not allow to repair of the data file. This type of repair gives the same
result as the myisamchk --recover -quick command works.

EXTENDED: Instead of creating the index row by row, this option allows MySQL to
create one index at a time with sorting. This type of repair gives the same result as
the myisamchk --safe-recover command works.

USE_FRM: This option is used when the .MYI index file is not found or if its header is
corrupted. The USE-FRM option informs MySQL to do not trust the information present
in this file header and re-create it by using the information provided from the data
dictionary. This type of repair cannot work with the myisamchk command.

Storage Engine and Partitioning Support with Repair Table

We have mentioned earlier that the repair table does not work for all storage engines.
It supports only MyISAM, ARCHIVE, and CSV tables. The repair table statement does not
support views.

We can also use the repair table statement for partitioned tables. But, here, we
cannot use the USE_FRM option with this statement. If we want to repair multiple
partitions, we can use the ALTER TABLE ... REPAIR PARTITION statement.
MySQL REPAIR TABLE Example
Let us understand the working of the repair table statement in MySQL through
example. First, we need to create a new table named vehicle in the selected database
as follows:

1. CREATE TABLE vehicle (

2. vehicle_no VARCHAR(18) PRIMARY KEY,
3. model_name VARCHAR(45),
4. cost_price DECIMAL(10,2 ),
5. sell_price DECIMAL(10,2)
6. );

Next, we will insert some data into this table with the below statement:

1. mysql> INSERT INTO vehicle (vehicle_no, model_name, cost_price, sell_price)

2. VALUES('S2001', 'Scorpio', 950000, 1000000),
3. ('M3000', 'Mercedes', 2500000, 3000000),
4. ('R0001', 'Rolls Royas', 75000000, 85000000);

Next, execute the below statement to verify the data:

1. mysql> SELECT * FROM vehicle;

We should get the below result:

Next, we will execute the below statement to check the storage engine of the vehicle
table:

1. mysql> SELECT table_name, engine

2. FROM information_schema.tables
3. WHERE table_name = 'vehicle';

After executing the statement, we should get the below output:

Here we can see that the storage engine of the vehicle table is InnoDB. Therefore, if
we create the repair table using the below query for this storage engine, MySQL
issued an error:

1. mysql> REPAIR TABLE vehicle;

See the below output:

To remove this error, we first need to alter the table storage engine to MyISAM with
the following query and then used the repair table statement.

1. mysql> ALTER TABLE vehicle ENGINE = 'MyISAM';

2. //Now, use the repair table query
3. mysql> REPAIR TABLE vehicle;

We will get the below output:

In this output, we can see that the REPAIR TABLE statement contains the following columns in the
result set:

SN Column Name Descriptions

 1. Table This column indicates the name of the table.

2. Op This column always contains repair word whether the storage engine su

3. Msg_type This column can be either status, error, info, note, or warning.

4. Msg_text This column consists of the informational message.

Let us see another example to use a repair table statement with any QUICK,
EXTENDED or USE_FRM options. Thus, we will first create another table
named memberships and stored this table in the "MyISAM" storage engine instead of
the default one InnoDB.

1. CREATE TABLE memberships (

2. id INT AUTO_INCREMENT PRIMARY KEY,
3. name VARCHAR(55) NOT NULL,
4. email VARCHAR(55) NOT NULL,
5. plan VARCHAR(45) NOT NULL,
6. validity_date DATE NOT NULL
7. ) ENGINE = MyISAM;

We will insert some data into this table with the below statement:

1. mysql> INSERT INTO memberships (name, email, plan, validity_date)

2. VALUES('Stephen', 'stephen@javatpoint.com', 'Gold', '2020-06-13'),
3. ('Jenifer', 'jenifer@javatpoint.com', 'Platinum', '2020-06-10'),
4. ('david', 'david@javatpoint.com', 'Silver', '2020-06-15');

Next, execute the SELECT statement to verify the data. We will get the below result:

Since we have created the MyISAM storage engine table, the repair table statement
does not issue any error. See the below statement:

1. mysql> REPAIR TABLE memberships QUICK EXTENDED;

We should get the output as follows:

If we use the REPAIR TABLE statement with the table that does not exist in our
selected database, MySQL gives an error message. See the below statement:

1. mysql> REPAIR TABLE service_memberships QUICK EXTENDED;

After execution, we will get the following output:

In this article, we have learned how to repair the corrupted table in MySQL using the
Repair Table statement. This statement works only for certain storage engines. Thus,
before using this query, we first check the table storage engine supports it or not. If it
is not supported, we need to change it into MyISAM, ARCHIVE, or CSV. It is always
good to keep our table's backup before performing the "table repair" query because it
might cause a loss of our data.
MySQL Add/Delete Column
A column is a series of cells in a table that may contain text, numbers, and images.
Every column stores one value for each row in a table. In this section, we are going to
discuss how to add or delete columns in an existing table.

How can we add a column in MySQL table?

MySQL allows the ALTER TABLE ADD COLUMN command to add a new column to an
existing table. The following are the syntax to do this:

1. ALTER TABLE table_name

2. ADD COLUMN column_name column_definition [FIRST|AFTER existing_column];

In the above,

o First, we need to specify the table name.

o Next, after the ADD COLUMN clause, we have to specify the name of a new column
along with its definition.
o Finally, we need to specify the FIRST or AFTER keyword. The FIRST Keyword is used to
add the column as the first column of the table. The AFTER keyword is used to add a
new column after the existing column. If we have not provided these keywords, MySQL
adds the new column as the last column in the table by default.

Sometimes it is required to add multiple columns into the existing table. Then, we
can use the syntax as follows:

1. ALTER TABLE table_name

2. ADD COLUMN column_name1 column_definition [FIRST|AFTER existing_column],
3. ADD COLUMN column_name2 column_definition [FIRST|AFTER existing_col
umn];
MySQL ADD COLUMN Example
Let us understand it with the help of various examples. Here, we will create a table
named "Test" using the following statements:

1. CREATE TABLE Test (

2. Stude_id int AUTO_INCREMENT PRIMARY KEY,
3. Name varchar(55) NOT NULL
4. );
The table structure looks like the below image:

After creating a table, we want to add a new column named City to the Test table.
Since we have not specified the new column position explicitly after the column name,
MySQL will add it as the last column.

1. ALTER TABLE Test

2. ADD COLUMN City VARCHAR(30) NOT NULL;

Next, we want to add a new column named Phone_number to the Test table. This
time, we will explicitly specify the new column position so that MySQL adds the
column to the specified place.

1. ALTER TABLE Test

2. ADD COLUMN Phone_number VARCHAR(20) NOT NULL AFTER Name;

In the below output, we can see that the two columns are added successfully at the
specified position.

Let us add some data into the Test table using the INSERT statement as follows:

1. INSERT INTO Test( Name, Phone_number, City)

 2. VALUES ('Peter', '34556745362', 'California'),
3. ('Mike', '983635674562', 'Texas');

It will look like this.

Suppose we want to add more than one column ,(Branch, Email) in the Test table. In
that case, execute the statement as follows:

1. ALTER TABLE Test

2. ADD COLUMN Branch VARCHAR(30) DEFAULT NULL After Name,
3. ADD COLUMN Email VARCHAR(20) DEFAULT NULL AFTER Phone_number;

It is to note that columns Branch and Email are assigned to default value NULL.
However, the Test table already has data so that MySQL will use null values for those
new columns.

We can verify the record in the Test table as below:

If we accidentally add a new column with the existing column name, MySQL
will throw an error. For example, execute the below statement that issues an error:

1. ALTER TABLE Test

2. ADD COLUMN City VARCHAR(30) NOT NULL;
We will get the following error message.

How can we rename a column in MySQL table?

MySQL allows the ALTER TABLE CHANGE COLUMN statement to change the old
column with a new name. The following are the syntax to do this:

1. ALTER TABLE table_name

2. CHANGE COLUMN old_column_name new_column_name column_definition [FIRST|
AFTER existing_column];

In the above,

o First, we need to specify the table name.

o Next, after the CHANGE COLUMN clause, we have to specify the old column name and
new column name along with its definition. We must have to specify the column
definition even it will not change.
o Finally, we need to specify the FIRST or AFTER keyword. It is optional that specified
when we need to change the column name at the specific position.

MySQL RENAME COLUMN Example

This example shows how we can change the column name in the MySQL table:

1. ALTER TABLE Test

2. CHANGE COLUMN Phone_number Mobile_number
3. varchar(20) NOT NULL;

This statement will change the column name Phone_number with the new
name Mobile_number in the Test table. The below output explains it more clearly.
How can we drop a column from MySQL table?
Sometimes, we want to remove single or multiple columns from the table. MySQL
allows the ALTER TABLE DROP COLUMN statement to delete the column from the
table. The following are the syntax to do this:

1. ALTER TABLE table_name DROP COLUMN column_name;

In the above,

o First, we need to specify the table name from which we want to remove the column.
o Next, after the DROP COLUMN clause, we have to specify the column name that we
want to delete from the table. It is to note that the COLUMN keyword is optional in the
DROP COLUMN clause.

If we want to remove multiple columns from the table, execute the following
statements:

1. ALTER TABLE table_name

2. DROP COLUMN column_1,
3. DROP COLUMN column_2,
4. ......;

MySQL DROP COLUMN Example

This example explains how we can delete a column from the MySQL table. Here, we
will take a table "Test" that we have created earlier and look like the below image:

Suppose we want to delete a column name "Branch" from the Test table. To do this,
execute the below statement:

1. ALTER TABLE Test DROP COLUMN Branch;

After successful execution, we can verify the result below where a column Branch is
deleted from the table:
In some cases, it is required to remove multiple columns from the table. To do this, we
need to execute the below statement:

1. ALTER TABLE Test

2. DROP COLUMN Mobile_number,
3. DROP COLUMN Email;

The command will delete both columns. We can verify it using the queries given in the
below image.

Remember the following key points before deleting a column from the table:

MySQL works with relational databases where the schema of one table can depend on
the columns of another table. So when we remove a column from one table, it will
effects all dependent tables also. Consider the below points while removing column:

o When we remove columns from a table, it will affect all associated objects such as
triggers, stored procedures, and views. Suppose we delete a column that is referencing
in the trigger. After removing the column, the trigger becomes invalid.
o The dropped column depends on other applications code, must also be changed, which
takes time and effort.
o When we remove a column from the large table, it will affect the database's
performance during removal time.
MySQL Show Columns
Columns in the table are a series of cells that can stores text, numbers, and images.
Every column stores one value for each row in a table. When we work with the MySQL
server, it is common to display the column information from a particular table. In this
section, we are going to discuss how to display or list columns in an existing table.

MySQL provides two ways for displaying the column information:

1. MySQL SHOW COLUMNS Statement

2. MySQL DESCRIBE Statement

Let us discuss both in detail.

MySQL SHOW COLUMNS Statement

SHOW COLUMNS statement in MySQL is a more flexible way to display the column
information in a given table. It can also support views. Using this statement, we will
get only that column information for which we have some privilege.

Syntax
The following is a syntax to display the column information in a specified table:

1. SHOW [EXTENDED] [FULL] {COLUMNS | FIELDS}

2. {FROM | IN} table_name
3. [{FROM | IN} db_name]
4. [LIKE 'pattern' | WHERE expr]

Let's discuss the syntax parameters in detail.

The EXTENDED is an optional keyword to display the information, including hidden

columns. MySQL uses hidden columns internally that are not accessible by users.

The FULL is also an optional keyword to display the column information, including
collation, comments, and the privileges we have for each column.

The table_name is the name of a table from which we are going to show column
information.

The db_name is the name of a database containing a table from which we will show
column information.

The LIKE or WHERE clause is used to display only the matched column information in
a given table.

We can also use the alternative of table_name FROM db_name syntax as

db_name.tbl_name. Therefore, the below statements are equivalent:

1. SHOW COLUMNS FROM mytable_name FROM mydb_name;

2. OR,
3. SHOW COLUMNS FROM mydb_name.mytable_name;
SHOW COLUMNS Statement Example
Let us understand how this statement works in MySQL through various examples.

Suppose we have a table named student_info in a sample database that contains

the data as follows:
Next, if we want to get the columns information of this table, we can use the
statement as follows:

1. mysql> SHOW COLUMNS FROM student_info;

We will see the below output:

If we want to filter the columns of a table, we need to use the LIKE or WHERE clause in
the statement. See the below query:

1. mysql> SHOW COLUMNS FROM student_info LIKE 's%';

This query shows the column information that starts with the letter S only. See the
below output:

If we want to display hidden column information, we need to add the FULL keyword to
the SHOW COLUMNS statement as follows:
 1. mysql> SHOW FULL COLUMNS FROM student_info;

It returns the below output that displays all columns information of the student_info
table in the sample database.

The SHOW COLUMNS statement provides the below information for each
column in a given table:

Field: It indicates the name of the column in a given table.

Type: It indicates the data type of each column.

Collation: It is used to sequence the order of a specific character set. Here it

indicates the string column for non-binary values and NULL for other columns. We will
see this column only when we use the FULL keyword.

Null: It indicates the nullability of a column. If a column can store NULL values, it
returns YES. And if a column cannot store NULL value, it contains NO value.

Key: It indicates the indexing of the columns as PRI, UNI, and MUL. Let us understand
this field in detail.

o If we have not specified any key, it means the column is not indexed. Otherwise, index
as a secondary column in a multiple-column.
o If the column is specified as a PRI, it means the column is a PRIMARY KEY or one of the
fields in a multiple-column PRIMARY KEY.
o If the column is specified as a UNI, it means the column contains a UNIQUE index.
o If the column is specified as a MUL, it means the column is the first column of a non-
unique index where we can use a given value multiple times.
o If the column is specified by more than one key-value, this field displays the key which
has the highest priority (the key priority is in the order of PRI, UNI, and MUL).

Default: It indicates the default value to the column. If the column includes no
DEFAULT clause or has an explicit NULL default, it contains a NULL value.

Extra: It indicates the additional information related to a given column. This field is
non-empty in the following cases:

o If the column is specified with the AUTO_INCREMENT attribute, its value is filled with
auto_increment.
 o If the column is specified with TIMESTAMP or DATETIME that have the ON UPDATE
CURRENT_TIMESTAMP attribute, its value is filled with on update CURRENT_TIMESTAMP.
o For the generated columns, its value filled with VIRTUAL GENERATED or VIRTUAL
STORED.
o If the column contains an expression default value, its value is filled with
DEFAULT_GENERATED.

Privileges: It indicates the privileges that we have for the column. We will see this
column only when we use the FULL keyword.

Comment: It indicates the comment that we have included in the column definition.
We will see this column only when we use the FULL keyword.

MySQL DESCRIBE Statement

DESCRIBE statement in MySQL is also provides information similar to the SHOW
COLUMNS command.

Syntax
The following is the syntax to display the column information in a given table:

1. {DESCRIBE | DESC} table_name;

In this syntax, the DESCRIBE and DESC clause return the same result.

Example
If we want to show column information of students_info table, we can execute the
below statement.

1. mysql> DESCRIBE students_info;

After successful execution, it will give the output as below image:

How to display column information in MySQL Workbench?
We first launch the tool and log in with the username and password to display the
given table's column information in MySQL Workbench. Now, we need to do the
following steps to show the column information:

1. Go to the Navigation tab and click on the Schema menu where all the previously
created databases available. Select your desired database (for
example, mstudentdb). It will pop up the following options.

2. Click on the Tables that show all tables stored in the mysqltestdb database.
Select a table whose column information you want to display. Then, mouse hour on
that table, it will show three icons. See the below image:

3. Click the icon (i) shown in the red rectangular box. We should get the screen as
follows:
4. Finally, click on the "Columns" menu. We can see the column information as like
below output.

MySQL Rename Column

Sometimes our column name is non-meaningful, so it is required to rename or change
the column's name. MySQL provides a useful syntax that can rename one or more
columns in the table. Few privileges are essential before renaming the column, such
as ALTER and DROP statement privileges.

MySQL can rename the column name in two ways:

 1. Using the CHANGE statement
2. Using the RENAME statement

Using the CHANGE Statement:

The following are the syntax that illustrates the column rename using the CHANGE
statement:

1. ALTER TABLE table_name

2. CHANGE COLUMN old_column_name new_column_name Data Type;

In this syntax, we can see that we may require re-specification of all the column
attributes. This syntax can also allow us to change the column's data types. But,
sometimes the CHANGE statement might have the following disadvantages:

o All information of column attributes might not be available to the application for
renaming.
o There is a risk of accidental data type change that might result in the application's data
loss.

Example
Let us understand how the CHANGE statement works in MySQL to rename a column
through the various examples. Suppose we have a table named balance that
contains the data as follows:

Due to some reason, we need to change the column name account_num along
with its data type. In that case, we first check the structure of the table using
the DESCRIBE statement as follows:
In this image, we can see that the data type of column name account_num is int.
And we want to change this column name as account_no and its data type as int to
varchar. Thus, we can execute the below statement to do this:

1. mysql> ALTER TABLE balance

2. CHANGE COLUMN account_num account_no VARCHAR(25);

After executing the above command, we can verify it by using the DESCRIBE
statement again. In the below image, the column name account_num and its data
type have changed successfully.

Using the RENAME Statement:

To remove the drawback of a CHANGE statement, MySQL proposed the following
syntax that illustrates the changing of the column name using a RENAME statement:

1. ALTER TABLE table_name

2. RENAME COLUMN old_column_name TO new_column_name;
Example
Let us understand how the RENAME statement works in MySQL to change the column
name through the various examples. Suppose we have a table named customer that
contains the following data:
Suppose we want to change the column name account with account_no without
changing its data types. We can do this by executing the below statement:

1. mysql> ALTER TABLE customer RENAME COLUMN account to account_no;

After executing the above command, we can verify it by using

the SELECT statement again. In the below image, the column name account has
changed successfully.

Renaming Multiple Columns

MySQL also allows us to change the multiple column names within a single statement.
If we want to rename multiple column names, we might use the below syntax:

1. ALTER TABLE table_name

2. CHANGE old_column_name1 new_column_name1 Data Type,
3. CHANGE old_column_name2 new_column_name2 Data Type,
4. ...
5. ...
6. CHANGE old_column_nameN new_column_nameN Data Type;

OR

1. ALTER TABLE table_name

2. RENAME COLUMN old_column_name1 TO new_column_name1,
3. RENAME COLUMN old_column_name2 TO new_column_name2,
 4. ...
5. ...
6. RENAME COLUMN old_column_nameN TO new_column_nameN;
Example
Suppose we want to change column names id and customer_name from
the customer table. To change multiple column names within a single statement, we
can use the statement as follows:

1. mysql> ALTER TABLE customer

2. CHANGE id cust_id int,
3. CHANGE customer_name cust_name varchar(45);

After executing the above command, we can verify it by using the SELECT statement
again. In the below image, the column name id and customer_name have changed
successfully:

Let us again change the currently modifying column name through the RENAME
COLUMN statement as follows:

1. mysql> ALTER TABLE customer

2. RENAME COLUMN cust_id TO id,
3. RENAME COLUMN cust_name TO customer_name;

After executing the above command, we can verify it by using the DESCRIBE
statement again. In the below image, the column name cust_id and
cust_name have changed successfully:
In this article, we have learned an introduction of the MySQL RENAME column and how
to change the column name in a specified table, along with a query example for
better understanding.
MySQL View
A view is a database object that has no values. Its contents are based on the base
table. It contains rows and columns similar to the real table. In MySQL, the View is
a virtual table created by a query by joining one or more tables. It is operated
similarly to the base table but does not contain any data of its own. The View and
table have one main difference that the views are definitions built on top of other
tables (or views). If any changes occur in the underlying table, the same changes
reflected in the View also.

MySQL allows us to create a view in mainly two ways:

1. MySQL Command line client

2. MySQL Workbench

Let us discuss both in detail.

MySQL Command Line Client

We can create a new view by using the CREATE
VIEW and SELECT statement. SELECT statements are used to take data from the source
table to make a VIEW.

Syntax
Following is the syntax to create a view in MySQL:

1. CREATE [OR REPLACE] VIEW view_name AS

2. SELECT columns
3. FROM tables
4. [WHERE conditions];
Parameters:
The view syntax contains the following parameters:

OR REPLACE: It is optional. It is used when a VIEW already exists. If you do not

specify this clause and the VIEW already exists, the CREATE VIEW statement will
return an error.

view_name: It specifies the name of the VIEW that you want to create in MySQL.

WHERE conditions: It is also optional. It specifies the conditions that must be met
for the records to be included in the VIEW.
Example
Let us understand it with the help of an example. Suppose our database has a
table course, and we are going to create a view based on this table. Thus, the below
example will create a VIEW name "trainer" that creates a virtual table made by
taking data from the table courses.

1. CREATE VIEW trainer AS

2. SELECT course_name, trainer
3. FROM courses;

Once the execution of the CREATE VIEW statement becomes successful, MySQL will
create a view and stores it in the database.

To see the created VIEW

We can see the created view by using the following syntax:

1. SELECT * FROM view_name;

Let's see how it looks the created VIEW:

1. SELECT * FROM trainer;

NOTE: It is essential to know that a view does not store the data physically. When we execute
the SELECT statement for the view, MySQL uses the query specified in the view's definition
and produces the output. Due to this feature, it is sometimes referred to as a virtual table.
MySQL Update VIEW
In MYSQL, the ALTER VIEW statement is used to modify or update the already created
VIEW without dropping it.

Syntax:

Following is the syntax used to update the existing view in MySQL:

1. ALTER VIEW view_name AS

2. SELECT columns
3. FROM table
4. WHERE conditions;

Example:

The following example will alter the already created VIEW name "trainer" by adding a
new column.

1. ALTER VIEW trainer AS

2. SELECT id, course_name, trainer
3. FROM courses;

Once the execution of the ALTER VIEW statement becomes successful, MySQL will
update a view and stores it in the database. We can see the altered view using the
SELECT statement, as shown in the output:

MySQL Drop VIEW

We can drop the existing VIEW by using the DROP VIEW statement.

Syntax:
The following is the syntax used to delete the view:

1. DROP VIEW [IF EXISTS] view_name;

Parameters:

view_name: It specifies the name of the VIEW that we want to drop.

IF EXISTS: It is optional. If we do not specify this clause and the VIEW doesn't exist,
the DROP VIEW statement will return an error.

Example:

Suppose we want to delete the view "trainer" that we have created above. Execute
the below statement:

1. DROP VIEW trainer;

After successful execution, it is required to verify the view is available or not as below:

MySQL Create View with JOIN Clause

Here, we will see the complex example of view creation that involves multiple tables
and uses a join clause.

Suppose we have two sample table as shown below:

Now execute the below statement that will create a view Trainer along with the join
statement:

1. CREATE VIEW Trainer

2. AS SELECT c.course_name, c.trainer, t.email
3. FROM courses c, contact t
4. WHERE c.id = t.id;

We can verify the view using the SELECT statement shown in the below image:
Create View using MySQL Workbench
To create a view in the database using this tool, we first need to launch the MySQL
Workbench and log in with the username and password to the MySQL server. It will
show the following screen:

Now do the following steps for database deletion:

1. Go to the Navigation tab and click on the Schema menu. Here, we can see all the
previously created databases. Select any database under the Schema menu, for
example, testdb. It will pop up the option that can be shown in the following screen.
2. Next, we need to right-click on the view option, and a new pop up screen will come:

3. As soon as we select the "Create View" option, it will give the below screen where
we can write our own view.

4. After completing the script's writing, click on the Apply button, we will see the
following screen:
5. In this screen, we will review the script and click the Apply button on the database

6. Finally, click on the Finish button to complete the view creation. Now, we can
verify the view as below:
Why we use View?
MySQL view provides the following advantages to the user:

Simplify complex query

It allows the user to simplify complex queries. If we are using the complex query, we
can create a view based on it to use a simple SELECT statement instead of typing the
complex query again.

Increases the Re-usability

We know that View simplifies the complex queries and converts them into a single line
of code to use VIEWS. Such type of code makes it easier to integrate with our
application. This will eliminate the chances of repeatedly writing the same formula in
every query, making the code reusable and more readable.

Help in Data Security

It also allows us to show only authorized information to the users and hide essential
data like personal and banking information. We can limit which information users can
access by authoring only the necessary data to them.

Enable Backward Compatibility

A view can also enable the backward compatibility in legacy systems. Suppose we
want to split a large table into many smaller ones without affecting the current
applications that reference the table. In this case, we will create a view with the same
name as the real table so that the current applications can reference the view as if it
were a table.
MySQL Table Locking
A lock is a mechanism associated with a table used to restrict the unauthorized access
of the data in a table. MySQL allows a client session to acquire a table lock
explicitly to cooperate with other sessions to access the table's data. MySQL
also allows table locking to prevent it from unauthorized modification into the same
table during a specific period.

A session in MySQL can acquire or release locks on the table only for itself. Therefore,
one session cannot acquire or release table locks for other sessions. It is to note that
we must have a TABLE LOCK and SELECT privileges for table locking.

Table Locking in MySQL is mainly used to solve concurrency problems. It will be

used while running a transaction, i.e., first read a value from a table (database) and
then write it into the table (database).

MySQL provides two types of locks onto the table, which are:

READ LOCK: This lock allows a user to only read the data from a table.

WRITE LOCK: This lock allows a user to do both reading and writing into a table.

It is to note that the default storage engine used in MySQL is InnoDB. The InnoDB
storage engine does not require table locking manually because MySQL automatically
uses row-level locking for InnoDB tables. Therefore, we can do multiple transactions
on the same table simultaneously to read and write operations without making each
other wait. All other storage engines use table locking in MySQL.

Before understanding the table locking concept, first, we will create a new table
named "info_table" using the statement as follows:

1. CREATE TABLE info_table (

2. Id INT NOT NULL AUTO_INCREMENT,
 3. Name VARCHAR(50) NOT NULL,
4. Message VARCHAR(80) NOT NULL,
5. PRIMARY KEY (Id)
6. );
MySQL LOCK TABLES Statement
The following is the syntax that allows us to acquire a table lock explicitly:

1. LOCK TABLES table_name [READ | WRITE];

In the above syntax, we have specified the table name on which we want to acquire
a lock after the LOCK TABLES keywords. We can specify the lock type, either READ
or WRITE.

We can also lock more than one table in MySQL by using a list of comma-separated
table's names with lock types. See the below syntax:

1. LOCK TABLES tab_name1 [READ | WRITE],

2. tab_name2 [READ | WRITE],...... ;
MySQL UNLOCK TABLES Statement
The following is the syntax that allows us to release a lock for a table in MySQL:

1. mysql> UNLOCK TABLES;

LOCK TYPES
Let us understand the lock types in detail.

READ Locks
The following are the features of the READ lock:

o At the same time, MySQL allows multiple sessions to acquire a READ lock for a table.
And all other sessions can read the table without acquiring the lock.
o If the session holds the READ lock on a table, they cannot perform a write operation on
it. It is because the READ lock can only read data from the table. All other sessions that
do not acquire a READ lock are not able to write data into the table without releasing
the READ lock. The write operations go into the waiting states until we have not
released the READ lock.
o When the session is terminated normally or abnormally, MySQL implicitly releases all
types of locks on to the table. This feature is also relevant for the WRITE lock.

Let us take an example to see how READ locks work in MySQL with the given scenario.
We will first connect to the database and use the CONNECTION_ID() function that
gives the current connection id in the first session as follows:
 1. mysql> SELECT CONNECTION_ID();

See the below output:

Next, we will insert few rows into the info_table using the below statement:

1. mysql> INSERT INTO info_table (name, message)

2. VALUES('Peter', 'Hi'),
3. ('Joseph', 'Hello'),
4. ('Mark', 'Welcome');

Now, verify the data into the table using the below statement:

1. mysql> SELECT * FROM info_table;

We should see the output as follows:

Now, we will execute the LOCK TABLE statement to acquire a lock onto the table:

1. mysql> LOCK TABLE info_table READ;

After that, we will try to insert a new record into the info_table as follows:

1. mysql> INSERT INTO info_table (name, message)

 2. VALUES ('Suzi', 'Hi');

We will get the below output where MySQL issues the following message "Table
'info_table' was locked with a READ lock and can't be updated".

Thus, we can see that once the READ lock is acquired on to the table, we cannot write
data to the table in the same session.

Now, we will check how the READ lock work from a different session. First, we will
connect to the database and see the connection id:

Next, we will query data from the info_table that returns the output as follows:

Then, insert some rows into this table as follows:

1. mysql> INSERT INTO info_table (name, message)

2. VALUES ('Stephen', 'Hello');

We should see the output as follows:

In the above output, we can see that the insert operation from the second session is
in the waiting state. It is due to the READ lock, which is already acquired on the
table by the first session and has not been released yet.

We can see the detailed information about them using the SHOW
PROCESSLIST statement in the first session. See the below output:

Finally, we need to release the lock by using the UNLOCK TABLES statement in the
first session. Now, we are able to execute the INSERT operation in the second session.

Write Locks
The following are the features of a WRITE lock:

o It is the session that holds the lock of a table and can read and write data both from the
table.
o It is the only session that accesses the table by holding a lock. And all other sessions
cannot access the data of the table until the WRITE lock is released.

Let us take an example to see how WRITE locks works in MySQL with the given
scenario. In the first session, we will acquire a WRITE lock using the below statement:

1. mysql> LOCK TABLE info_table WRITE;

Then, we will insert a new record into the info_table as follows:

1. mysql> INSERT INTO info_table (name, message)

2. VALUES ('Stephen', 'How R U');

The above statement worked. Now, we can verify the output using the SELECT
statement:
Again, we will attempt to access (read/write) the table from the second session:

1. INSERT INTO info_table (name, message)

2. VALUES ('George', 'Welcome');
3.
4. SELECT * FROM info_table;

We can see that these operations are put into a waiting state. See the detailed
information about them using the SHOW PROCESSLIST statement:

Finally, we will release the lock from the first session. Now, we can execute the
pending operations.

Read vs. Write Lock

o Read lock is similar to "shared" locks because multiple threads can acquire it at the
same time.
o Write lock is an "exclusive" locks because another thread cannot read it.
o We cannot provide read and write locks both on the table at the same time.
o Read lock has a low priority than Write lock, which ensures that updates are made as
soon as possible.

MySQL Lock Account

A lock is a mechanism used to prevent unauthorized modifications into our database.
It is essential to the security of our database. In this article, we are going to learn how
to use the CREATE USER… ACCOUNT LOCK and ALTER TABLE… ACCOUNT
LOCK statements for locking the user accounts in the MySQL server.

We can lock the user accounts by using the CREATE USER... ACCOUNT LOCK statement
as follows:

1. CREATE USER account_name IDENTIFIED BY 'password' ACCOUNT LOCK;

The ACCOUNT LOCK clause specifies the initial locking state for a new user account.
If we do not specify this clause with the statement, then a newly created user is
stored in an unlocked state by default. If we have enabled
the validate_password plugin during user creation, we cannot create an account
without a password, even if it is locked.

MySQL also allows us to provide the lock on an existing user account by using the
ALTER USER… ACCOUNT LOCK statement as follows:
 1. ALTER USER account_name IDENTIFIED BY 'password' ACCOUNT LOCK;

The account locking state remains unchanged if we do not specify the ACCOUNT LOCK
clause with the statement.

MySQL uses the account_locked column of the mysql.user system table to store
the account locking state. We can use the SHOW CREATE USER statement to
validate whether the account is unlocked or locked. If this column value is Y, it means
the account is locked. If it contains N, it means the account is unlocked.

If we will try to access the locked account, the attempt fails, and MySQL issues an
error that writes the below message to the error log:

1. Access denied for user 'user_name'@'host_name'.

2. An account is locked.

MySQL User Account Locking Examples

Let us understand the working of locking user accounts through examples:

1. Using ACCOUNT LOCK clause for locking a new user account

First, we will create a new user account named javatpoint@localhost in the locked
state using the below statement:

1. mysql> CREATE USER IF NOT EXISTS javatpoint@localhost

2. IDENTIFIED BY 'jtp123456'
3. ACCOUNT LOCK;

Next, we will execute the below statement to show the user account and its status:

1. mysql> SELECT user, host, account_locked

2. FROM mysql.user
3. WHERE user = 'javatpoint' AND host = 'localhost';

We should get the below output:

IN this output, we can see that the account_locked column in
the mysql.user system table indicates Y. It means the username javatpoint is
locked on the server.

If we try to access the user account javatpoint to connect to the MySQL Server, the
attempt fails, and we will receive an error:

1. mysql -u javatpoint -p
2. Enter password: *********

Here is the error message:

2. MySQL account locking for an existing user account

We can understand it by creating a new user account
named markus@localhost using the below statement:

1. mysql> CREATE USER IF NOT EXISTS markus@localhost

2. IDENTIFIED BY 'mark12345';

Next, we will log in to the MySQL server with a newly created user account
markus@localhost as follows:

1. mysql -u markus -p
2. Enter password: *********

We will get the below output that means the user account markus@localhost is login
successfully.
Now, we will use the ALTER TABLE LOCK ACCOUNT statement to lock this user
account as follows:

1. mysql> ALTER USER markus@localhost ACCOUNT LOCK;

Again, we will execute the below statement to show the user status:

1. mysql> SELECT user, host, account_locked

2. FROM mysql.user
3. WHERE user = 'markus' AND host = 'localhost';

We can see the below output that indicates user account markus was locked
successfully:

If we want to show the number of attempts to connect to the MySQL Server of locked
accounts, we need the locked_connects variables. Each time we try to connect the
locked user account, MySQL increases this variable's status by 1. See the below
command:

1. mysql> SHOW GLOBAL STATUS LIKE 'Locked_connects';

After execution, we will get this output that shows we have tried three times to
connect the locked user account:
MySQL Unlock Account
Unlock is a mechanism that allows the user to release all locks or any specific lock
associated with the account. In this article, we will learn how to unlock the user
accounts in the MySQL server.

When the CREATE USER… UNLOCK statement creates a new user account, the new
user stored as a locked state.

If we want to release a lock from the existing user account, we need to use the ALTER
USER… ACCOUNT UNLOCK statement as follows:

1. ALTER USER [IF EXISTS] user_account_name ACCOUNT UNLOCK;

In this syntax, we have to first specify the user account name that we want to
release a lock after the ALTER USER keyword. Next, we need to provide
the ACCOUNT UNLOCK clause next to the user name. It is to note the IF
EXISTS option can also be used to unlock the account only if it has existed in the
server.

MySQL also allows us to unlock multiple user accounts at the same time by using
the below statement:

1. ALTER USER [IF EXISTS]

2. user_account_name1, user_account_name2, ...
3. ACCOUNT UNLOCK;

In this syntax, we need to provide a list of comma-separated user name for unlocking
multiple accounts within a single query. If we do not specify the ACCOUNT UNLOCK
clause with the statement, the account unlocking state remains unchanged.
MySQL uses the account_locked column of the mysql.user system table to store
the account locking state. We can use the SHOW CREATE USER statement to
validate whether the account is unlocked or locked. If this column value is Y, it means
the account is locked. If it contains N, it means the account is unlocked.

If we will try to connect to the account without unlocking, MySQL issues an error that
writes the below message to the error log:

1. Access denied for user 'user_name'@'host_name'.

2. An account is locked.

MySQL User Account Unlocking Examples

Let us understand how to unlock the user accounts through examples. First, we will
create a new user account named javatpoint@localhost in the locked state using
the below statement:

1. mysql> CREATE USER IF NOT EXISTS javatpoint@localhost

2. IDENTIFIED BY 'jtp123456'
3. ACCOUNT LOCK;

Next, we will execute the below statement to show the user account and its status:

1. mysql> SELECT user, host, account_locked

2. FROM mysql.user
3. WHERE user = 'javatpoint' AND host = 'localhost';

We should get the below output:

In this output, we can see that the account_locked column in

the mysql.user system table indicates Y. It means the username javatpoint is locked
on the server.

If we try to connect with this account without unlocking in the MySQL Server, it
returns the following error:
 1. mysql -u javatpoint -p
2. Enter password: *********

Here is the error message:

Thus, we can use the ALTER USER statement to unlock the account before accessing
it as follows:

1. mysql> ALTER USER 'javatpoint'@'localhost' ACCOUNT UNLOCK;

In the below output, we can see that the account_locked column status is N. It means
the user account javatpoint does not have any lock.

In this article, we have learned how we can use the ALTER TABLE ACCOUNT UNLOCK
statement to release a lock from an existing user account.
MySQL Queries
A list of commonly used MySQL queries to create database, use database, create
table, insert record, update record, delete record, select record, truncate table and
drop table are given below.

1) MySQL Create Database

MySQL create database is used to create database. For example

1. create database db1;

More Details...

2) MySQL Select/Use Database

MySQL use database is used to select database. For example

1. use db1;

More Details...

3) MySQL Create Query

MySQL create query is used to create a table, view, procedure and function. For
example:

1. CREATE TABLE customers

2. (id int(10),
3. name varchar(50),
4. city varchar(50),
5. PRIMARY KEY (id )
6. );

More Details...

4) MySQL Alter Query

MySQL alter query is used to add, modify, delete or drop colums of a table. Let's see a
query to add column in customers table:

1. ALTER TABLE customers

2. ADD age varchar(50);

More Details...

5) MySQL Insert Query

MySQL insert query is used to insert records into table. For example:
 1. insert into customers values(101,'rahul','delhi');

More Details...

6) MySQL Update Query

MySQL update query is used to update records of a table. For example:

1. update customers set name='bob', city='london' where id=101;

More Details...

7) MySQL Delete Query

MySQL update query is used to delete records of a table from database. For example:

1. delete from customers where id=101;

More Details...

8) MySQL Select Query

Oracle select query is used to fetch records from database. For example:

1. SELECT * from customers;

More Details...

9) MySQL Truncate Table Query

MySQL update query is used to truncate or remove records of a table. It doesn't
remove structure. For example:

1. truncate table customers;

More Details...

10) MySQL Drop Query

MySQL drop query is used to drop a table, view or database. It removes structure and
data of a table if you drop table. For example:

1. drop table customers;

MySQL Constraints
The constraint in MySQL is used to specify the rule that allows or restricts what
values/data will be stored in the table. They provide a suitable method to ensure data
accuracy and integrity inside the table. It also helps to limit the type of data that will
be inserted inside the table. If any interruption occurs between the constraint and
data action, the action is failed.

Types of MySQL Constraints

Constraints in MySQL is classified into two types:

1. Column Level Constraints: These constraints are applied only to the single column that limits
the type of particular column data.
2. Table Level Constraints: These constraints are applied to the entire table that limits the type
of data for the whole table.
How to create constraints in MySQL
We can define the constraints during a table created by using the CREATE TABLE
statement. MySQL also uses the ALTER TABLE statement to specify the constraints in
the case of the existing table schema.

Syntax

The following are the syntax to create a constraints in table:

1. CREATE TABLE new_table_name (

2. col_name1 datatype constraint,
3. col_name2 datatype constraint,
4. col_name3 datatype constraint,
5. .........
6. );
Constraints used in MySQL
The following are the most common constraints used in the MySQL:

o NOT NULL
o CHECK
o DEFAULT
o PRIMARY KEY
o AUTO_INCREMENT
o UNIQUE
o INDEX
o ENUM
o FOREIGN KEY

Let us discuss each of these constraints in detail.

NOT NULL Constraint

This constraint specifies that the column cannot have NULL or empty values. The
below statement creates a table with NOT NULL constraint.

1. mysql> CREATE TABLE Student(Id INTEGER, LastName TEXT NOT NULL, FirstN
ame TEXT NOT NULL, City VARCHAR(35));

Execute the queries listed below to understand how it works:

1. mysql> INSERT INTO Student VALUES(1, 'Hanks', 'Peter', 'New York');

2.
3. mysql> INSERT INTO Student VALUES(2, NULL, 'Amanda', 'Florida');
Output

In the above image, we can see that the first INSERT query executes correctly, but the
second statement fails and gives an error that says column LastName cannot be null.

UNIQUE Constraint

This constraint ensures that all values inserted into the column will be unique. It
means a column cannot stores duplicate values. MySQL allows us to use more than
one column with UNIQUE constraint in a table. The below statement creates a table
with a UNIQUE constraint:

1. mysql> CREATE TABLE ShirtBrands(Id INTEGER, BrandName VARCHAR(40) U

NIQUE, Size VARCHAR(30));

Execute the queries listed below to understand how it works:

1. mysql> INSERT INTO ShirtBrands(Id, BrandName, Size) VALUES(1, 'Pantaloon

s', 38), (2, 'Cantabil', 40);
2.
3. mysql> INSERT INTO ShirtBrands(Id, BrandName, Size) VALUES(1, 'Raymond'
, 38), (2, 'Cantabil', 40);

Output

In the below output, we can see that the first INSERT query executes correctly, but the
second statement fails and gives an error that says: Duplicate entry 'Cantabil' for key
BrandName.

CHECK Constraint

It controls the value in a particular column. It ensures that the inserted value in a
column must be satisfied with the given condition. In other words, it determines
whether the value associated with the column is valid or not with the given condition.

Before the version 8.0.16, MySQL uses the limited version of this constraint syntax, as
given below:
 1. CHECK (expr)

After the version 8.0.16, MySQL uses the CHECK constraints for all storage engines
i.e., table constraint and column constraint, as given below:

1. [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]

Let us understand how a CHECK constraint works in MySQL. For example, the
following statement creates a table "Persons" that contains CHECK constraint on the
"Age" column. The CHECK constraint ensures that the inserted value in a column must
be satisfied with the given condition means the Age of a person should be greater
than or equal to 18:

1. mysql> CREATE TABLE Persons (

2. ID int NOT NULL,
3. Name varchar(45) NOT NULL,
4. Age int CHECK (Age>=18)
5. );

Execute the listed queries to insert the values into the table:

1. mysql> INSERT INTO Persons(Id, Name, Age)

2. VALUES (1,'Robert', 28), (2, 'Joseph', 35), (3, 'Peter', 40);
3.
4. mysql> INSERT INTO Persons(Id, Name, Age) VALUES (1,'Robert', 15);

Output

In the below output, we can see that the first INSERT query executes successfully, but
the second statement fails and gives an error that says: CHECK constraint is violated
for key Age.
DEFAULT Constraint

This constraint is used to set the default value for the particular column where we
have not specified any value. It means the column must contain a value, including
NULL.

For example, the following statement creates a table "Persons" that contains DEFAULT
constraint on the "City" column. If we have not specified any value to the City column,
it inserts the default value:

1. mysql> CREATE TABLE Persons (

2. ID int NOT NULL,
3. Name varchar(45) NOT NULL,
4. Age int,
5. City varchar(25) DEFAULT 'New York'
6. );

Execute the listed queries to insert the values into the table:

1. mysql> INSERT INTO Persons(Id, Name, Age, City)

2. VALUES (1,'Robert', 15, 'Florida'),
3. (2, 'Joseph', 35, 'California'),
4. (3, 'Peter', 40, 'Alaska');
5.
6. mysql> INSERT INTO Persons(Id, Name, Age) VALUES (1,'Brayan', 15);

Output

In the below output, we can see that the first insert query that contains all fields
executes successfully, while the second insert statement does not contain the "City"
column but also executed successfully. It is because it has a default value.

Now, executes the following statement to validate the default value for the 4th
column:
 1. mysql> SELECT * FROM Persons;

We can see that it works perfectly. It means default value "New York" stored
automatically in the City column.

PRIMARY KEY Constraint

This constraint is used to identify each record in a table uniquely. If the column
contains primary key constraints, then it cannot be null or empty. A table may have
duplicate columns, but it can contain only one primary key. It always contains unique
value into a column.

The following statement creates a table "Person" and explains the use of this primary
key more clearly:

1. CREATE TABLE Persons (

2. ID int NOT NULL PRIMARY KEY,
3. Name varchar(45) NOT NULL,
4. Age int,
5. City varchar(25));

Next, use the insert query to store data into a table:

1. INSERT INTO Persons(Id, Name, Age, City)

2. VALUES (1,'Robert', 15, 'Florida') ,
3. (2, 'Joseph', 35, 'California'),
4. (3, 'Peter', 40, 'Alaska');
5.
6. INSERT INTO Persons(Id, Name, Age, City)
7. VALUES (1,'Stephen', 15, 'Florida');

Output

In the below output, we can see that the first insert query executes successfully.
While the second insert statement fails and gives an error that says: Duplicate entry
for the primary key column.
AUTO_INCREMENT Constraint

This constraint automatically generates a unique number whenever we insert a new

record into the table. Generally, we use this constraint for the primary key field in a
table.

We can understand it with the following example where the id column going to be
auto-incremented in the Animal table:

1. mysql> CREATE TABLE Animals(

2. id int NOT NULL AUTO_INCREMENT,
3. name CHAR(30) NOT NULL,
4. PRIMARY KEY (id));

Next, we need to insert the values into the "Animals" table:

1. mysql> INSERT INTO Animals (name) VALUES

2. ('Tiger'),('Dog'),('Penguin'),
3. ('Camel'),('Cat'),('Ostrich');

Now, execute the below statement to get the table data:

1. mysql> SELECT * FROM Animals;

Output

In the output, we can see that I have not specified any value for the auto-increment
column, so MySQL automatically generates a unique number in the sequence order
for this field.
ENUM Constraint

The ENUM data type in MySQL is a string object. It allows us to limit the value chosen
from a list of permitted values in the column specification at the time of table
creation. It is short for enumeration, which means that each column may have one of
the specified possible values. It uses numeric indexes (1, 2, 3…) to represent string
values.

The following illustration creates a table named "shirts" that contains three columns:
id, name, and size. The column name "size" uses the ENUM data type that contains
small, medium, large, and x-large sizes.

1. mysql> CREATE TABLE Shirts (

2. id INT PRIMARY KEY AUTO_INCREMENT,
3. name VARCHAR(35),
4. size ENUM('small', 'medium', 'large', 'x-large')
5. );

Next, we need to insert the values into the "Shirts" table using the below statements:

1. mysql> INSERT INTO Shirts(id, name, size)

2. VALUES (1,'t-shirt', 'medium'),
3. (2, 'casual-shirt', 'small'),
4. (3, 'formal-shirt', 'large');

Now, execute the SELECT statement to see the inserted values into the table:

1. mysql> SELECT * FROM Shirts;

Output

We will get the following output:

INDEX Constraint

This constraint allows us to create and retrieve values from the table very quickly and
easily. An index can be created using one or more than one column. It assigns a
ROWID for each row in that way they were inserted into the table.

The following illustration creates a table named "shirts" that contains three columns:
id, name, and size.

1. mysql> CREATE TABLE Shirts (

2. id INT PRIMARY KEY AUTO_INCREMENT,
3. name VARCHAR(35),
4. size ENUM('small', 'medium', 'large', 'x-large')
5. );

Next, we need to insert the values into the "Shirts" table using the below statements:

1. mysql> INSERT INTO Shirts(id, name, size)

2. VALUES (1,'t-shirt', 'medium'),
3. (2, 'casual-shirt', 'small'),
4. (3, 'formal-shirt', 'large');

Now, execute this statement for creating index:

1. mysql> CREATE INDEX idx_name ON Shirts(name);

We can use the query below to retrieve the data using the index column:

1. mysql> SELECT * FROM Shirts USE INDEX(idx_name);

Output

The following output appears:

Foreign Key Constraint

This constraint is used to link two tables together. It is also known as the referencing
key. A foreign key column matches the primary key field of another table. It means a
foreign key field in one table refers to the primary key field of another table.

Let us consider the structure of these tables: Persons and Orders.

Table: Persons

1. CREATE TABLE Persons (

2. Person_ID int NOT NULL PRIMARY KEY,
3. Name varchar(45) NOT NULL,
4. Age int,
5. City varchar(25)
6. );

Table: Orders

1. CREATE TABLE Orders (

2. Order_ID int NOT NULL PRIMARY KEY,
3. Order_Num int NOT NULL,
4. Person_ID int,
5. FOREIGN KEY (Person_ID) REFERENCES Persons(Person_ID)
6. );

In the above table structures, we can see that the "Person_ID" field in the "Orders"
table points to the "Person_ID" field in the "Persons" table. The "Person_ID" is the
PRIMARY KEY in the "Persons" table, while the "Person_ID" column of the "Orders"
table is a FOREIGN KEY.

Output

Our table contains the following data:

MySQL INSERT Statement

MySQL INSERT statement is used to store or add data in MySQL table within the
database. We can perform insertion of records in two ways using a single query in
MySQL:

1. Insert record in a single row

2. Insert record in multiple rows

Syntax:
The below is generic syntax of SQL INSERT INTO command to insert a single record
in MySQL table:

1. INSERT INTO table_name ( field1, field2,...fieldN )

2. VALUES
3. ( value1, value2,...valueN );

In the above syntax, we first have to specify the table name and list of comma-
separated columns. Second, we provide the list of values corresponding to columns
name after the VALUES clause.
NOTE: Field name is optional. If we want to specify partial values, the field name is mandatory.
It also ensures that the column name and values should be the same. Also, the position of
columns and corresponding values must be the same.

If we want to insert multiple records within a single command, use the following
statement:

1. INSERT INTO table_name VALUES

2. ( value1, value2,...valueN )
3. ( value1, value2,...valueN )
4. ...........
5. ( value1, value2,...valueN );

In the above syntax, all rows should be separated by commas in the value fields.

MySQL INSERT Example

Let us understand how INSERT statements work in MySQL with the help of multiple
examples. First, create a table "People" in the database using the following command:

1. CREATE TABLE People(

2. id int NOT NULL AUTO_INCREMENT,
3. name varchar(45) NOT NULL,
4. occupation varchar(35) NOT NULL,
5. age int,
6. PRIMARY KEY (id)
7. );

1. If we want to store single records for all fields, use the syntax as follows:

1. INSERT INTO People (id, name, occupation, age)

2. VALUES (101, 'Peter', 'Engineer', 32);

2. If we want to store multiple records, use the following statements where we can
either specify all field names or don't specify any field.

1. INSERT INTO People VALUES

2. (102, 'Joseph', 'Developer', 30),
3. (103, 'Mike', 'Leader', 28),
4. (104, 'Stephen', 'Scientist', 45);

3. If we want to store records without giving all fields, we use the following partial
field statements. In such case, it is mandatory to specify field names.

1. INSERT INTO People (name, occupation)

2. VALUES ('Stephen', 'Scientist'), ('Bob', 'Actor');
In the below output, we can see that all INSERT statements have successfully
executed and stored the value in a table correctly.

We can use the below syntax to show the records of the People table:

1. mysql> SELECT * FROM People;

We will get the output as follows:

Inserting Date in MySQL Table:

We can also use the INSERT STATEMENT to add the date in MySQL table. MySQL
provides several data types for storing dates such as DATE, TIMESTAMP, DATETIME,
and YEAR. The default format of the date in MySQL is YYYY-MM-DD.

This format has the below descriptions:

o YYYY: It represents the four-digit year, like 2020.

 o MM: It represents the two-digit month, like 01, 02, 03, and 12.
o DD: It represents the two-digit day, like 01, 02, 03, and 31.

Following is the basic syntax to insert date in MySQL table:

1. INSERT INTO table_name (column_name, column_date) VALUES ('DATE: Manu

al Date', '2008-7-04');

If we want to insert a date in the mm/dd/yyyy format, it is required to use the below
statement:

1. INSERT INTO table_name VALUES (STR_TO_DATE(date_value, format_specifier

));

MySQL UPDATE Query

MySQL UPDATE query is a DML statement used to modify the data of the MySQL table
within the database. In a real-life scenario, records are changed over a period of time.
So, we need to make changes in the values of the tables also. To do so, it is required
to use the UPDATE query.

The UPDATE statement is used with the SET and WHERE clauses. The SET clause is
used to change the values of the specified column. We can update single or multiple
columns at a time.
Syntax
Following is a generic syntax of UPDATE command to modify data into
the MySQL table:

1. UPDATE table_name
2. SET column_name1 = new-value1,
3. column_name2=new-value2, ...
4. [WHERE Clause]
Parameter Explanation
The description of parameters used in the syntax of the UPDATE statement is given
below:

Parameter Descriptions

table_name It is the name of a table in which we want to perform updation.

column_na It is the name of a column in which we want to perform updation with the new value u
me update multiple columns, separate the columns with a comma operator by specifying

WHERE It is optional. It is used to specify the row name in which we are going to perform
Clause updates all rows.

Note:

o This statement can update values in a single table at a time.

o We can update single or multiple columns altogether with this statement.
o Any condition can be specified by using the WHERE clause.
o WHERE clause is very important because sometimes we want to update only a single
row, and if we omit this clause, it accidentally updates all rows of the table.

The UPDATE command supports these modifiers in MySQL:

LOW_PRIORITY: This modifier instructs the statement to delay the UPDATE

command's execution until no other clients reading from the table. It takes effects
only for the storage engines that use only table-level locking.

IGNORE: This modifier allows the statement to do not abort the execution even if
errors occurred. If it finds duplicate-key conflicts, the rows are not updated.

Therefore, the full syntax of UPDATE statement is given below:

1. UPDATE [LOW_PRIORITY] [IGNORE] table_name

2. SET column_assignment_list
 3. [WHERE condition]
Example:
Let us understand the UPDATE statement with the help of various examples. Suppose
we have a table "trainer" within the "testdb" database. We are going to update the
data within the "trainer" table.

Update Single Column

This query will update the email id of Java course with the new id as follows:

1. UPDATE trainer
2. SET email = 'mike@tutorialandexamples.com'
3. WHERE course_name = 'Java';

After successful execution, we will verify the table using the below statement:

1. SELECT * FROM trainer;

In the output, we can see that our table is updated as per our conditions.

Update Multiple Columns

The UPDATE statement can also be used to update multiple columns by specifying a
comma-separated list of columns. Suppose we have a table as below:

This statement explains will update the name and occupation whose id = 105 in
the People table as follows:

1. UPDATE People
2. SET name = 'Mary', occupation = 'Content Writer'
3. WHERE id = 105;

We can verify the output below:

UPDATE Statement to Replace String

We can also use the UPDATE statement in MySQL to change the string name in the
particular column. The following example updates the domain parts of emails
of Android course:

1. UPDATE Trainer_table
2. SET email = REPLACE(email,'@javatpoint.com','@tutorialandexample.com')
3. WHERE course_name = 'Testing';

It will give the following output:

MySQL DELETE Statement
MySQL DELETE statement is used to remove records from the MySQL table that is no
longer required in the database. This query in MySQL deletes a full row from the
table and produces the count of deleted rows. It also allows us to delete more
than one record from the table within a single query, which is beneficial while
removing large numbers of records from a table. By using the delete statement, we
can also remove data based on conditions.

Once we delete the records using this query, we cannot recover it. Therefore
before deleting any records from the table, it is recommended to create a backup of
your database. The database backups allow us to restore the data whenever we
need it in the future.

Syntax:

The following are the syntax that illustrates how to use the DELETE statement:

1. DELETE FROM table_name WHERE condition;

In the above statement, we have to first specify the table name from which we want
to delete data. Second, we have to specify the condition to delete records in
the WHERE clause, which is optional. If we omit the WHERE clause into the statement,
this query will remove whole records from the database table.

If we want to delete records from multiple tables using a single DELETE query, we
must add the JOIN clause with the DELETE statement.

If we want to delete all records from a table without knowing the count of deleted
rows, we must use the TRUNCATE TABLE statement that gives better performance.

Let us understand how the DELETE statement works in MySQL through various
examples.
MySQL DELETE Statement Examples
Here, we are going to use the "Employees" and "Payment" tables for the
demonstration of the DELETE statement. Suppose the Employees and Payment tables
contain the following data:

If we want to delete an employee whose emp_id is 107, we should use the DELETE
statement with the WHERE clause. See the below query:

1. mysql> DELETE FROM Employees WHERE emp_id=107;

After the execution of the query, it will return the output as below image. Once the
record is deleted, verify the table using the SELECT statement:

If we want to delete all records from the table, there is no need to use the WHERE
clause with the DELETE statement. See the below code and output:
In the above output, we can see that after removing all rows, the Employees table will
be empty. It means no records available in the selected table.

MySQL DELETE and LIMIT Clause

MySQL Limit clause is used to restrict the count of rows returns from the result set,
rather than fetching the whole records in the table. Sometimes we want to limit the
number of rows to be deleted from the table; in that case, we will use the LIMIT clause
as follows:

1. DELETE FROM table_name

2. WHERE condition
3. ORDER BY colm1, colm2, ...
4. LIMIT row_count;

It is to note that the order of rows in a MySQL table is unspecified. Therefore, we

should always use the ORDER BY clause while using the LIMIT clause.

For example, the following query first sorts the employees according to their names
alphabetically and deletes the first three employees from the table:

1. mysql> DELETE FROM Employees ORDER BY name LIMIT 3;

It will give the below output:

MySQL DELETE and JOIN Clause

The JOIN clause is used to add the two or more tables in MySQL. We will add the JOIN
clause with the DELETE statement whenever we want to delete records from multiple
tables within a single query. See the below query:

1. mysql> DELETE Employees, Payment FROM Employees

 2. INNER JOIN Payment
3. ON Employees.emp_id = Payment.emp_id
4. WHERE Employees.emp_id = 102;

Output:

After execution, we will see the output as below image:

To read more information about the DELETE statement with the JOIN clause, click here.

MySQL SELECT Statement

The SELECT statement in MySQL is used to fetch data from one or more tables.
We can retrieve records of all fields or specified fields that match specified criteria
using this statement. It can also work with various scripting languages such
as PHP, Ruby, and many more.

SELECT Statement Syntax

It is the most commonly used SQL query. The general syntax of this statement to
fetch data from tables are as follows:

1. SELECT field_name1, field_name 2,... field_nameN

 2. FROM table_name1, table_name2...
3. [WHERE condition]
4. [GROUP BY field_name(s)]
5. [HAVING condition]
6. [ORDER BY field_name(s)]
7. [OFFSET M ][LIMIT N];
Syntax for all fields:
1. SELECT * FROM tables [WHERE conditions]
2. [GROUP BY fieldName(s)]
3. [HAVING condition]
4. [ORDER BY fieldName(s)]
5. [OFFSET M ][LIMIT N];

Parameter Explanation
The SELECT statement uses the following parameters:

Parameter Name Descriptions

field_name(s) or * It is used to specify one or more columns to returns in the result set. The ast

table_name(s) It is the name of tables from which we want to fetch data.

WHERE It is an optional clause. It specifies the condition that returned the matched r

GROUP BY It is optional. It collects data from multiple records and grouped them by one

HAVING It is optional. It works with the GROUP BY clause and returns only those rows

ORDER BY It is optional. It is used for sorting the records in the result set.

OFFSET It is optional. It specifies to which row returns first. By default, It starts with z

LIMIT It is optional. It is used to limit the number of returned records in the result s

NOTE: It is to note that MySQL always evaluates the FROM clause first, and then the SELECT
clause will be evaluated.
MySQL SELECT Statement Example:
Let us understand how SELECT command works in MySQL with the help of various
examples. Suppose we have a table named employee_detail that contains the
following data:
1. If we want to retrieve a single column from the table, we need to execute the
below query:

1. mysql> SELECT Name FROM employee_detail;

We will get the below output where we can see only one column records.

2. If we want to query multiple columns from the table, we need to execute the
below query:

1. mysql> SELECT Name, Email, City FROM employee_detail;

We will get the below output where we can see the name, email, and city of
employees.

3. If we want to fetch data from all columns of the table, we need to use all
column's names with the select statement. Specifying all column names is not
convenient to the user, so MySQL uses an asterisk (*) to retrieve all column data as
follows:

1. mysql> SELECT * FROM employee_detail;

We will get the below output where we can see all columns of the table.

4. Here, we use the SUM function with the HAVING clause in the SELECT command to
get the employee name, city, and total working hours. Also, it uses the GROUP
BY clause to group them by the Name column.

1. SELECT Name, City, SUM(working_hours) AS "Total working hours"

2. FROM employee_detail
3. GROUP BY Name
4. HAVING SUM(working_hours) > 5;

It will give the below output:

5. MySQL SELECT statement can also be used to retrieve records from multiple tables
by using a JOIN statement. Suppose we have a table
named "customer" and "orders" that contains the following data:

Table: customer

Table: orders
Execute the following SQL statement that returns the matching records from both
tables using the INNER JOIN query:

1. SELECT cust_name, city, order_num, order_date

2. FROM customer INNER JOIN orders
3. ON customer.cust_id = orders.order_id
4. WHERE order_date < '2020-04-30'
5. ORDER BY cust_name;

After successful execution of the query, we will get the output as follows:
MySQL REPLACE
The REPLACE statement in MySQL is an extension of the SQL Standard. This statement
works the same as the INSERT statement, except that if an old row matches the new
record in the table for a PRIMARY KEY or a UNIQUE index, this command deleted the
old row before the new row is added.

This statement is required when we want to update the existing records into the table
to keep them updated. If we use the standard insert query for this purpose, it will give
a Duplicate entry for PRIMARY KEY or a UNIQUE key error. In this case, we will use the
REPLACE statement to perform our task. The REPLACE command requires one of the
two possible actions take place:

o If no matching value is found with the existing data row, then a standard INSERT
statement is performed.
o

o If the duplicate record found, the replace command will delete the existing row and
then adds the new record in the table.

In the REPLACE statement, the updation performed in two steps. First, it will delete
the existing record, and then the newly updated record is added, similar to a standard
INSERT command. Thus, we can say that the REPLACE statement performs two
standard functions, DELETE and INSERT.

Syntax

The following are the syntax of REPLACE statement in MySQL:

1. REPLACE [INTO] table_name(column_list)

2. VALUES(value_list);

MySQL REPLACE Example

Let us understand the working of the REPLACE statement in MySQL with the help of an
example. First, we are going to create a table named "Person" using the following
statement:

1. CREATE TABLE Person (

2. ID int AUTO_INCREMENT PRIMARY KEY,
3. Name varchar(45) DEFAULT NULL,
 4. Email varchar(45) DEFAULT NULL UNIQUE,
5. City varchar(25) DEFAULT NULL
6. );

Next, we need to fill the record into the table using the INSERT statement as below:

1. INSERT INTO Person(ID, Name, Email, City)

2. VALUES (1,'Mike', 'mike@javatpoint.com', 'California'),
3. (2, 'Alexandar', 'alexandar@javatpoint.com', 'New York'),
4. (3, 'Adam', 'adam@javatpoint.com', 'Los Angeles'),
5. (4, 'Peter', 'Peter@javatpoint.com', 'Alaska');

Execute the SELECT statement to verify the records that can be shown in the below
output:

After verifying the data into a table, we can replace any old row with the new row
using the REPLACE statement. Execute the below statement that updates the city of
a person whose id is 4.

1. REPLACE INTO Person (id, city)

2. VALUES(4,'Amsterdam');

After the successful execution of the above statement, it is required to query the data
of the table Person again to verify the replacement.

The value in the name and email columns are NULL now. It is because the REPLACE
statement works as follows:
 o This statement first tries to insert a new row into the Person table. But the insertion of a
new row is failed because the id = 4 already exists in the table.
o So this statement first delete the row whose id = 4 and then insert a new row with the
same id and city as Amsterdam. Since we have not specified the value for the name
and email column, it was set to NULL.

MySQL REPLACE statement to update a row

We can use the following REPLACE statement to update a row data into a table:

1. REPLACE INTO table

2. SET column1 = value1, column2 = value2;

The above syntax is similar to the UPDATE statement except for the REPLACE keyword. It
is to note that we cannot use the WHERE clause with this statement.

Execute the below example that uses the REPLACE statement to update the city of the
person named Mike from California to Birmingham.

1. REPLACE INTO Person

2. SET ID = 1,
3. Name = 'Mike',
4. City = 'Birmingham';

After verification of the table, we can see the following output:

If we have not specified the column's value in the SET clause, this command works
like the UPDATE statement, which means the REPLACE statement will use the
default value of that column.

MySQL REPLACE to insert data from the SELECT statement.

We can use the following REPLACE INTO statement to inserts data into a table with
the data returns from a query.
 1. REPLACE INTO table1(column_list)
2. SELECT column_list
3. FROM table2
4. WHERE condition;

It is to note that the above REPLACE query is similar to the INSERT INTO
SELECT statement. Execute the below example that uses the REPLACE INTO
statement to copy a row within the same table.

1. REPLACE INTO Person(Name, City)

2. SELECT Name, City
3. FROM Person WHERE id = 2;

After verification of the table, we will get the following output. In this output, we can
see that the copy of a row within the same table is successfully added.
MySQL INSERT ON DUPLICATE KEY UPDATE
The Insert on Duplicate Key Update statement is the extension of the INSERT
statement in MySQL. When we specify the ON DUPLICATE KEY UPDATE clause in a SQL
statement and a row would cause duplicate error value in a UNIQUE or PRIMARY
KEY index column, then updation of the existing row occurs.

In other words, when we insert new values into the table, and it causes duplicate row
in a UNIQUE OR PRIMARY KEY column, we will get an error message. However, if we
use ON DUPLICATE KEY UPDATE clause in a SQL statement, it will update the old row
with the new row values, whether it has a unique or primary key column.

For example, if column col1 is defined as UNIQUE and contains the value 10 into the
table tab1, we will get a similar effect after executing the below two statements:

1. mysql> INSERT INTO tab1 (col1, col2, col3) VALUES (10,20,30) ON DUPLICAT
E KEY UPDATE col3=col3+1;
2.
3. mysql> UPDATE tab1 SET col3=col3+1 WHERE col1=1;

It makes sure that if the inserted row matched with more than one unique index into
the table, then the ON DUPLICATE KEY statement only updates the first matched
unique index. Therefore, it is not recommended to use this statement on tables that
contain more than one unique index.

If the table contains AUTO_INCREMENT primary key column and the ON DUPLICATE
KEY statement tries to insert or update a row, the Last_Insert_ID() function returns its
AUTO_INCREMENT value.

The following are the syntax of Insert on Duplicate Key Update statement
in MySQL:

1. INSERT INTO table (column_names)

2. VALUES (data)
3. ON DUPLICATE KEY UPDATE
4. column1 = expression, column2 = expression…;

In this syntax, we can see that the INSERT statement only adds the ON DUPLICATE
KEY UPDATE clause with a column-value pair assignment whenever it finds
duplicate rows. The working of ON DUPLICATE KEY UPDATE clause first tries to insert
the new values into the row, and if an error occurs, it will update the existing row with
the new row values.

The VALUES() function only used in this clause, and it does not have any meaning in
any other context. It returns the column values from the INSERT portion and
particularly useful for multi-rows inserts.

MySQL gives the number of affected-rows with ON DUPLICATE KEY UPDATE

statement based on the given action:

o If we insert the new row into a table, it returns one affected-rows.

o If we update the existing row into a table, it returns two affected-rows.
o If we update the existing row using its current values into the table, it returns the
number of affected-rows 0.

MySQL INSERT ON DUPLICATE KEY Example

Let us understand the working of the INSERT ON DUPLICATE KEY UPDATE clause in
MySQL with the help of an example.

First, create a table named "Student" using the below statement:

1. CREATE TABLE Student (

2. Stud_ID int AUTO_INCREMENT PRIMARY KEY,
3. Name varchar(45) DEFAULT NULL,
4. Email varchar(45) DEFAULT NULL,
5. City varchar(25) DEFAULT NULL
6. );

Next, insert the data into the table. Execute the following statement:

1. INSERT INTO Student(Stud_ID, Name, Email, City)

2. VALUES (1,'Stephen', 'stephen@javatpoint.com', 'Texax'),
3. (2, 'Joseph', 'Joseph@javatpoint.com', 'Alaska'),
4. (3, 'Peter', 'Peter@javatpoint.com', 'california');

Execute the SELECT statement to verify the insert operation:

1. SELECT * FROM Student;

We will get the output as below where we have three rows into the table:
Again, add one more row into the table using the below query:

1. INSERT INTO Student(Stud_ID, Name, Email, City)

2. VALUES (4,'John', 'john@javatpoint.com', 'New York');

The above statement will add row successfully because it does not have any duplicate
values.

Finally, we are going to add a row with a duplicate value in the Stud_ID column:

1. INSERT INTO Student(Stud_ID, Name, Email, City)

2. VALUES (4, 'John', 'john@javatpoint.com', 'New York')
3. ON DUPLICATE KEY UPDATE City = 'California';

MySQL gives the following message after successful execution of the above query:

1. Query OK, 2 rows affected.

In the below out, we can see that the row id=4 already exists. So the query only
updates the City New York with California.
MySQL INSERT IGNORE
Insert Ignore statement in MySQL has a special feature that ignores the invalid
rows whenever we are inserting single or multiple rows into a table. We can
understand it with the following explanation, where a table contains a primary key
column.

The primary key column cannot stores duplicate values into a table. For example,
student_roll_number should always be unique for every student. Similarly,
the employee_id in the company should always be distinct into the employee table.
When we try to insert a duplicate record into a table with a primary key column, it
produces an error message. However, if we use the INSERT IGNORE statement to add
duplicate rows into a table with a primary key column, MySQL does not produce any
error. This statement is preferred when we are trying to insert records in bulk, and
resulting errors can interrupt the execution process. As a result, it does not store any
record into a table. In such a case, the INSERT IGNORE statement only generates the
warnings.

Below are the cases where an INSERT IGNORE statement avoids error:

o When we will try to insert a duplicate key where the column of a table has a PRIMARY or
UNIQUE KEY constraint.
o When we will try to add a NULL value where the column of a table has a NOT NULL
constraint.
o When we will try to insert a record to a partitioned table where the entered values do
not match the format of listed partitions.

Syntax
The following are a syntax to use the INSERT IGNORE statement in MySQL:

1. INSERT IGNORE INTO table_name (column_names)

2. VALUES ( value_list), ( value_list) .....;
MySQL INSERT IGNORE Example
Let us understand how INSERT IGNORE statement works in MySQL with the help of an
example. First, we need to create a table named "Student" using the following
statement:

1. CREATE TABLE Student (

2. Stud_ID int AUTO_INCREMENT PRIMARY KEY,
3. Name varchar(45) DEFAULT NULL,
4. Email varchar(45) NOT NULL UNIQUE,
5. City varchar(25) DEFAULT NULL
6. );

The UNIQUE constraint ensures that we cannot insert duplicate values into the email
column. Next, it is required to insert the records into the table. We can execute the
below statement to add data into a table:

1. INSERT INTO Student(Stud_ID, Name, Email, City)

2. VALUES (1,'Stephen', 'stephen@javatpoint.com', 'Texax'),
3. (2, 'Joseph', 'Joseph@javatpoint.com', 'Alaska'),
4. (3, 'Peter', 'Peter@javatpoint.com', 'california');

Finally, execute the SELECT statement to verify the insert operation:

1. SELECT * FROM Student;

We can see the below output where we have three rows into the table:
Let us execute the below statement that will try to add two records into the table:

1. INSERT INTO Student(Stud_ID, Name, Email, City)

2. VALUES (4,'Donald', 'donald@javatpoint.com', 'New York'),
3. (5, 'Joseph', 'Joseph@javatpoint.com', 'Chicago');

It will produce an error: ERROR 1062 (23000): Duplicate entry

'Joseph@javatpoint.com' for key 'student.Email' because of the email violets the
UNIQUE constraint.

Now, let us see what happened if we use the INSERT IGNORE statement into the
above query:

1. INSERT IGNORE INTO Student(Stud_ID, Name, Email, City)

2. VALUES (4,'Donald', 'donald@javatpoint.com', 'New York'),
3. (5, 'Joseph', 'Joseph@javatpoint.com', 'Chicago');

MySQL will produce a message: one row added, and the other row was ignored.

1. 1 row affected, 1 warning(s): 1062 Duplicate entry for key email.

2. Records: 2 Duplicates: 1 Warning: 1

We can see the detailed warning using the SHOW WARNINGS command:
Thus, we can say that if we use the INSERT IGNORE statement, MySQL gives a
warning instead of issuing an error.

MySQL INSERT IGNORE and STRICT mode

In MySQL, STRICT MODE handles invalid or missing values that are going to be
added into a table using INSERT OR UPDATE statement. If the strict mode is ON, and
we are trying to add invalid values into a table using the INSERT statement, the
statement is aborted, and we will get an error message.

However, if we use the INSERT IGNORE command, MySQL produces a warning

message instead of throwing an error. Also, this statement tries to truncate values to
make them valid before inserting it into the table.

Let us understand it with the help of an example. First, we are going to create a table
named "Test" using the below statement:

1. CREATE TABLE Test (

2. ID int AUTO_INCREMENT PRIMARY KEY,
3. Name varchar(5) NOT NULL
4. );

In the above table, the name column only accepts the string whose length is less than
or equal to five characters. Now, execute the below statement to insert the records
into a table.

1. INSERT INTO Test(Name)

2. VALUES ('Peter'), ('John');

We can see that the specified name validates the name column constraint so it will be
added successfully. Execute the SELECT statement to verify the result. It will give the
output as below:

Next, insert the name whose length is greater than five:

 1. INSERT INTO Test(Name) VALUES ('Stephen');

MySQL does not add values and gives an error message because the strict mode is
ON. However, if we use the INSERT IGNORE statement to insert the same string, it will
give the warning message instead of throwing an error.

1. INSERT IGNORE INTO Test(Name) VALUES ('Stephen');

Finally, we have executed the SHOW WARNINGS command to check the warning
message. The below output explains it more clearly that shows MySQL tries to
truncate data before inserting it into a table.
MySQL INSERT INTO SELECT
Sometimes we want to insert data of one table into the other table in the same or
different database. It is not very easy to enter these data using the INSERT query
manually. We can optimize this process with the use of MySQL INSERT INTO SELECT
query. It allows us to populate the MySQL tables quickly. This section will cover the
INSERT INTO SELECT command, syntax, and its use cases.

The INSERT INTO SELECT statement in MySQL allows us to insert values into a table
where data comes from a SELECT query. In other words, this query copies data
from one table and inserts them in the other table. We must consider the
following point before using this statement:

o The data types in source and target tables must be the same.
o The existing records in the target table should be unaffected.

The INSERT INTO SELECT command is advantageous when we need to copy data from
one table to another table or to summarize data from more than one table into a
single table.

Syntax
Earlier, we have used the INSERT command for adding single or multiple records
into a table along with listing column values in the VALUES clause as follows:

1. INSERT INTO table_name (column_list)

2. VALUES (value_list);

The following is the basic syntax that illustrates the use of the INSERT INTO SELECT
command in MySQL. If we want to copy all data from one table into another table, we
can use the below statement:

1. INSERT INTO table_name2

2. SELECT * FROM table_name1
3. WHERE condition;

From the MySQL version 8.0.19, we can use a TABLE statement in place of
SELECT query to get the same output, as shown below:

1. INSERT INTO table2 TABLE table1;

Here, TABLE table1 is equivalent to SELECT * FROM table1. When we want to add all
records from the source table into the target table without filtering the values, it is
used.
If we want to copy only some columns from one table to another table, we can use the
below statement:

1. INSERT INTO table_name2 (column_list)

2. SELECT column_list
3. FROM table_name1
4. WHERE condition;

In this syntax, we have used a SELECT statement instead of using the VALUES clause.
Here SELECT command retrieves values from one or more tables.

Parameter Explanation
The INSERT INTO SELECT statement uses the following parameters:

table_name1: It is the name of a source table.

table_name2: It is the name of a target table where we will copy source table data.

column_list: It represents the column names of the table.

condition: it is used to filter the table data.

MySQL INSERT INTO SELECT Example

Let us understand how the INSERT INTO SELECT statement works in MySQL with the
help of an example. First, we need to create a table named "person" using the
statement given below:

1. CREATE TABLE person (

2. id int AUTO_INCREMENT PRIMARY KEY,
3. name varchar(45) NOT NULL,
4. email varchar(45) NOT NULL,
5. city varchar(25) NOT NULL
6. );

Next, we will insert values into the table. We can execute the below statement to add
data into a table:

1. INSERT INTO person (id, name, email, city)

2. VALUES (1,'Stephen', 'stephen@javatpoint.com', 'Texas'),
3. (2, 'Joseph', 'Joseph@javatpoint.com', 'Alaska'),
4. (3, 'Peter', 'Peter@javatpoint.com', 'Texas'),
5. (4,'Donald', 'donald@javatpoint.com', 'New York'),
6. (5, 'Kevin', 'kevin@javatpoint.com', 'Texas');

We can verify the data by executing the SELECT statement:

 1. SELECT * FROM person;

After executing the query, we can see the below output where we have five rows into
the table:

Suppose we want to insert a person's name who belongs to Texas City into
another table. The following query is used to search all person who locates in Texas:

1. SELECT name, email, city

2. FROM person
3. WHERE city = 'Texas';

Now, we will create another table named person_info that have a same number of
column, and data types in the same order as of the above table:

1. CREATE TABLE person_info (

2. person_id int AUTO_INCREMENT PRIMARY KEY,
3. person_name varchar(45) NOT NULL,
4. email varchar(45) NOT NULL,
5. city varchar(25) NOT NULL
6. );

Second, we will use the INSERT INTO SELECT statement to insert persons located in
Texas from the person table into the person_info table:

1. INSERT INTO person_info (person_name, email, city)

2. SELECT name, email, city
3. FROM person
4. WHERE city = 'Texas';

After executing this statement, we can verify the insert operation using the SELECT
query. We will get the below output where all persons located in the Texas City
inserted successfully.
Suppose we want to insert all person's table data into the person_info
table without filtering any values; we can do this using the below statement:

1. INSERT INTO person_info Table person;

Execute the SELECT statement to verify the data. Here is the output:

How to Create Index in MySQL

An index is a data structure that allows us to add indexes in the existing table. It
enables you to improve the faster retrieval of records on a database table. It creates
an entry for each value of the indexed columns. We use it to quickly find the record
without searching each row in a database table whenever the table is accessed. We
can create an index by using one or more columns of the table for efficient access to
the records.

When a table is created with a primary key or unique key, it automatically creates a
special index named PRIMARY. We called this index as a clustered index. All indexes
other than PRIMARY indexes are known as a non-clustered index or secondary index.

Need for Indexing in MySQL

Suppose we have a contact book that contains names and mobile numbers of the
user. In this contact book, we want to find the mobile number of Martin Williamson. If
the contact book is an unordered format means the name of the contact book is not
sorted alphabetically, we need to go over all pages and read every name until we will
not find the desired name that we are looking for. This type of searching name is
known as sequential searching.

To find the name and contact of the user from table contactbooks, generally, we
used to execute the following query:

1. mysql> SELECT mobile_number FROM contactbooks WHERE first_name = 'Ma

rtin' AND last_name = 'Taybu';

This query is very simple and easy. Although it finds the phone number and name of
the user fast, the database searches entire rows of the table until it will not find the
rows that you want. Assume, the contactbooks table contains millions of rows, then,
without an index, the data retrieval takes a lot of time to find the result. In that case,
the database indexing plays an important role in returning the desired result and
improves the overall performance of the query.

MySQL CREATE INDEX Statement

Generally, we create an index at the time of table creation in the database. The
following statement creates a table with an index that contains two columns col2 and
col3.

1. mysql> CREATE TABLE t_index(

2. col1 INT PRIMARY KEY,
3. col2 INT NOT NULL,
4. col3 INT NOT NULL,
5. col4 VARCHAR(20),
6. INDEX (col2,col3)
7. );

If we want to add index in table, we will use the CREATE INDEX statement as follows:

1. mysql> CREATE INDEX [index_name] ON [table_name] (column names)

In this statement, index_name is the name of the index, table_name is the name of
the table to which the index belongs, and the column_names is the list of columns.

Let us add the new index for the column col4, we use the following statement:

1. mysql> CREATE INDEX ind_1 ON t_index(col4);

By default, MySQL allowed index type BTREE if we have not specified the type of
index. The following table shows the different types of an index based on the storage
engine of the table.

SN Storage Engine Index Typ

1. InnoDB BTREE
 2. Memory/Heap HASH, BT

3. MYISAM BTREE

Example

In this example, we are going to create a table student and perform the CREATE
INDEX statement on that table.

Table Name: student

Now, execute the following statement to return the result of the student
whose class is CS branch:

1. mysql> SELECT studentid, firstname, lastname FROM student WHERE class =

'CS';

This statement will give the following output:

In the above table, we can see the four rows that are indicating the students whose
class is the CS branch.

If you want to see how MySQL performs this query internally, execute the following
statement:

1. mysql> EXPLAIN SELECT studentid, firstname, lastname FROM student WHER

E class = 'CS';

You will get the output below. Here, MySQL scans the whole table that contains seven
rows to find the student whose class is the CS branch.

Now, let us create an index for a class column using the following statement.

1. mysql> CREATE INDEX class ON student (class);

After executing the above statement, the index is created successfully. Now, run the
below statement to see how MySQL internally performs this query.

1. mysql> EXPLAIN SELECT studentid, firstname, lastname FROM student WHER

E class = 'CS';

The above statement gives output, as shown below:

In this output, MySQL finds four rows from the class index without scanning the whole
table. Hence, it increases the speed of retrieval of records on a database table.

If you want to show the indexes of a table, execute the following statement:

1. mysql> SHOW INDEXES FROM student;

It will give the following output.

MySQL Drop Index

MySQL allows a DROP INDEX statement to remove the existing index from the table.
To delete an index from a table, we can use the following query:
 1. mysql>DROP INDEX index_name ON table_name [algorithm_option | lock_opti
on];

If we want to delete an index, it requires two things:

o First, we have to specify the name of the index that we want to remove.
o Second, name of the table from which your index belongs.

The Drop Index syntax contains two optional options, which are Algorithm and Lock
for reading and writing the tables during the index modifications. Let us explain both
in detail:

Algorithm Option
The algorithm_option enables us to specify the specific algorithm for removing the
index in a table. The syntax of algorithm_option are as follows:

1. Algorithm [=] {DEFAULT | INPLACE | COPY}

The Drop Index syntax supports mainly two algorithms which are INPLACE and COPY.

COPY: This algorithm allows us to copy one table into another new table row by row
and then DROP Index statement performed on this new table. On this table, we cannot
perform an INSERT and UPDATE statement for data manipulation.

INPLACE: This algorithm allows us to rebuild a table instead of copy the original
table. We can perform all data manipulation operations on this table. On this
table, MySQL issues an exclusive metadata lock during the index removal.

Note: If you not defined the algorithm clause, MySQL uses the INPLACE algorithm. If INPLACE
is not supported, it uses the COPY algorithm. The DEFAULT algorithm works the same as
without using any algorithm clause with the Drop index statement.

Lock Option
This clause enables us to control the level of concurrent reads and writes during the
index removal. The syntax of lock_option are as follows:

1. LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

In the syntax, we can see that the lock_option contains four modes that are
DEFAULT, NONE, SHARED, and EXCLUSIVE. Now, we are going to discuss all the
modes in detail:

SHARED: This mode supports only concurrent reads, not concurrent writes. When the
concurrent reads are not supported, it gives an error.

DEFAULT: This mode can have the maximum level of concurrency for a specified
algorithm. It will enable concurrent reads and writes if supported otherwise enforces
exclusive mode.
NONE: You have concurrent read and write if this mode is supported. Otherwise, it
gives an error.

EXCLUSIVE: This mode enforces exclusive access.

Example

First, execute the following command to show the indexes available in the table.

1. mysql> SHOW INDEXES FROM student;

It will give the following output.

In the output, we can see that there are three indexes available. Now, execute the
following statement to removes the class index from table student.

1. mysql> DROP INDEX class ON student;

Again, execute the SHOW INDEXES statement to verify the index is removed or not.
After performing this statement, we will get the following output, where only two
indexes are available.

Example using Algorithm and Lock

The following statement drops the age index form the student table using an
algorithm and a lock option.

1. mysql> DROP INDEX age ON student ALGORITHM = INPLACE LOCK = DEFAUL

T;

MySQL Drop PRIMARY Key Index

In some cases, the table contains a PRIMARY index that was created whenever you
create a table with a primary key or unique key. In that case, we need to execute the
following command because the PRIMARY is a reserved word.

1. mysql> DROP INDEX PRIMARY ON table_name;

To remove the primary key index from the student table, execute the following
statement:

1. mysql> DROP INDEX PRIMARY ON student;

MySQL Show Indexes
We can get the index information of a table using the Show Indexes statement. This
statement can be written as:

1. mysql> SHOW INDEXES FROM table_name;

In the above syntax, we can see that if we want to get the index of a table, it requires
to specify the table_name after the FROM keyword. After the successful execution of
the statement, it will return the index information of a table in the current database.

If we want to get the index information of a table in a different database or database

to which you are not connected, MySQL allows us to specify the database name with
the Show Indexes statement. The following statement explains it more clearly:

1. mysql> SHOW INDEXES FROM table_name IN database_name;

The above statement can also be written as:

1. mysql> SHOW INDEXES FROM database_name.table_name;

Note: It is noted that Index and Keys both are synonyms of Indexes, and IN is the synonyms of
FROM keyword. Therefore, we can also write the Show Indexes statement with these synonyms
as below:
1. mysql> SHOW INDEXES IN table_name FROM database_name;

OR,
 1. mysql> SHOW KEYS FROM table_name IN database_name;

The SHOW INDEX query returns the following fields/information:

Table: It contains the name of the table.

Non_unique: It returns 1 if the index contains duplicates. Otherwise, it returns 0.

Key_name: It is the name of an index. If the table contains a primary key, the index
name is always PRIMARY.

Seq_in_index: It is the sequence number of the column in the index that starts from
1.

Column_name: It contains the name of a column.

Collation: It gives information about how the column is sorted in the index. It
contains values where A represents ascending, D represents descending,
and Null represents not sorted.

Cardinality: It gives an estimated number of unique values in the index table where
the higher cardinality represents a greater chance of using indexes by MySQL.

Sub_part: It is a prefix of the index. It has a NULL value if all the column of the table
is indexed. When the column is partially indexed, it will return the number of indexed
characters.

Packed: It tells how the key is packed. Otherwise, it returns NULL.

NULL: It contains blank if the column does not have NULL value; otherwise, it returns
YES.

Index_type: It contains the name of the index method like BTREE, HASH, RTREE,
FULLTEXT, etc.

Comment: It contains the index information when they are not described in its
column. For example, when the index is disabled, it returns disabled.

Index_column: When you create an index with comment attributes, it contains the
comment for the specified index.

Visible: It contains YES if the index is visible to the query optimizer, and if not, it
contains NO.

Expression: MySQL 8.0 supports functional key parts that affect

both expression and column_name columns. We can understand it more clearly
with the below points:

o For functional parts, the expression column represents expression for the key part, and
column_name represents NULL.
o For the non-functional part, the expression represents NULL, and column_name
represents the column indexed by the key part.
MySQL SHOW INDEX Example

Here, we are going to create a table student_info that contains the student id, name,
age, mobile number, and email details. Execute the following command to create a
table:

1. CREATE TABLE `student_info` (

2. `studentid` int NOT NULL AUTO_INCREMENT,
3. `name` varchar(45) DEFAULT NULL,
4. `age` varchar(3) DEFAULT NULL,
5. `mobile` varchar(20) DEFAULT NULL,
6. `email` varchar(25) DEFAULT NULL,
7. PRIMARY KEY (`studentid`),
8. UNIQUE KEY `email_UNIQUE` (`email`)
9. )

Next, we create an index on this table by the following command:

1. mysql> CREATE INDEX mobile ON student_info (mobile) INVISIBLE;

2.
3. mysql> CREATE INDEX name ON student_info (name) COMMENT 'Student Na
me';

Now, execute the following command that returns the all index information from the
student_info table:

1. mysql> SHOW INDEXES FROM student_info;

We will get the output below:

Filter Index Information

We can filter the index information using where clause. The following statement can
be used to filter the index information:

1. Mysql> SHOW INDEXES FROM table_name where condition;

Example

If you want to get only invisible indexes of the student_info table, execute the
following command:

1. mysql> SHOW INDEXES FROM student_info WHERE visible = 'NO';

It will give the following output:

MySQL UNIQUE INDEX
Indexing is a process to find an unordered list into an ordered list that allows us to
retrieve records faster. It creates an entry for each value that appears in the index columns. It
helps in maximizing the query's efficiency while searching on tables in MySQL.
Without indexing, we need to scan the whole table to find the relevant information.
The working of MySQL indexing is similar to the book index.
Generally, we use the primary key constraint to enforce the uniqueness value of one
or more columns. But, we can use only one primary key for each table. So if we want
to make multiple sets of columns with unique values, the primary key constraint will
not be used.

MySQL allows another constraint called the UNIQUE INDEX to enforce the
uniqueness of values in one or more columns. We can create more than one UNIQUE
index in a single table, which is not possible with the primary key constraint.

Syntax
The following is a generic syntax used to create a unique index in MySQL table:

1. CREATE UNIQUE INDEX index_name

2. ON table_name (index_column1, index_column2,...);

MySQL allows another approach to enforcing the uniqueness value in one or more
columns using the UNIQUE Key statement. We can read more information about
the UNIQUE KEY here.

If we use a UNIQUE constraint in the table, MySQL automatically creates a UNIQUE

index behind the scenes. The following statement explains how to create a unique
constraint when we create a table.

1. CREATE TABLE table_name(

2. col1 col_definition,
3. col2 col_definition,
4. ...
5. [CONSTRAINT constraint_name]
6. UNIQUE Key (column_name(s))
7. );
NOTE: It is recommended to use the constraint name while creating a table. If we omit the
constraint name, MySQL generates a name for this column automatically.

UNIQUE Index and NULL

NULL values in MySQL considers distinct values similar to other databases. Hence, we
can store multiple NULL values in the UNIQUE index column. This feature of MySQL
sometimes reported as a bug, but it is not a bug.

MySQL UNIQUE Index Examples

Let us understand it with the help of an example. Suppose we want to manage the
employee details in a database application where we need email columns unique.
Execute the following statement that creates a table "Employee_Detail" with a
UNIQUE constraint:

1. CREATE TABLE Employee_Detail(

2. ID int AUTO_INCREMENT PRIMARY KEY,
3. Name varchar(45),
 4. Email varchar(45),
5. Phone varchar(15),
6. City varchar(25),
7. UNIQUE KEY unique_email (Email)
8. );

If we execute the below statement, we can see that MySQL created a UNIQUE index
for Email column of Employee_Detail table:

1. SHOW INDEXES FROM Employee_Detail;

In the below screen, we can see that the Email column is created as a unique index.

Next, we are going to insert records to the table using the following statements:

1. INSERT INTO Employee_Detail(ID, Name, Email, Phone, City)

2. VALUES (1, 'Peter', 'peter@javatpoint.com', '49562959223', 'Texas'),
3. (2, 'Suzi', 'suzi@javatpoint.com', '70679834522', 'California'),
4. (3, 'Joseph', 'joseph@javatpoint.com', '09896765374', 'Alaska');

The above statement executed successfully because all columns are unique. If we
insert a record whose email is suzi@javatpoint.com, we will get the duplicate error
message.

1. mysql> INSERT INTO Employee_Detail(ID, Name, Email, Phone, City)

2. VALUES (2, 'Suzi', 'suzi@javatpoint.com', '70679834522', 'Texas');

The following output explains all of the above steps more clearly:
Suppose we want the Name and Phone of the Employee_Detail table is also unique.
In this case, we will use the below statement to create a UNIQUE index for those
columns:

1. CREATE UNIQUE INDEX index_name_phone

2. ON Employee_Detail (Name, Phone);

If we execute the SHOW INDEX statement again, we can see that MySQL created a
UNIQUE index index_name_phone for name and phone columns also.

Adding this record into the table produces an error. It is because of the combination of
a name and phone already exists.

1. mysql> INSERT INTO Employee_Detail(ID, Name, Email, Phone, City)

2. VALUES (4, 'Joseph', 'joseph@javatpoint.com', '09896765374', 'Texas');

Look into this output:

MySQL Clustered Index
An index is a separate data structure that allows us to add indexes in the existing
table. It enables you to improve the faster retrieval of records on a database table. It
creates an entry for each value of the indexed columns.

A clustered index is actually a table where the data for the rows are stored. It defines
the order of the table data based on the key values that can be sorted in only one
way. In the database, each table can have only one clustered index. In a relational
database, if the table column contains a primary key or unique key, MySQL allows you
to create a clustered index named PRIMARY based on that specific column.

Characteristics
The essential characteristics of a clustered index are as follows:

o It helps us to store data and indexes at the same time.

o It stores data in only one way based on the key values.
o Key lookup.
o They are scan and index seek.
o Clustered index always use one or more column for creating an index.

Advantages
The main advantages of the clustered index are as follows:

o It helps us to maximize the cache hits and minimizes the page transfer.
o It is an ideal option for range or group with max, min, and count queries.
o At the start of the range, it uses a location mechanism for finding an index entry.

Disadvantages
The main disadvantages of the clustered index are as follows:

o It contains many insert records in a non-sequential order.

o It creates many constant page splits like data pages or index pages.
 o It always takes a long time to update the records.
o It needs extra work for SQL queries, such as insert, updates, and deletes.

Clustered Index on InnoDB Tables

MySQL InnoDB table must have a clustered index. The InnoDB table uses a clustered
index for optimizing the speed of most common lookups and DML (Data Manipulation
Language) operations like INSERT, UPDATE, and DELETE command.

When the primary key is defined in an InnoDB table, MySQL always uses it as a
clustered index named PRIMARY. If the table does not contain a primary key column,
MySQL searches for the unique key. In the unique key, all columns are NOT
NULL and use it as a clustered index. Sometimes, the table does not have a primary
key nor unique key, then MySQL internally creates hidden clustered
index GEN_CLUST_INDEX that contains the values of row id. Thus, there is only one
clustered index in the InnoDB table.

The indexes other than the PRIMARY Indexes (clustered indexes) are known as a
secondary index or non-clustered indexes. In the MySQL InnoDB tables, every record
of the non-clustered index has primary key columns for both row and columns. MySQL
uses this primary key value for searching a row in the clustered index or secondary
index.

Example
In the below statement, the PRIMARY KEY is a clustered index.

1. CREATE TABLE `student_info` (

2. `studentid` int NOT NULL AUTO_INCREMENT,
3. `name` varchar(45) DEFAULT NULL,
4. `age` varchar(3) DEFAULT NULL,
5. `mobile` varchar(20) DEFAULT NULL,
6. `email` varchar(25) DEFAULT NULL,
7. PRIMARY KEY (`studentid`), //clustered index
8. UNIQUE KEY `email_UNIQUE` (`email`)
9. )
Difference between MySQL Clustered and Non-
Clustered Index
The difference between clustered and non-clustered index is the most famous
question in the database related interviews. Both indexes have the same physical
structure and are stored as a BTREE structure in the MySQL server database. In this
section, we are going to explain the most popular differences between them.

Indexing in MySQL is a process that helps us to return the requested data from the
table very fast. If the table does not have an index, it scans the whole table for the
requested data. MySQL allows two different types of Indexing:

1. Clustered Index
2. Non-Clustered Index

Let us first discuss clustered and non-clustered indexing in brief.

What is a Clustered Index?

A clustered index is a table where the data for the rows are stored. It defines the
order of the table data based on the key values that can be sorted in only one
direction. In the database, each table can contains only one clustered index. In a
relational database, if the table column contains a primary key or unique key, MySQL
allows you to create a clustered index named PRIMARY based on that specific
column.

Example

The following example explains how the clustered index created in MySQL:

1. CREATE TABLE Student

2. ( post_id INT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL,
3. CONSTRAINT Post_PK
4. PRIMARY KEY (user_id, post_id), //clustered index
5. CONSTRAINT post_id_UQ
6. UNIQUE (post_id)
7. ) ENGINE = InnoDB ;
Characteristics
Following are the essential characteristics of a clustered index:

o It enables us to store data and indexes together.

o It stores data in only one way based on the key values.
o Key lookup.
o It support index scan and index seek data operations.
o Clustered index always use one or more column for creating an index.
What is a Non-Clustered Index?
The indexes other than PRIMARY indexes (clustered indexes) called a non-clustered
index. The non-clustered indexes are also known as secondary indexes. The non-
clustered index and table data are both stored in different places. It is not able to sort
(ordering) the table data. The non-clustered indexing is the same as a book where the
content is written in one place, and the index is at a different place. MySQL allows a
table to store one or more than one non-clustered index. The non-clustered indexing
improves the performance of the queries which uses keys without assigning primary
key.

Example
1. //It will create non-clustered index
2. CREATE NonClustered INDEX index_name ON table_name (column_name ASC);
Characteristics
Following are the essential characteristics of a non-clustered index:

o It stores only key values.

o It allows accessing secondary data that has pointers to the physical rows.
o It helps in the operation of an index scan and seeks.
o A table can contain one or more than one non-clustered index.
o The non-clustered index row stores the value of a non-clustered key and row locator.

Clustered VS Non-Clustered Index

Let us see some of the popular differences between clustered and non-clustered
indexes through the tabular form:

Parameter Clustered Index Non-Cluster

Definition A clustered index is a table where the data for the rows are stored. In The indexes
a relational database, if the table column contains a primary key, indexes) ca
MySQL automatically creates a clustered index named PRIMARY. clustered i
indexes.

Use for It can be used to sort the record and store the index in physical It creates a
memory. pointers for

Size Its size is large. Its size is sm

Data It accesses the data very fast. It has slowe

Accessing clustered in

Storing It stores records in the leaf node of an index. It does not

Method that means
 Additional It does not require additional reports. It requires
Disk Space separately.

Type of Key It uses the primary key as a clustered index. It can work
composite k

Contains in A table can only one clustered index. A table ca

Table clustered in

Index Id A clustered index always contains an index id of 0. A non-clus

id>0.

Most Important SQL Commands and Statements

1. SELECT Statement

This SQL statement reads the data from the SQL database and shows it as the output
to the database user.

Syntax of SELECT Statement:

1. SELECT column_name1, column_name2, .…, column_nameN

2. [ FROM table_name ]
3. [ WHERE condition ]
4. [ ORDER BY order_column_name1 [ ASC | DESC ], .... ];

Example of SELECT Statement:

1. SELECT Emp_ID, First_Name, Last_Name, Salary, City

2. FROM Employee_details
3. WHERE Salary = 100000
4. ORDER BY Last_Name

the Emp_ID, First_Name, Last_Name, Salary, and City of those employees from
the Employee_details table whose Salary is 100000.
The output shows all the specified details according to the ascending alphabetical
order of Last_Name.

2. UPDATE Statement

This SQL statement changes or modifies the stored data in the SQL database.

Syntax of UPDATE Statement:

1. UPDATE table_name
2. SET column_name1 = new_value_1, column_name2 = new_value_2, ...., column_nameN =
new_value_N
3. [ WHERE CONDITION ];

Example of UPDATE Statement:

1. UPDATE Employee_details
2. SET Salary = 100000
3. WHERE Emp_ID = 10;

3. DELETE Statement

This SQL statement deletes the stored data from the SQL database.

Syntax of DELETE Statement:

1. DELETE FROM table_name

2. [ WHERE CONDITION ];

Example of DELETE Statement:

1. DELETE FROM Employee_details

2. WHERE First_Name = 'Sumit';

This example deletes the record of those employees from

the Employee_details table whose First_Name is Sumit in the table.

4. CREATE TABLE Statement

This SQL statement creates the new table in the SQL database.

Syntax of CREATE TABLE Statement:

 1. CREATE TABLE table_name
2. (
3. column_name1 data_type [column1 constraint(s)],
4. column_name2 data_type [column2 constraint(s)],
5. .....
6. .....,
7. column_nameN data_type [columnN constraint(s)],
8. PRIMARY KEY(one or more col)
9. );

Example of CREATE TABLE Statement:

1. CREATE TABLE Employee_details(

2. Emp_Id NUMBER(4) NOT NULL,
3. First_name VARCHAR(30),
4. Last_name VARCHAR(30),
5. Salary Money,
6. City VARCHAR(30),
7. PRIMARY KEY (Emp_Id)
8. );

This example creates the table Employee_details with five columns or fields in the
SQL database. The fields in the table are Emp_Id, First_Name, Last_Name,
Salary, and City. The Emp_Id column in the table acts as a primary key, which
means that the Emp_Id column cannot contain duplicate values and null values.

5. ALTER TABLE Statement

This SQL statement adds, deletes, and modifies the columns of the table in the SQL
database.

Syntax of ALTER TABLE Statement:

1. ALTER TABLE table_name ADD column_name datatype[(size)];

The above SQL alter statement adds the column with its datatype in the existing
database table.

1. ALTER TABLE table_name MODIFY column_name column_datatype[(size)];

The above 'SQL alter statement' renames the old column name to the new column
name of the existing database table.

1. ALTER TABLE table_name DROP COLUMN column_name;

The above SQL alter statement deletes the column of the existing database table.
Example of ALTER TABLE Statement:

1. ALTER TABLE Employee_details

2. ADD Designation VARCHAR(18);

This example adds the new field whose name is Designation with size 18 in
the Employee_details table of the SQL database.

6. DROP TABLE Statement

This SQL statement deletes or removes the table and the structure, views,
permissions, and triggers associated with that table.

Syntax of DROP TABLE Statement:

1. DROP TABLE [ IF EXISTS ]

2. table_name1, table_name2, ……, table_nameN;

The above syntax of the drop statement deletes specified tables completely if they
exist in the database.

Example of DROP TABLE Statement:

1. DROP TABLE Employee_details;

This example drops the Employee_details table if it exists in the SQL database. This
removes the complete information if available in the table.

7. CREATE DATABASE Statement

This SQL statement creates the new database in the database management
system.

Syntax of CREATE DATABASE Statement:

1. CREATE DATABASE database_name;

Example of CREATE DATABASE Statement:

1. CREATE DATABASE Company;

The above example creates the company database in the system.

8. DROP DATABASE Statement

This SQL statement deletes the existing database with all the data tables and views
from the database management system.

Syntax of DROP DATABASE Statement:

1. DROP DATABASE database_name;

Example of DROP DATABASE Statement:

1. DROP DATABASE Company;

The above example deletes the company database from the system.

9. INSERT INTO Statement

This SQL statement inserts the data or records in the existing table of the SQL
database. This statement can easily insert single and multiple records in a single
query statement.

Syntax of insert a single record:

1. INSERT INTO table_name

2. (
3. column_name1,
4. column_name2, .…,
5. column_nameN
6. )
7. VALUES
8. (value_1,
9. value_2, ..…,
10.value_N
11. );

Example of insert a single record:

1. INSERT INTO Employee_details

2. (
3. Emp_ID,
4. First_name,
5. Last_name,
 6. Salary,
7. City
8. )
9. VALUES
10.(101,
11. Akhil,
12.Sharma,
13. 40000,
14.Bangalore
15. );

This example inserts 101 in the first column, Akhil in the second column, Sharma in
the third column, 40000 in the fourth column, and Bangalore in the last column of
the table Employee_details.

Syntax of inserting a multiple records in a single query:

1. INSERT INTO table_name

2. ( column_name1, column_name2, .…, column_nameN)
3. VALUES (value_1, value_2, ..…, value_N), (value_1, value_2, ..…, value_N),….;

Example of inserting multiple records in a single query:

1. INSERT INTO Employee_details

2. ( Emp_ID, First_name, Last_name, Salary, City )
3. VALUES (101, Amit, Gupta, 50000, Mumbai), (101, John, Aggarwal, 45000, Calc
utta), (101, Sidhu, Arora, 55000, Mumbai);

This example inserts the records of three employees in the Employee_details table
in the single query statement.

10. TRUNCATE TABLE Statement

This SQL statement deletes all the stored records from the table of the SQL
database.

Syntax of TRUNCATE TABLE Statement:

1. TRUNCATE TABLE table_name;

Example of TRUNCATE TABLE Statement:

1. TRUNCATE TABLE Employee_details;

This example deletes the record of all employees from the Employee_details table of
the database.
11. DESCRIBE Statement
This SQL statement tells something about the specified table or view in the query.

Syntax of DESCRIBE Statement:

1. DESCRIBE table_name | view_name;

Example of DESCRIBE Statement:

1. DESCRIBE Employee_details;

This example explains the structure and other details about

the Employee_details table.

12. DISTINCT Clause

This SQL statement shows the distinct values from the specified columns of the
database table. This statement is used with the SELECT keyword.

Syntax of DISTINCT Clause:

1. SELECT DISTINCT column_name1, column_name2, ...

2. FROM table_name;

Example of DISTINCT Clause:

1. SELECT DISTINCT City, Salary

2. FROM Employee_details;

This example shows the distinct values of the City and Salary column from
the Employee_details table.

13. COMMIT Statement

This SQL statement saves the changes permanently, which are done in the
transaction of the SQL database.

Syntax of COMMIT Statement:

1. COMMIT

Example of COMMIT Statement:

1. DELETE FROM Employee_details

2. WHERE salary = 30000;
3. COMMIT;
This example deletes the records of those employees whose Salary is 30000 and
then saves the changes permanently in the database.

14. ROLLBACK Statement

This SQL statement undo the transactions and operations which are not yet
saved to the SQL database.

Syntax of ROLLBACK Statement:

1. ROLLBACK

Example of ROLLBACK Statement:

1. DELETE FROM Employee_details

2. WHERE City = Mumbai;
3. ROLLBACK;

This example deletes the records of those employees whose City is Mumbai and then
undo the changes in the database.

15. CREATE INDEX Statement

This SQL statement creates the new index in the SQL database table.

Syntax of CREATE INDEX Statement:

1. CREATE INDEX index_name

2. ON table_name ( column_name1, column_name2, …, column_nameN );

Example of CREATE INDEX Statement:

1. CREATE INDEX idx_First_Name

2. ON employee_details (First_Name);

This example creates an index idx_First_Name on the First_Name column of

the Employee_details table.

16. DROP INDEX Statement

This SQL statement deletes the existing index of the SQL database table.

Syntax of DROP INDEX Statement:

1. DROP INDEX index_name;

Example of DROP INDEX Statement:

1. DROP INDEX idx_First_Name;

This example deletes the index idx_First_Name from the SQL database.

17. USE Statement

This SQL statement selects the existing SQL database. Before performing the
operations on the database table, you have to select the database from the multiple
existing databases.

Syntax of USE Statement:

1. USE database_name;

Example of USE DATABASE Statement:

1. USE Company;

SQL
keywords are NOT case sensitive: select is the same as SELECT
 SELECT - extracts data from a database
 UPDATE - updates data in a database
 DELETE - deletes data from a database
 INSERT INTO - inserts new data into a database
 CREATE DATABASE - creates a new database
 ALTER DATABASE - modifies a database
 CREATE TABLE - creates a new table
 ALTER TABLE - modifies a table
 DROP TABLE - deletes a table
 CREATE INDEX - creates an index (search key)
 DROP INDEX - deletes an index

Select Syntax

Select ALL columns

MySQL WHERE Clause
 The WHERE clause is used to filter records.

 MySQL WHERE Clause is used with SELECT, INSERT, UPDATE and DELETE clause
to filter the results. It specifies a specific position where you have to do the
operation.

Syntax:

1. WHERE conditions;
MySQL WHERE Clause with single condition

Table structure:

Execute this query:

 1. SELECT *
2. FROM officers
3. WHERE address = 'Mau';

Output:

MySQL WHERE Clause with AND condition

Execute the following query:

1. SELECT *
2. FROM officers
3. WHERE address = 'Lucknow'
4. AND officer_id < 5;
Output:

WHERE Clause with OR condition

Execute the following query:

1. SELECT *
2. FROM officers
3. WHERE address = 'Lucknow'
4. OR address = 'Mau';

Output:
MySQL WHERE Clause with combination of AND & OR conditions

Execute the following query:

1. SELECT *
2. FROM officers
3. WHERE (address = 'Mau' AND officer_name = 'Ajeet')
4. OR (officer_id < 5);

Output:
MySQL Distinct Clause

MySQL DISTINCT clause is used to remove duplicate records from the table and
fetch only the unique records.

The DISTINCT clause is only used with the SELECT statement.

Syntax:

1. SELECT DISTINCT expressions

2. FROM tables
3. [WHERE conditions];

Parameters
expressions: specify the columns or calculations that you want to retrieve.

tables: specify the name of the tables from where you retrieve records. There must
be at least one table listed in the FROM clause.
WHERE conditions: It is optional. It specifies the conditions that must be met for the
records to be selected.

Note:

o If you put only one expression in the DISTINCT clause, the query will return the unique
values for that expression.
o If you put more than one expression in the DISTINCT clause, the query will retrieve
unique combinations for the expressions listed.
o In MySQL, the DISTINCT clause doesn't ignore NULL values. So if you are using the
DISTINCT clause in your SQL statement, your result set will include NULL as a distinct
value.

MySQL DISTINCT Clause with single expression

If you use a single expression then the MySQL DISTINCT clause will return a single
field with unique records (no duplicate record).

See the table:

Use the following query:

1. SELECT DISTINCT address

2. FROM officers;
MySQL DISTINCT Clause with multiple expressions

If you use multiple expressions with DISTINCT Clause then MySQL DISTINCT clause will
remove duplicates from more than one field in your SELECT statement.

Use the following query:

1. SELECT DISTINCT officer_name, address

2. FROM officers;
MySQL FROM Clause

 The MySQL FROM Clause is used to select some records from a table.

 It can also be used to retrieve records from multiple tables using JOIN condition.

Syntax:

1. FROM table1
2. [ { INNER JOIN | LEFT [OUTER] JOIN| RIGHT [OUTER] JOIN } table2
3. ON table1.column1 = table2.column1 ]

Parameters
table1 and table2: specify tables used in the MySQL statement. The two tables are
joined based on table1.column1 = table2.column1.

Note:

o If you are using the FROM clause in a MySQL statement then at least one table must
have been selected.
o If you are using two or more tables in the MySQL FROM clause, these tables are
generally joined using INNER or OUTER joins.
MySQL FROM Clause: Retrieve data from one table

The following query specifies how to retrieve data from a single table.

Use the following Query:

1. SELECT *
2. FROM officers
3. WHERE officer_id <= 3;
MySQL FROM Clause: Retrieve data from two tables with inner
join

Let's take an example to retrieve data from two tables using INNER JOIN.

Here, we have two tables "officers" and "students".

Execute the following query:

1. SELECT officers.officer_id, students.student_name

2. FROM students
3. INNER JOIN officers
4. ON students.student_id = officers.officer_id;

MySQL FROM Clause: Retrieve data from two tables using outer
join

Execute the following query:

 1. SELECT officers.officer_id, students.student_name
2. FROM officers
3. LEFT OUTER JOIN students
4. ON officers.officer_id = students.student_id;

MySQL ORDER BY Clause

The MYSQL ORDER BY Clause is used to sort the records in ascending or

descending order.

Syntax:

1. SELECT expressions
2. FROM tables
3. [WHERE conditions]
 4. ORDER BY expression [ ASC | DESC ];
Parameters

expressions: It specifies the columns that you want to retrieve.

tables: It specifies the tables, from where you want to retrieve records. There must
be at least one table listed in the FROM clause.

WHERE conditions: It is optional. It specifies conditions that must be fulfilled for the
records to be selected.

ASC: It is optional. It sorts the result set in ascending order by expression (default, if
no modifier is provider).

DESC: It is also optional. It sorts the result set in descending order by expression.

Note: You can use MySQL ORDER BY clause in a SELECT statement, SELECT LIMIT
statement, and DELETE LIMIT statement.

MySQL ORDER BY: without using ASC/DESC attribute

If you use MySQL ORDER BY clause without specifying the ASC and DESC modifier
then by default you will get the result in ascending order.

Execute the following query:

1. SELECT *
2. FROM officers
3. WHERE address = 'Lucknow'
4. ORDER BY officer_name;

Output:
MySQL ORDER BY: with ASC attribute

Let's take an example to retrieve the data in ascending order.

Execute the following query:

1. SELECT *
2. FROM officers
3. WHERE address = 'Lucknow'
4. ORDER BY officer_name ASC;

Output:
MySQL ORDER BY: with DESC attribute

1. SELECT *
2. FROM officers
3. WHERE address = 'Lucknow'
4. ORDER BY officer_name DESC;
MySQL ORDER BY: using both ASC and DESC attributes

Execute the following query:

1. SELECT officer_name, address

2. FROM officers
3. WHERE officer_id < 5
4. ORDER BY officer_name DESC, address ASC;

Output:
MySQL GROUP BY Clause

The MYSQL GROUP BY Clause is used to collect data from multiple records and group
the result by one or more column. It is generally used in a SELECT statement.

You can also use some aggregate functions like COUNT, SUM, MIN, MAX, AVG etc.
on the grouped column.

Syntax:

1. SELECT expression1, expression2, ... expression_n,

2. aggregate_function (expression)
3. FROM tables
4. [WHERE conditions]
5. GROUP BY expression1, expression2, ... expression_n;

Parameters
expression1, expression2, ... expression_n: It specifies the expressions that are
not encapsulated within an aggregate function and must be included in the GROUP BY
clause.

aggregate_function: It specifies a function such as SUM, COUNT, MIN, MAX, or AVG

etc. tables: It specifies the tables, from where you want to retrieve the records. There
must be at least one table listed in the FROM clause.

WHERE conditions: It is optional. It specifies the conditions that must be fulfilled for
the records to be selected.

MySQL GROUP BY Clause with COUNT function

Execute the following query:

1. SELECT address, COUNT(*)

2. FROM officers
3. GROUP BY address;

Output:
MySQL GROUP BY Clause with SUM function

Now, the following query will GROUP BY the example using the SUM function and
return the emp_name and total working hours of each employee.

Execute the following query:

1. SELECT emp_name, SUM(working_hours) AS "Total working hours"

2. FROM employees
3. GROUP BY emp_name;

Output:
MySQL GROUP BY Clause with MIN function

Execute the following query:

1. SELECT emp_name, MIN(working_hours) AS "Minimum working hour"

2. FROM employees
3. GROUP BY emp_name;

Output:
MySQL GROUP BY Clause with MAX function

Execute the following query:

1. SELECT emp_name, MAX (working_hours) AS "Minimum working hour"

2. FROM employees
3. GROUP BY emp_name;

Output:

MySQL GROUP BY Clause with AVG function

Execute the following query:

1. SELECT emp_name, AVG(working_hours) AS "Average working hour"

2. FROM employees
3. GROUP BY emp_name;

Output:

MySQL HAVING Clause

 MySQL HAVING Clause is used with GROUP BY clause.

 It always returns the rows where condition is TRUE.

Syntax:

1. SELECT expression1, expression2, ... expression_n,

 2. aggregate_function (expression)
3. FROM tables
4. [WHERE conditions]
5. GROUP BY expression1, expression2, ... expression_n
6. HAVING condition;
Parameters

aggregate_function: It specifies any one of the aggregate function such as SUM,

COUNT, MIN, MAX, or AVG.

expression1, expression2, ... expression_n: It specifies the expressions that are

not encapsulated within an aggregate function and must be included in the GROUP BY
clause.

WHERE conditions: It is optional. It specifies the conditions for the records to be

selected.

HAVING condition: It is used to restrict the groups of returned rows. It shows only
those groups in result set whose conditions are TRUE.

HAVING Clause with SUM function

Consider a table "employees" table having the following data.

Execute the following query:

1. SELECT emp_name, SUM(working_hours) AS "Total working hours"

2. FROM employees
3. GROUP BY emp_name
4. HAVING SUM(working_hours) > 5;
Simply, it can also be used with COUNT, MIN, MAX and AVG functions.

Conditions
MySQL AND Condition

The MySQL AND condition is used with SELECT, INSERT, UPDATE or DELETE

Syntax:
1. WHERE condition1
2. AND condition2
3. ...
4. AND condition_n;
Parameter explanation:

condition1, condition2, ... condition_n: Specifies all conditions that must be

fulfilled for the records to be selected.
MySQL AND Example

Consider a table "cus_tbl", having the following data:

Execute the following query:

1. SELECT *
2. FROM cus_tbl
3. WHERE cus_firstname = 'Ajeet'
4. AND cus_id > 3;

Output:
MySQL OR Condition

The MySQL OR condition specifies that if you take two or more conditions then one of
the conditions must be fulfilled to get the records as result.

Syntax:

1. WHERE condition1
2. OR condition2
3. ...
4. OR condition_n;
Parameter explanation

condition1, condition2, ... condition_n: Specifies all conditions that must be

fulfilled for the records to be selected.
MySQL OR Example

Consider a table "cus_tbl", having the following data:

Execute the following query:

1. SELECT *
2. FROM cus_tbl
3. WHERE cus_firstname = 'Ajeet'
4. OR cus_id > 100;

Output:
MySQL AND & OR condition

In MySQL, you can use AND & OR condition both together with the SELECT, INSERT,
UPDATE and DELETE statement. While combine these conditions, you must be aware
where to use round brackets so that the database know the order to evaluate each
condition.

Syntax:

1. WHERE condition1
2. AND condition2
3. ...
4. OR condition_n;
Parameter

condition1, condition2, ... condition_n: It specifies the conditions that are

evaluated to determine if the records will be selected.

MySQL AND OR Example

Consider a table "students", having the following data.

Execute the following query:

1. SELECT *
2. FROM students
3. WHERE (course_name = 'Java' AND student_name = 'Aryan')
4. OR (student_id < 2);

Output:
MySQL Boolean
 A Boolean is the simplest data type that always returns two possible values,
either true or false.
 It can always use to get a confirmation in the form of YES or No value.
 MySQL does not contain built-in Boolean or Bool data type.
 They provide a TINYINT data type instead of Boolean or Bool data types.
 MySQL considered value zero as false and non-zero value as true.
 If you want to use Boolean literals, use true or false that always evaluates to 0 and
1 value.
 The 0 and 1 represent the integer values.

Execute the following statement to see the integer values of Boolean literals:

1. Mysql> Select TRUE, FALSE, true, false, True, False;

After successful execution, the following result appears:

MySQL Boolean Example

We can store a Boolean value in the MySQL table as an integer data type. Let us
create a table student that demonstrates the use of Boolean data type in MySQL:

1. mysql> CREATE TABLE student (

2. studentid INT PRIMARY KEY AUTO_INCREMENT,
3. name VARCHAR(40) NOT NULL,
4. age VARCHAR(3),
5. pass BOOLEAN
 6. );

In the above query, we can see that the pass field is defined as a Boolean when
showing the definition of a table; it contains TINIINT as follows:

1. mysql> DESCRIBE student;

Let us add two new rows in the above table with the help of following query:

1. mysql> INSERT INTO student(name, pass) VALUES('Peter',true), ('John',false);

When the above query executed, immediately MySQL checks for the Boolean data
type in the table. If the Boolean literals found, it will be converted into integer values
0 and 1. Execute the following query to get the data from the student table:

1. Mysql> SELECT studentid, name, pass FROM student;

You will get the following output where the true and false literal gets converted into 0
and 1 value.

Since MySQL always use TINYINT as Boolean, we can also insert any integer values
into the Boolean column. Execute the following statement:

1. Mysql> INSERT INTO student(name, pass) VALUES('Miller',2);

You will get the following result:

In some cases, you need to get the result in true and false literals. In that case, you
need to execute the if() function with the select statement as follows:
1. Mysql> SELECT studentid, name, IF(pass, 'true', 'false') completed FROM student
1;

It will give the following output:

MySQL Boolean Operators

MySQL also allows us to use operators with the Boolean data type. Execute the
following query to get all the pass result of table student.

1. SELECT studentid, name, pass FROM student1 WHERE pass = TRUE;

This statement returns the following output:

The above statement only returns the pass result if the value is equal to 1. We can fix
it by using the IS operator. This operator validates the value with the Boolean value.
The following statement explains this:

1. SELECT studentid, name, pass FROM student1 WHERE pass is TRUE;

After executing this statement, you will get the following result:

If you want to see the pending result, use IS FALSE or IS NOT TRUE operator as
below:

1. SELECT studentid, name, pass FROM student1 WHERE pass IS FALSE;

2.
3. OR,
4.
5. SELECT studentid, name, pass FROM student1 WHERE pass IS NOT TRUE;

You will get the following output:

MySQL LIKE condition
In MySQL, LIKE condition is used to perform pattern matching to find the correct
result. It is used in SELECT, INSERT, UPDATE and DELETE statement with the
combination of WHERE clause.

Syntax:

1. expression LIKE pattern [ ESCAPE 'escape_character' ]

Parameters
expression: It specifies a column or field.

pattern: It is a character expression that contains pattern matching.

escape_character: It is optional. It allows you to test for literal instances of a

wildcard character such as % or _. If you do not provide the
escape_character, MySQL assumes that "\" is the escape_character.

MySQL LIKE Examples

1) Using % (percent) Wildcard:
Consider a table "officers" having the following data.

Execute the following query:

1. SELECT officer_name
2. FROM officers
 3. WHERE address LIKE 'Luck%';

Output:

2) Using _ (Underscore) Wildcard:

We are using the same table "officers" in this example too.

Execute the following query:

1.
2. SELECT officer_name
3. FROM officers
4. WHERE address LIKE 'Luc_now';

Output:
3) Using NOT Operator:
You can also use NOT operator with MySQL LIKE condition. This example shows the
use of % wildcard with the NOT Operator.

Consider a table "officers" having the following data.

Execute the following query:

1. SELECT officer_name
2. FROM officers
3. WHERE address NOT LIKE 'Luck%';

Output:
MySQL IN Condition
The MySQL IN condition is used to reduce the use of multiple OR conditions in a
SELECT, INSERT, UPDATE and DELETE statement.

Syntax:

1. expression IN (value1, value2, .... value_n);

Parameters
expression: It specifies a value to test.

value1, value2, ... or value_n: These are the values to test against expression. If
any of these values matches expression, then the IN condition will evaluate to true.
This is a quick method to test if any one of the values matches expression.

MySQL IN Example
Consider a table "officers", having the following data.
Execute the following query:

1. SELECT *
2. FROM officers
3. WHERE officer_name IN ('Ajeet', 'Vimal', 'Deepika');

Output:

Let's see why it is preferred over OR condition:

Execute the following query:

1. SELECT *
2. FROM officers
3. WHERE officer_name = 'Ajeet'
4. OR officer_name = 'Vimal'
5. OR officer_name = 'Deepika';
Output:

It also produces the same result. So IN condition is preferred over OR condition

because it has minimum number of codes.
MySQL ANY
The ANY keyword is a MySQL operator that returns the Boolean value TRUE if the
comparison is TRUE for ANY of the subquery condition. In other words, this keyword
returns true if any of the subquery condition is fulfilled when the SQL query is
executed. The ANY keyword must follow the comparison operator. It is noted
that ALL SQL operator works related to ANY operator, but it returns true when all the
subquery values are satisfied by the condition in MySQL.

The ANY operator works like comparing the value of a table to each value in the result
set provided by the subquery condition. And then, if it finds any value that matches at
least one value/row of the subquery, it returns the TRUE result.

Syntax
The following is the syntax that illustrates the use of ANY operator in MySQL:

1. operand comparison_operator ANY (subquery)

Where comparison operators can be one of the following:

1. = > < >= <= <> !=

This syntax can also be written as:

1. SELECT column_lists FROM table_name1 WHERE column_name Operator ANY

(SELECT column_name FROM table_name2 WHERE condition);

We can understand how ANY works in MySQL through the below statement:

1. SELECT colm1 FROM table1 WHERE colm1 > ANY (SELECT colm1 FROM table
2);

Suppose table1 has a row that contains a number (10). In such a case, the above
expression returns true if table2 contains (20, 15, and 6). It is because there is a
value 6 in table2, which is less than 10. This expression returns false if table2
contains (15, 20), or if table2 is empty. If all the table fields contain (NULL, NULL,
NULL), this expression is unknown.
Example
Let us create a two table named table1 and table2 and then insert some values into
them using the below statements:

1. CREATE TABLE table1 (

2. num_value INT
3. );
4. INSERT INTO table1 (num_value)
5. VALUES(10), (20), (25);
6.
7. CREATE TABLE table2 (
8. num_val int
9. );
10.INSERT INTO table2 (num_val)
11. VALUES(20), (7), (10);

After successful execution of the above statement, we can verify it by using

the SELECT statement as follows:

Now, we will execute the below statement to understand the use of the ANY operator:

1. SELECT num_value FROM table1

2. WHERE num_value > ANY (SELECT num_val FROM table2);

This statement returns true and gives the below output because table2 contains (20,
10, and 7) and there is a value 7 in table2 which is less than 10, 20, and 25 of table1.
The IN keyword in MySQL is an alias for = ANY when used with a subquery in the
statement. Hence, the below two statements are identical in MySQL:

1. SELECT colm1 FROM table1 WHERE colm1 = ANY (SELECT colm1 FROM table
2);
2. SELECT colm1 FROM table1 WHERE colm1 IN (SELECT colm1 FROM table2);

But we cannot say that IN and = ANY were synonyms when we used it with an
expression list. It is because IN can take a list of expressions, but = ANY cannot.

Also, NOT IN cannot be an alias for <> ANY operator, but it can be used for <> ALL.

The word SOME in MySQL can be an alias for ANY. Therefore, these
two SQL statements are equivalent:

1. SELECT colm1 FROM table1 WHERE colm1 <>ANY (SELECT colm1 FROM tabl
e2);
2. SELECT colm1 FROM table1 WHERE colm1 <> SOME (SELECT colm1 FROM table2);
Advantages of ANY operator in MySQL
o ANY is a logical operator that returns the Boolean value. It allows us to select any or
some rows of the SELECT statement.
o Since comparison operators precede this operator, it always returns TRUE if any
subqueries satisfy the specified condition.
o It provides the result, which is a unique column value from a table that matches any
record in the second table.
o We can perform several comparisons using ANY operator with the SELECT and WHERE
keywords.

In this article, we have learned how to use the ANY operator in MySQL. It filters the
result set from SQL syntax only when any of the values satisfy the condition.
Otherwise, it gives a false value.
MySQL Exists
The EXISTS operator in MySQL is a type of Boolean operator which returns the true or
false result. It is used in combination with a subquery and checks the existence of
data in a subquery. It means if a subquery returns any record, this operator returns
true. Otherwise, it will return false. The true value is always represented numeric
value 1, and the false value represents 0. We can use it with SELECT, UPDATE,
DELETE, INSERT statement.

Syntax
The following are the syntax to use the EXISTS operator in MySQL:

1. SELECT col_names
2. FROM tab_name
3. WHERE [NOT] EXISTS (
4. SELECT col_names
 5. FROM tab_name
6. WHERE condition
7. );

The NOT operator is used to negates the EXISTS operator. It returns true when the
subquery does not return any row. Otherwise, it returns false.

Generally, the EXISTS query begins with SELECT *, but it can start with the SELECT
column, SELECT a_constant, or anything in the subquery. It will give the same output
because MySQL ignores the select list in the SUBQUERY.

This operator terminates immediately for further processing after the matching result
found. This feature improves the performance of the query in MySQL.

Parameter Explanation
The following are parameters used in the EXISTS operator:

Parameter Name Descriptions

col_names It is the name of column(s) that contains in the specified table.

tab_name It is the name of the table from which we are going to perform the EXISTS

condition It specifies for searching the specific value from the table.

subquery It is usually the SELECT statement that begins with SELECT *, but MySQL

MySQL EXISTS Operator Example

Let us understand how the EXISTS operator works in MySQL. Here, we are going to
first create two tables named "customer" and "orders" using the following
statement:

1. CREATE TABLE customer(

2. cust_id int NOT NULL,
3. name varchar(35),
4. occupation varchar(25),
5. age int
6. );

1. CREATE TABLE orders (

2. order_id int NOT NULL,
3. cust_id int,
4. prod_name varchar(45),
 5. order_date date
6. );

Next, we need to insert values into both tables. Execute the below statements:

1. INSERT INTO customer(cust_id, name, occupation, age)

2. VALUES (101, 'Peter', 'Engineer', 32),
3. (102, 'Joseph', 'Developer', 30),
4. (103, 'John', 'Leader', 28),
5. (104, 'Stephen', 'Scientist', 45),
6. (105, 'Suzi', 'Carpenter', 26),
7. (106, 'Bob', 'Actor', 25),
8. (107, NULL, NULL, NULL);

1. INSERT INTO orders (order_id, cust_id, prod_name, order_date)

2. VALUES (1, '101', 'Laptop', '2020-01-10'),
3. (2, '103', 'Desktop', '2020-02-12'),
4. (3, '106', 'Iphone', '2020-02-15'),
5. (4, '104', 'Mobile', '2020-03-05'),
6. (5, '102', 'TV', '2020-03-20');

To verify the tables, run the SELECT command as below:

1. msql> SELECT * FROM customer;

2. AND,
3. mysql> SELECT * FROM orders;

We will get the below output:

MySQL SELECT EXISTS Example
In this example, we are going to use EXISTS operator to find the name and occupation
of the customer who has placed at least one order:

1. mysql> SELECT name, occupation FROM customer

2. WHERE EXISTS (SELECT * FROM Orders
3. WHERE customer.cust_id = Orders.cust_id);

The following output appears:

Again, if we want to get the name of the customer who has not placed an order, then
use the NOT EXISTS operator:

1. mysql> SELECT name, occupation FROM customer

2. WHERE NOT EXISTS (SELECT * FROM Orders
 3. WHERE customer.cust_id = Orders.cust_id);

It will give the below output:

MySQL EXISTS With DELETE Statement Example

Suppose we want to delete a record from the Orders table whose order_id = 3,
execute the following query that deletes the record from Orders table permanently:

1. mysql> DELETE FROM Orders WHERE EXISTS (

2. SELECT * FROM customer
3. WHERE order_id=3);

To verify the output, run the below command:

1. mysql> SELECT * FROM Orders;

In the output, we can see that the table record whose order_id=3 is deleted
successfully.

If we want to check whether a row exists in a table or not, use the following query:

1. mysql> SELECT EXISTS(SELECT * from customer WHERE cust_id=104) AS Re

sult;

We will get the output 1 that means true. Hence, cust_id=104 exists in the table.
Difference between EXISTS and IN operator
The main differences between the EXISTS and IN operator is given in a tabular form:

S IN EXISTS
N

1. It is used to minimize the multiple OR conditions in MySQL. It is used to check the existen

2. SELECT col_names FROM tab_name WHERE col_name IN SELECT col_names

(subquery); FROM tab_name
WHERE [NOT] EXISTS (subquer

3. It compares all values inside the IN operator. It stops for further execut
occurrence.

4. It can use for comparing NULL values. It cannot use for comparing N

5. It executes faster when the subquery result is less. It executes faster when the s

6. It performs a comparison between parent query and child It does not perform a comp
query or subquery. query or subquery.
MySQL NOT Condition
The MySQL NOT condition is opposite of MySQL IN condition. It is used to negate a
condition in a SELECT, INSERT, UPDATE or DELETE statement.

Syntax:

1. NOT condition

Parameter
condition: It specifies the conditions that you want to negate.

MySQL NOT Operator with IN condition

Consider a table "officers", having the following data.

Execute the following query:

1. SELECT *
2. FROM officers
 3. WHERE officer_name NOT IN ('Ajeet','Vimal','Deepika');

Output:

MySQL NOT Operator with IS NULL condition:

Execute the following query:

1. SELECT *
2. FROM officers
3. WHERE officer_name IS NOT NULL;

Output:

MySQL NOT Operator with LIKE condition:

We are taking the same table "officer" for this operation also:
Execute the following query:

1. SELECT *
2. FROM officers
3. WHERE officer_name NOT LIKE 'A%';

Output:

MySQL NOT Operator with BETWEEN condition:

We are taking the same table "officer" for this operation also:

Execute the following query:

1. SELECT *
2. FROM officers
3. WHERE officer_id NOT BETWEEN 3 AND 5;

Output:
MySQL Not Equal
MySQL Not Equal is an inequality operator that used for returning a set of rows
after comparing two expressions that are not equal. The MySQL contains two types of
Not Equal operator, which are (< >) and (! =).
Difference Between (< >) and (! =) Operator
The Not Equal operators in MySQL works the same to perform an inequality test
between two expressions. They always give the same result. However, they contain
one difference that “< >” follows the ISO standard whereas “!=” does not follow ISO
standard.

Example 1

Let us create a table student to understand how Not Equal operator works in MySQL.
Suppose the “students” table contains the following data:

Table: students

If you want to get the student details who do not belong to England, then you need
to execute the following statement:

1. SELECT * FROM students WHERE city <> "England";

OR,

1. SELECT * FROM students WHERE city != "England";

After successful execution of the above queries, we will get the same output as below:

Example 2

In this example, we are going to understand how Not Equal operator works
with Group By clause. We can use the Group By clause for grouping rows that have
the same data. If we want to get all customers who do not have cellphone
number and duplicate income value, execute the following statement:

1. SELECT * FROM customers

2. JOIN contacts ON customer_id = contact_id
3. WHERE cellphone <> "Null"
4. GROUP BY income;

We will get the following output:

Example 3

In this example, we are going to understand how Not Equal operator works with
a JOIN statement. Let us create a table "contacts" and "customers" in a database
that contains the following data:

Table: contacts

Table: customers

If we want to get all the records from table customers and contacts where the
cellphone is Null, execute the following statement that returns all customers who do
not have a cellphone number:

1. SELECT * FROM customers

2. JOIN contacts ON customer_id = contact_id
3. WHERE cellphone != "Null;

After successful execution, it will give the following output:

Example 4

In this example, we are going to understand how the Not Equal operator works with
multiple conditions in the WHERE clause. For example, we want to get the customer
details where income is higher than 40000, and occupation is not
a developer. Execute the following statement to get the result:
 1. SELECT * FROM customers Where income>40000 and occupation<>"Develop
er";

After the successful execution of the above statement, we will get the following
output.

MySQL IS NULL Condition

MySQL IS NULL condition is used to check if there is a NULL value in the expression. It
is used with SELECT, INSERT, UPDATE and DELETE statement.

Syntax:

1. expression IS NULL

Parameter
expression: It specifies a value to test if it is NULL value.

Consider a table "officers" having the following data.

Execute the following query:

1. SELECT *
2. FROM officers
3. WHERE officer_name IS NULL;

Output:

Note: Here, you are getting the empty result because there is no NULL value in
officer_name column.
MySQL IS NOT NULL Condition
MySQL IS NOT NULL condition is used to check the NOT NULL value in the expression.
It is used with SELECT, INSERT, UPDATE and DELETE statements.

Syntax:

1. expression IS NOT NULL

Parameter
expression: It specifies a value to test if it is not NULL value.

MySQL IS NOT NULL Example

Consider a table "officers" having the following data.
Execute the following query:

1. SELECT *
2. FROM officers
3. WHERE officer_name IS NOT NULL;

Output:

Note: Here, you are getting the complete "officers" table as result because every
value is NOT NULL in the table.
MySQL BETWEEN Condition
The MYSQL BETWEEN condition specifies how to retrieve values from an expression
within a specific range. It is used with SELECT, INSERT, UPDATE and DELETE
statement.

Syntax:

1. expression BETWEEN value1 AND value2;

Parameters
expression: It specifies a column.

value1 and value2: These values define an inclusive range that expression is
compared to.

Let's take some examples:

(i) MySQL BETWEEN condition with numeric value:

Consider a table "officers" having the following data.

Execute the following query:

1. SELECT *
2. FROM officers
3. WHERE officer_id BETWEEN 1 AND 3;

Output:
Note: In the above example, you can see that only three rows are returned between 1 and 3.

(ii) MySQL BETWEEN condition with date:

MySQL BETWEEN condition also facilitates you to retrieve records according to date.

See this example:

Consider a table "employees", having the following data.

Execute the following query:

1. SELECT *
2. FROM employees
3. WHERE working_date BETWEEN CAST ('2015-01-24' AS DATE) AND CAST
('2015-01-25' AS DATE);

Output:
Note: In the above example you can see that only data between specific dates are shown.

Aggregate Functions

MySQL Aggregate Functions

MySQL's aggregate function is used to perform calculations on multiple values
and return the result in a single value like the average of all values, the sum
of all values, and maximum & minimum value among certain groups of values. We
mostly use the aggregate functions with SELECT statements in the data query languages.

Syntax:
The following are the syntax to use aggregate functions in MySQL:

1. function_name (DISTINCT | ALL expression)

In the above syntax, we had used the following parameters:

o First, we need to specify the name of the aggregate function.

o Second, we use the DISTINCT modifier when we want to calculate the result based on
distinct values or ALL modifiers when we calculate all values, including duplicates. The
default is ALL.
o Third, we need to specify the expression that involves columns and arithmetic
operators.

There are various aggregate functions available in MySQL. Some of the most
commonly used aggregate functions are summarised in the below table:

Aggregate Function Descriptions

count() It returns the number of rows, including rows with NULL values in

sum() It returns the total summed values (Non-NULL) in a set.

average() It returns the average value of an expression.

min() It returns the minimum (lowest) value in a set.

max() It returns the maximum (highest) value in a set.

groutp_concat() It returns a concatenated string.

first() It returns the first value of an expression.

last() It returns the last value of an expression.

Why we use aggregate functions?

We mainly use the aggregate functions in databases, spreadsheets and many other
data manipulation software packages. In the context of business, different
organization levels need different information such as top levels managers interested
in knowing whole figures and not the individual details. These functions produce the
summarised data from our database. Thus they are extensively used in economics
and finance to represent the economic health or stock and sector performance.

Let us take an example of myflix (video streaming website which has huge collections
of the movie) database, where management may require the following details:

o Most rented movies.

o Least rented movies.
 o Average number that each movie is rented out in a month.

We can easily produce these details with the help of aggregate functions.

Let us discuss the most commonly used aggregate functions in detail. First, we will
create a new table for the demonstration of all aggregate functions.

Execute the below statement to create an employee table:

1. CREATE TABLE employee(

2. name varchar(45) NOT NULL,
3. occupation varchar(35) NOT NULL,
4. working_date date,
5. working_hours varchar(10)
6. );

Execute the below statement to insert the records into the employee table:

1. INSERT INTO employee VALUES

2. ('Robin', 'Scientist', '2020-10-04', 12),
3. ('Warner', 'Engineer', '2020-10-04', 10),
4. ('Peter', 'Actor', '2020-10-04', 13),
5. ('Marco', 'Doctor', '2020-10-04', 14),
6. ('Brayden', 'Teacher', '2020-10-04', 12),
7. ('Antonio', 'Business', '2020-10-04', 11);

Now, execute the SELECT statement to show the record:

Count() Function
MySQL count() function returns the total number of values in the expression. This
function produces all rows or only some rows of the table based on a specified
condition, and its return type is BIGINT. It returns zero if it does not find any
matching rows. It can work with both numeric and non-numeric data types.

Example
Suppose we want to get the total number of employees in the employee table, we
need to use the count() function as shown in the following query:

1. mysql> SELECT COUNT(name) FROM employee;

Output:

After execution, we can see that this table has six employees.

To read more information, click here.

Sum() Function
The MySQL sum() function returns the total summed (non-NULL) value of an
expression. It returns NULL if the result set does not have any rows. It works with
numeric data type only.

Suppose we want to calculate the total number of working hours of all employees in
the table, we need to use the sum() function as shown in the following query:

1. mysql> SELECT SUM(working_hours) AS "Total working hours" FROM employe

e;

Output:

After execution, we can see the total working hours of all employees in the table.

To read more information, click here.

AVG() Function
MySQL AVG() function calculates the average of the values specified in the
column. Similar to the SUM() function, it also works with numeric data type only.

Suppose we want to get the average working hours of all employees in the table, we
need to use the AVG() function as shown in the following query:
 1. mysql> SELECT AVG(working_hours) AS "Average working hours" FROM emplo
yee;

Output:

After execution, we can see that the average working hours of all employees in the
organization:

To read more information, click here.

MIN() Function
MySQL MIN() function returns the minimum (lowest) value of the specified
column. It also works with numeric data type only.

Suppose we want to get minimum working hours of an employee available in the

table, we need to use the MIN() function as shown in the following query:

1. mysql> SELECT MIN(working_hours) AS Minimum_working_hours FROM emplo

yee;

Output:

After execution, we can see that the minimum working hours of an employee
available in the table:

To read more information, click here.

MAX() Function
MySQL MAX() function returns the maximum (highest) value of the specified
column. It also works with numeric data type only.

Suppose we want to get maximum working hours of an employee available in the

table, we need to use the MAX() function as shown in the following query:
 1. mysql> SELECT MAX(working_hours) AS Maximum_working_hours FROM empl
oyee;

Output:

After execution, we can see that the maximum working hours of an employee
available in the table:

To read more information, click here.

FIRST() Function
This function returns the first value of the specified column. To get the first value
of the column, we must have to use the LIMIT clause. It is because FIRST() function
only supports in MS Access.

Suppose we want to get the first working date of an employee available in the table,
we need to use the following query:

1. mysql> SELECT working_date FROM employee LIMIT 1;

Output:

After execution, we can see that the first working date of an employee available in the
table:

To read more information, click here.

LAST() Function
This function returns the last value of the specified column. To get the last value of
the column, we must have to use the ORDER BY and LIMIT clause. It is because the
LAST() function only supports in MS Access.
Suppose we want to get the last working hour of an employee available in the table,
we need to use the following query:

1. mysql> SELECT working_hours FROM employee ORDER BY name DESC LIMIT

1;

Output:

After execution, we can see that the last working hour of an employee available in the
table:

To read more information, click here.

GROUP_CONCAT() Function
The GROUP_CONCAT() function returns the concatenated string from multiple
rows into a single string. If the group contains at least one non-null value, it always
returns a string value. Otherwise, we will get a null value.

Suppose we have another employee table as below:

If we want to concatenate the designation of the same dept_id on the employee table,
we need to use the following query:

1. mysql> SELECT emp_id, emp_fname, emp_lname, dept_id,

2. GROUP_CONCAT(designation) as "designation" FROM employee group by emp_id;

Output:

After execution, we can see that the designation of the same dept_id concatenated
successfully:
To read more information, click here.

MySQL Count() Function

MySQL count() function is used to returns the count of an expression. It allows us to
count all rows or only some rows of the table that matches a specified condition. It is a
type of aggregate function whose return type is BIGINT. This function returns 0 if it
does not find any matching rows.

We can use the count function in three forms, which are explained below:

o Count (*)
o Count (expression)
o Count (distinct)

Let us discuss each in detail.

COUNT(*) Function: This function uses the SELECT statement to returns the count of
rows in a result set. The result set contains all Non-Null, Null, and duplicates rows.
COUNT(expression) Function: This function returns the result set without
containing Null rows as the result of an expression.

COUNT(distinct expression) Function: This function returns the count of distinct

rows without containing NULL values as the result of the expression.

Syntax
The following are the syntax of the COUNT() function:

1. SELECT COUNT (aggregate_expression)

2. FROM table_name
3. [WHERE conditions];
Parameter explanation
aggregate_expression: It specifies the column or expression whose NON-NULL
values will be counted.

table_name: It specifies the tables from where you want to retrieve records. There
must be at least one table listed in the FROM clause.

WHERE conditions: It is optional. It specifies the conditions that must be fulfilled for
the records to be selected.

MySQL count() function example

Consider a table named "employees" that contains the following data.
Let us understand how count() functions work in MySQL.

Example1

Execute the following query that uses the COUNT(expression) function to calculates
the total number of employees name available in the table:

1. mysql> SELECT COUNT(emp_name) FROM employees;

Output:

Example2

Execute the following statement that returns all rows from the employee table
and WHERE clause specifies the rows whose value in the column emp_age is greater
than 32:

1. mysql> SELECT COUNT(*) FROM employees WHERE emp_age>32;

Output:

Example3

This statement uses the COUNT(distinct expression) function that counts the Non-Null
and distinct rows in the column emp_age:

1. mysql> SELECT COUNT(DISTINCT emp_age) FROM employees;

Output:
MySQL Count() Function with GROUP BY Clause
We can also use the count() function with the GROUP BY clause that returns the count
of the element in each group. For example, the following statement returns the
number of employee in each city:

1. mysql> SELECT emp_name, city, COUNT(*) FROM employees GROUP BY city;

After the successful execution, we will get the result as below:

MySQL Count() Function with HAVING and ORDER BY Clause

Let us see another clause that uses ORDER BY and Having clause with the count()
function. Execute the following statement that gives the employee name who has at
least two age same and sorts them based on the count result:

1. mysql> SELECT emp_name, emp_age, COUNT(*) FROM employees

2. GROUP BY emp_age
3. HAVING COUNT(*)>=2
4. ORDER BY COUNT(*);

This statement will give the output as below:

MySQL sum() function
The MySQL sum() function is used to return the total summed value of an expression.
It returns NULL if the result set does not have any rows. It is one of the kinds of
aggregate functions in MySQL.

Syntax
Following are the syntax of sum() function in MySQL:

1. SELECT SUM(aggregate_expression)
 2. FROM tables
3. [WHERE conditions];
Parameter Explanation
aggregate_expression: It specifies the column or expression that we are going to
calculate the sum.

table_name: It specifies the tables from where we want to retrieve records. There
must be at least one table listed in the FROM clause.

WHERE conditions: It is optional. It specifies the conditions that must be fulfilled for
the records to be selected.

MySQL sum() function example

Consider our database has a table named employees, having the following data.
Now, we are going to understand this function with various examples:

1. Basic Example

Execute the following query that calculates the total number of working hours of all
employees in the table:

1. mysql> SELECT SUM(working_hours) AS "Total working hours" FROM employe

es;

Output:

We will get the result as below:

2. MySQL sum() function with WHERE clause

This example is used to return the result based on the condition specified in the
WHERE clause. Execute the following query to calculate the total working hours of
employees whose working_hours >= 12.

1. mysql> SELECT SUM(working_hours) AS "Total working hours" FROM employe

es WHERE working_hours>=12;

Output:

This statement will give the output as below:

3. MySQL sum() function with GROUP BY clause

We can also use the SUM() function with the GROUP BY clause to return the total
summed value for each group. For example, this statement calculates the total
working hours of each employee by using the SUM() function with the GROUP BY
clause, as shown in the following query:

1. mysql> SELECT emp_id, emp_name, occupation, SUM(working_hours) AS "Total

working hours" FROM employees GROUP BY occupation;

Output:

Here, we can see that the total working hours of each employee calculates by
grouping them based on their occupation.

4. MySQL sum() function with HAVING clause

The HAVING clause is used to filter the group with the sum() function in MySQL.
Execute the following statement that calculates the working hours of all employees,
grouping them based on their occupation and returns the result whose
Total_working_hours>24.

1. mysql> SELECT emp_id, emp_name, occupation,

2. SUM(working_hours) Total_working_hours
3. FROM employees
4. GROUP BY occupation
5. HAVING SUM(working_hours)>24;

Output:

5. MySQL sum() function with DISTINCT clause

MySQL uses the DISTINCT keyword to remove the duplicate rows from the column
name. This clause can also be used with sum() function to return the total summed
value of a Unique number of records present in the table.

Execute the following query that removes the duplicate records in the working_hours
column of the employee table and then calculates the sum:

1. mysql> SELECT emp_name, occupation,

2. SUM(DISTINCT working_hours) Total_working_hours
3. FROM employees
4. GROUP BY occupation;

Output:
MySQL avg() function
The MySQL avg() is an aggregate function used to return the average value of an
expression in various records.

Syntax
The following are the basic syntax an avg() function in MySQL:

1. SELECT AVG(aggregate_expression)
2. FROM tables
3. [WHERE conditions];
Parameter explanation
aggregate_expression: It specifies the column or expression that we are going to
find the average result.

table_name: It specifies the tables from where we want to retrieve records. There
must be at least one table listed in the FROM clause.

WHERE conditions: It is optional. It specifies the conditions that must be fulfilled for
the records to be selected.

MySQL avg() function example

Consider our database has a table named employees, having the following data.
Now, we are going to understand this function with various examples:
1. Basic Example

Execute the following query that calculates the average working hours of all
employees in the table:

1. mysql> SELECT AVG(working_hours) Avg_working_hours FROM employees;

Output:

We will get the result as below:

2. MySQL AVG() function with WHERE clause

The WHERE clause specifies the conditions that must be fulfilled for the selected
records. Execute the following query to calculate the total average working hours of
employees whose working_hours >= 12.

1. mysql> SELECT AVG(working_hours) Avg_working_hours FROM employees WH

ERE working_hours>=12;

Output:

It will give the following output:

3. MySQL AVG() function with GROUP BY clause

The GROUP BY clause is used to return the result for each group by one or more
columns. For example, this statement calculates the average working hours of each
employee using the AVG() function and then group the result with the GROUP BY
clause:
 1. mysql> SELECT emp_name, occupation, AVG(working_hours) Avg_working_hou
rs FROM employees GROUP BY occupation;

Output:

Here, we can see that the total working hours of each employee calculates by
grouping them based on their occupation.

4. MySQL AVG() function with HAVING clause

The HAVING clause is used to filter the average values of the groups in MySQL.
Execute the following statement that calculates the average working hours of all
employees, grouping them based on their occupation and returns the result
whose Avg_working_hours>9.

1. mysql> SELECT emp_name, occupation,

2. AVG(working_hours) Avg_working_hours
3. FROM employees
4. GROUP BY occupation
5. HAVING AVG(working_hours)>9;

Output:

5. MySQL AVG() function with DISTINCT clause

MySQL uses the DISTINCT keyword to remove the duplicate rows from the column
name. This clause is used with this avg() function to return the average value of a
unique number of records present in the table.

Execute the following query that removes the duplicate records in the working_hours
column of the employee table and then returns the average value:

1. mysql> SELECT emp_name, occupation,

2. AVG(DISTINCT working_hours) Avg_working_hours
3. FROM employees
4. GROUP BY occupation;

Output:

MySQL MIN() Function

The MIN() function in MySQL is used to return the minimum value in a set of values
from the table. It is an aggregate function that is useful when we need to find the
smallest number, selecting the least expensive product, etc.

Syntax
The following is the basic syntax of MIN() function in MySQL:

1. SELECT MIN ( DISTINCT aggregate_expression)

2. FROM table_name(s)
3. [WHERE conditions];
Parameter explanation
This function uses the following parameters:
aggregate_expression: It is the required expression. It specifies the column or
expression name from which the minimum value will be returned.

Table_name(s): It specifies the tables from where we want to retrieve records. There
must be at least one table listed in the FROM clause.

WHERE conditions: It is optional. It specifies the conditions that must be fulfilled for
the records to be selected.

DISTINCT: It allows us to return the minimum of the distinct values in the expression.
However, it does not affect the MIN() function and produces the same result without
using this keyword.

MySQL MIN() Function Example

Let us understand how MIN function works in MySQL with the help of various
examples. Consider our database has a table named "employees" that contains the
following data.

1. Basic Example
Execute the following query that uses the MIN function to find the minimum
income of the employee available in the table:

1. mysql> SELECT MIN(income) AS Minimum_Income FROM employees;

Output

The above query produces the result of minimum values in all rows. After execution,
we will get the output as below:
2. MySQL MIN() Function with WHERE Clause
The WHERE clause allows us to filter the result from the selected records. The
following statement finds the minimum income in all rows from the employee table
and WHERE clause specifies all those rows whose emp_age column is greater than
or equal to 32 and less than or equal to 40.

1. mysql> SELECT MIN(income) AS Minimum_Income

2. FROM employees
3. WHERE emp_age >= 32 AND emp_age <= 40;

Output

The above statement will get the output as below:

3. MySQL MIN() Function with GROUP BY Clause

The GROUP BY clause allows us to collect data from multiple rows and group it based
on one or more columns. For example, the following statement uses the MIN()
function with the GROUP BY clause to find the minimum income in all rows from the
employee table for each emp_age group.

1. mysql> SELECT emp_age, MIN(income) AS Minimum_Income

2. FROM employees
3. GROUP BY emp_age;

Output

After the successful execution, we can see that the income of each employee returns
by grouping them based on their age:
4. MySQL MIN() Function with HAVING Clause
The HAVING clause is always used with the GROUP BY clause to filter the records from
the table. For example, the below statement returns the minimum income of all
employees, grouping them based on their city and returns the result whose
MIN(income)>150000.

1. mysql> SELECT city, MIN(income) AS Minimum_Income

2. FROM employees
3. GROUP BY city
4. HAVING MIN(income) > 150000;

Output

This statement will return the output as below:

5. MySQL MIN() Function with DISTINCT Clause

MySQL uses the DISTINCT keyword to remove the duplicate rows from the column
name. We can also use this clause with MIN() function to return the minimum income
value of a unique number of records present in the table.

Execute the following query that removes the duplicate records in the income column
of the employee table, group by city, and then returns the minimum value:
 1. mysql> SELECT emp_name, city, MIN(DISTINCT income) AS Minimum_Income

2. FROM employees
3. GROUP BY city;

Output

This statement will give the output as below:

MySQL MAX() Function
The MySQL MAX() function is used to return the maximum value in a set of values of
an expression. This aggregate function is useful when we need to find the maximum
number, selecting the most expensive product, or getting the largest payment to the
customer from your table.

Syntax
The following is the basic syntax of MAX() function in MySQL:

1. SELECT MAX(DISTINCT aggregate_expression)

2. FROM table_name(s)
3. [WHERE conditions];
Parameter Explanation
This function uses the following parameters:

aggregate_expression: It is the required expression. It specifies the column,

expression, or formula from which the maximum value will be returned.

table_name(s): It specifies the tables from where we want to retrieve records. There
must be at least one table listed in the FROM clause.

WHERE conditions: It is optional. It specifies the conditions that must be fulfilled for
the records to be selected.

DISTINCT: It allows us to return the maximum of the distinct values in the

expression. However, it does not affect the MAX() function and produces the same
result without using this keyword.

MySQL MAX() Function Example

Let us understand how the MAX function works in MySQL with the help of various
examples. Consider our database has a table named "employees" that contains the
following data.
1. Basic Example
Execute the following query that uses the MAX function to find the maximum
income of the employee available in the table:

1. mysql> SELECT MAX(income) AS "Maximum Income" FROM employees;

Output

The above query produces the result of maximum values in all rows. After execution,
we will get the output as below:

2. MySQL MAX() Function with WHERE Clause

The WHERE clause allows us to filter the result from the selected records. The following
statement finds the maximum income in all rows from the employee table. The
WHERE clause specifies all those rows whose emp_age column is greater than 35.

1. mysql> SELECT MAX(income) AS "Maximum_Income"

2. FROM employees
3. WHERE emp_age > 35;

Output

The above statement will get the output as below:

3. MySQL MAX() Function with GROUP BY Clause
The GROUP BY clause allows us to collect data from multiple rows and group it based on
one or more columns. For example, the following statement uses the MAX() function
with the GROUP BY clause to find the maximum income in all rows from the employee
table for each emp_age group.

1. mysql> SELECT emp_age, MAX(income) AS "Maximum Income"

2. FROM employees
3. GROUP BY emp_age;

Output

After the successful execution, we can see that the maximum income of the employee
returns by grouping them based on their age:

3. MySQL MAX() Function with HAVING Clause

The HAVING clause is always used with the GROUP BY clause to filter the records from
the table. For example, the following statement returns the maximum income among
all employees, grouping them based on their city and returns the result whose
MAX(income) >= 200000.

1. mysql> SELECT city, MAX(income) AS "Maximum Income"

2. FROM employees
3. GROUP BY city
4. HAVING MAX(income) >= 200000;

Output
This statement will return the output as below:

5. MySQL MAX() Function with DISTINCT Clause

MySQL uses the DISTINCT keyword to remove the duplicate rows from the column
name. We can also use this clause with MAX() function to return the maximum income
value of a unique number of records present in the table.

Execute the following query that removes the duplicate records in the employee
table's income column, group by city, and then returns the maximum value:

1. mysql> SELECT city, MAX(DISTINCT income) AS "Maximum Income"

2. FROM employees
3. GROUP BY city;

Output

This statement will give the output as below:

6. MySQL MAX() Function in Subquery Example

Sometimes it is required to use the subquery for returning the maximum value in the
table. In that case, we use the following query:

1. mysql> SELECT * FROM employees WHERE

 2. emp_age = (SELECT MAX(emp_age) FROM employees);

The subquery first finds the maximum age of employees from the table. Then, the
main query (outer query) returns the result of age being equal to the maximum age
returned from the subquery and other information.

Output

MySQL GROUP_CONCAT() Function

The GROUP_CONCAT() function in MySQL is a type of an aggregate function. This
function is used to concatenate string from multiple rows into a single string using
various clauses. If the group contains at least one non-null value, it always returns a
string value. Otherwise, you will get a null value.

The following are the syntax of the GROUP_CONCAT() function:

1. GROUP_CONCAT(
2. DISTINCT expression
3. ORDER BY expression
4. SEPARATOR sep
5. );

OR,

1. mysql> SELECT c1, c2, ....., cN

2. GROUP_CONCAT (
3. [DISTINCT] c_name1
4. [ORDER BY]
5. [SEPARATOR] )
6. FROM table_name GROUP BY c_name2;
In this syntax,

o The c1, c2,….,cN are the table columns.

o The c_name1 is the table column whose values will be concatenated into a single string
for each group.
o The c_name2 is the table column from which grouping is performed.

The options of GROUP_CONCAT() function are explained below:

Distinct: This clause removes the duplicate values in the group before doing
concatenation.

Order By: It allows us to sorts the group data in ascending or descending order and
then perform concatenation. By default, it performs the sorting in the ascending
order. But, you can sort values in descending order using the DESC option explicitly.

Separator: By default, this clause uses comma(,) operator as a separator. If you want
to change the default separator, you can specify the literal value.

NOTE: This function always returns a result in binary or non-binary string value that depends
on the specified arguments. By default, it returns maximum length of string value equal to
1024. If you want to increase this length, you can use the group_concat_max_len system
variable.
GROUP_CONCAT() Example
Let us create a table employee to understand how this function works in MySQL using
different queries.

1. Using a Simple Query

1. mysql> SELECT emp_id, emp_fname, emp_lname, dept_id,

2. GROUP_CONCAT(designation) as "designation" FROM employee group by emp_id;

This statement will give the following output:

2. Using DISTINCT Clause

 1. mysql> SELECT emp_fname, dept_id,
2. GROUP_CONCAT(DISTINCT designation) as "designation" FROM employee group by e
mp_id;

After successful execution of the above statement, we will get the following output:

3. Using Separator Clause

1. mysql>SELECT emp_fname,
2. GROUP_CONCAT(DISTINCT designation SEPARATOR '; ') as "designation" FROM emplo
yee group by emp_id;

Here, the separator clause changes the default returning string comma(,) to a
semicolon(;) and a whitespace character.

The above statement will give the following output:

GROUP_CONCAT() and CONCAT_WS()

Now, you are aware of the working of the GROUP_CONCAT() function. Sometimes, we
can use this function with the CONCAT_WS() function that gives the more useful
result. The following statement explains it more clearly:

1. mysql>SELECT GROUP_CONCAT(CONCAT_WS(', ', emp_lname, emp_fname) SEP

ARATOR ';') as employeename FROM employee;

In this statement, the CONCAT_WS() function first concatenates the first name and
last name of each employee and results in the full name of the employees. Next, we
use the GROUP_CONCAT() function with a semicolon (;) separator clause to make the
list of all employees in a single row. Finally, execute the statement. After successful
execution, we will get the following output:

This function returns the result in a single row, not a list of values. Therefore, we
cannot work GROUP_CONCAT() function with the IN operator. If we use an IN operator
with this function, then the query will not work because the IN operator accepts a list
of values, not a string.
MySQL first function
The MySQL first function is used to return the first value of the selected column. Here,
we use limit clause to select first record or more.

Syntax:

1. SELECT column_name
2. FROM table_name
3. LIMIT 1;

MySQL first function example

To SELECT FIRST element:

Consider a table named "officers", having the following data.

Execute the following query:

1. SELECT officer_name
2. FROM officers
3. LIMIT 1;

Output:
To SELECT FIRST two records

1. SELECT officer_name
2. FROM officers
3. LIMIT 2;

Output:

MySQL last function

MySQL last function is used to return the last value of the selected column.

Syntax:
 1. SELECT column_name
2. FROM table_name
3. ORDER BY column_name DESC
4. LIMIT 1;

MySQL last function example

Consider a table "officers" having the following data.

Execute the following query:

1. SELECT officer_name
2. FROM officers
3. ORDER BY officer_id DESC
4. LIMIT 1;

This query will return the last officer_name ordering by officer_id.

Output:
Return the last officer_name ordering by officer_name:

1. SELECT officer_name
2. FROM officers
3. ORDER BY officer_name DESC
4. LIMIT 1;

Output:
Literals: Date and Time
In MySQL programming, Date and Time literals are in the form of strings or numbers.

Following are some more formats in which date and time can be displayed.

EXAMPLE FORMAT

'2018-10-18' 'YYYY-MM-DD'

'20181018' 'YYYYMMDD'

20181018 YYYYMMDD

'18-10-18' 'YY-MM-DD'

181018 YYMMDD

'2018-10-18 5:25:20' 'YYYY-MM-DD HH:MM:SS'

'2018101852520' 'YYYYMMDDHHMMSS'

2018101852520 YYYYMMDDHHMMSS

'18-10-18 5:25:20' 'YY-MM-DD HH:MM:SS'

'18101852520' 'YYMMDDHHMMSS'

18101852520 YYMMDDHHMMSS

Example 1

1. select id, name, salary, date_format(sal_date,'%d-%m-%y') as new_date_forma

te from staff1;

Example 2

1. select id, name, salary, date_format(sal_date,'%d%m%y') as new_date_format

e from staff1;
MySQL vs Oracle
In mysql you may create any number of databases but in oracle at a time only
one database will be there on the server.
Hence create database command is not work in oracle.
In oracle default date fomat is
dd-MMM-yy
in mysql the date format is ‘yyyy-mm-dd’