Difference between revisions of "MySQL Command Reference"

Revision as of 04:34, 7 November 2006

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
Reference: http://dev.mysql.com/doc/refman/5.0/en/change-column-order.html

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