MySQL Cheat Sheet - Revision history

Michael.mast at 13:56, 4 October 2022

2022-10-04T13:56:14Z

2018-11-27T18:53:59Z

Transactions per second

2018-11-27T18:33:58Z

Transactions per second

2018-11-27T18:32:58Z

Advanced Queries

2018-10-31T16:44:23Z

2018-10-29T19:36:18Z

2018-07-25T11:33:30Z

General use

2018-07-25T11:33:18Z

Advanced Queries

2018-07-25T11:32:59Z

General use

2018-07-06T19:46:48Z

Alter Table

@@ Line 65: / Line 65: @@
 (this is kept in your home directory)
   rm -rf ~/.mysql_history
-===Remove user===
+===User Management===
   drop user <username>@<host>;
 ===Remove database===

← Older revision		Revision as of 18:53, 27 November 2018
Line 38:		Line 38:
	1 row in set (0.00 sec)		1 row in set (0.00 sec)

		+	</pre>
		+	To do this without a calculator<ref>https://dev.mysql.com/doc/refman/8.0/en/arithmetic-functions.html</ref><ref>https://dev.mysql.com/doc/refman/8.0/en/performance-schema-status-variable-tables.html</ref>
		+	<pre>
		+	MariaDB [(none)]> select (select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME='Questions') DIV (select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME='UPTIME');
		+	+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
		+	\| (select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME='Questions') DIV (select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME='UPTIME') \|
		+	+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
		+	\| 19 \|
		+	+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
		+	1 row in set (0.00 sec)
	</pre>		</pre>

@@ Line 12: / Line 12: @@
 Something helpful for when you are looking to migrate to the cloud.<ref>https://forums.mysql.com/read.php?10,532967,533420</ref>
 <pre>
 </pre>

@@ Line 8: / Line 8: @@
 <pre>
 select user_name from groups where group_name = any (select user_group from permissions where directory like '%some/directory%');
 </pre>

@@ Line 37: / Line 37: @@
 ===Show granted privileges===
   <pre>SHOW GRANTS FOR 'root'@'localhost';</pre>
 ===View users===

@@ Line 54: / Line 54: @@
 <pre>
 grant all privileges on <database>.* to '<user name>'@'%' identified by '<password>' require ssl;
 </pre>

@@ Line 2: / Line 2: @@
 <ref>http://alvinalexander.com/blog/post/mysql/show-users-i-ve-created-in-mysql-database</ref>
-====Advanced Queries====
+===Advanced Queries===
-====Sub Query===
+====Sub Query====
 Select from an "array" created from another query. You can only pull from a single column. In this example I am looking for users that are members of a group that has access to a directory.
 <pre>

@@ Line 1: / Line 1: @@
 ==General use==
 <ref>http://alvinalexander.com/blog/post/mysql/show-users-i-ve-created-in-mysql-database</ref>
 ===Alter Table===
 ====Add columnn====
@@ Line 9: / Line 17: @@
   alter table <table name> modify column <column name> DECIMAL(10, 2);
-===View users===
+===Delete rows older than certain  date===
- select host, user, password from mysql.user;
+<ref>https://benperove.com/delete-mysql-rows-older-than-date/</ref>
-<ref>http://stackoverflow.com/questions/4561292/how-to-clear-query-cache-in-mysql</ref>
+<pre>DELETE FROM `table` WHERE `column` < DATE_SUB(NOW(), INTERVAL 3 MONTH);</pre>
 ===Remove history===
 (this is kept in your home directory)
@@ Line 27: / Line 36: @@
 ===Show granted privileges===
   <pre>SHOW GRANTS FOR 'root'@'localhost';</pre>
-===Delete rows older than certain  date===
-<ref>https://benperove.com/delete-mysql-rows-older-than-date/</ref>
+===View users===
-<pre>DELETE FROM `table` WHERE `column` < DATE_SUB(NOW(), INTERVAL 3 MONTH);</pre>
+ select host, user, password from mysql.user;
 ==AWS RDS==

@@ Line 4: / Line 4: @@
 ====Add columnn====
 <ref>http://www.mysqltutorial.org/mysql-add-column/</ref>
-alter table <tablename> add COLUMN hash VARCHAR(32) after <name of another column>;
+ alter table <tablename> add COLUMN hash VARCHAR(32) after <name of another column>;
 ===View users===