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
MySQLi Articles
Page 22 of 341
Remove specific fields/ rows and show other records in MySQL?
For this, use CASE WHEN statement in MySQL. Let us create a table −mysql> create table demo47 −> ( −> first_name varchar(20), −> last_name varchar(20) −> ); Query OK, 0 rows affected (1.57 sec)Insert some records into the table with the help of insert command −mysql> insert into demo47 values('John', 'Smith'); Query OK, 1 row affected (0.18 sec) mysql> insert into demo47 values('David', 'Miller'); Query OK, 1 row affected (0.11 sec) mysql> insert into demo47 values('John', 'Doe'); Query OK, 1 row affected (0.12 sec) mysql> insert into demo47 values('Chris', 'Brown'); Query OK, 1 row affected (0.12 sec)Display ...
Read MoreHow to convert MM/YY to YYYY-MM-DD with a specific day in MySQL?
To convert, use STR_TO_DATE(), as in the below syntax. Concatenate the day value with CONCAT() −select str_to_date(concat('yourDateValue/', yourColumnName), '%d/%m/%y') as anyAliasName from yourTableName;Let us create a table −mysql> create table demo46 −> ( −> id int not null auto_increment primary key, −> short_date varchar(20) −> ); Query OK, 0 rows affected (0.60 sec)Insert some records into the table with the help of insert command −mysql> insert into demo46(short_date) values('09/18'); Query OK, 1 row affected (0.08 sec) mysql> insert into demo46(short_date) values('12/20'); Query OK, 1 row affected (0.12 sec) mysql> insert into demo46(short_date) values('11/20'); Query OK, 1 row affected ...
Read MoreSelect highest salary in MySQL?
For this, you can use MAX(). The syntax is as follows −select MAX(yourColumnName) AS anyAliasName from yourTableName;Let us create a table −mysql> create table demo44 −> ( −> employee_id int not null auto_increment primary key, −> employee_name varchar(20), −> employee_salary int −> ) −> ; Query OK, 0 rows affected (1.27 sec)Insert some records into the table with the help of insert command −mysql> insert into demo44(employee_name, employee_salary) values('John', 3000); Query OK, 1 row affected (0.13 sec) mysql> insert into demo44(employee_name, employee_salary) values('David', 4500); Query OK, 1 row affected (0.12 sec) mysql> insert into demo44(employee_name, employee_salary) values('Bob', 3500); ...
Read MoreCreating a Table in MySQL to set current date as default
Following is the syntax for creating a table and adding DEFAULT constraint to set default value −CREATE TABLE yourTableName ( yourColumnName1 dataType not null , yourColumnName2 dataType default anyValue, . . . N );;Let us create a table wherein we have set “employee_joining_date” with default constraint for current date as default −mysql> create table demo43 −> ( −> employee_id int not null auto_increment primary key, −> employee_name varchar(40) not null, −> employee_status varchar(60) default "NOT JOINED", −> employee_joining_date date default(CURRENT_DATE) −> ); Query OK, 0 rows affected (0.66 sec)Insert some records into the table with the help of insert command ...
Read MoreSELECT a row by subtracting dates in WHERE in MySQL?
For this, use TIMESTAMPDIFF(). Let us create a table −mysql> create table demo42 −> ( −> start_date datetime −> ); Query OK, 0 rows affected (0.77 sec)Insert some records into the table with the help of insert command −mysql> insert into demo42 values('2020-01-10 12:30:05'); Query OK, 1 row affected (0.11 sec) mysql> insert into demo42 values('2019-02-24 10:40:45'); Query OK, 1 row affected (0.11 sec) mysql> insert into demo42 values('2020-05-12 05:45:55'); Query OK, 1 row affected (0.17 sec) mysql> insert into demo42 values('2020-05-12 05:40:55'); Query OK, 1 row affected (0.15 sec) mysql> insert into demo42 values('2020-05-12 05:42:55'); ...
Read MoreMySQL regexp to display only records with strings or strings mixed with numbers. Ignore only the number records
For this, you can use REGEXP. Following is the syntax −select yourColumnName from yourTableName where yourColumnName REGEXP '[a−zA&minu;Z]';Let us create a table −mysql> create table demo41 −> ( −> name varchar(40) −> ); Query OK, 0 rows affected (0.64 sec)Insert some records into the table with the help of insert command −mysql> insert into demo41 values('John Smith34') −> ; Query OK, 1 row affected (0.13 sec) mysql> insert into demo41 values('John Smith'); Query OK, 1 row affected (0.11 sec) mysql> insert into demo41 values('9234John Smith'); Query OK, 1 row affected (0.14 sec) mysql> insert into demo41 values('john smith'); Query OK, ...
Read MoreHow 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 More