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

Equivalent of Microsoft SQL Server IDENTITY column in MySQL is AUTO_INCREMENT. The IDENTITY in SQL Server acts like AUTO_INCREMENT in MySQL.The syntax is as follows −CREATE TABLE yourTableName (    yourColumnName1 dataType NOT NULL AUTO_INCREMENT,    yourColumnName2 dataType,    .    .    .    N,    PRIMARY KEY(yourColumnName1) );In MySQL, you need to use primary key if your column is auto_increment otherwise MySQL will give an error. Look at the error −mysql> create table EquivalentOfIdentityInMySQL    -> (    -> ProductId int NOT NULL AUTO_INCREMENT,    -> ProductName varchar(30)    -> ); ERROR 1075 (42000) − Incorrect table definition; ... Read More

You can update two columns using SET command separated with comma(, ). The syntax is as follows −UPDATE yourTableName SET yourColumnName1 = ’yourValue1’, yourColumnName2 = ’yourValue2’ where yourCondition;To understand the above syntax, let us create a table. The query to create a table is as follows −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)Insert some records in the table using insert command. The query is as follows −mysql> insert into StudentInformations(StudentFirstName, StudentLastName) ... Read More

To change the year in MySQL date, you need to use DATE_FORMAT() function with UPDATE command. The syntax is as follows.UPDATE yourTableName SET yourDateColumnName = DATE_FORMAT(yourDateColumnName ,'yourYearValue-%m-%d');To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table ChangeYear    -> (    -> id int not null auto_increment,    -> ArrivalTime date,    -> PRIMARY KEY(id)    -> ); Query OK, 0 rows affected (0.83 sec)Insert some records in the table using insert command −mysql> insert into ChangeYear(ArrivalTime) values(date_add(now(), interval -2 year)); Query OK, 1 row affected, 1 warning ... Read More