Combine 2 fields into one result
SELECT CONCAT(fieldname1, fieldname2) FROM table
Add fixed String to a field (beginning or end)
SELECT CONCAT(fieldname1, 'fixed string') FROM table
Do not return Duplicates
SELECT DISTINCT fieldname FROM table
Return the 'Sum' of all fields selected
SELECT SUM(fieldname) FROM table
2 = Round to 2 decimal places
SELECT ROUND(fieldname, 2) FROM table
Selects only the field with the highest numerical value
SELECT MAX(fieldname) FROM table
Selects the leftmost 4 characters in a field
SELECT LEFT(fieldname, 4) FROM table
Select date and format result in MM/DD/YYYY
SELECT DATE_FORMAT(fieldname, '%c/%d/%Y') from table
%a Abbreviated weekday name (Sun..Sat) %b Abbreviated month name (Jan..Dec) %c Month, numeric (0..12) %D Day of the month with English suffix (0th, 1st, 2nd, 3rd, â¦) %d Day of the month, numeric (00..31) %e Day of the month, numeric (0..31) %f Microseconds (000000..999999) %H Hour (00..23) %h Hour (01..12) %I Hour (01..12) %i Minutes, numeric (00..59) %j Day of year (001..366) %k Hour (0..23) %l Hour (1..12) %M Month name (January..December) %m Month, numeric (00..12) %p AM or PM %r Time, 12-hour (hh:mm:ss followed by AM or PM) %S Seconds (00..59) %s Seconds (00..59) %T Time, 24-hour (hh:mm:ss) %U Week (00..53), where Sunday is the first day of the week %u Week (00..53), where Monday is the first day of the week %V Week (01..53), where Sunday is the first day of the week; used with %X %v Week (01..53), where Monday is the first day of the week; used with %x %W Weekday name (Sunday..Saturday) %w Day of the week (0=Sunday..6=Saturday) %X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V %x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v %Y Year, numeric, four digits %y Year, numeric (two digits) %% A literal â%â character %x x, for any âxâ not listed above
Join fields from different tables
SELECT a.fieldname1 b.fieldname2 FROM tableA a LEFT JOIN tableB b ON a.fieldname1 = b.fieldname1
To join these fields the values of a.fieldname1 and b.fieldname1 must be equal
Example: SELECT j.JobName b.Amount FROM job j JOIN invoice i ON j.JobID = i.JobID WHERE j.JobID = '00123'
The job table and invoice table were joined by the value of their JobID fields
b.Amount was Selected from the invoice table where invoice.JobID = '00123' and j.JobName was Selected from the job table where job.JobID = '00123'
Change Column order within a table
ALTER TABLE TableName MODIFY COLUMN ColumnNameToMove longtext AFTER ColumnNameToPutAfter ALTER TABLE TableName MODIFY COLUMN ColumnNameToMove longtext BEFORE ColumnNameToPutBefore ALTER TABLE TableName MODIFY COLUMN ColumnNameToMove longtext FIRST