Saucepan

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
======MySQL Cheat Sheet======
=====Clear Screen=====
On Linux
<code>CTRL+L</code>
=====General Database=====
====Connect to MySQL====
<code>mysql -uUSERNAME -p -h hostname -P 3306</code>
Assuming that you are connecting to the localhost and that MySQL is running on the default port of 3306, you can use a shorter command
<code>mysql -uUSERNAME -p</code>
====Allow Perl to Connect To MySQL====
Run this command to allow Perl to connect to Database server while still having SELinux enabled.
<code>semanage boolean -m --on httpd_can_network_connect_db</code>
====Allow Remote Connection====
In this example we allow the user 'testuser' on remote PC workstation1.example.com to access everything on the database testdb. We also set the password to be 123456;
<code>GRANT ALL PRIVILEGES ON testdb.* to 'testuser'@'db.example.com' IDENTIFIED BY '123456';</code>
====Get Database Size====
<code>SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB"
FROM information_schema.TABLES GROUP BY table_schema ; </code>
=====Users=====
====List Users====
<code>SELECT user FROM mysql.user;</code>
<code>SELECT user,host FROM mysql.user;</code>
====Create User====
<code>CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'PASSWORD';</code>
====Delete User====
<code>DROP USER 'testuser'@'localhost';</code>
=====Passwords=====
====Change Password====
<code>SET PASSWORD FOR 'testuser'@'%' = PASSWORD('cleartextpassword');</code>
<code>SET PASSWORD FOR 'testuser'@'localhost' = PASSWORD('cleartextpassword');</code>
=====Permissions=====
====Show Permissions/Grants====
<code>SHOW GRANTS;</code>
<code>SHOW GRANTS FOR 'root'@'localhost';</code>
====Grant Permissions====
<code>GRANT ALL ON testdb.* TO 'testuser'@'localhost';
flush privileges;</code>
<code>GRANT SELECT, INSERT, UPDATE, DELETE ON testdb.* TO 'testuser'@'localhost';
flush privileges;</code>
====Revoke Permissions====
<code>REVOKE UPDATE, DELETE ON testdb.* FROM 'testuser'@'localhost';
flush privileges;</code>
=====Databases and Tables=====
====List Databases====
<code>SHOW DATABASES;</code>
====Create Database====
<code>CREATE DATABASE testdb;</code>
====Delete Database====
<code> DROP DATABASE testdb;</code>
====List Tables====
<code>SHOW TABLES FROM testdb</code>
====List Columns====
<code>SHOW COLUMNS FROM testdb;</code>

=====Adding Data=====
====Update Data====
<code>UPDATE databasename.tablename SET columnname="some_value" WHERE columnname="value";</code>
====Insert Data====
<code>INSERT INTO databasename.tablename (columnname1, columnname2) VALUES ("value1", 132);</code>
====Use COUNT====
<code>select a.col1, a.col2,  (select count(*) from table2 b where b.col1=a.col1) from table1 as a group by col1;</code>