MYSQL Logical Operators

MySQL Logical Operators :

    MySQL supports the following logical operations :

• AND(&&) Operator
• OR(||) Operator
• NOT(!) Operator

    Lets we see the Logical Operators in MySQL.

MySQL AND(&&) Operator :

    The logical AND(&&) operator indicates whether the both operands are true. Lets see a statement using AND operator.

mysql> select studid, name from student where marks > 80 
and marks < 100;
                            (or)
mysql> select studid, name from student where marks > 80 
&& marks < 100;
+--------+-------+
| studid | name  |
+--------+-------+
|      4 | jack  |
|      8 | mille |
+--------+-------+
2 rows in set (0.00 sec)

    In the above example it will list the studid and name of the student who have secured more than 80 and less than 100.

MySQL OR(||) Operator :

    The logical OR(||) operator indicates whether either operand is true. Lets see a statement using OR operator.

mysql> select name, marks, address from student where 
name like 'a%' or name like 's%';
    (or)
mysql> select name, marks, address from student where 
name like 'a%' || name like 's%';
+-------+-------+------------------+
| name  | marks | address          |
+-------+-------+------------------+
| steve |   100 | 5th cross street |
| anne  |   100 | downing street   |
| steve |    75 | downing street   |
| anne  |    80 | edinburgh        |
+-------+-------+------------------+
4 rows in set (0.00 sec)

    In the above statement it will list the name, marks and address of the student whose name starts with the letter A and S.

MySQL NOT(!) Operator :

    The logical NOT(!) operator have only one operand and it returns the inverse of the value.

mysql> select * from student where not (studid=1);
    (or)
mysql> select * from student where ! (studid=1);
+--------+-------+-------+-----------------+---------+
| studid | name  | marks | address         | phone   |
+--------+-------+-------+-----------------+---------+
|      2 | david |   100 | welling street  |  547896 |
|      4 | jack  |    82 | welling street  | 2436821 |
|      5 | anne  |   100 | downing street  | 2634821 |
|      6 | steve |    75 | downing street  | 2874698 |
|      7 | anne  |    80 | edinburgh       | 2569843 |
|      8 | mille |    98 | victoria street | 1236547 |
+--------+-------+-------+-----------------+---------+
6 rows in set (0.00 sec)

MYQL Comparison 

EQUAL(==) 

LESS THAN(<) 

LESS THAN OR EQUAL(<=) 

GREATER THAN(>) 

GREATER THAN OR EQUAL(>=) 

NOT EQUAL(<>,!=) 

Triggers and Stored Procedures

What is trigger?

SQL trigger is an SQL statements or a set of SQL statements which is stored to be activated or fired when an event associating with a database table occurs. The event can be any event including INSERT, UPDATE  and DELETE.

Advantages of MySQL triggers:

• SQL Trigger provides an alternative way to check integrity.
• SQL trigger can catch the errors in business logic in the database level.
• SQL trigger provides an alternative way to run scheduled tasks. With SQL trigger, you don’t have to wait to run the scheduled tasks. You can handle those tasks before or after changes being made to database tables.
• SQL trigger is very useful when you use it to audit the changes of data in a database table.

Disadvantages of MySQL triggers:

• SQL trigger only can provide extended validation and cannot replace all the validations. Some simple validations can be done in the application level.  For example, you can validate input check in the client side by using javascript or in the server side by server script using PHP or ASP.NET.
• SQL Triggers executes invisibly from client-application which connects to the database server so it is difficult to figure out what happen underlying database layer.
• SQL Triggers run every updates made to the table therefore it adds workload to the database and cause system runs slower.

Also, the triggers has few restrictions:

• It is not allowed to call a stored procedure in a trigger.
• It is not allowed to create a trigger for views or temporary table.
• It is not allowed to use transaction in a trigger.
• Return statement is disallowed in a trigger.
• All trigger for a database table must have unique name. It is allowed that triggers for different tables having the same name but it is recommended that trigger should have unique name in a specific database. 
• modify a trigger, you have to delete it first and recreate it. MySQL doesn't provide you SQL statement to alter an existing trigger like altering other database objects such as tables or stored procedures.
    

 HOW TO CREATE TRIGGERS
Use the following syntax to create a trigger:

CREATE TRIGGER trigger_name trigger_time trigger_event
ON table_name
FOR EACH ROW
BEGIN
...
END

Parameters required: 

 trigger_name: can be anything/user defined

 trigger_time: BEFORE or AFTER

 trigger_event: INSERT/ DELETE/ UPDATE 

MANAGING TRIGGERS

To view all triggers within a specific database, use the following syntax:

SELECT * FROM Information_Schema.Trigger
WHERE Trigger_schema = 'database_name';

To delete a trigger:

DROP TRIGGER table_name.trigger_name 

STORED PROCEDURES

What are stored procedures? 

Stored procedures are declarative SQL statements stored in a database catalog,

much like a user defined function in C++, only it is stored in a specific database.

HOW TO CREATE PROCEDURES:

CREATE PROCEDURE PROCEDURE_NAME()
  BEGIN...
  
  END

To be able to use the stored procedure, user can call it using the syntax:

CALL Procedure_name();