ADVANCE SQL

OPERATORS-
LOGICAL, ARITHMETIC, COMPARISON and BIT OPERATORS

Operators are the building blocks of complex queries

• Logical Operator
• Logical operators are AND and OR. This operators allow you to relate numbers  of conditions in various ways. Either true or false

• Arithmetic Operators
• It is used to perform basic math operations (+,-,/,*,%)

• Comparison Operators
• The comparison operators where used when making comparisons between values
• These are =, != or < >, > , < , >= , <=>, IS NULL, IS NOT NULL, BETWEEN, REGEXP, RLIKE, NOT REDEXP, NOT LIKE.

PERFORMING TRANSACTION with BEGIN and COMMIT

BEGIN TRANSACTION

• If an error encountered, and the modification is made after the begin transaction can set to rolled back to return the data to know state of consistency 

SYNTAX:

Begin;

COMMIT TRANSACTION

• This will instruct that the modifications made where permanent part of database

NOTE: ROLLBACK means restoring original value present before update query

RUNNING MYSQL IN BATCH FILE 

Running MYSQL queries without entering the mysql client itself.
Storing mysql queries into a text file and then run the text file through mysql

SYNTAX:

mysql -u username -p database <  '/path/to/filename.txt'

VIEW

A specific look on a data or more tables
It contains rows and columns just like table

SYNTAX:

CREATE VIEW            view_name
AS  SELECT                   column_name(s)
FROM                            table_name
WHERE                         condition

LOCK TABLES

Lock tables enables you to prevent other sessions from modifying tables during periods when a session required exclusive access to them. Lock may be used to emulate transactions or to get more speed when updating.

NOTE: Table locks protects only against inappropriate read or write. A session with another purposes like drop table can be acquired. 

SYNTAX: 

           LOCK TABLE      [tablename]      READ;

UNLOCK TABLES

Release any table locks held by the current session.

SYNTAX:
         UNLOCK TABLES;