Subquery to exclude a particular row in MySQL

Kumar Varma
Published on 11-Jul-2019 12:43:41
Let us first create a table −mysql> create table DemoTable    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> Name varchar(100),    -> Age int    -> ); Query OK, 0 rows affected (0.87 sec)Insert some records in the table using insert command −mysql> insert ... Read More

How to concatenate MySQL distinct query results into a string?

Rama Giri
Published on 11-Jul-2019 12:42:24
Use group_concat() function from MySQL to concatenate. Let us first create a table −mysql> create table DemoTable    -> (    -> Subject varchar(10)    -> ); Query OK, 0 rows affected (0.43 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('C'); Query OK, 1 ... Read More

Order by number of chars in MySQL?

karthikeya Boyini
Published on 11-Jul-2019 12:40:06
To order by number of chars, use ORDER BY and LENGTH() method. Following is the syntax −select *from yourTableName order by LENGTH(yourColumnName) DESC;Let us first create a table −mysql− create table DemoTable    -> (    -> Name varchar(100)    -> ); Query OK, 0 rows affected (0.50 sec)Insert some ... Read More

How to add current date to an existing MySQL table?

karthikeya Boyini
Published on 11-Jul-2019 11:45:29
To update an existing table, use UPDATE. With that, to set the current date, use the CURDATE() method −update yourTableName set yourCoumnName=CURDATE();Let us first create a table −mysql> create table DemoTable -> ( -> DueDate datetime -> ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table ... Read More

How to add subtotal to a table column displaying NULL in MySQL?

Kumar Varma
Published on 11-Jul-2019 11:39:26
Let us first create a table −mysql> create table DemoTable -> ( -> Amount int, -> SubTotal int -> ); Query OK, 0 rows affected (0.65 sec)Insert some records in the table using insert command −ysql> insert into DemoTable(Amount) values(50); Query OK, 1 row affected (0.17 sec) mysql> insert ... Read More

How to select everything before @ in an email-id with in MySQL?

karthikeya Boyini
Published on 11-Jul-2019 11:42:59
Use SUBSTRING_INDEX to select everything before @ in an email-id −select substring_index(yourColumnName, '@', 1) from yourTableName;Let us first create a table −mysql> create table DemoTable -> ( -> EmployeeMailId varchar(100) -> ); Query OK, 0 rows affected (0.52 sec)Insert some records in the table using insert command −mysql> insert into ... Read More

How to set default Field Value in MySQL?

karthikeya Boyini
Published on 11-Jul-2019 11:40:19
To set default field value, use the “default”. Let us first create a table −mysql> create table DemoTable -> ( -> Age int -> ); Query OK, 0 rows affected (0.58 sec)Here is the query to set default field value in MySQL −mysql> alter table DemoTable MODIFY Age int default ... Read More

How to correctly convert a date format into a MySQL date?

karthikeya Boyini
Published on 11-Jul-2019 11:38:22
Use the STR_TO_DATE() method for this. Let us first create a table −mysql> create table DemoTable -> ( -> DueDatetime varchar(100) -> ); Query OK, 0 rows affected (1.03 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('22-06-2019 14:40'); Query OK, 1 row affected (0.20 ... Read More

How to prevent duplicate INSERT in MySQL?

Rama Giri
Published on 11-Jul-2019 11:37:45
For this, you can use UNIQUE INDEX −alter table yourTableName ADD UNIQUE INDEX(yourColumnName1, yourColumnName2, ....N);Let us first create a table −mysql> create table DemoTable    -> (    -> Value1 int,    -> Value2 int    -> ); Query OK, 0 rows affected (0.55 sec)Following is the query to add ... Read More

Is there a way to retrieve the minimum value of fields in MySQL?

Kumar Varma
Published on 11-Jul-2019 11:36:07
Yes, you can use LEAST() function from MySQL −select least(yourColumnName1, yourColumnName2, ...N) from yourTableName;Let us first create a table −mysql> create table DemoTable    -> (    -> Date1 date,    -> Date2 date,    -> Date3 date    -> ); Query OK, 0 rows affected (0.54 sec)Insert some records ... Read More
Advertisements