MySQL Command Reference - Revision history

Ledhed at 03:21, 7 February 2023

2023-02-07T03:21:12Z

2023-02-07T03:20:48Z

‎Show Table Size

2023-02-07T03:20:24Z

2023-02-07T03:13:28Z

2014-08-16T23:55:39Z

2011-10-21T15:50:24Z

2011-10-21T15:48:12Z

2011-10-20T00:03:27Z

2006-11-08T18:52:08Z

2006-11-08T18:51:08Z

@@ Line 160: / Line 160: @@
   FROM information_schema.TABLES
   ORDER BY (data_length + index_length) DESC;
 [[Category:MySQL]]

@@ Line 156: / Line 156: @@
 == Show Table Size ==
-  SELECT
+  SELECT table_schema as "Database", table_name AS "Table",
- table_schema as "Database",
- table_name AS "Table",
   ROUND(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"
   FROM information_schema.TABLES

@@ Line 153: / Line 153: @@
   GROUP BY table_schema;
 <br>
 [[Category:MySQL]]

@@ Line 145: / Line 145: @@
 <br>
 <br>
 [[Category:MySQL]]

@@ Line 131: / Line 131: @@
 ''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>
 [[Category:MySQL]]

@@ Line 4: / Line 4: @@
 Example:
   SELECT CONCAT(u.first, ' ', u.last) AS name FROM usr_table u
-<br>
    ID |    first |      last |
   -----------------------------
-|      Led |       Hed |
+|    Led   |      Hed  |
 |    Jimmy |      Page |
 Results:
-      Name
+  Name
------------
+ -----------
-   Led Hed
+ Led Hed
-Jimmy Page
+ Jimmy Page

@@ Line 1: / Line 1: @@
-''Combine 2 fields into one result''
+== Combine 2 fields into one result ==
   SELECT CONCAT(fieldname1, fieldname2) FROM table
 <br>
-''Add fixed String to a field (beginning or end)''
+  ID |    first |      last |
   SELECT CONCAT(fieldname1, 'fixed string') FROM table
 <br>
-''Do not return Duplicates''
   SELECT DISTINCT fieldname FROM table
 <br>
-''Return the 'Sum' of all fields selected''
   SELECT SUM(fieldname) FROM table
 <br>
-''2 = Round to 2 decimal places''
   SELECT ROUND(fieldname, 2) FROM table
 <br>
-''Selects only the field with the highest numerical value''
   SELECT MAX(fieldname) FROM table
 <br>
-''Selects the leftmost 4 characters in a field''
   SELECT LEFT(fieldname, 4) FROM table
 <br>
-'' Select date and format result in MM/DD/YYYY ''
   SELECT DATE_FORMAT(fieldname, '%c/%d/%Y') from table
@@ Line 57: / Line 97: @@
   %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''<br>
@@ Line 65: / Line 108: @@
 <br>
 <br>
-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
@@ Line 86: / Line 133: @@
 <br>
 <br>
 [[Category:MySQL]]

@@ Line 72: / Line 72: @@
 <br>
 <br>
 Allow access from remote host
   GRANT ALL ON <DatabaseName>.* TO <User>@"<IP Address or Hostname>" IDENTIFIED BY "<Password>";
   FLUSH PRIVILEGES;
@@ Line 78: / Line 82: @@
   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' ''
+''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>
 <br>
 <br>
 [[Category:MySQL]]