Mysql/CheatSheet: Difference between revisions

From Wiki
No edit summary
Line 77: Line 77:
</pre>
</pre>


* data types
== data types ==
<pre>
<pre>
TINYINT         (A very small integer: -128/128 or 0/255)
TINYINT         (A very small integer: -128/128 or 0/255)
Line 104: Line 104:
SET         (A set; each column value may be assigned zero or more SET members
SET         (A set; each column value may be assigned zero or more SET members


DATE         (A date value in CCYY-MM-DD format
TIME         (A time value in hh:mm:ss format
DATETIME (A date and time value inCCYY-MM-DD hh:mm:ssformat
TIMESTAMP (A timestamp value in CCYY-MM-DD hh:mm:ss format
YEAR         (A year value in CCYY or YY format
</pre>
</pre>



Revision as of 08:12, 16 June 2020

Databases

  • Create
CREATE DATABASE [IF NOT EXISTS] database_name;
  • Use
USE database_name;
  • Delete
DROP DATABASE [IF EXISTS] database_name;
  • List all
SHOW DATABASES;
  • Storage Engines
    • MyISAM (optimized for compression and speed. portable. not transaction safe. used to be default)
    • InnoDB (optimized for speed. portable. 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
);
  • constraints
NOT NULL
UNIQUE
PRIMARY KEY   (NOT NULL and UNIQUE)
FOREIGN KEY
CHECK
DEFAULT
INDEX

data types

TINYINT	        (A very small integer: -128/128 or 0/255)
SMALLINT	(A small integer: -32768/32767 or 0/65535)
MEDIUMINT	(A medium-sized integer: -8388608/8388607 or 0/16777215)
INT	        (A standard integer: -2147483648/2147483647 or 0/4294967295)
BIGINT	        (A large integer: -9223372036854775808/9223372036854775807 or 0/18446744073709551615)
DECIMAL(P,D)    (A fixed-point number: P=digits, D=decimals)
FLOAT	        (A single-precision floating point number
DOUBLE	        (A double-precision floating point number
BIT	        (A bit field

CHAR	        (A fixed-length nonbinary (character) string
VARCHAR	        (A variable-length non-binary string
BINARY	        (A fixed-length binary string
VARBINARY	(A variable-length binary string
TINYBLOB	(A very small BLOB (binary large object)
BLOB     	(A small BLOB
MEDIUMBLOB	(A medium-sized BLOB
LONGBLOB	(A large BLOB
TINYTEXT	(A very small non-binary string
TEXT	        (A small non-binary string
MEDIUMTEXT	(A medium-sized non-binary string
LONGTEXT	(A large non-binary string
ENUM	        (An enumeration; each column value may be assigned one enumeration member
SET	        (A set; each column value may be assigned zero or more SET members

DATE	        (A date value in CCYY-MM-DD format
TIME	        (A time value in hh:mm:ss format
DATETIME	(A date and time value inCCYY-MM-DD hh:mm:ssformat
TIMESTAMP	(A timestamp value in CCYY-MM-DD hh:mm:ss format
YEAR	        (A year value in CCYY or YY format

other