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
MySQL query for sorting on a columns partial value like number in "John_120
For this, you can use SUBSTRING_INDEX() along with ORDER BY. Let us first create a table −mysql> create table DemoTable1502 -> ( -> StudentId varchar(40) -> ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1502 values('John_120'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1502 values('John_201'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1502 values('Mike_178'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable1502 values('Bob_198'); Query OK, 1 row affected (0.36 sec)Display all records from the table using select ...
Read MoreMySQL query to select multiple rows effectively?
You need to use index to select multiple rows effectively. Let us first create a table −mysql> create table DemoTable1501 -> ( -> Id int NOT NULL PRIMARY KEY, -> URL text -> ); Query OK, 0 rows affected (0.62 sec)Here is the query to create index −mysql> create index id_index on DemoTable1501(Id); Query OK, 0 rows affected (0.23 sec) Records: 0 Duplicates: 0 Warnings: 0Insert some records in the table using insert command −mysql> insert into DemoTable1501 values(101, 'www.facebook.com'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1501 values(110, 'www.google.com'); Query OK, 1 ...
Read MoreDuplicate a MySQL Database without using mysqldump?
To duplicate a MySQL database, the syntax is as follows −create table yourdatabaseName1.yourTableName1 select * from yourdatabaseName2.yourTableName2;Let us first create a table −mysql> use sample; Database changed mysql> create table DemoTable101 -> ( -> Id int, -> Name varchar(20) -> ); Query OK, 0 rows affected (1.45 sec)Insert some records in the table using insert command−mysql> insert into DemoTable101 values(101, 'Sam'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable101 values(102, 'Bob'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable101 values(103, 'David'); Query OK, 1 row affected (0.11 sec)Display all records ...
Read MoreHow to use MySQL LIKE query to search a column value with % in it?
To search a column value with %, the syntax is as follows −select * from yourTableName where yourColumnName LIKE '\%%';Let us first create a table −mysql> create table DemoTable1497 -> ( -> Name varchar(20) -> ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert −mysql> insert into DemoTable1497 values('%JohnSmith'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1497 values('DavidMiller'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1497 values('CarolTaylor%'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1497 values('%DavidMiller'); Query OK, 1 row affected (0.12 ...
Read MoreMySQL query to group results by date and display the count of duplicate values?
Let us first create a table −mysql> create table DemoTable1496 -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> PassengerCode varchar(20), -> ArrivalDate datetime -> ); Query OK, 0 rows affected (0.85 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1496(PassengerCode, ArrivalDate) values('202', '2013-03-12 10:12:34'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable1496(PassengerCode, ArrivalDate) values('202_John', '2013-03-12 11:00:00'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1496(PassengerCode, ArrivalDate) values('204', '2013-03-12 10:12:34'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1496(PassengerCode, ArrivalDate) values('208', '2013-03-14 ...
Read MoreGet two days data (today and yesterday) from a MySQL table with timestamp values
Let us first create a table −mysql> create table DemoTable1495 -> ( -> ShippingDate bigint -> ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1495 values(1570127400); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1495 values(1570213800); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1495 values(1570645800); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable1495 values(1570300200); Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −mysql> select * from DemoTable1495;This will produce the following ...
Read MoreFix MySQL ERROR 1064 (42000) check the manual that corresponds to your MySQL server version for the right syntax to use near ')'
This error may occur if you have used an incorrect syntax. Let’s say the following is the create table statement −mysql> create table DemoTable1492 -> ( -> timestamp TIMESTAMP, -> event int, -> ); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 5You need to remove extra comma above after the event column to fix. Let us first create a −mysql> create table DemoTable1492 -> ( -> timestamp TIMESTAMP, ...
Read MoreHow to store the PayPal decimal amount in the MySQL database?
In order to store PayPal decimal amount in the MySQL database, you can use DECIMAL(10, 2). Let us first create a table −mysql> create table DemoTable1491 -> ( -> Amount DECIMAL(10, 2) -> ); Query OK, 0 rows affected (0.66 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1491 values(987664.50); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1491 values(18783874.90); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1491 values(35363738.50); Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −mysql> select * from ...
Read MoreFind average of corresponding records (Product Price) from duplicate product ids in MYSQL
For this, use AVG() for average and GROUP BY to group records of duplicate column (Product Id). Let us first create a table −mysql> create table DemoTable1490 -> ( -> ProductId varchar(20), -> ProductPrice int -> ); Query OK, 0 rows affected (0.43 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1490 values('PRODUCT_100', 700); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1490 values('PRODUCT_200', 500); Query OK, 1 row affected (0.31 sec) mysql> insert into DemoTable1490 values('PRODUCT_200', 1000); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1490 values('PRODUCT_100', ...
Read MoreGet sum of column with conditions in MySQL
Let us first create a table −mysql> create table DemoTable1489 -> ( -> ProductId int, -> ProductPrice int -> ); Query OK, 0 rows affected (0.49 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1489 values(100, 900); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1489 values(115, 1000); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1489 values(119, 2100); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1489 values(125, 2100); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1489 values(128, 2900); Query OK, 1 ...
Read More