databases
Database reference
MySQL Cheat Sheet
Please be aware that there are considerably more MySQL commands than I've listed here. These are, what I consider, the basics. If you wish to browse the full reference (currently version 8.0) please visit the MySQL 8.0 Reference Manual
Where...
fld fk pk [...] {...}⁺ {...}* {...|...}Show all the databases on the server (which you have access to)
SHOW DATABASES;
Make the chosen database the focus of all sql commands
USE db;
Show all the tables in the active database
SHOW TABLES;
Show details of all the columns/fields in the table
SHOW [{COLUMNS|FIELDS}] FROM tbl / DESCRIBE tbl;
Show who has permission on the chosen database
SHOW GRANTS;
Show the sql command which can be used to recreate the table
SHOW CREATE TABLE tbl;
Create table
CREATE TABLE tbl [IF NOT EXISTS]
(
{fld type [{NULL|NOT NULL}] [DEFAULT val] [AUTO_INCREMENT] [PRIMARY KEY] [UNIQUE]}⁺
[INDEX name ({fld}⁺)]
[CONSTRAINT name FOREIGN KEY ({fk}⁺) REFERENCES tbl({pk}⁺)]
);
[ON UPDATE {CASCADE|SET NULL}]
[ON DELETE {CASCADE|SET NULL}]
Delete table (CARE!)
DROP TABLE [IF EXISTS] {tbl}⁺;
Rename table
RENAME TABLE {tbl TO tbl}⁺;
Truncate (empty) table (CARE!)
TRUNCATE [TABLE] tbl;
Alter table
ALTER TABLE tbl
ADD COLUMN {fld type [{NOT NULL|NULL}] [DEFAULT val] [AUTO_INCREMENT] [UNIQUE] [{FIRST|AFTER fld}]}⁺;
|ADD CONSTRAINT name {PRIMARY KEY|UNIQUE} ({fld}⁺);
|ADD CONSTRAINT name FOREIGN KEY ({fk}⁺) REFERENCES tbl({pk}⁺);
|ADD INDEX name {fld}⁺;
|DROP [COLUMN] fld;
|ALTER [COLUMN] fld {SET DEFAULT val|DROP DEFAULT};
|CHANGE fld fld type [{NOT NULL|NULL}] [DEFAULT val] [AUTO_INCREMENT] [UNIQUE] [{FIRST|AFTER fld}];
|MODIFY [COLUMN] fld type [{NOT NULL|NULL}] [DEFAULT val] [AUTO_INCREMENT] [UNIQUE] [{FIRST|AFTER fld}];
|RENAME TO tbl;
|RENAME COLUMN fld TO fld;
|ORDER BY {fld}⁺;
|DROP PRIMARY KEY;
|DROP FOREIGN KEY name;
|DROP INDEX name
|RENAME INDEX name TO name;
Select data
SELECT [DISTINCT] {*|{fld}⁺} FROM {tbl}⁺
[WHERE condition]
[GROUP BY fld]
[ORDER By {fld}⁺ [{ASC|DESC}]]
[LIMIT val]
Conditions
fld = val
fld <> val
fld LIKE 'pattern'
fld IS [NOT] NULL
fld IS [NOT] IN ({val}⁺)
condition {AND|OR} condition
For
patternThe percent sign (%) represents zero, one, or multiple characters
The underscore sign (_) represents one, single character
Insert data
INSERT INTO tbl ({fld}⁺) VALUES ({val}⁺);
Update data
UPDATE {tbl}⁺ SET {fld=val}⁺ WHERE condition;
Delete data
DELETE FROM {tbl}⁺ [WHERE condition]
https://devhints.io/mysql
Last modified: July 12th, 2023
