MySQL Articles - Page 365 of 439

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 More

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.

From the output returned by MySQL, it is very much clear that how many rows are there in the result set along with the execution time.ExampleFor example, in the following MySQL output we can see there are 3 rows in the result set.mysql> Select * from ratelist ORDER BY Price LIMIT 3; +----+------+-------+ | Sr | Item | Price | +----+------+-------+ |  5 | T    |   250 | |  1 | A    |   502 | |  2 | B    |   630 | +----+------+-------+ 3 rows in set (0.00 sec)But suppose if MySQL query has ... Read More

We can specify the number of records to be returned in output by adding a LIMIT clause in MySQL query. LIMIT clause restricts the number of rows to be returned. Consider the following example −mysql> Select * from ratelist ORDER BY Price; +----+------+-------+ | Sr | Item | Price | +----+------+-------+ |  5 | T    |   250 | |  1 | A    |   502 | |  2 | B    |   630 | |  4 | h    |   850 | |  3 | C    |  1005 | +----+------+-------+ 5 rows in set ... Read More

Actually, as we know that we can specify a sort order with the help of the ORDER BY clause. We need to write the ORDER BY keyword followed by the name of the column on which we want to sort the table. It is not necessary that we have to use that column name after the SELECT keyword in the query.Examplemysql> Select Sr, Item from ratelist ORDER BY Price; +----+------+ | Sr | Item | +----+------+ |  5 | T    | |  1 | A    | |  2 | B    | |  4 | h    | ... Read More

We can use a subquery as a table in the FROM clause in the same way as the result of a subquery can be used with an operator in the WHERE clause. In the following example, we are using the result of subquery as a table by writing it after the FROM clause. It is mandatory to use an alias after subquery, here we are using alias ‘C_car’. To demonstrate it we are using the data as follows from table ‘Cars’ −mysql> Select * from Cars; +------+--------------+---------+ | ID   | Name         | Price   | ... Read More

To make it understand we are using the data from the following tables −mysql> Select * from customers; +-------------+----------+ | Customer_Id | Name     | +-------------+----------+ | 1           | Rahul    | | 2           | Yashpal  | | 3           | Gaurav   | | 4           | Virender | +-------------+----------+ 4 rows in set (0.00 sec) mysql> Select * from reserve; +------+------------+ | ID   | Day        | +------+------------+ | 1    | 2017-12-30 | | ... Read More

To make it understand we are using the data from the following tables −mysql> Select * from Customers; +-------------+----------+ | Customer_Id | Name     | +-------------+----------+ | 1           | Rahul    | | 2           | Yashpal  | | 3           | Gaurav   | | 4           | Virender | +-------------+----------+ 4 rows in set (0.00 sec) mysql> Select * from Reserve; +------+------------+ | ID   | Day        | +------+------------+ | 1    | 2017-12-30 | | ... Read More

To make it understand we are using the data from the following tables −mysql> Select * from customers; +-------------+----------+ | Customer_Id | Name     | +-------------+----------+ |           1 | Rahul    | |           2 | Yashpal  | |           3 | Gaurav   | |           4 | Virender | +-------------+----------+ 4 rows in set (0.00 sec) mysql> Select * from reserve; +------+------------+ | ID   | Day        | +------+------------+ |    1 | 2017-12-30 | | ... Read More

With the help of SHOW TABLES From Database_name query, we can see the tables of another database. Here Database_name is the name of the database which we are not using currently. Consider the following example in which we run the query for getting the list of tables in database name ‘tutorial’.mysql> show tables from tutorial; +--------------------+ | Tables_in_tutorial | +--------------------+ | employee           | | showzerofill       | | student            | +--------------------+ 3 rows in set (0.00 sec)