Let us first create a table −mysql> create table DemoTable1 (FirstName varchar(100)); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values('Bob'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1 values('James'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1 values('John'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1 values('David'); Query OK, 1 row affected (0.18 sec)Display all records from the table using select statement −mysql> select *from DemoTable1;This will produce the following output -+-----------+ | FirstName | +-----------+ | Bob ... Read More

Let us first create a table −mysql> create table DemoTable    (       Id int,       Name varchar(100)    ); Query OK, 0 rows affected (1.40 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(100, 'John'); Query OK, 1 row affected (0.44 sec) mysql> insert into DemoTable values(101, 'Chris'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values(102, 'Chris'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values(103, 'Chris'); Query OK, 1 row affected (1.05 sec) mysql> insert into DemoTable values(104, 'David'); Query OK, 1 ... Read More

To compare date strings, use STR_TO_DATE() from MySQL.Let us first create a table −mysql> create table DemoTable712 (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    ArrivalDate varchar(100) ); Query OK, 0 rows affected (0.65 sec)Insert some records in the table using insert command −mysql> insert into DemoTable712(ArrivalDate) values('10.01.2019'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable712(ArrivalDate) values('11.12.2018'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable712(ArrivalDate) values('01.11.2017'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable712(ArrivalDate) values('20.06.2016'); Query OK, 1 row affected (0.23 sec)Display all records from the table using select ... Read More

The IN uses an index while OR does not use an index in MySQL.Let us first create a table −mysql> create table DemoTable711 (    Id int,    Name varchar(100) ); Query OK, 0 rows affected (0.92 sec)Insert some records in the table using insert command −mysql> insert into DemoTable711 values(100, 'Chris'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable711 values(101, 'Robert'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable711 values(102, 'Carol'); Query OK, 1 row affected (0.35 sec) mysql> insert into DemoTable711 values(103, 'Mike'); Query OK, 1 row affected (0.17 sec) mysql> insert ... Read More

For this, use INTERVAL 8 MONTH and fetch records 8 months from the current date −select *from yourTableName where yourColumnName>= (CURRENT_DATE() - INTERVAL 8 MONTH);Note − Let’s say the current date is: 2018-02-06Let us first create a table −mysql> create table DemoTable (StudentName varchar(100), AdmissionDate date); Query OK, 0 rows affected (0.75 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John', '2019-01-21'); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable values('Chris', '2019-10-04'); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable values('Robert', '2018-02-01'); Query OK, 1 row affected (0.16 sec) ... Read More

The Database Error #1064 may occur due to incorrect syntax. For example, let’s say we are creating the below table −mysql> create table DemoTable    (       UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,       UserName varchar(100),       UserAge int,       UserAddress varchar(200),       UserCountryName varchar(100) ,       isMarried boolean,    );This will produce the following output i.e. error −ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' ... Read More

You cannot give MySQL stored procedure parameter with @ in the beginning. You can give @ sign in user-defined variables.Following is the syntax −SET @yourVariableName=yourValue;Let us implement the above syntax to correctly place @sign −mysql> DELIMITER // mysql> CREATE PROCEDURE declare_Variable(IN StudentName varchar(100))    BEGIN       SET @Name=StudentName;       SELECT @Name;    END // Query OK, 0 rows affected (0.12 sec) mysql> DELIMITER ;Now you can call stored procedure with the help of CALL command −mysql> call declare_Variable('John Smith');This will produce the following output −+------------+ | @Name | +------------+ | John Smith | +------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected, 1 warning (0.03 sec)

Yes, use ON DUPLICATE KEY UPDATE. Let us first create a table −mysql> create table DemoTable(Id int NOT NULL PRIMARY KEY, Number int); Query OK, 0 rows affected (0.83 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(1, 190) ON DUPLICATE KEY UPDATE Number=Number+10; Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values(2, 130) ON DUPLICATE KEY UPDATE Number=Number+10; Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values(1, 190) ON DUPLICATE KEY UPDATE Number=Number+10; Query OK, 2 rows affected (0.14 sec) mysql> insert into DemoTable values(2, 130) ON DUPLICATE ... Read More

For this, use IFNULL(). Let us first create a table −mysql> create table DemoTable (Value int); Query OK, 0 rows affected (1.02 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(NULL); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(20); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values(NULL); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values(30); Query OK, 1 row affected (0.13 sec)Display all records from the table using select statement −mysql> ... Read More

To set special characters for a password, use the following syntax −create user 'yourUserName'@'yourHostName' identified by 'yourSpecialCharacterPassword';Let us implement the above syntax in order to create a new user and set password with special characters −mysql> create user 'Mike'@'localhost' identified by 'Mike_123456'; Query OK, 0 rows affected (0.35 sec)Let us check the table where MySQL user and host is stored −mysql> select user, host from MySQL.user;This will produce the following output. The new user created successfully −+------------------+-----------+ | user             | host      | +------------------+-----------+ | Bob              | ... Read More