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 32 of 355
How to select next row pagination in MySQL?
For this, use the LIMIT concept. Let us create a table −mysql> create table demo40 −> ( −> id int not null auto_increment primary key, −> name varchar(40) −> ); Query OK, 0 rows affected (1.73 sec)Insert some records into the table with the help of insert command −mysql> insert into demo40(name) values('Chris'); Query OK, 1 row affected (0.23 sec) mysql> insert into demo40(name) values('David'); Query OK, 1 row affected (0.12 sec) mysql> insert into demo40(name) values('Mike'); Query OK, 1 row affected (0.10 sec) mysql> insert into demo40(name) values('Sam'); Query OK, 1 row affected (0.19 sec) ...
Read MoreHow to select rows with condition via concatenate in MySQL?
For this, you can use CONCAT_WS(). Let us create a table −mysql> create table demo38 −> ( −> user_id int, −> user_first_name varchar(20), −> user_last_name varchar(20), −> user_date_of_birth date −> ); Query OK, 0 rows affected (1.70 sec)Insert some records into the table with the help of insert command −mysql> insert into demo38 values(10, 'John', 'Smith', '1990−10−01'); Query OK, 1 row affected (0.14 sec) mysql> insert into demo38 values(11, 'David', 'Miller', '1994−01−21'); Query OK, 1 row affected (0.13 sec) mysql> insert into demo38 values(11, 'John', 'Doe', '1992−02−01'); Query OK, 1 row affected (0.13 sec) mysql> insert into ...
Read MoreUsing Prepared statement correctly with WHERE condition in case of any value in MySQL Java
For this, you can use PrepareStatement in Java. Following is the syntax −String anyVariableName="select yourColumnName from yourTableName where name = ?"; PreparedStatement ps = (PreparedStatement) con.prepareStatement(yourVariableName); ps.setString(yourColumnIndex, yourValue);Let us create a table −mysql> create table demo37 −> ( −> id int not null auto_increment primary key, −> name varchar(200) −> ); Query OK, 0 rows affected (2.46 sec)Insert some records into the table with the help of insert command −mysql> insert into demo37(name) values('John'); Query OK, 1 row affected (0.09 sec) mysql> insert into demo37(name) values('Bob'); Query OK, 1 row affected (0.08 sec) mysql> insert into demo37(name) values('John'); Query OK, ...
Read MoreHow to select between/before/after dates in MySQL conditionally?
Following is the syntax −select *from yourTableName where yourColumnName1 < yourValue1 AND (yourColumnName2 > yourValue2 OR yourColumnName2 is null);Let us create a table −mysql> create table demo35 −> ( −> id int NOT NULL AUTO_INCREMENT PRIMARY KEY, −> joining_date date, −> relieving_date date −> ); Query OK, 0 rows affected (3.88 sec)Insert some records into the table with the help of insert command −mysql> insert into demo35(joining_date, relieving_date) values('2020−01−10', '2020−07−11'); Query OK, 1 row affected (0.15 sec) mysql> insert into demo35(joining_date, relieving_date) values('2020−05−07', '2020−12−08'); Query OK, 1 row affected (0.17 sec) mysql> insert into demo35(joining_date, relieving_date) values('2020−04−11', '2020−09−18'); Query OK, ...
Read MoreExtract Numeric Date Value from Date Format in MySQL?
For this, use UNIX_TIMESTAMP(). Following is the syntax −select UNIX_TIMESTAMP(STR_TO_DATE(yourColumnName, "%d-%b-%y")) as anyAliasName from yourTableName;Let us create a table −mysql> create table demo34 −> ( −> datevalue varchar(40) −> ); Query OK, 0 rows affected (1.51 sec)Insert some records into the table with the help of insert command −mysql> insert into demo34 values('31−Jan−19'); Query OK, 1 row affected (0.13 sec) mysql> insert into demo34 values('03−Mar−21'); Query OK, 1 row affected (0.12 sec) mysql> insert into demo34 values('13−Jun−20'); Query OK, 1 row affected (0.11 sec)Display records from the table using select statement −mysql> select *from demo34;This will produce the ...
Read MoreCreate a new table in MySQL with specific options with DEFAULT?
For this, use DEFAULT keyword after the column data type.Let us create a table −mysql> create table demo33 −> ( −> id int not null auto_increment primary key, −> name varchar(20) not null, −> start_date date default(current_date), −> end_date date default NULL, −> category enum('Good', 'Medium', 'Low') default 'Low' −> ); Query OK, 0 rows affected (2.32 sec)Insert some records into the table with the help of insert command −mysql> insert into demo33(name) values('John'); Query OK, 1 row affected (0.15 sec) mysql> insert into demo33(name, end_date, category) values('David', '2020−12−21', 'Medium'); Query OK, 1 row affected (0.09 sec) mysql> ...
Read MoreHow 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 More