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
MySQL Articles
Page 289 of 355
Does MySQL eliminate common subexpressions between SELECT and HAVING/GROUP BY clause? How to test it?
To test, use the sleep() function.Case 1 −The syntax is as follows −SELECT yourColumnName+sleep(yourIntegerValue) FROM yourTableName GROUP BY yourColumnName+sleep(yourIntegerValue);;Case 2 − You can use another syntax which is as follows −SELECT yourColumnName+sleep(yourIntegerValue) As anyAliasName FROM yourTableName GROUP BY yourAliasName;To understand the above syntaxes, let us create a table. The query to create a table is as follows −mysql> create table sleepDemo -> ( -> value int -> ); Query OK, 0 rows affected (1.25 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into sleepDemo values(40); Query OK, 1 row ...
Read MoreMySQL select dates in 30-day range?
To select dates in 30-day range, you can use arithmetic operation - with interval.The syntax is as follows −select *from yourTableName where yourDateColumnName > NOW() - INTERVAL 30 DAY and yourDateColumnName < NOW() + INTERVAL 30 DAY;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table selectDatesDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> ArrivalDate datetime -> ); Query OK, 0 rows affected (0.77 sec)Now you can insert some records in the table using insert command. The query is as ...
Read MoreHow to remove hyphens using MySQL UPDATE?
To remove hyphens using MySQL update, you can use replace() function. The syntax is as follows −update yourTableName set yourColumnName=replace(yourColumnName, '-', '' );To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table removeHyphensDemo -> ( -> userId varchar(100) -> ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into removeHyphensDemo values('John-123-456'); Query OK, 1 row affected (0.22 sec) mysql> insert into removeHyphensDemo values('Carol-9999-7777-66555'); Query OK, 1 row affected (0.19 sec) ...
Read MoreCount top 10 most occurring values in a column in MySQL?
To count the top 10 most occurring values in a column in MySQL, The syntax is as follows −SELECT yourColumnName, count(*) FROM yourTableName GROUP BY yourColumnName ORDER BY count(*) DESC LIMIT 10;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table countTop10Demo -> ( -> Value int -> ); Query OK, 0 rows affected (0.74 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into countTop10Demo values(10); Query OK, 1 row affected (0.12 sec) ...
Read MoreWhat is the PHP equivalent of MySQL's UNHEX()?
You can use hex2bin() function since it is the PHP equivalent of MySQL's UNHEX().The syntax is as follows −$anyVariableName = hex2bin("yourHexadecimalValue");To understand the above syntax, let us implement the above syntax in PHP. The PHP code is as follows −$myFirstValue = hex2bin("7777772E4D7953514C4578616D706C652E636F6D"); var_dump($myFirstValue); $mySecondValue=hex2bin("416476616E6365644A617661576974684672616D65776F726B"); echo(''); var_dump($mySecondValue);The snapshot of PHP code is as follows −Here is the snapshot of The output −Here is the MySQL UNHEX() −Case 1 − The query is as follows −mysql> SELECT UNHEX("7777772E4D7953514C4578616D706C652E636F6D");The following is The output −+---------------------------------------------------+ | UNHEX("7777772E4D7953514C4578616D706C652E636F6D") | +---------------------------------------------------+ | www.MySQLExample.com ...
Read MoreMySQL select order by acts like a string (not a number)?
You can use the following syntax if your column has varchar data type −select yourColumnName FROM yourTableName ORDER BY yourColumnName +0 DESC;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table selectOrderdemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(100), -> RankNumber varchar(100) -> ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into selectOrderdemo(Name, RankNumber) values('Larry', -100); Query OK, 1 row affected (0.23 ...
Read MoreMySQL where column = 'x, y, z'?
You can use IN operator for this.The syntax is as follows −SELECT *FROM yourTableName WHERE yourColumnName IN(‘yourValue1’, ‘yourValue2’, ‘yourValue3’, ...........N);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table InOperatorDemo -> ( -> ClientId int -> ); Query OK, 0 rows affected (0.52 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into InOperatorDemo values(101); Query OK, 1 row affected (0.19 sec) mysql> insert into InOperatorDemo values(110); Query OK, 1 row affected (0.11 sec) mysql> insert into InOperatorDemo ...
Read MoreMySQL query to skip the duplicate and select only one from the duplicated values
The syntax is as follows to skip the duplicate value and select only one from the duplicated values −select min(yourColumnName1), yourColumnName2 from yourTableName group by yourColumnName2;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table doNotSelectDuplicateValuesDemo -> ( -> User_Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> User_Name varchar(20) -> ); Query OK, 0 rows affected (0.78 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into doNotSelectDuplicateValuesDemo(User_Name) values('John'); Query OK, 1 row affected ...
Read MoreUsing MySQL SELECT for simple BOOLEAN evaluation?
You can use CASE statement for this. Let us see an example −mysql> create table BooleanEvaluationDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> FirstValue int, -> SecondValue int -> ); Query OK, 0 rows affected (0.71 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into BooleanEvaluationDemo(FirstValue, SecondValue) values(10, 5); Query OK, 1 row affected (0.20 sec) mysql> insert into BooleanEvaluationDemo(FirstValue, SecondValue) values(15, 20); Query OK, 1 row affected (0.16 sec) mysql> insert into BooleanEvaluationDemo(FirstValue, SecondValue) values(50, 40); Query OK, 1 row affected (0.14 ...
Read MoreHow to trim commas with MySQL?
The syntax is as follows to trim commas −SELECT TRIM(BOTH ', ' FROM yourColumnName) from yourTableName;Let us see an example −mysql> create table TrimCommasDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> AllTechnicalSkills text -> ); Query OK, 0 rows affected (0.81 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into TrimCommasDemo(AllTechnicalSkills) values(', C, C++, Java, '); Query OK, 1 row affected (0.14 sec) mysql> insert into TrimCommasDemo(AllTechnicalSkills) values(', MySQL, SQL Server, MongoDB, '); Query OK, 1 row affected (0.13 sec) mysql> ...
Read More