Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
MySQLi Articles
Page 196 of 341
Count zero, NULL and distinct values except zero and NULL with a single MySQL query
Let us first create a table −mysql> create table DemoTable( Value int ); Query OK, 0 rows affected (1.35 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.30 sec) mysql> insert into DemoTable values(NULL); Query OK, 1 row affected (0.29 sec) mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.59 sec) mysql> insert into DemoTable values(0); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable values(20); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.70 ...
Read MoreIs SELECT * faster than 40 columns listing in MySQL?
The SELECT * is slower than 40 columns listing. It’s a better choice to list the column names while using the SELECT query. Let us see a simple example and create a table −mysql> create table DemoTable( Id int, Name varchar(20), Age int, ZipCode varchar(20), CountryName varchar(20) ); Query OK, 0 rows affected (0.82 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(101, 'Chris', 23, '0909332', 'US'); Query OK, 1 row affected (0.48 sec) mysql> insert into DemoTable values(102, 'Bob', 24, '8747443', 'AUS'); Query OK, 1 row affected (0.50 sec) ...
Read MoreGet second largest marks from a MySQL table using subquery?
Let us first create a table −mysql> create table DemoTable( Marks int ); Query OK, 0 rows affected (1.34 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(78); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values(88); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values(67); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(76); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable values(98); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(86); Query OK, 1 row affected (0.11 ...
Read MoreMySQL query to count number of duplicate values in a table column
Let us first create a table −mysql> create table DemoTable( Data int ); Query OK, 0 rows affected (0.98 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(60); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable values(40); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values(50); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(60); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(40); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(80); Query OK, 1 row affected (0.15 ...
Read MoreA single MySQL query to select value from first table and insert in the second?
Let us first create a table −mysql> create table DemoTable1( Value int ); Query OK, 0 rows affected (0.65 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values(67); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1 values(46); Query OK, 1 row affected (0.11 sec)Display all records from the table using select statement −mysql> select *from DemoTable1;This will produce the following output −+-------+ | Value | +-------+ | 67 | | 46 | +-------+ 2 rows in set (0.00 sec)Following is the query to create the ...
Read MoreMySQL ENUM column match for quoted values
Let us first create a table with ENUM type column −mysql> create table DemoTable( isMarried ENUM('1', '0') ); Query OK, 0 rows affected (0.46 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('0'); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable values('1'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable values('1'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values('0'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable values('0'); Query OK, 1 row affected (1.00 sec) mysql> insert into DemoTable values('1'); Query ...
Read MoreOrder MySQL query by multiple ids?
For this, use ORDER BY FIELD(). Let us first create a table −mysql> create table DemoTable( ClientId varchar(40), ClientName varchar(40) ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('987_John', 'John'); Query OK, 1 row affected (0.33 sec) mysql> insert into DemoTable values('1000_Sam', 'Sam'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable values('777_Carol', 'Carol'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('2000_Bob', 'Bob'); Query OK, 1 row affected (0.20 sec)Display all records from the table using select statement −mysql> select ...
Read MoreDisplay Timestamp before the current date in MySQL
Let us first create a table −mysql> create table DemoTable( ArrivalDate timestamp ); Query OK, 0 rows affected (1.96 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-09-14 17:25:00'); Query OK, 1 row affected (0.26 sec) mysql> insert into DemoTable values('2019-09-13 17:25:00'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values('2016-09-01 17:20:10'); Query OK, 1 row affected (0.38 sec) mysql> insert into DemoTable values('2019-09-11 12:00:00'); Query OK, 1 row affected (0.16 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+---------------------+ | ...
Read MoreMySQL query to concatenate all the values in each row based on the common matching ID
Let us first create a table −mysql> create table DemoTable ( StudentId int, StudentFirstName varchar(100), StudentLastName varchar(100) ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(1000, 'Adam', 'Smith'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values(1000, 'John', 'Doe'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values(1000, 'David', 'Miller'); Query OK, 1 row affected (0.12 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+-----------+------------------+-----------------+ | StudentId | StudentFirstName ...
Read MoreMySQL query to sum the Product Price values from similar columns for same customers and display the result in the same column
For this, use SUM() along with GROUP BY. Let us first create a table −mysql> create table DemoTable ( CustomerName varchar(100), Product_1_Price int, Product_2_Price int ); Query OK, 0 rows affected (0.73 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John', 67, 89); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('David', 769, 890); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('David', 987, 1000); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('John', 900, 111); Query OK, 1 row affected (0.12 sec)Display ...
Read More