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 187 of 341
MySQL query to fetch records with arrangement in the form of numbers and letter like 99S, 50K, etc.?
The easiest way to achieve this is by using REGEXP. Let us first create a table −mysql> create table DemoTable ( Id varchar(50) ); Query OK, 0 rows affected (0.77 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John123'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable values('123Chris'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('99Bob'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('19David'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('99S'); Query OK, 1 row affected (0.18 sec) ...
Read MoreReplace a specific duplicate record with a new value in MySQL
Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Name varchar(50) ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Name) values('Chris'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(Name) values('Robert'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(Name) values('Chris'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable(Name) values('David'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable(Name) values('Mike'); Query OK, 1 row affected (0.13 sec)Display all records ...
Read MoreCan we use the result of a SUM() function in MySQL WHERE clause
We can use the HAVING clause rather than the WHERE in MySQL. Let us first create a table −mysql> create table DemoTable ( Name varchar(50), Price int ); Query OK, 0 rows affected (0.79 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Chris', 30); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('David', 40); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('Chris', 10); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('Mike', 44); Query OK, 1 row affected (0.14 sec) mysql> insert into ...
Read MoreMySQL date comparison to fetch dates between a given range?
Let us first create a table −mysql> create table DemoTable ( AdmissionDate date ); Query OK, 0 rows affected (0.73 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-08-31'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('2019-09-01'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('2019-05-10'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values('2019-06-12'); Query OK, 1 row affected (0.25 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+---------------+ | AdmissionDate | +---------------+ ...
Read MoreConditional WHERE clause in MySQL stored procedure to set a custom value for NULL values
To set a custom value for NULL values, use the UPDATE command along with IS NULL property in a stored procedure. Let us first create a table −mysql> create table DemoTable ( Id int, FirstName varchar(50) ); Query OK, 0 rows affected (0.67 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(100, 'Chris'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values(101, NULL); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values(102, 'Mike'); Query OK, 1 row affected (0.38 sec) mysql> insert into DemoTable values(103, NULL); Query ...
Read MoreAutoincrement in MySQL begins from 1? How can we begin it from another number?
The autoincrement in MySQL gives a unique number every time. By default, it starts at 1. If you want to start from another number, then you need to change the auto-increment value with the help of ALTER command or you can give value at the time of table creation.Let us first create a table −mysql> create table DemoTable ( UniqueNumber int NOT NULL AUTO_INCREMENT, PRIMARY KEY(UniqueNumber) ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(); Query OK, 1 row affected (0.30 sec) mysql> insert into DemoTable ...
Read MoreHow to use three conditions in a single MySQL query with id, name and age of students to fetch record of a student?
Let us first create a table −mysql> create table DemoTable ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentName varchar(50), StudentAge int ); Query OK, 0 rows affected (0.72 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(StudentName, StudentAge) values('Chris', 21); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(StudentName, StudentAge) values('David', 23); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(StudentName, StudentAge) values('Bob', 22); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(StudentName, StudentAge) values('Carol', 21); Query OK, 1 row affected (0.30 sec)Display all records from ...
Read MoreGet the count of unique phone numbers from a column with phone numbers declared as BIGINT type in MySQL
For this, you can use COUNT() along with DISTINCT. The COUNT() method is to count the records. However, the DISTINCT returns distinct records, whereas COUNT() method counts those unique records. Let us first create a table −mysql> create table DemoTable ( PhoneNumber bigint ); Query OK, 0 rows affected (1.29 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(8567789898); Query OK, 1 row affected (0.94 sec) mysql> insert into DemoTable values(8567789898); Query OK, 1 row affected (0.34 sec) mysql> insert into DemoTable values(9876564534); Query OK, 1 row affected (0.43 sec) mysql> insert into DemoTable ...
Read MoreMySQL CREATE statement with KEY keyword
As stated in the official docs −KEY is normally a synonym for INDEX. The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition. This was implemented for compatibility with other database systems.Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Name varchar(50), Age int ); Query OK, 0 rows affected (0.69 sec)Following is the query for INDEX, which is a synonym to KEY −mysql> create index Name_Age_Index on DemoTable(Name, Age); Query OK, 0 rows affected (0.65 sec) Records: 0 Duplicates: ...
Read MoreMySQL query to select a specific string with special characters
Let us first create a table −mysql> create table DemoTable ( Title text ); Query OK, 0 rows affected (0.66 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('MySQL'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('MongoDB's'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('MySQL's'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values('Java's'); Query OK, 1 row affected (0.24 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+-----------+ | Title ...
Read More