- 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
![Ankith Reddy](https://www.tutorialspoint.com/assets/profiles/22328/profile/60_142162-1519038074.jpg)
6K+ Views
To convert number INT in minutes to TIME in MySQL, you can use SEC_TO_TIME() function.The syntax is as followsselect SEC_TO_TIME(yourIntColumnName*60) AS `anyAliasName` from yourTableName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table convertNumberToMinute -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> NumberToMinute int -> ); Query OK, 0 rows affected (0.79 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into convertNumberToMinute(NumberToMinute) values(60); Query OK, 1 row affected (0.12 sec) mysql> insert into convertNumberToMinute(NumberToMinute) values(70); Query ... Read More
![George John](https://www.tutorialspoint.com/assets/profiles/13511/profile/60_58410-1512537824.png)
592 Views
You can use the COALESCE() function to convert MySQL null to 0SELECT COALESCE(yourColumnName, 0) AS anyAliasName FROM yourTableName;Let us first create a table. The query to create a table is as followsmysql> create table convertNullToZeroDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(20), -> Salary int -> ); Query OK, 0 rows affected (1.28 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into convertNullToZeroDemo(Name, Salary) values('John', NULL); Query OK, 1 row affected (0.20 sec) mysql> insert into convertNullToZeroDemo(Name, Salary) values('Carol', 5610); Query OK, 1 ... Read More
![Chandu yadav](https://www.tutorialspoint.com/assets/profiles/22330/profile/60_142811-1519038104.jpg)
10K+ Views
Update date of datetime field with the help of arithmetic operator minus(-).The syntax is as followsupdate yourTableName set yourDateTimeColumnName=yourDateTimeColumnName - interval yourValue day where date(yourDateTimeColumnName)=’yourDateValue’;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table updateDateDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> ArrivalDate datetime -> ); Query OK, 0 rows affected (0.66 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into updateDateDemo(ArrivalDate) values('2011-01-13'); Query OK, 1 row affected (0.19 sec) mysql> insert into updateDateDemo(ArrivalDate) values('2013-04-21'); ... Read More
![Arjun Thakur](https://www.tutorialspoint.com/assets/profiles/13574/profile/60_74441-1560315595.jpg)
11K+ Views
To achieve this for multiple tables, use the UNION ALL.The syntax is as followsselect sum(variableName.aliasName) from ( select count(*) as yourAliasName from yourTableName1 UNION ALL select count(*) as yourAliasName from yourTableName2 ) yourVariableName;Let us implement the above syntax. Here, I am using the sample database which has more tables.The two tables we are using areuserdemowheredemoHere is the query to display all records of both the tables. The query is as follows to display records from table ‘userdemo’.mysql> select *from userdemo;The following is the output+--------+----------+------------------+ | UserId | UserName | RegisteredCourse | +--------+----------+------------------+ | 1 ... Read More
![George John](https://www.tutorialspoint.com/assets/profiles/13511/profile/60_58410-1512537824.png)
1K+ Views
In order to delete everything after a space, you need to use SUBSTRING_INDEX().The syntax is as followsselect substring_index(yourColumnName, ' ', 1) as anyAliasName from yourTableName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table deleteAfterSpaceDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentName varchar(100) -> ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into deleteAfterSpaceDemo(StudentName) values('John Smith'); Query OK, 1 row affected (0.15 sec) mysql> insert into deleteAfterSpaceDemo(StudentName) ... Read More
![Chandu yadav](https://www.tutorialspoint.com/assets/profiles/22330/profile/60_142811-1519038104.jpg)
141 Views
You need to use UPDATE statement for this.The syntax is as followsupdate yourTableName set yourColumnName1=yourValue1, yourColumnName2=yourValue2, ....N where yourCondition;Let us create a table for our examplemysql> create table addWhereClauseDemo -> ( -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentName varchar(30), -> StudentPassword varchar(40) -> ); Query OK, 0 rows affected (0.45 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into addWhereClauseDemo(StudentName, StudentPassword) values('John', 'John123456'); Query OK, 1 row affected (0.14 sec) mysql> insert into addWhereClauseDemo(StudentName, StudentPassword) values('Carol', '99999'); Query OK, 1 row affected (0.24 sec) mysql> insert ... Read More
![Arjun Thakur](https://www.tutorialspoint.com/assets/profiles/13574/profile/60_74441-1560315595.jpg)
2K+ Views
You can compare only day and month with date field in MySQL with the help of DATE_FORMAT().The syntax is as followsselect *from yourTableName WHERE DATE_FORMAT(yourColumnName, '%m-%d') = DATE_FORMAT('yourValue', '%m-%d') and yourCondition;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table compareDayAndMonthDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> compareDayAndTime date -> ); Query OK, 0 rows affected (0.49 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into compareDayAndMonthDemo(compareDayAndTime) values('2014-01-31'); Query OK, 1 row affected (0.20 ... Read More
![Ankith Reddy](https://www.tutorialspoint.com/assets/profiles/22328/profile/60_142162-1519038074.jpg)
2K+ Views
To get the records from the previous day, the following is the syntaxselect *from yourTableName where date(yourColumnName)= DATE(NOW() - INTERVAL 1 DAY);To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table yesterDayRecordsDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> ArrivalDateTime datetime -> ); Query OK, 0 rows affected (0.44 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into yesterDayRecordsDemo(ArrivalDateTime) values('2014-11-09 13:45:21'); Query OK, 1 row affected (0.11 sec) mysql> insert into yesterDayRecordsDemo(ArrivalDateTime) values('2017-10-19 11:41:31'); Query ... Read More
![George John](https://www.tutorialspoint.com/assets/profiles/13511/profile/60_58410-1512537824.png)
500 Views
The syntax is as follows to update a column with an int based on orderset @yourVariableName=0; update yourTableName set yourColumnName=(@yourVariableName:=@yourVariableName+1) order by yourColumnName ASC;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table updateColumnDemo -> ( -> Id int, -> OrderCountryName varchar(100), -> OrderAmount int -> ); Query OK, 0 rows affected (1.76 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into updateColumnDemo(Id, OrderCountryName) values(10, 'US'); Query OK, 1 row affected (0.46 sec) mysql> insert into updateColumnDemo(Id, OrderCountryName) ... Read More
![Chandu yadav](https://www.tutorialspoint.com/assets/profiles/22330/profile/60_142811-1519038104.jpg)
126 Views
Use MySQL CASE for a fixed number of arguments.The syntax is as followsSELECT *, CASE WHEN yourColumName1>yourColumName2 THEN 'yourMessage1' ELSE 'yourMessage2' END AS anyAliasName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table CaseFunctionDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Value1 int, -> Value2 int -> ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into CaseFunctionDemo(Value1, Value2) values(10, 20); Query OK, 1 row affected ... Read More