We can use ROLLBACK command to eliminate the changes, made in a current transaction, permanently from MySQL database. Suppose if we run some DML statements and it updates some data objects, then ROLLBACK command will eliminate these updates permanently from the database. Example Suppose we have the following data in table ‘marks’ and we applied the transaction and ROLLBACK command as follows − mysql> SELECT * FROM Marks; +------+---------+---------+-------+ | Id | Name | Subject | Marks | +------+---------+---------+-------+ | 1 | Aarav | Maths | ... Read More

We can use COMMIT command to make the changes, made in a current transaction, permanently recorded in MySQL database. Suppose if we run some DML statements and it updates some data objects, then COMMIT command will record these updates permanently in the database.Examplemysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO Marks Values(1, 'Aarav', 'Maths', 50); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO Marks Values(2, 'Harshit', 'Maths', 55); Query OK, 1 row affected (0.00 sec) mysql> COMMIT; Query OK, 0 rows affected (0.06 sec)In this example, the COMMIT statement will ... Read More

We can run “SELECT @@AUTOCOMMIT” command to check the current transaction mode.mysql> Select @@AUTOCOMMIT; +--------------------+ | @@AUTOCOMMIT       | +--------------------+ |       1            | +--------------------+ 1 row in set (0.05 sec) mysql> SET AUTOCOMMIT = 0; Query OK, 0 rows affected (0.00 sec) mysql> Select @@AUTOCOMMIT; +--------------------+ | @@AUTOCOMMIT       | +--------------------+ |         0          | +--------------------+ 1 row in set (0.00 sec)

Following are the ways with the help of which current MySQL transaction can be ended implicitly −By Running DDL statementThe current MySQL transaction will end implicitly and changes will be committed by running any of the DDL statement such as CREATE or DROP databases, Create, ALTER or DROP tables or stored routines. It is because, in MySQL, these statements cannot be rolled back.Examplemysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO MARKS Values(3, 'gaurav', 'Comp', 69); Query OK, 1 row affected (0.26 sec) mysql> Create table student(id int, Name Varchar(10), ); Query OK, 0 ... Read More

By using \G at the end of MySQL statement, it returns the output in a vertical format rather than a tabular format. Consider the example below −mysql> Select curdate(); +------------+ | curdate()  | +------------+ | 2017-11-06 | +------------+ 1 row in set (0.00 sec) mysql> Select CURDATE()\G *************************** 1. row *************************** CURDATE(): 2017-11-06 1 row in set (0.00 sec)From the example above, the difference of using \G at the end of the MySQL statement can be understood. It returns the same output in a vertical format rather than a tabular format.

“WITH ROLLUP” is a modifier that is used with GROUP BY clause. Mainly, it causes the summary output to include extra rows that represent higher-level summary operations.ExampleIn the example below, WITH ROLLUP modifier gave the summary output with total price value in the extra row.mysql> Select Item, SUM(Price) AS Price from ratelist Group by item WITH ROLLUP; +------+-------+ | Item | Price | +------+-------+ | A    |   502 | | B    |   630 | | C    |  1005 | | h    |   850 | | T    |   250 | | NULL |  3237 | +------+-------+ 6 rows in set (0.00 sec)

With the help of the following built-in commands, MySQL can execute a query even if semicolon (;) terminator symbol is not used.egoWe can use this command by using \G option. It means to send the current statement to the server to be executed and display the result in vertical format. When we use \G and omitting semicolon(;) in a statement (single or multiple lines), MySQL determines the end of the statement as and when it encounters \G. Consider the example below −mysql> Select * from ratelist\G *************************** 1. row ***************************    Sr: 1  Item: A Price: 502 *************************** 2. row ... Read More

We can also use the BEGIN statement to start a new transaction. It is the same as the START TRANSACTION statement.Examplemysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO Marks Values(1, 'Aarav', 'History', 40); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO Marks Values(2, 'Harshit', 'History', 48); Query OK, 1 row affected (0.00 sec) mysql> ROLLBACK; Query OK, 0 rows affected (0.04 sec)In this example, the transaction is initiated by the BEGIN Statement rather than START TRANSACTION statement. Two INSERT statements are then executed followed by a ROLLBACK statement. ROLLBACK statement will ... Read More

Actually ROLLUP and ORDER BY are mutually exclusive in MySQL hence it is not a good practice to use both of them in a query. But still, if we use ROLLUP in ORDER BY then the main disadvantage is that the summary rows would get sorted along with the rows they are calculated. It is also significant to notice that the sort order will decide the position of summary rows.The summary rows would be at the beginning of ascending order and at the end of descending order. Consider the following example to understand it more clearly −mysql> Select * from ... Read More

In the case, where we use explicit sort order (ASC or DESC) with column names in the GROUP BY list along with the “WITH ROLLUP” modifier, the summary rows added by ROLLUP still appear after the rows from which they calculated regardless of the sort order.As we know that the default sort order is ascending hence in the example below if we will not use any explicit sort order then the output would be as follows −mysql> Select sr, SUM(Price) AS Price from ratelist Group by sr with rollup; +-----+-------+ | sr  | Price | +-----+-------+ |  1  |   ... Read More