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
MySQLi Articles
Page 38 of 341
MySQL query to get the count of distinct records in a column
To get the count of distinct records, use DISTINCT along with COUNT(). Following is the syntax −select count(DISTINCT yourColumnName) from yourTableName;Let us first create a table −mysql> create table DemoTable -> ( -> Name varchar(20), -> Score int -> ); Query OK, 0 rows affected (0.67 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John', 56); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('Sam', 89); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values('John', 56); Query OK, 1 row affected (0.17 sec) mysql> insert into ...
Read MoreMySQL query to convert timediff() to seconds?
For this, you can use TIME_TO_SEC() function. Let us first create a table −mysql> create table DemoTable -> ( -> SourceTime time, -> DestinationTime time -> ); Query OK, 0 rows affected (1.33 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('10:20:00', '4:50:54'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('12:05:10', '7:45:12'); Query OK, 1 row affected (0.30 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following output −+------------+-----------------+ | SourceTime | DestinationTime | +------------+-----------------+ | 10:20:00 ...
Read MoreHow to filter a specific month in MySQL when date is in varchar?
To filter, you can use STR_TO_DATE() function from MySQL. With that, use MONTH() to get the date from the specific month. Let us first create a table −mysql> create table DemoTable -> ( -> DueDate varchar(100) -> ); Query OK, 0 rows affected (1.18 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('06-19-2019'); Query OK, 1 row affected (0.36 sec) mysql> insert into DemoTable values('01-31-2018'); Query OK, 1 row affected (0.38 sec) mysql> insert into DemoTable values('12-01-2016'); Query OK, 1 row affected (0.14 sec)Display all records from the table using ...
Read MoreHow to sort an alphanumeric column in MySQL?
To sort an alphanumeric column, use LIKE operator along with SUBSTRING(). Let us first create a table −mysql> create table DemoTable -> ( -> StudentId varchar(100) -> ); Query OK, 0 rows affected (1.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('S/TU/100'); Query OK, 1 row affected (0.28 sec) mysql> insert into DemoTable values('S/TU/1000'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable values('S/TU/10'); Query OK, 1 row affected (0.47 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following ...
Read MoreHow to use a single MySQL query to count column values ignoring null?
For this, you can COUNT() method, which does not include NULL value. Let us first create a table −mysql> create table DemoTable -> ( -> Name varchar(100), -> CountryName varchar(100) -> ); Query OK, 0 rows affected (0.49 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John', null); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('Chris', 'US'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('Robert', null); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('Bob', 'UK'); Query ...
Read MoreImplement MySQL LIMIT and OFFSET in a single query stating its difference
The LIMIT tells about how many records you want while OFFSET gives the records from the given position+1. Let us first create a table −mysql> create table DemoTable -> ( -> Name varchar(100) -> ); Query OK, 0 rows affected (1.33 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('Chris'); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable values('David'); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable values('Bob'); Query OK, 1 ...
Read MoreHow to extract the digit part from the string in MySQL?
Let us first create a table −mysql> create table DemoTable -> ( -> StudentId varchar(100) -> ); Query OK, 0 rows affected (0.71 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John19383'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('Carol9999'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('David123456'); Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following output −+-------------+ | StudentId | +-------------+ | John19383 ...
Read MoreSet a certain value first with MySQL ORDER BY?
Let us first create a table −mysql> create table DemoTable -> ( -> Number int -> ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(20); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(12); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable values(14); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(19); Query OK, 1 row affected (0.14 sec) ...
Read MoreHow to use special characters in column names with MySQL?
Using backticks around the column name will allow you to use special characters. Let us first create a table −mysql> create table DemoTable -> ( -> `Student-Id` int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> `Student-Name` varchar(100), -> `Student-Age` int -> ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(`Student-Name`, `Student-Age`) values('Chris', 21); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable(`Student-Name`, `Student-Age`) values('Mike', 19); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable(`Student-Name`, `Student-Age`) values('Bob', 18); Query OK, ...
Read MoreFetch middle part of a string surrounded by slash in MySQL
Let us first create a table −mysql> create table DemoTable -> ( -> Code varchar(100) -> ); Query OK, 0 rows affected (1.07 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('/101/102/106'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('/110/111/101'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('/111/114/201'); Query OK, 1 row affected (0.47 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+--------------+ | Code | +--------------+ | ...
Read More