- 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
![karthikeya Boyini](https://www.tutorialspoint.com/assets/profiles/13518/profile/60_31598-1537784993.jpg)
486 Views
Use the concept of IFNULL() method to treat NULL as 0. Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Value1 int, Value2 int ); Query OK, 0 rows affected (0.64 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Value1, Value2) values(10, 20); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(Value1, Value2) values(null, null); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(Value1, Value2) values(40, null); Query OK, 1 row affected (0.18 sec)Following is the query to display all ... Read More
![Samual Sam](https://www.tutorialspoint.com/assets/profiles/13514/profile/60_83486-1512649303.jpg)
441 Views
You can use the concept of IFNULL() for this. Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Name varchar(100) DEFAULT 'Larry', Age int DEFAULT NULL ); Query OK, 0 rows affected (0.73 sec)Insert records in the table using insert command −mysql> insert into DemoTable(Name, Age) values('John', 23); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(); Query OK, 1 row affected (0.34 sec) mysql> insert into DemoTable(Name) values('David'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(Age) values(24); Query OK, 1 row ... Read More
![karthikeya Boyini](https://www.tutorialspoint.com/assets/profiles/13518/profile/60_31598-1537784993.jpg)
80 Views
You can use update along with lower() function for this. Let us first create a table −mysql> create table DemoTable ( Id varchar(100), StudentFirstName varchar(20), StudentLastName varchar(20), StudentCountryName varchar(10) ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('STU-101', 'John', 'Smith', 'US'); Query OK, 1 row affected (0.59 sec) mysql> insert into DemoTable values('STU-102', 'John', 'Doe', 'UK'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('STU-103', 'David', 'Miller', 'AUS'); Query OK, 1 row affected (0.19 sec)Following is the query to display all ... Read More
![Samual Sam](https://www.tutorialspoint.com/assets/profiles/13514/profile/60_83486-1512649303.jpg)
198 Views
To convert string to bitset, use the CONV() method. Let us first create a table −mysql> create table DemoTable ( stringValue BIT(4) ); Query OK, 0 rows affected (3.50 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(CONV('1110', 2, 10) * 1); Query OK, 1 row affected (0.62 sec) mysql> insert into DemoTable values(b'1011'); Query OK, 1 row affected (0.14 sec)Following is the query to display all records from the table using select statement −mysql> select *from DemoTable;Following is the output that displays blank result because the type is bitset −Following is the query ... Read More
![karthikeya Boyini](https://www.tutorialspoint.com/assets/profiles/13518/profile/60_31598-1537784993.jpg)
132 Views
You can easily add multiple items with only one insert command. The syntax is as follows −insert into yourTableName(yourColumnName1, yourColumnName2, ......N) values(yourValue1, yourValue2, ....N), (yourValue1, yourValue2, ....N), ..........N;Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Value1 int, Value2 int, Value3 int ); Query OK, 0 rows affected (0.79 sec)Insert multiple records in the table using insert command −mysql> insert into DemoTable(Value1, Value2, Value3) values(10, 20, 40), (100, 148, 120), (150, 670, 1000), (100000, 200000, 409999); Query OK, 4 rows affected (0.17 sec) Records : 4 Duplicates : ... Read More
![Samual Sam](https://www.tutorialspoint.com/assets/profiles/13514/profile/60_83486-1512649303.jpg)
3K+ Views
You can use DATE_FORMAT() to set time data type to be only HH:MM. Following is the syntax −select DATE_FORMAT(yourColumnName, "%H:%i") AS anyAliasName from yourTableName;Let us first create a table −mysql> create table DemoTable ( Arrivaltime time ); Query OK, 0 rows affected (0.61 sec)Insert records in the table using insert command −mysql> insert into DemoTable values('08:20'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('05:40'); Query OK, 1 row affected (0.12 sec)Following is the query to display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+-------------+ | Arrivaltime ... Read More
![karthikeya Boyini](https://www.tutorialspoint.com/assets/profiles/13518/profile/60_31598-1537784993.jpg)
5K+ Views
You can use stored procedure to insert into two tables in a single query. Let us first create a table −mysql> create table DemoTable ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentFirstName varchar(20) ); Query OK, 0 rows affected (0.56 sec)Here is the query to create second table −mysql> create table DemoTable2 ( ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY, ClientName varchar(20), ClientAge int ); Query OK, 0 rows affected (0.76 sec)Following is the query to create stored procedure to insert into two tables created above −mysql> DELIMITER // mysql> CREATE PROCEDURE insert_into_twoTables(name ... Read More
![Samual Sam](https://www.tutorialspoint.com/assets/profiles/13514/profile/60_83486-1512649303.jpg)
1K+ Views
To perform complex MySQL insert, you can use CONCAT() function. Let us see an example and create a table with StudentId and StudentFirstName.After that, complex MySQL insert will be performed and 'Web Student’ text will get inserted for every value and unique StudentId will get concatenated.The query to create first table is as follows −mysql> create table DemoTable ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentFirstName varchar(20) ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(StudentFirstName) values('John'); Query OK, 1 row affected (0.16 sec) mysql> insert ... Read More
![karthikeya Boyini](https://www.tutorialspoint.com/assets/profiles/13518/profile/60_31598-1537784993.jpg)
92 Views
You can use INFORMATION_SCHEMA.COLUMNS to get the table with the greatest number of columns. The syntax is as follows −SELECT TABLE_NAME, COUNT(*) AS anyAliasName FROM INFORMATION_SCHEMA.COLUMNS GROUP BY TABLE_NAME ORDER BY yourAliasName DESC LIMIT 1;Following is the query to select the table that has the greatest number of columns. We are getting this result because we have set the count to DESC and used GROUP BY TABLE_NAME −mysql> SELECT TABLE_NAME, COUNT(*) as TOTAL_COUNT FROM INFORMATION_SCHEMA.COLUMNS GROUP BY TABLE_NAME ORDER BY TOTAL_COUNT DESC LIMIT 1;This will produce the following output −+-----------------------------------+-------------+ | TABLE_NAME ... Read More
![Samual Sam](https://www.tutorialspoint.com/assets/profiles/13514/profile/60_83486-1512649303.jpg)
288 Views
You can use subquery for this. Let us first create a table −mysql> create table DemoTable ( Id int ); Query OK, 0 rows affected (0.68 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(100); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values(115); Query OK, 1 row affected (0.32 sec) mysql> insert into DemoTable values(200); Query OK, 1 row affected (0.28 sec) mysql> insert into DemoTable values(140); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values(124); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable ... Read More