MySQL cheat sheet

MySQL Locations

• Mac /usr/local/mysql/bin
• Windows /Program Files/MySQL/MySQL version/bin
• Xampp /xampp/mysql/bin

Add mysql to your PATH

# Current Session
export PATH=${PATH}:/usr/local/mysql/bin
# Permanantly
echo 'export PATH="/usr/local/mysql/bin:$PATH"' >> ~/.bash_profile

On Windows

1. Open the Start Search, type “env” or “environment variables,” and click on “Edit the system environment variables.”
2. In the System Properties window, click on the “Environment Variables…” button. More details here.

Reset Root Password

$ /etc/init.d/mysql stop

$ mysqld_safe --skip-grant-tables

$ mysql # on another terminal
mysql> UPDATE mysql.user SET password=PASSWORD('new_pass') WHERE user='root';

## Switch back to the mysqld_safe terminal and kill the process using Control + \
$ /etc/init.d/mysql start

Your commands may vary depending on your OS.

Main Data Types 1

TINYINT (1o: -128 to +127)
SMALLINT (2o: +-65 000)
MEDIUMINT (3o: +-16 000 000)
INT (4o: +- 2 000 000 000)
BIGINT (8o: +-9.10^18)

Precise interval: -(2^(8*N-1)) -> (2^8*N)-1

⚠ INT(2) = "2 digits displayed" -- NOT "number with 2 digits max"

FLOAT(M,D)
DOUBLE(M,D)
FLOAT(D=0->53)

⚠ 8,3 -> 12345,678 -- NOT 12345678,123!

Main Data Types 2

TIME (HH:MM)
YEAR (AAAA)
DATE (AAAA-MM-JJ)
DATETIME (AAAA-MM-JJ HH:MM; années 1000->9999)
TIMESTAMP (like DATETIME, but 1970->2038, compatible with Unix)

VARCHAR (single-line; explicit size)
TEXT (multi-lines; max size=65535)
BLOB (binary; max size=65535)

Variants for TEXT&BLOB: TINY (max=255), MEDIUM (max=~16000), and LONG (max=4Go). Ex: VARCHAR(32), TINYTEXT, LONGBLOB, MEDIUMTEXT

ENUM ('value1', 'value2', ...) -- (default NULL, or '' if NOT NULL)

Login, Backup, Restore, Repair

Login

mysql -u root -p

Backup Database to SQL File

mysqldump -u Username -p dbNameYouWant > databasename_backup.sql

Restore from backup SQL File

mysql -u Username -p dbNameYouWant < databasename_backup.sql;

Repair Tables After Unclean Shutdown

mysqlcheck --all-databases;
mysqlcheck --all-databases --fast;

Exit

exit;

Browsing

SHOW DATABASES;
SHOW TABLES;
SHOW FIELDS FROM table / DESCRIBE table;
SHOW CREATE TABLE table;
SHOW PROCESSLIST;
KILL process_number;

Select

SELECT * FROM table;
SELECT * FROM table1, table2;
SELECT field1, field2 FROM table1, table2;
SELECT ... FROM ... WHERE condition
SELECT ... FROM ... WHERE condition GROUP BY field;
SELECT ... FROM ... WHERE condition GROUP BY field HAVING condition2;
SELECT ... FROM ... WHERE condition ORDER BY field1, field2;
SELECT ... FROM ... WHERE condition ORDER BY field1, field2 DESC;
SELECT ... FROM ... WHERE condition LIMIT 10;
SELECT DISTINCT field1 FROM ...
SELECT DISTINCT field1, field2 FROM ...

Select - Join

SELECT ... FROM t1 JOIN t2 ON t1.id1 = t2.id2 WHERE condition;
SELECT ... FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2 WHERE condition;
SELECT ... FROM t1 JOIN (t2 JOIN t3 ON ...) ON ...

Conditions

field1 = value1
field1 <> value1
field1 LIKE 'value _ %'
field1 IS NULL
field1 IS NOT NULL
field1 IS IN (value1, value2)
field1 IS NOT IN (value1, value2)
condition1 AND condition2
condition1 OR condition2

Create / Open / Delete Database

CREATE DATABASE DatabaseName;
CREATE DATABASE DatabaseName CHARACTER SET utf8;
USE DatabaseName;
DROP DATABASE DatabaseName;
ALTER DATABASE DatabaseName CHARACTER SET utf8;

Insert

INSERT INTO table1 (field1, field2) VALUES (value1, value2);

Delete

DELETE FROM table1 / TRUNCATE table1
DELETE FROM table1 WHERE condition
DELETE FROM table1, table2 WHERE table1.id1 =
table2.id2 AND condition

Update

UPDATE table1 SET field1=new_value1 WHERE condition;
UPDATE table1, table2 SET field1=new_value1, field2=new_value2, ... WHERE
table1.id1 = table2.id2 AND condition;

Create

CREATE TABLE table (field1 type1, field2 type2);
CREATE TABLE table (field1 type1, field2 type2, INDEX (field));
CREATE TABLE table (field1 type1, field2 type2, PRIMARY KEY (field1));
CREATE TABLE table (field1 type1, field2 type2, PRIMARY KEY (field1,field2));

CREATE TABLE table1 (fk_field1 type1, field2 type2, ...,
FOREIGN KEY (fk_field1) REFERENCES table2 (t2_fieldA))
[ON UPDATE|ON DELETE] [CASCADE|SET NULL]

CREATE TABLE table1 (fk_field1 type1, fk_field2 type2, ...,
FOREIGN KEY (fk_field1, fk_field2) REFERENCES table2 (t2_fieldA, t2_fieldB))

CREATE TABLE table IF NOT EXISTS;

CREATE TEMPORARY TABLE table;

Drop

DROP TABLE table;
DROP TABLE IF EXISTS table;
DROP TABLE table1, table2, ...

Alter

ALTER TABLE table MODIFY field1 type1
ALTER TABLE table MODIFY field1 type1 NOT NULL ...
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 NOT NULL ...
ALTER TABLE table ALTER field1 SET DEFAULT ...
ALTER TABLE table ALTER field1 DROP DEFAULT
ALTER TABLE table ADD new_name_field1 type1
ALTER TABLE table ADD new_name_field1 type1 FIRST
ALTER TABLE table ADD new_name_field1 type1 AFTER another_field
ALTER TABLE table DROP field1
ALTER TABLE table ADD INDEX (field);

Change field order

ALTER TABLE table MODIFY field1 type1 FIRST
ALTER TABLE table MODIFY field1 type1 AFTER another_field
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 FIRST
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 AFTER
another_field

Keys

CREATE TABLE table (..., PRIMARY KEY (field1, field2))
CREATE TABLE table (..., FOREIGN KEY (field1, field2) REFERENCES table2
(t2_field1, t2_field2))

Show Users

SELECT User, Host FROM mysql.user;

Create User

CREATE USER 'someuser'@'localhost' IDENTIFIED BY 'somepassword';

Grant All Priveleges On All Databases

GRANT ALL PRIVILEGES ON * . * TO 'someuser'@'localhost';
FLUSH PRIVILEGES;

Show Grants

SHOW GRANTS FOR 'someuser'@'localhost';

Remove Grants

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'someuser'@'localhost';

Delete User

DROP USER 'someuser'@'localhost';

Useful MySQL Tips

• Index Usage: Apply indexes to columns used in WHERE, JOIN, or ORDER BY for improved query performance. Avoid over-indexing to prevent insert/update slowdowns.

• Query Optimization: Use EXPLAIN to analyze and optimize queries for better execution plans.

• Update Statistics: Regularly run ANALYZE TABLE to update statistics for the query optimizer.

• Query Caching: Enable query caching to speed up frequent identical read operations.

• Data Normalization: Normalize data to at least 3NF to eliminate redundancy and ensure data integrity.

• Batch INSERTs: Combine multiple INSERTs into a single query to reduce overhead.

• Parameterized Queries: Use parameterized queries with prepared statements for security and efficiency.

• Partition Large Tables: Use partitioning to divide large tables into manageable segments.

• Archive Old Data: Archive infrequently accessed data to improve query and backup performance.

• Stay Updated: Regularly update MySQL to benefit from the latest features, performance improvements, and security patches.

Useful MySQL Resources

1. MySQL Documentation - Comprehensive official MySQL documentation.
2. MySQL Tutorial - Tutorials for beginners to intermediate users.
3. Percona Database Performance Blog - MySQL performance tuning tips.
4. Use The Index, Luke - Guide on indexing and database performance.
5. MySQL Query Optimization - Official guide on optimizing queries.
6. MySQL Administration - Tools and practices for database administration.
7. MySQL Security Best Practices - Guide to securing MySQL databases.
8. Database Normalization Explained - Explanation of database normalization.
9. MySQL Backup and Recovery Best Practices - Strategies for database backup and recovery.
10. MySQL Features - Overview of MySQL's advanced features.