To remove leading and trailing space, we can use the trim() in MySQL. Firstly, we will create a table with the help of CREATE command.Creating a table −mysql> CREATE table TrailingANdLeadingDemo -> ( -> SpaceTrailLead varchar(100) -> ); Query OK, 0 rows affected (0.57 sec)After creating a table, we will insert a record with the help of INSERT command. Let us insert a record with leading and trailing space −mysql> INSERT into TrailingANdLeadingDemo values(' john '); Query OK, 1 row affected (0.12 sec)We can display all the records with the help of SELECT commandDisplaying recordsmysql> SELECT * from TrailingANdLeadingDemo; The ... Read More

The straight join in MySQL works like inner join or join. This means that it returns only the matching rows. Firstly, we need to understand Straight join in MySQL. For that, we need to create two tables and relate both the tables with foreign key constraints.Here is the first tablemysql> CREATE table ForeignTableDemo -> ( -> Id int, -> Name varchar(100), -> FK int -> ); Query OK, 0 rows affected (0.47 sec)Here is the second table −mysql> CREATE table PrimaryTableDemo -> ( -> FK int, -> Address varchar(100), -> primary key(FK) -> ); Query OK, 0 rows affected (0.47 ... Read More

To measure actual MySQL query time, we can use the concept of profiling that must be set to 1 before executing the query.The order must be like this.Set profiling to 1 Then execute query Then show profilesNow, I am applying the above order to get the actual MySQL query time −mysql> SET profiling = 1; Query OK, 0 rows affected, 1 warning (0.00 sec)After that I am executing the following query −mysql> SELECT * from MilliSecondDemo; The following is the output+-------------------------+ | MyTimeInMillSec | +-------------------------+ | 2018-10-08 15:19:50.202 | +-------------------------+ 1 row ... Read More

To save time in milliseconds, we can use now(3) function because “milli 3” can be used for te same purpose. Firstly, I will create a table with the help of CREATE command −mysql> CREATE table MilliSecondDemo -> ( -> MyTimeInMillSec datetime(3) -> ); Query OK, 0 rows affected (0.70 sec)Inserting record into the table −mysql> INSERT into MilliSecondDemo values(now(3)); Query OK, 1 row affected (0.98 sec)Let us now view the table records −mysql> SELECT * from MilliSecondDemo; The following is the output+-------------------------+ | MyTimeInMillSec | +-------------------------+ | 2018-10-08 15:19:50.202 | +-------------------------+ 1 row in set (0.00 sec)

To select a column name with spaces, use the back tick symbol with column name. The symbol is ( ` `). Back tick is displayed in the keyboard below the tilde operator ( ~).Firstly, create a table −mysql> CREATE table SpaceColumn -> ( -> `Student Name` varchar(100) -> ); Query OK, 0 rows affected (0.48 sec)Inserting recordsmysql> INSERT into SpaceColumn values('John'); Query OK, 1 row affected (0.18 sec) mysql> INSERT into SpaceColumn values('Bob'); Query OK, 1 row affected (0.17 sec)The syntax to get column name with space is as follows −SELECT `column_name` from yourTableName; Now I will apply the ... Read More

Firstly, we will create a table with the help of CREATE command.Creating a table −mysql> CREATE table InCaseSensDemo -> ( -> Name varchar(100) -> ); Query OK, 0 rows affected (0.50 sec)Inserting records into the table with the help of INSERT command −mysql> INSERT into InCaseSensDemo values('JOhN'); Query OK, 1 row affected (0.11 sec) mysql> INSERT into InCaseSensDemo values('bob'); Query OK, 1 row affected (0.21 sec) mysql> INSERT into InCaseSensDemo values('BoB'); Query OK, 1 row affected (0.13 sec) mysql> INSERT into InCaseSensDemo values('Bob'); Query OK, 1 row affected (0.18 sec)Displaying all the records with the help of ... Read More

We can do this with the help of lower() with column name. Firstly, we will create a table with the help of CREATE command.Creating a table −mysql> CREATE table InCaseSensDemo -> ( -> Name varchar(100) -> ); Query OK, 0 rows affected (0.50 sec)Inserting records into the table with the help of INSERT command −mysql> INSERT into InCaseSensDemo values('JOhN'); Query OK, 1 row affected (0.11 sec) mysql> INSERT into InCaseSensDemo values('bob'); Query OK, 1 row affected (0.21 sec) mysql> INSERT into InCaseSensDemo values('BoB'); Query OK, 1 row affected (0.13 sec) mysql> INSERT into InCaseSensDemo values('Bob'); Query OK, ... Read More

SELECT DISTINCT can be used to give distinct values. Use it to remove duplicate records and it can be used with aggregate function as well. For example: MAX, AVG etc. This can be applied on a single column.Now, I am creating a table to use SELECT DISTINCT for a column. Creating a table with the help of CREATE command −mysql> CREATE TABLE DistinctDemo -> ( -> id int, -> name varchar(100) -> ); Query OK, 0 rows affected (0.64 sec)Inserting records −mysql> INSERT into DistinctDemo values(1, 'John'); Query OK, 1 row affected (0.17 sec) mysql> INSERT into DistinctDemo values(2, ... Read More

To add 1 day, use date_add() function. Adding a day to datetime in MySQL gives the next day. The following is the syntax −SELECT DATE_ADD('Any date’', INTERVAL 1 DAY) AS AliasName;Now, I am applying the above query to add a day with date in MySQL. The query is as follows −mysql> SELECT DATE_ADD('2018-10-08', INTERVAL 1 DAY) AS NextDay; After executing the above query, we will get the following output −+------------+ | NextDay | +------------+ | 2018-10-09 | +------------+ 1 row in set (0.00 sec)Therefore, in this sample output, I am adding a day with current date

To enable query log, use the command SET global. You cannot use set general_log in MySQL version 8.0.12 version. If you are using version 8.0.12, then you will get the below given error. Here, we are trying to set general_log to see what error will come −mysql> SET general_log = 1;The following is the output −ERROR 1229 (HY000): Variable 'general_log' is a GLOBAL variable and should be set with SET GLOBAL Now, we will use the global command with general_log. The query is as follows −mysql> SET global general_log = 1; Query OK, 0 rows affected (0.10 sec)To check it ... Read More