a.3.6 database systems

Learn the professional principles of storing and managing massive amounts of structured information using linked tables to keep data reliable and easy to search.

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

= field name

fk

= foreign key name

pk

= primary key name

[...]

= optional

{...}⁺

= one or more (comma separated)

{...}*

= zero or more (comma separated)

{...|...}

= alternatives

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

pattern

...

The 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: February 10th, 2026

Login

a.3.6 database systems

MySQL Cheat Sheet