View source for Mysql/CheatSheet

{| class="wikitable" style="float:right; margin-left: 10px;" width="400px"
| Quick links
|-
|
* [[Python/pymysql]]
|}


= Databases =
* List all
<blockquote>
<pre>
SHOW DATABASES;
</pre>
</blockquote>

* Create
<blockquote>
<pre>
CREATE DATABASE [IF NOT EXISTS] database_name;
</pre>
</blockquote>

* Use 
<blockquote>
<pre>
USE database_name;
</pre>
</blockquote>

* Delete
<blockquote>
<pre>
DROP DATABASE [IF EXISTS] database_name;
</pre>
</blockquote>

* 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
<blockquote>
<pre>
SHOW TABLES;
</pre>
</blockquote>

* create
<blockquote>
<pre>
CREATE TABLE [IF NOT EXISTS] table_name(
  column1 datatype constraint,
  column2 datatype constraint
);
</pre>
</blockquote>

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

* show table information
<blockquote>
<pre>
DESCRIBE cars;

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

* delete
<blockquote>
<pre>
DROP TABLE [IF EXISTS] cars;
</pre>
</blockquote>

== column definitions ==
<blockquote>
<pre>
NOT NULL
DEFAULT default_value
AUTO_INCREMENT
ZEROFILL
UNIQUE 
PRIMARY KEY   (NOT NULL and UNIQUE)
INVISIBLE
COMMENT 'string'
CHECK (expr)
</pre>
</blockquote>

== data types ==
<blockquote>
<pre>
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)

</pre>
</blockquote>
* data type attributes
<blockquote>
<pre>
SIGNED
UNSIGNED
ZEROFILL
UNSIGNED ZEROFILL
ZEROFILL UNSIGNED 

AUTO_INCREMENT
</pre>
</blockquote>

* charsets and collations
<blockquote>
<pre>
utf8_general_ci      (maxlen=1)
latin1_swedish_ci    (maxlen=3)
_ci = case insensitive
</pre>
</blockquote>

= Data =
== Insert ==

== Select ==

== Update ==

== Replace ==

== Delete ==


= Docker CLI access =
<blockquote>
<pre>
docker exec -it containername mysql -p
</pre>
</blockquote>

= other =
<blockquote>
<pre>

</pre>
</blockquote>

<blockquote>
<pre>

</pre>
</blockquote>

<blockquote>
<pre>

</pre>
</blockquote>