To get the last access time, try the following syntax −SELECT update_time FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'yourDatabaseName' AND table_name = 'yourTableName'The above syntax gives the last access information about MyISAM engine type.Here, our database is ‘business’ and we will be using the table with the name ‘twoprimarykeytabledemo'.To get last access time of MySQL database, use the following query.Case 1 − The query is as follows −mysql> SELECT update_time    -> FROM INFORMATION_SCHEMA.TABLES    -> WHERE table_schema = 'business'    -> AND table_name = 'twoprimarykeytabledemo';The output is as follows −+---------------------+ | UPDATE_TIME ... Read More

The smallest datatype for one bit can be bit(1). The syntax is as follows −yourColumnName bit(1)To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table bitDemo    -> (    -> isValid bit(1)    -> ); Query OK, 0 rows affected (0.49 sec)Now you can check all the details of table with the help of SHOW CREATE command. The query is as follows −mysql> show create table bitDemo;Here is the output −+---------+-----------------------------------------------------------------------------------------------------------------------------+ | Table   | Create Table   ... Read More

The localhost means you can access from same machine while from % the remote host access is possible. The syntax is as follows to change the user password.SET PASSWORD FOR 'yourUserName'@'localhost' ='yourPassword';First check the user and host from MySQL.user table. The query is as follows −mysql> select user, host from MySQL.user;Here is the output −+------------------+-----------+ | user             | host      | +------------------+-----------+ | Bob              | %       | | Manish | % ... Read More

To select timestamp as date string in MySQL, the syntax is as follows −select FROM_UNIXTIME(yourColumnName, '%Y-%m-%d %H:%i:%s') from yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table select_timestampDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> ArrivalDateTime int    -> ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into select_timestampDemo(ArrivalDateTime) values(1546499730); Query OK, 1 row affected (0.18 sec) mysql> insert into select_timestampDemo(ArrivalDateTime) values(1546210820); Query OK, 1 ... Read More

To implement AND condition, the syntax is as follows −select *from yourTableName where yourColumnName1 = yourValue1 AND yourColumnName2 = yourValue2;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table MySQLANDConditionDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> Name varchar(100),    -> Age int    -> ); Query OK, 0 rows affected (0.80 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into MySQLANDConditionDemo(Name, Age) values('Larry', 23); Query OK, 1 row affected (0.11 sec) mysql> ... Read More

You can easily insert a row into a table that has only a single auto increment column. The syntax is as follows −insert into yourTableName set yourColumnName =NULL;You can use the below syntax −insert into yourTableName values(NULL);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table singleAutoIncrementColumnDemo    -> (    -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY    -> ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into singleAutoIncrementColumnDemo set UserId ... Read More

You can use GLOBAL variable as shown below −SET global innodb_stats_on_metadata =0;After including the above syntax, the INFORMATION_SCHEMA.key_column_usage will take less time and that would improve the performance.The query is as follows −mysql> set global innodb_stats_on_metadata =0; Query OK, 0 rows affected (0.00 sec) mysql> SELECT REFERENCED_TABLE_NAME,TABLE_NAME,COLUMN_NAME,CONSTRAINT_SCHEMA -> FROM INFORMATION_SCHEMA.key_column_usage;The following is the output −It returns 674 rows in 0.28 seconds.

To ignore the year with date range, use the DATE_FORMAT() with the between clause. Let us first create a demo table. The query to create a table is as follows −mysql> create table igonreYearDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> ShippingDate date    -> ); Query OK, 0 rows affected (0.75 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into igonreYearDemo(ShippingDate) values('2016-01-31'); Query OK, 1 row affected (0.16 sec) mysql> insert into igonreYearDemo(ShippingDate) values('2018-01-31'); Query OK, 1 row affected (0.13 sec) mysql> insert into ... Read More

To get the number of days between current date and date field, the syntax is as follows −SELECT DATEDIFF(CURDATE(), STR_TO_DATE(yourColumnName, '%d-%m-%Y')) AS anyAliasName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table DateDifferenceDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> ArrivalDate varchar(100)    -> ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into DateDifferenceDemo(ArrivalDate) values('12-10-2011'); Query OK, 1 row affected (0.14 sec) mysql> insert ... Read More

To reserve MySQL auto-incremented IDs, the syntax is as follows −START TRANSACTION; insert into yourTableName values(), (), (), (); ROLLBACK; SELECT LAST_INSERT_ID() INTO @anyVariableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table reservingAutoIncrementDemo    -> (    -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY    -> ); Query OK, 0 rows affected (0.45 sec)Insert some records in the table using insert command. The query is as follows −mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> insert into reservingAutoIncrementDemo values(), (), (), (); Query ... Read More