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

What is VIEW?

• ·         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. 
• 
  
  Syntax:
  CREATE VIEW view_name AS
  SELECT column_name(s)
  FROM table_name
  WHERE condition
  
  
  Example:
  
  
  
  
  
  A view is a database object than can be queried. 
  
  
  
  A view is deleted with the DROP VIEW syntax.
  
  
  

  
  

  
  
  Finding Views
  
  
  
  
  
  mysql> SELECT TABLE_NAME,TABLE_TYPE FROM information_schema.TABLES;
  
  Creating a view with a UNION