You can sort a column, with 0 come last with the help of ORDER BY. The syntax is as follows −select *from yourTableName order by yourFieldName = 0, yourFieldName;To understand the above concept, let us create a table. The query to create a table is as follows −mysql> create table SortColumnZeroAtLastDemo    −> (    −> RankNumber int    −> ); Query OK, 0 rows affected (1.40 sec)Now you can insert records in the table using the following query −mysql> insert into SortColumnZeroAtLastDemo values(100); Query OK, 1 row affected (0.20 sec) mysql> insert into SortColumnZeroAtLastDemo values(0); Query OK, 1 ... Read More

To select the row value containing string in MySQL, use the following syntax.SELECT *FROM yourTableName where yourColumnName like ‘%yourPattern%’;To understand the above syntax, let us first create a table. The query to create a table is as follows.mysql> create table PatternDemo -> ( -> Id int, -> Name varchar(100), -> Age int -> ); Query OK, 0 rows affected (0.97 sec)Insert records in the table using insert command. The query is as follows.mysql> insert into PatternDemo values(1, 'James', 23); Query OK, 1 row affected (0.11 sec) mysql> insert into PatternDemo values(2, 'Joseph', 21); Query OK, 1 row affected (0.18 ... Read More

You can decrement value in MySQL with update command. With this, you can also restrict the value to not reach below 0.The syntax is as follows −update yourTableName set yourColumnName = yourColumnName - 1 where yourColumnName > 0;To avoid the value to go below zero, you can use yourColumnName > 0.To understand the above syntax, let us create a table. The query to create a table.mysql> create table DecrementDemo −> ( −> DecrementValue int −> ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table with insert statement. ... Read More

You can select the row with highest ID in MySQL with the help of ORDER BY with LIMIT OFFSETThe syntax is as follows −select *from yourTableName order by yourColumnName desc limit 1 offset 0;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table HighestIdOrderBy    −> (    −> EmployeeId int,    −> EmployeeName varchar(200)    −> ); Query OK, 0 rows affected (0.58 sec)Insert records in the table with the help of insert command. The query is as follows −mysql> insert into HighestIdOrderBy values(200, 'David'); Query OK, ... Read More

First, create a user and password using CREATE command. The syntax is as follows.CREATE USER 'yourUserName'@'localhost' IDENTIFIED BY 'yourPassword';The syntax to give all privileges of the specific database to the user is as follows.GRANT ALL PRIVILEGES ON yourDatabaseName . * TO 'yourUserName'@'localhost';Now you can implement the above syntaxes to create a user and grant all privileges.Step 1 − Create a userThe query is as follows.mysql> create user 'Adam Smith'@'localhost' IDENTIFIED BY 'Adam123456'; Query OK, 0 rows affected (0.29 sec)Step 2 − Grant all privileges to the user.The query is as follows.mysql> GRANT ALL PRIVILEGES ON test . * TO 'Adam ... Read More

The “Data too long for column” error occurs when you insert more data for a column that does not have the capability to store that data.For Example - If you have data type of varchar(6) that means it stores only 6 characters. Therefore, if you will give more than 6 characters, then it will give an error.Let us create a table to understand the error. The query to create a table is as follows −mysql> create table DataToolongDemo   −> (   −> Name varchar(10) −> ); Query OK, 0 rows affected (0.55 sec)Above, we have created a table ... Read More

To get the count of rows, you need to use information_schema.tables. The syntax is as follows.SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘yourDatabaseName’;Let us implement the above syntax for a database with the name ‘test’. The query is as follows displaying the table names with the count of rows in the table.mysql> SELECT table_name, table_rows -> FROM INFORMATION_SCHEMA.TABLES -> WHERE TABLE_SCHEMA = 'test';The following is the output.+------------------------------------+------------+ | TABLE_NAME | TABLE_ROWS | +------------------------------------+------------+ | _student_trackerdemo | 0 | | _studenttrackerdemo | 0 | | add30minutesdemo | 0 | | addcolumn | 0 | ... Read More

To get age from a D.O.B field in MySQL, you can use the following syntax. Here, we subtract the DOB from the current date.select yourColumnName1, yourColumnName2, ........N, year(curdate())- year(yourDOBColumnName) 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 AgeDemo -> ( -> StudentId int, -> StudentName varchar(100), -> StudentDOB date -> ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command. The query is as follows.mysql> insert into AgeDemo values(1, 'John', '1998-10-1'); Query OK, 1 ... Read More

To get the random value between two values, use MySQL rand() method with floor(). The syntax is as follows.select FLOOR( RAND() * (maximumValue-minimumValue) + minimumValue) as anyVariableName;Let us check with some maximum and minimum value. The maximum value we are considering is 200 and minimum is 100. The random number will be between 100 and 200 including 100 and 200 itself.The query is as follows.mysql> select FLOOR( RAND() * (200-100) + 100) as RandomValue;The following is the output.+-------------+ | RandomValue | +-------------+ | 144 | +-------------+ 1 row in set (0.00 sec)Now ... Read More

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