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
MySQLi Articles
Page 169 of 341
How to mask user email addresses with a different domain in MySQL?
Let us first create a table −mysql> create table DemoTable1345 -> ( -> UserEmailAddress text -> ); Query OK, 0 rows affected (0.42 sec)Insert some records in the table using insert command. We have inserted email address here −mysql> insert into DemoTable1345 values('Carol123@gmail.com'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable1345 values('987Sam@gmail.com'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable1345 values('David_Miller@gmail.com'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1345 values('Bob@gmail.com'); Query OK, 1 row affected (0.14 sec)Display all records from the table using select statement −mysql> select * ...
Read MoreMySQL query to select rows where column value is only 0, group by another column?
For this, use group by. Let us first create a table −mysql> create table DemoTable1344 -> ( -> `SequenceId` int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> ClientId int, -> isMarried tinyint(1) -> ); Query OK, 0 rows affected (0.49 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1344(ClientId, isMarried) values(4567, 0); Query OK, 1 row affected (0.45 sec) mysql> insert into DemoTable1344(ClientId, isMarried) values(9876, 0); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1344(ClientId, isMarried) values(5432, 1); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1344(ClientId, isMarried) ...
Read MoreInsert all the values in a table with a single MySQL query separating records by comma
Let us first create a table −mysql> create table if not exists DemoTable1343 -> ( -> `_ClientId` int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> ClientName varchar(40), -> ClientProjectDeadline date -> )ENGINE=MyISAM, AUTO_INCREMENT=1000; Query OK, 0 rows affected (0.21 sec)Insert some records in the table using insert command with a single query −mysql> insert into DemoTable1343(ClientName, ClientProjectDeadline) values('Chris', '2019-09-24'), ('Bob', '2015-12-09'), -> ('Mike', '2017-01-20'), ('Carol', '2018-03-31'); Query OK, 4 rows affected (0.10 sec) Records: 4 Duplicates: 0 Warnings: 0Display all records from the table using select statement −mysql> select * from DemoTable1343;This will produce the ...
Read MoreHow to add a column from a select query but the value from the new column will be the row count of the MySQL select query?
For this, you can use MySQL row_number(). Let us first create a table −mysql> create table DemoTable1342 -> ( -> Score int -> ); Query OK, 0 rows affected (0.68 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1342 values(80); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable1342 values(98); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable1342 values(78); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1342 values(89); Query OK, 1 row affected (0.07 sec)Display all records from the table using select statement −mysql> select ...
Read MoreFetch the substring after last dot in MySQL
To fetch the substring after last dot, use substring_index(). Let us first create a table −mysql> create table DemoTable1341 -> ( -> Value varchar(60) -> ); Query OK, 0 rows affected (0.75 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1341 values('John.123.@gmail.com' ); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable1341 values('Carol.Taylor.gmail') ; Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable1341 values('C.MyFolder.Location') ; Query OK, 1 row affected (0.10 sec)Display all records from the table using select statement −mysql> select * from DemoTable1341;This will produce the following ...
Read MoreMySQL random rows sorted by a specific column name?
Let us first create a table −mysql> create table DemoTable1339 -> ( -> Name varchar(30), -> Score int -> ); Query OK, 0 rows affected (0.76 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1339 values('Chris', 56); Query OK, 1 row affected (0.36 sec) mysql> insert into DemoTable1339 values('Bob', 46); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1339 values('Adam', 78); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable1339 values('John', 90); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1339 values('Carol', 98); Query OK, 1 ...
Read MoreCount values based on conditions and display the result in different columns with MySQL?
Let us first create a table −mysql> create table DemoTable1485 -> ( -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentName varchar(20), -> StudentSubject varchar(20) -> ); Query OK, 0 rows affected (0.72 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1485(StudentName, StudentSubject) values('Chris', 'MySQL'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1485(StudentName, StudentSubject) values('Robert', 'MongoDB'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable1485(StudentName, StudentSubject) values('Robert', 'MongoDB'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable1485(StudentName, StudentSubject) ...
Read MoreUsing MySQL where clause and ordering by avg() to find the average of duplicate individual elements
For this, use having clause instead of where. Let us first create a table −mysql> create table DemoTable1338 -> ( -> Name varchar(10), -> Score int -> ); Query OK, 0 rows affected (1.54 sec)Insert some records in the table using insert command. Here, we have inserted duplicate names with scores −mysql> insert into DemoTable1338 values('Chris', 8); Query OK, 1 row affected (0.80 sec) mysql> insert into DemoTable1338 values('Bob', 4); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1338 values('Bob', 9); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable1338 values('Chris', 6); ...
Read MoreFetch maximum value from a column with values as string numbers like Value440, Value345, etc. in SQL
For this, you can use MAX() along with substring(). Let us first create a table −mysql> create table DemoTable1337 -> ( -> Value varchar(50) -> ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1337 values('Value400'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1337 values('Value345'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1337 values('Value567'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1337 values('Value489'); Query OK, 1 row affected (0.22 sec)Display all records from the table using select statement ...
Read MoreSelect different fields in MySQL even if a field is set to null?
For this, you can use COALESCE(). Let us first create a table −mysql> create table DemoTable1336 -> ( -> FirstName varchar(20) -> , -> SecondName varchar(20) -> ); Query OK, 0 rows affected (0.64 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1336 values('John', NULL); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable1336 values(NULL, 'Chris'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1336 values('David', 'Mike'); Query OK, 1 row affected (0.18 sec)Display all records from the table using select statement −mysql> select * from DemoTable1336;This ...
Read More