Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Let us first create a table −mysql> create table DemoTable756 ( Value1 int, Value2 int, Value3 int ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command −mysql> insert into DemoTable756 values(10, 20, 14); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable756 values(20, 34, 17); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable756 values(200, 134, 789); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable756 values(139, 98, 99); Query OK, 1 row affected (0.17 sec)Display all records from the table using select statement ... Read More
For this, use alias on HAVING clause.Let us first create a table −mysql> create table DemoTable755 ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Score1 int, Score2 int ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command −mysql> insert into DemoTable755(Score1, Score2) values(30, 23); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable755(Score1, Score2) values(50, 60); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable755(Score1, Score2) values(89, 90); Query OK, 1 row affected (0.29 sec) mysql> insert into DemoTable755(Score1, Score2) values(99, 99); Query OK, 1 row ... Read More
To return only the distinct values, use GROUP BY clause.Let us first create a table −mysql> create table DemoTable754 (ProductPrice int); Query OK, 0 rows affected (0.48 sec)Insert some records in the table using insert command −mysql> insert into DemoTable754 values(200); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable754 values(500); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable754 values(200); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable754 values(500); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable754 values(800); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable754 ... Read More
To fetch only the month part, use DATE_FORMAT().Let us first create a table −mysql> create table DemoTable753 (DueDate datetime); Query OK, 0 rows affected (0.51 sec)Insert some records in the table using insert command −mysql> insert into DemoTable753 values('2019-06-21'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable753 values('2019-01-31'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable753 values('2018-12-01'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable753 values('2016-11-11'); Query OK, 1 row affected (0.23 sec)Display all records from the table using select statement −mysql> select *from DemoTable753;This will produce the following output -+---------------------+ ... Read More
To implement IN() for custom ordering, use ORDER BY CASE.Let us first create a table −mysql> create table DemoTable752 ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Name varchar(100) ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command −mysql> insert into DemoTable752(Name) values('John'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable752(Name) values('Carol'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable752(Name) values('Bob'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable752(Name) values('Mike'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable752(Name) values('Sam'); ... Read More
For this, you can use GROUP BY HAVING clause.Let us first create a table −mysql> create table DemoTable751 ( StudentName varchar(100), SubjectName varchar(100) ); Query OK, 0 rows affected (0.66 sec)Insert some records in the table using insert command −mysql> insert into DemoTable751 values('John', 'MySQL'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable751 values('John', 'MongoDB'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable751 values('Sam', 'MySQL'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable751 values('Carol', 'Java'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable751 values('David', 'MySQL'); ... Read More
Let’s say some of the columns values in a table has a specific keyword and you want only those records. For this, use the LIKE operator.Let us first see an example and create a table −mysql> create table DemoTable750 (Title varchar(200)); Query OK, 0 rows affected (0.69 sec)Insert some records in the table using insert command −mysql> insert into DemoTable750 values('Java and MongoDB, MySQL'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable750 values('MySQL, SQL Server'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable750 values('PL/SQL, Python, MongoDB'); Query OK, 1 row affected (0.14 sec) mysql> ... Read More
To list logged-in MySQL users, you can use any of the following two methods −First MethodUse INFORMATION_SCHEMA.PROCESSLISTselect *from INFORMATION_SCHEMA.PROCESSLIST;Second MethodYou can use SHOW PROCESSLIST command as well. Following is the syntax −SHOW PROCESSLIST;Let us implement the above syntaxes in order to list logged in MySQL users −mysql> select *from information_schema.processlist;This will produce the following output -+----+-----------------+-----------------+------+---------+--------+-----------------------------+---------------------------------------------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO ... Read More
For this, you can use custom IF() and set a value whenever 0 appears.Let us first create a table −mysql> create table DemoTable749 (Value int); Query OK, 0 rows affected (1.02 sec)Insert some records in the table using insert command −mysql> insert into DemoTable749 values(10); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable749 values(0); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable749 values(769); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable749 values(0); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable749 values(78); Query OK, 1 row affected (0.14 sec) ... Read More
To extract only the day instead of the entire date, you need to use DAYOFMONTH() function from MySQL.Let us first create a table −mysql> create table DemoTable747 (DueDate datetime); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command −mysql> insert into DemoTable747 values('2019-01-31') ; Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable747 values('2018-12-01'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable747 values('2017-09-14'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable747 values('2016-07-21'); Query OK, 1 row affected (0.14 sec)Display all records from the table using select ... Read More