DEV Community

Meng Ly
Meng Ly

Posted on

MySQL cheatsheet

1. Browsing

SHOW DATABASES;
SHOW TABLES;
SHOW FIELDS FROM table / DESCRIBE table;
SHOW CREATE TABLE table;
SHOW PROCESSLIST;
KILL process_number;
Enter fullscreen mode Exit fullscreen mode

2. 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 ...
Enter fullscreen mode Exit fullscreen mode

3. 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 ...
Enter fullscreen mode Exit fullscreen mode

4. 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
Enter fullscreen mode Exit fullscreen mode

5. Create / Open / Delete Database

CREATE DATABASE DatabaseName;
CREATE DATABASE DatabaseName CHARACTER SET utf8;
USE DatabaseName;
DROP DATABASE DatabaseName;
ALTER DATABASE DatabaseName CHARACTER SET utf8;
Enter fullscreen mode Exit fullscreen mode

6. Backup Database to SQL File

mysqldump -u Username -p dbNameYouWant > databasename_backup.sql
Enter fullscreen mode Exit fullscreen mode

7. Restore from backup SQL File

mysql -u Username -p dbNameYouWant < databasename_backup.sql;
Enter fullscreen mode Exit fullscreen mode

8. Repair Tables After Unclean Shutdown

mysqlcheck --all-databases;
mysqlcheck --all-databases --fast;
Enter fullscreen mode Exit fullscreen mode

9. Insert

INSERT INTO table1 (field1, field2) VALUES (value1, value2); 
Enter fullscreen mode Exit fullscreen mode

10. Delete

DELETE FROM table1 / TRUNCATE table1
DELETE FROM table1 WHERE condition
DELETE FROM table1, table2 WHERE table1.id1 =
  table2.id2 AND condition
Enter fullscreen mode Exit fullscreen mode

11. 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;
Enter fullscreen mode Exit fullscreen mode

12. Create / Delete / Modify Table

12.1 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));
Enter fullscreen mode Exit fullscreen mode
CREATE TABLE table1 (fk_field1 type1, field2 type2, ...,
  FOREIGN KEY (fk_field1) REFERENCES table2 (t2_fieldA))
    [ON UPDATE|ON DELETE] [CASCADE|SET NULL]
Enter fullscreen mode Exit fullscreen mode
CREATE TABLE table1 (fk_field1 type1, fk_field2 type2, ...,
 FOREIGN KEY (fk_field1, fk_field2) REFERENCES table2 (t2_fieldA, t2_fieldB))
Enter fullscreen mode Exit fullscreen mode
CREATE TABLE table IF NOT EXISTS;
Enter fullscreen mode Exit fullscreen mode
CREATE TEMPORARY TABLE table;
Enter fullscreen mode Exit fullscreen mode

12.2 Delete

DROP TABLE table;
DROP TABLE IF EXISTS table;
DROP TABLE table1, table2, ...
Enter fullscreen mode Exit fullscreen mode

12.3 Modify

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);
Enter fullscreen mode Exit fullscreen mode

12.4 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
Enter fullscreen mode Exit fullscreen mode

13. Keys

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

14. Users and Privileges

CREATE USER 'user'@'localhost';
GRANT ALL PRIVILEGES ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, DELETE ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password';
REVOKE ALL PRIVILEGES ON base.* FROM 'user'@'host'; -- one permission only
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host'; -- all permissions
FLUSH PRIVILEGES;
Enter fullscreen mode Exit fullscreen mode
SET PASSWORD = PASSWORD('new_pass');
SET PASSWORD FOR 'user'@'host' = PASSWORD('new_pass');
SET PASSWORD = OLD_PASSWORD('new_pass');
Enter fullscreen mode Exit fullscreen mode
DROP USER 'user'@'host';
Enter fullscreen mode Exit fullscreen mode

15. 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
Enter fullscreen mode Exit fullscreen mode

Top comments (0)