To set a variable in MySQL, you need to use the SET command. Following is the syntax:set @yourVariableName:=yourValue; select *from yourTableName where yourColumnName=@yourVariableName;Let us first create a table:mysql> create table DemoTable (    Id int,    FirstName varchar(20),    LastName varchar(20) ); Query OK, 0 rows affected (0.83 sec)Following is the query to insert some records in the table using insert command:mysql> insert into DemoTable values(10, 'Carol', 'Taylor'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values(20, 'John', 'Doe'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values(30, 'John', 'Smith'); Query OK, 1 row ... Read More

Use LPAD() to add 0's to numbers with less than 9 digits. Let us first create a table −mysql> create table DemoTable (    Value varchar(20) ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('3646465'); Query OK, 1 row affected (0.29 sec) mysql> insert into DemoTable values('9485757591'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('485756'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('959585'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('124'); Query OK, 1 row affected ... Read More

To alter row_format to dynamic in MySQL, following is the syntax:ALTER TABLE yourTableName ROW_FORMAT=DYNAMIC;Let us first create a table:mysql> create table DemoTable (    CustomerId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    CustomerName varchar(200),    CustomerAge int,    CustomerAddress varchar(200) ); Query OK, 0 rows affected (0.73 sec)Let us check the description of table using DESC command:mysql> desc DemoTable;This will produce the following output:+-----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra ... Read More

Here, we will use OCTET_LENGTH to check the length of a record since we want the records with length more than 1. Let us first create a table −mysql> create table DemoTable (    UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    UserName varchar(20),    UserGender varchar(20) ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(UserName, UserGender) values('John', 'M'); Query OK, 1 row affected (0.82 sec) mysql> insert into DemoTable(UserName, UserGender) values('Carol', 'Male'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable(UserName, UserGender) values('Mia', 'Female'); Query OK, ... Read More

To delete record with the lowest id, you can use the following syntax:delete from yourTableName order by yourColumnName limit 1;Let us first create a table:mysql> create table DemoTable (    Id int,    Name varchar(20) ); Query OK, 0 rows affected (0.75 sec)Following is the query to insert records in the table using insert command:mysql> insert into DemoTable values(10, 'Larry'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values(100, 'Mike'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values(30, 'Sam'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values(90, 'Chris'); Query ... Read More

To select only those rows where first digit is a number from 0 to 9, use RLIKE. Following is the syntax −select *from yourTableName where yourColumnName RLIKE '^[0-9]+'Let us first create a table −mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    QuestionNumber varchar(200) ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(QuestionNumber) values('1Question'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable(QuestionNumber) values('Question2'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(QuestionNumber) values('311Question'); Query OK, 1 row affected (0.13 sec) ... Read More

To compare DateTime column with only Date, you need to use the Date() method. Following is the syntax. Below, you need to date in the 'yourDateValue':select *from yourTableName where Date(yourColumnName)='yourDateValue';Let us first create a table:mysql> create table DemoTable (    ArrivalTime datetime ); Query OK, 0 rows affected (0.74 sec)Following is the query to insert some records in the table using insert command:mysql> insert into DemoTable values('2019-01-31 02:34:56'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values('2019-04-09 18:20:58'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('2019-05-11 19:45:23'); Query OK, 1 row affected (0.13 ... Read More

Let us first create a table −mysql> create table DemoTable (    UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    UserName varchar(20) DEFAULT 'John' ); Query OK, 0 rows affected (0.76 sec)Let us check the description of table −mysql> desc DemoTable;This will produce the following output −+----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | UserId | int(11) | NO | PRI | NULL ... Read More

When updating a database, you may get a situation to add a new column to capture specific predefined values. For Example, if you are managing student data there will be a column gender that has fixed options like (Female or Male), in this case, an ENUM data type will be perfect. ALTER Command  To add a new enum column to an existing MySQL table, you can use the ALTER command. The MySQL ALTER command is used to modify the existing structure of a table. It enables you to make several kinds of changes, such as ... Read More

Yes, you can use stored procedure to insert into two tables in a single query. Let us first create a table −mysql> create table DemoTable (    StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    StudentFirstName varchar(20) ); Query OK, 0 rows affected (0.56 sec)Here is the query to create second table −mysql> create table DemoTable2 (    ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    ClientName varchar(20),    ClientAge int ); Query OK, 0 rows affected (0.76 sec)Following is the query to create stored procedure to insert into two tables created above −mysql> DELIMITER //    mysql> CREATE PROCEDURE ... Read More