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
Database Articles
Page 249 of 546
MySQL query to select records from a table on the basis of a particular month number?
You can select specific month with the help of MONTH() function. The syntax is as follows −SELECT yourColumnName FROM yourTableName WHERE MONTH(yourColumnName) = yourValue;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table UserLoginTimeInformation -> ( -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> UserLoginDatetime datetime -> ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into UserLoginTimeInformation(UserLoginDatetime) values(date_add(now(), interval 3 month)); Query OK, 1 row affected (0.14 sec) ...
Read MoreWhat happens when a negative value is inserted to UNSIGNED column in MySQL?
Error occurs when you set a negative value to UNSIGNED column in MySQL. For example, let us first create a table with an UNSIGNED field −mysql> create table UnsignedDemo -> ( -> Id int UNSIGNED -> ); Query OK, 0 rows affected (0.79 sec)The error is as follows whenever you insert negative value to column Id which is declared as UNSIGNED −mysql> INSERT INTO UnsignedDemo VALUES(-100); ERROR 1264 (22003): Out of range value for column 'Id' at row 1ExampleHowever, positive values work well for UNSIGNED. The same is shown in the example below. Insert some records in ...
Read MoreSelect and filter the records on month basis in a MySQL table?
You can use aggregate function SUM() with GROUP BY clause to achieve this.Let us create a table. The query to create a table is as follows −mysql> create table SelectPerMonthDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Price int, -> PurchaseDate datetime -> ); Query OK, 0 rows affected (2.34 sec)ExampleInsert some records in the table using insert command with one of them would be the date of purchase. The query is as follows −mysql> insert into SelectPerMonthDemo(Price, PurchaseDate) values(600, date_add(now(), interval -1 month)); Query OK, 1 row affected (0.42 sec) ...
Read MoreHow do I re-format datetime in MySQL?
To re-format datetime in MySQL, you can use DATE_FORMAT(). MySQL gives in the format yyyy-mm-dd.Let us first create a table −mysql> create table DemoTable1558 -> ( -> EmployeeJoiningDate datetime -> ); Query OK, 0 rows affected (1.10 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1558 values(CURDATE()); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1558 values(NOW()); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1558 values('2018-01-10'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1558 values('2017-12-31'); Query OK, 1 row affected (0.18 sec)Display all records from ...
Read MoreHow can we create a MySQL view by using data from multiple tables?
MySQL UNION operator can combine two or more result sets hence we can use UNION operator to create a view having data from multiple tables. To understand this concept we are using the base tables ‘Student_info’ and ‘Student_detail’ having the following data −mysql> Select * from Student_info; +------+---------+------------+------------+ | id | Name | Address | Subject | +------+---------+------------+------------+ | 101 | YashPal | Amritsar | History | | 105 | Gaurav | Chandigarh | Literature | | 125 | Raman | Shimla | Computers | | 130 | Ram ...
Read MoreSelect equal or nearest greater number from table in MySQL
Let us first create a table −mysql> create table DemoTable -> ( -> Value int -> ); Query OK, 0 rows affected (1.33 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(25); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(75); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(100); Query OK, 1 row affected (0.13 sec)Display all records from the table using select statement −mysql> select * from DemoTable;This will produce the following output −+-------+ | Value | +-------+ | 25 | | ...
Read MoreUpdate only a single column value in MySQL
Let us first create a table −mysql> create table DemoTable1605 -> ( -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentName varchar(20), -> StudentCountryName varchar(20) -> ); Query OK, 0 rows affected (0.48 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1605(StudentName, StudentCountryName) values('Adam', 'AUS'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable1605(StudentName, StudentCountryName) values('John', 'US'); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable1605(StudentName, StudentCountryName) values('Bob', 'UK'); Query OK, 1 row affected (0.11 sec)Display all records from the table using select statement −mysql> select ...
Read MoreHow to ignore specific records and add remaining corresponding records (numbers) in MySQL?
Let us first create a table −mysql> create table DemoTable -> ( -> Name varchar(20), -> Amount int -> ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John', 200); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('Chris', 150); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('Mike', 500); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('John', 350); Query OK, 1 row affected (0.18 sec)Display all records from the table using select statement −mysql> ...
Read MoreUpdate with multiple values in MySQL WHERE clause
Let us first create a table −mysql> create table DemoTable -> ( -> Id int, -> Name varchar(20), -> Age int, -> CountryName varchar(10) -> ); Query OK, 0 rows affected (0.81 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(100, 'Chris', 34, 'AUS'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable values(101, 'Chris', 31, 'US'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable values(102, 'David', 25, 'UK'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(103, 'Carol', 28, 'AUS'); ...
Read MoreWorking with WHERE IN() in a MySQL Stored Procedure
Let us first create a table −mysql> create table DemoTable -> ( -> Id int, -> Name varchar(20) -> ); Query OK, 0 rows affected (0.69 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(100, 'Chris'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values(101, 'Bob'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values(102, 'David'); Query OK, 1 row affected (0.18 sec)Display all records from the table using select statement −mysql> select * from DemoTable;This will produce the following output −+------+-------+ | Id ...
Read More