- Open Command Console.
2. Command to Import SQL Database into Selected Database.
Parameter :-
mysqldump – u<User Name> -p<Password> -d<DatabaseName> < <ImportFileName>
2. Command to Import SQL Database into Selected Database.
Parameter :-
mysqldump – u<User Name> -p<Password> -d<DatabaseName> < <ImportFileName>
MySQL Date Format :-
The date_format() function formats a date as specified format mask.
Format Description
%a Abbreviated weekday name (Sun to Sat)
%b Abbreviated month name (Jan to Dec)
%c Numeric month name (0 to 12)
%D Day of the month as a numeric value, followed by suffix (1st, 2nd, 3rd,
%d Day of the month as a numeric value (01 to 31)
%e Day of the month as a numeric value (0 to 31)
%f Microseconds (000000 to 999999)
%H Hour (00 to 23)
%h Hour (00 to 12)
%I Hour (00 to 12)
%i Minutes (00 to 59)
%j Day of the year (001 to 366)
%k Hour (00 to 23)
%l Hour (1 to 12)
%M Month name in full (January to December)
%m Month name as a numeric value (00 to 12)
%p AM or PM
%r Time in 12 hour AM or PM format (hh:mm:ss AM/PM)
%S Seconds (00 to 59)
%s Seconds (00 to 59)
%T Time in 24 hour format (hh:mm:ss)
%U Week where Sunday is the first day of the week (00 to 53)
%u Week where Monday is the first day of the week (00 to 53)
%V Week where Sunday is the first day of the week (01 to 53). Used with %X
%v Week where Monday is the first day of the week (01 to 53). Used with %X
%W Weekday name in full (Sunday to Saturday)
%w Day of the week where Sunday=0 and Saturday=6
%Y Year as a numeric, 4-digit value
%y Year as a numeric, 2-digit value
Movers in Dubai | Dubai Classifieds
Example :-
SELECT DATE_FORMAT(“2017-06-15”, “%d-%m-%Y”);
Results
15-06-2017
How to give GRANT access to existing MYSQL Users ?
1) SELECT access
GRANT select on <> to <>@%
DBName > Database Name
User Name > MYSQL User Access Name
Example :-
GRANT select on ‘MASTER.*’ to ‘GuestUser@%’
2) UPDATE, INSERT, DELETE Access
GRANT INSERT, UPDATE, DELETE on <> to <>@<>
How to get Yesterday date in MYSQL Query ?
The below function return the yesterday date in MySQL
Movers in Dubai
SELECT SUBDATE(CURDATE(),1)
How to Convert the DATE TIME column to local time zone ?
In mysql the function CONVERT_TZ (, <>,<), converting a time zone from default to new / local zone.
Movers in Dubai
| Check Responsive site
Example :
Change to IST from Default.
convert_tz(order_date, ‘+00:00’ , ‘+05:30’)
How to convert MYSQL Datas to JSON.
json_encode ($datas)
json_decode($datas)
This below example shows you how to use the LOAD DATA INFILE statement to import CSV file into MySQL table.
The LOAD DATA INFILE statement allows you to read data from a text file and import the file’s data into a database table very fast.
Before importing the file, you need to complete the following steps :
1) Data Import table should be created
2) CSV Columns Should be match with the Database Table.
3) Access required to execute the query
LOAD DATA INFILE ‘<<CSV File Name>>’
INTO TABLE <<Table Name>>
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\n’
IGNORE 1 ROWS
Example :-
LOAD DATA INFILE ‘f:/LeadsDetails.csv’
INTO TABLE oldleads
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\n’
IGNORE 1 ROWS