Found 4219 Articles for MySQLi

You need to use subquery with select statement, one select for inner and one for outer. The inner select will return rows and outer will order by ascending order. The syntax is as follows:SELECT *FROM (    SELECT *FROM yourTableName ORDER BY yourColumnName1 DESC LIMIT 9 ) AS anyAliasName ORDER BY yourColumnName2;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table OrderByAfterLimit    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> UserName varchar(20),    -> UserAge int,    -> PRIMARY KEY(Id)    -> ); Query OK, ... Read More

To populate a table from query results, use the following syntax:INSERT yourTableName(yourColumnName1, yourColumnName2, yourColumnName3, ..........N) SELECT yourColumnName1, yourColumnName2, yourColumnName3, ..........N FROM yourAnotherTableName;To understand the above syntax, let us create a table. The first table is as follows with some records. The query to create a table is as follows:mysql> create table PopulateTableDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(20),    -> Amount int,    -> ArrivalDateTime datetime,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.68 sec)Now you can insert some records in the table using insert command. The ... Read More

If you want to login as a different user on MySQL, you need to use “mysql -u -p command”. The syntax is as follows to login as a different user.>mysql -u yourUsername -p After pressing enter key Enter password −To understand the above syntax, let us create a user in MySQL. The syntax is as follows −CREATE USER 'yourUserName'@'localhost' IDENTIFIED BY 'yourPassword';Now I am going to create a user with name ‘John’ and password is ‘john123456’. The query is as follows −mysql> CREATE USER 'John'@'localhost' IDENTIFIED BY 'john123456'; Query OK, 0 rows affected (0.15 sec)Now check the user has been ... Read More

To delete last record (on condition) from a table, you need to use ORDER BY DESC with LIMIT 1. The syntax is as follows:DELETE FROM yourTableName WHERE yourColumnName1=yourValue ORDER BY yourColumnName2 DESC LIMIT 1;The above syntax will delete last record (on condition) from a table. It sorts the column in descending order and choose the first element to delete.To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table UserLoginTable    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> UserId int,    -> UserLoginDateTime datetime,    -> PRIMARY ... Read More

You need to use regular expression with ORDER BY clause. The syntax is as follows:SELECT *FROM yourTableName ORDER BY IF(yourColumnName RLIKE '^[a-z]', 1, 2), yourColumnName;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table AlphabetFirstThenNumberDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(20),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.95 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into AlphabetFirstThenNumberDemo(Name) values('John'); Query OK, 1 row affected (0.37 sec) mysql> insert ... Read More

To select multiple sum columns with MySQL query and display them in separate columns, you need to use CASE statement. The syntax is as follows:SELECT SUM( CASE WHEN yourColumnName1=’yourValue1’ THEN yourColumnName2 END ) AS yourSeparateColumnName1, SUM( CASE WHEN yourColumnName1=’yourValue2’ THEN yourColumnName2 END ) AS yourSeparateColumnName2, SUM( CASE WHEN yourColumnName1=’yourValue3’ THEN yourColumnName2 END ) AS yourSeparateColumnName3, . . . N FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table selectMultipleSumDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> PlayerName varchar(20),    -> PlayerScore int, ... Read More

To select max of mixed string/int column, you need to use substring() function. The syntax is as follows:SELECT MAX(CAST(SUBSTRING(yourColumnName, 4, length(yourColumnName)-3) AS UNSIGNED)) AS anyVariableName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table StringIntMixHighestDemo    -> (    -> InvoiceId int NOT NULL AUTO_INCREMENT,    -> InvoiceNumber varchar(20),    -> PRIMARY KEY(InvoiceId)    -> ); Query OK, 0 rows affected (0.65 sec)Now you can insert some records in the table using insert command. The query is as follows:mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV129'); Query OK, 1 row ... Read More

You need to use backticks around table name as well as database name. The syntax is as follows:UPDATE `yourDatabaseName`.`yourTableName` SET yourColumnName1=yourColumnName1+1 WHERE yourColumnName2=’yourValue’;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> use test; Database changed mysql> create table Add1Demo    -> (    -> Id varchar(10),    -> Value int    -> ); Query OK, 0 rows affected (1.19 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into Add1Demo values('1', 780); Query OK, 1 row affected (0.17 sec) mysql> insert into Add1Demo values('2', ... Read More

You need to use CONVERT() function along with binary keyword. The syntax is as follows −SELECT CONVERT(binary CONVERT(yourColumnName using latin1) USING UTF8) 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 UtfDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(15),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.76 sec)Insert some records in the table using INSERT command. The query is as follows −mysql> insert into UtfDemo(Name) values('Obama’s'); Query OK, 1 row affected (0.28 ... Read More

You can achieve it with the help of GROUP_CONCAT() function. The syntax is as follows −SELECT yourColumnName1, yourColumnName2, yourColumnName3, ..N, GROUP_CONCAT(yourColumnName4) as anyAliasName FROM yourTableName group by yourColumnName3, yourColumnName1, yourColumnName2;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table CommaDelimitedList    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(10),    -> GroupId int,    -> CompanyName varchar(15),    -> RefId int,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.68 sec)Insert some records in the table using INSERT command. ... Read More