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 315 of 341
Count number of times value appears in particular column in MySQL?
You can use aggregate function count() with group by. The syntax is as follows.select yourColumnName, count(*) as anyVariableName from yourtableName group by yourColumnName;To understand the above syntax, let us create a table. The query to create a table is as follows.mysql> create table CountSameValue -> ( -> Id int, -> Name varchar(100), -> Marks int -> ); Query OK, 0 rows affected (0.70 sec)Insert records in the table using insert command. The query is as follows.mysql> insert into CountSameValue values(1, 'Sam', 67); Query OK, 1 row affected (0.17 sec) mysql> insert into CountSameValue values(2, 'Mike', 87); Query OK, 1 ...
Read MoreHow to select domain name from email address in MySQL?
To select domain name from email address, you can use in-built SUBSTRING_INDEX() function from MySQL.To understand the concept, let us create a table. The following is the query to create a table.mysql> create table selectDomainNameOnly −> ( −> UserEmailAddress varchar(200) −> ); Query OK, 0 rows affected (0.52 sec)Insert records in the table using insert command. The record will have email-ids from which we need to fetch the domain name. The query is as follows −mysql> insert into selectDomainNameOnly values('John123@yahoo.com'); Query OK, 1 row affected (0.10 sec) mysql> insert into selectDomainNameOnly ...
Read MoreHow to bulk update MySQL data with a single query?
You can bulk update MySQL data with one query using CASE command. The syntax is as follows −update yourTableName set yourUpdateColumnName = ( Case yourConditionColumnName WHEN Value1 THEN ‘’UpdatedValue’ WHEN Value2 THEN ‘UpdatedValue’ . . N END) where yourConditionColumnName IN(Value1, Value2, .....N);To understand the above concept, let us create a table. The query to create a table is as follows −mysql> create table UpdateAllDemo −> ( −> BookId int, −> BookName varchar(200) −> ); Query OK, 0 rows affected (1.18 sec)Insert some records in the table using insert ...
Read MoreHow to order records by a column in MySQL and place empty records at the end?
To get order by column and place empty records at the end, use ORDER By and “is null” from MySQL. The syntax is as follows −select *from yourTableName order by if(yourColumName = ’ ’ or yourColumName is null, 1, 0), yourColumnName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table emptyCellsAtEnd −> ( −> ProductId varchar(100) −> ); Query OK, 0 rows affected (0.65 sec)Insert some records in the table using insert command. Some of these records are empty. The query is as follows −mysql> ...
Read MoreGetting last 5 character of a string with MySQL query?
To get the first n characters of string with MySQL, use LEFT(). To get the last n char of string, the RIGHT() method is used in MySQL.The syntax for RIGHT() method is as follows −SELECT RIGHT(yourColumnName, valueOfN) as anyVariableName from yourTableName;To understand the above concept, let us create a table. The query to create a table is as follows −mysql> create table gettingLast5Characters −> ( −> BookName varchar(100) −> ); Query OK, 0 rows affected (0.73 sec)Now you can insert records in the table using insert command. The query is as follows −mysql> insert into gettingLast5Characters values('Introduction ...
Read MoreWhat should I do? Select int as currency or convert int to currency format in MySql?
To convert int to current format, use CONCAT() with FORMAT() function from MySQL.The syntax is as follows −SELECT CONCAT(‘CurrencySymbol’, FORMAT(yourColumnName, valueAfterDecimal)) as AnyVariableName from yourTableName;To understand the above syntax, let us create a table. The query to create a table −mysql> create table AddingCurrencySymbolDemo −> ( −> Amount int −> ); Query OK, 0 rows affected (1.50 sec)Insert records in the table using insert command. The query is as follows −mysql> insert into AddingCurrencySymbolDemo values(250); Query OK, 1 row affected (0.22 sec) mysql> insert into AddingCurrencySymbolDemo values(500); Query OK, 1 ...
Read MoreCan we update MySQL with if condition?
You can update MySQL with IF condition as well as CASE statement. For this purpose, let us first create a table. The query to create a table −mysql> create table UpdateWithIfCondition −> ( −> BookId int, −> BookName varchar(200) −> ); Query OK, 0 rows affected (0.60 sec)Now you can insert records in the table using insert command. The query is as follows −mysql> insert into UpdateWithIfCondition values(1000, 'C in Depth'); Query OK, 1 row affected (0.12 sec) mysql> insert into UpdateWithIfCondition values(1001, 'Introduction to Java'); Query OK, 1 row affected (0.14 sec)Display all records ...
Read MoreHow to display the value of a variable on command line in MySQL?
To display the value of a variable, you can use select statement. The syntax is follows −SELECT @yourVariableName;Let us first create a variable. This can be done using SET command. The following is the syntax to create a variable −SET @yourVariableName = yourValue;Let us check the above syntax to create a variable and display the value of created variable.The following is the query to create a variable −mysql> set @FirstName = 'Bob'; Query OK, 0 rows affected (0.04 sec)Now you can display the value of a variable using select statement. The query is as follows −mysql> select @FirstName;The following is ...
Read MoreMySQL query to extract first word from a field?
To extract first word from a field, use in-built SUBSTRING_INDEX() function. The syntax is as follows −SELECT SUBSTRING_INDEX(yourColumnName, ’ ‘, 1) as anyVariableName from yourTableName;In the above query, if you use -1 in place of 1 then you will get the last word. To understand the above concept, let us create a table. The following is the query to create a table.mysql> create table FirstWordDemo −> ( −> AllWords longtext −> ); Query OK, 0 rows affected (0.83 sec)Now insert some words in the table using insert command. The query is ...
Read MoreHow to get the Average on MySQL time column?
To get average on time column, use the below syntax. It will give the average in time format −SELECT SEC_TO_TIME(AVG(TIME_TO_SEC(yourColumnName))) as anyVariableName from yourTableName;To understand the above concept, let us create a table. The following is the query −mysql> create table AverageOnTime −> ( −> PunchInTime time −> ); Query OK, 0 rows affected (0.61 sec)Insert time values in the table using insert command. The query to insert records is as follows −mysql> insert into AverageOnTime values('00:00:40'); Query OK, 1 row affected (0.20 sec) mysql> insert into AverageOnTime values('00:02:50'); Query OK, 1 row affected (0.15 sec) ...
Read More