MySQL Commands

What is MySQL?

MySQL is an open-source database application and it is the most popular RDBMS (Relational Database Management System) tool which is used to store data of the web-based applications.

The basic knowledge of the RDBMS concept and SQL is enough to understand MySQL.

Features of MySQL

• It is an open-source application.
• It is fast, flexible, reliable, and easy to use.
• It supports standard SQL.
• It is platform-independent.
• It provides transactional and non-transactional storage engines.
• It supports an in-memory heap table.
• It can handle large databases.
• MySQL server works in the server, client, or embedded systems.

MySQL Basic Commands

Create a Database in MySQL:

Create a database statement that is used to create a new database.

Syntax - CREATE DATABASE [database name];

Example - 

CREATE DATABASE Tutorials;

on MySQL terminal:

CREATE TABLE in MySQL:

Create table statement that is used to create a table inside a database.

Syntax - CREATE TABLE tablename (ColumnName ColumnType);

Example - 

CREATE TABLE Orders (id int, Name varchar(50), amount double, primary key(id));

on MySQL terminal:

INSERT INTO MySQL Table:

Insert Into statement is used to insert new records into a table.

Syntax - INSERT INTO tablename (column1, column2, column3, column4, ...)
                      VALUES (value1, value2, value3, value4, ...);

Example -

INSERT INTO Orders (id,Name,amount) VALUES (1,'Amit',10000);

on MySQL terminal:

SELECT Statment in MySQL:

Select Statement is used to retrieve the records from the table.

Syntax - SELECT column1, column2,column3, ...
                      FROM tablename;

                      To select all the fields from the table-

                      SELECT * FROM tablename;

Example-

SELECT id,amount from Orders;

                        To select all the fields from the table-

                        SELECT * from Orders;

on MySQL terminal:

WHERE CLAUSE - Where clause is used to filter out the records from the table

Syntax - SELECT *
                       FROM tablename
                       WHERE condition;

Example- 

SELECT * from Orders WHERE amount>10000;

on MySQL terminal:

UPDATE - Update statement is used to modify the records in the table.

Syntax- UPDATE tablename
                     SET column1 = value1
                     WHERE condition;

Example-

UPDATE Orders SET amount=17000 where id=2;

on MySQL terminal:

ORDER BY- Order by is used to sort the result in ascending or descending order. By default Order by sorts in ascending order. To sort the result in descending order we have to use the DESC keyword.

Syntax- SELECT column1, column2, ...
                     FROM tablename
                     ORDER BY column1, column2, ... ASC|DESC;

Example-

SELECT * from Orders ORDER BY amount DESC;

on MySQL terminal:

LIKE in MySQL:

Like Operator is used for pattern matching in a column.

There are two types of the LIKE operator-

1. % - The percent signs represent any number of characters which can be zero, one, or multiple characters.
2. _ - The underscore represents exactly one character.

Syntax- SELECT *
                     FROM table_name
                     WHERE column LIKE pattern;

Example- 

Select * from Orders where Name like '%i%'; 

on MySQL terminal: