MySQLi Articles - Page 248 of 388

To compare two tables and return missing ids, you need to use MySQL LEFT OUTER JOIN.Let us create a table with sample fields and then we will insert records. The query to create the first table −First_Tablemysql> create table First_Table    -> (    -> Id int    -> ); Query OK, 0 rows affected (0.88 sec)Now insert some records in the table using insert command. The query is as follows −mysql> insert into First_Table values(1); Query OK, 1 row affected (0.68 sec) mysql> insert into First_Table values(2); Query OK, 1 row affected (0.29 sec) mysql> insert into First_Table values(3); ... Read More

You can search between dates stored as varchar using STR_TO_DATE(). The syntax is as follows −select *from yourTableName where STR_TO_DATE(LEFT(yourColumnName, LOCATE('', yourColumnName)), '%m/%d/%Y') BETWEEN 'yourDateValue1' AND 'yourDateValue2’;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table SearchDateAsVarchar    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> ShippingDate varchar(100),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.99 sec)Insert some records in the table using INSERT command. The query is as follows −mysql> insert into SearchDateAsVarchar(ShippingDate) values('6/28/2011 9:58 AM'); Query OK, 1 ... Read More

Yes, you need to use AND or OR operator. The syntax is as follows −select *from yourTableName where yourColumnName1=yourValue AND yourColumnName=yourValue';For AND condition, both conditions must be true otherwise you will get an empty set.To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table WhereDemo -> ( -> Id int, -> Name varchar(20) -> ); Query OK, 0 rows affected (0.56 sec)Now you can insert some records in the table using insert command. The query is ... Read More

To correctly use aggregate function with where clause in MySQL, the following is the syntax −select *from yourTableName where yourColumnName > (select AVG(yourColumnName) from yourTableName);To understand the above concept, let us create a table. The query to create a table is as follows −mysql> create table EmployeeInformation    -> (    -> EmployeeId int,    -> EmployeeName varchar(20),    -> EmployeeSalary int,    -> EmployeeDateOfBirth datetime    -> ); Query OK, 0 rows affected (1.08 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into EmployeeInformation values(101, 'John', 5510, '1995-01-21'); ... Read More

You need to use != operator along with subquery. The syntax is as follows −select *from yourTableName where yourIdColumnName != (select max(yourIdColumnName) from yourTableName );To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table AllRecordsExceptLastOne    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> UserName varchar(10),    -> UserAge int    -> ,    -> PRIMARY KEY(Id)    -> ); 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> ... Read More

To check the read/write ratio, you need to use SHOW STATUS command. This will give all the ratios.Case 1 − The syntax is as follows to get the read/write ratio −SHOW STATUS LIKE ‘Com_%’;Case 2 − If you want the insert, update, select and delete ratio, use the below syntax −SHOW GLOBAL STATUS WHERE Variable_name = 'Com_insert' OR Variable_name = 'Com_select' OR Variable_name = 'Com_Update' OR Variable_name = 'Com_delete';Here is no need to do any changes in the above syntax. The query is as follows −mysql> SHOW GLOBAL STATUS WHERE Variable_name = 'Com_insert' OR Variable_name = 'Com_select' OR    -> ... Read More

To select part of a timestamp in a query, you need to use YEAR() function. The syntax is as follows in MySQL.select YEAR(yourTimestampColumnName) 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 SelectPartOfTimestampDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> ShippingTime TIMESTAMP -> ); Query OK, 0 rows affected (1.11 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> ... Read More

To perform search/ replace for only the first occurrence, use the CONCAT and REPLACE() function.The query is as follows to set user defined session variable −mysql> set @Sentence='Thks ks is a my string'; Query OK, 0 rows affected (0.00 sec)In this k will be replaced with i only once. The query is as follows. We have used INSTR() also −mysql> select @Sentence as NewString ,CONCAT(REPLACE(LEFT(@Sentence, INSTR(@Sentence, 'k')), 'k', 'i'),    -> SUBSTRING(@Sentence, INSTR(@Sentence, 'k') + 1)) as ChangeOnlyOneTime;The following is the output displaying only the first occurrence of a character is replaced −+------------------------+------------------------+ | NewString ... Read More

To exactly count all rows, you need to use the aggregate function COUNT(*). The syntax is as follows −select count(*) 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 CountAllRowsDemo    -> (    -> Id int,    -> Name varchar(10),    -> Age int    -> ); Query OK, 0 rows affected (1.49 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into CountAllRowsDemo values(1, 'John', 23); Query OK, 1 row affected (0.15 ... Read More

There is no equivalent of ROW_NUMBER() in MySQL for inserting but you can achieve this with the help of variable. The syntax is as follows −SELECT (@yourVariableName:=@yourVariableName + 1) AS `anyAliasName`, yourColumnName1, yourColumnName2, ...N FROM yourTableName ,(SELECT @yourVariableName:=0) AS anyAliasName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table RowNumberDemo -> ( -> UserId int, -> UserName varchar(20) -> ); Query OK, 0 rows affected (0.74 sec)Insert some records in the table using insert command. The ... Read More