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 213 of 547
How to use the name of the current database to delete it in MySQL?
To get the current database, you can use the SELECT DATABASE() −select database();Following is the syntax −set @anyVariableName = database(); select @anyVariableName; set @anyVariableName2 = concat('drop database ', @yourVariableName); prepare anyVariableName3 from @yourVariableName2; execute yourVariableName3;Let us execute the above query in order to get current database and delete it −mysql> set @currentDatabase = database(); Query OK, 0 rows affected (0.00 sec) mysql> select @currentDatabase; +------------------+ | @currentDatabase | +------------------+ | employeeonboard | +------------------+ 1 row in set (0.00 sec) mysql> set @sqlQuery = concat('drop database ', @currentDatabase); Query OK, 0 rows affected (0.00 sec) mysql> prepare ...
Read MoreSelect from another column if selected value is '0' in MySQL?
For this, use IF() in MySQL. The syntax is as follows −select IF(yourColumnName1=0, yourColumnName2, yourColumnName1) as anyAliasName from yourTableName;Let us create a table −mysql> create table demo30 −> ( −> id int not null auto_increment primary key, −> value int, −> original_value int −> ) −> ; Query OK, 0 rows affected (1.87 sec)Insert some records into the table with the help of insert command −mysql> insert into demo30(value, original_value) values(50, 10); Query OK, 1 row affected (0.10 sec) mysql> insert into demo30(value, original_value) values(1000, 0); Query OK, 1 row affected (0.13 sec) mysql> insert into demo30(value, original_value) ...
Read MoreOrder by multiple columns not working as expected in MySQL?
Following is the syntax to order by multiple columns −select *from yourTableName order by yourColumnName1 DESC, yourColumnName2, yourColumnName3;Let us create a table −mysql> create table demo29 −> ( −> value1 int, −> value2 int −> ); Query OK, 0 rows affected (1.67 sec)Insert some records into the table with the help of insert command −mysql> insert into demo29 values(10, 500); Query OK, 1 row affected (0.15 sec) mysql> insert into demo29 values(14, 400); Query OK, 1 row affected (0.14 sec) mysql> insert into demo29 values(9, 500); Query OK, 1 row affected (0.12 sec) mysql> insert into demo29 ...
Read MoreHow to select record except the lower value record against a specific value in MySQL?
For this, you need to use the WHERE clause. Following is the syntax −select *from yourTableName where yourColumnName > yourValue;Let us create a table −mysql> create table demo27 −> ( −> id int not null auto_increment primary key, −> value int −> ); Query OK, 0 rows affected (3.14 sec)Insert some records into the table with the help of insert command −mysql> insert into demo27(value) values(50); Query OK, 1 row affected (0.12 sec) mysql> insert into demo27(value) values(500); Query OK, 1 row affected (0.20 sec) mysql> insert into demo27(value) values(100); Query OK, 1 row affected (0.17 sec) ...
Read MoreHow to replace only the first repeated value in a string in MySQL
For this, you can use REGEXP_REPLACE(). Let’s say our string is −This is my first MySQL query. This is the first tutorial. I am learning for the first time.We need to replace only the 1st occurrence of a specific word, let’s say “first”. The output should be −This is my second MySQL query. This is the first tutorial. I am learning for the first time.Let us create a table −mysql> create table demo26 −> ( −> value text −> ); Query OK, 0 rows affected (2.04 sec)Insert some records into the table with the help of insert command −mysql> insert ...
Read MoreSpringboot + JSP + Spring Security: Failed to configure a DataSource. How to configure DataSource in MySQL?
To configure a DataSource in Springboot, you can define DataSource into application.properties.The application.properties is as follows for Springboot −spring.datasource.username=yourUserName spring.datasource.password=yourPassword spring.datasource.url=yourDatabaseUrl spring.datasource.driver-class-name=yourDriverClassNameThe project structure is as follows −ExampleTo understand the above concept, let us create a controller class with spring boot. The Java code is as follows −package com.demo.controller; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.Query; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; @RestController @RequestMapping("/users") public class DisplayController { @Autowired EntityManager entityManager; @GetMapping("/getdata") public String getAll() { Query data= entityManager.createNativeQuery("select first_name from demo25"); List allData= data.getResultList(); return ...
Read MoreSet default value to a JSON type column in MySQL?
To set default value, use the DEFAULT constraint as in the below syntax −alter table yourTableName modify column yourColumnName JSON NOT NULL DEFAULT ( JSON_OBJECT() );Let us create a table −mysql> create table demo24 −> ( −> employee_information text −> ) −> ; Query OK, 0 rows affected (1.43 sec)Here is the description of table. Following is the query −mysql> desc demo24;This will produce the following output −+----------------------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------+------+------+-----+---------+-------+ | employee_information | text | YES | | NULL ...
Read MoreMySQL query to fetch the maximum cumulative value
For this, use aggregate function COUNT(*) along with subquery. GROUP BY is also used.Let us create a table −mysql> create table demo23 −> ( −> id int not null auto_increment primary key, −> value1 int, −> value2 int −> ); Query OK, 0 rows affected (1.65 sec)Insert some records into the table with the help of insert command −mysql> insert into demo23(value1, value2) values(5, 600); Query OK, 1 row affected (0.20 sec) mysql> insert into demo23(value1, value2) values(20, 800); Query OK, 1 row affected (0.06 sec) mysql> insert into demo23(value1, value2) values(7, 400); Query OK, 1 row affected ...
Read MoreInsert the data into Table C IF The data is not in Table B while Comparing to Table A in MySQL?
For this, use left join on table A and B. Let us create the first table −mysql> create table demo20 −> ( −> id int, −> name varchar(20) −> ); Query OK, 0 rows affected (1.87 sec)Insert some records into the table with the help of insert command −mysql> insert into demo20 values(100, 'John'); Query OK, 1 row affected (0.07 sec) mysql> insert into demo20 values(101, 'Bob'); Query OK, 1 row affected (0.24 sec) mysql> insert into demo20 values(102, 'Mike'); Query OK, 1 row affected (0.12 sec) mysql> insert into demo20 values(103, 'Carol'); Query OK, 1 row ...
Read MoreHow to use quotes correctly while using LIKE with search variable in MySQL in Java?
Following is the correct syntax to use LIKE with search variable −String sqlQuery; sqlQuery = "select *from yourTableName where yourColumnName like '%" +yourSearchVariableName + "%'";Let us create a table −mysql> create table demo19 −> ( −> id int not null auto_increment primary key, −> name varchar(50) −> ); Query OK, 0 rows affected (3.48 sec)Insert some records into the table with the help of insert command −mysql> insert into demo19(name) values('John Smith'); Query OK, 1 row affected (0.15 sec) mysql> insert into demo19(name) values('David Miller'); Query OK, 1 row affected (0.15 sec) mysql> insert into demo19(name) values('Adam Smith'); ...
Read More