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
MySQL Articles
Page 57 of 355
How to skip first 10 results in MySQL?
To skip first 10 results, use “limit offset”. The syntax is as follows −select *from yourTableName limit 10 offset lastValue;Let us create a table to understand the above syntax. The following is the query to create a table −mysql> create table SkipFirstTenRecords −> ( −> StudentId int, −> StudentName varchar(200) −> ); Query OK, 0 rows affected (0.53 sec)Now you can insert some records in the table with the help of insert command. The query is as follows −mysql> insert into SkipFirstTenRecords values(100, 'John'); Query OK, 1 row affected (0.12 sec) ...
Read MoreMySQL Mass Update with CASE WHEN/ THEN/ ELSE?
The syntax for mass update with CASE WHEN/ THEN/ ELSE is as follows −UPDATE yourTableName set yourColumnName=case when yourColumnName=Value1 then anyUpdatedValue1 when yourColumnName=Value2 then anyUpdatedValue2 when yourColumnName=Value3 then anyUpdatedValue3 when yourColumnName=Value4 then anyUpdatedValue4 else yourColumnName end;To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table CaseUpdateDemo -> ( -> Id int, -> Name varchar(100) -> ); 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 CaseUpdateDemo values(1, 'John'); Query OK, 1 row affected ...
Read MoreMySQL always returning the bit values as blank? How to get the original values?
To get the original value, use the following syntax −Syntaxselect yourBitColumnName+0 from yourTableName;The above syntax cast the bit column to an integer. To understand the above concept, let us create a table and check how the returning value is blank. We will also see how to get the original value.The query to create a table.mysql> create table BitDemo -> ( -> hasValidId bit not null -> ); Query OK, 0 rows affected (1.21 sec)Insert some records in the table using insert command. The query to insert records is as follows −mysql> insert into BitDemo values(1); Query OK, 1 row affected ...
Read MoreFetching rows added in last hour with MySQL?
You can use date-sub() and now() function from MySQL to fetch the rows added in last hour.SyntaxThe syntax is as follows −select *from yourTableName where yourDateTimeColumnName create table LastHourRecords -> ( -> Id int, -> Name varchar(100), -> Login datetime -> ); Query OK, 0 rows affected (0.67 sec)Insert records in the form of datetime using insert command. The query to insert record is as follows −mysql> insert into LastHourRecords values(1, 'John', ' 2018-12-19 10:00:00'); Query OK, 1 row affected (0.17 sec) mysql> insert into LastHourRecords values(2, 'Carol', '2018-12-19 10:10:00'); Query OK, 1 row affected (0.15 sec) ...
Read MoreQuery in MySQL for string fields with a specific length?
To query for string fields with a specific length, use the char_length() or length() from MySQL.SyntaxThe syntax is as follows −Case 1 − Use of char_length()This can be used when we are taking length in a number of characters.The syntax −select *from yourTableName where char_length(yourColumnName)=anySpecificLengthValue;Case 2 − Use of length()This can be used when we are taking the length in bytes.The syntax −select *from yourTableName where length(yourColumnName)=anySpecificLengthValue;To understand the above concept, let us first create a table. The query to create a table is as follows −mysql> create table StringWithSpecificLength -> ( -> Id int, -> Name varchar(100), -> FavouriteLanguage ...
Read MoreGet the time difference and convert it to hours in MySQL?
You can achieve with the help of timestampdiff() method from MySQL. The syntax is as follows −SyntaxSELECT ABS(TIMESTAMPDIFF(HOUR, yourColumnName1, yourColumnName2)) 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 DifferenceInHours -> ( -> StartDateTime datetime, -> EndDateTime datetime -> ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into DifferenceInHours values('2018-12-20 10:00:00', '2018-12-19 12:00:00'); Query OK, 1 row affected (0.11 sec) mysql> insert into DifferenceInHours ...
Read MoreGet the index of last substring in a given string in MySQL?
To get the index of last substring in a given string, use the char_length() function. First, we need to calculate string length and subtract the last sub string length from the entire length. The difference in length is index of substring.SyntaxThe syntax is as follows −select CHAR_LENGTH(yourColumnName) - LOCATE('yourDelimiter ', REVERSE(yourColumnName))+1 as anyVariableName from yourTableName;To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table SubStringIndexDemo -> ( -> Words varchar(200) -> ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table ...
Read MoreHow to make a primary key start from 1000?
To make a primary key start from 1000, you need to alter your table and set to auto_increment with value 1000. The syntax is as follows −alter table yourTableName auto_increment=1000;To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table PrimaryKey1000Demo -> ( -> ProductId int auto_increment, -> PRIMARY KEY(ProductId) -> ); Query OK, 0 rows affected (0.56 sec)Now here is the query that will update the primary key to start from 1000 −mysql> alter table PrimaryKey1000Demo auto_increment=1000; Query OK, 0 rows affected (0.20 ...
Read MoreGet only the date in timestamp in MySQL?
In order to get the date from the timestamp, you can use DATE() function from MySQL.The syntax is as follows −SyntaxSELECT DATE(yourTimestampColumnName) as anyVariableName from yourTableName;To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table DateFromTimestamp -> ( -> ShippingDateTime timestamp -> ); Query OK, 0 rows affected (0.60 sec)Insert date and time for the column ShippingDateTime we created above.The query to insert record is as follows −mysql> insert into DateFromTimestamp values('2012-12-26 13:24:35'); Query OK, 1 row affected (0.14 sec) mysql> insert into DateFromTimestamp values('2013-11-26 14:36:40'); ...
Read MoreAdd a temporary column with a value in MySQL?
You can add a temporary column with value with the help of the following syntax −select yourColumnName1, yourColumnName2, .....N ,yourTemporaryColumnValue as yourTemporaryColumnName from yourTableName;To add a temporary column with a value, let us create a table. The following is the query −mysql> create table TemporaryColumnWithValueDemo −> ( −> StudentId int, −> StudentName varchar(100) −> ); Query OK, 0 rows affected (0.59 sec)Inserting some records in the table. The query to insert records are as follows −mysql> insert into TemporaryColumnWithValueDemo values(101, 'John'); Query OK, 1 row affected (0.13 sec) mysql> insert into ...
Read More