From LedHed's Wiki
Jump to: navigation, search
Line 65: Line 65:
 
<br>
 
<br>
 
<br>
 
<br>
Change Column order within a table
+
Change Column order within a table<br>
 +
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 AFTER ColumnNameToPutAfter
 
  ALTER TABLE TableName MODIFY COLUMN ColumnNameToMove longtext BEFORE ColumnNameToPutBefore
 
  ALTER TABLE TableName MODIFY COLUMN ColumnNameToMove longtext BEFORE ColumnNameToPutBefore

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