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 231 of 355
Find 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 MoreGet the minimum value from a list with multiple columns in MySQL?
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 MorePerform filtering on an alias in MySQL?
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 MoreHow to return distinct values in MySQL and their count?
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 MoreMySQL query to ORDER BY `user_id` IN (1,2,3) AND `name` for custom ordering
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 MoreGet all the records with two different values in another column with MySQL
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