You can bulk update MySQL data with one query using CASE command. The syntax is as follows −update yourTableName set yourUpdateColumnName = ( Case yourConditionColumnName WHEN Value1 THEN ‘’UpdatedValue’ WHEN Value2 THEN ‘UpdatedValue’ . . N END) where yourConditionColumnName IN(Value1, Value2, .....N);To understand the above concept, let us create a table. The query to create a table is as follows −mysql> create table UpdateAllDemo −> ( −> BookId int, −> BookName varchar(200) −> ); Query OK, 0 rows affected (1.18 sec)Insert some records in the table using insert ... Read More

To select domain name from email address, you can use in-built SUBSTRING_INDEX() function from MySQL.To understand the concept, let us create a table. The following is the query to create a table.mysql> create table selectDomainNameOnly −> ( −> UserEmailAddress varchar(200) −> ); Query OK, 0 rows affected (0.52 sec)Insert records in the table using insert command. The record will have email-ids from which we need to fetch the domain name. The query is as follows −mysql> insert into selectDomainNameOnly values('John123@yahoo.com'); Query OK, 1 row affected (0.10 sec) mysql> insert into selectDomainNameOnly ... Read More

Set column charset using character SET command. The syntax is as follows −ALTER TABLE yourTableName MODIFY youColumName type CHARACTER SET anyCharcaterSetName;You can use character set name utf8 or something elsE. To set column charset, let us first create a table. The query to create a table is as follows −mysql> create table setCharsetDemo −> ( −> FirstName varchar(60) −> ); Query OK, 0 rows affected (2.09 sec)Now you can check the current column character set with the help of show command. The query is as follows −mysql> show create table setCharsetDemo;The ... Read More

You can use aggregate function count() with group by. The syntax is as follows.select yourColumnName, count(*) as anyVariableName from yourtableName group by yourColumnName;To understand the above syntax, let us create a table. The query to create a table is as follows.mysql> create table CountSameValue -> ( -> Id int, -> Name varchar(100), -> Marks int -> ); Query OK, 0 rows affected (0.70 sec)Insert records in the table using insert command. The query is as follows.mysql> insert into CountSameValue values(1, 'Sam', 67); Query OK, 1 row affected (0.17 sec) mysql> insert into CountSameValue values(2, 'Mike', 87); Query OK, 1 ... Read More

To check for duplicates in MySQL, you can use group by having clause. The syntax is as follows.select yourColumnName1, yourColumnName2, ......N, count(*) as anyVariableName from yourTableName group by yourColumnName1, yourColumnName2 having count(*) > 1;To understand the above syntax, let us create a table. The query to create a table is as follows.mysql> create table DuplicateDemo -> ( -> StudentId int not null, -> StudentFirstName varchar(100), -> StudentLastName varchar(100), -> Primary Key(StudentId) -> ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table using insert command. The query is as follows.mysql> insert into DuplicateDemo values(1, 'John', 'Smith'); Query ... Read More

You can use aggregate function sum() inside COALESCE(). The below syntax returns the sum of all if the record exists otherwise 0 is returned. The syntax is as follows.select COALESCE(sum(yourColumnName2), 0) AS anyVariableName from yourTableName where yourColumnName1 like '%yourValue%';To understand the above syntax, let us create a table. The query to create a table is as follows.mysql> create table SumDemo -> ( -> Words varchar(100), -> Counter int -> ); Query OK, 0 rows affected (0.93 sec)Insert some records in the table using insert command. The query is as follows.mysql> insert into SumDemo values('Are You There', 10); Query OK, 1 ... Read More

To select data where a field has min value, you can use aggregate function min(). The syntax is as follows.SELECT *FROM yourTableName WHERE yourColumnName=(SELECT MIN(yourColumnName) FROM yourTableName);To understand the above syntax, let us create a table. The query to create a table is as follows.mysql> create table MinValueDemo -> ( -> ProductId int, -> ProductName varchar(100), -> ProductPrice int -> ); Query OK, 0 rows affected (0.77 sec)Insert some records in the table using insert command. The query is as follows.mysql> insert into MinValueDemo values(1, 'product-1', 4500); Query OK, 1 row affected (0.14 sec) mysql> insert into MinValueDemo values(2, ... Read More

You can use select case statement for this. The syntax is as follows.select yourColumnName1, yourColumnName2, ...N, case when yourColumnName=1 then 'true' else 'false' end as anyVariableName from yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows.mysql> create table selectReturnDemo -> ( -> Id int, -> Name varchar(100), -> isGreaterthan18 tinyint(1) -> ); Query OK, 0 rows affected (0.62 sec)Now you can insert some records in the table using insert command. The query is as follows.mysql> insert into selectReturnDemo values(1, 'Carol', 0); Query OK, 1 row affected (0.23 sec) mysql> ... Read More

This works in MySQL version 8.0.12. The syntax is as follows.create table yourTableName ( yourColumnName enum(‘Value1’,Value2’,Value3’,......N) default Value1’ or Value2 or Value3,..N );Set the enum type in MySQL with the following query.mysql> create table EnumInMySQL -> ( -> WebCRUD enum('CREATE','READ','UPDATE','DELETE') -> default 'CREATE' -> ); Query OK, 0 rows affected (0.60 sec)The syntax of enum in SQL Server.create table yourTableName ( yourColumnName varchar(100) CHECK(yourColumnName IN (‘Value1’,Value2’,Value3’,......N)) DEFAULT ‘Value1’ or Value2’ or Value3’,......N );Now the query is as follows for enum in SQL Server.

You can use tinyint(1) or bool or boolean. All are synonym. If you use bool or boolean datatype, then it nternally change into tinyint(1).In PHP, the value 0 represents false and 1 represents true. Any other number except 0 is also true.Let us check the internal representation of bool or boolean using a table. The query to create a table is as follows.mysql> create table AddBoolDemo -> ( -> isToggle bool -> ); Query OK, 0 rows affected (1.24 sec)To check the DDL of the table, the following is the query.SHOW CREATE TABLE yourTableName;Let us check the representation of bool ... Read More