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
MySQL Articles
Page 33 of 355
Order 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 MoreSort in a string mixed with numbers in MySQL?
Use ORDER BY with some cases. Let us create a table −mysql> create table demo18 −> ( −> value text −> ); Query OK, 0 rows affected (1.18 sec)Insert some records into the table with the help of insert command −mysql> insert into demo18 values('John Smith'); Query OK, 1 row affected (0.06 sec) mysql> insert into demo18 values('2J John has 58'); Query OK, 1 row affected (0.17 sec) mysql> insert into demo18 values('2J John has 9'); Query OK, 1 row affected (0.09 sec)Display records from the table using select statement −mysql> select *from demo18;This will produce the following ...
Read MoreCombine SELECT & SHOW command results in MySQL?
To combine SELECT and SHOW command results into one, use the below query −select @anyVariableName1 as anyAliasName1, @anyVariableName1 as anyAliasName2, ......N;To combine the SELECT and SHOW, first create and initialize the first variable. Following is the query −mysql> set @first_name='John'; Query OK, 0 rows affected (0.00 sec)To combine the SELECT and SHOW, create and initialize the second variable. Following is the query −mysql> set @last_name='Smith'; Query OK, 0 rows affected (0.00 sec)Following is the query to combine the SELECT and SHOW command −mysql> select @first_name as EmployeeFirstName, @last_name as EmployeeLastName;This will produce the following output −+-------------------+------------------+ | EmployeeFirstName | EmployeeLastName ...
Read More