Advance SQL

Logical, arithmetic, comparison, and bit operators

Operators are the building blocks of complex queries.

• Logical  operators
• Arithmetic operators
• Comparison operators
• Bit operators

Logical Operators
Logical operators (such as AND and OR) allow you to relate numbers of conditions in various ways.
Reduce to either true (1) or false (0).

Arithmetic Operators
Arithmetic operators are used to perform basic mathematical operations.

Comparison Operators
Comparison operators are used when making comparisons between values.

Regular Expressions (REGEXP, RLIKE)

Bit Operators
All the operands should be numerals, but they are internally represented as binary.

Performing transactions with BEGIN and COMMIT

A transaction is a sequential group of database manipulation operations, which is performed as if it were one single work unit. In other words, a transaction will never be complete unless each individual operation within the group is successful. If any operation within the transaction fails, the entire transaction will fail.

These statements provide control over use of transactions:

START TRANSACTION or BEGIN
›start a new transaction.

COMMIT
›commits the current transaction, making its changes permanent

ROLLBACK
›rolls back the current transaction, canceling its changes


SET autocommit
›disables or enables the default autocommit mode for the current session 

Table Locks

MySQL enables client sessions to acquire table locks explicitly for the purpose of cooperating with other sessions for access to tables, or to prevent other sessions from modifying tables during periods when a session requires exclusive access to them. A session can acquire or release locks only for itself. One session cannot acquire locks for another session or release locks held by another session.

Running Mysql in Batch File

The MySQL software also allows you to run queries without entering the mysql client itself. To do so is to use mysql in what is called batch mode.

Instead of entering queries directly, you can store them in a text file and then run the text file through mysql.

Views

A view is a specific look on data from one or more tables.

It is a dynamic, virtual table computed or collated from data in the database.

A view is a pseudo table. It is a stored query which looks like a table. And it can be referenced like a table.

It can arrange data in some specific order, highlight or hide some data.


The fields in a view are fields from one or more real tables in the database.

A view contains rows and columns, just like a real table.


EXAMPLE:

References: www.mysql.com/ and www.tutorialspoint.com/