Contents
- 1 Combine 2 fields into one result
- 2 Add fixed String to a field
- 3 Do not return Duplicates
- 4 Return the 'Sum' of all fields selected
- 5 Round to 2 decimal places
- 6 Selects only the field with the highest numerical value
- 7 Selects the leftmost 4 characters in a field
- 8 Select date and format result in MM/DD/YYYY
- 9 Join fields from different tables
- 10 Change Column order within a table
- 11 Allow access from remote host
Combine 2 fields into one result
SELECT CONCAT(fieldname1, fieldname2) FROM table
Example:
SELECT CONCAT(u.first, ' ', u.last) AS name FROM usr_table u
ID | first | last | ----------------------------- 1 | Led | Hed | 2 | Jimmy | Page |
Results:
Name
Led Hed
Jimmy Page
Add fixed String to a field
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
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
Allow access from remote host
GRANT ALL ON <DatabaseName>.* TO <User>@"<IP Address or Hostname>" IDENTIFIED BY "<Password>"; FLUSH PRIVILEGES;
Example:
GRANT ALL ON *.* TO root@"10.0.0.25" IDENTIFIED BY "SomePassword"; FLUSH PRIVILEGES;
This grants 'ALL' privileges for every database (*.*) to the user 'root' when connecting from IP Address 10.0.0.25 and the password given is 'SomePassword'
NOTE: Granting 'ALL' privileges to all (*.*) databases can be a security risk. In real world scenarios, only grant the privileges that are necessary and only to the user/hosts that need them.