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
MySQLi Articles
Page 21 of 341
Change value of decimal(19, 2) when inserting into the database in MySQL?
To store the exact real value, you need to use truncate() with 2 decimal point. Let us create a table −Following is the query to create a table.mysql> create table demo59 −> ( −> price decimal(19, 2) −> ); Query OK, 0 rows affected (1.12 sec)Insert some records into the table with the help of insert command −mysql> insert into demo59 values(truncate(15.346, 2)); Query OK, 1 row affected (0.14 sec) mysql> insert into demo59 values(truncate(20.379, 2)); Query OK, 1 row affected (0.72 sec) mysql> insert into demo59 values(truncate(25.555, 2)); Query OK, 1 row affected (0.16 sec) mysql> ...
Read MoreExpression in CASE WHEN Clause doesn't work in MySQL query?
Fir this, use CASE WHEN statement in MySQL correctly. Let us see how.Let us create a table −mysql> create table demo58 −> ( −> id int not null auto_increment primary key, −> first_name varchar(20), −> last_name varchar(20) −> ); Query OK, 0 rows affected (2.15 sec)Insert some records into the table with the help of insert command −mysql> insert into demo58(first_name, last_name) values('John', 'Doe'); Query OK, 1 row affected (0.12 sec) mysql> insert into demo58(first_name, last_name) values('David', 'Smith'); Query OK, 1 row affected (0.29 sec) mysql> insert into demo58(first_name, last_name) values('John', 'Brown'); Query OK, 1 row affected (0.11 ...
Read MoreHow to sort a particular value at the end in MySQL?
For this, you can use ORDER BY. Let us create a table −mysql> create table demo57 −> ( −> id int not null auto_increment primary key, −> full_name varchar(20) −> ); Query OK, 0 rows affected (1.60 sec)Insert some records into the table with the help of insert command −mysql> insert into demo57(full_name) values('John Smith'); Query OK, 1 row affected (0.24 sec) mysql> insert into demo57(full_name) values('David Miller'); Query OK, 1 row affected (0.13 sec) mysql> insert into demo57(full_name) values('Not Known'); Query OK, 1 row affected (0.13 sec) mysql> insert into demo57(full_name) values('Chris Brown'); Query OK, 1 ...
Read MoreMultiple data input at the same time in MySQL?
Following is the syntax −insert into yourTableName values(yourValue1, yourValue2, .....N), (yourValue1, yourValue2, .....N), (yourValue1, yourValue2, .....N), (yourValue1, yourValue2, .....N), . . . NLet us create a table −mysql> create table demo56 −> ( −> id int, −> first_name varchar(20), −> last_name varchar(20), −> age int −> ); Query OK, 0 rows affected (1.91 sec)Insert some records into the table with the help of insert command −mysql> insert into demo56 values(1, 'John', 'Smith', 23), −> (2, 'David', 'Miller', 21), −> (3, 'Chris', 'Brown', 22), −> (4, 'Carol', 'Taylor', 20); Query OK, 4 rows affected (0.10 sec) Records: 4 Duplicates: 0 Warnings: ...
Read MorePlacing order according to the condition in MySQL?
For this, use ORDER BY CASE WHEN statement.Let us create a table −mysql> create table demo51 −> ( −> id int not null auto_increment primary key, −> name varchar(20) −> ); Query OK, 0 rows affected (1.08 sec)Insert some records into the table with the help of insert command −mysql> insert into demo51(name) values('John'); Query OK, 1 row affected (0.15 sec) mysql> insert into demo51(name) values('Bob'); Query OK, 1 row affected (0.09 sec) mysql> insert into demo51(name) values('David'); Query OK, 1 row affected (0.35 sec) mysql> insert into demo51(name) values('Sam'); Query OK, 1 row affected (0.14 sec)Display ...
Read MoreSum of digits of year in MySQL?
At first, you need to extract the last digit and add the extracted value. And the same goes in till we get the sum of all digits of the year, for example, for the year 2020 −2 + 0 + 2 + 0 = 4The concept is as follows to extract the last digit from the year. Following is the query −select floor(@yourVariableName % 10);Following is the query to sum the digits of year −mysql> set @year_column_value = 2020; Query OK, 0 rows affected (0.00 sec) mysql> select −> floor(@year_column_value / 1000) −> + floor(@year_column_value % 1000 / 100) −> ...
Read MoreCreating a table with a TIMESTAMP field in MySQL?
For this, you can use TIMESTAMP keyword in MySQL.Let us create a table −mysql> create table demo50 −> ( −> id int not null auto_increment primary key, −> start_date timestamp default current_timestamp not null, −> end_date timestamp default current_timestamp not null −> ); Query OK, 0 rows affected (1.35 sec)Insert some records into the table with the help of insert command −mysql> insert into demo50 values(); Query OK, 1 row affected (0.15 sec) mysql> insert into demo50(end_date) values('2020−12−21'); Query OK, 1 row affected (0.07 sec) mysql> insert into demo50(start_date) values('2020−01−01'); Query OK, 1 row affected (0.14 sec)Display records ...
Read MoreSelect all records if it contains specific number in MySQL?
For this, use concat() along with LIKE. Following is the syntax −select *from yourTableName where concat(', ', yourColumnName, ', ') like '%, yourValue, %';Let us create a table −mysql> create table demo49 −> ( −> id varchar(20) −> , −> first_name varchar(20) −> ); Query OK, 0 rows affected (1.45 sec)Insert some records into the table with the help of insert command −mysql> insert into demo49 values('4, 5, 6', −> 'Adam'); Query OK, 1 row affected (0.20 sec) mysql> insert into demo49 values('5, 3, 2', 'Mike'); Query OK, 1 row affected (0.19 sec) mysql> insert into demo49 values('3, ...
Read MoreAppend wildcards in SELECT with MySQL?
For appending, use the concept of concat(). The syntax is as follows −select *from yourTableName where yourColumnName like concat('%', yourValue, '%');Let us create a table −mysql> create table demo48 -> ( −> id int not null auto_increment primary key, −> name varchar(20) −> ); Query OK, 0 rows affected (0.70 sec)Insert some records into the table with the help of insert command −mysql> insert into demo48(name) values('John Smith'); Query OK, 1 row affected (0.12 sec) mysql> insert into demo48(name) values('John Doe'); Query OK, 1 row affected (0.12 sec) mysql> insert into demo48(name) values('Adam Smith'); Query OK, 1 row ...
Read MoreWhat is the MySQL syntax error in this query – Creating a table with reserved keyword?
Let’s say we tried creating a table with name “groups”, which is a reserved keyword in MySQL You cannot use “groups” because groups is a reserved keyword in MySQL.Following error occurred while creating a table with name “groups” −mysql> create table groups −> ( −> id int, −> name varchar(40) −> ); 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 'groups ( id int, name varchar(40) )' at line 1In order to create a table with reserved keyword, you need ...
Read More