The default MySQL output would be different if we will run the same query interactively or in batch mode. For example, if we will run the query select * from hh interactively then following would be a format of output −mysql> select * from hh; +------+ | id   | +------+ |  1   | |  2   | +------+ 2 rows in set (0.01 sec)On the other hand, if we will run the same query in batch mode then following would be the format of output −C:\Program Files\MySQL\bin>mysql -u root -p gaurav < hh.sql Enter password: ***** id 1 2

We need to create a .sql file for running MySQL in batch mode. This file will contain the MySQL statements. Suppose I have hh.sql file in which I have written the statement select * from hh. With the help of the following command, we can run this file in batch mode −ExampleC:\Program Files\MySQL\bin>mysql -u root -p gaurav < hh.sql Enter password: *****Outputid 1 2Here Gaurav is the database name that contains the table hh. Whenever you’ll run this command it will ask for the password and then give the output.

Yes, we require authentication for login into MySQL command line tool. For example, if we are trying to log in from windows command line then it will prompt for the password every time. The command for login is as follows −C:\Program Files\MySQL\bin>mysql -u root -p Enter password: *****

It can be done with the following three ways in MySQLBy using EXTRACT() function For extracting YEAR and MONTH collectively then we can use the EXTRACT function. We need to provide the YEAR_MONTH as an argument for this function. To understand it, consider the following function using the data from table ‘Collegedetail’ −mysql> Select EXTRACT(YEAR_MONTH From estb) from collegedetail; +-------------------------------+ | EXTRACT(YEAR_MONTH From estb) | +-------------------------------+ |                        201005 | |                        199510 | |             ... Read More

We can get the list of MySQL server-side help categories by giving the keyword contents to the help command.mysql> help contents You asked for help about help category: "Contents" For more information, type 'help ', where is one of the following categories:    Account Management    Administration    Compound Statements    Data Definition    Data Manipulation    Data Types    Functions    Functions and Modifiers for Use with GROUP BY    Geographic Features    Help Metadata    Language Structure    Plugins    Procedures    Storage Engines    Table Maintenance    Transactions    User-Defined Functions    Utility

By using the WHERE clause with any of the MySQL date functions, the query will filter the rows based on the condition provided in the WHERE clause. To understand it, consider the data from ‘Collegedetail’ table as followsmysql> Select * from Collegedetail; +------+---------+------------+ | ID   | Country | Estb       | +------+---------+------------+ | 111  | INDIA   | 2010-05-01 | | 130  | INDIA   | 1995-10-25 | | 139  | USA     | 1994-09-25 | | 1539 | UK      | 2001-07-23 | | 1545 | Russia  | 2010-07-30 | +------+---------+------------+ 5 rows in ... Read More

To check this we need to have the profiling information which indicates resource usage for the statements executed during the course of the current session. Profiling information can get by SHOW PROFILE and SHOW PROFILES statement. Before running these statements, the profiling session variable must be set to 1 as follows − mysql> set profiling = 1; Query OK, 0 rows affected (0.00 sec) Now if we will run SHOW PROFILES statement then it will display the list of most recent statements sent to the server along with the duration and query id. mysql> show profiles; +----------+------------+--------------------------------------+ ... Read More

It would be more convenient to find a record if we will use ORDER BY clause while calculating the date. To understand it, we have the data from table ‘Collegedetail’ as follows −mysql> Select * from Collegedetail; +------+---------+------------+ | ID   | Country | Estb       | +------+---------+------------+ | 111  | INDIA   | 2010-05-01 | | 130  | INDIA   | 1995-10-25 | | 139  | USA     | 1994-09-25 | | 1539 | UK      | 2001-07-23 | | 1545 | Russia  | 2010-07-30 | +------+---------+------------+ 5 rows in set (0.00 sec)Now, suppose if ... Read More

In MySQL, we can use the following functions to calculate the Date −CURDATE() Function − Basically it returns the current date of the computer.YEAR() Function − It returns the year of the specified date.MONTH() function − It returns the month of the specified date.DAY() Function − It returns the day of the specified date.RIGHT() Function − It returns the number of character as specified within the function from the given date. The part of the expression that compares the returns from RIGHT() function evaluates 1 or 0.To understand it, consider the data, as follows, from a table named ‘Collegedetail’ −mysql> ... Read More

Actually, there is no single function in MySQL to capitalize only first letter of the string. We need to use nesting of functions and for this case, we can use UPPER() and LOWER() with SUBSTRING() functions. To understand it, we are using data, given as below, from ‘emp_tbl’.mysql> Select * from emp_tbl; +----+----------------+ | Id | Name           | +----+----------------+ | 1  | rahul singh    | | 2  | gaurav kumar   | | 3  | yashpal sharma | | 4  | krishan kumar  | | 5  | kuldeep rai    | | 6  | ... Read More