To get the last index, use the SUBSTRING_INDEX() function from MySQL. The syntax is as follows −SELECT yourColumnName1, ...N, SUBSTRING_INDEX(yourColumnName, ’yourDelimiter’, -1)as anyVariableName from yourTableName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table LastIndexString    -> (    -> Id int,    -> yourURL text    -> ); Query OK, 0 rows affected (0.89 sec)Insert some records in the table using INSERT command. The query is as follows −mysql> insert into LastIndexString values(1, 'https −//www.example.com/home.html'); Query OK, 1 row affected (0.26 sec) mysql> insert into LastIndexString values(2, ... Read More

The best data type for unix_timestamp in MySQL is integer. The integer data type is as followsint(11);The integer data type is useful for condition checking like ( > , create table UnixTime -> ( -> DueTime datetime -> ); Query OK, 0 rows affected (0.55 sec)Insert records in the form of date using insert command. The query is as followsmysql> insert into UnixTime values(now()); Query OK, 1 row affected (0.15 sec) mysql> insert into UnixTime values('2010-10-14'); Query OK, 1 row affected (0.15 sec) mysql> insert into UnixTime values('2020-09-24'); Query ... Read More

To select yesterday’s date, use the subdate() function from MySQL. The syntax is as followsselect subdate(yourDatetimeColumnName) as anyVariableName from yourTableName;To understand the above syntax, let us create a tablemysql> create table YesterdayDateDemo -> ( -> VisitedDateTime datetime -> ); Query OK, 0 rows affected (0.59 sec)Let us now insert date in the table using insert command. The query is as followsmysql> insert into YesterdayDateDemo values(now()); Query OK, 1 row affected (0.15 sec) mysql> insert into YesterdayDateDemo values('2012-12-26 13:24:35'); Query OK, 1 row affected (0.17 sec) mysql> insert into YesterdayDateDemo values('2013-10-22 12:20:32'); Query OK, 1 row affected (0.16 sec)Let ... Read More

If you are using Windows Operating System, check your directory my.cnf or my.ini file.mysql> select @@datadir;The following is the output+---------------------------------------------+ | @@datadir | +---------------------------------------------+ | C:\ProgramData\MySQL\MySQL Server 8.0\Data\ | +---------------------------------------------+ 1 row in set (0.00 sec)Reach the above location ‘C:\ProgramData\MySQL\MySQL Server 8.0\Data\”. The screenshot is as follows for my.cnf fileOpen the my.cnf file and write the sql_mode="TRADITIONAL". The syntax is as followssql_mode="TRADITIONAL".After that start your server once again.

You can create a parameter using IN and OUT. IN is used to take input parameter and OUT can be used for output.The syntax is as followsDELIMITER // CREATE PROCEDURE yourProcedureName(IN yourParameterName dataType, OUT yourParameterName dataType ) BEGIN yourStatement1; yourStatement2; . . N END; // DELIMITER ;First, we will create a table. The query to create a table is as followsmysql> create table SumOfAll -> ( -> Amount int -> ); Query OK, 0 rows affected (0.78 sec)Insert some records in the table using insert command. The query ... Read More

This error occurs when you drop a user with localhost while you have created a user with ‘%’.Let us create a user with ‘%’ and drop the user as a localhost. The syntax is as followsCREATE USER 'yourUserName'@'%' IDENTIFIED BY 'yourPassword';Let us create a user using the above syntax. The query to create a user is as followsmysql> CREATE USER 'Jack'@'%' IDENTIFIED BY '1234'; Query OK, 0 rows affected (0.26 sec)Check user is created successfully or notmysql> select user, host from MySQL.user;The following is the output+------------------+-----------+ | user             | host      | +------------------+-----------+ ... Read More

You can use SUBSTRING() from MySQL to limit length of strings. The syntax is as followsSELECT SUBSTRING(yourColumnName, 1, yourIntegerValueToGetTheCharacters) as anyVariableName from yourTableName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table limitLengthOfLongTextDemo -> ( -> sentence LONGTEXT -> ); Query OK, 0 rows affected (0.74 sec)Insert some records in the table using insert command. The query is as followsmysql> insert into limitLengthOfLongTextDemo values('This is the introduction to MySQL'); Query OK, 1 row affected (0.17 sec) mysql> insert into ... Read More

You can use multiplication operator (*) between two cells. The syntax is as followsSELECT yourColumnName1, yourColumnName2, yourColumnName1*yourColumnName2 as ‘anyVariableName’ from yourTableName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table MultiplicationDemo    -> (    -> FirstPrice int,    -> SecondPrice int    -> ); Query OK, 0 rows affected (0.63 sec)Now you can display all records from the table using insert command. The query is as followsmysql> insert into MultiplicationDemo values(10, 2); Query OK, 1 row affected (0.17 sec) mysql> insert into MultiplicationDemo values(4, 2); Query OK, ... Read More

To select last 20 records in ascending order, you can use subquery LIMIT clause. The syntax is as followsSELECT *FROM (    SELECT *FROM yourTableName ORDER BY yourColumnName desc limit 20 ) anyVariableName order by anyVariableName.yourColumnName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table ProductInformation    -> (    -> ProductId int,    -> ProductName varchar(100),    -> ProductPrice int    -> ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table using insert command. The query is as followsmysql> insert into ProductInformation values(101, 'Product-1', ... Read More

You can add a not null column at the time of table creation or you can use it for an existing table.Case 1 − Add a not null column at the time of creating a table. The syntax is as followsCREATE TABLE yourTableName (    yourColumnName1 dataType NOT NULL,    yourColumnName2 dataType    .      .    .    N );The query to create a table is as followsmysql> create table NotNullAtCreationOfTable    -> (    -> Id int not null,    -> Name varchar(100)    -> ); Query OK, 0 rows affected (0.60 sec)In the above table, we ... Read More