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 More

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 More

For this, you can use GROUP BY along with ORDER BY clause. Let us first create a table −mysql> create table DemoTable1499    -> (    -> StudentName varchar(20),    -> StudentMarks int    -> ); Query OK, 0 rows affected (0.46 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1499 values('Chris', 56); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1499 values('David', 78); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1499 values('Bob', 98); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1499 values('Chris', 45); Query OK, 1 row ... Read More

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 More

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 More

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 More

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 More

For this, use DECIMAL(21,20). Let us first create a table −mysql> create table DemoTable1493    -> (    -> LongValue DECIMAL(21,20)    -> ); Query OK, 0 rows affected (0.48 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1493 values(1.0047464644664677373); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1493 values(5.999999484757773); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1493 values(0.009994995885885); Query OK, 1 row affected (0.21 sec)Display all records from the table using select statement −mysql> select * from DemoTable1493;This will produce the following output −+------------------------+ | LongValue              | +------------------------+ | 1.00474646446646773730 | | 5.99999948475777300000 | | 0.00999499588588500000 | +------------------------+ 3 rows in set (0.00 sec)

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 More

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 More