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 326 of 341
Set MySQL DECIMAL with accuracy of 10 digits after the comma?
As you know the DECIMAL() method takes two parameter. The first parameter tells about the total number of digits and second parameter tells about number of digits after decimal point. Therefore, if you use DECIMAL(10, 10) that means you can use only 10 fractional digit.For Example: Store 0.9999999999 with DECIMAL(20, 10).To understand what we discussed above, let us create a table. The query to create a table is as follows:mysql> create table Decimal_Demo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Price DECIMAL(20, 10), -> PRIMARY KEY(Id) ...
Read MoreHow to find nth highest value of a MySQL column?
To find the nth highest value of a column, you need to use ORDER BY DESC with LIMIT clause. If you want the second highest value of a column, use the below syntax:SELECT *FROM yourTableName ORDER BY DESC yourColumnName LIMIT 1, 1;If you want the fourth highest value of a column, use the below syntax:SELECT *FROM yourTableName ORDER BY DESC yourColumnName LIMIT 3, 1;If you want the first highest value of a column, use the below syntax:SELECT *FROM yourTableName ORDER BY DESC yourColumnName LIMIT 1;As discussed in the above syntax, you need to change only in LIMIT clause. To understand ...
Read MoreHow to remove special characters from a database field in MySQL?
You can remove special characters from a database field using REPLACE() function. The special characters are double quotes (“ “), Number sign (#), dollar sign($), percent (%) etc.The syntax is as follows to remove special characters from a database field.UPDATE yourTableName SET yourColumnName=REPLACE(yourColumnName, ’yourSpecialCharacters’, ’’);To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table RemoveSpecialCharacterDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(20), -> PRIMARY Key(Id) -> ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using ...
Read MoreMySQL concat() to create column names to be used in a query?
To create column names to be used in a query, you need to use a user-defined variable with the set command. The syntax is as follows −SET @anyVariableName := ( SELECT CONCAT ( "SELECT", GROUP_CONCAT(CONCAT(" 1 as ", COLUMN_NAME) SEPARATOR ', '), " FROM DUAL") FROM INFORMATION_SCHEMA_COLUMNS WHERE TABLE_NAME= ‘yourTableName’ );Now prepare the statement using the PREPARE command. The syntax is as follows −PREPARE anyVariableName from @anyVariableName;Execute statement using EXECUTE command. The syntax is as follows −EXECUTE anyVariableName;Deallocate the prepared statement using DEALLOCATE command. The syntax is as follows −DEALLOCATE PREPARE anyVariableName; ...
Read MoreImprove MySQL Search Performance with wildcards (%%)?
No, MySQL won’t improve search performance whenever you have leading wildcards because MySQL will be unable to use the index. If you change to ‘anyLetter%’ then it will be able to use indexThe below syntax is better to use with trailing wildcards. The syntax is as follows −SELECT *FROM yourTableName WHERE yoorColumnName LIKE ‘anyLetter%’;The query to create a table is as follows −mysql> create table TrailingWildCardDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name Varchar(20), -> PRIMARY KEY(Id) -> ); Query OK, 0 ...
Read MoreFind rows where column value ends with a specific substring in MySQL?
To find rows and update with new value where column value ends with specific substring you need to use LIKE operator.The syntax is as follows:UPDATE yourTableName SET yourColumnName=’yourValue’ WHERE yourColumnName LIKE ‘%.yourString’;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table RowEndsWithSpecificString -> ( -> Id int NOT NULL AUTO_INCREMENT, -> FileName varchar(30), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (1.50 sec)Now you can insert some records in the table using ...
Read MoreSearch for text between delimiters in MySQL?
You need to use LOCATE() along with SUBSTR(). The below syntax will find the word after delimiter. Here, delimiter is colon(:), you can use another i.e. it is up to you. The syntax is as follows −SELECT SUBSTR(yourColumnName, LOCATE(':', yourColumnName)+1, (CHAR_LENGTH(yourColumnName) - LOCATE(':', REVERSE(yourColumnName)) - LOCATE(':', yourColumnName))) AS anyAliasName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table SearchTextBetweenDelimitersDemo -> ( -> ...
Read MoreSorting a VARCHAR column as FLOAT using the CAST operator isn't working in MySQL ?
If your cast does not work, then you can use yourColumnName*1 with ORDER BY clause.Using yourColumnName*1. The syntax is as follows:SELECT yourColumnName1, yourColumnName2, ...N FROM yourTableName ORDER BY yourColumnName*1 DESC;You can also use CAST() operator. The syntax is as follows:SELECT yourColumnName1, yourColumnName2, ...N FROM yourTableName ORDER BY CAST(yourColumnName as DECIMAL(8, 2)) DESC;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table VarcharColumnAsFloatDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Amount varchar(20), -> PRIMARY KEY(Id) ...
Read MoreEmpty string in not-null column in MySQL?
In PHP, the empty string equals to a NULL value, but in MySQL, the case is the different i.e. empty string is not equal to NULL value. To understand the above syntax, let us create a column with NOT NULL constraint while you can insert an empty string.Let us create a table. The query to create a table is as follows −mysql> create table EmptyStringNotNullDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(10) not null, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.83 sec)Now you can insert some records ...
Read MoreResolve an error whenever multiple rows are returned in MySQL Benchmark?
You will get an error whenever you return multiple rows in the benchmark. Return a scalar value or single row instead of multiple rows. The syntax is as follows −SELECT yourColumnName FROM yourTableName WHERE yourCondition.To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table UserDemo -> ( -> UserId int, -> UserName varchar(20), -> RegisteredCourse varchar(10) -> ); Query OK, 0 rows affected (0.67 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into UserDemo values(1, ...
Read More