MySQL - commands #mysql

mysql-commands.sh

### MySQL ###
#########################

# MySQL commands:
http://www.pantz.org/software/mysql/mysqlcommands.html

# Add database:
mysql> create database DATABASENAME;

# Delete database:
mysql> drop database DATABASENAME;

# MySQL dump:
$ mysqldump -u -p --add-drop-table DATABASENAME > ~/dumps/DATABASENAME.sql

# MySQL import:
$ mysql -uroot -p website_dev < ~/NAME/Public/website_dev.sql

# MySQL login into database - use credentials in config file:
/usr/bin/mysql -h localhost -u DATABASENAME -p

# MySQL socket path for MAMP:
/Applications/MAMP/tmp/mysql/mysql.sock

# Switch to a database.
mysql> use [db name];

# To see all the tables in the db.
mysql> show tables;

# delete table
mysql> DROP TABLE tablename;

# Check table sizes.
# change "database_name"
SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM information_schema.TABLES
WHERE table_schema = "database_name"
ORDER BY (data_length + index_length) DESC;

# Delete or truncate data from a table
# https://www.electrictoolbox.com/article/mysql/delete-all-data-mysql/
mysql> TRUNCATE TABLE tablename; # faster - deletes table and recreates it
# or
mysql> DELETE FROM tablename; # slower - keeps table by deletes data