- 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
1K+ Views
You can use TRIM() function to remove spaces. The syntax is as follows −UPDATE yourTableName SET yourColumnName=TRIM(yourColumnName);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table removeSpaceDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> UserId varchar(20), -> UserName varchar(10), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.81 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into removeSpaceDemo(UserId, ... Read More
91 Views
Here you can use BETWEEN operator. The syntax is as follows −SELECT *FROM yourTableName WHERE yourColumnName BETWEEN 6 AND 10;You can use regular expression like this. The syntax is as follows −SELECT *FROM yourTableName WHERE yourColumnName REGEXP '10|[6-9]';To understand the both syntax, let us create a table. The query to create a table is as follows −mysql> create table RegularExpressionDemo -> ( -> Id int -> ); 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> insert into RegularExpressionDemo values(1); Query OK, ... Read More
759 Views
As you know if you use LIMIT 0 in MySQL SELECT statement, it returns an empty set.The LIMIT can be used when you want a specified number of rows from a result rather than the entire rows. If you use any MySQL API, then the job of LIMIT is to acquire the type of result columns.LIMIT 0 can be used to check the validity of a query. For more details use the following link −https://dev.mysql.com/doc/refman/8.0/en/limit-optimization.htmlHere is the demo of LIMIT 0. The query to create a table is as follows −mysql> create table Limit0Demo -> ( -> Id ... Read More
1K+ Views
To delete all rows containing string “foo” in table “bar”, you need to use LIKE operator.To understand the above syntax, let us create a sample table with name “bar”. The query to create a table is as follows. We will always insert records with string “foo” using INSERT command after creating the below table −mysql> create table bar -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Words longtext, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.61 sec)Now you can insert some records in the table using insert command. The string “foo” ... Read More
1K+ Views
To compare two tables and return missing ids, you need to use a subquery. The syntax is as follows −SELECT yourFirstTableName.yourIdColumnName FROM yourFirstTableName WHERE NOT IN(SELECT yourSecondTableName.yourIdColumnName FROM youSecondTableName);To understand the above syntax, 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 ... Read More
182 Views
To drop a database with the character ‘?’ in its name, you need to use backtick symbol around the database name. The syntax is as follows −DROP DATABASE `yourDatabaseName`;To understand the above syntax, let us create a database. The query to create a database is as follows −mysql> create database `test?data`; Query OK, 1 row affected (0.14 sec)So, I have a database with? character. The query to show all databases is as follows −mysql> show databases;The following is the output −+-----------------------+ | Database | +-----------------------+ | business | ... Read More
239 Views
To remove \r from the text, you need to use REPLACE command. The syntax is as follows −UPDATE yourTableName SET yourColumnName=REPLACE(yourColumnName, ’\r’, ’ ‘);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table removeDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name text, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.79 sec)Now insert some records in the table using insert command. The query is as follows −mysql> insert into removeDemo(Name) values('John\rSmithCarol'); Query OK, 1 row affected (0.13 sec) mysql> insert into removeDemo(Name) values('LarryMike\rSam'); ... Read More
737 Views
To extract the Day/Month/Year from a timestamp, you need to use the date_parse() function. The syntax as follows −print_r(date_parse(“anyTimeStampValue”));The PHP code is as follows −$yourTimeStampValue="2019-02-04 12:56:50"; print_r(date_parse($yourTimeStampValue));The snapshot of PHP code is as follows −The following is the output −Array ( [year] => 2019 [month] => 2 [day] => 4 [hour] => 12 [minute] => 56 [second] => 50 [fraction] => 0 [warning_count] => 0 [warnings] => Array ( ) [error_count] => 0 [errors] => Array ( ) [is_localtime] => )The snapshot of the sample output −
861 Views
You cannot get the type of variable in MySQL. Cast the type of variable into another using CAST operator. The syntax is as follows −SET @yourVariableName:=’anyValue’Use the CAST operator to cast to another type. The syntax is as follows −SELECT CAST( @yourVariableName AS SIGNED);To understand the above syntax, let us cast to another type.Case 1: String to unsigned −mysql> set @StringToInt:='12345'; Query OK, 0 rows affected (0.00 sec)The query is as follows to another type −mysql> select CAST(@StringToInt as UNSIGNED);The following is the output −+--------------------------------+ | CAST(@StringToInt as UNSIGNED) | +--------------------------------+ | 12345 ... Read More
1K+ Views
To find invalid email address, use the below syntax −SELECT yourColumnName FROM yourTableName WHERE yourColumnName NOT LIKE '%_@_%._%';The above syntax will give the list of all invalid email addresses. To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table FindInvalidEmailAddressDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(20), -> EmailAddress varchar(40), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.75 sec)Now you can insert some records in the table using insert command. We have inserted some invalid ... Read More