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
Articles on Trending Technologies
Technical articles with clear explanations and examples
How to change a table (create/alter) so that the calculated “Average score” field is shown when querying the entire table without using MySQL INSERT, UPDATE?
Following is the syntax −alter table yourTableName add column yourColumnName yourDataType generated always as ((yourColumName1+yourColumName2+....N) / N) virtual;Let us create a table −mysql> create table demo32 −> ( −> value1 int, −> value2 int −> ); Query OK, 0 rows affected (1.42 sec)Insert some records into the table with the help of insert command −mysql> insert into demo32 values(30, 60); Query OK, 1 row affected (0.16 sec) mysql> insert into demo32 values(20, 40); Query OK, 1 row affected (0.15 sec) mysql> insert into demo32 values(35, 35); Query OK, 1 row affected (0.08 sec)Display records from the table using ...
Read MoreHow to calculate an average value across database rows in MySQL?
For this, you can use AVG(). Following is the syntax −select avg(yourColumnName1) as anyAliasName1, avg(yourColumnName2) as anyAliasName2, avg(yourColumnName3) as anyAliasName3, . . N from yourTableName;Let us create a table −mysql> create table demo31 −> ( −> value1 int, −> value2 int, −> value3 int −> ); Query OK, 0 rows affected (2.27 sec)Insert some records into the table with the help of insert command −mysql> insert into demo31 values(34, 55, 67); Query OK, 1 row affected (0.27 sec) mysql> insert into demo31 values(50, 60, 70); Query OK, 1 row affected (0.16 sec) mysql> insert into demo31 values(100, 200, ...
Read MoreHow 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 More