You can use INFORMATION_SCHEMA.COLUMNS to describe all tables in database through a single statement. The syntax is as follows.SELECT *FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=’yourDatabaseName’\GHere I am using my database sample with two tables.The table names are as follows −mytableyourtableImplement the above syntax for your database. The query is as follows −mysql> select * FROM information_schema.columns WHERE table_schema = 'sample'\GThe following is the output describing the two tables in our database.*************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: sample TABLE_NAME: mytable COLUMN_NAME: id ORDINAL_POSITION: 1 COLUMN_DEFAULT: NULL IS_NULLABLE: YES DATA_TYPE: int CHARACTER_MAXIMUM_LENGTH: NULL CHARACTER_OCTET_LENGTH: NULL NUMERIC_PRECISION: 10 NUMERIC_SCALE: 0 DATETIME_PRECISION: NULL CHARACTER_SET_NAME: NULL ... Read More

There is no whoami function in MySQL. The whoami can be used to know the current user in UNIX. Use user() or current_user() function from MySQL for the same purpose.The following is the output.+-----------+ | version() | +-----------+ | 8.0.12    | +-----------+ 1 row in set (0.00 sec)Case 1  −Using CURRENT_USER() function.The query to know the current user is as follows.mysql> select current_user();The following is the output.+----------------+ | current_user() | +----------------+ | root@%         | +----------------+ 1 row in set (0.00 sec)Case 2 − Using USER() function.The query is as follows −mysql> select user();The following is ... Read More

To select increment counter in MySQL, first you need to declare and initialize a variable. The syntax is as follows −set @anyVariableName=0; select yourColumnName, @anyVariableName:=@anyVariableName+1 as anyVariableName from yourTableName;To understand the above syntax and set an increment counter, let us first create a table. The query to create a table is as follows.mysql> create table incrementCounterDemo -> ( -> Name varchar(100) -> ); 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 incrementCounterDemo values('John'); Query OK, 1 row affected (0.18 sec) mysql> insert into incrementCounterDemo values('Carol'); ... Read More

You can select values that meet different conditions on different rows using IN() and GROUP BY. The syntax is as follows −SELECT yourColumnName1 from yourTableName WHERE yourColumnName2 IN(value1, value2, .....N) GROUP BY yourColumnName1 HAVING COUNT(DISTINCT yourColumnName2)=conditionValue;To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table DifferentRows -> ( -> FirstRow int, -> SecondRow int -> ); Query OK, 0 rows affected (0.72 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into DifferentRows values(10, 10); Query OK, 1 row affected ... Read More

To do a count on a union i.e. to get the count of the UNION result, use the below syntax −SELECT COUNT(*) FROM ( SELECT yourColumName1 from yourTableName1 UNION SELECT yourColumName1 from yourTableName2 ) anyVariableName;To understand the above syntax, let us create two tables with some records. The query to create a table is as follows −mysql> create table union_Table1 -> ( -> UserId int -> ); Query OK, 0 rows affected (0.47 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into union_Table1 values(1); Query OK, 1 row affected (0.18 sec) ... Read More

You can use in-built function CONCAT() from MySQL. The syntax is as follows −SELECT CONCAT(('(', yourColumnName1, ', ', yourColumnName2, ', ', yourColumnName3, ...N')')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 Concatenation_OperatorDemo -> ( -> -> VendorId int, -> VendorName varchar(100), -> VendorCountry varchar(100) -> ); Query OK, 0 rows affected (0.69 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into Concatenation_OperatorDemo values(101, 'Carol', 'US'); Query OK, 1 row affected (0.19 sec) mysql> insert ... Read More

The syntax for updating a column with random number between 1-3 is is as follows −update yourTableName set yourColumnName=FLOOR(1+RAND()*3);To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table UpdateNumber1To3 -> ( -> MyNumber int -> ); 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 UpdateNumber1To3 values(100); Query OK, 1 row affected (0.16 sec) mysql> insert into UpdateNumber1To3 values(140); Query OK, 1 row affected (0.25 sec) mysql> insert into UpdateNumber1To3 values(130); ... Read More

Let’s say we have a database “business” with number of tables. If you want to Get the fields in each constraint, then use the below query.The below query is to get the fields in each one of those constraints −mysql> select * −> from information_schema.key_column_usage −> where constraint_schema = 'business';The following is the output −+--------------------+-------------------+--------------------------+---------------+--------------+------------------------------+--------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+ | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME          | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME                   | COLUMN_NAME | ORDINAL_POSITION  | POSITION_IN_UNIQUE_CONSTRAINT | REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME | +--------------------+-------------------+--------------------------+---------------+--------------+------------------------------+--------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+ | def         ... Read More

To store query result in a variable with MySQL, use the SET command. The syntax is as follows −SET @anyVariableName = ( yourQuery);To understand the above concept, let us create a table. The following is the query to create a table −mysql> create table QueryResultDemo    −> (    −> Price int    −> ); Query OK, 0 rows affected (0.59 sec)Now let us insert some records into the table. The following is the query to insert records −mysql> insert into QueryResultDemo values(100); Query OK, 1 row affected (0.17 sec) mysql> insert into QueryResultDemo values(20); Query OK, 1 row ... Read More

You can compare DATE string with string from DATETIME field with the help of DATE() function in MySQL.The syntax is as follows −select *from yourTableName where DATE(yourColumnName) = ’anyDateString’;To understand the above syntax, let us create a table and set some datetime values in the table. The query to create a table −mysql> create table DateTimeDemo −> ( −> ArrivalTime datetime −> ); Query OK, 0 rows affected (0.61 sec)Let us insert some records in the table with the help of insert command. The following is the query to insert records ... Read More