Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
MySQL Articles
Page 74 of 355
How can a user implicitly end current MySQL transaction?
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 MoreHow changes, made in the current transaction, can be permanently recordednin MySQL database?
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 MoreWhat happens if I use both G and semicolon (;) termination symbol with a single MySQL statement?
As we know that \G option sends the command to MySQL server for execution and with the help of Semicolon (;) MySQL determines the end of the statement. It is also known that both of them have a different format of the result set.Now, if we will use both of those in MySQL statement then the output would be produced on the basis that which of them is encountered first by MySQL. For others, MySQL will produce an error. It can be understood with the help of the following example −mysql> Select CURDATE();\G +------------+ | CURDATE() | +------------+ | 2017-11-06 ...
Read MoreHow can I combine built-in-commands (g and G), used for executing a MySQL statement, with termination symbol semicolon (;) to get output without any error?
As we know that built-in-commands (\G and \g) send the command to MySQL server for execution and with the help of Semicolon (;) MySQL determines the end of the statement. It is also known that both of them have different format of the result set. For combining them and getting the result without error, we need to write two queries, one query with either \G or \g and other with a semicolon (;) at the end, in a single statement.ExampleCombining \G and Semicolon (;) −mysql> Select * from student\G select * from ratelist; *************************** 1. row *************************** Name: Gaurav ...
Read MoreIn MySQL, how we can get the total value by category in one output row?
With the help of the MySQL SUM() function, we can get the total value by category in one output row. For example in table ‘ratelist’ if we want to get the total value of category ‘price’ then we can use SUM() on price as follows −mysql> select SUM(price) as totalprice from ratelist; +------------+ | totalprice | +------------+ | 3237 | +------------+ 1 row in set (0.00 sec)The query above returns the total value of price in one output row.
Read MoreHow MySQL manage the behavior of a transaction?
MySQL can manage the behavior of a transaction with the help of the following two modes −Autocommit OnIt is the default mode. In this mode, each MySQL statement (within a transaction or not) is considered as a complete transaction and committed by default when it finishes. It can be started by setting the session variable AUTOCOMMIT to 1 as follows −SET AUTOCOMMIT = 1 mysql> SET AUTOCOMMIT = 1; Query OK, 0 rows affected (0.07 sec)Autocommit OffIt is not the default mode. In this mode, the subsequent series of MySQL statements act like a transaction, and no activities are committed ...
Read MoreWhile using the ROLLUP modifier, is it possible to use a MySQL ORDER BY clause to sort the result?
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 MoreWhat would be the effect on summary output when I use explicit sort order (ASC or DESC) with column names in the GROUP BY list along with "WITH ROLLUP" modifier?
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 MoreWhich statement, other than START TRANSACTION, is used for starting a transaction?
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 MoreHow can MySQL produce the output in a vertical format rather than tabular format?
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.
Read More