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
Articles by AmitDiwan
Page 786 of 840
How to implement MAX(distinct...) in MySQL and what is the difference without using DISTINCT?
Let us see the first syntax, which uses DISTINCT in MAX() −select max(DISTINCT yourColumnName) from yourTableName;The second syntax is as follows. It isn’t using DISTINCT −select max( yourColumnName) from yourTableName;NOTE − Both the above queries give the same result with or without a DISTINCT keyword. MySQL internally converts MAX(yourColumnName) to DISTINCT keyword.Let us now see an example and create a table −mysql> create table DemoTable ( Number int ); Query OK, 0 rows affected (1.50 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(80); Query OK, 1 row affected (0.09 sec) mysql> insert into ...
Read MoreIn MySQL stored procedures, how to check if a local variable is null?
For this, use COALESCE(). Let us implement a stored procedure to check if the local variable is null −mysql> DELIMITER // mysql> CREATE PROCEDURE local_VariableDemo() BEGIN DECLARE value1 int; DECLARE value2 int; select value1, value2; select concat('After checking local variable is null the sum is = ', COALESCE(value1, 0)+COALESCE(value2, 0)); END // Query OK, 0 rows affected (0.19 sec) mysql> DELIMITER ;Call the stored procedure using CALL command −mysql> call local_VariableDemo();This will produce the following output −+--------+--------+ | value1 | value2 | +--------+--------+ | NULL | NULL | +--------+--------+ 1 ...
Read MoreSET only two values for all the rows in a MySQL table based on conditions?
For condition based update, use UPDATE and IF(). Let us first create a table −mysql> create table DemoTable ( Value int ); Query OK, 0 rows affected (0.47 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable values(20); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values(20); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(30); Query OK, 1 row affected (0.10 sec)Display all records ...
Read MoreHow can I match a comma separated list against a value in MySQL?
Let us first create a table −mysql> create table DemoTable ( `Values` varchar(50) ); Query OK, 0 rows affected (1.15 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('23, 45, 78, 56'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('384, 476, 7456'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('68, 8, 88, 89'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values('78, 80, 84'); Query OK, 1 row affected (0.19 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This ...
Read MoreMySQL query to fetch only a single field on the basis of boolean value in another field
Let us first create a table −mysql> create table DemoTable ( EmployeeId int NOT NULL AUTO_INCREMENT PRIMARY KEY, EmployeeName varchar(40), isMarried boolean ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(EmployeeName, isMarried) values('Chris', true); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(EmployeeName, isMarried) values('Robert', false); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(EmployeeName, isMarried) values('Mike', false); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable(EmployeeName, isMarried) values('Bob', true); Query OK, 1 row affected (0.07 sec) mysql> insert into ...
Read MoreHow to insert an array of values in a MySQL table with a single INSERT?
Let us first create a table −mysql> create table DemoTable ( ClientId int, ClientName varchar(50) ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command. Here, we are inserting multiple values using only a single INSERT −mysql> insert into DemoTable values(101,'Adam'),(102,'Chris'),(103,'Robert'),(104,'Sam'),(105,'Mike'); Query OK, 5 rows affected (0.16 sec) Records: 5 Duplicates: 0 Warnings: 0Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+----------+------------+ | ClientId | ClientName | +----------+------------+ | 101 | Adam | | 102 | Chris | | 103 | Robert | | 104 | Sam | | 105 | Mike | +----------+------------+ 5 rows in set (0.00 sec)
Read MoreHow to structure some data in a MySQL database for easier retrieval?
You can use FIND_IN_SET to structure some data in a database for easier retrieval. Let us first create a table −mysql> create table DemoTable ( CountryName SET('US', 'UK', 'AUS') ); Query OK, 0 rows affected (0.49 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('US'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('UK'); Query OK, 1 row affected (0.09 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+-------------+ | CountryName | +-------------+ | US ...
Read MoreSelect rows from a table with date between 90 days ago and now in MySQL
Use MySQL INTERVAL for this. Let us first create a table −mysql> create table DemoTable ( DueDate date ); Query OK, 0 rows affected (1.13 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-07-10'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values('2019-09-02'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('2019-06-01'); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable values('2019-05-10'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('2019-08-21'); Query OK, 1 row affected (0.14 sec)Display all records from the table ...
Read MoreA single MySQL query to insert records (not all) in the second table from the first table
Use nested insert with select in MySQL for this as shown in the below syntax −insert into yourTableName2(yourColumnName1, yourColumnName2, .....N) select yourColumnName1, yourColumnName2, ....N from yourTableName1 where yourCondition;Let us first see an example and create a table −mysql> create table DemoTable1 ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Name varchar(40) ); Query OK, 0 rows affected (0.88 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1(Name) values('Chris'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1(Name) values('David'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1(Name) values('Bob'); Query OK, ...
Read MoreWhat happens when you insert nothing after declaring a column "timestamp default CURRENT_TIMESTAMP"?
In this case, the current timestamp gets inserted into the table column. Let us first create a table −mysql> create table DemoTable ( ArrivalDate timestamp default CURRENT_TIMESTAMP ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(now()); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(); 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 −+---------------------+ | ArrivalDate | +---------------------+ | 2019-09-01 14:08:47 | | 2019-09-01 14:08:50 | +---------------------+ 2 rows in set (0.00 sec)
Read More