MySQLi Articles - Page 72 of 422

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

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 More

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

MySQL EXPLAIN gives a query execution plan. EXPLAIN can be used in the beginning with SELECT, INSERT, DELETE, REPLACE, and UPDATE.To avoid the complete table scan in database, you need to use index. Let us first create a table −mysql> create table DemoTable1488    -> (    -> StudentId int,    -> StudentName varchar(20),    -> StudentAge int    -> ); Query OK, 0 rows affected (2.18 sec)Here is the query to create index −mysql> create index student_id_index on DemoTable1488(StudentId); Query OK, 0 rows affected (0.90 sec) Records: 0  Duplicates: 0  Warnings: 0insert into DemoTable1488 valueInsert some records in the ... Read More

For this, you can use ORDER BY along with some aggregate function right(). Let us first create a table −mysql> create table DemoTable1487    -> (    -> StudentCode text    -> ); Query OK, 0 rows affected (0.91 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1487 values('560'); Query OK, 1 row affected (0.36 sec) mysql> insert into DemoTable1487 values('789'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable1487 values('STUDENT78'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1487 values('John89'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1487 ... Read More

For this, you can use ORDER BY with LIMIT. Here, LIMIT is used to set the limit (count) of records you want to fetch. Let us first create a table −mysql> create table DemoTable1486    -> (    -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> StudentName varchar(20)    -> ); Query OK, 0 rows affected (0.66 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1486(StudentName) values('Chris Brown'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1486(StudentName) values('David Miller'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1486(StudentName) values('John Doe'); ... Read More

To count, you can use SUM() along with CASE statement for conditions. Let us first create a table −mysql> create table DemoTable1485    -> (    -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> StudentName varchar(20),    -> StudentSubject varchar(20)    -> ); Query OK, 0 rows affected (0.72 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1485(StudentName, StudentSubject) values('Chris', 'MySQL'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1485(StudentName, StudentSubject) values('Robert', 'MongoDB'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable1485(StudentName, StudentSubject) values('Robert', 'MongoDB'); Query OK, 1 row affected ... Read More