You can SELECT ….WHERE id IN(..) using field() function to order with any column. The syntax is as follows −SELECT *FROM yourTableName WHERE yourColumnName IN(‘value1’, ’value2’, .......N) ORDER BY FIELD(yourColumnName, value1’, ’value2’, .......N);To understand the above syntax, let us create a table −mysql> create table SelectOrderbyField    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(30),    -> Age int,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into SelectOrderbyField(Name, Age) values('John', 23); Query OK, ... Read More

You can update field to add value to an existing value with the help of UPDATE and SET command. The syntax is as follows −UPDATE yourTableName SET yourColumnName = yourColumnName+integerValueToAdd WHERE yourCondition;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table addingValueToExisting    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(30),    -> GameScore int,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.58 sec)Insert records in the table using insert command. The query is as follows −mysql> insert ... Read More

The problem with UNIX_TIMESTAMP() function is that it returns an integer while we want to insert custom date i.e. not any integer part to MySQL date.Do not use UNIX_TIMESTAMP() for your column defined as TIMESTAMP because UNIX_TIMESTAMP() returns an integer.Check the UNIX_TIMESTAMP. The query is as follows −mysql> select UNIX_TIMESTAMP( '2019-01-09 15 −48 −23') AS IntegerValue;The following is the output −+--------------+ | IntegerValue | +--------------+ | 1547029103   | +--------------+ 1 row in set (0.00 sec)Look at the sample output, the UNIX_TIMESTAMP() function returns an integer of corresponding date and time.The syntax is as follows to insert custom date for ... Read More

You can use ALTER command to remove primary key and auto_increment. The syntax is as follows −ALTER TABLE yourTableName DROP PRIMARY KEY, change yourColumnName yourColumnName data type;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table removePrimaryKey    -> (    -> StudentId int NOT NULL AUTO_INCREMENT,    -> StudentFirstName varchar(20),    -> StudentLastName varchar(20),    -> PRIMARY KEY(StudentId)    -> ); Query OK, 0 rows affected (0.47 sec)Check the description of table using DESC command. The syntax is as follows −desc yourTableName;Check the description of the table ... Read More

In MySQL, the ENUM is a data type that is used to create a column with a fixed set of values. It is helpful for the fields that have limited alternatives, such as categories, statuses, and color codes. However, as your application evolves, you might need to add more values to the list such as new colors. MySQL allows us to modify ENUM values without dropping and recreating the column. Adding New Value to an ENUM column For adding new values in an ENUM column in MySQL, you shall use the ALTER TABLE command with the MODIFY Keyword. For ... Read More

You can use ENGINE = InnoDB in place of TYPE = InnoDB, since the usage of TYPE became obsolete in MySQL version 5.1.The version we are using for our example is MySQL version 8.0.12. Let us check the MySQL version. The query is as follows −mysql> select version();The following is the output −+-----------+ | version() | +-----------+ | 8.0.12    | +-----------+ 1 row in set (0.00 sec)Here is the example of TYPE = InnoDB. Error is visible in MySQL 8 −mysql> create table Product_Information    -> (    -> ProductId int,    -> ProductName varchar(10),    -> ProductDeliveryDate datetime ... Read More

To grant a user permission to only view a MySQL view, use the below syntaxGRANT SELECT ON yourDatabaseName.yourViewName TO ' yourUserName@'yourLocalHost';First you need to display all the view names from a table. The syntax is as follows −SHOW FULL TABLES IN yourDatabaseName WHERE TABLE_TYPE LIKE 'VIEW';Now implement the above syntax to display all views from a database. Here I am using the database name ‘test’. The query is as follows −mysql> SHOW FULL TABLES IN test WHERE TABLE_TYPE LIKE 'VIEW';The following is the output −+-------------------------------+------------+ | Tables_in_test                | Table_type | +-------------------------------+------------+ | empidandempname_view ... Read More

Select non-empty column values using NOT IS NULL and TRIM() function. The syntax is as follows.SELECT * FROM yourTableName WHERE yourColumnName IS NOT NULL AND TRIM(yourColumnName) ' ';You can select non-empty value as well as whitespace from column using the same TRIM() function.To understand the syntax we discussed above, let us create a table. The query to create a table is as follows −mysql> create table SelectNonEmptyValues    -> (    -> Id int not null auto_increment,    -> Name varchar(30),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.62 sec)Insert records in the table ... Read More

First, determine the type of MySQL database i.e. whether its engine is InnoDB or MyISAM. To achieve this, use engine column from the information_schema.columns.tables.The syntax is as follows.SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ’yourDatabaseName’ AND TABLE_NAME = ’yourTableName’;Here, I have a table with the name ‘StudentInformations’ −mysql> create table StudentInformations    -> (    -> StudentId int not null auto_increment,    -> StudentFirstName varchar(20),    -> StudentLastName varchar(20),    -> Primary Key(StudentId)    -> ); Query OK, 0 rows affected (0.57 sec)Now you can know the table is using InnoDB or MyISAM using the implementation of above syntax. Our ... Read More

You can OR two like statements using the following syntax −SELECT *FROM yourTableName WHERE (yourColumnName like '%yourValue1%' OR yourColumnNamelike '%yourValue2%') AND yourColumnName = yourValue;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table ORLikeDemo    -> (    -> Id int not null auto_increment,    -> FirstName varchar(15),    -> LastName varchar(15),    -> Primary Key(Id)    -> ); Query OK, 0 rows affected (1.19 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into ORLikeDemo(FirstName, LastName) values('John', 'Smith'); Query OK, ... Read More