Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
To encode base64, you can use two functionalities −TO_BASE64()FROM_BASE64()The syntax for base64 encode is as follows −SELECT TO_BASE64(anyValue) as AnyVariableName;The syntax for base64 decode is as follows −SELECT FROM_BASE64(encodeValue) as anyVariableNameTo understand the above concept, let us use the above syntax −Case 1 − EncodeTo encode the value, use the to_base64(). The query is as follows −mysql> select TO_BASE64('Password') as Base64EncodeValue;Output+-------------------+ | Base64EncodeValue | +-------------------+ | UGFzc3dvcmQ= | +-------------------+ 1 row in set (0.00 sec)Case 2 − DecodeTo decode the value, use the from_base64(). The query is as follows −mysql> select FROM_BASE64('UGFzc3dvcmQ=') as Base64DecodeValue;Output+-------------------+ | Base64DecodeValue | +-------------------+ | ... Read More
You can add unique key to existing table with the help of alter command. The syntax is as follows −ALTER TABLE yourTableName ADD CONSTRAINT yourConstraintName UNIQUE(yourColumnName1, yourColumnName2, ............N);To understand the above concept, let us create a table with some columns. The query to create a table −mysql> create table MovieCollectionDemo −> ( −> MovieId int, −> MovieDirectorName varchar(200), −> NumberOfSongs int unsigned −> ); Query OK, 0 rows affected (0.62 sec)Now you can check the table does not have any unique constraint. The query to check unique constraint is ... Read More
The MySQL CASE works like a switch statement. The syntax of CASE is as follows −Case 1 − Compare StatementCase when anyCompareStatement then value1 when anyCompareStatement then value2 . . N else anyValue end as anyVariableName;Case 2 − ConditionsThe second syntax can be used when you are selecting only one column. The syntax is as follows −case yourColumnName when condition1 then result1 when condition1 then result2 . . N else anyValue end;To understand the above concept, let us use select statement.Case 1The query is as follows −mysql> select -> case when 45 < 55 then '55 is greater than ... Read More
To generate a range of numbers in MySQL, you can use stored procedure. Firstly, we need to create a table. After that, we will create a stored procedure that generates a range of number from 10 to 1.The following is the query to create a table −mysql> create table GeneratingNumbersDemo −> ( −> Number int −> ); Query OK, 0 rows affected (0.55 sec)Now you can create a stored procedure that stores a range of numbers in the table. The following is the query to create a stored procedure −mysql> delimiter // mysql> CREATE PROCEDURE ... Read More
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 More
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 More
To fetch fields with multiple values, use LIKE with OR in MySQL −select *from yourTableName where yourColumnName like ‘%AnyStringValue’ or yourColumnName like ‘%AnyStringValue’ or yourColumnName like ‘%AnyStringValue’ ……...N;You can understand with the help of a table −mysql> create table LikeDemo −> ( −> Hobby varchar(200) −> ); Query OK, 0 rows affected (1.71 sec)Insert some records in the table with the help of insert command. The query to insert records in the table is as follows −mysql> insert into LikeDemo values('Reading Book'); Query OK, 1 row affected (0.13 sec) mysql> insert into LikeDemo values('Playing ... Read More
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 More
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 More
A ternary operator uses 3 operands and it can be used to replace the if else statement. This can be done to make the code simpler and more compact.The syntax of the ternary operator is given as follows −Expression ? Statement 1 : Statement 2In the above syntax, the expression is a conditional expression that results in true or false. If the value of the expression is true, then statement 1 is executed otherwise statement 2 is executed.A program that demonstrates the ternary operator in Java is given as follows.Example Live Demopublic class Example { public static void main(String[] args) ... Read More