- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Found 6702 Articles for Database
![Chandu yadav](https://www.tutorialspoint.com/assets/profiles/22330/profile/60_142811-1519038104.jpg)
8K+ Views
To delete last record (on condition) from a table, you need to use ORDER BY DESC with LIMIT 1. The syntax is as follows:DELETE FROM yourTableName WHERE yourColumnName1=yourValue ORDER BY yourColumnName2 DESC LIMIT 1;The above syntax will delete last record (on condition) from a table. It sorts the column in descending order and choose the first element to delete.To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table UserLoginTable -> ( -> Id int NOT NULL AUTO_INCREMENT, -> UserId int, -> UserLoginDateTime datetime, -> PRIMARY ... Read More
![Arjun Thakur](https://www.tutorialspoint.com/assets/profiles/13574/profile/60_74441-1560315595.jpg)
787 Views
You need to use regular expression with ORDER BY clause. The syntax is as follows:SELECT *FROM yourTableName ORDER BY IF(yourColumnName RLIKE '^[a-z]', 1, 2), yourColumnName;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table AlphabetFirstThenNumberDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(20), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.95 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into AlphabetFirstThenNumberDemo(Name) values('John'); Query OK, 1 row affected (0.37 sec) mysql> insert ... Read More
![Ankith Reddy](https://www.tutorialspoint.com/assets/profiles/22328/profile/60_142162-1519038074.jpg)
830 Views
To select multiple sum columns with MySQL query and display them in separate columns, you need to use CASE statement. The syntax is as follows:SELECT SUM( CASE WHEN yourColumnName1=’yourValue1’ THEN yourColumnName2 END ) AS yourSeparateColumnName1, SUM( CASE WHEN yourColumnName1=’yourValue2’ THEN yourColumnName2 END ) AS yourSeparateColumnName2, SUM( CASE WHEN yourColumnName1=’yourValue3’ THEN yourColumnName2 END ) AS yourSeparateColumnName3, . . . N FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table selectMultipleSumDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> PlayerName varchar(20), -> PlayerScore int, ... Read More
![George John](https://www.tutorialspoint.com/assets/profiles/13511/profile/60_58410-1512537824.png)
224 Views
To select max of mixed string/int column, you need to use substring() function. The syntax is as follows:SELECT MAX(CAST(SUBSTRING(yourColumnName, 4, length(yourColumnName)-3) AS UNSIGNED)) 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 StringIntMixHighestDemo -> ( -> InvoiceId int NOT NULL AUTO_INCREMENT, -> InvoiceNumber varchar(20), -> PRIMARY KEY(InvoiceId) -> ); Query OK, 0 rows affected (0.65 sec)Now you can insert some records in the table using insert command. The query is as follows:mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV129'); Query OK, 1 row ... Read More
![Chandu yadav](https://www.tutorialspoint.com/assets/profiles/22330/profile/60_142811-1519038104.jpg)
517 Views
You need to use backticks around table name as well as database name. The syntax is as follows:UPDATE `yourDatabaseName`.`yourTableName` SET yourColumnName1=yourColumnName1+1 WHERE yourColumnName2=’yourValue’;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> use test; Database changed mysql> create table Add1Demo -> ( -> Id varchar(10), -> Value int -> ); Query OK, 0 rows affected (1.19 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert into Add1Demo values('1', 780); Query OK, 1 row affected (0.17 sec) mysql> insert into Add1Demo values('2', ... Read More
![Samual Sam](https://www.tutorialspoint.com/assets/profiles/13514/profile/60_83486-1512649303.jpg)
2K+ Views
You need to use CONVERT() function along with binary keyword. The syntax is as follows −SELECT CONVERT(binary CONVERT(yourColumnName using latin1) USING UTF8) 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 UtfDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(15), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.76 sec)Insert some records in the table using INSERT command. The query is as follows −mysql> insert into UtfDemo(Name) values('Obama’s'); Query OK, 1 row affected (0.28 ... Read More
![karthikeya Boyini](https://www.tutorialspoint.com/assets/profiles/13518/profile/60_31598-1537784993.jpg)
893 Views
You can achieve it with the help of GROUP_CONCAT() function. The syntax is as follows −SELECT yourColumnName1, yourColumnName2, yourColumnName3, ..N, GROUP_CONCAT(yourColumnName4) as anyAliasName FROM yourTableName group by yourColumnName3, yourColumnName1, yourColumnName2;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table CommaDelimitedList -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(10), -> GroupId int, -> CompanyName varchar(15), -> RefId int, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.68 sec)Insert some records in the table using INSERT command. ... Read More
![karthikeya Boyini](https://www.tutorialspoint.com/assets/profiles/13518/profile/60_31598-1537784993.jpg)
2K+ Views
The binary keyword can be used after WHERE clause to compare a value with exact case sensitive match.The following is an example −Case 1 − Case insensitive matchThe query is as follows −mysql> select 'joHN'='JOHN' as Result;The following is the output −+--------+ | Result | +--------+ | 1 | +--------+ 1 row in set (0.00 sec)In the above sample output, the result is true while we know joHN and JOHN are two different words. This is not a case sensitive match.Case 2 − If you want case sensitive match, use the binary keyword.The query is ... Read More
![Samual Sam](https://www.tutorialspoint.com/assets/profiles/13514/profile/60_83486-1512649303.jpg)
259 Views
You can get year out of timestamp using YEAR() function. The syntax is as follows −SELECT yourColumnName FROM yourTableName WHERE YEAR(yourTimestampColumnName)='yourYearValue’';To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table getYearOut -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(10), -> yourTimestamp timestamp default current_timestamp, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (1.56 sec)Insert some records in the table using INSERT command−mysql> insert into getYearOut(Name, yourTimestamp) values('John', now()); Query OK, 1 row affected (0.26 sec) ... Read More
![karthikeya Boyini](https://www.tutorialspoint.com/assets/profiles/13518/profile/60_31598-1537784993.jpg)
99 Views
The CONV() function can be used to convert one base number system to another base system.For Example, The 16 is one base system and 10 is another base system. The 16 base system is hexadecimal and 10 is a decimal.The syntax is as follows −SELECT CAST(CONV('yourColumnName', 16, 10) AS UNSIGNED INTEGER) 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 castTypeToBigIntDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Value varchar(100), -> PRIMARY KEY(Id) -> ); Query OK, ... Read More