Advance SQL

Logical, arithmetic, comparison, and bit operators

Operators

ž  Operators are the building blocks of complex queries.

1. Logical  operators
2. Arithmetic operators
3. Comparison operators
4. 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).

Logical Operators

Example:

Arithmetic Operators

•       Arithmetic operators are used to perform basic mathematical operations

Example:

Comparison Operators

•  Comparison operators are used when making comparisons between values. 

Example:

Bit Functions

 Bitwise Functions

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

Example:

ž  Bitwise AND

Example:

ž  Bitwise OR

Performing transactions with BEGIN and COMMIT

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 

CREATE TABLE t (f INT) TYPE=InnoDB;

BEGIN;

INSERT INTO t(f) VALUES (1);

SELECT * FROM t;

ROLLBACK;

SELECT * FROM t; 

CREATE TABLE t (f INT) TYPE=InnoDB;

BEGIN;

INSERT INTO t(f) VALUES (1);

SELECT * FROM t;

COMMIT;

ROLLBACK;

SELECT * FROM t;

Table locks

Unlock Tables

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.

Command:
mysql -u username -p databasename < '/path/to/filename.txt‘
Example:
mysql –u root sampledb <c:\query.txt

If you want to save the result of a batch file as its own 

text file, add > '/path/to/output.txt to your code.

The line bin/mysql -u username -p 

< '/path/to/input.txt' > '/path/to/output.txt'

Example:

Batch files can also be run from within the mysql client, if you'd prefer. 

Just log in, select your database, and then type
source/path/to/filename