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 217 of 341
How to display highest value from a string with numbers set as varchar in MySQL?
For this, you need to cast the varchar value to INTEGER.Let us first create a table −mysql> create table DemoTable765 (ItemPrice varchar(200)); Query OK, 0 rows affected (0.52 sec)Insert some records in the table using insert command −mysql> insert into DemoTable765 values('567.00'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable765 values('1089.00'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable765 values('540.00'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable765 values('788.00'); Query OK, 1 row affected (0.39 sec)Display all records from the table using select statement −mysql> select *from DemoTable765;This will produce the ...
Read MoreHow to concatenate columns based on corresponding duplicate id values in MySQL? Display the duplicate values in the same column separated by slash
For this, you can use GROUP_CONCAT().Let us first create a table −mysql> create table DemoTable764 ( ProductId int, ProductPrice int ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable764 values(101, 10000); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable764 values(102, 1090); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable764 values(103, 4000); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable764 values(102, 3450); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable764 values(101, 20000); Query OK, 1 row ...
Read MoreMySQL query to get result from multiple select statements?
To get result from multiple select statements, use UNION ALL. Following is the syntax −select yourValue1 AS anyColumnName UNION ALL select yourValue2 AS yourColumnName . . . . NLet us implement the above syntax in order to return enumeration of numbers in different rows −mysql> select 100 AS Number UNION ALL select 1000 AS Number UNION ALL select 10000 AS Number UNION ALL select 100000 AS Number UNION ALL select 1000000 AS Number UNION ALL select 10000000 AS Number UNION ALL select 100000000 AS Number UNION ALL select 1000000000 AS Number;This will produce the following output -+------------+ | Number | +------------+ | 100 | | 1000 | | 10000 | | 100000 | | 1000000 | | 10000000 | | 100000000 | | 1000000000 | +------------+ 8 rows in set (0.00 sec)
Read MoreHow to select last two rows in MySQL?
To select last two rows, use ORDER BY DESC LIMIT 2.Let us first create a table −mysql> create table DemoTable763 ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, FirstName varchar(100) ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable763(FirstName) values('John'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable763(FirstName) values('Sam'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable763(FirstName) values('Carol'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable763(FirstName) values('David'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable763(FirstName) ...
Read MoreMySQL query to match any of the two strings from column values
For this, you can use LIKE operator with OR condition.Let us first create a table −mysql> create table DemoTable762 (Title text); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable762 values('Introduction to Java'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable762 values('MySQL is a RDBMS'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable762 values('Data Structure and Algorithm in Java'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable762 values('Data Structure and Algorithm in C and C++'); Query OK, 1 row affected (0.18 ...
Read MoreFind sum by removing first character from a string followed by numbers in MySQL?
The strings (column values) begun with a character and rest of the string has numbers. We want the sum of these numbers −J230 A130s C13For this, use SUBSTRING() function along with SUM().Let us first create a table −mysql> create table DemoTable761 (Price varchar(100)); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command −mysql> insert into DemoTable761 values('J230'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable761 values('A130'); Query OK, 1 row affected (0.70 sec) mysql> insert into DemoTable761 values('C13'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable761 values('D456'); Query ...
Read MoreHow to merge queries in a single MySQL query to get the count of different values in different columns?
Let us first create a table −mysql> create table DemoTable760 ( ClientId int, ClientId2 int ); Query OK, 0 rows affected (0.79 sec)Insert some records in the table using insert command −mysql> insert into DemoTable760 values(100, 200); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable760 values(100, 200); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable760 values(300, 400); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable760 values(300, 400); Query OK, 1 row affected (0.26 sec) mysql> insert into DemoTable760 values(100, 200); Query OK, 1 row affected (0.23 sec) mysql> insert ...
Read MoreHow to set NULL values in a table column and insert the same
To set NULL values, set the type as NULL as in the below syntax −yourColumnName dataType NULL;Let us first create a table −mysql> create table DemoTable759 ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, FirstName varchar(100) NULL ); Query OK, 0 rows affected (0.72 sec)Insert some records in the table using insert command −mysql> insert into DemoTable759(FirstName) values('John'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable759(FirstName) values(NULL); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable759(FirstName) values('Carol'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable759(FirstName) values(NULL); Query OK, 1 row ...
Read MoreHow do I set the default value for a column in MySQL?
To set the default value, use the DEFAULT keyword.Let us first create a table −mysql> create table DemoTable758 ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, FirstName varchar(100) ); Query OK, 0 rows affected (0.66 sec)Following is the query to set default value for a column −mysql> alter table DemoTable758 add column Colors ENUM('RED', 'GREEN', 'BLUE', 'ORANGE', 'YELLOW') DEFAULT 'YELLOW'; Query OK, 0 rows affected (0.44 sec) Records: 0 Duplicates: 0 Warnings: 0Let us check the description of table once again −mysql> desc DemoTable758;This will produce the following output -+-----------+----------------------------------------------+------+-----+---------+----------------+ | Field | Type ...
Read MoreAlphanumeric Order by in MySQL for strings mixed with numbers
Let’s say you have a VARCHAR column in a table with values are strings and the numbers are on the right side. For example −John1023 Carol9871 David9098Now, consider you want to order by on the basis of these right-side numbers in the entire column. For this, use ORDER BY RIGHT.Let us first create a table −mysql> create table DemoTable757 ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, ClientId varchar(100) ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command −mysql> insert into DemoTable757(ClientId) values('John1023'); Query OK, 1 row ...
Read More