- Trending Categories
- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Found 4219 Articles for MySQLi
12K+ Views
You need to use NULLIF() function from MySQL. The syntax is as follows:SELECT NULLIF(yourCoumnName, ’ ’) as anyVariableName from yourTableName;In the above syntax, if you compare empty string( ‘ ‘) to empty string( ‘ ‘), the result will always be NULL. However, if you compare with NULL to empty string( ‘ ‘) then also the result will always be NULL.To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table ConvertEmptyStringToNULL -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(20), -> PRIMARY KEY(Id) ... Read More
832 Views
To select the top 2 rows from each group, use the where condition with subquery. Let us create a table. The query to create a table is as follows:mysql> create table selectTop2FromEachGroup -> ( -> Name varchar(20), -> TotalScores int -> ); Query OK, 0 rows affected (0.80 sec)Now insert some records in the table using insert command. The query is as follows:mysql> insert into selectTop2FromEachGroup values('John', 32); Query OK, 1 row affected (0.38 sec) mysql> insert into selectTop2FromEachGroup values('John', 33); Query OK, 1 row affected (0.21 sec) mysql> insert into selectTop2FromEachGroup values('John', 34); Query OK, ... Read More
1K+ Views
To add a new column that counts the number of rows as serial number, you can use the global variable in select statement.Let us create a table. The query to create a table is as follows:mysql> create table addColumnToCountAsSerialNumber -> ( -> Id int, -> Name varchar(20), -> Age int, -> Salary 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 addColumnToCountAsSerialNumber values(10, 'John', 23, 8576); Query OK, 1 row affected (0.10 sec) mysql> insert into addColumnToCountAsSerialNumber values(12, ... Read More
2K+ Views
The equivalent of SQL Server function SCOPE_IDENTITY() is equal to LAST_INSERT_ID() in MySQL. The syntax is as follows:SELECT LAST_INSERT_ID().This returns the id of last inserted record.Here, I am going to create a table with primary key column. The following is the demo of last_insert_id().First, let us create two tables. The query to create the first table table is as follows:mysql> create table TestOnLastInsertIdDemo -> ( -> StudentId int NOT NULL AUTO_INCREMENT, -> PRIMARY KEY(StudentId) -> ); Query OK, 0 rows affected (0.95 sec)Now creating the second table. The query is as follows:mysql> create table TestOnLastInsertIdDemo2 ... Read More
127 Views
To match only one character in MySQL, you can use underscore(_) in place of %. The syntax is as follows:SELECT *FROM yourTableName WHERE yourColumnName LIKE ‘yourString_’;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table OneCharactermatchDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> PassoutYear year, -> 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 OneCharactermatchDemo(PassoutYear) values('2008'); Query OK, 1 row affected (0.14 sec) mysql> ... Read More
2K+ Views
You need to use CAST() or CONVERT() function to convert output of MySQL query to UTF8. Here, I am using MySQL version 8.0.12. Let us first check the version:mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.12 | +-----------+ 1 row in set (0.00 sec)In this if you use utf8 then you will get warning of aliases because it has utf8mb4. Therefore, you can avoid the warning by placing utf8mb4.Note: Never use UTF8. For current versions, use UTF8MB4Here is the syntax to convert output of MySQL query to UTF8:SELECT yourColumnName1, convert(yourColumnName2 USING utf8) as anyVariableName FROM ... Read More
763 Views
There is a single way by which you can set a global event_scheduler=ON even if MySQL is restarted. You need to set global system variable ON and need to use this system variable even if MySQL restart.For this, I am using system variable @@event_scheduler using select statement. The query is as follows:mysql> select @@event_scheduler;The following is the output:+-------------------+ | @@event_scheduler | +-------------------+ | ON | +-------------------+ 1 row in set (0.00 sec)Now, restart MySQL. The query is as follows:mysql> restart; Query OK, 0 rows affected ... Read More
6K+ Views
You need to use LIKE with OR operator to find all the names that starts with a or b or c. The syntax is as follows:SELECT *FROM yourTableName WHERE yourColumnName like 'A%' or yourColumnName like 'B%' or yourColumnName like 'C%';The above query finds all names that starts only with the letter ‘a’ or ‘b’ or ‘c’. To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table AllNamesStartWithAorBorC -> ( -> Id int NOT NULL AUTO_INCREMENT, -> EmployeeName varchar(20), -> PRIMARY KEY(Id) -> ); Query ... Read More
2K+ Views
NOTE: There is only one difference between AND and && is that AND is a standard while && is ownership syntax.Except the above statement, there is no difference between AND and &&. Let us look at all the conditions.The result of AND and && will always be either 1 or 0. As we know the AND and && both are logical operators, if there are more than one operand and any one of them has value 0 then result becomes 0 otherwise 1.Here is the demo of AND and &&.Case 1(a): If both operands are 1. Using AND.The query is ... Read More
622 Views
NULL and empty string ‘ ’ both aren’t similar in MySQL. To check the field is empty like ‘ ’or is null, you need to use the IS NULL property or something else. You can check all the conditions with CASE statement. The syntax is as follows:SELECT *, CASE WHEN yourColumnName =’ ‘ THEN ‘yourMessage1’ WHEN yourColumnName IS NULL THEN ‘yourMessage2’ ELSE CONCAT(yourColumnName ,' is a Name') END 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 checkFieldDemo -> ( -> Id int ... Read More