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
Articles on Trending Technologies
Technical articles with clear explanations and examples
Limit the count using GROUP BY in MySQL
Let us first create a table −mysql> create table DemoTable ( UserId int, UserMessage varchar(100) ); Query OK, 0 rows affected (0.51 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(1, 'Hi'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable values(2, 'Hello'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values(2, 'Good'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values(1, 'Nice'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values(1, 'Awesome'); Query OK, 1 row affected (0.16 sec) mysql> insert ...
Read MoreHow to get MySQL query result in same order as given by IN clause?
For this, you can use IN() along with ORDER BY FIELD(). Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, FirstName varchar(100) ); Query OK, 0 rows affected (0.64 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(FirstName) values('Chris'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable(FirstName) values('Robert'); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable(FirstName) values('Mike'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(FirstName) values('Sam'); Query OK, 1 row affected (0.12 sec) mysql> insert into ...
Read MoreHow to display all the MySQL tables in one line?
Use information_schema.tables to display all the tables. With that, se the database name as well, so that you can display tables only from a specific database.Let us now display all the tables in the database “web” −mysql> select group_concat(table_name) from information_schema.tables where table_schema='web';This will produce the following output −+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | group_concat(table_name) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | demo_table725, demotabe619, demotabe620, demotable211, demotable212, demotable213, demotable214, demotable215, demotable216, demotable217, demotable218, demotable219, demotable220, demotable221, demotable222, demotable223, demotable224, demotable225, demotable226, demotable227, demotable228, demotable229, demotable230, demotable231, demotable232, demotable233, demotable234, demotable235, demotable236, demotable237, demotable238, demotable239, demotable240, demotable241, demotable244, demotable245, demotable246, demotable247, demotable248, demotable249, demotable250, demotable251, demotable252, demotable 253, demotable254, ...
Read MoreHow to select all the characters after the first 20 characters from a column in MySQL?
Let us first create a table −mysql> create table DemoTable ( Title text ); Query OK, 0 rows affected (0.86 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('C is a good programming language to start'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('Java is good with data structure and algorithm'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('Coding is very important'); Query OK, 1 row affected (0.20 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output ...
Read MoreMySQL query to find single value from duplicates with certain condition by excluding other records using NOT IN
Let us first create a table −mysql> create table DemoTable ( Id int, FirstName varchar(100) ); Query OK, 0 rows affected (0.69 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(100, 'Chris'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(100, 'Robert'); Query OK, 1 row affected (0.48 sec) mysql> insert into DemoTable values(100, 'Mike'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable values(100, 'Sam'); Query OK, 1 row affected (0.48 sec) mysql> insert into DemoTable values(101, 'David'); Query OK, 1 row affected (0.19 sec) mysql> insert ...
Read MoreDelete records from a MySQL table by excluding non-deleted records using NOT IN
Let us first create a table −mysql> create table DemoTable ( FirstName varchar(100) ); Query OK, 0 rows affected (0.65 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Chris'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable values('Robert'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values('Mike'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('Sam'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values('David'); Query OK, 1 row affected (0.12 sec)Display all records from the table using select statement −mysql> select ...
Read MoreReturn only the first 15 characters from a column with string values in MySQL
To return only the first 15 characters from string values, use the MySQL SUBSTR() function.Let us first create a table −mysql> create table DemoTable ( Title varchar(100) ); Query OK, 0 rows affected (0.69 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Introduction to MySQL'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('Introduction to Java'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('C in Depth with data structure and algorithm'); Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −mysql> ...
Read MoreAdd a percentage (%) sign at the end to each value while using MySQL SELECT statement
To add percentage sign at the end, use CONCAT() function. Let us first create a table −mysql> create table DemoTable ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentName varchar(100), StudentScore int ); Query OK, 0 rows affected (0.68 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(StudentName, StudentScore) values('John', 65); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable(StudentName, StudentScore) values('Chris', 98); Query OK, 1 row affected (0.30 sec) mysql> insert into DemoTable(StudentName, StudentScore) values('Robert', 91); Query OK, 1 row affected (0.09 sec)Display all records from the table using select ...
Read MoreAdd leading zeros to a MySQL column?
To add leading zeros, you can use LPAD(). Let us first create a table −mysql> create table DemoTable ( Code varchar(100) ); Query OK, 0 rows affected (0.87 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('JS'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('CB'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values('DM'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('CT'); Query OK, 1 row affected (0.07 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce ...
Read MoreHow to convert yyyymmdd in INT type to date?
For this, you can use the DATE() function. Let us first create a table −mysql> create table DemoTable ( Number int ); Query OK, 0 rows affected (0.48 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(20190108); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(20161231); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(20170411); Query OK, 1 row affected (0.09 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+----------+ | Number | +----------+ | 20190108 | ...
Read More