From LedHed's Wiki
Jump to: navigation, search
 
(11 intermediate revisions by the same user not shown)
Line 1: Line 1:
''Combine 2 fields into one result''
+
== Combine 2 fields into one result ==
 +
 
 
  SELECT CONCAT(fieldname1, fieldname2) FROM table
 
  SELECT CONCAT(fieldname1, fieldname2) FROM table
<br>
+
Example:
''Add fixed String to a field (beginning or end)''
+
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
 
  SELECT CONCAT(fieldname1, 'fixed string') FROM table
 
<br>
 
<br>
''Do not return Duplicates''
+
 
 +
 
 +
 
 +
== Do not return Duplicates ==
 +
 
 
  SELECT DISTINCT fieldname FROM table
 
  SELECT DISTINCT fieldname FROM table
 
<br>
 
<br>
''Return the 'Sum' of all fields selected''
+
 
 +
 
 +
== Return the 'Sum' of all fields selected ==
 +
 
 
  SELECT SUM(fieldname) FROM table
 
  SELECT SUM(fieldname) FROM table
 
<br>
 
<br>
''2 = Round to 2 decimal places''
+
 
 +
 
 +
 
 +
== Round to 2 decimal places ==
 +
 
 
  SELECT ROUND(fieldname, 2) FROM table
 
  SELECT ROUND(fieldname, 2) FROM table
 
<br>
 
<br>
''Selects only the field with the highest numerical value''
+
 
 +
 
 +
 
 +
== Selects only the field with the highest numerical value ==
 +
 
 
  SELECT MAX(fieldname) FROM table
 
  SELECT MAX(fieldname) FROM table
 
<br>
 
<br>
''Selects the leftmost 4 characters in a field''
+
 
 +
 
 +
 
 +
== Selects the leftmost 4 characters in a field ==
 +
 
 
  SELECT LEFT(fieldname, 4) FROM table
 
  SELECT LEFT(fieldname, 4) FROM table
 
<br>
 
<br>
'' Select date and format result in MM/DD/YYYY ''
+
 
 +
 
 +
 
 +
== Select date and format result in MM/DD/YYYY ==
 +
 
 
  SELECT DATE_FORMAT(fieldname, '%c/%d/%Y') from table
 
  SELECT DATE_FORMAT(fieldname, '%c/%d/%Y') from table
  
Line 57: Line 97:
 
  %x x, for any ‘x’ not listed above
 
  %x x, for any ‘x’ not listed above
  
''Join fields from different tables''
+
 
 +
 
 +
== Join fields from different tables ==
 +
 
 
  SELECT a.fieldname1 b.fieldname2 FROM tableA a LEFT JOIN tableB b ON a.fieldname1 = b.fieldname1
 
  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''<br>
 
''To join these fields the values of a.fieldname1 and b.fieldname1 must be equal''<br>
Line 65: Line 108:
 
<br>
 
<br>
 
<br>
 
<br>
Change Column order within a table<br>
+
 
 +
 
 +
 
 +
== Change Column order within a table ==
 +
 
 
Reference: http://dev.mysql.com/doc/refman/5.0/en/change-column-order.html
 
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
Line 72: Line 119:
 
<br>
 
<br>
 
<br>
 
<br>
Allow access from remote host
+
 
  GRANT ALL ON <DatabaseName>.* TO <user>@"<IP Address>" IDENTIFIED BY "<Password>";
+
 
 +
 
 +
== Allow access from remote host ==
 +
 
 +
  GRANT ALL ON <DatabaseName>.* TO <User>@"<IP Address or Hostname>" IDENTIFIED BY "<Password>";
 
  FLUSH PRIVILEGES;
 
  FLUSH PRIVILEGES;
 
Example:
 
Example:
 
  GRANT ALL ON *.* TO root@"10.0.0.25" IDENTIFIED BY "SomePassword";
 
  GRANT ALL ON *.* TO root@"10.0.0.25" IDENTIFIED BY "SomePassword";
 
  FLUSH PRIVILEGES;
 
  FLUSH PRIVILEGES;
''This grants 'ALL' privileges for every database (*.*) to user 'root' when connecting from IP Address 10.0.0.25 and the password given is 'SomePassword' ''
+
''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' ''<br>
 +
''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.''
 +
<br>
 +
<br>
 +
 
 +
 
 +
== Show a Triggers ==
 +
SHOW TRIGGERS;
 +
<br>
 +
<br>
 +
 
 +
 
 +
== Delete a Trigger ==
 +
DROP TRIGGER <Trigger_Name>;
 +
<br>
 +
<br>
 +
 
 +
 
 +
== Show Database Size ==
 +
SELECT table_schema AS "Database",
 +
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
 +
FROM information_schema.TABLES
 +
GROUP BY table_schema;
 +
<br>
 +
 
 +
 
 +
== Show Table Size ==
 +
SELECT table_schema as "Database", table_name AS "Table",
 +
ROUND(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"
 +
FROM information_schema.TABLES
 +
ORDER BY (data_length + index_length) DESC;
 +
<br>
 +
 
  
 
[[Category:MySQL]]
 
[[Category:MySQL]]

Latest revision as of 03:21, 7 February 2023

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.


Show a Triggers

SHOW TRIGGERS;




Delete a Trigger

DROP TRIGGER <Trigger_Name>;




Show Database Size

SELECT table_schema AS "Database", 
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" 
FROM information_schema.TABLES 
GROUP BY table_schema;



Show Table Size

SELECT table_schema as "Database", table_name AS "Table", 
ROUND(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" 
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;