A transaction is a logical unit of work that contains one or more SQL statements. Transactions are atomic units of work that can be committed or rolled back. When a transaction makes multiple changes to the database, either all the changes succeed when the transaction is committed, or all the changes are undone when the transaction is rolled back.
A transaction begins with the first executable SQL statement. A transaction ends when it is committed or rolled back, either explicitly with a COMMIT or ROLLBACK statement or implicitly when a DDL (Data Definition Language (DDL) is used to manage table and index structure and CREATE, ALTER, RENAME, DROP and TRUNCATE statements are to name a few data definition elements) statement is issued.
To understand the concept of a transaction, consider a banking database. Suppose a bank customer transfers money from his savings account (SB a/c) to his current account (CA a/c), the statement will be divided into four blocks :
Debit SB a/c.
Credit CA a/c.
Record in Transaction Journal
End Transaction
The SQL statement to debit SB a/c is as follows:
UPDATE sb_accounts SET balance = balance – 1000 WHERE account_no = 932656 ;
The SQL statement to credit OD a/c is as follows:
UPDATE ca_accounts SET balance = balance + 1000 WHERE account_no = 933456 ;
The SQL statement for record in transaction journal is as follows:
INSERT INTO journal VALUES (100896, ‘Tansaction on Benjamin Hampshair a/c’, ’26-AUG-08′ 932656, 933456, 1000);
The SQL statement for End Transaction is as follows :
COMMIT WORK;
MySQL and the ACID Model
ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably. In MySQL, InnoDB storage engine supports ACID-compliant features. The following sections discuss how MySQL features, in particular, the InnoDB storage engine, interact with the categories of the ACID model:
Atomicity: The atomicity aspect of the ACID model mainly involves InnoDB transactions. Related MySQL features include :
Autocommit setting.
COMMIT statement.
ROLLBACK statement.
Operational data from the INFORMATION_SCHEMA tables.
Consistency: The consistency aspect of the ACID model mainly involves internal InnoDB processing to protect data from crashes. Related MySQL features include :
InnoDB doublewrite buffer.
InnoDB crash recovery.
Isolation: The isolation aspect of the ACID model mainly involves InnoDB transactions, in particular, the isolation level that applies to each transaction. Related MySQL features include :
Autocommit setting.
SET ISOLATION LEVEL statement.
The low-level details of InnoDB locking. During performance tuning, you see these details through INFORMATION_SCHEMA tables.
Durability: The durability aspect of the ACID model involves MySQL software features interacting with your particular hardware configuration. Because of the many possibilities depending on the capabilities of your CPU, network, and storage devices, this aspect is the most complicated to provide concrete guidelines for. Related MySQL features include:
InnoDB doublewrite buffer turned on and off by the innodb_doublewrite configuration option.
Write buffer in a storage device, such as a disk drive, SSD, or RAID array.
Battery-backed cache in a storage device.
The operating system used to run MySQL, in particular, its support for the fsync() system call.
Uninterruptible power supply (UPS) protecting the electrical power to all computer servers and storage devices that run MySQL servers and store MySQL data.
Your backup strategy, such as frequency and types of backups, and backup retention periods.
For distributed or hosted data applications, the particular characteristics of the data centers where the hardware for the MySQL servers is located, and network connections between the data centers.
MySQL Transaction
MySQL (here we maintain version 5.6) supports local transactions (within a given client session) through statements such as SET autocommit, START TRANSACTION, COMMIT, and ROLLBACK. Here is the syntax of START TRANSACTION, COMMIT, and ROLLBACK:START TRANSACTION transaction_characteristic [, transaction_characteristic] …] transaction_characteristic: WITH CONSISTENT SNAPSHOT | READ WRITE | READ ONLY BEGIN [WORK] COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] SET autocommit = {0 | 1}
These statements provide control over use of transactions :
The START TRANSACTION or BEGIN statement begins a new transaction.
COMMIT commits the current transaction, making its changes permanent.
ROLLBACK rolls back the current transaction, canceling its changes.
The SETautocommit statement disables or enables the default autocommit mode for the current session.
By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk to make it permanent. The change cannot be rolled back.
Currently (by default), MySQL runs with autocommit mode enabled.mysql> select * from student_mast; +————+——————+———-+ | STUDENT_ID | NAME | ST_CLASS | +————+——————+———-+ | 2 | Neena Kochhar | 9 | | 3 | Lex De Haan | 9 | | 4 | Alexander Hunold | 11 | +————+——————+———-+ 3 rows in set (0.09 sec)
Let execute an update command:mysql>mysql> UPDATE STUDENT_MAST SET ST_CLASS=8 WHERE STUDENT_ID=2; Query OK, 1 row affected (0.08 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql>mysql> select * from student_mast; +————+——————+———-+ | STUDENT_ID | NAME | ST_CLASS | +————+——————+———-+ | 2 | Neena Kochhar | 8 | | 3 | Lex De Haan | 9 | | 4 | Alexander Hunold | 11 | +————+——————+———-+ 3 rows in set (0.00 sec)
Now execute the ROLLBACK command to return in the previous stage :mysql>mysql> ROLLBACK; Query OK, 0 rows affected (0.03 sec)
mysql>mysql> select * from student_mast; +————+——————+———-+ | STUDENT_ID | NAME | ST_CLASS | +————+——————+———-+ | 2 | Neena Kochhar | 8 | | 3 | Lex De Haan | 9 | | 4 | Alexander Hunold | 11 | +————+——————+———-+ 3 rows in set (0.00 sec)
There is no roll back as MySQL runs with autocommit mode enabled.
To disable autocommit mode, use the START TRANSACTION statement. See the following example :mysql>mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE STUDENT_MAST SET ST_CLASS=10 WHERE STUDENT_ID=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from student_mast; +————+——————+———-+ | STUDENT_ID | NAME | ST_CLASS | +————+——————+———-+ | 2 | Neena Kochhar | 10 | | 3 | Lex De Haan | 9 | | 4 | Alexander Hunold | 11 | +————+——————+———-+ 3 rows in set (0.00 sec) mysql> ROLLBACK; Query OK, 0 rows affected (0.07 sec) mysql> select * from student_mast; +————+——————+———-+ | STUDENT_ID | NAME | ST_CLASS | +————+——————+———-+ | 2 | Neena Kochhar | 8 | | 3 | Lex De Haan | 9 | | 4 | Alexander Hunold | 11 | +————+——————+———-+ 3 rows in set (0.00 sec)
MySQL statements that cannot be Rolled Back and statements that cause an implicit Commit
In MySQL, some statements cannot be rolled back. DDL statements such as CREATE or DROP databases, CREATE, ALTER or DROP tables or stored routines. You should design a transaction without these statements.
The statements listed in this section (and any synonyms for them) implicitly end any transaction active in the current session, as if you had done a COMMIT before executing the statement.
Data definition language (DDL) statements that define or modify database objects. ALTER DATABASE … UPGRADE DATA DIRECTORY NAME, ALTER EVENT, ALTER PROCEDURE, ALTER SERVER, ALTER TABLE, ALTER VIEW, CREATE DATABASE, CREATE EVENT, CREATE INDEX, CREATE PROCEDURE, CREATE SERVER, CREATE TABLE, CREATE TRIGGER, CREATE VIEW, DROP DATABASE, DROP EVENT, DROP INDEX, DROP PROCEDURE, DROP SERVER, DROP TABLE, DROP TRIGGER, DROP VIEW, RENAME TABLE, TRUNCATE TABLE.
ALTER FUNCTION, CREATE FUNCTION, and DROP FUNCTION also cause an implicit commit when used with stored functions, but not with UDFs. (ALTER FUNCTION can only be used with stored functions.)
ALTER TABLE, CREATE TABLE, and DROP TABLE do not commit a transaction if the TEMPORARY keyword is used.
Statements that implicitly use or modify tables in the MySQL database. CREATE USER, DROP USER, GRANT, RENAME USER, REVOKE, SET PASSWORD.
Transaction-control and locking statements. BEGIN, LOCK TABLES, SET autocommit = 1 (if the value is not already 1), START TRANSACTION, UNLOCK TABLES.
Data loading statements. LOAD DATA INFILE. LOAD DATA INFILE causes an implicit commit only for tables using the NDB storage engine.
Administrative statements. ANALYZE TABLE, CACHE INDEX, CHECK TABLE, LOAD INDEX INTO CACHE, OPTIMIZE TABLE, REPAIR TABLE.
Replication control statements. Beginning with MySQL 5.6.7: START SLAVE, STOP SLAVE, RESET SLAVE, CHANGE MASTER TO.
SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT
InnoDB supports the SQL statements SAVEPOINT, ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT and the optional WORK keyword for ROLLBACK.
The SAVEPOINT statement sets a named transaction savepoint with a name of the identifier. If the current transaction has a savepoint with the same name, the old savepoint is deleted and a new one is set.
The ROLLBACK TO SAVEPOINT statement rolls back a transaction to the named savepoint without terminating the transaction. Modifications that the current transaction made to rows after the savepoint was set are undone in the rollback, but InnoDB does not release the row locks that were stored in memory after the savepoint.
Here is the syntax:SAVEPOINT identifier ROLLBACK [WORK] TO [SAVEPOINT] identifier RELEASE SAVEPOINT identifier
LOCK and UNLOCK Tables
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.
LOCK TABLES explicitly acquires table locks for the current client session. Table locks can be acquired for base tables or views. You must have the LOCK TABLES privilege, and the SELECT privilege for each object to be locked.
UNLOCK TABLES explicitly releases any table locks held by the current session. LOCK TABLES implicitly releases any table locks held by the current session before acquiring new locks.
Here is the syntax:LOCK TABLES tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type] … lock_type: READ [LOCAL] | [LOW_PRIORITY] WRITE UNLOCK TABLES
A trigger is a set of actions that are run automatically when a specified change operation (SQL INSERT, UPDATE, or DELETE statement) is performed on a specified table. Triggers are useful for tasks such as enforcing business rules, validating input data, and keeping an audit trail.
Contents:
Uses for triggers
Benefits of using triggers in business
MySQL Triggers
Create MySQL triggers
Sample database, table, table structure, table records
Tool to create MySQL Triggers
MySQL Trigger : Example AFTER INSERT
MySQL Trigger : Example BEFORE INSERT
MySQL Trigger : Example AFTER UPDATE
MySQL Trigger : Example BEFORE UPDATE
MySQL Trigger : Example AFTER DELETE
How MySQL handle errors during trigger execution?
Delete a MySQL trigger
Uses for triggers:
Enforce business rules
Validate input data
Generate a unique value for a newly-inserted row in a different file.
Write to other files for audit trail purposes
Query from other files for cross-referencing purposes
Access system functions
Replicate data to different files to achieve data consistency
Benefits of using triggers in business:
Faster application development. Because the database stores triggers, you do not have to code the trigger actions into each database application.
Global enforcement of business rules. Define a trigger once and then reuse it for any application that uses the database.
Easier maintenance. If a business policy changes, you need to change only the corresponding trigger program instead of each application program.
Improve performance in client/server environment. All rules run on the server before the result returns.
Implementation of SQL triggers is based on the SQL standard. It supports constructs that are common to most programming languages. It supports the declaration of local variables, statements to control the flow of the procedure, assignment of expression results to variables, and error handling.
MySQL Triggers
We assume that you are habituated with “MySQL Stored Procedures”, if not you can read our MySQL Procedures tutorial. You can use the following statements of MySQL procedure in triggers:
Compound statements (BEGIN / END)
Variable declaration (DECLARE) and assignment (SET)
A trigger is a named database object that is associated with a table, and it activates when a particular event (e.g. an insert, update or delete) occurs for the table. The statement CREATE TRIGGER creates a new trigger in MySQL. Here is the syntax :
Syntax:CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_body trigger_time: { BEFORE | AFTER } trigger_event: { INSERT | UPDATE | DELETE }
Explanation:
DEFINER clause: The DEFINER clause specifies the MySQL account to be used when checking access privileges at trigger activation time. If a user value is given, it should be a MySQL account specified as ‘user_name’@’host_name’ (the same format used in the GRANT statement), CURRENT_USER, or CURRENT_USER(). The default DEFINER value is the user who executes the CREATE TRIGGER statement. This is the same as specifying DEFINER = CURRENT_USER explicitly.If you specify the DEFINER clause, these rules determine the valid DEFINER user values:
If you do not have the SUPER privilege, the only permitted user value is your own account, either specified literally or by using CURRENT_USER. You cannot set the definer to some other account.
If you have the SUPER privilege, you can specify any syntactically valid account name. If the account does not actually exist, a warning is generated.
Although it is possible to create a trigger with a nonexistent DEFINER account, it is not a good idea for such triggers to be activated until the account actually does exist. Otherwise, the behavior with respect to privilege checking is undefined.
trigger_name: All triggers must have unique names within a schema. Triggers in different schemas can have the same name.
trigger_time: trigger_time is the trigger action time. It can be BEFORE or AFTER to indicate that the trigger activates before or after each row to be modified.
trigger_event: trigger_event indicates the kind of operation that activates the trigger. These trigger_event values are permitted:
The trigger activates whenever a new row is inserted into the table; for example, through INSERT, LOAD DATA, and REPLACE statements.
The trigger activates whenever a row is modified; for example, through UPDATE statements.
The trigger activates whenever a row is deleted from the table; for example, through DELETE and REPLACE statements. DROP TABLE and TRUNCATE TABLE statements on the table do not activate this trigger, because they do not use DELETE. Dropping a partition does not activate DELETE triggers, either.
tbl_name : The trigger becomes associated with the table named tbl_name, which must refer to a permanent table. You cannot associate a trigger with a TEMPORARY table or a view.
trigger_body: trigger_body is the statement to execute when the trigger activates. To execute multiple statements, use the BEGIN … END compound statement construct. This also enables you to use the same statements that are permissible within stored routines.
Here is a simple example:mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account -> FOR EACH ROW SET @sum = @sum + NEW.amount; Query OK, 0 rows affected (0.06 sec)
In the above example, there is new keyword ‘NEW‘ which is a MySQL extension to triggers. There is two MySQL extension to triggers ‘OLD‘ and ‘NEW‘. OLD and NEW are not case sensitive.
Within the trigger body, the OLD and NEW keywords enable you to access columns in the rows affected by a trigger
In an INSERT trigger, only NEW.col_name can be used.
In a UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before it is updated and NEW.col_name to refer to the columns of the row after it is updated.
In a DELETE trigger, only OLD.col_name can be used; there is no new row.
A column named with OLD is read only. You can refer to it (if you have the SELECT privilege), but not modify it. You can refer to a column named with NEW if you have the SELECT privilege for it. In a BEFORE trigger, you can also change its value with SET NEW.col_name = value if you have the UPDATE privilege for it. This means you can use a trigger to modify the values to be inserted into a new row or used to update a row. (Such a SET statement has no effect in an AFTER trigger because the row change will have already occurred.)
Sample database, table, table structure, table records for various examples
Database Name: hr Host Name : localhost Database user : root Password : ‘ ‘
Structure of the table : emp_details
Records of the table (on some fields): emp_detailsmysql> SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY, COMMISSION_PCT FROM emp_details; +————-+————+———–+———+———-+—————-+ | EMPLOYEE_ID | FIRST_NAME | LAST_NAME | JOB_ID | SALARY | COMMISSION_PCT | +————-+————+———–+———+———-+—————-+ | 100 | Steven | King | AD_PRES | 24000.00 | 0.10 | | 101 | Neena | Kochhar | AD_VP | 17000.00 | 0.50 | | 102 | Lex | De Haan | AD_VP | 17000.00 | 0.50 | | 103 | Alexander | Hunold | IT_PROG | 9000.00 | 0.25 | | 104 | Bruce | Ernst | IT_PROG | 6000.00 | 0.25 | | 105 | David | Austin | IT_PROG | 4800.00 | 0.25 | +————-+————+———–+———+———-+—————-+ 6 rows in set (0.00 sec)
Tool to create MySQL Triggers
You can write a procedure in MySQL command line tool or you can use MySQL workbench which is an excellent front-end tool (here we have used version 5.3 CE).
MySQL command line tool: –
Select MySQL command Client from Start menu:
Selecting MySQL command prompt following screen will come:
After a successful login, you can access the MySQL command prompt:
Now you can write your own trigger on a specific table, see the following example :
MySQL workbench (5.3 CE): –
Select MySQL workbench from Start menu :
After selecting MySQL workbench following login screen will come:
Now input the login details :
After successful login, a new screen will come and from the object browser panel select a database:
After selecting the database, select the tables:
Now right click on emp_details a window pops up, click on Alter Table:
Clicking on ” Alter Table ” details of emp_details will come:
Now click on Trigger tab in the previous section, then select the Timing/Event it may be AFTER DELETE, AFTER INSERT, AFTER UPDATE or BEFORE DELETE, BEFORE INSERT OR BEFORE UPDATE. Let we select AFTER INSERT, you also notice that there is a button Add Trigger.
Clicking on Add Trigger button a default code on trigger will come on the basis of choosing Timing/Event:
Trigger Name: emp_details_AINS Default Trigger code details:USE hr; DELIMITER $$ CREATE TRIGGER emp_details_AINS AFTER INSERT ON emp_details FOR EACH ROW — Edit trigger body code below this line. Do not edit lines above this one
After completing the code, click on apply button.
Note: See a new text Delete Trigger has come in Add Trigger button. Clicking on this you can delete the trigger.
Finally you can review the script once again, as there is no error, let click on Apply button:
This the final window before finish. Let click on Finish button.
If you take a look at the schema, you will see emp_details_AINS trigger under the emp_details table as follows:
MySQL Trigger : Example AFTER INSERT
In the following example, we have two tables: emp_details and log_emp_details. To insert some information into log_ emp_details table (which have three fields employee id and salary and edttime) every time, when an INSERT happen into emp_details table we have used the following trigger :DELIMITER $$ USE hr $$ CREATE DEFINER=root@127.0.0.1 TRIGGER hr.emp_details_AINS AFTER INSERT ON hr.emp_details FOR EACH ROW — Edit trigger body code below this line. Do not edit lines above this one BEGIN INSERT INTO log_emp_details VALUES(NEW.employee_id, NEW.salary, NOW()); END$$
Records of the table (on some columns) : emp_detailsmysql> SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY, COMMISSION_PCT FROM emp_details; +————-+————+———–+———+———-+—————-+ | EMPLOYEE_ID | FIRST_NAME | LAST_NAME | JOB_ID | SALARY | COMMISSION_PCT | +————-+————+———–+———+———-+—————-+ | 100 | Steven | King | AD_PRES | 24000.00 | 0.10 | | 101 | Neena | Kochhar | AD_VP | 17000.00 | 0.50 | | 102 | Lex | De Haan | AD_VP | 17000.00 | 0.50 | | 103 | Alexander | Hunold | IT_PROG | 9000.00 | 0.25 | | 104 | Bruce | Ernst | IT_PROG | 6000.00 | 0.25 | | 105 | David | Austin | IT_PROG | 4800.00 | 0.25 | +————-+————+———–+———+———-+—————-+ 6 rows in set (0.00 sec)
Now insert one record in emp_details table see the records both in emp_details and log_emp_details tables :mysql> INSERT INTO emp_details VALUES(236, ‘RABI’, ‘CHANDRA’, ‘RABI’,’590.423.45700′, ‘2013-01-12’, ‘AD_VP’, 15000, .5); Query OK, 1 row affected (0.07 sec)
In the following example, before insert a new record in emp_details table, a trigger check the column value of FIRST_NAME, LAST_NAME, JOB_ID and – If there are any space(s) before or after the FIRST_NAME, LAST_NAME, TRIM() function will remove those. – The value of the JOB_ID will be converted to upper cases by UPPER() function.
Here is the trigger code :USE hr; DELIMITER $$ CREATE TRIGGER emp_details_BINS BEFORE INSERT ON emp_details FOR EACH ROW — Edit trigger body code below this line. Do not edit lines above this one BEGIN SET NEW.FIRST_NAME = TRIM(NEW.FIRST_NAME); SET NEW.LAST_NAME = TRIM(NEW.LAST_NAME); SET NEW.JOB_ID = UPPER(NEW.JOB_ID);END; $$Now insert a row into emp_details table (check the FIRST_NAME, LAST_NAME, JOB_ID columns) :mysql> INSERT INTO emp_details VALUES (334, ‘ Ana ‘, ‘ King’, ‘ANA’, ‘690.432.45701’, ‘2013-02-05’, ‘it_prog’, 17000, .50); Query OK, 1 row affected (0.04 sec)
Now list the following fields of emp_details :mysql> SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY, COMMISSION_PCT FROM emp_details; +————-+————+———–+———+———-+—————-+ | EMPLOYEE_ID | FIRST_NAME | LAST_NAME | JOB_ID | SALARY | COMMISSION_PCT | +————-+————+———–+———+———-+—————-+ | 100 | Steven | King | AD_PRES | 24000.00 | 0.10 | | 101 | Neena | Kochhar | AD_VP | 17000.00 | 0.50 | | 102 | Lex | De Haan | AD_VP | 17000.00 | 0.50 | | 103 | Alexander | Hunold | IT_PROG | 9000.00 | 0.25 | | 104 | Bruce | Ernst | IT_PROG | 6000.00 | 0.25 | | 105 | David | Austin | IT_PROG | 4800.00 | 0.25 | | 236 | RABI | CHANDRA | AD_VP | 15000.00 | 0.50 | | 334 | Ana | King | IT_PROG | 17000.00 | 0.50 | +————-+————+———–+———+———-+—————-+ 8 rows in set (0.00 sec)
See the last row :
FIRST_NAME – > ‘ Ana ‘ has changed to ‘Ana’ LAST_NAME – > ‘ King’ has changed to ‘King’ JOB_ID – > ‘ it_prog’ has changed to ‘IT_PROG’
MySQL Trigger : Example AFTER UPDATE
We have two tables student_mast and stu_log. student_mast have three columns STUDENT_ID, NAME, ST_CLASS. stu_log table has two columns user_id and description.mysql> SELECT * FROM STUDENT_MAST; +————+——————+———-+ | STUDENT_ID | NAME | ST_CLASS | +————+——————+———-+ | 1 | Steven King | 7 | | 2 | Neena Kochhar | 8 | | 3 | Lex De Haan | 8 | | 4 | Alexander Hunold | 10 | +————+——————+———-+ 4 rows in set (0.00 sec)
Let we promote all the students in next class i.e. 7 will be 8, 8 will be 9 and so on. After updating a single row in student_mast table a new row will be inserted in stu_log table where we will store the current user id and a small description regarding the current update. Here is the trigger code :– Full Trigger DDL Statements — Note: Only CREATE TRIGGER statements are allowed DELIMITER $$ USE test $$ CREATE DEFINER=root@127.0.0.1 TRIGGER test.student_mast_AUPD AFTER UPDATE ON test.student_mastFOR EACH ROW — Edit trigger body code below this line. Do not edit lines above this one BEGIN INSERT into stu_log VALUES (user(), CONCAT(‘Update Student Record ‘, OLD.NAME,’ Previous Class :’,OLD.ST_CLASS,’ Present Class ‘, NEW.st_class)); END $$
The trigger show you the updated records in ‘stu_log’. Here is the latest position of STUDENT_MAST and STU_LOG tables :mysql> SELECT * FROM STUDENT_MAST; +————+——————+———-+ | STUDENT_ID | NAME | ST_CLASS | +————+——————+———-+ | 1 | Steven King | 8 | | 2 | Neena Kochhar | 9 | | 3 | Lex De Haan | 9 | | 4 | Alexander Hunold | 11 | +————+——————+———-+ 4 rows in set (0.00 sec)mysql> SELECT * FROM STU_LOG; +—————-+—————————————————————————+ | user_id | description | +—————-+—————————————————————————+ | root@localhost | Update Student Record Steven King Previous Class :7 Present Class 8 | | root@localhost | Update Student Record Neena Kochhar Previous Class :8 Present Class 9 | | root@localhost | Update Student Record Lex De Haan Previous Class :8 Present Class 9 | | root@localhost | Update Student Record Alexander Hunold Previous Class :10 Present Class 11| +—————-+—————————————————————————+ 4 rows in set (0.00 sec)
MySQL Trigger : Example BEFORE UPDATE
We have a table student_marks with 10 columns and 4 rows. There are data only in STUDENT_ID and NAME columns.mysql> SELECT * FROM STUDENT_MARKS; +————+——————+——+——+——+——+——+——-+———–+——-+ | STUDENT_ID | NAME | SUB1 | SUB2 | SUB3 | SUB4 | SUB5 | TOTAL | PER_MARKS | GRADE | +————+——————+——+——+——+——+——+——-+———–+——-+ | 1 | Steven King | 0 | 0 | 0 | 0 | 0 | 0 | 0.00 | | | 2 | Neena Kochhar | 0 | 0 | 0 | 0 | 0 | 0 | 0.00 | | | 3 | Lex De Haan | 0 | 0 | 0 | 0 | 0 | 0 | 0.00 | | | 4 | Alexander Hunold | 0 | 0 | 0 | 0 | 0 | 0 | 0.00 | | +————+——————+——+——+——+——+——+——-+———–+——-+ 4 rows in set (0.00 sec)
Now the exam is over and we have received all subject marks, now we will update the table, total marks of all subject, the percentage of total marks and grade will be automatically calculated. For this sample calculation, the following conditions are assumed :
Total Marks (will be stored in TOTAL column) : TOTAL = SUB1 + SUB2 + SUB3 + SUB4 + SUB5
Percentage of Marks (will be stored in PER_MARKS column) : PER_MARKS = (TOTAL)/5
Grade (will be stored GRADE column) : – If PER_MARKS>=90 -> ‘EXCELLENT’ – If PER_MARKS>=75 AND PER_MARKS<90 -> ‘VERY GOOD’ – If PER_MARKS>=60 AND PER_MARKS<75 -> ‘GOOD’ – If PER_MARKS>=40 AND PER_MARKS<60 -> ‘AVERAGE’ – If PER_MARKS<40-> ‘NOT PROMOTED’
Here is the code :mysql> UPDATE STUDENT_MARKS SET SUB1 = 54, SUB2 = 69, SUB3 = 89, SUB4 = 87, SUB5 = 59 WHERE STUDENT_ID = 1; Query OK, 1 row affected (0.05 sec) Rows matched: 1 Changed: 1 Warnings: 0
Let update the marks of a student :USE test; DELIMITER $$ CREATE TRIGGER student_marks_BUPD BEFORE UPDATE ON student_marks FOR EACH ROW — Edit trigger body code below this line. Do not edit lines above this one BEGIN SET NEW.TOTAL = NEW.SUB1 + NEW.SUB2 + NEW.SUB3 + NEW.SUB4 + NEW.SUB5; SET NEW.PER_MARKS = NEW.TOTAL/5; IF NEW.PER_MARKS >=90 THEN SET NEW.GRADE = ‘EXCELLENT’; ELSEIF NEW.PER_MARKS>=75 AND NEW.PER_MARKS<90 THEN SET NEW.GRADE = ‘VERY GOOD’; ELSEIF NEW.PER_MARKS>=60 AND NEW.PER_MARKS<75 THEN SET NEW.GRADE = ‘GOOD’; ELSEIF NEW.PER_MARKS>=40 AND NEW.PER_MARKS<60 THEN SET NEW.GRADE = ‘AVERAGE’; ELSESET NEW.GRADE = ‘NOT PROMOTED’; END IF; END; $$
Now check the STUDENT_MARKS table with updated data. The trigger show you the updated records in ‘stu_log’.mysql> SELECT * FROM STUDENT_MARKS; +————+——————+——+——+——+——+——+——-+———–+——-+ | STUDENT_ID | NAME | SUB1 | SUB2 | SUB3 | SUB4 | SUB5 | TOTAL | PER_MARKS | GRADE | +————+——————+——+——+——+——+——+——-+———–+——-+ | 1 | Steven King | 54 | 69 | 89 | 87 | 59 | 358 | 71.60 | GOOD | | 2 | Neena Kochhar | 0 | 0 | 0 | 0 | 0 | 0 | 0.00 | | | 3 | Lex De Haan | 0 | 0 | 0 | 0 | 0 | 0 | 0.00 | | | 4 | Alexander Hunold | 0 | 0 | 0 | 0 | 0 | 0 | 0.00 | | +————+——————+——+——+——+——+——+——-+———–+——-+ 4 rows in set (0.00 sec)
MySQL Trigger : Example AFTER DELETE
In our ‘AFTER UPDATE’ example, we had two tables student_mast and stu_log. student_mast have three columns STUDENT_ID, NAME, ST_CLASS and stu_log table has two columns user_id and description. We want to store some information in stu_log table after a delete operation happened on student_mast table. Here is the trigger :USE test; DELIMITER $$ CREATE TRIGGER student_mast_ADEL AFTER DELETE ON student_mast FOR EACH ROW — Edit trigger body code below this line. Do not edit lines above this one BEGIN INSERT into stu_log VALUES (user(), CONCAT(‘Update Student Record ‘, OLD.NAME,’ Clas :’,OLD.ST_CLASS, ‘-> Deleted on ‘, NOW())); END; $$
Let delete a student from STUDENT_MAST.mysql> DELETE FROM STUDENT_MAST WHERE STUDENT_ID = 1; Query OK, 1 row affected (0.06 sec)
Here is the latest position of STUDENT_MAST, STU_LOG tables :mysql> SELECT * FROM STUDENT_MAST; +————+——————+———-+ | STUDENT_ID | NAME | ST_CLASS | +————+——————+———-+ | 2 | Neena Kochhar | 9 | | 3 | Lex De Haan | 9 | | 4 | Alexander Hunold | 11 | +————+——————+———-+ 3 rows in set (0.00 sec) mysql> SELECT * FROM STU_LOG; +—————-+—————————————————————————–+ | user_id | description | +—————-+—————————————————————————–+ | root@localhost | Update Student RecordSteven King Previous Class :7 Present Class 8 | | root@localhost | Update Student RecordNeena Kochhar Previous Class :8 Present Class 9 | | root@localhost | Update Student RecordLex De Haan Previous Class :8 Present Class 9 | | root@localhost | Update Student RecordAlexander Hunold Previous Class :10 Present Class 11 | | root@localhost | Update Student Record Steven King Clas :8-> Deleted on 2013-07-16 15:35:30 | +—————-+—————————————————————————–+ 5 rows in set (0.00 sec)
How MySQL handle errors during trigger execution?
If a BEFORE trigger fails, the operation on the corresponding row is not performed.
A BEFORE trigger is activated by the attempt to insert or modify the row, regardless of whether the attempt subsequently succeeds.
An AFTER trigger is executed only if any BEFORE triggers and the row operation execute successfully.
An error during either a BEFORE or AFTER trigger results in failure of the entire statement that caused trigger invocation.
For transactional tables, failure of a statement should cause a rollback of all changes performed by the statement.
Delete a MySQL trigger
To delete or destroy a trigger, use a DROP TRIGGER statement. You must specify the schema name if the trigger is not in the default (current) schema :DROP TRIGGER [IF EXISTS] [schema_name.]trigger_nam
if you drop a table, any triggers for the table are also dropped.
A procedure (often called a stored procedure) is a subroutine like a subprogram in a regular computing language, stored in database. A procedure has a name, a parameter list, and SQL statement(s). All most all relational database system supports stored procedure, MySQL 5 introduce stored procedure. In the following sections we have discussed MySQL procedure in details and used MySQL 5.6 under Windows 7. MySQL 5.6 supports “routines” and there are two kinds of routines : stored procedures which you call, or functions whose return values you use in other SQL statements the same way that you use pre-installed MySQL functions like pi(). The major difference is that UDFs can be used like any other expression within SQL statements, whereas stored procedures must be invoked using the CALL statement.
Contents:
Why Stored Procedures?
How to Create MySQL Procedure?
Pick a Delimiter
MySQL Procedure Example
Tools to create MySQL Procedure
Call a procedure
Procedure : Characteristics Clauses
MySQL : Compound-Statement
BEGIN … END Compound-Statement Syntax
Label Statement
Declare Statement
Variables in Stored Programs
Procedure Parameters
MySQL Procedure : Parameter IN example
MySQL Procedure : Parameter OUT example
MySQL Procedure : Parameter INOUT example
MySQL : If Statement
MySQL : Case Statement
MySQL : ITERATE, LEAVE Statement
MySQL : LOOP Statement
MySQL : REPEAT Statement
MySQL : RETURN Statement
MySQL : WHILE Statement
MySQL : ALTER PROCEDURE
MySQL : DROP PROCEDURE
MySQL : Cursor
Access Control for Stored Programs
Why Stored Procedures?
Stored procedures are fast. MySQL server takes some advantage of caching, just as prepared statements do. The main speed gain comes from reduction of network traffic. If you have a repetitive task that requires checking, looping, multiple statements, and no user interaction, do it with a single call to a procedure that’s stored on the server.
Stored procedures are portable. When you write your stored procedure in SQL, you know that it will run on every platform that MySQL runs on, without obliging you to install an additional runtime-environment package, or set permissions for program execution in the operating system, or deploy different packages if you have different computer types. That’s the advantage of writing in SQL rather than in an external language like Java or C or PHP.
Stored procedures are always available as ‘source code’ in the database itself. And it makes sense to link the data with the processes that operate on the data.
Stored procedures are migratory! MySQL adheres fairly closely to the SQL:2003 standard. Others (DB2, Mimer) also adhere.
Create Procedure
Following statements create a stored procedure. By default, a procedure is associated with the default database (currently used database). To associate the procedure with a given database, specify the name as database_name.stored_procedure_name when you create it. Here is the complete syntax :
Syntax:CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name ([proc_parameter[,…]]) [characteristic …] routine_body proc_parameter: [ IN | OUT | INOUT ] param_name type type: Any valid MySQL data type characteristic: COMMENT ‘string’ | LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } routine_body: Valid SQL routine statement
Before create a procedure we need some information which are described in this section :
Check the MySQL version:
Following command displays the version of MySQL :mysql>SELECT VERSION(); +———–+ | VERSION() | +———–+ | 5.6.12 | +———–+ 1 row in set (0.00 sec)
Check the privileges of the current user:
CREATE PROCEDURE and CREATE FUNCTION require the CREATE ROUTINE privilege. They might also require the SUPER privilege, depending on the DEFINER value, as described later in this section. If binary logging is enabled, CREATE FUNCTION might require the SUPER privilege. By default, MySQL automatically grants the ALTER ROUTINE and EXECUTE privileges to the routine creator. This behavior can be changed by disabling the automatic_sp_privileges system variable.mysql> SHOW PRIVILEGES; +—————–+—————————-+——————————————————-+ | Privilege | Context | Comment | +—————–+—————————-+——————————————————-+ | Alter | Tables | To alter the table | | Alter routine | Functions,Procedures | To alter or drop stored functions/procedures | | Create | Databases,Tables,Indexes | To create new databases and tables | | Create routine | Databases | To use CREATE FUNCTION/PROCEDURE | | Create temporary| Databases | To use CREATE TEMPORARY TABLE | | tables | | | | Create view | Tables | To create new views | | Create user | Server Admin | To create new users | | Delete | Tables | To delete existing rows | | Drop | Databases,Tables | To drop databases, tables, and views | | Event | Server Admin | To create, alter, drop and execute events | | Execute | Functions,Procedures | To execute stored routines | | File | File access on server | To read and write files on the server | | Grant option | Databases,Tables, | To give to other users those privileges you possess | | | Functions,Procedures | | | Index | Tables | To create or drop indexes | | Insert | Tables | To insert data into tables | | Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) | | Process | Server Admin | To view the plain text of currently executing queries | | Proxy | Server Admin | To make proxy user possible | | References | Databases,Tables | To have references on tables | | Reload | Server Admin | To reload or refresh tables, logs and privileges | | Replication | Server Admin | To ask where the slave or master servers are | | client | | | | Replication | Server Admin | To read binary log events from the master | | slave | | | | Select | Tables | To retrieve rows from table | | Show databases | Server Admin | To see all databases with SHOW DATABASES | | Show view | Tables | To see views with SHOW CREATE VIEW | | Shutdown | Server Admin | To shut down the server | | Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. | | Trigger | Tables | To use triggers | | Create | Server Admin | To create/alter/drop tablespaces | | tablespace | | | | Update | Tables | To update existing rows | | Usage | Server Admin | No privileges – allow connect only | +————————-+——————–+——————————————————-+ 31 rows in set (0.00 sec)
Select a database:
Before creates a procedure we must select a database. Let see the list of databases and choose one of them.mysql> SHOW DATABASES; +——————–+ | Database | +——————–+ | information_schema | | hr | | mysql | | performance_schema | | sakila | | test | | world | +——————–+ 7 rows in set (0.06 sec))
Now select the database ‘hr’ and list the tables :mysql> USE hr; Database changed mysql> SHOW TABLES; +————–+ | Tables_in_hr | +————–+ | alluser | | departments | | emp_details | | job_history | | jobs | | locations | | regions | | user | | user_details | +————–+ 9 rows in set (0.00 sec)
Pick a Delimiter
The delimiter is the character or string of characters which is used to complete an SQL statement. By default we use semicolon (;) as a delimiter. But this causes problem in stored procedure because a procedure can have many statements, and everyone must end with a semicolon. So for your delimiter, pick a string which is rarely occur within statement or within procedure. Here we have used double dollar sign i.e. $$.You can use whatever you want. To resume using “;” as a delimiter later, say “DELIMITER ; $$”. See here how to change the delimiter :mysql> DELIMITER $$ ;
Now the default DELIMITER is “$$”. Let execute a simple SQL command :mysql> SELECT * FROM user $$ +———-+———–+——–+ | userid | password | name | +———-+———–+——–+ | scott123 | 123@sco | Scott | | ferp6734 | dloeiu@&3 | Palash | | diana094 | ku$j@23 | Diana | +———-+———–+——–+ 3 rows in set (0.00 sec)
Now execute the following command to resume “;” as a delimiter :
mysql> DELIMITER ; $$
Example : MySQL Procedure
Here we have created a simple procedure called job_data, when we will execute the procedure it will display all the data from “jobs” tables.mysql> DELIMITER $$ ;mysql> CREATE PROCEDURE job_data() > SELECT * FROM JOBS; $$ Query OK, 0 rows affected (0.00 sec)
Explanation:
– CREATE PROCEDURE command creates the stored procedure. – Next part is the procedure name. Here the procedure name is ” job_data”. — Procedure names are not case sensitive, so job_data and JOB_DATA are same. — You cannot use two procedures with the same name in the same database. — You can use qualified names of the form “database-name.procedure-name”, for example “hr.job_data”. — Procedure names can be delimited. If the name is delimited, it can contain spaces. — The maximum name length is 64 characters. — Avoid using names of built-in MySQL functions. — The last part of “CREATE PROCEDURE” is a pair of parentheses. “()” holds the parameter(s) list as there are no parameters in this procedure, the parameter list is empty. – Next part is SELECT * FROM JOBS; $$ which is the last statement of the procedure body. Here the semicolon (;) is optional as $$ is a real statement-ender.
Tools to create MySQL Procedure
You can write a procedure in MySQL command line tool or you can use MySQL workbench which is an excellent front-end tool (here we have used version 5.3 CE).
MySQL command line tool: –
Select MySQL command Client from Start menu :
Selecting MySQL command prompt following screen will come :
After a successful login you can access the MySQL command prompt :
Now you write and run your own procedure, see the following example :
MySQL workbench (5.3 CE): –
Select MySQL workbench from Start menu :
After selecting MySQL workbench following login screen will come :
Now input the login details :
After successful login a new screen will come and from the object browser panel select a database :
After selecting the database right click on Routines a new popup will come :
After selecting “Create Procedure” following screen will come where you can write your own procedure.
After writing the procedure click on Apply button and the following screen will come :
Next screen will be to review the script and apply on the database.
Now click on Finish button and run the procedure :
Call a procedure
The CALL statement is used to invoke a procedure that is stored in a DATABASE. Here is the syntax :CALL sp_name([parameter[,…]]) CALL sp_name[()]
This statement is a MySQL extension. It returns the exact string that can be used to re-create the named stored procedure. Both statement require that you be the owner of the routine. Here is the syntax :SHOW CREATE PROCEDURE proc_name
Let execute the above and see the output :mysql> SHOW CREATE PROCEDURE job_data$$ https://www.w3resource.com/mysql/show-procedure.html
MySQL : Characteristics Clauses
There are some clauses in CREATE PROCEDURE syntax which describe the characteristics of the procedure. The clauses come after the parentheses, but before the body. These clauses are all optional. Here are the clauses :characteristic: COMMENT ‘string’ | LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER }
COMMENT :
The COMMENT characteristic is a MySQL extension. It is used to describe the stored routine and the information is displayed by the SHOW CREATE PROCEDURE statements.
LANGUAGE :
The LANGUAGE characteristic indicates that the body of the procedure is written in SQL.
NOT DETERMINISTIC :
NOT DETERMINISTIC, is informational, a routine is considered “deterministic” if it always produces the same result for the same input parameters, and “not deterministic” otherwise.
CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA
CONTAINS SQL :
CONTAINS SQL means there are no statements that read or write data, in the routine. For example statements SET @x = 1 or DO RELEASE_LOCK(‘abc’), which execute but neither read nor write data. This is the default if none of these characteristics is given explicitly.
NO SQL:
NO SQL means routine contains no SQL statements.
READS SQL DATA :
READS SQL DATA means the routine contains statements that read data (for example, SELECT), but not statements that write data.
MODIFIES SQL DATA :
MODIFIES SQL DATA means routine contains statements that may write data (for example, INSERT or DELETE).
SQL SECURITY { DEFINER | INVOKER }
SQL SECURITY, can be defined as either SQL SECURITY DEFINER or SQL SECURITY INVOKER to specify the security context; that is, whether the routine executes using the privileges of the account named in the routine DEFINER clause or the user who invokes it. This account must have permission to access the database with which the routine is associated. The default value is DEFINER. The user who invokes the routine must have the EXECUTE privilege for it, as must the DEFINER account if the routine executes in definer security context.
All the above characteristics clauses have defaults. Following two statements produce same result :
is the same as :mysql> CREATE PROCEDURE new_job_data() -> COMMENT ” -> LANGUAGE SQL -> NOT DETERMINISTIC -> CONTAINS SQL -> SQL SECURITY DEFINER -> SELECT * FROM JOBS; -> $$ Query OK, 0 rows affected (0.26 sec)
In the next section we will discuss on parameters
Before going to MySQL parameters let discuss some MySQL compound statements :
MySQL : Compound-Statement
A compound statement is a block that can contain other blocks; declarations for variables, condition handlers, and cursors; and flow control constructs such as loops and conditional tests. As of version 5.6 MySQL have following compound statements :BEGIN … END Compound-StatementStatement LabelDECLAREVariables in Stored ProgramsFlow Control StatementsCursorsCondition Handling
In this section we will discuss the first four statements to cover the parameters part of CREATE PROCEDURE statement.
BEGIN … END Compound-Statement Syntax
BEGIN … END block is used to write compound statements, i.e. when you need more than one statement within stored programs (e.g. stored procedures, functions, triggers, and events). Here is the syntax :[begin_label:] BEGIN
[statement_list]
END [end_label])
statement_list : It represents one or more statements terminated by a semicolon(;). The statement_list itself is optional, so the empty compound statement BEGIN END is valid.
begin_label, end_label : See the following section.
Label Statement
Labels are permitted for BEGIN … END blocks and for the LOOP, REPEAT, and WHILE statements. Here is the syntax :[begin_label:] BEGIN [statement_list] END [end_label] [begin_label:] LOOP statement_list END LOOP [end_label] [begin_label:] REPEAT statement_list UNTIL search_condition END REPEAT [end_label] [begin_label:] WHILE search_condition DO statement_list END WHILE [end_label]
Label use for those statements which follows following rules:
begin_label must be followed by a colon
begin_label can be given without end_label. If end_label is present, it must be the same as begin_label
end_label cannot be given without begin_label.
Labels at the same nesting level must be distinct
Labels can be up to 16 characters long.
Declare Statement
The DECLARE statement is used to define various items local to a program, for example local variables, conditions and handlers, cursors. DECLARE is used only inside a BEGIN … END compound statement and must be at its start, before any other statements. Declarations follow the following order :
Cursor declarations must appear before handler declarations.
Variable and condition declarations must appear before cursor or handler declarations.
Variables in Stored Programs
System variables and user-defined variables can be used in stored programs, just as they can be used outside stored-program context. Stored programs use DECLARE to define local variables, and stored routines (procedures and functions) can be declared to take parameters that communicate values between the routine and its caller.
Declare a Variable:DECLARE var_name [, var_name] … type [DEFAULT value]
To provide a default value for a variable, include a DEFAULT clause. The value can be specified as an expression; it need not be constant. If the DEFAULT clause is missing, the initial value is NULL.
Example: Local variables
Local variables are declared within stored procedures and are only valid within the BEGIN…END block where they are declared. Local variables can have any SQL data type. The following example shows the use of local variables in a stored procedure.DELIMITER $$ CREATE PROCEDURE my_procedure_Local_Variables() BEGIN /* declare local variables */ DECLARE a INT DEFAULT 10; DECLARE b, c INT; /* using the local variables */ SET a = a + 100; SET b = 2; SET c = a + b; BEGIN /* local variable in nested block */ DECLARE c INT; SET c = 5; /* local variable c takes precedence over the one of the same name declared in the enclosing block. */ SELECT a, b, c; END; SELECT a, b, c; END$$
Now execute the procedure :mysql> CALL my_procedure_Local_Variables(); +——+——+——+ | a | b | c | +——+——+——+ | 110 | 2 | 5 | +——+——+——+ 1 row in set (0.00 sec) +——+——+——+ | a | b | c | +——+——+——+ | 110 | 2 | 112 | +——+——+——+ 1 row in set (0.01 sec) Query OK, 0 rows affected (0.03 sec)
Example : User variables
In MySQL stored procedures, user variables are referenced with an ampersand (@) prefixed to the user variable name (for example, @x and @y). The following example shows the use of user variables within the stored procedure :
DELIMITER $$
CREATE PROCEDURE my_procedure_User_Variables()
BEGIN
SET @x = 15;
SET @y = 10;
SELECT @x, @y, @x-@y;
END$$
Copy
Now execute the procedure :mysql> CALL my_procedure_User_Variables() ; +——+——+——-+ | @x | @y | @x-@y | +——+——+——-+ | 15 | 10 | 5 | +——+——+——-+ 1 row in set (0.04 sec) Query OK, 0 rows affected (0.05 sec)
MySQL : Procedure Parameters
Here is the parameter part of CREATE PROCEDURE syntax :CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name ([proc_parameter[,…]]) [characteristic …] routine_body proc_parameter: [ IN | OUT | INOUT ] param_name type
We can divide the above CREATE PROCEDURE statement in the following ways :
In the first example, the parameter list is empty.
In the second examp,le an IN parameter passes a value into a procedure. The procedure might modify the value, but the modification is not visible to the caller when the procedure returns.
In the third example, an OUT parameter passes a value from the procedure back to the caller. Its initial value is NULL within the procedure, and its value is visible to the caller when the procedure returns.
In the fourth example, an INOUT parameter is initialized by the caller, can be modified by the procedure, and any change made by the procedure is visible to the caller when the procedure returns.
In a procedure, each parameter is an IN parameter by default. To specify otherwise for a parameter, use the keyword OUT or INOUT before the parameter name.
MySQL Procedure : Parameter IN example
In the following procedure, we have used a IN parameter ‘var1’ (type integer) which accept a number from the user. Within the body of the procedure, there is a SELECT statement which fetches rows from ‘jobs’ table and the number of rows will be supplied by the user. Here is the procedure :mysql> CREATE PROCEDURE my_proc_IN (IN var1 INT) -> BEGIN -> SELECT * FROM jobs LIMIT var1; -> END$$ Query OK, 0 rows affected (0.00 sec)
To execute the first 2 rows from the ‘jobs’ table execute the following command :mysql> CALL my_proc_in(2)$$ +———+——————————-+————+————+ | JOB_ID | JOB_TITLE | MIN_SALARY | MAX_SALARY | +———+——————————-+————+————+ | AD_PRES | President | 20000 | 40000 | | AD_VP | Administration Vice President | 15000 | 30000 | +———+——————————-+————+————+ 2 rows in set (0.00 sec)Query OK, 0 rows affected (0.03 sec)
Now execute the first 5 rows from the ‘jobs’ table :mysql> CALL my_proc_in(5)$$ +————+——————————-+————+————+ | JOB_ID | JOB_TITLE | MIN_SALARY | MAX_SALARY | +————+——————————-+————+————+ | AD_PRES | President | 20000 | 40000 | | AD_VP | Administration Vice President | 15000 | 30000 | | AD_ASST | Administration Assistant | 3000 | 6000 | | FI_MGR | Finance Manager | 8200 | 16000 | | FI_ACCOUNT | Accountant | 4200 | 9000 | +————+——————————-+————+————+ 5 rows in set (0.00 sec)Query OK, 0 rows affected (0.05 sec)
MySQL Procedure : Parameter OUT example
The following example shows a simple stored procedure that uses an OUT parameter. Within the procedure MySQL MAX() function retrieves maximum salary from MAX_SALARY of jobs table.
mysql> CREATE PROCEDURE my_proc_OUT (OUT highest_salary INT)
-> BEGIN
-> SELECT MAX(MAX_SALARY) INTO highest_salary FROM JOBS;
-> END$$
Query OK, 0 rows affected (0.00 sec)
Copy
In the body of the procedure, the parameter will get the highest salary from MAX_SALARY column. After calling the procedure the word OUT tells the DBMS that the value goes out from the procedure. Here highest_salary is the name of the output parameter and we have passed its value to a session variable named @M, in the CALL statement.mysql> CALL my_proc_OUT(@M)$$ Query OK, 1 row affected (0.03 sec) mysql< SELECT @M$$+——-+ | @M | +——-+ | 40000 | +——-+ 1 row in set (0.00 sec)
MySQL Procedure : Parameter INOUT example
The following example shows a simple stored procedure that uses an INOUT parameter and an IN parameter. The user will supply ‘M’ or ‘F’ through IN parameter (emp_gender) to count a number of male or female from user_details table. The INOUT parameter (mfgender) will return the result to a user. Here is the code and output of the procedure :mysql> CALL my_proc_OUT(@M)$$Query OK, 1 row affected (0.03 sec)mysql> CREATE PROCEDURE my_proc_INOUT (INOUT mfgender INT, IN emp_gender CHAR(1)) -> BEGIN -> SELECT COUNT(gender) INTO mfgender FROM user_details WHERE gender = emp_gender; -> END$$ Query OK, 0 rows affected (0.00 sec)
Now check the number of male and female users of the said tables :
MySQL supports IF, CASE, ITERATE, LEAVE, LOOP, WHILE, and REPEAT constructs for flow control within stored programs. It also supports RETURN within stored functions.
MySQL : If Statement
The IF statement implements a basic conditional construct within a stored programs and must be terminated with a semicolon. There is also an IF() function, which is different from the IF statement. Here is the syntax of if statement :IF condition THEN statement(s) [ELSEIF condition THEN statement(s)] … [ELSE statement(s)] END IF
– If the condition evaluates to true, the corresponding THEN or ELSEIF clause statements(s) executes. – If no condition matches, the ELSE clause statement(s) executes. – Each statement(s) consists of one or more SQL statements; an empty statement(s) is not permitted.
Example:
In the following example, we pass user_id through IN parameter to get the user name. Within the procedure, we have used IF ELSEIF and ELSE statement to get user name against multiple user id. The user name will be stored into INOUT parameter user_name.CREATE DEFINER=root@127.0.0.1 PROCEDURE GetUserName(INOUT user_name varchar(16), IN user_id varchar(16)) BEGIN DECLARE uname varchar(16); SELECT name INTO uname FROM user WHERE userid = user_id; IF user_id = “scott123” THEN SET user_name = “Scott”; ELSEIF user_id = “ferp6734” THEN SET user_name = “Palash”; ELSEIF user_id = “diana094” THEN SET user_name = “Diana”; END IF; END
Execute the procedure:mysql> CALL GetUserName(@A,’scott123′)$$ Query OK, 1 row affected (0.00 sec) mysql> SELECT @A; -> $$ +——-+ | @A | +——-+ | Scott | +——-+ 1 row in set (0.00 sec)
MySQL : Case Statement
The CASE statement is used to create complex conditional construct within stored programs. The CASE statement cannot have an ELSE NULL clause, and it is terminated with END CASE instead of END. Here is the syntax :CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] … [ELSE statement_list] END CASE
orCASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list] … [ELSE statement_list] END CASE
Explanation: First syntax – case_value is an expression. – This value is compared to the when_value expression in each WHEN clause until one of them is equal. – When an equal when_value is found, the corresponding THEN clause statement_list executes. – If no when_value is equal, the ELSE clause statement_list executes, if there is one.
Explanation: Second syntax – Each WHEN clause search_condition expression is evaluated until one is true, at which point its corresponding THEN clause statement_list executes. – If no search_condition is equal, the ELSE clause statement_list executes, if there is one. – Each statement_list consists of one or more SQL statements; an empty statement_list is not permitted.
Here is the procedure (the procedure is written into MySQL workbench 5.2 CE) :DELIMITER $$ CREATE PROCEDURE hr.my_proc_CASE (INOUT no_employees INT, IN salary INT) BEGIN CASE WHEN (salary>10000) THEN (SELECT COUNT(job_id) INTO no_employees FROM jobs WHERE min_salary>10000); WHEN (salary<10000) THEN (SELECT COUNT(job_id) INTO no_employees FROM jobs WHERE min_salary<10000); ELSE (SELECT COUNT(job_id) INTO no_employees FROM jobs WHERE min_salary=10000); END CASE; END$$
In the above procedure, we pass the salary (amount) variable through IN parameter. Within the procedure, there is CASE statement along with two WHEN and an ELSE which will test the condition and return the count value in no_employees. Let execute the procedure in MySQL command prompt :
Number of employees whose salary greater than 10000 :mysql> CALL my_proc_CASE(@C,10001); Query OK, 1 row affected (0.00 sec) mysql> SELECT @C; +——+ | @C | +——+ | 2 | +——+ 1 row in set (0.00 sec)
Number of employees whose salary less than 10000 :mysql> CALL my_proc_CASE(@C,9999); Query OK, 1 row affected (0.00 sec) mysql> SELECT @C; +——+ | @C | +——+ | 16 | +——+ 1 row in set (0.00 sec)
Number of employees whose salary equal to 10000 :mysql> CALL my_proc_CASE(@C,10000); Query OK, 1 row affected (0.00 sec) mysql> SELECT @C; +——+ | @C | +——+ | 1 | +——+ 1 row in set (0.00 sec)
MySQL: ITERATE Statement
ITERATE means “start the loop again”. ITERATE can appear only within LOOP, REPEAT, and WHILE statements. Here is the syntax :ITERATE label
MySQL: LEAVE Statement
LEAVE statement is used to exit the flow control construct that has the given label. If the label is for the outermost stored program block, LEAVE exits the program. LEAVE can be used within BEGIN … END or loop constructs (LOOP, REPEAT, WHILE). Here is the syntax :LEAVE label
MySQL : LOOP Statement
LOOP is used to create repeated execution of the statement list. Here is the syntax :[begin_label:] LOOP statement_list END LOOP [end_label]
statement_list consists one or more statements, each statement terminated by a semicolon (;). the statements within the loop are repeated until the loop is terminated. Usually, LEAVE statement is used to exit the loop construct. Within a stored function, RETURN can also be used, which exits the function entirely. A LOOP statement can be labeled.
Example:
In the following procedure rows will be inserted in ‘number’ table until x is less than num (number supplied by the user through IN parameter). A random number will be stored every time.DELIMITER $$ CREATE PROCEDURE my_proc_LOOP (IN num INT) BEGIN DECLARE x INT; SET x = 0; loop_label: LOOP INSERT INTO number VALUES (rand()); SET x = x + 1; IF x >= num THEN LEAVE loop_label; END IF; END LOOP; END$$
Now execute the procedure :mysql> CALL my_proc_LOOP(3); Query OK, 1 row affected, 1 warning (0.19 sec) mysql> select * from number; +————–+ | rnumber | +————–+ | 0.1228974146 | | 0.2705919913 | | 0.9842677433 | +————–+ 3 rows in set (0.00 sec)
MySQL: REPEAT Statement
The REPEAT statement executes the statement(s) repeatedly as long as the condition is true. The condition is checked every time at the end of the statements.[begin_label:] REPEAT statement_list UNTIL search_condition END REPEAT [end_label]
statement_list: List of one or more statements, each statement terminated by a semicolon(;). search_condition : An expression.
A REPEAT statement can be labeled.
Example:
Even numbers are numbers that can be divided evenly by 2. In the following procedure an user passes a number through IN parameter and make a sum of even numbers between 1 and that particular number.
DELIMITER $$
CREATE PROCEDURE my_proc_REPEAT (IN n INT)
BEGI
NSET @sum = 0;
SET @x = 1;
REPEAT
IF mod(@x, 2) = 0
THEN
SET @sum = @sum + @x;
END IF;
SET @x = @x + 1;
UNTIL @x > n
END REPEAT;
END $$
Copy
Now execute the procedure:mysql> call my_proc_REPEAT(5); Query OK, 0 rows affected (0.00 sec) mysql> SELECT @sum; +——+ | @sum | +——+ | 6 | +——+ 1 row in set (0.00 sec) mysql> call my_proc_REPEAT(10); Query OK, 0 rows affected (0.00 sec) mysql> SELECT @sum; +——+ | @sum | +——+ | 30 | +——+ 1 row in set (0.00 sec)
MySQL: RETURN Statement
The RETURN statement terminates execution of a stored function and returns the value expr to the function caller. There must be at least one RETURN statement in a stored function. There may be more than one if the function has multiple exit points. Here is the syntax :RETURN expr
This statement is not used in stored procedures or triggers. The LEAVE statement can be used to exit a stored program of those types.
MySQL : WHILE Statement
The WHILE statement executes the statement(s) as long as the condition is true. The condition is checked every time at the beginning of the loop. Each statement is terminated by a semicolon (;). Here is the syntax:[begin_label:] WHILE search_condition DO statement_list END WHILE [end_label]
A WHILE statement can be labeled.
Example:
Odd numbers are numbers that cannot be divided exactly by 2. In the following procedure, a user passes a number through IN parameter and make a sum of odd numbers between 1 and that particular number.
DELIMITER $$
CREATE PROCEDURE my_proc_WHILE(IN n INT)
BEGIN
SET @sum = 0;
SET @x = 1;
WHILE @x<n
DO IF mod(@x, 2) <> 0 THEN
SET @sum = @sum + @x;
END IF;
SET @x = @x + 1;
END WHILE;
END$$
This statement can be used to change the characteristics of a stored procedure. More than one change may be specified in an ALTER PROCEDURE statement. However, you cannot change the parameters or body of a stored procedure using this statement; to make such changes, you must drop and re-create the procedure using DROP PROCEDURE and CREATE PROCEDURE. Here is the syntax :ALTER PROCEDURE proc_name [characteristic …]characteristic: COMMENT ‘string’ | LANGUAGE SQL | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER }
You must have the ALTER ROUTINE privilege for the procedure. By default, that privilege is granted automatically to the procedure creator. In our previous procedure “my_proc_WHILE” the comment section was empty. To input new comment or modify the previous comment use the following command :mysql> ALTER PROCEDURE my_proc_WHILE COMMENT ‘Modify Comment’; >Query OK, 0 rows affected (0.20 sec)
You can check the result through SHOW CREATE PROCEDURE command which we have discussed earlier.
MySQL: DROP PROCEDURE
This statement is used to drop a stored procedure or function. That is, the specified routine is removed from the server. You must have the ALTER ROUTINE privilege for the routine. (If the automatic_sp_privileges system variable is enabled, that privilege and EXECUTE are granted automatically to the routine creator when the routine is created and dropped from the creator when the routine is droppedDROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name
The IF EXISTS clause is a MySQL extension. It prevents an error from occurring if the procedure or function does not exist. A warning is produced that can be viewed with SHOW WARNINGS. Here is an example:mysql> DROP PROCEDURE new_procedure; Query OK, 0 rows affected (0.05 sec)
You can check the result through SHOW CREATE PROCEDURE command which we have discussed earlier.
MySQL: Cursors
A database cursor is a control structure that enables traversal over the records in a database. Cursors are used by database programmers to process individual rows returned by database system queries. Cursors enable manipulation of whole result sets at once. In this scenario, a cursor enables the rows in a result set to be processed sequentially. In SQL procedures, a cursor makes it possible to define a result set (a set of data rows) and perform complex logic on a row by row basis. By using the same mechanics, an SQL procedure can also define a result set and return it directly to the caller of the SQL procedure or to a client application.
MySQL supports cursors inside stored programs. The syntax is as in embedded SQL. Cursors have these properties : – Asensitive: The server may or may not make a copy of its result table – Read only: Not updatable – Nonscrollable: Can be traversed only in one direction and cannot skip rows
To use cursors in MySQL procedures, you need to do the following : – Declare a cursor. – Open a cursor. – Fetch the data into variables. – Close the cursor when done.
Declare a cursor:
The following statement declares a cursor and associates it with a SELECT statement that retrieves the rows to be traversed by the cursor.DECLARE cursor_name CURSOR FOR select_statement
Open a cursor:
The following statement opens a previously declared cursor.OPEN cursor_name
Fetch the data into variables :
This statement fetches the next row for the SELECT statement associated with the specified cursor (which must be open) and advances the cursor pointer. If a row exists, the fetched columns are stored in the named variables. The number of columns retrieved by the SELECT statement must match the number of output variables specified in the FETCH statement.FETCH [[NEXT] FROM] cursor_name INTO var_name [, var_name] …
Close the cursor when done :
This statement closes a previously opened cursor. An error occurs if the cursor is not open.CLOSE cursor_name
Example:
The procedure starts with three variable declarations. Incidentally, the order is important. First, declare variables. Then declare conditions. Then declare cursors. Then, declare handlers. If you put them in the wrong order, you will get an error message.
DELIMITER $$
CREATE PROCEDURE my_procedure_cursors(INOUT return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR FOR
SELECT max_salary FROM jobs;
DECLARE CONTINUE HANDLER FOR NOT FOUNDSET b = 1;
OPEN cur_1;REPEATFETCH cur_1 INTO a;
UNTIL b = 1END REPEAT;
CLOSE cur_1;
SET return_val = a;
END;
$$
Copy
Now execute the procedure:mysql> CALL my_procedure_cursors(@R); Query OK, 0 rows affected (0.00 sec) mysql> SELECT @R; +——-+ | @R | +——-+ | 10500 | +——-+ 1 row in set (0.00 sec)
We will provide more examples on cursors soon.
Access Control for Stored Programs
Stored programs and views are defined prior to use and, when referenced, execute within a security context that determines their privileges. These privileges are controlled by their DEFINER attribute, and, if there is one, their SQL SECURITY characteristic. All stored programs (procedures, functions, and triggers) and views can have a DEFINER attribute that names a MySQL account. If the DEFINER attribute is omitted from a stored program or view definition, the default account is the user who creates the object.
MySQL uses the following rules to control which accounts a user can specify in an object DEFINER attribute :
You can specify a DEFINER value other than your own account only if you have the SUPER privilege.
If you do not have the SUPER privilege, the only legal user value is your own account, either specified literally or by using CURRENT_USER. You cannot set the definer to some other account.
For a stored routine or view, use SQL SECURITY INVOKER in the object definition when possible so that it can be used only by users with permissions appropriate for the operations performed by the object.
If you create definer-context stored programs or views while using an account that has the SUPER privilege, specify an explicit DEFINER attribute that names an account possessing only the privileges required for the operations performed by the object. Specify a highly privileged DEFINER account only when absolutely necessary.
Administrators can prevent users from specifying highly privileged DEFINER accounts by not granting them the SUPER privilege.
Definer-context objects should be written keeping in mind that they may be able to access data for which the invoking user has no privileges. In some cases, you can prevent reference to these objects by not granting unauthorized users particular privileges:
A stored procedure or function cannot be referenced by a user who does not have the EXECUTE privilege for it.
A view cannot be referenced by a user who does not have the appropriate privilege for it (SELECT to select from it, INSERT to insert into it, and so forth).
A subquery is a SQL query nested inside a larger query.
A subquery may occur in:
– A SELECT clause
– A FROM clause
– A WHERE clause
In MySQL subquery can be nested inside a SELECT, INSERT, UPDATE, DELETE, SET, or DO statement or inside another subquery.
A subquery is usually added within the WHERE Clause of another SQL SELECT statement.
You can use the comparison operators, such as >, <, or =. The comparison operator can also be a multiple-row operator, such as IN, ANY, SOME, or ALL.
A subquery can be treated as an inner query, which is a SQL query placed as a part of another query called as outer query.
The inner query executes first before its parent query so that the results of the inner query can be passed to the outer query.
Contents:
Subquery Syntax
MySQL Subquery Example
Subqueries : Guidelines and Types of Subqueries
MySQL Subquery as Scalar Operand
MySQL Subqueries : Using Comparisons
MySQL Subqueries with ALL, ANY, IN, or SOME
MySQL Row Subqueries
MySQL Subqueries with EXISTS or NOT EXISTS
MySQL Correlated Subqueries
MySQL Subqueries in the FROM Clause
Subquery Syntax:
The subquery (inner query) executes once before the main query (outer query) executes.
The main query (outer query) use the subquery result.
Subquery syntax as specified by the SQL standard and supported in MySQLDELETE FROM t1 WHERE s11 > ANY (SELECT COUNT(*) /* no hint */ FROM t2 WHERE NOT EXISTS (SELECT * FROM t3 WHERE ROW(5*t2.s1,77)= (SELECT 50,11*s1 FROM t4 UNION SELECT 50,77 FROM (SELECT * FROM t5) AS t5)));
A subquery can return a scalar (a single value), a single row, a single column, or a table (one or more rows of one or more columns). These are called scalar, column, row, and table subqueries.
MySQL Subquery Example:
Using a subquery, list the name of the employees, paid more than ‘Alexander’ from emp_details .
mysql> SELECT first_name,last_name, salary FROM emp_details WHERE salary >(SELECT salary FROM emp_details WHERE first_name=’Alexander’); +————+———–+———-+ | first_name | last_name | salary | +————+———–+———-+ | Steven | King | 24000.00 | | Neena | Kochhar | 17000.00 | | Lex | De Haan | 17000.00 | | RABI | CHANDRA | 15000.00 | | Ana | King | 17000.00 | +————+———–+———-+ 5 rows in set (0.00 sec)
Subqueries: Guidelines
There are some guidelines to consider when using subqueries : – A subquery must be enclosed in parentheses. – Use single-row operators with single-row subqueries, and use multiple-row operators with multiple-row subqueries. – If a subquery (inner query) returns a null value to the outer query, the outer query will not return any rows when using certain comparison operators in a WHERE clause.
Types of Subqueries
The Subquery as Scalar Operand
Comparisons using Subqueries
Subqueries with ALL, ANY, IN, or SOME
Row Subqueries
Subqueries with EXISTS or NOT EXISTS
Correlated Subqueries
Subqueries in the FROM Clause
MySQL Subquery as Scalar Operand
A scalar subquery is a subquery that returns exactly one column value from one row. A scalar subquery is a simple operand, and you can use it almost anywhere a single column value or literal is legal. If the subquery returns 0 rows then the value of scalar subquery expression in NULL and if the subquery returns more than one row then MySQL returns an error. There is some situation where a scalar subquery cannot be used. If a statement permits only a literal value, you cannot use a subquery. For example, LIMIT requires literal integer arguments, and LOAD DATA INFILE requires a literal string file name. You cannot use subqueries to supply these values.
Example: MySQL Subquery as Scalar Operandmysql> SELECT employee_id, last_name, (CASE WHEN department_id=( SELECT department_id from departments WHERE location_id=2500) THEN ‘Canada’ ELSE ‘USA’ END) location FROM employees; +————-+————-+———-+ | employee_id | last_name | location | +————-+————-+———-+ | 100 | King | USA | | 101 | Kochhar | USA | | 102 | De Haan | USA | | 103 | Hunold | USA | | 104 | Ernst | USA | | 105 | Austin | USA | | – – – – – – – – – – – – – – – – – – -| | – – – – – – – – – – – – – – – – – – -| 107 rows in set (0.00 sec)
MySQL Subqueries: Using Comparisons
A subquery can be used before or after any of the comparison operators. The subquery can return at most one value. The value can be the result of an arithmetic expression or a column function. SQL then compares the value that results from the subquery with the value on the other side of the comparison operator. You can use the following comparison operators:
Operator
Description
=
Equal to
>
Greater than
>=
Greater than or equal to
<
Less than
<=
Less than or equal to
!=
Not equal to
<>
Not equal to
<=>
NULL-safe equal to operator
For example, suppose you want to find the employee id, first_name, last_name, and salaries for employees whose average salary is higher than the average salary throughout the company.
You can use a subquery after a comparison operator, followed by the keyword ALL, ANY, or SOME.
The ALL operator compares value to every value returned by the subquery. Therefore ALL operator (which must follow a comparison operator) returns TRUE if the comparison is TRUE for ALL of the values in the column that the subquery returns.
Syntax:operand comparison_operator ALL (subquery)
NOT IN is an alias for <> ALL. Thus, these two statements are the same:
Code:
SELECT c1 FROM t1 WHERE c1 <> ALL (SELECT c1 FROM t2);
SELECT c1 FROM t1 WHERE c1 NOT IN (SELECT c1 FROM t2);
Copy
Example: MySQL Subquery, ALL operator
The following query selects the department with the highest average salary. The subquery finds the average salary for each department, and then the main query selects the department with the highest average salary.
https://www.w3resource.com/mysql/employees.phpmysql> SELECT department_id, AVG(SALARY) FROM EMPLOYEES GROUP BY department_id HAVING AVG(SALARY)>=ALL (SELECT AVG(SALARY) FROM EMPLOYEES GROUP BY department_id); +—————+————–+ | department_id | AVG(SALARY) | +—————+————–+ | 90 | 19333.333333 | +—————+————–+ 1 row in set (0.00 sec)
Note: Here we have used ALL keyword for this subquery as the department selected by the query must have an average salary greater than or equal to all the average salaries of the other departments.
The ANY operator compares the value to each value returned by the subquery. Therefore ANY keyword (which must follow a comparison operator) returns TRUE if the comparison is TRUE for ANY of the values in the column that the subquery returns.
Syntax:operand comparison_operator ANY (subquery)
Example: MySQL Subquery, ANY operator
The following query selects any employee who works in the location 1800. The subquery finds the department id in the 1800 location, and then the main query selects the employees who work in any of these departments.
employees table:
departments table:
https://www.w3resource.com/mysql/departments.phpmysql> SELECT first_name, last_name,department_id FROM employees WHERE department_id= ANY (SELECT DEPARTMENT_ID FROM departments WHERE location_id=1800); +————+———–+—————+ | first_name | last_name | department_id | +————+———–+—————+ | Michael | Hartstein | 20 | | Pat | Fay | 20 | +————+———–+—————+ 2 rows in set (0.00 sec)
Note: We have used ANY keyword in this query because it is likely that the subquery will find more than one departments in 1800 location. If you use the ALL keyword instead of the ANY keyword, no data is selected because no employee works in all departments of 1800 location
When used with a subquery, the word IN (equal to any member of the list) is an alias for = ANY. Thus, the following two statements are the same:
Code:
SELECT c1 FROM t1 WHERE c1 = ANY (SELECT c1 FROM t2);
SELECT c1 FROM t1 WHERE c1 IN (SELECT c1 FROM t2);
Copy
The word SOME is an alias for ANY. Thus, these two statements are the same:
Code:
SELECT c1 FROM t1 WHERE c1 <> ANY (SELECT c1 FROM t2);
SELECT c1 FROM t1 WHERE c1 <> SOME (SELECT c1 FROM t2);
Copy
MySQL Row Subqueries
A row subquery is a subquery that returns a single row and more than one column value. You can use = , >, <, >=, <=, <>, !=, <=> comparison operators. See the following examples:
Code:
SELECT * FROM table1 WHERE (col1,col2) = (SELECT col3, col4 FROM table2 WHERE id = 10);
SELECT * FROM table1 WHERE ROW(col1,col2) = (SELECT col3, col4 FROM table2 WHERE id = 10);
Copy
For both queries,
if the table table2 contains a single row with id = 10, the subquery returns a single row. If this row has col3 and col4 values equal to the col1 and col2 values of any rows in table1, the WHERE expression is TRUE and each query returns those table1 rows.
If the table2 row col3 and col4 values are not equal the col1 and col2 values of any table1 row, the expression is FALSE and the query returns an empty result set. The expression is unknown (that is, NULL) if the subquery produces no rows.
An error occurs if the subquery produces multiple rows because a row subquery can return at most one row.
Example: MySQL Row Subqueries
In the following examples, queries shows differentr result according to above conditions :
departments table:
employees table:
https://www.w3resource.com/mysql/employees.phpmysql> SELECT first_name FROM employees WHERE ROW(department_id, manager_id) = (SELECT department_id, manager_id FROM departments WHERE location_id = 1800); +————+ | first_name | +————+ | Pat | +————+ 1 row in set (0.00 sec)
Code:
mysql>SELECT first_name
FROM employees
WHERE ROW(department_id, manager_id) = (SELECT department_id, manager_id FROM departments WHERE location_id = 2800);
Empty set (0.00 sec)
Copy
Code:
mysql>SELECT first_name
FROM employees
WHERE ROW(department_id, manager_id) = (SELECT department_id, manager_id FROM departments WHERE location_id = 1700);
ERROR 1242 (21000): Subquery returns more than 1 row
Copy
MySQL Subqueries with EXISTS or NOT EXISTS
The EXISTS operator tests for the existence of rows in the results set of the subquery. If a subquery row value is found, EXISTS subquery is TRUE and in this case NOT EXISTS subquery is FALSE.
Syntax:SELECT column1 FROM table1 WHERE EXISTS (SELECT * FROM table2);
In the above statement, if table2 contains any rows, even rows with NULL values, the EXISTS condition is TRUE. Generally, an EXISTS subquery starts with SELECT *, but it could begin with SELECT ‘X’, SELECT 5, or SELECT column1 or anything at all. MySQL ignores the SELECT list in such a subquery, so it makes no difference.
Example: MySQL Subqueries with EXISTS
From the following tables (employees) find employees (employee_id, first_name, last_name, job_id, department_id) who have at least one person reporting to them.
employees table:
https://www.smart.com/mysql/employees.phpSELECT employee_id, first_name, last_name, job_id, department_id FROM employees E WHERE EXISTS (SELECT * FROM employees WHERE manager_id = E.employee_id); +————-+————+———–+———+—————+ | employee_id | first_name | last_name | job_id | department_id | +————-+————+———–+———+—————+ | 100 | Steven | King | AD_PRES | 90 | | 101 | Neena | Kochhar | AD_VP | 90 | | 102 | Lex | De Haan | AD_VP | 90 | | 103 | Alexander | Hunold | IT_PROG | 60 | | 108 | Nancy | Greenberg | FI_MGR | 100 | | 114 | Den | Raphaely | PU_MAN | 30 | | 120 | Matthew | Weiss | ST_MAN | 50 | | 121 | Adam | Fripp | ST_MAN | 50 | | ———- | ———- | ——— | ——- | ————- | +————-+————+———–+———+—————+ 18 rows in set (0.02 sec)
Example: MySQL Subqueries with NOT EXISTS
NOT EXISTS subquery almost always contains correlations. Here is an example : From the following table (departments and employees) find all departments (department_id, department_name) that do not have any employees.
departments table:
employees table:
https://www.smart.com/mysql/employees.phpmysql> SELECT department_id, department_name FROM departments d WHERE NOT EXISTS (SELECT * FROM employees WHERE department_id = d.department_id); +—————+———————-+ | department_id | department_name | +—————+———————-+ | 120 | Treasury | | 130 | Corporate Tax | | 140 | Control And Credit | | 150 | Shareholder Services | | 160 | Benefits | | 170 | Manufacturing | | 180 | Construction | | 190 | Contracting | | 200 | Operations | | ———— | ——————– | +—————+———————-+ 16 rows in set (0.00 sec)
MySQL Correlated Subqueries
A correlated subquery is a subquery that contains a reference to a table (in the parent query) that also appears in the outer query. MySQL evaluates from inside to outside.
Correlated subquery syntax:
Example – 1: MySQL Correlated Subqueries
Following query find all employees who earn more than the average salary in their department.
From the employees and job_history tables display details of those employees who have changed jobs at least once.
employees table:
job_history table:
https://www.smart.com/mysql/job_history.phpmysql> SELECT first_name, last_name, employee_id, job_id FROM employees E WHERE 1 <= (SELECT COUNT(*) FROM Job_history WHERE employee_id = E.employee_id); +————+———–+————-+———+ | first_name | last_name | employee_id | job_id | +————+———–+————-+———+ | Neena | Kochhar | 101 | AD_VP | | Lex | De Haan | 102 | AD_VP | | Den | Raphaely | 114 | PU_MAN | | Payam | Kaufling | 122 | ST_MAN | | Jonathon | Taylor | 176 | SA_REP | | Jennifer | Whalen | 200 | AD_ASST | | Michael | Hartstein | 201 | MK_MAN | +————+———–+————-+———+ 7 rows in set (0.00 sec)
MySQL Subqueries in the FROM Clause
Subqueries work in a SELECT statement’s FROM clause. The syntax is :SELECT … FROM (subquery) [AS] name …
Every table in a FROM clause must have a name, therefore the [AS] name clause is mandatory. Any columns in the subquery select list must have unique names.
Example: MySQL Subqueries in the FROM Clause
We have the following table tb1.mysql> CREATE TABLE tb1 (c1 INT, c2 CHAR(5), c3 FLOAT); Query OK, 0 rows affected (0.73 sec)
Here is how to use a subquery in the FROM clause, using the example table (tb1) :mysql> SELECT sc1, sc2, sc3 FROM (SELECT c1 AS sc1, c2 AS sc2, c3*3 AS sc3 FROM tb1) AS sb WHERE sc1 > 1; +——+——+——+ | sc1 | sc2 | sc3 | +——+——+——+ | 2 | 2 | 6 | | 3 | 3 | 9 | +——+——+——+ 2 rows in set (0.02 sec)
A join enables you to retrieve records from two (or more) logically related tables in a single result set.
JOIN clauses are used to return the rows of two or more queries using two or more tables that shares a meaningful relationship based on a common set of values.
These values are usually the same column name and datatype that appear in both the participating tables being joined. These columns, or possibly a single column from each table, are called the join key or common key.
Mostly but not all of the time, the join key is the primary key of one table and a foreign key in another table. The join can be performed as long as the data in the columns are matching.
It can be difficult when the join involving more than two tables. It is a good practice to think of the query as a series of two table joins when the involvement of three or more tables in joins.
MySQL JOIN Syntax:
MySQL supports the following JOIN syntaxes for the table_references (A table reference is also known as a join expression.) part of SELECT statements and multiple-table UPDATE and DELETE statements :table_references: escaped_table_reference [, escaped_table_reference] … escaped_table_reference: table_reference | { OJ table_reference } table_reference: table_factor | join_table table_factor: tbl_name [PARTITION (partition_names)] [[AS] alias] [index_hint_list] | table_subquery [AS] alias | ( table_references ) join_table: table_reference [INNER | CROSS] JOIN table_factor [join_condition] | table_reference STRAIGHT_JOIN table_factor | table_reference STRAIGHT_JOIN table_factor ON conditional_expr | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition | table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor join_condition: ON conditional_expr | USING (column_list) index_hint_list: index_hint [, index_hint] … index_hint: USE {INDEX|KEY} [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list]) | IGNORE {INDEX|KEY} [FOR {JOIN|ORDER BY|GROUP BY}] (index_list) | FORCE {INDEX|KEY} [FOR {JOIN|ORDER BY|GROUP BY}] (index_list) index_list: index_name [, index_name] …
Types of MySQL Joins :
INNER JOIN
LEFT JOIN
RIGHT JOIN
STRAIGHT JOIN
CROSS JOIN
NATURAL JOIN
Here is the sample tables table_A and table_B, which we have used to explain the technologies behind the joins.
MySQL INNER JOIN
The INNER JOIN is such a JOIN in which all rows can be selected from both participating tables as long as there is a match between the columns. Usage of INNER JOIN combines the tables. An INNER JOIN allows rows from either table to appear in the result if and only if both tables meet the conditions specified in the ON clause.
Example
Code:
SELECT * FROM table_A
INNER JOIN table_B
ON table_A.A=table_B.A;
Copy
Relational Algebra Expression:
Relational Algebra Tree:
MySQL LEFT JOIN
The LEFT JOIN is such a join which specifies that all records be fetched from the table on the left side of the join statement. If a record returned from the left table has no matching record in the table on the right side of the join, it is still returned, and the corresponding column from the right table returns a NULL value.
Example
Code:
SELECT * FROM table_A
LEFT JOIN table_B
ON table_A.A=table_B.A;
Copy
MySQL RIGHT JOIN
The RIGHT JOIN is such a join which specifies that all records be fetched from the table on the right side of the join statement, even if the table on the left has no matching record. In this case, the columns from the left table return NULL values.
Example
Code:
SELECT * FROM table_A
RIGHT JOIN table_B
ON table_A.A=table_B.A;
Copy
MySQL STRAIGHT JOIN
An STRAIGHT_JOIN is such a join which scans and combines matching rows ( if specified any condition) which are stored in associated tables other wise it behaves like an INNER JOIN or JOIN of without any condition.
Example
Code:
SELECT * FROM table_A
STRAIGHT JOIN table_B;
Copy
MySQL CROSS JOIN
A CROSS JOIN is such a join which specifies the complete cross product of two tables. For each record in the first table, all the records in the second table are joined, creating a potentially huge result set. This command has the same effect as leaving off the join condition, and its result set is also known as a Cartesian product.
Example
Code:
SELECT * FROM table_A
CROSS JOIN table_B;
Copy
MySQL NATURAL JOIN
A NATURAL JOIN is such a join that performs the same task as an INNER or LEFT JOIN, in which the ON or USING clause refers to all columns that the tables to be joined have in common.
Example
Code:
SELECT * FROM table_A
NATURAL JOIN table_B;
Copy
Relational Algebra Expression:
Relational Algebra Tree:
Key points to remember
Click on the following to get the slides presentation –
There are many occasions when you need to find duplicate values available in a column of a MySql table. Often, you may want to count the number of duplicate values in a MySQL table.
In this article, we have discussed a query where you can find duplicates, triplicates, quadruplicates (or more) data from a MySQL table.
We have discussed how to find duplicate values with INNER JOIN and subquery, INNER JOIN and DISTINCT, and also how to count duplicate values with GROUP BY and HAVING.
Table in question
We have used a table called ‘item’ to apply the query : Table Name: item Structure: item_code varchar(20), value int(11), quantity int(11) where item_code is the primary key.
Using INNER JOIN and Subquery
Now we want to get the details of those records where quantity field have duplicate/triplicates values. In the image above, values marked with red rectangle exist more than once.
Here is the query:
Code:
SELECT item_code, value, item.quantity
FROM item
INNER JOIN(
SELECT quantity
FROM item
GROUP BY quantity
HAVING COUNT(item_code) >1
)temp ON item.quantity= temp.quantity;
Copy
Relational Algebra Expression:
Relational Algebra Tree:
Sample Output:
To get the above result we have used a query with an INNER JOIN (INNER JOIN selects all rows from both participating tables as long as there is a match between the columns.) statement. INNER JOIN uses the main table ‘item’ and a temporary table ‘temp’ whose data comes from a subquery. Here is the subquery and it’s output:
Code:
SELECT quantity
FROM item
GROUP BY quantity
HAVING COUNT(item_code) >1
Copy
Relational Algebra Expression:
Relational Algebra Tree:
Sample Output:
Now the following main query will execute on ‘item’ and ‘temp’ tables where the common field is quantity and the result will be as follows:
Code:
SELECT item_code, value, item.quantity
FROM item
INNER JOIN temp ON item.quantity= temp.quantity;
Copy
Relational Algebra Expression:
Relational Algebra Tree:
Using INNER JOIN and DISTINCT
You can use the following query to get the same result. Here we apply INNER JOIN the table with itself. As the same quantity value exists in more than two records, a DISTINCT clause is used.
Here is the code and the output :
Code:
SELECT distinct a.item_code, a.value, a.quantity
FROM item a
INNER JOIN item b ON a.quantity = b.quantity
WHERE a.item_code <> b.item_code
Copy
Relational Algebra Expression:
Relational Algebra Tree:
Sample Output:
Count duplicate data in MySQL
The following query count those records where quantity field holds duplicate/triplicates (or more) data.
Table data:
Code:
SELECT item_code, COUNT( quantity ) x
FROM item
GROUP BY quantity
HAVING x >1
Copy
Sample Output:
Count duplicate records in MySQL
To count the total duplicate (or more) ‘quantity’ of ‘item’ table you can use the following query:
Code:
SELECT count(*) AS Total_duplicate_count
FROM
(SELECT item_code FROM item
GROUP BY quantity HAVING COUNT(quantity) > 1
)AS x
In the following section, we have given a list of FUNCTIONS and OPERATORS in MySQL as anyone can view the FUNCTIONS at a glance and jump immediately to his/her requirement.
MySQL logical AND operator compares two expressions and returns true if both of the expressions are true. Some Logical Operators are – And operatorNot operatorOr operatorXor operator
MySQL Control Flow Functions
Some Control Flow Functions are – Case operatorIF()IFNULL()NULLIF()
Some Information Functions in MySQL are – BENCHMARK()CHARSET()COERCIBILITY()COLLATION()CONNECTION_ID()CURRENT_USER(), CURRENT_USERDATABASE()FOUND_ROWS()LAST_INSERT_ID()SCHEMA()SESSION_USER()SYSTEM_USER()USER()VERSION()
In MySQL, the UNION operator is used to combine the result from multiple SELECT statements into a single result set.
The default characteristic of UNION is, to remove the duplicate rows from the result. The DISTINCT keyword which is optional does not make any effect, because, by default, it specifies duplicate-row removal. But if we use the optional keyword ALL, the duplicate-row removal does not happen and the result set includes all matching rows from all the SELECT statements.
Here in the above picture shows, only the employee_id and job_id surrounded the red rectangle are same in employees and job_history table, so it comes once in the output but the other employee_id and job_id are different in both the tables, so they come each.
MySQL UNION ALL
The UNION ALL operator does not eliminate duplicate selected rows and returns all rows.
Pictorial presentation of UNION ALL operator
The UNION ALL operator returns all the rows from both the queries and no duplication elimination happens.
MySQL UNION vs UNION ALL
In MySQL the UNION operator returns the combined result from multiple SELECT statements into a single result set but exclude the duplicate rows where as the UNION ALL operator avoids the elimination of duplicate selected rows and returns all rows.
See the example below.
Example
If we want to display the present and previous details of jobs of all employees, and they may appear more than once, the following MySQL statement can be used.
Select employee_id, job_id,department_id
FROM employees
UNION ALL
Select employee_id,job_id,department_id
FROM job_history;
Here in the above picture shows, only the employee_id and job_id surrounded the red rectangle are same in employees and job_history table and they all have appeared in the result set. Here UNION ALL have to avoid the elimination of duplicate rows.
MySQL UNION DISTINCTThe DISTINCT clause with UNION produced nothing extra as the simple UNION done. From the UNION operator, we know that all rows will be displayed from both the queries except the duplicate are once.
Example
If we want to display the present and previous details of jobs of all employees once the following MySQL statement can be used.
SELECT DISTINCT employee_id, job_id
FROM employees
UNION
SELECT DISTINCT employee_id,job_id
FROM job_history;
Copy
Output :
The result will be same as the UNION operator do.
MySQL UNION ORDER BYThe ORDER BY clause with UNION arrange the rows in the result set in a specific order. The default order is ascending. The ORDER BY only used at the very end of the statement.
Example
If we want to display the present and previous details of jobs of all employees once the following MySQL statement can be used.
SELECT employee_id, job_id
FROM employees
UNION
SELECT employee_id,job_id
FROM job_history
ORDER BY employee_id;
SELECT statement is used to fetch rows or records from one or more tables.
* , ALL
Indicating all columns.
column
Columns or list of columns.
table
Indicates the name of the table from where the rows will be retrieved.
DISTINCT
DISTINCT clause is used to retrieve unique rows from a table.
DISTINCTROW
DISTINCTROW is a synonym for DISTINCT.
HIGH_PRIORITY
If used with SELECT, the associated query obtains higher priority than an update statement. It runs even if the table on which the query is applied is locked by an update query. It can be used only with MyISAM, MEMORY, and MERGE storage engines. And can be used only if the associated query is very fast as well as done at once. If a SELECT statements are part of a UNION, you can not use HIGH_PRIORITY along with.
STRAIGHT_JOIN
If used with SELECT, associated tables are joined in the order they are arranged in the corresponding FROM clause. This can not be used to make a query fast and also can be used in the table_references list.
SQL_SMALL_RESULT
Can be used with GROUP BY or DISTINCT. It tells the optimizer (a component of DataBase system which determines to the most efficient way of executing a query) that the result set is small, so use fast temporary tables to store the resulting table instead of using sorting.
SQL_BIG_RESULT
Can be used with GROUP BY or DISTINCT. It tells the optimizer (a component of DataBase system which determines to the most efficient way of executing a query) that the result set has many rows, so use disk-based temporary tables if needed, and prefer sorting to use a temporary table with a key on the GROUP BY elements.
SQL_BUFFER_RESULT
It forces the result to be put into a temporary table. If used, MySQL can free the table locks early and takes less time to send the result set to the client where it may take a long time. It can not be used for subqueries or following UNION.
SQL_CACHE
It tells MySQL to store the result in the query cache (A DataBase system component which stores the text of a SELECT statement along with the corresponding result sent to the client ) if it is cacheable. The value of the query_cache_type system variable is 2 or DEMAND.
SQL_NO_CACHE
It tells MySQL not to store the result in the query cache (A DataBase system component which stores the text of a SELECT statement along with the corresponding result sent to the client ). It can also be used for views if it accompanies a SELECT statement.
SQL_CALC_FOUND_ROWS
It tells MySQL to calculate the number of rows in a result set. While calculating rows in this fashion, LIMIT clause is ignored. The number of rows can then be retrieved with SELECT FOUND_ROWS().
select_expr
An expression.
FROM
This clause is used after SELECT and preceding tables or subqueries.
table_references
Name of the tables used in a SELECT statement.
WHERE
The conditions are supplied after this keyword (in a select statement).
where_condition
Conditions placed after WHERE.
GROUP BY
If used, the results returned from the field name used after GROUP BY clause is grouped together in result set.
col_name
Name of the column or columns or fields.
expr
An expression.
position
Refers to the position of columns beginning with 1.
ASC
If used, results are returned in ascending order.
DESC
If used, results are returned in descending order.
WITH ROLLUP
This modifier can be used with GROUP BY clause. If used, extra rows are added to the summary output.
HAVING
This modifier can be used with GROUP BY clause and aggregate functions. Can not be used with WHERE clause.
ORDER BY
MySQL ORDER BY clause specifies the order in which columns are sorted while retrieving data in a SELECT statement.
LIMIT
It is always followed by one or two numeric arguments. The first argument is the offset of the first row to be returned and the second argument is the maximum number of rows returned in the result set.
PROCEDURE
This clause names a procedure that should process the data in the result set.
INTO OUTFILE
Takes a backup of the associated table in a file specified.
INTO DUMPFILE
If used instead of INTO OUTFILE, only one row is written into the file. Useful if BLOB values are to be stored in a file.
var_name
A variable name to store data temporarily.
FOR UPDATE
If used with a storage engine that uses page or row locks, untill the end of the transaction, rows checked by the query are write-locked.
LOCK IN SHARE MODE
If used, a shared lock is set upon the rows examined by the associated query. So, other transactions can read the examined rows, can not update or delete those rows.
Following clauses are commonly used in SELECT statements:
Each select_expr indicates a column that you want to retrieve. There must be at least one select_expr.
table_references indicates the table or tables from which to retrieve rows.
The WHERE clause, if given, indicates the condition or conditions that rows must satisfy to be selected. where_condition is an expression that evaluates to true for each row to be selected. The statement selects all rows if there is no WHERE clause (i.e without any condition).
In the WHERE expression, you can use any of the functions and operators that MySQL supports, except for aggregate functions.
SELECT can also be used to retrieve rows computed without reference to any table.
For example:mysql> SELECT 5 – 3 ; +——-+ | 5 – 3 | +——-+ | 2 | +——-+ 1 row in set (0.00 sec)
You can specify DUAL as a dummy table name where no tables are referenced :
mysql> SELECT 5 – 3 FROM DUAL; +——-+ | 5 – 3 | +——-+ | 2 | +——-+ 1 row in set (0.00 sec)
DUAL is purely for the convenience of people who require that all SELECT statements should have FROM and possibly other clauses. MySQL may ignore the clauses. MySQL does not require FROM DUAL if no tables are referenced.
In general, clauses used must be given in exactly the order shown in the syntax description. For example, a HAVING clause must come after any GROUP BY clause and before any ORDER BY clause. In the consequent pages of w3resource MySQL tutorial, you will find detail discussion accompanied by examples (often with PHP codes) of the said arguments of the SELECT statement.
MySQL selecting all data
MySQL SELECT statement without any condition retrieves all records from a table. The following SELECT statement will retrieve all data from publisher table.
SELECT *
FROM publisher;
Copy
Table: publisher
Output:mysql> SELECT * FROM publisher; +——–+——————————+———–+———–+—————-+————–+————+ | pub_id | pub_name | pub_city | country | country_office | no_of_branch | estd | +——–+——————————+———–+———–+—————-+————–+————+ | P001 | Jex Max Publication | New York | USA | New York | 15 | 1969-12-25 | | P002 | BPP Publication | Mumbai | India | New Delhi | 10 | 1985-10-01 | | P003 | New Harrold Publication | Adelaide | Australia | Sydney | 6 | 1975-09-05 | | P004 | Ultra Press Inc. | London | UK | London | 8 | 1948-07-10 | | P005 | Mountain Publication | Houstan | USA | Sun Diego | 25 | 1975-01-01 | | P006 | Summer Night Publication | New York | USA | Atlanta | 10 | 1990-12-10 | | P007 | Pieterson Grp. of Publishers | Cambridge | UK | London | 6 | 1950-07-15 | | P008 | Novel Publisher Ltd. | New Delhi | India | Bangalore | 10 | 2000-01-01 | +——–+——————————+———–+———–+—————-+————–+————+ 8 rows in set (0.00 sec)
When a user wants to retrieve some individual rows from a table, a WHERE clause has to be added with the SELECT statement immediately followed by a condition.
Syntax:SELECT * FROM WHERE where_condition
Here * indicates all columns.
Example:
This following SELECT statement will retrieve those particular rows where ‘country’ is the USA.
Code :SELECT * FROM publisher WHERE country=’USA’
Output:> mysql> SELECT * FROM publisher -> WHERE country=’USA’; +——–+————————–+———-+———+—————-+————–+————+ | pub_id | pub_name | pub_city | country | country_office | no_of_branch | estd | +——–+————————–+———-+———+—————-+————–+————+ | P001 | Jex Max Publication | New York | USA | New York | 15 | 1969-12-25 | | P005 | Mountain Publication | Houstan | USA | Sun Diego | 25 | 1975-01-01 | | P006 | Summer Night Publication | New York | USA | Atlanta | 10 | 1990-12-10 | +——–+————————–+———-+———+—————-+————–+————+ 3 rows in set (0.01 sec)
MySQL SELECT specific rows with AND operator
MySQL AND operator is used to combine more than one conditions aiming to fetch records when both of the conditions are satisfied. The following SELECT statement will retrieve those particular rows where country and city of the publisher are ‘USA’ and ‘New York’.
Code:
SELECT *
FROM publisher
WHERE country='USA'
AND pub_city='New York';
Copy
Output:mysql> SELECT * FROM publisher -> WHERE country=’USA’ -> AND pub_city=’New York’; +——–+————————–+———-+———+—————-+————–+————+ | pub_id | pub_name | pub_city | country | country_office | no_of_branch | estd | +——–+————————–+———-+———+—————-+————–+————+ | P001 | Jex Max Publication | New York | USA | New York | 15 | 1969-12-25 | | P006 | Summer Night Publication | New York | USA | Atlanta | 10 | 1990-12-10 | +——–+————————–+———-+———+—————-+————–+————+ 2 rows in set (0.00 sec)
MySQL SELECT specific columns
To retrieve records from specific columns, you need to specify a list of comma separated columns. The following MySQL statement returns the name of the book, author id, and price of the books from book_mast table.
Table: book_mast
Code:
SELECT book_name,aut_id,book_price
FROM book_mast;
Copy
Output:mysql> SELECT book_name,aut_id,book_price -> FROM book_mast; +————————————-+——–+————+ | book_name | aut_id | book_price | +————————————-+——–+————+ | Introduction to Electrodynamics | AUT001 | 85.00 | | Understanding of Steel Construction | AUT002 | 105.50 | | Guide to Networking | AUT003 | 200.00 | | Transfer of Heat and Mass | AUT004 | 250.00 | | Conceptual Physics | AUT005 | 145.00 | | Fundamentals of Heat | AUT006 | 112.00 | | Advanced 3d Graphics | AUT007 | 56.00 | | Human Anatomy | AUT008 | 50.50 | | Mental Health Nursing | AUT009 | 145.00 | | Fundamentals of Thermodynamics | AUT010 | 225.00 | | The Experimental Analysis of Cat | AUT011 | 95.00 | | The Nature of World | AUT005 | 88.00 | | Environment a Sustainable Future | AUT012 | 100.00 | | Concepts in Health | AUT013 | 180.00 | | Anatomy & Physiology | AUT014 | 135.00 | | Networks and Telecommunications | AUT015 | 45.00 | +————————————-+——–+————+ 16 rows in set (0.01 sec)
MySQL select specific columns with distinct operator
DISTINCT clause is used to retrieve unique rows from a table. The following MySQL statement retrieves the unique author ids from book_mast table.
Note : If you look at the book_mast table shown under Sample table : book_mast heading, you would find that AUT005 (in ‘aut_id’ column) has appeared more than once. Using DISTINCT clause, we got rid of this redundancy.
MySQL SELECT specific columns with logical OR operator
OR operator retrieves records from a table if at least one of the given conditions is satisfied. The following MySQL statement retrieves records of pub_name, country, pub_city columns from publisher table if either Country (i.e. country) of the publisher is ‘USA’ or his city (i.e. pub_city) is ‘New York’.
Code:
SELECT pub_name, country,pub_city
FROM publisher
WHERE country='USA' OR pub_city='New York';
Copy
Output:mysql> SELECT pub_name,country,pub_city -> FROM publisher -> WHERE country=’USA’ OR pub_city=’New York’; +————————–+———+———-+ | pub_name | country | pub_city | +————————–+———+———-+ | Jex Max Publication | USA | New York | | Mountain Publication | USA | Houstan | | Summer Night Publication | USA | New York | +————————–+———+———-+ 3 rows in set (0.00 sec)
MySQL sorting rows in ascending order
MySQL ORDER BY clause specifies the order in which columns are sorted while retrieving data in a SELECT statement. By default, columns are sorted in ascending order. You can use ASC keyword to achieve the same result.
Note : In the case of character type column sorting the sorting is dependent upon case sensitivity. The default sort order is ascending this means smallest value comes first. To sort in reverse order, DESC key has to be used.
In the following MySQL statement, all records of pub_name, country and pub_city columns of publisher table are being fetched and sorted against pub_name column. Since we have not specified any order keyword (ASC or DESC), by default, it is sorted in ascending order.
Code:
SELECT pub_name, country,pub_city
FROM publisher
ORDER BY pub_name;
Copy
Output:mysql> SELECT pub_name,country,pub_city -> FROM publisher -> ORDER BY pub_name; +——————————+———–+———–+ | pub_name | country | pub_city | +——————————+———–+———–+ | BPP Publication | India | Mumbai | | Jex Max Publication | USA | New York | | Mountain Publication | USA | Houstan | | New Harrold Publication | Australia | Adelaide | | Novel Publisher Ltd. | India | New Delhi | | Pieterson Grp. of Publishers | UK | Cambridge | | Summer Night Publication | USA | New York | | Ultra Press Inc. | UK | London | +——————————+———–+———–+ 8 rows in set (0.02 sec)
MySQL sorting rows in descending order
The following MySQL statement sort rows of a table in descending order using ORDER BY clause.
Code:
SELECT pub_name, country,pub_city
FROM publisher
ORDER BY pub_name DESC;
Copy
Output:mysql> SELECT pub_name,country,pub_city -> FROM publisher -> ORDER BY pub_name DESC; +——————————+———–+———–+ | pub_name | country | pub_city | +——————————+———–+———–+ | Ultra Press Inc. | UK | London | | Summer Night Publication | USA | New York | | Pieterson Grp. of Publishers | UK | Cambridge | | Novel Publisher Ltd. | India | New Delhi | | New Harrold Publication | Australia | Adelaide | | Mountain Publication | USA | Houstan | | Jex Max Publication | USA | New York | | BPP Publication | India | Mumbai | +——————————+———–+———–+ 8 rows in set (0.00 sec)
MySQL sorting rows on multiple columns
Sort can be performed on multiple columns. The way this type of sort happen is, firstly the rows will be sorted on the first column then the rows will be sorted on the second column whose first column’s data are same.
In the following MySQL statement, all records of pub_name, country and pub_city columns of publisher table are being fetched and sorted against country and pub_city columns. Since we have not specified any order keyword (ASC or DESC), by default, it is sorted in ascending order.
Code:
SELECT pub_name, country,pub_city
FROM publisher
ORDER BY country,pub_city;
Copy
Output :mysql> SELECT pub_name, country, pub_city -> FROM publisher -> ORDER BY country, pub_city; +——————————+———–+———–+ | pub_name | country | pub_city | +——————————+———–+———–+ | New Harrold Publication | Australia | Adelaide | | BPP Publication | India | Mumbai | | Novel Publisher Ltd. | India | New Delhi | | Pieterson Grp. of Publishers | UK | Cambridge | | Ultra Press Inc. | UK | London | | Mountain Publication | USA | Houstan | | Jex Max Publication | USA | New York | | Summer Night Publication | USA | New York | +——————————+———–+———–+ 8 rows in set (0.00 sec)
MySQL select with NULL value
IS NULL, IS NOT NULL is used to select or test if a value stored in a table is NULL. While writing a MySQL statement, NULL keyword is used to specify a null value.
What is NULL value ?
A value of NULL says that the value is unknown, not applicable or will be added later.
A value of NULL is not an empty or zero value.
No two null values are equal.
Since a value of NULL is unknown, comparisons between two null values, or between a NULL and any other value, returns unknown.
