Mysql/CheatSheet

Quick links

Python/pymysql

Databases

• List all

SHOW DATABASES;

• Create

CREATE DATABASE [IF NOT EXISTS] database_name;

• Use

USE database_name;

• Delete

DROP DATABASE [IF EXISTS] database_name;

• Storage Engines
  • MyISAM (optimized for compression and speed. portable. not transaction safe. used to be default)
  • InnoDB (optimized for speed. portable. default)
  • MERGE
  • MEMORY (HEAP)
  • ARCHIVE
  • CSV
  • FEDERATED

Tables

• list

SHOW TABLES;

• create

CREATE TABLE [IF NOT EXISTS] table_name(
  column1 datatype constraint,
  column2 datatype constraint
);

CREATE TABLE IF NOT EXISTS cars(
  vehicle_id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  first_seen DATE,
  description TEXT
);

• show table information

DESCRIBE cars;

+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| vehicle_id | int(11)     | NO   | PRI | NULL    | auto_increment |
| title      | varchar(255)| NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+

• delete

DROP TABLE [IF EXISTS] cars;

column definitions

NOT NULL
DEFAULT default_value
AUTO_INCREMENT
ZEROFILL
UNIQUE 
PRIMARY KEY   (NOT NULL and UNIQUE)
INVISIBLE
COMMENT 'string'
CHECK (expr)

data types

TINYINT	        (A very small integer: 0/255, 1 byte)
SMALLINT	(A small integer: 0/65535, 2 bytes)
MEDIUMINT	(A medium-sized integer: 0/16777215, 3 bytes)
INT	        (A standard integer: 0/4294967295 4 bytes)
BIGINT	        (A large integer: 0/18446744073709551615 8 bytes)
DECIMAL(M,D)    (A fixed-point number: M=total digits(max=65), D=decimals(max=30))
DEC             (=DECIMAL)
FLOAT[(M,D)]    (A single-precision float (4 bytes)(M=digits, D=decimals))
DOUBLE[(M,D)]   (A double-precision float (8 bytes))
BIT[(M)]	(A bit field)

CHAR[(M)]       (A fixed-length character string, M=0-255, default=1)
VARCHAR(M)      (A variable-length string, M=0-65532) 
BINARY(M)       (A fixed-length binary string, M=bytes)
VARBINARY(M)	(A variable-length binary string, M=bytes)

TINYTEXT	(A very small string, max 255 chars)
TEXT[(M)]       (A small  string, M=0-65532)
MEDIUMTEXT	(A medium-sized string, 16777215 chars)
LONGTEXT	(A large string, 4294967295 chars) 
JSON            (=LONGTEXT)

ENUM	        (Each column value may be assigned one ENUM member)
                (e.g. ENUM('v1','v2'))
SET	        (Each column value may be assigned zero or more SET members)
                (e.g. SET('v1,'v2'))

TINYBLOB	(A very small BLOB (binary large object)
BLOB     	(A small BLOB)
MEDIUMBLOB	(A medium-sized BLOB)
LONGBLOB	(A large BLOB)

DATE	        (A date value: YYYY-MM-DD, 3 bytes)
TIME	        (A time value: hh:mm:ss, 3 bytes)
DATETIME[x]	(A date and time value: YYYY-MM-DD hh:mm:ss, 8 bytes,
                 x=fractional second precision)
TIMESTAMP       (A timestamp value: YYYY-MM-DD hh:mm:ss, 4 bytes)
YEAR	        (A year value: YYYY, 1 byte)

• data type attributes

SIGNED
UNSIGNED
ZEROFILL
UNSIGNED ZEROFILL
ZEROFILL UNSIGNED 

AUTO_INCREMENT

• charsets and collations

utf8_general_ci      (maxlen=1)
latin1_swedish_ci    (maxlen=3)
_ci = case insensitive

Data

Insert

INSERT INTO employees VALUES ('peter', 'pan', 25000);
INSERT INTO employees ('first', 'last', 'pay') VALUES ('peter', 'pan', 25000);

Select

SELECT * FROM employees;

SELECT * FROM employees WHERE last='pan';

SELECT * FROM employees WHERE last='pan' ORDER BY DateTime DESC;

SELECT * FROM employees WHERE last='pan' ORDER BY DateTime DESC LIMIT 1;

Update

UPDATE employees SET first='Peter' WHERE first='peter';

Delete

DELETE FROM employees WHERE first='peter';

Docker CLI access

docker exec -it containername mysql -p

other

time based

time INT(13)