Triggers are automatic operations that MySQL performs when something happens in a table, like adding a new record (INSERT), changing a record (UPDATE), or getting rid of a record (DELETE). They come in handy because they can handle repetitive tasks and make sure the info in your database is consistent and accurate without any manual interference. Listing All Triggers in MySQL To list all the triggers in MYSQL we use information_schema.triggers table. This table stores the metadata, which means the details like the trigger name, the action it responds to (INSERT, UPDATE, DELETE), and the table it is associated with. ... Read More

For this, use BIT data type. Let us first create a table −mysql> create table DemoTable(binaryValue BIT(5)); Query OK, 0 rows affected (0.83 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values(15); Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output. Now you can see the records aren’t visible −+-------------+ | binaryValue | +-------------+ |             | |           ... Read More

To get fixed float data type, use DECIMAL(). This will fix the issue of unacceptance. Let us first create a table −mysql> create table DemoTable(Amount DECIMAL(10, 2)); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(2194848.90); Query OK, 1 row affected (0.65 sec) mysql> insert into DemoTable values(90309393.79); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable values(8999999.68); Query OK, 1 row affected (0.30 sec) mysql> insert into DemoTable values(90900000.99); Query OK, 1 row affected (0.26 sec)Display all records from the table using select statement −mysql> select ... Read More

For this, use DATEDIFF() function. Let us first create a table −mysql> create table DemoTable(DOB datetime, CurrentDate datetime); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('1995-01-21', CURDATE()); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('1998-11-01', CURDATE()); Query OK, 1 row affected (0.39 sec) mysql> insert into DemoTable values('2000-10-24', CURDATE()); Query OK, 1 row affected (0.22 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+---------------------+---------------------+ | DOB                 ... Read More

For this, use the ORDER BY FIELD() ASC. Let us first create a table −mysql> create table DemoTable(Title varchar(100)); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Java'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('C'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('Python'); Query OK, 1 row affected (0.63 sec) mysql> insert into DemoTable values('MongoDB'); Query OK, 1 row affected (0.20 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+---------+ ... Read More

To list temporary table columns in MySQL, let us first create a temporary table.Here’s an example. We have created a temporary table with some columns that includes the details of a student −mysql> CREATE TEMPORARY TABLE DemoTable745 (    StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    StudentName varchar(100),    StudentAge int,    StudentAddress varchar(100),    StudentCountryName varchar(20) ); Query OK, 0 rows affected (0.00 sec)Following is the query to list temporary table columns in MySQL−mysql> show columns from DemoTable745;This will produce the following output -+--------------------+--------------+------+-----+---------+----------------+ | Field              | Type         ... Read More

Yes, we can replace all the digits of column values to zero except the first digit. Let us first see an example and create a table −mysql> create table DemoTable744 (Number varchar(100)); Query OK, 0 rows affected (0.47 sec)Insert some records in the table using insert command −mysql> insert into DemoTable744 values('537737736252'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable744 values('989000099999'); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable744 values('343225666666664533'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable744 values('4322111899494'); Query OK, 1 row affected (0.15 sec)Display all records from the table ... Read More

To keep only first two characters and delete rest of the characters, use SUBSTRING().Let us first create a table −mysql> create table DemoTable743 (SubjectName varchar(100)); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable743 values('MySQL'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable743 values('Java'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable743 values('MongoDB'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable743 values('Python'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable743 values('Data Structure'); Query OK, 1 row affected (0.68 ... Read More

For this, use REGEXP. Let us first create a table −mysql> create table DemoTable(SubjectCode varchar(100)); Query OK, 0 rows affected (0.89 sec)Insert some records in the table using insert command. The records consists of text, numbers and special characters −mysql> insert into DemoTable values('Java899@22'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('C#'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values('~Python232'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('MongoDB%'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable values('C123456'); Query OK, 1 row affected (0.37 sec)Display all ... Read More

Let us first create a table −mysql> create table DemoTable742 (Value varchar(100)); Query OK, 0 rows affected (2.91 sec)Insert some records in the table using insert command −mysql> insert into DemoTable742 values('632535MIT'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable742 values('US%UK'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable742 values('56%78'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable742 values('%_1234'); Query OK, 1 row affected (0.09 sec)Display all records from the table using select statement −mysql> select *from DemoTable742;This will produce the following output -+-----------+ | Value | ... Read More