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 52 of 355
What are the difference ways to replace nulls values in MySQL using SELECT statement?
There are lots of options available to replace NULL values using select statement. You can use CASE statement or IFNULL() or COALESCE()Case 1 − Using IFNULL()The syntax of IFNULL() is as follows −SELECT IFNULL(yourColumnName, ’yourValue’) AS anyVariableName from yourTableName;Case 2 − Using COALESCE()The syntax of COALESCE() is as follows −SELECT COALESCE(yourColumnName, ’yourValue’) AS anyVariableName from yourTableName;Case 3 − Using CASE statementThe syntax of CASE statement.SELECT CASE WHEN yourColumnName IS NULL THEN ‘yourValue’ ELSE yourColumnName END AS anyVariableName FROM yourTableNameTo understand what we discussed above, let us create a table. The query to create a table is as follows −mysql> create ...
Read MoreUpdate an entire row in MySQL?
To update an entire row in MySQL, use UPDATE command. You need to know the primary key column. The syntax is as follows to update an entire row.UPDATE yourTableName SET yourColumnName1 = ’yourValue1’ ,yourColumnName2 = ’yourValue2’ , yourColumnName3 = ’yourValue3’ ,.......................N WHERE yourPrimaryKeyColumnName = yourValue;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table UpdateEntireRowDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(20), -> Age int, -> Marks int, -> PRIMARY KEY(Id) -> ); Query OK, ...
Read MoreHow can I change root username in MySQL?
To change the root username in MySQL, you need to use UPDATE and SET command. The syntax is as follows −UPDATE user set user = ’yourNewUserName’ WHERE user = ’root’;To understand the above syntax, let us switch the database to MySQL using USE command.The query is as follows to switch the database.mysql> use mysql; Database changedNow list all the users from MySQL.user table. The query is as follows −mysql> select user from MySQL.user;The following is the output −+------------------+ | user | +------------------+ | Manish | | User2 ...
Read MoreMySQL query to find a list of city names that do not start with vowels?
You can use DISTINCT with RLIKE operator to find a list of city names that do not start with vowels.The syntax is as follows −SELECT DISTINCT yourCityColumnName FROM yourTableName WHERE yourCityColumnName NOT RLIKE ‘ ^[AEIOUaeiou].*$’;To understand the above syntax, let us create a table. Here, we have a column for city names.The query to create a table is as follows −mysql> create table Employee_Information -> ( -> Id int NOT NULL AUTO_INCREMENT, -> EmployeeName varchar(20), -> CityName varchar(20), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.76 sec)Insert some records in the ...
Read MoreMySQL Query to remove all characters after last comma in string?
To remove all characters after the last comma in the string, you can use SUBSTRING_INDEX().If you do not know the location of the last comma, then you need to find the last comma dynamically using LENGTH().The syntax is as follows −UPDATE yourTableName set yourColumnName = SUBSTRING_INDEX(yourColumnName, ', ', LENGTH(yourColumnName) - LENGTH(REPLACE(yourColumnName, ', ', '')));To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table RemoveAllCharacters -> ( -> Id int NOT NULL AUTO_INCREMENT, -> FullInfo varchar(200), -> PRIMARY KEY(Id) -> ); Query OK, 0 ...
Read MoreReplace 0 with null in MySQL?
You can use NULLIF() from MySQL to replace 0 with NULL. The syntax is as follows −SELECT *, NULLIF(yourColumnName, 0) as anyVariableName from yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table Replace0WithNULLDemo -> ( -> Id int NOT NULL auto_increment, -> Name varchar(20), -> Marks int, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.53 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into Replace0WithNULLDemo(Name, Marks) values('John', ...
Read MoreFind out if a varchar contains a percent sign in MySQL?
To find out a varchar contains a percent sign in MySQL, you can use LIKE operator. The syntax is as follows −SELECT * FROM yourTableName WHERE yourColumnName like '%|%%' escape '|';To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table FindPercentInVarcharDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Words varchar(30), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.51 sec)Insert some records with % sign using insert command. The query is as follows −mysql> insert into FindPercentInVarcharDemo(Words) values('This is ...
Read MoreChange date format (in DB or output) to dd/mm/yyyy in PHP MySQL?
You can change the date format in PHP using date() fucntion. The syntax is as follows −date(d/m/Y, yourDateTimeVariable);In PHP, convert string to date using strtodate(). Here is the PHP code used to format the datetime −$LogintDate = strtotime('2019-01-12'); echo date('d/m/Y', $LogintDate);The snapshot of code is as follows −The following is the output −12/01/2019You can achieve in MySQL with the help of date_format() function. The syntax is as follows −SELECT DATE_FORMAT(yourColumnName, ’%d/%m/%Y’) as anyVariableName FROM yourTableName;To understand the above syntax, let us create a table −mysql> create table Date_FormatDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> ...
Read MoreMySQL LIMIT to select a single row
To select a single row in MySQL, you can use LIMIT. At first, let us create a table. The query to create a table is as follows −mysql> create table selectWithPrimaryKey -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(20), -> Age int, -> Marks int, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.78 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into selectWithPrimaryKey(Name, Age, Marks) values('Larry', 24, 98); Query OK, 1 row affected (0.15 sec) mysql> insert into ...
Read MoreConvert dd/mm/yyyy string to Unix timestamp in MySQL?
Convert dd/mm/yyyy string to Unix timestamp with the help of UNIX_TIMESTAMP(). The syntax is as follows −SELECT UNIX_TIMESTAMP(STR_TO_DATE(yourColumnName, '%d/%m/%Y')) as anyVariableName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table ConvertddmmyyyyInUnixTimeStamp -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Created_at varchar(30), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into ConvertddmmyyyyInUnixTimeStamp(Created_at) values('10/11/2012'); Query OK, 1 row affected (0.21 sec) mysql> ...
Read More