In MySQL, DROP TABLE command removes one or more tables from an existing database.
The user who is using the DROP command, must have DROP privilege for each table(s) he wants to drop. The command removes all the data and table definition from the database.
The IF EXIST optional clause can be used to avoid the error message occurred, when a specified table in the table list passed as argument, does not exist in the database.
RESTRICT and CASCADE
The RESTRICT and CASCADE options can be used to make porting easier.
TEMPORARY
This clause with statement drops only TEMPORARY tables. A TEMPORARY table is visible only to the session that created by the user. Using TEMPORARY is a good practice to ensure that accidentally you do not drop such a table which is important to you.
Example:
If you want to drop the table newauthor, the following sql can be used.
DROP TABLE IF EXISTS newauthor;
Copy
The above MySQL statement above will remove the ‘newauthor’ table with all data from the existing database.
MySQL DROP all TABLES or MySQL DROP DATABASE
If you want to drop all the tables from a database you need to use DROP DATABASE sql command to drops all tables in the database or empty the database. But you need to be very careful with this statement, and to use DROP DATABASE, you need the DROP privilege on the database
The clause IF EXISTS is used to prevent the display of an error message when the specific database does not exist which is going to be dropped.
The DROP DATABASE statement removes those files and directories that created by MySQL itself during the creation of database. The extension of files are – .BAK, .DAT, .HSH, .MRG, .MYD, .MYI, .TRG, .TRN, .db, .frm, .ibd, .ndb and .par .
Here are our databases.mysql> SHOW DATABASES; +——————–+ | Database | +——————–+ | information_schema | | bmcnetbank | | bupf | | employee | | empoloyee | | mucemppf | | mysql | | performance_schema | | sakila | | tempdatabase | | test | | world | +——————–+ 12 rows in set (0.00 sec)
We want to drop all the tables from the database tempdatabase.
Here the tables of the database tempdatabase.mysql> SHOW TABLES; +————————+ | Tables_in_tempdatabase | +————————+ | table1 | | table2 | +————————+ 2 rows in set (0.00 sec)
Now you can DROP all the tables in a database as well as the database. You can recreate the database again. Before you drop the database, start the MySQL server, then go the command prompt, then entering the password connect the server, in the following ‘mysql>’ prompt enter the statement
After DROP the database here is the remaining databases.mysql> SHOW DATABASES; +——————–+ | Database | +——————–+ | information_schema | | bmcnetbank | | bupf | | employee | | empoloyee | | mucemppf | | mysql | | performance_schema | | sakila | | test | | world | +——————–+ 12 rows in set (0.00 sec)
MySQL DROP multiple TABLES
Here the tables of the database tempdatabase.mysql> SHOW tables; +————————+ | Tables_in_tempdatabase | +————————+ | table1 | | table2 | | table3 | | table4 | | table5 | +————————+ 5 rows in set (0.10 sec)
If you want to delete the table table2,table4 and table5 from the database tempdatabase, the following sql can be used.mysql> DROP TABLE table2,table4,table5; Query OK, 0 rows affected (0.24 sec) mysql> SHOW TABLES; +————————+ | Tables_in_tempdatabase | +————————+ | table1 | | table3 | +————————+ 2 rows in set (0.00 sec)
MySQL DROP column
If MySQL ALTER command is used with DROP following the table column name, that column will be deleted from the table.
Example:
If we want to remove cate_descrip column from newcate table, the following statement can be used.
If you want to remove the column col1, col11 and col12 from the table table1, the following sql statement can be used.mysql> ALTER TABLE table1 drop col1, drop col11, drop col12; Query OK, 0 rows affected (0.20 sec) Records: 0 Duplicates: 0 Warnings: 0
MySQL DROP VIEW command is used to drop a view. You must have the DROP privilege for each view. If the view name specified into the DROP VIEW statement does not exists MySQL returns an error indicating by name.
The clause IF EXISTS prevents to display an error message from the operation happening for views does not exist.
RESTRICT, CASCADE
RESTRICT and CASCADE, if given, are parsed and ignored.
If you want to drop the view view_bookmast, the following sql statement can be used.DROP VIEW view_bookmast;
MySQL DROP INDEX
MySQL DROP INDEX command removes an index from a table. This statement is mapped to an ALTER TABLE statement to drop the index.
Syntax:DROP INDEX index_name ON table_name [algorithm_option | lock_option] … algorithm_option: ALGORITHM [=] {DEFAULT|INPLACE|COPY} lock_option: LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
Arguments:
Name
Description
index name
Index to be removed.
table name
Table the index belongs to.
ALGORITHM [=] {DEFAULT|INPLACE|COPY}
DEFAULT – You needed this when you are applying ALTER TABLE in earlier versions of MySQL (< 5.6) while altering a table online. This method was used to use a temporary table while altering a table. The ALGORITHM=INPLACE continue the operation inside the InnoDB storage engines by using the in-place technique, and fail which are not support this features with an error. COPY – is the same a specifying no ALGORITHM clause at all.
LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
DEFAULT – Permit a series of coincident events i.e. reads and writes when supported. Otherwise permit concurrent reads when supported else enforce exclusive access. NONE – When supported, permit concurrent reads and writes else return an error message. SHARED – When supported, allow concurrent reads but restrict writes. Remember that writes will be blocked even if concurrent writes are supported by the storage engine for the given ALGORITHM clause (if any) and ALTER TABLE operation. When concurrent reads are not supported an error message will be returned. EXCLUSIVE – This enforce exclusive access. It happens even if concurrent reads/writes are supported by the storage engine for the given ALGORITHM clause (if any) and ALTER TABLE operation.
Example:
If you want to drop the index newautid of newauthor table, the following sql statement can be used.
If you want to drop the indexes index_col2 and index_col5, the following sql statement can be used.mysql> ALTER TABLE table1 DROP INDEX index_col2, DROP INDEX index_col5; Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0
After drop the indexes from the table table1, here is the indexes for the table1mysql> show index from table1; +——–+————+————–+————–+————-+———–+————-+———-+——–+——+————+———+—————+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +——–+————+————–+————–+————-+———–+————-+———-+——–+——+————+———+—————+ | table1 | 1 | index_col7 | 1 | col7 | A | 0 | NULL | NULL | YES | BTREE | | | | table1 | 1 | index_col910 | 1 | col9 | A | 0 | NULL | NULL | YES | BTREE | | | | table1 | 1 | index_col910 | 2 | col10 | A | 0 | NULL | NULL | YES | BTREE | | | +——–+————+————–+————–+————-+———–+————-+———-+——–+——+————+———+—————+ 3 rows in set (0.00 sec)
MySQL DROP FUNCTION , PROCEDURE
Click here to see DROP FUNCTION, PROCEDURE.
MySQL DROP TRIGGER
Click here to see DROP TRIGGER.
MySQL DROP EVENT
The DROP EVENT drops the event.
Here we are creating an event.mysql> CREATE EVENT testevent -> ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE -> DO -> UPDATE tempdatabase.table3 SET col1 = col1 + 1; Query OK, 0 rows affected (0.01 sec)
The above statement creates an event named testevent and it executes once, after one minute following its creation, by running an SQL statement it will increments the value of the column col1 of tempdatabse.table3 by 1.
Syntax :DROP EVENT [IF EXISTS] event_name
Arguments:
Name
Description
event_name
Name of the event
IF EXISTS
If the event does not exist, the error ERROR 1517 (HY000): Unknown event ‘event_name’ results will display. To prevent this error message for nonexistent events the IF EXISTS clause can be used.
I you want to remove the event testevent the following sql statement can be used.mysql> DROP EVENT IF EXISTS testevent; Query OK, 0 rows affected (0.00 sec)
MySQL DROP PRIMARY KEY
Here is the structure of the sample table.mysql> DESCRIBE table1; +——-+————-+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +——-+————-+——+—–+———+——-+ | col2 | varchar(15) | NO | PRI | | | | col4 | int(5) | YES | MUL | NULL | | | col3 | int(10) | YES | MUL | NULL | | | col5 | int(5) | YES | MUL | NULL | | | col6 | int(5) | YES | MUL | NULL | | | col7 | int(5) | YES | MUL | NULL | | | col8 | int(5) | YES | | NULL | | | col9 | int(10) | YES | MUL | NULL | | | col10 | int(5) | YES | | NULL | | +——-+————-+——+—–+———+——-+ 9 rows in set (0.01 sec)
Here in the above table structure of table1 shows col2 is the PRIMARY KEY, which is indicated by red color.
If you want to drop the PRIMARY KEY on col2, the following sql can be used.mysql> DROP INDEX PRIMARY ON table1; Query OK, 0 rows affected (0.81 sec) Records: 0 Duplicates: 0 Warnings: 0
Now look the structure of the table table1 again.mysql> describe table1; +——-+————-+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +——-+————-+——+—–+———+——-+ | col2 | varchar(15) | NO | MUL | | | | col4 | int(5) | YES | MUL | NULL | | | col3 | int(10) | YES | MUL | NULL | | | col5 | int(5) | YES | MUL | NULL | | | col6 | int(5) | YES | MUL | NULL | | | col7 | int(5) | YES | MUL | NULL | | | col8 | int(5) | YES | | NULL | | | col9 | int(10) | YES | MUL | NULL | | | col10 | int(5) | YES | | NULL | | +——-+————-+——+—–+———+——-+ 9 rows in set (0.01 sec)
There are various forms of MySQL SHOW commands, which provides information about databases, tables, columns, or status information about the commands. See the following section:
SHOW BINARY LOGS statement is used to list the binary log files on the server. Here is the syntax:SHOW BINARY LOGS
See the following examples:mysql> SHOW BINARY LOGS; +—————+———–+ | Log_name | File_size | +—————+———–+ | binlog.000015 | 724935 | | binlog.000016 | 733481 | +—————+———–+
MySQL: SHOW BINLOG EVENTS
SHOW BINLOG EVENTS statement shows the events in the binary log. Here is the syntax:SHOW BINLOG EVENTS [IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count]
If you omit ‘log_name’, the first binary log is displayed.
The LIMIT clause can be used to constrain the number of rows returned by the statement.
MySQL: SHOW CHARACTER SET
SHOW CHARACTER SET statement is used to check all available character sets. Here is the syntax :SHOW CHARACTER SET [LIKE ‘pattern’ | WHERE expr]
The optional LIKE clause, if present, shows the matched character set. With WHERE clause you can use a condition.
See the following examples :mysql> SHOW CHARACTER SET; +———-+—————————–+———————+——–+ | Charset | Description | Default collation | Maxlen | +———-+—————————–+———————+——–+ | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 | | dec8 | DEC West European | dec8_swedish_ci | 1 | | cp850 | DOS West European | cp850_general_ci | 1 | | hp8 | HP West European | hp8_english_ci | 1 | | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 | | latin1 | cp1252 West European | latin1_swedish_ci | 1 | | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 | | swe7 | 7bit Swedish | swe7_swedish_ci | 1 | | ascii | US ASCII | ascii_general_ci | 1 | | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 | | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 | | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 | …….. 40 rows in set (0.03 sec)
Let see all the character set starting with ‘utf”mysql> SHOW CHARACTER SET LIKE ‘utf%’; +———+——————+——————–+——–+ | Charset | Description | Default collation | Maxlen | +———+——————+——————–+——–+ | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 | | utf16 | UTF-16 Unicode | utf16_general_ci | 4 | | utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 | | utf32 | UTF-32 Unicode | utf32_general_ci | 4 | +———+——————+——————–+——–+ 5 rows in set (0.00 sec)
Let see all the character set starting with ‘utf” and Maxlen is 3:
https://googleads.g.doubleclick.net/pagead/ads?gdpr=0&us_privacy=1—&gpp_sid=-1&client=ca-pub-2153208817642134&output=html&h=280&adk=2413866252&adf=1810428289&pi=t.aa~a.4097345806~i.57~rp.4&w=715&fwrn=4&fwrnh=100&lmt=1706467649&num_ads=1&rafmt=1&armr=3&sem=mc&pwprc=5856759792&ad_type=text_image&format=715×280&url=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-show.php&fwr=0&pra=3&rh=179&rw=715&rpe=1&resp_fmts=3&wgl=1&fa=27&uach=WyJXaW5kb3dzIiwiMTAuMC4wIiwieDg2IiwiIiwiMTA2LjAuNDk5OC41MiIsbnVsbCwwLG51bGwsIjY0IixbWyJOb3RfQSBCcmFuZCIsIjguMC4wLjAiXSxbIkNocm9taXVtIiwiMTIwLjAuNjA5OS4yMzQiXSxbIk9wZXJhIiwiMTA2LjAuNDk5OC41MiJdXSwxXQ..&dt=1706467506716&bpp=13&bdt=4166&idt=14&shv=r20240122&mjsv=m202401240101&ptt=9&saldr=aa&abxe=1&cookie=ID%3D6de4a56fe4484587%3AT%3D1706354011%3ART%3D1706420688%3AS%3DALNI_MYWYkKy5gAvXyEH7W4ZN6WCTnP0sA&gpic=UID%3D00000d0223079700%3AT%3D1706354011%3ART%3D1706420688%3AS%3DALNI_MaBKEehqphuMfn0yJfqUi_NOyR70w&prev_fmts=468×80%2C304x250%2C300x600%2C300x600%2C0x0%2C715x280%2C1297x644&nras=4&correlator=4273584465680&frm=20&pv=1&ga_vid=1729879426.1706400821&ga_sid=1706467505&ga_hid=1814021088&ga_fc=1&u_tz=-480&u_his=18&u_h=768&u_w=1366&u_ah=728&u_aw=1366&u_cd=24&u_sd=1&dmc=8&adx=238&ady=577&biw=1312&bih=644&scr_x=0&scr_y=0&eid=44759876%2C44759927%2C44759837%2C44795922%2C31080663%2C95320377%2C95320888%2C95321627%2C95322163%2C95323009&oid=2&pvsid=825911308207069&tmod=925717838&uas=1&nvt=1&ref=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-security.php&fc=384&brdim=0%2C0%2C0%2C0%2C1366%2C0%2C1366%2C728%2C1312%2C644&vis=1&rsz=%7C%7Cs%7C&abl=NS&fu=128&bc=31&bz=1.04&psd=W251bGwsbnVsbCxudWxsLDNd&ifi=10&uci=a!a&fsb=1&dtd=Mmysql> SHOW CHARACTER SET WHERE Maxlen=3; +———+—————————+———————+——–+ | Charset | Description | Default collation | Maxlen | +———+—————————+———————+——–+ | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 | | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 | +———+—————————+———————+——–+ 3 rows in set (0.00 sec)
MySQL: SHOW COLLATION
SHOW COLLATION statement is used to list collations (a collation is a set of rules for comparing characters in a character set) supported by the server. Here is the syntax:SHOW COLLATION SET [LIKE ‘pattern’ | WHERE expr]
The optional LIKE clause, if present, shows the matched collations.
The SHOW COLUMNS statement is used to display information about the columns in a given table. Here is the syntax:SHOW [FULL] COLUMNS {FROM | IN} tbl_name [{FROM | IN} db_name] [LIKE ‘pattern’ | WHERE expr]
The optional LIKE clause, if present shows the matched column names. With WHERE clause you can use a condition.
See the following examples:mysql> SHOW COLUMNS FROM user_details; +————-+————–+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +————-+————–+——+—–+———+——-+ | userid | varchar(16) | NO | PRI | NULL | | | password | varchar(16) | NO | | NULL | | | fname | varchar(100) | NO | | NULL | | | lname | varchar(100) | NO | | NULL | | | gender | varchar(1) | NO | | NULL | | | dtob | date | NO | | NULL | | | country | varchar(30) | NO | | NULL | | | user_rating | int(4) | NO | | NULL | | | emailid | varchar(60) | NO | | NULL | | +————-+————–+——+—–+———+——-+ 9 rows in set (0.25 sec)
MySQL: SHOW CREATE DATABASE
SHOW CREATE DATABASE statement is used to show CREATE DATABASE statement.SHOW CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name See the following example:mysql> SHOW CREATE DATABASE hr; +———-+————————————————————-+ | Database | Create Database | +———-+————————————————————-+ | hr | CREATE DATABASE hr /*!40100 DEFAULT CHARACTER SET utf8 */ | +———-+————————————————————-+ 1 row in set (0.00 sec)
MySQL : SHOW CREATE EVENT
This statement displays the CREATE EVENT statement needed to re-create a given event. It requires the EVENT privilege for the database from which the event is to be shown.SHOW CREATE EVENT event_name
MySQL : SHOW CREATE FUNCTION
SHOW CREATE FUNCTION statement is used to get the exact string that can be used to re-create the named stored function.
Here is the syntax:SHOW CREATE FUNCTION func_name
Here is the statement to create a function ‘test1’mysql> CREATE FUNCTION test1 (aa CHAR(25)) -> RETURNS CHAR(50) DETERMINISTIC -> RETURN CONCAT(‘w3resource.’,aa,’ sites’); -> // Query OK, 0 rows affected (0.00 sec)
Here is the following statement of SHOW CREATE FUNCTION.SHOW CREATE FUNCTION test1\G
Here ‘\G’ statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
Let execute the above and see the output:
Sample Output:mysql> SHOW CREATE FUNCTION test1\G *************************** 1. row *************************** Function: test1 sql_mode: Create Function: CREATE DEFINER=root@localhost FUNCTION test1(aa CHAR(25)) RETURNS char(50) CHARSET latin1 DETERMINISTIC RETURN CONCAT(‘w3resource.’,aa,’ sites’) character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci 1 row in set (0.00 sec)
MySQL: SHOW CREATE PROCEDURE
SHOW CREATE PROCEDURE statement is used to get the exact string that can be used to re-create the named stored procedure. The statement requires that you must be the owner of the routine.
Here is the syntax:SHOW CREATE PROCEDURE proc_name
Here is the statement to create a procedure ‘myprocedure’mysql> CREATE PROCEDURE myprocedure (OUT emp_ctr INT) -> BEGIN -> SELECT COUNT(*) INTO emp_ctr FROM empinfo.employees; -> END// Query OK, 0 rows affected (0.00 sec)
Here is the following statement of SHOW CREATE PROCEDURE.SHOW CREATE PROCEDURE empinfo.myprocedure\G
Here ‘\G’ statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
Let execute the above and see the output:
Sample Output:mysql> SHOW CREATE PROCEDURE empinfo.myprocedure\G *************************** 1. row *************************** Procedure: myprocedure sql_mode: Create Procedure: CREATE DEFINER=root@localhost PROCEDURE myprocedure(OUT emp_ctr INT) BEGIN SELECT COUNT(*) INTO emp_ctr FROM empinfo.employees; END character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci 1 row in set (0.00 sec)
MySQL: SHOW CREATE TABLE
SHOW CREATE TABLE statement is used to show the create table statement.
Here is the syntax:SHOW CREATE TABLE table_name;
See the following example:SHOW CREATE TABLE regions\G;
Here ‘\G’ statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
Let execute the above and see the output:
Sample Output:mysql> SHOW CREATE TABLE regions\G *************************** 1. row *************************** Table: regions Create Table: CREATE TABLE regions ( REGION_ID decimal(5,0) NOT NULL, REGION_NAME varchar(25) DEFAULT NULL, PRIMARY KEY (REGION_ID) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec)
MySQL: SHOW CREATE TRIGGER
SHOW CREATE TRIGGERS statement is used to show the CREATE TRIGGER statement that creates the named trigger.
Here is the syntax:SHOW CREATE TRIGGER trigger_name
See the following example:mysql> SHOW CREATE TRIGGER ins_sum\G; *************************** 1. row *************************** Trigger: ins_sum sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION SQL Original Statement: CREATE DEFINER=root@::1 TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci 1 row in set (0.02 sec) ERROR: No query specified
MySQL: SHOW CREATE VIEW
SHOW CREATE VIEW statement is used to show the create view statement.
Here is the syntax :SHOW CREATE VIEW view_name;
See the following example:SHOW CREATE VIEW myview\G;
Here ‘\G’ statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
Let execute the above and see the output:
Sample Output:mysql> SHOW CREATE VIEW MYVIEW\G; *************************** 1. row *************************** View: myview Create View: CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW myview AS select departments.DEPARTMENT_ID AS DEPARTMENT_ID, departments.DEPARTMENT_NAME AS DEPARTMENT_NAME, departments.MANAGER_ID AS MANAGER_ID, departments.LOCATION_ID AS LOCATION_ID from departments character_set_client: latin1 collation_connection: latin1_swedish_ci 1 row in set (0.13 sec)
MySQL: SHOW DATABASES
SHOW DATABASES statement is used to lists the databases on the MySQL server host. The SHOW SCHEMAS can be used as a synonym for SHOW DATABASES.
Here is the syntax :SHOW {DATABASES | SCHEMAS} [LIKE ‘pattern’ | WHERE expr]
If the LIKE clause is present along with the SHOW DATABASES, indicates which database names to match.
See the following example:SHOW DATABASES;
Let execute the above and see the output:
Sample Output:mysql> SHOW DATABASES; +——————–+ | Database | +——————–+ | information_schema | | bookinfo | | bupayroll | | bupf | | empinfo | | mucpf | | mucstuinfo | | mysql | +——————–+ 8 rows in set (0.16 sec)
Here is the alternate statement of SHOW DATABASES:mysql> SHOW SCHEMAS; +——————–+ | Database | +——————–+ | information_schema | | bookinfo | | bupayroll | | bupf | | empinfo | | mucpf | | mucstuinfo | | mysql | +——————–+ 8 rows in set (0.02 sec)
Here is the example of SHOW DATABASES using LIKE.mysql> SHOW DATABASES LIKE ‘m%’; +—————+ | Database (m%) | +—————+ | mucpf | | mucstuinfo | | mysql | +—————+ 3 rows in set (0.03 sec)
MySQL : SHOW ENGINE
The SHOW ENGINE statement is used to display operational information about a storage engine.
Here is the syntax:SHOW ENGINE INNODB STATUS SHOW ENGINE INNODB MUTEX SHOW ENGINE {NDB | NDBCLUSTER} STATUS SHOW ENGINE PERFORMANCE_SCHEMA STATUS
See the following example:SHOW ENGINE INNODB STATUS\G;
Here ‘\G’ statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
Let execute the above and see the output:
Sample Output:mysql> SHOW ENGINE INNODB STATUS\G; *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 130729 18:26:13 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 11 seconds ———- SEMAPHORES ———- OS WAIT ARRAY INFO: reservation count 3, signal count 3 Mutex spin waits 0, rounds 0, OS waits 0 RW-shared spins 4, OS waits 2; RW-excl spins 1, OS waits 1 ———— TRANSACTIONS ———— Trx id counter 0 1792 Purge done for trx’s n:o < 0 0 undo n:o < 0 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: —TRANSACTION 0 0, not started, OS thread id 3512 MySQL thread id 1, query id 16 localhost 127.0.0.1 root SHOW ENGINE INNODB STATUS ——– FILE I/O ——– I/O thread 0 state: wait Windows aio (insert buffer thread) I/O thread 1 state: wait Windows aio (log thread) I/O thread 2 state: wait Windows aio (read thread) I/O thread 3 state: wait Windows aio (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o’s: 0, sync i/o’s: 0 Pending flushes (fsync) log: 0; buffer pool: 0 29 OS file reads, 3 OS file writes, 3 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ————————————- INSERT BUFFER AND ADAPTIVE HASH INDEX ————————————- Ibuf: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 34679, node heap has 0 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s — LOG — Log sequence number 0 46419 Log flushed up to 0 46419 Last checkpoint at 0 46419 0 pending log writes, 0 pending chkp writes 8 log i/o’s done, 0.00 log i/o’s/second ———————- BUFFER POOL AND MEMORY ———————- Total memory allocated 14857048; in additional pool allocated 857856 Dictionary memory allocated 20024 Buffer pool size 512 Free buffers 493 Database pages 19 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 19, created 0, written 0 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout ————– ROW OPERATIONS ————– 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread id 1032, state: waiting for server activity Number of rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s —————————- END OF INNODB MONITOR OUTPUT ============================ 1 row in set (0.00 sec)
MySQL: SHOW ENGINES
The SHOW ENGINES statement is used to display the status information about the server’s storage engines. It is important for checking whether a storage engine is supported, or what the default engine is.
Here is the syntax:SHOW [STORAGE] ENGINES;
See the following example:SHOW ENGINES\G
Here ‘\G’ statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
Let execute the above and see the output :
Sample Output:mysql> SHOW ENGINES\G *************************** 1. row *************************** Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tables Transactions: NO XA: NO Savepoints: NO *************************** 2. row *************************** Engine: FEDERATED Support: NO Comment: Federated MySQL storage engine Transactions: NULL XA: NULL Savepoints: NULL *************************** 3. row *************************** Engine: MyISAM Support: DEFAULT Comment: Default engine as of MySQL 3.23 with great performance Transactions: NO XA: NO Savepoints: NO *************************** 4. row *************************** Engine: BLACKHOLE Support: YES Comment: /dev/null storage engine (anything you write to it disappears) Transactions: NO XA: NO Savepoints: NO *************************** 5. row *************************** Engine: MRG_MYISAM Support: YES Comment: Collection of identical MyISAM tables Transactions: NO XA: NO Savepoints: NO *************************** 6. row *************************** Engine: CSV Support: YES Comment: CSV storage engine Transactions: NO XA: NO Savepoints: NO *************************** 7. row *************************** Engine: ARCHIVE Support: YES Comment: Archive storage engine Transactions: NO XA: NO Savepoints: NO *************************** 8. row *************************** Engine: InnoDB Support: YES Comment: Supports transactions, row-level locking, and foreign keys Transactions: YES XA: YES Savepoints: YES 8 rows in set (0.00 sec)
MySQL : SHOW ERRORS
The SHOW ERRORS statement is used to display the errors, warnings, and notes. This statement is almost similar to SHOW WARNINGS except displaying errors.
Here is the syntax:SHOW ERRORS [LIMIT [offset,] row_count] SHOW COUNT(*) ERRORS
The LIMIT clause can be used to specify the number of rows to be retrieved.
The offset is an argument, which LIMIT takes optionally to retrieve the number of rows. When mention two arguments, the first one is from a particular position and the second one is a number of rows after the first one position.
The offset of the initial row is 0 (not 1)
The SHOW COUNT(*) ERRORS statement is used to displays the number of errors.
See the following example:
Here, in the below statement, there is an error. Execute this statement an error message will be generated.
SHOW DATABASE;
Copy
and now, here is the statement-
SHOW ERRORS\G
Copy
Here ‘\G’ statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
Let execute the above and see the output:
Sample Output:mysql> SHOW ERRORS\G *************************** 1. row *************************** Level: Error Code: 1064 Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘database’ at line 1 1 row in set (0.02 sec)
SHOW COUNT(*) ERRORS;
Copy
Let execute the above and see the output:
Sample Output:mysql> SHOW COUNT(*) ERRORS; +———————–+ | @@session.error_count | +———————–+ | 1 | +———————–+ 1 row in set (0.11 sec)
Number of errors can also be retrieve using the error_count variable in a SELECT statement.
SELECT @@error_count;
Copy
Let execute the above and see the output:
Sample Output:mysql> SELECT @@error_count; +—————+ | @@error_count | +—————+ | 1 | +—————+ 1 row in set (0.06 sec)
MySQL: SHOW EVENTS
The SHOW EVENTS statement is used to display information about Event Manager events. It requires the EVENT privilege for the database from which the events are to be shown.
Here is the syntax:SHOW EVENTS [{FROM | IN} schema_name] [LIKE ‘pattern’ | WHERE expr]
This SHOW FUNCTION STATUS statement returns the characteristics of a stored function, such as the database, name, type, creator, creation and modification dates, and character set information.
Here is the syntax:SHOW FUNCTION STATUS [LIKE ‘pattern’ | WHERE expr]
See the following example.
SHOW FUNCTION STATUS\G
Copy
Here ‘\G’ statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
Let execute the above and see the output :
Sample Output:mysql> SHOW FUNCTION STATUS\G *************************** 1. row *************************** Db: empinfo Name: test1 Type: FUNCTION Definer: root@localhost Modified: 2013-07-31 17:03:05 Created: 2013-07-31 17:03:05 Security_type: DEFINER Comment: character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci 1 row in set (0.00 sec)
Let execute the example below using pattern matching and see the output:
Sample Output:mysql> SHOW FUNCTION STATUS like ‘tes%’\G *************************** 1. row *************************** Db: empinfo Name: test1 Type: FUNCTION Definer: root@localhost Modified: 2013-07-31 17:03:05 Created: 2013-07-31 17:03:05 Security_type: DEFINER Comment: character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci 1 row in set (0.00 sec)
Let execute the example below using where and see the output:
Sample Output:mysql> SHOW FUNCTION STATUS WHERE Db = ’empinfo’\G *************************** 1. row *************************** Db: empinfo Name: test1 Type: FUNCTION Definer: root@localhost Modified: 2013-07-31 17:03:05 Created: 2013-07-31 17:03:05 Security_type: DEFINER Comment: character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci 1 row in set (0.00 sec)
MySQL: SHOW GRANTS
The SHOW GRANTS statement is used to list the GRANT statement or statements that must be issued to duplicate the privileges that are granted to a MySQL user account. The account is named using the same format as for the GRANT statement; If you specify only the username part of the account name, a host name part of ‘%’ is used.
Here is the syntax:SHOW GRANTS [FOR user]
See the following example.
SHOW GRANTS FOR 'root'@'localhost';
Copy
Let execute the above and see the output:
Sample Output:+———————————————————————+ | Grants for root@localhost | +———————————————————————+ | GRANT ALL PRIVILEGES ON *.* TO ‘root’@’localhost’ WITH GRANT OPTION | +———————————————————————+ 1 row in set (0.03 sec)
Here is the another example of not using the hostnamemysql> SHOW GRANTS FOR ‘root’; +————————————————————-+ | Grants for root@% | +————————————————————-+ | GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ WITH GRANT OPTION | +————————————————————-+ 1 row in set (0.00 sec)
In the above example, only the username have specified as the part of the account name that is why in host name part a ‘%’ have appeared.
MySQL : SHOW INDEX
The SHOW INDEX statement returns the information of index of a table.
Here is the syntax :SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr]
Here is another example of SHOW INDEX statement using where clausemysql> SHOW INDEX FROM employees -> FROM empinfo WHERE column_name=’employee_id’; +———–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +———–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+ | employees | 0 | PRIMARY | 1 | EMPLOYEE_ID | A | 107 | NULL | NULL | | BTREE | | +———–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+ 1 row in set (0.00 sec)
Here are alternate statements for the above output
SHOW INDEX
FROM empinfo.employees
WHERE column_name='employee_id';
Copy
SHOW KEYS
FROM empinfo.employees
WHERE column_name='employee_id';
Copy
SHOW KEYS
IN employees
WHERE column_name='employee_id';
Copy
MySQL: SHOW MASTER STATUS
The SHOW MASTER STATUS statement provides status information about the binary log files of the master. It requires either the SUPER or REPLICATION CLIENT privilege.
Here is the syntax :SHOW MASTER STATUS
See the following example.
SHOW MASTER STATUS\G
Copy
Let execute the above and see the output:
Sample Output:mysql> SHOW MASTER STATUS\G *************************** 1. row *************************** File: mysql-bin.000129 Position: 106 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.03 sec)
Here ‘\G’ statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
MySQL: SHOW OPEN TABLES
SHOW OPEN TABLES statement is used to list the non-TEMPORARY tables that are currently open in the table cache. Here is the syntax :
https://www.adsensecustomsearchads.com/afs/ads?psid=5134551505&channel=AutoRsVariant&cx=r-440389826592af9d2&fexp=44759876%2C44759927%2C44759837%2C44795922%2C31080663%2C95320377%2C95320888%2C95321627%2C95322163%2C95323009%2C0%2C21404%2C17301383%2C71847096&client=pub-2153208817642134&r=m&sct=ID%3Df1c5d672aa31266c%3AT%3D1706354011%3ART%3D1706354011%3AS%3DALNI_MZlJMMg_q5l3r_1tPiuFzhttpHLOQ&sc_status=6&hl=en&rpbu=http%3A%2F%2Fgoogle.com&rpqp=q&type=3&rs_tt=c&oe=UTF-8&ie=UTF-8&format=r5&nocache=6721706467507153&num=0&output=afd_ads&domain_name=www.w3resource.com&v=3&bsl=10&pac=0&u_his=17&u_tz=-480&dt=1706467507157&u_w=1366&u_h=768&biw=1297&bih=644&psw=1297&psh=644&frm=0&cl=600476684&uio=-&cont=autors-container-0&drt=0&jsid=csa&jsv=600476684&rurl=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-show.php&referer=https%3A%2F%2Fwww.w3resource.com%2Fmysql%2Fmysql-security.phpSHOW OPEN TABLES [{FROM | IN} db_name] [LIKE ‘pattern’ | WHERE expr]
See the following example.
SHOW OPEN TABLES FROM empinfo;
Copy
Let execute the above and see the output:
Sample Output:mysql> SHOW OPEN TABLES FROM empinfo; +———–+————-+——–+————-+ | Database | Table | In_use | Name_locked | +———–+————-+——–+————-+ | empinfo | employees | 0 | 0 | | empinfo | departments | 0 | 0 | +———–+————-+——–+————-+ 2 rows in set (0.02 sec)
The SHOW OPEN TABLES output has the following columns:
Database – the name of the database currently being used.
Table – name of the non temporary table(s) currently opened.
In_use – The number of table locks or lock requests there is in the database for the table.
Name_locked – Whether the table name is locked. Name locking is used for the operations like dropping or renaming tables.
If there are no privileges for a table, it does not show up in the output from SHOW OPEN TABLES.
Here is the another example of pattern matching
SHOW OPEN TABLES FROM employee LIKE 'e%';
Copy
Let execute the above and see the output :
Sample Output:mysql> SHOW OPEN TABLES FROM employee LIKE ‘e%’; +———–+———–+——–+————-+ | Database | Table | In_use | Name_locked | +———–+———–+——–+————-+ | employee | employees | 0 | 0 | +———–+———–+——–+————-+ 1 row in set (0.00 sec)
MySQL: SHOW PLUGINS
The SHOW PLUGINS statement is used to display the information about server plugins. The information or Plugins is also available in the INFORMATION_SCHEMA.PLUGINS table.
Here ‘\G’ statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
MySQL : SHOW PRIVILEGES
The SHOW PRIVILEGES statement shows the list of system privileges that the MySQL server supports. The exact list of privileges depends on the version of the server which you are using.
Here is the syntax:SHOW PRIVILEGES
See the following example.
SHOW PRIVILEGES\G
Copy
Let execute the above and see the output :
Sample Output:mysql> SHOW PRIVILEGES\G *************************** 1. row *************************** Privilege: Alter Context: Tables Comment: To alter the table *************************** 2. row *************************** Privilege: Alter routine Context: Functions,Procedures Comment: To alter or drop stored functions/procedures *************************** 3. row *************************** Privilege: Create Context: Databases,Tables,Indexes Comment: To create new databases and tables *************************** 4. row *************************** Privilege: Create routine Context: Databases Comment: To use CREATE FUNCTION/PROCEDURE *************************** 5. row *************************** Privilege: Create temporary tables Context: Databases Comment: To use CREATE TEMPORARY TABLE *************************** 6. row *************************** Privilege: Create view Context: Tables Comment: To create new views ….
Here ‘\G’ statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
MySQL : SHOW PROCEDURE CODE
The SHOW PROCEDURE CODE statement is used to display the internal implementation of the named stored procedure. This statement is a is available only for servers that have been built with debugging support.
Here is the syntax:SHOW PROCEDURE CODE proc_name
See the following example, here the server has not built with debugging support.mysql> SHOW PROCEDURE CODE job_data; ERROR 1289 (HY000): The ‘SHOW PROCEDURE|FUNCTION CODE’ feature is disabled; you need MySQL built with ‘–with-debug’ to have it working
MySQL : SHOW PROCEDURE STATUS
This SHOW PROCEDURE STATUS statement returns the characteristics of a stored procedure, such as the database, name, type, creator, creation and modification dates, and character set information.
Here is the syntax:SHOW PROCEDURE STATUS [LIKE ‘pattern’ | WHERE expr]
See the following example.
SHOW PROCEDURE STATUS\G
Copy
Here ‘\G’ statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
The SHOW PROCESSLIST statement shows you which threads are running. If you have the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MySQL account that you are using). If you do not use the FULL keyword, only the first 100 characters of each statement are shown in the Info field.
Here is the syntax:SHOW [FULL] PROCESSLIST
See the following example.
SHOW PROCESSLIST\G
Copy
Let execute the above and see the output:
Sample Output:mysql> SHOW FULL PROCESSLIST\G *************************** 1. row *************************** Id: 1 User: root Host: localhost:1300 db: NULL Command: Query Time: 0 State: NULL Info: SHOW FULL PROCESSLIST 1 row in set (0.00 sec)
Here ‘\G’ statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout.
MySQL : SHOW RELAYLOG EVENTS
The SHOW RELAYLOG statement shows the events in the relay log of a replication slave. If you do not specify ‘log_name’, the first relay log is displayed.
Here is the syntax:SHOW RELAYLOG EVENTS [IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count]
If ‘log_name’, is not specified the first relay log is displayed. This statement has no effect on the master.
MySQL: SHOW SLAVE HOSTS
SHOW SLAVE HOSTS statement is used to display a list of replication slaves currently registered with the master.
Here is the syntax:SHOW SLAVE HOSTS
MySQL: SHOW SLAVE STATUS
The SHOW SLAVE STATUS statement provides status information on essential parameters of the slave threads.
Here is the syntax:SHOW SLAVE STATUS
The statement requires either the SUPER or REPLICATION CLIENT privilege.
MySQL : SHOW STATUS
The SHOW STATUS statement provides the information of server status. The LIKE clause along with this statement helps to match the specific variable. The usage of WHERE clause can fetch rows against general conditions. This statement does not require any privilege.
Here is the syntax :SHOW [GLOBAL | SESSION] STATUS [LIKE ‘pattern’ | WHERE expr]
Sample Output:mysql> SHOW STATUS LIKE ‘Qca%’; +————————-+——-+ | Variable_name | Value | +————————-+——-+ | Qcache_free_blocks | 0 | | Qcache_free_memory | 0 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 0 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 0 | +————————-+——-+ 8 rows in set (0.00 sec)
MySQL : SHOW TABLE STATUS
The SHOW TABLE STATUS statement provides a lot of information about each non-TEMPORARY table. The LIKE clause, if present, indicates which table names to match. The usage of WHERE clause can fetch rows against general conditions.
Here is the syntax:SHOW TABLE STATUS [{FROM | IN} db_name] [LIKE ‘pattern’ | WHERE expr]
SHOW TABLES lists the non-TEMPORARY tables in a given database. The LIKE clause, if present, indicates which table names to match. The usage of WHERE clause can fetch rows against general conditions.
Here is the syntax :SHOW [FULL] TABLES [{FROM | IN} db_name] [LIKE ‘pattern’ | WHERE expr]
Here is the another example of SHOW TABLES with pattern matching
SHOW TABLES FROM empinfo LIKE 'e%';
Copy
Let execute the above and see the output:
Sample Output:mysql> SHOW TABLES FROM empinfo LIKE ‘e%’; +————————–+ | Tables_in_empinfo (e%) | +————————–+ | employees | +————————–+ 1 row in set (0.02 sec)
Here is the another example of SHOW TABLES with WHERE clause
SHOW TABLES FROM empinfo
WHERE Tables_in_empinfo='employees';
Copy
Let execute the above and see the output:
Sample Output:mysql> SHOW TABLES FROM empinfo WHERE Tables_in_empinfo=’employees’; +———————+ | Tables_in_empinfo | +———————+ | employees | +———————+ 1 row in set (0.00 sec)
MySQL : SHOW TRIGGERS
SHOW TRIGGERS statement is used to list the triggers currently defined for tables in a database
Here is the syntax:SHOW TRIGGERS [{FROM | IN} db_name] [LIKE ‘pattern’ | WHERE expr]
MySQL : SHOW VARIABLES
The SHOW VARIABLES statement shows the values of MySQL system variables. The LIKE clause, if present, indicates which table names to match. The usage of WHERE clause can fetch rows against general conditions. This statement does not require any privilege. It requires only the ability to connect to the server.
Here is the syntax:SHOW [GLOBAL | SESSION] VARIABLES [LIKE ‘pattern’ | WHERE expr]
Here is another example of SHOW VARIABLES with GLOBAL
SHOW GLOBAL VARIABLES;
Copy
Let execute the above and see the output:
Sample Output:mysql> SHOW GLOBAL VARIABLES; +—————————————–+———————————————-+ | Variable_name | Value | +—————————————–+———————————————-+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | | autocommit | ON | | automatic_sp_privileges | ON | | back_log | 50 | | basedir | c:\wamp\bin\mysql\mysql5.1.36\ | | big_tables | OFF | …
Here is another example of SHOW VARIABLES with LIKE
SHOW VARIABLES LIKE 'time%';
Copy
Let execute the above and see the output:
Sample Output:mysql> SHOW VARIABLES LIKE ‘time%’; +—————+————+ | Variable_name | Value | +—————+————+ | time_format | %H:%i:%s | | time_zone | SYSTEM | | timed_mutexes | OFF | | timestamp | 1375447849 | +—————+————+ 4 rows in set (0.00 sec)
Here is another example of SHOW VARIABLES with LIKE
SHOW VARIABLES LIKE 'time%';
Copy
MySQL: SHOW WARNINGS
The SHOW WARNINGS statement is used to display the warnings,errors, and notes that resulted from the last statement in the current session that generated messages. It shows nothing if the last statement does not generate any message.
Here is the syntax :SHOW WARNINGS [LIMIT [offset,] row_count] SHOW COUNT(*) WARNINGS
The LIMIT clause can be used to specify the number of rows to be retrieved.
The offset is an argument, which LIMIT takes optionally to retrieve the number of rows. When mention two arguments, the first one is from a particular position and the second one is a number of rows after the first one position.
The offset of the initial row is 0 (not 1)
The SHOW COUNT(*) WARNINGS statement is used to displays the number of warnings.
See the following example:
Here, in the below statement, there is an error. Execute this statement an error message will be generated.
SELECT * FORM employees;
Copy
and now, here is the statement-
SHOW WARNINGS\G
Copy
Here ‘\G’ statement have used as a terminator rather than a semicolon to obtain a more readable vertical layout:
Let execute the above and see the output:
Sample Output:mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Error Code: 1064 Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘FORM EMPLOYEES’ at line 1 1 row in set (0.00 sec)
SHOW COUNT(*) WARNINGS;
Copy
Let execute the above and see the output:
Sample Output:mysql> SHOW COUNT(*) WARNINGS; +————————-+ | @@session.warning_count | +————————-+ | 1 | +————————-+ 1 row in set (0.13 sec)
A number of warnings can also be retrieve using the warning_count variable in a SELECT statement.
SELECT @@warning_count;
Copy
Let execute the above and see the output:
Sample Output:mysql> SELECT @@warning_count; +—————–+ | @@warning_count | +—————–+ | 1 | +—————–+ 1 row in set (0.03 sec)
Database security entails allowing or disallowing user actions on the database and the objects within it. When you will create an database application, the security policy is the first step. An application security policy is a list of application security requirements and rules that regulate user access to database objects. This chapter discusses aspects of application security and MySQL Database features which contains the following topics :
Contents:
MySQL General Security Issues
The MySQL Access Privilege System
MySQL User Account Management
MySQL general security issues
Security Guidelines :
Except MySQL root account does not permit anyone to access the user table in the MySQL database.
Use the GRANT and REVOKE statements to control access to MySQL. Do not grant unnecessary privileges and never grant privileges to all hosts.
Never store simple text passwords in your database. Store the hash value using SHA2(), SHA1(), MD5() functions or other hashing function in a different way. Try to use a complex password.
Try to use a firewall and put MySQL behind the firewall.
3306 is the default user port of MySQL and this port should not be accessible from untrusted hosts. You can scan the ports from Internet using a tool such as nmap. From a remote machine you can check whether the port is open or not with this command: shell> telnet server_host 3306. If telnet hangs or the connection is refused, the port is blocked. If you get a connection and some garbage characters, the port is open and should be closed on your firewall or router, unless you really have a good reason to keep it open.
Some applications access MySQL database for different a purpose. Never trust these input data entered by the user and must validate properly before access database.
Do not transmit unencrypted data over the Internet. Use an encrypted protocol such as SSL (MySQL supports internal SSL connections) or SSH.
Use tcpdump and strings utilities. By issuing this command shell> tcpdump -l -i eth0 -w – src or dst port 3306 | strings you can check whether MySQL data streams are unencrypted or not.
Keeping Passwords Secure:
End-User Guidelines for Password Security
Use the -p or –password option on the command line with no password value specified. Here is the commandshell> mysql -u user_id -p database_name Enter password : *********** When you input the password it will not visible.
Store your password in an option file. For example Unix you can list your password in [client] section of the .my.cnf file in your home directory and to keep password safe, set the file access mode to 400 or 600.
Administrator Guidelines for Password Security : MySQL stores passwords for user accounts in the mysql.user table. Therefore this table should not be accessed by any nonadministrative accounts. User account password must reset time to time.
Passwords and Logging : Passwords can be written as plain text in SQL statements such as CREATE USER, GRANT, and SET PASSWORD, or statements that invoke the PASSWORD() function. If these statements are logged by the MySQL server as written, such passwords become available to anyone with access to the logs. Beginning with MySQL 5.6.3, statement logging is modified so that passwords do not appear in plain text for the following statements:CREATE USER … IDENTIFIED BY …GRANT … IDENTIFIED BY …SET PASSWORD …SLAVE START … PASSWORD = … (as of 5.6.4)CREATE SERVER … OPTIONS(… PASSWORD …) (as of 5.6.9)ALTER SERVER … OPTIONS(… PASSWORD …) (as of 5.6.9) Passwords in those statements are rewritten not to appear literally in statement text, for the general query log, slow query log, and binary log. Rewriting does not apply to other statements.
Password Hashing in MySQL : MySQL lists user accounts in the user table of the MySQL database. Each MySQL account can be assigned a password, although the user table does not store the cleartext version of the password, but a hash value computed from it.
Implications of Password Hashing Changes in MySQL 4.1 for Application Programs : An upgrade to MySQL version 4.1 or later can cause compatibility issues for applications that use PASSWORD() to generate passwords for their own purposes. Applications really should not do this, because PASSWORD() should be used only to manage passwords for MySQL accounts.
The validate_password plugin (available as of MySQL 5.6.6) can be used to test passwords and improve security.
Making MySQL Secure Against Attackers :
To make a MySQL system secure, you should maintain the following suggestions :
Require all MySQL accounts to have a password.
Make sure that the only Unix user account with read or write privileges in the database directories is the account that is used for running mysqld.
Never run the MySQL server as the Unix root user
Do not grant the FILE privilege to nonadministrative users
Do not permit the use of symlinks to tables.
Stored programs and views should be written using the security guidelines
If you do not trust your DNS, you should use IP addresses rather than hostnames in the grant tables.
If you want to restrict the number of connections permitted to a single account, you can do so by setting the max_user_connections variable in mysqld.
Security-Related mysqld Options and Variables :
The following table shows mysqld options and system variables that affect security.
Name
Description
Cmd-Line
Option file
System Var
Var Scope
Dynamic
allow-suspicious-udfs
This option controls whether user-defined functions that have only an xxx symbol for the main function can be loaded. By default,
Yes
Yes
automatic_sp_privileges
When this variable has a value of 1 (the default), the server automatically grants the EXECUTE and ALTER ROUTINE privileges to the creator of a stored routine, if the user cannot already execute and alter or drop the routine. (The ALTER ROUTINE privilege is required to drop the routine.) The server also automatically drops those privileges from the creator when the routine is dropped. If automatic_sp_privileges is 0, the server does not automatically add or drop these privileges.
Yes
Global
Yes
chroot
Put the mysqld server in a closed environment during startup by using the chroot() system call.
Yes
Yes
des-key-file
Read the default DES keys from this file. These keys are used by the DES_ENCRYPT() and DES_DECRYPT() functions.
Yes
Yes
local_infile
Whether LOCAL is supported for LOAD DATA INFILE statements. If this variable is disabled, clients cannot use LOCAL in LOAD DATA statements.
Yes
Global
Yes
old_passwords
This variable determines the type of password hashing performed by the PASSWORD() function and statements such as CREATE USER and GRANT.
Yes
Both
Yes
safe-user-create
If this option is enabled, a user cannot create new MySQL users by using the GRANT statement unless the user has the INSERT privilege for the mysql.user table or any column in the table. I
Yes
Yes
secure-auth
This option causes the server to block connections by clients that attempt to use accounts that have passwords stored in the old (pre-4.1) format. Use it to prevent all use of passwords employing the old format (and hence insecure communication over the network).
Yes
Yes
Global
Yes
– Variable: secure_auth
If this variable is enabled, the server blocks connections by clients that attempt to use accounts that have passwords stored in the old (pre-4.1) format.
Yes
Global
Yes
secure-file-priv
By default, this variable is empty. If set to the name of a directory, it limits the effect of the LOAD_FILE() function and the LOAD DATA and SELECT … INTO OUTFILE statements to work only with files in that directory.
Yes
Yes
Global
No
– Variable: secure_file_priv
Yes
Global
No
skip-grant-tables
This option causes the server to start without using the privilege system at all, which gives anyone with access to the server unrestricted access to all databases.
Yes
Yes
skip-name-resolve
All interaction with mysqld must be made using named pipes or shared memory (on Windows) or Unix socket files (on Unix). This option is highly recommended for systems where only local clients are permitted.
Yes
Yes
Global
No
– Variable: skip_name_resolve
Yes
Global
No
skip-networking
All interaction with mysqld must be made using named pipes or shared memory (on Windows) or Unix socket files (on Unix). This option is highly recommended for systems where only local clients are permitted.
Yes
Yes
Global
No
– Variable: skip_networking
Yes
Global
No
skip-show-database
This option sets the skip_show_database system variable that controls who is permitted to use the SHOW DATABASES statement.
Yes
Yes
Global
No
– Variable: skip_show_database
Yes
Global
No
How to Run MySQL as a Normal User:
On Windows, you can run the server as a Windows service using a normal user account.
On Unix, the MySQL server mysqld can be started and run by any user. However, you should avoid running the server as the Unix root user for security reasons.
Security Issues with LOAD DATA LOCAL:
There are two potential security issues with supporting the LOCAL version of LOAD DATA statements :
The transfer of the file from the client host to the server host is initiated by the MySQL server. In theory, a patched server could be built that would tell the client program to transfer a file of the server’s choosing rather than the file named by the client in the LOAD DATA statement. Such a server could access any file on the client host to which the client user has read access.
In a Web environment where the clients are connecting from a Web server, a user could use LOAD DATA LOCAL to read any files that the Web server process has read access to (assuming that a user could run any command against the SQL server). In this environment, the client with respect to the MySQL server actually is the Web server, not the remote program being run by the user who connects to the Web server.
Client Programming Security Guidelines:
Applications that access MySQL should not trust any data entered by users, who can try to trick your code by entering special or escaped character sequences in Web forms, URLs, or whatever application you have built. Be sure that your application remains secure if a user enters something like “; DROP DATABASE mysql;”. This is an extreme example, but large security leaks and data loss might occur as a result of hackers using similar techniques if you do not prepare for them. See the following guidelines :
Enable strict SQL mode to tell the server to be more restrictive of what data values it accepts.
Try to enter single and double quotation marks (“’” and “””) in all of your Web forms. If you get any kind of MySQL error, investigate the problem right away.
Try to modify dynamic URLs by adding %22 (“””), %23 (“#”), and %27 (“’”) to them.
Try to modify data types in dynamic URLs from numeric to character types using the characters shown in the previous examples. Your application should be safe against these and similar attacks.
Try to enter characters, spaces, and special symbols rather than numbers in numeric fields. Your application should remove them before passing them to MySQL or else generate an error. Passing unchecked values to MySQL is very dangerous.
Check the size of data before passing it to MySQL.
Do not give your applications any access privileges they do not need.
The MySQL Access Privilege System
Privileges Provided by MySQL :
MySQL provides privileges that apply in different contexts and at different levels of operation:
Administrative privileges enable users to manage the operation of the MySQL server. These privileges are global because they are not specific to a particular database.
Database privileges apply to a database and to all objects within it. These privileges can be granted for specific databases, or globally so that they apply to all databases.
Privileges for database objects such as tables, indexes, views, and stored routines can be granted for specific objects within a database, for all objects of a given type within a database (for example, all tables in a database), or globally for all objects of a given type in all databases).
Permissible Privileges for GRANT and REVOKE:
Privilege
Column
Context
CREATE
Create_priv
databases, tables, or indexes
DROP
Drop_priv
databases, tables, or views
GRANT OPTION
Grant_priv
databases, tables, or stored routines
LOCK TABLES
Lock_tables_priv
databases
REFERENCES
References_priv
databases or tables
EVENT
Event_priv
databases
ALTER
Alter_priv
tables
DELETE
Delete_priv
tables
INDEX
Index_priv
tables
INSERT
Insert_priv
tables or columns
SELECT
Select_priv
tables or columns
UPDATE
Update_priv
tables or columns
CREATE TEMPORARY TABLES
Create_tmp_table_priv
tables
TRIGGER
Trigger_priv
tables
CREATE VIEW
Create_view_priv
views
SHOW VIEW
Show_view_priv
views
ALTER ROUTINE
Alter_routine_priv
stored routines
CREATE ROUTINE
Create_routine_priv
stored routines
EXECUTE
Execute_priv
stored routines
FILE
File_priv
file access on server host
CREATE TABLESPACE
Create_tablespace_priv
server administration
CREATE USER
Create_user_priv
server administration
PROCESS
Process_priv
server administration
PROXY
see proxies_priv table
server administration
RELOAD
Reload_priv
server administration
REPLICATION CLIENT
Repl_client_priv
server administration
REPLICATION SLAVE
Repl_slave_priv
server administration
SHOW DATABASES
Show_db_priv
server administration
SHUTDOWN
Shutdown_priv
server administration
SUPER
Super_priv
server administration
ALL [PRIVILEGES]
server administration
USAGE
server administration
Privilege System Grant Tables:
Normally, you manipulate the contents of the grant tables in the mysql database indirectly by using statements such as GRANT and REVOKE to set up accounts and control the privileges available to each one.
These mysql database tables contain grant information:
user: Contains user accounts, global privileges, and other non-privilege columns.
db: Contains database-level privileges.
host: Obsolete. New MySQL installations no longer create this table as of MySQL 5.6.7.
tables_priv: Contains table-level privileges.
columns_priv: Contains column-level privileges.
procs_priv: Contains stored procedure and function privileges.
proxies_priv: Contains proxy-user privileges.
Specifying Account Names:
MySQL account names consist of a username and a hostname. This enables the creation of accounts for users with the same name who can connect from different hosts. This section describes how to write account names, including special values and wildcard rules. In SQL statements such as CREATE USER, GRANT, and SET PASSWORD, write account names using the following rules:
Syntax for account names is ‘user_name’@’host_name’.
An account name consisting only of a username is equivalent to ‘user_name’@’%’. For example, ‘me’ is equivalent to ‘me’@’%’.
The username and hostname need not be quoted if they are legal as unquoted identifiers. Quotes are necessary to specify a user_name string containing special characters (such as “-”), or a host_name string containing special characters or wildcard characters (such as “%”); for example, ‘test-user’@’%.com’.
Quote usernames and hostnames as identifiers or as strings, using either backtick (“`”), single quotation marks (“’”), or double quotation marks (“””).
The username and hostname parts, if quoted, must be quoted separately. That is, write ‘me’@’localhost’, not ‘me@localhost’; the latter is interpreted as ‘me@localhost’@’%’.
A reference to the CURRENT_USER or CURRENT_USER() function is equivalent to specifying the current client’s username and hostname literally.
Access Control, Stage 1: Connection Verification:
When you attempt to connect to a MySQL server, the server accepts or rejects the connection based on your identity and whether you can verify your identity by supplying the correct password. If not, the server denies access to you completely. Otherwise, the server accepts the connection, and then enters Stage 2 and waits for requests. Your identity is based on two pieces of information :
The client host from which you connect.
Your MySQL user name.
Access Control, Stage 2: Connection Verification:
After you establish a connection, the server enters Stage 2 of access control. For each request that you issue through that connection, the server determines what operation you want to perform, then checks whether you have sufficient privileges to do so. This is where the privilege columns in the grant tables come into play. These privileges can come from any of the user, db, tables_priv, columns_priv, or procs_priv tables.
The user table grants privileges that are assigned to you on a global basis and that apply no matter what the default database is. For example, if the user table grants you the DELETE privilege, you can delete rows from any table in any database on the server host! It is wise to grant privileges in the user table only to people who need them, such as database administrators.
The db table grants database-specific privileges. Values in the scope columns of this table can take the following forms :
A blank User value matches the anonymous user. A nonblank value matches literally; there are no wildcards in usernames.
The wildcard characters “%” and “_” can be used in the Host and Db columns. These have the same meaning as for pattern-matching operations performed with the LIKE operator. If you want to use either character literally when granting privileges, you must escape it with a backslash. For example, to include the underscore character (“_”) as part of a database name, specify it as “\_” in the GRANT statement.
A ‘%’ or blank Host value means “any host.”
A ‘%’ or blank Db value means “any database.”
When Privilege Changes Take Effect :
When mysqld starts, it reads all grant table contents into memory. The in-memory tables become effective for access control at that point. If you modify the grant tables indirectly using account-management statements such as GRANT, REVOKE, SET PASSWORD, or RENAME USER, the server notices these changes and loads the grant tables into memory again immediately.
Causes of Access-Denied Errors:
If you encounter problems when you try to connect to the MySQL server, the following items describe some courses of action you can take to correct the problem.
Make sure that the server is running. If it is not, clients cannot connect to it.
It might be that the server is running, but you are trying to connect using a TCP/IP port, named pipe, or Unix socket file different from the one on which the server is listening. To find out where the socket file is, you can use this command: shell> netstat -ln | grep mysql
Make sure that the server has not been configured to ignore network connections or (if you are attempting to connect remotely) that it has not been configured to listen only locally on its network interfaces.
Check to make sure that there is no firewall blocking access to MySQL.
The grant tables must be properly set up so that the server can use them for access control.
After a fresh installation, you should connect to the server and set up your users and their access permissions :shell> mysql -u root mysql
If you have updated an existing MySQL installation to a newer version, run the mysql_upgrade script.
If a client program receives the error message “Client does not support authentication protocol requested by server; consider upgrading MySQL client” it means that the server expects passwords in a newer format than the client is capable of generating.
If a client program seems to be sending incorrect default connection parameters when you have not specified them on the command line, check any applicable option files and your environment.
If you get the error message “Access denied for user ‘root’@’localhost’ (using password: YES)”, it means that you are using an incorrect root password.
If you change a password by using SET PASSWORD, INSERT, or UPDATE, you must encrypt the password using the PASSWORD() function. If you do not use PASSWORD() for these statements, the password will not work.
localhost is a synonym for your local hostname, and is also the default host to which clients try to connect if you specify no host explicitly.
The Access denied error message tells you who you are trying to log in as, the client host from which you are trying to connect, and whether you were using a password.
If you get an Access denied error when trying to connect to the database with mysql -u user_name, you may have a problem with the user table.
If you get the error message “Host … is not allowed to connect to this MySQL server”, when you try to connect from a host other than the one on which the MySQL server is running, it means that there is no row in the user table with a Host value that matches the client host.
If you specify a hostname when trying to connect, but get an error message where the hostname is not shown or is an IP address, it means that the MySQL server got an error when trying to resolve the IP address of the client host to a name:
MySQL User Account Management
UserNames and Passwords:
MySQL stores accounts in the user table of the mysql database. An account is defined in terms of a username and the client host or hosts from which the user can connect to the server. The account may also have a password
Adding and removing user accounts:
You can create MySQL accounts in two ways:
By using statements intended for creating accounts, such as CREATE USER or GRANT. These statements cause the server to make appropriate modifications to the grant tables.
By manipulating the MySQL grant tables directly with statements such as INSERT, UPDATE, or DELETE.
To remove an account, use the DROP USER statement,
Setting Account Resource Limits:
In MySQL 5.6, you can limit use of the following server resources for individual accounts:
The number of queries that an account can issue per hour
The number of updates that an account can issue per hour
The number of times an account can connect to the server per hour
The number of simultaneous connections to the server by an account
Assigning Account Passwords:
Required credentials for clients that connect to the MySQL server can include a password. In MySQL 5.6, it is also possible for clients to authenticate using plugins.
To assign a password when you create a new account with CREATE USER, include an IDENTIFIED BY clause :mysql> CREATE USER ‘user’@’localhost’ -> IDENTIFIED BY ‘mypass’;
To assign or change a password for an existing account, one way is to issue a SET PASSWORD statement :mysql> SET PASSWORD FOR -> ‘user’@’localhost’ = PASSWORD(‘mypass’);
View is a data object which does not contain any data. Contents of the view are the resultant of a base table. They are operated just like base table but they don’t contain any data of their own. The difference between a view and a table is that views are definitions built on top of other tables (or views). If data is changed in the underlying table, the same change is reflected in the view. A view can be built on top of a single or multiple tables.
Version: MySQL 5.6
Contents:
Why Views?
How to Create MySQL View?
Restrictions on View definition
Tools to create MySQL Views
Alter a MySQL view
DROP a MySQL view
MySQL CREATE VIEW with WHERE
MySQL CREATE VIEW with AND and OR
MySQL CREATE VIEW with GROUP BY
MySQL CREATE VIEW with ORDER BY
MySQL CREATE VIEW with BETWEEN and IN
MySQL CREATE VIEW with LIKE
MySQL CREATE VIEW using subqueries
MySQL CREATE VIEW with JOIN
MySQL CREATE VIEW with UNION
Why views?
Views can be effective copies of base tables.
Views can have column names and expressions.
You can use any clauses in views.
Views can be used in INSERT/UPDATE/DELETE.
Views can contain expressions in the select list.
Views can be views of views.
MySQL Views need Version 5.0 or higher
To get views to work, you’ll need to upgrade to MySQL version 5.0 (or higher). You can check your MySQL version in the following way:
Check the privileges of the current user:
The CREATE VIEW statement requires the CREATE VIEW privilege for the view and some privilege for each column selected by the SELECT statement. The following command shows the user privileges.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 connecting only | +————————-+——————–+——————————————————-+ 31 rows in set (0.00 sec)
Select a database :
Before creating a view we must select a database. Following command shows the list of databases.
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)
Create View
Following statements create a view. By default, a view is associated with the default database (currently used the database). To associate the view with a given database, specify the name as database_name. view_name when you create it. Here is the complete syntax :
view_name: view_name is the name of the view. A view always belongs to a database. By default, a new view is created in the currently used database. The view name may also be used with the database name, as database_name.view_name, but it is unnecessary if database_name is the default database.
select_statement: The select_statement is a SELECT statement and provides the definition of the view. select_statement can select data from base tables or other views.
Example:mysql> USE hr; Database changed mysql> CREATE VIEW my_v1 AS SELECT * FROM user_details; Query OK, 0 rows affected (0.13 sec)
column_list: The column_list part is optional. It provides a list of names for the view’s columns right after the view name where the names must be unique. The number of names in column_list must be the same as the number of columns retrieved by the SELECT statement. If you want to give your view columns a different name, you can do so by adding an [AS name] clause in the select list.
Example: View without column_listmysql> SELECT * FROM user; +———-+———–+——–+ | userid | password | name | +———-+———–+——–+ | scott123 | 123@sco | Scott | | ferp6734 | dloeiu@&3 | Palash | | diana094 | ku$j@23 | Diana | +———-+———–+——–+ 3 rows in set (0.04 sec) mysql> CREATE VIEW my_v2 AS SELECT * FROM user; Query OK, 0 rows affected (0.05 sec) mysql> SELECT * FROM my_v2; +———-+———–+——–+ | userid | password | name | +———-+———–+——–+ | scott123 | 123@sco | Scott | | ferp6734 | dloeiu@&3 | Palash | | diana094 | ku$j@23 | Diana | +———-+———–+——–+ 3 rows in set (0.05 sec)
Now specify the columns name in the above view:mysql> CREATE VIEW my_v3 AS SELECT userid AS User_ID, password AS Password, name AS Name FROM user; Query OK, 0 rows affected (0.04 sec) mysql> SELECT * FROM my_v3; +———-+———–+——–+ | User_ID | Password | Name | +———-+———–+——–+ | scott123 | 123@sco | Scott | | ferp6734 | dloeiu@&3 | Palash | | diana094 | ku$j@23 | Diana | +———-+———–+——–+ 3 rows in set (0.04 sec) CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
OR REPLACE: If the optional OR REPLACE clause is added with CREATE VIEW statement, the CREATE VIEW statement replaces an existing view and create a new one. If the view does not exist, CREATE VIEW is the same as CREATE OR REPLACE VIEW.CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
– ALGORITHM : The ALGORITHM clause is optional, it affects how MySQL processes the view. ALGORITHM takes three values: MERGE, TEMPTABLE, or UNDEFINED. The default algorithm is UNDEFINED.
[DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] : The DEFINER and SQL SECURITY clauses specify the security context to be used when checking access privileges at view invocation time.
If you specify the DEFINER clause, the following rules determine the legal DEFINER user values :
If you do not have the SUPER privilege, the only legal user value is your own account and you cannot set the definer to some other account.
If you have the SUPER privilege, you can specify any syntactically legal account name.
Within a stored routine that is defined with the SQL SECURITY DEFINER characteristic, CURRENT_USER returns the routine’s DEFINER value. This also affects a view defined within such a routine, if the view definition contains a DEFINER value of CURRENT_USER.
[WITH [CASCADED | LOCAL] CHECK OPTION] : The WITH CHECK OPTION clause can be given for an updatable view to preventing inserts or updates to rows except those for which the WHERE clause in the select_statement is true. In a WITH CHECK OPTION clause for an updatable view, the LOCAL and CASCADED keywords determine the scope of check testing when the view is defined in terms of another view. The LOCAL keyword restricts the CHECK OPTION only to the view being defined. CASCADED causes the checks for underlying views to be evaluated as well. When neither keyword is given, the default is CASCADED.
Restrictions on View definition
The SELECT statement cannot contain a subquery in the FROM clause.
The SELECT statement cannot refer to system or user variables.
Within a stored program, the definition cannot refer to program parameters or local variables.
The SELECT statement cannot refer to prepared statement parameters.
Any table or view referred to in the definition must exist.
The definition cannot refer to a TEMPORARY table, and you cannot create a TEMPORARY view.
Any tables named in the view definition must exist at definition time.
You cannot associate a trigger with a view.
Aliases for column names in the SELECT statement are checked against the maximum column length of 64 characters (not the maximum alias length of 256 characters).
Tools to create MySQL Views
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 create and run your own view, 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 selects a database:
After selecting the database right click on Views, a new popup will come:
After selecting “Create View ” following screen will come where you can write your own view.
After writing the view 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 view:
Alter a view
ALTER VIEW statement changes the definition of an existing view. The syntax of the statement is similar to CREATE VIEW.
The IF EXISTS clause prevents an error from occurring for views that don’t exist.
MySQL CREATE VIEW with WHERE
CREATE VIEW command can be used with WHERE clause.
Example:
Sample table: author
CREATE VIEW view_author
AS SELECT *
FROM author
WHERE country='USA'
Copy
The above MySQL statement will create a view ‘view_author’ taking records (for all columns) of author table if those records contain the value ‘USA’ for country column.
MySQL CREATE VIEW with AND and OR
CREATE VIEW command can be used with AND and OR operators.
Example:
Sample table: publisher
CREATE VIEW view_publisher
AS SELECT pub_name,pub_city,country
FROM publisher
WHERE (country='USA' AND pub_city='New York')
OR (country='India' AND pub_city='Mumbai');
Copy
The above MySQL statement will create a view ‘view_publisher’ taking records for pub_name, pub_city and country columns of publisher table, if (A)(i)value of the country column is the USA, and (ii)value of the pub_city is New York; or (B)(i)value of the country column is INDIA, and (ii)value of the pub_city is Mumbai.
MySQL CREATE VIEW with GROUP BY
CREATE VIEW command can be used with GROUP BY clause.
Example:
Sample table: book_mast
CREATE VIEW view_bookmast
AS SELECT pub_lang, count(*)
FROM book_mast
GROUP BY pub_lang
Copy
The above statement will create a view ‘view_bookmast’ taking all records grouped w.r.t. pub_lang, from pub_lang and number of books for each language (pub_lang).
MySQL CREATE VIEW with ORDER BY
CREATE VIEW command can be used with ORDER BY clause.
Example:
Sample table: book_mast
CREATE VIEW view_bookmast
AS SELECT pub_lang,count(*)
FROM book_mast
GROUP BY pub_lang ORDER BY pub_lang;
Copy
The above MySQL statement will create a view ‘view_bookmast’ taking all the records grouped w.r.t. pub_lang and sorted against pub_lang, from pub_lang and number of books for each language (pub_lang) of book_mast table.
MySQL CREATE VIEW with BETWEEN and IN
CREATE VIEW command can be used with BETWEEN and IN operator.
Example:
Sample table: book_mast
CREATE VIEW view_bookmast
AS SELECT *
FROM book_mast
WHERE book_name BETWEEN 'A' AND 'G'
AND no_page IN(165,250,350,400,510);
Copy
The above statement will create a view ‘view_bookmast’ taking all the records of book_mast table, if (A)name of the book (book_name) starts with any of the characters from ‘A’ through ‘G’ and (B) number of pages (no_page) are any of the following 165, 250, 350, 400, 510.
MySQL CREATE VIEW with LIKE
CREATE VIEW command can be used with LIKE operator.
Example:
Sample table: author
CREATE VIEW view_author
AS SELECT *
FROM author
WHERE aut_name
NOT LIKE 'T%' AND aut_name NOT LIKE 'W%';
Copy
The above MySQL statement will create a view ‘view_author’ taking all the records of author table if (A)name of the author (aut_name) does not start with ‘T’ and (B) name of the author (aut_name) does not start with ‘W’.
MySQL CREATE VIEW using subqueries
CREATE VIEW command can be used with subqueries.
Example:
Sample table: purchase
Sample table: book_mast
CREATE VIEW view_purchase
AS SELECT invoice_no,book_name,cate_id
FROM purchase
WHERE cate_id= (SELECT cate_id FROM book_mast WHERE no_page=201);
Copy
The above MySQL statement will create a view ‘view_purchase’ taking all the records of invoice_no, book_name and cate_id columns of purchase table, if category id (cate_id) satisfies the condition defined within a subquery (followed by cate_id=).
The subquery retrieves only cate_ids from book_mast table, which contain books with 201 pages.
MySQL CREATE VIEW with JOIN
CREATE VIEW command can be used along with a JOIN statement.
Example:
Sample table: category
Sample table: purchase
CREATE VIEW view_purchase
AS SELECT a.cate_id,a.cate_descrip, b.invoice_no,
b.invoice_dt,b.book_name
FROM category a,purchase b
WHERE a.cate_id=b.cate_id;
Copy
The above MySQL statement will create a view ‘view_purchase’ along with a JOIN statement.
The JOIN statement here retrieves cate_id, cate_descrip from category table and invoice_no, invoice_dt, and book_name from purchase table if cate_id of category table and that of purchase are same.
MySQL CREATE VIEW with UNION
CREATE VIEW command can be used with UNION.
Example:
Sample table: book_mast
CREATE VIEW view_bookmast AS
SELECT *
FROM book_mast
WHERE pub_id='P001' UNION
SELECT *
FROM book_mast
WHERE book_name BETWEEN 'A' AND 'G' UNION
SELECT *
FROM book_mast
WHERE no_page IN(165,250,350,400,510);
Copy
The above MySQL statement will create a view ‘view_bookmast’ contains columns as in the ‘book_mast’.
The records will be inserted with the union of three subqueries.
The first query inserts those rows into the ‘view_bookmast’ view from the ‘book_mast’ table whose ‘pub_id’ is ‘P001’.
The second query inserts those rows into the ‘view_bookmast’ view from the ‘book_mast’ table whose rows have the ‘book_name’ column beginning with any letter between ‘A’ to ‘G’.
The third query inserts those rows into the ‘view_bookmast’ view from the ‘book_mast’ table whose rows have any of the following values 165,250,350,400,510 in ‘no_page’.
Online Practice Editor:https://paiza.io/projects/e/nJh7N3gU3Cm3-HZRkFYkiA?theme=twilight
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
