Found 4219 Articles for MySQLi

You can use CRC32 checksum for this. The syntax is as follows −SELECT SUM(CRC32(yourColumnName)) 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 CRC32Demo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> UserId varchar(20)    -> ); Query OK, 0 rows affected (0.67 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into CRC32Demo(UserId) values('USER-1'); Query OK, 1 row affected (0.38 sec) mysql> insert into CRC32Demo(UserId) values('USER-123'); Query OK, 1 row ... Read More

You can use aggregate function SUM() with GROUP BY clause to achieve this.Let us create a table. The query to create a table is as follows −mysql> create table SelectPerMonthDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> Price int,    -> PurchaseDate datetime    -> ); Query OK, 0 rows affected (2.34 sec)ExampleInsert some records in the table using insert command with one of them would be the date of purchase. The query is as follows −mysql> insert into SelectPerMonthDemo(Price, PurchaseDate) values(600, date_add(now(), interval -1 month)); Query OK, 1 row affected (0.42 sec) ... Read More

You can create a temporary table with data type DECIMAL to get a warning when a float value is inserted into an int column. Display the same warning using SHOW WARNINGS.Let us create a table to understand. The query is as follows to create a table.mysql> create temporary table WarningDemo    -> (    -> Value DECIMAL    -> ); Query OK, 0 rows affected (0.13 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into WarningDemo values(9.80); Query OK, 1 row affected, 1 warning (0.03 sec)Here we are getting a warning. Let ... Read More

Using IN() sorts the result for the specific field. To avoid this, use ORDER BY and FIND_IN_SET() for the field.To understand the find_in_set(), let us create a table. The query to create a table is as follows −mysql> create table ProductStock    -> (    -> ProductId int,    -> ProductName varchar(20),    -> ProductQuantity int,    -> ProductPrice float    -> ); Query OK, 0 rows affected (0.79 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into ProductStock values(1, 'Product-101', 10, 500.56); Query OK, 1 row affected (0.20 ... Read More

Instead of using ORDER BY inside GROUP BY, you can use the MAX() aggregate function.The syntax is as follows −SELECT yourNameColumnName, MAX(yourRankColumnName) FROM yourTableName GROUP BY yourNameColumnName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table MaxDemo    -> (    -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> UserName varchar(20),    -> UserRank int    -> ); Query OK, 0 rows affected (0.77 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into MaxDemo(UserName, UserRank) values('Larry', 2); Query ... Read More

Yes, you can use LIMIT 1 with SELECT1.Suppose, you are using SELECT 1 and your table has billions of records. In this case, it will print 1 billion times.The syntax of SELECT 1 is as follows −SELECT 1 FROM yourTableName;Suppose, you are using LIMIT 1 and your table has billions of records. This case, it will print 1 only once.The syntax of SELECT 1 with LIMIT 1 is as follows −SELECT 1 FROM yourTableName LIMIT 1;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table Select1AndLimit1Demo    -> ... Read More

You can achieve this with the help of GROUP BY HAVING clause. The syntax is as follows −SELECT yourColumnName1,    SUM(yourCoumnName2)    from yourTableName    GROUP BY yourColumnName1 HAVING COUNT(yourCoumnName2) = COUNT(*);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table SumDemo    -> (    -> Id int,    -> Amount int    -> ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into SumDemo values(1, 200); Query OK, 1 row affected (0.22 ... Read More

You can use if statement in a stored procedure with multiple conditions with the help of AND or OR operator. The syntax is as follows −DECLARE X int; DECLARE Y int; SET X = value1; SET Y = value2; IF ( (X < Y AND X > value1 AND Y >value2) OR X! = anyValueToCompare) THEN    yourStatement; ELSE    yourStatement; END IFNow to understand the above syntax, let us create a stored procedure. The query to create a stored procedure is as follows −mysql> create procedure SP_IFELSEDEMO()    -> BEGIN    -> DECLARE X int;    -> DECLARE Y ... Read More

To update the current date and time to 5 days, you need to use the Now() + 5. That would update the entire date-time i.e. days, hour, minutes and seconds. To understand this, let us create a table. The query to create a table is as follows −mysql> create table UserInformationExpire    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> UserName varchar(10),    -> UserInformationExpireDateTime datetime not null    -> ); Query OK, 0 rows affected (0.83 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> ... Read More

You can use backticks and single quotes in MySQL. The backtick can be used around the column name and table name while single quotes can be used for the column name values.Let us take an example for both the quote marks. To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table QuotesDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> UserName varchar(20),    -> UserAge int    -> ); Query OK, 0 rows affected (2.53 sec)Insert some records in the table using ... Read More