Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
To insert the date with date formats, use the str_to_date() function with date in single quotes. The following is the syntax −insert into yourTableName values(Value1, value2, ......ValueN, str_to_date(‘anyDate’, ’%Y-%m-%d’));Here are the Date Formats in MySQL −FormatDescription%aAbbreviated weekday name (Sun to Sat)%bAbbreviated month name (Jan to Dec)%cNumeric month name (0 to 12)%DDay of the month as a numeric value, followed by suffix (1st, 2nd, 3rd, ...)%dDay of the month as a numeric value (01 to 31)%eDay of the month as a numeric value (0 to 31)%fMicroseconds (000000 to 999999)%HHour (00 to 23)%hHour (00 to 12)%IHour (00 to 12)%iMinutes (00 to 59)%jDay ... Read More
MySQL DAYOFWEEK() function returns 1 for Sunday, 2 for Monday and so on for day of week. Let us see an example by first creating a table −mysql> create table DayOfWeekDemo −> ( −> Issuedate datetime −> ); Query OK, 0 rows affected (0.52 sec)Inserting date in the table with the help of insert command. The query is as follows −mysql> insert into DayOfWeekDemo values(date_add(curdate(), interval 5 day)); Query OK, 1 row affected (0.52 sec) mysql> insert into DayOfWeekDemo values(date_add(curdate(), interval 6 day)); Query OK, 1 row affected (0.13 sec) mysql> insert into DayOfWeekDemo values(date_add(curdate(), interval 7 ... Read More
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