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
MySQL Articles
Page 302 of 355
MySQL query to find sum of fields with same column value?
Use GROUP BY clause for this. Let us first create a table −mysql> create table sumOfFieldsDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> ClientSerialNumber varchar(100), -> ClientCost int -> ); Query OK, 0 rows affected (0.50 sec)Following is the query to insert some records in the table using insert command −mysql> insert into sumOfFieldsDemo(ClientSerialNumber, ClientCost) values('1111', 450); Query OK, 1 row affected (0.16 sec) mysql> insert into sumOfFieldsDemo(ClientSerialNumber, ClientCost) values('2222', 550); Query OK, 1 row affected (0.15 sec) mysql> insert into sumOfFieldsDemo(ClientSerialNumber, ClientCost) values('3333', 150); Query OK, 1 row affected (0.64 ...
Read MoreHow to set time data type to be only HH:MM in MySQL?
You can use DATE_FORMAT() to set time data type to be only HH:MM. Following is the syntax −select DATE_FORMAT(yourColumnName, "%H:%i") AS anyAliasName from yourTableName;Let us first create a table −mysql> create table DemoTable ( Arrivaltime time ); Query OK, 0 rows affected (0.61 sec)Insert records in the table using insert command −mysql> insert into DemoTable values('08:20'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('05:40'); Query OK, 1 row affected (0.12 sec)Following is the query to display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+-------------+ | Arrivaltime ...
Read MoreHow to sort more than one column at a time in MySQL?
To sort more than one column at a time, you can use ORDER BY clause. Following is the syntax −select yourColumnName1, yourColumnName2, yourColumnName3 from yourTableName order by yourColumnName2, yourColumnName3;Let us first create a table −mysql> create table doubleSortDemo -> ( -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentName varchar(100), -> StudentCountryName varchar(10) -> ); Query OK, 0 rows affected (0.71 sec)Following is the query to insert records in the table using insert command −mysql> insert into doubleSortDemo(StudentName, StudentCountryName) values('John', 'AUS'); Query OK, 1 row affected (0.21 sec) mysql> insert into doubleSortDemo(StudentName, StudentCountryName) values('Sam', ...
Read MoreIs there any easy way to add multiple records in a single MySQL query?
You can easily add multiple items with only one insert command. The syntax is as follows −insert into yourTableName(yourColumnName1, yourColumnName2, ......N) values(yourValue1, yourValue2, ....N), (yourValue1, yourValue2, ....N), ..........N;Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Value1 int, Value2 int, Value3 int ); Query OK, 0 rows affected (0.79 sec)Insert multiple records in the table using insert command −mysql> insert into DemoTable(Value1, Value2, Value3) values(10, 20, 40), (100, 148, 120), (150, 670, 1000), (100000, 200000, 409999); Query OK, 4 rows affected (0.17 sec) Records : 4 Duplicates : ...
Read MoreMySQL select only duplicate records from database and display the count as well?
To select only duplicate records from database and display the count, use HAVING along with aggregate function count(). Let us first create a table −mysql> create table duplicateRecords -> ( -> ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> ClientName varchar(20) -> ); Query OK, 0 rows affected (0.49 sec)Following is the query to insert records in the table using insert command −mysql> insert into duplicateRecords(ClientName) values('John'); Query OK, 1 row affected (0.16 sec) mysql> insert into duplicateRecords(ClientName) values('Carol'); Query OK, 1 row affected (0.17 sec) mysql> insert into duplicateRecords(ClientName) values('John'); Query OK, 1 row affected ...
Read MoreHow to convert string to bitset in MySQL?
To convert string to bitset, use the CONV() method. Let us first create a table −mysql> create table DemoTable ( stringValue BIT(4) ); Query OK, 0 rows affected (3.50 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(CONV('1110', 2, 10) * 1); Query OK, 1 row affected (0.62 sec) mysql> insert into DemoTable values(b'1011'); Query OK, 1 row affected (0.14 sec)Following is the query to display all records from the table using select statement −mysql> select *from DemoTable;Following is the output that displays blank result because the type is bitset −Following is the query ...
Read MoreReorder integer except for value 0 with MySQL?
To reorder integer except for value 0, use the below syntax −select *from yourTableName order by yourColumnName=0 ,yourColumnName;Let us first create a table −mysql> create table reorderIntegerExcept0 -> ( -> value int -> ); Query OK, 0 rows affected (0.70 sec)Following is the query to insert records in the table using insert command −mysql> insert into reorderIntegerExcept0 values(90); Query OK, 1 row affected (0.17 sec) mysql> insert into reorderIntegerExcept0 values(10); Query OK, 1 row affected (0.21 sec) mysql> insert into reorderIntegerExcept0 values(0); Query OK, 1 row affected (0.18 sec) mysql> insert into reorderIntegerExcept0 values(40); ...
Read MoreHow to check whether column value is NULL or having DEFAULT value in MySQL?
You can use the concept of IFNULL() for this. Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Name varchar(100) DEFAULT 'Larry', Age int DEFAULT NULL ); Query OK, 0 rows affected (0.73 sec)Insert records in the table using insert command −mysql> insert into DemoTable(Name, Age) values('John', 23); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(); Query OK, 1 row affected (0.34 sec) mysql> insert into DemoTable(Name) values('David'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(Age) values(24); Query OK, 1 row ...
Read MoreSelect results from the middle of a sorted list in MySQL?
To select results from the middle of a sorted list, use ORDER BY clause along with LIMIT.Let us first create a table. Following is the query −mysql> create table sortedListDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentName varchar(100) -> ); Query OK, 0 rows affected (0.46 sec)Following is the query to insert some records in the table using insert command −mysql> insert into sortedListDemo(StudentName) values('John'); Query OK, 1 row affected (0.62 sec) mysql> insert into sortedListDemo(StudentName) values('Sam'); Query OK, 1 row affected (0.18 sec) mysql> insert into sortedListDemo(StudentName) values('Adam'); ...
Read MoreHow do you select from MySQL where last value in a string = x?
You can use LIKE operator with wildcards to select the records where last value in a string = x, for example ‘10’, ‘15’, etc.Let us first create a table −mysql> create table DemoTable ( ClientId varchar(20) ); Query OK, 0 rows affected (0.68 sec)Insert records in the table using insert command −mysql> insert into DemoTable values('CLI-101'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('CLI-110'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('CLI-201'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('CLI-210'); Query OK, 1 row affected (0.13 sec) ...
Read More