Tutorialspoint

MySQL select only duplicate records from database and display the count as well?

Smita Kapse
Answered on 16th Apr, 2019

To select only duplicate records from database and display the count, use HAVING along with aggregate function count(). Let us first create a table −mysql> create table duplicateRecords    -> (    -> ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> ClientName varchar(20)    -> ); Query OK, 0 ... Read More

How to calculate value from multiple columns in MySQL?

Anvi Jain
Answered on 16th Apr, 2019

To calculate a value from multiple columns, use GROUP BY. Following is the syntax −select yourColumnName1, sum(yourColumnName2*yourColumnName3) AS anyAliasName from yourTableName group by yourColumnName1;Let us first create a table −mysql> create table calculateValueDemo    -> (    -> Id int,    -> ProductPrice int,    -> ProductWeight int    -> ... Read More

Resolve MySQL ERROR 1064 (42000): You have an error in your syntax?

Smita Kapse
Answered on 16th Apr, 2019

This error occurs if let’s say you used var_char instead of varchar type. To remove this type of error, use, for example, varchar(100) instead of var_char(100).Let us now see how this error occurs −mysql> create table removeErrorDemo    -> (    -> StudentId int,    -> StudentName var_char(50)    -> ... Read More

How to select distinct value from one MySQL column only?

Nishtha Thakur
Answered on 16th Apr, 2019

To select distinct value from one column only, you can use aggregate function MAX() along with GROUP BY. Let us first create a table −mysql> create table distinctFromOneColumn    -> (    -> StudentId int,    -> StudentName varchar(100)    -> ); Query OK, 0 rows affected (0.77 sec)Following is ... Read More

MySQL search if more than one string contains special characters?

Anvi Jain
Answered on 16th Apr, 2019

To search if strings contain special characters, you can use REGEXP. Following is the syntax −select * from yourTableName where yourColumnName REGEXP '[^a-zA-Z0-9]';Let us first create a table −mysql> create table specialCharactersDemo    -> (    -> StudentId varchar(100)    -> ); Query OK, 0 rows affected (0.58 sec)Insert records ... Read More

Update multiple rows in a single column in MySQL?

Anvi Jain
Answered on 16th Apr, 2019

To update multiple rows in a single column, use CASE statement. Let us first create a table −mysql> create table updateMultipleRowsDemo    -> (    -> StudentId int,    -> StudentMathScore int    -> ); Query OK, 0 rows affected (0.63 sec)Following is the query to insert records in the ... Read More

MySQL query to display all the fields that contain a capital letter?

Nishtha Thakur
Answered on 16th Apr, 2019

To display all the fields that contain a capital letter, use the RLIKE that performs a pattern match of a string expression against a pattern.Let us first create a table −mysql> create table contains_capital_letterDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> Name varchar(100) ... Read More

How to sort more than one column at a time in MySQL?

Smita Kapse
Answered on 16th Apr, 2019

To sort more than one column at a time, you can use ORDER BY clause. Following is the syntax −select yourColumnName1, yourColumnName2, yourColumnName3 from yourTableName order by yourColumnName2, yourColumnName3;Let us first create a table −mysql> create table doubleSortDemo    -> (    -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, ... Read More

Convert UNIX timestamp into human readable format in MySQL?

Nishtha Thakur
Answered on 16th Apr, 2019

To convert UNIX timestamp into a human-readable format, use the FROM_UNIXTIME() method.Let us first create a table −mysql> create table timeConversionDemo    -> (    -> dateTimeConversion bigint    -> ); Query OK, 0 rows affected (0.45 sec)Following is the query to insert records in the table using insert command ... Read More

How to delete all rows except some in MySQL?

Smita Kapse
Answered on 16th Apr, 2019

You can use NOT IN operator for the rows you do not want to delete. Following is the syntax −delete from yourTableName where yourColumnName NOT IN(‘yourValue1’, ‘yourValue2’, ‘yourValue3’, .........N);Let us first create a table −mysql> create table deleteAllRowsWithCondition    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, ... Read More

How to find if a column is auto_increment in MySQL?

Nishtha Thakur
Answered on 16th Apr, 2019

To find if a column is auto_increment in MySQL, you can use the following syntax −select COLUMN_NAME from information_schema.columns where TABLE_SCHEMA='yourDatabaseName' and TABLE_NAME='yourTableName' and EXTRA like '%auto_increment%';Let us first create a table. Here, ClientId is set AUTO_INCREMENT −mysql> create table autoIncrementTableDemo    -> (    -> ClientId int NOT NULL ... Read More

MySQL query to find sum of fields with same column value?

Anvi Jain
Answered on 16th Apr, 2019

Use GROUP BY clause for this. Let us first create a table −mysql> create table sumOfFieldsDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> ClientSerialNumber varchar(100),    -> ClientCost int    -> ); Query OK, 0 rows affected (0.50 sec)Following is the query to ... Read More

Remove Trailing Zero in MySQL?

Anvi Jain
Answered on 16th Apr, 2019

Use trim() function to remove trailing zeroz in MySQL. Following is the syntax −select trim(yourColumnName)+0 As anyAliasName from yourTableName;Let us first create a table −mysql> create table removeTrailingZero    -> (    -> Number DECIMAL(10, 4)    -> ); Query OK, 0 rows affected (0.83 sec)Following is the query to ... Read More

Select rows having more than 2 decimal places in MySQL?

Smita Kapse
Answered on 16th Apr, 2019

To select rows with more than 2 decimal places, use SUBSTR() function from MySQL. Let us first create a table −mysql> create table selectRows2DecimalPlacesDemo    -> (    -> Amount varchar(100)    -> ); Query OK, 0 rows affected (0.73 sec)Following is the query to insert records in the table ... Read More

How to arrange data in s specific order in MySQL?

Nishtha Thakur
Answered on 16th Apr, 2019

Use ORDER BY IF() to arrange data in a specific order. Following is the syntax −select *from yourTableName ORDER BY IF(yourColumnName=yourValue1 OR yourColumnName=yourValue2 OR yourColumnName=yourValue3, yourColumnName, ~yourColumnName) ASC;Let us first create a table −mysql> create table arrangeDataInSpecificOrder    -> (    -> StudentId int,    -> StudentName varchar(20)    -> ... Read More

Previous 1 ... 4 5 6 7 8 9 10 ... 1284 Next
Advertisements
Loading...
Unanswered Questions View All

We use cookies to provide and improve our services. By using our site, you consent to our Cookies Policy.