MySQL Command List

This is a reference of the most commonly used MySQL commands, posted here in 2008 and still in the top results for this blog. Almost everything below still works unchanged on MySQL 8.0 — the notable exceptions are the user-management commands, which were modernized and are listed in their current form. Full documentation: MySQL 8.0 Reference Manual.

General commands

USE database_name;              -- switch database; you must pick one after connecting
SHOW DATABASES;                 -- list all databases on the server
SHOW TABLES;                    -- list tables in the current database
SHOW TABLES FROM database_name; -- list tables in a given database
DESCRIBE table_name;            -- columns, types, keys (same as SHOW COLUMNS FROM)
SHOW INDEX FROM table_name;     -- all indexes on the table
SHOW CREATE TABLE table_name;   -- the exact DDL used to create the table

Table commands

CREATE TABLE table_name (
  column1 INT AUTO_INCREMENT PRIMARY KEY,
  column2 VARCHAR(64) NOT NULL,
  column3 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Useful column modifiers: AUTO_INCREMENT assigns the next sequential number on NULL; PRIMARY KEY makes the column unique and indexes it (one per table, should be NOT NULL); NOT NULL rejects NULL inserts; DEFAULT value substitutes on NULL.

ALTER TABLE table_name ADD COLUMN col INT;        -- add columns
ALTER TABLE table_name DROP COLUMN col;           -- remove a column
ALTER TABLE table_name MODIFY COLUMN col BIGINT;  -- change type, keep name
ALTER TABLE table_name CHANGE COLUMN old new BIGINT; -- rename + change type
ALTER TABLE table_name ADD INDEX idx_name (col1, col2);
CREATE INDEX idx_name ON table_name (col1, col2);    -- equivalent
DROP TABLE table_name;            -- removes the table permanently. Be careful.

On composite indexes: the index covers all leftmost prefixes of the column list, so (col1, col2) also serves queries filtering on col1 alone. The index name is optional with ALTER TABLE but required with CREATE INDEX.

Data commands

INSERT INTO table_name VALUES (v1, v2, ...);              -- full row, every column in order
INSERT INTO table_name (col1, col2) VALUES (v1, v2);      -- listed columns only
INSERT INTO table_name SET col1 = v1, col2 = v2;          -- explicit assignments
INSERT INTO t (a, b) SELECT x, y FROM other WHERE ...;    -- insert from a SELECT

UPDATE table_name SET col1 = v1, col2 = v2 WHERE ...;
DELETE FROM table_name WHERE ...;   -- no WHERE = empty the whole table

-- Bulk load from a file (tab-separated by default):
LOAD DATA LOCAL INFILE '/path/to/file.tsv'
  INTO TABLE table_name
  FIELDS TERMINATED BY '\t'
  LINES TERMINATED BY '\n';

-- Export to a file (server-side):
SELECT col1, col2 FROM table_name
  INTO OUTFILE '/var/lib/mysql-files/out.tsv'
  FIELDS TERMINATED BY '\t';

Two caveats that still bite people: INTO OUTFILE requires a complete path, and since MySQL 5.7 the server only allows writing to the directory named in the secure_file_priv system variable (commonly /var/lib/mysql-files/). Check yours with SHOW VARIABLES LIKE 'secure_file_priv';.

Users and privileges (modernized)

The old GRANT ... IDENTIFIED BY syntax and the PASSWORD() function are gone in MySQL 8.0. Create users and set passwords like this:

CREATE USER 'app'@'localhost' IDENTIFIED BY 'strong-password';
GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'app'@'localhost';
GRANT ALL PRIVILEGES ON database_name.* TO 'app'@'localhost';
REVOKE ALL PRIVILEGES ON database_name.* FROM 'app'@'localhost';
ALTER USER 'app'@'localhost' IDENTIFIED BY 'new-password';
DROP USER 'app'@'localhost';

Notes: since 8.0 the default authentication plugin is caching_sha2_password (watch out with old clients). FLUSH PRIVILEGES is only needed if you edit the grant tables directly with INSERT/UPDATE — CREATE USER and GRANT take effect immediately.

-- Inspect who has what:
SELECT user, host FROM mysql.user;
SHOW GRANTS FOR 'app'@'localhost';

Still worth knowing

SELECT table_name, table_rows
  FROM information_schema.tables
  WHERE table_schema = 'database_name';  -- metadata queries

EXPLAIN SELECT ...;  -- how the optimizer plans your query

See also: InnoDB or MyISAM? — spoiler: InnoDB, it has been the default since 5.5.