Found 4219 Articles for MySQLi

To get a list of non-empty tables in a particular MySQL database, the following is the syntax −SELECT table_type, table_name, table_schema from information_schema.tables where table_rows >= 1 and table_schema = 'yourDatabaseName';Implement the above syntax for your database. Here, our database is “test”. The query is as follows −mysql> select table_type, table_name ,table_schema from information_schema.tables −> where table_rows >= 1 and table_schema = 'test';The following is the output displaying the non-empty tables in the database “test” −+------------+------------------------------+--------------+ | TABLE_TYPE | TABLE_NAME                   | TABLE_SCHEMA | +------------+------------------------------+--------------+ | BASE TABLE | add30minutesdemo     ... Read More

To find the number of days in month, use the below syntax.select DAY(LAST_DAY(yourColumnName)) as anyVariableName from yourTableName;To understand the above syntax, let us first create a table. The query to create a table is as follows.mysql> create table DaysInaGivenMonth -> ( -> MonthName datetime -> ); Query OK, 0 rows affected (1.62 sec)Insert some records in the table using insert command. The query is as follows.mysql> insert into DaysInaGivenMonth values(now()); Query OK, 1 row affected (0.24 sec) mysql> insert into DaysInaGivenMonth values(date_add(now(), interval -1 month)); Query OK, 1 row affected (0.16 sec) mysql> insert into DaysInaGivenMonth values(date_add(now(), interval ... Read More

You can create DATETIME from DATE and TIME with the help of ADDTIME() function in MySQL. The syntax is as follows −SELECT ADDTIME(CONVERT(yourDateColumnName, datetime), yourTimeColumnName) as anyVariableName from yourTableName;To understand the above concept, let us create a table. The query to create a table is as follows −mysql> create table DateTime −> ( −> DueDate date, −> DueTime time −> ); Query OK, 0 rows affected (1.19 sec)Now you can insert date and time separately. The query to insert is as follows −mysql> insert into DateTime values(curdate(), now()); ... Read More

The Sum() is an aggregate function in MySQL. You can use sum query with if condition. To understand the sum query with if condition, let us create a table.The query to create a table −mysql> create table SumWithIfCondition    −> (    −> ModeOfPayment varchar(100)    −> ,    −> Amount int    −> ); Query OK, 0 rows affected (1.60 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into SumWithIfCondition values('Offline', 10); Query OK, 1 row affected (0.21 sec) mysql> insert into SumWithIfCondition values('Online', 100); Query OK, 1 row affected ... Read More

To not allow any duplicate entry to be entered in a MySQL table, you need to add unique key. The syntax is as follows −alter ignore table yourTableName add constraint unique key(yourColumName);The above syntax sets unique key. To understand the above syntax, let us create a table.The following is the query to create a table −mysql> create table RemoveDuplicateEntry    −> (    −> Id int,    −> Name varchar(100)   −> )ENGINE = MyISAM; Query OK, 0 rows affected (0.15 sec)Now you can implement the syntax discussed in the beginning. The query is as follows −mysql> alter table ... Read More

You can check multiple columns for one value with the help of IN operator. The syntax is as follows −select *from yourTableName where value IN(yourColumnName1, yourColumnName2, ......N);To understand the above concept, let us create a table with some columns. The query to create a table is as follows −mysql> create table OneValueFromAllColumns −> ( −> StudentId int, −> StudentFirstname varchar(200), −> StudentLastname varchar(200), −> StudentAge int −> ); Query OK, 0 rows affected (1.41 sec)Insert some records in the table with the ... Read More

To achieve this, the following is the syntax.UPDATE yourTableName set yourColumnName=concat(ifnull(yourColumnName, ””), ’anyValue1, anyValue2, anyValue);To understand the above syntax, let us first create a table. The query to create a table is as follows -mysql> create table AppendDataDemo -> ( -> StudentId int, -> StudentName varchar(100), -> StudentAge int -> ); Query OK, 0 rows affected (1.54 sec)Insert some records in the table using insert command. The query is as follows.mysql> insert into AppendDataDemo values(101, 'John', 23); Query OK, 1 row affected (0.24 sec) mysql> insert into AppendDataDemo values(102, null, 24); Query OK, 1 row affected (0.74 sec) ... Read More

You can use LIKE with OR operator which works same as IN operator.Let us see the syntax for both the cases −Case 1 − Using Like with OR operator.select *from yourTableName where yourColumnName Like ‘Value1’ or yourColumnName Like ‘Value2’ or yourColumnName Like ‘Value3’ . . . NCase 2 − Using IN operator.select *from yourTableName where IN(value1, value2, value3, .....N);To understand both the syntaxes, let us create a table. The query to create a table is as follows −mysql> create table LikeDemo −> ( −> Id varchar(20) −> ); Query OK, 0 rows affected (0.58 sec)Now you can insert records in ... Read More

You can do this with the help of information_schema.tables. The syntax is as follows -SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024), 2) "MB Size" FROM information_schema.TABLES WHERE table_schema = "yourDatabaseName" ORDER BY (data_length + index_length) ASC;To understand the above syntax, let us implement it for any database. Here, I am using database TEST. Let us see the query for our database TEST.mysql> SELECT TABLE_NAME, table_rows, data_length, index_length, -> round(((data_length + index_length) / 1024 / 1024), 2) "MB Size" -> FROM information_schema.TABLES WHERE table_schema = "test" -> ORDER BY (data_length + index_length) ASC;The following is the ... Read More

To avoid this type of error in MySQL stored procedure, you need to change the delimiter ; to //.Suppose if you are using stored procedure or triggers or even function then you need to change the delimiter. The syntax is as follows.DELIMITER // CREATE PROCEDURE yourProcedureName() BEGIN Statement1, . . N END; // DELIMITER ;To understand the above syntax, let us create a stored procedure. The query to create a stored procedure is ... Read More