Structured Query Language (SQL) is used to manipulate, retrieve, insert, update, and delete data in relational database management system (RDBMS). To make it simpler, SQL tells the database what to do and exactly what it needs. SQL is a standard language that all RDBMS systems such as MySQL, MS Access, MS SQL, Oracle, Postgres, and others use.

Using SQL, the user can access, define and manipulate the data from MySQL. We can leverage SQL modules, libraries, and precompilers to embed with the other languages which help in creating/dropping databases and tables, manage view, stored procedures, function etc. in a database. Using SQL, we can also manage permissions on tables, procedures and different views.

The following are a few important SQL commands that we explain in detail later in the chapter:

• SELECT: Extracts data from a database
• UPDATE: Updates data from a...

Let's have a look at different storage engine of MySQL database. This is an important section to understand before we jump into data query techniques, as storage engines play an important role in data query techniques. MySQL stores data in the database as a subdirectory. In each database, data is stored as tables and each table definition information is being stored in a file with extension as .frm with the same name as the table name. Suppose if we create a new table as admin_user then it will store all table definition related information in admin_user.frm file.

We can see information related to a table with the use of SHOW TABLE STATUS command. Let's try to execute this command for admin_user table and pull the information.

mysql> SHOW TABLE STATUS LIKE 'admin_user' \G;
*************************** 1. row ************...

The Select statement is used to retrieve data from single or multiple tables:

SELECT field 1, field 2, field 3 from table_name [WHERE Clause] [GROUP BY {col_name }] [HAVING where_condition] [ORDER BY {col_name}  {ASC | DESC}, ...] [LIMIT{OFFSET M}{LIMIT N}]

This is the common syntax used to retrieve data from a single table:

• Fields one and two are the column names of the table. To fetch all columns from the table, the * expression can be used.
• table_name indicates the table name from where data needs to be retrieved.
• The WHERE clause can be used to specify any condition in a single and multiple column.
• The Group BY function is used with aggregate functions to group the result sets.
• The HAVING clause is needed after GROUP BY to filter based on conditions for a group of rows or aggregates. If we use the HAVING clause without GROUP BY, it would act similarly...

Insert, replace, and update statements are used to insert or modify data in the database. Let's take a look at them one by one.

INSERT inserts new rows into an existing table; the following is the syntax to add new rows to a table:

INSERT [INTO] tbl_name [(field_name1, field_name2...)] {VALUES | VALUE} ({value of filed 1},value of filed 2,...),(...),... 

The following inserts new users into the user table:

Insert INTO user (first_name,last_name,email,password) VALUES 
("Robert","Dotson","RuthFDotson@teleworm.us","17500917b5cd7cefdb57e87ce73485d3"),
("Ruth","Uselton","DarronAUselton@armyspy.com"...

Transaction is a logical unit of one or more Insert, Update, and Delete statements. Transaction is useful when you want to do multiple operations on the database. Either all the changes that are successful would be committed, unsuccessful ones would be undone, or errors generated during execution would perform transactions rolled back. The InnoDB storage engine supports the rollback of transactions. The goal of the InnoDB storage model is to combine the best properties of a multi-versioning database with two-phase locking. InnoDB performs locking at the row level and runs queries as non-locking for consistent reads by default for better performance and integrity of data.

Let's take an example to understand where transaction is useful. Consider a banking database. Suppose a bank customer want to transfers money from his account to another account. Generally...