- 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 4378 Articles for MySQL
2K+ Views
You can select all records that are 10 minutes within current timestamp using the following syntax−SELECT *FROM yourTableName WHERE yourColumnName > = DATE_SUB(NOW(), INTERVAL 10 MINUTE);To understand the above syntax, let us create a table. The query to create a table is as follows−mysql> create table users -> ( -> Id int NOT NULL AUTO_INCREMENT, -> UserName varchar(20), -> UserLastseen datetime, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.91 sec)Insert some records in the table using insert command. The query is as follows−mysql> insert into users(UserName, UserLastseen) values('Larry', '2019-01-15 02−45−00'); Query ... Read More
2K+ Views
You can grab the current date with CURDATE() and the day before with MySQL using DATE_SUB() with INTERVAL 1 DAY. The syntax is as follows:SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY);The syntax is as follows to get curdate and the day before with date_sub().SELECT *FROM yourTableName WHERE yourColumnName = CURDATE() OR yourColumnName = DATE_SUB(CURDATE(), INTERVAL 1 DAY);To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table ProductDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> ProductName varchar(20), -> ProductOfferDate datetime, -> PRIMARY KEY(Id) -> ... Read More
1K+ Views
You can use CONCAT() method to concatenate values while IFNULL() method is used to handle NULL values. The syntax is as follows:SELECT CONCAT('anyStringValue:', IFNULL(yourColumnName, 'anyStringValue’)) 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 ConcatValues -> ( -> Id int NOT NULL AUTO_INCREMENT, -> FirstName varchar(20), -> MiddleName varchar(20), -> LastName varchar(20), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command. The query is as follows:mysql> insert ... Read More
914 Views
The limit of auto_increment integer depends on column data type. Displayed as follows:The data type TINYINT range is 127 The data type UNSIGNED TINYINT range is 255 The data type SMALLINT range is 32767 The data type UNSIGNED SMALLINT range is 65535 The data type MEDIUMINT range is 8388607 The data type UNSIGNED MEDIUMINT range is 16777215 The data type INT range is 2147483647 The data type UNSIGNED INT range is 4294967295 The data type BIGINT range is 9223372036854775807 The data type UNSIGNED BIGINT range is 18446744073709551615Let us take an example of TINYINT. If you will give beyond 127, then ... Read More
124 Views
The \G modifier gets the result in vertical order. If you use \g modifier, then it won’t affect the result. The \g works likesemi-colon.Let us first create a table. The query to create a table is as follows:mysql> create table DemoOfVertical -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(20), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (3.40 sec)Now you can insert some records in the table using insert command. The query is as follows:mysql> insert into DemoOfVertical(Name) values('Bob'); Query OK, 1 row affected (0.18 sec) mysql> insert into ... Read More
1K+ Views
You can get the last entry in a MySQL table using ORDER BY. The first approach is as follows:Case 1: Using DESC LIMITSELECT * FROM yourTableName ORDER BY yourColumnName DESC LIMIT 1;The second approach is as follows:Case 2: Using MAX()SET @anyVariableName = (SELECT MAX(yourColumnName) FROM yourTableName); SELECT *FROM yourtableName WHERE yourColumnName = @anyVariableName;Now to understand both the approaches, let us create a table. The query to create a table is as follows:mysql> create table lastEntryDemo -> ( -> Id int NOt NULL AUTO_INCREMENT, -> Name varchar(30), -> Age int, -> PRIMARY KEY(Id) -> ); ... Read More
701 Views
You can create a function to remove double or more spaces from a string. The syntax is as follows:DELIMITER // create function yourFunctionName(paramter1, ...N) returns datatype; begin //your statement. end; // DELIMITER ;Here’s how to create a function:mysql> delimiter // mysql> create function function_DeleteSpaces(value varchar(200)) returns varchar(200) -> begin -> set value = trim(value); -> while instr(value, ' ') > 0 do -> set value = replace(value, ' ', ' '); -> end while; -> return value; -> END; -> // Query OK, 0 rows affected (0.20 sec) mysql> delimiter ;Now you ... Read More
12K+ Views
You can update boolean value using UPDATE command. If you use the BOOLEAN data type, MySQL internally convert it into tinyint(1). It can takes true or false literal in which true indicates 1 to tinyint(1) and false indicates 0 to tinyint(1).The syntax is as follows −UPDATE yourTableName SET yourColumnName = yourValue WHERE yourCondition;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table UpdateBooleans -> ( -> Id int NOT NULL AUTO_INCREMENT, -> isSuccessful BOOLEAN, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows ... Read More
5K+ Views
To MySQL order string with numbers, the following is the syntax, wherein we have used ORDER BY, SUBSTR() and CAST() −SELECT *FROM yourTableName ORDER BY SUBSTR(yourColumnName FROM 1 FOR 2), CAST(SUBSTR(yourColumnName FROM 2) AS UNSIGNED);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table OrderByStringWithNumbers -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Words varchar(10), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.86 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert ... Read More
2K+ Views
You can use CASE statement in MySQL to map keys to values for an individual field in select query. The syntax is as follows −SELECT yourColumnName1, yourColumnName2, yourColumnName3, .........N ( CASE WHEN yourColumnName = 1 THEN 'ENABLED' ELSE 'DISABLED' END ) AS anyVariableName FROM yourTableName;You can use IF() function also for the same purpose. The syntax is as follows −SELECT yourColumnName1, yourColumnName2, yourColumnName3, .........N ,IF(yourColumnName, 'ENABLED', 'DISABLED') 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 MapKeys -> ( ... Read More