MySQL

SQL

• Show all data in a table: SELECT * FROM [table];
• Vertical format results: SELECT * FROM [table]\G
• SELECT [col], count(*) FROM [table] GROUP BY 1 HAVING count(*) > 1;
• UPDATE [table] SET [col] = 0 where [col] is null;
• INSERT INTO [table] ([column1], [column2]) VALUES ([literal|now()]);
• INSERT INTO [table1] ([col1], [col2]) SELECT a.[col1], a.[col2] FROM [table2] a;
• Redirect output to file: TEE [file]. Stop redirect NOTEE
• Run SQL file: SOURCE [file]

Meta

• Database sizes: 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;
• Number of rows per table: SELECT table_name, FORMAT(table_rows,0) FROM information_schema.TABLES WHERE table_schema = '[database_name]';
• Show structure of tables: SELECT table_name, ordinal_position, column_name, data_type, character_maximum_length FROM columns where table_schema = '[database]' order by 1,2;
• Rename table: ALTER TABLE [table] RENAME [new_table];
• Rename column: ALTER TABLE [table] CHANGE COLUMN [col] [new_col] varchar(32);
• Make an existing column nullable: Columns are nullable by default so just modify the column, leaving the NOT NULL clause off. E.g. ALTER TABLE [table] MODIFY [boolean_column] BOOL;
• Show user access: SHOW GRANTS FOR '[user]'@'localhost';
• CREATE USER '[user]'@'localhost' IDENTIFIED BY '[password]';
• GRANT ALL ON [database].* to [user]@localhost;
• List all users: SELECT * FROM mysql.user;
• Refresh cached permissions: FLUSH PRIVILEGES;
• List all databases on the sql server: SHOW DATABASES;
• Switch to a database: USE [database];
• See all the tables in the database: SHOW TABLES;
• See database's field formats: DESCRIBE [table];
• Delete a database: DROP DATABASE [database];
• Delete a table: DROP TABLE [table];

Command Line

• To start server: sudo /etc/init.d/mysql start
• To stop server: sudo /etc/init.d/mysql stop
• To restart server: sudo /etc/init.d/mysql restart
• To check the status of server: sudo /etc/init.d/mysql status
• Login: mysql -uroot -proot
• Create database: mysql --user=root --execute="CREATE DATABASE if not exists [database] character set utf8"

mysqldump

• Dump single table: mysqldump -u[user] -p[password] [database] [table] > [file]
• Data only: mysqldump --password=[password] --skip-triggers --compact --no-create-info [database] > [file]
• When dumping a live database use the --lock-tables=false option.

.my.cnf

Create a .my.cnf configuration file in your home directory. Containing:

[client]
user = <userid>
password = <password>
database = <db_name>
[mysql]
# Do not apply updates or deletes which lack where clauses
safe-updates
# Include database name in prompt
prompt=\d>\_