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 345 of 547
How to correctly use DELIMITER in a MySQL stored procedure?
The correct way is as follows −DELIMITER // CREATE PROCEDURE yourStoredProcedureName() BEGIN IF yourCondition then yourStatement1 ; else yourStatement2 ; END IF ; END // DELIMITER ;Let us now see an example and create a stored procedure −mysql> DELIMITER // mysql> CREATE PROCEDURE delimiter_demo() -> BEGIN -> IF 1 THEN -> SELECT "If condition will always true"; -> else -> select "No" ; -> END IF ; -> END -> // Query OK, 0 rows affected (0.17 sec) mysql> DELIMITER ;Now you can call the ...
Read MoreSelect the minimum value from the maximum values of two tables with a single MySQLnquery?
For this, you can use UNION in MySQL. Let us first create a table −mysql> create table DemoTable1 -> ( -> Value int -> ) -> ; Query OK, 0 rows affected (0.48 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values(60); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1 values(78); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1 values(57); Query OK, 1 row affected (0.08 sec)Display all records from the table using select statement −mysql> select * from DemoTable1; This will produce the following ...
Read MoreWhat are the minimum MySQL user privileges to allow optimize and repair?
The select and insert statements are the minimum required MySQL user privileges to allow optimize and repair.You can use below syntax to give insert and select privileges to the user −grant insert, select on yourDatabaseName.* to 'yourUserName'@'localhost';At first, here is the query to create a user −mysql> create user 'Emma'@'localhost' identified by 'Emma123'; Query OK, 0 rows affected (0.26 sec)Here is the query to give grants for the above user −mysql> grant insert, select on web.* to 'Emma'@'localhost'; Query OK, 0 rows affected (0.21 sec)Here is the query to display all grants of the above user −mysql> show grants for ...
Read MoreQuery for implementing MySQL LIKE as MySQL IN?
To implement a query like MySQL IN(), you need to use COUNT(), IF() along with LIKE operator. Let us first create a table −mysql> create table DemoTable -> ( -> Subject varchar(80) -> ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('MySQLMongoDB'); Query OK, 1 row affected (0.86 sec) mysql> insert into DemoTable values('MySQL'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('JavaMySQL'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('MongoDB'); Query OK, 1 row affected (0.20 sec) ...
Read MoreFetch maximum value from multiple columns with null and non-null values?
For this, you can use COALESCE(). For the maximum value, use GREATEST() in MySQL. Let us first create a table −mysql> create table DemoTable -> ( -> Value1 int, -> Value2 int, -> Value3 int -> ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(NULL, 80, 76); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable values(NULL, NULL, 100); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values(56, NULL, 45); Query OK, 1 row affected (0.20 sec) mysql> ...
Read MoreFormat MySQL records (price values) after multiplying them
To format records, use FORMAT(). Let us first create a table −mysql> create table DemoTable -> ( -> Price decimal(10, 4), -> Rate decimal(10, 4) -> ); Query OK, 0 rows affected (0.96 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(1000, 10.2); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(2000, 20.4); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(100, 5); Query OK, 1 row affected (0.16 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the ...
Read MoreMySQL LIKE command doesn't work with strings containing dots to display records beginning with a specific number
To work with strings containing dots, and display records beginning with a specific number, you need to use REGEXP. Let us first create a table −mysql> create table DemoTable -> ( -> GameReleaseVersion varchar(20) -> ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('19.6'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('18.4'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('17.6'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('19.5'); Query OK, 1 row affected ...
Read MoreMySQL query to fetch records before currentdate + 2 weeks?
For this, use the below syntax −select * from yourTableName where yourColumnName < DATE_ADD(CURDATE(), INTERVAL 2 WEEK);Note: The current date is as follows −mysql> select curdate(); +------------+ | curdate() | +------------+ | 2019-10-20 | +------------+ 1 row in set (0.00 sec)Let us first create a table −mysql> create table DemoTable1607 -> ( -> ShippingDate date -> ) -> ; Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1607 values('2019-10-20'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1607 values('2019-11-04'); Query OK, 1 row affected ...
Read MoreImplement Harmonic mean and Quadratic mean in MySQL?
Let us first create a table −mysql> create table DemoTable1606 -> ( -> Value int -> ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1606 values(5); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1606 values(10); Query OK, 1 row affected (0.12 sec)Display all records from the table using select statement −mysql> select * from DemoTable1606;This will produce the following output −+-------+ | Value | +-------+ | 5 | | 10 | +-------+ 2 rows in set (0.00 sec)Here is the ...
Read MoreMySQL update column to NULL for blank values
For this, you can use IF() along with UPDATE command. Let us first create a table −mysql> create table DemoTable1601 -> ( -> FirstName varchar(20) , -> LastName varchar(20) -> ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1601 values('John', 'Doe'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1601 values('Adam', ''); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1601 values('David', 'Miller'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1601 values('Chris', ''); Query OK, 1 row affected ...
Read More