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

You can remove trailing zeros using TRIM() function. The syntax is as follows.SELECT TRIM(yourColumnName)+0 FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table removeTrailingZeroInDecimal    -> (    -> Id int not null auto_increment,    -> Amount decimal(5, 2),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (1.01 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into removeTrailingZeroInDecimal(Amount) values(405.50); Query OK, 1 row affected (0.22 sec) mysql> insert into removeTrailingZeroInDecimal(Amount) values(23.05); Query OK, ... Read More

To generate serial number i.e. row count in MySQL query, use the following syntax.SELECT @yourVariableName − = @yourVariableName+1 anyAliasName,    yourColumnName1, yourColumnName2, yourColumnName3, ....N from yourTableName ,    (select @yourVariableName − = 0) as yourVariableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table tblStudentInformation    -> (    -> StudentName varchar(20),    -> StudentAge int,    -> StudentMathMarks int    -> ); Query OK, 0 rows affected (0.68 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into tblStudentInformation values('Carol', ... Read More

To get the table column names in alphabetical order, you need to use ORDER BY. The syntax is as follows −SELECT anyReferenceName.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS anyReferenceName WHERE anyReferenceName.TABLE_NAME = ’yourTableName’ ORDER BY anyReferenceName.COLUMN_NAMEFirst, we need to get all the columns and then we need to use ORDER BY. In the above query, we are getting all columns using INFORMATION_SCHEMA.COLUMNS.To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table ColumnsOrder    -> (    -> StudentFirstName varchar(20),    -> Id int,    -> StudentAge int,    -> StudentLastName varchar(20)   ... Read More

To check a string contains numbers, you can use regexp i.e. Regular Expressions. The syntax is as follows −SELECT *FROM yourTableName where yourColumnName REGEXP ‘[0-9]’;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table StringContainsNumber    -> (    -> Id int not null auto_increment,    -> Words text,    -> primary key(Id)    -> ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into StringContainsNumber(Words) values('He12345llo'); Query OK, 1 row affected (0.19 sec) ... Read More