Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
Database Articles
Page 436 of 547
MySQL query to get records after an interval of 8 months
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 MoreImplement WHERE IN vs OR in MySQL with similar example
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 MoreFix MySQL Database Error #1064?
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 MoreMySQL stored procedure parameters don't seem to work with special character @?
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)
Read MoreIs it possible to make an insert or an update in the same MySQL query?
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 MoreSet the NULL values to 0 and display the entire column in a new column with MySQL SELECT
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 MoreSet special characters for password while creating a new MySQL user?
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 MoreExecute MySQL query from the terminal without printing results?
Let us first create a table −mysql> create table DemoTable709 (Amount int); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command −mysql> insert into DemoTable709 values(100); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable709 values(560); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable709 values(7800); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable709 values(1020); Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −mysql> select *from DemoTable709;This will produce the following output -+--------+ | Amount | +--------+ | 100 ...
Read MoreDisplay records after a particular date in MySQL
Let us first create a table −mysql> create table DemoTable708 ( CustomerName varchar(100), ShippingDate date ); Query OK, 0 rows affected (0.51 sec)Insert some records in the table using insert command −mysql> insert into DemoTable708 values('John', '2019-01-21'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable708 values('Chris', '2019-03-24'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable708 values('Robert', '2019-04-26'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable708 values('David', '2019-07-22'); Query OK, 1 row affected (0.17 sec)Display all records from the table using select statement −mysql> select *from DemoTable708;This will produce the ...
Read MoreMySQL query to order by NULL values
Let us first create a table −mysql> create table DemoTable707 ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentFirstName varchar(100), StudentMarks int ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using insert command −mysql> insert into DemoTable707(StudentFirstName, StudentMarks) values('John', 45); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable707(StudentFirstName, StudentMarks) values(NULL, 65); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable707(StudentFirstName, StudentMarks) values('Chris', 78); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable707(StudentFirstName, StudentMarks) values(NULL, 89); Query OK, 1 row affected (0.19 sec) mysql> insert into ...
Read More